SELECT VISIT.VISIT_NUMBER,
VISIT.VISIT_NAME,
UNIT.NAME UNIT_NAME,
HROU.NAME ORGANIZATION_NAME,
BDPT.DESCRIPTION DEPARTMENT_NAME,
VISIT.START_DATE_TIME START_DATE,
AHL_VWP_TIMES_PVT.get_visit_end_time(VISIT.visit_id) END_DATE,
INSTANCE.INSTANCE_NUMBER,
INSTANCE.SERIAL_NUMBER,
INSTANCE.LOT_NUMBER,
ITEM.CONCATENATED_SEGMENTS ITEM_NAME,
ITEM.DESCRIPTION ITEM_DESCRIPTION,
TASK.TASK_NUMBER,
TASK.TASK_NAME,
TASK.QUANTITY,
TASK.UOM,
TASK.MR_NAME MAINTENENCE_REQUIREMENT,
TASK.DEPARTMENT_NAME TASK_DEPT_NAME,
TASK.STAGE_NAME,
TASK.TASK_TYPE_NAME,
TASK.TASK_STATUS_NAME,
TASK.TASK_WARRANTY_FLAG,
TASK.INSTANCE_WARRANTY_FLAG,
TASK.CONTRACT_NUMBER WARRANTY_CONTRACT,
TASK.ENTITLEMENT_STATUS WARRANTY_STATUS
FROM MTL_SYSTEM_ITEMS_B_KFV ITEM,
AHL_VISITS_VL VISIT,
AHL_UNIT_CONFIG_HEADERS UNIT,
AHL_SEARCH_VISIT_TASK_V TASK,
HR_ALL_ORGANIZATION_UNITS_TL HROU,
BOM_DEPARTMENTS BDPT,
CSI_ITEM_INSTANCES INSTANCE
WHERE VISIT.VISIT_NUMBER = :P_VISIT_NUMBER
AND VISIT.VISIT_ID = TASK.VISIT_ID
AND UNIT.CSI_ITEM_INSTANCE_ID (+) = VISIT.ITEM_INSTANCE_ID
AND VISIT.ORGANIZATION_ID = HROU.ORGANIZATION_ID(+)
AND HROU.LANGUAGE(+) = USERENV('LANG')
AND VISIT.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
AND TASK.INSTANCE_NUMBER = INSTANCE.INSTANCE_NUMBER
AND ITEM.INVENTORY_ITEM_ID = TASK.ITEM_ID
AND ITEM.ORGANIZATION_ID = TASK.ITEM_ORGANIZATION_ID
ORDER BY VISIT_NUMBER, INSTANCE_NUMBER, TASK_NUMBER |
