akrual komisi
Mon Aug 10 2020 10:41:37 GMT+0000 (Coordinated Universal Time)
Saved by @haitonesia #sql
select a.id_transaksi_suretyship, b.jenis_suretyship, c.suretyship, d.nomor_sk, d.tgl_sk, d.tgl_sk_cetak, e.nama_principal, f.nama_obligee, a.flag_deletion, a.id_reference, a.nilai_proyek, a.nilai_bond, a.nilai_ijp, a.fee_base persen_fee_base, a.fee_base * a.nilai_ijp/100 fee_base, a.komisi_agen persen_komisi_agen, a.komisi_agen*a.nilai_ijp/100 komisi_agen, a.persen_reasuransi , --a.persen_reasuransi * a.nilai_ijp/100 reasuransi, a.reasuransi, --coalesce(a.fee_base/100,0)*a.persen_reasuransi * a.nilai_ijp/100 reasuransi_fee_base, a.reasuransi_fee_base, a.persen_coguarantee, a.persen_coguarantee * a.nilai_ijp/100 coguarantee, a.potongan_co persen_potongan_co, a.potongan_co * a.nilai_ijp/100 potongan_co, a.potongan_re persen_potongan_re, a.potongan_re * a.nilai_ijp/100 potongan_re, to_char(a.periode_berlaku + (a.jangka_waktu)* '1 day'::interval, 'DD-MM-YYYY') jatuh_tempo, to_char(a.periode_berlaku, 'DD-MM-YYYY') periode_berlaku, coalesce(e.jumlah_karyawan,0) tenaga_kerja, --a.nilai_bond * (100-coalesce(a.persen_reasuransi,0))/100 as plafond_or a.plafond_or, a.penjaminan nilai_penjaminan, c.is_konvensional, 0 nilai_asuransi_1, 0 nilai_asuransi_2, (a.fee_base*(a.persen_coguarantee*a.nilai_ijp/100)/100) coguarantee_fee_base, CASE when cast(usia AS varchar) IS NULL then cast(tanggal_usia AS varchar) else cast(usia AS varchar) END umur, '--' note, case when d.tgl_sk >= '2016-12-01' and d.tgl_sk <= '2017-11-30' then (coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*5/100 when d.tgl_sk >= '2017-12-01' and d.tgl_sk <= '2019-08-31' then (coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*7.5/100 when d.tgl_sk > '2019-09-01' then (coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*10/100 else 0 end ri_com, CASE when d.tgl_sk >= '2019-09-30' and d.tgl_sk <= '2020-04-30' then ((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)-((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)*(COALESCE(a.fee_base,0)/100)))*5/100 when d.tgl_sk >= '2020-05-01' then ((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)-((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)*(COALESCE(a.fee_base,0)/100)))*7.5/100 ELSE 0 end komisi_co, 0 komisi_asuransi --(ijp cogar - feebase cogar) x persen komisi from v_tr_transaksi_suretyship a inner join mt_jenis_suretyship b on a.id_jenis_suretyship = b.id_jenis_suretyship inner join mt_suretyship c on b.id_mt_suretyship = c.id_mt_suretyship inner join tr_sertifikat_penjaminan d on a.id_sertifikat = d.id_sertifikat inner join mt_principal e on a.id_principal = e.id_principal inner join mt_obligee f on a.id_obligee = f.id_obligee Left join mt_agen g on a.id_mt_agen=g.id_mt_agen left join mt_broker h on d.reas_id_broker = h.id_broker where --( a.flag_deletion is null OR (a.flag_deletion is not null and a.change_date > '2020-07-31') ) AND --d.id_reference is null and --a.flag_deletion is null and -- d.tgl_sk >='2015-01-01' and d.tgl_sk<='2020-07-31' a.flag_deletion is null and d.flag_delete is null and ( ( tgl_sk >='2015-01-01' and tgl_sk <='2020-07-31' and ( tgl_sk_cetak is null or ( tgl_sk_cetak is not null and d.id_reference is null) ) ) OR ( tgl_sk_cetak >='2015-01-01' and tgl_sk_cetak <='2020-07-31' and d.id_reference is not null) ) union all select a.id_transaksi_suretyship, b.jenis_suretyship, c.suretyship, d.nomor_sk, d.tgl_sk, d.tgl_sk_cetak, e.nama_principal, f.nama_bank_cabang as nama_obligee, a.flag_deletion, a.id_reference, a.nilai_proyek, a.nilai_bond, a.nilai_ijp, a.fee_base persen_fee_base, a.fee_base * a.nilai_ijp/100 fee_base, a.komisi_agen persen_komisi_agen, a.komisi_agen*a.nilai_ijp/100 komisi_agen, a.persen_reasuransi , --a.persen_reasuransi * a.nilai_ijp/100 reasuransi, a.reasuransi, --coalesce(a.fee_base/100,0)*a.persen_reasuransi * a.nilai_ijp/100 reasuransi_fee_base, a.reasuransi_fee_base, a.persen_coguarantee, a.persen_coguarantee * a.nilai_ijp/100 coguarantee, a.potongan_co persen_potongan_co, a.potongan_co * a.nilai_ijp/100 potongan_co, a.potongan_re persen_potongan_re, a.potongan_re * a.nilai_ijp/100 potongan_re, to_char(a.periode_berlaku + (a.jangka_waktu)* '1 MONTH'::interval, 'DD-MM-YYYY') jatuh_tempo, to_char(a.periode_berlaku, 'DD-MM-YYYY') periode_berlaku, coalesce(e.jumlah_karyawan,0) tenaga_kerja, --a.nilai_bond * (100-coalesce(a.persen_reasuransi,0))/100 as plafond_or a.plafond_or, a.penjaminan nilai_penjaminan, c.is_konvensional, 0 nilai_asuransi_1, 0 nilai_asuransi_2, (a.fee_base*(a.persen_coguarantee*a.nilai_ijp/100)/100) coguarantee_fee_base, case when cast(usia AS varchar) IS NULL then cast(tanggal_usia AS varchar) else cast(usia AS varchar) end umur, '--' note, case when d.tgl_sk >= '2016-12-01' and d.tgl_sk <= '2017-11-30' then (coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*5/100 when d.tgl_sk >= '2017-12-01' and d.tgl_sk <= '2019-08-31' then (coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*7.5/100 when d.tgl_sk > '2019-09-01' then (coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*10/100 else 0 end ri_com, CASE when d.tgl_sk >= '2019-09-30' and d.tgl_sk <= '2020-04-30' then ((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)-((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)*(COALESCE(a.fee_base,0)/100)))*5/100 when d.tgl_sk >= '2020-05-01' then ((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)-((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)*(COALESCE(a.fee_base,0)/100)))*7.5/100 ELSE 0 end komisi_co, 0 komisi_asuransi from v_tr_transaksi_suretyship a inner join mt_jenis_suretyship b on a.id_jenis_suretyship = b.id_jenis_suretyship inner join mt_suretyship c on b.id_mt_suretyship = c.id_mt_suretyship inner join tr_sertifikat_penjaminan d on a.id_sertifikat = d.id_sertifikat inner join mt_principal e on a.id_principal = e.id_principal inner join mt_bank_cabang f on d.id_mt_bank_cabang = f.id_mt_bank_cabang Left join mt_agen g on a.id_mt_agen=g.id_mt_agen left join mt_broker h on d.reas_id_broker = h.id_broker where -- ( a.flag_deletion is null OR (a.flag_deletion is not null and a.change_date > '2020-07-31') ) AND --d.id_reference is null and -- a.flag_deletion is null and -- d.tgl_sk >='2015-01-01' and d.tgl_sk<='2020-07-31' a.flag_deletion is null and d.flag_delete is null and ( ( tgl_sk >='2015-01-01' and tgl_sk <='2020-07-31' and ( tgl_sk_cetak is null or ( tgl_sk_cetak is not null and d.id_reference is null) ) ) OR ( tgl_sk_cetak >='2015-01-01' and tgl_sk_cetak <='2020-07-31' and d.id_reference is not null) ) and c.id_mt_suretyship != 9 union all select a.id_transaksi_suretyship, b.jenis_suretyship, c.suretyship, d.nomor_sk, d.tgl_sk, d.tgl_sk_cetak, e.nama_principal, f.nama_bank_cabang as nama_obligee, a.flag_deletion, a.id_reference, a.nilai_proyek, a.nilai_bond, a.nilai_ijp, a.fee_base persen_fee_base, a.fee_base * a.nilai_ijp/100 fee_base, a.komisi_agen persen_komisi_agen, a.komisi_agen*a.nilai_ijp/100 komisi_agen, a.persen_reasuransi , --a.persen_reasuransi * a.nilai_ijp/100 reasuransi, a.reasuransi, --coalesce(a.fee_base/100,0)*a.persen_reasuransi * a.nilai_ijp/100 reasuransi_fee_base, a.reasuransi_fee_base, a.persen_coguarantee, a.persen_coguarantee * a.nilai_ijp/100 coguarantee, a.potongan_co persen_potongan_co, a.potongan_co * a.nilai_ijp/100 potongan_co, a.potongan_re persen_potongan_re, a.potongan_re * a.nilai_ijp/100 potongan_re, to_char(a.periode_berlaku + (a.jangka_waktu)* '1 MONTH'::interval, 'DD-MM-YYYY') jatuh_tempo, to_char(a.periode_berlaku, 'DD-MM-YYYY') periode_berlaku, coalesce(e.jumlah_karyawan,0) tenaga_kerja, --a.nilai_bond * (100-coalesce(a.persen_reasuransi,0))/100 as plafond_or a.plafond_or, a.penjaminan nilai_penjaminan, c.is_konvensional, nilai_asuransi_1, nilai_asuransi_2, (a.fee_base*(a.persen_coguarantee*a.nilai_ijp/100)/100) coguarantee_fee_base, case when cast(usia AS varchar) IS NULL then cast(tanggal_usia AS varchar) else cast(usia AS varchar) end umur, a.catatan note, case when d.tgl_sk >= '2016-12-01' and d.tgl_sk <= '2017-11-30' then (coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*5/100 when d.tgl_sk >= '2017-12-01' and d.tgl_sk <= '2019-08-31' then (coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*7.5/100 when d.tgl_sk > '2019-09-01' then (coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*10/100 else 0 end ri_com, CASE when d.tgl_sk >= '2019-09-30' and d.tgl_sk <= '2020-04-30' then ((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)-((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)*(COALESCE(a.fee_base,0)/100)))*5/100 when d.tgl_sk >= '2020-05-01' then ((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)-((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)*(COALESCE(a.fee_base,0)/100)))*7.5/100 ELSE 0 end komisi_co, nilai_asuransi_2 * 5/100 komisi_asuransi from v_tr_transaksi_suretyship_asuransi a inner join mt_jenis_suretyship b on a.id_jenis_suretyship = b.id_jenis_suretyship inner join mt_suretyship c on b.id_mt_suretyship = c.id_mt_suretyship inner join tr_sertifikat_penjaminan d on a.id_sertifikat = d.id_sertifikat inner join mt_principal e on a.id_principal = e.id_principal inner join mt_bank_cabang f on d.id_mt_bank_cabang = f.id_mt_bank_cabang Left join mt_agen g on a.id_mt_agen=g.id_mt_agen left join mt_broker h on d.reas_id_broker = h.id_broker left join ( SELECT id_transaksi_suretyship, sum(nilai_asuransi_1) nilai_asuransi_1, sum(nilai_asuransi_2) nilai_asuransi_2 FROM ( SELECT id_transaksi_suretyship, CASE WHEN id_asuransi = 1 THEN nilai_asuransi ELSE 0 END AS nilai_asuransi_1, CASE WHEN id_asuransi = 2 THEN nilai_asuransi ELSE 0 END AS nilai_asuransi_2 FROM tr_transaksi_suretyship_ext ) ss GROUP BY id_transaksi_suretyship ) i ON a.id_transaksi_suretyship = i.id_transaksi_suretyship where -- ( a.flag_deletion is null OR (a.flag_deletion is not null and a.change_date > '2020-07-31') ) AND --d.id_reference is null and -- a.flag_deletion is null and -- d.tgl_sk >='2015-01-01' and d.tgl_sk<='2020-07-31' a.flag_deletion is null and d.flag_delete is null and ( ( tgl_sk >='2015-01-01' and tgl_sk <='2020-07-31' and ( tgl_sk_cetak is null or ( tgl_sk_cetak is not null and d.id_reference is null) ) ) OR ( tgl_sk_cetak >='2015-01-01' and tgl_sk_cetak <='2020-07-31' and d.id_reference is not null) ) and c.id_mt_suretyship = 9
Comments