Importing MT940 statements

[www.mgazelle.com]

When importing MT940 statements with the bankimport module, an integrity error occurs as in the log file below.

[2009-11-24 04:08:33,390] ERROR:orm:[02]: Environment Information :
[2009-11-24 04:08:33,390] ERROR:orm:[03]: System : Linux-2.6.24-25-generic-i686-with-debian-lenny-sid
[2009-11-24 04:08:33,390] ERROR:orm:[04]: OS Name : posix
[2009-11-24 04:08:33,391] ERROR:orm:[05]: Distributor ID: Ubuntu
[2009-11-24 04:08:33,391] ERROR:orm:[06]: Description: Ubuntu 8.04.3 LTS
[2009-11-24 04:08:33,392] ERROR:orm:[07]: Release: 8.04
[2009-11-24 04:08:33,392] ERROR:orm:[08]: Codename: hardy
[2009-11-24 04:08:33,392] ERROR:orm:[09]: Operating System Release : 2.6.24-25-generic
[2009-11-24 04:08:33,393] ERROR:orm:[10]: Operating System Version : #1 SMP Tue Oct 20 07:31:10 UTC 2009
[2009-11-24 04:08:33,393] ERROR:orm:[11]: Operating System Architecture : 32bit
[2009-11-24 04:08:33,394] ERROR:orm:[12]: Operating System Locale : en_US.UTF8
[2009-11-24 04:08:33,394] ERROR:orm:[13]: Python Version : 2.5.2
[2009-11-24 04:08:33,394] ERROR:orm:[14]: OpenERP-Server Version : 5.0.6
[2009-11-24 04:08:33,395] ERROR:orm:[15]: Last revision No. & ID :
[2009-11-24 04:08:33,395] ERROR:orm:[16]: Programming error: field 'def_rec_acc' does not exist in object 'res.partner.bank' !
[2009-11-24 04:08:34,248] ERROR:orm:[01]:
[2009-11-24 04:08:34,249] ERROR:orm:[02]: Environment Information :
[2009-11-24 04:08:34,252] ERROR:orm:[03]: System : Linux-2.6.24-25-generic-i686-with-debian-lenny-sid
[2009-11-24 04:08:34,252] ERROR:orm:[04]: OS Name : posix
[2009-11-24 04:08:34,253] ERROR:orm:[05]: Distributor ID: Ubuntu
[2009-11-24 04:08:34,253] ERROR:orm:[06]: Description: Ubuntu 8.04.3 LTS
[2009-11-24 04:08:34,254] ERROR:orm:[07]: Release: 8.04
[2009-11-24 04:08:34,254] ERROR:orm:[08]: Codename: hardy
[2009-11-24 04:08:34,254] ERROR:orm:[09]: Operating System Release : 2.6.24-25-generic
[2009-11-24 04:08:34,255] ERROR:orm:[10]: Operating System Version : #1 SMP Tue Oct 20 07:31:10 UTC 2009
[2009-11-24 04:08:34,255] ERROR:orm:[11]: Operating System Architecture : 32bit
[2009-11-24 04:08:34,256] ERROR:orm:[12]: Operating System Locale : en_US.UTF8
[2009-11-24 04:08:34,256] ERROR:orm:[13]: Python Version : 2.5.2
[2009-11-24 04:08:34,257] ERROR:orm:[14]: OpenERP-Server Version : 5.0.6
[2009-11-24 04:08:34,258] ERROR:orm:[15]: Last revision No. & ID :
[2009-11-24 04:08:34,258] ERROR:orm:[16]: Programming error: field 'def_rec_acc' does not exist in object 'res.partner.bank' !
[2009-11-24 04:08:35,812] ERROR:web-services:[01]:
[2009-11-24 04:08:35,813] ERROR:web-services:[02]: Environment Information :
[2009-11-24 04:08:35,813] ERROR:web-services:[03]: System : Linux-2.6.24-25-generic-i686-with-debian-lenny-sid
[2009-11-24 04:08:35,814] ERROR:web-services:[04]: OS Name : posix
[2009-11-24 04:08:35,814] ERROR:web-services:[05]: Distributor ID: Ubuntu
[2009-11-24 04:08:35,815] ERROR:web-services:[06]: Description: Ubuntu 8.04.3 LTS
[2009-11-24 04:08:35,815] ERROR:web-services:[07]: Release: 8.04
[2009-11-24 04:08:35,815] ERROR:web-services:[08]: Codename: hardy
[2009-11-24 04:08:35,816] ERROR:web-services:[09]: Operating System Release : 2.6.24-25-generic
[2009-11-24 04:08:35,816] ERROR:web-services:[10]: Operating System Version : #1 SMP Tue Oct 20 07:31:10 UTC 2009
[2009-11-24 04:08:35,817] ERROR:web-services:[11]: Operating System Architecture : 32bit
[2009-11-24 04:08:35,818] ERROR:web-services:[12]: Operating System Locale : en_US.UTF8
[2009-11-24 04:08:35,818] ERROR:web-services:[13]: Python Version : 2.5.2
[2009-11-24 04:08:35,818] ERROR:web-services:[14]: OpenERP-Server Version : 5.0.6
[2009-11-24 04:08:35,819] ERROR:web-services:[15]: Last revision No. & ID :
[2009-11-24 04:08:35,819] ERROR:web-services:[16]: Exception in call: Traceback (most recent call last):
[2009-11-24 04:08:35,820] ERROR:web-services:[17]: File "/usr/lib/python2.5/site-packages/openerp-server/wizard/__init__.py", line 73, in execute_cr
[2009-11-24 04:08:35,820] ERROR:web-services:[18]: action_res = action(self, cr, uid, data, context)
[2009-11-24 04:08:35,820] ERROR:web-services:[19]: File "/usr/lib/python2.5/site-packages/openerp-server/addons/account_bankimport.zip/account_bankimport/wizard/bank_import.py", line 244, in _bank_import
[2009-11-24 04:08:35,821] ERROR:web-services:[20]: File "/usr/lib/python2.5/site-packages/openerp-server/osv/orm.py", line 2741, in create
[2009-11-24 04:08:35,821] ERROR:web-services:[21]: cr.execute('insert into "'+self._table+'" (id'+upd0+") values ("+str(id_new)+upd1+')', tuple(upd2))
[2009-11-24 04:08:35,822] ERROR:web-services:[22]: File "/usr/lib/python2.5/site-packages/openerp-server/sql_db.py", line 76, in wrapper
[2009-11-24 04:08:35,822] ERROR:web-services:[23]: return f(self, *args, **kwargs)
[2009-11-24 04:08:35,823] ERROR:web-services:[24]: File "/usr/lib/python2.5/site-packages/openerp-server/sql_db.py", line 120, in execute
[2009-11-24 04:08:35,823] ERROR:web-services:[25]: res = self._obj.execute(query, params)
[2009-11-24 04:08:35,823] ERROR:web-services:[26]: IntegrityError: null value in column "account_id" violates not-null constraint

[Jan Verlaan (jan-verlaan)]

Error = Programming error: field 'def_rec_acc' does not exist in object 'res.partner.bank' !
Are you sure it is a bug you did log? Because this is a variable that seems not filled. This variable contains the default receivable account defined for your company and that one seems empty.
I can not reproduce this in my environment. Please check you company setup and general ledger setup. Normally these accounts (account receivable and account payable) are defined within your chart module.

[www.mgazelle.com (mgazelle)]

There are few issues regarding this module:
1. Line 44 in bankimport.py has to be commented out as the module BeautifulSoup does not exist
2. The module does install demo data and does install 5 accounts starting with "x ..." which are not part of the chart of account of the company
3. the only accounts that you can select after the installation are those installed with the parent "Chart for Automated Tests". It is not possible to select accounts from the chart of the company (This step is performed in the res.company to define the default values)
The IntegrityError from the log above does happen even if you have specified the def accounts in the Bankimport section in the res.company
4. Parsing the :61: and the :86: was not correct from a file I downloaded from ABN Amro as an MT940 format. Rabobank was OK by the way. So asc filter was fine but MT940 was not.

I am appending an extract from the MT940 file, where I chnged just sensitive data

NBVM6543

940

NBVM6543

:20:ABN AMRO BANK NV

:25:123456798

:28:201/1

:60F:C081231EUR1264,8

:61:0901010102D26,78N192NONREF

:86:11.22.33.444 MUSTER1 B.V. BETALINGSKENM. 0000002009001955

FACTUUR F2009001955

:61:0901010102D150,N422NONREF

:86:22.33.44.456 ABN AMRO CREDITCARDS

REF. 00000738103

:61:0901010102D1100,N422NONREF

:86:22.33.44.456 M GAZELLE SALARY

:62F:D090102EUR11,98

-

[www.mgazelle.com (mgazelle)]

Regarding the parsing issue number 4, I can add one remark here, that the error is related to the MT940 data.
With some files it is working fine, with some others not

[Jan Verlaan (jan-verlaan)]

Regarding your mentioned points:
1. BeautifulSoup can not be commented out, as this package is needed by the module. You have to install it via "sudo apt-get install python-beautifulsoup" at your linux prompt.
2. When the module installs demo data, your company is set up with demodata. If you install a company without demodata, the demodata of this module will also not be installed. (This is the OpenERP standard way of installing demo data.
3. I have no problems here with real data and the l10n_nl module installed (available in addons-community and contains a working dutch chart of accounts) with other words I can not reproduce this.
4. ABN-AMRO MT940 is not working yet in account_bankimport module. Only MT940 format from Postbank and ING are supported. Unfortunately every bank seems to have their own implementation of MT940. I am working with my partner on a ABN filter, but it is not finished yet.

[www.mgazelle.com (mgazelle)]

Thank you Jan for the quick reply.
1. I did comment it out and I had no issues at all
2. OK
3. The problem here is that the module did install a new top level account without getting the demo accounts nested in the account with the ID 0 of Openerp. This is what I meant before. This way you will only be able to select accounts from the "Automated Tests" chart and it would be good to just use the chart that is already installed.
4. I agree, however if you refer to the MT940 Specs, you will see as an example that the Debit / Credit sign is at index 10 so that the line checking the debit would read:

if bank_statement[current][':61:'][10] == "D":
    st_line['account_id'] = bank['def_pay_acc']
    st_line['amount'] = - st_line['amount']

The function get_number should have a pointer set to 11 to read the amount

This is according to the specs from swift_mt940_942-1.pdf

I agree with you however that every bank is dealing differently with MT940 which makes the job a bit difficult. I think a profile per bank would a good idea.

[www.mgazelle.com (mgazelle)]

A small addition:
If you use the l10n_nl module without demo data, you will see that you can not select any account as default for rec and pay for the company. You will have to create those per hand.

For the community, I think it would be best to extend the pdf document a bit.