currency-precise vs currency-amount fields

[Si Chen-2]

Hi all-

I noticed that while Product.price is currency-precise, certain  
fields which are related to it are only currency-amount, causing loss  
of precision when original prices have 3 or 4 decimal places of  
precision.  Specifically, I think the following should all be changed  
to currency-precise:
SupplierProduct.lastPrice
OrderItem.unitPrice, unitListPrice, unitAverageCost, unitRecurringPrice
InvoiceItem.amount

Is there any reason why these shouldn't be currency-precise?

Best Regards,

Si
[hidden email]

[David E Jones-2]

On Nov 21, 2006, at 8:09 PM, Si Chen wrote:

> Hi all-
>
> I noticed that while Product.price is currency-precise, certain  
> fields which are related to it are only currency-amount, causing  
> loss of precision when original prices have 3 or 4 decimal places  
> of precision.  Specifically, I think the following should all be  
> changed to currency-precise:
> SupplierProduct.lastPrice
> OrderItem.unitPrice, unitListPrice, unitAverageCost,  
> unitRecurringPrice
> InvoiceItem.amount
>
> Is there any reason why these shouldn't be currency-precise?

Most of these look fine, but I'm not sure about OrderItem.unitPrice,  
and I'm pretty uncomfortable with InvoiceItem.amount.

OrderItem.unitPrice may be arguable because some calculation may be  
done based on that, but the InvoiceItem.amount should be something  
that never results in any surprises...

-David

[Si Chen-2]

David,

Yeah, I got quite a surprise when I created products with prices like  
0.4375 and they ended up being 0.43 in orders and on the invoices!

I'm not sure why OrderItem.unitPrice should be different the Product  
or SupplierProduct price?  If someone wants to use a 3- or 4-digit  
price, wouldn't they want it to be the unit price on their orders?

Also, InvoiceItem.amount is not the line item's total amount.  It is  
equivalent to the unitPrice on OrderItem, so ... that's why I thought  
they should all be changed to currency-precise

Of course, for people who are using 2-decimal prices, none of this  
would change things.

On Nov 21, 2006, at 7:53 PM, David E Jones wrote:

>
> On Nov 21, 2006, at 8:09 PM, Si Chen wrote:
>
>> Hi all-
>>
>> I noticed that while Product.price is currency-precise, certain  
>> fields which are related to it are only currency-amount, causing  
>> loss of precision when original prices have 3 or 4 decimal places  
>> of precision.  Specifically, I think the following should all be  
>> changed to currency-precise:
>> SupplierProduct.lastPrice
>> OrderItem.unitPrice, unitListPrice, unitAverageCost,  
>> unitRecurringPrice
>> InvoiceItem.amount
>>
>> Is there any reason why these shouldn't be currency-precise?
>
> Most of these look fine, but I'm not sure about  
> OrderItem.unitPrice, and I'm pretty uncomfortable with  
> InvoiceItem.amount.
>
> OrderItem.unitPrice may be arguable because some calculation may be  
> done based on that, but the InvoiceItem.amount should be something  
> that never results in any surprises...
>
> -David

Best Regards,

Si
[hidden email]

[David E Jones-2]

On Nov 21, 2006, at 9:00 PM, Si Chen wrote:

> David,
>
> Yeah, I got quite a surprise when I created products with prices  
> like 0.4375 and they ended up being 0.43 in orders and on the  
> invoices!

This sounds like a bug, like something is truncating instead of  
rounding...

> I'm not sure why OrderItem.unitPrice should be different the  
> Product or SupplierProduct price?  If someone wants to use a 3- or  
> 4-digit price, wouldn't they want it to be the unit price on their  
> orders?

There is a pretty big different between OrderItem and the Product and  
SupplierProduct entities. The ProductPrice records represent an offer  
from the company to a prospective customer. The OrderItem record  
represents an offer from a customer to the vendor and if accepted by  
the vendor becomes an agreement between the two. When such an  
agreement is made it is for various specific amounts. At this point  
some companies may still want to have a precise amount.

By the time we get to an invoice we really need to have a fixed  
amount that won't change by variations in the calculation, and I  
guess that would be nice on the order as well. This was the reason  
for having a 2 decimal place amount in these two places.

> Also, InvoiceItem.amount is not the line item's total amount.  It  
> is equivalent to the unitPrice on OrderItem, so ... that's why I  
> thought they should all be changed to currency-precise
>
> Of course, for people who are using 2-decimal prices, none of this  
> would change things.

Reviewing it again based on the need for more precise prices common  
in B2B situations where large quantities and very small prices do  
happen perhaps what we should do is something like:

1. increase the precision of these fields, but after an initial  
calculation they should be used for information purposes only

2. add a field to the OrderItem and InvoiceItem that represents the  
calculated item total. This helps the display code so it doesn't have  
to calculate it over and over, but more importantly we now have a  
number that represents what the customer and vendor agreed on for the  
specific quantity, which is what is really important. This would only  
change if the order/invoice changes. To simplify things, and I think  
make a more useful number, this should _not_ include adjustments. It  
should simply represent the total for the quantity and unit price.

