Application Engine Curriculum

People Tools – Day 4 Class

Page | 1

PeopleSoft Application Engine 4 Day Course[A comprehensive 2 day tour on the capabilities of the SQR language and its usage in PeopleSoft]

Table of Contents

1.1 REVIEW AGENDA ......................................................................................... 3

1.2 GETTING STARTED WITH PEOPLESOFT APPLICATION ENGINE ................................... 4

1.3 APPLICATION ENGINE PROGRAM COMPONENTS .................................................... 7

1.4 UNDERSTANDING STATE RECORD ................................................................... 22

2.1 DO SELECT IN DETAIL ................................................................................. 28

2.2 APPLICATION ENGINE RESTART ..................................................................... 32

2.3 USING DO ACTIONS .................................................................................... 34

3.1 PEOPLECODE AND APPLICATION ENGINE ......................................................... 39

3.2 TESTING AND DEBUGGING APPLICATION ENGINE PROGRAM ................................... 45

3.3 APPLICATION ENGINE PROCESS DEFINITION ...................................................... 52

4.1 APPLICATION ENGINE PROGRAM EXECUTION ..................................................... 60

4.2 TEMPORARY TABLE .................................................................................... 63

4.3 SET PROCESSING ....................................................................................... 67

4.4 META-SQL ............................................................................................... 68

4.5 COMMAND LINE PARAMETERS ........................................................................ 74

Page | 2

1.1 Review Agenda

The following will be covered in Application Engine (AE):

Day 1 will include Introduction of Application Engine, Understanding Application Engine Program Structure and Usage of State Record.

Day 2 will include Details on Do Select and other Do Actions and understanding Restart Capability.

Day 3 will include Use of PeopleCode in AE, Testing and Debugging AE and AE Process definition.

Day 4 will include Application Engine Execution, Usage of Temporary Table, Set Processing and Usage of Meta SQL

Page | 3

1.2 Getting Started With PeopleSoft Application Engine

Describe the major features and benefits of Application Engine.

Application Engine Overview

• PeopleSoft Application Engine is used for creating applications that perform background SQL processing against the data. It provides an alternative to writing COBOL, or SQR programs for background SQL processing. While PeopleSoft Application Engine does not generate, parse, or understand SQL, it merely executes SQL that we provide. This tool is intended to be used by developers with knowledge of SQL, SQL tools and PeopleTools.

• In PeopleSoft Application Engine, a program is a set of SQL statements, PeopleCode, and program control actions that enable looping and conditional logic. A program is defined in PeopleSoft Application Designer and performs a business process. We 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 Benefits

• Graphical Developer Interface: Application Engine is fully integrated with PeopleTools. This means that we use Application Designer to create Application Engine Program. The Application Designer offers Application Engine developers the following benefits:1) Easy access to the new PeopleCode and SQL editors.

2) Two views of our program. The Definition view is where we create and modify our programs. The Program Flow view allows us to see the actual order in which our program will execute the statements.

• Data Dictionary Integration: Unlike applications developed using COBOL or SQR, Application Engine programs reside completely within our database. Because Application Engine works in harmony within the PeopleSoft system and references the data dictionary for object definitions, changes to meta-data in the database have no impact on Application Engine programs.

• Enhance SQL/Meta SQL Support: RDBMS platforms have many differing syntax rules-especially in regard to date, time, and other numeric calculations. For the most part, we can work around this problem using PeopleSoft meta-SQL, which Application Engine supports. This language is designed to replace RDBMS-specific SQL syntax with a standard syntax, called meta-strings. For example, if we reference a date field called AE_DATE in a SELECT clause, we specify it as %DATEOUT(AE_DATE). This meta-string will be resolved to the appropriate value as required by the current RDBMS platform.

• Effective Dating: Application sections are effective-dated—meaning we can activate/deactivate a section as of a particular date. This enables us to archive

Page | 4

sections as we modify them, instead of destroying them. In the future if we decide to revert to a previous incarnation of a section, we can simply reactivate it.

• Platform Flexibility: Even with the use of Meta-SQL, there may still be times when we have to write and execute different SQL in our Application Engine program, depending on the RDBMS platform on which we intend our program to run. In Application Engine, we have the ability to call different versions of a Section for different platforms, as needed.

• Reuse Business Logic: Application Engine programs can invoke PeopleCode. This means that we can call common PeopleCode functions from Application Engine. Conversely, PeopleCode can now invoke an Application Engine program synchronously. AE libraries are used to store common routines, stored as callable sections. These sections can then be called by multiple programs.

• Upgrade Support: Since Application Engine program definitions are defined in and stored as Application Designer objects, we can easily upgrade using Application Designer. We can insert records and Application Engine programs in the same project. This means that if we change our record definition, we can put the modified record into a “project”, and we can then also include in the project any Application Engine programs affected by the change—along with affected pages, and so on. Then we upgrade the entire project all at once.

• Built-in Restart Logic: Within each Application Engine program, we must define how frequently our program will issue a COMMIT. After doing so, each COMMIT becomes a checkpoint that Application Engine uses to locate where within a program to restart after an abend. The restarted Application Engine program locates the last checkpoint, or the last successfully executed and committed Step, and continues with the processing from that point. This type of built-in logic does not exist with COBOL or SQR, we would, of course, need to code it in your programs yourself.

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, this 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.

• Transform only, a program type used to support Extensible Stylesheet Language Transformations (XSLT).

Daemon Program Type

• PeopleSoft Application Engine provides a daemon process, called PSDAEMON that runs continuously when PeopleSoft Process Scheduler is running, and is intended for recurring jobs. It polls the system, checking for certain conditions to occur. A predefined set of conditions is an event. When the conditions are true, PSDAEMON schedules a process to handle the event.

• PSDAEMON supports limited tracing, because it runs indefinitely. Specifically, it only allows PeopleSoft Application Engine tracing at the step and SQL levels, in addition to the

Page | 5

standard PeopleSoft SQL and PeopleCode tracing. Other options, such as Timings and DB Optimizer tracing, are not supported.

• You activate PSDAEMON in PeopleSoft Process Scheduler or from the command line

Transform Program TypeTransform Only type programs enable different systems to communicate with one another by transforming messages into appropriate formats. When you specify an Application Engine program as a Transform Only program, you must specify actions of type XSLT or PeopleCode. You can use transform programs to do any of the following:

• Apply a transformation to a message to make its structure comply with the target system’s requirements.

• Perform a data translation on a message so its data is represented according to the target system’s conventions.

• Determine whether to pass a message through to its target, by filtering it based on its content.

Page | 6

1.3 Application Engine Program Components

Application Engine Designer

Application Engine programs are created and modified in the Application Designer.

• Log on to the PeopleSoft development environment. Start, Programs, PeopleSoft 8, PeopleTools. The UserId is PS and the password is PS.

• In the Application Designer, open the Application Engine Program PER099.

• Select the program PER099 by double clicking on the program name.

• Expand all the nodes, by either using the icon next to the MAIN folder, the expand

all nodes toolbar button , or selecting View, Expand All (Ctrl+Shift+X).

Page | 7

With PeopleSoft 8, Application Engine is fully integrated with PeopleTools. This means that you use the Application Designer, an intuitive graphical interface, to create your Application Engine program. The Application Designer offers Application Engine developers the following benefits:

• Ability to open multiple Application Engine programs concurrently.

• Ability to work on the entire Application Engine program at once. Users of previous versions will know that this is a change from only being allowed to work on a Section at a time.

• Easy access to PeopleCode and SQL editors.

• Two views of your program.

The Definition view is where you create and modify your programs.

The Program Flow view shows a graphical representation of your program execution.

Program Flow tab

An Application Engine program comprises of several key elements:

• Program.

• Sections.• Steps.• Actions.

Program

An Application Engine program identifies the set of processes to execute a given task. A program must contain at least one Section. The execution of the program always starts with the Section defined as “MAIN.”

Page | 8

Click on the SQL or PeopleCode icon and the content of the action is displayed.

Sections

• Sections comprise one or more steps and are equivalent to a COBOL paragraph or a Structured Query Report (SQR) procedure. All Application Engine programs must contain at least one section titled MAIN.

• 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

• Steps are the smallest unit of work that can be committed within a program. Although you can use a step to execute a PeopleCode command or log a message, typically you use a step to execute a SQL statement or to call another section. The SQL or PeopleCode that a step executes are the actions within the step.

• When a section gets called, its steps execute sequentially. Every program begins by executing the first step of the required section called MAIN and ends after the last step in the last section completes successfully.

Actions

There are multiple types of actions that you can specify to include within a step. It is common to have multiple actions associated with a single step.

Do Actions

Do actions contain a SQL Select statement designed to return results on which subsequent actions depend. For instance, if a Select statement returns no rows, subsequent actions may not need to execute. A Do action is equivalent to a COBOL Perform statement and has similar constructs.

The four types of Do actions are:

• Do While

• Do When

• Do Select

• Do Until

We will look into each of the Do Action in details later.

SQL

Most SQL actions contain a single SQL statement. These actions can perform the following types of SQL statements:

• Update

• Delete

• Insert

• Select

Page | 9

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.

For SQL Actions, there are two properties that can be set.

• ReUse - ReUse is an option you can enable to optimize the SQL components of your batch program.

• No Rows- In the event that the SQL (INSERT, UPDATE, and DELETE) associated with the SQL Action does not return any rows, you need to specify what your Application Engine program should do. The following list contains the options that you have when no rows are returned.

Abort. The program terminates.

Section Break. Application Engine exits the current Section immediately, and control returns to the calling Step.

Continue. The program continues processing.

Skip Step. Application Engine exits the current Step immediately and moves on to the next Step. When using Skip Step keep the following in mind:

• Application Engine ignores the commit for the current step at runtime.

• If the current Step contains only one Action, only use Skip Step to bypass the commit.

PeopleCode

You can include PeopleCode in the PeopleCode action. PeopleSoft Application Engine PeopleCode provides an excellent way to build dynamic SQL, perform simple if/else edits, set defaults, and other operations that don't require a trip to the database. It also enables you to reference and change active Application Engine state records.

Most importantly, PeopleCode provides access to the PeopleSoft integration technologies such as PeopleSoft Integration Broker, Component Interfaces, Business Interlinks, and file processing.

Log Message

You use a Log Message action to write a message to the message log based on a condition in your program. This gives your program multilanguage capability. The system stores the message generically as a message set, message number, and parameter values. 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.

Call Section

Page | 10

You can also insert an action that calls another section. 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. If a section already exists in one program, rather than copying it into another program, just call it.

Activity 1 – Writing the first Application Engine Program Shell

In this activity, we will go through the mechanics of creating a new program shell. We will use this to understand the various properties that we can set. This will not be a working program, as we are not going to add any SQL statements.

Page | 11

Before we start building Application Engine programs and related definitions, we want to make sure that our new definitions are automatically added to the PSTRN_AE project.

• Change the Insert Definition into Project group box to use “When definition is modified and saved, or deleted”.

• Click OK to save the setting.

• You create an Application Engine program using File, New, App Engine Program.

Initially you get the MAIN section and Step01 (with no Actions yet).

Inserting Section and Steps

An Application Engine toolbar will be associated with your new program to allow you to insert Sections, Steps, and Actions. You also have icons to refresh, expand and collapse the program's tree for better visibility.

Page | 12

I n se r t Sec t i o n

I n se r t S tep and Ac t i o n I n se r t Ac t i o n

Re f r e sh V i ew

Expand a l l NodesCo l l a p se a l l Nodes

Run P rog r am

You can also add sections, steps and actions using the Insert menu option or by using the right mouse to invoke the popup menu.

Notice that the Insert is dependent on where the cursor is placed. If you have your cursor on a section, the Insert Action will be grayed.

Section Properties

For each section, you can set properties for the section.

Control Description

Section Name Be as descriptive as you can, keeping in mind that you only have an 8-character limit. Develop a naming convention and be consistent throughout your projects.

Description Enter a descriptive name for the section.

Market Select the Market for which the Section is intended. If a particular Market is irrelevant to your batch program, you can leave the Market value as GBL.

Platform In some cases, you may be developing an application to run on a particular platform or RDBMS. This enables you to take advantage of any RDBMS-specific optimizations or to change your section logic because of RDBMS limitations.

Effective Date The default effective date will be 01/01/1900. To make a particular

Page | 13

section effective-dated, enter the effective date.

Effective Status You can indicate whether or not this section is active.

Section Type In the case of an abnormal termination of the program, the value of this system field specifies whether or not you will need to Restart the Section.

Auto Commit Here you specify the Commit level for the Section. You can opt to have no Commit or you can have Application Engine Commit after each Step successfully completes.

Access This property controls the Call Section Action. You can specify that a Section can or can’t be called from another program.

Comments Use the comments section to document the section.

To view comments, you can select View, Show all Comments (F2) from the menu or right mouse on the section and select Show Comment.

Execution Precedence

A Section is unique based on the program, section names, RDBMS platform, effective date and market. When you execute an Application Engine program, it executes Sections based on the following order of precedence:

• If a Section for the current Market exists, execute it.

• If a Section for the current Platform or RDBMS exists, execute it.

• If a Section for the current Effective Date exists, execute it.

• If a “unique” Section does not exist, Application Engine just executes the “default” Section.

For example, suppose you have two versions of a particular Section: SECT01 for Public Sector and SECT01 for Global use. If you request to run the Public Sector version of the program, Application Engine executes the Public Sector version of SECT01. If the program is running on Oracle, Application Engine then looks for an Oracle version of the SECT01 for Public Sector.The default Market value is GBL for Global, which is similar to the Platform value of default.

Step Properties

Each section contains one or more steps. The steps are executed in the order that they are displayed in the application designer. The steps contain the logical groupings of actions to be performed.

Page | 14

Control Description

Step Name Consecutively numbered based on last step name.

Step Description Enter a descriptive name for the step.

Commit After Determines when commit will be executed:

Default - uses value supplied for the section

Later - do not commit now

After step - commit when step completes

Frequency Only available in loops

On Error Determines the action to take when an error occurs:

• Abort - program will write message to message log and terminate

• Ignore - program will write message to message log and continue

• Suppress - no message will be written, but program will continue

Active Determines if the step is active (ticked) or inactive (blank).

Comment Add any additional comments to document your program.

Action Properties

Actions are the reason for your program. They contain the code you wish to execute. The primary type is a SQL statement. But you also have the ability to log messages, execute PeopleCode, call other Sections and perform transformations.

In a given Step you can have multiple actions but only one instance of each action. Moreover, the actions execute in a given sequence.With you cursor on Section1, Step01 - click the Add action 8 more times.

Page | 15

On the eighth insert, you will receive the following message.

Click OK.

You will also notice that there are 8 actions out of the available 9.When you refresh, the actions will be reordered into the order of execution.

To refresh, either select the Refresh Icon or View, Refresh or F5.

Page | 16

Note: SQL and Call Section are mutually exclusive.

Program Properties

When you create a new program, you will want to give your program a name. If you modify existing programs, you will want to document your changes. Descriptions and documentation are updated via the general tab in program properties.Open the Application Engine program PER099.

Open the Program Properties. File, Definition Properties or .

Page | 17

Item Description

Disable Restart Select this to disable the Application Engine built-in restart capabilities for this 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

Page | 18

Item Description

“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. Setting this option renames or removes any existing MAIN section.

Batch Only Select this check box 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 message set value that you want assigned as the default message set number for this Application Engine program. The system uses this Message Set value for all Log Message actions where the message set isn’t specified.

Program Type Select the appropriate program type from the list: Standard, Upgrade Only, Import Only, Daemon Only or Transform Only. During the course of this class we will only be dealing with Standard or Daemon Programs.

Application Library

Most Application Engine programs are defined to be executable. An executable program has a main entry point in its definition: the Section MAIN. This defines the entry point so whatever method you choose to invoke the program, such as command line or Process Scheduler, can initiate the program. However, in some cases, you may want a program to only contain 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 stand-alone program, it does not require the MAIN Section, or initial entry point. Setting the Application Library option renames or removes any existing MAIN Section.

Testing Application Engine Program

There are several methods that developers use to test Application Engine programs:

• Execute directly from Application Designer in 2-tier.

• Developers Shortcut (Generic Application Engine Test Type) through the Process Scheduler.

• Command Line

We will look at the first two methods here and the last will be discussed later.

Execute Directly from App Designer

In the application designer, you can select Edit, Run Program or the Run Program push button

. This method is fast and simple, but does not allow you to add any run control parameters at execution time.

Page | 19

Run Control ID Enter the run control ID of the program that you are testing. If your program requires run control parameters, you will need to populate the fields on the run control and specify the run control when executing from Application Designer.

Run Minimized Select this to have the window of the requested process minimized when it is submitted to run.

Output Log to File Select this if you want the output log to be written to a file.Log File Name Specify the log file name (enabled only when OutputProcess Instance Process Instance number will default to 0.

Developer’s Shortcut

PeopleSoft come delivered with a page called Request AE. This page is designed for developers to test Application Engine programs. The Request AE page allows you to enter parameters associated with the Application Engine program.

Select PeopleTools, Application Engine, Request AE to access the Application Engine Request page.

Process Origin Displays where the program was invoked: from PeopleSoft Process

Page | 20

Scheduler, from the command line, and so on.

Process Instance

Displays the process instance assigned to the previous program run.

Status Displays the status of the last program run, whether it is successful, pending, and so on.

Process Frequency

Specify how long a particular process request will remain active or valid:

• Always: Select to run the process request as needed.

• Once: Select if a process request is a one-time-only request.

• Don’t: Select to disable a process request, so that no one invokes it and potentially corrupts data.

As Of Date If you are requesting retroactive processing, specify the appropriate as of date.

Bind Variable Name

Enter the appropriate field or bind variable for which you are inserting a value.

Value Enter the initial value that you want to set for the specified field

Page | 21

1.4 Understanding State Record

Each Application Engine program can have zero, one or more Application Engine State records associated with it. A State Record is the method by which you allocate variables for your Application Engine program. The state record can be a derived work record or an SQL table. The only difference is that derived state records cannot have their values saved at commit time, therefore the values would be lost during a restart.

The state record is used as a worktable for:

• Initial values

• Values to be passed to program Steps from prior Sections, Steps and Actions

• Results of SQL Selects

• Values for SQL Updates, Inserts, Deletes

• Computed values or counters

• Program working values

• Programmable switches or flags

How to Create State Record

Open the record HR_AET in the Application Designer.

• The first field must always be PROCESS_INSTANCE and it must be a key field.

• When you execute an Application Engine program, this field will be populated with the process instance assigned by the system for this job.

• Any additional fields on the record will be variables that you will use within your Application Engine program.

• The name of the state record must end in AET. This is how the system identifies the record as a state record.

• If the state record is a physical table, you must perform a build before using the table.

• Any data types except character or numeric must not be required fields.

Page | 22

• No PeopleCode fires on an Application Engine state record.

• No validation of translates values or prompt tables are done.

Using State Record

State records are associated with Application Engine programs using the program properties for the program.

To add a new state record, use the push button. Notice that all state records end with AET.

Page | 23

Highlight the state record you wish to use. Use the pushbutton to add the state record to the program.

State RecordXX_XXXXXX_AET

Application Engine Program

Contains Actions(s) usingspecific SQL statements thatload or pull 1 row of data from

the State Name Record(s)

%Select()

%Bind()

Values are inserted into the state record, either by the run control (as we have seen in this example) or by using %Select in your Application Engine program.

Once a value is inserted into the state record, it can be accessed using the %Bind(fieldname) to execute the SQL.

When the Application Engine program completes successfully, the row of data in the state record is deleted.

%Select %Bind

A %Select is required at the beginning of any and all SELECT statements.

The %Bind function can be used anywhere in a SQL statement

%Select(ASOFDATE)

SELECT %DateOut(ASOFDATE) FROM PS_RUN_CNTL_HR

WHERE OPRID = %OperatorId AND RUN_CNTL_ID = %RUNCONTROL

SELECT ITEM_CODE, ITEM_DESCR

FROM PS_ITEM_TBLWHERE VENDOR_CODE = %BIND(FIELD1)

Page | 24

Sharing State Record

State records can be used by multiple sections and by multiple programs. When you call a section in another program, any additional state records defined for that program (as in state records that are not already in use by the calling program) are initialized, even if the program has been called previously during the run. However, state records that are common to both programs retain their current values.

To reference variables that exist within a state record, use the following:

%BIND(fieldname)

Unless a specific record name is specified preceding the fieldname, %BIND references the default state record. To reference a state record other than the default, use the following:

%BIND(recordname.fieldname)

In the case of a called program or section, 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. In theory, the called program does not require a state record if all the fields it needs for processing exist on the calling program’s state record.

For those state records that are shared between programs (during an external call section), any changes made by the called program remain when control returns to the calling program. Any subsequent actions in the calling program can access residual values left in the common state records by the called program. This can be useful to return output values or status to the calling program, yet it can also cause unforeseen errors.

Generally, a called program should not share state records with the caller unless you need to pass parameters between them. Most programs have their own set of state records unless a program calls another program that requires specific input or output variables. In that case, you must include the state record of the called program into the calling program’s state record list, and make sure to set the input values before issuing the call section.

Activity 2 – Writing the first AE Program using State Record

Create an Application Engine program using State Record and Log Message Action to write message with the program run date.

Solution:

1) Create the following entry in the Message Catalog.

