From: "David E Jones" <[EMAIL PROTECTED]> > > 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 > >>>>> [EMAIL PROTECTED] > >>>>> > >>>>> > >>>>> > >>> > >>> Best Regards, > >>> > >>> Si > >>> [EMAIL PROTECTED] > >>> > >>> > >>> > > > > Best Regards, > > > > Si > > [EMAIL PROTECTED] > > > > > >
