set trimspool on
--set serveroutput on size 100000;
set termout oFF;
set verify off;
set pagesize 0;
set heading off;
set feedback off;
set linesize700;
alter session set nls_date_format='dd-mm-yyyy';
------------------------------------------------------------------------
-- Tabel 1. Rekapitulasi Jumlah Menurut desa Kelurahan dan Jenis Kelamin
------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0101 NOLOGGING TABLESPACE SIAKDATA AS
select
   trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
     case when 
     getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
     getnamakab(no_kab,no_prop) else 
     getnamakab(no_kab,no_prop) end else 'KEC. '||
     getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum(lk) lk
    ,to_char(round(sum(lk)*100/(pd),2),'00.00')plk
    ,sum(pr)pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00')ppr    
    ,sum(jmljk) jmljk
    ,to_char(round(sum(jmljk)*100/(pd),2),'000.00')pjk
    from
    (    
SELECT 
    no_kec,no_kab,no_prop,no_kel
    ,case when jenis_klmin in(1) then 1 end LK
    ,case when jenis_klmin in(2) then 1 end PR
    ,(select count(*) from biodata_wni where flag_status='0') pd
    ,case when jenis_klmin in(1,2) then 1 end jmljk           
FROM biodata_wni WHERE flag_status='0'
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop,pd)
,(no_kec,no_kab,no_prop,pd)
,(no_kab,no_prop,pd)
)
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;

--------------------------------------------------------------------
-- Tabel 2. Penduduk Menurut Kecamatan dan Jenis Kelamin
--------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0102 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end NMKEC
    ,sum(lk) lk
    ,to_char(round(sum(lk)*100/(pd),2),'00.00')plk
    ,sum(pr)pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00')ppr    
    ,sum(jmljk) jmljk
    ,to_char(round(sum(jmljk)*100/(pd),2),'000.00')pjk
    from
    (    
SELECT 
    no_kec,no_kab,no_prop
    ,case when jenis_klmin in(1) then 1 end LK
    ,case when jenis_klmin in(2) then 1 end PR
    ,(select count(*) from biodata_wni where flag_status='0') pd
    ,case when jenis_klmin in(1,2) then 1 end jmljk           
FROM biodata_wni WHERE flag_status='0'
)
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd)
)
ORDER BY no_prop,no_kab,NO_KEC;

set termout off;
spool off;
--------------------------------------------------------------------
---Tabel 3. Angka Tertumbuhan Rasio Penduduk Berdasarkan Kecamatan
--------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0301 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
     case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum(lk)lk
    ,to_char(round(sum(lk)*100/(pd),2),'00.00')plk
    ,sum(pr)pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00')ppr
    ,sum(jmljk)jmljk
    ,to_char(round(sum(jmljk)*100/(pd),2),'000.00')pjk
    ,sum(tback) tback
    ,to_char(round(sum(tback)*100/(pd1),2),'000.00')ptback
    from
    (
    select
    a.no_kec,a.no_kab,a.no_prop,a.no_kel
    ,case when jenis_klmin in(1) then 1 end LK
    ,case when jenis_klmin in(2) then 1 end PR
    ,case when jenis_klmin in(1,2) then 1 end JMLJK
    ,(select count(*) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0') pd       
    ,case when (tgl_lhr<trunc(tgl_akhir, 'YYYY')-interval '1' year) then 1 end tback
    ,(select count(*) from biodata_wni a,tgl_aktif b where flag_status='0' and 
    tgl_lhr<trunc(tgl_akhir, 'YYYY')-interval '1' year) pd1
from biodata_wni a,tgl_aktif b where a.flag_status='0'
)
group by 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop,pd,pd1),(no_kec,no_kab,no_prop,pd,pd1),(no_kab,no_prop,pd,pd1))
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;
--------------------------------------------------------------------
---Tabel 4. Proporsi Penduduk Menurut Kelompok Umur dan Jenis Kelamin
--------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0302 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum(lk)lk
    ,to_char(round(sum(lk)*100/(pd),2),'00.00')plk
    ,sum(pr)pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00')ppr
    ,sum(jmljk)jmljk
    ,to_char(round(sum(jmljk)*100/(pd),2),'000.00')pjk
    ,sum(tback) tback
    ,to_char(round(sum(tback)*100/(pd1),2),'000.00')ptback
    from
    (
    select
    a.no_kec,a.no_kab,a.no_prop
    ,case when jenis_klmin in(1) then 1 end LK
    ,case when jenis_klmin in(2) then 1 end PR
    ,case when jenis_klmin in(1,2) then 1 end JMLJK
    ,(select count(*) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0') pd       
    ,case when (tgl_lhr<trunc(tgl_akhir, 'YYYY')-interval '1' year) then 1 end tback
    ,(select count(*) from biodata_wni,tgl_aktif b where a.flag_status='0' and 
    tgl_lhr<trunc(tgl_akhir, 'YYYY')-interval '1' year) pd1
from biodata_wni a,tgl_aktif b where a.flag_status='0'
)
group by 
GROUPING SETS((no_kec,no_kab,no_prop,pd,pd1),(no_kab,no_prop,pd,pd1)
)
order by no_kec;

set termout off;
spool off;
----------------------------------------------------------------------------------------------------
--Tabel 5. Proporsi Penduduk Menurut Kelompok Umur, Jenis Kelamin dan Rasio Jenis Kelamin (Sex Rasio)
----------------------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0401 NOLOGGING TABLESPACE SIAKDATA AS
SELECT  
    row_number() over(partition by no_kab order by no_kab) as norut
    ,umur,sum(lk)lk
    ,to_char(round(sum(lk)*100/(pd),2),'00.00')plk
    ,sum(pr)pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00')ppr    
    ,sum(jml)jml
    ,to_char(round(sum(jml)*100/(pd),2),'000.00')pjk
    from
    (
    select
    no_kab,no_prop
     ,decode (floor (months_between (tgl_akhir,tgl_lhr) / 60)
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29'
    ,6, '30-34',7, '35-39',8, '40-44',9, '45-49',10, '50-54',11, '55-59'
    ,12, '60-64',13, '65-69',14, '70-74','>=75') as UMUR
    ,case when jenis_klmin in(1) then 1 end LK
    ,case when jenis_klmin in(2) then 1 end PR
    ,case when jenis_klmin in(1,2) then 1 end JML
    ,(select count(*) from biodata_wni where flag_status='0')pd
    ,to_char(tgl_lhr,'dd-mm-yyyy') tgl_lhr
    ,to_char(tgl_akhir,'dd-mm-yyyy') tgl_akhir
    FROM biodata_wni a,tgl_aktif b
WHERE tgl_lhr < sysdate and flag_status='0'
)
GROUP BY
grouping sets((no_kab,no_prop,umur,pd),(no_kab,no_prop,pd)
    )
ORDER BY no_kab,norut;

set termout off;
spool off;
---------------------------------------------------------------------
--Tabel 6a. Penduduk Menurut Kecamatan, Jenis Kelamin dan Umur Median
---------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0402 NOLOGGING TABLESPACE SIAKDATA AS
SELECT  
    row_number() over(partition by no_kab order by no_kab) as norut
    ,umur
    ,sum(lk)lk
    ,to_char(round(sum(lk)*100/(pd),2),'00.00')plk
    ,sum(pr)pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00')ppr    
    ,sum(jml)jml
    ,to_char(round(sum(jml)*100/(pd),2),'000.00')pjk    
    from
    (
    select
    no_kab,no_prop
    ,decode (floor (months_between (tgl_akhir,tgl_lhr) / 12)
    ,0, '00-01',1,'01-02',2,'02-03',3,'03-04',4,'04-05',5,'05-06',6,'06-07'
    ,7,'07-08',8,'08-09',9,'09-10',10,'10-11',11,'11-12',12,'12-13'
    ,13,'13-14',14,'14-15',15,'15-16',16,'16-17',17,'17-18',18,'18-19'
    ,19,'19-20',20,'20-21',21,'21-22',22,'22-23',23,'23-24',24,'24-25'
    ,25,'25-26',26,'26-27',27,'27-28',28,'28-29',29,'29-30',30,'30-31'
    ,31,'31-32',32,'32-33',33,'33-34',34,'34-35',35,'35-36',36,'36-37'
    ,37,'37-38',38,'38-39',39,'39-40',40,'40-41',41,'41-42',42,'42-43'
    ,43,'43-44',44,'44-45',45,'45-46',46,'46-47',47,'47-48',48,'48-49'
    ,49,'49-50',50,'50-51',51,'51-52',52,'52-53',53,'53-54',54,'54-55'
    ,55,'55-56',56,'56-57',57,'57-58',58,'58-59',59,'59-60',60,'60-61'
    ,61,'61-62',62,'62-63',63,'63-64',64,'64-65',65,'65-66',66,'66-67'
    ,67,'67-68',68,'68-69',69,'69-70',70,'70-71',71,'71-72',72,'72-73'
    ,73,'73-74',74,'74-75',75,'75-76',76,'76-77',77,'77-78',78,'78-79'
    ,79,'79-80',80,'80-81',81,'81-82',82,'82-83',83,'83-84',84,'84-85'
    ,85,'85-86',86,'86-87',87,'87-88',88,'88-89',89,'89-90',90,'90-91'
    ,91,'91-92',92,'92-93',93,'93-94',94,'94-95',95,'95-96',96,'96-97'
    ,97,'97-98',98,'98-99',99,'99-100','> 100') as UMUR
    ,case when jenis_klmin in(1) then 1 end LK
    ,case when jenis_klmin in(2) then 1 end PR
     ,case when jenis_klmin in(1,2) then 1 end JML
     ,(select count(*) from biodata_wni where flag_status='0') pd
     ,to_char(tgl_lhr,'dd-mm-yyyy') tgl_lhr
     ,to_char(tgl_akhir,'dd-mm-yyyy') tgl_akhir
FROM biodata_wni a,tgl_aktif b
WHERE tgl_lhr < b.tgl_akhir and flag_status='0'
)
GROUP BY
grouping sets(( 
    no_kab,no_prop,umur,pd)
    ,(no_kab,no_prop,pd)
    )
ORDER BY no_kab,norut;

set termout off;
spool off;
---------------------------------------------------------------------------
--Tabel 6. Penduduk Menurut Kecamatan, Jenis Kelamin dan Rasio Jenis Kelamin
---------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0501 NOLOGGING TABLESPACE SIAKDATA AS
select
    row_number() over(partition by no_kab order by no_kab) as norut
    ,umur
    ,sum(lk)lk
    ,to_char(round(sum(lk)*100/(pd),2),'00.00')plk
    ,sum(pr)pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00')ppr
    ,sum(jmljk)jmljk
    ,to_char(round((sum(lk)/sum(pr)*100),2),'000.00')rasio    
from
(
select
    no_kab
    ,decode (floor (months_between (b.tgl_akhir,tgl_lhr) / 60)
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29'
    ,6, '30-34',7, '35-39',8, '40-44',9, '45-49',10, '50-54',11, '55-59'
    ,12, '60-64',13, '65-69',14, '70-74','>=75') as umur
   , case when jenis_klmin in(1) then 1 end LK
    ,case when jenis_klmin in(2) then 1 end PR
    ,case when jenis_klmin in(1,2) then 1 end JMLJK
    ,(select count(*) from biodata_wni where flag_status='0') pd
    ,floor(months_between(sysdate,tgl_lhr)/12) age
FROM biodata_wni a,tgl_aktif b
WHERE tgl_lhr<b.tgl_akhir and flag_status='0'
)
group by 
grouping sets((umur,no_kab,pd)
,(no_kab,pd)
)
order by umur;

set termout off;
spool off;
--------------------------------------------------------------------------------
--Tabel 7a. Penduduk Menurut Kecamatan, Muda (0-14), Produktif(15-64) dan Tua(>=65)
--------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0601 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum(lk)lk
    ,to_char(round(sum(lk)*100/(pd),2),'00.00')plk
    ,sum(pr)pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00')ppr
    ,sum(jml)jml
    ,to_char(round(sum(jml)*100/(pd),2),'000.00')pjk
    ,to_char(round(sum(lk)/sum(pr)*100,2),'000.00')rasio
    from
    (    
    select
    no_kec,no_kab,no_prop,no_kel
    ,case when jenis_klmin in(1) then 1 end LK
    ,case when jenis_klmin in(2) then 1 end PR
    ,case when jenis_klmin in(1,2) then 1 end JML
    ,(select count(*) from biodata_wni where flag_status='0') pd
FROM biodata_wni where flag_status='0'
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop,pd),(no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd)
)
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;
--------------------------------------------------------------------------
--Tabel 7. Penduduk Menurut Kecamatan Persentase Rasio Usia Produktif(15-64) 
--------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0602 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum(lk)lk
    ,to_char(round(sum(lk)*100/(pd),2),'00.00')plk
    ,sum(pr)pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00')ppr
    ,sum(jml)jml
    ,to_char(round(sum(jml)*100/(pd),2),'000.00')pjk
    ,to_char(round(sum(lk)/sum(pr)*100,2),'000.00')rasio
    from
    (    
    select
    no_kec,no_kab,no_prop
    ,case when jenis_klmin in(1) then 1 end LK
    ,case when jenis_klmin in(2) then 1 end PR
    ,case when jenis_klmin in(1,2) then 1 end JML
    ,(select count(*) from biodata_wni where flag_status='0') pd
FROM biodata_wni where flag_status='0'
)
GROUP BY 
GROUPING SETS((no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd)
)
ORDER BY NO_KAB,NO_KEC;

set termout off;
spool off;
--------------------------------------------------------------------------
--GARPHICS 7b. Penduduk Menurut Kecamatan Persentase Rasio Usia Produktif(15-64) 
--------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0603 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    row_number() over(partition by no_kab order by no_kab) as norut
    ,trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||
     trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum(lk)lk
    ,to_char(round(sum(lk)*100/(pd),2),'00.00')plk
    ,sum(pr)pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00')ppr
    ,sum(jml)jml
    ,to_char(round(sum(jml)*100/(pd),2),'000.00')pjk
    ,median(umur) umedian    
    from
    (
    select
    no_Prop,no_kab,no_kec,no_kel    
    ,case when jenis_klmin in(1) then 1 else 0 end LK
    ,case when jenis_klmin in(2) then 1 else 0 end PR
    ,case when jenis_klmin in(1,2) then 1 else 0 end JML
    ,(select count(*) from biodata_wni where flag_status='0') pd
    ,floor(months_between(b.tgl_akhir,tgl_lhr)/12) umur 
FROM biodata_wni a,tgl_aktif b where flag_status='0'
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop,pd),(no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd)
)
ORDER BY no_kab,NO_KEC,no_kel;

set termout off;
spool off;
------------------------------------------------
--Tabel 8a. Rasio Ketergantungan Menurut Kecamatan
------------------------------------------------
CREATE TABLE &1..xkuantitas0604 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    row_number() over(partition by no_kab order by no_kab) as norut
    ,trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum(lk)lk
    ,to_char(round(sum(lk)*100/(pd),2),'00.00')plk
    ,sum(pr)pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00')ppr
    ,sum(jml)jml
    ,to_char(round(sum(jml)*100/(pd),2),'000.00')pjk
    ,median(umur) umedian    
    from
    (
    select
    no_kec,no_kab,no_prop    
    ,case when jenis_klmin in(1) then 1 end LK
    ,case when jenis_klmin in(2) then 1 end PR
    ,case when jenis_klmin in(1,2) then 1 end JML
    ,(select count(*) from biodata_wni where flag_status='0') pd
    ,floor(months_between(b.tgl_akhir,tgl_lhr)/12) umur 
FROM biodata_wni a,tgl_aktif b where flag_status='0'
)
GROUP BY 
GROUPING SETS((no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd)
)
ORDER BY NO_KAB,NO_KEC;

set termout off;
spool off;
----------------------------------------------------
--Tabel 8b. Rasio Ketergantungan Menurut Jenis Kelamin
----------------------------------------------------
CREATE TABLE &1..xkuantitas0701 NOLOGGING TABLESPACE SIAKDATA AS
select
        row_number() over(partition by no_kab order by no_kab)||'.' as URUT
       ,case when u123 is null then 'JUMLAH' else substr(u123,4,100) end umur 
       ,sum(lk) lk
       ,sum(pr) pr
       ,sum(jml) jml
       ,to_char(round(sum(jml)*100/(pd),2),'000.00') PJK 
       from
       (
select
     no_kab
     ,case when floor(months_between(tgl_akhir,tgl_lhr)/12) between 0 and 14 then '1. 00-14 Tahun (Usia Muda/Non Produktif)'  
     when floor(months_between(tgl_akhir,tgl_lhr)/12) between 15 and 64 then '2. 15-64 Tahun (Usia Produktif)'
     else '3. >=65 Tahun  (Usia Tua/Non Produktif)'  end u123
     ,case when jenis_klmin in(1) then 1 end LK
     ,case when jenis_klmin in(2) then 1 end PR
     ,case when jenis_klmin in(1,2) then 1 end JML
     ,(select count(*) from biodata_wni  where flag_status='0') pd
from biodata_wni a, tgl_aktif b where 1=1 and flag_status='0'
)
group by 
grouping sets((no_kab,u123,pd),(no_kab,pd))
order by umur;

set termout off;
spool off;
------------------------------------------------------------------
--Tabel 8. Rasio Ketergantungan Menurut Kecamatan dan Jenis Kelamin
------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0702 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
     case when 
     getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
     getnamakab(no_kab,no_prop) else 
     getnamakab(no_kab,no_prop) end else 'KEC. '||
     getnamakec(no_kec,no_kab,no_prop) end end NMKEC
     ,sum(case when lk=1 and bk1=1 then 1 end) lkbk1     
     ,sum(case when pr=1 and bk1=1 then 1 end) prbk1
     ,sum(case when bk1=1 then 1 end) jmlbk1     
     ,sum(case when lk=1 and bk2=1 then 1 end) lkbk2     
     ,sum(case when pr=1 and bk2=1 then 1 end) prbk2
     ,sum(case when bk2=1 then 1 end) jmlbk2     
     ,to_char(round(sum(case when bk1=1 then 1 end)*100/(sum (case when bk2=1 then 1 else 0 end)),2),'00.00') psnbk    
     from
     (
     select 
     no_prop,no_kab,no_kec,no_kel
     ,case when jenis_klmin in(1) then 1 end lk
     ,case when jenis_klmin in(2) then 1 end pr
     ,case when jenis_pkrjn in(1) then 1 else 0 end bk1
     ,case when jenis_pkrjn>=2 then 1 else 0 end bk2 
FROM biodata_wni a,tgl_aktif b where 1=1 
and flag_status='0'
and floor(months_between (b.tgl_akhir,tgl_lhr)/12) between 15 and 64
)
GROUP BY 
GROUPING SETS ((no_prop,no_kab,no_kec,no_kel),(no_kab,no_prop,no_kec),(no_kab,no_prop)
)
ORDER BY no_kab,no_kec,no_kel;

set termout off;
spool off;
---------------------------------------------------------------------------------------
--Tabel 9a. Penduduk Menurut Kecamatan, Pendidikan ditammatkan berdasarkan Jenis Kelamin
---------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0703 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
     ,case when 
     getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
     getnamakab(no_kab,no_prop) else 
     getnamakab(no_kab,no_prop) end else 
     getnamakec(no_kec,no_kab,no_prop) end nmkec
     ,sum(case when lk=1 and bk1=1 then 1 end) lkbk1     
     ,sum(case when pr=1 and bk1=1 then 1 end) prbk1
     ,sum(case when bk1=1 then 1 end) jmlbk1     
     ,sum(case when lk=1 and bk2=1 then 1 end) lkbk2     
     ,sum(case when pr=1 and bk2=1 then 1 end) prbk2
     ,sum(case when bk2=1 then 1 end) jmlbk2     
     ,to_char(round(sum(case when bk1=1 then 1 end)*100/(sum (case when bk2=1 then 1 else 0 end)),2),'00.00') psnbk    
     from
     (
     select 
     no_prop,no_kab,no_kec
     ,case when jenis_klmin in(1) then 1 end lk
     ,case when jenis_klmin in(2) then 1 end pr
     ,case when jenis_pkrjn in(1) then 1 else 0 end bk1
     ,case when jenis_pkrjn>=2 then 1 else 0 end bk2 
FROM biodata_wni a,tgl_aktif b where 1=1 
and flag_status='0'
and floor(months_between (b.tgl_akhir,tgl_lhr)/12) between 15 and 64
)
GROUP BY 
GROUPING SETS ((no_prop,no_kab,no_kec),(no_kab,no_prop)
)
ORDER BY no_kab,no_kec;

set termout off;
spool off;
---------------------------------------------------------------------------------
Tabel 9b. Penduduk Usia Putus Sekolah Menurut Kecamatan Berdasarkan Jenis Kelamin
---------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0704 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
     case when 
     getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
     getnamakab(no_kab,no_prop) else 
     getnamakab(no_kab,no_prop) end else 'KEC. '||
     getnamakec(no_kec,no_kab,no_prop) end end NMKEC
     ,sum(case when umur between 15 and 64 and lk=1 then 1 else 0 end) ld1564
     ,sum(case when umur between 15 and 64 and pr=1 then 1 else 0 end) pd1564
     ,sum(case when umur between 15 and 64 then 1 else 0 end) jjk1564
     ,to_char(round(sum (case when umur between 15 and 64 then 1 end)*100/(pd),2),'00.00')plpd1564
     ,to_char(round((sum (case when umur >=65 then 1 else 0 end) + sum (case when umur between 0 and 14 then 1 else 0 end))/
     sum (case when umur between 15 and 64 then 1 else 0 end)*100,2),'00.00') rasio
     from
     (
     select 
     no_prop,no_kab,no_kec,no_kel
     ,case when jenis_klmin in(1) then 1 end lk
     ,case when jenis_klmin in(2) then 1 end pr
     ,(select count(nik) from biodata_wni where flag_status=0) pd
     ,floor(months_between (b.tgl_akhir,tgl_lhr)/12) umur
FROM biodata_wni a,tgl_aktif b where flag_status='0'
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop,pd),(no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd)
)
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;
--------------------------------------------------
-- Tabel 10. Penduduk menurut Kecamatan dan Agama
--------------------------------------------------
CREATE TABLE &1..xkuantitas0705 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
     ,case when 
     getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
     getnamakab(no_kab,no_prop) else 
     getnamakab(no_kab,no_prop) end else 
     getnamakec(no_kec,no_kab,no_prop) end nmkec
     ,sum(case when umur between 15 and 64 and lk=1 then 1 end) ld1564
     ,sum(case when umur between 15 and 64 and pr=1 then 1 end) pd1564
     ,sum(case when umur between 15 and 64 then 1 end) jjk1564
     ,to_char(round(sum (case when umur between 15 and 64 then 1 end)*100/(pd),2),'00.00')plpd1564
     ,to_char(round((sum (case when umur >=65 then 1 end) + sum (case when umur between 0 and 14 then 1 end))/
     sum (case when umur between 15 and 64 then 1 end)*100,2),'00.00') rasio
     from
     (
     select 
     no_prop,no_kab,no_kec
     ,case when jenis_klmin in(1) then 1 end lk
     ,case when jenis_klmin in(2) then 1 end pr
     ,(select count(nik) from biodata_wni where flag_status=0) pd
     ,floor(months_between (b.tgl_akhir,tgl_lhr)/12) umur
FROM biodata_wni a,tgl_aktif b where flag_status='0'
)
GROUP BY 
GROUPING SETS ((no_prop,no_kab,no_kec,pd),(no_kab,no_prop,pd)
)
ORDER BY no_kab,no_kec;

