Portal Security Sync

The portal registry (basically the left-hand menu navigation) is made up of folders that can contain subfolders and/or content references. Content references point to different types of content, but most of the time in your HCM, FSCM or CRM application they point to components.
The portal registry security table is called PSPRSMPERM. This table defines which permission lists and roles are assigned to each folder and content reference.

When you do edit permission lists on-line, PeopleCode keeps PSPRSMPERM in sync. But not so when you migrate permission lists, or when you build or update them via SQL (a technique I’ve grown to depend on).

So if you migrate a new permission list into an environment, how do you get the portal registry synchronized? Well, that’s the job of Portal Security Sync.
Portal security sync can be a confusing process. In this article I’ll try to make some sense of what it is, why it’s necessary, what kinds of issues you can expect, and some alternatives.
Portal Security Sync is an app engine that basically goes through all of your permission lists, grabs the components, and figures out which content references that permission list relates to. Then it updates the content reference with that permission list, and walks up the folder hierarchy updating each parent folder with that permission list as well. The table that gets updated is – you guessed it – PSPRSMPERM. Starting with PeopleTools 8.45 it even removes security from folders and content references that are not valid any more.

The problem is that Portal Security Sync can grant more access than you want. Keep in mind that the portal registry can provide multiple paths to get to the same component. Suppose you have AR, but not Order Management. You might end up with an Order Management folder on your top menu that takes you all the way down to the Dunn and Bradstreet Customer Information page in addition to the normal navigation through the Customers menu. Or you might find that managers and employees share a common component to update employee information, but it appears under both the Employee Self Service Navigation Collection and the Manager Self Service Navigation Collection. Now suppose you don’t want to let employees see a menu item called Manger Self Service. That’s too bad, because portal security sync will put it on everybody’s menu due to a common component four levels down.

I believe in keeping one set of permission lists across all environments. I depend on migrating permission lists to keep them in sync across all of my environments. So that means Portal Security Sync is essential. I have two techniques to keep folders from popping up where I don’t want them.

First, I use the “Hide from Portal Navigation” on folders that we don’t want (that aren’t licensed for instance). For example, in that Order Management example above I’d just pull up the unwanted folder in “Structure and Content” and click the Hide from Portal Navigation button.

 

 

 

Sometimes though you might not want to eliminate the entire navigation path. Consider the example I mentioned earlier where we have a manager self service and an employee self service navigation collection, and we only want people with a manager role to see the manager self service collection. In this case, we must manually remove the unwanted Employee Self Service permission lists from the Folder Security tab of the Manager Self Service folder, and leave the Manager Self Service ones. In reality, I have an SQL script that I run after Portal Security Sync for this purpose. I plan to create an SQR or app engine to do the deletes, and schedule it to run after Portal Security Sync within a PSJob.

One trick about portal security sync I wanted to mention: It needs to be run as a user with the Portal Administrator role. Portal Administrator is a special role which makes every entry in the portal registry show up, regardless of how your permission lists are defined.

Another problem I see from time to time is that after running portal security sync, the changes don’t necessarily show up for the users. If that happens to you, first check the PSPRSMPERM table or look on-line to make sure the content references actually got populated with the correct permission lists. If that looks OK, have the user clear their browser cache (temporary internet files). If that doesn’t work, purge app server cache. If that doesn’t work, try the web server cache.

Granted, Portal Security Sync is a shotgun approach. If you’re only wanting to set up security for a single component, You can run the Component Registration Wizard process in app designer to get ‘er done. If you like living on the edge, check out the following SQLPlus script I’m working on to show what rows should be in PSPRSMPERM, and inserts them without generating unique constraints. I uses Oracle’s Connect By feature to walk up the folder hierarchy, so my apologies to non-Oracle users. If you run it in SQLPlus, it’ll prompt you for a component.

-- Identify the rows that should be inserted into PSPRSMPERM
select DISTINCT AA.PORTAL_NAME, AA.PORTAL_REFTYPE, AA.PORTAL_OBJNAME, CC.CLASSID, 'P'
FROM
(SELECT PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, connect_by_root(A.PORTAL_URI_SEG2) PNLGRPNAME, 'ALLPAGES', 'P', '0'
from (SELECT distinct A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, a.PORTAL_PRNTOBJNAME,
A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3, A.PORTAL_REFTYPE
FROM PSPRSMDEFN A
WHERE
A.portal_name = 'EMPLOYEE' and
A.portal_objname <> A.portal_prntobjname and
not exists (
select 'x'
from PSPRSMSYSATTRVL
where portal_name = A.PORTAL_NAME AND
portal_Reftype = A.PORTAL_REFTYPE and
portal_objname = A.PORTAL_OBJNAME and
PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and
A.PORTAL_OBJNAME not in ('CO_NAVIGATION_COLLECTIONS', 'PORTAL_BASE_DATA')
)
) A
start with PORTAL_URI_SEG2 = '&1'
connect by prior A.portal_prntobjname = A.portal_objname ) AA, PSMENUITEM BB, PSAUTHITEM CC
WHERE AA.PNLGRPNAME = BB.PNLGRPNAME AND
BB.MENUNAME = CC.MENUNAME AND
BB.BARNAME = CC.BARNAME AND
BB.ITEMNAME = CC.BARITEMNAME
/
-- Update version stuff so the user doesn’t have to clear browser cache after the update
UPDATE PSVERSION SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = 'SYS'
/
UPDATE PSLOCK SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = 'PRSM'
/

