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