Description |
SELECT LotDT.*
,ItemDT.dblOnHand AS dblOnHandByItem
FROM (
SELECT intItemId
,intItemUOMId
,intSubLocationId
,intStorageLocationId
,SUM(dblQty) dblOnHandByLot
FROM tblICLot
GROUP BY intItemId
,intItemUOMId
,intSubLocationId
,intStorageLocationId
UNION
SELECT intItemId
,intWeightUOMId
,intSubLocationId
,intStorageLocationId
,SUM(dblWeight)
FROM tblICLot
WHERE intWeightUOMId IS NOT NULL
GROUP BY intItemId
,intWeightUOMId
,intSubLocationId
,intStorageLocationId
) AS LotDT
LEFT JOIN tblICItemStockUOM ItemDT ON LotDT.intItemId = ItemDT.intItemId
AND LotDT.intItemUOMId = ItemDT.intItemUOMId
AND LotDT.intSubLocationId = ItemDT.intSubLocationId
AND LotDT.intStorageLocationId = ItemDT.intStorageLocationId
WHERE LotDT.dblOnHandByLot > ItemDT.dblOnHand
!pastedImage_d81949_0.png!
|