--update Selaraskan data dkb sesuai kondisi :

alter session set nls_date_format='dd-mm-yyyy';

update BIODATA_WNI set stat_hbkel=9
where (jenis_klmin=1 and stat_hbkel=3)
or (jenis_klmin=2 and stat_hbkel=2)
or (floor(months_between('31-12-2015',tgl_lhr)/12)<14 and stat_hbkel in (1,2,3,7,8));
commit;

update BIODATA_WNI set jenis_pkrjn=88
where (jenis_klmin =1 and jenis_pkrjn in(73,87))
or (jenis_klmin=2 and jenis_pkrjn in(41,42,43))
or (agama=1 and jenis_pkrjn in(42,43,87))
or (agama<>1 and jenis_pkrjn in(41,45))
or (agama<>3 and jenis_pkrjn=43)
or (jenis_klmin=1 and jenis_pkrjn=2)
or (floor(months_between('31-12-2015',tgl_lhr)/12)>30 and jenis_pkrjn=3 and pddk_akh in(1,2,3,4,5,6));
commit;

update BIODATA_WNI set jenis_pkrjn=1
where jenis_pkrjn<1 or jenis_pkrjn>89
or jenis_pkrjn is null
or (floor(months_between('31-12-2015',tgl_lhr)/12)<15 and jenis_pkrjn not in(1,3,89));
commit;

update BIODATA_WNI set stat_kwn=1 where floor(months_between('31-12-2015',tgl_lhr)/12)<14 and stat_kwn<>1;
commit;
update BIODATA_WNI set stat_kwn=2 where stat_kwn=1 and stat_hbkel in (2,3);
commit;
update BIODATA_WNI set gol_drh=13 where gol_drh<1 or gol_drh>13 or gol_drh is null;
commit;
update BIODATA_WNI set pddk_akh=1 where pddk_akh<1 or pddk_akh>10 or pddk_akh is null;
commit;

update BIODATA_WNI set pddk_akh=1 where pddk_akh>1 and floor(months_between('31-12-2015',tgl_lhr)/12)<6;
commit;
update BIODATA_WNI set pddk_akh=2 where pddk_akh>2 and floor(months_between('31-12-2015',tgl_lhr)/12)>=6 and floor(months_between('31-12-2015',tgl_lhr)/12)<12;
commit;
update BIODATA_WNI set pddk_akh=3 where pddk_akh>3 and floor(months_between('31-12-2015',tgl_lhr)/12)>=12 and floor(months_between('31-12-2015',tgl_lhr)/12)<15;
commit;
update BIODATA_WNI set pddk_akh=4 where pddk_akh>4 and floor(months_between('31-12-2015',tgl_lhr)/12)>=15 and floor(months_between('31-12-2015',tgl_lhr)/12)<18;
commit;
update BIODATA_WNI set pddk_akh=5 where pddk_akh>5 and floor(months_between('31-12-2015',tgl_lhr)/12)>=18 and floor(months_between('31-12-2015',tgl_lhr)/12)<21;
commit;

drop table upd_stat_hbkel_01;
create table upd_stat_hbkel_01 nologging as
SELECT NO_KK
 FROM BIODATA_WNI A
        WHERE NOT EXISTS(SELECT 1 FROM
                                        (
                                            SELECT NO_KK
                                            FROM BIODATA_WNI
                                            WHERE STAT_HBKEL=1
                                            GROUP BY NO_KK
                                         )WHERE A.NO_KK=NO_KK
                                       )AND STAT_HBKEL=3                                     
GROUP BY NO_KK
HAVING COUNT(1)=1;

DELETE FROM upd_stat_hbkel_01 A 
WHERE EXISTS(SELECT 1 FROM BIODATA_WNA WHERE A.NO_KK=NO_KK AND STAT_HBKEL=1);
COMMIT;

