VIEW: APPS.AHL_SRH_WO_HISTORY_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, ORG.NAME, WDJ.OWNING_DEPARTMENT, BMD.DEPARTMENT_CODE, BMD.DESCRIPTION, BMD.DEPARTMENT_CLASS_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'), VTS.INVENTORY_ITEM_ID, VTS.INSTANCE_ID, SUBSTR(AHL_UTILITY_PVT.GET_UNIT_NAME(VST.ITEM_INSTANCE_ID), 1, 80), CSI.INSTANCE_NUMBER, CSI.OWNER_PARTY_ID, CSI.QUANTITY, MTLI.CONCATENATED_SEGMENTS, MTLI.DESCRIPTION, CSI.SERIAL_NUMBER, CSI.UNIT_OF_MEASURE, WDJ.COMPLETION_SUBINVENTORY, WDJ.COMPLETION_LOCATOR_ID, MLC.CONCATENATED_SEGMENTS, VST.VISIT_ID, VST.VISIT_NUMBER, VST.VISIT_NAME, WO.VISIT_TASK_ID, VST.STATUS_CODE, VTS.VISIT_TASK_NUMBER, VTS.VISIT_TASK_NAME, AMRT.MR_HEADER_ID, 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, CSIN.SUMMARY, CSIN.PROBLEM_CODE, CSIN.RESOLUTION_CODE, WDJ.CLASS_CODE, VTS.UNIT_EFFECTIVITY_ID, UE.OBJECT_VERSION_NUMBER, UE.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 FROM AHL_WORKORDERS WO, AHL_VISITS_VL VST, AHL_VISIT_TASKS_VL VTS, AHL_UE_DEFERRAL_DETAILS_V 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, MTL_SYSTEM_ITEMS_KFV MTLI, PA_PROJECTS_ALL PAA, PA_TASKS PAT, AHL_MR_ROUTES_APP_V AMRT, AHL_MR_HEADERS_APP_V 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 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 IN (SELECT ORGANIZATION_ID FROM org_organization_definitions WHERE NVL (operating_unit, NVL (TO_NUMBER (DECODE (SUBSTR (USERENV ('CLIENT_INFO'), 1,10), ' ', NULL, SUBSTR (USERENV ('CLIENT_INFO'), 1, 10))),-99)) = NVL (TO_NUMBER (DECODE (SUBSTR (USERENV ('CLIENT_INFO'),1, 1), ' ', NULL, SUBSTR (USERENV ('CLIENT_INFO'), 1, 10))),-99)) AND VST.ORGANIZATION_ID=MTLI.ORGANIZATION_ID AND VTS.INVENTORY_ITEM_ID=MTLI.INVENTORY_ITEM_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 VTS.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
, ORG.NAME
, WDJ.OWNING_DEPARTMENT
, BMD.DEPARTMENT_CODE
, BMD.DESCRIPTION
, BMD.DEPARTMENT_CLASS_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')
, VTS.INVENTORY_ITEM_ID
, VTS.INSTANCE_ID
, SUBSTR(AHL_UTILITY_PVT.GET_UNIT_NAME(VST.ITEM_INSTANCE_ID)
, 1
, 80)
, CSI.INSTANCE_NUMBER
, CSI.OWNER_PARTY_ID
, CSI.QUANTITY
, MTLI.CONCATENATED_SEGMENTS
, MTLI.DESCRIPTION
, CSI.SERIAL_NUMBER
, CSI.UNIT_OF_MEASURE
, WDJ.COMPLETION_SUBINVENTORY
, WDJ.COMPLETION_LOCATOR_ID
, MLC.CONCATENATED_SEGMENTS
, VST.VISIT_ID
, VST.VISIT_NUMBER
, VST.VISIT_NAME
, WO.VISIT_TASK_ID
, VST.STATUS_CODE
, VTS.VISIT_TASK_NUMBER
, VTS.VISIT_TASK_NAME
, AMRT.MR_HEADER_ID
, 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
, CSIN.SUMMARY
, CSIN.PROBLEM_CODE
, CSIN.RESOLUTION_CODE
, WDJ.CLASS_CODE
, VTS.UNIT_EFFECTIVITY_ID
, UE.OBJECT_VERSION_NUMBER
, UE.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
FROM AHL_WORKORDERS WO
, AHL_VISITS_VL VST
, AHL_VISIT_TASKS_VL VTS
, AHL_UE_DEFERRAL_DETAILS_V 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
, MTL_SYSTEM_ITEMS_KFV MTLI
, PA_PROJECTS_ALL PAA
, PA_TASKS PAT
, AHL_MR_ROUTES_APP_V AMRT
, AHL_MR_HEADERS_APP_V 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
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 IN (SELECT ORGANIZATION_ID
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE NVL (OPERATING_UNIT
, NVL (TO_NUMBER (DECODE (SUBSTR (USERENV ('CLIENT_INFO')
, 1
, 10)
, ' '
, NULL
, SUBSTR (USERENV ('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL (TO_NUMBER (DECODE (SUBSTR (USERENV ('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTR (USERENV ('CLIENT_INFO')
, 1
, 10)))
, -99))
AND VST.ORGANIZATION_ID=MTLI.ORGANIZATION_ID
AND VTS.INVENTORY_ITEM_ID=MTLI.INVENTORY_ITEM_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 VTS.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' )