set termout off;
spool off;
--------------------------------------------------
Tabel 10. Penduduk Menurut Agama dan Jenis Kelamin
--------------------------------------------------
CREATE TABLE &1..xkuantitas0801 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
   ,to_char(round(sum(u0014)/sum(u1564)*100,2),'00.00') mjk
   ,to_char(round(sum(uup65)/sum(u1564)*100,2),'00.00') tjk
   ,to_char(round((sum(u0014)+sum(uup65))/sum(u1564)*100,2),'00.00') pj1465
   from
   (   
   select
   no_prop,no_kab,no_kec,no_kel
   ,case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) between 0 and 14 then 1 end u0014
   ,case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) between 15 and 64 then 1 end u1564
   ,case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) >=65 then 1 end uup65       
FROM biodata_wni a,tgl_aktif b where flag_status='0'
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop),(no_kec,no_kab,no_prop),(no_kab,no_prop))
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;
---------------------------------------------------------------------------
--Tabel 11. Penduduk Menurut Kecamatan, Status Perkawinan dan Jenis Kelamin
---------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0802 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end NMKEC
   ,to_char(round(sum(u0014)/sum(u1564)*100,2),'00.00') mjk
   ,to_char(round(sum(uup65)/sum(u1564)*100,2),'00.00') tjk
   ,to_char(round((sum(u0014)+sum(uup65))/sum(u1564)*100,2),'00.00') pj1465
   from
   (   
   select
   no_prop,no_kab,no_kec
   ,case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) between 0 and 14 then 1 end u0014
   ,case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) between 15 and 64 then 1 end u1564
   ,case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) >=65 then 1 end uup65       
FROM biodata_wni a,tgl_aktif b where flag_status='0'
)
GROUP BY 
GROUPING SETS ((no_prop,no_kab,no_kec),(no_prop,no_kab)
)
ORDER BY no_kab,no_kec;

set termout off;
spool off;
---------------------------------------------------------------------------
--Tabel 11a. Proporsi Penduduk Menurut Kelompok Umur dan Status Perkawinan
---------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0803 NOLOGGING TABLESPACE SIAKDATA AS
select
       row_number() over(partition by no_kab order by no_kab)||'.' as urut
       ,case when nmjk is null then 'PENDUDUK' else nmjk end nmjk
       ,to_char(round(sum(u0014)/sum(u1564)*100,2),'00.00') mjk
       ,to_char(round(sum (uup65)/sum(u1564)*100,2),'00.00') tjk
       ,to_char(round((sum(u0014)+sum(uup65))/sum(u1564)*100,2),'00.00') PJ1465 
        from
        (
        select
       no_kab
       ,decode(jenis_klmin,1,'LAKI-LAKI','PEREMPUAN') nmjk
       ,case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) between 0 and 14 then 1 end u0014
       ,case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) between 15 and 64 then 1 else 0 end u1564
       ,case when floor (months_between (b.tgl_akhir,tgl_lhr)/12)>=65  then 1 else 0 end uup65       
FROM biodata_wni a, tgl_aktif b where flag_status='0'
)
GROUP BY 
GROUPING SETS ((nmjk,no_kab)
,(no_kab)
)
ORDER BY urut,nmjk;

set termout off;
spool off;
------------------------------------------------------------------------------------------------
--Tabel 12 b1. Proporsi Penduduk Menurut Kelompok Umur, Pendidikan Ditammatkan dan Jenis Kelamin
------------------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0804 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
 trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||getnamakec(no_kec,no_kab,no_prop) end end NMKEC
   ,sum(lm14)lm14
   ,sum(pm14)pm14
   ,to_char(round(sum(u0014)/sum(u1564)*100,2),'00.00')mpr
   ,sum(lt65)lt65
   ,sum(pt65)pt65
   ,to_char(round(sum(uup65)/sum(u1564)*100,2),'00.00')tpr  
   ,sum(u0014)+sum(uup65) j1465
   ,to_char(round((sum(u0014)+sum(uup65))/sum(u1564)*100,2),'00.00') pj1465
   from
   (
    select
    no_prop,no_kab,no_kec,no_kel
    ,case when umur between 0 and 14 and lk=1 then 1 end lm14
    ,case when umur between 0 and 14 and pr=1 then 1 end pm14
    ,case when umur between 0 and 14 then 1 end u0014    
    ,case when umur between 15 and 64 then 1 end u1564       
    ,case when umur  >=65 and lk=1 then 1 end lt65
    ,case when umur >=65 and pr=1 then 1 end pt65
    ,case when umur >=65 then 1 end uup65
    from
    (
    select
    no_prop,no_kab,no_kec,no_kel
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,floor(months_between(b.tgl_akhir,tgl_lhr)/12) umur
 FROM biodata_wni a, tgl_aktif b where flag_status='0'
)
)
GROUP BY
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop),(no_kec,no_kab,no_prop),(no_kab,no_prop))
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;
------------------------------------------------------------------------------------------------
--Tabel 12 b2. Proporsi Penduduk Menurut Kelompok Umur, Pendidikan Ditammatkan dan Jenis Kelamin
------------------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0805 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end NMKEC
   ,sum(lm14)lm14
   ,sum(pm14)pm14
   ,to_char(round(sum(u0014)/sum(u1564)*100,2),'00.00')mpr
   ,sum(lt65)lt65
   ,sum(pt65)pt65
   ,to_char(round(sum(uup65)/sum(u1564)*100,2),'00.00')tpr  
   ,sum(u0014)+sum(uup65) j1465
   ,to_char(round((sum(u0014)+sum(uup65))/sum(u1564)*100,2),'00.00') pj1465
   from
   (
    select
    no_prop,no_kab,no_kec
    ,case when umur between 0 and 14 and lk=1 then 1 end lm14
    ,case when umur between 0 and 14 and pr=1 then 1 end pm14
    ,case when umur between 0 and 14 then 1 end u0014    
    ,case when umur between 15 and 64 then 1 end u1564       
    ,case when umur  >=65 and lk=1 then 1 end lt65
    ,case when umur >=65 and pr=1 then 1 end pt65
    ,case when umur >=65 then 1 end uup65
    from
    (
    select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,floor(months_between(b.tgl_akhir,tgl_lhr)/12) umur
 FROM biodata_wni a, tgl_aktif b where flag_status='0'
)
)
GROUP BY 
GROUPING SETS ((no_prop,no_kab,no_kec),(no_prop,no_kab)
)
ORDER BY no_kab,no_kec;

set termout off;
spool off;
------------------------------------------------------------------------------------------------
--Tabel 12 b3. Proporsi Penduduk Menurut Kelompok Umur, Pendidikan Ditammatkan dan Jenis Kelamin
------------------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0901 NOLOGGING TABLESPACE SIAKDATA AS
select 
    npd,case when nmpd is null then 'JUMLAH' else nmpd end nmpd
    ,sum(lk) LK,to_char(round(sum(lk)*100/(plk),2),'00.00')PLK
    ,sum(pr) PR,to_char(round(sum(pr)*100/(plk),2),'00.00')PPR
    ,sum(jduk) JDUK,to_char(round(sum(jduk)*100/(plk),2),'00.00') PDUK
from
(
SELECT
    no_prop,no_kab
    ,to_char(a.pddk_akh,'00')||'.' NPD
    ,b.descrip as NMPD
    ,case when jenis_klmin in(1) then 1 end LK
    ,case when jenis_klmin in(2) then 1 end PR
    ,(select count(*) from biodata_wni)PLK
    ,case when jenis_klmin in(1,2) then 1 end JDUK
FROM biodata_wni a, ref_siak_wni b,tgl_aktif c 
where a.pddk_akh=b.no and a.flag_status='0' and b.sect='101'
and floor(months_between(c.tgl_akhir,tgl_lhr)/12)>=7
)
GROUP BY 
grouping sets((no_kab,npd,nmpd,plk)
,(plk)
)
ORDER BY npd;

set termout off;
spool off;
------------------------------------------------------------------------------------------------
--Tabel 12 b4. Proporsi Penduduk Menurut Kelompok Umur, Pendidikan Ditammatkan dan Jenis Kelamin
------------------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0902 NOLOGGING TABLESPACE SIAKDATA AS
select 
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end NMKEC
,sum(lk) LK,to_char(round(sum(lk)*100/(plk),2),'00.00')plk
,sum(pr) PR,to_char(round(sum(PR)*100/(plk),2),'00.00')ppr
,sum(jduk) JDUK,to_char(round(sum(jduk)*100/(plk),2),'00.00')pduk
from
(
SELECT
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 end LK
    ,case when jenis_klmin in(2) then 1 end PR
    ,case when jenis_klmin in(1,2) then 1 end JDUK
    ,(select count(*) from biodata_wni where pddk_akh in(1))PLK
FROM biodata_wni a,tgl_aktif b where flag_status='0'
and floor(months_between(b.tgl_akhir,tgl_lhr)/12) between 7 and 16 and pddk_akh in(1)
)
GROUP BY 
grouping sets((no_kec,no_prop,no_kab,plk),(no_prop,no_kab,plk)
)
ORDER BY no_kec;

set termout off;
spool off;
--------------------------------------------------------------------------------
--Tabel 12. Penduduk Menurut Kecamatan, Pendidikan ditammatkan dan Jenis Kelamin
--------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0903 NOLOGGING TABLESPACE SIAKDATA AS
select 
trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
,case when 
getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
getnamakab(no_kab,no_prop) else 
getnamakab(no_kab,no_prop) end else 
getnamakec(no_kec,no_kab,no_prop) end NMKEC
,sum (case when lk in(1) and umur between 4 and 6 then 1 else 0 end) LU1 
,sum (case when pr in(1) and umur between 4 and 6 then 1 else 0 end) PU1
,sum (case when lk in(1) and umur between 7 and 12 then 1 else 0 end) LU2 
,sum (case when pr in(1) and umur between 7 and 12 then 1 else 0 end) PU2
,sum (case when lk in(1) and umur between 13 and 15 then 1 else 0 end) LU3 
,sum (case when pr in(1) and umur between 13 and 15 then 1 else 0 end) PU3
,sum (case when lk in(1) and umur between 16 and 18 then 1 else 0 end) LU4 
,sum (case when pr in(1) and umur between 16 and 18 then 1 else 0 end) PU4
,sum (jduk) JDUK
,to_char(round(sum(jduk)*100/(plk),2),'000.00') PSN
from
(
SELECT
no_prop,no_kab,no_kec
,case when jenis_klmin in(1) then 1 end LK
,case when jenis_klmin in(2) then 1 end PR
,case when jenis_klmin in(1,2) then 1 end JDUK
,floor(months_between(b.tgl_akhir,tgl_lhr)/12) umur
,(select count(*) from biodata_wni where flag_status in('0') )PLK
FROM biodata_wni a,tgl_aktif b where flag_status='0'
and floor(months_between(b.tgl_akhir,tgl_lhr)/12) between 4 and 18 
)
GROUP BY 
grouping sets((no_kec,no_prop,no_kab,plk),(no_prop,no_kab,plk)
)
ORDER BY no_kec;

set termout off;
spool off;
--------------------------------------------------------------------------
--Tabel 13. Penduduk Menurut Kecamatan, Jenis Kecacatan dan Jenis Kelamin
--------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas0904 NOLOGGING TABLESPACE SIAKDATA AS
select 
     case when npd is null then to_char('11','00.') else to_char(npd,'00.') end npd
    ,case when npd is null then 'JUMLAH' else b.descrip end nmpd
    ,sum(lk)lk
    ,to_char(round(sum(lk)*100/(pd),2),'00.00')plk
    ,sum(pr)pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00')ppr
    ,sum(jduk)jduk
    ,to_char(round(sum(jduk)*100/(pd),2),'000.00')pduk    
from
(
select
    no_prop,no_kab,pddk_akh npd    
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,case when jenis_klmin in(1,2) then 1 end jduk
    ,(select count(*) from biodata_wni where flag_status='0') pd
from biodata_wni where flag_status='0'
)x    
join ref_siak_wni b on x.npd=b.no and b.sect='101'
GROUP BY 
grouping sets((no_prop,no_kab,npd,b.descrip,pd)
,(no_prop,no_kab,pd)
)
ORDER BY no_kab,npd;

set termout off;
spool off;
-------------------------------------------------------------------------------
--Tabel 14. Penduduk, Keluarga dan Rata-Rata Anggota Keluarga Menurut Kecamatan
-------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1001 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
   ,sum (case when ag in(1) then 1 end) AG1
    ,sum(case when ag in(2) then 1 end) AG2
    ,sum(case when ag in(3) then 1 end) AG3
    ,sum(case when ag in(4) then 1 end) AG4
    ,sum(case when ag in(5) then 1 end) AG5
    ,sum(case when ag in(6) then 1 end) AG6
    ,sum(case when ag in(7) then 1 end) AG7
    ,sum(jagm) jagm,to_char(round(sum(jagm)*100/(pd),2),'000.00') pagm
    from
    (
    select
    no_prop,no_kab,no_kec,no_kel
    ,agama ag,case when agama between 1 and 7 then 1 end JAGM
    ,(select count(*) from biodata_wni where flag_status='0') pd
FROM biodata_wni WHERE flag_status='0'
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop,pd),(no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd)
)
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;
-----------------------------------------------------------------------------------------
--Tabel 15. Penduduk Kecamatan, Status Hubungan dengan Kepala Keluarga dan Jenis Kelamin
-----------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1002 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) as NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end NMKEC
   ,sum (case when ag in(1) then 1 end) AG1
    ,sum(case when ag in(2) then 1 end) AG2
    ,sum(case when ag in(3) then 1 end) AG3
    ,sum(case when ag in(4) then 1 end) AG4
    ,sum(case when ag in(5) then 1 end) AG5
    ,sum(case when ag in(6) then 1 end) AG6
    ,sum(case when ag in(7) then 1 end) AG7
    ,sum(jagm) jagm,to_char(round(sum(jagm)*100/(pd),2),'000.00') pagm
    from
    (
    select
    no_prop,no_kab,no_kec
    ,agama ag,case when agama between 1 and 7 then 1 end JAGM
    ,(select count(*) from biodata_wni where flag_status='0') pd
FROM biodata_wni WHERE flag_status='0'
)
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd)
)
ORDER BY no_prop,no_kab,NO_KEC;

set termout off;
spool off;
---------------------------------------------------------------
--Tabel 16. Kepala Keluarga Menurut Kecamatan dan Jenis Kelamin
---------------------------------------------------------------
CREATE TABLE &1..xkuantitas1003 NOLOGGING TABLESPACE SIAKDATA AS
select
    agama||'.' NAG
    ,case when agama is null then 'JUMLAH' else b.descrip end  NMAG
    ,sum (lk) LK
    ,to_char(round(sum(lk)*100/(pd),2),'00.00')PLK    
    ,sum (pr) pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00')PPR    
    ,sum (jduk) JDUK
    ,to_char(round(sum(jduk)*100/(pd),2),'000.00')PDUK
from
(
select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,case when jenis_klmin in(1,2) then 1 end jduk        
    ,agama agama
    ,(select count(*) from biodata_wni where flag_status='0') pd
  FROM biodata_wni where flag_status='0' 
)x
join ref_siak_wni b on x.agama=b.no and b.sect='501' 
GROUP BY 
grouping sets((no_prop,no_kab,agama,b.descrip,pd)
,(no_kab,pd)
)
ORDER BY agama;

set termout off;
spool off;
----------------------------------------------------------------------------------
--Tabel 17. Kepala Keluarga Menurut Kecamatan, Status Perkawinan dan Jenis Kelamin
----------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1101 NOLOGGING TABLESPACE SIAKDATA AS
select
    nikah||'.' NKWN
    ,case when nikah is null then 'JUMLAH' else b.descrip end  NMKWN
    ,sum (lk) LK
    ,to_char(round(sum(lk)*100/(pd),2),'00.00')PLK    
    ,sum (pr) pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00')PPR    
    ,sum (jduk) JDUK
    ,to_char(round(sum(jduk)*100/(pd),2),'000.00')PDUK
from
(
select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,case when jenis_klmin in(1,2) then 1 end jduk        
    ,stat_kwn nikah
    ,(select count(*) from biodata_wni where flag_status='0') pd
  FROM biodata_wni where flag_status='0' 
)x
join ref_siak_wni b on x.nikah=b.no and b.sect='601' 
GROUP BY 
grouping sets((no_prop,no_kab,nikah,b.descrip,pd)
,(no_kab,pd)
)
ORDER BY nikah;

set termout off;
spool off;
----------------------------------------------------------------------------------
---Tabel 18. Proporsi Kepala Keluarga Menurut Kelompok Umur dan Status Perkawinan
----------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1102 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum (kwn1) KWN1,sum (kwn2) KWN2
    ,sum (kwn3) KWN3,sum (kwn4) KWN4
    ,sum (jkwn) JKWN,to_char(round(sum (jkwn)*100/(pd),2),'000.00') PKWN    
    from
    (
    select
    no_prop,no_kab,no_kec,no_kel
    ,case when stat_kwn in(1) then 1 end KWN1
    ,case when stat_kwn in(2) then 1 end KWN2
    ,case when stat_kwn in(3) then 1 end KWN3
    ,case when stat_kwn in(4) then 1 end KWN4
    ,case when stat_kwn between 1 and 4 then 1 end JKWN    
    ,(select count(*) from biodata_wni where flag_status='0') pd
FROM biodata_wni WHERE flag_status='0'
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop,pd),(no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd))
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;
------------------------------------------------------------------------------------------------
--Tabel 19. Proporsi Kepala Keluarga Menurut Kelompok Umur, Status Perkawinan dan Jenis Kelamin
------------------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1103 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) as NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end NMKEC
    ,sum (kwn1) KWN1,sum (kwn2) KWN2
    ,sum (kwn3) KWN3,sum (kwn4) KWN4
    ,sum (jkwn) JKWN,to_char(round(sum (jkwn)*100/(pd),2),'000.00') PKWN    
    from
    (
    select
    no_prop,no_kab,no_kec
    ,case when stat_kwn in(1) then 1 end KWN1
    ,case when stat_kwn in(2) then 1 end KWN2
    ,case when stat_kwn in(3) then 1 end KWN3
    ,case when stat_kwn in(4) then 1 end KWN4
    ,case when stat_kwn between 1 and 4 then 1 end JKWN    
    ,(select count(*) from biodata_wni where flag_status='0') pd
FROM biodata_wni WHERE flag_status='0'
)
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd))
ORDER BY no_prop,no_kab,NO_KEC;

set termout off;
spool off;
---------------------------------------------------------------------------------------
--Tabel 20. Kepala Keluarga Menurut Kecamatan, Pendidikan ditammatkan dan Jenis Kelamin
---------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1201 NOLOGGING TABLESPACE SIAKDATA AS
select
     row_number() over(partition by no_kab order by no_kab) as urut
	 ,case when umur is null then 'JUMLAH' else umur end umur
     ,sum(bkwn) bkwn
     ,to_char(round(sum(bkwn)*100/(pd),2),'00.00')pbkwn
     ,sum(skwn) skwn
     ,to_char(round(sum(skwn)*100/(pd),2),'00.00')pskwn
     ,sum(hkwn) hkwn
     ,to_char(round(sum(hkwn)*100/(pd),2),'00.00')phkwn     
     ,sum(mkwn) mkwn               
     ,to_char(round(sum(mkwn)*100/(pd),2),'00.00')pmkwn
     ,sum(jduk)jduk
     ,to_char(round(sum(jduk)*100/(pd),2),'000.00')pjduk     
     from
     (
    select 
    no_prop,no_kab
    ,decode (floor (months_between (b.tgl_akhir,tgl_lhr)/60)
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>= 75') as UMUR
    ,case when stat_kwn in(1) then 1 end BKWN
    ,case when stat_kwn in(2) then 1 end SKWN
    ,case when stat_kwn in(3) then 1 end HKWN
    ,case when stat_kwn in(4) then 1 end MKWN
    ,case when stat_kwn in(1,2,3,4) then 1 end  JDUK
    ,(select count(*) from biodata_wni where flag_status='0')  pd    
FROM biodata_wni a,tgl_aktif b WHERE tgl_lhr<b.tgl_akhir and flag_status='0'
)
GROUP BY
GROUPING SETS((no_kab,no_prop,umur,pd),(no_kab,no_prop,pd))    
ORDER BY no_kab,urut;

set termout off;
spool off;
--------------------------------------------------------------
--Tabel 21. Penduduk Menurut Jenis Pekerjaan dan Jenis Kelamin
--------------------------------------------------------------
CREATE TABLE &1..xkuantitas1202 NOLOGGING TABLESPACE SIAKDATA AS
select
row_number() over(partition by 1 order by 1) as urut
,case when umur is null then 'JUMLAH' else umur end umur
,sum(case when pdkn in(1) and jk in(1) then 1 end) lpd1
,sum(case when pdkn in(1) and jk in(2) then 1 end) ppd1
,sum(case when pdkn in(1) and jk in(1,2) then 1 end) jpd1
,sum(case when pdkn in(2) and jk in(1) then 1 end) lpd2
,sum(case when pdkn in(2) and jk in(2) then 1 end) ppd2
,sum(case when pdkn in(2) and jk in(1,2) then 1 end) jpd2
,sum(case when pdkn in(3) and jk in(1) then 1 end) lpd3
,sum(case when pdkn in(3) and jk in(2) then 1 end) ppd3
,sum(case when pdkn in(3) and jk in(1,2) then 1 end) jpd3

from
(
select
decode (floor (months_between (b.tgl_akhir,a.tgl_lhr) / 60)
,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24'
,5, '25-29',6, '30-34',7, '35-39',8, '40-44',9, '45-49'
,10, '50-54',11, '55-59',12, '60-64',13, '65-69',14
,'70-74','>=75') as UMUR
,case when jenis_klmin in(1) then 1 else 2 end jk
,pddk_akh pdkn
FROM biodata_wni a, tgl_aktif b 
WHERE 
floor(months_between(b.tgl_akhir,a.tgl_lhr)/12)>=0 
and pddk_akh in(1,2,3)
and flag_status='0'
)
group by
grouping sets((umur)
,()
)
order by umur;

set termout off;
spool off;
---------------------------------------------------------------------
--Tabel 22. Kepala Keluarga Menurut Jenis Pekerjaan dan Jenis Kelamin
---------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1203 NOLOGGING TABLESPACE SIAKDATA AS
select
row_number() over(partition by 1 order by 1) as urut
,case when umur is null then 'JUMLAH' else umur end umur
,sum(case when pdkn in(4) and jk in(1) then 1 end) lpd4
,sum(case when pdkn in(4) and jk in(2) then 1 end) ppd4
,sum(case when pdkn in(4) and jk in(1,2) then 1 end) jpd4
,sum(case when pdkn in(5) and jk in(1) then 1 end) lpd5
,sum(case when pdkn in(5) and jk in(2) then 1 end) ppd5
,sum(case when pdkn in(5) and jk in(1,2) then 1 end) jpd5
from
(
select
decode (floor (months_between (b.tgl_akhir,tgl_lhr) / 60)
,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24'
,5, '25-29',6, '30-34',7, '35-39',8, '40-44',9, '45-49'
,10, '50-54',11, '55-59',12, '60-64',13, '65-69',14
,'70-74','>=75') as UMUR
,case when jenis_klmin in(1) then 1 else 2 end jk
,pddk_akh pdkn
FROM biodata_wni a, tgl_aktif b 
WHERE 
floor(months_between(b.tgl_akhir,a.tgl_lhr)/12)>=0 
and pddk_akh between 4 and 5
and flag_status='0'
)
group by
grouping sets((umur)
,()
)
order by umur;

set termout off;
spool off;
----------------------------------------------------------------------------------
--Tabel 23. Jumlah dan Persentasi Kepemilikan Kartu Keluarga Berdasarkan Kecamatan
----------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1204 NOLOGGING TABLESPACE SIAKDATA AS
select
row_number() over(partition by 1 order by 1) as urut
,case when umur is null then 'JUMLAH' else umur end umur
,sum(case when pdkn in(6) and jk in(1) then 1 end) lpd6
,sum(case when pdkn in(6) and jk in(2) then 1 end) ppd6
,sum(case when pdkn in(6) and jk in(1,2) then 1 end) jpd6
,sum(case when pdkn in(7) and jk in(1) then 1 end) lpd7
,sum(case when pdkn in(7) and jk in(2) then 1 end) ppd7
,sum(case when pdkn in(7) and jk in(1,2) then 1 end) jpd7
,sum(case when pdkn in(8) and jk in(1) then 1 end) lpd8
,sum(case when pdkn in(8) and jk in(2) then 1 end) ppd8
,sum(case when pdkn in(8) and jk in(1,2) then 1 end) jpd8
from
(
select
decode (floor (months_between (b.tgl_akhir,tgl_lhr) / 60)
,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24'
,5, '25-29',6, '30-34',7, '35-39',8, '40-44',9, '45-49'
,10, '50-54',11, '55-59',12, '60-64',13, '65-69',14
,'70-74','>=75') as UMUR
,case when jenis_klmin in(1) then 1 else 2 end jk
,pddk_akh pdkn
FROM biodata_wni a, tgl_aktif b 
WHERE 
floor(months_between(b.tgl_akhir,a.tgl_lhr)/12)>=0 
and pddk_akh between 6 and 8
and flag_status='0'
)
group by
grouping sets((umur)
,()
)
order by umur;

set termout off;
spool off;
----------------------------------------------------------------------------------
---Tabel 24. Jumlah dan Persentase Kpemilikan Kartu Keluarga Berdasarkan Kecamatan
----------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1205 NOLOGGING TABLESPACE SIAKDATA AS
select
row_number() over(partition by 1 order by 1) as urut
,case when umur is null then 'JUMLAH' else umur end umur
,sum(case when pdkn in(9) and jk in(1) then 1 end) lpd9
,sum(case when pdkn in(9) and jk in(2) then 1 end) ppd9
,sum(case when pdkn in(9) and jk in(1,2) then 1 end) jpd9
,sum(case when pdkn in(10) and jk in(1) then 1 end) lpd10
,sum(case when pdkn in(10) and jk in(2) then 1 end) ppd10
,sum(case when pdkn in(10) and jk in(1,2) then 1 end) jpd10
from
(
select no_kab,
decode (floor (months_between (b.tgl_akhir,tgl_lhr) / 60)
,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24'
,5, '25-29',6, '30-34',7, '35-39',8, '40-44',9, '45-49'
,10, '50-54',11, '55-59',12, '60-64',13, '65-69',14
,'70-74','>=75') as UMUR
,case when jenis_klmin in(1) then 1 else 2 end jk
,pddk_akh pdkn
FROM biodata_wni a, tgl_aktif b 
WHERE 
floor(months_between(b.tgl_akhir,a.tgl_lhr)/12)>=0 
and pddk_akh between 9 and 10
and flag_status='0'
)
group by
grouping sets((no_kab,umur)
,()
)
order by umur;

set termout off;
spool off;
-------------------------------------------------------------------------
---Tabel 25. Jumlah dan Persentase Kepemilikan KTP Berdasarkan Kecamatan
-------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1206 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum (case when pdkn in(6) and jk in(1) then 1 end) lpd6
    ,sum (case when pdkn in(6) and jk in(2) then 1 end) ppd6
    ,sum (case when pdkn in(6) and jk in(1,2) then 1 end) jpd6
    ,sum (case when pdkn in(7) and jk in(1) then 1 end) lpd7
    ,sum (case when pdkn in(7) and jk in(2) then 1 end) ppd7
    ,sum (case when pdkn in(7) and jk in(1,2) then 1 end) jpd7
    ,sum (case when pdkn in(8) and jk in(1) then 1 end) lpd8
    ,sum (case when pdkn in(8) and jk in(2) then 1 end) ppd8
    ,sum (case when pdkn in(8) and jk in(1,2) then 1 end) jpd8    
    from
    (
    select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 else 2 end jk
    ,case when jenis_klmin in(1,2) then 1 end jduk
    ,pddk_akh pdkn        
FROM biodata_wni WHERE flag_status='0' and pddk_akh between 6 and 8
)
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop),(no_kab,no_prop)
)
ORDER BY no_prop,no_kab,NO_KEC;

set termout off;
spool off;
----------------------------------------------------------------------------
--Tabel 25a. Jumlah dan Persentase Kepemilikan KTP-EL Berdasarkan Kecamatan
----------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1207 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum (case when pdkn in(9) and jk in(1) then 1 end) lpd9
    ,sum (case when pdkn in(9) and jk in(2) then 1 end) ppd9
    ,sum (case when pdkn in(9) and jk in(1,2) then 1 end) jpd9
    ,sum (case when pdkn in(10) and jk in(1) then 1 end) lpd10
    ,sum (case when pdkn in(10) and jk in(2) then 1 end) ppd10
    ,sum (case when pdkn in(10) and jk in(1,2) then 1 end) jpd10
    from
    (
    select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 else 2 end jk
    ,case when jenis_klmin in(1,2) then 1 end jduk
    ,pddk_akh pdkn        
FROM biodata_wni WHERE flag_status='0' and pddk_akh between 9 and 10
)
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop),(no_kab,no_prop)
)
ORDER BY no_prop,no_kab,NO_KEC;

set termout off;
spool off;
-----------------------------------------------------
--Tabel 26a. Jumlah Penduduk Menumpang Kartu Keluarga
-----------------------------------------------------
CREATE TABLE &1..xkuantitas1208 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum (case when pdkn in(1) and jk in(1) then 1 end) lpd1
    ,sum (case when pdkn in(1) and jk in(2) then 1 end) ppd1
    ,sum (case when pdkn in(1) and jk in(1,2) then 1 end) jpd1
    ,sum (case when pdkn in(2) and jk in(1) then 1 end) lpd2
    ,sum (case when pdkn in(2) and jk in(2) then 1 end) ppd2
    ,sum (case when pdkn in(2) and jk in(1,2) then 1 end) jpd2
    ,sum (case when pdkn in(3) and jk in(1) then 1 end) lpd3
    ,sum (case when pdkn in(3) and jk in(2) then 1 end) ppd3
    ,sum (case when pdkn in(3) and jk in(1,2) then 1 end) jpd3
    from
    (
    select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 else 2 end jk
    ,case when jenis_klmin in(1,2) then 1 end jduk
    ,pddk_akh pdkn        
FROM biodata_wni WHERE flag_status='0' and pddk_akh between 1 and 3
)
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop),(no_kab,no_prop)
)
ORDER BY no_prop,no_kab,NO_KEC;

set termout off;
spool off;
-----------------------------------------------------------------
--Tabel 26. Jumlah Kepala Keluarga Pisah KK Berdasarkan Kecamatan
-----------------------------------------------------------------
CREATE TABLE &1..xkuantitas1209 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum (case when pdkn in(4) and jk in(1) then 1 end) lpd4
    ,sum (case when pdkn in(4) and jk in(2) then 1 end) ppd4
    ,sum (case when pdkn in(4) and jk in(1,2) then 1 end) jpd4
    ,sum (case when pdkn in(5) and jk in(1) then 1 end) lpd5
    ,sum (case when pdkn in(5) and jk in(2) then 1 end) ppd5
    ,sum (case when pdkn in(5) and jk in(1,2) then 1 end) jpd5
    from
    (
    select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 else 2 end jk
    ,case when jenis_klmin in(1,2) then 1 end jduk
    ,pddk_akh pdkn        
FROM biodata_wni WHERE flag_status='0' and pddk_akh between 4 and 5
)
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop),(no_kab,no_prop)
)
ORDER BY no_prop,no_kab,NO_KEC;

set termout off;
spool off;
------------------------------------------------------------------------
--Tabel 27. Jumlah dan Persentase Kepemilikan Akta Berdasarkan Kecamatan
------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1210 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum (case when pdkn in(6) and jk in(1) then 1 end) lpd6
    ,sum (case when pdkn in(6) and jk in(2) then 1 end) ppd6
    ,sum (case when pdkn in(6) and jk in(1,2) then 1 end) jpd6
    ,sum (case when pdkn in(7) and jk in(1) then 1 end) lpd7
    ,sum (case when pdkn in(7) and jk in(2) then 1 end) ppd7
    ,sum (case when pdkn in(7) and jk in(1,2) then 1 end) jpd7
    ,sum (case when pdkn in(8) and jk in(1) then 1 end) lpd8
    ,sum (case when pdkn in(8) and jk in(2) then 1 end) ppd8
    ,sum (case when pdkn in(8) and jk in(1,2) then 1 end) jpd8    
    from
    (
    select
    no_prop,no_kab,no_kec,no_kel
    ,case when jenis_klmin in(1) then 1 else 2 end jk
    ,case when jenis_klmin in(1,2) then 1 end jduk
    ,pddk_akh pdkn        
FROM biodata_wni WHERE flag_status='0' and pddk_akh between 6 and 8
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop),(no_kec,no_kab,no_prop),(no_kab,no_prop)
)
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;
------------------------------------------------------------------------
--Tabel 27a. Jumlah dan Persentase Kepemilikan Akta Berdasarkan Kecamatan
------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1211 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum (case when pdkn in(9) and jk in(1) then 1 end) lpd9
    ,sum (case when pdkn in(9) and jk in(2) then 1 end) ppd9
    ,sum (case when pdkn in(9) and jk in(1,2) then 1 end) jpd9
    ,sum (case when pdkn in(10) and jk in(1) then 1 end) lpd10
    ,sum (case when pdkn in(10) and jk in(2) then 1 end) ppd10
    ,sum (case when pdkn in(10) and jk in(1,2) then 1 end) jpd10
    from
    (
    select
    no_prop,no_kab,no_kec,no_kel
    ,case when jenis_klmin in(1) then 1 else 2 end jk
    ,case when jenis_klmin in(1,2) then 1 end jduk
    ,pddk_akh pdkn        
FROM biodata_wni WHERE flag_status='0' and pddk_akh between 9 and 10
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop),(no_kec,no_kab,no_prop),(no_kab,no_prop)
)
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;
-------------------------------------------------------
---Tabel 28. Kepemilikan Akta berdasarkan Struktur Umur
-------------------------------------------------------
CREATE TABLE &1..xkuantitas1212 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum (case when pdkn in(1) and jk in(1) then 1 end) lpd1
    ,sum (case when pdkn in(1) and jk in(2) then 1 end) ppd1
    ,sum (case when pdkn in(1) and jk in(1,2) then 1 end) jpd1
    ,sum (case when pdkn in(2) and jk in(1) then 1 end) lpd2
    ,sum (case when pdkn in(2) and jk in(2) then 1 end) ppd2
    ,sum (case when pdkn in(2) and jk in(1,2) then 1 end) jpd2
    ,sum (case when pdkn in(3) and jk in(1) then 1 end) lpd3
    ,sum (case when pdkn in(3) and jk in(2) then 1 end) ppd3
    ,sum (case when pdkn in(3) and jk in(1,2) then 1 end) jpd3
    from
    (
    select
    no_prop,no_kab,no_kec,no_kel
    ,case when jenis_klmin in(1) then 1 else 2 end jk
    ,case when jenis_klmin in(1,2) then 1 end jduk
    ,pddk_akh pdkn        
FROM biodata_wni WHERE flag_status='0' and pddk_akh between 1 and 3
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop),(no_kec,no_kab,no_prop),(no_kab,no_prop)
)
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;
-------------------------------------------------------------------------------
--Tabel 29. Kepemilikan Akta Kelahiran Kelompok Umur Berdasarkan Jenis Kelamin
-------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1213 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum (case when pdkn in(4) and jk in(1) then 1 end) lpd4
    ,sum (case when pdkn in(4) and jk in(2) then 1 end) ppd4
    ,sum (case when pdkn in(4) and jk in(1,2) then 1 end) jpd4
    ,sum (case when pdkn in(5) and jk in(1) then 1 end) lpd5
    ,sum (case when pdkn in(5) and jk in(2) then 1 end) ppd5
    ,sum (case when pdkn in(5) and jk in(1,2) then 1 end) jpd5
    from
    (
    select
    no_prop,no_kab,no_kec,no_kel
    ,case when jenis_klmin in(1) then 1 else 2 end jk
    ,case when jenis_klmin in(1,2) then 1 end jduk
    ,pddk_akh pdkn        
FROM biodata_wni WHERE flag_status='0' and pddk_akh between 4 and 5
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop),(no_kec,no_kab,no_prop),(no_kab,no_prop))
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;
---------------------------------------------------------------
---Tabel 30. Kepemilikan Akta Perkawinan Berdasarkan Kecamatan
---------------------------------------------------------------
CREATE TABLE &1..xkuantitas1301 NOLOGGING TABLESPACE SIAKDATA AS
select
    cacat||'.' NPC
    ,case when cacat is null then 'JUMLAH' else b.descrip end  NMPC
    ,sum (lk) LK
    ,to_char(round(sum(lk)*100/(pd),4),'00.0000')PLK    
    ,sum (pr) pr
    ,to_char(round(sum(pr)*100/(pd),4),'00.0000')PPR    
    ,sum (jduk) JDUK
    ,to_char(round(sum(jduk)*100/(pd),4),'000.0000')PDUK
from
(
select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,case when jenis_klmin in(1,2) then 1 end jduk        
    ,pnydng_cct cacat
    ,(select count(*) from biodata_wni where flag_status='0') pd
  FROM biodata_wni where flag_status='0' 
)x
join ref_siak_wni b on x.cacat=b.no and b.sect='701' 
GROUP BY 
grouping sets((no_prop,no_kab,cacat,b.descrip,pd)
,(no_kab,pd)
)
ORDER BY cacat;

set termout off;
spool off;
-------------------------------------------------------------------------------
--Tabel 31. Kepemilikan Akta Perkawinan Kelompok Umur Berdasarkan Jenis Kelamin
-------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1302 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||
	trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum (cct1) cct1,sum (cct2) cct2
    ,sum (cct3) cct3,sum (cct4) cct4
    ,sum (cct5) cct5,sum (cct6) cct6
    ,sum (jcct) jcct,to_char(round(sum (jcct)*100/(pd),4),'000.0000') pcct    
    from
    (
    select
    no_prop,no_kab,no_kec,no_kel
    ,case when pnydng_cct in(1) then 1 end cct1
    ,case when pnydng_cct in(2) then 1 end cct2
    ,case when pnydng_cct in(3) then 1 end cct3
    ,case when pnydng_cct in(4) then 1 end cct4
    ,case when pnydng_cct in(5) then 1 end cct5
    ,case when pnydng_cct in(6) then 1 end cct6
    ,case when pnydng_cct in(1,2,3,4,5,6) then 1 end jcct            
    ,(select count(*) from biodata_wni where flag_status='0') pd
FROM biodata_wni WHERE flag_status='0'
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop,pd),(no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd)
)
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;
--------------------------------------------------------------
---Tabel 32. Kepemilikan Akta Perceraian Berdasarkan Kecamatan
--------------------------------------------------------------
CREATE TABLE &1..xkuantitas1303 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) as NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end NMKEC
    ,sum (cct1) cct1,sum (cct2) cct2
    ,sum (cct3) cct3,sum (cct4) cct4
    ,sum (cct5) cct5,sum (cct6) cct6
    ,sum (jcct) jcct,to_char(round(sum (jcct)*100/(pd),4),'000.0000') pcct    
    from
    (
    select
    no_prop,no_kab,no_kec
    ,case when pnydng_cct in(1) then 1 end cct1
    ,case when pnydng_cct in(2) then 1 end cct2
    ,case when pnydng_cct in(3) then 1 end cct3
    ,case when pnydng_cct in(4) then 1 end cct4
    ,case when pnydng_cct in(5) then 1 end cct5
    ,case when pnydng_cct in(6) then 1 end cct6
    ,case when pnydng_cct in(1,2,3,4,5,6) then 1 end jcct            
    ,(select count(*) from biodata_wni where flag_status='0') pd
FROM biodata_wni WHERE flag_status='0'
)
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd))
ORDER BY no_prop,no_kab,NO_KEC;

set termout off;
spool off;
---------------------------------------------------------------------------------
--Tabel 33. Rekapitulasi Migrasi Pindah Antar Propinsi Berdasarkan Kecamatan Asal
---------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1401 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end nmkec
    ,sum(jduk) jduk
    ,to_char(round(sum(jduk)*100/(pd),2),'000.00') pjduk
    ,sum(jkk) jkk
    ,to_char(round(sum(jkk)*100/(pd1),2),'000.00') pjkk
    ,to_char(round(sum(jduk)/sum(jkk),2),'00.00') ravg
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec,a.no_kel
    ,case when nik is not null then 1 end jduk
    ,case when stat_hbkel in(1) then 1 end jkk
    ,(select count(*) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0') pd
    ,(select count(*) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.stat_hbkel=1 and a.flag_status='0') pd1
FROM biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0'
)    
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop,pd,pd1),(no_kec,no_kab,no_prop,pd,pd1),(no_kab,no_prop,pd,pd1))
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);
		
set termout off;
spool off;
---------------------------------------------------------------------------------------
--Tabel 34. Rekapitulasi Migrasi Pindah Antar Kabupaten/Kota Berdasarkan Kecamatan Asal
---------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1402 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) as NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum(jduk) jduk
    ,to_char(round(sum(jduk)*100/(pd),2),'000.00') pjduk
    ,sum(jkk) jkk
    ,to_char(round(sum(jkk)*100/(pd1),2),'000.00') pjkk
    ,to_char(round(sum(jduk)/sum(jkk),2),'00.00') ravg
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec
    ,case when nik is not null then 1 end jduk
    ,case when stat_hbkel in(1) then 1 end jkk
    ,(select count(*) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0') pd
    ,(select count(*) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.stat_hbkel=1 and a.flag_status='0') pd1
FROM biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0'
)    
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop,pd,pd1),(no_kab,no_prop,pd,pd1)
)
ORDER BY no_prop,no_kab,NO_KEC;
		
set termout off;
spool off;
-----------------------------------------------------------------------------------		
--Tabel 35. Rekapitulasi Migrasi Masuk Antar Propinsi Berdasarkan Kecamatan Tujuan
-----------------------------------------------------------------------------------		
CREATE TABLE &1..xkuantitas1403 NOLOGGING TABLESPACE SIAKDATA AS
select
    pkrjn||'.' NPC
    ,case when pkrjn is null then 'JUMLAH' else b.descrip end  NMPC
    ,sum (lk) LK
    ,to_char(round(sum(lk)*100/(pd),4),'00.0000')PLK    
    ,sum (pr) pr
    ,to_char(round(sum(pr)*100/(pd),4),'00.0000')PPR    
    ,sum (jduk) JDUK
    ,to_char(round(sum(jduk)*100/(pd),4),'000.0000')PDUK
from
(
select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,case when jenis_klmin in(1,2) then 1 end jduk        
    ,jenis_pkrjn pkrjn
    ,(select count(*) from biodata_wni where flag_status='0') pd
  FROM biodata_wni where flag_status='0' 
    and  jenis_pkrjn  in (5,6,7)
)x
join ref_siak_wni b on x.pkrjn=b.no and b.sect='201' 
GROUP BY 
grouping sets((no_prop,no_kab,pkrjn,b.descrip,pd)
,(no_kab,pd)
)
ORDER BY pkrjn;

set termout off;
spool off;
----------------------------------------------------------------------------------------	
--Tabel 36. Rekapitulasi Migrasi Masuk Antar Kabupaten/Kota Berdasarkan Kecamatan Tujuan
----------------------------------------------------------------------------------------	
CREATE TABLE &1..xkuantitas1404 NOLOGGING TABLESPACE SIAKDATA AS
select
    pkrjn||'.' NPC
    ,case when pkrjn is null then 'JUMLAH' else b.descrip end  NMPC
    ,sum (lk) LK
    ,to_char(round(sum(lk)*100/(pd),4),'00.0000')PLK    
    ,sum (pr) pr
    ,to_char(round(sum(pr)*100/(pd),4),'00.0000')PPR    
    ,sum (jduk) JDUK
    ,to_char(round(sum(jduk)*100/(pd),4),'000.0000')PDUK
from
(
select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,case when jenis_klmin in(1,2) then 1 end jduk        
    ,jenis_pkrjn pkrjn
    ,(select count(*) from biodata_wni where flag_status='0') pd
  FROM biodata_wni where flag_status='0' 
    and  jenis_pkrjn  in (15,16,17,18)
)x
join ref_siak_wni b on x.pkrjn=b.no and b.sect='201' 
GROUP BY 
grouping sets((no_prop,no_kab,pkrjn,b.descrip,pd)
,(no_kab,pd)
)
ORDER BY pkrjn;

set termout off;
spool off;
-----------------------------------------------------------------	
--GRAPHICS 4a. Pyramida Penduduk, Kelompok Umur dan Jenis Kelamin
-----------------------------------------------------------------	
CREATE TABLE &1..xkuantitas1405 NOLOGGING TABLESPACE SIAKDATA AS
select
    pkrjn||'.' NPC
    ,case when pkrjn is null then 'JUMLAH' else b.descrip end  NMPC
    ,sum (lk) LK
    ,to_char(round(sum(lk)*100/(pd),4),'00.0000')PLK    
    ,sum (pr) pr
    ,to_char(round(sum(pr)*100/(pd),4),'00.0000')PPR    
    ,sum (jduk) JDUK
    ,to_char(round(sum(jduk)*100/(pd),4),'000.0000')PDUK
from
(
select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,case when jenis_klmin in(1,2) then 1 end jduk        
    ,jenis_pkrjn pkrjn
    ,(select count(*) from biodata_wni where flag_status='0') pd
  FROM biodata_wni where flag_status='0' 
    and  jenis_pkrjn  in (41,42,43,45,87)
)x
join ref_siak_wni b on x.pkrjn=b.no and b.sect='201' 
GROUP BY 
grouping sets((no_prop,no_kab,pkrjn,b.descrip,pd)
,(no_kab,pd)
)
ORDER BY pkrjn;

set termout off;
spool off;
-----------------------------------------------------------------------------------------
--Graphics 33. Rekapitulasi Penduduk Migrasi Keluar berdasarkan Status Klasifikasi Pindah
-----------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1406 NOLOGGING TABLESPACE SIAKDATA AS
select
    pkrjn||'.' NPC
    ,case when pkrjn is null then 'JUMLAH' else b.descrip end  NMPC
    ,sum (lk) LK
    ,to_char(round(sum(lk)*100/(pd),4),'00.0000')PLK    
    ,sum (pr) pr
    ,to_char(round(sum(pr)*100/(pd),4),'00.0000')PPR    
    ,sum (jduk) JDUK
    ,to_char(round(sum(jduk)*100/(pd),4),'000.0000')PDUK
