PeopleSoft Wiki

SELECT PROJECTNAME,'Project',PROJECTNAME,LASTUPDOPRID,LASTUPDDTTM FROM PSPROJECTDEFN WHERE PROJECTNAME IN (<project names>)
UNION
SELECT ITEM.PROJECTNAME,'Application Engine',ITEM.OBJECTVALUE1 AS "Application Engine", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSAEAPPLDEFN DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 33
AND ITEM.OBJECTVALUE1 = DEFN.AE_APPLID
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Application Engine Section',RTRIM(ITEM.OBJECTVALUE1) || '.' || RTRIM(ITEM.OBJECTVALUE2) AS "Application Engine Section", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSAESECTDEFN DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 34
AND ITEM.OBJECTVALUE1 = DEFN.AE_APPLID AND ITEM.OBJECTVALUE2 = DEFN.AE_SECTION
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Application Package',RTRIM(ITEM.OBJECTVALUE2) || RTRIM(ITEM.OBJECTVALUE3) || RTRIM(ITEM.OBJECTVALUE1) AS "Application Package", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSPACKAGEDEFN DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 57
AND ITEM.OBJECTVALUE1 = DEFN.PACKAGEID AND ITEM.OBJECTVALUE2 = DEFN.PACKAGEROOT
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Component Interfaces',RTRIM(ITEM.OBJECTVALUE1) AS "Component Interfaces", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSBCDEFN DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 32
AND ITEM.OBJECTVALUE1 = DEFN.BCNAME
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Components',RTRIM(ITEM.OBJECTVALUE1) || '.' || RTRIM(ITEM.OBJECTVALUE2) AS "Components", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSPNLGRPDEFN DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 7
AND ITEM.OBJECTVALUE1 = DEFN.PNLGRPNAME
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Fields',ITEM.OBJECTVALUE1 AS "Fields", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSDBFIELD DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 2
AND ITEM.OBJECTVALUE1 = DEFN.FIELDNAME
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Menus',ITEM.OBJECTVALUE1 AS "Menus", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSMENUDEFN DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 6
AND ITEM.OBJECTVALUE1 = DEFN.MENUNAME
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Message Catalog Entries',RTRIM(ITEM.OBJECTVALUE1) || ', ' || RTRIM(ITEM.OBJECTVALUE2) AS "Message Catalog Entries", '',DEFN.LAST_UPDATE_DTTM
FROM PSPROJECTITEM ITEM, PSMSGCATDEFN DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 25
AND ITEM.OBJECTVALUE1 = DEFN.MESSAGE_SET_NBR AND ITEM.OBJECTVALUE2 = DEFN.MESSAGE_NBR
AND DEFN.LAST_UPDATE_DTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Message Channels',ITEM.OBJECTVALUE1 AS "Message Channels", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSCHNLDEFN DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 36
AND ITEM.OBJECTVALUE1 = DEFN.CHNLNAME
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Messages',ITEM.OBJECTVALUE1 AS "Messages", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSMSGDEFN DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 37
AND ITEM.OBJECTVALUE1 = DEFN.MSGNAME
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Pages',RTRIM(ITEM.OBJECTVALUE1) AS "Pages", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSPNLDEFN DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 5
AND ITEM.OBJECTVALUE1 = DEFN.PNLNAME
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Application Engine PeopleCode',RTRIM(ITEM.OBJECTVALUE1) || '.' || RTRIM(SUBSTR(ITEM.OBJECTVALUE2, 1, 8)) || '.' || RTRIM(SUBSTR(ITEM.OBJECTVALUE2, 9, 3)) || '.' || RTRIM(ITEM.OBJECTVALUE3) AS "Application Engine PeopleCode", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSPCMPROG DEFN
WHERE PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 43
AND ITEM.OBJECTVALUE1 = DEFN.OBJECTVALUE1 AND SUBSTR(ITEM.OBJECTVALUE2, 1, 8) = DEFN.OBJECTVALUE2 AND SUBSTR(ITEM.OBJECTVALUE2, 9, 3) = DEFN.OBJECTVALUE3 AND ITEM.OBJECTVALUE3 = DEFN.OBJECTVALUE6
AND DEFN.PROGSEQ = 0
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Application Package PeopleCode',RTRIM(ITEM.OBJECTVALUE1) || '.' || RTRIM(ITEM.OBJECTVALUE2) || '.' || RTRIM(ITEM.OBJECTVALUE3) AS "Application Package PeopleCode", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSPCMPROG DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 58
AND ITEM.OBJECTVALUE1 = DEFN.OBJECTVALUE1 AND ITEM.OBJECTVALUE2 = DEFN.OBJECTVALUE2 AND (ITEM.OBJECTID3 <> 107 OR ITEM.OBJECTVALUE3 = DEFN.OBJECTVALUE3) AND (ITEM.OBJECTID4 <> 107 OR ITEM.OBJECTVALUE4 = DEFN.OBJECTVALUE4)
AND DEFN.PROGSEQ = 0
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Component Interface PeopleCode',RTRIM(ITEM.OBJECTVALUE1) || '.' || RTRIM(ITEM.OBJECTVALUE2) AS "Component Interface PeopleCode", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSPCMPROG DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 42
AND ITEM.OBJECTVALUE1 = DEFN.OBJECTVALUE1 AND ITEM.OBJECTVALUE2 = DEFN.OBJECTVALUE2
AND DEFN.PROGSEQ = 0
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Component PeopleCode',RTRIM(ITEM.OBJECTVALUE1) || '.' || RTRIM(ITEM.OBJECTVALUE2) || '.' || RTRIM(ITEM.OBJECTVALUE3) AS "Component PeopleCode", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSPCMPROG DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 46
AND ITEM.OBJECTVALUE1 = DEFN.OBJECTVALUE1 AND ITEM.OBJECTVALUE2 = DEFN.OBJECTVALUE2 AND ITEM.OBJECTVALUE3 = DEFN.OBJECTVALUE3
AND DEFN.PROGSEQ = 0
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Component Record PeopleCode',RTRIM(ITEM.OBJECTVALUE1) || '.' || RTRIM(ITEM.OBJECTVALUE2) || '.' || RTRIM(ITEM.OBJECTVALUE3) || '.' || RTRIM(ITEM.OBJECTVALUE4) AS "Component Record PeopleCode", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSPCMPROG DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 47
AND ITEM.OBJECTVALUE1 = DEFN.OBJECTVALUE1 AND ITEM.OBJECTVALUE2 = DEFN.OBJECTVALUE2 AND ITEM.OBJECTVALUE3 = DEFN.OBJECTVALUE3 AND ITEM.OBJECTVALUE4 = DEFN.OBJECTVALUE4
AND DEFN.PROGSEQ = 0
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Page PeopleCode',RTRIM(ITEM.OBJECTVALUE1) || '.' || RTRIM(ITEM.OBJECTVALUE2) AS "Page PeopleCode", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSPCMPROG DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 44
AND ITEM.OBJECTVALUE1 = DEFN.OBJECTVALUE1 AND ITEM.OBJECTVALUE2 = DEFN.OBJECTVALUE2
AND DEFN.PROGSEQ = 0
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Record PeopleCode',RTRIM(ITEM.OBJECTVALUE1) || '.' || RTRIM(ITEM.OBJECTVALUE2) || '.' || RTRIM(ITEM.OBJECTVALUE3) AS "Record PeopleCode", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSPCMPROG DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 8
AND ITEM.OBJECTVALUE1 = DEFN.OBJECTVALUE1 AND ITEM.OBJECTVALUE2 = DEFN.OBJECTVALUE2 AND ITEM.OBJECTVALUE3 = DEFN.OBJECTVALUE3
AND DEFN.PROGSEQ = 0
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Component RecFld PeopleCode',RTRIM(ITEM.OBJECTVALUE1) || '.' || RTRIM(ITEM.OBJECTVALUE2) || '.' || RTRIM(ITEM.OBJECTVALUE3) || '.' || RTRIM(SUBSTR(ITEM.OBJECTVALUE4, 1, 18)) || '.' || RTRIM(SUBSTR(ITEM.OBJECTVALUE4, 19, 12)) AS "Comp Record Field PeopleCode",DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSPCMPROG DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 48
AND ITEM.OBJECTVALUE1 = DEFN.OBJECTVALUE1 AND ITEM.OBJECTVALUE2 = DEFN.OBJECTVALUE2 AND ITEM.OBJECTVALUE3 = DEFN.OBJECTVALUE3 AND SUBSTR(ITEM.OBJECTVALUE4, 1, 18) = DEFN.OBJECTVALUE4 AND SUBSTR(ITEM.OBJECTVALUE4, 19, 12) = DEFN.OBJECTVALUE5
AND DEFN.PROGSEQ = 0
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Message PeopleCode',RTRIM(ITEM.OBJECTVALUE1) || '.' || RTRIM(ITEM.OBJECTVALUE2) || '.' || RTRIM(ITEM.OBJECTVALUE3) AS "Message PeopleCode", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSPCMPROG DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 40
AND ITEM.OBJECTVALUE1 = DEFN.OBJECTVALUE1 AND ITEM.OBJECTVALUE2 = DEFN.OBJECTVALUE2 AND ITEM.OBJECTVALUE3 = DEFN.OBJECTVALUE3
AND DEFN.PROGSEQ = 0
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Portal Registry Structure',RTRIM(ITEM.OBJECTVALUE1) || '.' || RTRIM(ITEM.OBJECTVALUE3) AS "Portal Registry Structure", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSPRSMDEFN DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTVALUE1 = DEFN.PORTAL_NAME
AND ITEM.OBJECTVALUE3 = DEFN.PORTAL_OBJNAME
AND ITEM.OBJECTTYPE = 55
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Records',RTRIM(ITEM.OBJECTVALUE1) AS "Records", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSRECDEFN DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 0
AND ITEM.OBJECTVALUE1 = DEFN.RECNAME
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'SQL(Non App Engine)',RTRIM(ITEM.OBJECTVALUE1) AS "SQL (Non Application Engine)", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSSQLDEFN DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 30 and ITEM.OBJECTVALUE2 IN (0,2)
AND ITEM.OBJECTVALUE1 = DEFN.SQLID
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'SQL (Application Engine)',RTRIM(SUBSTR(ITEM.OBJECTVALUE1, 1, 12)) || '.' || RTRIM(SUBSTR(ITEM.OBJECTVALUE1, 13, 8)) || '.' || RTRIM(SUBSTR(ITEM.OBJECTVALUE1, 21, 8)) AS "SQL (Application Engine)", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSSQLDEFN DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 30 and ITEM.OBJECTVALUE2 = 1
AND ITEM.OBJECTVALUE1 = DEFN.SQLID
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Style Sheet',RTRIM(ITEM.OBJECTVALUE1) AS "Style Sheet", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSSTYLSHEETDEFN DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 50
AND ITEM.OBJECTVALUE1 = DEFN.STYLESHEETNAME
UNION
SELECT ITEM.PROJECTNAME,'Translate Values',RTRIM(ITEM.OBJECTVALUE1) || ':' || RTRIM(ITEM.OBJECTVALUE2) AS "Translate Values", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSXLATITEM DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 4
AND ITEM.OBJECTVALUE1 = DEFN.FIELDNAME AND ITEM.OBJECTVALUE2 = DEFN.FIELDVALUE
AND DEFN.LASTUPDDTTM > TO_DATE('2000-01-01', 'YYYY-MM-DD')
UNION
SELECT ITEM.PROJECTNAME,'Process Definition',RTRIM(ITEM.OBJECTVALUE1) || ':' || RTRIM(ITEM.OBJECTVALUE2) AS "Process Definitions", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PS_PRCSDEFN DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 20
AND ITEM.OBJECTVALUE2 = DEFN.PRCSNAME
UNION
SELECT ITEM.PROJECTNAME,'Query',RTRIM(ITEM.OBJECTVALUE1) AS "Query", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PSQRYDEFN DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 10
AND ITEM.OBJECTVALUE1 = DEFN.QRYNAME
UNION
SELECT ITEM.PROJECTNAME,'HTML Definition',RTRIM(ITEM.OBJECTVALUE1) AS "HTML Definitions",HTM.LASTUPDOPRID,HTM.LASTUPDDTTM
FROM PSPROJECTITEM ITEM,PSCONTDEFN HTM
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTVALUE1=HTM.CONTNAME
AND ITEM.OBJECTTYPE = 51
UNION
SELECT ITEM.PROJECTNAME,'Indexes',IND.RECNAME,REC.LASTUPDOPRID,REC.LASTUPDDTTM FROM PSPROJECTITEM ITEM,PSRECDEFN REC,PSINDEXDEFN IND
WHERE ITEM.OBJECTTYPE=1 AND ITEM.OBJECTVALUE1=REC.RECNAME AND ITEM.OBJECTVALUE2=IND.INDEXID AND REC.RECNAME=IND.RECNAME
AND ITEM.PROJECTNAME IN (<project names>)
UNION
SELECT ITEM.PROJECTNAME,'File Layout',FL.FLDDEFNNAME,FL.LASTUPDOPRID,FL.LASTUPDDTTM FROM PSPROJECTITEM ITEM,PSFLDDEFN FL
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE=31 AND ITEM.OBJECTVALUE1=FL.FLDDEFNNAME
UNION
SELECT ITEM.PROJECTNAME,'Job Definition',RTRIM(ITEM.OBJECTVALUE1) || ':' || RTRIM(ITEM.OBJECTVALUE2) AS "Process Definitions", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PS_PRCSJOBDEFN DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 23
AND ITEM.OBJECTVALUE2 = DEFN.PRCSJOBNAME
UNION
SELECT ITEM.PROJECTNAME,'Recurrence Definition',RTRIM(ITEM.OBJECTVALUE1) || ':' || RTRIM(ITEM.OBJECTVALUE2) AS "Process Definitions", DEFN.LASTUPDOPRID, DEFN.LASTUPDDTTM
FROM PSPROJECTITEM ITEM, PS_PRCSRECUR DEFN
WHERE ITEM.PROJECTNAME IN (<project names>)
AND ITEM.OBJECTTYPE = 24
AND ITEM.OBJECTVALUE2 = DEFN.RECURNAME