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;