API for Item On Hand Quantity

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);

   v_organization_id     VARCHAR2 (10);

   v_organization_code   VARCHAR2 (3) := 'C01';

   v_item_code           VARCHAR2 (40) := 'A4298LB';

BEGIN

   inv_quantity_tree_grp.clear_quantity_cache;

   -- To get the inventory Organization Id

   SELECT organization_id

     INTO v_organization_id

     FROM org_organization_definitions

    WHERE organization_code = v_organization_code;

   -- To get the Inventory item id

   SELECT inventory_item_id

     INTO v_inventory_item_id

     FROM mtl_system_items_b

    WHERE segment1 = v_item_code AND organization_id = v_organization_id;

   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 ('Total Quantity            : ' || v_qty_oh);

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

   DBMS_OUTPUT.put_line ('Available To Transact     : ' || v_qty_att);

   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 Reserve : ' || v_qty_atr);

END;