Setting up

Before you can start working with the advanced bank statement import process, you need to complete the steps detailed below.

Depending on your needs, you can have several sets of settings because they can be linked to either import format and/or company and/or bank account.

• Activate the BSI – Bank statement import activity code (functional).
• Create a dedicated payment entry type for the bank statement import and assign a default payment attribute.
• Define the bank import segments, (i.e., header, start/end totals, transaction).
• Define the bank import formats such as the definition of one or more MT940 structures with the sequence of the segments used.
• Define bank import type codes for BAI2 file formats only.
• Define invoice and business partner numbers used in the automatic matching process.
• Define bank import settings used for the statement import and controlled by company and bank account. For CFONB file formats, a specific bank is not required. You can enter a bank, but it is not considered when importing the file.

Payment entry types (GESTPY)

Open: Setup > A/P-A/R accounting > Payment entry types

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

These settings apply to all bank formats:

General section

Sign: Unspecified

Default payment attribute: to be used later for payments. The payment attribute (GESCDA) you enter must have the following settings:
Account structure: Bank <=> BP
Accounting sign: Payment sign

Entry section

Select the following check boxes: Bank, Reference, Header description, Line description, Payment method, and Value date.

Do not select the Entry batch check box.

Do not add fields to the Extra fields grid.

Steps section

Select the Remittances check box if payments should be posted automatically.

Do not enter any bank file generation settings.

Bank posting/Group entry: STEP1

No intermediate posting, no draft transfers

For users in Germany and Austria, the Payment grouping and Discount grouping settings are mandatory. Select Payment for both fields. This is particularly important for German users who are transferring data via the DATEV export interface.

Bank import segment definition (BSISCT)

Open: Setup > A/P- A/R accounting > Advanced bank statement > Bank import segment definition

The segment definition describes the detailed structure of the information contained in a segment.

Based on this, the import extracts and interprets the information contained in the file.

The segments define in which data field the extracted information is stored in your system later. For each segment, you must create at least one detail line. You can have variants of one segment, i.e., when you receive bank statement files from different banks using different formats.

The sequence of the segments is controlled by the import format definition.

See Data field mapping for a list of available database fields.

Defining a segment

To illustrate the segment definition, we will refer to an MT940 segment as an example. Look at segment :61:, which is a line segment containing information for a single transaction (date, amount etc.):

:61:1608100810C3000,00N016NONREF

This segment consists of the following fields. Your bank provides the MT940 format description:

• Segment ID: :61: (4 characters fixed)
• Value date: 160810 (YYMMDD, 6 numbers fixed)
• Posting date: 0810 (MMDD, 4 numbers fixed)
• Credit/debit sign: C (possible values are C, D, RC, RD, 2 characters variable)
• Amount: 3000.00 (decimal, 15 characters variable)
• Constant: N (1 character fixed)
• Transaction code: 016 (3 characters fixed)
• Reference: NONREF (16 characters variable)

For this, you create the following detail lines according to the segment fields. For each field within a segment, one detail line must be entered:

1. The first line defines the segment ID. This is used to recognize the beginning of a segment. Note that the segment ID itself is not stored in the database; it is only parsed.
2. The second line writes the second field (value date) of the segment to the VALDAT field in the BSIIMPD table.
3. The third line reads the next field (posting date), but this information is not stored, and no field is given as a destination.
4. The fourth line writes the credit/debit sign to the SNS field in the BSIIMPD table. Because the format of the credit/debit sign coming from the file cannot be interpreted directly—a C or D character is expected in the SNS field; a specific conversion function is used. This is controlled in the Formula column.
5. The fifth line writes the amount to the AMTCUR field. To convert the MT940 currency format, a specific function is used.
6. The sixth line actually reads two fields of the segment (constant N and transaction code) but does not store this data.

Compared to the example line for segment :61:, you do not create another detail line for the Reference field because it is not used. For MT940 files, the import process skips the remaining fields at the end of the segment.

Additional remarks on the different columns:

• Position and length must reflect the sequence of the different fields within this segment. For variable length fields, the maximum value must be set as length. The length type setting must be done according to the character of the field (fixed/variable length).
• The Type selection defines the data: either Segment ID (used for identifying the start of a segment), Field ID (used for identifying a field identifier), Data (for data to be stored), or Constant (to add a “virtual” constant, the value is given in the Formula column).
• The Field type controls if the content is alphanumeric or numeric. It must be chosen in regard to the Destination field.

Handling subfields in a segment

Some bank statement formats have fields with structured information. This means you can have subfields (identifier) as part of the data structure. The use of subfields and their meaning could depend on the bank.

As an example, look at the first part of segment :86: in the MT940 format:

:86:091?00UEBERWEISUNG?10944?20RG16S49000009 RG16S4X000003

• Segment ID: :86: (4 characters fixed)
• Transaction code: 091 (4 characters fixed)
• Subfield ID: ?00 (3 characters fixed)
• Content of subfield 00: UEBERWEISUNG (27 characters variable)
• Subfield ID: ?10 (3 characters fixed)
• Content of sub-field 10: 944 (27 characters variable)
• Subfield ID: ?20 (3 characters fixed)
• Content of subfield 20: RG16S49000009 RG16S4X000003 (27 characters variable)

Subfield IDs are identified similar to segment IDs using the Type and Identifier fields. This results in the following detail lines for segment :86: (excerpt):

Data field mapping

During the import of a bank statement file, the information contained in the different transactions must be written into certain database tables and fields. While the use of some of these elements is controlled through the import process, there are many fields that can be used in a flexible way when setting up the segments.

BSIIMP Table (import header data)

• BSITRS (A(20)): transaction number of the statement (informational)
• SEQNUM (A(12)): sequence number of the statement (informational)
• REFBAN (A(30)): bank account reference, used to search for the corresponding bank account (GESBAN, field account reference)
• BLCSTR/BLCEND (MD1): start/end balance of the statement (informational)
• BLCSTRSNS/BLCENDSNS (A4): sign of the start/end balance, must be given as C for credit and D for debit. See also SNS (A(2)) below.
• DATSTR/DATEND (D): start/end date of the statement (informational)

BSIIMPD Table (import detail data)

• VALDAT (D): Value date (used for payment creation)
• DES (DES): Reference text (used for additional information), used for search term list search
• CUR (CUR): Currency. Statement and bank account currency must be the same.
• SNS (A(2)): Sense (credit or debit flag) - C for credit and D for debit is expected. Use an expression/function that is entered as a formula in the segment definition to convert the sign information within the file to these values.
• AMTCUR (MD1): Amount in transaction currency
• FREREF(*) (A(30)): Ten free-reference fields for transaction details, searched by the matching process for BP number, invoice number, source document, and order number.
• BPRREF(*) (A(30)): Business partner information fields, (i.e., name, bank details), searched by the matching process for bank ID (BPRREF(0) and (1)) and BP name (BPRREF(2) and BPRREF(3))
• BPRNAM (NAM): Business partner name, searched by the matching process
• REM (A(250)): Comment
• TRS (A(10)): Transaction code

BSIIMPDS Table (import subdetail data – only to be used for CAMT import)

The CAMT structure contains two levels of line details (C and D level transaction information). D level information must be stored in the BSIIMPDS table.

• AMTCUR (MD1): amount in transaction currency
• CUR (CUR): Currency. The statement and bank account currency must be the same.
• SNS (A(4), values = CRDT/DBIT: Sense (credit/debit flag)
• ACCREF(3) (A(35)): bank account reference, i.e., IBAN/BIC
• BPRREF(5) (A(140)): BP reference
• RMTREF(10) (A(140)): remittance reference
• BANTRSREF(3) (A(35)): bank transaction reference
• BVRREF (A(27)): ISR reference number (specific to Switzerland)

Bank import format definition (BSIFILFMT)

Open: Setup > A/P-A/R accounting > Advanced bank statement > Bank import format definition

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).

The order of the entries in the format definition must reflect the sequence of the segments within the statement.

For each segment, you need to enter one line with the following data:

