Using Meta-SQL and PeopleCode

This chapter provides an overview of PeopleSoft 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 PeopleSoft Application Engine Meta-SQL

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

• Construct.

  A construct is a direct substitution of a value, which 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 can't be used in Application Engine programs at all. Only meta-SQL elements that can be used in PeopleSoft Application Engine are discussed in this PeopleBook. You can find a complete reference to all PeopleSoft meta-SQL elements in the PeopleTools 8.49 PeopleCode Reference PeopleBook.

See Also

Application Engine Meta-SQL Reference

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 APIs.

• Use the CommitWork function.

• Call WINWORD Mail Merge

• Use PeopleCode examples.

Understanding PeopleCode and Application Engine Programs

Inserting PeopleCode within 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 don't require a trip to the database.

Scope of Variables

The following table presents the different types of variables typically used in Application Engine programs and their scope.

Action Execution Order

No other types of actions are required within a step in conjunction with a PeopleCode action (or program). So, you can have a step that contains nothing but one PeopleCode action. If you include other actions with your PeopleCode action within the same step, it’s important to keep in mind the execution hierarchy.

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

Because there are multiple action types, they must execute in agreement within a system, and therefore the order in which action’s 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 and PeopleCode action exist within a given step, the Do When is always executed first.

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

Action execution hierarchy

Deciding When to Use PeopleCode

PeopleSoft Application Engine is not intended to execute programs that include nothing but PeopleCode actions. PeopleSoft Application Engine's primary purpose is to run SQL against your data.

For the most part, use PeopleCode for setting If, Then, Else logic constructs, performing data preparation tasks, and building dynamic portions of SQL statements, while still relying on SQL to complete the bulk of the actual program processing. You also use PeopleCode to reuse online logic that’s already developed. And, of course, PeopleCode is the tool for taking advantage of the new technologies, such as component interfaces and application classes.

Most programs need to check that a certain condition is true prior to executing 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 need to 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, approximated by the following formula:

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 500000 rows with one record per row, 50 fields, and 200 bytes per field would require approximately 2.3 gig 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, and, 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 on 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:

1. Declare a record object in PeopleCode.

   Here is an example:

   Local Record &MyRecord; 

2. Assign the record objects to any state record that you want to pass to the Application Engine program.

   Record objects are parameters to the CallAppEngine function.

3. Set the appropriate values on that state record.

4. Include the record object in the function call.

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

Defining Global Variables

You can also define global variables or objects in PeopleCode before calling an Application Engine program. Application Engine PeopleCode actions only are able to access 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 the corresponding value. If that value is changed within PeopleSoft Application Engine by updating the state record field, the component data is not be affected. The only way to update component buffers or external PeopleCode variables from PeopleSoft Application Engine is to use global PeopleCode variables and objects.

See Also

CallAppEngine

Accessing State Records with PeopleCode

Executing 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 the following:

%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 using a Do When action to check the value of a state record field.

When accessing state records with PeopleCode, keep the following in mind:

• State records are unique to Application Engine programs.

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

Note. When you launch an Application Engine program from PeopleSoft Process Scheduler, you can generate a process warning status on completion of the program 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 on the PeopleCode action properties to specify how your Application Engine program behaves according to the return of your PeopleCode program. The following illustration shows the On Return property:

By default, the program terminates, similar to what happens when a SQL error occurs. But by changing the On Return value to Skip Step, 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 the On Return action. The concepts of “return 1” and “return True” are equivalent. So, if the return value is non-zero or True, then PeopleSoft Application Engine performs what you specify for On Return, as in Abort or Skip Step. However, if the program returns zero, or False, PeopleSoft Application Engine ignores the selected On Return value.

Using PeopleCode in Loops

You can insert PeopleCode inside of a Do loop, but take care when using PeopleCode inside of high-volume Do loops (While, Select, Until). Keep the number of distinct programs inside the loop to a minimum. You should avoid having PeopleCode performing 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 gets called in a loop. If the PeopleCode loops, there is a good probability that PeopleSoft Application Engine will use a dedicated cursor, which saves the overhead of recompiling the SQL for all iterations. If the PeopleCode gets called from within a loop, PeopleSoft Application Engine does not reduce the number of compiles, but Application Engine 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 this.

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

On those database platforms for which PeopleSoft has implemented this feature, Setting BulkMode to True often results in significant performance gains when inserting rows into a table in a loop.

In general, avoid PeopleCode calls within a loop. If you can call the PeopleCode outside of the loop, use that approach. This can 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 PeopleSoft Application Engine tables directly. This 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 prior to execution.

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

When using an AESection object, keep the following in mind:

• Check to make sure that you primarily require dynamic capabilities with the SQL your program generates.

