Ask Question
Help Others
Home

SQL

Integration Broker

Thu, 07/17/2014 - 10:03pm — Lepa

PeopleSoft Integration Broker related articles, setups, tips and tricks.

Count Occurrence of a Character in a String

Sun, 03/20/2011 - 9:39pm — Lepa

I had the need to count an occurrence of a specific character in a string and after tossing some ideas back and forth between a couple of my colleges, we came up with the below two SQLs. You pass a string and identify the character you are looking to count its occurrence. In both SQLs below, I'm trying to count how many times does ">>" exists in string "This is>>a string>>to look into>>". The answer of course is 3 times.

SQL Option 1:

SELECT COUNT (*)
 FROM (SELECT SUBSTR ('This is>>a string>>to look into>>', ROWNUM, 2 ) s,
              ROWNUM pos

Get Edit Tables Behind a Record Fields

Sun, 12/14/2008 - 4:05pm — Lepa

A straight forward SQL to get edit tables behind field(s) for a specific record. The SQL execludes any edit tables that start with "%" as those are dynamic edit tables with values populated by PeopleCode at run time and thus will not be of a good use in this query.

SELECT R.FIELDNAME 
 , R.EDITTABLE 
  FROM PSRECFIELDDB R 
  , PSDBFIELD F 
 WHERE R.RECNAME = :RecordName
   AND SUBSTR(R.EDITTABLE,1,1) <> '%'  
   AND R.EDITTABLE <> ' '  
   AND R.FIELDNAME = F.FIELDNAME
   AND F.FLDNOTUSED = 0;

A List of Pages a Peoplesoft Role Name can Access

Tue, 12/09/2008 - 5:19pm — Lepa

A query that will take a PeopleSoft role name as an input and returns all pages that could be access by that role. The query will also indicate what kind of operations a user can perform on that page. Example, Add Update/Display and so forth.

SELECT   b.menuname, b.barname, b.baritemname, d.pnlname, c.pageaccessdescr
    FROM psroleclass a,
         psauthitem b,
         pspgeaccessdesc c,
         pspnlgroup d,
         psmenuitem e
   WHERE a.classid = b.classid
     AND d.pnlgrpname = e.pnlgrpname
     AND b.menuname = e.menuname

What Permission Lists does this Role Have?

Tue, 12/09/2008 - 4:51pm — Lepa

A query that takes a PeopleSoft role name as a value and returns Permission List(s) associated to that role.

SELECT a.rolename, a.classid AS permission_list
  FROM psroleclass a
 WHERE a.rolename = :rolename;

PeopleSoft Role Queries

Tue, 12/09/2008 - 4:44pm — Lepa

A section to collect and organize PeopleSoft Role related queries.

Content References Accessed by a Permission List

Wed, 10/01/2008 - 2:21pm — Lepa

Another permission list query to list Content References accessed by a specific 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

PeopleTools Objects Accessed by a Permission List

Sun, 09/28/2008 - 6:13pm — Lepa

A query that will list all peopletools objects (Query, Application Designer, Data Mover) that a specific permission list could access.

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'
                )

Pages Accessed by a Permission List

Sun, 09/28/2008 - 5:59pm — Lepa

A query to identify pages that could be accessed by a specific 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;

Roles Assigned to a Permission List

Thu, 09/18/2008 - 10:00pm — Lepa

A query to help you identify Roles that are assigned to a specific permission list.

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