shmxBpqG

1
2-- get total volume of sales during previous 6 months
3
4Declare @prodRangeDate Date
5Declare @prodRangeNum numeric(8,0)
6set @prodRangeDate = DATEADD(day, -183, GETDATE())
7set @prodRangeNum = YEAR(@prodRangeDate) * 10000 + MONTH(@prodRangeDate) * 100 + DAY(@prodRangeDate)
8
9IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.PRODUKTVOLYMER'))
10	   DROP table dbo.PRODUKTVOLYMER
11
12SELECT
13	VHPRNO as 'ArtikelNr',
14	SUM(VHMAQT)/6.0 as 'tillverkadKvantitet' -- divide by 6 to get per month volume
15INTO PRODUKTVOLYMER
16FROM [movex_repl].[dbo].[MWOHED]
17WHERE ((VHPRNO >= '0500000' AND VHPRNO < '0600000')
18	OR (VHPRNO >= '1500000' AND VHPRNO < '1600000')
19	OR (VHPRNO >= '2500000' AND VHPRNO < '2600000')
20	OR (VHPRNO >= '2700000' AND VHPRNO < '2800000')
21	OR (VHPRNO >= '4400000' AND VHPRNO < '4800000'))
22	AND VHRSDT > @prodRangeNum
23	AND VHREFD > @prodRangeNum
24GROUP BY VHPRNO
25
26
27/****** Create product info ******/
28
29IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.PRODUKTER'))
30	   DROP table dbo.PRODUKTER
31
32
33SELECT DISTINCT
34	MITMAS.MMITNO As 'ArtikelNr',
35	MITMAS.MMITCL AS 'ProduktGrupp',
36	MITMAS.MMUNMS As 'Enhet',
37	MITMAS.MMGRWE As 'Bruttovikt',
38	MITMAS.MMITDS As 'Benämning',
39	MITMAS.MMFUDS As 'Beskrivning',
40	MITMAS.MMITGR As 'Artikelgrupp',
41	PRODREG.STAL As 'Stål',
42	PRODREG.YD As 'Dimension',
43	MITBAL.MBSSQT As 'Säkerhetslager',
44	ISNULL(PRODUKTVOLYMER.tillverkadKvantitet, 0) as 'TillverkadKvantitet'
45INTO
46	PRODUKTER
47FROM
48	[movex_repl].[dbo].[MITBAL] MITBAL,
49	[movex_repl].[dbo].[PRODREG] PRODREG,
50	[movex_repl].[dbo].[MITMAS] MITMAS
51	LEFT JOIN PRODUKTVOLYMER on PRODUKTVOLYMER.ArtikelNr = MITMAS.MMITNO
52WHERE ((MITMAS.MMITNO >= '2500000' AND MITMAS.MMITNO < '2600000')
53	OR (MITMAS.MMITNO >= '2700000' AND MITMAS.MMITNO < '2800000')
54	OR (MITMAS.MMITNO >= '4400000' AND MITMAS.MMITNO < '4800000'))
55	AND MITBAL.MBWHLO = 'HAL'
56	AND PRODREG.PRODNR = MITMAS.MMITNO
57	AND MITBAL.MBITNO = MITMAS.MMITNO
58
59/****** Create lager info ******/
60
61IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.LAGER'))
62	   DROP table dbo.LAGER
63
64SELECT
65	MLITNO as 'ArtikelNr',
66	SUM((MLSTQT - MLALQT) * MITMAS.MMGRWE) as 'Gripbart',
67	SUM(MLALQT * MITMAS.MMGRWE) as 'Allokerat'
68INTO
69	LAGER
70FROM
71	[movex_repl].[dbo].[MITLOC] MITLOC,
72	[movex_repl].[dbo].[MITMAS] MITMAS
73WHERE
74	MITLOC.MLWHLO = 'HAL'
75	AND MLITNO = MITMAS.MMITNO
76	AND MITMAS.MMGRWE > 0
77	AND (MLSTQT > 0 OR MLALQT > 0)
78GROUP BY MLITNO, MITMAS.MMITCL
79
80/****** Create customer info table ******/
81-- todo: Use more appropriate base table
82
83IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.KUNDER'))
84	   DROP table dbo.KUNDER
85
86SELECT DISTINCT
87	OOHEAD.OACUNO As 'KundNr',
88	OOHEAD.OAALCU As 'Namn'
89INTO
90	KUNDER
91FROM [movex_repl].[dbo].[oohead] OOHEAD;
92
93-- Remove duplicate entries, needed as this table is not ideal for this
94-- some customers have misspelled or changed names so end up twice in list
95WITH CTE AS (
96SELECT *,ROW_NUMBER() OVER (PARTITION BY KundNr ORDER BY ( SELECT 0)) AS RN
97FROM KUNDER
98) DELETE FROM CTE WHERE RN<>1
99
100/****** Create customer order info ******/
101
102IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.KUNDORDRAR'))
103	   DROP table dbo.KUNDORDRAR
104
105SELECT
106	CONCAT(OOLINE.OBORNO, '/', CONCAT(OOLINE.OBPONR, '00')) as 'KundOrderNr',
107	OOLINE.OBCUNO As 'KundNr',
108	OOLINE.OBORQT As 'BeställdKvantitet',
109	OOLINE.OBORQA As 'ÅterståendeKvantitet',
110	TRY_CONVERT(date, TRY_CONVERT(varchar(10), OOLINE.OBDWDT)) as 'ÖnskatLeveransDatum',
111	TRY_CONVERT(date, TRY_CONVERT(varchar(10), OOLINE.OBCODT)) as 'BekräftatLeveransDatum'
112INTO
113	KUNDORDRAR
114FROM [movex_repl].[dbo].[ooline] OOLINE
115WHERE OOLINE.OBORNO >= '1700000';
116
117/****** Create info for active orders ******/
118
119Declare @specifiedDate Date
120Declare @numDate numeric(8,0)
121set @specifiedDate = DATEADD(day, 60, GETDATE())
122set @numDate = YEAR(@specifiedDate) * 10000 + MONTH(@specifiedDate) * 100 + DAY(@specifiedDate)
123
124IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.AKTIVAORDRAR'))
125	   DROP table dbo.AKTIVAORDRAR
126
127SELECT
128	CONCAT(LTRIM(RTRIM(MITPLO.MORIDN)), '/', MITPLO.MORIDL) as 'KundOrderNr',
129	MITPLO.MOITNO as 'ArtikelNr',
130	TRY_CONVERT(date, TRY_CONVERT(varchar(10), MITPLO.MOPLDT)) as 'BekräftatLeveransDatum',
131	MITPLO.MOSTAT as 'Status',
132	MITPLO.MOTRQT*-1 as 'Kvantitet',
133	CASE
134		WHEN PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', MITPLO.MORFTX) > 0 And ISNUMERIC(MITPLO.MORFTX) <> 1
135			THEN SUBSTRING(MITPLO.MORFTX, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', MITPLO.MORFTX), 5)
136		ELSE NULL
137	END As 'KundNr',
138	MITPLO.MORFTX As 'Referenstext'
139INTO
140	AKTIVAORDRAR
141FROM [movex_repl].[dbo].[MITPLO] MITPLO
142WHERE ((MITPLO.MOITNO >= '2700000' AND MITPLO.MOITNO < '2800000') OR (MITPLO.MOITNO >= '4600000' AND MITPLO.MOITNO < '4800000'))
143	AND MITPLO.MOPLDT < @numDate
144	AND MITPLO.MOTRQT < 0.0
145
146
147	
148-- TODO: be more consistent with steg, use string or num
149-- TODO: Fix issues with 05 article nums
150-- För att skapa tabellen med flaskhalsar
151-- https://support.discountasp.net/kb/a1179/how-to-import-a-csv-file-into-a-database-using-sql-server-management-studio.aspx
152-- Se till att formatet på kolumnen är UTF8 + att under advanced byta till unicode string (från string)
153
154
155
156/****** Create steps that each end-product goes through ******/
157
158IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.MATERIALIN'))
159	   DROP table dbo.MATERIALIN
160
161SELECT
162	MPDMAT.PMPRNO As 'ArtikelNr',
163	0 As 'StegNum',
164	-- setting a 9 in front of the 05 series for the later steps where we remove duplicate
165	-- ways to make products. The 05 products thus get a lower priority
166	CASE
167		WHEN (MPDMAT.PMMTNO >= '0500000' AND MPDMAT.PMMTNO < '0600000')
168			THEN CONCAT('9', MPDMAT.PMMTNO)
169		ELSE MPDMAT.PMMTNO
170	END As 'ArtikelNrIn',
171	MPDMAT.PMPRNO As 'ArtikelNrUt',
172	PRODUKTVOLYMER.tillverkadKvantitet As 'inKvantitet'
173INTO MATERIALIN
174FROM [movex_repl].[dbo].[MPDMAT] MPDMAT
175	LEFT JOIN PRODUKTVOLYMER on MPDMAT.PMMTNO = PRODUKTVOLYMER.ArtikelNr
176WHERE ((MPDMAT.PMPRNO >= '2500000' AND MPDMAT.PMPRNO < '2600000')
177		OR (MPDMAT.PMPRNO >= '2700000' AND MPDMAT.PMPRNO < '2800000')
178		OR (MPDMAT.PMPRNO >= '4400000' AND MPDMAT.PMPRNO < '4800000')
179	) AND ((MPDMAT.PMMTNO >= '0500000' AND MPDMAT.PMMTNO < '0600000')
180		OR (MPDMAT.PMMTNO >= '1500000' AND MPDMAT.PMMTNO < '1600000')
181		OR (MPDMAT.PMMTNO >= '2500000' AND MPDMAT.PMMTNO < '2600000')
182		OR (MPDMAT.PMMTNO >= '4400000' AND MPDMAT.PMMTNO < '4600000')
183	);
184	
185-- Remove duplicate entries, there seems to be just one out of 23k... probably an error (article num 2727157)
186WITH CTE AS (
187SELECT *,ROW_NUMBER() OVER (PARTITION BY ArtikelNr ORDER BY ArtikelNrIn ASC) AS RN
188FROM MATERIALIN
189) DELETE FROM CTE WHERE RN<>1
190
191-- create each step recursively (todo: make into a function of some sort)
192
193INSERT INTO MATERIALIN
194	SELECT DISTINCT
195		MATERIALIN.ArtikelNr,
196		1 As 'StegNum',
197		MATERIALIN2.ArtikelNrIn,
198		MATERIALIN.ArtikelNrIn As 'ArtikelNrUt',
199		MATERIALIN2.inKvantitet
200	FROM MATERIALIN
201		LEFT JOIN MATERIALIN as MATERIALIN2 on MATERIALIN2.ArtikelNr = MATERIALIN.ArtikelNrIn
202	WHERE MATERIALIN.StegNum = 0 AND MATERIALIN2.ArtikelNrIn IS NOT NULL;
203	
204INSERT INTO MATERIALIN
205	SELECT DISTINCT
206		MATERIALIN.ArtikelNr,
207		2 As 'StegNum',
208		MATERIALIN2.ArtikelNrIn,
209		MATERIALIN.ArtikelNrIn As 'ArtikelNrUt',
210		MATERIALIN2.inKvantitet
211	FROM MATERIALIN
212		LEFT JOIN MATERIALIN as MATERIALIN2 on MATERIALIN2.ArtikelNr = MATERIALIN.ArtikelNrIn
213	WHERE MATERIALIN.StegNum = 1 AND MATERIALIN2.ArtikelNrIn IS NOT NULL;
214	
215IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.MATERIALIN4'))
216	   DROP table dbo.MATERIALIN4
217
218INSERT INTO MATERIALIN
219	SELECT DISTINCT
220		MATERIALIN.ArtikelNr,
221		3 As 'StegNum',
222		MATERIALIN2.ArtikelNrIn,
223		MATERIALIN.ArtikelNrIn As 'ArtikelNrUt',
224		MATERIALIN2.inKvantitet
225	FROM MATERIALIN
226		LEFT JOIN MATERIALIN as MATERIALIN2 on MATERIALIN2.ArtikelNr = MATERIALIN.ArtikelNrIn
227	WHERE MATERIALIN.StegNum = 2 AND MATERIALIN2.ArtikelNrIn IS NOT NULL;
228	
229INSERT INTO MATERIALIN
230	SELECT DISTINCT
231		MATERIALIN.ArtikelNr,
232		4 As 'StegNum',
233		MATERIALIN2.ArtikelNrIn,
234		MATERIALIN.ArtikelNrIn As 'ArtikelNrUt',
235		MATERIALIN2.inKvantitet
236	FROM MATERIALIN
237		LEFT JOIN MATERIALIN as MATERIALIN2 on MATERIALIN2.ArtikelNr = MATERIALIN.ArtikelNrIn
238	WHERE MATERIALIN.StegNum = 3 AND MATERIALIN2.ArtikelNrIn IS NOT NULL;
239
240INSERT INTO MATERIALIN
241	SELECT DISTINCT
242		MATERIALIN.ArtikelNr,
243		5 As 'StegNum',
244		MATERIALIN2.ArtikelNrIn,
245		MATERIALIN.ArtikelNrIn As 'ArtikelNrUt',
246		MATERIALIN2.inKvantitet
247	FROM MATERIALIN
248		LEFT JOIN MATERIALIN as MATERIALIN2 on MATERIALIN2.ArtikelNr = MATERIALIN.ArtikelNrIn
249	WHERE MATERIALIN.StegNum = 4 AND MATERIALIN2.ArtikelNrIn IS NOT NULL;
250
251
252-- Remove duplicate entries as some products seem to have multiple ways to be created
253
254-- first remove the ones where same step figures twice, order by inKvantitet so products with
255-- input product with largest volume are picked first, second priority on as low article nums as possible
256-- so less complicated paths are chosen, except for the 05 series which are given a leading 9 to be deprioritized
257WITH CTE AS (
258SELECT *,ROW_NUMBER() OVER (PARTITION BY ArtikelNr, StegNum ORDER BY inKvantitet DESC, ArtikelNr ASC) AS RN
259FROM MATERIALIN
260) DELETE FROM CTE WHERE RN<>1;
261
262
263-- then removes cases where product has same input twice
264WITH CTE AS (
265SELECT *,ROW_NUMBER() OVER (PARTITION BY ArtikelNr, ArtikelNrIn ORDER BY StegNum ASC) AS RN
266FROM MATERIALIN
267) DELETE FROM CTE WHERE RN<>1;
268
269-- pick out the end products
270
271IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.PRODSTEG'))
272	   DROP table dbo.PRODSTEG
273
274SELECT
275	ArtikelNr,
276	StegNum,
277	CASE
278		WHEN (ArtikelNrIn >= '90500000' AND ArtikelNrIn < '90600000')
279			THEN SUBSTRING(ArtikelNrIn, 2, 15)
280		ELSE ArtikelNrIn
281	END As 'ArtikelNrIn',
282	ArtikelNrUt,
283	CAST (0 as numeric(38,9)) as 'Lager', -- TODO: precision seems insane
284	CAST(1 as numeric(7,2)) as 'Utbyte',
285	CAST('' as varchar(180)) as 'plgString',
286	CAST(0 as numeric(7,2)) as 'Tid'
287INTO PRODSTEG
288FROM MATERIALIN
289WHERE ((ArtikelNr >= '2700000' AND ArtikelNr < '2800000')
290	OR (ArtikelNr >= '4600000' AND ArtikelNr < '4800000'))
291ORDER BY ArtikelNr, StegNum
292
293-- remove products that are not in any active orders (purely for perf. reasons)
294--DELETE FROM PRODSTEG 
295--WHERE NOT EXISTS (SELECT 1 FROM AKTIVAORDRAR 
296--                  WHERE PRODSTEG.ArtikelNr  = AKTIVAORDRAR.ArtikelNr )
297
298/****** Create steps that each product goes through ******/
299
300IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.PRODSUBSTEG'))
301	   DROP table dbo.PRODSUBSTEG
302
303SELECT DISTINCT
304	MPDOPE.POPRNO As 'ArtikelNr',
305	MPDOPE.POOPNO As 'OperationsNr',
306	MPDOPE.POPLGR As 'PlaneringsGrupp',
307	MPDOPE.POOPDS As 'Operationsbenämning',
308	MPDOPE.POPITI As 'Tid',
309	1.0/(1.0 - MPDOPE.POSCPC/100.0) As 'Utbyte',
310	1.0/(1.0 - MPDOPE.POASPC/100.0) As 'AccUtbyte'
311INTO PRODSUBSTEG
312FROM [movex_repl].[dbo].[MPDOPE] MPDOPE
313WHERE ((MPDOPE.POPRNO >= '2500000' AND MPDOPE.POPRNO < '2600000')
314	OR (MPDOPE.POPRNO >= '2700000' AND MPDOPE.POPRNO < '2800000')
315	OR (MPDOPE.POPRNO >= '4400000' AND MPDOPE.POPRNO < '4800000'))
316ORDER BY ArtikelNr;
317
318
319-- add a concatenated string of all the plgs in the product
320-- From https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
321
322IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.PRODPLGSTRINGS'))
323	   DROP table dbo.PRODPLGSTRINGS;
324
325WITH CTE ( ArtikelNr, plg, seq )
326     AS ( SELECT p1.ArtikelNr, TRIM(p1.PlaneringsGrupp),
327            ROW_NUMBER() OVER ( PARTITION BY ArtikelNr ORDER BY PlaneringsGrupp )
328           FROM PRODSUBSTEG p1 WHERE PlaneringsGrupp <> 'EXTPROST')
329SELECT ArtikelNr,
330	MAX( CASE seq WHEN 1 THEN plg + ',' ELSE '' END ) +
331	MAX( CASE seq WHEN 2 THEN plg + ',' ELSE '' END ) +
332	MAX( CASE seq WHEN 3 THEN plg + ',' ELSE '' END ) +
333	MAX( CASE seq WHEN 4 THEN plg + ',' ELSE '' END ) +
334	MAX( CASE seq WHEN 5 THEN plg + ',' ELSE '' END ) +
335	MAX( CASE seq WHEN 6 THEN plg + ',' ELSE '' END ) +
336	MAX( CASE seq WHEN 7 THEN plg + ',' ELSE '' END ) +
337	MAX( CASE seq WHEN 8 THEN plg + ',' ELSE '' END ) +
338	MAX( CASE seq WHEN 9 THEN plg + ',' ELSE '' END ) +
339	MAX( CASE seq WHEN 10 THEN plg + ',' ELSE '' END ) +
340	MAX( CASE seq WHEN 11 THEN plg + ',' ELSE '' END ) +
341	MAX( CASE seq WHEN 12 THEN plg + ',' ELSE '' END ) +
342	MAX( CASE seq WHEN 13 THEN plg + ',' ELSE '' END ) +
343	MAX( CASE seq WHEN 14 THEN plg + ',' ELSE '' END ) +
344	MAX( CASE seq WHEN 15 THEN plg + ',' ELSE '' END ) +
345	MAX( CASE seq WHEN 16 THEN plg + ',' ELSE '' END ) +
346	MAX( CASE seq WHEN 17 THEN plg + ',' ELSE '' END ) +
347	MAX( CASE seq WHEN 18 THEN plg + ',' ELSE '' END ) +
348	MAX( CASE seq WHEN 19 THEN plg + ',' ELSE '' END ) +
349	MAX( CASE seq WHEN 20 THEN plg + ',' ELSE '' END ) as 'plgString'
350INTO PRODPLGSTRINGS
351FROM CTE
352GROUP BY ArtikelNr;
353
354-- Add aggregate time and utbyte information
355UPDATE PRODSTEG
356SET
357	Tid = (SELECT SUM(ISNULL(PRODSUBSTEG.Tid, 0.0)) FROM PRODSUBSTEG WHERE PRODSTEG.ArtikelNrUt = PRODSUBSTEG.ArtikelNr)
358
359UPDATE PRODSTEG
360SET
361	Utbyte = (SELECT exp(sum(log(ISNULL(PRODSUBSTEG.Utbyte, 1.0)))) FROM PRODSUBSTEG WHERE PRODSTEG.ArtikelNrUt = PRODSUBSTEG.ArtikelNr AND PRODSUBSTEG.Utbyte > 0.0)
362
363
364-- reverse order to ensure step 1 is first step (also adding off by one here to make first step 1 not step 0)
365UPDATE PRODSTEG
366SET
367	StegNum = (SELECT count(*) FROM PRODSTEG as PRODSTEG2 WHERE PRODSTEG.ArtikelNr = PRODSTEG2.ArtikelNr)-StegNum
368
369-- add lagerinfo
370UPDATE PRODSTEG
371SET
372	Lager = ISNULL((SELECT Allokerat+Gripbart FROM LAGER WHERE PRODSTEG.ArtikelNrIn = LAGER.ArtikelNr),0)
373
374-- add plg string
375UPDATE PRODSTEG
376SET
377	plgString = (SELECT REPLACE(plgString, ',', CONCAT(PRODSTEG.StegNum, ',')) FROM PRODPLGSTRINGS WHERE PRODSTEG.ArtikelNrUt = PRODPLGSTRINGS.ArtikelNr)
378
379IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.PRODPLGSTRINGS'))
380	   DROP table dbo.PRODPLGSTRINGS
381
382
383
384
385
386/****** Create table with rows for each flaskhals ******/
387
388IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.PRODFLASKHALSAR'))
389	   DROP table dbo.PRODFLASKHALSAR
390
391SELECT
392	PRODSTEG.ArtikelNr,
393	PRODSTEG.StegNum,
394	CONCAT('Steg ', PRODSTEG.StegNum) As 'Steg',
395	PRODSTEG.ArtikelNrIn,
396	PRODSTEG.ArtikelNrUt,
397	PRODSUBSTEG.OperationsNr,
398	PRODSUBSTEG.PlaneringsGrupp,
399	PRODSUBSTEG.Tid,
400	PRODSUBSTEG.Operationsbenämning,
401	FLASKHALSAR.Flaskhals,
402	CASE WHEN PRODSTEG.Utbyte < PRODSUBSTEG.AccUtbyte THEN 1.0 ELSE CAST(1.0/(1.0 + PRODSTEG.Utbyte - PRODSUBSTEG.AccUtbyte) As numeric(7,5)) END As 'PreUtbyte',
403	ROW_NUMBER() OVER(PARTITION BY PRODSTEG.ArtikelNr ORDER BY PRODSTEG.ArtikelNr, PRODSTEG.StegNum, PRODSUBSTEG.OperationsNr) As 'FHNum',
404	PRODUKTER.Bruttovikt * PRODUKTER.TillverkadKvantitet As 'TillverkadVikt'
405INTO PRODFLASKHALSAR
406FROM PRODSTEG
407	LEFT JOIN PRODSUBSTEG on PRODSTEG.ArtikelNrUt = PRODSUBSTEG.ArtikelNr
408	LEFT JOIN FLASKHALSAR on FLASKHALSAR.PlaneringsGrupp = PRODSUBSTEG.PlaneringsGrupp
409	LEFT JOIN PRODUKTER on PRODSTEG.ArtikelNrUt = PRODUKTER.ArtikelNr
410WHERE FLASKHALSAR.[Planeringsgrupp] IS NOT NULL
411ORDER BY PRODSTEG.ArtikelNr, PRODSTEG.StegNum
412
413
414/****** Create final table with all order info ******/
415
416SELECT
417	ROW_NUMBER() OVER(ORDER BY AKTIVAORDRAR.BekräftatLeveransDatum) AS 'Prioritet',
418	AKTIVAORDRAR.KundOrderNr,
419	AKTIVAORDRAR.Status,
420	TRIM(AKTIVAORDRAR.Referenstext) as Referenstext,
421	ISNULL(KUNDER.Namn, CONCAT('[', TRIM(AKTIVAORDRAR.Referenstext), ']')) As 'Kund',
422	ISNULL(KUNDORDRAR.KundNr, AKTIVAORDRAR.KundNr) As 'KundNr',
423	KUNDORDRAR.ÖnskatLeveransDatum As 'Önskad leverans',
424	AKTIVAORDRAR.BekräftatLeveransDatum As 'Bekräftad leverans',
425	ISNULL(KUNDORDRAR.BeställdKvantitet, AKTIVAORDRAR.Kvantitet) * PRODUKTER.Bruttovikt As 'Beställd vikt',
426	ISNULL(KUNDORDRAR.BeställdKvantitet - KUNDORDRAR.ÅterståendeKvantitet, 0) * PRODUKTER.Bruttovikt As 'Levererad vikt',
427	AKTIVAORDRAR.ArtikelNr,
428	PRODUKTER.ProduktGrupp,
429	PRODUKTER.ArtikelGrupp,
430	PRODUKTER.Benämning,
431	PRODUKTER.Beskrivning,
432	PRODUKTER.Stål,
433	PRODUKTER.Dimension,
434	PRODUKTER.Säkerhetslager,
435	STEG1.ArtikelNrIn as 'InputS1',
436	STEG2.ArtikelNrIn as 'InputS2',
437	STEG3.ArtikelNrIn as 'InputS3',
438	STEG4.ArtikelNrIn as 'InputS4',
439	STEG5.ArtikelNrIn as 'InputS5',
440	ISNULL(STEG1.Utbyte * ISNULL(STEG2.Utbyte, 1.0) * ISNULL(STEG3.Utbyte, 1.0) * ISNULL(STEG4.Utbyte, 1.0) * ISNULL(STEG5.Utbyte, 1.0), 1.0) as 'UtbyteS1',
441	ISNULL(STEG2.Utbyte * ISNULL(STEG3.Utbyte, 1.0) * ISNULL(STEG4.Utbyte, 1.0) * ISNULL(STEG5.Utbyte, 1.0), 1.0) as 'UtbyteS2',
442	ISNULL(STEG3.Utbyte * ISNULL(STEG4.Utbyte, 1.0) * ISNULL(STEG5.Utbyte, 1.0), 1.0) as 'UtbyteS3',
443	ISNULL(STEG4.Utbyte * ISNULL(STEG5.Utbyte, 1.0), 1.0) as 'UtbyteS4',
444	ISNULL(STEG5.Utbyte, 1.0) as 'UtbyteS5',
445	ISNULL(STEG5.Utbyte, 1.0) as 'UtbyteSlutProd', -- TODO: Review excel, unclear why needed
446	STEG1.Lager as 'LagerS1',
447	STEG2.Lager as 'LagerS2',
448	STEG3.Lager as 'LagerS3',
449	STEG4.Lager as 'LagerS4',
450	STEG5.Lager as 'LagerS5',
451	ISNULL(LAGER.Gripbart, 0) as 'GripbartLagerSlutProd',
452	CONCAT(ISNULL(STEG1.plgString, ''), ISNULL(STEG2.plgString, ''), ISNULL(STEG3.plgString, ''), ISNULL(STEG4.plgString, ''), ISNULL(STEG5.plgString, '')) as 'FilterString',
453	FH1.Flaskhals As 'FH1',
454	FH2.Flaskhals As 'FH2',
455	FH3.Flaskhals As 'FH3',
456	FH4.Flaskhals As 'FH4',
457	FH5.Flaskhals As 'FH5',
458	FH6.Flaskhals As 'FH6',
459	FH7.Flaskhals As 'FH7',
460	FH8.Flaskhals As 'FH8',
461	FH9.Flaskhals As 'FH9',
462	FH10.Flaskhals As 'FH10',
463	FH1.StegNum As 'FH1Steg',
464	FH2.StegNum As 'FH2Steg',
465	FH3.StegNum As 'FH3Steg',
466	FH4.StegNum As 'FH4Steg',
467	FH5.StegNum As 'FH5Steg',
468	FH6.StegNum As 'FH6Steg',
469	FH7.StegNum As 'FH7Steg',
470	FH8.StegNum As 'FH8Steg',
471	FH9.StegNum As 'FH9Steg',
472	FH10.StegNum As 'FH10Steg',
473	FH1.Tid As 'FH1Tid',
474	FH2.Tid As 'FH2Tid',
475	FH3.Tid As 'FH3Tid',
476	FH4.Tid As 'FH4Tid',
477	FH5.Tid As 'FH5Tid',
478	FH6.Tid As 'FH6Tid',
479	FH7.Tid As 'FH7Tid',
480	FH8.Tid As 'FH8Tid',
481	FH9.Tid As 'FH9Tid',
482	FH10.Tid As 'FH10Tid',
483	FH1.PreUtbyte As 'FH1PUtb',
484	FH2.PreUtbyte As 'FH2PUtb',
485	FH3.PreUtbyte As 'FH3PUtb',
486	FH4.PreUtbyte As 'FH4PUtb',
487	FH5.PreUtbyte As 'FH5PUtb',
488	FH6.PreUtbyte As 'FH6PUtb',
489	FH7.PreUtbyte As 'FH7PUtb',
490	FH8.PreUtbyte As 'FH8PUtb',
491	FH9.PreUtbyte As 'FH9PUtb',
492	FH10.PreUtbyte As 'FH10PUtb'
493FROM AKTIVAORDRAR
494	LEFT JOIN LAGER on LAGER.ArtikelNr = AKTIVAORDRAR.ArtikelNr
495	LEFT JOIN KUNDORDRAR on KUNDORDRAR.KundOrderNr = AKTIVAORDRAR.KundOrderNr
496	LEFT JOIN KUNDER on KUNDER.KundNr = ISNULL(KUNDORDRAR.KundNr, AKTIVAORDRAR.KundNr)
497	LEFT JOIN PRODUKTER on PRODUKTER.ArtikelNr = AKTIVAORDRAR.ArtikelNr
498	LEFT JOIN PRODSTEG as STEG1 on (STEG1.ArtikelNr = AKTIVAORDRAR.ArtikelNr AND STEG1.StegNum = 1)
499	LEFT JOIN PRODSTEG as STEG2 on (STEG2.ArtikelNr = AKTIVAORDRAR.ArtikelNr AND STEG2.StegNum = 2)
500	LEFT JOIN PRODSTEG as STEG3 on (STEG3.ArtikelNr = AKTIVAORDRAR.ArtikelNr AND STEG3.StegNum = 3)
501	LEFT JOIN PRODSTEG as STEG4 on (STEG4.ArtikelNr = AKTIVAORDRAR.ArtikelNr AND STEG4.StegNum = 4)
502	LEFT JOIN PRODSTEG as STEG5 on (STEG5.ArtikelNr = AKTIVAORDRAR.ArtikelNr AND STEG5.StegNum = 5)
503	LEFT JOIN PRODFLASKHALSAR as FH1 on (FH1.ArtikelNr = AKTIVAORDRAR.ArtikelNr AND FH1.FHNum = 1)
504	LEFT JOIN PRODFLASKHALSAR as FH2 on (FH2.ArtikelNr = AKTIVAORDRAR.ArtikelNr AND FH2.FHNum = 2)
505	LEFT JOIN PRODFLASKHALSAR as FH3 on (FH3.ArtikelNr = AKTIVAORDRAR.ArtikelNr AND FH3.FHNum = 3)
506	LEFT JOIN PRODFLASKHALSAR as FH4 on (FH4.ArtikelNr = AKTIVAORDRAR.ArtikelNr AND FH4.FHNum = 4)
507	LEFT JOIN PRODFLASKHALSAR as FH5 on (FH5.ArtikelNr = AKTIVAORDRAR.ArtikelNr AND FH5.FHNum = 5)
508	LEFT JOIN PRODFLASKHALSAR as FH6 on (FH6.ArtikelNr = AKTIVAORDRAR.ArtikelNr AND FH6.FHNum = 6)
509	LEFT JOIN PRODFLASKHALSAR as FH7 on (FH7.ArtikelNr = AKTIVAORDRAR.ArtikelNr AND FH7.FHNum = 7)
510	LEFT JOIN PRODFLASKHALSAR as FH8 on (FH8.ArtikelNr = AKTIVAORDRAR.ArtikelNr AND FH8.FHNum = 8)
511	LEFT JOIN PRODFLASKHALSAR as FH9 on (FH9.ArtikelNr = AKTIVAORDRAR.ArtikelNr AND FH9.FHNum = 9)
512	LEFT JOIN PRODFLASKHALSAR as FH10 on (FH10.ArtikelNr = AKTIVAORDRAR.ArtikelNr AND FH10.FHNum = 10)