-- Delete the rows that may already exist so we don’t get unique constraints
DELETE FROM PSPRSMPERM PPERM
WHERE (PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, PORTAL_PERMNAME, PORTAL_PERMTYPE) IN
(select DISTINCT AA.PORTAL_NAME, AA.PORTAL_REFTYPE, AA.PORTAL_OBJNAME, CC.CLASSID, 'P'
FROM
(SELECT PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, connect_by_root(A.PORTAL_URI_SEG2) PNLGRPNAME, 'ALLPAGES', 'P', '0'
from (SELECT distinct A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, a.PORTAL_PRNTOBJNAME,
A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3, A.PORTAL_REFTYPE
FROM PSPRSMDEFN A
WHERE
A.portal_name = 'EMPLOYEE' and
A.portal_objname <> A.portal_prntobjname and
not exists (
select 'x'
from PSPRSMSYSATTRVL
where portal_name = A.PORTAL_NAME AND
portal_Reftype = A.PORTAL_REFTYPE and
portal_objname = A.PORTAL_OBJNAME and
PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and
A.PORTAL_OBJNAME not in ('CO_NAVIGATION_COLLECTIONS', 'PORTAL_BASE_DATA')
)
) A
start with PORTAL_URI_SEG2 = '&1'
connect by prior A.portal_prntobjname = A.portal_objname ) AA, PSMENUITEM BB, PSAUTHITEM CC
WHERE AA.PNLGRPNAME = BB.PNLGRPNAME AND
BB.MENUNAME = CC.MENUNAME AND
BB.BARNAME = CC.BARNAME AND
BB.ITEMNAME = CC.BARITEMNAME
)
/
-- Now insert the new Portal Registry Rows
insert into PSPRSMPERM (PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, PORTAL_PERMNAME, PORTAL_PERMTYPE, PORTAL_ISCASCADE)
(select DISTINCT AA.PORTAL_NAME, AA.PORTAL_REFTYPE, AA.PORTAL_OBJNAME, CC.CLASSID, 'P', '0'
FROM
(SELECT PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, connect_by_root(A.PORTAL_URI_SEG2) PNLGRPNAME, 'ALLPAGES', 'P', '0'
from (SELECT distinct A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, a.PORTAL_PRNTOBJNAME,
A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3, A.PORTAL_REFTYPE
FROM PSPRSMDEFN A
WHERE
A.portal_name = 'EMPLOYEE' and
A.portal_objname <> A.portal_prntobjname and
not exists (
select 'x'
from PSPRSMSYSATTRVL
where portal_name = A.PORTAL_NAME AND
portal_Reftype = A.PORTAL_REFTYPE and
portal_objname = A.PORTAL_OBJNAME and
PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and
A.PORTAL_OBJNAME not in ('CO_NAVIGATION_COLLECTIONS', 'PORTAL_BASE_DATA')
)
) A
start with PORTAL_URI_SEG2 = '&1'
connect by prior A.portal_prntobjname = A.portal_objname ) AA, PSMENUITEM BB, PSAUTHITEM CC
WHERE AA.PNLGRPNAME = BB.PNLGRPNAME AND
BB.MENUNAME = CC.MENUNAME AND
BB.BARNAME = CC.BARNAME AND
BB.ITEMNAME = CC.BARITEMNAME
)
/
--Update the version stuff which should keep users from having to clear browser cache
UPDATE PSPRSMDEFN
SET VERSION = (SELECT VERSION FROM PSLOCK WHERE OBJECTTYPENAME = 'PRSM'),
LASTUPDDTTM=SYSDATE
WHERE (PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME) IN
(select DISTINCT AA.PORTAL_NAME, AA.PORTAL_REFTYPE, AA.PORTAL_OBJNAME
FROM
(SELECT PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, connect_by_root(A.PORTAL_URI_SEG2) PNLGRPNAME, 'ALLPAGES', 'P', '0'
from (SELECT distinct A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, a.PORTAL_PRNTOBJNAME,
A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3, A.PORTAL_REFTYPE
FROM PSPRSMDEFN A
WHERE
A.portal_name = 'EMPLOYEE' and
A.portal_objname <> A.portal_prntobjname and
not exists (
select 'x'
from PSPRSMSYSATTRVL
where portal_name = A.PORTAL_NAME AND
portal_Reftype = A.PORTAL_REFTYPE and
portal_objname = A.PORTAL_OBJNAME and
PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and
A.PORTAL_OBJNAME not in ('CO_NAVIGATION_COLLECTIONS', 'PORTAL_BASE_DATA')
)
) A
start with PORTAL_URI_SEG2 = '&1'
connect by prior A.portal_prntobjname = A.portal_objname ) AA
)
/
-- Un-define our &1 variable so it’ll prompt again the next time the script is run
UNDEF 1

Record Keys and Field Attributes SQL

SELECT r.recname, r.fieldname, l.longname, l.shortname, decode(f.fieldtype, 0, 'Character', 1, 'Long Character', 2, 'Number', 3, 'Signed Number', 4, 'Date', 5, 'Time', 6, 'Date Time', 8, 'Image', 9, 'Image Reference', ' ') fieldtype, f.LENGTH, decode(
  (SELECT 'Y'
   FROM pskeydefn k
   WHERE r.recname = k.recname AND r.fieldname = k.fieldname AND k.indexid = '_'), 'Y', 'Y', ' ')
KEY, decode(
  (SELECT ascdesc
   FROM pskeydefn k
   WHERE r.recname = k.recname AND r.fieldname = k.fieldname AND k.indexid = '_'), '1', 'Ascend', 0, 'Descend', ' ')
keytype, decode(
  (SELECT 'Y'
   FROM psrecfieldall rf
   WHERE rf.recname = r.recname AND rf.fieldname = r.fieldname AND bitand(rf.useedit, 256) > 0), 'Y', 'Y', ' ')
required
FROM psrecfieldall r, psdbfldlabl l, psdbfield f
WHERE r.fieldname = l.fieldname AND l.default_label = 1 AND r.fieldname = f.fieldname AND r.recname IN(Provide Record List)
ORDER BY r.recname, decode(
  (SELECT 'Y'
   FROM pskeydefn m
   WHERE r.recname = m.recname AND r.fieldname = m.fieldname AND m.indexid = '_'), 'Y', 'Y', ' ')
DESC;

Getting Top N Rows SQL

SELECT *
FROM   (SELECTJ.EMPLID,J.EMPL_RCD,J.EFFDT,J.EFFSEQ,J.EMPL_STATUS,J.Action,J.ACTION_REASON,J.POSITION_NBR,J.REPORTS_TO,
               Row_number() OVER(PARTITION BY J.EMPLID,J.EMPL_RCD ORDER BY J.EMPLID, J.EMPL_RCD, J.EFFDT DESC,J.EFFSEQ DESC) N
        FROM   PS_JOB J
        WHERE  EMPLID IN (Provide Emplids))
WHERE  N < 5

Find Navigation SQL

 SELECT LEVEL0.PORTAL_LABEL || ' > ' || LEVEL1.PORTAL_LABEL || ' > '  || LEVEL2.PORTAL_LABEL || ' > '  ||  level3.PORTAL_LABEL PATH_TO_COMPONENT
FROM PSPRSMDEFN level3
, PSPRSMDEFN level2
, PSPRSMDEFN level1
, PSPRSMDEFN LEVEL0
WHERE level3.PORTAL_URI_SEG2 = {Provide Component Name)
AND level3.PORTAL_PRNTOBJNAME = level2.PORTAL_OBJNAME
AND level2.PORTAL_PRNTOBJNAME = level1.PORTAL_OBJNAME
AND level1.PORTAL_PRNTOBJNAME = LEVEL0.PORTAL_OBJNAME
AND level3.PORTAL_NAME = level2.PORTAL_NAME
AND level2.PORTAL_NAME = level1.PORTAL_NAME
AND level1.PORTAL_NAME = LEVEL0.PORTAL_NAME;

