CB Mass Reconciliation upload template

Hello,
You will need to perform an upload to CB25 instead of CB80. Once you upload the transaction to CB25, you will need to run CB185 to process all of the transactions in CB25, the Run Option should be set to D.

You can automate this process with the help of your bank and IT team. The following article can be found on Infor's website:

Description:
How do I reconcile my bank transactions with CB185 (Bank Tape Mass Reconciliation)?
Resolution:
Before you can reconcile transactions from a bank tape using CB185, you must load data from the tape supplied by your bank to the CB185 tape work file.

The following table identifies each field in the CB185 tape work file and describes the type of information each field should contain. Note: This information can also be found in Work File Definition. To access Work File Definition, type workdef at the UNIX prompt.

The CB185 tape work file is 71 characters long per record, and must be structured as a flat file (sequential - with no return characters) instead of line sequential (with return characters). The file must be named CB185-TAPE, and should be placed at the following location: $LAWDIR/(productline)/work/CB185-TAPE .

Field Name

Type

Size

Description

BANK-ID

Alpha

15

Bank identification number. (CB00.2)

BANK-ACCT-NBR

Alpha

17

Bank account number. (CB00.2)

RECORD-TYPE

Alpha

1

Valid values are:

Blank = Cash Ledger transaction record

B = Beginning period balance record. If present, the system verifies this value to make sure that it equals the ending balance of the previous period.

BANK-INST-CODE

Alpha

3

Bank transaction code. Optional. See below for additional information under the heading "Options for Running the CB185 Parameters." (CB00.4)

TRANS-NBR

Alpha

10

Payment number.

RECON-BNK-AMT

Numeric

15.2

Reconciled payment amount. Do not put a decimal in the file. (Fifteen characters, two of them are assumed to be to the right of the decimal.)

AMT-SIGN

Alpha

1

Sign. Valid values are:

+ Lawson debit (Check)

- Lawson credit (Deposit)

Blank same as +

RECON-DATE

ccyymmdd

8

Reconciliation date, for example 20021231.

STATUS

Alpha

1

Valid values are:

Blank = Program will set this field to "R".

R = Regular payment to be reconciled. (The file is easier to read if you use the "R" on the file instead of letting it default from blank.)

V = Voided payment


Tips for Creating the Sequential File

Your bank will send you a tape file that will not match Lawson’s expected format. In addition, there is information that Lawson requires that will not be supplied by your bank. You must take the information that the bank provides and re-format it to match the file layout shown above. See the following line sequential file (There are two lines and there is an assumed carriage return following the ‘R’ at the end of the record). The file needs to be named anything other than CB185-TAPE. (In this example, my file is named TEST). Blanks in the file are represented by the ^ character.

123456789ABCDEF123456789ABCDEFGH^CHK1234567898123456789876543^20020101R

123456789ABCDEF123456789ABCDEFGH^MAN1234567899987654321012345^20020102R

The bank id for this file is:

123456789ABCDEF

15 characters

The bank account number for this file is:

123456789ABCDEFGH

17 characters

Record type is blank:

^

1 character

First Check

Second Check

Bank-inst-code (Bank Transaction Code):

CHK

MAN

3 characters

Transaction number (check number):

1234567898

1234567899

10 characters

Reconciled amount:

123456789876543

987654321012345

15 characters

Amount sign is blank:

^

^

1 character

Reconciliation date:

20020101

20020102

8 characters

Status:

R

R

1 character


Once you create a file that looks like the file layout above, you must convert the data into a sequential file that contains no carriage or line returns. You can use Lawson's cnvimp command to do this. Here is the usage/syntax for Lawson's cnvimp command:

Usage: To convert an import line sequential file to a sequential file

Syntax: cnvimp ImportFile RecordSize

From the UNIX prompt: cnvimp^TEST^71^>^CB185-TAPE

Blanks in the file are represented by the ^ character.

TEST is the name of the line sequential file.

71 is the number of characters in each line.

> is the symbol to redirect the output to a file.

CB185-TAPE is the new sequential file.



Additional Information:
This section provides information on common errors users receive while running CB185.

Note: These errors are very similar. If you have a job end abnormally, you will probably find one of these messages in the job's Examine Log. These errors are the result of an incorrectly formatted CB185-TAPE file. The "Lawson Translation" section below lists common reasons for these errors.

Error #1: "Bad File Status 9 018 on file CB185-TAPE"

This error is a Micro Focus COBOL run-time system message that reads as follows:

Read part record error: EOF before EOR or file open in wrong mode (recoverable).

This error means that a partial record has been found at the end of a file. Your run-time system will treat the data file as a record, will not be able to find a full record, and will deliver this error message. To resolve the error, make sure the record size you give when you read from or WRITE to a file is consistent.

Error #2: "Bad File Status 3 0 on file CB185-TAPE"

This error is a Micro Focus COBOL file status code that reads as follows:

The input-output statement was unsuccessfully executed as the result of a boundary violation for a sequential file, or as the result of an input-output error, such as a data check parity error, or a transmission error.

