View: AHL_MM_WRK_ORDER_RESERVTN_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:
Implementation/DBA Data:	APPS.AHL_MM_WRK_ORDER_RESERVTN_V

View Text

SELECT AWO.WORKORDER_ID
, AWO.WIP_ENTITY_ID
, AWO.WORKORDER_NAME
, AWO.STATUS_CODE
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE UPPER(LOOKUP_TYPE) = 'AHL_JOB_STATUS'
AND UPPER(LOOKUP_CODE) = UPPER(AWO.STATUS_CODE) ) STATUS_MEANING
, CSI.INVENTORY_ITEM_ID
, CSI.INSTANCE_ID ITEM_INSTANCE_ID
, CSI.INSTANCE_NUMBER INSTANCE_NUMBER
, (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = CSI.INVENTORY_ITEM_ID
AND ROWNUM <2 ) ITEM
, HZP.PARTY_NAME ITEM_OWNER
, CSI.SERIAL_NUMBER
, CSI.LOT_NUMBER
, CSIN.INCIDENT_ID NON_ROUTINE_ID
, CSIN.INCIDENT_NUMBER NON_ROUTINE_NUMBER
, AUE.UNIT_EFFECTIVITY_ID
, HOU.NAME ORG_NAME
, VST.ORGANIZATION_ID
, AWR.WORKORDER_NAME RESERVING_WORK_ORDER_NUM
, RES.RESERVATION_ID RESERVATION_ID
, WDJR.SCHEDULED_START_DATE WORK_ORDER_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE AVAILABLE_DATE
, AHL_CMP_UTIL_PKG.GET_RPR_BATCH_FOR_INST (CSI.INSTANCE_ID
, VST.ORGANIZATION_ID) REPAIR_BATCH_NAME
FROM CSI_ITEM_INSTANCES CSI
, AHL_WORKORDERS AWO
, -- FOR SUPPLY WO DETAILS AHL_WORKORDERS AWR
, -- FOR RESERVATION WO DETAILS AHL_VISITS_VL VST
, AHL_VISIT_TASKS_VL VTS
, CS_INCIDENTS_ALL_B CSIN
, AHL_UNIT_EFFECTIVITIES_B AUE
, HR_ORGANIZATION_UNITS HOU
, HZ_PARTIES HZP
, WIP_DISCRETE_JOBS WDJ
, -- FOR SUPPLY WO DETAILS WIP_DISCRETE_JOBS WDJR
, -- FOR RESERVATION WO DETAILS MTL_RESERVATIONS RES
WHERE AWO.MASTER_WORKORDER_FLAG = 'N'
AND AWO.STATUS_CODE NOT IN (7
, 22
, 17
, 12
, 18
, 21
, 4
, 5)---- CANCELLED
, DELETED
, DRAFT
, CLOSED
, DEFFERED
, PENDING DEFER/CANCEL APPROVAL
, COMPLETE
, COMPLETE NO-CHARGE -- FOR ITEM OWNER
AND CSI.OWNER_PARTY_SOURCE_TABLE = 'HZ_PARTIES'
AND CSI.OWNER_PARTY_ID = HZP.PARTY_ID -- PARTY ID WILL ALWAYS BE PRESENT FOR EACH INSTANCE
AND AWO.VISIT_TASK_ID = VTS.VISIT_TASK_ID
AND VST.VISIT_ID = VTS.VISIT_ID
AND NVL(VTS.INSTANCE_ID
, VST.ITEM_INSTANCE_ID) = CSI.INSTANCE_ID -- FOR ORGANIZATION NAME
AND VST.ORGANIZATION_ID = HOU.ORGANIZATION_ID -- RETURN TO SUPPLY MUST BE Y
AND UPPER(VTS.RETURN_TO_SUPPLY_FLAG) = UPPER('Y') -- FOR NR DETAILS
AND VTS.SERVICE_REQUEST_ID = CSIN.INCIDENT_ID(+)
AND CSIN.INCIDENT_ID = AUE.CS_INCIDENT_ID(+)
AND AWO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND AWR.WIP_ENTITY_ID = WDJR.WIP_ENTITY_ID(+)
AND RES.SUPPLY_SOURCE_HEADER_ID(+) = AWO.WIP_ENTITY_ID
AND RES.DEMAND_SOURCE_HEADER_ID = AWR.WIP_ENTITY_ID(+)
AND RES.SUPPLY_SOURCE_TYPE_ID (+) = 5
AND RES.DEMAND_SOURCE_TYPE_ID (+) = 5