DROP PACKAGE BODY ADR.ADR_UTIL;

CREATE OR REPLACE PACKAGE BODY ADR.adr_util
AS
  CURSOR adr_seq IS select owner, object_name
                      from dba_objects
                     where owner in ('ADR', 'PSD', 'PSIM')
                       and object_type = 'SEQUENCE'
                  order by owner, object_name;

  CURSOR adr_trig IS select owner, object_name
                       from dba_objects
                      where owner in ('ADR', 'PSD', 'PSIM')
                        and object_type = 'TRIGGER'
                   order by owner, object_name;


  obj      varchar2(50);


/***************************************************************************
 *
 *    NAME
 *      ADR_PIN_SEQ
 *
 *    DESCRIPTION
 *      ADR_PIN_SEQ marks all ADR, PSIM and PSD sequences as objects to be
 *      kept in the shared pool memory.
 *
 */

  PROCEDURE ADR_PIN_SEQ IS
  BEGIN
    FOR fnd_seq IN adr_seq LOOP
      obj := fnd_seq.owner ||'.'|| fnd_seq.object_name;
      DBMS_SHARED_POOL.KEEP(obj,'Q');
    END LOOP;

  END ADR_PIN_SEQ;

/***************************************************************************
 *
 *    NAME
 *      ADR_UNPIN_SEQ
 *
 *    DESCRIPTION
 *      ADR_UNPIN_SEQ marks all ADR, PSIM and PSD sequences as objects to be
 *      not kept in the shared pool memory.
 *
 */

  PROCEDURE ADR_UNPIN_SEQ IS
  BEGIN
    FOR fnd_seq IN adr_seq LOOP
      obj := fnd_seq.owner ||'.'|| fnd_seq.object_name;
      DBMS_SHARED_POOL.UNKEEP(obj,'Q');
    END LOOP;

  END ADR_UNPIN_SEQ;

/***************************************************************************
 *
 *    NAME
 *      ADR_PIN_TRIGGERS
 *
 *    DESCRIPTION
 *      ADR_PIN_TRIGGERS marks all ADR, PSIM and PSD triggers as objects
 *      to be kept in the shared pool memory.
 *
 */

  PROCEDURE ADR_PIN_TRIGGERS IS
  BEGIN
    FOR fnd_trig IN adr_trig LOOP
      obj := fnd_trig.owner ||'.'|| fnd_trig.object_name;
      DBMS_SHARED_POOL.KEEP(obj,'R');
    END LOOP;

  END ADR_PIN_TRIGGERS;

/***************************************************************************
 *
 *    NAME
 *      ADR_UNPIN_TRIGGERS
 *
 *    DESCRIPTION
 *      ADR_UNPIN_TRIGGERS marks all ADR, PSIM and PSD triggers as objects
 *      to be not kept in the shared pool memory.
 *
 */

  PROCEDURE ADR_UNPIN_TRIGGERS IS
  BEGIN
    FOR fnd_trig IN adr_trig LOOP
      obj := fnd_trig.owner ||'.'|| fnd_trig.object_name;
      DBMS_SHARED_POOL.UNKEEP(obj,'R');
    END LOOP;

  END ADR_UNPIN_TRIGGERS;

END adr_util;
/


CREATE OR REPLACE PUBLIC SYNONYM ADR_UTIL FOR ADR.ADR_UTIL;


GRANT EXECUTE ON ADR.ADR_UTIL TO AACDBA;
DROP PACKAGE BODY ADR.HL7_ARCHIVE_PKG;

CREATE OR REPLACE PACKAGE BODY ADR.Hl7_Archive_Pkg
AS

/*****************************************************************************
 *
 *    NAME: Hl7_Archive_Control
 *    TYPE: Function
 *            RETURN VALUE: VARCHAR2
 *
 *    DESCRIPTION
 *      Used to request that the HL7 archiving job in the Oracle job scheduler
 *      either starts or stops processing.
 *
 *      It sets the Value column in the ADR.Adr_Parameter table
 *      (Application_name = HL7_ARCHIVE_CONTROL,
 *       Parameter_name = START_STOP_PROCESS) to either 0 (stop processing) or
 *      1 (start processing.
 *
 *****************************************************************************
 */

FUNCTION Hl7_Archive_Control
  (PN_status_code
    IN     NUMBER)
  RETURN VARCHAR2
IS

  CC_msg_already_started             CONSTANT VARCHAR2(255) := 'Archive process start has already been requested';
  CC_msg_already_stopped             CONSTANT VARCHAR2(255) := 'Archive process stop has already been requested';
  CC_msg_input_parm_invalid          CONSTANT VARCHAR2(255) := 'ERROR - Invalid function input parameter. Function input must be 0 or 1.';
  CC_msg_input_unknown_reqst         CONSTANT VARCHAR2(255) := 'ERROR - Unknown request.';
  CC_msg_start_success               CONSTANT VARCHAR2(255) := 'Archive start request completed successfully';
  CC_msg_stop_success                CONSTANT VARCHAR2(255) := 'Archive stop request completed successfully';
  CC_msg_no_param_row                CONSTANT VARCHAR2(255) := 'ERROR - No HL7 archive parameter found in table ADRMGT.Adr_Parameter. Request failed.';


  VC_status_msg                      VARCHAR2(255);

  VN_row_count                       NUMBER;
  VN_curr_status_code                NUMBER(1);
  VN_new_status_code                 NUMBER(1);

BEGIN

  IF PN_status_code NOT IN (0,1) THEN

    VC_status_msg := CC_msg_input_parm_invalid;

  ELSE

    VN_new_status_code := PN_status_code;

    SELECT    COUNT(*)
    INTO      VN_row_count
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_ARCHIVE_CONTROL'
    AND       Parameter_name = 'START_STOP_PROCESS';

    IF VN_row_count = 0 THEN

      VC_status_msg := CC_msg_no_param_row;

    ELSE

      SELECT    AP.Value
      INTO      VN_curr_status_code
      FROM      Adr_Parameter AP
      WHERE     Application_name = 'HL7_ARCHIVE_CONTROL'
      AND       Parameter_name = 'START_STOP_PROCESS';

      IF VN_new_status_code = 0
       AND VN_curr_status_code = 0 THEN -- Stop requested, already stopped

        VC_status_msg := CC_msg_already_stopped;

      ELSIF VN_new_status_code = 1
       AND VN_curr_status_code = 1 THEN -- Start requested, already started

        VC_status_msg := CC_msg_already_started;

      ELSIF (VN_new_status_code = 0
            AND VN_curr_status_code = 1) -- Stop requested, valid request
       OR (VN_new_status_code = 1
          AND VN_curr_status_code = 0) THEN -- Start requested, valid request

        BEGIN

          UPDATE    Adr_Parameter AP
          SET       AP.Value = VN_new_status_code,
                    AP.Record_modified_date = SYSDATE,
                    AP.Record_modified_by = 'Start_Stop_Process'
          WHERE     AP.Application_name = 'HL7_ARCHIVE_CONTROL'
          AND       AP.Parameter_name = 'START_STOP_PROCESS';

          COMMIT;

          IF VN_new_status_code = 0 THEN

            VC_status_msg := CC_msg_stop_success;

          ELSE

           VC_status_msg := CC_msg_start_success;

          END IF;

        EXCEPTION

          WHEN OTHERS THEN

            VC_status_msg := SUBSTR('ERROR - Unable to update status to '
                                    || VN_new_status_code
                                    || ' in table ADRMGT.Adr_Parameter - ' || SQLERRM,1,254);

        END;

      ELSE

        VC_status_msg := CC_msg_input_unknown_reqst
                         || ' Input parameter: ' || VN_new_status_code
                         || ' Table parameter: ' || VN_curr_status_code;

      END IF;

    END IF;

  END IF;

  RETURN(VC_status_msg);

EXCEPTION

  WHEN OTHERS THEN

    VC_status_msg := SUBSTR('ERROR - Archive start/stop request cannot be completed - ' || SQLERRM,1,254);

END Hl7_Archive_Control;

/*****************************************************************************
 *
 *    NAME: Hl7_Delete_Control
 *    TYPE: Function
 *            RETURN VALUE: VARCHAR2
 *
 *    DESCRIPTION
 *      Used to request that the HL7 archive delete job in the Oracle job
 *      scheduler either starts or stops processing.
 *
 *      It sets the Value column in the ADR.Adr_Parameter table
 *      (Application_name = HL7_DELETE_CONTROL,
 *       Parameter_name = START_STOP_PROCESS) to either 0 (stop processing) or
 *      1 (start processing.
 *
 *****************************************************************************
 */

FUNCTION Hl7_Delete_Control
  (PN_status_code
    IN     NUMBER)
  RETURN VARCHAR2
IS

  CC_msg_already_started             CONSTANT VARCHAR2(255) := 'Delete process start has already been requested';
  CC_msg_already_stopped             CONSTANT VARCHAR2(255) := 'Delete process stop has already been requested';
  CC_msg_input_parm_invalid          CONSTANT VARCHAR2(255) := 'ERROR - Invalid function input parameter. Function input must be 0 or 1.';
  CC_msg_input_unknown_reqst         CONSTANT VARCHAR2(255) := 'ERROR - Unknown request.';
  CC_msg_start_success               CONSTANT VARCHAR2(255) := 'Delete start request completed successfully';
  CC_msg_stop_success                CONSTANT VARCHAR2(255) := 'Delete stop request completed successfully';
  CC_msg_no_param_row                CONSTANT VARCHAR2(255) := 'ERROR - No HL7 delete parameter found in table ADRMGT.Adr_Parameter. Request failed.';


  VC_status_msg                      VARCHAR2(255);

  VN_row_count                       NUMBER;
  VN_curr_status_code                NUMBER(1);
  VN_new_status_code                 NUMBER(1);

BEGIN

  IF PN_status_code NOT IN (0,1) THEN

    VC_status_msg := CC_msg_input_parm_invalid;

  ELSE

    VN_new_status_code := PN_status_code;

    SELECT    COUNT(*)
    INTO      VN_row_count
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_DELETE_CONTROL'
    AND       Parameter_name = 'START_STOP_PROCESS';

    IF VN_row_count = 0 THEN

      VC_status_msg := CC_msg_no_param_row;

    ELSE

      SELECT    AP.Value
      INTO      VN_curr_status_code
      FROM      Adr_Parameter AP
      WHERE     Application_name = 'HL7_DELETE_CONTROL'
      AND       Parameter_name = 'START_STOP_PROCESS';

      IF VN_new_status_code = 0
       AND VN_curr_status_code = 0 THEN -- Stop requested, already stopped

        VC_status_msg := CC_msg_already_stopped;

      ELSIF VN_new_status_code = 1
       AND VN_curr_status_code = 1 THEN -- Start requested, already started

        VC_status_msg := CC_msg_already_started;

      ELSIF (VN_new_status_code = 0
            AND VN_curr_status_code = 1) -- Stop requested, valid request
       OR (VN_new_status_code = 1
          AND VN_curr_status_code = 0) THEN -- Start requested, valid request

        BEGIN

          UPDATE    Adr_Parameter AP
          SET       AP.Value = VN_new_status_code,
                    AP.Record_modified_date = SYSDATE,
                    AP.Record_modified_by = 'Start_Stop_Process'
          WHERE     AP.Application_name = 'HL7_DELETE_CONTROL'
          AND       AP.Parameter_name = 'START_STOP_PROCESS';

          COMMIT;

          IF VN_new_status_code = 0 THEN

            VC_status_msg := CC_msg_stop_success;

          ELSE

           VC_status_msg := CC_msg_start_success;

          END IF;

        EXCEPTION

          WHEN OTHERS THEN

            VC_status_msg := SUBSTR('ERROR - Unable to update status to '
                                    || VN_new_status_code
                                    || ' in table ADRMGT.Adr_Parameter - ' || SQLERRM,1,254);

        END;

      ELSE

        VC_status_msg := CC_msg_input_unknown_reqst
                         || ' Input parameter: ' || VN_new_status_code
                         || ' Table parameter: ' || VN_curr_status_code;

      END IF;

    END IF;

  END IF;

  RETURN(VC_status_msg);

EXCEPTION

  WHEN OTHERS THEN

    VC_status_msg := SUBSTR('ERROR - Delete start/stop request cannot be completed - ' || SQLERRM,1,254);

END Hl7_Delete_Control;

/*****************************************************************************
 *
 *    NAME: Hl7_Process_Archives
 *    TYPE: Procedure
 *
 *    DESCRIPTION
 *      Used to move rows from the Hl7_Transaction_Log table to the
 *      Hl7_Archive_Transaction_log table based on customer defined archiving
 *      criteria.
 *
 *    PARAMETERS
 *      PB_run_standalone - Allows this procedure to run outside of the Oracle
 *                          DBMS_JOB scheduler.
 *      PB_allow_rpt_inst - Allows this procedure to run on reporting
 *                          databases (ie. ADRRP)
 *
 *****************************************************************************
 */

PROCEDURE Hl7_Process_Archives
  (PB_run_standalone
     IN     BOOLEAN DEFAULT FALSE,
   PB_allow_rpt_inst
     IN     BOOLEAN DEFAULT FALSE
  )
IS

-- CONSTANTS

  CC_process_name                      VARCHAR2(60) := 'HL7 Archiver - Archive HL7 Rows';

  CC_stat_type_complete                CONSTANT NUMBER := 1506921;
  CC_stat_type_uncomp_wait_tran        CONSTANT NUMBER := 1506922;
  CC_stat_type_uncomp_wait_ack         CONSTANT NUMBER := 1506923;
  CC_stat_type_uncomp_pending          CONSTANT NUMBER := 1506924;
  CC_stat_type_uncomp_retrans          CONSTANT NUMBER := 1506925;
  CC_stat_type_uncomp_not_trans        CONSTANT NUMBER := 1506926;
  CC_stat_type_uncomp_error            CONSTANT NUMBER := 1506927;
  CC_stat_type_uncomp_rtran_fail       CONSTANT NUMBER := 1506928;

  CC_tran_type_orfz10s                 CONSTANT NUMBER := 1510423;
  CC_tran_type_orfz11s                 CONSTANT NUMBER := 1510425;  -- CCR 1788
  CC_tran_type_oruz04s                 CONSTANT NUMBER := 1510420;
  CC_tran_type_oruz05e                 CONSTANT NUMBER := 1510407;  -- CCR 1788
  CC_tran_type_oruz05s                 CONSTANT NUMBER := 1510421;
  CC_tran_type_oruz10s                 CONSTANT NUMBER := 1510422;
  CC_tran_type_oruz11e                 CONSTANT NUMBER := 1510408;
  CC_tran_type_oruz11s                 CONSTANT NUMBER := 1510424;
  CC_tran_type_qryz10e                 CONSTANT NUMBER := 1510403;
  CC_tran_type_qryz11e                 CONSTANT NUMBER := 1510435;  -- CCR 1788

-- VARIABLES

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

  VC_allow_rpt_inst                    VARCHAR2(5) := NULL;
  VC_instance_name                     VARCHAR2(16) := NULL;
  VC_process_duration                  VARCHAR2(50) := NULL;
  VC_process_msg                       VARCHAR2(500) := NULL;
  VC_run_standalone                    VARCHAR2(5) := NULL;

  VD_log_date                          DATE;
  VD_process_end_date                  DATE;
  VD_process_start_date                DATE;

  VI_rows_to_keep_count                BINARY_INTEGER;

  VN_archive_count                     NUMBER := 0;
  VN_current_person_id                 NUMBER := 0;
  VN_delete_count                      NUMBER := 0;
  VN_institution_id                    NUMBER := 0;
  VN_insert_count                      NUMBER := 0;
  VN_job_count                         NUMBER := 0;
  VN_log_age_to_keep                   NUMBER := 180;
  VN_msg_status_id                     NUMBER;
  VN_msg_transtype_id                  NUMBER;
  VN_parm_keep_uncompleted_days        NUMBER := 30;
  VN_parm_keep_orfz10s_count           NUMBER := 9;
  VN_parm_keep_orfz11s_count           NUMBER := 4;  -- CCR 1788
  VN_parm_keep_oruz04s_count           NUMBER := 4;
  VN_parm_keep_oruz05e_count           NUMBER := 2;  -- CCR 1788
  VN_parm_keep_oruz05s_count           NUMBER := 9;
  VN_parm_keep_oruz10s_count           NUMBER := 9;
  VN_parm_keep_oruz11e_count           NUMBER := 8;
  VN_parm_keep_oruz11s_count           NUMBER := 9;
  VN_parm_keep_qryz10e_count           NUMBER := 9;
  VN_parm_keep_qryz11e_count           NUMBER := 4;  -- CCR 1788
  VN_person_count                      NUMBER := 0;
  VN_previous_person_id                NUMBER := 0;
  VN_process_control                   NUMBER := 0;
  VN_process_days                      NUMBER;
  VN_process_hours                     NUMBER;
  VN_process_minutes                   NUMBER;
  VN_process_seconds                   NUMBER;
  VN_process_time_in_secs              NUMBER;

-- RECORD DEFINITIONS

  TYPE TR_hl7_txn_rec IS RECORD
    (Hl7_transaction_log_id            ADR.Hl7_Transaction_Log.Hl7_transaction_log_id%TYPE,
     Record_created_date               ADR.Hl7_Transaction_Log.Record_created_date%TYPE);

-- TABLE DEFINITIONS

  TYPE TT_number_table IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

  TYPE TT_hl7_txn IS TABLE OF TR_hl7_txn_rec
    INDEX BY BINARY_INTEGER;

--TABLES

  T_institution_ids                    TT_number_table;

  T_status_types                       TT_number_table;

  T_trans_types                        TT_number_table;

  T_hl7_txns                           TT_hl7_txn;

-- CURSORS

  CURSOR C_inst_id_rec IS
    SELECT    DISTINCT Std_institution_id
    FROM      Hl7_Transaction_Log
    WHERE     Person_id = VN_current_person_id
    ORDER BY  Std_institution_id;

  CURSOR C_msg_status_rec IS
    SELECT    DISTINCT Hl7_message_status_type_id
    FROM      Hl7_Transaction_Log
    WHERE     Person_id = VN_current_person_id
    AND       Std_Institution_Id = VN_institution_id
    ORDER BY  Hl7_message_status_type_id;

  CURSOR C_msg_transtype_rec IS
    SELECT    DISTINCT Message_transmission_type_id
    FROM      Hl7_Transaction_Log
    WHERE     Person_id = VN_current_person_id
    AND       Std_Institution_Id = VN_institution_id
    AND       Hl7_message_status_type_id = VN_msg_status_id
    ORDER BY  Message_transmission_type_id;

  CURSOR C_hl7_txn_rec IS
    SELECT    Hl7_transaction_log_id,
              Record_Created_date
    FROM      Hl7_Transaction_Log
    WHERE     Person_id = VN_current_person_id
    AND       Std_Institution_Id = VN_institution_id
    AND       Hl7_message_status_type_id = VN_msg_status_id
    AND       Message_transmission_type_id = VN_msg_transtype_id
    ORDER BY  Record_Created_date ASC,
              Hl7_transaction_log_id ASC;

  CURSOR C_comp_oruz11e_rec IS
    SELECT    Hl7_transaction_log_id,
              Record_Created_date
    FROM      Hl7_Transaction_Log
    WHERE     Person_id = VN_current_person_id
    AND       Hl7_message_status_type_id = CC_stat_type_complete
    AND       Message_transmission_type_id = CC_tran_type_oruz11e
    ORDER BY  Record_Created_date ASC,
              Hl7_transaction_log_id ASC;

  CURSOR C_comp_oruz05e_rec IS  -- CCR1788
    SELECT    Hl7_transaction_log_id,
              Record_Created_date
    FROM      Hl7_Transaction_Log
    WHERE     Person_id = VN_current_person_id
    AND       Hl7_message_status_type_id = CC_stat_type_complete
    AND       Message_transmission_type_id = CC_tran_type_oruz05e
    ORDER BY  Record_Created_date ASC,
              Hl7_transaction_log_id ASC;

