Rem drv: <create type="pkgbodys" pos="audit/audit_admin_pkgbody.sql+"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/sdk/sdk_audit_log_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/1 2008/10/09 12:43:11 pshishir Exp $
Rem
Rem audit_log_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2008, Oracle and/or its affiliates. All rights reserved.
Rem
Rem    NAME
Rem      audit_log_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    pshishir    10/08/08 - removing utl_file references, they are not
Rem                           required
Rem    sradhakr    08/24/07 - Backport sradhakr_bug-6028566 from main
Rem    pshishir    05/10/07 - Fixing bug 5996233 
Rem    sradhakr    02/20/07 - Backport sradhakr_bug-05646376 from main
Rem    sradhakr    07/15/07 - Fix for bug 5646376 does not work in 9.2.0
Rem                           databases.
Rem    sradhakr    12/14/06 - ORA-01422 when auditing is enabled Bug# 5646376.
Rem    skini       06/23/06 - Change update_job_step_info to be not autonomous 
Rem    skini       07/21/06 - Backport skini_bug-5195692 from main 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    shianand    02/10/05 - 
Rem    shianand    02/03/05 - shianand_audit
Rem    shianand    12/17/04 - Created
Rem



CREATE OR REPLACE PACKAGE BODY MGMT_AUDIT_LOG IS

g_db_session_id NUMBER := NULL;
g_db_logon_time DATE   := NULL;
g_db_user_session_id_guid RAW(64) := NULL;

--Constants for audit on/off mode
AUDIT_ON        constant NUMBER(1) :=1; 
AUDIT_OFF       constant NUMBER(1) :=0; 


--This procedure returns the audit flag value for the given op_code 
PROCEDURE check_audit_on(p_op_code  IN  NUMBER,
                         p_audit_on OUT NUMBER)
IS
l_audit_level     NUMBER(1);
l_operation_audit NUMBER(1);

BEGIN
  mgmt_audit_admin.audit_level(l_audit_level);
  IF(l_audit_level = mgmt_audit_admin.AUDIT_LEVEL_ALL) THEN
      p_audit_on :=AUDIT_ON;
  ELSIF(l_audit_level = mgmt_audit_admin.AUDIT_LEVEL_SELECTED) THEN
      SELECT operation_audit
        INTO l_operation_audit
        FROM  MGMT_OPERATIONS_MASTER
        WHERE op_code = p_op_code;

      IF(l_operation_audit = mgmt_audit_admin.OPERATION_AUDIT_ON) THEN
          p_audit_on :=AUDIT_ON;
      ELSE
          p_audit_on :=AUDIT_OFF;
      END IF;
  ELSE
        p_audit_on :=AUDIT_OFF;
  END IF;
END check_audit_on;



--This procedure returns the user_type given the user_name as the input parameter
PROCEDURE get_em_user_type (p_user_name IN VARCHAR2,
                            p_user_type OUT VARCHAR2)
IS
l_system_user NUMBER;

BEGIN
   SELECT system_user 
     INTO l_system_user
     FROM MGMT_CREATED_USERS
     WHERE user_name = UPPER(p_user_name)
     AND ROWNUM <2;

   IF(l_system_user = 0) THEN
       p_user_type :=  'EM_USER';
   ELSIF(l_system_user =1) THEN
       p_user_type := 'SYSTEM_USER';
   ELSIF(l_system_user = 2) THEN
       p_user_type := 'SSO_USER';
   ELSIF(l_system_user = 3) THEN
       p_user_type := 'ENTERPRISE_USER';
   ELSE
      p_user_type := 'NO USER FOUND';
   END IF;
END get_em_user_type;



--This procedure generates the DB session guid and stores the same in the package
--variable g_db_user_session_id_guid. This is called when no session guid is 
--generated from the UI side to log in the information for all the backend 
--operations. This guid is unique for a session.
PROCEDURE gen_db_user_session_id_guid
IS

BEGIN
    g_db_user_session_id_guid := sys_guid();
