Bank import segment definition
Use this function to define the detailed structure of information contained in a segment of the bank statement import file for importing bank statement transactions. This information determines which data is imported and where the extracted information is stored in the database.
You must create one detail line per segment. You can also have multiple segments when, for example, you receive bank statement files from different banks using different formats.
See Understanding electronic bank statement formats for more information.
(missing or bad snippet)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.
These formats are supported:
- MT940
- AR Lockbox
- CAMT
- BAI2
- 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
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.
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
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.
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.
Prerequisites
Actions
Select Create to define a new bank import segment or select the Edit icon to modify an existing import segment.
Depending on the File type, not all fields are required and therefore not available.
Header
Segment (field CODSCT) |
Enter a unique identifier for each segment based on the Type. |
Description (field DES) |
Enter a description for the segment. |
File type (field FILTYP) |
Select a file type:
|
Maximum length (field LNGMAX) |
This field displays the maximum field length defined in the Lines grid. |
CAMT element mapping (field CODELT) |
Enter or select one of two predefined CAMT mapping element types:
CAMT element mapping describes a set of XML tags/elements in a CAMT statement file that is extracted during the bank statement import process. This field is not available for other file types. |
Level (field LEV) |
Enter the hierarchical level for the CAMT element mapping structure according to the bank import segment definition. This field is only available for CAMT file types. |
Grid
Position (field POS) |
Enter a number to define the starting position of the field in the segment. Starting position plus length of the previous field must match the starting position of the following field. Depending on the File type, this field might be disabled and therefore a position is not needed. The line order determines the sequence. |
Length (field LNG) |
Enter a number to define the maximum length of the fields in the segment. |
Length type (field LNGTYP) |
Use this field to select whether the field length is fixed or variable. |
Type (field TYP) |
Use this field to select the data type in the field:
A constant is sometimes required when a file format lacks information mandatory for processing the import. Typical examples would be currency code or payment sign. Both values are mandatory in the bank statement import process. If a constant is required, it is entered in the Formula field without quotation marks. |
Identifier (field FLDIDT) |
This field is required for Segment ID or Field ID types. Enter the string to recognize the segment or field. For example, :20: for the MT940 segment "20." |
Destination field (field FLD) |
For data fields, this indicates the database field to the store the imported data. For example, [F:BSIIMP]BSITRS]. Fields must be given in Sage X3 SAFE notation. This is a technical setting and changes should be done very carefully. If you are not certain, contact your Sage X3 administrator or business partner. |
Format (field FLDFMT) |
Use this field if you want to define a format for the field. The format is used to check the field data during the import. |
Field type (field FLDTYP) |
Select a field type.
The selected type must be compatible with the destination field type. |
Mandatory (field FLDOBY) |
Select this check box if this segment is mandatory. If the segment is missing in the file, an error occurs during the import. |
Description (field BSISCT_LINE_DES) |
You can enter an informative description about the line. |
Formula (field FRM) |
Enter a Sage X3 formula to apply specific controls or modifications to the data. Formulas must be given in Sage X3 SAFE notation. This is a technical setting and entries should be done very carefully. If you are not certain, contact your Sage X3 administrator or business partner. |
Error messages
In addition to the generic error messages, the following messages can appear during the entry:
Format and segment definition have to be of the same file type
The file types defined in the bank import segment definition and the linked bank import format must be the same.
Mandatory field '$1$' in segment '$2$' does not exist. Please check the according format and segment definition.
Review and update the segment definition.
Tables used
The following tables are implemented by the function:
Table | Table description |
|---|---|
BSISCT [BSISCT] | Bank import segment |
BSISCTD [BSISCTD] | Bank import segment detail |
Refer to the documentation on Implementation.