Plugin: BX Service Hibiscus

From iDempiere en

Description

This plugin allows you to:

  • Import CSV Files generated from Hibiscus into I_BankStatement
  • Match Customer Invoices
  • Match Vendor SEPA Invoices

Configuration

Hibiscus Configuration

Download the file de.willuhn.jameica.hbci.rmi.Umsatz.csv.vm and replace the existing file in your Hibiscus installation in the folder $HOME/.jameica/plugins/hibiscus/lib/velocity

Note that this file is overwritten on every Hibiscus update, so it must be copied again after updating.

You can check that the file is properly installed generating a CSV export from the transactions view. The resulting CSV file will have the following header:

"Konto_AccountNo";"Konto_RoutingNo";"Konto_Id";"Umsatz_Id";"Empfaenger_Konto";"Empfaenger_Blz";"Empfaenger_Name";"Betrag";"Zweck";"Zweck2";"Zweck3";"Datum";"Valuta";"Kommentar";"Checksum";"GvCode";"EndToEndId";"MandateId";"PrimaNota";"Art";"CustomerRef";"AddKey";"TxId";"PurposeCode";"Empfaenger_Name2";"UmsatzTyp_Name"

Window Bank/Cash > Account > Statement Loader

You need to create one record referencing the class de.bxservice.hibiscus.HibiscusLoader in one of the accounts to use this loader:

01H StatementLoader.png

Window Bank Statement Matcher

If you plan to use the bank statement matchers you need to create one record for each matcher:

  • Hibiscus Matcher Customer Invoice in Memo
    • Class de.bxservice.hibiscus.HibiscusMatcherCustomerInvoiceInMemo
  • Hibiscus Matcher Vendor SEPA Payment
    • Class de.bxservice.hibiscus.HibiscusMatcherVendorSEPAPayment

02H BankStatementMatcher.png

System Configurator

The plugin manages the following System Configurator keys:

  • BXS_HIBISCUS_STATEMENT_DESCRIPTION: Optional, if provided it will fill the description of the bank statement with this value
  • BXS_HIBISCUS_VALIDATE_DUPS_UMSATZID: Optional, by default the Umsatz_ID is validated to avoid loading duplicates, this is loaded and validated against the column EftTrxID, setting to N disables this validation (and potentially will allow loading the same Hibiscus record twice)
  • BXS_SALES_INVOICE_MATCH_REGEX: Required, regular expression that is used to search the invoice numbers within the EftMemo field
    • For example, if the customer invoice numbers have the prefix "BXS-" and the invoice number has a length of 6 digits (example invoice number BXS-453127), the regular expression can be defined as (BXS-[0-9]{6}), if there is more than one pattern, you can add several separated by commas
  • BXS_DATE_RANGE_MATCHER: Optional, when matching vendor SEPA payments, the date is compared with the fields Statement Line Date and Effective Date, this parameter optionally adds a number of days as a threshold to search in date ranges

Usage

Load Bank Statement

Simply select the Hibiscus Loader in the Load Bank Statement process and upload your CSV file.

The process will stop and show errors for example if:

  • The bank cannot be found in the data
  • The Umsatz_ID of one record was already loaded (searching for EftTrxID in the tables I_BankStatement and C_BankStatementLine)

When the process finish successfully, it shows the success message informing the number of rows imported.

03H LoadBankStatement.png

Import Bank Statement

The next step of the process is the normal in iDempiere:

  • open the Import Bank Statement window
  • check
  • run the process Import Bank Statement from gear button
  • if you are not satisfied with the records imported, the complete table can be cleared using the process Delete Import and selecting the table I_BankStatement_Import Bank Statement

04H ImportBankStatement.png

05H ImportBankStatementParams.png

Bank/Cash Statement

After the bank statement is imported you can use the buttons Match Bank Statement and Create Payment.

The process can be as follows:

  • open the Bank/Cash Statement window
  • Run Match Bank Statement (explained below)
  • Run Create Payment if necessary (explained below)
  • Navigate to Statement Line and match manually the lines that were not matched automatically, here the user can:
    • Select manually a Customer Invoice that matches the line (for positive lines)
    • Select manually a Vendor Payment that matches the line (for negative lines). NOTE: other companies can have a different process not paying via SEPA, in such case the payments must be created in a different way and matched here
    • Move the amount from Transaction Amount to Charge Amount and select a Charge. This is for example the method that must be used for bank expenses
    • Move the amount from Transaction Amount to Interest Amount. This must be done when the line represents interest revenues from the account
  • After all lines are matched the user can proceed to complete the Bank Statement

IMPORTANT NOTE: This plugin adds a validator that forbids the bank statement to be completed when it founds:

  • Lines with Transaction Amount, no Payment, no Invoice assigned. In this case, the line must be matched to an invoice, charge or interest.
  • Lines with Transaction Amount, no Payment, and an Invoice assigned. In this case, a payment must be matched, or created using the button.

06H BankCashStatement.png

Match Bank Statement

This process can be executed for the complete bank statement or for a specific line:

  • For positive lines:
    • It tries to match the customer invoices searching for invoice numbers in the field EftMemo based on the SysConfig BXS_SALES_INVOICE_MATCH_REGEX, with the same payment amount
    • It informs the user in the Description field:
      • when an exact match is found
      • when an invoice is found but the payment amount doesn't match the expected value
      • when several invoices are found, the invoice cannot be matched in this case, the user needs to create the payment and match it manually
  • For negative lines:
    • It tries to match the vendor SEPA payments searching for payments with the same amount, business partner and date (a threshold is possible using SysConfig BXS_DATE_RANGE_MATCHER)

Create Payment

This process can be executed for the complete bank statement or for a specific line:

This is the normal process from iDempiere.

  • It creates payments based on the information on the statement lines: invoice, date, bank, business partner, etc.

Technical Information

MAPPING:
+-------------------------+------------------+-----------------------+
|    Hibiscus DB          |    CSV File      |    I_BankStatement    |
+-------------------------+------------------+-----------------------+
| konto.kontonummer       | Konto_AccountNo  | BankAccountNo         |
+-------------------------+------------------+-----------------------+
| konto.bic               | Konto_RoutingNo  | RoutingNo             |
|                         |                  | C_BankAccount_ID      |
+-------------------------+------------------+-----------------------+
| konto.id                | Konto_Id         |                       |
+-------------------------+------------------+-----------------------+
| umsatz.id               | Umsatz_Id        | EftTrxID              |
+-------------------------+------------------+-----------------------+
| umsatz.empfaenger_konto | Empfaenger_Konto | EftPayeeAccount       |
+-------------------------+------------------+-----------------------+
| umsatz.empfaenger_blz   | Empfaenger_Blz   | EftCheckNo            |
+-------------------------+------------------+-----------------------+
| umsatz.empfaenger_name  | Empfaenger_Name  | EftPayee              |
+-------------------------+------------------+-----------------------+
| umsatz.betrag           | Betrag           | StmtAmt               |
|                         |                  | EftAmt                |
+-------------------------+------------------+-----------------------+
| umsatz.zweck            | Zweck            | EftMemo               |
| umsatz.zweck2           | Zweck2           |                       |
| umsatz.zweck3           | Zweck3           |                       |
+-------------------------+------------------+-----------------------+
| umsatz.datum            | Datum            | StatementLineDate     |
|                         |                  | EftStatementLineDate  |
+-------------------------+------------------+-----------------------+
| umsatz.valuta           | Valuta           | ValutaDate            |
|                         |                  | EftValutaDate         |
+-------------------------+------------------+-----------------------+
| umsatz.kommentar        | Kommentar        | LineDescription       |
+-------------------------+------------------+-----------------------+
| umsatz.checksum         | Checksum         | TrxType               |
+-------------------------+------------------+-----------------------+
| umsatz.gvcode           | GvCode           | EftTrxType            |
+-------------------------+------------------+-----------------------+
| umsatz.endtoendid       | EndToEndId       | EftReference          |
+-------------------------+------------------+-----------------------+
| umsatz.mandateid        | MandateId        | ReferenceNo           |
+-------------------------+------------------+-----------------------+
| umsatz.primanota        | PrimaNota        | Memo                  |
| umsatz.art              | Art              |                       |
| umsatz.customerref      | CustomerRef      |                       |
| umsatz.addkey           | AddKey           |                       |
| umsatz.txid             | TxId             |                       |
| umsatz.purposecode      | PurposeCode      |                       |
| umsatz.empfaenger_name2 | Empfaenger_Name2 |                       |
| umsatztyp.name          | UmsatzTyp_Name   |                       |
+-------------------------+------------------+-----------------------+
| Other Fields            | FileName         | EftStatementReference |
|                         +------------------+-----------------------+
|                         | Load Timestamp   | Name                  |
|                         |                  | StatementDate         |
|                         +------------------+-----------------------+
|                         | SysConfig        | Description           |
+-------------------------+------------------+-----------------------+
Cookies help us deliver our services. By using our services, you agree to our use of cookies.