How to programmatically close PO using PO_ACTIONS API

PO_ACTIONS.CLOSE_PO is the API used to programmatically close/final close Purchase Order. We have to pass P_ACTION parameter value as 'CLOSE'/'FINALLY CLOSE' depending on the requirement to close a Purchase Order.

Before applying the API, we are taking a PO as CLOSE_CODE  as 'CLOSED' for testing purpose and query the purchase order in Backend. The result of PO is given in snapshot below. 

Below are the code to execute the api.

Sample:-

/* Formatted on 2016/10/18 12:08 (Formatter Plus v4.8.8) */

DECLARE

   x_action         CONSTANT VARCHAR2 (20)  := 'FINALLY CLOSE';

   -- Change this parameter as per requirement

   x_calling_mode   CONSTANT VARCHAR2 (2)   := 'PO';

   x_conc_flag      CONSTANT VARCHAR2 (1)   := 'N';

   x_return_code_h           VARCHAR2 (100);

   x_auto_close     CONSTANT VARCHAR2 (1)   := 'N';

   x_origin_doc_id           NUMBER;

   x_returned                BOOLEAN        := NULL;

BEGIN

  

   apps.mo_global.set_policy_context ('S', 204);

   fnd_global.apps_initialize (1015932, 50578, 201);

   DBMS_OUTPUT.put_line

             ('Calling PO_Actions.close_po for Closing/Finally Closing PO =>');

   x_returned :=

      po_actions.close_po (p_docid              => 131583,

                           p_doctyp             => 'PO',

                           p_docsubtyp          => 'STANDARD',

                           p_lineid             => NULL,

                           p_shipid             => NULL,

                           p_action             => x_action,

                           p_reason             => NULL,

                           p_calling_mode       => x_calling_mode,

                           p_conc_flag          => x_conc_flag,

                           p_return_code        => x_return_code_h,

                           p_auto_close         => x_auto_close,

                           p_action_date        => SYSDATE,

                           p_origin_doc_id      => NULL

                          );

   COMMIT;

   IF x_returned = TRUE

   THEN

      DBMS_OUTPUT.put_line

                   ('Purchase Order which just got Closed to Finally Closed. ');

      DBMS_OUTPUT.put_line (x_return_code_h);

   ELSE

      DBMS_OUTPUT.put_line

                      ('API Failed to Close/Finally Close the Purchase Order');

   END IF;

END;

After applying the API, the status of given PO is  changed successfully.

Posted By : Mohammad M Alam

Create Bulk GL Code Combinations Using API

DECLARE

CURSOR c1
   IS
   SELECT  a.rowid row_id,a.*
FROM    temp_gl_code a
WHERE   process_flag= 'N';
     
       
  l_segment1                 GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
  l_segment2                 GL_CODE_COMBINATIONS.SEGMENT2%TYPE;
  l_segment3                 GL_CODE_COMBINATIONS.SEGMENT3%TYPE;
  l_segment4                 GL_CODE_COMBINATIONS.SEGMENT4%TYPE;
  l_segment5                 GL_CODE_COMBINATIONS.SEGMENT5%TYPE;
  l_segment6                 GL_CODE_COMBINATIONS.SEGMENT6%TYPE;
  l_valid_combination        BOOLEAN;
  l_cr_combination           BOOLEAN;
  l_ccid                     GL_CODE_COMBINATIONS_KFV.code_combination_id%TYPE;
  l_structure_num            FND_ID_FLEX_STRUCTURES.ID_FLEX_NUM%TYPE;
  l_conc_segs                GL_CODE_COMBINATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;
  p_error_msg1               VARCHAR2(240);
  p_error_msg2               VARCHAR2(240);
  v_error_flag               BOOLEAN;
  v_error_msg                VARCHAR2(3000) :=NULL;
  v_count1                   NUMBER;
 v_count2                    NUMBER;
 v_row_count                 NUMBER := 0;
 v_req_message               VARCHAR2(3000);
 v_req_id                    NUMBER;
 v_req_status                BOOLEAN;
 v_rphase                    VARCHAR2(20);
 v_rstatus                   VARCHAR2(20);
 v_dphase                    VARCHAR2(20);
 v_dstatus                   VARCHAR2(20);
  -- ----------------------
