rem
rem $Header: \\ukst76\rcsroot.4_0\model\repadm40\ins\RCS\ckonls.hpb 1.3 1995/09/15 14:12:36 dedwards Fz_R1_2 $ ckonls.hpb Copyr (c) 1992, 1989 Oracle
rem
rem NAME
rem   ckonls.hps
rem DESCRIPTION
rem   Creates packages fro loading/deleting of translation text files 
rem RETURNS
rem   None
rem NOTES
rem   Requires 6.0.7 or higher version of repository
rem MODIFIED   (MM/DD/YY)
rem   aobhrai   5/06/95 -  Creation
rem

PROMPT 
PROMPT Creating Stored Procedure ckonls

create or replace package body ckonls as

-- This package package has been written to support the loading of 
-- NLS text from the table rm_text_line
-- The RAU will directly call the procedures in this package.
-- The types of text are as follows:
--   CKNLDV - Data values
--   CKNLET - Element type names
--   CKNLPY - Property type names
--   CKNLTT - Text type names
-- In each case NLS text will be ordered by TXT_SEQ and TXT_REF = 0.

   procedure del (tab  in varchar2
                 ,lang in varchar2) is

-- This procedure is called to delete all instances of a particular
-- NLS value from a respective table.

   begin
       if tab = 'DV' then
          delete from RM$NLS_DATA_TYPE_VALUES where NLS_LANGUAGE = lang;
       elsif tab = 'ET' then
          delete from RM$NLS_ELEMENT_TYPES    where NLS_LANGUAGE = lang;
       elsif tab = 'TT' then
          delete from RM$NLS_TEXT_TYPES       where NLS_LANGUAGE = lang;
       elsif tab = 'PY' then
          delete from RM$NLS_PROPERTIES       where NLS_LANGUAGE = lang;
       end if;
   end;

   procedure ins (tab  in varchar2
                 ,lang in varchar2) is

-- This procedure inserts instances of NLS values into respective tables

-- Initialise global statistic

   stat_created number := 0; -- number of instances created
   stat_duplic  number := 0; -- number of duplicate ids found
   stat_not_fo  number := 0; -- number of ids not found
   stat_read    number := 0; -- number of rows read

-- Local function get_dv_id will return the id of a Data Value given 
-- its name.

   function get_dv_id ( key_name varchar2 ) return number is
      l_dv_id number;
   begin
      select id
      into   l_dv_id
      from   rm_data_types
      where  name = key_name;
      return (l_dv_id);
   exception
      when no_data_found then return 0;
      when too_many_rows then return -1;
   end;

-- Local function get_et_id will return the id of a Element Type given 
-- its name.

   function get_et_id ( key_name1 varchar2 
                      , key_name2 varchar2 ) return number is
      l_et_id number;
   begin
      select id
      into   l_et_id
      from   rm_element_types
      where  short_name = key_name1
      and    name       = key_name2;
      return (l_et_id);
   exception
      when no_data_found then return 0;
      when too_many_rows then return -1;
   end;

-- Local function get_py_id will return the id of a Property Type given 
-- its short name and name.

   function get_py_id ( key_name1  varchar2
                      , key_name2  varchar2 ) return number is
      l_py_id number;
   begin
      select py.id
      into   l_py_id
      from   rm_properties    py
      ,      rm_element_types et
      where  et.short_name = key_name1
      and    py.name       = key_name2
      and    et.id         = py.defined_against;
      return (l_py_id);
   exception
      when no_data_found then return 0;
      when too_many_rows then return -1;
   end;

