OFBiz › OFBiz - Dev

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

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

12 messages Options

Nicolas Malin (Jira)

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

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

Nicolas Malin (Jira)

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

[ 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

Nicolas Malin (Jira)

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

In reply to this post by Nicolas Malin (Jira)

[ http://issues.apache.org/jira/browse/OFBIZ-193?page=comments#action_12432355 ]

Daniel Kunkel commented on OFBIZ-193:
-------------------------------------

Ignore that last bit... I guess last night (Saturday) was better than I remember.

Nicolas Malin (Jira)

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

In reply to this post by Nicolas Malin (Jira)

[ http://issues.apache.org/jira/browse/OFBIZ-193?page=comments#action_12432357 ]

Jacques Le Roux commented on OFBIZ-193:
---------------------------------------

Daniel,

Do you want that we remove your 2 comments ?

Jacques

Daniel Kunkel

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

Yes

Thanks

On Sun, 2006-09-03 at 10:16 -0700, Jacques Le Roux (JIRA) wrote:

> [ http://issues.apache.org/jira/browse/OFBIZ-193?page=comments#action_12432357 ]
>
> Jacques Le Roux commented on OFBIZ-193:
> ---------------------------------------
>
> Daniel,
>
> Do you want that we remove your 2 comments ?
>
> Jacques
>
> > 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...)
>

Nicolas Malin (Jira)

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

In reply to this post by Nicolas Malin (Jira)

[ http://issues.apache.org/jira/browse/OFBIZ-193?page=all ]

Jacques Le Roux updated OFBIZ-193:
----------------------------------

Comment: was deleted

Nicolas Malin (Jira)

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

In reply to this post by Nicolas Malin (Jira)

[ http://issues.apache.org/jira/browse/OFBIZ-193?page=all ]

Jacques Le Roux updated OFBIZ-193:
----------------------------------

Comment: was deleted

Nicolas Malin (Jira)

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

In reply to this post by Nicolas Malin (Jira)

[ http://issues.apache.org/jira/browse/OFBIZ-193?page=all ]

Jacques Le Roux updated OFBIZ-193:
----------------------------------

Comment: was deleted

Nicolas Malin (Jira)

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

In reply to this post by Nicolas Malin (Jira)

[ http://issues.apache.org/jira/browse/OFBIZ-193?page=comments#action_12432641 ]

Si Chen commented on OFBIZ-193:
-------------------------------

Jacopo,

There already is a ProductAverageCost entity which records an averageCost based on productId, organizationPartyId when items are received. Maybe your logic should check the values in there?

Si

Nicolas Malin (Jira)

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

In reply to this post by Nicolas Malin (Jira)

[ 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

Nicolas Malin (Jira)

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

In reply to this post by Nicolas Malin (Jira)

[ http://issues.apache.org/jira/browse/OFBIZ-193?page=comments#action_12433165 ]

Si Chen commented on OFBIZ-193:
-------------------------------

There are some subtle differences now that I think about it:

1. ProductAverageCost is by organizationPartyId. ProductPrice is by productStoreGroupId -- so there may not be a good mapping between the two there. Many product stores could all be part of the same organization.

2. Also, ProductAverageCost is based on ShipmentReceipts' inventory unitCosts. That may or may not be what someone would want pricing to be based on . . .

I think this is one of those features which should wait until someone who really needs, as the actual meaning of things would depend on requirements.

Nicolas Malin (Jira)

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

In reply to this post by Nicolas Malin (Jira)

[ 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.