rem
rem $Header: L:\\\\model\\repman40\\api\\RCS\\CIUTL.IPB 1.8 1998/12/30 15:02:20 loldham Exp $
rem
Rem  Copyright (c) 1993 by Oracle Corporation
Rem    NAME
Rem      ciiutl.pkb - <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     stlyon     03/07/03 -  B2877444 : Rewrite identify() and add getDetails()
Rem     stlyon     22/01/03 -  B2670691 : Rewrite adjust_datatype_sizes
Rem     loldham    30/12/98 -  Remove str_exists function
Rem     wstallar   12/15/97 -  Del_structeure_elements proc changed- removed test that
Rem                            prevented deletion of DEUs. (BUG 551571)
Rem     wstallar   12/02/97 -  Delete Elements Proc changed BUG 587922
Rem                            Exception handling for no_data_found added.
Rem     jwetherb   01/11/95 -  Optimize identify() function
Rem     jwetherb   10/25/94 -  Change identify_str()
Rem     jwetherb   10/24/94 -  Provide default for pac_name in create_appxxx
Rem     jwetherb   10/14/94 -  Optimize del_structure_element()
Rem     jwetherb   10/14/94 -  Optimize delete_structure_element()
Rem     jwetherb   10/06/94 -  Fix identify_str()
Rem     jwetherb   10/03/94 -  Add identify_str function; rm.reference->number
Rem     jwetherb   10/03/94 -  Modify 'identify' to support strel's
Rem     jwetherb   09/28/94 -  Add occur_type to el_exists function
Rem     jwetherb   09/08/94 -  substr -> substrb
Rem     jwetherb   09/01/94 -  Remove entity_type param from create_appxxx
Rem     jwetherb   08/29/94 -  Add ref_meaning to in_domain() func
Rem     jwetherb   08/25/94 -  Return max(el_id) from get_pac_ref
Rem     aheath     07/11/94 -  Creation
Rem     jwetherb   08/09/93 -  Creation

CREATE OR REPLACE PACKAGE BODY ciiutl AS

  TYPE property_nameTabTyp is table of varchar2(40) index by binary_integer;
  TYPE data_lengthTabTyp is table of number index by binary_integer;

  FUNCTION is_unique_name(new_name in varchar2, current_name_list in NameTabTyp)
  RETURN boolean;

  FUNCTION is_reg_name_unique(app_id number,
                              pac_id number,
                              pac_type varchar2,
                              clash_id out number)
  RETURN boolean;
  

--=========================== GET ROLE NAME ==============================--
-- get the user defined string that names this element
FUNCTION get_role_name(
  element_ref   number)
RETURN varchar2
IS
  element_name  varchar2(100);
BEGIN
  select max(name)
    into element_name
    from ci_roles
   where id = element_ref;
  return(element_name);
END;

--============================== IS NUMBER? =================================--

FUNCTION is_number(
  p1  varchar2)
RETURN boolean
IS
  num number;
BEGIN
  begin
    num := p1;
    return(true);
  exception
    when VALUE_ERROR then
      return(false);
  end;
END;

--============================ DELETE ELEMENT ===============================--

PROCEDURE delete_element(
  el_reference  number,
  el_type   varchar2,
--        el_ty   number,
  par_reference number    default null,
  par_type  varchar2  default null)
IS

BEGIN
  -- Should all be handled by delete triggers
  --  or 'real' foreign key constraints
  --  Will need revisiting to deal with nullifying optional references
  null;
END;

--=============================== TYPE NAME =================================--

FUNCTION type_name(short_name varchar2)
RETURN varchar2
IS
  name varchar2(128);
BEGIN
  select name
    into type_name.name
    from rm_element_types
   where short_name = type_name.short_name;
  return(name);
EXCEPTION
  when no_data_found then
    return(null);
END;

--================================= EXISTS ==================================--

FUNCTION el_exists(id number,type varchar2,
  occur_type varchar2 default null)
RETURN boolean
IS
  cnt number;
  big_str varchar2(2000);
  cur_id integer;
  ret_val integer;
  tab_name varchar2(40);
BEGIN
  tab_name := ciiexp.get_table_name(type);
  cur_id  := DBMS_SQL.OPEN_CURSOR;
  big_str := 'select count(*) from ' || tab_name || ' where irid = :id';
  dbms_sql.parse(cur_id, big_str, dbms_sql.v7);
  dbms_sql.bind_variable(cur_id, ':id', id);

  dbms_sql.define_column(cur_id, 1, cnt);
  ret_val := dbms_sql.execute(cur_id);

  if dbms_sql.fetch_rows(cur_id) > 0 then
    dbms_sql.column_value(cur_id, 1, cnt);
  end if;

  DBMS_SQL.CLOSE_CURSOR(cur_id);

  if cnt = 0 then
    return(false);
  else
    return(true);
  end if;
END;

--=========================== IN CONTEXT APPSYS =============================--

FUNCTION in_context_appsys(id number,type varchar2)
RETURN boolean
IS
  cnt number;
BEGIN
/*
 * No longer required because working in the context of a workarea
 * This code should no longer be called

  select count(*)
    into cnt
    from sdd_folder_members
   where folder_reference = cdapi.app_sys_ref
     and member_object = id;
  if cnt = 0 then
    return(false);
  else
    return(true);
  end if;
*/
  return(true);
END;

--======================== ADJUST DATATYPE SIZES ============================--
-- B2670691 : replaced to process domains containing length, precision and scale
--            definitions, where the values of the first four characters of REF_VALUES.REF_MEANING are:
-- 1 = optionality of precision or length (y=mandatory, o=optional, n=not allowed, 1..9 = warning number)
-- 2 = optionality of scale               (y=mandatory, o=optional, n=not allowed, 1..9 = warning number)
-- 3 = set to 'l' when 1 represents length
-- 4 = start of precision and scale definitions : <p_or_s>{<min>:<max>}

-- Error/Warning messages defined in repadm61\cdaus.msg   -- => proposed message

--** For some bizarre reason, scale is called precision in this function **


PROCEDURE adjust_datatype_sizes(id              in      number,                         -- => i_id
                                type_of         in      varchar2,                       -- => i_typeOf
                                format          in      varchar2,                       -- => i_format
                                maximum_length  in out  number,    -- or precision      -- => io_maxLength
                                precision       in out  number,                         -- => io_scale
                                ref_domain_name in      varchar2 default 'DATA_FORMAT', -- => i_domain
                                average_length  in      number   default 0)             -- => i_averageLength