-- Local function get_string will return the a string starting at 
-- the startpos_th delimiter bound by the startpos_th+1 delimiter.
-- e.g.  string    = 'THIS,IS,A,,TEST'
-- and   delimiter = ','
--       startpos = 0 would return THIS
--       startpos = 1 would return IS
--       startpos = 2 would return A
--       startpos = 3 would return NULL
--       startpos = 5 would return TEST
-- Note: in the case where startpos = 0 the first string upto the first 
--       comma is returned,
--       in the case where startpos=3 there is no string bound by the
--       commas, therefore a NULL string is returned
--       in the case where startpos = 5 there is no bounding comma
--       at the end of the string, there for the the rest of the
--       string until the end is returned.
--       in all cases the string returned is rtrim'ed and ltrim'ed.

   function get_string ( startpos integer
                       , string   varchar2 ) return  varchar2 is

      delimit varchar2(1) := ',';
      startloc integer;
      endloc   integer;
      rstring  varchar2(1000);

   begin
      if (startpos > 0) then
         startloc := instr(string, delimit ,1,startpos) +1;
         endloc := instr(string, delimit ,1,startpos+1);
      else
         startloc := 0;
         endloc := instr(string, delimit ,1,startpos+1) -1;
      end if;

      if (endloc=0 and startpos > 0) then
        rstring := substr(string,startloc);
      else
        rstring := substr(string,startloc,endloc-startloc);
      end if;
      return ( rtrim(ltrim(rstring)) );
   end;

-- Local procedure display_warning will display the respective warning
-- message 
--  -1 means that a duplicate ID was found
--   0 means that no corresponding ID was found.

   procedure display_warning ( code_number in number 
                             , code_value  in varchar2
                             , nls_str     in varchar2 ) is
   begin
      if code_number  = -1 then
         stat_duplic := stat_duplic + 1;
         dbms_output.put_line('Warning : Too many rows fetching ID for '||
                              nls_str ||' '|| code_value ||
                              ', row skipped...');
      elsif code_number = 0 then
         stat_not_fo := stat_not_fo + 1;
         dbms_output.put_line('Warning : No ID found for '||
                              nls_str ||' '|| code_value ||
                              ', row skipped...');
      end if;
   end;

-- Local procedure do_stats will display the statistics for the 
-- respective NLS text.

   procedure do_stats (tab2 varchar2) is

   begin
      dbms_output.put_line ( ' '                                     );
      dbms_output.put_line ( 'Statistics for '|| tab2 ||' : '        );
      dbms_output.put_line ( ' '                                     );
      dbms_output.put_line ( 'Lines Read : '|| to_char(stat_read)    );
      dbms_output.put_line ( 'Created    : '|| to_char(stat_created) );
      dbms_output.put_line ( 'Duplicate  : '|| to_char(stat_duplic)  );
      dbms_output.put_line ( 'Not Found  : '|| to_char(stat_not_fo)  );
      dbms_output.put_line ( ' '                                     );
   end;

-- local procedure read_dv will handle Data Type Values

   procedure read_dv (lang in varchar2) is
      o_data_type_name varchar2(255);
      l_data_type_name varchar2(255);
      dv_rec            RM$NLS_DATA_TYPE_VALUES%ROWTYPE;
   begin
      o_data_type_name := 'x*&$!'; -- initialise with an impossible value
      dv_rec.seq       := 0;
      for dv in ( select rtrim(translate(TXT_TEXT, chr(10), ' ')) TXT_TEXT
                  from   RM_TEXT_LINES
                  where  TXT_TYPE = 'CKNLDV'
                  and    TXT_REF  = 0
                  order  by TXT_SEQ )
      loop
         stat_read           := stat_read + 1;
         l_data_type_name    := get_string(0,dv.txt_text);
         dv_rec.dev_value    := get_string(2,dv.txt_text);
         dv_rec.nls_value    := get_string(3,dv.txt_text);
         dv_rec.nls_language := lang;

-- if the old data value is the same as the current one we need to 
-- increment the sequence  dv_rec.seq

         if l_data_type_name = o_data_type_name then
            dv_rec.seq := dv_rec.seq + 1;
         else
