Using PeopleCode in Application Engine Programs

Inserting PeopleCode into Application Engine programs enables you to reuse common function libraries and improve performance. In many cases, a small PeopleCode program used instead of Application Engine PeopleCode is an excellent way to build dynamic SQL, perform simple If/Else edits, set defaults, and perform other tasks that do not require a trip to the database.

Action Execution Order

A step can contain only one PeopleCode action because no other types of actions are required within a step in conjunction with a PeopleCode action (or program). If you include other actions with your PeopleCode action within the same step, keep in mind the hierarchy when you run it.

With PeopleCode actions, Application Engine runs the PeopleCode program before the SQL, Call Section, or Log Message actions, but a PeopleCode program runsafter any program flow checks.

Because multiple action types exist, they must execute in agreement within a system; therefore, the order in which actions execute is significant. At runtime, actions defined for a given step are evaluated based on their action type. All of the action types exist within a strict hierarchy of execution. For example, if both a Do When action and a PeopleCode action exist within a given step, then the Do When action always runs first.

The following example shows the sequence and level of execution for each type of action:

Image: Example of action execution hierarchy

This is an example of action execution hierarchy.

Application Engine is not intended to run programs that include only PeopleCode actions. The primary purpose of Application Engine is to run SQL against your data.

Use PeopleCode primarily for setting If, Then, Else logic constructs, performing data preparation tasks, and building dynamic portions of SQL statements; rely on SQL to complete the bulk of actual program processing. Also use PeopleCode to reuse previously developed online logic. PeopleCode is the tool to use to take advantage of new technologies such as component interfaces and application classes.

Most programs must verify that a certain condition is true before they run a particular section. For example, if the hourly wage is less than or equal to X, do Step A; if not, fetch the next row. In certain instances, you must modify variables that exist in a state record. PeopleCode enables you to set state record variables dynamically.

Avoid rowset processing in an Application Engine program. Loading data into a rowset can use a significant amount of memory, which this formula approximates:

where

• mem is the amount of memory required to store the rowset.

• nrows is the number of rows.

• row overhead is the overhead per row.

• nrecords is the number of records per row.

• rec overhead is the record overhead (approximately 40 bytes).

• nfields is the number of fields in the record.

• field overhead is the overhead per field (approximately 80 bytes).

• average cumulative fielddata is the average amount of data per field.

Using this formula, a rowset containing 500,000 rows with one record per row, 50 fields, and 200 bytes per field would require approximately 2.3 gigabytes of memory.

When writing or referencing PeopleCode in a PeopleCode action, you must consider the environment in which the Application Engine program runs. Environment indicates the differences between online and batch modes. Application Engine programs usually run in batch mode; consequently, your PeopleCode cannot access pages or controls as it can while running in online mode. Any PeopleCode operations that manipulate pages will not run successfully. Even if you invoke your Application Engine program online from a record or a page using the CallAppEngine PeopleCode function, the Application Engine PeopleCode still does not have direct access to component buffers.

Any record field references that appear in a PeopleCode action can refer only to fields that exist on an Application Engine state record. Component buffers, controls, and so on are still inaccessible even if you define the page records as state records in the Program Properties dialog box. An Application Engine program can access only state records or other objects you create in PeopleCode.

However, you do have several options for passing data from a component buffer to an Application Engine program: you can use the CallAppEngine PeopleCode function or you can define global variables.

Running PeopleCode from Application Engine steps enables you to complete some simple operations without having to use SQL. For example, to assign a literal value to an Application Engine state record field using SQL, you may have issued a statement similar to this one:

%SELECT(MY_AET.MY_COLUMN)
SELECT 'BUSINESS_UNIT' FROM PS_INSTALLATION

You can use a PeopleCode assignment instead:

MY_AET.MY_COLUMN = "BUSINESS_UNIT";

Similarly, you can use a PeopleCode If statement instead of a Do When action to check the value of a state record field.

When accessing state records with PeopleCode, keep in mind that:

• State records are unique to Application Engine programs.

• Within Application Engine PeopleCode, state record values can be accessed and modified using the standard recordname.fieldname notation.

From PeopleCode, you can trigger an error status, or false return, by using the Exit function. Use the On Return value in the PeopleCode action properties to specify how your Application Engine program behaves according to the return of your PeopleCode program. This example shows the On Return property:

Image: Example of On Return action property

This example illustrates the fields and controls on the Example of On Return action property.

By default, the program terminates, similar to what happens when a SQL error occurs. By changing the On Return value to Skip Step, however, you can control the flow of your Application Engine program.

You can use Exit to add an If condition to a step or a section break. For example:

If StateRec.Field1 = ‘N’
Exit(1);
Else
/* Do processing */
End-if;

You must specify a non-zero return value to trigger an On Return action. The concepts of “return 1” and “return True” are equivalent; therefore, if the return value is non-zero or True, then Application Engine performs what you specify for On Return, as in Abort orSkip Step. However, if the program returns zero or False, Application Engine ignores the selected On Return value.

You can insert PeopleCode inside of a Do loop, but be careful when using PeopleCode inside of high-volume Do loops (While, Select, Until). Minimize the number of distinct programs inside the loop. You should avoid having PeopleCode perform the actual work of the program and instead use it primarily to control the flow (If, Then logic), build dynamic SQL, or interact with external systems.

Using bind variables instead of literals to pass values to SQL statements is essential in PeopleCode loops or if the PeopleCode is called in a loop. If the PeopleCode loops, Application Engine probably will use a dedicated cursor, which saves the overhead of recompiling the SQL for all iterations. If the PeopleCode is called from within a loop, Application Engine does not reduce the number of compiles, but it avoids flooding the SQL cache (for those database servers that support SQL cache) when it uses bind variables. Do not use bind variables for values in a Select list or for SQL identifiers, such as table and column names, as some databases do not support them.

On database platforms for which this feature is implemented, setting BulkMode to True often results in significant performance gains when inserting rows into a table within a loop.

In general, avoid PeopleCode calls within a loop. If you can call the PeopleCode outside of the loop, use that approach to increase overall performance.

The AESection PeopleCode class enables you to change the properties of an Application Engine program section dynamically, without having to modify any of the Application Engine tables directly. This capability enables you to develop rule-based applications that conform dynamically to variables that a user submits through a page, such as the Application Engine Request page.

The AESection class provides the following flexibility:

• Portions of SQL are determined by checks before a run.

• The logic flow conforms as rules change, and the program adjusts to the rules.

When using an AESection object:

• Ensure that you require primarily dynamic capabilities with the SQL your program generates.

• Ensure that the rules to which your program conforms are relatively static or at least defined well enough that a standard template could easily accommodate them.

• Consider using SQL definitions to create dynamic SQL for your programs to avoid the complexity created by the AESection object using the StoreSQL function.

• The AESection class is designed to dynamically update SQL-based actions only, not PeopleCode, Call Section, or other actions.

  You can add a PeopleCode action to your generated section, but you cannot alter the PeopleCode.

• The AESection class is designed to use for online processing.

  Typically, dynamic sections should be constructed in response to a user action.

To make synchronous online calls to an Application Engine program, use the PeopleCode function CallAppEngine.

If an Application Engine program called by CallAppEngine terminates abnormally, the user receives an error, similar to other save time errors, that forces the user to cancel the operation. The CallAppEngine function returns a value based on the result of the Application Engine call. If the program was successful, it returns a zero; if the program was unsuccessful, it returns a value other than zero.

The file layout class enables you to perform file input and output operations with Application Engine using PeopleCode. A file object enables you to open a file (for reading or writing), read data from a file, or write data to it. Using the combination of the file class and Application Engine provides an effective method to integrate (or exchange) the data stored in a legacy system with your PeopleSoft system. The file class facilitates the creation of a flat file that both your legacy system and Application Engine programs support.

An Application Engine program running on the application server uses a file object to read the file sent from the legacy system and to translate it so that the file can update affected PeopleSoft application tables. For the PeopleSoft system and the legacy system to communicate, you first must construct a file object that both systems can use to insert and read data.

Attain rowset and record access for a file using a file layout definition. You create the file layout definition in Application Designer, and it acts as a template for the file that both systems read from and write to. This file layout definition simplifies reading, writing, and manipulating complex transaction data with PeopleCode.

Generally, use the file class and Application Engine combination when you cannot implement the PeopleSoft Integration Broker solution.

Using the PeopleCode RemoteCall function, you can call COBOL modules from a PeopleCode action. This option supports existing Application Engine programs that call COBOL modules. You also can use it to upgrade Application Engine programs from previous releases.

RemoteCall ("PSRCCBL",?
 "PSCOBOLPROG", "PTPECOBL",?
 "AECOBOLPROG", "MY_GNT",?
 "STATERECORD", "MY_AET",?
 "PRCSINST", MY_AET.PROCESS_INSTANCE,?
 "RETCODE", &RC,?
 "ERRMSG", &ERR_MSG,?
 "FIELD1", MY_AET.FIELD1,?
 "FIELD2", MY_AET.FIELD2);

You can call all of the PeopleTools APIs from an Application Engine program. When using APIs, remember that:

• All the PeopleTools APIs contain a Save method.

  However, when you call an API from your Application Engine program, regardless of the Save method of the API, the data is not saved until the Application Engine program issues a commit.

• If you called a component interface from an Application Engine program, all the errors related to the API are logged in the PSMessage collection associated with the current session object.

• If you sent a message, errors are written to the message log and the Application Engine message log.

• If an Application Engine program called from a message subscription PeopleCode encounters errors and the program exits (with Exit (1)), the error is written to the message log and is marked as an error

This function commits pending changes (inserts, updates, and deletes) to the database. When using CommitWork, remember that:

• This function can be used only in an Application Engine program that has restart disabled.

• The CommitWork function is useful only when you are processing SQL one row at a time in a single PeopleCode program, and you need to commit without exiting the program.

  In a typical Application Engine program, SQL commands are split between multiple Application Engine actions that fetch, insert, update, or delete application data. You use the section or step level commit settings to manage the commits.

If the Process Scheduler is booted using a shared drive on another machine and you intend to call a WINWORD mail merge process from Application Engine, then you must do one of the following to ensure successful completion:

1. Configure the Process Scheduler to run Application Engine programs using psae instead ofpsaesrv.

2. Ensure the generated document is saved locally, not on a shared network drive.

The following topics provide examples of common ways that you can use PeopleCode within Application Engine programs.

%SELECT(EXISTS) SELECT 'Y' FROM PS_INSTALLATION WHERE %BIND(TYPE) = 'X'), 

If TYPE = 'X' Then
   Exit(0);
Else
   Exit(1);
End-if;

%SELECT(AE_WHERE1)
SELECT 'AND ACCOUNTING_DT  <= %Bind(ASOF_DATE)'

AE_WHERE1 = "AND ACCOUNTING_DT  <= %Bind(ASOF_DATE)";

SEQ_NBR = SEQ_NBR + 1;