Page | 25

2) Create a Derived State Record APPENG_TUT_SOA.

3) Create a simple App Engine program APPENG_TUT1_SOA and associate APPENG_TUT_SOA as the default State Record.

Page | 26

“Do Select” action has the following SQL to initialize the state record.

******************%Select(ASOFDATE) SELECT getdate()******************

4) Save and run the program.

Note: This program also illustrates passing of parameters to the Message Catalog

Page | 27

2.1 Do Select In Detail

The Do Select Action is a SELECT statement that executes subsequent Actions within the Step in a loop based on whether the SELECT statement in the Do Select Action has returned a row. This is equivalent to a FOR loop.Each row of data in the database meeting the criteria is loaded into the state record and processed within a loop. When there are no more rows meeting the criteria, the program will move to the next step.

Before we take a look into the 3 select types let take a look into the following concepts:

What is Cursor?

Anytime a SQL statement is sent to the database, a cursor must be opened on the database. The cursor is the avenue by which the database will communicate the results of that specific request back to the requester. The requester could be an online PeopleSoft page, a SQL query tool or an Application Engine program; and the results might be that of a SELECT statement, or perhaps other statements like an UPDATE or INSERT.While an Application Engine program is running, there may be many cursors open. Each cursor is responsible for carrying out a specific SQL statement. Cursors are frequently opened and closed during the program’s execution. Looping techniques work with many cursors.

SELECT Statements

When a SELECT statement is executed through a cursor, the row(s) retrieved will be placed into a memory buffer on the database for that cursor. From there, a FETCH is issued to retrieve the row(s) from the memory buffer as needed. If there are multiple rows in the memory buffer Application Engine can be made to execute the “Select then Fetch” the rows one at a time from the memory buffer in a loop.

Cursors and COMMIT

When a cursor is opened and work is performed, it is done so on a conditional basis and has the potential to be undone. If and when a COMMIT statement is issued to the database this conditional work is now permanent and cannot be undone.Within an Application Engine program COMMIT can be issued during the middle of the program’s run. When the COMMIT is issued it will be issued across all cursors. This means that any work that has been performed on any cursors that have been opened and closed, or cursors that still remain open, will be permanent changes. Once work is COMMITed it cannot be undone.

Cursors and ROLLBACK

A ROLLBACK is simply taking all the data that all the cursors have accomplished for your program, and undoing it. When issued, a ROLLBACK is done back to the last COMMIT issued. Once a ROLLBACK is issued a COMMIT that immediately follows would have nothing to COMMIT.

Do Select Types

The type of Do Select determines the specific looping rules. There are 3 select types:

Page | 28

• Select/Fetch

• Reselect

• Restartable

Select and Fetch

The Select and Fetch type:

• Opens the cursor to the database.

• Selects the first row that meets the SQL condition in the Do Select.

• Executes the actions in the step (Call Section, SQL, etc.).

Once the subsequent actions, are completed the program returns to the open cursor from the Do Select, retrieves the next row and continues again with the rest of the actions.Once the Do Select does not retrieve any rows (False), it will skip the subsequent actions in the same step and move onto the next step in the Section.

The Select and Fetch type will:• If restart is not disabled all COMMITs executed within the Do Select loop (for example:

COMMITs on Called Sections) are ignored. Disabling restart is a simple way of telling the application that you are freeing it up of the responsibilities of knowing where to restart. That responsibility is now yours via a SWITCH field or an ORDER BY clause.

• Write “COMMIT setting ignored” in the trace file for any commits within a Do Select loop.

The reason these COMMITs are ignored is because of limitations of the checkpoint written to the PS_AERUNCONTROL table. The checkpoint has no way of recording where inside a loop the process left off. Therefore a restart at the exact point (loop) is impossible.

Reselect

The Reselect type opens the cursor and closes the cursor on each iteration of the loop. The reselect functions as follows:

• Opens a cursor to the database.

• Selects the first row that meets the SQL condition in the Do Select.

• Closes the cursor.

• Executes the following Actions in the step (Call Section, SQL, etc.).

• Once the subsequent Actions, are completed the program returns to the SQL in the Do Select and opens a cursor again.

Essentially, it will be reselecting the same row of data. As a result, to prevent being caught in an endless loop, you need to make the SQL (in the current Step or a Called Section) such that it will be changing the status of the rows in the table the Do Select is selecting. This way you will be reducing the result set of the Do Select until it returns nothing or a false, thus ending the Do Select loop.

Page | 29

COMMITs are NOT ignored and will be committed in a Reselect loop. Because the rows being selected are being changed by your code, provided you COMMIT those changes, the program will in fact have a way to know where in the loop it left off, making a restart possible.

You need to:• Identify the rows to be processed

o Process Flag

o Delete rows

• Order the data values

• Place commits in the appropriate sections or steps

Remember, whenever a COMMIT is issued inside your program, provided you have NOT disabled restart, a checkpoint is also COMMITed to the PS_AERUNCONTROL table. The last section and step COMMITed will be listed in a column called AE_RUN_DATA.

Restartable

The Restartable type acts precisely like the Select and Fetch. It opens the cursor and leaves it open. This causes each iteration of your loop to select the next row that meets your condition without the need to change the status of that row as we do with the Re Select option.

The major difference with Restartable is that it WILL COMMIT inside the loop thus allowing a checkpoint to the PS_AERUNCONTROL table. If the program abends, the information in the AE_RUNCONTROL record and the state record is used to restart the program.

Page | 30

Select Type Summary

Select/Fetch ReSelect Restartable

Definition

Opens the cursor once & performs a

fetch for each iteration of the

loop.

Opens a new cursor for each iteration and selects the 1st row

that meets the select criteria.

Opens the cursor once & performs a

fetch for each iteration of the

loop.

Restartable from inside the Do Select loop

NoYes (provided you code it that way)

Yes (provided you code it that way)

Are program level commits

performed inside the Do

Select loop for restartable AE

programs?

No Yes Yes

Do you need to reduce the

result set of the Do Select loop

by deleting rows or

including a ‘processed flag’

No Yes Yes

Activity 3 – Writing AE Program to understand Do Select Types

Create a Reporting table based on JOB and NAMES to store current employee information (EMPLID, EMPL_RCD, EFFDT, EFFSEQ, NAME, DEPTID, JOBCODE, LOCATION) from JOB.

Build an AE program to do the following:

Truncate the Reporting table at the beginning of the program.

Use Do Select (Select and Fetch) and perform row by row processing to insert current employee information into the reporting table from JOB and NAMES. Set commit frequency for this step to 10.

Keep trace on and run the program.

See the trace file to find the effect of using Select/Fetch

Note: We are using row by row processing just to illustrate the Do Select type. In actual development this kind of situations should be handled using Set Processing.

Page | 31

2.2 Application Engine Restart

By default, Application Engine does not perform a commit until the entire program successful completes. It's up to you to design and set up individual Program Level commits where appropriate. Here are the Program Level commit settings you can select:

• Commit after each step in that section.

• Require commits after a step.

• Defer the commit for a step.

• Commit frequency within a step or n iterations for looping actions.

Here we will examine what happens when a commit is issued and how this affects the ability to resume an Application Engine program.

Check Points

If you have restart enabled, whenever there is a Program Level commit, Application Engine also performs a checkpoint beforehand. So, if in fact a failure occurs at any point in the process, the end user can restart the program and expect the program to behave in the following manner:

• Ignore the steps that have already completed up to the last successful commit.

• Begin processing at the next step after the last successful commit.

Manual commits will not have a checkpoint written for them and are not recommended.

AERUNCONTROL

The ability for Application Engine to “remember” what Steps have already completed and which Steps have not is attributed to an important record called AERUNCONTROL—keyed by Process Instance

If restart is enabled and the program fails, you will not be able to rerun the program with the same run control. The AERUNCONTROL table is checked and the program will not run.

If you are using a State record, the information about which row was currently processing will be in the state record if your state record is a physical table. If your state record is a derived work record, you will not be able to retrieve this information, so you want to build that into your design.

Page | 32

If you design and create your Application Engine program for restarts, then you will also want to test the restart capabilities. The QUIT option in the Application Engine debugger is used for exactly this purpose.

Disabling Restart

If your program does not perform critical updates and can be rerun with no adverse affects on data integrity, then you can disable restart for that program. When Disable Restart is selected, no check-pointing will occur.

We also saw that disable restart can be set in the Configuration Manager.

Programming for Restart

If you decide to create a program that can be restarted, you need to design for a restart at every level, as in the program, section, and step level.

Program Level

• State RecordYou may need to make at least one of your state records an SQL table. Keep in mind that if a work record is used Application Engine will re-initialize any state records that are work records at each COMMIT.

• Program properties

Page | 33

On the Advanced tab in the Program Properties dialog box, make sure that Disable Restart is not checked.

• Configuration ManagerIn the Configuration Manager, make sure that Disable Restart is not selected on the Process Scheduler tab.

Section Level

You need to have some conditions that reduce the answer set returned by the DO SELECT.

• Add an order by clause

%SELECT FIELD1SELECT FIELD1

FROM PS_SOME_RECORD WHERE FIELD1 > %BIND(FIELD1)

ORDER BY FIELD1

• Add a switch to the selected table

SELECT COLUMN1, COLUMN2,…FROM PS_TABLE1

WHERE PROCESSING_SWITCH = 'N'

• Delete processed rows

2.3 Using Do Actions

The four Do Actions—Do Select, Do When, Do While and Do Until act as SQL conditions for the execution of the other four actions - PeopleCode, SQL, Call Section and Log Message.

We have already looked at Do Select action. Here we will have a look at the rest of the Do Actions.

Do When

The Do When Action is a SELECT statement that allows subsequent actions to be executed if any rows of data are returned. The Do When statement is first in the execution order within a step. The Do When statement will contain SQL that tests for a condition. As long as the condition is true and a row of data is returned, the subsequent actions within the step will be performed. If there is no row of data returned the entire Step would terminate. If there are no more steps to execute, the program will end.

This Do When option is similar to a COBOL “IF” statement. A Do When statement runs before any other actions in the Step. If the Do When statement returns any rows, the next Action will be executed. If the Do When conditions are not met, the remaining Actions within that Step are not executed. A Do When Action is executed just once when a step is executed.

Executes the action once if the condition is TRUE.

Page | 34

Step01

Step02

Do When

action

NoTrue?

Yes

DO WHEN counter = 1000

Grant Award

Do While

The Do While Action is a SELECT statement that runs before subsequent Actions of the Step. If the Do While returns any rows of data, the subsequent actions in the step will be executed. You use a Do While Action to control the execution of the following Actions. The Do While Action runs each time before the next Action is repeated. If the Do While returns any rows of data, all the Actions in the Step will be repeated. If no rows are returned, Application Engine will move on to the next Step or Section.

