PeopleTools 8.52 : Application Engine

Using Meta-SQL and PeopleCode

This chapter provides an overview of Application Engine meta-Structured Query Language (SQL) and discusses how to:

Use PeopleCode in Application Engine programs.
Include dynamic SQL.
Use Application Engine meta-SQL.

Understanding Application Engine Meta-SQL

Application Engine meta-SQL is divided into the following categories:

Construct

A construct is a direct substitution of a value that helps to build or modify a SQL statement.
Function

A function performs an action on its own or causes another function to be called.
Meta-variable

A meta-variable allows substitution of text within SQL statements.

Note. Some meta-SQL elements can be used only in Application Engine programs, some can be used both in Application Engine programs and in other environments, and some cannot be used in Application Engine programs at all. This PeopleBook discusses only meta-SQL elements that can be used in Application Engine. You can find a complete reference to all PeopleSoft meta-SQL elements in PeopleTools 8.52 PeopleBook: PeopleCode Language Reference.

See Also

Using Application Engine Meta-SQL

Understanding Meta-SQL

Using PeopleCode in Application Engine Programs

This section provides an overview of PeopleCode and Application Engine programs and discusses how to:

Decide when to use PeopleCode.
Consider the program environment.
Access state records with PeopleCode.
Use If/Then logic.
Use PeopleCode in loops.
Use the AESection class.
Make synchronous online calls to Application Engine programs.
Use the file class.
Call COBOL modules.
Call PeopleTools application programming interfaces (APIs).
Use the CommitWork function.
Call WINWORD Mail Merge
Use PeopleCode examples.

Understanding PeopleCode and Application Engine Programs

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

Scope of Variables

This table presents the different types of variables typically used in Application Engine programs and their scope:

Type of Variable	Scope	Comments
State record (work record)	Transaction (unit of work)	Using a work record as your Application Engine state record means that the values in the work record cannot be committed to the database. Commits happen as directed, but any values in work records are not retained after a commit.
State record (database record)	Application Engine program	Using a database record as your Application Engine state record preserves the values in the state record on commit, and the committed values are available in the event of a restart.
Local PeopleCode variables	PeopleCode program	Local PeopleCode variables are available only for the duration of the PeopleCode program that is using them.
Global PeopleCode variables	Application Engine program	Global PeopleCode variables are available during the life of the program that is currently running. Any global PeopleCode variables are saved when an Application Engine program commits and checks points; therefore, they are available in the event of a restart.
Component PeopleCode variables	Application Engine program	Component PeopleCode variables act like global variables in Application Engine.

Action Execution Order

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

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

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

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

Example of action execution hierarchy

Deciding When to Use PeopleCode

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

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

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

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

mem = nrows * (row overhead + nrecords * (rec overhead + nfields * (field overhead) + average cumulative fielddata))

where

mem is the amount of memory required to store the rowset.
nrows is the number of rows.
row overhead is the overhead per row.
nrecords is the number of records per row.
rec overhead is the record overhead (approximately 40 bytes).
nfields is the number of fields in the record.
field overhead is the overhead per field (approximately 80 bytes).
average cumulative fielddata is the average amount of data per field.

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

Considering the Program Environment

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

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

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

Passing Parameters Through the CallAppEngine Function

For individual page fields and simple PeopleCode variables such as numbers and strings, you can use the CallAppEngine PeopleCode function to pass values as parameters.

To use the CallAppEngine function:

Declare a record object in PeopleCode.

For example, Local Record &MyRecord;.
Assign record objects to any state record that you want to pass to the Application Engine program.

Record objects are parameters to the CallAppEngine function.
Set the appropriate values on that state record.
Include the record object in the function call.

After these values are set in the state record, all the actions in a particular program, not just the PeopleCode actions, can use the values.

Defining Global Variables

You can define global variables or objects in PeopleCode before you call an Application Engine program. Application Engine PeopleCode actions are able to access only the variables you define; however, the PeopleCode could set a state record field equal to a number or string variable for use by other Application Engine actions.

Also, an Application Engine PeopleCode program can read or update a scroll area or a grid using a global rowset object. When accessing a scroll area or a grid from Application Engine PeopleCode, the same rules apply and the same illegal operations are possible that you see with accessing PeopleCode not in an Application Engine program.

The parameters submitted in a CallAppEngine are by value. These parameters seed the specified Application Engine state record field with a corresponding value. If that value changes within Application Engine by updating the state record field, then the component data will not be affected. The only way to update component buffers or external PeopleCode variables from Application Engine is to use global PeopleCode variables and objects.

See Also

CallAppEngine

Accessing State Records with PeopleCode

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

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

You can use a PeopleCode assignment instead:

MY_AET.MY_COLUMN = "BUSINESS_UNIT";

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

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

State records are unique to Application Engine programs.
Within Application Engine PeopleCode, state record values can be accessed and modified using the standard recordname.fieldname notation.

Note. When you launch an Application Engine program from PeopleSoft Process Scheduler, you can generate a process warning status after the program completes by including and modifying the AE_APPSTATUS field in a state record. You can generate the warning status by setting AE_APPSTATUS to a value of 1.

Using If/Then Logic

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

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

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

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

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

Using PeopleCode in Loops

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

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

Note. Null bind values of type DateTime, Date, or Time are always resolved into literals.

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

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

Using the AESection Class

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

The AESection class provides the following flexibility:

Portions of SQL are determined by checks before a run.
The logic flow conforms as rules change, and the program adjusts to the rules.

When using an AESection object:

Ensure that you require primarily dynamic capabilities with the SQL your program generates.
Ensure that the rules to which your program conforms are relatively static or at least defined well enough that a standard template could easily accommodate them.
Consider using SQL definitions to create dynamic SQL for your programs to avoid the complexity created by the AESection object using the StoreSQL function.
The AESection class is designed to dynamically update SQL-based actions only, not PeopleCode, Call Section, or other actions.

You can add a PeopleCode action to your generated section, but you cannot alter the PeopleCode.
The AESection class is designed to use for online processing.

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

Note. Do not call an AESection object from an Application Engine PeopleCode action.

See Also

AESection Class

Making Synchronous Online Calls to Application Engine Programs

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

Note. If you make a synchronous call, users cannot perform another PeopleSoft task until the Application Engine program completes. Consider the size and performance of the Application Engine program called by CallAppEngine. You should ensure that the program will run to successful completion consistently within an acceptable amount of time.

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

See Also

CallAppEngine

Using the File Class

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

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

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

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

See Also

File Class

Calling COBOL Modules

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

PTPECOBL Program

The PTPECOBL interface program is a PeopleSoft executable that enables you to invoke your called COBOL module and pass it required values. You code the RemoteCall function to invoke PTPECOBL, which in turn calls the specified COBOL module.

If you use PTPECOBL, you do not have to write your own executable to process this task. However, PTPECOBL does not perform any SQL processing other than retrieving a list of state record values. Consequently, if your current logic requires previous SQL processing, you may want to write your own executable file to call your COBOL module. In most situations, PTPECOBL saves you from having to write a custom executable file to handle each call to a generated dynamically loadable code (.GNT) file.

PTPECOBL performs the following tasks:

Initializes the specified state record in memory.
Invokes the COBOL module specified in your PeopleCode.
Submits required parameters to the called COBOL module.
Updates the state record as necessary, issues a commit, and then disconnects from the database after your program completes.

