SELECT

    -- nasabah
    TRIM(nasabah.NASABAH_ID) AS cif
    ,CAST(TRIM(nasabah.NASABAH_ID) AS UNSIGNED) AS int_cif
    ,CASE
        WHEN nasabah.TGL_BUKA = '0000-00-00' THEN '9999-12-31'
        ELSE nasabah.TGL_BUKA
    END AS tanggal_registrasi_cif
    ,TRIM(nasabah.NAMA_NASABAH) AS nama_nasabah
	,CASE
		WHEN nasabah.JENIS_NASABAH='1' THEN 'Individu'
		ELSE 'Non Invidividu'
	END AS jenis_nasabah
    ,TRIM(nasabah.ALAMAT_DOMISILI) AS alamat
    ,TRIM(nasabah.KOTA) AS kota
    ,TRIM(nasabah.NO_HP) AS no_handphone
    ,TRIM(nasabah.EMAIL) AS email
    ,TRIM(nasabah.no_id) AS nik
    ,nasabah.tgllahir AS tanggal_lahir
    ,TRIM(kodegroup1_nasabah.DESKRIPSI_GROUP1) AS agama
    ,TRIM(kodegroup2_nasabah.DESKRIPSI_GROUP2) AS nasabah_group2
    ,TRIM(kodegroup3_nasabah.DESKRIPSI_GROUP3) AS nasabah_group3
    ,TRIM(kodegroup4_nasabah.DESKRIPSI_GROUP4) AS nasabah_group4
	,TRIM(kodegroup5_nasabah.DESKRIPSI_GROUP5) AS nasabah_group5
    ,TRIM(kodegroup6_nasabah.DESKRIPSI_GROUP6) AS nasabah_group6
    ,TRIM(kodegroup7_nasabah.DESKRIPSI_GROUP7) AS nasabah_group7

    -- kredit
    ,TRIM(kredit.NO_REKENING) AS no_rekening
    ,TRIM(kredit.NO_REK_DEBET) AS no_rekening_autodebet
    ,NULL AS kolektibilitas_awal_bulan
    ,TRIM(kredit.KOLEKTIBILITAS) AS kolektibilitas
    ,kredit.TGL_REALISASI AS tgl_realisasi
    ,kredit.SUKU_BUNGA_PER_TAHUN AS suku_bunga
    ,kredit.GRACE_PERIOD AS grace_period
    ,kredit.JML_ANGSURAN AS jangka_waktu
    ,TRIM(kodejeniskredit.DESKRIPSI_JENIS_KREDIT) AS jenis_pinjaman
    ,kredit.TGL_JATUH_TEMPO AS tgl_jatuh_tempo
	,CASE
		WHEN kredit.STATUS_AKTIF IN ('1','2') THEN 'Aktif'
		WHEN kredit.STATUS_AKTIF IN ('3') THEN 'Tutup'
		ELSE 'UNDEFINED'
	END AS status_aktif
    ,kredit.JML_PINJAMAN AS jml_pinjaman
    ,kredit.JML_BUNGA_PINJAMAN AS jml_bunga_pinjaman
    ,kredit.ANGSURAN_POKOK AS angsuran_pokok
    ,kredit.ANGSURAN_BUNGA AS angsuran_bunga
    ,kredit.ANGSURAN_TOTAL AS angsuran_total
    ,0 AS tunggakan_pokok
    ,0 AS tunggakan_pokok_eir
    ,0 AS tunggakan_bunga
    ,0 AS tunggakan_bunga_eir
    ,kredit.POKOK_SALDO_AKHIR AS pokok_saldo_akhir
    ,kredit.pokok_saldo_akhir_eir AS pokok_saldo_akhir_eir
    ,kredit.BUNGA_SALDO_AKHIR AS bunga_saldo_akhir
    ,0 AS ftp
    ,0 AS ftb
    ,'9999-12-31' AS rencana_tgl_trans
    ,0 AS rencana_pokok_trans
    ,0 AS rencana_bunga_trans
    ,TRIM(kredit.KODE_GROUP1) AS kode_marketing
    ,TRIM(kodegroup1kredit.DESKRIPSI_GROUP1) AS marketing
    ,CASE
		WHEN kodegroup2kredit.KODE_GROUP2 = '004' THEN 'Kantor Kas Gajah'
		WHEN kodegroup2kredit.KODE_GROUP2 = '003' THEN 'Kantor Kas Bulusan'
		WHEN kodegroup2kredit.KODE_GROUP2 = '002' THEN 'Kantor Kas Kalipancur'
		ELSE 'Kantor Pusat'
    END AS kantor
    ,TRIM(kodegroup3kredit.DESKRIPSI_GROUP3) AS kode_group3
    ,TRIM(kodegroup4kredit.DESKRIPSI_GROUP4) AS kode_group4
    ,TRIM(kodegroup5kredit.DESKRIPSI_GROUP5) AS kode_group5
    ,TRIM(kodegroup6kredit.DESKRIPSI_GROUP6) AS kode_group6
    ,TRIM(kodegroup7kredit.DESKRIPSI_GROUP7) AS kode_group7

    -- kretrans
    ,kretrans.KRETRANS_ID AS kretrans_id
    ,kretrans.TGL_TRANS AS kretrans_tgl_trans
    ,kretrans.NO_REKENING AS kretrans_no_rekening
    ,kretrans.MY_KODE_TRANS AS kretrans_my_kode_trans
    ,kodetranskredit.KODE_TRANS AS kretrans_kode_trans
    ,kodetranskredit.DESKRIPSI_TRANS AS kretrans_deskripsi_trans
    ,kretrans.transkol AS kretrans_transkol
    ,kretrans.KUITANSI AS kretrans_keterangan
    ,kretrans.POKOK_TRANS AS kretrans_pokok_trans
    ,kretrans.BUNGA_TRANS AS kretrans_bunga_trans
    ,kretrans.ADMIN_TRANS AS kretrans_admin_trans
    ,kretrans.DENDA_TRANS AS kretrans_denda_trans
    ,kretrans.DISC_POKOK AS kretrans_disc_pokok
    ,kretrans.DISC_BUNGA AS kretrans_disc_bunga
    ,kretrans.ZAKAT_TRANS AS kretrans_zakat_trans
    ,kretrans.POKOK_TRANS + kretrans.BUNGA_TRANS + kretrans.ADMIN_TRANS + kretrans.DENDA_TRANS AS kretrans_total_angsuran
    ,kretrans.TransBakiDebet AS kretrans_transbakidebet
    ,kretrans.TransSisaBunga AS kretrans_transsisabunga
    ,kretrans.PELUNASAN AS kretrans_flag_pelunasan