The Do While is identical to the COBOL “WHILE” function. In other words, the subsequent Actions within the Step are executed in a loop as long as the SELECT statement returns at least one row for the Do While Action.

Loops as long as the condition is true. In short, if the Do While does not return any rows, the Step is complete.

Page | 35

Step01

Step02

Do While

action

NoTrue?

Yes

DO WHILE counter <1000

counter = counter + 1

Do Until

A Do Until Action is a SELECT statement that runs after each action within a Step. If the SELECT returns any rows of data, the step terminates.Use a Do Until if you want the “processing actions” to execute at least once, and to execute over and over until a certain condition is true, as in until a SELECT returns some rows.

You can also use a Do Until to stop a Do Select prematurely. For example, if the SELECT for the Do Until does not return any rows, then the Actions in the Step are repeated (except if a Do When appears in the Step). Normally, a DO Select continues until no rows are returned. If any rows of data are returned, the DO Select stops and the actions in the Step are not repeated.

Loops as long as condition is FALSE.

Step01

Step02

Do Until

Yes

No

Action(s)

True?

DO UNTIL counter = 1000

counter = counter + 1

Page | 36

Conditional Statements

In order to test for a condition, you will need to create a SQL statement that returns a True or False.

Here is an example of how the SQL statement can be coded:SELECT 'X' FROM PS_INSTALLATION WHERE %Bind(COUNTER) < 1000;

When this SQL statement executes, it will return TRUE if the value of the field COUNTER in the State Record is less than 1000 and FALSE if the value is equal to or greater than 1000.

Since your condition is being tested by the WHERE clause of your SQL statement, in some cases it does not matter which table you select from. For instance, if you are checking a condition on your State record using %BIND() to read a value from it, the FROM table is irrelevant. Since we only need one ‘X’ returned if our condition is true, any one row table in your FROM clause will suffice. A very common technique you will see in Application Engine programming is to use the PS_INSTALLATION as your FROM table. (The PS_INSTALLATION table is a one-row table delivered from PeopleSoft.)

Test Condition and State Record:

All SQL SELECT statements in an Application Engine Action must be preceded by a %SELECT(), causing the value to be written to the State record. We generally use the TEMP_FLD to write the value to, which acts as a container for the ‘X’ retrieved from the database if our condition is true. Once the ‘X’ is written to the field, there is no need for it. A subsequent SELECT ‘X’ statement in your AE program will overwrite it, and generally the character is not used in your processing.

%SELECT(TEMP_FLD) Meta-SQL to write ‘X’ to State Record

SELECT ‘X’ If ‘X’ is returned this equals TRUE

FROM PS_INSTALLATION Fills the requirement for a FROM table

WHERE %BIND(COUNTER) > 10 Condition to be tested.

Do Process Program Flow

Page | 37

The following shows the action execution hierarchy for Actions describes the sequence and level of execution for each type of Action:

WHEN a SELECT returns a row.

WHILE a SELECT returns a row.

For every row returned from a SELECT,continure in the following order:

Execute PeopleCode if any.

Execute SQL or Execute the Section.

Insert message into the Message Log.

UNTIL a SELECT returns a row.

Loop

Page | 38

3.1 PeopleCode and Application Engine

In PeopleSoft 8, the ability to use PeopleCode in an Application Engine program was introduced.

With Application Engine PeopleCode Actions, your Application Engine programs can solve a wider range of business problems with an added flexibility. However, when using PeopleCode in Application Engine programs, it’s important not to lose sight of Application Engine’s original (and current) purpose.

Application Engine is a development tool that allows you to develop programs to execute SQL, in batch and online mode, against your data in a procedural and dynamic structure. This is mainly due to the fact that Application Engine is most powerful when it executes SQL-based processing against your database. PeopleCode is an interpreted language, so there is an inherent extra performance overhead when compared to a compiled language.

The intent is to use this new capability to provide:

• Testing for Conditions (If-then-else).

• Computing capability.

• Building dynamic portions of SQL, while still relying on AE to complete the bulk of the actual program processing.

• Sharing complex business rules between online and batch processing. For example, tax computations.

• Accessing new PeopleTools integration technologies in PeopleSoft 8.x:

File Objects

Component Interfaces

Messaging

Business Interlinks

PeopleCode Editor and Application Engine

To create a new PeopleCode Action, use Insert, Action and change the Action Type to PeopleCode. You will need to save your Application Engine program before you can open the PeopleCode editor.

To open the PeopleCode Editor either:

• Double click on the gray area for the PeopleCode action.

• Select View, PeopleCode.

• Right mouse and select View PeopleCode from the popup menu.

Page | 39

There are three main parts to the PeopleCode Editor:

The left drop down box allows you to change which PeopleCode program you are working with directly through the Editor.

All sections and actions appear in this list, and the PeopleCode Actions appear in bold type. To change from one PeopleCode program to another just choose the action you want to look at/edit here.

The right drop down box has no effect when the PeopleCode Editor is accessed through Application Engine. It is normally used to select the PeopleCode Event that the code should be attached to, but in Application Engine, there is only one event available—OnExecute.

The final portion of the PeopleCode Editor is the main editor window. This works similarly to Notepad or other common text editors.

The PeopleCode Editor supports the standard text editing features and functions, such as Save, Cut, Copy, Paste, Find, Replace, and Undo. These can be invoked via the appropriate menu or by using the toolbar icons shown below. Also, most of them (except Save) are available from a popup menu when you right-click in the editor.

Command Key Button

Save CTRL+S

Undo CTRL+Z

Cut CTRL+X

Page | 40

Copy CTRL+C

Paste CTRL+V

Find CTRL+F

Replace CTRL+H

Validate Syntax

A time saving feature is drag-and-drop editing. You can drag and drop text or object names from the project workspace, or another window (including other PeopleCode Editor windows). You can access object definitions or PeopleCode functions by right clicking on the object name or function and choosing View Definition or View Function {functionName} from the popup menu. Doing this will open another window with the desired information.

A key feature of the PeopleCode Editor is syntax validation. This occurs automatically on

Save, or if you click the Validate Syntax icon— . This will check all your syntax for errors including bad object references (i.e. non-existent fields) and missing parameters.

PeopleCode Basics

There are different types of variables in PeopleCode, differentiated by the life span of the variable.

Variable Type Life Span Notes

Local PeopleCode program These variables are only available for the duration of the program in which they are declared.

Global Application Engine Program

These variables are available while the Application Engine program is running. They are saved at commits and checkpoints, so they can be used for restarts.

Component Application Engine Program

Same as Global PeopleCode Variables for Application Engine.

Variables are denoted with an “&” prefix, such as &MYROW or &Course.Local variables do not need to be declared. They can take on the appropriate data type when a value is assigned to the variable. However, Global and Component variables must be declared (and it is a good practice to declare your local variables as well). As you declare a variable you can also assign an initial value to it.

Declaring a variable:

LOCAL <datatype> <varName> = <initial_value>;

where LOCAL can be replaced with GLOBAL or COMPONENT depending on the variable type.

Page | 41

The data type can be any of the following:

Conventional Types: Boolean, Date, DateTime, Time, Number, Object, String, Any, Integer, Float.

Object Types: Field, Record, Row, RowSet, AESection, Array, File, Interlink, Message, ProcessRequest, SQL, Cookie, Request, Response, BIDocs, JavaObject, OptEngine, SoapDoc, SyncServer, TransformData, XMLDoc, XMLNode, APIObject

Example:Local Number &MyVariable = 3;

Referencing Record Fields

There are significant differences between executing PeopleCode in online and batch modes. Application Engine programs run in batch mode so, your PeopleCode can’t access pages or controls as it can while running in online mode.

Any RECORD.FIELD references that appear in a PeopleCode Action can only refer to fields that exist on an Application Engine State Records. Page buffers, controls, and so on are inaccessible even if you define the page records as state records on the Program Properties dialog. An Application Engine program can only access State Records or other Definitions you create in PeopleCode.

There are multiple methods used to reference fields in PeopleCode. Of the various methods, there are only two that apply to Application Engine.

First, you can refer to the field using the syntax <RecordName>.<FieldName>. For example, you would refer to an employee’s name using EMPLOYEE.NAME.

Second, you can use dot notation. For example, you can assign the employee’s name to a variable using the following syntax:

&NAME = GetRecord (RECORD.EMPLOYEE).GetField (FIELD.NAME);

Application Engine and the IF-THEN statement

In the Application Engine, the If-Then statement is used to control flow of execution with the Exit statement. The setting of the Exit can cause the next Application Engine step to conditionally be skipped. To do this, use syntax similar to the following:

If &Test = 10 ThenExit(0); /* or can use Exit 0; */

ElseExit(1); /* or can use Exit 1; */

End-If;

The parameter in the Exit function controls the path the Application Engine program will take.

One (1) indicates that the instruction to be done On Return will be done.

Page | 42

Zero (0) tells the program to ignore the On Return action.

The On Return options are:

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.

MAINSTEP01

Call Section1STEP02

Section1STEP01

PeopleCode

SQL

MAINSTEP01

Call Section1

Section1STEP01

PeopleCode

SQLSTEP02STEP03

STEP02

Break Exit(1); Skip Step Exit(1);

STEP02STEP03

BREAK SKIP STEP

Dynamic Calls

Rather than calling one specific Section, you can take advantage of the AE_APPLID and AE_SECTION fields in the State record to execute different Sections depending on the conditions a program encounters during runtime. This is called a Dynamic Call.If you will only be using the section within one Application Engine program, then only the AE_SECTION field must be on the state record. If you want to be able to call a section in other Application Engine programs, you will need to define both AE_APPLID and AE_SECTION on your state record. 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 into these fields needs to be based on various conditions met within your program. You then create a Call Section Action that calls whatever Section name appears in the State record field by selecting the Dynamic checkbox.

Sample:

Page | 43

State record - AE_STATEREC_AET

PROCESS_INSTANCE

NBR

AE_SECTION

Program = MYDYNM

Section Step Action

MAIN STEPM01 PCode If AE_STATEREC_AET.NBR = 15 Then

AE_STATEREC_AET.AE_SECTION= “MESSAGE1”;

Else

AE_STATEREC_AET.AE_SECTION= “MESSAGE2”;

End-If;

Call

Section

Dynamic Call

MESSAGE1 STPEMM1 Log Message "Number equal 15"

MESSAGE2 ATEPMM2 Log Message "Number not equal 15"

Activity 4 – Writing AE Program using PeopleCode

Take 2 numbers as input and find out the greatest.

PeopleCode Hint:

