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_SEARCH_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, VTS.QUANTITY TASK_QUANTITY, VST.ORGANIZATION_ID, ORG.ORGANIZATION_NAME, BMD.DESCRIPTION, WDJ.OWNING_DEPARTMENT, BMD.DEPARTMENT_CLASS_CODE, WO.STATUS_CODE, (SELECT MLU.meaning from fnd_lookup_values_vl MLU where MLU.LOOKUP_TYPE ='AHL_JOB_STATUS' AND MLU.LOOKUP_CODE = WO.STATUS_CODE) MEANING, WDJ.SCHEDULED_START_DATE, WDJ.SCHEDULED_COMPLETION_DATE, NVL(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID), AHL_UTILITY_PVT.GET_UNIT_NAME(NVL(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID)), (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, CSI.SERIAL_NUMBER, VST.VISIT_ID, VST.VISIT_NUMBER, VST.STATUS_CODE,VTS.VISIT_TASK_NUMBER, MRH.TITLE, WO.ROUTE_ID, PAA.NAME, PAT.TASK_NAME, VTS.SERVICE_REQUEST_ID, CSIN.INCIDENT_NUMBER, WDJ.CLASS_CODE, VTS.UNIT_EFFECTIVITY_ID, UE1.OBJECT_VERSION_NUMBER, (CASE WHEN MRH.QA_INSPECTION_TYPE IS NULL THEN NULL WHEN UE1.qa_collection_id IS NOT NULL THEN (SELECT qa.plan_id from qa_results qa where qa.collection_id = UE1.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 in (9999,2001) AND QPCT.collection_trigger_id = 87 AND QPCT.low_value = MRH.QA_INSPECTION_TYPE group by qp.plan_id, qpt.transaction_number having transaction_number = MAX(transaction_number)) END) QA_PLAN_ID, UE1.QA_COLLECTION_ID, DECODE(UE1.UNIT_EFFECTIVITY_ID, NULL,NULL,AHL_COMPLETIONS_PVT.GET_MR_STATUS(UE1.UNIT_EFFECTIVITY_ID) ), WO.PLAN_ID, WO.COLLECTION_ID, WDJ.PRIORITY, (SELECT MLK.MEANING from mfg_lookups MLK where MLK.LOOKUP_TYPE ='WIP_EAM_ACTIVITY_PRIORITY' AND MLK.LOOKUP_CODE = WDJ.PRIORITY) MLK_MEANING, WO.CONFIRM_FAILURE_FLAG, OSP.OSP_ORDER_ID, OSP.OSP_ORDER_NUMBER, OSP.PO_INTERFACE_HEADER_ID, OSP.OE_HEADER_ID, WO.LAST_UPDATE_DATE, WO.LAST_UPDATED_BY, WO.CREATION_DATE, WO.CREATED_BY,WO.LAST_UPDATE_LOGIN, AHL_UTIL_UC_PKG.IS_UNIT_QUARANTINED(NULL, NVL(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID)), WO.ACTUAL_START_DATE, WO.ACTUAL_END_DATE, WO.VISIT_TASK_ID, VST.VISIT_NAME, WDJ.WORK_ORDER_TYPE, MLK3.MEANING, AHL_COMPLETIONS_PVT.are_all_operations_complete(WO.WORKORDER_ID) all_ops_complete 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, BOM_DEPARTMENTS BMD, CSI_ITEM_INSTANCES CSI, PA_PROJECTS_ALL PAA, PA_TASKS PAT, AHL_MR_ROUTES AMRT, AHL_MR_HEADERS_B MRH, WIP_DISCRETE_JOBS WDJ, CS_INCIDENTS_ALL_B CSIN, AHL_OSP_ORDERS_B OSP, AHL_OSP_ORDER_LINES OSPL, MFG_LOOKUPS MLK3, AHL_UNIT_EFFECTIVITIES_B UE1, AHL_VISITS_VL VST, AHL_VISIT_TASKS_B VTS, AHL_WORKORDERS WO WHERE WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID AND WDJ.WIP_ENTITY_ID=WO.WIP_ENTITY_ID AND VTS.UNIT_EFFECTIVITY_ID = UE1.UNIT_EFFECTIVITY_ID(+) AND VST.VISIT_ID=VTS.VISIT_ID AND VST.ORGANIZATION_ID=ORG.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 NVL(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID)=CSI.INSTANCE_ID AND VST.PROJECT_ID=PAA.PROJECT_ID (+) AND VTS.PROJECT_TASK_ID=PAT.TASK_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'

View Text - HTML Formatted

SELECT WO.WORKORDER_ID
, WO.WIP_ENTITY_ID
, WO.OBJECT_VERSION_NUMBER
, WO.WORKORDER_NAME JOB_NUMBER
, WDJ.DESCRIPTION
, VTS.QUANTITY TASK_QUANTITY
, VST.ORGANIZATION_ID
, ORG.ORGANIZATION_NAME
, BMD.DESCRIPTION
, WDJ.OWNING_DEPARTMENT
, BMD.DEPARTMENT_CLASS_CODE
, WO.STATUS_CODE
, (SELECT MLU.MEANING
FROM FND_LOOKUP_VALUES_VL MLU
WHERE MLU.LOOKUP_TYPE ='AHL_JOB_STATUS'
AND MLU.LOOKUP_CODE = WO.STATUS_CODE) MEANING
, WDJ.SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE
, NVL(VTS.INSTANCE_ID
, VST.ITEM_INSTANCE_ID)
, AHL_UTILITY_PVT.GET_UNIT_NAME(NVL(VTS.INSTANCE_ID
, VST.ITEM_INSTANCE_ID))
, (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
, CSI.SERIAL_NUMBER
, VST.VISIT_ID
, VST.VISIT_NUMBER
, VST.STATUS_CODE
, VTS.VISIT_TASK_NUMBER
, MRH.TITLE
, WO.ROUTE_ID
, PAA.NAME
, PAT.TASK_NAME
, VTS.SERVICE_REQUEST_ID
, CSIN.INCIDENT_NUMBER
, WDJ.CLASS_CODE
, VTS.UNIT_EFFECTIVITY_ID
, UE1.OBJECT_VERSION_NUMBER
, (CASE WHEN MRH.QA_INSPECTION_TYPE IS NULL THEN NULL WHEN UE1.QA_COLLECTION_ID IS NOT NULL THEN (SELECT QA.PLAN_ID
FROM QA_RESULTS QA
WHERE QA.COLLECTION_ID = UE1.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 IN (9999
, 2001)
AND QPCT.COLLECTION_TRIGGER_ID = 87
AND QPCT.LOW_VALUE = MRH.QA_INSPECTION_TYPE GROUP BY QP.PLAN_ID
, QPT.TRANSACTION_NUMBER HAVING TRANSACTION_NUMBER = MAX(TRANSACTION_NUMBER)) END) QA_PLAN_ID
, UE1.QA_COLLECTION_ID
, DECODE(UE1.UNIT_EFFECTIVITY_ID
, NULL
, NULL
, AHL_COMPLETIONS_PVT.GET_MR_STATUS(UE1.UNIT_EFFECTIVITY_ID) )
, WO.PLAN_ID
, WO.COLLECTION_ID
, WDJ.PRIORITY
, (SELECT MLK.MEANING
FROM MFG_LOOKUPS MLK
WHERE MLK.LOOKUP_TYPE ='WIP_EAM_ACTIVITY_PRIORITY'
AND MLK.LOOKUP_CODE = WDJ.PRIORITY) MLK_MEANING
, WO.CONFIRM_FAILURE_FLAG
, OSP.OSP_ORDER_ID
, OSP.OSP_ORDER_NUMBER
, OSP.PO_INTERFACE_HEADER_ID
, OSP.OE_HEADER_ID
, WO.LAST_UPDATE_DATE
, WO.LAST_UPDATED_BY
, WO.CREATION_DATE
, WO.CREATED_BY
, WO.LAST_UPDATE_LOGIN
, AHL_UTIL_UC_PKG.IS_UNIT_QUARANTINED(NULL
, NVL(VTS.INSTANCE_ID
, VST.ITEM_INSTANCE_ID))
, WO.ACTUAL_START_DATE
, WO.ACTUAL_END_DATE
, WO.VISIT_TASK_ID
, VST.VISIT_NAME
, WDJ.WORK_ORDER_TYPE
, MLK3.MEANING
, AHL_COMPLETIONS_PVT.ARE_ALL_OPERATIONS_COMPLETE(WO.WORKORDER_ID) ALL_OPS_COMPLETE
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
, BOM_DEPARTMENTS BMD
, CSI_ITEM_INSTANCES CSI
, PA_PROJECTS_ALL PAA
, PA_TASKS PAT
, AHL_MR_ROUTES AMRT
, AHL_MR_HEADERS_B MRH
, WIP_DISCRETE_JOBS WDJ
, CS_INCIDENTS_ALL_B CSIN
, AHL_OSP_ORDERS_B OSP
, AHL_OSP_ORDER_LINES OSPL
, MFG_LOOKUPS MLK3
, AHL_UNIT_EFFECTIVITIES_B UE1
, AHL_VISITS_VL VST
, AHL_VISIT_TASKS_B VTS
, AHL_WORKORDERS WO
WHERE WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
AND WDJ.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND VTS.UNIT_EFFECTIVITY_ID = UE1.UNIT_EFFECTIVITY_ID(+)
AND VST.VISIT_ID=VTS.VISIT_ID
AND VST.ORGANIZATION_ID=ORG.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 NVL(VTS.INSTANCE_ID
, VST.ITEM_INSTANCE_ID)=CSI.INSTANCE_ID
AND VST.PROJECT_ID=PAA.PROJECT_ID (+)
AND VTS.PROJECT_TASK_ID=PAT.TASK_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'