PeopleCode

AddToDate

AddToDate is a PeopleCode built-in function for manipulating a date in PeopleCode.

You can use it to adjust dates forwards and backwards, by a given number of years, months or days.

The basic syntax is:

Local date &dtExample;
&dtExample = %Date;
   
/* Get date forward 1 year, 0 months, 0 days */
&dtExample = AddToDate(&dtExample, 1, 0, 0);
   
/* Get date forward 0 years, 0 months, 30 days */
&dtExample = AddToDate(&dtExample, 0, 0, 30);
   
/* Get date backward 0 years, 3 months, 0 days */
&dtExample = AddToDate(&dtExample, 0, - 3, 0);
   
/* Get date backward 0 years, 0 months, 14 days */
&dtExample = AddToDate(&dtExample, 0, 0, - 14);

There are are also two related functions:

An Object of class APIObject failed to deserialize

You might see this error in your .tracesql logs.

Apparently this happens if you are performing page transfers or pop ups (modal windows) and you are using ApiObject variables (search for ID 652536.1 in My Oracle Support for further details).

In my case, I saw this error after code for calling and using a component interface. The solution that worked for me was to set the value of all ApiObject variables I was using to null once I was finished with them (e.g. after saving the CI).

PeopleCode Statement Number

Most errors and logs will point you to a particular PeopleCode statement number. But how do you figure exactly what bit of PeopleCode the error is referring to?

In Application Designer:

  1. Open your PeopleCode
  2. Choose view and turn off Word Wrap (it should not have a tick next to it)
  3. Choose edit > goto (or CTRL + G)
  4. In the dialog box select the statement number option and enter the statement number you want.

goto-peoplecode-statement.png

Note if the statement number option is disabled, you need to first turn off word wrap through the view menu (step 2).

Page Data is Inconsistent with Database

During development you may come across the following error:

Page data is inconsistent with database. (18,1)

page-data-is-inconsistent-with-database.png

In addition to the examples given in the message, another scenario where this error can happen is when the keys in the record definition in application designer do not match the keys in the database table.

Check your keys and ensure they match between the record definition and the database table. If not, update the record definition and/or alter/recreate the database table with the correct keys.

Another thing to watch out for is any SavePostChange PeopleCode that may be changing the data outside of the buffer and causing it to be out of sync.

Web Server URI

If you need to retrieve the web server URI for rending images in HTML emails try this

method GetWebServerURI
   /+ Returns String +/
   Local string &strUrl, &strTabName, &strUrlModified;
   Local string &sRegex, &sReplace;
   Local JavaObject &jRegex, &jUrl;
   
   &strUrlModified = "";
   &strTabName = "DEFAULT";
   &strUrl = GenerateHomepagePortalURL(%Portal, %Node, &strTabName);
   
   /* Regex strings */
   &sRegex = "(.*)/(ps[cp])/(.*)";
   &sReplace = "$1/";
   
   /* Instantiate objects and replace */
   &jRegex = CreateJavaObject("java.lang.String", &sRegex);
   &jUrl = CreateJavaObject("java.lang.String", &strUrl);
   &strUrlModified = &jUrl.replaceAll(&jRegex, &sReplace);
   
   Return &strUrlModified;
   
end-method;

Generate Formatted XML

PeopleCode includes a class library for working with XML documents called XMLDoc.

There are a number of very useful methods within in this class including, GenFormattedXmlString which takes XML (as a string) and formats it with new line characters and indents so its more readable.

To use this method, simply declare an XmlDoc object, pass it the unformatted XML and call the GenFormattedXmlString method like so:

Local XmlDoc &oXD;
Local string &sUnformattedXML, &sFormattedXML;

&sUnformattedXML = "[Your unformatted XML goes here]";
&oXD = CreateXmlDoc(&sUnformattedXML);
&sFormattedXML = &oXD.GenFormattedXmlString();

Stop PeopleCode Processing for Debugging

A blunt, but useful method for debugging PeopleCode is to use the inbuilt Error function to stop processing and provide a message.

Something as simple as this can be a valuable debugging tool at times:

Error "The value of variable x=" | &x;

