View: ICX_MTL_DEMAND_SUMMARY_V

Product:	ICX - Oracle iProcurement
Description:	Item Availability by Organization View
Implementation/DBA Data:	APPS.ICX_MTL_DEMAND_SUMMARY_V

View Text

SELECT MSI.ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS
, MSI.DESCRIPTION
, MSI.PRIMARY_UNIT_OF_MEASURE
, MP.ORGANIZATION_CODE
, HOU.NAME
, NVL(MOS.TOTAL_QOH
, 0)
, SUM(DECODE(MD.RESERVATION_TYPE
, 1
, NVL(MD.LINE_ITEM_QUANTITY
, 0) - NVL(COMPLETED_QUANTITY
, 0)
, 0))
, SUM(DECODE(MD.RESERVATION_TYPE
, 2
, NVL(MD.LINE_ITEM_QUANTITY
, 0) - NVL(COMPLETED_QUANTITY
, 0)
, 0))
, SUM(DECODE(MD.RESERVATION_TYPE
, 3
, NVL(MD.LINE_ITEM_QUANTITY
, 0) - NVL(COMPLETED_QUANTITY
, 0)
, 0))
, NVL(MOS.TOTAL_QOH
, 0) - SUM(DECODE(MD.RESERVATION_TYPE
, 2
, NVL(MD.LINE_ITEM_QUANTITY
, 0) - NVL(COMPLETED_QUANTITY
, 0)
, 0))
, NVL(MOS.TOTAL_QOH
, 0) - SUM(DECODE(MD.RESERVATION_TYPE
, 1
, NVL(MD.LINE_ITEM_QUANTITY
, 0) - NVL(COMPLETED_QUANTITY
, 0)
, 0)) - SUM(DECODE(MD.RESERVATION_TYPE
, 2
, NVL(MD.LINE_ITEM_QUANTITY
, 0) - NVL(COMPLETED_QUANTITY
, 0)
, 0)) - SUM(DECODE(MD.RESERVATION_TYPE
, 3
, NVL(MD.LINE_ITEM_QUANTITY
, 0) - NVL(COMPLETED_QUANTITY
, 0)
, 0))
FROM HR_ORGANIZATION_UNITS HOU
, MTL_PARAMETERS MP
, MTL_ONHAND_ITEMS_V MOS
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_DEMAND MD
WHERE MD.PARENT_DEMAND_ID IS NOT NULL
AND ((NVL(MD.LINE_ITEM_QUANTITY
, 0) > 0) OR (NVL(MD.COMPLETED_QUANTITY
, 0) > 0))
AND MSI.ORGANIZATION_ID = MD.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MD.INVENTORY_ITEM_ID
AND MOS.ORGANIZATION_ID(+) = MD.ORGANIZATION_ID
AND MOS.INVENTORY_ITEM_ID(+) = MD.INVENTORY_ITEM_ID
AND MP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = MSI.ORGANIZATION_ID GROUP BY MSI.ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS
, MSI.DESCRIPTION
, MSI.PRIMARY_UNIT_OF_MEASURE
, MP.ORGANIZATION_CODE
, HOU.NAME
, NVL(MOS.TOTAL_QOH
, 0) UNION SELECT MSI.ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS
, MSI.DESCRIPTION
, MSI.PRIMARY_UNIT_OF_MEASURE
, MOS.ORGANIZATION_CODE
, MOS.ORGANIZATION_NAME
, NVL(MOS.TOTAL_QOH
, 0)
, 0
, 0
, 0
, 0
, NVL(MOS.TOTAL_QOH
, 0)
FROM MTL_SYSTEM_ITEMS_KFV MSI
, MTL_ONHAND_ITEMS_V MOS
WHERE MSI.ORGANIZATION_ID = MOS.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MOS.INVENTORY_ITEM_ID
AND NOT EXISTS ( SELECT 'X'
FROM MTL_DEMAND MD
WHERE MD.INVENTORY_ITEM_ID = MOS.INVENTORY_ITEM_ID
AND MD.ORGANIZATION_ID = MOS.ORGANIZATION_ID
AND MD.PARENT_DEMAND_ID IS NOT NULL )

Columns