View: AHL_WORKORDERS_OSP_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	Workorders View for Outside Processing module. Excludes Master Workorders.
Implementation/DBA Data:	APPS.AHL_WORKORDERS_OSP_V

View Text

SELECT WO.WORKORDER_ID
, WO.OBJECT_VERSION_NUMBER
, WO.WORKORDER_NAME JOB_NUMBER
, VST.ORGANIZATION_ID
, WDJ.OWNING_DEPARTMENT DEPARTMENT_ID
, BMD.DEPARTMENT_CLASS_CODE
, BMD.DEPARTMENT_CODE
, WO.STATUS_CODE JOB_STATUS_CODE
, WDJ.SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE SCHEDULED_END_DATE
, WO.ACTUAL_START_DATE
, WO.ACTUAL_END_DATE
, VTS.INVENTORY_ITEM_ID
, VTS.INSTANCE_ID ITEM_INSTANCE_ID
, CSI.INSTANCE_NUMBER ITEM_INSTANCE_NUMBER
, CSI.QUANTITY
, MTLI.CONCATENATED_SEGMENTS WO_PART_NUMBER
, MTLI.DESCRIPTION ITEM_DESCRIPTION
, CSI.SERIAL_NUMBER
, CSI.UNIT_OF_MEASURE ITEM_INSTANCE_UOM
, VST.VISIT_ID
, WO.VISIT_TASK_ID
, WO.ROUTE_ID
, ARB.SERVICE_ITEM_ID
, ARB.SERVICE_ITEM_ORG_ID
, MTLS.DESCRIPTION SERVICE_ITEM_DESCRIPTION
, MTLS.CONCATENATED_SEGMENTS SERVICE_ITEM_NUMBER
, MTLS.PRIMARY_UOM_CODE SERVICE_ITEM_UOM
, VST.PROJECT_ID
, PAA.NAME PROJECT_NAME
, VTS.PROJECT_TASK_ID
, PAT.TASK_NAME PROJECT_TASK_NAME
FROM AHL_WORKORDERS WO
, AHL_VISITS_B VST
, AHL_VISIT_TASKS_B VTS
, BOM_DEPARTMENTS BMD
, AHL_ROUTES_B ARB
, CSI_ITEM_INSTANCES CSI
, MTL_SYSTEM_ITEMS_KFV MTLS
, MTL_SYSTEM_ITEMS_KFV MTLI
, PA_PROJECTS_ALL PAA
, PA_TASKS PAT
, WIP_DISCRETE_JOBS WDJ
WHERE WO.VISIT_TASK_ID = VTS.VISIT_TASK_ID
AND WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = VST.ORGANIZATION_ID
AND VST.VISIT_ID = VTS.VISIT_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 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 VST.PROJECT_ID=PAA.PROJECT_ID (+)
AND VTS.PROJECT_TASK_ID=PAT.TASK_ID (+)
AND WO.MASTER_WORKORDER_FLAG = 'N'

Columns