END gen_db_user_session_id_guid;



--This procedure gets the DB session_id and logon_time from the V$SESSION. 
--This is called to get the current DB session_id to keep track of the same 
--unique session
PROCEDURE get_db_session_id(p_db_session_id OUT NUMBER,
                            p_db_logon_time OUT DATE)
IS
l_db_session_id NUMBER;
l_db_logon_time DATE;

BEGIN
-- as audsid is 0 fro dbms-jobs we cannot use userenv('sessionid')
    SELECT sid, logon_time
      INTO l_db_session_id, l_db_logon_time
      FROM V$SESSION
      WHERE sid = (select sid from V$MYSTAT where rownum=1);

-- this version break for older db versions so replace with  
-- older equiv - see bug 6028566  
--      SELECT sid, logon_time 
--      INTO l_db_session_id, l_db_logon_time
--      FROM V$SESSION 
--      WHERE sid = (select sys_context('userenv', 'sid') from dual); 

    p_db_session_id := l_db_session_id;
    p_db_logon_time := l_db_logon_time;
END get_db_session_id;


--This procedure sets the DB session_id and the logon_time in the package 
--variable g_db_session_id, g_db_logon_time
PROCEDURE set_db_session_id(p_db_session_id IN NUMBER,
                            p_db_logon_time IN DATE)
IS

BEGIN
    g_db_session_id := p_db_session_id;
    g_db_logon_time := p_db_logon_time;
END set_db_session_id;


--This logs in the information for the DB session in the MGMT_USER_SESSION Table
--This function logs the information once for a session and unique session_guid 
--associated with that session
PROCEDURE set_db_user_session_info
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_username  VARCHAR2(4000);
l_user_type VARCHAR2(4000);
l_osuser    VARCHAR2(4000);
l_machine   VARCHAR2(4000);
l_terminal  VARCHAR2(4000);
l_db_session_id   RAW(64);
l_session_type  VARCHAR2(30) := 'DB SESSION';

BEGIN
    SELECT osuser, machine, terminal
      INTO l_osuser, l_machine, l_terminal
      FROM V$SESSION
      WHERE sid = g_db_session_id
      AND logon_time = g_db_logon_time;
    l_username := MGMT_USER.GET_CURRENT_EM_USER;
    l_db_session_id := cast(to_char(g_db_session_id) as RAW);
    
    get_em_user_type (l_username,
                      l_user_type);
    
    INSERT INTO MGMT_USER_SESSION (user_session_id_guid,
                                   user_session_id,
                                   em_user,
                                   em_user_type,
                                   em_user_host_name,
                                   login_time,
                                   browser_type,
                                   osuser,
                                   session_type)
     VALUES (g_db_user_session_id_guid,
             l_db_session_id,
             l_username,
             l_user_type,
             l_machine,
             g_db_logon_time,
             l_terminal,
             l_osuser,
             l_session_type);
     COMMIT;
     EXCEPTION
        WHEN OTHERS THEN
          COMMIT;
END set_db_user_session_info;



--This stores the audit information in the MGMT_AUDIT_LOGS Table in the 
--audit_mode in MGMT_AUDIT_MASTER Table is set to AUDIT_MODE_DB
PROCEDURE db_mode_audit(p_user_session_id_guid  IN RAW,
                        p_audit_guid            IN RAW,
                        p_op_code               IN NUMBER,
                        p_object_name           IN VARCHAR2,
                        p_object_type           IN VARCHAR2,
                        p_object_owner          IN VARCHAR2,
                        p_time_stamp            IN DATE,
                        p_audit_column_value1   IN VARCHAR2,
                        p_audit_column_value2   IN VARCHAR2,
                        p_audit_column_value3   IN VARCHAR2,
                        p_audit_column_value4   IN VARCHAR2,
                        p_audit_column_value5   IN VARCHAR2,
                        p_audit_column_value6   IN VARCHAR2,
                        p_audit_column_value7   IN VARCHAR2,
                        p_audit_column_value8   IN VARCHAR2,
                        p_audit_column_value9   IN VARCHAR2,
                        p_audit_column_value10  IN VARCHAR2,
                        p_audit_column_value11  IN VARCHAR2,
                        p_audit_column_value12  IN VARCHAR2,
                        p_audit_column_value13  IN VARCHAR2,
                        p_audit_column_value14  IN VARCHAR2,
                        p_audit_column_value15  IN VARCHAR2,
                        p_audit_clob_value1     IN CLOB)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
