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;

Schedule Application Engine through Peoplecode

The following sample code can be used to Schedule App Engine through PeopleCode.

Code to Schedule App Engine thru PeopleCode:

Local ProcessRequest &MYRQST;
   
   &MyAppName = "MY_APP";
   &MYRQST = CreateProcessRequest("Application Engine", &MyAppName);
   &MYRQST.RunControlID = "TEST";
   &MYRQST.RunLocation = "PSUNX";       
   &MYRQST.Schedule();
   
   If &MYRQST.Status = 0 Then /* if Schedule status is success */
 
  End-If;

Code to know whether the scheduled App Engine ran to success or not:

If &MYRQST.Status = 0 Then /* if Schedule status is success */

      &LOOP = 0;
      While &LOOP = 0
         SQLExec("SELECT A.DISTSTATUS, A.RUNSTATUSDESCR FROM PS_PMN_PRCSLIST A WHERE A.PRCSNAME = :1 AND A.PRCSINSTANCE = (SELECT MAX(B.PRCSINSTANCE) FROM PS_PMN_PRCSLIST B WHERE B.PRCSNAME = A.PRCSNAME)", &MyAppName, &POSTED, &STATUS);
         
         If &STATUS = "Success" And
               &POSTED = 5 Then /* Posted */
            &LOOP = 1
         End-If;
         
         If &STATUS = "Success" And /* Not Posted */
               &POSTED = 4 Then
            &LOOP = 2
         End-If;
         
         If &STATUS = "No Success" Or
               &STATUS = "Error" Then
            &LOOP = 3;
         End-If;
         
      End-While;
      
      If &LOOP = 1 Then
            MessageBox(0, "", 27333, 594, "");  /* Success Msg */
      Else
         If &LOOP = 2 Or
               &LOOP = 3 Then
            MessageBox(0, "", 27333, 595, "");  /* Failed Error Msg */
         End-If;
      End-If; /* If &LOOP = 1 Then */ 
End-If;

The below function can also be used for schedule application engine: (Alternative Function)

Function ScheduleSetupAEProcess(&RESULT_SET_COMP)
   &RUN_SETUP_AE_REQST = CreateProcessRequest("Application Engine", "PTLT_SETUP");
   &REC_IMPL_RUN = CreateRecord(Record.PTLT_PROJ_RUN);
   
   /* Run Control ID = Configuration Set Name */
   &RUN_CNTL_ID = &RESULT_SET_COMP;
   
   &RUN_SETUP_AE_REQST.RunControlID = &RUN_CNTL_ID;
   &RUN_SETUP_AE_REQST.Schedule();
   
   If &RUN_SETUP_AE_REQST.Status = 0 Then
      &PROCESS_INSTANCE = &RUN_SETUP_AE_REQST.ProcessInstance;
      /* Insert into Setup Manager Run Control Record */
      &REC_IMPL_RUN.OPRID.Value = %OperatorId;
      &REC_IMPL_RUN.RUN_CNTL_ID.Value = &RUN_CNTL_ID;
      &REC_IMPL_RUN.PTLT_PROJ_NAME.Value = &RESULT_SET_COMP;
      &REC_IMPL_RUN.PROCESSINSTANCE.Value = &PROCESS_INSTANCE;
      
      If Not &REC_IMPL_RUN.Update() Then
         &REC_IMPL_RUN.Insert();
      End-If;
   Else
      Error MsgGet(218, 154, "Process cannot be scheduled");
   End-If;
End-Function;

Object–Based PeopleCode

 

What is Object – Based PeopleCode:

• Introduced in PeopleTools 8
• Evolution of procedural-based PeopleCode
• Provides for complex object definitions
• Overcomes procedural data scope limitations
• Similar to Visual Basic notation
• Interpreted (like VB)
• Not completely object oriented (like C++), but it’s getting there!
• Does not support polymorphisms
• Does support multiple inheritance
• Backwards compatible

Why do we need Object Based PeopleCode:

• Greater ability to write generic code
• Release 8 Integration
                       1. Component Interface
                       2. Application Messaging
                       3. Business Interlinks
• Ability to define custom classes (Release 8.4)
                       1. Application Classes/Packages
                       2. Replaces FUNCLIB Processing

Advantage of Object and Methods:

• Tight code
• Syntax is validated*
• No or low maintenance required

Object – Based PeopleCode Rule:

• DECLARE the object
• INSTANTIATE the object

Object–Based Code to avoid use of SQLEXEC

 

Below code can be used to avoid use of SQLExec.

SQLExec Code:

SQLExec to copy a row from ORD_HDR to ORD_HDR_EXT.

&ORD_NO = &NEW_ORD_NO;

SQLExec("select %timeout(order_dt), training_loc, vendor_cd, order_status, status_dt, deliver_method from ps_ord_hdr
where order_nbr = :1", &ORD_NO, &ORD_DT, &TRAIN_LOC,&VEND_CD, &ORD_STAT, &STAT_DT, &SHIP_VIA);

SQLExec("delete from ps_ord_hdr_ext where order_nbr = :1", &ORD_NO);

SQLExec("insert into ps_ord_hdr_ext (order_nbr, order_dt,training_loc, vendor_cd, order_status, status_dt,
deliver_method) values(:1,%datein(:2),:3,:4,:5,:6,:7)",&ORD_NO, &ORD_DT, &TRAIN_LOC, &VEND_CD, &ORD_STAT, &STAT_DT,&SHIP_VIA);

Same logic using Object Based code:

Local Record &REC1, &REC2;
&REC1 = GetRecord(RECORD.ORD_HDR);
&REC2 = CreateRecord(RECORD.ORD_HDR_EXT);
&REC1.ORD_NO = &NEW_ORD_NO;
&REC1.SelectByKey();
&REC1.CopyFieldsTo(&REC2);
&REC2.Delete();
&REC2.Insert();