View: WIP_JOB_OPEN_ALLOCATIONS_V

Product:	WIP - Work in Process
Description:	Open allocations for jobs
Implementation/DBA Data:	APPS.WIP_JOB_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
, WDJ.BUILD_SEQUENCE
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 WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WE.PRIMARY_ITEM_ID = MSIK.INVENTORY_ITEM_ID (+)
AND WE.ORGANIZATION_ID = MSIK.ORGANIZATION_ID (+)
AND WE.ENTITY_TYPE = 1
AND WDJ.SCHEDULE_GROUP_ID = WSG.SCHEDULE_GROUP_ID (+)
AND WDJ.ORGANIZATION_ID = WSG.ORGANIZATION_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 WRO.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WP.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WRO.WIP_ENTITY_ID = WO.WIP_ENTITY_ID (+)
AND WRO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM(+)
AND (WO.COUNT_POINT_TYPE IN (1
, 2) OR WO.COUNT_POINT_TYPE IS NULL)
AND ((WRO.SUPPLY_SUBINVENTORY IS NULL
AND WRO.REQUIRED_QUANTITY > NVL(WRO.QUANTITY_ISSUED
, 0) + 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 WRO.REQUIRED_QUANTITY > 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))))
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
, WDJ.BUILD_SEQUENCE

Columns