• Make sure that the rules to which your program conform are relatively static, or at least defined well enough such 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 only SQL-based actions, not PeopleCode, Call Section, or other actions.

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

• The AESection class is designed for 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, the user can’t perform another PeopleSoft task until the Application Engine program completes. Consider the size and performance of the Application Engine program called by CallAppEngine. You need to be sure 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, and if the program was unsuccessful, it returns a non-zero.

See Also

CallAppEngine

Using the File Class

The file layout class enables you to perform file input and /output operations with PeopleSoft Application Engine using PeopleCode. With a file object, you can 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 PeopleSoft 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 translate it, so that the file can update the affected PeopleSoft application tables. For the PeopleSoft system and the legacy system to interoperate, you need to first construct a file object that is compatible for both systems 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 PeopleSoft Application Designer, and it acts as a template for the file that both systems read from and write to. This simplifies reading, writing, and manipulating complex transaction data with PeopleCode.

Generally, use the file class and Application Engine combination when you can’t 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 can also 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 don’t have to write your own executable to process this task. However, PTPECOBL does not perform any SQL processing other than retrieve a list of state record values. Consequently, if your current logic requires prior 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:

1. Initializes the specified state record in memory.

2. Invokes the COBOL module specified in your PeopleCode.

3. Submits required parameters to the called COBOL module.

4. 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 PeopleSoft Application Engine and COBOL

Note the following options for sharing values between the Application Engine program and your called COBOL program:

• Use state records.

  If you add field names, PeopleSoft 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 manner, use PTPECACH to retrieve and update values just as PTPEFCNV does.

• Code custom SQL.

  If you do not pass the 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, it might be a better approach to create a custom executable file. This way, you can 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

The following 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);

Commit and RemoteCall

Note the following using RemoteCall and an Application Engine program:

• The called COBOL module executes as a separate unit of work.

• Execute 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.

  This enables the COBOL process to recognize the data changes made up to the point that it was called, and it also minimizes the time when the process might be in a non-restartable state.

• If you insert SQL processing into your COBOL module, commit updates are made by your module.

  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 has been 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 been completed and committed, will your module ignore a duplicate call or be able to undo or redo the work multiple times? This is similar to issues faced when you execute a remote call from PeopleCode.

• Typically, when a COBOL program updates the database and then disconnects or terminates without having issuing an explicit commit or rollback, an implicit rollback occurs.

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

Note. By default, RemoteCall doesn't 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 Also

Editing the PeopleSoft Process Scheduler Configuration File

Calling PeopleTools APIs

You can call all of the PeopleTools APIs from an Application Engine program. Keep the following items in mind when using APIs:

• All the PeopleTools APIs contain a Save method.

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

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

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

• If an Application Engine program called from 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. Keep the following in mind when using CommitWork:

• 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 level.

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

• The CommitWork function is useful only when you are doing row-at-a-time SQL processing 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, one of the following must be done to ensure successful completion:

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

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

Using PeopleCode Examples

The following sections provide some examples of common ways that you can utilize 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 the following 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 there is no trip to the database.

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 is significant. 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 are executed inside a loop.

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

Rowsets

You can use rowsets in Application Engine PeopleCode. However, using rowsets means you’ll 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 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.

• The only way to produce a Float value is by using built-in functions, such as Float or the Financial math functions.

The Float type is used 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. Generally speaking, if a complex calculation is executed repetitively in an Application Engine program, careful analyis should be done to determine whether to perform the calculation in a PeopleCode action or to use the relational database management (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 this 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.

It might appear easier to code all of the logic within a single PeopleCode program, but splitting the logic into smaller pieces is preferable because you will have better performance, and you get a finer granularity of 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 PeopleSoft Application Engine Programs.

Arrays

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

• Data is available for restarts.

• An RDBMS is efficient at managing and searching tables.

• Using temporary tables also lends itself 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 input entered through a page. There are a variety of ways to include dynamic SQL in Application Engine programs. For example, you could use:

• Dynamic sections, using the AESection object.

• Changing SQL, using the SQL class.

• References to SQL in your own tables.

The AESection class is primarily designed for online section building, and therefore won’t be the most frequently used solution.

Use the SQL class to store SQL in a SQL definition that you can also access in PeopleSoft Application Designer. Then, if you have a few SQL statements to execute, 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 executed 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 on the default state record.

Application Engine Meta-SQL Reference

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

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

%Abs

Description

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

See Also

%Abs

%AeProgram

Description

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

%AeSection

Description

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

%AeStep

Description

Use the %AeStep meta-variable to specify a quoted string containing the currently executing Application Engine Step name.

%AsOfDate

Description

Use the %AsOfDate meta-variable to specify a quoted string containing the as of date used for the current process.

%AsOfDateOvr

Description

Use the %AsOfDateOvr meta-variable only as a parameter of the %ExecuteEdits function, to override the default use of the system date with the value of a field on a joined record.

See Also

%Table.

%BINARYSORT

Description

Because the %BINARYSORT construct can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%BINARYSORT

%Bind

Syntax

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

Description

Use the %Bind construct to retrieve a field value from a state record. Can be used anywhere in a SQL statement. When executed, %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 can’t 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 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.

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.

This ensures 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 PeopleSoft Application Engine PeopleCode and normal PeopleCode is the same.

Alternately, if you compare PeopleSoft 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))

