On-Hand quantity details as per Oracle Form

Below is the PL/SQL, which gives you the On-hand Quantity details as per oracle form.

Oracle form show details like On-Hand Quantity, Available to reserve, Quantity Reserved,Quantity Suggested, Available to Transact and Available to Reserve.

All These details can be fetched using API => inv_quantity_tree_pub.query_quantities

****************************************************************************
DECLARE
   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;
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       segment1 = :item_number
            AND msib.organization_id = mp.organization_id
            AND mp.organization_code = :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_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                        => NULL,                        -- p_lot_number,
      p_lot_expiration_date            => SYSDATE,
      p_subinventory_code            => NULL,                 -- p_subinventory_code,
      p_locator_id                         => NULL,                        -- 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
   );


   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;


****************************************************************************


Ur's
AmarAlam