-- otherwise we need to lookup the id of the data value.
            dv_rec.did := get_dv_id (l_data_type_name);
            dv_rec.seq := 1;
         end if;

         if dv_rec.did < 1 then
            display_warning ( dv_rec.did, l_data_type_name, 'Data Value');
         else
            insert into RM$NLS_DATA_TYPE_VALUES
            ( NLS_LANGUAGE
            , DID
            , SEQ
            , DEV_VALUE
            , NLS_VALUE )
            values
            ( dv_rec.nls_language
            , dv_rec.did
            , dv_rec.seq
            , dv_rec.dev_value
            , dv_rec.nls_value );
            stat_created := stat_created + 1;
         end if;

         o_data_type_name := l_data_type_name;
      end loop;

      do_stats ('DATATYPE VALUES');
   end;

-- local procedure read_et will handle Element Type Names

   procedure read_et (lang in varchar2) is
      l_short_name      varchar2(255);
      l_name            varchar2(255);
      et_rec            RM$NLS_ELEMENT_TYPES%ROWTYPE;
   begin
      for et in ( select rtrim(translate(TXT_TEXT, chr(10), ' ')) TXT_TEXT
                  from   RM_TEXT_LINES
                  where  TXT_TYPE = 'CKNLET'
                  and    TXT_REF  = 0
                  order  by TXT_SEQ )
      loop
         stat_read              := stat_read + 1;
         l_short_name           := get_string(0,et.txt_text);
         l_name                 := get_string(1,et.txt_text);
         et_rec.nls_name        := get_string(2,et.txt_text);
         et_rec.nls_plural_name := get_string(3,et.txt_text);
         et_rec.nls_language    := lang;

-- lookup the id for this element type
         et_rec.etid            := get_et_id (l_short_name,l_name);

         if et_rec.etid < 1 then
            display_warning ( et_rec.etid, l_short_name, 'Element Type Name');
         else
            insert into RM$NLS_ELEMENT_TYPES
            ( NLS_LANGUAGE
            , ETID
            , NLS_NAME
            , NLS_PLURAL_NAME)
            values
            ( et_rec.nls_language
            , et_rec.etid
            , et_rec.nls_name
            , et_rec.nls_plural_name);

            stat_created := stat_created + 1;
         end if;
      end loop;

-- now directly create duplicate language row for UE element types

      insert into rm$nls_element_types
      (      nls_language
      ,      etid
      ,      nls_name
      ,      nls_plural_name  )
      select lang
      ,      r1.id
      ,      r1.name
      ,      r1.plural_name
      from   rm_element_types     r1
      ,      rm$nls_element_types r2
      where  substr(r1.user_extension,1,1) = 'Y'
      and    r2.etid         = r1.id
      and    r2.nls_language = 'US'
      and    not exists ( select 'x'
			  from   rm$nls_element_types r3
			  where  r3.etid         = r2.etid
			  and    r3.nls_language = lang );

      do_stats ('ELEMENT TYPE NAMES');
   end;

-- local procedure read_py will handle Property Types

   procedure read_py (lang in varchar2) is
      l_et_short_name   varchar2(255);
      l_name            varchar2(255);
      py_rec            RM$NLS_PROPERTIES%ROWTYPE;
   begin
      for py in ( select rtrim(translate(TXT_TEXT, chr(10), ' ')) TXT_TEXT
                  from   RM_TEXT_LINES
                  where  TXT_TYPE = 'CKNLPY'
                  and    TXT_REF  = 0
                  order  by TXT_SEQ )
      loop
         stat_read := stat_read + 1;

         l_et_short_name      := get_string(0,py.txt_text);
         l_name               := get_string(1,py.txt_text);
         py_rec.nls_name      := get_string(2,py.txt_text);
         py_rec.nls_default   := get_string(3,py.txt_text);
         py_rec.nls_hint      := get_string(4,py.txt_text);
         py_rec.nls_language  := lang;

