Reviewing Payment Predictor Sections and SQL Statements

The Payment Predictor multiprocess job (ARPREDCT) runs two Application Engine processes—AR_PREDICT1 and AR_PREDICT2. Each Application Engine process runs a collection of SQL statements. The AR_PREDICT2 process contains algorithm groups that represent sections. Each algorithm in the section represents a step that is either an SQL statement or the DO command of another section. If you want to perform a DO command on a section more than once, you use a DO SELECT. The processes perform the section that you specify once for each row that the select returns. It places values returned by the select in %BIND variables and references them by name in subsequent SQL statements.

The AR_PREDICT1 process prepares the staging tables and populates the temporary tables for the second multiprocess job. The staging tables are PS_PP_ITEM_TMP, PS_PP_CUST_TMP, and PS_PP_PYMNT_TMP, and they are images of the tables PS_PP_ITEM_TAO, PS_PP_CUST_TAO, and PS_PP_PYMNT_TAO. The staging tables populate the temporary tables used by the child processes. The AR_PREDICT2 process uses the information in the temporary tables to generate the groups and worksheets.

The AR_PREDICT2 process flow has four stages:

Executing method steps.
Generating transactions.
Updating tables and status.
Releasing process instances on tables.

This section provides background information about the sections in the AR_PREDICT1 and AR_PREDICT2 Application Engine processes.

AR_PREDICT1 - Preparing Temporary Tables

This table describes the sections that prepare temporary tables:

Section Description

Section	Description
PREPARE	Builds the temporary tables PS_PP_PYMNT_TAO and PS_PP_ITEM_TAO. For payments containing no references or completely invalid references (PS_PP_ PYMNT_TAO.PP_REF_SW = N), it loads all items for the customers identified through PS_PAYMENT_ID_CUST into PS_PP_ITEM_TAO.
ID_ITEM	Populates the temporary table PS_PP_CUST_TAO with customer identification information for payments with references (PS_ PYMNT_TAO.PP_REF_ STATUS = Y). Platform-specific codes can apply in this section. First, Payment Predictor determines a list of the types of references that it uses for all payments in the run. Then for each type of reference it uses, it builds a dynamic SQL statement to insert a row into PS_PP_CUST_TAO for each payment. For each type of reference, it runs two SQL statements based on the algorithms CUSTMP1 and CUSTMP2. For example, if ITEM references were included in any payments in the run, the module would concatenate "AND X.REF_ QUALIFIER_CODE = 'I' AND X.REF_VALUE = I.ITEM)" to the end of the statements and run the statements. Loaded from the PREPARE section as a DO SELECT, ID_ITEM. `%SelectInit(REF_QUALIFIER_CODE, FIELDNAME)SELECT DISTINCT R.REF_⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ QUALIFIER_CODE , R.FIELDNAMEFROM %Table(PP_PYMNT_TAO) P , PS_PAYMENT_ID_ITEM X , PS_AR_FLD_REF_TBL R WHERE P.PROCESS_INSTANCE = %Bind(PROCESS_⇒ INSTANCE) AND P.PP_REF_STATUS = 'Y' AND P.DEPOSIT_BU = X.DEPOSIT_BUAND P.DEPOSIT_ID = X.DEPOSIT_IDAND P.PAYMENT_SEQ_NUM = X.PAYMENT_⇒ SEQ_NUMAND X.REF_QUALIFIER_CODE = R.REF_QUALIFIER_CODE AND R.REF_STATUS =⇒ 'A'` Payment Predictor accomplishes the same results without a special module for the dynamic SQL. All the SQL is dynamic, and Payment Predictor uses the Application Engine bind variables that are returned from the DO SELECT. `AND X.REF_QUALIFIER_CODE = %BIND(REF_QUALIFIER_CODE)AND X.REF_⇒ VALUE = I.%BIND (FIELDNAME, NOQUOTES)`
ID_CUST	Populates the PS_PP_CUST_TAO temporary table with customer identification information for payments with MICR or customer references. This is also done from PREPARE. Platform-specific codes can apply in this section.

PREPARE

Builds the temporary tables PS_PP_PYMNT_TAO and PS_PP_ITEM_TAO. For payments containing no references or completely invalid references (PS_PP_ PYMNT_TAO.PP_REF_SW = N), it loads all items for the customers identified through PS_PAYMENT_ID_CUST into PS_PP_ITEM_TAO.

ID_ITEM

Populates the temporary table PS_PP_CUST_TAO with customer identification information for payments with references (PS_ PYMNT_TAO.PP_REF_ STATUS = Y). Platform-specific codes can apply in this section.

First, Payment Predictor determines a list of the types of references that it uses for all payments in the run. Then for each type of reference it uses, it builds a dynamic SQL statement to insert a row into PS_PP_CUST_TAO for each payment. For each type of reference, it runs two SQL statements based on the algorithms CUSTMP1 and CUSTMP2.

For example, if ITEM references were included in any payments in the run, the module would concatenate "AND X.REF_ QUALIFIER_CODE = 'I' AND X.REF_VALUE = I.ITEM)" to the end of the statements and run the statements.

