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