Implementation of a service to compute the product average cost

[Jacopo Cappellato]

Hi all,

I have to implement 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?

However I think that it is important to give the ability to maintain a
separate cost for each facility (for example the items in a facility in
US and the ones in a facility in China could have very different
inventory costs); the key of the ProductAverageCost is now composed by
productId, organizationPartyId, fromDate so I think we should add to it
also the facilityId and possibly a productAverageCostTypeId field (to
maintain different types of averages).

I don't see any code that is using this entity right now but, if I well
remember, it is used by some code in the external Financial component...
so I guess that Si could have some good advices :-)

Jacopo

[Jacopo Cappellato]

No comments about this? Can I go on with the proposed changes?

Jacopo

Jacopo Cappellato wrote:

> Hi all,
>
> I have to implement 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?
>
> However I think that it is important to give the ability to maintain a
> separate cost for each facility (for example the items in a facility in
> US and the ones in a facility in China could have very different
> inventory costs); the key of the ProductAverageCost is now composed by
> productId, organizationPartyId, fromDate so I think we should add to it
> also the facilityId and possibly a productAverageCostTypeId field (to
> maintain different types of averages).
>
> I don't see any code that is using this entity right now but, if I well
> remember, it is used by some code in the external Financial component...
> so I guess that Si could have some good advices :-)
>
> Jacopo

[David E Jones-2]

Jacopo,

Yes, I think it's fine to update that entity. No one has responded  
with concerns and I agree it's likely that it is not being used much  
if at all.

It's possible Si missed these 2 messages and there actually a problem  
with how he is using it. If that is the case I'm sure we can work  
together to make something that works for everyone (ie this isn't a  
real dangerous change or anything).

-David


On Dec 16, 2006, at 3:57 AM, Jacopo Cappellato wrote:

> No comments about this? Can I go on with the proposed changes?
>
> Jacopo
>
> Jacopo Cappellato wrote:
>> Hi all,
>> I have to implement 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?
>> However I think that it is important to give the ability to  
>> maintain a separate cost for each facility (for example the items  
>> in a facility in US and the ones in a facility in China could have  
>> very different inventory costs); the key of the ProductAverageCost  
>> is now composed by productId, organizationPartyId, fromDate so I  
>> think we should add to it also the facilityId and possibly a  
>> productAverageCostTypeId field (to maintain different types of  
>> averages).
>> I don't see any code that is using this entity right now but, if I  
>> well remember, it is used by some code in the external Financial  
>> component... so I guess that Si could have some good advices :-)
>> Jacopo
>

[Jacopo Cappellato]

Hi all,

I'd like to implement the average cost calculation using a view entity
that implements the following sql statement:

select
owner_Party_Id, facility_Id, product_Id,
sum(quantity_on_hand_total) as TOTAL_QOH,
sum(quantity_on_hand_total * unit_cost) as TOTAL_VALUE,
(sum(quantity_on_hand_total * unit_cost) / sum(quantity_on_hand_total))
as AVERAGE_COST
from inventory_item
group by
owner_Party_Id, facility_Id, product_Id

I'm trying to use <complex-alias but I'm having some problems with the
following part:
(sum(quantity_on_hand_total * unit_cost) / sum(quantity_on_hand_total))

I've also tried to create more than one view entity (a view entity based
on another view entity etc...) but without success...
Before I go too far, do you think it is possible to implement something
like this using the view-entity element?

Thanks,

Jacopo


David E Jones wrote:

>
> Jacopo,
>
> Yes, I think it's fine to update that entity. No one has responded with
> concerns and I agree it's likely that it is not being used much if at all.
>
> It's possible Si missed these 2 messages and there actually a problem
> with how he is using it. If that is the case I'm sure we can work
> together to make something that works for everyone (ie this isn't a real
> dangerous change or anything).
>
> -David
>
>
> On Dec 16, 2006, at 3:57 AM, Jacopo Cappellato wrote:
>
>> No comments about this? Can I go on with the proposed changes?
>>
>> Jacopo
>>
>> Jacopo Cappellato wrote:
>>> Hi all,
>>> I have to implement 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?
>>> However I think that it is important to give the ability to maintain
>>> a separate cost for each facility (for example the items in a
>>> facility in US and the ones in a facility in China could have very
>>> different inventory costs); the key of the ProductAverageCost is now
>>> composed by productId, organizationPartyId, fromDate so I think we
>>> should add to it also the facilityId and possibly a
>>> productAverageCostTypeId field (to maintain different types of
>>> averages).
>>> I don't see any code that is using this entity right now but, if I
>>> well remember, it is used by some code in the external Financial
>>> component... so I guess that Si could have some good advices :-)
>>> Jacopo
>>

[David E Jones-2]

Jacopo,

This is a good question, and something that the entity engine isn't  
really all that well suited for.

Are you trying to calculate this for a single Product/Facility/
OwnerParty or a small group of them, or is it something that you  
intend to have run globally and possible calculate many many of them?

One thing about this sort of calculation is that it may change over  
time or involve other factors and such. In other words, it would be  
nice to have this logic implemented with the rest of the logic in the  
service tier on the application server. Of course, that wouldn't work  
so well if this is meant to calculate a large set of these, like  
hundreds of thousands for example (it could work, but it would just  
take a much longer time to execute).

The closest thing to this that exits in the entity engine is the  
complex-alias stuff that is part of a view entity, but that does not  
currently support a combination of functions and math operations, it  
just supports math operations for now. It is hierarchical however, so  
if it was extended we might be able to reasonably do this...

-David


On Dec 18, 2006, at 8:23 AM, Jacopo Cappellato wrote:

> Hi all,
>
> I'd like to implement the average cost calculation using a view  
> entity that implements the following sql statement:
>
> select
> owner_Party_Id, facility_Id, product_Id,
> sum(quantity_on_hand_total) as TOTAL_QOH,
> sum(quantity_on_hand_total * unit_cost) as TOTAL_VALUE,
> (sum(quantity_on_hand_total * unit_cost) / sum
> (quantity_on_hand_total)) as AVERAGE_COST
> from inventory_item
> group by
> owner_Party_Id, facility_Id, product_Id
>
> I'm trying to use <complex-alias but I'm having some problems with  
> the following part:
> (sum(quantity_on_hand_total * unit_cost) / sum
> (quantity_on_hand_total))
>
> I've also tried to create more than one view entity (a view entity  
> based on another view entity etc...) but without success...
> Before I go too far, do you think it is possible to implement  
> something like this using the view-entity element?
>
> Thanks,
>
> Jacopo
>
>
> David E Jones wrote:
>> Jacopo,
>> Yes, I think it's fine to update that entity. No one has responded  
>> with concerns and I agree it's likely that it is not being used  
>> much if at all.
>> It's possible Si missed these 2 messages and there actually a  
>> problem with how he is using it. If that is the case I'm sure we  
>> can work together to make something that works for everyone (ie  
>> this isn't a real dangerous change or anything).
>> -David
>> On Dec 16, 2006, at 3:57 AM, Jacopo Cappellato wrote:
>>> No comments about this? Can I go on with the proposed changes?
>>>
>>> Jacopo
>>>
>>> Jacopo Cappellato wrote:
>>>> Hi all,
>>>> I have to implement 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?
>>>> However I think that it is important to give the ability to  
>>>> maintain a separate cost for each facility (for example the  
>>>> items in a facility in US and the ones in a facility in China  
>>>> could have very different inventory costs); the key of the  
>>>> ProductAverageCost is now composed by productId,  
>>>> organizationPartyId, fromDate so I think we should add to it  
>>>> also the facilityId and possibly a productAverageCostTypeId  
>>>> field (to maintain different types of averages).
>>>> I don't see any code that is using this entity right now but, if  
>>>> I well remember, it is used by some code in the external  
>>>> Financial component... so I guess that Si could have some good  
>>>> advices :-)
>>>> Jacopo
>>>
>

[Jacopo Cappellato]

David,

the idea was to 'implement' that sql statement within a service (and a
view entity definition) and run it (probably as a seca) every time a
product is received/shipped/etc... (i.e. everytime the qoh of a product
changes); the result of the service call could be stored in the
ProductAverageCost entity.
So it will run many times a day but for just one
Product/Facility/OwnerParty at a time.
Now we have two choices I guess:
1) improve the complex-alias stuff to support the sql statement
2) implement it in a different way (e.g. in a Java based service)

