To Check for the available on_hand for an item in a given organization using inv_quantity_tree_pub API

Inv_Quantity_Tree_Pub API can be used for querying the available Onhand in a given subinventory or organization.

The output of the API inv_quantity_tree_pub.query_quantities will show the Total Available Onhand , Reservations , Suggestions and the Actual Onhand that can be Transacted.

-- R12 - INV - Sample Script to Get Onhand Using INV_Quantity_Tree_PUB API:

DECLARE

l_api_return_status VARCHAR2 (1);
l_qty_oh NUMBER;
l_qty_res_oh NUMBER;
l_qty_res NUMBER;
l_qty_sug NUMBER;
l_qty_att NUMBER;
l_qty_atr NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
v_item VARCHAR2 (250) := '&Item_Num';
v_org VARCHAR2 (10) := '&Org_code';

Cursor c_item_info is

SELECT concatenated_segments item, msi.inventory_item_id,msi.organization_id, mp.organization_code
FROM mtl_system_items_kfv msi, mtl_parameters mp
WHERE concatenated_segments = v_item
AND msi.organization_id = mp.organization_id
AND mp.organization_code = v_org;

BEGIN

inv_quantity_tree_grp.clear_quantity_cache;

DBMS_OUTPUT.put_line ('Transaction Mode');

For i in c_item_info

LOOP

DBMS_OUTPUT.put_line ('Extracting the Onhand For the Item ===========> ' i.item);
DBMS_OUTPUT.put_line ('Extracting the Onhand For the Organization ======> ' i.organization_code);

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 => l_api_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => i.organization_id,
p_inventory_item_id => i.inventory_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 => NULL,
p_locator_id => NULL,
x_qoh => l_qty_oh,
x_rqoh => l_qty_res_oh,
x_qr => l_qty_res,
x_qs => l_qty_sug,
x_att => l_qty_att,
x_atr => l_qty_atr);

DBMS_OUTPUT.put_line ('Quantity on hand ======================> ' TO_CHAR (l_qty_oh));
DBMS_OUTPUT.put_line ('Reservable quantity on hand ===============> ' TO_CHAR (l_qty_res_oh));
DBMS_OUTPUT.put_line ('Quantity reserved =====================> ' TO_CHAR (l_qty_res));
DBMS_OUTPUT.put_line ('Quantity suggested ====================> ' TO_CHAR (l_qty_sug));
DBMS_OUTPUT.put_line ('Quantity Available To Transact ==============> ' TO_CHAR (l_qty_att));
DBMS_OUTPUT.put_line ('Quantity Available To Reserve ==============> ' TO_CHAR (l_qty_atr));

END LOOP;
END;

DBMS OUTPUT:

Transaction Mode
Extracting the Onhand For the Item =========> ELXV350ELL121MH12D^NIPPONSCC
Extracting the Onhand For the Organization ====> A66
Quantity on hand =======================> 3400
Reservable quantity on hand ===============> 3400
Quantity reserved =======================> 1000
Quantity suggested ======================> 0
Quantity Available To Transact==============> 2400
Quantity Available To Reserve ==============> 2400