update ikon.ref_pemda set province_id='01' where province_id='1';
update ikon.ref_pemda set province_id='02' where province_id='2';
update ikon.ref_pemda set province_id='03' where province_id='3';
update ikon.ref_pemda set province_id='04' where province_id='4';
update ikon.ref_pemda set province_id='05' where province_id='5';
update ikon.ref_pemda set province_id='06' where province_id='6';
update ikon.ref_pemda set province_id='07' where province_id='7';
update ikon.ref_pemda set province_id='08' where province_id='8';
update ikon.ref_pemda set province_id='09' where province_id='9';

CREATE OR REPLACE VIEW ikon.vw_lra AS select a.tahun,a.kd_provinsi,a.periode_id,a.kd_rek1,a.kd_rek2,a.kd_rek3,
(select nama as namaakun from  ikon.ref_akun  
        where kd_level1=a.kd_rek1 and kd_level2=0 
and kd_level3=0 and kd_level4='0' and kd_level5='0' and
        idbas=b.idbas) as namaakun1,
( select nama as namaakun from  ikon.ref_akun  
        where kd_level1=a.kd_rek1 and kd_level2=a.kd_rek2 
        and kd_level3=0 and kd_level4='0' and kd_level5='0' and
        idbas=b.idbas) as namaakun2,
(select nama as namaakun from  ikon.ref_akun  
        where kd_level1=a.kd_rek1 and kd_level2=a.kd_rek2 
and kd_level3=a.kd_rek3 and kd_level4='0' and kd_level5='0' and
        idbas=b.idbas) as namakun3,
sum(a.realisasi) as realisasi,coalesce(sum(a.realisasi)-
(select sum(nilai_eliminasi) from  ikon.akun_eliminasi  
        where kd_rek1=a.kd_rek1 and kd_rek2=a.kd_rek2 
and kd_rek3=a.kd_rek3 and kd_pemda=a.kd_provinsi and 
        idbas=b.idbas group by kd_rek1,kd_rek2,kd_rek3),sum(a.realisasi)) as nilai_eliminasi,
a.dk,b.idbas from ikon.raw_data a 
inner join ikon.header_data b on a.header_data_id=b.id 
inner join ikon.ref_jlaporan d on b.id_jnslaporan=d.id 
where d.id=1
group by a.tahun,a.kd_provinsi,a.periode_id,a.kd_rek1,a.kd_rek2,a.kd_rek3
,a.dk,b.idbas order by a.kd_provinsi asc; 
	       
		

CREATE OR REPLACE VIEW ikon.vw_neracalap AS select a.tahun,a.kd_provinsi,a.periode_id,a.kd_rek1,a.kd_rek2,a.kd_rek3,
(select nama as namaakun from  ikon.ref_akun  
        where kd_level1=a.kd_rek1 and kd_level2=0 
and kd_level3=0 and kd_level4='0' and kd_level5='0' and
        idbas=b.idbas) as namaakun1,
( select nama as namaakun from  ikon.ref_akun  
        where kd_level1=a.kd_rek1 and kd_level2=a.kd_rek2 
        and kd_level3=0 and kd_level4='0' and kd_level5='0' and
        idbas=b.idbas) as namaakun2,
(select nama as namaakun from  ikon.ref_akun  
        where kd_level1=a.kd_rek1 and kd_level2=a.kd_rek2 
and kd_level3=a.kd_rek3 and kd_level4='0' and kd_level5='0' and
        idbas=b.idbas) as namakun3,
sum(a.realisasi) as realisasi,coalesce(sum(a.realisasi)-
(select sum(nilai_eliminasi) from  ikon.akun_eliminasi  
        where kd_rek1=a.kd_rek1 and kd_rek2=a.kd_rek2 
and kd_rek3=a.kd_rek3 and kd_pemda=a.kd_provinsi and 
        idbas=b.idbas group by kd_rek1,kd_rek2,kd_rek3),sum(a.realisasi)) as nilai_eliminasi,
