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, WDJ.COMPLETION_SUBINVENTORY VST_SUBINVENTORY, WDJ.COMPLETION_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 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 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, 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 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 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 WDJ.COMPLETION_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' )

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
, WDJ.COMPLETION_SUBINVENTORY VST_SUBINVENTORY
, WDJ.COMPLETION_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 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 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
, 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
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
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 WDJ.COMPLETION_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' )