| Description |
1. Imported multiple count csv files.
2. Avg cost calculation is wrong when item have multiple lots
!pastedImage_d73798_0.png!
3. Avg should be 54.0875
!pastedImage_d73798_1.png!
Use greenbush 02 backup for testing. csv files attached.
As a work around, I recomputed the avg cost. Not shure if this is good enough or ot will create problems when transacations are added.
update P set dblAverageCost = C.AvgCost from
tblICItemPricing P
join
(select I.intItemId, I.strItemNo, I.strDescription, S.intItemLocationId, S.dblUnitOnHand, P.dblAverageCost, T.Value, T.AvgCost from tblICItem I
join tblICItemStock S on I.intItemId = S.intItemId
join tblICItemPricing P on S.intItemId = P.intItemId and S.intItemLocationId = P.intItemLocationId
cross apply
(select intItemId, intItemLocationId, sum(dblQty*dblCost) Value, sum(dblQty*dblCost)/sum(dblQty) AvgCost
from tblICInventoryTransaction
group by intItemId, intItemLocationId) T
where S.intItemId = T.intItemId and S.intItemLocationId = T.intItemLocationId
and T.AvgCost <> P.dblAverageCost) C
on C.intItemId = P.intItemId and C.intItemLocationId = P.intItemLocationId
|