rem
rem $Header: L:\\\\model\\repman40\\api\\RCS\\ciueat.opb 1.1 1998/05/28 16:56:15 mfrobins Exp $
rem
rem $Header: L:\\\\model\\repman40\\api\\RCS\\ciueat.opb 1.1 1998/05/28 16:56:15 mfrobins Exp $
rem
rem Copyright (c) Oracle Corporation 1991.  All Rights Reserved.
rem  NAME
rem    ciueat.opb - Operation Package Body
rem  DESCRIPTION
rem    This package is responsible for managing UE association
rem    type extensions to the CASE Repository.
rem  PUBLIC PROCEDURE(S)
rem    ins      - INSert a new extended association type
rem    upd      - UPDate an existing association type
rem    del      - DELete an existing association type
rem    publish  - PUBLISH an existing association type
rem  PRIVATE PROCEDURE(S)
rem    preprocess       - Preprocess property values
rem    constraint_check - Validate constraints
rem    check_access     - Validate access to association 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    04/03/95 -  Fixes for patch CI0114.041
rem    bferris    03/15/95 -  Added nls_name, nls_plural_name
rem    bferris    02/24/95 -  Fixes for APPxxx
rem                           The pre-production release
Rem    aheath     02/14/95 -  Merged into api release structure
rem    bferris    12/22/94 -  Creation


CREATE OR REPLACE PACKAGE BODY cioue_association_type IS

/*  PRIVATE GLOBALS  */

  et_et_id number;      -- ELEMENT_TYPE element type ID
  et_ea_id number;      -- EXTENDED_ELEMENT element type ID
  et_eas_id number;     -- EXTENDED_ELEMENT_SUPERTYPE element type ID
  ee_primary_row_type number;   -- Primary row type (for ins)
  ee_help_tag         varchar2(10); -- 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 association type                   */
procedure check_access(id IN number, care_if_pub IN varchar2 default 'N');

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

/*  END OF PRIVATE PROCEDURE PROTOTYPES  */

/*  PUBLIC PROCEDURES  */

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

    The pl parameter should contain the initial property values for the
    new extended association type.  Upon successful return the data.v.id
    member will be updated to contain the ID of the new association type.
  NOTES
  EXCEPTION
    ueat_invalid_sn   - Invalid short name
    ueat_miss_sn      - Missing short name
    ueat_duplicate_sn - Duplicate short name
    ueat_miss_nm      - Missing long name
    ueat_duplicate_nm - Duplicate long name
    ueat_miss_pn      - Missing plural name
    ueat_duplicate_pn - Duplicate plural name
    ueat_invalid_por  - Invalid part_of reference
    ueat_invalid_pod  - Invalid part_of degree
    ueat_invalid_uor  - Invalid use_of reference
    ueat_invalid_uod  - Invalid use_of degree
  RETURNS
*/

procedure ins(id IN number, pl IN OUT data) is
  posn varchar2(10);      -- PART OF short name
  uosn varchar2(10);      -- USE OF short name
