Query to get available quantity in oracle apps

Query to get available quantity in oracle apps

 

In this post , We will discuss bout query to get available quantity in oracle apps. As such this is not achievable through sql query but we can register this below function and then call this function from the sql query to get available quantity in oracle apps.

 

Function to find the available quantity in oracle apps

create or replace function xx_onhand_qty(

p_inv_item_id in varchar2,

p_org_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 (90);

 

 

   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

   );

 

 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;

 --return v_atr;

 

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

 

 

Query to get available quantity in oracle apps

In this query we are using the function xx_onhand_qty to get available quantity in oracle apps.

 

 

SELECT

 msib.segment1 item_code  , moqd.INVENTORY_ITEM_ID,

                moqd.SUBINVENTORY_CODE

,moqd.LOCATOR_ID

 ,SUM(moqd.TRANSACTION_QUANTITY)  onhand_quantity ,

xx_onhand_qty(moqd.INVENTORY_ITEM_ID,

MOQD.ORGANIZATION_ID,

‘ATR’) available_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 = 110

            and msib.INVENTORY_ITEM_ID = moqd.INVENTORY_ITEM_ID

            AND MSIB.ORGANIZATION_ID =MOQD.ORGANIZATION_ID

         and moqd.SUBINVENTORY_CODE = :P_SUBINVENTORY

           AND milkfv.INVENTORY_LOCATION_ID = moqd.LOCATOR_ID

          AND MSIB.SEGMENT1 = :P_ITEM

            GROUP BY MSIB.SEGMENT1,MOQD.SUBINVENTORY_CODE,MILKFV.CONCATENATED_SEGMENTS, moqd.INVENTORY_ITEM_ID, MOQD.ORGANIZATION_ID;