Check Onhand quantity levels With PL/SQL

INV_Quantity_Tree_PUB

How does one check onhand quantity levels with PL/SQL?

This script will provide snapshot for stock(material) for Item in an Organization / Subinventory.
The script output will provide details with the following results:

Title	Explanation
Quantity on hand	Onhand Quantity for the Item
Quantity res oh	Reservable Quantity On hand
Quantity res	Quantity reserved
Quantity sug	Quantity Suggested
Quantity ATT	Available To Transact
Quantity ATR	Available To Reserve

Note : Do not enter input parameters in quotes, such as subinventory when prompted 'Stores',
instead enter as Stores.

[@more@]

Create the below script in a form of a SQL file.
Example : OnHand.sql
Enter the Inventory_item_id, Organization_id and the Subinventory_code.

Video - Step thru API call (02:59)

set serveroutput on
prompt Enter Organization_id
accept org_id
prompt Enter Inventory_item_id
accept item_id
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);

BEGIN
apps.inv_quantity_tree_grp.clear_quantity_cache;

dbms_output.put_line('Transaction Mode');
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 => &org_id
, p_inventory_item_id => &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 => '&Subinventory'
, 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('Quantity res oh :'||to_char(l_qty_res_oh));
dbms_output.put_line('Quantity res :'||to_char(l_qty_res));
dbms_output.put_line('Quantity sug :'||to_char(l_qty_sug));
dbms_output.put_line('Quantity ATT :'||to_char(l_qty_att));
dbms_output.put_line('Quantity ATR :'||to_char(l_qty_atr));

end;
/

Example output:

SQL> @onhand
Enter Organization_id
207
Enter Inventory_item_id
163744
old 22: , p_organization_id => &org_id
new 22: , p_organization_id => 207
old 23: , p_inventory_item_id => &item_id
new 23: , p_inventory_item_id => 163744
Enter value for subinventory:
old 31: , p_subinventory_code => '&Subinventory'
new 31: , p_subinventory_code => ''
Transaction Mode
Quantity on hand :2390
Quantity res oh :2390
Quantity res :0
Quantity sug :0
Quantity ATT :2390
Quantity ATR :2390

PL/SQL procedure successfully completed.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13247/viewspace-1056630/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13247/viewspace-1056630/