wspr_lrar.sql

Please read your package and describe it at least 40 bytes in English. System will automatically de

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

















ALTER           procedure WSPR_LRAR
(
 @level char(2),
 @tahap char(2),
 @periode int
)
 as

 
-- declare @level char(2),@tahap char(2),@periode int
-- select @level = '5',@tahap = '4',@periode = 4

/*
declare @cur_thang varchar(50),@pemda_l varchar(50),@pemda_i varchar(50)
select @cur_thang = isnull((select config_val from Pemda where config_id = 'cur_thang'),'')
select @pemda_l = isnull((select config_val from Pemda where config_id = 'pemda_l'),'')
select @pemda_i = isnull((select config_val from Pemda where config_id = 'pemda_i'),'')
*/

if exists (select name from tempdb..sysobjects  where name = '##lrar')
  drop table ##lrar
if exists (select name from tempdb..sysobjects  where name = '##lrar1')
  drop table ##lrar1
if exists (select name from tempdb..sysobjects  where name = '##lrar2')
  drop table ##lrar2
if exists (select name from tempdb..sysobjects  where name = '##lrar3')
  drop table ##lrar3
if exists (select name from tempdb..sysobjects  where name = '##lrar4')
  drop table ##lrar4
if exists (select name from tempdb..sysobjects  where name = '##lrar5')
  drop table ##lrar5
if exists (select name from tempdb..sysobjects  where name = '##lrar6')
  drop table ##lrar6
if exists (select name from tempdb..sysobjects  where name = '##lrar7')
  drop table ##lrar7
if exists (select name from tempdb..sysobjects  where name = '##lrar8')
  drop table ##lrar8
if exists (select name from tempdb..sysobjects  where name = '##lrar9')
  drop table ##lrar9
if exists (select name from tempdb..sysobjects  where name = '##lrar10')
  drop table ##lrar10
if exists (select name from tempdb..sysobjects  where name = '##lrar11')
  drop table ##lrar11
if exists (select name from tempdb..sysobjects  where name = '##lrar12')
  drop table ##lrar12
if exists (select name from tempdb..sysobjects  where name = '##lrar13')
  drop table ##lrar13
if exists (select name from tempdb..sysobjects  where name = '##lrar14')
  drop table ##lrar14

declare @tglperiode datetime
declare @total1 money,@total2 money,@total3 money
declare @tfd char(1)
select @tfd = 'F'

select @tglperiode = (select akhir from periode where periode_id = @periode)

-- Hitung Nilai Anggaran utk 1 Thn
--Belanja Lsg
select mtgkey,sum(nilai) as nilai 
into ##lrar1 
from raskr
where kd_tahap  = @tahap and periode_id = 0
group by mtgkey

--Belanja tak Lsg
insert into ##lrar1 
select mtgkey,sum(nilai) as nilai 
from raskrtl
where kd_tahap  = @tahap and periode_id = 0
group by mtgkey

-- Hitung Nilai Anggaran Per Periode 
--Belanja Lsg
select mtgkey,sum(nilai) as nilai 
into ##lrar2 
from raskr
where kd_tahap  = @tahap and periode_id <> 0 and periode_id <= @periode 
group by mtgkey

--Belanja tak Lsg
insert into ##lrar2 
select mtgkey,sum(nilai) as nilai 
from raskrtl
where kd_tahap  = @tahap and periode_id <> 0 and periode_id <= @periode 
group by mtgkey

-- Pengeluaran STS (Berkurangnya Penerimaan)
--Belanja Lsg
select mtgkey,c.kdpers,sum(nilaists) as nilai 
into ##lrar3
from rkmdetr a left join bkud b on a.nobukas = b.nobukas and a.unitkey = b.unitkey
left join jtrnlkas c on a.nojetra = c.nojetra 
where tglvalid is not null and tglkas <= @tglperiode 
and a.nojetra in('02') and b.kdbukti in('12')
group by mtgkey,c.kdpers

