INV - Sample Script to Get Onhand Quantity Using INV_Quantity_Tree_PUB API

Excellent Repository for Oracle APIs
http://irep.oracle.com/index.html

This example is for a Lot controlled Item. Depending on your requirement, it can be Lot/Serial controlled, or no control. Also, in this example, I am using Subinventory.

DECLARE
x_return_status VARCHAR2 (1);
x_msg_data VARCHAR2 (4000);
x_msg_count NUMBER;
x_qoh NUMBER;
x_rqoh NUMBER;
x_qr NUMBER;
x_qs NUMBER;
x_att NUMBER;
x_atr NUMBER;
x_sqoh NUMBER;
x_srqoh NUMBER;
x_sqr NUMBER;
x_sqs NUMBER;
x_satt NUMBER;
x_sqtr NUMBER;
BEGIN
inv_globals.set_org_id (104);

inv_quantity_tree_pub.clear_quantity_cache;

inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => 107,
p_inventory_item_id => 28459,
p_tree_mode => inv_quantity_tree_pvt.g_transaction_mode,
p_is_revision_control => FALSE,
p_is_lot_control => TRUE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => '123456',
p_subinventory_code => 'PACK_FOAM',
p_locator_id => NULL,
x_qoh => x_qoh,
x_rqoh => x_rqoh,
x_qr => x_qr,
x_qs => x_qs,
x_att => x_att,
x_atr => x_atr);

DBMS_OUTPUT.put_line ('x_return_status = ' ||x_return_status);
DBMS_OUTPUT.put_line ('x_msg_count = ' || x_msg_count);
DBMS_OUTPUT.put_line ('x_qr = ' || x_qr);
DBMS_OUTPUT.put_line ('x_qoh = ' || x_qoh);
DBMS_OUTPUT.put_line ('x_rqoh = ' || x_rqoh);
DBMS_OUTPUT.put_line ('x_qs = ' || x_qs);
DBMS_OUTPUT.put_line ('x_att = ' || x_att);
DBMS_OUTPUT.put_line ('x_atr = ' || x_atr);

END;

Some explanations:
X_QOH NUMBER - quantity on hand
X_RQOH NUMBER - reservable quantity on hand
X_QR NUMBER - quantity reserved
X_QS NUMBER - quantity suggested
X_ATT NUMBER - quantity available to transact
X_ATR NUMBER - quantity available to reserve