PACKAGE em_management IS

   TYPE t_err_msg IS TABLE OF error_messages_1%ROWTYPE;
   PROCEDURE update_emconfig;
   PROCEDURE flip_em_synonym;
   PROCEDURE archive_log(v_timestamp IN DATE
                        ,v_source    IN VARCHAR2
                        ,v_message   IN VARCHAR2
                        ,v_severity  IN VARCHAR2);

END em_management;
PACKAGE BODY em_management IS
   pk_name VARCHAR2(30) := 'em_management';
   e_data_failure EXCEPTION;

   PROCEDURE update_emconfig IS
      min_em_1  DATE;
      min_em_2  DATE;
      max_em_1  DATE;
      max_em_2  DATE;
      proc_name VARCHAR2(30) := '.update_emconfig';
   BEGIN
      -- Update start and end dates of e_m_1 and e_m_2 in em_config_data
      SELECT trunc(MIN(TIMESTAMP))
      INTO   min_em_1
      FROM   error_messages_1;
      SELECT trunc(MIN(TIMESTAMP))
      INTO   min_em_2
      FROM   error_messages_2;
      SELECT trunc(MAX(TIMESTAMP))
      INTO   max_em_1
      FROM   error_messages_1;
      SELECT trunc(MAX(TIMESTAMP))
      INTO   max_em_2
      FROM   error_messages_2;

      max_em_1 := max_em_1 + 1;
      max_em_2 := max_em_2 + 1;


      UPDATE em_config_data
      SET    em_start_date = nvl(min_em_1
                                ,SYSDATE)
            ,em_end_date   = nvl(max_em_1
                                ,SYSDATE + 1)
      WHERE  em_table_name =
             'ERROR_MESSAGES_1';

      UPDATE em_config_data
      SET    em_start_date = nvl(min_em_2
                                ,SYSDATE)
            ,em_end_date   = nvl(max_em_2
                                ,SYSDATE + 1)
      WHERE  em_table_name =
             'ERROR_MESSAGES_2';

      COMMIT;
   EXCEPTION
      WHEN OTHERS THEN
         dbfl_error.raise_error('W'
                               ,'Update em config failed ' ||
                                SQLERRM
                               ,pk_name ||
                                proc_name);

   END update_emconfig;
   PROCEDURE flip_em_synonym IS
      proc_name     VARCHAR2(30) := '.flip_em_synonym';
      v_active_tbl  VARCHAR2(30);
      v_timestamp   DATE;
      v_months_data INTEGER;
      v_err_msg     t_err_msg;
      v_source      error_messages_1.SOURCE%TYPE;
      v_message     error_messages_1.message%TYPE;
      v_severity    error_messages_1.severity%TYPE;
      v_arch_date   DATE;
      v_start_date  DATE;
      v_ndays       INTEGER;
      v_flipcontrol INTEGER;
      CURSOR arch_logs_1 IS
         SELECT TIMESTAMP,SOURCE,message,severity FROM error_messages_1 WHERE TIMESTAMP <= (SELECT MIN(em_start_date) + 1
         FROM   em_config_data);
      CURSOR arch_logs_2 IS 
         SELECT TIMESTAMP,SOURCE,message,severity FROM error_messages_2 WHERE TIMESTAMP <= (SELECT MIN(em_start_date) + 1
         FROM   em_config_data);

   BEGIN

      v_flipcontrol := dbfl_store.get_stored_integer('FLIP JOB CONTROL');
      v_months_data := dbfl_store.get_stored_integer('NO OF MONTHS');
      IF v_months_data IS NULL THEN
         v_months_data := 2;
      END IF;

      SELECT SYSDATE - 30 *
             v_months_data
      INTO   v_arch_date
      FROM   dual;

      SELECT trunc(MIN(em_start_date))
      INTO   v_start_date
      FROM   em_config_data;


      -- select em inactive status
      SELECT em_table_name
      INTO   v_active_tbl
      FROM   em_config_data
      WHERE  em_active = 'N';

      -- Flip Synonym
      --EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM error_messages FOR dbfl.' ||
      --                v_active_tbl;

      -- Flip status of em_active in em_config_data
      UPDATE em_config_data
      SET    em_active = 'Y'
      WHERE  em_table_name =
             v_active_tbl;

      UPDATE em_config_data
      SET    em_active = 'N'
      WHERE  em_table_name !=
             v_active_tbl;

      COMMIT;


      v_ndays := v_arch_date -
                 v_start_date;
      IF v_ndays = 0 THEN
         v_ndays := 1;
      END IF;

      /* dbfl_error.raise_error('I'
      ,'No of days to be archived is - ' ||
       v_ndays
      ,pk_name ||
       proc_name);*/

      IF v_ndays < 0 THEN
         update_emconfig;
      ELSE

         FOR i IN 1 .. v_ndays LOOP
            OPEN arch_logs_1;
            FETCH arch_logs_1 BULK COLLECT
               INTO v_err_msg;
            CLOSE arch_logs_1;
            -- Archive oldest data into log files and delete in the main tables
            FOR indx IN 1 .. v_err_msg.COUNT LOOP
               IF v_flipcontrol = 0 THEN
                  update_emconfig;
                  dbfl_error.raise_error('I'
                                        ,'Job Manually exited '
                                        ,pk_name ||
                                         proc_name);

                  RETURN;
               END IF;
               archive_log(v_err_msg(indx)
                           .TIMESTAMP
                          , v_err_msg(indx)
                           .SOURCE
                          , v_err_msg(indx)
                           .message
                          , v_err_msg(indx)
                           .severity);
            END LOOP;
            OPEN arch_logs_2;
            FETCH arch_logs_2 BULK COLLECT
               INTO v_err_msg;
            CLOSE arch_logs_2;
            FOR indx IN 1 .. v_err_msg.COUNT LOOP
               archive_log(v_err_msg(indx)
                           .TIMESTAMP
                          , v_err_msg(indx)
                           .SOURCE
                          , v_err_msg(indx)
                           .message
                          , v_err_msg(indx)
                           .severity);
            END LOOP;
            update_emconfig;
         END LOOP;
      END IF;
   EXCEPTION
      WHEN OTHERS THEN
         dbfl_error.raise_error('W'
                               ,'Logging directory is not defined'
                               ,pk_name ||
                                proc_name);

   END flip_em_synonym;

   /* raise error messages to a file */
   PROCEDURE archive_log(v_timestamp IN DATE
                        ,v_source    IN VARCHAR2
                        ,v_message   IN VARCHAR2
                        ,v_severity  IN VARCHAR2) IS
      -- Program Data
      proc_name    VARCHAR2(20) := '.archive_log';
      lv_file      utl_file.file_type;
      lv_file_name VARCHAR2(240);
      lv_log_dir   VARCHAR2(240);
      e_log_failure EXCEPTION;
      v_logsep VARCHAR2(10);
      dummy    VARCHAR2(255);

      -- PL/SQL Block
   BEGIN

      -- get log_separator
      v_logsep := dbfl_store.get_stored_string('ERROR LOG SEPARATOR');
      IF v_logsep IS NULL THEN
         v_logsep := '##';
      END IF;
      -- get dir_name
      lv_log_dir := dbfl_store.get_stored_string('ERROR LOG DIR');
      IF lv_log_dir IS NULL THEN
         dbms_output.put_line('ERROR LOG DIR not defined in ' ||
                              pk_name ||
                              proc_name);
         RAISE e_log_failure;
      END IF;

      -- get file_name
      lv_file_name := dbfl_store.get_stored_string('ERROR LOG FILE');
      IF lv_file_name IS NULL THEN
         lv_file_name := 'log_' ||
                         dbfl_store.get_stored_string('SYSTEM NAME');
      END IF;
      -- log file name should be date based to allow archiving of old logs
      -- so log changes at midnight
      lv_file_name := lv_file_name || '_' ||
                      to_char(trunc(v_timestamp)
                             ,'YYYYMMDD') ||
                      '.log';

      -- check that file exists by opening in read mode
      IF NOT
          utl_file.is_open(lv_file) THEN
         BEGIN
            /*anon block */
            lv_file := utl_file.fopen(lv_log_dir
                                     ,lv_file_name
                                     ,'r');
         EXCEPTION
            WHEN utl_file.invalid_operation THEN
               -- create file as new
               lv_file := utl_file.fopen(lv_log_dir
                                        ,lv_file_name
                                        ,'w');
               dbfl_error.raise_error('I'
                                     ,'log file created ok as ' ||
                                      lv_log_dir || '/' ||
                                      lv_file_name
                                     ,pk_name ||
                                      proc_name);
               utl_file.put_line(lv_file
                                ,'in confidential - ' ||
                                 dbfl_store.get_stored_string('SYSTEM NAME') ||
                                 ' log report');
               utl_file.put_line(lv_file
                                ,'file ' ||
                                 lv_log_dir || '/' ||
                                 lv_file_name ||
                                 ' created on ' ||
                                 to_char(SYSDATE
                                        ,'dd-mm-yyyy hh24:mi:ss'));
         END; /*anon block */
         utl_file.fclose(lv_file);
      END IF;

      -- now open file for append.
      lv_file := utl_file.fopen(lv_log_dir
                               ,lv_file_name
                               ,'A');

      -- now append message to file.
      utl_file.put_line(lv_file
                       ,to_char(v_timestamp
                               ,'DD-MM-YYYY HH24:MI:SS') ||
                        v_logsep ||
                        v_severity ||
                        v_logsep ||
                        v_message ||
                        v_logsep ||
                        v_source);

      -- finally close the file.
      utl_file.fclose(lv_file);

      -- Delete original messages in e_m_1 or e_m_2
      DELETE FROM error_messages_1
      WHERE  TIMESTAMP =
             v_timestamp;

      DELETE FROM error_messages_2
      WHERE  TIMESTAMP =
             v_timestamp;
      COMMIT;
   EXCEPTION
      WHEN e_log_failure THEN
         dbfl_error.raise_error('W'
                               ,'Logging directory is not defined'
                               ,pk_name ||
                                proc_name);

      -- if the directory is invalid
      WHEN utl_file.invalid_path THEN
         dummy := dbfl_store.store_data_value('ERROR LOG DO'
                                             ,NULL
                                             ,'F'
                                             ,NULL); -- disable file logging
      /*dbfl_error.raise_error('C'
      ,'Log directory ' ||
       lv_log_dir ||
       ' is invalid'
      ,pk_name ||
       proc_name);*/
      -- if the file cannot be opened, or written to
      WHEN utl_file.invalid_operation THEN
         dummy := dbfl_store.store_data_value('ERROR LOG DO'
                                             ,NULL
                                             ,'F'
                                             ,NULL); -- disable file logging
         dbfl_error.raise_error('C'
                               ,'Log file ' ||
                                lv_log_dir || '/' ||
                                lv_file_name ||
                                ' cannot be used'
                               ,pk_name ||
                                proc_name);
         -- no matter what error occurs CLOSE ANY OPEN files
      WHEN OTHERS THEN
         dbms_output.put_line('Unhandled exception in ' ||
                              proc_name);
         utl_file.fclose_all;
         dummy := dbfl_store.store_data_value('ERROR LOG DO'
                                             ,NULL
                                             ,'F'
                                             ,NULL); -- disable file logging
         dbfl_error.raise_error('F'
                               ,'System failed in execution because of ' ||
                                SQLCODE ||
                                ' - ' ||
                                SQLERRM
                               ,pk_name ||
                                proc_name);
   END archive_log;

END em_management;