PeopleSoft Enterprise Banks Setup and Processing 9.1 PeopleBook

Setting Up Reconciliation

This chapter provides an overview of bank account reconciliation and discusses how to:

Set up reconciliation tables for sources and rules.
Define reconciliation options for accounts.
Define statement activities.
Set up Cash Management bank statement accounting functionality.

Understanding Reconciliation

Bank account reconciliation involves comparing and matching your recorded business transactions with your bank's corresponding transactions.

PeopleSoft provides you with many different ways to reconcile balances, from a fully automated approach to one where you compare transactions manually. The method that you use depends on how you receive transaction information from your bank and on which PeopleSoft Enterprise applications you use.

This diagram illustrates reconciliation set up, beginning with the steps required to set up reconciliation configurations, then details the steps required to create reconciliation rules, and then finally outlines the steps required to define processing parameters by bank account:

Reconciliation setup process

If you use Cash Management, you can also establish bank statement accounting for your reconciliation processing. You establish rules from which the system uses to match exceptions, or NTFs (No Transactions Found). When the system encounters an exception that matches a rule, it automatically creates a system-side accounting entry for the exception, and marks the exception as reconciled. This process reduces the number of exceptions that you must resolve.

See Setting Up Cash Management Bank Statement Accounting Functionality.

Setting Up Reconciliation Tables for Sources and Rules

To define reconciliation information, use the following components:

Reconciliation Rules component (RCN_SOURCE_PNLGRP_GBL).
Reconciliation Records component (RCN_RECORD_PNLGRP_GBL).
Recon Field Aliases component (RCN_ALIAS_PNLGRP_GBL).
Reconciliation Status Codes component (RCN_STATUS_PNLGRP_GBL).
Recon Criteria Options component (RCN_USR_OPT_PNLGRP_GBL).

Use the TR_UNRECON_TRANS_GBL component interface to load data into the Reconciliation Exceptions tables.

PeopleSoft delivers predefined data in the reconciliation setup tables. You should review this information to determine whether you need to set up your own information to meet your organization's needs.

This section discusses how to:

Define reconciliation record source types.
Define reconciliation field aliases.
Define reconciliation status codes.
Specify reconciliation criteria options.
Define reconciliation rules.
Specify field updates.
Update reconciliation criteria.

Delivered Reconciliation Rules

PeopleSoft includes a library of reconciliation rules. Reconciliation rules pertain to records in PeopleSoft. You can expand these reconciliation rules to meet your reconciliation requirements by adding one or more events and by defining event rules for each event. You can also create your own rules if needed. The following table lists delivered rules that are used for reconciling transactions that you might change, and it identifies the PeopleSoft application from which the transactions come:

Reconciliation Rule	PeopleSoft Application
PS_BNK_RCN_DISBRSE	Payables
PS_BNK_RCN_DEPOSIT PS_BNK_RCN_DRAFT PS_BNK_RCN_DEBIT PS_BNK_RCN_PAYMENT	Receivables
PS_BNK_RCN_DEALS	Treasury Management Cash Flows
PS_BNK_RCN_TRAN	External transactions

PeopleSoft also delivers two rules that you normally do not change because they are used only for the setup and cleanup steps in the reconciliation process. These rules are:

PS_BANK_STMT_SETUP
PS_BANK_STMT_EXCEPTIONS

Important! The PS_BANK_STMT_SETUP and PS_BANK_STMT_EXCEPTIONS are integral to the PeopleSoft automatic reconciliation process. If you are setting up a bank account for automatic reconciliation, you must specify these two rules in the Reconciliation Sources grid of the External Account - Account Reconciliation page.

See Selecting a Reconciliation Method.

Pages Used to Set Up Reconciliation Sources and Rules

