Home » SQL & PL/SQL » SQL & PL/SQL » complex delete and insert (2 Merged) (Oracle 10.2.0.1.0)
complex delete and insert (2 Merged) [message #567016] Sat, 22 September 2012 08:49 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I have three tables , ot_ins_item,ot_ins_heat,ot_ins_batch .User will initially enter the data in ot_ins_item,then in ot_ins_heat and ot_ins_batch , after executing my procedure the data in ot_ins_item will get replaced based on data in ot_ins_heat and ot_ins_batch, even the data in ot_ins_heat and ot_ins_batch will also be one is to one, i managed to get it done based on inputs provided but my problem comes when user input 2 batches in ot_ins_batch.


CREATE TABLE OT_INS_ITEM
(
  II_ID       NUMBER,
  II_ITEM_CD  VARCHAR2(12),
  II_HEAT_CD  VARCHAR2(12),
  II_QTY      NUMBER
);

ALTER TABLE OT_INS_ITEM ADD (
  CONSTRAINT OT_INS_ITEM_UK
 UNIQUE (II_ID));

CREATE SEQUENCE II_ID START WITH 1 ;
 
 CREATE TABLE OT_INS_HEAT
(
  IH_ID     NUMBER,
  IH_II_ID  NUMBER,
  IH_HEAT   VARCHAR2(12),
  IH_QTY    NUMBER
);

ALTER TABLE OT_INS_HEAT ADD (
  CONSTRAINT OT_INS_HEAT_FK_1 
 FOREIGN KEY (IH_II_ID) 
 REFERENCES OT_INS_ITEM (II_ID)); 
 
CREATE SEQUENCE IH_ID START WITH 1 ; 

CREATE TABLE OT_INS_BATCH
(
  IB_ID     NUMBER,
  IB_II_ID  NUMBER,
  IB_BATCH  VARCHAR2(12 BYTE),
  IB_QTY    NUMBER
)

ALTER TABLE OT_INS_BATCH ADD (
  CONSTRAINT OT_INS_BATCH_PK
 PRIMARY KEY
 (IB_ID));
 
ALTER TABLE OT_INS_BATCH ADD (
  CONSTRAINT OT_INS_BATCH_FK_1 
 FOREIGN KEY (IB_II_ID) 
 REFERENCES OT_INS_ITEM (II_ID)); 
 
CREATE SEQUENCE IB_ID START WITH 1 ; 

INSERT INTO OT_INS_ITEM VALUES (II_ID.NEXTVAL,'ITEM1','A',4);

INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,1,'X',1);

INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,1,'Y',1);

INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,1,'Z',2);

INSERT INTO OT_INS_BATCH VALUES (IB_ID.NEXTVAL,1,'XXX',4);

COMMIT;

---NOW DISLAYING DATA BEFORE EXECUTING INSP_HEAT_CHG2

SQL> SET PAGES 20000
SQL> SET LINES 2000
SQL> SELECT * FROM OT_INS_ITEM;

    II_ID II_ITEM_CD   II_HEAT_CD      II_QTY
--------- ------------ ------------ ---------
        1 ITEM1        A                    4

SQL> SELECT  * FROM OT_INS_HEAT
  2  /

    IH_ID  IH_II_ID IH_HEAT         IH_QTY
--------- --------- ------------ ---------
        1         1 X                    1
        2         1 Y                    1
        3         1 Z                    2

SQL> SELECT * FROM OT_INS_BATCH
  2  /

    IB_ID  IB_II_ID IB_BATCH        IB_QTY
--------- --------- ------------ ---------
        1         1 XXX                  4


--Now procedure

