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

GL TO AP QUERY

select * from
(select (select vendor_name from ap_suppliers where vendor_id=b.vendor_id) supplier,
b.invoice_id,b.INVOICE_NUM,b.voucher_no,b.SOURCE,b.INVOICE_TYPE_LOOKUP_CODE type,a.APPLIED_TO_SOURCE_ID_NUM_1,a.ACCOUNT_CODE,
--a.JE_CATEGORY_NAME,
nvl(sum(a.DR),0)-nvl(sum(a.CR),0) diff,
sum(a.DR) dr,sum(a.CR) cr
from
(
select  xah.ae_header_id,xal.ae_line_num,xdl.APPLIED_TO_SOURCE_ID_NUM_1,gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4 account_Code,xah.JE_CATEGORY_NAME,xal.ACCOUNTED_DR,xal.ACCOUNTED_cr,
sum(xdl.UNROUNDED_ACCOUNTED_DR) dr,sum(xdl.UNROUNDED_ACCOUNTED_CR) cr--,sum(xal.ACCOUNTED_DR),sum(xal.ACCOUNTED_cr)
from gl_import_references gir,xla_ae_lines xal,xla_ae_headers xah,gl_je_headers gjh,gl_je_lines gjl,gl_code_combinations gcc,
xla_distribution_links xdl--,
--ap_invoices_All aia
--xla.xla_events xev,
--xla.xla_transaction_entities XTE
where
--je_header_id=415326 and je_line_num=3
--and
 gir.GL_SL_LINK_ID=xal.GL_SL_LINK_ID
AND
gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE
AND XAH.ae_header_id = XAL.ae_header_id
AND XAH.gl_transfer_status_code= 'Y'
and trunc(gjl.effective_date) <= :to_date
and xal.code_combination_id in (SELECT code_Combination_id from gl_Code_combinations where segment4=:account_code)-- (26332,26347,48810,76059,77278,164197)
and gir.je_header_id=gjh.je_header_id
and gjh.je_header_id=gjl.je_header_id
and gir.je_header_id=gjl.je_header_id
and gir.je_line_num=gjl.je_line_num
and gjl.code_combination_id=gcc.code_combination_id
--AND xah.event_id= xev.event_id
--AND xev.entity_id= xte.entity_id
--AND xah.entity_id= xte.entity_id
and xah.ae_header_id=xdl.ae_header_id
and xal.ae_line_num=xdl.ae_line_num
and xal.ae_header_id=xdl.ae_header_id
--and aia.INVOICE_ID = xte.source_id_int_1(+)
AND gjh.STATUS='P'
AND gjh.Actual_flag='A'
AND XAH.gl_transfer_status_code= 'Y'
--and xdl.APPLIED_TO_SOURCE_ID_NUM_1=aia.invoice_id
group by
xah.ae_header_id,xal.ae_line_num,xdl.APPLIED_TO_SOURCE_ID_NUM_1,gcc.segment1,gcc.segment2,gcc.segment3,gcc.segment4,xah.JE_CATEGORY_NAME,xal.ACCOUNTED_DR,xal.ACCOUNTED_cr
) a,
(select aia.invoice_id,aia.invoice_num,aia.vendor_id,aia.source,aia.invoice_type_lookup_code,aia.DOC_SEQUENCE_VALUE voucher_no from ap_invoices_all aia) b
where
a.APPLIED_TO_SOURCE_ID_NUM_1=b.invoice_id(+)
group by
b.invoice_id,b.INVOICE_NUM,b.vendor_id,b.voucher_no,b.SOURCE,b.INVOICE_TYPE_LOOKUP_CODE,a.APPLIED_TO_SOURCE_ID_NUM_1,a.ACCOUNT_CODE--,a.JE_CATEGORY_NAME
union all
select
null supplier_name,null invoice_id,null INVOICE_NUM,null voucher_no,glh.je_source SOURCE,null  type,null APPLIED_TO_SOURCE_ID_NUM_1,
gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4 ACCOUNT_CODE,--glh.je_Category JE_CATEGORY_NAME,
nvl(sum(gjl.accounted_dr),0)-nvl(sum(gjl.accounted_cr),0) diff,
sum(gjl.accounted_dr) dr ,
sum(gjl.accounted_cr) CR
from gl_je_headers glh,gl_je_lines gjl,gl_code_combinations gcc  
where
glh.je_header_id=gjl.je_header_id
and
 trunc(gjl.effective_date) <= :to_date
and gjl.code_combination_id in (SELECT code_Combination_id from gl_Code_combinations where segment4=:account_code)
and glh.JE_SOURCE in ('Spreadsheet','Manual')
and gjl.code_Combination_id=gcc.code_combination_id
group by
glh.je_source,--glh.je_Category,
gcc.segment1,gcc.segment2,gcc.segment3,gcc.segment4
)
where
diff<>0
order by
applied_to_source_id_num_1