rem
rem $Header: L:\\\\model\\repman40\\api\\RCS\\ciappcon.mpb 1.12 1998/12/14 20:55:47 cvanes Exp $
rem
Rem  Copyright (c) 1994 by Oracle Corporation
Rem    NAME
Rem      ciappcon.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     mkilsby    27/02/02 -  Bug 1943441 - uniqueness check for UE elements
Rem     loldham    15/10/98 -  Remove ownership check on synonyms
Rem     loldham    29/09/98 -  Bug 735533 - uniqueness check for functions
Rem     jwetherb   01/11/95 -  Bug fixes; added EL_OCCUR_TYPE checks
Rem     jwetherb   01/11/95 -  Remove maxact calculation
Rem     jwetherb   01/11/95 -  Remove references to prev in cihappconu1
Rem     jwetherb   09/27/94 -  Constrain 'myappcons' to the context activity
Rem jwetherb   07/24/94 -  U2 now checks for conflict between name/name
Rem                            name/short_name, and short_name/short_name
Rem jwetherb   07/24/94 -  Check pac type when determining uniqueness
Rem jwetherb   07/21/94 -  Major rewrite
Rem     aheath     07/11/94 -  Creation
CREATE OR REPLACE PACKAGE BODY cimappcon IS

  cdapi_activity number;
--=========================== CHECK_CIHAPPCONU1 =============================--

  -- Retrieve a list of APP_SYS_REF and PAC_REF pairs, one per deferred check
  -- From 4.0.9 onwards this returns subtype name instead of supertype name
  --  eg TBL not TAB
  cursor myappcons(constraint_id number) is
    select fm.irid             app_con_ref,
           fm.folder_reference app_sys_ref,
           fm.member_object    pac_id,
           et.short_name       pac_type
      from rm_element_types et
          ,sdd_object_versions ov
          ,sdd_folder_members fm
          ,rm_deferred_checks dc
     where et.id = ov.logical_type_id
       and ov.irid = fm.member_object
       and fm.irid =  dc.element
       and dc.activity = cdapi_activity
       and dc.assertion = constraint_id;

  cursor myappcons1(constraint_id number) is
    select null             app_con_ref,
           null             app_sys_ref,
           ov.irid          pac_id,
           et.short_name    pac_type
      from rm_element_types    et
          ,sdd_object_versions ov
          ,rm_deferred_checks  dc
     where et.id = ov.logical_type_id
       and ov.irid = dc.element
       and dc.activity = cdapi_activity
       and dc.assertion = constraint_id;

FUNCTION is_ent_ter_syn_unique(app_id number, pac_id number, pac_type varchar2, clash_types out number)
RETURN number;

--======================================================================
-- Stub to get everything compiled
PROCEDURE check_cihappconu2(conid rm.reference)
IS
  clash_id number;
BEGIN
  null;
END;

--======================================================================
-- Used for most uniqueness name checks
PROCEDURE check_cihappconu1(conid rm.reference)
IS
  clash_id number;
  clash_types number;
  found_fm boolean := FALSE;
BEGIN
  rmman.record_check(conid,null,null,null,true);
  -- For each APP_SYS_REF and PAC_REF pair
  cdapi_activity := cdapi.activity;
  for appcon in myappcons (conid) loop
    found_fm := TRUE;
    rmdbg.trace('Calling is_pac_name_unique  App Sys = '||to_char(appcon.app_sys_ref)||
                'pac_id: '||to_char(appcon.pac_id)||'pac_type: '||
                              appcon.pac_type);
    if not is_pac_name_unique(appcon.app_sys_ref, appcon.pac_id,
                              appcon.pac_type, clash_id, clash_types) then
      -- Object FRED : Uniqueness clash in App Sys JOE with Object FRED
      rmman.record_check(conid, appcon.app_con_ref, null, cdapi_activity, false,
       'Y', ciiutl.identify(appcon.pac_id, appcon.pac_type),
       ciiutl.identify(appcon.app_sys_ref, 'CEL'), ciiutl.identify(clash_id, clash_types));
    end if;
  end loop;
  if found_fm = FALSE then
    for appcon1 in myappcons1 (conid) loop
      rmdbg.trace('Calling is_pac_name_unique For top level container '||
                  'pac_id: '||to_char(appcon1.pac_id)||'pac_type: '||
                                appcon1.pac_type);
      if not is_pac_name_unique(appcon1.app_sys_ref, appcon1.pac_id,
                                appcon1.pac_type, clash_id, clash_types) then
        -- Object FRED : Uniqueness clash in App Sys JOE with Object FRED
        rmman.record_check(conid, appcon1.pac_id, null, cdapi_activity, false,
         'Y', ciiutl.identify(appcon1.pac_id, appcon1.pac_type),
         'ALL ROOT CONTAINERS', ciiutl.identify(clash_id, clash_types));
      end if;
    end loop;
  end if;
END;

--======================================================================
-- This works for all subtypes of MUN (including SACs). Checks for uniqueness within Module, NOT
--  uniqueness within folder (done by APPCONU1)

function is_mun_unique(irid in number
                      ,subtype in varchar2
                      ,name in varchar2
                      ,clash_irid out number
                      ,clash_folder_reference out number)
                       return boolean
