PeopleSoft Application Engine Programming

PeopleSoft Application Engine

You use PeopleSoft Application Engine to develop batch or online programs that perform high-volume, background processing against your data.
While PeopleSoft Application Engine does not generate, parse, or understand SQL, it does execute SQL (Meta-SQL) that you provide.
It's a Structured framework or shell for executing SQL.
PeopleSoft Application Engine is designed to help you develop, test, and run background SQL processing programs (programs which do not require user intervention). PeopleSoft Application Engine offers you an alternative to writing COBOL, or SQR (Structured Query Reporter) programs for background SQL processing.
PeopleSoft Application Engine comprises two distinct components—a designer where you define your batch program and the runtime environment where you run and monitor your program.
In PeopleSoft Application Engine, a program is a set of

SQL statements,
Log Statements,
PeopleCode, and
Program control actions that enable looping and conditional logic.

You can use PeopleSoft Application Engine for straight, row-by-row processing, but the most efficient Application Engine programs are written to perform set-based processing.

Application Engine Program Elements

A PeopleSoft Application Engine program comprises the set of processes to execute a given task, and is made up of several key elements:

Sections.
- A section is a set of ordered steps that gets executed as part of a program.
- You can call sections (and other programs) from steps within other sections.
- A program must contain at least one section.
- The execution of the program always starts with the section defined as MAIN .
Steps.
- Used to execute a PeopleCode command or log a message,
- Typically you use a step to execute a SQL statement or to call another section.
Actions.
- The SQL or PeopleCode that a step executes are the actions within the step.
  - Do Actions
    - Do actions contain a SQL Select statement designed to return results on which subsequent actions depend.
      - Do While
      - Do When
      - Do Select
      - Do Unitl
  - SQL
    - The SQL action differs from the Do actions, which also contain SQL, in that the SQL action does not control the flow of the program.
      - Update
      - Delete
      - Insert
      - Select
  - PeopleCode
    - Most importantly, PeopleCode provides access to the PeopleSoft integration technologies such as PeopleSoft Integration Broker , Component Interfaces, Business Interlinks, and file processing.
    - PeopleCode for setting If, Then, Else logic constructs.
    - Performing data preparation tasks
    - Building dynamic portions of SQL statements.
    - Enables you to set state record variables dynamically.
    - 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.)
    - You can use the CallAppEngine PeopleCode function, or you can define global variables.
    - With a PeopleCode action, there is only one property that you can specify—On Return.
    - Determines how your Application Engine program reacts based on the return of your PeopleCode program. The On Return setting takes effect if your PeopleCode program issues a “return 1” or “exit 1.” You can use the True keyword in place of a non-zero numeric return.
      - Abort: The program issues an error and exits immediately.
      - Break: The program exits the current step and section, and control returns to the calling step.
      - Skip Step: The program exits the current step, and continues processing at the next step in the section. If this is the last step in the section, the calling step resumes control of the processing.
  - Log Message
    - You use a Log Message action to write a message to the message log based on a condition in your program.
    - When a user views the messages using the Application Engine Message Log page, the system retrieves the appropriate message string from the message catalog based on the user’s language preference.
    - Note. You can also use MessageBox PeopleCode to populate PS_MESSAGE_LOG instead of using the Log Message action. This enables you to easily record errors encountered within Application Engine PeopleCode programs.
  - Call Section
    - You can also insert an action that calls another section either in same or different app engine program. (Static or Dynamic)
    - The called section can be in the same program as the calling section, or it can be in an external program.
    - This enables you to chunk your program into more maintainable & reusable pieces.

Note. PeopleSoft Application Engine supports up to 99 levels of nested Call Section actions. For example, the first called section can call a second, which can call a third, and so on, up to 99 calls.

