CREATE TABLE ot_inspect_head 
(inh_sys NUMBER,i_txn_code VARCHAR2(12),i_no NUMBER,i_ref_txn VARCHAR2(20),i_ref_no NUMBER);

CREATE SEQUENCE inh_sys START WITH 1;

CREATE TABLE ot_inspect_item 
(ii_inh_sys NUMBER , ii_pi_sys NUMBER,ii_sys NUMBER,ii_item_cd VARCHAR2(12),
ii_grade VARCHAR2(12),ii_qty number, ii_flex_01 VARCHAR2(12),ii_flex01_qty number,
ii_flex_02 VARCHAR2(12),ii_flex02_qty number,ii_flex_03 VARCHAR2(12),ii_flex03_qty number);

CREATE SEQUENCE ii_sys START WITH 1;

insert into ot_po values ('ss-po',1,ph_sys.nextval);

insert into ot_inspect_head values (inh_sys.nextval,'ss-ins',1,'ss-po',1);

commit;

select * from ot_inspect_item 

II_INH_SYS II_PI_SYS II_SYS II_ITE II_GRADE II_QTY II_FLEX_01 II_FLEX01_QTY II_FLEX_02 II_FLEX02_QTY
---------- --------- ------ ------ -------- ------ ---------- ------------- ---------- -------------
         2         1      2 HEA100 A           100 


--Now if the inspection user issues the update statement , it will delete this row 
--from ot_inspect_item and reinserts the values with values based on 
--ii_flex_01,ii_flex_02,ii_flex_03

update ot_inspect_item 
set ii_flex_01='X',II_FLEX01_QTY=25,ii_flex_02='Y',II_FLEX02_QTY=25,ii_flex_03='Z',II_FLEX03_QTY=50
WHERE II_INH_SYS=2;

commit;

--it will create three records if he updates all ii_flex_01,ii_flex_02,ii_flex_03 , 
--if he inserts 2 fields then 2 records i want output like below.


II_INH_SYS II_PI_SYS II_SYS II_ITE II_GRADE II_QTY II_FLEX_01 II_FLEX01_QTY II_FLEX_02 II_FLEX02_QTY
---------- --------- ------ ------ -------- ------ ---------- ------------- ---------- -------------
         2         1      2 HEA100 X           25 
         2         1      2 HEA100 Y           25
         2         1      2 HEA100 Z           50

--If he updates like below

update ot_inspect_item set ii_flex_01='X',II_FLEX01_QTY=50,ii_flex_02='Y',II_FLEX02_QTY=50
WHERE II_INH_SYS=2;

commit;

--then the output should be like or what i want is
II_INH_SYS II_PI_SYS II_SYS II_ITE II_GRADE II_QTY II_FLEX_01 II_FLEX01_QTY II_FLEX_02 II_FLEX02_QTY
---------- --------- ------ ------ -------- ------ ---------- ------------- ---------- -------------
         2         1      2 HEA100 X           50 
         2         1      2 HEA100 Y           50


--I have written a proceedure by taking the backup of existing table into a temporary 
-- table and then inserting the new values as in procedure but i don't know whether 
-- my approach is right or i am following a right method, please correct me.

CREATE TABLE ot_inspect_item_bk AS SELECT * FROM ot_inspect_item WHERE 1=2

CREATE OR REPLACE PROCEDURE aft_upd_poN (p_h_sys IN NUMBER)
AS
   CURSOR c1
   IS
      SELECT ii_inh_sys, ii_pi_sys, ii_sys, ii_item_cd, ii_grade, ii_qty,
             ii_flex_01, ii_flex01_qty, ii_flex_02, ii_flex02_qty,
             ii_flex_03, ii_flex03_qty
        FROM ot_inspect_item
       WHERE ii_inh_sys = p_h_sys;

   CURSOR c2
   IS
      SELECT 1
        FROM ot_inspect_item
       WHERE     ii_inh_sys = p_h_sys
             AND NVL (ii_grade, 'X') <> NVL (ii_flex_01, 'X')
          OR NVL (ii_grade, 'X') <> NVL (ii_flex_02, 'X')
          OR NVL (ii_grade, 'X') <> NVL (ii_flex_03, 'X');

   CURSOR c3
   IS
      SELECT *
        FROM ot_inspect_item_bk
       WHERE ii_inh_sys = p_h_sys;

   m_ri_sys_id    NUMBER (20);
   m_fnd          NUMBER (1);
   m_req_qty_bu   NUMBER (20);
