PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 01/23/2017 12:30 PM by  harold_c
CB Mass Reconciliation upload template
 2 Replies
Sort:
You are not authorized to post a reply.
Author Messages
harold_c
accountant
Private
Basic Member
(19 points)
Basic Member
Posts:9


Send Message:

--
01/23/2017 10:50 AM
    Hi,

    I am trying to create a CB reconciliation upload using form CB80 for the mass reconciliation I am currently doing via CB185, but encounters errors after the 1st line is successfully uploaded, and cannot figure out why, error is: This form action valid on open transactions only (_f51r0).

    Does anybody have a template/process they would like to share? How do you do your reconciliation?

    Thanks.
    Basia
    Analyst
    CHOC
    Basic Member
    (22 points)
    Basic Member
    Posts:10


    Send Message:

    --
    01/23/2017 11:02 AM
    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
    harold_c
    accountant
    Private
    Basic Member
    (19 points)
    Basic Member
    Posts:9


    Send Message:

    --
    01/23/2017 12:30 PM
    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.
    You are not authorized to post a reply.