Ease invoice payment from statement

After improving the statment party rule matching we are still feeling that the statment workflow can be improved from the user experience point of view.

We have the problem that the Norm43 import from the Spanish banks does not have any structured text to match the invoices. Some people include them on the description but it depends on the party and also if the bank includes such information, so most of the time we just have the party name and the amount paid. But I guess, we are not the only country having such issues, so any improvement on this side will also help other formats without so much information.

The current situation is that after appling the rules, the user gets a list of parties without amounts but unrelated to invoices. Then they need to manually fill the invoice in order to avoid the reconciliation process or just reconcile all the lines latte. This is a lot of work if the number of invoices is big so I’m wondering if we should find a way to automate them.

We’ve seen two cases that it’s possible to know which invoice the payment is realted but the invoice field is left empty:

  1. When there is a single invoice for the party to be paid.
  2. When there are several pending lines to pay of diferents amounts, but the line amount matches the amount of one of them. In this case, if the lines is related to an invoice, we can fill the invoice to indicate that is a partiall payment.

In both cases, we can just make a search on the party lines to pay, to see if any matches by the amount and pick it’s invoice if found. Do you think this will be something that can be included on base module? If so, doest it make sense to have a flag on the rule to activate the feature (to avoid unexpected searches) or we should do it on all lines?

There the account_payment workflow is also which we should consider (specially when not using the clearing feature). Let me add a real example from one of our customers:

  1. They are generating nearly 300 invoices each month (grouping all sales of the month into a single invoice)
  2. Most of the invoices are paid with SEPA transfer, but they have diferent due dates, so for each month they have 5 or 6 payment groups with diferent amounts.
  3. When receiving the money in the statment, they get a single origin for the amount of each payment group.

Currently, each user should create a line for each customer/invoice included on the group. Following our example, this means creating between 50 or 60 lines to split the origin for each customer. Main issue here is that we already know which customers (and which amounts) are included on the origin because they are related to a single group. So the idea is to ask the user for a payment group and include a line for each payment that is included on it (or even with just the payments that are still processing). Do you think this should be a nice feature?

We are not using the account_statement_clearing because it requires to not have a party on it’s moves, but this is not the case of the related accounts of the Spanish chart. There is a related issue, but I’m still not sure if removing the flag for the accounts is the right solution.

Any comments will be much appreciated.

I see no difference with the reconciliation wizard. So for me we must not implement twice the same mechanism.

Normally they should receive a CAMT.053: Issue 4658: CAMT053 SEPA Statement module - Tryton issue tracker

I do not agree. You do not know which payments succeeded or not.

I do not think. For me the correct workflow is to use the clearing and follow the status of each payment with the CAMT.053.

Well main diference here is that the wizard should be done party by party, while when appling the rules it applies to a set of moves (which may be 50 or 60 parties).
Having the invoices set of 50 lines in a single click is much faster than using the doing 50 steps of the reconcile wizard. We are talking about 1 click vs 50 clicks which is a lot in user time. This is a big difference on such repetetive and boring tasks.

Maybe we should find a way to reuse the reconcile wizard logic in the statement rules.

In case all payments are included on a single line you know (all).
In case there is some failing payment, you should be able to know if by the combinations of amounts. And in case there are severall payments with same amount and a single one failed we can set the payments of the other amount and leave the unkwon amounts empty so the user will just need to enter the right ones but most of the work is already done by the system.

That’s not the case, we receive a single line on the bank statement

I failed to found any option to export the CAMT.053 file on our bank and from what I see on the old wiki blueprint it just contains the same statement information as our current format. So I do not see how using this file will make any diference as we will just get a single line with all the

This is not true because user will still have to validate each line because you can not base the accounting on guessing. Or you are managing customer accounts in global and so you do not care about specific reconciliation.

You can not base the design on a special case.

Guessing is not a correct method.

Complaint about the bank. They must provide status of each payment (or change for a real bank).

Indeed it is the CAMT.054 with the proper information.

They already do but they provide this information on their website and do not provide any way to export this information using a standard format. I can complaint the bank if you like, but we will not solve anything by just complaining.

Of course not, the system should not guess for the user but just suggest the best values.
I just mentioned what I think is a good sugestion algorithm, we can find better sugestions.

Maybe it’s worth to create another wizard that will do some search on the system to see if a massive search can be done and suggests the values to the user (before creating any line) so he will just need to check the correctness before doing anything.

5 clicks vs 50 clicks will be also a good improvement.

Maybe tools like weboob can extract the data.

It is always good to complaint about missing standard.

45 suggestions still requires 45 validation/clicks.

Not so easy. It requires authentication and also each bank has a diferent website. So we will endup implementing a solution for each diferent bank which will be unmaintenable.

I complained to my bank for other issues without any reply (and it had been 6 months so far). So I do not think I will have a diferent reply for this topic. Also I need to open an account on each bank in Spain (as each bank will need to implement the standard). Sincerly, I doubt of my success before starting such long journey.

I’m here complaining about some pain cases of the usage of the system and I just get a response to “Please go to complain to another place”. I do no think we will improve anything with this actitude :pensive:

Ok, so lets count correctly. Currently I get a line with an amount and not party. This line is related to 50 parties which diferents amounts each. Creating a line for each party, entering the amount and setting the invoices takes at least 5 clicks (and probably more for a non advanced user). So there are 250 clicks and having the risk to enter the wrong data.

