Search This Blog

Wednesday, January 30, 2008

Onhand Quantity on any historical Date

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 is Transaction 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
UNION
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 =
mtst.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.

5 Comments:

Lakki Reddy Sreehari Reddy said...

I used follwing code to findoud out onhand qty in one of my report

FUNCTION CF_LAST_BALFormula RETURN NUMBER IS
v_onhand_qty NUMBER;
v_last_qty NUMBER;
v_target_qty NUMBER;

BEGIN

SELECT SUM(transaction_quantity)
INTO v_onhand_qty
FROM mtl_onhand_quantities_detail
WHERE inventory_item_id = :inventory_item_id
AND organization_id = :organization_id
AND subinventory_code = :Subinventory;

SELECT NVL(SUM(Transaction_quantity),0)
INTO v_last_qty
FROM mtl_material_transactions
WHERE inventory_item_id = :inventory_item_id
AND organization_id = :organization_id
AND subinventory_code = :Subinventory
AND trunc(creation_date)>= :p_last_date;

v_target_qty:=(v_onhand_qty)-(v_last_qty);

RETURN( v_target_qty);

EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;

Suresh Vaishya said...

Lakki,
Thanks for sharing it.
Suresh

Sid said...

We are having a problem with the number of rows that get created in the MOQD table because we have a very high number of small quantity returns for a given SKU.
This is causing a huge impact on the Pick Release process, as the number of SELECT FOR UPDATE locks in the archive log is exponential, we are having archive logs upto 750 Gigs for a database that is only 200 Gigs.

Any input would be highly appreciated.

Thanks, Siddharth

Deepali said...

I was just browsing on the net and came across this article... There is one query in the article and another one in the comments section; I am doubtful about both those queries. We need to have some filter on the MMT table to fetch only those records which Issue out ( subtract) the inventory. Can writer throw light on this doubt.

Deepak Sharma said...

SELECT mmt.inventory_item_id, msib.segment1 item,
SUM (mmt.transaction_quantity) quantity_onhand
FROM mtl_material_transactions mmt, mtl_system_items_b msib
WHERE mmt.transaction_action_id NOT IN (24, 30)
AND (mmt.organization_id = :organization_id)
AND (mmt.inventory_item_id = NVL (:p_item_id, mmt.inventory_item_id))
AND (mmt.transaction_date >= :p_date)
AND (mmt.logical_transaction = 2 OR mmt.logical_transaction IS NULL)
AND msib.organization_id = mmt.organization_id
AND msib.inventory_item_id = mmt.inventory_item_id
GROUP BY mmt.inventory_item_id, msib.segment1

Copyright (c) All rights reserved. Presented by Suresh Vaishya