Anyone else have any thoughts on this? Implementing this would  
require a bit of effort so we should certainly discuss it first.

-David

> On Nov 21, 2006, at 7:53 PM, David E Jones wrote:
>
>>
>> On Nov 21, 2006, at 8:09 PM, Si Chen wrote:
>>
>>> Hi all-
>>>
>>> I noticed that while Product.price is currency-precise, certain  
>>> fields which are related to it are only currency-amount, causing  
>>> loss of precision when original prices have 3 or 4 decimal places  
>>> of precision.  Specifically, I think the following should all be  
>>> changed to currency-precise:
>>> SupplierProduct.lastPrice
>>> OrderItem.unitPrice, unitListPrice, unitAverageCost,  
>>> unitRecurringPrice
>>> InvoiceItem.amount
>>>
>>> Is there any reason why these shouldn't be currency-precise?
>>
>> Most of these look fine, but I'm not sure about  
>> OrderItem.unitPrice, and I'm pretty uncomfortable with  
>> InvoiceItem.amount.
>>
>> OrderItem.unitPrice may be arguable because some calculation may  
>> be done based on that, but the InvoiceItem.amount should be  
>> something that never results in any surprises...
>>
>> -David
>
> Best Regards,
>
> Si
> [hidden email]
>
>
>

[Si Chen-2]

David,

Actually, the "bug" was fixed just by changing OrderItem.unitPrice  
and InvoiceItem.amount to currency-precise.  The truncation vs.  
rounding behavior differs from database to database, and postgresql  
truncates.

I'm of course for changing these fields to currency-precise.  I was  
able to get OFBiz to work fine by changing all the named fields and  
then changing UtilFormatOut.formatCurrency (see http://
issues.apache.org/jira/browse/OFBIZ-490)

What kind of potential bug would OrderItem/InvoiceItem calculated  
item total fix?  Are you thinking something like 999 * 0.4375 =  
98.5625, so is that 98.56 or 98.57 kind of a thing?

On Nov 21, 2006, at 8:34 PM, David E Jones wrote:

>
> On Nov 21, 2006, at 9:00 PM, Si Chen wrote:
>
>> David,
>>
>> Yeah, I got quite a surprise when I created products with prices  
>> like 0.4375 and they ended up being 0.43 in orders and on the  
>> invoices!
>
> This sounds like a bug, like something is truncating instead of  
> rounding...
>
>> I'm not sure why OrderItem.unitPrice should be different the  
>> Product or SupplierProduct price?  If someone wants to use a 3- or  
>> 4-digit price, wouldn't they want it to be the unit price on their  
>> orders?
>
> There is a pretty big different between OrderItem and the Product  
> and SupplierProduct entities. The ProductPrice records represent an  
> offer from the company to a prospective customer. The OrderItem  
> record represents an offer from a customer to the vendor and if  
> accepted by the vendor becomes an agreement between the two. When  
> such an agreement is made it is for various specific amounts. At  
> this point some companies may still want to have a precise amount.
>
> By the time we get to an invoice we really need to have a fixed  
> amount that won't change by variations in the calculation, and I  
> guess that would be nice on the order as well. This was the reason  
> for having a 2 decimal place amount in these two places.
>
>> Also, InvoiceItem.amount is not the line item's total amount.  It  
>> is equivalent to the unitPrice on OrderItem, so ... that's why I  
>> thought they should all be changed to currency-precise
>>
>> Of course, for people who are using 2-decimal prices, none of this  
>> would change things.
>
> Reviewing it again based on the need for more precise prices common  
> in B2B situations where large quantities and very small prices do  
> happen perhaps what we should do is something like:
>
> 1. increase the precision of these fields, but after an initial  
> calculation they should be used for information purposes only
>
> 2. add a field to the OrderItem and InvoiceItem that represents the  
> calculated item total. This helps the display code so it doesn't  
> have to calculate it over and over, but more importantly we now  
> have a number that represents what the customer and vendor agreed  
> on for the specific quantity, which is what is really important.  
> This would only change if the order/invoice changes. To simplify  
> things, and I think make a more useful number, this should _not_  
> include adjustments. It should simply represent the total for the  
> quantity and unit price.
>
> Anyone else have any thoughts on this? Implementing this would  
> require a bit of effort so we should certainly discuss it first.
>
> -David
>
>
>> On Nov 21, 2006, at 7:53 PM, David E Jones wrote:
>>
>>>
>>> On Nov 21, 2006, at 8:09 PM, Si Chen wrote:
>>>
>>>> Hi all-
>>>>
>>>> I noticed that while Product.price is currency-precise, certain  
>>>> fields which are related to it are only currency-amount, causing  
>>>> loss of precision when original prices have 3 or 4 decimal  
>>>> places of precision.  Specifically, I think the following should  
>>>> all be changed to currency-precise:
>>>> SupplierProduct.lastPrice
>>>> OrderItem.unitPrice, unitListPrice, unitAverageCost,  
>>>> unitRecurringPrice
>>>> InvoiceItem.amount
>>>>
>>>> Is there any reason why these shouldn't be currency-precise?
>>>
>>> Most of these look fine, but I'm not sure about  
>>> OrderItem.unitPrice, and I'm pretty uncomfortable with  
>>> InvoiceItem.amount.
>>>
>>> OrderItem.unitPrice may be arguable because some calculation may  
>>> be done based on that, but the InvoiceItem.amount should be  
>>> something that never results in any surprises...
>>>
>>> -David
>>
>> Best Regards,
>>
>> Si
>> [hidden email]
>>
>>
>>

