Check Reconciliation File Format Table

The Check Reconciliation File Format table enables you to define the way the system reads the files sent by your bank for check reconciliation. The application accommodates two types of check reconciliation, one for accounts payable checks and the other for payroll checks. The system's check reconciliation options use Check Reconciliation File Format records to identify key information in the data file sent by your bank.

As there are two check reconciliation options, define at least two records in this table, one for each type of check. This applies even if you use the same bank account for both accounts payable and payroll.

To generate a report listing selected table records, click Print. The default file name of the report is recon_setup.rpt.

Menu Path: Fund Accounting > Reference Tables > Reference Tables > Check Reconciliation File Format

Using Check Reconciliation File Format Records

Check Reconciliation File Format records enable your system to decipher the records your bank provides for reconciling your account payable and payroll checking accounts.

A check record is an 80-character string composed of smaller strings, each of which stores a different type of data, such as the account number, check amount, and issuance date. The format defines the parameters for reading where and how this information is stored, for example, the beginning and ending positions of the check number or the format used to store the date.

Two options are provided for reconciling checks: Fund Accounting's Reconcile Checks from File option for accounts payable checks, and Human Resources' Check Reconciliation option for payroll checks. As a result, set up a format record for each type of check. If a file contains both types of checks, run both reconciliation options to process all the cleared checks in the file.

Fields

General Information Section

Field	Description
Bank Format	Identifies the check reconciliation 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. Character/1
A/P or Payroll	Specifies whether the record is for AP or Payroll checks. When you run a check reconciliation option, the system refers to this field to ensure you picked a file format compatible with the option you are using. Select: Accounts Payable Payroll At a minimum, you need to create two records, one defined with the Accounts Payable setting, the other defined with the Payroll setting. This is the case even if you use one checking account for both purposes.
Eliminate Zero Filling	Determines how the system must 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 - notifies the system to ignore 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. Check # - notifies the system to ignore leading zeroes in the bank file for the Check Number data but to include leading zeroes for the Account Number. Use this selection if you do not use leading zeroes for Check Numbers in your database, but you do use leading zeroes for Account Number. Check # and Account # - notifies the system to ignore leading zeroes in the bank file for both Account Number and Check Number data. This selection assumes you do not use leading zeroes in your database for either of these fields. None - notifies the system to include leading zeroes when comparing your data to the bank file’s data for both Account Number and Check Number. Use this selection if your database uses leading zeroes for both these fields.
Bank Name	Title of the bank associated with the Bank File Format record. Character/30

Check Information Section

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 assigned to the check number in an individual bank file character string. This includes leading zeroes. Integer/3
Zero-Fill Check Number	Checkbox to determine whether the system must add zeros to the front of check numbers in the bank file. Select the field if you want to use zero-filling. In this case, the number of zeros added depends on the entry in the Check Number Length field minus length of the check number entered. For example, if length is set to 9 and the check number is 123456, three zeros would be added to the front of the check number. Leave the field blank to prevent zeros from being added to the front of check numbers.
Begin Check Amount	Position where the check amount information begins in the bank file’s individual character strings. Integer/3
Check Amount Length	Number of characters assigned 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 indicating whether the bank file includes a decimal point in the check amount information. Select the field if the decimal point does not appear and is therefore implied. In this case, an amount such as $768.48 will display as 76848. Leave the field blank if the decimal point appears in the bank file for check amounts, in which case an amount such as $768.48 will display as 768.48.

Date Information Section

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 assigned to the check’s issue date in an individual bank file character string. If the issue date displays as MM/DD/YYYY, enter 10 in this field to account for the / symbols. Integer/3
Date Format	Format used in bank file character strings for dates. Character/10 Use this field to show the system the order and number of characters used for month, day, and year in the bank file. Also, account for any special characters that display, such as a dash ( - ) or right slash ( / ). For example, if the bank file displays December 15, 2018 as 12/15/2018, enter MM/DD/YYYY in the Date Format field. If the bank file uses Julian date format and you select the Julian Date field, enter YYDDD in the Date Format field.
Begin Clear Date	Beginning position for the check’s clearance date in the bank file’s individual character strings. Enter a value in this field if you want the system to update the Clear Date field for reconciled payroll and/or accounts payable checks. Integer/3
Clear Date Length	Number of characters assigned to the check’s clearance date in an individual bank file character string. If the clearance date displays as MM/DD/YYYY, enter 10 in this field to account for the / symbols. Integer/3 Enter a value in this field if you want the system to update the Clear Date field for reconciled payroll or accounts payable checks.
Julian Date	Checkbox indicating whether dates display in the following format: YYDDD. In this format, YY stands for the last two digits in the year, and DDD stands for the day’s number within a 365-day year (366 in leap years). In this format, January 1, 2018 would appear as 18001 - day 1 of the year 2018. Select the field if the bank file uses the YYDDD format. Otherwise, leave the field blank.

Account Information Section

Field

Description

Begin Account Number

Beginning position for account number information in the bank file’s individual character strings. This includes leading zeroes. Integer/3

Account Number Length

Number of characters assigned to the account number information in an individual bank file character string. Select the number from the drop-down list (1-10).

Account Number

Your bank account number entered exactly as you want the system to read it. 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. Character/10

If you use leading zeroes for the account number, include them in this field.
If you set the Eliminate Zero Filling field to None or Check #, use the leading zeroes in this field, particularly if the bank file uses leading zeroes for the account information.
If the bank file does not include account numbers and you do not need to track account numbers in the Check Reconciliation report, enter 0 in this field.