How to Query 'Total Onhand', 'Available To Reserve' & 'Available To Transact' Quantity of an ITEM in ORACLE APPS R21

/*Get ONHAND QUANTITIES through API in Oracle Apps R12 (INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES)*/

CREATE OR replace FUNCTION xx_get_ohqty_api(p_inv_item_id       IN VARCHAR2,

                                            p_org_id            NUMBER,
                                            p_subinventory_code VARCHAR2,
                                            p_lot_number        VARCHAR2,
                                            p_locator_id        NUMBER,
                                            p_qty_type          IN VARCHAR2)
RETURN NUMBER
IS
  x_return_status       VARCHAR2 (50);
  x_msg_count           VARCHAR2 (50);
  x_msg_data            VARCHAR2 (50);
  v_item_id             NUMBER;
  v_organization_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;
  l_qty                 NUMBER;
BEGIN
    SELECT inventory_item_id,
           mp.organization_id
    INTO   v_item_id, v_organization_id
    FROM   mtl_system_items_b msib,
           mtl_parameters mp
    WHERE  msib.inventory_item_id = p_inv_item_id
           AND msib.organization_id = mp.organization_id
           AND msib.organization_id = p_org_id; -- :organization_code;
    v_qoh := NULL;

    v_rqoh := NULL;

    v_atr := NULL;

    v_lot_control_code := FALSE;

    v_serial_control_code := FALSE;

    fnd_client_info.Set_org_context (1);

    inv_quantity_tree_grp.clear_quantity_cache;

    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_organization_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_revision,
    p_lot_number => p_lot_number, 

    p_lot_expiration_date => SYSDATE,
    p_subinventory_code => p_subinventory_code, -- p_subinventory_code,
    p_locator_id => p_locator_id, -- p_locator_id,
    p_onhand_source => 3, 

    x_qoh => v_qoh, -- Quantity on-hand
    x_rqoh => v_rqoh, --reservable quantity on-hand
    x_qr => v_qr, 

    x_qs => v_qs, 

    x_att => v_att, -- available to transact
    x_atr => v_atr -- available to reserve
    );

    IF p_qty_type = 'OHQ' THEN --On Hand qty
      l_qty := v_qoh; --v_QuantityOnhand;
    ELSE
      IF p_qty_type = 'ATR' THEN --Available to Reserve
        l_qty := v_atr;
      ELSE
        IF p_qty_type = 'ATT' THEN --Available to Transact
          l_qty := v_att;
        END IF;
      END IF;
    END IF;

    RETURN l_qty;

    dbms_output.Put_line ('On-Hand Quantity: '|| v_qoh);

    dbms_output.Put_line ('Available to reserve: '|| v_atr);

    dbms_output.Put_line ('Quantity Reserved: '|| v_qr);

    dbms_output.Put_line ('Quantity Suggested: '|| v_qs);

    dbms_output.Put_line ('Available to Transact: '|| v_att);

    dbms_output.Put_line ('Available to Reserve: '|| v_atr);
EXCEPTION
  WHEN OTHERS THEN
             dbms_output.Put_line ('ERROR: '|| SQLERRM);
END xx_get_ohqty_api; 

/*Query to find Onhand Quantity of an ITEM in Oracle APPS R12*/
SELECT moqd.organization_id,
       moqd.inventory_item_id,
       msib.segment1                          item_code,
       moqd.subinventory_code                 ISSUE_SUBINV,
       moqd.revision,
       msib.primary_uom_code,--moqd.TRANSACTION_UOM_CODE,
       moqd.locator_id,
       moqd.lot_number,
       NULL                                   serial_number,
       SUM(moqd.primary_transaction_quantity) onhand_quantity,
       --SUM(moqd.transaction_quantity) onhand_quantity,
       milkfv.concatenated_segments           locators
FROM   apps.mtl_system_items_b msib,
       apps.mtl_onhand_quantities_detail moqd,
       apps.mtl_item_locations_kfv milkfv
