Loading

Wednesday, May 26, 2010

GET ONHAND QUANTITIES THROUGH API in Oracle Apps R12 (INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES)

Below script will be useful in getting On hand quantity via API INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES. This script was tested in R12.1.1 instance.


SET SERVEROUTPUT ON;
DECLARE

v_api_return_status  VARCHAR2 (1);
v_qty_oh             NUMBER;
v_qty_res_oh         NUMBER;
v_qty_res            NUMBER;
v_qty_sug            NUMBER;
v_qty_att            NUMBER;
v_qty_atr            NUMBER;
v_msg_count          NUMBER;
v_msg_data           VARCHAR2(1000);
v_inventory_item_id  VARCHAR2(250) := '1234';
v_organization_id    VARCHAR2(10)  := '567';

BEGIN

inv_quantity_tree_grp.clear_quantity_cache;

DBMS_OUTPUT.put_line ('Transaction Mode');
DBMS_OUTPUT.put_line ('Onhand For the Item :'|| v_inventory_item_id );
DBMS_OUTPUT.put_line ('Organization        :'|| v_organization_id);

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       => v_api_return_status,
 x_msg_count           => v_msg_count,
 x_msg_data            => v_msg_data,
 p_organization_id     => v_organization_id,
 p_inventory_item_id   => v_inventory_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   => NULL,
 p_locator_id          => NULL,
 x_qoh                 => v_qty_oh,
 x_rqoh                => v_qty_res_oh,
 x_qr                  => v_qty_res,
 x_qs                  => v_qty_sug,
 x_att                 => v_qty_att,
 x_atr                 => v_qty_atr);

DBMS_OUTPUT.put_line ('on hand Quantity                :'|| v_qty_oh);
DBMS_OUTPUT.put_line ('Reservable quantity on hand     :'|| v_qty_res_oh);
DBMS_OUTPUT.put_line ('Quantity reserved               :'|| v_qty_res);
DBMS_OUTPUT.put_line ('Quantity suggested              :'|| v_qty_sug);
DBMS_OUTPUT.put_line ('Quantity Available To Transact  :'|| v_qty_att);
DBMS_OUTPUT.put_line ('Quantity Available To Reserve   :'|| v_qty_atr);

END;

1 Responses to “GET ONHAND QUANTITIES THROUGH API in Oracle Apps R12 (INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES)”

Rifky Ramadhana said...
July 9, 2010 at 12:44 AM

Thank you very much for your information about onhand quantities. But i want to ask you, Can this API count onhand quantities until lot_number level? Because I have tried to fill p_lot_number and p_subinventory_code but the result always 0. Any suggestions?

-Rifky Ramadhana-


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.