Bank import segment definition

Bank statement import setup overview

This provides an overview of the setup procedures necessary to perform advanced bank statement imports, which includes matching statements to open items and generating payments. You can also match to existing payments without generating a new payment. Note that you cannot match a single transaction to an open item and a payment combination. See Bank statement import overview.

This involves technical settings that should be done very carefully. If you are not certain, contact your Sage X3 administrator or business partner.

View the online help for each function for specifics on data entry and complete function help.

BSI - Bank statement import activity code
You need to activate this activity code to take advantage of the bank statement import process.

Understanding electronic bank formats
To set up the bank statement import successfully, at least a basic understanding about the structure and content of electronic bank statement files is required. This includes MT940, CAMT.053, BAI2, CFONB, and AR Lockbox CSV file types.

Note: Most banks provide CAMT messages in ZIP files. You need to unzip the files before importing into Sage X3.

Payment entry types for bank statement imports
It is strongly recommended to use dedicated payment entry types for the bank statement import (BSI) so that settings can be made independent from other entry types. A dedicated BSI entry type is available as part of the default setup that you can copy or adapt. Dedicated BAI2 and CFONB entry types are also available.

Bank import segment definition
The segment definition describes the detailed structure of the information contained in a segment. This includes segments from the logical parts, like the header, data line, footer, etc., of a bank import format. Based on this, the import extracts and interprets the information contained in the file. The sequence of the segments is controlled by the import format definition.

Note: If you are importing BAI2 files, you need to define Bank import type codes (BSIIMPTC). This is an additional step dedicated to this format.

Bank import format
The format definition describes the overall structure of the format and the sequence or occurrence of the segments. It also controls when new entries for statements and transactions are created during the import.

At least one Bank import format must be defined; it can be used for several bank accounts. You can also have several formats, (i.e., for bank accounts from different banks using different MT940 or CAMT.053 variants).

BP number and Invoice number definitions
The BP number and the invoice number are the main criteria for automatic matching, but are typically given in the bank statement file in a non-structured way as part of the payment reason or information text.

For an efficient search, you need to provide information about the structure of these numbers so the search process can recognize parts of the payment reason text as a BP or invoice number.

Bank import settings
These settings control various aspects of the import and the matching processes. Settings are done at the company level at a minimum, but you can have different settings per company for different bank accounts and/or file formats. For CFONB file formats, a specific bank is not entered.

These settings are applied in the context of an import, so if you import a bank statement file, you need to select a group of settings for this import. These settings are used for the import and the matching process later. For example, you can import and match transactions through the advanced bank statement import workflow or import files and then reconcile using the Bank statement reconciliation (RAPBAN) function.

If you want to included existing payments in the import and matching process, you need to select at least one option in the Payments block of the Search criteria section.

Note: For CFONB file formats, you cannot reconcile using the Bank statement reconciliation function for matching.

Excluded search terms
You can define common terms that are excluded from the business partner name search. Common search terms can include the abbreviations of legal forms that are used in BP names like Ltd, S.A., Inc., and GmbH AG. Salutations like Mister, Miss, Mrs., Herr, and Frau that are used in BP names can also be excluded.

Excluding these common terms that are found in many business partner records increases search performance because it reduces the number of possible BP accounts that need to be searched for open items.

Search term list
Search terms can be used if fixed identifications (like a BP name or a certain text given in the transaction) should be used to match the transaction to a certain G/L or BP account.

Understanding electronic bank statement formats

Advanced bank statement import supports different electronic bank statement formats. It is important to understand the formats relevant to your situation.

Note: Always confirm file format details with the bank to avoid problems when importing and matching. Standard settings and format might not apply to all variants and/or banks.

The current formats are supported:

• MT940
• AR Lockbox
• CAMT
• BAI2
• CFONB
• CSV

MT940

An MT940 bank statement contains several segments separated by a colon (:). Depending on the segment type, it can appear once per statement or it can be repeated.

The image below is an example of a single statement with two transactions. A file can contain several statements.

Information can be structured as follows:

Red numbers indicate the segment. 

:20:, :25: header information of the statement

:60:, :62: balance information

:61: transaction date/amount

:86: payment reference

Blue/yellow text indicates different fields identified by field length and position within a segment.

160810 length

0810 position

Green text is a field identifier that indicates the start of a subfield. This type of field is used in the payment reference.

?00