State records.
- A state record is a PeopleSoft record that must be created and maintained by the Application Engine developer.
- Working storage for your Application Engine program.
- Used to pass values from one action to another.(Place to store variables)
- Either a physical record or a work record. (Naming convention XXX_AET)
- Any number of state records can be associated with a program. (Max 200)
- Physical state records must be keyed by process instance.
- There is only one row in the state record for each process instance.
- After the program completes successfully, PeopleSoft Application Engine deletes the corresponding row in the state record.
- Multiple programs can use the same state record, and each program has its own row based on the unique process instance key.
- %Select inserts data
- %Bind retrieves data
- Default state record is inheritable in call stack for external app engine calls.
- You can have up to 200 state records associated with a particular Application Engine program. However, only one record can be the default state record.

You use the %Select construct to pass variables to the state record, and you use the %Bind construct to retrieve or reference the variables. Typically, when you use %Bind to provide a value for a field or a Where condition, the type of field in the state record that you reference with %Bind must match the field type of the corresponding database field used in the SQL statement. In the case of an external call to a section in another program, if the called program has its own default state record defined, then PeopleSoft Application Engine uses that default state record to resolve the %Bind(fieldname).Otherwise, the called program inherits the calling program's default state record. You must use the Date, Time, and DateTime output wrappers in the Select list that populates the state record fields.
Example
Consider the following sample statement:
%SELECT(BUSINESS_UNIT,CUST_ID)
SELECT BUSINESS_UNIT, CUST_ID
FROM PS_CUST_DATA
WHERE PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE)
The following steps illustrate the execution of the previous statement:
1. Resolve bind variables.
The string %Bind(PROCESS_INSTANCE) is replaced with the value of the state record field called PROCESS_INSTANCE.
2. Execute the SQL Select statement.
3. Perform a SQL Fetch statement.
If a row is returned, the state record fields BUSINESS_UNIT and CUST_ID are updated with the results. If the Fetch statement does not return any rows, all fields in the %Select construct retain their prior values.
Example 1)
Main Record

Data in main record

State Record

App Engine Program in "Program Flow" view

DoSelect SQL

SQLAction - SQL

Run Request

Result data in main record

APP_ENG_PROG.log
PeopleTools 8.48.06 - Application Engine
Copyright (c) 1988-2007 PeopleSoft, Inc.
All Rights Reserved
Application Engine program APP_ENG_PROG ended normally

End

Remember the following when inserting actions:

You cannot have more than one action of a specific type within the same step.
You cannot have a SQL action and a Call Section action within the same step.
You can only define XSLT type actions for programs defined as Transformation types (see the program properties).

At runtime, the system evaluates actions by type and executes them within a strict hierarchy. For example, if both a Do When and PeopleCode action exist within a given step, PeopleSoft Application Engine always executes the Do When first.
The following diagram depicts the sequence and level of execution for each type of action:

As you add actions to a step in the definition view, the actions are initially inserted after the selected definition (the owning step or a prior action). However, following a save request or a refresh of the view, the designer reorders all actions to match the execution hierarchy. This feature helps you visualize the sequence in which each step of your program logic executes.
Application Engine Program Types
There are five types of Application Engine programs. You specify the type in the Program Properties dialog box for your program definition.
The types are:

Standard, which is a normal entry-point program.
Upgrade Only, which is used in PeopleSoft upgrade utilities.
Import Only, which is used by PeopleSoft import utilities.
Daemon Only, a type of program used as a daemon process.
- Within a daemon group, programs are invoked sequentially,
- The programs contained in a daemon group should be quick programs that scan information to find events.
- When an event is discovered, the daemon program can use the ProcessRequest class to invoke programs that are not of the daemon type.
- These non-daemon type Application Engine programs can execute in parallel.
- For that reason, do not include application-specific processing in a PSDAEMON type program.
Transform Only, a program type used to support Extensible Stylesheet Language Transformations (XSLT).

Testing Application Engine Programs

After creating or modifying your program, you can test it while in PeopleSoft Application Designer in two-tier mode. You use the Run Request dialog box:
',escape('Run Request dialog box'))">

