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

  1. Select PeopleTools, Application Designer to open the Application Designer, and open the record on which you want to apply row-level security.
  2. With the record definition open in the Application Designer, click the Properties button, and select the Use tab from the Record Properties dialog box.
  3. Select the security record definition (usually a view) in the Query Security Record list box.
  4. 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:

  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;

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.

Social Media Lent 2015

iPhone 5 Desktop
Before (Fat Tuesday) and After (Ash Wednesday)

It’s that time of year again, where I feel compelled to fuckitall and run away from the world.  Lent is an ideal time to turn inward, to focus on health both spiritual and physical.  And this time around, I’m in desperate need to take care of myself.

I’ve neglected Matthew for many months, as the demands of both work and family have sapped my energy reserves.  I want to reduce distraction and make the daytime hours productive and meaningful.

Therefore, I’m repeating my abstinence from four years ago and disavowing my favorite social media platforms.  I’m temporarily retiring from Twitter, Facebook, and Instagram, and using that time to focus on improvement: to read real books instead of link-bait, to work out my body instead of letting it go fallow on my comfy couch, to look at the people around me instead of those in my feed.

The one exception I’m making relates to this blog.  Recently, I joined a weekly post challenge group on Facebook, with the intent to gain some inspiration from fellow bloggers and rekindle my love for writing.  I’ll still work on weekly blog posts, and I’ll still cross-post to that Facebook group.  But otherwise, I’ll be silent.

I’m no Luddite.  I know I’ll return to social media full-time — it’s where my closest friends reside these days, all of whom I’ll miss these next several weeks.  Besides, I’m not giving up text messaging and email, so y’all know where to find me.

Peace be with you, as I hope it is always with me.

My Personal Workflow

Workflow Word Cloud

The other week, I was catching up with a friend who inquired how I get shit done without forgetting about it.

Then the beginnings of this blog post spewed from my mouth.

Then I turned around and wrote everything down. Brace yourselves: wonky shit is about to go down. This post details my regular workflow for tying together my personal & work lives across multiple devices & roles.

Introduction

Regardless of device or tool, my workflow had to start somewhere. For many years, my system has been David Allen’s “Getting Things Done” (GTD), which I follow to a “T”.

My workflow serves these personal goals:

  • To remain organized. Enough to be productive but not become a hamster beholden to the workflow system itself
  • Retain knowledge for future review. Especially, don’t expend energy re-learning repeating tasks in the future
  • Reduce unnecessary noise or friction. Flow everything through a minimal number of inboxes, or use tools to automate where inboxes cannot be eliminated
  • Perform all of the above as efficiently as possible

Notice my goals don’t include never missing a task or always keeping my head above water. Like anyone else, I occasionally find myself drowning in unfinished business, or that the errant to-do has slipped off my radar. Instead, I am realistic and aim to minimize these exceptions and have a greater overall command of what’s on my plate.

Let’s start walking through my tools and the problems they solve.

The Tools and Platforms

Remember the Milk

Remember the Milk (RtM) is a web-based task manager that has been around since 2004, longer than your mom. I’ve personally used it since 2009, although not without occasionally flirting with competitors like Todoist, Omnifocus, Trello, Wunderlist, and more.  What makes me stick with RtM are the following combined:

  • It’s everywhere. A web interface accessible from anywhere. iPhone, Android and Windows Phone apps. Integrations with Gmail, Google Calendar, and Evernote. A robust API with third-parties developing for it. There are tons of ways to use RtM.
  • It’s stable. While the service has not changed much since 2004, they haven’t needed to because they got things right the first time.
  • Its data entry is the quickest. Their Smart Add feature, where I can enter all the parameters for my task (due date, start time, estimated time, priority, location, tags, etc.) by tagging them in the same text box I use to enter the actual task description, has yet to be matched by any competitor.

RtM allows you to create Smart Lists, which are saved searches. To drive GTD using RtM, I have several different Smart Lists which allow me to identify “next actions” at both work and home, so I can keep driving on those and “someday” tasks once punted to the future but now due. My setup mostly involves this one described in their blog.

