PeopleSoft

Useful PeopleSoft HCM Queries

Matthew McGarity

Matthew McGarity

— 33 min read

In a past work life, I spent much of my time honing PeopleSoft HCM data. Over the years, I accumulated a store of useful SQL scripts as part of my data quality duties. I’m no longer involved with using PeopleSoft (thank God), but here was my output in the event that someone can leverage this knowledge.

All scripts are written in PL-SQL but can be easily adapted to whatever query language you are using.  Most are for PSFT HCM, although there are some that work with PSFT Financials.  These were written against PeopleSoft 8.9, so I can’t ensure their compatibility past that version.

Component Interface Access

The following SQL statement identifies who has full access to Component Interfaces, and the Permission Lists/Roles to which they are tied:

SELECT A.ROLEUSER,
 A.ROLENAME,
 B.CLASSID,
 C.CLASSDEFNDESC,
 D.BCNAME
 FROM SYSADM.PSROLEUSER A,
 SYSADM.PSROLECLASS B,
 SYSADM.PSCLASSDEFN C,
 SYSADM.PSAUTHBUSCOMP D
 WHERE B.ROLENAME = A.ROLENAME
 AND C.CLASSID = B.CLASSID
 AND D.CLASSID = C.CLASSID
 AND D.BCMETHOD = 'Create'
 AND D.AUTHORIZEDACTIONS = 4;

Duplicate National IDs

The following SQL returns a list of PeopleSoft Employee IDs which share the exact same National ID as another Associate. By default, all duplicates are returned by this query, but it can be modified to research Associates one at a time.

Input Parameters:

  • EMPLID: Employee ID of individual Associate being researched.
SELECT A.EMPLID,
 A.COUNTRY,
 A.NATIONAL_ID_TYPE,
 A.NATIONAL_ID
 FROM PS_PERS_NID A
 WHERE EXISTS (SELECT 'x'
 FROM PS_PERS_NID B
 WHERE A.COUNTRY = B.COUNTRY
 AND A.NATIONAL_ID_TYPE = B.NATIONAL_ID_TYPE
 AND A.NATIONAL_ID = B.NATIONAL_ID
 --AND B.EMPLID = :EMPLID -- Uncomment to research an individual Associate
 AND A.EMPLID != B.EMPLID)
 --OR A.EMPLID = :EMPLID -- Uncomment to research an individual Associate
 ORDER BY A.COUNTRY,
 A.NATIONAL_ID_TYPE,
 A.NATIONAL_ID,
 A.EMPLID;

PeopleSoft Component Access

The following SQL returns a list of PeopleSoft users with access to a specific Component (as defined in Application Designer), along with the maximum level of access they possess.

Input Parameters:

  • PNLGRPNAME: Name of Component being researched. To determine the name of a Component while logged into PeopleSoft, use the -J command
SELECT E.PNLGRPNAME "Component Name",
 A.OPRID "OprId",
 A.OPRDEFNDESC "Name",
 F.DEPTNAME "HR Department",
 F.WORK_PHONE "Work Phone",
 DECODE(MAX(D.DISPLAYONLY),0,'N',
 'Y') "Display Only",
 CASE
 WHEN BITAND(MAX(D.AUTHORIZEDACTIONS),1) > 0 THEN 'Y'
 ELSE 'N'
 END "Add",
 CASE
 WHEN BITAND(MAX(D.AUTHORIZEDACTIONS),2) > 0 THEN 'Y'
 ELSE 'N'
 END "Update",
 CASE
 WHEN BITAND(MAX(D.AUTHORIZEDACTIONS),4) > 0 THEN 'Y'
 ELSE 'N'
 END "Update All",
 CASE
 WHEN BITAND(MAX(D.AUTHORIZEDACTIONS),8) > 0 THEN 'Y'
 ELSE 'N'
 END "Correction",
 CASE
 WHEN MAX(D.AUTHORIZEDACTIONS) > 15 THEN 'Y'
 ELSE 'N'
 END "Special"
 FROM PSOPRDEFN A,
 PSROLEUSER B,
 PSROLECLASS C,
 PSAUTHITEM D,
 PSMENUITEM E,
 PS_EMPLOYEES F
 WHERE A.OPRID = B.ROLEUSER
 AND B.ROLENAME = C.ROLENAME
 AND C.CLASSID = D.CLASSID
 AND D.MENUNAME = E.MENUNAME
 AND D.BARNAME = E.BARNAME
 AND D.BARITEMNAME = E.ITEMNAME
 AND A.EMPLID = F.EMPLID
 AND E.PNLGRPNAME = :PNLGRPNAME
 AND F.EMPL_STATUS = 'A'
 GROUP BY E.PNLGRPNAME,A.OPRID,A.OPRDEFNDESC,F.DEPTNAME,
 F.CIS_FIN_DEPT_DESCR,F.WORK_PHONE
 ORDER BY A.OPRID;

HCM Bundles, Maintenance Packs, and Tax Updates

The following SQL statements return which HCM 8.9 Bundles, Maintenance Packs, and Tax Updates have been installed in the current system. Because the only reliable way to determine their presence is checking for specific Report IDs, these queries should be updated regularly to incorporate future Bundle/Maintenance Pack/Tax Update-specific Report IDs as they are known.

Consult Oracle MetaLink for the release schedule:

  • 2011 release schedule
  • 2010 release schedule
  • 2009 release schedule
SELECT A.UPDATE_ID,
 CASE A.UPDATE_ID
 WHEN '601072' THEN 'HRMS 8.9 Bundle #1'
 WHEN '601074' THEN 'HRMS 8.9 Bundle #2'
 WHEN '601075' THEN 'HRMS 8.9 Bundle #3'
 WHEN '625723' THEN 'HRMS 8.9 Bundle #4'
 WHEN '637650' THEN 'HRMS 8.9 Bundle #5'
 WHEN '638558' THEN 'HRMS 8.9 Bundle #6'
 WHEN '644757' THEN 'HRMS 8.9 Bundle #7'
 WHEN '650887' THEN 'HRMS 8.9 Bundle #8'
 WHEN '652483' THEN 'HRMS 8.9 Bundle #9'
 WHEN '652560' THEN 'HRMS 8.9 Bundle #10'
 WHEN '686920' THEN 'HRMS 8.9 Bundle #11'
 WHEN '687033' THEN 'HRMS 8.9 Bundle #12'
 WHEN '689727' THEN 'HRMS 8.9 Bundle #13'
 WHEN '689868' THEN 'HRMS 8.9 Bundle #14'
 WHEN '732075' THEN 'HRMS 8.9 Bundle #15'
 WHEN '733684' THEN 'HRMS 8.9 Bundle #16'
 WHEN '734326' THEN 'HRMS 8.9 Bundle #17'
 WHEN '734685' THEN 'HRMS 8.9 Bundle #18'
 WHEN '765554' THEN 'HRMS 8.9 Bundle #19'
 WHEN '773254' THEN 'HRMS 8.9 Bundle #20'
 WHEN '773257' THEN 'HRMS 8.9 Bundle #21'
 WHEN '773258' THEN 'HRMS 8.9 Bundle #22'
 WHEN '814953' THEN 'HRMS 8.9 Bundle #23'
 WHEN '850728' THEN 'HRMS 8.9 Bundle #24'
 WHEN '852837' THEN 'HRMS 8.9 Bundle #25'
 ELSE 'ERROR'
 END
 "Report ID Description",
 SUBSTR(B.GLOBAL_NAME, 1, INSTR(B.GLOBAL_NAME, '.') - 1) "Database"
 FROM PS_MAINTENANCE_LOG A,
 GLOBAL_NAME B
 WHERE A.UPDATE_ID IN ( '601072', '601074', '601075', '625723',
 '637650', '638558', '644757', '650887',
 '652483', '652560', '686920', '687033',
 '689727', '689868', '732075', '733684',
 '734326', '734685', '765554', '773254',
 '773257', '773258', '814953', '850728', '852837' )
 GROUP BY A.UPDATE_ID,
 B.GLOBAL_NAME
 ORDER BY A.UPDATE_ID DESC;
 SELECT A.UPDATE_ID,
 CASE A.UPDATE_ID
 WHEN '584111' THEN 'Maintenance Pack #01'
 WHEN '625594' THEN 'Maintenance Pack #02'
 WHEN '643756' THEN 'Maintenance Pack #03 Cumulative'
 WHEN '643755' THEN 'Maintenance Pack #03 Delta'
 WHEN '662300' THEN 'Maintenance Pack #04 Cumulative'
 WHEN '662301' THEN 'Maintenance Pack #04 Delta'
 WHEN '671975' THEN 'Maintenance Pack #05 Cumulative'
 WHEN '671976' THEN 'Maintenance Pack #05 Delta'
 WHEN '681500' THEN 'Maintenance Pack #06 Cumulative'
 WHEN '681501' THEN 'Maintenance Pack #06 Delta'
 WHEN '702787' THEN 'Maintenance Pack #07 Delta'
 WHEN '717079' THEN 'Maintenance Pack #08 Delta'
 WHEN '730331' THEN 'Maintenance Pack #09 Delta'
 WHEN '750458' THEN 'Maintenance Pack #10 Delta'
 WHEN '765183' THEN 'Maintenance Pack #11 Delta'
 WHEN '781458' THEN 'Maintenance Pack #12 Delta'
 WHEN '796732' THEN 'Maintenance Pack #13 Delta'
 WHEN '815122' THEN 'Maintenance Pack #14 Delta'
 ELSE 'ERROR'
 END
 "Report ID Description",
 SUBSTR(B.GLOBAL_NAME, 1, INSTR(B.GLOBAL_NAME, '.') - 1) "Database"
 FROM PS_MAINTENANCE_LOG A,
 GLOBAL_NAME B
 WHERE A.UPDATE_ID IN ( '584111', '625594', '643756', '643755',
 '662300', '662301', '671975', '671976',
 '681500', '681501', '702787', '717079',
 '730331', '750458', '765183', '781458',
 '796732', '815122' )
 GROUP BY A.UPDATE_ID,
 B.GLOBAL_NAME
 ORDER BY 2 DESC;
 SELECT A.UPDATE_ID,
 CASE A.UPDATE_ID
 WHEN '606464' THEN 'Payroll Tax Update 04-E/04-F/05-A'
 WHEN '595578' THEN 'Payroll Tax Update 05-B'
 WHEN '609427' THEN 'Payroll Tax Update 05-C'
 WHEN '609539' THEN 'Payroll Tax Update 05-D'
 WHEN '621657' THEN 'Payroll Tax Update 05-E'
 WHEN '639121' THEN 'Payroll Tax Update 05-F'
 WHEN '642209' THEN 'Payroll Tax Update 06-A'
 WHEN '642283' THEN 'Payroll Tax Update 06-B'
 WHEN '654778' THEN 'Payroll Tax Update 06-C'
 WHEN '654802' THEN 'Payroll Tax Update 06-D'
 WHEN '654810' THEN 'Payroll Tax Update 06-E'
 WHEN '654913' THEN 'Payroll Tax Update 06-F'
 WHEN '694791' THEN 'Payroll Tax Update 07-A'
 WHEN '698734' THEN 'Payroll Tax Update 07-B'
 WHEN '702213' THEN 'Payroll Tax Update 07-C'
 WHEN '716885' THEN 'Payroll Tax Update 07-D'
 WHEN '726638' THEN 'Payroll Tax Update 07-E'
 WHEN '731473' THEN 'Payroll Tax Update 07-F'
 WHEN '733482' THEN 'Payroll Tax Update 08-A'
 WHEN '739115' THEN 'Payroll Tax Update 08-B'
 WHEN '741851' THEN 'Payroll Tax Update 08-C'
 WHEN '741865' THEN 'Payroll Tax Update 08-D'
 WHEN '741923' THEN 'Payroll Tax Update 08-E'
 WHEN '741929' THEN 'Payroll Tax Update 08-F'
 WHEN '766551' THEN 'Payroll Tax Update 09-A'
 WHEN '766647' THEN 'Payroll Tax Update 09-B'
 WHEN '766654' THEN 'Payroll Tax Update 09-C'
 WHEN '766828' THEN 'Payroll Tax Update 09-D'
 WHEN '766835' THEN 'Payroll Tax Update 09-E'
 WHEN '766840' THEN 'Payroll Tax Update 09-F'
 WHEN '804979' THEN 'Payroll Tax Update 10-A'
 WHEN '804986' THEN 'Payroll Tax Update 10-B'
 WHEN '804997' THEN 'Payroll Tax Update 10-C'
 WHEN '805003' THEN 'Payroll Tax Update 10-D'
 WHEN '805003' THEN 'Payroll Tax Update 10-D'
 WHEN '813278' THEN 'Payroll Tax Update 10-E'
 WHEN '813288' THEN 'Payroll Tax Update 10-F'
 WHEN '856194' THEN 'Payroll Tax Update 11-A'
 WHEN '860364' THEN 'Payroll Tax Update 11-B'
 WHEN '860804' THEN 'Payroll Tax Update 11-C'
 WHEN '860811' THEN 'Payroll Tax Update 11-D'
 WHEN '860822' THEN 'Payroll Tax Update 11-E'
 WHEN '860832' THEN 'Payroll Tax Update 11-F'
 ELSE 'ERROR'
 END
 "Report ID Description",
 SUBSTR(B.GLOBAL_NAME, 1, INSTR(B.GLOBAL_NAME, '.') - 1) "Database"
 FROM PS_MAINTENANCE_LOG A,
 GLOBAL_NAME B
 WHERE A.UPDATE_ID IN ( '595578', '606464', '609427', '609539',
 '621657', '639121', '642209', '642283',
 '654778', '654802', '654810', '654913',
 '694791', '698734', '702213', '716885',
 '733482', '731473', '726638', '739115',
 '741851', '741865', '741923', '741929',
 '766551', '766647', '766654', '766828',
 '766835', '766840', '804979', '804986',
 '804997', '805003', '813278', '813288',
 '856194', '860364', '860804', '860811',
 '860822', '860832' )
 GROUP BY A.UPDATE_ID,
 B.GLOBAL_NAME
 ORDER BY 2 DESC;