Note that unstructured variants can also exist where information is separated by keywords, not by subfields.

AR Lockbox

AR Lockbox is used in North America (NA) for importing bank files. You need to use the CSV file type when importing AR Lockbox bank statements.

Refer to the "Advanced bank statement import - Setup and workflow" guide on the How-to tab for details on how to define the AR Lockbox file structure.

CAMT

CAMT element mapping describes a set of XML tags/elements in a CAMT statement file that is extracted during the bank statement import process. There are two predefined standard CAMT types:

CAMT.053

CAMT.054

Note: These are defined in the CAMT element mapping function. Contact your Sage X3 administrator or business partner if you need additional CAMT types.

Importing CAMT files

You can import XML messages for bank to customer statements and process CAMT.053 account statements and CAMT.054 batch transaction notifications in ISO 20022 XML format. 

Importing XML messages for bank to customer statements is a two-step, automated process. In the first step, a CAMT file is processed into a CSV file by means of the CAMT element mapping structure. In the second step, the CSV file is interpreted according to segment definitions and the Sage X3 import records are created.

The CAMT element mapping screen provides a predefined list of XML elements in a hierarchical structure. Elements like message identification, opening and ending balances, transaction amount and currency, sign, transaction reference, and sender name are registered in the element mapping function. They are ordered by levels, which are defined by their position in the CAMT statement structure.

Note: When retrieving CAMT messages, most banks provide the XML messages in a ZIP file. You need to unzip the files to import into Sage X3.

Understanding the BAI2 file format

The BAI2 file is a standard bank statement format developed by the Bank Administration Institute (BAI) and used for electronic cash management balance reporting. It is mostly used in the United Kingdom and the United States.

A BAI2 file only contains multiple record types in a specific structure. Each file contains one File header (type 01) and one File trailer (type 99), but can contain multiple groups. Each group has a Group header (type 02) and a Group trailer (type 98). Within each group pair, there can be multiple Accounts (types 03 and 49) that contain the Transaction detail (type 16) followed by single or multiple Continuation lines (type 88).

The annotated sample below highlights the nesting structure of a BAI2 file that contains one group and two accounts.

The first two digits of each line followed by a comma identify the Record type.

On the lines for Transaction detail (highlighted in purple), the type code follows the Record type 16, separated by a comma. In this example, one Transaction detail line has a 195 type code and the second Transaction detail line has a 495 type code.

CFONB

The CFONB bank statement file is a standard format defined by the French Banking Organisation and Standardisation Committee. It displays financial transactions that occur within a period on a bank account held by a company.

CFONB is flat file with fixed length and four record types.

• Record type 01 – This contains the previous account balance and related information.
• Record type 04 – This contains transaction information.
• Record type 05 – This contains transaction details (optional). There could be multiple 05 record types.
• Record type 07 – This contains the new account balance and related information.

A CFONB file can include several bank account statements, Record types 01 and 07. Within each statement pair, there can be multiple transactions, Record type 04, each with multiple transaction detail lines, Record type 05. If there are no transaction within the period, only Record types 01 and 07 are present for that account.

In the sample file below, the first account highlighted in red has no transactions. The account highlighted in blue has several transactions, Record type 04 with transaction detail lines, Record type 05.

Bank ID

The CFONB file also includes information from the Bank ID sent from the bank. Sage X3 uses this ID to match open items as part of the advanced bank statement import process. If the ID from the bank and ID entered in the Bank file profile (GESBAN), the import fails.

Therefore, when entering Bank import settings (BSIIMPPAR), the bank field is disabled for the CFONB file format. EM uses the ID from the bank to match to accounts and open items.

CFONB file limitations

• The reconciliation flow is not managed for CFONB bank statement file. Only the standard flow is managed (matching and payment generation).
• Splitting a CFONB Bank statement file is managed for each Record type 01 (account level) if there is at least one transaction, Record type 04. Otherwise this record is skipped because this is the only record type useful for the matching and payment generation.
• No bank can be filtered when launching the import with a file with CFONB type. To prevent it, the bank field is disable when choosing a CFONB file type.
• Bank Statement with multi currencies not managed.
• Only one Record type 05 is mapped to a transaction, Record type 04. If there are several related to the same Record type 04, the others are ignored.
• Interbank code in payments, generated through advanced bank statement process, are not considered.

Note: There is nothing to prevent you from importing the same file twice. You only receive a warning message about the duplicate file name.