Finding quantities by lots can be only achieved by setting the p_is_lot_control flag to TRUE along with lot_number else set it to FALSE.
set serveroutput on
DECLARE
x_return_status VARCHAR2 (50);
x_msg_count VARCHAR2 (50);
x_msg_data VARCHAR2 (50);
l_item_id NUMBER;
l_organization_id NUMBER;
l_qty_on_hand NUMBER;
l_res_qty_on_hand NUMBER;
l_avail_to_tnsct NUMBER;
l_avail_to_reserve NUMBER;
l_qty_reserved NUMBER;
l_qty_suggested NUMBER;
l_lot_control_code BOOLEAN;
l_serial_control_code BOOLEAN;
BEGIN
-- Set the org context
FND_GLOBAL.apps_initialize ( user_id => 1, resp_id => 64521, resp_appl_id => 401,security_group_id => 0);
inv_quantity_tree_grp.clear_quantity_cache; -- Clear Quantity cache
-- Set the variable values
l_item_id := 15141562;
l_organization_id := 96;
l_lot_control_code := FALSE; --Only When Lot number is passed TRUE else FALSE
l_serial_control_code := FALSE;
-- Call API
inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0
,p_init_msg_lst => NULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_organization_id => l_organization_id
,p_inventory_item_id => l_item_id
,p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode ,p_is_revision_control => FALSE
,p_is_lot_control => l_lot_control_code-- is_lot_control,
,p_is_serial_control => l_serial_control_code
,p_revision => NULL -- p_revision,
,p_lot_number => NULL -- p_lot_number,
,p_lot_expiration_date => SYSDATE
,p_subinventory_code => NULL -- p_subinventory_code,
,p_locator_id => NULL -- p_locator_id,
--,p_cost_group_id => NULL
--,p_onhand_source => NULL
,x_qoh => l_qty_on_hand -- Quantity on-hand
,x_rqoh => l_res_qty_on_hand --reservable quantity on-hand
,x_qr => l_qty_reserved
,x_qs => l_qty_suggested
,x_att => l_avail_to_tnsct -- available to transact
,x_atr => l_avail_to_reserve-- available to reserve
);
DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || l_qty_on_hand);
DBMS_OUTPUT.put_line ('Quantity Reserved: ' || l_qty_reserved);
DBMS_OUTPUT.put_line ('Quantity Suggested: ' || l_qty_suggested);
DBMS_OUTPUT.put_line ('Available to Transact: ' || l_avail_to_tnsct);
DBMS_OUTPUT.put_line ('Available to Reserve: ' || l_avail_to_reserve);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
END;
