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'