create table upd_stat_hbkel_02 nologging as
SELECT NIK,NO_KK FROM BIODATA_WNI A 
WHERE EXISTS(SELECT 1 FROM upd_stat_hbkel_01 WHERE A.NO_KK=NO_KK)
AND STAT_HBKEL=2;

COMMIT;

UPDATE BIODATA_WNI A SET STAT_HBKEL=1
WHERE EXISTS(SELECT 1 FROM upd_stat_hbkel_02 WHERE A.NO_KK=NO_KK)
AND STAT_HBKEL=2;

COMMIT;

UPDATE BIODATA_WNI A SET STAT_HBKEL=1
WHERE EXISTS(SELECT 1 FROM upd_stat_hbkel_01 WHERE A.NO_KK=NO_KK)
AND NOT EXISTS(SELECT 1 FROM upd_stat_hbkel_02 WHERE A.NO_KK=NO_KK)
AND STAT_HBKEL=3;

                             
COMMIT;

drop table upd_stat_hbkel_01 purge;
drop table upd_stat_hbkel_02 purge;

create table upd_stat_hbkel nologging as
SELECT NO_KK
 FROM BIODATA_WNI A
        WHERE NOT EXISTS(SELECT 1 FROM
                                        (
                                            SELECT NO_KK
                                            FROM BIODATA_WNI
                                            WHERE STAT_HBKEL=1
                                            GROUP BY NO_KK
                                         )WHERE A.NO_KK=NO_KK
                                       )AND STAT_HBKEL=2
GROUP BY NO_KK
HAVING COUNT(1)=1;

UPDATE BIODATA_WNI A SET STAT_HBKEL=1
WHERE EXISTS(SELECT 1 FROM upd_stat_hbkel WHERE A.NO_KK=NO_KK)
AND STAT_HBKEL=2;
  
COMMIT;
                
drop table upd_stat_hbkel purge;
                
CREATE TABLE ISTRI_LBH_DARI_1_01 NOLOGGING AS
SELECT DENSE_RANK()OVER(PARTITION BY A.NO_KK ORDER BY A.TGL_LHR,ROWNUM ASC)RANKING,A.NIK,A.NO_KK,A.STAT_HBKEL 
FROM BIODATA_WNI A,(
SELECT NO_KK FROM BIODATA_WNI A
        WHERE NOT EXISTS(SELECT 1 FROM
                                        (
                                            SELECT NO_KK
                                            FROM BIODATA_WNI
                                            WHERE STAT_HBKEL=1
                                            GROUP BY NO_KK
                                         )WHERE A.NO_KK=NO_KK
                                       )AND STAT_HBKEL=3
GROUP BY NO_KK
HAVING COUNT(1)>1)B
WHERE A.NO_KK=B.NO_KK
AND STAT_HBKEL=3;

DELETE FROM ISTRI_LBH_DARI_1_01 A 
WHERE EXISTS(SELECT 1 FROM BIODATA_WNA WHERE A.NO_KK=NO_KK AND STAT_HBKEL=1);
COMMIT;

create table ISTRI_LBH_DARI_1_02 nologging as
SELECT NIK,NO_KK FROM BIODATA_WNI A 
WHERE EXISTS(SELECT 1 FROM ISTRI_LBH_DARI_1_01 WHERE A.NO_KK=NO_KK)
AND STAT_HBKEL=2;
COMMIT;

UPDATE BIODATA_WNI A SET STAT_HBKEL=1
WHERE EXISTS(SELECT 1 FROM ISTRI_LBH_DARI_1_02 WHERE A.NO_KK=NO_KK)
AND STAT_HBKEL=2;
COMMIT;

UPDATE BIODATA_WNI A SET STAT_HBKEL=1
WHERE EXISTS(SELECT 1 FROM ISTRI_LBH_DARI_1_01 WHERE A.NIK=NIK AND RANKING=1)
AND NOT EXISTS(SELECT 1 FROM ISTRI_LBH_DARI_1_02 WHERE A.NO_KK=NO_KK);
COMMIT;

