REM ********************************************************************************* REM Hand-carved package body for COPYING MODULE UNITS SUBTYPES created on 22-JUN-99 REM ********************************************************************************* CREATE OR REPLACE PACKAGE BODY cimmun IS --============================= MAKE PRIVATE COPY ================================-- procedure make_private_copy(i_irid in number ,smcpl in out cioSPECIFIC_MCO.data ,deep in varchar2 default 'Y') is /* Select the MCN for the RMC to convert and the MCNs for all it's sub-components */ CURSOR sel_mcn IS select mcn.irid , mcn.ivid , 'RMC' parent_type , NULL parent_name from sdd_mcn mcn where mcn.general_module_ref = smcpl.v.general_module_reference and mcn.module_component_ref = i_irid UNION ALL select mcn.irid , mcn.ivid , 'SUB' parent_type , sub.name parent_name from sdd_mcn mcn , sdd_mun sub where mcn.general_module_ref = smcpl.v.general_module_reference and sub.module_unit_subtype = 'SUB' and mcn.subcomponent_ref = sub.irid and sub.module_component_ref = i_irid order by 3; /* Select all the MCLs (KBL or CUL) either to or from each RMC or SUB to convert */ /* Outer join to MTI because Custom Links (CUL) don't reference MTIs */ CURSOR sel_mcl (mcn_id in number) IS select mcl.irid , mcl.module_component_link_type , mti1.alias from_alias , mti2.alias to_alias , mcl.mcn_ref_from mcn_from , mcl.mcn_ref_to mcn_to from sdd_mcl mcl , sdd_mti mti1 , sdd_mti mti2 where (mcl.mcn_ref_to = mcn_id or mcl.mcn_ref_from = mcn_id) and mcl.table_usage_ref_from = mti1.irid (+) and mcl.table_usage_ref_to = mti2.irid (+); /* Select all the ITEs (DBI or UBI) within the RMC or its SUBs to convert */ /* Select all the ITEs (DBI or UBI) in a set operation with the RMC to convert */ CURSOR sel_item IS select ite.name , ite.irid , 'RMC' parent_type , NULL parent_name from sdd_ite ite where ite.module_unit_ref = i_irid UNION ALL select ite.name , ite.irid , 'SQS' parent_type , sqs.name parent_name from sdd_ite ite , sdd_sqs sqs where ite.sql_query_set_ref = sqs.irid and sqs.module_component_ref = i_irid UNION ALL select ite.name , ite.irid , 'SUB' parent_type , sub.name parent_name from sdd_ite ite , sdd_mun sub where sub.module_unit_subtype = 'SUB' and ite.module_unit_ref = sub.irid and sub.module_component_ref = i_irid; /* Select all the SUBs copied to the new SMC which do not have an inclusion */ CURSOR sel_sub IS select sub.id from ci_subcomponents sub where sub.module_component_reference = smcpl.v.id and NOT EXISTS ( select NULL from sdd_mcn mcn where mcn.general_module_ref = smcpl.v.general_module_reference and mcn.subcomponent_ref = sub.id); pl cioreusable_mco.data; mcnpl ciomodule_component_inclusion.data; mcnpl_null ciomodule_component_inclusion.data; culpl ciocustom_link.data; culpl_null ciocustom_link.data; kblpl ciokey_based_link.data; kblpl_null ciokey_based_link.data; rmc_mcn boolean := false; save_dont_check_mcn_unique boolean; save_rm_do_csynch boolean; -- B1969650 type mcn_pairs is record (old_id number, new_id number); type mcn_array is table of mcn_pairs index by binary_integer; mcn_list mcn_array; mcn_count number; begin if rm.do_preop then rmu.preop('ci','REUSABLE_MCO','make_private_copy'); end if; if i_irid IS NULL or smcpl.v.general_module_reference IS NULL then -- ****** do need a new better error message here rmmes.post(cioreusable_mco.invalid_ref_fac,cioreusable_mco.invalid_ref_code, 'REUSABLE_MCO',NVL(make_private_copy.i_irid, smcpl.v.general_module_reference)); end if; save_dont_check_mcn_unique := dont_check_mcn_unique; save_rm_do_csynch := rm.do_csynch; -- B1969650 pl.v.id := i_irid; rmdbg.trace('Selecting RMC details: '||to_char(i_irid)); cioreusable_mco.sel(i_irid, pl); rmdbg.trace('Copying RMC details to SMC'); -- Copy all RMC properties to SMC if not smcpl.i.autoquery_flag then smcpl.v.autoquery_flag := pl.v.autoquery_flag; if smcpl.v.autoquery_flag is null then smcpl.i.autoquery_flag := false; else smcpl.i.autoquery_flag := true; end if; end if; if not smcpl.i.datasource_type then smcpl.v.datasource_type := pl.v.datasource_type; if smcpl.v.datasource_type is null then smcpl.i.datasource_type := false; else smcpl.i.datasource_type := true; end if; end if; if not smcpl.i.datatarget_type then smcpl.v.datatarget_type := pl.v.datatarget_type; if smcpl.v.datatarget_type is null then smcpl.i.datatarget_type := false; else smcpl.i.datatarget_type := true; end if; end if; if not smcpl.i.delete_flag then smcpl.v.delete_flag := pl.v.delete_flag; if smcpl.v.delete_flag is null then smcpl.i.delete_flag := false; else smcpl.i.delete_flag := true; end if; end if; if not smcpl.i.display_title then smcpl.v.display_title := pl.v.display_title; if smcpl.v.display_title is null then smcpl.i.display_title := false; else smcpl.i.display_title := true; end if; end if; smcpl.i.general_module_reference := true; if not smcpl.i.help_context_id_prefix then smcpl.v.help_context_id_prefix := pl.v.help_context_id_prefix; if smcpl.v.help_context_id_prefix is null then smcpl.i.help_context_id_prefix := false; else smcpl.i.help_context_id_prefix := true; end if; end if; if not smcpl.i.help_context_id_suffix then smcpl.v.help_context_id_suffix := pl.v.help_context_id_suffix; if smcpl.v.help_context_id_suffix is null then smcpl.i.help_context_id_suffix := false; else smcpl.i.help_context_id_suffix := true; end if; end if; if smcpl.v.id is null then smcpl.i.id := false; else smcpl.i.id := true; end if; if not smcpl.i.implementation_item then smcpl.v.implementation_item := pl.v.implementation_item; if smcpl.v.implementation_item is null then smcpl.i.implementation_item := false; else smcpl.i.implementation_item := true; end if; end if; if not smcpl.i.insert_flag then smcpl.v.insert_flag := pl.v.insert_flag; if smcpl.v.insert_flag is null then smcpl.i.insert_flag := false; else smcpl.i.insert_flag := true; end if; end if; if smcpl.v.irid is null then smcpl.i.irid := false; else smcpl.i.irid := true; end if; if smcpl.v.ivid is null then smcpl.i.ivid := false; else smcpl.i.ivid := true; end if; if not smcpl.i.language_reference then smcpl.v.language_reference := pl.v.language_reference; if smcpl.v.language_reference is null then smcpl.i.language_reference := false; else smcpl.i.language_reference := true; end if; end if; if not smcpl.i.layout_style then smcpl.v.layout_style := pl.v.layout_style; if smcpl.v.layout_style is null then smcpl.i.layout_style := false; else smcpl.i.layout_style := true; end if; end if; smcpl.v.module_level_flag := null; smcpl.i.module_level_flag := false; if not smcpl.i.name then smcpl.v.name := pl.v.name; if smcpl.v.name is null then smcpl.i.name := false; else smcpl.i.name := true; end if; end if; smcpl.v.pac_reference := null; smcpl.i.pac_reference := false; smcpl.v.parent_ivid := null; smcpl.i.parent_ivid := false; if not smcpl.i.req_overflow then smcpl.v.req_overflow := pl.v.req_overflow; if smcpl.v.req_overflow is null then smcpl.i.req_overflow := false; else smcpl.i.req_overflow := true; end if; end if; if not smcpl.i.req_page_height then smcpl.v.req_page_height := pl.v.req_page_height; if smcpl.v.req_page_height is null then smcpl.i.req_page_height := false; else smcpl.i.req_page_height := true; end if; end if; if not smcpl.i.req_page_width then smcpl.v.req_page_width := pl.v.req_page_width; if smcpl.v.req_page_width is null then smcpl.i.req_page_width := false; else smcpl.i.req_page_width := true; end if; end if; if not smcpl.i.req_view_height then smcpl.v.req_view_height := pl.v.req_view_height; if smcpl.v.req_view_height is null then smcpl.i.req_view_height := false; else smcpl.i.req_view_height := true; end if; end if; if not smcpl.i.req_view_width then smcpl.v.req_view_width := pl.v.req_view_width; if smcpl.v.req_view_width is null then smcpl.i.req_view_width := false; else smcpl.i.req_view_width := true; end if; end if; if not smcpl.i.rows_displayed then smcpl.v.rows_displayed := pl.v.rows_displayed; if smcpl.v.rows_displayed is null then smcpl.i.rows_displayed := false; else smcpl.i.rows_displayed := true; end if; end if; if not smcpl.i.runtime_pathname then smcpl.v.runtime_pathname := pl.v.runtime_pathname; if smcpl.v.runtime_pathname is null then smcpl.i.runtime_pathname := false; else smcpl.i.runtime_pathname := true; end if; end if; if not smcpl.i.select_flag then smcpl.v.select_flag := pl.v.select_flag; if smcpl.v.select_flag is null then smcpl.i.select_flag := false; else smcpl.i.select_flag := true; end if; end if; if not smcpl.i.short_description then smcpl.v.short_description := pl.v.short_description; if smcpl.v.short_description is null then smcpl.i.short_description := false; else smcpl.i.short_description := true; end if; end if; smcpl.v.types := null; smcpl.i.types := false; if not smcpl.i.update_flag then smcpl.v.update_flag := pl.v.update_flag; if smcpl.v.update_flag is null then smcpl.i.update_flag := false; else smcpl.i.update_flag := true; end if; end if; smcpl.v.user_defined_property_0 := null; smcpl.i.user_defined_property_0 := false; smcpl.v.user_defined_property_1 := null; smcpl.i.user_defined_property_1 := false; smcpl.v.user_defined_property_2 := null; smcpl.i.user_defined_property_2 := false; smcpl.v.user_defined_property_3 := null; smcpl.i.user_defined_property_3 := false; smcpl.v.user_defined_property_4 := null; smcpl.i.user_defined_property_4 := false; smcpl.v.user_defined_property_5 := null; smcpl.i.user_defined_property_5 := false; smcpl.v.user_defined_property_6 := null; smcpl.i.user_defined_property_6 := false; smcpl.v.user_defined_property_7 := null; smcpl.i.user_defined_property_7 := false; smcpl.v.user_defined_property_8 := null; smcpl.i.user_defined_property_8 := false; smcpl.v.user_defined_property_9 := null; smcpl.i.user_defined_property_9 := false; smcpl.v.user_defined_property_10 := null; smcpl.i.user_defined_property_10 := false; smcpl.v.user_defined_property_11 := null; smcpl.i.user_defined_property_11 := false; smcpl.v.user_defined_property_12 := null; smcpl.i.user_defined_property_12 := false; smcpl.v.user_defined_property_13 := null; smcpl.i.user_defined_property_13 := false; smcpl.v.user_defined_property_14 := null; smcpl.i.user_defined_property_14 := false; smcpl.v.user_defined_property_15 := null; smcpl.i.user_defined_property_15 := false; smcpl.v.user_defined_property_16 := null; smcpl.i.user_defined_property_16 := false; smcpl.v.user_defined_property_17 := null; smcpl.i.user_defined_property_17 := false; smcpl.v.user_defined_property_18 := null; smcpl.i.user_defined_property_18 := false; smcpl.v.user_defined_property_19 := null; smcpl.i.user_defined_property_19 := false; rmdbg.trace('Inserting new SMC'); ciospecific_mco.ins(smcpl.v.id, smcpl); if deep = 'Y' then rmdbg.trace('Deep copy SACs of RMC to SMC'); jr_copy.copy(i_irid, pl.v.ivid, smcpl.v.irid, smcpl.v.ivid, pl.v.types, true, false); end if; -- Copy the Module Component Inclusion if it exists rmdbg.trace('Copy the Module Component Inclusion if it exists'); -- Need to copy all MCNs before attempting to copy MCLs mcn_count := 0; rmdbg.trace('Before sel_mcn loop'); FOR cmcn IN sel_mcn LOOP rmdbg.trace('In sel_mcn loop'); mcnpl.v.general_module_reference := smcpl.v.general_module_reference; mcnpl.i.general_module_reference := true; if cmcn.parent_type = 'RMC' and deep = 'Y' then rmc_mcn := true; rmdbg.trace('Copy existing RMC-MCN : ' ||to_char(cmcn.irid)); mcnpl.v.module_component_reference := smcpl.v.irid; mcnpl.i.module_component_reference := true; ciomodule_component_inclusion.copy_sac(cmcn.irid, mcnpl); elsif cmcn.parent_type = 'SUB' and deep = 'Y' then rmdbg.trace('Copy existing SUB-MCN : '||cmcn.parent_name||':'||to_char(cmcn.irid)); SELECT max(sb.id) INTO mcnpl.v.subcomponent_reference FROM ci_subcomponents sb WHERE sb.name = cmcn.parent_name AND sb.module_component_reference = smcpl.v.irid; mcnpl.i.subcomponent_reference := true; -- Set parent mcn reference to the new SMC's MCN id -- Must always be first in list because of order by clause mcnpl.v.parent_mcn_reference := mcn_list(1).new_id; mcnpl.i.parent_mcn_reference := true; rmdbg.trace('Copy existing SUB-MCN : '||to_char(mcnpl.v.subcomponent_reference)); -- don't want to check uniqueness of MCN for SUB as it will always fail dont_check_mcn_unique := true; -- B1969650 : Stop ciomodule_component_inclusion.maintain (called from ciomodule_component_inclusion.copy_sac) -- setting mcnpl.i.parent_mcn_reference to false, and preventing new value of -- mcnpl.v.parent_mcn_reference being inserted for subcomponent. rm.do_csynch := true; ciomodule_component_inclusion.copy_sac(cmcn.irid, mcnpl); dont_check_mcn_unique := save_dont_check_mcn_unique; rm.do_csynch := save_rm_do_csynch; end if; -- Store list of old and new mcn irids mcn_count := mcn_count + 1; mcn_list(mcn_count).old_id := cmcn.irid; mcn_list(mcn_count).new_id := mcnpl.v.irid; -- Nullify copy property list mcnpl := mcnpl_null; END LOOP; -- Now copy all MCLs for each copied MCN FOR cmcn IN sel_mcn LOOP rmdbg.trace('In sel_mcn loop for copying MCLs : '||to_char(cmcn.irid)); -- Copy module component links FOR cmcl IN sel_mcl(cmcn.irid) LOOP if (cmcl.module_component_link_type = 'KBL') then rmdbg.trace('Copy existing Key Based Link: '||to_char(cmcl.irid)); rmdbg.trace('From Alias: '||cmcl.from_alias); kblpl.v.mcn_reference_to := cmcl.mcn_to; kblpl.i.mcn_reference_to := true; rmdbg.trace('MCN ref to : '||to_char(kblpl.v.mcn_reference_to)); kblpl.v.mcn_reference_from := cmcl.mcn_from; kblpl.i.mcn_reference_from := true; rmdbg.trace('MCN ref from : '||to_char(kblpl.v.mcn_reference_from)); -- Find new irid of MCNS if applicable FOR i in 1..mcn_list.last loop rmdbg.trace('Contents of MCN list OLD: '||to_char(mcn_list(i).old_id)); rmdbg.trace('Contents of MCN list NEW: '||to_char(mcn_list(i).new_id)); -- reset mcn_reference_to IF it should now point at a new MCN if (mcn_list(i).old_id = cmcl.mcn_to) then rmdbg.trace('Resetting to MCN ref to : '||to_char(kblpl.v.mcn_reference_to)); kblpl.v.mcn_reference_to := mcn_list(i).new_id; kblpl.i.mcn_reference_to := true; end if; -- reset mcn_reference_from IF it should now point at a new MCN if (mcn_list(i).old_id = cmcl.mcn_from) then rmdbg.trace('Resetting from MCN ref to OLD: '||to_char(kblpl.v.mcn_reference_from)); kblpl.v.mcn_reference_from := mcn_list(i).new_id; kblpl.i.mcn_reference_from := true; rmdbg.trace('Resetting from MCN ref to NEW: '||to_char(kblpl.v.mcn_reference_from)); end if; END LOOP; -- Find the irid of the corresponding copied 'from' MTI SELECT mti.irid INTO kblpl.v.table_usage_reference_from FROM sdd_mti mti , sdd_mcn mcn WHERE mti.alias = cmcl.from_alias AND mcn.irid = kblpl.v.mcn_reference_from AND mti.module_unit_ref = NVL(mcn.module_component_ref, mcn.subcomponent_ref); rmdbg.trace('Copy KBL 1: '||to_char(kblpl.v.table_usage_reference_from)); rmdbg.trace('To Alias: '||cmcl.to_alias); kblpl.i.table_usage_reference_from := true; -- Find the irid of the corresponding copied 'to' MTI SELECT mti.irid INTO kblpl.v.table_usage_reference_to FROM sdd_mti mti , sdd_mcn mcn WHERE mti.alias = cmcl.to_alias AND mcn.irid = kblpl.v.mcn_reference_to AND mti.module_unit_ref = NVL(mcn.module_component_ref, mcn.subcomponent_ref); rmdbg.trace('Copy KBL 1: '||to_char(kblpl.v.table_usage_reference_to)); kblpl.i.table_usage_reference_to := true; rmdbg.trace('Do actual copy of existing KBL : ' ||to_char(cmcl.irid)); ciokey_based_link.copy_sac(cmcl.irid, kblpl); -- Delete old key based link rmdbg.trace('Delete existing KBL : ' ||to_char(cmcl.irid)); ciokey_based_link.del(cmcl.irid); -- Nullify copy property list kblpl := kblpl_null; elsif (cmcl.module_component_link_type = 'CUL') then rmdbg.trace('Copy existing Custom Link: '||to_char(cmcl.irid)); -- Find new irid of MCNS if applicable FOR i in 1..mcn_list.last loop -- reset mcn_reference_to IF it should now point at a new MCN if (mcn_list(i).old_id = cmcl.mcn_to) then culpl.v.mcn_reference_to := mcn_list(i).new_id; culpl.i.mcn_reference_to := true; end if; -- reset mcn_reference_from IF it should now point at a new MCN if (mcn_list(i).old_id = cmcl.mcn_from) then culpl.v.mcn_reference_from := mcn_list(i).new_id; culpl.i.mcn_reference_from := true; end if; END LOOP; rmdbg.trace('Do actual copy of existing CUL : ' ||to_char(cmcl.irid)); ciocustom_link.copy_sac(cmcl.irid, culpl); -- Delete old custom link rmdbg.trace('Delete existing CUL : ' ||to_char(cmcl.irid)); ciocustom_link.del(cmcl.irid); -- Nullify copy property list culpl := culpl_null; end if; END LOOP; -- Delete old module component inclusion rmdbg.trace('Force delete existing '||cmcn.parent_type||'-MCN : ' ||to_char(cmcn.irid)); -- ciomodule_component_inclusion.del(cmcn.irid); jr_wastebasket.force_delete(cmcn.ivid, 'MCN', 'CI'); END LOOP; -- Adjust any LOV bindings to new items in SMC rmdbg.trace('Adjust LOV inclusions if they exist'); FOR ite IN sel_item LOOP if ite.parent_type = 'RMC' then rmdbg.trace('Update RMC-LOV inclusions for item: '||to_char(ite.irid)); UPDATE sdd_lvi lovi SET lovi.item_ref = ( SELECT it.irid FROM sdd_ite it WHERE it.name = ite.name AND it.module_unit_ref = smcpl.v.irid) WHERE lovi.item_ref = ite.irid; rmdbg.trace('Updated '||to_char(sql%rowcount)||' RMC-LOV inclusions for item: '||to_char(ite.irid)); elsif ite.parent_type = 'SQS' then rmdbg.trace('Update SQS-LOV inclusions for item: '||to_char(ite.irid)); UPDATE sdd_lvi lovi SET lovi.item_ref = ( SELECT it.irid FROM sdd_ite it , sdd_sqs sq WHERE it.name = ite.name AND it.sql_query_set_ref = sq.irid AND sq.name = ite.parent_name AND sq.module_component_ref = smcpl.v.irid) WHERE lovi.item_ref = ite.irid; rmdbg.trace('Updated '||to_char(sql%rowcount)||' SQS-LOV inclusions for item: '||to_char(ite.irid)); elsif ite.parent_type = 'SUB' then rmdbg.trace('Update SUB-LOV inclusions for item: '||to_char(ite.irid)); UPDATE sdd_lvi lovi SET lovi.item_ref = ( SELECT it.irid FROM sdd_ite it , ci_subcomponents sb WHERE it.name = ite.name AND it.module_unit_ref = sb.id AND sb.name = ite.parent_name AND sb.module_component_reference = smcpl.v.irid) WHERE lovi.item_ref = ite.irid; rmdbg.trace('Updated '||to_char(sql%rowcount)||' SUB-LOV inclusions for item: '||to_char(ite.irid)); end if; END LOOP; -- Since the triggers copy the SUBs of the RMC regardless of whether they have -- been included in the Module then the code now needs to delete any Subcomponents -- owned by the new SMC which have not had inclusions created for them. -- Delete any SUBs with no MCN rmdbg.trace('Delete any SUBs with no MCN'); FOR csub IN sel_sub LOOP -- Delete old module component inclusion rmdbg.trace('Delete superfluous Subcomponent: '||to_char(csub.id)); ciosubcomponent.del(csub.id); END LOOP; if rm.do_postop then rmu.postop('ci','REUSABLE_MCO','make_private_copy'); end if; exception when others then rmdbg.trace('In when others, sqlcode : '||sqlcode||' Message : '||sqlerrm); rmmes.post(cioreusable_mco.operation_failed_fac,cioreusable_mco.operation_failed_code, 'make_private_copy','REUSABLE_MCO'); raise; end make_private_copy; --============================= MAKE REUSABLE COPY ================================-- procedure make_reusable_copy(i_irid in number ,rmcpl in out cioREUSABLE_MCO.data ,deep in varchar2 default 'Y') is pl ciospecific_mco.data; CURSOR sel_mcn IS select mcn.irid , 'SMC' parent_type , NULL parent_name from sdd_mcn mcn where mcn.general_module_ref = pl.v.general_module_reference and mcn.module_component_ref = i_irid UNION ALL select mcn.irid , 'SUB' parent_type , sub.name parent_name from sdd_mcn mcn , sdd_mun sub where mcn.general_module_ref = pl.v.general_module_reference and sub.module_unit_subtype = 'SUB' and mcn.subcomponent_ref = sub.irid and sub.module_component_ref = i_irid order by 2; CURSOR sel_mcl (mcn_id in number) IS select mcl.irid , mcl.module_component_link_type , mti1.alias from_alias , mti2.alias to_alias , mcl.mcn_ref_from mcn_from , mcl.mcn_ref_to mcn_to from sdd_mcl mcl , sdd_mti mti1 , sdd_mti mti2 where (mcl.mcn_ref_to = mcn_id or mcl.mcn_ref_from = mcn_id) and mcl.table_usage_ref_from = mti1.irid (+) and mcl.table_usage_ref_to = mti2.irid (+); CURSOR sel_item IS select ite.name , ite.irid , 'SMC' parent_type , NULL parent_name from sdd_ite ite where ite.module_unit_ref = i_irid UNION ALL select ite.name , ite.irid , 'SQS' parent_type , sqs.name parent_name from sdd_ite ite , sdd_sqs sqs where ite.sql_query_set_ref = sqs.irid and sqs.module_component_ref = i_irid UNION ALL select ite.name , ite.irid , 'SUB' parent_type , sub.name parent_name from sdd_ite ite , sdd_mun sub where sub.module_unit_subtype = 'SUB' and ite.module_unit_ref = sub.irid and sub.module_component_ref = i_irid; mcnpl ciomodule_component_inclusion.data; mcnpl_null ciomodule_component_inclusion.data; culpl ciocustom_link.data; culpl_null ciocustom_link.data; kblpl ciokey_based_link.data; kblpl_null ciokey_based_link.data; smc_mcn boolean := false; save_dont_check_mcn_unique boolean; save_rm_do_csynch boolean; -- B1969650 type mcn_pairs is record (old_id number, new_id number); type mcn_array is table of mcn_pairs index by binary_integer; mcn_list mcn_array; mcn_count number; begin if rm.do_preop then rmu.preop('ci','SPECIFIC_MCO','make_reusable_copy'); end if; if i_irid IS NULL then -- ****** do need a new better error message here rmmes.post(ciospecific_mco.invalid_ref_fac,ciospecific_mco.invalid_ref_code, 'REUSABLE_MCO', make_reusable_copy.i_irid); end if; save_dont_check_mcn_unique := dont_check_mcn_unique; save_rm_do_csynch := rm.do_csynch; -- B1969650 pl.v.id := i_irid; rmdbg.trace('Selecting SMC details: '||to_char(i_irid)); ciospecific_mco.sel(i_irid, pl); rmdbg.trace('Copying SMC details to RMC'); -- Copy all SMC properties to RMC if not rmcpl.i.autoquery_flag then rmcpl.v.autoquery_flag := pl.v.autoquery_flag; if rmcpl.v.autoquery_flag is null then rmcpl.i.autoquery_flag := false; else rmcpl.i.autoquery_flag := true; end if; end if; if not rmcpl.i.datasource_type then rmcpl.v.datasource_type := pl.v.datasource_type; if rmcpl.v.datasource_type is null then rmcpl.i.datasource_type := false; else rmcpl.i.datasource_type := true; end if; end if; if not rmcpl.i.datatarget_type then rmcpl.v.datatarget_type := pl.v.datatarget_type; if rmcpl.v.datatarget_type is null then rmcpl.i.datatarget_type := false; else rmcpl.i.datatarget_type := true; end if; end if; if not rmcpl.i.delete_flag then rmcpl.v.delete_flag := pl.v.delete_flag; if rmcpl.v.delete_flag is null then rmcpl.i.delete_flag := false; else rmcpl.i.delete_flag := true; end if; end if; if not rmcpl.i.display_title then rmcpl.v.display_title := pl.v.display_title; if rmcpl.v.display_title is null then rmcpl.i.display_title := false; else rmcpl.i.display_title := true; end if; end if; if not rmcpl.i.help_context_id_prefix then rmcpl.v.help_context_id_prefix := pl.v.help_context_id_prefix; if rmcpl.v.help_context_id_prefix is null then rmcpl.i.help_context_id_prefix := false; else rmcpl.i.help_context_id_prefix := true; end if; end if; if not rmcpl.i.help_context_id_suffix then rmcpl.v.help_context_id_suffix := pl.v.help_context_id_suffix; if rmcpl.v.help_context_id_suffix is null then rmcpl.i.help_context_id_suffix := false; else rmcpl.i.help_context_id_suffix := true; end if; end if; if rmcpl.v.id is null then rmcpl.i.id := false; else rmcpl.i.id := true; end if; if not rmcpl.i.implementation_item then rmcpl.v.implementation_item := pl.v.implementation_item; if rmcpl.v.implementation_item is null then rmcpl.i.implementation_item := false; else rmcpl.i.implementation_item := true; end if; end if; if not rmcpl.i.insert_flag then rmcpl.v.insert_flag := pl.v.insert_flag; if rmcpl.v.insert_flag is null then rmcpl.i.insert_flag := false; else rmcpl.i.insert_flag := true; end if; end if; if rmcpl.v.irid is null then rmcpl.i.irid := false; else rmcpl.i.irid := true; end if; if rmcpl.v.ivid is null then rmcpl.i.ivid := false; else rmcpl.i.ivid := true; end if; if not rmcpl.i.language_reference then rmcpl.v.language_reference := pl.v.language_reference; if rmcpl.v.language_reference is null then rmcpl.i.language_reference := false; else rmcpl.i.language_reference := true; end if; end if; if not rmcpl.i.layout_style then rmcpl.v.layout_style := pl.v.layout_style; if rmcpl.v.layout_style is null then rmcpl.i.layout_style := false; else rmcpl.i.layout_style := true; end if; end if; if not rmcpl.i.name then rmcpl.v.name := pl.v.name; if rmcpl.v.name is null then rmcpl.i.name := false; else rmcpl.i.name := true; end if; end if; rmcpl.v.pac_reference := null; rmcpl.i.pac_reference := false; rmcpl.v.parent_ivid := null; rmcpl.i.parent_ivid := false; if not rmcpl.i.req_overflow then rmcpl.v.req_overflow := pl.v.req_overflow; if rmcpl.v.req_overflow is null then rmcpl.i.req_overflow := false; else rmcpl.i.req_overflow := true; end if; end if; if not rmcpl.i.req_page_height then rmcpl.v.req_page_height := pl.v.req_page_height; if rmcpl.v.req_page_height is null then rmcpl.i.req_page_height := false; else rmcpl.i.req_page_height := true; end if; end if; if not rmcpl.i.req_page_width then rmcpl.v.req_page_width := pl.v.req_page_width; if rmcpl.v.req_page_width is null then rmcpl.i.req_page_width := false; else rmcpl.i.req_page_width := true; end if; end if; if not rmcpl.i.req_view_height then rmcpl.v.req_view_height := pl.v.req_view_height; if rmcpl.v.req_view_height is null then rmcpl.i.req_view_height := false; else rmcpl.i.req_view_height := true; end if; end if; if not rmcpl.i.req_view_width then rmcpl.v.req_view_width := pl.v.req_view_width; if rmcpl.v.req_view_width is null then rmcpl.i.req_view_width := false; else rmcpl.i.req_view_width := true; end if; end if; if not rmcpl.i.rows_displayed then rmcpl.v.rows_displayed := pl.v.rows_displayed; if rmcpl.v.rows_displayed is null then rmcpl.i.rows_displayed := false; else rmcpl.i.rows_displayed := true; end if; end if; if not rmcpl.i.runtime_pathname then rmcpl.v.runtime_pathname := pl.v.runtime_pathname; if rmcpl.v.runtime_pathname is null then rmcpl.i.runtime_pathname := false; else rmcpl.i.runtime_pathname := true; end if; end if; if not rmcpl.i.select_flag then rmcpl.v.select_flag := pl.v.select_flag; if rmcpl.v.select_flag is null then rmcpl.i.select_flag := false; else rmcpl.i.select_flag := true; end if; end if; if not rmcpl.i.short_description then rmcpl.v.short_description := pl.v.short_description; if rmcpl.v.short_description is null then rmcpl.i.short_description := false; else rmcpl.i.short_description := true; end if; end if; rmcpl.v.types := null; rmcpl.i.types := false; if not rmcpl.i.update_flag then rmcpl.v.update_flag := pl.v.update_flag; if rmcpl.v.update_flag is null then rmcpl.i.update_flag := false; else rmcpl.i.update_flag := true; end if; end if; rmcpl.v.user_defined_property_0 := null; rmcpl.i.user_defined_property_0 := false; rmcpl.v.user_defined_property_1 := null; rmcpl.i.user_defined_property_1 := false; rmcpl.v.user_defined_property_2 := null; rmcpl.i.user_defined_property_2 := false; rmcpl.v.user_defined_property_3 := null; rmcpl.i.user_defined_property_3 := false; rmcpl.v.user_defined_property_4 := null; rmcpl.i.user_defined_property_4 := false; rmcpl.v.user_defined_property_5 := null; rmcpl.i.user_defined_property_5 := false; rmcpl.v.user_defined_property_6 := null; rmcpl.i.user_defined_property_6 := false; rmcpl.v.user_defined_property_7 := null; rmcpl.i.user_defined_property_7 := false; rmcpl.v.user_defined_property_8 := null; rmcpl.i.user_defined_property_8 := false; rmcpl.v.user_defined_property_9 := null; rmcpl.i.user_defined_property_9 := false; rmcpl.v.user_defined_property_10 := null; rmcpl.i.user_defined_property_10 := false; rmcpl.v.user_defined_property_11 := null; rmcpl.i.user_defined_property_11 := false; rmcpl.v.user_defined_property_12 := null; rmcpl.i.user_defined_property_12 := false; rmcpl.v.user_defined_property_13 := null; rmcpl.i.user_defined_property_13 := false; rmcpl.v.user_defined_property_14 := null; rmcpl.i.user_defined_property_14 := false; rmcpl.v.user_defined_property_15 := null; rmcpl.i.user_defined_property_15 := false; rmcpl.v.user_defined_property_16 := null; rmcpl.i.user_defined_property_16 := false; rmcpl.v.user_defined_property_17 := null; rmcpl.i.user_defined_property_17 := false; rmcpl.v.user_defined_property_18 := null; rmcpl.i.user_defined_property_18 := false; rmcpl.v.user_defined_property_19 := null; rmcpl.i.user_defined_property_19 := false; rmdbg.trace('Inserting new RMC'); cioreusable_mco.ins(rmcpl.v.id, rmcpl); if deep = 'Y' then rmdbg.trace('Deep copy SACs of SMC to RMC'); jr_copy.copy(i_irid, pl.v.ivid, rmcpl.v.irid, rmcpl.v.ivid, pl.v.types, true, false); end if; -- Copy the Module Component Inclusion if it exists rmdbg.trace('Copy the Module Component Inclusion if it exists'); -- Need to copy all MCNs before attempting to copy MCLs mcn_count := 0; rmdbg.trace('Before sel_mcn loop'); FOR cmcn IN sel_mcn LOOP rmdbg.trace('In sel_mcn loop'); mcnpl.v.general_module_reference := pl.v.general_module_reference; mcnpl.i.general_module_reference := true; if cmcn.parent_type = 'SMC' and deep = 'Y' then smc_mcn := true; rmdbg.trace('Copy existing SMC-MCN : ' ||to_char(cmcn.irid)); mcnpl.v.module_component_reference := rmcpl.v.irid; mcnpl.i.module_component_reference := true; ciomodule_component_inclusion.copy_sac(cmcn.irid, mcnpl); elsif cmcn.parent_type = 'SUB' and deep = 'Y' then rmdbg.trace('Copy existing SUB-MCN : ' ||to_char(cmcn.irid)); SELECT max(sb.id) INTO mcnpl.v.subcomponent_reference FROM ci_subcomponents sb WHERE sb.name = cmcn.parent_name AND sb.module_component_reference = rmcpl.v.irid; mcnpl.i.subcomponent_reference := true; -- Set parent mcn reference to the new RMC's MCN id -- Must always be first in list because of order by clause mcnpl.v.parent_mcn_reference := mcn_list(1).new_id; mcnpl.i.parent_mcn_reference := true; rmdbg.trace('Copy existing SUB-MCN : '||to_char(mcnpl.v.subcomponent_reference)); -- don't want to check uniqueness of MCN for SUB as it will always fail dont_check_mcn_unique := true; -- B1969650 : Stop ciomodule_component_inclusion.maintain (called from ciomodule_component_inclusion.copy_sac) -- setting mcnpl.i.parent_mcn_reference to false, and preventing new value of -- mcnpl.v.parent_mcn_reference being inserted for subcomponent. rm.do_csynch := true; ciomodule_component_inclusion.copy_sac(cmcn.irid, mcnpl); dont_check_mcn_unique := save_dont_check_mcn_unique; rm.do_csynch := save_rm_do_csynch; end if; -- Store list of old and new mcn irids mcn_count := mcn_count + 1; mcn_list(mcn_count).old_id := cmcn.irid; mcn_list(mcn_count).new_id := mcnpl.v.irid; -- Nullify copy property list mcnpl := mcnpl_null; END LOOP; -- Now copy all MCLs for each copied MCN FOR cmcn IN sel_mcn LOOP -- Copy module component links FOR cmcl IN sel_mcl(cmcn.irid) LOOP if (cmcl.module_component_link_type = 'KBL') then rmdbg.trace('Copy existing Key Based Link: '||to_char(cmcl.irid)); kblpl.v.mcn_reference_to := cmcl.mcn_to; kblpl.i.mcn_reference_to := true; rmdbg.trace('MCN ref to : '||to_char(kblpl.v.mcn_reference_to)); kblpl.v.mcn_reference_from := cmcl.mcn_from; kblpl.i.mcn_reference_from := true; rmdbg.trace('MCN ref from : '||to_char(kblpl.v.mcn_reference_from)); -- Find new irid of MCNS if applicable FOR i in 1..mcn_list.last loop rmdbg.trace('Contents of MCN list OLD: '||to_char(mcn_list(i).old_id)); rmdbg.trace('Contents of MCN list NEW: '||to_char(mcn_list(i).new_id)); -- reset mcn_reference_to IF it should now point at a new MCN if (mcn_list(i).old_id = cmcl.mcn_to) then kblpl.v.mcn_reference_to := mcn_list(i).new_id; kblpl.i.mcn_reference_to := true; end if; -- reset mcn_reference_from IF it should now point at a new MCN if (mcn_list(i).old_id = cmcl.mcn_from) then kblpl.v.mcn_reference_from := mcn_list(i).new_id; kblpl.i.mcn_reference_from := true; end if; END LOOP; -- Find the irid of the corresponding copied 'from' MTI SELECT mti.irid INTO kblpl.v.table_usage_reference_from FROM sdd_mti mti , sdd_mcn mcn WHERE mti.alias = cmcl.from_alias AND mcn.irid = kblpl.v.mcn_reference_from AND mti.module_unit_ref = NVL(mcn.module_component_ref, mcn.subcomponent_ref); kblpl.i.table_usage_reference_from := true; -- Find the irid of the corresponding copied 'to' MTI SELECT mti.irid INTO kblpl.v.table_usage_reference_to FROM sdd_mti mti , sdd_mcn mcn WHERE mti.alias = cmcl.to_alias AND mcn.irid = kblpl.v.mcn_reference_to AND mti.module_unit_ref = NVL(mcn.module_component_ref, mcn.subcomponent_ref); kblpl.i.table_usage_reference_to := true; rmdbg.trace('Do actual copy of existing KBL : ' ||to_char(cmcl.irid)); ciokey_based_link.copy_sac(cmcl.irid, kblpl); -- Delete old key based link rmdbg.trace('Delete existing KBL : ' ||to_char(cmcl.irid)); ciokey_based_link.del(cmcl.irid); -- Nullify copy property list kblpl := kblpl_null; elsif (cmcl.module_component_link_type = 'CUL') then rmdbg.trace('Copy existing Custom Link: '||to_char(cmcl.irid)); -- Find new irid of MCNS if applicable FOR i in 1..mcn_list.last loop -- reset mcn_reference_to IF it should now point at a new MCN if (mcn_list(i).old_id = cmcl.mcn_to) then culpl.v.mcn_reference_to := mcn_list(i).new_id; culpl.i.mcn_reference_to := true; end if; -- reset mcn_reference_from IF it should now point at a new MCN if (mcn_list(i).old_id = cmcl.mcn_from) then culpl.v.mcn_reference_from := mcn_list(i).new_id; culpl.i.mcn_reference_from := true; end if; END LOOP; rmdbg.trace('Do actual copy of existing CUL : ' ||to_char(cmcl.irid)); ciocustom_link.copy_sac(cmcl.irid, culpl); -- Delete old custom link rmdbg.trace('Delete existing CUL : ' ||to_char(cmcl.irid)); ciocustom_link.del(cmcl.irid); -- Nullify copy property list culpl := culpl_null; end if; END LOOP; END LOOP; -- Adjust any LOV bindings to new items in RMC rmdbg.trace('Adjust LOV inclusions if they exist'); FOR ite IN sel_item LOOP if ite.parent_type = 'SMC' then rmdbg.trace('Update SMC-LOV inclusions for item: '||to_char(ite.irid)); UPDATE sdd_lvi lovi SET lovi.item_ref = ( SELECT it.irid FROM sdd_ite it WHERE it.name = ite.name AND it.module_unit_ref = rmcpl.v.irid) WHERE lovi.item_ref = ite.irid; rmdbg.trace('Updated '||to_char(sql%rowcount)||' SMC-LOV inclusions for item: '||to_char(ite.irid)); elsif ite.parent_type = 'SQS' then rmdbg.trace('Update SQS-LOV inclusions for item: '||to_char(ite.irid)); UPDATE sdd_lvi lovi SET lovi.item_ref = ( SELECT it.irid FROM sdd_ite it , sdd_sqs sq WHERE it.name = ite.name AND it.sql_query_set_ref = sq.irid AND sq.name = ite.parent_name AND sq.module_component_ref = rmcpl.v.irid) WHERE lovi.item_ref = ite.irid; rmdbg.trace('Updated '||to_char(sql%rowcount)||' SQS-LOV inclusions for item: '||to_char(ite.irid)); elsif ite.parent_type = 'SUB' then rmdbg.trace('Update SUB-LOV inclusions for item: '||to_char(ite.irid)); UPDATE sdd_lvi lovi SET lovi.item_ref = ( SELECT it.irid FROM sdd_ite it , ci_subcomponents sb WHERE it.name = ite.name AND it.module_unit_ref = sb.id AND sb.name = ite.parent_name AND sb.module_component_reference = rmcpl.v.irid) WHERE lovi.item_ref = ite.irid; rmdbg.trace('Updated '||to_char(sql%rowcount)||' SUB-LOV inclusions for item: '||to_char(ite.irid)); end if; END LOOP; -- Delete the original specific module component -- Use force delete because MCNs may have blocking references jr_wastebasket.force_delete(pl.v.ivid, 'SMC', 'CI'); if rm.do_postop then rmu.postop('ci','REUSABLE_MCO','make_private_copy'); end if; exception when others then rmdbg.trace('In when others, sqlcode : '||sqlcode||' Message : '||sqlerrm); rmmes.post(ciospecific_mco.operation_failed_fac,ciospecific_mco.operation_failed_code, 'make_reusable_copy','SPECIFIC_MCO'); raise; end make_reusable_copy; --============================= MAKE PRIVATE COPY LOV ================================-- procedure make_private_copy_lov(i_irid in number ,slvpl in out cioSPECIFIC_LOV.data ,plvipl in out ciolov_inclusion.data ,is_lvi_context in varchar2 default 'N' ,deep in varchar2 default 'Y') is /* Select the LVI for the RLV to convert */ CURSOR sel_lvi IS select lvi.irid , lvi.ivid , lvi.usage_sequence from sdd_lvi lvi where lvi.general_module_ref = slvpl.v.general_module_reference and lvi.list_of_values_ref = i_irid; /* Select all the ITEs (DBI or UBI) within the RLV to convert */ /* Select all the ITEs (DBI or UBI) in a set operation with the RLV to convert */ CURSOR sel_item IS select ite.name , ite.irid , 'RLV' parent_type , NULL parent_name from sdd_ite ite where ite.module_unit_ref = i_irid UNION ALL select ite.name , ite.irid , 'SQS' parent_type , sqs.name parent_name from sdd_ite ite , sdd_sqs sqs where ite.sql_query_set_ref = sqs.irid and sqs.module_component_ref = i_irid; pl cioreusable_lov.data; lvipl ciolov_inclusion.data; lvipl_null ciolov_inclusion.data; context_lvi_id number; rmc_lvi boolean := false; begin if rm.do_preop then rmu.preop('ci','REUSABLE_LOV','make_private_copy_lov'); end if; if i_irid IS NULL or slvpl.v.general_module_reference IS NULL then -- ****** do need a new better error message here rmmes.post(cioreusable_lov.invalid_ref_fac,cioreusable_lov.invalid_ref_code, 'REUSABLE_LOV',NVL(make_private_copy_lov.i_irid, slvpl.v.general_module_reference)); end if; pl.v.id := i_irid; rmdbg.trace('Selecting RLV details: '||to_char(i_irid)); cioreusable_lov.sel(i_irid, pl); rmdbg.trace('Copying RLV details to SLV'); -- Copy all RLV properties to SLV if not slvpl.i.display_title then slvpl.v.display_title := pl.v.display_title; if slvpl.v.display_title is null then slvpl.i.display_title := false; else slvpl.i.display_title := true; end if; end if; slvpl.i.general_module_reference := true; if not slvpl.i.help_context_id_prefix then slvpl.v.help_context_id_prefix := pl.v.help_context_id_prefix; if slvpl.v.help_context_id_prefix is null then slvpl.i.help_context_id_prefix := false; else slvpl.i.help_context_id_prefix := true; end if; end if; if slvpl.v.id is null then slvpl.i.id := false; else slvpl.i.id := true; end if; if not slvpl.i.implementation_item then slvpl.v.implementation_item := pl.v.implementation_item; if slvpl.v.implementation_item is null then slvpl.i.implementation_item := false; else slvpl.i.implementation_item := true; end if; end if; if slvpl.v.irid is null then slvpl.i.irid := false; else slvpl.i.irid := true; end if; if slvpl.v.ivid is null then slvpl.i.ivid := false; else slvpl.i.ivid := true; end if; if not slvpl.i.layout_style then slvpl.v.layout_style := pl.v.layout_style; if slvpl.v.layout_style is null then slvpl.i.layout_style := false; else slvpl.i.layout_style := true; end if; end if; if not slvpl.i.name then slvpl.v.name := pl.v.name; if slvpl.v.name is null then slvpl.i.name := false; else slvpl.i.name := true; end if; end if; slvpl.v.pac_reference := null; slvpl.i.pac_reference := false; slvpl.v.parent_ivid := null; slvpl.i.parent_ivid := false; if not slvpl.i.req_page_height then slvpl.v.req_page_height := pl.v.req_page_height; if slvpl.v.req_page_height is null then slvpl.i.req_page_height := false; else slvpl.i.req_page_height := true; end if; end if; if not slvpl.i.req_page_width then slvpl.v.req_page_width := pl.v.req_page_width; if slvpl.v.req_page_width is null then slvpl.i.req_page_width := false; else slvpl.i.req_page_width := true; end if; end if; if not slvpl.i.rows_displayed then slvpl.v.rows_displayed := pl.v.rows_displayed; if slvpl.v.rows_displayed is null then slvpl.i.rows_displayed := false; else slvpl.i.rows_displayed := true; end if; end if; if not slvpl.i.short_description then slvpl.v.short_description := pl.v.short_description; if slvpl.v.short_description is null then slvpl.i.short_description := false; else slvpl.i.short_description := true; end if; end if; slvpl.v.types := null; slvpl.i.types := false; slvpl.v.user_defined_property_0 := null; slvpl.i.user_defined_property_0 := false; slvpl.v.user_defined_property_1 := null; slvpl.i.user_defined_property_1 := false; slvpl.v.user_defined_property_2 := null; slvpl.i.user_defined_property_2 := false; slvpl.v.user_defined_property_3 := null; slvpl.i.user_defined_property_3 := false; slvpl.v.user_defined_property_4 := null; slvpl.i.user_defined_property_4 := false; slvpl.v.user_defined_property_5 := null; slvpl.i.user_defined_property_5 := false; slvpl.v.user_defined_property_6 := null; slvpl.i.user_defined_property_6 := false; slvpl.v.user_defined_property_7 := null; slvpl.i.user_defined_property_7 := false; slvpl.v.user_defined_property_8 := null; slvpl.i.user_defined_property_8 := false; slvpl.v.user_defined_property_9 := null; slvpl.i.user_defined_property_9 := false; slvpl.v.user_defined_property_10 := null; slvpl.i.user_defined_property_10 := false; slvpl.v.user_defined_property_11 := null; slvpl.i.user_defined_property_11 := false; slvpl.v.user_defined_property_12 := null; slvpl.i.user_defined_property_12 := false; slvpl.v.user_defined_property_13 := null; slvpl.i.user_defined_property_13 := false; slvpl.v.user_defined_property_14 := null; slvpl.i.user_defined_property_14 := false; slvpl.v.user_defined_property_15 := null; slvpl.i.user_defined_property_15 := false; slvpl.v.user_defined_property_16 := null; slvpl.i.user_defined_property_16 := false; slvpl.v.user_defined_property_17 := null; slvpl.i.user_defined_property_17 := false; slvpl.v.user_defined_property_18 := null; slvpl.i.user_defined_property_18 := false; slvpl.v.user_defined_property_19 := null; slvpl.i.user_defined_property_19 := false; rmdbg.trace('Inserting new SLV'); ciospecific_lov.ins(slvpl.v.id, slvpl); if deep = 'Y' then rmdbg.trace('Deep copy SACs of RLV to SLV'); jr_copy.copy(i_irid, pl.v.ivid, slvpl.v.irid, slvpl.v.ivid, pl.v.types, true, false); end if; -- Copy the List of Values Inclusions if they exist rmdbg.trace('Copy the List of Values Inclusions if they exist'); -- If copy is in the context of a LOV inclusion then only need to copy -- this lov inclusion and not all inclusions if is_lvi_context = 'Y' then rmdbg.trace('Just copying the context LOV inclusion : '||to_char(plvipl.v.id)); rmc_lvi := true; context_lvi_id := plvipl.v.id; plvipl.v.id := null; plvipl.i.id := false; plvipl.v.irid := null; plvipl.i.irid := false; plvipl.v.ivid := null; plvipl.i.ivid := false; plvipl.v.general_module_reference := slvpl.v.general_module_reference; plvipl.i.general_module_reference := true; plvipl.v.list_of_values_reference := slvpl.v.irid; plvipl.i.list_of_values_reference := true; -- Let API default usage sequence plvipl.v.usage_sequence := null; plvipl.i.usage_sequence := false; ciolov_inclusion.copy_sac(context_lvi_id, plvipl); -- Delete old lov inclusion rmdbg.trace('Delete old lov inclusion: '||to_char(context_lvi_id)); ciolov_inclusion.del(context_lvi_id); else rmdbg.trace('Before sel_lvi loop'); FOR clvi IN sel_lvi LOOP rmdbg.trace('In sel_lvi loop, lvi id is : '||to_char(clvi.irid)); rmc_lvi := true; lvipl.v.general_module_reference := slvpl.v.general_module_reference; lvipl.i.general_module_reference := true; lvipl.v.list_of_values_reference := slvpl.v.irid; lvipl.i.list_of_values_reference := true; -- API must default usage sequences as otherwise we will have duplicates /* lvipl.v.usage_sequence := clvi.usage_sequence; */ lvipl.v.usage_sequence := null; lvipl.i.usage_sequence := false; ciolov_inclusion.copy_sac(clvi.irid, lvipl); -- Delete old lov inclusion rmdbg.trace('Delete old lov inclusion: '||to_char(clvi.irid)); ciolov_inclusion.del(clvi.irid); -- Nullify copy property list lvipl := lvipl_null; END LOOP; end if; -- Adjust any LOV bindings to new items in SLV rmdbg.trace('Adjust LOV inclusions if they exist'); FOR ite IN sel_item LOOP if ite.parent_type = 'RLV' then rmdbg.trace('Update SLV-LOV inclusions for item: '||to_char(ite.irid)); UPDATE sdd_lvi lovi SET lovi.item_ref = ( SELECT it.irid FROM sdd_ite it WHERE it.name = ite.name AND it.module_unit_ref = slvpl.v.irid) WHERE lovi.item_ref = ite.irid; rmdbg.trace('Updated '||to_char(sql%rowcount)||' SLV-LOV inclusions for item: '||to_char(ite.irid)); elsif ite.parent_type = 'SQS' then rmdbg.trace('Update SQS-LOV inclusions for item: '||to_char(ite.irid)); UPDATE sdd_lvi lovi SET lovi.item_ref = ( SELECT it.irid FROM sdd_ite it , sdd_sqs sq WHERE it.name = ite.name AND it.sql_query_set_ref = sq.irid AND sq.name = ite.parent_name AND sq.list_of_values_ref = slvpl.v.irid) WHERE lovi.item_ref = ite.irid; rmdbg.trace('Updated '||to_char(sql%rowcount)||' SQS-LOV inclusions for item: '||to_char(ite.irid)); end if; END LOOP; if rm.do_postop then rmu.postop('ci','REUSABLE_LOV','make_private_copy_lov'); end if; exception when others then rmdbg.trace('In when others, sqlcode : '||sqlcode||' Message : '||sqlerrm); rmmes.post(cioreusable_lov.operation_failed_fac,cioreusable_lov.operation_failed_code, 'make_private_copy_lov','REUSABLE_LOV'); raise; end make_private_copy_lov; --============================= MAKE REUSABLE COPY LOV ================================-- procedure make_reusable_copy_lov(i_irid in number ,rlvpl in out cioREUSABLE_LOV.data ,deep in varchar2 default 'Y') is /* Select the LVI for the SLV to convert */ CURSOR sel_lvi IS select lvi.irid , lvi.ivid , lvi.general_module_ref , lvi.usage_sequence from sdd_lvi lvi where lvi.list_of_values_ref = i_irid; /* Select all the ITEs (DBI or UBI) within the RLV to convert */ /* Select all the ITEs (DBI or UBI) in a set operation with the RLV to convert */ CURSOR sel_item IS select ite.name , ite.irid , 'SLV' parent_type , NULL parent_name from sdd_ite ite where ite.module_unit_ref = i_irid UNION ALL select ite.name , ite.irid , 'SQS' parent_type , sqs.name parent_name from sdd_ite ite , sdd_sqs sqs where ite.sql_query_set_ref = sqs.irid and sqs.module_component_ref = i_irid; pl ciospecific_lov.data; lvipl ciolov_inclusion.data; lvipl_null ciolov_inclusion.data; context_lvi_id number; begin if rm.do_preop then rmu.preop('ci','SPECIFIC_LOV','make_reusable_copy_lov'); end if; if i_irid IS NULL then -- ****** do need a new better error message here rmmes.post(ciospecific_lov.invalid_ref_fac,ciospecific_lov.invalid_ref_code, 'SPECIFIC_LOV', make_reusable_copy_lov.i_irid); end if; pl.v.id := i_irid; rmdbg.trace('Selecting SLV details: '||to_char(i_irid)); ciospecific_lov.sel(i_irid, pl); rmdbg.trace('Copying SLV details to RLV'); -- Copy all SLV properties to RLV if not rlvpl.i.display_title then rlvpl.v.display_title := pl.v.display_title; if rlvpl.v.display_title is null then rlvpl.i.display_title := false; else rlvpl.i.display_title := true; end if; end if; if not rlvpl.i.help_context_id_prefix then rlvpl.v.help_context_id_prefix := pl.v.help_context_id_prefix; if rlvpl.v.help_context_id_prefix is null then rlvpl.i.help_context_id_prefix := false; else rlvpl.i.help_context_id_prefix := true; end if; end if; if rlvpl.v.id is null then rlvpl.i.id := false; else rlvpl.i.id := true; end if; if not rlvpl.i.implementation_item then rlvpl.v.implementation_item := pl.v.implementation_item; if rlvpl.v.implementation_item is null then rlvpl.i.implementation_item := false; else rlvpl.i.implementation_item := true; end if; end if; if rlvpl.v.irid is null then rlvpl.i.irid := false; else rlvpl.i.irid := true; end if; if rlvpl.v.ivid is null then rlvpl.i.ivid := false; else rlvpl.i.ivid := true; end if; if not rlvpl.i.layout_style then rlvpl.v.layout_style := pl.v.layout_style; if rlvpl.v.layout_style is null then rlvpl.i.layout_style := false; else rlvpl.i.layout_style := true; end if; end if; if not rlvpl.i.name then rlvpl.v.name := pl.v.name; if rlvpl.v.name is null then rlvpl.i.name := false; else rlvpl.i.name := true; end if; end if; rlvpl.v.pac_reference := null; rlvpl.i.pac_reference := false; rlvpl.v.parent_ivid := null; rlvpl.i.parent_ivid := false; if not rlvpl.i.req_page_height then rlvpl.v.req_page_height := pl.v.req_page_height; if rlvpl.v.req_page_height is null then rlvpl.i.req_page_height := false; else rlvpl.i.req_page_height := true; end if; end if; if not rlvpl.i.req_page_width then rlvpl.v.req_page_width := pl.v.req_page_width; if rlvpl.v.req_page_width is null then rlvpl.i.req_page_width := false; else rlvpl.i.req_page_width := true; end if; end if; if not rlvpl.i.rows_displayed then rlvpl.v.rows_displayed := pl.v.rows_displayed; if rlvpl.v.rows_displayed is null then rlvpl.i.rows_displayed := false; else rlvpl.i.rows_displayed := true; end if; end if; if not rlvpl.i.short_description then rlvpl.v.short_description := pl.v.short_description; if rlvpl.v.short_description is null then rlvpl.i.short_description := false; else rlvpl.i.short_description := true; end if; end if; rlvpl.v.types := null; rlvpl.i.types := false; rlvpl.v.user_defined_property_0 := null; rlvpl.i.user_defined_property_0 := false; rlvpl.v.user_defined_property_1 := null; rlvpl.i.user_defined_property_1 := false; rlvpl.v.user_defined_property_2 := null; rlvpl.i.user_defined_property_2 := false; rlvpl.v.user_defined_property_3 := null; rlvpl.i.user_defined_property_3 := false; rlvpl.v.user_defined_property_4 := null; rlvpl.i.user_defined_property_4 := false; rlvpl.v.user_defined_property_5 := null; rlvpl.i.user_defined_property_5 := false; rlvpl.v.user_defined_property_6 := null; rlvpl.i.user_defined_property_6 := false; rlvpl.v.user_defined_property_7 := null; rlvpl.i.user_defined_property_7 := false; rlvpl.v.user_defined_property_8 := null; rlvpl.i.user_defined_property_8 := false; rlvpl.v.user_defined_property_9 := null; rlvpl.i.user_defined_property_9 := false; rlvpl.v.user_defined_property_10 := null; rlvpl.i.user_defined_property_10 := false; rlvpl.v.user_defined_property_11 := null; rlvpl.i.user_defined_property_11 := false; rlvpl.v.user_defined_property_12 := null; rlvpl.i.user_defined_property_12 := false; rlvpl.v.user_defined_property_13 := null; rlvpl.i.user_defined_property_13 := false; rlvpl.v.user_defined_property_14 := null; rlvpl.i.user_defined_property_14 := false; rlvpl.v.user_defined_property_15 := null; rlvpl.i.user_defined_property_15 := false; rlvpl.v.user_defined_property_16 := null; rlvpl.i.user_defined_property_16 := false; rlvpl.v.user_defined_property_17 := null; rlvpl.i.user_defined_property_17 := false; rlvpl.v.user_defined_property_18 := null; rlvpl.i.user_defined_property_18 := false; rlvpl.v.user_defined_property_19 := null; rlvpl.i.user_defined_property_19 := false; rmdbg.trace('Inserting new RLV'); cioreusable_lov.ins(rlvpl.v.id, rlvpl); if deep = 'Y' then rmdbg.trace('Deep copy SACs of SLV to RLV'); jr_copy.copy(i_irid, pl.v.ivid, rlvpl.v.irid, rlvpl.v.ivid, pl.v.types, true, false); end if; -- Copy the List of Values Inclusions at least one must exist rmdbg.trace('Copy the List of Values Inclusions'); rmdbg.trace('Before sel_lvi loop'); FOR clvi IN sel_lvi LOOP rmdbg.trace('In sel_lvi loop, lvi id is : '||to_char(clvi.irid)); lvipl.v.general_module_reference := clvi.general_module_ref; lvipl.i.general_module_reference := true; rmdbg.trace('Setting general mod ref to : '||to_char(clvi.general_module_ref)); lvipl.v.list_of_values_reference := rlvpl.v.irid; lvipl.i.list_of_values_reference := true; rmdbg.trace('Setting LOV ref to : '||to_char(rlvpl.v.irid)); -- API must default usage sequences as otherwise we will have duplicates lvipl.v.usage_sequence := null; lvipl.i.usage_sequence := false; ciolov_inclusion.copy_sac(clvi.irid, lvipl); -- Nullify copy property list lvipl := lvipl_null; END LOOP; -- Adjust any LOV bindings to new items in RLV rmdbg.trace('Adjust LOV inclusions if they exist'); FOR ite IN sel_item LOOP if ite.parent_type = 'SLV' then rmdbg.trace('Update RLV-LOV inclusions for item: '||to_char(ite.irid)); UPDATE sdd_lvi lovi SET lovi.item_ref = ( SELECT it.irid FROM sdd_ite it WHERE it.name = ite.name AND it.module_unit_ref = rlvpl.v.irid) WHERE lovi.item_ref = ite.irid; rmdbg.trace('Updated '||to_char(sql%rowcount)||' RLV-LOV inclusions for item: '||to_char(ite.irid)); elsif ite.parent_type = 'SQS' then rmdbg.trace('Update SQS-LOV inclusions for item: '||to_char(ite.irid)); UPDATE sdd_lvi lovi SET lovi.item_ref = ( SELECT it.irid FROM sdd_ite it , sdd_sqs sq WHERE it.name = ite.name AND it.sql_query_set_ref = sq.irid AND sq.name = ite.parent_name AND sq.list_of_values_ref = rlvpl.v.irid) WHERE lovi.item_ref = ite.irid; rmdbg.trace('Updated '||to_char(sql%rowcount)||' SQS-LOV inclusions for item: '||to_char(ite.irid)); end if; END LOOP; -- Delete original specific lov, this will cascade delete the inclusions rmdbg.trace('Delete original specific lov: '||to_char(i_irid)); ciospecific_lov.del(i_irid); if rm.do_postop then rmu.postop('ci','SPECIFIC_LOV','make_reusable_copy_lov'); end if; exception when others then rmdbg.trace('In when others, sqlcode : '||sqlcode||' Message : '||sqlerrm); rmmes.post(ciospecific_lov.operation_failed_fac,ciospecific_lov.operation_failed_code, 'make_reusable_copy_lov','SPECIFIC_LOV'); raise; end make_reusable_copy_lov; PROCEDURE check_cihmune_unique(conid rm.reference) is cursor get_mun_info(constraint_id number, cdapi_activity number) is select my_mun.irid ,my_mun.name ,my_mun.module_unit_subtype from ci_module_units my_mun ,rm_deferred_checks dc where dc.element = my_mun.irid and dc.assertion = constraint_id and dc.activity = cdapi_activity; clash_irid number; clash_folder_reference number; begin rmman.record_check(conid,null,null,null,true); for el in get_mun_info(conid, cdapi.activity) loop if not cimappcon.is_mun_unique(el.irid, el.module_unit_subtype, el.name, clash_irid, clash_folder_reference) then rmman.record_check(conid,el.irid,null,cdapi.activity,false,'Y', ciiutl.identify(el.irid, 'MUN'), ciiutl.identify(clash_irid, 'MUN'), ciiutl.identify(clash_folder_reference, 'CEL')); end if; end loop; end check_cihmune_unique; --================================== END ====================================-- -- -- Package instantiation block -- BEGIN is_installed := true; END; /