Hi Tim, The fields within OFBiz entities can have types including currency-amount, currency-precise and fixed-point.
These are translated to a database native type depending on the database. In most ANSI SQL databases, the above are NUMERIC( 18,2 ), NUMERIC( 18, 3 ) and NUMERIC( 18, 6 ) respectively. For example, the Postgres definitions are here: https://github.com/apache/ofbiz-framework/blob/trunk/framework/entity/fieldtype/fieldtypepostgres.xml#L33 . fixed-point is used for percentages, quantities and averages, but not for most money related things. The CostComponent.cost you noticed is defined at https://github.com/apache/ofbiz-framework/blob/trunk/applications/datamodel/entitydef/product-entitymodel.xml#L801 . You could try changing product price fields to use fixed-point instead of currency-precise. It is arguable a unit price is *not* a currency amount, because you would order small and cheap items like your washers in bulk, never one at a time. Cheers Paul Foxworthy On 4 January 2018 at 23:20, Boyden, Timothy <[email protected]> wrote: > Based on my reading of the comments in arithmetic.properties, it only > controls the display (rounding/format) of decimals, not the actual > underlying database field. In most cases, the related database fields only > have 3 (or less) decimal places, so you cannot store a value out to 5 > decimal places, to round from. > > What controls the creation of the fields? Is there/can there be a > properties file that lets you set the precision/scale of the decimal type > fields? > > Examples: > > PRODUCT_PRICE.PRICE = decimal(18,3) > QUOTE_ITEM.QUOTE_UNIT_PRICE = decimal(18,2) > INVOICE_ITEM.AMOUNT = decimal(18,3) > FIN_ACCOUNT_TRANS.AMOUNT = decimal(18,2) > ACCTG_TRANS_ENTRY.AMOUNT = decimal(18,2) > ORDER_ITEM.UNIT_PRICE = decimal(18,3) > ORDER_ITEM.UNIT_AVERAGE_COST = decimal(18,2) > REQUIREMENT_BUDGET_ALLOCATION.AMOUNT = decimal(18,2) > SUPPLIER_PRODUCT.LAST_PRICE = decimal(18,3) > SUPPLIER_PRODUCT.SHIPPING_PRICE = decimal(18,3) > > I can't easily identify what tables hold bill of materials and routings, > but my guess is, I would see similar issues there. > > It is not every cost or price field though, which to me is a baffling > inconsistency. For example, COST_COMPONENT.COST is decimal(18,6), which > would be fine. > > Thanks, > > Tim > > -----Original Message----- > From: Paul Foxworthy [mailto:[email protected]] > Sent: Wednesday, January 3, 2018 8:47 PM > To: [email protected] > Subject: Re: Add Product Supplier broken - similar to Edit Facility bug > > Hi again, > > I've changed my mind about a Jira for the moment. I think this is not > exactly the same thing as OFBIZ-6436. We could create a new issue in > future, but I'd like to consider this some more. > > I see the general point that there's a need for more decimal places in > prices. > > OFBiz has configuration options for decimal places in orders, invoices, > indirect taxes, financial accounts and general ledger reports. (All of > these are in https://github.com/apache/ofbiz/blob/trunk/applications/ > accounting/config/arithmetic.properties > <https://github.com/apache/ofbiz/blob/trunk/applications/ > accounting/config/arithmetic.properties#L24> > ). > > Here are questions I'd like Tim and everyone to consider: > > - Is a rule for product price DPs a separate thing to rules for other > amounts specified in arithmetic.properties? I think the answer is "yes". > > - Should OFBiz just accept a price to whatever level of precision a user > wants to enter it, and not round it? > > - Product pricing and the number of DPs might vary significantly for > different products. Is there any need to enforce some sort of consistency > here? > > - If enforcement is needed, is a single configuration option for product > pricing sufficient? > > - If enforcement is needed, should it be more fine-grained than one rule > for all products? > > Should the number of DPs be an attribute of some entity, perhaps > ProductCategory or SupplierProduct? > > Cheers > > Paul Foxworthy > > > On 4 January 2018 at 11:57, Paul Foxworthy <[email protected]> wrote: > > > Thanks, that makes sense. > > > > Maybe further discussion should move to https://issues.apache.org/ > > jira/browse/OFBIZ-6436 . I will put a comment there with your link to > > the NCES standard. > > > > Cheers > > > > Paul Foxworthy > > > > P.S. I buy my gasxxx petrol in litres and one place suffices :-). > > > > > > On 3 January 2018 at 23:20, Boyden, Timothy > > <[email protected]> > > wrote: > > > >> Same reason your gas prices are quoted to 3 digits at the pump. Those > >> extra cents amount to millions of dollars after millions of gallons > >> or hundreds of thousands of component sales. It is also necessary in > >> order to capture actual cost of products when using only a few of the > >> components in a part, or like I said, grams of a precious metal > >> coating on electrical connectors. Measurements to convert from grams > >> to a purchase UM of ounces will almost always be a 5 digit decimal > >> value. And you can bet those precious metal vendors count their > hundreds and thousandths of ounces. > >> Also, for the purposes of statistical analysis, which is used to > >> analyze production quality, the US national standard is (at least) 4 > >> decimal > >> places: https://nces.ed.gov/statprog/2002/std5_3.asp > >> > >> -Tim > >> > >> > >> -----Original Message----- > >> From: Paul Foxworthy [mailto:[email protected]] > >> Sent: Tuesday, January 2, 2018 7:43 PM > >> To: [email protected] > >> Subject: Re: Add Product Supplier broken - similar to Edit Facility > >> bug > >> > >> Hi Tim, > >> > >> Thanks for your feedback. > >> > >> I am curious what you would use the unit price for. Do you order your > >> washers in quantities of thousands, and might the price vary if you > >> ordered in quantities like 10,000 or 100,000? So acquisition price > >> might depend on quantity ordered. If you are calculating cost price > >> for a manufactured product, the prices for washers might be imprecise > >> but that's unlikely to be most significant contributor to the cost > price. What am I missing? > >> > >> Thanks > >> > >> Paul Foxworthy > >> > >> > >> On 24 December 2017 at 22:42, Boyden, Timothy > >> <[email protected]> > >> wrote: > >> > >> > The issue that I had was that the underlying field would not accept > >> > a value of 0.00590. I had to enter the value as a lot of 10000, to > >> > get the price to 59.00, before it would accept the value. If I read > >> > the first link correctly, that is referring to display format, and > >> > not validation of the field input, or the size of fields. Looking > >> > at the table spec, indeed the LAST_PRICE field of SUPPLIER_PRODUCT > >> > is only a decimal field with 3 decimal place precision. It should be > at least 5. > >> > All price fields should be at least 5 decimal place precision. > >> > > >> > -Tim > >> > > >> > -----Original Message----- > >> > From: Paul Foxworthy [mailto:[email protected]] > >> > Sent: Saturday, December 23, 2017 7:36 PM > >> > To: [email protected] > >> > Subject: Re: Add Product Supplier broken - similar to Edit Facility > >> > bug > >> > > >> > Hi Tim, > >> > > >> > Decimal places are configurable for many things in OFBiz - see > >> > https://github.com/apache/ofbiz/blob/trunk/applications/ > >> > accounting/config/arithmetic.properties#L24 > >> > and below. > >> > > >> > I can see product pricing should be configurable too. In > >> > https://issues.apache.org/jira/browse/OFBIZ-6436, Ingo Wolfmayer > >> > was proposing adding a similar thing for prices. > >> > > >> > Cheers > >> > > >> > Paul Foxworthy > >> > > >> > > >> > On 24 December 2017 at 08:02, Boyden, Timothy > >> > <[email protected]> > >> > wrote: > >> > > >> > > Also, it looks like the Last Purchase Price field doesn't accept > >> > > 5 digit decimal places. Major bad for a manufacturing system. A > >> > > steel washer, priced per each, would be like $0.00590 USD. > >> > > Similarly, precious metals, like silver for silver plating, would > >> > > be ordered in > >> > 5-digit decimal ounces. > >> > > That needs to get fixed. > >> > > > >> > > Thanks, > >> > > > >> > > Tim > >> > > > >> > > >> > > >> > > >> > -- > >> > Coherent Software Australia Pty Ltd PO Box 2773 Cheltenham Vic 3192 > >> > Australia > >> > > >> > Phone: +61 3 9585 6788 > >> > Web: http://www.coherentsoftware.com.au/ > >> > Email: [email protected] > >> > > >> > >> > >> > >> -- > >> Coherent Software Australia Pty Ltd > >> PO Box 2773 > >> Cheltenham Vic 3192 > >> Australia > >> > >> Phone: +61 3 9585 6788 > >> Web: http://www.coherentsoftware.com.au/ > >> Email: [email protected] > >> > > > > > > > > -- > > Coherent Software Australia Pty Ltd > > PO Box 2773 > > Cheltenham Vic 3192 > > Australia > > > > Phone: +61 3 9585 6788 <+61%203%209585%206788> > > Web: http://www.coherentsoftware.com.au/ > > Email: [email protected] > > > > > > -- > Coherent Software Australia Pty Ltd > PO Box 2773 > Cheltenham Vic 3192 > Australia > > Phone: +61 3 9585 6788 > Web: http://www.coherentsoftware.com.au/ > Email: [email protected] > -- Coherent Software Australia Pty Ltd PO Box 2773 Cheltenham Vic 3192 Australia Phone: +61 3 9585 6788 Web: http://www.coherentsoftware.com.au/ Email: [email protected]
