Bank Transmission File Format Table

Use this option to set up the format for providing payroll check information to your bank. The format defines the layout and content of the files you send.

Important

Before using this option, contact your bank for a copy of their file specifications.

Menu Path: Fund Accounting > Reference Tables > Reference Tables > Bank Transmission File Format

Creating Bank Transmission File Formats

Different software systems store records differently. For example, the format of check records in Human Resources is most likely different from the format of checks in your bank's computer system. As a result, you cannot simply send a copy of your check files to the bank. Rather, you need to create files that are formatted so the bank's system can read them.

File Elements

Fund Accounting's Bank File Format Transmission table enables you to set up a format based on the requirements of your bank's software system. Three elements need to be accounted for in a format: files, records, and fields. To understand the formatting process, it may be helpful to review some basics:

A file is a collection of records. It can also be a collection of files. For example, a bank file can contain multiple files that store checks by their dates. These smaller files are referred to as batch files.
In addition to records, a file includes header and trailer records. Generally, the header identifies the file, while the trailer summarizes its contents.
Records are divided into fields. For example, check records in Human Resources include seven fields. However, only three of these might be requested by your bank, for example: Check Number, Payee, and Amount. In addition, the fields must conform with the bank's data requirements.

Record Types

The files used to transmit check data can include up to six types of records:

File Header	Defines the information for the file's header record, such as the date, bank identification number, and your tax number.
Batch Header	Defines the information for the header records for a batch in the file, such as the check date and bank account number.
Detail Record	Specifies the information to include from each check, such as the check number, payee name, and check amount.
Batch Trailer	Summarizes the records of a batch within the file, such as the total number of checks issued and their total amount.
File Trailers 1 & 2	Summarize the records in the file, such as the number of batches, total number of checks, and their total amount.

Your bank's format may not require all of these Record Types. For example, if the bank only accepts one batch at a time, its format could include just three types of records: File Header, Detail Record, and File Trailer.

File Formatting

A file format requires multiple table records, one for each field in each record type. The primary components of these records include:

Layout Code, which identifies a format and ties all of its records together.
Record Type, which determines the type of record in the transmission file.
Field Name, which identifies a field within a record.

Format, which can be one of the following:

Right Justified	Data is stored beginning on the right-hand side of a field. Usually, numeric fields are right justified.
Left Justified	Data is stored beginning on the left-hand side of a field. Usually, character fields are left justified.
Date Format	Dates are stored in a format such as MMDDYY (month-day-year) or YYYYMMDD (year-month-day).

If a field's data does not does not fill the entire field, you can have the system add zeroes or characters to the beginning of the field for right-justified fields and at the end for left-justified fields. For example, $75 could be stored as 0000007500 in a right-justified field that contains 10 positions.

Start/Stop Positions, which define a field's length and determine where the field starts and stops within a record. For example, a check record might include a total of 50 positions:
Start Position
Field Name
Length
Format
Stop Position
01
Check Number
10 numeric
Right justified
10
11
Payee Name
30 character
Left justified
40
41
Check Amount
10 numeric
Right justified
50
Literal, which can define either filler, blank spaces, or a constant value. For example, a literal can specify a character or number to fill blank spaces in a field or can attach the same information to each check, such as your organization's name. For more information, refer to the Fields.
Batch Group, which enables you to create separate batches within a file, one for each check date.

Format Example

Following is an example of a bank format, organized by Record Type. The specifications you receive from your bank will differ in layout and content, but will be similar in terms of the type of information provided.

	Start	Field	Length	Format	Stop

File Headers	01	File Date	6 numeric	MMDDYY	06
File Headers	07	Bank #	10 numeric	Right justified	16
	17	Client Name	30 character	Left justified	46

Batch Headers	01	Account #	10 numeric	Right justified, 0 fill	10
Batch Headers	11	Check Date	8 numeric	MMDDYYYY	18

Detail Records	01	Check #	8 numeric	Right justified, 0 fill	08
Detail Records	09	Payee Name	30 character	Left justified, X fill	38
	39	Check Amt	10 numeric	Right justified, 0 fill	48

Batch Trailers	01	Total Batch Checks	6 numeric	Right justified	06
Batch Trailers	07	Total Batch Amt	10 numeric	Right justified, decimal	16

File Trailer	01	Total File Checks	6 numeric	Right justified	06
File Trailer	07	Total File Amt	10 numeric	Right justified, decimal	16

Important

Each line in the chart requires a record. For example, the File Header in the example includes three fields, each of which must have its own record.

