advanced UDC synchronisation

This is a slightly advanced way of synchronising your UDC’s.

This type of approach can be used when you are doing an upgrade, but do NOT want to do control table merges again.  You have your production 910 environment looking pretty tidy, but you want to move over anything new that might have been added to UDC’s in the last week or so (since your last dry run).  If you are in this situation, then this is for you.

Note that I have a dblink back to 812 called jde_p2jde.  Note also that this is oracle syntax.

Essentially the second script (F0005) is cooler than the first, so lets discuss that in more detail.

What we do first is get a set of the differences between 812 and 910, based upon the PK and description fields (because I want to know if any descriptions have changed)  I then select only the PK’s from this list.  I use this list of PK’s to feed a delete into 910.  What this achieves is deleting any duplicates that are going to affect the next insert.  Also this number is probably going to be less than the insert which is next.  You are only deleting conflicts.

Right, now you can do the insert, which will insert any changes (that you’ve deleted) and also any new items in 812 that are not in 910.  Cool!

Note that you might think I’m crazy doing the create table the way I did, but I’ve done a unicode conversion in the middle, and you cannot do string comparisons between charactercodes, so I create the 812 version of the table in unicode to enable my reconciliation.

 

desc prodctl.f0004;
drop table prodctl.f0004_812 ;
create table prodctl.f0004_812 as select * from prodctl.f0004 where 0 = 1;
insert into prodctl.f0004_812 select * from prodctl.f0004@jde_p2jde ;

select * from prodctl.F0004_812 t3
--select the difference
where (dtsy, dtrt, dtdl01) in (
select dtsy, dtrt, dtdl01 from prodctl.F0004_812 
minus 
select dtsy, dtrt, dtdl01 from prodctl.f0004);

--delete the dupes
delete from prodctl.f0004
where (dtsy, dtrt) in (
select dtsy, dtrt from (
select t2.dtsy, t2.dtrt  from prodctl.F0004_812 t2
minus 
select t3.dtsy, t3.dtrt  from prodctl.f0004 t3));

--insert delta
insert into prodctl.f0004
select * from prodctl.F0004_812 t3
--select the difference
where (dtsy, dtrt, dtdl01) in (
select dtsy, dtrt, dtdl01 from prodctl.F0004_812 
minus 
select dtsy, dtrt, dtdl01 from prodctl.f0004);

--F0005
desc prodctl.f0005;
drop table prodctl.f0005_812 ;
create table prodctl.f0005_812 as select * from prodctl.f0005 where 0 = 1;
insert into prodctl.f0005_812 select * from prodctl.f0005@jde_p2jde ;
commit;

select * from prodctl.F0005_812 t3
where (drsy, drrt, drky, drdl01, drdl02) in (
select drsy, drrt, drky, drdl01, drdl02 from prodctl.F0005_812 
minus 
select drsy, drrt, drky, drdl01, drdl02 from prodctl.f0005);

delete from prodctl.f0005
where (drsy, drrt, drky) in (
select drsy, drrt, drky from (
select t2.drsy, t2.drrt, t2.drky, t2.drdl01, t2.drdl02 from prodctl.F0005_812 t2
minus 
select t3.drsy, t3.drrt, t3.drky, t3.drdl01, t3.drdl02 from prodctl.f0005 t3));

insert into prodctl.f0005
select * from prodctl.f0005_812 
where (drsy, drrt, drky, drdl01, drdl02) in (
select drsy, drrt, drky, drdl01, drdl02 from prodctl.F0005_812 
minus 
select drsy, drrt, drky, drdl01, drdl02 from prodctl.f0005);

commit;

select * from prodctl.f0002 ;