· 6 years ago · Aug 13, 2019, 10:42 AM
1/*
2 Version 08.12
3 1) Fix can't execute scripts caused by table structure difference
4*/
5
6--using view to detect corruptions
7--using two cursors to get to base documents on detected document
8
9IF object_id('SAP_VIEW_INVQTY_RESULTS','u') is not null
10 drop table SAP_VIEW_INVQTY_RESULTS
11
12IF object_id('SAP_VIEW_INVQTY_RESULTS_BASE_1','u') is not null
13 drop table SAP_VIEW_INVQTY_RESULTS_BASE_1
14
15IF object_id('SAP_VIEW_INVQTY_RESULTS_BASE_2','u') is not null
16 drop table SAP_VIEW_INVQTY_RESULTS_BASE_2
17
18IF object_id('SAP_VIEW_OPENINVQTY_RESULTS','u') is not null
19 drop table SAP_VIEW_OPENINVQTY_RESULTS
20go
21
22Declare @version int;
23Declare @ver91PL02 int;
24Declare @ver90PL00 int;
25Declare @ver90PL06 int;
26Declare @sqlStr nvarchar(max)
27Declare @detectCount int
28SET @version = (SELECT Version FROM CINF)
29
30SET @ver91PL02 = 910120
31SET @ver90PL00 = 900000
32SET @ver90PL06 = 900058
33
34if(@version >= 900000)
35begin
36 if exists (select * from sysobjects where name = 'View_InvQty_All_Tables' and xtype = 'V')
37 begin
38 drop view View_InvQty_All_Tables
39 end
40
41 IF (@version >= @ver91PL02)
42 begin
43 set @sqlStr = 'CREATE VIEW dbo.View_InvQty_All_Tables
44 AS
45 Select ''CIN1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.CIN1 T0
46 inner join OCIN T1 on t0.docentry=t1.docentry
47 UNION ALL
48 Select ''CPI1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.CPI1 T0
49 inner join OCPI T1 on t0.docentry=t1.docentry
50 UNION ALL
51 Select ''CPV1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.CPV1 T0
52 inner join OCPV T1 on t0.docentry=t1.docentry
53 UNION ALL
54 Select ''CSI1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.CSI1 T0
55 inner join OCSI T1 on t0.docentry=t1.docentry
56 UNION ALL
57 Select ''CSV1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.CSV1 T0
58 inner join OCSV T1 on t0.docentry=t1.docentry
59 UNION ALL
60 Select ''DLN1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.DLN1 T0
61 inner join ODLN T1 on t0.docentry=t1.docentry
62 UNION ALL
63 Select ''DPI1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.DPI1 T0
64 inner join ODPI T1 on t0.docentry=t1.docentry
65 UNION ALL
66 Select ''DPO1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.DPO1 T0
67 inner join ODPO T1 on t0.docentry=t1.docentry
68 UNION ALL
69 Select ''DRF1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.DRF1 T0
70 inner join ODRF T1 on t0.docentry=t1.docentry
71 UNION ALL
72 Select ''IEI1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.IEI1 T0
73 inner join OIEI T1 on t0.docentry=t1.docentry
74 UNION ALL
75 Select ''IGE1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.IGE1 T0
76 inner join OIGE T1 on t0.docentry=t1.docentry
77 UNION ALL
78 Select ''IGN1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.IGN1 T0
79 inner join OIGN T1 on t0.docentry=t1.docentry
80 UNION ALL
81 Select ''INV1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.INV1 T0
82 inner join OINV T1 on t0.docentry=t1.docentry
83 UNION ALL
84 Select ''OEI1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.OEI1 T0
85 inner join OOEI T1 on t0.docentry=t1.docentry
86 UNION ALL
87 Select ''PCH1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.PCH1 T0
88 inner join OPCH T1 on t0.docentry=t1.docentry
89 UNION ALL
90 Select ''PDN1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.PDN1 T0
91 inner join OPDN T1 on t0.docentry=t1.docentry
92 UNION ALL
93 Select ''POR1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.POR1 T0
94 inner join OPOR T1 on t0.docentry=t1.docentry
95 UNION ALL
96 Select ''PQT1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.PQT1 T0
97 inner join OPQT T1 on t0.docentry=t1.docentry
98 UNION ALL
99 Select ''PRQ1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.PRQ1 T0
100 inner join OPRQ T1 on t0.docentry=t1.docentry
101 UNION ALL
102 Select ''QUT1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.QUT1 T0
103 inner join OQUT T1 on t0.docentry=t1.docentry
104 UNION ALL
105 Select ''RDN1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.RDN1 T0
106 inner join ORDN T1 on t0.docentry=t1.docentry
107 UNION ALL
108 Select ''RDR1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.RDR1 T0
109 inner join ORDR T1 on t0.docentry=t1.docentry
110 UNION ALL
111 Select ''RIN1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.RIN1 T0
112 inner join ORIN T1 on t0.docentry=t1.docentry
113 UNION ALL
114 Select ''RPC1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.RPC1 T0
115 inner join ORPC T1 on t0.docentry=t1.docentry
116 UNION ALL
117 Select ''RPD1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.RPD1 T0
118 inner join ORPD T1 on t0.docentry=t1.docentry
119 UNION ALL
120 Select ''WTQ1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.WTQ1 T0
121 inner join OWTQ T1 on t0.docentry=t1.docentry
122 UNION ALL
123 Select ''WTR1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.InvQtyOnly, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED, T0.ItemType from dbo.WTR1 T0
124 inner join OWTR T1 on t0.docentry=t1.docentry
125 UNION ALL
126 Select ''IPF1'' AS "Detect Table", T1.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, ''N'', T0.BaseType, T0.BaseEntry, T0.OrigLine, T0.Quantity, 0, T0.NumPerMsr, T0.InvQty, 0, 0, ''I'', T1.VersionNum, T0.ItemCode, ''N/A'', ''N/A'', T1.CANCELED, 0 from dbo.IPF1 T0
127 inner join OIPF T1 on t0.docentry=t1.docentry
128 '
129 exec(@sqlStr)
130
131 CREATE TABLE SAP_VIEW_INVQTY_RESULTS ("Detect Table" nvarchar(4), ObjType nvarchar(20), DocNum int, DocEntry int, LineNum int, InvQtyOnly char(1), BaseType int, BaseEntry int, BaseLine int, Quantity numeric(19, 6), OpenCreQty numeric(19, 6), NumperMsr numeric(19, 6), InvQty numeric(19, 6), OpenInvQty numeric(19, 6), BaseOpnQty numeric(19, 6), DocType char(1), VersionNum nvarchar(20), ItemCode nvarchar(50), TreeType nvarchar(3), "Drop-Ship" nvarchar(3), CANCELED varchar(1), ItemType int)
132
133 CREATE TABLE SAP_VIEW_INVQTY_RESULTS_BASE_1 ("Detect Table" nvarchar(4), ObjType nvarchar(20), DocNum int, DocEntry int, LineNum int, InvQtyOnly char(1), BaseType int, BaseEntry int, BaseLine int, Quantity numeric(19, 6), OpenCreQty numeric(19, 6), NumperMsr numeric(19, 6), InvQty numeric(19, 6), OpenInvQty numeric(19, 6), BaseOpnQty numeric(19, 6), DocType char(1), VersionNum nvarchar(20), ItemCode nvarchar(50), TreeType nvarchar(3), "Drop-Ship" nvarchar(3), CANCELED varchar(1), ItemType int, ChildID int)
134
135 CREATE TABLE SAP_VIEW_INVQTY_RESULTS_BASE_2 ("Detect Table" nvarchar(4), ObjType nvarchar(20), DocNum int, DocEntry int, LineNum int, InvQtyOnly char(1), BaseType int, BaseEntry int, BaseLine int, Quantity numeric(19, 6), OpenCreQty numeric(19, 6), NumperMsr numeric(19, 6), InvQty numeric(19, 6), OpenInvQty numeric(19, 6), BaseOpnQty numeric(19, 6), DocType char(1), VersionNum nvarchar(20), ItemCode nvarchar(50), TreeType nvarchar(3), "Drop-Ship" nvarchar(3), CANCELED varchar(1), ItemType int, ChildID int)
136
137 CREATE TABLE SAP_VIEW_OPENINVQTY_RESULTS ("Detect Table" nvarchar(4), ObjType nvarchar(20), DocNum int, DocEntry int, LineNum int, InvQtyOnly char(1), BaseType int, BaseEntry int, BaseLine int, Quantity numeric(19, 6), OpenCreQty numeric(19, 6), NumperMsr numeric(19, 6), InvQty numeric(19, 6), OpenInvQty numeric(19, 6), BaseOpnQty numeric(19, 6), DocType char(1), VersionNum nvarchar(20), ItemCode nvarchar(50), TreeType nvarchar(3), "Drop-Ship" nvarchar(3), CANCELED varchar(1), ItemType int)
138
139 end
140 else
141 begin
142 set @sqlStr = 'CREATE VIEW dbo.View_InvQty_All_Tables
143 AS
144 Select ''CIN1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.CIN1 T0
145 inner join OCIN T1 on t0.docentry=t1.docentry
146 UNION ALL
147 Select ''CPI1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.CPI1 T0
148 inner join OCPI T1 on t0.docentry=t1.docentry
149 UNION ALL
150 Select ''CPV1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.CPV1 T0
151 inner join OCPV T1 on t0.docentry=t1.docentry
152 UNION ALL
153 Select ''CSI1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.CSI1 T0
154 inner join OCSI T1 on t0.docentry=t1.docentry
155 UNION ALL
156 Select ''CSV1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.CSV1 T0
157 inner join OCSV T1 on t0.docentry=t1.docentry
158 UNION ALL
159 Select ''DLN1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.DLN1 T0
160 inner join ODLN T1 on t0.docentry=t1.docentry
161 UNION ALL
162 Select ''DPI1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.DPI1 T0
163 inner join ODPI T1 on t0.docentry=t1.docentry
164 UNION ALL
165 Select ''DPO1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.DPO1 T0
166 inner join ODPO T1 on t0.docentry=t1.docentry
167 UNION ALL
168 Select ''DRF1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.DRF1 T0
169 inner join ODRF T1 on t0.docentry=t1.docentry
170 UNION ALL
171 Select ''IEI1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.IEI1 T0
172 inner join OIEI T1 on t0.docentry=t1.docentry
173 UNION ALL
174 Select ''IGE1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.IGE1 T0
175 inner join OIGE T1 on t0.docentry=t1.docentry
176 UNION ALL
177 Select ''IGN1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.IGN1 T0
178 inner join OIGN T1 on t0.docentry=t1.docentry
179 UNION ALL
180 Select ''INV1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.INV1 T0
181 inner join OINV T1 on t0.docentry=t1.docentry
182 UNION ALL
183 Select ''OEI1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.OEI1 T0
184 inner join OOEI T1 on t0.docentry=t1.docentry
185 UNION ALL
186 Select ''PCH1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.PCH1 T0
187 inner join OPCH T1 on t0.docentry=t1.docentry
188 UNION ALL
189 Select ''PDN1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.PDN1 T0
190 inner join OPDN T1 on t0.docentry=t1.docentry
191 UNION ALL
192 Select ''POR1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.POR1 T0
193 inner join OPOR T1 on t0.docentry=t1.docentry
194 UNION ALL
195 Select ''PQT1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.PQT1 T0
196 inner join OPQT T1 on t0.docentry=t1.docentry
197 UNION ALL
198 Select ''PRQ1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.PRQ1 T0
199 inner join OPRQ T1 on t0.docentry=t1.docentry
200 UNION ALL
201 Select ''QUT1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.QUT1 T0
202 inner join OQUT T1 on t0.docentry=t1.docentry
203 UNION ALL
204 Select ''RDN1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.RDN1 T0
205 inner join ORDN T1 on t0.docentry=t1.docentry
206 UNION ALL
207 Select ''RDR1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.RDR1 T0
208 inner join ORDR T1 on t0.docentry=t1.docentry
209 UNION ALL
210 Select ''RIN1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.RIN1 T0
211 inner join ORIN T1 on t0.docentry=t1.docentry
212 UNION ALL
213 Select ''RPC1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.RPC1 T0
214 inner join ORPC T1 on t0.docentry=t1.docentry
215 UNION ALL
216 Select ''RPD1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.RPD1 T0
217 inner join ORPD T1 on t0.docentry=t1.docentry
218 UNION ALL
219 Select ''WTQ1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.WTQ1 T0
220 inner join OWTQ T1 on t0.docentry=t1.docentry
221 UNION ALL
222 Select ''WTR1'' AS "Detect Table", T0.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.Quantity, T0.OpenCreQty, T0.NumPerMsr, T0.InvQty, T0.OpenInvQty, T0.BaseOpnQty, T1.DocType, T1.VersionNum, T0.ItemCode, T0.TreeType, T0."DropShip" as "Drop-Ship", T1.CANCELED from dbo.WTR1 T0
223 inner join OWTR T1 on t0.docentry=t1.docentry
224 '
225 if (@version >= @ver90PL06)
226 begin
227 set @sqlStr = @sqlStr +
228 '
229 UNION ALL
230 Select ''IPF1'' AS "Detect Table", T1.ObjType, T1.DocNum, T0.DocEntry, T0.LineNum, T0.BaseType, T0.BaseEntry, T0.OrigLine, T0.Quantity, 0, T0.NumPerMsr, T0.InvQty, 0, 0, ''I'', T1.VersionNum, T0.ItemCode, ''N/A'', ''N/A'', T1.CANCELED from dbo.IPF1 T0
231 inner join OIPF T1 on t0.docentry=t1.docentry
232 '
233 end
234 exec(@sqlStr)
235
236 CREATE TABLE SAP_VIEW_INVQTY_RESULTS ("Detect Table" nvarchar(4), ObjType nvarchar(20), DocNum int, DocEntry int, LineNum int, BaseType int, BaseEntry int, BaseLine int, Quantity numeric(19, 6), OpenCreQty numeric(19, 6), NumperMsr numeric(19, 6), InvQty numeric(19, 6), OpenInvQty numeric(19, 6), BaseOpnQty numeric(19, 6), DocType char(1), VersionNum nvarchar(20), ItemCode nvarchar(50), TreeType nvarchar(3), "Drop-Ship" nvarchar(3), CANCELED varchar(1))
237
238 CREATE TABLE SAP_VIEW_INVQTY_RESULTS_BASE_1 ("Detect Table" nvarchar(4), ObjType nvarchar(20), DocNum int, DocEntry int, LineNum int, BaseType int, BaseEntry int, BaseLine int, Quantity numeric(19, 6), OpenCreQty numeric(19, 6), NumperMsr numeric(19, 6), InvQty numeric(19, 6), OpenInvQty numeric(19, 6), BaseOpnQty numeric(19, 6), DocType char(1), VersionNum nvarchar(20), ItemCode nvarchar(50), TreeType nvarchar(3), "Drop-Ship" nvarchar(3), CANCELED varchar(1), ChildID int)
239
240 CREATE TABLE SAP_VIEW_INVQTY_RESULTS_BASE_2 ("Detect Table" nvarchar(4), ObjType nvarchar(20), DocNum int, DocEntry int, LineNum int, BaseType int, BaseEntry int, BaseLine int, Quantity numeric(19, 6), OpenCreQty numeric(19, 6), NumperMsr numeric(19, 6), InvQty numeric(19, 6), OpenInvQty numeric(19, 6), BaseOpnQty numeric(19, 6), DocType char(1), VersionNum nvarchar(20), ItemCode nvarchar(50), TreeType nvarchar(3), "Drop-Ship" nvarchar(3), CANCELED varchar(1), ChildID int)
241
242 CREATE TABLE SAP_VIEW_OPENINVQTY_RESULTS ("Detect Table" nvarchar(4), ObjType nvarchar(20), DocNum int, DocEntry int, LineNum int, BaseType int, BaseEntry int, BaseLine int, Quantity numeric(19, 6), OpenCreQty numeric(19, 6), NumperMsr numeric(19, 6), InvQty numeric(19, 6), OpenInvQty numeric(19, 6), BaseOpnQty numeric(19, 6), DocType char(1), VersionNum nvarchar(20), ItemCode nvarchar(50), TreeType nvarchar(3), "Drop-Ship" nvarchar(3), CANCELED varchar(1))
243
244 end
245
246 declare @qtyDec int;
247 declare @BaseType int;
248 declare @BaseEntry nvarchar(20);
249 declare @BaseLine nvarchar(20);
250 declare @BaseType2 int;
251 declare @BaseEntry2 nvarchar(20);
252 declare @BaseLine2 nvarchar(20);
253 declare @ChildID int;
254
255 --ChildID is used to group together all linked documents
256 set @ChildID = 0;
257
258 SET @qtyDec = (SELECT qtydec
259 FROM oadm)
260
261 --In this step the original logic from InvQty detection is used to detect all suspect corrupt documents
262 IF (@version >= @ver91PL02)
263 BEGIN
264 set @sqlStr = '
265 Insert into SAP_VIEW_INVQTY_RESULTS
266 select *
267 from View_InvQty_All_Tables t1
268 WHERE Round(Round(isnull(t1.Quantity, 0) * t1.NumPerMsr, 6), $qtyDec) <> isnull(t1.InvQty , 0)
269 and isnull(t1.Quantity, 0) <> isnull(Round(Round(isnull(t1.InvQty , 0) / nullif(t1.NumPerMsr, 0), 6), $qtyDec), 0)
270 and t1.InvQtyOnly <> ''Y''
271 and t1.ItemType = 4
272 and t1.DocType = ''I''
273 '
274 END
275 ELSE
276 BEGIN
277 set @sqlStr = '
278 Insert into SAP_VIEW_INVQTY_RESULTS
279 select *
280 from View_InvQty_All_Tables t1
281 WHERE Round(Round(isnull(t1.Quantity, 0) * t1.NumPerMsr, 6), $qtyDec) <> isnull(t1.InvQty , 0)
282 and isnull(t1.Quantity, 0) <> isnull(Round(Round(isnull(t1.InvQty , 0) / nullif(t1.NumPerMsr, 0), 6), $qtyDec), 0)
283 and t1.DocType = ''I''
284 '
285 END
286 set @sqlStr = replace(@sqlStr, '$qtyDec', @qtyDec)
287 exec(@sqlStr)
288
289 --First step of cursor
290 --on each step ChildID is increased
291 --cursor on results of the corrupt documents based on the view
292
293 Declare viewCurTable CURSOR FAST_FORWARD for
294 select BaseType, BaseEntry, BaseLine from SAP_VIEW_INVQTY_RESULTS
295
296 OPEN viewCurTable
297 FETCH NEXT FROM viewCurTable INTO @BaseType, @BaseEntry, @BaseLine
298
299 WHILE @@FETCH_STATUS = 0
300 Begin
301
302 Insert into SAP_VIEW_INVQTY_RESULTS_BASE_1
303 Select View_InvQty_All_Tables.*, @ChildID
304 from View_InvQty_All_Tables
305 where
306 ObjType = @BaseType
307 and DocEntry = @BaseEntry
308 and LineNum = @BaseLine
309
310 --Second step
311 --using secondary cursor to get to source document
312
313 Declare viewCurTable2 CURSOR
314 DYNAMIC
315 for
316 select BaseType, BaseEntry, BaseLine
317 from SAP_VIEW_INVQTY_RESULTS_BASE_1
318
319 OPEN viewCurTable2
320 FETCH NEXT FROM viewCurTable2
321 INTO @BaseType2, @BaseEntry2, @BaseLine2
322
323
324 WHILE @@FETCH_STATUS = 0
325 Begin
326
327 Insert into SAP_VIEW_INVQTY_RESULTS_BASE_1
328 Select View_InvQty_All_Tables.*, @ChildID
329 from View_InvQty_All_Tables
330 where
331 ObjType = @BaseType2
332 and DocEntry = @BaseEntry2
333 and LineNum = @BaseLine2
334
335
336 FETCH NEXT FROM viewCurTable2
337 INTO @BaseType2, @BaseEntry2, @BaseLine2
338
339
340 end
341
342 --ending secondary cursom loop
343 --fetching next row from original results
344 FETCH NEXT FROM viewCurTable INTO @BaseType, @BaseEntry, @BaseLine
345
346 --purge SAP_VIEW_INVQTY_RESULTS_BASE_1 table as new cycle is started
347 --backup up SAP_VIEW_INVQTY_RESULTS_BASE_1 to SAP_VIEW_INVQTY_RESULTS_BASE_2
348 insert into SAP_VIEW_INVQTY_RESULTS_BASE_2
349 select *
350 from SAP_VIEW_INVQTY_RESULTS_BASE_1
351
352 --cleaning up temporary table 2
353 truncate table SAP_VIEW_INVQTY_RESULTS_BASE_1
354
355 --as new loop is started ChildID is increased to distinguish new document cluster
356 set @ChildID = @ChildID + 1
357
358 DEALLOCATE viewCurTable2
359 END
360
361 DEALLOCATE viewCurTable
362
363 --Cleaning up SAP_VIEW_INVQTY_RESULTS_BASE_2 table to get rid of those document clusters where on some document InvQtyOnly was enabled
364 --delete those results where some of the entry has InvQtyOnly enabled
365 if(@version >= @ver91PL02)
366 begin
367 set @sqlStr =
368 'delete from SAP_VIEW_INVQTY_RESULTS
369 from
370 SAP_VIEW_INVQTY_RESULTS T0
371 inner join SAP_VIEW_INVQTY_RESULTS_BASE_2 T1
372 on T0.BaseType = T1.ObjType and T0.BaseEntry = T1.DocEntry and T0.BaseLine = T1.LineNum
373 where ChildID in ( select ChildID
374 from SAP_VIEW_INVQTY_RESULTS_BASE_2
375 where InvQtyOnly = ''Y''
376 and T0.Quantity = Quantity
377 and T0.InvQty = InvQty)
378 '
379 exec(@sqlStr)
380 end
381 --delete lines which are the last copy but not full copy (or indirect last copy and full copy), and the base lines have been partially copied and have some rounding issue.
382 --(current line is last copy or base document is last copy)
383 --That means OpenInvQty in direct base is currect
384 -- a) Quantity in current line = BaseOpnQty in current line
385 -- b) and whose direct base should not be in result set
386 -- c) and the target lines of direct base should not be in result set
387 -- d) and InvQty - OpenInvQty (base line) = sum target InvQty of base line (this condition is neccesary, otherwise if InvQty in base document or other target documents have been fixed, current line may be detected as correct)
388 -- for Landed Costs, we don't need to cosider condition a) and c) (Landed Costs is always fully copied in one documents and only the original line, not splitted line may have InvQty error)
389 declare @deletedCount int
390 set @deletedCount = 1
391 while(@deletedCount > 0)
392 begin
393
394 delete from SAP_VIEW_INVQTY_RESULTS
395 from SAP_VIEW_INVQTY_RESULTS T0
396 where not exists (select 1 from SAP_VIEW_INVQTY_RESULTS T6,
397 (select * from View_InvQty_All_Tables T4
398 where T0.BaseType = T4.BaseType and T0.BaseEntry = T4.BaseEntry and T0.BaseLine = T4.BaseLine
399 and T4.CANCELED not in ('C', 'Y')
400 and (T0.BaseType <> 20
401 or T0.BaseType = 20 and T4."Detect Table" <> 'IPF1' and T4."Detect Table" <> 'WTR1' and T4."Detect Table" <> 'WTQ1')
402 and T4."Detect Table" <> 'DRF1') AS T5
403 where T5.ObjType = T6.ObjType and T5.DocEntry = T6.DocEntry and T5.LineNum = T6.LineNum
404 and (T0.ObjType <> T5.ObjType or T0.DocEntry <> T5.DocEntry or T0.LineNum <> T5.LineNum))
405 and not exists (select 1 from SAP_VIEW_INVQTY_RESULTS T3
406 where T0.BaseType = T3.ObjType and T0.BaseEntry = T3.DocEntry and T0.BaseLine = T3.LineNum
407 and T3."Detect Table" <> 'DRF1'
408 )
409 and exists (select 1 from View_InvQty_All_Tables T7
410 where T0.BaseType = T7.ObjType and T0.BaseEntry = T7.DocEntry and T0.BaseLine = T7.LineNum and T0.NumPerMsr = T7.NumPerMsr
411 and (T0.Quantity <> T7.Quantity or T0.Quantity = T7.Quantity and T0.InvQty = T7.InvQty)
412 and T7.InvQty - T7.OpenInvQty = (select isnull(sum(InvQty), 0) from View_InvQty_All_Tables T8 where T0.BaseType = T8.BaseType and T0.BaseEntry = T8.BaseEntry and T0.BaseLine = T8.BaseLine
413 and T8.CANCELED <> 'Y'
414 and (T0.BaseType <> 20
415 or T0.BaseType = 20 and T8."Detect Table" <> 'IPF1' and T8."Detect Table" <> 'WTR1' and T8."Detect Table" <> 'WTQ1')
416 and T8."Detect Table" <> 'DRF1'))
417 and (T0.BaseOpnQty = T0.Quantity or T0.CANCELED = 'C')
418 and T0.BaseType <> -1
419 and T0.InvQty <> 0
420 and T0.Quantity <> 0
421 and T0."Detect Table" not in ('IPF1', 'DRF1')
422 and (T0."Detect Table" not in ('PRQ1', 'POR1', 'PQT1')
423 or T0."Detect Table" in ('PRQ1', 'POR1', 'PQT1') and T0.BaseType not in (17, 23))
424
425 set @deletedCount = @@ROWCOUNT
426
427 delete from SAP_VIEW_INVQTY_RESULTS
428 from SAP_VIEW_INVQTY_RESULTS T0
429 where not exists (select 1 from SAP_VIEW_INVQTY_RESULTS T3
430 where T0.BaseType = T3.ObjType and T0.BaseEntry = T3.DocEntry and T0.BaseLine = T3.LineNum
431 )
432 and T0."Detect Table" = 'IPF1'
433 set @deletedCount = @deletedCount + @@ROWCOUNT
434 end
435
436 --GET final results
437 select count(1) AS "Total InvQty Detect Count" from SAP_VIEW_INVQTY_RESULTS
438 set @detectCount = (select count(1) from SAP_VIEW_INVQTY_RESULTS)
439 if (@detectCount > 0)
440 begin
441 select "Detect Table", count(1) as "Detect Count"from SAP_VIEW_INVQTY_RESULTS group by "Detect Table"
442 set @sqlStr = '
443 select T0."Detect Table", T0.VersionNum, T0.ObjType, T0.DocNum, T0.DocEntry, T0.LineNum, T0.ItemCode, T0.Quantity, T0.NumPerMsr, T0.InvQty,
444 '
445 if(@version >= @ver91PL02)
446 begin
447 set @sqlStr += 'T0.InvQtyOnly, '
448 end
449
450 set @sqlStr += '
451 T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.TreeType, T1.ManBtchNum as "Manage by BatcH", T1.ManSerNum as "Manage by Serial", T0."Drop-Ship", T1.InvntItem as "Is Inventory", T1.EvalSystem from SAP_VIEW_INVQTY_RESULTS T0
452 left join OITM T1 on T0.ItemCode = T1.ItemCode
453 order by T0."Detect Table", T0.ObjType, T0.DocEntry, T0.LineNum
454 '
455 exec(@sqlStr)
456 end
457
458 --OpenInvQty
459 IF (@version >= @ver91PL02)
460 BEGIN
461 set @sqlStr =
462 '
463 Insert into SAP_VIEW_OPENINVQTY_RESULTS
464 select *
465 from View_InvQty_All_Tables t1
466 WHERE Round(Round(isnull(t1.OpenCreQty, 0) * t1.NumPerMsr, 6), $qtyDec) <> isnull(t1.OpenInvQty , 0)
467 and isnull(t1.OpenCreQty, 0) <> isnull(Round(Round(isnull(t1.OpenInvQty , 0) / nullif(t1.NumPerMsr, 0), 6), $qtyDec), 0)
468 and t1.InvQtyOnly <> ''Y''
469 and t1.ItemType = 4
470 and t1.DocType = ''I''
471 '
472 END
473 ELSE
474 BEGIN
475 set @sqlStr =
476 '
477 Insert into SAP_VIEW_OPENINVQTY_RESULTS
478 select *
479 from View_InvQty_All_Tables t1
480 WHERE Round(Round(isnull(t1.OpenCreQty, 0) * t1.NumPerMsr, 6), $qtyDec) <> isnull(t1.OpenInvQty , 0)
481 and isnull(t1.OpenCreQty, 0) <> isnull(Round(Round(isnull(t1.OpenInvQty , 0) / nullif(t1.NumPerMsr, 0), 6), $qtyDec), 0)
482 and t1.DocType = ''I''
483 '
484 END
485
486 set @sqlStr = replace(@sqlStr, '$qtyDec', @qtyDec)
487 exec(@sqlStr)
488
489 --select * from SAP_VIEW_OPENINVQTY_RESULTS
490
491 --delete lines which have following condition
492 -- a) InvQty in current line is correct
493 -- b) InvQty in target lines of current line are correct
494 -- c) and InvQty - OpenInvQty (current line) = sum target InvQty of current line (this condition is neccesary, otherwise if InvQty in current document or other target documents have been fixed, current line may be detected as correct)
495 --delete until no lines can be deleted
496 set @deletedCount = 1
497 while(@deletedCount > 0)
498 begin
499 delete from SAP_VIEW_OPENINVQTY_RESULTS
500 from SAP_VIEW_OPENINVQTY_RESULTS T0
501 where not exists (select 1 from SAP_VIEW_INVQTY_RESULTS T1
502 where T0.ObjType = T1.ObjType and T0.DocEntry = T1.DocEntry and T0.LineNum = T1.LineNum)
503 and not exists (select 1 from SAP_VIEW_INVQTY_RESULTS T2,
504 (select * from View_InvQty_All_Tables T3
505 where T0.ObjType = T3.BaseType and T0.DocEntry = T3.BaseEntry and T0.LineNum = T3.BaseLine
506 and T3.CANCELED not in ('C', 'Y')) T4
507 where T2.ObjType = T4.ObjType and T2.DocEntry = T4.DocEntry and T2.LineNum = T4.LineNum)
508 and T0.InvQty - T0.OpenInvQty = (select isnull(sum(InvQty), 0) from View_InvQty_All_Tables T8 where T0.ObjType = T8.BaseType and T0.DocEntry = T8.BaseEntry and T0.LineNum = T8.BaseLine
509 and T8.CANCELED <> 'Y'
510 and (T0.ObjType <> 20
511 or T0.ObjType = 20 and T8."Detect Table" <> 'IPF1' and T8."Detect Table" <> 'WTR1' and T8."Detect Table" <> 'WTQ1')
512 and T8."Detect Table" <> 'DRF1')
513 and T0.OpenCreQty <> 0 and T0.OpenInvQty <> 0
514 and T0."Detect Table" not in ('IPF1', 'DRF1')
515 set @deletedCount = @@ROWCOUNT
516 end
517
518 select count(1) AS "Total OpenInvQty Detect Count" from SAP_VIEW_OPENINVQTY_RESULTS
519 set @detectCount = (select count(1) from SAP_VIEW_OPENINVQTY_RESULTS)
520 if (@detectCount > 0)
521 begin
522 select "Detect Table", count(1) as "Detect Count" from SAP_VIEW_OPENINVQTY_RESULTS group by "Detect Table"
523
524 set @sqlStr = '
525 select T0."Detect Table", T0.VersionNum, T0.ObjType, T0.DocNum, T0.DocEntry, T0.LineNum, T0.ItemCode, T0.OpenCreQty, T0.NumPerMsr, T0.OpenInvQty,
526 '
527 if(@version >= @ver91PL02)
528 begin
529 set @sqlStr += 'T0.InvQtyOnly, '
530 end
531
532 set @sqlStr += '
533 T0.BaseType, T0.BaseEntry, T0.BaseLine, T0.TreeType, T1.ManBtchNum as "Manage by BatcH", T1.ManSerNum as "Manage by Serial", T0."Drop-Ship", T1.InvntItem as "Is Inventory", T1.EvalSystem from SAP_VIEW_OPENINVQTY_RESULTS T0
534 left join OITM T1 on T0.ItemCode = T1.ItemCode
535 order by T0."Detect Table", T0.ObjType, T0.DocEntry, T0.LineNum
536 '
537 exec(@sqlStr)
538 end
539
540 IF object_id('SAP_VIEW_INVQTY_RESULTS','u') is not null
541 drop table SAP_VIEW_INVQTY_RESULTS
542 IF object_id('SAP_VIEW_INVQTY_RESULTS_BASE_1','u') is not null
543 drop table SAP_VIEW_INVQTY_RESULTS_BASE_1
544 IF object_id('SAP_VIEW_INVQTY_RESULTS_BASE_2','u') is not null
545 drop table SAP_VIEW_INVQTY_RESULTS_BASE_2
546 IF object_id('SAP_VIEW_OPENINVQTY_RESULTS','u') is not null
547 drop table SAP_VIEW_OPENINVQTY_RESULTS
548
549 if exists (select * from sysobjects where name = 'View_InvQty_All_Tables' and xtype = 'V')
550 begin
551 drop view View_InvQty_All_Tables
552 end
553end