· 6 years ago · Oct 01, 2019, 02:18 AM
1USE [THMilkBaseline2]
2GO
3/****** Object: StoredProcedure [dbo].[sp_DMS_Baseline_RawSales] Script Date: 10/1/2019 9:15:00 AM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8-- =============================================
9-- Author: Dinh Hoang Lam
10-- Create date: 2015/04/02
11-- Description:
12-- =============================================
13-- EXEC sp_DMS_Baseline_RawSales 3, '2018/01/01', '2018/08/22', null
14ALTER PROCEDURE [dbo].[sp_DMS_Baseline_RawSales]
15 @CompanyID INT
16 , @FromDate DATETIME
17 , @ToDate DATETIME
18 , @DistributorID VARCHAR(MAX)
19AS
20BEGIN
21 SET NOCOUNT ON
22
23 DECLARE @_CompanyID INT = @CompanyID
24 DECLARE @_FromDate DATETIME = @FromDate
25 DECLARE @_ToDate DATETIME = DATEADD(dd, 1, @ToDate)
26 DECLARE @_DistributorID VARCHAR(MAX) = NULLIF(LTRIM(RTRIM(@DistributorID)), '')
27
28 BEGIN TRY
29 DROP TABLE #tmpDistributor
30 END TRY
31 begin catch
32 print 'table does not exist'
33 end catch
34
35 SELECT * INTO #tmpDistributor FROM dbo.fn_DMS_ConvertIDListToTable(@_DistributorID)
36
37 IF @_DistributorID IS NULL
38 BEGIN
39 INSERT INTO #tmpDistributor
40 (
41 ID
42 )
43 SELECT BranchID FROM THMilkDMS2..Branch
44 END
45
46/* Hieu : Select thừa, không dùng tới
47 SELECT
48 CompanyID
49 , DistributorID = BranchID
50 , ClosedDate
51 INTO #tmpClosedDate
52 FROM
53 THMilkDMS2..DMSBaselineClosedDate
54 WHERE
55 CompanyID = @_CompanyID
56 AND (@_DistributorID IS NULL OR EXISTS (SELECT TOP 1 * FROM #tmpDistributor WHERE ID = BranchID))
57
58
59
60
61*/
62 ---- Xóa dữ liệu đã được baseline
63 DELETE FROM DMSBLRawSales
64 WHERE
65 CompanyID = @_CompanyID
66 AND OrderDate >= @_FromDate
67 AND OrderDate < @_ToDate
68 AND DistributorID IN (SELECT ID FROM #tmpDistributor)
69
70
71
72 --DELETE FROM DMSBLRawSales
73 --WHERE
74 -- CompanyID = @_CompanyID
75 -- AND LastModifiedDateTime >= @_FromDate
76 -- AND LastModifiedDateTime < @_ToDate
77 -- AND (@_DistributorID IS NULL OR EXISTS (SELECT TOP 1 * FROM #tmpDistributor WHERE ID = DistributorID))
78
79 ---- Lấy dữ liệu đã hoàn tất trong khoảng thời gian baseline
80 SELECT
81 MonthID = RIGHT('0' + CONVERT(VARCHAR(2), MONTH(soo.OrderDate)), 2) + CONVERT(CHAR(4), YEAR(soo.OrderDate))
82 , soo.CompanyID
83 , DistributorID = soo.BranchID
84 , DistributorLocationID = 0
85 , BLDistributorLocationID = 0
86 , soo.OrderNbr
87 , soo.OrderType
88 , soo.OrderDate
89 , soo.CustomerOrderNbr
90 , soo.CustomerID
91 , soo.CustomerLocationID
92 , BLCustomerLocationID = 0
93 , SalespersonID = soo.UsrSalesReps
94 , BLSalespersonID = 0
95 , OrigOrderNbr = NULLIF(LTRIM(RTRIM(soo.OrigOrderNbr)), '')
96 , soo.OrigOrderType
97 , Route = ISNULL(NULLIF(LTRIM(RTRIM(soo.UsrRoute)), ''), '')
98 , SourceType = soo.UsrSourceType
99 , IsHasTMK = ISNULL(soo.UsrIsHasTMK, 0)
100 , IsDispose = ISNULL(soo.UsrIsDispose, 0)
101 , IsRevenueDeduction = ISNULL(soo.UsrIsRevenueDeduction, 0)
102 , Multi = CASE WHEN ISNULL(soo.UsrIsRevenueDeduction, 0) = 0 THEN 1 ELSE -1 END
103 , IsValidDate = ISNULL(soo.UsrValidDate, 0)
104 , IsValidDistance = ISNULL(soo.UsrValidDistance, 0)
105 , CancelReasonCode = NULL
106 , ShipmentNbr = NULL
107 , ShipmentDate = NULL
108 , InvoiceNbr = arr.RefNbr
109 , InvoiceType = arr.DocType
110 , InvoiceDate = arr.DocDate
111 , InvtRefNbr = inr.RefNbr
112 , InvtDocType = inr.DocType
113 , InvtDate = inr.TranDate
114 , inr.LastModifiedDateTime
115 , soo.UsrPDAOrderDate
116 INTO #tmpReleasedDocument
117 FROM
118 THMilkDMS2..SOOrder soo WITH (NOLOCK)
119 JOIN THMilkDMS2..SOOrderShipment soos WITH (NOLOCK) ON soos.CompanyID = soo.CompanyID
120 AND soos.OrderNbr = soo.OrderNbr
121 AND soos.OrderType = soo.OrderType
122 JOIN THMilkDMS2..ARRegister arr WITH (NOLOCK) ON arr.CompanyID = soos.CompanyID
123 AND arr.RefNbr = soos.InvoiceNbr
124 AND arr.DocType = soos.InvoiceType
125 JOIN THMilkDMS2..INRegister inr WITH (NOLOCK) ON inr.CompanyID = soos.CompanyID
126 AND inr.RefNbr = soos.InvtRefNbr
127 AND inr.DocType = soos.InvtDocType
128 AND inr.Released = 1
129 WHERE
130 soo.CompanyID = @_CompanyID
131 AND soo.OrderDate >= @_FromDate
132 AND soo.OrderDate < @_ToDate
133 AND (@_DistributorID IS NULL OR EXISTS (SELECT * FROM #tmpDistributor WHERE ID = soo.BranchID))
134
135 CREATE NONCLUSTERED INDEX [#tmpReleasedDocument_index] ON #tmpReleasedDocument(MonthID, CompanyID, DistributorID, OrderNbr, OrderType)
136 CREATE NONCLUSTERED INDEX [#tmpReleasedDocument_index1] ON #tmpReleasedDocument([CompanyID],[SalespersonID],[OrderDate])
137 CREATE NONCLUSTERED INDEX [#tmpReleasedDocument_index2] ON #tmpReleasedDocument([CompanyID],[DistributorID],[CustomerID],[CustomerLocationID],[OrderDate])
138 CREATE NONCLUSTERED INDEX [#tmpReleasedDocument_index3] ON #tmpReleasedDocument([OrderType],[IsDispose],[IsRevenueDeduction],[OrigOrderNbr])INCLUDE ([CompanyID],[DistributorID],[OrigOrderType])
139 ---- Xóa dữ liệu những tháng đã được baseline tháng
140 DELETE rd
141 FROM #tmpReleasedDocument rd
142 WHERE
143 EXISTS (SELECT TOP 1 * FROM dbo.DMSBLMonthlyHistory blmh WHERE blmh.CompanyID = rd.CompanyID AND blmh.MonthID = rd.MonthID)
144
145 ---- Cập nhật thông tin baseline của khách hàng
146 UPDATE rd
147 SET
148 BLCustomerLocationID = blcl.ID
149 FROM
150 #tmpReleasedDocument rd
151 JOIN dbo.DMSBLCustomerLocation blcl WITH (NOLOCK) ON blcl.CompanyID = rd.CompanyID
152 AND blcl.CustomerID = rd.CustomerID
153 AND blcl.LocationID = rd.CustomerLocationID
154 WHERE
155 rd.OrderDate BETWEEN blcl.EffectiveDate AND ISNULL(blcl.ExpirationDate, @_ToDate)
156
157 ---- Cập nhật thông tin baseline của NVBH
158 UPDATE rd
159 SET
160 BLSalespersonID = blsf.ID
161 FROM
162 #tmpReleasedDocument rd
163 JOIN dbo.DMSBLSalesForce blsf WITH (NOLOCK) ON blsf.CompanyID = rd.CompanyID
164 AND blsf.EmployeeID = rd.SalespersonID
165 WHERE
166 rd.OrderDate BETWEEN blsf.EffectiveDate AND ISNULL(blsf.ExpirationDate, @_ToDate)
167
168 ---- Cập nhật dữ liệu đúng tuyến hay không
169 UPDATE rd
170 SET
171 rd.IsValidDate = CASE WHEN mcp.VisitDate IS NOT NULL OR mcp.SalespersonID IS NULL THEN 1 ELSE 0 END
172 FROM
173 #tmpReleasedDocument rd
174 LEFT JOIN ( SELECT * FROM dbo.DMSBLMCP m WITH (NOLOCK) where VisitDate between @_FromDate AND @_ToDate
175 UNION ALL select * from DMSBLMCPHistory mcph WITH (NOLOCK) where VisitDate between @_FromDate AND @_ToDate) mcp ON mcp.CompanyID = rd.CompanyID
176 AND mcp.VisitDate = rd.OrderDate
177 AND mcp.SalespersonID = rd.SalespersonID
178 AND mcp.CustomerID = rd.CustomerID
179 AND mcp.CustomerLocationID = rd.CustomerLocationID
180
181 ---- Cập nhật dữ liêu distributor location
182 UPDATE rd
183 SET
184 rd.DistributorLocationID = mcp.DistributorLocationID
185 FROM
186 #tmpReleasedDocument rd
187 INNER JOIN dbo.DMSBLMCPSettings mcp WITH (NOLOCK) ON mcp.CompanyID = rd.CompanyID
188 AND mcp.DistributorID = rd.DistributorID
189 AND mcp.CustomerID = rd.CustomerID
190 AND mcp.CustomerLocationID = rd.CustomerLocationID
191 AND rd.OrderDate BETWEEN mcp.EffectiveDate AND ISNULL(mcp.ExpirationDate, rd.OrderDate)
192
193 ---- Cập nhật thông tin baseline của NPP
194 UPDATE rd
195 SET
196 BLDistributorLocationID = bldl.ID
197 FROM
198 #tmpReleasedDocument rd
199 INNER JOIN dbo.DMSBLDistributorLocation bldl WITH (NOLOCK) ON bldl.CompanyID = rd.CompanyID
200 AND bldl.DistributorID = rd.DistributorID
201 AND bldl.LocationID = rd.DistributorLocationID
202 WHERE
203 rd.OrderDate BETWEEN bldl.EffectiveDate AND ISNULL(bldl.ExpirationDate, @_ToDate)
204
205 ---- Lấy dữ liệu distinct theo đơn hàng
206 SELECT
207 MonthID
208 , CompanyID
209 , DistributorID
210 , OrderNbr
211 , OrderType
212 , OrderDate
213 INTO #tmpReleasedDocumentByOrder
214 FROM
215 #tmpReleasedDocument
216 GROUP BY
217 MonthID
218 , CompanyID
219 , DistributorID
220 , OrderNbr
221 , OrderType
222 , OrderDate
223
224 CREATE NONCLUSTERED INDEX [#tmpReleasedDocumentByOrder_index] ON #tmpReleasedDocumentByOrder(MonthID, CompanyID, DistributorID, OrderNbr, OrderType)
225
226 ---- Cập nhật trạng thái IsDispose cho các đơn đã trả hàng nhưng chưa đóng ngày
227 ---- Lấy ra danh sách đơn CM hủy đơn hàng
228 SELECT
229 CompanyID
230 , DistributorID
231 , OrigOrderNbr
232 , OrigOrderType
233 INTO #tmpCMOrder
234 FROM
235 #tmpReleasedDocument
236 WHERE
237 OrderType = 'CM'
238 AND IsDispose = 0
239 AND OrigOrderNbr IS NOT NULL
240 AND IsRevenueDeduction = 0
241 GROUP BY
242 CompanyID
243 , DistributorID
244 , OrigOrderNbr
245 , OrigOrderType
246
247 CREATE NONCLUSTERED INDEX [#tmpCMOrder_index] ON #tmpCMOrder(CompanyID, OrigOrderNbr, OrigOrderType)
248
249 ---- Đánh dấu những đơn cũ thành IsDispose, những đơn đã đóng ngày thì không cần đánh dấu
250 UPDATE rt
251 SET IsDispose = 1
252 FROM
253 dbo.DMSBLRawSales rt WITH (NOLOCK)
254 Inner JOIN #tmpCMOrder cmo ON cmo.CompanyID = rt.CompanyID
255 AND cmo.OrigOrderNbr = rt.OrderNbr
256 AND cmo.OrigOrderType = rt.OrderType
257
258 SELECT
259 --RowNumber = ROW_NUMBER() OVER(PARTITION BY rd.CompanyID, rd.BranchID, rd.OrderNbr, rd.OrderType
260 -- , rd.InvtRefNbr, rd.InvtDocType, intr.LineNbr
261 -- ORDER BY ints.BaseQty)
262 rdbo.MonthID
263 , rdbo.CompanyID
264 , rdbo.DistributorID
265 , rdbo.OrderNbr
266 , rdbo.OrderDate
267 , rdbo.OrderType
268 , OrderLineNbr = sol.LineNbr
269 , InvoiceNbr = art.RefNbr
270 , InvoiceType = art.TranType
271 , InvtRefNbr = intr.RefNbr
272 , InvtDocType = intr.DocType
273 , InvtLineNbr = intr.LineNbr
274 , InvtSplitLineNbr = ints.SplitLineNbr
275 , ints.InventoryID
276 , BLInventoryID = 0
277 , ints.SiteID
278 , ints.LocationID
279 , BLSiteID = 0
280 , AllocationID = sol.UsrAllocationID
281 , sol.UOM
282 , OrigUnitPrice = ISNULL(NULLIF(sol.UsrOrgBasePrice, 0), sol.UnitPrice)
283 , sol.UnitPrice
284 , sol.IsFree
285 , sol.UsrIsManualDisc ManualDisc
286 , OrderQty = ISNULL(sol.UsrOrgQty, sol.BaseOrderQty)
287 , TotalShippedQty = intr.BaseQty
288 , SplitShippedQty = ints.BaseQty
289 , ints.LotSerialNbr
290 , ints.ExpireDate
291 , TotalTaxAmt = art.TaxAmt
292 , SplitTaxAmt = art.TaxAmt * ints.BaseQty / intr.BaseQty
293 , TotalDiscAmt = art.DiscAmt
294 , SplitDiscAmt = art.DiscAmt * ints.BaseQty / intr.BaseQty
295 , TotalTranAmt = art.TranAmt
296 , SplitTranAmt = art.TranAmt * ints.BaseQty / intr.BaseQty
297 , sol.ReasonCode
298 , sol.UsrPromotionID
299 INTO #tmpReleasedDocumentDetail
300 FROM
301 #tmpReleasedDocumentByOrder rdbo
302 JOIN THMilkDMS2..SOLine sol WITH (NOLOCK) ON sol.CompanyID = rdbo.CompanyID
303 AND sol.BranchID = rdbo.DistributorID
304 AND sol.OrderNbr = rdbo.OrderNbr
305 AND sol.OrderType = rdbo.OrderType
306 JOIN THMilkDMS2..INTran intr WITH (NOLOCK) ON intr.CompanyID = sol.CompanyID
307 AND intr.BranchID = sol.BranchID
308 AND intr.SOOrderNbr = sol.OrderNbr
309 AND intr.SOOrderType = sol.OrderType
310 AND intr.SOOrderLineNbr = sol.LineNbr
311 JOIN THMilkDMS2..ARTran art WITH (NOLOCK) ON art.CompanyID = intr.CompanyID
312 AND art.BranchID = intr.BranchID
313 AND art.RefNbr = intr.ARRefNbr
314 AND art.TranType = intr.ARDocType
315 AND art.LineNbr = intr.ARLineNbr
316 JOIN THMilkDMS2..INTranSplit ints WITH (NOLOCK) ON ints.CompanyID = intr.CompanyID
317 AND ints.RefNbr = intr.RefNbr
318 AND ints.DocType = intr.DocType
319 AND ints.LineNbr = intr.LineNbr
320 WHERE
321 intr.BaseQty > 0
322
323 CREATE NONCLUSTERED INDEX [#tmpReleasedDocumentDetail_index] ON #tmpReleasedDocumentDetail(MonthID, CompanyID, DistributorID, OrderNbr, OrderType, SiteID, LocationID, InventoryID)
324 CREATE NONCLUSTERED INDEX [#tmpReleasedDocumentDetail_index1] ON #tmpReleasedDocumentDetail([CompanyID],[InventoryID],[OrderDate])
325 CREATE NONCLUSTERED INDEX [#tmpReleasedDocumentDetail_index2] ON #tmpReleasedDocumentDetail([CompanyID],[DistributorID],[OrderNbr],[OrderType],[InvtRefNbr],[InvtDocType])
326INCLUDE ([MonthID],[OrderLineNbr],[InvtSplitLineNbr],[InventoryID],[BLInventoryID],[SiteID],[LocationID],[BLSiteID],[AllocationID],[UOM],[OrigUnitPrice],[UnitPrice],[IsFree],[ManualDisc],[OrderQty],[SplitShippedQty],[LotSerialNbr],[ExpireDate],[SplitTaxAmt],[SplitDiscAmt],[SplitTranAmt])
327 CREATE NONCLUSTERED INDEX [#tmpReleasedDocumentDetail_index3] ON #tmpReleasedDocumentDetail([CompanyID],[SiteID],[LocationID],[OrderDate])INCLUDE ([BLSiteID])
328
329 ---- Cập nhật các thông tin baseline
330 ---- Cập nhật thông tin baseline của hàng hóa
331 UPDATE rdd
332 SET
333 BLInventoryID = blii.ID
334 FROM
335 #tmpReleasedDocumentDetail rdd
336 JOIN dbo.DMSBLInventoryItem blii WITH (NOLOCK) ON blii.CompanyID = rdd.CompanyID
337 AND blii.InventoryID = rdd.InventoryID
338 WHERE
339 rdd.OrderDate BETWEEN blii.EffectiveDate AND ISNULL(blii.ExpirationDate, @_ToDate)
340
341
342 ---- Cập nhật thông tin baseline của kho
343 UPDATE rdd
344 SET
345 BLSiteID = bls.ID
346 FROM
347 #tmpReleasedDocumentDetail rdd
348 JOIN dbo.DMSBLINSite bls WITH (NOLOCK) ON bls.CompanyID = rdd.CompanyID
349 AND bls.SiteID = rdd.SiteID
350 AND bls.LocationID = rdd.LocationID
351 WHERE
352 rdd.OrderDate BETWEEN bls.EffectiveDate AND ISNULL(bls.ExpirationDate, @_ToDate)
353
354 ---- insert raw transaction được thực hiện giao dịch trong ngày
355 INSERT INTO dbo.DMSBLRawSales
356 (
357 BaselineDate
358 , MonthID
359 , CompanyID
360 , DistributorID
361 , BLDistributorLocationID
362 , OrderNbr
363 , OrderType
364 , OrderDate
365 , OrderLineNbr
366 , CustomerOrderNbr
367 , OrigOrderNbr
368 , OrigOrderType
369 , CustomerID
370 , CustomerLocationID
371 , BLCustomerLocationID
372 , SalespersonID
373 , BLSalespersonID
374 , Route
375 , SourceType
376 , IsHasTMK
377 , IsDispose
378 , IsRevenueDeduction
379 , AllocationID
380 , Multi
381 , IsValidDate
382 , IsValidDistance
383 , CancelReasonCode
384 , ShipmentNbr
385 , ShipmentDate
386 , InvoiceNbr
387 , InvoiceType
388 , InvoiceDate
389 , InvtRefNbr
390 , InvtDocType
391 , InvtDate
392 , InvtSplitLineNbr
393 , InventoryID
394 , BLInventoryID
395 , SiteID
396 , LocationID
397 , BLSiteID
398 , UOM
399 , OrigUnitPrice
400 , UnitPrice
401 , IsFree
402 , ManualDisc
403 , OrderQty
404 , ShippedQty
405 , LotSerialNbr
406 , ExpireDate
407 , TaxAmt
408 , DiscAmt
409 , TranAmt
410 , LastModifiedDateTime
411 , PDAOrderDate
412 , PromotionID
413 )
414 SELECT
415 GETDATE()
416 , rdd.MonthID
417 , rd.CompanyID
418 , rdd.DistributorID
419 , rd.BLDistributorLocationID
420 , rd.OrderNbr
421 , rd.OrderType
422 , rd.OrderDate
423 , rdd.OrderLineNbr
424 , rd.CustomerOrderNbr
425 , rd.OrigOrderNbr
426 , rd.OrigOrderType
427 , rd.CustomerID
428 , rd.CustomerLocationID
429 , rd.BLCustomerLocationID
430 , rd.SalespersonID
431 , rd.BLSalespersonID
432 , rd.Route
433 , rd.SourceType
434 , rd.IsHasTMK
435 , rd.IsDispose
436 , rd.IsRevenueDeduction
437 , rdd.AllocationID
438 , rd.Multi
439 , rd.IsValidDate
440 , rd.IsValidDistance
441 , rd.CancelReasonCode
442 , rd.ShipmentNbr
443 , rd.ShipmentDate
444 , rd.InvoiceNbr
445 , rd.InvoiceType
446 , rd.InvoiceDate
447 , rd.InvtRefNbr
448 , rd.InvtDocType
449 , rd.InvtDate
450 , rdd.InvtSplitLineNbr
451 , rdd.InventoryID
452 , rdd.BLInventoryID
453 , rdd.SiteID
454 , rdd.LocationID
455 , rdd.BLSiteID
456 , rdd.UOM
457 , rdd.OrigUnitPrice
458 , rdd.UnitPrice
459 , rdd.IsFree
460 , rdd.ManualDisc
461 , ISNULL(rdd.OrderQty, 0)
462 , rdd.SplitShippedQty
463 , rdd.LotSerialNbr
464 , rdd.ExpireDate
465 , ISNULL(rdd.SplitTaxAmt, 0)
466 , ISNULL(rdd.SplitDiscAmt, 0)
467 , ISNULL(rdd.SplitTranAmt, 0)
468 , rd.LastModifiedDateTime
469 , rd.UsrPDAOrderDate ------------------------------ Thêm baseline PDAOrderDate
470 , rdd.UsrPromotionID --- Them ma CTKM
471 FROM
472 #tmpReleasedDocumentDetail rdd
473 JOIN #tmpReleasedDocument rd ON rd.CompanyID = rdd.CompanyID
474 AND rd.DistributorID = rdd.DistributorID
475 AND rd.OrderNbr = rdd.OrderNbr
476 AND rd.OrderType = rdd.OrderType
477 AND rd.InvtRefNbr = rdd.InvtRefNbr
478 AND rd.InvtDocType = rdd.InvtDocType
479
480 ---- Lấy ra danh sách đơn CM trả đơn hàng
481 SELECT
482 CompanyID
483 , DistributorID
484 , OrderNbr
485 , OrderType
486 , OrigOrderNbr
487 , OrigOrderType
488 INTO #tmpCMOrderReturn
489 FROM
490 #tmpReleasedDocument
491 WHERE
492 OrderType = 'CM'
493 AND IsDispose = 0
494 AND OrigOrderNbr IS NOT NULL
495 AND IsRevenueDeduction = 1
496 GROUP BY
497 CompanyID
498 , DistributorID
499 , OrderNbr
500 , OrderType
501 , OrigOrderNbr
502 , OrigOrderType
503
504 CREATE NONCLUSTERED INDEX [#tmpCMOrderReturn_index] ON #tmpCMOrderReturn(CompanyID, OrderNbr, OrderType, OrigOrderNbr, OrigOrderType)
505
506 ---- Cập nhật lại thông tin đúng tuyến, trái tuyến, đúng tọa độ theo đơn hàng gốc
507 UPDATE rs
508 SET
509 --rs.IsValidDate = ors.IsValidDate ,
510 rs.IsValidDistance = ors.IsValidDistance
511 FROM
512 dbo.DMSBLRawSales rs
513 JOIN #tmpCMOrderReturn cmor ON cmor.CompanyID = rs.CompanyID
514 AND cmor.OrderNbr = rs.OrderNbr
515 AND cmor.OrderType = rs.OrderType
516 JOIN dbo.DMSBLRawSales ors WITH (NOLOCK) ON ors.CompanyID = cmor.CompanyID
517 AND ors.OrderNbr = cmor.OrigOrderNbr
518 AND ors.OrderType = cmor.OrigOrderType
519
520 UPDATE rs
521 SET
522 --rs.IsValidDate = ors.IsValidDate ,
523 rs.IsValidDistance = ors.IsValidDistance
524 FROM
525 dbo.DMSBLRawSales rs
526 JOIN #tmpCMOrderReturn cmor ON cmor.CompanyID = rs.CompanyID
527 AND cmor.OrderNbr = rs.OrderNbr
528 AND cmor.OrderType = rs.OrderType
529 JOIN dbo.DMSBLRawSalesHistory ors WITH (NOLOCK) ON ors.CompanyID = cmor.CompanyID
530 AND ors.OrderNbr = cmor.OrigOrderNbr
531 AND ors.OrderType = cmor.OrigOrderType
532
533 DROP TABLE #tmpReleasedDocument
534drop table #tmpReleasedDocumentByOrder
535drop table #tmpCMOrder
536drop table #tmpReleasedDocumentDetail
537drop table #tmpCMOrderReturn
538END