• Segment: Enter the code of the referred segment.
• Start/End: This indicates if the segment is a start/end segment.
• Header/Line: This indicates the type of the segment and influences the data storage. Header segments can appear only once per statement while line-type segments can occur several times in a statement. Line detail is used for additional detail level (like D level transaction information in CAMT).
• Mandatory: Missing mandatory segments cause an error during import.
• Operation: Defines data processing on each occurrence of this segment:
  • Create: A new data line is written into the database.
  • Update: The existing data line is updated.
  • Unspecified: The segment is analyzed, but the data is not stored immediately. It is written with the next create or update operation.
  • Ignore: The line, where the segment refers to, is ignored during the import. This must only be used for header or footer lines when an entire header or footer line is not relevant to the process.

Operation type example

The MT940 format is used here as an example. The general structure looks like this:

• Several different header-type segments (originating account information, statement identification, start balance etc.)
• Several different line-type segments (transaction date and amount, transaction information)
• Several different header-type segments to end the statement (end balance(s) etc.)

For this type of structure, the operation type should be set like this:

• The last header-type segment before the first line-type segment should be set to Create because at this point in the processing, the header information must be written to the database. The other header segments should be set to Unspecified.
• The last line-type segment should also be set to Create while the other line-type segments should be set to Unspecified. This writes the transaction information to the database after the last segment of the transaction has been processed.
• The last header-type segment for the closing of the statement should be set to Update because the closing data does not create a new header entry but updates the existing one, (i.e., add end balance or end date).

Bank import type codes (BSIIMPTC)

Open: Setup > A/P-A/R accounting > Advanced bank statement > Bank import type codes

This is specific to BAI2 file types only. See Appendix III: BAI2 type codes for more information about type codes.

In this step, you need to define the type codes according to your bank or the uniform type codes. Only type codes defined here can be imported for the advanced bank statement import process including automatic matching. Type codes not defined are highlighted as errors in the import log file.

Detail amounts are always positive or unsigned in a BAI2 file. The type code list provides the appropriate sign (debit or credit) for the transaction.

Invoice and Business partner number definition (BSIINVDIO, BSIBPRNUM)

Open: Setup > A/P-A/R accounting > Advanced bank statement > Invoice number definition, BP number definition

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.

Both settings have mostly the same options.

Defining numbers

In addition to the header information, you need to provide information such as the prefix and the number length range. You can enter several lines of definitions if needed.

The prefix is the constant part of the number that is used to recognize the beginning of a BP or invoice number contained in the transaction information.

The length range is used to identify the BP or invoice number further and to create the search key(s) used in the automatic matching. The amount you enter includes the total length and the length of the prefix.

Examples

If your invoices start with a fixed yearly prefix, (i.e., 2016-01211, 2015-12299), create two lines with 2016- and 2015- as prefixes. For the length range, give a value of 10 each because the length is constant in this example.

In this example, you need to update the settings when you start issuing invoices in 2017 with a new line with the prefix 2017- and possibly remove the old one(s).

If you have purely sequential invoice numbers, (i.e., 1 …99999), create nine lines, each with a different prefix: 1, 2, 3, etc. Because the invoice number length can vary between one and five in this example, each line needs a length range between 1 and 5 to recognize the invoice numbers. Of course, this kind of setup is not very good for search efficiency because many search keys are generated. A more reduced definition should be done if possible, (i.e., exclude old number ranges).

Search keys for the matching BPs and invoices are generated from each of the definition lines. For the first example, the following would happen:

1. The matching process searches the transaction information imported from the statement (see Data field mapping) for strings starting with 2016 - and having a total length of 10 characters. Each string that matches these criteria is tested against existing invoice or BP numbers and if found, added to the list of search keys to be checked in detail later.
2. Because there is a second line in the invoice no. definition, the same occurs for strings starting with 2015 -.
3. With the list of search keys, additional checks are performed against open items like checking the amount, etc. Depending on the results, a match is recorded.
4. It is important to note that a large number of definition lines and/or wide length ranges negatively impact the search efficiency. Try to define the ranges as close as possible and remove obsolete definitions (i.e., old invoice no. ranges). Keep in mind that for the bank statement import, only those invoice numbers where you actually expect payments are relevant.

Bank import settings (BSIIMPPAR)

Open: Setup > A/P-A/R accounting > Advanced bank statement > 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.

These settings are applied in the context of an import, so if you import a bank statement file, you have to select a set of settings for this import. These settings are used for the import and the matching process later.

Following is an overview of the settings highlighting important aspects. Detailed information on each option is available in the online help.

General section

Depending on the way you want to work with the advanced bank statement import process, you can choose between two different flow types. You can use different flow type settings per company and/or bank account:

Standard: The imported bank statement is processed within the full flow of the advanced bank statement import process. After importing the bank statement file, you can match the transactions to open items or posted payments. For open items, payments are generated when validating the statement using the Process bank transactions function (GESBSIMTC).

Reconciliation only: After importing the bank statement file, the transaction data is copied to the Bank statement reconciliation function (RAPBAN), where you can perform a standard bank reconciliation. There is no further processing within the advanced bank statement import process.

Accounting section

Here, you define the settings used for creating the payments resulting from matching to open items. Matches to existing payments does not generate a new payment.

Suppress intermediate posting: You have the option to prevent B or C level matches from posting to an intermediate or suspense account. If you select this option, you need to manually create payments or journal entries for those transactions if necessary.

Search criteria section

Defining the BP number and/or Invoice number are mandatory criteria. The options in this section impact search performance, so it is strongly recommended to select additional criteria carefully.

For any selected data type, (i.e., BP number, invoice no., etc.), data is compared against the data extracted from the imported bank transactions to find the BP and the open items.

A matching amount (including discount if applicable) is always an implicit decision criterion for an A or B level match. This option only controls if additional searches by amount are performed.

If you want to include existing payments, you need to activate at least one search criterion in the Payments block.

The BP bank ID, which needs to be an IBAN, uses the bank ID from the transaction to identify the BP.

Open items block

These settings determine the search criteria for open items.

Payments block

These settings determine the search criteria for existing payments. You must make at least one selection to include payments in the matching process.

Other block

These settings define additional search criteria.

Other section

These settings refer to handling the bank statement files to import.

Files can either be uploaded from the client or imported from a dedicated server volume.

After the import, the files can be automatically renamed and/or moved to a different volume.

Excluded search terms (BSISEAEXD)

Open: Setup > A/P-A/R accounting > Advanced bank statement > Excluded search terms

In this function, 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.,” “GmbH” and “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.

Enter excluded search terms as a list of texts. Wildcards are not allowed.

Search terms list (BSISEALIS)

Open: Setup > A/P-A/R accounting > Advanced bank statement > 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.

In Bank import settings, you can choose to apply the search term list and, if yes, whether to use the list before or after the standard search process. If applied after the standard search, only transactions where no matches to open items were found (C match) are considered.

In addition to the header information, you need to provide one detail line per search term. You need to include details for the payment generation like payment attribute, site, tax code, etc.

The search terms are processed line by line, from the top down. The first matching search term line and its settings are used for the matching.

Four different criteria for the search can be applied:

• Reference: This text is searched in the payment reference fields (must have been stored by the import segment definition in [F:BSIIMPD]FREREF(0) to (9)).
• BP reference: This text is searched in the BP reference fields (must have been stored by the import segment definition in [F:BSIIMPD]BPRREF(0) to (5)).
• Payment description: This text is searched in the payment description (must have been stored by the import segment definition in [F:BSIIMPD]DES).
• Amount: Only positive values

Wildcards are not allowed. If you enter several texts within one criterion (example: LEASE CONTRACT as reference), the exact string must be found. If several criteria are entered, (i.e., Reference text and amount), they are linked with “AND.”

Payment related settings

Additional settings need to be entered for payment creation like BP or G/L account, payment attribute (destination), etc.

You can enter either a G/L or a BP account but not both. For a BP account, you must enter a control account.

The sign of the payment attribute entered must match the payment sign of the transaction or set to payment direction. Otherwise there is no match, even when other criteria apply.

The Account structure of the payment attribute needs to be Bank <=> BP or Bank <=> Account.

If you use a G/L account that is subjected to a VAT, a VAT code is mandatory.

A (fixed) distribution can be used if either the G/L or the BP account are set up for analytical accounting.