CREATE OR REPLACE PROCEDURE ORION2007.insp_heat_chg2 (p_sys_id NUMBER)
IS
   CURSOR c3
   IS
      SELECT   ih_id, ih_ii_id, NVL (ih_heat, 'A') new_grade,
               SUM (ih_qty) reqd_qty
          FROM ot_ins_heat_bk
         WHERE ih_ii_id = p_sys_id
      GROUP BY NVL (ih_heat, 'A'), ih_ii_id, ih_id;

   CURSOR c4
   IS
      SELECT *
        FROM ot_ins_item_bk
       WHERE ii_id = p_sys_id;

   CURSOR c5
   IS
      SELECT *
        FROM ot_ins_batch_bk
       WHERE ib_ii_id = p_sys_id;

   CURSOR c6 (m_grade VARCHAR2)
   IS
      SELECT *
        FROM ot_ins_item
       WHERE ii_heat_cd = m_grade;

   m_ii_sys_id   NUMBER (20);
   m_ii_qty_bu   NUMBER (20);
BEGIN
   INSERT INTO ot_ins_item_bk
      SELECT *
        FROM ot_ins_item
       WHERE ii_id = p_sys_id;

   INSERT INTO ot_ins_batch_bk
      SELECT *
        FROM ot_ins_batch
       WHERE ib_ii_id = p_sys_id;

   INSERT INTO ot_ins_heat_bk
      SELECT *
        FROM ot_ins_heat
       WHERE ih_ii_id = p_sys_id;

   DELETE      ot_ins_batch
         WHERE ib_ii_id = p_sys_id;

   DELETE      ot_ins_heat
         WHERE ih_ii_id = p_sys_id;

   DELETE      ot_ins_item
         WHERE ii_id = p_sys_id;

   FOR i IN c3
   LOOP
      SELECT ii_id.NEXTVAL
        INTO m_ii_sys_id
        FROM DUAL;

      FOR j IN c4
      LOOP
         INSERT INTO ot_ins_item
                     (ii_id, ii_item_cd, ii_heat_cd, ii_qty
                     )
              VALUES (m_ii_sys_id, j.ii_item_cd, i.new_grade, i.reqd_qty
                     );
      END LOOP;

      FOR r IN c6 (i.new_grade)
      LOOP
         INSERT INTO ot_ins_heat
                     (ih_id, ih_ii_id, ih_heat, ih_qty
                     )
              VALUES (ih_id.NEXTVAL, m_ii_sys_id, i.new_grade, i.reqd_qty
                     );
      END LOOP;

      FOR k IN c5
      LOOP
         INSERT INTO ot_ins_batch
                     (ib_id, ib_ii_id, ib_batch, ib_qty
                     )
              VALUES (ib_id.NEXTVAL, m_ii_sys_id, k.ib_batch, i.reqd_qty
                     );
      END LOOP;
   END LOOP;
END;
/


exec insp_heat_chg2(1)

commit;


--after execution the data in tables will be as below

SQL> select  * from ot_ins_item;

    II_ID II_ITEM_CD   II_HEAT_CD      II_QTY
--------- ------------ ------------ ---------
       21 ITEM1        X                    1
       22 ITEM1        Z                    2
       23 ITEM1        Y                    1

SQL> select * from ot_ins_heat;

    IH_ID  IH_II_ID IH_HEAT         IH_QTY
--------- --------- ------------ ---------
       21        21 X                    1
       22        22 Z                    2
       23        23 Y                    1

SQL> select *
  2  from ot_ins_batch;

    IB_ID  IB_II_ID IB_BATCH        IB_QTY
--------- --------- ------------ ---------
       21        21 XXX                  1
       22        22 XXX                  2
       23        23 XXX                  1

---the requirement is or the problem is if the user enters two batches as below
-- the qty should be splitted according to ot_ins_heat qty and ii_id

INSERT INTO OT_INS_ITEM VALUES (II_ID.NEXTVAL,'ITEM2','B',4);

INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,24,'E',1);

INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,24,'F',1);

INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,24,'G',2);

INSERT INTO OT_INS_BATCH VALUES (IB_ID.NEXTVAL,24,'YYY',2);

