set arraysize 1

CREATE OR REPLACE PACKAGE cl IS
  load_running         BOOLEAN := FALSE;
  job_id               data_loads.job_id%TYPE;
  lines_in_file        data_loads.lines_in_file%TYPE := 0;
  data_records_in_file data_loads.data_records_in_file%TYPE := 0;
  message_type         data_load_message_codes.message_type%TYPE := 'INFO';
  lines_with_messages  data_loads.lines_with_messages%TYPE := 0;
  message_count        data_loads.message_count%TYPE := 0;
  lines_with_errors    data_loads.lines_with_errors%TYPE := 0;
  error_count          data_loads.error_count%TYPE := 0;

  FUNCTION Execute_Statement (p_statement VARCHAR2)
    RETURN NUMBER;

  PROCEDURE Log_Load_Event
   (p_load_rec          IN data_loads%ROWTYPE,
    p_event             IN VARCHAR2,  -- (S)tart or (F)inish
    o_successful        IN OUT BOOLEAN,
    o_err_msg           IN OUT VARCHAR2);

  PROCEDURE log_load_message
   (p_load_rec          IN data_load_inbox%ROWTYPE,
    p_message_code      IN data_load_messages.message_code%TYPE,
    p_start_position    IN data_load_messages.field_start_position%TYPE,
    p_length            IN data_load_messages.field_length%TYPE,
    p_data_value        IN data_load_messages.data_value%TYPE,
    p_dest_table        IN data_load_messages.destination_table%TYPE,
    p_dest_col          IN data_load_messages.destination_column%TYPE,
    p_rec_loaded        IN data_load_messages.was_record_loaded%TYPE,
    p_add_info          IN data_load_messages.additional_information%TYPE,
    o_successful        IN OUT BOOLEAN,
    o_err_msg           IN OUT VARCHAR2);

  PROCEDURE Insert_Report_Line
   (p_interface_name data_load_reports.interface_name%TYPE,
    p_job_id         data_load_reports.job_id%TYPE,
    p_line_text      data_load_reports.line_text%TYPE,
    p_line_no        data_load_reports.line_no%TYPE := NULL);

  PROCEDURE Report_Statistics
   (p_interface_name IN data_loads.interface_name%TYPE,
    p_job_id         IN data_loads.job_id%TYPE);

  PROCEDURE Report_Messages
   (p_interface_name IN data_loads.interface_name%TYPE,
    p_job_id         IN data_loads.job_id%TYPE);

END cl;
/

