-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
--	Name 		: ckmigtyp.sql
--	Description	: This script populates the jr_id_maps.type_id
--
--	Author		: Sukhy Bassi, Oracle Corporation UK
--	Date		: 30th November 1998
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Fixed US Bug 910075 Populate OWNERSHIP_FLAG TO 'Y' FOR APPSYN as they could never be shared   
-- Fixed US Bug 912115 FOLDER MEMBERS NOT CREATED FOR REUSABLE LIST OF VALUES
-- Work around for ST Bug 1481696 

DECLARE

BEGIN

  dbms_output.enable(1000000);


-- **************************************************************************
--
--	
--
-- **************************************************************************


  update ck_migration_objects set ci_created = 'N' where ci_stage = 'CKMIGTYP';
 

  -- For all ROWS in JR_ID_MAPS where POP_TAB = SDD_ELEMENTS
  -- Set element_type = occur_type except where element type in:
  -- APPLICATION_SYSTEM               
  -- COLUMN                           
  -- DATASTORE                      
  -- DATASTRUCTURE                  
  -- DATASTRUCTURE_ITEM             
  -- DIAGRAM                         
  -- EVENT                         
  -- FUNCTION   
  -- KEY_COMPONENT 
  -- MODULE_COMPONENT 
  -- PROGRAM_DATUM   
  -- WEIGHTING_FACTOR 

  dbms_output.put_line('* Setting JR_ID_MAPS.ELEMENT_TYPE for SDD_ELEMENTS');

  update jr_id_maps
  set element_type = decode(occur_type,'TABLE'  , 'TBL'
                                      ,'VIEW'   , 'VW'
                                      ,'SNAP'   , 'SNP'
                                      ,'RECORD' , 'REC'
                                      ,'PRIMARY', 'PCO'
				      ,'CHECK'  , 'CCO'
				      ,'UNIQUE' , 'UCO'
				      ,'FOREIGN', 'FCO'
                                                , occur_type )  
  where element_type not in ('APP','COL','DST','DAT','ITM','DIA','EVT','FUN','OCL','OKY','MCO','PGD','MWF', 'ORT')
  and   pop_tab   = 'SDD_ELEMENTS'
  and   occur_type is not null;

  -- General sets are now User Defined Sets
  dbms_output.put_line('* Setting JR_ID_MAPS.ELEMENT_TYPE for General Sets');
  
  update jr_id_maps
  set element_type = 'UDS'
  where element_type = 'GST'
  and pop_tab = 'SDD_ELEMENTS';

  -- Set flag IS_ODD for ODD element types  
  dbms_output.put_line('* Setting JR_ID_MAPS.IS_ODD for ODD elements');
  
  update jr_id_maps
  set is_odd = 'Y'
  where element_type in ('OBT', 'VAT', 'ATP', 'UTC', 'OTC', 'ATC', 'ARL', 'TAT', 'TOP', 'GCS', 'IVA', 'OPP',
                         'VVA', 'TOPABT', 'ABTABT', 'TCOTME');
						 
  -- Set flag IS_ODD for ODD Transformation Mapping Set
  dbms_output.put_line('* Setting JR_ID_MAPS.IS_ODD for ODD Transformation Mapping Sets');
  