Note. While your COBOL program runs, it can access and return values to the state record.

Shared Values in Application Engine and COBOL

The following options are available for sharing values between the Application Engine program and a called COBOL program:

Use state records.

If you add field names, Application Engine enables you to pass state record values to the called COBOL program and to get changes passed back to the calling PeopleCode program. If you pass the state record values in this way, use PTPECACH to retrieve and update values just as PTPEFCNV does.
Code custom SQL.

If you do not pass initial values using state record fields, you need to insert the appropriate SQL in your called COBOL module to retrieve the appropriate values. Then, to return any updated values to the calling Application Engine program, you must insert the appropriate SQL into a PeopleCode program.

If your COBOL program needs values that do not appear in a state record field, then you can pass PeopleCode variables and values. These variables and values are then retrieved and updated by calling PTPNETRT from within your COBOL program.
Create a custom executable file.

If you include extra SQL processing and use non-state record values, for consistency purposes, creating a custom executable file might be a better approach. It enables you to call your program directly and have it perform all the PTPNETRT processing. Remember that a RemoteCall command can only call an executable program, not a GNT file.

Syntax and Parameters

This example shows a sample RemoteCall function issued from an Application Engine PeopleCode action to a COBOL module:

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

This table describes each parameter in the RemoteCall function:

Parameters	Description
PSRCCBL	The Remote Call dispatcher, which runs the specified COBOL program using the connect information of the current operator.
PSCOBOLPROG	Specify the name of the COBOL program to run, which in this case is PTPECOBL. This parameter makes the remote call from Application Engine distinct from a normal remote call. When you enter this parameter, in effect you enable the following parameters, some of which are required.
AECOBOLPROG	Specify the name of the COBOL module you are calling; for example, MY_GNT.
STATERECORD	Specify the appropriate state record that your Application Engine program will share with your COBOL module; for example, MY_AET. PTPECOBL then reserves space in memory for all of the fields in the state record, regardless of whether they will ultimately store values for processing.
PRCSINST	Specify the state record and Process Instance field; for example, MY_AET.PROCESS_INSTANCE. This setting retrieves the current process instance value that appears on the state record and submits it to your COBOL module using PTPECOBL.
RETCODE and ERRMSG	(Optional) Include RETCODE if you need to return information about any potential problems that the COBOL processing encountered, or use it if your Application Engine program must know whether it completed successfully.
Fieldnames and Values	Specify any fields in the state record that contain initial values for your COBOL module. The quoted field names you specify must exist in the specified state record. The corresponding value can be a PeopleCode variable, a record.field reference, or a hard-coded value.

Commit and RemoteCall

When using RemoteCall and an Application Engine program:

The called COBOL module runs as a separate unit of work.
Run a commit in the step immediately preceding the step containing the RemoteCall PeopleCode action and also in the step containing the Remote Call PeopleCode action.

These two actions enable the COBOL process to recognize the data changes made up to the point that it was called, and minimizes the time when the process might be in a non-restartable state.
If you insert SQL processing into your COBOL module, your module makes commit updates.

PTPECOBL does not issue any commits.
If the intent of your COBOL process is to update the value of a passed state record field, then the calling Application Engine PeopleCode is responsible for ensuring that the state record field is modified, and the Application Engine program is responsible for committing the state record updates.
Consider how your COBOL module will react in the event of a restart.

Because the work in COBOL will have already completed and been committed, will your module ignore a duplicate call or be able to undo or redo the work multiple times? You face similar issues when you run a remote call from PeopleCode.
Typically, when a COBOL program updates the database and then disconnects or terminates without having issued an explicit commit or rollback, an implicit rollback occurs.

Without an explicit commit, the database does not retain any updates.

Note. By default, RemoteCall does not generate any log files after the program completes. To generate and retain the .out and .err log files, you must set the RCCBL Redirect parameter in the PeopleSoft Process Scheduler configuration file to a value of 1.

See SetNextPanel.

Calling PeopleTools APIs

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

All the PeopleTools APIs contain a Save method.

However, when you call an API from your Application Engine program, regardless of the Save method of the API, the data is not saved until the Application Engine program issues a commit.
If you called a component interface from an Application Engine program, all the errors related to the API are logged in the PSMessage collection associated with the current session object.
If you sent a message, errors are written to the message log and the Application Engine message log.
If an Application Engine program called from a message subscription PeopleCode encounters errors and the program exits (with Exit (1)), the error is written to the message log and is marked as an error

Using the CommitWork Function

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

This function applies only to a batch Application Engine program.

If the program is invoked by CallAppEngine, the CommitWork function is ignored. The same is true for commit settings at the section or step levels.
This function can be used only in an Application Engine program that has restart disabled.
The CommitWork function is useful only when you are processing SQL one row at a time in a single PeopleCode program, and you need to commit without exiting the program.

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

See Also

CommitWork

Calling WINWORD Mail Merge

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

Configure the Process Scheduler to run Application Engine programs using psae instead of psaesrv.
Ensure the generated document is saved locally, not on a shared network drive.

Using PeopleCode Examples

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

Do When Actions

Instead of a Do When action that checks a %BIND value, you can use PeopleCode to perform the equivalent operation. For example, suppose the following SQL exists in your program:

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

Using PeopleCode, you could insert this code:

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

If you set the On Return parameter on the PeopleCode action properties to Skip Step, this code behaves the same as the Do When action. The advantage of using PeopleCode is that no trip to the database occurs.

Dynamic SQL

If you have a Select statement that populates a text field with dynamic SQL, such as the following:

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

You can use this PeopleCode:

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

Sequence Numbering

If you typically use Select statements to increment a sequence number inside of a Do Select, While, or Until loop, you can use the following PeopleCode instead:

SEQ_NBR = SEQ_NBR + 1;

Using PeopleCode rather than SQL can affect performance significantly. Because the sequencing task occurs repeatedly inside a loop, the cost of using a SQL statement to increment the counter increases with the volume of transactions your program processes. When you are modifying a program to take advantage of PeopleCode, the areas of logic you should consider are those that start with steps that run inside a loop.

Note. You can also use the meta-SQL constructs %Next and %Previous when performing sequence numbering. These constructs may improve performance in both PeopleCode and SQL calls.

Rowsets

You can use rowsets in Application Engine PeopleCode; however, using rowsets means you will be using PeopleCode to handle more complicated processing, which degrades performance.

Math Functions

Use the math functions that your database offers whenever possible.

Internally, PeopleCode assigns types to numeric values. Calculations for the Decimal type are processed in arrays to ensure decimal point uniformity across hardware and operating system environments. This processing is much slower than calculations for type Integer, which are processed at the hardware level.

When PeopleCode converts strings to numeric values, it does so using the internal Decimal type. For performance reasons, avoid calculations using these values.

A third type of numeric value is the Float type. It is not used as frequently for the following reasons:

Constants are never stored as Float types in the compiled code.

For example, 2.5 is always Decimal type.
The only way to produce a Float value is by using built-in functions, such as Float or the Financial math functions.

Use the Float type to produce a float result only if all operands are also of the Float type. Float operations occur at the hardware level.

PeopleCode does not offer optimum performance when processing non-Integer, non-Float math calculations. To perform calculations with these numeric types, consider allowing the database to perform the calculations in COBOL.