Find Message Catalogs in PeopleCode SQL

With Srch As (
Select MsgSet_HexCode, MsgNbr_HexCode,
       Substr(MsgSet_HexCode, -2)|| SubStr(MsgSet_HexCode,1,2) MsgSet_HexSrch,
       Substr(MsgNbr_HexCode, -2)|| SubStr(MsgNbr_HexCode,1,2) MsgNbr_HexSrch,
       Nvl2(MsgSet_HexCode,'500000'||Substr(MsgSet_HexCode, -2)||
                           SubStr(MsgSet_HexCode,1,2)||Lpad('3', 30, '0'),Null) ||
       Nvl2(MsgNbr_HexCode,'500000'||Substr(MsgNbr_HexCode, -2)||
                           SubStr(MsgNbr_HexCode,1,2)||Lpad('3', 30, '0'),Null) Srch_Str
From
(
Select (
Select LPad(Replace(Max(Sys_connect_by_path(HEX, ' ')), ' ', ''), 4, '0') HexCode
From (Select NUM,
       Chr(Floor(Mod(:MsgSetNbr/POW, 16))
           + Decode(Floor(Floor(Mod(:MsgSetNbr/POW, 16))/10), 0, 48, 55)
          ) HEX
  From (Select Rownum-1 NUM, Power(16, Rownum-1) POW From Dual Connect By Level <= 8)
 Where :MsgSetNbr/POW > 1)
Start With NUM = FLOOR(LOG(16,:MsgSetNbr)) Connect By Prior NUM = NUM + 1) MsgSet_HexCode,
(
Select LPad(Replace(Max(Sys_connect_by_path(HEX, ' ')), ' ', ''), 4, '0') HexCode
From (Select NUM,
       Chr(Floor(Mod(:MsgNbr/POW, 16))
           + Decode(Floor(Floor(Mod(:MsgNbr/POW, 16))/10), 0, 48, 55)
          ) HEX
  From (Select Rownum - 1 NUM, Power(16, Rownum-1) POW From Dual Connect By Level <= 8)
 Where :MsgNbr / POW > 1)
Start With NUM = FLOOR(LOG(16,:MsgNbr)) Connect By Prior NUM = NUM + 1) MsgNbr_HexCode
From Dual
)
)
Select P.*
From PSPCMPROG P, Srch
Where DBMS_LOB.INSTR(PROGTXT, Srch_Str)  > 0;

Find String In PeopleCode Using SQL

With Srch As
(
Select Str, SubStr(Max(HexStr),3) Srch_Str
From
(
Select RNum, Str, Hexcode, Sys_connect_by_path(HexCode, '00') HexStr
From (
Select RNum, Str, Chr(Floor(Mod(Code/16, 16))
           + Decode(Floor(Floor(Mod(Code/16, 16))/10), 0, 48, 55)) ||
       Chr(Mod(Code, 16)
           + Decode(Floor(Mod(Code, 16)/10), 0, 48, 55)) HexCode
From (        
Select RNum, Str, Ascii(Substr(Str, RNum, 1)) Code
  From (Select Rownum RNum From Dual Connect By Level <= Length(:Str)),
       (Select :Str Str From Dual
        Union
        Select Upper(:Str) From Dual
        Union
        Select Lower(:Str) From Dual
        Union
        Select InitCap(:Str) From Dual
       )
 ))
Start With RNum = 1 Connect By Prior RNum = RNum - 1 And Prior Str = Str
)
Group By Str
)
Select
Str, P.*
From PSPCMPROG P, Srch
Where DBMS_LOB.INSTR(PROGTXT, Srch_Str)  > 0;

Project Object Finder SQL

