· 7 years ago · Oct 31, 2018, 07:58 AM
1USE [PNCDMS]
2GO
3/****** Object: StoredProcedure [dbo].[sp_DMS_RPT_SOSalesRealtime] Script Date: 31-Oct-18 2:48:07 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8---------------------------
9--Mod: nghia.tran
10--Date mod: 2017/10/22
11-- Revison:
12-- + Dang Huynh 2018-03-22: Get Manual Discount of Distributor
13-- + Dang Huynh 2018-06-13: Thay đổi các lấy đơn hà ng theo trạng thái
14/*
15EXEC dbo.sp_DMS_RPT_SOSalesRealtime
16 @CompanyID = 3, -- int
17 @DistributorID = '230', -- varchar(max)
18 @FromDate = '2018-07-01', -- datetime
19 @ToDate = '2018-10-31', -- datetime
20 @LoginID = 'sysadmin', -- varchar(50)
21 @SiteID = 0, -- int
22 -- @RouteID = '', -- varchar(max)
23 @SalesPersonsID = 0, -- varchar(max)
24 @CustomerID = '', -- varchar(max)
25 @HierarchyID = 0, -- int
26 @InventoryID = '', -- varchar(max)
27 @RefType = '', -- varchar(2)k
28 @Status = 'N,S,C' -- varchar(1)
29*/
30
31ALTER PROCEDURE [dbo].[sp_DMS_RPT_SOSalesRealtime]
32 @CompanyID INT = NULL,
33 @DistributorID VARCHAR(MAX) = NULL,
34 @FromDate DATETIME = NULL,
35 @ToDate DATETIME = NULL,
36 @LoginID VARCHAR(50) = NULL,
37 @SiteID INT,
38 --@RouteID VARCHAR(MAX) = NULL,
39 @SalesPersonsID VARCHAR(MAX) = NULL,
40 @CustomerID VARCHAR(MAX) = NULL,
41 @HierarchyID INT = NULL,
42 @InventoryID VARCHAR(MAX) = NULL,
43 @RefType VARCHAR(2) = NULL,
44 @Status VARCHAR(MAX) = NULL,
45 @SalesAreaID INT = NULL,
46 @InventoryType VARCHAR(MAX) = NULL,
47 @ProductHierarchies VARCHAR(MAX) = NULL
48AS
49BEGIN
50 SET NOCOUNT ON;
51 SET FMTONLY OFF;
52 SET NO_BROWSETABLE OFF;
53 DECLARE @_CompanyID INT = NULLIF(@CompanyID, 0);
54 --DECLARE @_DistributorID INT = NULLIF(@DistributorID, 0)--nghia.tran comment
55 DECLARE @_DistributorID VARCHAR(MAX) = NULLIF(LTRIM(RTRIM(@DistributorID)), ''); --nghia.tran: dùng list
56 DECLARE @_FromDate DATETIME = @FromDate;
57 DECLARE @_ToDate DATETIME = @ToDate;
58 DECLARE @_LoginID VARCHAR(50) = @LoginID;
59 --DECLARE @_SiteID INT = NULLIF(@SiteID, 0)
60 DECLARE @_TerritoryType CHAR(1);
61 DECLARE @_HierarchyID INT = NULLIF(@HierarchyID, 0);
62 --DECLARE @_RefType VARCHAR (2) =@RefType
63 --DECLARE @_Status VARCHAR(2) = @Status;
64 DECLARE @_Status VARCHAR(MAX) = NULLIF(LTRIM(RTRIM(@Status)), '');
65 DECLARE @_SalesAreaID INT = NULLIF(@SalesAreaID, 0);
66 DECLARE @_InventoryType VARCHAR(MAX) = NULLIF(LTRIM(RTRIM(@InventoryType)), '');
67 DECLARE @_ProductHierarchies VARCHAR(MAX) = NULLIF(LTRIM(RTRIM(@ProductHierarchies)), '');
68 DECLARE @_SalesPersonsID VARCHAR(MAX) = NULLIF(LTRIM(RTRIM(@SalesPersonsID)), '');
69 DECLARE @_CustomerID VARCHAR(MAX) = NULLIF(LTRIM(RTRIM(@CustomerID)), '');
70 DECLARE @_InventoryID VARCHAR(MAX) = NULLIF(LTRIM(RTRIM(@InventoryID)), '');
71 SELECT CompanyID,
72 SiteID
73 INTO #tmpSiteAccess
74 FROM dbo.DMSViewSiteByUser
75 WHERE CompanyID = @_CompanyID
76 AND Username = @_LoginID;
77
78 --Chuyển list @_DistributorID vỠarray
79 SELECT *
80 INTO #ListDistributor
81 FROM dbo.fn_DMS_ConvertIDListToTable(@_DistributorID);
82
83 --Chuyển list @_SalesPersonsID vỠarray
84 SELECT *
85 INTO #ListSalesPersons
86 FROM dbo.fn_DMS_ConvertIDListToTable_Char(@_SalesPersonsID);
87
88 --Chuyển list @_CustomerID vỠarray
89 SELECT *
90 INTO #ListCustomer
91 FROM dbo.fn_DMS_ConvertIDListToTable(@_CustomerID);
92
93 --Chuyển list @_InventoryID vỠarray
94 SELECT *
95 INTO #ListInventory
96 FROM dbo.fn_DMS_ConvertIDListToTable(@_InventoryID);
97
98 --Chuyển list @_InventoryType vỠarray
99 SELECT *
100 INTO #ListInventoryType
101 FROM dbo.fn_DMS_ConvertCharListToTableChar(@_InventoryType);
102 --Chuyển list @_Status vỠarray
103 SELECT *
104 INTO #ListStatus
105 FROM dbo.fn_DMS_ConvertCharListToTableChar(@_Status);
106 --Chuyển list @_ProductHierarchies vỠarray
107 SELECT *
108 INTO #ListProductHierarchies
109 FROM dbo.fn_DMS_ConvertIDListToTable(@_ProductHierarchies);
110 CREATE TABLE #tmp (CompanyID INT,
111 DistributorID INT,
112 DistributorLocationID INT,
113 SalesOrgID INT,
114 SalesOrg_0_ValueID INT,
115 SalesOrg_0_ValueCD VARCHAR(15),
116 SalesOrg_0_ValueName NVARCHAR(50),
117 SalesForce_0_Name NVARCHAR(50),
118 SalesOrg_1_ValueID INT,
119 SalesOrg_1_ValueCD VARCHAR(15),
120 SalesOrg_1_ValueName NVARCHAR(50),
121 SalesForce_1_Name NVARCHAR(50),
122 SalesOrg_2_ValueID INT,
123 SalesOrg_2_ValueCD VARCHAR(15),
124 SalesOrg_2_ValueName NVARCHAR(50),
125 SalesForce_2_Name NVARCHAR(50),
126 SalesOrg_3_ValueID INT,
127 SalesOrg_3_ValueCD VARCHAR(15),
128 SalesOrg_3_ValueName NVARCHAR(50),
129 SalesForce_3_Name NVARCHAR(50),
130 SalesOrg_4_ValueID INT,
131 SalesOrg_4_ValueCD VARCHAR(15),
132 SalesOrg_4_ValueName NVARCHAR(50),
133 SalesForce_4_Name NVARCHAR(50),
134 SalesOrg_5_ValueID INT,
135 SalesOrg_5_ValueCD VARCHAR(15),
136 SalesOrg_5_ValueName NVARCHAR(50),
137 SalesForce_5_Name NVARCHAR(50),
138 SalesOrg_6_ValueID INT,
139 SalesOrg_6_ValueCD VARCHAR(15),
140 SalesOrg_6_ValueName NVARCHAR(50),
141 SalesForce_6_Name NVARCHAR(50),
142 SalesOrg_7_ValueID INT,
143 SalesOrg_7_ValueCD VARCHAR(15),
144 SalesOrg_7_ValueName NVARCHAR(50),
145 SalesForce_7_Name NVARCHAR(50),
146 SalesOrg_8_ValueID INT,
147 SalesOrg_8_ValueCD VARCHAR(15),
148 SalesOrg_8_ValueName NVARCHAR(50),
149 SalesForce_8_Name NVARCHAR(50),
150 SalesOrg_9_ValueID INT,
151 SalesOrg_9_ValueCD VARCHAR(15),
152 SalesOrg_9_ValueName NVARCHAR(50),
153 SalesForce_9_Name NVARCHAR(50),
154 ParentID INT,
155 ParentLevel INT);
156
157 ---- Lấy thông tin permission theo user đăng nháºp
158 SELECT *
159 INTO #tmpPermission
160 FROM dbo.fn_DMS_GetSFPermission_ListDistributor(@_CompanyID, @_DistributorID, @_LoginID);
161
162 ---- Lấy working position của user đăng nháºp
163 ---- Äấy là báo cáo NPP nên nếu territory type khác SS thì set kiểu view 1 NPP
164 SELECT @_TerritoryType = (SELECT TOP 1 TerritoryType FROM #tmpPermission);
165 IF @_TerritoryType != 'D'
166 OR @_TerritoryType IS NULL
167 SET @_TerritoryType = 'X';
168
169 ---- Group by các dữ liệu permission
170 SELECT RouteCD
171 INTO #tmpPrmRoute
172 FROM #tmpPermission
173 GROUP BY RouteCD;
174
175 ---- Lấy thông tin NPP với cây sales org tương ứng
176 INSERT INTO #tmp
177 EXEC dbo.sp_DMS_MDM_GetDistributorWithSalesOrg @_CompanyID;
178
179 ---- 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
180 SELECT soo.CompanyID,
181 DistributorID = soo.BranchID,
182 DistributorLocationID = NULL,
183 soo.OrderNbr,
184 soo.OrderType,
185 soo.OrderDate,
186 soo.CustomerOrderNbr,
187 soo.CustomerID,
188 soo.CustomerLocationID,
189 SalesRepsID = soo.UsrSalesReps,
190 SalesForeID = soo.UsrSalesForce,
191 Route = soo.UsrRoute,
192 sol.InventoryID,
193 sol.UnitPrice,
194 sol.LineNbr,
195 OrderQty = ISNULL(sol.UsrOrgQty, sol.BaseOrderQty),
196 OpenQty = sol.BaseOpenQty,
197 ShipQty = sol.BaseOrderQty,
198 sol.OpenAmt,
199 sol.CuryExtPrice, -- thanh tien = don gia * soluong
200 sol.CuryLineAmt, -- thanh toan = don gia * so luong - chiet khau
201 sol.IsFree,
202 sol.ReasonCode,
203 --thao.le: ko xóa Ä‘Æ¡n trạng thái đã hủy (vì thêm Ä‘k lá»c thêm các Ä‘Æ¡n hà ng Hủy)
204 IsDelete = CASE -- WHEN soo.Cancelled = 1 THEN 1
205 WHEN ISNULL(soo.UsrIsDispose, 0) = 1 THEN 1
206 WHEN soo.OrderType = 'CM'
207 AND ISNULL(soo.UsrIsRevenueDeduction, 0) = 0 THEN 1 END,
208 Status = CASE
209 WHEN soo.Status = 'N' THEN N'Mở'
210 WHEN soo.Status = 'H' THEN N'Chá»'
211 WHEN soo.Status = 'S' THEN N'Äang giao'
212 WHEN soo.Status = 'C' THEN N'Hoà n tất'
213 --thao.le: vì thêm Ä‘k lá»c thêm các Ä‘Æ¡n hà ng Hủy
214 WHEN soo.Status = 'L' THEN N'Äã hủy' END,
215 Checkbox = CASE
216 WHEN pdaorder.ValidDate = 1 THEN N'Äúng tuyến'
217 WHEN pdaorder.ValidDate != 1 THEN N'Trái tuyến'
218 WHEN pdaorder.ValidDate = 1
219 AND pdaorder.ValidDistance != 1 THEN N'Äúng tuyến, Sai khoảng cách'
220 WHEN soo.CustomerOrderNbr IS NULL THEN N'Nhà phân phôi bán' END,
221 soo.RequestDate,
222 UsrSourceType = CASE
223 WHEN soo.UsrSourceType = 'RPO' THEN 'Request Purchase Order'
224 WHEN soo.UsrSourceType = 'PDA' THEN 'PDA Order'
225 WHEN soo.UsrSourceType = 'MAN' THEN 'Manual Order'
226 WHEN soo.UsrSourceType = 'TMK' THEN 'Trade Marketing' END,
227 soo.OrderDesc,
228 soo.UsrDiscTot, -- Tong Chiet Khau - Cac Khoan Giam Tru
229 soo.OrderWeight,
230 CuryOrderTotal, -- Tong Thanh Toan
231 soship.ShipmentNbr,
232 InvoiceNbr = arregister.RefNbr,
233 InvtRefNbr = inregister.RefNbr,
234 ShipDate = CASE
235 WHEN soo.OrderType = 'IN' THEN soo.UsrShipDate
236 ELSE soship.ShipDate END,
237 arregister.RefNbr,
238 arregister.DocDate,
239 inregister.TranDate,
240 sol.UsrPromotionID,
241 sol.UsrManualDiscountPct,
242 sol.UsrManualDiscountAmt,
243 soo.UsrReasonCodeID
244 INTO #tmpAllOrder
245 FROM dbo.SOOrder soo WITH (NOLOCK)
246 JOIN dbo.SOLine sol WITH (NOLOCK)
247 ON sol.CompanyID = soo.CompanyID
248 AND sol.BranchID = soo.BranchID
249 AND sol.OrderNbr = soo.OrderNbr
250 AND sol.OrderType = soo.OrderType
251 LEFT JOIN SOOrderShipment soship WITH (NOLOCK)
252 ON soo.CompanyID = soship.CompanyID
253 AND soo.OrderNbr = soship.OrderNbr
254 AND soo.OrderType = soship.OrderType
255 LEFT JOIN DMSPDAOrder pdaorder WITH (NOLOCK)
256 ON soo.CompanyID = pdaorder.CompanyID
257 AND soo.BranchID = pdaorder.BranchID
258 AND soo.CustomerOrderNbr = pdaorder.OrderNbr
259 --LEFT JOIN dbo.SOShipLine sosl WITH (NOLOCK) --- Thu.Nguyen - 2018.10.30: BỠbớt bảng - thay bằng cột ShipDate trong SOOrderShipment
260 -- ON sosl.CompanyID = sol.CompanyID
261 -- AND sosl.OrigOrderNbr = sol.OrderNbr
262 -- AND sosl.OrigOrderType = sol.OrderType
263 -- AND sosl.OrigLineNbr = sol.LineNbr
264 --LEFT JOIN dbo.SOShipment sos WITH (NOLOCK)
265 -- ON sos.CompanyID = soship.CompanyID
266 -- AND sos.ShipmentNbr = soship.ShipmentNbr
267 -- AND sos.ShipmentType = soship.ShipmentType
268 LEFT JOIN dbo.ARRegister arregister WITH (NOLOCK)
269 ON soship.CompanyID = arregister.CompanyID
270 AND soship.InvoiceNbr = arregister.RefNbr
271 AND soship.InvoiceType = arregister.DocType
272 LEFT JOIN dbo.INRegister inregister WITH (NOLOCK)
273 ON inregister.CompanyID = soship.CompanyID
274 AND inregister.RefNbr = soship.InvtRefNbr
275 AND inregister.DocType = soship.InvtDocType
276 WHERE soo.CompanyID = @_CompanyID
277 --AND soo.BranchID = @_DistributorID
278 AND ( soo.BranchID IN ( SELECT * FROM #ListDistributor d )
279 OR @_DistributorID IS NULL) -- nghia.tran add
280 AND soo.OrderDate BETWEEN @_FromDate AND @_ToDate
281 --AND (@_SiteID IS NULL OR sol.SiteID = @_SiteID)
282 AND EXISTS ( SELECT *
283 FROM #tmpSiteAccess sa
284 WHERE sa.CompanyID = sol.CompanyID
285 AND sa.SiteID = sol.SiteID)
286 AND ( @_TerritoryType = 'X'
287 OR EXISTS (SELECT TOP 1 * FROM #tmpPrmRoute pr WHERE pr.RouteCD = soo.UsrRoute)) ---- Kiểm tra permission với working position là SS
288 AND ( @_InventoryID IS NULL
289 OR EXISTS (SELECT ID FROM #ListInventory inv WHERE inv.ID = sol.InventoryID)) -- nghia.tran add
290 AND ( @_CustomerID IS NULL
291 OR EXISTS (SELECT ID FROM #ListCustomer cus WHERE cus.ID = sol.CustomerID))
292 --AND ( @_SalesPersonsID IS NULL
293 -- OR EXISTS ( SELECT ID
294 -- FROM #ListSalesPersons salesperson
295 -- WHERE salesperson.ID = soo.UsrSalesReps))
296 --AND (@_RefType IS NULL OR @_RefType = soo.OrderType)
297 --AND (@_Status IS NULL OR @_Status = soo.Status)
298 AND ( @_Status IS NULL
299 OR EXISTS (SELECT ID FROM #ListStatus ls WHERE ls.ID = soo.UsrDMSStatus))
300 AND ( @_InventoryType IS NULL
301 OR EXISTS ( SELECT ID
302 FROM #ListInventoryType invType
303 WHERE invType.ID = sol.ReasonCode));
304
305 --AND (@_InventoryType IS NULL AND (sol.ReasonCodeID IN ('ISS', 'ISSKM', 'ISSBH'))--ISS: Xuất bán; ISSKM: Xuất hà ng KM; ISSBH: Xuất hà ng bảo hà nh
306 -- OR @_InventoryType IS NOT NULL AND EXISTS(SELECT ID FROM #ListInventoryType invType WHERE invType.ID = sol.ReasonCode))
307 CREATE NONCLUSTERED INDEX [#tmpAllOrder_index]
308 ON #tmpAllOrder (CompanyID, OrderNbr, OrderType, LineNbr);
309 CREATE NONCLUSTERED INDEX [#tmpAllOrder_index_delete]
310 ON #tmpAllOrder (IsDelete);
311 DELETE FROM #tmpAllOrder
312 WHERE IsDelete = 1;
313 UPDATE ao
314 SET ao.DistributorLocationID = vsor.DistributorLocationID
315 FROM #tmpAllOrder ao
316 JOIN dbo.DMSViewSORoute vsor WITH (NOLOCK)
317 ON vsor.CompanyID = ao.CompanyID
318 AND vsor.CustomerID = ao.CustomerID
319 AND vsor.CustomerLocationID = ao.CustomerLocationID
320 AND vsor.RouteCD = ao.Route
321 AND ao.OrderDate BETWEEN vsor.EffectiveDate AND vsor.EndDate;
322
323 ---- Lấy raw data
324 SELECT ao.CompanyID,
325 ao.DistributorID,
326 ao.DistributorLocationID,
327 ao.OrderNbr,
328 ao.OrderType,
329 ao.OrderDate,
330 ao.CustomerOrderNbr,
331 ao.CustomerID,
332 ao.CustomerLocationID,
333 ao.SalesRepsID,
334 SalesForeID = MAX(ao.SalesForeID),
335 ao.Route,
336 ao.InventoryID,
337 UnitPrice = MAX(ao.UnitPrice),
338 SKU = CONVERT(DECIMAL(18, 5), 1),
339 TotalOrder = CONVERT(DECIMAL(18, 5), NULL),
340 OrderQty = SUM(CASE
341 WHEN ao.OrderType != 'CM' THEN ao.OrderQty
342 ELSE ao.OrderQty * (-1) END), --Số lượng đặt
343 OpenQty = SUM(CASE
344 WHEN ao.OrderType != 'CM' THEN ao.OpenQty
345 ELSE ao.OpenQty * (-1) END), --Số lượng chưa giao
346 OrderAmt = SUM(CASE
347 WHEN ao.OrderType != 'CM' THEN ao.CuryLineAmt
348 ELSE ao.CuryLineAmt * (-1) END), --Doanh số đặt
349 OpenAmt = SUM(CASE
350 WHEN ao.OrderType != 'CM' THEN ao.OpenAmt
351 ELSE OpenAmt * (-1) END), --Doanh số chưa giao
352 ExtPrice = SUM(CASE
353 WHEN ao.OrderType != 'CM' THEN ao.CuryExtPrice
354 --ELSE 0
355 ELSE ao.CuryExtPrice * (-1) END),
356 Status = MAX(ao.Status),
357 Checkbox = MAX(ao.Checkbox),
358 RequestDate = MAX(ao.RequestDate),
359 UsrSourceType = MAX(ao.UsrSourceType),
360 OrderDesc = MAX(ao.OrderDesc),
361 UsrDiscTot = MAX(ao.UsrDiscTot),
362 OrderWeight = MAX(ao.OrderWeight),
363 CuryOrderTotal = MAX(CuryOrderTotal),
364 ActualOrderQty = SUM(CASE
365 WHEN ao.OrderType != 'CM' THEN ao.ShipQty
366 ELSE ao.ShipQty * (-1) END),
367 ShipmentNbr = MAX(ao.ShipmentNbr),
368 InvoiceNbr = MAX(ao.InvoiceNbr),
369 InvtRefNbr = MAX(ao.InvtRefNbr),
370 ShipDate = MAX(ao.ShipDate),
371 RefNbr = MAX(ao.RefNbr),
372 DocDate = MAX(ao.DocDate),
373 TranDate = MAX(ao.TranDate),
374 ReasonCode = MAX(ao.ReasonCode),
375 UsrPromotionID = MAX(ao.UsrPromotionID),
376 UsrManualDiscountPct = SUM(ao.UsrManualDiscountPct),
377 UsrManualDiscountAmt = SUM(ao.UsrManualDiscountAmt),
378 --thêm reason của đơn để hiển thị lý do đơn hủy
379 SOReason = ao.UsrReasonCodeID
380 INTO #tmpRawData
381 FROM #tmpAllOrder ao
382 GROUP BY ao.CompanyID,
383 ao.DistributorID,
384 ao.DistributorLocationID,
385 ao.OrderNbr,
386 ao.OrderType,
387 ao.OrderDate,
388 ao.CustomerOrderNbr,
389 ao.CustomerID,
390 ao.CustomerLocationID,
391 ao.SalesRepsID,
392 ao.Route,
393 ao.InventoryID,
394 ao.UsrReasonCodeID;
395
396 CREATE NONCLUSTERED INDEX [#tmpRawData_index]
397 ON #tmpRawData
398 (CompanyID, DistributorID, DistributorLocationID, CustomerID, CustomerLocationID, SalesRepsID, InventoryID);
399 CREATE NONCLUSTERED INDEX [#tmpRawData_index_update]
400 ON #tmpRawData (CompanyID, DistributorID, OrderDate, SalesRepsID);
401 UPDATE rd
402 SET rd.TotalOrder = CONVERT(DECIMAL(18, 5), 1) / CONVERT(DECIMAL(18, 5), tmp.TotalRecord)
403 FROM #tmpRawData rd
404 JOIN ( SELECT CompanyID,
405 DistributorID,
406 OrderDate,
407 SalesRepsID,
408 CustomerLocationID,
409 TotalRecord = COUNT(CompanyID)
410 FROM #tmpRawData
411 WHERE OrderType != 'CM'
412 GROUP BY CompanyID,
413 DistributorID,
414 OrderDate,
415 SalesRepsID,
416 CustomerLocationID) tmp
417 ON tmp.CompanyID = rd.CompanyID
418 AND tmp.DistributorID = rd.DistributorID
419 AND tmp.OrderDate = rd.OrderDate
420 AND tmp.SalesRepsID = rd.SalesRepsID
421 AND tmp.CustomerLocationID = rd.CustomerLocationID
422 WHERE rd.OrderType != 'CM';
423
424 ---Lấy thông tin distributor
425 SELECT vd.CompanyID,
426 vd.DistributorID,
427 vd.DistributorCD,
428 vd.DistributorName,
429 vd.DistributorFullName,
430 vd.DistributorLocationID,
431 vd.AddressLine1,
432 #tmp.SalesOrgID,
433 #tmp.SalesOrg_0_ValueName,
434 #tmp.SalesOrg_1_ValueName,
435 #tmp.SalesOrg_2_ValueName,
436 #tmp.SalesOrg_3_ValueName,
437 #tmp.SalesOrg_4_ValueName,
438 #tmp.SalesOrg_5_ValueName,
439 #tmp.SalesOrg_6_ValueName,
440 #tmp.SalesOrg_7_ValueName,
441 #tmp.SalesOrg_8_ValueName,
442 #tmp.SalesOrg_9_ValueName
443 INTO #tmpDistributor
444 FROM dbo.DMSViewDistributor vd WITH (NOLOCK)
445 JOIN #tmp
446 ON #tmp.CompanyID = vd.CompanyID
447 AND #tmp.DistributorID = vd.DistributorID
448 AND #tmp.DistributorLocationID = vd.DistributorLocationID
449 WHERE ( @_SalesAreaID IS NULL
450 OR #tmp.SalesOrg_0_ValueID = @_SalesAreaID
451 OR #tmp.SalesOrg_1_ValueID = @_SalesAreaID
452 OR #tmp.SalesOrg_2_ValueID = @_SalesAreaID
453 OR #tmp.SalesOrg_3_ValueID = @_SalesAreaID
454 OR #tmp.SalesOrg_4_ValueID = @_SalesAreaID
455 OR #tmp.SalesOrg_5_ValueID = @_SalesAreaID
456 OR #tmp.SalesOrg_6_ValueID = @_SalesAreaID
457 OR #tmp.SalesOrg_7_ValueID = @_SalesAreaID
458 OR #tmp.SalesOrg_8_ValueID = @_SalesAreaID
459 OR #tmp.SalesOrg_9_ValueID = @_SalesAreaID);
460 CREATE NONCLUSTERED INDEX [#tmpDistributor_index]
461 ON #tmpDistributor (CompanyID, DistributorID, DistributorLocationID);
462
463 --Lấy thông tin Inventory
464 SELECT vii.CompanyID,
465 vii.InventoryID,
466 vii.InventoryCD,
467 vii.InventoryName,
468 vii.InventoryShortName,
469 vii.InventoryAbbrName,
470 vii.BaseUnit,
471 vii.PurchaseUnit,
472 vii.HierarchyCD,
473 vii.HierarchyDescr,
474 vii.Hierachy0Descr,
475 vii.Hierachy1Descr,
476 vii.Hierachy2Descr,
477 vii.Hierachy3Descr,
478 vii.Hierachy4Descr,
479 vii.Hierachy5Descr,
480 vii.Hierachy6Descr,
481 vii.Hierachy7Descr,
482 vii.Hierachy8Descr,
483 vii.Hierachy9Descr
484 INTO #tmpInventoryItem
485 FROM dbo.DMSViewInventoryItem vii WITH (NOLOCK)
486 WHERE ( @_HierarchyID IS NULL
487 OR vii.Hierachy0ID = @_HierarchyID
488 OR vii.Hierachy1ID = @_HierarchyID
489 OR vii.Hierachy2ID = @_HierarchyID
490 OR vii.Hierachy3ID = @_HierarchyID
491 OR vii.Hierachy4ID = @_HierarchyID
492 OR vii.Hierachy5ID = @_HierarchyID
493 OR vii.Hierachy6ID = @_HierarchyID
494 OR vii.Hierachy7ID = @_HierarchyID
495 OR vii.Hierachy8ID = @_HierarchyID
496 OR vii.Hierachy9ID = @_HierarchyID
497 OR @_HierarchyID = 0)
498 AND ( @_ProductHierarchies IS NULL
499 OR vii.HierarchyID IN ( SELECT ID FROM #ListProductHierarchies ));
500 CREATE NONCLUSTERED INDEX [#tmpInventoryItem_index]
501 ON #tmpInventoryItem (CompanyID, InventoryID);
502
503
504 SELECT d.DistributorCD,
505 d.DistributorName,
506 d.DistributorFullName,
507 d.AddressLine1,
508 d.SalesOrgID,
509 d.SalesOrg_0_ValueName,
510 d.SalesOrg_1_ValueName,
511 d.SalesOrg_2_ValueName,
512 d.SalesOrg_3_ValueName,
513 d.SalesOrg_4_ValueName,
514 d.SalesOrg_5_ValueName,
515 d.SalesOrg_6_ValueName,
516 d.SalesOrg_7_ValueName,
517 d.SalesOrg_8_ValueName,
518 d.SalesOrg_9_ValueName,
519 cl.CustomerCD,
520 cl.CustomerName,
521 cl.CustomerFullName,
522 cl.CustomerPhone1,
523 CAddressLine1 = cl.AddressLine1,
524 cl.Attribute0Descr,
525 cl.Attribute1Descr,
526 cl.Attribute2Descr,
527 cl.Attribute3Descr,
528 cl.Attribute4Descr,
529 cl.Attribute5Descr,
530 cl.Attribute6Descr,
531 cl.Attribute7Descr,
532 cl.Attribute8Descr,
533 cl.Attribute9Descr,
534 rd.OrderNbr,
535 rd.OrderType,
536 rd.OrderDate,
537 rd.ActualOrderQty,
538 OrderMonth = CAST(YEAR(rd.OrderDate) AS NVARCHAR(10))
539 + RIGHT('0' + CAST(MONTH(rd.OrderDate) AS NVARCHAR(2)), 2),
540 OrderQuater = CAST(YEAR(rd.OrderDate) AS NVARCHAR(10)) + 'Q'
541 + CAST(DATEPART(qq, rd.OrderDate) AS NVARCHAR(1)),
542 OrderYear = CAST(YEAR(rd.OrderDate) AS NVARCHAR(10)),
543 rd.CustomerOrderNbr,
544 rd.Route,
545 rd.UnitPrice,
546 rd.SKU,
547 rd.TotalOrder,
548 rd.OrderQty,
549 rd.OrderAmt,
550 rd.ExtPrice,
551 rd.OpenQty,
552 rd.OpenAmt,
553 rd.Status,
554 rd.Checkbox,
555 rd.RequestDate,
556 rd.UsrSourceType,
557 rd.OrderDesc,
558 rd.UsrDiscTot,
559 rd.OrderWeight,
560 CuryOrderTotal,
561 SalespersonCD = sf.EmployeeCD,
562 SalespersonName = sf.Descr,
563 SSCD = sf2.EmployeeCD,
564 SSName = sf2.Descr,
565 RouteDesc = route.Descr,
566 ii.InventoryCD,
567 ii.InventoryName,
568 ii.InventoryShortName,
569 ii.InventoryAbbrName,
570 ii.BaseUnit,
571 ii.HierarchyCD,
572 ii.HierarchyDescr,
573 ii.Hierachy0Descr,
574 ii.Hierachy1Descr,
575 ii.Hierachy2Descr,
576 ii.Hierachy3Descr,
577 ii.Hierachy4Descr,
578 ii.Hierachy5Descr,
579 ii.Hierachy6Descr,
580 ii.Hierachy7Descr,
581 ii.Hierachy8Descr,
582 ii.Hierachy9Descr,
583 rd.ShipmentNbr,
584 rd.InvoiceNbr,
585 rd.InvtRefNbr,
586 rd.ShipDate,
587 rd.RefNbr,
588 rd.DocDate,
589 rd.TranDate,
590 reason.ReasonCodeID,
591 ReasonDesc = reason.Descr,
592 PromotionID = disc.PromotionCD,
593 PromotionName = disc.Descr,
594 disc.DealID,
595 disc.SchemeID,
596 DisitributorManualPromotionPct = rd.UsrManualDiscountPct,
597 DistributorManualPromotionAmt = rd.UsrManualDiscountAmt,
598 SOReason = soReason.[Desc]
599 FROM #tmpRawData rd
600 JOIN #tmpDistributor d
601 ON d.CompanyID = rd.CompanyID
602 AND d.DistributorID = rd.DistributorID
603 AND d.DistributorLocationID = rd.DistributorLocationID
604 JOIN dbo.DMSViewCustomerLocation cl WITH (NOLOCK)
605 ON cl.CompanyID = rd.CompanyID
606 AND cl.CustomerID = rd.CustomerID
607 AND cl.LocationID = rd.CustomerLocationID
608 JOIN #tmpInventoryItem ii
609 ON ii.CompanyID = rd.CompanyID
610 AND ii.InventoryID = rd.InventoryID
611 --JOIN dbo.INUnit iu ON iu.CompanyID = ii.CompanyID -------- thu.nguyen : 2018-10-05 comment lại do không dùng
612 -- AND iu.InventoryID = ii.InventoryID
613 -- AND iu.FromUnit = ii.PurchaseUnit
614 -- AND iu.ToUnit = ii.BaseUnit
615 -- AND iu.UnitMultDiv = 'M'
616 LEFT JOIN dbo.DMSSalesForce sf
617 ON sf.CompanyID = rd.CompanyID
618 AND sf.EmployeeID = rd.SalesRepsID
619 LEFT JOIN dbo.DMSSalesForce sf2
620 ON sf2.CompanyID = rd.CompanyID
621 AND sf2.EmployeeID = rd.SalesForeID
622 LEFT JOIN dbo.DMSRoute route
623 ON route.CompanyID = rd.CompanyID
624 AND route.RouteCD = rd.Route
625 LEFT JOIN dbo.ReasonCode reason
626 ON rd.CompanyID = reason.CompanyID
627 AND rd.ReasonCode = reason.ReasonCodeID
628 LEFT JOIN DMSPRODiscount disc
629 ON disc.CompanyID = rd.CompanyID
630 AND disc.PromotionID = rd.UsrPromotionID
631 LEFT JOIN dbo.DMSSOReasonCode soReason
632 ON rd.CompanyID = soReason.CompanyID
633 AND rd.SOReason = soReason.ReasonCodeID;
634END;