Check Reconciliation File Format Table
The Check Reconciliation File Format table lets you define the way the system reads the files your bank sends to you for check reconciliation. The application accommodates two types of check reconciliation, one for payroll checks and one for accounts payable checks. The check reconciliation options use format records to identify key information in the data file your bank sends you.
Since there are two check reconciliation options, you need to define at least two records in the format table, one for payroll checks and one for accounts payable checks. This is the case even if you use the same bank account for both payroll and accounts payable.
The default file name when printing the Check Reconciliation File Format table to disk is recon_setup.rpt.
Menu Path: Human Resources > Reference Tables > Payroll > Check Reconciliation File Format
Using Check Reconciliation File Format Records
You use Check Reconciliation File Format records to define key characteristics of the data files received from the bank handling your accounts payable and payroll checking accounts. These records tell your system where and in what form check reconciliation data appears in the bank file.
A bank data file is composed of a series of character strings, each string holding up to 80 characters describing an individual check you issued. For the system to read the bank file and reconcile cleared checks in your database, you use the Check Reconciliation option for payroll checks and Fund Accounting's Reconcile Checks from File option for accounts payable checks.
Each check reconciliation option prompts you to select the correct format record to use for reading the bank file data. The Check Reconciliation File Format record's A/P or Payroll field determines if a selected record is compatible with the check reconciliation option you run.
The system reconciles checks based on the reconciliation option used. If the bank data file contains both payroll and accounts payable checks, you need to run both options to process your cleared checks.
The check reconciliation options are described in Additional Payroll Procedures and the Fund Accounting Vendor Checks section.
Sample Bank File Character String
When you define a Check Reconciliation File Format record, you enter parameters your system will use to locate the following information in each of the bank file's character strings: Check Number, Check Amount, Issue Date, Clear Date, and Account Number. Remember, the bank file contains a series of character strings, each string signifying a processed check.
Before you define your file format records, you need to understand two types of information:
The contents of a format record
The way this record's fields relate to the data file your bank sends you for check reconciliation.
To better understand the formatting requirements, try reading through the Fields that follow while looking at the data file your bank sends you.
The Check Reconciliation File Formats page's fields are divided into four logical groups: General, Check, Date, and Account Information sections. The General Information fields pertain to the file format record as a whole. The Check, Date, and Account Information sections define how the system identifies the respective data in the actual bank file.
These field descriptions frequently reference the position of information in individual bank file character strings. Assume the system reads from left to right when determining the starting position of information in the bank file.
General Information Section
Field | Description |
|---|---|
Bank Format | One-character code identifying the file format record. This code must be unique for each record. The drop-down list includes the letters A through Z and the digits 1 through 9. |
A/P or Payroll | Determines whether the record is for Accounts Payable or Payroll check. When you run a reconciliation, the system checks this field to ensure you picked a file format with the option you are using. Select: Accounts Payable |
Eliminate Zero Filling | Selection defining how the system should treat leading zeroes when comparing your data with information in the bank file. A leading zero is any zero in a field that comes before any other non-zero character. For example, if a field has a value of 00104A, it contains two leading zeroes. Select: Account Number - Ignores leading zeroes in the bank file for the Account Number data but to include leading zeroes for Check Numbers. Use this selection if you do not use leading zeroes for the Account Number in your database, but you do use leading zeroes for Check Numbers. The Account Number corresponds to the value you enter in this table’s Account Number field. |
Bank Name | Title of the bank associated with the Check Reconciliation File Format record. Character/30 |
Check Information Section
Use this section to determine the position and format of check information in the bank file.
Field | Description |
|---|---|
Begin Check Number | Position where check number information begins in the bank file’s individual character strings. Integer/3 |
Check Number Length | Number of characters devoted to the check number in an individual bank file character string. This includes leading zeroes. Integer/3 |
Zero-Fill Check Number | Checkbox determining whether leading zeroes will be added to check numbers in the bank file. Select the box to allow leading zeroes. |
Begin Check Amount | Position where check amount information begins in the bank file’s individual character strings. Integer/3 |
Check Amount Length | Number of characters devoted to the check amount in an individual bank file character string. This includes leading zeroes and decimal point if decimal points are used. Integer/3 |
Implied Decimal In Amount | Checkbox that indicates whether the bank file includes a decimal point in the check amount.
|
Date Information Section
Use this section to determine the position and format of date information in the bank file.
Field | Description |
|---|---|
Begin Issue Date | Beginning position for the check’s issue date in the bank file’s individual character strings. Integer/3 |
Issue Date Length | Number of characters dedicated to the check’s issue date in an individual bank file character string. If the issue date displays as 08/25/2017, you would enter 10 in this field to account for the numbers and slash ( / )symbols. Integer/3 |
Date Format | Format for dates in the bank file, using M, D, Y, and any special characters. For example:
|
Begin Clear Date | Beginning position for the check’s clearance date in the bank file’s individual character strings. You need to enter a value in this field if you want the system to change the Clear Date field for reconciled payroll and accounts payable checks. Integer/3 |
Clear Date Length | Number of characters dedicated to the check’s clearance date in an individual bank file character string. If the clearance date displays as 09/22/2017, you would enter 10 in this field to account for the numbers and / symbols. You must enter a value in this field if you want the system to change the Clear Date field for reconciled payroll and accounts payable checks. |
Julian Date | Checkbox indicating whether dates display in YYDDD format. In this format, YY stands for the year's last two digits, and DDD for the day’s number within the 365-day year (366 in leap years). For example, January 1, 2017 would appear as 17001, the first day in the year 2017. Select the field if the file uses Julian dating. |
Account Information Section
Use this section to determine the position and format of bank account information in the bank file.
Field | Description |
|---|---|
Begin Account Number | Beginning position for account number information in the bank file's individual character strings. This includes leading zeroes. |
Account Number Length | Number of characters dedicated to the account number information in an individual bank file character string. |
Account Number | Your bank account number entered exactly as you want it read by the system. This number appears in the Check Reconciliation report generated when either reconciliation process is run. The system also uses it to compare against the bank file character strings if they store account number information.
|