View: AHL_WORKORDERS_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	Workorders View for Production Module Restricted by Operating Unit id. Restriction for masterworkorders and workorders created for costing
Implementation/DBA Data:	APPS.AHL_WORKORDERS_V

View Text

SELECT WO.WORKORDER_ID
, WO.WIP_ENTITY_ID
, WO.OBJECT_VERSION_NUMBER
, WO.WORKORDER_NAME JOB_NUMBER
, WDJ.DESCRIPTION
, VST.ORGANIZATION_ID
, 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')
, VTS.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
, 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
, 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
, 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
, 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
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 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' )

Columns