rem 
rem $Header: L:\\\\model\\repman40\\api\\RCS\\ciuetu.opb 1.2 1998/11/25 16:18:11 cvanes Exp $ 
rem 
rem Copyright (c) Oracle Corporation 1991.  All Rights Reserved.

rem  NAME
rem    ciuetu.opb             - Operation Package Body
rem  DESCRIPTION
rem    This package is responsible for managing UE text type
rem    usage extensions to the CASE Repository.
rem  PUBLIC PROCEDURE(S)
rem    ins         - INSert a new extended text type usage
rem    upd         - UPDate an existing text type usage
rem    del         - DELete an existing text type usage
rem    publish     - PUBLISH an extended text type usage
rem  PRIVATE PROCEDURE(S)
rem    tu_validate - Validate a text type usage
rem    tu_exists   - Make sure a text type usage exists
rem    tu_access   - Validate operation access to a text type usage
rem  NOTES
rem  MODIFIED   (MM/DD/YY)
rem    hdodswor  05/04/2000 - Fix bug 1271492, DISPLAY_DATA_TYPE on RM_PROPERTY_EXTENSIONS should be 'MLT'
Rem     aheath     02/14/95 -  Creation 
rem    bferris   12/22/94 -  Creation


CREATE OR REPLACE PACKAGE BODY cioue_text_usage IS

/*  PRIVATE PROCEDURE PROTOTYPES  */

/*-------------------------------- tu_validate -----------------------------*/
/*  tu_validate - Validate a text type usage                                */
procedure tu_validate(text_type IN varchar2,
                      etid      IN number);

/*-------------------------------- tu_access -------------------------------*/
/*  tu_access - Validate operation access to a usage                        */
procedure tu_access(text_type IN varchar2,
                    etid      IN number, 
                    care_if_pub IN varchar2 default 'N');

/*  PUBLIC PROCEDURES  */

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

    The text_type parameter should contain the text type's name.

    The eid parameter should indicate the element type ID of the
    using the text type.

    The seq parameter should indicate relative sequence of the text
    type in the element type's usage list.
  NOTES
    For now we will allow the caller to insert new 'non-user'
    text types.
  EXCEPTION
    uetu_unknown_tt       - Unknown text type
    uetu_unknown_et       - Unknown element type
    uetu_exists           - Text usage already exists
    uetu_tt_not_published - Text type is not published
  RETURNS
*/

procedure ins(text_type IN varchar2,
              etid      IN number,
              seq       IN number) is
  dummy        varchar2(1);
begin

  --  Validate usage into

  tu_validate(text_type,etid);

  --  Make sure it doesn't already exist

  begin
    select null into dummy
      from rm$text_usages
     where text_type = ins.text_type
           and etid = ins.etid;
    raise uetu_exists;
  exception
    when no_data_found then
      null;
  end;

  --  Create RM$TEXT_USAGES

  insert into rm$text_usages
         (etid,
          el_type,
          el_occur_type,
          seq,
          text_type,
          is_extended,
          is_published)
  values (ins.etid,
          null,
          null,
          seq,
          text_type,
          'Y',
          'N');
  
  return;

--
--  Exceptions
--

exception

  when uetu_exists then
    rmmes.post(uetu_fac,uetu_exists_code,text_type,etid);
    raise;

end ins;

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

    The text_type parameter should contain the name of the text type
    associated with the usage.

    The etid parameter should contain the ID of the element type
    associated with the usage.

    The seq parameter should contain the relative sequence of the usage
    within the element type.
  NOTES
  EXCEPTION
    uetu_unknown_tt     - Unknown text type
    uetu_unknown_et     - Unknown element type
    uetu_unknown_tu     - Unknown text type usage
    uetu_not_user_usage - Not a user extendible usage
    uetu_is_published   - The text type usage is already published
  RETURNS
*/

procedure upd(text_type IN varchar2,
              etid      IN number,
              seq       IN number) is
begin

  --  Validate that we have operation access to it
  tu_access(text_type,etid, 'D'); -- For update, don't care if published

  --  Update the sequence
  update rm$text_usages
     set seq = upd.seq
   where text_type = upd.text_type
         and etid  = upd.etid;

end upd;


/*
^L
*/
/*-------------------------------- del -------------------------------------*/
/*
  NAME
    del - DELete an existing text type usage
  DESCRIPTION
    This procedure deletes the details for a given text type usage if it
    has not been published.

    The text_type parameter should indicate the usage's text type.

    The etid parameter should indicate the usage's element type's ID.
  NOTES
  EXCEPTION
    uetu_unknown_tt     - Unknown text type
    uetu_unknown_et     - Unknown element type
    uetu_unknown_tu     - Unknown text type usage
    uetu_not_user_usage - Not a user extendible usage
    uetu_is_published   - The text type usage is already published
  RETURNS
*/