Best Regards,

Si
[hidden email]

[David E Jones-2]

On Nov 21, 2006, at 10:52 PM, Si Chen wrote:

> David,
>
> Actually, the "bug" was fixed just by changing OrderItem.unitPrice  
> and InvoiceItem.amount to currency-precise.  The truncation vs.  
> rounding behavior differs from database to database, and postgresql  
> truncates.

That would be the problem then... we shouldn't be leaving this sort  
of thing to the database, the application should be making sure the  
data is ready before persisting it. I haven't looked at this stuff in  
a long time and while I think I remember some conversation about it a  
while back I guess it hasn't been worked on yet.

> I'm of course for changing these fields to currency-precise.  I was  
> able to get OFBiz to work fine by changing all the named fields and  
> then changing UtilFormatOut.formatCurrency (see http://
> issues.apache.org/jira/browse/OFBIZ-490)
>
> What kind of potential bug would OrderItem/InvoiceItem calculated  
> item total fix?  Are you thinking something like 999 * 0.4375 =  
> 98.5625, so is that 98.56 or 98.57 kind of a thing?

The concern with this is usually that different companies may  
calculate things differently, or that there could be something funny  
in code somewhere that is recalculating things that result in a  
difference between an order and an invoice amount, or a software  
update that ends up changing an invoice total or makes an invoice no  
longer add up to the persisted total. Because everything needs to be  
rounded to 2 decimal places at some point in order to actually do a  
financial transaction, this can happen.

It gets even worse for things like order changes, partial fulfillment  
and partial invoicing, and so on. Of course, for an order change  
things are recalculated so it's not such a big deal just from that,  
but all potential problems are open game again.

This is why it's nice to have persisted sub-totals that are rounded  
to 2 decimal places. The goal is to get the persisted stuff to the  
point where there is no way to get different results for the total  
calculations. This is another reason, BTW, why adjustments are always  
flat amounts instead of having a percentage or per quantity amount or  
that sort of thing like we did a long time ago. Of course, they still  
have a currency-precise amount, so that just has to be used  
judiciously and the code shouldn't be putting more than 2 decimals in  
there unless it is really required.

-David

> On Nov 21, 2006, at 8:34 PM, David E Jones wrote:
>
>>
>> On Nov 21, 2006, at 9:00 PM, Si Chen wrote:
>>
>>> David,
>>>
>>> Yeah, I got quite a surprise when I created products with prices  
>>> like 0.4375 and they ended up being 0.43 in orders and on the  
>>> invoices!
>>
>> This sounds like a bug, like something is truncating instead of  
>> rounding...
>>
>>> I'm not sure why OrderItem.unitPrice should be different the  
>>> Product or SupplierProduct price?  If someone wants to use a 3-  
>>> or 4-digit price, wouldn't they want it to be the unit price on  
>>> their orders?
>>
>> There is a pretty big different between OrderItem and the Product  
>> and SupplierProduct entities. The ProductPrice records represent  
>> an offer from the company to a prospective customer. The OrderItem  
>> record represents an offer from a customer to the vendor and if  
>> accepted by the vendor becomes an agreement between the two. When  
>> such an agreement is made it is for various specific amounts. At  
>> this point some companies may still want to have a precise amount.
>>
>> By the time we get to an invoice we really need to have a fixed  
>> amount that won't change by variations in the calculation, and I  
>> guess that would be nice on the order as well. This was the reason  
>> for having a 2 decimal place amount in these two places.
>>
>>> Also, InvoiceItem.amount is not the line item's total amount.  It  
>>> is equivalent to the unitPrice on OrderItem, so ... that's why I  
>>> thought they should all be changed to currency-precise
>>>
>>> Of course, for people who are using 2-decimal prices, none of  
>>> this would change things.
>>
>> Reviewing it again based on the need for more precise prices  
>> common in B2B situations where large quantities and very small  
>> prices do happen perhaps what we should do is something like:
>>
>> 1. increase the precision of these fields, but after an initial  
>> calculation they should be used for information purposes only
>>
>> 2. add a field to the OrderItem and InvoiceItem that represents  
>> the calculated item total. This helps the display code so it  
>> doesn't have to calculate it over and over, but more importantly  
>> we now have a number that represents what the customer and vendor  
>> agreed on for the specific quantity, which is what is really  
>> important. This would only change if the order/invoice changes. To  
>> simplify things, and I think make a more useful number, this  
>> should _not_ include adjustments. It should simply represent the  
>> total for the quantity and unit price.
>>
>> Anyone else have any thoughts on this? Implementing this would  
>> require a bit of effort so we should certainly discuss it first.
>>
>> -David
>>
>>
>>> On Nov 21, 2006, at 7:53 PM, David E Jones wrote:
>>>
>>>>
>>>> On Nov 21, 2006, at 8:09 PM, Si Chen wrote:
>>>>
>>>>> Hi all-
>>>>>
>>>>> I noticed that while Product.price is currency-precise, certain  
>>>>> fields which are related to it are only currency-amount,  
>>>>> causing loss of precision when original prices have 3 or 4  
>>>>> decimal places of precision.  Specifically, I think the  
>>>>> following should all be changed to currency-precise:
>>>>> SupplierProduct.lastPrice
>>>>> OrderItem.unitPrice, unitListPrice, unitAverageCost,  
>>>>> unitRecurringPrice
>>>>> InvoiceItem.amount
>>>>>
>>>>> Is there any reason why these shouldn't be currency-precise?
>>>>
>>>> Most of these look fine, but I'm not sure about  
>>>> OrderItem.unitPrice, and I'm pretty uncomfortable with  
>>>> InvoiceItem.amount.
>>>>
>>>> OrderItem.unitPrice may be arguable because some calculation may  
>>>> be done based on that, but the InvoiceItem.amount should be  
>>>> something that never results in any surprises...
>>>>
>>>> -David
>>>
>>> Best Regards,
>>>
>>> Si
>>> [hidden email]
>>>
>>>
>>>
>
> Best Regards,
>
> Si
> [hidden email]
>
>
>

[Si Chen-2]

David,

I know what you're talking about now.  Along those lines,  
OrderItem.unitPrice is a currency-amount, OrderAdjustment.amount  
and .recurringAmount are both currency-precise, but  
InvoiceItem.amount is a currency-amount, so I have seen occasional  
0.01 rounding errors in things like sales tax from order to invoice.  
Maybe we should wrap all of these into one big JIRA issue and attack  
it over time...


On Nov 21, 2006, at 11:58 PM, David E Jones wrote:

>
> On Nov 21, 2006, at 10:52 PM, Si Chen wrote:
>
>> David,
>>
>> Actually, the "bug" was fixed just by changing OrderItem.unitPrice  
>> and InvoiceItem.amount to currency-precise.  The truncation vs.  
>> rounding behavior differs from database to database, and  
>> postgresql truncates.
>
> That would be the problem then... we shouldn't be leaving this sort  
> of thing to the database, the application should be making sure the  
> data is ready before persisting it. I haven't looked at this stuff  
> in a long time and while I think I remember some conversation about  
> it a while back I guess it hasn't been worked on yet.
>
>> I'm of course for changing these fields to currency-precise.  I  
>> was able to get OFBiz to work fine by changing all the named  
>> fields and then changing UtilFormatOut.formatCurrency (see http://
>> issues.apache.org/jira/browse/OFBIZ-490)
>>
>> What kind of potential bug would OrderItem/InvoiceItem calculated  
>> item total fix?  Are you thinking something like 999 * 0.4375 =  
>> 98.5625, so is that 98.56 or 98.57 kind of a thing?
>
> The concern with this is usually that different companies may  
> calculate things differently, or that there could be something  
> funny in code somewhere that is recalculating things that result in  
> a difference between an order and an invoice amount, or a software  
> update that ends up changing an invoice total or makes an invoice  
> no longer add up to the persisted total. Because everything needs  
> to be rounded to 2 decimal places at some point in order to  
> actually do a financial transaction, this can happen.
>
> It gets even worse for things like order changes, partial  
> fulfillment and partial invoicing, and so on. Of course, for an  
> order change things are recalculated so it's not such a big deal  
> just from that, but all potential problems are open game again.
>
> This is why it's nice to have persisted sub-totals that are rounded  
> to 2 decimal places. The goal is to get the persisted stuff to the  
> point where there is no way to get different results for the total  
> calculations. This is another reason, BTW, why adjustments are  
> always flat amounts instead of having a percentage or per quantity  
> amount or that sort of thing like we did a long time ago. Of  
> course, they still have a currency-precise amount, so that just has  
> to be used judiciously and the code shouldn't be putting more than  
> 2 decimals in there unless it is really required.
>
> -David
>
>
>
>> On Nov 21, 2006, at 8:34 PM, David E Jones wrote:
>>
>>>
>>> On Nov 21, 2006, at 9:00 PM, Si Chen wrote:
>>>
>>>> David,
>>>>
>>>> Yeah, I got quite a surprise when I created products with prices  
>>>> like 0.4375 and they ended up being 0.43 in orders and on the  
>>>> invoices!
>>>
>>> This sounds like a bug, like something is truncating instead of  
>>> rounding...
>>>
>>>> I'm not sure why OrderItem.unitPrice should be different the  
>>>> Product or SupplierProduct price?  If someone wants to use a 3-  
>>>> or 4-digit price, wouldn't they want it to be the unit price on  
>>>> their orders?
>>>
>>> There is a pretty big different between OrderItem and the Product  
>>> and SupplierProduct entities. The ProductPrice records represent  
>>> an offer from the company to a prospective customer. The  
>>> OrderItem record represents an offer from a customer to the  
>>> vendor and if accepted by the vendor becomes an agreement between  
>>> the two. When such an agreement is made it is for various  
>>> specific amounts. At this point some companies may still want to  
>>> have a precise amount.
>>>
>>> By the time we get to an invoice we really need to have a fixed  
>>> amount that won't change by variations in the calculation, and I  
>>> guess that would be nice on the order as well. This was the  
>>> reason for having a 2 decimal place amount in these two places.
>>>
>>>> Also, InvoiceItem.amount is not the line item's total amount.  
>>>> It is equivalent to the unitPrice on OrderItem, so ... that's  
>>>> why I thought they should all be changed to currency-precise
>>>>
>>>> Of course, for people who are using 2-decimal prices, none of  
>>>> this would change things.
>>>
>>> Reviewing it again based on the need for more precise prices  
>>> common in B2B situations where large quantities and very small  
>>> prices do happen perhaps what we should do is something like:
>>>
>>> 1. increase the precision of these fields, but after an initial  
>>> calculation they should be used for information purposes only
>>>
>>> 2. add a field to the OrderItem and InvoiceItem that represents  
>>> the calculated item total. This helps the display code so it  
>>> doesn't have to calculate it over and over, but more importantly  
>>> we now have a number that represents what the customer and vendor  
>>> agreed on for the specific quantity, which is what is really  
>>> important. This would only change if the order/invoice changes.  
>>> To simplify things, and I think make a more useful number, this  
>>> should _not_ include adjustments. It should simply represent the  
>>> total for the quantity and unit price.
>>>
>>> Anyone else have any thoughts on this? Implementing this would  
>>> require a bit of effort so we should certainly discuss it first.
>>>
>>> -David
>>>
>>>
>>>> On Nov 21, 2006, at 7:53 PM, David E Jones wrote:
>>>>
>>>>>
>>>>> On Nov 21, 2006, at 8:09 PM, Si Chen wrote:
>>>>>
>>>>>> Hi all-
>>>>>>
>>>>>> I noticed that while Product.price is currency-precise,  
>>>>>> certain fields which are related to it are only currency-
>>>>>> amount, causing loss of precision when original prices have 3  
>>>>>> or 4 decimal places of precision.  Specifically, I think the  
>>>>>> following should all be changed to currency-precise:
>>>>>> SupplierProduct.lastPrice
>>>>>> OrderItem.unitPrice, unitListPrice, unitAverageCost,  
>>>>>> unitRecurringPrice
>>>>>> InvoiceItem.amount
>>>>>>
>>>>>> Is there any reason why these shouldn't be currency-precise?
>>>>>
>>>>> Most of these look fine, but I'm not sure about  
>>>>> OrderItem.unitPrice, and I'm pretty uncomfortable with  
>>>>> InvoiceItem.amount.
>>>>>
>>>>> OrderItem.unitPrice may be arguable because some calculation  
>>>>> may be done based on that, but the InvoiceItem.amount should be  
>>>>> something that never results in any surprises...
>>>>>
>>>>> -David
>>>>
>>>> Best Regards,
>>>>
>>>> Si
>>>> [hidden email]
>>>>
>>>>
>>>>
>>
>> Best Regards,
>>
>> Si
>> [hidden email]
>>
>>
>>

