| |
| 1 |
Use SolomonApplication |
| 2 |
|
| 3 |
-- Check history INTRAN with ItemSite table |
| 4 |
SELECT * |
| 5 |
FROM |
| 6 |
( |
| 7 |
SELECT InTran.CpnyID, InTran.SiteID, InTran.InvtID, |
| 8 |
SUM((CASE WHEN InTran.UnitMultDiv = 'M' OR InTran.UnitMultDiv = '' THEN isnull(InTran.Qty,0)*InTran.InvtMult*InTran.CnvFact |
| 9 |
ELSE isnull(InTran.Qty,0)*InTran.InvtMult/InTran.CnvFact END)) as ReportQty, |
| 10 |
its.QtyOnHand ItemSiteOnHand, |
| 11 |
its.QtyAvail ItemSiteAvail, |
| 12 |
its.StdCost, |
| 13 |
its.AvgCost, |
| 14 |
its.TotCost, |
| 15 |
l.QtyOnHand LocationOnHand |
| 16 |
|
| 17 |
FROM INTran |
| 18 |
LEFT OUTER JOIN ItemSite its ON InTran.InvtID = its.InvtID And its.SiteID = InTran.SiteID |
| 19 |
LEFT OUTER JOIN Location l ON its.InvtID = l.InvtID |
| 20 |
|
| 21 |
|
| 22 |
WHERE InTran.Rlsed = 1 |
| 23 |
AND InTran.SiteID <> 'KHONV' |
| 24 |
AND InTran.WhseLoc = 'MD' |
| 25 |
AND intran.TranType not in ('AB','CG','CT') |
| 26 |
AND InTran.TranDate <= '12/19/2009' |
| 27 |
|
| 28 |
GROUP BY InTran.CpnyID, InTran.SiteID, InTran.InvtID, |
| 29 |
its.QtyOnHand, its.QtyAvail,its.StdCost, |
| 30 |
its.AvgCost, |
| 31 |
its.TotCost, |
| 32 |
l.QtyOnHand |
| 33 |
) tmp |
| 34 |
Where ((ReportQty <> ItemSiteOnHand) OR (ItemSiteOnHand <> ItemSiteOnHand)) |
| 35 |
|
| 36 |
/* |
| 37 |
Update Location |
| 38 |
Set QtyOnHand = 15, |
| 39 |
QtyAvail = 15, |
| 40 |
QtyShipNotInv = 0 |
| 41 |
Where InvtID = '06SC05' |
| 42 |
|
| 43 |
Update itemSite |
| 44 |
Set QtyOnHand = 15, |
| 45 |
QtyAvail = 15, |
| 46 |
QtyShipNotInv = 0, |
| 47 |
AvgCost = StdCost, |
| 48 |
TotCost = QtyOnHand * StdCost |
| 49 |
Where InvtID = '06SC05' |
| 50 |
|
| 51 |
Update Location |
| 52 |
Set QtyShipNotInv = 0 |
| 53 |
Where QtyShipNotInv <> 0 |
| 54 |
|
| 55 |
|
| 56 |
Update ItemSite |
| 57 |
Set QtyShipNotInv = 0 |
| 58 |
Where QtyShipNotInv <> 0 |
| 59 |
*/ |
|
|
|
|
| |