emp_clob  CLOB;
des_clob  CLOB;
amt INTEGER       := 32767;
des_offset NUMBER := 1;
src_offset NUMBER := 1;
len NUMBER        := 1;

BEGIN
    emp_clob := EMPTY_CLOB();
    IF(p_audit_clob_value1 IS NULL) THEN
        des_clob := NULL;
    ELSE
        len := dbms_lob.getlength(p_audit_clob_value1);
        LOOP
            dbms_lob.copy(des_clob,
                          p_audit_clob_value1,
                          amt,
                          des_offset,
                          src_offset);
            exit when src_offset > len;
            src_offset := src_offset + amt;
            des_offset := des_offset + amt;
        END LOOP;
    END IF;

    INSERT INTO MGMT_AUDIT_LOGS (user_session_id_guid,
                                 audit_guid,
                                 op_code,
                                 object_name,
                                 object_type,
                                 object_owner,
                                 time_stamp,
                                 audit_column_value1,
                                 audit_column_value2,
                                 audit_column_value3,
                                 audit_column_value4,
                                 audit_column_value5,
                                 audit_column_value6,
                                 audit_column_value7,
                                 audit_column_value8,
                                 audit_column_value9,
                                 audit_column_value10,
                                 audit_column_value11,
                                 audit_column_value12,
                                 audit_column_value13,
                                 audit_column_value14,
                                 audit_column_value15,
                                 audit_clob_value1)
    VALUES (p_user_session_id_guid,
            p_audit_guid,
            p_op_code,
            p_object_name,
            p_object_type,
            p_object_owner,
            sysdate,
            p_audit_column_value1,
            p_audit_column_value2,
            p_audit_column_value3,
            p_audit_column_value4,
            p_audit_column_value5,
            p_audit_column_value6,
            p_audit_column_value7,
            p_audit_column_value8,
            p_audit_column_value9,
            p_audit_column_value10,
            p_audit_column_value11,
            p_audit_column_value12,
            p_audit_column_value13,
            p_audit_column_value14,
            p_audit_column_value15,
            emp_clob)
            RETURNING audit_clob_value1 INTO des_clob;
    COMMIT;

    EXCEPTION
        WHEN OTHERS THEN
          COMMIT; 
END db_mode_audit;


--This stores the audit information in the file AUDIT_DIR/em.log if the  
--audit_mode in MGMT_AUDIT_MASTER Table is set to AUDIT_MODE_FILE
PROCEDURE file_mode_audit(p_user_session_id_guid  IN RAW,
                          p_audit_guid            IN RAW,
                          p_op_code               IN NUMBER,
                          p_object_name           IN VARCHAR2,
                          p_object_type           IN VARCHAR2,
                          p_object_owner          IN VARCHAR2,
                          p_time_stamp            IN DATE,
                          p_audit_column_value1   IN VARCHAR2,
                          p_audit_column_value2   IN VARCHAR2,
                          p_audit_column_value3   IN VARCHAR2,
                          p_audit_column_value4   IN VARCHAR2,
                          p_audit_column_value5   IN VARCHAR2,
                          p_audit_column_value6   IN VARCHAR2,
                          p_audit_column_value7   IN VARCHAR2,
                          p_audit_column_value8   IN VARCHAR2,
                          p_audit_column_value9   IN VARCHAR2,
                          p_audit_column_value10  IN VARCHAR2,
                          p_audit_column_value11  IN VARCHAR2,
                          p_audit_column_value12  IN VARCHAR2,
                          p_audit_column_value13  IN VARCHAR2,
                          p_audit_column_value14  IN VARCHAR2,
                          p_audit_column_value15  IN VARCHAR2,
                          p_audit_clob_value1     IN CLOB)
