This blog is part 2 series of Advance Bank Statement Import – BAI2 format. In Part 1 we covered Setup, in this blog we will cover Import and processing of transactions.

Bank import type codes

When importing bank transactions using the BAI2 file format, the import process identifies whether a transaction code is a credit or a debit by referencing the type codes. The type codes are included in the Account identifier record (Record type 03) and Transaction detail (Record type 16) of the BAI2 file. If a code is not defined with the appropriate sign, the bank statement file may not be imported and produce error in the import log file.

In X3 you can create multiple sets of Bank type codes, either one for all banks or one per bank. Each bank will have their own Type codes so you may want to verify with the bank to ensure you are using the correct type codes.

For demonstration and simplicity, I will be creating the following Bank type codes for my “AU1AU” bank setup detailed in my previous blog in Step 6 – Bank import setting.

Note:

1. For this demonstration I will use default Bank import segment definition, you may need to verify with the bank to include all necessary fields.
2. I have included just 2 credit transactions type codes (Customer receipts & Income) for my demonstration, you need to include all the other Credit and Debit (Supplier payment & expense) codes as specified by the bank.

Bank import file:

 For this demonstration I will be including these 3 transactions. (All credit)

• Entry 1:  Customer receipt: Amount $2,200 received against Invoice – Automatic matching.
• Entry 2:  Customer advance payment $1,100 – We will perform manual matching against Customer Invoice.
• Entry 3: Interest received $50 (not recorded in X3) – we will manually entry to record this income transaction.

01,CBACAU2DK,CBACUST,230721,1301,01,80,2/
02,CBACUST,CBACAU2DK,1,230721,1301,,2/
03,083945516082203,AUD,010,0,,,015,1200000,,,045,1200000,,,400,2800000,4,,100/
88,010,251497669,,Z/
16,142,220000,Z,2003412,INCOMMING WIRE TRANSFER/
88,SIN2307AU01200023 AU001:Bike World Pty Ltd
16,142,110000,Z,1413245,AU002,INCOMMING WIRE TRANSFER/
16,169,5000,Z,82012177220,,BANK INTEREST/
49,1006609721,61/
98,187016410625,21,1594/
99,190724032504,4,2042/

My BAI2 import file:

01,CBACAU2DK,CBACUST,230721,1301,01,80,2/

02,CBACUST,CBACAU2DK,1,230721,1301,,2/

03,083945516082203,AUD,010,0,,,015,1200000,,,045,1200000,,,400,2800000,4,,100/

88,010,251497669,,Z/

16,142,220000,Z,2003412,INCOMMING WIRE TRANSFER/

88,SIN2307AU01200023 AU001:Bike World Pty Ltd

16,142,110000,Z,1413245,AU002,INCOMMING WIRE TRANSFER/

16,169,5000,Z,82012177220,,BANK INTEREST/

49,1006609721,61/

98,187016410625,21,1594/

99,190724032504,4,2042/

Refer to our online help Appendix III: BAI2 default settings for more information regarding BAI2 file format definition.

Note: This bank statement import file is for demonstration only in its simplest form.

Step 1: Import Statement

• Open function Bank transaction import (BSIIMPORT)
• Select your bank Import setting.
• Select your bank.

• Select your file.
• Select OK to process the import file, verify Import log is generated.

Note:

The warning is due to the fact I have not included all Type codes in my bank type code “CANBAI2” setup, we can ignore them at this stage.

Step 2: Process Bank Transaction

To process the transaction in the imported statement and perform matching we run the following:

• Open function Bank transaction management (BSIIMPJ)
• Select action item for the newly imported import statement file.
• Select action item Process bank transaction.

• Select Match transaction Button.

System process transactions in the import file and automatically matches transaction based on details in the file and Invoice number definition and/or BP number definition.

Based on my file

• Entry 1 – Customer receipt of $2200 is automatically matched since “SIN2307AU01200023 AU001: Bike World Pty Ltd” is referenced in the import file.
• Entry 2 and Entry 3 is not matched since there is no reference in the import file.

Step 3: Process Manual Matching

We will manually match the advance payment of $1100 against Customer invoice.

• Select the payment from the left list.
• Select Match transaction.

• Select open item.

• In Manual Selection windows entering amount $1100 and BP number as AU002

• Select the sales invoice from open item list.
• Save the manual matching.

Step 4: Manual Entry

This step explains how to manually enter any entry which is appearing in the statement but not recorded in X3. For instance, Interest received from bank.

• Select the record from left list.
• Click Match Transaction Button
• Select Manual entry from right panel.

• In Manual entry window enter all relevant details
• Select Ok to record the transaction.

Step 5: Validating the transactions.

The final step in the process is to validate transactions to generate payments for matches to open items and to create a link to existing payments for matches to payments.

• Verify if all the transactions have been updated with Letter in Code and Matching Type column in left list. Details of the Code and Matching Type available in our Online help.
• If you discover any error, you could select Undo matching from right panel to rectify the error.
• Once you have verified click Validation button to complete the transaction processing.

• Verify the Log File created for payment generation.