BEGIN
   INSERT INTO ot_inspect_item_bk
      SELECT *
        FROM ot_inspect_item
       WHERE ii_inh_sys = p_h_sys;

   IF c2%ISOPEN
   THEN
      CLOSE c2;
   END IF;

   m_fnd := NULL;

   OPEN c2;

   FETCH c2
    INTO m_fnd;

   IF c2%FOUND
   THEN
      DELETE      ot_inspect_item
            WHERE ii_inh_sys = p_h_sys;
   END IF;

   CLOSE c2;

   FOR j IN c3
   LOOP
      IF     j.ii_flex_01 IS NOT NULL THEN
         INSERT INTO ot_inspect_item
                     (ii_inh_sys, ii_pi_sys, ii_sys, ii_item_cd,
                      ii_grade, ii_qty, ii_flex_01,
                      ii_flex01_qty, ii_flex_02, ii_flex02_qty,
                      ii_flex_03, ii_flex03_qty
                     )
              VALUES (j.ii_inh_sys, j.ii_pi_sys, ii_sys.nextval, j.ii_item_cd,
                      j.ii_flex_01, j.ii_flex01_qty, j.ii_flex_01,
                      j.ii_flex01_qty, j.ii_flex_02, j.ii_flex02_qty,
                      j.ii_flex_03, j.ii_flex03_qty
                     );
     END IF;

     IF  j.ii_flex_02 IS NOT NULL THEN

         INSERT INTO ot_inspect_item
                     (ii_inh_sys, ii_pi_sys, ii_sys, ii_item_cd,
                      ii_grade, ii_qty, ii_flex_01,
                      ii_flex01_qty, ii_flex_02, ii_flex02_qty,
                      ii_flex_03, ii_flex03_qty
                     )
              VALUES (j.ii_inh_sys, j.ii_pi_sys, ii_sys.nextval, j.ii_item_cd,
                      j.ii_flex_02, j.ii_flex02_qty, j.ii_flex_01,
                      j.ii_flex01_qty, j.ii_flex_02, j.ii_flex02_qty,
                      j.ii_flex_03, j.ii_flex03_qty
                     );
      END IF;
      
      IF  j.ii_flex_03 IS NOT NULL THEN

         INSERT INTO ot_inspect_item
                     (ii_inh_sys, ii_pi_sys, ii_sys, ii_item_cd,
                      ii_grade, ii_qty, ii_flex_01,
                      ii_flex01_qty, ii_flex_02, ii_flex02_qty,
                      ii_flex_03, ii_flex03_qty
                     )
              VALUES (j.ii_inh_sys, j.ii_pi_sys, ii_sys.nextval, j.ii_item_cd,
                      j.ii_flex_03, j.ii_flex03_qty, j.ii_flex_01,
                      j.ii_flex01_qty, j.ii_flex_02, j.ii_flex02_qty,
                      j.ii_flex_03, j.ii_flex03_qty
                     );
     END IF;
      
   END LOOP;
END;
/

exec aft_upd_po (2)


SQL> select * from ot_inspect_item_bk
  2  /

II_INH_SYS II_PI_SYS II_SYS II_ITE II_GRADE II_QTY II_FLEX_01 II_FLEX01_QTY II_FLEX_02 II_FLEX02_QTY ii_flex_03 ii_flex03_qty 
---------- --------- ------ ------ -------- ------ ---------- ------------- ---------- ------------------------
         2         1      2 HEA100 A           100 X                     25 Y                     25 Z   50

SQL> select * from ot_inspect_item;

II_INH_SYS II_PI_SYS II_SYS II_ITE II_GRADE II_QTY II_FLEX_01 II_FLEX01_QTY II_FLEX_02 II_FLEX02_QTY ii_flex_03 ii_flex03_qty
---------- --------- ------ ------ -------- ------ ---------- ------------- ---------- ------------- ----------
         2         1      3 HEA100 X            25 X                     25 Y                     25 Z   50
         2         1      4 HEA100 Y            25 X                     25 Y                     25 Z   50
         2         1      5 HEA100 Z            50 X                     25 Y                     25 Z   50

CREATE TABLE ot_inspect_item
  (
     ii_inh_sys            NUMBER,
     ii_pi_sys             NUMBER,
     ii_sys                NUMBER,
     ii_item_cd            VARCHAR2(12),
     order_or_receipt_flag VARCHAR2(1),
     grade                 VARCHAR2(12),
     qty                   NUMBER
  );