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