is
  -- We seperate SMCs from RMCs and SUBs like this because 
  --  a) During copy, the MCN does not exist 
  --  b) performance improvement for the typical case 
  -- Bug 2192114, name of SMC must be unique within all MCOs of this general
  -- module but not including subcomponents
  cursor get_dup_smc(mco_id number, mco_name varchar2, general_module_ref number) is 
   select mun2.irid clash_irid, fm.folder_reference 
     from ci_module_units                mun2 
         ,ci_module_component_inclusions mcn2 
         ,sdd_folder_members             fm 
    where fm.member_object = nvl(mun2.general_module_reference, mun2.irid) 
      and mun2.irid = mcn2.module_component_reference
      and mcn2.general_module_reference = general_module_ref 
      and mun2.name = mco_name 
      and mun2.irid != mco_id 
      and fm.ownership_flag = 'Y'; 
 
  -- further separate RMC and SUB because code is slow and was incorrect !

  -- This check is only for RMCs
  -- Only need to check RMC name against SMC because RMC name must
  -- be unique within folder.
  cursor get_dup_rmc(mco_id number, mco_name varchar2) is 
   select mun2.irid clash_irid, fm.folder_reference 
     from ci_module_units                mun2 
         ,ci_module_component_inclusions this_RMC
         ,ci_module_component_inclusions mcn2
         ,sdd_folder_members             fm 
    where fm.member_object = nvl(mun2.general_module_reference, mun2.irid) 
      and mun2.irid = mcn2.module_component_reference
      and mcn2.general_module_reference = this_RMC.general_module_reference 
      and this_RMC.module_component_reference = mco_id
      and mun2.name = mco_name 
      and mun2.irid != mco_id
      and fm.ownership_flag = 'Y'; 
 
  -- This check is only for SUBs
   cursor get_dup_sub(mco_id number, mco_name varchar2) is 
   select mun2.irid clash_irid, fm.folder_reference 
     from ci_module_units                mun1 
         ,ci_module_units                mun2 
         ,sdd_folder_members             fm 
    where fm.member_object = mun2.pac_reference
      and mun1.module_component_reference = mun2.module_component_reference
      and mun1.id = mco_id 
      and mun2.name = mco_name 
      and mun2.irid != mco_id 
      and fm.ownership_flag = 'Y'; 

  -- We seperate SLVs from RLVs like this because
  --  a) During copy, the MCN does not exist
  --  b) performance improvement for the typical case
  cursor get_dup_slv(lov_id number, lov_name varchar2, general_module_ref number) is
   select lov2.irid clash_irid, fm.folder_reference
     from ci_lists_of_values lov2
         ,ci_lov_inclusions  lvi2
         ,sdd_folder_members fm
    where fm.member_object = nvl(lov2.general_module_reference, lov2.irid)
      and lov2.irid = lvi2.list_of_values_reference
      and lvi2.general_module_reference = general_module_ref
      and lov2.name = lov_name
      and lov2.irid != lov_id
      and fm.ownership_flag = 'Y';

  cursor get_dup_lov(lov_id number, lov_name varchar2) is
   select lov2.irid clash_irid, fm.folder_reference
     from ci_lists_of_values lov2
         ,ci_lov_inclusions  lvi1
         ,ci_lov_inclusions  lvi2
         ,sdd_folder_members fm
    where fm.member_object = nvl(lov2.general_module_reference, lov2.irid)
      and lov2.irid = lvi2.list_of_values_reference
      and lvi2.general_module_reference = lvi1.general_module_reference
      and lvi1.list_of_values_reference = lov_id
      and lov2.name = lov_name
      and lov2.irid != lov_id
      and fm.ownership_flag = 'Y';

  mun_name ci_module_units.name%type;
  general_module_ref ci_module_units.general_module_reference%type;
begin
  if name is null or subtype in ('SMC', 'SLV') then
    select mun.name, mun.general_module_reference
      into mun_name, general_module_ref
      from ci_module_units mun
     where mun.irid = is_mun_unique.irid;
  else
    mun_name := is_mun_unique.name;
  end if;
    --dbms_output.put_line('MU ' || mun_name || ' ' || subtype);  
  if subtype in ('SMC') then
    --dbms_output.put_line('MU2');  
    for clash_el in get_dup_smc(irid, mun_name, general_module_ref) loop
      -- We now have a UID clash
      clash_irid := clash_el.clash_irid;
      clash_folder_reference := clash_el.folder_reference;
      return false;
    end loop;
  elsif subtype = 'RMC' then 
    --dbms_output.put_line('MU3');   
    for clash_el in get_dup_rmc(irid, mun_name) loop 
      -- We now have a UID clash 
      clash_irid := clash_el.clash_irid; 
      clash_folder_reference := clash_el.folder_reference; 
      return false; 
    end loop; 
  elsif subtype = 'SUB' then 
    --dbms_output.put_line('MU3.1');   
    for clash_el in get_dup_sub(irid, mun_name) loop 
      -- We now have a UID clash 
      clash_irid := clash_el.clash_irid; 
      clash_folder_reference := clash_el.folder_reference; 
      return false; 
    end loop; 
  elsif subtype in ('SLV') then
    --dbms_output.put_line('MU4');  
    for clash_el in get_dup_slv(irid, mun_name, general_module_ref) loop
      -- We now have a UID clash
      clash_irid := clash_el.clash_irid;
      clash_folder_reference := clash_el.folder_reference;
      return false;
    end loop;
  else
    --dbms_output.put_line('MU5');  
    for clash_el in get_dup_lov(irid, mun_name) loop
      -- We now have a UID clash
      clash_irid := clash_el.clash_irid;
      clash_folder_reference := clash_el.folder_reference;
      return false;
    end loop;
  end if;
  clash_irid := null;
  clash_folder_reference := null;
  return true;
