Bank import settings

Use this function to define settings used for bank transaction imports and automatic matching. Alternatively, you have the option to send data to the Bank statement reconciliation function (RAPBAN) where you can complete the matching process by selecting Reconciliation only for the flow type.

The codes you define here are important in determining how transactions are imported, matched to open items, and how payments are generated using the Bank transaction management function (BSIIMP).

You can create multiple codes per company for different sites, banks, formats, etc. The settings on the Search criteria tab directly control the automatic matching process.

It is strongly recommended to create dedicated payment entry types to use when defining import settings on the Accounting tab.

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, and AR Lockbox CSV file types.

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. A dedicated BAI2 entry type is 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, and so on, 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.

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.

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.

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 specific text in the transaction, should be used to match the transaction to a certain general ledger or BP account.

Prerequisites

Refer to the documentation on Implementation.

Actions

Select Create to define new settings.

General tab

These fields define the company for the bank statement import. You can enter a bank and/or a file format to link a setting to those criteria if you want to use specific settings for a certain bank file format.

General

Code (field CODIMPPAR)

Enter a unique code that identifies the import setting.

Company (field CPY)

Enter a company.

Description (field DES)

Enter a description for the format (optional).

Bank (field BAN)

Enter a specific bank to limit this import code to transactions from this bank.

File format (field FILFMT)

Enter a file format to limit this import code to transactions in this format only.

Flow type (field FLOTYP)

Select a flow type to process imported bank statements:

Standard: The imported bank statement is processed within the full flow of the advanced bank statement import. After importing the bank statement file, the transactions are matched to open items or existing payments. For matched open items, payments are generated when validating the statement using the Process bank transactions function.

Reconciliation only: After importing the bank statement file, the transaction data is copied to the Bank reconciliation function (RAPBAN), where you can perform a standard bank reconciliation. There is no further processing within the advanced bank statement import. If you select this flow type, all the fields on the Accounting and Search tabs are disabled.

Post validation transfer: Use this flow type to also transfer the bank statement transactions to the Bank reconciliation function (RAPBAN) for further processing. This option combines the existing flow types Standard and Reconciliation only.

The bank transactions are transferred after matching and validating the statement in the Process bank transactions function (GESBSIMTC).

Matching information for A matches to open items or manual entries is not transferred to the Bank reconciliation function with this release.

File format (field FILFMT)

Enter a file format to limit this import code to transactions in this format only.

Preprocessing (field PREPRC)

Use this option to activate a custom processing of the bank import file, for example to reformat the file or remove non-required lines.

Standard script (field TRTSTD)

If you select the Preprocessing checkbox, you need to enter the name of the preprocessing script here.

Date/Balance control (field DATBALCTL)

If you are performing a manual import, select this option to display a warning if either of the conditions below occur. You can decide to continue with the import despite the inconsistency or cancel.

  • The end date of the previous statement and start date of the imported statement do not match.
  • The end balance of the previous statement and start balance of the imported statement do not match.

Accounting tab

This fields in this section determine the payment generation settings. Matches to existing payments do not generate a new payment

It is strongly recommended to use dedicated payment entry types for the bank statement import so settings can be made independent from other entry types.

This only applies if you select a Standard flow type.

Recommended settings for Payment entry types:

General tab

  • Sign: Unspecified
  • Default payment attribute to be used later for payments. This attribute must be set up with Accounting sign = Payment sign and Account structure: Bank <-> BP.

Entry tab

  • Select the following checkboxes: Bank, Reference, Header description, Line description, Payment method, and Value date.
  • Do not select the Entry batch checkbox.
  • Do not add fields to the Extra fields grid.

Steps tab

  • Select the Remittances checkbox if payments should be posted automatically.
  • Do not enter any bank file generation settings.
  • Group entry: STEP 1
  • No intermediate posting, no notes P/R risk closing
  • If you are in Germany or Austria, the Payment grouping and Discount grouping field settings under Bank posting are mandatory. You need to select Payment from the drop-down menu for each field. This is particularly important for Germany when transferring data via the DATEV export interface.

Accounting

Payment entry type (field PAYTYP)

Enter a dedicated payment entry type used to generate payments.

Payment attribute underpayment (field CDASHOPAY)

Enter a payment attribute (GESCDA) to generate specific payments for variances, not including discounts.

Payment attribute overpayment (field CDAEXCPAY)

Enter a payment attribute (GESCDA) to generate specific payments for variances.

Suspense account (field ACCUSUSPENSE)

Enter a suspense account.

Transactions not matched to an open item or a general ledger account are posted to this account for manual reconciliation later.

If you selected the Suppress intermediate posting checkbox, no postings are made to a suspense account.

Because the suspense account is used in the payment generation process, an address code needs to be assigned to this account in the Addresses section in Accounts (GESGAC).

Payment attribute suspense account (field CDAACCSUSP)

Enter a dedicated payment attribute for the suspense account. You can only enter a payment attribute with an accounting sign Unspecified.

You cannot use payment attributes with accounting signs Expense or Revenue. The reason for this restriction is that posting to suspense accounts can be revenue or expense and you cannot enter a payment attribute during a bank statement import life cycle.

If the accounting sign is not Unspecified, you receive a blocking message.

The only account structure allowed for the payment attribute is BANK <-> BP. Otherwise, you receive a blocking message.

Discount allowance days (field DEPTOL)

Enter the number of days accepted beyond the calculated discount date.

Example

If you allow a discount deduction when the payment arrives late by one day, enter 1. An automatic discount deduction is taken as if the payment arrived within the discount term.

Maximum amount variance (field AMTVAR)

Enter the maximum accepted variance, not including discounts, applied in the automatic matching. This allows you to automatically process amount variations in the payments.

Amount variance limit (field AMTVARLIM)

This field is active if you entered a variance amount. You can enter a maximum amount in the company currency to limit the variance percentage.

Maximum currency variance (field AMTVATCUR)

Enter the maximum accepted currency variance, applied in the automatic matching. This allows you to automatically process amount variations in the payments resulting from different currency rates between invoice creation and received payment.

This setting is only relevant when there are different currencies for statement and invoice currency.

Suppress intermediate posting (field FLGCLS)

If you select this checkbox, B or C level matches are not posted to an intermediary or Suspense account.

If you suppress intermediate posts, you need to manually create payments or journal entries for those transactions.
Create reconciliation (field )
If you selected Suppress intermediate posting, you can select this checkbox, which affects C matches.

Search criteria tab

Open items

BP number (field BPRNUM)

Select this checkbox to include the business partner number in the rules for the matching process.

BP name (field BPRNAM)

Select this checkbox to include the business partner name in the rules for the matching process.

Note: In the Excluded search term function, you can exclude common terms like the legal forms of a company (e.g., Inc. Ltd. GmbH, S.A.) from the search that would otherwise result in an extended search duration.

BP Bank ID (field BPRBANID)

Select this checkbox to only perform this search if the BP bank ID is given as an IBAN.

Invoice number (field INVNUM)

Select this checkbox to include the invoice number in the rules for the matching process. This is typically selected.

Source document number (field SRCDOC)

Select this checkbox to include the source document number in the rules for the matching process. If the Invoice number checkbox is selected, you should clear this option because it increases the run time for the matching process.

If this checkbox is selected, you need to select Source document no. under Search criteria when setting up the Invoice number definition.

Amount (field AMT)

Select this checkbox to carry out additional searches by amount.

A matching amount (including discount if applicable) is always an implicit decision criteria for an A or B matching level.

ISR reference number (field BVRREFNUM)

When this checkbox is selected, a Swiss ISR reference number can be used as matching criteria. This search applies to the import of Swiss CAMT bank statements only. The search is carried out on the subdetail (entry detail) level of CAMT.053/054 bank statement transactions only.

Use search term list (field USESEALIS)

This field controls the use of the search term list. Search terms can be used if fixed identifications (like BP name or transaction text) should be used to match the transaction to a specific general ledgeror BP account.

Select one of the following:

  • No: Do not use the search term list.
  • First pass: Use the search term list before the standard search process.
  • Second pass: Use the search term list after the standard search process; only C matches are considered.

Merge description field (field MRGDESFLG)

This field controls if the various text strings from the import are merged internally for search purposes or not. This can help identify search terms that have been distributed over several fields within the file.

Select one of the following:

  • No: Do not merge during search.
  • Always: Merge for all search runs.
  • Second pass: Perform a second search run with merged fields to improve results.

From BP (field BPRSTR)

This field is available if the All BPs checkbox is not selected.

To BP (field BPREND)

This field is available if the All BPs checkbox is not selected.

BP Type (field FIELDLABEL)

This field is available if the All BP type checkbox is not selected.

Customer category (field CATBPC)

This field is available if the All categories checkbox is not selected.

Supplier category (field CATBPS)

This field is available if the All categories checkbox is not selected.

Minimum search term length (field SEAEXDLEN)

You can enter a minimum length for search terms to improve the search efficiency. Shorter search terms are not considered by the matching process.

The minimum entered here must be shorter than the length of the actual BP and invoice no. definitions in the BP number definition and Invoice number definition functions.

Use excluded search term list (field SEAEXD)

Select this checkbox to exclude the terms defined in the Excluded search terms function (BSISEAEXD).

Payments

Payment reference (field PAYREF)

Select this checkbox to search by payment reference. The bank transaction reference must be stored in [BSIIMPD] PAYREF (bank import segment settings). Matching then compares this reference against the payment references stored in either the [PAYMENTD] NUM or REF fields.

If you select this option and a match is found, no other BP criteria are considered even if they are selected.

If the bank returns these references through the bank statement file, you should consider these as very precise matching criteria and use these matches.

BP bank account number (field BPRBANID)

Select this checkbox for an additional search for possible BP accounts in the list of bank IDs if the BP bank ID is given as an IBAN.

BP number (field BPRNUM)

Select this checkbox to include the business partner number in the rules for the matching process.

BP name (field BPRNAM)

Select this checkbox to include the business partner name in the rules for the matching process.

Note: In the Excluded search term function, you can exclude common terms like the legal forms of a company (e.g., Inc. Ltd. GmbH, S.A.) from the search that would otherwise result in an extended search duration.

Amount (field AMT)

Select this checkbox to search based on the payment amount. If other search criteria are selected, like BP bank account number, they limit the search by amount.

Days (field DAYSBACK)

Enter a number of days to limit search results to payments not older than the number entered (by accounting date). Use this option to limit the search to the most current payments to avoid accidental matches to old payments.

Entering a value of 0 is not recommended because there would be no limit and could impact performance.

Other

Merge description field (field MRGDESFLG)

This field controls if the various text strings from the import are merged internally for search purposes or not. This can help identify search terms that have been distributed over several fields within the file.

Select one of the following:

  • No: Do not merge during search.
  • Always: Merge for all search runs.
  • Second pass: Perform a second search run with merged fields to improve results.

From BP (field BPRSTR)

This field is available if the All BPs checkbox is not selected.

To BP (field BPREND)

This field is available if the All BPs checkbox is not selected.

BP Type (field FIELDLABEL)

This field is available if the All BP type checkbox is not selected.

Customer category (field CATBPC)

This field is available if the All categories checkbox is not selected.

Supplier category (field CATBPS)

This field is available if the All categories checkbox is not selected.

Minimum search term length (field SEAEXDLEN)

You can enter a minimum length for search terms to improve the search efficiency. Shorter search terms are not considered by the matching process.

The minimum entered here must be shorter than the length of the actual BP and invoice no. definitions in the BP number definition and Invoice number definition functions.

Use excluded search term list (field SEAEXD)

Select this checkbox to exclude the terms defined in the Excluded search terms function (BSISEAEXD).

Other tab

The fields in this section determine how import files are handled.

Other

File import (field FYPIMP)

Select one of the following:

  • Client
  • Server

Import volume (field IMPVOL)

This field is available if the Server checkbox is selected.

Move file after import (field MOVAFTIMP)

Select this checkbox to move the file to the location entered in the Move to volume field.

Move to volume (field MOVVOL)

This field is available if the Move file after import checkbox is selected.

Rename file after import (field RENAFTIMP)

Select this checkbox to rename the file. The file automatically receives a prefix with a status OK or NOK and a timestamp of the import.

Action panel

These actions are available when you display a single record.

Create

Select Create to define a new import setting.

Excel

Select Excel to export the current list to an Excel document.

 

Tables used

The following tables are implemented by the function:

Table

Table description

BSIIMPPAR [BSIIP]

Bank import settings