Plugin: BX Service Hibiscus
From iDempiere en
- Maintainer: Carlos Ruiz - BX Service GmbH
- Sponsor: BX Service GmbH
- Status: Production, working with release 9.0
- License: GPLv2
- Sources: GitHub de.bxservice.hibiscus
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:
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
- Class
- Hibiscus Matcher Vendor SEPA Payment
- Class
de.bxservice.hibiscus.HibiscusMatcherVendorSEPAPayment
- Class
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
- 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_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.
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
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.
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 (C_Payment.PayAmt vs positive C_BankStatementLine.TrxAmt), IBAN code (C_BP_BankAccount.IBAN vs C_BankStatementLine.EftPayeeAccount) and date (C_Payment.DateTrx = C_BankStatementLine.StatementLineDate or C_BankStatementLine.ValutaDate -> a threshold is possible using SysConfig BXS_DATE_RANGE_MATCHER)
- Business Partner name is compared too if the SysConfig BXS_MATCH_BP_NAME is set to Y
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 | +-------------------------+------------------+-----------------------+
