rem 
rem    Author 	   : VIPS DESAI
rem    Fiilename   : ckrptar.sql
rem    Title    	   : ARCHIVE CK REPOSITORY REPORTS TABLES TO XT_CK.... TABLES  
rem    Date   	   : SEPTEMBER 1996
rem    Commants  : New in Designer 2000 Release 2.0
rem
rem
rem
rem   Purpose      : This script is used to copy user defined meta data to temporary archive 		     
rem 		     The script is invoked within the Repository Reports Interface when 
rem                        extracting user meta data and is called by the RAU on upgrade
rem		     or when the user re-installs the repository user data
rem
rem  XT Tables    :  XT_CK_REPORTS
rem 		    XT_CK_NLS_REPORTS
rem	                XT_CK_REPORT_GROUPS
rem 		    XT_CK_NLS_REPORT_GROUPS
rem		    XT_CK_REPORT_PARAMETERS
rem		    XT_CK_NLS_PARAMETERS
rem 		    XT_CK_REPORT_GROUP_USAGES
rem 		    XT_CK_REPORT_PARAMETER_USAGES
rem 
rem  Procedures :  CKRPT_DRP_TABLE     - drop table
rem 		    CKRPT_COPY_TABLE  - copy table, copying rem only user defined data 
rem 		    CKRPT_ARCHIVE         - archive data from CK Repository Reports tables

DECLARE

  procedure CKRPT_DRP_TABLE (l_table IN varchar2) is
    csr NUMBER;
    table_does_not_exist EXCEPTION;
    PRAGMA EXCEPTION_INIT(table_does_not_exist, -00942);

   begin
     csr := DBMS_SQL.OPEN_CURSOR;
     DBMS_OUTPUT.PUT_LINE('Dropping table '||l_table);
     DBMS_SQL.PARSE(csr,'drop table '||l_table, DBMS_SQL.V7);
     DBMS_SQL.CLOSE_CURSOR(csr);
  exception
    when table_does_not_exist then
      DBMS_OUTPUT.PUT_LINE(l_table || ' ... table does not exist');
      DBMS_SQL.CLOSE_CURSOR(csr);
  when others then
     DBMS_OUTPUT.PUT_LINE (sqlerrm);
     DBMS_SQL.CLOSE_CURSOR(csr); 

end CKRPT_DRP_TABLE;

procedure CKRPT_COPY_TABLE (l_source_table IN varchar2) is
    csr NUMBER;
    table_exists         EXCEPTION;
    table_does_not_exist EXCEPTION;
    PRAGMA EXCEPTION_INIT(table_exists, -00955);
    PRAGMA EXCEPTION_INIT(table_does_not_exist, -00942);
  begin
    ckrpt_drp_table('XT_' || l_source_table);
    csr := DBMS_SQL.OPEN_CURSOR;
    DBMS_OUTPUT.PUT_LINE('Copying table from '||l_source_table||' to '|| 'XT_' || l_source_table);
    DBMS_SQL.PARSE(csr,'create table XT_' || l_source_table ||
			' as ( select * from ' || l_source_table|| '  WHERE CK_USER_DEFINED = ''Y'' ) ',DBMS_SQL.V7);
    DBMS_SQL.CLOSE_CURSOR(csr);
  exception
    when table_exists then
       DBMS_OUTPUT.PUT_LINE('... destination table XT_' || l_source_table||' already exists');
       DBMS_SQL.CLOSE_CURSOR(csr);
    when table_does_not_exist then
       DBMS_OUTPUT.PUT_LINE('... source table' || l_source_table ||' does not exists');
       DBMS_SQL.CLOSE_CURSOR(csr);
    when others then
      DBMS_OUTPUT.PUT_LINE (sqlerrm);
      DBMS_SQL.CLOSE_CURSOR(csr);
  
  end CKRPT_COPY_TABLE;


procedure CKRPT_ARCHIVE is
 
  begin
     dbms_output.enable(8000); 

    DBMS_OUTPUT.PUT_LINE('Archiving from CK Repository Reports tables to XT_CK....tables');
    
     ckrpt_copy_table('CK_REPORTS');
    ckrpt_copy_table('CK_NLS_REPORTS');
    ckrpt_copy_table('CK_REPORT_PARAMETERS');	
    ckrpt_copy_table('CK_NLS_PARAMETERS');
    ckrpt_copy_table('CK_REPORT_GROUPS');
    ckrpt_copy_table('CK_NLS_REPORT_GROUPS');
    ckrpt_copy_table('CK_REPORT_GROUP_USAGES');
    ckrpt_copy_table('CK_REPORT_PARAMETER_USAGES');
    commit;

end CKRPT_ARCHIVE;


begin
  ckrpt_archive;
end;
/