UPDATE BIODATA_WNI A SET STAT_HBKEL=9
WHERE EXISTS(SELECT 1 FROM ISTRI_LBH_DARI_1_01 WHERE A.NIK=NIK AND RANKING>1);
COMMIT;

DROP TABLE ISTRI_LBH_DARI_1_01 PURGE;
DROP TABLE ISTRI_LBH_DARI_1_02 PURGE;

CREATE TABLE SUAMI_LBH_DARI_1 NOLOGGING AS
SELECT DENSE_RANK()OVER(PARTITION BY A.NO_KK ORDER BY A.TGL_LHR,ROWNUM ASC)RANKING,A.NIK,A.NO_KK,A.STAT_HBKEL 
FROM BIODATA_WNI A,(
SELECT NO_KK FROM BIODATA_WNI A
        WHERE NOT EXISTS(SELECT 1 FROM
                        (
                            SELECT NO_KK
                            FROM BIODATA_WNI
                            WHERE STAT_HBKEL=1
                            GROUP BY NO_KK
                         )WHERE A.NO_KK=NO_KK
                       )AND STAT_HBKEL=2
GROUP BY NO_KK
HAVING COUNT(1)>1)B
WHERE A.NO_KK=B.NO_KK
AND STAT_HBKEL=2;

DELETE FROM SUAMI_LBH_DARI_1 A 
WHERE EXISTS(SELECT 1 FROM BIODATA_WNA WHERE A.NO_KK=NO_KK AND STAT_HBKEL=1);
COMMIT;

UPDATE BIODATA_WNI A SET STAT_HBKEL=1
WHERE EXISTS(SELECT 1 FROM SUAMI_LBH_DARI_1 WHERE A.NIK=NIK AND RANKING=1);
COMMIT;

UPDATE BIODATA_WNI A SET STAT_HBKEL=9
WHERE EXISTS(SELECT 1 FROM SUAMI_LBH_DARI_1 WHERE A.NIK=NIK AND RANKING>1);
COMMIT;

DROP TABLE SUAMI_LBH_DARI_1 PURGE;

CREATE TABLE KK_LBH_DARI_1_01 NOLOGGING AS
SELECT DENSE_RANK()OVER(PARTITION BY A.NO_KK ORDER BY A.TGL_LHR,ROWNUM ASC)RANKING,A.NIK,A.NO_KK,A.STAT_HBKEL 
FROM BIODATA_WNI A,(
SELECT NO_KK
        FROM BIODATA_WNI
        WHERE STAT_HBKEL=1
GROUP BY NO_KK
HAVING COUNT(1)>1)B
WHERE A.NO_KK=B.NO_KK
AND STAT_HBKEL=1;

CREATE TABLE KK_LBH_DARI_1_02 NOLOGGING AS
SELECT NO_KK FROM BIODATA_WNA A 
WHERE EXISTS(SELECT 1 FROM KK_LBH_DARI_1_01 WHERE A.NO_KK=NO_KK )
AND STAT_HBKEL=1;

CREATE TABLE KK_LBH_DARI_1_03 NOLOGGING AS
SELECT NIK,NO_KK FROM BIODATA_WNI A
WHERE EXISTS(SELECT 1 FROM KK_LBH_DARI_1_02 WHERE A.NO_KK=NO_KK)
AND EXISTS(SELECT 1 FROM data_keluarga WHERE A.NO_KK=NO_KK AND A.NAMA_LGKP=NAMA_KEP)
AND STAT_HBKEL=1;

UPDATE BIODATA_WNI A SET STAT_HBKEL=1
WHERE EXISTS(SELECT 1 FROM KK_LBH_DARI_1_03 WHERE A.NIK=NIK);
COMMIT;

UPDATE BIODATA_WNI A SET STAT_HBKEL=9
WHERE EXISTS(SELECT 1 FROM KK_LBH_DARI_1_03 WHERE A.NO_KK=NO_KK)
AND STAT_HBKEL=1;
COMMIT;

CREATE TABLE KK_LBH_DARI_1_04 NOLOGGING AS
SELECT NO_KK FROM KK_LBH_DARI_1_01 A 
WHERE NOT EXISTS(SELECT 1 FROM BIODATA_WNA WHERE A.NO_KK=NO_KK AND STAT_HBKEL=1);

CREATE TABLE KK_LBH_DARI_1_05 NOLOGGING AS
SELECT NIK,NO_KK FROM BIODATA_WNI A
WHERE EXISTS(SELECT 1 FROM KK_LBH_DARI_1_04 WHERE A.NO_KK=NO_KK)
AND EXISTS(SELECT 1 FROM data_keluarga WHERE A.NO_KK=NO_KK AND A.NAMA_LGKP=NAMA_KEP)
AND STAT_HBKEL=1;

UPDATE BIODATA_WNI A SET STAT_HBKEL=1
WHERE EXISTS(SELECT 1 FROM KK_LBH_DARI_1_05 WHERE A.NIK=NIK);
COMMIT;

UPDATE BIODATA_WNI A SET STAT_HBKEL=9
WHERE EXISTS(SELECT 1 FROM KK_LBH_DARI_1_04 WHERE A.NO_KK=NO_KK)
AND NOT EXISTS(SELECT 1 FROM KK_LBH_DARI_1_05 WHERE A.NIK=NIK)
AND STAT_HBKEL=1;
COMMIT;

CREATE TABLE KK_LBH_DARI_1_06 NOLOGGING AS
SELECT DENSE_RANK()OVER(PARTITION BY A.NO_KK ORDER BY A.TGL_LHR,ROWNUM ASC)RANKING,A.NIK,A.NO_KK,A.STAT_HBKEL 
FROM BIODATA_WNI A,(
SELECT NO_KK
        FROM BIODATA_WNI
        WHERE STAT_HBKEL=1
GROUP BY NO_KK
HAVING COUNT(1)>1)B
WHERE A.NO_KK=B.NO_KK
AND STAT_HBKEL=1;

UPDATE BIODATA_WNI A SET STAT_HBKEL=1
WHERE EXISTS(SELECT 1 FROM KK_LBH_DARI_1_06 WHERE A.NIK=NIK AND RANKING=1);
COMMIT;

UPDATE BIODATA_WNI A SET STAT_HBKEL=9
WHERE EXISTS(SELECT 1 FROM KK_LBH_DARI_1_06 WHERE A.NIK=NIK AND RANKING>1);
COMMIT;

DROP TABLE KK_LBH_DARI_1_01 PURGE;
DROP TABLE KK_LBH_DARI_1_02 PURGE;
DROP TABLE KK_LBH_DARI_1_03 PURGE;
DROP TABLE KK_LBH_DARI_1_04 PURGE;
DROP TABLE KK_LBH_DARI_1_05 PURGE;
DROP TABLE KK_LBH_DARI_1_06 PURGE;

CREATE TABLE ISTRI_LBH_DARI_1 NOLOGGING AS
SELECT DENSE_RANK()OVER(PARTITION BY A.NO_KK ORDER BY A.TGL_LHR,ROWNUM ASC)RANKING,A.NIK,A.NO_KK,A.STAT_HBKEL 
FROM BIODATA_WNI A,(
SELECT NO_KK
        FROM BIODATA_WNI
        WHERE STAT_HBKEL=3
GROUP BY NO_KK
HAVING COUNT(1)>1)B
WHERE A.NO_KK=B.NO_KK
AND STAT_HBKEL=3;

UPDATE BIODATA_WNI A SET STAT_HBKEL=9
WHERE EXISTS(SELECT 1 FROM ISTRI_LBH_DARI_1 WHERE A.NIK=NIK AND RANKING>1);
COMMIT;

DROP TABLE ISTRI_LBH_DARI_1 PURGE;
DROP TABLE TEMP_STAT_HBKEL_1 PURGE;
 CREATE TABLE TEMP_STAT_HBKEL_1 NOLOGGING AS
 SELECT DENSE_RANK()OVER(PARTITION BY A.NO_KK ORDER BY A.TGL_LHR,ROWNUM ASC)RANKING,A.NIK,A.NO_KK,A.STAT_HBKEL 
 FROM BIODATA_WNI A,(
 SELECT NO_KK 
 FROM BIODATA_WNI A 
 WHERE NOT EXISTS(SELECT 1 FROM (
                        SELECT NO_KK 
                        FROM BIODATA_WNI 
                        WHERE STAT_HBKEL IN(1,2)
                        )WHERE A.NO_KK=NO_KK
                )AND STAT_HBKEL=3
 GROUP BY NO_KK
 HAVING COUNT(1)>1)B
 WHERE A.NO_KK=B.NO_KK
 AND STAT_HBKEL=3;
                
 UPDATE BIODATA_WNI A SET STAT_HBKEL=1
 WHERE EXISTS(SELECT 1 FROM TEMP_STAT_HBKEL_1 WHERE A.NIK=NIK AND RANKING=1);
 COMMIT;

 UPDATE BIODATA_WNI A SET STAT_HBKEL=9
 WHERE EXISTS(SELECT 1 FROM TEMP_STAT_HBKEL_1 WHERE A.NIK=NIK AND RANKING>1);
COMMIT;

DROP TABLE TEMP_STAT_HBKEL_1 PURGE;
CREATE TABLE TEMP_STAT_HBKEL_1 NOLOGGING AS
 SELECT DENSE_RANK()OVER(PARTITION BY A.NO_KK ORDER BY A.TGL_LHR,ROWNUM ASC)RANKING,A.NIK,A.NO_KK,A.STAT_HBKEL 
 FROM BIODATA_WNI A,(
 SELECT NO_KK 
 FROM BIODATA_WNI A 
 WHERE NOT EXISTS(SELECT 1 FROM (
                        SELECT NO_KK 
                        FROM BIODATA_WNI 
                        WHERE STAT_HBKEL IN(1,2,3)
                        )WHERE A.NO_KK=NO_KK
                )AND STAT_HBKEL=3
 GROUP BY NO_KK
 HAVING COUNT(1)>1)B
 WHERE A.NO_KK=B.NO_KK
 AND STAT_HBKEL=3;

UPDATE BIODATA_WNI A SET STAT_HBKEL=1
WHERE EXISTS(SELECT 1 FROM TEMP_STAT_HBKEL_1 WHERE A.NIK=NIK AND RANKING=1);
COMMIT;
UPDATE BIODATA_WNI A SET STAT_HBKEL=9
WHERE EXISTS(SELECT 1 FROM TEMP_STAT_HBKEL_1 WHERE A.NIK=NIK AND RANKING>1);
COMMIT;

DROP TABLE TEMP_STAT_HBKEL_1 PURGE;
CREATE TABLE TEMP_STAT_HBKEL_1 NOLOGGING AS
 SELECT DENSE_RANK()OVER(PARTITION BY A.NO_KK ORDER BY A.TGL_LHR,ROWNUM ASC)RANKING,A.NIK,A.NO_KK,A.STAT_HBKEL 
 FROM BIODATA_WNI A,(
 SELECT NO_KK 
 FROM BIODATA_WNI A 
 WHERE NOT EXISTS(SELECT 1 FROM (SELECT NO_KK 
                FROM BIODATA_WNI 
                 WHERE STAT_HBKEL IN(1,2,3)
                 )WHERE A.NO_KK=NO_KK
        )
 GROUP BY NO_KK
 HAVING COUNT(1)>1)B
 WHERE A.NO_KK=B.NO_KK;