Getting 45 sugestions (1 click * suggestion) plus entering 5 moves manually (5 clicks per move) is a total of 70 clicks.

For me 70 clicks is still better than 250.

So for me there is a lot of room for improvement. If you don’t see there is anything to agree, I will prefer to end the discussion here.

Indeed we are facing the same case as Issue 10809: Allow to mark all payments in group as succeeded - Tryton issue tracker where a single statement origin is received with the full amount of the group and then if there is something that has not been succeded the bank will charge the money back.

We’ve done a quick test with the following code:


class StatementRule(metaclass=PoolMeta):                                       
    __name__ = 'account.statement.rule'                                        
                                                                               
    def apply(self, origin, keywords):                                         
        pool = Pool()                                                          
        Group = pool.get('account.payment.group')                              
        yield from super().apply(origin, keywords)                             
        for rule_line in self.lines:                                           
            if not rule_line.payment_group:                                    
                continue                                                       
            for group in Group.search([                                        
                        ('payment_complete', '=', False),                      
                        ]):                                                    
                if group.payment_amount == keywords['amount']:                 
                    yield from self.get_payment_group_lines(origin, group)     
                                                                               
    def get_payment_group_lines(self, origin, group):                          
        pool = Pool()                                                          
        Line = pool.get('account.statement.line')                              
        Invoice = pool.get('account.invoice')                                  
        for payment in group.payments:                                         
            line = Line()                                                      
            line.statement = origin.statement                                  
            line.number = origin.number                                        
            line.description = origin.description                              
            line.origin = origin                                               
            line.amount = payment.amount                                       
            line.date = origin.date                                            
            line.party = payment.party                                         
            if payment.line:                                                   
                line.account = payment.line.account                            
                if isinstance(payment.line.move.origin, Invoice):              
                    line.invoice = payment.line.move.origin                    
            else:                                                              
                if payment.amount > Decimal('0.0'):                            
                    line.account = line.party.account_receivable_used          
                else:                                                          
                    line.account = line.party.account_payable_used             
            yield line                                                         
                                                                               
                                                                               
class StatementRuleLine(metaclass=PoolMeta):                                   
    __name__ = 'account.statement.rule.line'                                   
                                                                               
    payment_group = fields.Boolean("Payment Group",                            
        help="Check to search for payment groups for the amount.")             

We did a test on a statement of one of our customers, which resulted in creating 250 lines for a statement of a single month with 3 different payment groups.

It will be great to have some standard solution to fix such case.

Normally payment group should have an identifier (like PmtInfId for SEPA). So I think the account_statement_rule module (or account_payment_clearing) should extend the rule mechanism to allow to match with payment and payment_group keywords.

I checked there different banks in Spain and I just found that:

  1. One bank is including our group number in the statement
  2. Another is using the PmtInfId number in the statement.
  3. The last is using a diferent identifier (probably because this is advancement payment so a different operation).

This means that your proposal will work for the first case but not for the others. In order to make it complete I’m wondering if we should add a reference field to the group (and fill with PmtInfId for SEPA). This will allow to match the second case automatically, but even update the reference including the last identifier for the third case.

Makes sense for me, but I think we should not require the account_payment_clearing module but:

  1. Fill the payment or the group fields if clearing is activated
  2. Fill the line with information otherwise.

Indeed a payment group can have multiple PmtInfId. So it should be stored as a list. But also there is no constraint to be globally unique but only inside one message. This make it not usable.

That’s why I was thinking on adding a reference field. So the user can update it to match the right identifier for the statement.

In case of multiple PmtInfId, tryton should match all payments and the user will be responisble of removing the non matching ones (otherwise it won’t be possible to confirm the statement).

Not sure if it’s a good idea, but this can be prevented by just searching groups with pending payments (payment_complete == False)

Indeed it would be a shame to have to manage the external reference of the bank when we provide already a unique identifier. But we could add an optional reference field but I doubt it will be often used.

It is not possible to link a statement with multiple payment lines. And this will be wrong.

This does not make it unique at all.
Only if we include the MsgId into PmtInfId that will make it unique.

Another way would be to generate UUID for both ids, to store them and use them when searching payment group by record name. This is probably the most general solution.

I think this is the righ way to go. I’ve seen some banks that they include the reference on the statement file.
We should store the uuid generated on each payment becuse on the same payment group it should be possible to have several payments with diferent identifiers (for example when convining FRST with RCUR)

Also probably we can include also the Date of the payment on the KEY of the UUID (or alow to customize it) so if the payments are paid on diferent dates we get a diferent for each statement line and everything is matched automatically.

We can not control which information the bank sends to us. For example, we have a case where the customer is requesting the advance payment of the payments and the bank uses a custom identifier for the statement. I guess this is related they are generating a new identifier for the advancement operation. In such case the reference field will be usefull.

I’m wondering which is the improvement of generating a UUID?
For me just storing the ids is enoguth as we may be sure that we will find the values latter for the statement. So there is no need to generate a random ID while we can have one predictable.

P.S: I started to work on this, I will share my work soon.

To be sure it is unique no matter the system generating it.
For example the company could have other system generate SEPA payment with its own MsgId and PmtInfId.

Leaking internal IDs outside is always a bad idea. It removed flexibility and block changing the system.

The only problem is that UUIDs are 36 chars but SEPA allow only 35. But we can remove the - from the UUID to have 32.

If filled two issues which implement what we discussed:

https://bugs.tryton.org/issue11052

https://bugs.tryton.org/issue11053