--START OF VALIDATION
-- ----------------------
BEGIN
 
 FOR i in c1
 
   LOOP
     v_error_flag := FALSE;
     v_error_msg := NULL;
   
  l_segment1  := i.segment1;
  l_segment2  := i.segment2;
  l_segment3  := i.segment3;
  l_segment4  := i.segment4;
  l_segment5  := i.segment5;
  l_segment6  := i.segment6;
  l_conc_segs := l_segment1||'.'||l_segment2||'.'||l_segment3||'.'||l_segment4||'.'||l_segment5||'.'||l_segment6 ;
 
 

  BEGIN
    SELECT
    id_flex_num
      INTO l_structure_num
      FROM apps.fnd_id_flex_structures
     WHERE id_flex_code        = 'GL#'
       AND id_flex_structure_code='COVAD_ACCOUNTING_FLEXFIELD';
  EXCEPTION
  WHEN OTHERS THEN
    l_structure_num:=NULL;
  END;
 
  ---------------Check if CCID exits with the above Concatenated Segments---------------
  BEGIN
    SELECT code_combination_id
      INTO l_ccid
      FROM apps.gl_code_combinations_kfv
     WHERE concatenated_segments = l_conc_segs;
  EXCEPTION
  WHEN OTHERS THEN
    l_ccid         :=NULL;
  END;
   
    -- -------------------------------------------------------------------------------
-- UPDATING THE PROCESS FLAG FOR ERRORS IN temp_gl_code TABLE
-- -------------------------------------------------------------------------------
    IF l_ccid IS NOT NULL THEN
    ------------------------The CCID is Available----------------------
    --DBMS_OUTPUT.PUT_LINE('COMBINATION_ID= ' ||l_ccid);
    v_error_msg := v_error_msg ||'Combination already Exists '|| '; ' ;
   
        UPDATE  temp_gl_code
        SET process_flag                = 'E'
        ,errror_message                 = 'Validation Error:'||v_error_msg
        ,last_update_date               = SYSDATE
        WHERE rowid                     = i.row_id
        AND process_flag                = 'N' ;
COMMIT;
    ELSE
   
DBMS_OUTPUT.PUT_LINE('This is a New Combination. Validation Starts....');
    ------------Validate the New Combination--------------------------
    l_valid_combination := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS
                          (
                          operation => 'CHECK_COMBINATION',
                          appl_short_name => 'SQLGL',
                          key_flex_code => 'GL#',
                          structure_number => L_STRUCTURE_NUM,
                          concat_segments => L_CONC_SEGS
                          );
    p_error_msg1 := FND_FLEX_KEYVAL.ERROR_MESSAGE;
   
    BEGIN
        UPDATE  temp_gl_code
        SET process_flag                = 'E'
        ,errror_message                  = FND_FLEX_KEYVAL.ERROR_MESSAGE
        ,last_update_date               = SYSDATE
        WHERE rowid                     = i.row_id
        AND process_flag                = 'N' ;
COMMIT;
    END;

    IF l_valid_combination then

      DBMS_OUTPUT.PUT_LINE('Validation Successful! Creating the Combination...');
      -------------------Create the New CCID--------------------------

      L_CR_COMBINATION := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS
                          (
                          operation => 'CREATE_COMBINATION',
                          appl_short_name => 'SQLGL',
                          key_flex_code => 'GL#',
                          structure_number => L_STRUCTURE_NUM,
                          concat_segments => L_CONC_SEGS );
         
          p_error_msg2 := FND_FLEX_KEYVAL.ERROR_MESSAGE;
         
         
   IF l_cr_combination THEN
        -------------------Fetch the New CCID--------------------------
        SELECT code_combination_id
          INTO l_ccid
          FROM apps.gl_code_combinations_kfv
        WHERE concatenated_segments = l_conc_segs;
       
        UPDATE  temp_gl_code
        SET process_flag                = 'S'
        ,errror_message                  = 'Import Success='|| l_ccid
        ,last_update_date               = SYSDATE
        WHERE rowid                     = i.row_id
        AND process_flag                = 'E' ;
    COMMIT;
    ELSE
        -------------Error in creating a combination-----------------
        DBMS_OUTPUT.PUT_LINE('Error in creating the combination: '||p_error_msg2);
      END IF;
    ELSE
      --------The segments in the account string are not defined in gl value set----------
      DBMS_OUTPUT.PUT_LINE('Error in validating the combination: '||p_error_msg1);
    END IF;
  END IF;
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
END;