from
(
select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,case when jenis_klmin in(1,2) then 1 end jduk        
    ,jenis_pkrjn pkrjn
    ,(select count(*) from biodata_wni where flag_status='0') pd
  FROM biodata_wni where flag_status='0' 
    and  jenis_pkrjn  in (44,77,78)
)x
join ref_siak_wni b on x.pkrjn=b.no and b.sect='201' 
GROUP BY 
grouping sets((no_prop,no_kab,pkrjn,b.descrip,pd)
,(no_kab,pd)
)
ORDER BY pkrjn;

set termout off;
spool off;
-----------------------------------------------------------------------------------------
--Graphics 34. Rekapitulasi Penduduk Migrasi Keluar berdasarkan Klasifikasi Pindah 12345
-----------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1407 NOLOGGING TABLESPACE SIAKDATA AS
select
    pkrjn||'.' NPC
    ,case when pkrjn is null then 'JUMLAH' else b.descrip end  NMPC
    ,sum (lk) LK
    ,to_char(round(sum(lk)*100/(pd),4),'00.0000')PLK    
    ,sum (pr) pr
    ,to_char(round(sum(pr)*100/(pd),4),'00.0000')PPR    
    ,sum (jduk) JDUK
    ,to_char(round(sum(jduk)*100/(pd),4),'000.0000')PDUK
from
(
select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,case when jenis_klmin in(1,2) then 1 end jduk        
    ,jenis_pkrjn pkrjn
    ,(select count(*) from biodata_wni where flag_status='0') pd
  FROM biodata_wni where flag_status='0' 
    and  jenis_pkrjn  in (64,65)
)x
join ref_siak_wni b on x.pkrjn=b.no and b.sect='201' 
GROUP BY 
grouping sets((no_prop,no_kab,pkrjn,b.descrip,pd)
,(no_kab,pd)
)
ORDER BY pkrjn;

set termout off;
spool off;
------------------------------------------------------------------------------------
--Graphics 35. Rekapitulasi Penduduk Migrasi Masuk Sesuai Status Klasifikasi Datang
------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1408 NOLOGGING TABLESPACE SIAKDATA AS
select
    pkrjn||'.' NPC
    ,case when pkrjn is null then 'JUMLAH' else b.descrip end  NMPC
    ,sum (lk) LK
    ,to_char(round(sum(lk)*100/(pd),4),'00.0000')PLK    
    ,sum (pr) pr
    ,to_char(round(sum(pr)*100/(pd),4),'00.0000')PPR    
    ,sum (jduk) JDUK
    ,to_char(round(sum(jduk)*100/(pd),4),'000.0000')PDUK
from
(
select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,case when jenis_klmin in(1,2) then 1 end jduk        
    ,jenis_pkrjn pkrjn
    ,(select count(*) from biodata_wni where flag_status='0') pd
  FROM biodata_wni where flag_status='0' 
    and  jenis_pkrjn  in (9,10,11,20,21,22)
)x
join ref_siak_wni b on x.pkrjn=b.no and b.sect='201' 
GROUP BY 
grouping sets((no_prop,no_kab,pkrjn,b.descrip,pd)
,(no_kab,pd)
)
ORDER BY pkrjn;

set termout off;
spool off;
------------------------------------------------------------------------------------
--Graphics 35. Rekapitulasi Penduduk Migrasi Masuk Sesuai Klasifikasi Datang 12345
------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas1409 NOLOGGING TABLESPACE SIAKDATA AS
select
    pkrjn||'.' NPC
    ,case when pkrjn is null then 'JUMLAH' else b.descrip end  NMPC
    ,sum (lk) LK
    ,to_char(round(sum(lk)*100/(pd),4),'00.0000')PLK    
    ,sum (pr) pr
    ,to_char(round(sum(pr)*100/(pd),4),'00.0000')PPR    
    ,sum (jduk) JDUK
    ,to_char(round(sum(jduk)*100/(pd),4),'000.0000')PDUK
from
(
select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,case when jenis_klmin in(1,2) then 1 end jduk        
    ,jenis_pkrjn pkrjn
    ,(select count(*) from biodata_wni where flag_status='0') pd
  FROM biodata_wni where flag_status='0' 
    and  jenis_pkrjn  in (14,66,79,81)
)x
join ref_siak_wni b on x.pkrjn=b.no and b.sect='201' 
GROUP BY 
grouping sets((no_prop,no_kab,pkrjn,b.descrip,pd)
,(no_kab,pd)
)
ORDER BY pkrjn;

set termout off;
spool off;
--------------------------------------------------------
--GRAPHICS 7. GRAPHICS ANGKATAN KERJA YANG SUDAH BEKERJA
--------------------------------------------------------
CREATE TABLE &1..xkuantitas1410 NOLOGGING TABLESPACE SIAKDATA AS
select
    pkrjn||'.' NPC
    ,case when pkrjn is null then 'JUMLAH' else b.descrip end  NMPC
    ,sum (lk) LK
    ,to_char(round(sum(lk)*100/(pd),4),'00.0000')PLK    
    ,sum (pr) pr
    ,to_char(round(sum(pr)*100/(pd),4),'00.0000')PPR    
    ,sum (jduk) JDUK
    ,to_char(round(sum(jduk)*100/(pd),4),'000.0000')PDUK
from
(
select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,case when jenis_klmin in(1,2) then 1 end jduk        
    ,jenis_pkrjn pkrjn
    ,(select count(*) from biodata_wni where flag_status='0') pd
  FROM biodata_wni where flag_status='0' 
    and  jenis_pkrjn  in (24,25,26,27,28,29,30,31,32,33,34)
)x
join ref_siak_wni b on x.pkrjn=b.no and b.sect='201' 
GROUP BY 
grouping sets((no_prop,no_kab,pkrjn,b.descrip,pd)
,(no_kab,pd)
)
ORDER BY pkrjn;
	  
set termout off;
spool off;
--------------------------------------------------------	  
--GRAPHICS 7. GRAPHICS ANGKATAN KERJA YANG BELUM BEKERJA	  
--------------------------------------------------------
CREATE TABLE &1..xkuantitas1411 NOLOGGING TABLESPACE SIAKDATA AS
select
    pkrjn||'.' NPC
    ,case when pkrjn is null then 'JUMLAH' else b.descrip end  NMPC
    ,sum (lk) LK
    ,to_char(round(sum(lk)*100/(pd),4),'00.0000')PLK    
    ,sum (pr) pr
    ,to_char(round(sum(pr)*100/(pd),4),'00.0000')PPR    
    ,sum (jduk) JDUK
    ,to_char(round(sum(jduk)*100/(pd),4),'000.0000')PDUK
from
(
select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,case when jenis_klmin in(1,2) then 1 end jduk        
    ,jenis_pkrjn pkrjn
    ,(select count(*) from biodata_wni where flag_status='0') pd
  FROM biodata_wni where flag_status='0'
)x
join ref_siak_wni b on x.pkrjn=b.no and b.sect='201' 
GROUP BY 
grouping sets((no_prop,no_kab,pkrjn,b.descrip,pd)
,(no_kab,pd)
)
ORDER BY pkrjn;
	  
set termout off;
spool off;
--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas1412 NOLOGGING TABLESPACE SIAKDATA AS
select
    pkrjn||'.' NPC
    ,case when pkrjn is null then 'JUMLAH' else b.descrip end  NMPC
    ,sum (lk) LK
    ,to_char(round(sum(lk)*100/(pd),4),'00.0000')PLK    
    ,sum (pr) pr
    ,to_char(round(sum(pr)*100/(pd),4),'00.0000')PPR    
    ,sum (jduk) JDUK
    ,to_char(round(sum(jduk)*100/(pd),4),'000.0000')PDUK
from
(
select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,case when jenis_klmin in(1,2) then 1 end jduk        
    ,jenis_pkrjn pkrjn
    ,(select count(*) from biodata_wni where flag_status='0') pd
  FROM biodata_wni where flag_status='0' 
    and  jenis_pkrjn  in (48,49,62,63)
)x
join ref_siak_wni b on x.pkrjn=b.no and b.sect='201' 
GROUP BY 
grouping sets((no_prop,no_kab,pkrjn,b.descrip,pd)
,(no_kab,pd)
)
ORDER BY pkrjn;
	  
set termout off;
spool off;



--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas1413 NOLOGGING TABLESPACE SIAKDATA AS
select
    pkrjn||'.' NPC
    ,case when pkrjn is null then 'JUMLAH' else b.descrip end  NMPC
    ,sum (lk) LK
    ,to_char(round(sum(lk)*100/(pd),4),'00.0000')PLK    
    ,sum (pr) pr
    ,to_char(round(sum(pr)*100/(pd),4),'00.0000')PPR    
    ,sum (jduk) JDUK
    ,to_char(round(sum(jduk)*100/(pd),4),'000.0000')PDUK
from
(
select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,case when jenis_klmin in(1,2) then 1 end jduk        
    ,jenis_pkrjn pkrjn
    ,(select count(*) from biodata_wni where flag_status='0') pd
  FROM biodata_wni where flag_status='0' 
    and  jenis_pkrjn in(51,52,56,57,58,59,60,61,86)
)x
join ref_siak_wni b on x.pkrjn=b.no and b.sect='201' 
GROUP BY 
grouping sets((no_prop,no_kab,pkrjn,b.descrip,pd)
,(no_kab,pd)
)
ORDER BY pkrjn;
	  
set termout off;
spool off;

--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas1414 NOLOGGING TABLESPACE SIAKDATA AS
select
    pkrjn||'.' NPC
    ,case when pkrjn is null then 'JUMLAH' else b.descrip end  NMPC
    ,sum (lk) LK
    ,to_char(round(sum(lk)*100/(pd),4),'00.0000')PLK    
    ,sum (pr) pr
    ,to_char(round(sum(pr)*100/(pd),4),'00.0000')PPR    
    ,sum (jduk) JDUK
    ,to_char(round(sum(jduk)*100/(pd),4),'000.0000')PDUK
from
(
select
    no_prop,no_kab,no_kec
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,case when jenis_klmin in(1,2) then 1 end jduk        
    ,jenis_pkrjn pkrjn
    ,(select count(*) from biodata_wni where flag_status='0') pd
  FROM biodata_wni where flag_status='0' 
    and  jenis_pkrjn  in (37,38,72,73,74,75,76)
)x
join ref_siak_wni b on x.pkrjn=b.no and b.sect='201' 
GROUP BY 
grouping sets((no_prop,no_kab,pkrjn,b.descrip,pd)
,(no_kab,pd)
)
ORDER BY pkrjn;
	  
set termout off;
spool off;

--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas1501 NOLOGGING TABLESPACE SIAKDATA AS
select
    hbkel||'.' NHK,case when 
    hbkel is null then 'JUMLAH' else 
    b.descrip end NMHK
    ,sum(lk)lk
    ,sum(pr)pr
    ,sum(jduk)jduk
from
(
select
     a.no_prop,a.no_kab,a.stat_hbkel hbkel
    ,case when jenis_klmin in(1) then 1 end LK
    ,case when jenis_klmin in(2) then 1 end PR
    ,case when jenis_klmin in(1,2) then 1 end JDUK
FROM biodata_wni a, data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0'
)x
join ref_siak_wni b on x.hbkel=b.no and b.sect='301'
GROUP BY 
grouping sets((no_prop,no_kab,hbkel,b.descrip)
,()
)
ORDER BY hbkel;
	  
set termout off;
spool off;
--------------------------------------------------------		  
--
--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas1502 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum(hbkel1)hbkel1 ,sum(hbkel2)hbkel2    
    ,sum(hbkel3)hbkel3 ,sum(hbkel4)hbkel4
    ,sum(hbkel5)hbkel5 ,sum(hbkel6)hbkel6
    ,sum(hbkel7)hbkel7 ,sum(hbkel8)hbkel8
    ,sum(hbkel9)hbkel9 ,sum(hbkel10)hbkel10
    ,sum(hbkel11)hbkel11 ,sum(jhbkel)jhbkel
    ,to_char(round(sum(jhbkel)*100/(pd),2),'000.00') phbkel               
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec,a.no_kel
    ,case when stat_hbkel in(1) then 1 end HBKEL1
    ,case when stat_hbkel in(2) then 1 end HBKEL2
    ,case when stat_hbkel in(3) then 1 end HBKEL3
    ,case when stat_hbkel in(4) then 1 end HBKEL4
    ,case when stat_hbkel in(5) then 1 end HBKEL5
    ,case when stat_hbkel in(6) then 1 end HBKEL6
    ,case when stat_hbkel in(7) then 1 end HBKEL7
    ,case when stat_hbkel in(8) then 1 end HBKEL8
    ,case when stat_hbkel in(9) then 1 end HBKEL9
    ,case when stat_hbkel in(10) then 1  end HBKEL10
    ,case when stat_hbkel in(11) then 1  end HBKEL11
    ,case when stat_hbkel between 1 and 11 then 1 end JHBKEL
    ,(select count(nik) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0') pd
FROM biodata_wni a,data_keluarga b WHERE a.no_kk=b.no_kk and a.flag_status='0'
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop,pd),(no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd))
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);
	  
set termout off;
spool off;

--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas1503 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum(hbkel1)hbkel1 ,sum(hbkel2)hbkel2    
    ,sum(hbkel3)hbkel3 ,sum(hbkel4)hbkel4
    ,sum(hbkel5)hbkel5 ,sum(hbkel6)hbkel6
    ,sum(jhbkel)jhbkel
    ,to_char(round(sum(jhbkel)*100/(pd),2),'000.00') phbkel               
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec,a.no_kel
    ,case when stat_hbkel in(1) then 1 end HBKEL1
    ,case when stat_hbkel in(2) then 1 end HBKEL2
    ,case when stat_hbkel in(3) then 1 end HBKEL3
    ,case when stat_hbkel in(4) then 1 end HBKEL4
    ,case when stat_hbkel in(5) then 1 end HBKEL5
    ,case when stat_hbkel in(6) then 1 end HBKEL6
    ,case when stat_hbkel between 1 and 6 then 1 end JHBKEL
    ,(select count(nik) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0' and a.stat_hbkel in(1,2,3,4,5,6)) pd
FROM biodata_wni a,data_keluarga b WHERE a.no_kk=b.no_kk and a.flag_status='0' and a.stat_hbkel in(1,2,3,4,5,6)
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop,pd),(no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd))
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);
	  
set termout off;
spool off;

--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas1504 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum(hbkel7)hbkel7 ,sum(hbkel8)hbkel8
    ,sum(hbkel9)hbkel9 ,sum(hbkel10)hbkel10
    ,sum(hbkel11)hbkel11 ,sum(jhbkel)jhbkel
    ,to_char(round(sum(jhbkel)*100/(pd),2),'000.00') phbkel               
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec,a.no_kel
    ,case when stat_hbkel in(7) then 1 end HBKEL7
    ,case when stat_hbkel in(8) then 1 end HBKEL8
    ,case when stat_hbkel in(9) then 1 end HBKEL9
    ,case when stat_hbkel in(10) then 1  end HBKEL10
    ,case when stat_hbkel in(11) then 1  end HBKEL11
    ,case when stat_hbkel between 7 and 11 then 1 end JHBKEL
    ,(select count(nik) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0' and a.stat_hbkel in(7,8,9,10,11)) pd
FROM biodata_wni a,data_keluarga b WHERE a.no_kk=b.no_kk and a.flag_status='0'
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop,pd),(no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd))
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);
	  
set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas1505 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) as NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end NMKEC
    ,sum(hbkel1)hbkel1 ,sum(hbkel2)hbkel2    
    ,sum(hbkel3)hbkel3 ,sum(hbkel4)hbkel4
    ,sum(hbkel5)hbkel5 ,sum(hbkel6)hbkel6
    ,sum(hbkel7)hbkel7 ,sum(hbkel8)hbkel8
    ,sum(hbkel9)hbkel9 ,sum(hbkel10)hbkel10
    ,sum(hbkel11)hbkel11 ,sum(jhbkel)jhbkel
    ,to_char(round(sum(jhbkel)*100/(pd),2),'000.00') phbkel               
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec
    ,case when stat_hbkel in(1) then 1 end HBKEL1
    ,case when stat_hbkel in(2) then 1 end HBKEL2
    ,case when stat_hbkel in(3) then 1 end HBKEL3
    ,case when stat_hbkel in(4) then 1 end HBKEL4
    ,case when stat_hbkel in(5) then 1 end HBKEL5
    ,case when stat_hbkel in(6) then 1 end HBKEL6
    ,case when stat_hbkel in(7) then 1 end HBKEL7
    ,case when stat_hbkel in(8) then 1 end HBKEL8
    ,case when stat_hbkel in(9) then 1 end HBKEL9
    ,case when stat_hbkel in(10) then 1  end HBKEL10
    ,case when stat_hbkel in(11) then 1  end HBKEL11
    ,case when stat_hbkel between 1 and 11 then 1 end JHBKEL
    ,(select count(nik) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0') pd
FROM biodata_wni a,data_keluarga b WHERE a.no_kk=b.no_kk and a.flag_status='0'
)
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd)
)
ORDER BY no_prop,no_kab,NO_KEC;
	  
set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas1506 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) as NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end NMKEC
    ,sum(hbkel1)hbkel1 ,sum(hbkel2)hbkel2    
    ,sum(hbkel3)hbkel3 ,sum(hbkel4)hbkel4
    ,sum(hbkel5)hbkel5 ,sum(hbkel6)hbkel6
    ,sum(jhbkel)jhbkel
    ,to_char(round(sum(jhbkel)*100/(pd),2),'000.00') phbkel               
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec
    ,case when stat_hbkel in(1) then 1 end HBKEL1
    ,case when stat_hbkel in(2) then 1 end HBKEL2
    ,case when stat_hbkel in(3) then 1 end HBKEL3
    ,case when stat_hbkel in(4) then 1 end HBKEL4
    ,case when stat_hbkel in(5) then 1 end HBKEL5
    ,case when stat_hbkel in(6) then 1 end HBKEL6
    ,case when stat_hbkel between 1 and 6 then 1 end JHBKEL
    ,(select count(nik) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0' and stat_hbkel in(1,2,3,4,5,6))  pd
FROM biodata_wni a,data_keluarga b WHERE a.no_kk=b.no_kk and a.flag_status='0' and stat_hbkel in(1,2,3,4,5,6)
)
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd)
)
ORDER BY no_prop,no_kab,NO_KEC;
	  
set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas1507 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) as NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end NMKEC
    ,sum(hbkel7)hbkel7 ,sum(hbkel8)hbkel8
    ,sum(hbkel9)hbkel9 ,sum(hbkel10)hbkel10
    ,sum(hbkel11)hbkel11 ,sum(jhbkel)jhbkel
    ,to_char(round(sum(jhbkel)*100/(pd),2),'000.00') phbkel               
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec
    ,case when stat_hbkel in(7) then 1 end HBKEL7
    ,case when stat_hbkel in(8) then 1 end HBKEL8
    ,case when stat_hbkel in(9) then 1 end HBKEL9
    ,case when stat_hbkel in(10) then 1  end HBKEL10
    ,case when stat_hbkel in(11) then 1  end HBKEL11
    ,case when stat_hbkel between 7 and 11 then 1 end JHBKEL
    ,(select count(nik) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0' and stat_hbkel in(7,8,9,10,11)) pd
FROM biodata_wni a,data_keluarga b WHERE a.no_kk=b.no_kk and a.flag_status='0' 
)
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd)
)
ORDER BY no_prop,no_kab,NO_KEC;
	  
set termout off;
spool off;

--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas1601 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
   ,sum(lkk) lkk
   ,to_char(round(sum(lkk)*100/(pd),2),'00.00') plkk
   ,sum(pkk) pkk
   ,to_char(round(sum(pkk)*100/(pd),2),'00.00') ppkk
   ,sum(jlpkk) jlpkk
   ,to_char(round(sum(jlpkk)*100/(pd),2),'000.00') plpkk    
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec,a.no_kel     
    ,case when jenis_klmin in(1) then 1 end LKK
    ,case when jenis_klmin in(2) then 1 end PKK    
    ,case when jenis_klmin in(1,2) then 1 end JLPKK
    ,(select count(*) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0' and a.stat_hbkel in(1)) pd       
FROM biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0' and a.stat_hbkel=1
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop,pd),(no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd))
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);
	  
set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas1602 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) as NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) 
    else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
   ,sum(lkk) lkk
   ,to_char(round(sum(lkk)*100/(pd),2),'00.00') plkk
   ,sum(pkk) pkk
   ,to_char(round(sum(pkk)*100/(pd),2),'00.00') ppkk
   ,sum(jlpkk) jlpkk
   ,to_char(round(sum(jlpkk)*100/(pd),2),'000.00') plpkk    
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec     
    ,case when jenis_klmin in(1) then 1 end LKK
    ,case when jenis_klmin in(2) then 1 end PKK    
    ,case when jenis_klmin in(1,2) then 1 end JLPKK
    ,(select count(*) from biodata_wni a, data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0' and stat_hbkel in(1)) pd       
FROM biodata_wni a, data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0' and stat_hbkel=1
)
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd))
ORDER BY no_prop,no_kab,NO_KEC;
	  
set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas1701 NOLOGGING TABLESPACE SIAKDATA AS
select
    stat_kwn||'.' NKWN,case when stat_kwn is null then 'JUMLAH' else b.descrip end NMKWN
    ,sum(lk) lk
    ,to_char(round(sum(lk)*100/(pd),2),'00.00') plk
    ,sum(pr) pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00') ppr    
    ,sum(jduk) jduk
    ,to_char(round(sum(jduk)*100/(pd),2),'000.00') pduk        
    from
    (
    select
    a.no_prop,a.no_kab
    ,case when jenis_klmin in(1) then 1 end LK
    ,case when jenis_klmin in(2) then 1 end PR
    ,case when jenis_klmin in(1,2) then 1 end JDUK
    ,stat_kwn
    ,(select count(*) from biodata_wni a, data_keluarga b where a.no_kk=b.no_kk and a.stat_hbkel=1 and a.flag_status='0') pd
FROM biodata_wni  a, data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0' and a.stat_hbkel=1
)x
join ref_siak_wni b on x.stat_kwn=b.no and b.sect='601'
GROUP BY 
grouping sets((no_prop,no_kab,stat_kwn,b.descrip,pd)
,(pd)
)
ORDER BY stat_kwn;
	  
