DROP PROCEDURE ADRMGT.JOB_PROCESS_LOG_SP;

CREATE OR REPLACE PROCEDURE ADRMGT.Job_Process_Log_Sp
  (PC_job_name
     IN     ADRMGT.Job_Process_Logs.Job_name%TYPE
       DEFAULT 'UNKNOWN',
   PD_job_log_date
     IN     ADRMGT.Job_Process_Logs.Job_log_date%TYPE
       DEFAULT SYSDATE,
   PC_job_status
     IN     ADRMGT.Job_Process_Logs.Job_status%TYPE
       DEFAULT NULL,
   PC_job_log_message
     IN     ADRMGT.Job_Process_Logs.Job_log_message%TYPE
       DEFAULT NULL,
   PD_date1
     IN     ADRMGT.Job_Process_Logs.Date1%TYPE
       DEFAULT NULL,
   PD_date2
     IN     ADRMGT.Job_Process_Logs.Date2%TYPE
       DEFAULT NULL,
   PD_date3
     IN     ADRMGT.Job_Process_Logs.Date3%TYPE
       DEFAULT NULL,
   PN_numeric1
     IN     ADRMGT.Job_Process_Logs.Numeric1%TYPE
       DEFAULT NULL,
   PN_numeric2
     IN     ADRMGT.Job_Process_Logs.Numeric2%TYPE
       DEFAULT NULL,
   PN_numeric3
     IN     ADRMGT.Job_Process_Logs.Numeric3%TYPE
       DEFAULT NULL,
   PC_text1
     IN     ADRMGT.Job_Process_Logs.Text1%TYPE
       DEFAULT NULL,
   PC_text2
     IN     ADRMGT.Job_Process_Logs.Text2%TYPE
       DEFAULT NULL,
   PC_text3
     IN     ADRMGT.Job_Process_Logs.Text3%TYPE
       DEFAULT NULL
)

IS

/*
REM ###########################################################################
REM #  Description: Create ADRMGT.Job_Process_Log_Sp stored procedure.
REM #
REM #               This procedure will be used to write job processing logs
REM #               from various scripts and DBMS_JOB jobs.
REM #
REM #   Usage:            Called by run_wrapper.sql
REM #   Input Parameters:
REM #     1) Job_name                       VARCHAR2(60)   (REQUIRED)
REM #        - Unique Name for each job using this table
REM #     2) Job_log_date                   DATE           (REQUIRED)
REM #        - Date/time of log write request
REM #     3) Job_status                     VARCHAR2(60)   (OPTIONAL)
REM #        - Current status of the job
REM #     4) Job_log_message                VARCHAR2(2000) (OPTIONAL)
REM #        - User defined job message
REM #     5) Date1                          DATE           (OPTIONAL)
REM #        - Generic date field for logging user defined information
REM #     6) Date2                          DATE           (OPTIONAL)
REM #        - Generic date field for logging user defined information
REM #     7) Date3                          DATE           (OPTIONAL)
REM #        - Generic date field for logging user defined information
REM #     8) Numeric1                       NUMBER         (OPTIONAL)
REM #        - Generic numeric field for logging user defined information
REM #     9) Numeric2                       NUMBER         (OPTIONAL)
REM #        - Generic numeric field for logging user defined information
REM #    10) Numeric3                       NUMBER         (OPTIONAL)
REM #        - Generic numeric field for logging user defined information
REM #    11) Text1                          VARCHAR2(255)  (OPTIONAL)
REM #        - Generic text field for logging user defined information
REM #    12) Text2                          VARCHAR2(255)  (OPTIONAL)
REM #        - Generic text field for logging user defined information
REM #    13) Text3                          VARCHAR2(255)  (OPTIONAL)
REM #        - Generic text field for logging user defined information
REM #   Output:
REM #     1) Rows will be inserted into the ADRMGT.Job_Process_Logs table
REM #
REM ###########################################################################
REM # Change History
REM #
REM # DATE       User Name         DESCRIPTION
REM # ---------- ----------------- -------------------------------------------
REM # 04/12/2010 Craig Wood        Created script
REM #
REM ###########################################################################
*/

  VD_log_date                          DATE;

  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

  INSERT
  INTO      ADRMGT.Job_Process_Logs
            (Job_process_logs_id,
             Job_name,
             Job_log_date,
             Job_status,
             Job_log_message,
             Date1,
             Date2,
             Date3,
             Numeric1,
             Numeric2,
             Numeric3,
             Text1,
             Text2,
             Text3,
             Record_created_date
            )
  VALUES    (ADRMGT.Job_process_logs_id_s.NEXTVAL,
             PC_job_name ,
             PD_job_log_date,
             PC_job_status,
             PC_job_log_message,
             PD_date1,
             PD_date2,
             PD_date3,
             PN_numeric1,
             PN_numeric2,
             PN_numeric3,
             PC_text1,
             PC_text2,
             PC_text3,
             SYSDATE
            );

  COMMIT;

