PeopleSoft Permission List Queries

1. Component Permission List Query:
This query identify the permission lists and its description associated with component.

SELECT   menu.menuname, compdfn.pnlgrpname, auth.classid permission_list,
CLASS.classdefndesc permission_desc
FROM psauthitem auth,
psmenudefn menu,
psmenuitem menuitm,
pspnlgroup comp,
pspnlgrpdefn compdfn,
psclassdefn CLASS
WHERE menu.menuname = menuitm.menuname
AND menuitm.pnlgrpname = comp.pnlgrpname
AND compdfn.pnlgrpname = comp.pnlgrpname
AND compdfn.pnlgrpname LIKE UPPER (:component_name)
AND auth.menuname = menu.menuname
AND auth.barname = menuitm.barname
AND auth.baritemname = menuitm.itemname
AND auth.pnlitemname = comp.itemname
AND auth.classid = CLASS.classid
GROUP BY menu.menuname, compdfn.pnlgrpname, auth.classid, CLASS.classdefndesc
ORDER BY menu.menuname, compdfn.pnlgrpname, permission_list;

2. Content Reference accessed by a permission list:

This query identifies Content references accessed by Permission List.

SELECT   a.portal_label AS PORTAL_LINK_NAME, a.portal_objname, a.portal_name, a.portal_reftype
FROM psprsmdefn a, psprsmperm b, psclassdefn c
WHERE a.portal_reftype = 'C'
AND a.portal_cref_usgt = 'TARG'
AND a.portal_name = b.portal_name
AND a.portal_reftype = b.portal_reftype
AND a.portal_objname = b.portal_objname
AND c.classid = b.portal_permname
AND a.portal_uri_seg1 <> ' '
AND a.portal_uri_seg2 <> ' '
AND a.portal_uri_seg3 <> ' '
AND c.classid = :permissionlist
AND a.portal_name = :portalname
ORDER BY portal_label;

FRMT = Frame Template

HPGC = Pagelet

HPGT = Homepage Tab

HTMT = HTML template

LINK = Content Reference Link

3. Page Access By Permission List:

SELECT b.menuname, b.barname, b.baritemname, b.pnlitemname AS pagename,
       c.pageaccessdescr,
       DECODE (b.displayonly, 0, 'No', 1, 'Yes') AS displayonly
  FROM psclassdefn a, psauthitem b, pspgeaccessdesc c
WHERE a.classid = b.classid
   AND a.classid = :1
   AND b.baritemname > ' '
   AND b.authorizedactions = c.authorizedactions;

4. PeopleTools Accessed By a Permission List:

SELECT DISTINCT b.menuname
FROM psclassdefn a, psauthitem b
WHERE a.classid = b.classid
AND (   b.menuname = 'CLIENTPROCESS'
OR b.menuname = 'DATA_MOVER'
OR b.menuname = 'IMPORT_MANAGER'
OR b.menuname = 'APPLICATION_DESIGNER'
OR b.menuname = 'OBJECT_SECURITY'
OR b.menuname = 'QUERY'
)
AND a.classid = :PermissionList;

5. Roles Assigned to a Permission List:

SELECT b.rolename, b.classid AS permission_list
FROM psclassdefn a, psroleclass b
WHERE a.classid = b.classid AND a.classid = :permissionlist;

6. User IDs assigned to a Permission List:

SELECT   c.roleuser AS USER_IDs
FROM psclassdefn a, psroleclass b, psroleuser c
WHERE a.classid = b.classid
AND b.rolename = c.rolename
AND a.classid = :permissionlist
GROUP BY c.roleuser;

PeopleCode Tricks

Here are a few quick and easy examples of often used PeopleCode. This quick reference is helpful because some of this syntax can be easily forgotten.

Application Package

12345

import C_APP_PACK:FooClass; Local C_APP_PACK:FooClass &foo; &foo = create C_APP_PACK:FooClass(); &result = &foo.ExampleMethod(&bar);

