wspr_lra.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_LRA
(
  @level char(2),
 @tahap char(2),
 @periode int,
 @hal char(2)
)
as

-- 
-- declare @level char(2),@tahap char(2),@periode int
-- select @level = '5',@tahap = '2',@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'),'')

declare @jabttd char(50), @namattd char(50), @nipttd char(50)
select @jabttd = isnull((select config_val from pemda where CONFIG_ID ='pemda_j'),'')
select @namattd = isnull((select config_val from pemda where CONFIG_ID ='pemda_k'),'')
select @nipttd = ''

if exists (select name from tempdb..sysobjects  where name = '##lra')
  drop table ##lra

create table ##lra
([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,
[nilai4] [money] null,
[sen4] [money] null,
[nilai5] [money] null,
[sen5] [money] null)

exec WSPR_LRAD @level, @tahap, @periode
exec WSPR_LRAR @level, @tahap, @periode
exec WSPR_LRAB @level, @tahap, @periode

declare @nilaid1 money, @nilaid2 money, @nilaid3 money
declare @nilair1 money, @nilair2 money, @nilair3 money
declare @nilairtl1 money, @nilairtl2 money, @nilairtl3 money
declare @nilaidr1 money, @nilaidr2 money, @nilaidr3 money
declare @nilaidr4 money, @nilaidr5 money, @sen4 money, @sen5 money
declare @nilaiSD1 money, @nilaiSD2 money, @nilaiSD3 money 
declare @nilaiPB1 money, @nilaiPB2 money, @nilaiPB3 money 
declare @nilaiSL1 money, @nilaiSL2 money, @nilaiSL3 money, @nilaiSL4 money, @nilaiSL5 money 

--Insert Pendapatan
INSERT INTO ##lra
select kdper, nmper, nilai1, nilai2, nilai3, type, mtglevel, idxall, nilai4, sen4, nilai5, sen5
from ##lrad order by idxall
select @nilaid1 = isnull((select nilai1 from ##lrad where type = 'J1'),0)  
select @nilaid2 = isnull((select nilai2 from ##lrad where type = 'J1'),0)  
select @nilaid3 = isnull((select nilai3 from ##lrad where type = 'J1'),0) 

--Insert Belanja
INSERT INTO ##lra
select kdper, nmper, nilai1, nilai2, nilai3, type, mtglevel, idxall, nilai4, sen4, nilai5, sen5
from ##lrar order by idxall

select @nilairtl1 = isnull((select nilai1 from ##lrar where type = 'J2'),0)  
select @nilairtl2 = isnull((select nilai2 from ##lrar where type = 'J2'),0)  
select @nilairtl3 = isnull((select nilai3 from ##lrar where type = 'J2'),0)  

select @nilair1 = isnull((select nilai1 from ##lrar where type = 'J3'),0)  
select @nilair2 = isnull((select nilai2 from ##lrar where type = 'J3'),0)  
select @nilair3 = isnull((select nilai3 from ##lrar where type = 'J3'),0)  

select @nilaidr1 = isnull(@nilaid1,0)-isnull(@nilair1,0)
select @nilaidr2 = isnull(@nilaid2,0)-isnull(@nilair2,0)
select @nilaidr3 = isnull(@nilaid3,0)-isnull(@nilair3,0)
select @nilaidr4 = isnull(@nilaidr3,0)-isnull(@nilaidr2,0)
select @nilaidr5 = isnull(@nilaidr1,0)-isnull(@nilaidr3,0)
select @sen4 = (case when isnull(@nilaidr2,0) = 0 then 0 else ((isnull(@nilaidr3,0)/isnull(@nilaidr2,0))*100) end)
select @sen5 = (case when isnull(@nilaidr1,0) = 0 then 0 else ((isnull(@nilaidr3,0)/isnull(@nilaidr1,0))*100) end)

 INSERT INTO ##lra(nmper,nilai1,nilai2,nilai3,type,mtglevel,idxall,nilai4, sen4, nilai5, sen5)
 values('JUMLAH SURPLUS/DEFISIT',@nilaidr1,@nilaidr2,@nilaidr3,'J5','00','06',@nilaidr4, @sen4, @nilaidr5, @sen5)

--Insert Pembiayaan
INSERT INTO ##lra
select kdper, nmper, nilai1, nilai2, nilai3, type, mtglevel, idxall, nilai4, sen4, nilai5, sen5
from ##lrab order by idxall


select @nilaiSD1 = isnull((select nilai1 from ##lra where type = 'J5'),0)  
select @nilaiSD2 = isnull((select nilai2 from ##lra where type = 'J5'),0)  
select @nilaiSD3 = isnull((select nilai3 from ##lra where type = 'J5'),0)  

select @nilaiPB1 = isnull((select nilai1 from ##lra where type = 'J8'),0)  
select @nilaiPB2 = isnull((select nilai2 from ##lra where type = 'J8'),0)  
select @nilaiPB3 = isnull((select nilai3 from ##lra where type = 'J8'),0)  

select @nilaiSL1 = isnull(@nilaiSD1,0)+isnull(@nilaiPB1,0)
select @nilaiSL2 = isnull(@nilaiSD2,0)+isnull(@nilaiPB2,0)
select @nilaiSL3 = isnull(@nilaiSD3,0)+isnull(@nilaiPB3,0)
select @nilaiSL4 = isnull(@nilaiSL3,0)+isnull(@nilaiSL2,0)
select @nilaiSL5 = isnull(@nilaiSL1,0)+isnull(@nilaiSL3,0)
--select @sen4 = (case when isnull(@nilaiSL2,0) = 0 then 0 else ((isnull(@nilaiSL3,0)/isnull(@nilaiSL2,0))*100) end)
--select @sen5 = (case when isnull(@nilaiSL1,0) = 0 then 0 else ((isnull(@nilaiSL3,0)/isnull(@nilaiSL1,0))*100) end)

INSERT INTO ##lra(nmper,nilai1,nilai2,nilai3,type,mtglevel,idxall,nilai4, sen4, nilai5, sen5)
values('SISA LEBIH PEMBIAYAAN ANGGARAN (SILPA)',@nilaiSL1,@nilaiSL2,@nilaiSL3,'J9','00','11',@nilaiSL4, @sen4, @nilaiSL5, @sen5)

select *, @cur_thang as cur_thang, @pemda_l as nmpemda, @pemda_i as kota, 
@jabttd as jabttd, @namattd as namattd, @nipttd as nipttd, @hal as hal
from ##lra





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO