openerp-expert-accounting team mailing list archive

[Ferdinand Gassauer <office@xxxxxxxxxx>]

Hello 
I have created a patch for 5.0 "Entries by statement" 

why?
because we need VAT also in bank (fees) and petty cash and this solution 
worked for us the last 15 years or so.

it does the following:

much more comfortable data entry (not perfect yet)
* date selects period
* journal selects next "name" (similar to invoices) on yearly basis
* Entry Encoding
** Partner selects type - if defined as supplier OR customer
** amount entry sets line date

bank statement allows now VAT entry by line
 * fetches VAT from account
 * calculates VAT if tax is entered and / or amount is changed
 * does not allow VAT if partner defined
 * checks that net+vat = amount
 * creates bank,net, vat move_lines witch correct tax codes
 * added analytic account to bank statement line

the patch is against branch 5.0 revision 2697

I will propose this for V.6 after testing during the next weeks to become 
standard as I do believe that banks and cash is managed at least in Europe 
very similar.

let me know what you think and if it is useful to you.

PS - I know there might be some python glitches - but I do not know better.
Suggestions are welcome.
-- 
regards
Ferdinand Gassauer
ChriCar Beteiligungs- und Beratungs- GmbH
Official OpenERP Partner

--- /home/terp/OpenERP/official/addons/account/account_bank_statement.py	2010-02-23 08:06:40.836422174 +0100
+++ account_bank_statement.py	2010-04-10 22:08:49.977618968 +0200
@@ -25,11 +25,15 @@ from osv import fields, osv
 
 from tools.misc import currency
 from tools.translate import _
+from tools import config
 
 import mx.DateTime
 from mx.DateTime import RelativeDateTime, now, DateTime, localtime
 
 
+#----------------------------------------------------------------------------
+# Account Bank Statement
+#----------------------------------------------------------------------------
 class account_bank_statement(osv.osv):
     def _default_journal_id(self, cr, uid, context={}):
         if context.get('journal_id', False):
@@ -107,16 +111,24 @@ class account_bank_statement(osv.osv):
     _name = "account.bank.statement"
     _description = "Bank Statement"
     _columns = {
-        'name': fields.char('Name', size=64, required=True, states={'confirm': [('readonly', True)]}),
+        'name': fields.char('Name', size=64, required=True,
+            help ="Keep empty to use automated numbering",
+            states={'confirm': [('readonly', True)]}),
         'date': fields.date('Date', required=True,
+            help = "Date of bank statement, used to propose period",
             states={'confirm': [('readonly', True)]}),
         'journal_id': fields.many2one('account.journal', 'Journal', required=True,
+            help="""Create journals for every bank and cash account and associate yearly sequences
+Yearly numbering for banks works only if companies fiscal year is a calandar year""",
             states={'confirm': [('readonly', True)]}, domain=[('type', '=', 'cash')]),
         'period_id': fields.many2one('account.period', 'Period', required=True,
+            help="Period is used for creating account moves in selected period",
             states={'confirm':[('readonly', True)]}),
         'balance_start': fields.float('Starting Balance', digits=(16,2),
+            help="must be entered once for the first statement of each journal", 
             states={'confirm':[('readonly',True)]}),
         'balance_end_real': fields.float('Ending Balance', digits=(16,2),
+            help="must be entered and will be compared to calculated result",
             states={'confirm':[('readonly', True)]}),
         'balance_end': fields.function(_end_balance, method=True, string='Balance'),
         'line_ids': fields.one2many('account.bank.statement.line',
@@ -132,13 +144,14 @@ class account_bank_statement(osv.osv):
     }
 
     _defaults = {
-        'name': lambda self, cr, uid, context=None: \
-                self.pool.get('ir.sequence').get(cr, uid, 'account.bank.statement'),
-        'date': lambda *a: time.strftime('%Y-%m-%d'),
+        # FIXME most defaults are return unusable values in daily life
+        #'name': lambda self, cr, uid, context=None: \
+        #        self.pool.get('ir.sequence').get(cr, uid, 'account.bank.statement'),
+        #'date': lambda *a: time.strftime('%Y-%m-%d'),
         'state': lambda *a: 'draft',
         'balance_start': _default_balance_start,
-        'journal_id': _default_journal_id,
-        'period_id': _get_period,
+        #'journal_id': _default_journal_id,
+        #'period_id': _get_period,
     }
 
     def button_confirm(self, cr, uid, ids, context={}):
@@ -195,9 +208,29 @@ class account_bank_statement(osv.osv):
                 amount = res_currency_obj.compute(cr, uid, st.currency.id,
                         company_currency_id, move.amount, context=context,
                         account=acc_cur)
+                # FIXME
+                # need amount later for bank move line
+                amount_move = amount
+                amount_tax = 0.0
+                if move.tax_id:
+                      if move.amount <> move.amount_net + move.amount_tax:
+                          raise osv.except_osv(_('Error !'),
+                              _('VAT and Amount Net do not match Amount in line "%s"') % move.name)
+                      if move.partner_id: 
+                          raise osv.except_osv(_('Error !'),
+                              _('Lines "%s" with VAT must not have partner account ') % move.name)
+                      amount_net = res_currency_obj.compute(cr, uid, st.currency.id,
+                         company_currency_id, move.amount_net, context=context,
+                         account=acc_cur)
+                      amount_tax = amount - amount_net
+                      amount = amount_net
+
                 if move.reconcile_id and move.reconcile_id.line_new_ids:
                     for newline in move.reconcile_id.line_new_ids:
                         amount += newline.amount
+                #   
+                # move line
+                # 
 
                 val = {
                     'name': move.name,
@@ -232,9 +265,36 @@ class account_bank_statement(osv.osv):
                                 move.account_id.currency_id.id, amount, context=context,
                                 account=acc_cur)
                     val['amount_currency'] = amount_cur
+                
+                if amount_tax <> 0.0 and move.tax_id:
+                    val['tax_amount']   = amount_net
+                    val['tax_code_id'] = move.tax_id.base_code_id.id
+              
+                if move.account_analytic_id:
+                    val['analytic_account_id'] = move.account_analytic_id.id
 
                 torec.append(account_move_line_obj.create(cr, uid, val , context=context))
 
+                # VAT Move line                 
+                if amount_tax <> 0.0 and move.tax_id:
+                    account_move_line_obj.create(cr, uid, {
+                    'name': move.name,
+                    'date': move.date,
+                    'ref': move.ref,
+                    'move_id': move_id,
+                    'partner_id': ((move.partner_id) and move.partner_id.id) or False,
+                    'account_id':  move.tax_id.account_collected_id.id,
+                    'credit': ((amount_tax>0) and  amount_tax) or 0.0,
+                    'debit' : ((amount_tax<0) and -amount_tax) or 0.0,
+                    'statement_id': st.id,
+                    'journal_id': st.journal_id.id,
+                    'period_id': st.period_id.id,
+                    'currency_id': st.currency.id,
+                    'tax_amount': amount_tax,
+                    'tax_code_id': move.tax_id.tax_code_id.id,
+                    } , context=context)
+
+
                 if move.reconcile_id and move.reconcile_id.line_new_ids:
                     for newline in move.reconcile_id.line_new_ids:
                         account_move_line_obj.create(cr, uid, {
@@ -254,6 +314,9 @@ class account_bank_statement(osv.osv):
 
                 # Fill the secondary amount/currency
                 # if currency is not the same than the company
+                # Bank Move Line
+                # reset amount to what is was before VAT calculation
+                amount = amount_move
                 amount_currency = False
                 currency_id = False
                 if st.currency.id <> company_currency_id:
@@ -319,7 +382,7 @@ class account_bank_statement(osv.osv):
         self.write(cr, uid, done, {'state':'draft'}, context=context)
         return True
 
-    def onchange_journal_id(self, cursor, user, statement_id, journal_id, context=None):
+    def onchange_journal_id(self, cursor, user, statement_id, journal_id,period_id,name, context=None):
         if not journal_id:
             return {'value': {'currency': False}}
 
@@ -341,7 +404,39 @@ class account_bank_statement(osv.osv):
                     context=context).company_id.currency_id.id
         currency = res_currency_obj.name_get(cursor, user, [currency_id],
                 context=context)[0]
-        return {'value': {'balance_start': balance_start, 'currency': currency}}
+        # FIXME get next Journal number here
+        #'name': lambda self, cr, uid, context=None: \
+        #        self.pool.get('ir.sequence').get(cr, uid, 'account.bank.statement'),
+        # context journal_id, period_id
+        # TODO
+        # check for existing account.sequence.fiscal_year sequence
+        # update next_number
+        if period_id and not name:
+            period_ids = self.pool.get('account.period').search(cursor,user,[('id' ,'=', period_id)])
+            period_obj = self.pool.get('account.period').browse(cursor,user,period_ids)[0]
+            if period_obj:
+                fiscalyear_id = period_obj.fiscalyear_id.id
+            else:
+                raise osv.except_osv(_('Error'), _('No fiscalyear is defined for this period '))
+
+            # FIXME for numbering the bank statements we use the invoice sequence
+            # for numbering the moves the accout journal move sequence shouzld be used
+            sequence_main_id = account_journal_obj.browse(cursor, user, journal_id,
+                context=context).invoice_sequence_id.id
+            if sequence_main_id:
+                sequence_ids = self.pool.get('account.sequence.fiscalyear').search(cursor,user,[('fiscalyear_id','=', fiscalyear_id),('sequence_main_id' ,'=', sequence_main_id)])
+                #sequence_ids = self.pool.get('account.sequence.fiscalyear').search(cursor,user,[('sequence_main_id' ,'=', sequence_main_id)])
+                #sequence_ids = self.pool.get('account.sequence.fiscalyear').search(cursor,user,[('fiscalyear_id','=', fiscalyear_id)])
+            if sequence_ids :
+                sequence_obj = self.pool.get('account.sequence.fiscalyear').browse(cursor,user,sequence_ids)[0]
+                sequence_id = sequence_obj.sequence_id.id
+            else:
+                sequence_id = sequence_main_id
+            
+            name =  self.pool.get('ir.sequence').get_id(cursor,user,  sequence_id )
+
+            
+        return {'value': {'balance_start': balance_start, 'currency': currency, 'name':name}}
 
     def unlink(self, cr, uid, ids, context=None):
         stat = self.read(cr, uid, ids, ['state'])
@@ -353,10 +448,24 @@ class account_bank_statement(osv.osv):
                 raise osv.except_osv(_('Invalid action !'), _('Cannot delete bank statement which are already confirmed !'))
         osv.osv.unlink(self, cr, uid, unlink_ids, context=context)
         return True
+        
+    def onchange_date(self, cursor, user, ids, date):
+        if date:
+            period_ids= self.pool.get('account.period').search(cursor,user,[('date_start','<=',date),('date_stop','>=',date )])
+            if len(period_ids):
+                period_id=period_ids[0]
+            else:
+                raise osv.except_osv(_('Error'), _('No period is defined for this date '))
+
+            return {'value': {'period_id':period_id}}
+
     
 account_bank_statement()
 
 
+#----------------------------------------------------------------------------
+# Account Bank Statement Reconcile
+#----------------------------------------------------------------------------
 class account_bank_statement_reconcile(osv.osv):
     _name = "account.bank.statement.reconcile"
     _description = "Statement reconcile"
@@ -510,6 +619,9 @@ class account_bank_statement_reconcile(o
     }
 account_bank_statement_reconcile()
 
+#----------------------------------------------------------------------------
+# Account Bank Statement Reconcile Line
+#----------------------------------------------------------------------------
 class account_bank_statement_reconcile_line(osv.osv):
     _name = "account.bank.statement.reconcile.line"
     _description = "Statement reconcile line"
@@ -524,6 +636,9 @@ class account_bank_statement_reconcile_l
     }
 account_bank_statement_reconcile_line()
 
+#----------------------------------------------------------------------------
+# Account Bank Statement Line
+#----------------------------------------------------------------------------
 
 class account_bank_statement_line(osv.osv):
 
@@ -542,22 +657,44 @@ class account_bank_statement_line(osv.os
 
         part = self.pool.get('res.partner').browse(cursor, user, partner_id,
                 context=context)
-        if type == 'supplier':
+ 
+        # quick FIXME check what partner is defined in partner and override input
+        if type == 'partner' :  # trigger runs from partner
+            type = 'general'
+            if not part.supplier and not part.customer :
+                print 'Partner should be customer and/or supplier'  
+                
+            if part.supplier == True and part.customer == True :
+                #type = '' # manual entry necessary FIXME - currently general remains in this case visible - no way to get the select field empty
+                type = 'general'
+                print 'Wizard missing - User has to choose category'  
+            else:
+                if part.supplier == True :
+                    type = 'supplier'
+                if part.customer == True :
+                    type = 'customer'
+       
+        account_id = ''
+        if type == 'supplier': 
             account_id = part.property_account_payable.id
-        else:
+        if type == 'customer': 
             account_id =  part.property_account_receivable.id
-
-        cursor.execute('SELECT sum(debit-credit) \
+     
+        balance = 0.0 
+        if account_id:
+            cursor.execute('SELECT sum(debit-credit) \
                 FROM account_move_line \
                 WHERE (reconcile_id is null) \
                     AND partner_id = %s \
                     AND account_id=%s', (partner_id, account_id))
-        res = cursor.fetchone()
-        balance = res and res[0] or 0.0
+            res = cursor.fetchone()
+            balance = res and res[0] or 0.0
 
-        balance = res_currency_obj.compute(cursor, user, company_currency_id,
+            balance = res_currency_obj.compute(cursor, user, company_currency_id,
                 currency_id, balance, context=context)
-        return {'value': {'amount': balance, 'account_id': account_id}}
+        
+        return {'value': {'amount': balance, 'account_id': account_id,'type': type,
+                 'tax_id':'', 'amount_tax':'', 'amount_net':'' }}
 
     def _reconcile_amount(self, cursor, user, ids, name, args, context=None):
         if not ids:
@@ -582,15 +719,21 @@ class account_bank_statement_line(osv.os
     _name = "account.bank.statement.line"
     _description = "Bank Statement Line"
     _columns = {
-        'name': fields.char('Name', size=64, required=True),
-        'date': fields.date('Date', required=True),
-        'amount': fields.float('Amount'),
+        'name': fields.char('Name', size=64, required=True,
+            help="""automatically adds partner name for general ledger moves"""),
+        'date': fields.date('Date', required=True,
+             help="Keep empty to auto copy date of statement"),
+        'amount': fields.float('Amount', required=True, digits=(16, int(config['price_accuracy'])),
+            help="""positive for cash inflow, negative for cash outflow"""),
         'type': fields.selection([
             ('supplier','Supplier'),
             ('customer','Customer'),
             ('general','General')
-            ], 'Type', required=True),
-        'partner_id': fields.many2one('res.partner', 'Partner'),
+            ], 'Type', required=True,
+            help="""Select 'General' for General Ledger or Customer/Supplier for partners.
+Based on this payables or receivable account defined in partner or properties is selected."""),
+        'partner_id': fields.many2one('res.partner', 'Partner',
+            help="""Customer/Supplier settings will be taken from partner if one and only one box is checked."""),
         'account_id': fields.many2one('account.account','Account',
             required=True),
         'statement_id': fields.many2one('account.bank.statement', 'Statement',
@@ -600,20 +743,88 @@ class account_bank_statement_line(osv.os
         'move_ids': fields.many2many('account.move',
             'account_bank_statement_line_move_rel', 'move_id','statement_id',
             'Moves'),
-        'ref': fields.char('Ref.', size=32),
+        'ref': fields.char('Ref.', size=32,
+            help="""Usually references the move to be reconciled"""),
         'note': fields.text('Notes'),
         'reconcile_amount': fields.function(_reconcile_amount,
             string='Amount reconciled', method=True, type='float'),
+        'tax_id': fields.many2one("account.tax","Tax",
+            help="VAT for this line, only allowed if no partner specified"),
+        'amount_net': fields.float('Amount Net', digits=(16, int(config['price_accuracy'])),
+            help="""Amount Net"""),
+        'amount_tax': fields.float('Amount Tax', digits=(16, int(config['price_accuracy'])),
+            help="""Amount Tax"""),
+        'account_analytic_id':  fields.many2one('account.analytic.account', 'Analytic Account',
+            help="Should only be used for P&amp;L accounts"),
+
+        
     }
     _defaults = {
-        'name': lambda self,cr,uid,context={}: self.pool.get('ir.sequence').get(cr, uid, 'account.bank.statement.line'),
-        'date': lambda *a: time.strftime('%Y-%m-%d'),
+        # FIXME 
+        #'name': lambda self,cr,uid,context={}: self.pool.get('ir.sequence').get(cr, uid, 'account.bank.statement.line'),
+        # FIXME most defaults are return unusable values in daily life
+        #'date': lambda *a: time.strftime('%Y-%m-%d'),
         'type': lambda *a: 'general',
     }
 
-account_bank_statement_line()
+    def onchange_amount(self, cursor, user, ids, date, date_statement,tax_id=False,amount=False):
+        res = {'value' : {'x':'y'}}
+        if not date:
+            date = date_statement
+            res['value']['date'] = date
+
+        if tax_id:
+           tax_obj = self.pool.get('account.tax').browse(cursor, user,tax_id)
+           if tax_obj.type == 'percent' and tax_obj.tax_group == 'vat':
+               amount_net = round(amount / (1 + tax_obj.amount),int(config['price_accuracy']))
+               res['value']['amount_net'] = amount_net
+               res['value']['amount_tax']  = amount - amount_net
 
+        return res
+           
 
+    def onchange_account(self, cursor, user, ids, account_id):
+        result = {}
+        account_obj = self.pool.get('account.account').browse(cursor, user,account_id)
+        tax_id = ''
+        if len(account_obj.tax_ids): 
+            #tax_ids = self.pool.get('account.tax').search(cursor, user, account_obj.tax_ids)
+            #tax_id = tax_ids[0].id
+            cursor.execute( 'select tax_id from account_account_tax_default_rel where account_id = %s limit 1;', (account_id,))
+            res = cursor.fetchone()
+            tax_id = (res and res[0]) or False
+        result = {'value': {
+            'tax_id': tax_id,
+            }
+        }
+        return result 
+        
+    def onchange_tax(self, cursor, user, ids, tax_id, amount ,partner_id): 
+        result = {}
+        amount_net = 0.0
+        amount_tax = 0.0
+        if tax_id:
+            if partner_id:
+                #raise osv.except_osv(_('Error!'),
+                #  _('VAT not allowed for moves lines with partner'))           
+                tax_id = ''
+            else: 
+                tax_obj = self.pool.get('account.tax').browse(cursor, user,tax_id)
+                if tax_obj.type == 'percent' and tax_obj.tax_group == 'vat':
+                    amount_net = round(amount / (1 + tax_obj.amount),int(config['price_accuracy']))
+                    amount_tax = amount - amount_net
+                else:
+                    raise osv.except_osv(_('Error!'),
+                       _('only tax group VAT with percentage supported')) 
+        result = {'value': {
+            'tax_id': tax_id,
+            'amount_net': amount_net,
+            'amount_tax': amount_tax,
+            }
+        }
+        return result
+
+account_bank_statement_line()
 
 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
 

--- /home/terp/OpenERP/official/addons/account/account_view.xml	2010-04-06 08:29:45.091463943 +0200
+++ account_view.xml	2010-04-10 22:40:46.132209660 +0200
@@ -317,8 +328,8 @@
             <field name="arch" type="xml">
                 <form string="Statement">
                     <field name="name" select="1"/>
-                    <field name="date" select="1"/>
-                    <field name="journal_id" on_change="onchange_journal_id(journal_id)" select="1"/>
+                    <field name="date" on_change="onchange_date(date)" select="1"/>
+                    <field name="journal_id" on_change="onchange_journal_id(journal_id,period_id,name)" select="1"/>
                     <field name="currency"/>
                     <field name="period_id" select="2"/>
                     <group colspan="2" col="3">
@@ -330,34 +341,41 @@
                     <field name="balance_end_real"/>
                     <notebook colspan="4">
                         <page string="Entry encoding">
-                            <field colspan="4" name="line_ids" nolabel="1">
-                                <tree editable="bottom" string="Statement lines">
+                            <field colspan="4" name="line_ids" nolabel="1" attrs="{'readonly':[('state','!=','draft')]}">
+                                <tree editable="bottom" string="Statement lines" >
                                     <field name="date"/>
                                     <field name="ref"/>
                                     <field name="name"/>
-                                    <field name="type"/>
-                                    <field name="partner_id" on_change="onchange_partner_id(partner_id, type, parent.currency)"/>
-                                    <field domain="[('journal_id','=',parent.journal_id)]" name="account_id"/>
-                                    <field name="amount"/>
+                                    <field name="partner_id" on_change="onchange_partner_id(partner_id, 'partner' , parent.currency)"/>
+                                    <field name="type" on_change="onchange_partner_id(partner_id, type, parent.currency)"/>
+                                    <field domain="[('journal_id','=',parent.journal_id), ('type', '&lt;&gt;', 'view')]" name="account_id" on_change="onchange_account(account_id)"/>
+                                    <field name="account_analytic_id" attrs="{'invisible':[('partner_id','=', True)]}" />
+                                    <field name="tax_id" on_change="onchange_tax(tax_id,amount,partner_id)"/>
+                                    <field name="amount"  on_change="onchange_amount(date,parent.date,tax_id,amount)"/>
                                     <field context="{'partner_id': partner_id, 'amount': amount, 'account_id': account_id, 'currency_id': parent.currency, 'journal_id': parent.journal_id, 'date':date}" name="reconcile_id"/>
                                     <field invisible="1" name="reconcile_amount"/>
+                                    <field name="amount_tax" />
+                                    <field name="amount_net" />
                                 </tree>
-                                <form string="Statement lines">
+                                <form string="Statement lines" >
                                     <field name="date"/>
                                     <field name="name"/>
-                                    <field name="type"/>
-                                    <field name="partner_id" on_change="onchange_partner_id(partner_id, type, parent.currency)"/>
-                                    <field domain="[('journal_id', '=', parent.journal_id), ('type', '&lt;&gt;', 'view')]" name="account_id"/>
-                                    <field name="amount"/>
+                                    <field name="partner_id" on_change="onchange_partner_id(partner_id, 'partner' , parent.currency)"/>
+                                    <field name="type" on_change="onchange_partner_id(partner_id, type, parent.currency)"/>
+                                    <field domain="[('journal_id', '=', parent.journal_id), ('type', '&lt;&gt;', 'view')]" name="account_id" on_change="onchange_account(account_id)"/>
+                                    <field name="amount" on_change="onchange_amount(date,parent.date,tax_id,amount)" />
                                     <field context="{'partner_id':partner_id,'amount':amount,'account_id':account_id,'currency_id': parent.currency,'journal_id':parent.journal_id, 'date':date}" name="reconcile_id"/>
                                     <field name="ref"/>
-                                    <separator colspan="4" string="Notes"/>
+                                    <field name="tax_id" on_change="onchange_tax(tax_id,amount,partner_id)" />
+                                    <field name="amount_tax"  attrs="{'invisible':[('partner_id','=', True)]}" />
+                                    <field name="account_analytic_id" attrs="{'invisible':[('partner_id','=', True)]}" />
+                                    <field name="amount_net" attrs="{'invisible':[('partner_id','=', True)]}" />
                                     <field colspan="4" name="note" nolabel="1"/>
                                 </form>
                             </field>
                         </page>
                         <page string="Real Entries">
-                            <field colspan="4" name="move_line_ids" nolabel="1"/>
+                            <field colspan="4" name="move_line_ids" nolabel="1" attrs="{'readonly':[('state','!=','draft')]}"/>
                         </page>
                     </notebook>
                     <group col="7" colspan="4">