SELECT VST.VISIT_NUMBER VISIT_NUM,
WO.WORKORDER_NAME WORKORDER_NUM,
WDJ.DESCRIPTION WORKORDER_DESC,
WDJ.SCHEDULED_COMPLETION_DATE WO_ENDDATE,
CSIN.INCIDENT_NUMBER NR_NUM,
OSP.OSP_ORDER_NUMBER OSP_NUM,
(SELECT MEANING FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'AHL_WARRANTY_ENTLMNT_STATUS'
AND LOOKUP_CODE = ASE.ENTITLEMENT_STATUS_CODE) REVIEW_STATUS,
AWCO.CONTRACT_NUMBER WAR_CONTRACT,
AWCO.ACTIVE_END_DATE CON_ENDDATE,
APS.VENDOR_NAME VENDOR_NAME,
AHL_UTILITY_PVT.GET_UNIT_NAME(NVL(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID)) UNIT_NAME,
(SELECT MTLI.CONCATENATED_SEGMENTS FROM MTL_SYSTEM_ITEMS_KFV MTLI
WHERE MTLI.INVENTORY_ITEM_ID = CSI.INVENTORY_ITEM_ID
AND MTLI.ORGANIZATION_ID = ORG.ORGANIZATION_ID) ITEM,
CSI.SERIAL_NUMBER SERIAL_NUM,
ASE.WARRANTY_LABOUR_CAPTURE LABOR,
ASE.WARRANTY_MTL_CAPTURE MATERIAL,
ASE.ORDER_CLAIM_AMOUNT CLAIM_AMT
FROM (SELECT ORGANIZATION_ID, ORGANIZATION_NAME FROM org_organization_definitions
WHERE NVL(operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id()) ORG,
CSI_ITEM_INSTANCES CSI,
WIP_DISCRETE_JOBS WDJ,
CS_INCIDENTS_ALL_B CSIN,
AHL_OSP_ORDERS_B OSP,
AHL_OSP_ORDER_LINES OSPL,
MFG_LOOKUPS MLK3,
AHL_VISITS_VL VST,
AHL_VISIT_TASKS_B VTS,
AHL_WORKORDERS WO,
AHL_WARRANTY_CLAIMS_B AWCL,
AHL_WARRANTY_ENTITLEMENTS ASE,
AHL_WARRANTY_CONTRACTS_B AWCO,
AP_SUPPLIERS APS
WHERE WO.VISIT_TASK_ID = VTS.VISIT_TASK_ID
AND WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND VST.VISIT_ID = VTS.VISIT_ID
AND VST.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND NVL(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID)= CSI.INSTANCE_ID
AND VTS.SERVICE_REQUEST_ID = CSIN.INCIDENT_ID(+)
AND WO.WORKORDER_ID = OSPL.WORKORDER_ID(+)
AND NVL(OSPL.STATUS_CODE, 'ENTERED') NOT IN ('PO_DELETED', 'PO_CANCELLED')
AND OSPL.OSP_ORDER_ID = OSP.OSP_ORDER_ID(+)
AND WO.MASTER_WORKORDER_FLAG = 'N'
AND WO.STATUS_CODE NOT IN ('17', '22')
AND WDJ.WORK_ORDER_TYPE = MLK3.LOOKUP_CODE(+)
AND MLK3.LOOKUP_TYPE(+) = 'WIP_EAM_WORK_ORDER_TYPE'
AND AWCL.WARRANTY_CLAIM_ID = ASE.WARRANTY_CLAIM_ID
AND ASE.VISIT_TASK_ID = WO.VISIT_TASK_ID
AND ASE.WARRANTY_CONTRACT_ID = AWCO.WARRANTY_CONTRACT_ID(+)
AND AWCL.WARRANTY_VENDOR_ID = APS.VENDOR_ID
AND AWCL.WARRANTY_CLAIM_ID = :P_AHL_WAR_CLAIM_ID |