Chapter 107: Working with Bank Reconciliation Transactions (WBRC)

Purpose: A Bank Reconciliation Transaction is a transaction that you can send to a bank or receive from a bank that contains refund check information.

You can use bank reconciliation transactions to send refund checks to a bank for automated reconciliation and then receive the processed refund check information back into CWDirect. Once the refund checks are reconciled, the system updates the Refund Reconciliation file.

In this chapter:

• Bank Reconciliation Transaction File (ACBRTF)

• Process Overview

• Work with Bank Reconciliation Transaction Screen

• Display Bank Reconciliation Transaction Screen

• Generating the Bank Reconciliation Transaction Outbound File

• Positive Pay Refund Check Outbound File (CSPPOP)

• Processing the Bank Reconciliation Transaction Inbound File

• Positive Pay Refund Check Inbound File (CSPPIP)

• Processing Unreconciled Bank Reconciliation Transactions

• Purge Bank Reconciliation Records Window

• Bank Reconciliation Transaction List

Bank reconciliation transaction set-up: You must set up this information in Working with Banks (WBNK).

• To send bank reconciliation transactions to a bank, you must define the program name of the outbound file you wish to use in the Outbound (Outbound reconciliation program) field for the bank. The system creates an outbound file when you press F7 on the Work with Bank Reconciliation Transaction Screen. The base program for outbound transactions is ACR0479.

• To receive bank reconciliation transactions from a bank, you must define the program name of the inbound file you wish to use in the Inbound (Inbound reconciliation program) field for the bank. The system receives the inbound file when you press F8 on the Work with Bank Reconciliation Transaction Screen. The base program for inbound transactions is ACX0481.

Related system control value: The Create Bank Interface Record (G12) system control value controls whether the Reconciliation outbound program and Reconciliation inbound program fields display in Work with Banks. In order to send and receive bank reconciliation transactions, you must set this system control value to Y.

This value also controls whether the system creates a bank interface transaction when you enter Y in the Generate refund checks field in Processing Refunds (MREF). When you generate refunds for a bank that is defined with a reconciliation outbound program, the system creates a bank reconciliation transaction in the Bank Reconciliation Transaction file. You can review the bank reconciliation transactions on the Work with Bank Reconciliation Transaction Screen.

Inbound/outbound files: The system uses these files to receive bank reconciliation transactions and to send bank reconciliation transactions:

• the Positive Pay Refund Check Inbound File (CSPPIP) stores bank reconciliation transactions received from the bank. When you press F8 at the Work with Bank Reconciliation Transaction Screen to receive inbound bank reconciliation transactions, the system retrieves the transactions from this file. Records received from the bank must be formatted as described in the Positive Pay Refund Check Inbound File (CSPPIP) table.

• the Positive Pay Refund Check Outbound File (CSPPOP) stores bank reconciliation transactions to be sent to the bank. The system adds a record to this file for each refund check you process in Process Refunds. When you press F7 at the Work with Bank Reconciliation Transaction Screen to generate the outbound bank reconciliation transactions, the system sends the transactions stored in this file.

The Work with Bank Reconciliation Transaction Screen displays bank reconciliation transactions created through Process Refunds, and bank reconciliation transactions received through the Positive Pay Refund Check Inbound File (CSPPIP).

Once you create an outbound bank reconciliation transaction by generating refund checks in Process Refunds or receiving inbound bank reconciliation transactions by pressing F8, the system stores the transactions in the Bank Reconciliation Transaction File (ACBRTF).

Bank Reconciliation Transaction File (ACBRTF)

Field name	Attributes	Description
Company	Numeric, 3 positions	The company for which you are sending and receiving bank reconciliation transactions. For inbound transactions, the system uses the first 3 positions of the check number to represent the company number.
Bank #	Alphanumeric, 3 positions	The bank number that issues the refund checks.
Check #	Numeric, 7 positions	The refund check number.
Transaction type	Alphanumeric, 1 position	Indicates the type of transaction. I = Inbound transaction O = Outbound transaction
Date sent	Numeric, 7 positions (CYYMMDD format)	The date the outbound transaction was created.
Date received	Numeric, 7 positions (CYYMMDD format)	The date the inbound transaction was uploaded to the Bank Reconciliation Transaction file.
Date reconciled	Numeric, 7 positions (CYYMMDD format)	The date the inbound transaction was reconciled.
Amount	Numeric, 9 positions with a 2-place decimal	The refund check amount.
Date paid	Numeric, 7 positions (CYYMMDD format)	The date the inbound transaction was paid.
Date issued	Numeric, 7 positions (CYYMMDD format)	The date the check was printed.
Account #	Numeric, 12 positions	Your account number with the bank.
Status	Alphanumeric, 1 position	Identifies the status of the transaction. E = Transaction in error. O = Transaction open P = Transaction processed
Error reason	Alphanumeric, 3 positions	The reason why the transaction is in error.
Order #	Numeric, 8 positions	The order number associated with the bank transaction.
Ship to #	Numeric, 3 positions	The ship to number associated with the bank transaction.

