wspr_lrab.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_LRAB
(
 @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 = '##lrab')
  drop table ##lrab
if exists (select name from tempdb..sysobjects  where name = '##lrab1')
  drop table ##lrab1
if exists (select name from tempdb..sysobjects  where name = '##lrab2')
  drop table ##lrab2
if exists (select name from tempdb..sysobjects  where name = '##lrab3')
  drop table ##lrab3
if exists (select name from tempdb..sysobjects  where name = '##lrab4')
  drop table ##lrab4
if exists (select name from tempdb..sysobjects  where name = '##lrab5')
  drop table ##lrab5
if exists (select name from tempdb..sysobjects  where name = '##lrab6')
  drop table ##lrab6
if exists (select name from tempdb..sysobjects  where name = '##lrab7')
  drop table ##lrab7
if exists (select name from tempdb..sysobjects  where name = '##lrab8')
  drop table ##lrab8
if exists (select name from tempdb..sysobjects  where name = '##lrab9')
  drop table ##lrab9
if exists (select name from tempdb..sysobjects  where name = '##lrab10')
  drop table ##lrab10
if exists (select name from tempdb..sysobjects  where name = '##lrab11')
  drop table ##lrab11
if exists (select name from tempdb..sysobjects  where name = '##lrab12')
  drop table ##lrab12
if exists (select name from tempdb..sysobjects  where name = '##lrab13')
  drop table ##lrab13
if exists (select name from tempdb..sysobjects  where name = '##lrab14')
  drop table ##lrab14

declare @nilaitr1 money,@nilaitr2 money,@nilaitr3 money 
declare @nilaikr1 money,@nilaikr2 money,@nilaikr3 money
declare @total1 money,@total2 money,@total3 money
declare @ckdtr char(4),@ckdkr char(4),@tfpbytr char(1),@tfpbykr char(1)
declare @panj int
declare @tglperiode datetime


select @tfpbytr = 'T',@tfpbykr = 'T'
select @panj = 13

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

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

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

-- Pengeluaran STS (Berkurangnya Penerimaan)
select mtgkey,c.kdpers,sum(nilaists) as nilai 
into ##lrab3
from rkmdetb 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','02') and b.kdbukti in ('12','15')
group by mtgkey,c.kdpers

-- Pengeluaran Belanja SP2D
insert into ##lrab3
select mtgkey,c.kdpers,sum(nilaisp2d) as nilai
from sp2ddetb 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='05' and b.kdbukti='20'
group by mtgkey,c.kdpers

-- Realisasi SPJ
insert into ##lrab3
select mtgkey,c.kdpers,sum(nilaispj) as nilai 
from spjdetb 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 kd_bulan <= month(@tglperiode)
and a.nojetra='13'
group by mtgkey,c.kdpers

-- Jurnal Umum
insert into ##lrab3
select mtgkey,kdpers,sum(nilaijur) as nilai 
from rbmdetb 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

-- Memisahkan nilai Debet dgn Kredit
select mtgkey,
nilaid=(isnull((case when kdpers = 'D' then nilai end),0)),
nilaik=(isnull((case when kdpers = 'K' then nilai end),0)) 
into ##lrab4 from ##lrab3

-- Pengelompokkan Nilai Per Rekening
select mtgkey,
sum(isnull(nilaid,0)) as nilaid,
sum(isnull(nilaik,0)) as nilaik 
into ##lrab5 from ##lrab4 
group by mtgkey

select a.mtgkey,
nilai = (case when substring(kdper,3,1) = '1' 
               then (isnull(nilaik,0)-isnull(nilaid,0)) 
               else (isnull(nilaid,0)-isnull(nilaik,0)) 
         end) 
into ##lrab6
from ##lrab5 a left join matangb b on a.mtgkey = b.mtgkey 

select mtgkey,sum(isnull(nilai,0)) as nilai 
into ##lrab7 
from ##lrab6 
group by mtgkey

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

insert into ##lrab8(mtgkey,nilai1)
select mtgkey,isnull(nilai,0) from ##lrab1
insert into ##lrab8(mtgkey,nilai2)
select mtgkey,isnull(nilai,0) from ##lrab2
insert into ##lrab8(mtgkey,nilai3)
select mtgkey,isnull(nilai,0) from ##lrab7

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

-- Hitung Nilai Penerimaan Pembiayaan
select @nilaitr1 = isnull((select sum(isnull(nilai1,0)) 
from ##lrab9 a left join matangb b on a.mtgkey = b.mtgkey 
where substring(kdper,3,1) = '1' ),0)  

