Child pages
  • PAYREC - Automatic Reconcile and Extraction Program1
Skip to end of metadata
Go to start of metadata

overview

The PAYREC program is designed to automatically reconcile a district's payroll and deduction company checks. The program consists of two options, Extract and Reconcile.

The first option extracts outstanding check information and copies it to a file in a format that can be used by the district's bank.

The second option reconciles the checks on the file returned by the bank.

In order to extract data in a form that can be read by the bank, a special initialization file must first be created by the DAS. This file is required for both export and import of data. The initialization file is required since different banks may have different formats for the data.

Refer to the chapter titled PAYREC in the USPS Reference Manual for district information on using this program.

initialization file

The DAS must first create an initialization file, PAYREC.INI, that will define all the data that is to be extracted and placed on the sequential file to be sent to the bank. This initialization file will also define all data that is returned from the bank to be reconciled by the district.

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

General requirements are that the field names remain exactly as shown in the examples. The field's picture clause is that of any valid COBOL definition with types of NUMERIC or ALPHA. Numeric fields must have a numeric picture clause and alpha fields an alpha picture clause. Numeric fields may not exceed a total of eighteen digits, including decimal positions.

Data Elements Defined

  • SECTION can be defined as either EXPORT_RECORD, IMPORT_RECORD, or GLOBAL.
    • EXPORT_RECORD indicates that the fields and field attributes will define the data within each record of the sequential file that is to be extracted and exported to the bank.
    • IMPORT_RECORD defines the record layout and field attributes for all data in a sequential file sent by the bank to the district to be reconciled.
    • GLOBAL indicates that the export extract records and import reconcile records will be the same as defined by the field attributes.
    • FILE_TYPE is a required field for delimited files. This field indicates what type of file (Delimited, or Delimited_Names) is being reconciled. If the FILE_TYPE field is not found, PAYREC will abort. The FILE_TYPE must be equal to one of the following:
      • Delimited - Representing a fixed-formated delimited file with the column numbers after each data element.
      • Delimited_Names - Representing a free-formated delimited file with the names of the fields in the file labeled after each data element.
    • FILE_FORMAT is a required field for a Delimited or Delimited Names file type. This field indicates the format of the import Delimited or Delimited Names file. If the FILE_FORMAT is not found for the Delimited or Delimited Names file, PAYREC will abort. FILE_FORMAT must be equal to one of the following:
      • "CSV" - Indicating comma separated delimited file.
      • "TAB" - Indicating a tab-delimited file.
  • VOID_CODE is an optional field to define what value in the VOID_CHECK_FLAG field indicates a voided check. This is an alphanumeric field (default value) equal to 'V' on the EXPORT_RECORD or if any value is found on the IMPORT_RECORD it will be converted to a 'V'.
  • RECORD_LENGTH indicates the physical size of each export and/or import record. The length of the export records need not be the same as the import records. The length of these two records can vary depending on the banking institution's specifications. However, if the institution indicates the import and export records are of the same length, the RECORD_LENGTH can be defined in the GLOBAL section of the initialization file. For the export file, the RECORD_LENGTH field is optional. If it is not defined or is omitted, the record size will be determined within the program. This is done using the parameters as they are defined in the EXPORT_SECTION of the initialization file.
    The PAYREC.INI file will need to be changed to record_length=230, if including both the payee name and complete address. 
  • CHECK_NUMBER indicates the number of the check. This is a required field. Maximum value 9(18), 18 digits.
  • CHECK_DATE_MONTH indicates the month portion of the check's date. This field is required.
  • CHECK_DATE_DAY indicates the day portion of the check's date. This is a required field.
  • CHECK_DATE_YEAR indicates the year portion of the check's date. This is a required field.
  • CHECK_DATE is a field used by the delimited files. This field indicates the clearing date of the imported check. Since different banks will submit different date layouts, this field is not validated by PAYREC. It is printed on the report as the clearing date. **The clearing date on the file from the bank, must be formatted in xx/xx/xxxx, in order for the Check Date on the PAYREC.TXT to appear correctly. This will also prompt the Clearing Date on the PAYREC.TXT to show**
  • CHECK_AMOUNT indicates the amount of the check. This field is required and will contain an implied decimal point or an explicit decimal point. By default, CHECK_AMOUNT will be processed with an implicit decimal point. To use the explicit decimal point, the user will need to add EXPLICIT_AMOUNT to the PAYREC.INI file. Please note if using the explicit decimal point, the CHECK_AMOUNT length field must be increased by one to allow for the extra decimal point, but the pic field should not change and should not contain a decimal point.
    If the length is not increased, the sign may not be extracted or the void_code may not be read on the import file (only applies to negative or voided checks).
  • VOID_CHECK_FLAG is an optional field that controls how voided checks should be handled during the import and exporting of records.
    If this field does not appear in the .INI file, then no voided checks will be exported nor appear on the extraction report. If this field is present in the .INI file, then the value defined for VOID_CODE will be placed here when extracting records. If the VOID_CODE flag is not defined, then "V" will be placed in this field on the export records.
    If the VOID_CHECK_FLAG is present in the .INI file, any import record with a value matching the VOID_CODE or "V", will not be reconciled. The check will appear on the error report as having been voided by the bank but not voided on the USPS files. If the value in this field on the import record does not match the VOID_CODE or "V", the check record will still not be reconciled. Instead a void code match error will be generated.
    When extracting checks, if the VOID_CHECK_FLAG is defined as numeric a warning will be issued on the report. This is an informational warning to alert the DAS of a possible mismatch of data. VOID_CHECK_FLAG should be defined as alphanumeric, as it represents the value for VOID_CODE, which is always defined as alphanumeric.

  • PAYEE_NAME is an optional field that will contain the name from the check whether it is from payroll (CHKPRT) or deductions (group or individual from PAYDED). 
  • PAYEE_NAME2 is an optional field that will contain the second name from the deductions check (group or individual from PAYDED).
    • Payroll Checks will be blank
    • Deduction checks will be the 2nd line of Payee name (if there is one) from DEDNAM
    • Vendor checks will be the Vendor Name 2 (if there is one) from VENSCN

  • PAYEE_ADDRESS is an optional field that will contain address line 1, address line 2, city, state, zip + 4.The address will appear in the .SEQ file as:
    • Address 1
    • Address 2
    • City
    • State
    • Zip + 4
  • BANK_ACCOUNT_NO is an optional field. It is possible that a district may have more than one account at a bank. If this is the case, the bank account number that is used with the extract process should be entered here. It will then be placed on each export record. This is not a required field. The account number is entered at the prompt in the PAYREC extraction option. It will then appear on all extracted records.
  • EXPLICIT_AMOUNT is an optional field. No parameters are necessary. If this field is specified in the PAYREC.INI file, the program will process check amounts with an explicit decimal point in the check amount field.
    If the EXPLICIT_AMOUNT is found in the PAYREC.INI file but the check amounts contain implicit decimal points, the program will give the error that the bank amounts do not match as the positions will be off due to the decimal point.
    If the EXPLICIT_AMOUNT is not in the PAYREC.INI file but the check amounts do contain explicit decimal point, the program will give an error that the check amount is not numeric because the period is considered an alpha character. An EXPLICIT_AMOUNT field is not necessary for a delimited file. The program will expect the check amounts imported to have a decimal point. If this field is detected, it will be ignored for a delimited file.

PAYREC.INI Format Examples

Listed below is an example of a PAYREC.INI file with separate export and import sections where data fields being exported are not the same as those being imported.

 
 
!   PAYREC.INI 
! 
SECTION=GLOBAL 
VOID_CODE=V 
RECORD_LENGTH=75 (If using PAYEE_NAME and PAYEE_ADDRESS length must be set to 230) 
! 
SECTION=EXPORT_RECORD 
CHECK_NUMBER=12,9,9(9),NUMERIC 
CHECK_DATE_MONTH=22,2,99,NUMERIC 
CHECK_DATE_DAY=24,2,99,NUMERIC 
CHECK_DATE_YEAR=26,4,9999,NUMERIC 
CHECK_AMOUNT=35,11,S9(8)V99,NUMERIC 
VOID_CHECK_FLAG=48,1,X,ALPHANUMERIC 
BANK_ACCOUNT_NO=50,12,X(12),ALPHANUMERIC (Optional)
PAYEE_NAME=65,45,X,ALPHANUMERIC (Optional)
PAYEE_ADDRESS=115,115,X,ALPHANUMERIC (Optional) 
! 
SECTION=IMPORT_RECORD 
CHECK_NUMBER=15,6,9(6),NUMERIC 
CHECK_DATE_MONTH=22,2,99,NUMERIC 
CHECK_DATE_DAY=24,2,99,NUMERIC 
CHECK_DATE_YEAR=28,2,99,NUMERIC 
CHECK_AMOUNT=37,9,S9(6)V99,NUMERIC 
VOID_CHECK_FLAG=48,1,X,ALPHANUMERIC 
 
 