Process Overview

Work with Bank Reconciliation Transaction Screen

How to display this screen: Enter WBRC in the Fast path field at the top of any menu, or select Work with Bank Reconciliation Transaction from a menu.

ACR0476 DISPLAY Work with Bank Reconciliation Transaction 7/06/99 16:20:19 KAL Co. Opt Bnk Check# Type Dt Iss Dt Sent Dt Rcvd Amount Recon Status Type options, press Enter. 5=Display KAL 54 O 7/06/99 7/06/99 3.40 P KAL 55 O 7/06/99 7.35 O KAL 56 O 7/06/99 7.35 O KAL 77 I 7/06/99 35.49 7/06/99 P NSL 78 I 7/06/99 78.65 7/06/99 P NSL 79 O 7/06/99 7/06/99 15.00 E F3=Exit F7=Generate outbound F8=Process Inbound F9=Process unreconciled F11=Purge F12=Cancel F21=Print list F24=Select company

Field	Description
Bnk (Bank code)	A code that represents a bank. Bank codes are defined in and validated against the Bank file. See Accounts Payable Chapter 6: Working with Banks (WBNK). Alphanumeric, 3 positions; optional.
Check#	The number assigned to the refund check. Numeric, 7 positions; optional.
Type (Transaction type)	A code that indicates the type of bank reconciliation transaction. Valid values are: I = This is an inbound bank reconciliation transaction. Inbound bank reconciliation transactions contain processed refund check information from the bank. O = This is an outbound bank reconciliation transaction. Outbound bank reconciliation transactions contain refund check information to send to the bank. Alphanumeric, 1 position; optional.
Dt iss (Date issued)	The date the refund check was printed. Numeric, 6 positions (MMDDYY format); optional.
Dt sent (Date sent)	The date the outbound bank reconciliation transaction was created. Numeric, 6 positions (MMDDYY format); optional.
Dt rcvd (Date received)	The date the inbound transaction was uploaded from the Positive Pay Refund Check Inbound File (CSPPIP). Numeric, 6 positions (MMDDYY format); optional.
Amount	The amount of the refund check. Numeric, 9 positions with a 2-place decimal; display-only.
Recon (Date reconciled)	The date the refund check was reconciled. Numeric, 6 positions (MMDDYY format); optional.
Status (Transaction status)	The status of the bank reconciliation transaction. Valid values are: E = The bank reconciliation transaction contains errors. O = The bank reconciliation transaction is open. P = The bank reconciliation transaction has been processed. The system changes a bank reconciliation transaction to a P status once the transaction has been reconciled. Note: If you unreconcile a check in Reconciling Checks (MREC), the system does not update the inbound transaction to an unreconciled status. You will need to purge the transaction and reprocess it. Alphanumeric, 1 positions; optional.

Screen Option	Procedure
Display a bank reconciliation transaction	Enter 5 next to a bank reconciliation transaction to advance to the Display Bank Reconciliation Transaction Screen.
Generate the Bank Reconciliation Transaction Outbound file	Press F7. See Generating the Bank Reconciliation Transaction Outbound File.
Process the Bank Reconciliation Transaction Inbound file	Press F8. See Processing the Bank Reconciliation Transaction Inbound File.
Process unreconciled bank reconciliation transactions	Press F9. See Processing Unreconciled Bank Reconciliation Transactions.
Purge bank reconciliation transactions	Press F11. See Purge Bank Reconciliation Records Window.

Display Bank Reconciliation Transaction Screen

Purpose: Use this screen to review for a bank reconciliation transaction:

• if an outbound transaction was issued and sent to the bank

• if an inbound transaction was received, reconciled, and paid

• the order number and ship to number associated with the transaction

• the bank associated with the transaction

• your account number with the bank

• whether the transaction is open, processed, or in error

How to display this screen: Enter 5 next to a bank reconciliation transaction on the Work with Bank Reconciliation Transaction Screen.

ACR0477 DISPLAY Display Bank Reconciliation Transaction 7/07/99 11:12:30 KAL Co. Check # . . . . . .: 59 Amount . . . . . .: 39.00 Type . . . . . . ..: O Date Issued . . . .: 7/07/99 Date Sent . . . . .: 7/07/99 Date Received . . .: Date Reconciled . .: Date Paid . . . . .: Order # . . . . . .: 3769 - 1 Bank. . . . . . . .: KAL COOPERATIVE BANK Account # . . . . : 555 Status . . . . . . : P Processed F3=Exit F12=Cancel