Page Name	Definition Name	Navigation	Usage
Reconciliation Records	RCN_RECORD_PNL	Banking, Administer Reconciliation, Reconciliation Records	Set up the sources of information that will be the foundation of the reconciliation process.
Reconciliation Field Aliases	RCN_DEFINE_EFLD_PL	Banking, Administer Reconciliation, Reconciliation Field Aliases	Create aliases for field names to make field names easier to identify in setup pages.
Reconciliation Status Code	RCN_STATUS_PNL	Banking, Administer Reconciliation, Reconciliation Status Codes	Create additional statuses (other than the ones that are delivered) to be applied during the auto-reconciliation process and to categorize reconciliation exceptions as needed.
Recon Criteria Options (reconciliation criteria options)	RCN_USR_OPT_PNL	Banking, Administer Reconciliation, Recon Criteria Options	Apply SQL statements to a reconciliation source instead applying those statements to every instance of a rule. You can define additional meta-SQL to append to a specific rule criterion at the reconciliation source level.
Reconciliation Rules	RCN_SOURCE_PNL	Banking, Administer Reconciliation, Reconciliation Rules	Define and assemble the conditions that are required for reconciliation to occur.
Reconciliation Source Notes	RCN_FREEFORM_PNL	Click View Notes on the Reconciliation Rules page.	Enter notes about a reconciliation rule.
Field Updates	RCN_UPDSTAT_PNL	Click the Field Updates icon in the Field Updates column on the Reconciliation Rules page.	Define additional fields (by their alias) and their update values that the reconciliation process can update for a specific reconciliation rule.
Update Criteria	RCN_CRITERIA_PNL	Click the Update Criteria icon in the Update Criteria column on the Reconciliation Rules page.	Define the additional requirements for reconciling data at the field level.
Add SQL Clause	RCN_FREEFORM_PNL	Click Free Form SQL on the Update Criteria page.	Add or modify a SQL statement for a reconciliation rule.
Reconciliation SQL	RCN_VIEWSQL_PNL	Click View SQL on the Update Criteria page.	Review the combined SQL statements for a reconciliation rule.

Defining Reconciliation Record Source Types

Access the Recon Record page (Banking, Administer Reconciliation, Reconciliation Records).

You create or map the source records of your reconciliation process. You can map to items in PeopleSoft applications, such as Payables and Receivables, or to third-party information sources.

Source Type

Select either Statement (bank statement side) or Transaction (system transaction side) for the reconciliation record.

Defining Reconciliation Field Aliases

Access the Reconciliation Field Aliases page (Banking, Administer Reconciliation, Reconciliation Field Aliases).

The selected reconciliation record determines which field names you can access.

Field Name	Enter the field name in the record.
Alias	Enter an alternate name from the field name to use when defining reconciliation rules.

Specifying Reconciliation Criteria Options

Access the Recon Criteria Options page (Banking, Administer Reconciliation, Recon Criteria Options).

A bank frequently sends transactions in cases which the identifier codes do not match the transaction types in the system. You create SQL to map the bank statement codes to the system transaction types.

Option Description	Indicates whether the system uses transaction types to map values. Enter Use Transaction Type ON when you want to use transaction types for reconciliation. This usually creates less exceptions. Enter Use Transaction Types OFF when you do not want to use transaction types for reconciliation. This creates more exceptions, but should be used if the bank is inconsistent with the codes that they provide for transaction types.
Meta SQL ID	Displays the transaction type identifier that you receive from the bank (for example, DEP_1 for a deposit type).
Description	Identify the record on which the transaction type resides.
Where Clause	Enter the conditional meta-SQL statement to execute. Use this field to map the transaction type to a system or bank value.

See Also

Updating Reconciliation Criteria

Defining Reconciliation Rules

Access the Reconciliation Rules page (Banking, Administer Reconciliation, Reconciliation Rules).

View Notes

Click to access the Reconciliation Source Notes page and add notes about this reconciliation rule.

Events

Define banking events, giving each event an event sequence number and event name.

Event Rules

Define event rules for each event.

Rule Seq (rule sequence)	Enter a sequence processing number for the rule.
Action	Enter an action to take for the rule, such as Update, Insert, Truncate, or Delete.
Record	Select a source record on which the reconciliation process takes the action. The record can be a PeopleSoft record or a third-party record. You can also use the BNK_RCN_TRAN record to hold transactions from a third-party feeder system. All the system records should precede the bank records when you add new event rules.
Recon Status (reconciliation status)	Select a reconciliation status. Reconciliation status codes are available only for actions with the value of Update. The default value is UNR (unreconciled). Note. The status code NTB is used for recon-process, recon rules to designate when two or more bank-statement transactions match a single system transaction. See Selecting a Reconciliation Method.
	Click Field Updates to access the Field Updates page and set field aliases and values. You must first enter a valid combination of record and reconciliation status field values to enable this button and access the Field Updates page.