If DYNCALL_SOA_AET.NUM1_SOA > DYNCALL_SOA_AET.NUM2_SOA Then DYNCALL_SOA_AET.AE_SECTION = "MSG1"Else DYNCALL_SOA_AET.AE_SECTION = "MSG2"End-If;

Handle the case when the user enters same values by giving proper messages.

Page | 44

3.2 Testing and Debugging Application Engine Program

During application development or production runs you may have a variety of program or system failures. The resolution of many of these problems will require you to troubleshoot your Application Engine program execution. To be able to dig into that execution, you have a variety of debugging tools to yield different levels of detail.

• Process Monitor

• Application Engine Trace File

• Application Engine Interactive Debugger

• PeopleCode Debugger

3.2.1 Process Monitor

For programs run through the Process Scheduler, the Process Monitor keeps track of the status of the programs during and after execution. It is one of the first things that should be inspected to determine if your program terminated correctly and if not, to see if any clues as to the problem are recorded.

The Details hyperlink provides additional information about the program.

Page | 45

Parameter

The Parameters hyperlink displays the command line that was used to execute the Application Engine program. This can be useful if an incorrect program name was used; you can identify the cause of your problem.

Message Log

This Message Log will provide messages supplied by your program.

Page | 46

View Locks

If you use any temporary tables in your program, they will be listed here during execution.

Batch Timings

The Batch Timings report contains a set of statistics that system administrators can use to tune the system to gain better performance. This report relates specifically to PeopleSoft Application Engine program performance.You have the option of setting up your Application Engine trace files to store statement timings traces to a table. When this is activated, you will see the batch timings displayed here.

View Log/Trace

When you click the View Log/Trace link, a new browser window opens, displaying links that enable you to view the message log and trace file in a browser. The View Log/Trace link appears on the Process Monitor Detail page when at least one of the following conditions is met:

• The output destination for the process request is Web, and the report and log files were successfully posted to the Report Repository by the Distribution Agent.

• The process must have a run status of Successful.

o If the report hasn't been transferred to the Report Repository, the run status of the process request remains Posting and the View/Log link is not visible.

o If the status of the request remains Posting, check the message log for messages from the Distribution Agent indicating that there were problems transferring files to the Report Repository.

• The process request ran from a PeopleSoft Process Scheduler Server Agent that was set up using the Server Definition page with a distribution node.

• You must have also selected to transfer log files to the Report Repository when you set up the preferences on this page.

Page | 47

3.2.2 Application Engine Trace File

For processes running on a Windows workstation, you can set your trace options in the Configuration Manager. This procedure is only valid when you are running the Application Engine program from the development environment or through a command line on the client.

The name of the trace file will depend upon whether or not a process instance number has been specified for your process run.

Process Instance Name of File

Without a Process Instance

AE_<Date/Time_Stamp>_<OS_PID>.AET

Where <Date/Time_Stamp> is in the format <mmddhhmmss>

<OS_PID> is the Operating System process id

With a Process instance

AE_<Program_name>_<Process_Instance>.AET

The trace files are placed in C:\Temp\PS\<database> directory.

Step Reports each step name that is executed, along with a timestamp, the Do level and the statement type.

SQL Same as Step, plus reports formatted SQL processes including COMMITs, ROLLBACKs, and Restarts.

Ded. Temp. Table Writes the details of the temporary tables used to the trace file – we will see this in action in both the temporary tables chapter and the performance tuning chapter later in the class.

Page | 48

Statement Timings (file) Initiates the AE timings trace, which monitors the execution timing of each statement and is written to the bottom of the AE Trace file.

Statement Timings (table) This option writes the Statement Timings traces to a table so you can store historical data in the database and do custom reporting.

DB Optimizer (file) The DB Optimizer trace reveals the execution/query plan for the SQL that your Application Engine program generates.

Activity 5 – Run AE Program using Trace on

Run the delivered AE program PER099 with trace on and see the trace file.

Application Engine Debugger

The Application Engine’s internal debugger can be used while running on the client or when running directly on the server—outside of the Process Scheduler. When you run your application, you’ll enter an interactive state in which you can set break points, issue COMMITs, look at the State record, etc.

The Application Engine debugger is turned on in the Configuration Manager.

• Open the Configuration Manager.

• First click on the Trace tab and turn off the Statement Timings.

• Click on the Profile tab.

• Click on the Edit push button.

Page | 49

On the Process Scheduler page, you will find the Application Engine options.

Debug Turning this option on will cause Application Engine to run in debug mode every time you invoke Application Engine in 2-tier.

Disable Restart This option will disable restart for all Application Engine programs run in 2-tier.

• Turn the debugger on and click OK.

• Click OK to save the new configuration.

After setting the Application Engine Debugger you need to

1. Execute the Application Engine program to debug.

2. At the Application Engine Debugger prompt, enter a command to enables a debugging option.

Each command is represented by a single letter, such as X, L, or M. Enter the letter that corresponds to the option you want to engage. To see a list of the available debugging options, enter “?” at the prompt.

Debugging Options

Option DescriptionQuit Enter Q. This option performs a rollback on the current unit of work in

the debugging run, and it ends the debugging session. It effectively terminates your Application Engine program.

Quit is useful for testing restart. Have some work committed and some uncommitted. Then, terminate the program at that point and roll back the pending work. You want to make sure the program restarts from the point of the last successful commit.

Exit This option is valid only after one step has completed and another has

Page | 50

Option Descriptionnot already begun. It is not valid once you reach the action level.

Use this option as an alternative to Quit. Exit ends the program run and the debugging session, but it also commits the current unit the program has already completed. This option can be helpful when testing your restart logic.

Commit Enter C. To commit the current unit of work in your program, use this option. It is valid only after a step has completed and before another has already begun. It is not valid once you reach the action level. You can use this option, for example, to use your database query tool to check the data in your database.

Break Enter B. Sets a breakpoint. When the program reaches the breakpoint, it temporarily halts execution to enable you to observe the state of the current process.

Breakpoint options include:Set: Enter S to set a breakpoint location.

The breakpoint location defaults to the current location in the program, but you can specify other sections or steps by overriding the default values that appear in the brackets. Unset: Enter U to remove breakpoints previously set.

List: Enter L to list breakpoints. When you enter this command, make sure that you have entered B first to specify the break option. If you just enter L from the main command prompt, you engage the Look option.

Look Enter L. Enables you to observe the values currently in the state record associated with the program you are debugging. You must specify the state record at the Record Name prompt. By default, the default state record as specified in your program properties appears with the brackets. You can also specify a specific field name on the state record in the Field Name prompt. To look at all the fields in the state record, leave the asterisk (*) within the brackets unchanged.

Modify Enter M. Enables you to modify the value of a state record value for debugging purposes. Suppose the previous steps did not set a value correctly. However, you may want to see how the rest of the program would perform if the appropriate value existed in the state record. This enables you to give your program some help in the debugging or testing phase.

As with the Look command, you must specify the appropriate state record (if you are using multiple state records), and you must specify one field. You can modify only one field at time.

Watch Enter W. When you specify a field as a watch field, the program stops when the value of the field changes.Similar to the Break command, you can specify options for Set, Unset, and List.

Step Over Enter S. Executes the current step to completion and stop at the next step in the current section.The behavior depends on the current level or the program. You start at the step level, and then can step into the action level. If you are at the step level and use step over, you go to the next step in the current

Page | 51

Option Descriptionsection, skipping over all actions (including any call sections). If you are at the action level, step over executes the current action and stops at the next action in the current step, or at the next step in the current section.

Step Into Enter I. Use this option to observe a step or called section in a more granular level. For instance, you can check each SQL statement and stop. By using this option and checking the state record at each stop, you can easily isolate problem SQL or PeopleCode. As with Step Over, the behavior depends on the level. At the step level, you can step into the action level and stop before the first action in the step. At the action level, if the current action is a call section, Step Into takes you to the first step in the called section. For other action types, Step Into acts the same as Step Over, because there is no deeper level in which to step.

Step Out of Enter O. After you’ve stepped into a step or called section, use the Step Out of option to run the rest of the current step or called section and stop. As with the previous step options, the behavior of Step Out of depends on the current level of the program. At the step level, Step Out of completes the remaining steps in the current section, returns to the calling section or step, and stops at the next action in that step. If the section is MAIN and is not called by another section or step, then Step Out of behaves the same as the Go option.

At the action level, Step Out of completes the current step and stops at the next step in the current section, or if the program is at the end of a section, Step Out of returns to the calling section or step.

Go Enter G. After the program has stopped at a specific location, and you’ve examined its current state, you can use the Go command to resume the execution of the program. This is a helpful command when you have breakpoints set. With this command, the program won’t stop at a step or action; it only stops at the next breakpoint or watch field, or when the program runs to completion.

Run to commit

Enter R. Resumes execution of your program after it has stopped. This command forces the program to stop again after the next commit. This is a good option to use when observing your commit strategy and how it will affect a restart.

Activity 6 – Run AE Program in Debug mode

Run the AE program created as part of Activity 3 in debug mode and use the various options.

3.3 Application Engine Process Definition

For each Application Engine program that you want to process via the process scheduler, you will need to create a process definition and a component designed to control the runtime parameters. PeopleSoft provides standard records and pages from which to clone.

Process Type

Page | 52

The primary purpose of the Process Type Definition is to globally define the command line, parameter list, working directory, and other general parameters. This means the information doesn't need to be duplicated for each Process Definition of the same type, and you can vary these global parameters as needed, depending on the target operating system and database platform.Navigate to the Process Scheduler Manager and search for the Process Type Definitions that start with A.

You will notice that we have both Application Engine and Application Engine Generic Test types. You will also notice that there are separate definitions for each database platform as well as operating system.

Open the Process Type Definition for Operating System used in the training and Database type used in the training.

Page | 53

Command Line points to the executable program PSAE.EXE, a PeopleSoft API program to run Application Engine programs. Enclose all server environment strings within a double pair of percent signs: %%TOOLBIN%%Parameter List contains the string of command line variables passed to the executable. The parameter list follows the same definitions as we saw for the command line. The variables are denoted by the %% and are retrieved from the system at run time.

Parameter Variables (meta-strings)

Description

-CT %%DBTYPE%% Database Type

-CD %%DBNAME%% Database Name

-CO %%OPRID%% User ID

-CP %%OPRPSWD%% User Password

-R %%RUNCNTLID%% Run Control ID

-I %%INSTANCE%% Process Instance

-AI %%PRCSNAME%% Process Definition

Process Scheduler uses a number of variables during run time. These are in the form of meta-strings, in-line bind variables, or client/server variables.The predefined meta-strings must also be enclosed in a set of double percent signs. When processing a request, if Process Scheduler encounters a string enclosed inside a double set of percent signs, it first compares the variable name with an internal list of predefined meta-strings. If the variable name is not one of these meta-strings, it is assumed to be a server-based environment variable.

