rem rem $Header: L:\\\\model\\repman40\\api\\RCS\\CIFUN.HPB 1.7 1998/11/26 11:24:36 loldham Exp $ rem Rem Copyright (c) 1993 by Oracle Corporation Rem NAME Rem cihfun.pkb - Rem DESCRIPTION Rem Rem RETURNS Rem Rem NOTES Rem Rem MODIFIED (MM/DD/YY) Rem stlyon 06/05/04 - B1513741 - maintain common function read-only properties in set with master function Rem stlyon 28/07/03 - B2989788 - change to query in assign_derived_values() Rem stlyon 04/02/03 - B1787721 - correct acc_type argument passed to ciiacc.privileged() Rem loldham 26/11/98 - Bug 758981 - CANNOT CREATE SUB FUNCTIONS USING API Rem loldham 29/09/98 - Bug 700174 - CANNOT INSERT FUNCTIONS USING API VIA LOADER Rem aheath 03/14/95 - Modified calls for common function checks Rem jwetherb 01/16/95 - Add more calls to log_change() Rem jwetherb 01/11/95 - Add UE processing Rem jwetherb 01/11/95 - Disable update of ineligible properties when Rem function is a copy Rem jwetherb 10/21/94 - Maintain denormalized SHORT_DEF. for COMFUN Rem jwetherb 10/14/94 - Move check_delete() to pre_process() Rem jwetherb 10/14/94 - Correct COMFUN creation Rem jwetherb 10/06/94 - Correct ATOMIC_FLAG assignment in update_parent Rem jwetherb 09/06/94 - Add update_parent() procedure Rem jwetherb 08/25/94 - don't clear short_definition when nullifying Rem common_function_reference Rem jwetherb 07/27/94 - check UID when function_label is updated Rem aheath 07/08/94 - Creation Rem jwetherb 08/09/93 - Creation CREATE OR REPLACE PACKAGE BODY cihfun IS -- Generic pre-process routines PROCEDURE assign_derived_values(pl in out NOCOPY ciofunction.data); -- Type-specific pre-process routines -- B1787721 : add i_operation parameter PROCEDURE extra_access_checks( i_operation varchar2, pl in out NOCOPY ciofunction.data ); -- Type-specific post-process routines PROCEDURE check_delete(func_reference rm.reference); --PROCEDURE update_parent(old_parent number,new_parent number); pac_type varchar2(3) := 'FUN'; old_parent_reference number; old_common_function_reference number; --======================== PRE-PROCESS (INS,UPD) ============================-- PROCEDURE pre_process(operation varchar2,id rm.reference, pl in out NOCOPY ciofunction.data) IS common_app_sys_ref rm.reference; pm ciofunction.data; -- B1513741 : Added BEGIN -- Perform extra access control checks extra_access_checks(operation, pl); -- B1787721 : add operation parameter -- access control checks -- Access rights checking performed by triggers in config 4.0.9 -- ciiacc.check_pac(pl.v.id,pac_type,operation); -- Validate user-defined properties if cieval.is_extended then cieval.check_element(id,pl.v.types,(operation='UPD') ,pl.v.user_defined_property_0,pl.i.user_defined_property_0 ,pl.v.user_defined_property_1,pl.i.user_defined_property_1 ,pl.v.user_defined_property_2,pl.i.user_defined_property_2 ,pl.v.user_defined_property_3,pl.i.user_defined_property_3 ,pl.v.user_defined_property_4,pl.i.user_defined_property_4 ,pl.v.user_defined_property_5,pl.i.user_defined_property_5 ,pl.v.user_defined_property_6,pl.i.user_defined_property_6 ,pl.v.user_defined_property_7,pl.i.user_defined_property_7 ,pl.v.user_defined_property_8,pl.i.user_defined_property_8 ,pl.v.user_defined_property_9,pl.i.user_defined_property_9 ,pl.v.user_defined_property_10,pl.i.user_defined_property_10 ,pl.v.user_defined_property_11,pl.i.user_defined_property_11 ,pl.v.user_defined_property_12,pl.i.user_defined_property_12 ,pl.v.user_defined_property_13,pl.i.user_defined_property_13 ,pl.v.user_defined_property_14,pl.i.user_defined_property_14 ,pl.v.user_defined_property_15,pl.i.user_defined_property_15 ,pl.v.user_defined_property_16,pl.i.user_defined_property_16 ,pl.v.user_defined_property_17,pl.i.user_defined_property_17 ,pl.v.user_defined_property_18,pl.i.user_defined_property_18 ,pl.v.user_defined_property_19,pl.i.user_defined_property_19 ); end if; -- Disallow operation if this is a Copy function and values for certain -- properties are provided -- (bpr_generate_flag and elementry flag removed due to 449022) -- (don't perform check if in load mode due to B369485) if not cdapi.load_mode then if pl.i.common_function_reference and pl.v.common_function_reference is not null then if pl.i.additional_cost_rate or pl.i.additional_cost_rate_unit or pl.i.competitive_index_value or pl.i.execution_string or pl.i.frequency or pl.i.frequency_unit or pl.i.function_type or pl.i.icon_file or pl.i.image_file or pl.i.measured_time_1 or pl.i.measured_time_2 or pl.i.measured_time_3 or pl.i.measured_time_unit or pl.i.overhead_cost_rate or pl.i.overhead_cost_rate_unit or pl.i.person_cost_rate or pl.i.person_cost_rate_unit or pl.i.post_delay_time or pl.i.post_delay_time_unit or pl.i.prior_delay_time or pl.i.prior_delay_time_unit or pl.i.quality_check_time or pl.i.quality_check_time_unit or pl.i.quality_percent or pl.i.resource_required_1 or pl.i.resource_required_2 or pl.i.resource_required_3 or pl.i.resource_required_4 or pl.i.response_needed or pl.i.sound_file or pl.i.total_cost_rate or pl.i.total_cost_rate_unit or pl.i.total_time or pl.i.total_time_unit or pl.i.user_defined_property_0 or pl.i.user_defined_property_1 or pl.i.user_defined_property_2 or pl.i.user_defined_property_3 or pl.i.user_defined_property_4 or pl.i.user_defined_property_5 or pl.i.user_defined_property_6 or pl.i.user_defined_property_7 or pl.i.user_defined_property_8 or pl.i.user_defined_property_9 or pl.i.user_defined_property_10 or pl.i.user_defined_property_11 or pl.i.user_defined_property_12 or pl.i.user_defined_property_13 or pl.i.user_defined_property_14 or pl.i.user_defined_property_15 or pl.i.user_defined_property_16 or pl.i.user_defined_property_17 or pl.i.user_defined_property_18 or pl.i.user_defined_property_19 or pl.i.value_added or pl.i.video_file or pl.i.work_time or pl.i.work_time_unit then ciierr.fatal(105,ciiutl.nls_type_name('FUNCTION'),pl.v.function_label); end if; end if; end if; -- Initialize unassigned properties with default values on INSERT if operation = 'INS' then pl.v.element_type_name := pac_type; pl.i.element_type_name := true; -- Retrieve old values for parent_reference and common_function_reference else select max(parent_reference),max(common_function_reference) into old_parent_reference,old_common_function_reference from ci_functions where id=pl.v.id; end if; -- Assign denormalized and derived property values assign_derived_values(pl); -- B1513741 : On INS or UPD of a common function, ensure its read-only properties are copies of -- its master function's properties -- if ( pl.v.common_function_reference is not null ) -- if there is a reference to a master function then ciofunction.sel( pl.v.common_function_reference, pm ); -- select master function's properties -- Copy master function's values for common function's read-only properties. The properties are the same -- as those in the "if not cdapi.load_mode" statement above -- pl.v.additional_cost_rate := pm.v.additional_cost_rate; pl.v.additional_cost_rate_unit := pm.v.additional_cost_rate_unit; pl.v.competitive_index_value := pm.v.competitive_index_value; pl.v.execution_string := pm.v.execution_string; pl.v.frequency := pm.v.frequency; pl.v.frequency_unit := pm.v.frequency_unit; pl.v.function_type := pm.v.function_type; pl.v.icon_file := pm.v.icon_file; pl.v.image_file := pm.v.image_file; pl.v.measured_time_1 := pm.v.measured_time_1; pl.v.measured_time_2 := pm.v.measured_time_2; pl.v.measured_time_3 := pm.v.measured_time_3; pl.v.measured_time_unit := pm.v.measured_time_unit; pl.v.overhead_cost_rate := pm.v.overhead_cost_rate; pl.v.overhead_cost_rate_unit := pm.v.overhead_cost_rate_unit; pl.v.person_cost_rate := pm.v.person_cost_rate; pl.v.person_cost_rate_unit := pm.v.person_cost_rate_unit; pl.v.post_delay_time := pm.v.post_delay_time; pl.v.post_delay_time_unit := pm.v.post_delay_time_unit; pl.v.prior_delay_time := pm.v.prior_delay_time; pl.v.prior_delay_time_unit := pm.v.prior_delay_time_unit; pl.v.quality_check_time := pm.v.quality_check_time; pl.v.quality_check_time_unit := pm.v.quality_check_time_unit; pl.v.quality_percent := pm.v.quality_percent; pl.v.resource_required_1 := pm.v.resource_required_1; pl.v.resource_required_2 := pm.v.resource_required_2; pl.v.resource_required_3 := pm.v.resource_required_3; pl.v.resource_required_4 := pm.v.resource_required_4; pl.v.response_needed := pm.v.response_needed; pl.v.sound_file := pm.v.sound_file; pl.v.total_cost_rate := pm.v.total_cost_rate; pl.v.total_cost_rate_unit := pm.v.total_cost_rate_unit; pl.v.total_time := pm.v.total_time; pl.v.total_time_unit := pm.v.total_time_unit; pl.v.user_defined_property_0 := pm.v.user_defined_property_0; pl.v.user_defined_property_1 := pm.v.user_defined_property_1; pl.v.user_defined_property_10 := pm.v.user_defined_property_10; pl.v.user_defined_property_11 := pm.v.user_defined_property_11; pl.v.user_defined_property_12 := pm.v.user_defined_property_12; pl.v.user_defined_property_13 := pm.v.user_defined_property_13; pl.v.user_defined_property_14 := pm.v.user_defined_property_14; pl.v.user_defined_property_15 := pm.v.user_defined_property_15; pl.v.user_defined_property_16 := pm.v.user_defined_property_16; pl.v.user_defined_property_17 := pm.v.user_defined_property_17; pl.v.user_defined_property_18 := pm.v.user_defined_property_18; pl.v.user_defined_property_19 := pm.v.user_defined_property_19; pl.v.user_defined_property_2 := pm.v.user_defined_property_2; pl.v.user_defined_property_3 := pm.v.user_defined_property_3; pl.v.user_defined_property_4 := pm.v.user_defined_property_4; pl.v.user_defined_property_5 := pm.v.user_defined_property_5; pl.v.user_defined_property_6 := pm.v.user_defined_property_6; pl.v.user_defined_property_7 := pm.v.user_defined_property_7; pl.v.user_defined_property_8 := pm.v.user_defined_property_8; pl.v.user_defined_property_9 := pm.v.user_defined_property_9; pl.v.value_added := pm.v.value_added; pl.v.video_file := pm.v.video_file; pl.v.work_time := pm.v.work_time; pl.v.work_time_unit := pm.v.work_time_unit; end if; END; --======================== PRE-PROCESS (DEL,SEL) ============================-- PROCEDURE pre_process(operation varchar2,id rm.reference) IS BEGIN -- access control checks -- Access rights checking performed by triggers in config 4.0.9 -- ciiacc.check_pac(id,pac_type,operation); if operation = 'DEL' then -- Perform referential integrity checks to ensure no depenedencies exist -- on this element check_delete(id); end if; -- Retrieve old values for parent_reference and common_function_reference select max(parent_reference),max(common_function_reference) into old_parent_reference,old_common_function_reference from ci_functions where id = pre_process.id; END; --===================== POST-PROCESS (INS,UPD,SEL) ==========================-- PROCEDURE post_process(operation varchar2,id rm.reference,pl ciofunction.data) IS pm ciofunction.data; -- B1513741 : Added BEGIN if operation = 'INS' then ciiutl.create_appxxx(pac_type,pl.v.id,pl.v.function_label); end if; rmdbg.trace('post_process, operation: '||operation); if operation in ('INS','UPD') then if pl.v.common_function_reference is not null then -- Set up constraint checks for invalid conditions rmman.log_change('CIHFUNE_MASTER_COPY',pl.v.id); rmman.log_change('CIHFUNE_MASTER_COPY',pl.v.common_function_reference); rmman.log_change('CIHFUNE_PARENT_COPY',pl.v.id); rmman.log_change('CIHFUNE_MAST_ANC',pl.v.id); end if; if pl.v.parent_reference is not null then rmdbg.trace('CIHFUNE_PAR_DESC'); rmman.log_change('CIHFUNE_PAR_DESC',pl.v.id); -- Set up constraint checks for invalid conditions if pl.v.parent_reference is not null then rmdbg.trace('CIHFUNE_PARENT_COPY'); rmman.log_change('CIHFUNE_PARENT_COPY',pl.v.parent_reference); if pl.v.common_function_reference is not null then rmdbg.trace('CIHFUNE_MAST_ANC'); rmman.log_change('CIHFUNE_MAST_ANC',pl.v.id); end if; end if; -- 4.0.12 config, bug 994810 property atomic_flag removed end if; end if; if operation = 'UPD' then -- Check UID 1 if function_label or sequence within -- parent function has changed if pl.i.function_label or pl.i.sequence_in_parent then cioapp_con_interface.traverse('CIHAPPCONU1',pl.v.id,'APPFUN'); end if; -- Update denormalized short_definition on common functions -- REMOVED DUE TO 1008868 --if pl.i.short_definition then -- /* Bug 792443 - can't update through ci_functions view */ -- update sdd_fun -- set short_definition = pl.v.short_definition -- where common_function_ref = pl.v.id; --end if; end if; -- B1513741 : update read-only properties of common functions linked to this master function -- if ( operation = 'UPD' and pl.v.common_function_reference is null ) -- updating a master function then for c in (select irid from sdd_fun where common_function_ref = post_process.id ) loop ciofunction.sel( c.irid, pm ); -- select current values for common function ciofunction.upd( c.irid, pm ); -- will set common function's read-only properties to -- master function's values. See PRE-PROCESS (INS,UPD) above end loop; end if; END; --========================= POST PROCESS (DEL) ==============================-- PROCEDURE post_process(operation varchar2,id rm.reference) IS BEGIN if operation = 'DEL' then -- Delete the function and all it's related sub-bits ciiutl.delete_element(id,pac_type,cdapi.app_sys_ref,'APP'); -- 4.0.12 config, bug 994810 property atomic_flag removed end if; END; --========================= EXTRA ACCESS CHECKS =============================-- -- B1787721 : add i_operation parameter PROCEDURE extra_access_checks( i_operation varchar2, pl in out NOCOPY ciofunction.data ) IS common_app_sys_ref rm.reference; func_name varchar2(100); parent_app_sys_ref rm.reference; my_app_sys_ref rm.reference; BEGIN if pl.i.common_function_reference and pl.v.common_function_reference is not null then -- Check for rights on the app sys which owns the common function select fm.folder_reference, fun.function_label into common_app_sys_ref,func_name from ci_functions fun , sdd_folder_members fm where fun.id = pl.v.common_function_reference and fun.id = fm.member_object and fm.ownership_flag = 'Y'; /* select max(application_system_owned_by),max(function_label) into common_app_sys_ref,func_name from ci_functions where id = pl.v.common_function_reference; */ if common_app_sys_ref != cdapi.app_sys_ref -- B1787721 : need to pass numeric value of acc_type(i_operation) to ciiacc.privileged -- and ciiacc.privileged(common_app_sys_ref, 'SHR') = false then and not ciiacc.privileged( common_app_sys_ref, jr_acc_rights.get_acc_type(i_operation) ) then ciierr.fatal(1107,ciiutl.identify(pl.v.id, pl.v.types),func_name); end if; end if; if pl.i.parent_reference and pl.v.parent_reference is not null then -- First check that the parent reference is valid select fm.folder_reference, fun.function_label into parent_app_sys_ref,func_name from ci_functions fun , sdd_folder_members fm where fun.id = pl.v.parent_reference and fun.id = fm.member_object and fm.ownership_flag = 'Y'; select NVL(max(fm.folder_reference), cdapi.app_sys_ref) into my_app_sys_ref from ci_functions fun , sdd_folder_members fm where fun.id = pl.v.id and fun.id = fm.member_object and fm.ownership_flag = 'Y'; if (parent_app_sys_ref is not null and parent_app_sys_ref != my_app_sys_ref) then ciierr.fatal(1108,ciiutl.identify(pl.v.id, pl.v.types),func_name); end if; end if; END; --========================== ASSIGN DERIVED VALUES ==========================-- PROCEDURE assign_derived_values(pl in out NOCOPY ciofunction.data) IS max_seq number; sequence number; BEGIN -- Re-assign the sequence_in_parent whenever the parent function changes -- or on insert, provided it isn't provided explicitly if (pl.i.parent_reference and pl.i.sequence_in_parent = false) or pl.v.sequence_in_parent is null then -- Generate the SEQUENCE_IN_PARENT if not provided if pl.v.parent_reference is null then select max(fun.sequence_in_parent)+1 into max_seq from ci_functions fun , sdd_folder_members fm where fun.parent_reference is null and fm.folder_reference = cdapi.app_sys_ref -- and fm.ownership_flag = 'Y' -- B2989788 : need to include shared functions aswell and fm.member_object = fun.id; else select max(sequence_in_parent)+1 into max_seq from ci_functions where parent_reference = pl.v.parent_reference; end if; if max_seq is null then pl.v.sequence_in_parent := 1; elsif max_seq > 999 then -- Try to find the first sequence not in use, starting with 1 sequence := 1; if pl.v.parent_reference is null then for nextfun in (select fun.sequence_in_parent from ci_functions fun , sdd_folder_members fm where fun.parent_reference is null and fm.folder_reference = cdapi.app_sys_ref and fm.ownership_flag = 'Y' and fm.member_object = fun.id order by fun.sequence_in_parent) loop if nextfun.sequence_in_parent != sequence then pl.v.sequence_in_parent := sequence; exit; else sequence := sequence + 1; end if; end loop; else for nextfun in (select sequence_in_parent from ci_functions where parent_reference = pl.v.parent_reference order by sequence_in_parent) loop if nextfun.sequence_in_parent != sequence then pl.v.sequence_in_parent := sequence; exit; else sequence := sequence + 1; end if; end loop; end if; if sequence = 999 then if pl.v.parent_reference is null then ciierr.fatal(705,ciiutl.identify(pl.v.id, pl.v.types)); else ciierr.fatal(706,ciiutl.identify(pl.v.id, pl.v.types), ciiutl.identify(pl.v.parent_reference, 'FUN')); end if; end if; else pl.v.sequence_in_parent := max_seq; end if; pl.i.sequence_in_parent := true; end if; -- Assign common function properties to NULL if a common function is provided if pl.i.common_function_reference and pl.v.common_function_reference is not null and pl.v.short_definition is null then --JW copy short_definition from the master func /** b594952 jkc 30sep98 - only copy if no short definition given **/ select max(short_definition) into pl.v.short_definition from ci_functions where id = pl.v.common_function_reference; pl.i.short_definition := true; end if; END; --============================= CHECK DELETE ================================-- PROCEDURE check_delete(func_reference rm.reference) IS mycount number; BEGIN -- 4.0.12 Config, Bug 908351 -- Following blocks have been removed from API as now handled by triggers -- Check if any event triggers are connected to me -- Check if any module functions are connected to me -- Check if any dataflows are connected to me -- Check if any child functions are connected to me -- Check if any common functions are connected to me null; END; --================================== END ====================================-- -- -- Package instantiation block -- BEGIN is_installed := true; END; /