IS
  cPRECISION_OPT  constant pls_integer := 1;    -- index to length(precision) optionality in REF_MEANING
  cSCALE_OPT      constant pls_integer := 2;    -- index to scale optionality in REF_MEANING
  cP_AND_S_DEFS   constant pls_integer := 4;    -- index to start of length and scale definitions in REF_MEANING
  cMANDATORY      constant char := 'y';
  cNOT_ALLOWED    constant char := 'n';
  cOPTIONAL       constant char := 'o';
  cWARNING_1      constant char := '1';
  cWARNING_2      constant char := '2';
  cWARNING_3      constant char := '3';
  lengthOpt       char;                         -- y, n, o, 1..9
  scaleOpt        char;                         -- y, n, o, 1..9
  lengthMin       number;
  lengthMax       number;
  scaleMin        number;
  scaleMax        number;
  el_type_of      varchar2(10) := upper(type_of);  -- Dont use nls_upper below because type_of is allways in 'US'
  ciiutl_identify varchar2(100);
  ref_system_use  REF_VALUES.REF_SYSTEM_USE%type;   -- INTO variable
  ref_meaning     REF_VALUES.REF_MEANING%type;      -- INTO variable
  startIndex      pls_integer := cP_AND_S_DEFS;     -- index to ref_meaning
  limitIndex      pls_integer := cP_AND_S_DEFS;     -- index to ref_meaning
  ok              boolean := true;
BEGIN

  if ( format is not null )
  then
    SELECT ref_meaning
        ,  ref_system_use
    INTO   ref_meaning
    ,      ref_system_use
    FROM   ref_values 
    WHERE  ref_domain = ref_domain_name
    AND    ref_code   = nls_upper(format);

    lengthOpt := substr( ref_meaning, cPRECISION_OPT, 1 );
    scaleOpt  := substr( ref_meaning, cSCALE_OPT,     1 );

    -- Extract precision and scale definitions from REF_MEANING.
    --
    if ( lengthOpt != cNOT_ALLOWED 
    and  substr( ref_meaning, startIndex, 2 ) = 'p{' )
    then
        startIndex := startIndex + 2;
        limitIndex := instr ( ref_meaning, ':', startIndex );
        lengthMin  := substr( ref_meaning, startIndex, (limitIndex-startIndex) );
        startIndex := limitIndex + 1;
        limitIndex := instr ( ref_meaning, '}',   startIndex );
        lengthMax  := substr( ref_meaning, startIndex, (limitIndex-startIndex) );
        startIndex := limitIndex + 1;
    end if;

    if ( scaleOpt != cNOT_ALLOWED 
    and  substr( ref_meaning, startIndex, 2 ) = 's{' )
    then
        startIndex := startIndex + 2;
        limitIndex := instr ( ref_meaning, ':', startIndex );
        scaleMin   := substr( ref_meaning, startIndex, (limitIndex-startIndex) );
        startIndex := limitIndex + 1;
        limitIndex := instr ( ref_meaning, '}',   startIndex );
        scaleMax   := substr( ref_meaning, startIndex, (limitIndex-startIndex) );
    end if;

--rmdbg.write_file('ref_domain_name='||ref_domain_name);
--rmdbg.write_file('format='||format);
--rmdbg.write_file('precision='||maximum_length);
--rmdbg.write_file('scale='||precision);
--rmdbg.write_file('ref_meaning='||ref_meaning);
--rmdbg.write_file('lengthMin='||lengthMin);
--rmdbg.write_file('lengthMax='||lengthMax);
--rmdbg.write_file('scaleMin='||scaleMin);
--rmdbg.write_file('scaleMax='||scaleMax);


    -- Record the fact that this is a valid format type
    rmman.record_check( 'CIH'||el_type_of||'V_FORMAT1', id, null, null, true );

    -- %0!s: Maximum length must be provided for this format type <%1!s>
    -- => %0!s: Length must be specified for type <%1!s>
    ok := true;
    if ( lengthOpt = cMANDATORY and maximum_length is null )
    then
--rmdbg.write_file('Length must be specified for type <%1!s>');
      ok := false;
    end if;
    rmman.record_check( 'CIH'||el_type_of||'V_MAX_LEN1', id, null, null, ok, 'Y',  ciiutl_identify,  format );

    -- %0!s: Length is invalid for this format type <%1!s> -- it has been reset to null
    -- => %0!s: Length is invalid for type <%1!s> -- it has been reset to null
    ok := true;
    if ( lengthOpt = cNOT_ALLOWED and maximum_length is not null )
    then
--rmdbg.write_file('Length is invalid for type <%1!s> -- it has been reset to null');
      ok := false;
      maximum_length := null;
    end if;
    rmman.record_check( 'CIH'||el_type_of||'V_MAX_LEN2', id, null, null, ok, 'Y', ciiutl_identify, format );

    -- %0!s: Maximum length is meaningless for this format type <%1!s>
    -- => %0!s: Length is meaningless for type <%1!s>
    ok := true;
    if ( lengthOpt = cWARNING_1 and maximum_length is not null )
    then
--rmdbg.write_file('Length is meaningless for type <%1!s>');
      ok := false;
    end if;
    rmman.record_check( 'CIH'||el_type_of||'V_MAX_LEN3', id, null, null, ok, 'Y', ciiutl_identify, format );

    -- %0!s: Specifying a precision for type <%1!s> may result in loss of accuracy
    --
    -- *** V_MAX_LEN6 not yet defined in RM_ASSERTIONS. Use V_MAX_LEN3 instead ***
    --
    ok := true;
    if ( lengthOpt = cWARNING_2 and maximum_length is not null )
    then
--rmdbg.write_file('Specifying a precision for type <%1!s> may result in loss of accuracy');
      ok := false;
    end if;
    --rmman.record_check( 'CIH'||el_type_of||'V_MAX_LEN6', id, null, null, ok, 'Y', ciiutl_identify, format );
    rmman.record_check( 'CIH'||el_type_of||'V_MAX_LEN3', id, null, null, ok, 'Y', ciiutl_identify, format );

    -- %0!s: Maximum length <%1!s> is larger that the allowed limit for this format type <%2!s>
    -- => %0!s: Length <%1!s> is larger than allowed for type <%2!s>
    ok := true;
    if ( maximum_length > lengthMax )   -- if either or both are NULL, result of condition is false
    then
--rmdbg.write_file('Length <%1!s> is larger than allowed for type <%2!s>');
      ok := false;
    end if;
    rmman.record_check( 'CIH'||el_type_of||'V_MAX_LEN4', id, null, null, ok,
                        'Y', ciiutl_identify, to_char(maximum_length), format );

    -- %0!s: Maximum length must be at least 1 for this format type <%1!s>
    -- => %0!s: Length must be at least <%1> for type <%2!s>
    ok := true;
    if ( maximum_length < lengthMin )   -- if either or both are NULL, result of condition is false
    then
--rmdbg.write_file('Length must be at least <%1> for type <%2!s>');
      ok := false;
    end if;
    rmman.record_check( 'CIH'||el_type_of||'V_MAX_LEN5', id, null, null, ok, 'Y', ciiutl_identify, /*lengthMin,*/ format );

    -- %0!s: Unusual format type <%1!s>
    -- => %0!s: Unusual type <%1!s>
    rmman.record_check( 'CIH'||el_type_of||'V_FORMAT2', id, null, null,
                        ref_system_use is null,
                        'Y', ciiutl_identify, format );

    -- %0!s: Precision is mandatory for this format type <%1!s>
    -- => %0!s: Scale is mandatory for type <%1!s>
    ok := true;
    if ( scaleOpt = cMANDATORY and precision is null )
    then