Working Directory points to the directory containing the database drivers. The working directory is only applicable to the Client and Windows NT servers.

Restart Enabled enables a process request to be restarted from Process Monitor. Currently this option is applicable only to Application Engine process types that complete with a run

Page | 54

status of Unsuccessful. Another restriction regarding the ability to restart a process has to do with a user's security profile. Restart is only allowed if the user can currently update a request (Cancel or Delete).

Process Definition

For every Application Engine program that you want to run, you will need to define a process definition. The process definition must have the same name as the Application Engine program.

A Process Definition serves four primary purposes:

1. Identify the TYPE of Process to be run (via the Process Type)

2. Identify the PROGRAM name that you are running. (The Process Definition must be the exact same name as the Program.)

3. Identify WHERE the Program can be run from. (via the Component)

4. Identify WHO has security access to run it. (via the Process Group.)

Process Type When you define a process, you assign it to a Process Type. When you set up a Process Scheduler Server definition, you select which process types the Process Scheduler Server Agent is allowed to process for load balancing.

Process Name This name must match the program name of the process you are defining. For example, if you are defining an Application Engine program named MYTEST, you must define the process name as MYTEST.

Description Displays in Process Scheduler Request page and is aimed at the user.

Page | 55

Priority This defines the relative priority to determine which process to initiate first, if multiple processes are queued to run on a given Process Scheduler Server Agent.

API Aware Application Engine is API aware, so this checkbox will be on.

Process Definition Options

Server Name Enter a Process Scheduler Server name if you want to restrict this process to run on a specific Process Scheduler Server Agent. Otherwise, leave it blank and the task will find an available Process Scheduler Server Agent to run on based on the Process Type. Only active if the run location is set to server.

Recurrence Name Previously defined intervals set up for processes to run. If you add a recurrence name here, this process will use this recurrence every time you run this process. It will override the recurrence provided on the Process Request page or in ScheduleProcess PeopleCode function. Only active if the run location is set to server.

Component The component in the online system where the Process Definition will be accessible in the Process Request page.

Process Group The “family” of processes to which this process belongs is a logical collection of programs and reports needed for a particular job function. If you choose an existing group, users that have access to that group will immediately have access to this process. If you create a new Process Group, you will have to give access to users through Maintain Security. This field is case sensitive.

Page | 56

Steps to add Application Engine Process

1. Design: The design of your Application Engine program will determine the controls and parameters that will be defined on your run control. The information needs to be coded into your Application Engine program to access the data from your run control page.

2. Determine Run Control Record: For your application, you may be able to use one of the PeopleSoft delivered run control records. If you have unique requirements, you will need to create a new run control record.

3. Build the record: If you created a new run control record, then you will need to build the record.

4. Determine the Run Control Page: For your application, you may be able to use one of the PeopleSoft delivered run control pages. If you have created a new run control record, you will need to create a new run control page.

5. Place the run control page on a component: Place the run control page that you will be using with your Application Engine program on a component.

6. Use registration wizard to add the component to a menu, register component in portal and set security.

7. Create Process Definition: Create a process definition that has the same name as the Application Engine program. The process definition will associate the Application Engine program with the component that was set up in step 6. The process group will be used to enable users to run the program.

8. Once you have completed the setup, you will need to test running the Application Engine program from the new run control page.

Process Security

Once the standard security for accessing the pages has been activated you need to make sure that your user can run the process by making sure that they are attached to a permission list that is linked to the process group you put the process into. In this course, we are logging in as user PS. PS is a member of several roles and hence several permission list. The permission list that we use for all of the process access is found on the users general tab under the Process box.

Page | 57

Process Group Permission

The Process Groups page lists the various process groups associated with a Permission List. Process groups are collections of Process Definitions that you create using PeopleSoft Process Scheduler. After being created in Process Scheduler, you add process groups to permission lists on the Process Group Permission page.Users can run only those processes through Process Scheduler that belong to process groups assigned to their role.

Page | 58

Process Profile Permission

PeopleSoft Process Scheduler security involves more than just adding a few process groups to a permission list. You also need to specify to what capacity a role (or set of users) can modify certain Process Scheduler settings. The process profile definition (defined in Process Scheduler) determines the default Process Scheduler settings for a user.

Page | 59

4.1 Application Engine Program Execution

Depending on your implementation, you can invoke Application Engine programs by using any of the following methods:

• Batch. This is the most typical mode of execution. You invoke programs that run in this mode using Process Scheduler or the Application Engine Process Request Page. Batch mode is also referred to as asynchronous execution meaning that it runs independently in the background.

• Online. Application Engine programs that execute online, typically get executed from a page with the CallAppEngine PeopleCode function. Such online processes are synchronous meaning that subsequent processes wait on the results. For instance, a page may be "frozen" until the online process returns the necessary results. When the program is called synchronously, no COMMITs are issued until the program completes.

• Manual. To execute an Application Engine program in manual mode, you would use the command line. Usually, you only use this technique during testing or if you need to manually restart the program.

PSAE.exe LOGON

RETRIEVE AE PROGRAM DEFINITIONS

ISSUE AE PROGRAMSQL

EXECUTE AE CONSTRUCTS PEOPLE CODE PROGRAMCALLS

PS Database

System CatalogTables

PeopleToolsTables

TablesApplication

Process Scheduler

Manual BatchAsynchronous

Process Scheduler Request

Application Engine Process Request page

CallAppEngine PeopleCode

OnlineSynchronous

Push button

Command line

Bat file

Executing Application Engine Program with Process Scheduler

PeopleSoft's Process Scheduler will maintain status of your executing program and execute your application asynchronously from the initiation process. PeopleTools Process Scheduler Manager is a centralized tool that enables application developers, system administrators, and application users to manage PeopleSoft batch processes. Using the PeopleSoft Internet Architecture (PIA), you can access a list of processes through a Web browser and schedule a process request.

Page | 60

You can schedule processes to run locally on a client workstation or remotely on a database server without having to exit the PeopleSoft system. And once a scheduled process begins, Process Monitor enables you to monitor the important details of PeopleSoft batch processes, such as where certain programs ran, where and when reports were printed, and what command-line parameters were passed to third-party programs with which Process Scheduler Manager interacts. The following example helps to illustrate the physical relationship between the basic components of the Process Scheduler in the PeopleSoft Internet Architecture.

PeopleSoftApplication

Server(PSAPPSRV,

PSSAMSRV, etc.)

Process

SchedulerServer

(PSNT)

Application ServerWorkstation

PSPRCSRQST

Database Server

Jolt

SQL

SQL

ServerProcess

ClientProcess

API Code

Process Operator Server Process Type StatusPROCESS_ORD2 PTTRN PSNT APPLICATION

ENGINESuccess

API Code

Web Server

PeopleSoftWeb

Server

HTML

Developing for Synchronous Program Execution with a 'Command' Pushbutton

1. Create a Record with a field into which you will code 'FieldChange' PeopleCode. Code a ''CallAppEngine"' to your Application Engine program.

2. Add a Push Button to your page.

3. In the Push Button Properties select Pushbutton Type as Command and select the Record Field that has the PeopleCode.

CallAppEngine

CallAppEngine starts the Application Engine program named applid. This is a way of starting your Application Engine programs synchronously from a page. The syntax is:

CallAppEngine(applid [, statereclist ]);

Where statereclist is list of record objects in the form:&staterecord1 [, &staterecord2] . . .

There can only be as many record objects in statereclist as there are state records for the Application Engine program. Additional record objects will be ignored.

MessageBox

MessageBox is a PeopleCode function used to display a message box. The syntax is:

Page | 61

MessageBox(style, title, message_set, message_num, default_txt [, paramlist])

where paramlist is an arbitrary-length list of parameters of undetermined (Any) data type to be substituted in the resulting text string, in the form:param1 [, param2]...

Executing Application Engine Program with a windows command scriptIn some cases, you may want to invoke an Application Engine program through the command line. For instance, this method is typically used in the following situations:

• Restarting. When a program abends, a system administrator might restart the program using the command line. If needed, you can locate all of the specific program/process information from the Process Monitor on the Process Request Detail dialog. Normally, end users (or system administrators) will perform a Restart from the Process Monitor.

• Development/Testing. Many developers include the command line in a batch file to launch a program they are developing or testing. This way, they can quickly execute the batch file as needed. This also allows separation of development of the Application Engine program from its associated pages.

• Debugging. You can set up the command line to debug, which offers an alternate method of invoking the Application Engine debugger without turning it on for all programs executed through the client. To debug a program with a Run Location of Server, you can log into the server (using Telnet, for example) and invoke the program from the command line.

In addition to the path where PSAE.exe resides listed below are the minimum parameters that are required.

Description Parameter

Database platform (e.g. Microsoft, Oracle, DB2, etc.)

-CT <dbtype>

Database name -CD <database name>

Logon ID -CO <oprid>

Password -CP <oprpswd>

Run Control -R <run control id>

Application Engine program -AI <application id>

There are additional parameters that can be added for tracing and debugging. See the complete list in section 4.5.

Page | 62

4.2 Temporary Table

Since Application Engine programs run in batch, it is very likely that multiple instances of the same program can be running in parallel. When batch programs begin running simultaneously, they introduce a significant risk of data contention and deadlocks on common tables and/or temporary tables.For Application Engine programs, PeopleTools provides a feature that enables you to drastically reduce the risk of table contention by dedicating specific instances of temporary tables for each program run. With this feature, you have a pool of temporary table instances, and within that pool some tables are dedicated to particular programs, and some instances are undedicated, meaning they are "shared."

Using Temporary Table for parallel processingThe ability for multiple instances of the same program to run simultaneously is called parallel or concurrent processing.For batch process there can be potential performance improvements by splitting the data to be processed into groups and simultaneously running multiple instances of your program to deal with different groups of data.If you have a program that uses a temporary table and is invoked multiple times, that single temporary table could be used concurrently in multiple executions of the code. This could create unpredictable results since the different instances of the code would be issuing Deletes, Inserts, and/or Updates unsynchronized with each other.

You could solve the problem by creating multiple temporary tables as a pool of tables. Each invocation of your program would have to allocate an unused temporary table, mark it as ‘in use’, use it and release it back to the pool when you are through with it (for each Application Engine program you write). PeopleSoft 8 introduces a Temporary Table record type definition. You are able to define a record and set its Type to Temporary Table. By doing this the PeopleSoft Build process will build multiple uniquely named copies of your Temporary Table as a pool. Additionally, it does Temporary Table management for your Application Engine programs. You can code your program with supplied meta-SQL, so each execution of your Application Engine program will be given access to its own copy of the Temporary Table for its exclusive use. When the program ends the table will be returned to the pool of Temporary Tables.