view rawApplication Package [peoplecode] hosted with ❤ by GitHub

Function

1	Declare Function MY_FUNC PeopleCode MY_FUNC.FIELD FieldFormula;

view rawFunction [peoplecode] hosted with ❤ by GitHub

SQL Class

123456

Local Record &rRecord = CreateRecord(Record.RECORD); Local SQL &SQL = CreateSQL("SELECT * FROM PS_RECORD"); While &SQL.Fetch(&rRecord) /**/ End-While;

Test Score Post in Campus Solutions

Test Id Security. Unless you set the test id security for a user you can not load or post the test scores from a flat file.

Here's the location to set that.

Navigation : Root --> Set Up SACR --> Security --> Secure Student Administration --> User ID --> Test ID Security.


Code extending the SOA framework to chunck a container based message when the message size exceeds Integration Broker max message size limit.

import SAD_CRM_INTEGRATION:SAD_TEST_POST_CRM:SAD_TEST_POST_CRM_AP;

Component Rowset &rsSadHeaderCrm, &rsSadApSus, &rsSccNameValue;
Component SAD_CRM_INTEGRATION:SAD_TEST_POST_CRM:SAD_TEST_POST_CRM_AP &oSadTestPostCRMAP;

Local number &i, &j;
Local Message &response;

&oSadTestPostCRMAP = create SAD_CRM_INTEGRATION:SAD_TEST_POST_CRM:SAD_TEST_POST_CRM_AP( Null, "SAD", "SAD_TEST_POST_CRM_AP");

&oSadTestPostCRMAP.rsSAD_HEADER_CRM = &rsSadHeaderCrm;
&oSadTestPostCRMAP.rsSAD_TEST_POST_AP_PART = &rsSadApSus;
&oSadTestPostCRMAP.rsSAD_TEST_POST_NAME_VAL_PART = &rsSccNameValue;
&response = &oSadTestPostCRMAP.execute();

If &oSadTestPostCRMAP.moduleError = Null Then
Exit (1); /* exit (1) to skip roll back action */
Else
MessageBox(%MsgStyle_OK, "", 99999, 999, "*** Post to CRM Integration Error ***");
MessageBox(%MsgStyle_OK, "", 99999, 999, &oSadTestPostCRMAP.moduleError.ToString( False));
If (&oSadTestPostCRMAP.moduleError.MessageSetNumber = 14099 And
&oSadTestPostCRMAP.moduleError.MessageNumber = 7) Then
MessageBox(%MsgStyle_OK, "", 99999, 999, "Attempting Secondary Burst of 100 transactions");


Local Rowset &rs100SadHeaderCrm, &rs100SadApSus, &rs100SadBioPrsSus, &rsSadBioPrsSus;
Local number &numberOfRowSet;

&rs100SadHeaderCrm = CreateRowset(&rsSadHeaderCrm);
&rs100SadApSus = CreateRowset(&rsSadApSus);

&rsSadHeaderCrm(1).CopyTo(&rs100SadHeaderCrm(1));

&rsSadBioPrsSus = &rsSadHeaderCrm(1).GetRowset(Scroll.SAD_BIO_PRS_SUS);
&rs100SadBioPrsSus = &rs100SadHeaderCrm(1).GetRowset(Scroll.SAD_BIO_PRS_SUS);


&i = 1;
While &i <= &rsSadBioPrsSus.ActiveRowCount
&j = 1;
&rs100SadBioPrsSus.Flush();
&rs100SadApSus.Flush();

For &j = 1 To 2

If &i > &rsSadBioPrsSus.ActiveRowCount Then
Break;
End-If;

If &j <> 1 Then
&rs100SadBioPrsSus.InsertRow(&rs100SadBioPrsSus.ActiveRowCount);
&rs100SadApSus.InsertRow(&rs100SadApSus.ActiveRowCount);
End-If;