end is_mun_unique;
                   
--======================================================================
FUNCTION is_ent_ter_syn_unique(app_id number, pac_id number, pac_type varchar2, clash_types out number)
RETURN number
IS
  my_clash_id number;
  my_clash_types number;
  el_name varchar2(40);
  el_short_name varchar2(10);
  el_plural varchar2(40);
BEGIN
  if (pac_type = 'ENT') then
    select name, short_name, plural
    into   el_name, el_short_name, el_plural
    from   sdd_ent
    where  irid = pac_id;
  elsif (pac_type = 'SYN') then
    select name
    into   el_name
    from   sdd_syn
    where  irid = pac_id;
  else /* (pac_type = 'TER') */
    select name
    into   el_name
    from   sdd_ter
    where  irid = pac_id;
  end if;

  -- 20Feb98 CvE Removed Rule hint (Bug 625941)
  -- changed 15/10/98 to not use union view as I think this will be quicker
  -- first check for clashing entities
 
  -- 21Jan00 CvE removed max and replaced with loops - each one is 20x faster
  for clashes in (
    select other_el.irid my_clash_id, other_el.types my_clash_types
      from sdd_folder_members fm
          ,sdd_ent other_el
     where (NVL(fm.name_in_context, other_el.name) in (el_name, el_short_name, el_plural) or
            other_el.short_name in (el_name, el_short_name, el_plural) or
            other_el.plural     in (el_name, el_short_name, el_plural) )
       and fm.member_object    = other_el.irid
       and fm.folder_reference = is_ent_ter_syn_unique.app_id
       and other_el.irid != is_ent_ter_syn_unique.pac_id) loop

    clash_types := clashes.my_clash_types;
    return(clashes.my_clash_id);
  end loop;

  -- second check for clashing terms
  for clashes in (
    select other_el.irid my_clash_id, other_el.types my_clash_types
      from sdd_folder_members fm
          ,sdd_ter other_el
     where NVL(fm.name_in_context, other_el.name) in (el_name, el_short_name, el_plural)
       and fm.member_object    = other_el.irid
       and fm.folder_reference = is_ent_ter_syn_unique.app_id
       and other_el.irid != is_ent_ter_syn_unique.pac_id) loop

    clash_types := clashes.my_clash_types;
    return(clashes.my_clash_id);
  end loop;

  -- third check for clashing syns
  for clashes in (
    select other_el.irid my_clash_id, other_el.types my_clash_types
      from sdd_folder_members fm
          ,sdd_syn other_el
     where NVL(fm.name_in_context, other_el.name) in (el_name, el_short_name, el_plural)
       and fm.member_object    = other_el.irid
       and fm.folder_reference = is_ent_ter_syn_unique.app_id
       and other_el.irid != is_ent_ter_syn_unique.pac_id) loop

    clash_types := clashes.my_clash_types;
    return(clashes.my_clash_id);
  end loop;
  
  clash_types := null;
  return(null);
  
/*
    select max(other_el.irid)
    into my_clash_id
    from sdd_folder_members fm
        ,ci_entity_namespace_objects other_el
        ,ci_entity_namespace_objects my_el
   where (other_el.name       in (my_el.name,
                                  my_el.short_name, my_el.plural) or
          other_el.short_name in (my_el.name,
                                  my_el.short_name, my_el.plural) or
          other_el.plural     in (my_el.name,
                                  my_el.short_name, my_el.plural))
     and fm.member_object    = other_el.irid
     and my_el.irid = is_ent_ter_syn_unique.pac_id
     and fm.folder_reference = is_ent_ter_syn_unique.app_id
     and other_el.irid != is_ent_ter_syn_unique.pac_id;

-- This fix depends on synonyms still being in union view and joined via pac_reference
-- Need to add some code for this once ownership of synonyms has been resolved
-- both lines of code shown below to show logic although only second line removed
-- and (fm.member_object    = other_el.irid  or
--      fm.folder_reference = other_el.application_system_owned_by) -- catch SYNs
*/

END is_ent_ter_syn_unique;

--======================================================================
FUNCTION is_dbobj_unique(app_id number, pac_id number, clash_types out number)
RETURN number
IS
  my_clash_id number;
  my_name varchar2(100);
  
  cursor matching_elems(app_id number, pac_id number, my_name varchar2) is
  select other_el.irid, other_el.types
    from sdd_folder_members fm
        ,ci_named_database_objects other_el
   where other_el.name = matching_elems.my_name
     and fm.member_object = other_el.pac_irid
     and fm.folder_reference = matching_elems.app_id
     and other_el.irid != matching_elems.pac_id;
       
BEGIN
  select my_el.name
    into my_name
    from ci_named_database_objects my_el
   where my_el.irid = pac_id;
   
  my_clash_id := null;
  clash_types := null;

  for other in matching_elems(app_id, pac_id, my_name) loop
    my_clash_id := other.irid;
    clash_types := other.types;
    exit;
  end loop;

  return my_clash_id;

END is_dbobj_unique;

--======================================================================
FUNCTION is_tbl_alias_unique(app_id number, pac_id number, clash_types out number)
RETURN number
IS
  my_clash_id number;
  my_alias varchar2(100);
  
  cursor matching_elems(app_id number, pac_id number, my_alias varchar2) is
  select other_el.irid, other_el.types
    from sdd_folder_members fm
        ,ci_relation_definitions other_el
   where other_el.alias = matching_elems.my_alias    -- NULL won't match
     and fm.member_object = other_el.irid
     and fm.folder_reference = matching_elems.app_id
     and other_el.irid != matching_elems.pac_id;
       
