wspr_lrad.sql

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

















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

/*
declare @level char(2),@tahap char(2),@periode int
select @level = '5',@tahap = '1',@periode = 2

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 = '##lrad')
  drop table ##lrad
if exists (select name from tempdb..sysobjects  where name = '##lrad1')
  drop table ##lrad1
if exists (select name from tempdb..sysobjects  where name = '##lrad2')
  drop table ##lrad2
if exists (select name from tempdb..sysobjects  where name = '##lrad3')
  drop table ##lrad3
if exists (select name from tempdb..sysobjects  where name = '##lrad4')
  drop table ##lrad4
if exists (select name from tempdb..sysobjects  where name = '##lrad5')
  drop table ##lrad5
if exists (select name from tempdb..sysobjects  where name = '##lrad6')
  drop table ##lrad6
if exists (select name from tempdb..sysobjects  where name = '##lrad7')
  drop table ##lrad7
if exists (select name from tempdb..sysobjects  where name = '##lrad8')
  drop table ##lrad8
if exists (select name from tempdb..sysobjects  where name = '##lrad9')
  drop table ##lrad9
if exists (select name from tempdb..sysobjects  where name = '##lrad10')
  drop table ##lrad10
if exists (select name from tempdb..sysobjects  where name = '##lrad11')
  drop table ##lrad11
if exists (select name from tempdb..sysobjects  where name = '##lrad12')
  drop table ##lrad12
if exists (select name from tempdb..sysobjects  where name = '##lrad13')
  drop table ##lrad13
if exists (select name from tempdb..sysobjects  where name = '##lrad14')
  drop table ##lrad14

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
select mtgkey,sum(nilai) as nilai 
into ##lrad1 
from raskd
where kd_tahap  = @tahap and periode_id = 0
group by mtgkey

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

-- Transaksi Pendapatan(STS)
select mtgkey,c.kdpers,sum(nilaists) as nilai 
into ##lrad3
from rkmdetd 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('01') and b.kdbukti in('11')
group by mtgkey,c.kdpers

-- Transaksi Pengeluaran(Sp2d)
insert into ##lrad3
select mtgkey,c.kdpers,sum(nilaisp2d) as nilai 
from sp2ddetd a left join bkuk b on a.nosp2d = b.nosp2d 
                left join jtrnlkas c on a.nojetra = c.nojetra 
where tglvalid is not null and tglkas <= @tglperiode 
and a.nojetra='07' and b.kdbukti='20'
group by mtgkey,c.kdpers

-- Transaksi Jurnal Umum / Memerioal Koreksi dll
insert into ##lrad3
select mtgkey,kdpers,sum(nilaijur) as nilai 
from rbmdetd 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 ##lrad4 from ##lrad3

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

select mtgkey,(isnull(nilaik,0)-isnull(nilaid,0)) as nilai 
into ##lrad6
from ##lrad5

select mtgkey,sum(isnull(nilai,0)) as nilai 
into ##lrad7 from ##lrad6
group by mtgkey

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

insert into ##lrad8(mtgkey,nilai1)
select mtgkey,isnull(nilai,0) from ##lrad1
insert into ##lrad8(mtgkey,nilai2)
select mtgkey,isnull(nilai,0) from ##lrad2
insert into ##lrad8(mtgkey,nilai3)
select mtgkey,isnull(nilai,0) from ##lrad7

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

select @total1 = isnull((select sum(isnull(nilai1,0)) from ##lrad9),0)  
select @total2 = isnull((select sum(isnull(nilai2,0)) from ##lrad9),0)  
select @total3 = isnull((select sum(isnull(nilai3,0)) from ##lrad9),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 ##lrad10 
from matangd a left join ##lrad9 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 ##lrad10 where ltrim(rtrim(idxall)) like ltrim(rtrim(a.idxall))+'%')end),
nilai2=(case when type = 'D' then nilai2 else (select sum(nilai2) from ##lrad10 where ltrim(rtrim(idxall)) like ltrim(rtrim(a.idxall))+'%')end),
nilai3=(case when type = 'D' then nilai3 else (select sum(nilai3) from ##lrad10 where ltrim(rtrim(idxall)) like ltrim(rtrim(a.idxall))+'%')end),
mtglevel,type,idxall 
into ##lrad11 
from ##lrad10 a 
order by idxall

select * into ##lrad12 from ##lrad11
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 ##lrad13 From ##lrad12
Where cast(substring(mtglevel,2,1) as int) <= cast(@level as int)

create table ##lrad14
([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 ##lrad14(kdper,nmper,nilai1,nilai2,nilai3,type,mtglevel,idxall)
  Select  kdper,nmper,nilai1,nilai2,nilai3,type,mtglevel,'01'+idxall
  from ##lrad13 

  insert into ##lrad14(kdper,nmper,nilai1,nilai2,nilai3,type,mtglevel,idxall)
  values('4z','JUMLAH PENDAPATAN',@total1,@total2,@total3,'J1','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 ##lrad
from ##lrad14
order by idxall







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO