Reviewing Payment Predictor Temporary Tables and Sections

This topic discusses how to:

  • Review temporary tables.

  • Review Payment Predictor sections and SQL statements.

As an Application Engine-based program, Payment Predictor makes extensive use of set processing and temporary tables. This design improves performance and enhances your ability to modify processing for your environment.

Set processing is a method of manipulating data that operates on more than one row at a time. It is not a SELECT, FETCH, and UPDATE approach; rather, it inserts, updates, and deletes rows in sets. For example, Payment Predictor runs an algorithm group for all payments in all business units that use the current method and meet the remittance conditions of the current step.

The Payment Predictor uses cursor-based processing with reuse selected to avoid data contention and improve performance in support of the parallel processing.

Each of the temporary tables has PROCESS_INSTANCE as the high-order key to enable multiple Payment Predictor jobs to run on the server in parallel without interference.

The temporary tables are organized according to logical levels of data: payments, customers, items, steps, and matches.

Payment Predictor uses the following key temporary tables:

Table

Description

PS_PP_PYMNT_TAO

This table is the first table Payment Predictor populates. It contains one row for each payment processed.

The PREPARE section populates this table.

Payment Predictor processes only payments selected for Payment Predictor (PS_PAYMENT.PP_SW = Y) that are in balanced deposits (PS_DEPOSIT_ CONTROL.BAL_STATUS = I) for requested business units.

Keys: PROCESS_INSTANCE, DEPOSIT_BU, DEPOSIT_ID, PAYMENT_SEQ_NUM.

If any rows exist in PS_PAYMENT_ID_ITEM, the system sets PP_REF_STATUS to Y—references supplied. This setting is important because Payment Predictor processes payments with reference information differently than payments without reference information.

Note: Payment Predictor changes the initial value of Y to N—no references supplied—if it cannot identify any customers based on the reference information. That is, a value of N might be interpreted as no references supplied or that all references supplied are invalid.

Payment Predictor sets the value of PP_MICR_STATUS based on an analysis of the contents of PS_PP_CUST_TAO. The field name is somewhat misleading; the values placed in the field refer to the broader results of customer identification and not just the presence of a MICR ID. End result values are:

  • M (more than one customer identified)

  • S (only one customer identified)

  • N (no customer identified).

Payment Predictor stores the method and the SetID for the method that was used to process the payment on this table. The PP_APPL_STATUS and PP_DISPOSITION fields contain information about the status of a payment and whether it has been applied or whether it requires a worksheet.

PS_PP_CUST_TAO

This table contains one row for each customer identified by each payment.

Keys: PROCESS_INSTANCE, DEPOSIT_BU, DEPOSIT_ID, PAYMENT_SEQ_NUM, CUST_ID (duplicates allowed)

Customers are identified using one or more of the following conditions:

  • One or many rows in PAYMENT_ID_ITEM containing reference information.

  • One or many rows in PAYMENT_ID_CUST containing a MICR ID.

  • One or many rows in PAYMENT_ID_CUST containing a customer ID (with or without a business unit).

Note: When a payment has reference information, Payment Predictor does not use the customer identification information placed in the PS_PP_CUST_TAO table. Instead, it uses the reference qualifier and reference information to find the corresponding items on PS_ITEM. The PS_ITEM records contain the business unit and the customer identifier. Payment Predictor uses the business unit and customer identifier to determine the appropriate customers and related remit from customers.

PS_PP_ITEM_TAO

This table contains one row for each item considered for payment application.

If the payment does not have reference information (rows in PS_PAYMENT_ ID_ITEM), Payment Predictor loads all open items for all customers in PS_PP_CUST_TAO into this table. Later, non-reference-based algorithms that are associated with a method evaluate the data in PS_PP_ITEM_TAO.

Note: An algorithm does not have to use this table as the basis for its evaluation. For example, suppose that the reference-based algorithms obtain information directly from PS_ITEM, bypassing PS_PP_ITEM_TAO completely. Payment Predictor then runs a reference algorithm by inserting a row for each item selected by that algorithm directly into PS_PP_MATCH_ TAO. It then backloads the matches into PS_PP_ITEM_TAO to maintain consistency between these two tables.

PS_PP_STEP_TAO

This table contains one row for each step, condition, and action of each method that is processed in a run. The SBLD module populates this table and then performs each step in turn.

SBLD drives the payment application process. After the process loads all steps for all identified methods into PS_PP_STEP_TAO, this module does a SELECT, FETCH, and DELETE of each method step in order. The actions associated with a step are performed simultaneously against all payments that have not been processed (or have been released), that have been assigned to the current method by Payment Predictor, and that meet the remittance pattern specified for the step. When the Payment Predictor process finishes, this table should be empty (because all steps should have been processed).

PS_PP_MATCH_TAO

The process uses this table to evaluate overpayment and underpayment conditions. This table contains one row for each item to be paid by the payment or to be included on a payment worksheet. It is populated by an algorithm (or by WBLD when you use the Generate A Worksheet option). It might also contain one additional row for each payment that represents a new item generated by the Payment Predictor. It is actually the join between this table and PS_PP_ITEM_TAO that represents the transactions generated by a payment for a worksheet or pending group.

PS_PP_OCSEQ_TAO

The process uses this table to evaluate the sequence of overdue charge entry reasons in the ENTRY_REASN_TBL.

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

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.