PeopleCode supports a range of mathematical functions and numeric types. In general, if a complex calculation is run repetitively in an Application Engine program, you should carefully analyze whether to perform the calculation in a PeopleCode action or use the relational database management system (RDBMS) functions through a SQL action. Using SQL may require PeopleSoft meta-SQL to handle platform differences, but it may be the most efficient way to update field values. If SQL is not appropriate, consider numeric typing in PeopleCode, as it affects the speed and accuracy of the calculation.

SQL Class

Instead of using the SQL class within PeopleCode, have Application Engine issue the SQL and use a Do Select action that loops around sections containing PeopleCode actions.

Coding all of the logic within a single PeopleCode program might appear to be easier, but splitting the logic into smaller pieces is preferable because you will have better performance and will get more detailed commit control. Within a PeopleCode program, you can commit in certain cases using the CommitWork function. You can always issue a commit between Application Engine steps.

See CommitWork.

See Understanding SQL Objects and Application Engine Programs.

Arrays

Instead of using arrays in Application Engine PeopleCode, explore the use of temporary tables for storing pertinent or affected data. Arrays offer the following advantages:

Data is available for restarts.
An RDBMS is efficient at managing and searching tables.
Temporary tables lend themselves to set-based processing.

You can use the Statement Timings and PeopleCode Detail Timings trace options to generate an Application Engine timings report to determine whether your program is spending significant time processing arrays.

Including Dynamic SQL

Typically, developers include dynamic constructs in Application Engine programs to change SQL based on various runtime factors or on user-defined entries on a page. You can include dynamic SQL in Application Engine programs in a variety of ways. For example, you can:

Include dynamic sections using the AESection object.
Change SQL using the SQL class.
Reference SQL in your own tables.

The AESection class is designed primarily for online section building; therefore, it will not be the most frequently used solution.

Use the SQL class to store SQL in a SQL definition that you can also access in Application Designer. Then, if you have a few SQL statements to run, generate the SQL IDs based on some methodology, such as a timestamp, and then store these in a table.

When the program runs, your SQL could query this table based on process and extract the appropriate SQL IDs to be run with a SQL action in a Do Select loop.

%SQL(%BIND(MY_SQLID, NOQUOTES))

For a dynamic Do action, the AE_APPLID and the AE_SECTION fields must appear in the default state record.

Using Application Engine Meta-SQL

This section describes the meta-SQL constructs, functions, and meta-variables you can use in Application Engine.

Note. The SQL Editor does not validate all of the meta-SQL constructs, such as %Bind and %Select. Messages might appear stating that these constructs are invalid.

%Abs

Description

Because the %Abs function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52 PeopleBook: PeopleCode Language Reference.

See Also

%Abs

%AeProgram

Description

Use the %AeProgram meta-variable to specify a quoted string containing the currently running Application Engine program name.

%AeSection

Description

Use the %AeSection meta-variable to specify a quoted string containing the currently running Application Engine section name.

%AeStep

Description

Syntax

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

Description

Use the %Bind construct to retrieve a field value from a state record. You can use %Bind anywhere in a SQL statement. When run, %Bind returns the value of the state record field identified within its parentheses.

Notes About %Bind

Typically, when you use %Bind to provide a value for a field or a Where condition, the type of field in the state record that you reference with %Bind must match the field type of the corresponding database field used in the SQL statement.

On most platforms, you cannot use a literal to populate a Long Varchar field. You should use the %Bind(recordname.fieldname) construct.

In the case of an external call to a section in another program, if the called program has its own default state record defined, then Application Engine uses that default state record to resolve the %Bind(fieldname). Otherwise, the called program inherits the default state record of the calling program.

All fields referenced by a %Select construct must be defined in the associated state record.

You must use the Date, Time, and DateTime output wrappers in the Select list that populates the state record fields to ensure compatibility across all supported database platforms.

For example:

First SQL Action

%Select(date_end)
   SELECT %DateOut(date_end ) 
      FROM PS_EXAMPLE

Second SQL Action

INSERT INTO PS_EXAMPLE
   VALUES(%Bind(date_end))

Bind Variables and Date Wraps

The behavior of bind variables within Application Engine PeopleCode and normal PeopleCode is the same.

If you compare Application Engine SQL to PeopleCode (of any type), then the system processes bind variables differently.

If you use the following approach:

AND TL_EMPL_DATA1.EFFDT <= %P(1))

Then in PeopleCode you issue

%SQL(MY_SQL, %DateIn(:1))

which assumes that you referenced the literal as a bind variable.

Or in Application Engine SQL, you issue

%SQL(MY_SQL, %Bind(date_field))
%SQL(MY_SQL, %Bind(date_field, NOWRAP))

Parameters

Recordname	The name of a state record. If you do not specify a particular state record, Application Engine uses the default state record to resolve the %Bind (fieldname).
Fieldname	The field defined in the state record.
NOQUOTES	If the field specified is a character field, its value is automatically enclosed in quotes unless you use the NOQUOTES parameter. Use NOQUOTES to include a dynamic table and field name reference, even an entire SQL statement or clause, in an Application Engine SQL action.
NOWRAP	If the field is of type Date, Time, or DateTime, the system automatically wraps its value in %DateIn or %DateOut, unless you use the NOWRAP parameter. Therefore, if the state record field is populated correctly, you do not need to be concerned with the inbound references, although you can suppress the inbound wrapping with the NOWRAP modifier inside the %Bind. Furthermore, Application Engine skips the inbound wrapper if the %Bind (date) is in the select field list of another %Select statement. This is because the bind value is already in the outbound format, and the system selects it into another state record field in memory. In this circumstance there is no need for either an outbound wrapper or an inbound wrapper. For example, First SQL action: %Select(date_end) SELECT %DateOut(date_end ) FROM PS_GREG Second SQL action: INSERT INTO ps_greg VALUES(%Bind(date_end))
STATIC	The STATIC parameter enables you to include a hard-coded value in a reused statement. For %Bind instances that contain dynamic SQL, this parameter must be used in conjunction with the NOQUOTES parameter for proper execution of a reused statement.

Example

UPDATE PS_REQ_HDR
   SET IN_PROCESS_FLG = %Bind(MY_AET.IN_PROCESS_FLG),
      PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
   WHERE IN_PROCESS_FLG = ‘N’
      AND BUSINESS_UNIT || REQ_ID
      IN (SELECT BUSINESS_UNIT ||REQ_ID
         FROM PS_PO_REQRCON_WK1
         WHERE PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE))

In the previous example, %Bind (PROCESS_INSTANCE) assigns the value of the field PROCESS_INSTANCE in the default state record to the PROCESS_INSTANCE field in table PS_REQ_HDR.

The %Bind construct is also used in a Where clause to identify rows in the table PS_PO_REQRCON_WK1, in which the value of PROCESS_INSTANCE equals the value of PROCESS_INSTANCE in the default state record.

%Cast

Description

Because the %Cast function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52 PeopleBook: PeopleCode Language Reference.

See Also

%Cast

%ClearCursor

Syntax

%ClearCursor({program, section, step, action | ALL})

Description

Use the %ClearCursor function to recompile a reused statement and reset any STATIC %Bind variables.

When you use the %ClearCursor function, remember that:

