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