In the report, the Batch Group column for the Batch Header record's Check Date field contains a Y, indicating that this format will create separate batch files, one for each check date.

Creating a file format

Select Fund Accounting > Reference Tables > Reference Tables > Bank Transmission File Format.
Click Add New.
In the Bank Layout Field Information section, complete the fields in accordance with your bank's specifications.
Tip
We recommend that you complete the Record Types in numeric order (lowest to highest) and create all records for each Record Type in order of their File Positions.
Click OK to save the record.

Displaying a list of records

Select Fund Accounting > Reference Tables > Reference Tables > Bank Transmission File Format.
In the Search Criteria section, enter criteria to select the records you want to display. To display all existing records, leave the section blank.
Click Find.
Use the down arrow key to scroll through the records.
At this point, you can select a record to change or delete. You also can print a report that includes the displayed records. Refer to the following procedures for the steps required.
Click Back to return to the Search Criteria section.

Changing a Bank Transmission File Format record

Select Fund Accounting > Reference Tables > Reference Tables > Bank Transmission File Format.
In the Search Criteria section, enter criteria to select the records you want to display. To display all existing records, leave the section blank.
Click Find.
In the List section, select the record you want to change.
In the Bank Information Data section, change the desired fields.
Click OK to save the record.

Deleting a Bank Transmission File Format record

Select Fund Accounting > Reference Tables > Reference Tables > Bank Transmission File Format.
In the Search Criteria section, enter criteria to select the records you want to display. To display all existing records, leave the section blank.
Click Find.
In the List section, select the record you want to delete.
Click Delete.
In the Confirmation dialog, click Yes.

Generating the Bank File Layout List report

Select Fund Accounting > Reference Tables > Reference Tables > Bank Transmission File Format.
In the Search Criteria section, enter criteria to select the records you want to include. To display all existing records, leave the section blank.
Click Find.
Click Print.
In the Print window, enter settings for generating the report. The report's default file name is bnk_layout.rpt.
Click OK.

Fields

Field

Description

Layout Code

Code identifying a bank transmission file. The same code applies to all records in the file. Character/10

You must start the code with one of the following characters:

A - Accounts Payable - to indicate that the file contains accounts payable checks generated in Fund Accounting.
P - Payroll - to indicate that the file contains payroll checks generated in Human Resources.

The Layout Code continues to display as you begin each subsequent record. Do not change the code, unless you want to start a new file format. Also, keep in mind that you cannot change a record’s code once the record is saved.

Description

Brief description of the file’s contents, for example, PAYROLL CHECK FORMAT or MIDLAND BANK FORMAT. Changing the description in one record changes it in all records associated with a format. Character/25

Record Type

Code identifying the type of record being created within a file.

Select:

1 - File Header - Designates the current record as the header record for the entire file.
2 - Batch Header - Designates the current record as the header record for a batch within a file.
3 - Detail Record - Designates the current record as a check record.
4 - Batch Trailer - Designates the current record as the trailer record for a batch within a file. This record usually provides a summary of the file, such as a count of the batch’s checks and the sum of all check amounts.
5 - File Trailer 1 - Designates the current record as the trailer record for the entire file. This record usually provides a summary of the file, such as a count of the file’s checks and the sum of all check amounts.
6 - File Trailer 2 - Allows an additional trailer record for the transmission file, if specified by the bank.

File Position

Number identifying a field’s location in a particular Record Type. Records should be entered and numbered in sequential order. Integer/4

Example: If the bank’s specifications call for a check detail record with five fields, you should enter a record for each field in the specified order, assigning 3-Detail Record as the Record Type and numbering the records from 1-5 in the File Position field.

Delimiter

Optional character for separating fields in a record, such as a comma or semicolon. This tells the system where to start and stop when reading a field. Character/1

When you enter a delimiter, it defaults to all records for the current format. Changing the delimiter in one record changes it in all records.
If you use a delimiter, you cannot access the Start Position and Stop Position fields. Instead, you must enter a length the Field Length field.
If you do not use a delimiter, you must complete the Start Position and Stop Position fields in each record.

Field Name

Name of a check-related field in an accounts payable or payroll record. The drop-down list’s selections apply to either payroll checks or accounts payable checks.

Select:

Payroll
HRM Amount	HRM Check Number
HRM Bank Account	HRM Payee Name
HRM Check Date

Accounts Payable
A/P Amount	A/P Check Number
A/P Bank Account	A/P Funding Source
A/P Check Date

Both Check Types
Today’s Date

