On Hand API

FUNCTION get_onhand_qty (
p_inventory_item_id NUMBER,
p_org_id NUMBER,
p_subinventory_code VARCHAR2
)
RETURN NUMBER
IS
v_api_version_number NUMBER := 1.0 ;
v_init_msg_lst VARCHAR2 (100);
v_return_status VARCHAR2 (100);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (2000);
v_organization_id NUMBER;
v_inventory_item_id NUMBER;
v_tree_mode INTEGER:=inv_quantity_tree_pub.g_transaction_mode;
v_is_revision_control BOOLEAN default false;
v_is_lot_control BOOLEAN default false;
v_is_serial_control BOOLEAN default false;
v_demand_source_type_id NUMBER;
v_demand_source_header_id NUMBER;
v_demand_source_line_id NUMBER;
v_demand_source_name VARCHAR2 (500);
v_lot_expiration_date DATE;
v_revision VARCHAR2 (100):=null;
v_lot_number VARCHAR2 (100):=null;
v_subinventory_code VARCHAR2 (100);
v_locator_id NUMBER:=null;
v_onhand_source NUMBER:= inv_quantity_tree_pvt.g_all_subs;
v_qoh NUMBER;
v_rqoh NUMBER;
v_qr NUMBER;
v_qs NUMBER;
v_att NUMBER;
v_atr NUMBER;
v_transfer_subinventory_code VARCHAR2 (100);
v_cost_group_id NUMBER;
v_lpn_id NUMBER;
v_transfer_locator_id NUMBER;
v_onhand_qty NUMBER;
BEGIN
setdebug('Calling inv_quantity_tree_pub.query_quantities Procedure..'
);
fnd_client_info.set_org_context (2);
inv_quantity_tree_pub.query_quantities
( p_api_version_number=> v_api_version_number
, p_init_msg_lst => v_init_msg_lst
, x_return_status => v_return_status
, x_msg_count => v_msg_count
, x_msg_data => v_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_inventory_item_id
, p_tree_mode => v_tree_mode
, p_is_revision_control => v_is_revision_control
, p_is_lot_control => v_is_lot_control
, p_is_serial_control => v_is_serial_control
--, p_demand_source_type_id => v_demand_source_type_id
--, p_demand_source_header_id => v_demand_source_header_id
--, p_demand_source_line_id => v_demand_source_line_id
--, p_demand_source_name => v_demand_source_name
--, p_lot_expiration_date => v_lot_expiration_date
, p_revision => v_revision
, p_lot_number => v_lot_number
, p_subinventory_code => p_subinventory_code
, p_locator_id => v_locator_id
, p_onhand_source => v_onhand_source
, x_qoh => v_qoh
, x_rqoh => v_rqoh
, x_qr => v_qr
, x_qs => v_qs
, x_att => v_att
, x_atr => v_atr
--, p_transfer_subinventory_code => v_transfer_subinventory_code
--, p_cost_group_id => v_cost_group_id
--, p_lpn_id => v_lpn_id
--, p_transfer_locator_id => v_transfer_locator_id
);

IF x_msg_count > 0
      THEN
         FOR v_index IN 1 .. x_msg_count
         LOOP
            fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => x_msg_data, p_msg_index_out => v_msg_index_out);
            DBMS_OUTPUT.put_line (x_msg_data);
            END LOOP;
end if;

v_onhand_qty := v_atr;
Return v_onhand_qty;
EXCEPTION
WHEN OTHERS
THEN
setdebug('Error in get_onhand_qty Function '
|| SUBSTR (SQLERRM, 1, 50)
);
RETURN NULL;
END get_onhand_qty;