· 6 years ago · Sep 05, 2019, 08:41 AM
1USE [MCMDMS]
2GO
3/****** Object: StoredProcedure [dbo].[sp_DMS_RPT_ASOGroupByDistributor] Script Date: 9/5/2019 3:34:15 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9-- EXEC [dbo].[sp_DMS_RPT_ASOGroupByDistributor] 3,null,'07','2017','sysadmin',3,''
10ALTER PROCEDURE [dbo].[sp_DMS_RPT_ASOGroupByDistributor]
11 @CompanyID INT ,
12 @DistributorID INT ,
13 @Month NVARCHAR(50) ,
14 @Year NVARCHAR(50) ,
15 @LoginID NVARCHAR(50) ,
16 @Type INT ,
17 @SalesAreaID INT
18AS
19 BEGIN
20 SET NOCOUNT ON;
21 SET FMTONLY OFF;
22 SET NO_BROWSETABLE OFF;
23
24 DECLARE @_CompanyID INT = NULLIF(@CompanyID, 0);
25 DECLARE @_Type INT = NULLIF(@Type, -1);
26 DECLARE @_DistributorID INT = NULLIF(@DistributorID, 0);
27 DECLARE @_LoginID VARCHAR(50) = @LoginID;
28 DECLARE @_TerritoryType CHAR(1);
29 DECLARE @_SalesAreaID INT = NULLIF(@SalesAreaID, 0);
30 DECLARE @_FirstDayOfMonth DATETIME = @Year + '/' + @Month + '/01';
31 DECLARE @_LastDayOfMonth DATETIME = DATEADD(dd, -1,
32 DATEADD(mm, 1,
33 @_FirstDayOfMonth));
34
35
36 CREATE TABLE #tmp
37 (
38 CompanyID INT ,
39 DistributorID INT ,
40 DistributorLocationID INT ,
41 SalesOrgID INT ,
42 SalesOrg_0_ValueID INT ,
43 SalesOrg_0_ValueCD VARCHAR(15) ,
44 SalesOrg_0_ValueName NVARCHAR(50) ,
45 SalesForce_0_Name NVARCHAR(50) ,
46 SalesOrg_1_ValueID INT ,
47 SalesOrg_1_ValueCD VARCHAR(15) ,
48 SalesOrg_1_ValueName NVARCHAR(50) ,
49 SalesForce_1_Name NVARCHAR(50) ,
50 SalesOrg_2_ValueID INT ,
51 SalesOrg_2_ValueCD VARCHAR(15) ,
52 SalesOrg_2_ValueName NVARCHAR(50) ,
53 SalesForce_2_Name NVARCHAR(50) ,
54 SalesOrg_3_ValueID INT ,
55 SalesOrg_3_ValueCD VARCHAR(15) ,
56 SalesOrg_3_ValueName NVARCHAR(50) ,
57 SalesForce_3_Name NVARCHAR(50) ,
58 SalesOrg_4_ValueID INT ,
59 SalesOrg_4_ValueCD VARCHAR(15) ,
60 SalesOrg_4_ValueName NVARCHAR(50) ,
61 SalesForce_4_Name NVARCHAR(50) ,
62 SalesOrg_5_ValueID INT ,
63 SalesOrg_5_ValueCD VARCHAR(15) ,
64 SalesOrg_5_ValueName NVARCHAR(50) ,
65 SalesForce_5_Name NVARCHAR(50) ,
66 SalesOrg_6_ValueID INT ,
67 SalesOrg_6_ValueCD VARCHAR(15) ,
68 SalesOrg_6_ValueName NVARCHAR(50) ,
69 SalesForce_6_Name NVARCHAR(50) ,
70 SalesOrg_7_ValueID INT ,
71 SalesOrg_7_ValueCD VARCHAR(15) ,
72 SalesOrg_7_ValueName NVARCHAR(50) ,
73 SalesForce_7_Name NVARCHAR(50) ,
74 SalesOrg_8_ValueID INT ,
75 SalesOrg_8_ValueCD VARCHAR(15) ,
76 SalesOrg_8_ValueName NVARCHAR(50) ,
77 SalesForce_8_Name NVARCHAR(50) ,
78 SalesOrg_9_ValueID INT ,
79 SalesOrg_9_ValueCD VARCHAR(15) ,
80 SalesOrg_9_ValueName NVARCHAR(50) ,
81 SalesForce_9_Name NVARCHAR(50) ,
82 ParentID INT ,
83 ParentLevel INT
84 );
85
86 ---- Lấy thông tin permission theo user đăng nhập
87 SELECT *
88 INTO #tmpPermission
89 FROM dbo.fn_DMS_GetSFPermission(@_CompanyID, @_DistributorID,
90 @_LoginID);
91
92 ---- Group by các dữ liệu permission
93 SELECT DistributorID ,
94 DistributorLocationID
95 INTO #tmpPrmDistributor
96 FROM #tmpPermission
97 GROUP BY DistributorID ,
98 DistributorLocationID;
99
100 -- Lấy thông tin NPP với cây sales org tương ứng
101 INSERT INTO #tmp
102 EXEC dbo.sp_DMS_MDM_GetDistributorWithSalesOrg @_CompanyID;
103
104 DELETE #tmp
105 FROM #tmp
106 WHERE ( #tmp.SalesOrg_0_ValueID IS NULL
107 OR #tmp.SalesOrg_0_ValueID != @_SalesAreaID
108 )
109 AND ( #tmp.SalesOrg_1_ValueID IS NULL
110 OR #tmp.SalesOrg_1_ValueID != @_SalesAreaID
111 )
112 AND ( #tmp.SalesOrg_2_ValueID IS NULL
113 OR #tmp.SalesOrg_2_ValueID != @_SalesAreaID
114 )
115 AND ( #tmp.SalesOrg_3_ValueID IS NULL
116 OR #tmp.SalesOrg_3_ValueID != @_SalesAreaID
117 )
118 AND ( #tmp.SalesOrg_4_ValueID IS NULL
119 OR #tmp.SalesOrg_4_ValueID != @_SalesAreaID
120 )
121 AND ( #tmp.SalesOrg_5_ValueID IS NULL
122 OR #tmp.SalesOrg_5_ValueID != @_SalesAreaID
123 )
124 AND ( #tmp.SalesOrg_6_ValueID IS NULL
125 OR #tmp.SalesOrg_6_ValueID != @_SalesAreaID
126 )
127 AND ( #tmp.SalesOrg_7_ValueID IS NULL
128 OR #tmp.SalesOrg_7_ValueID != @_SalesAreaID
129 )
130 AND ( #tmp.SalesOrg_8_ValueID IS NULL
131 OR #tmp.SalesOrg_8_ValueID != @_SalesAreaID
132 )
133 AND ( #tmp.SalesOrg_9_ValueID IS NULL
134 OR #tmp.SalesOrg_9_ValueID != @_SalesAreaID
135 );
136
137 -- Lấy thông tin tất cả MCP trong thời gian xem báo cáo
138 SELECT DISTINCT
139 mcpde.CompanyID ,
140 mcpde.DistributorID ,
141 mcpde.DistributorLocationID ,
142 mcpde.CustomerID ,
143 mcpde.CustomerLocationID
144 INTO #tempMCP
145 FROM dbo.DMSMCPDetail mcpde
146 JOIN dbo.DMSRouteSetting rs ON rs.CompanyID = mcpde.CompanyID
147 AND rs.RefNbr = mcpde.RefNbr
148 WHERE mcpde.CompanyID = @_CompanyID
149 AND rs.EffectiveDate <= @_LastDayOfMonth
150 AND @_FirstDayOfMonth <= ISNULL(rs.EndDate, GETDATE())
151 AND mcpde.EffectiveDate <= @_LastDayOfMonth
152 AND @_FirstDayOfMonth <= ISNULL(mcpde.EndDate, GETDATE())
153 AND rs.Status <> 'N'
154 AND ( @_DistributorID IS NULL
155 OR ( mcpde.DistributorID = @_DistributorID )
156 );
157 -- Lấy ra tổng số Outlet trong thời gian xem báo cáo
158 SELECT CompanyID ,
159 DistributorID ,
160 DistributorLocationID ,
161 COUNT(DISTINCT #tempMCP.CustomerLocationID) TotalOutlet
162 INTO #tempTotalOutlet
163 FROM #tempMCP
164 GROUP BY CompanyID ,
165 DistributorID ,
166 DistributorLocationID;
167
168-- Lấy ra các line hàng đã hoàn thành trong thời gian báo cáo
169 SELECT DISTINCT
170 so.CompanyID ,
171 DistributorID = so.BranchID ,
172 DistributorLocationID = NULL ,
173 so.CustomerID ,
174 so.CustomerLocationID ,
175 so.OrderDate ,
176 soline.InventoryID ,
177 hrchy.Hierachy0 ,
178 hrchy.Hierachy1 ,
179 hrchy.Hierachy2 ,
180 hrchy.Hierachy3 ,
181 hrchy.Hierachy4 ,
182 hrchy.Hierachy5 ,
183 hrchy.Hierachy6 ,
184 hrchy.Hierachy7 ,
185 hrchy.Hierachy8 ,
186 hrchy.Hierachy9
187 INTO #tempSOLine
188 FROM dbo.SOOrder so
189 JOIN dbo.SOLine soline ON soline.CompanyID = so.CompanyID
190 AND soline.OrderNbr = so.OrderNbr
191 AND soline.OrderType = so.OrderType
192 JOIN dbo.DMSEntityAttribute ent ON ent.CompanyID = soline.CompanyID
193 AND ent.EntityID = soline.InventoryID
194 JOIN dbo.DMSProductHierarchy hrchy ON hrchy.CompanyID = ent.CompanyID
195 AND hrchy.HierarchyID = ent.HierachyID
196 JOIN dbo.SOOrderShipment ordership ON ordership.CompanyID = so.CompanyID
197 AND ordership.OrderNbr = so.OrderNbr
198 AND ordership.OrderType = so.OrderType
199 JOIN dbo.INRegister inr ON inr.CompanyID = ordership.CompanyID
200 AND inr.RefNbr = ordership.InvtRefNbr
201 AND inr.DocType = ordership.InvtDocType
202 AND inr.Released = 1
203 WHERE so.CompanyID = @_CompanyID
204 AND so.OrderDate BETWEEN @_FirstDayOfMonth
205 AND @_LastDayOfMonth
206 AND so.OrderType <> 'CM'
207 AND ISNULL(so.UsrIsDispose, 0) <> 1
208 AND soline.IsFree = 0
209 AND ( @_DistributorID IS NULL
210 OR ( soline.BranchID = @_DistributorID )
211 );
212
213 -- Update lại các giá trị DistributorLocationID
214 UPDATE ao
215 SET ao.DistributorLocationID = vsor.DistributorLocationID
216 FROM #tempSOLine ao
217 JOIN dbo.DMSViewSORoute vsor WITH ( NOLOCK ) ON vsor.CompanyID = ao.CompanyID
218 AND vsor.CustomerID = ao.CustomerID
219 AND vsor.CustomerLocationID = ao.CustomerLocationID
220 AND ao.OrderDate BETWEEN vsor.EffectiveDate
221 AND
222 vsor.EndDate;
223
224 DELETE #tempSOLine
225 FROM #tempSOLine
226 LEFT JOIN #tmp ON #tmp.CompanyID = #tempSOLine.CompanyID
227 AND #tmp.DistributorID = #tempSOLine.DistributorID
228 AND #tmp.DistributorLocationID = #tempSOLine.DistributorLocationID
229 WHERE #tmp.CompanyID IS NULL;
230
231 -- Lấy ra các SO của theo các nhà phân phối mà user login đó được xem
232 SELECT *
233 INTO #tempSOLineWithLogin
234 FROM #tempSOLine sol
235 WHERE EXISTS ( SELECT TOP 1
236 *
237 FROM #tmpPrmDistributor pd
238 WHERE pd.DistributorID = sol.DistributorID
239 AND pd.DistributorLocationID = sol.DistributorLocationID );
240 CREATE TABLE #tempSoWithHierachy
241 (
242 CompanyID INT NULL ,
243 AttributeID INT NULL ,
244 BranchID INT NULL ,
245 DistributorLocationID INT NULL ,
246 CustomerID INT NULL ,
247 CustomerLocationID INT NULL ,
248 InventoryID INT NULL ,
249 Hierachy0 INT NULL ,
250 Hierachy1 INT NULL ,
251 Hierachy2 INT NULL ,
252 Hierachy3 INT NULL ,
253 Hierachy4 INT NULL ,
254 Hierachy5 INT NULL ,
255 Hierachy6 INT NULL ,
256 Hierachy7 INT NULL ,
257 Hierachy8 INT NULL ,
258 Hierachy9 INT NULL
259 );
260 IF @_Type <> -1
261 INSERT INTO #tempSoWithHierachy
262 SELECT DISTINCT
263 soline.CompanyID ,
264 att.AttributeID ,
265 soline.DistributorID ,
266 soline.DistributorLocationID ,
267 soline.CustomerID ,
268 soline.CustomerLocationID ,
269 soline.InventoryID ,
270 soline.Hierachy0 ,
271 soline.Hierachy1 ,
272 soline.Hierachy2 ,
273 soline.Hierachy3 ,
274 soline.Hierachy4 ,
275 soline.Hierachy5 ,
276 soline.Hierachy6 ,
277 soline.Hierachy7 ,
278 soline.Hierachy8 ,
279 soline.Hierachy9
280 FROM #tempSOLineWithLogin soline
281 JOIN DMSAttribute att ON att.CompanyID = soline.CompanyID
282 AND ( att.AttributeID = soline.Hierachy0
283 OR att.AttributeID = soline.Hierachy0
284 OR att.AttributeID = soline.Hierachy1
285 OR att.AttributeID = soline.Hierachy2
286 OR att.AttributeID = soline.Hierachy3
287 OR att.AttributeID = soline.Hierachy4
288 )
289 WHERE soline.CompanyID = @_CompanyID
290 AND att.Type = 'IN'
291 AND att.AttributeNbr = @_Type;
292 ELSE
293 INSERT INTO #tempSoWithHierachy
294 SELECT DISTINCT
295 soline.CompanyID ,
296 soline.InventoryID ,
297 soline.DistributorID ,
298 soline.DistributorLocationID ,
299 soline.CustomerID ,
300 soline.CustomerLocationID ,
301 soline.InventoryID ,
302 soline.Hierachy0 ,
303 soline.Hierachy1 ,
304 soline.Hierachy2 ,
305 soline.Hierachy3 ,
306 soline.Hierachy4 ,
307 soline.Hierachy5 ,
308 soline.Hierachy6 ,
309 soline.Hierachy7 ,
310 soline.Hierachy8 ,
311 soline.Hierachy9
312 FROM #tempSOLineWithLogin soline
313 WHERE soline.CompanyID = @_CompanyID;
314 SELECT #tempMCP.CompanyID ,
315 #tempMCP.DistributorID ,
316 #tempMCP.DistributorLocationID ,
317 hie.AttributeID ,
318 ASO = COUNT(DISTINCT #tempMCP.CustomerLocationID)
319 INTO #tempASO
320 FROM #tempMCP
321 JOIN #tempSoWithHierachy hie ON hie.CompanyID = #tempMCP.CompanyID
322 AND hie.BranchID = #tempMCP.DistributorID
323 AND hie.DistributorLocationID = #tempMCP.DistributorLocationID
324 AND hie.CustomerID = #tempMCP.CustomerID
325 GROUP BY #tempMCP.CompanyID ,
326 #tempMCP.DistributorID ,
327 #tempMCP.DistributorLocationID ,
328 hie.AttributeID;
329
330 SELECT #tempMCP.CompanyID ,
331 #tempMCP.DistributorID ,
332 #tempMCP.DistributorLocationID ,
333 COUNT(DISTINCT hie.CustomerID) TotalASO
334 INTO #tempTotalASO
335 FROM #tempMCP
336 JOIN #tempSoWithHierachy hie ON hie.CompanyID = #tempMCP.CompanyID
337 AND hie.BranchID = #tempMCP.DistributorID
338 AND hie.DistributorLocationID = #tempMCP.DistributorLocationID
339 AND hie.CustomerID = #tempMCP.CustomerID
340 GROUP BY #tempMCP.CompanyID ,
341 #tempMCP.DistributorID ,
342 #tempMCP.DistributorLocationID;
343 CREATE TABLE #tempResource
344 (
345 CompanyID INT NOT NULL ,
346 AttributeID INT NOT NULL ,
347 AttributeCD VARCHAR(15) NOT NULL ,
348 Descr NVARCHAR(50) NULL
349 );
350 IF @_Type <> -1
351 INSERT INTO #tempResource
352 SELECT CompanyID ,
353 AttributeID ,
354 AttributeCD ,
355 Descr
356 FROM DMSAttribute
357 WHERE CompanyID = @_CompanyID
358 AND Type = 'IN'
359 AND AttributeNbr = @_Type;
360 ELSE
361 INSERT INTO #tempResource
362 SELECT CompanyID ,
363 InventoryID ,
364 InventoryCD ,
365 Descr
366 FROM dbo.InventoryItem
367 WHERE CompanyID = @_CompanyID;
368
369 SELECT DISTINCT
370 #tempASO.CompanyID ,
371 #tempASO.DistributorID ,
372 #tempASO.DistributorLocationID ,
373 #tempTotalOutlet.TotalOutlet ,
374 #tempTotalASO.TotalASO ,
375 att.AttributeID ,
376 att.AttributeCD ,
377 att.Descr
378 INTO #tempFullMCP
379 FROM #tempASO
380 LEFT JOIN #tempTotalOutlet ON #tempASO.CompanyID = #tempTotalOutlet.CompanyID
381 AND #tempASO.DistributorID = #tempTotalOutlet.DistributorID
382 AND #tempASO.DistributorLocationID = #tempTotalOutlet.DistributorLocationID
383 LEFT JOIN #tempTotalASO ON #tempASO.CompanyID = #tempTotalASO.CompanyID
384 AND #tempASO.DistributorID = #tempTotalASO.DistributorID
385 AND #tempASO.DistributorLocationID = #tempTotalASO.DistributorLocationID
386 CROSS JOIN #tempResource att;
387 -- Lấy dữ liệu cuối cùng để xuất ra báo cáo
388 SELECT #tempFullMCP.CompanyID ,
389 #tempFullMCP.DistributorID ,
390 vdis.DistributorCD ,
391 vdis.DistributorName ,
392 #tmp.SalesOrgID ,
393 #tmp.SalesOrg_0_ValueName ,
394 #tmp.SalesOrg_1_ValueName ,
395 #tmp.SalesOrg_2_ValueName ,
396 #tmp.SalesOrg_3_ValueName ,
397 #tmp.SalesOrg_4_ValueName ,
398 #tmp.SalesOrg_5_ValueName ,
399 #tmp.SalesOrg_6_ValueName ,
400 #tmp.SalesOrg_7_ValueName ,
401 #tmp.SalesOrg_8_ValueName ,
402 #tmp.SalesOrg_9_ValueName ,
403 ASO = ISNULL(#tempASO.ASO, 0) ,
404 ASOItem = #tempFullMCP.AttributeCD ,
405 #tempFullMCP.Descr
406 FROM #tempFullMCP
407 LEFT JOIN #tempASO ON #tempFullMCP.CompanyID = #tempASO.CompanyID
408 AND #tempFullMCP.AttributeID = #tempASO.AttributeID
409 AND #tempASO.DistributorLocationID = #tempFullMCP.DistributorLocationID
410 LEFT JOIN dbo.DMSViewDistributorLocation vdis ON vdis.CompanyID = #tempFullMCP.CompanyID
411 AND vdis.DistributorID = #tempFullMCP.DistributorID
412 AND vdis.LocationID = #tempFullMCP.DistributorLocationID
413 LEFT JOIN #tmp ON #tmp.CompanyID = #tempFullMCP.CompanyID
414 AND #tmp.DistributorID = #tempFullMCP.DistributorID
415 AND #tmp.DistributorLocationID = #tempFullMCP.DistributorLocationID
416 -- Lấy dữ liệu của Total Outlet
417 UNION ALL
418 SELECT DISTINCT
419 #tempFullMCP.CompanyID ,
420 #tempFullMCP.DistributorID ,
421 vdis.DistributorCD ,
422 vdis.DistributorName ,
423 #tmp.SalesOrgID ,
424 #tmp.SalesOrg_0_ValueName ,
425 #tmp.SalesOrg_1_ValueName ,
426 #tmp.SalesOrg_2_ValueName ,
427 #tmp.SalesOrg_3_ValueName ,
428 #tmp.SalesOrg_4_ValueName ,
429 #tmp.SalesOrg_5_ValueName ,
430 #tmp.SalesOrg_6_ValueName ,
431 #tmp.SalesOrg_7_ValueName ,
432 #tmp.SalesOrg_8_ValueName ,
433 #tmp.SalesOrg_9_ValueName ,
434 ASO = #tempFullMCP.TotalOutlet ,
435 ASOItem = '.TotalOutlet' ,
436 Descr = 'Total Outlet'
437 FROM #tempFullMCP
438 LEFT JOIN dbo.DMSViewDistributorLocation vdis ON vdis.CompanyID = #tempFullMCP.CompanyID
439 AND vdis.DistributorID = #tempFullMCP.DistributorID
440 AND vdis.LocationID = #tempFullMCP.DistributorLocationID
441 LEFT JOIN #tmp ON #tmp.CompanyID = #tempFullMCP.CompanyID
442 AND #tmp.DistributorID = #tempFullMCP.DistributorID
443 AND #tmp.DistributorLocationID = #tempFullMCP.DistributorLocationID
444 -- Lấy dữ liệu của Total ASO
445 UNION ALL
446 SELECT DISTINCT
447 #tempFullMCP.CompanyID ,
448 #tempFullMCP.DistributorID ,
449 vdis.DistributorCD ,
450 vdis.DistributorName ,
451 #tmp.SalesOrgID ,
452 #tmp.SalesOrg_0_ValueName ,
453 #tmp.SalesOrg_1_ValueName ,
454 #tmp.SalesOrg_2_ValueName ,
455 #tmp.SalesOrg_3_ValueName ,
456 #tmp.SalesOrg_4_ValueName ,
457 #tmp.SalesOrg_5_ValueName ,
458 #tmp.SalesOrg_6_ValueName ,
459 #tmp.SalesOrg_7_ValueName ,
460 #tmp.SalesOrg_8_ValueName ,
461 #tmp.SalesOrg_9_ValueName ,
462 ASO = #tempFullMCP.TotalASO ,
463 ASOItem = '.TotalASO' ,
464 Descr = 'Total ASO'
465 FROM #tempFullMCP
466 LEFT JOIN dbo.DMSViewDistributorLocation vdis ON vdis.CompanyID = #tempFullMCP.CompanyID
467 AND vdis.DistributorID = #tempFullMCP.DistributorID
468 AND vdis.LocationID = #tempFullMCP.DistributorLocationID
469 LEFT JOIN #tmp ON #tmp.CompanyID = #tempFullMCP.CompanyID
470 AND #tmp.DistributorID = #tempFullMCP.DistributorID
471 AND #tmp.DistributorLocationID = #tempFullMCP.DistributorLocationID
472 END;