INSERT INTO OT_INS_BATCH VALUES (IB_ID.NEXTVAL,24,'ZZZ',2);
COMMIT;

--The required output in batch table should be as below


    IB_ID  IB_II_ID IB_BATCH        IB_QTY
--------- --------- ------------ ---------
                    YYY                  1
                    YYY                  1
                    ZZZ                  2



[EDITED by LF: removed superfluous empty lines]

[Updated on: Tue, 25 September 2012 13:26] by Moderator

Report message to a moderator

Re: complex delete and insert (2 Merged) [message #567026 is a reply to message #567016] Sat, 22 September 2012 17:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8007
Registered: November 2002
Location: California, USA
Senior Member
Your procedure refers to tables that you have not provided (ot_ins_item_bk, ot_ins_heat_bk, ot_ins_batch_bk).
Re: complex delete and insert (2 Merged) [message #567027 is a reply to message #567016] Sat, 22 September 2012 17:27 Go to previous messageGo to next message
spacebar
Messages: 26
Registered: February 2007
Junior Member
Shouldn't your "c5" cursor be like the below so you will get total qty by batch/item?
cusror c5 is 
  select ib_id, ib_ii_id, ib_batch, sum( ib_qty ) as ib_qty
   from  ot_ins_batch_bk
  where  ib_ii_id  =  p_sys_id
  group by ib_id, ib_ii_id, ib_batch;
Re: complex delete and insert (2 Merged) [message #567044 is a reply to message #567026] Sun, 23 September 2012 11:18 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Quote:
Your procedure refers to tables that you have not provided (ot_ins_item_bk, ot_ins_heat_bk, ot_ins_batch_bk).


I am really sorry mam for including the table structures for the following tables which are in my procedure , i am actually using them like backup table before deleting. here are they


OT_INS_ITEM_BK

CREATE TABLE OT_INS_ITEM_BK
(
  II_ID       NUMBER,
  II_ITEM_CD  VARCHAR2,
  II_HEAT_CD  VARCHAR2,
  II_QTY      NUMBER
)


ot_ins_heat_bk


CREATE TABLE OT_INS_HEAT_BK
(
  IH_ID     NUMBER,
  IH_II_ID  NUMBER,
  IH_HEAT   VARCHAR2(12),
  IH_QTY    NUMBER
)


ot_ins_batch_bk


CREATE TABLE OT_INS_BATCH_BK
(
  IB_ID     NUMBER,
  IB_II_ID  NUMBER,
  IB_BATCH  VARCHAR2(12),
  IB_QTY    NUMBER
)



Re: complex delete and insert (2 Merged) [message #567280 is a reply to message #567044] Wed, 26 September 2012 02:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8007
Registered: November 2002
Location: California, USA
Senior Member
The following produces the desired results, eliminates the need for 3 extra tables, and simplifies the procedure, but this is still a bad design.

-- tables, constraints, sequences, and starting data:
SCOTT@orcl_11gR2> CREATE TABLE OT_INS_ITEM
  2  (
  3    II_ID	   NUMBER,
  4    II_ITEM_CD  VARCHAR2(12),
  5    II_HEAT_CD  VARCHAR2(12),
  6    II_QTY	   NUMBER
  7  )
  8  /

Table created.

SCOTT@orcl_11gR2> ALTER TABLE OT_INS_ITEM ADD (
  2    CONSTRAINT OT_INS_ITEM_UK
  3   UNIQUE (II_ID))
  4  /

Table altered.

SCOTT@orcl_11gR2> CREATE SEQUENCE II_ID START WITH 1
  2  /

Sequence created.

SCOTT@orcl_11gR2>  CREATE TABLE OT_INS_HEAT
  2  (
  3    IH_ID	 NUMBER,
  4    IH_II_ID  NUMBER,
  5    IH_HEAT	 VARCHAR2(12),
  6    IH_QTY	 NUMBER
  7  )
  8  /

Table created.

SCOTT@orcl_11gR2> ALTER TABLE OT_INS_HEAT ADD (
  2    CONSTRAINT OT_INS_HEAT_FK_1
  3   FOREIGN KEY (IH_II_ID)
  4   REFERENCES OT_INS_ITEM (II_ID))
  5  /

Table altered.

SCOTT@orcl_11gR2> CREATE SEQUENCE IH_ID START WITH 1
  2  /

Sequence created.

SCOTT@orcl_11gR2> CREATE TABLE OT_INS_BATCH
  2  (
  3    IB_ID	 NUMBER,
  4    IB_II_ID  NUMBER,
  5    IB_BATCH  VARCHAR2(12 BYTE),
  6    IB_QTY	 NUMBER
  7  )
  8  /

Table created.

SCOTT@orcl_11gR2> ALTER TABLE OT_INS_BATCH ADD (
  2    CONSTRAINT OT_INS_BATCH_PK
  3   PRIMARY KEY
  4   (IB_ID))
  5  /

Table altered.

SCOTT@orcl_11gR2> ALTER TABLE OT_INS_BATCH ADD (
  2    CONSTRAINT OT_INS_BATCH_FK_1
  3   FOREIGN KEY (IB_II_ID)
  4   REFERENCES OT_INS_ITEM (II_ID))
  5  /

Table altered.

SCOTT@orcl_11gR2> CREATE SEQUENCE IB_ID START WITH 1
  2  /

Sequence created.

SCOTT@orcl_11gR2> BEGIN
  2    INSERT INTO OT_INS_ITEM VALUES (II_ID.NEXTVAL,'ITEM1','A',4);
  3    INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,II_ID.CURRVAL,'X',1);
  4    INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,II_ID.CURRVAL,'Y',1);
  5    INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,II_ID.CURRVAL,'Z',2);
  6    INSERT INTO OT_INS_BATCH VALUES (IB_ID.NEXTVAL,II_ID.CURRVAL,'XXX',4);
  7    INSERT INTO OT_INS_ITEM VALUES (II_ID.NEXTVAL,'ITEM2','B',4);
  8    INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,II_ID.CURRVAL,'E',1);
  9    INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,II_ID.CURRVAL,'F',1);
 10    INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,II_ID.CURRVAL,'G',2);
 11    INSERT INTO OT_INS_BATCH VALUES (IB_ID.NEXTVAL,II_ID.CURRVAL,'YYY',2);
 12    INSERT INTO OT_INS_BATCH VALUES (IB_ID.NEXTVAL,II_ID.CURRVAL,'ZZZ',2);
 13    COMMIT;
 14  END;
 15  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> SELECT * FROM OT_INS_ITEM ORDER BY ii_id
  2  /

     II_ID II_ITEM_CD II_HEAT_CD       II_QTY
