Import of remitance

[sebastienbeau]

Hi all

I would like to share with you some idea about credit card remittance, marketplace remittance.

My customer sells on 3 marketplace and also on his own website. This generate a lot of invoice per day and each day or week depending of the sale channel he receive a transfer for all the invoice paid

How I work on 6.1 and 7 version

For now we use the bank_statement_import framework combine with the module account_statement_one_move, in order to generate only one move with all the detail in the customer account (I mean one acccount.move.line per line on my credit card remittance) and a counterpart of the total amount in a transfer account (in French 581CB).

Afterwards I import the bank statement (the real on from my bank account) and it generates a move per line and in one of this line I have the total amount of the remittance that will go to the transfer account

As a picture is sometime the best explication, the flow is the following:

So with a simple exemple I have:

• 2 invoices (one with an amount of 100€ one with 50€)
• one CB remittance with 2 lines (a line with 100€ and a line with 50€)
• a bank statement with a lot of line and inside a transfer of 150€ for the sum of the CB remittance

Each invoice will be reconcile with a account.move.line of the remittance and the conterpart of the remittance will be reconcile with the account.move.line of the bank.statement

The reconciliation process is done automatically with account_easy_reconcile (and account_advanced_reconcile if need)

So it's work perfectly and Richard is an happy manager

Note : Why account_statement_one_move exist?

Note for sure we can also avoid using the module account_statement_one_move and in this case you have the following move

This flow is totaly correct in a point of view of accounting. And we can use this flow with the new bank statement framework of odoo. But the difference is in case of huge volume of data we have two issues

• odoo is slow (but we can always fix it), in a case of a remittance of 500 lines you have 1000 account.move.line instead of 501 lines
• the accountant do not like this solution, "why doing a move for each line of my remittance?" I just need a move with all the line. Indeed the first solution is easier to understand for an accountant and reading the data in the account is more easy (less lines in the transfer account)

  My personal point of view from my experience.

At the beginning my customer what using the second solution and after some complain from customer accountant, I propose and migrate one customer on the first solution. After some feedback I have migrated all of my customer on the first solution and I am read to spend time to have the same behaviour on 8 and 9 because it really worth. Moreover I can not propose to my customer to go back to the previous solution, I want to keep Richard happy !

You think is not worth

We live in a democratie and as both solution work, you are right and I am right ;). In my case I follow my customer need, so I will go in that direction (or an third solution if you have a better one ;) )

Now I need to port this to 8 and 9 version

First I really like the way we process the bank statement in version 8. But for the bank remiitance the new frameork will be not compatible with the module account_statement_one_move

With CamptoCamp we had some discussion about not processing the remittance using an account.move but with a different object (batch reconcile) that can flag the invoice in order to check what have been paid. Indeed in the real live card/marketplace remittance can have more than 500 lines so it's a lot of move line in the accounting. Avoid the generation of such amount of line can be a good thing

But as always customer have crazy flow and now I have to manage remittance that are not reconcile with only one invoice but For a same partner I can have 2 invoice with 2 payment with 2 different method like

• one invoice of 200€ and a bank transfer of 180€
• one invoice of 100€ and a CB payment of 120€ So using move (bank transfer) and batch reconcile (CB payment) will be a nightmare for solving this case.

The v7 community bank statement import is missing me :'(

What we need

In fact I do not really need v7 community bank statement import.

But what I really need is to generate an account.move from a CB remittance or a marketplace remittance. I do not need some extra object. I just need this and nothing more

What we plan to do

We want to :

• convert the module account_bank_statement_base_import into a module account_move_base_import and make it as simple as possible (no more import profile but just some extra field on the account journal). This module will be as generic as possible in order to simplify the import of account.move from accounting software, payroll....
• create a module account_move_remittance_import : This module will add some method and field on the journal for importing remittance, add the support of commission... well everything need for importing a card remittance
• create a module account_move_base_completion : This module will autocomplete the remittance based on the transaction_id or sale order name (complete the partner_id on the account.move.line)

We have just started to extract some module from this branch in order to develop the new module based on it the branch is here : https://github.com/akretion/account-move-base-import

What do you think?

[JordiBForgeFlow]

Hi, this case is similar to how I process my VISA remittances. In this case I create a Visa journal with a Visa bank account and a Visa-Bank reconciliation account.

I import the Visa remittamce into a bank statement, then reconcile the lines with the invoices. The outstanding payments are kept in the Visa account balance.

The invoices are therefore flagged as paid using the visa bank statement rec.

When I receive the real bank statement there will be a line for the total of the Visa. I then post it as: Dr. Bank Cr. Visa-Bank rec

Once I have finalized the bank rec, I must make sure that the Visa balance is 0. So I must enter a manual posting for the outstanding balance in Visa bank.

Cr. Visa Dr. Visa-Bank rec

And then reconcile the Dr. and Cr. of Visa-Bank Rec.

So Visa bank account is a transitory account that should always have 0 balance after the real bank statement reflects the total amount from the Visa

So, really no custom developments here. Just standard accounting

[fclementic2c]

@jbeficent Ok on the accounting principle. The trick here is make sure it is easy to handle high volumetry of transactions (ie : e-commerce). Let me play the devil's advocate on that one:

Odoo is so slow to create account moves that importing hundreds of card transactions is really tedious. It would be much better to simply flag SO/Invoices paid with a simple checkbox. You could create a tool to import transactions, identify them using a reference and then check the box. If a transaction is cancelled (payement refused) then you simple un tick the check box.

I add that a bank statement is a accounting document that must be translated in the ledger... but not the files or reports send by VISA an others.

[JordiBForgeFlow]

El dia 15/01/2016 8:36, "Frédéric Clementi" notifications@github.com va escriure:

@jbeficent Ok on the accounting principle. The trick here is make sure it is easy to handle high volumetry of transactions (ie : e-commerce). Let me play the devil's advocate on that one:

Odoo is so slow to create account moves that importing hundreds of card transactions is too tedious. Being a slow process does not make it an incorrect process.

It would be much better to simply flag SO/Invoices paid with a simple checkbox. You could create a tool to import transactions, identify them using a reference and then check the box.

If a transaction is cancelled (payement refused) then you simple un tick the check box.

I add that a bank statement is a accounting document that must be translated in the ledger... but not the file or reports send by VISA an others. I disagree. Visa handles for you an account for the credit card, where it keeps debits and credits. It is, in fact, an account, that is periodically cleared with the main bank. It is common to refer to the Visa Statement.

A bank statement in Odoo is a tool, not a formal accounting document. No auditor is going to look into your bank statements in Odoo used to reconcile your Visa transactions.

Example: customer invoice A for 300€. Customer pays 100€ using bank transfer, 200€ using Visa.

When you import the Visa statement, you do a partial rec for the 200€ on invoice A.

When you import the bank statement, you do a partial rec for the 100€ with invoice A, and this makes the invoice A be totally paid.

Now, how does the bank communicate that a visa transaction failed?

— Reply to this email directly or view it on GitHub.

[sebastienbeau]

Hi indeed the aim is to target hight volume.

I think I miss some extra information in my description, the big point is having only one move per remittance instead of one line per line of remittance

I have added this section : "Note : Why account_statement_one_move exist?" Hope my explication are better now.

[JordiBForgeFlow]

@sebastienbeau I don't really get it. I can understand that you have a high volume of transactions. But then that means high volume of customers as well, and you still want to know how much does each customer owe to you, right?

The whole point of managing AR and AP is to reconcile with payments made, and to have full traceability of what occurred.

If the accountant says, "I want to receive 1 line per statement", can he explain how is he going to manage the AP and AR? It's not straightforward to me at all.

[JordiBForgeFlow]

@sebastienbeau In the end, a customer invoice creates a journal entry with Dr. Accounts Receivable, and you really need to reconcile this with the corresponding payment, using another account.move.line. If you have 1000 transactions, means that you more or less have 1000 invoices to be paid. How do you expect to have less than, at least, 2000 move lines?

I see no way around this. If you have 1M invoices, you have to manage the process the same way as if you have 10 invoices.

[sebastienbeau]

Hi @jbeficent I think this is some misunderstood.

The first solution will need 501 account.move.line and 1 account.move for reconciling 500 invoice. Each invoice will be reconcile with one account.move.line.

The second solution willl generate 1000 account.move.line and 500 account.move for reconciling 500 invoice. Each invoice will be reconcile with one account.move.line.

In the both solution I reconcile each invoice with a account.move.line.

The difference is the number of account.move.line in the transfer account 1 instead of 500 account.move.line

Hope that my explication are better, tell me

[JordiBForgeFlow]

Thanks @sebastienbeau I now understand your case. IMHO you may want to create a separate object "Remittance" where you can handle the process.

Upon completion of the "Remittance", it would create: Cr. AR (partner 1) Cr. Commission Cr. AR (partner 2) Dr. Transfer Account

Finally, you would run an automated reconciliation wizard (standard Odoo or OCA) to match Dr and Cr from the AR based on partner.

I don't see a problem of a customer paying with two channels (visa and bank transfer). The AR reconciliation process would try to match whatever is left unreconciled. The AR recon process would be independent of whether the payment was received from bank or card remittance.

[sebastienbeau]

"I don't see a problem of a customer paying with two channels (visa and bank transfer)" Yes with the solution with account move there is not issue.

The issue was with an alternative object (batch reconcile) that will not generate an account move and just tick box to check what have been paid or not, but this will not work with multi payment.

This is why I want a solution with an account move so I can use standard reconciliation process to reconcile invoices with different payment method.

Now regarding the creation of an object "remittance" I was thinking about it. But maybe it's better to simply generate an account move without extra object. Let's give an exemple

1 - I import a remittance 2 - the auto-completion didn't fill all line and I fill manually the partner missing 3 - I validate it 4 - Latter I realise that the partner is the wrong one and I just want to edit the account.move.line to change it. (I have to do some extra code to update the remittance in order to be uptodate everythere)

I think it's always a pain to have duplicated information (need some extra write in case of update). So maybe just importing directly a move is enought?

Also If we go for the option "import a move without extra-object", we can builld a framework for importing remittance and also importing move from different account software or payroll, so less code ;)

[JordiBForgeFlow]

@sebastienbeau I understand. Would the remittance contain other information that is not really relevant to the account.move? Perhaps you'd want to know, when each transaction did occur in reality, or the transaction ID, but this information would not be added to the account.move.line, and thus you'd loose data.

Creating an interim model is good IMHO, as long as once you post the account.move you cannot change the originating model as well.

[sebastienbeau]

Yes it can be an issue.

In my case (in France) I import a remittance with only validaded customer payment that match with the total amount in bank. For all my customer case, I can work without adding extra field. But what I need is to always link the account_move generate to the original file

How does it work in Spain do you need extra field?

Thanks

[JordiBForgeFlow]

@sebastienbeau I think that this should be enough. In Spain your approach should be good enough.

[sebastienbeau]

Ok thank for your feedback !

[jgrandguillaume]

Hi,

Firstly, thank you to start this thread. You can count on us to helps you with this as we need a solution as much as you do :)

I share some thoughts we had around one year ago on the subject. This was how we imagine it.

Sales

• No change on this point, it is the invoice validation that create the accounting entries.

Confirm transaction (credit office, marketplace or paypal send you the remittance: confirmation that the card has been debited)

• Import those files in a similar way we did in v7 (through an import module providing generic import API and methods)
• The transactions will be stored in a new object called "Batch" (similar to old bank statement object), but that will not generate accounting entries
• We use a same kind of way than in v7 to make a completion on those lines through a similar rule system (completion module). The completion add data like partner, SO number, account, etc...
• All the lines will share a same batch_id (that will allow to find all transaction of a same batch import)

Receive cash on your bank account

• The amount is received on your bank account as a sum of a given number of transactions (e.g. on line of 5000EUR.- for 200 transactions). This amount of 5000EUR.- do not always correspond to the total amount of one batch_id => sometimes, VISA confirm you 6000EUR.-, but only pay you 4000EUR becasue of whatever reasons.
• The idea is then to have a way to match which transactions you match for the received amount in bank. A kind of batch_lines_ids that take all lines for the given 4000EUR.- Nice to have : The system can propose a selection of lines for the given amount because the timestamp are usually respected, so all we have to do is sum transaction sorted by datetime till we reach the bank amount.
• We need to deal with multiple payment terms that generate various debit and credit move lines.

Reconciliation of transactions

• Based on easy and advance reconcile -> This tools will work like before, trying to match move lines based on various rules and criteria (some simple like matching transaction_ref, other complexe like matching credit note, reimbursement, various payment method with a single line).
• We need to handle the generation of extra move sometimes (dunning fees, etc...)

Conclusion

I'm just sharing this to show what we though, but for me the main point are:

• Keep it simple (your approach is good I think)
• Manage easily the importation of batch files with hundreds lines
• Provide a way to generate new move lines for commissions, dunning fees, other (in Switzerland we can generate lines in some cases when the person is late to pay and have an automatic 10CHF.- to pay even if the real invoice amount has not change...)
• Deal with th case when the bank amount received IS NOT MATCHING a batch import of remittance
• Keep rules system in completion module
• Keep rules system in batch reconcile tools

With that in mind, I think we have something good. I think we'll have then:

• account_move_base_import
• account_move_remittance_import
• account_move_base_completion
• account_move_easyreconcile and account_move_advanced_reconcile

Or something similar.

Regards,

Joël

[elicoidal]

We have a customer in NZ that works that way. We customized the code in a similar way

[github-actions[bot]]

There hasn't been any activity on this issue in the past 6 months, so it has been marked as stale and it will be closed automatically if no further activity occurs in the next 30 days. If you want this issue to never become stale, please ask a PSC member to apply the "no stale" label.