Most people use MessageBox (or the now very deprecated WinMessage), but at times this may not appear depending on the context of the PeopleCode you are executing. Error stops processing (a poor man's breakpoint) so you can find out what's going on at that exact point in the code.

NOTE: The Error function causes a rollback of any pending database transactions, so if you need to store the state of the DB, put in a commit (e.g. SQLExec("commit") on Oracle) before throwing the error.

SearchClear

You can use the SearchClear() method which is part of the Field class to clear search values on a particular search key field. This is how you can reset search parameters specified by a user when they press the Return to Search button.

Use the following shortcut notation to call the function:

RECORD.FIELD.SearchClear();

Or you can declare a Field class and then call the method.

Put this code in the SearchInit event.

If you are clearing a number of fields, I recommend creating a simple function (e.g. ResetSearch) in FieldFormula and calling that function rather than putting a single line of code in the SearchInit event for each field you are clearing. Just makes maintenance a little bit simpler as you only need one function and one call.

Remove HTML Tags with Regex

The following is a rudimentary example of how you can remove HTML tags via PeopleCode using Regex (and the JavaObject):

function RemoveHTMLTags(&sInputHTML as String) Returns String

   Local JavaObject &jRemoveHTMLRegex = CreateJavaObject("java.lang.String", "<[^>]*>");
   Local JavaObject &jInputHTML = CreateJavaObject("java.lang.String", &sInputHTML);
   Local string &sPlainText = &jInputHTML.replaceAll(&jRemoveHTMLRegex, "");
   
   Return &sPlainText;

end-function;

Place this function (or turn it into an App class method) and use it as required. Useful for cases where the PeopleSoft rich-text editor has saved HTML in a DESCRLONG type field in the database.

NOTE: This will also might certain formatting, e.g. you might lose line breaks if they are saved as paragraph (p) or break (br) tags.

Pausing Execution

There isn't a delivered way to pause the execution of PeopleCode.

However, there are two alternatives that you can use:

Using java.lang.Thread.sleep()

To use this approach, add the following line to your PeopleCode, passing a value in milliseconds which is the amount of time to sleep:

GetJavaClass("java.lang.Thread").sleep(1000);

The code above will sleep for 1000 milliseconds (1 second). Note that this approach still uses up CPU cycles and has a performance hit on the application server. If you find this to be an issue, then the second approach may be better suited if you have an Oracle database.

Using DBMS_LOCK.SLEEP()

SQLExec("exec DBMS_LOCK.SLEEP(1)");

The code above will sleep for 1 second.

GetImageURL

GetImageURL is a nifty PeopleCode iScript (Response class) method that returns a URL for an image stored in the database, which is retrieved and stored on the web server. It allows you to store images in PeopleSoft through the Image definition type in Application Designer. Then, in your iScript you can use this method to retrieve and display the image on a custom page.

The basic syntax for the function is:

Local string &sImageURL;
&sImageURL = %Response.GetImageURL(Image.YOUR_IMAGE);

The URL returned is the absolute URL to the image on the web server.

You can also use GetImageURL to retrieve an image from any database table (RECORD.FIELD) and display it. Very handy stuff when displaying images on a page.

This is particularly useful for branding where you may use your own images but want to store them in the PeopleSoft database (through Application Designer) rather than on the web server(s).

Default a Search Field Value

This is a simple trick but can be easy to forget. If you want to use the default value of your field as your search field value there are two steps involved:

Step 1: Set the field default on your search field

In this example, I want the SETID to default from the operator defaults table in campus solutions OPR_DEF_TBL_CS for my SETID search field.

To do this, I set my field default as shown:

field-default-record-field-name.png

Step 2: Add SearchInit PeopleCode

Add the following PeopleCode to either the Record.Field.SearchInit or Component.Record.SearchInit PeopleCode.

SetSearchDefault(RECORD.FIELD);

Where RECORD.FIELD refers to the name of the record and field that you set the field default for in the first step.

Now when you hit the search page, your search field should be defaulting its value from its field default settings.

Searching PeopleCode for Message Catalog Entries

One of the best reasons to export PeopleCode to a file is so that you can perform a search on it — and perhaps the most common search you'll perform is to find which code is generating a message catalog message.

Unfortunately, message catalog entries aren't always consistent, for instance all of the following are legitimate entries:

A regular search for the string 14200,91 may not net you any results purely because of the white spacing. However, if you are using a grep tool, you can use a regular expression to improve your search.

This is an example of the regex you might use: 14200[\s,]+91.

This regular expression translates to find 14200 and then one or more occurrences of a white space (spaces, tabs, line breaks) and a comma followed by a 91.

It caters for all of the above scenarios.

Session Class

The Session Class is the root of the API classes provided by PeopleSoft. It provides access to control the PeopleSoft environment including security and acccess, errors and error handling, regional settings and tracing.

The following APIs are accessible through the an active session object:

Error Handling

The session object handles error processing for all APIs. The following properties are available to check for errors:

These errors are stored in the PSMessages collection which is a property of the session object.

Session Object

Declare the session onject as either Global, Component or Local. Use the %Session variable to set the session to the current session:

Local ApiObject &objSession;
&objSession = %Session;

WriteToLog

Next time you need to do some in-line PeopleCode debugging, rather than the traditional approach of using MessageBox, orWinMessage, consider using the PeopleCode WriteToLog function.

To summarise, the WriteToLog function consists of two parameters:

When called, it will write to a file called something like <OPRID>_<IP>.tracesql in the application server logs folder (where you would typically find other trace files).

The thing I like best about this is that it is unobtrusive. It doesn't impact other users and it doesn't stop execution flow (nothing worse than a MessageBox in a loop that you have to click through 50 times!).

Setting the value of Reserved Field Names

If you are trying to set the value of a field through PeopleCode and that field happens to be NAME then you will experience the following error when using the shortcut Rowset(Row).Record.Field.Value approach:

&rsExample(1).DERIVED.NAME.Value = "This will error";

Gives the following error when you try to validate/save PeopleCode:

Expression of type String is not an object reference. (2,48)

This is caused by PeopleCode getting confused with the fact that the Field class also has a property, Name. To get around this, use GetField to access the Field object and then set the value like this:

&rsExample(1).DERIVED.GetField(Field.NAME).Value = "This will work";

Other fields such as LABEL or VALUE in your record would give you a similar error given they are also properties of the Field class.

Rowset Sorting

The Rowset class includes a Sort method which can be used to sort items in the Rowset by one or more fields in ascending or descending order. Very much like what the order by clause does for you in SQL.

PeopleBooks gives an example of how to sort by a single field. To sort by multiple fields, add each record.field and order to the parameter list. For example:

&rs.Sort(RECORD.FIELD1, "A", RECORD.FIELD2, "D", RECORD.FIELD3, "A");

So this example will sort by FIELD1 (asecending) then FIELD2 (descending) and then FIELD3 (ascending). Pretty much the same as an order by syntax but you do need to be explicit about the sort format (A=ascending, D=descending).

NOTE: A filled rowset won't necessarily take the ordering you specify in your SQL or in a view. So you might go to the trouble of ordering in the view/SQL and it will probably revert to ordering the data in the sequence in which it was entered in the database. So you'll need to use the Rowset sort method in this scenario.

Standalone Rowset

In PeopleCode a standalone rowset is an independent rowset object not associated with the component buffer. They allow you to work with data outside of the buffer by getting whatever additional data you need form the database. In this sense they replace the functionality of derived records which were once used as place holders to store data not directly associated with the component.

Because this type of rowset is standalone, there is no automatic action by the component processor on it. This means that if a standalone rowset is used to manipulate data (inserts/updates), code will need to be added to manually save the changes.

To create a standalone rowset from a record:

Local Rowset &rsExample;
&rsExample = CreateRowset(Record.REC1);
NOTE: this will only create the placeholder object. At this point, the rowset is not populated any data from the database, it only consists of the properties of the record definition.

To fill a standalone rowset with data use the Fill method. This parameters to the fill method are a Where clause and bind values.

&rExample.Fill("where FIELD1 = :1", REC2.FIELD2);

Hide SQL Errors with Try Catch Blocks

If you are working on any PeopleCode that involves inserting or updating data in a table, and you want to avoid showing any SQL errors to the user, then use a try-catch section.

For example, I was working on some code to log extra information about a transaction to a logging table. In the event that the logging failed due to a SQL error, the entire transaction would stop. Not a good thing to happen to a user who doesn't know or care about the fact that you are doing some system logging.

Here's my basic code wrapped in a try-catch section:

try
  SQLExec(SQL.EXAMPLE, &p1, &p2, &p3);
catch Exception &ex
  /* Ignore the exception and move on */
end-try;

In the above, you could have some code to deal with the exception. But the point in my case was to not display a fatal SQL error which stopped the transaction from continuing. This is really just for safety. I'm not expecting a SQL error but if one does ever happen, the code will move on and not abort the transaction.

Check if a Directory Exists

How do you check if a directory exists on the application server?

The following code is one way I've found. It uses the FindFiles function to return an array of subdirectories (matching on the * wildcard) &aSubDirs in a base directory &sBaseDir. It then loops through all the subdirectories and compares them to the check directory (&sCheckDir). Note that in this example, I am using absolute file paths. This should work on both NT and Unix servers (with appropriate file path changes).

ocal array of string &aSubDirs;
Local string &sBaseDir;
Local string &sCheckDir;
Local boolean &bDirExists = False;
Local integer &i;
 
&sBaseDir = "C:\TEMP";
&sCheckDir = "C:\TEMP\TEST";
 
/* Get all the directories under the base directory &sBaseDir (C:\TEMP) */
&aSubDirs = FindFiles(&sBaseDir | "*", %FilePath_Absolute);
 
/* Loop through sub-directories and check if any of them match the directory &sCheckDir (C:\TEMP\TEST) */
For &i = 1 to &aSubDirs.Len
    If &aSubDirs[&i] = &sCheckDir Then
        &bDirExists = True;
    End-If;
End-For;
 
If &bDirExists = True Then
    /* The directory exists */
Else
    /* The directory does not exist */
End-If;

Think Time Functions

Think-time functions are PeopleCode functions that suspend PeopleSoft processing to wait for an external event (user input, an external process to complete etc). PeopleBooks strongly recommends not using think-time functions in certain PeopleCode events.

For example, MessageBox is not a think time function when the style parameter only specifies one button. This can only be the case when the style parameter is 0 or %MsgStyle_OK which means only show the OK button. So if you use0 or %MsgStyle_OK as the style parameter, you can use MessageBox as a normal function in any relevant PeopleCode event.

There is also a difference between the Warning and Error PeopleCode functions. Turns out that Warning behaves as a think-time function, while Error does not.

The results will be a general Think-time PeopleCode event message like this:

think-time-peoplecode-event-error.png

This is a strange one as it isn't listed in Think-time Functions list in PeopleBooks. So I suspect it may have been the way the Warning function was being used in this case.

Anyway, something to keep in mind. If you get a think-time message, try switching from Warning to Error.

Export PeopleCode to File

The Application Designer Find In... function allows you to export PeopleCode search results to a file. Having a searchable PeopleCode library as a text file (which can be quite big!) can be really handy at times.

To export all PeopleCode from the entire database (or project):

find-in-export-peoplecode.png

You may want to separate your PeopleCode exports (e.g. Record PeopleCode, Component PeopleCode, Application Engine PeopleCode, Application Package PeopleCode). If someone is looking for PeopleCode across all types they can simply grep all files.

A regular export of PeopleCode from your development master is a simple and effective backup and if you put it into version control (e.g. Git) you have a very simple way of managing change to code.

Caution! it takes a considerable amount of time (could be hours) to export all PeopleCode. This puts a fair load on your system so it would be wise to do this outside of regular hours and never on production!

Process Run Validation

You might have the need to perform some last minute validation before a user triggers a run control. Essentially, at the point at which they press the "Run" button to schedule the process. For example, you might want to check you have all the relevant parameters, or perform more complex validation based on two or more parameters.

My suggestion for doing this is to put it into component PeopleCode for the relevant run control component in either FieldEdit or FieldChange event of the Run button.

Specifically, the location for this code would be component-record-field PeopleCode at:

YOUR_COMPONENT.PRCSRQSTDLG_WRK.LOADPRCSRQSTDLGPB.FieldChange or FieldEdit

This is assuming that you have added the PRCSRUNCNTL_SBP to your run control page. The PeopleCode location will vary if you are using a different run control subpage.

The nice thing about using component-record-field PeopleCode here is that the validation is just for your component (and does not get called in other components that use the same shared sub-page).

Remember to use the PeopleCode Error command if you need to stop processing and prevent the run control being scheduled if you are using FieldChange. Alternatively use the FieldEdit event.

Creating Cookies

The documentation in PeopleBooks regarding the Cookie class is a little unclear. Here's a simple example of the PeopleCode you could use to create your own cookie and how to set properties in the cookie class:

Local object &Response = %Response;
Local object &YourCookie;
 
&YourCookie = &Response.CreateCookie("YourCookieName");
&YourCookie.Domain = %Request.AuthTokenDomain;
&YourCookie.MaxAge = -1; /* Makes this a session cookie (default) */
&YourCookie.Path = "/";
&YourCookie.Secure = True; /* Set to true if using https (will still work with http) */
&YourCookie.Value = "Set the cookie value here. Encrypt sensitive information.";

peoplesoft-example-cookie.png

If you don't see your cookie make sure you have set your domain correctly (with leading full stop) to match your environment. If this is incorrect, the cookie will not be created.

If you make the MaxAge greater than 0, that defines the lifetime of the cookie in seconds. A cookie with a MaxAge of 0 is deleted immediately (not sure what you would use that for?).

Required Fields Cue

If you are using PeopleCode to perform validation on a field that has to be entered by the user, then use the ShowRequiredFieldCue method in the Field Class. This will display the little asterisk next to the field on the page indicating to the user that the field is required.

By replicating the default PeopleSoft behaviour, you'll add to the usability of your application. To use the method you can use a field object, or you can shortcut and use RECORD.FIELD.ShowRequiredFieldCue = True.

Showing and Hiding a Grid

The Rowset class contains two methods that can be used to show and hide all rows:

You can use these two methods to show or hide a grid on a page associated wtih a particular rowset. Simply make sure that the grid uses the main record that your rowset refers to, then refer to this scroll record using a rowset and call the methods to show or hide the grid.

This is particularly useful if you have a field that determines whether or not a grid should be displayed. For example, a check box field called Specify individual Users and a grid used to enter individual users. Make sure you put your PeopleCode in both the FieldChange and RowInit methods to ensure consistency when showing/hiding your grid.

Also make sure that in your page properties, you have the Adjust Layout for Hidden Fields check box set. This ensures the page height adjusts dynamically based on whether or not the grid is visible, rather than leaving a big whitespace gap when it is hidden.

Note if you are receiving errors about modifying the current program context, move your code up a scroll level and then use ShowAllRows() or HideAllRows() from the higher level scroll on your rowset. You cannot use this code at the same scroll level as the grid itself.

Record Snapshot

The following is a bit of PeopleCode I came up with to get a snapshot of the contents of a given Record object through PeopleCode. You might find this useful as a way of inspecting a record and its contents at a given point in time during PeopleCode execution.

This code utilises the WriteToLog function to output the snapshot to the Application Server log directory as a tracesql file.

Local Record &rec_Snapshot = &rs(1).EXAMPLE_RECORD;
Local number &i;
Local number &fieldCount = &rec_Snapshot.FieldCount;
Local string &log = "";
 
WriteToLog(%ApplicationLogFence_Error, "--- BEGIN Record Snapshot for Record: " | &rec_Snapshot.Name | " ---");
For &i = 1 to &fieldCount
   &log = "... ";
   If &rec_Snapshot.GetField(&i).IsInBuf Then   
      &log = &log | &rec_Snapshot.GetField(&i).Name | '=' | &rec_Snapshot.GetField(&i).Value;  
   Else 
      &log = &log | &rec_Snapshot.GetField(&i).Name | '=' | '(Not in Buffer)';
   End-If;
   WriteToLog(%ApplicationLogFence_Error, &log);
End-For;
WriteToLog(%ApplicationLogFence_Error, "--- END Record Snapshot for Record: " | &rec_Snapshot.Name | " ---");

Also you'll notice that it uses the Field class IsInBuf method to check if the field is actually in the buffer (i.e. has some data ) and only report the value of the record's field if it has data in the buffer. If not it will report it as Not In Buffer.

Creating an Unique File Name

If you are working with files or file attachments in PeopleCode, you will typically want to create a unique file name for each user to prevent them from overwriting each others files if files are being stored in the same folder.

Here's a simple piece of PeopleCode that generates a file name prefix with the format OPRID-Date-Time.

Local string &sFilePrefix;
 
&sFilePrefix = %OperatorId | "-";
&sFilePrefix = &sFilePrefix | String(%Date) | "-";
&sFilePrefix = &sFilePrefix | Substring(String(%Time), 1, 8) | "-";
NOTE: the date and time formats will be specific to your region.

The Substring(String(%Time), 1, 8) drops the millisecond component of the time stamp (which is 6 digits long). If you want to include the millisecond component, don't perform the Substring. Also %Time is the database time not the application server time. If you want the application server time use %PerfTime instead. The application server time %PerfTime might be more accurate for file attachments for example.

If you have a number of files per each user, you might want to consider creating a subdirectory for each each operator ID and placing files in there.

The reason for using Operator IDs is that they have a strict format and don't contain any spaces. This isn't the case for run control IDs.

For example you can create run control IDs like these:

Not the most ideal file names! That is why run control ID is not used in the file prefix.

The at @ Meta Operator

The @ (at) meta operator is a very useful meta programming operator in PeopleCode. When applied, the operator turns a string into a reference in PeopleCode.

For example, the follow PeopleCode snippet:

Local string &sEmplidField = "EMPLID";
Local string &sEmplid;

&sEmplid = GetField(@("Field." | &sEmplidField)).Value;

Effectively turns into the following code:

Local string &sEmplid;

&sEmplid = GetField(Field.EMPLID).Value;

This is a trivial example, however, hopefully you can see the power of doing something like this to generate dynamic code based on variables.

For example, say you want to get the value of a field passed to a method, but you don't know what field that will be. For such a scenario, you could use code like the above to dynamically cater for any field.

The @ operator can be used in methods/functions that expect a string that is a reference to a particular Definition or an Object (however I'm not sure if this is the case for all objects ).

Some of the common functions where it can be used include:

However it can conceivably be used with any PeopleCode function or method that expects a definition or object reference as a string parameter, for example DoModal, Transfer, SetCursorPos, GenerateComponentPortalURL, and SQLExec.

FieldChange Event Does Not Fire

A FieldChange PeopleCode event may not fire on a field if the component is set to deferred processing and the page-field setting is to allow deferred processing:

allowed-deferred-processing-page-field-settings.png

This is a common problem when adding FieldChange events to radio buttons. Make sure that your field has allowed deferred processing unchecked in the page-field settings:

A good way to determine if a field is going to fire PeopleCode is to use your browser console/inspector tool to look at the field on the page at run time.

Here's the HTML source for a radio button input form field that did not fire PeopleCode:

<input type="radio" onclick="this.form.DERIVED_SR_ATND_ATTEND_CR_UPD_FLG$rad.value=this.value;
doFocus_win0(this,false,true);" value="U" tabindex="22" id="DERIVED_SR_ATND_ATTEND_CR_UPD_FLG$29$" 
name="DERIVED_SR_ATND_ATTEND_CR_UPD_FLG"/>

Here's the same HTML source for the radio button input form field after turning off allow deferred processing:

<input type="radio" onclick="this.form.DERIVED_SR_ATND_ATTEND_CR_UPD_FLG$rad.value=this.value;
submitAction_win0(this.form,this.name);/*ffffffff,0*/" checked="checked" value="U" tabindex="22"
id="DERIVED_SR_ATND_ATTEND_CR_UPD_FLG$29$" name="DERIVED_SR_ATND_ATTEND_CR_UPD_FLG"/>

The key difference here is the submitAction_win0(this.form,this.name);. In the code that does not fire this is set to doFocus_win0(this,false,true);.

Dynamic From SQL

This is how you can use a dynamic table name in the from clause of SQL called by PeopleCode.

First, you need create your SQL object. This is an example of the code you would use (in this example this SQL object will be named GET_OPRID_FOR_PERSON).

The %Table parameter returns the SQL (database) table name for a application designer record name. So if you pass it the record name PERSON it would translate it to PS_PERSON. The record name PSOPRDEFN would be translated to ... PSOPRDEFN - why? Because PSOPRDEFN has a non-standard SQL name already set as PSOPRDEFN.

The next step is to call your SQL object. In this example, I'm going to use SQLExec but you could also use the relevant methods in the SQL class.

Local string &sOprRecName = "PSOPRDEFN";
Local string &sEmplid = '1234';
Local string &sOprid;
 
SQLExec(SQL.GET_OPRID_FOR_PERSON, @("Record." | &sOprRecName), &sEmplid, &sOprid);

Note that parameter 1 (:1) is passed as @("Record." | &sOprRecName). What does this mean?

Well first you concatenate Record. with the record name stored in the variable &sOprRecName which gives you the string "Record.PSOPRDEFN". However, you don't want to pass the string but rather the PeopleCode as Record.PSOPRDEFN (note there are no quotes around this text). This is what the @ ( at meta operator ) does. It coverts strings with PeopleCode into callable PeopleCode.

Open New Window PeopleCode

Sometimes you need to open a new window to a PeopleSoft page and when that windows opens you lose the state of your previous page. Here is a function that uses the PeopleSoft newwin servlet processing to open a new window in a new state block.

/*****************************************************************************
 NewWinUrl(&strUrl)
 
 This function modifies a URL to return a new window URL 
 It allows a new state block to be generated in a separate browser instance.
 http://server:port/psp/ps/EMPLOYEE/HRMS/c/...... 
 turns into 
 http://server:port/psp/ps_newwin/EMPLOYEE/HRMS/c/...... 
 which renders to the browser as 
 http://server:port/psp/ps_1/EMPLOYEE/HRMS/c/...... 
 
 Input parameters:  &strUrl - a URL to manipulate
 Output parameters: &strUrlModified - New URL with _newwin parameter
 
 ****************************************************************************/
 
Function NewWinUrl(&strUrl As string) Returns string;
   Local string &sRegex, &sReplace, &Result;
   /* Declare java object */
   Local JavaObject &jUrl;
 
   /**
    * Peoplesoft Content types:
    * -------------------------
    * Component: c
    * Script: s
    * External: e
    * Homepage: h
    * Template: t
    * Query: q
    * Worklist: w
    * Navigation: n
    * File: f
   **/
 
   /* Regex strings */
   /*          psc/psp  Site      Portal    Node      Content Type */
   &sRegex = "/(ps[cp])/([^\/]*)?/([^\/]*)?/([^\/]*)?/([csehtqwnf]{1})/";
   &sReplace = "/$1/$2_newwin/$3/$4/$5/";
 
   /* Instantiate objects and replace */
   &jUrl = CreateJavaObject("java.lang.String", &strUrl);
   &Result = &jUrl.replaceAll(&sRegex, &sReplace);
 
   /* Return modified URL */
   Return &Result;
End-Function;

Creating Excel Files

With SQR, you were always limited to writing CSV files, unless you had some sort of overpriced third party app you could call.

Well, with App Engine (and more specifically using a PeopleCode action in App Engine) you can create Excel files via the Microsoft Excel COM object, as long as the process scheduler is running on Windows.

The following is an example of doing so, from a template file called template.xls, which needs to sit on the server somewhere. The template file exists, because most of the time you don't want to be doing everything from PeopleCode. Mine usually contains images like company letterhead layout.

/* Set up the Excel COM objects and open the template file */
Local object &oWorkApp, &oWorkBook;
&oWorkApp = CreateObject("COM", "Excel.Application");
&oWorkApp.DisplayAlerts = "False";
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\some_path_to\template.xls");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oWorkApp.ActiveWorkBook.SaveAs("C:\your_output_file.xls");

/* then go crazy with your worksheet object */
/* eg */
&oWorkSheet.Cells(1, 1).Value = "I'm adding stuff to be bolded";
&oWorkSheet.Cells(1, 1).Font.Bold = True; 

/* Save Excel file and quit */
&oWorkApp.ActiveWorkBook.Save();
&oWorkApp.ActiveWorkBook.Close();
&oWorkApp.DisplayAlerts = "True";
&oWorkApp.Quit();
You'll need to change the Open path and SaveAs path in the code above which also uses Sheet1 as the worksheet name.

That's it! Of course with COM objects, all the properties and methods available for use in VBA are available here. What I usually do is record a macro, and transfer over the code, but using PeopleSoft variable notation of course.

Data Being Added Conflicts with Existing Data

While developing a page with multiple scrolls levels, and especially when using a grid, you may get the error:

Data being added conflicts with existing data. (18,2).

data-being-added-conflicts-with-existing-data.png

This indicates a problem with the key structures in the tables in your lower scroll levels.

For example, you may have a parent record at scroll 0 with the keys: INSTITUTION and EFFDT, and then a child record at scroll 1 with the same keys INSTITUTION and EFFDT. That is, no additional key(s) to indicate the child data. If you are building a parent-child relationship, child records should have more keys than parent records, otherwise you have a design flaw.

A tell-tale sign of this in a grid, is that when you add another row in your grid all data is copied automatically to the new row. Check your child record (the one in your lower scroll level or grid) and examine the key structures, are you missing one or more keys?

Also check your overall page levels, do you have the scroll levels at the right occurs level? For example, have you accidently put a child record at the same level or higher than its parent? Remember scroll level hierarchy starts at 0 and goes up (1,2,3) so by higher I mean a child record with a lower or equal scroll level value as its parent. Be sure to fix up the occurs level in your scroll bar, scroll area or grid.

This error may also be happening because keys are not propagating correctly from parent to child records. Check the rows in your child record and ensure that it has the same high level keys as its parents. This is particularly common with the OPRID and RUN_CNTL_ID fields when working with run control records.

Another cause may be PeopleCode that modifies your key fields. For example, do you set a key field's value on FieldDefault, and then modify it later, say on SavePreChange causing a buffer mismatch?

URLs to Hyperlinks

As a follow on to the Email Mailto Links Regex article, this article gives an example of a PeopleCode function that searches for URLs (web addresses) in a text string and converts them into HTML hyperlinks.

This article gives an example of a PeopleCode function that searches for URLs in a text string and converts them into HTML hyperlinks complete with the anchor (<a>) tags and target="blank" to open in a new tab/window.

So for example if it were to find the text:

https://www.peoplesoftwiki.com

It would be replaced with the HTML:

<a href="https://www.peoplesoftwiki.com" target="_blank">https://www.peoplesoftwiki.com</a>

This allows a end user to click on the hyperlink and have it open in a new window. This is particularly useful for long description configuration fields where users can specify a URL and this will automatically displayed as a hyperlink.

NOTE: to use in a long description field a HTML area is required to display the hyperlink correctly.
Function ConvertURLsToHyperLinks(&sInput As string) Returns string
 
   Local JavaObject &jURLRegex, &jURLLinkRegex, &jInput;
   Local string &sHTML;
 
   /* Match any URLs */
   &jURLRegex = CreateJavaObject("java.lang.String", "\bhttps?://\S+\b");
 
   /* Replace the found match ($0 back reference) and enclose with a href HTML tag */
   &jURLLinkRegex = CreateJavaObject("java.lang.String", "<a href=""$0"" target=""_blank"">$0</a>");
 
   /* Store the text to be scanned for URLs as a Java string object */
   &jInput = CreateJavaObject("java.lang.String", &sInput);
 
   /* Perform a replace all using the search URL regex and the URL link regex */
   &sHTML = &jInput.replaceAll(&jURLRegex, &jURLLinkRegex);
 
   Return &sHTML;
 
End-Function;

A few limitations to be aware of with this function are:

Viewing Component Level PeopleCode

There are a few ways to view PeopleCode at the component level (component, component-record, component-record-field) in Application Designer.

Through the Structure tab

Open the component, switch to the structure tab, find the appropriate scroll (if you can), right-click, to bring up the View PeopleCode menu.

component-structure-view-peoplecode.png

Through the Project

Add the component to your project, use the development tab to expand out the list of components, right click on your component and bring up the View PeopleCode menu.

project-component-view-peoplecode.png

By inserting the Component Level PeopleCode in your project

Another, less obvious way to view PeopleCode, is to insert it into your project, using:

Insert > Definitions into Project (CTRL + F7)

Changing the definition type to PeopleCode, specifying the component name, and selecting the type (e.g. Component Record):

insert-peoplecode-type.png

Double click to add the PeopleCode to your project, then change to the upgrade tab to view the PeopleCode:

viewing-peoplecode-through-upgrade-tab.png

What is nifty about this method is that it lets you get directly to a piece of PeopleCode if you already know where it lives, without having to navigate through the component structure (which can be rather slow for a large component). Simply use your upgrade tab to open it up.

Another useful fact is that this gives you a list of all the PeopleCode at a particular level in the component, and may be a little easier to use for finding code than the tree scroll structure in the structure tab.

NOTE: that when you are in the view PeopleCode window you can use the left drop down list to find PeopleCode (anything that's in bold) and the right PeopleCode drop down list to view the events:

view-peoplecode-window.png

Setting a Date to Null in PeopleCode

To set a date to null in PeopleCode either use the SetDefault() function (deprecated) or the SetDefault field method

Using the function:

SetDefault(YOUR_RECORD.DT_FIELD);

Using the field method If you are in the current context:

YOUR_RECORD.DT_FIELD.SetDefault();

Using the field method if you are not in the current context using a field object:

Local Field &fldDateExample;
/* Code to set your &fldDateExample object */
&fldDateExample.SetDefault();

Not intuitive but it works.

There is also another way to set a date to null via PeopleCode! In some cases, using SetDefault() to blank out a field is not what we're after, as there may be a default for the field! The only other way (to my knowledge) is this:

RECNAME.DATE_FIELD.Value = "";

.Value is the KEY here. Not including .Value will throw Syntax errors in App Designer when you try to assign the date field to "" and save your program.

Getting current time in HHMMSS Format

Here's how to get the current time in the format HHMMSS from the current time:

Local &strCurrentTime;
&strCurrentTime = Substring(Substitute(String(TimePart(%Datetime)), ".", ""), 1, 6);

This takes the time part of the current date time on the application server, replaces the dot (.) seperators with blanks, and returns only the first 6 characters (hhmmss) ignoring the millisecond part. This returns say a time of 11.52.00.000000 as 115200.

Copying Rowsets

I find that you often need to create and manipulate standalone rowsets. Sometimes you can get the data for your standalone rowset from the database using the Fill method, however sometimes you'll want to copy from existing rowsets. This is where the CopyTo method comes in handy.

However there is one important thing to note when using CopyTo - it will only copy like-named record fields and subscrolls at corresponding levels.

In order to work correctly, the record in the source rowset must have the same name as the record in target rowset, unless you specify a record list in the parameters.

For instance, say I have data in a rowset &rsExample with one record, EXAMPLE which is populated in the component buffer.

The EXAMPLE record has the following fields:

Now, say I want to copy the data from my rowset &rsExample to another rowset, &rsExampleAudit which consists of an audit record for EXAMPLE called AUDIT_EXAMPLE which has the following fields:

What I want is to copy the like-name fields between &rsExample and &rsExampleAudit (the fields EMPLID and NAME).

The following code will NOT work:

&rsExample.CopyTo(&rsExampleAudit)

Why? Because &rsExample consists of a record named EXAMPLE, but &rsExampleAudit consists of a record named AUDIT_EXAMPLE. Because the two rowsets do not have the same underlying record name, the copy does absolutely nothing (quite frustrating!).

In this scenario, we need to specify a record list, so it knows the source and target record names. This how I to write this code to make it work:

&rsExample.CopyTo(&rsExampleAudit, Record.EXAMPLE, Record.AUDIT_EXAMPLE)

Generically the syntax is therefore:

&rsSource.CopyTo(&rsTarget, Record.SOURCE_RECNAME, Record.TARGET_RECNAME)

Modals

Secondary pages are modal in that they require some sort of user intervention (e.g. clicking an OK or CANCEL) button before the user can go back to what they were doing.

That is, they require some form of user intervention for the user to proceed. This makes them really good for warnings and ensuring the user enters the correct data.

There are three key functions for using modal secondary pages:

DoModal

You can call a secondary page as a push button/hyperlink or you can use the DoModal PeopleCode function.

The basic syntax is:

DoModal(PAGE.pagename, title, xpos, ypos, <level, scrollpath, target_row>)

For example:

If DoModal(Page.EXAMPLE_PAGE, "Example Modal Page", - 1, - 1) = 1 Then
    /* Do stuff */
Else
    /* Exception */
End-If;

If using the Peoplecode DoModal function, you need to add a "Secondary Page Control" on the primary (i.e. calling) page, otherwise the OK button won't return you to the primary page. You can also use a try-catch exception block. The xpos and ypos parameters of -1, -1, centre the modal secondary page.

IsModal

IsModal is typically used to separate logic for modal pages from that for standard pages. The syntax is just: IsModal(). The function returns either a true if the page is modal or a false if it isn't.

EndModal

EndModal is only required if your secondary page doesn't already have its own OK and CANCEL buttons, which you specify in the secondary page properties.

secondary_page_properties.png

Sometimes, the delivered OK and CANCEL buttons on a secondary page can misbehave and its better to put your own buttons on there and use EndModal.

The syntax for this function is:

EndModal(returnvalue)

The return value can be either 0 or 1. The 0 acts as the CANCEL button and the 1 as the OK button, so at the end of the PeopleCode for these buttons if you put them manually on your secondary page, put the line EndModal(0); in the CANCEL button PeopleCode and EndModal(1); in the OK button PeopleCode.

NOTE: , there are delivered fields OK_BTN and CANCEL_BTN which you should use rather than creating your own - one less customisation that way.

Effective Sequence PeopleCode

There are cases where you'll need to automatically increment the value of an effective sequence (EFFSEQ) field through PeopleCode and decrement it when a user deletes a row.

The following code is my own solution to this problem. It ensures that the effective sequence always increments by 1 correctly, and correctly adjusts the effective sequence if a user deletes a row out of order.

The code goes in two events.

EFFSEQ.RowInsert

When a new row is inserted, set the effective sequence value to match the ActiveRowCount property of the rowset in the buffer:

Local Rowset &rs_EXAMPLE_ONLY = GetRowset();
 
EXAMPLE_ONLY.EFFSEQ = &rs_EXAMPLE_ONLY.ActiveRowCount;

As the active row count reflects the number of rows in the buffer, this will always be in sync with the effective sequence.

EFFSEQ.RowDelete

The above code works fine, provided a user doesn't go and delete a row out of sequence. For example if we have 3 rows:

EFFSEQ DATA
1 First Entry
2 Second Entry
3 Third Entry

And the user decides to delete Row 2 (EFFSEQ = 2) then the result is:

EFFSEQ DATA
1 First Entry
3 Third Entry

But what we want ideally is:

EFFSEQ DATA
1 First Entry
2 Third Entry

So that if another row is added, we see:

EFFSEQ DATA
1 First Entry
2 Third Entry
3 Fourth Entry
NOTE: ignore the text in the data, as the data doesn't relate to the effective sequence value. The text is there in this example so you can see how the effective sequence is changing.

To get the above working, use the following PeopleCode:

Local integer &i;
Local Record &rec_EXAMPLE_ONLY;
Local Rowset &rs_EXAMPLE_ONLY = GetRowset();
 
For &i = 1 To &rs_EXAMPLE_ONLY.ActiveRowCount
   &rec_EXAMPLE_ONLY = &rs_EXAMPLE_ONLY(&i).EXAMPLE_ONLY;
   If &rec_EXAMPLE_ONLY.EFFSEQ.Value > EXAMPLE_ONLY.EFFSEQ Then
      /* Decrement by 1 as the user has deleted effective sequence out of order */
      &rec_EXAMPLE_ONLY.EFFSEQ.Value = &rec_EXAMPLE_ONLY.EFFSEQ.Value - 1;
   End-If;
End-For;

This code, simply finds all effective sequences greater than the one deleted and decrements them all by 1.

Commenting Tips

This article provides some advice about commenting code. Its not specific to PeopleSoft, but the examples relate to working with PeopleCode and customisations.

Commenting Principles

The point of code comments is to make code easy for other humans (including yourself) to read and refer to at a later date.

Your code should aim to be self-documenting.

This means writing your code so that things like function names, variable names and constants make it easy to understand. Comments should be used sparingly to explain code because your code should be self-explanatory. You aren't trying to teach someone how to write code. Comments throughout standard code blocks are a form of code smell. That this, they suggest the code could be written more concisely or needs refactoring due to complexity.

Remember code tells you how, and comments tell you why.

The other major use of comments in existing systems are to tag customisations and changes to delivered code.

Commenting out Delivered PeopleCode

At some point you'll need to comment out delivered PeopleCode due to a change. The general consensus is that because PeopleCode does not have version control (unless you are using a 3rd party product), you should comment out instead of removing delivered code.

The main point of this is for patching exercises later on. Remember that because comments should tell you why, you should state why you made changes to delivered code in your comments!

Here's the style I generally use for commenting out delivered code:

Rem REF99999 DD/MM/YYYY Author's Name: Comment out Delivered Code ->;
<*
... Delivered Code ...
*>
Rem REF99999 DD/MM/YYYY Author's Name: Comment out Delivered Code <-;

The Rem statements are the markers for the start and end of the change. Hopefully you are using some sort of reference number for your code changes that are tracked somewhere. The <* *> comment operators work the best as they can encompass existing comments and application designer will still perform the correct syntax highlighting for the comment block.

Comment Blocks

A lot of people are fond of using large numbers of characters (dashes, asterisks etc) to surround their comment blocks. So you see comment blocks (especially in headers) like this:

/******************************************************************************
 * REF99999 Modified Widget Y in Sprocket Z                                   *
 * Author: Developer's Name or Initials                                       *
 * Date:   DD/MM/YYYYY                                                        *
 *                                                                            *
 * Widget Y doesn't work in Sprocket Z, which is why it was changed.          *
 ******************************************************************************/

It might look nice, but I find this an absolute nightmare! Why are all those asterisks necessary? It is such a pain to put them in and get them sitting right, and they don't look the same in every editor anyway depending on tab/space settings.

Why not just have this? It's functionally the same, takes a fraction of the time to do (so you can focus on content rather than the presentation of those asterisks) and is far easier to modify later.

/*
    REF99999 Modified Widget Y in Sprocket Z
    Author: Developer's Name or Initials
    Date:   DD/MM/YYYYY
 
    Widget Y doesn't work in Sprocket Z, which is why it was changed.
*/

As with all programming, simplify when you can. Also, most editors have some form of code formatting. If that is available to you, use that. Comment formatting should be something your editor does automatically for you if possible.

Convert Emails to MailTo Links

The following PeopleCode function finds email addresses (using a regular expression) and encloses them in a anchor hyperlink tag with a mail to link.

So for example the text: test@peoplesoftwiki.com is replaced with the HTML:

<a href="mailto:test@peoplesoftwiki.com">test@peoplesoftwiki.com</a>

This is the PeopleCode function:

Constant &DOMAIN = "peoplesoftwiki.com";
 
Function ConvertEmailToLinks(&sInput As string) Returns string;
 
   Local JavaObject &jEmailRegex, &jEmailLinkRegex, &jInput;
   Local string &sHTML;
 
   &jEmailRegex = CreateJavaObject("java.lang.String", "[\w-.]+@(\w+.|)" | &DOMAIN);
 
   /* Replace the found match ($0 back reference) and enclose with a href mailto HTML tag */
   &jEmailLinkRegex = CreateJavaObject("java.lang.String", "<a href=""mailto:$0"">$0</a>");
 
   /* Store the text to be scanned for email addresses as a Java string object */
   &jInput = CreateJavaObject("java.lang.String", &sInput);
 
   /* Perform a replace all using the search email regex and the email link regex */
   &sHTML = &jInput.replaceAll(&jEmailRegex, &jEmailLinkRegex);
 
   Return &sHTML;
 
End-Function;
NOTE: Remember to replace the constant, &DOMAIN with your organisation's base domain. The search regex in this example may need changing to cater for more specific email formats.

There are three key parts to this code.

The first part is the regular expression stored in the Java object &jEmailRegex which is converted to:

[\w-.]+@(\w+.|)peoplesoftwiki.com}}

This matches any email address that ends with your base domain (@peoplesoftwiki.com), or has one subdomain in it (@test.peoplesoftwiki.com) and is preceded by a word that can include a full stop or a hyphen in it (e.g. test-person@peoplesoftwiki.com or test.person@peoplesoftwiki.com).

Underscores are automatically catered for by the \w word match expression.

You cam voew this regex in action using RegExr. Adjust and test to ensure it matches the email address format used in your organisation.

regexr-email-address-test.png

NOTE: the last example with the asterisk character which only does a partial match.

The second key part of this code is the Java object &jEmailLinkRegex. This regex is what performs the conversion to a hyperlink:

<a href="mailto:$0">$0</a>

The $0 in this code is a back reference and evaluates to the result of the search regex (&jEmailRegex). So it is replaced by the email address found.

The third key part is the call to the replaceAll method. This is what causes all matching email addresses to be converted, taking the search regex (&jEmailRegex) and replacing it with the link regex (&jEmailLinkRegex).

Transfer

Transfer is one of those PeopleCode functions that needs more explanation and some better examples.

This is the official syntax:

Transfer(new_instance, MENUNAME.menuname, BARNAME.barname, ITEMNAME.menu_itemname,
PAGE.component_item_name, action [, keylist] [, AutoSearch]);

New Instance can be either true or false. True means you want to create a new window, false means that it will reuse the existing window.

To figure out the values for MENUNAME, BARNAME, ITEMNAME, and PAGE, query the PSAUTHITEM table. You can use the following query provided you know the menu and page names. The more you know, the better. Remember that this returns all permission lists (CLASSID) associated with menu item:

select * 
from PSAUTHITEM
where MENUNAME = 'YOUR_MENU'
and PNLITEMNAME = 'YOUR_PAGE'

The corresponding values in this table are:

You have the following actions available (%Action):

Make sure that the users will have the appropriate action available on the page you are transferring to. This is where the AUTHORIZEDACTIONS field in the PSAUTHITEM table can be handy - this tells you which authorized actions correspond to which permission lists.

Here's a simple example of how to transfer to the application entry component in Campus Solutions, reusing the existing window and entering in add mode:

Transfer(False, MenuName."PROCESS_APPLICATIONS", BarName."USE", 
ItemName."APPLICATION_DATA_ENTRY", Page."SCC_BIO_DEMO_PERS", "A")

If the user does not have access to a particular component, transfer will give an error like this:

You are not authorized for this page.

Check that the user has access to at least one of the relevant permission lists for where they are being transferred.

A transfer can also take you to a blank page, even though you have specified a valid menu item. In this case:

One way this can happen is if the search record has been overridden at the menu level and the search record does not exist/has not been built in application designer.

NOTE: a trace may not necessarily tell you that the record is missing/not built so be sure to check your menu search record and your component search record both exist.

Tracing and Debugging

In the set trace flags on the signon page use Each Statement or you can configure this through:

PeopleTools > Utilities > Debug > Trace PeopleCode

Use Show Each to log all the PeopleCode that fires during your trace. This will tell you what PeopleCode and events are firing (and what is not firing) and is usually enough to give you an idea of what is going on. The best thing is that this trace produces a much smaller log file than selecting most of the other PeopleCode trace settings, making the PeopleCode trace manageable! Remember when it comes to tracing/logging, less is more.

Another way to set up specific tracing in Your PeopleCode is by using the SetTraceSQL and SetTracePC functions. For example:

/* Turn on tracing */
SetTracePC(3596);

/* PeopleCode you want to trace */

/* Turn off tracing */
SetTracePC(0);

Also, here's a simple example of how to log to a file in your PeopleCode if you need it.

&fileLog = GetFile("C:\temp\LOGFILE.log", "w", "a", %FilePath_Absolute);
&fileLog.WriteLine("Begin");
&fileLog.WriteLine("End");
&fileLog.Close();
It is a bad idea to use hardcoded absolute paths so adjust this code accordingly.

Encryption

The following functions are provided as built-in functions in PeopleCode for encryption/decryption:

The syntax for these functions:

Encrypt (KeyString, ClearText) returns CipherText
Decrypt (KeyString, CipherText) returns ClearText
Hash (ClearText) returns CipherText
EncryptNodePswd(ClearText) returns CipherText

Ciphertext is the term used in cryptography to refer to text once it has been encrypted. Cleartext is the plain text before any encryption is applied.

The Encrypt and Decrypt functions rely on a key string which is used as part of the encryption. Note that the key string can be blank so you can simply issue the commands Encrypt(ClearText) and Decrypt(CipherText). It is a good idea to trim spaces from the start and finish of your clear text.

Spaces are included in the encryption so the cipher text will be different if your clear text includes a space to if it didn't include a space.

Use rtrim and ltrim like this to remove spaces:

&strCipherText = encrypt("", rtrim(ltrim(&strClearText)));

The Hash function can only be used to encrypt clear text (one way) - PeopleSoft doesn't provide the equivalent decryption function (for good reason). The Hash function is the same algorithm (and key) that is used to encrypt the passwords in the OPERPSWD field in the PSOPRDEFN table (user passwords).

Similarly EncryptNodePswd can only be used to encrypt clear text. It is used to encrypt the password used by Integration Broker nodes. This is the encrypted password stored in the IBPASSWORD field in the PSMSGNODEDEFN table.

The encrypted ACCESSID and ACCESSPSWD stored in PSACCESSPRFL use whatever algorithm is run when issuing the CHANGE_ACCESS_PASSWORD command in data mover. The ACCESSID is used as the key for the encryption algorithm. This is the symbolic ID configured when installing PeopleSoft.

The encrypted passwords stored in the application server configuration (psappsrv.cfg) and process scheduler configuration (psprcs.cfg) files use the same internal algorithm as the CHANGE_ACCESS_PASSWORD command in data mover. This is the case for other applications such as configuration manager and application designer.

The integrationGateway.properties file contains encrypted passwords for each node. When accessing this through advanced gateway properties, you are provided with an area to generate passwords. Alternatively you can run the delivered password encryption utility, PSCipher. This is a Java program found on your PeopleSoft web server.

The syntax for encryption is (Windows and Unix):

pscipher.bat ClearText
pascipher.sh ClearText

For security reasons you should generate a unique PSCipher encryption key using:

pscipher.bat -buildkey
pascipher.sh -buildkey
The key version is presented at the start of the encrypted password: {V1.1} if a unique key has not built (default). Otherwise it will be {V1.2} or above.

Multilevel Rowsets

A multi-level rowset is one that has a parent-child-grandchild relationship structure. Such rowsets can be really handy when you need to emulate the structure of the component buffer (scroll levels).

For this example I'm going to use a structure based on PeopleTools security - the USER_ROLES page in the USERMAINT component which is located at:

PeopleTools > Security > User Profiles > User Profiles > Roles (tab)

The reason I'm using PeopleTools security is that it is available for all PeopleSoft applications (HRMS, Campus Solutions, Finance etc).

A simplified version of the scroll structure in the USER_ROLES page is:

+ Level 0: Operator ID (PSOPRDEFN)
  - Level 1: Role (PSROLEUSER_VW)

What I want is to create a rowset that emulates this scroll structure so that I can store the roles a particular user may have.

The first step is to declare the rowsets and use an appropriate naming convention and suffix with the scroll level which I makes sense:

Local Rowset &rsUser0;
Local Rowset &rsRoles1;
Local Rowset &rsUserRoles;

Rowset &rsUser0 is for PSOPRDEFN, &rsRoles1 is for PSROLEUSER_VW and &rsUserRoles is the multi-level rowset with the structure we want.

Now we need to create the standalone rowsets for level 0 and level 1 in our scroll structure:

&rsUser0 = CreateRowset(Record.PSOPRDEFN);
&rsRoles1 = CreateRowset(Record.PSROLEUSER_VW);

Finally we can create the multi-level rowset:

&rsUserRoles = CreateRowset(&rsUser0, &rsRoles1);

This gives us a rowset where each user (operator ID) can have one or more roles. In our example, we could then populate the standalone rowset from the component buffer like so:

Local Rowset &rs0;
&rs0 = GetLevel0();
&rs0.CopyTo(&rsUserRoles);

You might have a case where you want multiple children at a scroll level. Here's another example using the PSOPRALIAS page on the USERMAINT component.

The structure this time is:

+ Level 0: PSOPRDEFN
  - Level 1: PSOPRALIAS
  + Level 1: PSOPRALIASTYPE
    - Level 2: PSORPALIASFIELD

Now we have three scroll levels and two children at the same scroll level (scroll 1 has PSOPRALIAS and PSOPRALIASTYPE).

Once again we start with the standlone rowsets for each record in the scroll structure. However this time, using a bottom-up approach we link the lower scroll levels to the higher scroll levels as follows:

Local Rowset &rsUser0, &rsAlias1, &rsAliasType1, &rsAliasField2;
 
&rsAliasField2 = CreateRowset(Record.PSOPRALIASFIELD);
&rsAliasType1 = CreateRowset(Record.PSOPRALIASTYPE, &rsAliasField2);
&rsAlias1 = CreateRowset(Record.PSOPRALIAS, &rsAliasType1);
&rsUser0 = CreateRowset(Record.PSOPRDEFN);

Now to create the multilevel rowset.

Local Rowset &rsUserAlias;
&rsUserAlias = CreateRowset(&rsUser0, &rsAlias1);

When you are referencing anything below scroll 0, you need to use the same syntax as you would when populating a rowset from the component buffer.

For example, if I wanted to populate the PSOPRALIAS (scroll 1) of the &rsUserAlias multilevel rowset with the Rowset fill method (for standalone rowsets) for just the PS user, the code would look like this:

&rsUserAlias(1).GetRowset(Scroll.PSOPRALIAS).Fill("where OPRID = 'PS'");

The same applies for getting to a particular field. For example, say I wanted to message out the field PSOPRALIAS.OPRALIASTYPE. This would be the code to use with the &rsUserAlias multilevel rowset:

MessageBox(0, "", 0, 0, "Operator Alias Type = " 
| &rsUserAlias(1).GetRowset(Scroll.PSOPRALIAS)(1).PSOPRALIAS.OPRALIASTYPE.Value);
Going down to level 2 or 3 can get quite confusing! That's why it is easier to do the manipulation to the underlying rowsets then combine them together at the end into a multilevel rowset.

Getting information out of the multilevel rowset still provide the same challenges though. Which is why you might want to create scroll level rowsets that reference scroll levels on your multilevel rowset. The previous examples can be simplified this way by creating &rsUserAlias1 which references the PSOPRALIAS scroll at level 1 on the &rsUserAlias multilevel rowset.

Local Rowset &rsUserAlias1;
 
&rsUserAlias1 = &rsUserAlias(1).GetRowset(Scroll.PSOPRALIAS);
 
MessageBox(0, "", 0, 0, "Operator Alias Type = " &rsUserAlias1.PSOPRALIAS.OPRALIASTYPE.Value);

Remember, when it comes to rowsets, its all about context!