View: WIP_LOTJOB_OPEN_ALLOCATIONS_V

Product:	WIP - Work in Process
Description:	Open allocations for lot-based jobs
Implementation/DBA Data:	APPS.WIP_LOTJOB_OPEN_ALLOCATIONS_V

View Text

SELECT DISTINCT WE.WIP_ENTITY_NAME
, WE.ORGANIZATION_ID
, WE.WIP_ENTITY_ID
, WDJ.DESCRIPTION JOB_DESCRIPTION
, MSIK.CONCATENATED_SEGMENTS PRIMARY_ITEM
, MSIK.DESCRIPTION PRIMARY_ITEM_DESCRIPTION
, WE.PRIMARY_ITEM_ID
, WDJ.STATUS_TYPE STATUS_TYPE
, ML.MEANING STATUS
, MIN(WRO.DATE_REQUIRED) FIRST_OPEN_REQUIREMENTS_DATE
, WDJ.SCHEDULED_START_DATE START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE COMPLETION_DATE
, WDJ.END_ITEM_UNIT_NUMBER
, WDJ.PROJECT_ID
, WDJ.TASK_ID
, WL.LINE_CODE
, WSG.SCHEDULE_GROUP_NAME
FROM WIP_ENTITIES WE
, MTL_SYSTEM_ITEMS_KFV MSIK
, WIP_DISCRETE_JOBS WDJ
, WIP_REQUIREMENT_OPERATIONS WRO
, MFG_LOOKUPS ML
, WIP_PARAMETERS WP
, WIP_LINES WL
, WIP_OPERATIONS WO
, WIP_SCHEDULE_GROUPS WSG
WHERE WO.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WO.OPERATION_SEQ_NUM = WRO.OPERATION_SEQ_NUM
AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WE.ENTITY_TYPE = 5
AND WDJ.SCHEDULE_GROUP_ID = WSG.SCHEDULE_GROUP_ID (+)
AND WDJ.ORGANIZATION_ID = WSG.ORGANIZATION_ID (+)
AND WE.PRIMARY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND WDJ.STATUS_TYPE = ML.LOOKUP_CODE
AND WDJ.STATUS_TYPE IN (3
, 4)
AND WDJ.LINE_ID = WL.LINE_ID (+)
AND ML.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND MSIK.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WRO.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WP.ORGANIZATION_ID = WE.ORGANIZATION_ID /* REPLACED WRO.QUANTITY_ALLOCATED:
AND (WO.QUANTITY_IN_QUEUE + QUANTITY_RUNNING + WO.QUANTITY_WAITING_TO_MOVE + WO.QUANTITY_REJECTED + WO.QUANTITY_SCRAPPED + WO.QUANTITY_COMPLETED) * WRO.QUANTITY_PER_ASSEMBLY > WRO.QUANTITY_ISSUED + NVL(QUANTITY_ALLOCATED
, 0) WITH THIS: */
AND ((WRO.SUPPLY_SUBINVENTORY IS NULL
AND (WO.QUANTITY_IN_QUEUE + QUANTITY_RUNNING + WO.QUANTITY_WAITING_TO_MOVE + WO.QUANTITY_REJECTED + WO.QUANTITY_SCRAPPED + WO.QUANTITY_COMPLETED) * WRO.QUANTITY_PER_ASSEMBLY > WRO.QUANTITY_ISSUED + NVL( (SELECT SUM(NVL(MTRL.QUANTITY
, 0)-NVL(MTRL.QUANTITY_DELIVERED
, 0))
FROM MTL_TXN_REQUEST_LINES MTRL
WHERE MTRL.TXN_SOURCE_ID = WRO.WIP_ENTITY_ID
AND MTRL.TXN_SOURCE_LINE_ID = WRO.OPERATION_SEQ_NUM
AND MTRL.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MTRL.LINE_STATUS IN 7
AND MTRL.TRANSACTION_TYPE_ID = 35 GROUP BY MTRL.ORGANIZATION_ID
, MTRL.TXN_SOURCE_ID
, MTRL.TXN_SOURCE_LINE_ID
, MTRL.INVENTORY_ITEM_ID)
, 0)) OR (WRO.SUPPLY_SUBINVENTORY IS NOT NULL
AND (WO.QUANTITY_IN_QUEUE + QUANTITY_RUNNING + WO.QUANTITY_WAITING_TO_MOVE + WO.QUANTITY_REJECTED + WO.QUANTITY_SCRAPPED + WO.QUANTITY_COMPLETED) * WRO.QUANTITY_PER_ASSEMBLY > GREATEST(NVL(WRO.QUANTITY_ISSUED
, 0)
, NVL( (SELECT SUM(NVL(MTRL.QUANTITY
, 0))
FROM MTL_TXN_REQUEST_LINES MTRL
WHERE MTRL.TXN_SOURCE_ID = WRO.WIP_ENTITY_ID
AND MTRL.TXN_SOURCE_LINE_ID = WRO.OPERATION_SEQ_NUM
AND MTRL.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MTRL.LINE_STATUS IN (5
, 7)
AND MTRL.TRANSACTION_TYPE_ID = 51 GROUP BY MTRL.ORGANIZATION_ID
, MTRL.TXN_SOURCE_ID
, MTRL.TXN_SOURCE_LINE_ID
, MTRL.INVENTORY_ITEM_ID)
, 0)))) /* END REPLACEMENT*/
AND ( ( WP.ALLOCATE_BACKFLUSH_COMPONENTS = 'Y'
AND WRO.WIP_SUPPLY_TYPE IN (1
, 2
, 3) ) OR ( ( WP.ALLOCATE_BACKFLUSH_COMPONENTS = 'N' OR WP.ALLOCATE_BACKFLUSH_COMPONENTS IS NULL )
AND WRO.WIP_SUPPLY_TYPE = 1 ) ) GROUP BY WE.WIP_ENTITY_NAME
, WE.ORGANIZATION_ID
, WE.WIP_ENTITY_ID
, WDJ.DESCRIPTION
, MSIK.CONCATENATED_SEGMENTS
, MSIK.DESCRIPTION
, WE.PRIMARY_ITEM_ID
, WDJ.STATUS_TYPE
, ML.MEANING
, WDJ.SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE
, WDJ.END_ITEM_UNIT_NUMBER
, WDJ.PROJECT_ID
, WDJ.TASK_ID
, WL.LINE_CODE
, WSG.SCHEDULE_GROUP_NAME

Columns