Transcript CDCB Interface

QSEP
Quint’s SEPA converter
Introduction



QSEP was developed to simplify the SEPA conversion (from old accounts to IBAN/BIC) in
BaanIV environments
QSEP consists of 3 steps (export, convert, import). The export & import steps are
specifically designed for BaanIV. The convert step can be used in ALL environments.
Three concepts related to SEPA will be discussed:




IBAN (International Bank Account Number)
BIC (Bank Identifier Code)
Bank ID (Bank Identification)
Next the QSEP conversion will be discussed …
QSEP
BaanIVc4
17/07/2015
QSEP / Quint's SEPA converter
2
SEPA - Concepts

IBAN = International Bank Account Number. Belongs to a person/institution





The IBAN always starts with the International Country Code (2 characters)
Then a control number calculated from the BankId and the accountnumber (2 digits)
Then the BankId
Then the accountnumber. The length of the account number is set by country. If the number is too short leading zeros will be
added.
Bank identification. Two concepts:


BIC = Bank Identifier Code. The BIC is 8-11 characters long. The policy of BIC determination can differ by country & bank
BankId identifies the bank that is handling the account. The policy of BankId determination can differ by country & bank




NL : the BankId = the first 4 chars of the BIC
BE : the BankId = a 3 digits number
DE : the BankId = the BankLeitZahl
Notes on the BIC and the BankId:



17/07/2015
In some countries the BankId has always been part of the Accountnumber (Belgium, Germany). In that case determining the
IBAN is easy. In the Netherlands this is not the case. NL accountnumbers should always be sent to www.ibanbicservice.nl. This
service determines the correct BankId by checking a table with all NL accountnumbers. Then the BIC and the IBAN are sent
back
In some countries the BIC is handled in a rather complex way (Germany). Determining the correct BIC could be done feeding
the BankId and the accountnumber to a SEPA account converter. A good converter tool for DE is available at
http://www.sskm.de/tipps_hilfe/downloads_forms/software/sepa_account_converter.jsp
For other countries check www.ibancalculator.com
QSEP / Quint's SEPA converter
3
SEPA - Concepts

Notes on determining IBAN/BIC:




17/07/2015
The IBAN follows from Country, the BankId and the Accountnumber.
For a specific BankId several BICs can exist (for example a BIC for every office/location). The correct BIC
can’t be calculated. Nevertheless during payment you will have to give in the correct IBAN and BIC.
When bankinfo would be available for all bankoffices of all banks in the SEPA area (stating Country, BankId,
BIC, BankName) the SEPA conversion would be easy. This info is available at SWIFT but will only be
delivered to employees of banks.
To solve this problem a SEPA converter tool should contain a table in which the necessary bankinfo can be
accumulated.
QSEP / Quint's SEPA converter
4
QSEP – Export & Import (BaanIV)

The Export step is done using 3 queries:




Data will be imported in QSEP, customer & supplier info will be converted and exported:



Sepbnkexp exports Banks info from the BankAddresses table (tcmcs020) to file banks.csv
Sepcusexp exports BanksByCustomer info (tccom015) to file customers.csv
Sepsupexp exports BanksBySupplier info (tccom025) to file suppliers.csv
Converted customer data will be exported to exp-customers.csv
Converted supplier data to exp-suppliers.csv
The Import step is done using 2 queries:


17/07/2015
Sepcusimp imports BanksByCustomer info from exp-customers.csv into tccom015
Sepsupimp imports BanksBySupplier info from exp-suppliers.csv into tccom025
QSEP / Quint's SEPA converter
5
QSEP – Quint’s SEPA converter
17/07/2015
QSEP / Quint's SEPA converter
6
QSEP – Master data: Countries
17/07/2015
QSEP / Quint's SEPA converter
7
QSEP – Master data: Banks
17/07/2015
QSEP / Quint's SEPA converter
8
QSEP – Suppliers
17/07/2015
QSEP / Quint's SEPA converter
9
QSEP – Customers
17/07/2015
QSEP / Quint's SEPA converter
10
QSEP – Converting steps

Master data


SEPA specific settings by country are maintained in the Countries table
The Banks table can be filled from:




A banks.csv file created using the sepbnkexp query (ImportBanksOfCompany)
A banks<Country>.csv file containing bankinfo found on the internet (sometimes found at the site of the national
bank) (ImportBanksOfCountry)
Bank info entered manually / semi-automatic in the Suppliers and/or Customers form (AddBanksFromCust/Supp)
Converting Suppliers/Customers

Import Suppliers / Customers


Update Suppliers / Customers (BankId / BIC)




BIC and BankName info is searched in the Banks table and is copied to the Suppliers / Customers table
The IBAN is calculated. Sometimes the calculation can’t be done because of MS/Access limitations. This is reported
in the Remark field and then QSEP checks the Bankaccount field to see if it already contains an IBAN. If so the
Bankaccount is copied to the IBAN field
Export Suppliers / Customers

17/07/2015
Tries to fill BankId and/or BIC from the suppliers / customers info (checking the content of the BankAddress field
and/or the Bankaccount number).
For found BankId’s and/or BIC’s that don’t exist in the Banks table the BankName should be searched on the
internet and the resulting bankinfo should be copied to the Banks table (AddBanksFromCust/Supp)
Update Suppliers / Customers (IBAN/BIC/BankName)


Import the csv file that was created by sepsupexp / sepcusexp
Export the converted info to exp-suppliers.csv / exp-customers.csv
QSEP / Quint's SEPA converter
11
QSEP – Converting steps

Converting NL




NL Bankaccounts do not contain the BankId.
NL Bankaccounts should be exported to a csv file (Export NL Suppliers/Customers
IBANBIC) and this file should be fed to the IBANBIC service . The file will be completed
with the correct IBAN/BIC and will be sent back in 1 day.
Next the received file can be imported (Import NL Suppliers/Customers IBANBIC)
Converting DE




17/07/2015
This functionality was added after finding a German SEPA converter that could be used
locally (i.e on a laptop). This converter would also accept a csv file
DE Bankaccounts could be exported to a csv file (Export DE Suppliers/Customers
IBANBIC) and this file should be fed to the German converter installed locally.
Next the resulting file could be imported (Import DE Suppliers/Customers IBANBIC)
At the moment most German banks are present in the Banks table; so the calculation of
the German IBAN can now be done by QSEP
QSEP / Quint's SEPA converter
12