Spaces us Adrässe lösche
Mit däm Script cha me us Adrässe sämtlichi doppelti Spaces entfärne
-------------------------------------------------------------------------------------------------------------
-- Bank : RBSC Coutts Bank Ltd.
-- Business Area : SDA
-- Description : Script to modify addresses - Coutts/Adam/Jersey
-- Owner : OMD RBSC
-------------------------------------------------------------------------------------------------------------
-- ID Date Name, Company Issue Change Log or Description
-------------------------------------------------------------------------------------------------------------
-- 001
-------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE k.rbsc$addr_mdf (
i_obj_id number
,i_bu_id number
)
is
l_lock_doc_id varchar2(2000);
l_today constant date := base#.today;
l_doc doc_mgr#.t_doc;
l_doc_addr number;
l_wfc_action_id pls_integer;
begin
session#.open_session(i_bu_id => i_bu_id);
--
-- Check if object is locked by an order
select doc_id
into l_lock_doc_id
from obj
where id = i_obj_id;
--
if l_lock_doc_id is null then
-- Modify object and trim spaces
l_wfc_action_id := base_par#.base_par#id_sql('avq.doc.addr', 'wfc_modify', null, null, null, null);
l_doc := doc_mgr#.stack#next_doc;
doc_addr#.doc#new(l_doc);
doc_addr#.doc#do_wfc_action(l_doc, i_wfc_action_id => l_wfc_action_id, i_is_origin => '+' = '-', i_addr_id => i_obj_id);
l_doc_addr := doc_tab_addr#.doc#addr(l_doc, doc_addr#.c_addr);
doc_tab_addr#.addr#set_firm(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#firm(l_doc_addr)), '\s\s+', ' '), false);
doc_tab_addr#.addr#set_dept(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#dept(l_doc_addr)), '\s\s+', ' '));
doc_tab_addr#.addr#set_first_name(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#first_name(l_doc_addr)), '\s\s+', ' '), false);
doc_tab_addr#.addr#set_middle_name(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#middle_name(l_doc_addr)), '\s\s+', ' '), false);
doc_tab_addr#.addr#set_name(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#name(l_doc_addr)), '\s\s+', ' '), false);
doc_tab_addr#.addr#set_name_add(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#name_add(l_doc_addr)), '\s\s+', ' '));
doc_tab_addr#.addr#set_crof(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#crof(l_doc_addr)), '\s\s+', ' '));
doc_tab_addr#.addr#set_residency(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#residency(l_doc_addr)), '\s\s+', ' '));
doc_tab_addr#.addr#set_attn_of(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#attn_of(l_doc_addr)), '\s\s+', ' '));
doc_tab_addr#.addr#set_street(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#street(l_doc_addr)), '\s\s+', ' '));
doc_tab_addr#.addr#set_street_nr(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#street_nr(l_doc_addr)), '\s\s+', ' '));
doc_tab_addr#.addr#set_street_add(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#street_add(l_doc_addr)), '\s\s+', ' '));
doc_tab_addr#.addr#set_po_box(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#po_box(l_doc_addr)), '\s\s+', ' '));
doc_tab_addr#.addr#set_bldg_name(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#bldg_name(l_doc_addr)), '\s\s+', ' '));
doc_tab_addr#.addr#set_floor_nr(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#floor_nr(l_doc_addr)), '\s\s+', ' '));
doc_tab_addr#.addr#set_unit_nr(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#unit_nr(l_doc_addr)), '\s\s+', ' '));
doc_tab_addr#.addr#set_zip(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#zip(l_doc_addr)), '\s\s+', ' '));
doc_tab_addr#.addr#set_city(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#city(l_doc_addr)), '\s\s+', ' '));
doc_tab_addr#.addr#set_state(l_doc_addr, regexp_replace(trim(doc_tab_addr#.addr#state(l_doc_addr)), '\s\s+', ' '));
if iff(doc_tab_addr#.addr#address_a(l_doc_addr), '+', '-') != '+' then
doc_tab_addr#.addr#set_address(l_doc_addr, regexp_replace(regexp_replace(trim(doc_tab_addr#.addr#address(l_doc_addr)), ' +', ' '),'\s+$', '', 1, 0, 'm'), false);
end if;
l_wfc_action_id := base_par#.base_par#id_sql('avq.doc.addr', 'wfc_verify', null, null, null, null);
doc_addr#.doc#do_wfc_action(l_doc, i_wfc_action_id => l_wfc_action_id, i_is_origin => '-' = '-' , i_addr_id => null);
end if;
commit;
exception
when others then
install#.log#write('Issue with '||i_obj_id||' - Reason ' || 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 rbsc$addr_mdf;
/
declare
c_max_slaves pls_integer := 40;
l_master_id pls_integer;
l_sql_stmt varchar2(32000);
l_num_slaves pls_integer;
begin
session#.open_session;
install#.log#write('Update Address Objects:', 0);
install#.log#write('STARTING MASTER AND '||l_num_slaves||' SLAVES...');
install#.master#start_slave(i_wait=> true, i_cnt => l_num_slaves, x_master_id => l_master_id);
--
for c in (
select oie.bu_id, oie.obj_id
from obj_addr_hist oie
where 1=1
and oie.obj_id in (select id from obj where obj_type_id = 7 and close_date is null and doc_id is null)
and oie.date_valid_to = '31-DEC-4712' ---> sysdate
and oie.bu_id in (6,7,10,12)
and (1=0
or (nvl((oie.firm), 0) != nvl(trim((oie.firm)), 0)) or (nvl((oie.firm), 0) != nvl(replace((oie.firm), ' ', ' '), 0))
or (nvl((oie.dept), 0) != nvl(trim((oie.dept)), 0)) or (nvl((oie.dept), 0) != nvl(replace((oie.dept), ' ', ' '), 0))
or (nvl((oie.first_name), 0) != nvl(trim((oie.first_name)), 0)) or (nvl((oie.first_name), 0) != nvl(replace((oie.first_name), ' ', ' '), 0))
or (nvl((oie.middle_name), 0) != nvl(trim((oie.middle_name)), 0)) or (nvl((oie.middle_name), 0) != nvl(replace((oie.middle_name), ' ', ' '), 0))
or (nvl((oie.name), 0) != nvl(trim((oie.name)), 0)) or (nvl((oie.name), 0) != nvl(replace((oie.name), ' ', ' '), 0))
or (nvl((oie.name_add), 0) != nvl(trim((oie.name_add)), 0)) or (nvl((oie.name_add), 0) != nvl(replace((oie.name_add), ' ', ' '), 0))
or (nvl((oie.crof), 0) != nvl(trim((oie.crof)), 0)) or (nvl((oie.crof), 0) != nvl(replace((oie.crof), ' ', ' '), 0))
or (nvl((oie.residency), 0) != nvl(trim((oie.residency)), 0)) or (nvl((oie.residency), 0) != nvl(replace((oie.residency), ' ', ' '), 0))
or (nvl((oie.attn_of), 0) != nvl(trim((oie.attn_of)), 0)) or (nvl((oie.attn_of), 0) != nvl(replace((oie.attn_of), ' ', ' '), 0))
or (nvl((oie.street), 0) != nvl(trim((oie.street)), 0)) or (nvl((oie.street), 0) != nvl(replace((oie.street), ' ', ' '), 0))
or (nvl((oie.street_nr), 0) != nvl(trim((oie.street_nr)), 0)) or (nvl((oie.street_nr), 0) != nvl(replace((oie.street_nr), ' ', ' '), 0))
or (nvl((oie.street_add), 0) != nvl(trim((oie.street_add)), 0)) or (nvl((oie.street_add), 0) != nvl(replace((oie.street_add), ' ', ' '), 0))
or (nvl((oie.po_box), 0) != nvl(trim((oie.po_box)), 0)) or (nvl((oie.po_box), 0) != nvl(replace((oie.po_box), ' ', ' '), 0))
or (nvl((oie.bldg_name), 0) != nvl(trim((oie.bldg_name)), 0)) or (nvl((oie.bldg_name), 0) != nvl(replace((oie.bldg_name), ' ', ' '), 0))
or (nvl((oie.floor_nr), 0) != nvl(trim((oie.floor_nr)), 0)) or (nvl((oie.floor_nr), 0) != nvl(replace((oie.floor_nr), ' ', ' '), 0))
or (nvl((oie.unit_nr), 0) != nvl(trim((oie.unit_nr)), 0)) or (nvl((oie.unit_nr), 0) != nvl(replace((oie.unit_nr), ' ', ' '), 0))
or (nvl((oie.zip), 0) != nvl(trim((oie.zip)), 0)) or (nvl((oie.zip), 0) != nvl(replace((oie.zip), ' ', ' '), 0))
or (nvl((oie.city), 0) != nvl(trim((oie.city)), 0)) or (nvl((oie.city), 0) != nvl(replace((oie.city), ' ', ' '), 0))
or (nvl((oie.state_alt), 0) != nvl(trim((oie.state_alt)), 0)) or (nvl((oie.state_alt), 0) != nvl(replace((oie.state_alt), ' ', ' '), 0))
or (oie.addr_a is null and ((nvl((oie.addr), 0) != nvl(trim((oie.addr)), 0)) or (nvl((oie.addr), 0) != nvl(replace((oie.addr), ' ', ' '), 0))))
)
) loop
l_sql_stmt := 'begin k.rbsc$addr_mdf('||c.obj_id||', '||c.bu_id||'); end;';
install#.master#send(i_master_id => l_master_id, i_stmt => l_sql_stmt);
end loop;
install#.log#write('ALL JOBS SEND TO MASTER, WAITING FOR FEEDBACK...');
install#.master#feedback_wait(i_master_id => l_master_id, i_all => true);
install#.log#write('ALL JOBS DONE.');
install#.master#stop_slave(i_master_id => l_master_id);
install#.master#reset_master(i_master_id => l_master_id);
exception
when others then
install#.log#write('error: '||sqlerrm);
end;
/
drop procedure rbsc$addr_mdf;
exec session#.open_session();