---------- ---------- ------------ ----------
         1 ITEM1      A                     4
         2 ITEM2      B                     4

2 rows selected.

SCOTT@orcl_11gR2> SELECT  * FROM OT_INS_HEAT ORDER BY ih_id
  2  /

     IH_ID   IH_II_ID IH_HEAT     IH_QTY
---------- ---------- ------- ----------
         1          1 X                1
         2          1 Y                1
         3          1 Z                2
         4          2 E                1
         5          2 F                1
         6          2 G                2

6 rows selected.

SCOTT@orcl_11gR2> SELECT * FROM OT_INS_BATCH ORDER BY ib_id
  2  /

     IB_ID   IB_II_ID IB_BATCH     IB_QTY
---------- ---------- -------- ----------
         1          1 XXX               4
         2          2 YYY               2
         3          2 ZZZ               2

3 rows selected.


-- procedure:
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE insp_heat_chg2 (p_sys_id NUMBER)
  2  IS
  3    v_ib_qty  NUMBER := 0;
  4    v_ih_qty  NUMBER := 0;
  5  BEGIN
  6    FOR r IN
  7  	 (SELECT i.ii_id, i.ii_item_cd,
  8  		 h.ih_id, h.ih_heat, h.ih_qty,
  9  		 b.ib_id, b.ib_batch, b.ib_qty,
 10  		 ROW_NUMBER () OVER (PARTITION BY i.ii_id, b.ib_id ORDER BY h.ih_id) rn
 11  	  FROM	 ot_ins_item i, ot_ins_heat h, ot_ins_batch b
 12  	  WHERE  i.ii_id = h.ih_ii_id
 13  	  AND	 i.ii_id = b.ib_ii_id
 14  	  AND	 i.ii_id = p_sys_id
 15  	  ORDER  BY i.ii_id, b.ib_id, h.ih_id)
 16    LOOP
 17  	 IF r.rn = 1 THEN
 18  	   v_ib_qty := v_ib_qty + r.ib_qty;
 19  	   v_ih_qty := v_ih_qty * -1;
 20  	 END IF;
 21  	 IF v_ih_qty < 0 THEN
 22  	   v_ih_qty := v_ih_qty + r.ih_qty;
 23  	 ELSIF v_ib_qty - v_ih_qty > 0 THEN
 24  	   INSERT INTO ot_ins_item (ii_id, ii_item_cd, ii_heat_cd, ii_qty)
 25  	   VALUES (ii_id.NEXTVAL, r.ii_item_cd, r.ih_heat, r.ih_qty);
 26  	   INSERT INTO ot_ins_heat (ih_id, ih_ii_id, ih_heat, ih_qty)
 27  	   VALUES (ih_id.NEXTVAL, ii_id.CURRVAL, r.ih_heat, r.ih_qty);
 28  	   INSERT INTO ot_ins_batch (ib_id, ib_ii_id, ib_batch, ib_qty)
 29  	   VALUES (ib_id.NEXTVAL, ii_id.CURRVAL, r.ib_batch, r.ih_qty);
 30  	   v_ih_qty := v_ih_qty + r.ih_qty;
 31  	 END IF;
 32    END LOOP;
 33    DELETE ot_ins_batch WHERE ib_ii_id = p_sys_id;
 34    DELETE ot_ins_heat WHERE ih_ii_id = p_sys_id;
 35    DELETE ot_ins_item WHERE ii_id = p_sys_id;
 36  END insp_heat_chg2;
 37  /

