Ask Question
Help Others
Home › Blogs

Lepa's blog

Function to Increment a Date Field

Sun, 11/02/2008 - 9:40pm — Lepa

The function increments a date field using the AddToDate function and sets a weekday field to the corresponding weekday. You can increment date value by year, month or day.

Function increment_date(&DATE, &WEEKDAY, &YEAR_INCREMENT, &MONTH_INCREMENT, &DAY_INCREMENT);
   If All(&DATE) Then
      &DATE = AddToDate(&DATE, &YEAR_INC, &MONTH_INC, &DAY_INC);
      /*get day value for the incremented date*/
      &WEEKDAY = String(Weekday(&DATE));
   End-If;
End-Function;

Function to Add Leading Characters to a Value or Field

Mon, 10/27/2008 - 7:11pm — Lepa

A function to facilitate the addition of a leading character to a field value. The function takes three parameters:
- &LEADCHAR: The character you want to append to your value.
- &FIELDZISE: The over all size of your value after appending your desired character.
- &FIELDVALUE: The value you want to append the character to.

Function add_leading_char(&LEADCHAR, &FIELDSIZE, &FIELDVALUE, &RESULTFIELD);

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

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,

List Dates in a month

Fri, 08/22/2008 - 8:35am — Lepa

Here is an SQL that will list all dates for a particular month.

SELECT       TRUNC (TO_DATE (:target_date, 'MM/DD/YYYY'),
                    'MM'
                   )
           + LEVEL
           - 1 AS days_in_month
      FROM DUAL
CONNECT BY LEVEL <=
              EXTRACT (DAY FROM LAST_DAY (TRUNC (TO_DATE (:target_date,
                                                          'MM/DD/YYYY'
                                                         ),
                                                 'MM'
                                                )