Scribd
Upload
2K views

Query To Find The On Hand, Avaialble To Transact & Available To Reserve Quantities

The document describes how to write a wrapper function to retrieve on hand, available to transact, and available to reserve quantities from the EBS inv_quantity_tree_pub package. It defines an object type and function to return the quantity details. The function calls the package to get the quantities and returns them. A sample SQL is provided to call the function and return the quantities for an item.

Uploaded by

pksrikanth1
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2K views

Query To Find The On Hand, Avaialble To Transact & Available To Reserve Quantities

The document describes how to write a wrapper function to retrieve on hand, available to transact, and available to reserve quantities from the EBS inv_quantity_tree_pub package. It defines an object type and function to return the quantity details. The function calls the package to get the quantities and returns them. A sample SQL is provided to call the function and return the quantities for an item.

Uploaded by

pksrikanth1
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

QUERY TO FIND THE ON HAND , AVAIALBLE TO TRANSACT &

AVAILABLE TO RESERVE QUANTITIES

The EBS On Hand & Avaialble to Transact & Available to Reserve Quantities are caluclated
based on the EBS package inv_quantity_tree_pub.

We should write a wrapper code as below to call this in a sql to get the Quantities. 

--Object Definition --

CREATE OR REPLACE TYPE ONHAND_QTY_DETAILS_OBJ


AS OBJECT
(
    ONHAND_QTY                    NUMBER,
    RESERVABLE_QTY_ONHAND        NUMBER,
    RESERVED_QTY                NUMBER,
    SUGGESTED_QTY                NUMBER,
    AVAILABLE_TO_TRANSACT_QTY    NUMBER,
    AVAILABLE_TO_RESERVE_QTY    NUMBER
);
/

-- Funtion Definition --
CREATE OR REPLACE FUNCTION oh_att_atr(z_inv_item_id   IN NUMBER,
                                         z_inv_org_id    IN NUMBER,
                                         z_subinv_code   IN VARCHAR2,
                                         z_lot_number    IN VARCHAR2,
                                         z_LPN_ID        IN NUMBER,
                                         z_locator_id    IN NUMBER
                                         )
      RETURN ONHAND_QTY_DETAILS_OBJ
as
V_ONHAND_QTY_DETAILS_OBJ ONHAND_QTY_DETAILS_OBJ :=
ONHAND_QTY_DETAILS_OBJ(0,0,0,0,0,0);
  
      l_return_status     VARCHAR2 (50);
      l_msg_count         VARCHAR2 (50);
      l_msg_data          VARCHAR2 (50);
      l_qoh               NUMBER := 0;
      l_rqoh              NUMBER := 0;
      l_atr               NUMBER := 0;
      l_att               NUMBER := 0;
      l_qr                NUMBER := 0;
      l_qs                NUMBER := 0;
      l_days_of_inv_use   NUMBER := 0;
      l_overstock_qty     NUMBER := 0;
      l_qty_details       VARCHAR2 (200) := NULL;
      l_daily_usage       NUMBER :=0;
      e_qoh_exception     EXCEPTION;
      g_error_message     VARCHAR2(250);
      lv_lot_control BOOLEAN:=FALSE;
      lv_lot_exp_date DATE :=NULL;
   BEGIN
      g_error_message := NULL;
      IF z_lot_number IS NOT NULL
      THEN
        lv_lot_control := TRUE;
        lv_lot_exp_date := SYSDATE;
      END IF;

      BEGIN
         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         => l_return_status,
            x_msg_count             => l_msg_count,
            x_msg_data              => l_msg_data,
            p_organization_id       => z_inv_org_id,
            p_inventory_item_id     => z_inv_item_id,
            p_tree_mode             => apps.inv_quantity_tree_pub.g_transaction_mode,
            p_is_revision_control   => FALSE,
            p_is_lot_control        => lv_lot_control,
            p_is_serial_control     => FALSE,
            p_revision              => NULL,          
            p_lot_number            => z_lot_number,  
            p_lot_expiration_date   => lv_lot_exp_date,
            p_subinventory_code     => z_subinv_code, 
            p_locator_id            => z_locator_id,  
            p_onhand_source         => 3,
            x_qoh                   => l_qoh,         
            x_rqoh                  => l_rqoh,  
            x_qr                    => l_qr,
            x_qs                    => l_qs,
            x_att                   => l_att,   
            x_atr                   => l_atr,   
            p_lpn_id                => z_LPN_ID
            );

        V_ONHAND_QTY_DETAILS_OBJ.ONHAND_QTY    := l_qoh;


        V_ONHAND_QTY_DETAILS_OBJ.RESERVABLE_QTY_ONHAND := l_rqoh;
        V_ONHAND_QTY_DETAILS_OBJ.RESERVED_QTY := l_qr;
        V_ONHAND_QTY_DETAILS_OBJ.SUGGESTED_QTY := l_qs;
        V_ONHAND_QTY_DETAILS_OBJ.AVAILABLE_TO_TRANSACT_QTY := l_att;
        V_ONHAND_QTY_DETAILS_OBJ.AVAILABLE_TO_RESERVE_QTY := l_atr;

         IF (l_return_status <> 'S') THEN


            RAISE e_qoh_exception;
         END IF ;

      EXCEPTION
         WHEN OTHERS
         THEN
            g_error_message :=
                  'WHEN OTHERS Exception while running the onhand quantity API for item id: '
               || z_inv_item_id
               || ' and organization id: '
               || z_inv_org_id;
            g_error_message :=
               g_error_message || ' - ' || SUBSTR (SQLERRM, 1, 200);
            FND_FILE.PUT_LINE (FND_FILE.LOG, g_error_message);
            RETURN NULL;
      END;
      IF (l_return_status = 'S') THEN
          RETURN V_ONHAND_QTY_DETAILS_OBJ;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         g_error_message :=
               'WHEN OTHERS Exception while getting item quantity details for item id: '
            || z_inv_item_id
            || ' and organization id: '
            || z_inv_org_id;
         g_error_message :=
            g_error_message || ' - ' || SUBSTR (SQLERRM, 1, 200);
         FND_FILE.PUT_LINE (FND_FILE.LOG, g_error_message);
         RETURN NULL;
   END oh_att_atr;
/

After defining the wrapper code and function we can call the function in sql to get the
ON Hand, ATR and ATT quantities as below.

  select
inventory_item_id,item_name,primary_uom_code,organization_id,subinventory_code,lot_nu
mber,lpn_id
,locator_id,
   (oh_att_atr (inventory_item_id,
                                    organization_id,
                                    subinventory_code,
                                    lot_number,
                                    lpn_id,
                                    locator_id).onhand_qty)
             onhand_qty,
         (oh_att_atr (inventory_item_id,
                                    organization_id,
                                    subinventory_code,
                                    lot_number,
                                    lpn_id,
                                    locator_id).available_to_transact_qty)
             att,
         (oh_att_atr (inventory_item_id,
                                    organization_id,
                                    subinventory_code,
                                    lot_number,
                                    lpn_id,
                                    locator_id).available_to_reserve_qty)
             atr       
  from(
  SELECT moqd.inventory_item_id,
         msib.segment1 item_name,
         msib.primary_uom_code,
         moqd.organization_id,
         moqd.subinventory_code,
         moqd.lot_number,
         moqd.lpn_id,
         moqd.locator_id
    FROM mtl_onhand_quantities_detail moqd,
         mtl_system_items_b msib
   WHERE    1=1
         AND moqd.inventory_item_id = msib.inventory_item_id
         AND moqd.organization_id = msib.organization_id
GROUP BY moqd.inventory_item_id,
         moqd.organization_id,
         moqd.subinventory_code,
         moqd.lot_number,
         msib.segment1,
         msib.primary_uom_code,     
         moqd.lpn_id,
          moqd.locator_id);

You might also like