Cleared Check File Import

The Cleared Check File function is an automated way to import checking account information provided by the bank. It is designed to alleviate the need for manual cross-referencing cleared checks and deposits from a bank statement to the Agvance system.

This function is found in the lower left corner of the Checking Account Reconciliation window.

Cleared Check File Location

There is no required location for placement of the cleared check file. As long as there is access to the location from the Windows session running Agvance, the file can be imported. After the Load File button is clicked, a file import window displays to browse through available network locations to find the file.

Pending File

The pending file contains exceptions to the import rules, as detailed above. It is recommended to monitor this file after every import to ensure certain items included in the file do not need to be manually reconciled in Agvance. Once created, the pending file is automatically stored in the Agvance data directory; the naming system is SSIReconAccountID.txt (Ex: SSIRecon1010.txt). The Edit Pending File button allows pending file entries to be viewed and deleted.

Standard

Layout of Agvance Cleared Check File Import

There are no Header and Trailer records.

The following table describes the Detail records.

PositionLengthField NameTypeAdditional Information
01-1010Account #Alpha-numeric 
11-2010Serial #NumericAgvance drops leading zeros.
21-3010AmountNumericTwo decimals are assumed by Agvance.
34-418Paid DateYYYYMMDD 
50-578Check DateYYYYMMDD 
821Transaction TypeAlpha-numericThree record types are supported.
P stands for Processed.
D stands for ACH Debits.
C stands for Miscellaneous Credits.

Record Types

  • P – The P transaction type records are matched against the Checks grid using the reference number and the amount.
  • D – The D transaction type records are matched against the Checks grid using the amount only.
  • C – The C transaction type records are matched against the Deposits grid using the reference number and the amount.

Any other records with a transaction type other than those listed above are not matched against either grid and are written to the pending file.

When a new file is loaded using the Load File button, a warning message displays that O records may get overwritten in the pending file.

A sample of the cleared check file is below:

PNC

Record
Field
Position
HeaderIdentified with an H in Position 1
HeaderBank Account Number13 - 22
DetailIdentified with a D in Position 1
DetailBank Account Number
(matches account number in associated Header record)
5 - 14
DetailRecord Type (P, S, C, D)*15
DetailCheck Number24 - 33
DetailAmount34 - 45
TrailerIdentified with a T in Position 1
TrailerBank Account Number
(matches associated Header and Detail Records)
5 - 14

*Record Types:

  • P – These records are checks that should be matched on Check Number and Amount. If there is no match, send to pending file.
  • S – These records should go straight to the pending file.
  • C – These records are deposits and should be matched on Amount only. If there is no match, send to pending file.
  • D – These records are checks and should be matched on Amount only. If there is no match, send to pending file.

CoBank

Record
Field
Bytes
Position
DetailAccount Code1 Value: "0"1
DetailAccount NBR *102 - 11*
DetailCheck NBR *1012 - 21*
DetailCheck Amt *1021 - 31* Assumed Decimal
TrailerPaid Date (MMDDYYYY)832 - 39
TrailerTrailer ID161 - 16 Value: "9999999999999998"
TrailerTotal Amount *1517 - 31* Assumed Decimal
TrailerTotal Count *732 - 38*

Wells Fargo

Column values for the CSV file are as follows:

  1. Begin Date
  2. End Date
  3. Bank ID
  4. Bank Name
  5. State
  6. Account Number
  7. Account Type
  8. Account Name
  9. Currency
  10. Section/Report Name
  11. Transaction Type
  12. Serial Number/Reference Number
  13. Amount
  14. Issue Date
  15. Posted Date
  16. Stop Date
  17. Release Date
  18. As Of Date
  19. Reversed Date
  20. Debit/Credit Code
  21. Optional Info/Tran Desc

Bell Bank

Column values for the CSV file are as follows:

  1. Account Number
  2. Post Date
  3. Check
  4. Description
  5. Debit
  6. Credit
  7. Status