Procedure - Class Uplaod
Die Procedure wird vom Klasseupload Task benutzt, um Klassen uf emene Objekt via CSV sheet azpasse
CREATE OR REPLACE procedure K.rbsc$task_load_class(
o_text out long
,i_out_job_id pls_integer := null
,i_file_upl_id pls_integer
,i_eff_date date
,i_obj_type_id pls_integer
,i_classif_id pls_integer
)
/*
-------------------------------------------------------------------------------------------------------------
Bank :
Business Area :
Description : This task is used to update classes via csv-file using the Avaloq Client
Owner :
-------------------------------------------------------------------------------------------------------------
ID Date Name, Company Issue Change Log or Description
-------------------------------------------------------------------------------------------------------------
001
*/
is
-- parse csv
c_sep constant varchar2(1) := ';';
c_null constant varchar2(4) := 'null';
c_auto constant varchar2(4) := 'auto';
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_inv_map_tab def.map_number;
l_data_tab def.tab_char400;
l_obj_id pls_integer;
l_obj_key_id pls_integer;
l_line_has_err boolean := false;
l_err_list varchar2(1000) := null;
l_end_date date;
l_factor pls_integer;
l_class_list varchar2(2000);
l_obj_class2_log_id pls_integer;
l_auto boolean;
-- parse output handling
l_parse_err_cnt pls_integer := 0;
l_insert_cnt pls_integer := 0;
l_obj_type_name varchar2(40);
l_classif_name varchar2(40);
l_classif_type_id pls_integer;
-- parameter for obj recalc
l_db_list_nr db_list#.t_db_list_nr;
l_list_number_id number;
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 := 40
) 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 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;
procedure submit_recalc_task(
i_obj_type_list_id number
,i_eff_date date
,i_list_number_id number
)
is
--
l_restr_sql_stmt varchar2(4000) := 'select val from list_number where id = '||i_list_number_id;
l_skip_lock_obj varchar2(1) := '+';
--
l_task_exec task_exec#.t_task_exec;
l_task_par_id pls_integer;
begin
l_task_exec := task_exec#.task_exec#new(i_task_def_id => lookup_ddic#.task_def_id('TASK_RECALC_CLASSIF_DERIV2',221,null,'+',null)
,i_task_templ_id => null
,i_task_descn => null
);
l_task_par_id := task#.task_def#par_id(
task_exec#.task_exec#task_def_id(l_task_exec)
,'i_obj_type_list_id'
);
task_exec#.task_exec#add_val(
i_task_exec => task_exec#.task_exec_par#task_exec_id(
rpad(l_task_exec, 10) || l_task_par_id
)
,i_par_id => task_exec#.task_exec_par#par_id(
rpad(l_task_exec, 10) || l_task_par_id
)
,i_val_num => i_obj_type_list_id
);
l_task_par_id := task#.task_def#par_id(
task_exec#.task_exec#task_def_id(l_task_exec)
,'i_eff_date'
);
task_exec#.task_exec#add_val(
i_task_exec => task_exec#.task_exec_par#task_exec_id(
rpad(l_task_exec, 10) || l_task_par_id
)
,i_par_id => task_exec#.task_exec_par#par_id(
rpad(l_task_exec, 10) || l_task_par_id
)
,i_val_date => base#.today
);
l_task_par_id := task#.task_def#par_id(
task_exec#.task_exec#task_def_id(l_task_exec)
,'i_restr_sql_stmt'
);
task_exec#.task_exec#add_val(
i_task_exec => task_exec#.task_exec_par#task_exec_id(
rpad(l_task_exec, 10) || l_task_par_id
)
,i_par_id => task_exec#.task_exec_par#par_id(
rpad(l_task_exec, 10) || l_task_par_id
)
,i_val_txt => l_restr_sql_stmt
);
l_task_par_id := task#.task_def#par_id(
task_exec#.task_exec#task_def_id(l_task_exec)
,'i_skip_lock_obj'
);
task_exec#.task_exec#add_val(
i_task_exec => task_exec#.task_exec_par#task_exec_id(
rpad(l_task_exec, 10) || l_task_par_id
)
,i_par_id => task_exec#.task_exec_par#par_id(
rpad(l_task_exec, 10) || l_task_par_id
)
,i_val_txt => l_skip_lock_obj
);
task_exec#.submit(
i_task_def_id => task_exec#.task_exec#task_def_id(l_task_exec)
,i_templ_id => task_exec#.task_exec#task_templ_id(l_task_exec)
,i_val_tab => task_exec#.task_exec#val_tab(l_task_exec)
,i_prcq_id => null
,i_prcq_item_id => null
,i_no_autr => false
,i_bu_id => null
,i_task_descn => task_exec#.task_exec#task_descn(l_task_exec)
,i_trace_prof_list_id => null
,i_doc => null
);
task_exec#.task_exec#remv(l_task_exec);
end submit_recalc_task;
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);
o_text := 'File uploaded successfully ('||buf#.buf#len(l_buf)||' characters)'||chr(10);
-- get object name
select name
into l_obj_type_name
from code_obj_type
where id = i_obj_type_id;
-- get classif name
select name
,obj_classif_type_id
into l_classif_name
,l_classif_type_id
from code_obj_classif
where id = i_classif_id;
-- get db list number
l_db_list_nr := db_list#.db_list_nr#new;
loop
-- ensure that the auto flag is set by default to false
l_auto := false;
-- ensure that during parsing the o_text variable has no buffer overflow
if check_otext_length(o_text) then
l_err_list := substr(o_text, 50, 960); -- remove non error messages in o_text ('File uploaded successfully...')
l_err_list := substr(l_err_list, 1, instr(l_err_list,chr(10),-2)); -- remove not completed error line at the end
raise_ui_err(1, 'Too many errors during parsing, stopped at line '||l_line_nr||chr(10)||chr(10)||
'First errors found are listed below:'||chr(10)||l_err_list);
exit;
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;
-- remove trailing TAB, CR and SPACE characters
l_data_line := rtrim(l_data_line, chr(13)||' ');
-- split-up line according to separator
l_data_tab := split_line(l_data_line, l_sep);
if l_line_nr = 1 then
-- build field mapping table
for x in 1 .. l_data_tab.count loop
l_inv_map_tab(lower(l_data_tab(x))) := x;
end loop;
-- check whether necessary column headers are available
if not l_inv_map_tab.exists('class_id') or not l_inv_map_tab.exists('key') or
not l_inv_map_tab.exists('key_type') then
raise_ui_err(1, 'The following headers in the csv file are mandatory: ' ||chr(13)||chr(13)||'class_id, key, key_type');
end if;
else
-- pre-processing line data key_type
if l_inv_map_tab.exists('key_type') then
l_obj_key_id := is_number(l_data_tab(l_inv_map_tab('key_type')));
else
l_obj_key_id := null;
end if;
if l_inv_map_tab.exists('end_date') then
l_end_date := is_date(l_data_tab(l_inv_map_tab('end_date')),'DD.MM.YYYY');
end if;
if l_inv_map_tab.exists('factor') then
l_factor := is_number(l_data_tab(l_inv_map_tab('factor')));
end if;
-- do validations for key_val
if not l_data_tab.exists(l_inv_map_tab('key')) then -- value available?
l_parse_err_cnt := l_parse_err_cnt + 1;
l_line_has_err := true;
o_text := o_text||chr(10)||' Line '||l_line_nr||': key is missing';
-- value not null?
elsif l_data_tab(l_inv_map_tab('key')) is null then
l_parse_err_cnt := l_parse_err_cnt + 1;
l_line_has_err := true;
o_text := o_text||chr(10)||' Line '||l_line_nr||': key is empty';
-- key available?
elsif lookup#.obj_id(l_data_tab(l_inv_map_tab('key')), l_obj_key_id, i_obj_type_id) is null then
l_parse_err_cnt := l_parse_err_cnt + 1;
l_line_has_err := true;
o_text := o_text||chr(10)||' Line '||l_line_nr||': key is unknown, key (type: '||
nvl(to_char(l_obj_key_id),'none')||'): '||l_data_tab(l_inv_map_tab('key'));
else
-- get obj ID for the key values
l_obj_id := lookup#.obj_id(l_data_tab(l_inv_map_tab('key')), l_obj_key_id, i_obj_type_id);
end if;
-- do validations for class_id
if not l_data_tab.exists(l_inv_map_tab('class_id')) then
l_parse_err_cnt := l_parse_err_cnt + 1;
l_line_has_err := true;
o_text := o_text||chr(10)||' Line '||l_line_nr||': class_id is missing';
-- value not null?
elsif l_data_tab(l_inv_map_tab('class_id')) is null then
l_parse_err_cnt := l_parse_err_cnt + 1;
l_line_has_err := true;
o_text := o_text||chr(10)||' Line '||l_line_nr||': class_id is empty';
-- does class_id belong to stated classif?
elsif (l_data_tab(l_inv_map_tab('class_id')) != c_null and l_data_tab(l_inv_map_tab('class_id')) != c_auto) and
nvl(lookup#.fld('code_obj_class',l_data_tab(l_inv_map_tab('class_id')),'obj_classif_id'),-1) != i_classif_id then
l_parse_err_cnt := l_parse_err_cnt + 1;
l_line_has_err := true;
o_text := o_text||chr(10)||' Line '||l_line_nr||': class_id '||l_data_tab(l_inv_map_tab('class_id'))||
' does not belong to classif '||i_classif_id;
-- is class_id set textwise to null?
elsif l_data_tab(l_inv_map_tab('class_id')) = c_null and l_classif_type_id in (def_obj_classif_type.hist, def_obj_classif_type.nohist) then
l_data_tab(l_inv_map_tab('class_id')) := null;
elsif l_data_tab(l_inv_map_tab('class_id')) = c_null and l_classif_type_id not in (def_obj_classif_type.hist, def_obj_classif_type.nohist) then
l_line_has_err := true;
o_text := o_text||chr(10)||' Line '||l_line_nr||': class_id '||l_data_tab(l_inv_map_tab('class_id'))||
' is not allowed for multi-value classes';
-- is class_id set textwise to auto?
elsif l_data_tab(l_inv_map_tab('class_id')) = c_auto and l_classif_type_id in (def_obj_classif_type.hist, def_obj_classif_type.nohist) then
l_data_tab(l_inv_map_tab('class_id')) := null;
l_auto := true;
-- is class_id the same than before?
elsif l_classif_type_id != def_obj_classif_type.set_ and l_data_tab(l_inv_map_tab('class_id')) = obj_class#.obj#class_id(l_obj_id, i_classif_id, i_eff_date) then
l_line_has_err := true;
o_text := o_text||chr(10)||' Line '||l_line_nr||': class_id '||l_data_tab(l_inv_map_tab('class_id'))||
' is the same as already set';
elsif l_classif_type_id not in (def_obj_classif_type.hist, def_obj_classif_type.nohist) then
begin
select listagg(obj_class_id,',') within group (order by obj_class_id)
into l_class_list
from k.obj_class2 oc
where oc.obj_classif_id = i_classif_id
and oc.obj_id = l_obj_id
and i_eff_date between oc.start_date and oc.end_date
group by obj_id;
exception
when no_data_found then
l_class_list := null;
end;
if instr(l_class_list,l_data_tab(l_inv_map_tab('class_id'))) > 0 and l_data_tab(l_inv_map_tab('end_date')) != c_null then
l_line_has_err := true;
o_text := o_text||chr(10)||' Line '||l_line_nr||': class_id '||l_data_tab(l_inv_map_tab('class_id'))||
' is the same as already set';
end if;
end if;
-- update class if not already set to the desired value
if not (l_line_has_err) then
if l_classif_type_id in (def_obj_classif_type.hist, def_obj_classif_type.nohist) then
obj_class_intf#.obj#add_class(
i_obj_id => l_obj_id
,i_eff_date => i_eff_date
,i_obj_classif_id => i_classif_id
,i_obj_class_id => l_data_tab(l_inv_map_tab('class_id'))
,i_obj_class_a => l_auto
,i_class_factor => l_factor
);
else
if l_data_tab(l_inv_map_tab('end_date')) = c_null then
begin
select obj_class2_log_id
into l_obj_class2_log_id
from obj_class2 oc
where oc.obj_classif_id = i_classif_id
and oc.obj_id = l_obj_id
and i_eff_date between oc.start_date and oc.end_date;
exception
when no_data_found then
l_obj_class2_log_id := null;
end;
if l_obj_class2_log_id is not null then
obj_class_intf#.obj#remv_class (
i_obj_id => l_obj_id
,i_obj_class2_log_id => l_obj_class2_log_id
,i_eff_date => i_eff_date
);
end if;
else
obj_class_intf#.obj#add_class(
i_obj_id => l_obj_id
,i_eff_date => i_eff_date
,i_obj_classif_id => i_classif_id
,i_obj_class_id => l_data_tab(l_inv_map_tab('class_id'))
,i_start_date => i_eff_date
,i_end_date => nvl(l_end_date,def.max_date)
,i_class_factor => l_factor
);
end if;
end if;
l_insert_cnt := l_insert_cnt + 1;
-- collect obj data for recalc
db_list#.db_list_nr#add_item(l_db_list_nr, l_obj_id);
end if;
end if;
l_line_has_err := false;
l_line_nr := l_line_nr + 1;
end loop;
-- store obj_data for recalc if there is at least one dataset
if l_insert_cnt > 0 then
db_list#.db_list_nr#store_list(l_db_list_nr);
l_list_number_id := db_list#.db_list_nr#list_nr_id(l_db_list_nr);
end if;
if l_parse_err_cnt > 0 then
o_text := o_text||chr(10);
end if;
if l_insert_cnt = 0 then
o_text := o_text||chr(10)||'FILE DISCARDED.'||chr(10)||'No valid records found.';
-- 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.discd);
else
o_text := o_text||chr(10)||'PROCESSING FILE: '||l_insert_cnt||' valid records found.'||chr(10);
submit_recalc_task(
i_obj_type_list_id => i_obj_type_id
,i_eff_date => i_eff_date
,i_list_number_id => l_list_number_id
);
-- 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);
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_class('
||'i_out_job_id=>'||i_out_job_id
||',i_file_upl_id=>'||i_file_upl_id
||')'
);
end rbsc$task_load_class;
/