--Belanja tak Lsg
insert into ##lrar3
select mtgkey,c.kdpers,sum(nilaists) as nilai 
from rkmdetrtl a left join bkud b on a.nobukas = b.nobukas and a.unitkey = b.unitkey
left join jtrnlkas c on a.nojetra = c.nojetra 
where tglvalid is not null and tglkas <= @tglperiode 
and a.nojetra in ('02') and b.kdbukti in('12')
group by mtgkey,c.kdpers

-- Pengeluaran Belanja SP2D
--Belanja Lsg
insert into ##lrar3
select mtgkey,c.kdpers,sum(nilaisp2d) as nilai 
from sp2ddetr a left join bkuk b on a.nosp2d = b.nosp2d
               left join jtrnlkas c on a.nojetra = c.nojetra 
               left join sp2d d on a.nosp2d = d.nosp2d and a.unitkey = d.unitkey where tglvalid is not null and tglkas <= @tglperiode 
and a.nojetra in ('05') and b.kdbukti in('20') 
group by mtgkey,c.kdpers

--Belanja tak Lsg
insert into ##lrar3
select mtgkey,c.kdpers,sum(nilaisp2d) as nilai 
from sp2ddetrtl a left join bkuk b on a.nosp2d = b.nosp2d
               left join jtrnlkas c on a.nojetra = c.nojetra 
               left join sp2d d on a.nosp2d = d.nosp2d and a.unitkey = d.unitkey
where tglvalid is not null and tglkas <= @tglperiode 
and a.nojetra in ('05') and b.kdbukti in('20') 
group by mtgkey,c.kdpers

-- SPJ Pengeluaran Belanja 
--Belanja Lsg
insert into ##lrar3
select mtgkey,c.kdpers,sum(nilaispj) as nilai 
from spjdetr a left join pspj b on a.nospj = b.nospj and a.unitkey = b.unitkey
               left join jtrnlkas c on a.nojetra = c.nojetra 
where tglvalid is not null 
and tglbuku <= @tglperiode 
--and kd_bulan <= cast (month(@tglperiode)as varchar(2))
and a.nojetra='13'
group by mtgkey,c.kdpers

--Belanja tak Lsg
insert into ##lrar3
select mtgkey,c.kdpers,sum(nilaispj) as nilai 
from spjdetrtl a left join pspj b on a.nospj = b.nospj and a.unitkey = b.unitkey
               left join jtrnlkas c on a.nojetra = c.nojetra 
where tglvalid is not null 
and tglbuku <= @tglperiode 
--and kd_bulan <= cast (month(@tglperiode)as varchar(2))
and a.nojetra='13'
group by mtgkey,c.kdpers

--Pengeluaran Jurnal Umum
--Belanja Lsg
insert into ##lrar3
select mtgkey,kdpers,sum(nilaijur) as nilai 
from rbmdetr a left join bktmem b on a.nobm = b.nobm and a.unitkey = b.unitkey
where validbm is not null and tglbm <= @tglperiode 
group by mtgkey,kdpers

--Pengeluaran Jurnal Umum
--Belanja tak Lsg
insert into ##lrar3
select mtgkey,kdpers,sum(nilaijur) as nilai 
from rbmdetrtl a left join bktmem b on a.nobm = b.nobm and a.unitkey = b.unitkey
where validbm is not null and tglbm <= @tglperiode 
group by mtgkey,kdpers

select mtgkey,
nilaid=(isnull((case when kdpers = 'D' then nilai end),0)),
nilaik=(isnull((case when kdpers = 'K' then nilai end),0)) 
into ##lrar4 from ##lrar3

select mtgkey,
sum(isnull(nilaid,0)) as nilaid,
sum(isnull(nilaik,0)) as nilaik 
into ##lrar5 from ##lrar4 
group by mtgkey

select mtgkey,(isnull(nilaid,0)-isnull(nilaik,0)) as nilai 
into ##lrar6
from ##lrar5

select mtgkey,sum(isnull(nilai,0)) as nilai 
into ##lrar7 from ##lrar6
group by mtgkey

create table ##lrar8
(
[mtgkey][varchar](30)null,
[nilai1][money] null,
[nilai2][money] null,
[nilai3][money] null
)