--- By--  Sivachandaran ---

Sales Order details with India Localization tax

SELECT h.header_id header_id, party.party_name sold_to,
       h.order_number order_number,
       NVL (TO_CHAR (h.ordered_date, 'DD-MON-YYYY'),
            TO_CHAR (h.request_date, 'DD-MON-YYYY')
           ) ordered_date,
       (SELECT TO_CHAR (MAX (oola.schedule_ship_date),
                        'DD-MON-YYYY'
                       )
          FROM oe_order_lines_all oola
         WHERE oola.header_id = h.header_id) dispatch_date,
       bill_su.LOCATION invoice_to_location,
       bill_loc.address1 invoice_to_address1,
       bill_loc.address2 invoice_to_address2,
       bill_loc.address3 invoice_to_address3,
       bill_loc.address4 invoice_to_address4,
          DECODE (bill_loc.city,
                  NULL, NULL,
                  bill_loc.city || ', '
                 )
       || DECODE (bill_loc.state,
                  NULL, bill_loc.province || ', ',
                  bill_loc.state || ', '
                 )
       || DECODE (bill_loc.postal_code,
                  NULL, NULL,
                  bill_loc.postal_code || ', '
                 )
       || DECODE (ft.territory_short_name,
                  NULL, NULL,
                  ft.territory_short_name || '.'
                 ) invoice_to_address5,
       ship_su.LOCATION ship_to_location, ship_loc.address1 ship_to_address1,
       ship_loc.address2 ship_to_address2, ship_loc.address3 ship_to_address3,
       ship_loc.address4 ship_to_address4,
          DECODE (ship_loc.city,
                  NULL, NULL,
                  ship_loc.city || ', '
                 )
       || DECODE (ship_loc.state,
                  NULL, ship_loc.province || ', ',
                  ship_loc.state || ', '
                 )
       || DECODE (ship_loc.postal_code,
                  NULL, NULL,
                  ship_loc.postal_code || ', '
                 )
       || DECODE (ft.territory_short_name,
                  NULL, NULL,
                  ft.territory_short_name || '.'
                 ) ship_to_address5,
       h.cust_po_number reference_number, h.attribute1 transporter_name,
       term.NAME terms, cust_acct.cust_account_id cust_account_id,
       l.line_number line_no,
       (SELECT NVL (l1.user_item_description, msi.description)
          FROM oe_order_lines_all l1, mtl_system_items_b msi
         WHERE l1.inventory_item_id = msi.inventory_item_id
           AND l1.ship_from_org_id = msi.organization_id
           AND l1.header_id = l.header_id
           AND l1.line_id = l.line_id
           AND l1.inventory_item_id = l.inventory_item_id) particulars,
       l.unit_selling_price unit_selling_price,
       l.ordered_quantity ordered_quantity,
      -- l.order_quantity_uom order_quantity_uom,
      (SELECT primary_unit_of_measure
          FROM oe_order_lines_all l2, mtl_system_items_b msi1
         WHERE l2.inventory_item_id = msi1.inventory_item_id
           AND l2.ship_from_org_id = msi1.organization_id
           AND l2.header_id = l.header_id
           AND l2.line_id = l.line_id
           AND l2.inventory_item_id = l.inventory_item_id) order_quantity_uom,
       (l.ordered_quantity * l.unit_selling_price) line_amount,
       NVL (jcta.tax_rate, 0) tax_rate,
       (SELECT NVL (SUM (  ABS (NVL (amount_due_remaining, 0))
                         + ABS (NVL (tax_remaining, 0))
                        ),
                    0
                   )
          FROM ar_payment_schedules_all apsa1
         WHERE 1 = 1
           AND apsa1.CLASS IN ('PMT')
           AND apsa1.customer_id = h.sold_to_org_id) customer_balance,
       h.attribute2 remarks, fu.user_name prepared_by,
       l.ordered_item item_code, jcca.pan_no pan_no,
       jcca.cst_reg_no cst_reg_no, jcca.vat_reg_no vat_reg_no,
       jcca.service_tax_regno service_tax_regno
  FROM hz_parties party,
       hz_cust_accounts cust_acct,
       oe_order_headers_all h,
       oe_order_lines_all l,
       hz_cust_site_uses_all bill_su,
       hz_locations bill_loc,
       hz_cust_acct_sites_all bill_cas,
       hz_party_sites bill_ps,
       hz_cust_site_uses_all ship_su,
       hz_party_sites ship_ps,
       hz_locations ship_loc,
       hz_cust_acct_sites_all ship_cas,
       ra_terms_tl term,
       jai_om_oe_so_taxes jst,
       jai_cmn_taxes_all jcta,
       fnd_user fu,
       jai_cmn_cus_addresses jcca,
       fnd_territories_vl ft
 WHERE 1 = 1
   AND h.header_id = l.header_id
   AND h.org_id = l.org_id
   AND cust_acct.party_id = party.party_id
   AND h.sold_to_org_id = cust_acct.cust_account_id
   AND h.invoice_to_org_id = bill_su.site_use_id(+)
   AND bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id(+)
   AND bill_cas.party_site_id = bill_ps.party_site_id(+)
   AND bill_loc.location_id(+) = bill_ps.location_id
   AND h.ship_to_org_id = ship_su.site_use_id(+)
   AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
   AND ship_cas.party_site_id = ship_ps.party_site_id(+)
   AND ship_loc.location_id(+) = ship_ps.location_id
   AND h.payment_term_id = term.term_id(+)
   AND term.LANGUAGE(+) = USERENV ('LANG')
   AND l.header_id = jst.header_id(+)
   AND l.line_id = jst.line_id(+)
   AND h.created_by = fu.user_id
   AND jst.tax_id = jcta.tax_id(+)
   AND jcta.tax_type IN ('CST', 'VALUE ADDED TAX')
   AND cust_acct.cust_account_id = jcca.customer_id
   AND ship_su.cust_acct_site_id = jcca.address_id
   AND ship_su.site_use_code = 'SHIP_TO'
   AND h.flow_status_code IN ('BOOKED')
   AND bill_loc.country = ft.territory_code(+)
   AND h.order_number = :p_sales_order

   ---Pradipta Behera

