eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AHL_WORKORDERS_V

Source

View Text - Preformatted


SELECT WO.WORKORDER_ID, WO.WIP_ENTITY_ID, WO.OBJECT_VERSION_NUMBER, WO.WORKORDER_NAME JOB_NUMBER, WDJ.DESCRIPTION, VST.ORGANIZATION_ID, VTS.QUANTITY TASK_QUANTITY, ORG.NAME, BMD.DESCRIPTION, WDJ.OWNING_DEPARTMENT, BMD.DEPARTMENT_CLASS_CODE, BMD.DEPARTMENT_CODE, WO.STATUS_CODE, MLU.MEANING, WDJ.SCHEDULED_START_DATE, TO_CHAR (WDJ.SCHEDULED_START_DATE,'HH24'), TO_CHAR (WDJ.SCHEDULED_START_DATE,'MI'), WDJ.SCHEDULED_COMPLETION_DATE, TO_CHAR (WDJ.SCHEDULED_COMPLETION_DATE,'HH24'), TO_CHAR (WDJ.SCHEDULED_COMPLETION_DATE,'MI'), WO.ACTUAL_START_DATE, TO_CHAR (WO.ACTUAL_START_DATE,'HH24'), TO_CHAR (WO.ACTUAL_START_DATE,'MI'), WO.ACTUAL_END_DATE, TO_CHAR (WO.ACTUAL_END_DATE,'HH24'), TO_CHAR (WO.ACTUAL_END_DATE,'MI'), CSI.INVENTORY_ITEM_ID, NVL(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID), AHL_UTILITY_PVT.GET_UNIT_NAME(NVL(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID)), CSI.INSTANCE_NUMBER, CSI.QUANTITY, (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) CONCATENATED_SEGMENTS, (select MTLI.DESCRIPTION from MTL_SYSTEM_ITEMS_KFV MTLI where mtli.inventory_item_id = csi.inventory_item_id and MTLI.ORGANIZATION_ID = ORG.ORGANIZATION_ID) DESCRIPTION, CSI.SERIAL_NUMBER, CSI.UNIT_OF_MEASURE, MLC.SUBINVENTORY_CODE VST_SUBINVENTORY, VST.INV_LOCATOR_ID VST_LOCATOR_ID, MLC.CONCATENATED_SEGMENTS, VST.VISIT_ID, VST.VISIT_NUMBER, VST.VISIT_NAME, WO.VISIT_TASK_ID, VST.STATUS_CODE, AMRT.MR_HEADER_ID, VTS.VISIT_TASK_NUMBER, MRH.TITLE, VTS.MR_ROUTE_ID, WO.ROUTE_ID, ARB.TITLE, ARB.ROUTE_NO, ARB.REVISION_NUMBER, ARB.SERVICE_ITEM_ID, ARB.SERVICE_ITEM_ORG_ID, MTLS.DESCRIPTION, MTLS.CONCATENATED_SEGMENTS, MTLS.PRIMARY_UOM_CODE, VST.PROJECT_ID, PAA.NAME, VTS.PROJECT_TASK_ID, PAT.TASK_NAME, VTS.SERVICE_REQUEST_ID, CSIN.INCIDENT_NUMBER, WDJ.CLASS_CODE, VTS.UNIT_EFFECTIVITY_ID, UE.OBJECT_VERSION_NUMBER, ( CASE WHEN MRH.QA_INSPECTION_TYPE IS NULL AND UE.QA_INSPECTION_TYPE IS NULL THEN NULL WHEN UE.qa_collection_id IS NOT NULL THEN (SELECT qa.plan_id FROM qa_results qa WHERE qa.collection_id = UE.qa_collection_id AND rownum < 2 ) ELSE (SELECT QP.plan_id FROM QA_PLANS_VAL_V QP, QA_PLAN_TRANSACTIONS QPT, QA_PLAN_COLLECTION_TRIGGERS QPCT WHERE QP.plan_id = QPT.plan_id AND QPT.plan_transaction_id = QPCT.plan_transaction_id AND QP.organization_id = ORG.organization_id AND QPT.transaction_number = 2001 AND QPCT.collection_trigger_id = 87 AND QPCT.low_value = nvl(UE.QA_INSPECTION_TYPE, MRH.QA_INSPECTION_TYPE) GROUP BY qp.plan_id, qpt.transaction_number HAVING transaction_number = MAX(transaction_number) ) END) QA_PLAN_ID, UE.QA_COLLECTION_ID, DECODE(UE.UNIT_EFFECTIVITY_ID, NULL, NULL, AHL_COMPLETIONS_PVT.GET_MR_STATUS(UE.UNIT_EFFECTIVITY_ID) ), WO.PLAN_ID, WO.COLLECTION_ID, WDJ.PRIORITY, MLK.MEANING, WO.CONFIRM_FAILURE_FLAG, CSI.LOT_NUMBER, OSP.OSP_ORDER_ID, OSP.OSP_ORDER_NUMBER, OSP.PO_INTERFACE_HEADER_ID, OSP.OE_HEADER_ID, WDJ.FIRM_PLANNED_FLAG, WO.ATTRIBUTE_CATEGORY, WO.ATTRIBUTE1, WO.ATTRIBUTE2, WO.ATTRIBUTE3, WO.ATTRIBUTE4, WO.ATTRIBUTE5, WO.ATTRIBUTE6, WO.ATTRIBUTE7, WO.ATTRIBUTE8, WO.ATTRIBUTE9, WO.ATTRIBUTE10, WO.ATTRIBUTE11, WO.ATTRIBUTE12, WO.ATTRIBUTE13, WO.ATTRIBUTE14, WO.ATTRIBUTE15, WO.LAST_UPDATE_DATE, WO.LAST_UPDATED_BY, WO.CREATION_DATE, WO.CREATED_BY, WO.LAST_UPDATE_LOGIN, AHL_UTIL_UC_PKG.GET_UC_HEADER_ID(NVL(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID)) UC_HEADER_ID, ahl_prd_sern_change_pvt.get_serialtag_code(NVL(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID)), ahl_prd_sern_change_pvt.get_serialtag_meaning(NVL(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID)), UE.ORIGINATING_WO_ID, ( SELECT org_wo.workorder_name FROM ahl_workorders org_wo WHERE org_wo.workorder_id = UE.originating_wo_id ) ORIGINATING_WO_NAME, ARB.UNIT_RECEIPT_UPDATE_FLAG, AHL_COMPLETIONS_PVT.are_all_operations_complete(WO.WORKORDER_ID), WO.HOLD_REASON_CODE, (SELECT MEANING FROM FND_LOOKUP_VALUES_VL WHERE LOOKUP_TYPE = 'AHL_PRD_WO_HOLD_REASON' and LOOKUP_CODE = WO.HOLD_REASON_CODE) HOLD_REASON, WDJ.DATE_RELEASED, WDJ.DATE_COMPLETED, WDJ.DATE_CLOSED, AHL_PRD_PRINT_PVT.Get_JC_File_Location('WO',WO.WORKORDER_ID) FILE_LOCATION, AHL_PRD_PRINT_PVT.Generated_JobCard_Exists('WO',WO.WORKORDER_ID) JOB_CARD_EXISTS, AHL_PRD_PRINT_PVT.Get_Latest_JCG_Status('WO',WO.WORKORDER_ID) LATEST_JC_STATUS, AHL_PRD_PRINT_PVT.Get_JCG_Error_Msg('WO',WO.WORKORDER_ID) JOB_CARD_ERROR_MSG, VTS.SERVICE_TYPE_CODE, (select meaning from PA_LOOKUPS where LOOKUP_TYPE = 'SERVICE TYPE' and LOOKUP_CODE = VTS.SERVICE_TYPE_CODE) SERVICE_TYPE, AHL_WARRANTY_ENTL_PVT.Is_Task_Warranty_Available(WO.visit_task_id) TASK_WARRANTY_FLAG, NVL2( (SELECT DISTINCT 'X' FROM AHL_WARRANTY_CONTRACTS_B WHERE ITEM_INSTANCE_ID = CSI.INSTANCE_ID AND CONTRACT_STATUS_CODE ='ACTIVE' ),'Y','N' ) INSTANCE_WARRANTY_FLAG, WCB.CONTRACT_NUMBER , CLAIM.WARRANTY_CLAIM_NUMBER, CLS.MEANING CLAIM_STATUS, NVL(SE.ENTITLEMENT_STATUS_CODE,'NOT_APPLICABLE') ENTL_STATUS_CODE, NVL(COS.MEANING, (SELECT MEANING FROM FND_LOOKUP_VALUES_VL WHERE LOOKUP_TYPE = 'AHL_WARRANTY_ENTLMNT_STATUS' AND LOOKUP_CODE = 'NOT_APPLICABLE' ) ) ENTL_STATUS, (SELECT LKP.MEANING FROM AHL_VISIT_INST_ROUTINGS RTNG, FND_LOOKUP_VALUES_VL LKP WHERE RTNG.VISIT_ID = VST.VISIT_ID AND RTNG.INSTANCE_ID = VTS.INSTANCE_ID AND LKP.LOOKUP_TYPE = 'AHL_INSTANCE_ROUTING' AND LKP.LOOKUP_CODE = RTNG. INST_ROUTING_CODE AND NVL(LKP.ENABLED_FLAG,'N') = 'Y' AND NVL(LKP.END_DATE_ACTIVE,SYSDATE+1) > SYSDATE) INSTANCE_ROUTING , NVL(WO.AOG_FLAG, 'N') AOG_FLAG, DEMAND_WO_DATA.DEMAN_WO_ID DEMAN_WORKORDER_ID, DEMAND_WO_DATA.DEMAND_WO_NAME DEMAN_WORKORDER_NAME, NVL(FLV.MEANING, 'No') AOG_FLAG_MEANING FROM AHL_WORKORDERS WO, AHL_VISITS_VL VST, AHL_VISIT_TASKS_VL VTS, AHL_UNIT_EFFECTIVITIES_B UE, HR_ORGANIZATION_UNITS ORG, BOM_DEPARTMENTS BMD, AHL_ROUTES_VL ARB, FND_LOOKUP_VALUES_VL MLU, CSI_ITEM_INSTANCES CSI, MTL_SYSTEM_ITEMS_KFV MTLS, PA_PROJECTS_ALL PAA, PA_TASKS PAT, AHL_MR_ROUTES_APP_V AMRT, AHL_MR_HEADERS_B MRH, MFG_LOOKUPS MLK, WIP_DISCRETE_JOBS WDJ, MTL_ITEM_LOCATIONS_KFV MLC , CS_INCIDENTS_ALL_B CSIN, AHL_OSP_ORDERS_B OSP, AHL_OSP_ORDER_LINES OSPL, (SELECT ORGANIZATION_ID FROM org_organization_definitions WHERE NVL (operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id()) OUORG, AHL_WARRANTY_ENTITLEMENTS SE, AHL_WARRANTY_CONTRACTS_B WCB, AHL_WARRANTY_CLAIMS_B CLAIM, FND_LOOKUP_VALUES_VL CLS, FND_LOOKUP_VALUES_VL COS, (SELECT AW.WORKORDER_ID DEMAN_WO_ID, AW.WORKORDER_NAME DEMAND_WO_NAME, WDJ.WIP_ENTITY_ID SUPPLY_WIP_ID FROM MTL_RESERVATIONS MR, AHL_WORKORDERS AW, WIP_DISCRETE_JOBS WDJ WHERE MR.SUPPLY_SOURCE_HEADER_ID = WDJ.WIP_ENTITY_ID AND AW.WIP_ENTITY_ID = MR.DEMAND_SOURCE_HEADER_ID AND MR.SUPPLY_SOURCE_TYPE_ID = 5 AND MR.EXTERNAL_SOURCE_CODE = 'AHL' ) DEMAND_WO_DATA, FND_LOOKUP_VALUES_VL FLV WHERE WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID AND WDJ.WIP_ENTITY_ID=WO.WIP_ENTITY_ID AND UE.UNIT_EFFECTIVITY_ID (+)=VTS.UNIT_EFFECTIVITY_ID AND VST.VISIT_ID=VTS.VISIT_ID AND VST.ORGANIZATION_ID=ORG.ORGANIZATION_ID AND VST.ORGANIZATION_ID=OUORG.ORGANIZATION_ID AND WDJ.OWNING_DEPARTMENT=BMD.DEPARTMENT_ID(+) AND VTS.MR_ROUTE_ID=AMRT.MR_ROUTE_ID (+) AND AMRT.MR_HEADER_ID=MRH.MR_HEADER_ID(+) AND WO.ROUTE_ID=ARB.ROUTE_ID (+) AND ARB.SERVICE_ITEM_ORG_ID=MTLS.ORGANIZATION_ID (+) AND ARB.SERVICE_ITEM_ID=MTLS.INVENTORY_ITEM_ID (+) AND NVL(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID)=CSI.INSTANCE_ID AND MLU.LOOKUP_TYPE(+)='AHL_JOB_STATUS' AND WO.STATUS_CODE=MLU.LOOKUP_CODE(+) AND VST.PROJECT_ID=PAA.PROJECT_ID (+) AND VTS.PROJECT_TASK_ID=PAT.TASK_ID (+) AND MLK.LOOKUP_TYPE(+)='WIP_EAM_ACTIVITY_PRIORITY' AND WDJ.PRIORITY=MLK.LOOKUP_CODE(+) AND VST.INV_LOCATOR_ID=MLC.INVENTORY_LOCATION_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 WO.VISIT_TASK_ID = SE.VISIT_TASK_ID(+) AND SE.WARRANTY_CLAIM_ID = CLAIM.WARRANTY_CLAIM_ID(+) AND CLAIM.CLAIM_STATUS_CODE = CLS.LOOKUP_CODE(+) AND CLS.LOOKUP_TYPE(+) = 'AHL_WARRANTY_CLAIM_STATUS' AND SE.ENTITLEMENT_STATUS_CODE = COS.LOOKUP_CODE(+) AND COS.LOOKUP_TYPE(+) = 'AHL_WARRANTY_ENTLMNT_STATUS' AND SE.WARRANTY_CONTRACT_ID = WCB.WARRANTY_CONTRACT_ID(+) AND WDJ.WIP_ENTITY_ID = DEMAND_WO_DATA.SUPPLY_WIP_ID (+) AND WO.AOG_FLAG = FLV.LOOKUP_CODE(+) AND FLV.LOOKUP_TYPE(+) = 'AHL_YES_NO_TYPE'

View Text - HTML Formatted

SELECT WO.WORKORDER_ID
, WO.WIP_ENTITY_ID
, WO.OBJECT_VERSION_NUMBER
, WO.WORKORDER_NAME JOB_NUMBER
, WDJ.DESCRIPTION
, VST.ORGANIZATION_ID
, VTS.QUANTITY TASK_QUANTITY
, ORG.NAME
, BMD.DESCRIPTION
, WDJ.OWNING_DEPARTMENT
, BMD.DEPARTMENT_CLASS_CODE
, BMD.DEPARTMENT_CODE
, WO.STATUS_CODE
, MLU.MEANING
, WDJ.SCHEDULED_START_DATE
, TO_CHAR (WDJ.SCHEDULED_START_DATE
, 'HH24')
, TO_CHAR (WDJ.SCHEDULED_START_DATE
, 'MI')
, WDJ.SCHEDULED_COMPLETION_DATE
, TO_CHAR (WDJ.SCHEDULED_COMPLETION_DATE
, 'HH24')
, TO_CHAR (WDJ.SCHEDULED_COMPLETION_DATE
, 'MI')
, WO.ACTUAL_START_DATE
, TO_CHAR (WO.ACTUAL_START_DATE
, 'HH24')
, TO_CHAR (WO.ACTUAL_START_DATE
, 'MI')
, WO.ACTUAL_END_DATE
, TO_CHAR (WO.ACTUAL_END_DATE
, 'HH24')
, TO_CHAR (WO.ACTUAL_END_DATE
, 'MI')
, CSI.INVENTORY_ITEM_ID
, NVL(VTS.INSTANCE_ID
, VST.ITEM_INSTANCE_ID)
, AHL_UTILITY_PVT.GET_UNIT_NAME(NVL(VTS.INSTANCE_ID
, VST.ITEM_INSTANCE_ID))
, CSI.INSTANCE_NUMBER
, CSI.QUANTITY
, (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) CONCATENATED_SEGMENTS
, (SELECT MTLI.DESCRIPTION
FROM MTL_SYSTEM_ITEMS_KFV MTLI
WHERE MTLI.INVENTORY_ITEM_ID = CSI.INVENTORY_ITEM_ID
AND MTLI.ORGANIZATION_ID = ORG.ORGANIZATION_ID) DESCRIPTION
, CSI.SERIAL_NUMBER
, CSI.UNIT_OF_MEASURE
, MLC.SUBINVENTORY_CODE VST_SUBINVENTORY
, VST.INV_LOCATOR_ID VST_LOCATOR_ID
, MLC.CONCATENATED_SEGMENTS
, VST.VISIT_ID
, VST.VISIT_NUMBER
, VST.VISIT_NAME
, WO.VISIT_TASK_ID
, VST.STATUS_CODE
, AMRT.MR_HEADER_ID
, VTS.VISIT_TASK_NUMBER
, MRH.TITLE
, VTS.MR_ROUTE_ID
, WO.ROUTE_ID
, ARB.TITLE
, ARB.ROUTE_NO
, ARB.REVISION_NUMBER
, ARB.SERVICE_ITEM_ID
, ARB.SERVICE_ITEM_ORG_ID
, MTLS.DESCRIPTION
, MTLS.CONCATENATED_SEGMENTS
, MTLS.PRIMARY_UOM_CODE
, VST.PROJECT_ID
, PAA.NAME
, VTS.PROJECT_TASK_ID
, PAT.TASK_NAME
, VTS.SERVICE_REQUEST_ID
, CSIN.INCIDENT_NUMBER
, WDJ.CLASS_CODE
, VTS.UNIT_EFFECTIVITY_ID
, UE.OBJECT_VERSION_NUMBER
, ( CASE WHEN MRH.QA_INSPECTION_TYPE IS NULL
AND UE.QA_INSPECTION_TYPE IS NULL THEN NULL WHEN UE.QA_COLLECTION_ID IS NOT NULL THEN (SELECT QA.PLAN_ID
FROM QA_RESULTS QA
WHERE QA.COLLECTION_ID = UE.QA_COLLECTION_ID
AND ROWNUM < 2 ) ELSE (SELECT QP.PLAN_ID
FROM QA_PLANS_VAL_V QP
, QA_PLAN_TRANSACTIONS QPT
, QA_PLAN_COLLECTION_TRIGGERS QPCT
WHERE QP.PLAN_ID = QPT.PLAN_ID
AND QPT.PLAN_TRANSACTION_ID = QPCT.PLAN_TRANSACTION_ID
AND QP.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND QPT.TRANSACTION_NUMBER = 2001
AND QPCT.COLLECTION_TRIGGER_ID = 87
AND QPCT.LOW_VALUE = NVL(UE.QA_INSPECTION_TYPE
, MRH.QA_INSPECTION_TYPE) GROUP BY QP.PLAN_ID
, QPT.TRANSACTION_NUMBER HAVING TRANSACTION_NUMBER = MAX(TRANSACTION_NUMBER) ) END) QA_PLAN_ID
, UE.QA_COLLECTION_ID
, DECODE(UE.UNIT_EFFECTIVITY_ID
, NULL
, NULL
, AHL_COMPLETIONS_PVT.GET_MR_STATUS(UE.UNIT_EFFECTIVITY_ID) )
, WO.PLAN_ID
, WO.COLLECTION_ID
, WDJ.PRIORITY
, MLK.MEANING
, WO.CONFIRM_FAILURE_FLAG
, CSI.LOT_NUMBER
, OSP.OSP_ORDER_ID
, OSP.OSP_ORDER_NUMBER
, OSP.PO_INTERFACE_HEADER_ID
, OSP.OE_HEADER_ID
, WDJ.FIRM_PLANNED_FLAG
, WO.ATTRIBUTE_CATEGORY
, WO.ATTRIBUTE1
, WO.ATTRIBUTE2
, WO.ATTRIBUTE3
, WO.ATTRIBUTE4
, WO.ATTRIBUTE5
, WO.ATTRIBUTE6
, WO.ATTRIBUTE7
, WO.ATTRIBUTE8
, WO.ATTRIBUTE9
, WO.ATTRIBUTE10
, WO.ATTRIBUTE11
, WO.ATTRIBUTE12
, WO.ATTRIBUTE13
, WO.ATTRIBUTE14
, WO.ATTRIBUTE15
, WO.LAST_UPDATE_DATE
, WO.LAST_UPDATED_BY
, WO.CREATION_DATE
, WO.CREATED_BY
, WO.LAST_UPDATE_LOGIN
, AHL_UTIL_UC_PKG.GET_UC_HEADER_ID(NVL(VTS.INSTANCE_ID
, VST.ITEM_INSTANCE_ID)) UC_HEADER_ID
, AHL_PRD_SERN_CHANGE_PVT.GET_SERIALTAG_CODE(NVL(VTS.INSTANCE_ID
, VST.ITEM_INSTANCE_ID))
, AHL_PRD_SERN_CHANGE_PVT.GET_SERIALTAG_MEANING(NVL(VTS.INSTANCE_ID
, VST.ITEM_INSTANCE_ID))
, UE.ORIGINATING_WO_ID
, ( SELECT ORG_WO.WORKORDER_NAME
FROM AHL_WORKORDERS ORG_WO
WHERE ORG_WO.WORKORDER_ID = UE.ORIGINATING_WO_ID ) ORIGINATING_WO_NAME
, ARB.UNIT_RECEIPT_UPDATE_FLAG
, AHL_COMPLETIONS_PVT.ARE_ALL_OPERATIONS_COMPLETE(WO.WORKORDER_ID)
, WO.HOLD_REASON_CODE
, (SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'AHL_PRD_WO_HOLD_REASON'
AND LOOKUP_CODE = WO.HOLD_REASON_CODE) HOLD_REASON
, WDJ.DATE_RELEASED
, WDJ.DATE_COMPLETED
, WDJ.DATE_CLOSED
, AHL_PRD_PRINT_PVT.GET_JC_FILE_LOCATION('WO'
, WO.WORKORDER_ID) FILE_LOCATION
, AHL_PRD_PRINT_PVT.GENERATED_JOBCARD_EXISTS('WO'
, WO.WORKORDER_ID) JOB_CARD_EXISTS
, AHL_PRD_PRINT_PVT.GET_LATEST_JCG_STATUS('WO'
, WO.WORKORDER_ID) LATEST_JC_STATUS
, AHL_PRD_PRINT_PVT.GET_JCG_ERROR_MSG('WO'
, WO.WORKORDER_ID) JOB_CARD_ERROR_MSG
, VTS.SERVICE_TYPE_CODE
, (SELECT MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE = 'SERVICE TYPE'
AND LOOKUP_CODE = VTS.SERVICE_TYPE_CODE) SERVICE_TYPE
, AHL_WARRANTY_ENTL_PVT.IS_TASK_WARRANTY_AVAILABLE(WO.VISIT_TASK_ID) TASK_WARRANTY_FLAG
, NVL2( (SELECT DISTINCT 'X'
FROM AHL_WARRANTY_CONTRACTS_B
WHERE ITEM_INSTANCE_ID = CSI.INSTANCE_ID
AND CONTRACT_STATUS_CODE ='ACTIVE' )
, 'Y'
, 'N' ) INSTANCE_WARRANTY_FLAG
, WCB.CONTRACT_NUMBER
, CLAIM.WARRANTY_CLAIM_NUMBER
, CLS.MEANING CLAIM_STATUS
, NVL(SE.ENTITLEMENT_STATUS_CODE
, 'NOT_APPLICABLE') ENTL_STATUS_CODE
, NVL(COS.MEANING
, (SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'AHL_WARRANTY_ENTLMNT_STATUS'
AND LOOKUP_CODE = 'NOT_APPLICABLE' ) ) ENTL_STATUS
, (SELECT LKP.MEANING
FROM AHL_VISIT_INST_ROUTINGS RTNG
, FND_LOOKUP_VALUES_VL LKP
WHERE RTNG.VISIT_ID = VST.VISIT_ID
AND RTNG.INSTANCE_ID = VTS.INSTANCE_ID
AND LKP.LOOKUP_TYPE = 'AHL_INSTANCE_ROUTING'
AND LKP.LOOKUP_CODE = RTNG. INST_ROUTING_CODE
AND NVL(LKP.ENABLED_FLAG
, 'N') = 'Y'
AND NVL(LKP.END_DATE_ACTIVE
, SYSDATE+1) > SYSDATE) INSTANCE_ROUTING
, NVL(WO.AOG_FLAG
, 'N') AOG_FLAG
, DEMAND_WO_DATA.DEMAN_WO_ID DEMAN_WORKORDER_ID
, DEMAND_WO_DATA.DEMAND_WO_NAME DEMAN_WORKORDER_NAME
, NVL(FLV.MEANING
, 'NO') AOG_FLAG_MEANING
FROM AHL_WORKORDERS WO
, AHL_VISITS_VL VST
, AHL_VISIT_TASKS_VL VTS
, AHL_UNIT_EFFECTIVITIES_B UE
, HR_ORGANIZATION_UNITS ORG
, BOM_DEPARTMENTS BMD
, AHL_ROUTES_VL ARB
, FND_LOOKUP_VALUES_VL MLU
, CSI_ITEM_INSTANCES CSI
, MTL_SYSTEM_ITEMS_KFV MTLS
, PA_PROJECTS_ALL PAA
, PA_TASKS PAT
, AHL_MR_ROUTES_APP_V AMRT
, AHL_MR_HEADERS_B MRH
, MFG_LOOKUPS MLK
, WIP_DISCRETE_JOBS WDJ
, MTL_ITEM_LOCATIONS_KFV MLC
, CS_INCIDENTS_ALL_B CSIN
, AHL_OSP_ORDERS_B OSP
, AHL_OSP_ORDER_LINES OSPL
, (SELECT ORGANIZATION_ID
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE NVL (OPERATING_UNIT
, MO_GLOBAL.GET_CURRENT_ORG_ID()) = MO_GLOBAL.GET_CURRENT_ORG_ID()) OUORG
, AHL_WARRANTY_ENTITLEMENTS SE
, AHL_WARRANTY_CONTRACTS_B WCB
, AHL_WARRANTY_CLAIMS_B CLAIM
, FND_LOOKUP_VALUES_VL CLS
, FND_LOOKUP_VALUES_VL COS
, (SELECT AW.WORKORDER_ID DEMAN_WO_ID
, AW.WORKORDER_NAME DEMAND_WO_NAME
, WDJ.WIP_ENTITY_ID SUPPLY_WIP_ID
FROM MTL_RESERVATIONS MR
, AHL_WORKORDERS AW
, WIP_DISCRETE_JOBS WDJ
WHERE MR.SUPPLY_SOURCE_HEADER_ID = WDJ.WIP_ENTITY_ID
AND AW.WIP_ENTITY_ID = MR.DEMAND_SOURCE_HEADER_ID
AND MR.SUPPLY_SOURCE_TYPE_ID = 5
AND MR.EXTERNAL_SOURCE_CODE = 'AHL' ) DEMAND_WO_DATA
, FND_LOOKUP_VALUES_VL FLV
WHERE WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
AND WDJ.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND UE.UNIT_EFFECTIVITY_ID (+)=VTS.UNIT_EFFECTIVITY_ID
AND VST.VISIT_ID=VTS.VISIT_ID
AND VST.ORGANIZATION_ID=ORG.ORGANIZATION_ID
AND VST.ORGANIZATION_ID=OUORG.ORGANIZATION_ID
AND WDJ.OWNING_DEPARTMENT=BMD.DEPARTMENT_ID(+)
AND VTS.MR_ROUTE_ID=AMRT.MR_ROUTE_ID (+)
AND AMRT.MR_HEADER_ID=MRH.MR_HEADER_ID(+)
AND WO.ROUTE_ID=ARB.ROUTE_ID (+)
AND ARB.SERVICE_ITEM_ORG_ID=MTLS.ORGANIZATION_ID (+)
AND ARB.SERVICE_ITEM_ID=MTLS.INVENTORY_ITEM_ID (+)
AND NVL(VTS.INSTANCE_ID
, VST.ITEM_INSTANCE_ID)=CSI.INSTANCE_ID
AND MLU.LOOKUP_TYPE(+)='AHL_JOB_STATUS'
AND WO.STATUS_CODE=MLU.LOOKUP_CODE(+)
AND VST.PROJECT_ID=PAA.PROJECT_ID (+)
AND VTS.PROJECT_TASK_ID=PAT.TASK_ID (+)
AND MLK.LOOKUP_TYPE(+)='WIP_EAM_ACTIVITY_PRIORITY'
AND WDJ.PRIORITY=MLK.LOOKUP_CODE(+)
AND VST.INV_LOCATOR_ID=MLC.INVENTORY_LOCATION_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 WO.VISIT_TASK_ID = SE.VISIT_TASK_ID(+)
AND SE.WARRANTY_CLAIM_ID = CLAIM.WARRANTY_CLAIM_ID(+)
AND CLAIM.CLAIM_STATUS_CODE = CLS.LOOKUP_CODE(+)
AND CLS.LOOKUP_TYPE(+) = 'AHL_WARRANTY_CLAIM_STATUS'
AND SE.ENTITLEMENT_STATUS_CODE = COS.LOOKUP_CODE(+)
AND COS.LOOKUP_TYPE(+) = 'AHL_WARRANTY_ENTLMNT_STATUS'
AND SE.WARRANTY_CONTRACT_ID = WCB.WARRANTY_CONTRACT_ID(+)
AND WDJ.WIP_ENTITY_ID = DEMAND_WO_DATA.SUPPLY_WIP_ID (+)
AND WO.AOG_FLAG = FLV.LOOKUP_CODE(+)
AND FLV.LOOKUP_TYPE(+) = 'AHL_YES_NO_TYPE'