The function must be located at the beginning of the statement.
%ClearCursor can be the only function or command contained in the statement.

Parameters

program	Specify the name of the Application Engine program containing the reused statement you want to recompile.
section	Specify the name of the section containing the reused statement you want to recompile.
step	Specify the name of the step containing the reused statement you want to recompile.
action	Specify one of the following values: D: Do Select. H: Do When. N: Do Until. W: Do While. S: SQL.
ALL	Clear all cursors in the current Application Engine program.

By default, Application Engine expects a semicolon 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.

Note. When you use the %Execute function, it must be located at the beginning of the statement and must be the only function or command contained in the statement. The action type must be SQL.

Example

The following code enables you to use an Oracle PL/SQL block in an %Execute statement:

%Execute(/)
DECLARE
 counter INTEGER;
BEGIN
 FOR counter := 1 TO 10
  UPDATE pslock SET version = version + 1;
 END FOR;
END;
/

%ExecuteEdits

Syntax

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

Description

Use the %ExecuteEdits function to apply data dictionary edits in batch. The %ExecuteEdits function is Application Engine-only meta-SQL. You cannot use it in COBOL, SQR, or PeopleCode, not even in Application Engine PeopleCode.

Notes About %ExecuteEdits

Note the following points about the %ExecuteEdits function:

Consider performance carefully when using this function.

Prompt table and Translate table edits have a significant effect because they involve correlated subqueries. Run a SQL trace at runtime so that you can view the SQL generated by %ExecuteEdits. Look for opportunities to optimize it.
In general, %ExecuteEdits is best used on a temporary table.

If you must run it against a real application table, you should provide Where clause conditions to limit the number of rows to include only those that the program is currently processing. Process the rows in the current set at one time rather than row by row.
With %ExecuteEdits, you cannot use work records in a batch, set-based operation.

All higher-order key fields used by prompt table edits must exist in the record that your code intends to edit, and the field names must match exactly. For example,
```
%ExecuteEdits(%Edit_PromptTable, MY_DATA_TMP)  
```
The record MY_DATA_TMP contains the field STATE with a prompt table edit against PS_REGION_VW, which has key fields COUNTRY and REGION. The REGION field corresponds to STATE, and COUNTRY is the higher-order key. For %ExecuteEdits to work correctly, the MY_DATA_TMP record must contain a field called COUNTRY. The edited field (STATE) can use a different name because Application Engine always references the last key field (ignoring EFFDT).
In Application Engine, %ExecuteEdits uses the system date when performing comparisons with effective date (EFFDT); however, in some cases this date is not appropriate (Journal Edit, Journal Import, and so on). In these situations, use Journal Date when comparing with EFFDT. To override the use of the default system date with a selected field from a joined table, use %AsOfDateOvr. For example,
```
%ExecuteEdits(%AsOfDateOvr(alias.fieldname), %Bind(...)...)
```
Restrict the number and type of edits to the minimum required.

Do not edit fields that are known to be valid or that are given default values later in the process. Also consider using a separate record with edits defined specifically for batch or provide a list of fields to be edited.

Parameters

type

Specify any combination of the following (added together):

%Edit_Required
%Edit_YesNo
%Edit_DateRange
%Edit_PromptTable
%Edit_TranslateTable

recordname

Specify the record used to obtain the data dictionary edits.

field1, field2, ...

Specify a subset of the fields of the record to which edits apply.

Example

Suppose you want to insert rows with missing or invalid values in three specific fields, selecting data from a temporary table but using edits defined on the original application table. Notice the use of an alias, or correlation name, inside the meta-SQL:

INSERT INTO PS_JRNL_LINE_ERROR (...)
SELECT ... FROM PS_JRNL_LINE_TMP A
WHERE A.PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE)
  AND %EXECUTEEDITS(%Edit_Required + %Edit_PromptTable,?
 JRNL_LINE A, BUSINESS_UNIT, JOURNAL_ID, ACCOUNTING_DT)

To update rows that have some kind of edit error in a temporary table, you can use custom edits defined in the temporary table record:

UPDATE PS_PENDITEM_TAO
SELECT ERROR_FLAG = 'Y'
WHERE PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE)
  AND %EXECUTEEDITS(%Edit_Required + %Edit_YesNo + %Edit_DateRange +?
 %Edit_PromptTable + %Edit_TranslateTable, PENDITEM_TAO)

%FirstRows

Description

Because the %FirstRows meta-variable can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52 PeopleBook: PeopleCode Language Reference.

See Also

%FirstRows

%GetProgText

Syntax

%GetProgText(&Prog, &Section, &Market, &Platform, &Effdt, &Step, &Event)

Description

The %GetProgText function returns a string with the text of a PeopleCode program uniquely identified by the parameters.

Parameters

&Prog	A string with the name of an Application Engine program.
&Section	A string with the name of an Application Engine program section.
&Market	A string specifying the market for an Application Engine program section.
&Platform	A string specifying the platform for an Application Engine program section.
&Effdt	A string specifying the effective date for an Application Engine program section.
&Step	A string specifying a step in an Application Engine program section.
&Event	A string specifying the PeopleCode event.

Returns

A string containing the text of a PeopleCode program.

Example

&PeopleCodeText = GetProgText("DYNROLE_PUBL", "MAIN", "GBL", "default", 
"1900-01-01", "Step03", "OnExecute");

See Also

Using the SQL Editor.

%InsertSelect

Description

Because the %InsertSelect construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52 PeopleBook: PeopleCode Language Reference.

See Also

%InsertSelect

%InsertSelectWithLongs

Description

Because the%InsertSelectWithLongs construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52 PeopleBook: PeopleCode Language Reference.

See Also

%InsertSelectWithLongs

%JobInstance

Description

Use the %JobInstance meta-variable to specify the numeric (unquoted) PeopleSoft Process Scheduler job instance.

%Join

Description

Because the %Join construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52 PeopleBook: PeopleCode Language Reference.

See Also

%Join

%LeftParen

Description

Use the %LeftParen meta-variable to specify a left parenthesis. Usage is similar to %Comma.

See Also

%Comma

%SQL

%Like

Description

Because the %Like construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52 PeopleBook: PeopleCode Language Reference.

See Also

%Like

%LikeExact

Description

Because the %LikeExact construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52 PeopleBook: PeopleCode Language Reference.

See Also

%LikeExact

%List

Syntax

%List({FIELD_LIST | FIELD_LIST_NOLONGS | KEY_FIELDS | ORDER_BY}, recordname [ correlation_id])

Description

The %List construct expands into a list of field names delimited by commas. The fields included in the expanded list depend on the parameters.

Note. This meta-SQL is not implemented for COBOL, dynamic view SQL, or PeopleCode.

Considerations for Using %List

When using %List in an Insert/Select or Insert/Values or %Select statement, you must have matching pairs of %List (or %ListBind) variables in the target and source field lists. Use the same list type argument and record name to ensure consistency.

Parameters