Field	Description
Check#	The number assigned to the refund check. Numeric, 7 positions; display-only.
Amount	The amount of the refund check. Numeric, 9 positions with a 2-place decimal; display-only.
Type (Transaction type)	A code that indicates the type of bank reconciliation transaction. Valid values are: I = This is an inbound bank reconciliation transaction. Inbound bank reconciliation transactions contain processed refund check information from the bank. O = This is an outbound bank reconciliation transaction. Outbound bank reconciliation transactions contain refund check information to send to the bank. Alphanumeric, 1 position; display-only.
Date issued	The date the refund check was printed. Numeric, 6 positions (MMDDYY format); display-only.
Date sent	The date the outbound bank reconciliation transaction was created. Numeric, 6 positions (MMDDYY format); display-only.
Date received	The date the inbound transaction was uploaded from the Positive Pay Refund Check Inbound File (CSPPIP). Numeric, 6 positions (MMDDYY format); display-only.
Date reconciled	The date the refund check was processed for reconciliation. Numeric, 6 positions (MMDDYY format); display-only.
Date paid	The date the refund check was paid. Numeric, 6 positions (MMDDYY format); display-only.
Order # (Order number and ship to number)	The order number and ship to number associated with the refund. Order number: Numeric, 8 positions; display-only. Ship to number: Numeric, 3 positions; display-only.
Bank (Bank code)	A code that represents a bank. Bank codes are defined in and validated against the Bank file. See Accounts Payable Chapter 6: Working with Banks (WBNK). Alphanumeric, 3 positions; display-only.
Account #	Your account number with the bank. Numeric, 12 positions; display-only.
Status (Transaction status)	The status of the bank reconciliation transaction. Valid values are: E = The bank reconciliation transaction contains errors. O = The bank reconciliation transaction is open. P = The bank reconciliation transaction has been processed. The system changes a bank reconciliation transaction to a P status once the transaction has been reconciled. Alphanumeric, 1 positions; display-only.

Generating the Bank Reconciliation Transaction Outbound File

Purpose: Use this pop-up window to specify the bank for which you wish to send bank reconciliation transactions.

When you generate outbound transactions, the system sends O (outbound) bank reconciliation transactions that the system created when you generated checks in Processing Refunds (MREF) that are in an open status and do not have a sent date.

Note: If you void a check in Reconcile Checks that is associated with an outbound transaction that is available to send to the bank, the system does not remove the outbound transaction and you can still send the transaction to the bank.

How to display this window: Press F7 on the Work with Bank Reconciliation Transaction Screen.

Enter Bank # F3=Exit F9=Accept F12=Cancel

Field	Description
Enter bank #	The code for the bank to which you wish to send bank reconciliation transactions. A message displays if you enter a bank code that does not have an outbound program defined:   No outbound program defined for bank.   Alphanumeric, 3 positions; required.

When you press F9 to generate the Outbound file, the system creates the Positive Pay Refund Check Outbound File (CSPPOP), which contains a record for each outbound bank reconciliation transaction, and generates the Bank Reconciliation Transaction List.

Positive Pay Refund Check Outbound File (CSPPOP)

Detail
Field Name	Attributes	Positions	CWDirect Field Name	Description
Record ID	Numeric, 2 positions	1-2		01 = Detail 02 = Trailer
Account number	Numeric, 12 positions	3-14	Account #	The account number associated with the bank.
Check #	Numeric, 10 positions	15-24	Check #	The first 3 positions of the check number represents the company number. The last 7 positions represents the check number that prints on the check.
Check amount	Numeric, 12 positions	25-36	Amount	The refund check amount.
Issue Date	Numeric, 8 positions	37-44	Date Issued	The date printed from the Refund Reconciliation file.
Voided chk	Numeric, 1 position	45.000		V = Voided O = Original This field is always blank.
Additional date	Numeric, 15 positions	46-60		Zero-filled
Filler	Numeric, 20 positions	61-80		Zero-filled

Trailer
Field Name	Attributes	Positions	CWDirect Field Name	Description
Record ID	Numeric, 2 positions	1-2		01 = Detail 02 = Trailer
Account number	Numeric, 12 positions	3-14	Account #	The account number associated with the bank.
Total # records	Numeric, 10 positions	15-24		The total number of records being transmitted to the bank.
Total amount	Numeric, 12 positions	25-36		The total dollar amount of the checks included in this transmission.
Filler	Numeric, 20 positions	37-80		Zero-filled

Processing the Bank Reconciliation Transaction Inbound File

Purpose: Use this window to specify the bank for which you wish to receive bank reconciliation transactions.

When you process the Inbound file, the system tries to match each inbound transaction with refund check information in the Bank Reconciliation Transaction File (ACBRTF). If a match is found, the inbound transaction is processed; if a match is not found, the inbound transaction is placed in an error status.

How to display this window: Press F8 on the Work with Bank Reconciliation Transaction Screen.

