Cara buat VIEW untuk Master Data Perekaman KTP-el di Database SIAK

Cerita sedikit, penting gak adanya master data Perekaman?? Jawabnya penting gak penting.. kenapa karena kita kadang di tanya mencongak, berapa jumlah perekaman sama bos2 pemangku jabatan, atau berapa yg duplikat, berapa yg SFE berapa bla bla bla... jadi inti masalahnya kita perlu buat VIEW yg mirror ke semua table perekaman, pencetakan dan demographics terakhir versi pusat. nah kalo musti jalanin script ini tiap hari sih boleh2 aja... tapi akan lebih baiknya kita buat VIEW supaya nantinya kalo mau sanding2 data lebih efektif dan efisien, serta ramah lingkungan...
oke pertama kita harus paham skema,


  1. Kita bakal bekerja dan nge-remot database SIAKOFF,
  2. Di database SIAKOFF sudah ada DB-link, lagi2 db-link, ini cuma pengingat doang ya... karena harusnya kalo sudah ada, gak usah buat lagi, belum ada DB-link? liat cara membuat DB-LINK di postingan lama.
  3. Demographics bulanan yg di download dari FTP dan sudah ter-Import di Database SIAKOFF,
Oke Lanjut ya... jadi caranya itu simple seharusnya tgl copas script di bawah ini, F9, kelar idup loe... 
Jadi nomor 3 diatas, setelah import table yg di download dari FTP, silahkan rename Table nya jadi demographics, kalo sebelumnya sudah ada table demographics di siakoff, bisa di drop atau kalo masih mau ngoleksi, atau bisa di rename dulu jadi apalah apalah.. karena script ini ngebaca table dengan nama demographics 
Catatan Lainnya : 
1. Table SETUP_APLIKASI Harus sudah terisi...
2. Di script ini Nama DB-link nya @ktporcl dan @ktplocal sesuaikan dg db-link yg di punya... 
3. Nama VIEW di script dibawah MASTER_DATA_PEREKAMAN
4. Bisa di sesuaikan atau ditambah kolom untuk viewnya, silahkan Explore Sendiri
LANGSUNG SCRIPT NYA yaaa.....

DROP VIEW SIAKOFF.MASTER_DATA_PEREKAMAN;

