· 8 years ago · Jun 01, 2018, 08:52 AM
1SET QUOTED_IDENTIFIER ON
2SET ANSI_NULLS ON
3GO
4-- =============================================
5-- Author: Trinh Van Minh
6-- Create date: 25/05/2018
7-- Description:
8-- =============================================
9-- EXEC [dbo].[sp_DMS_RPT_ASOGroupByDistributor] 3,291,'04','2018','sysadmin',3,'','900',''
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,
18 @QueryID INT,
19 @InventoryID INT
20AS
21BEGIN
22 SET NOCOUNT ON;
23 SET FMTONLY OFF;
24 SET NO_BROWSETABLE OFF;
25
26 DECLARE @_CompanyID INT = NULLIF(@CompanyID, 0);
27 DECLARE @_Type INT = NULLIF(@Type, -1);
28 DECLARE @_DistributorID INT = NULLIF(@DistributorID, 0);
29 DECLARE @_LoginID VARCHAR(50) = @LoginID;
30 DECLARE @_TerritoryType CHAR(1);
31 DECLARE @_SalesAreaID INT = NULLIF(@SalesAreaID, 0);
32 DECLARE @_FirstDayOfMonth DATETIME = @Year + '/' + @Month + '/01';
33 DECLARE @_LastDayOfMonth DATETIME = DATEADD(dd, -1, DATEADD(mm, 1, @_FirstDayOfMonth));
34 DECLARE @_Hierarchy INT = COALESCE(NULLIF(@QueryID, ''),0) ;
35 DECLARE @_Bussiness INT =COALESCE(NULLIF(@QueryID, ''),0) ;
36 DECLARE @_Brand INT = COALESCE(NULLIF(@QueryID, ''),0) ;
37 DECLARE @_Category INT = COALESCE(NULLIF(@QueryID, ''),0) ;
38 DECLARE @_Subcat INT = COALESCE(NULLIF(@QueryID, ''),0) ;
39 DECLARE @_Packsize INT = COALESCE(NULLIF(@QueryID, ''),0) ;
40 DECLARE @_ParentSKU INT = COALESCE(NULLIF(@QueryID, ''),0) ;
41 DECLARE @_PackType INT = COALESCE(NULLIF(@QueryID, ''),0) ;
42 DECLARE @_Variant INT = COALESCE(NULLIF(@QueryID, ''),0) ;
43 DECLARE @_MaterialGroup INT = COALESCE(NULLIF(@QueryID, ''),0) ;
44 DECLARE @_InventoryID INT = COALESCE(NULLIF(@InventoryID, ''),0) ;
45
46
47
48 CREATE TABLE #tmp
49 (
50 CompanyID INT,
51 DistributorID INT,
52 DistributorLocationID INT,
53 SalesOrgID INT,
54 SalesOrg_0_ValueID INT,
55 SalesOrg_0_ValueCD VARCHAR(15),
56 SalesOrg_0_ValueName NVARCHAR(50),
57 SalesForce_0_Name NVARCHAR(50),
58 SalesOrg_1_ValueID INT,
59 SalesOrg_1_ValueCD VARCHAR(15),
60 SalesOrg_1_ValueName NVARCHAR(50),
61 SalesForce_1_Name NVARCHAR(50),
62 SalesOrg_2_ValueID INT,
63 SalesOrg_2_ValueCD VARCHAR(15),
64 SalesOrg_2_ValueName NVARCHAR(50),
65 SalesForce_2_Name NVARCHAR(50),
66 SalesOrg_3_ValueID INT,
67 SalesOrg_3_ValueCD VARCHAR(15),
68 SalesOrg_3_ValueName NVARCHAR(50),
69 SalesForce_3_Name NVARCHAR(50),
70 SalesOrg_4_ValueID INT,
71 SalesOrg_4_ValueCD VARCHAR(15),
72 SalesOrg_4_ValueName NVARCHAR(50),
73 SalesForce_4_Name NVARCHAR(50),
74 SalesOrg_5_ValueID INT,
75 SalesOrg_5_ValueCD VARCHAR(15),
76 SalesOrg_5_ValueName NVARCHAR(50),
77 SalesForce_5_Name NVARCHAR(50),
78 SalesOrg_6_ValueID INT,
79 SalesOrg_6_ValueCD VARCHAR(15),
80 SalesOrg_6_ValueName NVARCHAR(50),
81 SalesForce_6_Name NVARCHAR(50),
82 SalesOrg_7_ValueID INT,
83 SalesOrg_7_ValueCD VARCHAR(15),
84 SalesOrg_7_ValueName NVARCHAR(50),
85 SalesForce_7_Name NVARCHAR(50),
86 SalesOrg_8_ValueID INT,
87 SalesOrg_8_ValueCD VARCHAR(15),
88 SalesOrg_8_ValueName NVARCHAR(50),
89 SalesForce_8_Name NVARCHAR(50),
90 SalesOrg_9_ValueID INT,
91 SalesOrg_9_ValueCD VARCHAR(15),
92 SalesOrg_9_ValueName NVARCHAR(50),
93 SalesForce_9_Name NVARCHAR(50),
94 ParentID INT,
95 ParentLevel INT
96 );
97
98 ---- Lấy thông tin NPP với cây sales org tương ứng
99 INSERT INTO #tmp
100 EXEC dbo.sp_DMS_MDM_GetDistributorWithSalesOrg @_CompanyID;
101
102
103 SELECT vd.CompanyID,
104 vd.DistributorID,
105 vd.DistributorCD,
106 vd.DistributorName,
107 vd.DistributorFullName,
108 #tmp.SalesOrg_0_ValueCD,
109 #tmp.SalesOrg_0_ValueName,
110 #tmp.SalesOrg_1_ValueCD,
111 #tmp.SalesOrg_1_ValueName,
112 #tmp.SalesOrg_2_ValueCD,
113 #tmp.SalesOrg_2_ValueName,
114 #tmp.SalesOrg_3_ValueCD,
115 #tmp.SalesOrg_3_ValueName,
116 #tmp.SalesOrg_4_ValueCD,
117 #tmp.SalesOrg_4_ValueName,
118 #tmp.SalesOrg_5_ValueCD,
119 #tmp.SalesOrg_5_ValueName,
120 #tmp.SalesOrg_6_ValueCD,
121 #tmp.SalesOrg_6_ValueName,
122 #tmp.SalesOrg_7_ValueCD,
123 #tmp.SalesOrg_7_ValueName,
124 #tmp.SalesOrg_8_ValueCD,
125 #tmp.SalesOrg_8_ValueName,
126 #tmp.SalesOrg_9_ValueCD,
127 #tmp.SalesOrg_9_ValueName,
128 vd.DistrictName,
129 vd.ProvinceName,
130 vd.Attribute0Descr
131 INTO #tmpDistributor
132 FROM dbo.DMSViewDistributorLocation vd WITH (NOLOCK)
133 INNER JOIN #tmp
134 ON #tmp.CompanyID = vd.CompanyID
135 AND #tmp.DistributorID = vd.DistributorID
136 AND #tmp.DistributorLocationID = vd.LocationID;
137
138 CREATE NONCLUSTERED INDEX [#tmpDistributor_index]
139 ON #tmpDistributor
140 (
141 CompanyID,
142 DistributorID
143 );
144
145
146 ---- Lấy thông tin permission theo user đăng nháºp
147 SELECT *
148 INTO #tmpPermission
149 FROM dbo.fn_DMS_GetSFPermission(@_CompanyID, @_DistributorID, @_LoginID);
150 ---- Group by các dữ liệu permission
151 SELECT DistributorID,
152 DistributorLocationID
153 INTO #tmpPrmDistributor
154 FROM #tmpPermission
155 GROUP BY DistributorID,
156 DistributorLocationID;
157
158 -- Lấy thông tin NPP với cây sales org tương ứng
159 INSERT INTO #tmp
160 EXEC dbo.sp_DMS_MDM_GetDistributorWithSalesOrg @_CompanyID;
161
162 DELETE #tmp
163 FROM #tmp
164 WHERE (
165 #tmp.SalesOrg_0_ValueID IS NULL
166 OR #tmp.SalesOrg_0_ValueID != @_SalesAreaID
167 )
168 AND
169 (
170 #tmp.SalesOrg_1_ValueID IS NULL
171 OR #tmp.SalesOrg_1_ValueID != @_SalesAreaID
172 )
173 AND
174 (
175 #tmp.SalesOrg_2_ValueID IS NULL
176 OR #tmp.SalesOrg_2_ValueID != @_SalesAreaID
177 )
178 AND
179 (
180 #tmp.SalesOrg_3_ValueID IS NULL
181 OR #tmp.SalesOrg_3_ValueID != @_SalesAreaID
182 )
183 AND
184 (
185 #tmp.SalesOrg_4_ValueID IS NULL
186 OR #tmp.SalesOrg_4_ValueID != @_SalesAreaID
187 )
188 AND
189 (
190 #tmp.SalesOrg_5_ValueID IS NULL
191 OR #tmp.SalesOrg_5_ValueID != @_SalesAreaID
192 )
193 AND
194 (
195 #tmp.SalesOrg_6_ValueID IS NULL
196 OR #tmp.SalesOrg_6_ValueID != @_SalesAreaID
197 )
198 AND
199 (
200 #tmp.SalesOrg_7_ValueID IS NULL
201 OR #tmp.SalesOrg_7_ValueID != @_SalesAreaID
202 )
203 AND
204 (
205 #tmp.SalesOrg_8_ValueID IS NULL
206 OR #tmp.SalesOrg_8_ValueID != @_SalesAreaID
207 )
208 AND
209 (
210 #tmp.SalesOrg_9_ValueID IS NULL
211 OR #tmp.SalesOrg_9_ValueID != @_SalesAreaID
212 );
213
214
215 -- Lấy thông tin tất cả MCP trong thá»i gian xem báo cáo
216 SELECT DISTINCT
217 mcpde.CompanyID,
218 mcpde.DistributorID,
219 mcpde.DistributorLocationID,
220 mcpde.CustomerID,
221 mcpde.CustomerLocationID,
222 rs.EndDate
223 INTO #tempMCP
224 FROM dbo.DMSMCPDetail mcpde
225 INNER JOIN dbo.DMSRouteSetting rs
226 ON rs.CompanyID = mcpde.CompanyID
227 AND rs.RefNbr = mcpde.RefNbr
228 WHERE mcpde.CompanyID = @_CompanyID
229 AND rs.EffectiveDate <= @_LastDayOfMonth
230 AND (rs.EndDate IS NULL OR ( @_FirstDayOfMonth <= rs.EndDate))
231 AND mcpde.EffectiveDate <= @_LastDayOfMonth
232 AND ( mcpde.EndDate IS NULL OR @_FirstDayOfMonth <= mcpde.EndDate)
233 AND rs.Status <> 'N'
234 AND
235 (
236 @_DistributorID IS NULL
237 OR (mcpde.DistributorID = @_DistributorID)
238 );
239 -- Lấy ra tổng số Outlet trong thá»i gian xem báo cáo
240
241 SELECT CompanyID,
242 DistributorID,
243 DistributorLocationID,
244 COUNT(DISTINCT #tempMCP.CustomerLocationID) TotalOutlet
245 INTO #tempTotalOutlet
246 FROM #tempMCP
247 GROUP BY CompanyID,
248 DistributorID,
249 DistributorLocationID;
250
251 -- Lấy ra các line hà ng đã hoà n thà nh trong thá»i gian báo cáo
252 SELECT DISTINCT
253 so.CompanyID,
254 DistributorID = so.BranchID,
255 DistributorLocationID = NULL,
256 so.CustomerID,
257 so.CustomerLocationID,
258 so.OrderDate,
259 soline.InventoryID,
260 hrchy.Hierachy0,
261 hrchy.Hierachy1,
262 hrchy.Hierachy2,
263 hrchy.Hierachy3,
264 hrchy.Hierachy4,
265 hrchy.Hierachy5,
266 hrchy.Hierachy6,
267 hrchy.Hierachy7,
268 hrchy.Hierachy8,
269 hrchy.Hierachy9
270 INTO #tempSOLine
271 FROM dbo.SOOrder so
272 JOIN dbo.SOLine soline
273 ON soline.CompanyID = so.CompanyID
274 AND soline.OrderNbr = so.OrderNbr
275 AND soline.OrderType = so.OrderType
276 JOIN dbo.DMSEntityAttribute ent
277 ON ent.CompanyID = soline.CompanyID
278 AND ent.EntityID = soline.InventoryID
279 JOIN dbo.DMSProductHierarchy hrchy
280 ON hrchy.CompanyID = ent.CompanyID
281 AND hrchy.HierarchyID = ent.HierachyID
282 JOIN dbo.SOOrderShipment ordership
283 ON ordership.CompanyID = so.CompanyID
284 AND ordership.OrderNbr = so.OrderNbr
285 AND ordership.OrderType = so.OrderType
286 JOIN dbo.INRegister inr
287 ON inr.CompanyID = ordership.CompanyID
288 AND inr.RefNbr = ordership.InvtRefNbr
289 AND inr.DocType = ordership.InvtDocType
290 AND inr.Released = 1
291 WHERE so.CompanyID = @_CompanyID
292 AND so.OrderDate
293 BETWEEN @_FirstDayOfMonth AND @_LastDayOfMonth
294 AND so.OrderType <> 'CM'
295 AND ISNULL(so.UsrIsDispose, 0) <> 1
296 AND soline.IsFree = 0
297 AND
298 (
299 @_DistributorID IS NULL
300 OR (soline.BranchID = @_DistributorID)
301 );
302
303 -- Update lại các giá trị DistributorLocationID
304 UPDATE ao
305 SET ao.DistributorLocationID = vsor.DistributorLocationID
306 FROM #tempSOLine ao
307 JOIN dbo.DMSViewSORoute vsor WITH (NOLOCK)
308 ON vsor.CompanyID = ao.CompanyID
309 AND vsor.CustomerID = ao.CustomerID
310 AND vsor.CustomerLocationID = ao.CustomerLocationID
311 AND ao.OrderDate
312 BETWEEN vsor.EffectiveDate AND vsor.EndDate;
313
314 DELETE #tempSOLine
315 FROM #tempSOLine
316 LEFT JOIN #tmp
317 ON #tmp.CompanyID = #tempSOLine.CompanyID
318 AND #tmp.DistributorID = #tempSOLine.DistributorID
319 AND #tmp.DistributorLocationID = #tempSOLine.DistributorLocationID
320 WHERE #tmp.CompanyID IS NULL;
321
322 -- Lấy ra các SO của theo các nhà phân phối mà user login đó được xem
323 SELECT *
324 INTO #tempSOLineWithLogin
325 FROM #tempSOLine sol
326 WHERE EXISTS
327 (
328 SELECT TOP 1
329 *
330 FROM #tmpPrmDistributor pd
331 WHERE pd.DistributorID = sol.DistributorID
332 AND pd.DistributorLocationID = sol.DistributorLocationID
333 );
334
335 CREATE TABLE #tempSoWithHierachy
336 (
337 CompanyID INT NULL,
338 AttributeID INT NULL,
339 BranchID INT NULL,
340 DistributorLocationID INT NULL,
341 CustomerID INT NULL,
342 CustomerLocationID INT NULL,
343 InventoryID INT NULL,
344 Hierachy0 INT NULL,
345 Hierachy1 INT NULL,
346 Hierachy2 INT NULL,
347 Hierachy3 INT NULL,
348 Hierachy4 INT NULL,
349 Hierachy5 INT NULL,
350 Hierachy6 INT NULL,
351 Hierachy7 INT NULL,
352 Hierachy8 INT NULL,
353 Hierachy9 INT NULL
354 );
355 IF @_Type <> -1
356 INSERT INTO #tempSoWithHierachy
357 SELECT DISTINCT
358 soline.CompanyID,
359 att.AttributeID,
360 soline.DistributorID,
361 soline.DistributorLocationID,
362 soline.CustomerID,
363 soline.CustomerLocationID,
364 soline.InventoryID,
365 soline.Hierachy0,
366 soline.Hierachy1,
367 soline.Hierachy2,
368 soline.Hierachy3,
369 soline.Hierachy4,
370 soline.Hierachy5,
371 soline.Hierachy6,
372 soline.Hierachy7,
373 soline.Hierachy8,
374 soline.Hierachy9
375 FROM #tempSOLineWithLogin soline
376 JOIN DMSAttribute att
377 ON att.CompanyID = soline.CompanyID
378 AND
379 (
380 att.AttributeID = soline.Hierachy0
381 OR att.AttributeID = soline.Hierachy0
382 OR att.AttributeID = soline.Hierachy1
383 OR att.AttributeID = soline.Hierachy2
384 OR att.AttributeID = soline.Hierachy3
385 OR att.AttributeID = soline.Hierachy4
386 )
387 WHERE soline.CompanyID = @_CompanyID
388 AND att.Type = 'IN'
389 AND att.AttributeNbr = @_Type;
390 ELSE
391 INSERT INTO #tempSoWithHierachy
392 SELECT DISTINCT
393 soline.CompanyID,
394 soline.InventoryID,
395 soline.DistributorID,
396 soline.DistributorLocationID,
397 soline.CustomerID,
398 soline.CustomerLocationID,
399 soline.InventoryID,
400 soline.Hierachy0,
401 soline.Hierachy1,
402 soline.Hierachy2,
403 soline.Hierachy3,
404 soline.Hierachy4,
405 soline.Hierachy5,
406 soline.Hierachy6,
407 soline.Hierachy7,
408 soline.Hierachy8,
409 soline.Hierachy9
410 FROM #tempSOLineWithLogin soline
411 WHERE soline.CompanyID = @_CompanyID;
412
413 SELECT #tempMCP.CompanyID,
414 #tempMCP.DistributorID,
415 #tempMCP.DistributorLocationID,
416 hie.AttributeID,
417 hie.InventoryID,
418 #tempMCP.CustomerID,
419 #tempMCP.CustomerLocationID,
420 ASO = COUNT(DISTINCT #tempMCP.CustomerLocationID)
421 INTO #tempASO
422 FROM #tempMCP
423 JOIN #tempSoWithHierachy hie
424 ON hie.CompanyID = #tempMCP.CompanyID
425 AND hie.BranchID = #tempMCP.DistributorID
426 AND hie.DistributorLocationID = #tempMCP.DistributorLocationID
427 AND hie.CustomerID = #tempMCP.CustomerID
428 GROUP BY #tempMCP.CompanyID,
429 #tempMCP.DistributorID,
430 #tempMCP.DistributorLocationID,
431 hie.AttributeID,
432 hie.InventoryID,
433 #tempMCP.CustomerID,
434 #tempMCP.CustomerLocationID;
435
436
437
438
439
440 CREATE TABLE #tempResource
441 (
442 CompanyID INT NOT NULL,
443 AttributeID INT NOT NULL,
444 AttributeCD VARCHAR(15) NOT NULL,
445 Descr NVARCHAR(50) NULL
446 );
447 IF @_Type <> -1
448 INSERT INTO #tempResource
449 SELECT CompanyID,
450 AttributeID,
451 AttributeCD,
452 Descr
453 FROM DMSAttribute
454 WHERE CompanyID = @_CompanyID
455 AND Type = 'IN'
456 AND AttributeNbr = @_Type;
457 ELSE
458 INSERT INTO #tempResource
459 SELECT CompanyID,
460 InventoryID,
461 InventoryCD,
462 Descr
463 FROM dbo.InventoryItem
464 WHERE CompanyID = @_CompanyID;
465
466
467 SELECT DISTINCT
468 #tempASO.CompanyID,
469 #tempASO.DistributorID,
470 #tempASO.DistributorLocationID,
471 #tempTotalOutlet.TotalOutlet,
472 #tempASO.CustomerID,
473 #tempASO.CustomerLocationID,
474 cl.CustomerCD,
475 cl.CustomerName,
476 cl.Attribute0CD,
477 cl.Attribute0Descr,
478 cl.Attribute1CD,
479 cl.Attribute1Descr,
480 cl.Attribute2CD,
481 cl.Attribute2Descr,
482 cl.Attribute3CD,
483 cl.Attribute3Descr,
484 cl.Attribute4CD,
485 cl.Attribute4Descr,
486 cl.Attribute5CD,
487 cl.Attribute5Descr,
488 cl.Attribute6CD,
489 cl.Attribute6Descr,
490 cl.Attribute7CD,
491 cl.Attribute7Descr,
492 cl.Attribute8CD,
493 cl.Attribute8Descr,
494 cl.Attribute9CD,
495 cl.Attribute9Descr,
496 cl.CustomerAddress,
497 cl.DistrictName,
498 cl.ProvinceName,
499 InventoryID,
500 #tempASO.ASO,
501 att.AttributeID,
502 att.AttributeCD,
503 att.Descr
504 INTO #tempFullMCP
505 FROM #tempASO
506 INNER JOIN dbo.DMSViewCustomerLocation cl
507 ON cl.CompanyID = #tempASO.CompanyID
508 AND cl.CustomerID = #tempASO.CustomerID
509 AND cl.LocationID = #tempASO.CustomerLocationID
510 LEFT JOIN #tempTotalOutlet
511 ON #tempASO.CompanyID = #tempTotalOutlet.CompanyID
512 AND #tempASO.DistributorID = #tempTotalOutlet.DistributorID
513 AND #tempASO.DistributorLocationID = #tempTotalOutlet.DistributorLocationID
514 LEFT JOIN #tempResource att
515 ON att.CompanyID = #tempASO.CompanyID
516 AND att.AttributeID = #tempASO.AttributeID;
517
518
519 SELECT pd.CompanyID,
520 pd.DistributorID,
521 rs.SalesForceID,
522 rs.SalespersonID,
523 pd.CustomerID,
524 pd.CustomerLocationID,
525 rs.SellingCategoryID,
526 CASE
527 WHEN pd.Monday = 1
528 AND
529 (
530 pd.Tuesday = 1
531 OR pd.Wednesday = 1
532 OR pd.Thursday = 1
533 OR pd.Friday = 1
534 OR pd.Saturday = 1
535 OR pd.Sunday = 1
536 ) THEN
537 'Monday - '
538 WHEN pd.Monday = 1
539 AND
540 (
541 pd.Tuesday = 0
542 AND pd.Wednesday = 0
543 AND pd.Thursday = 0
544 OR pd.Friday = 0
545 AND pd.Saturday = 0
546 AND pd.Sunday = 0
547 ) THEN
548 'Monday'
549 ELSE
550 ''
551 END + CASE
552 WHEN pd.Tuesday = 1
553 AND
554 (
555 pd.Wednesday = 1
556 OR pd.Thursday = 1
557 OR pd.Friday = 1
558 OR pd.Saturday = 1
559 OR pd.Sunday = 1
560 ) THEN
561 'Tuesday - '
562 WHEN pd.Tuesday = 1
563 AND
564 (
565 pd.Wednesday = 0
566 AND pd.Thursday = 0
567 AND pd.Friday = 0
568 AND pd.Saturday = 0
569 AND pd.Sunday = 0
570 ) THEN
571 'Tuesday'
572 ELSE
573 ''
574 END + CASE
575 WHEN pd.Wednesday = 1
576 AND
577 (
578 pd.Thursday = 1
579 OR pd.Friday = 1
580 OR pd.Saturday = 1
581 OR pd.Sunday = 1
582 ) THEN
583 'Wednesday - '
584 WHEN pd.Wednesday = 1
585 AND
586 (
587 pd.Thursday = 0
588 AND pd.Friday = 0
589 AND pd.Saturday = 0
590 AND pd.Sunday = 0
591 ) THEN
592 'Wednesday'
593 ELSE
594 ''
595 END + CASE
596 WHEN pd.Thursday = 1
597 AND
598 (
599 pd.Friday = 1
600 OR pd.Saturday = 1
601 OR pd.Sunday = 1
602 ) THEN
603 'Thursday - '
604 WHEN pd.Thursday = 1
605 AND
606 (
607 pd.Friday = 0
608 AND pd.Saturday = 0
609 AND pd.Sunday = 0
610 ) THEN
611 'Thursday'
612 ELSE
613 ''
614 END + CASE
615 WHEN pd.Friday = 1
616 AND
617 (
618 pd.Saturday = 1
619 OR pd.Sunday = 1
620 ) THEN
621 'Friday - '
622 WHEN pd.Friday = 1
623 AND
624 (
625 pd.Saturday = 0
626 AND pd.Sunday = 0
627 ) THEN
628 'Friday'
629 ELSE
630 ''
631 END + CASE
632 WHEN pd.Saturday = 1
633 AND pd.Sunday = 1 THEN
634 'Saturday - '
635 WHEN pd.Saturday = 1
636 AND pd.Sunday = 0 THEN
637 'Saturday'
638 ELSE
639 ''
640 END + CASE
641 WHEN pd.Sunday = 1 THEN
642 'Sunday'
643 ELSE
644 ''
645 END BeatPlan
646 INTO #tmpBeat
647 FROM MRCDMS..DMSRouteSetting rs
648 INNER JOIN MRCDMS..DMSMCPDetail pd
649 ON pd.CompanyID = rs.CompanyID
650 AND pd.RefNbr = rs.RefNbr
651 WHERE (
652 (pd.EffectiveDate
653 BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, EOMONTH(DATEFROMPARTS(@Year, @Month, 1))), 0) AND EOMONTH(DATEFROMPARTS(
654 @Year,
655 @Month,
656 1
657 )
658 )
659 )
660 OR
661 (
662 rs.EndDate IS NULL
663 OR (rs.EndDate
664 BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, EOMONTH(DATEFROMPARTS(@Year, @Month, 1))), 0) AND EOMONTH(DATEFROMPARTS(
665 @Year,
666 @Month,
667 1
668 )
669 )
670 )
671 )
672 )
673 AND rs.Status = 'G' --Generated
674 AND rs.Active = 1 -- Active
675 AND pd.CompanyID = 3;
676
677
678
679 SELECT DISTINCT
680 cmp.CompanyID,
681 tdd.SalesOrg_3_ValueName AS Bussiness,
682 tdd.Attribute0Descr AS Chanel,
683 dl.DistributorCD,
684 dl.DistributorName,
685 sf.EmployeeCD AS SalesSupCD,
686 sf.Descr AS SalesSupName,
687 sf1.EmployeeCD AS SRCode,
688 sf1.Descr AS SRName,
689 cmp.CustomerCD,
690 cmp.CustomerName,
691 BeatPlan,
692 cmp.CustomerAddress,
693 cmp.DistrictName,
694 cmp.ProvinceName,
695 #tmpBeat.CustomerLocationID,
696 cmp.Attribute0Descr,
697 cmp.Attribute1Descr,
698 cmp.Attribute2Descr,
699 cmp.Attribute3Descr,
700 cmp.Attribute4Descr,
701 cmp.Attribute5Descr,
702 cmp.Attribute6Descr,
703 cmp.Attribute7Descr,
704 cmp.Attribute8Descr,
705 cmp.Attribute9Descr,
706 cmp.ASO--,
707 --cmp.InventoryID,
708 --ii.HierarchyID,
709 --ii.HierarchyCD,
710 --ii.HierarchyDescr,
711 --ii.Hierachy0ID,
712 --ii.Hierachy0CD,
713 --ii.Hierachy0Descr,
714 --ii.Hierachy1ID,
715 --ii.Hierachy1CD,
716 --ii.Hierachy1Descr,
717 --ii.Hierachy2ID,
718 --ii.Hierachy2CD,
719 --ii.Hierachy2Descr,
720 --ii.Hierachy3ID,
721 --ii.Hierachy3CD,
722 --ii.Hierachy3Descr,
723 --ii.Hierachy4ID,
724 --ii.Hierachy4CD,
725 --ii.Hierachy4Descr,
726 --ii.Attribute6ID,
727 --ii.Attribute6CD,
728 --ii.Attribute6Descr,
729 --ii.Attribute7ID,
730 --ii.Attribute7CD,
731 --ii.Attribute7Descr,
732 --ii.Attribute8ID,
733 --ii.Attribute8CD,
734 --ii.Attribute8Descr,
735 --ii.Attribute9ID,
736 --ii.Attribute9CD,
737 --ii.Attribute9Descr
738 FROM #tempFullMCP cmp
739 LEFT JOIN #tmpDistributor tdd
740 ON tdd.CompanyID = cmp.CompanyID
741 AND tdd.DistributorID = cmp.DistributorID
742 LEFT JOIN DMSViewDistributorLocation dl
743 ON dl.CompanyID = cmp.CompanyID
744 AND dl.DistributorID = cmp.DistributorID
745 INNER JOIN dbo.DMSViewInventoryItem ii
746 ON ii.CompanyID = cmp.CompanyID
747 AND cmp.InventoryID = ii.InventoryID
748 LEFT JOIN #tmpBeat
749 ON #tmpBeat.CompanyID = cmp.CompanyID
750 AND #tmpBeat.CustomerID = cmp.CustomerID
751 AND #tmpBeat.CustomerLocationID = cmp.CustomerLocationID
752 AND #tmpBeat.DistributorID = cmp.DistributorID
753 LEFT JOIN dbo.DMSSalesForce sf
754 ON sf.CompanyID = cmp.CompanyID
755 AND sf.EmployeeID = SalesForceID
756 LEFT JOIN dbo.DMSSalesForce sf1
757 ON sf1.CompanyID = cmp.CompanyID
758 AND sf1.EmployeeID = SalespersonID
759 WHERE (@_InventoryID = 0 OR ii.InventoryID = @_InventoryID)
760 AND ( (@_Hierarchy = 0 OR ii.HierarchyID = @_Hierarchy)
761 OR (@_ParentSKU = 0 OR ii.Attribute6ID = @_ParentSKU)
762 OR (@_PackType = 0 OR ii.Attribute7ID = @_PackType)
763 OR (@_Variant = 0 OR ii.Attribute8ID = @_Variant)
764 OR (@_MaterialGroup = 0 OR ii.Attribute9ID = @_MaterialGroup)
765 OR (@_Bussiness = 0 OR ii.Hierachy0ID = @_Bussiness)
766 OR (@_Brand = 0 OR ii.Hierachy1ID = @_Brand)
767 OR (@_Category = 0 OR ii.Hierachy2ID = @_Category)
768 OR (@_Subcat = 0 OR ii.Hierachy3ID = @_Subcat)
769 OR (@_Packsize = 0 OR ii.Hierachy4ID = @_Packsize))
770
771 -- Lấy dữ liệu cuối cùng để xuất ra báo cáo
772-- SELECT DISTINCT #tempFullMCP.CompanyID,
773-- #tempFullMCP.DistributorID,
774-- vdis.DistributorCD,
775-- vdis.DistributorName,
776--#tempFullMCP.CustomerID,
777--#tempFullMCP.CustomerLocationID,
778-- #tmp.SalesOrgID,
779-- #tmp.SalesOrg_0_ValueName,
780-- #tmp.SalesOrg_1_ValueName,
781-- #tmp.SalesOrg_2_ValueName,
782-- #tmp.SalesOrg_3_ValueName,
783-- #tmp.SalesOrg_4_ValueName,
784-- #tmp.SalesOrg_5_ValueName,
785-- #tmp.SalesOrg_6_ValueName,
786-- #tmp.SalesOrg_7_ValueName,
787-- #tmp.SalesOrg_8_ValueName,
788-- #tmp.SalesOrg_9_ValueName,
789-- ASO = ISNULL(#tempASO.ASO, 0),
790-- ASOItem = #tempFullMCP.AttributeCD,
791-- #tempFullMCP.Descr
792-- FROM #tempFullMCP
793-- LEFT JOIN #tempASO
794-- ON #tempFullMCP.CompanyID = #tempASO.CompanyID
795-- AND #tempFullMCP.AttributeID = #tempASO.AttributeID
796-- AND #tempASO.DistributorLocationID = #tempFullMCP.DistributorLocationID
797-- LEFT JOIN dbo.DMSViewDistributorLocation vdis
798-- ON vdis.CompanyID = #tempFullMCP.CompanyID
799-- AND vdis.DistributorID = #tempFullMCP.DistributorID
800-- AND vdis.LocationID = #tempFullMCP.DistributorLocationID
801-- LEFT JOIN #tmp
802-- ON #tmp.CompanyID = #tempFullMCP.CompanyID
803-- AND #tmp.DistributorID = #tempFullMCP.DistributorID
804-- AND #tmp.DistributorLocationID = #tempFullMCP.DistributorLocationID
805
806
807END;
808
809
810
811
812GO