Get On Hand Quantities using API in Oracle Apps

This script can be used to get the below quantities.

1. On-hand Quantity

2. Available to Reserve

3. Quantity Reserved

4. Quantity Suggested

5. Available to Transact

6. Available to Reserve

You can also get the On-hand quantities from the table mtl_onhand_quantities 

Script :

==========================================================

DECLARE

--  

   x_return_status         VARCHAR2 (50);

   x_msg_count             VARCHAR2 (50);

   x_msg_data              VARCHAR2 (50);

   v_item_id               NUMBER;

   v_org_id                NUMBER;

   v_qoh                   NUMBER;

   v_rqoh                  NUMBER;

   v_atr                   NUMBER;

   v_att                   NUMBER;

   v_qr                    NUMBER;

   v_qs                    NUMBER;

   v_lot_control_code      BOOLEAN;

   v_serial_control_code   BOOLEAN;

--

 BEGIN

--

    -- Set the variable values

    v_item_id := '7867';

    v_org_id := 122;

    v_qoh := NULL;

    v_rqoh := NULL;

    v_atr := NULL;

    v_lot_control_code := FALSE;

    v_serial_control_code := FALSE;

--

    -- Set the org context

    fnd_client_info.set_org_context (26);

--

    -- Call API

    inv_quantity_tree_pub.query_quantities

    (p_api_version_number       => 1.0,

     p_init_msg_lst             => 'F',

     x_return_status            => x_return_status,

     x_msg_count                => x_msg_count,

     x_msg_data                 => x_msg_data,

     p_organization_id          => v_org_id,

     p_inventory_item_id        => v_item_id,

     p_tree_mode                => apps.inv_quantity_tree_pub.g_transaction_mode,     

     p_is_revision_control      => FALSE,

     p_is_lot_control           => v_lot_control_code,     

     p_is_serial_control        => v_serial_control_code,

     p_revision                 => NULL,        

     p_lot_number               => NULL,           

     p_lot_expiration_date      => SYSDATE,

     p_subinventory_code        => NULL,    

     p_locator_id               => NULL,     

     p_onhand_source            => 3,

     x_qoh                      => v_qoh,      

     x_rqoh                     => v_rqoh,           

     x_qr                       => v_qr,

     x_qs                       => v_qs,

     x_att                      => v_att,  

     x_atr                      => v_atr    

    );

--

   DBMS_OUTPUT.put_line ('On-Hand Quantity is: ' || v_qoh);

   DBMS_OUTPUT.put_line ('Available to reserve is: ' || v_atr);

   DBMS_OUTPUT.put_line ('Quantity Reserved is: ' || v_qr);

   DBMS_OUTPUT.put_line ('Quantity Suggested is: ' || v_qs);

   DBMS_OUTPUT.put_line ('Available to Transact is: ' || v_att);

   DBMS_OUTPUT.put_line ('Available to Reserve is: ' || v_atr);

--

 EXCEPTION

    WHEN OTHERS

    THEN

       DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);

 END;

========================================================================================