AHL Supplier Warranty Claims

      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