set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas1801 NOLOGGING TABLESPACE SIAKDATA AS
select 
    row_number() over(partition by no_kab order by no_kab) as urut
    ,case when umur is null then 'JUMLAH' else umur end umur
    ,sum(kbk) kbk
    ,to_char(round(sum(kbk)*100/(pd),2),'00.00') pkbk
    ,sum(ksk) ksk
    ,to_char(round(sum(ksk)*100/(pd),2),'00.00') pksk
    ,sum(kch) kch
    ,to_char(round(sum(kch)*100/(pd),2),'00.00') pkch    
    ,sum(kcm) kcm
    ,to_char(round(sum(kcm)*100/(pd),2),'00.00') pkcm    
    ,sum(jduk) jduk    
    ,to_char(round(sum(jduk)*100/(pd),2),'000.00') pjduk    
    from
    (
    select a.no_kab
    ,decode (floor (months_between (b.tgl_akhir,tgl_lhr) / 60)
    ,3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64'
    ,13, '65-69',14,'70-74','>=75') as UMUR
    ,case when stat_kwn in(1) then 1 else 0 end KBK
    ,case when stat_kwn in(2) then 1 else 0 end KSK
    ,case when stat_kwn in(3) then 1 else 0 end KCH    
    ,case when stat_kwn in(4) then 1 else 0 end KCM
    ,case when stat_kwn between 1 and 4 then 1 end jduk
    ,(select count(*) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.stat_hbkel=1 and a.flag_status='0') pd
    from biodata_wni a,tgl_aktif b,data_keluarga c where a.no_kk=c.no_kk and a.flag_status='0' and a.stat_hbkel in(1)
    and floor (months_between (b.tgl_akhir,tgl_lhr)/12)>=0 
)
group by 
grouping sets((umur,no_kab,pd)
,(no_kab,pd)
)
order by umur;
	  
set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas1901 NOLOGGING TABLESPACE SIAKDATA AS
select 
    row_number() over(partition by no_kab order by no_kab) as urut
    ,case when umur is null then 'JUMLAH' else umur end umur
    ,sum(kbkl) kbkl,sum(kbkp) kbkp
    ,to_char(round((sum(kbkl)+sum(kbkp))*100/(pd),2),'00.00') pkbk
    ,sum(kskl)kskl,sum(kskp)kskp
    ,to_char(round((sum(kskl)+sum(kskp))*100/(pd),2),'00.00') pksk
    ,sum(kchl)kchl,sum(kchp)kchp
    ,to_char(round((sum(kchl)+sum(kchp))*100/(pd),2),'00.00') pkch
    ,sum(kcml)kcml,sum(kcmp)kcmp
    ,to_char(round((sum(kcml)+sum(kcmp))*100/(pd),2),'00.00') pkcm    
    ,sum(jduk) jduk
    ,to_char(round(sum(jduk)*100/(pd),2),'000.00') pjduk    
    from
    (
    select a.no_kab
    ,decode (floor (months_between (b.tgl_akhir,tgl_lhr) / 60)
    ,3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64'
    ,13, '65-69',14,'70-74','>=75') as UMUR
    ,case when stat_kwn in(1) and jenis_klmin in(1) then 1 end KBKL
    ,case when stat_kwn in(1) and jenis_klmin in(2) then 1 end KBKP    
    ,case when stat_kwn in(2) and jenis_klmin in(1) then 1 end KSKL
    ,case when stat_kwn in(2) and jenis_klmin in(2) then 1 end KSKP    
    ,case when stat_kwn in(3) and jenis_klmin in(1) then 1 end KCHL
    ,case when stat_kwn in(3) and jenis_klmin in(2) then 1 end KCHP    
    ,case when stat_kwn in(4) and jenis_klmin in(1) then 1 end KCML
    ,case when stat_kwn in(4) and jenis_klmin in(2) then 1 end KCMP 
    ,case when stat_kwn in(1,2,3,4) then 1 end jduk   
    ,(select count(*) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.stat_hbkel=1 and a.flag_status='0') pd
    from biodata_wni a,tgl_aktif b,data_keluarga c where a.no_kk=c.no_kk and a.flag_status='0' and a.stat_hbkel in(1)
    and floor (months_between (b.tgl_akhir,tgl_lhr)/12)>=0 
)
group by 
grouping sets((umur,no_kab,pd)
,(no_kab,pd)
)
order by umur;
set termout off;
spool off;



--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2001 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
   ,sum (case when ag in(1) then 1 end) AG1
    ,sum(case when ag in(2) then 1 end) AG2
    ,sum(case when ag in(3) then 1 end) AG3
    ,sum(case when ag in(4) then 1 end) AG4
    ,sum(case when ag in(5) then 1 end) AG5
    ,sum(case when ag in(6) then 1 end) AG6
    ,sum(case when ag in(7) then 1 end) AG7
    ,sum(jagm) jagm,to_char(round(sum(jagm)*100/(pd),2),'000.00') pagm
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec,a.no_kel
    ,agama ag,case when agama between 1 and 7 then 1 end JAGM
    ,(select count(*) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0' and a.stat_hbkel in(1)) pd
FROM biodata_wni a,data_keluarga b WHERE a.no_kk=b.no_kk and a.flag_status='0'
					   and a.stat_hbkel in(1)
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop,pd),(no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd)
)
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2002 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) as NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end NMKEC
   ,sum (case when ag in(1) then 1 end) AG1
    ,sum(case when ag in(2) then 1 end) AG2
    ,sum(case when ag in(3) then 1 end) AG3
    ,sum(case when ag in(4) then 1 end) AG4
    ,sum(case when ag in(5) then 1 end) AG5
    ,sum(case when ag in(6) then 1 end) AG6
    ,sum(case when ag in(7) then 1 end) AG7
    ,sum(jagm) jagm,to_char(round(sum(jagm)*100/(pd),2),'000.00') pagm
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec
    ,agama ag,case when agama between 1 and 7 then 1 end JAGM
    ,(select count(*) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0' and a.stat_hbkel in(1)) pd
FROM biodata_wni a,data_keluarga b WHERE a.no_kk=b.no_kk and a.flag_status='0'
				 and a.stat_hbkel in(1)
)
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd)
)
ORDER BY no_prop,no_kab,NO_KEC;

set termout off;
spool off;



--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2003 NOLOGGING TABLESPACE SIAKDATA AS
select
    pddkn||'.' NPD,case when pddkn is null then 'JUMLAH' else b.descrip end NMPD
    ,sum(lk) lk
    ,to_char(round(sum(lk)*100/(pd),2),'00.00') plk
    ,sum(pr) pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00') ppr
    ,sum(jduk) jduk
    ,to_char(round(sum(jduk)*100/(pd),2),'000.00') pduk    
    from
    (
    select
    a.no_prop,a.no_kab,a.pddk_akh pddkn
    ,case when jenis_klmin in(1) then 1 end LK
    ,case when jenis_klmin in(2) then 1 end PR
    ,case when jenis_klmin in(1,2) then 1 end JDUK
    ,(select count(NIK) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.stat_hbkel=1 and a.flag_status='0') pd
from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and 
            a.flag_status='0' and
            a.Stat_hbkel=1
)x
join ref_siak_wni b on x.pddkn=b.no and b.sect='101'           
GROUP BY 
grouping sets((no_kab,pddkn,b.descrip,pd)
,(pd)
)
ORDER BY pddkn;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2004 NOLOGGING TABLESPACE SIAKDATA AS
select
    agama||'.' NAG
    ,case when agama is null then 'JUMLAH' else b.descrip end  NMAG
    ,sum (lk) LK
    ,to_char(round(sum(lk)*100/(pd),2),'00.00')PLK    
    ,sum (pr) pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00')PPR    
    ,sum (jduk) JDUK
    ,to_char(round(sum(jduk)*100/(pd),2),'000.00')PDUK
from
(
select
    a.no_prop,a.no_kab,a.no_kec
    ,case when jenis_klmin in(1) then 1 end lk
    ,case when jenis_klmin in(2) then 1 end pr
    ,case when jenis_klmin in(1,2) then 1 end jduk        
    ,agama agama
    ,(select count(*) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0' and a.stat_hbkel in(1)) pd
  FROM biodata_wni a,data_keluarga b where a.no_kk=b.no_kk
				   and a.flag_status='0'
  				   and a.stat_hbkel in(1) 	 
)x
join ref_siak_wni b on x.agama=b.no and b.sect='501' 
GROUP BY 
grouping sets((no_prop,no_kab,agama,b.descrip,pd)
,(no_kab,pd)
)
ORDER BY agama;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2101 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    pkrjn||'.' NPD,case when pkrjn is null then 'JUMLAH' else b.descrip end NMPD
    ,sum(lk) lk
    ,to_char(round(sum(lk)*100/(pd),2),'00.00') plk
    ,sum(pr) pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00') ppr
    ,sum(jduk) jduk
    ,to_char(round(sum(jduk)*100/(pd),2),'000.00') pduk    
    from
    (
    select
    a.no_prop,a.no_kab,a.jenis_pkrjn pkrjn
    ,case when jenis_klmin in(1) then 1 end LK
    ,case when jenis_klmin in(2) then 1 end PR
    ,case when jenis_klmin in(1,2) then 1 end JDUK
    ,(select count(NIK) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.stat_hbkel=1 and a.flag_status='0') pd
FROM biodata_wni a,data_keluarga b where
			a.no_kk=b.no_kk and 
            flag_status='0' and
            Stat_hbkel=1
)x
join ref_siak_wni b on x.pkrjn=b.no and b.sect='201'           
GROUP BY 
grouping sets((no_kab,pkrjn,b.descrip,pd)
,(pd)
)
ORDER BY pkrjn;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2201 NOLOGGING TABLESPACE SIAKDATA AS
select
    pddkn||'.' NPD,case when pddkn is null then 'JUMLAH' else b.descrip end NMPD
    ,sum(lk) lk
    ,to_char(round(sum(lk)*100/(pd),2),'00.00') plk
    ,sum(pr) pr
    ,to_char(round(sum(pr)*100/(pd),2),'00.00') ppr
    ,sum(jduk) jduk
    ,to_char(round(sum(jduk)*100/(pd),2),'000.00') pduk    
    from
    (
    select
    a.no_prop,a.no_kab,a.pddk_akh pddkn
    ,case when jenis_klmin in(1) then 1 end LK
    ,case when jenis_klmin in(2) then 1 end PR
    ,case when jenis_klmin in(1,2) then 1 end JDUK
    ,(select count(NIK) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.stat_hbkel=1 and a.flag_status='0') pd
from biodata_wni a,data_keluarga b where
			a.no_kk=b.no_kk and
            a.flag_status='0' and
            a.Stat_hbkel=1
)x
join ref_siak_wni b on x.pddkn=b.no and b.sect='101'           
GROUP BY 
grouping sets((no_kab,pddkn,b.descrip,pd)
,(pd)
)
ORDER BY pddkn;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2202 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum (case when pdkn in(6) and jk in(1) then 1 end) lpd6
    ,sum (case when pdkn in(6) and jk in(2) then 1 end) ppd6
    ,sum (case when pdkn in(6) and jk in(1,2) then 1 end) jpd6
    ,sum (case when pdkn in(7) and jk in(1) then 1 end) lpd7
    ,sum (case when pdkn in(7) and jk in(2) then 1 end) ppd7
    ,sum (case when pdkn in(7) and jk in(1,2) then 1 end) jpd7
    ,sum (case when pdkn in(8) and jk in(1) then 1 end) lpd8
    ,sum (case when pdkn in(8) and jk in(2) then 1 end) ppd8
    ,sum (case when pdkn in(8) and jk in(1,2) then 1 end) jpd8    
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec
    ,case when jenis_klmin in(1) then 1 else 2 end jk
    ,case when jenis_klmin in(1,2) then 1 end jduk
    ,pddk_akh pdkn        
FROM biodata_wni a,data_keluarga b WHERE a.no_kk=b.no_kk
			and a.flag_status='0' 
                            and a.pddk_akh between 6 and 8
                            and a.stat_hbkel in(1)
)
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop),(no_kab,no_prop)
)
ORDER BY no_prop,no_kab,NO_KEC;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2203 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum (case when pdkn in(9) and jk in(1) then 1 end) lpd9
    ,sum (case when pdkn in(9) and jk in(2) then 1 end) ppd9
    ,sum (case when pdkn in(9) and jk in(1,2) then 1 end) jpd9
    ,sum (case when pdkn in(10) and jk in(1) then 1 end) lpd10
    ,sum (case when pdkn in(10) and jk in(2) then 1 end) ppd10
    ,sum (case when pdkn in(10) and jk in(1,2) then 1 end) jpd10
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec
    ,case when jenis_klmin in(1) then 1 else 2 end jk
    ,case when jenis_klmin in(1,2) then 1 end jduk
    ,a.pddk_akh pdkn        
FROM biodata_wni a,data_keluarga b WHERE a.no_kk=b.no_kk
				and a.flag_status='0' 
				and a.pddk_akh between 9 and 10
				and a.stat_hbkel in(1)
)
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop),(no_kab,no_prop)
)
ORDER BY no_prop,no_kab,NO_KEC;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2204 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum (case when pdkn in(1) and jk in(1) then 1 end) lpd1
    ,sum (case when pdkn in(1) and jk in(2) then 1 end) ppd1
    ,sum (case when pdkn in(1) and jk in(1,2) then 1 end) jpd1
    ,sum (case when pdkn in(2) and jk in(1) then 1 end) lpd2
    ,sum (case when pdkn in(2) and jk in(2) then 1 end) ppd2
    ,sum (case when pdkn in(2) and jk in(1,2) then 1 end) jpd2
    ,sum (case when pdkn in(3) and jk in(1) then 1 end) lpd3
    ,sum (case when pdkn in(3) and jk in(2) then 1 end) ppd3
    ,sum (case when pdkn in(3) and jk in(1,2) then 1 end) jpd3
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec
    ,case when jenis_klmin in(1) then 1 else 2 end jk
    ,case when jenis_klmin in(1,2) then 1 end jduk
    ,a.pddk_akh pdkn        
FROM biodata_wni a,data_keluarga b WHERE a.no_kk=b.no_kk
							and a.flag_status='0' 
                            and a.pddk_akh between 1 and 3
                            and a.stat_hbkel in(1)
)
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop),(no_kab,no_prop)
)
ORDER BY no_prop,no_kab,NO_KEC;

set termout off;
spool off;

--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2205 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum (case when pdkn in(4) and jk in(1) then 1 end) lpd4
    ,sum (case when pdkn in(4) and jk in(2) then 1 end) ppd4
    ,sum (case when pdkn in(4) and jk in(1,2) then 1 end) jpd4
    ,sum (case when pdkn in(5) and jk in(1) then 1 end) lpd5
    ,sum (case when pdkn in(5) and jk in(2) then 1 end) ppd5
    ,sum (case when pdkn in(5) and jk in(1,2) then 1 end) jpd5
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec
    ,case when jenis_klmin in(1) then 1 else 2 end jk
    ,case when jenis_klmin in(1,2) then 1 end jduk
    ,a.pddk_akh pdkn        
FROM biodata_wni a,data_keluarga b WHERE a.no_kk=b.no_kk
							and a.flag_status='0' 
                            and a.pddk_akh between 4 and 5
                            and a.stat_hbkel in(1)
)
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop),(no_kab,no_prop)
)
ORDER BY no_prop,no_kab,NO_KEC;

set termout off;
spool off;





--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2206 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum (case when pdkn in(6) and jk in(1) then 1 end) lpd6
    ,sum (case when pdkn in(6) and jk in(2) then 1 end) ppd6
    ,sum (case when pdkn in(6) and jk in(1,2) then 1 end) jpd6
    ,sum (case when pdkn in(7) and jk in(1) then 1 end) lpd7
    ,sum (case when pdkn in(7) and jk in(2) then 1 end) ppd7
    ,sum (case when pdkn in(7) and jk in(1,2) then 1 end) jpd7
    ,sum (case when pdkn in(8) and jk in(1) then 1 end) lpd8
    ,sum (case when pdkn in(8) and jk in(2) then 1 end) ppd8
    ,sum (case when pdkn in(8) and jk in(1,2) then 1 end) jpd8    
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec,a.no_kel
    ,case when jenis_klmin in(1) then 1 else 2 end jk
    ,case when jenis_klmin in(1,2) then 1 end jduk
    ,a.pddk_akh pdkn        
FROM biodata_wni a,data_keluarga b WHERE a.no_kk=b.no_kk
				and a.flag_status='0' 
				and a.pddk_akh between 6 and 8
				and a.stat_hbkel in(1)
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop),(no_kec,no_kab,no_prop),(no_kab,no_prop)
)
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;

--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2207 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum (case when pdkn in(9) and jk in(1) then 1 end) lpd9
    ,sum (case when pdkn in(9) and jk in(2) then 1 end) ppd9
    ,sum (case when pdkn in(9) and jk in(1,2) then 1 end) jpd9
    ,sum (case when pdkn in(10) and jk in(1) then 1 end) lpd10
    ,sum (case when pdkn in(10) and jk in(2) then 1 end) ppd10
    ,sum (case when pdkn in(10) and jk in(1,2) then 1 end) jpd10
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec,a.no_kel
    ,case when jenis_klmin in(1) then 1 else 2 end jk
    ,case when jenis_klmin in(1,2) then 1 end jduk
    ,a.pddk_akh pdkn        
FROM biodata_wni a,data_keluarga b WHERE a.no_kk=b.no_kk
				and a.flag_status='0' 
				and a.pddk_akh between 9 and 10
				and a.stat_hbkel in(1)
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop),(no_kec,no_kab,no_prop),(no_kab,no_prop)
)
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2208 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum (case when pdkn in(1) and jk in(1) then 1 end) lpd1
    ,sum (case when pdkn in(1) and jk in(2) then 1 end) ppd1
    ,sum (case when pdkn in(1) and jk in(1,2) then 1 end) jpd1
    ,sum (case when pdkn in(2) and jk in(1) then 1 end) lpd2
    ,sum (case when pdkn in(2) and jk in(2) then 1 end) ppd2
    ,sum (case when pdkn in(2) and jk in(1,2) then 1 end) jpd2
    ,sum (case when pdkn in(3) and jk in(1) then 1 end) lpd3
    ,sum (case when pdkn in(3) and jk in(2) then 1 end) ppd3
    ,sum (case when pdkn in(3) and jk in(1,2) then 1 end) jpd3
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec,a.no_kel
    ,case when jenis_klmin in(1) then 1 else 2 end jk
    ,case when jenis_klmin in(1,2) then 1 end jduk
    ,a.pddk_akh pdkn        
FROM biodata_wni a,data_keluarga b WHERE a.no_kk=b.no_kk 
				and a.flag_status='0' 
				and a.pddk_akh between 1 and 3
				and a.stat_hbkel in(1)						
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop),(no_kec,no_kab,no_prop),(no_kab,no_prop)
)
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2209 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum (case when pdkn in(4) and jk in(1) then 1 end) lpd4
    ,sum (case when pdkn in(4) and jk in(2) then 1 end) ppd4
    ,sum (case when pdkn in(4) and jk in(1,2) then 1 end) jpd4
    ,sum (case when pdkn in(5) and jk in(1) then 1 end) lpd5
    ,sum (case when pdkn in(5) and jk in(2) then 1 end) ppd5
    ,sum (case when pdkn in(5) and jk in(1,2) then 1 end) jpd5
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec,a.no_kel
    ,case when jenis_klmin in(1) then 1 else 2 end jk
    ,case when jenis_klmin in(1,2) then 1 end jduk
    ,a.pddk_akh pdkn        
FROM biodata_wni a,data_keluarga b WHERE a.no_kk=b.no_kk
				and a.flag_status='0' 
				 and a.pddk_akh between 4 and 5
				 and a.stat_hbkel in(1)
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop),(no_kec,no_kab,no_prop),(no_kab,no_prop))
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2301 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||
	trim(to_char(no_kab,'00'))||
	trim(to_char(no_kec,'00'))||
	trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
     case when 
     getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
     getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum(sckk) sckk
    ,to_char(round(sum(sckk)*100/(pd),2),'00.00') psckk
    ,sum(bckk) bckk
    ,to_char(round(sum(bckk)*100/(pd),2),'00.00') pbckk
    ,sum(jduk) jduk
    ,to_char(round(sum(jduk)*100/(pd),2),'000.00') pduk    
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec,a.no_kel
    ,case when a.pflag='Y' then 1 end SCKK
    ,case when a.pflag='N' then 1 end BCKK   
    ,case when a.NO_KK IS NOT NULL then 1 end JDUK    
    ,(select count(*) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0' and a.stat_hbkel=1) pd
FROM data_keluarga a inner join biodata_wni b on a.no_kk=b.no_kk 
        and b.flag_status='0'
        and b.stat_hbkel=1
)        
GROUP BY
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop,pd),(no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd))
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;

--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2302 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop)  else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum(sckk) sckk
    ,to_char(round(sum(sckk)*100/(pd),2),'00.00') psckk
    ,sum(bckk) bckk
    ,to_char(round(sum(bckk)*100/(pd),2),'00.00') pbckk
    ,sum(jduk) jduk
    ,to_char(round(sum(jduk)*100/(pd),2),'000.00') pduk    
    from
    (
    select
    a.no_prop,a.no_kab,a.no_kec
    ,case when a.pflag='Y' then 1 end SCKK
    ,case when a.pflag='N' then 1 end BCKK   
    ,case when a.pflag in('Y','N') then 1 end JDUK    
    ,(select count(*) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.flag_status='0' and a.stat_hbkel=1) pd
FROM data_keluarga a inner join biodata_wni b on a.no_kk=b.no_kk 
        and b.flag_status='0'
        and b.stat_hbkel=1
)        
GROUP BY
GROUPING SETS((no_prop,no_kab,no_kec,pd),(no_prop,no_kab,pd))
ORDER BY no_kab,no_kec;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2401 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum(sckkl) sckkl
    ,to_char(round(sum(sckkl)*100/(pd),2),'00.00') psckkl
    ,sum(sckkp) sckkp
    ,to_char(round(sum(sckkp)*100/(pd),2),'00.00') psckkp    
    ,sum(jsckk) jsckk
    ,to_char(round(sum(jsckk)*100/(pd),2),'00.00') pjsckk    
    ,sum(bckkl) bckkl
    ,to_char(round(sum(bckkl)*100/(pd),2),'00.00') pbckkl    
    ,sum(bckkp) bckkp
    ,to_char(round(sum(bckkp)*100/(pd),2),'00.00') pbckkp    
    ,sum(jbckk) jbckk
    ,to_char(round(sum(jbckk)*100/(pd),2),'00.00') pjbckk    
    ,sum(jkkduk) jkkduk
    ,to_char(round(sum(jkkduk)*100/(pd),2),'000.00') pjkkduk        
    from 
    (
    select
    a.no_prop,a.no_kab,a.no_kec,a.no_kel
    ,case when b.pflag in('Y') and a.jenis_klmin in(1) then 1 end SCKKL
    ,case when b.pflag in('Y') and a.jenis_klmin in(2) then 1 end SCKKP    
    ,case when b.pflag in('Y') then 1 end JSCKK    
    ,case when b.pflag in('N') and a.jenis_klmin in(1) then 1 end BCKKL    
    ,case when b.pflag in('N') and a.jenis_klmin in(2) then 1 end BCKKP
    ,case when b.pflag in('N') then 1 end JBCKK    
    ,case when b.no_kk is not null then 1 end JKKDUK
    ,(select count(*) from biodata_wni a,data_keluarga b where a.no_kk=b.no_kk and a.stat_hbkel=1 and a.flag_status='0') pd
FROM biodata_wni a, data_keluarga b
WHERE 1=1 and a.no_kk=b.no_kk and stat_hbkel=1 and flag_status='0'
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop,pd),(no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd))
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;

--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2402 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum(sckkl) sckkl
    ,to_char(round(sum(sckkl)*100/(pd),2),'00.00') psckkl
    ,sum(sckkp) sckkp
    ,to_char(round(sum(sckkp)*100/(pd),2),'00.00') psckkp    
    ,sum(jsckk) jsckk
    ,to_char(round(sum(jsckk)*100/(pd),2),'00.00') pjsckk    
    ,sum(bckkl) bckkl
    ,to_char(round(sum(bckkl)*100/(pd),2),'00.00') pbckkl    
    ,sum(bckkp) bckkp
    ,to_char(round(sum(bckkp)*100/(pd),2),'00.00') pbckkp    
    ,sum(jbckk) jbckk
    ,to_char(round(sum(jbckk)*100/(pd),2),'00.00') pjbckk    
    ,sum(jkkduk) jkkduk
    ,to_char(round(sum(jkkduk)*100/(pd),2),'000.00') pjkkduk        
    from 
    (
    select
    a.no_prop,a.no_kab,a.no_kec
    ,case when b.pflag in('Y') and a.jenis_klmin in(1) then 1 end SCKKL
    ,case when b.pflag in('Y') and a.jenis_klmin in(2) then 1 end SCKKP    
    ,case when b.pflag in('Y') then 1 end JSCKK    
    ,case when b.pflag in('N') and a.jenis_klmin in(1) then 1 end BCKKL    
    ,case when b.pflag in('N') and a.jenis_klmin in(2) then 1 end BCKKP
    ,case when b.pflag in('N') then 1 end JBCKK    
    ,case when b.no_kk is not null then 1 end JKKDUK
    ,(select count(*) from biodata_wni a,data_keluarga b  where a.no_kk=b.no_kk and a.stat_hbkel=1 and a.flag_status='0') pd
FROM biodata_wni a, data_keluarga b
WHERE 1=1 and a.no_kk=b.no_kk and stat_hbkel=1 and flag_status='0'
)
GROUP BY 
GROUPING SETS((no_prop,no_kab,no_kec,pd),(no_prop,no_kab,pd))
ORDER BY no_kab,no_kec;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2501 NOLOGGING TABLESPACE SIAKDATA AS
select
      trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00'))NOKEC
     ,case when 
     getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
     getnamakab(no_kab,no_prop) else 
     getnamakab(no_kab,no_prop) end else 
     getnamakec(no_kec,no_kab,no_prop) end NMKEC
    ,sum (case when flag=1 and jk=1 then 1 end) scktpl
    ,sum (case when flag=1 and jk=2 then 1 end) scktpp
    ,to_char(round(sum(case when flag=1 then 1 end)*100/(pd),2),'00.00') pscktpp
    ,sum (case when flag=2 and jk=1 then 1 end) bcktpl
    ,sum (case when flag=2 and jk=2 then 1 end) bcktpp
    ,to_char(round(sum(case when flag=2 then 1 end)*100/(pd),2),'00.00') pbcktpp
    ,sum (case when jk=1 then 1 end) jwktpl
    ,sum (case when jk=2 then 1 end) jwktpp
    ,sum (case when jk in(1,2) then 1 end) jwktp
    ,to_char(round(sum (case when jk in(1,2) then 1 end)*100/(pd),2),'000.00') pjwktp
     from
     (        
    select
     a.no_prop,a.no_kab,a.no_kec     
     ,case when jenis_klmin='1' then 1 else 2 end jk
     ,case when flag_ektp='1' then 1 else 2 end flag
     ,(select count(*) from biodata_wni a,tgl_aktif b,data_keluarga c where a.no_kk=c.no_kk and (floor(months_between(b.tgl_akhir,TGL_LHR)/12)>=17 OR stat_kwn>1) and flag_status='0') pd
FROM biodata_wni a,tgl_aktif b,data_keluarga c
WHERE a.no_kk=c.no_kk and a.flag_status=0 and (floor(months_between(b.tgl_akhir,TGL_LHR)/12)>=17 OR stat_kwn>1)
)
GROUP BY 
GROUPING SETS((no_prop,no_kab,no_kec,pd)
,(no_prop,no_kab,pd)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2502 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum (case when flag=1 and jk=1 then 1 end) scktpl
    ,sum (case when flag=1 and jk=2 then 1 end) scktpp
    ,to_char(round(sum(case when flag=1 then 1 end)*100/(pd),2),'00.00') pscktpp
    ,sum (case when flag=2 and jk=1 then 1 end) bcktpl
    ,sum (case when flag=2 and jk=2 then 1 end) bcktpp
    ,to_char(round(sum(case when flag=2 then 1 end)*100/(pd),2),'00.00') pbcktpp
    ,sum (case when jk=1 then 1 end) jwktpl
    ,sum (case when jk=2 then 1 end) jwktpp
    ,sum (case when jk in(1,2) then 1 end) jwktp
    ,to_char(round(sum (case when jk in(1,2) then 1 end)*100/(pd),2),'000.00') pjwktp
     from
     (        
    select
     a.no_prop,a.no_kab,a.no_kec,a.no_kel     
     ,case when jenis_klmin='1' then 1 else 2 end jk
     ,case when flag_ektp='1' then 1 else 2 end flag
     ,(select count(*) from biodata_wni a,tgl_aktif b,data_keluarga c where a.no_kk=c.no_kk and (floor(months_between(b.tgl_akhir,TGL_LHR)/12)>=17 OR stat_kwn>1) and flag_status='0') pd
FROM biodata_wni a,tgl_aktif b,data_keluarga c
WHERE a.no_kk=c.no_kk and a.flag_status=0 and (floor(months_between(b.tgl_akhir,TGL_LHR)/12)>=17 OR stat_kwn>1)
)
GROUP BY 
GROUPING SETS ((no_kel,no_kec,no_kab,no_prop,pd),(no_kec,no_kab,no_prop,pd),(no_kab,no_prop,pd))
ORDER BY no_prop,no_kab,NO_KEC,substr(no_kel,2,3);

set termout off;
spool off;

--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2601 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00'))NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum(lnkk) lnkk
    ,sum(pnkk) pnkk
    ,sum(jnkk) jnkk
    ,to_char(round(sum(jnkk)*100/(pd),2),'000.00') pjnkk
    from
    (
    select
    b.no_prop,b.no_kab,b.no_kec
    ,case when a.jenis_klmin in(1) then 1 end LNKK
    ,case when a.jenis_klmin in(2) then 1 end PNKK
    ,case when a.jenis_klmin in(1,2) then 1 end JNKK
    ,(select count(a.nik) from biodata_wni a,numpang_kk_detail b,tgl_aktif c where a.nik=b.nik_anggota and to_char(b.created_date,'yyyy')=to_char(c.tgl_akhir,'yyyy')) pd 
FROM 
biodata_wni a,numpang_kk_detail b,tgl_aktif c
WHERE
a.nik=b.nik_anggota
and to_char(b.created_date,'yyyy')=to_char(c.tgl_akhir,'yyyy')
)
GROUP BY 
GROUPING SETS((no_kec,no_kab,no_prop,pd)
,(no_kab,no_prop,pd)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool off;

--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2602 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00'))NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum(lpkk) lpkk
    ,sum(ppkk) ppkk
    ,sum(jpkk) jpkk
    ,to_char(round(sum(jpkk)*100/(pd),2),'000.00') pjpkk
    from
    (
    select
    b.no_prop,b.no_kab,b.no_kec
    ,case when jenis_klmin in(1) then 1 end LPKK
    ,case when jenis_klmin in(2) then 1 end PPKK
    ,case when jenis_klmin in(1,2) then 1 end JPKK
    ,(select count(no_kk) from biodata_wni a,pisah_kk b,tgl_aktif c where a.no_kk=b.baru_no_kk and a.stat_hbkel=1 and to_char(b.created_date,'yyyy')=to_char(c.tgl_akhir,'yyyy')) pd
FROM 
biodata_wni a,pisah_kk b,tgl_aktif c
WHERE
a.no_kk=b.baru_no_kk
and a.stat_hbkel=1
and to_char(b.created_date,'yyyy')=to_char(c.tgl_akhir,'yyyy')
)
GROUP BY 
GROUPING SETS((no_kec,no_kab,no_prop,pd)
,(no_kab,no_prop,pd)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2701 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||trim(to_char(no_kab,'00'))||trim(to_char(no_kec,'00'))||trim(to_char(no_kel,'0000')) as NOKEC
    ,case when 
    getnamakel(no_kel,no_kec,no_kab,no_prop) is not null then 
    getnamakel(no_kel,no_kec,no_kab,no_prop) else 
    case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 'KEC. '||
    getnamakec(no_kec,no_kab,no_prop) end end NMKEC
    ,sum(lsakta) lsakta
    ,sum(psakta) psakta
    ,sum(jsakta) jsakta
    ,to_char(round(sum(jsakta)*100/(sum(jduk)),2),'00.00') ppsakta    
    ,sum(lbakta) lbakta
    ,sum(pbakta) pbakta
    ,sum(jbakta) jbakta    
    ,to_char(round(sum(jbakta)*100/(sum(jduk)),2),'00.00') ppbakta
    ,sum(jduk) jduk
    from
    (
    select
    no_prop,no_kab,no_kec,no_kel    
    ,case when akta_lhr in(2) and jenis_klmin=1 then 1 end LSAKTA
    ,case when akta_lhr in(2) and jenis_klmin=2 then 1 end PSAKTA
    ,case when akta_lhr in(2) then 1 end JSAKTA        
    ,case when akta_lhr in(1) and jenis_klmin=1 then 1 end LBAKTA
    ,case when akta_lhr in(1) and jenis_klmin=2 then 1 end PBAKTA
    ,case when akta_lhr in(1) then 1 end JBAKTA       
    ,case when jenis_klmin in(1,2) then 1 end JDUK
FROM biodata_wni a, tgl_aktif b where 1=1 
and flag_status='0'
and floor(months_between(b.tgl_akhir,tgl_lhr)/12) between 0 and 18
)
GROUP BY 
GROUPING SETS((no_prop,no_kab,no_kec,no_kel)
,(no_prop,no_kab,no_kec)
,(no_prop,no_kab)
)
ORDER BY no_prop,no_kab,no_kec,no_kel;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2702 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum(lsakta) lsakta
    ,sum(psakta) psakta
    ,to_char(round((sum(psakta)+sum(lsakta))*100/(pd),2),'00.00') ppsakta    
    ,sum(lbakta) lbakta
    ,sum(pbakta) pbakta
    ,to_char(round((sum(pbakta)+sum(lbakta))*100/(pd),2),'00.00') ppbakta
    ,sum(jlakta) jlakta
    ,sum(jpakta) jpakta
    ,sum(jduk) jduk
    ,to_char(round(sum(jduk)*100/(pd),2),'000.00') pjduk
    from
    (
    select
    no_prop,no_kab,no_kec    
    ,case when akta_lhr in(2) and jenis_klmin=1 then 1 end LSAKTA
    ,case when akta_lhr in(2) and jenis_klmin=2 then 1 end PSAKTA
    ,case when akta_lhr in(1) and jenis_klmin=1 then 1 end LBAKTA
    ,case when akta_lhr in(1) and jenis_klmin=2 then 1 end PBAKTA
    ,(select count(NIK) from biodata_wni where flag_status='0') pd   
    ,case when jenis_klmin in(1) then 1 end JLAKTA
    ,case when jenis_klmin in(2) then 1 end JPAKTA
    ,case when jenis_klmin in(1,2) then 1 end JDUK
FROM biodata_wni where flag_status='0'
)
GROUP BY 
GROUPING SETS((no_prop,no_kab,no_kec,pd)
,(no_prop,no_kab,pd)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2703 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00')) NOKEC
    ,case when 
    getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
    getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum(lsakta) lsakta
    ,sum(psakta) psakta
    ,sum(jsakta) jsakta
    ,to_char(round(sum(jsakta)*100/(sum(jduk)),2),'00.00') ppsakta    
    ,sum(lbakta) lbakta
    ,sum(pbakta) pbakta
    ,sum(jbakta) jbakta    
    ,to_char(round(sum(jbakta)*100/(sum(jduk)),2),'00.00') ppbakta
    ,sum(jduk) jduk
    from
    (
    select
    no_prop,no_kab,no_kec    
    ,case when akta_lhr in(2) and jenis_klmin=1 then 1 end LSAKTA
    ,case when akta_lhr in(2) and jenis_klmin=2 then 1 end PSAKTA
    ,case when akta_lhr in(2) then 1 end JSAKTA        
    ,case when akta_lhr in(1) and jenis_klmin=1 then 1 end LBAKTA
    ,case when akta_lhr in(1) and jenis_klmin=2 then 1 end PBAKTA
    ,case when akta_lhr in(1) then 1 end JBAKTA       
    ,case when jenis_klmin in(1,2) then 1 end JDUK
FROM biodata_wni a, tgl_aktif b where 1=1 
and flag_status='0'
and floor(months_between(b.tgl_akhir,tgl_lhr)/12) between 0 and 18
)
GROUP BY 
GROUPING SETS((no_prop,no_kab,no_kec)
,(no_prop,no_kab)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2801 NOLOGGING TABLESPACE SIAKDATA AS
select
row_number() over(partition by 1 order by 1) as urut
,umur,sum(alak)alak,sum(blak)blak,sum(lkak)lkak
,sum(apak)apak,sum(bpak)bpak,sum(pkak)pkak,sum(jak)jak
from
(
select
no_prop,no_kab
,case when age between 0 and 01 then ' 00-01' 
      when age between 2 and 05 then '>01-05'
      when age between 6 and 18 then '>05-18'
      else '>18+' end umur
,case when akta in(2) and jk in(1) then 1 end ALAK
,case when akta in(1) and jk in(1) then 1 end BLAK
,case when akta in(1,2) and jk in(1) then 1 end LKAK
,case when akta in(2) and jk in(2) then 1 end APAK
,case when akta in(1) and jk in(2) then 1 end BPAK
,case when akta in(1,2) and jk in(2) then 1 end PKAK
,case when akta in(1,2) then 1 end as JAK
from
(
select
 no_prop,no_kab,no_kec
,jenis_klmin  jk
,akta_lhr akta
,floor(months_between(b.tgl_akhir,tgl_lhr)/12) age
from biodata_wni a,tgl_aktif b where flag_status='0'
)
where age>=0
)
group by 
grouping sets((umur)
,()
)
order by urut;

set termout off;
spool off;



--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas2901 NOLOGGING TABLESPACE SIAKDATA AS
select
     row_number() over(partition by no_kab order by no_kab) as urut
     ,umur
     ,sum(lsak)lsak
     ,sum(psak)psak
     ,to_char(round((sum(psak)+sum(lsak))*100/(pd),2),'00.00') ppsak     
     ,sum(lbak)lbak    
     ,sum(pbak)pbak
     ,to_char(round((sum(pbak)+sum(lbak))*100/(pd),2),'00.00') ppbak     
     ,sum(jlk)jlk
     ,sum(jpr)jpr
     ,sum(jduk) jduk
     ,to_char(round(sum(jduk)*100/(pd),2),'000.00') pjduk     
from 
(
select
    no_prop,no_kab,no_kec
    ,decode (floor ((months_between (sysdate, tgl_lhr) / 60))
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29'
    ,6, '30-34',7, '35-39',8, '40-44',9, '45-49',10, '50-54',11, '55-59'
    ,12, '60-64',13, '65-69',14, '70-74','75>+')  UMUR
    ,case when akta_lhr in(2) and jenis_klmin=1 then 1 end LSAK
    ,case when akta_lhr in(2) and jenis_klmin=2 then 1 end PSAK
    ,case when akta_lhr in(1) and jenis_klmin=1 then 1 end LBAK
    ,case when akta_lhr in(1) and jenis_klmin=2 then 1 end PBAK       
    ,case when jenis_klmin in(1) then 1 end JLK
    ,case when jenis_klmin in(2) then 1 end JPR
    ,case when jenis_klmin in(1,2)  then 1 end JDUK
    ,(select count(*) from biodata_wni where flag_status='0')  pd
FROM biodata_wni a,tgl_aktif b
WHERE 1=1 and  flag_status='0'
and floor(months_between(b.tgl_akhir,tgl_lhr)/12)>=0
)
GROUP BY 
GROUPING SETS((no_kab,no_prop,umur,pd)
,(no_kab,pd)
)    
ORDER BY no_kab,UMUR;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3001 NOLOGGING TABLESPACE SIAKDATA AS
SELECT  
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00'))NOKEC
   ,case when 
   getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
   getnamakab(no_kab,no_prop) else 
   getnamakab(no_kab,no_prop) end else 
   getnamakec(no_kec,no_kab,no_prop) end nmkec
  ,sum(lsak) lsak
  ,sum(psak) psak
  ,to_char(round((sum(psak)+sum(lsak))*100/(pd),2),'00.00') ppsak
  ,sum(lbak) lbak 
  ,sum(pbak) pbak
  ,to_char(round((sum(pbak)+sum(lbak))*100/(pd),2),'00.00') ppbak  
  ,sum(jlk) jlk
  ,sum(jpr) jpr
  ,sum(jduk) jduk
  ,to_char(round(sum(jduk)*100/(pd),2),'000.00') pjduk
from
(
select
   no_kec,no_kab,no_prop
   ,case when tgl_kwn is not null and jenis_klmin=1 then 1 end LSAK
   ,case when tgl_kwn is not null and akta_kwn in(2) and jenis_klmin=2 then 1 end PSAK   
   ,case when tgl_kwn is null and akta_kwn in(1) and jenis_klmin=1 then 1 end LBAK
   ,case when tgl_kwn is null and akta_kwn in(1) and jenis_klmin=2 then 1 end PBAK   
   ,case when jenis_klmin in(1) then 1 end JLK
   ,case when jenis_klmin in(2) then 1 end  JPR
   ,case when jenis_klmin in(1,2) then 1 end  JDUK
   ,(select count(nik) from biodata_wni where stat_kwn in(2) and flag_status='0' and stat_hbkel in(1)) pd

FROM biodata_wni
WHERE stat_kwn in(2) and flag_status in('0') and stat_hbkel in(1)
)
GROUP BY 
GROUPING SETS((no_prop,no_kab,no_kec,pd)
,(no_prop,no_kab,pd)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3002 NOLOGGING TABLESPACE SIAKDATA AS
SELECT  
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00'))NOKEC
   ,case when 
   getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
   getnamakab(no_kab,no_prop) else 
   getnamakab(no_kab,no_prop) end else 
   getnamakec(no_kec,no_kab,no_prop) end nmkec
  ,sum(lsak) lsak
  ,sum(psak) psak
  ,to_char(round((sum(psak)+sum(lsak))*100/(pd),2),'00.00') ppsak
  ,sum(lbak) lbak 
  ,sum(pbak) pbak
  ,to_char(round((sum(pbak)+sum(lbak))*100/(pd),2),'00.00') ppbak  
  ,sum(jlk) jlk
  ,sum(jpr) jpr
  ,sum(jduk) jduk
  ,to_char(round(sum(jduk)*100/(pd),2),'000.00') pjduk
from
(
select
   no_kec,no_kab,no_prop
   ,case when tgl_kwn is not null and jenis_klmin=1 then 1 end LSAK
   ,case when tgl_kwn is not null and jenis_klmin=2 then 1 end PSAK   
   ,case when tgl_kwn is null and jenis_klmin=1 then 1 end LBAK
   ,case when tgl_kwn is null and jenis_klmin=2 then 1 end PBAK   
   ,case when jenis_klmin in(1) then 1 end JLK
   ,case when jenis_klmin in(2) then 1 end  JPR
   ,case when jenis_klmin in(1,2) then 1 end  JDUK
   ,(select count(nik) from biodata_wni where stat_kwn=2 and flag_status='0') pd
FROM biodata_wni
WHERE stat_kwn=2 and flag_status=0
)
GROUP BY 
GROUPING SETS((no_prop,no_kab,no_kec,pd)
,(no_prop,no_kab,pd)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3101 NOLOGGING TABLESPACE SIAKDATA AS
select
    row_number() over(partition by no_kab order by no_kab) as urut
    ,case when umur is null then 'JUMLAH' else umur end umur
    ,sum(lsak) LSAK,sum(psak) PSAK
    ,to_char(round((sum(psak)+sum(lsak))*100/(pd),2),'00.00') ppsak
    ,sum(lbak) LBAK,sum(pbak) PBAK
    ,to_char(round((sum(pbak)+sum(lbak))*100/(pd),2),'00.00') ppbak    
    ,sum(jlk)  JLK,sum(jpr) JPR
    ,sum(jduk) JDUK,to_char(round(sum(jduk)*100/(pd),2),'000.00') pjduk
    from
    (
    select 
    no_kec,no_kab,no_prop
    ,decode (floor ((months_between (sysdate, tgl_lhr) / 60))
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39',
    8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','75>+')  UMUR
   ,case when akta_kwn in(2) and jenis_klmin=1 then 1 end LSAK
   ,case when akta_kwn in(2) and jenis_klmin=2 then 1 end PSAK   
   ,case when akta_kwn in(1) and jenis_klmin=1 then 1 end LBAK
   ,case when akta_kwn in(1) and jenis_klmin=2 then 1 end PBAK   
   ,case when jenis_klmin in(1) then 1 end JLK
   ,case when jenis_klmin in(2) then 1 end  JPR
   ,case when jenis_klmin in(1,2) then 1 end  JDUK
   ,(select count(*) from biodata_wni where stat_kwn=2 and flag_status='0') pd    
FROM biodata_wni a, tgl_aktif b
WHERE 1=1 and tgl_lhr < b.tgl_akhir and flag_status='0' and stat_kwn=2
)
GROUP BY 
GROUPING SETS((no_kab,no_prop,umur,pd)
,(NO_KAB,pd)
)    
ORDER BY no_kab,UMUR;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3201 NOLOGGING TABLESPACE SIAKDATA AS
select
    row_number() over(partition by no_kab order by no_kab) as urut
    ,case when umur is null then 'JUMLAH' else umur end umur
    ,sum(lsak) LSAK,sum(psak) PSAK
    ,to_char(round((sum(psak)+sum(lsak))*100/(pd),2),'00.00') ppsak
    ,sum(lbak) LBAK,sum(pbak) PBAK
    ,to_char(round((sum(pbak)+sum(lbak))*100/(pd),2),'00.00') ppbak    
    ,sum(jlk)  JLK,sum(jpr) JPR
    ,sum(jduk) JDUK,to_char(round(sum(jduk)*100/(pd),2),'000.00') pjduk
    from
    (
    select 
    no_kec,no_kab,no_prop
    ,decode (floor ((months_between (sysdate, tgl_lhr) / 60))
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39',
    8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','75>+')  UMUR
   ,case when akta_kwn in(2) and jenis_klmin=1 then 1 end LSAK
   ,case when akta_kwn in(2) and jenis_klmin=2 then 1 end PSAK   
   ,case when akta_kwn in(1) and jenis_klmin=1 then 1 end LBAK
   ,case when akta_kwn in(1) and jenis_klmin=2 then 1 end PBAK   
   ,case when jenis_klmin in(1) then 1 end JLK
   ,case when jenis_klmin in(2) then 1 end  JPR
   ,case when jenis_klmin in(1,2) then 1 end  JDUK
   ,(select count(*) from biodata_wni where stat_kwn=3 and flag_status='0') pd    
FROM biodata_wni a, tgl_aktif b
WHERE 1=1 and tgl_lhr < b.tgl_akhir and flag_status='0' and stat_kwn=3
)
GROUP BY 
GROUPING SETS((no_kab,no_prop,umur,pd)
,(NO_KAB,pd)
)    
ORDER BY no_kab,UMUR;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3202 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00'))NOKEC
   ,case when 
   getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
   getnamakab(no_kab,no_prop) else 
    getnamakab(no_kab,no_prop) end else 
    getnamakec(no_kec,no_kab,no_prop) end nmkec
    ,sum(lsak) LSAK,sum(psak) PSAK,to_char(round((sum(psak)+sum(lsak))*100/(pd),2),'00.00') ppsak
    ,sum(lbak) LBAK,sum(pbak) PBAK,to_char(round((sum(pbak)+sum(lbak))*100/(pd),2),'00.00') ppbak    
    ,sum(jlk)  JLK ,sum(jpr) JPR
    ,sum(jduk) JDUK,to_char(round(sum(jduk)*100/(pd),2),'000.00') pjduk
    from
    (
    select 
    no_kec,no_kab,no_prop
   ,case when akta_crai in(2) and jenis_klmin=1 then 1 end LSAK
   ,case when akta_crai in(2) and jenis_klmin=2 then 1 end PSAK   
   ,case when akta_crai in(1) and jenis_klmin=1 then 1 end LBAK
   ,case when akta_crai in(1) and jenis_klmin=2 then 1 end PBAK   
   ,case when jenis_klmin in(1) then 1 end JLK
   ,case when jenis_klmin in(2) then 1 end  JPR
   ,case when jenis_klmin in(1,2) then 1 end  JDUK
   ,(select count(*) from biodata_wni where stat_kwn=3 and flag_status='0') pd    
FROM biodata_wni a, tgl_aktif b
WHERE 1=1 and tgl_lhr < b.tgl_akhir and flag_status='0' and stat_kwn=3
)
GROUP BY 
GROUPING SETS((no_prop,no_kab,no_kec,pd)
,(no_prop,NO_KAB,pd)
)
ORDER BY no_prop,NO_KAB,no_kec;

set termout off;
spool off;


--------------------------------------------------------		  
-- MATI
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3203 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(mati_no_prov,'00'))||''||trim(to_char(mati_no_kab,'00'))||''||trim(to_char(mati_no_kec,'00'))NOKEC
   ,case when 
   getnamakec(mati_no_kec,mati_no_kab,mati_no_prov) is null then case when mati_no_kab<70 then 'KAB. '||
   getnamakab(mati_no_kab,mati_no_prov) else 
    getnamakab(mati_no_kab,mati_no_prov) end else 
    getnamakec(mati_no_kec,mati_no_kab,mati_no_prov) end nmkec
    ,sum(lsak) LSAK,sum(psak) PSAK,to_char(round((sum(psak)+sum(lsak))*100/(pd),2),'00.00') ppsak
    ,sum(lbak) LBAK,sum(pbak) PBAK,to_char(round((sum(pbak)+sum(lbak))*100/(pd),2),'00.00') ppbak    
    ,sum(jlk)  JLK ,sum(jpr) JPR
    ,sum(jduk) JDUK,to_char(round(sum(jduk)*100/(pd),2),'000.00') pjduk
    from
    (
    select 
    mati_no_kec,mati_no_kab,mati_no_prov
   ,case when ADM_AKTA_KTP_TGL is not null and MATI_JNS_KELAMIN=1 then 1 else 0 end LSAK
   ,case when ADM_AKTA_KTP_TGL is not null and MATI_JNS_KELAMIN=2 then 1 else 0 end PSAK   
   ,case when ADM_AKTA_KTP_TGL is null and MATI_JNS_KELAMIN=1 then 1 else 0 end LBAK
   ,case when ADM_AKTA_KTP_TGL is null and MATI_JNS_KELAMIN=2 then 1 else 0 end PBAK   
   ,case when MATI_JNS_KELAMIN in(1) then 1 else 0 end JLK
   ,case when MATI_JNS_KELAMIN in(2) then 1 else 0 end  JPR
   ,case when MATI_JNS_KELAMIN in(1,2) then 1 else 0 end  JDUK
   ,(select count(*) from capil_mati) pd    
FROM capil_mati a, tgl_aktif b
WHERE 1=1 and mati_tgl_lahir < b.tgl_akhir
and mati_no_prov in(select no_prop from biodata_wni where flag_status='0')
and mati_no_kab in(select no_kab from biodata_wni where flag_status='0') 
)
GROUP BY 
GROUPING SETS((mati_no_prov,mati_no_kab,mati_no_kec,pd)
,(mati_no_prov,mati_no_kab,pd)
)
ORDER BY mati_no_prov,mati_no_kab,mati_no_kec;

set termout off;
spool off;


--------------------------------------------------------		  
-- MATI
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3204 NOLOGGING TABLESPACE SIAKDATA AS
select
    row_number() over(partition by mati_no_kab order by mati_no_kab) as urut
    ,case when umur is null then 'JUMLAH' else umur end umur
    ,sum(lsak) LSAK,sum(psak) PSAK
    ,to_char(round((sum(psak)+sum(lsak))*100/(pd),2),'00.00') ppsak
    ,sum(lbak) LBAK,sum(pbak) PBAK
    ,to_char(round((sum(pbak)+sum(lbak))*100/(pd),2),'00.00') ppbak    
    ,sum(jlk)  JLK,sum(jpr) JPR
    ,sum(jduk) JDUK,to_char(round(sum(jduk)*100/(pd),2),'000.00') pjduk
    from
    (
    select 
    mati_no_kec,mati_no_kab,mati_no_prov
    ,decode (floor ((months_between (mati_tgl_mati, mati_tgl_lahir) / 60))
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39',
    8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','75>+')  UMUR
   ,case when ADM_AKTA_KTP_TGL is not null and MATI_JNS_KELAMIN=1 then 1 else 0 end LSAK
   ,case when ADM_AKTA_KTP_TGL is not null and MATI_JNS_KELAMIN=2 then 1 else 0 end PSAK   
   ,case when ADM_AKTA_KTP_TGL is null and MATI_JNS_KELAMIN=1 then 1 else 0 end LBAK
   ,case when ADM_AKTA_KTP_TGL is null and MATI_JNS_KELAMIN=2 then 1 else 0 end PBAK   
   ,case when MATI_JNS_KELAMIN in(1) then 1 else 0 end JLK
   ,case when MATI_JNS_KELAMIN in(2) then 1 else 0 end  JPR
   ,case when MATI_JNS_KELAMIN in(1,2) then 1 else 0 end  JDUK
   ,(select count(*) from capil_mati) pd       
FROM capil_mati a, tgl_aktif b
WHERE 1=1 and mati_tgl_lahir < b.tgl_akhir
and mati_no_prov in(select no_prop from biodata_wni where flag_status='0')
and mati_no_kab in(select no_kab from biodata_wni where flag_status='0') 
)
GROUP BY 
GROUPING SETS((mati_no_kab,mati_no_prov,umur,pd)
,(mati_NO_KAB,pd)
)    
ORDER BY mati_no_kab,UMUR;

set termout off;
spool off;

--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3301 NOLOGGING TABLESPACE SIAKDATA AS
select
   trim(to_char(prop,'00'))||''||
   trim(to_char(kab,'00'))||''||
   trim(to_char(kec,'00')) as NOKEC
   ,case when 
   getnamakec(kec,kab,prop) is null then case when kab<70 then 'KAB. '||
   getnamakab(kab,prop) else 
   getnamakab(kab,prop) end else 
   getnamakec(kec,kab,prop) end nmkec
   ,sum(lprop) lprop
   ,sum(pprop) pprop
   ,sum(jprop)jprop 
   from
  (
  select   
   from_no_prop prop,from_no_kab kab,from_no_kec kec
   ,a.created_date thn
    ,case when b.jenis_klmin in(1) then 1 end LPROP
    ,case when b.jenis_klmin in(2) then 1 end PPROP
    ,case when b.jenis_klmin in(1,2) then 1 end as JPROP
    from pindah_header a join pindah_detail b on
    a.no_pindah=b.no_pindah and a.klasifikasi_pindah in(5) where 
    a.from_no_prop in(select no_prop from biodata_wni where flag_status='0') and
    a.from_no_kab in(select no_kab from biodata_wni where flag_status='0')
)x
join tgl_aktif c on to_char(tgl_akhir,'yyyy')=to_char(x.thn,'yyyy')
       group by
        grouping sets ((kec,prop,kab)
        ,(prop,kab)
        )
        order by prop,kab,kec;

set termout off;
spool off;



--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3302 NOLOGGING TABLESPACE SIAKDATA AS
select
   trim(to_char(prop,'00'))||''||
   trim(to_char(kab,'00'))||''||
   trim(to_char(kec,'00')) as NOKEC
   ,case when 
   getnamakec(kec,kab,prop) is null then case when kab<70 then 'KAB. '||
   getnamakab(kab,prop) else 
   getnamakab(kab,prop) end else 
   getnamakec(kec,kab,prop) end nmkec
   ,sum(PKRJN) PKRJN
   ,sum(PDDKN) PDDKN
   ,sum(KAMAN) KAMAN
   ,sum(KSHTN) KSHTN
   ,sum(PRMHN) PRMHN
   ,sum(KLRGA) KLRGA
   ,sum(LAINY) LAINY
   ,SUM(PKRJN)+SUM(PDDKN)+SUM(KAMAN)+SUM(KSHTN)+SUM(PRMHN)+SUM(KLRGA)+SUM(LAINY) AS JMLAH
   from
  (
  select   
   from_no_prop prop,from_no_kab kab,from_no_kec kec
   ,a.created_date thn
    ,case when a.alasan_pindah in(1) then 1 else 0 end PKRJN
    ,case when a.alasan_pindah in(2) then 1 else 0 end PDDKN
    ,case when a.alasan_pindah in(3) then 1 else 0 end KAMAN
    ,case when a.alasan_pindah in(4) then 1 else 0 end KSHTN
    ,case when a.alasan_pindah in(5) then 1 else 0 end PRMHN
    ,case when a.alasan_pindah in(6) then 1 else 0 end KLRGA
    ,case when a.alasan_pindah in(7) then 1 else 0 end LAINY   
    from pindah_header a join pindah_detail b on
    a.no_pindah=b.no_pindah and a.klasifikasi_pindah in(5) where 
    a.from_no_prop in(select no_prop from biodata_wni where flag_status='0') and
    a.from_no_kab in(select no_kab from biodata_wni where flag_status='0')
)x
join tgl_aktif c on to_char(tgl_akhir,'yyyy')=to_char(x.thn,'yyyy')
       group by
        grouping sets ((kec,prop,kab)
        ,(prop,kab)
        )
        order by prop,kab,kec;

set termout off;
spool off;



--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3303 NOLOGGING TABLESPACE SIAKDATA AS
select
  row_number() over(partition by 1 order by 1) NOKEC
   ,case when bln is null then 'JUMLAH' else bln end bln
   ,sum(lprop) lprop
   ,sum(pprop) pprop
   ,sum(jprop)jprop 
   from
  (
  select   
   from_no_prop prop,from_no_kab kab,from_no_kec kec
   ,a.created_date thn
   ,to_char(a.created_date,'mm-yyyy') bln
    ,case when b.jenis_klmin in(1) then 1 end LPROP
    ,case when b.jenis_klmin in(2) then 1 end PPROP
    ,case when b.jenis_klmin in(1,2) then 1 end as JPROP
    from pindah_header a join pindah_detail b on
    a.no_pindah=b.no_pindah and a.klasifikasi_pindah in(5) where 
    a.from_no_prop in(select no_prop from biodata_wni where flag_status='0') and
    a.from_no_kab in(select no_kab from biodata_wni where flag_status='0')
)x
join tgl_aktif c on to_char(tgl_akhir,'yyyy')=to_char(x.thn,'yyyy')
       group by
        grouping sets ((prop,kab,bln)
        ,(prop,kab)
        )
        order by prop,kab;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3304 NOLOGGING TABLESPACE SIAKDATA AS
select   
 row_number() over(partition by 1 order by 1) NOKEC
   ,TAHUN,JAN,FEB,MAR,APR,MEI,JUN
   ,JAN+FEB+MAR+APR+MEI+JUN JML
   from
  (
  select   
   to_char(a.created_date,'yyyy') tahun
   ,to_char(a.created_date,'mm') bln
    from pindah_header a join pindah_detail b on
    a.no_pindah=b.no_pindah and a.klasifikasi_pindah in(5) 
    join tgl_aktif d on to_char(d.tgl_akhir,'yyyy') - 5 < = to_char(a.created_date,'yyyy')
    where 1=1 and  
    a.from_no_prop in(select no_prop from biodata_wni where flag_status='0') and
    a.from_no_kab in(select no_kab from biodata_wni where flag_status='0')
)
 PIVOT (SUM(1) FOR (BLN) IN ('01' JAN, '02' FEB,'03' MAR,'04' APR,'05' MEI,'06' JUN)
           )
 ORDER BY 1;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3305 NOLOGGING TABLESPACE SIAKDATA AS
select   
row_number() over(partition by 1 order by 1) NOKEC
,TAHUN,JUL,AGS,SEP,OKT,NOV,DES
   ,JUL+AGS+SEP+OKT+NOV+DES JML
   from
  (
  select   
   to_char(a.created_date,'yyyy') tahun
   ,to_char(a.created_date,'mm') bln
    from pindah_header a join pindah_detail b on
    a.no_pindah=b.no_pindah and a.klasifikasi_pindah in(5) 
    join tgl_aktif d on to_char(d.tgl_akhir,'yyyy') - 5 < to_char(a.created_date,'yyyy')     
    where 1=1 and 
    a.from_no_prop in(select no_prop from biodata_wni where flag_status='0') and
    a.from_no_kab in(select no_kab from biodata_wni where flag_status='0') 
  order by 1
)
 PIVOT (SUM(1) FOR (BLN) IN ('07' JUL, '08' AGS,'09' SEP,'10' OKT,'11' NOV,'12' DES)
           )
 ORDER BY 1;

set termout off;
spool off;

--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3401 NOLOGGING TABLESPACE SIAKDATA AS
select
   trim(to_char(prop,'00'))||''||
   trim(to_char(kab,'00'))||''||
   trim(to_char(kec,'00')) as NOKEC
   ,case when 
   getnamakec(kec,kab,prop) is null then case when kab<70 then 'KAB. '||
   getnamakab(kab,prop) else 
   getnamakab(kab,prop) end else 
   getnamakec(kec,kab,prop) end nmkec
   ,sum(lprop) lprop
   ,sum(pprop) pprop
   ,sum(jprop)jprop 
   from
  (
  select   
   from_no_prop prop,from_no_kab kab,from_no_kec kec
   ,a.created_date thn
    ,case when b.jenis_klmin in(1) then 1 end LPROP
    ,case when b.jenis_klmin in(2) then 1 end PPROP
    ,case when b.jenis_klmin in(1,2) then 1 end as JPROP
    from pindah_header a join pindah_detail b on
    a.no_pindah=b.no_pindah and a.klasifikasi_pindah in(4) where 
    a.from_no_prop in(select no_prop from biodata_wni where flag_status='0') and
    a.from_no_kab in(select no_kab from biodata_wni where flag_status='0')
)x
join tgl_aktif c on to_char(tgl_akhir,'yyyy')=to_char(x.thn,'yyyy')
       group by
        grouping sets ((kec,prop,kab)
        ,(prop,kab)
        )
        order by prop,kab,kec;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3402 NOLOGGING TABLESPACE SIAKDATA AS
select
   trim(to_char(prop,'00'))||''||
   trim(to_char(kab,'00'))||''||
   trim(to_char(kec,'00')) as NOKEC
   ,case when 
   getnamakec(kec,kab,prop) is null then case when kab<70 then 'KAB. '||
   getnamakab(kab,prop) else 
   getnamakab(kab,prop) end else 
   getnamakec(kec,kab,prop) end nmkec
   ,sum(PKRJN) PKRJN
   ,sum(PDDKN) PDDKN
   ,sum(KAMAN) KAMAN
   ,sum(KSHTN) KSHTN
   ,sum(PRMHN) PRMHN
   ,sum(KLRGA) KLRGA
   ,sum(LAINY) LAINY
   ,SUM(PKRJN)+SUM(PDDKN)+SUM(KAMAN)+SUM(KSHTN)+SUM(PRMHN)+SUM(KLRGA)+SUM(LAINY) AS JMLAH
   from
  (
  select   
   from_no_prop prop,from_no_kab kab,from_no_kec kec
   ,a.created_date thn
    ,case when a.alasan_pindah in(1) then 1 else 0 end PKRJN
    ,case when a.alasan_pindah in(2) then 1 else 0 end PDDKN
    ,case when a.alasan_pindah in(3) then 1 else 0 end KAMAN
    ,case when a.alasan_pindah in(4) then 1 else 0 end KSHTN
    ,case when a.alasan_pindah in(5) then 1 else 0 end PRMHN
    ,case when a.alasan_pindah in(6) then 1 else 0 end KLRGA
    ,case when a.alasan_pindah in(7) then 1 else 0 end LAINY   
    from pindah_header a join pindah_detail b on
    a.no_pindah=b.no_pindah and a.klasifikasi_pindah in(4) where 
    a.from_no_prop in(select no_prop from biodata_wni where flag_status='0') and
    a.from_no_kab in(select no_kab from biodata_wni where flag_status='0')
)x
join tgl_aktif c on to_char(tgl_akhir,'yyyy')=to_char(x.thn,'yyyy')
       group by
        grouping sets ((kec,prop,kab)
        ,(prop,kab)
        )
        order by prop,kab,kec;

set termout off;
spool off;

--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3403 NOLOGGING TABLESPACE SIAKDATA AS
select
  row_number() over(partition by 1 order by 1) NOKEC
   ,case when bln is null then 'JUMLAH' else bln end bln
   ,sum(lprop) lprop
   ,sum(pprop) pprop
   ,sum(jprop)jprop 
   from
  (
  select   
   from_no_prop prop,from_no_kab kab,from_no_kec kec
   ,a.created_date thn
   ,to_char(a.created_date,'mm-yyyy') bln
    ,case when b.jenis_klmin in(1) then 1 end LPROP
    ,case when b.jenis_klmin in(2) then 1 end PPROP
    ,case when b.jenis_klmin in(1,2) then 1 end as JPROP
    from pindah_header a join pindah_detail b on
    a.no_pindah=b.no_pindah and a.klasifikasi_pindah in(4) where 
    a.from_no_prop in(select no_prop from biodata_wni where flag_status='0') and
    a.from_no_kab in(select no_kab from biodata_wni where flag_status='0')
)x
join tgl_aktif c on to_char(tgl_akhir,'yyyy')=to_char(x.thn,'yyyy')
       group by
        grouping sets ((prop,kab,bln)
        ,(prop,kab)
        )
        order by prop,kab;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3501 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(prop,'00'))||''||
    trim(to_char(kab,'00'))||''||
    trim(to_char(kec,'00'))NOKEC
   ,case when 
   getnamakec(kec,kab,prop) is null then case when kab<70 then 'KAB. '||
   getnamakab(kab,prop) else 
   getnamakab(kab,prop) end else 
   getnamakec(kec,kab,prop) end NMKEC
   ,sum (lprop) lprop
   ,sum (pprop) pprop   
   ,sum (jprop) jprop   
   from
   (
   select
   a.no_prop prop,a.no_kab kab,a.no_kec kec
   ,a.tgl_datang thn
    ,case when c.jenis_klmin in(1)   then 1 end as LPROP
    ,case when c.jenis_klmin in(2) then 1 end as PPROP
    ,case when c.jenis_klmin in(1,2)  then 1 end as JPROP
from datang_header a
    join datang_detail b on a.no_datang=b.no_datang
    join pindah_detail c on a.no_pindah=c.no_pindah and b.nik=c.nik and a.klasifikasi_pindah in(5) 
    join pindah_header d on a.no_pindah=d.no_pindah and d.klasifikasi_pindah in(5)
    where  
    a.no_prop in(select no_prop from biodata_wni where flag_status='0') and
    a.no_kab in(select no_kab from biodata_wni where flag_status='0')
)x
join tgl_aktif d on to_char(tgl_akhir,'yyyy')=to_char(thn,'yyyy')      
group by
grouping sets((prop,kab,kec)
,(prop,kab)
)
order by prop,kab,kec;

set termout off;
spool off;

--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3502 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(prop,'00'))||''||
    trim(to_char(kab,'00'))||''||
    trim(to_char(kec,'00'))NOKEC
   ,case when 
   getnamakec(kec,kab,prop) is null then case when kab<70 then 'KAB. '||
   getnamakab(kab,prop) else 
   getnamakab(kab,prop) end else 
   getnamakec(kec,kab,prop) end NMKEC
,sum(PKRJN) PKRJN
   ,sum(PDDKN) PDDKN
   ,sum(KAMAN) KAMAN
   ,sum(KSHTN) KSHTN
   ,sum(PRMHN) PRMHN
   ,sum(KLRGA) KLRGA
   ,sum(LAINY) LAINY
   ,SUM(PKRJN)+SUM(PDDKN)+SUM(KAMAN)+SUM(KSHTN)+SUM(PRMHN)+SUM(KLRGA)+SUM(LAINY) AS JMLAH
   from
  (
  select   
   a.no_prop prop,a.no_kab kab,a.no_kec kec
   ,a.created_date thn
    ,case when d.alasan_pindah in(1) then 1 else 0 end PKRJN
    ,case when d.alasan_pindah in(2) then 1 else 0 end PDDKN
    ,case when d.alasan_pindah in(3) then 1 else 0 end KAMAN
    ,case when d.alasan_pindah in(4) then 1 else 0 end KSHTN
    ,case when d.alasan_pindah in(5) then 1 else 0 end PRMHN
    ,case when d.alasan_pindah in(6) then 1 else 0 end KLRGA
    ,case when d.alasan_pindah in(7) then 1 else 0 end LAINY  
from datang_header a
    join datang_detail b on a.no_datang=b.no_datang
    join pindah_detail c on a.no_pindah=c.no_pindah and b.nik=c.nik and a.klasifikasi_pindah in(5)
    join pindah_header d on a.no_pindah=d.no_pindah and d.klasifikasi_pindah in(5)
   where   
   a.no_prop  in(select e.no_prop from biodata_wni e where e.flag_status='0') and
   a.no_kab  in(select e.no_kab from biodata_wni e where e.flag_status='0')
)x
join tgl_aktif d on to_char(tgl_akhir,'yyyy')=to_char(thn,'yyyy')
group by
grouping sets((prop,kab,kec)
,(prop,kab)
)
order by prop,kab,kec;

set termout off;
spool off;

--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3503 NOLOGGING TABLESPACE SIAKDATA AS
select
  row_number() over(partition by 1 order by 1) NOKEC
   ,case when bln is null then 'JUMLAH' else bln end bln
   ,sum(lprop) lprop
   ,sum(pprop) pprop
   ,sum(jprop)jprop 
   from
   (
   select
   a.no_prop prop,a.no_kab kab,a.no_kec kec
   ,a.tgl_datang thn
   ,to_char(a.tgl_datang,'mm-yyyy') bln
    ,case when c.jenis_klmin in(1)   then 1 end as LPROP
    ,case when c.jenis_klmin in(2) then 1 end as PPROP
    ,case when c.jenis_klmin in(1,2)  then 1 end as JPROP
from datang_header a
    join datang_detail b on a.no_datang=b.no_datang
    join pindah_detail c on a.no_pindah=c.no_pindah and b.nik=c.nik and a.klasifikasi_pindah in(5) 
    join pindah_header d on a.no_pindah=d.no_pindah and d.klasifikasi_pindah in(5)
    where 
    a.no_prop in(select no_prop from biodata_wni where flag_status='0') and
    a.no_kab in(select no_kab from biodata_wni where flag_status='0')
)x
join tgl_aktif d on to_char(tgl_akhir,'yyyy')=to_char(thn,'yyyy')
      
group by
grouping sets((prop,kab,bln)
,(prop,kab)
)
order by prop,kab;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3601 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(prop,'00'))||''||
    trim(to_char(kab,'00'))||''||
    trim(to_char(kec,'00'))NOKEC
   ,case when 
   getnamakec(kec,kab,prop) is null then case when kab<70 then 'KAB. '||
   getnamakab(kab,prop) else 
   getnamakab(kab,prop) end else 
   getnamakec(kec,kab,prop) end NMKEC
   ,sum (lprop) lprop
   ,sum (pprop) pprop   
   ,sum (jprop) jprop   
   from
   (
   select
   a.no_prop prop,a.no_kab kab,a.no_kec kec
   ,a.tgl_datang thn
    ,case when c.jenis_klmin in(1)   then 1 end as LPROP
    ,case when c.jenis_klmin in(2) then 1 end as PPROP
    ,case when c.jenis_klmin in(1,2)  then 1 end as JPROP
from datang_header a
    join datang_detail b on a.no_datang=b.no_datang
    join pindah_detail c on a.no_pindah=c.no_pindah and b.nik=c.nik and a.klasifikasi_pindah in(4) 
	join pindah_header d on a.no_pindah=d.no_pindah and d.klasifikasi_pindah in(4)
	where 
    a.no_prop in(select no_prop from biodata_wni where flag_status='0') and
    a.no_kab in(select no_kab from biodata_wni where flag_status='0')
)x
join tgl_aktif d on to_char(tgl_akhir,'yyyy')=to_char(thn,'yyyy')
group by
grouping sets((prop,kab,kec)
,(prop,kab)
)
order by prop,kab,kec;

set termout off;
spool off;


--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3602 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(prop,'00'))||''||
    trim(to_char(kab,'00'))||''||
    trim(to_char(kec,'00'))NOKEC
   ,case when 
   getnamakec(kec,kab,prop) is null then case when kab<70 then 'KAB. '||
   getnamakab(kab,prop) else 
   getnamakab(kab,prop) end else 
   getnamakec(kec,kab,prop) end NMKEC
,sum(PKRJN) PKRJN
   ,sum(PDDKN) PDDKN
   ,sum(KAMAN) KAMAN
   ,sum(KSHTN) KSHTN
   ,sum(PRMHN) PRMHN
   ,sum(KLRGA) KLRGA
   ,sum(LAINY) LAINY
   ,SUM(PKRJN)+SUM(PDDKN)+SUM(KAMAN)+SUM(KSHTN)+SUM(PRMHN)+SUM(KLRGA)+SUM(LAINY) AS JMLAH
   from
  (
  select   
   a.no_prop prop,a.no_kab kab,a.no_kec kec
   ,a.created_date thn
    ,case when d.alasan_pindah in(1) then 1 else 0 end PKRJN
    ,case when d.alasan_pindah in(2) then 1 else 0 end PDDKN
    ,case when d.alasan_pindah in(3) then 1 else 0 end KAMAN
    ,case when d.alasan_pindah in(4) then 1 else 0 end KSHTN
    ,case when d.alasan_pindah in(5) then 1 else 0 end PRMHN
    ,case when d.alasan_pindah in(6) then 1 else 0 end KLRGA
    ,case when d.alasan_pindah in(7) then 1 else 0 end LAINY  
from datang_header a
    join datang_detail b on a.no_datang=b.no_datang
    join pindah_detail c on a.no_pindah=c.no_pindah and b.nik=c.nik and a.klasifikasi_pindah in(4)
    join pindah_header d on a.no_pindah=d.no_pindah and d.klasifikasi_pindah in(4)
   where   
   a.no_prop  in(select e.no_prop from biodata_wni e where e.flag_status='0') and
   a.no_kab  in(select e.no_kab from biodata_wni e where e.flag_status='0')
)x
join tgl_aktif d on to_char(tgl_akhir,'yyyy')=to_char(thn,'yyyy')
group by
grouping sets((prop,kab,kec)
,(prop,kab)
)
order by prop,kab,kec;

set termout off;
spool off;

--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas3603 NOLOGGING TABLESPACE SIAKDATA AS
select
  row_number() over(partition by 1 order by 1) NOKEC
   ,case when bln is null then 'JUMLAH' else bln end bln
   ,sum(lprop) lprop
   ,sum(pprop) pprop
   ,sum(jprop)jprop 
   from
   (
   select
   a.no_prop prop,a.no_kab kab,a.no_kec kec
   ,a.tgl_datang thn
   ,to_char(a.tgl_datang,'mm-yyyy') bln
    ,case when c.jenis_klmin in(1)   then 1 end as LPROP
    ,case when c.jenis_klmin in(2) then 1 end as PPROP
    ,case when c.jenis_klmin in(1,2)  then 1 end as JPROP
from datang_header a
    join datang_detail b on a.no_datang=b.no_datang
    join pindah_detail c on a.no_pindah=c.no_pindah and b.nik=c.nik and a.klasifikasi_pindah in(4) 
    join pindah_header d on a.no_pindah=d.no_pindah and d.klasifikasi_pindah in(4)
    where 
    a.no_prop in(select no_prop from biodata_wni where flag_status='0') and
    a.no_kab in(select no_kab from biodata_wni where flag_status='0')
)x
join tgl_aktif d on to_char(tgl_akhir,'yyyy')=to_char(thn,'yyyy')
group by
grouping sets((prop,kab,bln)
,(prop,kab)
)
order by prop,kab;

set termout off;
spool off;

--------------------------------------------------------		  
--
--------------------------------------------------------
CREATE TABLE &1..xkuantitas5501 NOLOGGING TABLESPACE SIAKDATA AS
select
      trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00'))NOKEC
     ,case when 
     getnamakec(no_kec,no_kab,no_prop) is null then case when no_kab<70 then 'KAB. '||
     getnamakab(no_kab,no_prop) else 
     getnamakab(no_kab,no_prop) end else 
     getnamakec(no_kec,no_kab,no_prop) end NMKEC
,sum (case when jk=1 and aktif=1 then 1 end) jduk_lk
,sum (case when jk=2 and aktif=1 then 1 end) jduk_pr
,sum (case when jk=1 and kk=1 and aktif=1 then 1 end) milik_kk_lk
,sum (case when jk=2 and kk=1 and aktif=1 then 1 end) milik_kk_pr
,sum (case when jk=1 and aktif=1 and wktp=1 then 1 end) wktp_lk
,sum (case when jk=2 and aktif=1 and wktp=1 then 1 end) wktp_pr
,sum (case when jk=1 and aktif=1 and ktp_el=1 then 1 end) ektp_lk
,sum (case when jk=2 and aktif=1 and ktp_el=1 then 1 end) ektp_pr
,sum (case when jk=1 and ak_lhr=1 and aktif=1 then 1 end)+
sum (case when jk=1 and mati=1 then 1 end)+
sum (case when jk=1 and ak_kwn=1 and aktif=1 then 1 end)+
sum (case when jk=1 and ak_crai=1 and aktif=1 then 1 end) akta_lk
,sum (case when jk=2 and ak_lhr=1 and aktif=1 then 1 end)+
sum (case when jk=2 and mati=1 then 1 end)+
sum (case when jk=2 and ak_kwn=1 and aktif=1 then 1 end)+
sum (case when jk=2 and ak_crai=1 and aktif=1 then 1 end) akta_pr
,sum (case when ak_lhr=1 and aktif=1 then 1 end) ak_lhr
,sum (case when mati=1 then 1 end) ak_mati
,sum (case when ak_kwn=1 and aktif=1 then 1 end) ak_kwn
,sum (case when ak_crai=1 and aktif=1 then 1 end) ak_crai

from
(
select
a.no_prop,a.no_kab,a.no_kec,a.nik,a.nama_lgkp
,a.jenis_klmin jk
,case when b.count_kk>0 and a.stat_hbkel=1 then 1 end kk
,floor(months_between(sysdate,a.tgl_lhr)/12) umur
,case when (floor(months_between(sysdate,a.tgl_lhr)/12)>=17 or a.stat_kwn>1) then 1 end wktp
,case when c.current_status_code is not null then 1 end ktp_el 
,case when e.bayi_nik is not null then 1 end ak_lhr
,case when d.mati_nik is not null then 1 end ak_mati
,case when a.akta_lhr=2 and length(a.no_akta_lhr)>=5 then 1 end ak_lhr
,case when a.akta_kwn=2 and length(a.no_akta_kwn)>=5 then 1 end ak_kwn
,case when a.akta_crai=2 and length(a.no_akta_crai)>=5 then 1 end ak_crai
,case when a.flag_status=0 then 1 end aktif
,case when a.flag_status=1 then 1 end mati
from biodata_wni a join data_keluarga b on a.no_kk=b.no_kk and flag_status in(0,1) 
left join demographics c on a.nik=c.nik
left join capil_mati d on a.nik=d.mati_nik 
left join capil_lahir e on a.nik=e.bayi_nik
)
GROUP BY 
GROUPING SETS((no_prop,no_kab,no_kec)
,(no_prop,no_kab)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool off;

--------------------------------------------------------		  
-- GRAPHICS DKB PDAK
--------------------------------------------------------
CREATE TABLE &1..xGraphics0101 NOLOGGING TABLESPACE SIAKDATA AS
SELECT no_kec
       ,getnamakec(no_kec,no_kab,no_prop) NMKEC
       ,sum (case when jenis_klmin in(1,2) then 1 else null end) JUMLAH
    FROM biodata_wni where flag_status=0  
    GROUP BY no_kec,no_kab,no_prop
    ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool off;



--------------------------------------------------------		  
-- GRAPHICS DKB PDAK 1
--------------------------------------------------------
CREATE TABLE &1..xGraphics0101 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_kec,'00')) NOKEC
    ,getnamakec(no_kec,no_kab,no_prop) NMKEC
   ,sum(case when tgl_entri < trunc(b.tgl_akhir, 'YYYY') + interval '1' year then  1 else 0 end) as SBACK
   ,sum(case when tgl_entri < trunc(b.tgl_akhir, 'YYYY') - interval '0' year then  1 else 0 end) as TBACK   
from biodata_wni a,tgl_aktif b where flag_status=0
group by no_kec,no_kab,no_prop
order by no_kec;

set termout off;
spool off;


--------------------------------------------------------		  
-- GRAPHICS DKB PDAK 3
--------------------------------------------------------
CREATE TABLE &1..xGraphics0103 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_kec,'00')) NOKEC
    ,getnamakec(no_kec,no_kab,no_prop) NMKEC
   ,sum(case when tgl_entri < trunc(b.tgl_akhir, 'YYYY') + interval '1' year then  1 else 0 end) as SBACK
   ,sum(case when tgl_entri < trunc(b.tgl_akhir, 'YYYY') - interval '0' year then  1 else 0 end) as TBACK   
from biodata_wni a,tgl_aktif b where flag_status=0
group by no_kec,no_kab,no_prop
order by no_kec;

set termout off;
spool off;


--------------------------------------------------------		  
-- GRAPHICS DKB PDAK 4
--------------------------------------------------------
CREATE TABLE &1..xGraphics0104 NOLOGGING TABLESPACE SIAKDATA AS
select
    row_number() over(partition by no_kab order by no_kab) as norut
    ,umur umur
    ,sum(jml) jml
    from
    (
select  
    no_prop,no_kab
    ,decode (floor (months_between (b.tgl_akhir,tgl_lhr) / 60)
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>=75') as UMUR
    ,case when jenis_klmin in(1,2) then 1 end JML
FROM biodata_wni a,tgl_aktif b
WHERE tgl_lhr < b.tgl_akhir and flag_status='0'
)
GROUP BY no_kab,no_prop,umur
ORDER BY UMUR;

set termout off;
spool off;

--------------------------------------------------------		  
-- GRAPHICS DKB PDAK 5
--------------------------------------------------------
CREATE TABLE &1..xGraphics0105 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    row_number() over(partition by no_kab order by no_kab) as norut
    ,decode (floor (months_between (b.tgl_akhir,tgl_lhr) / 60)
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>=75') as umur
    ,to_char(round(sum(case when jenis_klmin in(1) then 1 end)/
    sum(case when jenis_klmin in(2) then 1 end)*100,2),'000.00') RASIO    
FROM biodata_wni a,tgl_aktif b 
WHERE tgl_lhr < b.tgl_akhir and flag_status='0'
GROUP BY no_kab,no_prop,
    decode (floor (months_between (b.tgl_akhir,tgl_lhr) / 60),
    0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39',
    8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>=75')
ORDER BY no_kab,UMUR;

set termout off;
spool off;


--------------------------------------------------------		  
-- GRAPHICS DKB PDAK 6
--------------------------------------------------------
CREATE TABLE &1..xGraphics0106 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    trim(to_char(no_kec,'00')) NOKEC
    ,getnamakec(no_kec,no_kab,no_prop) NMKEC
    ,to_char(round(sum(case when jenis_klmin in(1) then 1 end)/
    sum(case when jenis_klmin in(2) then 1 end)*100,2),'000.00') RASIO
	FROM biodata_wni where flag_status='0'
	GROUP BY no_kec,no_kab,no_prop
	ORDER BY NO_KAB,NO_KEC;

set termout off;
spool off;

--------------------------------------------------------		  
-- GRAPHICS DKB PDAK 7
--------------------------------------------------------
CREATE TABLE &1..xGraphics0107 NOLOGGING TABLESPACE SIAKDATA AS
select
         sum (ul14) LM0014
         ,sum (up14) PM0014
         ,sum (uj14) JM0014              
         ,sum (ul64) LD1564
         ,sum (up64) PD1564
         ,sum (uj64) JD1564         
         ,sum (ul65) LT65UP
         ,sum (up65) PT65UP
         ,sum (uj65) JT65UP      
        from
        (
        SELECT
          no_prop,no_kab
         ,case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) between 0 and 14 and jenis_klmin in(1) then 1 end ul14
         ,case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) between 0 and 14 and jenis_klmin in(2) then 1 end up14
         ,case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) between 0 and 14 and jenis_klmin in(1,2) then 1 end uj14         
         ,case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) between 15 and 64 and jenis_klmin in(2) then 1 end ul64
         ,case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) between 15 and 64 and jenis_klmin in(1) then 1 end up64
         ,case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) between 15 and 64 and jenis_klmin in(2) then 1 end uj64
         ,case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) >=65 and jenis_klmin in(1) then 1 end ul65
         ,case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) >=65 and jenis_klmin in(2) then 1 end up65
         ,case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) >=65 and jenis_klmin in(1,2) then 1 end uj65         
        FROM biodata_wni a,tgl_aktif b where flag_status='0'
        )
        GROUP BY no_prop,no_kab;

