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
