Oracle EBS On-Hand vs Material Transactions Reconciliation SQL Generator

-- On-hand quantity versus net material transactions
-- Verify table and column names in your release before running
SELECT msi.segment1 AS item,
       oh.subinventory_code,
       oh.onhand_qty,
       NVL(tx.net_txn_qty, 0) AS net_txn_qty,
       oh.onhand_qty - NVL(tx.net_txn_qty, 0) AS difference
  FROM (SELECT organization_id, inventory_item_id, subinventory_code,
               SUM(transaction_quantity) AS onhand_qty
          FROM mtl_onhand_quantities_detail
         WHERE organization_id = 207
           AND subinventory_code = 'STORES'
         GROUP BY organization_id, inventory_item_id, subinventory_code) oh
  JOIN mtl_system_items_b msi
    ON msi.inventory_item_id = oh.inventory_item_id
   AND msi.organization_id = oh.organization_id
  LEFT JOIN (SELECT organization_id, inventory_item_id, subinventory_code,
                    SUM(transaction_quantity) AS net_txn_qty
               FROM mtl_material_transactions
              WHERE organization_id = 207
                AND subinventory_code = 'STORES'
                AND transaction_date >= TO_DATE('2025-01-01', 'YYYY-MM-DD')
              GROUP BY organization_id, inventory_item_id, subinventory_code) tx
    ON tx.inventory_item_id = oh.inventory_item_id
   AND tx.organization_id = oh.organization_id
   AND tx.subinventory_code = oh.subinventory_code
 WHERE msi.segment1 LIKE 'AS-%'
 ORDER BY ABS(oh.onhand_qty - NVL(tx.net_txn_qty, 0)) DESC;

-- Replace :item_id with the inventory_item_id from the query above
SELECT mmt.transaction_id,
       mmt.transaction_date,
       mmt.transaction_type_id,
       mmt.subinventory_code,
       mmt.transaction_quantity,
       mmt.transaction_uom
  FROM mtl_material_transactions mmt
 WHERE mmt.organization_id = 207
   AND mmt.subinventory_code = 'STORES'
   AND mmt.inventory_item_id = :item_id
 ORDER BY mmt.transaction_date, mmt.transaction_id;