VIEW: APPS.MRP_ORG_QUANTITY_V

Source

View Text - Preformatted

SELECT ITEMS.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , MIL.PROJECT_ID , MIL.TASK_ID , SUB.SUB_INVENTORY_CODE , ITEMS.COMPILE_DESIGNATOR , MPO.ORGANIZATION_ID , SUB.NETTING_TYPE , NVL(SUM(MOQ.TRANSACTION_QUANTITY * DECODE(SUB.NETTING_TYPE, 1, 1, 0)), 0) , NVL(SUM(MOQ.TRANSACTION_QUANTITY * DECODE(SUB.NETTING_TYPE, 1, 0, 1)), 0) , NULL FROM MRP_SUB_INVENTORIES SUB, MTL_ITEM_LOCATIONS MIL, MTL_ONHAND_QUANTITIES MOQ, MRP_SYSTEM_ITEMS ITEMS, MRP_PLAN_ORGANIZATIONS_V MPO WHERE (MOQ.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+) AND MOQ.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)) AND SUB.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND SUB.COMPILE_DESIGNATOR = ITEMS.COMPILE_DESIGNATOR AND SUB.SUB_INVENTORY_CODE = MOQ.SUBINVENTORY_CODE AND MOQ.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND MOQ.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND ITEMS.ORGANIZATION_ID = MPO.PLANNED_ORGANIZATION AND ITEMS.COMPILE_DESIGNATOR = MPO.COMPILE_DESIGNATOR AND NVL( ITEMS.EFFECTIVITY_CONTROL, 1) = 1 GROUP BY ITEMS.INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID, MIL.PROJECT_ID, MIL.TASK_ID, SUB.SUB_INVENTORY_CODE, ITEMS.COMPILE_DESIGNATOR, MPO.ORGANIZATION_ID, SUB.NETTING_TYPE, NULL UNION SELECT ITEMS.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , MIL.PROJECT_ID , MIL.TASK_ID , SUB.SUB_INVENTORY_CODE , ITEMS.COMPILE_DESIGNATOR , MPO.ORGANIZATION_ID , SUB.NETTING_TYPE , NVL(SUM(DECODE(SUB.NETTING_TYPE, 1, 1, 0)), 0) , NVL(SUM(DECODE(SUB.NETTING_TYPE, 1, 0, 1)), 0) , MSN.END_ITEM_UNIT_NUMBER FROM MRP_SUB_INVENTORIES SUB , MTL_ITEM_LOCATIONS MIL , MTL_SERIAL_NUMBERS MSN , MRP_SYSTEM_ITEMS ITEMS , MRP_PLAN_ORGANIZATIONS_V MPO WHERE MSN.CURRENT_ORGANIZATION_ID = MIL.ORGANIZATION_ID(+) AND MSN.CURRENT_LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+) AND MSN.CURRENT_STATUS in (3,5) AND SUB.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND SUB.COMPILE_DESIGNATOR = ITEMS.COMPILE_DESIGNATOR AND SUB.SUB_INVENTORY_CODE = MSN.CURRENT_SUBINVENTORY_CODE AND MSN.CURRENT_STATUS IN (3, 5) AND MSN.CURRENT_ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND MSN.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND ITEMS.ORGANIZATION_ID = MPO.PLANNED_ORGANIZATION AND ITEMS.COMPILE_DESIGNATOR = MPO.COMPILE_DESIGNATOR AND NVL( ITEMS.EFFECTIVITY_CONTROL, 1) = 2 GROUP BY ITEMS.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , MIL.PROJECT_ID , MIL.TASK_ID , SUB.SUB_INVENTORY_CODE , ITEMS.COMPILE_DESIGNATOR , MPO.ORGANIZATION_ID , SUB.NETTING_TYPE , MSN.END_ITEM_UNIT_NUMBER

View Text - HTML Formatted

SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, MIL.PROJECT_ID
, MIL.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, MPO.ORGANIZATION_ID
, SUB.NETTING_TYPE
, NVL(SUM(MOQ.TRANSACTION_QUANTITY * DECODE(SUB.NETTING_TYPE
, 1
, 1
, 0))
, 0)
, NVL(SUM(MOQ.TRANSACTION_QUANTITY * DECODE(SUB.NETTING_TYPE
, 1
, 0
, 1))
, 0)
, NULL
FROM MRP_SUB_INVENTORIES SUB
, MTL_ITEM_LOCATIONS MIL
, MTL_ONHAND_QUANTITIES MOQ
, MRP_SYSTEM_ITEMS ITEMS
, MRP_PLAN_ORGANIZATIONS_V MPO
WHERE (MOQ.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MOQ.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+))
AND SUB.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND SUB.COMPILE_DESIGNATOR = ITEMS.COMPILE_DESIGNATOR
AND SUB.SUB_INVENTORY_CODE = MOQ.SUBINVENTORY_CODE
AND MOQ.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND MOQ.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = MPO.PLANNED_ORGANIZATION
AND ITEMS.COMPILE_DESIGNATOR = MPO.COMPILE_DESIGNATOR
AND NVL( ITEMS.EFFECTIVITY_CONTROL
, 1) = 1 GROUP BY ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, MIL.PROJECT_ID
, MIL.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, MPO.ORGANIZATION_ID
, SUB.NETTING_TYPE
, NULL UNION SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, MIL.PROJECT_ID
, MIL.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, MPO.ORGANIZATION_ID
, SUB.NETTING_TYPE
, NVL(SUM(DECODE(SUB.NETTING_TYPE
, 1
, 1
, 0))
, 0)
, NVL(SUM(DECODE(SUB.NETTING_TYPE
, 1
, 0
, 1))
, 0)
, MSN.END_ITEM_UNIT_NUMBER
FROM MRP_SUB_INVENTORIES SUB
, MTL_ITEM_LOCATIONS MIL
, MTL_SERIAL_NUMBERS MSN
, MRP_SYSTEM_ITEMS ITEMS
, MRP_PLAN_ORGANIZATIONS_V MPO
WHERE MSN.CURRENT_ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MSN.CURRENT_LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND MSN.CURRENT_STATUS IN (3
, 5)
AND SUB.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND SUB.COMPILE_DESIGNATOR = ITEMS.COMPILE_DESIGNATOR
AND SUB.SUB_INVENTORY_CODE = MSN.CURRENT_SUBINVENTORY_CODE
AND MSN.CURRENT_STATUS IN (3
, 5)
AND MSN.CURRENT_ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND MSN.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = MPO.PLANNED_ORGANIZATION
AND ITEMS.COMPILE_DESIGNATOR = MPO.COMPILE_DESIGNATOR
AND NVL( ITEMS.EFFECTIVITY_CONTROL
, 1) = 2 GROUP BY ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, MIL.PROJECT_ID
, MIL.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, MPO.ORGANIZATION_ID
, SUB.NETTING_TYPE
, MSN.END_ITEM_UNIT_NUMBER