Rem    NAME 
Rem      ckupgalt.sql
Rem    DESCRIPTION
Rem      Alters Upg_<tables> used for Specific PreUpgrades
Rem    RETURNS
Rem 	 None
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem    CREATED
Rem	  12/04/1999
Rem
Rem    UPDATED
Rem     26/07/1999 VDESAI - B94622 - NEED TO REMOVE DEF VALUE FOR ALIGN_PROMPT_FLAG ON CI_ITEMS IN STAGE 'CKUPGALT'

DECLARE

   procedure add ( l_table IN varchar2
                 , l_add   IN varchar2 ) is
      csr               NUMBER;
      col_exist         EXCEPTION;
      tab_exist         EXCEPTION;
      PRAGMA EXCEPTION_INIT(col_exist, -01430);
      PRAGMA EXCEPTION_INIT(tab_exist, -00942);  
   begin
      csr := DBMS_SQL.OPEN_CURSOR;
      dbms_output.put_line('Adding    '||l_table||'.'||l_add);
      DBMS_SQL.PARSE(csr,'alter table '||l_table||' add ( '||l_add||' )',DBMS_SQL.V7);
      DBMS_SQL.CLOSE_CURSOR(csr);
   exception
      when col_exist then
         NULL;
         DBMS_SQL.CLOSE_CURSOR(csr);
      when tab_exist then
         NULL;
         DBMS_SQL.CLOSE_CURSOR(csr);
      when others then 
	 DBMS_OUTPUT.PUT_LINE (substrb(sqlerrm, 1, 200));
         DBMS_SQL.CLOSE_CURSOR(csr); 
         RAISE;
   end;

   procedure modify ( l_table IN varchar2
                 ,    l_mod   IN varchar2 ) is
      csr               NUMBER;
       tab_exist         EXCEPTION;
      PRAGMA EXCEPTION_INIT(tab_exist, -00942);  
   begin
      csr := DBMS_SQL.OPEN_CURSOR;
      dbms_output.put_line('Modifying    '||l_table||'.'||l_mod);
      DBMS_SQL.PARSE(csr,'alter table '||l_table||' modify ( '||l_mod||' )',DBMS_SQL.V7);
      DBMS_SQL.CLOSE_CURSOR(csr);
   exception
      when tab_exist then
         NULL;
         DBMS_SQL.CLOSE_CURSOR(csr);
      when others then 
	 DBMS_OUTPUT.PUT_LINE (substrb(sqlerrm, 1, 200));
         DBMS_SQL.CLOSE_CURSOR(csr); 
         RAISE;
   end;

BEGIN

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


   -- UPG_CI_ITEMS

   add   ( 'UPG_CI_ITEMS','ALIGN_PROMPT_FLAG  VARCHAR2(1)');
   add   ( 'UPG_CI_ITEMS','DEFAULT_VALUE_TYPE VARCHAR2(15)');
   add   ( 'UPG_CI_ITEMS','END_TAB            NUMBER(10, 0)');
   add   ( 'UPG_CI_ITEMS','START_TAB          NUMBER(10, 0)');

   modify( 'UPG_CI_ITEMS','element_type_name DEFAULT ''ITE''');
   modify( 'UPG_CI_ITEMS','insert_flag DEFAULT       ''N''');
   modify( 'UPG_CI_ITEMS','update_flag DEFAULT       ''N''');
   modify( 'UPG_CI_ITEMS','select_flag DEFAULT       ''N''');
   modify( 'UPG_CI_ITEMS','range_search_flag DEFAULT ''D''');
   modify( 'UPG_CI_ITEMS','truncate_flag DEFAULT     ''W''');
   modify( 'UPG_CI_ITEMS','context_flag DEFAULT      ''N''');
   modify( 'UPG_CI_ITEMS','nullify_flag DEFAULT      ''N''');

   -- UPG_CI_IGR

   add   ( 'UPG_CI_IGR','ORIENTATION          VARCHAR2(10)');
   add   ( 'UPG_CI_IGR','TAB_SCOPE            VARCHAR2(6)');
   add   ( 'UPG_CI_IGR','START_TAB            NUMBER(10)');
   add   ( 'UPG_CI_IGR','END_TAB              NUMBER(10)');
   add   ( 'UPG_CI_IGR','ITEM_GROUP_FOR       VARCHAR2(3)');
   add   ( 'UPG_CI_IGR','SUBCOMPONENT_REF     NUMBER(38)');

   modify( 'UPG_CI_IGR','tab_scope DEFAULT      ''NONE''');
   modify( 'UPG_CI_IGR','item_group_for DEFAULT ''MCO''');
   modify( 'UPG_CI_IGR','ORIENTATION    DEFAULT ''VERTICAL''');

   -- UPG_LVI
   modify( 'UPG_LVI','RESTRICTION_CLAUSE DEFAULT ''DEFAULT''');

   -- UPG_CI_MDTU
   modify( 'UPG_CI_MDTU','ELEMENT_TYPE_NAME DEFAULT ''MTI''');

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

   commit;

EXCEPTION 
   when others then
   dbms_output.put_line (' ** Exception Raised ** ');
   dbms_output.put_line ('...ERROR: '||substrb(sqlerrm, 1, 200));
   raise;

END;
/