--rmdbg.write_file('Scale is mandatory for this format type <%1!s>');
      ok := false;
    end if;
    rmman.record_check( 'CIH'||el_type_of||'V_PRECISION1', id, null, null, ok, 'Y', ciiutl_identify, format );

    -- %0!s: Precision cannot be specified for this format type <%1!s> -- it has been reset to null
    -- => %0!s: Scale is not allowed for type <%1!s> -- it has been set to null
    ok := true;
    if ( scaleOpt = cNOT_ALLOWED and precision is not null )
    then
--rmdbg.write_file('Scale is not allowed for for type <%1!s> -- it has been set to null');
      ok := false;
      precision := null;
    end if;
    rmman.record_check( 'CIH'||el_type_of||'V_PRECISION2', id, null, null, ok, 'Y', ciiutl_identify, format );

    -- %0!s: Precision places are not meaningful for this format type <%1!s>
    -- => %0!s: Scale is meaningless for type <%1!s> -- it has been set to null
    ok := true;
    if ( scaleOpt = cWARNING_1 and precision is not null )
    then
--rmdbg.write_file('Scale is meaningless for type <%1!s> -- it has been set to null');
      ok := false;
      precision := null;
    end if;
    rmman.record_check( 'CIH'||el_type_of||'V_PRECISION3', id, null, null, ok, 'Y', ciiutl_identify, format );

    -- => %0!s: Scale is not allowed without precision for type <%1!s> -- it has been set to null
    ok := true;
    if ( scaleOpt = cWARNING_3
    and  maximum_length is null
    and  precision is not null )
    then
--rmdbg.write_file('Scale is not allowed without precision for type <%1!s> -- it has been set to null');
      ok := false;
      precision := null;
    end if;
    -- *** V_SCALE5 not yet defined in RM_ASSSERTIONS ***
    --rmman.record_check( 'CIH'||el_type_of||'V_SCALE5', id, null, null, ok, 'Y', ciiutl_identify, format );

    -- Changed to fix bug #490851.   ?? Why not have separate tests for < and >, like Length or vice-versa ??
    -- %0!s: Precision out of range for this format <%1!s>
    -- => %0!s: Scale out of range for type <%1!s>
    ok := true;
    if ( ( scaleOpt = cMANDATORY or scaleOpt = cOPTIONAL or scaleOpt between '1'and '9' )
    and  ( precision < scaleMin  or precision > scaleMax ) )
    then
--rmdbg.write_file('Scale out of range for type <%1!s>');
      ok := false;
      -- precision := scaleMax;     ??
    end if;
    rmman.record_check( 'CIH'||el_type_of||'V_PRECISION4', id, null, null, ok, 'Y', ciiutl_identify, format );

    -- Check that the average_length is less than the absolute max. for the format.
    -- If average length is more than absolute max. for the format set maximum_length to lengthMax 
    -- for the format this will trigger off the constraint CIHCOLE_AVG_MAX_LEN
    if ( el_type_of in ('COL','ATT','DOM','DIT')
    and  average_length is not null
    and  maximum_length is  null
    and  average_length > lengthMax )
    then
        maximum_length := lengthMax;
    end if;
  end if;

EXCEPTION
  WHEN no_data_found THEN
    if ( el_type_of = 'ARG' or el_type_of = 'PGD' )
    then
      -- %0!s: Unusual format type <%1!s>
      /* %0!s: Unusual type <%1!s> */
      rmman.record_check('CIH'||el_type_of||'V_FORMAT2', id, null, null,false, 'Y', ciiutl_identify, format );
    else
      -- %0!s: Invalid format type <%1!s>
      /* %0!s: Invalid type <%1!s> */
      rmman.record_check('CIH'||el_type_of||'V_FORMAT1', id, null, null,false, 'Y', ciiutl_identify, format );
    end if;

END adjust_datatype_sizes;
/******************************************************************************
-- Original version
PROCEDURE adjust_datatype_sizes(id number,type_of varchar2,
  format in varchar2,maximum_length in out number,
        precision in out number,
        ref_domain_name in varchar2 default 'DATA_FORMAT',
        average_length in number default 0)
IS
  types number;
  len_optionality varchar2(15);
  dec_optionality varchar2(15);
  unusual_type    varchar2(1);
  max_len   number;
  el_type_of    varchar2(10);
  ciiutl_identify varchar2(100);
BEGIN
 if format is not null then

  -- pre calculate identify
  --ciiutl_identify := ciiutl.identify(id);

  -- Dont use nls_upper below because type_of is allways in 'US'
  el_type_of := upper(type_of);

  -- bug 1936545 : new 9i datatypes added to decode (ref_code) : afackere 03-sep-2001
  SELECT decode(substrb(ref_meaning,1,1)
     ,'n' ,'NOT ALLOWED'
     ,'w' ,'WARNING'
     ,'o' ,'OPTIONAL'
     ,'y' ,'MANDATORY')
  ,      decode(substrb(ref_meaning,2,1)
     ,'n' ,'NOT ALLOWED'
     ,'w' ,'WARNING'
     ,'o' ,'OPTIONAL'
     ,'y' ,'MANDATORY')
  ,      ref_system_use
  ,      decode(ref_code
     ,'DECIMAL'     , 38
     ,'NUMBER'      , 38
     ,'INTEGER'     , 38
     ,'FLOAT'       , 126
     ,'RAW'         , 2000
     ,'CHAR'  , 2000
     ,'VARCHAR'     , 4000
     ,'VARCHAR2'    , 4000
     ,'NCHAR' , 2000
     ,'NVARCHAR2' , 4000
     ,'TIMESTAMP WITH TIME ZONE' , 38
     ,'TIMESTAMP WITH LOCAL TIME ZONE' , 38
     ,'INTERVAL YEAR TO MONTH' , 4000
     ,'INTERVAL DAY TO SECOND' , 4000
        , null)
  INTO   len_optionality
  ,      dec_optionality
  ,      unusual_type
  ,      max_len
  FROM   ref_values
  WHERE  ref_domain = ref_domain_name
    AND  ref_code   = format;

  -- Record the fact that this is a valid format type
  rmman.record_check('CIH'||el_type_of||'V_FORMAT1',id,null,null,true);

  -- %0!s: Maximum length must be provided for this format type <%1!s>
  rmman.record_check('CIH'||el_type_of||'V_MAX_LEN1',id,null,null,
    len_optionality != 'MANDATORY' or maximum_length is not null,
    'Y',ciiutl_identify,format);

  -- %0!s: Length is invalid for this format type <%1!s> -- it has been
  -- reset to null
  rmman.record_check('CIH'||el_type_of||'V_MAX_LEN2',id,null,null,
    len_optionality != 'NOT ALLOWED' or maximum_length is null,
    'Y',ciiutl_identify,format);
  if len_optionality = 'NOT ALLOWED' and maximum_length is not null then
    maximum_length := null;
  end if;

  -- JW Workaround a minor bug in REF_VALUES which declares INTEGER maximum
  -- length meaningless
  -- %0!s: Maximum length is meaningless for this format type <%1!s>
  rmman.record_check('CIH'||el_type_of||'V_MAX_LEN3',id,null,null,
    len_optionality != 'WARNING' or maximum_length is null or
    format = 'INTEGER',
    'Y',ciiutl_identify,format);

  -- %0!s: Maximum length <%1!s> is larger that the allowed limit for format
  -- type <%2!s>
  rmman.record_check('CIH'||el_type_of||'V_MAX_LEN4',id,null,null,
    maximum_length is null or max_len is null or maximum_length <= max_len,
    'Y',ciiutl_identify,to_char(maximum_length),format);

  -- %0!s: Maximum length must be at least 1 for this format type <%1!s>
  rmman.record_check('CIH'||el_type_of||'V_MAX_LEN5',id,null,null,
    maximum_length is null or maximum_length >= 1,
    'Y',ciiutl_identify,format);

  -- %0!s: Unusual format type <%1!s>
  rmman.record_check('CIH'||el_type_of||'V_FORMAT2',id,null,null,
    unusual_type is null,
    'Y',ciiutl_identify,format);

  -- %0!s: Precision is mandatory for this format type <%1!s>
  rmman.record_check('CIH'||el_type_of||'V_PRECISION1',id,null,null,
    dec_optionality is null or dec_optionality != 'MANDATORY' or
    precision is not null,
    'Y',ciiutl_identify,format);

  -- %0!s: Precision cannot be specified for this format type <%1!s> -- it has
  -- been reset to null
  rmman.record_check('CIH'||el_type_of||'V_PRECISION2',id,null,null,
    dec_optionality is null or dec_optionality != 'NOT ALLOWED' or
    precision is null,
    'Y',ciiutl_identify,format);
  if dec_optionality = 'NOT ALLOWED' and precision is not null then
    precision := null;
  end if;

  -- %0!s: Precision places are not meaningful for this format type <%1!s>
  rmman.record_check('CIH'||el_type_of||'V_PRECISION3',id,null,null,
    dec_optionality is null or dec_optionality != 'WARNING' or
    precision is null,
    'Y',ciiutl_identify,format);

  --Changed to fix bug #490851.
  --%0!s: Precision out of range for this format <%1!s>
  rmman.record_check('CIH'||el_type_of||'V_PRECISION4',id,null,null,
  precision is null or (precision <= 127 and precision >= -84),
  'Y',ciiutl_identify,format);

-- Check that the average_length is less than the absolute max. for the
-- datatype.
-- if average length is more than absolute max. for the datatype
-- set maximum_length to max_len for the datatype
-- this will trigger off the constraint CIHCOLE_AVG_MAX_LEN
  if el_type_of in ('COL','ATT','DOM','DIT') and
     average_length is not null and
     maximum_length is  null and
     average_length > max_len  then

     maximum_length := max_len;

  end if;

 end if;
EXCEPTION
  WHEN no_data_found THEN
    if el_type_of = 'ARG' or el_type_of = 'PGD' then
      -- %0!s: Unusual format type <%1!s>
      rmman.record_check('CIH'||el_type_of||'V_FORMAT2',id,null,null,false,
      'Y',ciiutl_identify,format);
    else
      -- %0!s: Invalid format type <%1!s>
      rmman.record_check('CIH'||el_type_of||'V_FORMAT1',id,null,null,false,
      'Y',ciiutl_identify,format);
    end if;
END;
*******************************************************************************/