set termout off;
spool off;


--------------------------------------------------------		  
-- GRAPHICS DKB PDAK 8
--------------------------------------------------------
CREATE TABLE &1..xGraphics0108 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    	 trim(to_char(no_kec,'00')) NOKEC
         ,getnamakec(no_kec,no_kab,no_prop) NMKEC
          ,to_char(round((sum(case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) between 0 and 14 then 1 else 0 end)+
          sum(case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) >=65 then 1 else 0 end))/
          sum (case when floor (months_between (b.tgl_akhir,tgl_lhr)/12) between 15 and 64 then 1 else 0 end)*100,2),'00.00') PJ1465 
FROM biodata_wni a,tgl_aktif b where flag_status='0'
GROUP BY no_prop,no_kab,no_kec
ORDER BY no_kab,no_kec;

set termout off;
spool off;


--------------------------------------------------------		  
-- GRAPHICS DKB PDAK 9
--------------------------------------------------------
CREATE TABLE &1..xGraphics0109 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
		trim(to_char(no_kec,'00')) NOKEC
		,getnamakec(no_kec,no_kab,no_prop) NMKEC
        ,sum (case when jenis_klmin in(1) then 1 else 0 end ) LK
        ,sum (case when jenis_klmin in(2) then 1 else 0 end) PR
        ,sum (case when jenis_klmin in(1,2) then 1 else 0 end) JDUK
      FROM biodata_wni a,tgl_aktif b where flag_status='0'
			and floor(months_between(b.tgl_akhir,tgl_lhr)/12) between 7 and 16 and pddk_akh in(1)
      GROUP BY no_prop,no_kab,no_kec
     ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool off;

