PROCEDURE log (sid           NUMBER,
                         lnum         NUMBER,
                         ecode       VARCHAR2,
                         emessg   VARCHAR2)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   l_svrty  VARCHAR2 (1);
BEGIN
   l_svrty:= NULL;
   pkg_excep.p_get (ecode, l_svrty); -- get the severity from below 

   IF (l_svrty= NULL)
   THEN
      l_svrty:= 'C';
   END IF;

   pkg_excep.p_log (sid,
                               lnum,
                               ecode,
                               emessg,
                               l_svrty); -- log into the log table, what the error is
   COMMIT;

   IF (l_svrty= 'C')
   THEN
      pkg2.p_upd_table (sid, l_svrty); -- update your master transaction table with error 
      COMMIT;
   END IF;
END;

---**---


PROCEDURE pkg2.p_upd_table (sid IN NUMBER, in_result_flag IN VARCHAR2)
IS
BEGIN
   IF in_result_flag = 'C'
   THEN
      UPDATE my_master_table
         SET test_result = 'E', err_severity = 'C'
       WHERE seq_id = sid;
   ELSE                                                            
      IF recname..err_severity IS NULL
      THEN
         UPDATE my_master_table
            SET err_severity = in_result_flag
          WHERE seq_id = sid;
      END IF;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END;

----**---

PROCEDURE p_get (p_code IN VARCHAR2, p_svrty OUT VARCHAR2)
IS
   v_error_code   VARCHAR2 (100) := p_error_code;
   v_severity     VARCHAR2 (100);
BEGIN
   SELECT severity
     INTO v_severity
     FROM code_table_error
    WHERE ERROR_CODE = v_error_code; -- code_table_error is static table where you store error info

   --
   p_svrty := v_severity;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      p_svrty := NULL;
--------
END;