rem 
rem $Header: L:\\\\model\\repman40\\api\\RCS\\ciuepy.opb 1.1 1998/05/28 16:57:05 mfrobins Exp $ 
rem 
rem $Header: L:\\\\model\\repman40\\api\\RCS\\ciuepy.opb 1.1 1998/05/28 16:57:05 mfrobins Exp $ 
rem 
rem Copyright (c) Oracle Corporation 1991.  All Rights Reserved.

rem  NAME
rem    ciuepy.opb - Operation Package Body
rem  DESCRIPTION
rem    This package is responsible for managing UE property
rem    extensions to the CASE Repository.
rem  PUBLIC PROCEDURE(S)
rem    upd              - UPDate an existing property
rem    publish          - PUBLISH an existing property
rem  PRIVATE PROCEDURE(S)
rem    check_access     - Validate access to property
rem    read_values      - Read current values of a property
rem    constraint_check - Perform constraint checks
rem  NOTES
rem  MODIFIED    (MM/DD/YY)
rem    bferris    02/24/95 -  Changed publish to set 'is_displayed' = 'Y'
Rem    aheath     02/14/95 -  Merged into api release area
rem    bferris    01/09/95 -  Creation


CREATE OR REPLACE PACKAGE BODY cioue_property IS

/*  END OF PRIVATE GLOBALS  */

/*  PRIVATE PROCEDURE PROTOTYPES  */

/*--------------------------------------------------------------------------*/
/*  check_access - Validate access to a property                            */
procedure check_access(id IN number, care_if_pub IN varchar2 default 'N');

/*--------------------------------------------------------------------------*/
/*  read_values - Read current values of a property                         */
procedure read_values(id IN number,pl IN data, outpl OUT data);

/*--------------------------------------------------------------------------*/
/*  constraint_check - Perform and apply constraints                        */
procedure constraint_check(id IN number,pl IN data);

/*  END OF PRIVATE PROCEDURE PROTOTYPES  */

/*  PUBLIC PROCEDURES  */

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

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

    The pl parameter should contain the property values to change.
  NOTES
  EXCEPTION
    uepy_not_exist    - Property does not exist
    uepy_not_user     - Exists but is not user extendible
    uepy_is_published - Exists and is extendible but is published
    uepy_dname_missing     - Missing 'display_name'
    uepy_unknown_datatype  - Unknown data type
    uepy_invalid_length    - Invalid length
    uepy_invalid_precision - Invalid precision
    uepy_invalid_scale     - Invalid scale
  RETURNS
*/

procedure upd(id IN     number,
              pl IN OUT data) is
  usrext varchar2(3);
  ntm    number;
  ownet  number;
  ourpl  data;      -- Merged values
  oldpl  data;      -- old values
  dummy  varchar2(1);
  care_if_pub varchar2(1) default 'D'; -- Do I care if this element is published
