ALTER PROCEDURE "MONODBC"."sp_gummilagring"( /* [IN | OUT | INOUT] parameter_name parameter_type [DEFAULT default_value], ... */ )
as
BEGIN
Declare @saldoArt1 int
Declare @saldoArt2 int
Declare @saldoArt3 int
Declare @saldoArt4 int
Declare @saldoArt5 int
Declare @saldoArt6 int
Declare @saldoArt7 int
Declare @saldoArt8 int
Declare @saldoArt9 int
Declare @saldoArt10 int
Declare @saldoArt11 int
Declare @saldoArt12 int
Declare @saldoArt13 int
Declare @saldoArt14 int
Declare @saldoArt15 int
Declare @saldoArt16 int
Declare @artnr1ant int
Declare @artnr2ant int
Declare @art1 varchar(15)
Declare @art2 varchar(15)
Declare @art3 varchar(15)
Declare @art4 varchar(15)
Declare @art5 varchar(15)
Declare @art6 varchar(15)
Declare @art7 varchar(15)
Declare @art8 varchar(15)
Declare @art9 varchar(15)
Declare @art10 varchar(15)
Declare @art11 varchar(15)
Declare @art12 varchar(15)
Declare @art13 varchar(15)
Declare @art14 varchar(15)
Declare @art15 varchar(15)
Declare @art16 varchar(15)


set @art1 = 'VSV422652'
set @art2 = 'VSV423768'
set @art3 = 'VSV423769'
set @art4 = 'VSV423770'
set @art5 = 'VSV423771'
set @art6 = 'VSV441099'
set @art7 = 'VSV447709'
set @art8 = 'VSV452680'
set @art9 = 'VSV467564'
set @art10 = 'VSV467607'
set @art11 = 'VSV467636'
set @art12 = 'VSV468583'
set @art13 = 'VSV468800'
set @art14 = 'VSV470414'


set @saldoArt1 = (SELECT(SELECT convert(int,sum(LP_SALDO)) FROM MONITOR.ARTIKEL_LPLATS WHERE ARTIKEL_LPLATS.ART_ID = ARTIKEL.ART_ID)
FROM MONITOR.ARTIKEL WHERE ART_ARTNR = @art1)
set @saldoArt2 = (SELECT(SELECT convert(int,sum(LP_SALDO)) FROM MONITOR.ARTIKEL_LPLATS WHERE ARTIKEL_LPLATS.ART_ID = ARTIKEL.ART_ID)
FROM MONITOR.ARTIKEL WHERE ART_ARTNR = @art2)
set @saldoArt3 = (SELECT(SELECT convert(int,sum(LP_SALDO)) FROM MONITOR.ARTIKEL_LPLATS WHERE ARTIKEL_LPLATS.ART_ID = ARTIKEL.ART_ID)
FROM MONITOR.ARTIKEL WHERE ART_ARTNR = @art3)
set @saldoArt4 = (SELECT(SELECT convert(int,sum(LP_SALDO)) FROM MONITOR.ARTIKEL_LPLATS WHERE ARTIKEL_LPLATS.ART_ID = ARTIKEL.ART_ID)
FROM MONITOR.ARTIKEL WHERE ART_ARTNR = @art4)
set @saldoArt5 = (SELECT(SELECT convert(int,sum(LP_SALDO)) FROM MONITOR.ARTIKEL_LPLATS WHERE ARTIKEL_LPLATS.ART_ID = ARTIKEL.ART_ID)
FROM MONITOR.ARTIKEL WHERE ART_ARTNR = @art5)
set @saldoArt6 = (SELECT(SELECT convert(int,sum(LP_SALDO)) FROM MONITOR.ARTIKEL_LPLATS WHERE ARTIKEL_LPLATS.ART_ID = ARTIKEL.ART_ID)
FROM MONITOR.ARTIKEL WHERE ART_ARTNR = @art6)
set @saldoArt7 = (SELECT(SELECT convert(int,sum(LP_SALDO)) FROM MONITOR.ARTIKEL_LPLATS WHERE ARTIKEL_LPLATS.ART_ID = ARTIKEL.ART_ID)
FROM MONITOR.ARTIKEL WHERE ART_ARTNR = @art7)
set @saldoArt8 = (SELECT(SELECT convert(int,sum(LP_SALDO)) FROM MONITOR.ARTIKEL_LPLATS WHERE ARTIKEL_LPLATS.ART_ID = ARTIKEL.ART_ID)
FROM MONITOR.ARTIKEL WHERE ART_ARTNR = @art8)
set @saldoArt9 = (SELECT(SELECT convert(int,sum(LP_SALDO)) FROM MONITOR.ARTIKEL_LPLATS WHERE ARTIKEL_LPLATS.ART_ID = ARTIKEL.ART_ID)
FROM MONITOR.ARTIKEL WHERE ART_ARTNR = @art9)
set @saldoArt10 = (SELECT(SELECT convert(int,sum(LP_SALDO)) FROM MONITOR.ARTIKEL_LPLATS WHERE ARTIKEL_LPLATS.ART_ID = ARTIKEL.ART_ID)
FROM MONITOR.ARTIKEL WHERE ART_ARTNR = @art10)
set @saldoArt11 = (SELECT(SELECT convert(int,sum(LP_SALDO)) FROM MONITOR.ARTIKEL_LPLATS WHERE ARTIKEL_LPLATS.ART_ID = ARTIKEL.ART_ID)
FROM MONITOR.ARTIKEL WHERE ART_ARTNR = @art11)
set @saldoArt12 = (SELECT(SELECT convert(int,sum(LP_SALDO)) FROM MONITOR.ARTIKEL_LPLATS WHERE ARTIKEL_LPLATS.ART_ID = ARTIKEL.ART_ID)
FROM MONITOR.ARTIKEL WHERE ART_ARTNR = @art12)
set @saldoArt13 = (SELECT(SELECT convert(int,sum(LP_SALDO)) FROM MONITOR.ARTIKEL_LPLATS WHERE ARTIKEL_LPLATS.ART_ID = ARTIKEL.ART_ID)
FROM MONITOR.ARTIKEL WHERE ART_ARTNR = @art13)
set @saldoArt14 = (SELECT(SELECT convert(int,sum(LP_SALDO)) FROM MONITOR.ARTIKEL_LPLATS WHERE ARTIKEL_LPLATS.ART_ID = ARTIKEL.ART_ID)
FROM MONITOR.ARTIKEL WHERE ART_ARTNR = @art14)

