Monday, 11 November 2013

Simple UDC sync when upgrading. Merge F0005 and F0004

For a multitude of reasons I have issues when doing upgrades and ensuring all of the UDC's from PS910 have been merged into my custom ones.  Generally I run a script to identify and then insert any UDC's from PS910 into my upgraded environment.  Please see the following SQL that will add any F0004 or F0005 records from PS if they are not in your target schemas:

In my example DVFINCTL has just been upgraded and is missing some records.

insert into DVFINCTL.F0004
select * from ps910ctl.f0004 t1
where not exists
(select 1 from DVFINCTL.f0004 t2
where t1.dtsy = t2.dtsy
and t1.dtrt = t2.dtrt);
commit ;

insert into DVFINCTL.F0005
select * from ps910ctl.f0005 t1
where not exists
(select 1 from DVFINCTL.f0005 t2
where t1.drsy = t2.drsy
and t1.drrt = t2.drrt
and t1.drky = t2.drky);
commit;

Another method I use for this is with IMP and EXP and oracle.  You could exp the contents of PS to a file and then import with ROWS_ONLY over the top of your other environment.  Oh oracle, you give me so many ways to skin my cat!


No comments:

Extending JDE to generative AI