Using finely-tuned Smart Lists, I find it easy to focus on tasks and filter out noise using the web application itself. The mobile application’s interface differs enough that I use it solely for data entry. On days where I feel über-distracted, I will print out my day’s task list and work exclusively from paper. It’s got an old-school feel to it, especially the dopamine hit you get when physically scratching off a completed task. I will also use the back of the printout to capture the day’s call/meeting notes. Then at the end of the day, I’ll review my chicken-scratch and move anything worth preserving into other tools, such as notes tied to the original RtM tasks.

In Safari, I have RtM setup as the first bookmark in my Favorites, which positions it front-and-center on the browser’s toolbar. This is purposeful, as hitting ⌘-1 while Safari is in focus will automatically launch your first-positioned Favorite. So to fire RtM, it’s a simple, out-of-the-box keyboard shortcut to quickly get my task management system in focus.

While nearly every task manager product supports inbounding tasks via email, RtM has a slick workflow for their form. The subject line of your email acts the same as their Smart Add field. This means it supports all the same tagging features as entering a task online. For example, if I get a work email which I can’t immediately complete, I can forward it to RtM, tag it with a due date, time estimate, etc. Then at that point, I can file the original email itself to keep my inbox clean!

Calendar

In my opinion, any calendar is fine, as long as you follow the same tenets below. My experiences are based on digital calendars, although I know some of you Luddites will always cling to your paper calendars.

I view all meetings as interruptions of my time. To cut down on this, use a rolling block of time to keep people from last-minute scheduling. For example, at the beginning of the day I plan out what I need to get accomplished based on how my calendar looks — if I don’t have an appointment, then the time in between is designated for achieving the day’s tasks. I then immediately create an all-day appointment that makes my calendar look full. I do this to prevent people from sending me invites for 2:00pm meetings at 1:30pm — and thwarting the feeling that I must go because my calendar did say I was free. By doing this, I am ensuring I get the day’s tasks done, and these people can always meet with me the following day when I can plan ahead for the interruption. Sometimes I go extreme and extend the block to the following day, just to get some relief from the pressure of meeting. Go ahead, try it. It feels soooo good!

In the past, I used to just schedule blocks of time to ensure I got things done. For example, I’d reserve every morning on my calendar, which forced all meetings to the afternoon. This worked well for me, since mornings are when I’m most-focused while afternoons are when I want to sit and not think too hard.

Above all, schedule daily blocks of time to triage your inboxes. I always take the first and last 15 minutes of each day to ensure my task list gets reviewed and the day’s activities get keyed into status reports & time-keeping systems. This also prevents people from scheduling 4:00pm meetings on Fridays (there’s a special place in hell for those individuals).

Evernote

Evernote is like some of my ex-girlfriends: at the time we were together, it was lots of fun. Then I look back and wonder, “What was I thinking?!”

At my previous employer, Evernote was my everything. This was because they did not have a robust internal CMS. I spent years fighting bureaucracy to establish a wiki, which always failed because old-school business people were like “What’s a wiki?” and because of the executive’s allegiance to Sharepoint (partially due to the company’s Microsoft alliance). It was natural to turn my attention inward and retain knowledge using Evernote.

Since I’ve left that company for greener (or redder) pastures, I rarely touch Evernote. This is because my company is a Google Drive/Confluence shop. Within both, I get generous amounts of space and creative freedom, and what I document is instantly available to others & searchable. Couple this with Evernote’s maddening design practices, where Evernote apps on different platforms support different features and use different UIs, using Evernote is hardly a seamless experience.

Nowadays, Evernote serves the following purposes for me:

  • Managing complex, long tasks. Since RtM doesn’t (yet) support parent/child tasks, it’s not suited to tracking projects. Therefore, that type of effort lives within Evernote notes, where I use a soup of bulleted lists and checkboxes to manage tasks. Examples: annual tax return, various home renovation projects, call logs.
  • Creating a reference library. I long ago began a healthy habit of storing PDFs of product manuals, warranties, recipes, e-books, etc. within Evernote and ridding myself of the paper versions. This is especially useful for things I look at rarely, such as the home alarm instructions when it’s freaking out at 3:00am on some random weekend night.
  • Backup redundancy. I’ll pump my various social media posts (Twitter, Foursquare/Swarm, Instagram) to single or multiple Evernote notes, as a secondary backup. Before Twitter starting providing you copies of your Twitter archive for download at anytime, this was one of the few means for hosting a searchable personal archive. See below for how I actually drive these archives.
  • Rough drafts of new blog posts. As the WordPress “edit post” screen is still a brutal, distracting mess (even with 4.0’s “distraction-free writing” functionality), I still utilize.

Going back to RtM, one nifty feature it supports is Evernote integration. Once setup, any Evernote note (should I ever have to say “Evernote note”?) with a reminder gets a corresponding RtM task that contains a link to the web version of Evernote. This is helpful because RtM works better to “tickle” me than Evernote ever will (Evernote notifications are not S.M.A.R.T. and the Evernote application itself is always a bloated mess no matter the platform). The reminder appears in RtM along with my other reminders. I can also click the Evernote icon displayed on the RtM task, which then displays the note within Evernote’s web version of Evernote then I can make the necessary updates in Evernote. This works from all RtM apps, both iOS and web client.

Dropbox

Of course I use Dropbox. And if you don’t, how the fuck do you get anything done in this work?

I use Dropbox for two main purposes:

  • Backup redundancy. sound familiar? Just like Evernote, but for all my social media assets, including my full Twitter archive. One can never be too paranoid. Can they? Can they?!
  • Connective tissue between devices. Because I use my personal MacBook Pro at work, but am still saddled with a Windows PC for certain programs, I have several folders I use to transfer common files between machines. This allows me to maximize the strengths of each platform (start an email on my MacBook, create a screenshot on my Windows PC, drop the screenshot into Dropbox, jump back to the MacBook to include the screenshot in my email, rinse, repeat). I also leverage this sync for personal means, such as my 1Password archive.

I also use Dropbox for some minor yet important purposes:

  • A “someday” folder for files. I sometimes have a pile of digital assets I can’t even get around to processing anytime soon. I can dump them into a Dropbox folder and forget about them until I have time or need to recover disk space.
  • Sharing family data. The program I use to maintain our family budget leverages Dropbox. I will reconcile our checking accounts on my machine, then my wife (running Dropbox on her machine) can use the same budgeting program to have instant access to our finances.

One nifty Dropbox feature that doesn’t get much pub is their Screenshots feature. You can define Dropbox to capture screenshots, which overrides any native functionality on your platform. I like this on a Mac, because it keeps screenshots from cluttering up my Desktop (the default location).

Like Evernote, I have enough Dropbox storage that I’m not yet compelled to overly-organize or cull anything. I let it pile up until I need room. If you’re a free Dropbox user, I highly recommend doing everything you can to maximize your storage allotment.

Gmail

Email sucks. Even Gmail sucks. Don’t use it to manage anything!  Forget organizing folders and labels, or task-management. I’m willing to bet my and your first-borns that you don’t search email enough to justify all the nested folders you’ve created in the past.

Instead, focus on reducing volume: archive everything you receive (including the junk) into one folder, then use Gmail search to find what you need. You have way too much Gmail space to be concerned with deleting anything. Gmail’s organization of conversations into single-line threads helps to eliminate noise in your search (Outlook could really use this, although it’s getting there). In the past, I used to label everything “Alerts”, “Bills”, “Comments”, etc. until it just became a burden and the email log-jammed.

I do highly recommend filters, as they are so choice. I use exactly one filter: it marks everything as read the moment it is received. It works on everything with one exception: emails from my wife are never marked read. This way, the unread count badges on my iPhone only appear when her emails come across, as I consider them priorities.

Some grandfatherly advice:

    • Use GMail Labs to your advantage. My favorites include “Quick Links“, which saves your searches as shortcuts in the sidebar and “Undo Send“. My all-time bae “Send & Archive”, which immediately archives email threads after you sent a reply, graduated from Gmail Labs to be a production feature!
    • Use actionable subjects. If someone at work sends you an email subjected “Question”, change the subject of your reply to the actual topic being discussed. This will help you down the line, in the event you are searching for specific text.
    • Don’t be afraid to talk to yourself. Reply to just yourself and record notes — they’ll be maintained along with the conversation thread, and you’ll always know what the last place you were at.
    • Reduce noise. Unsubscribe from newsletters, as there are more-efficient means to gain the information they include (such as Twitter). Create filters to automatically archive racists email forwards sent by your family.
    • Turn off chat. Does anyone use GChat anymore, anyway?
    • Only check email 2 or 3 times a day. Most of the time, I check it when I get to work, after lunch, and right before I leave. I then close my client and concentrate on actual work. This removes the pressure to respond because of a false sense of immediacy — if anything is truly important, people will find alternative ways to contact you (telephone, IM, etc.).

Mailbox

Gmail’s functionality is great, but it is still limited. Luckily, the Mailbox app has helped bridge some of these gaps.

GTD mandates that you handle tasks immediately if they can be done in a couple minutes, or you push them out to clear your working area. Mailbox’s biggest strength are its tools for achieving Inbox Zero. Using swipe gestures, you can quickly unjam your inbox via archives and snoozes. And the snoozes are fairly intelligent. You can boot items to later in the day, the evening, the next morning, the weekend, next week, a month out, or “someday” (the burn-it-with-fire option). Since Mailbox entered my life, I’ve never suffered from “inbox paralysis”, where your inbox is so full that you’d rather go to your happy place vs. try to tackle its mountain of work.

Use these snoozes to your advantage. For example, they’re great for tracking packages. If you order something that won’t arrive for a week, snooze the order confirmation email for eight days out. That way, you don’t forget that you ordered something — the day after its scheduled arrival, you’ll see the email popup in Mailbox and you can swipe to complete it.

The same principle applies for email conversations. Anytime I have a task that’s moved into email, I usually keep tracking it there. For example in RtM, I might have a task to discuss something with someone; I initiate the conversation in email, close out the RtM task, then use snoozes after my last reply to ensure that the conversation does not get dropped.

In other words, trust Mailbox tell you when something needs to be done.

Mailbox is currently beta-testing a desktop version of their client. I’ve found it’s not nearly as useful, not because it’s missing any significant feature, but because it’s far easier to process an inbox using your iPhone + thumb vs. MacBook + mouse/trackpad.

Messages

By Messages, I am referring to the like-named applications on both iOS and OS X.

Until recent years, Messages was more often a black hole where conversations went to die. Unlike Gmail, where I have greater incentive to live within the tool and constantly trim its discussion pools, someone would send me a message, I’d read it & be unable to respond at that point, then I’d forget I ever got it.

It used to be that messages only appeared on your iOS devices. Then Mac OS X Mountain Lion offered unified messaging for Apple’s iMessage protocol. If you were on an iOS/OS X client and sent a message to another user using the same platform(s), the message would appear within all their clients. Once I started receiving messages on my computer, I started responding much more often and verbosely.

Unified messaging was slick, but still not fool-proof: about 5% of my friends do not use iOS, which means they still send old-skool text messages. For those people, they were falling into the same black hole that pre-Mountain Lion messages did. Then Apple got their shit together and shipped Mac OS X Yosemite, with its support for sending text messages from your Mac’s Messages app. Finally, unified messaging across all my devices.

My Messages advice is simple:

  • Turn on unified messaging. Then treat OS X Messages like an email inbox: keep conversations open and continuously respond to them; don’t close them until driven to completion.
  • Turn on iCloud message archiving. This gives me the comfort of closing conversations after sending replies. Thanks to the history, whenever the other parties reply, the entire conversation thread will reappear & I can remember where we left off.
  • Make it both unified and universal. OS X Messages supports adding Jabber accounts, so you can use it for Facebook, WebEx Connect, and more. At least for Facebook, it allows me to overcome that platform’s black hole issues. Messages may support more than Jabber, but I haven’t tried.

Feedly

Feedly is a online RSS aggregator, one of the many which stepped into the void left by the shutdown of Google Reader (RIP). I’m not wedded to Feedly over other providers, but it has consistently met my needs. I barely spend any time on Feedly’s website itself. Instead, its engine powers Newsify, the iPhone app I use to review my unread items.

While some  subjects require real-time access (weather and local news being the primary ones) are found in my Twitter stream (shameless plug inserted here), all other news I consume comes through Feedly. My top read feeds include:

In addition, I will also subscribe to development blogs for my favorite products, such as Day One, Instapaper, etc. This greatly reduces the chance of missing feature announcements, since I subscribe to just enough feeds to keep me busy but not so many that I can’t keep up with the firehose.

At one time, I used to also subscribe to personal blogs. However, I’ve stopped doing that in Feedly. This is because Feedly is great at consuming content, but not interacting with it. In addition, I’m often reading Feedly on my iPhone, so commenting on posts is painful to do on such a platform. I’ve found an alternate method for keeping up with these (see below).

IFTTT

If This Then That (IFTTT) is a powerful connective tissue that drives automation in many flexible ways. It allows me to take some of my recurring tasks and shift them into code, so I can forget about them. These tasks, called Recipes, can then be shared with other users. Here are my top Recipes; let me know if you want copies:

  • Starring a Feedly article sends it to Instapaper. Long reads especially, since they’re easier to consume there.
  • New personal blog posts are also sent to Instapaper. Doing so allows me to sit & concentrate on them there.
  • New tweets are sent to Evernote. These are compiled in a single note as redundant Twitter archive.
  • New Foursquare/Swarm checkins are sent to Evernote. These are also a single note, and it’s a great way to maintain a “passive diary”. This provides a great supporting mechanism for my actual journaling — if writing an entry about something days/weeks back, this Evernote note lets me determine where I was that day.
  • New Instagram posts are sent to Evernote. This is a killer way to archive my Instagram feed (another shameless plug), because you can include both the image and associated text/timestamps.
  • Favorited tweets are also sent to Evernote. Why not?
  • Tweets by my wife create an iOS notification. Because I’m interested in what she has to say. And so should you be. The old Tweetdeck for iOS (RIP) used to be great for this kind of stuff, but no Twitter client today allows you to follow a single individual’s stream — this is the best substitute I’ve found so far.

Nota bene regarding Evernote as a social media archive: like all apps, you have to authorize Evernote to play nice with them. However, Evernote authorizations expire once per year, and you aren’t told in advance (or ever) when they’ll happen. So don’t fully trust Evernote as your archive, as you might miss some posts before you reauthorize Evernote.

Buffer

Like IFTTT, Buffer is another kind of connective tissue. It is a form of social media management you can use to schedule a queue of posts. I currently use it to spread certain Facebook and Twitter posts over the course of the day, so I don’t have large gaps of time between postings.

I use Buffer for many reasons:

  • Sharing interesting blog posts. During the same coffee talk that inspired this blog post, my friend asked me what motivates me to share. Largely, I read interesting things and want my friends to also experience them. Buffer’s deep integration into iOS allows me to easily share content from whatever app I find it within (Twitter, Newsify, etc.).
  • Time-shifting conversations. If I have a general thought I want to get out but don’t have time to monitor the subsequent conversation, I’ll throw it out onto Buffer, let it cross-post to all platforms after a certain delay. This affords me some time to get things done, then turn back to the threads I started.
  • Optimizing the time of my posts. Buffer runs on a schedule you define. Mine says to post 4 times a day, starting at 10am and every two hours after that. It allow me to share the interesting thing I read at 4:00am but not actually make it public until 10:00am at the earliest. This ensure that more eyeballs are around to read whatever I shared.
  • Cross-posting across multiple platforms. Whatever I am sharing, it is easy to post to Twitter and Facebook with minimal clicks. Otherwise, I would be resorting to doing the same steps twice in two different apps.

Buffer’s free plan supports 10 posts per platform queue. I’ve found this is plenty enough for me to get content out without a big backlog. If your queue is larger than your day’s schedule, you can either increase the frequency of your schedule, or just let Buffer work through your backlog over the course of several days.

Instapaper

Remember Instapaper, the modern-day equivalent of hiding shit under your bed when ordered by your parents to clean your room?  Well, it doesn’t have to be that way!

For GTD, I use Instapaper regularly. If something I encounter on Twitter or Feedly can be consumed quickly, I read it right then and there (the equivalent of the “2 minute rule”). However, if it is too long to consume until later, I punt it Instapaper, which becomes the “someday” folder for media.

Don’t be afraid to not read things — treat Instapaper as “nice to have” but not “must have” in terms of consumption, or you fall into “inbox paralysis” again. In other words, it’s not hurting anything to let it pile in Instapaper. Besides, you can always nuke it if its stack of unread articles daunts you too much.

Getting things into Instapaper is rather seamless. I primarily use a IFTTT Recipe to send starred Feedly articles to Instapaper. Just as often these days, I use Instapaper’s iOS 8 extension, which not only allows me to save any URL but also categorize it into sub-folders right then and there!

Getting things out of Instapaper is awkward, but works well enough in the end. I own an Amazon Kindle Paperwhite, which I’ve setup to receive an emailed digest of the last ten articles saved to Instapaper. While on the Kindle, I have some awkward but usable control over the articles. For example, I can archive completed articles (individually or in batches). Such articles get out of the way and eventually get purged during my irregular visits to the Instapaper website. You can also Like articles directly from the Kindle. I will Like articles as a reminder that I want to either share or blog about them. I remember to do this with an IFTTT Recipe, which takes my Instapaper Likes and creates RtM tasks, complete with all the necessary metadata (tags, location, list name, etc.) — that way, they don’t get lost in the soup of other tasks I already have in the system. From there, I will process them accordingly (post to Buffer, create a blog post draft, etc.) to get it out of its temporary home in RtM.

If you don’t own a Kindle, I highly recommend getting one. The above workflow ensures it’s always filled with a variety of interesting, free content. In addition, it drives getting me to “unplug” at the end of the day. Because it is always at my bedside, instead of browsing Twitter or posting to Instagram before bed (and having the screen’s blue light fuck up my biorhythms or scientific sumshit), I will plow through Instapaper articles on my Kindle and sleep like a baby.

My Other Tools

While outside of the specific theme of this post (organizing actions), these assist me by reducing friction across multiple platforms.

  • 1Password.  Consolidation of all credentials, both work & personal make Hulk happy . Ever since I adopted this tool, it’s cut down on things as simple as website navigation. For example, instead of going to the URL bar and typing “gmail.com”, I can instead ⌘-\, type “Gmail”, hit enter, and I’m both taken to “gmail.com” and authenticated with the least amount of typing. And when you do this several times a day for multiple websites, the time savings add up quick. As a result, I’ve been able to minimize the number of browser bookmarks I need, because 1Password basically acts like my bookmarks manager!
  • Post-It Notes.  I learned this trick from my day: keep a Post-It Notes pad and pen in every room. Write it thoughts immediately, then paste these on outside of laptop monitor. It’s hard to close/store your MacBook when there are a bunch of Post-Its sticking everywhere — my action would be to key these into RtM, then eventually triage them. I’ll also write down single actions per Post-It, then carry around and process the stack (examples: write down some phone numbers, walk down the street to grab lunch, call people while walking). Another good use is my “pile system”: write a Post-It Note, paste it to my keychain, later get in my car, paste the same note onto my rear view mirror, and stare at it during errands until complete. Yes, it’s physically awkward, but I remember shit!
  • Pen.  I knew a dude in high school who taught me to always walk around with a pen. Everywhere you go will always have something to write on, so you can’t fail to capture knowledge when necessary. Ever since I got an iPhone, I don’t carry the pens around anymore, but the analogy remains sound. For nearly three decades, I’ve been using nothing but these pens for sketching and note-taking; they take a pounding and last forever (like your mom, once again).
  • Notebook.  I always have one Moleskine notebook that I carry in my messenger bag. Nothing I ever write within is considered permanent; instead, it’s scratch paper for long-term thinking. And I do not retain these notebooks when full; when the end is nigh, I’ll review each page & rip out only the ones worth retaining.
  • Safari for iOS.  The ability to synchronize bookmarks, browser tabs, and history between iOS and OS X is occasionally helpful.
  • eBooks + Audiobooks. These sate my desire to remain educated and productive, but not tied to anything physical. Amazon has a great feature via Audible: you can purchase a Kindle e-book and matching Audible content, then use their Whispersync services to hand-off between the two. In other words, read the book before going to bed, wake up in the morning to drive to work, and listen to the audio version at the exact point you left off. Yes, it costs money, but you can splurge a little to keep your brain happy.
  • Notes (iOS and OS X).  I’ll use it to facilitate copy-and-paste between platforms, or to work out longer thoughts on iOS (this in combo with iOS Dictation is a killer set of tools).

The Unsolved Black Holes

As you see above, my workflow is complicated, largely because the soup of applications I use itself has a mishmash of incompatible DNA I’ve had to force into cooperation. However, there are still some workflow dead-ends I’m working to overcome. These include:

  • Using multiple desktop computers. As mentioned earlier, I have to manage two machines: my personal MacBook Pro and my work Windows PC. Whenever I’m on the latter, I lose access to much of the above, either because I’m barred from installing it or because the PC equivalents don’t work quite the same. I also can’t Remote Desktop into my work machine, so lately I’ve been seen using two machines at once (NERD!).
  • Facebook Messaging.  I half-jokingly tell friends that if they want me to never see what they have to say, send me a Facebook message. The barriers to using Facebook messaging are many, including the fact you must install a separate iPhone application (the OCD in me is strong when faced with a cluttered iOS screen).  I used to solve for this by turning on email notifications for new messages, so I could at least get notifications in my main inbox (Gmail) & use things like Gmail + Mailbox to track things, but Facebook email notifications don’t work the same anymore and I gave up.
  • YouTube Watch Later playlist.  Sometimes on Twitter, Facebook, or Feedly, I’ll encounter a video I want to watch but just can’t at the time. I’ll add it to a “Watch Later” YouTube playlist. I can’t remember ever opening that playlist. Watching video takes too much commitment/focus vs. other forms of consumption that either work offline (reading Feedly within underground train tunnel) or can be performed alongside other activity (listening to podcasts while running/driving). Yet I keep adding videos to that playlist….
  • Keeping Instapaper cleaned out. All of those articles I archive still sit in Instapaper; they just sit within the Archive folder instead of the main (inbox) folder. I don’t need to clean them out — they could remain there forever without causing trouble for my current workflow. However, they take up space on my iPhone when syncing Instapaper, and I’m a stickler for not wasting iPhone storage. Manually trashing my Instapaper archive is a manual process, with no way to automate it.
  • Sharing from my Kindle. Right now, IFTTT allows me to take my Instapaper Likes and create RtM tasks. I could fully automate this and directly post Likes to Twitter or Facebook, but I would lose the ability to drive which networks I post to, actually editing my post, etc. This is a Kindle, not Instapaper, limitation, and chances of this improving in the future are slim-to-none. One alternative would be to read Instapaper on my iPhone, but I try to avoid bright screens before going to bed. Another alternative would be not to share anything from my Kindle and get over my assumption that all y’all care what I have to say!
  • Keeping my Kindle Library organized. Amazon does a great job pretending they care about Kindles, but their lack of innovation in content management shows they don’t. All of those Instapaper digests that get emailed to my Kindle get deleted from there once I’m finished. However, a copy resides in the cloud, accessible from my online Amazon account. These things pile up, and the only way I can hard-delete them is one. At. A. Time. Kill. Me. Now.
  • iOS Camera Roll.  This is by far the biggest accumulator of trash in my life. I’d say less than 10% of the images I take are worth preserving, but keeping the other 90% from migrating to my MacBook Pro’s iPhotos application and eating up precious disk space takes specific, concentrated effort. I’m hoping that the upcoming OS X Photos app, with its deep integrations into iCloud and shared iOS devices, solves for some of this pain — I imagine I’ll be able to clean out photos on my iPhone and they’d disappear from my Mac simultaneously (and vice-versa).
  • iCloud Notes. Because iOS Evernote sucks donkey dick (bloated, unintuitive UI, ugly), I sometimes use iOS Notes write down thoughts before I lose them. But I don’t have a great track record of ever reviewing these, and the brevity of what I write is often missing the context necessary to remind me why it was so important to create such notes in the first place. When at home or in the office, I find it far easier to write things down on Post-It Notes (see above).

Experimentation

To try and neutralize the black holes, or to lubricate the machinery of my current workflow, I’m always on the hunt for new and improved tools.

Currently, I am trying out location-based reminders. These are ones where you’re tickled when arriving or leaving a specific area (defined by a geo-fence). Reminders (for iOS/OS X) are promising because of their flexibility and ability to be snoozed like alarms (useful for when I get somewhere but can’t immediately act upon it). RtM supports location-based reminders as well, but in a more-limited fashion.

I’ve also been disabling nearly all push notifications and reminders, to cut down on the blur of information flying across my iPhone. Instead, I’m using more passive notifications such as custom vibrations and badge counts, or finding way to limit who can fire the notifications. For example, my wife’s texts have a specific vibration tone (hey now), so I can prioritize her messages while ignoring the rest until later times.

I’m learning to better utilize task prioritization, which refers to the habit of ranking tasks 1-2-3, Critical-Major-Minor, etc. This helps with the paralyzation caused by a holy-massive to-do list that seemingly never shrinks. In RtM, I can mark tasks with Priority 1, 2, or 3, and these display color-coded for additional emphasis. I then create a Smart List that I’ll switch to, so I see just prioritized tasks and achieve some focus.

If you’ve got something in your arsenal that I’m not using, please be sure to share.

Some Closing Advice

These are the things that keep me alive, e.g. my mental tools.

  • Don’t be cheap.  Don’t be afraid to spend money, if it gives you bang for the buck. Sure, RtM costs $25/year, but it’s a no-brainer for me: my membership support product development, gets me access to both premium support and the latest betas, doubles my votes for new features, and allows unlimited sync to iOS. And although I am grandfathered into it, I would still pay for Google Apps for Work today.
  • Check email only 2-3 times a day. When you get into the office, when you leave, and maybe once in between. If people need you, they can find you (encourage them to always call — email often turns into an exercise of dumping off work, so control your time this way). Also, staying out of Gmail and/or Outlook cuts down on the tendency to always reply immediately. Some people will argue they need Outlook open so their meeting notifications pop up — I say add your Outlook calendar to your phone and let the iPhone alert you to what’s coming up.
  • Create focus out of chaos. I arranged my home screen to use must-have apps and no folders whatsoever; everything else goes into one folder. In iOS Spotlight, I removed everything but the app search, so it becomes a de-facto launcher. This combination allows me to disregard organizing applications beyond my home screen.
  • Inbox Armageddon is always an option. If you’re good at GTD, this is never a consideration. However, sometimes you just need to say “Fuck it” and archive everything. If anything is truly important, it will come back to you. This tactic works on anything with an inbox equivalent, not just email (“Google Reader Suicide”, “Instapaper Intervention”, etc.).
  • Never underestimate the “Pile System”. Besides my Post-It Note example above, use it with other objects. For example, I’ll put my keychain on the kitchen counter, then pile underneath keys everything for my errands (library books to be dropped off, parts to return to Lowes, etc.). I’ll also put my keys within my messenger bag, so I cannot possibly drive to work without also bringing along my computer.
  • Unsubscribe from email newsletters. Instead, follow accounts on Twitter. Better yet, don’t follow them on Twitter and instead add them to a Twitter list. For example, I have a news and politics Twitter list that contains information sources  like AP, New York Times, NPR, etc. which are exclusive to that list and not part of my main timeline. There’s rarely anything you get in email that you can’t also get on Twitter and in a far simpler to consume form (thanks 140 character limit!). Less email inbox clutter = greater chance of GTD there.
  • Minimize your other subscriptions.  Besides email digests, limit your consumption in other areas. In your podcast client, don’t subscribe to all new episodes, but just the most recent one. In Twitter, unfollow anyone that isn’t consistently interesting. If you don’t know that you’re missing something, you ultimately won’t ever.
  • Take advantage of the Apple Kool-Aid.  Although their hardware is expensive, you get what you pay for with the benefits of cross-platform unity. Between my MacBook Pro, my wife’s, our numerous iOS devices (including Apple TV), and iCloud, we reap many collective organizational benefits. You can do most (but not all) of the same on PC and/or Android platforms, but not without doing a bunch of the groundwork setup yourselves.
  • You don’t owe anyone anything.  Just because someone emailed you doesn’t mean you must respond except on your own terms. Just because someone followed you on Twitter doesn’t mean you must follow them back. Just because you are Facebook friends with a relative doesn’t mean you cannot block them. Setup the boundaries necessary for you to thrive on these platforms and personally succeed!

In Conclusion

If you’re still here, I salute you. Thanks for indulging me on a subject I’m quite passionate about. Hopefully what I shared inspires you to improve your own workflow’s efficiency, and that doing so reaps benefits elsewhere in your world. I’d love to hear your thoughts on the above.

Update 2015-02-13: I’ve created a Pinterest board of the iOS applications used to support my workflow. And they recently announced you can install the apps directly from Pinterest when viewing the board on your phone!  I guess this means I need to start working Pinterest into my workflow…. 🙂