Procedure created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.


-- execution and results:
SCOTT@orcl_11gR2> BEGIN
  2    FOR r IN
  3  	 (SELECT ii_id FROM ot_ins_item)
  4    LOOP
  5  	 insp_heat_chg2 (r.ii_id);
  6    END LOOP;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11gR2> SELECT * FROM OT_INS_ITEM ORDER BY ii_id
  2  /

     II_ID II_ITEM_CD II_HEAT_CD       II_QTY
---------- ---------- ------------ ----------
         3 ITEM1      X                     1
         4 ITEM1      Y                     1
         5 ITEM1      Z                     2
         6 ITEM2      E                     1
         7 ITEM2      F                     1
         8 ITEM2      G                     2

6 rows selected.

SCOTT@orcl_11gR2> SELECT  * FROM OT_INS_HEAT ORDER BY ih_id
  2  /

     IH_ID   IH_II_ID IH_HEAT     IH_QTY
---------- ---------- ------- ----------
         7          3 X                1
         8          4 Y                1
         9          5 Z                2
        10          6 E                1
        11          7 F                1
        12          8 G                2

6 rows selected.

SCOTT@orcl_11gR2> SELECT * FROM OT_INS_BATCH ORDER BY ib_id
  2  /

     IB_ID   IB_II_ID IB_BATCH     IB_QTY
---------- ---------- -------- ----------
         4          3 XXX               1
         5          4 XXX               1
         6          5 XXX               2
         7          6 YYY               1
         8          7 YYY               1
         9          8 ZZZ               2