PeopleSoft Navigation Path Lookup

The following SQL returns the PeopleSoft 8.9 navigation path for a single Component. For instructions on returning navigation paths for all Components, please see comments within the script.

Input Parameters:

  • PNLGRPNAME: Name of Component (as shown in Application Designer) whose navigation path is being sought
SELECT A.PORTAL_URI_SEG2 "8.9 Component Name",
 DECODE(F.PORTAL_LABEL,NULL,NULL,
 F.PORTAL_LABEL
 ||' > ')
 ||DECODE(E.PORTAL_LABEL,NULL,NULL,
 E.PORTAL_LABEL
 ||' > ')
 ||DECODE(D.PORTAL_LABEL,NULL,NULL,
 D.PORTAL_LABEL
 ||' > ')
 ||DECODE(C.PORTAL_LABEL,NULL,NULL,
 C.PORTAL_LABEL
 ||' > ')
 ||DECODE(B.PORTAL_LABEL,NULL,NULL,
 B.PORTAL_LABEL
 ||' > ')
 ||DECODE(A.PORTAL_LABEL,NULL,NULL,
 A.PORTAL_LABEL) "8.9 Navigation Path"
 FROM PSPRSMDEFN A,
 PSPRSMDEFN B,
 PSPRSMDEFN C,
 PSPRSMDEFN D,
 PSPRSMDEFN E,
 PSPRSMDEFN F
 WHERE A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME (+)
 AND A.PORTAL_NAME = B.PORTAL_NAME (+)
 AND B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME (+)
 AND B.PORTAL_NAME = C.PORTAL_NAME (+)
 AND C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAME (+)
 AND C.PORTAL_NAME = D.PORTAL_NAME (+)
 AND D.PORTAL_PRNTOBJNAME = E.PORTAL_OBJNAME (+)
 AND D.PORTAL_NAME = E.PORTAL_NAME (+)
 AND E.PORTAL_PRNTOBJNAME = F.PORTAL_OBJNAME (+)
 AND E.PORTAL_NAME = F.PORTAL_NAME (+)
 AND A.PORTAL_NAME = 'EMPLOYEE'
 AND A.PORTAL_LABEL != ' '
 AND A.PORTAL_URI_SEG2 != ' '
 AND a.PORTAL_URI_SEG2 = :PNLGRPNAME
 ORDER BY A.PORTAL_URI_SEG2;

Record Fields and Datatypes

The following SQL returns a list of columns for a specific Record (as defined in Application Designer), along with their datatypes.

Input Parameters:

  • RECNAME: Name of Record being researched. Do not include the “PS” or “PS_” prefixes.
SELECT :RECNAME "RECNAME",
 A.COLUMN_NAME "FIELDNAME",
 A.DATA_TYPE,
 A.DATA_LENGTH,
 A.DATA_PRECISION,
 A.DATA_SCALE
 FROM ALL_TAB_COLUMNS A
 WHERE A.TABLE_NAME = (SELECT DECODE(B.SQLTABLENAME,' ','PS_'
 ||B.RECNAME,
 B.SQLTABLENAME)
 FROM PSRECDEFN B
 WHERE B.RECNAME = :RECNAME)
 ORDER BY A.COLUMN_ID;

Competencies (Skills) List

The following SQL returns a list of PeopleSoft Competencies (a.k.a. Skills).

SELECT (SELECT A.DESCR
 FROM PS_CM_TYPE_TBL A
 WHERE A.CM_TYPE = SUBSTR(B.CM_TYPE,1,3)) "Competency Type Level 1",
 C.DESCR "Competency Type Level 2",
 E.XLATLONGNAME "Competency Category",
 CASE
 WHEN D.CM_CATEGORY = 'S'
 THEN D.DESCR
 ELSE F.DESCR
 ||' '
 ||D.DESCR
 END
 FROM PS_COMPETENCY_TYPS B,
 PS_CM_TYPE_TBL C,
 PS_COMPETENCY_TBL D,
 PSXLATITEM E,
 PS_TRN_VENDOR_TBL F
 WHERE B.CM_TYPE = C.CM_TYPE
 AND B.COMPETENCY = D.COMPETENCY
 AND D.CM_CATEGORY = E.FIELDVALUE
 AND D.VENDOR = F.VENDOR (+)
 AND E.FIELDNAME = 'CM_CATEGORY'
 AND E.EFFDT = (SELECT MAX(E1.EFFDT)
 FROM PSXLATITEM E1
 WHERE E.FIELDNAME = E1.FIELDNAME
 AND E.FIELDVALUE = E1.FIELDVALUE)
 ORDER BY 1,
 2,
 CASE
 WHEN D.CM_CATEGORY = 'S'
 THEN 1
 WHEN D.CM_CATEGORY = 'P'
 THEN 2
 ELSE 3
 END,
 3;

PeopleSoft Object Audit Compares > Field-Level

The following SQL returns the names of all Records whose field-level audit flags do not match. In addition, it will translate the USEEDIT bit value one or more of the following flags:

  • A: Add
  • C: Change
  • D: Delete

As currently written, this SQL is designed to be run in any non-production HR environment; the audit flags will then be compared between the current environment and production (here, referred to using a database link “R_PHR”).

SELECT A.RECNAME,
 A.FIELDNAME,
 CASE
 WHEN BITAND(A.USEEDIT,8) > 0
 AND BITAND(A.USEEDIT,128) = 0
 AND BITAND(A.USEEDIT,1024) = 0 THEN 'A'
 WHEN BITAND(A.USEEDIT,8) > 0
 AND BITAND(A.USEEDIT,128) > 0
 AND BITAND(A.USEEDIT,1024) = 0 THEN 'AC'
 WHEN BITAND(A.USEEDIT,8) > 0
 AND BITAND(A.USEEDIT,128) > 0
 AND BITAND(A.USEEDIT,1024) > 0 THEN 'ACD'
 WHEN BITAND(A.USEEDIT,8) = 0
 AND BITAND(A.USEEDIT,128) > 0
 AND BITAND(A.USEEDIT,1024) = 0 THEN ' C'
 WHEN BITAND(A.USEEDIT,8) = 0
 AND BITAND(A.USEEDIT,128) > 0
 AND BITAND(A.USEEDIT,1024) > 0 THEN ' CD'
 WHEN BITAND(A.USEEDIT,8) = 0
 AND BITAND(A.USEEDIT,128) = 0
 AND BITAND(A.USEEDIT,1024) > 0 THEN ' D'
 ELSE NULL
 END AS "USEEDIT_TARGET",
 CASE
 WHEN BITAND(B.USEEDIT,8) > 0
 AND BITAND(B.USEEDIT,128) = 0
 AND BITAND(B.USEEDIT,1024) = 0 THEN 'A'
 WHEN BITAND(B.USEEDIT,8) > 0
 AND BITAND(B.USEEDIT,128) > 0
 AND BITAND(B.USEEDIT,1024) = 0 THEN 'AC'
 WHEN BITAND(B.USEEDIT,8) > 0
 AND BITAND(B.USEEDIT,128) > 0
 AND BITAND(B.USEEDIT,1024) > 0 THEN 'ACD'
 WHEN BITAND(B.USEEDIT,8) = 0
 AND BITAND(B.USEEDIT,128) > 0
 AND BITAND(B.USEEDIT,1024) = 0 THEN ' C'
 WHEN BITAND(B.USEEDIT,8) = 0
 AND BITAND(B.USEEDIT,128) > 0
 AND BITAND(B.USEEDIT,1024) > 0 THEN ' CD'
 WHEN BITAND(B.USEEDIT,8) = 0
 AND BITAND(B.USEEDIT,128) = 0
 AND BITAND(B.USEEDIT,1024) > 0 THEN ' D'
 ELSE NULL
 END AS "USEEDIT_CURRENT"
 FROM PSRECFIELD@R_PHR A,
 PSRECFIELD B
 WHERE A.RECNAME = B.RECNAME (+)
 AND A.FIELDNAME = B.FIELDNAME (+)
 AND (BITAND(A.USEEDIT,8) != BITAND(B.USEEDIT,8)
 OR BITAND(A.USEEDIT,128) != BITAND(B.USEEDIT,128)
 OR BITAND(A.USEEDIT,1024) != BITAND(B.USEEDIT,1024))
 ORDER BY A.RECNAME,
 A.FIELDNUM;

PeopleSoft Object Audit Compares > Record-Level

The following SQL returns the names of all Records whose Audit Record names do not match. As currently written, this SQL is designed to be run in any non-production HR environment; the audit flags will then be compared between the current environment and production (once again, a DB link called “R_PHR”.

SELECT A.RECNAME,
 A.AUDITRECNAME "AUDITRECNAME_PRODUCTION",
 B.AUDITRECNAME "AUDITRECNAME_CURRENT"
 FROM PSRECDEFN@R_PHR A,
 PSRECDEFN B
 WHERE A.RECNAME = B.RECNAME
 AND A.AUDITRECNAME != B.AUDITRECNAME

HCM Workflows > Roles/Role Queries

The following SQL returns Roles and matching Role Query names for a HCM Workflows. Searches can be performed for specific field values, including Activity Name and Event Name.

Input Parameters:

  • ACTIVITYNAME: Name of specific Activity for limiting results
  • EVENTNAME: Name of specific Event Name for limiting results
SELECT A.ACTIVITYNAME,
 A.EVENTNAME,
 B.ROLENAME,
 B.QRYNAME
 FROM PSMAPROLENAME A,
 PSROLEDEFN B
 WHERE A.ROLENAME = B.ROLENAME
 --AND A.ACTIVITYNAME = :ACTIVITYNAME -- Uncomment to search for specific Activity Name
 --AND A.EVENTNAME = :EVENTNAME -- Uncomment to search for specific Event Name
 --AND B.QRYNAME != ' ' -- Uncomment to bring back only rows referencing Role Queries
 ORDER BY A.ACTIVITYNAME,
 A.EVENTNAME;

HCM Workflows > Workflow Details

The following SQL returns ALL information on HCM Workflows. Searches can be performed for specific field values, including Business Process Name, Activity Name, Role Name, etc.

Three artificial keys are constructed to assist with offline data analysis:

  • Key 1: combination of Business Process and Activity Name
  • Key 2: combination of Event Name and Routing Name
  • Key 3: combination of 1) Routing Field Number; 2) order of Expresions, if any; 3) order of Roles, if any; and 4) order of Binds, if any

Input Parameters:

  • BUSPROCNAME: Name of specific Business Process for limiting results
  • ACTIVITYNAME: Name of specific Activity for limiting results
  • EVENTNAME: Name of specific Event for limiting results
  • ROUTENAME: Name of specific Event Routing for limiting results
  • ROLENAME: Name of specific Role Name for limiting results
  • FIELDNAME: Name of specific Bind Field for limiting results