&rsSadBioPrsSus(&i).CopyTo(&rs100SadBioPrsSus(&rs100SadBioPrsSus.ActiveRowCount));
&numberOfRowSet = 1;
While &numberOfRowSet <= &rsSadBioPrsSus(&i).ChildCount
&rsSadBioPrsSus(&i).GetRowset(&numberOfRowSet).CopyTo(&rs100SadBioPrsSus(&rs100SadBioPrsSus.ActiveRowCount).GetRowset(&numberOfRowSet));
&numberOfRowSet = &numberOfRowSet + 1;
End-While;

&rsSadApSus(&i).CopyTo(&rs100SadApSus(&rs100SadApSus.ActiveRowCount));
&numberOfRowSet = 1;
While &numberOfRowSet <= &rsSadApSus(&i).ChildCount
&rsSadApSus(&i).GetRowset(&numberOfRowSet).CopyTo(&rs100SadApSus(&rs100SadApSus.ActiveRowCount).GetRowset(&numberOfRowSet));
&numberOfRowSet = &numberOfRowSet + 1;
End-While;

&i = &i + 1;

End-For;

&oSadTestPostCRMAP.rsSAD_HEADER_CRM = &rs100SadHeaderCrm;
&oSadTestPostCRMAP.rsSAD_TEST_POST_AP_PART = &rs100SadApSus;
&oSadTestPostCRMAP.rsSAD_TEST_POST_NAME_VAL_PART = &rsSccNameValue;
&oSadTestPostCRMAP.moduleError = Null;
&response = &oSadTestPostCRMAP.execute();


If &oSadTestPostCRMAP.moduleError <> Null And
(&oSadTestPostCRMAP.moduleError.MessageSetNumber = 14099 And
&oSadTestPostCRMAP.moduleError.MessageNumber = 7) Then

MessageBox(%MsgStyle_OK, "", 99999, 999, "*** Secondary Burst too large ***");
Exit (0); /* Return 0 to call Rollback Action */

End-If;

End-While;
End-If;

End-If;

Process Definition File Dependency

 In PeopleSoft process scheduler we have setup some Process Definitions with a File Dependency turned on. What this does is Block a process from running until a file is found. This is setup in the Process Definition on the Process Definition Options tab under the On File Creation section.

Well today we updated the Wait for File path. We rescheduled our processes but in the Parameters it still had the old file path. After some head scratching I looked in PeopleBooks where it says this file path can be set at runtime. So I looked and sure enough this value is stored on each Run Control Id. It will only pull from the Process Definition if it is a new Run Control Id or if the filepath is blank.

Here is some SQL to help with this situation

12345

/* Process Sched Parameters */ SELECT prcsinstance, prcsfilename FROM psprcsparms ORDER BY prcsinstance DESC

view raw

 

12345

/* Stored at Process Defn Level */ SELECT prcsname, prcsfilename FROM ps_prcsdefn WHERE prcsname LIKE 'TEST123'

view raw

 

12345

/* Stored at Run Control Level */ SELECT a.prcsfilename, a.* FROM ps_prcsruncntldtl a WHERE a.prcsname LIKE 'TEST123'

view raw

123456789101112

/* Update Run Control to match current Process Definition */ UPDATE ps_prcsruncntldtl a SET a.prcsfilename = (SELECT c.prcsfilename FROM ps_prcsdefn c WHERE a.prcstype = c.prcstype AND a.prcsname = c.prcsname) WHERE a.prcstype = 'Application Engine' AND a.prcsname = 'TEST123' AND EXISTS (SELECT 'X' FROM ps_prcsdefn b WHERE a.prcstype = b.prcstype AND a.prcsname = b.prcsname)

view raw

Important PeopleSoft SQLs

• MicroSsoft SQL Server

Top few rows from a table, following will select
first 301 rows from a table.

SELECT
DISTINCT TOP 301 RUN_CNTL_ID,
LANGUAGE_CD
FROM
PS_PRCSRUNCNTL
WHERE
OPRID='PS'
AND
UPPER(RUN_CNTL_ID)
LIKE UPPER('%act')