FIELD_LIST	Use all field names in the given record. You can select only one option from FIELD_LIST, ORDER_BY, FIELD_LIST_NOLONGS, or KEY_FIELDS.
KEY_FIELDS	Use all key fields in the given record. You can select only one option from FIELD_LIST, FIELD_LIST_NOLONGS, KEY_FIELDS, or ORDER_BY.
ORDER_BY	Use all the key fields of recordname, adding the DESC field for descending key columns. This parameter is often used when the list being generated is for an Order By clause. You can select only one option from FIELD_LIST, KEY_FIELDS, ORDER_BY, or FIELD_LIST_NOLONGS.
FIELD_LIST_NOLONGS	Use all field names in the given record, except any long columns (long text or image fields.) You can select only one option from FIELD_LIST, ORDER_BY, KEY_FIELDS, or FIELD_LIST_NOLONGS.
recordname	Identify either a record or a subrecord that the field names are drawn from. This can be a bind variable, a record object, or a record name in the form recname. You cannot specify RECORD.recname, a record name in quotation marks, or a table name.
correlation_id	Identify the single-letter correlation ID to relate the record specified by recordname and its fields.

Example

The following is a good example of using %List. Both the Insert and Select statements use the same %List variable:

INSERT INTO PS_PO_DISTRIB_STG (  %Sql(POCOMMONDISTSTGFLDLSTU) 
,  %List(FIELD_LIST, CF16_AN_SBR) 
,  MERCHANDISE_AMT 
,  MERCH_AMT_BSE 
,  QTY_DEMAND 
,  QTY_PO 
,  QTY_PO_STD 
,  QTY_REQ)   
SELECT  %Sql(POCOMMONDISTSTGFLDLSTU) 
,  %List(FIELD_LIST, CF16_AN_SBR) 
,  MERCHANDISE_AMT 
,  MERCH_AMT_BSE 
,  QTY_DEMAND 
,  QTY_PO 
,  QTY_PO_STD 
,  QTY_REQ    
FROM PS_PO_DIST_STG_WRK WRK   
WHERE WRK.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)

The following example shows a poor example of how to use %List. The Insert and Select field lists both use %List, but the Select field list is only partly dynamic; the rest is hard-coded.

INSERT INTO PS_EN_TRN_CMP_TMP (%List(FIELD_LIST, EN_TRN_CMP_TMP))  
SELECT B.EIP_CTL_ID 
, %List(SELECT_LIST, EN_BOM_COMPS A) 
, E.COPY_DIRECTION 
, E.BUSINESS_UNIT_TO 
, E.BOM_TRANSFER_STAT 
, 'N' 
, B.MASS_MAINT_CODE 
, 0 
 FROM PS_EN_BOM_COMPS A 
 , PS_EN_ASSY_TRN_TMP B 
 , PS_EN_TRNS_TMP E 
WHERE ...

The following example shows the previous poor example rewritten in a better way:

INSERT INTO PS_EN_TRN_CMP_TMP (EIP_CTL_ID, 
,  %List(FIELD_LIST, EN_BOM_COMPS) 
,  COPY_DIRECTION 
,  BUSINESS_UNIT_TO 
,  BOM_TRANSFER_STAT 
,  EN_MMC_UPDATE_FLG 
,  MASS_MAINT_CODE 
,  EN_MMC_SEQ_FLG01 
,  ... 
, EN_MMC_SEQ_FLG20)  
SELECT B.EIP_CTL_ID 
  , %List(FIELD_LIST, EN_BOM_COMPS A) 
, E.COPY_DIRECTION 
, E.BUSINESS_UNIT_TO 
, E.BOM_TRANSFER_STAT 
, 'N' 
, B.MASS_MAINT_CODE 
, 0 
, ... 
, 0 
 FROM PS_EN_BOM_COMPS A 
 , PS_EN_ASSY_TRN_TMP B 
 , PS_EN_TRNS_TMP E 
WHERE ...

The following code segment is another poor example. Only the field list of the Insert statement is dynamically generated, and the Select statement is statically coded. If the table STL_NET_TBL is reordered, the Insert statement will be incorrect.

INSERT INTO PS_STL_NET_TBL (%List(FIELD_LIST, STL_NET_TBL ) )  
SELECT :1  
, :2  
, :3  
, :4  
, :5  
, :6  
, :7  
,:8  
FROM PS_INSTALLATION

The following code shows the previous poor example rewritten in a better way:

INSERT INTO PS_STL_NET_TBL (%List(FIELD_LIST, STL_NET_TBL)) 
                
VALUES (%List(BIND_LIST, STL_NET_TBL MY_AET))

%ListBind

Syntax

%ListBind({FIELD_LIST | FIELD_LIST_NOLONGS | KEY_FIELDS}, recordname [ State_record_alias])

Description

The %ListBind meta-SQL construct expands a field list as bind references for use in an Insert/Value statement.

Note. This meta-SQL is not implemented for COBOL, dynamic view SQL, or PeopleCode.

Considerations for Using %ListBind

When using %ListBind in an insert/select or insert/values or %Select statement, you must have matching pairs of %List or %ListBind in the target and source field lists, using the same list type argument and record name to ensure consistency.

Parameters

FIELD_LIST	Use all field names in a record. You can select only one option from FIELD_LIST, FIELD_LIST_NOLONGS, or KEY_FIELDS.
FIELD_LIST_NOLONGS	Use all field names in a record, except any long columns (long text or image fields). You can select only one option from FIELD_LIST, FIELD_LIST_NOLONGS, or KEY_FIELDS.
KEY_FIELDS	Use all key field names in a record. You can select only one option from FIELD_LIST, FIELD_LIST_NOLONGS, or KEY_FIELDS.
recordname	Identify either a record or a subrecord that the field names are drawn from. This can be a bind variable, a record object, or a record name in the form recname. You cannot specify RECORD.recname, a record name in quotation marks, or a table name.
State_record_alias	Specify the Application Engine state record buffer that contains the values (this could be different than the record used to derive the field list). If missing, the default state record is assumed.

Example

INSERT INTO PS_TARGET (FIELD1, FIELD2, %List(FIELD_LIST, CF_SUBREC), FIELDN)⇒
 VALUES (%Bind(MY_AET.FIELD1), %Bind(MY_AET.FIELD2), %ListBind(FIELD_LIST, CF_⇒
SUBREC MY_AET), %Bind(MY_AET.FIELDN))

%ListEqual

Syntax

%ListEqual({ALL | KEY }, Recordname [alias], RecordBuffer [, Separator])

Description

The %ListEqual construct maps each field, possibly to an alias with a %Bind value, with a separator added before each equality. Each field is mapped as follows:

alias.X = %Bind(recbuffer.X)

This construct can be used in the Set clause of an Update statement or in a Where clause.

Note. This meta-SQL is not implemented for COBOL, dynamic view SQL, or PeopleCode.

Parameters

ALL \| KEY	Specify if you want all fields or just key fields.
recordname	Identify either a record or a subrecord that the field names are drawn from. This can be a bind variable, a record object, or a record name in the form recname. You cannot specify RECORD.recname, a record name in quotation marks, or a table name.
alias	(Optional) Specify an alias to precede each field name.
RecordBuffer	Specify the record buffer for the bind variables (this could be different than the record used to derive the field list).
Separator	If you want to specify a logical separator, specify either AND or OR with this parameter. If you do not specify a separator, no logical separator is used; the value of a comma is used instead.

Example

UPDATE PS_TEMP

SET %ListEqual(ALL, CF_SUBREC, MY_AET) 
WHERE %ListEqual(KEYS, TEMP, MY_AET, AND)

%Mod

Description