Enter Bank # F3=Exit F9=Accept F12=Cancel

Field	Description
Enter bank #	The code for the bank to which you wish to receive bank reconciliation transactions. A message displays if you enter a bank code that does not have an inbound program defined:   No inbound program defined for bank.   Alphanumeric, 3 positions; required.

When you press F9 to generate the Inbound file, the system:

• creates the Positive Pay Refund Check Inbound File (CSPPIP), which contains a record for each inbound bank reconciliation transaction.

• generates the Bank Reconciliation Transaction List. This report displays each inbound transaction that contains errors.

Positive Pay Refund Check Inbound File (CSPPIP)

Field Name	Attributes	Positions	CWDirect Field Name	Description
Record ID	Numeric, 2 positions	1-2		Informational only.
Account number	Numeric, 12 positions	3-14	Account #	Your account number with the bank.
Check #	Numeric, 10 positions	15-24	Check #	The first 3 positions of the check number represents the company number. The last 7 positions represents the check number that prints on the check.
Check amount	Numeric, 12 positions	25-36	Amount	The refund check amount.
Paid Date	Numeric, 6 positions	37-42	Date Paid	The date the bank paid the check.
Item sequence #	Numeric, 8 position	43-50		Informational only.
Record type	Alphanumeric, 1 position	51.000		3 always displays in this field.
Filler	Alphanumeric, 29 positions	52-80

Processing Unreconciled Bank Reconciliation Transactions

To process unreconciled checks: Press F9 at the Work with Bank Reconciliation Transaction Screen to process bank reconciliation transactions that have not yet been reconciled.

To reconcile an inbound transaction, the system looks for corresponding refund check information in the Bank Reconciliation Transaction file, using:

• Check number

• Dollar amount

• Blank reconciliation date

If the system finds an exact match:

• the inbound transaction is reconciled

• the system writes a record to the Bank Reconciliation Transaction File (ACBRTF)

• the system writes an order transaction history message indicating the transaction has been reconciled.

If the system cannot find an exact match:

• the inbound transaction is not reconciled and is placed in an error status.

• the inbound transaction that is in error must be purged

• the system generates the Bank Reconciliation Transaction List indicating the transactions in error.

The system only processes inbound bank reconciliation transactions that are in an O (open) status and that do not have a date defined in the Date reconciled field in the Bank Reconciliation Transaction File (ACBRTF).

Edits performed: To reconcile a bank reconciliation transaction, the system looks at the Bank Reconciliation Transaction File (ACBRTF) to determine if a matching bank reconciliation transaction exists:

• the system validates that a matching check number exists. If a matching check number does not exist, the system assigns the bank reconciliation transaction an error status with the error description: No matching record.

• the system validates that a matching check amount exists. If a matching check amount does not exist, the system assigns the bank reconciliation transaction an error status with the error description: Check amount does not match.

• the system validates that the check has not been voided. If the check has already been voided, the system assigns the bank reconciliation transaction an error status with the error description:
Check Voided.

• the system validates that a reconcilement date does not exist. If a date exists in the Date reconciled field in the Refund Reconciliation file, and/or the Bank Reconciliation Transaction file, the system assigns the bank reconciliation transaction an error status with the error description: Already reconciled.

If the bank reconciliation transaction passes all of the edits above, the system reconciles the transaction and assigns the transaction a P (processed) status. For each transaction that has been reconciled, the system writes an order transaction history message.

Note: The system does not write an order transaction history message when you reconcile a check through Reconcile Checks.

If a bank reconciliation transaction fails the edits above, the system lists the transaction on the Bank Reconciliation Transaction List.

Purge Bank Reconciliation Records Window

Purpose: Use this window to select bank reconciliation transactions you wish to purge. You can purge transactions that are in a P (processed) or E (error) status.

How to display this window: Press F11 on the Work with Bank Reconciliation Transaction Screen.

Purge Bank Reconciliation Records Record Type To Purge (I or O) Date Purge Error Records (Y/N) F3=Exit

Field	Description
Record type to purge	The bank reconciliation transaction type you wish to purge. Valid values are: I = Purge inbound bank reconciliation transactions. O = Purge outbound bank reconciliation transactions. Alphanumeric, 1 positions; required.
Date	The date you wish to use to purge bank reconciliation transactions. For inbound transactions, the system purges transactions with a reconciled date earlier or equal to the date you define. For outbound transactions, the system purges transactions with a sent date earlier or equal to the date you define. Numeric, 6 positions (MMDDYY format); required.
Purge error records	This field indicates whether you wish to purge bank reconciliation transactions that are in an error status. Valid values are: Y = Purge transactions that are in an error status. N = Do not purge transactions that are in an error status. An error message displays if you enter Y in this field and you have selected to purge outbound transactions:   Record Type O not valid with Purge Error Y.   Alphanumeric, 1 position; required.