rem 
rem $Header: L:\\\\model\\repman40\\api\\RCS\\ciueet.opb 1.1 1998/05/28 16:56:52 mfrobins Exp $ 
rem 
rem Copyright (c) Oracle Corporation 1991.  All Rights Reserved.
rem
rem  NAME
rem    ciueet.opb             - Operation Package Body
rem  DESCRIPTION
rem    This package is responsible for managing UE element
rem    type extensions to the CASE Repository.
rem  PUBLIC PROCEDURE(S)
rem    ins      - INSert a new extended element type
rem    upd      - UPDate an existing element type
rem    del      - DELete an existing element type
rem    publish  - PUBLISH an existing element type
rem  PRIVATE PROCEDURE(S)
rem    preprocess       - Preprocess property values
rem    constraint_check - Validate constraints
rem    check_access     - Validate access to element type
rem  NOTES
rem  MODIFIED     (MM/DD/YY)
rem    loldham    11/23/00 -  Fix bug 1511311, preserve name property in preprocess
rem    hdodswor   05/05/2000  Fix bug 1283749, doesn't assume that the 2nd and subsequent characters of the short name are
rem                           numeric
rem    bferris    03/29/95 -  Fixed nls_name, nls_plural_name on update
rem    bferris    03/15/95 -  Added nls_name, nls_plural_name
Rem    aheath     02/27/95 -  Make text usages extended
Rem                           The pre-production release
Rem    bferris    02/24/95 -  Fixes for APPxxx
Rem    aheath     02/14/95 -  Merged into api release structure
rem    bferris    12/22/94 -  Creation


CREATE OR REPLACE PACKAGE BODY cioue_element_type IS

/*  PRIVATE GLOBALS  */

  et_et_id number;      -- ELEMENT_TYPE element type ID
  et_ee_id number;      -- EXTENDED_ELEMENT element type ID
  et_ees_id number;     -- EXTENDED_ELEMENT_SUPERTYPE element type ID
  et_ac_id number;      -- APPLICATION_CONNECT element type ID
  ee_primary_row_type number;   -- EE Primary row type (for ins)
  ee_help_tag         varchar2(10); -- EE Help tag
  ac_primary_row_type number;   -- AC Primary row type (for ins)
  ac_help_tag         varchar2(10); -- AC Help tag
  dict_lang           varchar2(3);  -- Configured dictionary language

/*  END OF PRIVATE GLOBALS  */

/*  PRIVATE PROCEDURE PROTOTYPES  */

/*--------------------------------------------------------------------------*/
/*  preprocess - Preprocess property values                                 */
procedure preprocess(pl IN OUT data,isins IN boolean default false);

/*--------------------------------------------------------------------------*/
/*  constraint_check - Validate constraints                                 */
procedure constraint_check(pl IN data, isins IN boolean);

/*--------------------------------------------------------------------------*/
/*  check_access - Validate access to an element type                       */
procedure check_access(id IN number, care_if_pub IN varchar2 default 'N');
-- care_if_pub = 'N'  - must not be published
-- care_if_pub = 'M'  - must be published
-- care_if_pub = 'D'  - Don't care if published or not

/*--------------------------------------------------------------------------*/
/* check the short name is a valid 'shape' and unused */
procedure short_name_check(short_name IN varchar2);

/*  END OF PRIVATE PROCEDURE PROTOTYPES  */

/*  PUBLIC PROCEDURES  */

/*
^L
*/
/*-------------------------------- ins -------------------------------------*/
/*
  NAME
    ins - INSert a new extended element type.
  DESCRIPTION
    This procedure create a new extended element type

    The pl parameter should contain the initial property values for the
    new extended element type.  Upon successful return the data.v.id
    member will be updated to contain the ID of the new element type.
  NOTES
  EXCEPTION
    ueet_invalid_sn   - Invalid short name
    ueet_miss_sn      - Missing short name
    ueet_duplicate_sn - Duplicate short name
    ueet_miss_nm      - Missing long name
    ueet_duplicate_nm - Duplicate long name
    ueet_miss_pn      - Missing plural name
    ueet_duplicate_pn - Duplicate plural name
  RETURNS
*/

procedure ins(id IN number, pl IN OUT data) is
  appetid number;