IS
BEGIN
    NULL;
END file_mode_audit;






--This procedure calls the file_mode_audit or db_mode_audit depending on the 
--audit_mode set in the MGMT_AUDIT_MASTER Table
PROCEDURE insert_audit_data(p_audit_mode           IN NUMBER,
                            p_user_session_id_guid IN RAW,
                            p_audit_guid           IN RAW,
                            p_op_code              IN NUMBER,
                            p_object_name          IN VARCHAR2 DEFAULT NULL,
                            p_object_type          IN VARCHAR2 DEFAULT NULL,
                            p_object_owner         IN VARCHAR2 DEFAULT NULL,
                            p_time_stamp           IN DATE,
                            p_audit_column_value1  IN VARCHAR2 DEFAULT NULL,
                            p_audit_column_value2  IN VARCHAR2 DEFAULT NULL,
                            p_audit_column_value3  IN VARCHAR2 DEFAULT NULL,
                            p_audit_column_value4  IN VARCHAR2 DEFAULT NULL,
                            p_audit_column_value5  IN VARCHAR2 DEFAULT NULL,
                            p_audit_column_value6  IN VARCHAR2 DEFAULT NULL,
                            p_audit_column_value7  IN VARCHAR2 DEFAULT NULL,
                            p_audit_column_value8  IN VARCHAR2 DEFAULT NULL,
                            p_audit_column_value9  IN VARCHAR2 DEFAULT NULL,
                            p_audit_column_value10 IN VARCHAR2 DEFAULT NULL,
                            p_audit_column_value11 IN VARCHAR2 DEFAULT NULL,
                            p_audit_column_value12 IN VARCHAR2 DEFAULT NULL,
                            p_audit_column_value13 IN VARCHAR2 DEFAULT NULL,
                            p_audit_column_value14 IN VARCHAR2 DEFAULT NULL,
                            p_audit_column_value15 IN VARCHAR2 DEFAULT NULL,
                            p_audit_clob_value1    IN CLOB     DEFAULT NULL) 
IS

BEGIN
        -- Support only DB mode. 
        db_mode_audit(p_user_session_id_guid,
                      p_audit_guid,
                      p_op_code,
                      p_object_name,
                      p_object_type,
                      p_object_owner,
                      p_time_stamp,
                      p_audit_column_value1,
                      p_audit_column_value2,
                      p_audit_column_value3,
                      p_audit_column_value4,
                      p_audit_column_value5,
                      p_audit_column_value6,
                      p_audit_column_value7,
                      p_audit_column_value8,
                      p_audit_column_value9,
                      p_audit_column_value10,
                      p_audit_column_value11,
                      p_audit_column_value12,
                      p_audit_column_value13,
                      p_audit_column_value14,
                      p_audit_column_value15,
                      p_audit_clob_value1);
END insert_audit_data;



--This procedure is called when ever a data is audited.
PROCEDURE audit_log (p_op_code              IN  NUMBER,
                     p_object_name          IN  VARCHAR2 DEFAULT NULL,
                     p_object_type          IN  VARCHAR2 DEFAULT NULL,
                     p_object_owner         IN  VARCHAR2 DEFAULT NULL,
                     p_audit_column_value1  IN  VARCHAR2 DEFAULT NULL,
                     p_audit_column_value2  IN  VARCHAR2 DEFAULT NULL,
                     p_audit_column_value3  IN  VARCHAR2 DEFAULT NULL,
                     p_audit_column_value4  IN  VARCHAR2 DEFAULT NULL,
                     p_audit_column_value5  IN  VARCHAR2 DEFAULT NULL,
                     p_audit_column_value6  IN  VARCHAR2 DEFAULT NULL,
                     p_audit_column_value7  IN  VARCHAR2 DEFAULT NULL,
                     p_audit_column_value8  IN  VARCHAR2 DEFAULT NULL,
                     p_audit_column_value9  IN  VARCHAR2 DEFAULT NULL,
                     p_audit_column_value10 IN  VARCHAR2 DEFAULT NULL,
                     p_audit_column_value11  IN  VARCHAR2 DEFAULT NULL,
                     p_audit_column_value12 IN  VARCHAR2 DEFAULT NULL,
                     p_audit_column_value13 IN  VARCHAR2 DEFAULT NULL,
                     p_audit_column_value14 IN  VARCHAR2 DEFAULT NULL,
                     p_audit_column_value15 IN  VARCHAR2 DEFAULT NULL,
                     p_audit_clob_value1    IN  CLOB     DEFAULT NULL,
                     p_user_session_id_guid IN  RAW      DEFAULT NULL)