Best Regards,

Si
[hidden email]

[David E Jones-2]

On Nov 22, 2006, at 11:08 AM, Si Chen wrote:

> David,
>
> I know what you're talking about now.  Along those lines,  
> OrderItem.unitPrice is a currency-amount, OrderAdjustment.amount  
> and .recurringAmount are both currency-precise, but  
> InvoiceItem.amount is a currency-amount, so I have seen occasional  
> 0.01 rounding errors in things like sales tax from order to  
> invoice.  Maybe we should wrap all of these into one big JIRA issue  
> and attack it over time...

That sounds fine to me. To make it clear the approach I am for  
related to the OrderItem and InvoiceItem amounts is what I described  
earlier:

>>> Reviewing it again based on the need for more precise prices  
>>> common in B2B situations where large quantities and very small  
>>> prices do happen perhaps what we should do is something like:
>>>
>>> 1. increase the precision of these fields, but after an initial  
>>> calculation they should be used for information purposes only
>>>
>>> 2. add a field to the OrderItem and InvoiceItem that represents  
>>> the calculated item total. This helps the display code so it  
>>> doesn't have to calculate it over and over, but more importantly  
>>> we now have a number that represents what the customer and vendor  
>>> agreed on for the specific quantity, which is what is really  
>>> important. This would only change if the order/invoice changes.  
>>> To simplify things, and I think make a more useful number, this  
>>> should _not_ include adjustments. It should simply represent the  
>>> total for the quantity and unit price.

-David

> On Nov 21, 2006, at 11:58 PM, David E Jones wrote:
>
>>
>> On Nov 21, 2006, at 10:52 PM, Si Chen wrote:
>>
>>> David,
>>>
>>> Actually, the "bug" was fixed just by changing  
>>> OrderItem.unitPrice and InvoiceItem.amount to currency-precise.  
>>> The truncation vs. rounding behavior differs from database to  
>>> database, and postgresql truncates.
>>
>> That would be the problem then... we shouldn't be leaving this  
>> sort of thing to the database, the application should be making  
>> sure the data is ready before persisting it. I haven't looked at  
>> this stuff in a long time and while I think I remember some  
>> conversation about it a while back I guess it hasn't been worked  
>> on yet.
>>
>>> I'm of course for changing these fields to currency-precise.  I  
>>> was able to get OFBiz to work fine by changing all the named  
>>> fields and then changing UtilFormatOut.formatCurrency (see http://
>>> issues.apache.org/jira/browse/OFBIZ-490)
>>>
>>> What kind of potential bug would OrderItem/InvoiceItem calculated  
>>> item total fix?  Are you thinking something like 999 * 0.4375 =  
>>> 98.5625, so is that 98.56 or 98.57 kind of a thing?
>>
>> The concern with this is usually that different companies may  
>> calculate things differently, or that there could be something  
>> funny in code somewhere that is recalculating things that result  
>> in a difference between an order and an invoice amount, or a  
>> software update that ends up changing an invoice total or makes an  
>> invoice no longer add up to the persisted total. Because  
>> everything needs to be rounded to 2 decimal places at some point  
>> in order to actually do a financial transaction, this can happen.
>>
>> It gets even worse for things like order changes, partial  
>> fulfillment and partial invoicing, and so on. Of course, for an  
>> order change things are recalculated so it's not such a big deal  
>> just from that, but all potential problems are open game again.
>>
>> This is why it's nice to have persisted sub-totals that are  
>> rounded to 2 decimal places. The goal is to get the persisted  
>> stuff to the point where there is no way to get different results  
>> for the total calculations. This is another reason, BTW, why  
>> adjustments are always flat amounts instead of having a percentage  
>> or per quantity amount or that sort of thing like we did a long  
>> time ago. Of course, they still have a currency-precise amount, so  
>> that just has to be used judiciously and the code shouldn't be  
>> putting more than 2 decimals in there unless it is really required.
>>
>> -David
>>
>>
>>
>>> On Nov 21, 2006, at 8:34 PM, David E Jones wrote:
>>>
>>>>
>>>> On Nov 21, 2006, at 9:00 PM, Si Chen wrote:
>>>>
>>>>> David,
>>>>>
>>>>> Yeah, I got quite a surprise when I created products with  
>>>>> prices like 0.4375 and they ended up being 0.43 in orders and  
>>>>> on the invoices!
>>>>
>>>> This sounds like a bug, like something is truncating instead of  
>>>> rounding...
>>>>
>>>>> I'm not sure why OrderItem.unitPrice should be different the  
>>>>> Product or SupplierProduct price?  If someone wants to use a 3-  
>>>>> or 4-digit price, wouldn't they want it to be the unit price on  
>>>>> their orders?
>>>>
>>>> There is a pretty big different between OrderItem and the  
>>>> Product and SupplierProduct entities. The ProductPrice records  
>>>> represent an offer from the company to a prospective customer.  
>>>> The OrderItem record represents an offer from a customer to the  
>>>> vendor and if accepted by the vendor becomes an agreement  
>>>> between the two. When such an agreement is made it is for  
>>>> various specific amounts. At this point some companies may still  
>>>> want to have a precise amount.
>>>>
>>>> By the time we get to an invoice we really need to have a fixed  
>>>> amount that won't change by variations in the calculation, and I  
>>>> guess that would be nice on the order as well. This was the  
>>>> reason for having a 2 decimal place amount in these two places.
>>>>
>>>>> Also, InvoiceItem.amount is not the line item's total amount.  
>>>>> It is equivalent to the unitPrice on OrderItem, so ... that's  
>>>>> why I thought they should all be changed to currency-precise
>>>>>
>>>>> Of course, for people who are using 2-decimal prices, none of  
>>>>> this would change things.
>>>>
>>>> Reviewing it again based on the need for more precise prices  
>>>> common in B2B situations where large quantities and very small  
>>>> prices do happen perhaps what we should do is something like:
>>>>
>>>> 1. increase the precision of these fields, but after an initial  
>>>> calculation they should be used for information purposes only
>>>>
>>>> 2. add a field to the OrderItem and InvoiceItem that represents  
>>>> the calculated item total. This helps the display code so it  
>>>> doesn't have to calculate it over and over, but more importantly  
>>>> we now have a number that represents what the customer and  
>>>> vendor agreed on for the specific quantity, which is what is  
>>>> really important. This would only change if the order/invoice  
>>>> changes. To simplify things, and I think make a more useful  
>>>> number, this should _not_ include adjustments. It should simply  
>>>> represent the total for the quantity and unit price.
>>>>
>>>> Anyone else have any thoughts on this? Implementing this would  
>>>> require a bit of effort so we should certainly discuss it first.
>>>>
>>>> -David
>>>>
>>>>
>>>>> On Nov 21, 2006, at 7:53 PM, David E Jones wrote:
>>>>>
>>>>>>
>>>>>> On Nov 21, 2006, at 8:09 PM, Si Chen wrote:
>>>>>>
>>>>>>> Hi all-
>>>>>>>
>>>>>>> I noticed that while Product.price is currency-precise,  
>>>>>>> certain fields which are related to it are only currency-
>>>>>>> amount, causing loss of precision when original prices have 3  
>>>>>>> or 4 decimal places of precision.  Specifically, I think the  
>>>>>>> following should all be changed to currency-precise:
>>>>>>> SupplierProduct.lastPrice
>>>>>>> OrderItem.unitPrice, unitListPrice, unitAverageCost,  
>>>>>>> unitRecurringPrice
>>>>>>> InvoiceItem.amount
>>>>>>>
>>>>>>> Is there any reason why these shouldn't be currency-precise?
>>>>>>
>>>>>> Most of these look fine, but I'm not sure about  
>>>>>> OrderItem.unitPrice, and I'm pretty uncomfortable with  
>>>>>> InvoiceItem.amount.
>>>>>>
>>>>>> OrderItem.unitPrice may be arguable because some calculation  
>>>>>> may be done based on that, but the InvoiceItem.amount should  
>>>>>> be something that never results in any surprises...
>>>>>>
>>>>>> -David
>>>>>
>>>>> Best Regards,
>>>>>
>>>>> Si
>>>>> [hidden email]
>>>>>
>>>>>
>>>>>
>>>
>>> Best Regards,
>>>
>>> Si
>>> [hidden email]
>>>
>>>
>>>
>
> Best Regards,
>
> Si
> [hidden email]
>
>
>

