Rounding issues with sales taxes

[Jacopo Cappellato]

Unfortunately, there are still some rounding issues when sales taxes are
involved.
After some research, I think I have found where the problem is:
OrderPaymentPreference.maxAmount (the field where the auth/captured
amount is stored) has 2 decimal digits; the field OrderAdjustment.amount
has 3 decimal digits; the number of digits (set in the arithmetic
properties file) for sales taxes is 3.
My guess is that somewhere we move a 3 decimal number (without proerly
rounding it as we do with the order grand total) to a 2 decimal field
(maxAmount) and so the db approximate the number for us (in a different
way from the grand total).
The end result is this:

https://demo.hotwaxmedia.com/ordermgr/control/orderview?orderId=WSCO10001

The order total is $18.83 (rounded from 18.825)
The amount auth/captured/invoiced is $18.82

Any hints on how we can fix this?

Jacopo

[Tim Ruppert]

Yes - we are seeing this as well.  This causes an additional attempted auth after a capture - which is way confusing for people using the OrderMgr :)

Cheers,

Tim

--

Tim Ruppert

HotWax Media

http://www.hotwaxmedia.com

o:801.649.6594

f:801.649.6595

On Jun 22, 2007, at 9:26 AM, Jacopo Cappellato wrote:

Unfortunately, there are still some rounding issues when sales taxes are involved.

After some research, I think I have found where the problem is:

OrderPaymentPreference.maxAmount (the field where the auth/captured amount is stored) has 2 decimal digits; the field OrderAdjustment.amount has 3 decimal digits; the number of digits (set in the arithmetic properties file) for sales taxes is 3.

My guess is that somewhere we move a 3 decimal number (without proerly rounding it as we do with the order grand total) to a 2 decimal field (maxAmount) and so the db approximate the number for us (in a different way from the grand total).

The end result is this:

https://demo.hotwaxmedia.com/ordermgr/control/orderview?orderId=WSCO10001

The order total is $18.83 (rounded from 18.825)

The amount auth/captured/invoiced is $18.82

Any hints on how we can fix this?

Jacopo

[Jacopo Cappellato]

Well,

in the order in the demo server the amount invoiced is equal to the
order grand total: $18.83

So, the rounding issues in the payment is leaving the invoice open for
one cent

Jacopo

Jacopo Cappellato wrote:

> Unfortunately, there are still some rounding issues when sales taxes are
> involved.
> After some research, I think I have found where the problem is:
> OrderPaymentPreference.maxAmount (the field where the auth/captured
> amount is stored) has 2 decimal digits; the field OrderAdjustment.amount
> has 3 decimal digits; the number of digits (set in the arithmetic
> properties file) for sales taxes is 3.
> My guess is that somewhere we move a 3 decimal number (without proerly
> rounding it as we do with the order grand total) to a 2 decimal field
> (maxAmount) and so the db approximate the number for us (in a different
> way from the grand total).
> The end result is this:
>
> https://demo.hotwaxmedia.com/ordermgr/control/orderview?orderId=WSCO10001
>
> The order total is $18.83 (rounded from 18.825)
> The amount auth/captured/invoiced is $18.82
>
> Any hints on how we can fix this?
>
> Jacopo

[jonwimp]

Jacopo,

I posted something on this before. As with all rounding, there will be losses (or pluses). It
doesn't matter how many decimal places you do your rounding with. The eventual invoices going out
to the customers is the weak link! Those invoices have 2 decimal places, which means a rounding
action is already done there.

One idea is to keep the total in view, and then compensate for rounding discrepancies in the last
invoice.

Say you have 3 invoices. For the first 2, you send those invoices out with rounding as usual. The
last invoice will take the correct total and deduct the amounts from past invoices. Whatever is
"extra/less" in the final invoice, label it as "rounding adjustments". That way, no matter what
the rounding discrepancies, the final invoice will always bring the total invoiced amount to the
correct figure.

Jonathon

Jacopo Cappellato wrote:

> Unfortunately, there are still some rounding issues when sales taxes are
> involved.
> After some research, I think I have found where the problem is:
> OrderPaymentPreference.maxAmount (the field where the auth/captured
> amount is stored) has 2 decimal digits; the field OrderAdjustment.amount
> has 3 decimal digits; the number of digits (set in the arithmetic
> properties file) for sales taxes is 3.
> My guess is that somewhere we move a 3 decimal number (without proerly
> rounding it as we do with the order grand total) to a 2 decimal field
> (maxAmount) and so the db approximate the number for us (in a different
> way from the grand total).
> The end result is this:
>
> https://demo.hotwaxmedia.com/ordermgr/control/orderview?orderId=WSCO10001
>
> The order total is $18.83 (rounded from 18.825)
> The amount auth/captured/invoiced is $18.82
>
> Any hints on how we can fix this?
>
> Jacopo
>
>

[Scott Gray]

Hi Jacopo

If you can give me a test case I'm happy to take a look at it.

Regards
Scott

On 23/06/07, Jacopo Cappellato <[hidden email]> wrote:

>
> Well,
>
> in the order in the demo server the amount invoiced is equal to the
> order grand total: $18.83
>
> So, the rounding issues in the payment is leaving the invoice open for
> one cent
>
> Jacopo
>
> Jacopo Cappellato wrote:
> > Unfortunately, there are still some rounding issues when sales taxes are
> > involved.
> > After some research, I think I have found where the problem is:
> > OrderPaymentPreference.maxAmount (the field where the auth/captured
> > amount is stored) has 2 decimal digits; the field OrderAdjustment.amount
> > has 3 decimal digits; the number of digits (set in the arithmetic
> > properties file) for sales taxes is 3.
> > My guess is that somewhere we move a 3 decimal number (without proerly
> > rounding it as we do with the order grand total) to a 2 decimal field
> > (maxAmount) and so the db approximate the number for us (in a different
> > way from the grand total).
> > The end result is this:
> >
> >
> https://demo.hotwaxmedia.com/ordermgr/control/orderview?orderId=WSCO10001
> >
> > The order total is $18.83 (rounded from 18.825)
> > The amount auth/captured/invoiced is $18.82
> >
> > Any hints on how we can fix this?
> >
> > Jacopo
>
>

[Jacopo Cappellato]

Scott,

that is great, thanks!

For the test case:

https://demo.hotwaxmedia.com/ordermgr/control/orderview?orderId=WSCO10001

I got this order in the following way:

1) removed all the promotions from the store
2) changed the tax percentage for the UT state to 8.63; you can do this
in the following screen:
https://demo.hotwaxmedia.com/accounting/control/EditTaxAuthorityRateProducts?taxAuthPartyId=UT_TAXMAN&taxAuthGeoId=UT
3) in the order manager, start an order for DemoCustomer
4) put one product in the cart and change its price to 11.99
5) select the "UPS Air  -  $5.80" as shipment method
6) checkout and quickship

Please let me know if you need further information!

Jacopo


Scott Gray wrote:

> Hi Jacopo
>
> If you can give me a test case I'm happy to take a look at it.
>
> Regards
> Scott
>
> On 23/06/07, Jacopo Cappellato <[hidden email]> wrote:
>>
>> Well,
>>
>> in the order in the demo server the amount invoiced is equal to the
>> order grand total: $18.83
>>
>> So, the rounding issues in the payment is leaving the invoice open for
>> one cent
>>
>> Jacopo
>>
>> Jacopo Cappellato wrote:
>> > Unfortunately, there are still some rounding issues when sales taxes
>> are
>> > involved.
>> > After some research, I think I have found where the problem is:
>> > OrderPaymentPreference.maxAmount (the field where the auth/captured
>> > amount is stored) has 2 decimal digits; the field
>> OrderAdjustment.amount
>> > has 3 decimal digits; the number of digits (set in the arithmetic
>> > properties file) for sales taxes is 3.
>> > My guess is that somewhere we move a 3 decimal number (without proerly
>> > rounding it as we do with the order grand total) to a 2 decimal field
>> > (maxAmount) and so the db approximate the number for us (in a different
>> > way from the grand total).
>> > The end result is this:
>> >
>> >
>> https://demo.hotwaxmedia.com/ordermgr/control/orderview?orderId=WSCO10001
>> >
>> > The order total is $18.83 (rounded from 18.825)
>> > The amount auth/captured/invoiced is $18.82
>> >
>> > Any hints on how we can fix this?
>> >
>> > Jacopo
>>
>>
>

[Scott Gray]

Still digging but here's the problem:
remainingTotal = 18.824999999999999289457264239899814128875732421875
remainingAmount = remainingAmount.setScale(2, BigDecimal.ROUND_HALF_UP);
result = 18.82

Scott

On 23/06/07, Jacopo Cappellato <[hidden email]> wrote:

>
> Scott,
>
> that is great, thanks!
>
> For the test case:
>
> https://demo.hotwaxmedia.com/ordermgr/control/orderview?orderId=WSCO10001
>
> I got this order in the following way:
>
> 1) removed all the promotions from the store
> 2) changed the tax percentage for the UT state to 8.63; you can do this
> in the following screen:
>
> https://demo.hotwaxmedia.com/accounting/control/EditTaxAuthorityRateProducts?taxAuthPartyId=UT_TAXMAN&taxAuthGeoId=UT
> 3) in the order manager, start an order for DemoCustomer
> 4) put one product in the cart and change its price to 11.99
> 5) select the "UPS Air  -  $5.80" as shipment method
> 6) checkout and quickship
>
> Please let me know if you need further information!
>
> Jacopo
>
>
> Scott Gray wrote:
> > Hi Jacopo
> >
> > If you can give me a test case I'm happy to take a look at it.
> >
> > Regards
> > Scott
> >
> > On 23/06/07, Jacopo Cappellato <[hidden email]> wrote:
> >>
> >> Well,
> >>
> >> in the order in the demo server the amount invoiced is equal to the
> >> order grand total: $18.83
> >>
> >> So, the rounding issues in the payment is leaving the invoice open for
> >> one cent
> >>
> >> Jacopo
> >>
> >> Jacopo Cappellato wrote:
> >> > Unfortunately, there are still some rounding issues when sales taxes
> >> are
> >> > involved.
> >> > After some research, I think I have found where the problem is:
> >> > OrderPaymentPreference.maxAmount (the field where the auth/captured
> >> > amount is stored) has 2 decimal digits; the field
> >> OrderAdjustment.amount
> >> > has 3 decimal digits; the number of digits (set in the arithmetic
> >> > properties file) for sales taxes is 3.
> >> > My guess is that somewhere we move a 3 decimal number (without
> proerly
> >> > rounding it as we do with the order grand total) to a 2 decimal field
> >> > (maxAmount) and so the db approximate the number for us (in a
> different
> >> > way from the grand total).
> >> > The end result is this:
> >> >
> >> >
> >>
> https://demo.hotwaxmedia.com/ordermgr/control/orderview?orderId=WSCO10001
> >> >
> >> > The order total is $18.83 (rounded from 18.825)
> >> > The amount auth/captured/invoiced is $18.82
> >> >
> >> > Any hints on how we can fix this?
> >> >
> >> > Jacopo
> >>
> >>
> >
>
>

[Scott Gray]

The problem was ShoppingCart.getTotalSalesTax not rounding down to
taxFinalScale, should be fixed now in rev. 550044.

Regards
Scott

On 23/06/07, Scott Gray <[hidden email]> wrote:

>
> Still digging but here's the problem:
> remainingTotal = 18.824999999999999289457264239899814128875732421875
> remainingAmount = remainingAmount.setScale(2, BigDecimal.ROUND_HALF_UP);
> result = 18.82
>
> Scott
>
> On 23/06/07, Jacopo Cappellato <[hidden email]> wrote:
> >
> > Scott,
> >
> > that is great, thanks!
> >
> > For the test case:
> >
> > https://demo.hotwaxmedia.com/ordermgr/control/orderview?orderId=WSCO10001
> >
> >
> > I got this order in the following way:
> >
> > 1) removed all the promotions from the store
> > 2) changed the tax percentage for the UT state to 8.63; you can do this
> > in the following screen:
> >
> > https://demo.hotwaxmedia.com/accounting/control/EditTaxAuthorityRateProducts?taxAuthPartyId=UT_TAXMAN&taxAuthGeoId=UT
> > 3) in the order manager, start an order for DemoCustomer
> > 4) put one product in the cart and change its price to 11.99
> > 5) select the "UPS Air  -  $5.80" as shipment method
> > 6) checkout and quickship
> >
> > Please let me know if you need further information!
> >
> > Jacopo
> >
> >
> > Scott Gray wrote:
> > > Hi Jacopo
> > >
> > > If you can give me a test case I'm happy to take a look at it.
> > >
> > > Regards
> > > Scott
> > >
> > > On 23/06/07, Jacopo Cappellato <[hidden email]> wrote:
> > >>
> > >> Well,
> > >>
> > >> in the order in the demo server the amount invoiced is equal to the
> > >> order grand total: $18.83
> > >>
> > >> So, the rounding issues in the payment is leaving the invoice open
> > for
> > >> one cent
> > >>
> > >> Jacopo
> > >>
> > >> Jacopo Cappellato wrote:
> > >> > Unfortunately, there are still some rounding issues when sales
> > taxes
> > >> are
> > >> > involved.
> > >> > After some research, I think I have found where the problem is:
> > >> > OrderPaymentPreference.maxAmount (the field where the auth/captured
> > >> > amount is stored) has 2 decimal digits; the field
> > >> OrderAdjustment.amount
> > >> > has 3 decimal digits; the number of digits (set in the arithmetic
> > >> > properties file) for sales taxes is 3.
> > >> > My guess is that somewhere we move a 3 decimal number (without
> > proerly
> > >> > rounding it as we do with the order grand total) to a 2 decimal
> > field
> > >> > (maxAmount) and so the db approximate the number for us (in a
> > different
> > >> > way from the grand total).
> > >> > The end result is this:
> > >> >
> > >> >
> > >> https://demo.hotwaxmedia.com/ordermgr/control/orderview?orderId=WSCO10001
> >
> > >> >
> > >> > The order total is $18.83 (rounded from 18.825)
> > >> > The amount auth/captured/invoiced is $18.82
> > >> >
> > >> > Any hints on how we can fix this?
> > >> >
> > >> > Jacopo
> > >>
> > >>
> > >
> >
> >
>

[Tim Ruppert]

That's great scott - thanks for swinging in on that.

Cheers,

Tim

--

Tim Ruppert

HotWax Media

http://www.hotwaxmedia.com

o:801.649.6594

f:801.649.6595

On Jun 23, 2007, at 8:07 AM, Scott Gray wrote:

The problem was ShoppingCart.getTotalSalesTax not rounding down to

taxFinalScale, should be fixed now in rev. 550044.

Regards

Scott

On 23/06/07, Scott Gray <[hidden email]> wrote:

Still digging but here's the problem:

remainingTotal = 18.824999999999999289457264239899814128875732421875

remainingAmount = remainingAmount.setScale(2, BigDecimal.ROUND_HALF_UP);

result = 18.82

Scott

On 23/06/07, Jacopo Cappellato <[hidden email]> wrote:

>

> Scott,

>

> that is great, thanks!

>

> For the test case:

>

> https://demo.hotwaxmedia.com/ordermgr/control/orderview?orderId=WSCO10001

>

>

> I got this order in the following way:

>

> 1) removed all the promotions from the store

> 2) changed the tax percentage for the UT state to 8.63; you can do this

> in the following screen:

>

> https://demo.hotwaxmedia.com/accounting/control/EditTaxAuthorityRateProducts?taxAuthPartyId=UT_TAXMAN&taxAuthGeoId=UT

> 3) in the order manager, start an order for DemoCustomer

> 4) put one product in the cart and change its price to 11.99

> 5) select the "UPS Air  -  $5.80" as shipment method

> 6) checkout and quickship

>

> Please let me know if you need further information!

>

> Jacopo

>

>

> Scott Gray wrote:

> > Hi Jacopo

> >

> > If you can give me a test case I'm happy to take a look at it.

> >

> > Regards

> > Scott

> >

> > On 23/06/07, Jacopo Cappellato <[hidden email]> wrote:

> >>

> >> Well,

> >>

> >> in the order in the demo server the amount invoiced is equal to the

> >> order grand total: $18.83

> >>

> >> So, the rounding issues in the payment is leaving the invoice open

> for

> >> one cent

> >>

> >> Jacopo

> >>

> >> Jacopo Cappellato wrote:

> >> > Unfortunately, there are still some rounding issues when sales

> taxes

> >> are

> >> > involved.

> >> > After some research, I think I have found where the problem is:

> >> > OrderPaymentPreference.maxAmount (the field where the auth/captured

> >> > amount is stored) has 2 decimal digits; the field

> >> OrderAdjustment.amount

> >> > has 3 decimal digits; the number of digits (set in the arithmetic

> >> > properties file) for sales taxes is 3.

> >> > My guess is that somewhere we move a 3 decimal number (without

> proerly

> >> > rounding it as we do with the order grand total) to a 2 decimal

> field

> >> > (maxAmount) and so the db approximate the number for us (in a

> different

> >> > way from the grand total).

> >> > The end result is this:

> >> >

> >> >

> >> https://demo.hotwaxmedia.com/ordermgr/control/orderview?orderId=WSCO10001

>

> >> >

> >> > The order total is $18.83 (rounded from 18.825)

> >> > The amount auth/captured/invoiced is $18.82

> >> >

> >> > Any hints on how we can fix this?

> >> >

> >> > Jacopo

> >>

> >>

> >

>

>