Scripts for menu , user name menu , User , Responsibility

/* Formatted on 25-09-2014 15:19:08 (QP5 v5.115.810.9015) */

SELECT   FRV.RESPONSIBILITY_NAME,

fmv.MENU_NAME,

FMV.USER_MENU_NAME,

FU.USER_NAME , fmev.PROMPT

FROM   FND_RESPONSIBILITY_VL FRV,

FND_MENUS_VL FMV,

fnd_user fu,

fnd_user_resp_groups furg ,

FND_MENU_ENTRIES_VL fmev

WHERE     --  RESPONSIBILITY_NAME = 'Su-Kam India Local Inventory Super User'

    FRV.menu_id = 80345

AND FRV.menu_id = FMV.menu_id

AND fu.user_id = furg.user_id

AND FRV.RESPONSIBILITY_ID = furg.RESPONSIBILITY_ID

and fmev.MENU_ID = fmv.MENU_ID

and fmev.prompt= 'Miscellaneous Transaction'

TRANSFER THE ONE ORG TO ANOTHER ORG ITEMWISE SCRIPTS

/* Formatted on 2014/09/07 14:15 (Formatter Plus v4.8.8) */
DECLARE
   --   g_user_id         fnd_user.user_id%TYPE := NULL;
   l_appl_id         fnd_application.application_id%TYPE;
   l_resp_id         fnd_responsibility_tl.responsibility_id%TYPE;
   l_api_version     NUMBER                                         := 1.0;
   l_init_msg_list   VARCHAR2 (2)                          := fnd_api.g_false;
   l_commit          VARCHAR2 (2)                          := fnd_api.g_false;
   x_message_list    error_handler.error_tbl_type;
   x_return_status   VARCHAR2 (2);
   x_msg_count       NUMBER                                         := 0;
   itemid            NUMBER;
   segment1          VARCHAR2 (300);
   uom               VARCHAR2 (300);

   CURSOR cur
   IS
      SELECT DISTINCT inventory_item_id, concatenated_segments,
                      primary_uom_code
                 --           INTO itemid, segment1, uom
      FROM            mtl_system_items_kfv
                WHERE organization_id = 106;
BEGIN
   fnd_global.apps_initialize (1697, 50641, 7000);

   FOR x IN cur
   LOOP
      ego_item_pub.assign_item_to_org
                                   (p_api_version            => l_api_version,
                                    p_init_msg_list          => l_init_msg_list,
                                    p_commit                 => l_commit,
                                    p_inventory_item_id      => x.inventory_item_id,
                                    p_item_number            => x.concatenated_segments,
                                    p_organization_id        => 227,
                                    p_organization_code      => 566,
                                    p_primary_uom_code       => x.primary_uom_code,
                                    x_return_status          => x_return_status,
                                    x_msg_count              => x_msg_count
                                   );
      DBMS_OUTPUT.put_line ('Status: ' || x_return_status);
   END LOOP;

   IF (x_return_status <> fnd_api.g_ret_sts_success)
   THEN
      DBMS_OUTPUT.put_line ('Error Messages :');
      error_handler.get_message_list (x_message_list => x_message_list);

      FOR j IN 1 .. x_message_list.COUNT
      LOOP
         DBMS_OUTPUT.put_line (x_message_list (j).MESSAGE_TEXT);
      END LOOP;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Exception Occured :');
      DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);
END;

To Find The Path Of FORMS AND REPORTS

 SELECT af.app_short_name "Application"                      ,
  af.filename "File Name"                                    ,
  afv.VERSION "File Version"                                 ,
  NVL (aap.patch_name, 'Default Installation') "Patch Number",
  '$'
  || fa.basepath
  || '/'
  || af.subdir "Location"
   FROM apps.ad_files af           ,
  apps.ad_patch_run_bug_actions apa,
  apps.ad_patch_run_bugs aprb      ,
  apps.ad_patch_runs apr           ,
  apps.ad_patch_drivers apd        ,
  apps.ad_applied_patches aap      ,
  apps.ad_file_versions afv        ,
  apps.fnd_application fa
  WHERE af.filename = 'INVTOMAI.fmb'               -- 'OEXWFASG.rdf'
AND af.file_id      = apa.file_id(+)
  --AND apa.common_action_id(+) = 4042
AND apa.patch_run_bug_id = aprb.patch_run_bug_id(+)
AND aprb.patch_run_id    = apr.patch_run_id(+)
AND apr.patch_driver_id  = apd.patch_driver_id(+)
AND apd.applied_patch_id = aap.applied_patch_id(+)
AND af.file_id           = afv.file_id
AND af.app_short_name    = fa.application_short_name
GROUP BY aap.patch_name,
  af.filename          ,
  afv.VERSION          ,
  af.app_short_name    ,
  af.file_id           ,
  af.subdir            ,
  fa.basepath;

