CREATE TABLE CUT_SUM ( CD_PM_CODE VARCHAR2(12) ,CD_PS_CODE VARCHAR2(12),CD_QTY NUMBER );
        
        INSERT INTO CUT_SUM (CD_PM_CODE ,CD_PS_CODE ,CD_QTY) VALUES ('A','A1001', 4) 
        
        INSERT INTO CUT_SUM (CD_PM_CODE ,CD_PS_CODE ,CD_QTY) VALUES ('A','A1002', 2) 
        
        INSERT INTO CUT_SUM (CD_PM_CODE ,CD_PS_CODE ,CD_QTY) VALUES ('B','B1001', 2)

--CD_QTY Is the sum of qty


--First table have qty more than cd_qty and hence one row needs to be updated as 0

CREATE TABLE CUT_SEC (SEC_PM_CODE VARCHAR2(12), SEC_PS_CODE VARCHAR2(12),SEC_QTY NUMBER);
        
        INSERT INTO CUT_SEC ( SEC_PM_CODE,SEC_PS_CODE,SEC_QTY) VALUES ( 'A','A1001',1);
        
        INSERT INTO CUT_SEC ( SEC_PM_CODE,SEC_PS_CODE,SEC_QTY) VALUES ( 'A','A1001',1);
        
        INSERT INTO CUT_SEC ( SEC_PM_CODE,SEC_PS_CODE,SEC_QTY) VALUES ( 'A','A1001',1);
        
        INSERT INTO CUT_SEC ( SEC_PM_CODE,SEC_PS_CODE,SEC_QTY) VALUES ( 'A','A1001',1);
        
        INSERT INTO CUT_SEC ( SEC_PM_CODE,SEC_PS_CODE,SEC_QTY) VALUES ( 'A','A1001',1);

--second table no need to update anything since the qty is matching.

      CREATE TABLE CUT_SEC_REF (SECR_PM_CODE VARCHAR2(12), SECR_PS_CODE VARCHAR2(12),SECR_QTY NUMBER);
        
        INSERT INTO CUT_SEC_REF ( SECR_PM_CODE,SECR_PS_CODE,SECR_QTY) VALUES ( 'A','A1002',1);
        
        INSERT INTO CUT_SEC_REF ( SECR_PM_CODE,SECR_PS_CODE,SECR_QTY) VALUES ( 'A','A1002',1);

--third table one row is missing hence one row has to be inserted with value as 1 qty .

        CREATE TABLE CUT_PL (PL_PM_CODE VARCHAR2(12), PL_PS_CODE VARCHAR2(12),PL_QTY NUMBER);      
        
        INSERT INTO CUT_PL (PL_PM_CODE ,PL_PS_CODE,PL_QTY) VALUES ('B','B1001',1)


--THE Problem is i can find out where the difference is using the view and goind and updating it manullay as below.

create or replace view cut_log as        
        SELECT sec_pm_code p ,sec_ps_code s,sum(sec_qty) qty FROM CUT_SEC
        group by sec_pm_code,sec_ps_code
        union
        select secr_pm_code p , secr_ps_code s , sum(secr_qty) qty 
        from cut_sec_ref
        group by secr_pm_code,secr_ps_code
        union
        select pl_pm_code p , pl_ps_code s , sum(pl_qty) qty
        from cut_pl
        group by pl_pm_code,pl_ps_code
        order by 1

select * from cut_sum,cut_log
        where p = cd_pm_code
        and s = cd_ps_code
        and cd_qty <> qty

SQL> create table items(item_id varchar2(5) primary key, quantity number not null);

Table created.

SQL> insert into items(item_id, quantity) values('a', 3);

1 row created.

SQL> insert into items(item_id, quantity) values('b', 5);

1 row created.

SQL> insert into items(item_id, quantity) values('c', 2);

1 row created.

SQL> insert into items(item_id, quantity) values('d', 1);

1 row created.

SQL> create type numtab_ty as table of number
  2  /

Type created.

SQL> show errors;
No errors.
SQL> 
SQL> 
SQL> select
  2      t1.item_id
  3  from
  4      items t1
  5          cross join
  6      table
  7      (
  8          cast
  9          (
 10              multiset
 11              (
 12                  select
 13                      level
 14                  from
 15                      "PUBLIC".dual
 16                  connect by
 17                      level <= t1.quantity
 18              ) as numtab_ty
 19          )
 20      ) t2
 21  order by
 22    t1.item_id
SQL>



ITEM_
-----
a
a
a
b
b
b
b
b
c
c
d

11 rows selected.

SQL>

       CREATE TABLE CUT_SUM ( CD_PM_CODE VARCHAR2(12) ,CD_PS_CODE VARCHAR2(12),CD_QTY NUMBER );
        
        INSERT INTO CUT_SUM (CD_PM_CODE ,CD_PS_CODE ,CD_QTY) VALUES ('A','A1001', 4) 

--now i have work table or temporary table where the relevant records exists with higher quantity, now i want to reduce it to the above one.

CREATE TABLE CUT_SEC (SEC_PM_CODE VARCHAR2(12), SEC_PS_CODE VARCHAR2(12),SEC_QTY NUMBER);
        
        INSERT INTO CUT_SEC ( SEC_PM_CODE,SEC_PS_CODE,SEC_QTY) VALUES ( 'A','A1001',1);
        
        INSERT INTO CUT_SEC ( SEC_PM_CODE,SEC_PS_CODE,SEC_QTY) VALUES ( 'A','A1001',1);
        
        INSERT INTO CUT_SEC ( SEC_PM_CODE,SEC_PS_CODE,SEC_QTY) VALUES ( 'A','A1001',1);
        
        INSERT INTO CUT_SEC ( SEC_PM_CODE,SEC_PS_CODE,SEC_QTY) VALUES ( 'A','A1001',1);
        
        INSERT INTO CUT_SEC ( SEC_PM_CODE,SEC_PS_CODE,SEC_QTY) VALUES ( 'A','A1001',1);

--the data i want in cut_sec is

SEC_PM_CODE	SEC_PS_CODE	SEC_QTY
A	           A1001	1
A	           A1001	1
A	           A1001	1
A	           A1001	1
A	           A1001	0

SEC_PM_CODE	SEC_PS_CODE	SEC_QTY
A	           A1001	1
A	           A1001	1
A	           A1001	1
A	           A1001	1
A	           A1001	1

SEC_PM_CODE	SEC_PS_CODE	SEC_QTY
A	           A1001	1
A	           A1001	1
A	           A1001	1
A	           A1001	1
A	           A1001	0