//select @saldoArt8

drop table if EXISTS #TempGummilagring
CREATE LOCAL TEMPORARY TABLE #TempGummilagring ( OrderNr nvarchar(15),Rowkey nvarchar(32),ArtikelNr nVARCHAR (16),ArtBeskrivning nvarchar (35),Amount INT, LevDatum nvarchar(10),calcAmount INT )


--delete from tblVSVMont
insert into #TempGummilagring(OrderNr,Rowkey,Artikelnr,Artbeskrivning,Amount,levDatum,calcAmount)
select KORDERRAD.KO_NR,
KORDERRAD.KO_NR+'_'+KORDERRAD.KOR_ARTNR+'_'+convert(VARCHAR(20),kor_ldat,112),
 KORDERRAD.KOR_ARTNR,
 KORDERRAD.KOR_TXT,
 convert(int,KORDERRAD.KOR_RANT),
 convert(date,KORDERRAD.KOR_LDAT),
 0
 from MONITOR.KORDERRAD join monitor.korder on korder.ko_id = KORDERRAD.ko_id
where KORDERRAD.KOR_ARTNR in (@art1,@art2,@art3,@art4,@art5,@art6,@art7,@art8,@art9,@art10,@art11,@art12,@art13,@art14) AND (Monitor.korder.ko_status between 1 AND 5) AND KORDERRAD.KOR_RTYP =1 AND KORDERRAD.KOR_RANT > 0
order by KORDERRAD.KOR_LDAT

DECLARE CheckOrderNr CURSOR FOR
SELECT Rowkey
FROM #TempGummilagring
DECLARE @OrderNummer CHAR(40)
OPEN CheckOrderNr
FETCH NEXT CheckOrderNr INTO @OrderNummer
WHILE @@FETCH_STATUS = 0

