· 7 years ago · Sep 14, 2018, 10:10 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
7GO
8/****** Object: StoredProcedure [dbo].[sp_DMS_RPT_SOSalesRealtime] Script Date: 7/6/2018 9:49:09 AM ******/
9SET ANSI_NULLS ON;
10GO
11SET QUOTED_IDENTIFIER ON;
12GO
13---- EXEC dbo.sp_DMS_RPT_SOSalesRealtime 3, 219, '2018/06/01','2018/06/30','admin',null
14CREATE PROCEDURE [dbo].[sp_DMS_RPT_SOSalesRealtime]
15 @CompanyID INT = NULL,
16 @SelectedDistributorID INT = NULL,
17 @DistributorID INT = NULL,
18 @FromDate DATETIME = NULL,
19 @ToDate DATETIME = NULL,
20 @LoginID VARCHAR(50) = NULL,
21 @SiteID INT = NULL,
22 @SalesAreaID INT = NULL,
23 @RouteCD VARCHAR(15) = NULL,
24 @CustomerID INT = NULL,
25 @HierarchyID INT = NULL,
26 @AttributeID INT = NULL
27
28AS
29BEGIN
30 SET NOCOUNT ON;
31 SET FMTONLY OFF;
32 SET NO_BROWSETABLE OFF;
33
34 DECLARE @_CompanyID INT = NULLIF(@CompanyID, 0);
35 DECLARE @_DistributorID INT = NULLIF(@DistributorID, 0);
36 DECLARE @_FromDate DATETIME = @FromDate;
37 DECLARE @_ToDate DATETIME = @ToDate;
38 DECLARE @_FirstDayOfCurrentMonth DATETIME
39 = CONVERT(VARCHAR(4), YEAR(GETDATE())) + '/' + CONVERT(VARCHAR(2), MONTH(GETDATE())) + '/01';
40 DECLARE @_LoginID VARCHAR(50) = @LoginID;
41 DECLARE @_TerritoryType CHAR(1);
42 DECLARE @_CurrentDate DATETIME = GETDATE();
43 DECLARE @_SiteID INT = NULLIF(@SiteID, 0);
44 DECLARE @_SalesAreaID INT = NULLIF(@SalesAreaID, 0);
45 DECLARE @_CustomerID INT = NULLIF(@CustomerID, 0);
46 DECLARE @_HierarchyID INT = NULLIF(@HierarchyID, 0);
47 DECLARE @_SelectedDistributorID INT = NULLIF(@SelectedDistributorID, 0);
48 DECLARE @_AttributeID INT = NULLIF(@AttributeID, 0);
49 DECLARE @_RouteCD VARCHAR(15) = NULLIF(@RouteCD, '');
50
51 SELECT CompanyID,
52 SiteID
53 INTO #tmpSiteAccess
54 FROM dbo.DMSViewSiteByUser WITH (NOLOCK)
55 WHERE CompanyID = @_CompanyID
56 AND Username = @_LoginID;
57 --SELECT @_DistributorID = DistributorID
58 --FROM dbo.DMSViewDistributorLocation
59 --WHERE IsHO = 1;
60
61 CREATE TABLE #tmp (CompanyID INT,
62 DistributorID INT,
63 DistributorLocationID INT,
64 SalesOrgID INT,
65 SalesOrg_0_ValueID INT,
66 SalesOrg_0_ValueCD VARCHAR(15),
67 SalesOrg_0_ValueName NVARCHAR(50),
68 SalesForce_0_Name NVARCHAR(50),
69 SalesOrg_1_ValueID INT,
70 SalesOrg_1_ValueCD VARCHAR(15),
71 SalesOrg_1_ValueName NVARCHAR(50),
72 SalesForce_1_Name NVARCHAR(50),
73 SalesOrg_2_ValueID INT,
74 SalesOrg_2_ValueCD VARCHAR(15),
75 SalesOrg_2_ValueName NVARCHAR(50),
76 SalesForce_2_Name NVARCHAR(50),
77 SalesOrg_3_ValueID INT,
78 SalesOrg_3_ValueCD VARCHAR(15),
79 SalesOrg_3_ValueName NVARCHAR(50),
80 SalesForce_3_Name NVARCHAR(50),
81 SalesOrg_4_ValueID INT,
82 SalesOrg_4_ValueCD VARCHAR(15),
83 SalesOrg_4_ValueName NVARCHAR(50),
84 SalesForce_4_Name NVARCHAR(50),
85 SalesOrg_5_ValueID INT,
86 SalesOrg_5_ValueCD VARCHAR(15),
87 SalesOrg_5_ValueName NVARCHAR(50),
88 SalesForce_5_Name NVARCHAR(50),
89 SalesOrg_6_ValueID INT,
90 SalesOrg_6_ValueCD VARCHAR(15),
91 SalesOrg_6_ValueName NVARCHAR(50),
92 SalesForce_6_Name NVARCHAR(50),
93 SalesOrg_7_ValueID INT,
94 SalesOrg_7_ValueCD VARCHAR(15),
95 SalesOrg_7_ValueName NVARCHAR(50),
96 SalesForce_7_Name NVARCHAR(50),
97 SalesOrg_8_ValueID INT,
98 SalesOrg_8_ValueCD VARCHAR(15),
99 SalesOrg_8_ValueName NVARCHAR(50),
100 SalesForce_8_Name NVARCHAR(50),
101 SalesOrg_9_ValueID INT,
102 SalesOrg_9_ValueCD VARCHAR(15),
103 SalesOrg_9_ValueName NVARCHAR(50),
104 SalesForce_9_Name NVARCHAR(50),
105 ParentID INT,
106 ParentLevel INT);
107
108 ---- Lấy thông tin NPP với cây sales org tương ứng
109 INSERT INTO #tmp
110 EXEC dbo.sp_DMS_MDM_GetDistributorWithSalesOrg @_CompanyID;
111
112 SELECT *
113 INTO #tmpPermission
114 FROM dbo.fn_DMS_GetSFPermission(@_CompanyID, @_DistributorID, @_LoginID);
115
116 SELECT CompanyID,
117 DistributorLocationID
118 INTO #tmpPrmDistributor
119 FROM #tmpPermission
120 GROUP BY CompanyID,
121 DistributorLocationID;
122
123 ---- 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
124 SELECT DISTINCT soo.CompanyID,
125 DistributorID = soo.BranchID,
126 soo.OrderNbr,
127 soo.OrderType,
128 soo.OrderDate,
129 soo.CustomerOrderNbr,
130 soo.CustomerID,
131 soo.CustomerLocationID,
132 SalesRepsID = soo.UsrSalesReps,
133 Route = soo.UsrRoute,
134 RouteName = dr.Descr,
135 sol.InventoryID,
136 sol.UnitPrice,
137 sol.LineNbr,
138 OrderQty = COALESCE(sol.UsrOrgQty, sol.BaseOrderQty) * CASE
139 WHEN soo.OrderType = 'CM' THEN -1
140 ELSE 1 END,
141 OrderAmt = COALESCE(sol.UsrOrgQty, sol.BaseOrderQty) * sol.UnitPrice - COALESCE(sol.UsrOrgDiscAmt, 0),
142 sol.ExtPrice,
143 sol.IsFree,
144 sol.ExpireDate,
145 IsDelete = soo.Cancelled,
146 sf.EmployeeID,
147 sf.EmployeeCD,
148 sf.Descr EmployeeName,
149 ss.EmployeeCD SalesSupCD,
150 ss.Descr SalesSupName,
151 soo.OrderDate PDAOrderDate,
152 FreeAmt = NULL, -- discount.FreeAmt,
153 soo.UsrReasonCodeID ReasonCode,
154 soo.InvoiceDate,
155 IsManual = CASE sol.ManualDisc
156 WHEN 1 THEN 'TRUE'
157 ELSE 'FALSE' END,
158 UsrPDAOrderType = CASE
159 WHEN soo.OrderType = 'CM' THEN 'CM'
160 ELSE soo.UsrPDAOrderType END,
161 PDALineAmt = CASE
162 WHEN soo.CustomerOrderNbr IS NOT NULL THEN
163 (sol.UsrOrgQty * sol.UnitPrice * CASE
164 WHEN soo.OrderType = 'CM' THEN -1
165 ELSE 1 END)
166 ELSE 0 END,
167 SalesPrice = sol.UnitPrice,
168 TrueRouteAmt = CASE
169 WHEN soo.UsrValidDate = 1
170 AND soo.CustomerOrderNbr IS NULL THEN sol.LineAmt
171 ELSE 0 END * CASE
172 WHEN soo.OrderType = 'CM' THEN -1
173 ELSE 1 END,
174 NotTrueRouteAmt = CASE
175 WHEN ISNULL(soo.UsrValidDate, 0) <> 1
176 AND soo.CustomerOrderNbr IS NULL THEN sol.LineAmt
177 ELSE 0 END * CASE
178 WHEN soo.OrderType = 'CM' THEN -1
179 ELSE 1 END
180 INTO #tmpAllOrder
181 FROM dbo.SOOrder soo WITH (NOLOCK)
182 JOIN dbo.SOLine sol WITH (NOLOCK)
183 ON sol.CompanyID = soo.CompanyID
184 AND sol.OrderNbr = soo.OrderNbr
185 AND sol.OrderType = soo.OrderType
186 JOIN DMSSalesForce sf WITH (NOLOCK)
187 ON sf.CompanyID = soo.CompanyID
188 AND sf.EmployeeID = soo.UsrSalesReps
189 JOIN DMSRouteSetting r WITH (NOLOCK)
190 ON soo.UsrRoute = r.RouteCD
191 AND soo.CompanyID = r.CompanyID
192 AND r.EffectiveDate <= soo.OrderDate
193 AND soo.OrderDate < ISNULL(r.EndDate, '2222-12-30')
194 JOIN DMSSalesForce ss WITH (NOLOCK)
195 ON ss.CompanyID = r.CompanyID
196 AND ss.EmployeeID = r.SalesForceID
197 JOIN dbo.DMSRoute dr WITH (NOLOCK)
198 ON dr.CompanyID = r.CompanyID
199 AND dr.RouteCD = r.RouteCD
200 WHERE soo.CompanyID = @_CompanyID
201 --AND soo.BranchID = @_DistributorID
202 AND soo.OrderDate BETWEEN @_FromDate AND @_ToDate
203 AND soo.Cancelled = 0
204 AND ( @_SiteID IS NULL
205 OR EXISTS ( SELECT *
206 FROM #tmpSiteAccess s
207 WHERE s.CompanyID = @_CompanyID
208 AND s.SiteID = @_SiteID
209 AND s.SiteID = sol.SiteID))
210 AND (EXISTS ( SELECT *
211 FROM #tmpPermission p
212 WHERE p.CompanyID = @_CompanyID
213 AND p.DistributorID = soo.BranchID))
214 AND ( @_RouteCD IS NULL
215 OR soo.UsrRoute = @_RouteCD)
216 AND ( @_CustomerID IS NULL
217 OR soo.CustomerID = @_CustomerID)
218 AND ( @_SelectedDistributorID IS NULL
219 OR soo.BranchID = @_SelectedDistributorID);
220
221 CREATE NONCLUSTERED INDEX [#tmpAllOrder_index]
222 ON #tmpAllOrder (CompanyID, OrderNbr, OrderType, LineNbr);
223
224 ---- Lấy raw data
225 SELECT ao.CompanyID,
226 ao.DistributorID,
227 ao.OrderNbr,
228 ao.OrderType,
229 OrderDate = MAX(ao.OrderDate),
230 ao.CustomerOrderNbr,
231 ao.CustomerID,
232 ao.CustomerLocationID,
233 ao.SalesRepsID,
234 ao.Route,
235 ao.RouteName,
236 ao.InventoryID,
237 sos.ShipmentNbr,
238 ShipDate = MAX(sos.ShipDate),
239 UnitPrice = MAX(ao.UnitPrice),
240 SKU = CONVERT(DECIMAL(18, 5), 1),
241 TotalOrder = CONVERT(DECIMAL(18, 5), NULL),
242 ao.OrderQty, --= SUM(CASE WHEN ao.OrderType != 'CM'
243 -- AND ao.IsFree = 0 THEN ao.OrderQty
244 -- ELSE 0
245 -- END) ,
246 OrderPromotionQty = SUM(CASE
247 WHEN ao.OrderType != 'CM'
248 AND ao.IsFree = 1 THEN ao.OrderQty
249 ELSE 0 END),
250 OrderAmt = SUM(ao.OrderAmt * CASE
251 WHEN ao.OrderType != 'CM' THEN 1
252 ELSE -1 END),
253 ShippingQty = SUM(CASE
254 WHEN ao.IsFree = 0
255 AND sos.Status = 'N' THEN sosl.BaseShippedQty
256 ELSE 0 END),
257 ShippingPromotionQty = SUM(CASE
258 WHEN ao.IsFree = 1
259 AND sos.Status = 'N' THEN sosl.BaseShippedQty
260 ELSE 0 END),
261 ShippingAmt = SUM(CASE
262 WHEN sos.Status = 'N' THEN ao.ExtPrice
263 ELSE 0 END),
264 ShippedQty = SUM(intr.BaseQty * CASE
265 WHEN ao.OrderType != 'CM' THEN 1
266 ELSE -1 END),
267 ShippedPromotionQty = SUM(CASE
268 WHEN ao.IsFree = 1 THEN intr.BaseQty
269 ELSE 0 END * CASE
270 WHEN ao.OrderType != 'CM' THEN 1
271 ELSE -1 END),
272 ShippedAmt = SUM(intr.TranAmt * CASE
273 WHEN ao.OrderType != 'CM' THEN 1
274 ELSE -1 END),
275 ReceivedQty = CONVERT(DECIMAL(18, 2), NULL),
276 ReceivedPromotionQty = CONVERT(DECIMAL(18, 2), NULL),
277 ReceivedAmt = CONVERT(DECIMAL(18, 2), NULL),
278 ReceiptType = CONVERT(NVARCHAR(100), NULL),
279 ReceiptNbr = CONVERT(NVARCHAR(100), NULL),
280 ReceiptDate = CONVERT(SMALLDATETIME, NULL),
281 RPONbr = CONVERT(NVARCHAR(100), NULL),
282 RequestDate = CONVERT(SMALLDATETIME, NULL),
283 InvoiceNbr = CONVERT(NVARCHAR(100), NULL),
284 InvoiceDate = ao.InvoiceDate, -- CONVERT(SMALLDATETIME, NULL),
285 SiteCD = CONVERT(NVARCHAR(100), NULL),
286 SiteDescr = CONVERT(NVARCHAR(100), NULL),
287 DueDate = CONVERT(SMALLDATETIME, NULL),
288 LotSerialNbr = CONVERT(NVARCHAR(100), NULL),
289 FromSerialCD = CONVERT(NVARCHAR(100), NULL),
290 ToSerialCD = CONVERT(NVARCHAR(100), NULL),
291 ao.IsFree,
292 PDAOrderDate,
293 ao.ExpireDate,
294 ao.FreeAmt,
295 ao.EmployeeID,
296 EmployeeCD,
297 EmployeeName,
298 SalesSupCD,
299 SalesSupName,
300 NetAmt = ao.OrderAmt - ISNULL(ao.FreeAmt, 0),
301 ao.ReasonCode,
302 ao.IsManual,
303 UsrPDAOrderType,
304 PDALineAmt = 0,
305 ao.SalesPrice,
306 TrueRouteAmt = ao.TrueRouteAmt + PDALineAmt + ao.NotTrueRouteAmt,
307 NotTrueRouteAmt = 0,
308 intr.LastModifiedDateTime CompleteDate
309 INTO #tmpRawData
310 FROM #tmpAllOrder ao
311 LEFT JOIN dbo.SOShipLine sosl WITH (NOLOCK)
312 ON sosl.CompanyID = ao.CompanyID
313 AND sosl.OrigOrderNbr = ao.OrderNbr
314 AND sosl.OrigOrderType = ao.OrderType
315 AND sosl.OrigLineNbr = ao.LineNbr
316 LEFT JOIN dbo.SOShipment sos WITH (NOLOCK)
317 ON sos.CompanyID = sosl.CompanyID
318 AND sos.ShipmentNbr = sosl.ShipmentNbr
319 LEFT JOIN dbo.INTran intr WITH (NOLOCK)
320 ON intr.CompanyID = ao.CompanyID
321 AND intr.SOOrderNbr = ao.OrderNbr
322 AND intr.SOOrderType = ao.OrderType
323 AND intr.SOOrderLineNbr = ao.LineNbr
324 AND intr.Released = 1
325 LEFT JOIN dbo.ARTran art WITH (NOLOCK)
326 ON art.CompanyID = intr.CompanyID
327 AND art.RefNbr = intr.ARRefNbr
328 AND art.TranType = intr.ARDocType
329 AND art.LineNbr = intr.ARLineNbr
330 GROUP BY ao.CompanyID,
331 ao.DistributorID,
332 ao.OrderNbr,
333 ao.OrderType,
334 ao.CustomerOrderNbr,
335 ao.CustomerID,
336 ao.CustomerLocationID,
337 ao.SalesRepsID,
338 ao.Route,
339 ao.InventoryID,
340 sos.ShipmentNbr,
341 ao.IsFree,
342 ao.RouteName,
343 PDAOrderDate,
344 ao.ExpireDate,
345 ao.FreeAmt,
346 ao.EmployeeID,
347 EmployeeCD,
348 EmployeeName,
349 SalesSupCD,
350 SalesSupName,
351 ao.OrderAmt - ao.FreeAmt,
352 ao.ReasonCode,
353 ao.OrderAmt,
354 ao.IsManual,
355 ao.UsrPDAOrderType,
356 ao.PDALineAmt,
357 ao.OrderQty,
358 ao.SalesPrice,
359 ao.TrueRouteAmt,
360 ao.NotTrueRouteAmt,
361 intr.LastModifiedDateTime,
362 ao.InvoiceDate;
363
364
365 ---- Lấy Ä‘Æ¡n thá»±c nháºn
366 SELECT so.CompanyID,
367 itr.InventoryID,
368 itr.Qty,
369 itr.LotSerialNbr,
370 sos.InvoiceNbr,
371 sos.UsrInvoiceDate,
372 so.OrderNbr,
373 so.OrderType
374 INTO #tmpInvoice
375 FROM SOOrder so WITH (NOLOCK)
376 JOIN SOOrderShipment sos WITH (NOLOCK)
377 ON sos.CompanyID = so.CompanyID
378 AND sos.OrderNbr = so.OrderNbr
379 AND sos.OrderType = so.OrderType
380 JOIN ARTran art WITH (NOLOCK)
381 ON art.CompanyID = so.CompanyID
382 AND art.SOOrderNbr = so.OrderNbr
383 AND art.SOOrderType = so.OrderType
384 JOIN INTran itr WITH (NOLOCK)
385 ON itr.CompanyID = so.CompanyID
386 AND itr.SOOrderNbr = so.OrderNbr
387 AND itr.SOOrderType = so.OrderType
388 WHERE so.CompanyID = @_CompanyID
389 AND ( ( @_ToDate >= @_FirstDayOfCurrentMonth
390 AND so.OrderDate <= @_ToDate)
391 OR @_ToDate < @_FirstDayOfCurrentMonth)
392 AND (EXISTS ( SELECT *
393 FROM #tmpRawData rd
394 WHERE rd.CompanyID = so.CompanyID
395 AND rd.OrderNbr = so.OrderNbr));
396
397 CREATE NONCLUSTERED INDEX [#tmpInvoice_index]
398 ON #tmpInvoice (CompanyID, OrderNbr, OrderType, InventoryID);
399
400 UPDATE rd
401 SET rd.InvoiceNbr = r.InvoiceNbr,
402 rd.InvoiceDate = r.UsrInvoiceDate,
403 rd.LotSerialNbr = r.LotSerialNbr
404 FROM #tmpRawData rd
405 JOIN #tmpInvoice r
406 ON r.CompanyID = rd.CompanyID
407 AND r.OrderNbr = rd.OrderNbr
408 AND r.OrderType = rd.OrderType
409 AND r.InventoryID = rd.InventoryID;
410
411 CREATE NONCLUSTERED INDEX [#tmpRawData_index]
412 ON #tmpRawData (CompanyID, DistributorID, CustomerID, CustomerLocationID, SalesRepsID, InventoryID);
413 CREATE NONCLUSTERED INDEX [#tmpRawData_index_update]
414 ON #tmpRawData (CompanyID, DistributorID, OrderDate, SalesRepsID);
415
416
417 ---- Lấy dữ liệu KH và NPP đại diện cho KH đó trên HO
418 SELECT dl.CompanyID,
419 dl.LocationID,
420 vcl.CustomerID,
421 vcl.CustomerCD,
422 vcl.CustomerName,
423 t.DistributorLocationID,
424 SalesOrgID = salesorg.SalesOrgCD,
425 t.SalesOrg_0_ValueCD SalesOrg_0_ValueName,
426 t.SalesOrg_1_ValueCD SalesOrg_1_ValueName,
427 t.SalesOrg_2_ValueCD SalesOrg_2_ValueName,
428 t.SalesOrg_3_ValueCD SalesOrg_3_ValueName,
429 t.SalesOrg_4_ValueCD SalesOrg_4_ValueName,
430 t.SalesOrg_5_ValueCD SalesOrg_5_ValueName,
431 t.SalesOrg_6_ValueCD SalesOrg_6_ValueName,
432 t.SalesOrg_7_ValueCD SalesOrg_7_ValueName,
433 t.SalesOrg_8_ValueCD SalesOrg_8_ValueName,
434 t.SalesOrg_9_ValueCD SalesOrg_9_ValueName,
435 AddressLine1 = vcl.AddressLine1 + ' ' + vcl.AddressLine2,
436 AddressFull = ISNULL(NULLIF(vcl.AddressLine1, '') + ', ', '')
437 + ISNULL(NULLIF(vcl.AddressLine2, '') + ', ', '')
438 + ISNULL(NULLIF(vcl.AddressLine3, '') + ', ', '')
439 + ISNULL(NULLIF(vcl.AddressLine4, '') + ', ', '')
440 + ISNULL(NULLIF(vcl.AddressLine5, '') + ', ', '')
441 + ISNULL(NULLIF(vcl.StreetName, '') + ', ', '') + ISNULL(NULLIF(vcl.WardName, ''), ''),
442 dist.StreetName,
443 dist.WardName,
444 dist.ProvinceCD,
445 dist.ProvinceName,
446 dist.DistrictCD,
447 dist.DistrictName,
448 ChannelCD = dist.Attribute0CD,
449 ChannelName = dist.Attribute0Descr,
450 DistributorCD,
451 DistributorName,
452 vcl.Attribute0Descr,
453 vcl.Attribute1Descr,
454 vcl.Attribute2Descr,
455 vcl.Attribute3Descr,
456 vcl.Attribute4Descr,
457 vcl.Attribute5Descr,
458 vcl.Attribute6Descr,
459 vcl.Attribute7Descr,
460 vcl.Attribute8Descr,
461 vcl.Attribute9Descr,
462 mcp.EffectiveDate,
463 mcp.EndDate,
464 dist.DistributorID,
465 vcl.CustomerPhone1,
466 vcl.CustomerPhone2,
467 mcp.RouteCD
468 INTO #tmpCustomerLocation
469 FROM Location dl
470 JOIN DMSViewCustomerLocation vcl WITH (NOLOCK)
471 ON vcl.CompanyID = dl.CompanyID
472 AND vcl.CustomerID = dl.BAccountID
473 AND vcl.LocationID = dl.LocationID
474 JOIN DMSMCPDetail mcp WITH (NOLOCK)
475 ON mcp.CompanyID = dl.CompanyID
476 AND mcp.CustomerID = dl.BAccountID
477 AND mcp.CustomerLocationID = dl.LocationID
478 JOIN #tmp t WITH (NOLOCK)
479 ON t.CompanyID = mcp.CompanyID
480 AND t.DistributorID = mcp.DistributorID
481 AND t.DistributorLocationID = mcp.DistributorLocationID
482 JOIN dbo.DMSViewDistributorLocation dist WITH (NOLOCK)
483 ON dist.CompanyID = mcp.CompanyID
484 AND dist.DistributorID = mcp.DistributorID
485 AND dist.LocationID = t.DistributorLocationID
486 JOIN dbo.DMSSalesOrg salesorg WITH (NOLOCK)
487 ON salesorg.CompanyID = t.CompanyID
488 AND salesorg.SalesOrgID = t.SalesOrgID
489 WHERE dl.CompanyID = @_CompanyID
490 AND EXISTS ( SELECT TOP 1 pd.CompanyID
491 FROM #tmpPrmDistributor pd
492 WHERE pd.CompanyID = t.CompanyID
493 AND pd.DistributorLocationID = t.DistributorLocationID)
494 AND ( @_SalesAreaID IS NULL
495 OR t.SalesOrg_0_ValueID = @_SalesAreaID
496 OR t.SalesOrg_1_ValueID = @_SalesAreaID
497 OR t.SalesOrg_2_ValueID = @_SalesAreaID
498 OR t.SalesOrg_3_ValueID = @_SalesAreaID
499 OR t.SalesOrg_4_ValueID = @_SalesAreaID
500 OR t.SalesOrg_5_ValueID = @_SalesAreaID
501 OR t.SalesOrg_6_ValueID = @_SalesAreaID
502 OR t.SalesOrg_7_ValueID = @_SalesAreaID
503 OR t.SalesOrg_8_ValueID = @_SalesAreaID
504 OR t.SalesOrg_9_ValueID = @_SalesAreaID);
505
506 CREATE NONCLUSTERED INDEX [#tmpCustomerLocation_index]
507 ON #tmpCustomerLocation (CompanyID, DistributorID, CustomerID, LocationID, EffectiveDate, EndDate, RouteCD);
508
509
510 SELECT vii.CompanyID,
511 vii.InventoryID,
512 vii.InventoryCD,
513 vii.InventoryName,
514 vii.InventoryShortName,
515 vii.InventoryAbbrName,
516 vii.BaseUnit,
517 vii.PurchaseUnit,
518 vii.Hierachy0Descr,
519 vii.Hierachy1Descr,
520 vii.Hierachy2Descr,
521 vii.Hierachy3Descr,
522 vii.Hierachy4Descr,
523 vii.Hierachy5Descr,
524 vii.Hierachy6Descr,
525 vii.Hierachy7Descr,
526 vii.Hierachy8Descr,
527 vii.Hierachy9Descr,
528 vii.ItemStatus,
529 vii.ItemClass,
530 vii.Attribute0Descr,
531 vii.Attribute1Descr,
532 vii.Attribute2Descr,
533 vii.Attribute3Descr,
534 vii.Attribute4Descr,
535 vii.Attribute5Descr,
536 vii.Attribute6Descr,
537 vii.Attribute7Descr,
538 vii.Attribute8Descr,
539 vii.Attribute9Descr
540 INTO #tmpInventoryItem
541 FROM dbo.DMSViewInventoryItem vii WITH (NOLOCK)
542 WHERE vii.CompanyID = @_CompanyID
543 AND ( @_HierarchyID IS NULL
544 OR vii.Hierachy0ID = @_HierarchyID
545 OR vii.Hierachy1ID = @_HierarchyID
546 OR vii.Hierachy2ID = @_HierarchyID
547 OR vii.Hierachy3ID = @_HierarchyID
548 OR vii.Hierachy4ID = @_HierarchyID
549 OR vii.Hierachy5ID = @_HierarchyID
550 OR vii.Hierachy6ID = @_HierarchyID
551 OR vii.Hierachy7ID = @_HierarchyID
552 OR vii.Hierachy8ID = @_HierarchyID
553 OR vii.Hierachy9ID = @_HierarchyID)
554 AND ( @_AttributeID IS NULL
555 OR vii.Attribute0ID = @_AttributeID
556 OR vii.Attribute1ID = @_AttributeID
557 OR vii.Attribute2ID = @_AttributeID
558 OR vii.Attribute3ID = @_AttributeID
559 OR vii.Attribute4ID = @_AttributeID
560 OR vii.Attribute5ID = @_AttributeID
561 OR vii.Attribute6ID = @_AttributeID
562 OR vii.Attribute7ID = @_AttributeID
563 OR vii.Attribute8ID = @_AttributeID
564 OR vii.Attribute9ID = @_AttributeID);
565
566 CREATE NONCLUSTERED INDEX [#tmpInventoryItem_index]
567 ON #tmpInventoryItem (CompanyID, InventoryID);
568
569 SELECT cl.CompanyID,
570 cl.SalesOrgID,
571 cl.SalesOrg_0_ValueName,
572 cl.SalesOrg_1_ValueName,
573 cl.SalesOrg_2_ValueName,
574 cl.SalesOrg_3_ValueName,
575 cl.SalesOrg_4_ValueName,
576 cl.SalesOrg_5_ValueName,
577 cl.SalesOrg_6_ValueName,
578 cl.SalesOrg_7_ValueName,
579 cl.SalesOrg_8_ValueName,
580 cl.SalesOrg_9_ValueName,
581 cl.DistributorCD,
582 cl.DistributorName,
583 cl.CustomerCD,
584 cl.CustomerName,
585 cl.AddressFull,
586 cl.ProvinceCD,
587 cl.ProvinceName,
588 cl.ChannelCD,
589 cl.ChannelName,
590 cl.DistrictCD,
591 cl.DistrictName,
592 cl.CustomerPhone1,
593 cl.CustomerPhone2,
594 cl.Attribute0Descr,
595 cl.Attribute1Descr,
596 cl.Attribute2Descr,
597 cl.Attribute3Descr,
598 cl.Attribute4Descr,
599 cl.Attribute5Descr,
600 cl.Attribute6Descr,
601 cl.Attribute7Descr,
602 cl.Attribute8Descr,
603 cl.Attribute9Descr,
604 cl.StreetName,
605 cl.WardName,
606 cl.AddressLine1,
607 rd.OrderNbr,
608 rd.OrderType,
609 rd.OrderDate,
610 rd.CustomerOrderNbr,
611 rd.ShipmentNbr,
612 rd.ShipDate,
613 ii.InventoryID,
614 ii.InventoryCD,
615 ii.InventoryName,
616 ii.InventoryShortName,
617 ii.InventoryAbbrName,
618 ii.BaseUnit,
619 ii.Hierachy0Descr,
620 ii.Hierachy1Descr,
621 ii.Hierachy2Descr,
622 ii.Hierachy3Descr,
623 ii.Hierachy4Descr,
624 ii.Hierachy5Descr,
625 ii.Hierachy6Descr,
626 ii.Hierachy7Descr,
627 ii.Hierachy8Descr,
628 ii.Hierachy9Descr,
629 ii.ItemClass,
630 INAttribute0Descr = ii.Attribute0Descr,
631 INAttribute1Descr = ii.Attribute1Descr,
632 INAttribute2Descr = ii.Attribute2Descr,
633 INAttribute3Descr = ii.Attribute3Descr,
634 INAttribute4Descr = ii.Attribute4Descr,
635 INAttribute5Descr = ii.Attribute5Descr,
636 INAttribute6Descr = ii.Attribute6Descr,
637 INAttribute7Descr = ii.Attribute7Descr,
638 INAttribute8Descr = ii.Attribute8Descr,
639 INAttribute9Descr = ii.Attribute9Descr,
640 rd.UnitPrice,
641 rd.OrderQty,
642 rd.OrderPromotionQty,
643 rd.OrderAmt,
644 rd.ShippingQty,
645 rd.ShippingPromotionQty,
646 rd.ShippingAmt,
647 rd.ShippedQty,
648 rd.ShippedPromotionQty,
649 rd.ShippedAmt,
650 rd.ReceivedQty,
651 rd.ReceivedPromotionQty,
652 rd.ReceivedAmt,
653 Revenue = rd.ShippedAmt,
654 SalesOut = rd.ShippedQty,
655 --Quang: Thêm mỗi cột Qty 2 cột 'Số lượng theo đơn vị mua' và 'Số lượng lẻ'.
656 OrderQtyPurchaseUnit = FLOOR(rd.OrderQty / (CASE
657 WHEN iu.UnitRate IS NULL
658 OR iu.UnitRate = 0 THEN 1
659 ELSE iu.UnitRate END)),
660 OrderQtySalesUnit = rd.OrderQty % (CASE
661 WHEN iu.UnitRate IS NULL
662 OR iu.UnitRate = 0 THEN 1
663 ELSE iu.UnitRate END),
664 OrderPromotionQtyPurchaseUnit = FLOOR(rd.OrderPromotionQty / (CASE
665 WHEN iu.UnitRate IS NULL
666 OR iu.UnitRate = 0 THEN 1
667 ELSE iu.UnitRate END)),
668 OrderPromotionQtySalesUnit = rd.OrderPromotionQty % (CASE
669 WHEN iu.UnitRate IS NULL
670 OR iu.UnitRate = 0 THEN 1
671 ELSE iu.UnitRate END),
672 ShippingQtyPurchaseUnit = FLOOR(rd.ShippingQty / (CASE
673 WHEN iu.UnitRate IS NULL
674 OR iu.UnitRate = 0 THEN 1
675 ELSE iu.UnitRate END)),
676 ShippingQtySalesUnit = rd.ShippingQty % (CASE
677 WHEN iu.UnitRate IS NULL
678 OR iu.UnitRate = 0 THEN 1
679 ELSE iu.UnitRate END),
680 ShippingPromotionQtyPurchaseUnit = FLOOR(rd.ShippingPromotionQty / (CASE
681 WHEN iu.UnitRate IS NULL
682 OR iu.UnitRate = 0 THEN 1
683 ELSE iu.UnitRate END)),
684 ShippingPromotionQtySalesUnit = rd.ShippingPromotionQty % (CASE
685 WHEN iu.UnitRate IS NULL
686 OR iu.UnitRate = 0 THEN 1
687 ELSE iu.UnitRate END),
688 ShippedQtyPurchaseUnit = FLOOR(rd.ShippedQty / (CASE
689 WHEN iu.UnitRate IS NULL
690 OR iu.UnitRate = 0 THEN 1
691 ELSE iu.UnitRate END)),
692 ShippedQtySalesUnit = rd.ShippedQty % (CASE
693 WHEN iu.UnitRate IS NULL
694 OR iu.UnitRate = 0 THEN 1
695 ELSE iu.UnitRate END),
696 ShippedPromotionQtyPurchaseUnit = FLOOR(rd.ShippedPromotionQty / (CASE
697 WHEN iu.UnitRate IS NULL
698 OR iu.UnitRate = 0 THEN 1
699 ELSE iu.UnitRate END)),
700 ShippedPromotionQtySalesUnit = rd.ShippedPromotionQty % (CASE
701 WHEN iu.UnitRate IS NULL
702 OR iu.UnitRate = 0 THEN 1
703 ELSE iu.UnitRate END),
704 Rate = CONVERT(DECIMAL(18, 2), NULL),
705 rd.ReceiptType,
706 rd.ReceiptNbr,
707 rd.ReceiptDate,
708 rd.RPONbr,
709 rd.RequestDate,
710 rd.InvoiceNbr,
711 rd.SiteCD,
712 rd.SiteDescr,
713 rd.DueDate,
714 rd.LotSerialNbr,
715 rd.FromSerialCD,
716 rd.ToSerialCD,
717 rd.IsFree,
718 rd.Route,
719 rd.RouteName,
720 TrueRoute = CASE
721 WHEN rd.Route IS NULL THEN N'Không'
722 ELSE N'Có' END,
723 iu.UnitRate,
724 rd.PDAOrderDate,
725 rd.CustomerOrderNbr PDAOrderNbr,
726 rd.ExpireDate,
727 rd.InvoiceDate,
728 rd.FreeAmt,
729 rd.EmployeeID,
730 rd.EmployeeCD,
731 rd.EmployeeName,
732 rd.SalesSupCD,
733 rd.SalesSupName,
734 rd.NetAmt,
735 rd.ReasonCode,
736 rd.IsManual,
737 rc.[Desc] ReasonCodeDesc,
738 UsrPDAOrderType PDAOrderType,
739 PDALineAmt,
740 SalesPrice,
741 TrueRouteAmt,
742 NotTrueRouteAmt,
743 rd.CompleteDate
744 INTO #tmpResult
745 FROM #tmpRawData rd
746 JOIN #tmpCustomerLocation cl
747 ON cl.CompanyID = rd.CompanyID
748 AND cl.CustomerID = rd.CustomerID
749 AND cl.LocationID = rd.CustomerLocationID
750 --AND cl.EffectiveDate <= rd.OrderDate
751 --AND (ISNULL(cl.EndDate, '2200-12-30') >= rd.OrderDate)
752 AND cl.DistributorID = rd.DistributorID
753 AND rd.Route = cl.RouteCD
754 LEFT JOIN #tmpInventoryItem ii
755 ON ii.CompanyID = rd.CompanyID
756 AND ii.InventoryID = rd.InventoryID
757 LEFT JOIN dbo.INUnit iu WITH (NOLOCK)
758 ON iu.CompanyID = ii.CompanyID
759 AND iu.InventoryID = ii.InventoryID
760 AND iu.FromUnit = ii.PurchaseUnit
761 AND iu.ToUnit = ii.BaseUnit
762 AND iu.UnitMultDiv = 'M'
763 LEFT JOIN DMSSOReasonCode rc WITH (NOLOCK)
764 ON rc.CompanyID = rd.CompanyID
765 AND rc.ReasonCodeID = rd.ReasonCode;
766
767 SELECT SalesOrgID,
768 ChannelName Channel,
769 SalesOrg_0_ValueName,
770 SalesOrg_1_ValueName,
771 SalesOrg_2_ValueName,
772 SalesOrg_3_ValueName,
773 SalesOrg_4_ValueName,
774 SalesOrg_5_ValueName,
775 SalesOrg_6_ValueName,
776 SalesOrg_7_ValueName,
777 SalesOrg_8_ValueName,
778 SalesOrg_9_ValueName,
779 DistributorCD,
780 DistributorName,
781 Route,
782 RouteName,
783 TrueRoute,
784 ------------Thiếu ngà y viếng thăm
785 EmployeeID,
786 EmployeeCD,
787 EmployeeName,
788 SalesSupCD,
789 SalesSupName,
790 CustomerCD,
791 CustomerName,
792 AddressFull,
793 StreetName,
794 WardName,
795 ProvinceName Province,
796 DistrictName District,
797 Attribute0Descr,
798 Attribute1Descr,
799 Attribute2Descr,
800 Attribute3Descr,
801 Attribute4Descr,
802 Attribute5Descr,
803 Attribute6Descr,
804 Attribute7Descr,
805 Attribute8Descr,
806 Attribute9Descr,
807 Hierachy0Descr,
808 Hierachy1Descr,
809 Hierachy2Descr,
810 Hierachy3Descr,
811 Hierachy4Descr,
812 Hierachy5Descr,
813 Hierachy6Descr,
814 Hierachy7Descr,
815 Hierachy8Descr,
816 Hierachy9Descr,
817 ItemClass,
818 INAttribute0Descr,
819 INAttribute1Descr,
820 INAttribute2Descr,
821 INAttribute3Descr,
822 INAttribute4Descr,
823 INAttribute5Descr,
824 INAttribute6Descr,
825 INAttribute7Descr,
826 INAttribute8Descr,
827 INAttribute9Descr,
828 InventoryCD,
829 InventoryName,
830 InventoryShortName,
831 UnitRate,
832 BaseUnit,
833 OrderType,
834 PDAOrderDate,
835 OrderNbr,
836 OrderDate,
837 r.OrderQty,
838 ShippedQty,
839 ReceiptDate,
840 LotSerialNbr,
841 ExpireDate,
842 InvoiceNbr,
843 InvoiceDate,
844 IsFree,
845 OrderAmt,
846 FreeAmt,
847 NetAmt,
848 rc.[Desc] ReasonCode,
849 IsReturn = CASE ReceiptType
850 WHEN 'RN' THEN N'Có'
851 ELSE N'Không' END,
852 PDAOrderNbr,
853 --VisitDate = CASE DATEPART(dw, ISNULL(PDAOrderDate, OrderDate))
854 -- WHEN 2 THEN 'Monday'
855 -- WHEN 3 THEN 'Tuesday'
856 -- WHEN 4 THEN 'Wednesday'
857 -- WHEN 5 THEN 'Thursday'
858 -- WHEN 6 THEN 'Friday'
859 -- WHEN 7 THEN 'Saturday' END,
860 ChannelName,
861 --ReceiptType = CASE ReceiptType
862 -- WHEN 'RT' THEN 'Receipt'
863 -- WHEN 'RN' THEN 'Return'
864 -- ELSE 'Transfer Receipt' END,
865 ReceiptNbr,
866 ReceiptDate = CONVERT(VARCHAR(24), ReceiptDate, 103),
867 RPONbr,
868 RequestDate = CONVERT(VARCHAR(24), RequestDate, 103),
869 ReceivedQty,
870 SiteDescr,
871 ReceivedAmt,
872 DueDate = CONVERT(VARCHAR(24), DueDate, 103),
873 FromSerialCD,
874 ToSerialCD,
875 IsManual,
876 ReasonCodeDesc,
877 CompleteDate,
878 Commando = CASE DATEPART(dw, PDAOrderDate)
879 WHEN 1 THEN N'Có'
880 ELSE N'Không' END,
881 PDAOrderType = CASE PDAOrderType
882 WHEN 'Pre' THEN N'Äặt trÆ°á»›c giao sau'
883 WHEN 'Van' THEN N'Bán trực tiếp'
884 ELSE '' END,
885 PDALineAmt,
886 SKU = CASE
887 WHEN OrderType != 'CM'
888 AND IsFree = 0 THEN 1
889 WHEN OrderType != 'CM'
890 AND IsFree = 1 THEN 0
891 ELSE -1 END,
892 PromotionQtyPurchaseUnit = CASE
893 WHEN IsFree = 1
894 AND OrderType != 'CM' THEN OrderQty
895 WHEN IsFree = 1
896 AND OrderType = 'CM' THEN OrderQty * -1
897 ELSE 0 END,
898 SalesPrice,
899 TrueRouteAmt,
900 NotTrueRouteAmt,
901 Revenue,
902 r.CustomerPhone1,
903 r.CustomerPhone2,
904 [Month] = MONTH(r.OrderDate),
905 [Year] = YEAR(r.OrderDate),
906 r.AddressLine1
907 FROM #tmpResult r
908 LEFT JOIN dbo.DMSSOReasonCode rc
909 ON rc.CompanyID = r.CompanyID
910 AND rc.ReasonCodeID = r.ReasonCode
911 ORDER BY CustomerCD;
912END;