UPDATE BIODATA_WNI A SET STAT_HBKEL=1
WHERE EXISTS(SELECT 1 FROM TEMP_STAT_HBKEL_1 WHERE A.NIK=NIK AND RANKING=1);
COMMIT;
UPDATE BIODATA_WNI A SET STAT_HBKEL=9
WHERE EXISTS(SELECT 1 FROM TEMP_STAT_HBKEL_1 WHERE A.NIK=NIK AND RANKING>1);
COMMIT;

update BIODATA_WNI set stat_hbkel=9
where (jenis_klmin=1 and stat_hbkel=3)
or (jenis_klmin=2 and stat_hbkel=2)
or (floor(months_between('31-12-2015',tgl_lhr)/12)<11 and stat_hbkel in (1,2,3,7,8));
commit;

INSERT /*+ APPEND */ into data_keluarga
    (no_kk,nama_kep,alamat,no_kel,no_kec,no_kab,no_prop,
     tgl_insertion, tgl_updation, pflag, cflag, sync_flag, tipe_kk, nik_kk, count_kk)
SELECT to_char(no_kk),nama_lgkp as nama_kep,
            getnamakel(no_kel,no_kec,no_kab,no_prop) as alamat,
            no_kel,no_kec,no_kab,no_prop,
            tgl_entri as tgl_insertion, '' as tgl_updation, 
            'N' pflag, 'N' cflag, 0 sync_flag, '1' as tipe_kk, nik nik_kk, 0 count_kk
FROM BIODATA_WNI
WHERE stat_hbkel in (select distinct stat_hbkel from BIODATA_WNI where stat_hbkel=1) 
            and no_kk in (select a.no_kk from BIODATA_WNI a left join data_keluarga b
                                 on a.no_kk=b.no_kk where b.no_kk is null);
commit;

update BIODATA_WNI set stat_hbkel=1 
where
    (months_between(to_date('31-12-2015','DD-MM-YYYY'),tgl_lhr)/12) in
        (select max(months_between(to_date('31-12-2015','DD-MM-YYYY'),tgl_lhr)/12)
        from biodata_wni 
        where no_kk in(select a.no_kk from data_keluarga a left join BIODATA_WNI b on
            a.no_kk=b.no_kk and b.stat_hbkel =1
            where b.no_kk is null
            group by a.no_kk)
        group by no_kk)
    and
    no_kk in (select no_kk from BIODATA_WNI 
            where no_kk in(select a.no_kk from data_keluarga a left join BIODATA_WNI b on a.no_kk=b.no_kk and b.stat_hbkel =1 
                where b.no_kk is null group by a.no_kk) 
           group by no_kk)
    and (floor(months_between('31-12-2015',tgl_lhr)/12)>=17 or stat_kwn>1);
commit;

DROP TABLE TEMP_STAT_HBKEL_1 PURGE;
CREATE TABLE TEMP_STAT_HBKEL_1 AS 
SELECT A.NO_KK NO_KK,NIK,NAMA_LGKP NAMA_KEP
FROM BIODATA_WNI A, data_keluarga B
WHERE A.NO_KK=B.NO_KK AND A.NAMA_LGKP<>B.NAMA_KEP AND STAT_HBKEL=1;

BEGIN
FOR ROW_APP IN(SELECT NO_KK,NAMA_KEP,NIK FROM TEMP_STAT_HBKEL_1)
    LOOP
        BEGIN
            UPDATE data_keluarga SET NAMA_KEP=ROW_APP.NAMA_KEP,NIK_KK=ROW_APP.NIK WHERE NO_KK=ROW_APP.NO_KK;
            COMMIT;
       END; 
    END LOOP;
END;
/

DROP TABLE TEMP_STAT_HBKEL_1 PURGE;
PURGE Recyclebin;

delete data_keluarga a 
where not exists(select 1 from BIODATA_WNI where a.no_kk=no_kk);
commit;
