Procedure Guidance Required

[hellozishan]

Hi all, following is my procedure, As u can see values are going from "pos_test_ext" to "pos_test" table. But one field in "pos_test" table is null named "cond". I want to populated this "cond" field in such a way that if a customer insert asset_type in pos_test_ext table with values(20,30) then cond column is populated with 1 and if customer insert asset_type in pos_test_ext with values(40,50) then "cond" field populated with value 2 and else it will populated with value 3. How can i amend this procedure pls help me in this regard I am very gratefult to you.

Code: Select all

create or replace procedure myprocpos as
    begin
      execute immediate 'truncate table pos_ins_upd';
      execute immediate 'truncate table pos_del_upd';
      execute immediate 'truncate table pos_upd';
      execute immediate 'truncate table pos_ins';
      execute immediate 'truncate table pos_del';
insert into pos_ins_upd
     select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_test_ext
     minus
     select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_test;
insert into pos_del_upd
     select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_test
     minus
     select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_test_ext;
insert into pos_upd
     select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_ins_upd
     intersect
     select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_del_upd;
insert into pos_ins
     select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_ins_upd
     minus
     select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_upd;
insert into pos_del
     select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_del_upd
     minus
     select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_upd;

insert into pos_test
     select pos_date,pos_asset_type,pos_ref_no,pos_application,null from pos_test_ext
     where pos_asset_type in (select pos_asset_type from pos_ins);
     
delete from pos_test
     where pos_asset_type in (select pos_asset_type from pos_del);
update pos_test
     set cond = (select cond
                 from pos_test_ext
                 where pos_asset_type = pos_test.pos_asset_type)
     where pos_asset_type in (select pos_asset_type from pos_upd);
Commit;  
end;