Automatic Payment Reconciliation
User will need the permission of USPS_ADMIN or USPS_STANDARD_PAYMENTTRANSACTION_UPDATE
The Automatic Payment Reconciliation program is used to creates a file, that will define all the data that is to be extracted and placed on the sequential file to be sent to the bank.  This file will also define all data that is returned from the bank to be reconciled by the district. This file allows you to define the specifics of your import/extract routine - what and how you want to import or extract. When setting up importing or extracting, you specify the type of file layout (fixed length or comma separated) and what records are contained in the file.Â
The Automatic Payment Reconciliation file must include all data fields to be extracted, the starting position of the data within each record, the field length, picture, and field type as shown in the examples below.
Pay Rec - Import File
The File Format must be equal to one of the following:
Import Type:
- CSV - Indicating comma separated delimited file. Â The Length is preset for each field
- Comma separated files place quotes around text fields and separate fields with commas. These files are simpler to work with compared to fixed length files, as only the field order needs to be known.
- Comma separated files place quotes around text fields and separate fields with commas. These files are simpler to work with compared to fixed length files, as only the field order needs to be known.
- Fixed Length - All information is formatted with either valid data or spaces. Fixed Length fields are filled with spaces to a specified width. Each field has an offset from the start of the line, and a length in number of characters.
- Example:
- SMITH, TODD                        10004A 12000.00 155 ESTELLINE RD.
- 123456789012345678901234567890123456789012345678901234567890
- The start of the Name field is at position 1 and the length is 24. The Number field starts at position 25 and its length is 6. Notice how the name starts at character position 1, and there is room for a long name, although not all of the space is used with this one. The length of the name field is 24 characters. Then the account number starts at character position 25 in the file, and is 6 characters wide. When you import Fixed Length files, you will need to know the starting position and width of every field you want to import.
- Example:
The Automatic Payment Reconciliation file can include these data fields to be imported, the starting position of the data within each record, the field length, picture, and field type as shown below:
Field Types options -
- Amount (Explicit Decimal)
- Amount (Implicit Decimal)
- Bank Account
- Check Date
- Check Day
- Check Month
- Check Number
- Check Year
- Ignored - When importing from bank, can select 'ignored' to ignore a field that is included in the file from the bank
- Payee Address
- Payee Name
- Payee Name 2
- Void Flag
Length - The Length is how many characters the field can be. This will only relate to if the file is Fixed Length. If the length for Bank Account is 12, then the field might contain 12 characters
Format -Â The Format determines what characters can go in that field. i.e. Bank Account is defaulted to ([0-9]+) which means the data has to be any combinations of digits 0-9. Example; 4123456733 would be valid 32134A1344 would fail the constraint check
The Import Fields can be saved using the 'Saved Formats' option.  Once saved, clicked on , select file type and click on .
Pay Rec Extract
Extracts outstanding check information and copies it to a file in a format that can be used by the district's bank.  In order to extract data in a form that can be read by the bank, a special file must first be created. This file is required since different banks may have different formats for the data.
For more information on the Format Specifications, please click here
Extract Type:
- CSV - Indicating comma separated delimited file. Â The Length is preset for each field
- Comma separated files place quotes around text fields and separate fields with commas. These files are simpler to work with compared to fixed length files, as only the field order needs to be known.
- Fixed Length - All information is formatted with either valid data or spaces. Fixed Length fields are filled with spaces to a specified width. Each field has an offset from the start of the line, and a length in number of characters.
- Example:
- SMITH, TODD                        10004A 12000.00 155 ESTELLINE RD.
- 123456789012345678901234567890123456789012345678901234567890
- The start of the Name field is at position 1 and the length is 24. The Number field starts at position 25 and its length is 6. Notice how the name starts at character position 1, and there is room for a long name, although not all of the space is used with this one. The length of the name field is 24 characters. Then the account number starts at character position 25 in the file, and is 6 characters wide. When you import Fixed Length files, you will need to know the starting position and width of every field you want to import.
- Example:
Extract Field options -
- Check Number
- Check Date Month - Example 04
- Check Date Day - Example 15
- Check Date Year - Example 2019
- Check Date - Example 04/15/2019
- Check Amount (Implicit) - Example 00003115
- Check Amount (Decimal) - Example 000031.15
- Void Check Flag - is a 1 alpha field - Example P = Paid
- Payee Name
- Payee Name 2
- Payee Address
- Bank Account Number
- Spacer - (Only used for Fixed Format) is to space over a field - Example is ,, .  To get more than 1 field, you would need to have a spacer option for every field you want as blank
- Â
Length - The Length is how many characters the field can be. This will only relate to if the file is Fixed Length. If the length for Bank Account is 12, then the field might contain 12 characters
Format -Â The Format determines what characters can go in that field. i.e. Bank Account Number is defaulted to %8s which means the data has to be any string, which is alpha and numeric.Â
Check Date -Â Â Date needs to be before the Issue Date of the check in order for it to be included in the Extract File
Bank Account -Â Â This will need to be setup under the Core/Bank Accounts option first.
Payment Transaction Type - Choose one of the following:
- Payroll Check
- Deduction Check
- Group Deduction Check
- Refund of Payroll Item
- All
Once all data is entered, click on  to create the submission file
Example of a CSV FormatÂ
Example of a Fixed FormatÂ
Once all data is entered, click onto create a .pdf report to view