Error #3: Program Messages

Processing CB185 - Bank Tape-Mass Reconciliation

Work File Distribution Path ///work

File /CBADJUST

StoreDBRec error is Bad data in field(s) (82).

Additional information is 2

Error: Stopped On Exit 1.

Lawson Translation

As you are working with CB185 work files, make sure they are line sequential. You can use Lawson's cnvimp utility to ensure that your files are line sequential. (More information on cnvimp is available earlier in this document.)

This section contains some common errors, along with the causes/solutions for these errors.

Error: The ImportFile name and the NewFile name can not be the same file name

This error is caused by incorrect formatting. Check the data, field justification, and length of each record, removing any carriage returns.

Tip: There is a quick way to find out if you have the correct number of characters/spaces in your CB185-TAPE file. If there are return characters, divide the byte size of the file by 72 to determine how many records are in the file. If it is not a whole number, the layout is incorrect. (This is also the number of checks in the reconciliation file. If there are no return characters, divide the byte size by 71. If it is not a whole number, the layout is incorrect.)

You can re-populate your file with carriage returns to make it easier to examine for errors. To re-populate your carriage returns, enter the cnvexp utility at the UNIX prompt. Here is the usage/syntax information on cnvexp:

Usage: To convert an export sequential file to have return characters.

Syntax: cnvexp -f ExportFile RecordSize > NewFile

f - Output fixed length records.

Example: cnvexp -f CB185-TAPE 71 > tapefile

Error: Bad File Status 3 5 on file CB185-TAPE

This error is a Micro Focus COBOL file status code that reads: An OPEN statement with the I_O, INPUT, or EXTEND phrases has been attempted on a non-OPTIONAL file that is not present .

Lawson Translation: The program cannot locate the CB185-TAPE file. The file needs to be placed at $LAWDIR/(productline)/work/CB185-TAPE with a file name of CB185-TAPE .

Error: Bad File Status 3 7 on file CB185-TAPE

This error is a Micro Focus COBOL file status code that reads as follows: An OPEN statement has been attempted on a file which does not support the open mode specified in the OPEN statement .

Lawson Translation:

This is a UNIX permissions error. Give the file CB185-TAPE rwxrwxrwx (chmod 777), making sure the user running CB185 is in the same UNIX user group as the person that created the CB185-TAPE file.

This is an example of a normal file's display when you type ls -l at the UNIX prompt:

-rw-rw---- 1 fred cbstaff 0 Feb 02 12:01 CB185-TAPE

The user is ‘fred’, ‘cbstaff’ is the UNIX user group that the creator/owner of the file

belongs to. The user running CB185 must also belong to the UNIX user group ‘cbstaff’.

Running CB185:

CB185 lets you reconcile transactions from a bank tape by selecting T (Tape) in the Run Option field. The program uses the bank account and transaction numbers from the bank tape and updates corresponding transactions with a reconciled status, amount, and date. This program creates a listing of reconciled transactions and an error report listing transactions that were not reconciled. If the reconciled amount of a transaction is higher or lower than the transaction amount beyond the accepted tolerance defined in CB00.3 (Company-Cash Code Relationships), CB185 creates an adjustment record for the difference.

Use CB30.1 (Reconciliation Adjustment) to adjust and release the bank transaction. The transaction will not be reconciled and will not update the bank account balance until you adjust and release the transaction in CB30.1. If the CB30.1 record is in error, you can delete the transaction in CB30.1 and manually reconcile the item in CB80. For more information, see "Adjusting Unreconciled Bank Transactions" in your Cash Ledger Procedure Manual .

If BANK-INST-CODE is filled in on the CB185-TAPE file, run the following parameters:

Run Option:

T

Cash Code:

Blank

Transaction Numbers:

Blank – Blank

Transaction Code:

Blank

Reconcile or Void:

Blank

Transaction Date:

Blank

Default Transaction Code:

Blank

Update:

N (for report only), then Y for updating


By filling in the Default Transaction Code in the parameters, the BANK-INST-CODE does not

need to be filled in on the CB185-TAPE file. Run the following parameter with each of the

Transaction Codes established. This option is available so that you do not have to know the Transaction Code associated with individual payments. You would run the first time for ‘SYS’ which might be your Transaction Code associated with system payments. Any items that did not find a match will show on your error report and remain in the CB185-TAPE file. You can then run the program for ‘MAN’ which might be your Transaction Code associated with manual payments. Any items that did

not find a match will show on your error report, and remain in the CB185-TAPE file. You would repeat this process for each Transaction Code associated with this bank account.

Run Option:

T

Cash Code:

Blank

Transaction Numbers:

Blank – Blank

Transaction Code:

Blank

Reconcile or Void:

Blank

Transaction Date:

Blank

Default Transaction Code:

(Your transaction code)

Update:

N (for report only), then Y for updating

Thank you very much Basia.

This is very helpful. initial testing for CB25 works. I will prepare the whole file and it looks like it should work. As of this moment, I will be using the CB25 as you suggested. I will explore the tape option after I get this going.