View Suresh Vaishya's profile
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:
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;
Lakki,
Thanks for sharing it.
Suresh
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
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.
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
Post a Comment