set dta on macc
set dta on macc
---------------------------------------------------------------------------------------------------------------------------
-- Parallelisierung
-- Hilfstabelle anlegen
begin
x.install#.oracle#drop(
i_owner => 'K'
,i_name => 'BLKB$DTA_AUTH'
,i_type => 'TABLE'
);
x.install#.oracle#create(
''
,'BLKB$DTA_AUTH'
,'TABLE'
,'CREATE TABLE K.BLKB$DTA_AUTH
(
SEQ_NR NUMBER(12),
BP_ID NUMBER(12),
MACC_ID NUMBER(12),
PERS_ID NUMBER(12),
DOCM_ID NUMBER(12)
)'
);
end;
/
-- Procedure für das Anhängen der Rolle erstellen
create or replace procedure dta_auth_role (i_bp_id number, i_macc_id number, i_pers_id number, i_docm_id number)
is
l_bp_id varchar2(2000) := i_bp_id;
l_macc_id varchar2(2000) := i_macc_id;
l_pers_id varchar2(2000) := i_pers_id;
l_docm_id varchar2(2000) := i_docm_id;
begin
session#.open_session(i_bu_id=> 1, i_oracle_user=> 'F03264');
script#.do_dyn('
declare
l_bp_id number:= ' || l_bp_id || ';
l_macc_id number:= ' || l_macc_id || ';
l_pers_id number:= ' || l_pers_id || ';
l_docm_id number:= ' || l_docm_id || ';
l_done boolean:= false;
begin
if obj_bp(l_bp_id).has_lock = "-" then
with new mem_doc_bp(3130, l_bp_id) as mem_bp do
for auth_list in mem_bp.bp_person_rel_list loop
if auth_list.auth_role_id = code_auth_role.ebanking_bp_id and auth_list.ref_docm_id = l_docm_id and auth_list.trg_obj_id = l_pers_id then
if auth_list.bp_p_auth_obj_list.count > 0 then
for obj_list in auth_list.bp_p_auth_obj_list loop
if obj_list.obj_id = l_macc_id then
l_done := true;
auth_list.auth_trx_type_list.add(auth_trx_type_id => code_auth_trx_grp.afs_dta_upld_id);
else
if not l_done then
with new mem_bp.bp_person_rel_list as auth_new_list do
auth_new_list.trg_obj_id := l_pers_id;
auth_new_list.auth_role_id := code_auth_role.ebanking_bp_id;
auth_new_list.ref_docm_id := l_docm_id;
auth_new_list.auth_trx_type_id := code_auth_trx_grp.afs_dta_upld_id;
with new auth_new_list.bp_p_auth_obj_list as macc do
macc.obj_id := l_macc_id;
end with;
end with;
end if;
end if;
end loop;
else
with new mem_bp.bp_person_rel_list as auth_new_list do
auth_new_list.trg_obj_id := l_pers_id;
auth_new_list.auth_role_id := code_auth_role.ebanking_bp_id;
auth_new_list.ref_docm_id := l_docm_id;
auth_new_list.auth_trx_type_id := code_auth_trx_grp.afs_dta_upld_id;
with new auth_new_list.bp_p_auth_obj_list as macc do
macc.obj_id := l_macc_id;
end with;
end with;
end if;
end if;
end loop;
mem_bp.add_override(override => code_override.auth_remark_instr_id, force => "+");
mem_bp.do_wfc_action(2090);
end with;
install#.log#write("DTA-Berechtigung erfolgreich BP: " || l_bp_id || " MACC: " || l_macc_id || " PERSON: " || l_pers_id || " DOCM: " || l_docm_id);
else
install#.log#write("DTA-Berechtigung blockiert BP: " || l_bp_id || " MACC: " || l_macc_id || " PERSON: " || l_pers_id || " DOCM: " || l_docm_id);
end if;
exception
when others then
install#.log#write("DTA-Berechtigung gescheitert BP: " || l_bp_id || " MACC: " || l_macc_id || " PERSON: " || l_pers_id || " DOCM: " || l_docm_id);
session.rollback;
end;
session.reset;
');
exception
when others then
dbms_output.put_line(regexp_substr(sqlerrm, '.*')||chr(10)||' log# '||log#.log#init_log_id(co_err.last_err_log_id)||': '||log#.log#ctx(log#.log#init_log_id(co_err.last_err_log_id)));
end;
/
declare
c_max_slaves pls_integer := 10;
l_master_id pls_integer;
l_sql_stmt varchar2(32000);
l_num_slaves pls_integer;
l_max_seq_par_nr pls_integer := 0;
l_scope pls_integer := 0;
l_result pls_integer := 0;
begin
install#.log#write('--- DTA AUTH ROLES: START UPDATE ---', 0);
l_num_slaves := least(c_max_slaves, install#.master#cpu_cnt);
session#.open_session(i_bu_id => 1);
insert into blkb$dta_auth (seq_nr, bp_id, macc_id, pers_id, docm_id)
select 1, 826400, 2586074,11009494,14289789 from dual union
...
select 1,17836578,17878924,10881993,17895891 from dual
;
-- scope Anzahl ermitteln
select count(*) into l_scope from blkb$dta_auth;
install#.log#write('--- DTA AUTH ROLES: ANZAHL BP IM SCOPE: ' || l_scope || ' ---', 0);
-- ermitteln wieviele Iterationen benötigt werden (seq_nr mit mehr als 10 BP)
with max_par_seq_nr as(
select seq_nr
from blkb$dta_auth
where 1=1
having count(seq_nr) > 9
group by seq_nr
)
select max(seq_nr)
into l_max_seq_par_nr
from max_par_seq_nr
;
install#.log#write('--- DTA AUTH ROLES: ANZAHL PARALLELISIERT: ' || l_max_seq_par_nr || ' ---', 0);
if l_max_seq_par_nr > 0 then -- falls der Schwellenwert nicht erreicht wird, gibt es keine Parallelisierung
for i in 1..l_max_seq_par_nr loop
install#.log#write('--- DTA AUTH ROLES: PARALLELISIERT LOOP: ' || i || ' ---', 0);
-- Ausführen des Skripts Part 1 parallelisiert
install#.master#start_slave(i_wait=> true, i_cnt => l_num_slaves, x_master_id => l_master_id);
for c in (
select bp_id, macc_id, pers_id, docm_id
from blkb$dta_auth
where seq_nr = i
)
loop
l_sql_stmt := 'begin dta_auth_role(' || c.bp_id || ',' || c.macc_id || ',' || c.pers_id || ',' || c.docm_id || '); end;';
install#.master#send(i_master_id => l_master_id, i_stmt => l_sql_stmt);
l_result := l_result + 1;
end loop;
install#.master#feedback_wait(i_master_id => l_master_id, i_all => true);
install#.master#stop_slave(i_master_id => l_master_id);
install#.master#reset_master(i_master_id => l_master_id);
end loop;
else
install#.log#write('--- DTA AUTH ROLES: SCHWELLENWERT NICHT ERREICHT, KEINE PARALLELE AUSFÜHRUNG ---', 0);
end if;
install#.log#write('--- DTA AUTH ROLES: SINGLE UPDATE ---', 0);
-- Update der restlichen BP's nicht parallelisiert
for c in (
select bp_id, macc_id, pers_id, docm_id
from blkb$dta_auth
where seq_nr >= nvl(l_max_seq_par_nr, 1)
)
loop
begin
dta_auth_role(c.bp_id, c.macc_id, c.pers_id, c.docm_id);
l_result := l_result + 1;
exception
when others then
dbms_output.put_line(regexp_substr(sqlerrm, '.*')||chr(10)||' log# '||log#.log#init_log_id(co_err.last_err_log_id)||': '||log#.log#ctx(log#.log#init_log_id(co_err.last_err_log_id)));
end;
end loop;
install#.log#write('--- DTA AUTH ROLES: ANZAHL BP MUTIERT: ' || l_result || ' DIFFERENZ: ' || l_scope - l_result || ' ---', 0);
install#.log#write('--- DTA AUTH ROLES: END UPDATE ---', 0);
exception
when others then
dbms_output.put_line(regexp_substr(sqlerrm, '.*')||chr(10)||' log# '||log#.log#init_log_id(co_err.last_err_log_id)||': '||log#.log#ctx(log#.log#init_log_id(co_err.last_err_log_id)));
dbms_session.reset_package;
end;
/
-- Procedure und Hilfstabelle entfernen
drop procedure dta_auth_role;
begin
x.install#.oracle#drop(
i_owner => 'K'
,i_name => 'BLKB$DTA_AUTH'
,i_type => 'TABLE'
);
end;
/
