PeopleSoft related articles.

Dynamic Prompt Table Depending on a Drop Down Value

In this post I will attempt to explain how to dynamically assign a prompt table depending on a drop down value (see image below)

The table behind the grid is PORTAL_SECDYVW and as you can see from the image below, the PORTAL_AUTHNAME field has %EDITTABLE defined as a prompt table. The PORTAL_AUTHNAME is the "Name" column you see on the grid.

Delete All Grid Rows

A very simple yet very useful code to give users the option to delete all grid rows on a page at once. I've implemented such a requirement by placing a "Delete all" button above a grid to give PeopleSoft users the option to delete all rows at once instead of clicking the "-" grid button. If the gird has, lets say, 20 rows, then a user will have to click the "-" delete grid button 20 times to delete all rows, compared to one click on the "Delete all" button.

Place the code below behind a field change event.

   &RECCNT = ActiveRowCount(Record.record_name_behind_grid);

Get Edit Tables Behind a Record Fields

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.

 WHERE R.RECNAME = :RecordName
   AND SUBSTR(R.EDITTABLE,1,1) <> '%'  
   AND R.EDITTABLE <> ' '  

A List of Pages a Peoplesoft Role Name can Access

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?

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

A section to collect and organize PeopleSoft Role related queries.

Leading Zero Issue in CSV/Excel Formatterd Report

If you use APP ENGINE to report the data from the temp table /staging table on to CSV file using excel format, there is a known issue with fields that have leading zeros. For ex:You have a field of length 6 characters. If the field value is 002123, the excel file removes the leading zeros. But if the requirement is to preserve those leading zeros, we can do something like this.
Update the temp table right before you write out into a file.

Update %Table(Temp_Stg) A
Set A.Field = (Select '"='||A1.Field||'"' from  %Table(Temp_Stg) A1 where A1.Keyfield = A.Keyfield)

Function to Increment a Date Field

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.

   If All(&DATE) Then
      &DATE = AddToDate(&DATE, &YEAR_INC, &MONTH_INC, &DAY_INC);
      /*get day value for the incremented date*/
      &WEEKDAY = String(Weekday(&DATE));

Function to Add Leading Characters to a Value or Field

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.


Convert a String Representing a Number to a Number



Use the Value function to convert a string representing a number to the number.

str: value to be passed to the function.

The example sets &VAL1 to 5.25 and &VAL2 to 500:
&VAL1 = Value("5.25");
&VAL2 = Value("500");

Check out the following post to convert a number variable type to a string type.