Run Request dialog box
To run an Application Engine program in two-tier mode:
1. Select Edit, Run Program from the PeopleSoft Application Designer toolbar.
The Run Request dialog box appears.
2. Enter appropriate values.
When you click OK, these values are passed as runtime parameters to the initiated PeopleSoft Application Engine runtime executable.

Run Control ID	Enter the run control ID of the program that you are testing.
Run Minimized	Select to have the window of the requested process minimized when it is submitted to run.
Output Log to File	Select to write the output log to a file.
Log File Name	Specify the log file name (enabled only when Output Log to File is selected).
Process Instance	Specify the process instance for this run request, or use the default value of zero if an instance number is not needed.

3. Click OK.

Setting General Properties

Access the Program Properties dialog box and select the General tab. You can specify identification values for your Application Engine program.

Owner ID

(Optional) Enter the owner ID for the program. The owner ID is a way to identify which definitions are owned by which PeopleSoft applications, such as PeopleSoft General Ledger, Accounts Receivables, and so on. The values in the drop-down list box are Translate table values associated with the OBJECTOWNERID field.

Setting State Record Properties

Select the State Records tab.

Qualify Search	Enter any wildcard characters or complete table names to limit the results that appear in the record list. By default, the Record List box contains all record names that end with the extension AET. This extension identifies the record as an Application Engine record.
Get List	Click to populate the Record List box.
Record List	This text box contains the results of your state record search.
Selected	Select state records for use with a particular program. Click Add to include selected records from the record list into the selected list. Click Remove to remove selected records from the selected list. Indicate which state record to act as the default state record by selecting its check box. For your default state record, you need to reference only fieldnames in your PeopleCode and SQL (for the active program). When you reference a non-default state record, you do so by using recname.fieldname.

Specifying Temporary Tables

Select the Temp Tables tab.
Temporary tables store intermediate results during a program run.
Note. You must have already defined required temporary tables in your database prior to associating them with an Application Engine program.

Qualify Search	Enter any wildcard characters or complete table names to limit the results that appear in the record list. By default, the Record List box contains only records that are of type Temporary Table. You apply this attribute when you create the record in PeopleSoft Application Designer.
Get List	Click to populate the Record List box.
Record List	This text box contains the results of your search for temporary tables.
Selected	Select temporary tables for use with a particular program. Click Add to include selected records that appear in the record list. Click Remove to exclude selected records that appear in the selected list.
Instance Count	Enter the number of physical tables to be created for each dedicated table for this program during the SQL Build procedure in PeopleSoft Application Designer. Typically, you would set this number to equal the maximum number of parallel program runs that you anticipate. For instance if you expect up to five instances of the same program to run simultaneously, then you would set the instance count to 5.
Insert Selected List into Project	If the active Application Engine program definition belongs to a project, select to include the dedicated temporary tables for this program within the same project.
Runtime	Control how an Application Engine program behaves if an instance of its specified dedicated temporary tables is not available. If you select Continue, then PeopleSoft Application Engine uses the base version, or nondedicated version, of the temporary tables. If you select Abort, then the program exits with an error message.

Note. If the table is keyed by PROCESS_INSTANCE, and the application SQL includes the process instance in the Where clause, then the table can be shared by multiple processes. The best performance, however, occurs when a program runs against a dedicated temporary table instance.

Note. You must set the instance count on the Temp Tables tab prior to building the tables in PeopleSoft Application Designer.

Setting Advanced Properties

