rem rem $Header: L:\\\\model\\repman40\\api\\RCS\\cidbi.hpb 1.4 1998/12/15 14:14:03 cvanes Exp $ rem rem Copyright (c) 1996 by Oracle Corporation rem NAME rem cidbi.hpb - rem DESCRIPTION rem rem RETURNS rem rem NOTES rem rem MODIFIED (MM/DD/YY) rem cvanes 07/10/96 - Creation rem *************************************************************************** rem Hand-carved package body for DATA BOUND ITEM created on 10-JAN-96 rem *************************************************************************** CREATE OR REPLACE PACKAGE BODY cihdbi IS -- Generic post-process routines PROCEDURE check_delete(id number); FUNCTION is_name_valid (name varchar2) return boolean; sac_type constant varchar2(6) := 'ITE'; --======================== PRE-PROCESS (INS,UPD) ============================-- PROCEDURE pre_process(operation varchar2,id number, pl in out NOCOPY ciodata_bound_item.data) IS cursor get_column_props(column_id number) is select col.format_modifier display_format, col.display_height , col.prompt , col.help_text , col.order_sequence , col.sorting_order , col.highlighting , col.name, col.display_flag, col.justification, col.default_value, col.default_value_type, col.default_implementation_item, --nvl( col.default_display_type,ref.ref_parent_code) display_type, (Bug 598081) nvl(col.default_display_type,'TEXT') display_type, nvl(col.display_length,col.maximum_length) display_width, col.null_indicator -- null_indicator from ci_columns col --, ref_values ref where col.id = column_id; --and ref.ref_domain = 'DATA_TYPE' --and ref.ref_code = col.datatype; -- Will return null values if associated with a LOV or SUB cursor get_mti_props(mti_id number) is select max(mco.insert_flag) insert_flag ,max(mco.select_flag) select_flag ,max(mco.update_flag) update_flag ,max(mti.usage_type) usage_type from ci_module_detail_table_usages mti ,ci_module_components mco where mti.id = mti_id and mti.module_unit_reference = mco.id; /* -- This is not needed as if the connection is via SQS, then the situation is -- implicitly read-only - Bill UNION select mco.insert_flag, mco.select_flag, mco.update_flag, mti.usage_type from ci_module_detail_table_usages mti ,ci_module_components mco ,ci_sql_query_sets sqs where mti.id = mti_id and mti.sql_query_set_reference = sqs.id and sqs.module_component_reference = mco.id; */ pac_type varchar2(6); parent_supertype varchar2(6); pac_reference number; parent_ref number; colp get_column_props%rowtype; mtip get_mti_props%rowtype; BEGIN -- Calculate the PAC reference which determines access control rights -- DBI may be within a subcomponent or SMC or RMC if pl.v.module_unit_reference IS NOT NULL then pac_type := 'MUN'; else pac_type := 'SQS'; end if; -- Access control checks, need to use module ref for specific mcos /* select NVL(mun1.general_module_ref, mun1.irid) into parent_ref from sdd_mun mun1 where mun1.irid = (select NVL(mun2.module_component_ref, mun2.irid) from sdd_mun mun2 where mun2.irid = pl.v.module_unit_reference); parent_supertype := 'SHR'; else parent_supertype := 'SAC'; end if; pac_reference := nvl(pl.v.sql_query_set_reference, parent_ref); */ -- Access rights checking performed by triggers in config 4.0.9 -- ciiacc.check_access_rights(pac_reference, pac_type, 'UPD', parent_supertype); -- 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; -- Only do the magic transfer from UBI to DBI as Bill requested -- when in Trusted Mode 'rm.do_csynch'. if operation = 'UPD' and pl.v.item_type = 'UBI' and rm.do_csynch then pl.v.item_type := 'DBI'; pl.i.item_type := true; select id into pl.v.types from rm_element_types et where et.short_name = 'DBI'; pl.i.types := true; -- Force all Mandatory properties to be set pl.i.column_reference := true; pl.i.detail_table_usage_reference := true; -- Set the following madatory properties to their default values -- if they are not set. -- DANGER! I have taken the default values from the 2.04 model. -- If these change, this code will be out of date. if not pl.i.context_option_query_flag then pl.v.context_option_query_flag := 'N'; end if; pl.i.context_option_query_flag := true; delete from rm_text_lines where txt_ref = pl.v.id and txt_type = 'CDEXPR' ; update ci_unbound_items set domain_reference = null ,reset_group = null ,reset_level = null ,unbound_type = null ,user_defined_property_0 = null ,user_defined_property_1 = null ,user_defined_property_2 = null ,user_defined_property_3 = null ,user_defined_property_4 = null ,user_defined_property_5 = null ,user_defined_property_6 = null ,user_defined_property_7 = null ,user_defined_property_8 = null ,user_defined_property_9 = null ,user_defined_property_10 = null ,user_defined_property_11 = null ,user_defined_property_12 = null ,user_defined_property_13 = null ,user_defined_property_14 = null ,user_defined_property_15 = null ,user_defined_property_16 = null ,user_defined_property_17 = null ,user_defined_property_18 = null ,user_defined_property_19 = null where id = pl.v.id ; end if; --============================================================================== /* The follwoing section of code has been added to fix bug #653289. It defaults the values of those flag type properties which can be set to a value of NULL for an update. Leaving them as NULL causes error #1301 'Property must not be NULL'. If they are NULL then they defaulted to values using the same logic as the INSERT defaulting code which follows this section. */ if operation = 'UPD' and pl.v.item_type ~= 'UBI'then open get_column_props(pl.v.column_reference); fetch get_column_props into colp; close get_column_props; open get_mti_props(pl.v.detail_table_usage_reference); fetch get_mti_props into mtip; close get_mti_props; if pl.i.display_flag = true and pl.v.display_flag IS NULL then pl.v.display_flag := colp.display_flag; end if; if pl.i.select_flag = true and pl.v.select_flag IS NULL then pl.v.select_flag := mtip.select_flag; end if; -- B786020 add 'and pl.v.nullify_flag is null' if pl.i.nullify_flag = true and pl.v.nullify_flag is null then if colp.null_indicator = 'NULL' and (mtip.insert_flag = 'Y' or mtip.update_flag = 'Y') then pl.v.nullify_flag := 'Y'; else pl.v.nullify_flag := 'N'; end if; end if; if pl.i.update_flag = true and pl.v.update_flag IS NULL then if mtip.usage_type = 'BASE' and mtip.update_flag = 'Y' and cihfco.is_column_updateable(pl.v.column_reference) and cihpco.is_column_updateable(pl.v.column_reference) and cihuco.is_column_updateable(pl.v.column_reference) then pl.v.update_flag := 'Y'; else pl.v.update_flag := 'N'; --B660195 end if; end if; if pl.i.insert_flag = true and pl.v.insert_flag IS NULL then if mtip.usage_type = 'BASE' and mtip.insert_flag = 'Y' or (mtip.update_flag = 'Y' and pl.v.update_flag = 'Y') then pl.v.insert_flag := 'Y'; else pl.v.insert_flag := 'N'; --B660195 end if; end if; end if; --End of section for bug #653289 --============================================================================== -- Initialize unassigned properties with default values on INSERT if operation = 'INS' then pl.v.element_type_name := sac_type; pl.i.element_type_name := true; pl.v.item_type := 'DBI'; pl.i.item_type := true; pl.v.item_for := pac_type; pl.i.item_for := true; /* if pl.v.cqa_reference is not null then pl.v.data_bound_item_for := 'CQA'; else pl.v.data_bound_item_for := 'COL'; end if; pl.i.data_bound_item_for := true; */ -- Default values -- The default values are derived from properties of the referenced column -- and the referenced module detail table usage if not cdapi.load_mode then -- B560744 (Don't derive values if in loader mode) open get_column_props(pl.v.column_reference); fetch get_column_props into colp; close get_column_props; open get_mti_props(pl.v.detail_table_usage_reference); fetch get_mti_props into mtip; close get_mti_props; if pl.i.display_format = false then pl.v.display_format := colp.display_format; pl.i.display_format := true; end if; if pl.i.display_height = false then pl.v.display_height := colp.display_height; pl.i.display_height := true; end if; if pl.i.prompt = false then pl.v.prompt := colp.prompt; pl.i.prompt := true; end if; if pl.i.hint_text = false then pl.v.hint_text := SUBSTRB(colp.help_text,0,132); --B648245 pl.i.hint_text := true; end if; if pl.i.order_sequence = false and mtip.usage_type = 'BASE' then pl.v.order_sequence := colp.order_sequence; pl.i.order_sequence := true; end if; if pl.i.usage_sequence = false then select nvl(max(usage_sequence),0) + 100 into pl.v.usage_sequence from ci_items ite where ite.module_unit_reference = pac_reference; pl.i.usage_sequence := true; end if; if pl.i.highlighting = false then pl.v.highlighting := colp.highlighting; pl.i.highlighting := true; end if; if pl.i.name = false then pl.v.name := TRANSLATE(colp.name,'$#','__'); --b342290 pl.i.name := true; end if; if pl.i.display_flag = false then pl.v.display_flag := colp.display_flag; pl.i.display_flag := true; end if; if pl.i.justification = false then pl.v.justification := colp.justification; pl.i.justification := true; end if; if pl.i.select_flag = false then pl.v.select_flag := mtip.select_flag; pl.i.select_flag := true; end if; if mtip.usage_type = 'BASE' then if pl.i.sorting_order = false then pl.v.sorting_order := colp.sorting_order; pl.i.sorting_order := true; end if; if pl.i.default_value = false then pl.v.default_value := colp.default_value; pl.i.default_value := true; /**** Bug 1382297 :: keep type in step with value. Can't rely on Bug 863188 fix when this procedure **** is called from ciodata_bound_item.ins() as it calls ciodata_bound_item.maintain() before this **** procedure which sets pl.v.default_value_type = 'LITERAL', and pl.i.default_value_type := true. **** Not sure if Bug 863188 fix is required. ***/ if colp.default_value_type is not null -- check fetch returned a row then pl.v.default_value_type := colp.default_value_type; pl.i.default_value_type := true; end if; end if; -- Bug 863188, copy default_value_type from column if not set by user if pl.i.default_value_type = false then pl.v.default_value_type := colp.default_value_type; pl.i.default_value_type := true; end if; end if; if pl.i.implementation_item = false then pl.v.implementation_item := colp.default_implementation_item; --B717519 pl.i.implementation_item := true; end if; if pl.i.display_type = false then pl.v.display_type := colp.display_type; pl.i.display_type := true; end if; if pl.i.display_width = false then pl.v.display_width := colp.display_width; pl.i.display_width := true; end if; if pl.i.nullify_flag = false then if colp.null_indicator = 'NULL' and (mtip.insert_flag = 'Y' or mtip.update_flag = 'Y') then pl.v.nullify_flag := 'Y'; else pl.v.nullify_flag := 'N'; end if; pl.i.nullify_flag := true; end if; if pl.i.update_flag = false then if mtip.usage_type = 'BASE' and mtip.update_flag = 'Y' and cihfco.is_column_updateable(pl.v.column_reference) and cihpco.is_column_updateable(pl.v.column_reference) and cihuco.is_column_updateable(pl.v.column_reference) then pl.v.update_flag := 'Y'; else pl.v.update_flag := 'N'; --B660195 end if; pl.i.update_flag := true; end if; if pl.i.insert_flag = false then if mtip.usage_type = 'BASE' and mtip.insert_flag = 'Y' or (mtip.update_flag = 'Y' and pl.v.update_flag = 'Y') then pl.v.insert_flag := 'Y'; else pl.v.insert_flag := 'N'; --B660195 end if; pl.i.insert_flag := true; end if; end if; end if; END; --======================== PRE-PROCESS (DEL,SEL) ============================-- PROCEDURE pre_process(operation varchar2,id number) IS BEGIN -- Access rights for data bound item derive from the module -- Access rights checking performed by triggers in config 4.0.9 -- ciiacc.check_access_rights(id,sac_type,operation,'SAC'); -- if operation is 'DELETE', make sure no non-cascading references exist if operation = 'DEL' then check_delete(id); end if; END; --===================== POST-PROCESS (INS,UPD,SEL) ==========================-- PROCEDURE post_process(operation varchar2,id number, pl ciodata_bound_item.data) IS BEGIN if operation = 'INS' or (operation = 'UPD' and pl.i.name = true) then -- Need to do a UID check on constarints rmman.log_change('CIHDBIU1',pl.v.id); end if; if pl.i.name then -- check name is a valid ORACLE name ER#342290 if is_name_valid(pl.v.name) then ciierr.fatal(1003,ciiutl.identify(pl.v.id, pl.v.types),pl.v.name); end if; end if; END; --========================= POST PROCESS (DEL) ==============================-- PROCEDURE post_process(operation varchar2,id number) IS BEGIN if operation = 'DEL' then -- Delete the data bound item and all it's related sub-bits ciiutl.delete_element(id,sac_type); end if; END; --============================= CHECK DELETE ================================-- PROCEDURE check_delete(id number) IS dummy number; error_count number := 0; BEGIN null; END; --============================================================================== -- This function has been added to satisfy Enhancement Request #342290 and is almost -- exactly the same as cihtbl.is_name_valid except for different allowable characters FUNCTION is_name_valid (name varchar2) return boolean IS dummy number; language varchar2(30); BEGIN -- Check that name is enclosed in quotes if substr(name,1,1) = '"' and substr(name,(length(name)),1) = '"' then return false; end if; select count(*) into dummy from ref_values where ref_domain = 'RESERVED_NAMES' and ref_code = upper(name); if dummy > 0 then return true; end if; -- Removed due to Bug 1308557 NLS: MULTIBYTE CHAR IS REFUSED WHILE COMMITING IN CREATING TABLE DEFINITIONS -- --The following checks can only be made when the National Language is American -- select value into language from nls_session_parameters -- where parameter = 'NLS_LANGUAGE'; -- if language = 'AMERICAN' then -- --Check that name does not contain illegal characters -- if (length(translate (upper(name), '*ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_','*'))) > 0 then -- return true; -- end if; -- -- Check name begins with a letter -- if ((substr(upper(name),1,1)) not in ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')) then -- return true; -- end if; -- end if; return false; end; --End of Function added for ER#342290 --=============================================================================== --================================== END ====================================-- -- -- Package instantiation block -- BEGIN is_installed := true; END; /