SELECT PROJECTNAME,
CASE OBJECTTYPE
  WHEN 0 THEN CASE NVL((SELECT RECTYPE FROM PSRECDEFN WHERE RECNAME = I.OBJECTVALUE1), 99)
                           WHEN 0 THEN 'SQL Table in DB'
                           WHEN 1 THEN 'SQL View in DB'
                           WHEN 2 THEN 'Work Record'
                           WHEN 3 THEN 'Sub Record'
                           WHEN 5 THEN 'Dynamic View'
                           WHEN 6 THEN 'Query View'
                           WHEN 7 THEN 'Temporary Table'
                           ELSE 'Other Record or Deleted' END
  WHEN 1     THEN 'Index'
  WHEN 2     THEN 'Field'
  WHEN 3     THEN 'Field Format'
  WHEN 4     THEN 'Translate Value'
  WHEN 5     THEN 'Page'
  WHEN 6     THEN 'Menu'
  WHEN 7     THEN 'Component'
  WHEN 8     THEN 'Record PeopleCode'
  WHEN 9     THEN 'Menu PeopleCode'
  WHEN 10    THEN 'Query'
  WHEN 11    THEN 'Tree Structure'
  WHEN 12    THEN 'Tree'
  WHEN 13    THEN 'Access Group'
  WHEN 14    THEN 'Color'
  WHEN 15    THEN 'Style'
  WHEN 16    THEN 'Business Process Map'
  WHEN 17    THEN 'Business Process'
  WHEN 18    THEN 'Activity'
  WHEN 19    THEN 'Role'
  WHEN 20    THEN 'Process Definition'
  WHEN 21    THEN 'Process Server Definition'
  WHEN 22    THEN 'Process Type Definition'
  WHEN 23    THEN 'Process Job Definition'
  WHEN 24    THEN 'Process Recurrence Definition'
  WHEN 25    THEN 'Message Catalog'
  WHEN 26    THEN 'Dimension'
  WHEN 27    THEN 'Cube Definition'
  WHEN 28    THEN 'Cube Instance Definition'
  WHEN 29    THEN 'Business Interlink'
  WHEN 30    THEN CASE OBJECTVALUE2
                               WHEN '0' THEN 'SQL Object'
                               WHEN '1' THEN 'App Engine SQL'
                               WHEN '2' THEN 'Record View SQL'
                               WHEN '5' THEN 'Query for DDAUDIT or SYSAUDIT'
                               WHEN '6' THEN 'App Engine XML SQL'
                               ELSE 'SQL' END
  WHEN 31    THEN 'File Layout'
  WHEN 32    THEN 'Component Interface'
  WHEN 33    THEN 'Application Engine Program'
  WHEN 34    THEN 'Application Engine Section'
  WHEN 35    THEN 'Message Node'
  WHEN 36    THEN 'Message Channel'
  WHEN 37    THEN 'Message'
  WHEN 38    THEN 'Approval rule set'
  WHEN 39    THEN 'Message PeopleCode'
  WHEN 40    THEN 'Subscription PeopleCode'
  WHEN 41    THEN 'N/A'
  WHEN 42    THEN 'Component Interface PeopleCode'
  WHEN 43    THEN 'Application Engine PeopleCode'
  WHEN 44    THEN 'Page PeopleCode'
  WHEN 45    THEN 'Page Field PeopleCode'
  WHEN 46    THEN 'Component PeopleCode'
  WHEN 47    THEN 'Component Record PeopleCode'
  WHEN 48    THEN 'Component Record Field PeopleCode'
  WHEN 49    THEN 'Image'
  WHEN 50    THEN 'Style sheet'
  WHEN 51    THEN 'HTML'
  WHEN 52    THEN 'Not used'
  WHEN 53    THEN 'Permission List'
  WHEN 54    THEN 'Portal Registry Definitions'
  WHEN 55    THEN 'Portal Registry Structure'
  WHEN 56    THEN 'URL Definitions'
  WHEN 57    THEN 'Application Packages'
  WHEN 58    THEN 'Application Package PeopleCode'
  WHEN 59    THEN 'Portal Registry User Homepage'
  WHEN 60    THEN 'Problem Type'
  WHEN 61    THEN 'Archive Templates'
  WHEN 62    THEN 'XSLT'
  WHEN 63    THEN 'Portal Registry User Favorite'
  WHEN 64    THEN 'Mobile Page'
  WHEN 65    THEN 'Relationships'
  WHEN 66    THEN 'Component Interface Property PeopleCode'
  WHEN 67    THEN 'Optimization Models'
  WHEN 68    THEN 'File References'
  WHEN 69    THEN 'File Type Codes'
  WHEN 70    THEN 'Archive Object Definitions'
  WHEN 71    THEN 'Archive Templates (Type 2)'
  WHEN 72    THEN 'Diagnostic Plug In'
  WHEN 73    THEN 'Analytic Model'
  WHEN 79    THEN 'Service'
  WHEN 80    THEN 'Service Operation'
  WHEN 81    THEN 'Service Operation Handler'
  WHEN 82    THEN 'Service Operation Version'
  WHEN 83    THEN 'Service Operation Routing'
  WHEN 84    THEN 'Info Broker Queues'
  WHEN 85    THEN 'XLMP Template Definition'
  WHEN 86    THEN 'XLMP Report Definition'
  WHEN 87    THEN 'XMLP File Definition'
  WHEN 88    THEN 'XMPL Data Source Definition'
  ELSE 'Unknown ' || OBJECTTYPE END AS Object_Type,CASE OBJECTTYPE
  WHEN 12    THEN OBJECTVALUE3
  WHEN 30    THEN CASE WHEN OBJECTVALUE2 = 0 THEN OBJECTVALUE1 /* SQL Object */
                       WHEN OBJECTVALUE2 = 1 THEN SUBSTR(OBJECTVALUE1, 1, 12)
                       WHEN OBJECTVALUE2 = 2 THEN OBJECTVALUE1 /* Record View SQL */
                       ELSE ' ' END
  WHEN 34    THEN TRIM(OBJECTVALUE1) || '.' || TRIM(OBJECTVALUE2)
  WHEN 62    THEN TRIM(SUBSTR(OBJECTVALUE1, 1, 12))
  ELSE OBJECTVALUE1 END AS NAME,
