View: POR_SRC_SUBINV_LOV_V

Product:	ICX - Oracle iProcurement
Description:	View for subinventory LOV on the Select Source page.
Implementation/DBA Data:	APPS.POR_SRC_SUBINV_LOV_V

View Text

SELECT MOS.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, MOS.ORGANIZATION_ID
, MOS.ORGANIZATION_NAME
, MOS.SUBINVENTORY_CODE
, UOM2.UNIT_OF_MEASURE_TL
, ROUND(INV_CONVERT.INV_UM_CONVERT(MOS.INVENTORY_ITEM_ID
, 5
, (MOS.TOTAL_QOH-SUM(NVL(MRS.RESERVATION_QUANTITY
, 0)))
, NULL
, NULL
, UOM.UNIT_OF_MEASURE
, NVL(MSI.UNIT_OF_ISSUE
, MSI.PRIMARY_UNIT_OF_MEASURE)))
FROM MTL_ONHAND_SUB_V MOS
, MTL_RESERVATIONS MRS
, MTL_SYSTEM_ITEMS MSI
, MTL_UNITS_OF_MEASURE_VL UOM
, MTL_UNITS_OF_MEASURE_VL UOM2
, MTL_SECONDARY_INVENTORIES MSUB
WHERE MSI.INVENTORY_ITEM_ID = MOS.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MSUB.ORGANIZATION_ID
AND MOS.INVENTORY_ITEM_ID = MRS.INVENTORY_ITEM_ID (+)
AND MOS.ORGANIZATION_ID = MRS.ORGANIZATION_ID (+)
AND MOS.SUBINVENTORY_CODE = MRS.SUBINVENTORY_CODE (+)
AND MOS.PRIMARY_UOM_CODE = UOM.UOM_CODE
AND NVL(MSI.UNIT_OF_ISSUE
, MSI.PRIMARY_UNIT_OF_MEASURE) = UOM2.UNIT_OF_MEASURE
AND MSUB.QUANTITY_TRACKED = 1
AND (TRUNC(SYSDATE) < NVL(MSUB.DISABLE_DATE
, TRUNC(SYSDATE+1)))
AND (MSI.RESTRICT_SUBINVENTORIES_CODE = 2 OR (MSI.RESTRICT_SUBINVENTORIES_CODE = 1
AND EXISTS (SELECT NULL
FROM MTL_ITEM_SUB_INVENTORIES MIS
WHERE MIS.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIS.SECONDARY_INVENTORY = MOS.SUBINVENTORY_CODE) ) ) GROUP BY MOS.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, MSI.UNIT_OF_ISSUE
, MSI.PRIMARY_UNIT_OF_MEASURE
, UOM.UNIT_OF_MEASURE
, MOS.ORGANIZATION_ID
, MOS.ORGANIZATION_NAME
, MOS.SUBINVENTORY_CODE
, MOS.TOTAL_QOH
, UOM2.UNIT_OF_MEASURE_TL UNION SELECT MSI.INVENTORY_ITEM_ID
, MSI2.ORGANIZATION_ID
, MSI.ORGANIZATION_ID
, HOU.NAME
, MSUB.SECONDARY_INVENTORY_NAME
, UOM.UNIT_OF_MEASURE_TL
, 0 AVAIL_QUANTITY
FROM MTL_SECONDARY_INVENTORIES MSUB
, MTL_SYSTEM_ITEMS MSI
, MTL_SYSTEM_ITEMS MSI2
, MTL_UNITS_OF_MEASURE_VL UOM
, HR_ORGANIZATION_UNITS HOU
WHERE MSI.INVENTORY_ITEM_ID = MSI2.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MSUB.ORGANIZATION_ID
AND MSI.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND NVL(MSI2.UNIT_OF_ISSUE
, MSI2.PRIMARY_UNIT_OF_MEASURE) = UOM.UNIT_OF_MEASURE
AND MSUB.QUANTITY_TRACKED = 1
AND (TRUNC(SYSDATE) < NVL(MSUB.DISABLE_DATE
, TRUNC(SYSDATE+1)))
AND NOT EXISTS (SELECT 1
FROM MTL_ONHAND_SUB_V MOS
WHERE MOS.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MOS.SUBINVENTORY_CODE = MSUB.SECONDARY_INVENTORY_NAME
AND MOS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID)
AND (MSI.RESTRICT_SUBINVENTORIES_CODE = 2 OR (MSI.RESTRICT_SUBINVENTORIES_CODE = 1
AND EXISTS (SELECT NULL
FROM MTL_ITEM_SUB_INVENTORIES MIS
WHERE MIS.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIS.SECONDARY_INVENTORY = MSUB.SECONDARY_INVENTORY_NAME) ) )

Columns