SQL Scripts to extract data after Project Compare

Here are some SQL scripts to extract data from PeopleSoft Meta tables after running Project Compare. This is to provide summary level of changes that has been carried out and doesnt replace the actual compare reports. The scripts assume that the Source database is the development/production database and Target database is the Demo database.

-- RECORDS ABSENT in Target DB i.e. New records added
select OBJECTVALUE1 FROM PSPROJECTITEM A
WHERE PROJECTNAME = 'ORG_DMO_COMPARE'
AND OBJECTTYPE = 0 -- record
AND OBJECTID1 = 1
AND OBJECTID2 = 0
AND TARGETSTATUS = 1 -- absent



-- RECORDS Customised in Source
select OBJECTVALUE1 FROM PSPROJECTITEM A
WHERE PROJECTNAME = 'ORG_DMO_COMPARE'
AND OBJECTTYPE = 0 -- record
AND OBJECTID1 = 1
AND OBJECTID2 = 0
AND TARGETSTATUS in (2,3) -- changed, unchanged
AND SOURCESTATUS IN (4) -- * Changed
AND EXISTS (SELECT 'X' FROM PSPROJECTITEM
WHERE A.PROJECTNAME = PROJECTNAME
AND A.OBJECTVALUE1 = OBJECTVALUE1
AND OBJECTTYPE = 0 -- record
AND OBJECTID2 = 2 -- field within the record
AND TARGETSTATUS IN (1,2,3) -- absent, changed, unchanged
AND SOURCESTATUS IN (4) -- * Changed
)


select * FROM PSPROJECTITEM A
WHERE PROJECTNAME = 'ORG_DMO_COMPARE'
AND OBJECTID2 = 2
AND OBJECTVALUE1 = 'COMPENSATION'


-- Custom Added Pages
select OBJECTVALUE1 FROM PSPROJECTITEM A
WHERE PROJECTNAME = 'ORG_DMO_COMPARE'
AND OBJECTTYPE = 5
AND OBJECTID1 = 9
AND SOURCESTATUS in ( 4, 5) -- * Changed
AND TARGETSTATUS = 1 -- absent
AND A.OBJECTVALUE1 NOT LIKE '%OLD'
AND NOT EXISTS (SELECT 'X' FROM PSPNLFIELD B
WHERE A.OBJECTVALUE1 = B.PNLNAME
AND B.RECNAME NOT LIKE 'ORG%')



-- Customised PS delivered pages
select OBJECTVALUE1 FROM PSPROJECTITEM A
WHERE PROJECTNAME = 'ORG_DMO_COMPARE'
AND OBJECTTYPE = 5
AND OBJECTID1 = 9
AND SOURCESTATUS in ( 4, 5) -- * Changed
AND TARGETSTATUS in (2,3) -- changed, unchanged



-- Custom Added Comp PeopleCode
select DISTINCT
OBJECTVALUE1, OBJECTVALUE2--, OBJECTVALUE3, OBJECTVALUE4
FROM PSPROJECTITEM A
WHERE PROJECTNAME = 'ORG_DMO_COMPARE'
AND OBJECTTYPE IN (46, 48)
AND OBJECTID1 = 10
AND SOURCESTATUS in ( 4, 5) -- * Changed
AND TARGETSTATUS = 1 -- absent

-- Custom Added Page PeopleCode
select --DISTINCT
OBJECTVALUE1, OBJECTVALUE2 FROM PSPROJECTITEM A
WHERE PROJECTNAME = 'ORG_DMO_COMPARE'
AND OBJECTTYPE = 44
AND OBJECTID1 = 9
AND SOURCESTATUS in ( 4, 5) -- * Changed
AND TARGETSTATUS = 1 -- absent



-- Custom Added Record PeopleCode
select DISTINCT
OBJECTVALUE1--, OBJECTVALUE2
FROM PSPROJECTITEM A
WHERE PROJECTNAME = 'ORG_DMO_COMPARE'
AND OBJECTTYPE = 8
AND OBJECTID1 = 1
AND SOURCESTATUS in ( 4, 5) -- * Changed
AND TARGETSTATUS = 1 -- absent
AND OBJECTVALUE1 LIKE 'ORG%'



-- Customised Comp PeopleCode
select --DISTINCT
OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4
, CASE WHEN TARGETSTATUS = 1 THEN 'NEW' ELSE 'CHANGED' END STATUS
FROM PSPROJECTITEM A
WHERE PROJECTNAME = 'ORG_DMO_COMPARE'
AND OBJECTTYPE IN (46,48)
AND OBJECTID1 = 10
AND SOURCESTATUS in ( 4, 5) -- * Changed
AND TARGETSTATUS In (1,2,3) -- changed, unchanged
and OBJECTVALUE1 NOT LIKE 'ORG%'


-- Customised Page PeopleCode
select --DISTINCT
OBJECTVALUE1, OBJECTVALUE2
, CASE WHEN TARGETSTATUS = 1 THEN 'NEW' ELSE 'CHANGED' END STATUS
FROM PSPROJECTITEM A
WHERE PROJECTNAME = 'ORG_DMO_COMPARE'
AND OBJECTTYPE = 44
AND OBJECTID1 = 9
AND SOURCESTATUS in ( 4, 5) -- * Changed
AND TARGETSTATUS in (1,2,3) -- changed, unchanged
and OBJECTVALUE1 NOT LIKE 'ORG%'


-- Custom Added Record PeopleCode
select DISTINCT
OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3
, CASE WHEN TARGETSTATUS = 1 THEN 'NEW' ELSE 'CHANGED' END STATUS
FROM PSPROJECTITEM A
WHERE PROJECTNAME = 'ORG_DMO_COMPARE'
AND OBJECTTYPE = 8
AND OBJECTID1 = 1
AND SOURCESTATUS in ( 4, 5) -- * Changed
AND TARGETSTATUS IN ( 2,3) -- changed, unchanged
and OBJECTVALUE1 NOT LIKE 'ORG%'


SELECT * FROM PSPROJECTITEM
WHERE PROJECTNAME = 'ORG_DMO_COMPARE'
-- AND OBJECTTYPE = 5
and OBJECTVALUE1 LIKE 'ABSV_REQUEST'

Upgrade - Upgrading 2 versions in one go

There are occassions when PeopleSoft doesnt provide a direct upgrade path from one version to another i.e. 7.5 to 8.8 or 8.3 to 9.0. PeopleSoft suggests that such installations require two upgrades in one go, i.e. from 7.5 to 8.3 and 8.3 to 8.8 or 8.3 to 8.9 and 8.9 to 9.0 in my examples. However, what PeopleSoft doesnt warn you about any special care when performing such upgrades. All PeopleSoft provides is the documentation to upgrade from one version to another.

However, from experience, we have found this to be a process that requires special attention. As you are aware that upgrades require 2 passes i.e. first 'initial-upgrade', using a copy of live database, and then 'move to live'. When you perform the 2nd pass i.e. 'move to live' upgrade, it requires the copy of database on which you performed the 'initial' pass. Now consider a situation where you performed 'initial' upgrade on a 7.5 database to move to 8.3 and then you performed 'initial' pass to upgrade the same database to 8.8. When you come to performing you 'move to live' pass from 7.5 to 8.3, you dont have a copy of database that is on version 8.3.

Therefore, it is quite important that you keep a copy of database, in working order, before moving to the next version.

Also, sometimes it may be easier to re-implement rather than upgrading 2 versions. However, it all depends on the usage of PeopleSoft and the complexity involved.

Upgrade Paths to HCM/HRMS 9.0

Customer Connection link for Upgrade paths to HCM/HRMS 9.0