CASE
  WHEN OBJECTTYPE = 1    THEN 'Index: ' || OBJECTVALUE2
  WHEN OBJECTTYPE = 4    THEN 'XLAT: ' || OBJECTVALUE2 || '; Date: ' || OBJECTVALUE3 || '; ' ||
                             NVL((SELECT 'ShortName: ' || XLATSHORTNAME || '; LongName: ' ||
                                          XLATLONGNAME || '; Status: ' || EFF_STATUS
                                  FROM PSXLATITEM
                                  WHERE FIELDNAME = I.OBJECTVALUE1 AND FIELDVALUE = I.OBJECTVALUE2
                                     AND EFFDT = TO_DATE(I.OBJECTVALUE3, 'YYYY-MM-DD')
                                  ), 'XLAT Deleted')
  WHEN OBJECTTYPE = 7    THEN 'Market: ' || OBJECTVALUE2
  WHEN OBJECTTYPE = 8    THEN OBJECTVALUE1 || '.' || OBJECTVALUE2 || '.' || OBJECTVALUE3
  WHEN OBJECTTYPE = 9    THEN OBJECTVALUE2 || '.' || OBJECTVALUE3 || '.' || OBJECTVALUE4
  WHEN OBJECTTYPE = 12   THEN 'EFFDT: ' || OBJECTVALUE4
  WHEN OBJECTTYPE = 20   THEN 'Process Name: ' || OBJECTVALUE2
  WHEN OBJECTTYPE IN(22, 40)   THEN OBJECTVALUE2 || '.' || OBJECTVALUE3
  WHEN OBJECTTYPE = 25   THEN 'Message: ' || OBJECTVALUE2 ||
                              ' (Message Set Descr: ' || OBJECTVALUE3 || ')'
  WHEN OBJECTTYPE = 30   THEN
                         CASE WHEN OBJECTVALUE2 = 0 THEN ' ' /* SQL Object */
                              WHEN OBJECTVALUE2 = 1 THEN 'AE Progam: ' ||
                                           SUBSTR(OBJECTVALUE1, 1, 12) || '  Section: ' ||
                                           SUBSTR(I.OBJECTVALUE1, 13, 8) || '  Step: ' ||
                                           SUBSTR(OBJECTVALUE1, 21, 8) || ' Type: ' ||
                                           DECODE(SUBSTR(OBJECTVALUE1, 29, 1), 'S', 'SQL',
                                                  'D', 'Do Select', 'W', 'Do While',
                                                  'H', 'Do When', 'N', 'Do Until',
                                           SUBSTR(OBJECTVALUE1, 29, 1))
                               WHEN OBJECTVALUE2 = 2 THEN ' ' /* Record View SQL */
                               ELSE ' ' END
  WHEN OBJECTTYPE = 38   THEN 'EFFDT: ' || OBJECTVALUE2
  WHEN OBJECTTYPE IN(39, 42, 44)   THEN OBJECTVALUE2
  WHEN OBJECTTYPE = 43   THEN
                         CASE WHEN TRIM(OBJECTVALUE4) = 'OnExecute' THEN
                                     'Section: ' || SUBSTR(I.OBJECTVALUE2, 1, 8) || '; Step: ' ||
                                     OBJECTVALUE3 || '; Market: ' || SUBSTR(I.OBJECTVALUE2, 9, 3) ||
                                     '; Database: ' || TRIM(SUBSTR(OBJECTVALUE2, 12, 8)) ||
                                     '; EFFDT: ' || TRIM(SUBSTR(OBJECTVALUE2, 21, 10))
                              ELSE 'Section: ' || OBJECTVALUE2 || '; Market: ' || OBJECTVALUE3
                                       || '; Database: ' || TRIM(SUBSTR(OBJECTVALUE4, 12, 8)) ||
                                       '; EFFDT: ' || TRIM(SUBSTR(OBJECTVALUE4, 21, 10)) END
  WHEN OBJECTTYPE = 46   THEN 'Market: ' || OBJECTVALUE2 || '; Event: ' || OBJECTVALUE3
  WHEN OBJECTTYPE = 47   THEN 'Market: ' || OBJECTVALUE2 || '; Record: ' || OBJECTVALUE3
                                         || '; Event: ' || OBJECTVALUE4
  WHEN OBJECTTYPE = 48   THEN 'Market: ' || OBJECTVALUE2 || '; Record: ' || OBJECTVALUE3
                                         || '; Field: ' || TRIM(SUBSTR(OBJECTVALUE4, 1, 18))
                                         || '; Event: ' || TRIM(SUBSTR(OBJECTVALUE4, 19, 16))
  WHEN OBJECTTYPE = 55   THEN DECODE(OBJECTVALUE2, 'C', 'Content: ', 'F', 'Folder: ') || OBJECTVALUE3
  WHEN OBJECTTYPE = 57   THEN
                         CASE WHEN TRIM(OBJECTVALUE4) NOT IN(' ', ':', '.') THEN
                                  'Subclass: ' || TRIM(OBJECTVALUE2) || ':' || TRIM(OBJECTVALUE3)
                                   || ':' || TRIM(OBJECTVALUE4)
                              ELSE
                                 CASE WHEN TRIM(OBJECTVALUE3) NOT IN(' ', ':', '.') THEN
                                           'Subclass: ' || TRIM(OBJECTVALUE2) || ':' ||
                                           TRIM(OBJECTVALUE3)
                                     ELSE
                                         CASE WHEN TRIM(OBJECTVALUE2) NOT IN(' ', ':', '.') THEN
                                                  'Subclass: ' ||  TRIM(OBJECTVALUE2)
                                              ELSE ' '
                                         END
                                 END
                         END
  WHEN OBJECTTYPE IN(58, 63, 68, 81, 82, 83, 87, 88) THEN
                         CASE WHEN TRIM(OBJECTVALUE4) IS NOT NULL THEN
                                   TRIM(OBJECTVALUE2) || '.' || TRIM(OBJECTVALUE3)  || '.'
                                   || TRIM(OBJECTVALUE4)
                              ELSE
                                  CASE WHEN TRIM(OBJECTVALUE3) IS NOT NULL THEN
                                            TRIM(OBJECTVALUE2) || '.' || TRIM(OBJECTVALUE3)
                                  ELSE
                                      CASE WHEN TRIM(OBJECTVALUE2) IS NOT NULL THEN
                                                TRIM(OBJECTVALUE2)
                                           ELSE ' '
                                      END
                                  END
                              END
  WHEN OBJECTTYPE = 59   THEN TRIM(OBJECTVALUE2)
  WHEN OBJECTTYPE = 62   THEN 'AE Progam: ' || SUBSTR(OBJECTVALUE1, 1, 12) || '  Section: ' ||
                              SUBSTR(I.OBJECTVALUE1, 13, 8) || '  Step: ' ||
                              SUBSTR(OBJECTVALUE1, 21, 8)
  ELSE ' ' END AS EXTENDED_OBJ_NAME,
CASE OBJECTTYPE
  WHEN 0    THEN NVL((SELECT RECDESCR FROM PSRECDEFN WHERE RECNAME = I.OBJECTVALUE1), ' ')
  WHEN 1    THEN NVL((SELECT IDXCOMMENTS FROM PSINDEXDEFN WHERE RECNAME = I.OBJECTVALUE1
                      AND INDEXID = I.OBJECTVALUE2), ' ')
  WHEN 3    THEN NVL((SELECT DESCR FROM PSFMTDEFN WHERE FORMATFAMILY = I.OBJECTVALUE1), ' ')
  WHEN 5    THEN NVL((SELECT DESCR FROM PSPNLDEFN WHERE PNLNAME = I.OBJECTVALUE1), ' ')
  WHEN 6    THEN NVL((SELECT DESCR FROM PSMENUDEFN WHERE MENUNAME = I.OBJECTVALUE1), ' ')
  WHEN 7    THEN NVL((SELECT DESCR FROM PSPNLGRPDEFN WHERE PNLGRPNAME = I.OBJECTVALUE1
                      AND MARKET = I.OBJECTVALUE2), ' ')
  WHEN 20   THEN NVL((SELECT DESCR FROM PS_PRCSDEFN WHERE PRCSTYPE = I.OBJECTVALUE1
                      AND PRCSNAME = I.OBJECTVALUE2), ' ')
  WHEN 32   THEN NVL((SELECT DESCR FROM PSBCDEFN WHERE BCNAME = I.OBJECTVALUE1), ' ')
  WHEN 33   THEN NVL((SELECT DESCR FROM PSAEAPPLDEFN WHERE AE_APPLID = I.OBJECTVALUE1), ' ')
  ELSE ' ' END AS DESCR
FROM PSPROJECTITEM I
WHERE OBJECTTYPE = (PROVIDE THE OBJECT TYPE AND OBJECTVALUE2(IF NEEDED) FROM THE SELECT PORTION OF THE SQL)
ORDER BY OBJECTTYPE, 2, OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4

PeopleSoft Meta Tables

Application Engine Meta Data

PSAEAPPLDEFN

AE header record; 1 row per app engine

PSAEAPPLSTATE

AE state records (shows which one is the default)

PSAEAPPLTEMPTBL

AE temp tables assigned

PSAESECTDEFN

AE sections: public or private

PSAESECTDTLDEFN

AE section: descriptions, market, DB Type, EFFDT, EFF_STATUS, and auto commit

PSAESTEPDEFN

AE steps within section: description, market, DB Type, EFFDT, EFF_STATUS

PSAESTMTDEFN

AE actions within AE step: Step type (SQL, Do Select, etc.) with SQLID. 

PSAESTEPMSGDEFN

AE message (parameters in each step)

AEREQUESTPARM

AE request parameters table behind the AE run control page.

Find All Records Referenced in App Engine

1. Find the Temp Records (TAO) that are used:

SELECT RECNAME FROM PSAEAPPLTEMPTBL WHERE AE_APPLID = 'MY_APP_PROGRAM_NAME'

2. If there are records, find the number of instances:

