From my LinkedIn Projects List:
PeopleSoft/SQR Dynamic Conversion Code Generation - Unilever/Best Foods - May 2001
Developed Dynamic SQR Conversion Code Generation Tool for Unilever/Best
Foods Acquisition & Merger to single PeopleSoft HR/Benefits/Payroll
System. Using PeopleTools Meta-Data the Code Generator accounts for
structural differences between Best Foods Source/Unilever Target
(Version/Customization Delta) & produces full ready-to-run
conversion programs for each record (with sections for special data
transformations if needed). *Original Project Requirement was to merge
Best Foods 7.5 data to Unilever's Upgraded 8.0 database. When the
upgrade was placed on hold the focus was then a 7.5 to 7.5 merger - the
Code Generation Tool reproduced the full set of Conversions in less than
an hour followed by immediate testing. A major project alteration was
hardly a blip on the timeline due to this unique approach. The LinkedIn project summary gives a very high-level overview of the situation. In short, Best Foods was acquired by Unilever & their data needed to be merged into Unilever's database. Unilever was scheduled for a 7.5 to 8.0 upgrade prior to the merger. Therefore, PeopleSoft versions would be different - Best Food's 7.5 to Unilever's 8.0. New fields & altered field names/attributes must be detected & accounted for as well as customized field changes/additions. The universal approach would be to run a series of compare reports, note the differences & code each conversion program by hand - very dull & time consuming. I felt an automated approach would make better use of meta-data, my time and my brain... this proved to be a wise decision. At the eleventh hour the target upgrade to 8.0 was delayed but the merger was still to be performed. Running the Upgrade Analyzer/SQR Code Generation against the 7.5 target (instead of 8.0) yielded the full suite of required conversion programs & reports. It's important to note I used a very rapid & precise developmental approach to constructing this automation (as opposed to manual individual conversion program development) - the down side was that I was way ahead of schedule on the project plan - the complex, innovative, automated approach led to some personal boredom once the exciting work was complete.
To bring my strategy to life I needed to develop several complex components:
TDUPGREC.SQC - Upgrade Analyzer - A package of routines that will compare records from two different databases (BF/UNI), detect all differences (names/attributes/additions/deletions/etc), produce a report & make all the deltas available for Dynamic Code Generation. This includes accounting for multiple levels of nested subrecord definitions in both the source & target databases. (I previously designed a utility to accomplish this TDSUBDYN.SQC).
TD80GEN.SQC - SQR Code Generator - Using the Upgrade Analyzer Record/Field level deltas produced by TDUPGREC.SQC the Source to Target Conversion program(s) can be dynamically generated.
*The databases are accessed via DBLink - either from the Source or Target depending upon the method of execution (Push/Pull).
Once the core routines were established I created some simple driver modules to call them.
TD80UPGA.SQR - Produce Upgrade Analyzer Report by RECNAME Prompt.
TD80UPGB.SQR - Produce Upgrade Analyzer Report by Record List File.
TD80UPGC.SQR - Produce Upgrade Analyzer Report by PSRECDEFN Select (Full).
TD80UPXA.SQR - Dynamic Code Generation by RECNAME Prompt.
TD80UPXB.SQR - Dynamic Code Generation by Record List File
TD80UPXC.SQR - Dynamic Code Generation by PSRECDEFN Select (Full)
Hierarchy of Functionality:
Report Only ->
TDUPGREC.SQC ->
TDSUBDYN.SQC
Code Generation ->
TD80GEN.SQC ->
TDUPGREC.SQC ->
TDSUBDYN.SQC
For example, run TD80UPGA.SQR to produce the Upgrade Analyzer Report by prompt (No Code Generation necessary - just a hard copy for Meetings/Project Reports/Etc.):
let $RC_log = 'Y' ! SubRecord Analyzer Log - ON
let $RC_rpt = 'BREAK' ! Page Break after each Recname
do UPG-Pull-Prompt($RC_pull) ! Prompt for PUSH or PULL Method
do UPG-Alias-Prompt ! Prompt for Alias Conversion
input $RC_link 'Enter Database Link (i.e. @HTRY)'
input $RC_recname 'Enter RECNAME (i.e. JOB)'
while $RC_recname <> ''
uppercase $RC_recname
do UPG-Main($RC_link, $RC_pull, $RC_recname, $RC_log, $RC_rpt, #O_ctr, #U_ctr)
input $RC_recname 'Enter RECNAME (i.e. JOB)'
end-while
...
...
...
#Include 'tdupgrec.sqc' !TD Upgrade Record Analyzer
#Include 'tdsubdyn.sqc' !TD SubRecord Analyzer
#include 'tdfunc.sqc' !TD Custom SQR Function Library
The routine UPG-Main performs all the analysis & writes the report for the selected RECNAME. Note that the TD80GEN.SQC Code Generator also calls this routine.
Below is a portion of the Upgrade Analyzer Report for the record PAY_LINE. The field-by-field Source to Target results are displayed with the delta status (Added, Dropped or Changed):
Above: Notice the fields PAGE_NUM, LINE_NUM & EMPL_RCD. These fields (along with others) were renamed in the 7.5 to 8.0 upgrade. Originally PAGE#, LINE# & EMPL_RCD#. The Upgrade Analyzer & Code Generator takes this into account.
The reports in the above example were produced via TD80UPGB.SQR - A list of Record Names in a file was processed with a full report produced.
Full Upgrade Analyzer Report for this example: CLICK HERE
Here's the main function from TDUPGREC.SQR - this is the backbone for both reporting & code generation. All delta/structural information for the Source/Target records are deciphered, stored & available through the UPG-Main() function below.
!**********************************************************************
!* UPG Main *
!**********************************************************************
!* *
!* INPUT: $I_link - Database Link (or null) *
!* $I_pull - DB Link Method: Y = PULL / N = PUSH. *
!* $I_rec - Primary Recname *
!* $I_log - Subrecord Analyzer Log Option (Y/N) *
!* $I_rpt - Report Option (NONE/BREAK/SKIP) *
!* OUTPUT: #O_ctr - Record Matrix Count (V80 Entries) *
!* #U_ctr - Upgrade Matrix Count (V75/V80) *
!* *
!**********************************************************************
begin-procedure UPG-Main($I_link, $I_pull, $I_rec, $I_log, $I_rpt,
:#O_ctr, :#U_ctr)
! Establish PUSH/PULL DB Link Method
let $SRC_link = ''
let $TGT_link = ''
if $I_pull = 'Y'
let $SRC_link = $I_link
else
let $TGT_link = $I_link
end-if
! Report Print Options - BREAK/SKIP/NONE
let $I_rpt = upper(rtrim($I_rpt,' '))
if $I_rpt <> 'BREAK'
and $I_rpt <> 'SKIP'
let $I_rpt = 'NONE'
end-if
clear-array name=UPGmtx
clear-array name=UPGptr
let #UPG = 0
! Process Record - SOURCE Database (7.5)
do REC-Func($SRC_link, $I_rec, $I_log, #O_ctr)
let #idx = 0
while #idx < #O_ctr
do REC-Func-Get(#idx, $rec, $fld, $sub, #typ, #len, #dec, #use, $def, #lev)
if $sub <> 'Y'
do UPG-Func-Add(#UPG, 0, $rec, $fld, $def, #typ, #len, #dec, #use, #lev)
let #UPG = #UPG + 1
end-if
let #idx = #idx + 1
end-while
! Total Number Source Entries
let #SRC = #UPG
! Process Record - TARGET Database (8.0)
do REC-Func($TGT_link, $I_rec, $I_log, #O_ctr)
let #idx = 0
while #idx < #O_ctr
do REC-Func-Get(#idx, $rec, $fld, $sub, #typ, #len, #dec, #use, $def, #lev)
if $sub <> 'Y'
do UPG-Func-Ptr($fld, #SRC, $hit, #ptr)
if $hit <> 'Y'
let #ptr = #UPG
let #UPG = #UPG + 1
end-if
do UPG-Func-Add(#ptr, 1, $rec, $fld, $def, #typ, #len, #dec, #use, #lev)
! Save Upgrade Matrix Pointer
let UPGptr.UPGptr (#idx) = #ptr
end-if
let #idx = #idx + 1
end-while
! Process Output
if $I_rpt <> 'NONE'
do UPG-Func-Print($I_rec, $I_rpt, #UPG)
end-if
! Return Upgrade Matrix Count
let #U_ctr = #UPG
end-procedure
!**********************************************************************
Here's a smidgeon of code from my SubRecord Analyzer Component - TDSUBDYN.SQC. It shows how the subrecord array is populated with all nested subordinate entries. It is called within the Rec-Func() which the Upgrade Analyzer uses for both the Source & Target databases.
!**********************************************************************
!* Populate Array (w/subordinate entries) *
!**********************************************************************
!* *
!* INPUT: $I_link - Database Link (or null) *
!* $I_rec - Record / SubRecord Name *
!* $I_log - Display on SQR.LOG - Y/N *
!* OUTPUT: #U_ctr - Running Total of Nested Fields *
!* *
!**********************************************************************
begin-procedure REC-Populate($I_link, $I_rec, :#U_ctr)
let $DYN_psrecfield = 'PSRECFIELD' || $I_link || ' rec'
begin-select
rec.fieldnum
rec.fieldname
rec.useedit
rec.defrecname
rec.deffieldname
let #U_ctr = #U_ctr + 1
let RECmtx.RECid (#U_ctr) = $I_rec
let RECmtx.RECfld (#U_ctr) = &rec.fieldname
let RECmtx.RECuse (#U_ctr) = &rec.useedit
do REC-SubRecord($I_link, &rec.fieldname, $RECsub, #RECtyp,
#REClen, #RECdec)
let RECmtx.RECsub (#U_ctr) = $RECsub
let RECmtx.RECtyp (#U_ctr) = #RECtyp
let RECmtx.REClen (#U_ctr) = #REClen
let RECmtx.RECdec (#U_ctr) = #RECdec
! Default may be RECNAME.FIELDNAME, Literal or Meta-String
let $RECdef = rtrim(&rec.defrecname,' ')
let $RECfld = rtrim(&rec.deffieldname,' ')
if $RECdef <> ''
let $RECdef = '<' || $RECdef || '.' || $RECfld || '>'
else
if substr($RECfld,1,1) = '%'
lowercase $RECfld
end-if
let $RECdef = $RECfld
end-if
let RECmtx.RECdef (#U_ctr) = $RECdef
from [psrecfield rec:$DYN_psrecfield]
where rec.recname = $I_rec
order by rec.fieldnum
end-select
end-procedure
!**********************************************************************
You can see the Upgrade Analyzer results on the report samples which provides the blueprint for the Code Generation. With that being said Part 2 will display the functionality of the Code Generation portion - coming up next!
CODE Generation - Upgrade/Conversion Project - Part 2
