I found multiple components that help with the reconciliation of payments. For example, “M7169 Novulo Aflettering Mastercard Visa” or “M8005 Novulo Paypal Transaction Search”.
However, I have difficulties understanding the basic underlying data model that is required to enable a user, like a financial employee, to validate whether a payment has been settled successfully for a given invoice line.
Let’s say I want to build a new component to reconciliate payments for stripe.
Which records are required to automatically process and reconciliate payments within Novulo, and how do they relate?
The basic business process for the components you mentioned is as follows:
Every day payments are processed via the given payment provider
At the end of the day the sum of all payment minus the cost the provider takes into account is transferred to the bank account of the my organization.
An overview of the individual payments + costs is made available by the payment provider via a file (e.g. Ingenico) or an API (e.g. PayPal)
Without the functionality of the components an user should perform the following tasks:
When matching a bank statement the total payment amount should be transffered to a designated balance account (credit).
After receiving the payout specification a general entry should be made so the designated balance account is in balance (debit), the costs are booked (debit) and each invoice that is payed is booked (credit)
The journal entry scheme is as follows for the two steps mentioned above
Step 1 - Journal = Bank
Ledger account
Debit
Credit
Bank
10.000,-
Balance account payment provider
10.000,-
Step 2 - Journal = General
Ledger account
Debit
Credit
Balance account payment provider
10.000,-
For each payed invoice - Debtor account
11.000,-
Cost payment provider
1.000,-
The first step is often automated by creating a bank template. The second step is where the application can really make the difference. The process in the reconciliation components tries to reduce the manual labour for an user by:
Importing a payout + the transactions of a payout
Finding the bank transaction based on the pay out reference
Matching all the imported transacties to an invoice
Reconciliating the designated balance accounf for the payment provider
Thanks a lot for your response!
I hope I understood everything correctly, as finance is not my domain of expertise. Could you maybe validate whether the steps below are correct?
Also, I tried to translate each step into the required Novulo finance concepts by using M7169 as an example. Could you validate whether I got it right?
A customer wants to buy a product via a website for €100.
→ New N_Invoice + N_InvoiceLine are created based on a N_Sales + N_SalesLine
The payment provider processes the payment details. If successful, a confirmation is sent and the merchant delivers the product to the customer.
The merchant delivered the product successfully to the customer and now expects to receive €100 (minus the fee) from the payment provider.
→ By now, an N_ExpectedTransaction should be linked to the N_Invoice, right? Is it triggered by the finalization of the delivery?
At the end of the day, the payment provider transfers the sum of all transactions of the day (e.g.: €11.000) minus the transaction fee (€1000) to the bank of the merchant.
The merchant adds this large transaction into his administration by importing a bank statement.
→ A N_BankStatement leads to the creation of a new N_Transaction with at least one N_TransactionLineItem. The N_TransactionLineItem with the amount €10.000 is linked to the N_LedgerAccount that is designated to the payment provider.
Now, even though the money has been received, it is unclear for the merchant whether the large sum of €10.000 also contains the €100 from step 1. This is why reconcilitation is required.
The merchant imports all data about the individual customer transactions that make up the large transaction of €10.00 from the API of the payment provider.
→ A ReconciliationMastercardVisa (RMV) record is added for each import. I can contain multiple ReconciliationMastercardVisaPayouts (RMVpayout) records. Each RMVpayout refers to a large N_Transaction similar to the one from step 6. Each RMVpayout contains multiple ReconciliationMastercardVisaTransactions (RMVTransactions). The RMVTransactions refer to the individual customer payments that need to be matched to the N_ExpectedTransactions.
Based on the payout reference, the bank transaction of €10.000 can be matched.
→ The RMVpayout is matched and linked to the correct N_Transaction based on the payout_reference field.
Also, the expected transactions of the customer invoices can be matched based on a unique reference.
→ Each RMVtransaction is matched and linked to an N_ExpectedTransaction based on the transaction_reference field.
When all imported data has been matched successfully, the actual reconciliation can take place. After that, every € that has been received by the payment provider can be traced back to the initial customer invoice.
→ The process ‘Reconciliation Mastercard / Visa Payout - Create and finalize transaction batch’ is executed to bundle all N_ExpectedTransactions of a RMVpayout into one N_ExpectedTransactionBatch. Then, the batch is finalized and leads to a single N_Journalentry with a lot of lines. One of these lines is linked to the N_ExpectedTransaction from step 3.
Seems about right! The only 2 comments at this moment is
that the expected transaction of the invoice is the basis for sending the request to the payment provider. So this is not created at step 3 but at step 1. But the components mentioned in the conversation earlier is not responsible for step 1,2 and 3)
after transferring the N_ExpectedTransactionBatch (thus step 11) the two journalentrylines (one via the bank journal and one via general journal) that creates the mutations on the designated ledger account for the payment provider are crossed off (sum of the two journalentrylines must be 0,-) using a suspense account control line.
