[ http://issues.apache.org/jira/browse/OFBIZ-193?page=comments#action_12432353 ] Daniel Kunkel commented on OFBIZ-193: -------------------------------------
Please consider a slightly different technique that I think will lead to penny perfect cog calculations. Rather than trying to keep one average cost value, keep the total spent on the product, and total quantity. At any given time you can divide to get the current average cost, but I believe this system will be far superior avoiding rounding and other accounting issues that crop up with fractional costs. To illustrate, imagine buying 10 million chips for $1000 and selling them 1 at a time... Using the normal accounting the cost would always round to 0 even through it is really $ 0.0001. Unless this is accounted for, the cog account would be $1000 off. The above suggestion would work such that most customers would pay 0 for cog, but every 100th (If I calculated right) would pay $ 0.01... coming out exactly right every time. Thanks > New service to automatically calculate the product's average cost > ----------------------------------------------------------------- > > Key: OFBIZ-193 > URL: http://issues.apache.org/jira/browse/OFBIZ-193 > Project: OFBiz (The Open for Business Project) > Issue Type: New Feature > Components: order, product > Reporter: Jacopo Cappellato > Priority: Minor > > Implement a service to automatically calculate the average cost > (ProductPrice.averageCost) based on previous purchases. > Right now the value is just set manually. These averages would have nothing > to do with what is used for inventory costing. The average could somewhat > easily be calculated from the order or invoice histories as a weighted sum > based on the quantity and price. (suggested By David Jones) > Such a service could be based on a SQL command like this one: > SELECT > PRODUCT_ID, SUM(UNIT_PRICE * QUANTITY) / SUM(QUANTITY) AS AVERAGE_COST > FROM > ORDER_ITEM AS OI, ORDER_HEADER AS OH > WHERE > OH.ORDER_ID = OI.ORDER_ID AND > OH.ORDER_TYPE_ID = 'PURCHASE_ORDER' AND > ORDER_DATE > '2004-12-31 00:00:00.0' > GROUP BY > PRODUCT_ID > However this is only a draft (e.g. we should also take into account cancelled > items, adjustments etc...) -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