SELECT TEMPTBLINSTANCES FROM PSAEAPPLDEFN WHERE AE_APPLID = 'MY_APP_PROGRAM_NAME'

3. For each table found in step 1, create as many instances as step 2 indicates.
For example, step 1 returns record MY_AE_TEMP_TAO. The SQL executed below gives me a count of 4.
Therefore, I have 5 tables that could be used in my Application Engine: PS_MY_AE_TEMP_TAO, PS_MY_AE_TEMP_TAO1, PS_MY_AE_TEMP_TAO2, PS_MY_AE_TEMP_TAO3, PS_MY_AE_TEMP_TAO4
4. Retrieve all the App Engine SQL:

SELECT SQLTEXT FROM PSAESTMTDEFN AE, PSSQLTEXTDEFN S WHERE AE.AE_APPLID = 'MY_APP_PROGRAM_NAME' AND S.SQLID = AE.SQLID

ORDER BY AE.SQLID, S.SEQNUM

5. Visually break apart all the SQL statements to list the tables referenced in the App Engine.
6. Review all App Engine PeopleCode to see if any references to outside tables.

Change Control

PSCHGCTLHIST

History of PeopleTools objects locked with OPRID, project name, incident, and description

PSCHGCTLLOCK

Current PeopleTools objects locked with OPRID, project name, incident, and description

Component Interface Meta Data

PSBCDEFN

Component Interface header record; one row for each component interface

PSBCITEM

One row for each property on the component interface

Component Meta Data

PSPNLGRPDEFN

Component header flags, description, and component search records.

PSPNLGROUP

All pages in a component

Field Meta Data

PSDBFIELD
Lists PeopleSoft fields and the field characteristics

FIELDTYPE Definitions

0 = Character

1 = Long Character

2 = Number

3 = Signed Number

4 = Date

5 = Time

6 = Date Time

8 = Image

9 = Image Reference

PSDBFLDLABL

Lists the field labels with DEFAULT_LABEL = 1 being the default label

PSXLATITEM

Lists Translate Values

PSFMTITEM

Lists field formats

Field Values for Tools Tables

PSPROJECTITEM

PSPROJECTITEM.OBJECTTYPE

0 = Record

1 = Index

2 = Field

3 = Field Format

4 = Translate Value

5 = Page

6 = Menu

7 = Component

8 = Record PeopleCode

9 = Menu PeopleCode

10 = Query

11 = Tree Structure

12 = Tree

13 = Access Group

14 = Color

15 = Style

16 = Business Process Map

17 = Business Process

18 = Activity

19 = Role

20 = Process Definition

21 = Process Server Definition

22 = Process Type Definition

23 = Process Job Definition

24 = Process Recurrence Definition

25 = Message Catalog

26 = Dimension

27 = Cube Definition

28 = Cube Instance Definition

29 = Business Interlink

30 = SQL Object

           Check value of OBJECTVALUE2

           0 = SQL Object

           1 = App Engine SQL

           2 = Record View SQL

           5 = Query for DDAUDIT or SYSAUDIT

           6 = App Engine XML SQL

31 = File Layout

32 = Component Interface

33 = Application Engine Program

34 = Application Engine Section

35 = Message Node

36 = Message Channel

37 = Message

38 = Approval rule set

39 = Message PeopleCode

40 = Subscription PeopleCode

41 = N/A

42 = Component Interface PeopleCode

43 = Application Engine PeopleCode

44 = Page PeopleCode

45 = Page Field PeopleCode

46 = Component PeopleCode

47 = Component Record PeopleCode

48 = Component Record Field PeopleCode

49 = Image

50 = Style sheet

51 = HTML

52 = Not used

53 = Permission List

54 = Portal Registry Definitions

55 = Portal Registry Structure

56 = URL Definitions

57 = Application Packages

58 = Application Package PeopleCode

59 = Portal Registry User Homepage

60 = Problem Type

61 = Archive Templates

62 = XSLT

63 = Portal Registry User Favorite

64 = Mobile Page

65 = Relationships

66 = Component Interface Property PeopleCode

67 = Optimization Models

68 = File References

69 = File Type Codes

70 = Archive Object Definitions

71 = Archive Templates (Type 2)

72 = Diagnostic Plug In

73 = Analytic Model

79 = Service

80 = Service Operation

81 = Service Operation Handler

82 = Service Operation Version

83 = Service Operation Routing

84 = Info Broker Queues

85 = XLMP Template Definition

86 = XLMP Report Definition

87 = XMLP File Definition

88 = XMPL Data Source Definition

PSPROJECTITEM.UPGRADEACTION

0 = Copy

1 = Delete

2 = None

3 = CopyProp

PSPROJECTITEM.SOURCESTATUS

0 = Unknown

1 = Absent

2 = Changed

3 = Unchanged

4 = *Changed

5 = *Unchanged

6 = Same

PSRECDEFN

PSRECDEFN.RECTYPE

0 = SQL Table in DB

1 = SQL View in DB

2 = Derived/Work Record

3 = SubRecord

5 = Dynamic View

6 = Query View

7 = Temporary Table

PSDBFIELD

PSDBFIELD.FIELDTYPE

0 = Character

1 = Long Character

2 = Number

3 = Signed Number

4 = Date

5 = Time

6 = Date Time

8 = Image

9 = Image Reference

PSPNLFIELD

PSPNLFIELD.FIELDTYPE

0 = Label

1 = Frame

2 = Group Box

3 = Static Image

4 = Edit Box

5 = Drop-Down List Box

6 = Long Edit Box

7 = Check Box

8 = Radio Button

9 = Image

10 = Scroll Bar

11 = Subpage

12 = Push Button/Hyperlink - Destination: PeopleCode Command

13 = Push Button/Hyperlink – Destination: Scroll Action

14 = Push Button/Hyperlink – Destination: Toolbar Action

15 = Push Button/Hyperlink – Destination: External Link

16 = Push Button/Hyperlink – Destination: Internal Link (Transfer)

17 = Push Button/Hyperlink – Destination: Process (AE, etc.)

18 = Secondary Page Button

19 = Grid

20 = Tree

21 = Push Button/Hyperlink - Destination: Secondary Page

22 = N/A

23 = Horizontal Rule

24 = Tab Separator (in a grid)

25 = HTML Area

26 = Push Button/Hyperlink – Destination: Prompt Action

27 = Scroll Area

30 = Chart

31 = Push Button/Hyperlink – Destination: Instant Messaging Action

32 = Analytic Grid

PSSQLDEFN

PSSQLDEFN.SQLTYPE

0 = SQL Object

1 = App Engine SQL

2 = Record View SQL

5 = Query for DDAUDIT or SYSAUDIT

