Saturday, August 7, 2010

Onhand Quantity on any Historical Dates

Now here is another requirement. There is a need to get onhand quantity of an item in a manufacturing organization at any given date.
Oracle has provided with a report that gives this information. The name of concurrent program isTransaction historical summary

But has that solved all our problem, what if there is a requirement and we need to use a query in a report to do some calculation or have some logic based upon historical value.
Below is the query that can help in getting 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
                 , moqv.inventory_item_id
                 , moqv.item_cost
          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

Note: The query is not fully tested, if found any problem please comment with issue.

No comments:

Post a Comment