begin

  --  Preprocess and check 

  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_eas_id,
          sysdate,
          user,
          1,
          'CI',
          pl.v.short_name,
          pl.v.name,
          pl.v.plural_name,
          'N',
          ee_primary_row_type,
          'YYN',
          'N',
          'N',
          '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);

  --  Insert into SDW_VALID_STRUCTURE_ELEMENTS

  select et.short_name
    into posn
    from rm_element_types et
   where et.id = pl.v.part_of_reference;
  select et.short_name
    into uosn
    from rm_element_types et
   where et.id = pl.v.use_of_reference;
  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 (pl.v.short_name,
          posn,
          pl.v.part_of_degree,
          uosn,
          pl.v.use_of_degree,
          pl.v.is_for_matrix,
          'User extended association type',
          'N'
         );

  --
  --  Duplicate the properties and their property maps...
  --

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

  --
  --  Duplicate the text type usages
  --

  ciue_util.dup_text_usages(et_ea_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;

end ins;

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

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

    The pl parameter should contain the property values to change.
  NOTES
  EXCEPTION
    ueat_invalid_sn   - Invalid short name
    ueat_miss_sn      - Missing short name
    ueat_duplicate_sn - Duplicate short name
    ueat_miss_nm      - Missing long name
    ueat_duplicate_nm - Duplicate long name
    ueat_miss_pn      - Missing plural name
    ueat_duplicate_pn - Duplicate plural name
    ueat_not_exist    - Association type does not exist
    ueat_not_user     - Not a user extendible type
    ueat_invalid_por  - Invalid part_of reference
    ueat_invalid_pod  - Invalid part_of degree
    ueat_invalid_uor  - Invalid use_of reference
    ueat_invalid_uod  - Invalid use_of degree
    ueat_is_published - Association type is published
  RETURNS
*/

procedure upd(id IN     number,
              pl IN OUT data) is
  ourpl data;
  osn  varchar2(10);      -- Old short_name
  posn varchar2(10);                     -- PART OF short name
  uosn varchar2(10);                     -- USE OF short name
begin

  --  Validate access

  check_access(id);

  --  Preprocess and check constraints

  ourpl := pl;
  ourpl.v.id := id;
  preprocess(ourpl);
  constraint_check(pl,false);

  --  Get old short_name (if it changed)

  if pl.i.short_name then
    select short_name
      into osn
      from rm_element_types
     where id = ourpl.v.id;
  else   
    osn := ourpl.v.short_name;
  end if;

  --  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 = ourpl.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 = ourpl.v.id;

  --  Update SDW_VALID_STRUCTURE_ELEMENTS

  select et.short_name
    into posn
    from rm_element_types et
   where et.id = ourpl.v.part_of_reference;
  select et.short_name
    into uosn
    from rm_element_types et
   where et.id = ourpl.v.use_of_reference;
  update sdw_valid_structure_elements
     set vsl_type                  = ourpl.v.short_name,
         vsl_part_of               = posn,
         vsl_part_degree           = ourpl.v.part_of_degree,
         vsl_use_of                = uosn,
         vsl_use_degree            = ourpl.v.use_of_degree,
         vsl_for_matrix_diagrammer = ourpl.v.is_for_matrix
   where vsl_type = osn;

  --  Return the updated property list

  pl := ourpl;

end upd;

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

    The id parameter should indicate the extended association type to delete.
  NOTES
  EXCEPTION
    ueat_not_exist    - Association type does not exist
    ueat_not_user     - Not a user extendible type
    ueat_is_published - Association type is published
  RETURNS
*/

procedure del(id IN number) is
  sn varchar2(10);
begin

  --  Validate access

  check_access(id);

  --  Get the short name

  select short_name
    into sn
    from rm_element_types
   where 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 SDW_VALID_STRUCTURE_ELEMENTS

  delete from SDW_VALID_STRUCTURE_ELEMENTS
   where vsl_type = sn;

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

end del;

/*
^L
*/
/*-------------------------------- publish ---------------------------------*/
/*
  NAME
    publish - PUBLISH an existing extended association type
  DESCRIPTION
    This procedure publishes the details for a given extended association
    type if it is not already published.  This includes creating the view,
    package spec, and package body DDL associated with the association
    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.
  EXCEPTION
    ueat_not_exist    - Association type does not exist
    ueat_not_user     - Not a user extendible type
    ueat_is_published - Association type is published
  RETURNS
*/

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

  -- Only used when duplicating part_of and use_of for UE associations
  cursor assoc_props(netid in number) is
  select po_et.name po_et_name
        ,po_etx.nls_name po_etx_nls_name
        ,uo_et.name uo_et_name
        ,uo_etx.nls_name uo_etx_nls_name
        ,my_et.name my_et_name
        ,my_et.short_name my_et_sname
        ,po_et.id po_et_id
        ,uo_et.id uo_et_id
        ,py.id py_id
        ,py.name py_name
    from rm_element_type_extensions po_etx
        ,rm_element_types po_et
        ,rm_element_type_extensions uo_etx
        ,rm_element_types uo_et
        ,sdw_valid_structure_elements vsee
        ,rm_properties py
        ,rm_element_types my_et
   where po_etx.for_type = po_et.id
     and po_et.short_name = vsee.vsl_part_of
     and uo_etx.for_type = uo_et.id
     and uo_et.short_name = vsee.vsl_use_of
     and vsee.vsl_type = my_et.short_name
     and py.name in ('PART_OF_REFERENCE', 'USE_OF_REFERENCE')
     and py.defined_against = my_et.id
     and my_et.id = netid;

  link_type_id number;
  
begin
  --  Validate access

  check_access(id);

  --  Read in some info

  select is_user_type 
    into isuet
    from ciue_association_types
   where id = publish.id;

  --  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, 'Y');
    ciue_util.gen_body(id, 'Y');
  end if;

  -- Create and publish the REFSET Properties at the other end element types
  --  (Do this here rather than on insert to avoid the hassle of updates)
  -- Now create link properties for the use_of and part_of
  for ap in assoc_props(publish.id) loop  -- Should only go round twice
    if ap.py_name = 'PART_OF_REFERENCE' then
      -- Insert Part_Of          
      link_type_id := null;
      jr_rm_ins.rm_link_property_ins_fk_end(
         ap.py_id                             -- i_property_irid              number
        ,upper(ap.po_et_name) || '_REFERENCE' -- i_fk_name                    varchar2
        ,upper(ap.my_et_name) || '_REFERENCE' -- i_pk_name                    varchar2
        ,'Y'                                  -- i_is_owning_assoc            varchar2
        ,ap.po_et_id                          -- i_pk_element_type_id         number
        ,'REFSET_' || ap.my_et_sname          -- i_pk_property_name           varchar2
        ,link_type_id                         -- i_link_type_id        in out number
        ,1                                    -- i_sequence_in_link           number      
        );
      -- Update 'Prompt' of FK end
      update rm$nls_properties
         set nls_name = ap.po_etx_nls_name
       where pid = ap.py_id;

    else
      -- Insert Use_Of          
      link_type_id := null;
      jr_rm_ins.rm_link_property_ins_fk_end(
         ap.py_id                              -- i_property_irid              number
        ,upper(ap.uo_et_name) || '_REFERENCE'  -- i_fk_name                    varchar2
        ,upper(ap.my_et_name) || '_REFERENCE'  -- i_pk_name                    varchar2
        ,'N'                                   -- i_is_owning_assoc            varchar2
        ,ap.uo_et_id                           -- i_pk_element_type_id         number
        ,'REFSET_' || ap.my_et_sname           -- i_pk_property_name           varchar2
        ,link_type_id                          -- i_link_type_id        in out number
        ,1                                     -- i_sequence_in_link           number      
        );
      -- Update 'Prompt' of FK end
      update rm$nls_properties
         set nls_name = ap.uo_etx_nls_name
       where pid = ap.py_id;
    end if;      
  end loop;
  
  --  Set "is published" flag
  update rm_element_types et
     set et.user_extension = 'YYY'
   where et.id = publish.id;

  --  Set "is published" flag on the REFSET properties at the other ends
  update rm_link_properties lp1
     set lp1.user_extension = 'YYY'
   where exists (
         select null
           from rm_link_properties lp2
          where lp1.link_type = lp2.link_type
            and lp1.id != lp2.id
            and lp2.defined_against = publish.id
            and lp2.name in ('PART_OF_REFERENCE', 'USE_OF_REFERENCE')
         );

  --  Set "is extended" flag on the element types at the other ends
  --   (Can't really undo this on unpublish, as the types may have been extended for other
  --    reasons. I'm not sure this matters a whole lot.)
  update rm_element_types et
     set et.user_extension = substr(et.user_extension, 1, 1) || 'Y' || substr(et.user_extension, 3, 1)
   where exists (
         select null
           from sdw_valid_structure_elements vse
               ,rm_element_types my_et
          where et.short_name in (vse.vsl_part_of, vse.vsl_use_of)
            and vse.vsl_type = my_et.short_name
            and my_et.id = publish.id
         );

end publish;

/*
^L
*/
/*------------------------------ unpublish ---------------------------------*/
/*
  NAME
    unpublish - UNPUBLISH an existing extended association type
  DESCRIPTION
    This procedure unpublishes the details for a given extended association
    type if it is already published.  This includes dropping the view,
    package spec, and package body DDL associated with the association
    type.

    The id parameter should indicate the element type to unpublish.
  NOTES
    The view, package spec, and package body DDL *ARE* dropped by
    this procedure.  
  EXCEPTION
    ueat_not_exist    - Association type does not exist
    ueat_not_user     - Not a user extendible type
    ueat_is_published - Association type is published
  RETURNS
*/

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

  appxxxsn varchar2(10);
  short_name varchar2(4);

  spare_str varchar2(40);

  view_name varchar2(40);
  app_view_name varchar2(40);
  pack_name varchar2(40);

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

begin
  --  Validate access

  check_access(id,'M');

  --  Read in some info

  select is_user_type 
    into isuet
    from ciue_association_types
   where id = unpublish.id;

  -- Check no instance data
  select count(*)
    into cnt
    from sdd_ues
   where types = unpublish.id;
  if cnt != 0 then
    rmmes.post(ueat_fac, ueat_data_exists_code, short_name);
    raise ueat_data_exists;
  end if;

  -- 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(ueat_fac, ueat_text_u_exists_code, spare_str, short_name);
    raise ueat_text_u_exists;
  end if;

/*
  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 = unpublish.id;
*/

  -- if isuet = 'Y' then
    -- cioue_element_type.drop_views_packs(unpublish.id, null);
/*
    cur := dbms_sql.open_cursor;

    str := 'drop view ' || view_name ;
    dbms_sql.parse(cur, str, dbms_sql.v7);
    ret := dbms_sql.execute(cur);

    str := 'drop package ' || pack_name ; -- drops package body too
    dbms_sql.parse(cur, str, dbms_sql.v7);
    ret := dbms_sql.execute(cur);

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

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

    -- Remove DDL
    --delete from rm_ue_text_lines
    -- where txt_ref = unpublish.id
    --   and txt_type in ('DDLVEW', 'DDLPKS', 'DDLPKB');
*/
  -- end if; /* isuet */

  for lp in (select lp.id
               from rm_link_properties lp
              where lp.defined_against = unpublish.id
                and lp.name in ('PART_OF_REFERENCE', 'USE_OF_REFERENCE') ) loop
    jr_rm_del.rm_link_property_del_fk_end(lp.id);
  end loop;

  --  UnSet "is published" flag

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

  --  UnSet "is published" flag on the REFSET properties at the other ends
  update rm_link_properties lp1
     set lp1.user_extension = substr(lp1.user_extension,1,2) || 'N'
   where exists (
         select null
           from rm_link_properties lp2
          where lp1.link_type = lp2.link_type
            and lp1.id != lp2.id
            and lp2.defined_against = unpublish.id
            and lp2.name in ('PART_OF_REFERENCE', 'USE_OF_REFERENCE')
         );
         
  --  UnSet "is extended" flag on the element types at the other ends
  --   (Can't really undo this on unpublish, as the types may have been extended for other
  --    reasons. I'm not sure this matters a whole lot.)

end unpublish;

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

    The id parameter the id of the existing association type

    The short_name parameter is the new short name 'A number' of the association 
  NOTES
  EXCEPTION
    ueet_invalid_sn   - Invalid short name
    ueat_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 ueat_miss_sn;
  end if;

  short_name_check(short_name, id); 

  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 ueat_is_not_user_type;
  end if;

  --
  -- Before doing anything, drop the old views and packages
  --

  -- cioue_element_type.drop_views_packs(remap.id, null);

  --
  --  Update the element type rows
  --

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

  update sdw_valid_structure_elements
     set vsl_type = remap.short_name
   where vsl_type = 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_ues
     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 believe I need to do this
  -- 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, 'Y');
  ciue_util.gen_body(id, 'Y');

exception
  when ueat_miss_sn then
    rmmes.post(ueat_fac,ueat_miss_sn_code);
    raise;
  when ueat_is_not_user_type then
    rmmes.post(ueat_fac,ueat_is_not_user_type_code, short_name);
    raise;

end remap;

/*  END OF PUBLIC PROCEDURES  */

/*  PRIVATE PROCEDURES  */

/*
^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;
  p_type varchar2(10);
  u_type varchar2(10);
begin

  --  Read existing values (if any)

  if not isins then
    select short_name,
           name,
           plural_name,
           nls_name,
           nls_plural_name,
           part_of_reference,
           part_of_degree,
           use_of_reference,
           use_of_degree,
           is_for_matrix
      into ourpl.v.short_name,
           ourpl.v.name,
           ourpl.v.plural_name,
           ourpl.v.nls_name,
           ourpl.v.nls_plural_name,
           ourpl.v.part_of_reference,
           ourpl.v.part_of_degree,
           ourpl.v.use_of_reference,
           ourpl.v.use_of_degree,
           ourpl.v.is_for_matrix
      from ciue_association_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;
     if not pl.i.part_of_reference then
       pl.v.part_of_reference := ourpl.v.part_of_reference;
     end if;
     if not pl.i.part_of_degree then
       pl.v.part_of_degree := ourpl.v.part_of_degree;
     end if;
     if not pl.i.use_of_reference then
       pl.v.use_of_reference := ourpl.v.use_of_reference;
     end if;
     if not pl.i.use_of_degree then
       pl.v.use_of_degree := ourpl.v.use_of_degree;
     end if;
     if not pl.i.is_for_matrix then
       pl.v.is_for_matrix := ourpl.v.is_for_matrix;
     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;

  --  The matrix diagrammer can cope with all user extended associations
  pl.i.is_for_matrix := true;
  pl.v.is_for_matrix := 'Y';


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
    ueat_invalid_sn   - Invalid short name 
    ueat_miss_sn      - Missing short name
    ueat_duplicate_sn - Duplicate short name
    ueat_miss_nm      - Missing long name
    ueat_duplicate_nm - Duplicate long name
    ueat_miss_pn      - Missing plural name
    ueat_duplicate_pn - Duplicate plural name
    ueat_invalid_por  - Invalid part_of reference
    ueat_invalid_pod  - Invalid part_of degree
    ueat_invalid_uor  - Invalid use_of reference
    ueat_invalid_uod  - Invalid use_of degree
  RETURNS
*/

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

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

  --  Standard short name validation

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

  --  Standard name validation

  if isins or pl.i.name then
    if pl.v.name is null then
      raise ueat_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 ueat_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 ueat_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 ueat_duplicate_pn;
      end if;
    exception when no_data_found then
      null;
    end;
  end if;

  --  Validate PART OF stuff

  begin
    -- Allow associations to some SACs as well as PACs
    select null into dummy
      from ciue_element_types et
     where et.id = pl.v.part_of_reference
     and (et.short_name in ('FUN', 'DAT', 'OTB', 'DBU', 'GRP', 
                            'ORS', 'DAF', 'LOF', 'COL', 'ATT')
      or exists 
       (select null from rm_element_types et2
  where et2.name = 'SHAREABLE_ELEMENT'
  start with et2.id = et.id
  connect by et2.id = prior et2.supertypes));
  exception
    when no_data_found then
      raise ueat_invalid_por;
  end;
  if pl.v.part_of_degree is not null and
     pl.v.part_of_degree not in ('1','M') then
    raise ueat_invalid_pod;
  end if;

  --  Validate USE OF stuff

  begin
    -- Allow associations to some SACs as well as PACs
    select null into dummy
      from ciue_element_types et 
     where et.id = pl.v.use_of_reference
     and (et.short_name in ('FUN', 'DAT', 'OTB', 'DBU', 'GRP', 
                            'ORS', 'DAF', 'LOF', 'COL', 'ATT')
      or exists 
       (select * from rm_element_types et2
  where et2.name = 'SHAREABLE_ELEMENT'
  start with et2.id = et.id
  connect by et2.id = prior et2.supertypes));
  exception
    when no_data_found then
      raise ueat_invalid_uor;
  end;
  if pl.v.use_of_degree is not null and
     pl.v.use_of_degree not in ('1','M') then
    raise ueat_invalid_uod;
  end if;

  return;
--
--  Exceptions
--
exception
  when ueat_miss_sn then
    rmmes.post(ueat_fac,ueat_miss_sn_code);
    raise;
  when ueat_miss_nm then
    rmmes.post(ueat_fac,ueat_miss_nm_code);
    raise;
  when ueat_duplicate_nm then
    rmmes.post(ueat_fac,ueat_duplicate_nm_code,pl.v.short_name);
    raise;

  when ueat_miss_pn then
    rmmes.post(ueat_fac,ueat_miss_pn_code);
    raise;
  when ueat_duplicate_pn then
    rmmes.post(ueat_fac,ueat_duplicate_pn_code,pl.v.short_name);
    raise;

  when ueat_invalid_por then
    rmmes.post(ueat_fac,ueat_invalid_por_code,pl.v.id);
    raise;
  when ueat_invalid_pod then
    rmmes.post(ueat_fac,ueat_invalid_pod_code,pl.v.part_of_degree);
    raise;
  when ueat_invalid_uor then
    rmmes.post(ueat_fac,ueat_invalid_uor_code,pl.v.id);
    raise;
  when ueat_invalid_uod then
    rmmes.post(ueat_fac,ueat_invalid_uod_code,pl.v.use_of_degree);
    raise;

end constraint_check;

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

    The short_name parameter should contain the association 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, id IN number)
is
  existid number;
  dummy number;
begin
  if short_name is null then
    raise ueat_invalid_sn;
  end if;
  if substr(short_name,1,1) not in ('A','a') then
    raise ueat_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 ueat_invalid_sn;
    WHEN OTHERS THEN
      RAISE;
  END;
  
  if dummy < 0 or dummy > 499 then
    raise ueat_invalid_sn;
  end if;

  --  Duplicate short name validation
  
  begin
    select id into existid
      from rm_element_types et
     where et.short_name = short_name_check.short_name;
    if short_name_check.id is null or short_name_check.id != existid then
      raise ueat_duplicate_sn;
    end if;
  exception when no_data_found then
    null;
  end;

exception
  when ueat_invalid_sn then
    rmmes.post(ueat_fac,ueat_invalid_sn_code,short_name);
    raise;
  when ueat_duplicate_sn then
    rmmes.post(ueat_fac,ueat_duplicate_sn_code,short_name);
    raise;

end short_name_check;
/*
^L
*/
/*-------------------------------- check_access ----------------------------*/
/*
  NAME
    check_access - Validate access to a given association type
  DESCRIPTION
    This procedure validates a given association type id and makes sure that
    it exists, is a user extendible type, and is not published.

    The id parameter should contain the association type's id.
  NOTES
  EXCEPTION
    ueat_not_exist    - Association type does not exist
    ueat_not_user     - Exists but is not user extendible
    ueat_is_published - Exists and is extendible but is published
  RETURNS
*/

