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