, , , , , ,

How to find INV Onhand Quantity at given date?

–The query inputs the Item ID, organization ID and date.

  SELECT   SUM (target_qty), item_id
    FROM   (  SELECT   moqv.subinventory_code subinv,
                       moqv.inventory_item_id item_id,
                       SUM (transaction_quantity) target_qty
                FROM   mtl_onhand_qty_cost_v moqv
               WHERE   moqv.organization_id = :org_id
                       AND moqv.inventory_item_id = :item_id
            GROUP BY   moqv.subinventory_code,
              SELECT   mmt.subinventory_code subinv,
                       mmt.inventory_item_id item_id,
                       -SUM (primary_quantity) target_qty
                FROM   mtl_material_transactions mmt, mtl_txn_source_types mtst
               WHERE   mmt.organization_id = :org_id
                       AND transaction_date >= TO_DATE (:hist_date) + 1
                       AND mmt.transaction_source_type_id =
                       AND mmt.inventory_item_id = :item_id
            GROUP BY   mmt.subinventory_code, mmt.inventory_item_id) oq
GROUP BY   oq.item_id
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply