Need help to figure out an issue with sales tax decimals in InvoiceItem

[Jacopo Cappellato <ti...@sastau.it>]

While testing the GL accounting transactions I've found something that 
could be an issue in the procedure that computes the sales tax 
adjustment for the invoice.
I've noticed that the InvoiceItem.amount for sales tax contains 
sometimes a number with 3 decimals, even if the arithmetic.properties 
file we have:

salestax.calc.decimals = 3
salestax.final.decimals = 2
salestax.rounding = ROUND_HALF_UP

You can recreate this by creating and invoicing a sales order for 3 
units of GZ-1000.

For example, look at this invoice:

https://demo.hotwaxmedia.com/accounting/control/invoiceOverview?invoiceId=CI1

Having 3 decimals is an issue for the gl auto posting service for sales 
invoices because the sales tax item generates an AcctgTransEntry, but 
the AcctgTransEntry.amount field can only store 2 decimals.

I'd appreciate suggestions/hints.

Cheers,

Jacopo

Re: Need help to figure out an issue with sales tax decimals in InvoiceItem

[BJ Freeman <bj...@free-man.net>]

I believe the best solution is to step back and make some high level
design decisions having to do with math.

I believe a complete path map of processes first needs to be generated
to see when the "final" figure is done. for instance when an invoice is
sent.

It may be time to refactor where the calculations are done to get a
invoice "final" figure so the Accounting will flow properly.

Testing should be run to make sure the "Final" figure, matches when two
different processes do the calculation.

Jacopo Cappellato sent the following on 1/7/2008 6:14 AM:
> While testing the GL accounting transactions I've found something that
> could be an issue in the procedure that computes the sales tax
> adjustment for the invoice.
> I've noticed that the InvoiceItem.amount for sales tax contains
> sometimes a number with 3 decimals, even if the arithmetic.properties
> file we have:
> 
> salestax.calc.decimals = 3
> salestax.final.decimals = 2
> salestax.rounding = ROUND_HALF_UP
> 
> You can recreate this by creating and invoicing a sales order for 3
> units of GZ-1000.
> 
> For example, look at this invoice:
> 
> https://demo.hotwaxmedia.com/accounting/control/invoiceOverview?invoiceId=CI1
> 
> 
> Having 3 decimals is an issue for the gl auto posting service for sales
> invoices because the sales tax item generates an AcctgTransEntry, but
> the AcctgTransEntry.amount field can only store 2 decimals.
> 
> I'd appreciate suggestions/hints.
> 
> Cheers,
> 
> Jacopo
> 
> 
> 

Re: Need help to figure out an issue with sales tax decimals in InvoiceItem

[David E Jones <jo...@hotwaxmedia.com>]

Yes, okay, that actually makes good sense. It would basically be the  
same process of sum and round used for getting tax totals, but for  
accounting purposes justing summing per tax authority.

I'd still like to hear from someone who knows about this from a  
regulatory perspective in at least a couple parts of the world so we  
can parameterize as necessary (or at least have a start in the right  
direction).

In any case, this does sound like a good place to start.

-David


On Jan 7, 2008, at 10:17 PM, Scott Gray wrote:

> Perhaps we need to do 2 things:
> 1. Summarize AcctgTransEntries so that you have one entry per  
> TaxAuthority
> rather than for each tax adjustment
> 2. During order/invoice processing, calc and final should be applied  
> on a
> per TaxAuthority basis, so that we are only ever collecting final  
> rounded
> amounts for each tax authority.
>
> So for example if we have invoice with the following adjustments:
> (I just made these numbers up, they don't relate to any percentages)
> UT_TAXMAN - $4.311
> UT_TAXMAN - $7.397
> UT_UTAH_TAXMAN - $5.643
> UT_UTAH_TAXMAN - $16.828
>
> Tax final would be calculated like this:
> UT_TAXMAN - $4.311 + $7.399 = $11.71 (2dp)
> UT_UTAH_TAXMAN - $5.643 + $16.828 = $22.47 (2dp)
> Tax Total = $11.71 + $22.47 = $34.18
>
> Then the AcctgTransEntries would be:
> UT_TAXMAN = $11.71
> UT_UTAH_TAXMAN = $22.47
>
> Regards
> Scott
>
>
> On 08/01/2008, David E Jones <jo...@hotwaxmedia.com> wrote:
>>
>>
>> On Jan 7, 2008, at 11:04 AM, Scott Gray wrote:
>>
>>> Hi Jacopo
>>>
>>> My understanding of calc and final:
>>> calc - adjustment level rounding
>>> final - the sum of all tax adjustments (tax total) is rounded to  
>>> this
>>> precision
>>>
>>> Perhaps AcctgTransEntry.amount needs to store to a higher precision
>>> as well?
>>
>> What Scott says above is correct as I understand it, but I'm not sure
>> this last part is a good idea.
>>
>> Accounting/GL transactions are meant to be final and to avoid  
>> problems
>> they are structured in a way where reporting just involves adding
>> things up and using straight totals with no rounding, etc to avoid  
>> any
>> biasing (with the exception of certain averages and such, but that is
>> different as precision on those is used in a different way).
>>
>> It seems to me that posting anything to an accounting with more  
>> than 2
>> decimals of precision seems like a bad idea to me, except perhaps the
>> infamous "rounding remainder" accounts. We should probably consult
>> with an accounting before doing much of that sort of thing, but of
>> course if we do change the AcctgTransEntry.amount to be higher
>> precision we can always configure/code around it.
>>
>> -David
>>
>>
>>> On 08/01/2008, Jacopo Cappellato <ti...@sastau.it> wrote:
>>>>
>>>> While testing the GL accounting transactions I've found something
>>>> that
>>>> could be an issue in the procedure that computes the sales tax
>>>> adjustment for the invoice.
>>>> I've noticed that the InvoiceItem.amount for sales tax contains
>>>> sometimes a number with 3 decimals, even if the  
>>>> arithmetic.properties
>>>> file we have:
>>>>
>>>> salestax.calc.decimals = 3
>>>> salestax.final.decimals = 2
>>>> salestax.rounding = ROUND_HALF_UP
>>>>
>>>> You can recreate this by creating and invoicing a sales order for 3
>>>> units of GZ-1000.
>>>>
>>>> For example, look at this invoice:
>>>>
>>>>
>>>>
>> https://demo.hotwaxmedia.com/accounting/control/invoiceOverview?invoiceId=CI1
>>>>
>>>> Having 3 decimals is an issue for the gl auto posting service for
>>>> sales
>>>> invoices because the sales tax item generates an AcctgTransEntry,  
>>>> but
>>>> the AcctgTransEntry.amount field can only store 2 decimals.
>>>>
>>>> I'd appreciate suggestions/hints.
>>>>
>>>> Cheers,
>>>>
>>>> Jacopo
>>>>
>>
>>
>>

Re: Need help to figure out an issue with sales tax decimals in InvoiceItem

[Scott Gray <le...@gmail.com>]

Perhaps we need to do 2 things:
1. Summarize AcctgTransEntries so that you have one entry per TaxAuthority
rather than for each tax adjustment
2. During order/invoice processing, calc and final should be applied on a
per TaxAuthority basis, so that we are only ever collecting final rounded
amounts for each tax authority.

So for example if we have invoice with the following adjustments:
(I just made these numbers up, they don't relate to any percentages)
UT_TAXMAN - $4.311
UT_TAXMAN - $7.397
UT_UTAH_TAXMAN - $5.643
UT_UTAH_TAXMAN - $16.828

Tax final would be calculated like this:
UT_TAXMAN - $4.311 + $7.399 = $11.71 (2dp)
UT_UTAH_TAXMAN - $5.643 + $16.828 = $22.47 (2dp)
Tax Total = $11.71 + $22.47 = $34.18

Then the AcctgTransEntries would be:
UT_TAXMAN = $11.71
UT_UTAH_TAXMAN = $22.47

Regards
Scott


On 08/01/2008, David E Jones <jo...@hotwaxmedia.com> wrote:
>
>
> On Jan 7, 2008, at 11:04 AM, Scott Gray wrote:
>
> > Hi Jacopo
> >
> > My understanding of calc and final:
> > calc - adjustment level rounding
> > final - the sum of all tax adjustments (tax total) is rounded to this
> > precision
> >
> > Perhaps AcctgTransEntry.amount needs to store to a higher precision
> > as well?
>
> What Scott says above is correct as I understand it, but I'm not sure
> this last part is a good idea.
>
> Accounting/GL transactions are meant to be final and to avoid problems
> they are structured in a way where reporting just involves adding
> things up and using straight totals with no rounding, etc to avoid any
> biasing (with the exception of certain averages and such, but that is
> different as precision on those is used in a different way).
>
> It seems to me that posting anything to an accounting with more than 2
> decimals of precision seems like a bad idea to me, except perhaps the
> infamous "rounding remainder" accounts. We should probably consult
> with an accounting before doing much of that sort of thing, but of
> course if we do change the AcctgTransEntry.amount to be higher
> precision we can always configure/code around it.
>
> -David
>
>
> > On 08/01/2008, Jacopo Cappellato <ti...@sastau.it> wrote:
> >>
> >> While testing the GL accounting transactions I've found something
> >> that
> >> could be an issue in the procedure that computes the sales tax
> >> adjustment for the invoice.
> >> I've noticed that the InvoiceItem.amount for sales tax contains
> >> sometimes a number with 3 decimals, even if the arithmetic.properties
> >> file we have:
> >>
> >> salestax.calc.decimals = 3
> >> salestax.final.decimals = 2
> >> salestax.rounding = ROUND_HALF_UP
> >>
> >> You can recreate this by creating and invoicing a sales order for 3
> >> units of GZ-1000.
> >>
> >> For example, look at this invoice:
> >>
> >>
> >>
> https://demo.hotwaxmedia.com/accounting/control/invoiceOverview?invoiceId=CI1
> >>
> >> Having 3 decimals is an issue for the gl auto posting service for
> >> sales
> >> invoices because the sales tax item generates an AcctgTransEntry, but
> >> the AcctgTransEntry.amount field can only store 2 decimals.
> >>
> >> I'd appreciate suggestions/hints.
> >>
> >> Cheers,
> >>
> >> Jacopo
> >>
>
>
>

Re: Need help to figure out an issue with sales tax decimals in InvoiceItem

[David E Jones <jo...@hotwaxmedia.com>]

On Jan 7, 2008, at 11:04 AM, Scott Gray wrote:

> Hi Jacopo
>
> My understanding of calc and final:
> calc - adjustment level rounding
> final - the sum of all tax adjustments (tax total) is rounded to this
> precision
>
> Perhaps AcctgTransEntry.amount needs to store to a higher precision  
> as well?

What Scott says above is correct as I understand it, but I'm not sure  
this last part is a good idea.

Accounting/GL transactions are meant to be final and to avoid problems  
they are structured in a way where reporting just involves adding  
things up and using straight totals with no rounding, etc to avoid any  
biasing (with the exception of certain averages and such, but that is  
different as precision on those is used in a different way).

It seems to me that posting anything to an accounting with more than 2  
decimals of precision seems like a bad idea to me, except perhaps the  
infamous "rounding remainder" accounts. We should probably consult  
with an accounting before doing much of that sort of thing, but of  
course if we do change the AcctgTransEntry.amount to be higher  
precision we can always configure/code around it.

-David


> On 08/01/2008, Jacopo Cappellato <ti...@sastau.it> wrote:
>>
>> While testing the GL accounting transactions I've found something  
>> that
>> could be an issue in the procedure that computes the sales tax
>> adjustment for the invoice.
>> I've noticed that the InvoiceItem.amount for sales tax contains
>> sometimes a number with 3 decimals, even if the arithmetic.properties
>> file we have:
>>
>> salestax.calc.decimals = 3
>> salestax.final.decimals = 2
>> salestax.rounding = ROUND_HALF_UP
>>
>> You can recreate this by creating and invoicing a sales order for 3
>> units of GZ-1000.
>>
>> For example, look at this invoice:
>>
>>
>> https://demo.hotwaxmedia.com/accounting/control/invoiceOverview?invoiceId=CI1
>>
>> Having 3 decimals is an issue for the gl auto posting service for  
>> sales
>> invoices because the sales tax item generates an AcctgTransEntry, but
>> the AcctgTransEntry.amount field can only store 2 decimals.
>>
>> I'd appreciate suggestions/hints.
>>
>> Cheers,
>>
>> Jacopo
>>

Re: Need help to figure out an issue with sales tax decimals in InvoiceItem

[Tim Ruppert <ti...@hotwaxmedia.com>]

+1

On Jan 7, 2008, at 6:21 PM, Jacques Le Roux wrote:

> From: "Scott Gray" <le...@gmail.com>
>> Hi Jacopo
>> My understanding of calc and final:
>> calc - adjustment level rounding
>> final - the sum of all tax adjustments (tax total) is rounded to this
>> precision
>> Perhaps AcctgTransEntry.amount needs to store to a higher precision  
>> as well?
>
> Yes, I agree with Scott's suggestion
>
> Jacques
>> Regards
>> Scott
>> On 08/01/2008, Jacopo Cappellato <ti...@sastau.it> wrote:
>>>
>>> While testing the GL accounting transactions I've found something  
>>> that
>>> could be an issue in the procedure that computes the sales tax
>>> adjustment for the invoice.
>>> I've noticed that the InvoiceItem.amount for sales tax contains
>>> sometimes a number with 3 decimals, even if the  
>>> arithmetic.properties
>>> file we have:
>>>
>>> salestax.calc.decimals = 3
>>> salestax.final.decimals = 2
>>> salestax.rounding = ROUND_HALF_UP
>>>
>>> You can recreate this by creating and invoicing a sales order for 3
>>> units of GZ-1000.
>>>
>>> For example, look at this invoice:
>>>
>>>
>>> https://demo.hotwaxmedia.com/accounting/control/invoiceOverview?invoiceId=CI1
>>>
>>> Having 3 decimals is an issue for the gl auto posting service for  
>>> sales
>>> invoices because the sales tax item generates an AcctgTransEntry,  
>>> but
>>> the AcctgTransEntry.amount field can only store 2 decimals.
>>>
>>> I'd appreciate suggestions/hints.
>>>
>>> Cheers,
>>>
>>> Jacopo
>>>
>>

Re: Need help to figure out an issue with sales tax decimals in InvoiceItem

[Jacques Le Roux <ja...@les7arts.com>]

From: "Scott Gray" <le...@gmail.com>
> Hi Jacopo
> 
> My understanding of calc and final:
> calc - adjustment level rounding
> final - the sum of all tax adjustments (tax total) is rounded to this
> precision
> 
> Perhaps AcctgTransEntry.amount needs to store to a higher precision as well?

Yes, I agree with Scott's suggestion

Jacques
 
> Regards
> Scott
> 
> On 08/01/2008, Jacopo Cappellato <ti...@sastau.it> wrote:
>>
>> While testing the GL accounting transactions I've found something that
>> could be an issue in the procedure that computes the sales tax
>> adjustment for the invoice.
>> I've noticed that the InvoiceItem.amount for sales tax contains
>> sometimes a number with 3 decimals, even if the arithmetic.properties
>> file we have:
>>
>> salestax.calc.decimals = 3
>> salestax.final.decimals = 2
>> salestax.rounding = ROUND_HALF_UP
>>
>> You can recreate this by creating and invoicing a sales order for 3
>> units of GZ-1000.
>>
>> For example, look at this invoice:
>>
>>
>> https://demo.hotwaxmedia.com/accounting/control/invoiceOverview?invoiceId=CI1
>>
>> Having 3 decimals is an issue for the gl auto posting service for sales
>> invoices because the sales tax item generates an AcctgTransEntry, but
>> the AcctgTransEntry.amount field can only store 2 decimals.
>>
>> I'd appreciate suggestions/hints.
>>
>> Cheers,
>>
>> Jacopo
>>
>

Re: Need help to figure out an issue with sales tax decimals in InvoiceItem

[Scott Gray <le...@gmail.com>]

Hi Jacopo

My understanding of calc and final:
calc - adjustment level rounding
final - the sum of all tax adjustments (tax total) is rounded to this
precision

Perhaps AcctgTransEntry.amount needs to store to a higher precision as well?

Regards
Scott

On 08/01/2008, Jacopo Cappellato <ti...@sastau.it> wrote:
>
> While testing the GL accounting transactions I've found something that
> could be an issue in the procedure that computes the sales tax
> adjustment for the invoice.
> I've noticed that the InvoiceItem.amount for sales tax contains
> sometimes a number with 3 decimals, even if the arithmetic.properties
> file we have:
>
> salestax.calc.decimals = 3
> salestax.final.decimals = 2
> salestax.rounding = ROUND_HALF_UP
>
> You can recreate this by creating and invoicing a sales order for 3
> units of GZ-1000.
>
> For example, look at this invoice:
>
>
> https://demo.hotwaxmedia.com/accounting/control/invoiceOverview?invoiceId=CI1
>
> Having 3 decimals is an issue for the gl auto posting service for sales
> invoices because the sales tax item generates an AcctgTransEntry, but
> the AcctgTransEntry.amount field can only store 2 decimals.
>
> I'd appreciate suggestions/hints.
>
> Cheers,
>
> Jacopo
>