EXCEPTION

  WHEN OTHERS THEN

    VD_log_date := SYSDATE;
    ADRMGT.Job_Process_Err_Sp('ADRMGT.Job_Process_Log_Sp',
                              VD_log_date,
                              SUBSTR('ERROR! Unable to insert into ADR_EXTRACT_PROCESS_LOG table.',1,60),
                              SUBSTR(SQLERRM,1,2000)
                             );

END Job_Process_Log_Sp;
/


GRANT EXECUTE ON ADRMGT.JOB_PROCESS_LOG_SP TO ADR;

GRANT EXECUTE ON ADRMGT.JOB_PROCESS_LOG_SP TO ADRMGT_APP_RW;

GRANT EXECUTE ON ADRMGT.JOB_PROCESS_LOG_SP TO PSIM;
DROP PROCEDURE ADRMGT.JOB_PROCESS_ERR_SP;

CREATE OR REPLACE PROCEDURE ADRMGT.Job_Process_Err_Sp
  (PC_job_name
     IN     ADRMGT.Job_Process_Errs.Job_name%TYPE
       DEFAULT 'UNKNOWN',
   PD_job_error_date
     IN     ADRMGT.Job_Process_Errs.Job_error_date%TYPE
       DEFAULT SYSDATE,
   PC_job_error_code
     IN     ADRMGT.Job_Process_Errs.Job_error_code%TYPE
       DEFAULT NULL,
   PC_job_error_message
     IN     ADRMGT.Job_Process_Errs.Job_error_message%TYPE
       DEFAULT NULL,
   PD_date1
     IN     ADRMGT.Job_Process_Errs.Date1%TYPE
       DEFAULT NULL,
   PD_date2
     IN     ADRMGT.Job_Process_Errs.Date2%TYPE
       DEFAULT NULL,
   PD_date3
     IN     ADRMGT.Job_Process_Errs.Date3%TYPE
       DEFAULT NULL,
   PN_numeric1
     IN     ADRMGT.Job_Process_Errs.Numeric1%TYPE
       DEFAULT NULL,
   PN_numeric2
     IN     ADRMGT.Job_Process_Errs.Numeric2%TYPE
       DEFAULT NULL,
   PN_numeric3
     IN     ADRMGT.Job_Process_Errs.Numeric3%TYPE
       DEFAULT NULL,
   PC_text1
     IN     ADRMGT.Job_Process_Errs.Text1%TYPE
       DEFAULT NULL,
   PC_text2
     IN     ADRMGT.Job_Process_Errs.Text2%TYPE
       DEFAULT NULL,
   PC_text3
     IN     ADRMGT.Job_Process_Errs.Text3%TYPE
       DEFAULT NULL
)

IS

