SELECT DISTINCT

    TRIM(kredit.NO_REKENING) AS no_rekening
    ,TRIM(kredit.NASABAH_ID) AS cif
    ,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

FROM kretrans
JOIN kredit ON kretrans.NO_REKENING = kredit.NO_REKENING
LEFT JOIN kodejeniskredit ON kredit.JENIS_PINJAMAN = kodejeniskredit.KODE_JENIS_KREDIT
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
WHERE kretrans.MY_KODE_TRANS NOT LIKE '2%'
ORDER BY kretrans.KRETRANS_ID