begin

  --  Preprocess and check constraints

  preprocess(pl,true);

  constraint_check(pl,true);


  --  Seems to be OK...  Allocate an ID if required
  pl.i.id := true;   
  pl.v.id := id;
  
  if pl.v.id is null then     
    pl.v.id := jr_util.get_new_irid;
  end if;    

  --
  --  Create the element type rows
  --

  --  Insert into RM_ELEMENT_TYPES

  insert into rm_element_types 
         (
          id,
          types,
          supertypes,
          date_created,
          created_by,
          number_of_times_modified,
          product,
          short_name,
          name,
          plural_name,
          abstract,
          primary_row_type,
          user_extension,
          is_primary,
          versionable,
          icon
         )
  values (
          pl.v.id,
          et_et_id,
          et_ees_id,
          sysdate,
          user,
          1,
          'CI',
          pl.v.short_name,
          pl.v.name,
          pl.v.plural_name,
          'N',
          ee_primary_row_type,
          'YYN',
          'Y',
          'Y',
          'user_def_type'
         );

  --  Insert into RM$NLS_ELEMENT_TYPES

  insert into rm$nls_element_types 
         (nls_language,
          etid,
          nls_name,
          nls_plural_name
         )
  values (
          'US',
          pl.v.id,
          pl.v.nls_name,
          pl.v.nls_plural_name
         );
  
  if dict_lang != 'US' then
    insert into rm$nls_element_types
         (nls_language,
          etid,
          nls_name,
          nls_plural_name
         )
  values (
          dict_lang,
          pl.v.id,
          pl.v.nls_name,
          pl.v.nls_plural_name
         );
  end if;

  --  Insert into RM$ELEMENT_TYPE_EXTENSIONS

  insert into rm$element_type_extensions
         (etid   ,help_tag)
  values (pl.v.id,ee_help_tag);

  --
  --  Duplicate the properties, their data types, and their property maps...
  --


  ciue_util.dup_properties(et_ee_id,pl.v.id);


  --
  --  Duplicate the text type usages
  --

  ciue_util.dup_text_usages(et_ee_id,pl.v.id);


  -- AH the text usages should be extended. Should be data stored
  -- against UEE but for now just update the created rows
  update rm$text_usages
  set is_extended = 'Y'
  where etid = pl.v.id;


  /* Louise Oldham 12th October 1999
     ************************************************************************
     Should not do this in 6.5 because all APPSYS elements are of type APPXXX
     ************************************************************************
  --
  --  Create the APPxxx rows so we can share this extended
  --  element type and so it appears in the matrix diagrammer.
  --
  --  This involves creating another structure element type
  --  called APP<sn> (where <sn> = This new ET's short_name)
  --  and creating an appropriate row in SDW_VALID_STRUCTURE_ELEMENTS
  --

  --  Allocate an ID for the APPxxx element type

  appetid := jr_util.get_new_irid;
  
  --  Insert into RM_ELEMENT_TYPES

  insert into rm_element_types 
         (
          id,
          types,
          supertypes,
          date_created,
          created_by,
          number_of_times_modified,
          product,
          short_name,
          name,
          plural_name,
          abstract,
          primary_row_type,
          user_extension
         )
  values (
          appetid,
          et_et_id,
          et_ac_id,
          sysdate,
          user,
          1,
          'CI',
          'APP'||pl.v.short_name,
          'APP_SYS_'||pl.v.short_name,
          'APP_SYS_'||pl.v.short_name||'S',
          'N',
          ac_primary_row_type,
          'NNY'
         );

  -- Don't need this either ........
  --  Insert into RM$NLS_ELEMENT_TYPES

  insert into rm$nls_element_types 
         (nls_language,
          etid,
          nls_name,
          nls_plural_name
         )
  values (
          'US',
          appetid,
          'APP_SYS_'||pl.v.short_name,
          'APP_SYS_'||pl.v.short_name||'S'
         );
  
  -- Don't need this either ........
  if dict_lang != 'US' then
    insert into rm$nls_element_types
         (nls_language,
          etid,
          nls_name,
          nls_plural_name
         )
  values (
          dict_lang,
          appetid,
          'APP_SYS_'||pl.v.short_name,
          'APP_SYS_'||pl.v.short_name||'S'
         );
  end if;

  -- Don't need this either ........
  --  Insert into RM$ELEMENT_TYPE_EXTENSIONS

  insert into rm$element_type_extensions
         (etid   ,help_tag)
  values (appetid,ac_help_tag);

  -- Don't need this either ........
  --  Copy the properties for APPCON


  ciue_util.dup_properties(et_ac_id,appetid);

Should not do any of the above in 6.5 because all APPSYS elements are of type APPXXX
************************************************************************************
*/

  --  Finally, create a SDW_VALID_STRUCTURE_ELEMENTS row
  --  Between APPSYS and the newly created element type

  insert into sdw_valid_structure_elements
         (vsl_type,
          vsl_part_of,
          vsl_part_degree,
          vsl_use_of,
          vsl_use_degree,
          vsl_for_matrix_diagrammer,
          vsl_comment,
          vsl_cdi_only
         )
  values ('APPXXX',
          'APP',
          'M',
          pl.v.short_name,
          'M',
          'N',
          'Application / '||pl.v.short_name,
          'N'
         );

 