6 rows selected.

icon14.gif  Re: complex delete and insert (2 Merged) [message #567320 is a reply to message #567280] Wed, 26 September 2012 08:46 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks for the excellent help mam, this is what exactly i want , i need to understand this first, secondly can we treat this as trigger after i insert all the records in batch ot_ins_batch.
Re: complex delete and insert (2 Merged) [message #567330 is a reply to message #567320] Wed, 26 September 2012 11:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8007
Registered: November 2002
Location: California, USA
Senior Member
It would not be a good idea to try to run the procedure from a trigger on the ot_ins_batch table, because the procedure does an insert into the ot_ins_batch table, which would fire the trigger, which would run the procedure, which would fire the trigger, which would run the procedure, and so on in an infinite loop. There are ways of stopping the loop, but they are complex and inefficient, and best avoided. This is one of the problems with this design.
Re: complex delete and insert (2 Merged) [message #570704 is a reply to message #567330] Wed, 14 November 2012 11:44 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks mam for the help,there is one problem i am facing now, there is no restriction on how user will enter data in ot_ins_heat table and ot_ins_batch table, there may be 3 or more records or less records in both tables but qty should not exceed their reference qty in ot_ins_item table, as you see the example below , i have inserted one record extra in ot_ins_batch table for ii_id 2 , batch is 'AAA' with qty 1, hence there should be one record for 'AAA' with qty 1 in ot_ins_heat table but unfortunately its not happening.


SQL> CREATE TABLE OT_INS_ITEM
  2      (
  3        II_ID    NUMBER,
  4        II_ITEM_CD  VARCHAR2(12),
  5        II_HEAT_CD  VARCHAR2(12),
  6        II_QTY    NUMBER
  7      )
  8      /

Table created.

SQL> ALTER TABLE OT_INS_ITEM ADD (
  2        CONSTRAINT OT_INS_ITEM_UK
  3       UNIQUE (II_ID))
  4      /

Table altered.

SQL> CREATE SEQUENCE II_ID START WITH 1;

Sequence created.

SQL> CREATE TABLE OT_INS_HEAT
  2      (
  3        IH_ID  NUMBER,
  4        IH_II_ID  NUMBER,
  5        IH_HEAT  VARCHAR2(12),
  6        IH_QTY  NUMBER
  7      )
  8      /

Table created.

SQL> ALTER TABLE OT_INS_HEAT ADD (
  2        CONSTRAINT OT_INS_HEAT_FK_1
  3       FOREIGN KEY (IH_II_ID)
  4       REFERENCES OT_INS_ITEM (II_ID))
  5      /

Table altered.

SQL> CREATE SEQUENCE IH_ID START WITH 1;

Sequence created.

SQL> CREATE TABLE OT_INS_BATCH
  2      (
  3        IB_ID  NUMBER,
  4        IB_II_ID  NUMBER,
  5        IB_BATCH  VARCHAR2(12 BYTE),
  6        IB_QTY  NUMBER
  7      )
  8      /

Table created.

SQL> ALTER TABLE OT_INS_BATCH ADD (
  2        CONSTRAINT OT_INS_BATCH_PK
  3       PRIMARY KEY
  4       (IB_ID))
  5      /

Table altered.

SQL> ALTER TABLE OT_INS_BATCH ADD (
  2        CONSTRAINT OT_INS_BATCH_FK_1
  3       FOREIGN KEY (IB_II_ID)
  4       REFERENCES OT_INS_ITEM (II_ID))
  5      /

Table altered.

SQL> CREATE SEQUENCE IB_ID START WITH 1
  2  /

Sequence created.

SQL> BEGIN
  2        INSERT INTO OT_INS_ITEM VALUES (II_ID.NEXTVAL,'ITEM1','A',4);
  3        INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,II_ID.CURRVAL,'X',1);
  4        INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,II_ID.CURRVAL,'Y',1);
  5        INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,II_ID.CURRVAL,'Z',2);
  6        INSERT INTO OT_INS_BATCH VALUES (IB_ID.NEXTVAL,II_ID.CURRVAL,'XXX',4);
  7        INSERT INTO OT_INS_ITEM VALUES (II_ID.NEXTVAL,'ITEM2','B',4);
  8        INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,II_ID.CURRVAL,'E',1);
  9        INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,II_ID.CURRVAL,'F',1);
 10        INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,II_ID.CURRVAL,'G',2);
 11        INSERT INTO OT_INS_BATCH VALUES (IB_ID.NEXTVAL,II_ID.CURRVAL,'YYY',1);
 12        INSERT INTO OT_INS_BATCH VALUES (IB_ID.NEXTVAL,II_ID.CURRVAL,'ZZZ',2);
 13        INSERT INTO OT_INS_BATCH VALUES (IB_ID.NEXTVAL,II_ID.CURRVAL,'AAA',1);
 14        COMMIT;
 15    END;
 16     /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM OT_INS_ITEM;

     II_ID II_ITEM_CD   II_HEAT_CD       II_QTY                                 