Note: some of the fields returned by this query are of the Long datatype. The content of such fields, including MAPEXPR.EXTTEXT, cannot be extracted by TOAD. If you need to download these values, utilize another tool such as Oracle SQL Developer.

SELECT A.BUSPROCNAME
 ||'/'
 ||C.ACTIVITYNAME "Key 1",
 D.EVENTNAME
 ||'/'
 ||E.ROUTENAME "Key 2",
 LPAD(DECODE(F.SEQNO,NULL,1,
 F.SEQNO + 1),2,'0')
 ||LPAD(DECODE(H.EXPNUM,NULL,0,
 H.EXPNUM),2,'0')
 ||LPAD(DECODE(I.ROLENUM,NULL,0,
 I.ROLENUM),2,'0')
 ||LPAD(DECODE(I.BINDSEQNO,NULL,'0',
 I.BINDSEQNO),2,'0') "Key 3",
 A.BUSPROCNAME,
 A.DESCR60 "Business Process Description",
 C.ACTIVITYNAME,
 C.DESCR60 "Activity Description",
 D.EVENTNAME,
 DECODE(D.ACTIVE,0,'N',
 'Y') "Active?",
 D.RECNAME "PeopleCode Record",
 E.ROUTENAME,
 E.MAPFIELDCNT,
 (F.SEQNO + 1) "Sequence Number",
 F.FORMFIELDNAME,
 E.MAPRECFIELDCNT,
 G.FIELDNUM,
 G.RECNAME,
 G.FIELDNAME,
 E.MAPEXPRCNT,
 H.EXPNUM,
 H.EXPTEXT,
 E.MAPROLENAMECNT,
 I.ROLENUM,
 I.ROLENAME,
 I.ROLEBINDCNT,
 I.BINDSEQNO,
 DECODE(I.BINDTYPE,1,'RECORD.Field',
 'CONSTANT') "Bindtype",
 I.RECNAME,
 I.FIELDNAME,
 I.BINDCONSTANT
 FROM PSBUSPROCDEFN A,
 (SELECT B.OBJNAME,
 B.COMPNAME,
 B.DESCR60
 FROM PSBUSPROCITEM B
 WHERE B.OBJTYPE = 0) B,
 PSACTIVITYDEFN C,
 PSEVENTDEFN D,
 (SELECT E.ACTIVITYNAME,
 E.EVENTNAME,
 E.ROUTENAME,
 E.MAPFIELDCNT,
 E.MAPEXPRCNT,
 E.MAPRECFIELDCNT,
 E.MAPROLENAMECNT
 FROM PSEVENTROUTE E
 WHERE E.ROUTETYPE = 1) E,
 PSMAPFIELD F,
 (SELECT G.ACTIVITYNAME,
 G.EVENTNAME,
 G.MAPTYPE,
 G.MAPNAME,
 (G.FIELDNUM + 1) "FIELDNUM",
 G.RECNAME,
 G.FIELDNAME
 FROM PSMAPRECFIELD G
 WHERE G.FIELDNAME = 'EMPLID') G,
 (SELECT H.ACTIVITYNAME,
 H.EVENTNAME,
 H.MAPTYPE,
 H.MAPNAME,
 (H.EXPNUM + 1) "EXPNUM",
 H.EXPTEXT
 FROM PSMAPEXPR H) H,
 (SELECT I1.ACTIVITYNAME,
 I1.EVENTNAME,
 I1.MAPTYPE,
 I1.MAPNAME,
 (I1.NAMESEQNO + 1) "ROLENUM",
 I1.ROLENAME,
 I1.ROLEBINDCNT,
 (I2.BINDSEQNO + 1) "BINDSEQNO",
 I2.BINDTYPE,
 I2.RECNAME,
 I2.FIELDNAME,
 I2.BINDCONSTANT
 FROM PSMAPROLENAME I1,
 PSMAPROLEBIND I2
 WHERE I1.ACTIVITYNAME = I2.ACTIVITYNAME (+)
 AND I1.EVENTNAME = I2.EVENTNAME (+)
 AND I1.MAPTYPE = I2.MAPTYPE (+)
 AND I1.MAPNAME = I2.MAPNAME (+)
 AND I1.NAMESEQNO = I2.NAMESEQNO (+)) I
 WHERE A.BUSPROCNAME = B.OBJNAME
 AND B.COMPNAME = C.ACTIVITYNAME
 AND C.ACTIVITYNAME = D.ACTIVITYNAME
 AND D.ACTIVITYNAME = E.ACTIVITYNAME
 AND D.EVENTNAME = E.EVENTNAME
 AND E.ACTIVITYNAME = F.ACTIVITYNAME
 AND E.EVENTNAME = F.EVENTNAME
 AND F.ACTIVITYNAME = G.ACTIVITYNAME (+)
 AND F.EVENTNAME = G.EVENTNAME (+)
 AND F.MAPTYPE = G.MAPTYPE (+)
 AND F.MAPNAME = G.MAPNAME (+)
 AND F.RECFIELDNUM = G.FIELDNUM (+)
 AND F.ACTIVITYNAME = H.ACTIVITYNAME (+)
 AND F.EVENTNAME = H.EVENTNAME (+)
 AND F.MAPTYPE = H.MAPTYPE (+)
 AND F.MAPNAME = H.MAPNAME (+)
 AND F.EXPNUM = H.EXPNUM (+)
 AND F.ACTIVITYNAME = I.ACTIVITYNAME (+)
 AND F.EVENTNAME = I.EVENTNAME (+)
 AND F.MAPTYPE = I.MAPTYPE (+)
 AND F.MAPNAME = I.MAPNAME (+)
 AND F.ROLENUM = I.ROLENUM (+)
 AND A.BUSPROCNAME LIKE 'CIS%'
 AND D.ACTIVE = 1
 --AND A.BUSPROCNAME = :BUSPROCNAME -- Uncomment to search for specific Business Process Name
 --AND C.ACTIVITYNAME = :ACTIVITYNAME -- Uncomment to search for specific Activity Name
 --AND D.EVENTNAME = :EVENTNAME -- Uncomment to search for specific Event Name
 --AND E.ROUTENAME = :ROUTENAME -- Uncomment to search for specific Event Routing Name
 --AND F.FORMFIELDNAME IN ('TO','CC') -- Uncomment to bring back only Workflow receipents
 --AND I.ROLENAME = :ROLENAME -- Uncomment to bring back Workflows for a specific Role Name
 --AND I.FIELDNAME = :FIELDNAME -- Uncomment to bring back Workflows using a specific Bind Field, such as EMPLID
 ORDER BY 1,
 2,
 3;

Installation Tables > HRMS

The following SQL returns the Installation Table for PeopleSoft HCM 8.9. Returned column names beginning with “PSC” are descriptions for adjacent fields found on PS_INSTALLATION

SELECT A.COMPANY "Company",
 B.DESCR "PSC Company Descr",
 A.MIN_STD_HRS "Minimum Standard Hours",
 A.MAX_STD_HRS "Maximum Standard Hours",
 A.EMPLID_LENGTH "EMPLID Field Length",
 A.STD_HRS_DEFAULT "Default Standard Hours",
 A.STD_HRS_FREQUENCY "Standard Work Period",
 C.DESCR50 "PSC Standard Work Period Descr",
 A.TEMP_SSN_MASK "'Temporary SSN' Mask",
 A.COMMIT_AFTER "Commit After Empl Processed",
 A.POSITION_MGMT "Position Management Option",
 A.COUNTRY "Country",
 D.DESCR "PSC Country Descr",
 A.COMP_FREQUENCY "Compensation Frequency",
 E.DESCR50 "PSC Compensation Freq Descr",
 A.EMPLID_LAST_EMPL "Last Employee ID Assigned",
 A.NON_EMPLOYEE_LAST "Last H_S Non-Employee ID Assgn",
 A.DEMAND_ID_LAST "Last Demand ID Assigned",
 A.POSN_NBR_LAST "Last Position Number Assigned",
 A.GRIEVANCE_NBR_LAST "Last Grievance Number Assigned",
 A.INCIDENT_NBR_LAST "Last Incident # Assignend",
 A.CLAIM_NBR_LAST "Last H/S Claim # Assigned",
 A.FILE_CREATE_LAST "Last Dir Dep File Creation #",
 A.ROE_NBR_LAST "Last ROE # Used (Canadian)",
 A.CAN_CMA "Canadian Census Metropol Area",
 A.CAN_IND_SECTOR "Canadian Industrial Sector",
 A.MULTI_STEP_GRADE "Multi-Step Grade",
 A.FSA_CLAIMS "FSA Claims Administration",
 A.BENEFIT_ADMINISTRN "Benefits Administration",
 A.START_EVENT_DT "Start Date for BenAdmin",
 A.EXCHNG_TO_CURRENCY "To Currency",
 F.DESCR "PSC To Currency Descr",
 A.EXCHNG_TO_RT_TYPE "Currency Rate Type",
 G.DESCR "PSC Currency Rate Type Descr",
 A.HR "Human Resources",
 A.AM "Asset Management",
 A.AP "Payables",
 A.AR "Receivables",
 A.GL "General Ledger",
 A.BD "Budgets",
 A.PC "Project Costing",
 A.PO "Purchase Order",
 A.INV "Inventory",
 A.BI "Billing",
 A.OM "Order Management",
 A.PUBLIC_SECTOR "Education and Government",
 A.RETROPAY_SEQ_LAST "Last Retropay Request #",
 A.RETRODED_SEQ_LAST "Last Retroded Request Seq. No.",
 A.RETRO_DEDUCTIONS "Retroactive Deductions",
 A.TL "PS/Time and Labor Product",
 A.AUTO_EE_TAX "Automatic Employee Tax Data",
 A.SAME_ST_LCL_RSDNCE "Use State Residence for Local",
 A.FMLA_ADMINISTRN "FMLA Administration",
 A.BAL_ID_FOR_CAL_YR "Balance ID for Calendar Year",
 A.MAX_ROWS_SCROLL "Max Number of Rows in Scrolls",
 A.PA "PS/Pension Administration",
 A.CAR_LAST "Last Car # Assigned",
 A.BEN_BILLING "Benefits Billing",
 A.COBRA_ADMINISTRN "COBRA Administration",
 A.LAST_TL_CONTRCTR "Last TL Contractor ID Assigned",
 A.ORGCHART "Organization Chart",
 A.COBRA_EMPLID_LAST "Last COBRA Emplid Assigned",
 A.FSA_CLAIM_NBR_LAST "Last FSA Claim # Assigned",
 A.FSA_CRY_CLAIM_LAST "Last FSA CarryForward Claim #",
 A.US "USA - United States",
 A.CAN "CAN - Canada",
 A.GER "DEU - Germany",
 A.FRA "FRA - France",
 A.UK "GBR - United Kingdom",
 A.JPN "JPN - Japan",
 A.BEL "BEL - Belgium",
 A.NLD "NLD - Netherlands",
 A.ESP "ESP - Spain",
 A.MEX "MEX - Mexico",
 A.AUS "AUS - Australia",
 A.MYS "MYS - Malaysia",
 A.NZL "NZL - New Zealand",
 A.SGP "SGP - Singapore",
 A.HKG "HKG - Hong Kong",
 A.INSTALLED_PAY_GBL "Global Payroll Core",
 A.IND "IND - India",
 A.BRA "BRA - Brazil",
 A.INSTALLED_PAY_NA "Payroll for North America",
 A.INSTALLED_PAY_INT "Payroll Interface",
 A.USE_POINTS_SW "Salary Points",
 A.USE_RTCD_GROUP_SW "Use Rate Code Groups",
 A.ROWSECCLASS "Row Security Permission List",
 H.CLASSDEFNDESC "PSC Row Sec Perm List Descr",
 A.ILL_NBR_LAST_GER "Last Illness # Assigned",
 A.FED "Federal Functionality",
 A.FP_FLAG "French Public Sector",
 A.GVT_PAY_BASIS "Pay Basis",
 A.ACCT_CD_LAST "Last Account Cd Assigned",
 A.CPS_TRANS_ID_LAST "Last CPS Transmission ID #",
 A.CHE "CHE - Switzerland",
 A.ITA "ITA - Italy",
 A.ALLW_CONC_CALCCONF "Concurrent Calc/Confirm",
 A.PIN_NUM_LAST "Last Used Element Number",
 A.GP_CST_INSTALL_IND "Customer Installation",
 A.AP_INVNO_PREFIX "AP Invoice Number Prefix",
 A.AP_INVNO_LAST "Last AP Invoice Number",
 A.ST "PS/Stock Administration",
 A.ILL_NBR_LAST_NLD "Last Illness Report # Assigned",
 A.ADJ_TL_FINAL_CHK "Change Final Check",
 A.ADJ_TL_ONL_CHK "Change Online Check",
 A.ADJ_TL_REV_ADJ "Change Reversal Adjustments",
 A.REFRSH_TL_JOB_CHG "Refresh on Job Change",
 A.REFRSH_TL_PRECALC "Load in Preliminary Calc",
 A.PSEBENEFITS "PeopleSoft eBenefits",
 A.PSECOMP "PeopleSoft eCompensation",
 A.PSEPAY "PeopleSoft ePay",
 A.PSEPROFILE "PeopleSoft eProfile",
 A.PSERECRUIT "Candidate Gateway",
 A.CCU "Currency Conversion Utility",
 A.PSEA "PeopleSoft eDevelopment",
 A.INSTALLED_GP_AUS "GP - AUS - Australia",
 A.INSTALLED_GP_BRA "GP - BRA - Brazil",
 A.INSTALLED_GP_CAN "GP - CAN - Canada",
 A.INSTALLED_GP_CHE "GP - CHE - Switzerland",
 A.INSTALLED_GP_DEU "GP - DEU - Germany",
 A.INSTALLED_GP_ESP "GP - ESP - Spain",
 A.INSTALLED_GP_FRA "GP - FRA - France",
 A.INSTALLED_GP_HKG "GP - HKG - Hong Kong",
 A.INSTALLED_GP_IND "GP - IND - India",
 A.INSTALLED_GP_ITA "GP - ITA - Italy",
 A.INSTALLED_GP_JPN "GP - JPN - Japan",
 A.INSTALLED_GP_MEX "GP - MEX - Mexico",
 A.INSTALLED_GP_MYS "GP - MYS - Malaysia",
 A.INSTALLED_GP_NLD "GP - NLD - Netherlands",
 A.INSTALLED_GP_NZL "GP - NZL - New Zealand",
 A.INSTALLED_GP_SGP "GP - SGP - Singapore",
 A.INSTALLED_GP_UK "GP - GBR - United Kingdom",
 A.INSTALLED_GP_USA "GP - USA - United States",
 A.PSECOMP_MGR "PeopleSoft eCompensation Mgr",
 A.PSEPROFILE_MGR "PeopleSoft eProfile Manager",
 A.PSERECRUIT_MGR "Talent Acquisition Mgmt",
 A.EPW "Employee Portal",
 A.FO_PBM "Pay/Bill Management",
 A.AUTO_JOB_END "Automatic Job Termination",
 A.AUTO_JOB_SUSPEND "Automatic Job Suspension",
 A.TEMP_ASSGN_ID_LAST "Last Temp Assignment # Used",
 A.EHC "HRMS Portal Pack",
 A.EPG "Government Portal",
 A.EPU "Campus Portal",
 A.APS_AUS "Education _ Government",
 A.DI "Directory Interface",
 A.RP "Resume Processing",
 A.INSTALLED_GP_ARG "GP - ARG - Argentina",
 A.INSTALLED_GP_IRL "GP - IRL - Ireland",
 A.INSTALLED_GP_TWN "GP - TWN - Taiwan",
 A.TL_MOBILE "PS/Mobile Time Management",
 A.PSEPERF_MANAGEMENT "PeopleSoft ePerformance",
 A.AA_ENABLED_JPN "Additional Appointment Enabled",
 A.INSTALLED_PAY_PPS "Payroll PIP - SAP",
 A.AV "Contributor Relations",
 A.SA "Student Administration",
 A.SG "Gradebook",
 A.HGA "Absence Management",
 A.ELM "Enterprise Learning Management",
 A.PS_GL_VERSION "PeopleSoft General Ledger",
 A.ADP "Payroll Interface for ADP",
 A.RSRW "Recruit Workforce",
 A.CSS "Campus Self Service",
 A.HMCF_VALID_CACHE "Cache Validations",
 A.HMCF_CACHE_RETAIN "Cache Retention Days",
 A.HMCF_EIP_LIMIT "EIP Message Limit",
 A.LAST_OSHA_CASE_NBR "Last OSHA Case # Assigned_"
 FROM PS_INSTALLATION A,
 (SELECT B.COMPANY,
 B.DESCR
 FROM PS_COMPANY_TBL B
 WHERE B.EFFDT = (SELECT MAX(B1.EFFDT)
 FROM PS_COMPANY_TBL B1
 WHERE B.COMPANY = B1.COMPANY
 AND B1.EFFDT <= SYSDATE)) B,
 (SELECT C.FREQUENCY_ID,
 C.DESCR50
 FROM PS_STDHRS_FREQ_VW C
 WHERE C.EFFDT = (SELECT MAX(C1.EFFDT)
 FROM PS_STDHRS_FREQ_VW C1
 WHERE C.FREQUENCY_ID = C1.FREQUENCY_ID
 AND C1.EFFDT <= SYSDATE)) C,
 PS_COUNTRY_TBL D,
 (SELECT E.FREQUENCY_ID,
 E.DESCR50
 FROM PS_COMP_FREQ_VW E
 WHERE E.EFFDT = (SELECT MAX(E1.EFFDT)
 FROM PS_COMP_FREQ_VW E1
 WHERE E.FREQUENCY_ID = E1.FREQUENCY_ID
 AND E1.EFFDT <= SYSDATE)) E,
 (SELECT F.CURRENCY_CD,
 F.DESCR
 FROM PS_CURRENCY_CD_TBL F
 WHERE F.EFFDT = (SELECT MAX(F1.EFFDT)
 FROM PS_CURRENCY_CD_TBL F1
 WHERE F.CURRENCY_CD = F1.CURRENCY_CD
 AND F1.EFFDT <= SYSDATE)) F, PS_RT_TYPE_TBL G, PS_OPRDEFN_SCRTY H WHERE A.COMPANY = B.COMPANY (+) AND A.STD_HRS_FREQUENCY = C.FREQUENCY_ID (+) AND A.COUNTRY = D.COUNTRY (+) AND A.COMP_FREQUENCY = E.FREQUENCY_ID (+) AND A.EXCHNG_TO_CURRENCY = F.CURRENCY_CD (+) AND A.EXCHNG_TO_RT_TYPE = G.RT_TYPE (+) AND A.ROWSECCLASS = H.ROWSECCLASS (+)

Installation Tables > Finance

The following SQL returns the Installation Table for PeopleSoft FS 8.9. Returned column names beginning with “PSC” are descriptions for adjacent fields found on PS_INSTALLATION.

SELECT A.COUNTRY "Country",
 B.DESCR "PSC Country Descr",
 A.EXCHNG_TO_CURRENCY "To Currency",
 C.DESCR "PSC Currency Descr",
 A.EXCHNG_TO_RT_TYPE "Currency Rate Type",
 D.DESCR "PSC Currency Rate Type Descr",
 A.INSTALLATION_CD "Installation Code",
 A.ADDRESS_VENDOR "Address Vendor",
 A.TAX_VENDOR "Tax Vendor",
 A.JETFORMS "JetForms Installed",
 A.DC_INSTALLED "Document Management",
 A.MULTIBOOK_FLAG "Multibook entries in Subsystem",
 A.ALTACCT_OPTN "Enable Alternate Account",
 A.COMMITMENT_CNTL_AP "Commitment Control on AP",
 A.COMMITMENT_CNTL_AR "Commitment Control on AR",
 A.COMMITMENT_CNTL_BI "Commitment Control on BI",
 A.COMMITMENT_CNTL_EX "Commitment Control on EX",
 A.COMMITMENT_CNTL_GL "Commitment Control on GL",
 A.COMMITMENT_CNTL_GM "Commitment Control on GM",
 A.COMMITMENT_CNTL_IN "Commitment Control on IN",
 A.COMMITMENT_CNTL_PC "Commitment Control on PC",
 A.COMMITMENT_CNTL_PO "Commitment Control on PO",
 A.COMMITMENT_PROCARD "Procurement Card",
 A.BUDG_DT_DFLT_OPT "Default Budget Date",
 A.RVSL_DT_OPTN "Reversal Date Option",
 A.PROC_PART_GL "Process Partition for GL",
 A.SYNCID "Synchronization ID",
 A.SYNCDTTM "Sync Date Time"
 FROM PS_INSTALLATION A,
 PS_COUNTRY_TBL B,
 (SELECT C.CURRENCY_CD,
 C.DESCR
 FROM PS_CURRENCY_CD_TBL C
 WHERE C.EFFDT = (SELECT MAX(C1.EFFDT)
 FROM PS_CURRENCY_CD_TBL C1
 WHERE C.CURRENCY_CD = C1.CURRENCY_CD
 AND C1.EFFDT <= SYSDATE)) C,
 (SELECT D.CUR_RT_TYPE,
 D.DESCR
 FROM PS_CUR_RT_TYPE_TBL D
 WHERE D.EFFDT = (SELECT MAX(D1.EFFDT)
 FROM PS_CUR_RT_TYPE_TBL D1
 WHERE D.CUR_RT_TYPE = D1.CUR_RT_TYPE
 AND D1.EFFDT <= SYSDATE)) D WHERE A.COUNTRY = B.COUNTRY (+) AND A.EXCHNG_TO_CURRENCY = C.CURRENCY_CD (+) AND A.EXCHNG_TO_RT_TYPE = D.CUR_RT_TYPE (+)

PeopleSoft Project Dump

The following SQL returns the rows from PSPROJECTITEM for the Project Name provided. Integer fields such as OBJECTTYPE, a.OBJECTID1, etc. will be translated and outputted within separate columns. Resulting output should resemble the Upgrade tab for that Project within Application Designer.

Input Parameters:

  • PROJECTNAME: Name of Project to be dumped
SELECT A.PROJECTNAME,
 A.OBJECTTYPE,
 CASE
 WHEN A.OBJECTTYPE = 0 THEN 'Record'
 WHEN A.OBJECTTYPE = 1 THEN 'Index'
 WHEN A.OBJECTTYPE = 2 THEN 'Field'
 WHEN A.OBJECTTYPE = 3 THEN 'Format Definition'
 WHEN A.OBJECTTYPE = 4 THEN 'Translate'
 WHEN A.OBJECTTYPE = 5 THEN 'Page'
 WHEN A.OBJECTTYPE = 6 THEN 'Menu'
 WHEN A.OBJECTTYPE = 7 THEN 'Component'
 WHEN A.OBJECTTYPE = 8 THEN 'Record PeopleCode'
 WHEN A.OBJECTTYPE = 9 THEN 'Menu PeopleCode'
 WHEN A.OBJECTTYPE = 10 THEN 'Query'
 WHEN A.OBJECTTYPE = 11 THEN 'Tree Structure'
 WHEN A.OBJECTTYPE = 12 THEN 'Tree'
 WHEN A.OBJECTTYPE = 13 THEN 'Access Group'
 WHEN A.OBJECTTYPE = 14 THEN 'Color'
 WHEN A.OBJECTTYPE = 15 THEN 'Style'
 WHEN A.OBJECTTYPE = 16 THEN 'unused (was Business Process Map)'
 WHEN A.OBJECTTYPE = 17 THEN 'Business Process'
 WHEN A.OBJECTTYPE = 18 THEN 'Activity'
 WHEN A.OBJECTTYPE = 19 THEN 'Role'
 WHEN A.OBJECTTYPE = 20 THEN 'Process Definition'
 WHEN A.OBJECTTYPE = 21 THEN 'Process Server'
 WHEN A.OBJECTTYPE = 22 THEN 'Process Type'
 WHEN A.OBJECTTYPE = 23 THEN 'Process Job'
 WHEN A.OBJECTTYPE = 24 THEN 'Process Recurrence'
 WHEN A.OBJECTTYPE = 25 THEN 'Message'
 WHEN A.OBJECTTYPE = 26 THEN 'Dimension'
 WHEN A.OBJECTTYPE = 27 THEN 'Analysis Model'
 WHEN A.OBJECTTYPE = 28 THEN 'Cube Template'
 WHEN A.OBJECTTYPE = 29 THEN 'Business Interlink'
 WHEN A.OBJECTTYPE = 30 THEN 'SQL Object'
 WHEN A.OBJECTTYPE = 31 THEN 'File layout Definition'
 WHEN A.OBJECTTYPE = 32 THEN 'Component Interface'
 WHEN A.OBJECTTYPE = 33 THEN 'App Engine Program'
 WHEN A.OBJECTTYPE = 34 THEN 'App Engine Section'
 WHEN A.OBJECTTYPE = 35 THEN 'Message Node'
 WHEN A.OBJECTTYPE = 36 THEN 'Message Channel'
 WHEN A.OBJECTTYPE = 37 THEN 'Message Definition'
 WHEN A.OBJECTTYPE = 38 THEN 'Approval Rule Set'
 WHEN A.OBJECTTYPE = 39 THEN 'Message PeopleCode'
 WHEN A.OBJECTTYPE = 40 THEN 'Subscription PeopleCode'
 WHEN A.OBJECTTYPE = 41 THEN 'unused in 8.x'
 WHEN A.OBJECTTYPE = 42 THEN 'Component Interface PeopleCode'
 WHEN A.OBJECTTYPE = 43 THEN 'Application Engine PeopleCode'
 WHEN A.OBJECTTYPE = 44 THEN 'Component PeopleCode'
 WHEN A.OBJECTTYPE = 45 THEN 'Page Field PeopleCode'
 WHEN A.OBJECTTYPE = 46 THEN 'Component PeopleCode'
 WHEN A.OBJECTTYPE = 47 THEN 'Component Record PeopleCode'
 WHEN A.OBJECTTYPE = 48 THEN 'Component Record Field PeopleCode'
 WHEN A.OBJECTTYPE = 49 THEN 'Image'
 WHEN A.OBJECTTYPE = 50 THEN 'Style Sheet'
 WHEN A.OBJECTTYPE = 51 THEN 'HTML Catalog'
 WHEN A.OBJECTTYPE = 52 THEN 'Unused'
 WHEN A.OBJECTTYPE = 53 THEN 'Class'
 WHEN A.OBJECTTYPE = 54 THEN 'Portal Registry Definition'
 WHEN A.OBJECTTYPE = 55 THEN 'Portal Registry Structure'
 WHEN A.OBJECTTYPE = 56 THEN 'URL Definitions'
 WHEN A.OBJECTTYPE = 57 THEN 'Application Package'
 WHEN A.OBJECTTYPE = 58 THEN 'Application Package PeopleCode'
 WHEN A.OBJECTTYPE = 59 THEN 'Portal Registry User Homepage'
 WHEN A.OBJECTTYPE = 60 THEN 'Problem (Analytic) Type Definition'
 WHEN A.OBJECTTYPE = 61 THEN 'Data Archival'
 WHEN A.OBJECTTYPE = 62 THEN 'XSLT'
 WHEN A.OBJECTTYPE = 63 THEN 'Portal Registry User Favorite'
 WHEN A.OBJECTTYPE = 64 THEN 'Mobile Page'
 WHEN A.OBJECTTYPE = 65 THEN 'Relationship'
 WHEN A.OBJECTTYPE = 66 THEN 'Component Interface Property PeopleCode'
 WHEN A.OBJECTTYPE = 67 THEN 'Optimization Model'
 WHEN A.OBJECTTYPE = 68 THEN 'File Reference'
 WHEN A.OBJECTTYPE = 69 THEN 'File Reference Type Code'
 WHEN A.OBJECTTYPE = 70 THEN 'Archive Objects'
 WHEN A.OBJECTTYPE = 71 THEN 'Archive Templates'
 WHEN A.OBJECTTYPE = 72 THEN 'Diagnostic Plug-In'
 WHEN A.OBJECTTYPE = 73 THEN 'Analytic Model'
 ELSE 'Unknown'
 END "Object Type",
 A.OBJECTID1,
 CASE
 WHEN A.OBJECTID1 = 0 THEN NULL
 WHEN A.OBJECTID1 = 1 THEN 'Record'
 WHEN A.OBJECTID1 = 2 THEN 'Field'
 WHEN A.OBJECTID1 = 3 THEN 'Menu'
 WHEN A.OBJECTID1 = 4 THEN 'Menu Bar'
 WHEN A.OBJECTID1 = 5 THEN 'Menu Item'
 WHEN A.OBJECTID1 = 6 THEN 'DB Field'
 WHEN A.OBJECTID1 = 7 THEN 'Business Process'
 WHEN A.OBJECTID1 = 8 THEN 'Business Process Map'
 WHEN A.OBJECTID1 = 9 THEN 'Panel'
 WHEN A.OBJECTID1 = 10 THEN 'Panel Group'
 WHEN A.OBJECTID1 = 11 THEN 'Project'
 WHEN A.OBJECTID1 = 12 THEN 'Method'
 WHEN A.OBJECTID1 = 13 THEN 'Function'
 WHEN A.OBJECTID1 = 14 THEN 'Source Token'
 WHEN A.OBJECTID1 = 15 THEN 'Source Line'
 WHEN A.OBJECTID1 = 16 THEN 'Language Code'
 WHEN A.OBJECTID1 = 17 THEN 'ACCESS Group'
 WHEN A.OBJECTID1 = 18 THEN 'Activity Name'
 WHEN A.OBJECTID1 = 19 THEN 'Color Name'
 WHEN A.OBJECTID1 = 20 THEN 'DB Type'
 WHEN A.OBJECTID1 = 21 THEN 'Effdt'
 WHEN A.OBJECTID1 = 22 THEN 'Field Value'
 WHEN A.OBJECTID1 = 23 THEN 'Format Family'
 WHEN A.OBJECTID1 = 24 THEN 'Index ID'
 WHEN A.OBJECTID1 = 25 THEN 'Opr ID'
 WHEN A.OBJECTID1 = 26 THEN 'Opsys'
 WHEN A.OBJECTID1 = 27 THEN 'Process Job Name'
 WHEN A.OBJECTID1 = 28 THEN 'Process Name'
 WHEN A.OBJECTID1 = 29 THEN 'Process Type'
 WHEN A.OBJECTID1 = 30 THEN 'Query Name'
 WHEN A.OBJECTID1 = 31 THEN 'Recurrence Name'
 WHEN A.OBJECTID1 = 32 THEN 'Role Name'
 WHEN A.OBJECTID1 = 33 THEN 'Server Name'
 WHEN A.OBJECTID1 = 34 THEN 'Set ID'
 WHEN A.OBJECTID1 = 35 THEN 'Style Name'
 WHEN A.OBJECTID1 = 36 THEN 'Tree Name'
 WHEN A.OBJECTID1 = 37 THEN 'Tree Structure ID'
 WHEN A.OBJECTID1 = 38 THEN 'Long Name'
 WHEN A.OBJECTID1 = 39 THEN 'Market'
 WHEN A.OBJECTID1 = 40 THEN 'Panel Reference'
 WHEN A.OBJECTID1 = 41 THEN 'Panel Group Reference'
 WHEN A.OBJECTID1 = 42 THEN 'System Color'
 WHEN A.OBJECTID1 = 43 THEN 'Style'
 WHEN A.OBJECTID1 = 44 THEN 'Field Format'
 WHEN A.OBJECTID1 = 45 THEN 'Tool Bar'
 WHEN A.OBJECTID1 = 46 THEN 'File Reference'
 WHEN A.OBJECTID1 = 47 THEN 'Tablespace'
 WHEN A.OBJECTID1 = 48 THEN 'Message Set Number'
 WHEN A.OBJECTID1 = 49 THEN 'Message Number'
 WHEN A.OBJECTID1 = 50 THEN 'Message Description'
 WHEN A.OBJECTID1 = 51 THEN 'Dimension ID'
 WHEN A.OBJECTID1 = 52 THEN 'Dimension Type'
 WHEN A.OBJECTID1 = 53 THEN 'Dimension Description'
 WHEN A.OBJECTID1 = 54 THEN 'Analysis Model ID'
 WHEN A.OBJECTID1 = 55 THEN 'Analysis Model Description'
 WHEN A.OBJECTID1 = 56 THEN 'Analysis DB ID'
 WHEN A.OBJECTID1 = 57 THEN 'Cube Template Description'
 WHEN A.OBJECTID1 = 58 THEN 'Business Process Reference'
 WHEN A.OBJECTID1 = 59 THEN 'Activity Reference'
 WHEN A.OBJECTID1 = 60 THEN 'Message'
 WHEN A.OBJECTID1 = 61 THEN 'Channel'
 WHEN A.OBJECTID1 = 62 THEN 'Message Node'
 WHEN A.OBJECTID1 = 63 THEN 'Message Filter'
 WHEN A.OBJECTID1 = 64 THEN 'Interface Object'
 WHEN A.OBJECTID1 = 65 THEN 'SQL'
 WHEN A.OBJECTID1 = 66 THEN 'AE Application ID'
 WHEN A.OBJECTID1 = 67 THEN 'Panel Field'
 WHEN A.OBJECTID1 = 68 THEN 'Set Control Value'
 WHEN A.OBJECTID1 = 69 THEN 'Old Business Process'
 WHEN A.OBJECTID1 = 70 THEN 'Old Activity'
 WHEN A.OBJECTID1 = 71 THEN 'File Layout'
 WHEN A.OBJECTID1 = 72 THEN 'Print'
 WHEN A.OBJECTID1 = 73 THEN 'Print File Reference'
 WHEN A.OBJECTID1 = 74 THEN 'Business Component'
 WHEN A.OBJECTID1 = 75 THEN 'BC Interface'
 WHEN A.OBJECTID1 = 76 THEN 'BC Property'
 WHEN A.OBJECTID1 = 77 THEN 'AE Section Node'
 WHEN A.OBJECTID1 = 78 THEN 'AE Step Node'
 WHEN A.OBJECTID1 = 79 THEN 'AE Action Node'
 WHEN A.OBJECTID1 = 80 THEN 'Rule'
 WHEN A.OBJECTID1 = 81 THEN 'SQL Type'
 WHEN A.OBJECTID1 = 82 THEN 'PC Debugger'
 WHEN A.OBJECTID1 = 83 THEN 'Scroll'
 WHEN A.OBJECTID1 = 84 THEN 'Executable Statement'
 WHEN A.OBJECTID1 = 85 THEN 'Approval Rule Set'
 WHEN A.OBJECTID1 = 86 THEN 'Report'
 WHEN A.OBJECTID1 = 87 THEN 'Subscription'
 WHEN A.OBJECTID1 = 88 THEN 'Lanaguage Translate'
 WHEN A.OBJECTID1 = 89 THEN 'Permission List'
 WHEN A.OBJECTID1 = 90 THEN 'HTML Catalog'
 WHEN A.OBJECTID1 = 91 THEN 'Image'
 WHEN A.OBJECTID1 = 92 THEN 'Alt Cont Num'
 WHEN A.OBJECTID1 = 93 THEN 'Dynamic Page'
 WHEN A.OBJECTID1 = 94 THEN 'Stylesheet'
 WHEN A.OBJECTID1 = 95 THEN 'Content Type'
 WHEN A.OBJECTID1 = 96 THEN 'Path Reference'
 WHEN A.OBJECTID1 = 97 THEN 'Field Type'
 WHEN A.OBJECTID1 = 98 THEN 'Portal Definition'
 WHEN A.OBJECTID1 = 99 THEN 'Portal Structure'
 WHEN A.OBJECTID1 = 100 THEN 'Portal Reference Type'
 WHEN A.OBJECTID1 = 101 THEN 'Portal Object Name'
 WHEN A.OBJECTID1 = 102 THEN 'Field Label'
 WHEN A.OBJECTID1 = 103 THEN 'URL'
 WHEN A.OBJECTID1 = 104 THEN 'Application Package'
 WHEN A.OBJECTID1 = 105 THEN 'Application Package 1'
 WHEN A.OBJECTID1 = 106 THEN 'Application Package 2'
 WHEN A.OBJECTID1 = 107 THEN 'Application Class'
 WHEN A.OBJECTID1 = 108 THEN 'Portal User Homepage'
 WHEN A.OBJECTID1 = 109 THEN 'Problem (Analytic) Type'
 WHEN A.OBJECTID1 = 110 THEN 'AE Debugger'
 WHEN A.OBJECTID1 = 111 THEN 'Mobile Page'
 WHEN A.OBJECTID1 = 112 THEN 'PS Arch ID'
 WHEN A.OBJECTID1 = 113 THEN 'XSLT'
 WHEN A.OBJECTID1 = 114 THEN 'Portal User Favorite'
 WHEN A.OBJECTID1 = 115 THEN 'Portal Label Name'
 WHEN A.OBJECTID1 = 116 THEN 'Package Root'
 WHEN A.OBJECTID1 = 117 THEN 'Package Qualify Path'
 WHEN A.OBJECTID1 = 118 THEN 'Relationship ID'
 WHEN A.OBJECTID1 = 119 THEN 'IM Info'
 WHEN A.OBJECTID1 = 120 THEN 'Opt Model'
 WHEN A.OBJECTID1 = 121 THEN 'File Reference Name'
 WHEN A.OBJECTID1 = 122 THEN 'TYPE Code'
 WHEN A.OBJECTID1 = 123 THEN 'PS Arch Object'
 WHEN A.OBJECTID1 = 124 THEN 'Package ID'
 WHEN A.OBJECTID1 = 125 THEN 'Unknown'
 WHEN A.OBJECTID1 = 126 THEN 'Analytic Model'
 ELSE 'Unknown'
 END "Object ID 1",
 A.OBJECTVALUE1,
 A.OBJECTID2,
 CASE
 WHEN A.OBJECTID2 = 0 THEN NULL
 WHEN A.OBJECTID2 = A.OBJECTID1 THEN NULL
 WHEN A.OBJECTID2 = 1 THEN 'Record'
 WHEN A.OBJECTID2 = 2 THEN 'Field'
 WHEN A.OBJECTID2 = 3 THEN 'Menu'
 WHEN A.OBJECTID2 = 4 THEN 'Menu Bar'
 WHEN A.OBJECTID2 = 5 THEN 'Menu Item'
 WHEN A.OBJECTID2 = 6 THEN 'DB Field'
 WHEN A.OBJECTID2 = 7 THEN 'Business Process'
 WHEN A.OBJECTID2 = 8 THEN 'Business Process Map'
 WHEN A.OBJECTID2 = 9 THEN 'Panel'
 WHEN A.OBJECTID2 = 10 THEN 'Panel Group'
 WHEN A.OBJECTID2 = 11 THEN 'Project'
 WHEN A.OBJECTID2 = 12 THEN 'Method'
 WHEN A.OBJECTID2 = 13 THEN 'Function'
 WHEN A.OBJECTID2 = 14 THEN 'Source Token'
 WHEN A.OBJECTID2 = 15 THEN 'Source Line'
 WHEN A.OBJECTID2 = 16 THEN 'Language Code'
 WHEN A.OBJECTID2 = 17 THEN 'ACCESS Group'
 WHEN A.OBJECTID2 = 18 THEN 'Activity Name'
 WHEN A.OBJECTID2 = 19 THEN 'Color Name'
 WHEN A.OBJECTID2 = 20 THEN 'DB Type'
 WHEN A.OBJECTID2 = 21 THEN 'Effdt'
 WHEN A.OBJECTID2 = 22 THEN 'Field Value'
 WHEN A.OBJECTID2 = 23 THEN 'Format Family'
 WHEN A.OBJECTID2 = 24 THEN 'Index ID'
 WHEN A.OBJECTID2 = 25 THEN 'Opr ID'
 WHEN A.OBJECTID2 = 26 THEN 'Opsys'
 WHEN A.OBJECTID2 = 27 THEN 'Process Job Name'
 WHEN A.OBJECTID2 = 28 THEN 'Process Name'
 WHEN A.OBJECTID2 = 29 THEN 'Process Type'
 WHEN A.OBJECTID2 = 30 THEN 'Query Name'
 WHEN A.OBJECTID2 = 31 THEN 'Recurrence Name'
 WHEN A.OBJECTID2 = 32 THEN 'Role Name'
 WHEN A.OBJECTID2 = 33 THEN 'Server Name'
 WHEN A.OBJECTID2 = 34 THEN 'Set ID'
 WHEN A.OBJECTID2 = 35 THEN 'Style Name'
 WHEN A.OBJECTID2 = 36 THEN 'Tree Name'
 WHEN A.OBJECTID2 = 37 THEN 'Tree Structure ID'
 WHEN A.OBJECTID2 = 38 THEN 'Long Name'
 WHEN A.OBJECTID2 = 39 THEN 'Market'
 WHEN A.OBJECTID2 = 40 THEN 'Panel Reference'
 WHEN A.OBJECTID2 = 41 THEN 'Panel Group Reference'
 WHEN A.OBJECTID2 = 42 THEN 'System Color'
 WHEN A.OBJECTID2 = 43 THEN 'Style'
 WHEN A.OBJECTID2 = 44 THEN 'Field Format'
 WHEN A.OBJECTID2 = 45 THEN 'Tool Bar'
 WHEN A.OBJECTID2 = 46 THEN 'File Reference'
 WHEN A.OBJECTID2 = 47 THEN 'Tablespace'
 WHEN A.OBJECTID2 = 48 THEN 'Message Set Number'
 WHEN A.OBJECTID2 = 49 THEN 'Message Number'
 WHEN A.OBJECTID2 = 50 THEN 'Message Description'
 WHEN A.OBJECTID2 = 51 THEN 'Dimension ID'
 WHEN A.OBJECTID2 = 52 THEN 'Dimension Type'
 WHEN A.OBJECTID2 = 53 THEN 'Dimension Description'
 WHEN A.OBJECTID2 = 54 THEN 'Analysis Model ID'
 WHEN A.OBJECTID2 = 55 THEN 'Analysis Model Description'
 WHEN A.OBJECTID2 = 56 THEN 'Analysis DB ID'
 WHEN A.OBJECTID2 = 57 THEN 'Cube Template Description'
 WHEN A.OBJECTID2 = 58 THEN 'Business Process Reference'
 WHEN A.OBJECTID2 = 59 THEN 'Activity Reference'
 WHEN A.OBJECTID2 = 60 THEN 'Message'
 WHEN A.OBJECTID2 = 61 THEN 'Channel'
 WHEN A.OBJECTID2 = 62 THEN 'Message Node'
 WHEN A.OBJECTID2 = 63 THEN 'Message Filter'
 WHEN A.OBJECTID2 = 64 THEN 'Interface Object'
 WHEN A.OBJECTID2 = 65 THEN 'SQL'
 WHEN A.OBJECTID2 = 66 THEN 'AE Application ID'
 WHEN A.OBJECTID2 = 67 THEN 'Panel Field'
 WHEN A.OBJECTID2 = 68 THEN 'Set Control Value'
 WHEN A.OBJECTID2 = 69 THEN 'Old Business Process'
 WHEN A.OBJECTID2 = 70 THEN 'Old Activity'
 WHEN A.OBJECTID2 = 71 THEN 'File Layout'
 WHEN A.OBJECTID2 = 72 THEN 'Print'
 WHEN A.OBJECTID2 = 73 THEN 'Print File Reference'
 WHEN A.OBJECTID2 = 74 THEN 'Business Component'
 WHEN A.OBJECTID2 = 75 THEN 'BC Interface'
 WHEN A.OBJECTID2 = 76 THEN 'BC Property'
 WHEN A.OBJECTID2 = 77 THEN 'AE Section Node'
 WHEN A.OBJECTID2 = 78 THEN 'AE Step Node'
 WHEN A.OBJECTID2 = 79 THEN 'AE Action Node'
 WHEN A.OBJECTID2 = 80 THEN 'Rule'
 WHEN A.OBJECTID2 = 81 THEN 'SQL Type'
 WHEN A.OBJECTID2 = 82 THEN 'PC Debugger'
 WHEN A.OBJECTID2 = 83 THEN 'Scroll'
 WHEN A.OBJECTID2 = 84 THEN 'Executable Statement'
 WHEN A.OBJECTID2 = 85 THEN 'Approval Rule Set'
 WHEN A.OBJECTID2 = 86 THEN 'Report'
 WHEN A.OBJECTID2 = 87 THEN 'Subscription'
 WHEN A.OBJECTID2 = 88 THEN 'Lanaguage Translate'
 WHEN A.OBJECTID2 = 89 THEN 'Permission List'
 WHEN A.OBJECTID2 = 90 THEN 'HTML Catalog'
 WHEN A.OBJECTID2 = 91 THEN 'Image'
 WHEN A.OBJECTID2 = 92 THEN 'Alt Cont Num'
 WHEN A.OBJECTID2 = 93 THEN 'Dynamic Page'
 WHEN A.OBJECTID2 = 94 THEN 'Stylesheet'
 WHEN A.OBJECTID2 = 95 THEN 'Content Type'
 WHEN A.OBJECTID2 = 96 THEN 'Path Reference'
 WHEN A.OBJECTID2 = 97 THEN 'Field Type'
 WHEN A.OBJECTID2 = 98 THEN 'Portal Definition'
 WHEN A.OBJECTID2 = 99 THEN 'Portal Structure'
 WHEN A.OBJECTID2 = 100 THEN 'Portal Reference Type'
 WHEN A.OBJECTID2 = 101 THEN 'Portal Object Name'
 WHEN A.OBJECTID2 = 102 THEN 'Field Label'
 WHEN A.OBJECTID2 = 103 THEN 'URL'
 WHEN A.OBJECTID2 = 104 THEN 'Application Package'
 WHEN A.OBJECTID2 = 105 THEN 'Application Package 1'
 WHEN A.OBJECTID2 = 106 THEN 'Application Package 2'
 WHEN A.OBJECTID2 = 107 THEN 'Application Class'
 WHEN A.OBJECTID2 = 108 THEN 'Portal User Homepage'
 WHEN A.OBJECTID2 = 109 THEN 'Problem (Analytic) Type'
 WHEN A.OBJECTID2 = 110 THEN 'AE Debugger'
 WHEN A.OBJECTID2 = 111 THEN 'Mobile Page'
 WHEN A.OBJECTID2 = 112 THEN 'PS Arch ID'
 WHEN A.OBJECTID2 = 113 THEN 'XSLT'
 WHEN A.OBJECTID2 = 114 THEN 'Portal User Favorite'
 WHEN A.OBJECTID2 = 115 THEN 'Portal Label Name'
 WHEN A.OBJECTID2 = 116 THEN 'Package Root'
 WHEN A.OBJECTID2 = 117 THEN 'Package Qualify Path'
 WHEN A.OBJECTID2 = 118 THEN 'Relationship ID'
 WHEN A.OBJECTID2 = 119 THEN 'IM Info'
 WHEN A.OBJECTID2 = 120 THEN 'Opt Model'
 WHEN A.OBJECTID2 = 121 THEN 'File Reference Name'
 WHEN A.OBJECTID2 = 122 THEN 'TYPE Code'
 WHEN A.OBJECTID2 = 123 THEN 'PS Arch Object'
 WHEN A.OBJECTID2 = 124 THEN 'Package ID'
 WHEN A.OBJECTID2 = 125 THEN 'Unknown'
 WHEN A.OBJECTID2 = 126 THEN 'Analytic Model'
 ELSE 'Unknown'
 END "Object ID 2",
 A.OBJECTVALUE2,
 CASE
 -- OBJECTTYPE of 55 = Portal Registry Structure
 WHEN A.OBJECTTYPE = 55
 AND A.OBJECTVALUE2 = 'C' THEN 'Content Reference'
 WHEN A.OBJECTTYPE = 55
 AND A.OBJECTVALUE2 = 'F' THEN 'Content Reference'
 -- a.OBJECTID of 81 = SQL Type
 WHEN A.OBJECTID2 = 81
 AND OBJECTVALUE2 = 0 THEN 'Normal SQL'
 WHEN A.OBJECTID2 = 81
 AND OBJECTVALUE2 = 1 THEN 'AE SQL'
 WHEN A.OBJECTID2 = 81
 AND OBJECTVALUE2 = 2 THEN 'Record View SQL'
 WHEN A.OBJECTID2 = 81
 AND OBJECTVALUE2 = 2 THEN 'Audit SQL'
 WHEN A.OBJECTID2 = 81
 AND OBJECTVALUE2 = 6 THEN 'XSLT SQL'
 ELSE NULL
 END "Object Value 2",
 A.OBJECTID3,
 CASE
 WHEN A.OBJECTID3 = 0 THEN NULL
 WHEN A.OBJECTID3 = A.OBJECTID2 THEN NULL
 WHEN A.OBJECTID3 = 1 THEN 'Record'
 WHEN A.OBJECTID3 = 2 THEN 'Field'
 WHEN A.OBJECTID3 = 3 THEN 'Menu'
 WHEN A.OBJECTID3 = 4 THEN 'Menu Bar'
 WHEN A.OBJECTID3 = 5 THEN 'Menu Item'
 WHEN A.OBJECTID3 = 6 THEN 'DB Field'
 WHEN A.OBJECTID3 = 7 THEN 'Business Process'
 WHEN A.OBJECTID3 = 8 THEN 'Business Process Map'
 WHEN A.OBJECTID3 = 9 THEN 'Panel'
 WHEN A.OBJECTID3 = 10 THEN 'Panel Group'
 WHEN A.OBJECTID3 = 11 THEN 'Project'
 WHEN A.OBJECTID3 = 12 THEN 'Method'
 WHEN A.OBJECTID3 = 13 THEN 'Function'
 WHEN A.OBJECTID3 = 14 THEN 'Source Token'
 WHEN A.OBJECTID3 = 15 THEN 'Source Line'
 WHEN A.OBJECTID3 = 16 THEN 'Language Code'
 WHEN A.OBJECTID3 = 17 THEN 'ACCESS Group'
 WHEN A.OBJECTID3 = 18 THEN 'Activity Name'
 WHEN A.OBJECTID3 = 19 THEN 'Color Name'
 WHEN A.OBJECTID3 = 20 THEN 'DB Type'
 WHEN A.OBJECTID3 = 21 THEN 'Effdt'
 WHEN A.OBJECTID3 = 22 THEN 'Field Value'
 WHEN A.OBJECTID3 = 23 THEN 'Format Family'
 WHEN A.OBJECTID3 = 24 THEN 'Index ID'
 WHEN A.OBJECTID3 = 25 THEN 'Opr ID'
 WHEN A.OBJECTID3 = 26 THEN 'Opsys'
 WHEN A.OBJECTID3 = 27 THEN 'Process Job Name'
 WHEN A.OBJECTID3 = 28 THEN 'Process Name'
 WHEN A.OBJECTID3 = 29 THEN 'Process Type'
 WHEN A.OBJECTID3 = 30 THEN 'Query Name'
 WHEN A.OBJECTID3 = 31 THEN 'Recurrence Name'
 WHEN A.OBJECTID3 = 32 THEN 'Role Name'
 WHEN A.OBJECTID3 = 33 THEN 'Server Name'
 WHEN A.OBJECTID3 = 34 THEN 'Set ID'
 WHEN A.OBJECTID3 = 35 THEN 'Style Name'
 WHEN A.OBJECTID3 = 36 THEN 'Tree Name'
 WHEN A.OBJECTID3 = 37 THEN 'Tree Structure ID'
 WHEN A.OBJECTID3 = 38 THEN 'Long Name'
 WHEN A.OBJECTID3 = 39 THEN 'Market'
 WHEN A.OBJECTID3 = 40 THEN 'Panel Reference'
 WHEN A.OBJECTID3 = 41 THEN 'Panel Group Reference'
 WHEN A.OBJECTID3 = 42 THEN 'System Color'
 WHEN A.OBJECTID3 = 43 THEN 'Style'
 WHEN A.OBJECTID3 = 44 THEN 'Field Format'
 WHEN A.OBJECTID3 = 45 THEN 'Tool Bar'
 WHEN A.OBJECTID3 = 46 THEN 'File Reference'
 WHEN A.OBJECTID3 = 47 THEN 'Tablespace'
 WHEN A.OBJECTID3 = 48 THEN 'Message Set Number'
 WHEN A.OBJECTID3 = 49 THEN 'Message Number'
 WHEN A.OBJECTID3 = 50 THEN 'Message Description'
 WHEN A.OBJECTID3 = 51 THEN 'Dimension ID'
 WHEN A.OBJECTID3 = 52 THEN 'Dimension Type'
 WHEN A.OBJECTID3 = 53 THEN 'Dimension Description'
 WHEN A.OBJECTID3 = 54 THEN 'Analysis Model ID'
 WHEN A.OBJECTID3 = 55 THEN 'Analysis Model Description'
 WHEN A.OBJECTID3 = 56 THEN 'Analysis DB ID'
 WHEN A.OBJECTID3 = 57 THEN 'Cube Template Description'
 WHEN A.OBJECTID3 = 58 THEN 'Business Process Reference'
 WHEN A.OBJECTID3 = 59 THEN 'Activity Reference'
 WHEN A.OBJECTID3 = 60 THEN 'Message'
 WHEN A.OBJECTID3 = 61 THEN 'Channel'
 WHEN A.OBJECTID3 = 62 THEN 'Message Node'
 WHEN A.OBJECTID3 = 63 THEN 'Message Filter'
 WHEN A.OBJECTID3 = 64 THEN 'Interface Object'
 WHEN A.OBJECTID3 = 65 THEN 'SQL'
 WHEN A.OBJECTID3 = 66 THEN 'AE Application ID'
 WHEN A.OBJECTID3 = 67 THEN 'Panel Field'
 WHEN A.OBJECTID3 = 68 THEN 'Set Control Value'
 WHEN A.OBJECTID3 = 69 THEN 'Old Business Process'
 WHEN A.OBJECTID3 = 70 THEN 'Old Activity'
 WHEN A.OBJECTID3 = 71 THEN 'File Layout'
 WHEN A.OBJECTID3 = 72 THEN 'Print'
 WHEN A.OBJECTID3 = 73 THEN 'Print File Reference'
 WHEN A.OBJECTID3 = 74 THEN 'Business Component'
 WHEN A.OBJECTID3 = 75 THEN 'BC Interface'
 WHEN A.OBJECTID3 = 76 THEN 'BC Property'
 WHEN A.OBJECTID3 = 77 THEN 'AE Section Node'
 WHEN A.OBJECTID3 = 78 THEN 'AE Step Node'
 WHEN A.OBJECTID3 = 79 THEN 'AE Action Node'
 WHEN A.OBJECTID3 = 80 THEN 'Rule'
 WHEN A.OBJECTID3 = 81 THEN 'SQL Type'
 WHEN A.OBJECTID3 = 82 THEN 'PC Debugger'
 WHEN A.OBJECTID3 = 83 THEN 'Scroll'
 WHEN A.OBJECTID3 = 84 THEN 'Executable Statement'
 WHEN A.OBJECTID3 = 85 THEN 'Approval Rule Set'
 WHEN A.OBJECTID3 = 86 THEN 'Report'
 WHEN A.OBJECTID3 = 87 THEN 'Subscription'
 WHEN A.OBJECTID3 = 88 THEN 'Lanaguage Translate'
 WHEN A.OBJECTID3 = 89 THEN 'Permission List'
 WHEN A.OBJECTID3 = 90 THEN 'HTML Catalog'
 WHEN A.OBJECTID3 = 91 THEN 'Image'
 WHEN A.OBJECTID3 = 92 THEN 'Alt Cont Num'
 WHEN A.OBJECTID3 = 93 THEN 'Dynamic Page'
 WHEN A.OBJECTID3 = 94 THEN 'Stylesheet'
 WHEN A.OBJECTID3 = 95 THEN 'Content Type'
 WHEN A.OBJECTID3 = 96 THEN 'Path Reference'
 WHEN A.OBJECTID3 = 97 THEN 'Field Type'
 WHEN A.OBJECTID3 = 98 THEN 'Portal Definition'
 WHEN A.OBJECTID3 = 99 THEN 'Portal Structure'
 WHEN A.OBJECTID3 = 100 THEN 'Portal Reference Type'
 WHEN A.OBJECTID3 = 101 THEN 'Portal Object Name'
 WHEN A.OBJECTID3 = 102 THEN 'Field Label'
 WHEN A.OBJECTID3 = 103 THEN 'URL'
 WHEN A.OBJECTID3 = 104 THEN 'Application Package'
 WHEN A.OBJECTID3 = 105 THEN 'Application Package 1'
 WHEN A.OBJECTID3 = 106 THEN 'Application Package 2'
 WHEN A.OBJECTID3 = 107 THEN 'Application Class'
 WHEN A.OBJECTID3 = 108 THEN 'Portal User Homepage'
 WHEN A.OBJECTID3 = 109 THEN 'Problem (Analytic) Type'
 WHEN A.OBJECTID3 = 110 THEN 'AE Debugger'
 WHEN A.OBJECTID3 = 111 THEN 'Mobile Page'
 WHEN A.OBJECTID3 = 112 THEN 'PS Arch ID'
 WHEN A.OBJECTID3 = 113 THEN 'XSLT'
 WHEN A.OBJECTID3 = 114 THEN 'Portal User Favorite'
 WHEN A.OBJECTID3 = 115 THEN 'Portal Label Name'
 WHEN A.OBJECTID3 = 116 THEN 'Package Root'
 WHEN A.OBJECTID3 = 117 THEN 'Package Qualify Path'
 WHEN A.OBJECTID3 = 118 THEN 'Relationship ID'
 WHEN A.OBJECTID3 = 119 THEN 'IM Info'
 WHEN A.OBJECTID3 = 120 THEN 'Opt Model'
 WHEN A.OBJECTID3 = 121 THEN 'File Reference Name'
 WHEN A.OBJECTID3 = 122 THEN 'TYPE Code'
 WHEN A.OBJECTID3 = 123 THEN 'PS Arch Object'
 WHEN A.OBJECTID3 = 124 THEN 'Package ID'
 WHEN A.OBJECTID3 = 125 THEN 'Unknown'
 WHEN A.OBJECTID3 = 126 THEN 'Analytic Model'
 ELSE 'Unknown'
 END "Object ID 3",
 A.OBJECTVALUE3,
 A.OBJECTID4,
 CASE
 WHEN A.OBJECTID4 = 0 THEN NULL
 WHEN A.OBJECTID4 = A.OBJECTID3 THEN NULL
 WHEN A.OBJECTID4 = 1 THEN 'Record'
 WHEN A.OBJECTID4 = 2 THEN 'Field'
 WHEN A.OBJECTID4 = 3 THEN 'Menu'
 WHEN A.OBJECTID4 = 4 THEN 'Menu Bar'
 WHEN A.OBJECTID4 = 5 THEN 'Menu Item'
 WHEN A.OBJECTID4 = 6 THEN 'DB Field'
 WHEN A.OBJECTID4 = 7 THEN 'Business Process'
 WHEN A.OBJECTID4 = 8 THEN 'Business Process Map'
 WHEN A.OBJECTID4 = 9 THEN 'Panel'
 WHEN A.OBJECTID4 = 10 THEN 'Panel Group'
 WHEN A.OBJECTID4 = 11 THEN 'Project'
 WHEN A.OBJECTID4 = 12 THEN 'Method'
 WHEN A.OBJECTID4 = 13 THEN 'Function'
 WHEN A.OBJECTID4 = 14 THEN 'Source Token'
 WHEN A.OBJECTID4 = 15 THEN 'Source Line'
 WHEN A.OBJECTID4 = 16 THEN 'Language Code'
 WHEN A.OBJECTID4 = 17 THEN 'ACCESS Group'
 WHEN A.OBJECTID4 = 18 THEN 'Activity Name'
 WHEN A.OBJECTID4 = 19 THEN 'Color Name'
 WHEN A.OBJECTID4 = 20 THEN 'DB Type'
 WHEN A.OBJECTID4 = 21 THEN 'Effdt'
 WHEN A.OBJECTID4 = 22 THEN 'Field Value'
 WHEN A.OBJECTID4 = 23 THEN 'Format Family'
 WHEN A.OBJECTID4 = 24 THEN 'Index ID'
 WHEN A.OBJECTID4 = 25 THEN 'Opr ID'
 WHEN A.OBJECTID4 = 26 THEN 'Opsys'
 WHEN A.OBJECTID4 = 27 THEN 'Process Job Name'
 WHEN A.OBJECTID4 = 28 THEN 'Process Name'
 WHEN A.OBJECTID4 = 29 THEN 'Process Type'
 WHEN A.OBJECTID4 = 30 THEN 'Query Name'
 WHEN A.OBJECTID4 = 31 THEN 'Recurrence Name'
 WHEN A.OBJECTID4 = 32 THEN 'Role Name'
 WHEN A.OBJECTID4 = 33 THEN 'Server Name'
 WHEN A.OBJECTID4 = 34 THEN 'Set ID'
 WHEN A.OBJECTID4 = 35 THEN 'Style Name'
 WHEN A.OBJECTID4 = 36 THEN 'Tree Name'
 WHEN A.OBJECTID4 = 37 THEN 'Tree Structure ID'
 WHEN A.OBJECTID4 = 38 THEN 'Long Name'
 WHEN A.OBJECTID4 = 39 THEN 'Market'
 WHEN A.OBJECTID4 = 40 THEN 'Panel Reference'
 WHEN A.OBJECTID4 = 41 THEN 'Panel Group Reference'
 WHEN A.OBJECTID4 = 42 THEN 'System Color'
 WHEN A.OBJECTID4 = 43 THEN 'Style'
 WHEN A.OBJECTID4 = 44 THEN 'Field Format'
 WHEN A.OBJECTID4 = 45 THEN 'Tool Bar'
 WHEN A.OBJECTID4 = 46 THEN 'File Reference'
 WHEN A.OBJECTID4 = 47 THEN 'Tablespace'
 WHEN A.OBJECTID4 = 48 THEN 'Message Set Number'
 WHEN A.OBJECTID4 = 49 THEN 'Message Number'
 WHEN A.OBJECTID4 = 50 THEN 'Message Description'
 WHEN A.OBJECTID4 = 51 THEN 'Dimension ID'
 WHEN A.OBJECTID4 = 52 THEN 'Dimension Type'
 WHEN A.OBJECTID4 = 53 THEN 'Dimension Description'
 WHEN A.OBJECTID4 = 54 THEN 'Analysis Model ID'
 WHEN A.OBJECTID4 = 55 THEN 'Analysis Model Description'
 WHEN A.OBJECTID4 = 56 THEN 'Analysis DB ID'
 WHEN A.OBJECTID4 = 57 THEN 'Cube Template Description'
 WHEN A.OBJECTID4 = 58 THEN 'Business Process Reference'
 WHEN A.OBJECTID4 = 59 THEN 'Activity Reference'
 WHEN A.OBJECTID4 = 60 THEN 'Message'
 WHEN A.OBJECTID4 = 61 THEN 'Channel'
 WHEN A.OBJECTID4 = 62 THEN 'Message Node'
 WHEN A.OBJECTID4 = 63 THEN 'Message Filter'
 WHEN A.OBJECTID4 = 64 THEN 'Interface Object'
 WHEN A.OBJECTID4 = 65 THEN 'SQL'
 WHEN A.OBJECTID4 = 66 THEN 'AE Application ID'
 WHEN A.OBJECTID4 = 67 THEN 'Panel Field'
 WHEN A.OBJECTID4 = 68 THEN 'Set Control Value'
 WHEN A.OBJECTID4 = 69 THEN 'Old Business Process'
 WHEN A.OBJECTID4 = 70 THEN 'Old Activity'
 WHEN A.OBJECTID4 = 71 THEN 'File Layout'
 WHEN A.OBJECTID4 = 72 THEN 'Print'
 WHEN A.OBJECTID4 = 73 THEN 'Print File Reference'
 WHEN A.OBJECTID4 = 74 THEN 'Business Component'
 WHEN A.OBJECTID4 = 75 THEN 'BC Interface'
 WHEN A.OBJECTID4 = 76 THEN 'BC Property'
 WHEN A.OBJECTID4 = 77 THEN 'AE Section Node'
 WHEN A.OBJECTID4 = 78 THEN 'AE Step Node'
 WHEN A.OBJECTID4 = 79 THEN 'AE Action Node'
 WHEN A.OBJECTID4 = 80 THEN 'Rule'
 WHEN A.OBJECTID4 = 81 THEN 'SQL Type'
 WHEN A.OBJECTID4 = 82 THEN 'PC Debugger'
 WHEN A.OBJECTID4 = 83 THEN 'Scroll'
 WHEN A.OBJECTID4 = 84 THEN 'Executable Statement'
 WHEN A.OBJECTID4 = 85 THEN 'Approval Rule Set'
 WHEN A.OBJECTID4 = 86 THEN 'Report'
 WHEN A.OBJECTID4 = 87 THEN 'Subscription'
 WHEN A.OBJECTID4 = 88 THEN 'Lanaguage Translate'
 WHEN A.OBJECTID4 = 89 THEN 'Permission List'
 WHEN A.OBJECTID4 = 90 THEN 'HTML Catalog'
 WHEN A.OBJECTID4 = 91 THEN 'Image'
 WHEN A.OBJECTID4 = 92 THEN 'Alt Cont Num'
 WHEN A.OBJECTID4 = 93 THEN 'Dynamic Page'
 WHEN A.OBJECTID4 = 94 THEN 'Stylesheet'
 WHEN A.OBJECTID4 = 95 THEN 'Content Type'
 WHEN A.OBJECTID4 = 96 THEN 'Path Reference'
 WHEN A.OBJECTID4 = 97 THEN 'Field Type'
 WHEN A.OBJECTID4 = 98 THEN 'Portal Definition'
 WHEN A.OBJECTID4 = 99 THEN 'Portal Structure'
 WHEN A.OBJECTID4 = 100 THEN 'Portal Reference Type'
 WHEN A.OBJECTID4 = 101 THEN 'Portal Object Name'
 WHEN A.OBJECTID4 = 102 THEN 'Field Label'
 WHEN A.OBJECTID4 = 103 THEN 'URL'
 WHEN A.OBJECTID4 = 104 THEN 'Application Package'
 WHEN A.OBJECTID4 = 105 THEN 'Application Package 1'
 WHEN A.OBJECTID4 = 106 THEN 'Application Package 2'
 WHEN A.OBJECTID4 = 107 THEN 'Application Class'
 WHEN A.OBJECTID4 = 108 THEN 'Portal User Homepage'
 WHEN A.OBJECTID4 = 109 THEN 'Problem (Analytic) Type'
 WHEN A.OBJECTID4 = 110 THEN 'AE Debugger'
 WHEN A.OBJECTID4 = 111 THEN 'Mobile Page'
 WHEN A.OBJECTID4 = 112 THEN 'PS Arch ID'
 WHEN A.OBJECTID4 = 113 THEN 'XSLT'
 WHEN A.OBJECTID4 = 114 THEN 'Portal User Favorite'
 WHEN A.OBJECTID4 = 115 THEN 'Portal Label Name'
 WHEN A.OBJECTID4 = 116 THEN 'Package Root'
 WHEN A.OBJECTID4 = 117 THEN 'Package Qualify Path'
 WHEN A.OBJECTID4 = 118 THEN 'Relationship ID'
 WHEN A.OBJECTID4 = 119 THEN 'IM Info'
 WHEN A.OBJECTID4 = 120 THEN 'Opt Model'
 WHEN A.OBJECTID4 = 121 THEN 'File Reference Name'
 WHEN A.OBJECTID4 = 122 THEN 'TYPE Code'
 WHEN A.OBJECTID4 = 123 THEN 'PS Arch Object'
 WHEN A.OBJECTID4 = 124 THEN 'Package ID'
 WHEN A.OBJECTID4 = 125 THEN 'Unknown'
 WHEN A.OBJECTID4 = 126 THEN 'Analytic Model'
 ELSE 'Unknown'
 END "Object ID 4",
 A.OBJECTVALUE4,
 A.SOURCESTATUS,
 CASE
 WHEN A.SOURCESTATUS = 0 THEN 'Unknown'
 WHEN A.SOURCESTATUS = 1 THEN 'Absent'
 WHEN A.SOURCESTATUS = 2 THEN 'Changed'
 WHEN A.SOURCESTATUS = 3 THEN 'Unchanged'
 WHEN A.SOURCESTATUS = 4 THEN 'Custom Changed'
 WHEN A.SOURCESTATUS = 5 THEN 'Custom Unchanged'
 WHEN A.SOURCESTATUS = 6 THEN 'Same'
 ELSE 'Unknown'
 END "Source Status",
 A.TARGETSTATUS,
 CASE
 WHEN A.TARGETSTATUS = 0 THEN 'Unknown'
 WHEN A.TARGETSTATUS = 1 THEN 'Absent'
 WHEN A.TARGETSTATUS = 2 THEN 'Changed'
 WHEN A.TARGETSTATUS = 3 THEN 'Unchanged'
 WHEN A.TARGETSTATUS = 4 THEN 'Custom Changed'
 WHEN A.TARGETSTATUS = 5 THEN 'Custom Unchanged'
 WHEN A.TARGETSTATUS = 6 THEN 'Same'
 ELSE 'Unknown'
 END "Target Status",
 A.UPGRADEACTION,
 CASE
 WHEN A.UPGRADEACTION = 0 THEN 'Copy'
 WHEN A.UPGRADEACTION = 1 THEN 'DELETE'
 WHEN A.UPGRADEACTION = 2 THEN 'Non'
 WHEN A.UPGRADEACTION = 3 THEN 'CopyProp'
 ELSE 'Unknown'
 END "Upgrade Action",
 A.TAKEACTION,
 CASE
 WHEN A.TAKEACTION = 0 THEN 'No'
 WHEN A.TAKEACTION = 1 THEN 'Yes'
 ELSE 'Unknown'
 END "Take Action?",
 A.COPYDONE,
 CASE
 WHEN A.COPYDONE = 0 THEN 'No'
 WHEN A.COPYDONE = 1 THEN 'Yes'
 ELSE 'Unknown'
 END "Copy Done?"
 FROM PSPROJECTITEM A
 WHERE A.PROJECTNAME = :PROJECTNAME
 ORDER BY A.PROJECTNAME,
 A.OBJECTTYPE,
 A.OBJECTID1,
 A.OBJECTID2,
 A.OBJECTID3,
 A.OBJECTID4;

Application Designer Project Audit

The following SQL pulls distinct Application Designer Project Names from two PeopleSoft systems, then performs comparisons to determine which are present/missing from each. To properly run this, log into one of the databases, then ensure that the DB Link references the system against you wish to compare.

SELECT A.PROJECTNAME "Project Name",
 B.EXISTS_FLG "In Current DB?",
 C.EXISTS_FLG "In Target DB?",
 B.PROJECTDESCR "Current DB Project Description",
 C.PROJECTDESCR "Target DB Project Description"
 FROM (SELECT A.PROJECTNAME
 FROM PSPROJECTDEFN A
 UNION
 SELECT A.PROJECTNAME
 FROM PSPROJECTDEFN@R_FS A) A,
 (SELECT 'X' "EXISTS_FLG",
 B.PROJECTNAME,
 B.PROJECTDESCR
 FROM PSPROJECTDEFN B) B,
 (SELECT 'X' "EXISTS_FLG",
 C.PROJECTNAME,
 C.PROJECTDESCR
 FROM PSPROJECTDEFN@R_FS C) C
 WHERE A.PROJECTNAME = B.PROJECTNAME (+)
 AND A.PROJECTNAME = C.PROJECTNAME (+)
 ORDER BY A.PROJECTNAME;

Record- and Field-Level Audits

The following SQL returns a list of all:

  1. PeopleSoft Audit Records and the specific fields being audited, or
  2. Audited Fields, as defined in PSRECFIELD.USEEDIT properties.
SELECT D.RECNAME,
 D.RECDESCR,
 D.AUDITRECNAME,
 D.RECUSE,
 FA.FIELDNAME,
 F.FIELDNUM
 FROM PSRECDEFN D
 INNER JOIN PSRECFIELD F
 ON D.RECNAME = F.RECNAME
 INNER JOIN PSRECFIELD FA
 ON FA.RECNAME = D.AUDITRECNAME
 AND FA.FIELDNAME = F.FIELDNAME
 WHERE D.AUDITRECNAME != ' '
 ORDER BY D.RECNAME,
 F.FIELDNUM;
 SELECT F.RECNAME,
 F.FIELDNUM,
 F.FIELDNAME,
 F.USEEDIT,
 CASE
 WHEN BITAND(F.USEEDIT, 8) > 0 THEN 'Y'
 ELSE 'N'
 END AUDIT_FIELD_ADD,
 CASE
 WHEN BITAND(F.USEEDIT, 128) > 0 THEN 'Y'
 ELSE 'N'
 END AUDIT_FIELD_CHANGE,
 CASE
 WHEN BITAND(F.USEEDIT, 1024) > 0 THEN 'Y'
 ELSE 'N'
 END AUDIT_FIELD_DELETE
 FROM PSRECFIELD F
 WHERE F.FIELDNAME = (SELECT CASE
 WHEN ( BITAND(USEEDIT, 8) > 0
 OR BITAND(USEEDIT, 128) > 0
 OR BITAND(USEEDIT, 1024) > 0 ) THEN
 FIELDNAME
 ELSE ''
 END AS FIELD_AUDITED
 FROM PSRECFIELD
 WHERE RECNAME = F.RECNAME
 AND FIELDNAME = F.FIELDNAME)
 ORDER BY F.RECNAME,
 F.FIELDNUM;

Tags

Read more