Solvency - DTM
dr hirarchisch dtm zum Solvency Report
[Report 2.0]
/*
-------------------------------------------------------------------------------------------------------------
Bank:
Business Area:
Description: Used for solvency II reporting
-------------------------------------------------------------------------------------------------------------
ID Date Name, Company Issue Affected BUs, Description
-------------------------------------------------------------------------------------------------------------
001
-------------------------------------------------------------------------------------------------------------
*/
report rbsc$task_pos_list_solvency.hira_dtm
datamart
connect dtm_pos_list as dp
group by
k_prov [dp.bp.extn.rbsc$pen_prov_id]
order by [dp.bp.extn.rbsc$pen_prov.name]
bp [dp.bp_id]
order by [dp.bp.name]
cont [dp.cont_id]
order by [dp.cont.name]
asset [dp.asset_id]
order by [dp.asset.name]
pos [dp.pos_id]
order by [dp.pos.name]
consolidate
on top
file number assign [0]
file_type number assign [case when task_exec.param('csv_prc').text_val = '+' then code_file_type.csv else code_file_type.xls end]
fld_sep text assign [case when task_exec.param('csv_prc').text_val = '+' then ';' else util.tab end]
on k_prov
prov_name text assign [dp.bp.extn.rbsc$pen_prov.name]
file_name text assign [ltrim(rtrim(regexp_replace(regexp_replace(regexp_replace('Solvency_'||dp.bp.extn.rbsc$pen_prov.name, '(.*) \([0-9]+\)$', '\1'), '[^A-Za-z]', '_'), '__+', '_'), '_'), '_')||case when task_exec.param('csv_prc').text_val = '+' then '.csv' else '.xls' end]
on asset
date_from date assign [task_exec.param('date_from').date_val]
date_to date assign [task_exec.param('date_to').date_val]
asset_id number assign [dp.pos.asset_id]
cash_asset text assign [case when dp.pos.asset.extn.ass_type_id = 1512 then 'yes' else 'no' end]
type_id number assign [dp.asset.extn.ass_type]
grp_id number assign [dp.asset.extn.ass_group]
bp_id number assign [dp.bp_id]
bp_nr_key text assign [dp.bp.extn.bp_nr]
cont_id number assign [dp.cont_id]
policy_nr text assign [dp.bp.name || ' ' || dp.cont.name]
dep_prov text assign [dp.asset.issuer.name]
isin text assign [dp.asset.extn.asset_isin]
dep_ref text assign [dp.asset.extn.asset_contr]
qua_fund text assign [dp.cont.extn.rbsc$cont_qua_fund_old]
sedol text assign [dp.asset.extn.asset_ntnl_val(2006)]
curry_id text assign [dp.pos_curry]
curry text assign [dp.pos_curry.name]
fund_curry_id text assign [dp.asset.nom_curry]
fund_curry text assign [dp.asset.nom_curry.name]
name text assign [dp.asset.name]
issuer text assign [dp.asset.issuer.name]
fund_mgr text assign [dp.asset.extn.asset_fund_manager]
units number assign [dp.qty]
price number assign [dp.curr_price]
nom_value number assign [dp.curr_val_pos+dp.accr_pos]
cost_price number assign [dp.hist_price_pos]
curr_xrate number assign [dp.curr_xrate]
curr_val number assign [dp.curr_val_ref+dp.accr_ref]
accr_intr number assign [dp.accr_ref]
val_date date assign [dp.ref_date]
mat_date date assign [dp.asset.maturity_date]
end datamart
end report