-- ***************************************************************************
-- *
-- * Update Last Person Processed Parameter
-- *
-- ***************************************************************************

PROCEDURE Update_Last_Person_Parm
  (PN_person_id
     IN     NUMBER DEFAULT 0
  )
IS

BEGIN

  UPDATE    Adr_Parameter AP
  SET       AP.Value = PN_person_id
  WHERE     Application_name = 'HL7_PROCESS_ARCHIVES'
  AND       Parameter_name = 'LAST PERSON_ID PROCESSED';

  COMMIT;

EXCEPTION

  WHEN OTHERS THEN

    VB_error := TRUE;

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                              PC_job_log_message => SUBSTR('Failed to update parameter row (person id failure).'
                                                           || ' Application: HL7_PROCESS_ARCHIVES'
                                                           || ' Parameter: LAST PERSON_ID PROCESSED'
                                                           || ' Requested new Person_id: '
                                                           || LTRIM(TO_CHAR(PN_person_id,'9999999999990'))
                                                           || ' Current Person_id: '
                                                           || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                           || ' Previous Person_id: '
                                                           || LTRIM(TO_CHAR(VN_previous_person_id,'9999999999990'))
                                                           || ' - Error: '
                                                           || SQLERRM,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_error_date  => VD_log_date,
                              PC_job_error_code => SUBSTR(SQLCODE,1,60),
                              PC_job_error_message => SUBSTR('Failed to update parameter row (person id failure).'
                                                           || ' Application: HL7_PROCESS_ARCHIVES'
                                                           || ' Parameter: LAST PERSON_ID PROCESSED'
                                                           || ' Requested new Person_id: '
                                                           || LTRIM(TO_CHAR(PN_person_id,'9999999999990'))
                                                           || ' Current Person_id: '
                                                           || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                           || ' Previous Person_id: '
                                                           || LTRIM(TO_CHAR(VN_previous_person_id,'9999999999990'))
                                                           || ' - Error: '
                                                           || SQLERRM,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

END Update_Last_Person_Parm;

-- ***************************************************************************
-- *
-- * Table Row Insert/Delete Sub-Procedure
-- *
-- ***************************************************************************

PROCEDURE Process_Archive_Rows
  (PC_process_msg
     IN     VARCHAR2 DEFAULT NULL
  )
IS

-- EXCEPTIONS

   EE_DML_ERROR                        EXCEPTION;
   PRAGMA                              EXCEPTION_INIT(EE_DML_ERROR, -24381);

BEGIN  -- insert/delete of archive rows

-- Archive old completed HL7 rows

  VN_insert_count := 0;

  DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Inserting Archive Data');

-- The number of rows in the table that will be archived will be adjusted by the number of rows to keep
--  by subtracting the number to keep (VI_rows_to_keep_count) from the maximum internal row number in
--  the collection table. This value is set in the main processing logic.

  BEGIN  -- insert archive rows

    FORALL I_hl7_txns_ins IN T_hl7_txns.FIRST..T_hl7_txns.LAST - VI_rows_to_keep_count SAVE EXCEPTIONS
      INSERT
      INTO      Hl7_Archive_Transaction_Log
                (HL7_TRANSACTION_LOG_ID,
                 PERSON_ID,
                 MESSAGE_TRANSMISSION_TYPE_ID,
                 HL7_MESSAGE_STATUS_TYPE_ID,
                 REF_HL7_TRANSACTION_LOG_ID,
                 STD_INSTITUTION_ID,
                 ACK_TYPE_ID,
                 MESSAGE_CONTROL_NUMBER,
                 BATCH_CONTROL_NUMBER,
                 ACK_DATE,
                 TRANSMISSION_DATE,
                 RETRANSMISSION_COUNT,
                 RETRANSMISSION_INFO_TEXT,
                 ERROR_TEXT,
                 SITE_RECORD_ELGBTY_STATUS_CODE,
                 RECORD_CREATED_BY,
                 RECORD_CREATED_DATE,
                 MESSAGE_BODY,
                 INTERNAL_ERROR_TEXT)
      SELECT    HL7_TRANSACTION_LOG_ID,
                PERSON_ID,
                MESSAGE_TRANSMISSION_TYPE_ID,
                HL7_MESSAGE_STATUS_TYPE_ID,
                REF_HL7_TRANSACTION_LOG_ID,
                STD_INSTITUTION_ID,
                ACK_TYPE_ID,
                MESSAGE_CONTROL_NUMBER,
                BATCH_CONTROL_NUMBER,
                ACK_DATE,
                TRANSMISSION_DATE,
                RETRANSMISSION_COUNT,
                RETRANSMISSION_INFO_TEXT,
                ERROR_TEXT,
                SITE_RECORD_ELGBTY_STATUS_CODE,
                RECORD_CREATED_BY,
                RECORD_CREATED_DATE,
                MESSAGE_BODY,
                INTERNAL_ERROR_TEXT
      FROM      Hl7_Transaction_Log
      WHERE     Hl7_transaction_log_id = T_hl7_txns(I_hl7_txns_ins).Hl7_transaction_log_id;

    VN_insert_count := SQL%ROWCOUNT;

    IF (T_hl7_txns.COUNT - VI_rows_to_keep_count) = VN_insert_count THEN  -- If all the rows were inserted

  -- Delete old completed HL7 rows

      VN_delete_count := 0;

      DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Deleting Archived HL7 Data');

  -- The number of rows in the table that will be archived will be adjusted by the number of rows to keep
  --  by subtracting the number to keep (VI_rows_to_keep_count) from the maximum internal row number in
  --  the collection table. This value is set in the main processing logic.

      BEGIN  -- delete HL7 rows

        FORALL I_hl7_txns_del IN T_hl7_txns.FIRST..T_hl7_txns.LAST - VI_rows_to_keep_count SAVE EXCEPTIONS
          DELETE
          FROM      Hl7_Transaction_Log
          WHERE     Hl7_Transaction_Log_id = T_hl7_txns(I_hl7_txns_del).Hl7_Transaction_Log_id;

        VN_delete_count := SQL%ROWCOUNT;

        IF (T_hl7_txns.COUNT - VI_rows_to_keep_count) = VN_delete_count THEN  -- if all rows were deleted

          IF VN_insert_count = VN_delete_count THEN  -- insert and delete counts match

            COMMIT;

            VN_archive_count := VN_archive_count + VN_insert_count;
            VN_person_count := VN_person_count + 1;

            DBMS_APPLICATION_INFO.SET_CLIENT_INFO(TO_CHAR(SYSDATE,'DD-MON HH24:MI')
                                                  || ' -Total Persons '  -- Total people processed
                                                  || LTRIM(TO_CHAR(VN_person_count,'9,999,999,990'))
                                                  || ' -Total Rows '  -- Total rows processed
                                                  || LTRIM(TO_CHAR(VN_archive_count,'999,999,999,990'))
                                                  || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                  || '.');

          ELSE  -- insert and delete counts do not match

            VB_error := TRUE;

            VD_log_date := SYSDATE;

            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_log_date => VD_log_date,
                                      PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                      PC_job_log_message => SUBSTR('INSERT/DELETE counts do not match. '
                                                                   || LTRIM(RTRIM(PC_process_msg))
                                                                   || ' - Rows in collection after clean up: '
                                                                   || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                                   || ' - INSERT COUNT: '
                                                                   || LTRIM(TO_CHAR(VN_insert_count,'999,990'))
                                                                   || ' - DELETE COUNT: '
                                                                   || LTRIM(TO_CHAR(VN_delete_count,'999,990')),1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_error_date  => VD_log_date,
                                      PC_job_error_code => NULL,
                                      PC_job_error_message => SUBSTR('INSERT/DELETE counts do not match. '
                                                                     || LTRIM(RTRIM(PC_process_msg))
                                                                     || ' - Rows in collection after clean up: '
                                                                     || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                                     || ' - INSERT COUNT: '
                                                                     || LTRIM(TO_CHAR(VN_insert_count,'999,990'))
                                                                     || ' - DELETE COUNT: '
                                                                     || LTRIM(TO_CHAR(VN_delete_count,'999,990')),1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            ROLLBACK;

            Update_Last_Person_Parm(VN_previous_person_id);

          END IF;-- insert and delete counts match

        ELSE  -- not all rows were deleted

          VB_error := TRUE;

          VD_log_date := SYSDATE;

          ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                    PD_job_log_date => VD_log_date,
                                    PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                    PC_job_log_message => SUBSTR('Number of rows deleted do not match number of rows in temp table. '
                                                                 || LTRIM(RTRIM(PC_process_msg))
                                                                 || ' - Rows in collection after clean up: '
                                                                 || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                                 || ' - DELETE COUNT: '
                                                                 || LTRIM(TO_CHAR(VN_delete_count,'999,990')),1,2000),
                                    PD_date1 => VD_process_start_date,
                                    PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

          ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                    PD_job_error_date  => VD_log_date,
                                    PC_job_error_code => NULL,
                                    PC_job_error_message => SUBSTR('Number of rows deleted do not match number of rows in temp table. '
                                                                   || LTRIM(RTRIM(PC_process_msg))
                                                                   || ' - Rows in collection after clean up: '
                                                                   || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                                   || ' - DELETE COUNT: '
                                                                   || LTRIM(TO_CHAR(VN_delete_count,'999,990')),1,2000),
                                    PD_date1 => VD_process_start_date,
                                    PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

          ROLLBACK;

          Update_Last_Person_Parm(VN_previous_person_id);

        END IF;  -- if all rows were deleted

      EXCEPTION   -- failed during delete

        WHEN EE_DML_ERROR THEN

          VB_error := TRUE;
          VN_delete_count := SQL%ROWCOUNT;

          VD_log_date := SYSDATE;

          ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                    PD_job_log_date => VD_log_date,
                                    PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                    PC_job_log_message => SUBSTR('All rows not deleted. '
                                                                 || LTRIM(RTRIM(PC_process_msg))
                                                                 || ' - Rows in collection after clean up: '
                                                                 || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                                 || '. Error count: '
                                                                 || LTRIM(TO_CHAR(SQL%BULK_EXCEPTIONS.COUNT,'999,990'))
                                                                 || '. Rows attempted to delete: '
                                                                 || LTRIM(TO_CHAR(VN_delete_count,'999,990')),1,2000),
                                    PD_date1 => VD_process_start_date,
                                    PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

          ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                    PD_job_error_date  => VD_log_date,
                                    PC_job_error_code => NULL,
                                    PC_job_error_message => SUBSTR('All rows not deleted. '
                                                                   || LTRIM(RTRIM(PC_process_msg))
                                                                   || ' - Rows in collection after clean up: '
                                                                   || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                                   || '. Error count: '
                                                                   || LTRIM(TO_CHAR(SQL%BULK_EXCEPTIONS.COUNT,'999,990'))
                                                                   || '. Rows attempted to delete: '
                                                                   || LTRIM(TO_CHAR(VN_delete_count,'999,990')),1,2000),
                                    PD_date1 => VD_process_start_date,
                                    PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

          FOR I_del_err IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP

            VD_log_date := SYSDATE;

            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_log_date => VD_log_date,
                                      PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                      PC_job_log_message => SUBSTR('Failed DELETE row # '
                                                                   || I_del_err
                                                                   || ' occurred during iteration # '
                                                                   || SQL%BULK_EXCEPTIONS(I_del_err).ERROR_INDEX
                                                                   || ' Error message: '
                                                                   || SQLERRM(-SQL%BULK_EXCEPTIONS(I_del_err).ERROR_CODE),1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_error_date  => VD_log_date,
                                      PC_job_error_code => SUBSTR(-SQL%BULK_EXCEPTIONS(I_del_err).ERROR_CODE,1,60),
                                      PC_job_error_message => SUBSTR('Failed DELETE row # '
                                                                     || I_del_err
                                                                     || ' occurred during iteration # '
                                                                     || SQL%BULK_EXCEPTIONS(I_del_err).ERROR_INDEX
                                                                     || ' Error message: '
                                                                     || SQLERRM(-SQL%BULK_EXCEPTIONS(I_del_err).ERROR_CODE),1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

          END LOOP;

          ROLLBACK;

          Update_Last_Person_Parm(VN_previous_person_id);

        WHEN OTHERS THEN

          VB_error := TRUE;
          VN_insert_count := SQL%ROWCOUNT;

          VD_log_date := SYSDATE;

          ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                    PD_job_log_date => VD_log_date,
                                    PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                    PC_job_log_message => SUBSTR('Unknown error while deleting HL7 rows. '
                                                                 || LTRIM(RTRIM(PC_process_msg))
                                                                 || ' - Rows in collection after clean up: '
                                                                 || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                                 || '. Error count: '
                                                                 || LTRIM(TO_CHAR(SQL%BULK_EXCEPTIONS.COUNT,'999,990'))
                                                                 || '. Rows attempted to delete: '
                                                                 || LTRIM(TO_CHAR(VN_insert_count,'999,990')),1,2000),
                                    PD_date1 => VD_process_start_date,
                                    PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

          ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                    PD_job_error_date  => VD_log_date,
                                    PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                    PC_job_error_message => SUBSTR('Unknown error while deleting HL7 rows. '
                                                                   || LTRIM(RTRIM(PC_process_msg))
                                                                   || ' - Rows in collection after clean up: '
                                                                   || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                                   || '. Error count: '
                                                                   || LTRIM(TO_CHAR(SQL%BULK_EXCEPTIONS.COUNT,'999,990'))
                                                                   || '. Rows attempted to delete: '
                                                                   || LTRIM(TO_CHAR(VN_insert_count,'999,990')),1,2000),
                                    PD_date1 => VD_process_start_date,
                                    PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

          ROLLBACK;

          Update_Last_Person_Parm(VN_previous_person_id);

      END;  -- end delete HL7 rows

    ELSE  -- not all the rows were inserted

      VB_error := TRUE;

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                PC_job_log_message => SUBSTR('Number of rows inserted do not match number of rows in temp table. '
                                                             || LTRIM(RTRIM(PC_process_msg))
                                                             || ' - Rows in collection after clean up: '
                                                             || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                             || ' - INSERT COUNT: '
                                                             || LTRIM(TO_CHAR(VN_insert_count,'999,990')),1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => NULL,
                                PC_job_error_message => SUBSTR('Number of rows inserted do not match number of rows in temp table. '
                                                               || LTRIM(RTRIM(PC_process_msg))
                                                               || ' - Rows in collection after clean up: '
                                                               || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                               || ' - INSERT COUNT: '
                                                               || LTRIM(TO_CHAR(VN_insert_count,'999,990')),1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ROLLBACK;

      Update_Last_Person_Parm(VN_previous_person_id);

    END IF;  -- end if all the rows were inserted

  EXCEPTION   -- failed during insert

    WHEN EE_DML_ERROR THEN

      VB_error := TRUE;
      VN_insert_count := SQL%ROWCOUNT;

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                PC_job_log_message => SUBSTR('All rows not inserted. '
                                                             || LTRIM(RTRIM(PC_process_msg))
                                                             || ' - Rows in collection after clean up: '
                                                             || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                             || '. Error count: '
                                                             || LTRIM(TO_CHAR(SQL%BULK_EXCEPTIONS.COUNT,'999,990'))
                                                             || '. Rows attempted to insert: '
                                                             || LTRIM(TO_CHAR(VN_insert_count,'999,990')),1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => NULL,
                                PC_job_error_message => SUBSTR('All rows not inserted. '
                                                               || LTRIM(RTRIM(PC_process_msg))
                                                               || ' - Rows in collection after clean up: '
                                                               || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                               || '. Error count: '
                                                               || LTRIM(TO_CHAR(SQL%BULK_EXCEPTIONS.COUNT,'999,990'))
                                                               || '. Rows attempted to insert: '
                                                               || LTRIM(TO_CHAR(VN_insert_count,'999,990')),1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      FOR I_ins_err IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP

        VD_log_date := SYSDATE;

        ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                  PD_job_log_date => VD_log_date,
                                  PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                  PC_job_log_message => SUBSTR('Failed INSERT row # '
                                                               || I_ins_err
                                                               || ' occurred during iteration # '
                                                               || SQL%BULK_EXCEPTIONS(I_ins_err).ERROR_INDEX
                                                               || ' Error message: '
                                                               || SQLERRM(-SQL%BULK_EXCEPTIONS(I_ins_err).ERROR_CODE),1,2000),
                                  PD_date1 => VD_process_start_date,
                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

        ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                  PD_job_error_date  => VD_log_date,
                                  PC_job_error_code => SUBSTR(-SQL%BULK_EXCEPTIONS(I_ins_err).ERROR_CODE,1,60),
                                  PC_job_error_message => SUBSTR('Failed INSERT row # '
                                                               || I_ins_err
                                                               || ' occurred during iteration # '
                                                               || SQL%BULK_EXCEPTIONS(I_ins_err).ERROR_INDEX
                                                               || ' Error message: '
                                                               || SQLERRM(-SQL%BULK_EXCEPTIONS(I_ins_err).ERROR_CODE),1,2000),
                                  PD_date1 => VD_process_start_date,
                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      END LOOP;

      ROLLBACK;

      Update_Last_Person_Parm(VN_previous_person_id);

    WHEN OTHERS THEN

      VB_error := TRUE;
      VN_insert_count := SQL%ROWCOUNT;

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                PC_job_log_message => SUBSTR('Unknown error while inserting archive rows. '
                                                             || LTRIM(RTRIM(PC_process_msg))
                                                             || ' - Rows in collection after clean up: '
                                                             || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                             || '. Error count: '
                                                             || LTRIM(TO_CHAR(SQL%BULK_EXCEPTIONS.COUNT,'999,990'))
                                                             || '. Rows attempted to insert: '
                                                             || LTRIM(TO_CHAR(VN_insert_count,'999,990')),1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unknown error while inserting archive rows. '
                                                               || LTRIM(RTRIM(PC_process_msg))
                                                               || ' - Rows in collection after clean up: '
                                                               || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                               || '. Error count: '
                                                               || LTRIM(TO_CHAR(SQL%BULK_EXCEPTIONS.COUNT,'999,990'))
                                                               || '. Rows attempted to insert: '
                                                               || LTRIM(TO_CHAR(VN_insert_count,'999,990')),1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ROLLBACK;

      Update_Last_Person_Parm(VN_previous_person_id);

  END;  -- end insert archive rows

EXCEPTION

  WHEN OTHERS THEN

    VB_error := TRUE;

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                              PC_job_log_message => SUBSTR('Unknown error in procedure Process_Archive_Rows. '
                                                           || LTRIM(RTRIM(PC_process_msg))
                                                           || ' - Rows in collection after clean up: '
                                                           || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                           || ' - Error: '
                                                           || SQLERRM,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_error_date  => VD_log_date,
                              PC_job_error_code => SUBSTR(SQLCODE,1,60),
                              PC_job_error_message => SUBSTR('Unknown error in procedure Process_Archive_Rows. '
                                                           || LTRIM(RTRIM(PC_process_msg))
                                                           || ' - Rows in collection after clean up: '
                                                           || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                           || ' - Error: '
                                                           || SQLERRM,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    ROLLBACK;

    Update_Last_Person_Parm(VN_previous_person_id);

END Process_Archive_Rows;

-- ***************************************************************************
-- *
-- * Main Procedure
-- *
-- ***************************************************************************

BEGIN

  DBMS_APPLICATION_INFO.SET_MODULE(Module_name => 'Hl7_Process_Archives',
                                   Action_name => NULL);

  VD_process_start_date := SYSDATE;

  VD_log_date := SYSDATE;

  ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                            PD_job_log_date => VD_log_date,
                            PC_job_status => SUBSTR('INFORMATION - Process Initiation',1,60),
                            PC_job_log_message => NULL,
                            PD_date1 => VD_process_start_date,
                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

-- ***************************************************************************
-- *
-- * Validate input parameters
-- *
-- ***************************************************************************

  DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Validating Input Parameters');

-- * Check run standalone flag

  IF PB_run_standalone IS NULL
   OR NOT PB_run_standalone THEN

    VB_run_standalone := FALSE;
    VC_run_standalone := 'FALSE'; -- used for log message, boolean can't be displayed

  ELSE

    VB_run_standalone := TRUE;
    VC_run_standalone := 'TRUE'; -- used for log message, boolean can't be displayed

  END IF;

-- * Check run on report instance flag

  IF PB_allow_rpt_inst IS NULL
   OR NOT PB_allow_rpt_inst THEN

    VB_allow_rpt_inst := FALSE;
    VC_allow_rpt_inst := 'FALSE'; -- used for log message, boolean can't be displayed

  ELSE

    VB_allow_rpt_inst := TRUE;
    VC_allow_rpt_inst := 'TRUE'; -- used for log message, boolean can't be displayed

  END IF;

  VD_log_date := SYSDATE;

  ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                            PD_job_log_date => VD_log_date,
                            PC_job_status => SUBSTR('INFORMATION - Command Line Parameters',1,60),
                            PC_job_log_message => SUBSTR('Current job command line parameters - '
                                                      || ' Run standalone: ' || VC_run_standalone
                                                      || ' Run on reporting databases: ' || VC_allow_rpt_inst,1,2000),
                            PD_date1 => VD_process_start_date,
                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

-- ***************************************************************************
-- *
-- * Check to see if process is running on a reporting database.
-- *  - If VB_allow_rpt_inst is FALSE, then it's not allowed. End the job.
-- *
-- ***************************************************************************

  DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Check if running on reporting DB');

  BEGIN

    SELECT    Instance_name
    INTO      VC_instance_name
    FROM      V$instance;

    VD_log_date := SYSDATE;
    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Instance Name' ,1,60),
                              PC_job_log_message => SUBSTR('Job is running on instance ' || UPPER(VC_instance_name) || '.',1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    IF NOT VB_allow_rpt_inst
     AND UPPER(SUBSTR(VC_instance_name,1,4)) = 'ADRR' THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - Reporting Database Check',1,60),
                                PC_job_log_message => SUBSTR('Attempting to run on a reporting database with execution parameter'
                                                          || ' VB_allow_rpt_inst set to FALSE. This job has been terminated.'
                                                          || ' To run on a reporting instance, set parameter VB_allow_rpt_inst'
                                                          || ' to TRUE and resubmit the job.',1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

    END IF;

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - Reporting Database Check',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve instance name. Terminating script.',1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve instance name. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- ***************************************************************************
-- *
-- * Check to see if any other jobs of this procedure are running.
-- *  - If yes, then end this job.
-- *
-- ***************************************************************************

  DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Check for duplicate jobs running');

  SELECT COUNT(*)
  INTO   VN_job_count
  FROM   SYS.DBA_JOBS J
  WHERE  UPPER(J.WHAT) LIKE '%HL7_PROCESS_ARCHIVES%';

  IF VN_job_count > 1 THEN

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('ERROR       - Multiple Execution Check',1,60),
                              PC_job_log_message => SUBSTR('There is another instance of this job already in the job queue.'
                                                        || ' Exiting job without running.',1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    RETURN;

  ELSE

    IF VB_run_standalone THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('INFORMATION - Execution Mode',1,60),
                                PC_job_log_message => SUBSTR('Starting in STANDALONE mode.',1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    ELSE

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('INFORMATION - Execution Mode',1,60),
                                PC_job_log_message => SUBSTR('Starting in DBMS_JOB mode.',1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    END IF;

  END IF;

-- ***************************************************************************
-- *
-- * Retrieve modifiable parameters
-- *
-- *  If the process control parameter = 0 then the job returns control to
-- *  to DBMS_JOB and waits until it is called to run again.
-- *
-- ***************************************************************************

  DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Fetching user parameters');

-- * START_STOP_PROCESS parameter

  BEGIN

    SELECT    AP.Value
    INTO      VN_process_control
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_ARCHIVE_CONTROL'
    AND       Parameter_name = 'START_STOP_PROCESS';

    IF VN_process_control = 0 THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('INFORMATION - Process Control',1,60),
                                PC_job_log_message => SUBSTR('Start/Stop control flag set to STOP (0). Going back to sleep.',1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

    ELSE

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('INFORMATION - Process Control',1,60),
                                PC_job_log_message => SUBSTR('Start/Stop control flag set to START (1). Archiving process starting.',1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    END IF;

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - Process Control',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve parameter START_STOP_PROCESS.'
                                                             || ' Terminating execution of the job. '|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve parameter START_STOP_PROCESS. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- * OLDEST LOG FILE DAYS KEPT parameter

  BEGIN

    SELECT    AP.Value
    INTO      VN_log_age_to_keep
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_PROCESS_ARCHIVES'
    AND       Parameter_name = 'OLDEST LOG FILE DAYS KEPT';

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - User Defined Parameters',1,60),
                              PC_job_log_message => SUBSTR('Parameter: OLDEST LOG FILE DAYS KEPT  - Value: '|| VN_log_age_to_keep,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - User Defined Parameters',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve parameter OLDEST LOG FILE DAYS KEPT.'
                                                             || ' Terminating execution of the job. '|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve parameter OLDEST LOG FILE DAYS KEPT. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- * OLDEST UNCOMPLETED HL7 DAYS NOT ARCHIVED parameter

  BEGIN

    SELECT    AP.Value
    INTO      VN_parm_keep_uncompleted_days
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_PROCESS_ARCHIVES'
    AND       Parameter_name = 'OLDEST UNCOMPLETED HL7 DAYS NOT ARCHIVED';

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - User Defined Parameters',1,60),
                              PC_job_log_message => SUBSTR('Parameter: OLDEST UNCOMPLETED HL7 DAYS NOT ARCHIVED - Value: '|| VN_parm_keep_uncompleted_days,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - User Defined Parameters',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve parameter OLDEST UNCOMPLETED HL7 DAYS NOT ARCHIVED.'
                                                             || ' Terminating execution of the job. '|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve parameter OLDEST UNCOMPLETED HL7 DAYS NOT ARCHIVED. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- * RECORDS KEPT COUNT - ORFZ10-S parameter

  BEGIN

    SELECT    AP.Value
    INTO      VN_parm_keep_orfz10s_count
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_PROCESS_ARCHIVES'
    AND       Parameter_name = 'RECORDS KEPT COUNT - ORFZ10-S';

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - User Defined Parameters',1,60),
                              PC_job_log_message => SUBSTR('Parameter: RECORDS KEPT COUNT - ORFZ10-S - Value: '|| VN_parm_keep_orfz10s_count,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - User Defined Parameters',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - ORFZ10-S.'
                                                             || ' Terminating execution of the job. '|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - ORFZ10-S. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- * RECORDS KEPT COUNT - ORFZ11-S parameter  -- CCR 1788 Begin

  BEGIN

    SELECT    AP.Value
    INTO      VN_parm_keep_orfz11s_count
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_PROCESS_ARCHIVES'
    AND       Parameter_name = 'RECORDS KEPT COUNT - ORFZ11-S';

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - User Defined Parameters',1,60),
                              PC_job_log_message => SUBSTR('Parameter: RECORDS KEPT COUNT - ORFZ11-S - Value: '|| VN_parm_keep_orfz11s_count,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - User Defined Parameters',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - ORFZ11-S.'
                                                             || ' Terminating execution of the job. '|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - ORFZ11-S. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;  -- CCR 1788 End

-- * RECORDS KEPT COUNT - ORUZ04-S parameter

  BEGIN

    SELECT    AP.Value
    INTO      VN_parm_keep_oruz04s_count
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_PROCESS_ARCHIVES'
    AND       Parameter_name = 'RECORDS KEPT COUNT - ORUZ04-S';

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - User Defined Parameters',1,60),
                              PC_job_log_message => SUBSTR('Parameter: RECORDS KEPT COUNT - ORUZ04-S - Value: '|| VN_parm_keep_oruz04s_count,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - User Defined Parameters',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - ORUZ04-S.'
                                                             || ' Terminating execution of the job. '|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - ORUZ04-S. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- * RECORDS KEPT COUNT - ORUZ05-E parameter  -- CCR 1788 Begin

  BEGIN

    SELECT    AP.Value
    INTO      VN_parm_keep_oruz05e_count
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_PROCESS_ARCHIVES'
    AND       Parameter_name = 'RECORDS KEPT COUNT - ORUZ05-E';

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - User Defined Parameters',1,60),
                              PC_job_log_message => SUBSTR('Parameter: RECORDS KEPT COUNT - ORUZ05-E - Value: '|| VN_parm_keep_oruz05e_count,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - User Defined Parameters',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - ORUZ05-E.'
                                                             || ' Terminating execution of the job. '|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - ORUZ05-E. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;  -- CCR 1788 End

-- * RECORDS KEPT COUNT - ORUZ05-S parameter

  BEGIN

    SELECT    AP.Value
    INTO      VN_parm_keep_oruz05s_count
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_PROCESS_ARCHIVES'
    AND       Parameter_name = 'RECORDS KEPT COUNT - ORUZ05-S';

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - User Defined Parameters',1,60),
                              PC_job_log_message => SUBSTR('Parameter: RECORDS KEPT COUNT - ORUZ05-S - Value: '|| VN_parm_keep_oruz05s_count,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - User Defined Parameters',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - ORUZ05-S.'
                                                             || ' Terminating execution of the job. '|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - ORUZ05-S. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- * RECORDS KEPT COUNT - ORUZ10-S parameter

  BEGIN

    SELECT    AP.Value
    INTO      VN_parm_keep_oruz10s_count
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_PROCESS_ARCHIVES'
    AND       Parameter_name = 'RECORDS KEPT COUNT - ORUZ10-S';

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - User Defined Parameters',1,60),
                              PC_job_log_message => SUBSTR('Parameter: RECORDS KEPT COUNT - ORUZ10-S - Value: '|| VN_parm_keep_oruz10s_count,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - User Defined Parameters',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - ORUZ10-S.'
                                                             || ' Terminating execution of the job. '|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - ORUZ10-S. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- * RECORDS KEPT COUNT - ORUZ11-E parameter

  BEGIN

    SELECT    AP.Value
    INTO      VN_parm_keep_oruz11e_count
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_PROCESS_ARCHIVES'
    AND       Parameter_name = 'RECORDS KEPT COUNT - ORUZ11-E';

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - User Defined Parameters',1,60),
                              PC_job_log_message => SUBSTR('Parameter: RECORDS KEPT COUNT - ORUZ11-E - Value: '|| VN_parm_keep_oruz11e_count,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - User Defined Parameters',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - ORUZ11-E.'
                                                             || ' Terminating execution of the job. '|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - ORUZ11-E. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- * RECORDS KEPT COUNT - ORUZ11-S parameter

  BEGIN

    SELECT    AP.Value
    INTO      VN_parm_keep_oruz11s_count
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_PROCESS_ARCHIVES'
    AND       Parameter_name = 'RECORDS KEPT COUNT - ORUZ11-S';

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - User Defined Parameters',1,60),
                              PC_job_log_message => SUBSTR('Parameter: RECORDS KEPT COUNT - ORUZ11-S - Value: '|| VN_parm_keep_oruz11s_count,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - User Defined Parameters',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - ORUZ11-S.'
                                                             || ' Terminating execution of the job. '|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - ORUZ11-S. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- * RECORDS KEPT COUNT - QRYZ10-E parameter

  BEGIN

    SELECT    AP.Value
    INTO      VN_parm_keep_qryz10e_count
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_PROCESS_ARCHIVES'
    AND       Parameter_name = 'RECORDS KEPT COUNT - QRYZ10-E';

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - User Defined Parameters',1,60),
                              PC_job_log_message => SUBSTR('Parameter: RECORDS KEPT COUNT - QRYZ10-E - Value: '|| VN_parm_keep_qryz10e_count,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - User Defined Parameters',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - QRYZ10-E.'
                                                             || ' Terminating execution of the job. '|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - QRYZ10-E. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- * RECORDS KEPT COUNT - QRYZ11-E parameter  -- CCR 1788 Begin

  BEGIN

    SELECT    AP.Value
    INTO      VN_parm_keep_qryz11e_count
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_PROCESS_ARCHIVES'
    AND       Parameter_name = 'RECORDS KEPT COUNT - QRYZ11-E';

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - User Defined Parameters',1,60),
                              PC_job_log_message => SUBSTR('Parameter: RECORDS KEPT COUNT - QRYZ11-E - Value: '|| VN_parm_keep_qryz11e_count,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - User Defined Parameters',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - QRYZ11-E.'
                                                             || ' Terminating execution of the job. '|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve parameter RECORDS KEPT COUNT - QRYZ11-E. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;  -- CCR 1788 End

-- * LAST PERSON_ID PROCESSED parameter

  BEGIN

    SELECT    AP.Value
    INTO      VN_previous_person_id
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_PROCESS_ARCHIVES'
    AND       Parameter_name = 'LAST PERSON_ID PROCESSED';

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - User Defined Parameters',1,60),
                              PC_job_log_message => SUBSTR('Parameter: LAST PERSON_ID PROCESSED - Value: '|| VN_previous_person_id,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - User Defined Parameters',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve parameter LAST PERSON_ID PROCESSED.'
                                                             || ' Terminating execution of the job. '|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve parameter LAST PERSON_ID PROCESSED. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- ***************************************************************************
-- *
-- * Remove outdated log and error file rows
-- *
-- ***************************************************************************

  DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Removing old logs and error rows');

  BEGIN

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Log File Cleanup',1,60),
                              PC_job_log_message => SUBSTR('Removing Error Logs more than '|| VN_log_age_to_keep || ' days old.',1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    DELETE
    FROM      Job_Process_Errs
    WHERE     Job_name = CC_process_name
    AND       Date1 < VD_process_start_date - VN_log_age_to_keep;

    COMMIT;

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - Log file cleanup',1,60),
                                PC_job_log_message => SUBSTR('Unable to remove rows from error log table.'
                                                             || ' Terminating execution of the job. '|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to remove rows from error log table. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

  BEGIN

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Log File Cleanup',1,60),
                              PC_job_log_message => SUBSTR('Removing Process Logs more than '|| VN_log_age_to_keep || ' days old.',1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    DELETE
    FROM      Job_Process_Logs
    WHERE     Job_name = CC_process_name
    AND       Date1 < VD_process_start_date - VN_log_age_to_keep;

    COMMIT;

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - Log file cleanup',1,60),
                                PC_job_log_message => SUBSTR('Unable to remove rows from process log table.'
                                                             || ' Terminating execution of the job. '|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to remove rows from process log table. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- ***************************************************************************
-- *
-- * Archive Processing loop
-- *
-- ***************************************************************************

  BEGIN  -- main processing block

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Archive Process Loop',1,60),
                              PC_job_log_message => SUBSTR('Starting archival of HL7 rows',1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    LOOP  -- main archive processing loop

      DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Setting Up Archive Data');

      BEGIN  -- Get next person_id to process

        SELECT    NVL(MIN(Person_id),-1) -- going past the last Person_id (EOF) returns a NULL Person_id but not the exception NO_DATA_FOUND
        INTO      VN_current_person_id   -- so flag EOF by putting -1 in VN_current_person_id
        FROM      Hl7_Transaction_Log
        WHERE     Person_id > VN_previous_person_id;

        IF VN_current_person_id = -1 THEN  -- if past the last Person_id (EOF)

          RAISE NO_DATA_FOUND;  -- force a NO_DATA_FOUND exception to tell the process to complete

        END IF;  -- end if past the last Person_id (EOF)

        DBMS_APPLICATION_INFO.SET_CLIENT_INFO(TO_CHAR(SYSDATE,'DD-MON HH24:MI:SS')
                                              || ' - Person '
                                              || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                              || '.');

-- CCR 1788 Begin
-- ***************************************************************************
-- * Process Status = COMPLETED (1506921)
-- *         Transmission Type = ORUZ05-E txns (1510407)
-- ***************************************************************************

        BEGIN  -- process ORUZ05-E txns (1510407)

          DBMS_APPLICATION_INFO.SET_CLIENT_INFO(TO_CHAR(SYSDATE,'DD-MON HH24:MI')
                                                || ' -P '  -- Person ID
                                                || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                || ' -T '  -- Msg Transmission ID
                                                || LTRIM(TO_CHAR(CC_tran_type_oruz05e,'99999990'))
                                                || '.');

          VI_rows_to_keep_count := 0; -- reset variable that determines how many rows to remove from archiving request table

          OPEN C_comp_oruz05e_rec;

          FETCH C_comp_oruz05e_rec
          BULK COLLECT INTO T_hl7_txns;

          CLOSE C_comp_oruz05e_rec;

          DBMS_APPLICATION_INFO.SET_CLIENT_INFO(TO_CHAR(SYSDATE,'DD-MON HH24:MI')
                                                || ' -P '  -- Person ID
                                                || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                || ' -T '  -- Msg Transmission ID
                                                || LTRIM(TO_CHAR(CC_tran_type_oruz05e,'99999990'))
                                                || ' -C '  -- First count of rows in collection
                                                || LTRIM(TO_CHAR(T_hl7_txns.COUNT,'999,990'))
                                                || '.');

          IF T_hl7_txns.COUNT > 0 THEN  -- completed ORUZ05-E txns exist

            IF T_hl7_txns.COUNT > VN_parm_keep_oruz05e_count THEN  -- there are more in the table than need to be kept

              VI_rows_to_keep_count := VN_parm_keep_oruz05e_count;  -- save how many need to be kept

            END IF;  -- end there are more in the table than need to be kept

          END IF;  -- end completed ORUZ05-E txns exist

          IF VI_rows_to_keep_count > 0 THEN -- remove rows from archiving request table

            DBMS_APPLICATION_INFO.SET_CLIENT_INFO(TO_CHAR(SYSDATE,'DD-MON HH24:MI')
                                                  || ' -P '  -- Person ID
                                                  || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                  || ' -T '  -- Msg Transmission ID
                                                  || LTRIM(TO_CHAR(CC_tran_type_oruz05e,'99999990'))
                                                  || ' -F '  -- Final count of rows in collection
                                                  || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                  || '.');

            VC_process_msg := 'COMPLETED ORUZ05-E txn. Person ID: '
                              || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                              || ' - Status Type ID: '
                              || CC_stat_type_complete
                              || ' - Msg Transmission Type ID: '
                              || CC_tran_type_oruz05e;

            Process_Archive_Rows(VC_process_msg); -- call INSERT/DELETE procedure

            IF VB_error THEN  -- error flag is set in sub-procedure if there were problems

              RETURN;

            END IF;

          END IF; -- end remove rows from archiving request table

        EXCEPTION

          WHEN OTHERS THEN

            VD_log_date := SYSDATE;

            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_log_date => VD_log_date,
                                      PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                      PC_job_log_message => SUBSTR('Failed in transmission ID ORUZ05-E processing step. Person_id: '
                                                                   || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                                   || ' - Transmission type ID: '
                                                                   || LTRIM(TO_CHAR(CC_tran_type_oruz05e,'99999990'))
                                                                   || ' - Rows in collection after clean up: '
                                                                   || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                                   || ' - Error: '
                                                                   || SQLERRM,1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_error_date  => VD_log_date,
                                      PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                      PC_job_error_message => SUBSTR('Failed in transmission ID ORUZ05-E processing step. Person_id: '
                                                                     || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                                     || ' - Transmission type ID: '
                                                                     || LTRIM(TO_CHAR(CC_tran_type_oruz05e,'99999990'))
                                                                     || ' - Rows in collection after clean up: '
                                                                     || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                                     || ' - Error: '
                                                                     || SQLERRM,1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            Update_Last_Person_Parm(VN_previous_person_id);

            RETURN;

        END;  -- end process ORUZ05-E txns
-- CCR 1788 End

-- ***************************************************************************
-- * Process Status = COMPLETED (1506921)
-- *         Transmission Type = ORUZ11-E txns (1510408)
-- ***************************************************************************

        BEGIN  -- process ORUZ11-E txns (1510408)

          DBMS_APPLICATION_INFO.SET_CLIENT_INFO(TO_CHAR(SYSDATE,'DD-MON HH24:MI')
                                                || ' -P '  -- Person ID
                                                || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                || ' -T '  -- Msg Transmission ID
                                                || LTRIM(TO_CHAR(CC_tran_type_oruz11e,'99999990'))
                                                || '.');

          VI_rows_to_keep_count := 0; -- reset variable that determines how many rows to remove from archiving request table

          OPEN C_comp_oruz11e_rec;

          FETCH C_comp_oruz11e_rec
          BULK COLLECT INTO T_hl7_txns;

          CLOSE C_comp_oruz11e_rec;

          DBMS_APPLICATION_INFO.SET_CLIENT_INFO(TO_CHAR(SYSDATE,'DD-MON HH24:MI')
                                                || ' -P '  -- Person ID
                                                || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                || ' -T '  -- Msg Transmission ID
                                                || LTRIM(TO_CHAR(CC_tran_type_oruz11e,'99999990'))
                                                || ' -C '  -- First count of rows in collection
                                                || LTRIM(TO_CHAR(T_hl7_txns.COUNT,'999,990'))
                                                || '.');

          IF T_hl7_txns.COUNT > 0 THEN  -- completed ORUZ11-E txns exist

            IF T_hl7_txns.COUNT > VN_parm_keep_oruz11e_count THEN  -- there are more in the table than need to be kept

              VI_rows_to_keep_count := VN_parm_keep_oruz11e_count;  -- save how many need to be kept

            END IF;  -- end there are more in the table than need to be kept

          END IF;  -- end completed ORUZ11-E txns exist

          IF VI_rows_to_keep_count > 0 THEN -- remove rows from archiving request table

            DBMS_APPLICATION_INFO.SET_CLIENT_INFO(TO_CHAR(SYSDATE,'DD-MON HH24:MI')
                                                  || ' -P '  -- Person ID
                                                  || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                  || ' -T '  -- Msg Transmission ID
                                                  || LTRIM(TO_CHAR(CC_tran_type_oruz11e,'99999990'))
                                                  || ' -F '  -- Final count of rows in collection
                                                  || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                  || '.');

            VC_process_msg := 'COMPLETED ORUZ11-E txn. Person ID: '
                              || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                              || ' - Status Type ID: '
                              || CC_stat_type_complete
                              || ' - Msg Transmission Type ID: '
                              || CC_tran_type_oruz11e;

            Process_Archive_Rows(VC_process_msg); -- call INSERT/DELETE procedure

            IF VB_error THEN  -- error flag is set in sub-procedure if there were problems

              RETURN;

            END IF;

          END IF; -- end remove rows from archiving request table

        EXCEPTION

          WHEN OTHERS THEN

            VD_log_date := SYSDATE;

            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_log_date => VD_log_date,
                                      PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                      PC_job_log_message => SUBSTR('Failed in transmission ID ORUZ11-E processing step. Person_id: '
                                                                   || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                                   || ' - Transmission type ID: '
                                                                   || LTRIM(TO_CHAR(CC_tran_type_oruz11e,'99999990'))
                                                                   || ' - Rows in collection after clean up: '
                                                                   || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                                   || ' - Error: '
                                                                   || SQLERRM,1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_error_date  => VD_log_date,
                                      PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                      PC_job_error_message => SUBSTR('Failed in transmission ID ORUZ11-E processing step. Person_id: '
                                                                     || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                                     || ' - Transmission type ID: '
                                                                     || LTRIM(TO_CHAR(CC_tran_type_oruz11e,'99999990'))
                                                                     || ' - Rows in collection after clean up: '
                                                                     || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                                     || ' - Error: '
                                                                     || SQLERRM,1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            Update_Last_Person_Parm(VN_previous_person_id);

            RETURN;

        END;  -- end process ORUZ11-E txns

        BEGIN  -- Process Institution IDs for selected person

          OPEN C_inst_id_rec;

          FETCH C_inst_id_rec
          BULK COLLECT INTO T_institution_ids;

          CLOSE C_inst_id_rec;

          IF T_institution_ids.COUNT = 0 THEN  -- check if any institution ID rows exist

            VD_log_date := SYSDATE;

            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_log_date => VD_log_date,
                                      PC_job_status => SUBSTR('WARNING     - Fetch Institution ID Rows',1,60),
                                      PC_job_log_message => SUBSTR('No institution IDs found for Person_id '
                                                                   || VN_current_person_id
                                                                   || '.',1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

          ELSE

            FOR I_inst_id IN T_institution_ids.FIRST..T_institution_ids.LAST LOOP -- loop through institution IDs

              VN_institution_id := T_institution_ids(I_inst_id);

              DBMS_APPLICATION_INFO.SET_CLIENT_INFO(TO_CHAR(SYSDATE,'DD-MON HH24:MI:SS')
                                                    || ' - Person '
                                                    || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                    || ' - Inst '
                                                    || LTRIM(TO_CHAR(VN_institution_id,'99999990'))
                                                    || '.');

              BEGIN -- Process message statuses for selected person/institution

                OPEN C_msg_status_rec;

                FETCH C_msg_status_rec
                BULK COLLECT INTO T_status_types;

                CLOSE C_msg_status_rec;

                IF T_status_types.COUNT = 0 THEN  -- check if any msg status rows exist

                  VD_log_date := SYSDATE;

                  ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                            PD_job_log_date => VD_log_date,
                                            PC_job_status => SUBSTR('WARNING     - Fetch Message Status Rows',1,60),
                                            PC_job_log_message => SUBSTR('No message statuses found for Person ID '
                                                                         || VN_current_person_id
                                                                         || ' and Institution ID '
                                                                         || VN_institution_id
                                                                         || '.',1,2000),
                                            PD_date1 => VD_process_start_date,
                                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

                ELSE

                  FOR I_status_id IN T_status_types.FIRST..T_status_types.LAST LOOP -- loop through message statuses

                    VN_msg_status_id := T_status_types(I_status_id);

                    DBMS_APPLICATION_INFO.SET_CLIENT_INFO(TO_CHAR(SYSDATE,'DD-MON HH24:MI')
                                                          || ' -P '  -- Person ID
                                                          || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                          || ' -I '  -- Institution ID
                                                          || LTRIM(TO_CHAR(VN_institution_id,'99999990'))
                                                          || ' -S '  -- Status code ID
                                                          || LTRIM(TO_CHAR(VN_msg_status_id,'99999990'))
                                                          || '.');

                    BEGIN -- Process message transmission type for selected person/institution/status

                      OPEN C_msg_transtype_rec;

                      FETCH C_msg_transtype_rec
                      BULK COLLECT INTO T_trans_types;

                      CLOSE C_msg_transtype_rec;

                      IF T_trans_types.COUNT = 0 THEN  -- check if any msg transmission type rows exist

                        VD_log_date := SYSDATE;

                        ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                                  PD_job_log_date => VD_log_date,
                                                  PC_job_status => SUBSTR('WARNING     - Fetch Message Transmission Type Rows',1,60),
                                                  PC_job_log_message => SUBSTR('No message transmission types found for Person ID '
                                                                               || VN_current_person_id
                                                                               || ' and Institution ID '
                                                                               || VN_institution_id
                                                                               || ' and Status Type ID '
                                                                               || VN_msg_status_id
                                                                               || '.',1,2000),
                                                  PD_date1 => VD_process_start_date,
                                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

                      ELSE

                        FOR I_transtype_id IN T_trans_types.FIRST..T_trans_types.LAST LOOP -- loop through message transmission types

                          VN_msg_transtype_id := T_trans_types(I_transtype_id);

                          DBMS_APPLICATION_INFO.SET_CLIENT_INFO(TO_CHAR(SYSDATE,'DD-MON HH24:MI')
                                                                || ' -P'  -- Person ID
                                                                || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                                || ' -I'  -- Institution ID
                                                                || LTRIM(TO_CHAR(VN_institution_id,'99999990'))
                                                                || ' -S'  -- Status code ID
                                                                || LTRIM(TO_CHAR(VN_msg_status_id,'99999990'))
                                                                || ' -T'  -- Transmission type ID
                                                                || LTRIM(TO_CHAR(VN_msg_transtype_id,'99999990'))
                                                                || '.');

                          BEGIN  -- Load HL7 txn table (Data for distinct Person ID, Inst ID, Status Type, and Transmission Type)

                            VI_rows_to_keep_count := 0; -- reset variable that determines how many rows to remove from archiving request table

                            OPEN C_hl7_txn_rec;

                            FETCH C_hl7_txn_rec
                            BULK COLLECT INTO T_hl7_txns;

                            CLOSE C_hl7_txn_rec;

                            DBMS_APPLICATION_INFO.SET_CLIENT_INFO(TO_CHAR(SYSDATE,'DD-MON HH24:MI')
                                                                  || ' -P '  -- Person ID
                                                                  || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                                  || ' -I '  -- Institution ID
                                                                  || LTRIM(TO_CHAR(VN_institution_id,'99999990'))
                                                                  || ' -T '  -- Msg Transmission ID
                                                                  || LTRIM(TO_CHAR(VN_msg_transtype_id,'99999990'))
                                                                  || ' -C '  -- First count of rows in collection
                                                                  || LTRIM(TO_CHAR(T_hl7_txns.COUNT,'999,990'))
                                                                  || '.');

                          EXCEPTION

                            WHEN OTHERS THEN

                              VD_log_date := SYSDATE;

                              ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                                        PD_job_log_date => VD_log_date,
                                                        PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                                        PC_job_log_message => SUBSTR('Failed in completed transaction table load step. Person_id: '
                                                                                     || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                                                     || ' - Institution ID: '
                                                                                     || LTRIM(TO_CHAR(VN_institution_id,'99999990'))
                                                                                     || ' - Status code ID: '
                                                                                     || LTRIM(TO_CHAR(VN_msg_status_id,'99999990'))
                                                                                     || ' - Transmission type ID: '
                                                                                     || LTRIM(TO_CHAR(VN_msg_transtype_id,'99999990'))
                                                                                     || ' - Rows in collection after clean up: '
                                                                                     || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                                                     || ' - Error: '
                                                                                     || SQLERRM,1,2000),
                                                        PD_date1 => VD_process_start_date,
                                                        PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

                              ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                                        PD_job_error_date  => VD_log_date,
                                                        PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                                        PC_job_error_message => SUBSTR('Failed in completed transaction table load step. Person_id: '
                                                                                       || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                                                       || ' - Institution ID: '
                                                                                       || LTRIM(TO_CHAR(VN_institution_id,'99999990'))
                                                                                       || ' - Status code ID: '
                                                                                       || LTRIM(TO_CHAR(VN_msg_status_id,'99999990'))
                                                                                       || ' - Transmission type ID: '
                                                                                       || LTRIM(TO_CHAR(VN_msg_transtype_id,'99999990'))
                                                                                       || ' - Rows in collection after clean up: '
                                                                                       || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                                                       || ' - Error: '
                                                                                       || SQLERRM,1,2000),
                                                        PD_date1 => VD_process_start_date,
                                                        PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

                              Update_Last_Person_Parm(VN_previous_person_id);

                              RETURN;

                          END;  -- End Load HL7 txn table (Data for distinct Person ID, Inst ID, Status Type, and Transmission Type)

-- ***************************************************************************
-- * Process Status = COMPLETED (1506921)
-- *         Transmission Type = QRYZ10E (1510403)
-- *                             ORUZ11E (1510408)
-- *                             ORUZ04S (1510420)
-- *                             ORUZ05S (1510421)
-- *                             ORUZ10S (1510422)
-- *                             ORFZ10S (1510423)
-- *                             ORUZ11S (1510424)
-- ***************************************************************************

                          IF VN_msg_status_id = CC_stat_type_complete -- Process HL7 txn table (Complete/Uncomplete)
                           AND VN_msg_transtype_id IN (CC_tran_type_orfz10s,
                                                       CC_tran_type_orfz11s,  -- CCR 1788
                                                       CC_tran_type_oruz04s,
                                                       CC_tran_type_oruz05s,
                                                       CC_tran_type_oruz10s,
                                                       CC_tran_type_oruz11s,
                                                       CC_tran_type_qryz10e,
                                                       CC_tran_type_qryz11e) THEN  -- CCR 1788
                          -- process completed txns (status 1506921) w/ currently defined transmission types

                            IF T_hl7_txns.COUNT = 0 THEN  -- completed txn table count selection
                            -- No rows for this person/institution/status type/transmission type (this shouldn't happen)
                              VD_log_date := SYSDATE;

                              ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                                        PD_job_log_date => VD_log_date,
                                                        PC_job_status => SUBSTR('WARNING     - Fetch HL7 Txn Rows',1,60),
                                                        PC_job_log_message => SUBSTR('No HL7 Txns found for Person ID '
                                                                                     || VN_current_person_id
                                                                                     || ' and Institution ID '
                                                                                     || VN_institution_id
                                                                                     || ' and Status Type '
                                                                                     || VN_msg_status_id
                                                                                     || ' and Transmission ID '
                                                                                     || VN_msg_transtype_id
                                                                                     || '.',1,2000),
                                                        PD_date1 => VD_process_start_date,
                                                        PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

                            ELSIF VN_msg_transtype_id = CC_tran_type_orfz10s THEN

                              IF T_hl7_txns.COUNT > VN_parm_keep_orfz10s_count THEN

                                  VI_rows_to_keep_count := VN_parm_keep_orfz10s_count;

                              END IF;

                            ELSIF VN_msg_transtype_id = CC_tran_type_orfz11s THEN  -- CCR 1788 Begin

                              IF T_hl7_txns.COUNT > VN_parm_keep_orfz11s_count THEN

                                  VI_rows_to_keep_count := VN_parm_keep_orfz11s_count;

                              END IF;  --CCR 1788 End

                            ELSIF VN_msg_transtype_id = CC_tran_type_oruz04s THEN

                              IF T_hl7_txns.COUNT > VN_parm_keep_oruz04s_count THEN

                                  VI_rows_to_keep_count := VN_parm_keep_oruz04s_count;

                              END IF;

                            ELSIF VN_msg_transtype_id = CC_tran_type_oruz05s THEN

                              IF T_hl7_txns.COUNT > VN_parm_keep_oruz05s_count THEN

                                  VI_rows_to_keep_count := VN_parm_keep_oruz05s_count;

                              END IF;

                            ELSIF VN_msg_transtype_id = CC_tran_type_oruz10s THEN

                              IF T_hl7_txns.COUNT > VN_parm_keep_oruz10s_count THEN

                                  VI_rows_to_keep_count := VN_parm_keep_oruz10s_count;

                              END IF;

                            ELSIF VN_msg_transtype_id = CC_tran_type_oruz11s THEN

                              IF T_hl7_txns.COUNT > VN_parm_keep_oruz11s_count THEN

                                  VI_rows_to_keep_count := VN_parm_keep_oruz11s_count;

                              END IF;

                            ELSIF VN_msg_transtype_id = CC_tran_type_qryz10e THEN

                              IF T_hl7_txns.COUNT > VN_parm_keep_qryz10e_count THEN

                                VI_rows_to_keep_count := VN_parm_keep_qryz10e_count;

                              END IF;

                            ELSIF VN_msg_transtype_id = CC_tran_type_qryz11e THEN  -- CCR 1788 Begin

                              IF T_hl7_txns.COUNT > VN_parm_keep_qryz11e_count THEN

                                VI_rows_to_keep_count := VN_parm_keep_qryz11e_count;

                              END IF;  -- CCR 1788 End

                            END IF;  -- end completed txn table count selection

                            IF VI_rows_to_keep_count > 0 THEN -- remove completed rows from archiving request table

                              DBMS_APPLICATION_INFO.SET_CLIENT_INFO(TO_CHAR(SYSDATE,'DD-MON HH24:MI')
                                                                    || ' -P '  -- Person ID
                                                                    || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                                    || ' -I '  -- Institution ID
                                                                    || LTRIM(TO_CHAR(VN_institution_id,'99999990'))
                                                                    || ' -T '  -- Msg Transmission ID
                                                                    || LTRIM(TO_CHAR(VN_msg_transtype_id,'99999990'))
                                                                    || ' -F '  -- Final count of rows in collection
                                                                    || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                                    || '.');

                              VC_process_msg := 'COMPLETED non-ORUZ11-E txns. Person ID: '
                                                || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                || ' - Institution ID: '
                                                || LTRIM(TO_CHAR(VN_institution_id,'99999990'))
                                                || ' - Status code ID: '
                                                || LTRIM(TO_CHAR(VN_msg_status_id,'99999990'))
                                                || ' - Transmission type ID: '
                                                || LTRIM(TO_CHAR(VN_msg_transtype_id,'99999990'));

                              Process_Archive_Rows(VC_process_msg); -- call INSERT/DELETE procedure

                              IF VB_error THEN  -- error flag is set in sub-procedure if there were problems

                                RETURN;

                              END IF;

                            END IF; -- end remove completed rows from archiving request table

-- ***************************************************************************
-- * Process Status = ERROR      (1506927)
-- *                  NOT_TRANS  (1506926)
-- *                  PENDING    (1506924)
-- *                  RETRANS    (1506925)
-- *                  RTRAN_FAIL (1506928)
-- *                  WAIT_ACK   (1506923)
-- *                  WAIT_TRAN  (1506922)
-- *         Transmission Type = ALL
-- ***************************************************************************

                          ELSIF VN_msg_status_id IN (CC_stat_type_uncomp_error,            -- 1506927
                                                     CC_stat_type_uncomp_not_trans,        -- 1506926
                                                     CC_stat_type_uncomp_pending,          -- 1506924
                                                     CC_stat_type_uncomp_retrans,          -- 1506925
                                                     CC_stat_type_uncomp_rtran_fail,       -- 1506928
                                                     CC_stat_type_uncomp_wait_ack,         -- 1506923
                                                     CC_stat_type_uncomp_wait_tran) THEN   -- 1506922
                          -- process all uncompleted status txns (uncompleted status types all process the same)

                            IF T_hl7_txns.COUNT = 0 THEN  -- uncompleted txn table count selection
                            -- No rows for this person/institution/status type/transmission type (this shouldn't happen)
                              VD_log_date := SYSDATE;

                              ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                                        PD_job_log_date => VD_log_date,
                                                        PC_job_status => SUBSTR('WARNING     - Fetch HL7 Txn Rows',1,60),
                                                        PC_job_log_message => SUBSTR('No HL7 Txns found for Person ID '
                                                                                     || VN_current_person_id
                                                                                     || ' and Institution ID '
                                                                                     || VN_institution_id
                                                                                     || ' and Status Type '
                                                                                     || VN_msg_status_id
                                                                                     || ' and Transmission ID '
                                                                                     || VN_msg_transtype_id
                                                                                     || '.',1,2000),
                                                        PD_date1 => VD_process_start_date,
                                                        PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

                            ELSE

                              IF T_hl7_txns(T_hl7_txns.LAST).Record_created_date <= VD_process_start_date - VN_parm_keep_uncompleted_days THEN  -- check for rows to keep
    -- No rows exist that are newer than the archive cutoff date, so remove the newest row from the archiving list so that it stays in the HL7 table.
                                VI_rows_to_keep_count := 1; -- remove the last entry in the collection table

                              ELSE
    -- Remove all rows that are newer than the archive cutoff date from the archiving list.
    -- (TRIM deletes starting with the highest indexed row in the collection so we index in reverse order [highest to lowest])
                                FOR I_uncomp_hl7_txns IN REVERSE T_hl7_txns.FIRST..T_hl7_txns.LAST LOOP

                                  IF T_hl7_txns(I_uncomp_hl7_txns).Record_created_date > VD_process_start_date - VN_parm_keep_uncompleted_days THEN

                                    VI_rows_to_keep_count := VI_rows_to_keep_count + 1; -- remove another entry in the collection table starting with the last one.

                                  ELSE

                                    EXIT;

                                  END IF;

                                END LOOP;

                              END IF;  -- end check for rows to keep

                              IF T_hl7_txns.COUNT > VI_rows_to_keep_count THEN  -- rows exist that are older than the archive cutoff date.

                                DBMS_APPLICATION_INFO.SET_CLIENT_INFO(TO_CHAR(SYSDATE,'DD-MON HH24:MI')
                                                                      || ' -P '  -- Person ID
                                                                      || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                                      || ' -I '  -- Institution ID
                                                                      || LTRIM(TO_CHAR(VN_institution_id,'99999990'))
                                                                      || ' -S '  -- Status code ID
                                                                      || LTRIM(TO_CHAR(VN_msg_status_id,'99999990'))
                                                                      || ' -F '  -- Final count of rows in collection after clean up
                                                                      || LTRIM(TO_CHAR(T_hl7_txns.COUNT - VI_rows_to_keep_count,'999,990'))
                                                                      || '.');

                                VC_process_msg := 'UNCOMPLETED txns. Person ID: '
                                                  || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                  || ' - Institution ID: '
                                                  || LTRIM(TO_CHAR(VN_institution_id,'99999990'))
                                                  || ' - Status code ID: '
                                                  || LTRIM(TO_CHAR(VN_msg_status_id,'99999990'))
                                                  || ' - Transmission type ID: '
                                                  || LTRIM(TO_CHAR(VN_msg_transtype_id,'99999990'));

                                Process_Archive_Rows(VC_process_msg); -- call INSERT/DELETE procedure

                                IF VB_error THEN  -- error flag is set in sub-procedure if there were problems

                                  RETURN;

                                END IF;

                              END IF;  -- end rows exist that are older than the archive cutoff date.

                            END IF; -- end uncompleted txn table count selection

                          END IF;  -- end Process HL7 txn table (Complete/Uncomplete) (ignore any unknown status/tranmission types)

                        END LOOP;  -- end loop through message transmission types

                      END IF;   -- end check if any msg transmission type rows exist

                    EXCEPTION

                      WHEN OTHERS THEN

                        VD_log_date := SYSDATE;

                        ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                                  PD_job_log_date => VD_log_date,
                                                  PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                                  PC_job_log_message => SUBSTR('Failed to retrieve message transmission types. Person_id: '
                                                                               || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                                               || ' - Institution ID: '
                                                                               || LTRIM(TO_CHAR(VN_institution_id,'99999990'))
                                                                               || ' - Status code ID: '
                                                                               || LTRIM(TO_CHAR(VN_msg_status_id,'99999990'))
                                                                               || ' - Error: '
                                                                               || SQLERRM,1,2000),
                                                  PD_date1 => VD_process_start_date,
                                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

                        ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                                  PD_job_error_date  => VD_log_date,
                                                  PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                                  PC_job_error_message => SUBSTR('Failed to retrieve message transmission types. Person_id: '
                                                                                 || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                                                 || ' - Institution ID: '
                                                                                 || LTRIM(TO_CHAR(VN_institution_id,'99999990'))
                                                                                 || ' - Status code ID: '
                                                                                 || LTRIM(TO_CHAR(VN_msg_status_id,'99999990'))
                                                                                 || ' - Error: '
                                                                                 || SQLERRM,1,2000),
                                                  PD_date1 => VD_process_start_date,
                                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

                        Update_Last_Person_Parm(VN_previous_person_id);

                        RETURN;

                    END;  -- End Process message transmission type for selected person/institution/status

                  END LOOP;  -- end loop through message statuses

                END IF;  -- end check if any msg status rows exist

              EXCEPTION

                WHEN OTHERS THEN

                  VD_log_date := SYSDATE;

                  ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                            PD_job_log_date => VD_log_date,
                                            PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                            PC_job_log_message => SUBSTR('Failed to retrieve message status IDs. Person_id: '
                                                                         || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                                         || ' - Institution ID: '
                                                                         || LTRIM(TO_CHAR(VN_institution_id,'99999990'))
                                                                         || ' - Error: '
                                                                         || SQLERRM,1,2000),
                                            PD_date1 => VD_process_start_date,
                                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

                  ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                            PD_job_error_date  => VD_log_date,
                                            PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                            PC_job_error_message => SUBSTR('Failed to retrieve message status IDs. Person_id: '
                                                                           || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                                           || ' - Institution ID: '
                                                                           || LTRIM(TO_CHAR(VN_institution_id,'99999990'))
                                                                           || ' - Error: '
                                                                           || SQLERRM,1,2000),
                                            PD_date1 => VD_process_start_date,
                                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

                  Update_Last_Person_Parm(VN_previous_person_id);

                  RETURN;

              END;  -- end process message statuses for selected person/institution

            END LOOP;  -- end loop through institution IDs

          END IF;  -- end check if any institution ID rows exist

        EXCEPTION

          WHEN OTHERS THEN

            VD_log_date := SYSDATE;

            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_log_date => VD_log_date,
                                      PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                      PC_job_log_message => SUBSTR('Failed to retrieve institution IDs. Person_id: '
                                                                   || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                                   || ' - Error: '
                                                                   || SQLERRM,1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_error_date  => VD_log_date,
                                      PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                      PC_job_error_message => SUBSTR('Failed to retrieve institution IDs. Person_id: '
                                                                     || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                                     || ' - Error: '
                                                                     || SQLERRM,1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            Update_Last_Person_Parm(VN_previous_person_id);

            RETURN;

        END;  -- end process institution IDs for selected person

        BEGIN  -- check if stop was requested

          SELECT    AP.Value
          INTO      VN_process_control
          FROM      Adr_Parameter AP
          WHERE     Application_name = 'HL7_ARCHIVE_CONTROL'
          AND       Parameter_name = 'START_STOP_PROCESS';

          IF VN_process_control = 0 THEN

            VD_log_date := SYSDATE;

            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_log_date => VD_log_date,
                                      PC_job_status => SUBSTR('INFORMATION - Archive Process Loop',1,60),
                                      PC_job_log_message => SUBSTR('Application has cancelled the archiving process.',1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            VD_log_date := SYSDATE;

            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_log_date => VD_log_date,
                                      PC_job_status => SUBSTR('INFORMATION - Archive Process Loop',1,60),
                                      PC_job_log_message => SUBSTR('Number of Person_ids processed: '
                                                                   || TO_CHAR(VN_person_count,'99,999,999,990'),1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            VD_log_date := SYSDATE;

-- archive row count is accumulated in the Process_Archive_Rows procedure
            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_log_date => VD_log_date,
                                      PC_job_status => SUBSTR('INFORMATION - Archive Process Loop',1,60),
                                      PC_job_log_message => SUBSTR('Number of rows archived: '
                                                                   || TO_CHAR(VN_archive_count,'99,999,999,990'),1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            Update_Last_Person_Parm(VN_previous_person_id);

            DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Archiving Processing Cancelled');

            EXIT;

          END IF;

        EXCEPTION

          WHEN OTHERS THEN

            VD_log_date := SYSDATE;

            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_log_date => VD_log_date,
                                      PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                      PC_job_log_message => SUBSTR('Unable to retrieve parameter START_STOP_PROCESS.'
                                                                   || ' Terminating execution of the job.'|| SQLERRM,1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_error_date  => VD_log_date,
                                      PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                      PC_job_error_message => SUBSTR('Failure in archive processing loop - Unable to retrieve parameter START_STOP_PROCESS. '
                                                                     || SQLERRM,1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            Update_Last_Person_Parm(VN_previous_person_id);

            RETURN;

        END;  -- end check if stop was requested

      EXCEPTION

        WHEN NO_DATA_FOUND THEN -- all rows have been processed

          VD_log_date := SYSDATE;

          ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                    PD_job_log_date => VD_log_date,
                                    PC_job_status => SUBSTR('INFORMATION - Archive Process Loop - COMPLETED',1,60),
                                    PC_job_log_message => SUBSTR('All Person_ids have been processed.',1,2000),
                                    PD_date1 => VD_process_start_date,
                                    PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

          VD_log_date := SYSDATE;

          ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                    PD_job_log_date => VD_log_date,
                                    PC_job_status => SUBSTR('INFORMATION - Archive Process Loop - COMPLETED',1,60),
                                    PC_job_log_message => SUBSTR('Number of Person_ids processed: '
                                                                 || TO_CHAR(VN_person_count,'99,999,999,990'),1,2000),
                                    PD_date1 => VD_process_start_date,
                                    PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));
          VD_log_date := SYSDATE;

          ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                    PD_job_log_date => VD_log_date,
                                    PC_job_status => SUBSTR('INFORMATION - Archive Process Loop - COMPLETED',1,60),
                                    PC_job_log_message => SUBSTR('Number of rows archived: '
                                                                 || TO_CHAR(VN_archive_count,'99,999,999,990'),1,2000),
                                    PD_date1 => VD_process_start_date,
                                    PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

          DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Archive Processing Completed');

          BEGIN

            UPDATE    Adr_Parameter AP
            SET       AP.Value = 0
            WHERE     Application_name = 'HL7_ARCHIVE_CONTROL'
            AND       Parameter_name = 'START_STOP_PROCESS';

            COMMIT;

          EXCEPTION

            WHEN OTHERS THEN

              ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                        PD_job_log_date => VD_log_date,
                                        PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                        PC_job_log_message => SUBSTR('Unable to update parameter START_STOP_PROCESS.'
                                                                     || ' Terminating execution of the job.'|| SQLERRM,1,2000),
                                        PD_date1 => VD_process_start_date,
                                        PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

              ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                        PD_job_error_date  => VD_log_date,
                                        PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                        PC_job_error_message => SUBSTR('Failure in archive processing loop - Unable to update parameter START_STOP_PROCESS. '
                                                                       || SQLERRM,1,2000),
                                        PD_date1 => VD_process_start_date,
                                        PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

          END;

          Update_Last_Person_Parm(0);

          EXIT;

        WHEN OTHERS THEN

          VD_log_date := SYSDATE;

          ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                    PD_job_log_date => VD_log_date,
                                    PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                    PC_job_log_message => SUBSTR('Failed to retrieve next Person ID. Previous Person_id: '
                                                                 || LTRIM(TO_CHAR(VN_previous_person_id,'9999999999990'))
                                                                 || ' Current Person_id'
                                                                 || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                                 || ' - Error: '
                                                                 || SQLERRM,1,2000),
                                    PD_date1 => VD_process_start_date,
                                    PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

          ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                    PD_job_error_date  => VD_log_date,
                                    PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                    PC_job_error_message => SUBSTR('Failed to retrieve next Person ID. Previous Person_id: '
                                                                 || LTRIM(TO_CHAR(VN_previous_person_id,'9999999999990'))
                                                                 || ' Current Person_id'
                                                                 || LTRIM(TO_CHAR(VN_current_person_id,'9999999999990'))
                                                                 || ' - Error: '
                                                                 || SQLERRM,1,2000),
                                    PD_date1 => VD_process_start_date,
                                    PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

          Update_Last_Person_Parm(VN_previous_person_id);

          RETURN;

      END;  -- end get next person_id to process

      VN_previous_person_id := VN_current_person_id;

    END LOOP;  -- end main archive processing loop

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - Archive Process Loop',1,60),
                                PC_job_log_message => SUBSTR('Failure in archive processing loop.'
                                                             || ' Terminating execution of the job.'|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Failure in archive processing loop. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      Update_Last_Person_Parm(VN_previous_person_id);

      RETURN;

  END;  -- end main processing block

  VD_process_end_date := SYSDATE;

  VN_process_time_in_secs := (VD_process_end_date - VD_process_start_date)*24*60*60;

  VN_process_days := TRUNC(VN_process_time_in_secs/24/60/60);

  VN_process_hours := TRUNC((VN_process_time_in_secs -
                             (VN_process_days*24*60*60))/60/60);

  VN_process_minutes := TRUNC((VN_process_time_in_secs -
                               (VN_process_days*24*60*60) -
                                 (VN_process_hours*60*60))/60);

  VN_process_seconds := TRUNC(VN_process_time_in_secs -
                               (VN_process_days*24*60*60) -
                                 (VN_process_hours*60*60) -
                                   (VN_process_minutes*60));

  VC_process_duration := LTRIM(TO_CHAR(VN_process_days,'9990')) || ' Days '
                         || LTRIM(TO_CHAR(VN_process_hours,'90')) || ' Hrs '
                         || LTRIM(TO_CHAR(VN_process_minutes,'90')) || ' Mins '
                         || LTRIM(TO_CHAR(VN_process_seconds,'90')) || ' Secs';

  VD_log_date := SYSDATE;

  ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                            PD_job_log_date => VD_log_date,
                            PC_job_status => SUBSTR('INFORMATION - Process Completion',1,60),
                            PC_job_log_message => 'Elapsed processing time: ' || VC_process_duration,
                            PD_date1 => VD_process_start_date,
                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

EXCEPTION

  WHEN OTHERS THEN

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('ERROR       - Unknown Error',1,60),
                              PC_job_log_message => SUBSTR('Unknown error archiving HL7 rows.'
                                                           || ' Terminating execution of the job.'|| SQLERRM,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_error_date  => VD_log_date,
                              PC_job_error_code => SUBSTR(SQLCODE,1,60),
                              PC_job_error_message => SUBSTR('Unknown error archiving HL7 rows. '
                                                             || SQLERRM,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    RETURN;

END Hl7_Process_Archives;

/*****************************************************************************
 *
 *    NAME: Hl7_Process_Deletes
 *    TYPE: Procedure
 *
 *    DESCRIPTION
 *      Used to delete rows from the Hl7_Archive_Transaction_log table based
 *      pm the aging of the Creation_date column.
 *
 *  PB_run_standalone   Ignore DBMS_JOB logic for checking running jobs.
 *                       Default: FALSE
 *
 *  PB_allow_rpt_inst   Allow stored procedure to be submitted in the
 *                      DBMS_JOB queue on the ADR reporting databases
 *                       Default: FALSE
 *
 *****************************************************************************
 */

PROCEDURE Hl7_Process_Deletes
  (PB_run_standalone
     IN     BOOLEAN DEFAULT FALSE,
   PB_allow_rpt_inst
     IN     BOOLEAN DEFAULT FALSE
  )
IS

-- CONSTANTS

  CC_process_name                      VARCHAR2(60) := 'HL7 Archiver - Delete Archived Rows';

-- VARIABLES

  VB_allow_rpt_inst                    BOOLEAN := FALSE;
  VB_run_standalone                    BOOLEAN := FALSE;

  VC_allow_rpt_inst                    VARCHAR2(5) := NULL;
  VC_instance_name                     VARCHAR2(16);
  VC_process_duration                  VARCHAR2(50);
  VC_run_standalone                    VARCHAR2(5) := NULL;

  VD_log_date                          DATE;
  VD_process_end_date                  DATE;
  VD_process_start_date                DATE;
  VD_del_cutoff_date                   DATE;

  VN_arch_rows_age_to_keep             NUMBER := 365;
  VN_delete_count                      NUMBER := 0;
  VN_job_count                         NUMBER := 0;
  VN_log_age_to_keep                   NUMBER := 180;
  VN_num_rows_to_process               NUMBER := 20000;
  VN_process_control                   NUMBER := 0;
  VN_process_days                      NUMBER;
  VN_process_hours                     NUMBER;
  VN_process_minutes                   NUMBER;
  VN_process_seconds                   NUMBER;
  VN_process_time_in_secs              NUMBER;
  VN_total_deletes_count               NUMBER :=0;

  TYPE TT_pk_id IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

  T_pk_id                              TT_pk_id;

  CURSOR C_hl7_arch_rec IS
    SELECT    Hl7_transaction_log_id
    FROM      Hl7_Archive_Transaction_Log
    WHERE     Transmission_date <= VD_del_cutoff_date + 2 -- Transmission_date may be up to 2 days newer that Record_created_date
    AND       Record_created_date <= VD_del_cutoff_date;

-- EXCEPTIONS

   EE_DML_ERROR                        EXCEPTION;
   PRAGMA                              EXCEPTION_INIT(EE_DML_ERROR, -24381);

BEGIN

  DBMS_APPLICATION_INFO.SET_MODULE(Module_name => 'Hl7_Process_Deletes',
                                   Action_name => NULL);

  VD_process_start_date := SYSDATE;

  VD_log_date := SYSDATE;

  ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                            PD_job_log_date => VD_log_date,
                            PC_job_status => SUBSTR('INFORMATION - Process Initiation',1,60),
                            PC_job_log_message => NULL,
                            PD_date1 => VD_process_start_date,
                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

-- ***************************************************************************
-- *
-- * Validate input parameters
-- *
-- ***************************************************************************

  DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Validating Input Parameters');

-- * Check run standalone flag

  IF PB_run_standalone IS NULL
   OR NOT PB_run_standalone THEN

    VB_run_standalone := FALSE;
    VC_run_standalone := 'FALSE'; -- used for log message, boolean can't be displayed

  ELSE

    VB_run_standalone := TRUE;
    VC_run_standalone := 'TRUE'; -- used for log message, boolean can't be displayed

  END IF;

-- * Check run on report instance flag

  IF PB_allow_rpt_inst IS NULL
   OR NOT PB_allow_rpt_inst THEN

    VB_allow_rpt_inst := FALSE;
    VC_allow_rpt_inst := 'FALSE'; -- used for log message, boolean can't be displayed

  ELSE

    VB_allow_rpt_inst := TRUE;
    VC_allow_rpt_inst := 'TRUE'; -- used for log message, boolean can't be displayed

  END IF;

  VD_log_date := SYSDATE;

  ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                            PD_job_log_date => VD_log_date,
                            PC_job_status => SUBSTR('INFORMATION - Command Line Parameters',1,60),
                            PC_job_log_message => SUBSTR('Current job command line parameters - '
                                                      || ' Run standalone: ' || VC_run_standalone
                                                      || ' Run on reporting databases: ' || VC_allow_rpt_inst,1,2000),
                            PD_date1 => VD_process_start_date,
                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

-- ***************************************************************************
-- *
-- * Check to see if process is running on a reporting database.
-- *  - If VB_allow_rpt_inst is FALSE, then it's not allowed. End the job.
-- *
-- ***************************************************************************

  DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Check if running on reporting DB');

  BEGIN

    SELECT    Instance_name
    INTO      VC_instance_name
    FROM      V$instance;

    VD_log_date := SYSDATE;
    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Instance Name' ,1,60),
                              PC_job_log_message => SUBSTR('Job is running on instance ' || UPPER(VC_instance_name) || '.',1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    IF NOT VB_allow_rpt_inst
     AND UPPER(SUBSTR(VC_instance_name,1,4)) = 'ADRR' THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - Reporting Database Check',1,60),
                                PC_job_log_message => SUBSTR('Attempting to run on a reporting database with execution parameter'
                                                          || ' VB_allow_rpt_inst set to FALSE. This job has been terminated.'
                                                          || ' To run on a reporting instance, set parameter VB_allow_rpt_inst'
                                                          || ' to TRUE and resubmit the job.',1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

    END IF;

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - Reporting Database Check',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve instance name. Terminating script.',1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve instance name. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- ***************************************************************************
-- *
-- * Check to see if any other jobs of this procedure are running.
-- *  - If yes, then end this job.
-- *
-- ***************************************************************************

  DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Check for duplicate jobs running');

  SELECT COUNT(*)
  INTO   VN_job_count
  FROM   SYS.DBA_JOBS J
  WHERE  UPPER(J.WHAT) LIKE '%HL7_PROCESS_DELETES%';

  IF VN_job_count > 1 THEN

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('ERROR       - Multiple Execution Check',1,60),
                              PC_job_log_message => SUBSTR('There is another instance of this job already in the job queue.'
                                                        || ' Exiting job without running.',1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    RETURN;

  ELSE

    IF VB_run_standalone THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('INFORMATION - Execution Mode',1,60),
                                PC_job_log_message => SUBSTR('Starting in STANDALONE mode.',1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    ELSE

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('INFORMATION - Execution Mode',1,60),
                                PC_job_log_message => SUBSTR('Starting in DBMS_JOB mode.',1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    END IF;

  END IF;

-- ***************************************************************************
-- *
-- * Retrieve modifiable parameters
-- *
-- *  If the process control parameter = 0 then the job returns control to
-- *  to DBMS_JOB and waits until it is called to run again.
-- *
-- ***************************************************************************

  DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Fetching user parameters');

  BEGIN

    SELECT    AP.Value
    INTO      VN_process_control
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_DELETE_CONTROL'
    AND       Parameter_name = 'START_STOP_PROCESS';

    IF VN_process_control = 0 THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('INFORMATION - Process Control',1,60),
                                PC_job_log_message => SUBSTR('Start/Stop control flag set to STOP (0). Going back to sleep.',1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

    ELSE

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('INFORMATION - Process Control',1,60),
                                PC_job_log_message => SUBSTR('Start/Stop control flag set to START (1). Delete process starting.',1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    END IF;

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - Process Control',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve parameter START_STOP_PROCESS.'
                                                             || ' Terminating execution of the job.'|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve parameter START_STOP_PROCESS. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

  BEGIN

    SELECT    AP.Value
    INTO      VN_log_age_to_keep
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_PROCESS_DELETES'
    AND       Parameter_name = 'OLDEST LOG FILE DAYS KEPT';

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - User Defined Parameters',1,60),
                              PC_job_log_message => SUBSTR('Parameter: OLDEST LOG FILE DAYS KEPT  - Value: '|| VN_log_age_to_keep,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - User Defined Parameters',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve parameter OLDEST LOG FILE DAYS KEPT.'
                                                             || ' Terminating execution of the job.'|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve parameter OLDEST LOG FILE DAYS KEPT. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

  BEGIN

    SELECT    AP.Value
    INTO      VN_num_rows_to_process
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_PROCESS_DELETES'
    AND       Parameter_name = 'ROWS TO PROCESS';

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - User Defined Parameters',1,60),
                              PC_job_log_message => SUBSTR('Parameter: ROWS TO PROCESS  - Value: '|| VN_num_rows_to_process,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - User Defined Parameters',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve parameter ROWS TO PROCESS.'
                                                            || ' Terminating execution of the job.'|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve parameter ROWS TO PROCESS. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

  BEGIN

    SELECT    AP.Value
    INTO      VN_arch_rows_age_to_keep
    FROM      Adr_Parameter AP
    WHERE     Application_name = 'HL7_PROCESS_DELETES'
    AND       Parameter_name = 'OLDEST ARCHIVED HL7 DAYS NOT DELETED';

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - User Defined Parameters',1,60),
                              PC_job_log_message => SUBSTR('Parameter: OLDEST ARCHIVED HL7 DAYS NOT DELETED  - Value: '|| VN_arch_rows_age_to_keep,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    VD_del_cutoff_date := TRUNC(VD_process_start_date) - VN_arch_rows_age_to_keep;

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - User Defined Parameters',1,60),
                                PC_job_log_message => SUBSTR('Unable to retrieve parameter OLDEST ARCHIVED HL7 DAYS NOT DELETED.'
                                                             || ' Terminating execution of the job.'|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to retrieve parameter OLDEST ARCHIVED HL7 DAYS NOT DELETED. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  END;

-- ***************************************************************************
-- *
-- * Remove outdated log and error file rows
-- *
-- ***************************************************************************

  DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Removing old logs and error rows');

  BEGIN

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Log File Cleanup',1,60),
                              PC_job_log_message => SUBSTR('Removing Error Logs more than '|| VN_arch_rows_age_to_keep || 'days old.',1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    DELETE
    FROM      Job_Process_Errs
    WHERE     Job_name = CC_process_name
    AND       Date1 < VD_process_start_date - VN_log_age_to_keep;

    COMMIT;

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - Log file cleanup',1,60),
                                PC_job_log_message => SUBSTR('Unable to remove rows from error log table.'
                                                             || ' Terminating execution of the job.'|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to remove rows from error log table. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

  BEGIN

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Log File Cleanup',1,60),
                              PC_job_log_message => SUBSTR('Removing Process Logs more than '|| VN_arch_rows_age_to_keep || 'days old.',1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    DELETE
    FROM      Job_Process_Logs
    WHERE     Job_name = CC_process_name
    AND       Date1 < VD_process_start_date - VN_log_age_to_keep;

    COMMIT;

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - Log file cleanup',1,60),
                                PC_job_log_message => SUBSTR('Unable to remove rows from process log table.'
                                                             || ' Terminating execution of the job.'|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Unable to remove rows from process log table. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- ***************************************************************************
-- *
-- * Main processing loop
-- *
-- ***************************************************************************

  BEGIN  -- main processing loop block

    DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Processing Deletes');

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Delete Process Loop',1,60),
                              PC_job_log_message => SUBSTR('Starting deletion of archived HL7 rows more than '
                                                           || VN_arch_rows_age_to_keep
                                                           || ' days old.',1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    VN_total_deletes_count :=0;

    LOOP  -- main processing loop

      OPEN C_hl7_arch_rec;

      FETCH C_hl7_arch_rec
      BULK COLLECT INTO T_pk_id
      LIMIT VN_num_rows_to_process;

      CLOSE C_hl7_arch_rec;

      IF T_pk_id.COUNT = 0 THEN  -- if collection table is empty, all rows processed

        VD_log_date := SYSDATE;

        ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                  PD_job_log_date => VD_log_date,
                                  PC_job_status => SUBSTR('INFORMATION - Delete Process Loop - COMPLETED',1,60),
                                  PC_job_log_message => SUBSTR('All archived HL7 rows more than '
                                                               || VN_arch_rows_age_to_keep
                                                               || ' days old have been deleted.',1,2000),
                                  PD_date1 => VD_process_start_date,
                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

        VD_log_date := SYSDATE;

        ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                  PD_job_log_date => VD_log_date,
                                  PC_job_status => SUBSTR('INFORMATION - Delete Process Loop - COMPLETED',1,60),
                                  PC_job_log_message => SUBSTR('Total number of rows deleted: '
                                                               || TO_CHAR(VN_total_deletes_count,'999,999,999,990'),1,2000),
                                  PD_date1 => VD_process_start_date,
                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

        DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Delete Processing Completed');

        BEGIN

          UPDATE    Adr_Parameter AP
          SET       AP.Value = 0
          WHERE     Application_name = 'HL7_DELETE_CONTROL'
          AND       Parameter_name = 'START_STOP_PROCESS';

          COMMIT;

        EXCEPTION

          WHEN OTHERS THEN

            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_log_date => VD_log_date,
                                      PC_job_status => SUBSTR('ERROR       - Delete Process Loop',1,60),
                                      PC_job_log_message => SUBSTR('Unable to update parameter START_STOP_PROCESS(1).'
                                                                   || ' Terminating execution of the job.'|| SQLERRM,1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_error_date  => VD_log_date,
                                      PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                      PC_job_error_message => SUBSTR('Failure in delete processing loop - Unable to update parameter START_STOP_PROCESS(1). '
                                                                     || SQLERRM,1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

        END;

        EXIT;

      ELSE  -- still rows to process

        BEGIN

          FORALL I_pk_id IN T_pk_id.FIRST..T_pk_id.LAST SAVE EXCEPTIONS
            DELETE
            FROM      Hl7_Archive_Transaction_Log
            WHERE     Hl7_Transaction_Log_id = T_pk_id(I_pk_id);

            VN_delete_count := SQL%ROWCOUNT;

        EXCEPTION

          WHEN EE_DML_ERROR THEN

            VN_delete_count := SQL%ROWCOUNT;

            VD_log_date := SYSDATE;

            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_log_date => VD_log_date,
                                      PC_job_status => SUBSTR('ERROR       - Delete Process Loop',1,60),
                                      PC_job_log_message => SUBSTR('All rows not deleted.'
                                                                   || ' - Rows in collection: '
                                                                   || LTRIM(TO_CHAR(T_pk_id.COUNT,'999,990'))
                                                                   || '. Error count: '
                                                                   || LTRIM(TO_CHAR(SQL%BULK_EXCEPTIONS.COUNT,'999,990'))
                                                                   || '. Rows attempted to delete: '
                                                                   || LTRIM(TO_CHAR(VN_delete_count,'999,990')),1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_error_date  => VD_log_date,
                                      PC_job_error_code => NULL,
                                      PC_job_error_message => SUBSTR('All rows not deleted. '
                                                                     || ' - Rows in collection: '
                                                                     || LTRIM(TO_CHAR(T_pk_id.COUNT,'999,990'))
                                                                     || '. Error count: '
                                                                     || LTRIM(TO_CHAR(SQL%BULK_EXCEPTIONS.COUNT,'999,990'))
                                                                     || '. Rows attempted to delete: '
                                                                     || LTRIM(TO_CHAR(VN_delete_count,'999,990')),1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            FOR I_del_err IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP

              VD_log_date := SYSDATE;

              ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                        PD_job_log_date => VD_log_date,
                                        PC_job_status => SUBSTR('ERROR       - Delete Process Loop',1,60),
                                        PC_job_log_message => SUBSTR('Failed DELETE row # '
                                                                     || I_del_err
                                                                     || ' occurred during iteration # '
                                                                     || SQL%BULK_EXCEPTIONS(I_del_err).ERROR_INDEX
                                                                     || ' Error message: '
                                                                     || SQLERRM(-SQL%BULK_EXCEPTIONS(I_del_err).ERROR_CODE),1,2000),
                                        PD_date1 => VD_process_start_date,
                                        PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

              ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                        PD_job_error_date  => VD_log_date,
                                        PC_job_error_code => SUBSTR(-SQL%BULK_EXCEPTIONS(I_del_err).ERROR_CODE,1,60),
                                        PC_job_error_message => SUBSTR('Failed DELETE row # '
                                                                       || I_del_err
                                                                       || ' occurred during iteration # '
                                                                       || SQL%BULK_EXCEPTIONS(I_del_err).ERROR_INDEX
                                                                       || ' Error message: '
                                                                       || SQLERRM(-SQL%BULK_EXCEPTIONS(I_del_err).ERROR_CODE),1,2000),
                                        PD_date1 => VD_process_start_date,
                                        PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            END LOOP;

            ROLLBACK;

            BEGIN

              UPDATE    Adr_Parameter AP
              SET       AP.Value = 0
              WHERE     Application_name = 'HL7_DELETE_CONTROL'
              AND       Parameter_name = 'START_STOP_PROCESS';

              COMMIT;

            EXCEPTION

              WHEN OTHERS THEN

                ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                          PD_job_log_date => VD_log_date,
                                          PC_job_status => SUBSTR('ERROR       - Delete Process Loop',1,60),
                                          PC_job_log_message => SUBSTR('Unable to update parameter START_STOP_PROCESS(2).'
                                                                       || ' Terminating execution of the job.'|| SQLERRM,1,2000),
                                          PD_date1 => VD_process_start_date,
                                          PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

                ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                          PD_job_error_date  => VD_log_date,
                                          PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                          PC_job_error_message => SUBSTR('Failure in delete processing loop - Unable to update parameter START_STOP_PROCESS(2). '
                                                                         || SQLERRM,1,2000),
                                          PD_date1 => VD_process_start_date,
                                          PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            END;

            RETURN;

          WHEN OTHERS THEN

            VD_log_date := SYSDATE;

            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_log_date => VD_log_date,
                                      PC_job_status => SUBSTR('ERROR       - Delete Process Loop',1,60),
                                      PC_job_log_message => SUBSTR('Unknown error while deleting.'
                                                                   || ' Terminating execution of the job.'|| SQLERRM,1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                      PD_job_error_date  => VD_log_date,
                                      PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                      PC_job_error_message => SUBSTR('Failure in delete processing loop - Unknown error while deleting.'
                                                                     || SQLERRM,1,2000),
                                      PD_date1 => VD_process_start_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            ROLLBACK;

            BEGIN

              UPDATE    Adr_Parameter AP
              SET       AP.Value = 0
              WHERE     Application_name = 'HL7_DELETE_CONTROL'
              AND       Parameter_name = 'START_STOP_PROCESS';

              COMMIT;

            EXCEPTION

              WHEN OTHERS THEN

                ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                          PD_job_log_date => VD_log_date,
                                          PC_job_status => SUBSTR('ERROR       - Delete Process Loop',1,60),
                                          PC_job_log_message => SUBSTR('Unable to update parameter START_STOP_PROCESS(3).'
                                                                       || ' Terminating execution of the job.'|| SQLERRM,1,2000),
                                          PD_date1 => VD_process_start_date,
                                          PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

                ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                          PD_job_error_date  => VD_log_date,
                                          PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                          PC_job_error_message => SUBSTR('Failure in delete processing loop - Unable to update parameter START_STOP_PROCESS(3). '
                                                                         || SQLERRM,1,2000),
                                          PD_date1 => VD_process_start_date,
                                          PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            END;

            RETURN;

        END;

        IF T_pk_id.COUNT = VN_delete_count THEN  -- all collection rows were deleted

          COMMIT;

          VN_total_deletes_count := VN_total_deletes_count + VN_delete_count;

          DBMS_APPLICATION_INFO.SET_CLIENT_INFO(TO_CHAR(SYSDATE,'DD-MON HH24:MI:SS')
                                                || ' - Current delete count: '
                                                || TO_CHAR(VN_total_deletes_count,'99,999,999,990'));

          BEGIN  -- get process control parameter

            SELECT    AP.Value
            INTO      VN_process_control
            FROM      Adr_Parameter AP
            WHERE     Application_name = 'HL7_DELETE_CONTROL'
            AND       Parameter_name = 'START_STOP_PROCESS';

            IF VN_process_control = 0 THEN

              VD_log_date := SYSDATE;

              ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                        PD_job_log_date => VD_log_date,
                                        PC_job_status => SUBSTR('INFORMATION - Delete Process Loop - CANCELLED',1,60),
                                        PC_job_log_message => SUBSTR('Application has cancelled the delete process.',1,2000),
                                        PD_date1 => VD_process_start_date,
                                        PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

              VD_log_date := SYSDATE;

              ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                        PD_job_log_date => VD_log_date,
                                        PC_job_status => SUBSTR('INFORMATION - Delete Process Loop - CANCELLED',1,60),
                                        PC_job_log_message => SUBSTR('Total number of rows deleted: '
                                                                     || TO_CHAR(VN_total_deletes_count,'99,999,999,990'),1,2000),
                                        PD_date1 => VD_process_start_date,
                                        PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

              DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Delete Processing Cancelled');

              EXIT;

            END IF;

          EXCEPTION

            WHEN OTHERS THEN

              VD_log_date := SYSDATE;

              ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                        PD_job_log_date => VD_log_date,
                                        PC_job_status => SUBSTR('ERROR       - Delete Process Loop',1,60),
                                        PC_job_log_message => SUBSTR('Unable to retrieve parameter START_STOP_PROCESS.'
                                                                     || ' Terminating execution of the job.'|| SQLERRM,1,2000),
                                        PD_date1 => VD_process_start_date,
                                        PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

              ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                        PD_job_error_date  => VD_log_date,
                                        PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                        PC_job_error_message => SUBSTR('Failure in delete processing loop - Unable to retrieve parameter START_STOP_PROCESS. '
                                                                       || SQLERRM,1,2000),
                                        PD_date1 => VD_process_start_date,
                                        PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

              RETURN;

          END;  -- end get process control parameter

        ELSE  -- all collection rows were not deleted

          VD_log_date := SYSDATE;

          ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                    PD_job_log_date => VD_log_date,
                                    PC_job_status => SUBSTR('ERROR       - Delete Process Loop',1,60),
                                    PC_job_log_message => SUBSTR('Delete count does not match colletion table count.'
                                                                 || ' - Delete count: '
                                                                 || VN_delete_count
                                                                 || ' - Collection count: '
                                                                 || T_pk_id.COUNT
                                                                 || ' - Terminating execution of the job.',1,2000),
                                    PD_date1 => VD_process_start_date,
                                    PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

          ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                    PD_job_error_date  => VD_log_date,
                                    PC_job_error_code => NULL,
                                    PC_job_error_message => SUBSTR('Failure in delete processing loop - Delete count does not match colletion table count.'
                                                                 || ' - Delete count: '
                                                                 || VN_delete_count
                                                                 || ' - Collection count: '
                                                                 || T_pk_id.COUNT
                                                                 || ' - Terminating execution of the job.',1,2000),
                                    PD_date1 => VD_process_start_date,
                                    PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

          ROLLBACK;

          BEGIN

            UPDATE    Adr_Parameter AP
            SET       AP.Value = 0
            WHERE     Application_name = 'HL7_DELETE_CONTROL'
            AND       Parameter_name = 'START_STOP_PROCESS';

            COMMIT;

          EXCEPTION

            WHEN OTHERS THEN

              ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                        PD_job_log_date => VD_log_date,
                                        PC_job_status => SUBSTR('ERROR       - Delete Process Loop',1,60),
                                        PC_job_log_message => SUBSTR('Unable to update parameter START_STOP_PROCESS(4).'
                                                                     || ' Terminating execution of the job.'|| SQLERRM,1,2000),
                                        PD_date1 => VD_process_start_date,
                                        PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

              ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                        PD_job_error_date  => VD_log_date,
                                        PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                        PC_job_error_message => SUBSTR('Failure in delete processing loop - Unable to update parameter START_STOP_PROCESS(4). '
                                                                       || SQLERRM,1,2000),
                                        PD_date1 => VD_process_start_date,
                                        PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

          END;

          RETURN;

        END IF;  -- end all collection rows were deleted

      END IF;  -- end if collection table is empty, all rows processed

    END LOOP;  -- emd main processing loop

    VD_process_end_date := SYSDATE;

    VN_process_time_in_secs := (VD_process_end_date - VD_process_start_date)*24*60*60;

    VN_process_days := TRUNC(VN_process_time_in_secs/24/60/60);

    VN_process_hours := TRUNC((VN_process_time_in_secs -
                               (VN_process_days*24*60*60))/60/60);

    VN_process_minutes := TRUNC((VN_process_time_in_secs -
                                 (VN_process_days*24*60*60) -
                                   (VN_process_hours*60*60))/60);

    VN_process_seconds := TRUNC(VN_process_time_in_secs -
                                 (VN_process_days*24*60*60) -
                                   (VN_process_hours*60*60) -
                                     (VN_process_minutes*60));

    VC_process_duration := LTRIM(TO_CHAR(VN_process_days,'9990')) || ' Days '
                           || LTRIM(TO_CHAR(VN_process_hours,'90')) || ' Hrs '
                           || LTRIM(TO_CHAR(VN_process_minutes,'90')) || ' Mins '
                           || LTRIM(TO_CHAR(VN_process_seconds,'90')) || ' Secs';

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Process Completion',1,60),
                              PC_job_log_message => 'Elapsed processing time: ' || VC_process_duration,
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('ERROR       - Delete Process Loop',1,60),
                                PC_job_log_message => SUBSTR('Failure in delete processing loop.'
                                                             || ' Terminating execution of the job.'|| SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                PC_job_error_message => SUBSTR('Failure in delete processing loop. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_process_start_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;  -- end main processing loop block

EXCEPTION

  WHEN OTHERS THEN

    VD_log_date := SYSDATE;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('ERROR       - Unknown Error',1,60),
                              PC_job_log_message => SUBSTR('Unknown error processing HL7 deletions.'
                                                           || ' Terminating execution of the job.'|| SQLERRM,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(CC_process_name,1,60),
                              PD_job_error_date  => VD_log_date,
                              PC_job_error_code => SUBSTR(SQLCODE,1,60),
                              PC_job_error_message => SUBSTR('Unknown error processing HL7 deletions. '
                                                             || SQLERRM,1,2000),
                              PD_date1 => VD_process_start_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    RETURN;

END Hl7_Process_Deletes;

END Hl7_Archive_Pkg;
/


GRANT EXECUTE ON ADR.HL7_ARCHIVE_PKG TO EEUSR;
DROP PACKAGE BODY ADR.IAM_UTIL_PKG;

CREATE OR REPLACE PACKAGE BODY ADR.IAM_UTIL_PKG
AS
PROCEDURE USERS_IAM_INSERT_SP(
	  p_USERNAME	    IN USERS.USERNAME%TYPE,
	  p_LAST_NAME	     IN USERS.LAST_NAME%TYPE,
	  p_FIRST_NAME	      IN USERS.FIRST_NAME%TYPE,
	  p_MIDDLE_NAME        IN USERS.MIDDLE_NAME%TYPE,
	  p_STD_INSTITUTION_ID	  IN STD_INSTITUTION.ID%TYPE,
	  p_JOB_TITLE	     IN USERS.JOB_TITLE%TYPE,
	  p_PASSWORD	    IN USERS.PASSWORD%TYPE,
	  p_ACCOUNT_EXPIRE_DATE    IN USERS.ACCOUNT_EXPIRE_DATE%TYPE,
	  p_PASSWORD_EXPIRE_DATE    IN USERS.PASSWORD_EXPIRE_DATE%TYPE,
	  p_AGREEMENT_SIGNATURE_CODE IN USERS.AGREEMENT_SIGNATURE_CODE%TYPE,
	  p_PASSWORD_CHANGE_DATE    IN USERS.PASSWORD_CHANGE_DATE%TYPE,
	  p_PASSWORD_CREATE_DATE    IN USERS.PASSWORD_CREATE_DATE%TYPE,
	  p_INACTIVE_FLAG	 IN USERS.INACTIVE_FLAG%TYPE,
          p_NUMBER_OF_LOGIN_ATTEMPTS IN USERS.NUMBER_OF_LOGIN_ATTEMPTS%TYPE,
          p_SIGNATURE_VERIFIED_IND   IN USERS.SIGNATURE_VERIFIED_IND%TYPE,
	  p_ROLES	     IN VARCHAR2
) IS

	      lb_cnt integer;
		    la_Tab_Str IAM_UTIL_PKG.varchar2_table;
	      lo_Val  VARCHAR2(1000);
	      lo_userid NUMBER;
	      lo_user_role_id number;

	  BEGIN
	     Select TRANSACTION_S.NEXTVAL
	     INTO lo_userid
	     FROM dual;
	      INSERT INTO USERS(USERID
					    ,USERNAME
					    ,LAST_NAME
					    ,FIRST_NAME
					    ,MIDDLE_NAME
					    ,STD_INSTITUTION_ID
					    ,JOB_TITLE
					    ,PASSWORD
					    ,ACCOUNT_EXPIRE_DATE
					    ,PASSWORD_EXPIRE_DATE
					    ,AGREEMENT_SIGNATURE_CODE
					    ,PASSWORD_CHANGE_DATE
					    ,PASSWORD_CREATE_DATE
					    ,INACTIVE_FLAG
                                            ,NUMBER_OF_LOGIN_ATTEMPTS
                                            ,SIGNATURE_VERIFIED_IND
					    ,RECORD_CREATED_BY
					    ,RECORD_CREATED_DATE
					    ,RECORD_MODIFIED_BY
					    ,RECORD_MODIFIED_DATE
					    ,RECORD_MODIFIED_COUNT)
	      VALUES
	      (lo_userid
	     , p_USERNAME
	     , p_LAST_NAME
	     ,	p_FIRST_NAME
	     , p_MIDDLE_NAME
	     , p_STD_INSTITUTION_ID
	     , p_JOB_TITLE
	     , p_PASSWORD
	     , p_ACCOUNT_EXPIRE_DATE
	     , p_PASSWORD_EXPIRE_DATE
	     , p_AGREEMENT_SIGNATURE_CODE
	     , SYSDATE
	     , p_PASSWORD_CREATE_DATE
	     , p_INACTIVE_FLAG
             , p_NUMBER_OF_LOGIN_ATTEMPTS
             , p_SIGNATURE_VERIFIED_IND
	     , USER
	     , SYSDATE
	     , USER
	     , SYSDATE
	     , 0);
	  IF (p_ROLES IS NOT NULL)
	  THEN
	    IAM_UTIL_PKG.delimstring_to_table (p_ROLES,la_Tab_Str,lb_cnt);
	    FOR i IN 1 .. lb_cnt LOOP

	    Select TRANSACTION_S.NEXTVAL
	     INTO lo_user_role_id
	     FROM dual;

	    lo_Val := REPLACE(la_Tab_Str(i), '"', '');
	    INSERT INTO USER_ROLES(USER_ROLE_ID
						   ,USERID
						   ,ROLE_ID
						   ,RECORD_CREATED_BY
						   , RECORD_CREATED_DATE
						   ,RECORD_MODIFIED_BY
						   ,RECORD_MODIFIED_DATE
						   ,RECORD_MODIFIED_COUNT)
	    VALUES (  lo_user_role_id
			 ,  lo_userid
			 , (SELECT ROLE_ID FROM ROLES WHERE ROLE_NAME=lo_Val)
			 , USER
			 , SYSDATE
			 , USER
			 , SYSDATE
			 , 0 );
	    END LOOP;
	  END IF;
	      COMMIT;
	  EXCEPTION
	      WHEN OTHERS THEN
		  ROLLBACK;
		  RAISE_APPLICATION_ERROR(-20900, 'Insert Error - ' || SUBSTR(SQLERRM,1,2000));

END USERS_IAM_INSERT_SP;

PROCEDURE USERS_IAM_UPDATE_SP
	  (p_USERNAME	     IN USERS.USERNAME%TYPE,
	  p_LAST_NAME	     IN USERS.LAST_NAME%TYPE,
	  p_FIRST_NAME	      IN USERS.FIRST_NAME%TYPE,
	  p_MIDDLE_NAME        IN USERS.MIDDLE_NAME%TYPE,
	  p_STD_INSTITUTION_ID	  IN STD_INSTITUTION.ID%TYPE,
	  p_JOB_TITLE	     IN USERS.JOB_TITLE%TYPE,
	  p_PASSWORD	    IN USERS.PASSWORD%TYPE,
	  p_ACCOUNT_EXPIRE_DATE    IN USERS.ACCOUNT_EXPIRE_DATE%TYPE,
	  p_PASSWORD_EXPIRE_DATE    IN USERS.PASSWORD_EXPIRE_DATE%TYPE,
	  p_AGREEMENT_SIGNATURE_CODE IN USERS.AGREEMENT_SIGNATURE_CODE%TYPE,
	  p_PASSWORD_CHANGE_DATE    IN USERS.PASSWORD_CHANGE_DATE%TYPE,
	  p_PASSWORD_CREATE_DATE    IN USERS.PASSWORD_CREATE_DATE%TYPE,
	  p_INACTIVE_FLAG	 IN USERS.INACTIVE_FLAG%TYPE,
          p_NUMBER_OF_LOGIN_ATTEMPTS IN USERS.NUMBER_OF_LOGIN_ATTEMPTS%TYPE,
          p_SIGNATURE_VERIFIED_IND   IN USERS.SIGNATURE_VERIFIED_IND%TYPE,
	  p_ROLES	     IN VARCHAR2
)  IS

	      lb_cnt integer;
		    la_Tab_Str IAM_UTIL_PKG.varchar2_table;
	      lo_Val  VARCHAR2(1000);
	      lo_Val1 VARCHAR2(1000);
	      l_exists INTEGER;
	      r_exists INTEGER;
	      lo_user_role_id Number;
	      lo_userid Number;
	      Username_not_found EXCEPTION;
	      PRAGMA EXCEPTION_INIT(Username_not_found, -20905);

	  BEGIN

	      SELECT USERID
	      INTO lo_userid
	      FROM USERS
	      WHERE USERNAME = p_USERNAME;

	      UPDATE USERS SET LAST_NAME = p_LAST_NAME
					  , FIRST_NAME = p_FIRST_NAME
					  , MIDDLE_NAME = p_MIDDLE_NAME
					  , STD_INSTITUTION_ID = p_STD_INSTITUTION_ID
					  , JOB_TITLE = p_JOB_TITLE
					  , PASSWORD = p_PASSWORD
					  , ACCOUNT_EXPIRE_DATE = p_ACCOUNT_EXPIRE_DATE
					  , PASSWORD_EXPIRE_DATE = p_PASSWORD_CREATE_DATE
					  , AGREEMENT_SIGNATURE_CODE = p_AGREEMENT_SIGNATURE_CODE
					  , PASSWORD_CHANGE_DATE = p_PASSWORD_CHANGE_DATE
					  , PASSWORD_CREATE_DATE = p_PASSWORD_CREATE_DATE
					  , INACTIVE_FLAG = p_INACTIVE_FLAG
                                          , NUMBER_OF_LOGIN_ATTEMPTS = p_NUMBER_OF_LOGIN_ATTEMPTS
                                          , SIGNATURE_VERIFIED_IND  = p_SIGNATURE_VERIFIED_IND
					  , RECORD_MODIFIED_BY = USER
					  , RECORD_MODIFIED_DATE = SYSDATE
					  , RECORD_MODIFIED_COUNT = RECORD_MODIFIED_COUNT + 1
	      WHERE USERID = lo_userid;
	      IF SQL%ROWCOUNT = 0 THEN
		 RAISE Username_not_found;
	      END IF;

	      IF p_ROLES IS NULL THEN
		  DELETE FROM USER_ROLES WHERE USERID = lo_userid;
	      ELSE
		  lo_Val1 := REPLACE(p_ROLES, '"', '');
		  SELECT COUNT(*)
		  INTO r_exists
		  FROM USER_ROLES
		  WHERE USERID = lo_userid
		  AND ROLE_ID NOT IN (SELECT ROLE_ID FROM ROLES WHERE ROLE_NAME IN (lo_Val1) );

		  IF r_exists >= 1 THEN
		      DELETE FROM USER_ROLES
		      WHERE USERID = lo_userid
		      AND ROLE_ID NOT IN (SELECT ROLE_ID FROM  ROLES WHERE ROLE_NAME IN (lo_Val1) ) ;
		  END IF;

      IAM_UTIL_PKG.delimstring_to_table (p_ROLES,la_Tab_Str,lb_cnt);
	      FOR i IN 1 .. lb_cnt LOOP

		      lo_Val := REPLACE(la_Tab_Str(i), '"', '');
		      SELECT COUNT(*)
		      INTO l_exists
		      FROM USER_ROLES
		      WHERE USERID =  lo_userid AND ROLE_ID = (SELECT ROLE_ID FROM  ROLES WHERE ROLE_NAME IN (lo_Val) );

		      IF l_exists != 1	THEN
			 Select TRANSACTION_S.NEXTVAL
			 INTO lo_user_role_id
			 FROM dual;
			  INSERT INTO USER_ROLES(USER_ROLE_ID
								 ,USERID
								 ,ROLE_ID
								 ,RECORD_CREATED_BY
								 ,RECORD_CREATED_DATE
								 ,RECORD_MODIFIED_BY
								 ,RECORD_MODIFIED_DATE
								 ,RECORD_MODIFIED_COUNT)
			 VALUES ( lo_user_role_id
				    , lo_userid
				    , (SELECT ROLE_ID FROM ROLES WHERE ROLE_NAME=lo_Val)
				    , USER
				    , SYSDATE
				    , USER
				    , SYSDATE
				    , 0);
		      END IF;

		  END LOOP;
	      END IF;
	      COMMIT;
	  EXCEPTION
	      WHEN Username_not_found THEN
		   ROLLBACK;
		   RAISE_APPLICATION_ERROR(-20905, 'Username not found');
	      WHEN NO_DATA_FOUND THEN
		   ROLLBACK;
		   RAISE_APPLICATION_ERROR(-20904, 'Unable to retrieve USERID');
	      WHEN OTHERS THEN
		  ROLLBACK;
		  RAISE_APPLICATION_ERROR(-20901, 'Update Error - ' || SUBSTR(SQLERRM,1,2000));
END USERS_IAM_UPDATE_SP;

PROCEDURE USERS_IAM_DELETE_SP(
p_USERNAME    IN	   USERS.USERNAME%TYPE
) IS
	      Username_not_found EXCEPTION;
	      PRAGMA EXCEPTION_INIT(Username_not_found, -20905);
BEGIN
	  UPDATE USERS SET INACTIVE_FLAG = 1
				      , INACTIVE_DATE = SYSDATE
				      , RECORD_MODIFIED_BY = USER
				      , RECORD_MODIFIED_DATE = SYSDATE
				      , RECORD_MODIFIED_COUNT = RECORD_MODIFIED_COUNT	+ 1
	  WHERE USERNAME = p_USERNAME;
	  IF SQL%ROWCOUNT = 0 THEN
		RAISE Username_not_found;
	     END IF;
	  COMMIT;

	  EXCEPTION
	      WHEN Username_not_found THEN
		  ROLLBACK;
		  RAISE_APPLICATION_ERROR(-20905, 'Username not found');
	      WHEN OTHERS THEN
		  ROLLBACK;
		  RAISE_APPLICATION_ERROR(-20902, 'Delete Error - ' || SUBSTR(SQLERRM,1,2000));
END USERS_IAM_DELETE_SP;

PROCEDURE delimstring_to_table
  ( p_delimstring IN VARCHAR2
  , p_table OUT varchar2_table
  , p_nfields OUT INTEGER
  , p_delim IN VARCHAR2 DEFAULT ','
  )
IS
  v_string VARCHAR2(32767) := p_delimstring;
  v_nfields PLS_INTEGER := 1;
  v_table varchar2_table;
  v_delimpos PLS_INTEGER := INSTR(p_delimstring, p_delim);
  v_delimlen PLS_INTEGER := LENGTH(p_delim);
BEGIN
  WHILE v_delimpos > 0
  LOOP
    v_table(v_nfields) := SUBSTR(v_string,1,v_delimpos-1);
    v_string := SUBSTR(v_string,v_delimpos+v_delimlen);
    v_nfields := v_nfields+1;
    v_delimpos := INSTR(v_string, p_delim);
  END LOOP;
  v_table(v_nfields) := v_string;
  p_table := v_table;
  p_nfields := v_nfields;
END delimstring_to_table;

PROCEDURE table_to_delimstring
  ( p_table IN varchar2_table
  , p_delimstring OUT VARCHAR2
  , p_delim IN VARCHAR2 DEFAULT ','
  )
IS
  v_nfields PLS_INTEGER := p_table.COUNT;
  v_string VARCHAR2(32767);
BEGIN
  FOR i IN 1..v_nfields
  LOOP
    v_string := v_string || p_table(i);
    IF i != v_nfields THEN
      v_string := v_string || p_delim;
    END IF;
  END LOOP;
  p_delimstring := v_string;
END table_to_delimstring;

END IAM_UTIL_PKG;
/


CREATE OR REPLACE PUBLIC SYNONYM IAM_UTIL_PKG FOR ADR.IAM_UTIL_PKG;


GRANT EXECUTE ON ADR.IAM_UTIL_PKG TO IAMUSR;
DROP PACKAGE BODY ADR.MEC_PERIOD_PKG;

CREATE OR REPLACE PACKAGE BODY ADR.MEC_PERIOD_PKG
AS
   FUNCTION Calc_Exec_time (v_start_dt DATE)
      RETURN VARCHAR
   AS
   BEGIN
      RETURN    ' Duration: '
             || (   LTRIM (TO_CHAR (TRUNC ( (SYSDATE - v_start_dt) * 24), '00'))
                 || ' Hrs '
                 || LTRIM (TO_CHAR (TRUNC ( ( ( (SYSDATE - v_start_dt) * 24) - TRUNC ( ( (SYSDATE - v_start_dt) * 24))) * 60), '00'))
                 || ' Mins '
                 || LTRIM (
                       TO_CHAR (TRUNC ( ( ( (SYSDATE - v_start_dt) * 24 * 60) - TRUNC ( ( (SYSDATE - v_start_dt) * 24 * 60))) * 60), '00'))
                 || ' Secs');
   END Calc_Exec_time;

   /***************************************************************************
          DESCRIPTION
             WRAPPER_JOB_PROCESS_LOG_SP procedure for logging INFO, WARN and ERROR into table.
    ******************************************************************************/
   PROCEDURE WRAPPER_JOB_PROCESS_LOG_SP (P_JOB_STATUS VARCHAR2, P_JOB_LOG_MESSAGE VARCHAR2)
   AS
   BEGIN
      ADRMGT.Job_Process_Log_Sp (PC_job_name          => SUBSTR (g_job_name, 1, 59),
                                 PD_job_log_date      => SYSDATE,
                                 PC_job_status        => SUBSTR (P_JOB_STATUS, 1, 59),
                                 PC_job_log_message   => SUBSTR (P_JOB_LOG_MESSAGE, 1, 1980));
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (   SQLERRM
                               || ' : '
                               || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
   END WRAPPER_JOB_PROCESS_LOG_SP;

   /***************************************************************************
        DESCRIPTION
                     Procedure does main logic of inserting the data with all business logic.
  ******************************************************************************/

   PROCEDURE FINAL_LOADER_MEC_PERIOD (p_future_lower_bound NUMBER)
   AS
      v_rec_cnt    INTEGER;
      v_start_dt   DATE;
   BEGIN
      g_job_name :=
            g_pkg_name
         || '.FINAL_LOADER_MEC_PERIOD';
      v_start_dt := SYSDATE;

      --- All below STEPS starting from 3 through 6 happens in one transaction.
      --STEP 3: Delete last inserted start date that doesn't have a matching end date.
      DELETE FROM MEC_PERIOD_NEW m
            WHERE EXISTS
                     (SELECT 1
                        FROM STG_MEC_PERIOD f
                       WHERE f.Person_id = m.Person_id -- Added fix WI-321159
                                                      AND m.MEC_END_DATE IS NULL AND f.DERVD_MEC_END_DATE IS NOT NULL);

      v_rec_cnt := SQL%ROWCOUNT;

      WRAPPER_JOB_PROCESS_LOG_SP ('INFO',
                                     'STEP 3 - LOADER: (DELETE) MEC_PERIOD_NEW v_rec_cnt: '
                                  || v_rec_cnt
                                  || Calc_Exec_time (v_start_dt));
      v_start_dt := SYSDATE;

      --STEP 4: insert final data set both incremental and initial loads
      INSERT INTO MEC_PERIOD_NEW (MEC_PERIOD_ID,
                                  PERSON_ID,
                                  MEC_START_DATE,
                                  MEC_END_DATE,
                                  RECORD_CREATED_BY,
                                  RECORD_CREATED_DATE,
                                  RECORD_MODIFIED_BY,
                                  RECORD_MODIFIED_DATE,
                                  RECORD_MODIFIED_COUNT)
         SELECT s.MEC_PERIOD_ID,
                s.PERSON_ID,
                s.DERVD_MEC_START_DATE,
                s.DERVD_MEC_END_DATE,
                g_pkg_name RECORD_CREATED_BY,
                SYSDATE RECORD_CREATED_DATE,
                g_pkg_name RECORD_MODIFIED_BY,
                SYSDATE RECORD_MODIFIED_DATE,
                0 RECORD_MODIFIED_COUNT
           FROM STG_MEC_PERIOD s,
                -- Added fix WI-321159
                (SELECT MEC_PERIOD_ID FROM STG_MEC_PERIOD
                 MINUS
                 SELECT MEC_PERIOD_ID FROM MEC_PERIOD_NEW) t
          WHERE s.MEC_PERIOD_ID = t.MEC_PERIOD_ID;

      v_rec_cnt := SQL%ROWCOUNT;
      g_final_rec_cnt := v_rec_cnt;

      WRAPPER_JOB_PROCESS_LOG_SP ('INFO',
                                     'STEP 4 - LOADER: (INSERT) MEC_PERIOD_NEW v_rec_cnt: '
                                  || v_rec_cnt
                                  || Calc_Exec_time (v_start_dt));
      v_start_dt := SYSDATE;

      --STEP 5: Update / insert max(LAST_ENROLL_DETERM_H_ID) for each person id for use in next cycle run
      MERGE INTO MEC_PERIOD_LAST_HIST_NEW d
           USING (  SELECT MAX (MEC_PERIOD_ID) LAST_ENROLL_DETERM_H_ID, person_id
                      FROM STG_MEC_PERIOD
                  GROUP BY person_id) s
              ON (d.person_id = s.person_id)
      WHEN MATCHED
      THEN
         UPDATE SET d.LAST_ENROLL_DETERM_H_ID = s.LAST_ENROLL_DETERM_H_ID, STATUS = 'Y'
                 WHERE d.LAST_ENROLL_DETERM_H_ID < s.LAST_ENROLL_DETERM_H_ID
      WHEN NOT MATCHED
      THEN
         INSERT     (PERSON_ID, LAST_ENROLL_DETERM_H_ID, STATUS)
             VALUES (s.PERSON_ID, s.LAST_ENROLL_DETERM_H_ID, 'Y');

      v_rec_cnt := SQL%ROWCOUNT;

      WRAPPER_JOB_PROCESS_LOG_SP ('INFO',
                                     'STEP 5 - METADATA: (MERGE) MEC_PERIOD_LAST_HIST_NEW v_rec_cnt: '
                                  || v_rec_cnt
                                  || Calc_Exec_time (v_start_dt));
      v_start_dt := SYSDATE;

      --STEP 6: update lower bound for tomorrow with p_future_lower_bound
      UPDATE ADRMGT.ADR_PARAMETER
         SET VALUE = p_future_lower_bound, RECORD_MODIFIED_BY = SUBSTR (g_job_name, 1, 29), RECORD_MODIFIED_DATE = SYSDATE
       WHERE     APPLICATION_NAME = 'ACA_MEC'
             AND UPPER (PARAMETER_NAME) = g_param_name_for_lower_bound
             AND INACTIVE_FLAG = 'N'
             AND TO_NUMBER (VALUE) < p_future_lower_bound;

      v_rec_cnt := SQL%ROWCOUNT;

      COMMIT;

      -- Log correctly based on the above update
      IF v_rec_cnt > 0
      THEN
         WRAPPER_JOB_PROCESS_LOG_SP ('INFO',
                                        'STEP 6a - METADATA: (UPDATE) ADR_PARAMETER.VALUE (future_lower_bound) from: '
                                     || g_current_lower_bound
                                     || ' to: '
                                     || p_future_lower_bound
                                     || Calc_Exec_time (v_start_dt));
      ELSE
         WRAPPER_JOB_PROCESS_LOG_SP ('INFO',
                                        'STEP 6a - METADATA: (NO-UPDATE) ADR_PARAMETER.VALUE (future_lower_bound) remains same: '
                                     || g_current_lower_bound);
      END IF;
   END FINAL_LOADER_MEC_PERIOD;

   /***************************************************************************
            DESCRIPTION
                  Update the ADR_PARAMETER table with lower bounds for next run
   ******************************************************************************/

   PROCEDURE PREP_WORK_MEC_PERIOD (p_upper_bound NUMBER, p_multi_batch_flg BOOLEAN)
   AS
      v_rec_cnt              INTEGER;
      v_start_dt             DATE;
      v_future_lower_bound   INTEGER;
   BEGIN
      g_job_name :=
            g_pkg_name
         || '.PREP_WORK_MEC_PERIOD';
      v_start_dt := SYSDATE;

      --STEP 1: PREP-DATA FILTERATION for processing daily incremental and inital loads.
      --Build person_id start and end dates with ranking correctly for use in the next steps
      -- start and end dates shall have thier respective records and they are mutual exclusive;
      -- A record with start dates shall not have end date and vice-versa.
      INSERT INTO STG_MEC_PERIOD_ENRMNT_DTRM_H (ENROLLMENT_DETERMINATION_H_ID,
                                                PERSON_ID,
                                                DERVD_MEC_START_DATE,
                                                DERVD_MEC_END_DATE,
                                                current_rank)
         SELECT Enrollment_determination_h_id,
                h.Person_id,
                CASE WHEN STD_ENROLLMENTSTATUS_ID = 1504142 THEN NVL (Enrollment_start_date, Record_created_date) END dervd_MEC_START_DATE,
                CASE
                   WHEN STD_ENROLLMENTSTATUS_ID IN (1504151, -- Rejected
                                                    1504152, -- Rejected
                                                    1504153, -- Rejected
                                                    1504154, -- Rejected
                                                    1504162 -- Rejected
                                                           )
                   THEN
                      Record_modified_date
                   WHEN STD_ENROLLMENTSTATUS_ID <> 1504142
                   THEN
                      NVL (Enrollment_end_date, Record_modified_date)
                END
                   dervd_MEC_END_DATE,
                DENSE_RANK ()
                   OVER (PARTITION BY h.Person_id
                         ORDER BY
                            Record_created_date,
                            DECODE (transaction_type_code,  'I', 1,  'U', 2,  'D', 3,  transaction_type_code), -- WI 231441 CSW
                            Record_modified_date, -- WI 234731 CSW
                            Enrollment_determination_h_id ASC NULLS LAST)
                   current_rank
           FROM Enrollment_Determination_H h, STG_MEC_PERIOD_PERSON_ID prsn
          WHERE     h.person_id = prsn.person_id
                AND Std_enrollmentstatus_id IN (1504142, -- Verified
                                                1504141, -- Unverified
                                                1504144, -- Rejected
                                                1504146, -- Deceased
                                                1504147, -- Cancelled/Declined
                                                1504149, -- Pending
                                                1504150, -- Not Eligible
                                                1504151, -- Rejected
                                                1504152, -- Rejected
                                                1504153, -- Rejected
                                                1504154, -- Rejected
                                                1504155, -- Pending; No Eligibility Code
                                                1504157, -- Pending; Eligibility Status is Unverified
                                                1504158, -- Pending; Other
                                                1504159, -- Pending; Other
                                                1504160, -- Not Eligible
                                                1504162, -- Rejected
                                                1504163 -- Not Applicable
                                                       );

      v_rec_cnt := SQL%ROWCOUNT;

      WRAPPER_JOB_PROCESS_LOG_SP ('INFO',
                                     'STEP 1 - PREP: (INSERT) STG_MEC_PERIOD_ENRMNT_DTRM_H  v_rec_cnt: '
                                  || v_rec_cnt
                                  || Calc_Exec_time (v_start_dt));

      v_start_dt := SYSDATE;

      IF v_rec_cnt > 0
      THEN
         --STEP 2: Apply Business Logic: by re-aligning start and end dates correctly for each person_id
         INSERT INTO STG_MEC_PERIOD (MEC_PERIOD_ID,
                                     PERSON_ID,
                                     DERVD_MEC_START_DATE,
                                     DERVD_MEC_END_DATE)
            SELECT fq.Enrollment_determination_h_id MEC_PERIOD_ID,
                            --MAX (Enrollment_determination_h_id) MEC_PERIOD_ID,
                            fq.person_id,
                            fq.dervd_MEC_START_DATE,
                            fq.dervd_MEC_END_DATE
              FROM (SELECT CASE
                              WHEN end_dte.Enrollment_determination_h_id > start_dte.Enrollment_determination_h_id
                              THEN
                                 end_dte.Enrollment_determination_h_id
                              WHEN end_dte.Enrollment_determination_h_id < start_dte.Enrollment_determination_h_id
                              THEN
                                 start_dte.Enrollment_determination_h_id
                              WHEN end_dte.Enrollment_determination_h_id IS NULL
                              THEN
                                 start_dte.Enrollment_determination_h_id
                           END
                              Enrollment_determination_h_id,
                           start_dte.person_id,
                           start_dte.dervd_MEC_START_DATE,
                           end_dte.dervd_MEC_END_DATE,
                           start_dte.current_rank start_dte_current_rank,
                           end_dte.current_rank end_dte_current_rank
                      FROM (SELECT Enrollment_determination_h_id,
                                   person_id,
                                   dervd_MEC_START_DATE,
                                   prev_rank,
                                   current_rank,
                                   NEXT_rank
                              FROM (SELECT Enrollment_determination_h_id,
                                           Person_id,
                                           dervd_MEC_START_DATE,
                                           LAG (current_rank, 1, NULL)
                                              OVER (PARTITION BY Person_id ORDER BY Person_id, current_rank ASC NULLS LAST)
                                              prev_rank,
                                           current_rank,
                                           LEAD (current_rank, 1, NULL)
                                              OVER (PARTITION BY Person_id ORDER BY Person_id, current_rank ASC NULLS LAST)
                                              NEXT_rank
                                      FROM (SELECT *
                                              FROM STG_MEC_PERIOD_ENRMNT_DTRM_H
                                             WHERE dervd_MEC_START_DATE IS NOT NULL))
                             --below where condition is remove consecutive similar records
                             WHERE current_rank <> NVL (prev_rank, -1) + 1) start_dte
                           LEFT OUTER JOIN
                           (SELECT Enrollment_determination_h_id,
                                   person_id,
                                   dervd_MEC_END_DATE,
                                   prev_rank,
                                   current_rank,
                                   NEXT_rank
                              FROM (SELECT Enrollment_determination_h_id,
                                           Person_id,
                                           dervd_MEC_END_DATE,
                                           LAG (current_rank, 1, NULL)
                                              OVER (PARTITION BY Person_id ORDER BY Person_id, current_rank ASC NULLS LAST)
                                              prev_rank,
                                           current_rank,
                                           LEAD (current_rank, 1, NULL)
                                              OVER (PARTITION BY Person_id ORDER BY Person_id, current_rank ASC NULLS LAST)
                                              NEXT_rank
                                      FROM (SELECT *
                                              FROM STG_MEC_PERIOD_ENRMNT_DTRM_H
                                             WHERE dervd_MEC_END_DATE IS NOT NULL))
                             --below where condition is remove consecutive similar records
                             WHERE current_rank <> NVL (prev_rank, -1) + 1) end_dte
                              -- The below condition is very critical in aligning the start and end dates correctly.
                              ON (    start_dte.Person_id = end_dte.Person_id
                                  AND end_dte.current_rank BETWEEN start_dte.current_rank AND NVL (end_dte.next_rank, end_dte.current_rank)
                                  AND NVL (end_dte.prev_rank, 1) <= start_dte.current_rank)) fq
                   -- outer join is to only consider the new records plus open ended records with no end dates
                   LEFT OUTER JOIN MEC_PERIOD_LAST_HIST_NEW mx ON (fq.person_id = mx.person_id)
             WHERE    fq.Enrollment_Determination_h_id >= NVL (mx.LAST_ENROLL_DETERM_H_ID, 0)
                   OR (dervd_MEC_end_DATE IS NULL AND fq.Enrollment_Determination_h_id <= NVL (mx.LAST_ENROLL_DETERM_H_ID, 0))
                   -- Added fix WI-321159
                   OR (    (dervd_MEC_start_DATE IS NOT NULL AND dervd_MEC_end_DATE IS NOT NULL)
                       AND fq.Enrollment_Determination_h_id <= NVL (mx.LAST_ENROLL_DETERM_H_ID, 0));

         -- GROUP BY person_id, dervd_MEC_START_DATE, dervd_MEC_END_DATE;

         v_rec_cnt := SQL%ROWCOUNT;

         WRAPPER_JOB_PROCESS_LOG_SP ('INFO',
                                        'STEP 2a - PREP: (INSERT) STG_MEC_PERIOD v_rec_cnt: '
                                     || v_rec_cnt
                                     || Calc_Exec_time (v_start_dt));
         v_start_dt := SYSDATE;

         -- Below commit is used release the temparory space used by global temprary table : STG_MEC_PERIOD_ENRMNT_DTRM_H
         -- Data in STG_MEC_PERIOD is preserved for use by below steps 3 through 6
         COMMIT;

         IF NOT p_multi_batch_flg
         THEN
            -- Get the future lower bound for the DELTA only.
            SELECT NVL (MIN (MEC_PERIOD_ID), 0)
              INTO v_future_lower_bound
              FROM STG_MEC_PERIOD s LEFT OUTER JOIN MEC_PERIOD_LAST_HIST_NEW mx ON (s.person_id = mx.person_id)
             WHERE s.MEC_PERIOD_ID > NVL (mx.LAST_ENROLL_DETERM_H_ID, 0);

            WRAPPER_JOB_PROCESS_LOG_SP ('INFO',
                                           'STEP 2b - METADATA: (COLLECT) future_lower_bound from STG_MEC_PERIOD '
                                        || v_future_lower_bound
                                        || Calc_Exec_time (v_start_dt));
         ELSE
            v_future_lower_bound := g_current_lower_bound;
            WRAPPER_JOB_PROCESS_LOG_SP ('INFO',
                                           'STEP 2b - METADATA: (INITIAL-COLLECT) No change to future_lower_bound ('
                                        || v_future_lower_bound
                                        || ') since it is in INTIAL loading');
         END IF;
      END IF;

      -- Call the final inserter to MEC_PERIOD, if there is any record found
      -- You may have few records in STG_MEC_PERIOD and if v_future_lower_bound=0; this means there is nothing to insert in the final MEC_PERIOD_NEW table
      -- so we try avoid going to the FINAL_LOADER_MEC_PERIOD
      IF v_rec_cnt > 0 AND v_future_lower_bound > 0
      THEN
         FINAL_LOADER_MEC_PERIOD (v_future_lower_bound);
      ELSE
         WRAPPER_JOB_PROCESS_LOG_SP ('INFO',
                                        'STEP NOTHING_TO_PROCESS STG_MEC_PERIOD insert count: '
                                     || v_rec_cnt
                                     || ' and final insert into MEC_PERIOD_NEW is: '
                                     || v_future_lower_bound);
      END IF;

      IF p_multi_batch_flg
      THEN
         v_start_dt := SYSDATE;

         UPDATE MEC_PERIOD_LAST_HIST_NEW
            SET STATUS = 'Y'
          WHERE PERSON_ID IN (SELECT PERSON_ID
                                FROM STG_MEC_PERIOD_PERSON_ID);

         v_rec_cnt := SQL%ROWCOUNT;

         COMMIT;
         WRAPPER_JOB_PROCESS_LOG_SP ('INFO',
                                        'STEP 6b - METADATA: (INITIAL-UPDATE) MEC_PERIOD_LAST_HIST_NEW.STATUS = Y v_rec_cnt: '
                                     || v_rec_cnt
                                     || Calc_Exec_time (v_start_dt));
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         WRAPPER_JOB_PROCESS_LOG_SP ('ERROR',
                                        SQLERRM
                                     || ' : '
                                     || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
         ROLLBACK;
   END PREP_WORK_MEC_PERIOD;

   /***************************************************************************
       DESCRIPTION
           Main procedure that shall be called by the DBMS_JOBS to process MEC_PERIOD
   ******************************************************************************/
   PROCEDURE KICK_OFF_MEC_PERIOD
   AS
      v_multi_batch_flg    BOOLEAN := FALSE;
      v_loading_type       VARCHAR (60);
      v_misc_text          VARCHAR (300);
      v_transition_text    VARCHAR (300);
      v_tobe_process_cnt   INTEGER := 0;
      v_upper_bound        NUMBER;
      v_start_dt           DATE;
      v_batch_size         INTEGER;
      v_job_number         NUMBER;
      v_rec_cnt            INTEGER;
   BEGIN
      g_job_name :=
            g_pkg_name
         || '.KICK_OFF_MEC_PERIOD';
      v_start_dt := SYSDATE;

      SELECT TO_NUMBER (VALUE)
        INTO g_current_lower_bound
        FROM ADRMGT.ADR_PARAMETER
       WHERE APPLICATION_NAME = 'ACA_MEC' AND UPPER (PARAMETER_NAME) = g_param_name_for_lower_bound AND INACTIVE_FLAG = 'N';

      SELECT UPPER (VALUE)
        INTO v_loading_type
        FROM ADRMGT.ADR_PARAMETER
       WHERE APPLICATION_NAME = 'ACA_MEC' AND UPPER (PARAMETER_NAME) = 'MEC_PERIOD_LOAD_TYPE' AND INACTIVE_FLAG = 'N';

      WRAPPER_JOB_PROCESS_LOG_SP ('INFO',
                                     'STARTED:  current_lower_bound: '
                                  || g_current_lower_bound
                                  || ' : v_loading_type : '
                                  || v_loading_type);

      IF v_loading_type = 'INITIAL'
      THEN
         SELECT TO_NUMBER (VALUE)
           INTO v_batch_size
           FROM ADRMGT.ADR_PARAMETER
          WHERE APPLICATION_NAME = 'ACA_MEC' AND UPPER (PARAMETER_NAME) = 'MEC_PERIOD_BATCH_SIZE' AND INACTIVE_FLAG = 'N';

         SELECT COUNT (*)
           INTO v_tobe_process_cnt
           FROM MEC_PERIOD_LAST_HIST_NEW
          WHERE STATUS = 'N';

         IF v_tobe_process_cnt > 0
         THEN
            -- Need to catch up with lot of records in more than one batch. This is mainly valid for the initial load
            v_multi_batch_flg := TRUE;

            INSERT INTO STG_MEC_PERIOD_PERSON_ID (PERSON_ID)
               SELECT PERSON_ID
                 FROM MEC_PERIOD_LAST_HIST_NEW
                WHERE STATUS = 'N' AND ROWNUM < v_batch_size;

            v_rec_cnt := SQL%ROWCOUNT;
            v_misc_text :=
                  ' v_batch_size : '
               || v_batch_size
               || ' To Be Processed Person_ID Count : '
               || v_rec_cnt;
         ELSE
            SELECT job
              INTO v_job_number
              FROM SYS.DBA_JOBS J
             WHERE UPPER (J.WHAT) LIKE
                         '%'
                      || g_pkg_name
                      || '%';

            UPDATE ADRMGT.ADR_PARAMETER
               SET VALUE = 'DELTA', RECORD_MODIFIED_BY = SUBSTR (g_job_name, 1, 29), RECORD_MODIFIED_DATE = SYSDATE
             WHERE APPLICATION_NAME = 'ACA_MEC' AND UPPER (PARAMETER_NAME) = 'MEC_PERIOD_LOAD_TYPE' AND INACTIVE_FLAG = 'N';

            -- change the job interval
            DBMS_JOB.change (v_job_number,
                             NULL,
                             NULL,
                             'TRUNC(SYSDATE+1)+7/24');

            v_transition_text := ' : Auto-Transitioning from INITIAL TO DELTA ';
         END IF;
      END IF;

      IF v_loading_type <> 'INITIAL' OR v_tobe_process_cnt = 0
      THEN
         -- for current run ; this is for todays run
         SELECT MAX (Enrollment_Determination_h_id) INTO v_upper_bound FROM Enrollment_Determination_H;

         INSERT INTO STG_MEC_PERIOD_PERSON_ID (PERSON_ID)
            SELECT DISTINCT s.person_id
              FROM Enrollment_Determination_H s
             WHERE Enrollment_Determination_h_id > g_current_lower_bound AND Enrollment_Determination_h_id <= v_upper_bound;

         v_rec_cnt := SQL%ROWCOUNT;
         v_misc_text :=
               v_transition_text
            || ' To Be Processed Person_ID Count : '
            || v_rec_cnt
            || ' v_upper_bound: '
            || v_upper_bound;
      END IF;

      COMMIT;
      WRAPPER_JOB_PROCESS_LOG_SP ('INFO',
                                     'STEP 0 - METADATA: (COLLECT) '
                                  || v_misc_text
                                  || Calc_Exec_time (v_start_dt));

      PREP_WORK_MEC_PERIOD (v_upper_bound, v_multi_batch_flg);

      WRAPPER_JOB_PROCESS_LOG_SP ('INFO',
                                     'COMPLETE: Total inserted records : '
                                  || g_final_rec_cnt
                                  || ' Total '
                                  || Calc_Exec_time (v_start_dt));
   EXCEPTION
      WHEN OTHERS
      THEN
         WRAPPER_JOB_PROCESS_LOG_SP ('ERROR',
                                        SQLERRM
                                     || ' : '
                                     || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
         ROLLBACK;
   END KICK_OFF_MEC_PERIOD;
END MEC_PERIOD_PKG;
/


CREATE OR REPLACE PUBLIC SYNONYM MEC_PERIOD_PKG FOR ADR.MEC_PERIOD_PKG;