--============================== CREATE APPXXX ==============================--
PROCEDURE create_appxxx(pac_type_of varchar2,pac_ref number,
  pac_name varchar2 default null,app_sys_ref number default cdapi.app_sys_ref,
  app_sys_name varchar2 default cdapi.app_sys_name)
IS
  appcon_id number;
BEGIN
  cioapp_con_interface.ins(appcon_id,pac_ref,app_sys_ref,pac_type_of);
END;

--============================== IN DOMAIN? =================================--

FUNCTION in_domain( value varchar2, domain varchar2, meaning varchar2 default null )
RETURN boolean
IS
  cnt number;
BEGIN
  if meaning is null
  then
    select count(ref_code) into cnt
    from   ref_values
    where  ref_domain = domain
    and    ref_code   = nls_upper(value);      -- B2670691 : Add nls_upper();
  else
    select count(ref_code) into cnt
    from   ref_values
    where  ref_domain  = domain
    and    ref_code    = nls_upper(value)      -- B2670691 : Add nls_upper()
    and    ref_meaning = meaning;
  end if;

  return cnt != 0;
END;

--============================================ getDetails ===========================================--
--
-- B2877444 : Added. Called from identify() and itself to get the properties of properties that are
--            references to other element types.
--
-- To change the layout from
--     Constraint (Table Name DEPT, Name PK_DEPT)
-- to
--     Constraint (Table{Name{DEPT}}, Name{PK_DEPT}) 
-- replace the statements preceeding --1 with the statements following them.
--
function getDetails( i_parentTableName  varchar2,
                     i_parentTableAlias varchar2,
                     i_parentColumnName varchar2,
                     i_typeId           number,
                     i_selectListItemNo number )