Conditional Rule	Select if this is a conditional rule and enter an Option Code that defines the conditional rule to use.
	Click Update Criteria to view the reconciliation rules that are specified on the Update Criteria page.

See Also

Updating Reconciliation Criteria

Specifying Field Updates

Access the Field Updates page (click the Field Updates icon in the Field Updates column on the Reconciliation Rules page).

Field Alias

Select the field that you want the reconciliation process to update. Select from the aliases that you defined on the Recon Field Alias page.

Update Type

Select Bind Variable, Constant, or SQL Object.

Update Value

Enter an update value description. You can enter multiple update values for the same field alias.

Updating Reconciliation Criteria

Access the Update Criteria page (click the Update Criteria icon in the Update Criteria column on the Reconciliation Rules page).

You create SQL statements to map fields in a record on one side to fields on the other side or to a constant value when you create reconciliation criteria.

To update reconciliation criteria:

Complete the Transaction and Statement fields to map the items (transactons in the system versus transactions on the bank statement) that you will compare and reconcile.

Generally, under Statement, you find the bank statement that is defined (under Reconciliation Records) as a source. Under Transaction, you will reconcile the system side to the bank.
Enter the criteria sequence number in the Seq Nbr field.
Select Transaction ,Statement, or Add SQL in the Source 1 field.
1. If you select Transaction or Statement, select your value in the Field 1 Alias field and its value in the Operator field, and indicate the type of relationship it must have to what you are reconciling. Then map your statement's Source 2 field by selecting Constant, Statement, or Transaction, and enter the appropriate value in the Field 2 Alias field.
2. If you select Add SQL, click the Free Form SQL button to enter free-form SQL statements on the Add SQL Clause page. If you decide to customize the reconciliation rules, you must manually add the SQL to the system source record when the option code for the bank side contains a meta-SQL statement to the bank side. You can add the additional SQL by clicking the Add SQL button, or you can define it by selecting bank Statement in the Source 1 field and completing the rest of the criteria to search for the same condition that you defined as meta-SQL statements on the Recon Criteria Options page.

Click Free Form SQL to enter SQL statements.

Including Fees, Interest, and Miscellaneous Transactions

With automatic reconciliation, PeopleSoft delivers functionality that marks all bank statement transactions that have fees (F), interest (I), or miscellaneous (M) reconciliation transaction codes with a miscellaneous (MSC) reconciliation status. This excludes these MSC transactions from the automatic reconciliation process and prevents them from being reconciled to the wrong system transaction or marked as an exception.

If you do not want to exclude these transactions from the automatic reconciliation process, you must edit the Setup Bank Statement rule (PS_BANK_STMT_SETUP). Omit the entire rule to include the F, I, and M transactions in the automatic reconciliation process. Or, edit the rule SQL to include only one or two reconciliation transaction codes in the automatic reconciliation process.

To omit the entire rule:

Select the Setup Bank Statement (PS_BANK_STMT_SETUP) reconciliation rule.
Either delete the rule, or select the Conditional Rule option and specify an Option Code of NA.

To edit the rule SQL:

Select the Setup Bank Statement (PS_BANK_STMT_SETUP) reconciliation rule.
Locate the row with a Recon Status of MSC on the Reconciliation Rules page, and click the Update Criteria button.
Locate the row with a Field 1 Alias value of TRANS CODE. In the Field 2 Alias field, edit the field values by deleting the value that you want to include in the automatic reconciliation process. For example, if you want to include fees, delete F.
Click OK to save your changes.

Defining Reconciliation Options for Accounts

You specify which reconciliation method to use for each external account and additional reconciliation processing options for the account.

This section discusses how to:

Select the reconciliation method and reconciliation sources for an account.
Define reconciliation tolerances for matching bank and system transactions.

Prerequisites

Before establishing reconciliation rules for bank accounts, you must set up the bank accounts and you must install all reconciliation rules (the DMS file that is loaded during installation).

See Also

Defining External Account Information

Setting Up Reconciliation Tables for Sources and Rules

