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 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. |
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.
|
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.
|
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
|