SQLExec for Number Column

The Snag

Today I was hitting a problem using the SQLExec function with a query against a single number column.  I needed to retrieve a specific value for an employee and if no rows are found my program would skip this employee.  The logic seemed simple enough something like the following:

SQLExec(SQL.MCM_SNLF_BN_RCD_NBR, &employee, &EmplRcd);
If None(&benefitEmplRcd) Then
   MessageBox(0, "", 20002, 4, " No Employee Record skip employee");
   &record = Null;
   Return &record;
End-If;

BAD! 

When you run this code the query is against a Number column and that number column even if no record is found will return a 0 not a null and your IF condition will never be true.  Also if zero is a valid value for Employee record then code after using that value could execute against all the wrong data.

Solution

My solution to this is pretty easy simply include another key value of your query in my case Employee ID which will never be empty and also a String value.  Use that column as your check for existence instead of the number column.

Local  &emplidExists;
SQLExec(SQL.MCM_SNLF_BN_RCD_NBR, &employee, &emplidExists, &EmplRcd);
If None(&emplidExists) Then
   MessageBox(0, "", 20002, 4, " No Employee Record skip employee");
   &record = Null;
   Return &record;
End-If;

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

Long readable SQL in peoplecode

Readable SQL statements

Problem 

First I'm going to admit when it comes to my code I'm a minimalist.  I like short clean code and I often use any shortcuts available in a language.  However, I will not sacrifice readability only to have the smallest line count.  Some languages you could get away with writing an entire function in 1 return statement but is a nightmare to read or modify.  For a while now I've been in pursuit of how to deal with long SQL statements in PeopleCode.  There are a few problems with this language that make writing long friendly SQL statements.  There is no way to continue a string on multiple lines without string concatenations.  There is not a shorthand append string that some languages have such as += either.

&sqlQry = " select * from PS_JOB A";
&sqlQry = &sqlQry | " where emplid = :1 and empl_rcd = :2 ";
&sqlQry = &sqlQry | " and effdt = (select max(effdt) from PS_JOB B";
&sqlQry = &sqlQry | " where A.emplid = B.em... ";

You can see where this is going. It's not to bad approach, however your indentation is off and your long query names waste space on that could be used for the query itself and actually reduce the lines you need. I finally settled with using &qry but I still wasn't satisfied with the doing the concatenation method on every line. What if I needed multiple queries and wanted a more descriptive name.

My solution

Here is my approach for now until they come up with += shortcut for string concatenation or a way to build a string on multiple lines.  I define a very simple several line short named variable scheme (&sq1, &sq2...) for each query line.  Then string the set together into the longer descriptive name and reuse them on my next query if needed.

Local string &sq1, &sq2, &sq3, &sq4, &sq5;
Local string &queryJobs, &queryEmpl;

/* query the jobs table */ 
&sq1 = " select * from PS_JOB A";
&sq2 = " where emplid = :1 and empl_rcd = :2 ";
&sq3 = " and effdt = (select max(effdt) from PS_JOB B ";
&sq4 = "            where A.emplid = B.em.....  ";
&queryJobs = &sq1 | &sq2 | &sq3 | &sq4;

/* query the Employee table */
&sq1 = " select * from PS_employees A";
&sq2 = " where emplid = :1 and empl_rcd = :2 ";
&queryEmpl = &sq1 | &sq2;

Just remember to put a space in at the beginning and/or ending of every line.

Using SQL Object

An alternative way is to use an SQL object. Just create a new SQL object from the menu File/New/SQL. Enter you valid SQL statement with the parameters you wish again using the same ":1" placeholders. Then call that SQL in your peoplecode with SQLEXEC and pass your parameters values in as shown below.

Local Record &rec = CreateRecord(Record.JOB);
SQLExec(SQL.MYSQL_JOB, &emplid, &rcd, &rec);

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.