-- lookup the id for property type
         py_rec.pid           := get_py_id (l_et_short_name, l_name);

         if py_rec.pid < 1 then
            display_warning ( py_rec.pid, l_name, 'Propery Name');
         else
            insert into RM$NLS_PROPERTIES
            ( NLS_LANGUAGE
            , PID
            , NLS_NAME
            , NLS_DEFAULT
            , NLS_HINT)
            values
            ( py_rec.nls_language
            , py_rec.pid
            , py_rec.nls_name
            , py_rec.nls_default
            , py_rec.nls_hint);

            stat_created := stat_created + 1;
         end if;
      end loop;

-- Now directly create language rows for UE properties

      insert into rm$nls_properties
      (      nls_language
      ,      pid
      ,      nls_name
      ,      nls_default
      ,      nls_hint      )
      select lang
      ,      py1.id
      ,      py1.name
      ,      py2.nls_default
      ,      py2.nls_hint
      from   rm_properties          py1
      ,      rm_property_extensions py2
      ,      rm$nls_properties      py3
      where  substr(py1.user_extension,1,1) = 'Y'
      and    py3.pid          = py1.id
      and    py2.for_property = py1.id
      and    py3.nls_language = 'US'
      and    not exists ( select 'x'
			  from   rm$nls_properties py4
			  where  py4.pid = py3.pid
			  and    py4.nls_language = lang );

      do_stats ('PROPERTY NAMES');
   end;

-- local procedure read_tt will handle Text Types

   procedure read_tt (lang in varchar2) is
      tt_rec        RM$NLS_TEXT_TYPES%ROWTYPE;
   begin
      for tt in ( select rtrim(translate(TXT_TEXT, chr(10), ' ')) TXT_TEXT
                  from   RM_TEXT_LINES
                  where  TXT_TYPE = 'CKNLTT'
                  and    TXT_REF  = 0
                  order  by TXT_SEQ )
      loop
         stat_read := stat_read + 1;

         tt_rec.nls_language := lang;
         tt_rec.text_type    := get_string(0,tt.txt_text);
         tt_rec.nls_desc     := get_string(1,tt.txt_text);

         insert into RM$NLS_TEXT_TYPES
         ( NLS_LANGUAGE
         , TEXT_TYPE
         , NLS_DESC)
         values
         ( tt_rec.nls_language
         , tt_rec.text_type
         , tt_rec.nls_desc);

         stat_created := stat_created + 1;
      end loop;

-- Now directly create language rows for UE Text types

      insert into rm$nls_text_types
      (      nls_language
      ,      text_type
      ,      nls_desc     )
      select lang
      ,      tt1.text_type
      ,      tt1.nls_desc
      from   rm_text_types     tt1
      ,      rm$nls_text_types tt2
      where  tt1.is_extended  = 'Y'
      and    tt2.nls_language = 'US'
      and    tt2.text_type    = tt1.text_type
      and    not exists ( select 'x'
			  from    rm$nls_text_types tt3
			  where   tt3.text_type = tt2.text_type
			  and     tt3.nls_language = lang );

      do_stats ('TEXT TYPE DESCRIPTIONS');
   end;

   begin
    if tab = 'DV' then
      dbms_output.put_line ('Log for load of NLS DATATYPE VALUES ...        ');
      dbms_output.put_line (' '                                              );
      read_dv (lang);
    elsif tab = 'ET' then
      dbms_output.put_line ('Log for load of NLS ELEMENT TYPE NAMES ...     ');
      dbms_output.put_line (' '                                              );
      read_et (lang);
    elsif tab = 'PY' then
      dbms_output.put_line ('Log for load of NLS PROPERTY NAMES ...         ');
      dbms_output.put_line (' '                                              );
      read_py (lang);
    elsif tab = 'TT' then
      dbms_output.put_line ('Log for load of NLS TEXT TYPES DESCRIPTIONS ...');
      dbms_output.put_line (' '                                              );
      read_tt (lang);
    end if;
   end;

end;
/
rem