IS
l_time_stamp            DATE;
l_audit_guid            RAW(64);
l_audit_on              NUMBER;
l_audit_mode            NUMBER;
l_user_session_id_guid  RAW(64):= NULL;
l_db_session_id         NUMBER;
l_db_logon_time         DATE;

BEGIN
    check_audit_on(p_op_code,
                   l_audit_on);
    IF (l_audit_on =AUDIT_ON) THEN
        IF(p_user_session_id_guid IS NOT NULL) THEN
            l_user_session_id_guid := p_user_session_id_guid;
        ELSE
            l_user_session_id_guid := mgmt_audit_admin.get_user_session_id_guid;
        END IF;
        l_audit_guid := sys_guid();
        l_time_stamp := cast(sys_extract_utc(systimestamp) as DATE);

        SELECT audit_mode 
          INTO l_audit_mode
          FROM MGMT_AUDIT_DESTINATION;

        IF (l_user_session_id_guid IS NULL) THEN
            get_db_session_id(l_db_session_id, 
                              l_db_logon_time);
            IF((g_db_session_id IS NOT NULL) AND (g_db_user_session_id_guid IS NOT NULL)) THEN
                IF(l_db_session_id != g_db_session_id 
                   AND l_db_logon_time != g_db_logon_time) THEN
                    gen_db_user_session_id_guid;
                    set_db_session_id(l_db_session_id,
                                      l_db_logon_time);
                    set_db_user_session_info;
                END IF;
            ELSE
                gen_db_user_session_id_guid;
                set_db_session_id(l_db_session_id,
                                  l_db_logon_time);
                set_db_user_session_info;
            END IF;
            l_user_session_id_guid := g_db_user_session_id_guid;
        END IF;
            insert_audit_data(l_audit_mode,
                              l_user_session_id_guid,
                              l_audit_guid,
                              p_op_code,
                              p_object_name,
                              p_object_type,
                              p_object_owner,
                              l_time_stamp,
                              p_audit_column_value1,
                              p_audit_column_value2,
                              p_audit_column_value3,
                              p_audit_column_value4,
                              p_audit_column_value5,
                              p_audit_column_value6,
                              p_audit_column_value7,
                              p_audit_column_value8,
                              p_audit_column_value9,
                              p_audit_column_value10,
                              p_audit_column_value11,
                              p_audit_column_value12,
                              p_audit_column_value13,
                              p_audit_column_value14,
                              p_audit_column_value15,
                              p_audit_clob_value1);
    END IF;
END audit_log;



--This procedure is called when ever a data is audited.
PROCEDURE audit_log (p_op_code        IN  NUMBER,
                     p_audit_records  IN  AUDIT_ARRAY)
IS
l_time_stamp            DATE;
l_audit_on              NUMBER; 
l_audit_guid            RAW(64);
l_audit_mode            NUMBER;
l_user_session_id_guid  RAW(64);
l_db_session_id         NUMBER;
l_db_logon_time         DATE;

BEGIN
    check_audit_on(p_op_code,
                   l_audit_on);
    IF (l_audit_on =AUDIT_ON) THEN
        l_user_session_id_guid := mgmt_audit_admin.get_user_session_id_guid;
        l_audit_guid := sys_guid();
        l_time_stamp := cast(sys_extract_utc(systimestamp) as DATE);

        SELECT audit_mode 
          INTO l_audit_mode
          FROM MGMT_AUDIT_DESTINATION;

        IF (l_user_session_id_guid IS NULL) THEN
            get_db_session_id(l_db_session_id,
                              l_db_logon_time);
            IF((g_db_session_id IS NOT NULL) AND (g_db_user_session_id_guid IS NOT NULL)) THEN
                 IF (l_db_session_id != g_db_session_id 
                     AND l_db_logon_time != g_db_logon_time) THEN
                     gen_db_user_session_id_guid;
                     set_db_session_id(l_db_session_id,
                                       l_db_logon_time);
                     set_db_user_session_info;
                 END IF;
            ELSE
                 gen_db_user_session_id_guid;
                 set_db_session_id(l_db_session_id,
                                   l_db_logon_time);
                 set_db_user_session_info;
            END IF;
            l_user_session_id_guid :=  g_db_user_session_id_guid;
        END IF;

        FOR i in p_audit_records.FIRST..p_audit_records.LAST 
        LOOP
            insert_audit_data(l_audit_mode,
                              l_user_session_id_guid,
                              l_audit_guid,
                              p_op_code,
                              p_audit_records(i).object_name,
                              p_audit_records(i).object_type,
                              p_audit_records(i).object_owner,
                              l_time_stamp,
                              p_audit_records(i).audit_column_value1,
                              p_audit_records(i).audit_column_value2,
                              p_audit_records(i).audit_column_value3,
                              p_audit_records(i).audit_column_value4,
                              p_audit_records(i).audit_column_value5,
                              p_audit_records(i).audit_column_value6,
                              p_audit_records(i).audit_column_value7,
                              p_audit_records(i).audit_column_value8,
                              p_audit_records(i).audit_column_value9,
                              p_audit_records(i).audit_column_value10,
                              p_audit_records(i).audit_column_value11,
                              p_audit_records(i).audit_column_value12,
                              p_audit_records(i).audit_column_value13,
                              p_audit_records(i).audit_column_value14,
                              p_audit_records(i).audit_column_value15,
                              p_audit_records(i).audit_clob_value1);
        END LOOP;
    END IF;

END audit_log;



--Converts the step_status constant into character strings
--SCHEDULED_STATUS constant NUMBER(2) := 1;
--EXECUTING_STATUS constant NUMBER(2) := 2;
--ABORTED_STATUS constant NUMBER(2) := 3;
--FAILED_STATUS constant NUMBER(2) := 4;
--COMPLETED_STATUS constant NUMBER(2) := 5;
--SUSPENDED_STATUS constant NUMBER(2) := 6;
--AGENTDOWN_STATUS constant NUMBER(2) := 7;
--STOPPED_STATUS constant NUMBER(2) := 8;
PROCEDURE job_step_status(p_step_status     IN NUMBER,
                          p_out_step_status OUT VARCHAR2)
IS
BEGIN
   IF (p_step_status = MGMT_JOB_ENGINE.SCHEDULED_STATUS) THEN
       p_out_step_status := 'SCHEDULED_STATUS';
   ELSIF (p_step_status = MGMT_JOB_ENGINE.EXECUTING_STATUS) THEN
       p_out_step_status := 'EXECUTING_STATUS';
   ELSIF (p_step_status = MGMT_JOB_ENGINE.ABORTED_STATUS) THEN   
       p_out_step_status := 'ABORTED_STATUS';
   ELSIF (p_step_status = MGMT_JOB_ENGINE.FAILED_STATUS) THEN
       p_out_step_status := 'FAILED_STATUS';
   ELSIF (p_step_status = MGMT_JOB_ENGINE.COMPLETED_STATUS) THEN
       p_out_step_status := 'COMPLETED_STATUS';
   ELSIF (p_step_status = MGMT_JOB_ENGINE.SUSPENDED_STATUS) THEN
       p_out_step_status := 'SUSPENDED_STATUS';
   ELSIF (p_step_status = MGMT_JOB_ENGINE.AGENTDOWN_STATUS) THEN
       p_out_step_status := 'AGENTDOWN_STATUS';
   ELSIF (p_step_status = MGMT_JOB_ENGINE.STOPPED_STATUS) THEN
       p_out_step_status := 'STOPPED_STATUS';
   ELSE 
       p_out_step_status := 'STATUS NOT TO BE SHOWN';
   END IF;
