Get On_Hand Quantities through API

This script can be used to get the below quantities.
1. On-hand Quantity
2. Available to Reserve
3. Quantity Reserved
4. Quantity Suggested
5. Available to Transact
6. Available to Reserve

You can also get the On-hand quantities from the table mtl_onhand_quantities

CREATE OR REPLACE function APPS.XX_TP_GET_ITEMS_TEST(v_organization_id in number,
v_inventory_item_id in number
)

return number

as

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);
--v_inventory_item_id VARCHAR2(250) := '64';
--v_organization_id VARCHAR2(10) := '12';

Cursor c_item_info is

select msb.INVENTORY_ITEM_ID,msb.ORGANIZATION_ID
from
MTL_SYSTEM_ITEMS_B msb
where
msb.ORGANIZATION_ID=nvl(v_organization_id,msb.ORGANIZATION_ID)
and msb.INVENTORY_ITEM_ID=nvl(v_inventory_item_id,msb.INVENTORY_ITEM_ID);

BEGIN


inv_quantity_tree_grp.clear_quantity_cache;


DBMS_OUTPUT.put_line ('Transaction Mode');
DBMS_OUTPUT.put_line ('Onhand For the Item :'|| v_inventory_item_id );
DBMS_OUTPUT.put_line ('Organization :'|| v_organization_id);

For i in c_item_info
LOOP

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 => 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 => APPS.INV_QUANTITY_TREE_PVT.g_all_subs, --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 => 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 ('on hand Quantity :'|| v_qty_oh);
DBMS_OUTPUT.put_line ('Reservable quantity on hand :'|| v_qty_res_oh);
DBMS_OUTPUT.put_line ('Quantity reserved :'|| v_qty_res);
DBMS_OUTPUT.put_line ('Quantity suggested :'|| v_qty_sug);
DBMS_OUTPUT.put_line ('Quantity Available To Transact :'|| v_qty_att);
DBMS_OUTPUT.put_line ('Quantity Available To Reserve :'|| v_qty_atr);

return v_qty_oh;
end loop;

exception
when others then
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;