procedure check_access(id IN number, care_if_pub IN varchar2 default 'N') 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 ueat_not_user;
  end if;

  if substr(ue,3,1) = 'Y' and care_if_pub = 'N' then
    raise ueat_is_published;
  end if;
  if substr(ue,3,1) = 'N' and care_if_pub = 'M' then
    raise ueat_is_not_published;
  end if;

  return;

--
--  Exceptions
--

exception
  when no_data_found then
    rmmes.post(ueat_fac,ueat_not_exist_code,id);
    raise;
  when ueat_not_user then
    rmmes.post(ueat_fac,ueat_not_user_code,sn);
    raise;
  when ueat_is_published then
    rmmes.post(ueat_fac,ueat_is_published_code,sn);
    raise;     -- Only recently added - not in Bruce's original, I wonder why?
  when ueat_is_not_published then
    rmmes.post(ueat_fac,ueat_is_not_published_code,sn);
    raise;

end check_access;

--
--  Package instantiation block
--

begin

  select id into et_et_id
    from rm_element_types
   where name = 'ELEMENT_TYPE';
  select id,
         primary_row_type
    into et_ea_id,
         ee_primary_row_type
    from rm_element_types
   where name = 'EXTENDED_STRUCTURE_ELEMENT';
  select help_tag
    into ee_help_tag
    from rm_element_type_extensions
   where for_type = et_ea_id;

  select nls_language into dict_lang
    from rm_repositories;

  -- Get supertype of the Axxx element (MM)
  select id
    into et_eas_id
    from rm_element_types
   where short_name = 'MM';

end cioue_association_type;
/