The Field Name field is optional if you are using the Literal field to create a blank field or the Count or Sum field to set up totals in trailer records. Refer to the descriptions of these fields for details.
To use the HRM Record Status and A/P Record Status selections as field names, you must set up a translation table in Human Resources’ User Defined Table option. Refer to the previous section, User Defined Check Status Translation Table, for details.
The drop-down list’s Line Number and Detail Line Number selections do not appear in the above listing, because they are not being used.

Format

Defines a field’s format.

Select:

Right Justified - Aligns the last number or character in a value at the right-hand side of the field. If specified in the Literal field, the system fills any empty positions at the beginning of the field with zeros or characters.
Left Justified - Aligns the first character or number in a value at the left-hand side of the field. If specified in the Literal field, the system fills any empty positions at the end of the field with zeros or characters.
Date Formats - Determines the format of the month, day, and year in a date, for example, MMDDYYYY. The selections include most widely-used formats, including Julian dating (YYDDD).

Literal

Multi-functional field. Character/30

Your entry can include one of the following, depending on your bank’s specifications:

A constant value for displaying in a record. For example, if you enter PAY as the literal and set the field length to 3, the word PAY will appear at the defined file position. In this case, do not select a field name.
A value to use as a fill character when a field’s value does not
Field Name
HRM Payee
HRM Check Amount
Format
Left Justified
Right Justified
Length
20
10
Literal
X
0 (zero)
Original Value
THOMAS A WILSON
51425
File Value
THOMAS A WILSONXXXXX
0000051425
The value BLANK, which allows you to use a record as a blank filler. For example, if you define a field’s length as 25, enter 25 spaces in the Literal field, then follow the spaces with the word BLANK as an identifier.
You can enter a literal in either upper/lower case or all capital letters.
If an apostrophe ( ' ) is specified, you must precede it with an additional apostrophe. If the extra character is omitted, the system ignores your entry.

Start Position

Number identifying the position in a record where a field begins. Integer/4

This value must be one greater than the Stop Position of the previous field, unless the current record is a Record Type’s first field, in which case you must enter 1 as the Start Position. You cannot access this field if a Delimiter is being used.

Stop Position

Number identifying the position in a record where a field ends. You cannot access this field if a Delimiter is being used. Integer/4

Field Length

Number of positions in a field. Integer/4

If a Delimiter is not being used, the system calculates the length based on the values in the Start Position and Stop Position fields. Otherwise, you must enter the length.

Include Decimal Point

Checkbox indicating whether a decimal point should be included in a field’s numeric value.

Select the box to use a decimal point, for example, in check amounts.
If you leave the field blank, a decimal point would not be used but would be implied. For example, $545.45 would appear as 54545.

Batch Group

Checkbox for creating separate batches in your transmission file. This setting only applies if the Record Type is 2 - Batch Header. Leave the checkbox blank if your bank does not want checks grouped.

You can group checks by dates, status, or other criteria, depending on your selection in the Field Name field.

Example: If a bank wants records grouped by date, select 2 - Batch Header in the Record Type field and HRM Check Date in the Field Name field, and then select the Batch Group checkbox. These settings create Batch Header records for each date. You must also set up a Batch Trailer record to follow each batch. For an example, refer to the Record Type to Count field.

Count or Sum

Selection indicating that current record should provide either a record count or total amount of the checks in a batch or file. This field only applies to 4 - Batch Trailer, 5 - File Trailer 1, and 6 - File Trailer 2 records.

Select:

C - Count
S - Sum issued checks
B - Sum issued and void checks

Leave the field blank if you are not setting up a count or sum record.

Record Types to Count (1-6)

Checkboxes for designating the Record Types that apply to a record count. The boxes correspond to the selections in the Record Types field. Select the boxes to indicate which Record Types to use in the count.

Note

If you want the Batch Trailer record to include the number of batches in the file, you must use the field name entered in the Batch Header record.

Example: For a count of all checks in the batches created in the previous example (Batch Group field), set up the Batch Trailer record as follows:

Field	Selection
Record Type	4 - Batch Trailer
Field Name	HRM Check Date
Count or Sum	C - Count
Record Types to Count	3 (Detail Record)

In this case, the Field Name field in the Batch Header record was HRM Check Date. Also, keep in mind that to produce multiple batches, the Batch Header’s Batch Group checkbox must be selected.

Start Position	Field Name	Length	Format	Stop Position
01	Check Number	10 numeric	Right justified	10
11	Payee Name	30 character	Left justified	40
41	Check Amount	10 numeric	Right justified	50