Topic Text   Topic Comments (0)   Topic Properties   Topic Information liemnm@yah...
Topic title: 03.Check_Stock_among_F1Report_and_ItemSiteTable Tuesday May 18, 2010 08:56:50

Download topic text | View in variable-width font | Tab width set to 4 (change to 8)

Files in topic: (view all files)  
03.Check_Stock_among_F1Report_and_ItemSiteTable.txt   {+61,-0}

[Add General Comment] to topic.

File 03.Check_Stock_among_F1Report_and_ItemSiteTable.txt (Revision 1.0) [Add File Comment] [Top]
 
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 */
 
File 03.Check_Stock_among_F1Report_and_ItemSiteTable.txt (Revision 1.0) [Add File Comment] [Top]
  
Legend:
Removed 
Changed
 Added

[Add General Comment] to topic.