BEGIN
  select my_el.alias
    into my_alias
    from ci_relation_definitions my_el
   where my_el.irid = pac_id;
   
  my_clash_id := null;
  clash_types := null;

  if my_alias is null then
    return null;
  end if;
  
  for other in matching_elems(app_id, pac_id, my_alias) loop
    my_clash_id := other.irid;
    clash_types := other.types;
    exit;
  end loop;

  return my_clash_id;

END is_tbl_alias_unique;

--======================================================================
FUNCTION is_publicdsy_unique(app_id number, pac_id number, clash_types out number)
RETURN number
IS
  dummy number;
  my_clash_id number;
  my_clash_occur_type varchar2(10);
  my_clash_types number;
BEGIN
  -- 20Feb02 LO removed max and replaced with loop (Bug 2192114)
  for clashes in ( 
  select other_el.irid my_clash_id, other_el.types  my_clash_types 
    from sdd_folder_members fm 
        ,sdd_dsy other_el 
        ,sdd_dsy my_el 
   where other_el.name = my_el.name 
     and fm.member_object = other_el.pac_ref 
     and my_el.irid = is_publicdsy_unique.pac_id 
     and fm.folder_reference = is_publicdsy_unique.app_id 
     and other_el.irid != is_publicdsy_unique.pac_id 
     and other_el.scope = 'PUBLIC' ) loop
 
    clash_types := clashes.my_clash_types; 
    return(clashes.my_clash_id); 

  end loop;

  clash_types := null; 
  return(null); 

END is_publicdsy_unique;

--======================================================================
FUNCTION is_short_name_unique(app_id number, pac_id number, pac_type varchar2, clash_types out number)
RETURN number
IS
  my_clash_id number := null; 
  my_clash_types number := null; 
  big_str varchar2(1000); 
  cur_id integer; 
  ret_val integer; 
  tab_name varchar2(40); 
  el_name varchar2(40); 
  el_short_name varchar2(20); 

BEGIN

  tab_name := ciiexp.get_table_name(pac_type);

  rmdbg.trace('In is_short_name_unique');
  big_str :=
  'select other_el.irid, other_el.types' || 
  '  from sdd_folder_members fm' ||
  '      ,' || tab_name || ' other_el' ||
  '      ,' || tab_name || ' my_el' ||
  ' where fm.member_object = other_el.irid' ||
  '   and my_el.irid = :pac_id';
  if pac_type in ('BUN') then
    big_str := big_str ||
    '   and (other_el.name in (my_el.name, my_el.short_name)' ||
    '     or other_el.short_name in (my_el.name, my_el.short_name))';
  elsif pac_type in ('GEM', 'JAM', 'PLM') then  -- Bug 652241
    select m.name, m.short_name
    into   el_name, el_short_name
    from   sdd_mod   m
    where  m.irid = is_short_name_unique.pac_id;

    big_str := big_str || 
    '   and (other_el.name in (:mod_name, :mod_short_name)' || 
    '     or other_el.short_name in (:mod_name, :mod_short_name))' || 
    '   and other_el.module_type = :mod_type'; 
  else -- 'EXT', 'DST'
    -- Bug 732742
    big_str := big_str ||
    '   and (other_el.name in (my_el.name, my_el.short_identifier)' ||
    '     or other_el.short_identifier in (my_el.name, my_el.short_identifier))';
  end if;
  big_str := big_str ||
  '   and fm.folder_reference = :app_id' ||
  '   and other_el.irid != my_el.irid';

  rmdbg.trace('Cursor is: '||big_str);
  rmdbg.trace('app is: '||to_char(app_id));
  rmdbg.trace('pac is: '||to_char(pac_id));
  cur_id  := dbms_sql.open_cursor;
  dbms_sql.parse(cur_id, big_str, dbms_sql.native);
  dbms_sql.bind_variable(cur_id, ':pac_id', pac_id);
  dbms_sql.bind_variable(cur_id, ':app_id', app_id);
  if pac_type in ('GEM', 'JAM', 'PLM') then
    dbms_sql.bind_variable(cur_id, ':mod_name', el_name); 
    dbms_sql.bind_variable(cur_id, ':mod_short_name', el_short_name); 
    dbms_sql.bind_variable(cur_id, ':mod_type', pac_type); 
  end if;
  dbms_sql.define_column(cur_id, 1, my_clash_id);
  dbms_sql.define_column(cur_id, 2, my_clash_types);
  ret_val := dbms_sql.execute(cur_id);
  if dbms_sql.fetch_rows(cur_id) > 0 then
    dbms_sql.column_value(cur_id, 1, my_clash_id);
    dbms_sql.column_value(cur_id, 2, my_clash_types);
  end if;
  dbms_sql.close_cursor(cur_id);
  clash_types := my_clash_types;
  return my_clash_id;
END is_short_name_unique;

--======================================================================
FUNCTION is_name_unique(app_id number,
                        pac_id number,
                        pac_type varchar2,
                        clash_types out number,
                        case_independent in boolean default false)
  -- Only set case_independent to true if you want to 'upper()' the name, so
  -- it's not suitable for values that are stored as uppercase already
RETURN number
IS
  my_clash_id number := null; 
  my_clash_types number := null; 
  big_str varchar2(1000);
  cur_id integer;
  ret_val integer;
  tab_name varchar2(40);
BEGIN

  tab_name := ciiexp.get_table_name(pac_type);

  /* Bug 735533 */
  rmdbg.trace('In is_name_unique, pac_type = '||pac_type);
  rmdbg.trace(', tab_name ' || tab_name);
  if (pac_type = 'FUN') then
    big_str :=
    'select other_el.irid, other_el.types' || 
    '  from sdd_folder_members fm' ||
    '      ,' || tab_name || ' other_el' ||
    '      ,' || tab_name || ' my_el' ||
    ' where fm.member_object = other_el.irid' ||
    '   and my_el.irid = :pac_id' ||
    '   and other_el.function_label = my_el.function_label' ||
    '   and fm.folder_reference = :app_id' ||
    '   and other_el.irid != my_el.irid';
  else
    big_str :=
    'select other_el.irid, other_el.types' || 
    '  from sdd_folder_members fm' ||
    '      ,' || tab_name || ' other_el' ||
    '      ,' || tab_name || ' my_el' ||
    ' where fm.member_object = other_el.irid' ||
    '   and my_el.irid = :pac_id' ||
    '   and fm.folder_reference = :app_id' ||
    '   and other_el.irid != my_el.irid';
    if case_independent then
      big_str := big_str ||  '   and upper(other_el.name) = upper(my_el.name)';
    else
      big_str := big_str ||  '   and other_el.name = my_el.name';
    end if;
--  Bug 1943441.  All user defined types are implemented in the same
--  two tables, and so their uniqueness checks need to be qualified
--  by the types column.
    if tab_name in ('SDD_UEE', 'SDD_UES') then
      big_str := big_str ||  '   and other_el.types = my_el.types';
    end if;
  end if;
  rmdbg.trace('Cursor is: '||big_str);
--dbms_output.put_line('Cursor is: '||big_str);
--dbms_output.put_line('pac_id is: '||pac_id);
--dbms_output.put_line('app_id is: '||app_id);
  cur_id  := dbms_sql.open_cursor;
  dbms_sql.parse(cur_id, big_str, dbms_sql.native);
  dbms_sql.bind_variable(cur_id, ':pac_id', pac_id);
  dbms_sql.bind_variable(cur_id, ':app_id', app_id);
  dbms_sql.define_column(cur_id, 1, my_clash_id);
  dbms_sql.define_column(cur_id, 2, my_clash_types);
  ret_val := dbms_sql.execute(cur_id);
  if dbms_sql.fetch_rows(cur_id) > 0 then
    dbms_sql.column_value(cur_id, 1, my_clash_id);
    dbms_sql.column_value(cur_id, 2, my_clash_types);
  end if;
  dbms_sql.close_cursor(cur_id);

  clash_types := my_clash_types;
  return my_clash_id;
END is_name_unique;

--============================= IS CLASS NAME UNIQUE ===========================
FUNCTION is_class_name_unique(app_id number, pac_id number, clash_types out number)
RETURN number
IS
  dummy number;
  my_clash_id number;
BEGIN

  select max(other_elem.irid), max(other_elem.types)
    into my_clash_id, clash_types
    from ci_folder_members fm
        ,(select ass.irid, ass.name  chk_name
            from ci_associations ass
           union all
          select cla.irid, cla.class_name   chk_name
            from ci_classifiers cla
           union all
          select pck.irid, pck.name   chk_name
            from ci_packages pck
           union all
          select vli.irid, vli.name   chk_name
            from ci_view_links vli) my_elem
        ,(select ass.irid, ass.name    chk_name, ass.types
            from ci_associations ass
           union all
          select cla.irid, cla.class_name   chk_name, cla.types
            from ci_classifiers cla
           union all
          select vli.irid, vli.name   chk_name, vli.types
            from ci_view_links   vli) other_elem
    where other_elem.irid = fm.member_object
      and fm.folder_reference = is_class_name_unique.app_id
      and upper(my_elem.chk_name) = upper(other_elem.chk_name)
      and my_elem.irid = is_class_name_unique.pac_id
      and other_elem.irid != is_class_name_unique.pac_id;

  /* Bug 1024719, added association name (back) into uniqueness check
  select max(other_elem.irid), max(other_elem.types)
    into my_clash_id, clash_types
    from ci_folder_members fm
        ,ci_classifiers my_elem
        ,ci_classifiers other_elem
    where other_elem.irid = fm.member_object
      and fm.folder_reference = is_class_name_unique.app_id
      and upper(my_elem.class_name) = upper(other_elem.class_name)
      and my_elem.irid = is_class_name_unique.pac_id
      and other_elem.irid != is_class_name_unique.pac_id;
  */
  return my_clash_id;

end is_class_name_unique;

--============================= IS CLASS NAME UNIQUE 2 ===========================
FUNCTION is_class_name_unique2(app_id number, pac_id number, clash_types out number)
RETURN number
IS
  dummy number;
  my_clash_id number;
BEGIN

  select max(other_elem.irid), max(other_elem.types)
    into my_clash_id, clash_types
    from ci_folder_members fm
        ,(select ass.irid, ass.name  chk_name
            from ci_associations ass
           union all
          select cla.irid, cla.class_name   chk_name
            from ci_classifiers cla
           union all
          select vli.irid, vli.name   chk_name
            from ci_view_links    vli) my_elem
        ,ci_packages    other_elem
    where other_elem.irid = is_class_name_unique2.app_id
      and fm.folder_reference = other_elem.irid
      and fm.ownership_flag = 'Y'
      and upper(my_elem.chk_name) = upper(other_elem.name)
      and my_elem.irid = is_class_name_unique2.pac_id
      and other_elem.irid != is_class_name_unique2.pac_id;

  return my_clash_id;

