PeopleSoft Wiki

SELECT Decode(a.objecttype,0,'Record',1,'Index',2,'Field',3,'Field Format',4,'Translate'
,5,'Page',6,'Menu',7,'Component',8,'Record PeopleCode',9,'Menu PeopleCode',10,'Query'
,11,'Tree Structure',12,'Tree',13,'Access Group',14,'Color',15,'Style',16,'Business Process Map'
,17,'Business Process',18,'Activity',19,'Role',20,'Process Definition',21,'Server Definition'
,22,'Process Type Definition',23,'Job Definition',24,'Recurrence Definition',25,'Message Catalog Entry'
,26,'Dimension',27,'Cube Definition',28,'Cube Instance Definition',29,'Business Interlink'
,30,'SQL Object',31,'File Layout Definition',32,'Component Interface',33,'Process Engine Program'
,34,'Process Engine Section',35,'Message Node',36,'Message Channel',37,'Message'
,38,'Approval Rule Set',39,'Message PeopleCode',40,'Subscription PeopleCode'
,41,'Channel PeopleCode',42,'Component Interface PeopleCode',43,'Application Engine PeopleCode'
,44,'Page PeopleCode',45,'Page Field PeopleCode',46,'Component PeopleCode'
,47,'Component Record PeopleCode',48,'Component Record Field PeopleCode',49,'Image'
,50,'Style Sheet',51,'HTML',52,'File Reference',53,'Permission List',54,'Portal Registry Definition'
,55,'Portal Registry Structure',56,'URL Definition',57,'Application Package'
,58,'Application Package PeopleCode',59,'n/a (59)',60,'Problem Type',61,'n/a (61)',62,'XSLT'
,63,'Portal Registry User Favorites',64,'Mobile Page',65,'Relationship') "Object Type",
OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
' ' "Owner", ' ' "Owner Full Name"
FROM PSPROJECTITEM A
WHERE A.PROJECTNAME = 'UHS_DB_COMPARE'
AND a.objecttype not in (0,5,7,8,44,2,6,30,4)
union
SELECT 'Record',
OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
b.lastupdoprid, c.oprdefndesc
FROM PSPROJECTITEM A, psrecdefn B, psoprdefn c
WHERE A.PROJECTNAME = 'INFRA_2C_BAUCR_24AUG09_HFIX1'
and a.objectvalue1 = b.recname (+)
AND a.objecttype = 0
and b.lastupdoprid = c.oprid (+)
union
SELECT 'Component',
OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
b.lastupdoprid, c.oprdefndesc
FROM PSPROJECTITEM A, pspnlgrpdefn B, psoprdefn c
WHERE A.PROJECTNAME = 'INFRA_2C_BAUCR_24AUG09_HFIX1'
and a.objectvalue1 = b.pnlgrpname (+)
AND a.objecttype = 7
and b.lastupdoprid = c.oprid (+)
union
SELECT 'Page',
OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
b.lastupdoprid, c.oprdefndesc
FROM PSPROJECTITEM A, pspnldefn B, psoprdefn c
WHERE A.PROJECTNAME = 'INFRA_2C_BAUCR_24AUG09_HFIX1'
and a.objectvalue1 = b.pnlname (+)
AND a.objecttype = 5
and b.lastupdoprid = c.oprid (+)
union
SELECT 'Record PCode',
A.OBJECTVALUE1, A.OBJECTVALUE2, A.OBJECTVALUE3, A.OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
b.lastupdoprid, c.oprdefndesc
FROM PSPROJECTITEM A, pspcmprog B, psoprdefn c
WHERE A.PROJECTNAME = 'INFRA_2C_BAUCR_24AUG09_HFIX1'
and a.objectvalue1 = b.objectvalue1 (+)
and a.objectvalue2 = b.objectvalue2 (+)
and a.objectvalue3 = b.objectvalue3 (+)
AND a.objecttype = 8
and b.lastupdoprid = c.oprid (+)
union
SELECT 'Page PCode',
A.OBJECTVALUE1, A.OBJECTVALUE2, A.OBJECTVALUE3, A.OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
b.lastupdoprid, c.oprdefndesc
FROM PSPROJECTITEM A, pspcmprog B, psoprdefn c
WHERE A.PROJECTNAME = 'INFRA_2C_BAUCR_24AUG09_HFIX1'
and a.objectvalue1 = b.objectvalue1 (+)
and a.objectvalue2 = b.objectvalue2 (+)
and a.objectvalue3 = b.objectvalue3 (+)
AND a.objecttype  in (39,40,41,42,43,44,45,46,47,48)
and b.lastupdoprid = c.oprid (+)
union
SELECT 'Fields',
A.OBJECTVALUE1, A.OBJECTVALUE2, A.OBJECTVALUE3, A.OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
b.lastupdoprid, c.oprdefndesc
FROM PSPROJECTITEM A, psdbfield B, psoprdefn c
WHERE A.PROJECTNAME = 'INFRA_2C_BAUCR_24AUG09_HFIX1'
and a.objectvalue1 = b.fieldname (+)
AND a.objecttype = 2
and a.objectid2 = 0
and b.lastupdoprid = c.oprid (+)
union
SELECT 'Menu',
A.OBJECTVALUE1, A.OBJECTVALUE2, A.OBJECTVALUE3, A.OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
b.lastupdoprid, c.oprdefndesc
FROM PSPROJECTITEM A, PSMENUDEFN B, psoprdefn c
WHERE A.PROJECTNAME = 'INFRA_2C_BAUCR_24AUG09_HFIX1'
and a.objectvalue1 = B.MENUNAME (+)
AND a.objecttype = 6
and b.lastupdoprid = c.oprid (+)
union
SELECT 'SQL Object',
A.OBJECTVALUE1, A.OBJECTVALUE2, A.OBJECTVALUE3, A.OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
b.lastupdoprid, c.oprdefndesc
FROM PSPROJECTITEM A, PSSQLDEFN B, psoprdefn c
WHERE A.PROJECTNAME = 'INFRA_2C_BAUCR_24AUG09_HFIX1'
and a.objectvalue1 = B.SQLID (+)
and a.objectvalue2 = B.SQLTYPE (+)
AND a.objecttype = 30
and b.lastupdoprid = c.oprid (+)
UNION
SELECT 'Translate',
A.OBJECTVALUE1, A.OBJECTVALUE2, A.OBJECTVALUE3, A.OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
b.lastupdoprid, c.oprdefndesc
FROM PSPROJECTITEM A, psxlatitem B, psoprdefn c
WHERE A.PROJECTNAME = 'YOUR_PROJECT_HERE'
and a.objectvalue1 = b.fieldname (+)
and a.objectvalue2 = b.fieldvalue (+)
AND a.objecttype = 4
and b.lastupdoprid = c.oprid (+)
;