This assumes that you have referenced the literal as a bind variable.

Or in PeopleSoft Application Engine SQL you issue:

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

Parameters

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

INSERT INTO ps_greg
VALUES(%Bind(date_end))

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.

%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, keep the following in mind:

• The function must be located at the beginning of the statement.

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

Parameters

%COALESCE

Description

Because the %COALESCE function can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%COALESCE

%Comma

Description

Use the %Comma meta-variable to specify a comma. This is useful where you must 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.

See Also

%SQL

%Concat

Description

Because the %Concat meta-variable can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%Concat

%CurrentDateIn

Description

Because the %CurrentDateIn meta-variable can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%CurrentDateIn

%CurrentDateOut

Description

Because the %CurrentDateOut meta-variable can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%CurrentDateOut

%CurrentDateTimeIn

Description

Because the %CurrentDateTimeIn meta-variable can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%CurrentDateTimeIn

%CurrentDateTimeOut

Description

Because the %CurrentDateTimeOut meta-variable can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%CurrentDateTimeOut

%CurrentTimeIn

Description

Because the %CurrentTimeIn meta-variable can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%CurrentTimeIn

%CurrentTimeOut

Description

Because the %CurrentTimeOut meta-variable can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%CurrentTimeOut

%DateAdd

Description

Because the %DateAdd function can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%DateAdd

%DateDiff

Description

Because the %DateDiff function can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%DateDiff

%DateIn

Description

Because the %DateIn construct can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%DateIn

%DateNull

Description

Because the %DateNull meta-variable can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%DateNull

%DateOut

Description

Because the %DateOut function can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%DateOut

%DatePart

Description

Because the %DatePart function can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%DatePart

%DateTimeDiff

Description

Because the %DateTimeDiff function can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%DateTimeDiff

%DateTimeIn

Description

Because the %DateTimeIn construct can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%DateTimeIn

%DateTimeNull

Description

Because the %DateTimeNull meta-variable can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%DateTimeNull

%DateTimeOut

Description

Because the %DateTimeOut function can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%DateTimeOut

%DecDiv

Description

Because the %DecDiv function can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%DecDiv

%DecMult

Description

Because the %DecMult function can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%DecMult

%DTTM

Description

Because the %DTTM function can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%DTTM

%EffDtCheck

Description

Because the %EffDtCheck construct can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%EffDtCheck

%Execute

Syntax

%Execute([/])
command1{; | /}
command2{; | /}...
commandN{; | /}

Description

Use the %Execute function to execute database-specific commands from within your Application Engine program. Also, the %Execute function enables you to include multiple statements in a single Application Engine action without encountering database-specific differences. For instance, there are instances where you could code a single Application Engine action to contain multiple SQL statements, and they may run successfully on one database platform. However, if you attempt to run the same code against a different database platform, you might encounter errors or skipped SQL.

By default, PeopleSoft Application Engine expects a semicolon to be used to delimit multiple commands within an %Execute function statement. You can instruct PeopleSoft 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 can 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 can't use it in COBOL, SQR, or PeopleCode—not even in Application Engine PeopleCode.

Notes About %ExecuteEdits

Note the following:

• Consider performance carefully when using this function.

  Prompt table and Translate table edits have a significant impact, because they involve correlated subqueries. Run a SQL trace at execution time so that you can view the SQL generated by %ExecuteEdits. Look for opportunities where it can be optimized.

• In general, %ExecuteEdits is best used on a temporary table.

  If you must run this 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 all at once rather than processing them row by row.

• With %ExecuteEdits, you can't use work records in a batch, set-based operation.

  All higher-order key fields used by prompt table edits must exist on 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. It's permissible for the edited field (STATE) to use a different name, because PeopleSoft 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 is not appropriate (Journal Edit, Journal Import, and so on). In these situations, Journal Date should be used when comparing with EFFDT. To override a program's 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.

  Don't perform edits on 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

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 in a temporary table that have some kind of edit error, you can use custom edits defined on 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's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%FirstRows

%InsertSelect