end is_class_name_unique2;

--============================= IS STE UNIQUE ===============================
FUNCTION is_STE_unique(app_id number, pac_id number, pac_type varchar2, clash_types out number)
RETURN number
IS
  dummy number;
  my_clash_id number := null;

BEGIN
 
  my_clash_id := is_name_unique(app_id, pac_id, pac_type, clash_types, true);  
  
  if my_clash_id IS NULL then
  
    return my_clash_id;
  
  else
    
    select max(other_elem.irid), max(other_elem.types)
          into my_clash_id, clash_types
          from ci_folder_members fm
              ,ci_stereotypes my_elem
              ,ci_stereotypes other_elem
          where other_elem.irid = fm.member_object
            and fm.folder_reference = is_STE_unique.app_id
            and my_elem.base_class = upper(other_elem.base_class)
            and my_elem.irid = is_STE_unique.pac_id
        and other_elem.irid != is_STE_unique.pac_id;
        
    return  my_clash_id;
    
  end if;
  
  
end is_STE_unique;


--============================= IS FUN SEQ UNIQUE ===========================
FUNCTION is_fun_seq_unique(app_id number, pac_id number, clash_types out number)
RETURN number
IS
  dummy number;
  my_clash_id number;
BEGIN

  select max(other_elem.irid), max(other_elem.types)
    into my_clash_id, clash_types
    from ci_folder_members fm
        ,ci_functions my_elem
        ,ci_functions other_elem
    where other_elem.irid = fm.member_object
      and fm.folder_reference = is_fun_seq_unique.app_id
      and my_elem.sequence_in_parent = other_elem.sequence_in_parent
      and NVL(my_elem.parent_reference, '1') = NVL(other_elem.parent_reference, '1')
      and my_elem.irid = is_fun_seq_unique.pac_id
      and other_elem.irid != is_fun_seq_unique.pac_id;

  return my_clash_id;

end is_fun_seq_unique;

--============================= IS ABT NAME UNIQUE ===========================
/*
  -- Gone for 4.0.5 (ABT rules are as normal PACs) CvE
FUNCTION is_abt_name_unique(app_id number, pac_id number, pc_type_of varchar2)
RETURN number
IS
  dummy number;
  my_clash_id number;
  pack_ref number;
  abt_name ci_abstract_types.name%TYPE;
BEGIN

  select abt.package_reference, abt.name
    into pack_ref, abt_name
    from ci_abstract_types abt
   where abt.id = pac_id;

  if pack_ref is null then    -- Use ordinary PAC rules for UID
    return is_name_unique(app_id, pac_id, pc_type_of);
  else                        -- Effectivly, it's a SAC of Package
    select max(abt.id)
      into my_clash_id
      from ci_abstract_types abt
     where abt.package_reference = pack_ref
       and abt.name = abt_name
       and abt.id != pac_id;
  end if;
  return my_clash_id;
END is_abt_name_unique;
*/

--============================= IS DIA NAME UNIQUE ===========================
FUNCTION is_dia_name_unique(app_id number, pac_id number, clash_types out number)
RETURN number
IS
  my_clash_id number;
  my_clash_types number;
BEGIN
  select max(other_dia.id), max(other_dia.types)
    into my_clash_id, my_clash_types
    from sdd_folder_members fm
        ,ci_diagrams my_dia
        ,ci_diagrams other_dia
   where fm.member_object = other_dia.id
     and other_dia.diagram_type = my_dia.diagram_type -- Different types of diagrams may share the same name
     and other_dia.name = my_dia.name
     and fm.folder_reference = is_dia_name_unique.app_id
     and other_dia.id != my_dia.id
     and my_dia.id = is_dia_name_unique.pac_id;

  clash_types := my_clash_types;
  return my_clash_id;
END is_dia_name_unique;

--============================= IS CEL NAME UNIQUE ===========================
FUNCTION is_cel_name_unique(pac_id number, clash_types out number,
                            case_independent in boolean default false)

RETURN number
IS
  my_clash_id number;
  my_clash_types number;
BEGIN
  -- Changed to check root_flag rather than 'not exists a folder member' due to bug 1185217 
  if case_independent then
    select max(other_cel.irid), max(other_cel.types)
      into my_clash_id, my_clash_types
      from sdd_root_container_elements   my_cel
      ,    sdd_root_container_elements   other_cel
     where UPPER(other_cel.name) = UPPER(my_cel.name)
       and other_cel.irid != my_cel.irid
       and my_cel.irid = is_cel_name_unique.pac_id;
  else
    select max(other_cel.irid), max(other_cel.types)
      into my_clash_id, my_clash_types
      from sdd_root_container_elements   my_cel
      ,    sdd_root_container_elements   other_cel
     where other_cel.name = my_cel.name
       and other_cel.irid != my_cel.irid
       and my_cel.irid = is_cel_name_unique.pac_id;
  end if;

  clash_types := my_clash_types;
  return my_clash_id;
END is_cel_name_unique;

--============================= IS CDE UNIQUE ===========================
FUNCTION is_cde_unique(pac_id number, clash_types out number)
RETURN number
IS
  my_clash_id number;
  my_clash_types number;
BEGIN
  select max(other_cde.irid), max(other_cde.types)
    into my_clash_id, my_clash_types
    from sdd_cde   my_cde
    ,    sdd_cde   other_cde
   where other_cde.primary_access_file_ref = my_cde.primary_access_file_ref
     and other_cde.irid != my_cde.irid
     and my_cde.irid = is_cde_unique.pac_id;

  clash_types := my_clash_types;
  return my_clash_id;
