[jira] Commented: (OFBIZ-193) New service to automatically calculate the product's average cost

["Jacopo Cappellato (JIRA)" <ji...@apache.org>]

    [ http://issues.apache.org/jira/browse/OFBIZ-193?page=comments#action_12457082 ] 
            
Jacopo Cappellato commented on OFBIZ-193:
-----------------------------------------

Si, (all)

I'll need to implement (very very soon) a service that computes the inventory average cost of a product (based on current QOH and inventory items' unit costs) and stores it somewhere...
Do you think that storing it in the ProductAverageCost entity is a good idea?
I was thinking to run the average against the items of one facility, but filtering the items by organizationPartyId would be ok too.

Also, apart from the entity definition, I don't see any code that is using the ProductAverageCost in svn... maybe the service you were speaking about is in the Financial component?

I'd love to get your feedback on this subject since I'm going to implement this stuff in a few days.


> 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