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@]
instead enter as Stores.
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/