END is_cde_unique;

--============================= IS PSE SEQ UNIQUE ===========================
FUNCTION is_pse_seq_unique(app_id number, pac_id number, clash_types out number)
RETURN number
IS
  my_clash_id number;
  my_clash_types number;
BEGIN
  select max(other_pse.id), max(other_pse.types)
    into my_clash_id, my_clash_types
    from sdd_folder_members fm
        ,ci_pseudostates my_pse
        ,ci_pseudostates other_pse
   where fm.member_object = other_pse.id
     and other_pse.sequence_number = my_pse.sequence_number
     and fm.folder_reference = is_pse_seq_unique.app_id
     and other_pse.id != my_pse.id
     and my_pse.id = is_pse_seq_unique.pac_id;

  clash_types := my_clash_types;
  return my_clash_id;
END is_pse_seq_unique;

--============================= IS TMS UNIQUE ===========================
FUNCTION is_tms_unique(app_id number, pac_id number, clash_types out number)
RETURN number
IS
  my_clash_id number;
  my_clash_types number;
BEGIN
  select max(other_tms.id), max(other_tms.types)
    into my_clash_id, my_clash_types
    from sdd_folder_members fm
        ,ci_transformation_mapping_sets my_tms
        ,ci_transformation_mapping_sets other_tms
   where fm.member_object = other_tms.id
     and other_tms.transformation_type = my_tms.transformation_type
     and fm.folder_reference = is_tms_unique.app_id
     and other_tms.id != my_tms.id
     and my_tms.id = is_tms_unique.pac_id;

  clash_types := my_clash_types;
  return my_clash_id;
END is_tms_unique;

--======================================================================
FUNCTION is_pac_name_unique(app_id number, pac_id number, pac_type varchar2, clash_id out number)
RETURN boolean
IS
  my_clash_id number;
  my_clash_types number;
  ret_code boolean;
BEGIN
  my_clash_id := null;
  my_clash_types := null;

  -- call overloaded function and ignore my_clash_types
  ret_code:= is_pac_name_unique(app_id, pac_id, pac_type, my_clash_id, my_clash_types);

  clash_id := my_clash_id;
  return ret_code;
END;

--======================================================================
FUNCTION is_pac_name_unique(app_id number, pac_id number, pac_type varchar2,
                                   clash_id out number, clash_types out number)
RETURN boolean
IS
  my_clash_id number;
  my_clash_types number;
  test_dbobj boolean;  -- test against other database objects
  public_dsy boolean;  -- PUBLIC synonyms are in their own namespace
  dsy_scope i$sdd_dsy.scope%type;
  dummy boolean;
  cnt number;
  view_checking varchar2(1); -- Store whether jr_acc_rights.view_checking is ON
BEGIN
  my_clash_id := null;
  my_clash_types := null;
  test_dbobj := false;
  public_dsy := false;

  -- Allow elements to be 'seen' even if current user does not have SEL rights
  -- Must be re-enabled before leaving this function
  view_checking := jr_acc_rights.view_checking;
  jr_acc_rights.disable_view_checking;

  if pac_type in ('DSY', 'OSQ', 'TBL', 'SNP', 'VW', 'OOT', 'PLM', 'QTD') then
    if pac_type = 'DSY' then
      select scope 
        into dsy_scope
        from sdd_dsy
       where irid = pac_id;
      if dsy_scope = 'PRIVATE' then
        test_dbobj := true;
      else
        test_dbobj := false; -- PUBLIC synonyms are in their own namespace
        public_dsy := true;
      end if;
    else
      test_dbobj := true;
    end if;
  end if;

  if pac_type in ('ENT', 'SYN', 'TER') then
    -- Ent / Syn etc: Unusual case
    my_clash_id := is_ent_ter_syn_unique(app_id, pac_id, pac_type, my_clash_types);


  elsif pac_type in ('DST','BUN','EXT', 'PLM', 'GEM', 'JAM') then
    -- Match includes short name
    rmdbg.trace('Calling is_short_name_unique');
    my_clash_id := is_short_name_unique(app_id, pac_id, pac_type, my_clash_types);
    if my_clash_id is null and pac_type = 'PLM' then
      -- Additional test for PLMs
      select count(*)
        into cnt
        from ci_plsql_modules plm
            ,ci_module_networks modmod
       where modmod.child_module_reference = pac_id
         and modmod.parent_module_reference = plm.id
         and plm.plsql_module_type in ('PACKAGE', 'TYPE BODY');

      if cnt = 0 then
        -- non-packaged (stand-alone) PLM in same namespace as
        -- other DB objects
        my_clash_id := is_dbobj_unique(app_id, pac_id, my_clash_types);
      end if;
    end if;

  elsif test_dbobj then -- Must come after MOD test
    my_clash_id := is_dbobj_unique(app_id, pac_id, my_clash_types);
    if my_clash_id is null and pac_type in ('TBL', 'SNP', 'VW') then
      my_clash_id := is_tbl_alias_unique(app_id, pac_id, my_clash_types);
    end if;
  elsif public_dsy then -- Must come after MOD test
    my_clash_id := is_publicdsy_unique(app_id, pac_id, my_clash_types);

  elsif pac_type = 'FIL' then
    my_clash_id := is_name_unique(app_id, pac_id, pac_type, my_clash_types);