Pages Used to Reconcile Statements Automatically

Page Name	Definition Name	Navigation	Usage
External Accounts - Reconciliation	BANK_PNLRECON	Banking, Bank Accounts, External Accounts, Reconciliation tab	Select a method of reconciliation, the reconciliation sources to match against the bank statement, and the sequence of execution.
Reconciliation Options	BANK_RCN_OPT_PNL	Click Options on the External Accounts - Account Reconciliation page.	Specify reconciliation options for the account.
Reconciliation Tolerances	BNK_LIMITS_SBP	Click Tolerances on the External Accounts - Account Reconciliation page.	Set reconciliation tolerance ranges on the bank side for matching bank and system transactions.

Selecting a Reconciliation Method

Access the External Accounts - Reconciliation page (Banking, Bank Accounts, External Accounts, Reconciliation tab).

Recon Method (reconciliation method)

The method to use for reconciling transactions for this account.

Automatic: Matches bank transactions to system transactions automatically. You must receive transaction information from your bank in an electronic format or enter your bank statements online to use this option.

SemiMan: (semi-manual): Use this option to compare system transactions and bank transactions online and match them manually.

Manual: You must use manual reconciliation when you have only printed bank statements. With this option, you view only the system transactions online and match them to those in your printed bank statement.

None: Use this option when reconciliation processing is not necessary. Also, select to clear the fields in the Reconciliation Sources group box.

Enable Workflow

Select to enable automatic workflow notification.

This option is available only if you select Automatic for the reconciliation method. It enables the reconciliation process to initiate workflow processing for exceptions and notifies users of reconciliation exceptions on their worklists.

Enforce Bank Statement Edits

Selecting this option affects bank statement entry by:

Displaying a warning message if the bank balance entry is missing an opening or closing balance (codes 010 and 015).

Displaying a warning message if the sum of the bank transactions plus the opening balance does not match the closing balance.

Reconciliation Sources

The source types that you select determine what system data (reconciled against the bank statement) appears on the different reconciliation pages (Automatic Reconciliation, Semi Manual Reconciliation, and Manual Reconciliation).

Source Type	Defines the process type to take place. Select from: Pre-Process: Assign only to the first row for automatic reconciliation. Post Process: Assign only to the last row for automatic reconciliation. Recon Process: Assign to all rows between the first and the last for automatic reconciliation. Assign to all rows for semi-manual and manual reconciliation.
Source Seq (source sequence number)	Specify a number to order the processing of the source types. The first row should contain the lowest number so that it executes first, and the last row should have the highest number. Order the source types to achieve a greater reconciliation success rate. Although PeopleSoft has configured the delivered order of source types to provide an optimum reconciliation rate, you can change the sample sequence numbers depending on how accurate the given source is likely to reconcile against the bank transactions (for example, transactions for which the bank provides you a reference ID are likely to have a higher reconciliation rate). Place these source types at the top with a lower source sequence number. Conversely, transactions with no reference ID are likely to have duplicates (or lower reconciliation rates), and you should place them at the bottom with a higher source sequence number. Refer to the previous page example for an example of the recommended order.
Recon Rule (reconciliation rule)	Identifies the reconciliation rule to use for reconciling the source type. PeopleSoft delivers seven rules that you use for all three reconciliation methods, or you can use your own rules as appropriate. You can sequence and modify these rules as needed. These rules look at both the system transactions and the bank statement transactions: PS_BNK_RCN_DISBRSE: For Payables. PS_BNK_RCN_DEPOSIT: For Receivables. PS_BNK_RCN_DRAFT: For Receivables. PS_BNK_RCN_DEBIT: For Receivables. PS_BNK_RCN_PAYMENT: For Receivables. PS_BNK_RCN_DEALS: For Treasury products. PS_BNK_RCN_TRAN: For PeopleSoft Payables, Receivables, Treasury products, and external transactions. PeopleSoft also delivers two rules that you must use when setting up automatic reconciliation method for an external bank account. You select PS_BANK_STMT_SETUP for the first source type and PS_BANK_STMT_EXCEPTIONS for the last selected source type for an automatic reconciliation. Options and Tolerances are not available for these source types.
Options	Click to select the options to use with the reconciliation rule. This link is available only for the automatic reconciliation method and is a required step. You must specify options for the source transaction type (meta SQL) so the reconciliation process can use the appropriate reconciliation rule from the library. An option added to a specific reconciliation rule executes during automatic reconciliation. The library rules have specific meta-SQL pertaining to specific functionality (for example, TRAN TYPES ON / OFF). By selecting an option, you can attach additional meta-SQLto a reconciliation rule.
Tolerances	(Optional) Click to specify day, monetary, or amount tolerances for the reconciliation rule. This link is available only for the automatic reconciliation method.

Setting Up Reconciliation Tolerances

Access the Reconciliation Tolerances page (click Tolerances on the External Accounts - Account Reconciliation page).

The Reconciliation Tolerances page focuses on the bank side as it reviews transactions on the statement side.

Day Tolerance

Between - and and +

Day tolerance range for matching system and bank transactions. When processing reconciliation, the system looks for source transactions a specific number of days before and after the bank date to match with the bank transactions.

The system uses the Bank Date field (RECON_BANK_DT on the BANK_STMT_TBL) and the Date and Value Date fields (PYMNT_DT and VALUE_DT on the PAYMENT_TBL) for matching.

Monetary Tolerance

Monetary Limit	Indicates whether you use a specific dollar amount or a percentage to determine the limit.
Accounting Template ID	Template that defines how the system generates accounting entries.

Percentage Tolerance and Amount Tolerance

The system displays either the Percentage Tolerance or the Amount Tolerance group box depending on your selection for the monetary limit.

Between - and and +

Specify an acceptable amount or percentage range. When processing reconciliation, the system starts with the source transaction amount, factors in the tolerances, and searches for bank transaction amounts that occur within the specified amount or percentage parameters.

Defining Statement Activities

To define statement activities, use the Statement Activity Type component (STMT_ACT_TYPE_GBL).

The system flags bank transactions such as overdraft fees, interest, or other miscellaneous fees as MISC (miscellaneous) exception items during reconciliation processing because there are no corresponding system transactions. You associate statement activity types with these items, so that the Treasury products generate accounting entries for the transactions based on the ChartFields that are specified in an accounting template when you force them to reconcile.

This section discusses how to create statement activity types.

Page Used to Define Statement Activity Types

Page Name	Definition Name	Navigation	Usage
Statement Activity Type	BANK_ACT_TYPE	Banking, Administer Reconciliation, Statement Activity Type	Define statement activity types.

Creating Statement Activity Types

Access the Statement Activity Type page (Banking, Administer Reconciliation, Statement Activity Type).

Allow Force Reconciliation

Select to enable the reconciliation of transactions that are associated with this statement activity type. When you select this option, you can select and force reconciliation transactions that are associated with this category of statement activity on the AutoRecon Exceptions page. The system adds the item to the system transactions and schedules the appropriate accounting event based on the accounting template that is assigned.

You must select this option to complete the Transaction Table and Accounting Template ID fields.

Transaction Table

Database table that contains the transactions for this type of statement activity. Unless you have modified the PeopleSoft application, you should use the default table, PS_BNK_RCN_TRAN, which stores system transactions for reconciliation processing.

Accounting Template ID

Select the accounting template that defines what accounting entries that the system creates when you force reconciliations.

See Also

Reconciling Transactions Automatically

Entering Accounting Template Information

Setting Up Cash Management Bank Statement Accounting Functionality

To define bank statement accounting, use the Bank Statement Accounting component (BNK_BSAC_SETUP_GBL).

This section discusses how to define bank statement accounting.

Important! You can only implement this functionality if you have purchased and licensed Cash Management.

Bank Statement Accounting Automatic Reconciliation

The Cash Management Bank Statement Accounting (BSA) functionality integrates with the PeopleSoft banks reconciliation functionality. The BSA functionality reduces the number of exceptions (NTFs) that you must resolve. It works in tandem with the PeopleSoft automatic reconciliation process and provides a second layer of filtering that automatically resolves reconciliation exceptions. To enable this functionality, you must first establish the conversion (or accounting) rules that the system uses to search for reconciliation exceptions.

The BSA functionality handles transactions in the bank statement that are not yet booked in the general ledger or a general ledger subledger. These items might include such first notice items such as interest income, interest expense, or forecasted wires.

BSA functionality uses information from the imported bank statement addenda text field. You first create rules to match text strings in the addenda text by selecting Pattern (matching a specified text string), Position (searching for text between two defined numeric values), or Value (matching a defined value). When the system encounters a match, it automatically reconciles the exception by creating a system-side transaction that is assigned to an accounting template, and changes the exception's reconciliation status flag to REC (reconciled). Note that the system does not perform any BSA processing on exceptions that do not match any BSA predefined criteria. Also, the bank statement accounting process will not create a system-side transaction if the bank statement line had previously been matched to a system-side transaction. For example, if the bank-statement accounting process matches a previously reconciled transaction that was unreconciled using the semimanual reconciliation process, it will not create a new transaction.

After the BSA resolved exception accounting entries are complete, you run Treasury Accounting processes to complete the accounting entries prior to posting to the general ledger.

The following are some points to consider about the BSA process:

The process is only as good as the rules and accounting templates that you establish.

Note. The automatically generated accounting entries are only as valid as the accounting rules that you establish. If the accounting rule or specified accounting template for a rule is incorrect, the accounting entries for all of the affected matched exceptions will also be incorrect. It is very important that you test the validity of your BSA data setup and output prior to using this functionality in a production environment.
You can only use this functionality with the automatic reconciliation process.
The BSA process greatly streamlines the reconciliation process, but reconciliation can not be fully automated. There will always be reconciliation exceptions that you must manually research and resolve.
This process becomes increasingly efficient as the number of bank statements that you process increases. When you first implement the process, you must develop the rule structure—researching exceptions, editing rules, and adding new rules—until this structure is sufficiently constructed to handle your organization's exceptions.

See Also

Processing Automated Accounting

Prerequisites

Prior to establishing bank statement accounting parameters, you must set up:

Bank statement activity types.
Accounting templates for bank statement processing.

Page Used to Set Up Cash Management Bank Statement Accounting

Page Name	Definition Name	Navigation	Usage
Bank Statement Accounting	BNK_BSAC_HDR	Banking, Administer Reconciliation, Bank Statement Accounting	Define bank statement accounting match rules. The system searches the bank statement addenda for the transactional text that you define and automatically assigns a default accounting template to the transaction.

Setting Up Cash Management Bank Statement Accounting

Access the Bank Statement Accounting page (Banking, Administer Reconciliation, Bank Statement Accounting).

The system derives the Description and Activity Default Template field information from the selected statement activity type.

You can set Activity Status options at both the overall header level and the individual rule level.

Sample Addenda	Enter sample addenda text to test your defined rule. For example, to define a pattern match (and assuming all required fields are defined): Select the Pattern Match button. Enter the pattern TEST@@@. In the Sample Addenda field, enter the text TEST123, and save the page. Click the Test Rule button. A message will let you know which accounting template would be assigned per the current rule sequencing— whether it came up with an exact match, a rule default, or the activity default.
Test Rule button	Click to test if the rule is behaving as desired. This process applies the defined rule(s) against data in the Sample Addenda field. By doing so, this tests whether the defined BSA rule assigns the appropriate accounting template.
Rule ID	Enter a unique identification code for the match rule, up to seven characters long. This ID is used by the system to order the rules that are applied to the addenda. In this manner, you can dictate which rules should be applied before others.
Rule Default Template	Select a default accounting template for defined positional and pattern rules (not applicable for Value Match option). For exceptions that match the defined rule, the system automatically creates corresponding system-side accounting entries per the default accounting template parameters.
Help Information	Click to view information on defining match values.
Positional Match	Select to match data based on the positions that are entered in the From and Through fields.
Pattern Match	Select to match the entire masked string value that is adjacent to the entered Pattern.
Value Match	Select to match the values that are entered in the Match Valuefield and also specify a Match Template value. If you define a value that cannot be matched, the system uses the Activity Default Template value.

Field Alias	Select the field that you want the reconciliation process to update. Select from the aliases that you defined on the Recon Field Alias page.
Update Type	Select Bind Variable, Constant, or SQL Object.
Update Value	Enter an update value description. You can enter multiple update values for the same field alias.