Rem  Copyright (c) 1994 by Oracle Corporation 
Rem    NAME
Rem      civoa.mpb - <one-line expansion of the name>
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem    RETURNS
Rem 
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem    MODIFIED   (MM/DD/YY)
Rem     cvanes     27/10/99 -  Creation 
CREATE OR REPLACE PACKAGE BODY cimvoa IS

cursor ass_c(conid rm.reference) is
  select d.element 
  from rm_deferred_checks d
  where d.assertion = conid;


--========================== IS_VOA_EOU_NAME_UNIQUE ========================--
FUNCTION is_voa_eou_name_unique(elem_ref number, vob_ref number, elem_name varchar2, 
                                elem_sql_alias varchar2, elem_type varchar2, clash_id out number, clash_type out varchar2)
RETURN boolean
IS
  clashing_id number;
  clashing_type varchar2(10);
BEGIN
  if elem_type = 'VOA' then
    FOR clashes in (
      select voa.id clashing_id, 'VOA' clashing_type
      from   ci_view_object_attributes   voa
      where  voa.view_object_reference = vob_ref
      and   (UPPER(voa.name) = elem_name or  voa.sql_alias = elem_sql_alias)
      and    voa.id <> elem_ref
      UNION
      select eou.id, 'EOU'
      from   ci_entity_object_usages  eou
      where  eou.view_object_reference = vob_ref
      and    eou.sql_alias = elem_name    ) LOOP

      clash_id := clashes.clashing_id;
      clash_type := clashes.clashing_type;
      return(false);
    END LOOP;
  elsif elem_type = 'EOU' then
    FOR clashes in (
      select voa.id clashing_id, 'VOA' clashing_type
      from   ci_view_object_attributes   voa
      where  voa.view_object_reference = vob_ref
      and    UPPER(voa.name) = elem_sql_alias
      UNION
      select eou.id, 'EOU'
      from   ci_entity_object_usages  eou
      where  eou.view_object_reference = vob_ref
      and   (eou.sql_alias = elem_sql_alias or  UPPER(eou.name) = elem_name) 
      and    eou.id <> elem_ref
    ) LOOP

      clash_id := clashes.clashing_id;
      clash_type := clashes.clashing_type;
      return(false);
    END LOOP;
  end if;
  clash_id := null;
  clash_type := null;
  return(true);
END is_voa_eou_name_unique;
--============================ CIHVOAE_UNIQUE_NAME =============================--
-- %0!s: The Name must be unique in the owning object
PROCEDURE check_cihvoae_unique_name(conid rm.reference)
IS
  ele_name ci_view_object_attributes.name%type;                      --varchar2(100)
  ele_sql_alias ci_view_object_attributes.sql_alias%type;            --varchar2(30)
  ele_short_name ci_view_object_attributes.element_type_name%type;   --varchar2(10)
  vob_ref ci_view_object_attributes.view_object_reference%type;
  clash_id number;
  clash_type varchar2(10);
BEGIN

  for assertion in ass_c(conid) loop
    select UPPER(voa.name)
    ,      voa.sql_alias
    ,      'VOA'
    ,      voa.view_object_reference
    into   ele_name
    ,      ele_sql_alias
    ,      ele_short_name
    ,      vob_ref
    from   ci_view_object_attributes    voa
    where  voa.id = assertion.element
    UNION ALL
    select UPPER(eou.name)
    ,      eou.sql_alias
    ,      'EOU'
    ,      eou.view_object_reference
    from   ci_entity_object_usages      eou
    where  eou.id = assertion.element
;
     
    if is_voa_eou_name_unique(assertion.element, vob_ref, ele_name, 
                              ele_sql_alias, ele_short_name, clash_id, clash_type) then
      rmman.record_check(conid,assertion.element,null,null,true);
    else
      rmman.record_check(conid,assertion.element,null,null,false,'Y',
                         ciiutl.identify(assertion.element, ele_short_name),
                         ciiutl.identify(clash_id, clash_type) );
    end if;
  end loop;
end check_cihvoae_unique_name;
END;
/