Disable Restart	Select to disable the built-in restart capabilities for a particular program.
Application Library	In some cases, you may want a program to contain only a collection, or library, of common routines (in the form of callable sections) that you do not want to run as a standalone program. When sections are defined as public, other programs can call the sections, or routines, that exist in the library at runtime. Because this type of program is not designed to run as a standalone program, it does not require the MAIN section, or initial entry point. Select this check box to rename or remove any existing MAIN section. Note. An application library is the appropriate location to store a collection of shared Application Engine program sections. Libraries are not intended for storing a specific SQL action within a section. To share common SQL, use the SQL repository.
Batch Only	Select for batch-only programs. Batch-only programs are not executed from the CallAppEngine PeopleCode function. Any dedicated temporary table used for batch-only programs do not have online instances created.
Message Set	Specify the default message set value for this program. The system uses this message set value for all Log Message actions where the message set isn’t specified.
Program Type	Select from: · Standard: Used by standard entry-point programs. · Upgrade Only: Used by PeopleSoft upgrade utilities only. · Import Only: Used by PeopleSoft import utilities only · Daemon Only: Use for daemon-type programs. · Transform Only: Support for XSLT programs.

Specifying Call Section Actions

Use the Call Section action to call another section defined in an Application Engine program. You can call a local section defined within your current program, and you can make external calls to a section defined in another Application Engine program.
The external section you intend to call must have its access property set to Public. If a section’s access property is set to Private, that section can be called only from within the same program. By default, a section’s access property is Private. If you attempt to make a call to a section that does not allow external calls, you receive an error message at runtime.
Note. You can call only programs that reside within the same database as the calling program.
Program ID Property
Because you can call sections defined in the current program or within external programs, you must first specify the program ID of the program containing the section you intend to call.
The default value is (current). If you call a section defined in another program, make sure that you first select the appropriate external program from the Program ID drop-down list box. The drop-down list box contains the names of all program definitions that currently exist in the database.
Section Name Property
Select from names defined in the program that appears in the Program ID list box. To call a section that is defined in an external program, select the program name in the Program ID edit box prior to selecting the section name.
Also use the Call Section action to call an entire external program. First select the program ID, then select section name MAIN. At runtime, this call executes the entire program defined by the value in the Program ID field.
Note. PeopleSoft Application Designer does not prevent you from calling the main section of the current program or the current section. For instance, Section1 can contain a step that has a local call section reference for Section1. This enables recursive calls, and should therefore be used with caution.
Dynamic Property
Use the AE_APPLID and AE_SECTION fields in the state record to execute different sections depending on the conditions a program encounters during runtime.
These two fields must be defined on the default state record for the program. If AE_APPLID is not present or is blank (at runtime), the current program is substituted for the AE_APPLID value. If AE_SECTION is not present or is blank, an error occurs.
When issuing a dynamic call, both the section and the program ID must be dynamically set. You enable a dynamic call by first having your program store different section names in the AE_SECTION field, and different program names in AE_APPLID field. The values you insert in these fields are normally based on various conditions met within your program. You then create a Call Section action that calls the section name defined in the state record field by selecting the Dynamic check box.
Selecting Dynamic automatically populates the AE_SECTION field with the symbolic value %Section, and the Program ID field with the symbolic value %AEAPPLID. At runtime, the program calls the section name stored in AE_SECTION that belongs to the program name defined by AE_APPLID.
Program Properties of Called Sections
When you call a section defined in an external program, the current program (the program containing the defined call section) defines the properties that apply to the running process. Suppose tracing is enabled for the current program, but tracing is disabled for the called program section. In this case, the called program has the trace option enabled at runtime because it inherits the calling program's properties.
For example, if program A calls program B, and program B calls program C, then the properties of A apply to both programs B and C. The calling program always controls the properties for the called program. In this case, program A controls the properties for program B, and because program B inherits the properties of program A, when program B calls program C, program A’s properties also apply to program C.
Note. Although program properties are inherited, state records do not follow this inheritance model.
State Records of Called Programs
When you call a program from another program, the called program’s default state record becomes active until processing returns to the initial program. However, all of the state records associated with both programs are available. State records that are common between the two programs share values. To communicate between the two programs, or share %BIND variables, define the same state records in both programs.

Peoplesoft

PeopleSoft Application Engine Programming