--------------------------------------------------------		  
-- GRAPHICS DKB PDAK 10
--------------------------------------------------------
CREATE TABLE &1..xGraphics0110 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
        b.no as URUT
        ,b.descrip as AGAMA        
        ,sum (case when jenis_klmin in(1) then 1 else 0 end ) LK
        ,sum (case when jenis_klmin in(2) then 1 else 0 end) PR
        ,sum (case when jenis_klmin in(1,2) then 1 else 0 end) JUMLAH
      FROM biodata_wni a join ref_siak_wni b on a.agama=b.no and b.sect='501' and flag_status=0  
      GROUP BY b.no,B.descrip
     ORDER BY b.no;

set termout off;
spool off;


--------------------------------------------------------		  
-- GRAPHICS DKB PDAK 11A
--------------------------------------------------------
CREATE TABLE &1..xGraphics0111 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
		sum (case when jenis_klmin in(1) and stat_kwn=1 then 1  end)LKBLM
		,sum (case when jenis_klmin in(2) and stat_kwn=1 then 1 end)PRBLM
		,sum (case when jenis_klmin in(1) and stat_kwn=2 then 1  end)LKKWN
		,sum (case when jenis_klmin in(2) and stat_kwn=2 then 1 end)PRKWN
		,sum (case when jenis_klmin in(1) and stat_kwn=3 then 1  end)LKCRH
		,sum (case when jenis_klmin in(2) and stat_kwn=3 then 1 end)PRCRH
		,sum (case when jenis_klmin in(1) and stat_kwn=4 then 1  end)LKCRM
		,sum (case when jenis_klmin in(2) and stat_kwn=4 then 1 end)PRCRM
FROM biodata_wni
WHERE flag_status=0
GROUP BY NO_PROP,NO_KAB;

set termout off;
spool off;



--------------------------------------------------------		  
-- GRAPHICS DKB PDAK 11B
--------------------------------------------------------
CREATE TABLE &1..xGraphics0102 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    row_number() over(partition by no_kab order by no_kab) as urut
    ,decode (floor (months_between (b.tgl_akhir,tgl_lhr) / 60)
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>= 75') as UMUR
    ,sum (case when stat_kwn in(1) then 1 else 0 end ) BKWN
    ,sum (case when stat_kwn in(2) then 1 else 0 end ) SKWN
    ,sum (case when stat_kwn in(3) then 1 else 0 end ) HKWN
    ,sum (case when stat_kwn in(4) then 1 else 0 end ) MKWN	
FROM biodata_wni a,tgl_aktif b WHERE tgl_lhr < b.tgl_akhir and flag_status='0' and floor (months_between (b.tgl_akhir,tgl_lhr) / 12)>=15
GROUP BY no_kab,no_prop
    ,decode (floor (months_between (b.tgl_akhir,tgl_lhr) / 60)
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>= 75')    
ORDER BY urut,UMUR;

set termout off;
spool off;

--------------------------------------------------------		  
-- GRAPHICS DKB PDAK 12
--------------------------------------------------------
CREATE TABLE &1..xGraphics0112 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    a.pddk_akh NPD,b.descrip NMPD
    ,sum (case when jenis_klmin in(1) then 1 else 0 end) LK
    ,sum (case when jenis_klmin in(2) then 1 else 0 end) PR
    --,sum (case when jenis_klmin in(1,2) then 1 else 0 end) JDUK
FROM biodata_wni a, ref_siak_wni b where a.pddk_akh=b.no and a.flag_status=0 and b.sect='101'
GROUP BY no_prop,no_kab,a.pddk_akh,b.descrip
ORDER BY no_kab,a.pddk_akh;

set termout off;
spool off;


--------------------------------------------------------		  
-- GRAPHICS DKB PDAK 14
--------------------------------------------------------
CREATE TABLE &1..xGraphics0113 NOLOGGING TABLESPACE SIAKDATA AS
select 
      trim(to_char(no_kAB,'00')) NOKEC      
     ,sum (case when jk in(1) and umur between 0 and 4 then -1 end) L0004
     ,sum (case when jk in(2) and umur between 0 and 4 then 1 end) P0004
     ,sum (case when jk in(1) and umur between 5 and 9 then -1 end) L0509
     ,sum (case when jk in(2) and umur between 5 and 9 then 1 end) P0509
     ,sum (case when jk in(1) and umur between 10 and 14 then -1 end) L1014
     ,sum (case when jk in(2) and umur between 10 and 14 then 1 end) P1014
     ,sum (case when jk in(1) and umur between 15 and 19 then -1 end) L1519
     ,sum (case when jk in(2) and umur between 15 and 19 then 1 end) P1519
     ,sum (case when jk in(1) and umur between 20 and 24 then -1 end) L2024
     ,sum (case when jk in(2) and umur between 20 and 24 then 1 end) P2024
     ,sum (case when jk in(1) and umur between 25 and 29 then -1 end) L2529
     ,sum (case when jk in(2) and umur between 25 and 29 then 1 end) P2529
     ,sum (case when jk in(1) and umur between 30 and 34 then -1 end) L3034
     ,sum (case when jk in(2) and umur between 30 and 34 then 1 end) P3034
     ,sum (case when jk in(1) and umur between 35 and 39 then -1 end) L3539
     ,sum (case when jk in(2) and umur between 35 and 39 then 1 end) P3539
     ,sum (case when jk in(1) and umur between 40 and 44 then -1 end) L4044
     ,sum (case when jk in(2) and umur between 40 and 44 then 1 end) P4044
     ,sum (case when jk in(1) and umur between 45 and 49 then -1 end) L4549
     ,sum (case when jk in(2) and umur between 45 and 49 then 1 end) P4549
     ,sum (case when jk in(1) and umur between 50 and 54 then -1 end) L5054
     ,sum (case when jk in(2) and umur between 50 and 54 then 1 end) P5054
     ,sum (case when jk in(1) and umur between 55 and 59 then -1 end) L5559
     ,sum (case when jk in(2) and umur between 55 and 59 then 1 end) P5559
     ,sum (case when jk in(1) and umur between 60 and 64 then -1 end) L6064
     ,sum (case when jk in(2) and umur between 60 and 64 then 1 end) P6064
     ,sum (case when jk in(1) and umur between 65 and 69 then -1 end) L6569
     ,sum (case when jk in(2) and umur between 65 and 69 then 1 end) P6569
     ,sum (case when jk in(1) and umur between 70 and 74 then -1 end) L7074
     ,sum (case when jk in(2) and umur between 70 and 74 then 1 end) P7074
     ,sum (case when jk in(1) and umur>=75 then -1 end) L75UP
     ,sum (case when jk in(2) and umur>=75  then 1 end) P75UP     
      from
      (
SELECT
      trim(to_char(no_kAB,'00')) NOKEC,no_kab,no_prop
      ,case when jenis_klmin in(1) then  1 else 2 end jk
      ,floor(months_between(b.tgl_akhir,tgl_lhr)/12) umur
FROM biodata_wni a,tgl_aktif b where flag_status='0'
)
GROUP BY no_prop,no_kab;

set termout off;
spool off;
	  
set termout on;
set pagesize 100;
set heading on;
set feedback on;
set linesize80;
exit;

