How to find the Quantity on hand.

How to find the Quantity on hand.

Abstract
This article is how to find the Quantity on hand.

The quantities considered in calculating the
Available quantities are
On hand Quantity --> quantity from mtl_onhand_quantities_details
Allocated Quantity --> mtl_material_transactions_temp
Reserved Quantity --> mtl_reservations

But these table reflects the total of quantity available in the sub inventory without
considering the provision for reserve or allocated quantity.

For Example - Total on hand quantity: 100,
Allocated quantity : 10
and reserved quantity: 20.
let suppose out of 20 reserved quantity, 5 is reserved against the demand source for
which we are performing the transaction right now.
Then the available quantity while transacting the above source is 100-10-20+5 =75.

But in the above tables we will get it as 100 only as not a single unit is went of of the sub inventory.



The relevant form in application is INVMATWB.fmb.
NAVIGATION:
Inventory > On Hand Availability>On-hand quantity --- query the item.



The API, that calculates the available quantities is
inv_quantity_tree_pvt.create_tree.

SET SERVEROUTPUT ON; 

Declare 
--IN parameters 
item_id NUMBER := 28131; 
org_id NUMBER := 110; 
subinv VARCHAR2(20) := 'WH-01'; 
-- 
v_api_return_status VARCHAR2 (1); 
v_qty_oh NUMBER; 
v_qty_res_oh NUMBER; 
v_qty_res NUMBER; 
v_qty_sug NUMBER; 
v_qty_att NUMBER; 
v_qty_atr NUMBER; 
v_msg_count NUMBER; 
v_msg_data VARCHAR2(1000); 

BEGIN 
inv_quantity_tree_grp.clear_quantity_cache; 
apps.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES 
(p_api_version_number => 1.0, 
p_init_msg_lst => apps.fnd_api.g_false, 
x_return_status => v_api_return_status, 
x_msg_count => v_msg_count, 
x_msg_data => v_msg_data, 
p_organization_id => org_id, 
p_inventory_item_id => item_id, 
p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode, 
p_onhand_source => 3, 
p_is_revision_control => FALSE, 
p_is_lot_control => FALSE, 
p_is_serial_control => FALSE, 
p_revision => NULL, 
p_lot_number => NULL, 
p_subinventory_code => subinv, 
p_locator_id => NULL, 
x_qoh => v_qty_oh, 
x_rqoh => v_qty_res_oh, 
x_qr => v_qty_res, 
x_qs => v_qty_sug, 
x_att => v_qty_att, 
x_atr => v_qty_atr); 


DBMS_OUTPUT.PUT_LINE('Onhand Quantity: '|| v_qty_oh); 
DBMS_OUTPUT.PUT_LINE('Available to Transact Quantity: '|| v_qty_att); 
DBMS_OUTPUT.PUT_LINE('Available to Reserve Quantity: '|| v_qty_atr); 
END;