• Oracle

Escape characters used in data.

Select * From STUDENTS Where STU_NAME Like '%\_%' Escape '\'

• Delete duplicate rows
  
  Delete From CLIENT_MASTER
  Where ROWID NOT IN
  ( Select min (ROWID)
  From CLIENT_MASTER
  Group By CLIENT_NO, NAME, BAL_DUE )

• In Oracle/PLSQL, the NVL function lets you substitute a value when a null value is encountered.

Select ID, FIRST_NAME, LAST_NAME, NVL(PHONE, 'Unknown Phone Number') As Phone From CUSTOMER

DMS - Data Mover Scripts

DMS to Export PeopleSoft Project Message Catalog Entries
-
Export PSMSGCATDEFN Where MESSAGE_SET_NBR=14950 And MESSAGE_NBR IN (55, 56, 57, 58, 59, 60, 61, 64, 65, 66, 67);
Export PSMSGCATDEFN Where MESSAGE_SET_NBR=14951 And MESSAGE_NBR IN 138, 139, 27, 28, 29, 30, 63, 64);

We moved to following DMS to export all the message catalog entries inserted in the project definition. And it doesn’t have to change every time you create and insert new entries in the project definition.

Similar DMS is used to pick the master project catalog entries from bundle project, to which all the teams in campus solutions merge their project to.

EXPORT PSMSGCATDEFN
WHERE EXISTS
(
SELECT 'X' FROM PSPROJECTITEM B
WHERE B.PROJECTNAME = '<< insert your project name >>'
AND OBJECTTYPE = 25
AND OBJECTVALUE1 = MESSAGE_SET_NBR
AND OBJECTVALUE2 = MESSAGE_NBR
);

-
-
DMS to remove past and future dated rows from a effective dated table
-

Following gave SQL Validator issues stating that table name alias can NOT be used in a delete / update SQL in a DMS script.
Following is the DMS scripts which leads to SQL Validator issue
-- Removing past and future dated rows from a effective dated table

DELETE FROM PS_TABLE_NAME TABLE_ALIAS1
WHERE EXISTS(
SELECT 'X' FROM PS_TABLE_NAME TABLE_ALIAS2
WHERE TABLE_ALIAS1 .FIELD1 = TABLE_ALIAS2.FIELD1
AND TABLE_ALIAS1 .FIELD2 = TABLE_ALIAS2.FIELD2
AND %EffdtCheck(TABLE_NAME, TABLE_ALIAS2, %CurrentDateTimeIn)
AND TABLE_ALIAS1.EFFDT <> TABLE_ALIAS2.EFFDT
);

And I used the following to resolve that issue, the crux is to hold the required data in a .dat file, delete the complete table and re-populate the table with the data in the .dat file. So both export and import happening in the same DMS import script we submit to bundle.

-- Removing past and future dated rows from a effective dated table
SET OUTPUT updXXXXXX_XX.dat;
EXPORT TABLE_NAME WHERE %EffdtCheck(TABLE_NAME, PS_TABLE_NAME, %CurrentDateIn);
DELETE FROM PS_SAE_CRSCTLG_EXT;
SET INPUT updXXXXXX_XX.dat;
IMPORT TABLE_NAME;

"You are not authorized for this page" error on Worklist link

Within the New PeopleSoft Approval Workflow setup, Worklist notifications was tested. The Worklist entries were generating just fine for all users. However, some users were not able to successfully click on the transaction link. When clicking on the link, they were receiving an error message "You are not authorized for this page". Confirmed that the user did have access to the transaction Page and Component by navigating to it through the Portal menu.

Issue was the user needed access to:

• Menu: EOAW_APPROVAL_WORKFLOW
• Component: EOAW_REDIRECT
• Page: EOAW_REDIRECT

To resolve this, Create a new Permission List with this access and gave it to a Role assigned to the users. This should fix the issue.