/* Formatted on 6/2/2020 11:22:47 PM (QP5 v5.326) */
CREATE OR REPLACE FORCE VIEW SIAKOFF.MASTER_DATA_PEREKAMAN
(
    NIK,
    NO_KK,
    NAMA_LGKP,
    TMPT_LHR,
    TGL_LHR,
    JENIS_KLMIN,
    NO_PROP,
    NO_KAB,
    NO_KEC,
    NO_KEL,
    NAMA_LGKP_AYAH,
    NAMA_LGKP_IBU,
    SUMBER,
    FLAG_STATUS,
    CURRENT_STATUS_CODE,
    CREATED,
    CREATED_USERNAME,
    LAST_UPDATED,
    LAST_UPDATED_USERNAME,
    UPLOAD_LOCATION
)
AS
    SELECT a.nik
               AS NIK,
           (CASE
                WHEN a.nik IN (SELECT nik
                                 FROM biodata_wni
                                WHERE flag_status = 0)
                THEN
                    (SELECT b.NO_KK
                       FROM BIODATA_WNI b
                      WHERE a.nik = b.nik)
                ELSE
                    a.NO_KK
            END)
               AS NO_KK,
           (CASE
                WHEN a.nik IN (SELECT nik
                                 FROM biodata_wni
                                WHERE flag_status = 0)
                THEN
                    (SELECT UPPER (b.NAMA_LGKP)
                       FROM BIODATA_WNI b
                      WHERE a.nik = b.nik)
                ELSE
                    UPPER (a.NAMA_LGKP)
            END)
               AS NAMA_LGKP,
           (CASE
                WHEN a.nik IN (SELECT nik
                                 FROM biodata_wni
                                WHERE flag_status = 0)
                THEN
                    (SELECT UPPER (b.TMPT_LHR)
                       FROM BIODATA_WNI b
                      WHERE a.nik = b.nik)
                ELSE
                    UPPER (a.TMPT_LHR)
            END)
               AS TMPT_LHR,
           (CASE
                WHEN a.nik IN (SELECT nik
                                 FROM biodata_wni
                                WHERE flag_status = 0)
                THEN
                    (SELECT TO_CHAR (b.TGL_LHR, 'DD-MM-YYYY')
                       FROM BIODATA_WNI b
                      WHERE a.nik = b.nik)
                ELSE
                    TO_CHAR (a.TGL_LHR, 'DD-MM-YYYY')
            END)
               AS TGL_LHR,
           (CASE
                WHEN a.nik IN (SELECT nik
                                 FROM biodata_wni
                                WHERE flag_status = 0)
                THEN
                    (SELECT DECODE (b.JENIS_KLMIN,
                                    1, 'LAKI-LAKI',
                                    2, 'PEREMPUAN')
                                JENIS_KLMIN
                       FROM BIODATA_WNI b
                      WHERE a.nik = b.nik)
                ELSE
                    a.JENIS_KLMIN
            END)
               AS JENIS_KLMIN,
           (CASE
                WHEN a.nik IN (SELECT nik
                                 FROM biodata_wni
                                WHERE flag_status = 0)
                THEN
                    (SELECT b.NO_PROP
                       FROM BIODATA_WNI b
                      WHERE a.nik = b.nik)
                ELSE
                    a.no_prop
            END)
               AS NO_PROP,
           (CASE
                WHEN a.nik IN (SELECT nik
                                 FROM biodata_wni
                                WHERE flag_status = 0)
                THEN
                    (SELECT b.NO_KAB
                       FROM BIODATA_WNI b
                      WHERE a.nik = b.nik)
                ELSE
                    a.NO_KAB
            END)
               AS NO_KAB,
           (CASE
                WHEN a.nik IN (SELECT nik
                                 FROM biodata_wni
                                WHERE flag_status = 0)
                THEN
                    (SELECT b.NO_KEC
                       FROM BIODATA_WNI b
                      WHERE a.nik = b.nik)
                ELSE
                    a.no_KEC
            END)
               AS NO_KEC,
           (CASE
                WHEN a.nik IN (SELECT nik
                                 FROM biodata_wni
                                WHERE flag_status = 0)
                THEN
                    (SELECT b.NO_KEL
                       FROM BIODATA_WNI b
                      WHERE a.nik = b.nik)
                ELSE
                    a.no_KEL
            END)
               AS NO_KEL,
           (CASE
                WHEN a.nik IN (SELECT nik
                                 FROM biodata_wni
                                WHERE flag_status = 0)
                THEN
                    (SELECT b.nama_lgkp_ayah
                       FROM BIODATA_WNI b
                      WHERE a.nik = b.nik)
                ELSE
                    a.nama_lgkp_ayah
            END)
               AS nama_lgkp_ayah,
           (CASE
                WHEN a.nik IN (SELECT nik
                                 FROM biodata_wni
                                WHERE flag_status = 0)
                THEN
                    (SELECT b.nama_lgkp_ibu
                       FROM BIODATA_WNI b
                      WHERE a.nik = b.nik)
                ELSE
                    a.nama_lgkp_ibu
            END)
               AS nama_lgkp_ibu,
           (CASE
                WHEN a.nik IN (SELECT nik
                                 FROM biodata_wni
                                WHERE flag_status = 0)
                THEN
                    'SIAKOFF'
                ELSE
                    'DEMOGRAPHICS'
            END)
               AS SUMBER,
           (CASE
                WHEN a.nik IN (SELECT nik FROM biodata_wni)
                THEN
                    (SELECT DECODE (b.flag_status,
                                    0, 'AKTIF',
                                    1, 'MATI',
                                    2, 'PINDAH DALAM NEGRI',
                                    3, 'PINDAH LUAR NEGRI',
                                    4, 'GANDA',
                                    8, 'PINDAH SUDAH LAPOR')
                       FROM BIODATA_WNI b
                      WHERE a.nik = b.nik)
                ELSE
                    'TIDAK TAHU'
            END)
               AS flag_status,
           a.CURRENT_STATUS_CODE
               AS CURRENT_STATUS_CODE,
           a.CREATED
               AS CREATED,
           a.created_username
               AS CREATED_USERNAME,
           a.last_updated
               AS LAST_UPDATED,
           a.last_updated_username
               AS last_updated_username,
           a.upload_location
               AS upload_location
      FROM (SELECT NIK,
                   NO_KK,
                   NO_PROP,
                   NO_KAB,
                   NO_KEC,
                   NO_KEL,
                   NAMA_LGKP,
                   TMPT_LHR,
                   JENIS_KLMIN,
                   TGL_LHR,
                   CREATED,
                   CURRENT_STATUS_CODE,
                   ALAMAT,
                   created_username,
                   last_updated,
                   last_updated_username,
                   upload_location,
                   nama_lgkp_ayah,
                   nama_lgkp_ibu
              FROM demographics@ktporcl aa
             WHERE     last_updated >
                       (SELECT MAX (last_updated) FROM demographics)
                   AND (NO_PROP, NO_KAB) IN
                           (SELECT NO_PROP, NO_KAB FROM SETUP_APLIKASI)
            UNION
            SELECT NIK,
                   NO_KK,
                   NO_PROP,
                   NO_KAB,
                   NO_KEC,
                   NO_KEL,
                   NAMA_LGKP,
                   TMPT_LHR,
                   JENIS_KLMIN,
                   TGL_LHR,
                   CREATED,
                   CURRENT_STATUS_CODE,
                   ALAMAT,
                   created_username,
                   last_updated,
                   last_updated_username,
                   upload_location,
                   nama_lgkp_ayah,
                   nama_lgkp_ibu
              FROM demographics bb
             WHERE nik NOT IN
                       (SELECT NIK
                          FROM demographics@ktporcl aa
                         WHERE     last_updated >
                                   (SELECT MAX (last_updated)
                                      FROM demographics)
                               AND (NO_PROP, NO_KAB) IN
                                       (SELECT NO_PROP, NO_KAB
                                          FROM SETUP_APLIKASI))
            UNION
            SELECT NIK,
                   NO_KK,
                   NO_PROP,
                   NO_KAB,
                   NO_KEC,
                   NO_KEL,
                   NAMA_LGKP,
                   TMPT_LHR,
                   JENIS_KLMIN,
                   TGL_LHR,
                   CREATED,
                   CURRENT_STATUS_CODE,
                   ALAMAT,
                   created_username,
                   last_updated,
                   last_updated_username,
                   upload_location,
                   nama_lgkp_ayah,
                   nama_lgkp_ibu
              FROM demographics@ktplocal cc
             WHERE     (   LAST_UPDATED >
                           (SELECT MAX (last_updated) FROM demographics)
                        OR CURRENT_STATUS_CODE IN
                               ('BIO_CAPTURED',
                                'SENT_FOR_ENROLLMENT',
                                'PROCESSING'))
                   AND (NO_PROP, NO_KAB) IN
                           (SELECT NO_PROP, NO_KAB FROM SETUP_APLIKASI)
                   AND nik NOT IN (SELECT nik
                                     FROM demographics bb)
                   AND nik NOT IN
                           (SELECT NIK
                              FROM demographics@ktporcl aa
                             WHERE     last_updated >
                                       (SELECT MAX (last_updated)
                                          FROM demographics)
                                   AND (NO_PROP, NO_KAB) IN
                                           (SELECT NO_PROP, NO_KAB
                                              FROM SETUP_APLIKASI))) a;
Semoga bermanfaat,

Subscribe to receive free email updates:

0 Response to "Cara buat VIEW untuk Master Data Perekaman KTP-el di Database SIAK"

Posting Komentar