begin
  
  -- Cannot trust indicator variables on update - always set to true!

  select pyx.display_data_type,
         pyx.display_length,
         pyx.display_precision,
         pyx.display_scale,
         py.mandatory,
         py.case_mode
    into oldpl.v.display_datatype,
         oldpl.v.display_length,
         oldpl.v.display_precision,
         oldpl.v.display_scale,
         oldpl.v.is_mandatory,
         oldpl.v.case_mode
    from rm_property_extensions pyx
        ,rm_properties py
   where pyx.for_property = upd.id
     and py.id = upd.id;

  -- prop name                updateable if published?
  ----------------------------------------------
  -- id                       N
  -- for_element_type         N
  -- display_name             Y
  -- display_datatype         N, unless DATE and NUMBER -> CHAR only
  -- display_length           N, unless bigger 
  -- display_precision        N, unless bigger
  -- display_scale            N, unless bigger
  -- is_mandatory             N, unless Y -> N
  -- case_mode                N, unless U and L -> M only
  -- display_sequence         Y
  -- is_displayed             Y
  -- is_updateable            Y

  -- I care if the property is published or not if ...
  if pl.i.id or pl.i.for_element_type then
    care_if_pub := 'N';
  elsif pl.v.display_datatype != oldpl.v.display_datatype and pl.v.display_datatype != 'CHAR' then
    care_if_pub := 'N';
  elsif pl.v.display_length > oldpl.v.display_length then
    care_if_pub := 'N';
  elsif pl.v.display_precision > oldpl.v.display_precision then
    care_if_pub := 'N';
  elsif pl.v.display_scale > oldpl.v.display_scale then
    care_if_pub := 'N';
  elsif pl.v.is_mandatory != oldpl.v.is_mandatory and pl.v.is_mandatory != 'N' then
    care_if_pub := 'N';   -- Used to check if ET was UE, but all of them have
                          -- is_mandatory = 'N' anyway, so who cares?
  elsif pl.v.case_mode != oldpl.v.case_mode and pl.v.case_mode != 'M' then
    care_if_pub := 'N';
  end if;

  --  Check access and read in missing values

  check_access(id, care_if_pub);

  read_values(id, pl, ourpl);

  --  Check constraints

  constraint_check(id,ourpl);

  --  Read some stuff and lock the row(s)

  select user_extension,
         number_of_times_modified,
         defined_against
    into usrext,
         ntm,
         ownet
    from rm_properties py
   where id = upd.id
     for update;

  select null into dummy
    from rm$property_extensions
   where pid = upd.id
     for update;

  -- EJH 16-OCT-2000 Bug #1429891 -->
  -- replaced original statement below
  -- with code supplied by Oracle Japan
  --
  -- select null into dummy
  --  from rm$nls_properties
  -- where pid = upd.id
  --  for update;

  select null into dummy
   from rm$nls_properties
  where pid = upd.id
   and nls_language = ( select nls_language from rm$repositories )
  for update;

  -- <-- EJH 16-OCT-2000 Bug #1429891 

  select null into dummy
    from rm_element_types et
   where et.id = ownet
     for update;

  --  Write the data to RM_PROPERTIES

  update rm_properties
     set user_extension           = substr(usrext,1,1) ||
                                    'Y' ||
                                    substr(usrext,3,1),
         displayed                = ourpl.v.is_displayed,
         update_mode              = decode(ourpl.v.is_updateable,'Y','U','N'),
         case_mode                = ourpl.v.case_mode,
         mandatory                = ourpl.v.is_mandatory,
         date_changed             = sysdate,
         changed_by               = user
   where id = upd.id;

  --  Write the data to rm$property_extensions

  update rm$property_extensions
     set display_sequence = ourpl.v.display_sequence,
         data_type        = ourpl.v.display_datatype,
         length           = ourpl.v.display_length,
         precision        = ourpl.v.display_precision,
         scale            = ourpl.v.display_scale
   where pid = upd.id;

  --  Write the data to rm$nls_properties

  update rm$nls_properties
     set nls_name         = ourpl.v.display_name
   where pid = upd.id;

  --  Update the element type to be 'extended'

  update rm_element_types
     set user_extension = substr(user_extension,1,1) ||
                          'Y' ||
                          substr(user_extension,3,1)
   where id = ownet;

  --  Return updated property list to caller

  pl := ourpl;

end upd;

/*
^L
*/
/*-------------------------------- publish ---------------------------------*/
/*
  NAME
    publish - PUBLISH an existing extended property
  DESCRIPTION
    This procedure publishes the details for a given extended
    property.

    The id parameter should indicate the property to publish.
  NOTES
  EXCEPTION
    uepy_not_exist    - Property does not exist
    uepy_not_user     - Exists but is not user extendible
    uepy_is_published - Exists and is extendible but is published
  RETURNS
*/

procedure publish(id IN number) is
  ownet number;
  dummy varchar2(1);
begin
  --  Check access and read in the missing property values

  check_access(id);
  
  --  Read some stuff and lock the row(s)

  select defined_against
    into ownet
    from rm_properties py
   where id = publish.id
     for update;

  select null into dummy
    from rm_element_types et
   where et.id = ownet
     for update;

  --  Update it

  update rm_properties
     set user_extension = substr(user_extension,1,2)||'Y',
         displayed = 'Y'
   where id = publish.id;

  --  Update the element type to be 'extended'

  update rm_element_types
     set user_extension = substr(user_extension,1,1) ||
                          'Y' ||
                          substr(user_extension,3,1)
   where id = ownet;

end publish;


/*
^L
*/
/*-------------------------------- remap -- ---------------------------------*/
/*
  NAME
    remap - REMAP an existing extended property to another underlying column
  DESCRIPTION
    This procedure remaps property old_pid to unpublished property new_pid
    and vice versa.

    The old_pid parameter should indicate an existing published property.

    The new_pid parameter should indicate an existing unpublished property in
      the same element type.
  NOTES
  EXCEPTION
    uepy_not_exist    - Property does not exist
    uepy_not_user     - Exists but is not user extendible
    uepy_is_published - Exists and is extendible but is published
  RETURNS
*/

procedure remap(old_pid IN number, new_pid IN number) is
  ownet number;
  ownet2 number;
  dummy varchar2(1);
  is_ex varchar2(1);
  is_pub varchar2(1);
  old_name varchar2(30);
  new_name varchar2(30);
  new_col_ref number;
  old_col_ref number;
  new_col_name varchar2(30);
  old_col_name varchar2(30);
  base_table_name varchar2(30);
  type_column_name varchar2(30);
  str varchar2(100);
  ret integer;
  cur integer;
begin
  --  Check access and read in the missing property values

  -- B646820: There is no need for property to be published before remap
  -- check_access(old_pid, 'M'); -- must be published
  check_access(new_pid, 'N'); -- must not be published
  
  --  Read some stuff and lock the row(s)

  select defined_against
        ,substr(user_extension,2,1)
        ,name
        ,substr(user_extension,3,1)
    into ownet, is_ex, old_name, is_pub
    from rm_properties py
   where id = remap.old_pid
     for update;

  if is_ex != 'Y' then
    rmmes.post(uepy_fac, uepy_is_not_extended_code, old_name);
    raise uepy_is_not_extended;
  end if;

  select defined_against, substr(user_extension,2,1), name
    into ownet2, is_ex, new_name
    from rm_properties py
   where id = remap.new_pid
     for update;

  if is_ex != 'N' then
    rmmes.post(uepy_fac, uepy_is_extended_code, new_name);
    raise uepy_is_extended;
  end if;

  if ownet != ownet2 then
    rmmes.post(uepy_fac, uepy_different_ets_code, old_name, new_name);
    raise uepy_different_ets;
  end if;

  select null into dummy
    from rm_element_types et
   where et.id = ownet
     for update;

