[ http://issues.apache.org/jira/browse/OFBIZ-193?page=comments#action_12432663 ] Jacopo Cappellato commented on OFBIZ-193: -----------------------------------------
Yes, this makes sense, thanks for pointing out this: I'm not going to work on this issue in the short term (I just moved it here from the old Jira issue tracker). So are you suggesting that the only thing that we should implement is a service (or something else) that get the cost from the ProductAverageCost and updates with this value the ProductPrice entity? This is interesting. Thanks, Jacopo > 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
