View: MRP_ORG_QUANTITY_DET_V

Product:	MRP - Master Scheduling/MRP
Description:	On hand quantities view
Implementation/DBA Data:	APPS.MRP_ORG_QUANTITY_DET_V

View Text

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.PRIMARY_TRANSACTION_QUANTITY * DECODE(SUB.NETTING_TYPE
, 1
, 1
, 0))
, 0)
, NVL(SUM(MOQ.PRIMARY_TRANSACTION_QUANTITY * DECODE(SUB.NETTING_TYPE
, 1
, 0
, 1))
, 0)
, NULL
FROM MRP_SUB_INVENTORIES SUB
, MTL_ITEM_LOCATIONS MIL
, MTL_ONHAND_QUANTITIES_DETAIL 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

Columns