Because the %Mod function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52 PeopleBook: PeopleCode Language Reference.

See Also

%Mod

%Next and %Previous

Description

Use the %Next and %Previous functions to return the value of the next or previous field in a numbered sequence. These functions are valid in any Application Engine SQL action and should be used when performing sequence-numbering processing. Typically, you use them instead of a %Bind construct. These functions use the current value of the number field as a bind variable and then increment (%Next) or decrement (%Previous) the value after the statement runs successfully. A number field indicates the numeric field of the state record that you initially set to a particular value (as in 1 to start).

If the statement is a Select and no rows are returned, the field value is not changed. The substitution rules are the same as for %Bind. For example, if the ReUse property is enabled, then the field is a true bind (':n' substituted). Otherwise, inline substitution occurs.

Example

You could use these functions in an Update statement within a Do Select action:

Do Select action

%SELECT(field1, field2, ...) SELECT key1, key2, ... FROM PS_TABLE WHERE ... 
ORDER BY key1, key2, ..."

SQL

UPDATE PS_TABLE SET SEQ_NBR = %Next(seq_field) WHERE key1 = %Bind(field1) 
AND key2 = %Bind(field2) ...

With a Do Select action, the increment/decrement occurs once per run, not once for every fetch. So unless your Do Select action implements the Reselect property, the value is changed only on the first iteration of the loop. Alternatively, with the Reselect property or Do While and Do Until actions, every iteration reruns the Select statement and then fetches one row. With these types of loops, the value changes on every iteration.

See Also

%Bind

%NoUpperCase

Description

Because the %NoUpperCase construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52 PeopleBook: PeopleCode Language Reference.

See Also

%NoUppercase

%NumToChar

Description

Because the %NumToChar construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52 PeopleBook: PeopleCode Language Reference.

See Also

%NumToChar

%ProcessInstance

Description

Use the %ProcessInstance meta-variable to specify the numeric (unquoted) process instance.

%ResolveMetaSQL

Syntax

%ResolveMetaSQL(&SQL,%DbType)

Description

The %ResolveMetaSQL function returns a string with any meta-SQL in the string expanded to platform-specific SQL, similar to the text that is returned on the Meta-SQL tab when using the Resolve Meta-SQL option in the SQL Editor.

If &SQL does not contain any meta-SQL, then the function returns a string identical to &SQL.

%DBType value represents the type of current database.

Parameters

&SQL	Specify a string containing the SQL to be resolved.
%DBType	%DBType value is DB2

For more information on valid values to %DBType:

See FetchSQL.

Returns

A string with meta-SQL expanded to platform-specific SQL.

Example

Here is an example:

&SQLText = FetchSQL(SQL.PTLT_CODE_MARKET);
&ResolveSQLText = ResolveMetaSQL(&SQLText,DB2);

Suppose &SQLText contains the following SQL:

INSERT INTO %Table(PTLT_ASSGN_TASK)(PTLT_FEATURE_CODE 
 , PTLT_TASK_CODE 
 , PORTAL_NAME 
 , PTLT_TASK_CODE2 
 , MENUNAME 
 , OBJECTOWNERID) 
 SELECT A.EOLT_FEATURE_CODE 
 , %Sql(PTLT_TASK_CODE, A.PNLGRPNAME, A.MARKET) 
 , 'EMPLOYEE' 
 , %Sql(PTLT_TASK_CODE, A.PNLGRPNAME, A.MARKET) 
 , A.MENUNAME 
 , ' ' 
  FROM %Table(EOLT_FEAT_COMP) A 
  , %Table(PTLT_TASK) B 
  , %Table(PTLT_TASK_LOAD) C 
 WHERE %Sql(PTLT_TASK_CODE, A.PNLGRPNAME, A.MARKET) = B.PTLT_TASK_CODE 
   AND B.PTLT_TASK_CODE = C.PTLT_TASK_CODE 
   AND B.PTLT_LOAD_METHOD = C.PTLT_LOAD_METHOD 
   AND A.MENUNAME <> ' ' 
   AND A.MENUNAME <> C.MENUNAME 
   AND NOT EXISTS ( 
 SELECT 'X' 
  FROM %Table(PTLT_ASSGN_TASK) Z 
 WHERE Z.PTLT_FEATURE_CODE = A.EOLT_FEATURE_CODE 
   AND Z.PTLT_TASK_CODE = %Sql(PTLT_TASK_CODE, A.PNLGRPNAME, A.MARKET))

&ResolveSQLText would contain the following text (depending on your database platorm):

INSERT INTO PS_PTLT_ASSGN_TASK(PTLT_FEATURE_CODE  
 , PTLT_TASK_CODE  
 , PORTAL_NAME  
 , PTLT_TASK_CODE2  
 , MENUNAME  
 , OBJECTOWNERID)  
 SELECT A.EOLT_FEATURE_CODE  
 , RTRIM(SUBSTR( A.PNLGRPNAME 
 ,1 
 ,18)) || '.' ||  A.MARKET  
 , 'EMPLOYEE'  
 , RTRIM(SUBSTR( A.PNLGRPNAME 
 ,1 
 ,18)) || '.' ||  A.MARKET  
 , A.MENUNAME  
 , ' '  
  FROM PS_EOLT_FEAT_COMP A  
  , PS_PTLT_TASK B  
  , PS_PTLT_TASK_LOAD C  
 WHERE RTRIM(SUBSTR( A.PNLGRPNAME,1,18)) || '.' ||  A.MARKET = B.PTLT_TASK_CODE  
   AND B.PTLT_TASK_CODE = C.PTLT_TASK_CODE  
   AND B.PTLT_LOAD_METHOD = C.PTLT_LOAD_METHOD  
   AND A.MENUNAME <> ' '  
   AND A.MENUNAME <> C.MENUNAME  
   AND NOT EXISTS (  
 SELECT 'X'  
  FROM PS_PTLT_ASSGN_TASK Z  
 WHERE Z.PTLT_FEATURE_CODE = A.EOLT_FEATURE_CODE  
   AND Z.PTLT_TASK_CODE = RTRIM(SUBSTR( A.PNLGRPNAME,1,18)) || '.' ||  A.MARKET)

See Also

Using the SQL Editor.

%ReturnCode

Description

Use the %ReturnCode meta-variable to evaluate or specify the return code of the last Application Engine program step performed. If the operation fails, breaks, or generates an error, %ReturnCode is set to one of the following types of return codes:

Database (SQL) call errors.
PeopleCode function errors.
GEN_ERROR, when produced by general runtime exceptions.
AE_ABORT, when produced by application or runtime logic, including some memory-related errors.

If the application process is not terminated, %ReturnCode is reset to the default value of 0 for each subsequent successful operation.

%RightParen

Description

Use the %RightParen meta-variable to specify a right parenthesis. Usage is similar to that of %Comma.

See Also

%Comma

%SQL

%Round

Description

Because the %Round function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52 PeopleBook: PeopleCode Language Reference.

See Also

%Round

%RoundCurrency

Syntax

%RoundCurrency (expression, [ALIAS.]currency_field)

Description

Use the %RoundCurrency function to return the value of an amount field rounded to the currency precision specified by the Currency Control Field property of the field, as defined in the Application Designer Record Field Properties dialog box. For this function to work, you must have the Multi-Currency option selected on the PeopleTools Options page.