Application Engine programs are designed for two types of execution and each has its own pool of Temporary Tables:

Online Batch

Invoked by CallAppEngine from PeopleCode.

Invoked through the Process Scheduler.

Run quickly, synchronously, and at random times.

Run for longer amounts of time, asynchronously, and at scheduled times.

Potential for simultaneous execution. Can be designed for parallel execution for performance.

Uses the Online Temporary Table pool. Uses the Batch/Dedicated Temporary Table pool.

Not Restartable Restartable

Page | 63

Steps to Implement Parallel Processing

1. Define your Temporary Tables by defining and saving your Temporary Table records as type Temporary Table.

PeopleSoft recommends that you insert the PROCESS_INSTANCE field as a key on any temporary tables you intend to use with Application Engine. Application Engine expects Temporary Table records to contain the PROCESS_INSTANCE field. When all instances of a Temporary Table are in use and the Temp Table runtime options are set to "Continue," PeopleTools will insert rows into the base table using PROCESS_INSTANCE as a key. If you opt not to include PROCESS_INSTANCE as a key field in a Temporary Table, you should change the Temp Table runtime options to "Abort" in the appropriate Application Engine programs.

2. Set the Temporary Table Online pool. This will set the basic Temporary Table Online pool based on the PeopleTools Options specifications.

Application Designer builds the Temporary Table instances at the same time it builds the base table for the record definition. When Application Designer builds a table (as in, Build, Current Object) and the Record Type is Temporary Table, it determines the total number of instances of the Temporary Table to build based on sum of the following two items.

• The Temporary Table Instance (Total) specified on the PeopleTools Options page.

• Plus the sum of all instance count values specified in all of the Application Engine programs that use that temporary table.

Application Designer only creates a maximum of 99 Temporary Table instances, even if the sum exceeds 99 for a particular Temporary Table.

In the PeopleTools Options page, the number of temporary tables that can be used online (CallAppEngine) is established.

Page | 64

There are 2 fields that are used in connection with the temporary table instances.Temp Table Instances (Total) and Temp Table Instances (Online). These 2 values should always be the same except for EPM.

3. Assign Temporary Tables to your Application Engine program in its Program Properties, setting the appropriate number of Instance Counts and Runtime option.

4. Set Temporary Table Batch Pool by setting the instance count in the program properties.

5. Build /Rebuild your Temporary Table record. This will build the necessary Batch temporary tables into that record’s Temporary Table pool for use at execution time.

The actual number of Batch Temporary Tables built is the sum of the Instance Counts in all the programs that have assigned that table as a Temp Table. For example, with three Application Engine programs using a variety of Temporary Tables with different Instance Counts.

Page | 65

TX

AE1 Program Properties

TX TY TZ

4 Instance Count

Temp Tables

PS Options

(Total) (Online)

3 3 TX1

TX2 TX3 TX4 TX5 TX6 TX7 TX8

AE2 Program Properties

TX TY 3 Instance

Count

AE3 Program Properties

TX 0 Instance

Count

TX9 TX10

The sum of the

Instance Counts

Online

6. Code %Table meta-SQL as references to Temporary Tables in your Application Engine program, so that Application Engine can resolve table references to the assigned Temporary Table instance dynamically at runtime.

To reference a temp table (Online or Batch), you need to use: %Table(record)

You can reference any table with %Table, but only those records defined as Temporary Tables get replaced by Application Engine with a numbered instance of a Temporary Table from the Temporary Table pool.

For batch/dedicated Temporary Tables, when Application Engine resolves any %Table, it checks an internal array to see if a Temporary Table instance has already been chosen for the current record. If so, then Application Engine substitutes the chosen table name. If there are no more batch/dedicated instances available, then Application Engine uses the base table instance (PS_recname) by default. Regardless of whether %Table is in PeopleCode SQL or in an Application Engine SQL Action the program uses the same physical SQL table.

Multiple Program Instances Running against Multiple Temporary Table Instances The Application Engine runtime program invokes logic to pick one of the available instances. Once each program instance gets matched with an available Temporary Table instance, the %Table meta-SQL construct resolves to use the corresponding Temporary Table instance. Run control parameters passed to each instance of the MYAPPL program enable it to identify which input rows "belong" to it, and each program instance inserts the rows from the source table into its assigned temporary table instance using %Table. For example, for the first instance started, the Select would be resolved with the following:

Select PROCESS_INSTANCE,TEMP_COL1,TEMP_COL2

from PS_MYAPPLTMP01

Page | 66

PS_MYAPPLTMP1

First 500,000 Rowsof Affected Data

PS_MYAPPLTMP2

Second 500,000 Rowsof Affected Data

MYAPPL2

MYAPPL1

Clear Temporary Tables (%TruncateTable)

You do not need to delete data from a Temporary Table manually. The Temporary Tables are truncated automatically when they are assigned to your program. If the shared base table has been allocated because no batch/dedicated instances were available, then Application Engine performs a delete by process instance instead of performing a truncate. In such a case, the PROCESS_INSTANCE is required as a high-level key. You can perform additional deletes of Temporary Table results during the run, but you will need to include your own SQL Action that does a %TruncateTable.

4.3 Set Processing

Although Application Engine achieves high performance while processing row-by-row programs, the real secret to gaining outstanding performance with Application Engine is to employ a technique called Set Processing.Set Processing uses SQL to process groups, or sets, of rows at one time rather than processing each row individually. With Row by Row Processing you follow a repetitive loop that selects a row, determines if it meets a given criteria, if so, apply rule x to row, update row, commit, select next row, and so on. With Set Processing, you only select those rows that meet the filtering criteria and then run the rule once against all the affected rows.The rule that you are applying will use an UPDATE or INSERT/SELECT SQL statement. The bulk of the performance gain lies in the fact that the processing occurs in the database engine as opposed to in the Application Engine Program.Since the data never leaves the database engine (whether or not it remains in the same table), you effectively eliminate the network round trip and database API overhead required to pull the data into the application program and then insert the results back into the database.

Note: The only SELECT statements used in Set Processing appear in sub-queries of an INSERT or UPDATE statement.

Advantages of Set Processing

In most situations encountered with PeopleSoft applications, perhaps 95%, Set Processing can be implemented to improve performance. This includes those instances in which row-by-row processing seemed like the only alternative. The following list includes some of the major reasons why you are encouraged to use Set Processing and some of the key benefits that you can expect from a set-based program.

• Improved Performance. In an overwhelming majority of cases, Set Processing performs significantly better than its row-by-row counterpart for "reasonable" batch processing volumes. Set-Based programs tend to scale in a geometric manner. Row-

Page | 67

by-row processing scales in a more linear manner. When you encounter higher volumes, row-by-row processing can sometimes become overwhelmed.

• Minimized (PeopleTools) SQL Overhead. Set Processing is important with Application Engine because Application Engine has built in checkpoint/restart capabilities. Since Application Engine records the status of a program run, there is an avoidable degree of overhead associated with each Application Engine SQL statement that gets executed. If you use Set Processing, you would tend to use fewer Application Engine SQL Statements that each processed more data than the statements executed in row-by-row processing. As a result, the Application Engine overhead becomes less of a factor in the overall program.

• Easy Maintenance. Maintenance activities include tuning, fixing, adding enhancements, and so on. Suppose your application logic is in set-based SQL rather than COBOL. If you need to make a fix or add an enhancement to SQL, it’s just a matter of modifying the SQL or inserting the new “chunk”. With COBOL, you first need to identify all the lines of code affected, modify each line of code, and then re-compile and re-link the program. In short, SQL offers more immediate results.

• Leveraging the RDBMS. With set-based processing, you take advantage of the SQL processing engine on the database rather than placing the processing burden and overhead on the application executable. And, as the RDBMS systems get more sophisticated, set-based programs will reap even more performance gains. Using a row-by-row approach and pulling data into the program for each transaction degrades performance regardless of where the program runs. Even if the batch server and database engine are on the same physical machine, the data still travels through a variety of API layers.

Activity 7 – Write AE Program using set processing

Re develop the AE program written as part of activity 3 using set processing.

4.4 Meta-SQL

You can write SQL within PeopleSoft Application Engine, or you can copy SQL statements into Application Engine from any SQL utility with few, if any, changes. This enables you to write and tune SQL statements before you try to incorporate them into an Application Engine program.

Database platforms can have different syntax rules, especially in regard to date, time, and other numeric calculations. Generally, you can work around syntax differences using PeopleSoft meta-SQL, which PeopleSoft Application Engine supports. Meta-SQL is a set of predefined terms (meta-strings), designed to replace relational database management system (RDBMS)-specific SQL syntax with a common syntax.

In addition, PeopleSoft meta-SQL enables you to dynamically generate portions of SQL code. For example, to join two tables based on their common keys, use the following meta-string:

%Join(COMMON_KEYS, PSAESECTDEFN ABC, PSAESTEPDEFN XYZ )

At runtime, the function would be expanded into the following:

ABC.AE_APPLID = XYZ.AE_APPLID

AND ABC.AE_SECTION = XYZ.AE_SECTION

Page | 68

AND ABC.DBTYPE = XYZ.DBTYPE AND ABC.EFFDT = XYZ.EFFDT

The following table lists the Meta-SQL available to use in your Application Engine applications. For a full list and description refer to PeopleSoft PeopleBooks PeopleCode, PeopleCode Reference, Meta-SQL.

Meta-SQL Description

%Bind The Application Engine function, %Bind, is used to retrieve a field value from a State Record. The %Bind function can be used anywhere in a SQL statement.

Syntax:

%BIND([recordname.]fieldname[,NOQUOTES][,NOWRAP][,STATIC])

%ExecuteEdits The %ExecuteEdits construct is Application Engine-only meta-SQL. You can’t use it in COBOL, SQR, or PeopleCode—not even Application Engine PeopleCode. This function allows Application Engine to support data dictionary edits in batch.

Syntax:

%ExecuteEdits(<type>, recordname [alias][, field1, field2, ...])

%InsertSelect The %InsertSelect function will generate an INSERT statement with a SELECT for you. It does not generate the FROM statement. You must specify all the select records before you specify any over ride fields.

The INSERT column list is composed of all the fields in the specified insert_recname, with the exception of LONG_CHAR or IMAGE fields.

Syntax

%InsertSelect([DISTINCT, ]insert_recname, select_recname [ correlation_id][, select_recname_n [ correlation_id_n]] [, override_field = value]. . .)

%Join Use the %Join function to dynamically build a WHERE clause joining one table to another. At runtime, the entire function will be replaced with a character string.

Syntax

%Join({COMMON_KEYS | COMMON_FIELDS}, join_recname

[ correlation_id1], to_recname [ correlation_id2]

[, override_field_list])

where override_field_list is an arbitrary-length list of fields to be substituted in the resulting text string, in the form:

field1 [, field2]. . .

%List The %List function expands into a list of field names, delimited by

Page | 69

Meta-SQL Description

commas. Which fields are included in the expanded list depends on the parameters passed to the function.

Syntax:

%List({FIELD_LIST | ORDER_BY | SELECT_LIST | NONULL_FIELDS | SELECT_LIST_NOLONGS | FIELD_LIST_NOLONGS}, recordname [ correlation_id])

%Select A %SELECT is required at the beginning of any and all SELECT statements. For example, you need one in the Flow Control Actions as well as one in the SQL Actions that contain a SELECT. The %SELECT function identifies the State Record fields to hold the values returned by the corresponding SELECT statement. In other words, you use %SELECT to pass values to the State Record buffers.

You use the %SELECT construct to pass variables to the State Record, and you use the %BIND construct to retrieve or reference the variables.

The syntax for %SELECT is:

%SELECT(statefield1[, statefield2]…[, statefieldN])

SELECT field1[, field2]…[, fieldN]

%SelectInit This meta-SQL construct, %SelectInit, is identical to %SELECT barring the following exception. If the SELECT returns no rows, %SelectInit reinitializes the buffers. In the case of a %SELECT and no rows are returned, the State Record fields retain their previous values.

%SQL When you use %SQL in a statement, Application Engine is replaces it with the specified SQL object. This allows commonly used SQL text to be shared among Application Engine and PeopleCode programs alike.

%Table The %Table function returns the SQL table name for the record specified with recname. The basic syntax is as follows:

%Table(recname)

If the record is a temporary table and the current process has a temporary table instance number specified, then %Table resolves to that instance of the temporary table

%TruncateTable The %TruncateTable meta-SQL construct is functionally identical to a DELETE SQL statement with no WHERE clause, but it is faster and requires less resources on databases that support bulk deletes. If you’re familiar with COBOL this construct is an enhanced version of the COBOL meta-SQL construct with the same name.

The basic syntax for %TruncateTable is

%TruncateTable(table name)

Page | 70

Meta-SQL Description

%UpdateStats Application Engine replaces this meta-SQL construct with a platform dependent SQL statement that updates the system catalog tables used by the database optimizer in choosing optimal query plans. We intend that you use this construct after your program has inserted large amounts of data into a temporary table that will be deleted before the end of the program run. This saves you from having to use "dummy" seed data for the temporary table and having to update statistics manually.

The basic syntax for %UpdateStats is

%UpdateStats(table name)

Meta SQL Date and Time Functions

Meta-SQL Description

%CurrentDateIn Expands to a platform-specific SQL substring representing the current date in the WHERE clause of a SQL SELECT or UPDATE statement, or when the current date is passed in an INSERT statement.

%CurrentDateOut Expands to platform-specific SQL for the current date in the SELECT clause of an SQL query.

%DateIn(dt) Where dt is either a Date value or a date literal in YYYY-MM-DD format, expands into platform-specific SQL syntax for the date. %DateIn should be used whenever a date literal or Date bind variable is used in a comparison in the WHERE clause of a SELECT or UPDATE statement.

%DateOut(dt) Where dt is a date column, causes dt to be converted from its platform-specific format to PeopleSoft format when the value is received from the database server.

%DateTimeIn(dtt) Expands to platform-specific SQL for a DateTime value being sent to the database server. The parameter dtt is either a Datetime bind variable or a string literal in the form:

MM/DD/YY[YY] hh:mm:ss.ssssss [{AM|PM}]

%DateTimeOut(datetime_col) Where datetime_col is a Datetime column, causes datetime_col to be converted from its platform-specific format to PeopleSoft format when the value is received from the database server.

%TimeIn(tm) Expands to platform-specific SQL for a DateTime value being sent to the database server. The parameter tm is either a Time bind variable or a string literal in the form:

hh:mm:ss.ssssss [{AM|PM}]

%TimeOut(tm) Where time_col is a time column, causes time_col to be converted from its platform-specific format to PeopleSoft format when the value is received from

Page | 71

the database server.

Application Engine System (Meta) Variables

Variable Description

%AeProgram Returns a quoted string containing the currently executing Application Engine program name.

%AeSection Returns a quoted string containing the currently executing Application Engine Section name.

%AeStep Returns a quoted string containing the currently executing Application Engine Step name.

%JobInstance Returns the numeric (unquoted) Process Scheduler Job Instance.

%ProcessInstance Returns the numeric (unquoted) Process Instance.

%ReturnCode Returns the numeric (unquoted) return code of the last SQL operation performed.

%RunControl Returns a quoted string containing the current Run Control identifier. The Run Control ID is available to your program, when using %RunControl, regardless of whether there's a row in the AEREQUEST table.

%AsOfDate Returns a quoted string containing the “AsOfDate” used for the current process.

%Comma Returns a comma. This is useful in those cases where you need to use a comma, but commas are not allowed due to the parsing rules. For example, you might use this if you wanted to pass a comma, as a parameter, to the %SQL meta-SQL function.

%LeftParen Returns a left parenthesis. Usage is similar to %Comma.

%RightParen Returns a right parenthesis. Usage is similar to %Comma.

%Space Returns a single space. Usage is similar to %Comma.

%SQLRows This construct can be used in any Application Engine SQL statement, but the underlying value is only affected by SQL Actions. It is not affected by the program flow control Actions: Do When, Do Select, Do While, and Do Until. Regardless of where it’s used, the semantic remains the same: "How many rows were affected by the last SQL action?"

For SELECT statements, the value can only be 0 or 1: row not found or rows found, respectively. It does not reflect the actual number of rows that meet the WHERE criteria. In order to find the number of rows that meet the WHERE criteria, you need to code a

Page | 72

Application Engine Macros

Application Engine Macro

Description

%ClearCursor Use the %CLEARCURSOR function to recompile a re-used statement and reset any STATIC %BINDs. The proper syntax is as follows:

%CLEARCURSOR([ program, ]section, step, type)

or

%CLEARCURSOR(ALL)

When you use the %CLEARCURSOR function keep the following items in mind:

• It must be located at the beginning of the statement.

• It can be the only function or command contained in the statement.

• The Action must be SQL.

%Execute The %EXECUTE function allows you to execute RDBMS-specific commands from within your Application Program. The syntax is as follows:

%EXECUTE([/])command1{; | /}command2{; | /}…commandN{; | /}

By default, Application Engine expects a semi-colon to be used to delimit multiple commands within an %EXECUTE function statement. You can instruct Application Engine to use a forward slash (/) delimiter instead by placing a forward slash inside the function parentheses.

%Next and %Previous

This construct is valid in any Application Engine SQL Action, and we intend that you use it when performing sequence-numbering processing. Typically, you’d use it in place of a %Bind. These constructs use the current value of the number field as a Bind variable, and then increment (%Next) or decrement (%Previous) the value after the statement is executed successfully. By “number” field, we are referring to the numeric field on the State Record that you have initially set to a particular value (as in ‘1’ to start).

%RoundCurrency This meta-SQL is an enhanced version of the Application Engine &ROUND construct that appeared in previous releases. The %ROUNDCURRENCY function rounds an amount field to the currency precision specified by the field’s Currency Control Field property—as defined in Application Designer’s Record Field Properties dialog. For this function to work, you must have the Multi-Currency option selected in the PeopleTools Options page. As with all Application Engine macros described in the current section, this construct is only valid in Application Engine SQL; it is not valid for SQLExecs or view text.

The syntax for %RoundCurrency is:

%RoundCurrency( <EXPRESSION>, [ALIAS.]<CURRENCY_FIELD>)

Page | 73

4.5 Command Line Parameters

Parameter Description and Values

-CT <dbtype> Required. Corresponds to the type of database to which you are connecting. Valid values are MICROSFT, ORACLE, SYBASE, INFORMIX, DB2UNIX, and DB2.

-CD <server> Required for Sybase, Informix. For those platforms that require a server name as a part of the signon, enter the appropriate server name. This affects Sybase, Informix, and Microsoft SQL Server. However, for Microsoft SQL Server, this option is valid but not required.

-CD <database name> Required. Enter the name of the database to which the program will connect.

-CO <oprid> Required. Enter the userid that is running the program.

-CP <oprpswd> Required. Enter the password associated with the userid.

-R <run control id> Required. Enter the Run Control ID for this run of the program.

-AI <application id> Required. Specify the Application Engine program to run.

-I <process_instance> Required for Restart. Enter the Process Instance for the program run. The default is 0, which means Application Engine uses the next available process instance.

-DEBUG <Y/N> This parameter controls the Debug utility. Enter Y to indicate that you want the program to run in debugging mode, or enter N to indicate that you do not.

-DR <Y/N> This parameter controls Restart. Enter Y to disable Restart, or enter N to enable Restart. (DR represents Disable Restart).

Page | 74

Parameter Description and Values

-TRACE <value> This parameter turns on the Application Engine trace. To enable tracing from the command line, enter this parameter and a specific Trace value.

-DBFLAGS Using this parameter, you can disable the %UpdateStats meta-SQL construct.

To disable %UpdateStats enter: -DBFLAGS 1

-TOOLSTRACESQL <value>

Use this parameter to enable SQL Trace.

-TOOLSTRACEPC <value>

Use this parameter to enable the PeopleCode Trace

Psae <Parmfile> If you submit a file to Application Engine as the first parameter in the command line, Application Engine reads the contents of the file and interprets the contents as if it were parameters entered on the command line. This option is intended mainly for the Windows NT and UNIX process Scheduler environment. For example:

Psae $temp/myparmfile.txt

Note: For security reasons, after Application Engine interprets the contents of the <parmfile>, it immediately deletes the <parmfile>

AE Trace Options

1 Trace STEP execution sequence to AET file

2 Trace Application SQL statements to AET file

4 Trace Dedicated Temp Table management to AET file

128 Timings Report to AET file

256 Method/BuiltIn detail instead of summary in report

1024 Timings Report to tables (ignored if Prcs. Instance=0)

2048 DB optimizer trace to file

4096 DB optimizer trace to tables

Page | 75

SQL Trace Options

1 SQL statements

2 SQL statement variables

4 SQL connect, disconnect, commit and rollback

8 Row Fetch (indicates that it occurred, not data)

16 All other API calls except ssb

32 Set Select Buffers (identifies the attributes of columns to be selected).

64 Database API specific calls

128 COBOL statement timings

256 Sybase Bind information

512 Sybase Fetch information

4096 Manager information

8192 Message Agent information

PeopleCode Trace Options

1 Trace instructions

2 List the program

4 Show assignments to variables

8 Show fetched values

16 Show stack

64 Trace start of programs

128 Trace external function calls

256 Trace internal function calls

512 Show parameter values

1024 Show function return value

2048 Trace each statement in program

Page | 76