BEGIN
update #TempGummilagring
set calcAmount =
(CASE
WHEN @art1=ArtikelNr THEN (@saldoart1 - Amount)
WHEN @art2=ArtikelNr THEN (@saldoart2 - Amount)
WHEN @art3=ArtikelNr THEN (@saldoart3 - Amount)
WHEN @art4=ArtikelNr THEN (@saldoart4 - Amount)
WHEN @art5=ArtikelNr THEN (@saldoart5 - Amount)
WHEN @art6=ArtikelNr THEN (@saldoart6 - Amount)
WHEN @art7=ArtikelNr THEN (@saldoart7 - Amount)
WHEN @art8=ArtikelNr THEN (@saldoart8 - Amount)
WHEN @art9=ArtikelNr THEN (@saldoart9 - Amount)
WHEN @art10=ArtikelNr THEN (@saldoart10 - Amount)
WHEN @art11=ArtikelNr THEN (@saldoart11 - Amount)
WHEN @art12=ArtikelNr THEN (@saldoart12 - Amount)
WHEN @art13=ArtikelNr THEN (@saldoart13 - Amount)
WHEN @art14=ArtikelNr THEN (@saldoart14 - Amount)
ELSE
'100'
END)
where Rowkey = @OrderNummer

set @saldoart1 = (SELECT CASE WHEN @art1=ArtikelNr THEN calcAmount
ELSE @saldoart1 END
FROM #TempGummilagring
WHERE @OrderNummer=Rowkey)
set @saldoart2 = (SELECT CASE WHEN @art2=ArtikelNr THEN calcAmount
ELSE @saldoart2 END
FROM #TempGummilagring
WHERE @OrderNummer=Rowkey)
set @saldoart3 = (SELECT CASE WHEN @art3=ArtikelNr THEN calcAmount
ELSE @saldoart3 END
FROM #TempGummilagring
WHERE @OrderNummer=Rowkey)
set @saldoart4 = (SELECT CASE WHEN @art4=ArtikelNr THEN calcAmount
ELSE @saldoart4 END
FROM #TempGummilagring
WHERE @OrderNummer=Rowkey)
set @saldoart5 = (SELECT CASE WHEN @art5=ArtikelNr THEN calcAmount
ELSE @saldoart5 END
FROM #TempGummilagring
WHERE @OrderNummer=Rowkey)
set @saldoart6 = (SELECT CASE WHEN @art6=ArtikelNr THEN calcAmount
ELSE @saldoart6 END
FROM #TempGummilagring
WHERE @OrderNummer=Rowkey)
set @saldoart7 = (SELECT CASE WHEN @art7=ArtikelNr THEN calcAmount
ELSE @saldoart7 END
FROM #TempGummilagring
WHERE @OrderNummer=Rowkey)
set @saldoart8 = (SELECT CASE WHEN @art8=ArtikelNr THEN calcAmount
ELSE @saldoart8 END
FROM #TempGummilagring
WHERE @OrderNummer=Rowkey)
set @saldoart9 = (SELECT CASE WHEN @art9=ArtikelNr THEN calcAmount
ELSE @saldoart9 END
FROM #TempGummilagring
WHERE @OrderNummer=Rowkey)
set @saldoart10 = (SELECT CASE WHEN @art10=ArtikelNr THEN calcAmount
ELSE @saldoart10 END
FROM #TempGummilagring
WHERE @OrderNummer=Rowkey)
set @saldoart11 = (SELECT CASE WHEN @art11=ArtikelNr THEN calcAmount
ELSE @saldoart11 END
FROM #TempGummilagring
WHERE @OrderNummer=Rowkey)
set @saldoart12 = (SELECT CASE WHEN @art12=ArtikelNr THEN calcAmount
ELSE @saldoart12 END
FROM #TempGummilagring
WHERE @OrderNummer=Rowkey)
set @saldoart13 = (SELECT CASE WHEN @art13=ArtikelNr THEN calcAmount
ELSE @saldoart13 END
FROM #TempGummilagring
WHERE @OrderNummer=Rowkey)
set @saldoart14 = (SELECT CASE WHEN @art14=ArtikelNr THEN calcAmount
ELSE @saldoart14 END
FROM #TempGummilagring
WHERE @OrderNummer=Rowkey)



FETCH NEXT CheckOrderNr INTO @OrderNummer
END

CLOSE CheckOrderNr
//select @saldoArt8

SELECT OrderNr, ArtikelNr, ArtBeskrivning, Amount, LevDatum, calcAmount from #TempGummilagring WHERE calcAmount < '0'

END