a.dk,b.idbas from ikon.raw_data a 
inner join ikon.header_data b on a.header_data_id=b.id 
inner join ikon.ref_jlaporan d on b.id_jnslaporan=d.id 
where d.id=2
group by a.tahun,a.kd_provinsi,a.periode_id,a.kd_rek1,a.kd_rek2,a.kd_rek3
,a.dk,b.idbas order by a.kd_provinsi asc; 
	       


CREATE OR REPLACE VIEW ikon.vw_lo AS select a.tahun,a.kd_provinsi,a.periode_id,a.kd_rek1,a.kd_rek2,a.kd_rek3,
(select nama as namaakun from  ikon.ref_akun  
        where kd_level1=a.kd_rek1 and kd_level2=0 
and kd_level3=0 and kd_level4='0' and kd_level5='0' and
        idbas=b.idbas) as namaakun1,
( select nama as namaakun from  ikon.ref_akun  
        where kd_level1=a.kd_rek1 and kd_level2=a.kd_rek2 
        and kd_level3=0 and kd_level4='0' and kd_level5='0' and
        idbas=b.idbas) as namaakun2,
(select nama as namaakun from  ikon.ref_akun  
        where kd_level1=a.kd_rek1 and kd_level2=a.kd_rek2 
and kd_level3=a.kd_rek3 and kd_level4='0' and kd_level5='0' and
        idbas=b.idbas) as namakun3,
sum(a.realisasi) as realisasi,coalesce(sum(a.realisasi)-
(select sum(nilai_eliminasi) from  ikon.akun_eliminasi  
        where kd_rek1=a.kd_rek1 and kd_rek2=a.kd_rek2 
and kd_rek3=a.kd_rek3 and kd_pemda=a.kd_provinsi and 
        idbas=b.idbas group by kd_rek1,kd_rek2,kd_rek3),sum(a.realisasi)) as nilai_eliminasi,
a.dk,b.idbas from ikon.raw_data a 
inner join ikon.header_data b on a.header_data_id=b.id 
inner join ikon.ref_jlaporan d on b.id_jnslaporan=d.id 
where d.id=3
group by a.tahun,a.kd_provinsi,a.periode_id,a.kd_rek1,a.kd_rek2,a.kd_rek3
,a.dk,b.idbas order by a.kd_provinsi asc; 


CREATE OR REPLACE VIEW ikon.vw_neraca_trialbalance AS select a.tahun,a.kd_provinsi,a.periode_id,a.kd_rek1,a.kd_rek2,a.kd_rek3,
(select nama as namaakun from  ikon.ref_akun  
        where kd_level1=a.kd_rek1 and kd_level2=0 
and kd_level3=0 and kd_level4='0' and kd_level5='0' and
        idbas=b.idbas) as namaakun1,
( select nama as namaakun from  ikon.ref_akun  
        where kd_level1=a.kd_rek1 and kd_level2=a.kd_rek2 
        and kd_level3=0 and kd_level4='0' and kd_level5='0' and
        idbas=b.idbas) as namaakun2,
(select nama as namaakun from  ikon.ref_akun  
        where kd_level1=a.kd_rek1 and kd_level2=a.kd_rek2 
and kd_level3=a.kd_rek3 and kd_level4='0' and kd_level5='0' and
        idbas=b.idbas) as namakun3,
sum(a.realisasi) as realisasi,coalesce(sum(a.realisasi)-
(select sum(nilai_eliminasi) from  ikon.akun_eliminasi  
        where kd_rek1=a.kd_rek1 and kd_rek2=a.kd_rek2 
and kd_rek3=a.kd_rek3 and kd_pemda=a.kd_provinsi and 
        idbas=b.idbas group by kd_rek1,kd_rek2,kd_rek3),sum(a.realisasi)) as nilai_eliminasi,
a.dk,b.idbas from ikon.raw_data a 
inner join ikon.header_data b on a.header_data_id=b.id 
inner join ikon.ref_jlaporan d on b.id_jnslaporan=d.id 
where d.id=7
group by a.tahun,a.kd_provinsi,a.periode_id,a.kd_rek1,a.kd_rek2,a.kd_rek3
,a.dk,b.idbas order by a.kd_provinsi asc; 
