The PeopleSoft State Records and Run Controls

Goal

We have a process that generates an Export file.  The following are some of the simple requirements for this process request.

• There is a need to allow the customer to request this file at any time and to provide the File Name of the file. 
• If the file exists the process will fail with a status of "No Success" so we will provide a Delete Flag for the requester to have the existing one deleted if found.
• A header in the export file needs a Sequence Number from the requester

Overview

You can think of the state record as the record of assigned variables that exists for an single instance of an application engine execution process.  Each execution will have its own record and can be unique to that process run.  A state record is passed variables from the process request record commonly called the Run Control.  There can also be fields on a state record to indicate process completion status or restart positions or any other useful information you'd like to store between executions or restarts.  To be able to restart using previous state records you need to commit your state record to the database in a physical table.  In many cases this is not required so a Derived record is used and will only exist in memory during that execution.  In this example I'm going to demonstrate a very simply run control and state record where we can pass in variables like the desired file name for output.

Records

Run Control

We are going to need two records the first is the Run Control which will be a physical table to store each user's values for this Application Engine run requests.  It is an SQL table that must have two keys the RUN_CNTL_ID and OPRID.  These fields are required because this table is a child of PRCSRUNCNTL which to my knowledge serves little purpose because the only other columns on the parent table are language codes.  After this you can add any fields that you need to pass into your application Engine.  In this example we are passing in FILENAME, SEQNUM and DELETE_FLAG

State Record

For the second record the state record we are NOT going to use a SQL table because we don't need to commit and retain process information after completion of this Application Engine process.  So your record can be set to Derived which means it will only exist in memory during the execution process.  This record must have a single key column PROCESS_INSTANCE. Add additional fields that your Run Control is passing to the execution process or Application Engine status flags you wish to set in your PeopleCode.

* Note: AE_APPSTATUS is just one example if a field which gives developers a way to flag the return status of this process.  It's a way to flag the run as Success, No Success or Warnings using your own peoplecode.

Run Control Page

This page is where the customer requests the run of this Application Engine Process.

In this example I'm going to pass into my Application Engine process 3 variables.  File Name, Sequence Number and a Delete Flag value.  At any time in my application Engine I will have the ability to query a copy of these values stored on my state record during that instance of processing.  The page is built by copying an existing Run Control page and this will ensure the correct Run Control sub pages are in place.  These sub pages are what display the ID, Language, Report Manager, Process Monitor and Run button on your new page.

We are only making changes to the title and our custom Run Control fields in Yellow and we leave the subpage and Derived titles as is.

Component

The search page assigned to this Run Control component is the parent table PRCSRUNCNTL this means that a single Run Control ID can be used across all your different Run Control pages.

The table structure shows how the parent table links to each individual run control record and that run control record is copied into your Process Instance State Record during your run.

Application Engine

Now you are ready to put this to use in your Application Engine.  First you need to open the properties of your App Engine and add your State Record.

To populate the values of your state record at runtime you need to have an SQL step at the very top of you Application Engine.  This first SQL action will query your Run Control table with the requester's ID and Run Control ID and copy the fields with the same names to your state record.  The follow step can be your first Application Engine PeopleCode process.  This is where you can access your state record and to use the values passed in.

SQL

%Select(FILENAME, SEQNUM, DELETE_FLAG) 
 SELECT FILENAME, SEQNUM, DELETE_FLAG   
 FROM PS_MCM_TRN_DS_RC 
 WHERE OPRID = %OperatorId AND RUN_CNTL_ID = %RUNCONTROL

Now that the State record is has your values they can be access in any of the PeopleCode used within the current Application Engine process.  The following code will just display it in the log file but you can use the values in queries, properties, settings or generating a file with a specific name.

PeopleCode

MessageBox(0, "", 0, 0, "Application Engine! ");
MessageBox(0, "", 0, 0, "State Record FILENAME = %1", MCM_TRN_DS_AET.FILENAME);
MessageBox(0, "", 0, 0, "State Record SEQNUM = %1", MCM_TRN_DS_AET.SEQNUM);
MessageBox(0, "", 0, 0, "State Record DELETE_FLAG = %1", MCM_TRN_DS_AET.DELETE_FLAG);

Setting up the right titles

Understanding the Titles

Starting out with PeopleSoft I was always getting confused with what title/label/heading is put on what screen or menu option. Hopefully this reference will help straighten out the confusion.

Menu

The menu title on the comes from the Structure and Content short label reference.  This is also set in if you use the component registration wizard when setting up which folder you component will live under.

Search Page Title

The search page and add new page title comes from the Menu item label.  This is found in your custom menu and typical under a menu item called "Use".

Page Tab Label

The tab title on your page is from the Item Label on your component.

Custom Display Date format

Date Format

In order to use a customized date format such as "January 14, 2014" in PeopleCode you need to use the function DateTimeToLocalizedString(&SupportDT, "MMM d, yyyy"); The second parameter is a pattern you define using any of the documented symbols found in the function documentation.

Local String &SupportText;
Local Date &SupportDT = %Date;
&SupportText = DateTimeToLocalizedString(&SupportDT, "MMM d, yyyy");

Thanks Ernie for sharing this one!

PeopleCode information Queries

Queries for PeopleSoft Objects

Page information and objects

-- Information and Properties on a page
select * from pspnlfield 
where pnlname = 'EXTRA_ACTIVITY_TBL';

All records where a Field is used

-- Find all the Records a field is used
SELECT DISTINCT recname, fieldname FROM psrecfield 
WHERE  fieldname = 'ELEVENTH_GRADE';

All the pages that a field exists on

-- Find all the pages where a field is used
SELECT pnlname FROM pspnlfield 
WHERE recname='EXTRACUR_ACTVTY' AND fieldname = 'DESCR';

All the pages that a record is used on

-- Find pages a record is used on
SELECT DISTINCT pnlname FROM pspnlfield 
WHERE recname='EXTRACUR_ACTVTY';

Check for outstanding Run Controls

Simply change this to a delete if you have a process that failed and you want to restart from the beginning.

SELECT * FROM ps_aeruncontrol 
WHERE oprid = 'YOURUSERID';