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,
INSTANCE.ACTIVE_START_DATE SERVICE_ENTRY_DATE,
CONTRACT.CONTRACT_NUMBER,
FND_STATUS.MEANING CONTRACT_STATUS,
CONTRACT.DESCRIPTION CONTRACT_DESC,
VENDOR.VENDOR_NAME WARRANTY_VENDOR_NAME,
CONTRACT.WARRANTY_PERIOD WARRANTY_PERIOD,
UOM.DESCRIPTION CONTRACT_UOM,
CONTRACT.ACTIVE_START_DATE CONTRACT_ACTIVE_START_DATE,
CONTRACT.EXPIRATION_DATE CONTRACT_EXPIRATION_DATE,
COUNTER.NAME CONTRACT_COUNTER_NAME,
UOM_CTR.DESCRIPTION CONTRACT_CNTR_UOM_DESC,
CONT_CTR.THRESHOLD CONTRACT_CNTR_THRESHOLD,
CONT_CTR.START_VALUE CONTRACT_CNTR_START_VALUE,
(CONT_CTR.THRESHOLD + CONT_CTR.START_VALUE) STOP_VALUE,
CONT_CTR.EXPIRATION_DATE CONTRACT_CNTR_EXPIRATION_DATE
FROM MTL_SYSTEM_ITEMS_B_KFV ITEM,
AHL_VISITS_VL VISIT,
AHL_UNIT_CONFIG_HEADERS UNIT,
HR_ALL_ORGANIZATION_UNITS_TL HROU,
BOM_DEPARTMENTS BDPT,
CSI_ITEM_INSTANCES INSTANCE,
AHL_WARRANTY_CONTRACTS_VL CONTRACT,
AHL_WARRANTY_CONT_CTR_VL CONT_CTR,
FND_LOOKUP_VALUES_VL FND_STATUS,
PO_VENDORS VENDOR,
MTL_UNITS_OF_MEASURE_VL UOM,
CSI_COUNTERS_VL COUNTER,
MTL_UNITS_OF_MEASURE_VL UOM_CTR
WHERE VISIT.VISIT_NUMBER = :P_VISIT_NUMBER
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 EXISTS (SELECT 'X'
FROM AHL_VISIT_TASKS_B TSK
WHERE TSK.INSTANCE_ID = INSTANCE.INSTANCE_ID
AND TSK.VISIT_ID = VISIT.VISIT_ID)
AND ITEM.ORGANIZATION_ID = INSTANCE.INV_MASTER_ORGANIZATION_ID
AND ITEM.INVENTORY_ITEM_ID = INSTANCE.INVENTORY_ITEM_ID
AND CONTRACT.ITEM_INSTANCE_ID(+) = INSTANCE.INSTANCE_ID
AND CONTRACT.WARRANTY_CONTRACT_ID = CONT_CTR.WARRANTY_CONTRACT_ID(+)
AND FND_STATUS.LOOKUP_TYPE(+) = 'AHL_WARRANTY_CONT_STATUS'
AND FND_STATUS.LOOKUP_CODE(+) = CONTRACT.CONTRACT_STATUS_CODE
AND VENDOR.VENDOR_ID (+) = CONTRACT.WARRANTY_VENDOR_ID
AND CONTRACT.PERIOD_UOM_CODE = UOM.UOM_CODE(+)
AND CONT_CTR.COUNTER_ID = COUNTER.COUNTER_ID (+)
AND COUNTER.UOM_CODE = UOM_CTR.UOM_CODE (+)
ORDER BY VISIT_NUMBER, INSTANCE_NUMBER |
