This is insane…. super ultra long query. Definitely will kill your DBMS in 5 minutes…
select distinct production_year,
production_month,
cust.sales_number,
cust.revision_number,
cust.status,
cust.customer_code_orig,
customer_name,
boffice,
insurance_periode_from_awal,
cust.insurance_period_from,
cust.insurance_period_to,
cust.sales_status,
cast(‘ ‘ as text) as occupation_code,
cast(‘ ‘ as text) as building_category,
cast(‘ ‘ as text) as construction_class,
cast(‘ ‘ as text) as construction_year,
cast(‘ ‘ as text) as jumlah_tingkat,
H_BUILDING,
H_STOCK,
H_MACHINERY,
H_CONTENT,
H_OTHERS,
0.0 as OLD_H_BUILDING,
0.0 as OLD_H_STOCK,
0.0 as OLD_H_MACHINERY,
0.0 as OLD_H_CONTENT,
0.0 as OLD_H_OTHERS,
cust.reference,
obj.location_id,
risk.risk_number,risk_definition,
kel.str_kelurahan_name as kelurahan,
kab.str_kabupaten_name as kabupaten,
prov.str_province_name as province,
city.str_city_name as city,
country.str_country_name as country,
alm.street_address || ‘. ‘ || coalesce(kel.str_kelurahan_name,’-NO KELURAHAN-’) || ‘. ‘ || coalesce(city.str_city_name,’-NO CITY-’) || ‘. ‘ || coalesce(kab.str_kabupaten_name,’-NO KABUPATEN-’) || ‘. ‘ || coalesce(str_province_name,’-NO PROVINCE-’) || ‘. ‘ || alm.postal_code || ‘. ‘ || COALESCE(country.str_country_name,’-NO COUNTRY-’) || ‘. ‘ as obj_location,
alm.postal_code as obj_postal_code,
cust.revision_flag,
cust.insurance_product_code,
insurance_product_name,
cust.coins_premium,
cust.own_share
into temporary tbl_rpt_bppdan_customer
from (
select period1 as production_year,period2 as production_month,
det.sales_number,det.revision_number,
cast(det.revision_number as numeric) as status,
customer_code_orig,
cco_name as customer_name,
front_office_code_handling as boffice,
cast(null as date) as insurance_periode_from_awal,
insurance_period_from,
insurance_period_to,
sales_status,
coalesce(H_BUILDING,0) as H_BUILDING,
coalesce(H_STOCK,0) as H_STOCK,
coalesce(H_MACHINERY,0) as H_MACHINERY,
coalesce(H_CONTENT,0) as H_CONTENT,
coalesce(H_OTHERS,0) as H_OTHERS,
det.reference,
det.revision_flag,
det.insurance_product_code,
insurance_product_name,
det.coins_premium,
det.own_share
from “tbl_so_sales_details” as det
left join (
select obj.sales_number,obj.revision_number,sum(declared_value) as H_CONTENT
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘CNT’
group by obj.sales_number,obj.revision_number
) as cnt on cnt.sales_number = det.sales_number and cnt.revision_number = det.revision_number
left join (
select obj.sales_number,obj.revision_number,sum(declared_value) as H_STOCK
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘STK’
group by obj.sales_number,obj.revision_number
) as stk on stk.sales_number = det.sales_number and stk.revision_number = det.revision_number
left join (
select obj.sales_number,obj.revision_number,sum(declared_value) as H_MACHINERY
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘MCH’
group by obj.sales_number,obj.revision_number
) as mch on mch.sales_number = det.sales_number and mch.revision_number = det.revision_number
left join (
select obj.sales_number,obj.revision_number,sum(declared_value) as H_BUILDING
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘BLD’
group by obj.sales_number,obj.revision_number
) as bld on bld.sales_number = det.sales_number and bld.revision_number = det.revision_number
left join (
select obj.sales_number,obj.revision_number,sum(declared_value) as H_OTHERS
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘OTH’
group by obj.sales_number,obj.revision_number
) as oth on oth.sales_number = det.sales_number and oth.revision_number = det.revision_number
join “tbl_gl_general_jem” as g on split_part(g.reference,’;',1) = det.sales_number and split_part(g.reference,’;',2) = det.revision_number
inner join “tbl_pd_insurance_products” as prod on prod.insurance_product_code = det.insurance_product_code
where g.period1 = ’2009′ and cast(g.period2 as integer) between ’4′ and ’9′ and ttype = ‘Insurance premium’
and det.insurance_product_code in (’115′)
) as cust
join (select sales_number,revision_number,location_id from “tbl_so_sales_objects” group by sales_number,revision_number,location_id) as obj on obj.sales_number || obj.revision_number = cust.sales_number || cust.revision_number
join (select sales_number,sales_rev_number,location_id,risk_number,risk_definition from “tbl_ra_object_risk_relations” group by sales_number,sales_rev_number,location_id,risk_number,risk_definition)as risk on obj.sales_number = risk.sales_number and obj.revision_number = risk.sales_rev_number and obj.location_id = risk.location_id
join “tbl_so_sales_locations” as alm on alm.sales_number = obj.sales_number and alm.revision_number = obj.revision_number and alm.location_number = obj.location_id
left join tbl_mstr_kelurahan as kel on kel.num_kelurahan_id = alm.kel_code
left join “tbl_mstr_kabupaten” as kab on kab.num_kabupaten_id = alm.kab_kodya_code and kab.num_province_id = alm.province_code
left join “tbl_mstr_province” as prov on prov.num_province_id = alm.province_code
left join “tbl_mstr_city” as city on city.num_city_id = alm.kec_code
left join “tbl_mstr_country” as country on country.str_country_code = alm.country_code;
–===========================================================================================
update tbl_rpt_bppdan_customer
set insurance_periode_from_awal = b.insurance_period_from
from “tbl_so_sales_details” as b
where b.sales_number = tbl_rpt_bppdan_customer.sales_number and b.revision_number = ’000′;
–===========================================================================================
–select set_occupation_code_bppdan(‘Andi’);
update “tbl_rpt_bppdan_customer” set occupation_code = attribute_value
from (
select sales_number,revision_number,product_attribute_name,attribute_value
from tbl_so_object_attributes as atr
join tbl_pd_attributes as pd on pd.product_attribute_code = atr.attribute_code
where product_attribute_name like ‘%occup%’ –and sales_number || revision_number in (select sales_number || revision_number from “tbl_rpt_bppdan_customer”)
group by sales_number,revision_number,product_attribute_name,attribute_value
order by sales_number,revision_number
) as obj
where “tbl_rpt_bppdan_customer”.sales_number = obj.sales_number and “tbl_rpt_bppdan_customer”.revision_number = obj.revision_number;
–===========================================================================================
select set_eq_extention(‘construction_year’);
select set_eq_extention(‘construction_class’);
select set_eq_extention(‘building_category’);
select set_eq_extention(‘jumlah_tingkat’);
–============================================================================================================================
select gen.boffice,
split_part(gen.reference,’;',1) as sales_number,
split_part(gen.reference,’;',2) as revision_number,
split_part(gen.reference,’;',3) as risk_number,
split_part(gen.reference,’;',4) as treaty_name,
split_part(gen.reference,’;',6) as treaty_code,
cid,
cerates,
l.amount,
l.amount2,
gen.temp_no
into temporary tbl_rpt_bppdan_gl_eq
from tbl_gl_general_jem as gen
join tbl_gl_lines_jem as l on l.temp_no = gen.temp_no
where account in (’6350000′,’6331000′,’6341000′) and split_part(gen.reference,’;',4) in (‘Indonesian standard earthquake reinsurance (MAIPARK)’,'Indonesian standard earthquake reinsurance (MAIPARK) 09H2′)
and split_part(gen.reference,’;',1) || ‘;’ || split_part(gen.reference,’;',2) || ‘;’ || split_part(gen.reference,’;',3) in (select obj.sales_number || ‘;’ || obj.revision_number || ‘;’ || obj.risk_number from “tbl_rpt_bppdan_customer” as obj where insurance_product_code = ’115′);
–=============================================================================================================================
select distinct b.risk_number,
b.revision_number,
(
select sum(amount) from tbl_rpt_bppdan_gl_eq as gl where upper(treaty_code) in (select upper(ext_code) from “adm_extention_code” where ext_id = ‘Fire’)
and gl.sales_number = b.sales_number and gl.revision_number = b.revision_number and gl.risk_number = b.risk_number and gl.boffice = b.boffice
) as premium_rate_bppdan_fire,
(
select sum(amount) from tbl_rpt_bppdan_gl_eq as gl where upper(treaty_code) in (select upper(ext_code) from “adm_extention_code” where ext_id = ‘Flood’)
and gl.sales_number = b.sales_number and gl.revision_number = b.revision_number and gl.risk_number = b.risk_number and gl.boffice = b.boffice
) as premium_rate_bppdan_flood,
(
select sum(amount) from tbl_rpt_bppdan_gl_eq as gl where upper(treaty_code) in (select upper(ext_code) from “adm_extention_code” where ext_id = ‘RSMD’)
and gl.sales_number = b.sales_number and gl.revision_number = b.revision_number and gl.risk_number = b.risk_number and gl.boffice = b.boffice
) as premium_rate_bppdan_rsmd,
(
select sum(amount) from tbl_rpt_bppdan_gl_eq as gl where upper(treaty_code) in (select upper(ext_code) from “adm_extention_code” where ext_id = ‘EQ’)
and gl.sales_number = b.sales_number and gl.revision_number = b.revision_number and gl.risk_number = b.risk_number and gl.boffice = b.boffice
) as premium_rate_bppdan_eq,
total_tsi, c.total_premium, prgbi_tsi.cession_amount as prgbi_tsi, prgbi_premium.premium_amount as prgbi_premium,
(
select sum(total_premi)
from(
select risk_number,revision_number,sum(premium_amount) as total_premi
from tbl_ra_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘Fire’)
group by risk_number,revision_number
union
select risk_number,revision_number,sum(premium_amount) as total_premi
from tbl_or_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘Fire’)
group by risk_number,revision_number
) as a
where a.risk_number = b.risk_number –and a.revision_number = cast(b.revision_number as numeric) + 9
) as total_premi_fire,
(
select sum(total_premi)
from(
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_ra_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘Flood’)
group by risk_number,revision_number
union
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_or_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘Flood’)
group by risk_number,revision_number
) as a
where a.risk_number = b.risk_number –and a.revision_number = cast(b.revision_number as numeric) + 9
) as total_premi_flood,
(
select sum(total_premi)
from(
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_ra_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘RSMD’)
group by risk_number,revision_number
union
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_or_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘RSMD’)
group by risk_number,revision_number
) as a
where a.risk_number = b.risk_number –and a.revision_number = cast(b.revision_number as numeric) + 9
) as total_premi_rsmd,
(
select sum(total_premi)
from(
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_ra_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘EQ’)
group by risk_number,revision_number
union
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_or_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘EQ’)
group by risk_number,revision_number
) as a
where a.risk_number = b.risk_number –and a.revision_number = cast(b.revision_number as numeric) + 9
) as total_premi_eq
into temporary tbl_rpt_bppdan_detail_eq
from tbl_rpt_bppdan_customer as b
left join (
select risk_number,max(revision_number) as revision_number–,base_sum as total_tsi
from tbl_ra_risk_cession
group by risk_number
) as link on link.risk_number = b.risk_number
join (
select distinct risk_number,revision_number,base_sum as total_tsi
from “tbl_ra_risk_cession”
) as a on a.risk_number = b.risk_number and a.revision_number = link.revision_number
left join (
select risk_number,max(revision_number) as revision_number –,base_premium as total_premium
from “tbl_ra_risk_premium”
group by risk_number
) as link_b on link_b.risk_number = b.risk_number
join (
select distinct risk_number,revision_number,base_premium as total_premium
from “tbl_ra_risk_premium”
) as c on c.risk_number = b.risk_number and c.revision_number = link_b.revision_number
join (
select risk_number,revision_number,cession_amount
from “tbl_ra_risk_cession”
where outward_name in (‘Indonesian standard earthquake reinsurance (MAIPARK)’,'Indonesian standard earthquake reinsurance (MAIPARK) 09H2′)
) as prgbi_tsi on prgbi_tsi.risk_number = b.risk_number and prgbi_tsi.revision_number = link.revision_number
join (
select risk_number,revision_number,premium_amount
from “tbl_ra_risk_premium”
where outward_name in (‘Indonesian standard earthquake reinsurance (MAIPARK)’,'Indonesian standard earthquake reinsurance (MAIPARK) 09H2′)
) prgbi_premium on prgbi_premium.risk_number = b.risk_number and prgbi_premium.revision_number = link_b.revision_number
where b.insurance_product_code = ’115′;
–Download Report
–=============================================================================================================================
select cust.sales_number,
cust.revision_number,
–obj.object_number,
yr.outward_year,
line.period2 as produksi,
cust.customer_name,
cust.insurance_periode_from_awal,
cust.insurance_period_from,
cust.insurance_period_to,
cust.insurance_product_code,
cust.insurance_product_name,
cust.revision_flag as policy_status,
cust.occupation_code,
cust.reference,
cust.risk_number,
cust.revision_flag,
–declared_value_new as tsi_new,
–declared_value_old as tsi_old,
prgbi_tsi,
prgbi_premium,
OLD_H_BUILDING,
OLD_H_STOCK,
OLD_H_MACHINERY,
OLD_H_CONTENT,
OLD_H_OTHERS,
H_BUILDING,
H_STOCK,
H_MACHINERY,
H_CONTENT,
H_OTHERS,
–obj.interest_name,
cust.obj_location,
cust.city,
cust.obj_postal_code,
cust.location_id,
cust.building_category,
cust.construction_class,
cust.construction_year,
cust.jumlah_tingkat,
line.cerates as kurs,
line.cid as currency,
cust.own_share,
cust.coins_premium,
cust.sales_status,
total_premi as premi100,
0.025 * total_premi as premi25
from tbl_rpt_bppdan_customer as cust
join (
select risk_number,total_premium as total_premi,case coalesce(total_premium,0) when 0 then 0 else round((total_premium/total_tsi),6) end as total_rate,
total_premi_fire,total_premi_flood,total_premi_rsmd,total_premi_eq,total_tsi,
premium_rate_bppdan_fire,premium_rate_bppdan_flood,premium_rate_bppdan_rsmd,premium_rate_bppdan_eq,
prgbi_tsi,prgbi_premium
from tbl_rpt_bppdan_detail_eq
) as det on det.risk_number = cust.risk_number
join (
select risk_number, outward_year
from “tbl_ra_risk_cession”
group by risk_number,outward_year
) as yr on yr.risk_number = cust.risk_number
inner join (
select cid,
cerates,period2,period1,
split_part(foo.reference,’;',1) as gl_sales_number,
split_part(foo.reference,’;',2) as gl_revision_number,
split_part(foo.reference,’;',3) as gl_risk_number,
sum(amount) as gl_premi
from(
select gen.reference,cerates,period2,period1,
cid,
amount
from (
select gen.reference,cid,temp_no,cerates,period2,period1
from “tbl_gl_general_jem” as gen
where substring(temp_no,1,3) = ‘OTP’
) as gen
join (
select temp_no,amount
from “tbl_gl_lines_jem”
where account in (’6350000′,’6331000′,’6341000′)
) as foo on foo.temp_no = gen.temp_no
) as foo
group by cid,split_part(foo.reference,’;',1),split_part(foo.reference,’;',2),split_part(foo.reference,’;',3),cerates,period2,period1
) as line on gl_sales_number = cust.sales_number and gl_revision_number = cust.revision_number and gl_risk_number = cust.risk_number and line.period1 = cust.production_year and line.period2 = cust.production_month
–where total_premi notnull and total_premi <> 0
where cust.insurance_product_code = ’115′
order by cust.sales_number,cust.revision_number
select distinct production_year,
production_month,
cust.sales_number,
cust.revision_number,
cust.status,
cust.customer_code_orig,
customer_name,
boffice,
insurance_periode_from_awal,
cust.insurance_period_from,
cust.insurance_period_to,
cust.sales_status,
cast(‘ ‘ as text) as occupation_code,
cast(‘ ‘ as text) as building_category,
cast(‘ ‘ as text) as construction_class,
cast(‘ ‘ as text) as construction_year,
cast(‘ ‘ as text) as jumlah_tingkat,
H_BUILDING,
H_STOCK,
H_MACHINERY,
H_CONTENT,
H_OTHERS,
0.0 as OLD_H_BUILDING,
0.0 as OLD_H_STOCK,
0.0 as OLD_H_MACHINERY,
0.0 as OLD_H_CONTENT,
0.0 as OLD_H_OTHERS,
cust.reference,
obj.location_id,
risk.risk_number,risk_definition,
kel.str_kelurahan_name as kelurahan,
kab.str_kabupaten_name as kabupaten,
prov.str_province_name as province,
city.str_city_name as city,
country.str_country_name as country,
alm.street_address || ‘. ‘ || coalesce(kel.str_kelurahan_name,’-NO KELURAHAN-’) || ‘. ‘ || coalesce(city.str_city_name,’-NO CITY-’) || ‘. ‘ || coalesce(kab.str_kabupaten_name,’-NO KABUPATEN-’) || ‘. ‘ || coalesce(str_province_name,’-NO PROVINCE-’) || ‘. ‘ || alm.postal_code || ‘. ‘ || COALESCE(country.str_country_name,’-NO COUNTRY-’) || ‘. ‘ as obj_location,
alm.postal_code as obj_postal_code,
cust.revision_flag,
cust.insurance_product_code,
insurance_product_name,
cust.coins_premium,
cust.own_share
into temporary tbl_rpt_bppdan_customer
from (
select period1 as production_year,period2 as production_month,
det.sales_number,det.revision_number,
cast(det.revision_number as numeric) as status,
customer_code_orig,
cco_name as customer_name,
front_office_code_handling as boffice,
cast(null as date) as insurance_periode_from_awal,
insurance_period_from,
insurance_period_to,
sales_status,
coalesce(H_BUILDING,0) as H_BUILDING,
coalesce(H_STOCK,0) as H_STOCK,
coalesce(H_MACHINERY,0) as H_MACHINERY,
coalesce(H_CONTENT,0) as H_CONTENT,
coalesce(H_OTHERS,0) as H_OTHERS,
det.reference,
det.revision_flag,
det.insurance_product_code,
insurance_product_name,
det.coins_premium,
det.own_share
from “tbl_so_sales_details” as det
left join (
select obj.sales_number,obj.revision_number,sum(declared_value) as H_CONTENT
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘CNT’
group by obj.sales_number,obj.revision_number
) as cnt on cnt.sales_number = det.sales_number and cnt.revision_number = det.revision_number
left join (
select obj.sales_number,obj.revision_number,sum(declared_value) as H_STOCK
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘STK’
group by obj.sales_number,obj.revision_number
) as stk on stk.sales_number = det.sales_number and stk.revision_number = det.revision_number
left join (
select obj.sales_number,obj.revision_number,sum(declared_value) as H_MACHINERY
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘MCH’
group by obj.sales_number,obj.revision_number
) as mch on mch.sales_number = det.sales_number and mch.revision_number = det.revision_number
left join (
select obj.sales_number,obj.revision_number,sum(declared_value) as H_BUILDING
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘BLD’
group by obj.sales_number,obj.revision_number
) as bld on bld.sales_number = det.sales_number and bld.revision_number = det.revision_number
left join (
select obj.sales_number,obj.revision_number,sum(declared_value) as H_OTHERS
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘OTH’
group by obj.sales_number,obj.revision_number
) as oth on oth.sales_number = det.sales_number and oth.revision_number = det.revision_number
join “tbl_gl_general_jem” as g on split_part(g.reference,’;',1) = det.sales_number and split_part(g.reference,’;',2) = det.revision_number
inner join “tbl_pd_insurance_products” as prod on prod.insurance_product_code = det.insurance_product_code
where g.period1 = ’2009′ and cast(g.period2 as integer) between ’4′ and ’9′ and ttype = ‘Insurance premium’
and det.insurance_product_code in (’115′)
) as cust
join (select sales_number,revision_number,location_id from “tbl_so_sales_objects” group by sales_number,revision_number,location_id) as obj on obj.sales_number || obj.revision_number = cust.sales_number || cust.revision_number
join (select sales_number,sales_rev_number,location_id,risk_number,risk_definition from “tbl_ra_object_risk_relations” group by sales_number,sales_rev_number,location_id,risk_number,risk_definition)as risk on obj.sales_number = risk.sales_number and obj.revision_number = risk.sales_rev_number and obj.location_id = risk.location_id
join “tbl_so_sales_locations” as alm on alm.sales_number = obj.sales_number and alm.revision_number = obj.revision_number and alm.location_number = obj.location_id
left join tbl_mstr_kelurahan as kel on kel.num_kelurahan_id = alm.kel_code
left join “tbl_mstr_kabupaten” as kab on kab.num_kabupaten_id = alm.kab_kodya_code and kab.num_province_id = alm.province_code
left join “tbl_mstr_province” as prov on prov.num_province_id = alm.province_code
left join “tbl_mstr_city” as city on city.num_city_id = alm.kec_code
left join “tbl_mstr_country” as country on country.str_country_code = alm.country_code;
–===========================================================================================
update tbl_rpt_bppdan_customer
set insurance_periode_from_awal = b.insurance_period_from
from “tbl_so_sales_details” as b
where b.sales_number = tbl_rpt_bppdan_customer.sales_number and b.revision_number = ’000′;
–===========================================================================================
–select set_occupation_code_bppdan(‘Andi’);
update “tbl_rpt_bppdan_customer” set occupation_code = attribute_value
from (
select sales_number,revision_number,product_attribute_name,attribute_value
from tbl_so_object_attributes as atr
join tbl_pd_attributes as pd on pd.product_attribute_code = atr.attribute_code
where product_attribute_name like ‘%occup%’ –and sales_number || revision_number in (select sales_number || revision_number from “tbl_rpt_bppdan_customer”)
group by sales_number,revision_number,product_attribute_name,attribute_value
order by sales_number,revision_number
) as obj
where “tbl_rpt_bppdan_customer”.sales_number = obj.sales_number and “tbl_rpt_bppdan_customer”.revision_number = obj.revision_number;
–===========================================================================================
select set_eq_extention(‘construction_year’);
select set_eq_extention(‘construction_class’);
select set_eq_extention(‘building_category’);
select set_eq_extention(‘jumlah_tingkat’);
–============================================================================================================================
select gen.boffice,
split_part(gen.reference,’;',1) as sales_number,
split_part(gen.reference,’;',2) as revision_number,
split_part(gen.reference,’;',3) as risk_number,
split_part(gen.reference,’;',4) as treaty_name,
split_part(gen.reference,’;',6) as treaty_code,
cid,
cerates,
l.amount,
l.amount2,
gen.temp_no
into temporary tbl_rpt_bppdan_gl_eq
from tbl_gl_general_jem as gen
join tbl_gl_lines_jem as l on l.temp_no = gen.temp_no
where account in (’6350000′,’6331000′,’6341000′) and split_part(gen.reference,’;',4) in (‘Indonesian standard earthquake reinsurance (MAIPARK)’,'Indonesian standard earthquake reinsurance (MAIPARK) 09H2′)
and split_part(gen.reference,’;',1) || ‘;’ || split_part(gen.reference,’;',2) || ‘;’ || split_part(gen.reference,’;',3) in (select obj.sales_number || ‘;’ || obj.revision_number || ‘;’ || obj.risk_number from “tbl_rpt_bppdan_customer” as obj where insurance_product_code = ’115′);
–=============================================================================================================================
select distinct b.risk_number,
b.revision_number,
(
select sum(amount) from tbl_rpt_bppdan_gl_eq as gl where upper(treaty_code) in (select upper(ext_code) from “adm_extention_code” where ext_id = ‘Fire’)
and gl.sales_number = b.sales_number and gl.revision_number = b.revision_number and gl.risk_number = b.risk_number and gl.boffice = b.boffice
) as premium_rate_bppdan_fire,
(
select sum(amount) from tbl_rpt_bppdan_gl_eq as gl where upper(treaty_code) in (select upper(ext_code) from “adm_extention_code” where ext_id = ‘Flood’)
and gl.sales_number = b.sales_number and gl.revision_number = b.revision_number and gl.risk_number = b.risk_number and gl.boffice = b.boffice
) as premium_rate_bppdan_flood,
(
select sum(amount) from tbl_rpt_bppdan_gl_eq as gl where upper(treaty_code) in (select upper(ext_code) from “adm_extention_code” where ext_id = ‘RSMD’)
and gl.sales_number = b.sales_number and gl.revision_number = b.revision_number and gl.risk_number = b.risk_number and gl.boffice = b.boffice
) as premium_rate_bppdan_rsmd,
(
select sum(amount) from tbl_rpt_bppdan_gl_eq as gl where upper(treaty_code) in (select upper(ext_code) from “adm_extention_code” where ext_id = ‘EQ’)
and gl.sales_number = b.sales_number and gl.revision_number = b.revision_number and gl.risk_number = b.risk_number and gl.boffice = b.boffice
) as premium_rate_bppdan_eq,
total_tsi, c.total_premium, prgbi_tsi.cession_amount as prgbi_tsi, prgbi_premium.premium_amount as prgbi_premium,
(
select sum(total_premi)
from(
select risk_number,revision_number,sum(premium_amount) as total_premi
from tbl_ra_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘Fire’)
group by risk_number,revision_number
union
select risk_number,revision_number,sum(premium_amount) as total_premi
from tbl_or_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘Fire’)
group by risk_number,revision_number
) as a
where a.risk_number = b.risk_number –and a.revision_number = cast(b.revision_number as numeric) + 9
) as total_premi_fire,
(
select sum(total_premi)
from(
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_ra_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘Flood’)
group by risk_number,revision_number
union
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_or_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘Flood’)
group by risk_number,revision_number
) as a
where a.risk_number = b.risk_number –and a.revision_number = cast(b.revision_number as numeric) + 9
) as total_premi_flood,
(
select sum(total_premi)
from(
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_ra_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘RSMD’)
group by risk_number,revision_number
union
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_or_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘RSMD’)
group by risk_number,revision_number
) as a
where a.risk_number = b.risk_number –and a.revision_number = cast(b.revision_number as numeric) + 9
) as total_premi_rsmd,
(
select sum(total_premi)
from(
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_ra_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘EQ’)
group by risk_number,revision_number
union
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_or_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘EQ’)
group by risk_number,revision_number
) as a
where a.risk_number = b.risk_number –and a.revision_number = cast(b.revision_number as numeric) + 9
) as total_premi_eq
into temporary tbl_rpt_bppdan_detail_eq
from tbl_rpt_bppdan_customer as b
left join (
select risk_number,max(revision_number) as revision_number–,base_sum as total_tsi
from tbl_ra_risk_cession
group by risk_number
) as link on link.risk_number = b.risk_number
join (
select distinct risk_number,revision_number,base_sum as total_tsi
from “tbl_ra_risk_cession”
) as a on a.risk_number = b.risk_number and a.revision_number = link.revision_number
left join (
select risk_number,max(revision_number) as revision_number –,base_premium as total_premium
from “tbl_ra_risk_premium”
group by risk_number
) as link_b on link_b.risk_number = b.risk_number
join (
select distinct risk_number,revision_number,base_premium as total_premium
from “tbl_ra_risk_premium”
) as c on c.risk_number = b.risk_number and c.revision_number = link_b.revision_number
join (
select risk_number,revision_number,cession_amount
from “tbl_ra_risk_cession”
where outward_name in (‘Indonesian standard earthquake reinsurance (MAIPARK)’,'Indonesian standard earthquake reinsurance (MAIPARK) 09H2′)
) as prgbi_tsi on prgbi_tsi.risk_number = b.risk_number and prgbi_tsi.revision_number = link.revision_number
join (
select risk_number,revision_number,premium_amount
from “tbl_ra_risk_premium”
where outward_name in (‘Indonesian standard earthquake reinsurance (MAIPARK)’,'Indonesian standard earthquake reinsurance (MAIPARK) 09H2′)
) prgbi_premium on prgbi_premium.risk_number = b.risk_number and prgbi_premium.revision_number = link_b.revision_number
where b.insurance_product_code = ’115′;
–Download Report
–=============================================================================================================================
select cust.sales_number,
cust.revision_number,
–obj.object_number,
yr.outward_year,
line.period2 as produksi,
cust.customer_name,
cust.insurance_periode_from_awal,
cust.insurance_period_from,
cust.insurance_period_to,
cust.insurance_product_code,
cust.insurance_product_name,
cust.revision_flag as policy_status,
cust.occupation_code,
cust.reference,
cust.risk_number,
cust.revision_flag,
–declared_value_new as tsi_new,
–declared_value_old as tsi_old,
prgbi_tsi,
prgbi_premium,
OLD_H_BUILDING,
OLD_H_STOCK,
OLD_H_MACHINERY,
OLD_H_CONTENT,
OLD_H_OTHERS,
H_BUILDING,
H_STOCK,
H_MACHINERY,
H_CONTENT,
H_OTHERS,
–obj.interest_name,
cust.obj_location,
cust.city,
cust.obj_postal_code,
cust.location_id,
cust.building_category,
cust.construction_class,
cust.construction_year,
cust.jumlah_tingkat,
line.cerates as kurs,
line.cid as currency,
cust.own_share,
cust.coins_premium,
cust.sales_status,
total_premi as premi100,
0.025 * total_premi as premi25
from tbl_rpt_bppdan_customer as cust
join (
select risk_number,total_premium as total_premi,case coalesce(total_premium,0) when 0 then 0 else round((total_premium/total_tsi),6) end as total_rate,
total_premi_fire,total_premi_flood,total_premi_rsmd,total_premi_eq,total_tsi,
premium_rate_bppdan_fire,premium_rate_bppdan_flood,premium_rate_bppdan_rsmd,premium_rate_bppdan_eq,
prgbi_tsi,prgbi_premium
from tbl_rpt_bppdan_detail_eq
) as det on det.risk_number = cust.risk_number
join (
select risk_number, outward_year
from “tbl_ra_risk_cession”
group by risk_number,outward_year
) as yr on yr.risk_number = cust.risk_number
inner join (
select cid,
cerates,period2,period1,
split_part(foo.reference,’;',1) as gl_sales_number,
split_part(foo.reference,’;',2) as gl_revision_number,
split_part(foo.reference,’;',3) as gl_risk_number,
sum(amount) as gl_premi
from(
select gen.reference,cerates,period2,period1,
cid,
amount
from (
select gen.reference,cid,temp_no,cerates,period2,period1
from “tbl_gl_general_jem” as gen
where substring(temp_no,1,3) = ‘OTP’
) as gen
join (
select temp_no,amount
from “tbl_gl_lines_jem”
where account in (’6350000′,’6331000′,’6341000′)
) as foo on foo.temp_no = gen.temp_no
) as foo
group by cid,split_part(foo.reference,’;',1),split_part(foo.reference,’;',2),split_part(foo.reference,’;',3),cerates,period2,period1
) as line on gl_sales_number = cust.sales_number and gl_revision_number = cust.revision_number and gl_risk_number = cust.risk_number and line.period1 = cust.production_year and line.period2 = cust.production_month
–where total_premi notnull and total_premi <> 0
where cust.insurance_product_code = ’115′
order by cust.sales_number,cust.revision_number
Alkisah dua orang praktisi IT, seorang Programmer dan Sysadmin sepakat untuk membangun sebuah website. Website yang dibangung adalah website yang menggunakan webserver dan database. User dari seluruh penjuru internet akan berkomunikasi dengan webserver dan meminta webserver untuk menampilkan halaman yang mereka inginkan. Selanjutnya webserver akan meminta kepada database data-data yang dibutuhkan untuk menghasilkan halaman yang dimaksud. Si Programmer bertugas melakukan coding, dan Sysadmin menyiapkan webserver dan database server.