-- ## 404 CHANGES !! (no pc_long_name) ##
    if my_clash_id is null /* and pc_long_name  is not null */ then
      dummy := cimfil.is_fil_name_unique(app_id, pac_id, my_clash_id, my_clash_types);
    end if;

  elsif pac_type = 'DIA' then
    my_clash_id := is_dia_name_unique(app_id, pac_id, my_clash_types);

  elsif pac_type = 'PSE' then
    my_clash_id := is_pse_seq_unique(app_id, pac_id, my_clash_types);

  elsif pac_type in ('EOC', 'EVC', 'INC', 'AMC', 'ADC', 'GEC', 'VOB', 'ASS', 'VLI') then
    my_clash_id := is_name_unique(app_id, pac_id, pac_type, my_clash_types, true);
    if my_clash_id is null then
      my_clash_id := is_class_name_unique(app_id, pac_id, my_clash_types);
    end if;
    if my_clash_id is null then
      my_clash_id := is_class_name_unique2(app_id, pac_id, my_clash_types);
    end if;

  -- ASS is just unique in NAME now (4.0.11)
  -- elsif pac_type = 'ASS' then       -- Bug 877900 - special uniqueness check
  --   my_clash_id := is_ass_name_unique(app_id, pac_id, my_clash_types);

  elsif pac_type = 'TMS' then       -- Bug 745617, no name property, #1218870
    my_clash_id := is_TMS_unique(app_id, pac_id, my_clash_types);

  elsif pac_type = 'STE' then       
    my_clash_id := is_STE_unique(app_id, pac_id, pac_type, my_clash_types);


  elsif pac_type in ('FOL', 'APP') then
    -- APP names are no longer forced to be upper case in the model
    if app_id is not null then
      rmdbg.trace('Calling is_name_unique');
  
      -- B2126457 : ensure we get actual value of file-name case-sensitive policy, not default value 
      --my_clash_id := is_name_unique(app_id, pac_id, pac_type, my_clash_types, not jr_name.case_sensitive);
      my_clash_id := is_name_unique( app_id, pac_id, pac_type, my_clash_types,
                                     (jr_policy.get_policy('CASE_SENSITIVE_FILENAMES') = 'N') );
    else
      -- for root folders name must be unique in all root containers
      rmdbg.trace('Calling is_cel_name_unique');
      -- B2126457 : ensure we get actual value of file-name case-sensitive policy, not default value 
      --my_clash_id := is_cel_name_unique(pac_id, my_clash_types, not jr_name.case_sensitive);
      my_clash_id := is_cel_name_unique( pac_id, my_clash_types, (jr_policy.get_policy('CASE_SENSITIVE_FILENAMES') = 'N') );
    end if;

  elsif pac_type in ('PCK', 'PRJ') then
    if app_id is not null then
      -- Elements with mixed case NAMEs but case insensitive unique check
      -- As it performs an UPPER() it will be slower than other checks
      rmdbg.trace('Calling is_name_unique');
      my_clash_id := is_name_unique(app_id, pac_id, pac_type, my_clash_types, true);
    else
      -- for root packages and projects name must be unique in all root containers
      -- Elements with mixed case NAMEs but case insensitive unique check
      -- As it performs an UPPER() it will be slower than other checks
      rmdbg.trace('Calling is_cel_name_unique');
      my_clash_id := is_cel_name_unique(pac_id, my_clash_types, true);
    end if;
    if pac_type = 'PCK' and my_clash_id is null then
      -- for packages, want to check name clashes within package not owning cel hence pass
      -- pac_id as first parameter and not app_id
      my_clash_id := is_class_name_unique(pac_id, pac_id, my_clash_types);
    end if;

  elsif pac_type in ('FILES') then
    -- Elements with mixed case NAMEs but case insensitive unique check
    -- As it performs an UPPER() it will be slower than other checks
    -- B2126457 : ensure we get actual value of file-name case-sensitive policy, not default value 
    --my_clash_id := is_name_unique(app_id, pac_id, pac_type, my_clash_types, not jr_name.case_sensitive);
    my_clash_id := is_name_unique(  app_id, pac_id, pac_type, my_clash_types,
                                    (jr_policy.get_policy('CASE_SENSITIVE_FILENAMES') = 'N') );

  elsif pac_type = 'CDE' then
    my_clash_id := is_cde_unique(pac_id, my_clash_types);

  elsif pac_type in ('ACR', 'COS', 'TRS', 'COR', 'USC', 'PER') then
    -- Elements with mixed case NAMEs but case insensitive unique check
    -- As it performs an UPPER() it will be slower than other checks
    my_clash_id := is_name_unique(app_id, pac_id, pac_type, my_clash_types, true);

  else
    -- Normal : Match name in type
    --  (picks up FUN - only does half the job)
    rmdbg.trace('Calling is_name_unique');
    my_clash_id := is_name_unique(app_id, pac_id, pac_type, my_clash_types);
    if my_clash_id is null and pac_type = 'FUN' then
      my_clash_id := is_fun_seq_unique(app_id, pac_id, my_clash_types);
    end if;

  end if;

  clash_id := my_clash_id;
  clash_types := my_clash_types;

  -- Re-enable view enforced SEL access rights
  if view_checking = 'Y' then
    jr_acc_rights.enable_view_checking;
  end if;

  if my_clash_id is null then
    return true;
  else
    return false;
  end if;
EXCEPTION
  -- Just in case something goes wrong.
  WHEN OTHERS THEN
    -- Re-enable view enforced SEL access rights
    if view_checking = 'Y' then
      jr_acc_rights.enable_view_checking;
    end if;
    raise;
END;
--================================== END ====================================--

END;
/