Description

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

See Also

%InsertSelect

%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's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

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's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%Like

%LikeExact

Description

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

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 depends on the parameters.

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

Considerations 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, using the same list type argument and record name to ensure consistency.

Parameters

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

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

Example

UPDATE PS_TEMP

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

%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 in place 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 is executed successfully. A number field indicates the numeric field on the state record that you have 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 execution, 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 re-executes 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's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%NoUppercase

%NumToChar

Description

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

See Also

%NumToChar

%ProcessInstance

Description

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

%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 %Comma.

See Also

%Comma

%SQL

%Round

Description

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

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 field’s Currency Control Field property, as defined in the PeopleSoft 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 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 on 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 there's a row in the AEREQUEST table.

%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:

1. Resolve bind variables.

   The string %Bind(PROCESS_INSTANCE) is replaced with the value of the state record field called PROCESS_INSTANCE.

2. Execute the SQL Select statement.

3. Perform a SQL Fetch statement.

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

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, there is no difference whether you use %SelectInit or %Select.

%SelectInit

Syntax

%SelectInit(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 %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, %SelectInit reinitializes 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, there is no difference whether you use %SelectInit or %Select.

%Space

Description

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

See Also

%Comma

%SQL

%SQL

Description

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

:record.field 

or

:1

If you create SQL objects that you plan to share between PeopleSoft 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 PeopleSoft Application Engine and PeopleCode. However, the base SQL statement that uses %SQL to represent a shared object with binds needs to be tailored to PeopleSoft 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, 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, 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

That 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 PeopleSoft Application Engine, the base SQL, or source statement, might look like the following:

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

The PeopleCode SQL may appear as the following:

%SQL(SQL_ID, :MY_AET.ACTIVE)

Note. You can use %SQL only to reference SQL objects created directly in PeopleSoft Application Designer. For instance, you can not 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.

Can be used in any PeopleSoft 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's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

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)

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 PS_ABSENCE_HISTnn, where nn is the instance number.

See Also

%Table

%Test

Description

Because the %Test construct can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%Test

%TextIn

Description

Because the %TextIn construct can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%TextIn

%TimeAdd

Description

Because the %TimeAdd construct can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%TimeAdd

%TimeIn

Description

Because the %TimeIn construct can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%TimeIn

%TimeNull

Description

Because the %TimeNull meta-variable can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%TimeNull

%TimeOut

Description

Because the %TimeOut construct can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%TimeOut

%TimePart

Description

Because the %TimePart function can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%TimePart

%TrimSubstr

Description

Because the %TrimSubstr function can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%TrimSubstr

%Truncate

Description

Because the %Truncate function can be used in more than just Application Engine programs, it's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%Truncate

%TruncateTable

Syntax

%TruncateTable(table name)

Description

Use the %TruncateTable construct to invoke a bulk delete command on a table. It's functionally identical to a Delete SQL statement with no Where clause, but it is faster 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.

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, PeopleSoft 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, it's best to 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, PeopleSoft Application Engine determines if a commit is possible prior to making the substitution. If a commit is possible, PeopleSoft Application Engine makes the substitution and then forces a checkpoint and commit after the successful execution of the delete.

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

For databases that either execute 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 within 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 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 execute an implicit commit for %UpdateStats or require a commit before or after this meta-SQL, PeopleSoft Application Engine skips %UpdateStats in the following circumstances:

• A commit is not allowed, as in within 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.

  PeopleSoft 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.

UPDATE STATISTICS tablename

UPDATE STATISTICS tablename WITH FULLSCAN

execute DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'PT8468908', tabname=>'PSSTATUS', estimate_percent=>20, method_opt=> 'FOR
ALL INDEXED COLUMNS SIZE 1',cascade=>TRUE)

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)

RUNSTATS ON TABLE tablename AND INDEXES ALL  

RUNSTATS ON TABLE tablename WITH DISTRIBUTION AND DETAILED INDEXES ALL 

RUNSTATS TABLESPACE [DBNAME].[TBSPCNAME] TABLE([DBNAME].[TABLE]) SAMPLE 25 [INDEXLIST] REPORT NO SHRLEVEL CHANGE UPDATE ACCESSPATH

RUNSTATS TABLESPACE [DBNAME].[TBSPCNAME] TABLE([DBNAME].[TABLE]) [INDEXLIST] REPORT NO SHRLEVEL CHANGE UPDATE ACCESSPATH

UPDATE STATISTICS MEDIUM FOR TABLE tablename

UPDATE STATISTICS HIGH FOR TABLE tablename

%UpdateStats Database Considerations

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

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's documented in the PeopleTools 8.49 PeopleCode Language Reference PeopleBook.

See Also

%Upper