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.

Expanding your Meta-SQL for validation

PeopleCode META-SQL getting the expanded statement

I couldn't find a way to get PeopleCode to log or dump the expanded SQL. This is necessary to confirm the META-SQL has expanded into the query you expected. I finally found a way to get your expanded SQL.  Here is how you do it.

1. Create a new SQL Definition
2. Paste your SQL syntax form your Application Engine PeopleCode
3. Replace all your variables with a dummy values or in the case of a record holder put the record name
4. Right click on the query screen and select "Resolve Meta Data"
5. Look down at the output Meta SQL screen.  Voila expanded full SQL.

Categories and search screen with Long Translate values only

I had a mapping table that each group of mapping belonged to a category.  The requirement was to make a search page for just the category displaying the long description.  For each category the user could add multiple mappings.  Here is how I created the custom search page for just a list of category translate values.

Field

Create my category field MCM_ADS_CAT which had about a dozen translate values.

Record

My page required a parent record that included my new category, effective date and effective status.  The child record obviously included category, effective date followed by my addition fields for the mappings.

Page

I created a page to edit the listings within each category or Translate grouping and effective date.  This page requires 2 scroll levels to work properly the first level being the parent record and the second level the grid is the child data.  The initial search record or ADS Category you see at the top is actually a View that I will describe later.

Component

The component includes the new page but for the search record we are going to use a new view that selects the distinct values from our parent record.

View

The view is where I need to get the distinct values along with the long description translate values.  This view will be used as the search for record on my new component.  The view includes 2 fields my MCM_ADS_CAT and the XLATLONGNAME.  Here is the SQL to get the distinct in use values with long description from the translate items table PSXLATITEM.

SELECT DISTINCT aa.mcm_ads_cat, bb.xlatlongname 
  FROM ps_mcm_ads_eff aa, psxlatitem bb 
 WHERE aa.mcm_ads_cat = bb.fieldvalue
   AND bb.fieldname = 'MCM_ADS_CAT'
   AND bb.effdt = ( SELECT MAX(cc.effdt) FROM psxlatitem cc 
                    WHERE bb.fieldname = cc.fieldname 
                      AND bb.fieldvalue = cc.fieldvalue 
                      AND cc.eff_status = 'A')

Now on your view record field set the following properties to use only the long translate value on the search page:

 

ADS_CATEGORY

• Key Yes
• Search Key Yes
• List Box Item No

XLATLONGNAME

• Key No
• Search Key No
• List Box Item Yes

Search Page Results

Peoplecode experimenting with pages

In need to hack up a page?

I'm often in need to experiment or completely hack up a page trying to figure out a different way to solve a specific problem.  I don't really want to delete and destroy a working copy of a page in case my idea doesn't work.  So I found this great way to build a addition pages and flip between them.

Steps

1. Build or use an existing working page already in a registered component.
2. Open your page and "Save As" a second copy with a new name.
3. Drag the page to your Component.
4. Using the Hidden checkbox you can hide your original page and use your new page.

Now you can hack up your new page as much as you like and even go back and view your original page just by flipping the Hidden value on your component.