PROCEDURE FOR ITEM WISE WITH NON MANDATORY IF ITEM CODE IS NULL OR NOT NULL

GRANT ALL ON  XXTNPL.tnpl_om_stock_in_hand_reel TO APPS.XXTNPL.tnpl_om_stock_in_hand_reel

 CREATE SYNONYM XXTNPL.tnpl_om_stock_in_hand_reel FOR APPS.XXTNPL.tnpl_om_stock_in_hand_reel



GRANT ALL ON  XXTNPL.TNPL_OM_STOCK_IN_HAND TO APPS

 CREATE SYNONYM APPS.TNPL_OM_STOCK_IN_HAND FOR XXTNPL.TNPL_OM_STOCK_IN_HAND;


CREATE OR REPLACE PROCEDURE XXTNPL.tnpl_om_stock_in_hand_reel (
   p_from_date   IN   DATE DEFAULT NULL,
   p_to_date     IN   DATE DEFAULT NULL,
   p_variety     IN   VARCHAR2 DEFAULT NULL,
   p_gsm         IN   VARCHAR2 DEFAULT NULL,
   p_size        IN   VARCHAR2 DEFAULT NULL,
   p_seg         IN   VARCHAR2 DEFAULT NULL,
   p_org_id      IN   NUMBER DEFAULT NULL
)
IS
--DECLARE
--   p_from               DATE          := '1-May-2013';
--   p_to_date            DATE          := '02-May-2013';
--   p_variety            VARCHAR2 (25) := NULL;
--   p_gsm                VARCHAR2 (25) := NULL;
--   p_size               VARCHAR2 (25) := NULL;
--   p_seg                VARCHAR2 (35) := NULL;
--   p_org_id             NUMBER        := 110;
--   l_opn_balace         NUMBER        := NULL;
   l_opn_balace         NUMBER;
   l_close_qty_cal      NUMBER;
   l_open_qty_bal       NUMBER;
   l_open_qty_balance   NUMBER;
   l_receipt_qty        NUMBER;
   l_openinig_lot       NUMBER;
   l_open_lot           NUMBER;
   l_closing_lot        NUMBER;

   CURSOR c1
   IS
    
      SELECT   *
          FROM TNPL_OM_STOCK_IN_HAND
      ORDER BY creation_date;
