9dLdqUX2

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