VIEW: APPS.POR_SRC_SUBINV_LOV_V

Source

View Text - Preformatted

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) ) )

View Text - HTML Formatted

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) ) )