Query to get opening,purchasing,receive,sales and closing quantity of items

SELECT *
  FROM (SELECT   b.item_code, b.item_description item_description,
                 b.primary_uom, b.sub_inventory, b.LOCATOR,
                 b.opening_qty opening_qty, b.opening_value opening_value,
                 SUM (b.purchase_qty) purchase_qty,
                 SUM (b.purchasing_value) purchasing_value,
                 SUM (b.receive_qty) receive_qty,
                 SUM (b.receive_value) receive_value,
                 SUM (b.sales_qty) sales_qty, SUM (b.sale_value) sale_value,
                 SUM (b.other_plant_qty) other_plant_qty,
                 SUM (b.other_plant_value) other_plant_value,
                   (SUM (b.purchase_qty) + SUM (b.receive_qty) + b.opening_qty
                   )
                 - (ABS (SUM (b.sales_qty)) - SUM (b.other_plant_qty))
                                                                  closing_qty,
                 (  (  (  SUM (b.purchase_qty)
                        + SUM (b.receive_qty)
                        + b.opening_qty
                       )
                     - (ABS (SUM (b.sales_qty)) - SUM (b.other_plant_qty))
                    )
                  * cmpnt_cost
                 ) closing_value,
                 cmpnt_cost
            FROM (SELECT a.item_code, a.item_description, a.primary_uom,
                         a.sub_inventory, a.LOCATOR,
                         NVL (a.opening_qty, 0) opening_qty,
                         (NVL (opening_qty, 0) * cmpnt_cost) opening_value,
                         a.purchase_qty,
                         NVL (purchase_qty, 0) * cmpnt_cost purchasing_value,
                         a.receive_qty,
                         NVL (receive_qty, 0) * cmpnt_cost receive_value,
                         a.sales_qty,
                         NVL (sales_qty, 0) * cmpnt_cost sale_value,
                         a.other_plant_qty,
                           NVL (other_plant_qty, 0)
                         * cmpnt_cost other_plant_value,
                         cmpnt_cost
                    FROM (SELECT   msib.segment1 item_code,
                                   msib.description item_description,
                                   msib.primary_uom_code primary_uom,
                                   msi.secondary_inventory_name sub_inventory,
                                   (SELECT SUM
                                              (CASE
                                                  WHEN mmt1.transaction_type_id IN
                                                                     (36,15,63,52,21)
                                                     THEN DECODE
                                                           (mmt1.primary_quantity
                                                                ,
                                                             1, 0,
                                                             mmt1.primary_quantity
                                                            )
                                                  WHEN mmt1.transaction_type_id IN
                                                         (18, 12, 42, 43, 61,
                                                          33,35, 32, 34, 54,
                                                          62,21)
                                                     THEN mmt1.primary_quantity
                                                  ELSE 0
                                               END
                                              )
                                      FROM mtl_material_transactions mmt1,
                                           mtl_transaction_types mtt1
                                     WHERE 1 = 1
                                       AND mmt1.transaction_type_id =
                                                      mtt1.transaction_type_id
                                       AND mmt1.organization_id =
                                                          msib.organization_id
                                       AND mmt1.subinventory_code =
                                                  msi.secondary_inventory_name
                                       AND mmt1.inventory_item_id =
                                                        msib.inventory_item_id
                                       AND (mmt1.locator_id =
                                                     mil.inventory_location_id OR mil.inventory_location_id IS NULL)
                                       AND TRUNC (mmt1.transaction_date) <
                                                TRUNC (TO_DATE (:p_from_date)))
                                                                  opening_qty,
                                   mil.concatenated_segments LOCATOR,
                                   TRUNC
                                       (mmt.transaction_date)
                                                             transaction_date,
                                   SUM
                                      (CASE
                                          WHEN mmt.transaction_type_id IN
                                                                         (36)
                                             THEN DECODE
                                                       (primary_quantity,
                                                        1, 0,
                                                        primary_quantity
                                                       )
                                          WHEN mmt.transaction_type_id = 18
                                             THEN primary_quantity
                                          ELSE 0
                                       END
                                      ) purchase_qty,
                                   SUM
                                      (CASE
                                          WHEN mmt.subinventory_code LIKE
                                                                  'Staging_SI'
                                             THEN
                                             CASE
                                                    WHEN mmt.transaction_type_id IN
                                                           (12, 42, 43, 61,
                                                            15, 52)
                                                 THEN primary_quantity
                                                    ELSE 0
                                             END
                                          ELSE
                                           CASE
                                              WHEN mmt.transaction_type_id IN
                                                             (12, 42, 43, 61, 15)
                                           THEN primary_quantity
                                              ELSE 0
                                           END
                                       END
                                      ) receive_qty,
                                   (SUM
                                       (CASE
                                           WHEN mmt.subinventory_code LIKE
                                                                  'Staging_SI'
                                              THEN CASE
                                                   
                                                  WHEN mmt.transaction_type_id IN
                                                          (33, 35)
                                                        THEN primary_quantity
                                                     ELSE 0
                                              END
                                                ELSE CASE
                                                 
                                                WHEN mmt.transaction_type_id IN
                                                                         (33, 35, 52,21)
                                                      THEN primary_quantity
                                                   ELSE 0
                                                END
                                        END
                                       )
                                   ) sales_qty,
                                   (SELECT (SUM
                                               (CASE
                                                   WHEN mmt1.transaction_type_id IN
                                                                     (36, 15)
                                                      THEN DECODE
                                                             (mmt1.primary_quantity
                                                                 ,
                                                              1, 0,
                                                              mmt1.primary_quantity
                                                             )
                                                   WHEN mmt1.transaction_type_id IN
                                                          (18, 12, 42, 43, 61,
                                                           33, 35, 32, 34, 54,
                                                           62, 52)
                                                      THEN mmt1.primary_quantity
                                                   ELSE 0
                                                END
                                               )
                                           )
                                      FROM mtl_material_transactions mmt1,
                                           mtl_transaction_types mtt1
                                     WHERE 1 = 1
                                       AND mmt1.transaction_type_id =
                                                      mtt1.transaction_type_id
                                       AND mmt1.organization_id =
                                                          msib.organization_id
                                       AND mmt1.subinventory_code =
                                                         mil.subinventory_code
                                       AND mmt1.inventory_item_id =
                                                        msib.inventory_item_id
                                       AND mmt1.subinventory_code = msi.secondary_inventory_name
                                       AND mmt1.locator_id =
                                                     mil.inventory_location_id
                                       AND TRUNC (mmt1.transaction_date) =
                                                  TRUNC (mmt.transaction_date))
                                                                  closing_qty,
                                   SUM
                                      (CASE
                                          WHEN mmt.subinventory_code LIKE
                                                                  'SLT_SI'
                                             THEN CASE
                                                   
                                                  WHEN mmt.transaction_type_id IN
                                                          (32, 34, 54, 62, 63)
                                                        THEN primary_quantity
                                                     ELSE 0
                                              END
                                           
                                              WHEN mmt.subinventory_code LIKE
                                                                  'RM_SI'
                                             THEN CASE
                                                   
                                                  WHEN mmt.transaction_type_id IN
                                                          (34, 54, 62, 63)
                                                        THEN primary_quantity
                                                     ELSE 0
                                              END
                                           
                                              ELSE CASE
                                                 
                                                WHEN mmt.transaction_type_id IN
                                                                         (32, 34, 54, 62, 63,64)
                                                      THEN primary_quantity
                                                   ELSE 0
                                                END
                                       END
                                      ) other_plant_qty,
                                   xx_item_costs
                                          (msib.inventory_item_id,
                                           msib.organization_id,
                                           :p_to_date,
                                           :p_from_date
                                          ) cmpnt_cost
                              FROM mtl_system_items_b msib,
                                   mtl_secondary_inventories msi,
                                   mtl_item_locations_kfv mil,
                                   mtl_item_categories mic,
                                   mtl_categories_kfv mcb,
                                   org_organization_definitions ood,
                                   mtl_material_transactions mmt,
                                   mtl_transaction_types mtt
                             WHERE 1 = 1
                               AND ood.organization_id = msib.organization_id
                               AND msib.organization_id = msi.organization_id
                               AND msi.secondary_inventory_name(+) =
                                                         mmt.subinventory_code
                               AND mic.inventory_item_id =
                                                        msib.inventory_item_id
                               AND mic.organization_id = msib.organization_id
                               AND mic.category_id = mcb.category_id
                               AND msib.organization_id = :p_organization_id
                               AND msi.secondary_inventory_name =
                                      NVL (:p_sub_inventory,
                                           msi.secondary_inventory_name
                                          )
                               AND (   mil.concatenated_segments =
                                          NVL (:p_locator,
                                               mil.concatenated_segments
                                              )
                                    OR mil.concatenated_segments IS NULL
                                   )
                               AND mcb.segment1 =
                                               NVL (:p_segment1, mcb.segment1)
                               AND mcb.segment2 =
                                               NVL (:p_segment2, mcb.segment2)
                               AND mcb.segment3 =
                                               NVL (:p_segment3, mcb.segment3)
                               AND mcb.segment4 =
                                               NVL (:p_segment4, mcb.segment4)
                               AND ood.operating_unit = :p_org_id
                               AND mmt.transaction_type_id =
                                                       mtt.transaction_type_id
                               AND mmt.transaction_type_id IN
                                      (18, 12, 42, 43, 61, 33, 35, 32, 34, 54,
                                       62, 36, 15, 52, 63, 64,21)
                               AND mmt.organization_id = msib.organization_id(+)
                               AND mmt.subinventory_code = mil.subinventory_code(+)
                               AND mmt.locator_id = mil.inventory_location_id(+)
                               AND mmt.inventory_item_id = msib.inventory_item_id(+)
                               AND TRUNC (mmt.transaction_date)
                                      BETWEEN TRUNC (TO_DATE (:p_from_date))
                                          AND TRUNC (TO_DATE (:p_to_date))
                               AND category_set_id = 1
                             --  AND MMT.INVENTORY_ITEM_ID=24256
                          GROUP BY msib.segment1,
                                   msib.description,
                                   msib.primary_uom_code,
                                   msi.secondary_inventory_name,
                                   mil.concatenated_segments,
                                   TRUNC (mmt.transaction_date),
                                   msib.organization_id,
                                   mil.subinventory_code,
                                   mil.inventory_location_id,
                                   msib.inventory_item_id) a) b
        GROUP BY b.item_code,
                 b.item_description,
                 b.primary_uom,
                 b.sub_inventory,
                 b.LOCATOR,
                 b.opening_qty,
                 b.opening_value,
                 cmpnt_cost
          HAVING (   b.opening_qty <> 0
                  OR SUM (b.purchase_qty) <> 0
                  OR SUM (b.receive_qty) <> 0
                  OR SUM (b.sales_qty) <> 0
                  OR SUM (b.other_plant_qty) <> 0
                  OR   (  SUM (b.purchase_qty)
                        + SUM (b.receive_qty)
                        + b.opening_qty
                       )
                     - (ABS (SUM (b.sales_qty)) - SUM (b.other_plant_qty)) <>
                                                                             0
                 )
        ORDER BY b.item_code, b.sub_inventory, b.LOCATOR)
        UNION ALL
