Process bank transactions

Bank statement import overview

You must complete the setup process before importing bank statements, reviewing and processing matches, and generating payments through validation.

See Bank statement setup overview. The setup involves technical settings that should be done very carefully. If you are not certain, contact your administrator or business partner.

This is an overview of the bank statement import and matching process. See the relevant function help for specifics regarding each step.

The process varies based on the flow type selected in the Bank import settings function (BSIIMPPAR).

Standard: Importing, matching, and generating payments takes place within the full bank statement import workflow, including the Process bank transaction function (GESBSIMTC).

Reconciliation only: Importing takes place within the bank statement import workflow. Matching and generating payments takes place in the Bank statement reconciliation function (RAPBAN). This flow type is not available for CFONB file types.

1. Import transactions

This function imports bank transactions in various electronic formats from one or several bank statement files. These files have been obtained directly from your bank or exported from an online banking tool and saved to your workstation or server. The bank transaction import function (BSIIMPORT) imports these files from their saved location for matching and processing.

This step applies to Standard and Reconciliation only flow types.

See Bank transaction import

2. Managing imports and matching

Next, you need to manage how you import and match transactions.

An imported statement can have one of the following statuses:

• Not matched: Initial status after import, no matching has been performed.
• Matched: Matching or partial matching has been performed for the statement.
• Validated: Payments have been generated and the statement is read-only.
• Reconcile: The bank statement was imported with Reconciliation only as the flow type. The transaction data has been copied to the Bank statement reconciliation function (RAPBAN). The statement here is read-only.

Standard flow type:

Match transactions to open items, accounts, or existing payments using the Bank transaction management function (BSIIMP). You can perform an automatic match from this screen, but you cannot review or modify results and you cannot generate payments.

Reconciliation only flow type:

The transaction data for the statement has been copied to the Bank statement reconciliation function (RAPBAN) where you can perform a standard manual bank reconciliation. Import data is display-only. For each statement, you directly access the transaction data from the Action icon menu at the beginning of each line. You cannot process the statement through the Process bank transactions screen.

See Bank transaction management

3. Matching transactions to open items or accounts

This step only applies to the Standard flow type.

The imported transactions need to be reviewed and matched to open items or other accounts such as a G/L account, BP account, or a suspense account. Transactions can also be matched to existing payments.

Note: Existing payments refers to payments created through the standard payment processes like payment proposals and manual payment entry and have a status of Posted.

Matching is done in the Process bank transactions screen. You can match items automatically or manually.

• Automatic matching: From the Bank transaction management screen, click Start matching to run the process on all unmatched statements in the grid.
• Manual matching: From the Action icon menu at the beginning of a line, select Process bank transactions. This opens a new screen where you can review and edit results from automatic matching, perform manual matching and selection, and manual entry.

Processing transactions

This step only applies to the Standard flow type.

A: This represents an automatic exact match to one or several open items and/or existing payments. Matched open items display in the grid and are selected. The "A" status can also include:

Automatic matches:

A: to an open item or payment

AC: to open items in a different currency

AS: by search term list that automatically creates a manual entry

Manual matches:

AM: to an open item and/or an existing payment

AD: manual entry

B: Some information was found, but no exact matches. Possible matching open items or existing payments are displayed in the related grids and are not selected. If not processed manually before validating, payments are posted to the Suspense account unless you have selected to Suppress intermediation posing as defined in the Bank import settings function (BSIIMPPAR).

BC for possible matches involving open items in a different currency.

C: No information was found and no open items or existing payments display in the related grids. If not processed manually before validating, payments are posted to the Suspense account unless you have selected to Suppress intermediation posing as defined in the Bank import settings function (BSIIMPPAR).

Notes: If you review a statement before matching or if you unmatched a transaction, the status code is blank.

For a manual match, you must distribute the complete transaction amount as paid, plus overpayment if applicable. Otherwise you can't save your changes. You can distribute transactions in a statement to either multiple open items or accounts or existing payments.

For A level matches

To open items: This generates the payment and no more changes can be made.

              

To existing payments: This simply creates the match to the bank transaction with no new posting or payments.*

              

To an existing payment linked also to manual entry: This matches the existing payment and creates a new payment posting for the manual entry amount only.

*For A level matches to existing payments, validation creates the following data:

PAYMENTH: a reference to the linked transaction in BSIIMPD

GACCENTRY: set the matching flag entry  (BSI) for those journal entries related to the entry in PAYMENTH

For B and C level matches

Payments are created for the suspense account unless you have selected to Suppress intermediate posting. In that case, you need to manually generate payments and journal entries for those transactions. This option and suspense account settings are defined in the Bank import settings.

See Process bank transactions

4. Validating a statement

This step only applies to the Standard flow type.

Validating is the final step in the bank import process and generates a payment for transactions matched to open items or BP/GL accounts. After you have validated the statement, you can no longer modify the transactions. The validation is always performed for the entire current statement and depends on the matching status:

See Process bank transactions

5. Manually completing a transaction

This step only applies to the Standard flow type.

In certain cases, open items associated with a bank statement transaction might have been settled outside the bank statement import process. In other words, a payment transaction included in the bank statement already exists as a payment.

Complete transaction is active when the transaction status is Not validated but has already been matched or when the transaction status is Partially validated.

This option is inactive when the transaction status is Not validated but has not yet been matched, or when the transaction status is Validated or Manually completed.

Note: Use this action with care. Completing a transaction cannot be undone.

Important considerations

Site determination

If the bank account is linked to a dedicated site, only invoices belonging to the same site are considered in the matching process and this site is used for payment generation.

If there is no site linked to the bank account, the payment site is determined from the invoice.

Creditor payments

Typically, creditor payments are generated in the standard payment life cycle and have already been posted when the transactions show in the bank statement.

By enabling one or more payment matching criteria in the bank import settings, you can activate the automatic matching of bank statement transactions to existing payments previously having been created through the payment proposal or manual payment entry (i.e., for bank file generation).

This will create a matching mark but (contrary to open item matching) no payment entry as a payment already exists. Manually matching bank statement transactions to such payments is also an option.

In case that creditor payments are not created as mentioned through the standard payment process, there are additional options for handling this:

1. When generating the creditor payments, post them to an intermediate account instead of the actual bank account. In bank statement import, creditor payments not matched to existing payments are posted to the suspense account (defined in the bank import settings) because they usually have a C status after matching.
   As a last step, you can perform a manual posting later from the suspense to the intermediate account to balance these accounts.
2. Use the Complete transaction action for each of these transactions. They receive the Manually completed status and no posting is generated when the statement is validated.
3. Use the option Suppress intermediate posting in the bank import settings. With this setting, no intermediate postings are generated by the advanced bank statement import for all transactions with B and C matching status.

Note: For creditor direct debits etc., creditor payments can be matched also to unpaid creditor open items just like debtor payments.

Working with multiple currencies

A financial institute can have a bank account in the local currency or a foreign currency. Examples:

• Local currency:
• A French financial institute has a bank account in EUR.
• A British financial institute has a bank account in GBP.
• Foreign currency:
• A German financial institute has a bank account in USD.
• A British financial institute has a bank account in EUR.

The financial institute manages the transactions in the bank account currency and supplies the bank statement in this currency. If foreign currency transactions occur in the bank account, the bank converts them into the bank account currency first. Example: A USD payment received on a bank account in EUR is converted to a EUR payment.

For the Sage X3 advanced bank statement import, the statement currency and the currency setting for the Sage X3 bank account must be the same. Note that multiple currencies do not apply to CFONB file formats.

A bank statement import with a currency different from the Sage X3 bank account is not possible and is rejected.

In addition to the bank statement currency, the open item currency (invoice currency) must also be considered because this currency could differ from the statement currency. The advanced bank import supports the following cases for automatic and manual matching:

• Bank statement in company currency, open item in company currency. Example: EUR statement and EUR open item
• Bank statement in foreign currency, open item in same foreign currency. Example: USD statement and USD open item
• Bank statement in company currency, open item in foreign currency. Example: EUR statement and GBP open item

Other cases (like matching a GBP statement from a GBP bank account to USD open items) are not supported. Note also that for matching, within one transaction of a bank statement, only one currency can be used. This means that if you match one transaction to two open items, both open items must have the same currency.

Automatic matching and currency variances

In the context of the bank statement import, currency variances occur mainly for the following reasons:

• From Sage X3, a sales invoice is issued in USD.
• The customer pays the invoice in USD to a EUR bank account of the company. The bank converts the USD payment to EUR using the current exchange rate.
• The payment transaction appears in EUR in the electronic bank statement.

Because the exchange rate for the payment usually differs from the exchange rate for the invoice, you can define an "accepted currency variance %" to automatically handle these types of variances:

• If the variance in the reference currency is within the defined limit, the automatic matching assumes a full payment of the invoice and posts the variance as the currency variance.
• If the variance in the reference currency is outside the defined limit, the automatic matching assumes a partial payment. You can either accept the partial payment or manually modify the matching result.

Example of a full match

• You create a sales invoice of 100 USD. When you create the invoice, the currency rate EUR/USD stored in Sage X3 is 1.1111 and therefore the open item amount of 100 USD corresponds to 90 EUR in company currency (reference currency).
• The customer pays 100 USD to a EUR bank account. The bank converts the 100 USD payment to 93 EUR, which corresponds to a currency rate EUR/USD of 1.0753.
• The payment transaction of 93 EUR is listed in the electronic bank statement for the Sage X3 user.

Assumption: All search criteria are met, and the accepted currency conversion variance is set to 5%.

• The matching calculates the difference between the transaction amount and open item amount in reference currency:
  93 EUR - 90 EUR = 3 EUR. 3/90 = 3.333%
  The currency conversion variance between open item amount in ledger currency and transaction amount in ledger currency is 3.333%.
• Therefore, the transaction is qualified as a full match and receives an AC status.
• The transactionamount in currency is set to 100 USD to match the open item amount in currency, assuming a full payment. Also, a currency variance of 3 EUR is posted. 

Open item amount in currency	Open item amount in reference currency	Transaction amount in currency	Transaction amount in reference currency
100 USD	90 EUR	100 USD assumed as a full match by matching	93 EUR imported transaction amount

Example of a partial match

• You create a sales invoice of 100 USD. When you create the invoice, the currency rate EUR/USD stored in Sage X3 is 1.1111 and therefore the open item amount of 100 USD corresponds to 90 EUR in company currency (reference currency).
• The customer pays 86.02 USD to a EUR bank account because he deducts a certain amount due to a problem with the delivery. The bank converts the 86.02 USD payment to 80 EUR, which corresponds to a currency rate EUR/USD of 1.0753.
• The payment transaction of 80 EUR is listed in the electronic bank statement of the Sage X3 user.

Assumption: All search criteria are met; accepted currency conversion variance is set to 5%.

• The matching calculates the difference between the transaction amount and open item amount in reference currency:
  80 EUR - 90 EUR = (ABS) 10 EUR. 10/90 = 11.111%
  The currency conversion variance between open item amount in ledger currency and transaction amount in ledger currency is 11.111%.
• Therefore, the transaction is not qualified as a full match because the difference is too great. The matching status is set to BC and you need to manually resolve this by either accepting the difference or creating a partial payment. The matching process does not suggest any amounts in this case or any other B matches.

You need to manually select the open item to assign the payment to it:

1. When the open item is selected, Sage X3 compares the transaction amount in transaction currency (EUR) to the open item amount in the reference currency (EUR). Because the transaction amount is less than the open item amount, the transaction amount is suggested, which is the standard behavior for manual selections.
2. Sage X3 recalculates the suggested transaction amount in a currency to its equivalent in the open item currency based on the last currency rate available in the currency rate management of Sage X3 for the given rate type.
   For the example, if a currency rate EUR/USD stored in Sage X3 is 1.0625 (daily rate, monthly rate, etc.): 80 EUR * 1.0625 = 85 USD.
3. Because the bank has stated an amount of 86.02 USD in the remittance information of the statement, you need to correct or modify the amount in the transaction currency manually in the open items grid. This creates a partial payment with 86.02 USD/80 EUR to the open item. No currency variance is posted for the partial payment.

Open item amount in currency	Open item amount in reference currency	Transaction amount in currency	Transaction amount in reference currency
100 USD	90 EUR	86.02 USD entered by the user	80 EUR imported transaction amount

Matching multiple open items in currency

If the automatic matching matches a payment in a currency to several open items, the same calculation is performed as described above except that calculation and check of the allowed currency variance is done on the total amount in the reference currency of the relevant open items.
Note: All open items considered for the matching must have the same open item currency.

Example:

• You create two sales invoices: 100 USD and 200 USD.
• When you create the first invoice, the currency rate EUR/USD stored in Sage X3 is 1.1111 and therefore the open item amount of 100 USD corresponds to 90 EUR in company currency (reference currency).
• When you create the second invoice, the currency rate EUR / USD stored in Sage X3 is 1.1494 and therefore the open item amount of 200 USD corresponds to 174 EUR in company currency (reference currency).
• The customer pays 300 USD to a EUR bank account of the Sage X3 user.
• The bank converts the 300 USD payment to 269 EUR, which corresponds to a currency rate EUR/USD of 1.1152.
• The payment transaction of 269 EUR is listed in the electronic bank statement of the Sage X3 user.

Assumption: All search criteria are met and all open items to be matched have the same currency; currency conversion variance is set to 5%.

• The currency conversion variance between the open item total amount in reference currency (264 EUR) and the transaction amount in transaction currency (269 EUR) is 1.89%:
  264 EUR - 269 EUR = (ABS) 5 EUR. 5 EUR/ 264 EUR = 1.8939 %
  Therefore the underlying transaction qualifies as an AC match.
• Sage X3 calculates the transaction amount in the ledger currency for each open item currency on a pro rata basis of the open item total amount in currency:
  90/264 * 269 = 91.70 EUR
  174/264 * 269 = 177.30 EUR
• Assuming a full payment, you set the transaction amount in currency to the open item amount in currency:
  For the first open item this is 100 USD .
  For the second open item this is 200 USD. 

Open item amount in currency	Open item amount in reference currency	Transaction amount in currency	Transaction amount in reference currency
100 USD	90 EUR	100 USD assumed as a full match by matching	91.70 EUR calculated pro rata
200 USD	174 EUR	200 USD assumed as a full match by the matching)	177.30 EUR calculated pro rata

This settles the two open items, and a currency variance of 5 EUR in total is posted.

Tab Payment information

Tab Transaction detail

Tab Matching details