FROM kretrans
JOIN kredit ON kretrans.NO_REKENING = kredit.NO_REKENING
JOIN nasabah ON kredit.NASABAH_ID = nasabah.nasabah_id
LEFT JOIN kodejeniskredit ON kredit.JENIS_PINJAMAN = kodejeniskredit.KODE_JENIS_KREDIT
LEFT JOIN kodetranskredit ON kretrans.KODE_TRANS = kodetranskredit.KODE_TRANS
LEFT JOIN kodegroup1kredit ON kredit.KODE_GROUP1 = kodegroup1kredit.KODE_GROUP1
LEFT JOIN kodegroup2kredit ON kredit.KODE_GROUP2 = kodegroup2kredit.KODE_GROUP2
LEFT JOIN kodegroup3kredit ON kredit.KODE_GROUP3 = kodegroup3kredit.KODE_GROUP3
LEFT JOIN kodegroup4kredit ON kredit.KODE_GROUP4 = kodegroup4kredit.KODE_GROUP4
LEFT JOIN kodegroup5kredit ON kredit.KODE_GROUP5 = kodegroup5kredit.KODE_GROUP5
LEFT JOIN kodegroup6kredit ON kredit.KODE_GROUP6 = kodegroup6kredit.KODE_GROUP6
LEFT JOIN kodegroup7kredit ON kredit.KODE_GROUP7 = kodegroup7kredit.KODE_GROUP7
LEFT JOIN kodegroup1_nasabah ON nasabah.NASABAH_GROUP1 = kodegroup1_nasabah.NASABAH_GROUP1
LEFT JOIN kodegroup2_nasabah ON nasabah.NASABAH_GROUP2 = kodegroup2_nasabah.NASABAH_GROUP2
LEFT JOIN kodegroup3_nasabah ON nasabah.NASABAH_GROUP3 = kodegroup3_nasabah.NASABAH_GROUP3
LEFT JOIN kodegroup4_nasabah ON nasabah.NASABAH_GROUP4 = kodegroup4_nasabah.NASABAH_GROUP4
LEFT JOIN kodegroup5_nasabah ON nasabah.NASABAH_GROUP5 = kodegroup5_nasabah.NASABAH_GROUP5
LEFT JOIN kodegroup6_nasabah ON nasabah.NASABAH_GROUP6 = kodegroup6_nasabah.NASABAH_GROUP6
LEFT JOIN kodegroup7_nasabah ON nasabah.NASABAH_GROUP7 = kodegroup7_nasabah.NASABAH_GROUP7
WHERE kretrans.MY_KODE_TRANS NOT LIKE '2%'
ORDER BY kretrans.KRETRANS_ID