insert into ##lrar8(mtgkey,nilai1)
select mtgkey,isnull(nilai,0) from ##lrar1
insert into ##lrar8(mtgkey,nilai2)
select mtgkey,isnull(nilai,0) from ##lrar2
insert into ##lrar8(mtgkey,nilai3)
select mtgkey,isnull(nilai,0) from ##lrar7

select mtgkey,sum(isnull(nilai1,0)) as nilai1,
sum(isnull(nilai2,0)) as nilai2,sum(isnull(nilai3,0)) as nilai3
into ##lrar9 from ##lrar8 group by mtgkey

select @total1 = isnull((select sum(isnull(nilai1,0)) from ##lrar9),0)  
select @total2 = isnull((select sum(isnull(nilai2,0)) from ##lrar9),0)  
select @total3 = isnull((select sum(isnull(nilai3,0)) from ##lrar9),0)  

if @total1 <> 0 or @total2 <> 0 or @total3 <> 0
begin
  select @tfd = 'T'
end
  
select a.mtgkey,kdper,nmper,isnull(nilai1,0) as nilai1,
isnull(nilai2,0) as nilai2,isnull(nilai3,0) as nilai3,
mtglevel,type,idxall
into ##lrar10 
from matangr a left join ##lrar9 b on a.mtgkey = b.mtgkey 
order by idxall

select mtgkey,kdper,nmper,
nilai1=(case when type = 'D' then nilai1 else (select sum(nilai1) from ##lrar10 where ltrim(rtrim(idxall)) like ltrim(rtrim(a.idxall))+'%')end),
nilai2=(case when type = 'D' then nilai2 else (select sum(nilai2) from ##lrar10 where ltrim(rtrim(idxall)) like ltrim(rtrim(a.idxall))+'%')end),
nilai3=(case when type = 'D' then nilai3 else (select sum(nilai3) from ##lrar10 where ltrim(rtrim(idxall)) like ltrim(rtrim(a.idxall))+'%')end),
mtglevel,type,idxall 
into ##lrar11 
from ##lrar10 a 
order by idxall

select * into ##lrar12 from ##lrar11
where (type = 'H' and (nilai1 <> 0 or nilai2 <> 0 or nilai3 <> 0)) or 
(type = 'D' and (nilai1 <> 0 or nilai2 <> 0 or nilai3 <> 0))order by idxall

Select * into ##lrar13 From ##lrar12 Where cast(substring(mtglevel,2,1) as int) <= cast(@level as int)

create table ##lrar14
([nourut] [int] identity (1, 1) not null ,
[kdper] [varchar] (50)null ,
[nmper] [varchar](250)null ,
[nilai1] [money] null,
[nilai2] [money] null,
[nilai3] [money] null,
[type] [char] (2) null,
[mtglevel] [char] (2) null,
[idxall] [varchar](30) null
)

if @tfd = 'T'
begin
  insert into ##lrar14(kdper,nmper,nilai1,nilai2,nilai3,type,mtglevel,idxall)
  Select  kdper,nmper,nilai1,nilai2,nilai3,type,mtglevel,'01'+idxall
  from ##lrar13 

  insert into ##lrar14(kdper,nmper,nilai1,nilai2,nilai3,type,mtglevel,idxall)
  values('59','JUMLAH BELANJA',@total1,@total2,@total3,'J3','00','01'+'ZZ')
end

select *,(isnull(nilai3,0)-isnull(nilai2,0)) as nilai4,
sen4 = (case when isnull(nilai2,0) = 0 then 0 else ((isnull(nilai3,0)/isnull(nilai2,0))*100) end),
(isnull(nilai1,0)-isnull(nilai3,0)) as nilai5,
sen5 = (case when isnull(nilai1,0) = 0 then 0 else ((isnull(nilai3,0)/isnull(nilai1,0))*100) end)
--,@cur_thang as cur_thang, @pemda_l as nmpemda, @pemda_i as kota
INTO ##lrar
from ##lrar14
order by idxall




















GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO