Reviewing Temporary Tables

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.