BEGIN
   BEGIN
   execute immediate 'truncate table TNPL_OM_STOCK_IN_HAND';
      INSERT INTO TNPL_OM_STOCK_IN_HAND
                  (creation_date, receipt_qty, receipt_lot, variety, gsm,
                   width_size, dispatch_qty, dispatch_lot, sales_return,
                   sales_lot, stock_return, stock_lot, item_code)
         (SELECT   creation_date, SUM (receipt_qty) receipt_qty,
                   SUM (receipt_lot) receipt_lot, variety, gsm, width_size,
                   SUM (dispatch_qty) dispatch_qty,
                   SUM (dispatch_lot) dispatch_lot,
                   SUM (sales_return) sales_return, SUM (sales_lot)
                                                                   sales_lot,
                   SUM (stock_return) stock_return,
                   SUM (stock_retrun_lot) stock_lot, item_code
              FROM (SELECT   creation_date, /* transaction_type_name,*/ SUM
                                                                          (receipt_qty
                                                                          )
                                                                  receipt_qty,
                             (receipt_lot_plus - receipt_lot_minus
                             ) receipt_lot,
                             variety, gsm, width_size,
                             SUM (dispatch_qty) dispatch_qty,
                             (dispatch_lot_plus - dispatch_lot_minus
                             ) dispatch_lot,
                             sales_return,
                             (sales_lot_plus - sales_lot_minus) sales_lot,
                             stock_return, stock_retrun_lot, item_code
                        FROM (SELECT   creation_date, transaction_type_name,
                                       SUM (receipt_qty) receipt_qty,
                                       COUNT
                                           (receipt_lot_plus)
                                                             receipt_lot_plus,
                                       COUNT
                                          (receipt_lot_minus
                                          ) receipt_lot_minus,
                                       variety, gsm, width_size,
                                       SUM (dispatch_qty) dispatch_qty,
                                       COUNT
                                          (dispatch_lot_plus
                                          ) dispatch_lot_plus,
                                       COUNT
                                          (dispatch_lot_minus
                                          ) dispatch_lot_minus,
                                       SUM (sales_qty) sales_return,
                                       COUNT (sales_lot_plus) sales_lot_plus,
                                       COUNT (sales_lot_minus)
                                                              sales_lot_minus,
                                       COUNT
                                           (stock_retrun_lot)
                                                             stock_retrun_lot,
                                       SUM (stock_return) stock_return,
                                       item_code
                                  FROM (SELECT TRUNC
                                                  (mmt.transaction_date
                                                  ) creation_date,
                                               mtt.transaction_type_name,
                                               msib.segment1 item_code,
                                               (CASE
                                                   WHEN mtt.transaction_type_name NOT IN
                                                          ('Sales order issue',
                                                           'Int Order Intr Ship',
                                                           'RMA Receipt')
                                                      THEN mlot.transaction_quantity
                                                   ELSE 0
                                                END
                                               ) receipt_qty,
                                               (CASE
                                                   WHEN mlot.transaction_quantity >
                                                                             0
                                                   AND mtt.transaction_type_name NOT IN
                                                          ('Sales order issue',
                                                           'Int Order Intr Ship',
                                                           'RMA Receipt')
                                                      THEN mlot.lot_number
                                                   ELSE NULL
                                                END
                                               ) receipt_lot_plus,
                                               (CASE
                                                   WHEN mlot.transaction_quantity <
                                                                             0
                                                   AND mtt.transaction_type_name NOT IN
                                                          ('Sales order issue',
                                                           'Int Order Intr Ship',
                                                           'RMA Receipt')
                                                      THEN mlot.lot_number
                                                   ELSE NULL
                                                END
                                               ) receipt_lot_minus,
                                               SUBSTR (segment1, 4,
                                                       3) variety,
                                               SUBSTR (segment1, 8, 4) gsm,
                                               SUBSTR (segment1,
                                                       12,
                                                       4
                                                      ) width_size,
                                               (CASE
                                                   WHEN mtt.transaction_type_name IN
                                                          ('Sales order issue',
                                                           'Int Order Intr Ship')
                                                      THEN mlot.transaction_quantity
                                                   ELSE 0
                                                END
                                               ) dispatch_qty,
                                               (CASE
                                                   WHEN mlot.transaction_quantity >
                                                                             0
                                                   AND mtt.transaction_type_name IN
                                                          ('Sales order issue',
                                                           'Int Order Intr Ship')
                                                      THEN mlot.lot_number
                                                   ELSE NULL
                                                END
                                               ) dispatch_lot_plus,
                                               (CASE
                                                   WHEN mlot.transaction_quantity <
                                                                             0
                                                   AND mtt.transaction_type_name IN
                                                          ('Sales order issue',
                                                           'Int Order Intr Ship')
                                                      THEN mlot.lot_number
                                                   ELSE NULL
                                                END
                                               ) dispatch_lot_minus,
                                               (CASE
                                                   WHEN mtt.transaction_type_name IN
                                                              ('RMA Receipt')
                                                      THEN mlot.transaction_quantity
                                                   ELSE 0
                                                END
                                               ) sales_qty,
                                               (CASE
                                                   WHEN mlot.transaction_quantity >
                                                                             0
                                                   AND mtt.transaction_type_name IN
                                                              ('RMA Receipt')
                                                      THEN mlot.lot_number
                                                   ELSE NULL
                                                END
                                               ) sales_lot_plus,
                                               (CASE
                                                   WHEN mlot.transaction_quantity <
                                                                             0
                                                   AND mtt.transaction_type_name IN
                                                              ('RMA Receipt')
                                                      THEN mlot.lot_number
                                                   ELSE NULL
                                                END
                                               ) sales_lot_minus,
                                               (CASE
                                                   WHEN mlot.transaction_quantity <
                                                                             0
                                                   AND mtt.transaction_type_name IN
                                                          ('Subinventory Transfer')
                                                   AND mmt.subinventory_code IN
                                                          ('ASRS-1', 'ASRS-2',
                                                           'GODOWN-1')
                                                   AND mmt.transfer_subinventory IN
                                                          ('BUNDLE_ST',
                                                           'BOX_ST',
                                                           'PACK_ST')
                                                      THEN mlot.lot_number
                                                   ELSE NULL
                                                END
                                               ) stock_retrun_lot,
                                               (CASE
                                                   WHEN mtt.transaction_type_name IN
                                                          ('Subinventory Transfer')
                                                   AND mmt.subinventory_code IN
                                                          ('ASRS-1', 'ASRS-2',
                                                           'GODOWN-1')
                                                   AND mmt.transfer_subinventory IN
                                                          ('BUNDLE_ST',
                                                           'BOX_ST',
                                                           'PACK_ST')
                                                   AND mlot.transaction_quantity <
                                                                             0
                                                      THEN mlot.transaction_quantity
                                                   ELSE 0
                                                END
                                               ) stock_return
                                          FROM apps.mtl_system_items_b msib,
                                               apps.mtl_material_transactions mmt,
                                               apps.mtl_transaction_types mtt,
                                               apps.rcv_transactions rt,
                                               apps.mtl_transaction_lot_numbers mlot
                                         WHERE msib.segment1 =
                                                    NVL (p_seg, msib.segment1)
                                           AND SUBSTR (msib.segment1, 1, 1) =
                                                                           'R'
                                           AND msib.inventory_item_id =
                                                         mmt.inventory_item_id
                                           AND msib.organization_id =
                                                           mmt.organization_id
                                           AND mmt.transaction_source_type_id =
                                                  mlot.transaction_source_type_id
                                           AND mmt.inventory_item_id =
                                                        mlot.inventory_item_id
                                           AND mmt.transaction_id =
                                                           mlot.transaction_id
                                           AND msib.inventory_item_id =
                                                         mmt.inventory_item_id
--     And Msib.Segment1 = 'U1Nrdpr006005850910004'
                                           AND rt.transaction_id(+) =
                                                            mmt.transaction_id
                                           AND msib.organization_id =
                                                  NVL (p_org_id,
                                                       msib.organization_id
                                                      )
                                           AND SUBSTR (segment1, 4, 3) =
                                                  NVL (p_variety,
                                                       SUBSTR (segment1, 4, 3)
                                                      )
                                           AND SUBSTR (segment1, 8, 4) =
                                                  NVL (p_gsm,
                                                       SUBSTR (segment1, 8, 4)
                                                      )
                                           AND SUBSTR (segment1, 12, 4) =
                                                  NVL (p_size,
                                                       SUBSTR (segment1, 12,
                                                               4)
                                                      )
                                           AND mtt.transaction_type_id =
                                                       mmt.transaction_type_id
                                           AND mmt.transaction_source_type_id =
                                                  mtt.transaction_source_type_id
                                           AND mmt.transaction_date
                                                  BETWEEN (p_from_date)
                                                      AND (p_to_date))
                              GROUP BY creation_date,
                                       transaction_type_name,
                                       variety,
                                       gsm,
                                       width_size,
                                       item_code)
                    GROUP BY creation_date,
                             receipt_lot_plus,
                             receipt_lot_minus,
                             variety,
                             gsm,
                             width_size,
                             dispatch_lot_plus,
                             dispatch_lot_minus,
                             sales_return,
                             sales_lot_plus,
                             sales_lot_minus,
                             stock_return,
                             stock_retrun_lot,
                             item_code)
          GROUP BY creation_date, variety, gsm, width_size, item_code);
   END;

   BEGIN
      SELECT SUM (stock) stock
        INTO l_opn_balace
        FROM (SELECT   NVL (SUM (mmt.transaction_quantity), 0) AS stock,
                       SUBSTR (segment1, 4, 3) variety,
                       SUBSTR (segment1, 8, 4) p_gsm, msib.segment1,
                       msib.organization_id, SUBSTR (segment1, 12, 4)
                  FROM apps.mtl_system_items_b msib,
                       apps.mtl_material_transactions mmt,
                       apps.rcv_transactions rt
                 WHERE msib.inventory_item_id = mmt.inventory_item_id
                   AND msib.organization_id = mmt.organization_id
                   AND msib.segment1 = NVL (p_seg, msib.segment1)
                   AND SUBSTR (segment1, 4, 3) =
                                      NVL (p_variety, SUBSTR (segment1, 4, 3))
                   AND SUBSTR (segment1, 8, 4) =
                                          NVL (p_gsm, SUBSTR (segment1, 8, 4))
                   AND SUBSTR (segment1, 12, 4) =
                                        NVL (p_size, SUBSTR (segment1, 12, 4))
                   AND rt.transaction_id(+) = mmt.transaction_id
                   AND msib.organization_id =
                                          NVL (p_org_id, msib.organization_id)
                   AND mmt.transaction_date < p_from_date
                   AND transaction_action_id NOT IN (24, 30)
                   AND (logical_transaction = 2 OR logical_transaction IS NULL
                       )
              GROUP BY SUBSTR (segment1, 4, 3),
                       SUBSTR (segment1, 8, 4),
                       msib.organization_id,
                       SUBSTR (segment1, 12, 4),
                       msib.segment1,
                       mmt.transaction_date
              ORDER BY mmt.transaction_date);
   END;

   DBMS_OUTPUT.put_line (l_opn_balace);

   BEGIN
      SELECT COUNT (lot_count)
        INTO l_open_lot                               ----- opening lot number
        FROM (SELECT   CASE
                          WHEN trans_qty <> 0
                             THEN (lot_number)
                       END AS lot_count, item_id, loc
                  FROM (SELECT   SUM (mlot.transaction_quantity) AS trans_qty,
                                 mlot.lot_number AS lot_number,
                                 mmt1.inventory_item_id AS item_id,
                                 mmt1.locator_id AS loc
                            FROM apps.mtl_transaction_lot_numbers mlot,
                                 apps.mtl_material_transactions mmt1,
                                 apps.mtl_system_items_b msib
                           WHERE 1 = 1
--            and mmt1.inventory_item_id    = mlot.inventory_item_id
                             AND msib.inventory_item_id =
                                                        mmt1.inventory_item_id
                             AND msib.organization_id = mmt1.organization_id
                             AND mmt1.transaction_id = mlot.transaction_id
                             AND msib.segment1 = p_seg
                             AND mmt1.subinventory_code IN
                                             ('ASRS-1', 'ASRS-2', 'GODOWN-1')
                             AND mlot.transaction_date < p_from_date
                             AND mlot.organization_id = p_org_id
                        GROUP BY mlot.lot_number,
                                 mmt1.inventory_item_id,
                                 mmt1.locator_id,
                                 mlot.lot_number)
                 WHERE lot_number IS NOT NULL
              GROUP BY trans_qty, item_id, loc, lot_number);
   END;

   FOR i IN c1
   LOOP
      IF p_seg IS NULL
      THEN
         SELECT SUM (stock) stock
           INTO l_open_qty_bal
           FROM (SELECT   NVL (SUM (mmt.transaction_quantity), 0) AS stock,
                          SUBSTR (segment1, 4, 3) variety,
                          SUBSTR (segment1, 8, 4) p_gsm, msib.segment1,
                          msib.organization_id, SUBSTR (segment1, 12, 4)
                     FROM apps.mtl_system_items_b msib,
                          apps.mtl_material_transactions mmt,
                          apps.rcv_transactions rt
                    WHERE msib.inventory_item_id = mmt.inventory_item_id
                      AND msib.organization_id = mmt.organization_id
                      AND msib.segment1 = i.item_code
                      AND SUBSTR (segment1, 4, 3) =
                                      NVL (p_variety, SUBSTR (segment1, 4, 3))
                      AND SUBSTR (segment1, 8, 4) =
                                          NVL (p_gsm, SUBSTR (segment1, 8, 4))
                      AND SUBSTR (segment1, 12, 4) =
                                        NVL (p_size, SUBSTR (segment1, 12, 4))
                      AND rt.transaction_id(+) = mmt.transaction_id
                      AND msib.organization_id =
                                          NVL (p_org_id, msib.organization_id)
                      AND mmt.transaction_date < p_from_date
                      AND transaction_action_id NOT IN (24, 30)
                      AND (   logical_transaction = 2
                           OR logical_transaction IS NULL
                          )
                 GROUP BY SUBSTR (segment1, 4, 3),
                          SUBSTR (segment1, 8, 4),
                          msib.organization_id,
                          SUBSTR (segment1, 12, 4),
                          msib.segment1,
                          mmt.transaction_date
                 ORDER BY mmt.transaction_date);
      ELSE
         l_open_qty_bal := NVL (l_opn_balace, 0);
      END IF;

      IF p_seg IS NULL
      THEN
         SELECT COUNT (lot_count)
           INTO l_openinig_lot                        ----- opening lot number
           FROM (SELECT   CASE
                             WHEN trans_qty <> 0
                                THEN (lot_number)
                          END AS lot_count,
                          item_id, loc
                     FROM (SELECT   SUM
                                       (mlot.transaction_quantity
                                       ) AS trans_qty,
                                    mlot.lot_number AS lot_number,
                                    mmt1.inventory_item_id AS item_id,
                                    mmt1.locator_id AS loc
                               FROM apps.mtl_transaction_lot_numbers mlot,
                                    apps.mtl_material_transactions mmt1,
                                    apps.mtl_system_items_b msib
                              WHERE 1 = 1
--            and mmt1.inventory_item_id    = mlot.inventory_item_id
                                AND msib.inventory_item_id =
                                                        mmt1.inventory_item_id
                                AND msib.organization_id =
                                                          mmt1.organization_id
                                AND mmt1.transaction_id = mlot.transaction_id
                                AND msib.segment1 = i.item_code
                                AND mmt1.subinventory_code IN
                                             ('ASRS-1', 'ASRS-2', 'GODOWN-1')
                                AND mlot.transaction_date < p_from_date
                                AND mlot.organization_id =
                                          NVL (p_org_id, mlot.organization_id)
                           GROUP BY mlot.lot_number,
                                    mmt1.inventory_item_id,
                                    mmt1.locator_id,
                                    mlot.lot_number)
                    WHERE lot_number IS NOT NULL
                 GROUP BY trans_qty, item_id, loc, lot_number);
      ELSE
         DBMS_OUTPUT.put_line ('l_open_qty_bal' || l_open_qty_bal);
         l_openinig_lot := NVL (l_open_lot, 0);
      END IF;

      l_closing_lot :=
           NVL (l_openinig_lot, 0)
         + NVL (i.receipt_lot, 0)
         + NVL (i.dispatch_lot, 0);
      l_close_qty_cal :=
           NVL (l_open_qty_bal, 0)
         + NVL (i.receipt_qty, 0)
         + NVL (i.dispatch_qty, 0);
      DBMS_OUTPUT.put_line ('l_open_qty_bal' || l_open_qty_bal);

      UPDATE TNPL_OM_STOCK_IN_HAND
         SET bal_stock = l_open_qty_bal,
             closing_qty = l_close_qty_cal,
             bal_sto_lot = l_openinig_lot,
             closing_lot = l_closing_lot
       WHERE creation_date = i.creation_date AND item_code = i.item_code;

      l_opn_balace := l_close_qty_cal;
      l_open_lot := l_closing_lot;
   END LOOP;