SELECT a.item_code, a.item_description, a.primary_uom, a.sub_inventory,
       a.LOCATOR, NVL (a.opening_qty, 0) opening_qty, NVL(a.opening_qty * a.cmpnt_cost,0) opening_value,
       NVL(a.purchase_qty,0) purchase_qty, NVL(a.purchasing_value,0) purchasing_value, NVL(a.receive_qty,0) receive_qty,
       NVL(a.receive_value,0)receive_value,
       NVL(a.sales_qty,0) sales_qty, NVL(a.sale_value,0)sale_value, NVL(a.other_plant_qty,0) other_plant_qty, NVL(a.other_plant_value,0)other_plant_value,
       NVL (a.closing_qty, 0) closing_qty, NVL(a.closing_qty * a.cmpnt_cost,0) closing_value,
       a.cmpnt_cost
       FROM(SELECT   msib.segment1 item_code, msib.description item_description,
         msib.primary_uom_code primary_uom,
         msi.secondary_inventory_name sub_inventory,
         mil.concatenated_segments LOCATOR,
         (SELECT SUM
                    (CASE
                        WHEN mmt1.transaction_type_id IN
                                                   (36,15,52,63,21)
                           THEN DECODE (mmt1.primary_quantity,
                                        1, 0,
                                        mmt1.primary_quantity
                                       )
                        WHEN mmt1.transaction_type_id IN
                                 (18, 12, 42, 43, 61, 33, 35, 32, 34, 54,62)
                           THEN mmt1.primary_quantity
                        ELSE 0
                     END
                    )
            FROM mtl_material_transactions mmt1, mtl_transaction_types mtt1
          WHERE  1 = 1
             AND mmt1.transaction_type_id = mtt1.transaction_type_id
             AND mmt1.organization_id = msib.organization_id
             AND mmt1.subinventory_code = msi.secondary_inventory_name
             AND msi.organization_id = :p_organization_id
             AND mmt1.inventory_item_id = msib.inventory_item_id
             AND mmt1.locator_id = mil.inventory_location_id
             AND mmt1.organization_id = :p_organization_id
             AND TRUNC (mmt1.transaction_date) <
                                                TRUNC (TO_DATE (:p_from_date)))
                                                                  opening_qty,
         NULL purchase_qty, NULL purchasing_value, NULL receive_qty,
         NULL receive_value, NULL sales_qty, NULL sale_value,
         NULL other_plant_qty, NULL other_plant_value,
         (SELECT (SUM
                                               (CASE
                                                   WHEN mmt1.transaction_type_id IN
                                                                     (36, 15,21)
                                                      THEN DECODE
                                                             (mmt1.primary_quantity,
                                                              1, 0,
                                                              mmt1.primary_quantity
                                                             )
                                                   WHEN mmt1.transaction_type_id IN
                                                          (18, 12, 42, 43, 61,
                                                           33, 35, 32, 34, 54,
                                                           62,52)
                                                      THEN mmt1.primary_quantity
                                                   ELSE 0
                                                END
                                               )
                                           )
                                           FROM mtl_material_transactions mmt1,
                                           mtl_transaction_types mtt1
                                     WHERE 1 = 1
                                       AND mmt1.transaction_type_id =
                                                      mtt1.transaction_type_id
                                       AND mmt1.organization_id =msib.organization_id
                                       AND mmt1.subinventory_code =mil.subinventory_code
                                       AND mmt1.inventory_item_id = msib.inventory_item_id
                                       AND mmt1.subinventory_code = msi.secondary_inventory_name
                                       AND mmt1.locator_id = mil.inventory_location_id
                                       AND TRUNC (mmt1.transaction_date) <= TRUNC (TO_DATE (:p_to_date)))closing_qty,
         xx_item_costs (msib.inventory_item_id,
                        msib.organization_id,
                        :p_to_date,
                        :p_from_date
                       ) cmpnt_cost
    FROM mtl_secondary_inventories msi,
         mtl_item_locations_kfv mil,
         mtl_system_items_b msib,
         org_organization_definitions ood,
         mtl_item_categories mic,
         mtl_categories_kfv mcb,
         mtl_material_transactions mmt,
         mtl_transaction_types mtt      
   WHERE 1 = 1
     AND ood.organization_id = msib.organization_id
     AND msib.organization_id = msi.organization_id
     AND msi.secondary_inventory_name = mil.subinventory_code
     AND msib.organization_id = :p_organization_id
     AND ood.operating_unit = :p_org_id
     AND mic.inventory_item_id = msib.inventory_item_id
     AND mic.organization_id = msib.organization_id
     AND mic.category_id = mcb.category_id
     AND msi.secondary_inventory_name =
                          NVL (:p_sub_inventory, msi.secondary_inventory_name)
     AND mil.concatenated_segments =
                                   NVL (:p_locator, mil.concatenated_segments)
     AND mcb.segment1 = NVL (:p_segment1, mcb.segment1)
     AND mcb.segment2 = NVL (:p_segment2, mcb.segment2)
     AND mcb.segment3 = NVL (:p_segment3, mcb.segment3)
     AND mcb.segment4 = NVL (:p_segment4, mcb.segment4)
     AND category_set_id = 1
     AND msib.inventory_item_id NOT IN (
                    SELECT inventory_item_id
                      FROM mtl_material_transactions mmt
                     WHERE 1 = 1
                       AND TRUNC (mmt.transaction_date)
                              BETWEEN TRUNC (TO_DATE (:p_from_date))
                                  AND TRUNC (TO_DATE (:p_to_date))
                       AND mmt.organization_id = :p_organization_id
                       AND mmt.subinventory_code= mil.subinventory_code)
   AND mmt.transaction_type_id = mtt.transaction_type_id
   AND mmt.subinventory_code =mil.subinventory_code
   AND mmt.inventory_item_id = msib.inventory_item_id
   AND mmt.subinventory_code = msi.secondary_inventory_name
   AND mmt.locator_id = mil.inventory_location_id
  -- AND MMT.INVENTORY_ITEM_ID=24256        
GROUP BY msib.segment1,
         msib.description,
         msib.primary_uom_code,
         msib.inventory_item_id,
         msib.organization_id,
         msi.secondary_inventory_name,
         mil.inventory_location_id,
         msi.organization_id,
         mil.subinventory_code,
         mil.concatenated_segments)a
         where ( a.opening_qty <> 0
               OR a.closing_qty <> 0 )

      ---Pradipta Behera