/*
REM ###########################################################################
REM #  Description: Create ADRMGT.Job_Process_Err_Sp stored procedure.
REM #
REM #               This procedure will be used to write job processing logs
REM #               from various scripts and DBMS_JOB jobs.
REM #
REM #   Usage:            Called by run_wrapper.sql
REM #   Input Parameters:
REM #     1) Job_name                       VARCHAR2(60)   (REQUIRED)
REM #        - Unique Name for each job using this table
REM #     2) Job_error_date                 DATE           (REQUIRED)
REM #        - Date/time of log write request
REM #     3) Job_error_code                 VARCHAR2(60)   (OPTIONAL)
REM #        - Error code from the job
REM #     4) Job_error_message              VARCHAR2(2000) (OPTIONAL)
REM #        - User defined error message
REM #     5) Date1                          DATE           (OPTIONAL)
REM #        - Generic date field for logging user defined information
REM #     6) Date2                          DATE           (OPTIONAL)
REM #        - Generic date field for logging user defined information
REM #     7) Date3                          DATE           (OPTIONAL)
REM #        - Generic date field for logging user defined information
REM #     8) Numeric1                       NUMBER         (OPTIONAL)
REM #        - Generic numeric field for logging user defined information
REM #     9) Numeric2                       NUMBER         (OPTIONAL)
REM #        - Generic numeric field for logging user defined information
REM #    10) Numeric3                       NUMBER         (OPTIONAL)
REM #        - Generic numeric field for logging user defined information
REM #    11) Text1                          VARCHAR2(255)  (OPTIONAL)
REM #        - Generic text field for logging user defined information
REM #    12) Text2                          VARCHAR2(255)  (OPTIONAL)
REM #        - Generic text field for logging user defined information
REM #    13) Text3                          VARCHAR2(255)  (OPTIONAL)
REM #        - Generic text field for logging user defined information
REM #   Output:
REM #     1) Rows will be inserted into the ADRMGT.Job_Process_Errs table
REM #
REM ###########################################################################
REM # Change History
REM #
REM # DATE       User Name         DESCRIPTION
REM # ---------- ----------------- -------------------------------------------
REM # 04/12/2010 Craig Wood        Created script
REM #
REM ###########################################################################
*/

  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

  INSERT
  INTO      ADRMGT.Job_Process_Errs
            (Job_process_errs_id,
             Job_name,
             Job_error_date,
             Job_error_code,
             Job_error_message,
             Date1,
             Date2,
             Date3,
             Numeric1,
             Numeric2,
             Numeric3,
             Text1,
             Text2,
             Text3,
             Record_created_date
            )
  VALUES    (ADRMGT.Job_process_errs_id_s.NEXTVAL,
             PC_job_name ,
             PD_job_error_date,
             PC_job_error_code,
             PC_job_error_message,
             PD_date1,
             PD_date2,
             PD_date3,
             PN_numeric1,
             PN_numeric2,
             PN_numeric3,
             PC_text1,
             PC_text2,
             PC_text3,
             SYSDATE
            );

  COMMIT;

EXCEPTION

  WHEN OTHERS THEN

    NULL;

END Job_Process_Err_Sp;
/


GRANT EXECUTE ON ADRMGT.JOB_PROCESS_ERR_SP TO ADR;

GRANT EXECUTE ON ADRMGT.JOB_PROCESS_ERR_SP TO ADRFILE;

GRANT EXECUTE ON ADRMGT.JOB_PROCESS_ERR_SP TO ADRMGT_APP_RW;

GRANT EXECUTE ON ADRMGT.JOB_PROCESS_ERR_SP TO PSIM;
DROP PROCEDURE ADRMGT.JOB_MONITOR_SP;

CREATE OR REPLACE PROCEDURE ADRMGT.JOB_MONITOR_SP
  (
   P_COL_LIST           IN VARCHAR2, 	--REQUIRED
   P_TAB_NAME           IN VARCHAR2,	--REQUIRED
   P_LIMIT_DATE_COLUMN 	IN VARCHAR2,	--REQUIRED
   P_BEG_DATE   	IN DATE DEFAULT SYSDATE,
   P_WHERE_LIST         IN VARCHAR2,
   P_ORDER_BY          	IN VARCHAR2,	--REQUIRED
   P_EMAIL_GROUP        IN VARCHAR2 DEFAULT 'ADR_DBA')