WHERE  msib.organization_id = 105
       AND msib.inventory_item_id = moqd.inventory_item_id
       AND msib.organization_id = moqd.organization_id
       AND moqd.subinventory_code = 'S19'
       AND milkfv.inventory_location_id(+) = moqd.locator_id
       AND msib.inventory_item_status_code = 'Active'
       AND msib.mtl_transactions_enabled_flag = 'Y'
--AND msib.segment1 = :p_item
GROUP  BY moqd.organization_id,
          moqd.inventory_item_id,
          msib.segment1,
          moqd.subinventory_code,
          moqd.revision,
          msib.primary_uom_code,--moqd.TRANSACTION_UOM_CODE,
          moqd.locator_id,
          moqd.lot_number,
          milkfv.concatenated_segments
UNION
SELECT moqd.organization_id,
       moqd.inventory_item_id,
       msib.segment1                item_code,
       moqd.subinventory_code,
       moqd.revision,
       msib.primary_uom_code,--moqd.TRANSACTION_UOM_CODE,
       moqd.locator_id,
       moqd.lot_number,
       serial_number,
       1                            onhand_quantity,
       --SUM(moqd.transaction_quantity) onhand_quantity,
       milkfv.concatenated_segments locators
FROM   mtl_system_items_b msib,
       mtl_onhand_quantities_detail moqd,
       mtl_item_locations_kfv milkfv,
       mtl_serial_numbers msn
WHERE  1 = 1
       AND msib.organization_id = 105
       AND msib.inventory_item_id = moqd.inventory_item_id
       AND msib.organization_id = moqd.organization_id
       AND moqd.subinventory_code = 'S19'
       AND milkfv.inventory_location_id(+) = moqd.locator_id
       AND msib.inventory_item_status_code = 'Active'
       AND msib.mtl_transactions_enabled_flag = 'Y'
       AND msn.current_organization_id = moqd.organization_id
       AND moqd.subinventory_code = msn.current_subinventory_code
       AND msn.current_status = 3 --Resides in stores
       AND moqd.inventory_item_id = msn.inventory_item_id
       AND moqd.lot_number = msn.lot_number
GROUP  BY moqd.organization_id,
          moqd.inventory_item_id,
          msib.segment1,
          moqd.subinventory_code,
          moqd.revision,
          msib.primary_uom_code,--moqd.TRANSACTION_UOM_CODE,
          moqd.locator_id,
          moqd.lot_number,
          serial_number,
          milkfv.concatenated_segments; 

P.S.

Available to Reserve = (Quantity in on hand) – (Quantity already reserved for other orders)

i.e.

Available to Reserve = (Total Quantity in Reservable Subinventory) – (Total Quantity of the Item in the MTL_RESERVATIONS table)

Available to Transact = Quantity already reserved and ready to transact

SELECT SUM(transaction_quantity)
INTO   tr_qty
FROM   mtl_onhand_quantities_detail
WHERE  inventory_item_id = :INVENTORY_ITEM_ID
AND    organization_id =:P_ORG_ID ;


SELECT SUM(reservation_quantity)
INTO   tr_qty_b
FROM   mtl_reservations
WHERE  inventory_item_id = :INVENTORY_ITEM_ID
AND    organization_id = :P_ORG_ID ;


SELECT SUM(transaction_quantity)
INTO   tr_qty_c
FROM   mtl_onhand_quantities_detail
WHERE  inventory_item_id = :INVENTORY_ITEM_ID
AND    organization_id = :P_ORG_ID
AND    subinventory_code IN
       (
              SELECT secondary_inventory_name
              FROM   mtl_secondary_inventories
              WHERE  organization_id =:P_ORG_ID
              AND    reservable_type = '2');

Available_to_transact = nvl(tr_qty,0) – nvl(tr_qty_b,0);

Available_to_reserve = nvl(tr_qty,0) – (nvl(tr_qty_b,0)+ nvl(tr_qty_c,0));