See Using Administration Utilities.

This function is an enhanced version of the Application Engine &ROUND construct that appeared in previous releases, and it is valid only in Application Engine SQL; it is not valid for SQLExecs or view text.

You can use this function in the Set clause of an Update statement or the Select list of an Insert/Select statement. The first parameter is an arbitrary expression of numeric values and columns from the source tables that computes the monetary amount to be rounded. The second parameter is the control currency field from a particular source table (the Update table, or a table in the From clause of an Insert/Selectstatement). This field identifies the corresponding currency value for the monetary amount.

Note. Remember that the as of date of the Application Engine program is used for obtaining the currency rounding factor. The currency rounding factor is determined by the value of DECIMAL_POSITIONS in the corresponding row in PS_CURRENCY_CD_TBL, which is an effective-dated table.

If multicurrency is not in effect, the result is rounded to the precision of the amount field (either 13.2 or 15.3 amount formats are possible).

Example

UPDATE PS_PENDING_DST
   SET MONETARY_AMOUNT = 
   %RoundCurrency( FOREIGN_AMOUNT * CUR_EXCHNG_RT, CURRENCY_CD)
   WHERE GROUP_BU = %Bind(GROUP_BU) AND GROUP_ID = %Bind(GROUP_ID)

%RunControl

Description

Use the %RunControl meta-variable to specify a quoted string containing the current run control identifier. The run control ID is available to your program when using %RunControl, regardless of whether the AEREQUEST table contains a row.

%Select

Syntax

%Select(statefield1[, statefield2]...[, statefieldN])

Select field1[, field2]...[, fieldN]

The statefields must be valid fields on the state record (they may be fieldname or recordname.fieldname, as with %Bind), and fields must be either valid fields in the From tables or hard-coded values.

Description

Use the %Select construct to identify the state record fields to hold the values returned by the corresponding Select statement. The %Select construct is required at the beginning of all Select statements. For example, you need one in the flow control actions and one in the SQL actions that contain a Select statement.

You use the %Select construct to pass variables to the state record, and you use the %Bind construct to retrieve or reference the variables.

Example

Consider the following sample statement:

%SELECT(BUSINESS_UNIT,CUST_ID)
SELECT BUSINESS_UNIT, CUST_ID
FROM PS_CUST_DATA
   WHERE PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE)

The following steps illustrate the execution of the previous statement:

Resolve bind variables.

The string %Bind(PROCESS_INSTANCE) is replaced with the value of the state record field called PROCESS_INSTANCE.
Execute the SQL Select statement.
Perform a SQL Fetch statement.

If a row is returned, the state record fields BUSINESS_UNIT and CUST_ID are updated with the results. If the Fetch statement does not return any rows, all fields in the %Select construct retain their previous values.

Note. All fields referenced by a %Select construct must be defined in the associated state record. Also, aggregate functions always return a row, so they always cause the state record to be updated. As such, for aggregate functions, no difference exists between using %SelectInit or %Select.

%SelectInit

Syntax

%SelectInit(statefield1[, statefield2]...[, statefieldN])

Select field1[, field2]...[, fieldN]

Description

Use the %SelectInit construct to identify the state record fields to hold the values returned by the corresponding Select statement.

The %SelectInit construct is identical to the %Select construct with the following exception: if the Select statement returns no rows, then %SelectInit re-initializes the buffers. In the case of a %Select construct where no rows are returned, the state record fields retain their previous values.

Note. For aggregate functions, no difference exists between using %SelectInit or %Select.

%Space

Description

Use the %Space meta-variable to specify a single space. Usage is similar to %Comma.

See Also

%Comma

%SQL

Description

Use the %SQL construct to specify a SQL object, which replaces the %SQL construct in a statement. This construct enables commonly used SQL text to be shared among Application Engine and PeopleCode programs. In Application Engine, you use %Bind to specify bind variables. In PeopleCode SQL, you can use

:record.field

If you create SQL objects that you plan to share between Application Engine and PeopleCode programs, the %SQL construct enables you to pass parameters for resolving bind variables without being concerned with the difference in the bind syntax that exists between Application Engine and PeopleCode. However, you must tailor the base SQL statement that uses %SQL to represent a shared object with binds to Application Engine or to PeopleCode.

When a SQL object specified has more than one version, the database type always takes precedence. That is:

If one or more versions of a SQL definition are found for the database type of the current database connection and if any of the versions have an effective date less than or equal to the current date, then the most recent version is used.
If no versions are found for the current database type or if all of the versions have effective dates greater than the current date, then the system looks for an effective version of the SQL definition under the database type Generic. If no version is found, an error occurs.

Example

For example, assume that your SQL is similar to the following:

UPDATE PS_TEMP_TBL SET ACTIVE = %BIND(MY_AET.ACTIVE)
WHERE PROCESS_INSTANCE = %ProcessInstance

This code would not be valid if the SQL ran in PeopleCode. However, if you define your SQL as shown, you could use parameters in %SQL to insert the appropriate bind variable:

UPDATE PS_TEMP_TBL SET ACTIVE = %P(1)
WHERE PROCESS_INSTANCE = %ProcessInstance

From Application Engine, the base SQL, or source statement, might look like this:

%SQL(SQL_ID, %BIND(MY_AET.ACTIVE))

The PeopleCode SQL might look like this:

%SQL(SQL_ID, :MY_AET.ACTIVE)

Note. You can use %SQL only to reference SQL objects created directly in Application Designer. For instance, you cannot use %SQL to reference SQL that resides within a section in an application library. Common SQL should be stored as a proper SQL object.

See Also

%SQL

%SQLRows

Description

Use the %SQLRows meta-variable to specify whether a SQL action returned any rows.

You can use %SQLRows in any Application Engine SQL statement, but the underlying value is affected only by SQL actions. It is not affected by Do When, Do Select, Do While, and Do Until actions. 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. To find the number of rows that meet the Where criteria, code a Select Count (*) statement.

%Substring

Description

Because the %Substring function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52 PeopleBook: PeopleCode Language Reference.

See Also

%Substring

%Table

Syntax

%Table(recname)

Description

Use the %Table construct to return the SQL table name for the record specified with recname.

This construct can be used to specify temporary tables for running parallel Application Engine processes across different subsets of data.

Example

For example, the following statement returns the record PS_ABSENCE_HIST:

%Table(ABSENCE_HIST)

%TruncateTable(table name)

Description

Use the %TruncateTable construct to invoke a bulk delete command on a table. This construct is functionally identical to a Delete SQL statement with no Where clause, but it is faster on databases that support bulk deletes. If you are familiar with COBOL, this construct is an enhanced version of the COBOL meta-SQL construct with the same name.

Some database vendors have implemented bulk delete commands that decrease the time required to delete all the rows in a table by not logging rollback data in the transaction log. For the databases that support these commands, Application Engine replaces %TruncateTable with Truncate Table SQL. For the other database types, %TruncateTable is replaced with Delete From SQL.

You should commit after the step that immediately precedes the step containing the %TruncateTable statement. In general, you should use this construct early in your Application Engine program as an initialization task. In addition, avoid using this meta-SQL when your Application Engine program is started from the PeopleCode CallAppEngine function.