end ins;
/*
^L
*/
/*------------------------------ remap -------------------------------------*/
/*
  NAME
    remap - REMAP an existing extended element type to another underlying type.
  DESCRIPTION
    This procedure remaps an existing extended element type

    The id parameter the id of the existing element type

    The short_name parameter is the new short name 'E number' of the element 
  NOTES
  EXCEPTION
    ueet_invalid_sn   - Invalid short name
    ueet_miss_sn      - Missing short name
    ueet_duplicate_sn - Duplicate short name
    ueet_miss_nm      - Missing long name
    ueet_duplicate_nm - Duplicate long name
    ueet_miss_pn      - Missing plural name
    ueet_duplicate_pn - Duplicate plural name
  RETURNS
*/

procedure remap(id IN number, short_name IN varchar2) is
  appetid number;
  old_short_name varchar2(4);
  is_user_type varchar2(1);
begin

  --  Preprocess and check constraints

  if short_name is null then
    raise ueet_miss_sn;
  end if;

  short_name_check(short_name); 

  select et.short_name, substr(et.user_extension,1,1)
    into old_short_name, is_user_type
    from rm_element_types et
   where et.id = remap.id;

  if is_user_type != 'Y' then
    raise ueet_is_not_user_type;
  end if;

/* Not required at 6.5
  select id
    into appetid
    from rm_element_types
   where short_name = 'APP' || old_short_name;
*/
  --
  -- Before doing anything, drop the old views and packages
  --

  -- Done by RAU
  -- drop_views_packs(remap.id, 'APP'||old_short_name);

  --
  --  Update the element type rows
  --

  update rm_element_types
     set short_name = remap.short_name
   where id = remap.id;

/* Not required at 6.5
  update rm_element_types
     set short_name  = 'APP'||remap.short_name,
         name        = 'APP_SYS_'||remap.short_name,
         plural_name = 'APP_SYS_'||remap.short_name||'S'
   where id = appetid;

  -- if dict_lang != 'US', then this will update 2 rows
  update rm$nls_element_types
     set nls_name = 'APP_SYS_'||short_name,
         nls_plural_name = 'APP_SYS_'||short_name||'S'
   where etid = appetid;

*/
  update sdw_valid_structure_elements
     set vsl_use_of = short_name,
         vsl_comment = 'Application / '||short_name
   where vsl_use_of = old_short_name;

  -- update instance data
  jr_context.disable(jr_context.NO_CHECKIN_UPDATE);
  jr_context.disable(jr_context.ACCESS_RIGHTS);
  jr_context.disable(jr_context.CHECK_WORKAREA_CONTEXT);

  update sdd_uee
     set element_type_name = short_name
   where types = remap.id;

  jr_context.enable(jr_context.NO_CHECKIN_UPDATE);
  jr_context.enable(jr_context.ACCESS_RIGHTS);
  jr_context.enable(jr_context.CHECK_WORKAREA_CONTEXT);


  -- I don't think this needs to be done, as all associations are by reference
  -- cioue_association_type.remap(....);
  -- cioue_text_type.remap(....);

  --  Generate the view, spec, and body if it's a user type

  ciue_util.gen_view(id);
  ciue_util.gen_spec(id);
  ciue_util.gen_body(id);
  --ciue_util.gen_view(appetid);

exception
  when ueet_miss_sn then
    rmmes.post(ueet_fac,ueet_miss_sn_code);
    raise;
  when ueet_is_not_user_type then
    rmmes.post(ueet_fac,ueet_is_not_user_type_code, short_name);
    raise;

end remap;

/*
^L
*/
/*-------------------------------- upd -------------------------------------*/
/*
  NAME
    upd - UPDate an existing extended element type
  DESCRIPTION
    This procedure updates the details for an existing extended element
    type.

    The id parameter should indicate the ID of the extended element type
    to update.

    The pl parameter should contain the property values to change.
  NOTES
  EXCEPTION
    ueet_invalid_sn   - Invalid short name
    ueet_miss_sn      - Missing short name
    ueet_duplicate_sn - Duplicate short name
    ueet_miss_nm      - Missing long name
    ueet_duplicate_nm - Duplicate long name
    ueet_miss_pn      - Missing plural name
    ueet_duplicate_pn - Duplicate plural name
    ueet_not_exist    - Element type does not exist
    ueet_not_user     - Not a user extendible type
    ueet_is_published - Element type is published
  RETURNS
*/

procedure upd(id IN     number,
              pl IN OUT data) is
  ourpl data;
  care_if_pub varchar2(1) default 'D'; -- Do I care if this element is published