---------- ------------ ------------ ----------                                 
         1 ITEM1        A                     4                                 
         2 ITEM2        B                     4                                 

SQL> SELECT * FROM OT_INS_HEAT;

     IH_ID   IH_II_ID IH_HEAT          IH_QTY                                   
---------- ---------- ------------ ----------                                   
         1          1 X                     1                                   
         2          1 Y                     1                                   
         3          1 Z                     2                                   
         4          2 E                     1                                   
         5          2 F                     1                                   
         6          2 G                     2                                   

6 rows selected.

SQL> SELECT * FROM OT_INS_BATCH;

     IB_ID   IB_II_ID IB_BATCH         IB_QTY                                   
---------- ---------- ------------ ----------                                   
         1          1 XXX                   4                                   
         2          2 YYY                   1                                   
         3          2 ZZZ                   2                                   
         4          2 AAA                   1                                   

SQL>  CREATE OR REPLACE PROCEDURE insp_heat_chg2 (p_sys_id NUMBER)
  2      IS
  3        v_ib_qty  NUMBER := 0;
  4        v_ih_qty  NUMBER := 0;
  5      BEGIN
  6        FOR r IN
  7        (SELECT i.ii_id, i.ii_item_cd,
  8         h.ih_id, h.ih_heat, h.ih_qty,
  9         b.ib_id, b.ib_batch, b.ib_qty,
 10        ROW_NUMBER () OVER (PARTITION BY i.ii_id, b.ib_id ORDER BY h.ih_id) rn
 11        FROM  ot_ins_item i, ot_ins_heat h, ot_ins_batch b
 12        WHERE  i.ii_id = h.ih_ii_id
 13        AND  i.ii_id = b.ib_ii_id
 14        AND  i.ii_id = p_sys_id
 15        ORDER  BY i.ii_id, b.ib_id, h.ih_id)
 16       LOOP
 17       IF r.rn = 1 THEN
 18         v_ib_qty := v_ib_qty + r.ib_qty;
 19         v_ih_qty := v_ih_qty * -1;
 20       END IF;
 21       IF v_ih_qty < 0 THEN
 22              v_ih_qty := v_ih_qty + r.ih_qty;
 23       ELSIF v_ib_qty - v_ih_qty > 0 THEN
 24         INSERT INTO ot_ins_item (ii_id, ii_item_cd, ii_heat_cd, ii_qty)
 25         VALUES (ii_id.NEXTVAL, r.ii_item_cd, r.ih_heat, r.ih_qty);
 26         INSERT INTO ot_ins_heat (ih_id, ih_ii_id, ih_heat, ih_qty)
 27         VALUES (ih_id.NEXTVAL, ii_id.CURRVAL, r.ih_heat, r.ih_qty);
 28         INSERT INTO ot_ins_batch (ib_id, ib_ii_id, ib_batch, ib_qty)
 29         VALUES (ib_id.NEXTVAL, ii_id.CURRVAL, r.ib_batch, r.ih_qty);
 30         v_ih_qty := v_ih_qty + r.ih_qty;
 31       END IF;
 32       END LOOP;
 33       DELETE ot_ins_batch WHERE ib_ii_id = p_sys_id;
 34       DELETE ot_ins_heat WHERE ih_ii_id = p_sys_id;
 35       DELETE ot_ins_item WHERE ii_id = p_sys_id;
 36     END insp_heat_chg2;
 37     /

