· 6 years ago · Jul 04, 2019, 09:14 AM
1USE [reporting]
2GO
3/****** Object: StoredProcedure [dbo].[Report_ProductOnOrders_3663_0002_20180809_074446] Script Date: 07/04/2019 10:10:05 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER OFF
7GO
8
9ALTER PROCEDURE [dbo].[Report_ProductOnOrders_3663_0002_20180809_074446]
10
11 @StartDate DateTime,
12 @EndDate DateTime,
13 @ProductCodeList Varchar(MAX) = '',
14 @BarcodeList Varchar(MAX) = '',
15 @AgreementNoList Varchar(MAX) = '',
16 @BBDList Varchar(MAX) = '',
17 @Status VarChar(MAX) = 'All'
18
19AS
20SET NOCOUNT ON
21BEGIN
22 ------------------------------------------------------------------------------------------------------------------------------
23 --SET UP
24 ------------------------------------------------------------------------------------------------------------------------------
25 --declarations, constants and temp tables
26 DECLARE @ErrorCode Int = 0, @BBDateCount Int = 0, @ProductCodeCount Int = 0, @BarcodeCount Int = 0, @AgreementNoCount Int = 0, @ProductCodeHeader Varchar(MAX) = '|'
27
28 SET @EndDate = DATEADD(ms, -3, DATEADD(dd, 1, @EndDate))
29
30 CREATE TABLE #BBDateRanges
31 (
32 StartDateIn Varchar(MAX),
33 EndDateIn Varchar(MAX),
34 StartDateOut Varchar(8),
35 EndDateOut Varchar(8),
36 StartDate DateTime,
37 EndDate DateTime
38 )
39
40 CREATE TABLE #ProductCodeRanges (ProductCode Varchar(MAX), ProductCode2 Varchar(MAX))
41 CREATE TABLE #BarcodeRanges (Barcode Varchar(MAX), Barcode2 Varchar(MAX))
42 CREATE TABLE #AgreementNoRanges (AgreementNo Varchar(MAX), AgreementNo2 Varchar(MAX))
43
44 CREATE TABLE #ProductCodes(ProductCode Varchar(100), ProductName Varchar(100), ProductPack Varchar(100))
45 CREATE TABLE #AgreementNos(AgreementNo Varchar(100))
46 CREATE TABLE #BBDateLinesPBO(SalesLineID Int)
47 CREATE TABLE #BBDateLinesPBL(LineID Int)
48
49 CREATE TABLE #RawData
50 (
51 SourceType VarChar(3),
52 HeaderID Int,
53 LineID Int,
54 DeliveryDate DateTime,
55 DeliveryDay VarChar(20),
56 [Route] VarChar(20),
57 [Drop] Int,
58 DocumentNo VarChar(20),
59 OrderType VarChar(100),
60 [Status] VarChar(20),
61 [LineNo] VarChar(100),
62 QtyOrdered Int,
63 QtyPicked Int,
64 CaseSize Int,
65 AccountID Int,
66 TLC VarChar(100),
67 AgreementNo Varchar(100),
68 BestBeforeDate DateTime,
69 ProductCode Varchar(100),
70 ProductName Varchar(100),
71 ProductPack Varchar(100)
72 )
73
74 CREATE TABLE #Alternates
75 (
76 Seq Int IDENTITY(1,1),
77 DeliveryDate DateTime,
78 [Route] VarChar(20),
79 [Drop] Int,
80 [LineNo] VarChar(100),
81 OrderRef Int,
82 Additional Varchar(1) DEFAULT ''
83 )
84
85 -------------------------------------------------------------------------------------------------------------------
86 --CONVERT PARAMETER LISTS
87 -------------------------------------------------------------------------------------------------------------------
88 --best before dates
89 IF LEN(LTRIM(RTRIM(@BBDList))) > 0
90 BEGIN
91 INSERT INTO #BBDateRanges (StartDateIn, EndDateIn)
92 SELECT RangeStart, RangeEnd
93 FROM dbo.fn_ParseQueryString(@BBDList)
94
95 SET @BBDateCount = (SELECT COUNT(*) FROM #BBDateRanges)
96 END
97
98 --product code
99 IF LEN(LTRIM(RTRIM(@ProductCodeList))) > 0
100 BEGIN
101 INSERT INTO #ProductCodeRanges (ProductCode, ProductCode2)
102 SELECT RangeStart, RangeEnd
103 FROM dbo.fn_ParseQueryString(@ProductCodeList)
104
105 SET @ProductCodeCount = (SELECT COUNT(*) FROM #ProductCodeRanges)
106 END
107
108 --barcodes
109 IF LEN(LTRIM(RTRIM(@BarcodeList))) > 0
110 BEGIN
111 INSERT INTO #BarcodeRanges (Barcode, Barcode2)
112 SELECT RangeStart, RangeEnd
113 FROM dbo.fn_ParseQueryString(@BarcodeList)
114
115 SET @BarcodeCount = (SELECT COUNT(*) FROM #BarcodeRanges)
116 END
117
118 --agreement nos
119 IF LEN(LTRIM(RTRIM(@AgreementNoList))) > 0
120 BEGIN
121 INSERT INTO #AgreementNoRanges (AgreementNo, AgreementNo2)
122 SELECT RangeStart, RangeEnd
123 FROM dbo.fn_ParseQueryString(@AgreementNoList)
124
125 SET @AgreementNoCount = (SELECT COUNT(*) FROM #AgreementNoRanges)
126 END
127
128 -------------------------------------------------------------------------------------------------------------------
129 --VALIDATE PARAMETER LISTS
130 -------------------------------------------------------------------------------------------------------------------
131 --best before date validation
132 IF EXISTS (SELECT 1 FROM #BBDateRanges WHERE LEN(StartDateIn) <> 6 OR LEN(EndDateIn) <> 6)
133 BEGIN
134 SET @ErrorCode = 1
135 END
136 ELSE
137 BEGIN
138 UPDATE #BBDateRanges
139 SET StartDateOut = '20' + SUBSTRING(StartDateIn,5,2) + SUBSTRING(StartDateIn,3,2) + SUBSTRING(StartDateIn,1,2),
140 EndDateOut = '20' + SUBSTRING(EndDateIn,5,2) + SUBSTRING(EndDateIn,3,2) + SUBSTRING(EndDateIn,1,2)
141
142 IF EXISTS (SELECT 1 FROM #BBDateRanges WHERE ISDATE(StartDateOut) = 0 OR ISDATE(EndDateOut) = 0)
143 BEGIN
144 SET @ErrorCode = 1
145 END
146 ELSE
147 BEGIN
148 UPDATE #BBDateRanges
149 SET StartDate = StartDateOut,
150 EndDate = EndDateOut
151 END
152 END
153
154 --product code validation
155 IF @ErrorCode = 0 AND EXISTS (SELECT 1 FROM #ProductCodeRanges WHERE ProductCode <> ProductCode2)
156 BEGIN
157 SET @ErrorCode = 2
158 END
159 --barcode validation
160 ELSE IF @ErrorCode = 0 AND EXISTS (SELECT 1 FROM #BarcodeRanges WHERE Barcode <> Barcode2)
161 BEGIN
162 SET @ErrorCode = 3
163 END
164 --agreement no validation
165 ELSE IF @ErrorCode = 0 AND EXISTS (SELECT 1 FROM #AgreementNoRanges WHERE AgreementNo <> AgreementNo2)
166 BEGIN
167 SET @ErrorCode = 4
168 END
169 --at least one of product code or barcode validation
170 ELSE IF @ErrorCode = 0 AND @ProductCodeCount = 0 AND @BarcodeCount = 0
171 BEGIN
172 SET @ErrorCode = 5
173 END
174
175 IF @ErrorCode = 0
176 BEGIN
177 -------------------------------------------------------------------------------------------------------------------
178 --CONVERT LISTS INTO SOMETHING THAT CAN BE USED
179 -------------------------------------------------------------------------------------------------------------------
180 --get a list of product codes from the imported product codes and barcodes converted to product codes plus additional info required later on
181 ;WITH ProductList (Code, Name, Pack)
182 AS
183 (
184 SELECT ProductCode AS Code,
185 ProductName AS Name,
186 UserDef6 As Pack
187 FROM Pickmanager_dbo_SalesOrderLines
188 UNION ALL
189 SELECT ProductCode AS Code,
190 [Description] AS Name,
191 UserDef6 As Pack
192 FROM Pickmanager_dbo_PickByLineOrderHeaders
193 ),
194 ParameterList (Code)
195 AS
196 (
197 SELECT pcr.ProductCode
198 FROM #ProductCodeRanges pcr
199 UNION ALL
200 SELECT bar.ProductCode
201 FROM #BarcodeRanges bcr
202 JOIN Pickmanager_dbo_Barcodes bar ON bar.Barcode = bcr.Barcode
203 )
204 INSERT INTO #ProductCodes (ProductCode, ProductName, ProductPack)
205 SELECT prl.Code, MAX(prl.Name), MAX(prl.Pack)
206 FROM ProductList prl
207 JOIN ParameterList pal ON prl.Code = pal.Code
208 GROUP BY prl.Code
209
210 --get the list of product codes from the product code list and barcode list to populated the report header
211 SELECT @ProductCodeHeader = @ProductCodeHeader + ProductCode + '|'
212 FROM #ProductCodes
213
214 --get a distinct list of agreement numbers
215 IF @AgreementNoCount > 0
216 BEGIN
217 INSERT INTO #AgreementNos(AgreementNo)
218 SELECT RIGHT('0000' + AgreementNo, 8)
219 FROM #AgreementNoRanges
220 GROUP BY AgreementNo
221 END
222
223 --get a distinct list of best before lines
224 IF @BBDateCount > 0
225 BEGIN
226 INSERT INTO #BBDateLinesPBO(SalesLineID)
227 SELECT bbd.SalesLineID
228 FROM Pickmanager_dbo_BestBeforeDates bbd
229 JOIN #BBDateRanges bdr ON bbd.BestBeforeDate BETWEEN bdr.StartDate AND bdr.EndDate
230 GROUP BY bbd.SalesLineID
231
232 INSERT INTO #BBDateLinesPBL(LineID)
233 SELECT ptl.LineID
234 FROM Pickmanager_dbo_PickTrackerByLine ptl
235 JOIN #BBDateRanges bdr ON ptl.BestBeforeDate BETWEEN bdr.StartDate AND bdr.EndDate
236 GROUP BY ptl.LineID
237 END
238
239 -------------------------------------------------------------------------------------------------------------------
240 --GET THE RAW DATA
241 -------------------------------------------------------------------------------------------------------------------
242 INSERT INTO #RawData(SourceType, HeaderID, LineID, DeliveryDate, DeliveryDay,
243 [Route], [Drop], DocumentNo, OrderType, [Status],
244 [LineNo], QtyOrdered, QtyPicked, CaseSize, AccountID,
245 TLC, AgreementNo, BestBeforeDate, ProductCode, ProductName, ProductPack)
246 SELECT 'PBO',
247 sol.SalesHeaderID,
248 sol.SalesLineID,
249 soh.PickDate,
250 soh.UserDef2,
251 rdo.[Route],
252 rdo.TripDrop,
253 soh.Documentno,
254 soh.UserDef9,
255 ost.OrderStatus,
256 sol.UserDef1,
257 sol.QtyOrdered,
258 ISNULL(ptr.QtyPicked,sol.QtyPicked),
259 sol.CaseSize,
260 soh.AccountID,
261 soh.UserDef10,
262 soh.UserDef1,
263 bbd.BestBeforeDate,
264 pco.ProductCode,
265 pco.ProductName,
266 pco.ProductPack
267 FROM Pickmanager_dbo_SalesOrderLines sol
268 JOIN Pickmanager_dbo_SalesOrderHeaders soh ON sol.SalesHeaderID = soh.SalesHeaderID
269 JOIN Pickmanager_dbo_OrderStatusTypes ost ON soh.OrderStatusID = ost.OrderStatusID
270 JOIN Pickmanager_dbo_RunDeliveryOrder rdo ON soh.SalesHeaderID = rdo.SalesHeaderID
271 JOIN #ProductCodes pco ON sol.ProductCode = pco.ProductCode
272 LEFT OUTER JOIN Pickmanager_dbo_BestBeforeDates bbd ON sol.SalesLineID = bbd.SalesLineID
273 LEFT OUTER JOIN Pickmanager_dbo_PickTracker ptr ON bbd.TrackID = ptr.TrackID
274 LEFT OUTER JOIN #AgreementNos ano ON soh.UserDef1 = ano.AgreementNo
275 LEFT OUTER JOIN #BBDateLinesPBO bbt ON sol.SalesLineID = bbt.SalesLineID
276 WHERE soh.PickDate BETWEEN @StartDate AND @EndDate
277 AND (@AgreementNoCount = 0 OR ano.AgreementNo IS NOT NULL)
278 AND (@BBDateCount = 0 OR bbt.SalesLineID IS NOT NULL)
279 AND (@Status = 'All' OR PATINDEX('%|' + ost.OrderStatus + '|%', @Status) > 0)
280 UNION ALL
281 SELECT 'PBL',
282 lol.HeaderID,
283 lol.LineID,
284 loh.Deliverydate,
285 lol.UserDef2,
286 lol.[RouteFrom],
287 lol.TripDrop,
288 loh.DocumentNo,
289 lol.UserDef9,
290 ost.OrderStatus,
291 loh.UserDef1,
292 lol.QtyOrdered,
293 ptl.QtyPicked,
294 loh.CaseSize,
295 lol.AccountID,
296 lol.UserDef10,
297 lol.UserDef1,
298 ptl.BestBeforeDate,
299 pco.ProductCode,
300 pco.ProductName,
301 pco.ProductPack
302 FROM Pickmanager_dbo_PickByLineOrderLines lol
303 JOIN Pickmanager_dbo_PickByLineOrderHeaders loh ON lol.HeaderID = loh.HeaderID
304 JOIN Pickmanager_dbo_OrderStatusTypes ost ON loh.OrderStatusID = ost.OrderStatusID
305 JOIN #ProductCodes pco ON loh.ProductCode = pco.ProductCode
306 LEFT OUTER JOIN Pickmanager_dbo_PickTrackerByLine ptl ON ptl.LineID = lol.LineID
307 LEFT OUTER JOIN #AgreementNos ano ON lol.UserDef1 = ano.AgreementNo
308 LEFT OUTER JOIN #BBDateLinesPBL bbt ON lol.LineID = bbt.LineID
309 WHERE loh.Deliverydate BETWEEN @StartDate AND @EndDate
310 AND (@AgreementNoCount = 0 OR ano.AgreementNo IS NOT NULL)
311 AND (@BBDateCount = 0 OR bbt.LineID IS NOT NULL)
312 AND (@Status = 'All' OR PATINDEX('%|' + ost.OrderStatus + '|%', @Status) > 0)
313
314 INSERT INTO #Alternates(DeliveryDate, [Route], [Drop], [LineNo], OrderRef)
315 SELECT DeliveryDate, [Route], [Drop], [LineNo], (CASE SourceType WHEN 'PBL' THEN LineID ELSE HeaderID END)
316 FROM #RawData
317 GROUP BY DeliveryDate, [Route], [Drop], [LineNo], (CASE SourceType WHEN 'PBL' THEN LineID ELSE HeaderID END)
318 ORDER BY DeliveryDate, [Route], [Drop], [LineNo], (CASE SourceType WHEN 'PBL' THEN LineID ELSE HeaderID END)
319
320 UPDATE #Alternates
321 SET Additional = ' '
322 WHERE Seq % 2 = 0
323
324 IF NOT EXISTS (SELECT 1 FROM #RawData)
325 BEGIN
326 SELECT [0000LN&101||Heading1] = 'Location: ' + (SELECT TextValue FROM Parameters_ic WHERE ParameterName = 'DefaultCustomerName') +
327 ' - Date: ' + CONVERT(VarChar, GETDATE(), 106) + ' - Time: ' + LEFT(CONVERT(VarChar, GETDATE(), 108), 5),
328 [0000LN&202||Heading2] = 'Product Code: ' + (CASE @ProductCodeHeader WHEN '|' THEN 'All' ELSE dbo.fn_FormatComboListText_STD_0000(@ProductCodeHeader) END),
329 [AAA0LN|| ] = 'No data available for input criteria'
330 END
331 ELSE
332 BEGIN
333 SELECT [0000LN&101||Heading1] = 'Location: ' + (SELECT TextValue FROM Parameters_ic WHERE ParameterName = 'DefaultCustomerName') +
334 ' - Date: ' + CONVERT(VarChar, GETDATE(), 106) + ' - Time: ' + LEFT(CONVERT(VarChar, GETDATE(), 108), 5),
335 [0000LN&202||Heading2] = 'Product Code: ' + (CASE @ProductCodeHeader WHEN '|' THEN 'All' ELSE dbo.fn_FormatComboListText_STD_0000(@ProductCodeHeader) END),
336 [AAA0LNM||Delivery Date] = CONVERT(VarChar, dat.DeliveryDate, 106) + alt.Additional,
337 [AAA0LNM||Delivery Day] = dat.DeliveryDay + alt.Additional,
338 [AAA0LNM||Route] = CONVERT(Varchar,dat.[Route]) + alt.Additional,
339 [AAA0LNM||Drop] = CONVERT(Varchar,dat.[Drop]) + alt.Additional,
340 [AAA0LNM||Order No 3663] = CONVERT(Varchar,dat.DocumentNo) + alt.Additional,
341 [AAA0LNM||Order Ref VoiceMan]= CONVERT(Varchar,(CASE dat.SourceType WHEN 'PBL' THEN dat.LineID ELSE dat.HeaderID END)) + alt.Additional,
342 [AAA0LNM||Order Type] = CONVERT(Varchar,dat.OrderType) + alt.Additional,
343 [AAA0LNM||Status] = dat.[Status] + alt.Additional,
344 [AAA0LNM||Line No] = CONVERT(Varchar,dat.[LineNo]) + alt.Additional,
345 [AAA0LNM||Product Code] = CONVERT(Varchar,ProductCode) + alt.Additional,
346 [AAA0LNM||Product Name] = ProductName + alt.Additional,
347 [AAA0LNM||Product Pack] = ProductPack + alt.Additional,
348 [AAA0LNM||Qty Ordered Case] = CONVERT(Varchar,CONVERT(Int, dat.QtyOrdered / (CASE ISNULL(dat.CaseSize, 0) WHEN 0 THEN 1 ELSE dat.CaseSize END))) + alt.Additional,
349 [AAA0LNM||Qty Ordered Unit] = CONVERT(Varchar,CONVERT(Int, dat.QtyOrdered % (CASE ISNULL(dat.CaseSize, 0) WHEN 0 THEN 1 ELSE dat.CaseSize END))) + alt.Additional,
350 [AAA0LN||Qty Picked Case] = CONVERT(Varchar,CONVERT(Int, dat.QtyPicked / (CASE ISNULL(dat.CaseSize, 0) WHEN 0 THEN 1 ELSE dat.CaseSize END))),
351 [AAA0LN||Qty Picked Unit] = CONVERT(Varchar,CONVERT(Int, dat.QtyPicked % (CASE ISNULL(dat.CaseSize, 0) WHEN 0 THEN 1 ELSE dat.CaseSize END))),
352 [AAA0LN||Captured BBD] = (CASE ISNULL(BestBeforeDate, 0) WHEN 0 THEN '' ELSE CONVERT(Varchar,BestBeforeDate,106) END),
353 [AAA0LNM||Agreement No] = (CASE AgreementNo WHEN '00000000' THEN '' ELSE RIGHT(CONVERT(Varchar, AgreementNo),4) END) + alt.Additional,
354 [AAA0LNM||Account No] = CONVERT(Varchar,cac.AccountCode) + alt.Additional,
355 [AAA0LNM||Account Name] = cac.AccountName + alt.Additional,
356 [AAA0LNM||TLC] = dat.TLC + alt.Additional
357 FROM #RawData dat
358 JOIN #Alternates alt ON dat.DeliveryDate = alt.DeliveryDate
359 AND dat.[Route] = alt.[Route]
360 AND dat.[Drop] = alt.[Drop]
361 AND dat.[LineNo] = alt.[LineNo]
362 AND (CASE dat.SourceType WHEN 'PBL' THEN dat.LineID ELSE dat.HeaderID END) = alt.OrderRef
363 JOIN Pickmanager_dbo_CustomerAccounts cac ON dat.AccountID = cac.AccountID
364 ORDER BY dat.DeliveryDate, dat.[Route], dat.[Drop], dat.[LineNo], (CASE dat.SourceType WHEN 'PBL' THEN dat.LineID ELSE dat.HeaderID END)
365 END
366 END
367 ELSE
368 BEGIN
369 SELECT [0000LN&101||Heading1] = 'Location: ' + (SELECT TextValue FROM Parameters_ic WHERE ParameterName = 'DefaultCustomerName') +
370 ' - Date: ' + CONVERT(VarChar, GETDATE(), 106) + ' - Time: ' + LEFT(CONVERT(VarChar, GETDATE(), 108), 5),
371 [0000LN&202||Heading2] = 'Product Code: ' + (CASE @ProductCodeHeader WHEN '|' THEN 'All' ELSE dbo.fn_FormatComboListText_STD_0000(@ProductCodeHeader) END),
372 [AAA0LN|| ] = (CASE @ErrorCode
373 WHEN 1 THEN 'Invalid date entered, Date format should be DDMMYY (' + @BBDList + ')'
374 WHEN 2 THEN 'Invalid product codes, you cannot enter a range (' + @ProductCodeList + ')'
375 WHEN 3 THEN 'Invalid barcodes, you cannot enter a range (' + @BarcodeList + ')'
376 WHEN 4 THEN 'Invalid agreement numbers, you cannot enter a range (' + @AgreementNoList + ')'
377 WHEN 5 THEN 'You must enter either product codes or barcodes'
378 ELSE 'No data available for input criteria'
379 END)
380
381 END
382 -------------------------------------------------------------------------------------------------------------------
383 --CLEAN UP
384 -------------------------------------------------------------------------------------------------------------------
385 DROP TABLE #BBDateRanges
386 DROP TABLE #ProductCodeRanges
387 DROP TABLE #BarcodeRanges
388 DROP TABLE #AgreementNoRanges
389 DROP TABLE #ProductCodes
390 DROP TABLE #AgreementNos
391 DROP TABLE #BBDateLinesPBO
392 DROP TABLE #BBDateLinesPBL
393 DROP TABLE #RawData
394 DROP TABLE #Alternates
395END