begin

  -- I don't care if the element is published or not if only nls_name or 
  --  nls_plural name are to be updated
  if pl.i.short_name or pl.i.name or pl.i.plural_name then
    care_if_pub := 'N';
  end if;

  --  Validate access

  pl.v.id := id;
  check_access(id, care_if_pub);

  --  Preprocess and check constraints

  ourpl := pl;
  preprocess(ourpl);
  constraint_check(pl,false);

  --  Update RM_ELEMENT_TYPES

  update rm_element_types
     set short_name     = ourpl.v.short_name,
         name           = ourpl.v.name,
         plural_name    = ourpl.v.plural_name
   where id = pl.v.id;

  --  Update RM$NLS_ELEMENT_TYPES

  update rm$nls_element_types
     set nls_name = ourpl.v.nls_name,
         nls_plural_name = ourpl.v.nls_plural_name
   where etid = pl.v.id;

  --  Return the updated property list

  pl := ourpl;

end upd;

/*
^L
*/
/*-------------------------------- del -------------------------------------*/
/*
  NAME
    del - DELete an existing extended element type
  DESCRIPTION
    This procedure deletes the details for a given extended element
    type.

    The id parameter should indicate the extended element type to delete.
  NOTES
  EXCEPTION
    ueet_not_exist    - Element type does not exist
    ueet_not_user     - Not a user extendible type
    ueet_is_published - Element type is published
  RETURNS
*/

procedure del(id IN number) is
  appxxxid number;
  appxxxsn varchar2(10);
  doappxxx boolean;
begin

  --  Validate access

  check_access(id);

  /***********************************************************************************************
  /*  Don't need any of this in Release 6.5
  --  Get the APPxxx's short name and ID

  select 'APP'||short_name
    into appxxxsn
    from rm_element_types
   where id = del.id;
  doappxxx := true;
  begin
    select id into appxxxid
      from rm_element_types 
     where short_name = appxxxsn;
  exception
    when no_data_found then
      doappxxx := false;
  end;
  ***********************************************************************************************/
  
  --
  --  Delete the extended element type
  --

  --  Delete from SDW_VALID_STRUCTURE_ELEMENTS
  
  delete from SDW_VALID_STRUCTURE_ELEMENTS
  where vsl_use_of = (select et.short_name
                      from   rm_element_types   et
                      where  et.id = del.id);

  --  Delete from RM_ELEMENT_TYPES

  delete from rm_element_types
   where id = del.id;

  --  Delete from RM$NLS_ELEMENT_TYPES

  delete from RM$NLS_ELEMENT_TYPES
   where etid = del.id;

  --  Delete from RM$ELEMENT_TYPE_EXTENSIONS

  delete from RM$ELEMENT_TYPE_EXTENSIONS
   where etid = del.id;

  --  Delete from RM_TEXT_USAGES

  delete from RM$TEXT_USAGES
   where etid = del.id;

  --  Delete from RM_UE_TEXT_LINES

  --delete from RM_UE_TEXT_LINES
  -- where txt_ref = del.id;

  --
  --  Delete each property and related rows
  --

  for py in (select id        id,
                    of_domain dtid
               from rm_properties
              where defined_against = del.id
            ) loop
--    --  Delete from RM_DATA_TYPES
--    delete from rm_data_types
--     where id = py.dtid;
    --  Delete from RM$NLS_DATA_TYPE_VALUES
    delete from rm$nls_data_type_values
     where did = py.dtid;
    --  Delete from RM$NLS_PROPERTIES
    delete from rm$nls_properties
     where pid = py.id;
    --  Delete from RM_PROPERTY_RANGES
    delete from rm_property_ranges
     where for_property = py.id;
    --  Delete from RM$NLS_PROPERTIES
    delete from rm$nls_properties
     where pid = py.id;
  end loop;
  
  --  Delete from RM_PROPERTIES

  delete from rm_properties where defined_against = del.id;

  --  Delete from RM_PROPERTY_MAPS

  delete from rm_property_maps where context = del.id;

  --
  --  Finally, delete the APPxxx if it exists
  --  (it won't exist if we're running on old data)
  --

  /*****************************************************************************************
  /* Changed for 6.5 - no rows to delete
  if doappxxx then

    --  Delete from RM_ELEMENT_TYPES
  
    delete from rm_element_types
     where id = appxxxid;
  
    --  Delete from RM$NLS_ELEMENT_TYPES
  
    delete from RM$NLS_ELEMENT_TYPES
     where etid = appxxxid;
  
    --  Delete from RM$ELEMENT_TYPE_EXTENSIONS
  
    delete from RM$ELEMENT_TYPE_EXTENSIONS
     where etid = appxxxid;
  
  
    --  Delete from RM_TEXT_USAGES
  
    delete from RM$TEXT_USAGES
     where etid = appxxxid;
  
    --  Delete from RM_UE_TEXT_LINES
  
    --delete from RM_UE_TEXT_LINES
    -- where txt_ref = appxxxid;
  
    --
    --  Delete each property and related rows
    --
  
    for py in (select id        id,
                      of_domain dtid
                 from rm_properties
                where defined_against = appxxxid
              ) loop
  --    --  Delete from RM_DATA_TYPES
  --    delete from rm_data_types
  --     where id = py.dtid;
      --  Delete from RM$NLS_DATA_TYPE_VALUES
      delete from rm$nls_data_type_values
       where did = py.dtid;
      --  Delete from RM$NLS_PROPERTIES
      delete from rm$nls_properties
       where pid = py.id;
      --  Delete from RM_PROPERTY_RANGES
      delete from rm_property_ranges
       where for_property = py.id;
      --  Delete from RM$NLS_PROPERTIES
      delete from rm$nls_properties
       where pid = py.id;
    end loop;
  
    --  Delete from RM_PROPERTIES
  
    delete from rm_properties where defined_against = appxxxid;
  
    --  Delete from RM_PROPERTY_MAPS

    delete from rm_property_maps
     where context = appxxxid;

  end if;
*************************************************************************/

