This INV Inventory Transactions query displays detailed inventory transaction data, including on-hand, inbound, receiving, and reserved quantities, for each item across organizations and subinventories as of today, helping users monitor inventory movement and optimize stock management decisions.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Tracks inventory transactions and quantities to support efficient stock and supply chain management.
IOP.ORGANIZATION_CODE,
IODV.ORGANIZATION_NAME,
ESI.ITEM_NUMBER,
ESI.DESCRIPTION,
IMT.SUBINVENTORY_CODE,
ITL.LOCATOR_NAME,
-- TRANSACTION DETAILS
IMT.TRANSACTION_ID MATERIAL_TRANSACTION_NUMBER,
IMT.TRANSACTION_DATE,
IMT.TRANSACTION_QUANTITY,
IMT.TRANSACTION_UOM,
IMT.CREATION_DATE,
IMT.CREATED_BY,
-- QUANTITIES
NVL(OQD.ONHAND_QTY, 0) ONHAND_QUANTITY,
NVL(RSUP.RECEIVING_QTY, 0) RECEIVING_QUANTITY,
NVL(ISUP.INBOUND_QTY, 0) INBOUND_QUANTITY,
-- AVAILABILITY CALCULATIONS
NVL(OQD.ONHAND_QTY, 0)
- NVL(PENDING_TXN.PENDING_QTY, 0)
- NVL(RES.RESERVED_QTY, 0) "AVAILABLE TO RESERVE",
-- TOTAL QUANTITY CALCULATION
NVL(OQD.ONHAND_QTY, 0)
+ NVL(RSUP.RECEIVING_QTY, 0)
+ NVL(ISUP.INBOUND_QTY, 0) TOTAL_QUANTITY
FROM
EGP_SYSTEM_ITEMS ESI,
INV_ORG_PARAMETERS IOP,
INV_ORGANIZATION_DEFINITIONS_V IODV,
INV_UNITS_OF_MEASURE_B UOMB,
INV_MATERIAL_TXNS IMT,
INV_ITEM_LOCATIONS ITL,
(SELECT INVENTORY_ITEM_ID,ORGANIZATION_ID,SUM(TRANSACTION_QUANTITY) ONHAND_QTY
FROM INV_ONHAND_QUANTITIES_DETAIL
GROUP BY INVENTORY_ITEM_ID, ORGANIZATION_ID) OQD,
(SELECT INVENTORY_ITEM_ID,ORGANIZATION_ID,SUM(TRANSACTION_QUANTITY) PENDING_QTY
FROM INV_MATERIAL_TXNS_TEMP
WHERE TRANSACTION_STATUS = 'PENDING'
GROUP BY INVENTORY_ITEM_ID, ORGANIZATION_ID) PENDING_TXN,
(SELECT INVENTORY_ITEM_ID,ORGANIZATION_ID,SUM(RESERVATION_QUANTITY) RESERVED_QTY
FROM INV_RESERVATIONS
GROUP BY INVENTORY_ITEM_ID, ORGANIZATION_ID) RES,
(SELECT ITEM_ID,TO_ORGANIZATION_ID,SUM(QUANTITY) RECEIVING_QTY
FROM RCV_SUPPLY
GROUP BY ITEM_ID, TO_ORGANIZATION_ID) RSUP,
(SELECT ITEM_ID,TO_ORGANIZATION_ID,SUM(QUANTITY) INBOUND_QTY
FROM INV_SUPPLY
WHERE SUPPLY_TYPE_CODE = 'PO'
GROUP BY ITEM_ID, TO_ORGANIZATION_ID) ISUP
WHERE
ESI.ORGANIZATION_ID = IOP.ORGANIZATION_ID
AND IODV.ORGANIZATION_ID = IOP.ORGANIZATION_ID
AND UOMB.UOM_CODE = ESI.PRIMARY_UOM_CODE
AND IMT.INVENTORY_ITEM_ID = ESI.INVENTORY_ITEM_ID
AND IMT.LOCATOR_ID = ITL.INVENTORY_LOCATION_ID
AND OQD.INVENTORY_ITEM_ID = ESI.INVENTORY_ITEM_ID
AND OQD.ORGANIZATION_ID = IOP.ORGANIZATION_ID
AND PENDING_TXN.INVENTORY_ITEM_ID(+) = ESI.INVENTORY_ITEM_ID
AND PENDING_TXN.ORGANIZATION_ID(+) = IOP.ORGANIZATION_ID
AND RES.INVENTORY_ITEM_ID(+) = ESI.INVENTORY_ITEM_ID
AND RES.ORGANIZATION_ID(+) = IOP.ORGANIZATION_ID
AND RSUP.ITEM_ID(+) = ESI.INVENTORY_ITEM_ID
AND RSUP.TO_ORGANIZATION_ID(+) = IOP.ORGANIZATION_ID
AND ISUP.ITEM_ID(+) = ESI.INVENTORY_ITEM_ID
AND ISUP.TO_ORGANIZATION_ID(+) = IOP.ORGANIZATION_ID
ORDER BY
ESI.ITEM_NUMBER;