Procedure created.

SQL> BEGIN
  2        FOR r IN
  3        (SELECT ii_id FROM ot_ins_item)
  4        LOOP
  5        insp_heat_chg2 (r.ii_id);
  6        END LOOP;
  7      END;
  8      /

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM OT_INS_ITEM ORDER BY ii_id;

     II_ID II_ITEM_CD   II_HEAT_CD       II_QTY                                 
---------- ------------ ------------ ----------                                 
         3 ITEM1        X                     1                                 
         4 ITEM1        Y                     1                                 
         5 ITEM1        Z                     2                                 
         6 ITEM2        E                     1                                 
         7 ITEM2        F                     1                                 
         8 ITEM2        G                     2                                 

6 rows selected.

SQL> SELECT  * FROM OT_INS_HEAT ORDER BY ih_id;

     IH_ID   IH_II_ID IH_HEAT          IH_QTY                                   
---------- ---------- ------------ ----------                                   
         7          3 X                     1                                   
         8          4 Y                     1                                   
         9          5 Z                     2                                   
        10          6 E                     1                                   
        11          7 F                     1                                   
        12          8 G                     2                                   

6 rows selected.

SQL> SELECT * FROM OT_INS_BATCH ORDER BY ib_id;

     IB_ID   IB_II_ID IB_BATCH         IB_QTY                                   
---------- ---------- ------------ ----------                                   
         5          3 XXX                   1                                   
         6          4 XXX                   1                                   
         7          5 XXX                   2                                   
         8          6 YYY                   1                                   
         9          7 ZZZ                   1                                   
        10          8 ZZZ                   2                                   

6 rows selected.


--Desired output must be


SQL> SELECT * FROM OT_INS_ITEM ORDER BY ii_id;

     II_ID II_ITEM_CD   II_HEAT_CD       II_QTY                                 
---------- ------------ ------------ ----------                                 
         3 ITEM1        X                     1                                 
         4 ITEM1        Y                     1                                 
         5 ITEM1        Z                     2                                 
         6 ITEM2        E                     1                                 
         7 ITEM2        F                     1 
         8 ITEM2        F                     1                                 
         9 ITEM2        G                     1                                 

6 rows selected.

SQL> SELECT  * FROM OT_INS_HEAT ORDER BY ih_id;

     IH_ID   IH_II_ID IH_HEAT          IH_QTY                                   
---------- ---------- ------------ ----------                                   
         7          3 X                     1                                   
         8          4 Y                     1                                   
         9          5 Z                     2                                   
        10          6 E                     1                                   
        11          7 F                     1                                   
        12          8 F                     1
        13          9 G                     1                                   






     IB_ID   IB_II_ID IB_BATCH         IB_QTY                                   
---------- ---------- ------------ ----------                                   
         5          3 XXX                   1                                   
         6          4 XXX                   1                                   
         7          5 XXX                   2                                   
         8          6 YYY                   1                                   
         9          7 ZZZ                   1                                   
        10          8 ZZZ                   1
        11          9 AAA                   1                                    




Previous Topic: Get the week of the Year (First week with starting with first SUNDAY of year)
Next Topic: Delete Partition
Goto Forum:
  


Current Time: Fri Nov 28 08:58:06 CST 2014

Total time taken to generate the page: 0.10632 seconds