end del;
  
/*
^L
*/
/*-------------------------------- publish ---------------------------------*/
/*
  NAME
    publish - PUBLISH an existing extended element type
  DESCRIPTION
    This procedure publishes the details for a given extended element type
    if it is not already published.  This includes creating the view,
    package spec, and package body DDL associated with the element type.

    The id parameter should indicate the element type to publish.
  NOTES
    The view, package spec, and package body DDL are *NOT* submitted by
    this procedure.  It is up to the caller to submit the DDL after
    successful completion of this procedure.
    Everything done in this package *MUST* be undone by unpublish.
  EXCEPTION
    ueet_not_exist    - Element type does not exist
    ueet_not_user     - Not a user extendible type
    ueet_is_published - Element type is published
  RETURNS
*/

procedure publish(id IN number) is
  isuet varchar2(1);

  appxxxsn varchar2(10);
  appxxxid number;
begin
  --  Validate access

  check_access(id);

  --  Read in some info

  select 'APP'||short_name,is_user_type
    into appxxxsn,isuet
    from ciue_element_types
   where id = publish.id;

  --  Get the APPXXX info (if there)

  begin
    select id into appxxxid
      from rm_element_types
     where short_name = appxxxsn;
  exception
    when no_data_found then
      null;
  end;

  --  Generate the view, spec, and body if it's a user type

  if isuet = 'Y' then

    ciue_util.gen_view(id);

    ciue_util.gen_spec(id);


    ciue_util.gen_body(id);


    if appxxxid is not null then
      ciue_util.gen_view(appxxxid);
    end if;
  end if;

  --  Set "is published" flag

  update rm_element_types
     set user_extension = 'YYY'
   where id = publish.id;
end publish;

/*
^L
*/
/*-------------------------------- unpublish -------------------------------*/
/*
  NAME
    unpublish - UNPUBLISH an existing extended element type
  DESCRIPTION
    This procedure undoes the work of the publish procedure above. It is
    only permitted if there is no instance data, no associations and no
    text usages. The associated package and views are dropped.

    The id parameter should indicate the element type to unpublish.
  NOTES
    The view and package *ARE* dropped by
    this procedure.
  EXCEPTION
  RETURNS
*/

procedure unpublish(id IN number) is
  isuet varchar2(1);

  cnt integer;

  short_name varchar2(4);

  spare_str varchar2(40);

begin
  --  Validate access

  check_access(id, 'M');

  --  Read in some info

  select is_user_type, short_name
    into isuet, short_name
    from ciue_element_types
   where id = unpublish.id;

  -- Check no instance data
  select count(*)
    into cnt
    from sdd_uee
   where types = unpublish.id;
  if cnt != 0 then
    rmmes.post(ueet_fac, ueet_data_exists_code, short_name);
    raise ueet_data_exists;
  end if;

  -- Check no associations (published or unpublished) (except APPXXX)
  select min(ast.short_name)
    into spare_str
    from ciue_association_types ast
   where (ast.part_of_reference = unpublish.id
      or ast.use_of_reference = unpublish.id)
     and ast.short_name != 'APPXXX';
  if spare_str is not null then
    rmmes.post(ueet_fac, ueet_assoc_exists_code, spare_str, short_name);
    raise ueet_assoc_exists;
  end if;