[Jacques Le Roux]

From: "David E Jones" <[hidden email]>

>
> On Nov 22, 2006, at 11:08 AM, Si Chen wrote:
>
> > David,
> >
> > I know what you're talking about now.  Along those lines,  
> > OrderItem.unitPrice is a currency-amount, OrderAdjustment.amount  
> > and .recurringAmount are both currency-precise, but  
> > InvoiceItem.amount is a currency-amount, so I have seen occasional  
> > 0.01 rounding errors in things like sales tax from order to  
> > invoice.  Maybe we should wrap all of these into one big JIRA issue  
> > and attack it over time...
>
> That sounds fine to me. To make it clear the approach I am for  
> related to the OrderItem and InvoiceItem amounts is what I described  
> earlier:
>
> >>> Reviewing it again based on the need for more precise prices  
> >>> common in B2B situations where large quantities and very small  
> >>> prices do happen perhaps what we should do is something like:
> >>>
> >>> 1. increase the precision of these fields, but after an initial  
> >>> calculation they should be used for information purposes only
> >>>
> >>> 2. add a field to the OrderItem and InvoiceItem that represents  
> >>> the calculated item total. This helps the display code so it  
> >>> doesn't have to calculate it over and over, but more importantly  
> >>> we now have a number that represents what the customer and vendor  
> >>> agreed on for the specific quantity, which is what is really  
> >>> important. This would only change if the order/invoice changes.  
> >>> To simplify things, and I think make a more useful number, this  
> >>> should _not_ include adjustments. It should simply represent the  
> >>> total for the quantity and unit price.

I agree with Davis, this idea to store the value seems really a good way to go

Jacques
 