Thanks for your help,

Jacopo

David E Jones wrote:

>
> Jacopo,
>
> This is a good question, and something that the entity engine isn't
> really all that well suited for.
>
> Are you trying to calculate this for a single
> Product/Facility/OwnerParty or a small group of them, or is it something
> that you intend to have run globally and possible calculate many many of
> them?
>
> One thing about this sort of calculation is that it may change over time
> or involve other factors and such. In other words, it would be nice to
> have this logic implemented with the rest of the logic in the service
> tier on the application server. Of course, that wouldn't work so well if
> this is meant to calculate a large set of these, like hundreds of
> thousands for example (it could work, but it would just take a much
> longer time to execute).
>
> The closest thing to this that exits in the entity engine is the
> complex-alias stuff that is part of a view entity, but that does not
> currently support a combination of functions and math operations, it
> just supports math operations for now. It is hierarchical however, so if
> it was extended we might be able to reasonably do this...
>
> -David
>

[David E Jones-2]

Jacopo,

In that case it shouldn't be a big deal performance-wise to do the  
calculation in a simple-method or Java service based on a database  
query, rather than putting the formula in the query itself.

-David


On Dec 19, 2006, at 10:51 PM, Jacopo Cappellato wrote:

> David,
>
> the idea was to 'implement' that sql statement within a service  
> (and a view entity definition) and run it (probably as a seca)  
> every time a product is received/shipped/etc... (i.e. everytime the  
> qoh of a product changes); the result of the service call could be  
> stored in the ProductAverageCost entity.
> So it will run many times a day but for just one Product/Facility/
> OwnerParty at a time.
> Now we have two choices I guess:
> 1) improve the complex-alias stuff to support the sql statement
> 2) implement it in a different way (e.g. in a Java based service)
>
> Thanks for your help,
>
> Jacopo
>
> David E Jones wrote:
>> Jacopo,
>> This is a good question, and something that the entity engine  
>> isn't really all that well suited for.
>> Are you trying to calculate this for a single Product/Facility/
>> OwnerParty or a small group of them, or is it something that you  
>> intend to have run globally and possible calculate many many of them?
>> One thing about this sort of calculation is that it may change  
>> over time or involve other factors and such. In other words, it  
>> would be nice to have this logic implemented with the rest of the  
>> logic in the service tier on the application server. Of course,  
>> that wouldn't work so well if this is meant to calculate a large  
>> set of these, like hundreds of thousands for example (it could  
>> work, but it would just take a much longer time to execute).
>> The closest thing to this that exits in the entity engine is the  
>> complex-alias stuff that is part of a view entity, but that does  
>> not currently support a combination of functions and math  
>> operations, it just supports math operations for now. It is  
>> hierarchical however, so if it was extended we might be able to  
>> reasonably do this...
>> -David
>

[Rashko Rejmer]

Hi Jacopo,

I have similar needs to yours. I want to create jira issue and then to
implement the service that you proposed, but I am not sure if I am not
missing something that has changed until now.

First I wanted to implement the service and to use it in a report - similar
to "Inventory Item Totals".
Then I intend to make suggested changes to the ProductAverageCost entity and
to persist the result of the metioned service there.

Any suggestions are velcome.

Regards,
Rashko Rejmer


Jacopo Cappellato wrote:

>
> Hi all,
>
> I have to implement 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?
>
> However I think that it is important to give the ability to maintain a
> separate cost for each facility (for example the items in a facility in
> US and the ones in a facility in China could have very different
> inventory costs); the key of the ProductAverageCost is now composed by
> productId, organizationPartyId, fromDate so I think we should add to it
> also the facilityId and possibly a productAverageCostTypeId field (to
> maintain different types of averages).
>
> I don't see any code that is using this entity right now but, if I well
> remember, it is used by some code in the external Financial component...
> so I guess that Si could have some good advices :-)
>
> Jacopo
>
>

--
View this message in context: http://www.nabble.com/Implementation-of-a-service-to-compute-the-product-average-cost-tp7891049p16490988.html
Sent from the OFBiz - Dev mailing list archive at Nabble.com.