6 = App Engine XML SQL

File Layout Definitions

PSFLDDEFN

Header record for File Layout

PSFLDSEGDEFN

Stores the segments for each layout

PSFLDFIELDDEFN

Stores the individual file fields for the segment

HTML & Image Meta Data

PSPNLHTMLAREA

Static HTML Areas on Pages with the HTMLValue

PSCONTDEFN

HTML & Image header record; last update time, etc.

PSCONTENT

HTML & Image storage

Menu Meta Data

PSMENUDEFN

Menu header table

PSMENUITEM

Menu Items

Message Catalog

PSMSGSETDEFN

Message Catalog header

PSMSGCATDEFN

Message Catalogs entries
Previous PeopleSoft message catalog tables:
PS_MESSAGE_SET_TBL
PS_MESSAGE_CATALOG

Page Meta Data

PSPNLDEFN

Page header table holding the field count, size, style, and description of the page

PSPNLFIELD

Lists all objects on the page

PSPNLFIELD.FIELDTYPE

0 = Label

1 = Frame

2 = Group Box

3 = Static Image

4 = Edit Box

5 = Drop-Down List Box

6 = Long Edit Box

7 = Check Box

8 = Radio Button

9 = Image

10 = Scroll Bar

11 = Subpage

12 = Push Button/Hyperlink - Destination: PeopleCode Command

13 = Push Button/Hyperlink – Destination: Scroll Action

14 = Push Button/Hyperlink – Destination: Toolbar Action

15 = Push Button/Hyperlink – Destination: External Link

16 = Push Button/Hyperlink – Destination: Internal Link (Transfer)

17 = Push Button/Hyperlink – Destination: Process (AE, etc.)

18 = Secondary Page Button

19 = Grid

20 = Tree

21 = Push Button/Hyperlink - Destination: Secondary Page

22 = N/A

23 = Horizontal Rule

24 = Tab Separator (in a grid)

25 = HTML Area

26 = Push Button/Hyperlink – Destination: Prompt Action

27 = Scroll Area

30 = Chart

31 = Push Button/Hyperlink – Destination: Instant Messaging Action

32 = Analytic Grid

PeopleCode Meta Data

PSPCMPROG

Stores the PeopleCode, LASTUPDOPRID and LASTUPDDTTM. The PeopleCode is stored in a binary format, and cannot be read by normal SQL. 

PSPCMNAME

PeopleCode Reference table. This table lists all the PeopleSoft objects (FIELD, RECORD, SQL, etc.) that are referenced. For example, if you are about to make a change to a field, you can find everywhere in the system that it is referenced by using this table.

Portal (Structure and Content)

PSPRSMATTR

Portal Attribute Table

PSPRSMDEFN

Content References and Folders

PORTAL_PRNTOBJNAME = Parent Folder

PORTAL_OBJNAME     = Content Reference Name

PORTAL_URI_SEG1    = Component Menu

PORTAL_URI_SEG3    = Market

PORTAL_URI_SEG2    = Component

PSPRUHTABPGLT

Portal User HP Tab Pagelet

PSPRSMPERM

Shows the permission lists that are assigned to a portal registry structure (content reference). The permission list name is under field PORTAL_PERMNAME

Process Scheduler Information

Process Scheduler Setup

PS_PRCSDEFN

Process Definition header with descriptions, server options, override options, and destination options

PS_PRCSDEFNGRP

Permission Lists authorized to use this process

PS_PRCSDEFNPNL

Components from which this process can be called

PS_PRCSMUTUALEXCL

Lists processes that cannot run at the same time to prevent data corruption, deadlocks, etc.

PS_PRCSDEFNCNTDIST

List roles or users to distribute process output

PS_PRCSDEFNXFER

List page that user will be sent to following a successful process completion

PS_PRCSDEFNNOTIFY

Process completion notification via email (on Error, Warning, Success)

PS_PRCSDEFNMESSAGE

Message to be sent during notify (from Message Catalog, custom text)

PS_PRCSJOBDEFN

Job header with description and runtime characteristics (run mode, priority, etc.)

PS_PRCSJOBITEM

Processes that will run for each Job

PS_PRCSJOBPNL

Components from which this job can be called.

PS_PRCSJOBCNTDIST

Job output Distribution List via email

PS_PRCSJOBNOTIFY

Job completion notification via email (on Error, Warning, Success)

PS_PRCSJOBMESSAGE

Message to be sent during notify (from Message Catalog, custom text)

Process Scheduler Transaction Records

PSPRCSRQST

Process Request Instance detail

PSPRCSPARMS

Process request parameters

PSPRCSQUE

Process request Queue

PSPRCSRQSTTEXT

Process Request Text

PS_CDM_LIST

Content Distribution Manager List

PS_CDM_AUTH

Content Distribution Manager List – User Access (Who can view output)

Process Scheduler Timings

BAT_TIMINGS_LOG

BAT_TIMINGS_DTL

BAT_TIMINGS_FN

Project Meta Data

PSPROJECTDEFN

Project header table (Short & Long Project Description fields)

PSPROJECTITEM

Objects in the project

PSPROJECTITEM.OBJECTTYPE

0 AND RECTYPE FROM PSRECDEFN WHERE RECNAME = OBJECTVALUE1

      0 = Record

      1 = View

      2 = Work Record

      3 = Sub Record

      5 = Dynamic View

      6 = Query View

      7 = Temporary Table

1 = Index

2 = Field

3 = Field Format

4 = Translate Value

5 = Page

6 = Menu

7 = Component

8 = Record PeopleCode

9 = Menu PeopleCode

10 = Query

11 = Tree Structure

12 = Tree

13 = Access Group

14 = Color

15 = Style

16 = Business Process Map

17 = Business Process

18 = Activity

19 = Role

20 = Process Definition

21 = Process Server Definition

22 = Process Type Definition

23 = Process Job Definition

24 = Process Recurrence Definition

25 = Message Catalog

26 = Dimension

27 = Cube Definition

28 = Cube Instance Definition

29 = Business Interlink

30 AND WHEN OBJECTVALUE2 = 0 THEN SQL Object

       WHEN OBJECTVALUE2 = 1 THEN App Engine SQL

       WHEN OBJECTVALUE2 = 2 THEN Record View SQL

       WHEN OBJECTVALUE2 = 5 THEN Query for DDAUDIT or SYSAUDIT

       WHEN OBJECTVALUE2 = 6 THEN App Engine XML SQL

31 = File Layout

32 = Component Interface

33 = Application Engine Program

34 = Application Engine Section

35 = Message Node

36 = Message Channel

37 = Message

38 = Approval rule set

39 = Message PeopleCode

40 = Subscription PeopleCode