return varchar2
is
  typeShortName    rm_element_types.short_name%type;
  select_list      varchar2(32767);
  select_list_item varchar2(32767);
  from_list        varchar2(100);
  tableAlias       varchar2(30) := i_parentTableAlias || i_selectListItemNo;
  selectListItemNo number       := 1;

  -- Identical cursor to the one in identify(), but I can't work out how to declare it once and use it
  -- in both functions without getting a cusor already open error.
  --
  -- CvE 01Aug2000
  -- Translate of quote for back quote was introduced to fix bug 1369015
  --
  CURSOR c_properties( i_typeShortName varchar2 ) is
  select ex.nls_name     element_nls_name
  ,      translate(px.nls_name,'''', '`')     prop_nls_name
  ,      c.name          col_name
  ,      t.name          tab_name
  ,      to_number(null) target_type_id
  ,      p.descriptor_sequence
  from rm_sql_tables t
  ,    rm_sql_columns c
  ,    rm_properties p
  ,    rm_property_extensions px
  ,    rm_property_maps pm
  ,    rm_element_types et
  ,    rm_element_type_extensions ex
  where pm.in_column = c.id
  and c.in_table = t.id
  and pm.property = p.id
  and p.id = px.for_property
  and pm.context = et.id
  and et.product = 'CI'
  and et.short_name = i_typeShortName
  and et.id = ex.for_type
  and p.descriptor_sequence > 0
  and p.types = 3 -- non-link properties
  union all
  select ex.nls_name, translate(px.nls_name,'''', '`'), c.name, t.name , et2.id, p.descriptor_sequence
  from rm_sql_tables t
  ,    rm_sql_columns c
  ,    rm_property_extensions px
  ,    rm_link_properties p
  ,    rm_link_properties p2
  ,    rm_property_maps pm
  ,    rm_element_types et
  ,    rm_element_types et2
  ,    rm_element_type_extensions ex
  where pm.in_column = c.id
  and c.in_table = t.id
  and pm.property = p.id
  and p.id = px.for_property
  and pm.context = et.id
  and p.link_type = p2.link_type
  and p.id != p2.id
  and p2.defined_against = et2.id
  and et.product = 'CI'
  and et.short_name = i_typeShortName
  and et.id = ex.for_type
  and p.descriptor_sequence > 0
  order by 6;

begin

  select et.short_name
  into   typeShortName
  from   rm_element_types et
  where  et.id = i_typeId;

  for ep in c_properties( typeShortName )
  loop

    if ( selectListItemNo = 1 )
    then
      select_list := '(select decode(';
      from_list   := ') from  '||ep.tab_name||' '||tableAlias;
    else
      select_list :=   select_list || '||'', '' ) || decode(';
      --1 select_list :=   select_list || '||''}, '' ) || decode(';
    end if;

    if ( ep.target_type_id IS NOT NULL )
    then
      select_list_item := getDetails( ep.tab_name, tableAlias, ep.col_name, ep.target_type_id, selectListItemNo );
    else
      select_list_item := tableAlias||'.'||ep.col_name||' ||''''';
      --1 select_list_item := tableAlias||'.'||ep.col_name||' ||''}''';
    end if;

    select_list :=   select_list
                || select_list_item
                || ', null, null, '''
                || ep.prop_nls_name
                || ' ''||'                  --1 || '{''||'
                || select_list_item;


    selectListItemNo := selectListItemNo + 1;

  end loop;

  return   select_list
        || from_list
        || ' where '||tableAlias||'.irid = (select '||i_parentColumnName
        ||                                ' from '  ||i_parentTableName
        ||                                ' where irid = '||i_parentTableAlias||'.irid))';

end getDetails;

--=============================== IDENTIFY ==================================--

function identify( id number, types number ) return varchar2
is
  typeShortName rm_element_types.short_name%type;
begin
   select et.short_name
   into   typeShortName
   from   rm_element_types et
   where  et.id = identify.types;
   
  return identify( id, typeShortName );
end identify;

--=============================== IDENTIFY ==================================--

function identify( id number, short_name varchar2 ) return varchar2
is
  cur_id integer := dbms_sql.open_cursor;
  ret_val integer;
  p_name varchar2(40);
  p_descriptor number;
  fk_id number;
  select_list      varchar2(32767);
  select_list_item varchar2(16384);
  selectListItemNo number := 1;
  from_list        varchar2(100);
  descriptor       varchar2(2000);
  
  -- Identical cursor to the one in getDetails(), but I can't work out how to declare it once and use it
  -- in both functions without getting a cusor already open error.
  --
  -- CvE 01Aug2000
  -- Translate of quote for back quote was introduced to fix bug 1369015
  --
  CURSOR c_properties( i_typeShortName varchar2 ) is
  select ex.nls_name     element_nls_name
  ,      translate(px.nls_name,'''', '`')     prop_nls_name
  ,      c.name          col_name
  ,      t.name          tab_name
  ,      to_number(null) target_type_id
  ,      p.descriptor_sequence
  from rm_sql_tables t
  ,    rm_sql_columns c
  ,    rm_properties p
  ,    rm_property_extensions px
  ,    rm_property_maps pm
  ,    rm_element_types et
  ,    rm_element_type_extensions ex
  where pm.in_column = c.id
  and c.in_table = t.id
  and pm.property = p.id
  and p.id = px.for_property
  and pm.context = et.id
  and et.product = 'CI'
  and et.short_name = i_typeShortName
  and et.id = ex.for_type
  and p.descriptor_sequence > 0
  and p.types = 3 -- non-link properties
  union all
  select ex.nls_name, translate(px.nls_name,'''', '`'), c.name, t.name , et2.id, p.descriptor_sequence
  from rm_sql_tables t
  ,    rm_sql_columns c
  ,    rm_property_extensions px
  ,    rm_link_properties p
  ,    rm_link_properties p2
  ,    rm_property_maps pm
  ,    rm_element_types et
  ,    rm_element_types et2
  ,    rm_element_type_extensions ex
  where pm.in_column = c.id
  and c.in_table = t.id
  and pm.property = p.id
  and p.id = px.for_property
  and pm.context = et.id
  and p.link_type = p2.link_type
  and p.id != p2.id
  and p2.defined_against = et2.id
  and et.product = 'CI'
  and et.short_name = i_typeShortName
  and et.id = ex.for_type
  and p.descriptor_sequence > 0
  order by 6;

begin
  --rmdbg.write_file('===========================================================================');
  --rmdbg.write_file('Element Id='||to_char(identify.id)||', Type Short Name='||short_name);

  -- B2877444 : rewritten to recursively replace properties that reference other element types with
  --            the properties of those element types.

  for ep in c_properties( short_name )
  loop

    if ( selectListItemNo = 1 )
    then
      select_list := 'select '''||ep.element_nls_name||' [''';
      from_list   := ')||'') ]'' from  '||ep.tab_name||' t1';
    else
      select_list :=   select_list || '||''),'' ) ';
    end if;

    if ( ep.target_type_id IS NOT NULL )
    then
      select_list_item := getDetails( ep.tab_name, 't1', ep.col_name, ep.target_type_id, selectListItemNo );
    else
      select_list_item := 't1.'||ep.col_name;
    end if;

/*
declare
  y integer := 1;
begin
  while y < length(select_list_item)
  loop
    rmdbg.write_file(selectListItemNo||'::'||substr(select_list_item,y,100));
    y := y + 100;
  end loop;
  rmdbg.flush_trace;
end;
*/

    -- The decode means that null values do not get reported.
    select_list :=   select_list
                  || ' || decode('
                  || select_list_item
                  || ', null, null, '' '
                  || ep.prop_nls_name
                  || ' (''||'
                  || select_list_item;

    selectListItemNo := selectListItemNo + 1;

  end loop;

  select_list := select_list ||from_list|| ' where t1.irid = :id';

/*
declare
  y integer := 1;
begin
  rmdbg.write_file('-----------------select_list('||length(select_list)||')-----------------------------------------');
  while y < length(select_list)
  loop
    rmdbg.write_file(substr(select_list,y,100));
    y := y + 100;
  end loop;
  rmdbg.flush_trace;
end;
*/

  if ( selectListItemNo <> 1 )
  then
    dbms_sql.parse(cur_id, select_list, dbms_sql.native);
    dbms_sql.bind_variable(cur_id, ':id', identify.id);
    dbms_sql.define_column(cur_id, 1, descriptor, 2000);
    ret_val := dbms_sql.execute(cur_id);

    if ( dbms_sql.fetch_rows(cur_id) > 0 )
    then
      dbms_sql.column_value(cur_id, 1, descriptor);
    end if;

    dbms_sql.close_cursor(cur_id);

    if ( descriptor is not null )
    then
      -- B3427377 : return value of function is expected to e a maximum of 240 characters in many places so
      --            we need to truncate it here.
      if ( length(descriptor) > 240 )
      then
        return substr(descriptor, 1, 236) || ' ...';
      else
      	return descriptor;
      end if;
    end if;

  end if;

  return('<unknown element with id = '|| identify.id ||'>');

end identify;


FUNCTION nls_type_name(type_name varchar2)
RETURN varchar2
IS
  nls_name varchar2(240);
BEGIN
  -- Get the NLS string of the element type name to display to the user

  select max(ex.nls_name) into nls_type_name.nls_name
  from rm_element_type_extensions ex, rm_element_types et
  where et.id = ex.FOR_TYPE
  and et.name = nls_type_name.type_name;

  return(nls_name);
END;

FUNCTION nls_type_plural_name(type_name varchar2)
RETURN varchar2
IS
  nls_plural_name varchar2(240);
BEGIN
  -- Get the NLS string of the element type plural name to display to the user

  select max(ex.nls_plural_name) into nls_type_plural_name.nls_plural_name
  from rm_element_type_extensions ex, rm_element_types et
  where et.id = ex.FOR_TYPE
  and et.name = nls_type_plural_name.type_name;

  return(nls_plural_name);
END;

FUNCTION nls_property_name(type_name varchar2, property_name varchar2)
RETURN varchar2
IS
  nls_name varchar2(240);
BEGIN
  -- Get the NLS string of the property name to display to the user

  select max(px.nls_name) into nls_property_name.nls_name
  from rm_property_extensions px, rm_properties pr,
  rm_property_maps pm, rm_element_types et
  where et.id = pm.context
  and pm.property = pr.id
  and pr.id = px.for_property
  and et.name = nls_property_name.type_name
  and pr.name = nls_property_name.property_name;

  return(nls_name);
END;

FUNCTION nls_type_name_s(type_name varchar2)
RETURN varchar2
IS
  nls_name varchar2(240);
BEGIN
  select max(net.NLS_NAME) into nls_name
  from rm$nls_element_types net,
       rm_element_types et,
       rm_repositories rm
  where net.ETID = et.id
    and net.NLS_LANGUAGE = rm.NLS_LANGUAGE
    and et.short_name = type_name;

  return(nls_name);
END;

FUNCTION nls_type_plural_name_s(type_name varchar2)
RETURN varchar2
IS
  nls_pluraL_name varchar2(240);
BEGIN
  select max(net.NLS_PLURAL_NAME) into nls_plural_name
  from rm$nls_element_types net,
       rm_element_types et,
       rm_repositories rm
  where net.ETID = et.id
    and net.NLS_LANGUAGE = rm.NLS_LANGUAGE
    and et.short_name = type_name;

  return(nls_plural_name);
END;

FUNCTION is_reserved_word(word in varchar2) RETURN boolean
IS
  n_cnt number;
BEGIN
  select count(*) into n_cnt from ref_values
  where  ref_domain = 'RESERVED_NAMES' AND  ref_code = is_reserved_word.word;

  if n_cnt = 0 then
    return(true);
  else
    return(false);
  end if;

END;

-- Returns new_name, appended with a number to make it unique in the
-- Table of existing names - current_name_list
-- Assumes all names are length 30
-- Note name_list must be indexed consecutively from 1
FUNCTION unique_name(new_name in varchar2, current_name_list in NameTabTyp, type_name in varchar2)
RETURN varchar2
IS
  sequence_num  integer := 1;
  suffix        varchar2(100) := null;
  revised_name  varchar2(100);
BEGIN
  revised_name := substrb(new_name,1,30);
  loop
    if is_unique_name(revised_name, current_name_list) then
      return(revised_name);
    end if;
    sequence_num := sequence_num + 1;
    suffix := '_' || sequence_num;
    if lengthb(suffix) > 24 then   -- Not likely !!
      ciierr.fatal(1357, type_name, new_name);
    end if;
    revised_name := substrb(new_name, 1, 30 - lengthb(suffix) ) || suffix;
  end loop;
END;

FUNCTION is_unique_name(new_name in varchar2, current_name_list in NameTabTyp)
RETURN boolean
IS
  loop_count number := 1;
BEGIN
  loop
    if current_name_list(loop_count) = new_name then
      return(false);
    end if;
    loop_count := loop_count +1;
  end loop;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    return (true);  -- Fallen off the end - must be unique now!
END;

--====================== POPULATE_NAME_PROPERTIES ===================--
-- Get the naming properties for this type.
PROCEDURE populate_name_properties(type_id IN number, type_short_name IN varchar2,
                                   property_name OUT property_nameTabTyp,
                                   data_length OUT data_lengthTabTyp, max_property_name OUT number)
IS
  special_case boolean := true;

  cursor candidate_properties (type_id number) is
    select py.name, py.descriptor_sequence, dt.data_length
      from rm_properties py
          ,rm_property_maps pm
          ,rm_data_types dt
     where py.id = pm.property
       and dt.id = py.of_domain
       and pm.context = type_id
       and dt.data_type = 'VARCHAR2'
       and py.descriptor_sequence > 0
       and dt.name != 'REFERENCE'
       and py.name != 'TYPE'
       and not exists (select null
                         from rm_data_type_values dtv
                        where dtv.for_data_type = dt.id)
     order by py.descriptor_sequence;
BEGIN
  max_property_name := 0;
  -- Deal with special cases
  if type_short_name in ('ENT') then
    property_name(1) := 'NAME';
    property_name(2) := 'PLURAL';
    property_name(3) := 'SHORT_NAME';
    max_property_name := 3;
  elsif type_short_name in ('FUN') then
    property_name(1) := 'FUNCTION_LABEL';
    property_name(2) := 'SEQUENCE_IN_PARENT';
    max_property_name := 2;
  elsif type_short_name in ('BUN', 'GEM', 'JAM') then
    property_name(1) := 'NAME';
    property_name(2) := 'SHORT_NAME';
    max_property_name := 2;
  elsif type_short_name in ('PLM') then
    property_name(1) := 'NAME';
    property_name(2) := 'SHORT_NAME';
    property_name(3) := 'IMPLEMENTATION_NAME';
    max_property_name := 3;
  elsif type_short_name in ('DST', 'EXT') then
    property_name(1) := 'NAME';
    property_name(2) := 'SHORT_IDENTIFIER';
    max_property_name := 2;
  elsif type_short_name in ('RFI', 'SFI') then
    property_name(1) := 'NAME';
    property_name(2) := 'FULL_PATHNAME';
    max_property_name := 2;
  elsif type_short_name in ('TBL', 'SNP', 'VW') then
    property_name(1) := 'NAME';
    property_name(2) := 'ALIAS';
    max_property_name := 2;
  elsif type_short_name in ('DIA') then
    property_name(1) := 'NAME';
    max_property_name := 1;
  elsif type_short_name in ('MCN') then
    property_name(1) := 'USAGE_SEQUENCE';
    max_property_name := 1;
  elsif type_short_name in ('EOC', 'EVC', 'INC', 'AMC', 'ADC', 'GEC', 'VOB') then
    property_name(1) := 'NAME';
    property_name(2) := 'CLASS_NAME';
    max_property_name := 2;
  else
    special_case := false;
  end if;

  if special_case then
    -- Get property lengths
    for py_index in 1..max_property_name loop
      select min(dt.data_length)
        into data_length(py_index)
        from rm_properties py
            ,rm_property_maps pm
            ,rm_data_types dt
       where py.id = pm.property
         and dt.id = py.of_domain
         and pm.context = type_id
         and py.name = property_name(py_index);
    end loop;
  else
    -- We can get property and length in one query.
    for py in candidate_properties(type_id) loop
      max_property_name := max_property_name + 1;
      property_name(max_property_name) := py.name;
      data_length(max_property_name) := py.data_length;
    end loop;
  end if;
END;

--========================= IS SAC AUTOCHECKED ===========================--
-- Returns true if the supplied SAC type has a unique key defined for it

FUNCTION is_sac_autochecked(type_id in number)
return boolean is
  cursor auto_check (type_id in varchar2) is
    select null
      from rm_element_types et
     where et.id = auto_check.type_id
        and not exists (
       select null
         from rm_sql_tables tab
             ,rm_sql_row_types rt
             ,user_constraints uc
        where tab.id = rt.table_mapped
          and rt.id = et.primary_row_type
          and uc.table_name = 'I$' || tab.name
          and uc.deferred = 'DEFERRED'
       and uc.constraint_type = 'U');
begin
  for i in auto_check(type_id) loop
    return false;
  end loop;
  return true;
end;

--========================= IS SAC UNIQUE AUTO ===========================--
-- Returns true if the supplied SAC is unique
FUNCTION is_sac_unique_auto(type_id in number)
return boolean is
  cursor constraint_names(tab_name varchar2) is
    select uc.constraint_name
      from user_constraints uc
     where uc.table_name = tab_name
       and uc.constraint_type = 'U'
       and uc.deferred = 'DEFERRED';

  cur_id integer;
  ret_val integer;
  big_str varchar2(200);
  table_name varchar2(40);
begin
  table_name := ciiexp.get_repos_table_name(type_id);
  -- dbms_output.put_line('ISU2 ' || to_char(sysdate, 'SSSSS'));
  for cons in constraint_names(table_name) loop
  -- dbms_output.put_line('ISU3 ');
    big_str := 'set constraint ' || cons.constraint_name || ' immediate';
    cur_id  := DBMS_SQL.OPEN_CURSOR;
    dbms_sql.parse(cur_id, big_str, dbms_sql.native);
    ret_val := dbms_sql.execute(cur_id);
    DBMS_SQL.CLOSE_CURSOR(cur_id);
  -- dbms_output.put_line('ISU4 ');

    big_str := 'set constraint ' || cons.constraint_name || ' deferred';
    cur_id  := DBMS_SQL.OPEN_CURSOR;
    dbms_sql.parse(cur_id, big_str, dbms_sql.native);
    ret_val := dbms_sql.execute(cur_id);
    DBMS_SQL.CLOSE_CURSOR(cur_id);
  -- dbms_output.put_line('ISU5 ');
  end loop;
  return true;
exception
  when DUP_VAL_ON_INDEX then
  -- dbms_output.put_line('ISU6 ');
    DBMS_SQL.CLOSE_CURSOR(cur_id);
    -- Set to immediate failed so still deferred
    return false;
end;

--========================= IS SAC UNIQUE ===========================--
-- Returns true if the specified SAC element is unique
--    irid            - the element to be checked
--    type_short_name - the short name of the type of the element (EG 'ENT')
--    type_id         - the id of the type of the element
FUNCTION is_sac_unique(irid in number,
                       type_short_name in varchar2,
                       type_id in number)
RETURN boolean IS
  cur integer := dbms_sql.open_cursor;
  ret_val integer;
  big_str varchar2(200);
  outparam number;
begin
  -- dbms_output.put_line('ISU1 ' || type_short_name || ' ' || to_char(sysdate, 'SSSSS'));

  if is_sac_autochecked(type_id) and type_short_name not in ('PGD') then
    return is_sac_unique_auto(type_id);
  else
    -- Hand written SAC unique checks are done in a Designer-only package
    --  Called via dynamic SQL so a core install can compile
    big_str := 'BEGIN cimqsu.is_sac_unique_hand(:irid, :type_short_name, :type_id, :outparam); END;';
    --dbms_output.put_line('ISU1 ' || to_char(sysdate, 'SSSSS'));
    dbms_sql.parse(cur, big_str, DBMS_SQL.NATIVE);
    --dbms_output.put_line('ISU2');
    dbms_sql.bind_variable(cur, 'irid', is_sac_unique.irid);
    dbms_sql.bind_variable(cur, 'type_short_name', is_sac_unique.type_short_name);
    dbms_sql.bind_variable(cur, 'type_id', is_sac_unique.type_id);
    dbms_sql.bind_variable(cur, 'outparam', outparam);
    --dbms_output.put_line('ISU3');
    ret_val := dbms_sql.execute(cur);    
    --dbms_output.put_line('ISU4');
    dbms_sql.variable_value(cur, 'outparam', outparam);
    --dbms_output.put_line('ISU5');
    dbms_sql.close_cursor(cur);
    --dbms_output.put_line('ISU6');
    if outparam = 1 then
      return true;
    else
      return false;
    end if;
  end if;
end;

--========================= IS ELEM UNIQUE ===========================--
-- Returns true if the specified element is unique
--    irid            - the element to be checked
--    type_short_name - the short name of the type of the element (EG 'ENT')
--    type_id         - the id of the type of the element
--    access_type     - 'PAC' or 'SAC'
--    container_ref   - The container to be tested for uniqueness (For PACs only)
FUNCTION is_elem_unique(irid in number,
                        type_short_name in varchar2,
                        type_id in number,
                        access_type in varchar2,
                        container_ref in number default null,
            is_reg_type in boolean default false)
RETURN boolean IS
  clash_id number;  -- Returned from pac check
  clash_folder number;  -- Returned from mun check
begin
  -- An exception is made for Database Synonyms which are SACs but have PAC-like 
  --  name uniqueness. As the parent PAC may be shared into many CELs already, 
  --  all are checked.
  
  if is_reg_type then
    return is_reg_name_unique(container_ref, irid, type_short_name, clash_id);
  elsif type_short_name = 'DSY' then
    for cel in (select fm.folder_reference id
                  from ci_folder_members fm
                      ,ci_database_synonyms dsy
                 where fm.member_object = dsy.pac_reference
                   and dsy.irid = is_elem_unique.irid) loop
      if cimappcon.is_pac_name_unique(cel.id, irid, type_short_name, clash_id) = false then
        return false;
      end if;
    end loop;
    return true;
  elsif type_short_name in ('RMC', 'RLV') then
    -- MUN uniqueness check
    return cimappcon.is_mun_unique(irid, type_short_name, null, clash_id, clash_folder)
       AND cimappcon.is_pac_name_unique(container_ref, irid, type_short_name, clash_id);
  elsif type_short_name in ('SMC', 'SLV', 'SUB') then
    -- MUN uniqueness check
    return cimappcon.is_mun_unique(irid, type_short_name, null, clash_id, clash_folder);
  elsif access_type in ('SHR', 'NSHR', 'PAC', 'CEL') then
    -- PAC uniqueness check
    return cimappcon.is_pac_name_unique(container_ref, irid, type_short_name, clash_id);
  else
    -- SAC uniqueness check
    return ciiutl.is_sac_unique(irid, type_short_name, type_id);
  end if;
end;

--============================  NAME MANGLE  =============================--
-- Called by all ocode just after the insert of a copy is performed, but
-- before the deep parts.
-- Only checks for uniqueness in the owning Container (ie assumes it is not
--  shared in anywhere else (which is true for a new copy)
-- An exception is made for Database Synonyms which are SACs but have PAC-like 
--  name uniqueness. As the parent PAC may be shared into many CELs already, 
--  all are checked.
PROCEDURE name_mangle(irid in number, 
                      type_id in number,
            is_reg_type in boolean default false)
IS
  TYPE original_property_valueTabTyp is table of varchar2(100) index by binary_integer;

  table_name                   varchar2(40);                  -- EG 'SDD_ENT'
  type_short_name              varchar2(30);                  -- EG 'ENT'
  property_name                property_nameTabTyp;           -- EG 'NAME'
  max_property_name            number := 0;
  original_property_name_val1  varchar2(100);
  original_property_name_value original_property_valueTabTyp; -- EG 'FRED'
  data_length                  data_lengthTabTyp;
  extension_str                varchar2(100);           -- 
  start_str                    varchar2(100);           -- 
  acc_type                     varchar2(10);     -- EG PAC, SAC
  container_ref                number;
  clash_id                     number;
  cnt                          number;
  cur_id                       integer;
  ret_val                      integer;
  big_str                      varchar2(2000);
  pos1               number;

BEGIN
  --dbms_output.put_line('NM1 ');

  select short_name
    into type_short_name
    from rm_element_types
   where id = name_mangle.type_id;

  acc_type := ciisty.supertype(type_short_name, irid);
  
  if acc_type in ('SHR', 'NSHR', 'CEL') then
    select max(folder_reference)
      into container_ref
      from sdd_folder_members
     where member_object = name_mangle.irid
       and ownership_flag = 'Y';
  end if;
    --dbms_output.put_line('NM2 ');

  populate_name_properties(type_id, type_short_name, property_name, data_length, max_property_name);

  -- There's nothing we can do if there is no candidate property
  if max_property_name = 0 then
    return;
  end if;

  -- If it's unique already, then do no more
  if ciiutl.is_elem_unique(irid, type_short_name, type_id, acc_type, container_ref, is_reg_type) then
    return;
  end if;
    --dbms_output.put_line('NM3 ');

  table_name := ciiexp.get_table_name(type_id);

  cur_id  := DBMS_SQL.OPEN_CURSOR;
  -- for each of the properties in the unique identifier of the element type,
  --  get the current value
  for prop in 1..max_property_name loop

    --dbms_output.put_line('NM7 ' || table_name || '.' || property_name(prop));

    big_str := 'select ' || property_name(prop) || ' from ' || table_name || ' tn where tn.irid = :irid';

    -- Can't use uninitialised array members in dbms_sql.define_column, so use ...val1 instead
    dbms_sql.parse(cur_id, big_str, dbms_sql.native);
    dbms_sql.bind_variable(cur_id, ':irid', irid);
    dbms_sql.define_column(cur_id, 1, original_property_name_val1, 100);

    ret_val := dbms_sql.execute(cur_id);

    if dbms_sql.fetch_rows(cur_id) > 0 then
      dbms_sql.column_value(cur_id, 1, original_property_name_val1);
    end if;

    -- Make sure that original_property_name_value is shorter than max length, so we
    -- can add a suffix to make it unique.
    original_property_name_value(prop) :=
                            substrb(original_property_name_val1, 1, data_length(prop) -2);
    -- dbms_output.put_line('NM8 ' || original_property_name_value(prop) || ' ' || data_length(prop));
  end loop;
  DBMS_SQL.CLOSE_CURSOR(cur_id);

  cnt := 1;

  cur_id  := DBMS_SQL.OPEN_CURSOR;
  -- Make 9 attempts at making a unique name
  while cnt <= 9 loop
    -- update each of the properties in the unique identifier of the element type
    for prop in 1..max_property_name loop
      big_str := 'update ' || table_name || ' tn set tn. ' || property_name(prop) || ' = :new_name ';
      big_str := big_str || ' where tn.irid = :irid';
      dbms_sql.parse(cur_id, big_str, dbms_sql.native);
    -- B1272764 if name value is empty set as empty, rather than defauling a '_'||cnt as the name
      if original_property_name_value(prop) is null then
        dbms_sql.bind_variable(cur_id, ':new_name', '');              
      elsif property_name(prop) like '%SEQUENCE' 
         or property_name(prop) in ('SEQUENCE_IN_PARENT') then
        dbms_sql.bind_variable(cur_id, ':new_name', original_property_name_value(prop) + cnt);
      else
        if type_id = 4819 then -- if we are dealing with files (4819) make sure the unique char is prior to the extension
          pos1 := instr(original_property_name_value(prop), '.',-1,1);
          if pos1 != 0 then
            extension_str := original_property_name_value(prop);
            extension_str := substr(extension_str,pos1);
            start_str := substr(original_property_name_value(prop),1,pos1-1);
            dbms_sql.bind_variable(cur_id, ':new_name', start_str||'_' || cnt || extension_str);
          else
            dbms_sql.bind_variable(cur_id, ':new_name', original_property_name_value(prop) || '_' || cnt);
          end if;
        else
          dbms_sql.bind_variable(cur_id, ':new_name', original_property_name_value(prop) || '_' || cnt);  
        end if;
      end if;
      dbms_sql.bind_variable(cur_id, ':irid', irid);
      ret_val := dbms_sql.execute(cur_id);
    end loop;

    cnt := cnt+1;

    if ciiutl.is_elem_unique(irid, type_short_name, type_id, acc_type, container_ref, is_reg_type) then
      exit;
    end if;

  end loop;
  DBMS_SQL.CLOSE_CURSOR(cur_id);

END;


FUNCTION is_reg_name_unique(app_id number,
                            pac_id number,
                            pac_type varchar2,
                            clash_id out number)
  -- 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 boolean
IS
  my_clash_id number;
  my_clash_types number;
  big_str varchar2(1000);
  cur_id integer;
  ret_val integer;
  tab_name varchar2(40);
  case_independent boolean := false;
  descriptor_name varchar2(40);

  cursor cur_descriptors (type_short_name varchar2) is
    select p.name
    from rm_element_types e, rm_properties p
   where e.short_name = type_short_name
     and p.defined_against = e.id
     and p.descriptor_sequence is not null;

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);

    big_str :=
    'select max(other_el.irid), max(other_el.types)' ||
    '  from ' || tab_name || ' other_el' ||
    '      ,' || tab_name || ' my_el' ||
    ' where my_el.irid = :pac_id' ||
    '   and other_el.irid != my_el.irid';

    -- Note - there will be at least one descriptor, or this function wouldn't have been called
    for rec_descriptors in cur_descriptors(is_reg_name_unique.pac_type) loop
      if case_independent then
        big_str := big_str ||  '   and upper(other_el.' || rec_descriptors.name || ') = upper(my_el.' || rec_descriptors.name || ')';
      else
        big_str := big_str ||  '   and other_el.' || rec_descriptors.name || ' = my_el.' || rec_descriptors.name;
      end if;
  end loop;

  --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);

  if my_clash_id is null then
    return true;
  else
    clash_id := my_clash_id;
    return false;
  end if;
END is_reg_name_unique;

--=================================== END ===================================--
--
-- Package instantiation block
--
BEGIN
  is_installed := true;
END;
/