Loaded from the PREPARE section as a DO SELECT, ID_ITEM.

%SelectInit(REF_QUALIFIER_CODE, FIELDNAME)SELECT DISTINCT R.REF_⇒
⇒
⇒
⇒
⇒
⇒
⇒
⇒
⇒
⇒
⇒
⇒
QUALIFIER_CODE ,
R.FIELDNAMEFROM %Table(PP_PYMNT_TAO) P , PS_PAYMENT_ID_ITEM X , 
PS_AR_FLD_REF_TBL R WHERE P.PROCESS_INSTANCE = %Bind(PROCESS_⇒
INSTANCE)
AND P.PP_REF_STATUS = 'Y' AND P.DEPOSIT_BU = X.DEPOSIT_BUAND 
P.DEPOSIT_ID = X.DEPOSIT_IDAND P.PAYMENT_SEQ_NUM = X.PAYMENT_⇒
SEQ_NUMAND 
X.REF_QUALIFIER_CODE = R.REF_QUALIFIER_CODE AND R.REF_STATUS =⇒
 'A'

Payment Predictor accomplishes the same results without a special module for the dynamic SQL. All the SQL is dynamic, and Payment Predictor uses the Application Engine bind variables that are returned from the DO SELECT.

AND X.REF_QUALIFIER_CODE = %BIND(REF_QUALIFIER_CODE)AND X.REF_⇒
VALUE = I.%BIND
(FIELDNAME, NOQUOTES)

ID_CUST

Populates the PS_PP_CUST_TAO temporary table with customer identification information for payments with MICR or customer references. This is also done from PREPARE. Platform-specific codes can apply in this section.

AR_PREDICT2 - Executing Method Steps

This table describes the sections that run method steps:

Section	Description
SBLD	This section builds the PS_PP_STEP_TAO temporary table and then performs each step in turn. First, it inserts one row for each step, condition, and action of each method needed to process all payments for all business units requested. For example, if the step has a condition that runs an algorithm and then generates an adjustment for an underpayment, it inserts two rows into PS_PP_STEP_TAO. It deletes each row upon completion; this table should be empty at the end of a normal run. After the PS_PP_STEP_TAO table is created, the second phase begins.
STEP	The Step Manager, done from SBLD with a DO SELECT. Depending on the step and condition that the system is processing, the appropriate section is done.
GENITEM	Run for each Generate An Item step. It might generate items based on the criteria specified in the method and on the condition of the payment.
WBLD	Run for each Generate A Worksheet step. It might generate worksheets based on the criteria specified in the method and on the condition of the payment.
CNTL_ACT	Run for a control account step.
RELEASE	Run for each release. The payment step uses only the temporary tables. It releases a payment that an algorithm has attempted to apply, enabling subsequent method steps that match the remittance profile to process the payment. When an algorithm finds any items to apply for a payment, another algorithm (or method step) does not consider that payment for application unless a Release The Payment step releases it.
ALGR	Not a true section. A dynamic DO performs the actual run of the SQL contained in the algorithm group, and the section that is run is the name of the algorithm group. The name of an algorithm group must begin with a # and must match the name of a section. An algorithm is a group of statements in a section. When an algorithm populates PS_PP_MATCH_TAO, it must set the value of PP_PROC_FLAG to 0. Subsequent statements included in the algorithm group enable you to further adjust or refine the answer set contained in PS_PP_MATCH_TAO to: Limit the answer set to just the customers included in the identifying information. Add a unique single debit or credit.
ALGRDONE	Performs system processing after an algorithm populates PS_PP_MATCH_TAO. This includes handling the bulk of the Payment Predictor hold logic, performing the Check Pending option, and eliminating duplicate items from being selected. After it is called, all items selected have PS_PP_MATCH_TAO. PP_PROC_FLAG = 1. This switch changes to 2 after the group of steps is completed—after an Execute Algorithm and then overpayment and underpayment clauses. ALGRDONE appears in sections at the points where the answer set can be modified. PeopleSoft Receivables implements this in the algorithm groups of #BALANCE, #COMBOS, #PASTDUE, and #STATEMNT.

AR_PREDICT2 - Generating Transactions

This table describes the sections that generate transactions:

Section	Description
UPDM	Updates the matches. This is required to assign sequence numbers needed for building the PS_PENDING_ITEM and PS_PAYMENT_ITEM tables. This is a SELECT, FETCH, and UPDATE operation. UPDM performs other functions, including building deposits and payments generated during the run, making an additional pass for the check pending option, and handling the orphan payments that have not been processed. In addition, it excludes items that have been selected but that have a different currency from the payment.
PGEN	Generates payment worksheets, group control, and pending items, as needed. These are all set mode operations—a series of SQL statements run, each creating worksheets for a subset of payments.
PUPD	Performs a SELECT, FETCH, and UPDATE of each payment being processed in this run and updates the PS_PAYMENT table accordingly. PUPD exists as an individual section, because it can be replaced with a stored procedure on some SQL platforms.
TERMINATE	Calculates and logs messages for the payment totals and payment amounts either applied, generated to worksheets, or released. Releases the process instances on tables used.