SELECT DISTINCT

    TRIM(deposito.NO_REKENING) AS no_rekening
    ,TRIM(deposito.NO_ALTERNATIF) AS no_bilyet
    ,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
    ,kodejenisdeposito.KODE_JENIS_DEPOSITO AS kode_jenis_deposito
    ,kodejenisdeposito.DESKRIPSI_JENIS_DEPOSITO AS jenis_deposito
    ,TRIM(deposito.NASABAH_ID) AS cif
    ,TRIM(kodegroup1deposito.KODE_GROUP1) AS kode_marketing_deposito
    ,TRIM(kodegroup1deposito.DESKRIPSI_GROUP1) AS nama_marketing_deposito
    ,deposito.JML_DEPOSITO AS nominal_deposito
    ,deposito.JKW AS jangka_waktu
    ,deposito.SUKU_BUNGA AS suku_bunga
    ,deposito.TGL_REGISTRASI AS tanggal_registrasi
    ,deposito.TGL_MULAI AS tanggal_pembukaan
    ,deposito.TGL_JT AS tanggal_jatuh_tempo   
    ,TRIM(deposito.STATUS_BUNGA) AS status_bunga
    ,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
    ,CASE
        WHEN deposito.STATUS_AKTIF = '3' THEN 0
        ELSE deposito.JML_DEPOSITO
    END AS nominal_outstanding
    ,deposito.BUNGA_BERBUNGA AS flag_bunga_masuk_pokok
	,CASE
		WHEN kodegroup2deposito.KODE_GROUP2 = '004' THEN 'Kantor Kas Gajah'
		WHEN kodegroup2deposito.KODE_GROUP2 = '003' THEN 'Kantor Kas Bulusan'
		WHEN kodegroup2deposito.KODE_GROUP2 = '002' THEN 'Kantor Kas Kalipancur'
		ELSE 'Kantor Pusat'
	END AS kantor
    ,TRIM(kodegroup3deposito.DESKRIPSI_GROUP3) AS kode_group3
    ,TRIM(kodegroup4_deposito.DESKRIPSI_GROUP4) AS kode_group4
    ,TRIM(kodegroup5deposito.DESKRIPSI_GROUP5) AS kode_group5

FROM deptrans
JOIN deposito ON deptrans.NO_REKENING = deposito.NO_REKENING
LEFT JOIN kodetransdeposito ON deptrans.KODE_TRANS = kodetransdeposito.KODE_TRANS
LEFT JOIN kodejenisdeposito ON deposito.JENIS_DEPOSITO = kodejenisdeposito.KODE_JENIS_DEPOSITO
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
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