END job_step_status;



--This updates the MGMT_AUDIT_LOGS Table which has the remote_op, get_file, put_file,
--file_transfer entry corresponding to the specified job step_id.
PROCEDURE update_job_step_info(p_output_id   IN RAW,
                               p_step_id     IN NUMBER,
                               p_step_status IN NUMBER)
IS
l_audit_on NUMBER;
amt        INTEGER := 32767;
des_offset INTEGER := 1;
src_offset INTEGER := 1;
emp_clob   CLOB;
des_clob   CLOB := NULL;
src_clob   CLOB := NULL;
len        NUMBER := 1;
l_op_code  NUMBER;
l_out_step_status VARCHAR2(30);

BEGIN
 --IF the output_id  is null then source clob is null  
  IF p_step_status IN (MGMT_JOB_ENGINE.COMPLETED_STATUS,
                       MGMT_JOB_ENGINE.FAILED_STATUS,
                       MGMT_JOB_ENGINE.ABORTED_STATUS,
                       MGMT_JOB_ENGINE.STOPPED_STATUS) THEN
     SELECT op_code
       INTO l_op_code
       FROM MGMT_AUDIT_LOGS
       WHERE op_code in (REMOTE_OP, GET_FILE, PUT_FILE, FILE_TRANSFER)
       AND  audit_column_value1 = to_char(p_step_id);

     check_audit_on(l_op_code,
                    l_audit_on);
     IF (l_audit_on =AUDIT_ON) THEN
         BEGIN
            emp_clob := EMPTY_CLOB();
            job_step_status(p_step_status,
                            l_out_step_status);

            UPDATE MGMT_AUDIT_LOGS
              SET  audit_column_value2 = l_out_step_status, audit_clob_value1 = emp_clob
              WHERE op_code = l_op_code
              AND  audit_column_value1 = to_char(p_step_id)
              RETURNING audit_clob_value1 INTO des_clob;

            IF p_output_id IS NULL THEN
               src_clob := NULL;
               des_clob := NULL;
            ELSE
               SELECT output
                 INTO src_clob
                 FROM MGMT_JOB_OUTPUT
                 WHERE output_id = p_output_id;

               len := DBMS_LOB.GETLENGTH(src_clob);
               LOOP
                  DBMS_LOB.COPY(des_clob,
                                src_clob,
                                amt,
                                des_offset,
                                src_offset);
                  EXIT WHEN src_offset > len;
                  src_offset := src_offset + amt;
                  des_offset := des_offset + amt;
               END LOOP;
            END IF;
            EXCEPTION
                WHEN OTHERS THEN
                    RETURN;
         END;
     END IF;
  ELSE
     RETURN;
  END IF;
  EXCEPTION
     WHEN NO_DATA_FOUND THEN
        RETURN;
END update_job_step_info;


--Gives the job_name and job_type and job_owner after querying the MGMT_JOB
--with specified job_id
PROCEDURE get_job_step_info (p_job_id    IN RAW,
                             p_job_name  OUT VARCHAR2,
                             p_job_type  OUT VARCHAR2,
                             p_job_owner OUT VARCHAR2)
IS

BEGIN
   SELECT job_name, job_type, job_owner
     INTO p_job_name, p_job_type, p_job_owner
     FROM MGMT_JOB
     WHERE job_id = p_job_id;
   
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         p_job_name  := NULL;
         p_job_type  := NULL;
         p_job_owner := NULL;
END get_job_step_info;


END MGMT_AUDIT_LOG;

/
show errors;