View: ICX_MTL_DEMAND_SUMMARY_V

Product:	INV - Inventory
Description:
Implementation/DBA Data:	APPS.ICX_MTL_DEMAND_SUMMARY_V

View Text

SELECT MSI.ORGANIZATION_ID ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM
, MSI.DESCRIPTION DESCRIPTION
, MSI.PRIMARY_UNIT_OF_MEASURE PRIMARY_UNIT_OF_MEASURE
, MP.ORGANIZATION_CODE ORGANIZATION_CODE
, HOU.NAME ORGANIZATION_NAME
, NVL(MOQD.PRIMARY_TRANSACTION_QUANTITY
, 0) ONHAND_QUANTITY
, (OOL.LINE_ITEM_QUANTITY-OOL.COMPLETED_QUANTITY) DEMAND_QUANTITY
, SUM(DECODE(MR.SUPPLY_SOURCE_TYPE_ID
, 13
, NVL(MR.PRIMARY_RESERVATION_QUANTITY
, 0)
, 0)) HARD_RESERVATION_QUANTITY
, SUM(DECODE(MR.SUPPLY_SOURCE_TYPE_ID
, 5
, NVL(MR.PRIMARY_RESERVATION_QUANTITY
, 0)
, 0)) SUPPLY_RESERVATION_QUANTITY
, NVL(MOQD.PRIMARY_TRANSACTION_QUANTITY
, 0) - SUM(DECODE(MR.SUPPLY_SOURCE_TYPE_ID
, 13
, NVL(MR.PRIMARY_RESERVATION_QUANTITY
, 0)
, 0)) TRANSACTABLE_QUANTITY
, NVL(MOQD.PRIMARY_TRANSACTION_QUANTITY
, 0) - (OOL.LINE_ITEM_QUANTITY-OOL.COMPLETED_QUANTITY)- SUM(DECODE(MR.SUPPLY_SOURCE_TYPE_ID
, 13
, NVL(MR.PRIMARY_RESERVATION_QUANTITY
, 0)
, 0)) - SUM(DECODE(MR.SUPPLY_SOURCE_TYPE_ID
, 5
, NVL(MR.PRIMARY_RESERVATION_QUANTITY
, 0)
, 0)) NET_AVAILABLE_QUANTITY
FROM HR_ORGANIZATION_UNITS HOU
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_RESERVATIONS MR
, (SELECT SUM(NVL(PRIMARY_TRANSACTION_QUANTITY
, 0)) PRIMARY_TRANSACTION_QUANTITY
, ORGANIZATION_ID
, INVENTORY_ITEM_ID
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE IS_CONSIGNED = 2 GROUP BY ORGANIZATION_ID
, INVENTORY_ITEM_ID) MOQD
, (SELECT SHIP_FROM_ORG_ID ORGANIZATION_ID
, INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, SUM(NVL(ORDERED_QUANTITY
, 0)) LINE_ITEM_QUANTITY
, SUM(NVL(DECODE(SHIPPED_QUANTITY
, NULL
, 0
, INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(SHIP_FROM_ORG_ID
, INVENTORY_ITEM_ID
, ORDER_QUANTITY_UOM
, SHIPPED_QUANTITY))
, 0)) COMPLETED_QUANTITY
FROM OE_ORDER_LINES_ALL
WHERE OPEN_FLAG = 'Y' GROUP BY SHIP_FROM_ORG_ID
, INVENTORY_ITEM_ID) OOL
WHERE MSI.ORGANIZATION_ID = MR.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MR.INVENTORY_ITEM_ID
AND MOQD.ORGANIZATION_ID(+) = MR.ORGANIZATION_ID
AND MOQD.INVENTORY_ITEM_ID(+) = MR.INVENTORY_ITEM_ID
AND MP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND OOL.INVENTORY_ITEM_ID = MR.INVENTORY_ITEM_ID (+)
AND OOL.ORGANIZATION_ID = MR.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
, MOQD.PRIMARY_TRANSACTION_QUANTITY
, (OOL.LINE_ITEM_QUANTITY-OOL.COMPLETED_QUANTITY) UNION SELECT MSI.ORGANIZATION_ID ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM
, MSI.DESCRIPTION DESCRIPTION
, MSI.PRIMARY_UNIT_OF_MEASURE PRIMARY_UNIT_OF_MEASURE
, MP.ORGANIZATION_CODE ORGANIZATION_CODE
, HOU.NAME ORGANIZATION_NAME
, SUM(NVL(MOQD.PRIMARY_TRANSACTION_QUANTITY
, 0)) ONHAND_QUANTITY
, 0 DEMAND_QUANTITY
, 0 HARD_RESERVATION_QUANTITY
, 0 SUPPLY_RESERVATION_QUANTITY
, SUM(NVL(MOQD.PRIMARY_TRANSACTION_QUANTITY
, 0)) TRANSACTABLE_QUANTITY
, SUM(NVL(MOQD.PRIMARY_TRANSACTION_QUANTITY
, 0)) NET_AVAILABLE_QUANTITY
FROM MTL_SYSTEM_ITEMS_KFV MSI
, MTL_ONHAND_QUANTITIES_DETAIL MOQD
, MTL_PARAMETERS MP
, HR_ORGANIZATION_UNITS HOU
WHERE MSI.ORGANIZATION_ID = MOQD.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MOQD.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND NOT EXISTS ( SELECT 'X'
FROM MTL_RESERVATIONS MR
WHERE MR.INVENTORY_ITEM_ID = MOQD.INVENTORY_ITEM_ID
AND MR.ORGANIZATION_ID = MOQD.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 UNION SELECT MSI.ORGANIZATION_ID ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM
, MSI.DESCRIPTION DESCRIPTION
, MSI.PRIMARY_UNIT_OF_MEASURE PRIMARY_UNIT_OF_MEASURE
, MP.ORGANIZATION_CODE ORGANIZATION_CODE
, HOU.NAME ORGANIZATION_NAME
, NVL(MOQD.PRIMARY_TRANSACTION_QUANTITY
, 0) ONHAND_QUANTITY
, 0 DEMAND_QUANTITY
, SUM(DECODE(MR.SUPPLY_SOURCE_TYPE_ID
, 13
, NVL(MR.PRIMARY_RESERVATION_QUANTITY
, 0)
, 0)) HARD_RESERVATION_QUANTITY
, SUM(DECODE(MR.SUPPLY_SOURCE_TYPE_ID
, 5
, NVL(MR.PRIMARY_RESERVATION_QUANTITY
, 0)
, 0)) SUPPLY_RESERVATION_QUANTITY
, NVL(MOQD.PRIMARY_TRANSACTION_QUANTITY
, 0) - SUM(DECODE(MR.SUPPLY_SOURCE_TYPE_ID
, 13
, NVL(MR.PRIMARY_RESERVATION_QUANTITY
, 0)
, 0)) TRANSACTABLE_QUANTITY
, NVL(MOQD.PRIMARY_TRANSACTION_QUANTITY
, 0) - SUM(DECODE(MR.SUPPLY_SOURCE_TYPE_ID
, 13
, NVL(MR.PRIMARY_RESERVATION_QUANTITY
, 0)
, 0)) - SUM(DECODE(MR.SUPPLY_SOURCE_TYPE_ID
, 5
, NVL(MR.PRIMARY_RESERVATION_QUANTITY
, 0)
, 0)) NET_AVAILABLE_QUANTITY
FROM MTL_SYSTEM_ITEMS_KFV MSI
, MTL_PARAMETERS MP
, HR_ORGANIZATION_UNITS HOU
, MTL_RESERVATIONS MR
, (SELECT SUM(NVL(PRIMARY_TRANSACTION_QUANTITY
, 0)) PRIMARY_TRANSACTION_QUANTITY
, ORGANIZATION_ID
, INVENTORY_ITEM_ID
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE IS_CONSIGNED = 2 GROUP BY ORGANIZATION_ID
, INVENTORY_ITEM_ID) MOQD
WHERE MSI.ORGANIZATION_ID = MOQD.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MOQD.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND MR.ORGANIZATION_ID = MOQD.ORGANIZATION_ID
AND MR.INVENTORY_ITEM_ID = MOQD.INVENTORY_ITEM_ID
AND NOT EXISTS (SELECT 'X'
FROM OE_ORDER_LINES_ALL OOL
WHERE OOL.INVENTORY_ITEM_ID = MOQD.INVENTORY_ITEM_ID
AND OOL.SHIP_FROM_ORG_ID = MOQD.ORGANIZATION_ID
AND OPEN_FLAG = 'Y') GROUP BY MSI.ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS
, MSI.DESCRIPTION
, MSI.PRIMARY_UNIT_OF_MEASURE
, MP.ORGANIZATION_CODE
, HOU.NAME
, MOQD.PRIMARY_TRANSACTION_QUANTITY

Columns