END;
/

------------------------------BUNDLE--------------------------------------------------------

CREATE OR REPLACE PROCEDURE XXTNPL.tnpl_om_stock_in_hand_bundle (
   p_from_date   IN   DATE DEFAULT NULL,
   p_to_date     IN   DATE DEFAULT NULL,
   p_variety     IN   VARCHAR2 DEFAULT NULL,
   p_gsm         IN   VARCHAR2 DEFAULT NULL,
   p_size        IN   VARCHAR2 DEFAULT NULL,
   p_seg         IN   VARCHAR2 DEFAULT NULL,
   p_org_id      IN   NUMBER DEFAULT NULL
)
IS
--DECLARE
--   p_from_date               DATE          := '1-May-2013';
--   p_to_date            DATE          := '02-May-2013';
--   p_variety            VARCHAR2 (25) := NULL;
--   p_gsm                VARCHAR2 (25) := NULL;
--   p_size               VARCHAR2 (25) := NULL;
--   p_seg                VARCHAR2 (35) := NULL;    --'U3NRDPR008005850910003';
--   p_org_id             NUMBER        := 110;
--   l_opn_balace         NUMBER        := NULL;
   l_opn_balace         NUMBER;
   l_close_qty_cal      NUMBER;
   l_open_qty_bal       NUMBER;
   l_open_qty_balance   NUMBER;
   l_receipt_qty        NUMBER;
   l_openinig_lot       NUMBER;
   l_open_lot           NUMBER;
   l_closing_lot        NUMBER;

   CURSOR c1
   IS
   
      SELECT   *
          FROM TNPL_OM_STOCK_IN_HAND
      ORDER BY creation_date;