/*
  -- Dropped due to B631866
  -- Check no text usages (published or unpublished)
  select min(tu.text_type)
    into spare_str
    from ciue_text_usages tu
   where element_type = unpublish.id;
  if spare_str is not null then
    rmmes.post(ueet_fac, ueet_text_u_exists_code, spare_str, short_name);
    raise ueet_text_u_exists;
  end if;
*/
  -- if isuet = 'Y' then
    -- drop_views_packs(unpublish.id, appxxxsn);
  -- end if; /* isuet */

  --  UnSet "is published" flag

  update rm_element_types
     set user_extension = substr(user_extension,1,2) || 'N'
   where id = unpublish.id;

end unpublish;

/*  END OF PUBLIC PROCEDURES  */

/*  PRIVATE PROCEDURES  */

/*
^L
*/
/*-------------------------------- drop_views_packs -------------------------*/
/*
  NAME
    drop_views_packs - Drop views, packages and package bodies
  DESCRIPTION

    The id parameter is the id of the type to be dropped

    The appxxxsn is the short_name of the APPXXX object to be dropped
    It may be null

  NOTES
  EXCEPTION
  RETURNS
*/
procedure drop_views_packs(id IN number)
is
  view_name varchar2(40);
  app_view_name varchar2(40);
  pack_name varchar2(40);
  appxxxsn varchar2(40);

  str varchar2(100);
  ret integer;
  cur integer;

begin

  --  Read in some info

  select rtrim(et.product) ||'_'||rtrim(upper(et.plural_name)),
         lower(rtrim(et.product)||'O'||rtrim(upper(et.name)))
    into view_name
        ,pack_name
    from rm_element_types et
   where et.id = drop_views_packs.id;

  begin
    select 'APP'||short_name
      into appxxxsn
      from ciue_element_types
     where id = drop_views_packs.id;

    select rtrim(et.product) || '_' || rtrim(upper(et.plural_name))
      into app_view_name
      from rm_element_types et
     where short_name = appxxxsn;
  exception
    when no_data_found then
      null;
  end;

  cur := dbms_sql.open_cursor;

  str := 'drop view ' || view_name ;
  begin
    dbms_sql.parse(cur, str, dbms_sql.v7);
    ret := dbms_sql.execute(cur);
  exception
    -- ignore ORA-00942: table or view does not exist
    when others then if SQLCODE != -942 then raise; end if;
  end;

  str := 'drop package ' || pack_name ; -- drops package body too
  begin
    dbms_sql.parse(cur, str, dbms_sql.v7);
    ret := dbms_sql.execute(cur);
  exception
    -- ignore ORA-04043: object XXXX does not exist
    when others then if SQLCODE != -4043 then raise; end if;
  end;

  if app_view_name is not null then
    str := 'drop view ' || app_view_name ;
    begin
      dbms_sql.parse(cur, str, dbms_sql.v7);
      ret := dbms_sql.execute(cur);
    exception
      -- ignore ORA-00942: table or view does not exist
      when others then if SQLCODE != -942 then raise; end if;
    end;
  end if;

  dbms_sql.close_cursor(cur);

  view_name := upper(view_name);
  pack_name := upper(pack_name);
  app_view_name := upper(app_view_name);
  
  -- Remove Reserved Names entry and comment
  delete from sdd_reserved_names
   where (rn_res_name = view_name and rn_type = 'VIEW')
      or (rn_res_name = pack_name and rn_type = 'PACKAGE')
      or (rn_res_name = pack_name and rn_type = 'PACKAGE BODY')
      or (rn_res_name = app_view_name and rn_type = 'VIEW BODY');
      
  --This removal was once done like this, but due to
  -- Server bug 1396675 on Oracle 8.1.7 it was replaced by the above
  -- CvE 01Nov2000
  --delete from sdd_reserved_names
  -- where (rn_res_name, rn_type) in ((upper(view_name), 'VIEW')
  --                                 ,(upper(pack_name), 'PACKAGE')
  --                                 ,(upper(pack_name), 'PACKAGE BODY')
  --                                 ,(upper(app_view_name), 'VIEW BODY'));

  -- Remove DDL
  --delete from rm_ue_text_lines
  -- where txt_ref = drop_views_packs.id
  --   and txt_type in ('DDLVEW', 'DDLPKS', 'DDLPKB');

end drop_views_packs;

/*
^L
*/
/*-------------------------------- preprocess ------------------------------*/
/*
  NAME
    preprocess - Preprocess property values
  DESCRIPTION
    This procedure preprocesses all property values and changes them to
    properly translated values if specified.

    The pl parameter should contain the properties to preprocess.
  NOTES
    If isins = false existing property values will be updated into the
    pl parameter.

    If isins = false this function assumes that the caller has already
    performed check_access.
  EXCEPTION
  RETURNS
*/

procedure preprocess (pl IN OUT data, isins IN boolean) is
  ourpl data;
begin

  --  Read existing values (if any)

  if not isins then
    select short_name,
           name,
           plural_name,
           nls_name,
           nls_plural_name
      into ourpl.v.short_name,
           ourpl.v.name,
           ourpl.v.plural_name,
           ourpl.v.nls_name,
           ourpl.v.nls_plural_name
      from ciue_element_types
     where id = pl.v.id;
     if not pl.i.short_name then
       pl.v.short_name := ourpl.v.short_name;
     end if;
     if not pl.i.name then
       pl.v.name := ourpl.v.name;
     end if;
     if not pl.i.plural_name then
       pl.v.plural_name := ourpl.v.plural_name;
     end if;
     if not pl.i.nls_name then
       pl.v.nls_name := ourpl.v.nls_name;
     end if;
     if not pl.i.nls_plural_name then
       pl.v.nls_plural_name := ourpl.v.nls_plural_name;
     end if;
  end if;

  --  NLS name defaulting

  if pl.v.nls_name is null then
    pl.v.nls_name := pl.v.name;
  end if;
  if pl.v.nls_plural_name is null then
    pl.v.nls_plural_name := pl.v.plural_name;
  end if;

  --  Name translation

  if pl.i.short_name then
    pl.v.short_name := upper(translate(pl.v.short_name,
                             ' !@%^&*()-=+\|`~[{]};:''",<.>/?',
                             '______________________________'));
  end if;

  if pl.i.name then
    pl.v.name := upper(translate(pl.v.name,
                       ' !@%^&*()-=+\|`~[{]};:''",<.>/?',
                       '______________________________'));
  end if;

  if pl.i.plural_name then
    pl.v.plural_name := upper(translate(pl.v.plural_name,
                              ' !@%^&*()-=+\|`~[{]};:''",<.>/?',
                              '______________________________'));
  end if;

end preprocess;

/*
^L
*/
/*-------------------------------- constraint_check-------------------------*/
/*
  NAME
    constraint_check - Do UE element type constraint checks
  DESCRIPTION
    This procedure validates the properties of a UE element type
    and raises exceptions if any errors occur.

    The pl parameter should contain the element type's data.
  NOTES
    This assumes that all properties of the element type have been
    set in the pl parameter even if the .i is 'false'.

    If an exception is raised the error will be posted on the stack.
  EXCEPTION
    ueet_invalid_sn   - Invalid short name 
    ueet_miss_sn      - Missing short name
    ueet_duplicate_sn - Duplicate short name
    ueet_miss_nm      - Missing long name
    ueet_duplicate_nm - Duplicate long name
    ueet_miss_pn      - Missing plural name
    ueet_duplicate_pn - Duplicate plural name
  RETURNS
*/

procedure constraint_check(pl IN data, isins IN boolean) is
  existid number;
begin
  --
  --  Validate short name
  --
 
  --  Make sure short name is specified

  if isins and pl.i.short_name = false then
    raise ueet_miss_sn;
  end if;

  --  Standard short name validation

  if isins or pl.i.short_name then
    short_name_check(pl.v.short_name); 
  end if;

  --  Standard name validation

  if isins or pl.i.name then
    if pl.v.name is null then
      raise ueet_miss_nm;
    end if;
  end if;

  --  Duplicate name validation

  if isins or pl.i.name then
    begin
      select id into existid
        from rm_element_types et
       where name = pl.v.name;
      if pl.v.id is null or pl.v.id != existid then
        raise ueet_duplicate_nm;
      end if;
    exception when no_data_found then
      null;
    end;
  end if;

  --  Standard plural name validation

  if isins or pl.i.plural_name then
    if pl.v.plural_name is null then
      raise ueet_miss_pn;
    end if;
  end if;

  --  Duplicate plural name validation

  if isins or pl.i.plural_name then
    begin
      select id into existid
        from rm_element_types et
       where plural_name = pl.v.plural_name;
      if pl.v.id is null or pl.v.id != existid then
        raise ueet_duplicate_pn;
      end if;
    exception when no_data_found then
      null;
    end;
  end if;

  return;
--
--  Exceptions
--
exception
  when ueet_miss_sn then
    rmmes.post(ueet_fac,ueet_miss_sn_code);
    raise;

  when ueet_miss_nm then
    rmmes.post(ueet_fac,ueet_miss_nm_code);
    raise;
  when ueet_duplicate_nm then
    rmmes.post(ueet_fac,ueet_duplicate_nm_code,pl.v.name);
    raise;

  when ueet_miss_pn then
    rmmes.post(ueet_fac,ueet_miss_pn_code);
    raise;
  when ueet_duplicate_pn then
    rmmes.post(ueet_fac,ueet_duplicate_pn_code,pl.v.plural_name);
    raise;

