Procedure, um Testdate z'erzüge
Das isch en Päckli, wo me cha Testdate drmit erzüge
[script 1.0]
script package rbsc$sda_test
is
/*
-------------------------------------------------------------------------------------------------------------
Bank:
Business Area:
Description: This package is used for testing only! (And should be flagged in ICE as TEST SOURCE)
* The package provides simple procedures to generate test data objects for the various BUs
-------------------------------------------------------------------------------------------------------------
ID Date Name, Company Issue Affected BUs, Description
-------------------------------------------------------------------------------------------------------------
001
-------------------------------------------------------------------------------------------------------------
*/
import native install#; -- For testing purpose only!
import util;
import rbsc$curry;
import rbsc$country;
import obj_bp_kyc_review;
----------------------------------------------------------------------------------
-- Package variables
----------------------------------------------------------------------------------
b_test_date date := session.today;
----------------------------------------------------------------------------------
-- Getter and setter for package variables
function test_date
return date
is
begin
return b_test_date;
end test_date;
-- Set date for current test data generation
procedure set_test_date(
i_test_date date
)
is
begin
require(i_test_date <= session.today, 'Test date cannot be in the future');
b_test_date := i_test_date;
exception
when others then
session.raise_fa_err('set_test_date(' || i_test_date || ')');
end set_test_date;
----------------------------------------------------------------------------------
-- Helper procedure to ensure that task is not run on PROD
procedure chk_prod
is
begin
if session.is_prod_env = '+' or session.global_name like 'PACB%' then
session.raise_ui_err(1, 'This task cannot be run on PROD');
end if;
end chk_prod;
----------------------------------------------------------------------------------
-- Helper procedure to log any data related to the execution
procedure log#write(
i_text text
,i_level number := 1
)
is
begin
require(session.is_prod_env = '-', 'Not allowed to run on PROD');
require(session.global_name not like 'PACB%', 'Not allowed to run on PROD');
require(i_level > 0, 'Valid level');
install#.log#write(i_text, i_level * 2);
end log#write;
----------------------------------------------------------------------------------
-- Helper function to find the best container to book money from/to (=Data transfer?)
function get_cash_cow_cont_id(
i_sbu_id id obj_bp_sbu := null
) return id obj_cont
is
l_cont_id id obj_cont;
begin
case session.bu_id
when 1 then
l_cont_id := lookup.cont_id('38001571.3000',assert_val => '+');
when 2 then
l_cont_id := lookup.cont_id('88001721.3003',assert_val => '+');
when 6 then
l_cont_id := lookup.cont_id('COUZRH.BNPLON.NS',assert_val => '+');
when 7 then
l_cont_id := lookup.cont_id('COUZRH.BNPLON.NS',assert_val => '+');
when 9 then
l_cont_id := lookup.cont_id('COULON.BNPLON.NS',assert_val => '+' ,restr_bu => "+");
when 10 then
case i_sbu_id
when 4061329 then
l_cont_id := lookup.cont_id('DT.MACC.10.NW',assert_val => '+');
when 4061330 then
l_cont_id := lookup.cont_id('DT.MACC.10.NI',assert_val => '+');
when 4061331 then
l_cont_id := lookup.cont_id('DT.MACC.10.RB',assert_val => '+');
end case;
when 11 then
l_cont_id := lookup.cont_id('DT.MACC.33',assert_val => '+');
when 12 then
l_cont_id := lookup.cont_id('COUZRH.NS.ALL',assert_val => '+');
end case;
return l_cont_id;
exception
when others then
session.raise_fa_err('get_cash_cow_cont_id()');
end get_cash_cow_cont_id;
----------------------------------------------------------------------------------
-- Helper function to find the best container to book securities from/to (=Data transfer?)
function get_sec_pos_cow_cont_id(
i_asset_id id obj_asset
,i_trg_cont_id id obj_cont := null
,i_sbu_id id obj_bp_sbu := null
) return id obj_cont
is
l_pos_id id obj_pos;
l_cont_id id obj_cont;
begin
-- If a target container has been specified, then try to create the target position and read the custodian container of this position.
if i_trg_cont_id is not null then
with new mem_lookup_pos as look do
-- new init (force proper initialization of the window/lookup)
look.init;
-- initalize lookup fields
look.cont_id := i_trg_cont_id;
look.asset_id := i_asset_id;
--
look.add_pos;
for sel in 1 .. look.sel_list.count loop
l_pos_id := look.sel_list(sel);
end loop;
end with;
-- Return the custodian container of the new position
if l_pos_id is not null then
return obj_pos(l_pos_id).cust_cont_id;
end if;
end if;
-- Fallback to a "good default custodian" for each BU
case session.bu_id
when 1 then
l_cont_id := lookup.cont_id('COUZRH.UBSZRH.NS',assert_val => '+');
when 2 then
l_cont_id := lookup.cont_id('COUZRH.UBSZRH.NS',assert_val => '+');
when 6 then
l_cont_id := lookup.cont_id('COUZRH.BNPLON.NS',assert_val => '+');
when 7 then
l_cont_id := lookup.cont_id('COUZRH.BNPLON.NS',assert_val => '+');
when 9 then
l_cont_id := lookup.cont_id('COULON.BNPLON.NS',assert_val => '+' ,restr_bu => "+");
when 10 then
case i_sbu_id
when 4061329 then
l_cont_id := lookup.cont_id('COUZRH.SISZRH.NS.UK.NW',assert_val => '+');
when 4061330 then
l_cont_id := lookup.cont_id('COUZRH.SISZRH.NS.UK.NWI',assert_val => '+');
when 4061331 then
l_cont_id := lookup.cont_id('COUZRH.SISZRH.NS.UK.RBS',assert_val => '+');
end case;
when 11 then
l_cont_id := lookup.cont_id('DT.SEC.33',assert_val => '+');
when 12 then
l_cont_id := lookup.cont_id('COUZRH.SISZRH.NS',assert_val => '+');
end case;
return l_cont_id;
exception
when others then
session.raise_fa_err('get_sec_pos_cow_cont_id(' || i_asset_id || ')');
end get_sec_pos_cow_cont_id;
----------------------------------------------------------------------------------
-- Helper function to find the best container to book securities from/to (=Data transfer?)
function get_macc_asset_id(
i_cont_type_id number := null
,i_curry text := null
) return id obj_asset
is
l_asset_id id obj_asset;
begin
case session.bu_id
when 1 then
l_asset_id := lookup.asset_id('SG.CR.' || nvl(i_curry, 'CHF'),assert_val => '+');
when 2 then
l_asset_id := lookup.asset_id('HK.CR.' || nvl(i_curry, 'CHF'),assert_val => '+');
when 6 then
l_asset_id := lookup.asset_id('CUK_NIB_PCA_' || nvl(i_curry, 'GBP'));
if l_asset_id is null then
l_asset_id := lookup.asset_id('CUK_PCA_' || nvl(i_curry, 'GBP'),assert_val => '+');
end if;
when 7 then
l_asset_id := lookup.asset_id('ADA_NIB_PCA_' || nvl(i_curry, 'GBP'));
if l_asset_id is null then
l_asset_id := lookup.asset_id('ADA_PCA_' || nvl(i_curry, 'GBP'),assert_val => '+');
end if;
when 9 then
l_asset_id := lookup.asset_id('CH.CA.' || nvl(i_curry, 'CHF'),assert_val => '+');
when 10 then
if i_cont_type_id = code_obj_classif_cont_cont_type.isa_cash then
l_asset_id := lookup.asset_id('PBA_CASH_ISA_' || nvl(i_curry, 'GBP'));
elsif i_cont_type_id = code_obj_classif_cont_cont_type.cont_investment then
l_asset_id := lookup.asset_id('PBA_IMS_CAP_' || nvl(i_curry, 'GBP'));
elsif i_cont_type_id = code_obj_classif_cont_cont_type.cont_trust_estate then
l_asset_id := lookup.asset_id('PBA_TTE_CAP_' || nvl(i_curry, 'GBP'));
end if;
if l_asset_id is null then
l_asset_id := lookup.asset_id('PBA_NIB_CAP_' || nvl(i_curry, 'GBP'),assert_val => '+');
end if;
when 11 then
l_asset_id := lookup.asset_id('MON.CA.' || nvl(i_curry, 'EUR'),assert_val => '+');
when 12 then
l_asset_id := lookup.asset_id('JER_CA_' || nvl(i_curry, 'GBP'));
if l_asset_id is null then
l_asset_id := lookup.asset_id('JER_NIB_PCA_' || nvl(i_curry, 'GBP'),assert_val => '+');
end if;
end case;
return l_asset_id;
exception
when others then
session.raise_fa_err('get_macc_asset_id');
end get_macc_asset_id;
----------------------------------------------------------------------------------
-- Helper function to find the default container type for banking products
function get_bank_cont_type
return number
is
begin
if session.bu_id in (1, 2, 4, 9, 11) then
return code_obj_classif_cont_cont_type.portf_clt_id;
elsif session.bu_id in (10) then
return code_obj_classif_cont_cont_type.isa_cash;
else
return code_obj_classif_cont_cont_type.cont_banking;
end if;
exception
when others then
session.raise_fa_err('get_bank_cont_type');
end get_bank_cont_type;
----------------------------------------------------------------------------------
-- Helper function to find the default container type for investment products
function get_invst_cont_type
return number
is
begin
if session.bu_id in (1, 2, 4, 9, 11) then
return code_obj_classif_cont_cont_type.portf_clt_id;
else
return code_obj_classif_cont_cont_type.cont_investment;
end if;
exception
when others then
session.raise_fa_err('get_invst_cont_type');
end get_invst_cont_type;
----------------------------------------------------------------------------------
-- Helper function to find the first container of a BP with a certain type
function bp#first_cont_of_type(
i_bp_id id obj_bp
,i_cont_type_id number
) return id obj_cont
is
begin
-- Inhibit running on PROD
chk_prod;
with obj_bp(i_bp_id) as bp do
for c in bp.cont_list(eff_date => session.today ,incl_vostri => "+") loop
if c.extn.cont_type_id = i_cont_type_id then
return c;
end if;
end loop;
end with;
return null;
exception
when others then
session.raise_fa_err('bp#first_cont_of_type(' || i_cont_type_id || ')');
end bp#first_cont_of_type;
----------------------------------------------------------------------------------
-- Create an address object
function addr#create(
i_first_name text := null
,i_middle_name text := null
,i_last_name text := null
,i_firm text := null
,i_street text := null
,i_street_nr text := null
,i_zip text := null
,i_city text := null
,i_country_id id obj_country
,i_open_date date := null
,i_salut_id id table code_salut := null
,i_salut_letter_id id table code_salut_letter := null
) return number
is
l_obj_id id obj_bp;
l_open_date date := nvl(i_open_date, test_date);
begin
-- Inhibit running on PROD
chk_prod;
with new mem_doc_addr(avq$base_par_doc_addr.wfc_new) as d do
d.addr_type_id := code_addr_type.postal;
d.lang_id := code_lang.e;
d.salut_id := i_salut_id;
d.salut_letter_id := i_salut_letter_id;
d.first_name := i_first_name;
d.middle_name := i_middle_name;
d.name := i_last_name;
d.firm := i_firm;
d.street := i_street;
d.street_nr := i_street_nr;
d.zip := i_zip;
d.city := i_city;
d.country_id := i_country_id;
if l_open_date is not null then
d.open_date := l_open_date;
end if;
d.do_wfc_action(avq$base_par_doc_addr.wfc_verify);
l_obj_id := d.kernel_addr_id;
log#write('addr#create: ' || d.doc_id || ' => ' || l_obj_id, 4);
end with;
return l_obj_id;
exception
when others then
session.raise_fa_err('addr#create()');
end addr#create;
----------------------------------------------------------------------------------
-- Create an empty DOCM object (which can be linked to a BP or person)
function docm#create(
i_name text
) return id obj_docm
is
l_obj_id id obj_docm;
begin
-- Inhibit running on PROD
chk_prod;
with new mem_doc_docm(avq$base_par_doc_docm.wfc_new) as d do
d.name := i_name;
d.do_wfc_action(avq$base_par_doc_docm.wfc_verify,no_commit => '+'); -- don't commit yet
l_obj_id := d.kernel_docm_id;
log#write('docm#create: ' || d.doc_id || ' => ' || l_obj_id, 4);
end with;
return l_obj_id;
exception
when others then
session.raise_fa_err('docm#create()');
end docm#create;
----------------------------------------------------------------------------------
-- Update ID Question DOCM object
procedure docm#upd_idq(
i_kyc_docm_id id obj_docm
,i_person_id id obj_person
)
is
begin
-- Inhibit running on PROD
chk_prod;
with obj_person(i_person_id) as person do
with new mem_doc_docm(avq$base_par_doc_docm.wfc_modify, i_kyc_docm_id) as d do
d.name := 'ID Questionnaire - ' || person.person_name_dflt.full_name;
d.docm_type_id := code_obj_sub_type.docm_std_id;
d.obj_extn.docm_type_id := code_obj_classif_docm_docm_type.id_question_id;
d.open_date := person.open_date;
d.form_id := form_docm#id_questions.id;
d.obj_extn.docm_nr := person.extn.person_nr;
-- Add some questions
with new d.obj_extn.docm_id_questions.id_question_list as idq do
idq.code_question_id := rbsc$code_id_question.rbsc$first_car;
--idq.free_text_question := question.free_text_question;
idq.answer := "Audi A3";
end with;
with new d.obj_extn.docm_id_questions.id_question_list as idq do
idq.code_question_id := rbsc$code_id_question.rbsc$clt_pwd;
idq.answer := "Secret";
end with;
d.do_wfc_action(avq$base_par_doc_docm.wfc_verify);
log#write('docm#upd_idq: ' || d.doc_id, 4);
end with;
end with;
exception
when others then
session.raise_fa_err('docm#upd_idq()');
end docm#upd_idq;
----------------------------------------------------------------------------------
-- Create an KYC DOCM object
procedure docm#upd_kyc(
i_kyc_docm_id id obj_docm
,i_person_id id obj_person
)
is
begin
-- Inhibit running on PROD
chk_prod;
with obj_person(i_person_id) as person do
with new mem_doc_docm(avq$base_par_doc_docm.wfc_modify, i_kyc_docm_id) as kyc_docm do
obj_bp_kyc_review.create_kyc_docm(kyc_docm, person.person_det.person_type_id, person.name);
kyc_docm.form_id := obj_bp_kyc_review.kyc_form_id(obj_bp_kyc_review.get_kyc_type_id(person.person_det.person_type_id), person.person_det.person_type_id);
if kyc_docm.obj_extn.kyc_questions.kyc_q_01 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_01 := 'd';
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_02 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_02 := 'd';
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_03 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_03 := 'd';
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_04 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_04 := 'd';
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_05 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_05 := 'd';
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_06 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_06 := 'n';
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_07 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_07 := 'n';
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_08 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_08 := 'n';
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_09 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_09 := 'n';
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_10 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_10 := 'n';
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_11 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_11 := 'n';
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_12 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_12 := 'd';
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_13 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_13 := 's';
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_14 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_14 := null; -- normally empty
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_15 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_15 := 's';
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_16 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_16 := 's';
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_17 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_17 := null; -- normally empty
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_18 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_18 := null; -- normally empty
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_19 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_19 := null; -- normally empty
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_20 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_20 := null; -- normally empty
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_q_21 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_a_21 := null; -- normally empty
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_eq_01 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_ea_01 := null; -- normally empty
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_eq_02 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_ea_02 := null; -- normally empty
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_eq_03 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_ea_03 := null; -- normally empty
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_eq_04 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_ea_04 := null; -- normally empty
end if;
if kyc_docm.obj_extn.kyc_questions.kyc_eq_05 is not null then
kyc_docm.obj_extn.kyc_questions.kyc_ea_05 := null; -- normally empty
end if;
kyc_docm.obj_extn.kyc_questions.kyc_intro_country_id := null;
kyc_docm.obj_extn.kyc_questions.kyc_delivery_channel_id := 5234;
kyc_docm.obj_extn.kyc_questions.kyc_ind_involvement_id := 5235;
kyc_docm.obj_extn.kyc_questions.kyc_rep_ind_inv_id := 5257;
kyc_docm.obj_extn.kyc_questions.kyc_source_funds_id := 5273;
kyc_docm.obj_extn.kyc_questions.kyc_source_wealth_id := 5276;
kyc_docm.obj_extn.kyc_questions.kyc_source_income_id := 5278;
kyc_docm.obj_extn.kyc_questions.kyc_source_global := '+';
kyc_docm.obj_extn.kyc_questions.kyc_trx_proj_cash_id := 5283;
kyc_docm.obj_extn.kyc_questions.kyc_trx_proj_tpp_id := 5286;
kyc_docm.obj_extn.kyc_questions.kyc_nat_leg_id := 5289;
kyc_docm.do_wfc_action(avq$base_par_doc_docm.wfc_verify,no_commit => '+'); -- don't commit yet
log#write('docm#upd_kyc: ' || kyc_docm.doc_id, 4);
end with;
end with;
exception
when others then
session.raise_fa_err('docm#upd_kyc(' || i_person_id || ')');
end docm#upd_kyc;
----------------------------------------------------------------------------------
-- Create a person object
function person#create(
i_first_name text := null
,i_middle_name text := null
,i_last_name text := null
,i_birth_date date := null
,i_establish_date date := null
,i_gender_id id table code_gender := null
,i_marital_status_id id table code_marital_status := null
,i_country_nati_id id obj_country := null
,i_domi_addr_id id obj_addr := null
,i_sym_key text := null
,i_migr_key text := null
,i_cin_key text := null
,i_open_date date := null
,i_add_obj_id_to_name boolean := false
) return number
is
l_obj_id id obj_bp;
l_kyc_docm_id id obj_docm;
l_idq_docm_id id obj_docm;
l_open_date date := nvl(i_open_date, test_date);
begin
-- Inhibit running on PROD
chk_prod;
with new mem_doc_person(avq$base_par_doc_person.wfc_new) as d do
if l_open_date is not null then
d.open_date := l_open_date;
end if;
d.person_det.person_type_id := code_obj_sub_type.person_natural;
d.person_det.country_nati_id := rbsc$country.c_gb_id;
d.obj_extn.person_legal_form_id := code_obj_classif_person_person_legal_form.priv;
d.obj_extn.person_form_id := code_obj_classif_person_person_form.pers_form_doc;
if i_sym_key is not null then
d.obj_extn.person_sym := i_sym_key;
end if;
if i_migr_key is not null then
d.obj_extn.rbsc$person_migrt := i_migr_key;
end if;
if i_cin_key is not null then
d.obj_extn.rbsc$person_dsn_old := i_cin_key;
end if;
with d.person_name_dflt as pn do
pn.first_name := i_first_name;
pn.middle_name := i_middle_name;
pn.last_name := i_last_name || case
when i_add_obj_id_to_name then
d.kernel_person_id
end;
end with;
if i_birth_date is not null then
d.birth_date := i_birth_date;
end if;
if i_establish_date is not null then
d.establish_date := i_establish_date;
end if;
if i_gender_id is not null then
d.gender_id := i_gender_id;
end if;
if i_marital_status_id is not null then
d.person_det.marital_status_id := i_marital_status_id;
end if;
if i_country_nati_id is not null then
d.person_det.country_nati_id := i_country_nati_id;
end if;
if i_domi_addr_id is not null then
d.domi_addr_id := i_domi_addr_id;
end if;
d.obj_extn.pers_fatca_status_id := code_obj_classif_person_pers_fatca_status.rbsc$fatca_non_us_person;
-- As of CRS Start date the Person must be flagged accordingly
if rbsc$base_par_crs_legislation.crs_start_date(session.bu_id) is not null then
d.obj_extn.rbsc$crs_type_id := code_obj_classif_person_rbsc$crs_type.rbsc$crs_np_doc;
end if;
-- Create an empty KYC document and add it to the person
l_kyc_docm_id := docm#create(i_name => 'Empty KYC - ' || d.person_name_dflt.full_name);
with new d.rel_list(
obj_rel_type_id => code_obj_rel_type.docm
,obj_rel_sub_type_id => code_obj_rel_sub_type.kyc
,obj_2_id => l_kyc_docm_id
) as rel do
rel.sign_date := l_open_date;
end with;
-- Create an ID Question document and add it to the person
if session.bu_id in (6, 12) then
l_idq_docm_id := docm#create(
i_name => 'Empty ID Questionnaire - ' || d.person_name_dflt.full_name
);
d.rel_list.add(
obj_rel_type_id => code_obj_rel_type.docm
,obj_rel_sub_type_id => code_obj_rel_sub_type.id_questions
,obj_2_id => l_idq_docm_id
);
end if;
-- Verify the person
d.do_wfc_action(avq$base_par_doc_person.wfc_verify);
l_obj_id := d.kernel_person_id;
log#write('person#create: ' || d.doc_id || ' => ' || l_obj_id, 4);
end with;
-- Update the KYC document with the relevant data
docm#upd_kyc(l_kyc_docm_id, l_obj_id);
-- Update the IDQ document with relevant data
if l_idq_docm_id is not null then
docm#upd_idq(l_idq_docm_id, l_obj_id);
end if;
return l_obj_id;
exception
when others then
session.raise_fa_err('person#create()');
end person#create;
----------------------------------------------------------------------------------
-- Create a bp object
function bp#create(
i_ao_person_id id obj_person
,i_ref_curry_id id obj_curry := null
,i_sym_key text := null
,i_migr_key text := null
,i_open_date date := null
) return number
is
l_obj_id id obj_bp;
l_ref_curry_id id obj_curry := nvl(i_ref_curry_id, rbsc$curry.c_gbp_id);
l_open_date date := nvl(i_open_date, test_date);
begin
-- Inhibit running on PROD
chk_prod;
-- Then create a BP with the person attached as AO
with new mem_doc_bp(avq$base_par_doc_bp.wfc_new) as d do
if l_open_date is not null then
d.open_date := l_open_date;
end if;
d.lang_id := code_lang.e;
d.ref_curry_id := l_ref_curry_id;
d.obj_extn.bp_custr_type_id := code_obj_classif_bp_bp_custr_type.custr;
d.obj_extn.bp_mifid_cat_id := code_obj_classif_bp_bp_mifid_cat.mifid_retail;
if i_sym_key is not null then
d.obj_extn.bp_sym := i_sym_key;
end if;
if i_migr_key is not null then
d.obj_extn.rbsc$bp_migrt := i_migr_key;
end if;
with new d.bp_person_rel_list as r do
r.auth_role_id := code_auth_role.acc_owner;
r.trg_obj_id := i_ao_person_id;
r.start_date := d.open_date;
end with;
-- For CH we have to setup FWT BO
if session.bu_id = 9 then
with new d.bp_person_rel_list as r do
r.auth_role_id := code_auth_role.ch_fwt_bo;
r.trg_obj_id := i_ao_person_id;
end with;
end if;
-- Add a private banker relation
with new d.rel_list(obj_rel_type_id => code_obj_rel_type.role) as r do
r.rel_sub_type_id := code_obj_rel_sub_type.rm;
r.obj_2_id := 7930429;
end with;
d.do_wfc_action(avq$base_par_doc_bp.wfc_verify);
l_obj_id := d.kernel_bp_id;
log#write('bp#create: ' || d.doc_id || ' => ' || l_obj_id || ' ' || i_migr_key, 4);
end with;
return l_obj_id;
exception
when others then
session.raise_fa_err('bp#create(' || i_ao_person_id || ',' || i_open_date || ')');
end bp#create;
----------------------------------------------------------------------------------
-- Create a container object
function cont#create(
i_bp_id id obj_bp
,i_sbu_id id obj_bp_sbu := null
,i_sym_key text := null
,i_migr_key text := null
,i_open_date date := null
,i_cont_type_id number := null
,i_price_type_id number := null
,i_mand_code_id number := null
) return number
is
l_obj_id id obj_bp;
l_test_date date := nvl(i_open_date, test_date);
l_cont_type number;
l_price_type number;
begin
-- Inhibit running on PROD
chk_prod;
-- Create cont
with new mem_doc_cont(avq$base_par_doc_cont.wfc_new) as d do
if l_test_date is not null then
d.eff_date := l_test_date;
d.open_date := l_test_date;
end if;
d.bp_id := i_bp_id;
d.ref_curry_id := rbsc$curry.c_gbp_id;
d.calc_perf := "+";
if i_sym_key is not null then
d.obj_extn.cont_sym := i_sym_key;
end if;
if i_migr_key is not null then
d.obj_extn.rbsc$cont_migrt := i_migr_key;
end if;
l_cont_type := coalesce(i_cont_type_id, case
when session.bu_id in (1, 2, 9, 11) then
code_obj_classif_cont_cont_type.portf_clt_id
when session.bu_id in (10) then
code_obj_classif_cont_cont_type.isa_cash
else
code_obj_classif_cont_cont_type.cont_banking
end);
l_price_type := coalesce(i_price_type_id, case session.bu_id
when 1 then
code_obj_classif_cont_cont_pricing_type.sgp_prt_ticket_size
when 2 then
code_obj_classif_cont_cont_pricing_type.hkg_prt_exec
when 9 then
code_obj_classif_cont_cont_pricing_type.ch_prt_execution
when 11 then
code_obj_classif_cont_cont_pricing_type.ch_prt_execution
when 6 then
case
when l_cont_type = code_obj_classif_cont_cont_type.cont_banking then
code_obj_classif_cont_cont_pricing_type.cont_prt_bank_priv
else
code_obj_classif_cont_cont_pricing_type.cuk_prt_standalone
end
when 7 then
case
when l_cont_type = code_obj_classif_cont_cont_type.cont_banking then
code_obj_classif_cont_cont_pricing_type.ada_prt_banking
else
code_obj_classif_cont_cont_pricing_type.ada_prt_adamco_safek
end
when 10 then
case
when l_cont_type = code_obj_classif_cont_cont_type.isa_cash then
code_obj_classif_cont_cont_pricing_type.cuk_prt_isa_cash
else
code_obj_classif_cont_cont_pricing_type.cuk_prt_ims
end
when 12 then
case
when l_cont_type = code_obj_classif_cont_cont_type.cont_banking then
code_obj_classif_cont_cont_pricing_type.rbsc$jer_prt_bnk
else
code_obj_classif_cont_cont_pricing_type.rbsc$jer_of_execution
end
else
code_obj_classif_cont_cont_pricing_type.cuk_prt_exempt_id
end);
d.obj_extn.cont_type_id := l_cont_type;
d.obj_extn.cont_pricing_type_id := l_price_type;
if l_cont_type = get_invst_cont_type then
d.obj_extn.cont_inv_prof_id := coalesce(i_mand_code_id, case
when session.bu_id in (1, 2, 9, 11) then
3026 -- SPEZIAL (29)
when session.bu_id in (7, 8) then
code_obj_classif_cont_cont_inv_prof.mc_exec_serv -- Execution Service (38)
else
code_obj_classif_cont_cont_inv_prof.mc_ucits_n -- Execution Only (42)
end);
end if;
if i_sbu_id is not null then
d.sbu_id := i_sbu_id;
end if;
d.do_wfc_action(avq$base_par_doc_cont.wfc_verify);
l_obj_id := d.kernel_cont_id;
log#write('cont#create: ' || d.doc_id || ' => ' || l_obj_id || ' ' || i_migr_key, 4);
end with;
return l_obj_id;
exception
when others then
session.raise_fa_err('cont#create()');
end cont#create;
----------------------------------------------------------------------------------
-- Create a macc object
function macc#create(
i_cont_id id obj_cont
,i_contr_id id obj_asset
,i_sym_key text := null
,i_migr_key text := null
,i_open_date date := null
) return number
is
l_obj_id id obj_bp;
l_open_date date := nvl(i_open_date, test_date);
begin
-- Inhibit running on PROD
chk_prod;
with new mem_doc_macc(avq$base_par_doc_macc.wfc_new) as d do
if l_open_date is not null then
d.eff_date := l_open_date;
d.open_date := l_open_date;
end if;
d.cont_id := i_cont_id;
d.contr_id := i_contr_id;
if i_sym_key is not null then
d.obj_extn.pos_sym := i_sym_key;
end if;
if i_migr_key is not null then
d.obj_extn.rbsc$pos_migrt := i_migr_key;
end if;
d.do_wfc_action(avq$base_par_doc_macc.wfc_verify);
l_obj_id := d.kernel_macc_id;
log#write('macc#create: ' || d.doc_id || ' => ' || l_obj_id || ' ' || i_migr_key, 4);
end with;
return l_obj_id;
exception
when others then
session.raise_fa_err('macc#create()');
end macc#create;
----------------------------------------------------------------------------------
-- Generate some money into a macc
function xfermon#gen_money(
i_dest_macc_id id obj_macc
,i_qty number
,i_curry_id id obj_curry := null
,i_test_date date := null
,i_val_date date := null
,i_trx_date date := null
,i_perf_date date := null
,i_veri_date date := null
) return number
is
l_doc_id id doc_xfermon;
l_val_date date := coalesce(i_val_date, i_test_date, test_date);
l_trx_date date := coalesce(i_trx_date, i_test_date, test_date);
l_perf_date date := coalesce(i_perf_date, i_test_date, test_date);
l_veri_date date := coalesce(i_veri_date, i_test_date, test_date);
begin
-- Inhibit running on PROD
chk_prod;
with new mem_doc_xfermon('dom_bank') as d do
d.val_date := l_val_date;
d.trx_date := l_trx_date;
d.perf_date := l_perf_date;
d.done_date := l_veri_date;
d.cred_macc_id := i_dest_macc_id;
d.deb_cont_id := get_cash_cow_cont_id(i_sbu_id => d.cred_cont.sbu_id);
d.curry_id := coalesce(i_curry_id, d.cred_macc.ref_curry_id);
d.amount := i_qty;
d.do_wfc_action('rbsc_marg_adj');
l_doc_id := d.doc_id;
log#write('xfermon#gen_money: ' || l_doc_id, 4);
end with;
return l_doc_id;
exception
when others then
session.raise_fa_err('xfermon#gen_money()');
end xfermon#gen_money;
----------------------------------------------------------------------------------
-- Generate a security position
function xfer#gen_secpos(
i_asset_id id obj_asset
,i_qty number
,i_dest_cont_id id obj_cont
,i_src_cont_id id obj_cont := null
,i_test_date date := null
,i_val_date date := null
,i_trx_date date := null
,i_perf_date date := null
,i_veri_date date := null
) return number
is
l_doc_id id doc_xfer;
l_src_cont_id id obj_cont;
l_val_date date := coalesce(i_val_date, i_test_date, test_date);
l_trx_date date := coalesce(i_trx_date, i_test_date, test_date);
l_perf_date date := coalesce(i_perf_date, i_test_date, test_date);
l_veri_date date := coalesce(i_veri_date, i_test_date, test_date);
begin
-- Inhibit running on PROD
chk_prod;
require(i_asset_id is not null, 'Valid asset');
require(i_qty > 0, 'Positive quantity');
require(i_dest_cont_id is not null, 'Valid target container');
l_src_cont_id := nvl(
i_src_cont_id,
get_sec_pos_cow_cont_id(i_asset_id => i_asset_id, i_trg_cont_id => i_dest_cont_id, i_sbu_id => obj_cont(i_dest_cont_id).sbu_id)
);
-- Create a new transfer order
with new mem_doc_xfer("migr_open") as xfer do
xfer.cred_cont_id := i_dest_cont_id;
xfer.deb_cont_id := l_src_cont_id;
xfer.book_kind_id := code_book_kind.dlv_sec_id;
xfer.val_date := l_val_date;
xfer.trx_date := l_trx_date;
xfer.perf_date := l_perf_date;
xfer.veri_date := l_veri_date;
with new xfer.book_list as book_list do
-- Create a new position within target container:
with new mem_lookup_pos as look do
-- new init (force proper initialization of the window/lookup)
look.init;
-- initalize lookup fields
look.cont_id := xfer.cred_cont_id;
look.asset_id := i_asset_id;
look.add_pos;
for sel in 1 .. look.sel_list.count loop
book_list.cred_pos_id := look.sel_list(sel);
end loop;
end with;
-- Find a position in debit container
with new mem_lookup_pos as look do
-- new init (force proper initialization of the window/lookup)
look.init;
-- initalize lookup fields
look.cont_id := xfer.deb_cont_id;
look.asset_id := i_asset_id;
look.add_pos;
for sel in 1 .. look.sel_list.count loop
book_list.deb_pos_id := look.sel_list(sel);
end loop;
end with;
book_list.qty := i_qty;
end with;
xfer.do_wfc_action("migr_verify");
l_doc_id := xfer.doc_id;
log#write('xfer#gen_secpos: ' || l_doc_id, 4);
end with;
return l_doc_id;
exception
when others then
session.raise_fa_err('xfer#gen_secpos()');
end xfer#gen_secpos;
----------------------------------------------------------------------------------
-- Create a limit
function limit#create(
i_bp_id id obj_bp
,i_limit_asset_id id obj_asset
,i_amount number
) return number
is
l_doc_id id doc_limit;
begin
-- Inhibit running on PROD
chk_prod;
-- Grant a limit
with new mem_doc_limit(1) as doc do
doc.form_id := form_limit#rbsc$uk.id;
doc.bp_id := i_bp_id;
doc.asset_id := i_limit_asset_id;
doc.amount := i_amount;
doc.close_date := test_date + 365; -- Expiration date
doc.val_date := test_date;
doc.trx_date := test_date;
doc.perf_date := test_date;
doc.done_date := test_date;
doc.asset_extn.agreem_date := test_date;
doc.asset_extn.ass_regdet_secur_id := code_obj_classif_asset_ass_regdet_secur.sec_reg_unsec;
doc.extn.rbsc$grant_type_id := 5435; -- rbsc$new_money
doc.asset_extn.ass_limit_sanction_id := code_obj_classif_asset_ass_limit_sanction.ass_limit_sanction_mu;
doc.do_wfc_action(13 /*verify*/);
l_doc_id := doc.doc_id;
log#write('limit#create: ' || l_doc_id, 4);
end with;
return l_doc_id;
exception
when others then
session.raise_fa_err('limit#create()');
end limit#create;
----------------------------------------------------------------------------------
-- Generate multiple security position based on a list of assets and quantities
-- @param i_sec_asset_list textual representation of the assets and quantities separated by ":" and ","
-- for example "#14902989:10000;IE00B0V9T425:3850;XS0142963445:45000" will generate 3 positions
-- @param i_dest_cont_id target container
procedure gen_secpos(
i_sec_asset_list long
,i_dest_cont_id id obj_cont
,i_test_date date := null
,i_val_date date := null
,i_trx_date date := null
,i_perf_date date := null
,i_veri_date date := null
)
is
l_curr_asset text;
l_asset_id id obj_asset;
l_key text;
l_qty number;
l_doc_id number;
l_pos_id number;
begin
log#write('gen_secpos: i_sec_asset_list ' || i_sec_asset_list, 2);
for i in 1 .. util.item_count(i_sec_asset_list) loop
l_curr_asset := util.item(i_sec_asset_list, i);
if trim(l_curr_asset) is not null then
l_key := trim(util.item(l_curr_asset, 1, ":"));
l_qty := trim(util.item(l_curr_asset, 2, ":"));
l_asset_id := coalesce(substr(regexp_substr(l_key, "^#([0-9]+)"), 2), lookup.asset_id(l_key,eff_date => session.today));
if l_asset_id is not null then
with obj_asset(l_asset_id) as asset do
if asset.extn.ass_type_id = code_obj_classif_asset_ass_type.macc then
l_pos_id := macc#create(
i_cont_id => i_dest_cont_id
,i_contr_id => l_asset_id
,i_open_date => i_test_date
);
if l_qty is not null then
l_doc_id := xfermon#gen_money(
i_dest_macc_id => l_pos_id
,i_qty => l_qty
,i_curry_id => asset.nom_curry_id
,i_test_date => i_test_date
,i_val_date => i_val_date
,i_trx_date => i_trx_date
,i_perf_date => i_perf_date
,i_veri_date => i_veri_date
);
end if;
elsif asset.extn.ass_type_id = code_obj_classif_asset_ass_type.limt then
l_doc_id := limit#create(obj_cont(i_dest_cont_id).bp_id, l_asset_id, l_qty);
else
l_doc_id := xfer#gen_secpos(
i_asset_id => l_asset_id
,i_qty => l_qty
,i_dest_cont_id => i_dest_cont_id
,i_test_date => i_test_date
,i_val_date => i_val_date
,i_trx_date => i_trx_date
,i_perf_date => i_perf_date
,i_veri_date => i_veri_date
);
end if;
end with;
else
log#write('gen_secpos: Unable to transform ' || l_key, 4);
end if;
end if;
end loop;
exception
when others then
session.raise_fa_err('gen_secpos(' || i_sec_asset_list || ',' || i_dest_cont_id || ')');
end gen_secpos;
----------------------------------------------------------------------------------
-- Create a new client in the current BU
procedure create_clt(
i_bp_id id obj_bp := null
,i_cont_id id obj_cont := null
,i_sym_key text := null
,i_migr_key text := null
,i_sbu_id id obj_bp_sbu := null
,i_test_date date := null
,i_first_name text := null
,i_last_name text := null
,i_macc_pos_list long := null
,i_sec_pos_list long := null
)
is
l_bu_id number;
l_addr_id id obj_addr;
l_person_id id obj_person;
l_bp_id id obj_bp;
l_cont_id id obj_cont;
l_sbu_id id obj_bp_sbu;
l_macc_id id obj_macc;
l_first_name text;
l_last_name text;
l_doc_id id doc;
l_sec_pos_list long := coalesce(i_sec_pos_list, "#14902989:10000;IE00B0V9T425:3850;XS0142963445:45000;IE00B3XXRP09:1420;LU0174119775:1320;DE000BAY0017:100;US4581401001:100;CH0038863350:100;CH0017410314:50000;CH0024899483:100");
l_cont_type_id number;
begin
-- Inhibit running on PROD
chk_prod;
log#write('create_clt: i_sym_key ' || i_sym_key || ', i_migr_key ' || i_migr_key || ' => ' || i_first_name || ' ' || i_last_name, 2);
session.reset;
-- Preconditions
require(i_test_date <= session.today, 'Test date must not be in the future.');
-- Test data
l_bu_id := session.bu_id;
l_sbu_id := i_sbu_id;
if l_bu_id = 7 and i_sbu_id is null then
l_sbu_id := nvl(i_sbu_id, lookup.bp_id('RBS.ADA.LO',assert_val => '+'));
elsif l_bu_id = 9 and i_sbu_id is null then
l_sbu_id := nvl(i_sbu_id, lookup.bp_id('ZH.11',assert_val => '+'));
elsif l_bu_id = 10 and i_sbu_id is null then
l_sbu_id := nvl(i_sbu_id, lookup.bp_id('RBS.PBA.RBS',assert_val => '+'));
end if;
set_test_date(coalesce(i_test_date, lookup.date('som')));
--
l_first_name := coalesce(i_first_name, 'John');
l_last_name := coalesce(i_last_name, 'Doe');
-- Check for existing BP object
l_bp_id := i_bp_id;
if l_bp_id is null and i_sym_key is not null then
l_bp_id := lookup.bp(i_sym_key, 'bp_sym');
end if;
if l_bp_id is null and i_migr_key is not null then
l_bp_id := lookup.bp(i_migr_key, 'rbsc$bp_migrt');
end if;
-- If there is no BP yet, then create a complete client: ADDR, AO PERSON and BP
if l_bp_id is null and i_cont_id is null then
-- Create the domicile address
l_addr_id := addr#create(
i_first_name => l_first_name
,i_last_name => l_last_name
,i_street => 'Avon Street'
,i_zip => 'BS2 0PT'
,i_city => 'Bristol'
,i_country_id => rbsc$country.c_gb_id
);
-- First create the person object
l_person_id := person#create(
i_first_name => l_first_name
,i_last_name => l_last_name
,i_birth_date => lookup.date("-50y")
,i_domi_addr_id => l_addr_id
,i_add_obj_id_to_name => i_first_name || i_last_name is null -- Add OBJ ID if the name is not specified by caller
);
-- Then create a BP with the person attached as AO
l_bp_id := bp#create(
i_ao_person_id => l_person_id
,i_migr_key => i_migr_key
,i_sym_key => i_sym_key
);
elsif i_bp_id is not null then
-- Use the provided BP
l_bp_id := i_bp_id;
elsif i_cont_id is not null then
-- Use the provided CONT's BP
l_bp_id := obj_cont(i_cont_id).bp_id;
end if;
----------------------------------------------------------------------
-- Cash positions/banking portfolio
----------------------------------------------------------------------
l_cont_type_id := get_bank_cont_type;
l_cont_id := coalesce(i_cont_id, bp#first_cont_of_type(l_bp_id, l_cont_type_id));
if l_cont_id is null then
-- Then create a container for banking
l_cont_id := cont#create(
i_bp_id => l_bp_id
,i_sym_key => case
when i_sym_key is not null then
i_sym_key || '.01'
end
,i_sbu_id => l_sbu_id
,i_cont_type_id => l_cont_type_id
);
-- Create a MACC and transfer money
l_macc_id := macc#create(
i_cont_id => l_cont_id
,i_contr_id => get_macc_asset_id(obj_cont(l_cont_id).extn.cont_type_id, 'GBP')
,i_sym_key => case
when i_sym_key is not null then
i_sym_key || '.01.01'
end
);
l_doc_id := xfermon#gen_money(
i_dest_macc_id => l_macc_id
,i_qty => 150000
);
-- No non-GBP accounts for PB&A
if session.bu_id not in (10) then
-- Create a MACC and transfer money
l_macc_id := macc#create(
i_cont_id => l_cont_id
,i_contr_id => get_macc_asset_id(obj_cont(l_cont_id).extn.cont_type_id, 'EUR')
,i_sym_key => case
when i_sym_key is not null then
i_sym_key || '.01.02'
end
);
l_doc_id := xfermon#gen_money(
i_dest_macc_id => l_macc_id
,i_qty => 24860.40
);
-- Create a MACC and transfer money
l_macc_id := macc#create(
i_cont_id => l_cont_id
,i_contr_id => get_macc_asset_id(obj_cont(l_cont_id).extn.cont_type_id, 'CHF')
,i_sym_key => case
when i_sym_key is not null then
i_sym_key || '.01.03'
end
);
l_doc_id := xfermon#gen_money(
i_dest_macc_id => l_macc_id
,i_qty => 47332.24
);
end if;
end if;
-- Create all additional maccs in the banking container
gen_secpos(i_sec_asset_list => i_macc_pos_list ,i_dest_cont_id => l_cont_id);
----------------------------------------------------------------------
-- Investment positions/portfolio
----------------------------------------------------------------------
l_cont_id := coalesce(i_cont_id, bp#first_cont_of_type(l_bp_id, get_invst_cont_type));
if l_cont_id is null then
-- Then create an additional container for investments in the UK
l_cont_id := cont#create(
i_bp_id => l_bp_id
,i_sym_key => case
when i_sym_key is not null then
i_sym_key || '.02'
end
,i_sbu_id => l_sbu_id
,i_cont_type_id => code_obj_classif_cont_cont_type.cont_investment
,i_mand_code_id => code_obj_classif_cont_cont_inv_prof.mc_ucits_n
);
end if;
gen_secpos(i_sec_asset_list => l_sec_pos_list ,i_dest_cont_id => l_cont_id);
-- Show the newly created client in the portfolio view
if session.is_intf = '+' then
session.ui_mgr.open_app('portf', 'invst_portf');
if i_cont_id is not null then
session.ui_mgr.desk('portf').val('custr_obj_id') := i_cont_id;
else
session.ui_mgr.desk('portf').val('custr_obj_id') := l_bp_id;
end if;
end if;
exception
when others then
session.raise_fa_err('create_clt');
end create_clt;
end rbsc$sda_test;