> -David
>
>
> > On Nov 21, 2006, at 11:58 PM, David E Jones wrote:
> >
> >>
> >> On Nov 21, 2006, at 10:52 PM, Si Chen wrote:
> >>
> >>> David,
> >>>
> >>> Actually, the "bug" was fixed just by changing  
> >>> OrderItem.unitPrice and InvoiceItem.amount to currency-precise.  
> >>> The truncation vs. rounding behavior differs from database to  
> >>> database, and postgresql truncates.
> >>
> >> That would be the problem then... we shouldn't be leaving this  
> >> sort of thing to the database, the application should be making  
> >> sure the data is ready before persisting it. I haven't looked at  
> >> this stuff in a long time and while I think I remember some  
> >> conversation about it a while back I guess it hasn't been worked  
> >> on yet.
> >>
> >>> I'm of course for changing these fields to currency-precise.  I  
> >>> was able to get OFBiz to work fine by changing all the named  
> >>> fields and then changing UtilFormatOut.formatCurrency (see http://
> >>> issues.apache.org/jira/browse/OFBIZ-490)
> >>>
> >>> What kind of potential bug would OrderItem/InvoiceItem calculated  
> >>> item total fix?  Are you thinking something like 999 * 0.4375 =  
> >>> 98.5625, so is that 98.56 or 98.57 kind of a thing?
> >>
> >> The concern with this is usually that different companies may  
> >> calculate things differently, or that there could be something  
> >> funny in code somewhere that is recalculating things that result  
> >> in a difference between an order and an invoice amount, or a  
> >> software update that ends up changing an invoice total or makes an  
> >> invoice no longer add up to the persisted total. Because  
> >> everything needs to be rounded to 2 decimal places at some point  
> >> in order to actually do a financial transaction, this can happen.
> >>
> >> It gets even worse for things like order changes, partial  
> >> fulfillment and partial invoicing, and so on. Of course, for an  
> >> order change things are recalculated so it's not such a big deal  
> >> just from that, but all potential problems are open game again.
> >>
> >> This is why it's nice to have persisted sub-totals that are  
> >> rounded to 2 decimal places. The goal is to get the persisted  
> >> stuff to the point where there is no way to get different results  
> >> for the total calculations. This is another reason, BTW, why  
> >> adjustments are always flat amounts instead of having a percentage  
> >> or per quantity amount or that sort of thing like we did a long  
> >> time ago. Of course, they still have a currency-precise amount, so  
> >> that just has to be used judiciously and the code shouldn't be  
> >> putting more than 2 decimals in there unless it is really required.
> >>
> >> -David
> >>
> >>
> >>
> >>> On Nov 21, 2006, at 8:34 PM, David E Jones wrote:
> >>>
> >>>>
> >>>> On Nov 21, 2006, at 9:00 PM, Si Chen wrote:
> >>>>
> >>>>> David,
> >>>>>
> >>>>> Yeah, I got quite a surprise when I created products with  
> >>>>> prices like 0.4375 and they ended up being 0.43 in orders and  
> >>>>> on the invoices!
> >>>>
> >>>> This sounds like a bug, like something is truncating instead of  
> >>>> rounding...
> >>>>
> >>>>> I'm not sure why OrderItem.unitPrice should be different the  
> >>>>> Product or SupplierProduct price?  If someone wants to use a 3-  
> >>>>> or 4-digit price, wouldn't they want it to be the unit price on  
> >>>>> their orders?
> >>>>
> >>>> There is a pretty big different between OrderItem and the  
> >>>> Product and SupplierProduct entities. The ProductPrice records  
> >>>> represent an offer from the company to a prospective customer.  
> >>>> The OrderItem record represents an offer from a customer to the  
> >>>> vendor and if accepted by the vendor becomes an agreement  
> >>>> between the two. When such an agreement is made it is for  
> >>>> various specific amounts. At this point some companies may still  
> >>>> want to have a precise amount.
> >>>>
> >>>> By the time we get to an invoice we really need to have a fixed  
> >>>> amount that won't change by variations in the calculation, and I  
> >>>> guess that would be nice on the order as well. This was the  
> >>>> reason for having a 2 decimal place amount in these two places.
> >>>>
> >>>>> Also, InvoiceItem.amount is not the line item's total amount.  
> >>>>> It is equivalent to the unitPrice on OrderItem, so ... that's  
> >>>>> why I thought they should all be changed to currency-precise
> >>>>>
> >>>>> Of course, for people who are using 2-decimal prices, none of  
> >>>>> this would change things.
> >>>>
> >>>> Reviewing it again based on the need for more precise prices  
> >>>> common in B2B situations where large quantities and very small  
> >>>> prices do happen perhaps what we should do is something like:
> >>>>
> >>>> 1. increase the precision of these fields, but after an initial  
> >>>> calculation they should be used for information purposes only
> >>>>
> >>>> 2. add a field to the OrderItem and InvoiceItem that represents  
> >>>> the calculated item total. This helps the display code so it  
> >>>> doesn't have to calculate it over and over, but more importantly  
> >>>> we now have a number that represents what the customer and  
> >>>> vendor agreed on for the specific quantity, which is what is  
> >>>> really important. This would only change if the order/invoice  
> >>>> changes. To simplify things, and I think make a more useful  
> >>>> number, this should _not_ include adjustments. It should simply  
> >>>> represent the total for the quantity and unit price.
> >>>>
> >>>> Anyone else have any thoughts on this? Implementing this would  
> >>>> require a bit of effort so we should certainly discuss it first.
> >>>>
> >>>> -David
> >>>>
> >>>>
> >>>>> On Nov 21, 2006, at 7:53 PM, David E Jones wrote:
> >>>>>
> >>>>>>
> >>>>>> On Nov 21, 2006, at 8:09 PM, Si Chen wrote:
> >>>>>>
> >>>>>>> Hi all-
> >>>>>>>
> >>>>>>> I noticed that while Product.price is currency-precise,  
> >>>>>>> certain fields which are related to it are only currency-
> >>>>>>> amount, causing loss of precision when original prices have 3  
> >>>>>>> or 4 decimal places of precision.  Specifically, I think the  
> >>>>>>> following should all be changed to currency-precise:
> >>>>>>> SupplierProduct.lastPrice
> >>>>>>> OrderItem.unitPrice, unitListPrice, unitAverageCost,  
> >>>>>>> unitRecurringPrice
> >>>>>>> InvoiceItem.amount
> >>>>>>>
> >>>>>>> Is there any reason why these shouldn't be currency-precise?
> >>>>>>
> >>>>>> Most of these look fine, but I'm not sure about  
> >>>>>> OrderItem.unitPrice, and I'm pretty uncomfortable with  
> >>>>>> InvoiceItem.amount.
> >>>>>>
> >>>>>> OrderItem.unitPrice may be arguable because some calculation  
> >>>>>> may be done based on that, but the InvoiceItem.amount should  
> >>>>>> be something that never results in any surprises...
> >>>>>>
> >>>>>> -David
> >>>>>
> >>>>> Best Regards,
> >>>>>
> >>>>> Si
> >>>>> [hidden email]
> >>>>>
> >>>>>
> >>>>>
> >>>
> >>> Best Regards,
> >>>
> >>> Si
> >>> [hidden email]
> >>>
> >>>
> >>>
> >
> > Best Regards,
> >
> > Si
> > [hidden email]
> >
> >
> >