AS
  /* ========================================================================== */
  /* Description: CCR 1652: ADR JOB Monitoring					*/
  /*										*/
  /* This store procedure is to monitor 2 tables JOB_PROCESS_LOGS and 	      	*/
  /* JOB_PROCESS_ERRS and send out notifications via utl_mail to ADR_DBA group	*/
  /* The output layout will limit up to 30 rows display on email. There are 7 	*/
  /* passing arguments:                                                       	*/
  /*                                           					*/
  /* P_COL_LIST:  lists of columns to be displayed in the body.  The first    	*/
  /*          column must be the job name.  Limit up to 5 columns.         	*/
  /*										*/
  /* P_TAB_NAME:  table to be monitored.                      			*/
  /*										*/
  /* P_LIMIT_DATE_COLUMN: Emter column to be limited by query.  For example	*/
  /* 										*/
  /* P_BEG_DATE: Monitoring date and goes back to xxx days of one of the inputs	*/
  /*										*/
  /* P_WHERE_LIST:lists where clause conditions.                  		*/
  /*										*/
  /* P_ORDER_BY:  list a column to be sorted by.  Limit one column.          	*/
  /*										*/
  /* P_EMAIL_GROUP: email group name which is found in ADR_USER_CONTACT     	*/
  /*										*/
  /* VN_max_num_days_monitored: look up from adr_parameter table		*/
  /*										*/
  /* VN_max_rec_display: Look up from adr_parameter table. Limit up to 30 rows  */
  /*										*/
  /* Sample code:								*/
  /* DRMGT.JOB_MONITOR_SP( P_COL_LIST => 'JOB_NAME, JOB_ERROR_DATE, 	 	*/
  /*  	JOB_ERROR_CODE, JOB_ERROR_MESSAGE', 					*/
  /* 	P_TAB_NAME   => 'ADRMGT.JOB_PROCESS_ERRS', 				*/
  /*  	P_LIMIT_DATE_COLUMN   => 'JOB_ERROR_DATE', 				*/
  /* 	P_BEG_DATE   => SYSDATE,  P_WHERE_LIST => NULL,				*/
  /* 	P_ORDER_BY   => 'JOB_PROCESS_ERRS_ID', 					*/
  /* 	P_EMAIL_GROUP=> 'ADR_DBA');						*/
  /*										*/
  /******************************************************************************/
  -- # Change History
  -- #
  -- # DATE       User Name         DESCRIPTION
  -- # ---------- ----------------- -------------------------------------------------
  -- # 06/26/2012 Richard Do        Created - CCR 1652
  -- #



  -- VARIABLES

  TYPE TABLE_COL_TYPE IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
  VN_STR_LEN        NUMBER:= 100;
  VN_SEPARATOR      CONSTANT VARCHAR2(3):= ' | ';

  VC_WHERE          VARCHAR2(3200);
  LV_Str_List       VARCHAR2(500);
  LB_cnt            BINARY_INTEGER;
  LA_Tab_Str        DBMS_UTILITY.UNCL_ARRAY;

  LC_TMP            VARCHAR2(100);
  LC_err_str        VARCHAR2(32767);
  LC_str            LONG;
  l_row_cnt         BINARY_INTEGER := 0;
  LN_col_count      BINARY_INTEGER := 0;

  VC_log_step       VARCHAR2(500);
  lc_col1           TABLE_COL_TYPE;
  lc_col2           TABLE_COL_TYPE;
  lc_col3           TABLE_COL_TYPE;
  lc_col4           TABLE_COL_TYPE;
  lc_col5           TABLE_COL_TYPE;


  VC_SELECT1         VARCHAR2(60):= 'SELECT * FROM (';
  LC_WHERE_LIMIT     VARCHAR2(2000);
  LN_MAX_ROWS_DISP   NUMBER := 30;  --This number limit number of rows display on the body message
  VN_max_num_days_monitored   NUMBER;
  VN_max_rec_display		NUMBER;


  VC_SQLSTMT         VARCHAR2(5000);

  vMesg              LONG;
  vSubj              VARCHAR2(800);


  VB_allow_rpt_inst        BOOLEAN := FALSE;
  VB_error                BOOLEAN := FALSE;
  VB_run_standalone                    BOOLEAN := FALSE;
  VB_update_row             BOOLEAN := FALSE;

  VC_allow_rpt_inst         VARCHAR2(5) := NULL;

  VC_process_duration       VARCHAR2(25);
  VC_process_name            VARCHAR2(30) := 'ADRMGT.JOB_MONITOR_SP';


  VN_Inst_name             VARCHAR2(60);
  VC_host_name               VARCHAR2(60);
  VC_email_list             VARCHAR2(300);


-- **************************************************************************
-- *
-- *  MAIN PROCEDURE:
-- *
-- *    ADR.Adr_Address_Z05_Update_Sp
-- *
-- **************************************************************************