41 = N/A

42 = Component Interface PeopleCode

43 = Application Engine PeopleCode

44 = Page PeopleCode

45 = Page Field PeopleCode

46 = Component PeopleCode

47 = Component Record PeopleCode

48 = Component Record Field PeopleCode

49 = Image

50 = Style sheet

51 = HTML

52 = Not used

53 = Permission List

54 = Portal Registry Definitions

55 = Portal Registry Structure

56 = URL Definitions

57 = Application Packages

58 = Application Package PeopleCode

59 = Portal Registry User Homepage

60 = Problem Type

61 = Archive Templates

62 = XSLT

63 = Portal Registry User Favorite

64 = Mobile Page

65 = Relationships

66 = Component Interface Property PeopleCode

67 = Optimization Models

68 = File References

69 = File Type Codes

70 = Archive Object Definitions

71 = Archive Templates - Type 2

72 = Diagnostic Plug In

73 = Analytic Model

79 = Service

80 = Service Operation

81 = Service Operation Handler

82 = Service Operation Ver

83 = Service Operation Routing

84 = Info Broker Queues

85 = XLMP Template Definition

86 = XLMP Report Definition

87 = XMLP File Definition

88 = XMLP Data Source Definition

Query Tables

PSQRYDEFN

Query header information

PSQRYFIELD

Displays all fields used in the SELECT clause (COLUMNNUM = 1) and fields used in the WHERE clause (COLUMNNUM = 0)

PSQRYCRITERIA

Displays all fields used in the WHERE clause. You can get the name of the fields by joining PSQRYCRITERIA.LCRTFLDNUM to PSQRYFIELD.FLDNUM

PSQRYEXPR

Stores query expressions (PSQRYCRITERIA.R1CRTEXPNUM to PSQRYEXPR.EXPNUM or PSQRYFIELD.FLDEXPNUM to PSQRYEXPR.EXPNUM)

PSQRYBIND

Stores query bind variable definition

PSQRYRECORD

Stores all records used in all aspects of query creation (SELNUM > 1 when in a subquery)

PSQRYSELECT

Stores query and subquery relationships along with record and field counts

PSQRYEXECLOG

Query run time log table that stores (only 8.4x and higher)

PSQRYSTATS

Query run time statistics table such as count of query execution, and date time of last execution (only in 8.4x and higher).

Record Meta Data

PSRECDEFN

Record header table. Tracks number of fields and number of indexes in record along with descriptions

PSRECDEFN.RECTYPE

0 = SQL Table in DB

1 = SQL View in DB

2 = Derived/Work Record

3 = Sub Record

5 = Dynamic View

6 = Query View

7 = Temporary Table

PSRECFIELD

Fields in the record (subrecord fields are not listed) along with field order, field defaults, edit tables

PSRECFIELDALL

All fields in the record, including subrecord fields

PSINDEXDEFN

Contains 1 row per index defined for a record

PSKEYDEFN

Contains all fields that make up the index, and their position in the key structure

PSTBLSPCCAT

Lists available tablespace

PSRECTBLSPC

DB Name and tablespace allocated for a SQL record

Security Information

PSAUTHITEM

What Permission Lists have access to a page, and what are authorized actions?

SELECT CLASSID, MENUNAME, BARNAME, BARITEMNAME, PNLITEMNAME, DECODE(DISPLAYONLY, 0, 'N', 1, 'Y') AS "Display Only",

CASE AUTHORIZEDACTIONS

    WHEN 1 THEN 'Add'

    WHEN 2 THEN 'Update/Display'

    WHEN 3 THEN 'Add, Update/Display'

    WHEN 4 THEN 'Update/Display All'

    WHEN 5 THEN 'Add, Update/Display All'

    WHEN 6 THEN 'Update/Display, Update/Display All'

    WHEN 7 THEN 'Add, Update/Display, Update/Display All'

    WHEN 8 THEN 'Correction'

    WHEN 9 THEN 'Add, Correction'

    WHEN 10 THEN 'Update/Display, Correction'

    WHEN 11 THEN 'Add, Update/Display, Correction'

    WHEN 12 THEN 'Update/Display All, Correction'

    WHEN 13 THEN 'Add, Update/Display All, Correction'

    WHEN 14 THEN 'Update/Display, Update/Display All, Correction'

    WHEN 15 THEN 'Add, Update/Display, Update/Display All, Correction'

    ELSE 'SPECIAL' END AS "Authorized Actions",

AUTHORIZEDACTIONS

FROM PSAUTHITEM

PSAUTHBUSCOMP

What Permission List has access to a component interface?

SELECT CLASSID FROM PSAUTHBUSCOMP WHERE BCNAME = 'MY_COMPONENT_INTERFACE'

PSCLASSDEFN

Permission List header table

PSPRSMPERM

Portal Structure Permissions

PSROLECLASS

Permission Lists in roles

PSROLEDEFN

Role header table

SQL Definitions

PSSQLDEFN

Header record for all SQL from views and application engine

PSSQLTEXTDEFN

Stores the SQL definition

PSSQLDESCR

Stores SQL objects descriptions, market, DB Type, and EFFDT

Tree Manager

PSTREEDEFN

Tree Definition and Properties

PSTREENODE

Folders and records (nodes of the tree/tree node type)

PSTREEBRANCH

Tree Branch

PSTREELEAF

Tree Leaf

PSTREELEVEL

Tree Level

User Profile & Security

PSOPRDEFN

User ID header table: User Name, email, Primary & Row security permission lists

PS_ROLEXLATOPR

Workflow Routing Preferences; email; workflow attributes

PSUSEREMAIL

Users email

PSROLEUSER

OPRID (Roleuser) and Roles granted

PSOPRCLS

OPRID and associated Permission lists

Workflow

Meta Data

PSBUSPROCDEFN

Business Process Header

PSACTIVITYDEFN

Activity Header

PSBUSPROCITEM

Activity items in each activity

PSEVENTDEFN

Event items in each activity

PS_APPR_RULE_DETL

Approval rule definition details

PS_APPR_RULE_FIELD

Approval rule definition route control

PS_APPR_RULE_AMT

Approval rule amounts

PS_RTE_CNTL_LN

Route control profile line

PS_RTE_CNTL_RUSER

RoleUser route control profiles

PS_RTE_CNTL_TYPE

Route control type

PS_RTE_CNTL_HDR

Routing control type header

Transaction Record

PSWORKLIST

Lists worklist entries by event and OPRID

XLAT – Translate Values

PSXLATITEM

Stores field translate values (PeopleSoft version 8.4 and above)

XLATTABLE

Stores field translate values (PeopleSoft version prior to 8.4)

Share and Enjoy: