PeopleSoft Object Owner: (OBJECTOWNERID)
Many years ago PeopleSoft introduced the object owner id which helps identify pretty much every PeopleSoft object (records, pages, content references, etc….) into where that object belongs.
This past week while working on a security matrix, the matrix needed to be broken down by module. While with a little SQL magic and a little help from Excel and its filters the task was made significantly easier. In order to get a listing of the object owner ids within the system you can use look them up in the PSXLATITEM table.
SELECT * FROM PSXLATITEM WHERE FIELDNAME = ‘OBJECTOWNERID’ ORDER BY FIELDVALUE
If you want a specific application, Finance Objects have objectownerid starting with “F”, Human Resources “H”, Supply Chain “D”, Campus Solutions “S”, PeopleTools = “PPT”. I also found some modules had multiple objectownerid values.
This handy bit of SQL courtesy of my friend Issam came in really handy, I honestly had never worked with the “WITH” function. This code is specific to SQL Server 2014, but with a tweak or two it will work in Oracle 12 without an issue. I am sure it will run in earlier versions, but as I have limited experience with the “WITH” function, it was only tested with MSSQL 2014 and Oracle 12c.
WITH PR (PORTAL_NAME, PORTAL_PRODUCT, PORTAL_SEQ_NUM, OBJECTOWNERID, PORTAL_OBJNAME, PORTAL_LABEL, PORTAL_REFTYPE, PORTAL_URI_SEG1, PORTAL_URI_SEG2, MYPATH) AS (
SELECT P.PORTAL_NAME, P.PORTAL_PRODUCT, P.PORTAL_SEQ_NUM, P.OBJECTOWNERID, P.PORTAL_OBJNAME, P.PORTAL_LABEL, P.PORTAL_REFTYPE, PORTAL_URI_SEG1, PORTAL_URI_SEG2, cast(P.PORTAL_LABEL as varchar(4000)) AS MYPATH FROM PSPRSMDEFN P
WHERE P.PORTAL_LABEL = ‘Root’
AND P.PORTAL_NAME = ‘EMPLOYEE’
UNION ALL
SELECT P_ONE.PORTAL_NAME, P_ONE.PORTAL_PRODUCT, P_ONE.PORTAL_SEQ_NUM, P_ONE.OBJECTOWNERID, P_ONE.PORTAL_OBJNAME, P_ONE.PORTAL_LABEL, P_ONE.PORTAL_REFTYPE, P_ONE.PORTAL_URI_SEG1, P_ONE.PORTAL_URI_SEG2, cast( (MYPATH + ‘ –> ‘ + P_ONE.PORTAL_LABEL) as varchar(4000)) AS MYPATH FROM PR P INNER JOIN PSPRSMDEFN P_ONE ON P.PORTAL_NAME = P_ONE.PORTAL_NAME
AND P.PORTAL_REFTYPE = ‘F’
AND P.PORTAL_OBJNAME = P_ONE.PORTAL_PRNTOBJNAME WHERE P_ONE.PORTAL_LABEL <> ‘Root’ AND P_ONE.PORTAL_NAME = ‘EMPLOYEE’ )
SELECT PORTAL_NAME, PORTAL_PRODUCT, PORTAL_SEQ_NUM, OBJECTOWNERID, PORTAL_OBJNAME, PORTAL_LABEL, PORTAL_REFTYPE, PORTAL_URI_SEG1, PORTAL_URI_SEG2, MYPATH FROM PR;
GO