end constraint_check;

/*------------------------------- short_name_check -------------------------*/
/*
  NAME
    short_name_check - Do UE element type short name checks
  DESCRIPTION
    This procedure validates the short_name property of a UE element type
    and raises exceptions if any errors occur.

    The short_name parameter should contain the element type's short_name.
  NOTES
    If an exception is raised the error will be posted on the stack.
  EXCEPTION
    ueet_invalid_sn   - Invalid short name 
    ueet_duplicate_sn - Duplicate short name
  RETURNS
*/
procedure short_name_check(short_name IN varchar2)
is
  dummy number;
begin
  if short_name is null then
    raise ueet_invalid_sn;
  end if;
  if substr(short_name,1,1) not in ('E','e') then
    raise ueet_invalid_sn;
  end if;
  
  -- Fix bug 1283749, doesn't assume that the 2nd and subsequent characters of the short name are numeric
  BEGIN
    dummy := substr(short_name, 2);
  EXCEPTION
    WHEN VALUE_ERROR THEN
    RAISE ueet_invalid_sn;
  WHEN OTHERS THEN
    RAISE;
  END;
  
  if dummy < 0 or dummy > 499 then
    raise ueet_invalid_sn;
  end if;

  --  Duplicate short name validation

  select count(*) into dummy
    from rm_element_types et
   where et.short_name = short_name_check.short_name;
  if dummy > 0 then
    raise ueet_duplicate_sn;
  end if;

exception
  when ueet_invalid_sn then
    rmmes.post(ueet_fac,ueet_invalid_sn_code,short_name);
    raise;
  when ueet_duplicate_sn then
    rmmes.post(ueet_fac,ueet_duplicate_sn_code,short_name);
    raise;
end short_name_check;
/*
^L
*/
/*-------------------------------- check_access ----------------------------*/
/*
  NAME
    check_access - Validate access to a given element type
  DESCRIPTION
    This procedure validates a given element type id and makes sure that
    it exists, is a user extendible type, and is not published.

    The id parameter should contain the element type's id.
  NOTES
    care_if_pub may have 3 values M - must be published, N - must not be 
    published, D Don't care if it's published of not.
  EXCEPTION
    ueet_not_exist    - Element type does not exist
    ueet_not_user     - Exists but is not user extendible
    ueet_is_published - Exists and is extendible but is published
  RETURNS
*/

procedure check_access(id IN number, care_if_pub IN varchar2) is
  ue rm_element_types.user_extension%type;
  sn rm_element_types.short_name%type;
begin
  
  select user_extension,short_name into ue,sn
    from rm_element_types
   where id = check_access.id;

  if substr(ue,1,1) = 'N' then
    raise ueet_not_user;
  end if;
  
  if substr(ue,3,1) = 'Y' and care_if_pub = 'N' then
    raise ueet_is_published;
  end if;
  if substr(ue,3,1) = 'N' and care_if_pub = 'M' then
    raise ueet_is_not_published;
  end if;

  return;

--
--  Exceptions
--

exception
  when no_data_found then
    rmmes.post(ueet_fac,ueet_not_exist_code,id);
    raise;
  when ueet_not_user then
    rmmes.post(ueet_fac,ueet_not_user_code,sn);
    raise;
  when ueet_is_published then
    rmmes.post(ueet_fac,ueet_is_published_code,sn);
    raise;     -- Only recently added - not in Bruce's original, I wonder why?
  when ueet_is_not_published then
    rmmes.post(ueet_fac,ueet_is_not_published_code,sn);
    raise;

end check_access;

--
--  Package instantiation block
--

begin

  --  Get info for element type: ELEMENT_TYPE

  select id into et_et_id
    from rm_element_types
   where name = 'ELEMENT_TYPE';

  --  Get info for element type: EXTENDED_ELEMENT

  select id,
         primary_row_type
    into et_ee_id,
         ee_primary_row_type
    from rm_element_types
   where name = 'EXTENDED_ELEMENT';
  select help_tag
    into ee_help_tag
    from rm_element_type_extensions
   where for_type = et_ee_id;

  --  Get info for element type: APP_CON_INTERFACE

  select id,
         primary_row_type
    into et_ac_id,
         ac_primary_row_type
    from rm_element_types
   where name = 'FOLDER_MEMBER';
  select help_tag
    into ac_help_tag
    from rm_element_type_extensions
   where for_type = et_ac_id;

  --  Get current CDAPI configuration NLS language

  select nls_language into dict_lang
    from rm_repositories;

  -- Get supertype of the Exxx element (SHR)
  select id
    into et_ees_id
    from rm_element_types
   where short_name = 'SHR';

end cioue_element_type;
/