Draft
This document is still a draft and might change
Handling rounding and VAT in order integration
One potential challenge in order integration relates to the disparate methods employed by various systems and businesses for managing VAT and rounding of prices on sales orders. If an ERP or OMS that received the order from Norce expects the calculation differently, errors might occur that need to be handled by the integration and sometimes by manual input.
VAT and rounding in Norce
First, lets look at how Norce calculates the amounts on the Order.
- A unit price is saved on the order row (best price from the product)
- A VAT is added, based on the product's VAT rate (rounded according to client settings)
- The row is multiplied with quantity to give the row amount
- All row amounts (from 3) adds up to the order total amount
- All VAT's (from 2) multiplied by quantity and aggregated from all rows is the total VAT
This method works with most PSP's currently used with Norce, which is important since they will not be able to capture the correct amounts otherwise later on.
However, some ERP systems work differently. For example, some want to calculate the VAT after they multiply the quantity with (i.e. switch 2 and 3 in the list above). And if the quantity is high and the ERP also makes nicely rounded subtotals, the risk of discrepancy between how Norce and the ERP gets quite high.
On certain occasions, especially when dealing with orders containing large quantities, discrepancies in the two calculation approaches can cause significant variations, potentially triggering error messages from the ERP system during order submission.
Solving the discrepancy problem
Sadly, there is no easy way to fix this problem.
Norce have chosen our way to calculate this, based on how most PSP's and eCommerce solutions does it and we must adherre to their way, so not to have problems later, when payment captures, credits or cancels are done.
A common way we have seen solutions handle this is to flag orders for manual adjustments when passed into the ERP system. Using a tolerance threshold and a "manual check" flag that is passed in by the integration on the order.
Tolerance
Set up the integration so that some tolerance is allowed. Let some small discrepancies through and, if the amount that differ is too big, add a manual check where the order is adjusted. This is preferably done by passing in a "manual check" flag on the order to the ERP system.
Manual checks
For an order already reserved by a PSP, the VAT is already a fact and cannot be changed, and the same for the total amount. It is the other parts that needs to be adjusted.
- If you use partial capture each row must add up (or a bit lower) to the ecommerce order line amount, compensate for this in the ERP.
- Calculate backwards and use centrounding rows for compensating the last diff in needed.
Payment method
Not all orders might need this treatment.
An order that is marked to be invoiced from the ERP doesnt have to follow the VAT Norce has calculated and can just recalculate the VAT in the integration.
Amount focused payment methods, like Credit card or bank transfers are only concerned with the total amount and doesnt care about VAT. In this case adjustments of the line amounts are easier to do as well.
Example 1 on rows
Three examples on how the row amount might differ between Norce and the ERP
First how Norce will calculate it:
Row | Unit price | VAT Rate | Quantity | Unit price inc VAT | Line amount inc VAT | VAT amount |
---|---|---|---|---|---|---|
1 | 12,23 | 25% | 1 | 15,29 | 15,29 | 3,06 |
2 | 12,23 | 25% | 100 | 15,29 | 1529 | 306 |
3 | 12,23 | 25% | 1000 | 15,29 | 15290 | 3060 |
Lets look at how the ERP would calculate the same lines, and see the amount that differs.
Row | Unit price | VAT Rate | Quantity | Line amount ex VAT | Line amount inc VAT | VAT amount | Diff | Tolerance (<1,00) |
---|---|---|---|---|---|---|---|---|
1 | 12,23 | 25% | 1 | 12,23 | 15,29 | 3,06 | 0 | OK |
2 | 12,23 | 25% | 100 | 1223 | 1528,75 | 305,75 | 0,25 | OK |
3 | 12,23 | 25% | 1000 | 12230 | 15287,50 | 3057,50 | 2,5 | Check |
If we have a tolerance check for everything over 1.00#, the third line would be marked for manual check.
In the manual check, the handler can adjust the ERP order, by changing the unit price so that the VAT amount is correct.
Row | Unit price | VAT Rate | Quantity | Line amount ex VAT | Line amount inc VAT | VAT amount |
---|---|---|---|---|---|---|
3 | 12,24 | 25% | 1000 | 12240 | 15300 | 3060 |
CR | -10,00 | 0% | 1000 | -10,00 | -10,00 | 0 |
Where row CR is a centrounting line, to make the total add up.
In this case the unit price was adjusted with an additional centrounding row to set the correct total amount.
Calculate an adjusted order
In this example we try adjusting the unit price, so that we would get the same VAT amount. 12,24 hit it right on. But the total amount was instead to high, with exactly 10.00. We added -10 as a centrounding line and the total amount and VAT was adjusted.
Alternatively, when the payment methods is amount focused (credit card) the adjustment could be simpler
Row | Unit price | VAT Rate | Quantity | Line amount ex VAT | Line amount inc VAT | VAT amount |
---|---|---|---|---|---|---|
3 | 12,23 | 25% | 1000 | 12230 | 15287,50 | 3057,50 |
CR | 0,5 | 0% | 1 | 0,50 | 0,50 | 0 |
Here the original unit price works fine, because the total amount is lower than the reserved amount by the PSP (15290). The centrounding is optional, if one prefers a nicer total of 15288.00
Example 2 - whole order
Lets look at one whole order with to rows.
Norce order:
Row | Unit price | VAT Rate | Quantity | Unit price inc VAT | Line amount inc VAT | VAT amount |
---|---|---|---|---|---|---|
1 | 12,23 | 25% | 1000 | 15,29 | 15290 | 3060 |
2 | 11,89 | 25% | 1212 | 14,86 | 18010,32 | 3599,64 |
Tot. | 33 300,32 | 6659,64 |
ERP Order:
Row | Unit price | VAT Rate | Quantity | Line amount ex VAT | Line amount inc VAT | VAT amount | Diff | Tolerance (<1,00) |
---|---|---|---|---|---|---|---|---|
1 | 12,23 | 25% | 1000 | 12230 | 15287,50 | 3057,50 | 2,5 | Check |
2 | 11,89 | 25% | 1212 | 14410,68 | 18013,35 | 3602,67 | -3,03 | Check |
Tot. | 33 300,85 | 6660,17 | -0,53 () |
Both rows requires manual check based on the tolerance. In the manual check, the handler can adjust the ERP order as a whole, by changing the unit prices so that the VAT amounts are correct.
Row | Unit price | VAT Rate | Quantity | Line amount ex VAT | Line amount inc VAT | VAT amount | Not. |
---|---|---|---|---|---|---|---|
1 | 12,24 | 25% | 1000 | 12240 | 15300 | 3060 | Same as ex1 above. The total amount differs with -10.00 |
2 | 11,88 | 25% | 1212 | 14398,56 | 17998,2 | 3602,67 | This one differs with +12,12 instead. |
CR | 2,12 | 0% | 1 | 2,12 | 2,12 | 0 | Two cent roudings clears almost out the whole diff. |
Note
Again, the adjustment of the unit price hits right on the VAT amount (which is due to the nice VAT rate used, less likely for 19% or 24%). But the total amount was too low instead. Centrounding evens out the diff.
Alternatively, for credit card paid orders, and only caring about the total amount the adjustment is the same in this case
Row | Unit price | VAT Rate | Quantity | Line amount ex VAT | Line amount inc VAT | VAT amount | Not. |
---|---|---|---|---|---|---|---|
1 | 12,23 | 25% | 1000 | 12230 | 15287,50 | 3057,50 | No change |
CR | 0,5 | 0% | 1 | 0,50 | 0,50 | 0 | |
2 | 11,89 | 25% | 1212 | 14410,68 | 18013,35 | 3602,67 | No change |
CR | 12,12 | 0% | 1 | 12,12 | 12,12 | 0 |
Note
Row 1 total amount differs with +0,50 and row 2 differs with +3,03. Change either the unit price on row 2 to 11,88 or use a centrounding row to push the order total down to the reserved amount. Here we use the latter.