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



insert into ikon.ref_lingkup_data (id,nama) values 
(4,'-');


ALTER TABLE ikon.akun_eliminasi 
ALTER COLUMN flag TYPE VARCHAR(10);


CREATE VIEW ikon.vw_lak AS select tahun,kd_provinsi,periode_id,kd_rek1,kd_rek2,kd_rek3,nm_rek1,nm_rek2,nm_rek3,nilai,dk from ikon.raw_data_lak;


CREATE VIEW ikon.vw_lpe AS select tahun,kd_provinsi,periode_id,ekuitas_awal,surplus_defisit,koreksi_persediaan,selisih_revaluasi_asset,lain_lain,
((ekuitas_awal+surplus_defisit)-(koreksi_persediaan)+(selisih_revaluasi_asset)+(lain_lain)) as ekuitas_akhir from ikon.raw_data_lpe;


CREATE VIEW ikon.vw_lpsal AS select tahun,kd_provinsi,periode_id,saldo_awal,penggunaan_sal,silpa,koreksi,lainnya, 
((saldo_awal-penggunaan_sal)+(silpa+koreksi+lainnya)) as saldo_akhir from ikon.raw_data_lpsal;


CREATE 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),0) 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 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),0) 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 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),0) 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 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),0) 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; 
