VIEW: APPS.WIP_JOBS_RESERVATIONS_V

Source

View Text - Preformatted

SELECT WDJ.WIP_ENTITY_ID ,WE.WIP_ENTITY_NAME ,WDJ.DESCRIPTION , WDJ.ORGANIZATION_ID , WE.ENTITY_TYPE , WDJ.JOB_TYPE , WDJ.SCHEDULED_START_DATE , WDJ.SCHEDULED_COMPLETION_DATE , WDJ.PRIMARY_ITEM_ID , WDJ.STATUS_TYPE , WDJ.START_QUANTITY ,WDJ.NET_QUANTITY , WDJ.BOM_REVISION , WDJ.COMPLETION_SUBINVENTORY , WDJ.COMPLETION_LOCATOR_ID , WDJ.LOT_NUMBER JOB_LOT_NUMBER , WDJ.PROJECT_ID , WDJ.TASK_ID , WDJ.SERIALIZATION_START_OP FROM WIP_DISCRETE_JOBS WDJ , WIP_ENTITIES WE WHERE WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID AND ((START_QUANTITY - QUANTITY_SCRAPPED - QUANTITY_COMPLETED) > ( SELECT SUM(PRIMARY_QUANTITY) FROM WIP_RESERVATIONS_V WRV WHERE WRV.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID GROUP BY WIP_ENTITY_ID ) OR NOT EXISTS (SELECT 'X' FROM WIP_RESERVATIONS_V WRV WHERE WRV.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID ) OR exists (SELECT mtrl.primary_quantity FROM mtl_txn_request_lines mtrl, wms_license_plate_numbers wlpn WHERE mtrl.organization_id = wdj.organization_id AND mtrl.inventory_item_id = wdj.primary_item_id AND NVL(mtrl.quantity_delivered, 0) = 0 AND mtrl.txn_source_id = wdj.wip_entity_id AND mtrl.lpn_id = wlpn.lpn_id AND wlpn.lpn_context = 2 AND mtrl.line_status <> 5 ) ) AND ((exists (SELECT mtrl.primary_quantity FROM mtl_txn_request_lines mtrl, wms_license_plate_numbers wlpn WHERE mtrl.organization_id = wdj.organization_id AND mtrl.inventory_item_id = wdj.primary_item_id AND NVL(mtrl.quantity_delivered, 0) = 0 AND mtrl.txn_source_id = wdj.wip_entity_id AND mtrl.lpn_id = wlpn.lpn_id AND wlpn.lpn_context = 2 AND mtrl.line_status <> 5 ) and wdj.status_type in (1,3,4,6) ) or WDJ.STATUS_TYPE IN (1,3,6))

View Text - HTML Formatted

SELECT WDJ.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WDJ.DESCRIPTION
, WDJ.ORGANIZATION_ID
, WE.ENTITY_TYPE
, WDJ.JOB_TYPE
, WDJ.SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE
, WDJ.PRIMARY_ITEM_ID
, WDJ.STATUS_TYPE
, WDJ.START_QUANTITY
, WDJ.NET_QUANTITY
, WDJ.BOM_REVISION
, WDJ.COMPLETION_SUBINVENTORY
, WDJ.COMPLETION_LOCATOR_ID
, WDJ.LOT_NUMBER JOB_LOT_NUMBER
, WDJ.PROJECT_ID
, WDJ.TASK_ID
, WDJ.SERIALIZATION_START_OP
FROM WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
WHERE WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND ((START_QUANTITY - QUANTITY_SCRAPPED - QUANTITY_COMPLETED) > ( SELECT SUM(PRIMARY_QUANTITY)
FROM WIP_RESERVATIONS_V WRV
WHERE WRV.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID GROUP BY WIP_ENTITY_ID ) OR NOT EXISTS (SELECT 'X'
FROM WIP_RESERVATIONS_V WRV
WHERE WRV.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID ) OR EXISTS (SELECT MTRL.PRIMARY_QUANTITY
FROM MTL_TXN_REQUEST_LINES MTRL
, WMS_LICENSE_PLATE_NUMBERS WLPN
WHERE MTRL.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND MTRL.INVENTORY_ITEM_ID = WDJ.PRIMARY_ITEM_ID
AND NVL(MTRL.QUANTITY_DELIVERED
, 0) = 0
AND MTRL.TXN_SOURCE_ID = WDJ.WIP_ENTITY_ID
AND MTRL.LPN_ID = WLPN.LPN_ID
AND WLPN.LPN_CONTEXT = 2
AND MTRL.LINE_STATUS <> 5 ) )
AND ((EXISTS (SELECT MTRL.PRIMARY_QUANTITY
FROM MTL_TXN_REQUEST_LINES MTRL
, WMS_LICENSE_PLATE_NUMBERS WLPN
WHERE MTRL.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND MTRL.INVENTORY_ITEM_ID = WDJ.PRIMARY_ITEM_ID
AND NVL(MTRL.QUANTITY_DELIVERED
, 0) = 0
AND MTRL.TXN_SOURCE_ID = WDJ.WIP_ENTITY_ID
AND MTRL.LPN_ID = WLPN.LPN_ID
AND WLPN.LPN_CONTEXT = 2
AND MTRL.LINE_STATUS <> 5 )
AND WDJ.STATUS_TYPE IN (1
, 3
, 4
, 6) ) OR WDJ.STATUS_TYPE IN (1
, 3
, 6))