-- Work around for ST Bug 1481696 because not in fails on 8.1.7
-- Split into two different update statements.

  update jr_id_maps 
      set is_odd = 'Y' 
      where pop_tab = 'SDD_ELEMENTS' 
    and element_type = 'TMS';
   
    
    update jr_id_maps 
    set is_odd = 'N' 
    where pop_tab = 'SDD_ELEMENTS' 
    and element_type = 'TMS' 
    and old_id in ( select id 
              	      from ci_transformation_mapping_sets@r212 
              	      where source_level = 'DBRELN' 
            	      and target_level = 'DBOBJ' );					
					  
				  
  dbms_output.put_line('* Setting JR_ID_MAPS.ELEMENT_TYPE for APP_SYS_<elements>');
    
  update jr_id_maps a
  set    a.element_type = 'APPXXX'
  where  a.element_type like 'APP%'
  and    a.pop_tab = 'SDD_STRUCTURE_ELEMENTS';		
  
  
  dbms_output.put_line('* Setting JR_ID_MAPS.IS_SYS_LAN for system LANGUAGES');
  
  update jr_id_maps a
  set a.is_sys_lan = 'Y'
  where exists ( SELECT 1
			   FROM cdi_language_definitions lan
				 WHERE lan.lan_name = a.lan_name )
  and a.element_type = 'LAN'
  and POP_TAB = 'SDD_ELEMENTS'
  and not exists ( select 1
				   from ci_weighting_factors@r212 mwf
		   where a.old_id = mwf.language_reference )
  and not exists ( select language_type,
				  source_directory,				
				  runtime_directory,
			  command_line 			                
			   from upg_ci_lan lan3	
		   where lan3.id = a.old_id		
				   minus
			   select lan_type,
				  lan_source_directory,
				  lan_runtime_directory,
				  lan_command_line
			   from cdi_language_definitions@r212 def_lan
			   where decode(def_lan.lan_internal_name,'Forms','Oracle Forms','Web','Web PL/SQL',
				 'Reports', 'Oracle Reports','D2KLIB','Oracle Common Library',
				def_lan.lan_name) = a.lan_name
				)
  and not exists ( select 1
				   from upg_ci_lan lan
				   where lan.id = a.old_id
				   and exists ( select 1
								from upg_rm_text_lines
								where txt_ref = lan.id )
				 );


  dbms_output.put_line('* Setting JR_ID_MAPS.IRID, IVID for system LANGUAGES');

  update jr_id_maps a
  set (a.irid, a.ivid) = ( SELECT lan.lan_irid, lan.lan_ivid
  						   FROM cdi_language_definitions lan
						   WHERE lan.lan_name = a.lan_name )
  where a.element_type = 'LAN'
  and  a.is_sys_lan = 'Y'
  and  a.POP_TAB = 'SDD_ELEMENTS';			     
				  
				  

  -- Synonym has become a PAC element so we need to create an APPXXX for them
  -- B912115 FOLDER MEMBERS NOT CREATED FOR REUSABLE LIST OF VALUES  
  dbms_output.put_line('* Creating APPXXX''s for Synonyms');
  
  insert into jr_id_maps ( OLD_ID                 
			 , IRID                   
			 , IVID                   
			 , ELEMENT_TYPE           
			 , IS_A_PAC_ELEMENT       
			 , OWNING_APP             
			 , OWNING_APP_ID          
			 , TYPE_ID  
                         , PART_OF_ID              
			 , USE_OF_ID              
			 , POP_TAB)
  select 		 0
			 , jr_util.get_new_irid
			 , jr_system_util.get_new_ivid
			 , 'APPXXX'
			 ,  NULL
			 ,  'Y'
			 ,  OWNING_APP_ID
			 ,  NULL
                         ,  PART_OF_ID
			 ,  old_id
			 ,  'SDD_STRUCTURE_ELEMENTS'
  from 			 jr_id_maps 
  where 		 element_type in ('SYN','RLV');
    

  -- B1234984 - NEED TO ENFORCE THAT EACH PAC ELEMENT MIGRATED HAS A VALID CONTAINER
  -- Create folder members for PAC elements that do not have an APPXXX entry in JR_ID_MAPS
  dbms_output.put_line('* Creating missing APPXXX''s for PAC elements');
  
  insert into jr_id_maps ( OLD_ID                 
						 , IRID                   
						 , IVID                   
						 , ELEMENT_TYPE           
						 , IS_A_PAC_ELEMENT       
						 , OWNING_APP             
						 , OWNING_APP_ID          
						 , TYPE_ID  
						 , PART_OF_ID              
						 , USE_OF_ID              
						 , POP_TAB)
				select 	   0
						 , jr_util.get_new_irid
						 , jr_system_util.get_new_ivid
						 , 'APPXXX'
						 ,  NULL
						 ,  'Y'
						 ,  sdd.OWNING_APP_ID
						 ,  NULL
						 ,  OWNING_APP_ID
						 ,  sdd.old_id
						 ,  'SDD_STRUCTURE_ELEMENTS'
				from 	jr_id_maps sdd
			    where     POP_TAB = 'SDD_ELEMENTS'
	  		    AND sdd.ELEMENT_TYPE IN ('ORT','COM','PLM','GEM','SFI','RFI','DOM','DST','BUN','FUN','OSQ',
										 'OSP','ENT','OCL','TBL','VW','SNP','REC','DOC','LAN',
			       				         'E1','E2','E3','E4','E5','E6','E20','EXT','PRS','ANS','ORC','RMC')
	  		    AND sdd.IS_SYS_LAN = 'N'
	  		    AND sdd.OLD_ID NOT IN ( select map2.use_of_id
	  			                        from jr_id_maps map2
						   			    where pop_tab = 'SDD_STRUCTURE_ELEMENTS'
									    and element_type = 'APPXXX'	
									    and map2.part_of_id = sdd.owning_app_id );
  
  
  -- B1265432 - APPXXX ELEMENTS NEED TO BE AUTOGENERATED FOR PAC UE ELEMENTS
  -- Create folder members for UE PAC elements that do not have an APPXXX entry in JR_ID_MAPS  
  dbms_output.put_line('* Creating missing APPXXX''s for User Extended PAC elements');
  
  insert into jr_id_maps ( OLD_ID                 
  						 , IRID                   
  						 , IVID                   
  						 , ELEMENT_TYPE           
  						 , IS_A_PAC_ELEMENT       
  						 , OWNING_APP             
  						 , OWNING_APP_ID          
  						 , TYPE_ID  
  						 , PART_OF_ID              
  						 , USE_OF_ID              
  						 , POP_TAB)
  				select 	   0
  						 , jr_util.get_new_irid
  						 , jr_system_util.get_new_ivid
  						 , 'APPXXX'
  						 ,  NULL
  						 ,  'Y'
  						 ,  sdd.OWNING_APP_ID
  						 ,  NULL
  						 ,  OWNING_APP_ID
  						 ,  sdd.old_id
  						 ,  'SDD_STRUCTURE_ELEMENTS'
  				from 	jr_id_maps sdd
			    where     POP_TAB = 'SDD_ELEMENTS'
   				AND sdd.ELEMENT_TYPE IN ( select short_name
                    		              from ciue_element_types
                    		              where is_user_type = 'Y' )
  	  			AND sdd.IS_SYS_LAN = 'N'
  	  			AND sdd.OLD_ID NOT IN ( select map2.use_of_id
  	                			        from jr_id_maps map2
					  		   			where pop_tab = 'SDD_STRUCTURE_ELEMENTS'
  										and element_type = 'APPXXX'	
										and map2.part_of_id = sdd.owning_app_id );  
 
  

  -- PLSQL Modules of PLSQL_MODULE_TYPE = TYPE BODY nolonger require APPXXX for them so we should remove them here
  dbms_output.put_line('* Removing obsolete APPMOD''s for PLSQL_MODULE_TYPE = TYPE_BODY');

  delete from JR_ID_MAPS a
  where  a.element_type = 'APPXXX'
  and    POP_TAB = 'SDD_STRUCTURE_ELEMENTS'
  and    exists (select 1
                 from   ci_plsql_modules@r212 b
                 where  b.plsql_module_type = 'TYPE BODY'
                 and    b.id = a.use_of_id);				   


  dbms_output.put_line('* Removing redundant APPLAN''s for system LANGUAGES');

  delete from JR_ID_MAPS a
  where  a.element_type = 'APPXXX'
  and    a.POP_TAB = 'SDD_STRUCTURE_ELEMENTS'
  and    exists (select 1
                 from   jr_id_maps b
                 where  b.old_id = a.use_of_id
		     and    b.is_sys_lan = 'Y'
		     and    b.element_type = 'LAN'
		     and    b.POP_TAB = 'SDD_ELEMENTS');


  dbms_output.put_line('* Moving text type CDISPC to PLSDEC where PLSQL_MODULE_TYPE != PACKAGE ');
 
  update upg_rm_text_lines
  set txt_type = 'PLSDEC'
  where txt_type = 'CDISPC'
  and txt_ref in ( select plm.id
                   from ci_plsql_modules@r212 plm
                   where plm.plsql_module_type != 'PACKAGE' );


  dbms_output.put_line('* Splitting CDIPLS to CDIPLS and PLSDEC where PLSQL_MODULE_TYPE = PACKAGE ');
  --
  --  BUG 1470283 UNABLE TO GENERATE MIGRATED PACKAGE DUE TO PROPERTY PL/SQL BLOCK
  --
  --  NOTE: This code parses the multi-line text of type CDIPLS for all 
  --        modules of type package.  Like all parsing code it makes 
  --        assumptions about what it is likely to receive as text. So
  --        there is the possibility that it may not work for everyones
  --        data.  (Although I believe it does cater for all cases)
  --        There are test files ckrau/unittest/cnv/bug1470283_data.sql
  --        and ckrau/unittest/cnv/bug1470283_qry.sql
  --        These should be used to setup test data and query the results 
  --        of the transformation this code performs.  Please maintain 
  --        the data file if any bugs are found in this code.
  --
  --  INEALL August 3rd 2001
  --     
  declare
    cursor c_packages is
      select plm.id id
      from ci_plsql_modules@r212 plm
      where plm.plsql_module_type = 'PACKAGE' 
    ; 

    --
    --  debug variables only
    --
    l_debug constant boolean := false;
    l_step  integer := 0;
    l_stop  constant integer := 500;
    l_break constant integer := 25;

    --
    --  text lines are stored with CHR(13)||CHR10) delimiting lines.  
    --  Sometimes several lines will appear in one row of the table.
    --  Therefore this code must unravel this and still find the keyword
    --  BEGIN.
    --  
    procedure process_mod( p_id in integer)
    is
      cursor c_lines is 
        select tl.txt_text  txt_text
        ,      tl.txt_seq   txt_seq
        ,      tl.txt_ref   txt_ref
        from   upg_rm_text_lines tl
        where  tl.txt_ref = p_id
        and    tl.txt_type = 'CDIPLS'
        order by tl.txt_seq asc
      ;
      --
      --  The buffer can contain upto two rows from the text table
      --  When we have parsed past the first row we adjust the buffer
      --  to throw away the first row.  This avoids nibbling the
      --  buffer away which would incurr a hugh amount of memory
      --  allocation/dealloc.
      --
      l_null constant  varchar2(10) := chr(0);
      l_buffer         varchar2(4000);   -- part of MLT being examined
      l_eol  constant  varchar2(2)  := chr(13) || chr(10);
      l_ws   constant  varchar2(10) := l_null ||l_eol || chr(9) || ' ';

      l_row_seq        integer := null;  -- the seq value for last row read
      l_prev_row_seq   integer := null;  -- the seq value for the previous row 
                                         -- to the last one read
      l_curr_pos       integer := 1;     -- the position in the buffer of the
                                         -- current character being examined
      l_prev_row_lc    integer := 0;     -- the position in the buffer of the  
                                         -- last char from the previous row.
      procedure debug(p_header boolean)
      is
        function f(p1 varchar2, p2 integer) return varchar2 is
        begin if p1 is null then return lpad(' ',p2); end if;return lpad(p1,p2);
        end;
      begin
        if not l_debug then return; end if;
        if p_header then
          dbms_output.put_line
          (' l_row_seq l_prev_row_seq l_curr_pos l_prev_row_lc l_buffer(50c)' );
        else    
          dbms_output.put_line
          (  ' '||f(l_row_seq,9)
          || ' '||f(l_prev_row_seq,14)
          || ' '||f(l_curr_pos,10)
          || ' '||f(l_prev_row_lc,13)
          || ' '||replace(replace(substr(l_buffer,l_curr_pos,50),chr(10),'\10'),chr(13),'\13')
          );
        end if;
      end;
      
      --
      --  when we find the keyword BEGIN update the text rows, including
      --  splitting a row into two.
      --
      procedure split
      is
        ll_update_text varchar2(2000);
        ll_update_seq  integer;
      begin
        if l_prev_row_seq is null then
          ll_update_seq := l_row_seq;
        else
          ll_update_seq := l_prev_row_seq;
        end if;
        --
        --  Update all the rows that occured before the row that 
        --  contained the BEGIN as they are private declarations
        --
        update upg_rm_text_lines tl
        set    tl.txt_type = 'PLSDEC'
        where  tl.txt_ref  = p_id
        and    tl.txt_type = 'CDIPLS'
        and    tl.txt_seq  < ll_update_seq
        ;
        --
        --  if BEGIN didn't occur at the start of a ROW then plit the row
        --
        if l_curr_pos!= 1 then
          --
          --  update the row so that everything to the left of the BEGIN
          --  is kept but changed type.
          --
          update upg_rm_text_lines tl
          set    tl.txt_text = substr(tl.txt_text,1, l_curr_pos-1)
          ,      tl.txt_type = 'PLSDEC'
          where  tl.txt_ref = p_id
          and    tl.txt_type = 'CDIPLS'
          and    tl.txt_seq = ll_update_seq;
          
          --  example buffer;
          --  BEGIN
          --    ^l_prev_row_lc = 3
          if ( l_curr_pos < l_prev_row_lc ) then
            ll_update_text := substr(l_buffer,l_curr_pos,l_prev_row_lc - l_curr_pos + 1);
          else
            ll_update_text := substr(l_buffer,l_curr_pos);
          end if;
          
          insert into upg_rm_text_lines
          (        txt_ref, txt_seq,       txt_type, txt_text       )
          values ( p_id,    ll_update_seq, 'CDIPLS', ll_update_text )
          ;
        end if;
      end split;
      
      --
      --  fetch a row and add text to the buffer we are parsing
      --
      function buffer_add_row return boolean
      is
        ll_text      varchar2(2000);
        ll_row_seq   integer;
        ll_text_ref  number;
      begin
        --
        --  ignore rows that have no text
        --
        loop
          fetch c_lines into ll_text, ll_row_seq, ll_text_ref;
          if c_lines%notfound then
            return false;
          end if;
          if ll_text is not null then 
            --
            --  if the end of the buffer plus
            --  this line is still not 6 chars long then
            --  append this line to the one we are working on
            --  to emulate the line being longer
            --
            if  l_row_seq is not null 
            and length(l_buffer)-l_curr_pos + length(ll_text) < 5 then
              l_buffer := l_buffer || ll_text;
              --
              -- Now need to reflect this faking the current working line 
              -- containg this short line by updating database
              --
              update upg_rm_text_lines tl
              set    tl.txt_text= tl.txt_text || ll_text
              where  tl.txt_ref  = p_id
              and    tl.txt_type = 'CDIPLS'
              and    tl.txt_seq  = l_row_seq
              ;
              delete from upg_rm_text_lines tl
              where  tl.txt_ref  = p_id
              and    tl.txt_type = 'CDIPLS'
              and    tl.txt_seq  = ll_row_seq
              ;
            else
              exit;
            end if;
          end if;
        end loop;
        --
        --  adjust counters
        --
        l_prev_row_seq := l_row_seq;
        l_prev_row_lc  := length(l_buffer);
        --
        --  fix irritating null return from length of null string
        --
        if l_prev_row_lc is null then 
          l_prev_row_lc := 0; 
        end if;
        l_row_seq      := ll_row_seq;
        l_buffer       := l_buffer || ll_text;
        return true;
      end buffer_add_row;

      --
      --  logically trim leading characters from the buffer.
      --  example buffer;
      --  abcd efg BEGIN
      --             ^l_prev_row_lc = 12
      --  ie the IN from begin is at the start of row l_row_seq
      --  if asked to trim less than 12 then the buffer still
      --  spans two rows.  If 12 or more then the buffer only has 
      --  text from one row.
      --
      procedure buffer_trim(p_chars in integer) 
      is
        l_trim_pos integer;
      begin
        if p_chars < 0 then
          --
          --  trim from RH end!
          --
          l_trim_pos := length(l_buffer)-1;
        else
          l_trim_pos := l_curr_pos + p_chars;
        end if;
        if  l_prev_row_lc > 0 
        and l_trim_pos > l_prev_row_lc 
        then
          --
          --  leaves only one row in buffer, throw away previous row
          --
          l_buffer       := substr(l_buffer,l_prev_row_lc+1);
          l_prev_row_seq := null;
          l_curr_pos     := l_trim_pos - l_prev_row_lc;
          l_prev_row_lc  := 0; 
        else
          --
          --  just move the current pointer up the buffer
          --
          l_curr_pos     := l_curr_pos + p_chars;
        end if;
      end buffer_trim;
      
    begin 
      declare
        l_pos      integer;           -- temp var to hold location of char in buf
        l_nc       varchar2(2);       -- next character to examine
        l_more     boolean := false;  -- any more rows to read
        l_in_mlc   boolean := false;  -- in MultiLine comment
        l_in_slc   boolean := false;  -- in Single Line comment
        l_at_te    boolean := true;   -- have we just reached the end of a token
        
        procedure debugi(loc in varchar2) is
          function b (p1 in boolean, p2 integer) return varchar2 
          is begin if p1 then return rpad('Y',p2); end if; return rpad('N',p2); 
          end;
        begin
          if not l_debug then return; end if;
          if mod(l_step,l_break) = 0 then
            --
            --  output headers
            --
            dbms_output.put( 'Loctn  STEP MLC SLC l_at_te' );
            debug(true);
          end if;
          dbms_output.put
          ( rpad(loc,5) 
          ||' '|| lpad(l_step,5)
          ||' '|| b(l_in_mlc,3)
          ||' '|| b(l_in_slc,3)
          ||' '|| b(l_at_te,7)
          );
          debug(false);
          l_step := l_step + 1;
          if l_step >= l_stop then
            raise no_data_found;
          end if;
        end;
        
      begin
        open c_lines;
        l_more := buffer_add_row;
        while l_more loop
          <<start_parse>>
          <<find_something>>
          debugi('FS');
          --
          --  if the buffer is empty then read some more
          --
          if length(l_buffer) < l_curr_pos then
            goto continue;
          end if;
  
          --
          --  process the buffer content
          --
          if l_in_mlc then
            --
            --  just throw away until we find the end
            --
            if length(l_buffer)-l_curr_pos < 2 then goto continue; end if;
            l_pos := instr(l_buffer, '*/', l_curr_pos);
            if l_pos = 0 then 
              --
              --  trim the buffer to leave the last character just in case 
              --  */ was split over two rows
              --
              buffer_trim(-1);
              debugi('MLC');
              goto continue;
            else
              buffer_trim(l_pos+2-l_curr_pos);
              l_in_mlc := false;
            end if;
          end if;
          if l_in_slc then
            --
            --  just throw away until we find the end
            --
            if length(l_buffer)-l_curr_pos < 2 then goto continue; end if;
            l_pos := instr(l_buffer, l_eol, l_curr_pos);
            if l_pos = 0 then 
              --
              --  trim the buffer to leave the last character just in case 
              --  chr(13)chr(10) was split over two rows
              --
              buffer_trim(-1);
              debugi('SLC');
              goto continue;
            else
              buffer_trim(l_pos+2-l_curr_pos);
              l_in_slc := false;
            end if;
          end if;
          
          --
          --  Throw away white space
          --
          l_nc := substr(l_buffer,l_curr_pos,1);
          if translate(l_nc,l_ws ,l_null) is null then
            buffer_trim(1);
            l_at_te := true;
            debugi('WS');
            goto find_something;
          end if;
  
          --
          --  semicolon also marks the end of a token
          --
          if l_nc = ';' then        
            buffer_trim(1);
            l_at_te := true;
            debugi(';;');
            goto find_something;
          end if;
          
          --
          --  always check we have enough characters for a token
          --
          if length(l_buffer)-l_curr_pos < 2 then goto continue; end if;
          if substr( l_buffer, l_curr_pos,2) = '/*' then
            l_in_mlc := true;
            l_at_te := true;
            goto start_parse;
          end if;
          if substr( l_buffer, l_curr_pos,2) = '--' then
            l_in_slc := true;
            l_at_te := true;
            goto start_parse;
          end if;
          
          --
          --  Check for 5 characters plus a terminator.  Its possible that
          --  BEGIN could be the last word in the last row in which case
          --  we trap this outside the main loop.
          --
          if length(l_buffer)-l_curr_pos < 6 then goto continue; end if;
          -- 
          --  if we at a token end then look for the word BEGIN
          --  
          if l_at_te then
            if upper(substr( l_buffer, l_curr_pos,5)) = 'BEGIN' then
              --
              --  check that the next character is not a valid character in an
              --  identifier
              --
              l_nc := substr(l_buffer,l_curr_pos+5,1);
              if translate(l_nc,l_ws || '/-',l_null) is null then
                --
                --  found the token
                --
                split;
                l_buffer := null;
                exit;
              end if;
            end if;
          end if;
  
          --
          --  in mid token
          --
          l_at_te := false;
          buffer_trim(1);
          goto find_something;
                
          <<continue>>
          debugi('CONT');
          --
          --  get the next line of the text
          --
          l_more := buffer_add_row;
          
        end loop;

        --
        --  check for a trailing token
        --
        if l_buffer is not null
        and upper(substr(l_buffer,l_curr_pos)) = 'BEGIN' 
        then
          split;
        elsif not l_more then
          --
          --  Never found BEGIN so change all rows to declaration
          --
          
          --
          --  Adding 1 makes split occur after the last row read.
          --
          l_row_seq     := l_row_seq + 1;
          --
          --  Make sure there is no single row to split.
          --
          l_curr_pos    := 1;
          l_prev_row_lc := 0;
          split;
        end if;
          
        close c_lines;
      end; -- block for truely local vars
    end process_mod;
    
  begin
    for l_mod in c_packages loop
      process_mod(l_mod.id); 
    end loop;
  end; 

  dbms_output.put_line('* Setting access rights for Frozen Application Systems');
  update upg_sdw_access_rights
  set ar_del_allw = 'N', ar_ins_allw ='N', ar_upd_allw ='N'
  where ar_appid_for in ( select old_id
                          from jr_id_maps
                          where element_type = 'APP'
				  and pop_tab = 'SDD_ELEMENTS'
				  and is_a_pac_element = 'F' );

  -- BUG 1383589 MIGRATION OF 6.0 --> 6I STOPS WITH ORA-1400 IN CKMIGDAT
  -- Need to remove any MLT stored against CNU's as model does not allow
  delete from upg_rm_text_lines
  where txt_ref in ( select old_id
                     from jr_id_maps
                     where element_type = 'CNU'
                     and pop_tab = 'SDD_ELEMENTS');      


  update ck_migration_objects set ci_created = 'Y' where ci_stage = 'CKMIGTYP';     

  COMMIT;
    
EXCEPTION 
  when others then 
     dbms_output.put_line (' ** Exception Raised ** ');
     raise;
END;
/


