Note: Technical Talk being moved to:
www.nazimcricket.com/wiki


Wednesday, 6 August 2008

PeopleSoft Customisations/Migrations

Here is one way of keeping tabs of customised projects and migrations across various PeopleSoft environments.

I have used database links to simultaneously query multiple databases. In this case:

DEV (Development) TST (Testing) UAT (User Acceptance Testing) PRD (Production) RPT (Reporting)

The DEV environment is the benchmark as I have assumed this is where the project begins its life.

The SQL queries the DEV environment first and compares to see if the project exists in the other environments. Where it does, it displays the LASTUPDDTTM and the LASTUPDOPRID, which I'm interpreting to be the migrator of the project. Where the project does not exist, I'm displaying the text "Not Migrated".

In order to pick up customised projects only, I'm filtering out projects last updated by the PPLSOFT user, which signifies it as being Vanilla.

One final detail is that I'm using Outer Joins to ensure I pick up all projects from DEV.

SELECT A.PROJECTNAME
     , A.PROJECTDESCR
     , TO_CHAR(A.LASTUPDDTTM,'YYYY-MM-DD') || ' - ' || A.LASTUPDOPRID PSDEV
     , DECODE(B.PROJECTNAME,A.PROJECTNAME, TO_CHAR(B.LASTUPDDTTM,'YYYY-MM-DD') || ' - ' || B.LASTUPDOPRID,'Not Migrated') PSTST
     , DECODE(C.PROJECTNAME,A.PROJECTNAME, TO_CHAR(C.LASTUPDDTTM,'YYYY-MM-DD') || ' - ' || C.LASTUPDOPRID,'Not Migrated') PSUAT
     , DECODE(D.PROJECTNAME,A.PROJECTNAME, TO_CHAR(D.LASTUPDDTTM,'YYYY-MM-DD') || ' - ' || D.LASTUPDOPRID,'Not Migrated') PSPRD
     , DECODE(E.PROJECTNAME,A.PROJECTNAME, TO_CHAR(E.LASTUPDDTTM,'YYYY-MM-DD') || ' - ' || E.LASTUPDOPRID,'Not Migrated') PSRPT
     , A.DESCRLONG
     , TRUNC(A.LASTUPDDTTM) DEVUPDTM
     , TRUNC(B.LASTUPDDTTM) TSTUPDTM
     , TRUNC(C.LASTUPDDTTM) UATUPDTM
     , TRUNC(D.LASTUPDDTTM) PRDUPDTM
     , TRUNC(E.LASTUPDDTTM) RPTUPDTM
  FROM PSPROJECTDEFN@PSDEV A
     , PSPROJECTDEFN@PSTST B
     , PSPROJECTDEFN@PSUAT C
     , PSPROJECTDEFN@PSPRD D
     , PSPROJECTDEFN@PSRPT E
 WHERE A.PROJECTNAME = B.PROJECTNAME (+)
   AND A.PROJECTNAME = C.PROJECTNAME (+)
   AND A.PROJECTNAME = D.PROJECTNAME (+)
   AND A.PROJECTNAME = E.PROJECTNAME (+)
   AND A.LASTUPDOPRID <> 'PPLSOFT';
Note: I have also included the LASTUPDDTTM separately in the select statement as they can be handy to perform some date calculations, example the number of jobs migrated to a certain environment between a date range. The same of course can be achieved by grabbing the LASTUPDDTTM from the decode statement, but it saves having to perform substrings and date conversions on that string.

TIP: If you've run the query a few times and hit a limit on the number of database links open, you could always get around this by performing a Commit. There are Oracle documents available supporting the notion of using commits against Select statements across database links.

No comments: