Personal – Page 2 – McGarityDotMe

A Better “Write Every Day”

There’s another way that blogging makes my writing better: writing every day makes it easier to write every day. When I was a baby writer, I thought the injunction to “write every day” was purely aspirational, like “do an hour’s aerobic exercise” or “eat five helpings of vegetables.” I deeply regret the years in which I waited for inspiration to strike before writing (as I regret the years when I didn’t get adequate exercise or nutrition) because of all the practice I missed and the habits I waited too long to develop.
Cory Doctorow, The Memex Method

The last sentence struck me hard. I’ve had this blog for nearly as long as Mr. Doctorow, yet by becoming fallow it’s lost its power to drive my current & future thoughts.

The last sentence was enough to inspire me to write something, anything, maybe as a germ for future regular authorship.

Rules of the Internet

Some wisdom I’ve complied over the 20 years since I first discovered the internet:

Never read comments
Never respond to comments
A website’s usefulness must always be greater than its beating
Everything is about porn
Avoid using Facebook Connect as your means to authenticate into anything
Never “Like” your own posts
Don’t use hashtags on sites that don’t support them (like the “old” Facebook)
Don’t believe everything you read (websites often exaggerate)
1. Don’t spread unconfirmed shit
2. *Everything* is cuter/hotter on the Internet vs. real-life (don’t trust anything)
If you want me to respond: Phone > Email > Text Message > Twitter Public Reply > Facebook Wall Post > Twitter DM > Facebook Message
Never be the first to upgrade
Log into only one account at a time
Don’t write anything you aren’t prepared for anyone to read, especially the subject
If you’re going to lie, make sure your Foursquare check-ins support your story
Don’t rely on non-anticipated use cases (ex: Tweetbot 3 ridding itself of lists-as-default views)
Be brief and to the point
Don’t use tweet lengtheners
Don’t use Facebook Notes
Just don’t

Interestingly, I remember when I first discovered the web. It was Spring 1996, I lived in Bruce Hall at the University of North Texas. Across the street was Chilton Hall, an academic building with both the closest and least-frequented computer lab on-campus. One day I intended to go Gopher some information and instead discovered a nifty little application called Mosaic. Soon after, I got sucked into the world of Netscape Navigator, and the rest of my online journey was history.

PeopleSoft Query- and Row-Level Security

Related to yesterday’s PeopleSoft-related post, here’s some more research from my PeopleSoft consulting past. This time, it’s related to PeopleSoft Query Security and row-level security, both of which are squirrelly portions of the platform — but hopefully will make more sense after you’ve read this.

General

Query is a PeopleTool that helps you build SQL queries to retrieve information from your application tables.
Query takes advantage of user’s security settings, row-level security, and primary permission list.
For each Query user, you can specify the records they are allowed to access when building and running queries. We can achieve this by creating Query Access Groups in the Query Access Group Manager, and then you assign users to those groups with Query permissions.

Query Access Group Trees

Trees are a graphical way of presenting hierarchical information.
PeopleSoft Query uses query access group trees to control the access of the tables in your PeopleSoft database.
Define a hierarchy of PeopleSoft record definitions, based on logical or functional groupings, and then give users access to one or more nodes of the tree.
Users can retrieve information only from those tables whose record definitions to which they have access.
Nodes: Query access group trees contain two types of Nodes: groups and records.
- Groups are a logical representation of a set of child groups or records. It is similar to folder in Windows.
- Records represent a PeopleSoft record definition.
Structure:
- Always use the ACCESS_GROUP Tree Structure.
- Do not use SetID or UKV/BU.
- Do not have Details.
- Do not use Levels.
- No Branches.
Requirements:
- The Root Node is always a group.
- Groups must be unique in a given Tree while records definitions can be repeated.
- Groups and records could have Child Groups and Child Records.
- Each record needs a unique fully qualified path in the tree. You can’t add the same record under the same parent node (group or record).
To open an existing Query Access Group Tree, Select PeopleTools, Security, Query Access Manager.
Create custom Query Access Group suitable to your organization. Create functional group names and add records under the group.
To Add the Query Access Groups to user:
- Open the primary Permission List for the user
- Go to ‘Query’ Tab
- Click on Access Group Permissions.
- Add the tree name, select the proper Access Group, Select ‘Accessible’ button. Repeat to add more Access groups.
- Save the permission List.

Row Level Security

By default, when you give Query users access to a record definition, they have access to all the rows of data in the table built using the associated record definition.
With row-level security, users can have access to a table without having access to all rows on that table.
This type of security is typically applied to tables that hold sensitive data.
For example, you might want users to be able to review personal data for employees in their own department, but not for people in other departments. You would give everyone access to the PERSONAL_DATA table, but would enforce row-level security so that they could only see rows where the DEPTID matches their own.
PeopleSoft applications implement row-level security by using a SQL view that joins the data table with an authorization table.
When a user searches for data in the data table, the system performs a related record join between the view and the base table rather than searching the table directly.

Query Security Record Definitions

You implement row-level security by having Query search for data using a query security record definition. The query security record definition adds a security check to the search.
Query security record definitions serve the same purpose as search record definitions do for panels. Just as a panel’s search record definition determines what data the user can display in the panel, the query security record definition determines what data the user can display with Query.
To get Query to retrieve data by joining a security record definition to the base table, you specify the appropriate Query Security Record when you create the base table’s record definition.

Applying Row-Level Security

Select PeopleTools, Application Designer to open the Application Designer, and open the record on which you want to apply row-level security.
With the record definition open in the Application Designer, click the Properties button, and select the Use tab from the Record Properties dialog box.
Select the security record definition (usually a view) in the Query Security Record list box.
Once you’ve set the query security record definition, click OK to close the Record Properties dialog box, then save the record definition. If you’ve already used SQL Create to build a table from this record definition, you don’t need to rebuild it.

Note: PeopleSoft row-level security views restrict users from seeing certain rows of data. To secure data through the search record, simply put one of the three Row Level Security fields on your record as a Key, not a List Box Item. The three Row Level Security fields are OPRID (User ID), OPRCLASS (Primary Permission List), and ROWSECCLASS (Row Security Permission List). If one of these fields is on the search record as a Key, not a List Box Item, PeopleTools does the following. PeopleTools adds a WHERE clause when it performing a SELECT through the record forcing the value to be equal to the current user’s value.

Useful PeopleSoft HCM Queries

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:

PeopleSoft Audit Records and the specific fields being audited, or
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;

The Friendster Apocalypse

My wife and I met on Friendster back in the winter of 2003-2004. Then we moved onto MySpace, then Facebook, then Twitter. And there we finally settled, like homesteaders headed further and further west until they said, “There it is.”

Imagine where you met the girl of your dreams. It was likely a place in the real world, one that you might occasionally visit.

Now imagine that place disappearing, and the memory of it fading into the ether of history. That’s what happened to ours four years ago today.

Friendster has long been a shell of its former self, but in 2003 it was announced anything related to its current form was about to go bye-bye. Time for everyone to log into their accounts and preserve their memories, we were told. So I did.

I logged into my account for the first time since November 2006 (I’m sure this coincides with Facebook opening its doors to the non @*.edu blessed). Not much was there anymore.

Sure there were some grainy photos, but nothing I didn’t already have a copy of elsewhere.

There were connections with other profiles. A good majority of them were to people I don’t speak with anyone or even connect with on other services. They’re friends who are now strangers. It’s amazing how your social circles change in just a decade.

Finally, there were tons of messages, 118 in fact, sent by all sorts of random people — or “people” — desperate to connect online.

However, I stuck with the messages, digging for the most-important one of my life. And there it was, dated 1/14/2004. It was the first thing I ever said to my future wife:

Subject: Nice Backpack
Contents: I sense that it should be holding fish instead of markers. Either that, or fantastically-powerful fuel that powers what is actually your top-secret jet pack. Either way…howdy!
MMc…

And thankfully she responded this this stranger and decided to marry him & have two beautiful kids.

R.I.P. Friendster. Thanks for helping make me the happiest man on earth.