SELECT
    TRIM(deposito.NO_REKENING) AS no_rekening
    ,TRIM(deposito.NO_ALTERNATIF) AS no_bilyet
    ,deposito.JKW AS jangka_waktu
    ,deposito.SUKU_BUNGA AS suku_bunga
    ,deposito.BUNGA_BERBUNGA AS flag_bunga_masuk_pokok
    ,deposito.TGL_REGISTRASI AS tanggal_registrasi
    ,deposito.TGL_MULAI AS tanggal_pembukaan
    ,deposito.TGL_JT AS tanggal_jatuh_tempo
    ,kodejenisdeposito.KODE_JENIS_DEPOSITO AS kode_jenis_deposito
    ,kodejenisdeposito.DESKRIPSI_JENIS_DEPOSITO AS jenis_deposito
    ,deposito.JML_DEPOSITO AS nominal_deposito
    ,CASE
        WHEN deptrans.KUITANSI LIKE '%ARO%' THEN deposito.JML_DEPOSITO
        ELSE 0
    END AS nominal_outstanding
    ,TRIM(deposito.STATUS_BUNGA) AS status_bunga
    ,TRIM(deposito.qq) AS qq
    ,TRIM(deposito.and_or) AS and_or
    ,TRIM(deposito.nasabah_qq) AS nasabah_qq
    ,TRIM(deposito.NO_REK_TABUNGAN) AS no_rekening_tabungan
    ,CASE
        WHEN deposito.STATUS_AKTIF = '1' THEN 'Baru'
        WHEN deposito.STATUS_AKTIF = '2' THEN 'Aktif'
        WHEN deposito.STATUS_AKTIF = '3' THEN 'Tutup'
        ELSE 'Invalid'
    END AS status_aktif
    ,TRIM(kodegroup1deposito.KODE_GROUP1) AS kode_marketing_deposito
    ,TRIM(kodegroup1deposito.DESKRIPSI_GROUP1) AS nama_marketing_deposito
    ,TRIM(kodegroup2deposito.DESKRIPSI_GROUP2) AS kode_group2
    ,TRIM(kodegroup3deposito.DESKRIPSI_GROUP3) AS kode_group3
    ,TRIM(kodegroup4_deposito.DESKRIPSI_GROUP4) AS kode_group4
    ,TRIM(kodegroup5deposito.DESKRIPSI_GROUP5) AS kode_group5

    ,TRIM(nasabah.NASABAH_ID) AS cif
    ,nasabah.TGL_BUKA 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

    ,deptrans.DEPTRANS_ID AS deptrans_id
    ,TRIM(deptrans.NO_REKENING) AS deptrans_no_rekening
    ,deptrans.TGL_TRANS AS deptrans_tgl_trans
    ,TRIM(deptrans.MY_KODE_TRANS) AS deptrans_my_kode_trans
    ,TRIM(kodetransdeposito.KODE_TRANS) AS deptrans_kode_trans
    ,CASE
        WHEN deptrans.KUITANSI LIKE '%ARO%' THEN  deptrans.KUITANSI
        ELSE TRIM(kodetransdeposito.DESKRIPSI_TRANS)
    END AS deptrans_deskripsi_trans
    ,deptrans.SALDO_TRANS AS deptrans_saldo_trans
    ,TRIM(deptrans.KUITANSI) AS deptrans_kuitansi
FROM deptrans
JOIN deposito ON deptrans.NO_REKENING = deposito.NO_REKENING
JOIN nasabah ON deposito.NASABAH_ID = nasabah.nasabah_id
LEFT JOIN kodetransdeposito ON deptrans.KODE_TRANS = kodetransdeposito.KODE_TRANS
LEFT JOIN kodegroup1deposito ON deposito.KODE_GROUP1 = kodegroup1deposito.KODE_GROUP1
LEFT JOIN kodegroup2deposito ON deposito.KODE_GROUP2 = kodegroup2deposito.KODE_GROUP2
LEFT JOIN kodegroup3deposito ON deposito.KODE_GROUP3 = kodegroup3deposito.KODE_GROUP3
LEFT JOIN kodegroup4_deposito ON deposito.KODE_GROUP4 = kodegroup4_deposito.KODE_GROUP4
LEFT JOIN kodegroup5deposito ON deposito.KODE_GROUP5 = kodegroup5deposito.KODE_GROUP5
LEFT JOIN kodegroup1_nasabah ON nasabah.NASABAH_GROUP1 = kodegroup1_nasabah.NASABAH_GROUP1
LEFT JOIN kodejenisdeposito ON deposito.JENIS_DEPOSITO = kodejenisdeposito.KODE_JENIS_DEPOSITO
WHERE deptrans.MY_KODE_TRANS NOT IN ('500')
AND (deptrans.KODE_TRANS IN ('01','08','03','09') OR deptrans.KUITANSI LIKE '%ARO%')
ORDER BY deptrans.DEPTRANS_ID
LIMIT 100
;

