eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: ICX_MTL_DEMAND_DETAILS_V

Product:	ICX - Oracle iProcurement
Description:	Item Availability by Sub-inventory View
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT MSI.ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID
, MD.SUBINVENTORY
, ICX_CUSTOMER_PANEL.GET_AR_CODE('YES/NO'
, DECODE(MOS.NET
, 1
, 'Y'
, 'N') )
, ICX_CUSTOMER_PANEL.GET_AR_CODE('YES/NO'
, DECODE(MOS.ATP
, 1
, 'Y'
, 'N') )
, MSI.CONCATENATED_SEGMENTS ITEM
, MSI.DESCRIPTION
, MSI.PRIMARY_UNIT_OF_MEASURE
, MP.ORGANIZATION_CODE
, HOU.NAME
, NVL(MOS.TOTAL_QOH
, 0) ONHAND
, SUM(DECODE(MD.RESERVATION_TYPE
, 1
, NVL(MD.LINE_ITEM_QUANTITY
, 0) - NVL(COMPLETED_QUANTITY
, 0)
, 0)) DEMAND_QUANTITY
, SUM(DECODE(MD.RESERVATION_TYPE
, 2
, NVL(MD.LINE_ITEM_QUANTITY
, 0) - NVL(COMPLETED_QUANTITY
, 0)
, 0)) HARD_QUANTITY
, SUM(DECODE(MD.RESERVATION_TYPE
, 3
, NVL(MD.LINE_ITEM_QUANTITY
, 0) - NVL(COMPLETED_QUANTITY
, 0)
, 0)) SUPPLY_QUANTITY
, NVL(MOS.TOTAL_QOH
, 0) - SUM(DECODE(MD.RESERVATION_TYPE
, 2
, NVL(MD.LINE_ITEM_QUANTITY
, 0) - NVL(COMPLETED_QUANTITY
, 0)
, 0)) ATT_QUANTITY
, 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)) ATP_QUANTITY
FROM HR_ORGANIZATION_UNITS HOU
, MTL_PARAMETERS MP
, ICX_MTL_ONHAND_SUB_V MOS
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_DEMAND MD
WHERE MD.PARENT_DEMAND_ID IS NOT NULL
AND MD.SUBINVENTORY 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 MOS.SUBINVENTORY_CODE(+) = MD.SUBINVENTORY
AND MP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = MSI.ORGANIZATION_ID GROUP BY MSI.ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID
, MD.SUBINVENTORY
, ICX_CUSTOMER_PANEL.GET_AR_CODE('YES/NO'
, DECODE(MOS.NET
, 1
, 'Y'
, 'N'))
, ICX_CUSTOMER_PANEL.GET_AR_CODE('YES/NO'
, DECODE(MOS.ATP
, 1
, 'Y'
, 'N'))
, 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
, MOS.SUBINVENTORY_CODE
, ICX_CUSTOMER_PANEL.GET_AR_CODE('YES/NO'
, DECODE(MOS.NET
, 1
, 'Y'
, 'N'))
, ICX_CUSTOMER_PANEL.GET_AR_CODE('YES/NO'
, DECODE(MOS.ATP
, 1
, 'Y'
, 'N'))
, MSI.CONCATENATED_SEGMENTS ITEM
, MSI.DESCRIPTION
, MSI.PRIMARY_UNIT_OF_MEASURE
, MOS.ORGANIZATION_CODE
, MOS.ORGANIZATION_NAME
, NVL(MOS.TOTAL_QOH
, 0) ONHAND
, 0
, 0
, 0
, 0
, NVL(MOS.TOTAL_QOH
, 0) ATP_QUANTITY
FROM MTL_SYSTEM_ITEMS_KFV MSI
, ICX_MTL_ONHAND_SUB_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.SUBINVENTORY = MOS.SUBINVENTORY_CODE
AND MD.PARENT_DEMAND_ID IS NOT NULL )

Columns

Name
ORGANIZATION_ID
INVENTORY_ITEM_ID
SUBINVENTORY
NETTABLE
INCLUDE_IN_ATP
ITEM
DESCRIPTION
PRIMARY_UNIT_OF_MEASURE
ORGANIZATION_CODE
ORGANIZATION_NAME
ONHAND_QUANTITY
DEMAND_QUANTITY
HARD_RESERVATION_QUANTITY
SUPPLY_RESERVATION_QUANTITY
TRANSACTABLE_QUANTITY
NET_AVAILABLE_QUANTITY