prompt Package Body : jr_reg_ver create or replace package body jr_reg_ver is ---------------------------------------------------------------------- -- get_table -- -- For a given element type short name or type irid, returns -- the table which implements that type and the type of the -- table. The type of table can be : -- P (Primary) -- S (Secondary) -- O (Primary and Secondary) -- -- NB. If specifying short_name, only works for Designer elements -- ie. product 'CI' -- CW 7-May-1999 ... and application logic ---------------------------------------------------------------------- procedure get_table ( type_irid in number , short_name in varchar2 , table_name out varchar2 , table_type out varchar2 , get_repos_table in boolean default false ) is query_repos_table varchar2(1) := 'N'; begin if get_repos_table then query_repos_table := 'Y'; end if; -- Use type_irid if specified if type_irid is not null then SELECT DECODE( query_repos_table , 'Y', tab.repos_table_name , 'N', tab.name ) , DECODE( tab.owned_by , null, 'P' , 'TRUE', 'S' , 'NO OFK', 'S' , 'OPTIONAL', 'O' ) INTO get_table.table_name, get_table.table_type FROM rm_element_types et , rm_sql_row_types srt , rm_sql_tables tab WHERE et.irid = get_table.type_irid AND srt.id = et.primary_row_type AND tab.id = srt.table_mapped ; elsif short_name is not null then if short_name != 'APPLOG' then SELECT DECODE( query_repos_table , 'Y', tab.repos_table_name , 'N', tab.name ) , DECODE( tab.owned_by , null, 'P' , 'TRUE', 'S' , 'NO OFK', 'S' , 'OPTIONAL', 'O' ) INTO get_table.table_name, get_table.table_type FROM rm_element_types et , rm_sql_row_types srt , rm_sql_tables tab WHERE et.product = 'CI' AND et.short_name = get_table.short_name AND srt.id = et.primary_row_type AND tab.id = srt.table_mapped ; elsif short_name = 'APPLOG' then SELECT DECODE( query_repos_table , 'Y', tab.repos_table_name , 'N', tab.name ) , DECODE( tab.owned_by , null, 'P' , 'TRUE', 'S' , 'NO OFK', 'S' , 'OPTIONAL', 'O' ) INTO get_table.table_name, get_table.table_type FROM rm_sql_tables tab WHERE tab.name = 'CK_APPLICATION_LOGIC' ; end if; end if; exception when no_data_found then if type_irid is not null then -- Cannot find Table which implements Type with %0!s = %1!s rmmes.post('CDR', 115, 'irid', type_irid); elsif short_name is not null then -- Cannot find Table which implements Type with %0!s = %1!s rmmes.post('CDR', 115, 'short_name', short_name); end if; raise table_not_found_for_type; end get_table; -------------------------------------------------------------- -- query_parent_info -------------------------------------------------------------- procedure query_parent_info ( table_name in varchar2 , table_type in varchar2 , ofk_column in number , pac_ref out number , parent_ivid out number , query_by_ivid in boolean default false ) is select_columns varchar2(50); vn_ctxt_columns varchar2(50); stmt varchar2(300); cur integer; ret integer; begin if table_type = 'P' then -- parent is a primary object select_columns := 'irid, ivid'; vn_ctxt_columns := 'owning_tab.ivid'; elsif table_type = 'S' then -- parent is a secondary object select_columns := 'pac_ref, parent_ivid'; vn_ctxt_columns := 'owning_tab.parent_ivid'; elsif table_type = 'O' then -- parent is a primary and secondary object select_columns := 'nvl(pac_ref, irid), nvl(parent_ivid, ivid)'; vn_ctxt_columns := 'nvl(owning_tab.parent_ivid, owning_tab.ivid)'; end if; stmt := 'select ' || select_columns || ' ' || 'from ' || table_name || ' owning_tab '; if query_by_ivid then stmt := stmt || 'where owning_tab.ivid = :ofk_column ' ; else stmt := stmt || 'where owning_tab.irid = :ofk_column ' || 'and jr_vn.context(' || vn_ctxt_columns || ') > 0' ; end if; cur:= dbms_sql.open_cursor; dbms_sql.parse(cur, stmt, dbms_sql.NATIVE); dbms_sql.define_column(cur, 1, pac_ref); dbms_sql.define_column(cur, 2, parent_ivid); dbms_sql.bind_variable(cur, ':ofk_column', ofk_column); ret := dbms_sql.execute_and_fetch(cur); if ret > 0 then dbms_sql.column_value(cur, 1, pac_ref); dbms_sql.column_value(cur, 2, parent_ivid); end if; dbms_sql.close_cursor(cur); if ret = 0 then -- No rows returned, failed to query parent info -- Failed to query parent details from Table %0!s where irid = %1!s rmmes.post('CDR', 116, table_name, ofk_column); end if; end query_parent_info; -------------------------------------------------------------- -- populate_parent_info -------------------------------------------------------------- procedure populate_parent_info ( ofk_column in number , short_name in varchar2 , pac_ref out number , parent_ivid out number ) is table_name varchar2(30); table_type varchar2(1); begin get_table( null -- type_irid , short_name , table_name , table_type ); if table_name is not null then query_parent_info( table_name , table_type , ofk_column , pac_ref , parent_ivid ); end if; end populate_parent_info; -------------------------------------------------------------- -- populate_parent_info -------------------------------------------------------------- procedure populate_parent_info ( ofk_column in number , type_irid in number , pac_ref out number , parent_ivid out number ) is table_name varchar2(30); table_type varchar2(1); begin get_table( type_irid , null -- short_name , table_name , table_type ); if table_name is not null then query_parent_info( table_name , table_type , ofk_column , pac_ref , parent_ivid ); end if; end populate_parent_info; -------------------------------------------------------------- -- populate_parent_info_by_ivid -------------------------------------------------------------- procedure populate_parent_info_by_ivid ( owner_ivid in number , type_irid in number , pac_ref out number , parent_ivid out number ) is table_name varchar2(30); table_type varchar2(1); begin get_table( type_irid , null -- short_name , table_name , table_type , true -- get repos table name (ie. i$ name) ); if table_name is not null then query_parent_info ( table_name , table_type , owner_ivid , pac_ref , parent_ivid , true -- query by ivid ); end if; end populate_parent_info_by_ivid; -------------------------------------------------------------- -- vercopy_user_prefs -------------------------------------------------------------- procedure vercopy_user_prefs ( old_parent_ivid number , new_parent_ivid number ) is cursor upr_cur is select * from i$sdd_upr the_tab where the_tab.parent_ivid = old_parent_ivid; begin for upr_rec in upr_cur loop insert into i$sdd_upr ( irid , ivid , pac_ref , parent_ivid , types , element_type_name , date_created , created_by , date_changed , changed_by , notm , cielement_ref , preference_name , preference_value , frozen_flag , preference_for , product_flavor ) values ( upr_rec.irid -- irid , jr_system_util.get_new_ivid -- ivid , upr_rec.pac_ref -- pac_ref , new_parent_ivid -- parent_ivid , upr_rec.types , upr_rec.element_type_name , upr_rec.date_created , upr_rec.created_by , upr_rec.date_changed , upr_rec.changed_by , upr_rec.notm , upr_rec.cielement_ref , upr_rec.preference_name , upr_rec.preference_value , upr_rec.frozen_flag , upr_rec.preference_for , upr_rec.product_flavor ); end loop; end vercopy_user_prefs; -------------------------------------------------------------- -- vercopy_pref_usages -------------------------------------------------------------- procedure vercopy_pref_usages ( old_parent_ivid number , new_parent_ivid number ) is cursor pru_cur is select * from i$sdd_pru the_tab where the_tab.parent_ivid = old_parent_ivid; begin for pru_rec in pru_cur loop insert into i$sdd_pru ( irid , ivid , pac_ref , parent_ivid , types , element_type_name , date_created , created_by , date_changed , changed_by , notm , preference_set_ref , cielement_ref , preference_set_usage_for ) values ( pru_rec.irid -- irid , jr_system_util.get_new_ivid -- ivid , pru_rec.pac_ref -- pac_ref , new_parent_ivid -- parent_ivid , pru_rec.types , pru_rec.element_type_name , pru_rec.date_created , pru_rec.created_by , pru_rec.date_changed , pru_rec.changed_by , pru_rec.notm , pru_rec.preference_set_ref , pru_rec.cielement_ref , pru_rec.preference_set_usage_for ); end loop; end vercopy_pref_usages; -------------------------------------------------------------- -- vercopy_elem_rule_usages -------------------------------------------------------------- procedure vercopy_elem_rule_usages ( old_parent_ivid number , new_parent_ivid number ) is cursor eru_cur is select * from i$sdd_eru the_tab where the_tab.parent_ivid = old_parent_ivid; begin for eru_rec in eru_cur loop insert into i$sdd_eru ( irid , ivid , pac_ref , parent_ivid , types , element_type_name , date_created , created_by , date_changed , changed_by , notm , cielement_ref , uml_rule_ref , element_type_for ) values ( eru_rec.irid -- irid , jr_system_util.get_new_ivid -- ivid , eru_rec.pac_ref -- pac_ref , new_parent_ivid -- parent_ivid , eru_rec.types , eru_rec.element_type_name , eru_rec.date_created , eru_rec.created_by , eru_rec.date_changed , eru_rec.changed_by , eru_rec.notm , eru_rec.cielement_ref , eru_rec.uml_rule_ref , eru_rec.element_type_for ); end loop; end vercopy_elem_rule_usages; -------------------------------------------------------------- -- vercopy_tagged_values -------------------------------------------------------------- procedure vercopy_tagged_values ( old_parent_ivid number , new_parent_ivid number ) is cursor tagval_cur is select * from i$sdd_tagval the_tab where the_tab.parent_ivid = old_parent_ivid; begin for tagval_rec in tagval_cur loop insert into i$sdd_tagval ( irid , ivid , pac_ref , parent_ivid , types , element_type_name , date_created , created_by , date_changed , changed_by , notm , element_ref , tag_ref , value , tagged_element_type ) values ( tagval_rec.irid -- irid , jr_system_util.get_new_ivid -- ivid , tagval_rec.pac_ref -- pac_ref , new_parent_ivid -- parent_ivid , tagval_rec.types , tagval_rec.element_type_name , tagval_rec.date_created , tagval_rec.created_by , tagval_rec.date_changed , tagval_rec.changed_by , tagval_rec.notm , tagval_rec.element_ref , tagval_rec.tag_ref , tagval_rec.value , tagval_rec.tagged_element_type ); end loop; end vercopy_tagged_values; -------------------------------------------------------------- -- vercopy_ues -------------------------------------------------------------- procedure vercopy_ues ( old_parent_ivid number , new_parent_ivid number ) is cursor ues_cur is select * from i$sdd_ues the_tab where the_tab.parent_ivid = old_parent_ivid; begin for ues_rec in ues_cur loop insert into i$sdd_ues ( irid , ivid , pac_ref , parent_ivid , types , element_type_name , date_created , created_by , date_changed , changed_by , notm , part_of_ref , use_of_ref , comments , element_type_for , usrx0 , usrx1 , usrx2 , usrx3 , usrx4 , usrx5 , usrx6 , usrx7 , usrx8 , usrx9 , usrx10 , usrx11 , usrx12 , usrx13 , usrx14 , usrx15 , usrx16 , usrx17 , usrx18 , usrx19 ) values ( ues_rec.irid -- irid , jr_system_util.get_new_ivid -- ivid , ues_rec.pac_ref -- pac_ref , new_parent_ivid -- parent_ivid , ues_rec.types , ues_rec.element_type_name , ues_rec.date_created , ues_rec.created_by , ues_rec.date_changed , ues_rec.changed_by , ues_rec.notm , ues_rec.part_of_ref , ues_rec.use_of_ref , ues_rec.comments , ues_rec.element_type_for , ues_rec.usrx0 , ues_rec.usrx1 , ues_rec.usrx2 , ues_rec.usrx3 , ues_rec.usrx4 , ues_rec.usrx5 , ues_rec.usrx6 , ues_rec.usrx7 , ues_rec.usrx8 , ues_rec.usrx9 , ues_rec.usrx10 , ues_rec.usrx11 , ues_rec.usrx12 , ues_rec.usrx13 , ues_rec.usrx14 , ues_rec.usrx15 , ues_rec.usrx16 , ues_rec.usrx17 , ues_rec.usrx18 , ues_rec.usrx19 ); end loop; end vercopy_ues; -------------------------------------------------------------- -- vercopy_text -------------------------------------------------------------- procedure vercopy_text ( old_parent_ivid number , new_parent_ivid number ) is cursor text_cur is select * from i$rm_text_lines the_tab where the_tab.parent_ivid = old_parent_ivid; begin for text_rec in text_cur loop insert into i$rm_text_lines ( irid , ivid , pac_ref , parent_ivid , txt_ref , txt_seq , txt_type , element_type_for , txt_notm , txt_text ) values ( text_rec.irid -- irid , jr_system_util.get_new_ivid -- ivid , text_rec.pac_ref -- pac_ref , new_parent_ivid -- parent_ivid , text_rec.txt_ref , text_rec.txt_seq , text_rec.txt_type , text_rec.element_type_for , text_rec.txt_notm , text_rec.txt_text ); end loop; end vercopy_text; -------------------------------------------------------------- -- vercopy_application_logic -------------------------------------------------------------- procedure vercopy_application_logic ( old_parent_ivid number , new_parent_ivid number ) is cursor applog_cur is select * from i$ck_application_logic the_tab where the_tab.parent_ivid = old_parent_ivid; begin for applog_rec in applog_cur loop insert into i$ck_application_logic ( irid , ivid , pac_ref , parent_ivid , al_id , al_source , al_type , al_repos_element , al_gen_product_id , al_gen_own_ref , al_gen_unique_context , al_target_location , al_evt_name , al_evt_seq_in_event , al_evt_context_repos_element , al_evt_execution_style , al_evt_short_description , al_nr_routine_name , al_nr_public_scope , al_nr_type , al_code_language , al_create_flag , element_type_for ) values ( applog_rec.irid -- irid , jr_system_util.get_new_ivid -- ivid , applog_rec.pac_ref -- pac_ref , new_parent_ivid -- parent_ivid , applog_rec.al_id , applog_rec.al_source , applog_rec.al_type , applog_rec.al_repos_element , applog_rec.al_gen_product_id , applog_rec.al_gen_own_ref , applog_rec.al_gen_unique_context , applog_rec.al_target_location , applog_rec.al_evt_name , applog_rec.al_evt_seq_in_event , applog_rec.al_evt_context_repos_element , applog_rec.al_evt_execution_style , applog_rec.al_evt_short_description , applog_rec.al_nr_routine_name , applog_rec.al_nr_public_scope , applog_rec.al_nr_type , applog_rec.al_code_language , applog_rec.al_create_flag , applog_rec.element_type_for ); end loop; end vercopy_application_logic; -------------------------------------------------------------- -- vercopy_dependent_objects -------------------------------------------------------------- procedure vercopy_dependent_objects ( old_parent_ivid number , new_parent_ivid number ) is begin vercopy_user_prefs (old_parent_ivid, new_parent_ivid); vercopy_pref_usages (old_parent_ivid, new_parent_ivid); vercopy_elem_rule_usages (old_parent_ivid, new_parent_ivid); vercopy_tagged_values (old_parent_ivid, new_parent_ivid); vercopy_ues (old_parent_ivid, new_parent_ivid); vercopy_text (old_parent_ivid, new_parent_ivid); vercopy_application_logic (old_parent_ivid, new_parent_ivid); end vercopy_dependent_objects; -------------------------------------------------------------- -- cpycopy_user_prefs -- -- This method copies user preferences for any element -------------------------------------------------------------- procedure cpycopy_user_prefs ( old_irid number , old_parent_ivid number , new_irid number , new_pac_ref number , new_parent_ivid number ) is cursor upr_cur (el_ref number, parent_ivid number) is select * from i$sdd_upr the_tab where the_tab.parent_ivid = upr_cur.parent_ivid and the_tab.cielement_ref = upr_cur.el_ref; begin for upr_rec in upr_cur (old_irid, old_parent_ivid) loop insert into i$sdd_upr ( irid , ivid , pac_ref , parent_ivid , types , element_type_name , date_created , created_by , date_changed , changed_by , notm , cielement_ref , preference_name , preference_value , frozen_flag , preference_for , product_flavor ) values ( jr_util.get_new_irid -- irid , jr_system_util.get_new_ivid -- ivid , new_pac_ref -- pac_ref , new_parent_ivid -- parent_ivid , upr_rec.types , upr_rec.element_type_name , upr_rec.date_created , upr_rec.created_by , upr_rec.date_changed , upr_rec.changed_by , upr_rec.notm , new_irid -- cielement_ref , upr_rec.preference_name , upr_rec.preference_value , upr_rec.frozen_flag , upr_rec.preference_for , upr_rec.product_flavor ); end loop; end cpycopy_user_prefs; -------------------------------------------------------------- -- cpycopy_pref_usages -- -- This method copies preference set usages for any element -------------------------------------------------------------- procedure cpycopy_pref_usages ( old_irid number , old_parent_ivid number , new_irid number , new_pac_ref number , new_parent_ivid number ) is cursor pru_cur (el_ref number, parent_ivid number) is select * from i$sdd_pru the_tab where the_tab.parent_ivid = pru_cur.parent_ivid and the_tab.cielement_ref = pru_cur.el_ref; begin for pru_rec in pru_cur (old_irid, old_parent_ivid) loop insert into i$sdd_pru ( irid , ivid , pac_ref , parent_ivid , types , element_type_name , date_created , created_by , date_changed , changed_by , notm , preference_set_ref , cielement_ref , preference_set_usage_for ) values ( jr_util.get_new_irid -- irid , jr_system_util.get_new_ivid -- ivid , new_pac_ref -- pac_ref , new_parent_ivid -- parent_ivid , pru_rec.types , pru_rec.element_type_name , pru_rec.date_created , pru_rec.created_by , pru_rec.date_changed , pru_rec.changed_by , pru_rec.notm , pru_rec.preference_set_ref , new_irid -- cielement_ref , pru_rec.preference_set_usage_for ); end loop; end cpycopy_pref_usages; -------------------------------------------------------------- -- cpycopy_elem_rule_usages -- -- This method copies element rule usages for any element -------------------------------------------------------------- procedure cpycopy_elem_rule_usages ( old_irid number , old_parent_ivid number , new_irid number , new_pac_ref number , new_parent_ivid number ) is cursor eru_cur (el_ref number, parent_ivid number) is select * from i$sdd_eru the_tab where the_tab.parent_ivid = eru_cur.parent_ivid and the_tab.cielement_ref = eru_cur.el_ref; begin for eru_rec in eru_cur (old_irid, old_parent_ivid) loop insert into i$sdd_eru ( irid , ivid , pac_ref , parent_ivid , types , element_type_name , date_created , created_by , date_changed , changed_by , notm , cielement_ref , uml_rule_ref , element_type_for ) values ( jr_util.get_new_irid -- irid , jr_system_util.get_new_ivid -- ivid , new_pac_ref -- pac_ref , new_parent_ivid -- parent_ivid , eru_rec.types , eru_rec.element_type_name , eru_rec.date_created , eru_rec.created_by , eru_rec.date_changed , eru_rec.changed_by , eru_rec.notm , new_irid -- cielement_ref , eru_rec.uml_rule_ref , eru_rec.element_type_for ); end loop; end cpycopy_elem_rule_usages; -------------------------------------------------------------- -- cpycopy_tagged_values -- -- This method copies tagged values for any element -------------------------------------------------------------- procedure cpycopy_tagged_values ( old_irid number , old_parent_ivid number , new_irid number , new_pac_ref number , new_parent_ivid number ) is cursor tagval_cur (el_ref number, parent_ivid number) is select * from i$sdd_tagval the_tab where the_tab.parent_ivid = tagval_cur.parent_ivid and the_tab.element_ref = tagval_cur.el_ref; begin for tagval_rec in tagval_cur (old_irid, old_parent_ivid) loop insert into i$sdd_tagval ( irid , ivid , pac_ref , parent_ivid , types , element_type_name , date_created , created_by , date_changed , changed_by , notm , element_ref , tag_ref , value , tagged_element_type ) values ( jr_util.get_new_irid -- irid , jr_system_util.get_new_ivid -- ivid , new_pac_ref -- pac_ref , new_parent_ivid -- parent_ivid , tagval_rec.types , tagval_rec.element_type_name , tagval_rec.date_created , tagval_rec.created_by , tagval_rec.date_changed , tagval_rec.changed_by , tagval_rec.notm , new_irid -- element_ref , tagval_rec.tag_ref , tagval_rec.value , tagval_rec.tagged_element_type ); end loop; end cpycopy_tagged_values; -------------------------------------------------------------- -- cpycopy_ues -- -- This method copies extended structure elements for any element -------------------------------------------------------------- procedure cpycopy_ues ( old_irid number , old_parent_ivid number , new_irid number , new_pac_ref number , new_parent_ivid number ) is cursor ues_cur (el_ref number, parent_ivid number) is select * from i$sdd_ues the_tab where the_tab.parent_ivid = ues_cur.parent_ivid and the_tab.part_of_ref = ues_cur.el_ref; begin -- bug fix 2099838 For extended copy ensure that we pick up the -- mapped "use of" foreign key from the id mappings table. -- This call will leave the id unchanged if the copy is not -- being done in the context of an extended copy (i.e. if -- the mappings table does not hold a new id for the use of reference). for ues_rec in ues_cur (old_irid, old_parent_ivid) loop insert into i$sdd_ues ( irid , ivid , pac_ref , parent_ivid , types , element_type_name , date_created , created_by , date_changed , changed_by , notm , part_of_ref , use_of_ref , comments , element_type_for , usrx0 , usrx1 , usrx2 , usrx3 , usrx4 , usrx5 , usrx6 , usrx7 , usrx8 , usrx9 , usrx10 , usrx11 , usrx12 , usrx13 , usrx14 , usrx15 , usrx16 , usrx17 , usrx18 , usrx19 ) values ( jr_util.get_new_irid -- irid , jr_system_util.get_new_ivid -- ivid , new_pac_ref -- pac_ref , new_parent_ivid -- parent_ivid , ues_rec.types , ues_rec.element_type_name , ues_rec.date_created , ues_rec.created_by , ues_rec.date_changed , ues_rec.changed_by , ues_rec.notm , new_irid -- part_of_ref , jr_copy.get_new_irid(ues_rec.use_of_ref) -- bf 2099838 , ues_rec.comments , ues_rec.element_type_for , ues_rec.usrx0 , ues_rec.usrx1 , ues_rec.usrx2 , ues_rec.usrx3 , ues_rec.usrx4 , ues_rec.usrx5 , ues_rec.usrx6 , ues_rec.usrx7 , ues_rec.usrx8 , ues_rec.usrx9 , ues_rec.usrx10 , ues_rec.usrx11 , ues_rec.usrx12 , ues_rec.usrx13 , ues_rec.usrx14 , ues_rec.usrx15 , ues_rec.usrx16 , ues_rec.usrx17 , ues_rec.usrx18 , ues_rec.usrx19 ); end loop; end cpycopy_ues; -------------------------------------------------------------- -- cpycopy_text -- -- This method copies text for any object -------------------------------------------------------------- procedure cpycopy_text ( old_txt_ref number , old_parent_ivid number , new_txt_ref number , new_pac_ref number , new_parent_ivid number ) is cursor text_cur (txt_ref number, parent_ivid number) is select * from i$rm_text_lines the_tab where the_tab.parent_ivid = text_cur.parent_ivid and the_tab.txt_ref = text_cur.txt_ref; begin for text_rec in text_cur (old_txt_ref, old_parent_ivid) loop insert into i$rm_text_lines ( irid , ivid , pac_ref , parent_ivid , txt_ref , txt_seq , txt_type , element_type_for , txt_notm , txt_text ) values ( jr_util.get_new_irid -- irid , jr_system_util.get_new_ivid -- ivid , new_pac_ref -- pac_ref , new_parent_ivid -- parent_ivid , new_txt_ref -- txt_ref , text_rec.txt_seq , text_rec.txt_type , text_rec.element_type_for , text_rec.txt_notm , text_rec.txt_text ); end loop; end cpycopy_text; -------------------------------------------------------------- -- cpycopy_application_logic -- -- This method copies application logic for any element -------------------------------------------------------------- procedure cpycopy_application_logic ( old_irid number , old_parent_ivid number , new_irid number , new_pac_ref number , new_parent_ivid number ) is new_applog_irid number; cursor applog_cur (el_ref number, parent_ivid number) is select * from i$ck_application_logic the_tab where the_tab.parent_ivid = applog_cur.parent_ivid and the_tab.al_repos_element = applog_cur.el_ref; begin for applog_rec in applog_cur (old_irid, old_parent_ivid) loop new_applog_irid := jr_util.get_new_irid; insert into i$ck_application_logic ( irid , ivid , pac_ref , parent_ivid , al_id , al_source , al_type , al_repos_element , al_gen_product_id , al_gen_own_ref , al_gen_unique_context , al_target_location , al_evt_name , al_evt_seq_in_event , al_evt_context_repos_element , al_evt_execution_style , al_evt_short_description , al_nr_routine_name , al_nr_public_scope , al_nr_type , al_code_language , al_create_flag , element_type_for ) values ( new_applog_irid -- irid , jr_system_util.get_new_ivid -- ivid , new_pac_ref -- pac_ref , new_parent_ivid -- parent_ivid , new_applog_irid -- al_id , applog_rec.al_source , applog_rec.al_type , new_irid -- al_repos_element , applog_rec.al_gen_product_id , applog_rec.al_gen_own_ref , applog_rec.al_gen_unique_context , applog_rec.al_target_location , applog_rec.al_evt_name , applog_rec.al_evt_seq_in_event , applog_rec.al_evt_context_repos_element , applog_rec.al_evt_execution_style , applog_rec.al_evt_short_description , applog_rec.al_nr_routine_name , applog_rec.al_nr_public_scope , applog_rec.al_nr_type , applog_rec.al_code_language , applog_rec.al_create_flag , applog_rec.element_type_for ); -- Copy any text referencing the application logic -- CW 25-Jul-2000 Fix bug 1314234, objects with application logic fail to -- be copied. Caused by passing the irid of the new object -- instead of the new irid of the application logic cpycopy_text( applog_rec.al_id , old_parent_ivid , new_applog_irid , new_pac_ref , new_parent_ivid ); end loop; end cpycopy_application_logic; -------------------------------------------------------------- -- cpycopy_dependent_objects -------------------------------------------------------------- procedure cpycopy_dependent_objects ( old_irid number , old_parent_ivid number , new_irid number , new_pac_ref number , new_parent_ivid number , copy_text boolean default true ) is begin cpycopy_user_prefs (old_irid, old_parent_ivid, new_irid, new_pac_ref, new_parent_ivid); cpycopy_pref_usages (old_irid, old_parent_ivid, new_irid, new_pac_ref, new_parent_ivid); cpycopy_elem_rule_usages (old_irid, old_parent_ivid, new_irid, new_pac_ref, new_parent_ivid); cpycopy_tagged_values (old_irid, old_parent_ivid, new_irid, new_pac_ref, new_parent_ivid); cpycopy_ues (old_irid, old_parent_ivid, new_irid, new_pac_ref, new_parent_ivid); if copy_text then cpycopy_text (old_irid, old_parent_ivid, new_irid, new_pac_ref, new_parent_ivid); end if; cpycopy_application_logic (old_irid, old_parent_ivid, new_irid, new_pac_ref, new_parent_ivid); end cpycopy_dependent_objects; -- Fix bug 881549, delete secondary objects with no ofk -------------------------------------------------------------- -- delete_user_prefs -------------------------------------------------------------- procedure delete_user_prefs (object_irid number ,par_ivid number) is begin delete i$sdd_upr deltab where deltab.cielement_ref = object_irid and deltab.parent_ivid=par_ivid; end delete_user_prefs; -------------------------------------------------------------- -- delete_pref_usages -------------------------------------------------------------- procedure delete_pref_usages (object_irid number ,par_ivid number) is begin delete i$sdd_pru deltab where deltab.cielement_ref = object_irid and deltab.parent_ivid=par_ivid; end delete_pref_usages; -------------------------------------------------------------- -- delete_elem_rule_usages -------------------------------------------------------------- procedure delete_elem_rule_usages (object_irid number ,par_ivid number) is begin delete i$sdd_eru deltab where deltab.cielement_ref = object_irid and deltab.parent_ivid = par_ivid; end delete_elem_rule_usages; -------------------------------------------------------------- -- delete_tagged_values -------------------------------------------------------------- procedure delete_tagged_values (object_irid number ,par_ivid number) is begin delete i$sdd_tagval deltab where deltab.element_ref = object_irid and deltab.parent_ivid = par_ivid; end delete_tagged_values; -------------------------------------------------------------- -- delete_ues -------------------------------------------------------------- procedure delete_ues (object_irid number ,par_ivid number) is begin delete i$sdd_ues deltab where deltab.part_of_ref = object_irid and deltab.parent_ivid = par_ivid; end delete_ues; -------------------------------------------------------------- -- delete_text -------------------------------------------------------------- procedure delete_text (object_irid number ,par_ivid number) is begin delete i$rm_text_lines deltab where deltab.txt_ref = object_irid and deltab.parent_ivid = par_ivid; end delete_text; -------------------------------------------------------------- -- delete_application_logic -------------------------------------------------------------- procedure delete_application_logic (object_irid number ,par_ivid number) is begin -- Note, application logic itself may have text defined against it ! delete i$rm_text_lines deltab where deltab.txt_ref in (select al.al_id from ck_application_logic al where al.al_repos_element = object_irid and al.parent_ivid=par_ivid ) and deltab.parent_ivid=par_ivid ; delete i$ck_application_logic deltab where deltab.al_repos_element = object_irid or deltab.al_evt_context_repos_element = object_irid and deltab.parent_ivid=par_ivid; end delete_application_logic; -------------------------------------------------------------- -- delete_dependencies -- -- CW 27-Sep-1999 Add irid to interface and to where clause -- so that indexes on supplier or client -- irid *and* ivid are used -------------------------------------------------------------- procedure delete_dependencies ( object_irid number , object_ivid number ) is begin delete sdd_dependencies deltab where deltab.supplier_irid = object_irid and deltab.supplier_ivid = object_ivid; delete sdd_dependencies deltab where deltab.client_irid = object_irid and deltab.client_ivid = object_ivid; end delete_dependencies; -------------------------------------------------------------------------- -- delete_dependent_objects -- Fix bug 881549, delete secondary objects with no ofk -- CW 12-Jan-2000 -- Fix bug 1148906, don't delete application logic if table being deleted -- from is application logic or we get mutating table error -------------------------------------------------------------------------- procedure delete_dependent_objects ( object_irid number , parent_ivid number , table_name varchar2 default null ) is APPLICATION_LOGIC_TABLE constant varchar2(20) := 'CK_APPLICATION_LOGIC'; begin delete_user_prefs (object_irid,parent_ivid); delete_pref_usages (object_irid,parent_ivid); delete_elem_rule_usages (object_irid,parent_ivid); delete_tagged_values (object_irid,parent_ivid); delete_ues (object_irid,parent_ivid); delete_text (object_irid,parent_ivid); if table_name != APPLICATION_LOGIC_TABLE then delete_application_logic (object_irid,parent_ivid); end if; end delete_dependent_objects; ----------------------------------------------------------------- -- copy_dependencies -- -- Note : the vercopy parameter indicates whether dependencies -- are being copied as a result of a versioning operation -- (true) or a copy operation ----------------------------------------------------------------- procedure copy_dependencies ( object_irid number , object_ivid number , new_object_irid number , new_object_ivid number , new_parent_ivid number default null , copy_deps_policy boolean default true , vercopy boolean default true ) is copy_deps_pol varchar2(1) := 'Y'; version_cpy varchar2(1) := 'Y'; -- is copy as a result of a versioning operation ? new_supplier_irid number; new_supplier_ivid number; new_supplier_p_ivid number; new_client_irid number; new_client_ivid number; new_client_p_ivid number; -- Query dependencies where the client or the supplier is the object being versioned/copied -- and return the correct rows to copy depending on policy and whether a version or copy -- operation is being performed cursor dep_cur (policy varchar2, vercpy varchar2) is select * from sdd_dependencies the_tab where ((the_tab.supplier_irid = object_irid and the_tab.supplier_ivid = object_ivid) or (the_tab.client_irid = object_irid and the_tab.client_ivid = object_ivid) ) and (( dep_cur.vercpy = 'Y' and (dep_cur.policy = 'Y' and (the_tab.copy_on_version = 'Y' or the_tab.copy_on_version is null)) or (dep_cur.policy = 'N' and the_tab.copy_on_version = 'Y') ) or ( dep_cur.vercpy = 'N' and (dep_cur.policy = 'Y' and (the_tab.copy_on_copy = 'Y' or the_tab.copy_on_copy is null)) or (dep_cur.policy = 'N' and the_tab.copy_on_copy = 'Y') ) ) ; begin if not copy_deps_policy then copy_deps_pol := 'N'; end if; if not vercopy then version_cpy := 'N'; end if; for dep_rec in dep_cur(copy_deps_pol, version_cpy) loop if dep_rec.supplier_irid = object_irid and dep_rec.supplier_ivid = object_ivid then -- The supplier is being versioned/copied, keep client details the same new_supplier_irid := new_object_irid; new_supplier_ivid := new_object_ivid; new_supplier_p_ivid := new_parent_ivid; new_client_irid := dep_rec.client_irid; new_client_ivid := dep_rec.client_ivid; new_client_p_ivid := dep_rec.client_parent_ivid; else -- The client is being versioned/copied, keep supplier details the same new_supplier_irid := dep_rec.supplier_irid; new_supplier_ivid := dep_rec.supplier_ivid; new_supplier_p_ivid := dep_rec.supplier_parent_ivid; new_client_irid := new_object_irid; new_client_ivid := new_object_ivid; new_client_p_ivid := new_parent_ivid; end if; insert into sdd_dependencies ( irid , ivid , dependency_type_irid , supplier_irid , supplier_ivid , supplier_type_id , client_irid , client_ivid , client_type_id , status , number_of_times_modified , date_created , created_by , date_changed , changed_by , remark , copy_on_version , copy_on_copy , supplier_parent_ivid , client_parent_ivid , purge_candidate ) values ( jr_util.get_new_irid -- irid , jr_system_util.get_new_ivid -- ivid , dep_rec.dependency_type_irid , new_supplier_irid -- supplier_irid , new_supplier_ivid -- supplier_ivid , dep_rec.supplier_type_id , new_client_irid -- client_irid , new_client_ivid -- client_ivid , dep_rec.client_type_id , dep_rec.status , dep_rec.number_of_times_modified , dep_rec.date_created , dep_rec.created_by , dep_rec.date_changed , dep_rec.changed_by , dep_rec.remark , dep_rec.copy_on_version , dep_rec.copy_on_copy , new_supplier_p_ivid -- supplier_parent_ivid , new_client_p_ivid -- client_parent_ivid , dep_rec.purge_candidate ); end loop; end copy_dependencies; ----------------------------------------------------------------- -- vercopy_dependencies -- Fix bug 993715, copy dependencies when an object is versioned -- There is a copy_on_version flag on dependencies with the -- following values : -- Y : Always copy (regardless of policy) -- N : Never copy (regardless of policy) -- null : Copy only if policy is set ----------------------------------------------------------------- procedure vercopy_dependencies ( object_irid number , object_ivid number , new_object_ivid number , new_parent_ivid number default null , vercopy_deps_policy boolean default true ) is -- irid stays the same when an object is versioned new_object_irid number := object_irid; begin copy_dependencies ( object_irid , object_ivid , new_object_irid , new_object_ivid , new_parent_ivid , vercopy_deps_policy , true -- copying as a result of a versioning operation ); end vercopy_dependencies; ----------------------------------------------------------------- -- cpycopy_dependencies -- -- For now, use the same copy code as for when versioning -- This assumes the treatment of rows with copy_on_version set -- is the same as for copy -- Fix bug xxxxxx, copy dependencies when an object is copied ----------------------------------------------------------------- procedure cpycopy_dependencies ( object_irid number , object_ivid number , new_object_irid number , new_object_ivid number , new_parent_ivid number default null , cpycopy_deps_policy boolean default true ) is begin copy_dependencies ( object_irid , object_ivid , new_object_irid , new_object_ivid , new_parent_ivid , cpycopy_deps_policy , false -- copying as a result of a copying operation ); end cpycopy_dependencies; end jr_reg_ver; /