/*
  --  Update properties

  update rm_properties
     set user_extension = substr(user_extension,1,1) || 'NN'
   where id = old_pid;

  update rm_properties
     set user_extension = substr(user_extension,1,1) ||
                          'Y' || is_pub
   where id = new_pid;
*/
  update rm_properties
     set name = old_name
   where id = new_pid;

  update rm_properties
     set name = new_name
   where id = old_pid;

  update rm$nls_properties
     set nls_name = 'Usrx' || ltrim(new_name, 'USER_DEFINED_PROPERTY_')
   where pid = old_pid;

  update rm$nls_properties
     set nls_name = 'Usrx' || ltrim(old_name, 'USER_DEFINED_PROPERTY_')
   where pid = new_pid;

  --  Update property maps

  select pm1.in_column, pm2.in_column
    into old_col_ref, new_col_ref
    from rm_property_maps pm1
        ,rm_property_maps pm2
   where pm1.property = old_pid
     and pm2.property = new_pid;

  update rm_property_maps
     set in_column = new_col_ref
   where property = old_pid;

  update rm_property_maps
     set in_column = old_col_ref
   where property = new_pid;

  -- Update instance data
  select sc1.name, sc2.name, st.name
    into old_col_name
        ,new_col_name
        ,base_table_name
    from rm_sql_columns sc1
        ,rm_sql_columns sc2
        ,rm_sql_tables st
   where sc1.id = old_col_ref
     and sc2.id = new_col_ref
     and st.id = sc2.in_table;

  cur := dbms_sql.open_cursor;

  -- Eg update sdd_elements set EL_USRX9 = EL_USRX7, EL_USRX7 = null
  --    where el_ty = 1234;

  jr_context.disable(jr_context.NO_CHECKIN_UPDATE);
  jr_context.disable(jr_context.ACCESS_RIGHTS);

  str := 'update ' || base_table_name || ' set ' || new_col_name || ' = '  ;
  str := str || old_col_name || ', ' || old_col_name || ' = null where ';
  str := str || 'types' || ' = ' || ownet;
  dbms_sql.parse(cur, str, dbms_sql.v7);
  ret := dbms_sql.execute(cur);

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

  -- Drop and recreate generated view and package
  -- (no need to change the appxxx
  -- cioue_element_type.drop_views_packs(ownet, null);
  ciue_util.gen_view(ownet);
  ciue_util.gen_spec(ownet);
  ciue_util.gen_body(ownet);

end remap;

/*
^L
*/
/*------------------------------ unpublish ---------------------------------*/
/*
  NAME
    unpublish - UNPUBLISH an existing extended property
  DESCRIPTION
    This procedure publishes the details for a given extended
    property.

    The id parameter should indicate the property to publish.
  NOTES
  EXCEPTION
    uepy_not_exist    - Property does not exist
    uepy_not_user     - Exists but is not user extendible
    uepy_is_not_published - Exists and is extendible but is published
  RETURNS
*/

procedure unpublish(id IN number) is
  ownet number;
  dummy varchar2(1);
  py_name varchar2(40);
  el_plural_name varchar2(40);
  el_is_pub varchar2(1);
  str   varchar2(240);
  cur integer;            -- cursor
  ret integer;            -- 
  instance_count integer; -- how much instance data is there?
begin
  --  Check access and read in the missing property values

  check_access(id,'M');
  
  --  Read some stuff and lock the row(s)

  select defined_against, name
    into ownet, py_name
    from rm_properties py
   where id = unpublish.id
     for update;

  -- Lock the row and get info
  select plural_name, substr(user_extension,3,1)
    into el_plural_name, el_is_pub
    from rm_element_types et
   where et.id = ownet
     for update;

  if el_is_pub = 'Y' then
    -- If element is published, check instance data does not exist
    str := 'select count(*) from ci_' || el_plural_name || ' where ' 
           || py_name || ' is not null';
  
    cur := dbms_sql.open_cursor;
    dbms_sql.parse(cur, str, dbms_sql.v7);
    dbms_sql.define_column(cur, 1, instance_count);
  
    ret := dbms_sql.execute(cur);
  
    if dbms_sql.fetch_rows(cur) > 0 then
      dbms_sql.column_value(cur, 1, instance_count);
    end if;
  
    dbms_sql.close_cursor(cur);
  
    if instance_count > 0 then
      rmmes.post(uepy_fac, uepy_data_exists_code, py_name);
      raise uepy_data_exists;
    end if;
  end if;

  --  Update it

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

  --  Update the element type to be 'unextended' only if there are no
  --   extended properties against it.

  update rm_element_types et
     set user_extension = substr(et.user_extension,1,1) ||
                          'N' ||
                          substr(et.user_extension,3,1)
   where id = ownet
     and not exists (select null
                       from rm_properties py
                      where py.defined_against = et.id
                        and substr(py.user_extension,2,1) = 'Y');

end unpublish;

/*  END OF PUBLIC PROCEDURES  */

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

    The id parameter should contain the property'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
    uepy_not_exist    - Property does not exist
    uepy_not_user     - Exists but is not user extendible
    uepy_is_published - Exists and is extendible but is published
  RETURNS
*/

procedure check_access(id IN number, care_if_pub IN varchar2) is
  name   rm_property_extensions.nls_name%type;
  is_up  varchar2(1);
  is_pub varchar2(1);
begin
 
  --  Get some info about the property

  select pyx.nls_name,
         substr(py.user_extension,1,1),
         substr(py.user_extension,3,1)
    into name,
         is_up,
         is_pub
    from rm_property_extensions pyx,
         rm_properties py
   where py.id = check_access.id
         and pyx.for_property = py.id;

  --  Validate access

  if is_up = 'Y' then
    null;
  else
    raise uepy_not_user;
  end if;
  if is_pub = 'Y' and care_if_pub = 'N' then
    raise uepy_is_published;
  end if;
  if is_pub = 'N' and care_if_pub = 'M' then
    raise uepy_is_not_published;
  end if;

  return;

--
--  Exceptions
--

exception
  when no_data_found then
    rmmes.post(uepy_fac,uepy_not_exist_code,id);
    raise;
  when uepy_not_user then
    rmmes.post(uepy_fac,uepy_not_user_code,name);
    raise;
  when uepy_is_published then
    rmmes.post(uepy_fac,uepy_is_published_code,name);
    raise;
  when uepy_is_not_published then
    rmmes.post(uepy_fac,uepy_is_not_published_code,name);
    raise;

end check_access;

/*
^L
*/
/*-------------------------------- read_values -----------------------------*/
/*
  NAME
    read_values - Read current values of a property
  DESCRIPTION
    This procedure populates a property record with the values that have
    pl.i.<x> = false.
  NOTES
    This procedure assumes that 
  EXCEPTION
    uepy_not_exist - Property does not exist
  RETURNS
*/

procedure read_values(id IN number, pl IN data, outpl OUT data) is
begin

  --  Get current values

  select py.id,
         py.defined_against,
         pyx.nls_name,
         pyx.display_data_type,
         pyx.display_length,
         pyx.display_precision,
         pyx.display_scale,
         py.mandatory,
         py.case_mode,
         pyx.display_sequence,
         py.displayed,
         py.updateable
    into outpl.v.id,
         outpl.v.for_element_type,
         outpl.v.display_name,
         outpl.v.display_datatype,
         outpl.v.display_length,
         outpl.v.display_precision,
         outpl.v.display_scale,
         outpl.v.is_mandatory,
         outpl.v.case_mode,
         outpl.v.display_sequence,
         outpl.v.is_displayed,
         outpl.v.is_updateable
    from rm_property_extensions pyx,
         rm_properties py
   where py.id = read_values.id
         and pyx.for_property = py.id;

  --  Merge in any user specified values

  outpl.v.id := id;

  if pl.i.for_element_type then
    outpl.v.for_element_type := pl.v.for_element_type;
  end if;
  if pl.i.display_name then
    outpl.v.display_name      := pl.v.display_name;
  end if;
  if pl.i.display_datatype then
    outpl.v.display_datatype  := upper(pl.v.display_datatype);
  end if;
  if pl.i.display_length then
    outpl.v.display_length    := pl.v.display_length;
  end if;
  if pl.i.display_precision then
    outpl.v.display_precision := pl.v.display_precision;
  end if;
  if pl.i.display_scale then
    outpl.v.display_scale     := pl.v.display_scale;
  end if;
  if pl.i.is_mandatory then
    outpl.v.is_mandatory      := pl.v.is_mandatory;
  end if;
  if pl.i.case_mode then
    outpl.v.case_mode         := pl.v.case_mode;
  end if;
  if pl.i.display_sequence then
    outpl.v.display_sequence  := pl.v.display_sequence;
  end if;
  if pl.i.is_displayed then
    outpl.v.is_displayed      := pl.v.is_displayed;
  else
    outpl.v.is_displayed      := 'Y';
  end if;
  if pl.i.is_updateable then
    outpl.v.is_updateable     := pl.v.is_updateable;
  end if;

  return;

--
--  Exceptions
--

exception 

  when no_data_found then
    rmmes.post(uepy_fac,uepy_not_exist_code,id);
    raise uepy_not_exist;

end read_values;

/*
^L
*/
/*-------------------------------- constraint_check ------------------------*/
/*
  NAME
    constraint_check - Check and apply constraints
  DESCRIPTION
    This procedure checks and applies required constraints.
  NOTES
  EXCEPTION
    uepy_dname_missing     - Missing 'display_name'
    uepy_unknown_datatype  - Unknown data type
    uepy_invalid_length    - Invalid length
    uepy_invalid_precision - Invalid precision
    uepy_invalid_scale     - Invalid scale
    uepy_invalid_mandatory - Invalid 'is_mandatory' flag
  RETURNS
*/

procedure constraint_check(id IN number, pl IN data) is

begin

  --  Make sure display_name is specified

  if pl.v.display_name is null then
    raise uepy_dname_missing;
  end if;

  --  Validate datatype

  if pl.v.display_datatype not in
    ('CHAR','NUMBER','DATE') then
    raise uepy_unknown_datatype;
  end if;

  --  Validate length, precision, and scale

  if pl.v.display_datatype = 'NUMBER' then
    if pl.v.display_length is null or
       pl.v.display_length < 0 or
       pl.v.display_length > 240 then
      raise uepy_invalid_length;
    end if;
    if pl.v.display_precision is not null then
      if pl.v.display_precision <= 0 or
         pl.v.display_precision > 38 then
        raise uepy_invalid_precision;
      end if;
    end if;
    if pl.v.display_scale is not null then
      if pl.v.display_scale < 0 or
         pl.v.display_scale > pl.v.display_precision then
        raise uepy_invalid_scale;
      end if;
    end if;
  elsif pl.v.display_datatype = 'DATE' then
    if pl.v.display_length is null then
      raise uepy_invalid_length;
    end if;
    if pl.v.display_length > 240 then
      raise uepy_invalid_length;
    end if;
    if pl.v.display_precision is not null then
      raise uepy_invalid_precision;
    end if;
    if pl.v.display_scale is not null then
      raise uepy_invalid_scale;
    end if;
  else
    if pl.v.display_length is null or
       pl.v.display_length > 240 then
      raise uepy_invalid_length;
    end if;
    if pl.v.display_precision is not null then
      raise uepy_invalid_precision;
    end if;
    if pl.v.display_scale is not null then
      raise uepy_invalid_scale;
    end if;
  end if;

  --  Validate misc things

  if pl.i.is_mandatory then
    if pl.v.is_mandatory not in ('Y','N') then
      raise uepy_invalid_mandatory;
    end if;
  end if;

  return;

--
--  Exceptions
--

exception 

  when uepy_dname_missing     then
    rmmes.post(uepy_fac,uepy_dname_missing_code,id);
    raise;
  when uepy_unknown_datatype  then
    rmmes.post(uepy_fac,uepy_unknown_datatype_code,
               pl.v.display_name,pl.v.display_datatype);
    raise;
  when uepy_invalid_length    then
    rmmes.post(uepy_fac,uepy_invalid_length_code,
               pl.v.display_name,pl.v.display_datatype,pl.v.display_length);
    raise;
  when uepy_invalid_precision then
    rmmes.post(uepy_fac,uepy_invalid_precision_code,
               pl.v.display_name,pl.v.display_datatype,pl.v.display_precision);
    raise;
  when uepy_invalid_scale     then
    rmmes.post(uepy_fac,uepy_invalid_scale_code,
               pl.v.display_name,pl.v.display_datatype,pl.v.display_scale);
    raise;
 
  when uepy_invalid_mandatory then
    rmmes.post(uepy_fac,uepy_invalid_mandatory_code,
               pl.v.is_mandatory);
    raise;

end constraint_check;

/*  PRIVATE PROCEDURES  */

end cioue_property;
/