· 7 years ago · Sep 12, 2018, 10:56 AM
1USE [THMilkDMS2]
2GO
3/****** Object: StoredProcedure [dbo].[sp_DMS_RPT_SalesInExportRawData] Script Date: 9/12/2018 5:49:17 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8---- EXEC dbo.sp_DMS_RPT_SalesInExportRawData1 3, 0, '2018-08-01','2018-09-30','300104',0,0,0,0,0
9ALTER PROCEDURE [dbo].[sp_DMS_RPT_SalesInExportRawData1]
10 @CompanyID INT = NULL,
11 @DistributorID INT = NULL,
12 @FromDate DATETIME = NULL,
13 @ToDate DATETIME = NULL,
14 @LoginID VARCHAR(50) = NULL,
15 @ProvinceID INT = NULL,
16 @SalesAreaID INT = NULL,
17 @HierarchyID INT = NULL,
18 @InventoryID INT = NULL,
19 @ChannelID INT = NULL,
20 @SelectedDistributorID INT = NULL
21AS
22BEGIN
23 SET NOCOUNT ON;
24 SET FMTONLY OFF;
25 SET NO_BROWSETABLE OFF;
26
27 DECLARE @_CompanyID INT = NULLIF(@CompanyID, 0);
28 DECLARE @_DistributorID INT = NULLIF(@DistributorID, 0);
29 DECLARE @_FromDate DATETIME = @FromDate;
30 DECLARE @_ToDate DATETIME = @ToDate;
31 DECLARE @_LoginID VARCHAR(50) = @LoginID;
32 DECLARE @_ProvinceID INT = NULLIF(@ProvinceID, 0);
33 DECLARE @_SalesAreaID INT = NULLIF(@SalesAreaID, 0);
34 DECLARE @_HierarchyID INT = NULLIF(@HierarchyID, 0);
35 DECLARE @_InventoryID INT = NULLIF(@InventoryID, 0);
36 DECLARE @_ChannelID INT = NULLIF(@ChannelID, 0);
37 DECLARE @_TerritoryType CHAR(1);
38 DECLARE @_SelectedDistributorID INT = NULLIF(@SelectedDistributorID, 0);
39
40 SELECT
41 CompanyID,
42 SiteID
43INTO
44 #tmpSiteAccess
45FROM
46 dbo.DMSViewSiteByUser
47WHERE
48 CompanyID = @_CompanyID
49 AND Username = @_LoginID;
50
51
52CREATE TABLE #tmpMDM_GetDistributorWithSalesOrg
53 (
54 CompanyID INT,
55 DistributorID INT,
56 DistributorLocationID INT,
57 SalesOrgID INT,
58 SalesOrg_0_ValueID INT,
59 SalesOrg_0_ValueCD VARCHAR(15),
60 SalesOrg_0_ValueName NVARCHAR(50),
61 SalesForce_0_Name NVARCHAR(50),
62 SalesOrg_1_ValueID INT,
63 SalesOrg_1_ValueCD VARCHAR(15),
64 SalesOrg_1_ValueName NVARCHAR(50),
65 SalesForce_1_Name NVARCHAR(50),
66 SalesOrg_2_ValueID INT,
67 SalesOrg_2_ValueCD VARCHAR(15),
68 SalesOrg_2_ValueName NVARCHAR(50),
69 SalesForce_2_Name NVARCHAR(50),
70 SalesOrg_3_ValueID INT,
71 SalesOrg_3_ValueCD VARCHAR(15),
72 SalesOrg_3_ValueName NVARCHAR(50),
73 SalesForce_3_Name NVARCHAR(50),
74 SalesOrg_4_ValueID INT,
75 SalesOrg_4_ValueCD VARCHAR(15),
76 SalesOrg_4_ValueName NVARCHAR(50),
77 SalesForce_4_Name NVARCHAR(50),
78 SalesOrg_5_ValueID INT,
79 SalesOrg_5_ValueCD VARCHAR(15),
80 SalesOrg_5_ValueName NVARCHAR(50),
81 SalesForce_5_Name NVARCHAR(50),
82 SalesOrg_6_ValueID INT,
83 SalesOrg_6_ValueCD VARCHAR(15),
84 SalesOrg_6_ValueName NVARCHAR(50),
85 SalesForce_6_Name NVARCHAR(50),
86 SalesOrg_7_ValueID INT,
87 SalesOrg_7_ValueCD VARCHAR(15),
88 SalesOrg_7_ValueName NVARCHAR(50),
89 SalesForce_7_Name NVARCHAR(50),
90 SalesOrg_8_ValueID INT,
91 SalesOrg_8_ValueCD VARCHAR(15),
92 SalesOrg_8_ValueName NVARCHAR(50),
93 SalesForce_8_Name NVARCHAR(50),
94 SalesOrg_9_ValueID INT,
95 SalesOrg_9_ValueCD VARCHAR(15),
96 SalesOrg_9_ValueName NVARCHAR(50),
97 SalesForce_9_Name NVARCHAR(50),
98 ParentID INT,
99 ParentLevel INT
100 );
101
102---- Lấy thông tin permission theo user đăng nháºp
103SELECT
104 *
105INTO
106 #tmpPermission
107FROM
108 dbo.fn_DMS_GetSFPermission(@_CompanyID, @_DistributorID, @_LoginID);
109
110
111---- Lấy working position của user đăng nháºp
112---- Äấy là báo cáo NPP nên nếu territory type khác SS thì set kiểu view 1 NPP
113SELECT
114 @_TerritoryType =
115 (
116 SELECT TOP 1
117 TerritoryType
118 FROM
119 #tmpPermission
120 );
121IF @_TerritoryType != 'D'
122 OR @_TerritoryType IS NULL
123 SET @_TerritoryType = 'X';
124
125---- Group by các dữ liệu permission
126SELECT
127 RouteCD
128INTO
129 #tmpPrmRoute
130FROM
131 #tmpPermission
132GROUP BY
133 RouteCD;
134
135---- Lấy thông tin NPP với cây sales org tương ứng
136INSERT INTO #tmpMDM_GetDistributorWithSalesOrg
137EXEC dbo.sp_DMS_MDM_GetDistributorWithSalesOrg
138 @_CompanyID;
139
140--SELECT *
141--INTO #tmp
142--FROM #tmpMDM_GetDistributorWithSalesOrg;
143
144--Hieu : Lấy danh sách NPP theo phan quyen
145
146SELECT
147 vd.DistributorCD,
148 vd.DistributorName,
149 vd.DistributorFullName,
150 tmp.*
151INTO
152 #tmpDistributor
153FROM
154 dbo.DMSViewDistributorLocation vd WITH (NOLOCK)
155 INNER JOIN
156 #tmpMDM_GetDistributorWithSalesOrg tmp WITH (NOLOCK)
157 ON tmp.CompanyID = vd.CompanyID
158 AND tmp.DistributorID = vd.DistributorID
159 AND tmp.DistributorLocationID = vd.LocationID
160 INNER JOIN
161 #tmpPermission p
162 ON tmp.DistributorID = p.DistributorID
163 AND tmp.DistributorLocationID = p.DistributorLocationID
164WHERE
165 vd.CompanyID = 3
166 AND vd.IsActive = 1
167 AND vd.IsDefault = 1
168 AND
169 (
170 @_SalesAreaID IS NULL
171 OR tmp.SalesOrg_0_ValueID = @_SalesAreaID
172 OR tmp.SalesOrg_1_ValueID = @_SalesAreaID
173 OR tmp.SalesOrg_2_ValueID = @_SalesAreaID
174 OR tmp.SalesOrg_3_ValueID = @_SalesAreaID
175 OR tmp.SalesOrg_4_ValueID = @_SalesAreaID
176 OR tmp.SalesOrg_5_ValueID = @_SalesAreaID
177 OR tmp.SalesOrg_6_ValueID = @_SalesAreaID
178 OR tmp.SalesOrg_7_ValueID = @_SalesAreaID
179 OR tmp.SalesOrg_8_ValueID = @_SalesAreaID
180 OR tmp.SalesOrg_9_ValueID = @_SalesAreaID
181 )
182 AND vd.DistributorName NOT LIKE N'%test%';
183
184DROP TABLE #tmpMDM_GetDistributorWithSalesOrg
185DROP TABLE #tmpPermission
186
187CREATE NONCLUSTERED INDEX [#tmpDistributor_index]
188 ON #tmpDistributor (CompanyID, DistributorID);
189
190--Hieu : Lá»c bảng SOOrder ra bảng tạm, giá»›i hạn số data lại vì bảng nà y nhiá»u query truy xuất và o
191
192SELECT
193 o.*
194INTO
195 #SOOrder
196FROM
197 dbo.SOOrder o WITH (NOLOCK)
198 INNER JOIN
199 #tmpDistributor d
200 ON o.CompanyID = d.CompanyID
201 AND o.BranchID = d.DistributorID
202WHERE
203 o.OrderDate
204 BETWEEN @_FromDate AND @_ToDate
205 AND o.Completed = 1
206 AND o.Cancelled = 0
207 AND
208 (
209 @_SelectedDistributorID IS NULL
210 OR o.BranchID = @_SelectedDistributorID
211 );
212
213CREATE NONCLUSTERED INDEX idx_#SOOrder_select
214 ON #SOOrder ([CompanyID], [OrderType], [OrderNbr])
215 INCLUDE ([UsrReasonCodeID], [UsrPDAOrderType], [UsrPDAOrderDate]);
216
217CREATE NONCLUSTERED INDEX idx_#SOOrder_return
218 ON #SOOrder ([CompanyID], [OrderNbr]);
219
220
221 SELECT * FROM #SOOrder WHERE OrderNbr = 'DH00200-0010744'; RETURN;
222
223
224--------------------------------- Lấy đơn CM đã hoà n tất
225SELECT
226 so.OrigOrderNbr,
227 so.CompanyID,
228 so.OrigOrderType
229INTO
230 #tmpCompletedCM
231FROM
232 dbo.SOOrder so
233 INNER JOIN
234 #SOOrder o
235 ON so.CompanyID = o.CompanyID
236 AND so.OrigOrderNbr = o.OrderNbr
237WHERE
238 so.CompanyID = @_CompanyID
239 AND so.OrderType = 'CM'
240 AND so.Completed = 1
241 AND so.Cancelled = 0
242 AND
243 (
244 @_TerritoryType = 'X'
245 OR EXISTS
246 (
247 SELECT TOP 1
248 *
249 FROM
250 #tmpPrmRoute pr
251 WHERE
252 pr.RouteCD = so.UsrRoute
253 )
254 ) ---- Kiểm tra permission với working position là SS
255 AND
256 (
257 @_SelectedDistributorID IS NULL
258 OR so.BranchID = @_SelectedDistributorID
259 );
260
261---- Lấy tất cả Ä‘Æ¡n hà ng trong khoảng thá»i gian đã chá»n, không quan tâm hoà n tất hay không
262SELECT
263 soo.CompanyID,
264 DistributorID = soo.DistributorID,
265 DistributorLocationID = NULL,
266 SalesForceID = NULL,
267 soo.OrderNbr,
268 OrderType = CASE soo.OrderType
269 WHEN 'CM'
270 THEN
271 'CM'
272 ELSE
273 so.UsrPDAOrderType
274 END,
275 soo.OrderDate,
276 soo.CustomerOrderNbr,
277 soo.CustomerID,
278 soo.CustomerLocationID,
279 SalesRepsID = soo.SalespersonID,
280 Route = soo.Route,
281 RouteName = r.Descr,
282 soo.InventoryID,
283 soo.UnitPrice,
284 soo.OrderLineNbr,
285 OrderQty = soo.OrderQty,
286 soo.LotSerialNbr,
287 soo.ExpireDate,
288 ShippingQty = soo.ShippedQty,
289 OpenAmt = 0,
290 soo.TranAmt,
291 soo.IsFree,
292 soo.SiteID,
293 soo.IsValidDate,
294 soo.IsValidDistance,
295 Status = '',
296 so.UsrPDAOrderDate PDAOrderDate,
297 soo.CustomerOrderNbr PDAOrderNbr,
298 TrueRoute = CASE ISNULL(soo.IsValidDate, 0)
299 WHEN 1
300 THEN
301 'True'
302 ELSE
303 'False'
304 END,
305 soo.InvoiceNbr,
306 soo.InvoiceDate,
307 soo.TranAmt NetAmt,
308 soo.DiscAmt FreeAmt,
309 OrderAmt = soo.OrderQty * soo.UnitPrice,
310 rc.[Desc] ReasonCodeDesc,
311 IsManual = CASE ISNULL(soo.ManualDisc, 0)
312 WHEN 1
313 THEN
314 'True'
315 ELSE
316 'False'
317 END,
318 ShipmentNbr = NULL,
319 ShipDate = NULL,
320 SOOrderType = soo.OrderType,
321 CompletedDate = soo.LastModifiedDateTime,
322 soo.IsDispose
323INTO
324 #tmpAllOrder
325FROM
326 THMilkBaseline2..DMSBLRawSales soo WITH (NOLOCK)
327 INNER JOIN
328 #SOOrder so
329 ON so.CompanyID = soo.CompanyID
330 AND so.OrderNbr = soo.OrderNbr
331 AND so.OrderType = soo.OrderType
332 LEFT JOIN
333 dbo.DMSRoute r WITH (NOLOCK)
334 ON r.CompanyID = soo.CompanyID
335 AND r.RouteCD = soo.Route
336 LEFT JOIN
337 dbo.DMSSOReasonCode rc WITH (NOLOCK)
338 ON rc.CompanyID = so.CompanyID
339 AND rc.ReasonCodeID = so.UsrReasonCodeID
340WHERE
341 soo.CompanyID = @_CompanyID
342 AND soo.OrderDate
343 BETWEEN @_FromDate AND @_ToDate
344 AND EXISTS
345 (
346 SELECT
347 *
348 FROM
349 #tmpSiteAccess sa
350 WHERE
351 sa.CompanyID = so.CompanyID
352 AND sa.SiteID = soo.SiteID
353 )
354 AND
355 (
356 @_TerritoryType = 'X'
357 OR EXISTS
358 (
359 SELECT TOP 1
360 *
361 FROM
362 #tmpPrmRoute pr
363 WHERE
364 pr.RouteCD = soo.Route
365 )
366 ); ---- Kiểm tra permission với working position là SS
367
368 SELECT * FROM #tmpAllOrder WHERE OrderNbr = 'DH00200-0010744'; RETURN;
369
370INSERT INTO #tmpAllOrder
371 SELECT
372 soo.CompanyID,
373 DistributorID = soo.DistributorID,
374 DistributorLocationID = NULL,
375 SalesForceID = NULL,
376 soo.OrderNbr,
377 OrderType = CASE soo.OrderType
378 WHEN 'CM'
379 THEN
380 'CM'
381 ELSE
382 so.UsrPDAOrderType
383 END,
384 soo.OrderDate,
385 soo.CustomerOrderNbr,
386 soo.CustomerID,
387 soo.CustomerLocationID,
388 SalesRepsID = soo.SalespersonID,
389 Route = soo.Route,
390 RouteName = r.Descr,
391 soo.InventoryID,
392 soo.UnitPrice,
393 soo.OrderLineNbr,
394 OrderQty = soo.OrderQty,
395 soo.LotSerialNbr,
396 soo.ExpireDate,
397 ShippingQty = soo.ShippedQty,
398 OpenAmt = 0,
399 soo.TranAmt,
400 soo.IsFree,
401 soo.SiteID,
402 soo.IsValidDate,
403 soo.IsValidDistance,
404 Status = '',
405 so.UsrPDAOrderDate PDAOrderDate,
406 soo.CustomerOrderNbr PDAOrderNbr,
407 TrueRoute = CASE ISNULL(soo.IsValidDate, 0)
408 WHEN 1
409 THEN
410 'True'
411 ELSE
412 'False'
413 END,
414 soo.InvoiceNbr,
415 soo.InvoiceDate,
416 soo.TranAmt NetAmt,
417 soo.DiscAmt FreeAmt,
418 OrderAmt = soo.OrderQty * soo.UnitPrice,
419 rc.[Desc] ReasonCodeDesc,
420 IsManual = CASE ISNULL(soo.ManualDisc, 0)
421 WHEN 1
422 THEN
423 'True'
424 ELSE
425 'False'
426 END,
427 ShipmentNbr = NULL,
428 ShipDate = NULL,
429 SOOrderType = soo.OrderType,
430 CompletedDate = soo.LastModifiedDateTime,
431 soo.IsDispose
432 FROM
433 THMilkBaseline2..DMSBLRawSalesHistory soo WITH (NOLOCK)
434 INNER JOIN
435 #SOOrder so
436 ON so.CompanyID = soo.CompanyID
437 AND so.OrderNbr = soo.OrderNbr
438 AND so.OrderType = soo.OrderType
439 LEFT JOIN
440 dbo.DMSRoute r WITH (NOLOCK)
441 ON r.CompanyID = soo.CompanyID
442 AND r.RouteCD = soo.Route
443 LEFT JOIN
444 dbo.DMSSOReasonCode rc WITH (NOLOCK)
445 ON rc.CompanyID = so.CompanyID
446 AND rc.ReasonCodeID = so.UsrReasonCodeID
447 WHERE
448 soo.CompanyID = @_CompanyID
449 AND soo.OrderDate
450 BETWEEN @_FromDate AND @_ToDate
451 AND EXISTS
452 (
453 SELECT
454 *
455 FROM
456 #tmpSiteAccess sa
457 WHERE
458 sa.CompanyID = so.CompanyID
459 AND sa.SiteID = soo.SiteID
460 )
461 AND
462 (
463 @_TerritoryType = 'X'
464 OR EXISTS
465 (
466 SELECT TOP 1
467 *
468 FROM
469 #tmpPrmRoute pr
470 WHERE
471 pr.RouteCD = soo.Route
472 )
473 ); ---- Kiểm tra permission với working position là SS
474CREATE NONCLUSTERED INDEX [#tmpAllOrder_index]
475 ON #tmpAllOrder (CompanyID, OrderNbr, OrderType, OrderLineNbr);
476--Hieu : Them index cho bang tam de tang toc select do bang tam nay rat lon
477CREATE NONCLUSTERED INDEX #idx_AllOrder_select
478 ON #tmpAllOrder ([CompanyID], [DistributorID], [CustomerID], [CustomerLocationID])
479 INCLUDE ([OrderDate]);
480DROP TABLE #tmpPrmRoute
481DROP TABLE #tmpSiteAccess
482
483UPDATE
484 ao
485SET
486 ao.DistributorLocationID = vsor.DistributorLocationID,
487 ao.SalesForceID = vsor.SalesForceID
488FROM
489 #tmpAllOrder ao
490 INNER JOIN
491 dbo.DMSViewSORoute vsor WITH (NOLOCK)
492 ON vsor.CompanyID = ao.CompanyID
493 AND vsor.CustomerID = ao.CustomerID
494 AND vsor.CustomerLocationID = ao.CustomerLocationID
495 AND ao.OrderDate
496 BETWEEN vsor.EffectiveDate AND vsor.EndDate
497 AND ao.DistributorID = vsor.DistributorID;
498
499
500SELECT
501 ir.CompanyID,
502 ir.OrderNbr,
503 ir.OrderType,
504 ir.FreeInventoryID,
505 ir.Qty,
506 p.ProgramCD,
507 p.Descr ProgramName,
508 p.IOCode
509INTO
510 #tmpIncentive
511FROM
512 DMSIncentiveReceived ir WITH (NOLOCK)
513 INNER JOIN
514 #SOOrder so
515 ON so.CompanyID = ir.CompanyID
516 AND so.OrderNbr = ir.OrderNbr
517 AND so.OrderType = ir.OrderType
518 INNER JOIN
519 dbo.DMSProgram p WITH (NOLOCK)
520 ON p.CompanyID = ir.CompanyID
521 AND p.ProgramID = ir.ProgramID;
522
523CREATE NONCLUSTERED INDEX [#tmpIncentive_index]
524 ON #tmpIncentive (CompanyID, OrderNbr, OrderType);
525
526DROP TABLE #SOOrder
527
528---- Lấy raw data
529SELECT
530 ao.CompanyID,
531 ao.DistributorID,
532 ao.DistributorLocationID,
533 ao.OrderNbr,
534 ao.OrderType,
535 ao.OrderDate,
536 ao.CustomerOrderNbr,
537 ao.CustomerID,
538 ao.CustomerLocationID,
539 ao.SalesRepsID,
540 ao.Route,
541 ao.RouteName,
542 ao.InventoryID,
543 ao.ShipmentNbr,
544 ao.ShipDate,
545 UnitPrice = ao.UnitPrice,
546 SKU = CONVERT(DECIMAL(18, 5), 1),
547 TotalOrder = CONVERT(DECIMAL(18, 5), NULL),
548 OrderQty = ao.OrderQty * (CASE
549 WHEN ISNULL(ao.OrderType, '') != 'CM'
550 THEN
551 1
552 ELSE
553 -1
554 END
555 ),
556 OpenQty = 0,
557 ShippingQty = CASE
558 WHEN ISNULL(ao.OrderType, '') != 'CM'
559 AND ao.IsFree = 0
560 AND ao.ShippingQty IS NULL
561 THEN
562 ao.ShippingQty
563 ELSE
564 0
565 END,
566 ShippingPromotionQty = CASE
567 WHEN ISNULL(ao.OrderType, '') != 'CM'
568 AND ao.IsFree = 1
569 AND ao.ShippingQty IS NULL
570 THEN
571 ao.ShippingQty
572 ELSE
573 0
574 END,
575 ShippedQty = ao.ShippingQty * (CASE
576 WHEN ISNULL(ao.OrderType, '') != 'CM'
577 THEN
578 1
579 ELSE
580 -1
581 END
582 ),
583 PromotionQty = CASE
584 WHEN ao.IsFree = 1
585 THEN
586 ao.ShippingQty * (CASE
587 WHEN ISNULL(ao.OrderType, '') != 'CM'
588 THEN
589 1
590 ELSE
591 -1
592 END
593 )
594 ELSE
595 0
596 END,
597 ReturnQty = CASE
598 WHEN ISNULL(ao.OrderType, '') = 'CM'
599 THEN
600 ao.OrderQty
601 ELSE
602 0
603 END,
604 OrderAmt = ao.OrderAmt * CASE
605 WHEN ISNULL(ao.OrderType, '') != 'CM'
606 THEN
607 1
608 ELSE
609 -1
610 END,
611 OpenAmt = CASE
612 WHEN ISNULL(ao.OrderType, '') != 'CM'
613 THEN
614 ao.OpenAmt
615 ELSE
616 0
617 END,
618 ShippingAmt = 0,
619 ShippedAmt = ao.ShippingQty * UnitPrice * (CASE
620 WHEN ISNULL(ao.OrderType, '') != 'CM'
621 THEN
622 1
623 ELSE
624 -1
625 END
626 ),
627 PromotionAmt = ao.FreeAmt * (CASE
628 WHEN ISNULL(ao.OrderType, '') != 'CM'
629 THEN
630 1
631 ELSE
632 -1
633 END
634 ),
635 ReturnAmt = CASE
636 WHEN ISNULL(ao.OrderType, '') = 'CM'
637 THEN
638 ao.TranAmt
639 ELSE
640 0
641 END,
642 ao.SiteID,
643 ao.IsManual,
644 ao.IsFree,
645 ao.IsValidDate,
646 ao.IsValidDistance,
647 ao.Status,
648 ao.ExpireDate,
649 ao.SalesForceID,
650 ao.LotSerialNbr,
651 ao.PDAOrderDate,
652 ao.PDAOrderNbr,
653 ao.TrueRoute,
654 EmployeeID = sf.LinkedVendorCode,
655 sf.EmployeeCD,
656 sf.Descr EmployeeName,
657 ss.EmployeeCD SalessupCD,
658 ss.Descr SalessupName,
659 ao.InvoiceNbr,
660 ao.InvoiceDate,
661 FreeAmt = ao.FreeAmt * (CASE
662 WHEN ISNULL(ao.OrderType, '') != 'CM'
663 THEN
664 1
665 ELSE
666 -1
667 END
668 ),
669 NetAmt = ao.NetAmt * (CASE
670 WHEN ISNULL(ao.OrderType, '') != 'CM'
671 THEN
672 1
673 ELSE
674 -1
675 END
676 ),
677 ao.ReasonCodeDesc,
678 i.ProgramCD,
679 i.ProgramName,
680 i.IOCode,
681 ao.OrderLineNbr,
682 ao.CompletedDate,
683 ao.IsDispose
684INTO
685 #tmpRawData
686FROM
687 #tmpAllOrder ao
688 LEFT JOIN
689 DMSSalesForce sf WITH (NOLOCK)
690 ON sf.CompanyID = ao.CompanyID
691 AND sf.EmployeeID = ao.SalesRepsID
692 LEFT JOIN
693 dbo.DMSSalesForce ss WITH (NOLOCK)
694 ON ss.CompanyID = ao.CompanyID
695 AND ss.EmployeeID = ao.SalesForceID
696 LEFT JOIN
697 #tmpIncentive i WITH (NOLOCK)
698 ON i.CompanyID = ao.CompanyID
699 AND i.OrderNbr = ao.OrderNbr
700 AND i.OrderType = ao.OrderType
701 AND
702 (
703 i.FreeInventoryID IS NULL
704 OR
705 (
706 ao.InventoryID = i.FreeInventoryID
707 AND ao.IsFree = 1
708 ))
709
710--CREATE NONCLUSTERED INDEX [#tmpRawData_index]
711-- ON #tmpRawData
712-- (CompanyID, DistributorID, DistributorLocationID, CustomerID, CustomerLocationID, SalesRepsID, InventoryID);
713CREATE NONCLUSTERED INDEX [#tmpRawData_index_update]
714 ON #tmpRawData (CompanyID, DistributorID, OrderDate, SalesRepsID);
715
716--Hieu : Bo cac bang tam ko dung
717 DROP TABLE #tmpAllOrder
718 DROP TABLE #tmpIncentive
719
720UPDATE
721 rd
722SET
723 rd.TotalOrder = CONVERT(DECIMAL(18, 5), 1) / CONVERT(DECIMAL(18, 5), tmp.TotalRecord)
724FROM
725 #tmpRawData rd
726 JOIN
727 (
728 SELECT
729 CompanyID,
730 DistributorID,
731 OrderDate,
732 SalesRepsID,
733 CustomerLocationID,
734 TotalRecord = COUNT(CompanyID)
735 FROM
736 #tmpRawData
737 WHERE
738 OrderType != 'CM'
739 GROUP BY
740 CompanyID,
741 DistributorID,
742 OrderDate,
743 SalesRepsID,
744 CustomerLocationID
745 ) tmp
746 ON tmp.CompanyID = rd.CompanyID
747 AND tmp.DistributorID = rd.DistributorID
748 AND tmp.OrderDate = rd.OrderDate
749 AND tmp.SalesRepsID = rd.SalesRepsID
750 AND tmp.CustomerLocationID = rd.CustomerLocationID
751WHERE
752 rd.OrderType != 'CM';
753
754
755
756SELECT
757 cu.CompanyID,
758 cu.CustomerID,
759 cu.LocationID,
760 CustomerCD,
761 CustomerName,
762 CustomerFullName,
763 Province = cu.ProvinceName,
764 District = cu.DistrictName,
765 cu.CustomerAddress,
766 cu.Attribute0Descr,
767 cu.Attribute1Descr,
768 cu.Attribute2Descr,
769 cu.Attribute3Descr,
770 cu.Attribute4Descr,
771 cu.Attribute5Descr,
772 cu.Attribute6Descr,
773 cu.Attribute7Descr,
774 cu.Attribute8Descr,
775 cu.Attribute9Descr,
776 cu.AddressLine1,
777 cu.AddressLine2,
778 cu.AddressLine3,
779 cu.AddressLine4,
780 cu.AddressLine5,
781 cu.WardName,
782 sc.SellingCategoryCD,
783 cu.CountryName Country
784INTO
785 #tmpCustomerLocation
786FROM
787 dbo.DMSViewCustomerLocation cu
788 LEFT JOIN
789 dbo.DMSSellingCategory sc
790 ON sc.CompanyID = cu.CompanyID
791 AND sc.SellingCategoryID = cu.SellingCategoryID
792WHERE
793 cu.CompanyID = @_CompanyID
794 AND
795 (
796 @_ProvinceID IS NULL
797 OR cu.ProvinceID = @_ProvinceID
798 )
799 AND
800 (
801 @_ChannelID IS NULL
802 OR cu.Attribute0ID = @_ChannelID
803 );
804
805SELECT
806 vii.CompanyID,
807 vii.InventoryID,
808 vii.InventoryCD,
809 vii.InventoryName,
810 vii.InventoryShortName,
811 vii.InventoryAbbrName,
812 vii.BaseUnit,
813 vii.PurchaseUnit,
814 vii.Hierachy0Descr,
815 vii.Hierachy1Descr,
816 vii.Hierachy2Descr,
817 vii.Hierachy3Descr,
818 vii.Hierachy4Descr,
819 vii.Hierachy5Descr,
820 vii.Hierachy6Descr,
821 vii.Hierachy7Descr,
822 vii.Hierachy8Descr,
823 vii.Hierachy9Descr,
824 INAttribute0Descr = vii.Attribute0Descr,
825 INAttribute1Descr = vii.Attribute1Descr,
826 INAttribute2Descr = vii.Attribute2Descr,
827 INAttribute3Descr = vii.Attribute3Descr,
828 INAttribute4Descr = vii.Attribute4Descr,
829 INAttribute5Descr = vii.Attribute5Descr,
830 INAttribute6Descr = vii.Attribute6Descr,
831 INAttribute7Descr = vii.Attribute7Descr,
832 INAttribute8Descr = vii.Attribute8Descr,
833 INAttribute9Descr = vii.Attribute9Descr,
834 UnitRate = CASE
835 WHEN
836 (
837 iu.UnitRate > 1
838 AND iu.UnitMultDiv = 'M'
839 )
840 THEN
841 iu.UnitRate
842 ELSE
843 0
844 END
845INTO
846 #tmpInventoryItem
847FROM
848 dbo.DMSViewInventoryItem vii WITH (NOLOCK)
849 LEFT JOIN
850 INUnit iu WITH (NOLOCK)
851 ON iu.CompanyID = vii.CompanyID
852 AND iu.InventoryID = vii.InventoryID
853 AND vii.BaseUnit = iu.ToUnit
854 AND iu.FromUnit = vii.PurchaseUnit
855WHERE
856 vii.CompanyID = @_CompanyID
857 AND
858 (
859 @_InventoryID IS NULL
860 OR vii.InventoryID = @_InventoryID
861 )
862 AND
863 (
864 @_HierarchyID IS NULL
865 OR vii.Hierachy0ID = @_HierarchyID
866 OR vii.Hierachy1ID = @_HierarchyID
867 OR vii.Hierachy2ID = @_HierarchyID
868 OR vii.Hierachy3ID = @_HierarchyID
869 OR vii.Hierachy4ID = @_HierarchyID
870 OR vii.Hierachy5ID = @_HierarchyID
871 OR vii.Hierachy6ID = @_HierarchyID
872 OR vii.Hierachy7ID = @_HierarchyID
873 OR vii.Hierachy8ID = @_HierarchyID
874 OR vii.Hierachy9ID = @_HierarchyID
875 );
876
877--DECLARE @_CompanyID INT = 3
878SELECT
879 d.SalesOrg_0_ValueCD SalesOrg_0_ValueName,
880 d.SalesOrg_1_ValueCD SalesOrg_1_ValueName,
881 d.SalesOrg_2_ValueCD SalesOrg_2_ValueName,
882 d.SalesOrg_3_ValueCD SalesOrg_3_ValueName,
883 d.SalesOrg_4_ValueCD SalesOrg_4_ValueName,
884 d.SalesOrg_5_ValueCD SalesOrg_5_ValueName,
885 cl.Province,
886 d.DistributorCD,
887 d.DistributorName,
888 rd.Route,
889 rd.RouteName,
890 rd.TrueRoute,
891 VisitDate = dbo.fn_DMS_TranslateUnit( @_CompanyID, CASE DATEPART(dw, rd.PDAOrderDate)
892 WHEN 2
893 THEN
894 'Monday'
895 WHEN 3
896 THEN
897 'Tuesday'
898 WHEN 4
899 THEN
900 'Wednesday'
901 WHEN 5
902 THEN
903 'Thursday'
904 WHEN 6
905 THEN
906 'Friday'
907 WHEN 7
908 THEN
909 'Saturday'
910 WHEN 1
911 THEN
912 'Sunday'
913 END
914 ),
915 EmployeeCD = rd.EmployeeCD,
916 EmployeeName = rd.EmployeeName,
917 SalessupCD = rd.SalessupCD,
918 SalessupName = rd.SalessupName,
919 cl.CustomerCD,
920 cl.CustomerName,
921 cl.AddressLine1,
922 cl.AddressLine2,
923 cl.AddressLine4,
924 cl.District,
925 cl.Attribute2Descr,
926 cl.Attribute4Descr,
927 ii.INAttribute1Descr,
928 ii.INAttribute2Descr,
929 ii.INAttribute3Descr,
930 ii.INAttribute4Descr,
931 ii.INAttribute5Descr,
932 ii.INAttribute6Descr,
933 ii.INAttribute7Descr,
934 ii.INAttribute8Descr,
935 ii.INAttribute9Descr,
936 ii.InventoryCD,
937 ii.InventoryShortName,
938 CONVERT(INT, ii.UnitRate) ItemClass,
939 BaseUnit = dbo.fn_DMS_TranslateUnit(@_CompanyID, ii.BaseUnit),
940 OrderType = dbo.fn_DMS_TranslateUnit(@_CompanyID, rd.OrderType),
941 rd.PDAOrderDate,
942 rd.OrderNbr,
943 rd.OrderDate,
944 rd.OrderQty,
945 rd.ShippedQty,
946 rd.LotSerialNbr,
947 rd.ExpireDate,
948 rd.InvoiceNbr,
949 rd.InvoiceDate,
950 IsFree,
951 rd.OrderAmt,
952 rd.PromotionAmt,
953 rd.NetAmt,
954 rd.ReasonCodeDesc,
955 IsReturn = CASE
956 WHEN rd.OrderType = 'CM'
957 OR cm.OrigOrderNbr IS NOT NULL
958 THEN
959 'True'
960 ELSE
961 'False'
962 END,
963 rd.CustomerOrderNbr PDAOrderNbr,
964 rd.IsManual,
965 CompleteDate = rd.CompletedDate,
966 rd.ProgramCD,
967 rd.ProgramName,
968 rd.IOCode,
969 rd.UnitPrice,
970 rd.ShippedAmt,
971 rd.ReturnQty,
972 rd.ReturnAmt,
973 OrderNoPromotionAmt = rd.NetAmt,
974 rd.OrderLineNbr LineNbr,
975 rd.EmployeeID
976FROM
977 #tmpRawData rd
978 INNER JOIN
979 #tmpCustomerLocation cl WITH (NOLOCK)
980 ON cl.CompanyID = rd.CompanyID
981 AND cl.CustomerID = rd.CustomerID
982 AND cl.LocationID = rd.CustomerLocationID
983 Inner JOIN
984 #tmpInventoryItem ii WITH (NOLOCK)
985 ON ii.CompanyID = rd.CompanyID
986 AND ii.InventoryID = rd.InventoryID
987 INNER JOIN
988 #tmpDistributor d WITH (NOLOCK)
989 ON d.CompanyID = rd.CompanyID
990 AND d.DistributorID = rd.DistributorID
991
992 LEFT JOIN
993 #tmpCompletedCM cm
994 ON cm.CompanyID = rd.CompanyID
995 --AND cm.OrigOrderType = rd.OrderType
996 AND cm.OrigOrderNbr = rd.OrderNbr
997ORDER BY
998 rd.DistributorID;
999 DROP TABLE #tmpCompletedCM
1000 DROP TABLE #tmpCustomerLocation
1001 DROP TABLE #tmpDistributor
1002 DROP TABLE #tmpInventoryItem
1003 DROP TABLE #tmpRawData
1004END;