SHOW ERRORS PACKAGE cl;
CREATE OR REPLACE PACKAGE BODY cl IS

  FUNCTION Execute_Statement(p_statement IN VARCHAR2)
    RETURN NUMBER IS
    v_cursor     INTEGER := dbms_sql.open_cursor;
    rows_processed  NUMBER  := 0;
  BEGIN
      DBMS_SQL.PARSE(v_cursor, p_statement, DBMS_SQL.NATIVE );
      rows_processed := DBMS_SQL.EXECUTE(v_cursor);
      DBMS_SQL.CLOSE_CURSOR(v_cursor);
      RETURN rows_processed;
  EXCEPTION
      WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(v_cursor) THEN
          DBMS_SQL.CLOSE_CURSOR(v_cursor);
        END IF;
        RAISE;
  END;

  --
  -- Log the start or finish of a data load
  -- Also used to update the status of a data load
  --
  PROCEDURE Log_Load_Event
   (p_load_rec          IN data_loads%ROWTYPE,
    p_event             IN VARCHAR2,  -- (S)tart or (F)inish
    o_successful        IN OUT BOOLEAN,
    o_err_msg           IN OUT VARCHAR2) IS
  BEGIN
    o_successful := TRUE;
    IF (p_event = 'S') THEN
      BEGIN
        INSERT INTO data_loads
         (interface_name,
          job_id,
          status,
          lines_in_file,
          data_records_in_file,
          lines_with_messages,
          message_count,
          lines_with_errors,
          error_count,
          load_started
         )
        VALUES
         (p_load_rec.interface_name,
          p_load_rec.job_id,
          p_load_rec.status,
          NVL(p_load_rec.lines_in_file,0),
          NVL(p_load_rec.data_records_in_file,0),
          NVL(p_load_rec.lines_with_messages,0),
          NVL(p_load_rec.message_count,0),
          NVL(p_load_rec.lines_with_errors,0),
          NVL(p_load_rec.error_count,0),
          SYSDATE
         );
      EXCEPTION
        WHEN OTHERS THEN
          o_err_msg := 'cl.log_load_event insert: '||SQLERRM;
          o_successful := FALSE;
      END;    
    ELSE
      BEGIN
        UPDATE data_loads
        SET status                = DECODE(p_load_rec.status, NULL, status, p_load_rec.status),
            lines_in_file         = DECODE(p_load_rec.lines_in_file, NULL, lines_in_file, p_load_rec.lines_in_file),
            data_records_in_file  = DECODE(p_load_rec.data_records_in_file, NULL, data_records_in_file, p_load_rec.data_records_in_file),
            lines_with_messages   = DECODE(p_load_rec.lines_with_messages, NULL, lines_with_messages, p_load_rec.lines_with_messages),
            message_count         = DECODE(p_load_rec.message_count, NULL, message_count, p_load_rec.message_count),
            lines_with_errors     = DECODE(p_load_rec.lines_with_errors, NULL, lines_with_errors, p_load_rec.lines_with_errors),
            error_count           = DECODE(p_load_rec.error_count, NULL, error_count, p_load_rec.error_count),
            load_finished         = DECODE(p_load_rec.load_finished, NULL, load_finished, p_load_rec.load_finished)
        WHERE interface_name      = p_load_rec.interface_name
          AND job_id              = p_load_rec.job_id;
      EXCEPTION
        WHEN OTHERS THEN
          o_err_msg := 'cl.load_event update: '||SQLERRM;
          o_successful := FALSE;
      END;    
    END IF;
  END log_load_event;
  --
  -- Log a load message
  --
  PROCEDURE log_load_message
   (p_load_rec          IN data_load_inbox%ROWTYPE,
    p_message_code      IN data_load_messages.message_code%TYPE,
    p_start_position    IN data_load_messages.field_start_position%TYPE,
    p_length            IN data_load_messages.field_length%TYPE,
    p_data_value        IN data_load_messages.data_value%TYPE,
    p_dest_table        IN data_load_messages.destination_table%TYPE,
    p_dest_col          IN data_load_messages.destination_column%TYPE,
    p_rec_loaded        IN data_load_messages.was_record_loaded%TYPE,
    p_add_info          IN data_load_messages.additional_information%TYPE,
    o_successful        IN OUT BOOLEAN,
    o_err_msg           IN OUT VARCHAR2) IS
    CURSOR chk_data_load_records IS
      SELECT null
        FROM data_load_records
       WHERE interface_name = p_load_rec.interface_name
         AND job_id         = p_load_rec.job_id
         AND line_no        = p_load_rec.line_no;
    CURSOR chk_message_code IS
      SELECT message_type
        FROM data_load_message_codes
       WHERE message_code = p_message_code;
    CURSOR chk_msg_type_cnt(p_msg_type VARCHAR2) IS
      SELECT COUNT(*)
        FROM data_load_messages dlm, data_load_message_codes dlmc
       WHERE interface_name    = p_load_rec.interface_name
         AND job_id            = p_load_rec.job_id
         AND line_no           = p_load_rec.line_no
         AND dlm.message_code  = dlmc.message_code
         AND dlmc.message_type = p_msg_type;
         
    v_dummy VARCHAR2(1);
    v_message_type data_load_message_codes.message_type%TYPE;
    v_count INTEGER;
  BEGIN
    o_successful := TRUE;
    OPEN chk_message_code;
    FETCH chk_message_code INTO v_message_type;
    IF (chk_message_code%NOTFOUND) THEN
      CLOSE chk_message_code;
      o_err_msg := 'cl.log_load_message: undefined message code '''||p_message_code||'''';
      o_successful := FALSE;
    ELSE
      CLOSE chk_message_code;
      OPEN chk_data_load_records;
      FETCH chk_data_load_records INTO v_dummy;
      -- If record does not have prior message of any type
      IF (chk_data_load_records%NOTFOUND) THEN
        INSERT INTO data_load_records
         (interface_name,
          job_id,
          line_no,
          line_text)
        VALUES
         (p_load_rec.interface_name,
          p_load_rec.job_id,
          p_load_rec.line_no,
          p_load_rec.line_text);
        IF (v_message_type = cl.message_type) THEN
          cl.message_count := cl.message_count + 1;
          cl.lines_with_messages := cl.lines_with_messages + 1;
        ELSE
          cl.error_count := cl.error_count + 1;
          cl.lines_with_errors := cl.lines_with_errors + 1;
        END IF;
      ELSE
        -- Get count of prior messages of same type
        OPEN chk_msg_type_cnt(v_message_type);
        FETCH chk_msg_type_cnt INTO v_count;
        CLOSE chk_msg_type_cnt;
        IF (v_count = 0) THEN  -- No pior messages of this type
          IF (v_message_type = cl.message_type) THEN
            cl.lines_with_messages := cl.lines_with_messages + 1;
          ELSE
            cl.lines_with_errors := cl.lines_with_errors + 1;
          END IF;
        END IF;
        IF (v_message_type = cl.message_type) THEN
          cl.message_count := cl.message_count + 1;
        ELSE
          cl.error_count := cl.error_count + 1;
        END IF;
      END IF;
      CLOSE chk_data_load_records;
      INSERT INTO data_load_messages
       (interface_name,
        job_id,
        line_no,
        message_code,
        field_start_position,
        field_length,
        data_value,
        destination_table,
        destination_column,
        was_record_loaded,
        additional_information)
      VALUES
       (p_load_rec.interface_name,
        p_load_rec.job_id,
        p_load_rec.line_no,
        p_message_code,
        p_start_position,
        p_length,
        p_data_value,
        p_dest_table,
        p_dest_col,
        p_rec_loaded,
        p_add_info);
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      o_err_msg := 'cl.log_load_message: '||SQLERRM;
      o_successful := FALSE;
  END log_load_message;

  PROCEDURE Insert_Report_Line
   (p_interface_name data_load_reports.interface_name%TYPE,
    p_job_id         data_load_reports.job_id%TYPE,
    p_line_text      data_load_reports.line_text%TYPE,
    p_line_no        data_load_reports.line_no%TYPE := NULL) IS

    CURSOR get_line_no IS
      SELECT NVL(MAX(line_no),0) + 1
        FROM data_load_reports
       WHERE interface_name = p_interface_name
         AND job_id = p_job_id;
    CURSOR chk_line_no IS
      SELECT 'X'
        FROM data_load_reports
       WHERE interface_name = p_interface_name
         AND job_id = p_job_id
         AND line_no = p_line_no;
    v_line_no data_load_reports.line_no%TYPE;
    v_dummy VARCHAR2(1);
  BEGIN
    IF (p_line_no IS NULL) THEN
      OPEN get_line_no;
      FETCH get_line_no INTO v_line_no;
      CLOSE get_line_no;
      INSERT INTO data_load_reports
       (interface_name, job_id, line_no, line_text)
      VALUES
       (p_interface_name, p_job_id, v_line_no, p_line_text);
    ELSE
      OPEN chk_line_no;
      FETCH chk_line_no INTO v_dummy;
      IF (chk_line_no%FOUND) THEN
        UPDATE data_load_reports
           SET line_text = p_line_text
         WHERE interface_name = p_interface_name
           AND job_id = p_job_id
           AND line_no = p_line_no;
      ELSE
        INSERT INTO data_load_reports
         (interface_name, job_id, line_no, line_text)
        VALUES
         (p_interface_name, p_job_id, p_line_no, p_line_text);
      END IF;
      CLOSE chk_line_no;
    END IF;
  END;

  PROCEDURE Report_Statistics
   (p_interface_name IN data_loads.interface_name%TYPE,
    p_job_id         IN data_loads.job_id%TYPE)
  IS
    CURSOR get_info IS
      SELECT *
        FROM data_loads
       WHERE interface_name = p_interface_name
         AND job_id = p_job_id;
    v_load_info get_info%ROWTYPE;
    v_indent    INTEGER := 30;
    v_line_text data_load_reports.line_text%TYPE;
  BEGIN
    OPEN get_info;
    FETCH get_info INTO v_load_info;
    CLOSE get_info;
    v_line_text := RPAD('Interface:',v_indent)||v_load_info.interface_name;
    cl.Insert_Report_Line(v_load_info.interface_name, v_load_info.job_id, v_line_text);
    v_line_text := RPAD('Job ID:',v_indent)||v_load_info.job_id;
    cl.Insert_Report_Line(v_load_info.interface_name, v_load_info.job_id, v_line_text);
    v_line_text := RPAD('Load Started:',v_indent)||TO_CHAR(v_load_info.load_started,'DD-MON-YYYY HH24:MI:SS');
    cl.Insert_Report_Line(v_load_info.interface_name, v_load_info.job_id, v_line_text);
    v_line_text := RPAD('Load Finished:',v_indent)||TO_CHAR(v_load_info.load_finished,'DD-MON-YYYY HH24:MI:SS');
    cl.Insert_Report_Line(v_load_info.interface_name, v_load_info.job_id, v_line_text);
    v_line_text := RPAD('Load Status:',v_indent)||v_load_info.status;
    cl.Insert_Report_Line(v_load_info.interface_name, v_load_info.job_id, v_line_text);
    v_line_text := RPAD('Lines in file:',v_indent)||LTRIM(TO_CHAR(v_load_info.lines_in_file,'099,999,999'));
    cl.Insert_Report_Line(v_load_info.interface_name, v_load_info.job_id, v_line_text);
    v_line_text := RPAD('Data records in file:',v_indent)||LTRIM(TO_CHAR(v_load_info.data_records_in_file,'099,999,999'));
    cl.Insert_Report_Line(v_load_info.interface_name, v_load_info.job_id, v_line_text);
    v_line_text := RPAD('Lines with errors:',v_indent)||LTRIM(TO_CHAR(v_load_info.lines_with_errors,'099,999,999'));
    cl.Insert_Report_Line(v_load_info.interface_name, v_load_info.job_id, v_line_text);
    v_line_text := RPAD('Total error count:',v_indent)||LTRIM(TO_CHAR(v_load_info.error_count,'099,999,999'));
    cl.Insert_Report_Line(v_load_info.interface_name, v_load_info.job_id, v_line_text);
    v_line_text := RPAD('Lines with messages:',v_indent)||LTRIM(TO_CHAR(v_load_info.lines_with_messages,'099,999,999'));
    cl.Insert_Report_Line(v_load_info.interface_name, v_load_info.job_id, v_line_text);
    v_line_text := RPAD('Total message count:',v_indent)||LTRIM(TO_CHAR(v_load_info.message_count,'099,999,999'));
    cl.Insert_Report_Line(v_load_info.interface_name, v_load_info.job_id, v_line_text);
  END Report_Statistics;

  PROCEDURE Report_Messages
   (p_interface_name IN data_loads.interface_name%TYPE,
    p_job_id         IN data_loads.job_id%TYPE)
  IS
    CURSOR msg_codes IS
      SELECT dlm.message_code,
             dlmc.message_type,
             dlmc.description,
             COUNT(*) cnt
        FROM data_load_messages dlm,
             data_load_message_codes dlmc
       WHERE dlm.interface_name = p_interface_name
         AND dlm.job_id         = p_job_id
         AND dlm.message_code   = dlmc.message_code
       GROUP BY dlm.message_code,
                dlmc.message_type,
                dlmc.description
       ORDER BY dlmc.message_type,
                dlmc.description;
    CURSOR msg_details (p_msg_code VARCHAR2) IS
      SELECT NVL(data_value,additional_information) info,
             COUNT(*) cnt
        FROM data_load_messages dlm
       WHERE dlm.interface_name = p_interface_name
         AND dlm.job_id         = p_job_id
         AND dlm.message_code   = p_msg_code
      GROUP BY NVL(data_value,additional_information)
      ORDER BY COUNT(*) DESC;
    v_indent    INTEGER := 30;
    v_line_text data_load_reports.line_text%TYPE;
    v_cnt INTEGER;
  BEGIN
    v_line_text := ' ';
    cl.Insert_Report_Line(p_interface_name, p_job_id, v_line_text);
    v_line_text := RPAD('Message',41)||RPAD('Occurrences',12)||'      %';
    cl.Insert_Report_Line(p_interface_name, p_job_id, v_line_text);
    v_line_text := RPAD('-',40,'-')||' '||RPAD('-',11,'-')||' -------';
    cl.Insert_Report_Line(p_interface_name, p_job_id, v_line_text);
    FOR msg_co_rec IN msg_codes LOOP
      v_line_text := RPAD(SUBSTR(msg_co_rec.message_type||':'||msg_co_rec.description,1,40),40)||
                     RPAD(TO_CHAR(msg_co_rec.cnt,'999,999,999'),12)||' '||
                     TO_CHAR(ROUND((msg_co_rec.cnt/cl.data_records_in_file)*100,2),'999.99');
      cl.Insert_Report_Line(p_interface_name, p_job_id, v_line_text);
      v_cnt := 0;
      FOR msg_detail_rec IN msg_details(msg_co_rec.message_code) LOOP
        v_cnt := v_cnt + 1;
        v_line_text := RPAD(TO_CHAR(v_cnt,'9,999')||'.  '''||msg_detail_rec.info||'''',40)||
                       TO_CHAR(msg_detail_rec.cnt,'999,999,999')||' '||
                       TO_CHAR(ROUND((msg_detail_rec.cnt/cl.data_records_in_file)*100,2),'999.99');
        cl.Insert_Report_Line(p_interface_name, p_job_id, v_line_text);
      END LOOP;
    END LOOP;
  END Report_Messages;

END cl;
/

SHOW ERRORS PACKAGE BODY cl;