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