select @nilaitr2 = isnull((select sum(isnull(nilai2,0)) 
from ##lrab9 a left join matangb b on a.mtgkey = b.mtgkey 
where substring(kdper,3,1) = '1' ),0)  

select @nilaitr3 = isnull((select sum(isnull(nilai3,0)) 
from ##lrab9 a left join matangb b on a.mtgkey = b.mtgkey 
where substring(kdper,3,1) = '1' ),0)  

-- Hitung Nilai Pengeluaran Pembiayaan
select @nilaikr1 = isnull((select sum(isnull(nilai1,0)) 
from ##lrab9 a left join matangb b on a.mtgkey = b.mtgkey 
where substring(kdper,3,1) = '2' ),0)  

select @nilaikr2 = isnull((select sum(isnull(nilai2,0)) 
from ##lrab9 a left join matangb b on a.mtgkey = b.mtgkey 
where substring(kdper,3,1) = '2' ),0)  

select @nilaikr3 = isnull((select sum(isnull(nilai3,0)) 
from ##lrab9 a left join matangb b on a.mtgkey = b.mtgkey 
where substring(kdper,3,1) = '2' ),0)  

-- Hitung Total Nilai Pembiayaan
select @total1 = isnull(@nilaitr1,0)-isnull(@nilaikr1,0)
select @total2 = isnull(@nilaitr2,0)-isnull(@nilaikr2,0)
select @total3 = isnull(@nilaitr3,0)-isnull(@nilaikr3,0)

if @nilaitr1 = 0 and @nilaitr2 = 0 and @nilaitr3 = 0 
begin 
  select @tfpbytr = 'F'
end

if @nilaikr1 = 0 and @nilaikr2 = 0 and @nilaikr3 = 0

begin 
  select @tfpbykr = 'F'
end

-- Hanya Pembiayaan Saja Tanpa Rek Non.Anggaran
select a.mtgkey,kdper,nmper,isnull(nilai1,0) as nilai1,isnull(nilai2,0) as nilai2,
isnull(nilai3,0) as nilai3,mtglevel,type,idxall,babid
into ##lrab10
from matangb a left join ##lrab9 b on a.mtgkey = b.mtgkey 
where babid = 6 and left(kdper,1)='6' and substring(mtglevel,2,1) <> '1' 
order by kdper
-- Termasuk Non anggaran 
-- babid = 6 or babid = 7 


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

select * into ##lrab12 from ##lrab11
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 kdper

Select * Into ##lrab13 From ##lrab12 
Where  cast(substring(mtglevel,2,1) as int) <= cast(@level as int)

create table ##lrab14
([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 @tfpbytr = 'T'
begin
  insert into ##lrab14(kdper,nmper,nilai1,nilai2,nilai3,type,mtglevel,idxall)
  Select kdper,nmper,nilai1,nilai2,nilai3,type,mtglevel,'08'+idxall
  from ##lrab13 where babid = 6 and substring(kdper,3,1) = '1'

  insert into ##lrab14(kdper,nmper,nilai1,nilai2,nilai3,type,mtglevel,idxall)
  values ('6.1z','JUMLAH PENERIMAAN DAERAH',@nilaitr1,@nilaitr2,@nilaitr3,'J6','00','08'+'ZZ')
end

if @tfpbykr = 'T'
begin
  insert into ##lrab14(kdper,nmper,nilai1,nilai2,nilai3,type,mtglevel,idxall)
  Select  kdper,nmper,nilai1,nilai2,nilai3,type,mtglevel,'09'+idxall
  from ##lrab13 where babid = 6 and substring(kdper,3,1) = '2'
  
  insert into ##lrab14(kdper,nmper,nilai1,nilai2,nilai3,type,mtglevel,idxall)
  values ('6.2z','JUMLAH PENGELUARAN DAERAH',@nilaikr1,@nilaikr2,@nilaikr3,'J7','00','09'+'ZZ')
end

if @tfpbytr = 'T' or @tfpbykr = 'T'
begin
  insert into ##lrab14(kdper,nmper,nilai1,nilai2,nilai3,type,mtglevel,idxall)
  Select  kdper,nmper,@total1,@total2,@total3,type,mtglevel,'07'
  from matangb where babid = 6 and left(kdper,1)='6' and substring(mtglevel,2,1) = '1' 
  -- Termasuk Non anggaran 
  -- babid = 6 or babid = 7 

  insert into ##lrab14(kdper,nmper,nilai1,nilai2,nilai3,type,mtglevel,idxall)
  values('6z','JUMLAH PEMBIAYAAN',@total1,@total2,@total3,'J8','00','10')
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 ##lrab
from ##lrab14 order by idxall






















GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO