· 6 years ago · Oct 02, 2019, 02:46 AM
1USE [MRCDMS]
2GO
3
4/****** Object: StoredProcedure [dbo].[sp_DMS_RPT_SOSalesRealtime] Script Date: 10/2/2019 9:42:35 AM ******/
5SET ANSI_NULLS ON
6GO
7
8SET QUOTED_IDENTIFIER ON
9GO
10
11---- EXEC dbo.sp_DMS_RPT_SOSalesRealtime 3, 291, '2018/05/01','2018/05/31','sysadmin','ACHO', null
12ALTER PROCEDURE [dbo].[sp_DMS_RPT_SOSalesRealtime]
13 @CompanyID INT = NULL,
14 @DistributorID INT = NULL,
15 @FromDate DATETIME = NULL,
16 @ToDate DATETIME = NULL,
17 @LoginID VARCHAR(50) = NULL,
18 @Role VARCHAR(50) = NULL,
19 @SiteID INT,
20 @SalesAreaID INT = NULL,
21 @TypeIN INT = NULL,
22 @AttInventoryID INT = NULL,
23 @InventoryID INT = NULL,
24 @TypeCU INT = NULL,
25 @AttCustomerID INT = NULL,
26 @CountryID VARCHAR(2) = NULL,
27 @ProvinceID INT = NULL,
28 @DistrictID INT = NULL,
29 @SourceType VARCHAR(3) = NULL
30AS
31BEGIN
32 SET NOCOUNT ON;
33 SET FMTONLY OFF;
34 SET NO_BROWSETABLE OFF;
35
36 DECLARE @_CompanyID INT = NULLIF(@CompanyID, 0);
37 DECLARE @_DistributorID INT = NULLIF(@DistributorID, 0);
38 DECLARE @_FromDate DATETIME = @FromDate;
39 DECLARE @_ToDate DATETIME = @ToDate;
40 DECLARE @_LoginID VARCHAR(50) = @LoginID;
41 DECLARE @_SiteID INT = NULLIF(@SiteID, 0);
42 DECLARE @_SalesAreaID INT = NULLIF(@SalesAreaID, 0);
43 DECLARE @_TypeIN INT = ISNULL(@TypeIN, -1);
44 DECLARE @_AttInventoryID INT = NULLIF(@AttInventoryID, 0);
45 DECLARE @_InventoryID INT = NULLIF(@InventoryID, 0);
46 DECLARE @_TypeCU INT = ISNULL(@TypeCU, -1);
47 DECLARE @_CountryID VARCHAR(2) = @CountryID;
48 DECLARE @_ProvinceID INT = NULLIF(@ProvinceID, 0);
49 DECLARE @_DistrictID INT = NULLIF(@DistrictID, 0);
50 DECLARE @_AttCustomerID INT = NULLIF(@AttCustomerID, 0);
51 DECLARE @_Role VARCHAR(50) = NULLIF(LTRIM(RTRIM(@Role)), '');
52 DECLARE @_TerritoryType CHAR(1);
53 DECLARE @_SourceType VARCHAR(3) = @SourceType;
54 DECLARE @_CurrentDate DATETIME = CONVERT(DATE, GETDATE());
55
56 SELECT CompanyID,
57 SiteID
58 INTO #tmpSiteAccess
59 FROM dbo.DMSViewSiteByUser
60 WHERE CompanyID = @_CompanyID
61 AND Username = @_LoginID;
62
63 CREATE TABLE #tmp
64 (
65 CompanyID INT,
66 DistributorID INT,
67 DistributorLocationID INT,
68 SalesOrgID INT,
69 SalesOrg_0_ValueID INT,
70 SalesOrg_0_ValueCD VARCHAR(15),
71 SalesOrg_0_ValueName NVARCHAR(50),
72 SalesForce_0_Name NVARCHAR(50),
73 SalesOrg_1_ValueID INT,
74 SalesOrg_1_ValueCD VARCHAR(15),
75 SalesOrg_1_ValueName NVARCHAR(50),
76 SalesForce_1_Name NVARCHAR(50),
77 SalesOrg_2_ValueID INT,
78 SalesOrg_2_ValueCD VARCHAR(15),
79 SalesOrg_2_ValueName NVARCHAR(50),
80 SalesForce_2_Name NVARCHAR(50),
81 SalesOrg_3_ValueID INT,
82 SalesOrg_3_ValueCD VARCHAR(15),
83 SalesOrg_3_ValueName NVARCHAR(50),
84 SalesForce_3_Name NVARCHAR(50),
85 SalesOrg_4_ValueID INT,
86 SalesOrg_4_ValueCD VARCHAR(15),
87 SalesOrg_4_ValueName NVARCHAR(50),
88 SalesForce_4_Name NVARCHAR(50),
89 SalesOrg_5_ValueID INT,
90 SalesOrg_5_ValueCD VARCHAR(15),
91 SalesOrg_5_ValueName NVARCHAR(50),
92 SalesForce_5_Name NVARCHAR(50),
93 SalesOrg_6_ValueID INT,
94 SalesOrg_6_ValueCD VARCHAR(15),
95 SalesOrg_6_ValueName NVARCHAR(50),
96 SalesForce_6_Name NVARCHAR(50),
97 SalesOrg_7_ValueID INT,
98 SalesOrg_7_ValueCD VARCHAR(15),
99 SalesOrg_7_ValueName NVARCHAR(50),
100 SalesForce_7_Name NVARCHAR(50),
101 SalesOrg_8_ValueID INT,
102 SalesOrg_8_ValueCD VARCHAR(15),
103 SalesOrg_8_ValueName NVARCHAR(50),
104 SalesForce_8_Name NVARCHAR(50),
105 SalesOrg_9_ValueID INT,
106 SalesOrg_9_ValueCD VARCHAR(15),
107 SalesOrg_9_ValueName NVARCHAR(50),
108 SalesForce_9_Name NVARCHAR(50),
109 ParentID INT,
110 ParentLevel INT
111 );
112
113 ---- Lấy thông tin permission theo user đăng nhập
114 SELECT *
115 INTO #tmpPermission
116 FROM dbo.fn_DMS_GetSFPermission(@_CompanyID, @_DistributorID, @_LoginID);
117
118 ---- Lấy working position của user đăng nhập
119 ---- Đấy là báo cáo NPP nên nếu territory type khác SS thì set kiểu view 1 NPP
120 SELECT @_TerritoryType =
121 (
122 SELECT TOP 1 TerritoryType FROM #tmpPermission
123 );
124 IF @_TerritoryType != 'D'
125 OR @_TerritoryType IS NULL
126 SET @_TerritoryType = 'X';
127
128 ---- Group by các dữ liệu permission
129 SELECT RouteCD
130 INTO #tmpPrmRoute
131 FROM #tmpPermission
132 GROUP BY RouteCD;
133
134 ---- Lấy thông tin NPP với cây sales org tương ứng
135 INSERT INTO #tmp
136 EXEC dbo.sp_DMS_MDM_GetDistributorWithSalesOrg @_CompanyID;
137
138 ---- Lấy VAT của từng sản phẩm
139 SELECT tax.CompanyID,
140 inv.InventoryID,
141 tax.TaxRate,
142 inv.SalesUnit
143 INTO #tmpTax
144 FROM TaxRev tax
145 INNER JOIN dbo.TaxCategoryDet det
146 ON det.CompanyID = tax.CompanyID
147 AND det.TaxID = tax.TaxID
148 INNER JOIN dbo.TaxCategory taxCat
149 ON taxCat.CompanyID = det.CompanyID
150 AND taxCat.TaxCategoryID = det.TaxCategoryID
151 INNER JOIN dbo.InventoryItem inv
152 ON inv.CompanyID = taxCat.CompanyID
153 AND inv.TaxCategoryID = taxCat.TaxCategoryID
154 WHERE tax.CompanyID = @_CompanyID
155 AND tax.TaxRate != 0;
156
157 -- Lay ra don CM de tru doanh so cho don goc co IsDispose = 1
158
159 SELECT soo.*
160 INTO #tmpOrigin
161 FROM SOOrder soo WITH (NOLOCK)
162 WHERE soo.UsrIsDispose = CAST(1 AS BIT)
163 AND soo.OrderType != 'CM'
164 AND OrderDate
165 BETWEEN @_FromDate AND @_ToDate
166 AND CompanyID = @_CompanyID;
167
168 SELECT cm.*
169 INTO #tmpCM
170 FROM SOOrder cm WITH (NOLOCK)
171 INNER JOIN #tmpOrigin soo
172 ON cm.CompanyID = soo.CompanyID
173 AND cm.UsrOriginDoc = soo.OrderNbr
174 AND cm.UsrOriginType = soo.OrderType
175 WHERE cm.OrderType = 'CM'
176 AND cm.Completed = CAST(1 AS BIT)
177 AND cm.Status = 'C';
178
179 ---- Lấy thông tin đơn hàng
180 SELECT soo.CompanyID,
181 DistributorID = soo.BranchID,
182 DistributorLocationID = NULL,
183 OrderID = COALESCE(soo.UsrCustomerOrderNbr, soo.CustomerOrderNbr),
184 OrderDate = CONVERT(DATE, sol.RequestDate),
185 BillID = soo.OrderNbr,
186 BillType = soo.OrderType,
187 BiLLDate = soo.OrderDate,
188 Beat = DATENAME(dw, soo.OrderDate),
189 soo.CustomerOrderNbr,
190 soo.CustomerID,
191 soo.CustomerLocationID,
192 SalesRepsID = soo.UsrSalesReps,
193 Route = soo.UsrRoute,
194 sol.InventoryID,
195 UnitPriceBeforeTax = ROUND(sol.UnitPrice / (1 + (tax.TaxRate / 100)), 3),
196 sol.UnitPrice,
197 sol.LineNbr,
198 OrderQty = sol.OrderQty,
199 OpenQty = sol.OpenQty,
200 ShippingQty = sol.BaseShippedQty,
201 OrderBeforeTax = ROUND(sol.UnitPrice / (1 + (tax.TaxRate / 100)), 3) * sol.OrderQty,
202 OrderAmt = sol.OrderQty * sol.UnitPrice,
203 OrderPayment = sol.OrderQty * sol.UnitPrice - ISNULL(sol.DiscAmt, 0),
204 InvoiceAmt = NULL,
205 InvoiceDate = NULL,
206 sol.OpenAmt,
207 sol.LineAmt,
208 sol.DiscAmt,
209 sol.IsFree,
210 IsDelete = NULL,
211 Status = soo.Status,
212 soo.UsrReasonCodeID,
213 sol.UOM,
214 soo.Completed,
215 soo.UsrValidDate,
216 soo.UsrSourceType,
217 soo.UsrIsDispose,
218 soo.UsrIsRevenueDeduction,
219 sol.SiteID,
220 sol.UsrPromotionID
221 INTO #tmpSOOrderInfo
222 FROM dbo.SOOrder soo WITH (NOLOCK)
223 JOIN dbo.SOLine sol WITH (NOLOCK)
224 ON sol.CompanyID = soo.CompanyID
225 AND sol.OrderNbr = soo.OrderNbr
226 AND sol.OrderType = soo.OrderType
227 LEFT JOIN #tmpTax tax
228 ON tax.CompanyID = sol.CompanyID
229 AND tax.InventoryID = sol.InventoryID
230 WHERE soo.CompanyID = @_CompanyID
231 AND
232 (
233 soo.BranchID = @_DistributorID
234 OR soo.BranchID IN (
235 SELECT DistributorID FROM #tmpPermission
236 )
237 )
238 AND soo.OrderDate
239 BETWEEN @_FromDate AND @_ToDate
240 AND
241 (
242 @_SiteID IS NULL
243 OR sol.SiteID = @_SiteID
244 )
245 AND EXISTS
246 (
247 SELECT *
248 FROM #tmpSiteAccess sa
249 WHERE sa.CompanyID = sol.CompanyID
250 AND sa.SiteID = sol.SiteID
251 )
252 AND
253 (
254 @_TerritoryType = 'X'
255 OR EXISTS
256 (
257 SELECT TOP 1 * FROM #tmpPrmRoute pr WHERE pr.RouteCD = soo.UsrRoute
258 )
259 )
260 AND
261 (
262 @_SourceType = 'ALL'
263 OR soo.UsrSourceType = @_SourceType
264 )
265 AND (soo.OrderNbr NOT IN (
266 SELECT OrderNbr FROM #tmpCM
267 )
268 )
269 AND (soo.OrderNbr NOT IN (
270 SELECT UsrOriginDoc FROM #tmpCM
271 )
272 );
273
274 ---- 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
275 SELECT *
276 INTO #tmpAllOrder
277 FROM
278 (
279 SELECT soo.*,
280 OriginalQty = pdaline.OrderQty,
281 OriginalAmt = pdaline.OrderQty * pdaline.SalesPrice,
282 PDAIsFree = pdaline.IsFree,
283 SalesAreaID = 0
284 FROM #tmpSOOrderInfo soo
285 LEFT JOIN dbo.DMSPDAOrderLine pdaline WITH (NOLOCK)
286 ON pdaline.CompanyID = soo.CompanyID
287 AND pdaline.BranchID = soo.DistributorID
288 AND pdaline.OrderNbr = soo.OrderID
289 AND pdaline.InventoryID = soo.InventoryID
290 AND pdaline.IsFree = soo.IsFree
291 WHERE ISNULL(soo.IsFree, 0) = 0
292 UNION ALL
293 SELECT soo.*,
294 OriginalQty = pdaline.OrderQty,
295 OriginalAmt = pdaline.OrderQty * pdaline.SalesPrice,
296 PDAIsFree = pdaline.IsFree,
297 SalesAreaID = 0
298 FROM #tmpSOOrderInfo soo
299 LEFT JOIN dbo.DMSPDAOrderLine pdaline WITH (NOLOCK)
300 ON pdaline.CompanyID = soo.CompanyID
301 AND pdaline.BranchID = soo.DistributorID
302 AND pdaline.OrderNbr = soo.OrderID
303 AND pdaline.InventoryID = soo.InventoryID
304 AND pdaline.PromotionID = soo.UsrPromotionID
305 WHERE ISNULL(soo.IsFree, 0) = 1
306 ) tmp;
307
308 CREATE NONCLUSTERED INDEX [#tmpAllOrder_index]
309 ON #tmpAllOrder
310 (
311 CompanyID,
312 BillID,
313 BillType,
314 LineNbr
315 );
316 CREATE NONCLUSTERED INDEX [#tmpAllOrder_index_delete]
317 ON #tmpAllOrder (IsDelete);
318
319 DELETE FROM #tmpAllOrder
320 WHERE IsDelete = 1;
321
322 UPDATE ao
323 SET ao.DistributorLocationID = vsor.DistributorLocationID,
324 ao.SalesAreaID = vsor.SalesAreaID
325 FROM #tmpAllOrder ao
326 JOIN dbo.DMSViewSORoute vsor WITH (NOLOCK)
327 ON vsor.CompanyID = ao.CompanyID
328 AND vsor.DistributorID = ao.DistributorID
329 AND vsor.CustomerID = ao.CustomerID
330 AND vsor.CustomerLocationID = ao.CustomerLocationID
331 AND ao.BiLLDate
332 BETWEEN vsor.EffectiveDate AND vsor.EndDate;
333
334 DELETE FROM #tmpAllOrder
335 WHERE SalesAreaID IS NOT NULL
336 AND SalesAreaID != 0
337 AND SalesAreaID NOT IN (
338 SELECT SalesAreaID FROM #tmpPermission
339 );
340
341 UPDATE rd
342 SET rd.DistributorLocationID = dis.LocationID,
343 rd.SalesAreaID = dis.SalesAreaID
344 FROM #tmpAllOrder rd
345 JOIN DMSViewDistributorLocation dis WITH (NOLOCK)
346 ON rd.DistributorID = dis.DistributorID
347 AND rd.CompanyID = dis.CompanyID
348 WHERE rd.DistributorLocationID IS NULL;
349
350 ---- Lấy raw data
351 SELECT ao.CompanyID,
352 ao.DistributorID,
353 ao.DistributorLocationID,
354 ao.BillID,
355 ao.BiLLDate,
356 ao.BillType,
357 ao.Beat,
358 ao.OrderID,
359 ao.OrderDate,
360 ao.CustomerOrderNbr,
361 ao.CustomerID,
362 ao.CustomerLocationID,
363 ao.SalesRepsID,
364 ao.Route,
365 ao.InventoryID,
366 UOM = intr.UOM,
367 UnitPrice = (CASE
368 WHEN ao.BillType != 'CM' THEN
369 MAX(ao.UnitPrice)
370 WHEN ao.BillType = 'CM' THEN
371 MAX(ao.UnitPrice) * -1
372 END
373 ),
374 OriginalQty = SUM( CASE
375 WHEN ao.BillType != 'CM'
376 AND ao.PDAIsFree = 0 THEN
377 ao.OriginalQty
378 ELSE
379 0
380 END
381 ),
382 OrderQty = SUM( CASE
383 WHEN ao.BillType != 'CM'
384 AND ao.IsFree = 0 THEN
385 ao.OrderQty
386 WHEN ao.BillType = 'CM'
387 AND ao.IsFree = 0 THEN
388 ao.OrderQty * -1
389 END
390 ),
391 OpenQty = SUM( CASE
392 WHEN ao.BillType != 'CM' THEN
393 ao.OpenQty
394 ELSE
395 0
396 END
397 ),
398 ShippingQty = SUM( CASE
399 WHEN ao.IsFree = 0
400 AND ISNULL(intr.Released, 0) = 0 THEN
401 ao.OpenQty
402 ELSE
403 0
404 END
405 ),
406 ShippingPromotionQty = SUM( CASE
407 WHEN ao.BillType != 'CM'
408 AND ao.IsFree = 1
409 AND ISNULL(intr.Released, 0) = 0 THEN
410 ao.ShippingQty
411 ELSE
412 0
413 END
414 ),
415 ShippedQty = SUM( CASE
416 WHEN (
417 intr.Released = 1
418 OR ao.LineNbr = -1
419 )
420 AND ao.IsFree = 0
421 AND
422 (
423 intr.BaseQty > 0
424 OR intr.BaseQty IS NULL
425 )
426 AND ao.BillType != 'CM' THEN
427 intr.Qty
428 WHEN ao.BillType = 'CM'
429 AND intr.Released = 1
430 AND ao.IsFree = 0 THEN
431 intr.Qty * -1
432 END
433 ),
434 PromotionQty = SUM( CASE
435 WHEN ao.IsFree = 1 THEN
436 ao.OrderQty * (CASE
437 WHEN ao.BillType != 'CM' THEN
438 1
439 ELSE
440 -1
441 END
442 )
443 ELSE
444 0
445 END
446 ),
447 ReturnQty = SUM( CASE
448 WHEN ao.BillType = 'CM' THEN
449 ao.OrderQty
450 ELSE
451 0
452 END
453 ),
454 OrderBeforeTax = SUM( CASE
455 WHEN ao.BillType != 'CM' THEN
456 ao.OrderBeforeTax
457 WHEN ao.BillType = 'CM' THEN
458 ao.OrderBeforeTax * -1
459 END
460 ),
461 OriginalAmt = SUM( CASE
462 WHEN ao.BillType != 'CM' THEN
463 ao.OriginalAmt
464 ELSE
465 0
466 END
467 ),
468 OrderAmt = SUM( CASE
469 WHEN ao.BillType != 'CM' THEN
470 ao.OrderAmt
471 WHEN ao.BillType = 'CM' THEN
472 ao.OrderAmt * -1
473 END
474 ),
475 OrderPayment = SUM( CASE
476 WHEN ao.BillType != 'CM' THEN
477 ao.OrderPayment
478 WHEN ao.BillType = 'CM' THEN
479 ao.OrderPayment * -1
480 END
481 ),
482 InvoiceAmt = SUM( CASE
483 WHEN ao.IsFree = 0
484 AND
485 (
486 intr.Released = 1
487 OR ao.LineNbr = -1
488 )
489 AND
490 (
491 intr.BaseQty > 0
492 OR intr.BaseQty IS NULL
493 ) THEN
494 intr.TranAmt * (CASE
495 WHEN ao.BillType != 'CM' THEN
496 1
497 ELSE
498 -1
499 END
500 )
501 ELSE
502 0
503 END
504 ),
505 InvoiceDate = CASE
506 WHEN (
507 intr.Released = 1
508 OR ao.LineNbr = -1
509 )
510 AND ao.BillType != 'CM' THEN
511 ao.BiLLDate
512 END,
513 OpenAmt = SUM( CASE
514 WHEN ao.BillType != 'CM' THEN
515 ao.OpenAmt
516 ELSE
517 0
518 END
519 ),
520 ShippingAmt = SUM( CASE
521 WHEN ao.IsFree = 0
522 AND ISNULL(intr.Released, 0) = 0 THEN
523 ao.OrderAmt
524 ELSE
525 0
526 END
527 ),
528 ShippedAmt = SUM( CASE
529 WHEN ao.IsFree = 0
530 AND
531 (
532 intr.Released = 1
533 OR ao.LineNbr = -1
534 ) THEN
535 intr.Qty * intr.UnitPrice * (CASE
536 WHEN ao.BillType != 'CM' THEN
537 1
538 ELSE
539 -1
540 END
541 )
542 ELSE
543 0
544 END
545 ),
546 PromotionAmt = SUM( ao.DiscAmt * (CASE
547 WHEN ao.BillType != 'CM' THEN
548 1
549 WHEN ao.BillType = 'CM' THEN
550 -1
551 END
552 )
553 ),
554 PromotionShippedQty = SUM( CASE
555 WHEN (
556 intr.Released = 1
557 OR ao.LineNbr = -1
558 )
559 AND ao.IsFree = 1
560 AND
561 (
562 intr.BaseQty > 0
563 OR intr.BaseQty IS NULL
564 )
565 AND ao.BillType != 'CM' THEN
566 intr.Qty
567 WHEN ao.BillType = 'CM'
568 AND intr.Released = 1
569 AND ao.IsFree = 1 THEN
570 intr.Qty * -1
571 END
572 ),
573 ReturnAmt = SUM( CASE
574 WHEN ao.BillType = 'CM' THEN
575 ao.OrderAmt
576 ELSE
577 0
578 END
579 ),
580 Status = CASE
581 WHEN ao.OrderID IS NOT NULL
582 AND ao.BillID IS NULL
583 AND ao.BillType != 'CM' THEN
584 'Waiting for Confirm'
585 WHEN (
586 ao.Status = 'N'
587 OR ao.Status = 'I'
588 )
589 AND ao.UsrReasonCodeID IS NULL
590 AND ao.BillType != 'CM' THEN
591 'Waiting for Delivering'
592 WHEN (
593 ao.Status = 'C'
594 OR ao.Completed = CAST(1 AS BIT)
595 )
596 AND ao.BillType != 'CM' THEN
597 'Delivered'
598 WHEN ao.Status = 'L'
599 --AND ao.BillType != 'CM' -- Thu.Nguyen 2019-07-04: 54896 - Đơn hàng CM trạng thái Cancel bị trạng thái Blank trên báo cáo bán hàng
600 THEN
601 'Cancel'
602 WHEN ao.CustomerOrderNbr IS NOT NULL
603 AND ao.UsrReasonCodeID IS NOT NULL
604 AND
605 (
606 ao.Status = 'N'
607 OR ao.Status = 'I'
608 )
609 AND ao.BillType != 'CM' THEN
610 'Changing'
611 WHEN ao.BillType = 'CM'
612 AND
613 (
614 ao.Status = 'N'
615 OR ao.Status = 'I'
616 ) THEN
617 'Waiting for Returning'
618 WHEN ao.BillType = 'CM'
619 AND
620 (
621 ao.Status = 'C'
622 OR ao.Completed = CAST(1 AS BIT)
623 ) THEN
624 'Returned'
625 END,
626 Reason = reason.[Desc],
627 In_OutRoute = CASE
628 WHEN ao.UsrValidDate = CAST(1 AS BIT) THEN
629 'In Route'
630 WHEN ao.UsrValidDate = CAST(0 AS BIT) THEN
631 'Out Route'
632 END,
633 SourceType = CASE
634 WHEN ao.UsrSourceType = 'MAN' THEN
635 'MANUAL'
636 ELSE
637 ao.UsrSourceType
638 END,
639 IsDispose = ao.UsrIsDispose,
640 IsReturn = ao.UsrIsRevenueDeduction,
641 ao.SalesAreaID
642 INTO #tmpRawData
643 FROM #tmpAllOrder ao
644 LEFT JOIN dbo.INTran intr WITH (NOLOCK)
645 ON intr.CompanyID = ao.CompanyID
646 AND intr.SOOrderNbr = ao.BillID
647 AND intr.SOOrderType = ao.BillType
648 AND intr.SOOrderLineNbr = ao.LineNbr
649 AND intr.Released = 1
650 LEFT JOIN dbo.ARTran art WITH (NOLOCK)
651 ON art.CompanyID = intr.CompanyID
652 AND art.RefNbr = intr.ARRefNbr
653 AND art.TranType = intr.ARDocType
654 AND art.LineNbr = intr.ARLineNbr
655 LEFT JOIN DMSSOReasonCode reason WITH (NOLOCK)
656 ON reason.CompanyID = ao.CompanyID
657 AND reason.ReasonCodeID = ao.UsrReasonCodeID
658 GROUP BY ao.CompanyID,
659 ao.DistributorID,
660 ao.DistributorLocationID,
661 ao.BillID,
662 ao.BillType,
663 ao.BiLLDate,
664 ao.OrderID,
665 ao.OrderDate,
666 ao.CustomerOrderNbr,
667 ao.CustomerID,
668 ao.CustomerLocationID,
669 ao.SalesRepsID,
670 ao.Route,
671 ao.InventoryID,
672 intr.Released,
673 ao.Status,
674 ao.UsrReasonCodeID,
675 intr.UOM,
676 reason.[Desc],
677 ao.Completed,
678 ao.Beat,
679 ao.UsrValidDate,
680 ao.LineNbr,
681 ao.UsrSourceType,
682 ao.UsrIsDispose,
683 ao.UsrIsRevenueDeduction,
684 ao.SalesAreaID;
685
686 CREATE NONCLUSTERED INDEX [#tmpRawData_index]
687 ON #tmpRawData
688 (
689 CompanyID,
690 DistributorID,
691 DistributorLocationID,
692 CustomerID,
693 CustomerLocationID,
694 SalesRepsID,
695 InventoryID
696 );
697 CREATE NONCLUSTERED INDEX [#tmpRawData_index_update]
698 ON #tmpRawData
699 (
700 CompanyID,
701 DistributorID,
702 OrderDate,
703 SalesRepsID
704 );
705
706 SELECT vd.CompanyID,
707 vd.DistributorID,
708 vd.DistributorCD,
709 vd.DistributorName,
710 vd.DistributorFullName
711 INTO #tmpDistributor
712 FROM dbo.DMSViewDistributor vd WITH (NOLOCK);
713
714 CREATE NONCLUSTERED INDEX [#tmpDistributor_index]
715 ON #tmpDistributor
716 (
717 CompanyID,
718 DistributorID
719 );
720 -- select thứ viếng thăm trong tuần của khách hàng
721 CREATE TABLE #tmpMCP
722 (
723 rankRoute INT,
724 CompanyID INT,
725 CustomerID INT,
726 CustomerLocationID INT,
727 DistributorID INT,
728 Beat NVARCHAR(255)
729 );
730
731
732 INSERT INTO #tmpMCP
733 (
734 rankRoute,
735 CompanyID,
736 CustomerID,
737 CustomerLocationID,
738 DistributorID,
739 Beat
740 )
741 SELECT RANK() OVER (PARTITION BY rs.CompanyID,
742 mcp.DistributorID,
743 mcp.CustomerID,
744 mcp.CustomerLocationID
745 ORDER BY rs.EndDate,
746 rs.RouteCD DESC
747 ) rankRoute,
748 rs.CompanyID,
749 mcp.CustomerID,
750 mcp.CustomerLocationID,
751 mcp.DistributorID,
752 Beat = CASE mcp.Monday
753 WHEN 1 THEN
754 N'Thứ Hai, '
755 ELSE
756 ''
757 END + CASE mcp.Tuesday
758 WHEN 1 THEN
759 N'Thứ Ba, '
760 ELSE
761 ''
762 END + CASE mcp.Wednesday
763 WHEN 1 THEN
764 N'Thứ Tư, '
765 ELSE
766 ''
767 END + CASE mcp.Thursday
768 WHEN 1 THEN
769 N'Thứ Năm, '
770 ELSE
771 ''
772 END + CASE mcp.Friday
773 WHEN 1 THEN
774 N'Thứ Sáu, '
775 ELSE
776 ''
777 END + CASE mcp.Saturday
778 WHEN 1 THEN
779 N'Thứ Bảy, '
780 ELSE
781 ''
782 END + CASE mcp.Sunday
783 WHEN 1 THEN
784 N'Chủ Nhật,'
785 ELSE
786 ''
787 END
788 FROM dbo.DMSRouteSetting rs WITH (NOLOCK)
789 JOIN dbo.DMSMCPDetail mcp WITH (NOLOCK)
790 ON mcp.CompanyID = rs.CompanyID
791 AND mcp.RefNbr = rs.RefNbr
792 JOIN dbo.DMSViewDistributorLocation vdl WITH (NOLOCK)
793 ON vdl.CompanyID = mcp.CompanyID
794 AND vdl.DistributorID = mcp.DistributorID
795 AND vdl.LocationID = mcp.DistributorLocationID
796 LEFT JOIN dbo.DMSSalesOrgValue sov WITH (NOLOCK)
797 ON sov.CompanyID = vdl.CompanyID
798 AND sov.ValueID = vdl.SalesAreaID
799 AND sov.DeletedDatabaseRecord = 0
800 LEFT JOIN dbo.DMSSalesForce sf WITH (NOLOCK)
801 ON sf.CompanyID = rs.CompanyID
802 AND sf.EmployeeID = rs.SalespersonID
803 LEFT JOIN dbo.DMSSalesForce ss WITH (NOLOCK)
804 ON ss.CompanyID = rs.CompanyID
805 AND ss.EmployeeID = rs.SalesForceID
806 GROUP BY rs.CompanyID,
807 mcp.CustomerID,
808 mcp.CustomerLocationID,
809 mcp.DistributorID,
810 mcp.Monday,
811 mcp.Tuesday,
812 mcp.Wednesday,
813 mcp.Thursday,
814 mcp.Friday,
815 mcp.Saturday,
816 mcp.Sunday,
817 rs.EndDate,
818 rs.RouteCD;
819
820 DELETE #tmpMCP
821 WHERE rankRoute != 1;
822
823 CREATE TABLE #tmpCustomerLocation
824 (
825 CompanyID INT,
826 CustomerID INT,
827 LocationID INT NULL,
828 CustomerCD NVARCHAR(30) NULL,
829 CustomerName NVARCHAR(60) NULL,
830 CustomerFullName NVARCHAR(255) NULL,
831 DMSCode VARCHAR(20) NULL,
832 LinkedVendorCode VARCHAR(20) NULL,
833 Country NVARCHAR(60) NULL,
834 Region NVARCHAR(50) NULL,
835 Province NVARCHAR(50) NULL,
836 District NVARCHAR(50) NULL,
837 CustomerAddress NVARCHAR(255) NULL,
838 Attribute0Descr NVARCHAR(50) NULL,
839 Attribute1Descr NVARCHAR(50) NULL,
840 Attribute2Descr NVARCHAR(50) NULL,
841 Attribute3Descr NVARCHAR(50) NULL,
842 Attribute4Descr NVARCHAR(50) NULL,
843 Attribute5Descr NVARCHAR(50) NULL,
844 Attribute6Descr NVARCHAR(50) NULL,
845 Attribute7Descr NVARCHAR(50) NULL,
846 Attribute8Descr NVARCHAR(50) NULL,
847 Attribute9Descr NVARCHAR(50) NULL,
848 Beat NVARCHAR(255) NULL,
849 DistributorID INT NULL,
850 CreatedDateTime DATETIME NULL,
851 SellingCategoryCD NVARCHAR(30) NULL,
852 SellingCategoryName NVARCHAR(50) NULL,
853 IsDelete BIT NULL
854 );
855
856 INSERT INTO #tmpCustomerLocation
857 SELECT vcl.CompanyID,
858 vcl.CustomerID,
859 vcl.LocationID,
860 vcl.CustomerCD,
861 vcl.CustomerName,
862 vcl.CustomerFullName,
863 vcl.DMSCode,
864 vcl.LinkedVendorCode,
865 vcl.CountryName,
866 vcl.RegionName,
867 vcl.ProvinceName,
868 vcl.DistrictName,
869 CustomerAddress = ISNULL(NULLIF(vcl.AddressLine1, '') + ', ', '')
870 + ISNULL(NULLIF(vcl.AddressLine2, '') + ', ', '')
871 + ISNULL(NULLIF(vcl.AddressLine3, '') + ', ', '')
872 + ISNULL(NULLIF(vcl.StreetName, '') + ', ', '')
873 + ISNULL(NULLIF(vcl.WardName, '') + ', ', '')
874 + ISNULL(NULLIF(vcl.DistrictName, '') + ', ', '') + vcl.ProvinceName,
875 vcl.Attribute0Descr,
876 vcl.Attribute1Descr,
877 vcl.Attribute2Descr,
878 vcl.Attribute3Descr,
879 vcl.Attribute4Descr,
880 vcl.Attribute5Descr,
881 vcl.Attribute6Descr,
882 vcl.Attribute7Descr,
883 vcl.Attribute8Descr,
884 vcl.Attribute9Descr,
885 mcp.Beat,
886 mcp.DistributorID,
887 vcl.CreatedDateTime,
888 sc.SellingCategoryCD,
889 SellingCategoryName = sc.Descr,
890 NULL
891 FROM dbo.DMSViewCustomerLocation vcl WITH (NOLOCK)
892 LEFT JOIN #tmpMCP mcp
893 ON mcp.CompanyID = vcl.CompanyID
894 AND mcp.CustomerID = vcl.CustomerID
895 AND mcp.CustomerLocationID = vcl.LocationID
896 LEFT JOIN dbo.DMSSellingCategory sc WITH (NOLOCK)
897 ON sc.CompanyID = vcl.CompanyID
898 AND sc.SellingCategoryID = vcl.SellingCategoryID
899 WHERE vcl.CompanyID = @_CompanyID
900 AND
901 (
902 @_AttCustomerID IS NULL
903 OR
904 (
905 vcl.Attribute0ID = @_AttCustomerID
906 OR vcl.Attribute1ID = @_AttCustomerID
907 OR vcl.Attribute2ID = @_AttCustomerID
908 OR vcl.Attribute3ID = @_AttCustomerID
909 OR vcl.Attribute4ID = @_AttCustomerID
910 OR vcl.Attribute5ID = @_AttCustomerID
911 OR vcl.Attribute6ID = @_AttCustomerID
912 OR vcl.Attribute7ID = @_AttCustomerID
913 OR vcl.Attribute8ID = @_AttCustomerID
914 OR vcl.Attribute9ID = @_AttCustomerID
915 )
916 )
917 AND
918 (
919 @_CountryID IS NULL
920 OR vcl.CountryID = @_CountryID
921 )
922 AND
923 (
924 @_ProvinceID IS NULL
925 OR vcl.ProvinceID = @_ProvinceID
926 )
927 AND
928 (
929 @_DistrictID IS NULL
930 OR vcl.DistrictID = @_DistrictID
931 );
932
933
934 CREATE TABLE #tmpInventoryItem
935 (
936 CompanyID INT,
937 InventoryID INT,
938 InventoryCD NVARCHAR(30) NULL,
939 InventoryName NVARCHAR(255) NULL,
940 InventoryShortName NVARCHAR(100) NULL,
941 InventoryAbbrName NVARCHAR(100) NULL,
942 BaseUnit NVARCHAR(6) NULL,
943 PurchaseUnit NVARCHAR(6) NULL,
944 Hierachy0Descr NVARCHAR(50) NULL,
945 Hierachy1Descr NVARCHAR(50) NULL,
946 Hierachy2Descr NVARCHAR(50) NULL,
947 Hierachy3Descr NVARCHAR(50) NULL,
948 Hierachy4Descr NVARCHAR(50) NULL,
949 Hierachy5Descr NVARCHAR(50) NULL,
950 Hierachy6Descr NVARCHAR(50) NULL,
951 Hierachy7Descr NVARCHAR(50) NULL,
952 Hierachy8Descr NVARCHAR(50) NULL,
953 Hierachy9Descr NVARCHAR(50) NULL,
954 Attribute0Descr NVARCHAR(50) NULL,
955 Attribute1Descr NVARCHAR(50) NULL,
956 Attribute2Descr NVARCHAR(50) NULL,
957 Attribute3Descr NVARCHAR(50) NULL,
958 Attribute4Descr NVARCHAR(50) NULL,
959 Attribute5Descr NVARCHAR(50) NULL,
960 Attribute6Descr NVARCHAR(50) NULL,
961 Attribute7Descr NVARCHAR(50) NULL,
962 Attribute8Descr NVARCHAR(50) NULL,
963 Attribute9Descr NVARCHAR(50) NULL
964 );
965
966 INSERT INTO #tmpInventoryItem
967 SELECT vii.CompanyID,
968 vii.InventoryID,
969 vii.InventoryCD,
970 vii.InventoryName,
971 vii.InventoryShortName,
972 vii.InventoryAbbrName,
973 vii.BaseUnit,
974 vii.PurchaseUnit,
975 vii.Hierachy0Descr,
976 vii.Hierachy1Descr,
977 vii.Hierachy2Descr,
978 vii.Hierachy3Descr,
979 vii.Hierachy4Descr,
980 vii.Hierachy5Descr,
981 vii.Hierachy6Descr,
982 vii.Hierachy7Descr,
983 vii.Hierachy8Descr,
984 vii.Hierachy9Descr,
985 vii.Attribute0Descr,
986 vii.Attribute1Descr,
987 vii.Attribute2Descr,
988 vii.Attribute3Descr,
989 vii.Attribute4Descr,
990 vii.Attribute5Descr,
991 vii.Attribute6Descr,
992 vii.Attribute7Descr,
993 vii.Attribute8Descr,
994 vii.Attribute9Descr
995 FROM dbo.DMSViewInventoryItem vii WITH (NOLOCK)
996 WHERE vii.CompanyID = @_CompanyID
997 AND
998 (
999 @_AttInventoryID IS NULL
1000 OR
1001 (
1002 vii.Hierachy0ID = @_AttInventoryID
1003 OR vii.Hierachy1ID = @_AttInventoryID
1004 OR vii.Hierachy2ID = @_AttInventoryID
1005 OR vii.Hierachy3ID = @_AttInventoryID
1006 OR vii.Hierachy4ID = @_AttInventoryID
1007 OR vii.Hierachy5ID = @_AttInventoryID
1008 OR vii.Hierachy6ID = @_AttInventoryID
1009 OR vii.Hierachy7ID = @_AttInventoryID
1010 OR vii.Hierachy8ID = @_AttInventoryID
1011 OR vii.Hierachy9ID = @_AttInventoryID
1012 )
1013 )
1014 AND
1015 (
1016 @_InventoryID IS NULL
1017 OR (vii.InventoryID = @_InventoryID)
1018 );
1019
1020 SELECT RANK() OVER (PARTITION BY rs.CompanyID,
1021 rs.RouteCD,
1022 sf.EmployeeID
1023 ORDER BY rs.EffectiveDate DESC
1024 ) rankRoute,
1025 rs.CompanyID,
1026 rs.RouteCD,
1027 sf.EmployeeID,
1028 PreviousEmployeeID = ps.EmployeeID,
1029 SalespersonCD = MAX(sf.EmployeeCD),
1030 SalespersonName = MAX(sf.Descr),
1031 PreviousSalesManCD = MAX(ps.EmployeeCD),
1032 PreviousSalesManName = MAX(ps.Descr),
1033 SalessupName = MAX(ss.Descr),
1034 SalessupCode = MAX(ss.EmployeeCD),
1035 rs.EffectiveDate,
1036 EndDate = ISNULL(rs.EndDate, '9999-12-31'),
1037 sph.RefNbr
1038 INTO #tmpSalesForce
1039 FROM dbo.DMSRouteSetting rs WITH (NOLOCK)
1040 JOIN dbo.DMSMCPDetail mcp WITH (NOLOCK)
1041 ON mcp.CompanyID = rs.CompanyID
1042 AND mcp.RefNbr = rs.RefNbr
1043 LEFT JOIN dbo.DMSSalesForce sf WITH (NOLOCK)
1044 ON sf.CompanyID = rs.CompanyID
1045 AND sf.EmployeeID = rs.SalespersonID
1046 LEFT JOIN dbo.DMSSalesForce ss WITH (NOLOCK)
1047 ON ss.CompanyID = rs.CompanyID
1048 AND ss.EmployeeID = rs.SalesForceID
1049 LEFT JOIN dbo.DMSSalesForce ps WITH (NOLOCK)
1050 ON ps.CompanyID = rs.CompanyID
1051 AND ps.EmployeeID = rs.PreviousSalesMan
1052 LEFT JOIN DMSSellingProvinceHeader sph WITH (NOLOCK)
1053 ON rs.CompanyID = sph.CompanyID
1054 AND rs.SalesAreaID = sph.SellingProvinceHeaderID
1055 WHERE @_ToDate >= rs.StartDate
1056 AND ISNULL(rs.EndDate, '9999-12-31') >= @_FromDate
1057 AND rs.CompanyID = @_CompanyID
1058 GROUP BY rs.CompanyID,
1059 rs.RouteCD,
1060 sf.EmployeeID,
1061 ps.EmployeeID,
1062 sf.EmployeeCD,
1063 sf.Descr,
1064 ss.Descr,
1065 ss.EmployeeCD,
1066 rs.EffectiveDate,
1067 rs.EndDate,
1068 sph.RefNbr;
1069
1070 -- DELETE #tmpSalesForce
1071 -- WHERE rankRoute != 1
1072 --AND PreviousEmployeeID IS NULL
1073 --AND EXISTS (SELECT 1
1074 -- FROM dbo.DMSRouteSetting rst
1075 -- WHERE rst.CompanyID=3 AND rst.RouteCD = RouteCD AND rst.SalespersonID = EmployeeID AND rst.PreviousSalesMan IS NULL AND rst.EndDate < GETDATE())
1076
1077 SELECT *
1078 INTO #tmpDis
1079 FROM #tmp
1080 WHERE #tmp.CompanyID = @_CompanyID
1081 AND
1082 (
1083 @_SalesAreaID IS NULL
1084 OR
1085 (
1086 #tmp.SalesOrgID = @_SalesAreaID
1087 OR #tmp.SalesOrg_0_ValueID = @_SalesAreaID
1088 OR #tmp.SalesOrg_1_ValueID = @_SalesAreaID
1089 OR #tmp.SalesOrg_2_ValueID = @_SalesAreaID
1090 OR #tmp.SalesOrg_3_ValueID = @_SalesAreaID
1091 OR #tmp.SalesOrg_4_ValueID = @_SalesAreaID
1092 OR #tmp.SalesOrg_5_ValueID = @_SalesAreaID
1093 OR #tmp.SalesOrg_6_ValueID = @_SalesAreaID
1094 OR #tmp.SalesOrg_7_ValueID = @_SalesAreaID
1095 OR #tmp.SalesOrg_8_ValueID = @_SalesAreaID
1096 OR #tmp.SalesOrg_9_ValueID = @_SalesAreaID
1097 )
1098 );
1099
1100 UPDATE cus
1101 SET cus.DistributorID = rd.DistributorID,
1102 cus.IsDelete = 1
1103 FROM #tmpCustomerLocation cus
1104 JOIN #tmpRawData rd
1105 ON rd.CompanyID = cus.CompanyID
1106 AND rd.CustomerID = cus.CustomerID
1107 AND rd.CustomerLocationID = cus.LocationID
1108 WHERE cus.DistributorID IS NULL;
1109
1110 SELECT d.DistributorCD,
1111 d.DistributorName,
1112 d.DistributorFullName,
1113 #tmpDis.SalesOrgID,
1114 #tmpDis.SalesOrg_0_ValueName,
1115 #tmpDis.SalesOrg_1_ValueName,
1116 #tmpDis.SalesOrg_2_ValueName,
1117 #tmpDis.SalesOrg_3_ValueName,
1118 #tmpDis.SalesOrg_4_ValueName,
1119 #tmpDis.SalesOrg_5_ValueName,
1120 #tmpDis.SalesOrg_6_ValueName,
1121 #tmpDis.SalesOrg_7_ValueName,
1122 #tmpDis.SalesOrg_8_ValueName,
1123 #tmpDis.SalesOrg_9_ValueName,
1124 rd.BillID,
1125 rd.BiLLDate,
1126 rd.BillType,
1127 rd.OrderID,
1128 rd.OrderDate,
1129 rd.CustomerOrderNbr,
1130 cl.CustomerCD,
1131 cl.CustomerName,
1132 cl.CustomerFullName,
1133 cl.DMSCode,
1134 cl.LinkedVendorCode,
1135 cl.Country,
1136 cl.Region,
1137 cl.Province,
1138 cl.District,
1139 Beat = SUBSTRING(cl.Beat, 0, LEN(cl.Beat)),
1140 cl.CustomerAddress,
1141 cl.Attribute0Descr,
1142 cl.Attribute1Descr,
1143 cl.Attribute2Descr,
1144 cl.Attribute3Descr,
1145 cl.Attribute4Descr,
1146 cl.Attribute5Descr,
1147 cl.Attribute6Descr,
1148 cl.Attribute7Descr,
1149 cl.Attribute8Descr,
1150 cl.Attribute9Descr,
1151 DateCreated = CONVERT(VARCHAR(10), cl.CreatedDateTime, 120),
1152 SalespersonCD = CASE
1153 WHEN cl.IsDelete = CAST(1 AS BIT) THEN
1154 ''
1155 ELSE
1156 COALESCE(sf.SalespersonCD, ps.PreviousSalesManCD)
1157 END,
1158 SalespersonName = CASE
1159 WHEN cl.IsDelete = CAST(1 AS BIT) THEN
1160 ''
1161 ELSE
1162 COALESCE(sf.SalespersonName, ps.PreviousSalesManName)
1163 END,
1164 SalessupName = COALESCE(sf.SalessupName, ps.SalessupName),
1165 SalessupCode = COALESCE(sf.SalessupCode, ps.SalessupCode),
1166 rd.Route,
1167 SellingCategoryID = cl.SellingCategoryCD,
1168 ii.InventoryCD,
1169 ii.InventoryName,
1170 ii.InventoryShortName,
1171 ii.InventoryAbbrName,
1172 ii.BaseUnit,
1173 ii.Hierachy0Descr,
1174 ii.Hierachy1Descr,
1175 ii.Hierachy2Descr,
1176 ii.Hierachy3Descr,
1177 ii.Hierachy4Descr,
1178 ii.Hierachy5Descr,
1179 ii.Hierachy6Descr,
1180 ii.Hierachy7Descr,
1181 ii.Hierachy8Descr,
1182 ii.Hierachy9Descr,
1183 INAttribute0Descr = ii.Attribute0Descr,
1184 INAttribute1Descr = ii.Attribute1Descr,
1185 INAttribute2Descr = ii.Attribute2Descr,
1186 INAttribute3Descr = ii.Attribute3Descr,
1187 INAttribute4Descr = ii.Attribute4Descr,
1188 INAttribute5Descr = ii.Attribute5Descr,
1189 INAttribute6Descr = ii.Attribute6Descr,
1190 INAttribute7Descr = ii.Attribute7Descr,
1191 INAttribute8Descr = ii.Attribute8Descr,
1192 INAttribute9Descr = ii.Attribute9Descr,
1193 rd.UnitPrice,
1194 rd.OriginalQty,
1195 rd.OrderQty,
1196 rd.OpenQty,
1197 rd.ShippingQty,
1198 rd.ShippingPromotionQty,
1199 rd.ShippedQty,
1200 rd.ReturnQty,
1201 rd.PromotionQty,
1202 NotShipQty = rd.OrderQty - rd.ShippedQty - rd.PromotionQty,
1203 --Quang: Thêm mỗi cột Qty 2 cột 'Số lượng theo đơn vị mua' và 'Số lượng lẻ'.
1204 OrderQtyPurchaseUnit = FLOOR( rd.OrderQty / (CASE
1205 WHEN iu.UnitRate IS NULL
1206 OR iu.UnitRate = 0 THEN
1207 1
1208 ELSE
1209 iu.UnitRate
1210 END
1211 )
1212 ),
1213 OrderQtySalesUnit = rd.OrderQty % (CASE
1214 WHEN iu.UnitRate IS NULL
1215 OR iu.UnitRate = 0 THEN
1216 1
1217 ELSE
1218 iu.UnitRate
1219 END
1220 ),
1221 OpenQtyPurchaseUnit = FLOOR( rd.OpenQty / (CASE
1222 WHEN iu.UnitRate IS NULL
1223 OR iu.UnitRate = 0 THEN
1224 1
1225 ELSE
1226 iu.UnitRate
1227 END
1228 )
1229 ),
1230 OpenQtySalesUnit = rd.OpenQty % (CASE
1231 WHEN iu.UnitRate IS NULL
1232 OR iu.UnitRate = 0 THEN
1233 1
1234 ELSE
1235 iu.UnitRate
1236 END
1237 ),
1238 ShippingQtyPurchaseUnit = FLOOR( rd.ShippingQty / (CASE
1239 WHEN iu.UnitRate IS NULL
1240 OR iu.UnitRate = 0 THEN
1241 1
1242 ELSE
1243 iu.UnitRate
1244 END
1245 )
1246 ),
1247 ShippingQtySalesUnit = rd.ShippingQty % (CASE
1248 WHEN iu.UnitRate IS NULL
1249 OR iu.UnitRate = 0 THEN
1250 1
1251 ELSE
1252 iu.UnitRate
1253 END
1254 ),
1255 ShippingPromotionQtyPurchaseUnit = FLOOR( rd.ShippingPromotionQty / (CASE
1256 WHEN iu.UnitRate IS NULL
1257 OR iu.UnitRate = 0 THEN
1258 1
1259 ELSE
1260 iu.UnitRate
1261 END
1262 )
1263 ),
1264 ShippingPromotionQtySalesUnit = rd.ShippingPromotionQty % (CASE
1265 WHEN iu.UnitRate IS NULL
1266 OR iu.UnitRate = 0 THEN
1267 1
1268 ELSE
1269 iu.UnitRate
1270 END
1271 ),
1272 ShippedQtyPurchaseUnit = FLOOR( rd.ShippedQty / (CASE
1273 WHEN iu.UnitRate IS NULL
1274 OR iu.UnitRate = 0 THEN
1275 1
1276 ELSE
1277 iu.UnitRate
1278 END
1279 )
1280 ),
1281 ShippedQtySalesUnit = rd.ShippedQty % (CASE
1282 WHEN iu.UnitRate IS NULL
1283 OR iu.UnitRate = 0 THEN
1284 1
1285 ELSE
1286 iu.UnitRate
1287 END
1288 ),
1289 ReturnQtyPurchaseUnit = FLOOR( rd.ReturnQty / (CASE
1290 WHEN iu.UnitRate IS NULL
1291 OR iu.UnitRate = 0 THEN
1292 1
1293 ELSE
1294 iu.UnitRate
1295 END
1296 )
1297 ),
1298 ReturnQtySalesUnit = rd.ReturnQty % (CASE
1299 WHEN iu.UnitRate IS NULL
1300 OR iu.UnitRate = 0 THEN
1301 1
1302 ELSE
1303 iu.UnitRate
1304 END
1305 ),
1306 PromotionQtyPurchaseUnit = FLOOR( rd.PromotionQty / (CASE
1307 WHEN iu.UnitRate IS NULL
1308 OR iu.UnitRate = 0 THEN
1309 1
1310 ELSE
1311 iu.UnitRate
1312 END
1313 )
1314 ),
1315 PromotionQtySalesUnit = rd.PromotionQty % (CASE
1316 WHEN iu.UnitRate IS NULL
1317 OR iu.UnitRate = 0 THEN
1318 1
1319 ELSE
1320 iu.UnitRate
1321 END
1322 ),
1323 NotShipQtyPurchaseUnit = FLOOR( (rd.OrderQty - rd.ShippedQty - rd.PromotionQty)
1324 / (CASE
1325 WHEN iu.UnitRate IS NULL
1326 OR iu.UnitRate = 0 THEN
1327 1
1328 ELSE
1329 iu.UnitRate
1330 END
1331 )
1332 ),
1333 NotShipQtySalesUnit = (rd.OrderQty - rd.ShippedQty - rd.PromotionQty) % (CASE
1334 WHEN iu.UnitRate IS NULL
1335 OR iu.UnitRate = 0 THEN
1336 1
1337 ELSE
1338 iu.UnitRate
1339 END
1340 ),
1341 rd.OriginalAmt,
1342 rd.OrderAmt,
1343 rd.ShippedAmt,
1344 rd.ShippingAmt,
1345 rd.PromotionAmt,
1346 rd.PromotionShippedQty,
1347 rd.OrderPayment,
1348 rd.InvoiceAmt,
1349 rd.InvoiceDate,
1350 VAT = rd.OrderAmt - rd.OrderBeforeTax,
1351 rd.Status,
1352 rd.UOM,
1353 rd.Reason,
1354 rd.In_OutRoute,
1355 rd.SourceType,
1356 rd.IsDispose,
1357 rd.IsReturn,
1358 SellingProvinceCD = COALESCE(sf.RefNbr, ps.RefNbr)
1359 FROM #tmpRawData rd
1360 LEFT JOIN #tmpDistributor d
1361 ON d.CompanyID = rd.CompanyID
1362 AND d.DistributorID = rd.DistributorID
1363 INNER JOIN #tmpDis
1364 ON #tmpDis.CompanyID = d.CompanyID
1365 AND #tmpDis.DistributorID = rd.DistributorID
1366 AND #tmpDis.DistributorLocationID = rd.DistributorLocationID
1367 AND #tmpDis.SalesOrg_0_ValueID = rd.SalesAreaID
1368 INNER JOIN #tmpCustomerLocation cl WITH (NOLOCK)
1369 ON cl.CompanyID = rd.CompanyID
1370 AND cl.CustomerID = rd.CustomerID
1371 AND cl.LocationID = rd.CustomerLocationID
1372 AND cl.DistributorID = rd.DistributorID
1373 LEFT JOIN #tmpSalesForce sf
1374 ON sf.CompanyID = rd.CompanyID
1375 AND sf.EmployeeID = rd.SalesRepsID
1376 AND sf.RouteCD = rd.Route
1377 --AND rd.OrderDate BETWEEN sf.EffectiveDate and sf.EndDate
1378 AND COALESCE(rd.OrderDate, rd.BiLLDate)
1379 BETWEEN sf.EffectiveDate AND sf.EndDate --nghia.tran: 2019.04.04; lỗi không lấy được thông tin SM khi làm đơn Manual
1380 LEFT JOIN #tmpSalesForce ps
1381 ON ps.CompanyID = rd.CompanyID
1382 AND ps.PreviousEmployeeID = rd.SalesRepsID
1383 AND ps.RouteCD = rd.Route
1384 --AND rd.OrderDate BETWEEN ps.EffectiveDate and ps.EndDate
1385 AND COALESCE(rd.OrderDate, rd.BiLLDate)
1386 BETWEEN ps.EffectiveDate AND ps.EndDate --nghia.tran: 2019.04.04; lỗi không lấy được thông tin SM khi làm đơn Manual
1387 INNER JOIN #tmpInventoryItem ii
1388 ON ii.CompanyID = rd.CompanyID
1389 AND ii.InventoryID = rd.InventoryID
1390 LEFT JOIN dbo.INUnit iu WITH (NOLOCK)
1391 ON iu.CompanyID = ii.CompanyID
1392 AND iu.InventoryID = ii.InventoryID
1393 AND iu.FromUnit = ii.PurchaseUnit
1394 AND iu.ToUnit = ii.BaseUnit
1395 AND iu.UnitMultDiv = 'M';
1396
1397 DROP TABLE #tmp;
1398 DROP TABLE #tmpAllOrder;
1399 DROP TABLE #tmpCM;
1400 DROP TABLE #tmpCustomerLocation;
1401 DROP TABLE #tmpDis;
1402 DROP TABLE #tmpDistributor;
1403 DROP TABLE #tmpInventoryItem;
1404 DROP TABLE #tmpMCP;
1405 DROP TABLE #tmpOrigin;
1406 DROP TABLE #tmpPermission;
1407 DROP TABLE #tmpPrmRoute;
1408 DROP TABLE #tmpRawData;
1409 DROP TABLE #tmpSalesForce;
1410 DROP TABLE #tmpSiteAccess;
1411 DROP TABLE #tmpSOOrderInfo;
1412 DROP TABLE #tmpTax;
1413
1414END;
1415
1416GO