BEGIN

   VC_log_step := 'Get instance name';

   SELECT sys_context('USERENV','INSTANCE_NAME'), sys_context('USERENV', 'SERVER_HOST')
   into VN_Inst_name, VC_host_name
   FROM dual;

   ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                            PD_job_log_date => SYSDATE,
                            PC_job_status => SUBSTR('INFORMATION - Process Initiation',1,60),
                            PD_date1 => SYSDATE,
                            PC_text1 => VC_log_step||' - Initiating ADR Monitoring Job');


   LV_Str_List  := P_COL_LIST;

   -- ==============================================================================
   -- Obtain value max num days to be monitored from run date
   -- ==============================================================================

   VC_log_step := 'Obtain value max num days to be monitored';

   SELECT VALUE
     INTO VN_max_num_days_monitored
   FROM ADRMGT.ADR_PARAMETER
   WHERE  application_name = 'ADR_JOB_MONITORING'
   AND PARAMETER_NAME = 'DAYS_BE_MONITORED';

   VN_max_num_days_monitored := NVL(VN_max_num_days_monitored,1);  -- set default to 1 day


   -- ===================================================================================
   -- Obtain value max num errors records to be displayed in the body email notification.
   -- ===================================================================================
   VC_log_step := 'Obtain value max num days to be monitored';

   SELECT VALUE
        INTO VN_max_rec_display
   FROM ADRMGT.ADR_PARAMETER
   WHERE  application_name = 'ADR_JOB_MONITORING'
   AND PARAMETER_NAME = 'NUM_ROWS_DISPLAY_MAX';

   VN_max_rec_display := NVL(VN_max_rec_display,30);  -- set max default to 30 rows


   VC_log_step := 'Parsing string column names';

   -- ==============================================================================
   -- Parse the string the column list to cound number of column delimeted by comma
   -- ==============================================================================

   DBMS_UTILITY.COMMA_TO_TABLE(LV_Str_List, LB_cnt, LA_Tab_Str);

   -- ===========================================================
   -- Settup WHERE clause limit to the last 3 days from sysdate
   -- ===========================================================

   LC_WHERE_LIMIT := ' WHERE TRUNC('||P_LIMIT_DATE_COLUMN||') >= TO_DATE('''||P_BEG_DATE||''')-'||VN_max_num_days_monitored;

   DBMS_OUTPUT.PUT_LINE(LC_WHERE_LIMIT);

   IF P_WHERE_LIST IS NOT NULL THEN
     VC_WHERE := LC_WHERE_LIMIT|| ' AND '||
          	P_WHERE_LIST||
          	' ORDER BY '||P_ORDER_BY||' DESC )'||
          	' WHERE ROWNUM <='||VN_max_rec_display;  --Make sure number of displayed rows not exceed 30

   ELSE
      VC_WHERE := LC_WHERE_LIMIT||
 		' ORDER BY '||P_ORDER_BY||' DESC )'||
          	' WHERE ROWNUM <='||VN_max_rec_display;  --Make sure number of displayed rows not exceed 30
   END IF;

   DBMS_OUTPUT.PUT_LINE(VC_WHERE);

   VC_log_step := 'Parsed and count columns number.';
   /****************************************************/
   /*                                                  */
   /* Parse the column list to count number of columns */
   /*                                                  */
   /****************************************************/

   FOR I IN 1 .. LA_Tab_Str.COUNT LOOP
     	-- display substring

     	LC_TMP := TRIM(LA_Tab_Str(I));
     	LN_col_count := I;

     	IF LC_TMP IS NULL THEN
     	   EXIT;
     	END IF;

   END LOOP;

   IF LN_col_count > 1 THEN

   	LN_col_count := LN_col_count - 1;
   ELSE

  	LN_col_count := 1;
   END IF;

   VC_log_step := 'Building VC_SQLSTMT';
   VC_SQLSTMT := VC_SELECT1|| 'SELECT '||P_COL_LIST||'  FROM '||P_TAB_NAME ||' '||VC_WHERE;


   DBMS_OUTPUT.PUT_LINE(VC_SQLSTMT);

   /************************************************************/
   /* Format body email when the column list has only 1 column */
   /************************************************************/

   IF LN_col_count = 1 THEN

       VC_log_step := 'Formatting email body of 1 column.';
       EXECUTE IMMEDIATE VC_SQLSTMT  BULK COLLECT INTO lc_col1 ;


       -- Appending values while looping thru collections
       FOR I in 1..lc_col1.COUNT LOOP

          LC_err_str:=  'Ln# '||I||'- Job Name: '||SUBSTR(TRIM(lc_col1(I)),1,VN_STR_LEN)||CHR(10);

          LC_str := LC_str||CHR(10)||LC_err_str;

          DBMS_OUTPUT.PUT_LINE(LC_str);

          IF LN_MAX_ROWS_DISP < I THEN  -- Make sure displayed rows not exists more than 20

          	EXIT;
          END IF;

       END LOOP;


   /*************************************************************/
   /* Format body email when the column list has only 2 columns */
   /*************************************************************/

   ELSIF LN_col_count = 2 THEN

       VC_log_step := 'Formatting email body of 2 columns.';
       EXECUTE IMMEDIATE VC_SQLSTMT  BULK COLLECT INTO lc_col1, lc_col2;

       -- Appending values while looping thru collections

       FOR I in 1..lc_col1.COUNT LOOP

          LC_err_str:= 'Ln# '||I||'- Job Name: '||SUBSTR(TRIM(lc_col1(I)),1,VN_STR_LEN)||CHR(10)||
            SUBSTR(TRIM(lc_col2(I)),1,VN_STR_LEN);

          LC_str := LC_str||CHR(10)||LC_err_str;


          DBMS_OUTPUT.PUT_LINE(LC_str);


          IF LN_MAX_ROWS_DISP < I THEN  -- Make sure displayed rows not exists more than 20
              EXIT;
          END IF;

       END LOOP;


   /*************************************************************/
   /* Format body email when the column list has only 3 columns */
   /*************************************************************/

   ELSIF LN_col_count = 3 THEN

       VC_log_step := 'Formatting email body of 2 columns.';
       EXECUTE IMMEDIATE VC_SQLSTMT  BULK COLLECT INTO lc_col1, lc_col2, lc_col3 ;

       FOR I in 1..lc_col1.COUNT LOOP

          LC_err_str:=  'Ln# '||I||'- Job Name: '||SUBSTR(TRIM(lc_col1(I)),1,VN_STR_LEN)||CHR(10)||
           SUBSTR(TRIM(lc_col2(I)),1,VN_STR_LEN)|| VN_SEPARATOR|| SUBSTR(TRIM(lc_col3(I)),1,VN_STR_LEN);

          LC_str := LC_str||CHR(10)||LC_err_str;
          DBMS_OUTPUT.PUT_LINE(LC_err_str) ;

          IF LN_MAX_ROWS_DISP < I THEN  -- Make sure rows displayed not exists more than 20
               EXIT;
          END IF;

       END LOOP;


   /*************************************************************/
   /* Format body email when the column list has only 4 columns */
   /*************************************************************/

   ELSIF LN_col_count = 4 THEN

      VC_log_step := 'Formatting email body of 3 columns.';
      EXECUTE IMMEDIATE VC_SQLSTMT  BULK COLLECT INTO lc_col1, lc_col2, lc_col3,lc_col4;

      FOR I in 1..lc_col1.COUNT LOOP

          LC_err_str:=  'Ln# '||I||'- Job Name: '||SUBSTR(TRIM(lc_col1(I)),1,VN_STR_LEN)||CHR(10)||
           SUBSTR(TRIM(lc_col2(I)),1,VN_STR_LEN)||
            VN_SEPARATOR||SUBSTR(TRIM(lc_col3(I)),1,VN_STR_LEN)||VN_SEPARATOR||SUBSTR(TRIM(lc_col4(I)),1,VN_STR_LEN)||CHR(10);

          LC_str := LC_str||CHR(10)||LC_err_str;

          IF LN_MAX_ROWS_DISP < I THEN  -- Make sure rows displayed not exists more than 20
              EXIT;
          END IF;

      END LOOP;

   /*************************************************************/
   /* Format body email when the column list has only 5 columns */
   /*************************************************************/

   ELSIF LN_col_count = 5 THEN

       VC_log_step := 'Formatting email body of 5 columns.';
       EXECUTE IMMEDIATE VC_SQLSTMT  BULK COLLECT INTO lc_col1, lc_col2, lc_col3,lc_col4, lc_col5 ;

       FOR I in 1..lc_col1.COUNT LOOP

          LC_err_str:=  'Ln# '||I||'- Job Name: '||SUBSTR(TRIM(lc_col1(I)),1,VN_STR_LEN)||CHR(10)||

          SUBSTR(TRIM(lc_col2(I)),1,VN_STR_LEN)|| VN_SEPARATOR||SUBSTR(TRIM(lc_col3(I)),1,VN_STR_LEN)||VN_SEPARATOR||SUBSTR(TRIM(lc_col4(I)),1,VN_STR_LEN)||
                   VN_SEPARATOR||SUBSTR(TRIM(lc_col5(I)),1,VN_STR_LEN);

           LC_str := LC_str||CHR(10)||LC_err_str;
          DBMS_OUTPUT.PUT_LINE(LC_str);

          DBMS_OUTPUT.PUT_LINE('line '||i||': '||lc_col1(i)||'***: '|| lc_col2(i)||'***: '|| lc_col3(i)||'***: '|| lc_col4(i)||lc_col5(i)||'**END');

          IF LN_MAX_ROWS_DISP < I THEN  -- Make sure rows displayed not exists more than 20
                EXIT;
          END IF;


      END LOOP;

   END IF;


   /**************************************************************/
   /* If format email body is empty that means no error found.   */
   /* Therefore, no need to send out notifications.		*/
   /**************************************************************/

   IF LC_str IS NOT NULL THEN

      VC_log_step := 'Retrieving emails from '||p_email_group;

      SELECT EMAIL_ADDRESS_LIST
    	INTO  VC_email_list
      FROM ADRMGT.ADR_USER_CONTACT
      WHERE CONTACT_EMAIL_USER_NAME = p_email_group;


      VC_log_step := 'Constructing subj email.';

      vSubj := VC_process_name||' - Error Found in '||P_TAB_NAME ||'('||VN_Inst_name||'@'||VC_host_name||')';

      VC_log_step := 'Constructing body email.';

      vMesg := 'Runtime Criteria: Max num rows <= '|| LEAST(VN_max_rec_display,LN_MAX_ROWS_DISP)||CHR(10)||
  		VC_WHERE||CHR(10)||
  		'==================================='||CHR(10)||
  		LC_str||  CHR(10)||
     '*************************************************************************************************************'||CHR(10)||
     'ACTION: Please take appropriate action to resolve the errors. Check '||P_TAB_NAME||' for more details.'||CHR(10)||
     '*************************************************************************************************************';


     /*******************************************************************/
     /*									*/
     /* Sending email notifications to groups on errors found.		*/
     /*									*/
     /*******************************************************************/

     VC_log_step := 'Invoking utl_mail.send';

     UTL_MAIL.SEND (SENDER  	=> 'oracle@'||VC_host_name||'aac.DNS   ',
               	    RECIPIENTS 	=> VC_email_list,
                    SUBJECT    	=> vSubj,
                    MESSAGE 	=> vMesg);

     ADRMGT.Job_Process_Log_Sp(
           PC_job_name => SUBSTR(VC_process_name,1,60),
           PD_job_log_date => SYSDATE,
           PC_job_status => SUBSTR('INFORMATION - '||VC_log_step,1,60),
           PC_job_log_message => SUBSTR(VC_log_step,1,2000),
           PD_date1 => SYSDATE,
           PC_text1 => SUBSTR(VC_log_step,1,255));


   ELSE

     VC_log_step := 'No Data Message found.';
     ADRMGT.Job_Process_Log_Sp(
           PC_job_name => SUBSTR(VC_process_name,1,60),
           PD_job_log_date => SYSDATE,
           PC_job_status => SUBSTR('INFORMATION - '||VC_log_step,1,60),
           PC_job_log_message => SUBSTR(VC_log_step,1,2000),
           PD_date1 => SYSDATE,
           PC_text1 => SUBSTR(VC_log_step,1,255));

   END IF;

  EXCEPTION

    WHEN NO_DATA_FOUND THEN

      NULL;

    WHEN OTHERS THEN

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                                PD_job_error_date  => SYSDATE,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unknown error while removing old process logs. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => SYSDATE,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));


END JOB_MONITOR_SP;
/
