|
WITH PrioritizedPinjaman AS (
|
|
SELECT
|
|
cms_anggota_id,
|
|
cms_kartupinjaman_id,
|
|
pinjamanno,
|
|
statusangsuran,
|
|
COALESCE(dateinvoiced, datereceived) AS tanggal_cair,
|
|
ROW_NUMBER() OVER (
|
|
PARTITION BY cms_anggota_id
|
|
ORDER BY
|
|
CASE
|
|
WHEN statusangsuran = 'AC' THEN 1 -- Prioritaskan pinjaman aktif
|
|
ELSE 2 -- Pinjaman lainnya
|
|
END,
|
|
GREATEST(dateinvoiced, datereceived) DESC -- Urutkan berdasarkan tanggal cair termuda
|
|
) AS rn
|
|
FROM cms_kartupinjaman
|
|
WHERE statusangsuran = 'AC'
|
|
OR cms_kartupinjaman_id IN (
|
|
SELECT cms_kartupinjaman_id
|
|
FROM cms_kartuangsuran
|
|
WHERE tanggallunas IS NOT NULL
|
|
OR datedoc IS NOT NULL
|
|
)
|
|
)
|
|
SELECT *
|
|
FROM
|
|
(
|
|
SELECT
|
|
TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') AS tgl_tarik_data,
|
|
TO_CHAR(CURRENT_DATE, 'YYYY-MM') AS Bulan_tarik_data,
|
|
TO_CHAR(ca.tanggal_pendaftaran, 'YYYY-MM-DD') as Tanggal_pendaftaran,
|
|
ca.value as No_anggota,
|
|
ca.nama as Nama_anggota,
|
|
ckp.nama_perusahaan as AFFCO,
|
|
ca.npk,
|
|
TO_CHAR(ca.tanggal_lahir, 'YYYY-MM-DD') as Tanggal_lahir,
|
|
EXTRACT(YEAR FROM AGE(CURRENT_DATE, ca.tanggal_lahir)) AS current_usia,
|
|
TO_CHAR(ca.tanggal_masuk_kerja, 'YYYY-MM-DD') as Tanggal_masuk_kerja,
|
|
ckg.kode_golongan as Golonga_kopastra,
|
|
ckg2.kode_golongan as Golongan_affco,
|
|
TO_CHAR(cca.tgl_masuk_koperasi, 'YYYY-MM-DD') as Tanggal_masuk_koperasi,
|
|
ca.pinjaman_coresys,
|
|
ca.simpanan_coresys,
|
|
CASE
|
|
WHEN ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP'))
|
|
AND ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE x.status_simpanan = 'Y') THEN 'Member Loan Deposit'
|
|
WHEN ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP'))
|
|
AND ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE x.status_simpanan = 'N') THEN 'Member Loan'
|
|
WHEN ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP')) THEN 'Member Loan'
|
|
WHEN ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran NOT IN ('AC', 'EP'))
|
|
AND ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE x.status_simpanan = 'Y') THEN 'Member Deposit'
|
|
WHEN ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE x.status_simpanan = 'Y') THEN 'Member Deposit'
|
|
WHEN ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP'))
|
|
AND ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE status_simpanan = 'Y')
|
|
AND ca.pinjaman_coresys = 'Y' AND ca.simpanan_coresys = 'Y' THEN 'Sleeping Member Aktif'
|
|
WHEN ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP'))
|
|
AND ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE status_simpanan = 'Y')
|
|
AND ca.pinjaman_coresys = 'Y' AND ca.simpanan_coresys = 'N' THEN 'Sleeping Member Aktif'
|
|
WHEN ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP'))
|
|
AND ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE status_simpanan = 'Y')
|
|
AND ca.pinjaman_coresys = 'N' AND ca.simpanan_coresys = 'Y' THEN 'Sleeping Member Aktif'
|
|
WHEN ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP'))
|
|
AND ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE status_simpanan = 'Y')
|
|
AND ca.pinjaman_coresys = 'N' AND ca.simpanan_coresys = 'N'
|
|
AND ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran NOT IN ('AC', 'EP'))
|
|
AND ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE status_simpanan = 'N')
|
|
THEN 'Sleeping Member Aktif'
|
|
WHEN ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP'))
|
|
AND ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE status_simpanan = 'Y')
|
|
AND ca.pinjaman_coresys = 'N' AND ca.simpanan_coresys = 'N'
|
|
AND ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran NOT IN ('AC', 'EP'))
|
|
THEN 'Sleeping Member Aktif'
|
|
WHEN ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP'))
|
|
AND ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE status_simpanan = 'Y')
|
|
AND ca.pinjaman_coresys = 'N' AND ca.simpanan_coresys = 'N'
|
|
AND ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE status_simpanan = 'N')
|
|
THEN 'Sleeping Member Aktif'
|
|
WHEN ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP'))
|
|
AND ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE status_simpanan = 'Y')
|
|
AND ca.pinjaman_coresys = 'N' AND ca.simpanan_coresys = 'N'
|
|
AND ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x)
|
|
AND ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_simpanan x)
|
|
THEN 'Sleeping Member Pasif'
|
|
END as Kategori_anggota,
|
|
CASE
|
|
WHEN EXISTS (SELECT 1 FROM cms_kartupinjaman ck_sub WHERE ck_sub.cms_anggota_id = ca.cms_anggota_id AND ck_sub.statusangsuran NOT IN ('AC','EP','IA'))
|
|
AND ca.pinjaman_coresys = 'N' THEN 'Sleeping Loan - Active'
|
|
WHEN NOT EXISTS (SELECT 1 FROM cms_kartupinjaman ck_sub WHERE ck_sub.cms_anggota_id = ca.cms_anggota_id)
|
|
AND ca.pinjaman_coresys = 'Y' THEN 'Sleeping Loan - Active'
|
|
WHEN EXISTS (SELECT 1 FROM cms_kartupinjaman ck_sub WHERE ck_sub.cms_anggota_id = ca.cms_anggota_id AND ck_sub.statusangsuran NOT IN ('AC','EP','IA'))
|
|
AND ca.pinjaman_coresys = 'Y' THEN 'Sleeping Loan - Active'
|
|
WHEN NOT EXISTS (SELECT 1 FROM cms_kartupinjaman ck_sub WHERE ck_sub.cms_anggota_id = ca.cms_anggota_id AND ck_sub.statusangsuran NOT IN ('AC','EP','IA'))
|
|
AND ca.pinjaman_coresys = 'N' THEN 'Sleeping Loan - Pasif'
|
|
END AS Type_sleeping_Loan_Member,
|
|
CASE
|
|
WHEN EXISTS (SELECT 1 FROM cms_simpanan cs_sub WHERE cs_sub.cms_anggota_id = ca.cms_anggota_id AND cs_sub.status_simpanan != 'Y')
|
|
AND NOT EXISTS (SELECT 1 FROM cms_simpanan cs_sub WHERE cs_sub.cms_anggota_id = ca.cms_anggota_id AND cs_sub.status_simpanan = 'Y')
|
|
AND ca.simpanan_coresys = 'N' THEN 'Sleeping Deposit - Active'
|
|
WHEN NOT EXISTS (SELECT 1 FROM cms_simpanan cs_sub WHERE cs_sub.cms_anggota_id = ca.cms_anggota_id)
|
|
AND ca.simpanan_coresys = 'Y' THEN 'Sleeping Deposit Active'
|
|
WHEN NOT EXISTS (SELECT 1 FROM cms_simpanan cs_sub WHERE cs_sub.cms_anggota_id = ca.cms_anggota_id AND cs_sub.status_simpanan != 'Y')
|
|
AND ca.simpanan_coresys = 'N' THEN 'Sleeping Deposit Pasif'
|
|
ELSE NULL
|
|
END AS Sleeping_deposit_typ,
|
|
lp.pinjamanno,
|
|
lp.tanggal_cair,
|
|
lp.statusangsuran,
|
|
CASE
|
|
WHEN lp.statusangsuran = 'PA' THEN COALESCE(ck2.tanggallunas, ck2.datedoc) -- Ambil tanggal lunas jika status PA
|
|
WHEN lp.statusangsuran = 'AC' THEN NULL -- Kosongkan jika status AC
|
|
ELSE NULL -- Kosongkan untuk status lainnya jika diperlukan
|
|
END AS tanggal_lunas_terakhir,
|
|
CASE
|
|
WHEN COALESCE(ck2.tanggallunas, ck2.datedoc) IS NOT NULL THEN
|
|
EXTRACT(YEAR FROM AGE(CURRENT_DATE, COALESCE(ck2.tanggallunas, ck2.datedoc))) * 12 +
|
|
EXTRACT(MONTH FROM AGE(CURRENT_DATE, COALESCE(ck2.tanggallunas, ck2.datedoc)))
|
|
ELSE NULL -- Kosongkan jika tidak ada tanggal lunas
|
|
END AS bulan_terakhir_lunas
|
|
FROM cms_anggota ca
|
|
LEFT JOIN cms_calon_anggota cca ON ca.cms_anggota_id = cca.cms_anggota_id
|
|
LEFT JOIN cms_keanggotaan_perusahaan ckp ON ca.cms_keanggotaan_perusahaan_id = ckp.cms_keanggotaan_perusahaan_id
|
|
LEFT JOIN cms_keanggotaan_golongan ckg ON ca.cms_keanggotaan_golongan_id = ckg.cms_keanggotaan_golongan_id
|
|
LEFT JOIN cms_keanggotaan_golongan ckg2 ON ca.cms_perusahaan_golmap_id = ckg2.cms_keanggotaan_golongan_id
|
|
LEFT OUTER JOIN cms_kartupinjaman ck ON ca.cms_anggota_id = ck.cms_anggota_id AND ck.statusangsuran != 'IA'
|
|
LEFT JOIN cms_kartuangsuran ck2 ON ck2.cms_kartupinjaman_id = ck.cms_kartupinjaman_id AND ck2.line = 10
|
|
LEFT JOIN PrioritizedPinjaman lp ON ca.cms_anggota_id = lp.cms_anggota_id
|
|
WHERE 1=1
|
|
AND ca.status_anggota IS null
|
|
AND ca.value = @PARAMETER.NO_ANGGOTA@
|
|
) x
|
|
WHERE x.Kategori_anggota IN ('Sleeping Member Pasif', 'Sleeping Member Aktif')
|