Listed below is an example of a PAYREC.INI file where the export and import records are the same, so only the GLOBAL section is needed. It is not necessary to duplicate the information in two separate EXPORT_RECORD and IMPORT_RECORD sections.

 
 
!   PAYREC.INI 
! 
SECTION=GLOBAL 
VOID_CODE=V 
RECORD_LENGTH=75 
! 
CHECK_NUMBER=12,9,9(9),NUMERIC 
CHECK_DATE_MONTH=22,2,99,NUMERIC 
CHECK_DATE_DAY=24,2,99,NUMERIC 
CHECK_DATE_YEAR=26,4,9999,NUMERIC 
CHECK_AMOUNT=35,11,S9(8)V99,NUMERIC 
VOID_CHECK_FLAG=48,1,X,ALPHANUMERIC 
BANK_ACCOUNT_NO=50,12,X(12),ALPHANUMERIC (Optional)

Listed below is an example of a Delimited file with numbers after each data element indicating which column the data element is in. Each data field is separated by a Tab.

 
SECTION=IMPORT_RECORD 
FILE_TYPE=DELIMITED 
FILE_FORMAT=TAB 
CHECK_DATE_MONTH=2 
CHECK_DATE_YEAR=4 
CHECK_NUMBER=1 
CHECK_AMOUNT=5 
VOID_CHECK_FLAG=VOID 
 

Listed below is an example of a Delimited_Names file with names after each data element indicating which name the data element is.

 
 
SECTION=IMPORT_RECORD 
CHECK_DATE=Date 
CHECK_NUMBER=Number 
CHECK_AMOUNT=Amount 
VOID_CHECK_FLAG=Voided 
FILE_TYPE=Delimited_Names 
FILE_FORMAT=CSV 
 

logicals

A new logical called OECN$PAY_PAYREC must be defined that will point to the name and location of the PAYREC.INI initialization file. Generally you will need to define a PAYREC.INI file for each bank. This file may be shared by several districts that use the same bank. Examples follow:

 
   $ DEFINE OECN$PAY_PAYREC OECN$PAY:PAYREC.INI 
 
   $ DEFINE OECN$PAY_PAYREC OECN$CUSTOM:PAYREC_BANKA.INI 
 

PAYREC will first look for the import file in OECN$OUT. If the file is not found, it will look in OECN$DTA. If the import file is not found in either OECN$OUT or OECN$DTA, an error will be issued.

messages

Export Message

Message Possible Solution

Void check flag requested as numeric, but flag is alpha_numeric.

Check field value in Export Sequential file for a non-numeric character or change value on .INI file to alphanumeric.

Reconcile Messages

Message Field Value Action

Check amount not numeric

######

The amount showing on the import file for the check is not numeric. Verify field layouts in the .INI file. If the file is correct, contact the bank.

Check voided by bank

Void Code: X

Examine check status on USPS files and verify with the bank.

Void Code and Flag don't match

Void Code: X Void Check Flag: Y

Check the value of the VOID_CHECK_FLAG on the import record against the VOID_CODE value.

Bank check number is invalid

Import value: ######

The check number on the import file from the bank is not on the USPS files. The number on the report should be verified with bank personnel.

Check amounts do not match

USPS: $ BANK: $

Examine check amount on USPS files and amount reported by bank. Contact the bank if necessary.

Check already reconciled

USPS Check Status: R

Examine check status on USPS files.

Check already voided

USPS Check Status: V

Examine check status on USPS files.

Check is a Direct Deposit

USPS Check Status: D

Examine check status on USPS files.

 
  • No labels