PROMPT CREATE OR REPLACE PACKAGE add_app_msg

CREATE OR REPLACE PACKAGE add_app_msg IS


-- This package is used to log application message.
-- It implement the pragma AUTONOMOUS_TRANSACTION to ensure that
-- any commit or rollback used within this package does not affect the main
-- transaction
--

--
-- MODIFICATION HISTORY
-- Person      Date    Comments
-- ---------   ------  ------------------------------------------

   PROCEDURE log_msg
     (  l_error_level  IN number
       ,l_msg_text     IN varchar2
       ,l_subsystem    IN varchar2
       ,l_process      IN varchar2
       ,l_remarks      IN varchar2
     );

   PROCEDURE log_oracle_error
     (  p_process_name IN VARCHAR2,
        p_err_msg      IN VARCHAR2,
        p_err_code     IN NUMBER);
        
END ADD_APP_MSG; -- Package spec
/
PROMPT CREATE OR REPLACE PACKAGE BODY add_app_msg

CREATE OR REPLACE PACKAGE BODY add_app_msg IS
   PROCEDURE log_msg
     (
        l_error_level  IN number
       ,l_msg_text     IN varchar2
       ,l_subsystem    IN varchar2
       ,l_process      IN varchar2
       ,l_remarks      IN varchar2
     ) IS

     err_msg        varchar2(300);
     err_code       number(10);

     PRAGMA         AUTONOMOUS_TRANSACTION; -- directive to compiler

   BEGIN
      insert into msg_log
        (   log_time
          , error_level
          , msg_text
          , subsystem
          , process
          , remarks
        )
         values
        (   sysdate
         ,  rtrim(l_error_level)
         ,  rtrim(l_msg_text)
         ,  rtrim(l_subsystem)
         ,  rtrim(l_process)
         ,  rtrim(l_remarks)
        );
        commit;

   EXCEPTION

      WHEN others THEN
           err_msg  := SQLERRM;
           err_code := SQLCODE;
          insert into oracle_error
            (  process_name
              ,errror_msg
              ,error_code
              ,error_time
            ) values
            (
              'ADD_APP_MSG.LOG_MSG'
              ,err_msg
              ,err_code
              ,sysdate
            );
            commit;
   END log_msg;

   PROCEDURE log_oracle_error(p_process_name IN    VARCHAR2,
                              p_err_msg      IN    VARCHAR2,
                              p_err_code     IN    NUMBER) IS 
  
   PRAGMA         AUTONOMOUS_TRANSACTION;

   BEGIN
   
     INSERT INTO ORACLE_ERROR (PROCESS_NAME,
                               ERRROR_MSG,
                               ERROR_CODE,
                               ERROR_TIME
                              )
                       VALUES ( p_process_name, 
                                p_err_msg,
                                p_err_code,
                                SYSDATE 
                              );
                              
     COMMIT;
     
   EXCEPTION
     WHEN OTHERS THEN
        RAISE;
   END log_oracle_error; 
     
END ADD_APP_MSG;
/
