ALTER PROCEDURE [dbo].[proc_sme_del_gauges_forecast_m]
@ProdKlass as varchar(4)
AS
declare @StartPeriod varchar(10)
,@EndPeriod varchar(10)
,@MidPeriod varchar(10)
set @StartPeriod = (select distinct convert(varchar,datepart(yyyy,(dateadd(mm,-36,convert(varchar,/**/convert(varchar,fiscalyear)+'-'+
right('0'+convert(varchar,fiscalperiod),2)/**/)+'-01'))))+'-'+
right('0'+convert(varchar,datepart(mm,(dateadd(m,-36,convert(varchar,convert(varchar,fiscalyear)+'-'+
right('0'+convert(varchar,fiscalperiod),2))+'-01')))),2)
from sme_sr_periodic_dates
where convert(varchar,actualdate,112) = convert(varchar,getdate(),112))
set @MidPeriod = (select fyfp from v_dates where datum = convert(varchar,getdate(),112))
set @EndPeriod = (select distinct convert(varchar,datepart(yyyy,(dateadd(mm,12,convert(varchar,/**/convert(varchar,fiscalyear)+'-'+
right('0'+convert(varchar,fiscalperiod),2)/**/)+'-01'))))+'-'+
right('0'+convert(varchar,datepart(mm,(dateadd(m,12,convert(varchar,convert(varchar,fiscalyear)+'-'+
right('0'+convert(varchar,fiscalperiod),2))+'-01')))),2)
from sme_sr_periodic_dates
where convert(varchar,actualdate,112) = convert(varchar,getdate(),112))
declare @affo int
set @affo = 1
select distinct
fyfp
,'fy' = fiscalyear
into #dates
from v_dates
where fyfp between @StartPeriod and @EndPeriod
select spd.fyfp
,'qty' = count(oh.ordernr)
,'artprodklass' = oh.q_prodklass
,'artprodklbeskr' = xp.artprodklbeskr
into #del
from oh inner join (select distinct
orp.ordernr
,'q_oh_deldaysr_date' = min(olh.q_oh_deldaysr_date)
from orp inner join orpp with(nolock) on orp.ordberlevdat = orpp.ordberlevdat
and orp.ordernr = orpp.ordernr
and orp.ordradnr = orpp.ordradnr
and orp.ordradnrstrpos = orpp.ordradnrstrpos
and orp.ordrestnr = orpp.ordrestnr
inner join orpk with(nolock) on orpp.kollinummer = orpk.kollinummer
and orpp.lagstalle = orpk.lagstalle
and orpp.ordberlevdat = orpk.ordberlevdat
and orpp.ordernr = orpk.ordernr
and orpp.olh_ordrestnr = orpk.olh_ordrestnr
inner join olh with(nolock) on orpk.lagstalle = olh.lagstalle
and orpk.ordberlevdat = olh.ordberlevdat
and orpk.ordernr = olh.ordernr
and orpk.ordlevnr = olh.ordlevnr
and orpk.olh_ordrestnr = olh.ordrestnr
inner join ar with(nolock) on orp.artnr = ar.artnr
where ar.varugruppkod in (43,44)
and orp.ordradnrstrpos = 0
and olh.q_oh_deldaysr_date is not null
group by orp.ordernr) olh_2 on oh.ordernr = olh_2.ordernr
inner join v_dates spd with(nolock) on spd.actualdate = olh_2.q_oh_deldaysr_date
inner join xp with(nolock) on oh.q_prodklass = xp.artprodklass
where oh.ordtyp = 90
and oh.ordstat <> 90
and oh.prospegd5 = 1
and oh.q_prodklass = @ProdKlass
and oh.q_oh_valkonto is null
and spd.fyfp between @StartPeriod and @EndPeriod
group by spd.fyfp
,oh.q_prodklass
,xp.artprodklbeskr
select distinct
#dates.fyfp
,'artprodklbeskr' = case
when #del.artprodklbeskr is null
then (select xp.artprodklbeskr from xp where xp.artprodklass = @ProdKlass)
else #del.artprodklbeskr end
,'flagga' = case
when #dates.fyfp > @MidPeriod
then 'forecast'
else 'used' end
,'InOrder' = isnull(i.ordantal,0)
,'ProdQty' = isnull(p.ProdQty,0)
,'InProdQty' = isnull(m.InProdQty,0)
,'DelQty' = cast(isnull(#del.qty,0) as int)
,#dates.fy
,'valueSEK' = n.netto
,'fakt_sys' = isnull(f.fakt_sys,0)
,'fakt_int' = isnull(c.fakt_int,0)
,'tot_fakt' = isnull(f.fakt_sys,0)+isnull(c.fakt_int,0)
from #dates left outer join #del with(nolock) on #dates.fyfp = #del.fyfp
left join (select v.fyfp
,'ordantal' = count (oh.ordernr)
from oh inner join (select distinct orp.ordernr
from orp inner join ar on orp.artnr = ar.artnr
where ar.varugruppkod in (43,44)
and orp.ordradst < 50
and orp.ordradnrstrpos = 0) orp_2 on oh.ordernr = orp_2.ordernr
inner join v_dates v on oh.ordlovlevdat = v.actualdate
where oh.ordtyp = 90
and oh.ordstat < 50
and oh.prospegd5 = 1
and oh.q_prodklass = @ProdKlass
and oh.q_oh_valkonto is null
group by v.fyfp) i on
#dates.fyfp = i.fyfp
left join (select v.fyfp
,'ProdQty' = count (oh.ordernr)
from oh inner join (select distinct orp.ordernr
from orp inner join ar on orp.artnr = ar.artnr
where ar.varugruppkod in (43,44)
and orp.q_oh_status2 in ('P5','P7')
and orp.ordradnrstrpos = 0) orp_2 on oh.ordernr = orp_2.ordernr
inner join v_dates v on oh.ordlovlevdat = v.actualdate
where oh.ordtyp = 90
and oh.ordstat <> 90
and oh.prospegd5 = 1
and oh.q_prodklass = @ProdKlass
and oh.q_oh_valkonto is null
group by v.fyfp) p on
#dates.fyfp = p.fyfp
left join (select v.fyfp
,'InProdQty' = count (oh.ordernr)
from oh inner join (select distinct orp.ordernr
from orp inner join ar on orp.artnr = ar.artnr
where ar.varugruppkod in (43,44)
and orp.q_oh_status2 in ('P1','P2','P3','P4')
and orp.ordradst between 13 and 49
and orp.ordradnrstrpos = 0) orp_2 on oh.ordernr = orp_2.ordernr
inner join v_dates v on oh.ordlovlevdat = v.actualdate
where oh.ordtyp = 90
and oh.ordstat between 13 and 49
and oh.prospegd5 = 1
and oh.q_prodklass = @ProdKlass
and oh.q_oh_valkonto is null
group by v.fyfp) m on
#dates.fyfp = m.fyfp
left join (select v.fyfp
,'netto' = sum(oh.q_oh_netamount_sek)
from oh inner join v_dates v on oh.ordlovlevdat = v.actualdate
where oh.q_prodklass = @ProdKlass
and oh.lagstalle = '0'
group by v.fyfp) n on
#dates.fyfp = n.fyfp
left join (select pd.fyfp
,'fakt_sys' = sum(fh.fakttotumoms)
from fh left join (select uoh.faktnr
,uft.ktonr
,uoh.ordernr
,uoh.prelfakttyp
from uoh inner join uft (nolock) on uoh.workfaktnr = uft.workfaktnr
where (uoh.prelfakttyp <> 910 OR uoh.prelfakttyp IS NULL) and uft.ktonr in ('3011','3012','3013','3014')
group by uoh.faktnr, uft.ktonr, uoh.ordernr, uoh.prelfakttyp) uoh on fh.faktnr = uoh.faktnr
left join oh (nolock) on fh.ordernr = oh.ordernr
left join oh oh_1 (nolock) on uoh.ordernr = oh_1.ordernr
inner join v_dates pd (nolock) on fh.faktdat = pd.actualdate
left join xp on oh.q_prodklass = xp.artprodklass or oh_1.q_prodklass = xp.artprodklass
where (oh.ordtyp IN (90, 190) or oh_1.ordtyp IN (90, 190))
and (oh.prospegd5 = @affo OR oh_1.prospegd5 = @affo)
and xp.artprodklass = @ProdKlass
and fh.faktnr not in (526,16583,200788,331390,98782,989009,989023,99239,99240,99242)
group by pd.fyfp) f on
#dates.fyfp = f.fyfp
left join (select pd.fyfp
,'fakt_int' = sum(fh.fakttotumoms)
from fh left join (select uoh.faktnr
,uft.ktonr
,uoh.ordernr
,uoh.prelfakttyp
from uoh inner join uft (nolock) on uoh.workfaktnr = uft.workfaktnr
where (uoh.prelfakttyp <> 910 OR uoh.prelfakttyp IS NULL) and uft.ktonr in ('3011','3012','3013','3014')
group by uoh.faktnr, uft.ktonr, uoh.ordernr, uoh.prelfakttyp) uoh on fh.faktnr = uoh.faktnr
left join oh (nolock) on fh.ordernr = oh.ordernr
left join oh oh_1 (nolock) on uoh.ordernr = oh_1.ordernr
inner join v_dates pd (nolock) on fh.faktdat = pd.actualdate
left join xp on oh.q_prodklass = xp.artprodklass or oh_1.q_prodklass = xp.artprodklass
where (oh.ordtyp IN (94, 194) or oh_1.ordtyp IN (94, 194))
and (oh.prospegd5 = @affo OR oh_1.prospegd5 = @affo)
and xp.artprodklass = @ProdKlass
and fh.faktnr not in (526,16583,200788,331390,98782,989009,989023,99239,99240,99242)
group by pd.fyfp) c on
#dates.fyfp = c.fyfp
order by #dates.fyfp
drop table #dates
drop table #del