procedure del(text_type IN varchar2, etid IN number) is
begin

  --  Validate that we have operation access to it
  tu_access(text_type,etid);

  --  Do the delete
  delete from rm$text_usages
   where text_type = del.text_type
         and etid = del.etid;

end del;

/*
^L
*/
/*-------------------------------- publish ---------------------------------*/
/*
  NAME
    publish - PUBLISH an existing text type
  DESCRIPTION
    This procedure publishes the details for a given text type if it
    is a user text type and it has not been published.

    The text_type parameter should indicate the usage's text type.

    The etid parameter should indicate the usage's element type's ID.
  NOTES
  EXCEPTION
    uett_invalid_type     - Invalid text type
    uett_unknown_type     - Unknown text type (it doesn't exist)
    uett_not_user_type    - Denied: Not a user extended type
    uett_is_published     - Denied: Text type usage is published
    uett_tt_not_published - Denied: Text type is not published
  RETURNS
*/

procedure publish(text_type IN varchar2, etid IN number) is
  nls_name    varchar2(240);
  seq         number;
  old_product varchar2(240);
begin
  -- Remember current product setting
  old_product := jr_rm_ins.get_product;

  --  Validate that we have operation access to it
  tu_access(text_type,etid);

  --  Do the publish
  update rm$text_usages
     set is_published = 'Y'
   where text_type = publish.text_type
         and etid  = publish.etid;

  -- CW 13-Mar-2000
  -- Fix bug 1035564, create property, extension and property map for
  -- user defined text usages, note, we have to create a property first !
  -- Call JR api to create property, extension and property map

  select t.nls_desc
       , t.seq
  into   nls_name
       , seq
  from   rm_text_usages t
  where  t.text_type    = upper(publish.text_type)
  and    t.element_type = publish.etid
  ;

  -- Set product code before using jr_rm_ins package
  jr_rm_ins.set_product_code('CI');

  jr_rm_ins.rm_property_ins
    ( i_element_type_irid => publish.etid
    , i_property_irid     => ciilok.idgen
    , i_srt_id            => null
    , i_column_id         => null
    , i_prop_name         => upper(publish.text_type)
    , i_data_type         => 'MLT'  -- Fix bug 1271492, DISPLAY_DATA_TYPE on RM_PROPERTY_EXTENSIONS should be 'MLT'
    , i_data_length       => null
    , i_data_precision    => null
    , i_data_scale        => null
    , i_nullable          => 'Y'
    , i_default_length    => null
    , i_data_default      => null
    , i_prop_nls_name     => nls_name
    , i_prop_nls_default  => null
    , i_prop_nls_hint     => nls_name
    , i_prop_display_seq  => seq
    , i_user_extension    => 'YYY'
    , i_domain_name       => 'RM_MLT'
    );

  -- Reset product code
  jr_rm_ins.set_product_code(old_product);

  --  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 = publish.etid;

exception
  when others then
    -- Reset product code
    jr_rm_ins.set_product_code(old_product);
    raise;
end publish;

/*
^L
*/
/*------------------------------ unpublish ---------------------------------*/
/*
  NAME
    unpublish - UNPUBLISH an existing text type
  DESCRIPTION
    This procedure unpublishes the details for a given text type if it
    is a user text type and it has been published.

    The text_type parameter should indicate the usage's text type.

    The etid parameter should indicate the usage's element type's ID.
  NOTES
  EXCEPTION
    uett_invalid_type     - Invalid text type
    uett_unknown_type     - Unknown text type (it doesn't exist)
    uett_not_user_type    - Denied: Not a user extended type
    uett_is_published     - Denied: Text type usage is published
    uett_tt_not_published - Denied: Text type is not published
  RETURNS
*/

procedure unpublish(text_type IN varchar2, etid IN number) is
  dummy number;
  tab_name varchar2(40);
  big_str3 varchar2(1000);
  cur_id3 integer;
  ret_val integer;
begin

  --  Validate that we have operation access to it
  tu_access(text_type,etid,'M');
  
  tab_name := ciiexp.get_table_name(etid);

  -- B992255 - VDESAI - 14/09/99
  big_str3 := 'select null from rm_text_lines txt ,' ||
    tab_name || ' el where txt.txt_type = :text_type ' ||
    'and txt.txt_ref = el.irid';
  cur_id3 := dbms_sql.open_cursor;

  dbms_sql.parse(cur_id3, big_str3, dbms_sql.native);

  dbms_sql.bind_variable(cur_id3, ':text_type', text_type); 

  ret_val := dbms_sql.execute(cur_id3);

  -- if rows are returned then
  if dbms_sql.fetch_rows(cur_id3) > 0 then
    rmmes.post(uetu_fac,uetu_data_exists_code, text_type);
    raise uetu_data_exists;
  end if;
  dbms_sql.close_cursor(cur_id3);

  --  Do the unpublish
  update rm$text_usages
     set is_published = 'N'
   where text_type = unpublish.text_type
         and etid = unpublish.etid;

  -- CW 13-Mar-2000
  -- Fix bug 1035564, remove property ,extension and property map for
  -- user defined text usage
  -- Call JR api to delete property, extension and property map
  jr_rm_del.rm_property_del ( unpublish.etid              -- type id
                            , upper(unpublish.text_type)  -- property name
                            );



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

/*  PRIVATE PROCEDURE  */

/*
^L
*/
/*-------------------------------- tu_validate -----------------------------*/
/*
  NAME
    tu_validate - Validate that a text type usage is valid
  DESCRIPTION
    This procedure validates a text type usage to ensure that the
    text type and element type exist.

    The text_type parameter should contain the text type for the usage.

    The etid parameter should indicate the id of the element type
    the usage is for.
  NOTES
    If an exception is raised it will *not* have been posted on the
    error stack.
  EXCEPTION
    uetu_unknown_tt       - Unknown text type
    uetu_unknown_et       - Unknown element type
    uetu_tt_not_published - Text type is not published
  RETURNS
*/

procedure tu_validate(text_type IN varchar2, etid IN number) is
  dummy varchar2(1);
begin
  begin
    select is_published into dummy
      from rm_text_types
     where text_type = tu_validate.text_type;
    if dummy != 'Y' then
      raise uetu_tt_not_published;
    end if;
  exception when no_data_found then
    raise uetu_unknown_tt;
  end;
  begin
    select 'Y' into dummy
      from rm_element_types
     where id = tu_validate.etid;
  exception when no_data_found then
    raise uetu_unknown_et;
  end;

--
--  Exceptions
--

exception
  when uetu_unknown_tt then
    rmmes.post(uetu_fac,uetu_unknown_tt_code,text_type);
    raise;
  when uetu_unknown_et then
    rmmes.post(uetu_fac,uetu_unknown_et_code,etid);
    raise;
  when uetu_unknown_tu then
    rmmes.post(uetu_fac,uetu_unknown_tu_code,text_type,etid);
    raise;
  when uetu_tt_not_published then
    rmmes.post(uetu_fac,uetu_tt_not_published_code,text_type);
    raise;
end tu_validate;

/*
^L
*/
/*-------------------------------- tu_access -------------------------------*/
/*
  NAME
    tu_access - Validate operation access to a given text type usage
  DESCRIPTION
    This procedure checks to see if a given text type usage exists.

    The text_type parameter should contain the usage's text type name.

    The etid parameter should contain the usage's element type's id.
  NOTES
    If an exception is raised the error will have been reported on
    the error stack.
  EXCEPTION
    uetu_unknown_tt     - Unknown text type
    uetu_unknown_et     - Unknown element type
    uetu_unknown_tu     - Unknown text type usage
    uetu_not_user_usage - Not a user extendible usage
    uetu_is_published   - The text type usage is already published
  RETURNS
*/

procedure tu_access(text_type IN varchar2, etid IN  number, care_if_pub IN varchar2) is
  is_ext varchar2(1);
  is_pub varchar2(1);
begin
  tu_validate(text_type,etid);
  --  Make sure it's a user extendible usage and it's not published
  select is_extended,
         is_published
    into is_ext,
         is_pub
    from rm$text_usages
   where text_type = tu_access.text_type
         and etid = tu_access.etid;
  if is_ext != 'Y' then
    raise uetu_not_user_usage;
  end if;
  if is_pub = 'Y'  and care_if_pub = 'N' then
    raise uetu_is_published;
  end if;
  if is_pub = 'N'  and care_if_pub = 'M' then
    raise uetu_is_not_published;
  end if;
  
--
--  Exceptions
--

exception

  when uetu_not_user_usage then
    rmmes.post(uetu_fac,uetu_not_user_usage_code);
    raise;

  when uetu_is_published then
    rmmes.post(uetu_fac,uetu_is_published_code);
    raise;

end tu_access;

/*  END OF PRIVATE PROCEDURES  */

end cioue_text_usage;
/