BEGIN
   BEGIN
    execute immediate 'truncate table TNPL_OM_STOCK_IN_HAND';
      INSERT INTO TNPL_OM_STOCK_IN_HAND
                  (creation_date, receipt_qty, receipt_lot, variety, gsm,
                   width_size, dispatch_qty, dispatch_lot, sales_return,
                   sales_lot, stock_return, stock_lot, item_code)
         (SELECT   creation_date, SUM (receipt_qty) receipt_qty,
                   ROUND (  NVL ((receipt_qty / NVL (conversion_rate, 1)), 0)
                          * 1000
                         ) receipt_lot,
                   variety, gsm, width_size, SUM (dispatch_qty) dispatch_qty,
                   ROUND (  NVL ((dispatch_qty / NVL (conversion_rate, 1)), 0)
                          * 1000
                         ) dispatch_lot,
                   SUM (sales_return) sales_return,
                   ROUND (  NVL ((sales_return / NVL (conversion_rate, 1)), 0)
                          * 1000
                         ) sales_lot,
                   SUM (stock_return) stock_return,
                   ROUND (  NVL ((stock_return / NVL (conversion_rate, 1)), 0)
                          * 1000
                         ) stock_lot,
                   item_code
              FROM (SELECT   creation_date, conversion_rate,
                             SUM (receipt_qty) receipt_qty, variety, gsm,
                             width_size, SUM (dispatch_qty) dispatch_qty,
                             sales_return, stock_return, item_code
                        FROM (SELECT   creation_date, transaction_type_name,
                                       conversion_rate,
                                       SUM (receipt_qty) receipt_qty, variety,
                                       gsm, width_size,
                                       SUM (dispatch_qty) dispatch_qty,
                                       SUM (sales_qty) sales_return,
                                       SUM (stock_return) stock_return,
                                       item_code
                                  FROM (SELECT TRUNC
                                                  (mmt.transaction_date
                                                  ) creation_date,
                                               msib.segment1 item_code,
                                               mucc.conversion_rate
                                                              conversion_rate,
                                               mtt.transaction_type_name,
                                               (CASE
                                                   WHEN mtt.transaction_type_name NOT IN
                                                          ('Sales order issue',
                                                           'Int Order Intr Ship',
                                                           'RMA Receipt')
                                                      THEN mmt.transaction_quantity
                                                   ELSE 0
                                                END
                                               ) receipt_qty,
                                               SUBSTR (segment1, 4,
                                                       3) variety,
                                               SUBSTR (segment1, 8, 4) gsm,
                                               SUBSTR (segment1,
                                                       12,
                                                       4
                                                      ) width_size,
                                               (CASE
                                                   WHEN mtt.transaction_type_name IN
                                                          ('Sales order issue',
                                                           'Int Order Intr Ship')
                                                      THEN mmt.transaction_quantity
                                                   ELSE 0
                                                END
                                               ) dispatch_qty,
                                               (CASE
                                                   WHEN mtt.transaction_type_name IN
                                                              ('RMA Receipt')
                                                      THEN mmt.transaction_quantity
                                                   ELSE 0
                                                END
                                               ) sales_qty,
                                               (CASE
                                                   WHEN mtt.transaction_type_name IN
                                                          ('Subinventory Transfer')
                                                   AND mmt.subinventory_code IN
                                                          ('ASRS-1', 'ASRS-2',
                                                           'GODOWN-1')
                                                   AND mmt.transfer_subinventory IN
                                                          ('BUNDLE_ST',
                                                           'BOX_ST',
                                                           'PACK_ST')
                                                   AND mmt.transaction_quantity <
                                                                             0
                                                      THEN mmt.transaction_quantity
                                                   ELSE 0
                                                END
                                               ) stock_return
                                          FROM apps.mtl_system_items_b msib,
                                               apps.mtl_material_transactions mmt,
                                               apps.mtl_transaction_types mtt,
                                               apps.rcv_transactions rt,
                                               apps.mtl_uom_class_conversions mucc
                                         WHERE msib.segment1 =
                                                    NVL (p_seg, msib.segment1)
                                           AND SUBSTR (msib.segment1, 1, 1) IN
                                                  ('U', 'P', 'R', 'S', 'X',
                                                   'Z')
                                           AND msib.inventory_item_id =
                                                         mmt.inventory_item_id
                                           AND msib.organization_id =
                                                           mmt.organization_id
                                           AND msib.inventory_item_id =
                                                         mmt.inventory_item_id
                                           AND rt.transaction_id(+) =
                                                            mmt.transaction_id
                                           AND msib.organization_id =
                                                  NVL (p_org_id,
                                                       msib.organization_id
                                                      )
                                           AND SUBSTR (segment1, 4, 3) =
                                                  NVL (p_variety,
                                                       SUBSTR (segment1, 4, 3)
                                                      )
                                           AND SUBSTR (segment1, 8, 4) =
                                                  NVL (p_gsm,
                                                       SUBSTR (segment1, 8, 4)
                                                      )
                                           AND SUBSTR (segment1, 12, 4) =
                                                  NVL (p_size,
                                                       SUBSTR (segment1, 12,
                                                               4)
                                                      )
                                           AND mtt.transaction_type_id =
                                                       mmt.transaction_type_id
                                           AND mmt.transaction_source_type_id =
                                                  mtt.transaction_source_type_id
                                           AND mmt.transaction_date
                                                  BETWEEN (p_from_date)
                                                      AND (p_to_date)
                                           AND mucc.inventory_item_id =
                                                         mmt.inventory_item_id)
                              GROUP BY creation_date,
                                       transaction_type_name,
                                       variety,
                                       gsm,
                                       width_size,
                                       conversion_rate,
                                       item_code)
                    GROUP BY creation_date,
                             variety,
                             gsm,
                             width_size,
                             sales_return,
                             stock_return,
                             conversion_rate,
                             item_code)
          GROUP BY creation_date,
                   variety,
                   gsm,
                   width_size,
                   receipt_qty,
                   conversion_rate,
                   dispatch_qty,
                   sales_return,
                   stock_return,
                   item_code);
   END;

   BEGIN
      SELECT stock
        INTO l_opn_balace
        FROM (SELECT   NVL (SUM (mmt.transaction_quantity), 0) AS stock
                  FROM apps.mtl_system_items_b msib,
                       apps.mtl_material_transactions mmt,
                       apps.rcv_transactions rt
                 WHERE msib.inventory_item_id = mmt.inventory_item_id
                   AND msib.organization_id = mmt.organization_id
                   AND msib.segment1 = NVL (p_seg, msib.segment1)
                   AND rt.transaction_id(+) = mmt.transaction_id
                   AND msib.organization_id =
                                          NVL (p_org_id, msib.organization_id)
                   AND mmt.transaction_date < p_from_date
                   AND transaction_action_id NOT IN (24, 30)
                   AND (logical_transaction = 2 OR logical_transaction IS NULL
                       )
              ORDER BY mmt.transaction_date);
   END;

   BEGIN
      SELECT SUM (stock) stock
        INTO l_open_lot
        FROM (SELECT ROUND (NVL ((stock / NVL (conversion_rate, 1)), 0) * 1000
                           ) stock
                FROM (SELECT   NVL ((mmt.transaction_quantity), 0) AS stock,
                               uom.conversion_rate
                          FROM apps.mtl_system_items_b msib,
                               apps.mtl_material_transactions mmt,
                               apps.rcv_transactions rt,
                               apps.mtl_uom_class_conversions uom
                         WHERE msib.inventory_item_id = mmt.inventory_item_id
                           AND msib.organization_id = mmt.organization_id
                           AND msib.segment1 = NVL (p_seg, msib.segment1)
                           AND rt.transaction_id(+) = mmt.transaction_id
                           AND msib.organization_id =
                                          NVL (p_org_id, msib.organization_id)
                           AND mmt.transaction_date < p_from_date
                           AND uom.inventory_item_id = mmt.inventory_item_id
                           AND transaction_action_id NOT IN (24, 30)
                           AND (   logical_transaction = 2
                                OR logical_transaction IS NULL
                               )
                      --group by uom.conversion_rate , mmt.transaction_date
                      ORDER BY mmt.transaction_date));
   END;

   DBMS_OUTPUT.put_line (l_opn_balace);

   FOR i IN c1
   LOOP
      IF p_seg IS NULL
      THEN
         SELECT stock
           INTO l_opn_balace
           FROM (SELECT   NVL (SUM (mmt.transaction_quantity), 0) AS stock
                     FROM apps.mtl_system_items_b msib,
                          apps.mtl_material_transactions mmt,
                          apps.rcv_transactions rt
                    WHERE msib.inventory_item_id = mmt.inventory_item_id
                      AND msib.organization_id = mmt.organization_id
                      AND msib.segment1 = i.item_code
                      AND rt.transaction_id(+) = mmt.transaction_id
                      AND msib.organization_id =
                                          NVL (p_org_id, msib.organization_id)
                      AND mmt.transaction_date < p_from_date
                      AND transaction_action_id NOT IN (24, 30)
                      AND (   logical_transaction = 2
                           OR logical_transaction IS NULL
                          )
                 ORDER BY mmt.transaction_date);
      ELSE
         l_open_qty_bal := NVL (l_opn_balace, 0);
      END IF;

      DBMS_OUTPUT.put_line ('l_open_qty_bal' || l_open_qty_bal);

      IF p_seg IS NULL
      THEN
         SELECT SUM (stock) stock
           INTO l_openinig_lot
           FROM (SELECT ROUND (  NVL ((stock / NVL (conversion_rate, 1)), 0)
                               * 1000
                              ) stock
                   FROM (SELECT   NVL ((mmt.transaction_quantity),
                                       0
                                      ) AS stock,
                                  uom.conversion_rate
                             FROM apps.mtl_system_items_b msib,
                                  apps.mtl_material_transactions mmt,
                                  apps.rcv_transactions rt,
                                  apps.mtl_uom_class_conversions uom
                            WHERE msib.inventory_item_id =
                                                         mmt.inventory_item_id
                              AND msib.organization_id = mmt.organization_id
                              AND msib.segment1 = i.item_code
                              AND rt.transaction_id(+) = mmt.transaction_id
                              AND msib.organization_id =
                                          NVL (p_org_id, msib.organization_id)
                              AND mmt.transaction_date < p_from_date
                              AND uom.inventory_item_id =
                                                         mmt.inventory_item_id
                              AND transaction_action_id NOT IN (24, 30)
                              AND (   logical_transaction = 2
                                   OR logical_transaction IS NULL
                                  )
                         --group by uom.conversion_rate , mmt.transaction_date
                         ORDER BY mmt.transaction_date));
      ELSE
         DBMS_OUTPUT.put_line (l_open_lot);
         l_openinig_lot := NVL (l_open_lot, 0);
         DBMS_OUTPUT.put_line ('l_openinig_lot' || l_openinig_lot);
      END IF;

      ----      DBMS_OUTPUT.put_line ('l_open_lot' || l_open_lot);
      l_closing_lot :=
           NVL (l_openinig_lot, 0)
         + NVL (i.receipt_lot, 0)
         + NVL (i.dispatch_lot, 0);
      l_open_qty_bal := NVL (l_opn_balace, 0);
      DBMS_OUTPUT.put_line (l_open_qty_bal);
      l_close_qty_cal :=
           NVL (l_open_qty_bal, 0)
         + NVL (i.receipt_qty, 0)
         + NVL (i.dispatch_qty, 0);

      UPDATE TNPL_OM_STOCK_IN_HAND
         SET bal_stock = l_open_qty_bal,
             closing_qty = l_close_qty_cal,
             bal_sto_lot = l_openinig_lot,
             closing_lot = l_closing_lot
       WHERE creation_date = i.creation_date AND item_code = i.item_code;

      l_opn_balace := l_close_qty_cal;
      l_open_lot := l_closing_lot;
   END LOOP;
END;
/