Unlike the COBOL version, Application Engine determines if a commit is possible before making the substitution. If a commit is possible, Application Engine makes the substitution and then forces a checkpoint and commit after the delete runs successfully.

If a commit is not possible, Application Engine replaces the meta-SQL with a Delete From string. This string ensures restart integrity when your program runs against a database for which an implicit commit is associated with Truncate Table or for which rollback data is not logged.

For databases that either run an implicit commit for %TruncateTable or require a commit before or after this meta-SQL, replace %TruncateTable with an unconditional delete in the following circumstances:

A commit is not allowed, as in an Application Engine program called from PeopleCode.
The program issues a non-select SQL statement since the last commit occurred. In such a situation, data is likely to have changed.
You are deferring commits in a Select/Fetch loop within a restartable program.

Note. To use a record name as the argument for %TruncateTable (instead of an explicit table name), you must include a %Table meta-SQL function to resolve the unspecified table name. For example, to specify the record PO_WEEK as the argument, use the following statement: %TruncateTable(%Table(PO_WEEK)).

See Also

%Table

%TruncateTable

%UpdateStats

Syntax

%UpdateStats(record name ,[HIGH/LOW])

For example,

%UpdateStats(PO_WRK1)

The default is LOW.

Description

Use the %UpdateStats construct to generate a platform-dependent SQL statement that updates the system catalog tables used by the database optimizer in choosing optimal query plans. 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 construct saves you from having to use dummy seed data for the temporary table and having to update statistics manually.

Notes About %UpdateStats

For databases that either run an implicit commit for %UpdateStats or require a commit before or after this meta-SQL, Application Engine skips %UpdateStats in the following circumstances:

A commit is not allowed, as in an Application Engine program called from PeopleCode.
The program issues a non-select SQL statement since the last commit occurred.

In such a situation, data is likely to have changed.
You are deferring commits in a Select/Fetch loop in a restartable program.

Application Engine skips %UpdateStats even if the previous condition is false.

The following table shows how the %UpdateStats construct is resolved by the supported database systems:

Database Function	Behavior
MSS %UpdateStats	Specifying LOW produces the statement UPDATE STATISTICS tablename Specifying HIGH produces the statement UPDATE STATISTICS tablename WITH FULLSCAN
Sybase %UpdateStats	LOW and HIGH = UPDATE ALL STATISTICS tablename
Oracle %UpdateStats	Oracle uses DDL templates (in PSDDLMODEL) to determine SQL statements for %UpdateStats. Use DDLORA.DMS to change. Specifying LOW produces the statement execute DBMS_STATS.GATHER_TABLE_STATS (ownname=>⇒ 'PT8468908', tabname=>'PSSTATUS', estimate_percent=⇒ >20, method_opt=> 'FOR ALL INDEXED COLUMNS SIZE⇒ 1',cascade=>TRUE) Specifying HIGH produces the statement execute DBMS_STATS.GATHER_TABLE_STATS (ownname=>⇒ 'PT848908', tabname=>'PSSTATUS', estimate_percent=>⇒ dbms_stats.auto_sample_size, method_opt=> 'FOR ALL⇒ INDEXED COLUMNS SIZE 1',cascade=>TRUE)
DB2 UNIX %UpdateStats	In DB2 UNIX, %UpdateStats is performed by issuing sqlustat() calls that are equivalent to SQL statements. The sqlustat() is an internal DB2 API call function rather than an SQL command. Specifying LOW is equivalent to issuing the statement RUNSTATS ON TABLE tablename AND INDEXES ALL Specifying HIGH is equivalent to issuing the statement RUNSTATS ON TABLE tablename WITH DISTRIBUTION AND⇒ DETAILED INDEXES ALL Note. You cannot view the sqlustat() calls nor the RUNSTATS statement in the SQL trace.
DB2 390 %UpdateStats	Uses a DDL model template (in PSDDLMODEL) to format a control statement for the DB2 UDB for OS390 and z/OS Runstats utility. Refer to the PeopleTools Installation Guide and the Administration Guide for more details on using %UpdateStats with DB2 UDB for OS390 and z/OS. Specifying LOW produces the statement RUNSTATS TABLESPACE [DBNAME].[TBSPCNAME] TABLE([⇒ DBNAME].[TABLE]) SAMPLE 25 [INDEXLIST] REPORT NO⇒ SHRLEVEL CHANGE UPDATE ACCESSPATH Specifying HIGH produces the statement RUNSTATS TABLESPACE [DBNAME].[TBSPCNAME] TABLE([⇒ DBNAME].[TABLE]) [INDEXLIST] REPORT NO SHRLEVEL⇒ CHANGE UPDATE ACCESSPATH
Informix %UpdateStats	Specifying LOW produces the statement UPDATE STATISTICS MEDIUM FOR TABLE tablename Specifying HIGH produces the statement UPDATE STATISTICS HIGH FOR TABLE tablename

%UpdateStats Database Considerations

The following table lists potential issues that you might encounter when using %UpdateStats:

Database	Consideration
Microsoft SQL Server Sybase UDB	Application Engine forces a commit before and after the %UpdateStats statement. Therefore, the system skips this meta-SQL if a commit is not allowed. For instance, a commit is not allowed in the following situations: The Application Engine program is not running in batch mode. You have issued non-Select/Fetch SQL (in which the data is likely to change) since the last commit. You are deferring commits in a Select/Fetch loop within a restartable program.
Oracle	Oracle has an implicit commit after the %UpdateStats statement executes. Same behavior as previous consideration.
DB2 UDB for OS/390 and z/OS	For DB2 UDB for OS/390 and z/OS, %UpdateStats requires IBM stored procedure DSNUTILS running in an authorized Work Load Manager Application Environment. It is also highly recommeded that individual tables intended to be a target of the %UpdateStats function are segregated to their own tablespaces. Refer to the following documents for more details on using %UpdateStats: PeopleTools Installation Guide for DB2 UDB for OS/390 and z/OS; PeopleTools Administration Guide for DB2 UDB for OS/390 and z/OS. Note. You can trace information messages from the Runstats command on DB2 for z/os executed as a result of issuing %UpdateStats. To enable this trace, select the SQL Informational Trace check box on the Configuration Manager – Trace page.
Informix IBM UDB	%UpdateStats locks the table being analyzed on UDB and Informix. Therefore, use this meta-SQL only on tables that are not likely to be concurrently accessed by other applications and users. You might use %UpdateStats to analyze Application Engine dedicated temporary tables.
All	%UpdateStats consumes a large amount of time and database resources if run against very large tables. Therefore, analyze permanent data tables outside of application programs. Also, if temporary tables are likely to grow very large during a batch run, run the batch program only with %UpdateStats enabled to seed the statistics data or when the data composition changes dramatically.

Disabling %UpdateStats

You can disable %UpdateStats in the following ways:

Include the following parameter on the command line when running an Application Engine program:
```
-DBFLAGS  1 
```
Change the Dbflags=0 parameter in the PeopleSoft Process Scheduler configuration file (or PSADMIN) to Dbflags=1.

Using %UpdateStats With COBOL

You can use the %UpdateStats construct from SQL embedded in COBOL programs. Use this syntax:

%UpdateStats(tablename)

When you issue this construct from PeopleTools, the parameter is record name.

%Upper

Description

Because the %Upper construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52 PeopleBook: PeopleCode Language Reference.

See Also

%Upper