Ask Question
Help Others
Home

SQL

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;

Identify Records with a Specific Subrecord

Wed, 09/17/2008 - 9:08am — Lepa

A subrecord in PeopleSoft is a way to group fields together and place them on a record, view, or a temp table. Here is a query you can use to identify records that have a specific subrecord.

SELECT a.recname, a.fieldname, b.rectype, b.objectownerid
  FROM psrecfield a, psrecdefn b
 WHERE a.recname = b.recname
   AND a.fieldname = 'SUB-RECORD-NAME';

PeopleSoft Permission List Queries

Wed, 09/17/2008 - 8:46am — Lepa

A section to collect and organize PeopleSoft permission lists related queries.

User IDs assigned to a Permission List

Tue, 09/16/2008 - 1:45pm — Lepa

A query to list all user IDs that are assigned to a specific 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;

Database Table Space Statistics (Free, Used, and Percent Used)

Wed, 09/03/2008 - 2:18pm — Lepa

An SQL to help gathering storage tablespace statistics. The SQL will list all tablespace names in a particular database along with available, used and percent used storage space in MB.

SELECT   a.tablespace_name, (a.BYTES / 1024) || 'MB' AS used,
         (b.BYTES / 1024) || 'MB' AS free,
         (b.largest / 1024) || 'MB' AS largest_block,
         ROUND (((a.BYTES - b.BYTES) / a.BYTES) * 100, 2) AS percent_used
    FROM (SELECT   tablespace_name, SUM (BYTES) BYTES
              FROM dba_data_files
          GROUP BY tablespace_name) a,

Oracle Database Version

Wed, 09/03/2008 - 2:07pm — Lepa

A Very simple SQL to return Oracle's database version you are currently in.

SELECT *
  FROM v$version
 WHERE ROWNUM = 1;

BANNER
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production

PeopleCode to AutoIncrement an AlphaNumeric Transaction Number

Tue, 08/26/2008 - 1:12pm — Larry

I have code to auto-increment an alphanumeric field, using SQL calls. It is specific to a 4-character field, but can easily be adapted to different length fields.

SQLExec("SELECT decode(substr(oldvalue,2,3),'ZZZ', TRANSLATE(UPPER(SUBSTR(oldvalue,1,1)),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0'),substr(oldvalue,1,1)) ||decode(substr(oldvalue,3,2),'ZZ',TRANSLATE(UPPER(SUBSTR(oldvalue,2,1)), '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0'),substr(oldvalue,2,1))||decode(substr(oldvalue,4,1),'Z', TRANSLATE(UPPER(SUBSTR(oldvalue,3,1)), '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0'),substr(oldvalue,3,1))||TRANSLATE(UPPER(SUBSTR(oldvalue,4,1)), '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0') FROM dual, (select :1 as oldvalue from dual)", &priorvalue, &newvalue);
If &newvalue = "0000" Then
   &bOverrun_Error = True;
End-If;