View: MRP_RHX_ONHAND_QUANTITIES_V

View Text

SELECT ITEM_ID
, ORG_ID
, ITEM_NUMBER
, SUBINVENTORY_CODE
, AVAILABILITY_TYPE
, SUM(NETTABLE_QUANTITY)
, SUM(NONNETTABLE_QUANTITY)
, ATTRIBUTE_ALIAS
FROM ( SELECT ITEMS.INVENTORY_ITEM_ID ITEM_ID
, ITEMS.ORGANIZATION_ID ORG_ID
, ITEMS.ITEM_NUMBER ITEM_NUMBER
, MSI.SECONDARY_INVENTORY_NAME SUBINVENTORY_CODE
, MSI.AVAILABILITY_TYPE AVAILABILITY_TYPE
, NVL(MOQ.TRANSACTION_QUANTITY * DECODE(MSI.AVAILABILITY_TYPE
, 1
, 1
, 0)
, 0) NETTABLE_QUANTITY
, NVL(MOQ.TRANSACTION_QUANTITY * DECODE(MSI.AVAILABILITY_TYPE
, 1
, 0
, 1)
, 0) NONNETTABLE_QUANTITY
, DECODE(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_ENABLE_ABP'
, MOQ.ORGANIZATION_ID)
, 'Y'
, RTRIM(LTRIM(SUBSTR(LOT_NUMBER
, DECODE(INSTR(MOQ.LOT_NUMBER
, MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_INV_AA_SEP'
, MOQ.ORGANIZATION_ID))
, 0
, LENGTH(MOQ.LOT_NUMBER)
, INSTR(MOQ.LOT_NUMBER
, MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_INV_AA_SEP'
, MOQ.ORGANIZATION_ID)))+LENGTH(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_INV_AA_SEP'
, MOQ.ORGANIZATION_ID)))))
, 'N'
, NULL) ATTRIBUTE_ALIAS
FROM MTL_SECONDARY_INVENTORIES MSI
, MTL_ONHAND_QUANTITIES MOQ
, MRP_RHX_PLANNED_ITEMS_V ITEMS
WHERE MOQ.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND MOQ.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND MOQ.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MOQ.ORGANIZATION_ID = MSI.ORGANIZATION_ID UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID ITEM_ID
, ITEMS.ORGANIZATION_ID ORG_ID
, ITEMS.ITEM_NUMBER ITEM_NUMBER
, SUB.SECONDARY_INVENTORY_NAME SUBINVENTORY_CODE
, SUB.AVAILABILITY_TYPE AVAILABILITY_TYPE
, NVL(TEMP.PRIMARY_QUANTITY * DECODE(SUB.AVAILABILITY_TYPE
, 1
, 1
, 0)
, 0) NETTABLE_QUANTITY
, NVL(TEMP.PRIMARY_QUANTITY * DECODE(SUB.AVAILABILITY_TYPE
, 1
, 0
, 1)
, 0) NONNETTABLE_QUANTITY
, DECODE(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_ENABLE_ABP'
, TEMP.ORGANIZATION_ID)
, 'Y'
, RTRIM(LTRIM(SUBSTR(LTEMP.LOT_NUMBER
, DECODE(INSTR(LTEMP.LOT_NUMBER
, MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_INV_AA_SEP'
, TEMP.ORGANIZATION_ID))
, 0
, LENGTH(LTEMP.LOT_NUMBER)
, INSTR(LTEMP.LOT_NUMBER
, MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_INV_AA_SEP'
, TEMP.ORGANIZATION_ID)))+LENGTH(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_INV_AA_SEP'
, TEMP.ORGANIZATION_ID)))))
, 'N'
, NULL)
FROM MTL_SECONDARY_INVENTORIES SUB
, MTL_MATERIAL_TRANSACTIONS_TEMP TEMP
, MRP_RHX_PLANNED_ITEMS_V ITEMS
, MTL_TRANSACTION_LOTS_TEMP LTEMP
WHERE SUB.ORGANIZATION_ID = TEMP.ORGANIZATION_ID
AND SUB.SECONDARY_INVENTORY_NAME = TEMP.SUBINVENTORY_CODE
AND TEMP.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND TEMP.POSTING_FLAG = 'Y'
AND TEMP.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND LTEMP.TRANSACTION_TEMP_ID(+) = TEMP.TRANSACTION_TEMP_ID ) GROUP BY ITEM_ID
, ORG_ID
, ITEM_NUMBER
, SUBINVENTORY_CODE
, AVAILABILITY_TYPE
, ATTRIBUTE_ALIAS

Columns