Cut Off Task - Procedure
E Task, wo duet es CSV parse und Orders via pl/sql generiere
CREATE OR REPLACE procedure K.rbsc$task_load_cut_off(
o_text out long
,i_out_job_id pls_integer := null
,i_file_upl_id pls_integer
,i_provider_id pls_integer
,i_add_id pls_integer := null
,i_time_offset pls_integer := null
,i_add_postit boolean := false
,i_postit_text varchar2 := null
)
/*
-------------------------------------------------------------------------------------------------------------
Bank : RBSC Coutts Bank Ltd.
Business Area : Business Applications Team
Description : This task is used to upload cutoff times via AVQ client
Owner : RBS Coutts SDA
-------------------------------------------------------------------------------------------------------------
ID Date Name, Company Issue Change Log or Description
-------------------------------------------------------------------------------------------------------------
001
*/
is
c_sep constant varchar2(1) := ';';
c_ubs_day_time constant timestamp := to_timestamp('17:00','HH24:MI');
c_ubs_day_from constant timestamp := to_timestamp('8:30','HH24:MI');
c_ubs_day_to constant timestamp := to_timestamp('17:30','HH24:MI');
c_ubs_delta constant pls_integer := 30;
c_bnp_day_time constant timestamp := to_timestamp('16:45','HH24:MI');
c_bnp_day_from constant timestamp := to_timestamp('9:30','HH24:MI');
c_bnp_day_to constant timestamp := to_timestamp('18:15','HH24:MI');
c_bnp_delta constant pls_integer := 90;
c_fs_day_time constant timestamp := to_timestamp('16:45','HH24:MI');
c_fs_day_from constant timestamp := to_timestamp('9:30','HH24:MI');
c_fs_day_to constant timestamp := to_timestamp('18:15','HH24:MI');
c_fs_delta constant pls_integer := 90;
c_asset_cutoff_time_fs constant pls_integer := lookup#.code_id('code_obj_add', 'rbsc$asset_cutoff_time_fs', 'intl2_id,intl_id');
c_asset_cutoff_time_ubs constant pls_integer := lookup#.code_id('code_obj_add', 'asset_cutoff_time_ubs', 'intl2_id,intl_id');
c_asset_cutoff_time_bnp constant pls_integer := lookup#.code_id('code_obj_add', 'asset_cutoff_time_bnp', 'intl2_id,intl_id');
l_sep varchar2(1);
l_buf buf#.t_buf;
l_is_eof boolean;
l_line_nr pls_integer := 1;
l_data_line varchar2(32000);
l_data_line_add varchar2(32000);
l_inv_map_tab def.map_number;
l_data_tab def.tab_char400;
l_asset_id pls_integer := 0;
l_cutoff_subscr varchar2(100);
l_provider varchar2(3);
l_time_offset varchar2(10);
l_time_old_offset varchar2(10);
l_nec_header varchar2(200) :=null;
l_ubs_main_cur_id pls_integer :=0;
l_asset_add_id pls_integer :=0;
l_asset_add_oth_id pls_integer :=0;
l_asset_in_scope pls_integer :=0;
l_asset_in_scope_cnt pls_integer :=0;
l_asset_same_cutoff_cnt pls_integer :=0;
l_asset_out_scope pls_integer :=0;
l_start_time timestamp;
l_end_time timestamp;
l_avg_prc_time varchar2(20);
l_err_full boolean := false;
c_no_trace constant number := ld_lib_2.cnv_code_sql('code_log_type', 'info', 'intl_id');
c_trace_low constant number := ld_lib_2.cnv_code_sql('code_log_type', 'warn', 'intl_id');
c_trace_high constant number := ld_lib_2.cnv_code_sql('code_log_type', 'alert', 'intl_id');
c_debug constant number := ld_lib_2.cnv_code_sql('code_env_module', 'rbsc$omd', 'intl_id');
c_trace constant number := c_no_trace;
c_mod constant number :=
case c_trace
when c_no_trace then 10000
when c_trace_low then 100
when c_trace_high then 1
else 10000 end;
function get_sep(
i_hdr_line varchar2
) return varchar2
is
l_num_semicolon pls_integer;
l_num_comma pls_integer;
l_num_tab pls_integer;
l_num_pipe pls_integer;
l_max pls_integer;
begin
l_num_semicolon := length(i_hdr_line)-length(replace(i_hdr_line, ';', ''));
l_num_comma := length(i_hdr_line)-length(replace(i_hdr_line, ',', ''));
l_num_tab := length(i_hdr_line)-length(replace(i_hdr_line, chr(9), ''));
l_num_pipe := length(i_hdr_line)-length(replace(i_hdr_line, '|', ''));
l_max := greatest(l_num_semicolon, l_num_comma, l_num_tab, l_num_pipe);
l_sep := case
when l_num_semicolon = l_max then ';'
when l_num_comma = l_max then ','
when l_num_tab = l_max then chr(9)
when l_num_pipe = l_max then '|'
else c_sep
end;
return l_sep;
exception
when others then
raise_fa_err('get_sep('||i_hdr_line||')');
end get_sep;
function split_line(
i_data_line varchar2
,i_sep varchar2
,i_max_el_cnt pls_integer := 100
) return def.tab_char400
is
l_el_cnt pls_integer := 0;
l_data_tab def.tab_char400;
l_start_pos pls_integer;
l_sep_pos pls_integer;
begin
l_start_pos := 1;
loop
l_sep_pos := nvl(instr(i_data_line, i_sep, l_start_pos), 0);
exit when l_sep_pos = 0;
l_data_tab(l_data_tab.count+1) := substr(i_data_line, l_start_pos, l_sep_pos-l_start_pos);
l_start_pos := l_sep_pos + 1;
l_el_cnt := l_el_cnt + 1;
assert(l_el_cnt < i_max_el_cnt, 'Max element count not reached '||i_max_el_cnt);
end loop;
l_data_tab(l_data_tab.count+1) := substr(i_data_line, l_start_pos);
return l_data_tab;
exception
when others then
raise_fa_err('split_line('||i_data_line||','||i_sep||')');
end split_line;
function apply_time_offset(
i_cutoff varchar2, i_provider varchar2
)return varchar2
is
l_apply_time_offset timestamp;
l_offset pls_integer :=
case i_provider
when 'BNP' then c_bnp_delta
when 'UBS' then c_ubs_delta
when 'FS' then c_fs_delta
else 0 end;
l_day_from timestamp :=
case i_provider
when 'BNP' then c_bnp_day_from
when 'UBS' then c_ubs_day_from
when 'FS' then c_fs_day_from
else null
end;
l_day_to timestamp :=
case i_provider
when 'BNP' then c_bnp_day_to
when 'UBS' then c_ubs_day_to
when 'FS' then c_fs_day_to
else null
end;
l_day_time timestamp :=
case i_provider
when 'BNP' then c_bnp_day_time
when 'UBS' then c_ubs_day_time
when 'FS' then c_fs_day_time
else null
end;
begin
if i_time_offset is not null then
l_offset := i_time_offset;
end if;
l_apply_time_offset := to_timestamp(i_cutoff, 'HH24:MI');
l_apply_time_offset := l_apply_time_offset - numtodsinterval(abs(l_offset), 'MINUTE');
return to_char(l_apply_time_offset, 'HH24:MI');
exception
when others then
if sqlcode = -1858 then return null;-- ora-01858: garbled input
else raise_fa_err('apply_time_offset('|| i_cutoff ||')'); end if;
end apply_time_offset;
function apply_night_constant(
i_cutoff varchar2, i_provider varchar2
)return varchar2
is
l_apply_night_constant timestamp;
l_day_from timestamp :=
case i_provider
when 'BNP' then c_bnp_day_from
when 'UBS' then c_ubs_day_from
when 'FS' then c_fs_day_from
else null
end;
l_day_to timestamp :=
case i_provider
when 'BNP' then c_bnp_day_to
when 'UBS' then c_ubs_day_to
when 'FS' then c_fs_day_to
else null
end;
l_day_time timestamp :=
case i_provider
when 'BNP' then c_bnp_day_time
when 'UBS' then c_ubs_day_time
when 'FS' then c_fs_day_time
else null
end;
begin
l_apply_night_constant := to_timestamp(i_cutoff, 'HH24:MI');
if l_apply_night_constant not between l_day_from and l_day_to then
l_apply_night_constant := l_day_time;
end if;
return to_char(l_apply_night_constant, 'HH24:MI');
exception
when others then
if sqlcode = -1858 then return null;-- ora-01858: garbled input
else raise_fa_err('apply_night_constant('|| i_cutoff ||')'); end if;
end apply_night_constant;
function check_otext_length(
i_otext long := null
) return boolean
is
l_len_max boolean := true;
begin
if length(i_otext) < 31500 then
l_len_max := false;
end if;
return l_len_max;
exception
when others then
raise_fa_err('check_otext_length('||i_otext||')');
end check_otext_length;
begin
require(i_file_upl_id is not null, 'file upload failed');
-- Set file type to "CSV": ensure conversion of RAW BLOB to CLOB (=standard characterset)
rbsc$file_upl_util#.file_upl#set_type(i_file_upl_id, def_file_upl_type.csv);
-- Set status to "dispatching"
rbsc$file_upl_util#.file_upl#set_status(i_file_upl_id, def_file_upl_status.dsp);
-- Get buffer for processing of file content
rbsc$file_upl_util#.file_upl#get_buf(l_buf, i_file_upl_id);
-- get provider and their respective addition
case
when i_provider_id = 11128 then
l_provider:= 'FS';
l_asset_add_id := c_asset_cutoff_time_fs;
l_asset_add_oth_id := null;
when i_provider_id = 11127 then
l_provider:= 'BNP';
l_asset_add_id := c_asset_cutoff_time_bnp;
l_asset_add_oth_id := c_asset_cutoff_time_ubs;
when i_provider_id = 11126 then
l_provider:= 'UBS';
l_asset_add_id := c_asset_cutoff_time_ubs;
l_asset_add_oth_id := c_asset_cutoff_time_bnp;
when i_provider_id = 509397 then
if i_add_id is null then
-- ensure for provider other that an addition is set
raise_ui_err(1, 'If you choose as provider ''Other'' you have to set a value under additions');
end if;
l_provider:='OTH';
l_asset_add_id := i_add_id;
l_asset_add_oth_id := null;
end case;
-- task output informations
o_text := 'File uploaded successfully ('||buf#.buf#len(l_buf)||' characters)'||chr(10);
l_start_time := sysdate;
o_text := o_text||chr(10)||'Task started: '||l_start_time;
if c_trace >= c_trace_high or co_err.get_debug(c_debug) then
k.log#.alert(i_context => 'rbsc$task_load_cut_off - start: ' || to_char(l_start_time, 'HH24:MI:SS'));
delete rbsc$task_load$trace$tab;
end if;
loop
if c_trace >= c_trace_low and (mod(l_line_nr, c_mod) = 0 or co_err.get_debug(c_debug)) then
k.log#.warn(i_context => 'rbsc$task_load_cut_off: number of processed records:' || l_line_nr);
end if;
-- ensure that o_text
if check_otext_length(o_text) then
o_text := substr(o_text, 0, 850)||chr(10)||'max number of errors showed, but more occured...'||chr(10); --
l_err_full := true;
end if;
-- get next line from buffer
buf#.buf#read_line(l_data_line, l_is_eof, l_buf);
exit when l_is_eof;
if l_line_nr = 1 then
-- detect field separator
l_sep := get_sep(l_data_line);
end if;
-- get rid of the ugly cr/lf within the xl cell
if(mod(length(l_data_line)-length(replace(l_data_line, '"', '')),2) > 0) then
loop
l_data_line_add := l_data_line;
l_line_nr := l_line_nr + 1;
buf#.buf#read_line(l_data_line, l_is_eof, l_buf);
l_data_line := l_data_line_add||l_data_line;
exit when l_is_eof;
if (mod(length(l_data_line)-length(replace(l_data_line, '"', '')),2) = 0) then
exit;
end if;
end loop;
end if;
l_data_line := rtrim(l_data_line, chr(13)||' '); -- remove trailing TAB, CR and SPACE characters
l_data_tab := split_line(l_data_line, l_sep); -- split-up line according to separator
if c_trace >= c_trace_high or co_err.get_debug(c_debug) then
declare
l_data_tab_tst t_char4000_tab;
begin
select column_value bulk collect into l_data_tab_tst from table(l_data_tab);
insert into rbsc$task_load$trace$tab
values (rbsc$task_load$trace$seq.nextval, c_trace_high, l_data_tab_tst);
if mod(l_line_nr, c_mod) = 0 or co_err.get_debug(c_debug) then commit; end if;
exception
when others then
k.log#.alert(i_context => 'rbsc$task_load_cut_off - insert in trace tab failed - error:' || sqlerrm);
end;
end if;
if l_line_nr = 1 then
-- build field mapping table
for x in 1 .. l_data_tab.count loop
l_data_tab(x) := nvl(l_data_tab(x), '##empty_'||x);
l_inv_map_tab(lower(l_data_tab(x))) := x;
end loop;
-- check whether necessary universal column headers are available
if not l_inv_map_tab.exists('isin') then
l_nec_header := ', ISIN';
end if;
-- check whether necessary BNP column headers are available
if l_provider = 'BNP' then
if not l_inv_map_tab.exists('fund cut off swift (remark) redemption') then
l_nec_header := l_nec_header||', Fund cut off swift (remark) Redemption';
end if;
if not l_inv_map_tab.exists('nav frequency subscription') then
l_nec_header := l_nec_header||', Nav frequency subscription';
end if;
end if;
-- check whether necessary UBS column headers are available
if l_provider = 'UBS' then
if not l_inv_map_tab.exists('isocurrencycode') then
l_nec_header := l_nec_header||', IsoCurrencyCode';
end if;
if not l_inv_map_tab.exists('primmktcode') then
l_nec_header := l_nec_header||', PrimMktCode';
end if;
if not l_inv_map_tab.exists('cutofftime') then
l_nec_header := l_nec_header||', CutOffTime';
end if;
end if;
-- check whether necessary FS column headers are available
if l_provider = 'FS' then
if not l_inv_map_tab.exists('avaloq cut off') then
l_nec_header := l_nec_header||', Avaloq Cut off';
end if;
end if;
-- check whether necessary OTH column headers are available
if l_provider = 'OTH' then
if not l_inv_map_tab.exists('cutofftime') then
l_nec_header := l_nec_header||', CutOffTime';
end if;
end if;
--raise on error
if length(l_nec_header)-length(replace(l_nec_header, ',', '')) = 1 then
raise_ui_err(1, 'The following header in the csv file is mandatory: '||chr(13)||chr(13)||substr(l_nec_header,2));
elsif length(l_nec_header)-length(replace(l_nec_header, ',', '')) > 1 then
raise_ui_err(1, 'The following headers in the csv file are mandatory: '||chr(13)||chr(13)||substr(l_nec_header,2));
end if;
else
-- lookup asset_id
l_asset_id := lookup#.asset_id(l_data_tab(l_inv_map_tab('isin')), 1);
-- is the asset in scope?
select count(*)
into l_asset_in_scope
from obj_class oc
,obj ob
where 1=1
and ob.id = oc.obj_id
and trunc(sysdate) < nvl(ob.close_date, trunc(sysdate)+1)
and oc.obj_id = l_asset_id
and oc.obj_class_id in (3222, 5390);
--
if l_asset_in_scope > 0 then
-- process BNP specific data
if l_provider = 'BNP' then
-- get current cutoff time of addition
select asset_cutoff_time_bnp
into l_time_old_offset
from obj_asset_add
where obj_id = l_asset_id;
if l_data_tab(l_inv_map_tab('nav frequency subscription')) = 'Daily with excluded dates'
and regexp_substr(l_data_tab(l_inv_map_tab('fund cut off swift (remark) redemption')), '^"[0-9] B') = '"0 B'
and l_data_tab(l_inv_map_tab('fund cut off swift (remark) redemption')) is not null then
l_cutoff_subscr := substr(regexp_substr(l_data_tab(l_inv_map_tab('fund cut off swift (remark) redemption')),'[0-9:]+ \(cut off swift\)'),1, 5);
l_time_offset := apply_night_constant(apply_time_offset(l_cutoff_subscr, l_provider), l_provider);
-- did the offset time not change compared to the existing one then ignore
if l_time_offset = l_time_old_offset then
l_asset_in_scope := 0;
l_asset_same_cutoff_cnt := l_asset_same_cutoff_cnt +1;
end if;
else
l_asset_in_scope := 0;
l_asset_out_scope := l_asset_out_scope +1;
end if;
end if;
-- process UBS specific data
if l_provider = 'UBS' then
-- get current cutoff time of addition
select asset_cutoff_time_ubs
into l_time_old_offset
from obj_asset_add
where obj_id = l_asset_id;
-- get currency of prio 1 market
select trade_curry_id
into l_ubs_main_cur_id
from obj_asset_mkt_md_scen
where asset_id = l_asset_id
and prio = 1;
-- only the entry of the main market should be processed
if l_ubs_main_cur_id = lookup#.curry_id(l_data_tab(l_inv_map_tab('isocurrencycode')), 9) then
-- only PrimMktCode P and PS will be considered
if l_data_tab(l_inv_map_tab('primmktcode')) in ('P', 'PS') then
l_cutoff_subscr := regexp_substr(l_data_tab(l_inv_map_tab('cutofftime')),'[0-9]{1,2}:[0-9][0-9]');
l_time_offset := apply_night_constant(apply_time_offset(l_cutoff_subscr, l_provider), l_provider);
-- did the offset time not change compared to the existing one then ignore
if l_time_offset = l_time_old_offset then
l_asset_in_scope := 0;
l_asset_same_cutoff_cnt := l_asset_same_cutoff_cnt +1;
end if;
else
l_asset_in_scope := 0;
l_asset_out_scope := l_asset_out_scope +1;
end if;
else
l_asset_in_scope := 0;
l_asset_out_scope := l_asset_out_scope +1;
end if;
end if;
if l_provider = 'FS' then
-- get current cutoff time of addition
select rbsc$asset_cutoff_time_fs
into l_time_old_offset
from obj_asset_add
where obj_id = l_asset_id;
if regexp_like(l_data_tab(l_inv_map_tab('avaloq cut off')), '[0-9]{1,2}:[0-9][0-9]')
and l_data_tab(l_inv_map_tab('avaloq cut off')) is not null then
l_cutoff_subscr := substr(regexp_substr(l_data_tab(l_inv_map_tab('avaloq cut off')),'[0-9:]+'), 1, 5);
l_time_offset := apply_night_constant(l_cutoff_subscr, l_provider);
-- did the offset time not change compared to the existing one then ignore
if l_time_offset = l_time_old_offset then
l_asset_in_scope := 0;
l_asset_same_cutoff_cnt := l_asset_same_cutoff_cnt +1;
end if;
else
l_asset_in_scope := 0;
l_asset_out_scope := l_asset_out_scope +1;
end if;
end if;
-- process OTH specific data
if l_provider = 'OTH' then
l_cutoff_subscr := regexp_substr(l_data_tab(l_inv_map_tab('cutofftime')),'[0-9]{1,2}:[0-9][0-9]');
l_time_offset := apply_night_constant(apply_time_offset(l_cutoff_subscr, l_provider), l_provider);
end if;
elsif l_asset_in_scope = 0 then
l_asset_in_scope := 0;
l_asset_out_scope := l_asset_out_scope +1;
end if;
-- create the order if in scope
if l_asset_in_scope > 0 then
-- AVQ script which is the base of pl/sql code below
-- begin
-- script#.do_dyn('
-- declare
-- l_asset_id number := '||l_asset_id||';
-- l_cut text :="'||l_time_offset||'";
-- l_postit_add boolean := '||bool_to_char(i_add_postit)||';
-- l_postit_txt text :="'||substr(i_postit_text,1,100)||'";
-- begin
-- with new mem_doc_asset(wfc_action_id => 1100, asset_id => l_asset_id) as doc do
-- doc.add('||l_asset_add_id||') := l_cut;
-- if '||l_asset_add_oth_id||' is not null then
-- if doc.add('||l_asset_add_oth_id||') is null and l_cut is null then
-- doc.obj_extn.asset_auto_funds_pool_id := null;
-- else
-- doc.obj_extn.asset_auto_funds_pool_id := 5033;
-- end if;
-- end if
-- if l_postit_add then
-- doc.postit_list.add(topic=>"Cutoff Task: Addition updated", text=>l_postit_txt);
-- end if;
-- doc.do_wfc_action(10190);
-- end with;
-- end;
-- session.reset;
-- ');
-- exception
-- when others then
-- o_text := o_text||chr(10)||l_asset_id||' ';
-- o_text := o_text||trim(regexp_replace(replace(sqlerrm, chr(10), ' '),'ORA-20001: [0-9]+:',''));
-- end;
-- create the order
declare
l_doc doc_mgr#.t_doc;
l_postit doc_tab_postit#.t_postit;
begin
-- create order
l_doc := doc_mgr#.stack#next_doc;
doc_asset#.doc#new(l_doc);
doc_asset#.doc#do_wfc_action(l_doc, i_wfc_action_id => 1100, i_is_origin => true, i_asset_id => l_asset_id);
-- fill additions, 5033 asset_auto_funds_pool, 5230 code_const yes
doc_tab_obj_add#.add#set_text(doc_tab_obj_add#.doc#add(l_doc, l_asset_add_id), l_time_offset);
if l_asset_add_oth_id is not null then
if doc_tab_obj_add#.add#text(doc_tab_obj_add#.doc#add(l_doc, l_asset_add_oth_id)) is null and l_time_offset is null then
doc_tab_obj_add#.add#set_id(doc_tab_obj_add#.doc#add(l_doc, 5033), null);
else
doc_tab_obj_add#.add#set_id(doc_tab_obj_add#.doc#add(l_doc, 5033), 5230);
end if;
end if;
-- add postit
if i_add_postit then
l_postit := doc_tab_postit#.doc#add_postit(
i_doc => l_doc
,i_topic => 'Cutoff Task: Addition updated'
,i_text => substr(i_postit_text,1,100)
,i_postit_type_id => null
,i_postit_sub_type_id => null
,i_role_id => null
,i_copy_to => false
);
end if;
-- verify order
doc_asset#.doc#do_wfc_action(l_doc, i_wfc_action_id => 10190, i_is_origin => true, i_asset_id => null);
-- reset
doc_dsp.new_id.delete;
doc_mgr#.reset(null);
exception
when others then
if not l_err_full then
o_text := o_text||chr(10)||l_asset_id||' ';
o_text := o_text||trim(regexp_replace(replace(sqlerrm, chr(10), ' '),'ORA-20001: [0-9]+:',''));
end if;
end;
--count in scope 1 up
l_asset_in_scope_cnt := l_asset_in_scope_cnt + 1;
end if;
end if;
l_line_nr := l_line_nr + 1;
end loop;
-- update report stats
o_text := o_text||chr(10)||'assets match selection criteria: '||l_asset_in_scope_cnt
||chr(10)||'assets don''t match the criteria: '||l_asset_out_scope
||chr(10)||'assets with same cutoff time: '||l_asset_same_cutoff_cnt;
-- Update status to "done" (=so that garbcol will remove the data entry in FILE_UPL)
rbsc$file_upl_util#.file_upl#set_status(i_file_upl_id, def_file_upl_status.done);
l_end_time := sysdate;
o_text := o_text||chr(10)||'Task ended: '||l_end_time;
-- avoid division by zero
if l_asset_in_scope_cnt > 0 then
l_avg_prc_time := substr((l_end_time-l_start_time)/l_asset_in_scope_cnt,12,8);
else
l_avg_prc_time := 'no asset processed';
end if;
o_text := o_text||chr(10)||'average time to proceed an asset: '||l_avg_prc_time;
if c_trace >= c_trace_high or co_err.get_debug(c_debug) then
k.log#.alert(i_context => 'rbsc$task_load_cut_off - end: ' || to_char(l_end_time, 'HH24:MI:SS'));
end if;
commit;
exception
when others then
-- Update status to "error"
rbsc$file_upl_util#.file_upl#set_status(i_file_upl_id, def_file_upl_status.err_dsp);
raise_fa_err('rbsc$task_load_cut_off('
||'i_out_job_id=>'||i_out_job_id
||',i_file_upl_id=>'||i_file_upl_id
||')'
);
end rbsc$task_load_cut_off;
