Scribd Logo
Upload

Welcome to Scribd!

  • 2K views

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

    Uploaded by

    pksrikanth1
    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.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

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

    Uploaded by

    pksrikanth1
    2K views4 pages
    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.

    Original Description:

    SQL query to find On-Hand Inventory - Oracle Apps

    Original Title

    On-Hand SQL query

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    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.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    2K views4 pages

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

    Uploaded by

    pksrikanth1
    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.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 4
    At a glance
    Powered by AI
    The document describes how to write a wrapper code and function to retrieve ON Hand, Available to Transact and Available to Reserve quantities from Oracle E-Business Suite using the inv_quantity_tree_pub package.

    The wrapper code and function oh_att_atr are defined to call the inv_quantity_tree_pub package and return the quantity details in an object. The function takes the item, organization and locator details as input and returns the quantity object.

    The oh_att_atr function is called in a SQL query along with joins to other tables to retrieve the item name, uom etc. The function return values are used to display the ON Hand, ATT and ATR quantities for the item.

    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