Rem Copyright (c) 1993 by Oracle Corporation Rem NAME Rem cihrelend.pkb - Rem DESCRIPTION Rem Rem RETURNS Rem Rem NOTES Rem Rem MODIFIED (MM/DD/YY) Rem stlyon 11/07/03 - B1286628 : Pass capitalized element name to nls_type_plural_name() instead of plural name Rem jwetherb 01/10/94 - Reset bogus_arc_ref_flag to false each pass Rem jwetherb -9/21/94 - Allow update of FROM_ENTITY_REFERENCE Rem aheath 07/08/94 - Creation Rem jwetherb 08/09/93 - Creation rem rem $Header: L:\\\\model\\repman40\\api\\RCS\\cirelend.hpb 1.2 1998/10/09 13:26:15 jkchow Exp $ rem CREATE OR REPLACE PACKAGE BODY cihrelend AS -- Generic pre-process routines PROCEDURE assign_insert_defaults(pl in out NOCOPY ciorelationship_end.data); -- Type-specific pre-process routines PROCEDURE check_delete(id rm.reference); old_from_entity_reference number; old_name varchar2(100); old_arc_number number; relend2 boolean := false; bogus_arc_ref_flag boolean := false; pac_type varchar2(6) := 'RELEND'; --======================== PRE-PROCESS (INS,UPD) ============================-- PROCEDURE pre_process(operation varchar2,id rm.reference, pl in out NOCOPY ciorelationship_end.data) IS bogus_arc_ref number; BEGIN -- access control checks (hack, sort of) -- Access rights checking performed by triggers in config 4.0.9 -- ciiacc.check_pac(pl.v.from_entity_reference,'ENT',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; -- Initialize unassigned properties with default values on INSERT if operation = 'INS' then assign_insert_defaults(pl); -- check rest of access control if this is relend2 elsif operation = 'UPD' then bogus_arc_ref_flag := false; select max(old.name),max(old.from_entity_reference),max(old.arc_number) into old_name,old_from_entity_reference,old_arc_number from ci_relationship_ends old where old.id = pl.v.id; -- Check if the relationship end is part of a UID if in_uid(pl.v.id) then -- The from_entity_reference may not be changed if the relend is -- part of the UID of the entity (CDA-384) rmman.record_check('CIHRELENDE_UID_FROM_ENT',pl.v.id,null,null, pl.v.from_entity_reference is null or old_from_entity_reference is null or pl.v.from_entity_reference = old_from_entity_reference, 'Y',ciiutl.identify(pl.v.id, pl.v.types)); pl.v.from_entity_reference := old_from_entity_reference; else rmman.record_check('CIHRELENDE_UID_FROM_ENT',pl.v.id,null,null,true); end if; -- Check if the relationship is in an arc, the arc is not being updated, -- and the from_entity_reference is being changed rmman.record_check('CIHRELENDE_ARC_FROM_ENT',pl.v.id,null,null, pl.v.arc_number is null or old_arc_number is null or pl.v.arc_number != old_arc_number or pl.v.from_entity_reference is null or old_from_entity_reference is null or pl.v.from_entity_reference = old_from_entity_reference, 'Y',ciiutl.identify(pl.v.id, pl.v.types)); -- Check if an arc_number is being assigned if pl.i.arc_number and pl.v.arc_number is not null then -- Check if any other relends exist within this arc, and assign the -- arc_reference if so select max(arc_reference),min(arc_reference) into pl.v.arc_reference,bogus_arc_ref from ci_relationship_ends where arc_number = pl.v.arc_number and from_entity_reference = pl.v.from_entity_reference; -- If no other relends exist in this arc, assign a new reference if pl.v.arc_reference is null then pl.v.arc_reference := ciilok.idgen; -- Else, if there are differing arc_reference values for relends in the -- arc, clear this up later by assigning them all to this relend's -- arc_reference value elsif pl.v.arc_reference != bogus_arc_ref then bogus_arc_ref_flag := true; end if; pl.i.arc_reference := true; end if; 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); -- Relend may not be deleted if either end is part of a UID if operation = 'DEL' then check_delete(id); end if; END; --======================= POST-PROCESS (INS,UPD) ============================-- PROCEDURE post_process(operation varchar2,id rm.reference,pl ciorelationship_end.data) IS BEGIN -- Hack to clean up any possible problems with arc_reference mismatches -- spotted during pre-process if bogus_arc_ref_flag and not cdapi.load_mode then update ci_relationship_ends relend set relend.arc_reference = pl.v.arc_reference where relend.arc_number = pl.v.arc_number and relend.from_entity_reference = pl.v.from_entity_reference and relend.id != pl.v.id; end if; if cdapi.load_mode then return; end if; if operation = 'INS' then -- JW If this is relend2, create the relationship element if relend2 then -- update relend1 with details from this relend update ci_relationship_ends set to_entity_reference = pl.v.from_entity_reference ,other_relationship_end_ref = pl.v.id where id = pl.v.other_relationship_end_ref; -- Remove optionality constraint violations from relend1 following -- internal update rmman.record_check('CIRELENDO_TO_ENTITY_REFERENCE', pl.v.other_relationship_end_ref,null,null,true); rmman.record_check('CIRELENDO_OTHER_RELATIONSHIP_END_REF', pl.v.other_relationship_end_ref,null,null,true); -- log CORE secondary update end if; elsif operation = 'UPD' then -- If the relend is being transferred to another ENTITY, update the -- denormalized TO_ENTITY_REFERENCE stored in the other RELEND if pl.i.from_entity_reference then update ci_relationship_ends set to_entity_reference = pl.v.from_entity_reference where id = pl.v.other_relationship_end_ref; end if; end if; END; --======================= POST PROCESS (DEL,SEL) ============================-- PROCEDURE post_process(operation varchar2,id rm.reference) IS other_id rm.reference; -- rel_id rm.reference; BEGIN if operation = 'DEL' then -- Delete the other relend as well, if it exists select max(ci.id) into other_id from ci_relationship_ends ci where ci.other_relationship_end_ref = post_process.id; if other_id is not null then ciorelationship_end.del(other_id); end if; -- Delete this and all it's related sub-bits ciiutl.delete_element(id,pac_type); end if; END; --========================= ASSIGN INSERT DEFAULTS ==========================-- PROCEDURE assign_insert_defaults(pl in out NOCOPY ciorelationship_end.data) IS ent_ref rm.reference; relend_ref rm.reference; BEGIN -- Default element_type_name, etc. pl.v.element_type_name := pac_type; pl.i.element_type_name := true; -- check if the other relend already exists; if so, get it's FROM entity -- reference if cdapi.load_mode then select max(to_entity_reference), max(other_relationship_end_ref) into ent_ref, relend_ref from ci_relationship_ends where id = pl.v.other_relationship_end_ref; else select max(from_entity_reference), max(to_entity_reference), max(other_relationship_end_ref) into pl.v.to_entity_reference, ent_ref, relend_ref from ci_relationship_ends where id = pl.v.other_relationship_end_ref; end if; if pl.v.to_entity_reference is not null then -- Check that the other relend isn't already associated with a different -- relend if ent_ref is not null and ent_ref != pl.v.from_entity_reference then ciierr.fatal(702,ciiutl.identify(pl.v.id, pl.v.types),ciiutl.nls_type_plural_name('ENTITY')); -- B1286628 elsif relend_ref is null and relend_ref != pl.v.id then ciierr.fatal(702,ciiutl.identify(pl.v.id, pl.v.types),ciiutl.nls_type_plural_name('RELATIONSHIP_END')); -- B1286628 end if; relend2 := true; pl.i.to_entity_reference := true; else relend2 := false; end if; END; --=============================== IS UNIQUE? ================================-- FUNCTION is_unique(id rm.reference,name varchar2, other_relationship_end_ref rm.reference,other_name varchar2, from_entity_reference rm.reference,to_entity_reference rm.reference) RETURN boolean IS dummy number; BEGIN select max(1) into dummy from sys.dual where exists (select null from ci_relationship_ends from_rel,ci_relationship_ends to_rel where from_rel.from_entity_reference = is_unique.from_entity_reference and to_rel.from_entity_reference = is_unique.to_entity_reference and from_rel.other_relationship_end_ref = to_rel.id and from_rel.name = is_unique.name and to_rel.name = is_unique.other_name and from_rel.id != is_unique.id); if dummy is null then return(true); else -- If it gets this far, then a relationship with these relend names and -- between these ents already exists return(false); end if; END; --================================ IN UID? ==================================-- FUNCTION in_uid(relend_ref rm.reference) RETURN boolean IS dummy number; BEGIN select count(*) into dummy from ci_unique_identifier_entries where relationship_end_reference = relend_ref and attribute_or_relation = 'R'; if dummy = 0 then return(false); else return(true); end if; END; --============================== CHECK DELETE ===============================-- PROCEDURE check_delete(id rm.reference) IS other_relend_ref rm.reference; BEGIN null; -- 4.0.12 Removed all simple delete checking -- Check that neither relationship end is in a UID END; --================================== END ====================================-- -- -- Package instantiation block -- BEGIN is_installed := true; END; /