DROP PROCEDURE ADR.ACTIVEAGG_TRACKER_PROC;

CREATE OR REPLACE PROCEDURE ADR.ACTIVEAGG_TRACKER_PROC
IS
   gc_schema_owner_name        VARCHAR2 (20) := 'ADR';
   v_partition_high_val_date   DATE;
   v_sql                       VARCHAR2 (2000);
   v_cut_off_date              INTEGER := 6;
   v_CURRENT_ACTIVE_DATE       DATE;
   v_PREVIOUS_ACTIVE_DATE      DATE;
BEGIN
   SELECT CURRENT_ACTIVE_DATE, PREVIOUS_ACTIVE_DATE
     INTO v_CURRENT_ACTIVE_DATE, v_PREVIOUS_ACTIVE_DATE
     FROM ADR.ACTIVEAGG_TRACKER
    WHERE LOADING_STATUS = 'YES';

   ADRMGT.Job_Process_Log_Sp (PC_job_name          => SUBSTR ('ACTIVEAGG_TRACKER_AUR_TRGR', 1, 60),
                              PD_job_log_date      => SYSDATE,
                              PC_job_status        => 'SUCCESS',
                              PC_JOB_LOG_MESSAGE   => SUBSTR (   'v_CURRENT_ACTIVE_DATE : '
                                                              || v_CURRENT_ACTIVE_DATE
                                                              || ' v_PREVIOUS_ACTIVE_DATE: '
                                                              || v_PREVIOUS_ACTIVE_DATE,
                                                              1,
                                                              60));

   FOR r IN (SELECT table_name,
                    partition_name,
                    high_value,
                    interval
               FROM user_tab_partitions
              WHERE UPPER (table_name) = ('ACTIVEAGG'))
   LOOP
      EXECUTE IMMEDIATE
            'SELECT '
         || r.high_value
         || ' from dual'
         INTO v_partition_high_val_date;

      IF     (TRUNC (v_partition_high_val_date) < TRUNC (v_CURRENT_ACTIVE_DATE - v_cut_off_date))
         AND TRUNC (v_partition_high_val_date) <> TRUNC (v_PREVIOUS_ACTIVE_DATE)
      THEN
         IF r.interval = 'NO'
         THEN
            v_sql :=
                  'ALTER TABLE '
               || gc_schema_owner_name
               || '.'
               || r.table_name
               || ' TRUNCATE PARTITION '
               || r.partition_name;

            ADRMGT.Job_Process_Log_Sp (PC_job_name          => SUBSTR ('ACTIVEAGG_TRACKER_AUR_TRGR', 1, 60),
                                       PD_job_log_date      => SYSDATE,
                                       PC_job_status        => 'SUCCESS',
                                       PC_JOB_LOG_MESSAGE   => SUBSTR (   v_sql
                                                                       || ' '
                                                                       || v_partition_high_val_date,
                                                                       1,
                                                                       60));

            EXECUTE IMMEDIATE v_sql;
         ELSE
            v_sql :=
                  'ALTER TABLE '
               || gc_schema_owner_name
               || '.'
               || r.table_name
               || ' DROP PARTITION '
               || r.partition_name;

            ADRMGT.Job_Process_Log_Sp (PC_job_name          => SUBSTR ('ACTIVEAGG_TRACKER_AUR_TRGR', 1, 60),
                                       PD_job_log_date      => SYSDATE,
                                       PC_job_status        => 'SUCCESS',
                                       PC_JOB_LOG_MESSAGE   => SUBSTR (   v_sql
                                                                       || ' '
                                                                       || v_partition_high_val_date,
                                                                       1,
                                                                       60));

            EXECUTE IMMEDIATE v_sql;
         END IF;
      END IF;
   END LOOP;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      ADRMGT.Job_Process_Log_Sp (
         PC_job_name          => SUBSTR ('ACTIVEAGG_TRACKER_AUR_TRGR', 1, 60),
         PD_job_log_date      => SYSDATE,
         PC_job_status        => 'SUCCESS',
         PC_JOB_LOG_MESSAGE   => SUBSTR ('Nothing to drop; no record FOUND in ADr.ACTIVEAGG_TRACKER  WHERE LOADING_STATUS = ''YES''', 1, 60));
   WHEN OTHERS
   THEN
      ADRMGT.Job_Process_Log_Sp (PC_job_name          => SUBSTR ('ACTIVEAGG_TRACKER_AUR_TRGR', 1, 60),
                                 PD_job_log_date      => SYSDATE,
                                 PC_job_status        => 'PARTITION DROP FAILED',
                                 PC_JOB_LOG_MESSAGE   => SUBSTR (   DBMS_UTILITY.FORMAT_CALL_STACK
                                                                 || DBMS_UTILITY.format_error_stack
                                                                 || DBMS_UTILITY.format_error_backtrace,
                                                                 1,
                                                                 60));
END ACTIVEAGG_TRACKER_PROC;
/
DROP PROCEDURE ADR.ADR_ADDRESS_Z05_UPDATE_SP;

CREATE OR REPLACE PROCEDURE ADR.Adr_Address_Z05_Update_Sp
  (PB_run_standalone
     IN     BOOLEAN DEFAULT FALSE,
   PB_allow_rpt_inst
     IN     BOOLEAN DEFAULT FALSE
  )
IS

-- ***************************************************************************
-- * Procedure: Adr_Address_Z05_Update_Sp
-- *
-- *  Temporary 3.1 Address table bug fix to be run nightly.
-- *
-- *  - Selects all temporary Address records where the Institution_id or the
-- *    Address_change_src_type_id is NULL
-- *  - If Institution_id is NULL, try to find valid Institution_id and update
-- *    column with new value.
-- *  - Report any rows where the Institution_id cannot be set.
-- *  - If Address_change_src_type_id is NULL, update column with value of
-- *    1466952
-- *
-- * Parameters:
-- *
-- *  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
-- *
-- ***************************************************************************
--
-- ===========================================================================
-- Date        Author            Description
-- ===========================================================================
-- 04/05/2010  Craig Wood        Created for CCR 1156.
-- 05/03/2010  Craig Wood        Add logic to check for merged address rows
--
-- ===========================================================================

-- CONSTANTS

  CC_temp_addr_id                      CONSTANT NUMBER := 1449005;
  CC_inst_id_mask                      CONSTANT VARCHAR2(5) := 'VAMC-';
  CN_addr_chg_src_type                 CONSTANT NUMBER := 1466952;

-- VARIABLES

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

  VC_allow_rpt_inst                    VARCHAR2(5) := NULL;
  VC_instance_name                     VARCHAR2(16);
  VC_process_duration                  VARCHAR2(25);
  VC_process_name                      VARCHAR2(60) := 'ADR Update Temp Addresses (Z05)';
  VC_run_standalone                    VARCHAR2(5) := NULL;

  VD_log_date                          DATE;
  VD_process_end_date                  DATE;
  VD_process_start_date                DATE;
  VD_rec_group_date                    DATE;

  VN_addr_chg_src_type_id              NUMBER := 0;
  VN_job_count                         NUMBER := 0;
  VN_job_number                        NUMBER := 0;
  VN_inst_id_upd_count                 NUMBER := 0;
  VN_error_recs_count                  NUMBER := 0;
  VN_no_inst_id_count                  NUMBER := 0;
  VN_rec_count                         NUMBER := 0;
  VN_src_type_upd_count                NUMBER := 0;
  VN_std_institution_id                NUMBER := 0;
  VN_total_recs_count                  NUMBER := 0;

  CURSOR C_addr_rec IS
    SELECT    A.Address_id,
              A.Person_id,
              A.Address_change_src_type_id,
              A.Std_institution_id,
              A.Record_created_by,
              A.Record_modified_by
    FROM      ADR.Address A
    WHERE     A.Std_addresstype_id = CC_temp_addr_id
    AND       (Std_institution_id IS NULL
              OR
               Address_change_src_type_id IS NULL);

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

BEGIN

  VD_rec_group_date := TRUNC(SYSDATE);

  VD_log_date := SYSDATE;
  ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                            PD_job_log_date => VD_log_date,
                            PC_job_status => SUBSTR('Process Initiation',1,60),
                            PD_date1 => VD_rec_group_date,
                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

-- ***************************************************************************
-- *
-- * Validate 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(VC_process_name,1,60),
                            PD_job_log_date => VD_log_date,
                            PC_job_status => SUBSTR('INFORMATION - Processing parameters for this job',1,60),
                            PC_job_log_message => SUBSTR('Current job parameters - '
                                                      || ' Run standalone: ' || VC_run_standalone
                                                      || ' Run on reporting databases: ' || VC_allow_rpt_inst,1,2000),
                            PD_date1 => VD_rec_group_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.
-- *
-- ***************************************************************************

  IF NOT VB_allow_rpt_inst THEN

    BEGIN

      VD_log_date := SYSDATE;
      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('INFORMATION - Checking if running on a reporting database',1,60),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      SELECT    Instance_name
      INTO      VC_instance_name
      FROM      V$instance;

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

        VD_log_date := SYSDATE;
        ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                                  PD_job_log_date => VD_log_date,
                                  PC_job_status => SUBSTR('WARNING     - Processing Failed',1,60),
                                  PC_job_log_message => SUBSTR('Attempting to run on a reporting instance. Instance name: '
                                                            || VC_instance_name
                                                            || '. 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_rec_group_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(VC_process_name,1,60),
                                  PD_job_log_date => VD_log_date,
                                  PC_job_status => SUBSTR('ERROR       - Unable to retrieve instance name',1,60),
                                  PC_job_log_message => SUBSTR('Terminating script.',1,2000),
                                  PD_date1 => VD_rec_group_date,
                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

        RETURN;

    END;

  END IF;

-- ***************************************************************************
-- *
-- * Check to see if the job has already run today
-- *  - If running in stand-alone mode, don't check.
-- *  - Otherwise, if job has already run today, then exit the job.
-- *
-- ***************************************************************************

  IF NOT PB_run_standalone THEN

    BEGIN

      SELECT    COUNT(*)
      INTO      VN_rec_count
      FROM      ADRMGT.Job_Process_Logs
      WHERE     Job_name = VC_process_name
      AND       Date1 = VD_rec_group_date
      AND       Job_status = 'SUCCESS     - Processing Completed';

      IF VN_rec_count > 0 THEN

        VD_log_date := SYSDATE;
        ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                                  PD_job_log_date => VD_log_date,
                                  PC_job_status => SUBSTR('INFORMATION - This date has already been processed',1,60),
                                  PC_job_log_message => SUBSTR('Processing has already completed for this date. Exiting to job queue.',1,2000),
                                  PD_date1 => VD_rec_group_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(VC_process_name,1,60),
                                  PD_job_log_date => VD_log_date,
                                  PC_job_status => SUBSTR('ERROR       - Unable to verify if job has already run',1,60),
                                  PC_job_log_message => SUBSTR('Terminating script.',1,2000),
                                  PD_date1 => VD_rec_group_date,
                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

        RETURN;

    END;

  END IF;

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

  SELECT COUNT(*)
  INTO   VN_job_count
  FROM   SYS.DBA_JOBS_RUNNING R,
         SYS.DBA_JOBS J
  WHERE  J.JOB = R.JOB
  AND    UPPER(J.WHAT) LIKE '%ADR_ADDRESS_Z05_UPDATE_SP%';

  IF VN_job_count > 1 THEN

    VD_log_date := SYSDATE;
    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('WARNING     - Check for running jobs',1,60),
                              PC_job_log_message => SUBSTR('There is another address (Z05) update job running.'
                                                        || ' Exiting job without running.',1,2000),
                              PD_date1 => VD_rec_group_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(VC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('INFORMATION - Starting in STANDALONE mode',1,60),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    ELSE

      SELECT    MAX(J.Job)
      INTO      VN_job_number
      FROM      SYS.DBA_JOBS_RUNNING R,
                SYS.DBA_JOBS J
      WHERE     J.JOB = R.JOB
      AND       UPPER(J.WHAT) LIKE '%ADR_ADDRESS_Z05_UPDATE_SP%';

      VD_log_date := SYSDATE;
      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('INFORMATION - Starting in DBMS_JOB mode',1,60),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    END IF;

  END IF;

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

  VN_error_recs_count := 0;
  VN_inst_id_upd_count := 0;
  VN_no_inst_id_count := 0;
  VN_src_type_upd_count := 0;
  VN_total_recs_count := 0;

  VD_process_start_date := SYSDATE;

-- Remove all process logs over 14 days old

  BEGIN

    VD_log_date := SYSDATE;
    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Removing old process logs',1,60),
                              PC_job_log_message => SUBSTR('Removing process logs over 14 days old',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    DELETE
    FROM      ADRMGT.Job_Process_Logs
    WHERE     Job_name = VC_process_name
    AND       Date1 < TRUNC(SYSDATE) - 14;

    COMMIT;

    VD_log_date := SYSDATE;
    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Removed old process logs',1,60),
                              PC_job_log_message => SUBSTR('Removed process logs over 14 days old',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));


  EXCEPTION

    WHEN NO_DATA_FOUND THEN

      NULL;

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;
      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(VC_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 removing old process logs. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  END;

-- Remove all process errors over 14 days old

  BEGIN

    VD_log_date := SYSDATE;
    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Removing old process errors',1,60),
                              PC_job_log_message => SUBSTR('Removing process errors over 14 days old',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    DELETE
    FROM      ADRMGT.Job_Process_Errs
    WHERE     Job_name = VC_process_name
    AND       Date1 < TRUNC(SYSDATE) - 14;

    COMMIT;

    VD_log_date := SYSDATE;
    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Removed old process errors',1,60),
                              PC_job_log_message => SUBSTR('Removed process errors over 14 days old',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN NO_DATA_FOUND THEN

      NULL;

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;
      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(VC_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 removing old process errors. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  END;

-- Main record cursor logic

  FOR I_addr_rec IN C_addr_rec LOOP

    VB_error := FALSE;
    VB_update_row := FALSE;
    VN_total_recs_count := VN_total_recs_count + 1;
    VN_addr_chg_src_type_id := I_addr_rec.Address_change_src_type_id;
    VN_std_institution_id := I_addr_rec.Std_institution_id;

-- Logic for Address_change_src_type_id

    IF VN_addr_chg_src_type_id IS NULL THEN

      VN_addr_chg_src_type_id := CN_addr_chg_src_type;
      VB_update_row := TRUE;
      VN_src_type_upd_count := VN_src_type_upd_count + 1;

    END IF;

-- Logic for Std_institution_id

    IF VN_std_institution_id IS NULL THEN

--   First try for station number from record_modified_by column

      IF I_addr_rec.Record_modified_by LIKE CC_inst_id_mask || '%' THEN

        BEGIN

          SELECT    Id
          INTO      VN_std_institution_id
          FROM      Std_Institution
          WHERE     Stationnumber = SUBSTR(I_addr_rec.Record_modified_by,
                                           6,
                                           LENGTH(I_addr_rec.Record_modified_by)-5);

          VB_update_row := TRUE;
          VN_inst_id_upd_count := VN_inst_id_upd_count + 1;

        EXCEPTION

          WHEN NO_DATA_FOUND THEN

            NULL;

          WHEN OTHERS THEN

            VB_error := TRUE;
            VN_error_recs_count := VN_error_recs_count + 1;

            VD_log_date := SYSDATE;
            ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(VC_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 fetching Std_institution_id from Record_modified_by.'
                                                                  || ' Std_institution_id: '
                                                                  || SUBSTR(I_addr_rec.Record_modified_by,6,LENGTH(I_addr_rec.Record_modified_by)-5)
                                                                  || '. '
                                                                  || SQLERRM,1,2000),
                                      PD_date1 => VD_rec_group_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255),
                                      PN_numeric1 => I_addr_rec.Address_id);

        END;

      END IF;

--   If still no institution_id, try for station number from record_created_by column

      IF NOT VB_error
       AND VN_std_institution_id IS NULL
       AND I_addr_rec.Record_created_by LIKE CC_inst_id_mask || '%' THEN

        BEGIN

          SELECT    Id
          INTO      VN_std_institution_id
          FROM      Std_Institution
          WHERE     Stationnumber = SUBSTR(I_addr_rec.Record_created_by,
                                           6,
                                           LENGTH(I_addr_rec.Record_created_by)-5);

          VB_update_row := TRUE;
          VN_inst_id_upd_count := VN_inst_id_upd_count + 1;

        EXCEPTION

          WHEN NO_DATA_FOUND THEN

            NULL;

          WHEN OTHERS THEN

            VB_error := TRUE;
            VN_error_recs_count := VN_error_recs_count + 1;

            VD_log_date := SYSDATE;
            ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(VC_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 fetching Std_institution_id from Record_created_by.'
                                                                  || ' Std_institution_id: '
                                                                  || SUBSTR(I_addr_rec.Record_created_by,6,LENGTH(I_addr_rec.Record_created_by)-5)
                                                                  || '.'
                                                                  || SQLERRM,1,2000),
                                      PD_date1 => VD_rec_group_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255),
                                      PN_numeric1 => I_addr_rec.Address_id);

        END;

      END IF;

--   If still no institution_id, try for institution_id from address history

      IF NOT VB_error
       AND VN_std_institution_id IS NULL THEN

        BEGIN

          SELECT    A1.Std_institution_id
          INTO      VN_std_institution_id
          FROM      ADR.Address_H A1
          WHERE     A1.Person_id = I_addr_rec.Person_id -- No index on Address_id, Person_id is next best thing
          AND       A1.Address_h_id =
                      (SELECT    MAX(A2.Address_h_id)
                       FROM      ADR.Address_H A2
                       WHERE     A2.Person_id = I_addr_rec.Person_id -- No index on Address_id, Person_id is next best thing
                       AND       A2.Address_Id = I_addr_rec.Address_id
                       AND       A2.Std_institution_id IS NOT NULL
                       AND       A2.Transaction_type_code != 'D');

          VB_update_row := TRUE;
          VN_inst_id_upd_count := VN_inst_id_upd_count + 1;

        EXCEPTION

          WHEN NO_DATA_FOUND THEN

            NULL;

          WHEN OTHERS THEN

            VB_error := TRUE;
            VN_error_recs_count := VN_error_recs_count + 1;

            VD_log_date := SYSDATE;
            ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(VC_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 fetching Std_institution_id from Address_H table.'
                                                                  || SQLERRM,1,2000),
                                      PD_date1 => VD_rec_group_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255),
                                      PN_numeric1 => I_addr_rec.Address_id);

        END;

      END IF;

--   If still no institution_id, try for institution_id from merged person address history

      IF NOT VB_error
       AND VN_std_institution_id IS NULL THEN

        BEGIN

          SELECT    AH.Std_institution_id
          INTO      VN_std_institution_id
          FROM      ADR.Person_Merge PM,
                    ADR.Address_H AH
          WHERE     PM.Primary_person_id = I_addr_rec.Person_id
          AND       AH.Address_h_id =
                    (SELECT    MAX(AH2.Address_h_id)
                     FROM      ADR.Address_H AH2
                     WHERE     AH2.Person_id = PM.Deprecated_person_id
                     AND       AH2.Std_addresstype_id = CC_temp_addr_id
                     AND       AH2.Std_institution_id IS NOT NULL);

          VB_update_row := TRUE;
          VN_inst_id_upd_count := VN_inst_id_upd_count + 1;

        EXCEPTION

          WHEN NO_DATA_FOUND THEN

            NULL;

          WHEN OTHERS THEN

            VB_error := TRUE;
            VN_error_recs_count := VN_error_recs_count + 1;

            VD_log_date := SYSDATE;
            ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(VC_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 fetching Std_institution_id '
                                                                  ||'from merged person Address_H table.'
                                                                  || SQLERRM,1,2000),
                                      PD_date1 => VD_rec_group_date,
                                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255),
                                      PN_numeric1 => I_addr_rec.Address_id);

        END;

      END IF;

--   If still no institution_id, it's toast. Write an error.


      IF NOT VB_error
       AND VN_std_institution_id IS NULL THEN

        VN_no_inst_id_count := VN_no_inst_id_count + 1;

        VD_log_date := SYSDATE;
        ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                                  PD_job_error_date  => VD_log_date,
                                  PC_job_error_code => SUBSTR(SQLCODE,1,60),
                                  PC_job_error_message => SUBSTR('No institution ID found for this address row.',1,2000),
                                  PD_date1 => VD_rec_group_date,
                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255),
                                  PN_numeric1 => I_addr_rec.Address_id);

      END IF;

    END IF;

    IF VB_update_row THEN

      BEGIN

        UPDATE    ADR.Address
        SET       Address_change_src_type_id = VN_addr_chg_src_type_id,
                  Std_institution_id = VN_std_institution_id,
                  Record_modified_by = 'ADRCQ00001162',
                  Record_modified_date = SYSDATE,
                  Record_modified_count = Record_modified_count + 1
        WHERE     Address_id = I_addr_rec.Address_id;

        COMMIT;

      EXCEPTION

        WHEN OTHERS THEN

          VN_error_recs_count := VN_error_recs_count + 1;

          ROLLBACK;

          VD_log_date := SYSDATE;
          ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(VC_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 updating ADR.Address row.'
                                                                || SQLERRM,1,2000),
                                    PD_date1 => VD_rec_group_date,
                                    PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255),
                                    PN_numeric1 => I_addr_rec.Address_id);

      END;

    END IF;

  END LOOP;

-- Process completion information

  VD_log_date := SYSDATE;
  ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                            PD_job_log_date => VD_log_date,
                            PC_job_status => SUBSTR('INFORMATION - Process Statistics',1,60),
                            PD_date1 => VD_rec_group_date,
                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255),
                            PN_numeric2 => VN_total_recs_count,
                            PC_text2 => SUBSTR('Temp addresses reviewed',1,255));

  VD_log_date := SYSDATE;
  ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                            PD_job_log_date => VD_log_date,
                            PC_job_status => SUBSTR('INFORMATION - Process Statistics',1,60),
                            PD_date1 => VD_rec_group_date,
                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255),
                            PN_numeric2 => VN_src_type_upd_count,
                            PC_text2 => SUBSTR('Change source types updated',1,255));

  VD_log_date := SYSDATE;
  ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                            PD_job_log_date => VD_log_date,
                            PC_job_status => SUBSTR('INFORMATION - Process Statistics',1,60),
                            PD_date1 => VD_rec_group_date,
                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255),
                            PN_numeric2 => VN_inst_id_upd_count,
                            PC_text2 => SUBSTR('Institution IDs updated',1,255));

  VD_log_date := SYSDATE;
  ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                            PD_job_log_date => VD_log_date,
                            PC_job_status => SUBSTR('INFORMATION - Process Statistics',1,60),
                            PD_date1 => VD_rec_group_date,
                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255),
                            PN_numeric2 => VN_no_inst_id_count,
                            PC_text2 => SUBSTR('Institution IDs not updated',1,255));

  VD_log_date := SYSDATE;
  ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                            PD_job_log_date => VD_log_date,
                            PC_job_status => SUBSTR('INFORMATION - Process Statistics',1,60),
                            PD_date1 => VD_rec_group_date,
                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255),
                            PN_numeric2 => VN_error_recs_count,
                            PC_text2 => SUBSTR('***Errors updating address rows***',1,255));

  VD_process_end_date := SYSDATE;
  VC_process_duration := LTRIM(TO_CHAR(TRUNC((VD_process_end_date - VD_process_start_date)*24),'00')) || ' Hrs '
                         || LTRIM(TO_CHAR(TRUNC((((VD_process_end_date - VD_process_start_date)*24)-
                            TRUNC(((VD_process_end_date - VD_process_start_date)*24))) * 60),'00')) || ' Mins '
                         || LTRIM(TO_CHAR(TRUNC((((VD_process_end_date - VD_process_start_date)*24*60)-
                            TRUNC(((VD_process_end_date - VD_process_start_date)*24*60))) * 60),'00')) || ' Secs';

  VD_log_date := SYSDATE;
  ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                            PD_job_log_date => VD_log_date,
                            PC_job_status => SUBSTR('SUCCESS     - Processing Completed',1,60),
                            PC_job_log_message => SUBSTR('Processing Time: ' || VC_process_duration,1,2000),
                            PD_date1 => VD_rec_group_date,
                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

EXCEPTION

  WHEN OTHERS THEN

    ROLLBACK;

    VD_log_date := SYSDATE;
    ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(VC_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 processing error in procedure Adr_Address_Z05_Update_Sp.'
                                                          || SQLERRM,1,2000),
                              PD_date1 => VD_rec_group_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(VC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('ERROR       - Unknown processing error',1,60),
                              PC_job_log_message => SUBSTR('See error log table for details.',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

END Adr_Address_Z05_Update_Sp;
/
DROP PROCEDURE ADR.ADR_DELETE_ADDRESS_SP;

CREATE OR REPLACE PROCEDURE ADR.Adr_delete_address_Sp
  (PB_run_standalone
     IN     BOOLEAN DEFAULT FALSE,
   PB_allow_rpt_inst
     IN     BOOLEAN DEFAULT FALSE
  )
IS

-- ***************************************************************************
-- * Procedure: Adr_delete_address_Sp
-- *
-- *  Temporary  Address table bug fix to be run nightly.
-- *
-- *  - Selects all Address records where the INSURANCE_ID STD_ADDRESSTYPE_ID
-- *     is NULL
-- *
-- *
-- * Parameters:
-- *
-- *  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
-- *
-- ***************************************************************************
--
-- ===========================================================================
-- Date        Author                           Description
-- ===========================================================================
-- 11/30/2010  Brenda Canington       Created for CCR 1252.

--
-- ===========================================================================

-- CONSTANTS


-- VARIABLES

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

  VC_allow_rpt_inst                    VARCHAR2(5) := NULL;
  VC_instance_name                     VARCHAR2(16);
  VC_process_duration                  VARCHAR2(25);
  VC_process_name                      VARCHAR2(60) := 'ADR Delete Address Records';
  VC_run_standalone                    VARCHAR2(5) := NULL;

  VD_log_date                          DATE;
  VD_process_end_date                  DATE;
  VD_process_start_date                DATE;
  VD_rec_group_date                    DATE;

  VN_addr_chg_src_type_id              NUMBER := 0;
  VN_job_count                         NUMBER := 0;
  VN_job_number                        NUMBER := 0;
  VN_inst_id_upd_count                 NUMBER := 0;
  VN_error_recs_count                  NUMBER := 0;
  VN_no_inst_id_count                  NUMBER := 0;
  VN_rec_count                         NUMBER := 0;
  VN_src_type_upd_count                NUMBER := 0;
  VN_std_institution_id                NUMBER := 0;
  VN_total_recs_count                  NUMBER := 0;
  sql_stmt                             varchar2(500);

-- CURSORS

CURSOR my_del_records  IS
     SELECT A.ADDRESS_ID
     FROM      ADR.Address A
    WHERE     A.STD_ADDRESSTYPE_ID IS NULL
    AND
                   A.INSURANCE_ID IS NULL;

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

BEGIN

  VD_rec_group_date := TRUNC(SYSDATE);

  VD_log_date := SYSDATE;
  ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                            PD_job_log_date => VD_log_date,
                            PC_job_status => SUBSTR('Process Initiation',1,60),
                            PD_date1 => VD_rec_group_date,
                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

-- ***************************************************************************
-- *
-- * Validate 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(VC_process_name,1,60),
                            PD_job_log_date => VD_log_date,
                            PC_job_status => SUBSTR('INFORMATION - Processing parameters for this job',1,60),
                            PC_job_log_message => SUBSTR('Current job parameters - '
                                                      || ' Run standalone: ' || VC_run_standalone
                                                      || ' Run on reporting databases: ' || VC_allow_rpt_inst,1,2000),
                            PD_date1 => VD_rec_group_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.
-- *
-- ***************************************************************************

  IF NOT VB_allow_rpt_inst THEN

    BEGIN

      VD_log_date := SYSDATE;
      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('INFORMATION - Checking if running on a reporting database',1,60),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      SELECT    Instance_name
      INTO      VC_instance_name
      FROM      V$instance;

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

        VD_log_date := SYSDATE;
        ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                                  PD_job_log_date => VD_log_date,
                                  PC_job_status => SUBSTR('WARNING     - Processing Failed',1,60),
                                  PC_job_log_message => SUBSTR('Attempting to run on a reporting instance. Instance name: '
                                                            || VC_instance_name
                                                            || '. 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_rec_group_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(VC_process_name,1,60),
                                  PD_job_log_date => VD_log_date,
                                  PC_job_status => SUBSTR('ERROR       - Unable to retrieve instance name',1,60),
                                  PC_job_log_message => SUBSTR('Terminating script.',1,2000),
                                  PD_date1 => VD_rec_group_date,
                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

        RETURN;

    END;

  END IF;

-- ***************************************************************************
-- *
-- * Check to see if the job has already run today
-- *  - If running in stand-alone mode, don't check.
-- *  - Otherwise, if job has already run today, then exit the job.
-- *
-- ***************************************************************************

  IF NOT PB_run_standalone THEN

    BEGIN

      SELECT    COUNT(*)
      INTO      VN_rec_count
      FROM      ADRMGT.Job_Process_Logs
      WHERE     Job_name = VC_process_name
      AND       Date1 = VD_rec_group_date
      AND       Job_status = 'SUCCESS     - Processing Completed';

      IF VN_rec_count > 0 THEN

        VD_log_date := SYSDATE;
        ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                                  PD_job_log_date => VD_log_date,
                                  PC_job_status => SUBSTR('INFORMATION - This date has already been processed',1,60),
                                  PC_job_log_message => SUBSTR('Processing has already completed for this date. Exiting to job queue.',1,2000),
                                  PD_date1 => VD_rec_group_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(VC_process_name,1,60),
                                  PD_job_log_date => VD_log_date,
                                  PC_job_status => SUBSTR('ERROR       - Unable to verify if job has already run',1,60),
                                  PC_job_log_message => SUBSTR('Terminating script.',1,2000),
                                  PD_date1 => VD_rec_group_date,
                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

        RETURN;

    END;

  END IF;

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

  SELECT COUNT(*)
  INTO   VN_job_count
  FROM   SYS.DBA_JOBS_RUNNING R,
         SYS.DBA_JOBS J
  WHERE  J.JOB = R.JOB
  AND    UPPER(J.WHAT) LIKE '%ADR_DELETE_ADDRESS_SP%';

  IF VN_job_count > 1 THEN

    VD_log_date := SYSDATE;
    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('WARNING     - Check for running jobs',1,60),
                              PC_job_log_message => SUBSTR('There is another address Delete job running.'
                                                        || ' Exiting job without running.',1,2000),
                              PD_date1 => VD_rec_group_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(VC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('INFORMATION - Starting in STANDALONE mode',1,60),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    ELSE

      SELECT    MAX(J.Job)
      INTO      VN_job_number
      FROM      SYS.DBA_JOBS_RUNNING R,
                SYS.DBA_JOBS J
      WHERE     J.JOB = R.JOB
      AND       UPPER(J.WHAT) LIKE '%ADR_DELETE_ADDRESS_SP%';

      VD_log_date := SYSDATE;
      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                                PD_job_log_date => VD_log_date,
                                PC_job_status => SUBSTR('INFORMATION - Starting in DBMS_JOB mode',1,60),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    END IF;

  END IF;

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

  VN_error_recs_count := 0;
  VN_inst_id_upd_count := 0;
  VN_no_inst_id_count := 0;
  VN_src_type_upd_count := 0;
  VN_total_recs_count := 0;

  VD_process_start_date := SYSDATE;

-- Remove all process logs over 14 days old

  BEGIN

    VD_log_date := SYSDATE;
    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Removing old process logs',1,60),
                              PC_job_log_message => SUBSTR('Removing process logs over 14 days old',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    DELETE
    FROM      ADRMGT.Job_Process_Logs
    WHERE     Job_name = VC_process_name
    AND       Date1 < TRUNC(SYSDATE) - 14;

    COMMIT;

    VD_log_date := SYSDATE;
    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Removed old process logs',1,60),
                              PC_job_log_message => SUBSTR('Removed process logs over 14 days old',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));


  EXCEPTION

    WHEN NO_DATA_FOUND THEN

      NULL;

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;
      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(VC_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 removing old process logs. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  END;

-- Remove all process errors over 14 days old

  BEGIN

    VD_log_date := SYSDATE;
    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Removing old process errors',1,60),
                              PC_job_log_message => SUBSTR('Removing process errors over 14 days old',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    DELETE
    FROM      ADRMGT.Job_Process_Errs
    WHERE     Job_name = VC_process_name
    AND       Date1 < TRUNC(SYSDATE) - 14;

    COMMIT;

    VD_log_date := SYSDATE;
    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                              PD_job_log_date => VD_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Removed old process errors',1,60),
                              PC_job_log_message => SUBSTR('Removed process errors over 14 days old',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN NO_DATA_FOUND THEN

      NULL;

    WHEN OTHERS THEN

      VD_log_date := SYSDATE;
      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(VC_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 removing old process errors. '
                                                               || SQLERRM,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  END;

-- Main record cursor logic
BEGIN
    FOR this_del_records in my_del_records LOOP
            sql_stmt := 'DELETE     FROM      ADR.Address A WHERE A.ADDRESS_ID = ' || this_del_records.ADDRESS_ID ;
             EXECUTE IMMEDIATE sql_stmt;
             COMMIT;
          END LOOP;



        EXCEPTION
        WHEN NO_DATA_FOUND THEN
             NULL;
        WHEN OTHERS THEN
          VN_error_recs_count := VN_error_recs_count + 1;
          ROLLBACK;
          VD_log_date := SYSDATE;
          ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(VC_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 from ADR.Address row.'
                                                                || SQLERRM,1,2000),
                                    PD_date1 => VD_rec_group_date,
                                    PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255)
                                    );

      END;



END Adr_delete_address_Sp;
/
DROP PROCEDURE ADR.ADR_INCOME_TEST_FIX_SP;

CREATE OR REPLACE PROCEDURE ADR.Adr_Income_Test_Fix_Sp
  (p_run_standalone
     IN     BOOLEAN DEFAULT FALSE,
   p_allow_rpt_inst
     IN     BOOLEAN DEFAULT FALSE
  )
IS

-- ***************************************************************************
-- * Procedure: Adr_Income_Test_Fix_Sp
-- *
-- *  Fix data in Income_Test table from application bug to be run nightly.
-- *
-- *  - Selects future Income_Test table records for individuals with multiple income_test records and multiple records where the income_test_type_id is the same
--  *  And  the future_test_flag = 1
-- *    And there is also an Income_Test table record Wherfuture_test_flag = 0
-- *  -And  there is a record where the income_test_effective_date = MAX(income_test_effective_date)
-- *   And there is a record where the income_year is greater than the income_year selected where the future_test_flag = 1
-- *
-- *  - It then selects the current year records for each individual in the future Income_Test query
-- *    Where future_test_flag = 0
-- *
-- *  It then updates income_test with correct information.
-- *
-- * Parameters:
-- *
-- *  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
-- *
-- ***************************************************************************
--
-- ===========================================================================
-- Date        Author            Description
-- ===========================================================================
-- 06/22/2010  Craig Wood        Created for CCR 1173.
--
--
-- ===========================================================================

  CURSOR futureyearscur
   IS
      SELECT it1.income_test_id income_test_idf, it1.person_id person_idf,
       it1.income_year income_yearf,
       it1.income_test_effective_date income_test_effective_datef,
       it1.income_test_type_id
  FROM income_test it1
 WHERE it1.future_test_flag = 1
   AND EXISTS (
          SELECT it2.income_year
            FROM income_test it2
           WHERE it2.future_test_flag = 0
             AND it2.income_test_effective_date =
                    (SELECT MAX (it4.income_test_effective_date)
                       FROM income_test it4
                      WHERE it4.person_id = it1.person_id
                        AND it4.income_test_type_id = it1.income_test_type_id
                        AND it4.future_test_flag = 0)
             AND it2.person_id = it1.person_id
             AND it2.income_test_type_id = it1.income_test_type_id
             AND it2.income_year > it1.income_year);


   v_f_income_test_id               income_test.income_test_id%TYPE;
   v_f_person_id                    income_test.person_id%TYPE;
   v_f_income_year                  income_test.income_year%TYPE;
   v_f_income_test_effective_date   income_test.income_test_effective_date%TYPE;
   v_f_income_test_type_id          income_test.income_test_type_id%TYPE;
   v_c_income_test_id               income_test.income_test_id%TYPE;
   v_c_person_id                    income_test.person_id%TYPE;
   v_c_income_year                  income_test.income_year%TYPE;
   v_c_income_test_effective_date   income_test.income_test_effective_date%TYPE;
   v_income_test_effective_date     income_test.income_test_effective_date%TYPE;
   v_income_test_detail_id          income_test_detail.income_test_detail_id%TYPE;
   v_income_test_id                 income_test_detail.income_test_detail_id%TYPE;
   v_income_test_type_id            income_test_detail.income_test_id%TYPE;
   v_test_status_type_id            income_test_detail.test_status_type_id%TYPE;
   v_test_detmnd_status_type_id     income_test_detail.test_detmnd_status_type_id%TYPE;
   v_income_test_last_edited_date   income_test_detail.income_test_last_edited_date%TYPE;
   v_income_test_completed_date     income_test_detail.income_test_completed_date%TYPE;
   v_income_test_expiration_date    income_test_detail.income_test_expiration_date%TYPE;
   v1_income_test_effective_date    income_test_detail.income_test_effective_date%TYPE;
   v_income_test_signed_date        income_test_detail.income_test_signed_date%TYPE;
   vh_income_test_last_edit_date    income_test_detail.income_test_last_edited_date%TYPE;
   vh_income_test_completed_date    income_test_detail.income_test_completed_date%TYPE;
   v_count                                       NUMBER;
   v_debug                                      VARCHAR2(2000);

  v_allow_rpt_inst                      BOOLEAN := FALSE;
  v_run_standalone                      BOOLEAN := FALSE;
  v_instance_name                       VARCHAR2(16);
  v_job_count                           NUMBER := 0;
  vc_allow_rpt_inst                     VARCHAR2(5) := NULL;
  vc_run_standalone                     VARCHAR2(5) := NULL;
  v_log_date                            DATE;
  v_rec_group_date                      DATE;
  v_process_name                        VARCHAR2(60) := 'ADR Income Test Fix';
   CURSOR detailcursor
   IS
      SELECT income_test_detail_id, income_test_id, income_test_type_id,
             test_status_type_id, test_detmnd_status_type_id,
             income_test_last_edited_date, income_test_completed_date,
             income_test_expiration_date, income_test_effective_date,
             income_test_signed_date
        FROM income_test_detail
       WHERE income_test_id = v_f_income_test_id;



BEGIN

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

-- * Check run standalone flag

  IF p_run_standalone IS NULL
   OR NOT p_run_standalone THEN
        v_run_standalone := FALSE;
        vc_run_standalone := 'FALSE'; -- used for log message, boolean can't be displayed
  ELSE
    v_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 p_allow_rpt_inst IS NULL
   OR NOT p_allow_rpt_inst THEN
    v_allow_rpt_inst := FALSE;
    vc_allow_rpt_inst := 'FALSE'; -- used for log message, boolean can't be displayed
  ELSE
    v_allow_rpt_inst := TRUE;
    vc_allow_rpt_inst := 'TRUE'; -- used for log message, boolean can't be displayed
  END IF;
  v_log_date := SYSDATE;
  ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                            PD_job_log_date => v_log_date,
                            PC_job_status => SUBSTR('INFORMATION - Processing parameters for this job',1,60),
                            PC_job_log_message => SUBSTR('Current job parameters - '
                                                      || ' Run standalone: ' || vc_run_standalone
                                                      || ' Run on reporting databases: ' || vc_allow_rpt_inst,1,2000),
                            PD_date1 => v_rec_group_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 v_allow_rpt_inst is FALSE, then it's not allowed. End the job.
-- *
-- ***************************************************************************
    IF NOT v_allow_rpt_inst THEN
        BEGIN
             v_log_date := SYSDATE;
             ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                                PD_job_log_date => v_log_date,
                                PC_job_status => SUBSTR('INFORMATION - Checking if running on a reporting database',1,60),
                                PD_date1 => v_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            SELECT    Instance_name
            INTO      v_instance_name
            FROM      V$instance;
            IF UPPER(SUBSTR(v_instance_name,1,4)) = 'ADRR' THEN
                v_log_date := SYSDATE;
                ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                                  PD_job_log_date => v_log_date,
                                  PC_job_status => SUBSTR('WARNING     - Processing Failed',1,60),
                                  PC_job_log_message => SUBSTR('Attempting to run on a reporting instance. Instance name: '
                                                            || v_instance_name
                                                            || '. This job has been terminated. To run on a reporting instance,'
                                                            || ' set parameter p_allow_rpt_inst to TRUE and resubmit the job.',1,2000),
                                  PD_date1 => v_rec_group_date,
                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));
                RETURN;
             END IF;
             EXCEPTION
                WHEN OTHERS THEN
                  v_log_date := SYSDATE;
                  ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                                  PD_job_log_date => v_log_date,
                                  PC_job_status => SUBSTR('ERROR       - Unable to retrieve instance name',1,60),
                                  PC_job_log_message => SUBSTR('Terminating script.',1,2000),
                                  PD_date1 => v_rec_group_date,
                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

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

    SELECT COUNT(*)
    INTO   v_job_count
    FROM   SYS.DBA_JOBS_RUNNING R,
         SYS.DBA_JOBS J
   WHERE  J.JOB = R.JOB
   AND    UPPER(J.WHAT) LIKE '%ADR_INCOME_TEST_FIX_SP%';
    IF v_job_count > 1 THEN
        BEGIN
            v_log_date := SYSDATE;
            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('WARNING     - Check for running jobs',1,60),
                              PC_job_log_message => SUBSTR('There is another Income Test Fix job running.'
                                                        || ' Exiting job without running.',1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            RETURN;
        END;
    ELSE
        IF v_run_standalone THEN
            v_log_date := SYSDATE;
            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                                PD_job_log_date => v_log_date,
                                PC_job_status => SUBSTR('INFORMATION - Starting in STANDALONE mode',1,60),
                                PD_date1 => v_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));
        END IF;
    END IF;
-- ***************************************************************************
-- *
-- * Main processing loop
-- *
-- ***************************************************************************




-- Remove all process logs over 14 days old

  BEGIN

    v_log_date := SYSDATE;
    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Removing old process logs',1,60),
                              PC_job_log_message => SUBSTR('Removing process logs over 14 days old',1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    DELETE
    FROM      ADRMGT.Job_Process_Logs
    WHERE     Job_name = v_process_name
    AND       Date1 < TRUNC(SYSDATE) - 14;

    COMMIT;

    v_log_date := SYSDATE;
    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Removed old process logs',1,60),
                              PC_job_log_message => SUBSTR('Removed process logs over 14 days old',1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));


  EXCEPTION

    WHEN NO_DATA_FOUND THEN

      NULL;

    WHEN OTHERS THEN

      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('ERROR - UNKOWN EXCEPTION',1,60),
                              PC_job_log_message => SUBSTR(v_debug,1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  END;

    OPEN futureyearscur;
    LOOP
      FETCH futureyearscur
       INTO v_f_income_test_id, v_f_person_id, v_f_income_year,
            v_f_income_test_effective_date, v_f_income_test_type_id;
      EXIT WHEN futureyearscur%NOTFOUND;
      v_debug := '';
      v_debug := v_debug || 'STEP1 for :' || v_f_income_test_id ;
      v_log_date := SYSDATE;
      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Step 1',1,60),
                              PC_job_log_message => SUBSTR(v_debug,1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

--find the current test for this person
  BEGIN
    SELECT income_test_id, person_id, income_year, income_test_effective_date
      INTO v_c_income_test_id, v_c_person_id, v_c_income_year,v_c_income_test_effective_date
      FROM income_test it2
     WHERE it2.income_test_effective_date =
                   (SELECT MAX (income_test_effective_date)
                  FROM income_test
                 WHERE person_id = v_f_person_id AND future_test_flag = 0)
         AND it2.person_id = v_f_person_id AND it2.future_test_flag = 0;

         v_debug := 'STEP2 for :' || v_f_income_test_id|| CHR(10) ||
                           'SELECT income_test_id, person_id, income_year, income_test_effective_date'|| CHR(10) ||
                           'INTO v_c_income_test_id, v_c_person_id, v_c_income_year,v_c_income_test_effective_date '|| CHR(10) ||
                           ' FROM income_test it2' || CHR(10) ||
                           ' WHERE it2.income_test_effective_date ='|| CHR(10) ||
                           '(SELECT MAX (income_test_effective_date)' || CHR(10) ||
                           'FROM income_test' || CHR(10) ||
                           ' WHERE person_id = v_f_person_id AND future_test_flag = 0)' ;

         v_log_date := SYSDATE;
         ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Step 2',1,60),
                              PC_job_log_message => SUBSTR(v_debug,1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));
EXCEPTION

            WHEN TOO_MANY_ROWS THEN
                SELECT income_test_id, person_id, income_year, income_test_effective_date
                INTO v_c_income_test_id, v_c_person_id, v_c_income_year,v_c_income_test_effective_date
                FROM income_test it2
                WHERE it2.income_test_effective_date =
                (SELECT MAX (income_test_effective_date)
                FROM income_test
                WHERE person_id = v_f_person_id AND future_test_flag = 0)
                AND it2.income_year = TO_NUMBER (TO_CHAR (SYSDATE, 'YYYY')) - 1
                AND it2.person_id = v_f_person_id AND it2.future_test_flag = 0;

                v_debug := 'STEP2 for :' || v_f_income_test_id|| CHR(10) ||
                           'SELECT income_test_id, person_id, income_year, income_test_effective_date:'|| CHR(10) ||
                           'INTO v_c_income_test_id, v_c_person_id, v_c_income_year,v_c_income_test_effective_date '|| CHR(10) ||
                           ' FROM income_test it2' || CHR(10) ||
                           ' WHERE it2.income_test_effective_date ='|| CHR(10) ||
                           '(SELECT MAX (income_test_effective_date)' || CHR(10) ||
                           'FROM income_test' || CHR(10) ||
                           ' WHERE person_id = v_f_person_id AND future_test_flag = 0)' || CHR(10) ||
                           ' AND it2.income_year = TO_NUMBER (TO_CHAR (SYSDATE, YYYY)) - 1 ' || CHR(10) ||
                           ' AND it2.person_id = v_f_person_id AND it2.future_test_flag = 0';

                v_log_date := SYSDATE;
                ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('INFORMATION - STEP 2',1,60),
                              PC_job_log_message => SUBSTR(v_debug,1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      WHEN OTHERS THEN
          v_debug := 'UNKNOWN EXCEPTION :  ' ||CHR(10)||
                 'error code is : '||sqlcode || CHR(10)||
                 'error code message is : '||SQLERRM || CHR(10)||
                 'Exception occured for  v_c_income_test_id: ' || v_c_income_test_id|| CHR(10)||
                                        'v_c_person_id:  '||v_c_person_id ;
         v_log_date := SYSDATE;
                ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('ERROR - STEP 2',1,60),
                              PC_job_log_message => SUBSTR(v_debug,1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      END;

-- we have the cuurent income_year and future income year.
      BEGIN
         SELECT DISTINCT income_test_effective_date
           INTO v_income_test_effective_date
           FROM income_test_h
          WHERE person_id = v_f_person_id
            AND income_test_id = v_f_income_test_id
            AND future_test_flag = 0
            AND ( record_modified_by <> 'CCR7649 DataCleanup' OR record_modified_by <> 'ADRCQ00001173');


          v_debug := 'STEP3 [income_test_h]for : v_f_income_test_id '||v_f_income_test_id;
           v_log_date := SYSDATE;
           ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Step 3',1,60),
                              PC_job_log_message => SUBSTR(v_debug,1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_debug := 'NO DATA FOUND IN THE HISTORY TABLE FOR : '
                                || v_f_income_test_id || ' Exiting Loop';
            v_log_date := SYSDATE;
            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('INFORMATION - NO DATA FOUND',1,60),
                              PC_job_log_message => SUBSTR(v_debug,1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));
            GOTO  END_OF_LOOP;
            WHEN TOO_MANY_ROWS THEN

                v_debug := 'TOO MANY ROWS  :  '
	                             || v_income_test_detail_id ||' Exiting Loop';
                v_log_date := SYSDATE;
                ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('INFORMATION - STEP 3',1,60),
                              PC_job_log_message => SUBSTR(v_debug,1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));
              GOTO  END_OF_LOOP;
      END;



      --update income test record
      UPDATE income_test
         SET future_test_flag = 0,
             income_test_effective_date = v_income_test_effective_date,
             cad_adjusted_ind = NULL,
             record_modified_by = 'ADRCQ00001173',
             record_modified_date = SYSDATE,
             record_modified_count = record_modified_count + 1
       WHERE income_test_id = v_f_income_test_id
         AND future_test_flag = 1
         AND person_id = v_f_person_id;


      v_debug := 'STEP4 UPDATE finished';
      v_log_date := SYSDATE;
      ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('INFORMATION - STEP 4',1,60),
                              PC_job_log_message => SUBSTR(v_debug,1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));
      --update income test detail table
      OPEN detailcursor;

      LOOP
         FETCH detailcursor
          INTO v_income_test_detail_id, v_income_test_id,
               v_income_test_type_id, v_test_status_type_id,
               v_test_detmnd_status_type_id, v_income_test_last_edited_date,
               v_income_test_completed_date, v_income_test_expiration_date,
               v1_income_test_effective_date, v_income_test_signed_date;

         EXIT WHEN detailcursor%NOTFOUND;


          v_debug :=     'Inner cursor data :' || v_c_income_test_id
                                 || '--'
                                 || v_c_person_id
                                 || '--'
                                 || v_c_income_year
                                 || '--'
                                 || v_c_income_test_effective_date
                                 || '--'
                                 || v_f_income_test_id
                                 || '--'
                                 || v_f_person_id
                                 || '--'
                                 || v_f_income_year
                                 || '--'
                                 || v_f_income_test_effective_date
                                 || '--'
                                 || v_income_test_completed_date || '--'
                                 || v_income_test_effective_date;

             v_log_date := SYSDATE;
             ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('INFORMATION - Inner Cursor Data',1,60),
                              PC_job_log_message => SUBSTR(v_debug,1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

         BEGIN

            SELECT income_test_last_edited_date,
                   income_test_completed_date
              INTO vh_income_test_last_edit_date,
                   vh_income_test_completed_date
              FROM income_test_detail_h
             WHERE income_test_id = v_income_test_id
               AND income_test_detail_id = v_income_test_detail_id
               AND income_test_type_id = v_income_test_type_id
               AND income_test_completed_date <> v_income_test_completed_date
               AND record_modified_by <> 'ADRCQ00001173' ;

             v_debug := 'Fixing the detail :'
                                  || v_income_test_detail_id
                                  || ' With date :'
                                  || vh_income_test_completed_date;

             v_log_date := SYSDATE;
             ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('INFORMATION - FIXING DETAIL',1,60),
                              PC_job_log_message => SUBSTR(v_debug,1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

            UPDATE income_test_detail
               SET income_test_completed_date = vh_income_test_completed_date,
                   record_modified_by = 'ADRCQ00001173',
                   record_modified_date = SYSDATE,
                   record_modified_count = record_modified_count + 1
               WHERE income_test_detail_id = v_income_test_detail_id;


         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               v_debug := 'NO DETAIL DATA FOUND IN THE HISTORY TABLE FOR :'
                         || v_income_test_detail_id;
               v_log_date := SYSDATE;
               ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('INFORMATION - NO DATA FOUND',1,60),
                              PC_job_log_message => SUBSTR(v_debug,1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));
            WHEN TOO_MANY_ROWS THEN
                  v_debug := 'TOO MANY ROWS for income_test_detail :'
                         || v_income_test_detail_id || 'v_debug :' || v_debug;
                  v_log_date := SYSDATE;
                  ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('INFORMATION - TOO MANY ROWS',1,60),
                              PC_job_log_message => SUBSTR(v_debug,1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

         END;
      END LOOP;

      CLOSE detailcursor;

    <<END_OF_LOOP>>
          v_debug := 'END OF LOOP: '
                      ||  v_c_income_test_id
                            || '--'
                            || v_c_person_id
                            || '--'
                            || v_c_income_year
                            || '--'
                            || v_c_income_test_effective_date
                            || '--'
                            || v_f_income_test_id
                            || '--'
                            || v_f_person_id
                            || '--'
                            || v_f_income_year
                            || '--'
                            || v_f_income_test_effective_date
                            || '--'
                            || v_income_test_effective_date;
            v_log_date := SYSDATE;
            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('INFORMATION - <<END OF LOOP>>',1,60),
                              PC_job_log_message => SUBSTR(v_debug,1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

   END LOOP;

   CLOSE futureyearscur;

   --get the final count
 SELECT count(*) INTO v_count
  FROM income_test it1
 WHERE it1.future_test_flag = 1
   AND EXISTS (
          SELECT it2.income_year
            FROM income_test it2
           WHERE it2.future_test_flag = 0
             AND it2.income_test_effective_date =
                    (SELECT MAX (it4.income_test_effective_date)
                       FROM income_test it4
                      WHERE it4.person_id = it1.person_id
                        AND it4.income_test_type_id = it1.income_test_type_id
                        AND it4.future_test_flag = 0)
             AND it2.person_id = it1.person_id
             AND it2.income_test_type_id = it1.income_test_type_id
             AND it2.income_year > it1.income_year);

       v_debug := 'Completed the cleanup. Total Number of records still need to be fixed [SHOULD BE 0]:'
       || v_count;
       v_log_date := SYSDATE;
       ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('INFORMATION - CLEANUP COMPLETED',1,60),
                              PC_job_log_message => SUBSTR(v_debug,1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
            v_log_date := SYSDATE;
            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('INFORMATION - NO DATA FOUND',1,60),
                              PC_job_log_message => SUBSTR('NO DATA FOUND',1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));
   WHEN OTHERS
   THEN
      v_debug := 'UNKNOWN EXCEPTION :' ||CHR(10)||
                 'error code is : '||sqlcode || CHR(10)||
                 'error code message is : '||SQLERRM || CHR(10)||
                 'Exception occured for  ' || v_c_income_test_id
                            || '--'
                            || v_c_person_id
                            || '--'
                            || v_c_income_year
                            || '--'
                            || v_c_income_test_effective_date
                            || '--'
                            || v_f_income_test_id
                            || '--'
                            || v_f_person_id
                            || '--'
                            || v_f_income_year
                            || '--'
                            || v_f_income_test_effective_date
                            || '--'
                            || v_income_test_effective_date;
           v_log_date := SYSDATE;
           ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(v_process_name,1,60),
                              PD_job_log_date => v_log_date,
                              PC_job_status => SUBSTR('ERROR - UNKOWN EXCEPTION',1,60),
                              PC_job_log_message => SUBSTR(v_debug,1,2000),
                              PD_date1 => v_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

END;
/
DROP PROCEDURE ADR.BATCH_SSN_PROCESSED_SP;

CREATE OR REPLACE PROCEDURE ADR.BATCH_SSN_PROCESSED_SP
( P_batch_SSN_ADRToProcess_DIR 	IN VARCHAR2,
  P_batch_SSN_ToProcess_DIR 	IN VARCHAR2,
  P_batch_SSN_log_DIR 		IN VARCHAR2,
  P_batch_SSN_err_DIR 		IN VARCHAR2,
  P_num_files_process		IN NUMBER DEFAULT 2,
  PB_run_standalone     	IN BOOLEAN DEFAULT FALSE,
  PB_allow_rpt_inst     	IN BOOLEAN DEFAULT FALSE)

-- ###############################################################################
-- #  Description: CCR 1674: Process a backlog of SSN Verificatio
-- #
-- #   Usage:            Called by run_wrapper.sql
-- #   Input Parameters:
-- #     1) None
-- #   Output:
-- #     1) Terminal
-- #   Internal Script Parameters:
-- #     1) None
-- #
-- #
-- ###########################################################################
-- # Change History
-- #
-- # DATE       User Name         DESCRIPTION
-- # ---------- ----------------- -------------------------------------------------
-- # 06/10/2012 Richard Do        Created
-- #
-- #
-- ###########################################################################
AS

   CC_SSN_VERIFICATION_EXT     	  CONSTANT VARCHAR2(20):= 'SSN_VERIFICATION_EXT';
   CC_BATCH_SSN_LOG_DIR           CONSTANT VARCHAR2(20):= 'BATCH_SSN_LOG_DIR';
   CC_BATCH_SSN_TOPROCESS_DIR     CONSTANT VARCHAR2(25):= 'BATCH_SSN_TOPROCESS_DIR';
   CC_BATCH_SSN_ADRTOPROCESS_DIR  CONSTANT VARCHAR2(35):= 'BATCH_SSN_ADRTOPROCESS_DIR';
   CC_BATCH_SSN_ERR_DIR           CONSTANT VARCHAR2(20):= 'BATCH_SSN_ERR_DIR';
   CC_JOB_SSN_TERM_SOL		  VARCHAR2(23) := 'Job-SSNIterimSolution';

   CC_MAX_ROWS_COMMIT		CONSTANT NUMBER := 1000;

   V_File_handle UTL_FILE.FILE_TYPE;
   V_FILE_Line VARCHAR2(1024);

   VD_log_date      	DATE;
   VD_rec_group_date    DATE;
   VN_SSN_VER_ID	NUMBER;
   VC_log_step          VARCHAR2(500);
   VN_job_number	NUMBER := 0;
   VC_process_name      VARCHAR2(30) := 'ADR.BATCH_SSN_PROCESSED_SP';

   VC_file_name     	VARCHAR2(300);
   VC_log_file   	VARCHAR2(200);
   VC_error_file 	VARCHAR2(200);
   VC_prefix_file     	VARCHAR2(40):= 'SSA.r';
   VN_vpid_value	ADR.person.vpid_value%TYPE;

   VN_person_id 		ADR.person.person_id%TYPE;
   VN_error_count		NUMBER;
   VN_num_rows_beProcessed	NUMBER;
   VN_num_rows_processed	NUMBER;
   VD_days_deletion		NUMBER:= 14;
   VN_rec_count			NUMBER:= 0;
   VN_job_count			NUMBER:= 0;

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

   VC_allow_rpt_inst            VARCHAR2(5) := NULL;
   VC_instance_name             VARCHAR2(16);
   VC_run_standalone            VARCHAR2(5) := NULL;
   t1 				INTEGER;
   t2 				INTEGER;



   VC_ADRtoProcess_file_path   	ALL_DIRECTORIES.DIRECTORY_PATH%TYPE;
   VC_ToProcess_file_path   	ALL_DIRECTORIES.DIRECTORY_PATH%TYPE;
   VC_error_file_path       	ALL_DIRECTORIES.DIRECTORY_PATH%TYPE;
   VC_log_file_path         	ALL_DIRECTORIES.DIRECTORY_PATH%TYPE;
   VC_TMP      			NUMBER := 0;
   VN_commit_cnt		NUMBER := 0;

   VC_SQLSTMNT        		VARCHAR2(2000);

   FILE_PATH_ADRTOPROCESS_NULL_E EXCEPTION;
   FILE_PATH_TOPROCESS_NULL_E	EXCEPTION;
   LOG_FILE_PATH_NULL_E		EXCEPTION;
   ERROR_FILE_PATH_NULL_E	EXCEPTION;

   INVALID_SSN_ADRTOPROCESS_E  	EXCEPTION;
   INVALID_SSN_TOPROCESS_E  	EXCEPTION;
   INVALID_SSN_LOG_E  		EXCEPTION;
   INVALID_SSN_ERR_E  		EXCEPTION;


BEGIN

   VD_rec_group_date := TRUNC(SYSDATE);
   t1 :=  DBMS_UTILITY.get_time;


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

   VC_log_step := 'Checking input parameters.';

   IF NVL(P_batch_SSN_ADRToProcess_DIR,'***') != CC_BATCH_SSN_ADRTOPROCESS_DIR THEN

     RAISE INVALID_SSN_ADRTOPROCESS_E;

   ELSIF NVL(P_batch_SSN_ToProcess_DIR,'***') != CC_BATCH_SSN_TOPROCESS_DIR THEN

      RAISE INVALID_SSN_TOPROCESS_E;

   ELSIF NVL(P_batch_SSN_log_DIR,'***') != CC_BATCH_SSN_LOG_DIR THEN

      RAISE INVALID_SSN_LOG_E;

   ELSIF NVL(P_batch_SSN_err_DIR,'***') != CC_BATCH_SSN_ERR_DIR THEN

      RAISE INVALID_SSN_ERR_E;

   END IF;


   -- * 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;



   BEGIN

      -- ***************************************************************************
      -- *
      -- * 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.
      -- *
      -- ***************************************************************************

      IF NOT VB_allow_rpt_inst THEN

         SELECT    instance_name
         INTO      VC_instance_name
         FROM      V$instance;

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

            VD_log_date := SYSDATE;
            ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                 PD_job_log_date => VD_log_date,
                 PC_job_status => SUBSTR('WARNING     - Processing Failed',1,60),
                 PC_job_log_message => SUBSTR('Attempting to run on a reporting instance. Instance name: '
                                                    || VC_instance_name
                       || '. 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_rec_group_date,
                 PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

           RETURN;

         END IF;

      END IF;


      -- ***************************************************************************
      -- *
      -- * Check to see if the job has already run today
      -- *  - If running in stand-alone mode, don't check.
      -- *  - Otherwise, if job has already run today, then exit the job.
      -- *
      -- ***************************************************************************
      IF NOT PB_run_standalone THEN

           SELECT    COUNT(*)
            INTO      VN_rec_count
           FROM      ADRMGT.Job_Process_Logs
           WHERE     Job_name = VC_process_name
           AND       Date1 = VD_rec_group_date
           AND       Job_status = 'SUCCESS     - Processing Completed';

           IF VN_rec_count > 0 THEN

              VD_log_date := SYSDATE;
              ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                      PD_job_log_date => VD_log_date,
                      PC_job_status => SUBSTR('INFORMATION - This date has already been processed',1,60),
                      PC_job_log_message => SUBSTR('Processing has already completed for this date. Exiting to job queue.',1,2000),
                      PD_date1 => VD_rec_group_date,
                      PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

              RETURN;

           END IF;

      END IF;

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

      SELECT COUNT(*)
        INTO   VN_job_count
      FROM  SYS.DBA_JOBS_RUNNING R,
            SYS.DBA_JOBS J
      WHERE  J.JOB = R.JOB
      AND schema_user = 'ADR'
      AND upper(WHAT) LIKE '%BATCH_SSN_PROCESSED_SP%';


      IF VN_job_count > 1 THEN

    	VD_log_date := SYSDATE;
    	ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                 PD_job_log_date => VD_log_date,
                 PC_job_status => SUBSTR('WARNING     - Check for running jobs',1,60),
                 PC_job_log_message => SUBSTR('There is another SSN VERIFICATION job running.'
                                          || ' Exiting job without running.',1,2000),
                 PD_date1 => VD_rec_group_date,
                 PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

          RETURN;

      ELSIF VB_run_standalone THEN

          VD_log_date := SYSDATE;
          ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                 PD_job_log_date => VD_log_date,
                 PC_job_status => SUBSTR('INFORMATION - Starting in STANDALONE mode',1,60),
                 PD_date1 => VD_rec_group_date,
                 PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      ELSE

          SELECT    MAX(J.Job)
           INTO      VN_job_number
          FROM      SYS.DBA_JOBS_RUNNING R,
                    SYS.DBA_JOBS J
          WHERE     J.JOB = R.JOB
          AND       UPPER(J.WHAT) LIKE '%ADR_ADDRESS_Z05_UPDATE_SP%';

          VD_log_date := SYSDATE;
          ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                   PD_job_log_date => VD_log_date,
                   PC_job_status => SUBSTR('INFORMATION - Starting DBMS_JOB mode',1,60),
                   PD_date1 => VD_rec_group_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(VC_process_name,1,60),
                   PD_job_log_date => VD_log_date,
                   PC_job_status => SUBSTR('ERROR       - Unable to retrieve instance name',1,60),
                   PC_job_log_message => SUBSTR('Terminating script.',1,2000),
                   PD_date1 => VD_rec_group_date,
                   PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

         RETURN;

   END;


   VC_log_step := 'Retrieving dir paths for '||P_batch_SSN_ADRToProcess_DIR;

   -- ==================================================================
   --
   -- MAIN PROGRAM
   --
   -- Retrieving directory paths of external files, log/error/processed
   --
   -- =================================================================

   SELECT d.directory_path ,
          l.directory_path,
          err.directory_path,
          p.directory_path
   INTO
      VC_ADRToProcess_file_path,
      VC_log_file_path,
      VC_error_file_path,
      VC_ToProcess_file_path
   FROM all_directories d,
       (  SELECT directory_path
          FROM  all_directories
          WHERE directory_name = P_batch_SSN_log_DIR) l,
       (  SELECT directory_path
          FROM  all_directories
          WHERE directory_name = P_batch_SSN_err_DIR) err,
       (  SELECT directory_path
       	  FROM  all_directories
          WHERE directory_name = P_batch_SSN_ToProcess_DIR) p
   WHERE d.directory_name = P_batch_SSN_ADRToProcess_DIR;


   ADRMGT.Job_Process_Log_Sp(
         PC_job_name => SUBSTR(VC_process_name,1,60),
         PD_job_log_date => SYSDATE,
         PC_job_status => SUBSTR('INFORMATION - Process Initiation',1,60),
         PC_job_log_message => SUBSTR('INFORMATION - ADRToProcess DIR= '||VC_ADRToProcess_file_path||' ** '||
         			      'Log DIR= '||P_batch_SSN_log_DIR||' ** '||
         			      'Error DIR= '||P_batch_SSN_err_DIR||' ** '||
         			      'ToProcess DIR= '||VC_ToProcess_file_path||' ** '||
         			      VC_log_step,1,2000),
         PD_date1 => SYSDATE,
         PC_text1 => SUBSTR(VC_log_step,1,255));


   IF VC_ADRtoProcess_file_path IS NULL THEN

     VC_log_step := 'Directory path of ADRToProcess IS NOT setup.';

     RAISE  FILE_PATH_ADRTOPROCESS_NULL_E;

   ELSIF VC_ToProcess_file_path IS NULL THEN

     VC_log_step := 'Directory path of ToProcessed IS NOT setup.';
     RAISE  FILE_PATH_TOPROCESS_NULL_E;

   ELSIF VC_log_file_path IS NULL THEN

     VC_log_step := 'Directory path of Log IS NOT setup.';
     RAISE  LOG_FILE_PATH_NULL_E;

   ELSIF VC_error_file_path IS NULL THEN

     VC_log_step := 'Directory path of ERROR IS NOT setup.';
     RAISE  ERROR_FILE_PATH_NULL_E;

   END IF;

   VC_TMP := 0;


 -- ==========================================================
 --
 -- This will loop number of times to process each source
 -- data file.  As required, there will be 2 files processed
 --
 -- ==========================================================

 FOR I IN 1..P_num_files_process LOOP


   SELECT VC_prefix_file||to_char(sysdate,'YY')||TO_CHAR(SYSDATE,'DDD')||I||'.txt'
      INTO VC_file_name
   FROM dual;

   --DBMS_OUTPUT.PUT_LINE('File name: '||VC_ADRToProcess_file_path||'/'||VC_file_name);
   VC_log_step := 'Processing File '||VC_file_name;

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

   -- ============================================
   --
   -- Opening source data file for reading and
   -- loading into SSN_VERIFICATION and details
   --
   -- ============================================
   BEGIN

     VC_log_step := 'Reading file '|| VC_file_name;
     V_File_handle := UTL_FILE.FOPEN(P_batch_SSN_ADRToProcess_DIR, VC_file_name, 'R');

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


     -- =========================================================
     --
     -- Checking external table existed or nor prior creating one
     --
     -- ==========================================================
     SELECT COUNT(1) INTO
       VC_TMP
     FROM  all_external_tables
     WHERE table_name = CC_SSN_VERIFICATION_EXT;

     IF VC_TMP = 0   THEN
         VC_log_step := 'Building SQLSTMNT create Ext Tab.';

         VC_SQLSTMNT :=
           'CREATE TABLE ADR.'||CC_SSN_VERIFICATION_EXT||CHR(10)||
                '(SSN         VARCHAR2(9), '        ||CHR(10)||
                'ENTRY_CODE       VARCHAR2(3),'     ||CHR(10)||
                'PROCESSING_CODE  VARCHAR2(3),'     ||CHR(10)||
                'LAST_NAME        VARCHAR2(13),'    ||CHR(10)||
                'FIRST_NAME       VARCHAR2(10),'    ||CHR(10)||
                'MIDDLE_NAME      VARCHAR2(7),'     ||CHR(10)||
                'DOB              VARCHAR2(8),'     ||CHR(10)||
                'SEX              VARCHAR2(1),'     ||CHR(10)||
                'INCOME_YEAR      VARCHAR2(4),'     ||CHR(10)||
                'HEC_VA_ID        VARCHAR2(15),'    ||CHR(10)||
                'SPOUSE_CODE      VARCHAR2(1),'     ||CHR(10)||
                'BLANK_1          VARCHAR2(29),'    ||CHR(10)||
                'BLANK_2          VARCHAR2(20),'    ||CHR(10)||
                'REQUEST_CODE     VARCHAR2(4),'     ||CHR(10)||
                'BLANK_3          VARCHAR2(3))'     ||CHR(10)||
                'ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER'    	||CHR(10)||
                'DEFAULT DIRECTORY '|| CC_BATCH_SSN_ADRTOPROCESS_DIR ||' ' ||CHR(10)||
                'ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE '	||CHR(10)||
                'BADFILE '||CC_BATCH_SSN_ERR_DIR||':''' ||VC_prefix_file||'_%a_%p.err' ||''' '        ||CHR(10)||
                'LOGFILE '||CC_BATCH_SSN_LOG_DIR||':''' ||VC_prefix_file||'_%a_%p.log' ||''' '        ||CHR(10)||
                'FIELDS ('                			||CHR(10)||
                'SSN          POSITION(1:9) CHAR(9),'    	||CHR(10)||
                'ENTRY_CODE   POSITION(10:12) CHAR(3),'    	||CHR(10)||
                'PROCESSING_CODE  POSITION(13:15) CHAR(3),'	||CHR(10)||
                'LAST_NAME    POSITION(16:28) CHAR(13),'	||CHR(10)||
                'FIRST_NAME   POSITION(29:38) CHAR(10),'	||CHR(10)||
                'MIDDLE_NAME  POSITION(39:45) CHAR(7),' 	||CHR(10)||
                'DOB          POSITION(46:53) CHAR(8),'    	||CHR(10)||
                'SEX          POSITION(54:54) CHAR(1),'    	||CHR(10)||
                'INCOME_YEAR  POSITION(55:58) CHAR(4),'    	||CHR(10)||
                'HEC_VA_ID    POSITION(59:73) CHAR(15),'	||CHR(10)||
                'SPOUSE_CODE  POSITION(74:74) CHAR(1),'    	||CHR(10)||
                'BLANK_1      POSITION(75:103) CHAR(29),'	||CHR(10)||
                'BLANK_2      POSITION(104:123) CHAR(20),'	||CHR(10)||
                'REQUEST_CODE POSITION(124:127) CHAR(4),'	||CHR(10)||
                'BLANK_3      POSITION(128:130) CHAR(3)'	||CHR(10)||
                ') ) LOCATION ('''||VC_file_name||''')) PARALLEL 4 REJECT LIMIT UNLIMITED';


         VC_log_step := 'Created Ext table '||VC_file_name;

         EXECUTE IMMEDIATE VC_SQLSTMNT;

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

         VC_SQLSTMNT := 'GRANT SELECT ON ADR.'|| CC_SSN_VERIFICATION_EXT||' TO ADR_R';
         EXECUTE IMMEDIATE VC_SQLSTMNT;

     ELSE

         VC_log_step := 'Modified Ext tab on datafile '||VC_file_name;

         VC_SQLSTMNT := 'ALTER TABLE ADR.'||CC_SSN_VERIFICATION_EXT||' LOCATION ('''||VC_file_name||''')';


         EXECUTE IMMEDIATE VC_SQLSTMNT;

         ADRMGT.Job_Process_Log_Sp(
         	PC_job_name => SUBSTR(VC_process_name,1,60),
                PD_job_log_date => SYSDATE,
               	PC_job_status => SUBSTR('Ext table '||CC_SSN_VERIFICATION_EXT||' EXISTED.',1,60),
               	PC_job_log_message => SUBSTR('INFORMATION - External table points to file '||VC_file_name|| ' - '||VC_log_step,1,2000),
                PD_date1 => VD_rec_group_date,
                PC_text1 => SUBSTR(VC_log_step,1,255));

     END IF;

     -- =================================
     --
     -- Insert rows to SSN_VERIFICATION
     --
     -- =================================
     BEGIN  -- Insert rows to SSN_VERIFICATION

        VC_log_step := 'Get nex sequence number.';

        SELECT TRANSACTION_S.NEXTVAL INTO VN_SSN_VER_ID
        FROM DUAL;

        VC_log_step := 'Add a row into ADR.SSN_VERIFICATION';

        INSERT /*+ APPEND */INTO ADR.SSN_VERIFICATION
         ( SSN_VERIFICATION_ID,
         	SSA_SENT_DATE      ,
         	RECORD_CREATED_BY  ,
         	RECORD_CREATED_DATE ,
       	        RECORD_MODIFIED_BY  ,
       		RECORD_MODIFIED_DATE ,
       		RECORD_MODIFIED_COUNT
      	 )
    	VALUES(VN_SSN_VER_ID, SYSDATE, CC_JOB_SSN_TERM_SOL, SYSDATE, CC_JOB_SSN_TERM_SOL, SYSDATE,0);


        -- ===========================================
        --
        -- Insert rows to SSN_VERIFICATION_DETAIL
        --
        -- ===========================================
        BEGIN  -- looping external table

          VN_num_rows_processed := 0;
          VC_log_step := 'Looping external table '||CC_SSN_VERIFICATION_EXT;

          FOR I IN (SELECT blank_1, hec_va_id, ssn FROM ADR.SSN_VERIFICATION_EXT) LOOP

             VN_vpid_value := I.blank_1;

             BEGIN

               	VC_log_step := 'Obtaining person id using vpid_value='||VN_vpid_value;

              	SELECT p.person_id
                   INTO VN_person_id
           	FROM   person p
           	WHERE p.vpid_value = VN_vpid_value;

                VC_log_step := 'Adding row to SSN_VERIFICATION_DETAIL, person_id='||VN_person_id||', and vpid_value='||VN_vpid_value;


                INSERT /*+ APPEND */ INTO ADR.SSN_VERIFICATION_DETAIL
    		(
      			SSN_VERIFICATION_DETAIL_ID ,
      			SSN_VERIFICATION_ID        ,
      			PERSON_ID                  ,
      			SSN_ID                     ,
      			SSN                         ,
      			VERIFICATION_REQUEST_NUMBER ,
      			RECORD_CREATED_BY           ,
      			RECORD_CREATED_DATE         ,
      			RECORD_MODIFIED_BY          ,
      			RECORD_MODIFIED_DATE        ,
      			RECORD_MODIFIED_COUNT
    		)
                VALUES(	TRANSACTION_S.NEXTVAL,
                	VN_SSN_VER_ID,
                	VN_person_id,
                	NULL,
                	I.SSN,
     			I.HEC_VA_ID,
     			CC_JOB_SSN_TERM_SOL,
     			SYSDATE,
     			CC_JOB_SSN_TERM_SOL,
     			SYSDATE,
     			0
     			);

                IF VN_commit_cnt >= CC_MAX_ROWS_COMMIT THEN
                   VN_commit_cnt := 0;

                   COMMIT;
                ELSE
                   VN_commit_cnt := VN_commit_cnt + 1;

                END IF;

                VN_num_rows_processed := VN_num_rows_processed + 1;

                EXCEPTION
                   WHEN NO_DATA_FOUND THEN

                      ADRMGT.Job_Process_Err_Sp(
		       	   PC_job_name => SUBSTR(VC_process_name,1,60),
		    	   PD_job_error_date  => SYSDATE,
		    	   PC_job_error_code => SUBSTR(SQLCODE,1,60),
		    	   PC_job_error_message => SUBSTR('ERROR: No DATA FOUND in PERSON table of File '||VC_file_name||
		    		'. VPID_VALUE= '||VN_vpid_value||
		    		'==> No row added to SSN_VERIFICATION_DETAIL. ERRMSG: ' || SQLERRM,1,2000),
		    	   PD_date1 => SYSDATE,
                           PC_text1 => SUBSTR(VC_log_step||' VPID_VALUE='||VN_vpid_value,1,255));



                   WHEN OTHERS THEN
                	ADRMGT.Job_Process_Err_Sp(
          	   		PC_job_name => SUBSTR(VC_process_name,1,60),
			        PD_job_error_date  => SYSDATE,
			        PC_job_error_code => SUBSTR(SQLCODE,1,60),
			        PC_job_error_message => SUBSTR('ERROR: Unknown Exception of File '||VC_file_name||
			        	'. ' || SQLERRM||' - VPID_VALUE: '||VN_vpid_value,1,2000),
			        PD_date1 => SYSDATE,
                                PC_text1 => SUBSTR(VC_log_step||' VPID_VALUE='||VN_vpid_value,1,255));

             END;   -- SSN_VERIFICATION_DETAIL

          END LOOP;  -- SSN_VERIFICATION_EXT

          -- ==========================================================
	  --
          --  Update rows processed of SSN_VERIFICATION.  Otherwise
          --  delete SSN_VERIFICATION when no records matched to
          --  detail table (SSN_VERIFICATION_DETAIL)
          --
          -- ==========================================================

          VN_num_rows_beProcessed := 0;

          SELECT COUNT(1)
             INTO VN_num_rows_beProcessed
          FROM ADR.SSN_VERIFICATION_EXT;


          IF VN_num_rows_processed > 0 THEN

              VC_log_step := 'Updating processed rows of SSN_VERIFICATION.';

              MERGE INTO ADR.SSN_VERIFICATION
	           USING DUAL
	             ON (SSN_VERIFICATION_ID = VN_SSN_VER_ID)
	    	   WHEN MATCHED THEN
	              UPDATE SET SSN_SENT_COUNT  = VN_num_rows_processed;

          ELSE

              VC_log_step := 'Removing header record in SSN_VERIFICATION';

              MERGE INTO ADR.SSN_VERIFICATION
	      	   USING DUAL
	      	   ON (SSN_VERIFICATION_ID = VN_SSN_VER_ID)
	      	   WHEN MATCHED THEN
	      	      UPDATE SET SSN_SENT_COUNT  = VN_num_rows_processed
	              DELETE WHERE SSN_VERIFICATION_ID = VN_SSN_VER_ID;

          END IF;
          COMMIT;

          -- ==========================================================
          --
          -- Moving file name to Processed dir from toProcess folder
          --
          -- ==========================================================


          VC_log_step := 'Moving file name '|| VC_file_name||' to '||VC_ToProcess_file_path;
          --dbms_output.put_line(VC_log_step);

	  VN_error_count:= VN_num_rows_beProcessed-VN_num_rows_processed;

          UTL_FILE.FRENAME(P_batch_SSN_ADRToProcess_DIR, VC_file_name,P_batch_SSN_ToProcess_DIR,VC_file_name,TRUE);


          ADRMGT.Job_Process_Log_Sp(
	  	PC_job_name => SUBSTR(VC_process_name,1,60),
	  	PD_job_log_date => SYSDATE,
	  	PC_job_status => SUBSTR('INFORMATION - Process Completed.',1,60),
	  	PC_job_log_message => SUBSTR('**** SUMMARY INFO CHANGES TO SSN_VERIFICATION_DETAIL ****'||CHR(10)||
	  			'  Total Rows Processed: '|| VN_num_rows_beProcessed||CHR(10)||
	  			'  Num Rows Added: '|| VN_num_rows_processed||CHR(10)||
	  	  	  	'  Num Rows Rejected: '||VN_error_count||CHR(10)||
	  	  	  	'  File Name '||VC_file_name||' is now moved to '||VC_ToProcess_file_path||CHR(10)||
	  	  	  	'*************************** END TRANSACTION *****************************',1,2000),
	  	PD_date1 => SYSDATE,
                PC_text1 => SUBSTR(VC_log_step||' VPID_VALUE='||VN_vpid_value,1,255));


          EXCEPTION
              WHEN OTHERS THEN
		ADRMGT.Job_Process_Err_Sp(
		  	PC_job_name => SUBSTR(VC_process_name,1,60),
		        PD_job_error_date  => SYSDATE,
		        PC_job_error_code => SUBSTR(SQLCODE,1,60),
		        PC_job_error_message => SUBSTR('ERROR: Unknown Exception of File '||VC_file_name||
			        	'. ' || SQLERRM||' - VPID_VALUE: '||VN_vpid_value,1,2000),
		        PD_date1 => SYSDATE,
                        PC_text1 => SUBSTR(VC_log_step||' VPID_VALUE='||VN_vpid_value,1,255));

        END; -- looping external table


        EXCEPTION

           WHEN UTL_FILE.INVALID_PATH THEN

	     ADRMGT.Job_Process_Err_Sp(
	      	PC_job_name => SUBSTR(VC_process_name,1,60),
	        PD_job_error_date  => SYSDATE,
	        PC_job_error_code => SUBSTR(SQLCODE,1,60),
	        PC_job_error_message => SUBSTR('ERROR: Invalid PATH - '||VC_ADRtoProcess_file_path||
	                   		' ERRMSG: '||   SQLERRM,1,2000),
	        PD_date1 => SYSDATE,
                PC_text1 => SUBSTR(VC_log_step,1,255));

      	   WHEN UTL_FILE.INVALID_FILENAME THEN

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

          WHEN NO_DATA_FOUND THEN

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

          WHEN OTHERS THEN

            ADRMGT.Job_Process_Err_Sp(
            	PC_job_name => SUBSTR(VC_process_name,1,60),
                PD_job_error_date  => SYSDATE,
                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                PC_job_error_message => SUBSTR('ERROR: Unknown Exception of File '||VC_file_name||
			        	'. ' || SQLERRM,1,2000),
                PD_date1 => VD_rec_group_date,
                PC_text1 => SUBSTR(VC_log_step,1,255));

       END;   -- SSN_VERIFICATION

    EXCEPTION

      WHEN UTL_FILE.INVALID_PATH THEN
      	ADRMGT.Job_Process_Err_Sp(
	   	PC_job_name => SUBSTR(VC_process_name,1,60),
	        PD_job_error_date  => SYSDATE,
	        PC_job_error_code => SUBSTR(SQLCODE,1,60),
	        PC_job_error_message => SUBSTR('ERROR: Invalid PATH - '||VC_ADRtoProcess_file_path||
	                		' ERRMSG: '||   SQLERRM,1,2000),
	        PD_date1 => SYSDATE,
                PC_text1 => SUBSTR(VC_log_step,1,255));


      WHEN UTL_FILE.INVALID_FILENAME THEN


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


      WHEN UTL_FILE.INVALID_OPERATION THEN


  	ADRMGT.Job_Process_Log_Sp(
	        PC_job_name => SUBSTR(VC_process_name,1,60),
	        PD_job_log_date => SYSDATE,
	        PC_job_status => SUBSTR(VC_log_step,1,60),
	        PC_job_log_message => SUBSTR('INFORMATION - File Name Not find/Invalid operation of File '|| VC_file_name,1,2000),
	        PD_date1 => SYSDATE,
                PC_text1 => SUBSTR(VC_log_step,1,255));


      WHEN UTL_FILE.WRITE_ERROR THEN
       	ADRMGT.Job_Process_Err_Sp(
      		PC_job_name => SUBSTR(VC_process_name,1,60),
                PD_job_error_date  => SYSDATE,
                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                PC_job_error_message => SUBSTR('ERROR: Invalid Write Error. ERRMSG: ' || SQLERRM,1,2000),
                PD_date1 => SYSDATE,
                PC_text1 => SUBSTR(VC_log_step,1,255));

      WHEN NO_DATA_FOUND THEN
        ADRMGT.Job_Process_Log_Sp(
      		PC_job_name => SUBSTR(VC_process_name,1,60),
                PD_job_log_date => SYSDATE,
                PC_job_status => SUBSTR(VC_log_step,1,60),
                PC_job_log_message => SUBSTR('INFORMATION - No DATA FOUND of File '||VC_file_name,1,2000),
                PD_date1 => SYSDATE,
                PC_text1 => SUBSTR(VC_log_step,1,255));

      WHEN OTHERS THEN

     	ADRMGT.Job_Process_Err_Sp(
     		PC_job_name => SUBSTR(VC_process_name,1,60),
                PD_job_error_date  => SYSDATE,
                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                PC_job_error_message => SUBSTR('ERROR: Unknown Exception of File '||VC_file_name||
			        	'. ' || SQLERRM||' - VPID_VALUE: '||VN_vpid_value,1,2000),
                PD_date1 => SYSDATE,
                PC_text1 => SUBSTR(VC_log_step,1,255));
      END;

   END LOOP;


   -- ===========================================
   --
   -- Clean up old log records
   --
   -- ===========================================
   DELETE
   FROM      ADRMGT.Job_Process_Logs
   WHERE     Job_name = VC_process_name
   AND       TRUNC(DATE1) < TRUNC(SYSDATE) - VD_days_deletion;

   COMMIT;



   VD_log_date := SYSDATE;
   t2 :=  DBMS_UTILITY.get_time;

   ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
             PD_job_log_date => VD_log_date,
             PC_job_status => SUBSTR('SUCCESS     - Processing Completed',1,60),
             PC_job_log_message => SUBSTR('Processing Time (sec): ' || TO_CHAR((t2 - t1)/100),1,2000),
             PD_date1 => VD_rec_group_date,
             PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));


   EXCEPTION

     WHEN INVALID_SSN_ADRTOPROCESS_E THEN
         ADRMGT.Job_Process_Err_Sp(
	        PC_job_name => SUBSTR(VC_process_name,1,60),
	        PD_job_error_date  => SYSDATE,
	        PC_job_error_code => SUBSTR(SQLCODE,1,60),
	        PC_job_error_message => SUBSTR('ERROR - Invalid input parameter ADRToProcess: '||P_batch_SSN_ADRToProcess_DIR,1,2000),
	        PD_date1 => SYSDATE,
                PC_text1 => SUBSTR(VC_log_step,1,255));

     WHEN INVALID_SSN_TOPROCESS_E  	THEN
         ADRMGT.Job_Process_Err_Sp(
     	        PC_job_name => SUBSTR(VC_process_name,1,60),
     	        PD_job_error_date  => SYSDATE,
     	        PC_job_error_code => SUBSTR(SQLCODE,1,60),
     	        PC_job_error_message => SUBSTR('ERROR - Invalid input parameter ToProcess: '||P_batch_SSN_ToProcess_DIR,1,2000),
     	        PD_date1 => SYSDATE,
                PC_text1 => SUBSTR(VC_log_step,1,255));


     WHEN INVALID_SSN_LOG_E  THEN

         ADRMGT.Job_Process_Err_Sp(
	        PC_job_name => SUBSTR(VC_process_name,1,60),
	        PD_job_error_date  => SYSDATE,
	        PC_job_error_code => SUBSTR(SQLCODE,1,60),
	        PC_job_error_message => SUBSTR('ERROR - Invalid input parameter Log: '||P_batch_SSN_log_DIR,1,2000),
	        PD_date1 => SYSDATE,
	        PC_text1 => SUBSTR(VC_log_step,1,255));

     WHEN INVALID_SSN_ERR_E THEN
         ADRMGT.Job_Process_Err_Sp(
   	        PC_job_name => SUBSTR(VC_process_name,1,60),
   	        PD_job_error_date  => SYSDATE,
   	        PC_job_error_code => SUBSTR(SQLCODE,1,60),
   	        PC_job_error_message => SUBSTR('ERROR - Invalid input parameter Error: '||P_batch_SSN_err_DIR,1,2000),
   	        PD_date1 => SYSDATE,
                PC_text1 => SUBSTR(VC_log_step,1,255));

     WHEN NO_DATA_FOUND THEN

         ADRMGT.Job_Process_Err_Sp(
               	PC_job_name => SUBSTR(VC_process_name,1,60),
                PD_job_error_date  => SYSDATE,
                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                PC_job_error_message => SUBSTR('ERROR - No DATA FOUND in ADR ToProcess of File '||P_batch_SSN_ADRToProcess_DIR,1,2000),
                PD_date1 => SYSDATE,
                PC_text1 => SUBSTR(VC_log_step,1,255));

     WHEN FILE_PATH_TOPROCESS_NULL_E THEN
         ADRMGT.Job_Process_Err_Sp(
           	PC_job_name => SUBSTR(VC_process_name,1,60),
                PD_job_error_date  => SYSDATE,
                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                PC_job_error_message => SUBSTR('ERROR: '||P_batch_SSN_ToProcess_DIR||' needs to be configured. ' || SQLERRM,1,2000),
                PD_date1 => SYSDATE,
                PC_text1 => SUBSTR(VC_log_step,1,255));


     WHEN FILE_PATH_ADRTOPROCESS_NULL_E THEN
         ADRMGT.Job_Process_Err_Sp(
           	PC_job_name => SUBSTR(VC_process_name,1,60),
                PD_job_error_date  => SYSDATE,
                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                PC_job_error_message => SUBSTR('ERROR: '||P_batch_SSN_ADRToProcess_DIR||' needs to be configured. ' || SQLERRM,1,2000),
                PD_date1 => SYSDATE,
                PC_text1 => SUBSTR(VC_log_step,1,255));

     WHEN LOG_FILE_PATH_NULL_E THEN

         ADRMGT.Job_Process_Err_Sp(
           	PC_job_name => SUBSTR(VC_process_name,1,60),
                PD_job_error_date  => SYSDATE,
                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                PC_job_error_message => SUBSTR('ERROR: '||P_batch_SSN_log_DIR||' needs to be configured. ' || SQLERRM,1,2000),
                PD_date1 => SYSDATE,
                PC_text1 => SUBSTR(VC_log_step,1,255));

     WHEN ERROR_FILE_PATH_NULL_E THEN

       	 ADRMGT.Job_Process_Err_Sp(
   	        PC_job_name => SUBSTR(VC_process_name,1,60),
   	        PD_job_error_date  => SYSDATE,
   	        PC_job_error_code => SUBSTR(SQLCODE,1,60),
   	        PC_job_error_message => SUBSTR('ERROR: '||P_batch_SSN_err_DIR||' needs to be configured. ' || SQLERRM,1,2000),
   	        PD_date1 => SYSDATE,
                PC_text1 => SUBSTR(VC_log_step,1,255));

     WHEN OTHERS THEN

      	 ADRMGT.Job_Process_Err_Sp(
       		PC_job_name => SUBSTR(VC_process_name,1,60),
                PD_job_error_date  => SYSDATE,
                PC_job_error_code => SUBSTR(SQLCODE,1,60),
                PC_job_error_message => SUBSTR('ERROR: Unknown Exception of File '||VC_file_name||
   		        	'. ' || SQLERRM,1,2000),
                PD_date1 => SYSDATE,
                PC_text1 => SUBSTR(VC_log_step,1,255));

 END;
/
DROP PROCEDURE ADR.CLEAN_SSN_FRM_ADDR;

CREATE OR REPLACE PROCEDURE ADR.CLEAN_SSN_FRM_ADDR IS
   v_rec_cnt   INTEGER;
BEGIN
   ADRMGT.Job_Process_Log_Sp (PC_job_name          => SUBSTR ('CLEAN_SSN_FRM_ADDR', 1, 60),
                              PD_job_log_date      => SYSDATE,
                              PC_job_status        => 'STG-BEGIN',
                              PC_JOB_LOG_MESSAGE   => SUBSTR (' Starting to process', 1, 60));

   INSERT INTO ADR.STG_ADDRESS_SSN_CLEANUP (ADDRESS_ID,
                                            ADDRESS_LINE1,
                                            ADDRESS_LINE2,
                                            ADDRESS_LINE3,
                                            CITY,
                                            SSN)
      SELECT address_id,
             address_line1,
             address_line2,
             address_line3,
             city,
             rpt_psim_traits.ssn
        FROM address, person, psim.rpt_psim_traits
       WHERE     address.person_id = person.person_id
             AND person.vpid_value = rpt_psim_traits.vpid_value
             AND (   (INSTR (
                         REGEXP_REPLACE (address_line1,
                                         '([[:alpha:]]|[[:space:]]|[[:punct:]])*',
                                         ''),
                         rpt_psim_traits.ssn) > 0)
                  OR (INSTR (
                         REGEXP_REPLACE (address_line2,
                                         '([[:alpha:]]|[[:space:]]|[[:punct:]])*',
                                         ''),
                         rpt_psim_traits.ssn) > 0)
                  OR (INSTR (
                         REGEXP_REPLACE (address_line3,
                                         '([[:alpha:]]|[[:space:]]|[[:punct:]])*',
                                         ''),
                         rpt_psim_traits.ssn) > 0)
                  OR (INSTR (
                         REGEXP_REPLACE (city, '([[:alpha:]]|[[:space:]]|[[:punct:]])*', ''),
                         rpt_psim_traits.ssn) > 0));

   v_rec_cnt := SQL%ROWCOUNT;
   COMMIT;

   ADRMGT.Job_Process_Log_Sp (
      PC_job_name          => SUBSTR ('CLEAN_SSN_FRM_ADDR', 1, 60),
      PD_job_log_date      => SYSDATE,
      PC_job_status        => 'STG-COMPLETE',
      PC_JOB_LOG_MESSAGE   => SUBSTR (
                                   'STAGE record_count : '
                                || v_rec_cnt
                                || ' Completed collecting ADDRESS_IDs',
                                1,
                                60));

   UPDATE address tgt
      SET (address_line1,
           address_line2,
           address_line3,
           city) =
             (SELECT CASE
                        WHEN (INSTR (
                                 REGEXP_REPLACE (address_line1,
                                                 '([[:alpha:]]|[[:space:]]|[[:punct:]])*',
                                                 ''),
                                 ssn) > 0) THEN
                           NULL
                        ELSE
                           address_line1
                     END
                        address_line1_rep,
                     CASE
                        WHEN (INSTR (
                                 REGEXP_REPLACE (address_line2,
                                                 '([[:alpha:]]|[[:space:]]|[[:punct:]])*',
                                                 ''),
                                 ssn) > 0) THEN
                           NULL
                        ELSE
                           address_line2
                     END
                        address_line2_rep,
                     CASE
                        WHEN (INSTR (
                                 REGEXP_REPLACE (address_line3,
                                                 '([[:alpha:]]|[[:space:]]|[[:punct:]])*',
                                                 ''),
                                 ssn) > 0) THEN
                           NULL
                        ELSE
                           address_line3
                     END
                        address_line3_rep,
                     CASE
                        WHEN (INSTR (
                                 REGEXP_REPLACE (city,
                                                 '([[:alpha:]]|[[:space:]]|[[:punct:]])*',
                                                 ''),
                                 ssn) > 0) THEN
                           NULL
                        ELSE
                           city
                     END
                        city_rep
                FROM STG_ADDRESS_SSN_CLEANUP src
               WHERE src.address_id = tgt.address_id),
          record_modified_by = '581393',
          record_modified_date = SYSDATE,
          record_modified_count = record_modified_count + 1
    WHERE EXISTS
             (SELECT 1
                FROM STG_ADDRESS_SSN_CLEANUP src
               WHERE src.address_id = tgt.address_id);

   v_rec_cnt := SQL%ROWCOUNT;
   COMMIT;

   ADRMGT.Job_Process_Log_Sp (
      PC_job_name          => SUBSTR ('CLEAN_SSN_FRM_ADDR', 1, 60),
      PD_job_log_date      => SYSDATE,
      PC_job_status        => 'COMPLETE',
      PC_JOB_LOG_MESSAGE   => SUBSTR (
                                   'Updated record_count : '
                                || v_rec_cnt
                                || ' Starting to process',
                                1,
                                60));
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      ADRMGT.Job_Process_Log_Sp (
         PC_job_name          => SUBSTR ('CLEAN_SSN_FRM_ADDR', 1, 60),
         PD_job_log_date      => SYSDATE,
         PC_job_status        => 'WARN',
         PC_JOB_LOG_MESSAGE   => SUBSTR ('Nothing to remove; no record FOUND', 1, 60));
   WHEN OTHERS THEN
      ADRMGT.Job_Process_Log_Sp (
         PC_job_name          => SUBSTR ('CLEAN_SSN_FRM_ADDR', 1, 60),
         PD_job_log_date      => SYSDATE,
         PC_job_status        => 'FAILED',
         PC_JOB_LOG_MESSAGE   => SUBSTR (
                                      DBMS_UTILITY.FORMAT_CALL_STACK
                                   || DBMS_UTILITY.format_error_stack
                                   || DBMS_UTILITY.format_error_backtrace,
                                   1,
                                   60));
END CLEAN_SSN_FRM_ADDR;
/


CREATE OR REPLACE PUBLIC SYNONYM CLEAN_SSN_FRM_ADDR FOR ADR.CLEAN_SSN_FRM_ADDR;
DROP PROCEDURE ADR.HANDBOOK_SP;

CREATE OR REPLACE PROCEDURE ADR.HANDBOOK_SP
IS

  -- VARIABLES

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

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

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

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


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

  VC_SQLSTMT         VARCHAR2(5000);

  vMesg              LONG;
  vSubj              VARCHAR2(800);


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

  VC_allow_rpt_inst         VARCHAR2(5) := NULL;

  VC_process_duration       VARCHAR2(25);
  VC_process_name            VARCHAR2(30) := 'ADR.HANDBOOK_SP';


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


  CURSOR c_hbr IS SELECT batch_release_size, batch_release_date
    FROM handbook_batch_request
    WHERE (record_created_date >= TRUNC(sysdate) - 7
    AND record_created_date < sysdate)
    AND std_handbookbatchrequeststa_id = 2000010
    ORDER BY record_created_date;

-- **************************************************************************
-- *
-- *  MAIN PROCEDURE:
-- *
-- *
-- **************************************************************************

BEGIN

   VC_log_step := 'Get instance name';

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

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

   VN_max_rec_display := NVL(VN_max_rec_display,30);  -- set max default to 30 rows
    LC_str := 'List of batch requests for the week: ';
    FOR this_sql in  c_hbr  LOOP
      LC_str :=  LC_str ||CHR(10)||RPAD(this_sql.batch_release_size,20)||' '||this_sql.batch_release_date;
    End Loop;

      SELECT EMAIL_ADDRESS_LIST
    	INTO  VC_email_list
      FROM ADRMGT.ADR_USER_CONTACT
      WHERE CONTACT_EMAIL_USER_NAME = 'ESR_GRP';
      SELECT NVL(SUM(batch_release_size), 0) weekly_batch_release_total
      INTO VN_vbr_tot
      FROM handbook_batch_request
      WHERE (record_created_date >= TRUNC(sysdate) - 7
      AND record_created_date < sysdate)
      AND std_handbookbatchrequeststa_id = 2000010;
      LC_str := LC_str||CHR(10)||'Total number of records selected for the week from the batch request:  '||VN_vbr_tot||CHR(10);

      SELECT COUNT(*) requests_to_vendor_total_count
      INTO VN_ven_tot
      FROM handbook_mail_queue hbq, handbook_mail_status_h hms
      WHERE hbq.handbook_mail_queue_id = hms.handbook_mail_queue_id
      AND hms.std_handbookmailstattype_id = 2000005
      AND (hbq.record_created_date >= TRUNC(sysdate) - 7
      AND hbq.record_created_date < sysdate);
      LC_str := LC_str||CHR(10)||'Total Number of requests sent to vendor:  '|| VN_ven_tot||CHR(10);

      SELECT COUNT(*) batch_requests_to_vendor_count
      into VN_brv_tot
      FROM handbook_mail_queue hbq, handbook_mail_status_h hms
      WHERE hbq.handbook_mail_queue_id = hms.handbook_mail_queue_id
      AND hms.std_handbookmailstattype_id = 2000005
      AND hbq.handbook_batch_request_id IN (SELECT handbook_batch_request_id
                                           FROM handbook_batch_request
                                          WHERE (record_created_date >= TRUNC(sysdate) - 7
                                            AND record_created_date < sysdate)
                                            AND std_handbookbatchrequeststa_id = 2000010);
       LC_str := LC_str||CHR(10)||'Total Number of requests sent to vendor from the batch requests for the week:  '|| VN_brv_tot||CHR(10);
       SELECT COUNT(*) handbooks_received_count
       INTO VN_hbr_tot
       FROM vbr.document_receipt d, vbr.document dr
       WHERE d.document_receipt_id = dr.document_receipt_id
       AND receipt_string IN (SELECT handbook_mail_queue_id
                            FROM handbook_mail_queue hmq, handbook_batch_request hbr
                           WHERE hmq.handbook_batch_request_id = hbr.handbook_batch_request_id
                             AND hbr.std_handbookbatchrequeststa_id = 2000010
                             AND (hbr.record_created_date >= TRUNC(sysdate) - 7
                             AND hbr.record_created_date < sysdate))
       AND dr.document_type_id = 3;
       LC_str := LC_str||CHR(10)||'Number of handbooks received:  '|| VN_hbr_tot||CHR(10);
       SELECT COUNT(*) profiles_received_count
       INTO VN_pr_tot
       FROM vbr.document_receipt d, vbr.document dr
       WHERE d.document_receipt_id = dr.document_receipt_id
       AND receipt_string IN (SELECT handbook_mail_queue_id
                            FROM handbook_mail_queue hmq, handbook_batch_request hbr
                           WHERE hmq.handbook_batch_request_id = hbr.handbook_batch_request_id
                             AND hbr.std_handbookbatchrequeststa_id = 2000010
                             AND (hbr.record_created_date >= TRUNC(sysdate) - 7
                             AND hbr.record_created_date < sysdate))
        AND dr.document_type_id = 4;
       LC_str := LC_str||CHR(10)||'Number of benefit profiles received:  '|| VN_pr_tot;
--      DBMS_OUTPUT.PUT_LINE(LC_str);
      vSubj := VC_process_name||' - Report ' ||'('||VN_Inst_name||'@'||VC_host_name||')';
      vMesg := LC_str;
      VC_log_step := 'Invoking utl_mail.send';
      UTL_MAIL.SEND (SENDER  	=> 'oracle@'||VC_host_name||'aac.DNS   ',
               	    RECIPIENTS 	=> VC_email_list,
                    SUBJECT    	=> vSubj,
                    MESSAGE 	=> vMesg);

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

  EXCEPTION

    WHEN NO_DATA_FOUND THEN

      NULL;

    WHEN OTHERS THEN

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


END HANDBOOK_SP;
/
DROP PROCEDURE ADR.HL7_TXN_LOG_NULL_ERR_COL_SP;

CREATE OR REPLACE PROCEDURE ADR.Hl7_Txn_Log_Null_Err_Col_Sp
  (PB_run_standalone
     IN     BOOLEAN DEFAULT FALSE,
   PB_allow_rpt_inst
     IN     BOOLEAN DEFAULT FALSE,
   PN_rows_to_process
     IN     NUMBER DEFAULT NULL
  )
IS

-- ***************************************************************************
-- * Procedure: Hl7_Txn_Log_Null_Error_Col
-- *
-- *   Update HL7 transcations to remove data from the internal_error_text
-- *   column for all rows more than 6 months old.
-- *
-- * Parameters:
-- *
-- *  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
-- *
-- ***************************************************************************
--
-- ===========================================================================
-- Date        Author            Description
-- ===========================================================================
-- 06/23/2010  Craig Wood        Created for CCR 1100.
--
-- ===========================================================================

-- CONSTANTS

  CC_no                                VARCHAR2(1) := 'N';
  CC_parm_application_name    CONSTANT VARCHAR2(80) := 'HL7 NULL INTERNAL_ERROR_TEXT COLUMN';
  CC_parm_hl7_err_txt_age     CONSTANT VARCHAR2(50) := 'OLDEST HL7 ERROR TEXT DAYS';
  CC_parm_last_hl7_id         CONSTANT VARCHAR2(50) := 'LAST HL7 ID PROCESSED';
  CC_parm_log_file_age        CONSTANT VARCHAR2(50) := 'OLDEST LOG FILE DAYS';
  CC_process_name             CONSTANT VARCHAR2(60) := 'HL7 NULL INTERNAL_ERROR_TEXT COLUMN';
  CC_sp_name                  CONSTANT VARCHAR2(35) := 'Hl7_Txn_Log_Null_Err_Col_Sp';
  CC_yes                               VARCHAR2(1) := 'Y';

-- 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(25);
  VC_run_standalone                    VARCHAR2(5) := NULL;
  VC_sql_statement                     VARCHAR2(2000);

  VD_log_date                          DATE;
  VD_process_end_date                  DATE;
  VD_process_start_date                DATE;
  VD_rec_group_date                    DATE;

  VN_job_count                         NUMBER := 0;
  VN_job_number                        NUMBER;
  VN_max_log_id                        NUMBER := 0;
  VN_parm_hl7_err_txt_age              NUMBER;
  VN_parm_log_file_age                 NUMBER;
  VN_parm_last_log_id                  NUMBER := 0;
  VN_parm_rows_to_process              NUMBER;
  VN_pass_count                        NUMBER := 0;
  VN_rec_count                         NUMBER := 0;
  VN_update_count                      NUMBER := 0;

-- CURSORS

  CURSOR C_hl7_id IS
    SELECT    Hl7_transaction_log_id
    FROM      Hl7_transaction_log
    WHERE     Hl7_transaction_log_id > VN_parm_last_log_id
    AND       Record_created_date < TRUNC(SYSDATE) - VN_parm_hl7_err_txt_age
    AND       Internal_error_text IS NOT NULL;

-- TYPES

  TYPE TT_hl7_id IS
    TABLE OF ADR.Hl7_Transaction_Log.Hl7_transaction_log_id%TYPE;

-- MEMORY TABLES

  T_hl7_id                             TT_hl7_id;

-- **************************************************************************
-- *
-- *  MAIN PROCEDURE:
-- *
-- **************************************************************************

BEGIN

  VC_sql_statement:='ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT = 128';
  EXECUTE IMMEDIATE VC_sql_statement;

  VC_sql_statement:='ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 6';
  EXECUTE IMMEDIATE VC_sql_statement;

  VC_sql_statement:='ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS';
  EXECUTE IMMEDIATE VC_sql_statement;

  VC_sql_statement:='ALTER SESSION SET SORT_AREA_SIZE = 8192000';
  EXECUTE IMMEDIATE VC_sql_statement;

  VC_sql_statement:='ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8';
  EXECUTE IMMEDIATE VC_sql_statement;

  VC_sql_statement:='ALTER SESSION FORCE PARALLEL DML PARALLEL 8';
  EXECUTE IMMEDIATE VC_sql_statement;

  VD_rec_group_date := TRUNC(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('Process Initiation',1,60),
                            PD_date1 => VD_rec_group_date,
                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

-- ***************************************************************************
-- *
-- * Validate 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;

-- ***************************************************************************
-- *
-- * Retrieve processing parameters
-- *
-- ***************************************************************************

-- Process Parameter OLDEST HL7 ERROR TEXT DAYS KEPT

  BEGIN

    SELECT    Value
    INTO      VN_parm_hl7_err_txt_age
    FROM      ADRMGT.Adr_Parameter
    WHERE     Application_name = 'HL7 NULL INTERNAL_ERROR_TEXT COLUMN'
    AND       Parameter_name = 'OLDEST HL7 ERROR TEXT DAYS KEPT';

    IF VN_parm_hl7_err_txt_age IS NULL 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       - Unable to retrieve job parameters',1,60),
                                PC_job_log_message => SUBSTR('Parameter OLDEST HL7 ERROR TEXT DAYS KEPT is NULL. Terminating process.',1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

    END IF;

  EXCEPTION

    WHEN NO_DATA_FOUND 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       - Unable to retrieve job parameters',1,60),
                                PC_job_log_message => 'Parameter for OLDEST HL7 ERROR TEXT DAYS KEPT was not found. Terminating process.',
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      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       - Unable to retrieve job parameters',1,60),
                                PC_job_log_message => SUBSTR('Unknown error retrieving parameter for OLDEST HL7 ERROR TEXT DAYS KEPT. Terminating process. '
                                                             || SQLERRM,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- Process Parameter OLDEST LOG FILE DAYS KEPT

  BEGIN

    SELECT    Value
    INTO      VN_parm_log_file_age
    FROM      ADRMGT.Adr_Parameter
    WHERE     Application_name = 'HL7 NULL INTERNAL_ERROR_TEXT COLUMN'
    AND       Parameter_name = 'OLDEST LOG FILE DAYS KEPT';

    IF VN_parm_log_file_age IS NULL 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       - Unable to retrieve job parameters',1,60),
                                PC_job_log_message => SUBSTR('Parameter OLDEST LOG FILE DAYS KEPT is NULL. Terminating process.',1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

    END IF;

  EXCEPTION

    WHEN NO_DATA_FOUND 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       - Unable to retrieve job parameters',1,60),
                                PC_job_log_message => 'Parameter for OLDEST LOG FILE DAYS KEPT was not found. Terminating process.',
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      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       - Unable to retrieve job parameters',1,60),
                                PC_job_log_message => SUBSTR('Unknown error retrieving parameter for OLDEST LOG FILE DAYS KEPT. Terminating process. '
                                                             || SQLERRM,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- Process Parameter ROWS TO PROCESS

  BEGIN

    SELECT    Value
    INTO      VN_parm_rows_to_process
    FROM      ADRMGT.Adr_Parameter
    WHERE     Application_name = 'HL7 NULL INTERNAL_ERROR_TEXT COLUMN'
    AND       Parameter_name = 'ROWS TO PROCESS';

    IF VN_parm_rows_to_process IS NULL 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       - Unable to retrieve job parameters',1,60),
                                PC_job_log_message => SUBSTR('Parameter ROWS TO PROCESS is NULL. Terminating process.',1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

    END IF;

  EXCEPTION

    WHEN NO_DATA_FOUND 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       - Unable to retrieve job parameters',1,60),
                                PC_job_log_message => 'Parameter for ROWS TO PROCESS was not found. Terminating process.',
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      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       - Unable to retrieve job parameters',1,60),
                                PC_job_log_message => SUBSTR('Unknown error retrieving parameter for ROWS TO PROCESS. Terminating process. '
                                                             || SQLERRM,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- Process Parameter LAST PROCESSED LOG ID

  BEGIN

    SELECT    Value
    INTO      VN_parm_last_log_id
    FROM      ADRMGT.Adr_Parameter
    WHERE     Application_name = 'HL7 NULL INTERNAL_ERROR_TEXT COLUMN'
    AND       Parameter_name = 'LAST PROCESSED LOG ID';

    IF VN_parm_last_log_id IS NULL 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       - Unable to retrieve job parameters',1,60),
                                PC_job_log_message => SUBSTR('Parameter LAST PROCESSED LOG ID is NULL. Terminating process.',1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

    END IF;

  EXCEPTION

    WHEN NO_DATA_FOUND 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       - Unable to retrieve job parameters',1,60),
                                PC_job_log_message => 'Parameter for LAST PROCESSED LOG ID was not found. Terminating process.',
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      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       - Unable to retrieve job parameters',1,60),
                                PC_job_log_message => SUBSTR('Unknown error retrieving parameter for LAST PROCESSED LOG ID. Terminating process. '
                                                             || SQLERRM,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- Write out Parameter log messages for submitted job

  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 - Job submission parameters',1,60),
                            PC_job_log_message => SUBSTR(' Run standalone: ' || VC_run_standalone,1,2000),
                            PD_date1 => VD_rec_group_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 - Job submission parameters',1,60),
                            PC_job_log_message => SUBSTR(' Run on reporting databases: ' || VC_allow_rpt_inst,1,2000),
                            PD_date1 => VD_rec_group_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 - Job submission parameters',1,60),
                            PC_job_log_message => SUBSTR(' Rows to process each loop: ' || NVL(TO_CHAR(PN_rows_to_process),'NULL'),1,2000),
                            PD_date1 => VD_rec_group_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 - Job table parameters',1,60),
                            PC_job_log_message => SUBSTR(' OLDEST HL7 ERROR TEXT DAYS KEPT: ' || VN_parm_hl7_err_txt_age,1,2000),
                            PD_date1 => VD_rec_group_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 - Job table parameters',1,60),
                            PC_job_log_message => SUBSTR(' OLDEST LOG FILE DAYS KEPT: ' || VN_parm_log_file_age,1,2000),
                            PD_date1 => VD_rec_group_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 - Job table parameters',1,60),
                            PC_job_log_message => SUBSTR(' ROWS TO PROCESS (in each loop): ' || NVL(TO_CHAR(VN_parm_rows_to_process),'NULL'),1,2000),
                            PD_date1 => VD_rec_group_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 - Job table parameters',1,60),
                            PC_job_log_message => SUBSTR(' LAST PROCESSED LOG ID: ' || NVL(TO_CHAR(VN_parm_last_log_id),'NULL'),1,2000),
                            PD_date1 => VD_rec_group_date,
                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

-- If ROWS TO PROCESS parameter is overridden, write out a log message

  IF PN_rows_to_process IS NOT NULL THEN

    VN_parm_rows_to_process := PN_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('WARNING     - Job table parameters',1,60),
                              PC_job_log_message => SUBSTR(' Processing parameter ROWS TO PROCESS (in each loop) has been overridden.'
                                                      || ' The override value is: ' || NVL(TO_CHAR(VN_parm_rows_to_process),'NULL'),1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  END IF;

-- ***************************************************************************
-- *
-- * 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.
-- *
-- ***************************************************************************

  IF NOT VB_allow_rpt_inst THEN

    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 - Checking if running on a reporting database',1,60),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      SELECT    Instance_name
      INTO      VC_instance_name
      FROM      V$instance;

      IF 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       - Processing Failed',1,60),
                                  PC_job_log_message => SUBSTR('Attempting to run on a reporting instance. Instance name: '
                                                            || VC_instance_name
                                                            || '. 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_rec_group_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       - Unable to retrieve instance name',1,60),
                                  PC_job_log_message => SUBSTR('Terminating process. ' || SQLERRM,1,2000),
                                  PD_date1 => VD_rec_group_date,
                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

        RETURN;

    END;

  END IF;

-- ***************************************************************************
-- *
-- * Check to see if the job has already run today
-- *  - If running in stand-alone mode, don't check.
-- *  - Otherwise, if job has already run today, then exit the job.
-- *
-- ***************************************************************************

  IF NOT PB_run_standalone THEN

    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 - Checking if process has already ran today',1,60),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      SELECT    COUNT(*)
      INTO      VN_rec_count
      FROM      ADRMGT.Job_Process_Logs
      WHERE     Job_name = CC_process_name
      AND       Date1 = VD_rec_group_date
      AND       Job_status LIKE 'SUCCESS%-%Processing Completed';

      IF VN_rec_count > 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('WARNING     - This date has already been processed',1,60),
                                  PC_job_log_message => SUBSTR('Processing has already completed for this date. Exiting to job queue.',1,2000),
                                  PD_date1 => VD_rec_group_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       - Unable to verify if job has already run',1,60),
                                  PC_job_log_message => SUBSTR('Terminating process. ' || SQLERRM,1,2000),
                                  PD_date1 => VD_rec_group_date,
                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

        RETURN;

    END;

  END IF;

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

  SELECT COUNT(*)
  INTO   VN_job_count
  FROM   SYS.DBA_JOBS_RUNNING R,
         SYS.DBA_JOBS J
  WHERE  J.JOB = R.JOB
  AND    UPPER(J.WHAT) LIKE '%' || UPPER(CC_sp_name) || '%';

  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('WARNING     - Check for running jobs',1,60),
                              PC_job_log_message => SUBSTR('There is another '
                                                        || CC_sp_name
                                                        || ' job running.'
                                                        || ' Exiting job without running.',1,2000),
                              PD_date1 => VD_rec_group_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 - Starting in STANDALONE mode',1,60),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    ELSE

      SELECT    MAX(J.Job)
      INTO      VN_job_number
      FROM      SYS.DBA_JOBS_RUNNING R,
                SYS.DBA_JOBS J
      WHERE     J.JOB = R.JOB
      AND       UPPER(J.WHAT) LIKE '%' || UPPER(CC_sp_name) || '%';

      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 - Starting in DBMS_JOB mode',1,60),
                                PC_job_log_message => SUBSTR('DBMS_JOB Number: '
                                                             || VN_job_number,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    END IF;

  END IF;

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

  VN_pass_count := 0;
  VN_update_count := 0;
  VD_process_start_date := SYSDATE;

-- ***************************************************************************
-- * Remove old process log 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 - Removing old process logs',1,60),
                              PC_job_log_message => SUBSTR('Removing process logs over '
                                                           || VN_parm_log_file_age
                                                           || ' days old',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    DELETE
    FROM      ADRMGT.Job_Process_Logs
    WHERE     Job_name = CC_process_name
    AND       Date1 < TRUNC(SYSDATE) - VN_parm_log_file_age;

    COMMIT;

    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 - Removed old process logs',1,60),
                              PC_job_log_message => SUBSTR('Removed process logs over '
                                                           || VN_parm_log_file_age
                                                           || ' days old',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));


  EXCEPTION

    WHEN NO_DATA_FOUND THEN

      NULL;

    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 removing old process logs.',1,60),
                                PC_job_log_message => SUBSTR(SQLERRM,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  END;

-- ***************************************************************************
-- * Remove old process 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 - Removing old process errors',1,60),
                              PC_job_log_message => SUBSTR('Removing process errors over '
                                                           || VN_parm_log_file_age
                                                           || ' days old',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    DELETE
    FROM      ADRMGT.Job_Process_Errs
    WHERE     Job_name = CC_process_name
    AND       Date1 < TRUNC(SYSDATE) - VN_parm_log_file_age;

    COMMIT;

    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 - Removed old process errors',1,60),
                              PC_job_log_message => SUBSTR('Removed process errors over '
                                                           || VN_parm_log_file_age
                                                           || ' days old',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN NO_DATA_FOUND THEN

      NULL;

    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 removing old process errors.',1,60),
                                PC_job_log_message => SUBSTR(SQLERRM,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  END;

-- ***************************************************************************
-- * Main record cursor logic
-- ***************************************************************************

  BEGIN

    VN_max_log_id := 0;
    VN_pass_count := 0;
    VN_update_count := 0;

    OPEN C_hl7_id;

    LOOP

      FETCH C_hl7_id
      BULK COLLECT INTO T_hl7_id
      LIMIT VN_parm_rows_to_process;

      EXIT WHEN T_hl7_id.COUNT = 0;

      FORALL I_hl7_id IN T_hl7_id.FIRST..T_hl7_id.last
        UPDATE    ADR.Hl7_transaction_log
        SET       Internal_error_text = NULL
        WHERE     Hl7_transaction_log_id = T_hl7_id(I_hl7_id);

      COMMIT;

      FOR I_hl7_id IN T_hl7_id.FIRST..T_hl7_id.last LOOP

        IF T_hl7_id(I_hl7_id) > VN_max_log_id THEN

          VN_max_log_id := T_hl7_id(I_hl7_id);

        END IF;

      END LOOP;

      VN_pass_count := VN_pass_count + 1;

      VN_update_count := VN_update_count + T_hl7_id.COUNT;

    END LOOP;

    CLOSE C_hl7_id;

    BEGIN

      IF NVL(VN_max_log_id,0) <> 0 THEN

        UPDATE    ADRMGT.Adr_Parameter
        SET       Value = VN_max_log_id
        WHERE     Application_name = 'HL7 NULL INTERNAL_ERROR_TEXT COLUMN'
        AND       Parameter_name = 'LAST PROCESSED LOG ID';

        COMMIT;

      ELSE

        VN_max_log_id := VN_parm_last_log_id;

      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       - Unknown error updating LAST PROCESSED LOG ID.',1,60),
                                  PC_job_log_message => SUBSTR('Last processed log id: ' || VN_max_log_id
                                                               || '. ' || SQLERRM,1,2000),
                                  PD_date1 => VD_rec_group_date,
                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

        RETURN;

    END;

-- Process completion information

    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 Statistics',1,60),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255),
                              PN_numeric2 => VN_pass_count,
                              PC_text2 => SUBSTR('Cursor Loop Count',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 - Process Statistics',1,60),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255),
                              PN_numeric2 => VN_update_count,
                              PC_text2 => SUBSTR('Updated Record Count',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 - Process Statistics',1,60),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255),
                              PN_numeric2 => VN_max_log_id,
                              PC_text2 => SUBSTR('Last processed log id',1,255));

    VD_process_end_date := SYSDATE;
    VC_process_duration := LTRIM(TO_CHAR(TRUNC((VD_process_end_date - VD_process_start_date)*24),'00')) || ' Hrs '
                           || LTRIM(TO_CHAR(TRUNC((((VD_process_end_date - VD_process_start_date)*24)-
                              TRUNC(((VD_process_end_date - VD_process_start_date)*24))) * 60),'00')) || ' Mins '
                           || LTRIM(TO_CHAR(TRUNC((((VD_process_end_date - VD_process_start_date)*24*60)-
                              TRUNC(((VD_process_end_date - VD_process_start_date)*24*60))) * 60),'00')) || ' 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('SUCCESS     - Processing Completed',1,60),
                              PC_job_log_message => SUBSTR('Processing Time: ' || VC_process_duration,1,2000),
                              PD_date1 => VD_rec_group_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 updating Internal_error_text.',1,60),
                                PC_job_log_message => SUBSTR('Cursor Loop Count: ' || VN_pass_count
                                                             || ' Updated Record Count: ' || VN_update_count
                                                             || '. ' || SQLERRM,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

EXCEPTION

  WHEN OTHERS THEN

    ROLLBACK;

    VD_log_date := SYSDATE;
    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 processing error in procedure Hl7_Txn_Log_Null_Err_Col_Sp.'
                                                          || SQLERRM,1,2000),
                              PD_date1 => VD_rec_group_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('ERROR       - Unknown processing error',1,60),
                              PC_job_log_message => SUBSTR('See error log table for details.',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

END Hl7_Txn_Log_Null_Err_Col_Sp;
/
DROP PROCEDURE ADR.LOAD_MISSING_RESIDENT_ADDR_SP;

CREATE OR REPLACE PROCEDURE ADR.LOAD_MISSING_RESIDENT_ADDR_SP(
    p_bulk_limit 	NUMBER DEFAULT 1000)
IS

   -- 549234: Create A Job to Insert Residential Addresses for Persons with Permanent but  without Residential addresses
   --gc_schema_owner_name        VARCHAR2 (10) := 'ADR';
   gc_program_name             VARCHAR2 (40) := 'LOAD_MISSING_RESIDENT_ADDR_SP';

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

   time_a     INTEGER;
   time_b     INTEGER;
   time_c     INTEGER;
   time_d     INTEGER;

   VC_msg_step       VARCHAR2(200);
   VC_error_msg	  VARCHAR2(200);
   VN_total_count     NUMBER := 0;

   VN_elapsed_time   NUMBER;

   VN_rowcnt_fetched  NUMBER := 0;
   VN_EXCEPTION_CNT  PLS_INTEGER;

   C_PERM_ADDRESSTYPE_ID CONSTANT NUMBER := 1449012;
   C_RESI_ADDRESSTYPE_ID CONSTANT NUMBER := 1807412;


   TYPE NEW_RESIDENT_ADDRESS_TAB IS TABLE OF ADDRESS%ROWTYPE  INDEX BY BINARY_INTEGER;
   L_new_resident_addr_tab    NEW_RESIDENT_ADDRESS_TAB;

   c_limit  CONSTANT PLS_INTEGER := p_bulk_limit;
   gc_interation  PLS_INTEGER := 0;

   CURSOR GetPermAddrInfo_C  IS
   WITH ADD_RES_ADDR AS
     ( SELECT  person_id
       FROM (
          SELECT DISTINCT person_id
          FROM address a
          WHERE STD_ADDRESSTYPE_ID = C_PERM_ADDRESSTYPE_ID  -- permanent addr
          AND person_id IS NOT NULL
        MINUS
          SELECT DISTINCT person_id
          FROM address a
          WHERE  STD_ADDRESSTYPE_ID = C_RESI_ADDRESSTYPE_ID  -- residential addr
          AND person_id IS NOT NULL
       )
     )
   SELECT *
   FROM ADDRESS a
   WHERE EXISTS (SELECT 1
                FROM ADD_RES_ADDR d
                WHERE a.person_id = d.person_id)
   AND a.STD_ADDRESSTYPE_ID = C_PERM_ADDRESSTYPE_ID  -- permanent addr
   AND a.person_id IS NOT NULL;

BEGIN

    -- ========================================================
    --
    -- Begin Bulk Collect Fetch
    --
    -- ========================================================
    EXECUTE IMMEDIATE 'ALTER SESSION SET PARALLEL_FORCE_LOCAL=TRUE';

    DBMS_APPLICATION_INFO.set_client_info(gc_program_name ||': '||p_bulk_limit||' STARTED '||TO_CHAR(SYSDATE,'MM/DD/YY HH24:MI:SS'));

    time_a := dbms_utility.get_time;
    VN_total_count := 0;

    VC_msg_step := 'OPEN CURSOR';

   ADRMGT.Job_Process_Log_Sp(PC_job_name => gc_program_name,
                                PD_job_log_date => SYSDATE,
                                PC_job_status => 'INFORMATION - Process Initiation',
                                PC_job_log_message => 'Load missing resident addresses ',
                                pc_text1 => 'BULK LIMIT: '|| c_limit,
                                PD_date1 => SYSDATE
                            );

    OPEN GetPermAddrInfo_C ;
    LOOP
         gc_interation := gc_interation + 1;

         FETCH GetPermAddrInfo_C BULK COLLECT INTO L_new_resident_addr_tab LIMIT c_limit;

         VN_rowcnt_fetched := VN_rowcnt_fetched + L_new_resident_addr_tab.COUNT;

         VC_msg_step := 'BULK LOAD BEGINS';

   	 time_b := dbms_utility.get_time;

         FORALL indx IN L_new_resident_addr_tab.FIRST.. L_new_resident_addr_tab.LAST SAVE EXCEPTIONS

            INSERT /*+ APPEND */ INTO ADDRESS (
                    ADDRESS_ID ,
                    PERSON_ID,
                    INSURANCE_ID,
                    STD_ADDRESSTYPE_ID,
                    ADDRESS_CHANGE_SRC_TYPE_ID,
                    STD_INSTITUTION_ID,
                    ADDRESS_INVALID_TYPE_ID,
                    NCOA_STATUS_TYPE_ID,
                    NCOA_DELIVERY_TYPE_ID,
                    NCOA_ZIP_MATCH_TYPE_ID,
                    NCOA_ZIP_NO_MATCH_TYPE_ID,
                    ADDRESS_LINE1,
                    ADDRESS_LINE2,
                    ADDRESS_LINE3,
                    CITY,
                    STATE_CODE,
                    COUNTY_CODE,
                    PROVINCE_CODE,
                    ZIP_CODE,
                    ZIP_PLUS_4,
                    POSTAL_CODE,
                    COUNTRY_CODE,
                    ADDRESS_CHANGE_EFFECTIVE_DATE,
                    ADDRESS_INVALIDATED_DATE,
                    NCOA_ADDRESS_CHECK_DATE,
                    ADDRESS_START_DATE_TXT,
                    ADDRESS_END_DATE_TXT,
                    ADDRESS_PHONE_NUMBER,
                    RECORD_CREATED_BY,
                    RECORD_CREATED_DATE,
                    RECORD_MODIFIED_BY,
                    RECORD_MODIFIED_DATE,
                    RECORD_MODIFIED_COUNT,
                    RECORD_MODIFIED_SRC_NAME,
                    PERSON_RELATION_ID,
                    CASSCERTIFIEDTYPE_ID,
                    CASS_CERTIFIED_DATE,
                    PURPOSE_OF_USE_TYPE_ID)
                VALUES(
                    TRANSACTION_S.NEXTVAL ,
                    L_new_resident_addr_tab(indx).PERSON_ID,
                    L_new_resident_addr_tab(indx).INSURANCE_ID,
                    1807412,
                    L_new_resident_addr_tab(indx).ADDRESS_CHANGE_SRC_TYPE_ID,
                    L_new_resident_addr_tab(indx).STD_INSTITUTION_ID,
                    L_new_resident_addr_tab(indx).ADDRESS_INVALID_TYPE_ID,
                    L_new_resident_addr_tab(indx).NCOA_STATUS_TYPE_ID,
                    L_new_resident_addr_tab(indx).NCOA_DELIVERY_TYPE_ID,
                    L_new_resident_addr_tab(indx).NCOA_ZIP_MATCH_TYPE_ID,
                    L_new_resident_addr_tab(indx).NCOA_ZIP_NO_MATCH_TYPE_ID,
                    L_new_resident_addr_tab(indx).ADDRESS_LINE1,
                    L_new_resident_addr_tab(indx).ADDRESS_LINE2,
                    L_new_resident_addr_tab(indx).ADDRESS_LINE3,
                    L_new_resident_addr_tab(indx).CITY,
                    L_new_resident_addr_tab(indx).STATE_CODE,
                    L_new_resident_addr_tab(indx).COUNTY_CODE,
                    L_new_resident_addr_tab(indx).PROVINCE_CODE,
                    L_new_resident_addr_tab(indx).ZIP_CODE,
                    L_new_resident_addr_tab(indx).ZIP_PLUS_4,
                    L_new_resident_addr_tab(indx).POSTAL_CODE,
                    L_new_resident_addr_tab(indx).COUNTRY_CODE,
                    L_new_resident_addr_tab(indx).ADDRESS_CHANGE_EFFECTIVE_DATE,
                    L_new_resident_addr_tab(indx).ADDRESS_INVALIDATED_DATE,
                    L_new_resident_addr_tab(indx).NCOA_ADDRESS_CHECK_DATE,
                    L_new_resident_addr_tab(indx).ADDRESS_START_DATE_TXT,
                    L_new_resident_addr_tab(indx).ADDRESS_END_DATE_TXT,
                    L_new_resident_addr_tab(indx).ADDRESS_PHONE_NUMBER,
                    '549234',
                    SYSDATE,
                    '549234',
                    SYSDATE,
                    0,
                    L_new_resident_addr_tab(indx).RECORD_MODIFIED_SRC_NAME,
                    L_new_resident_addr_tab(indx).PERSON_RELATION_ID,
                    L_new_resident_addr_tab(indx).CASSCERTIFIEDTYPE_ID,
                    L_new_resident_addr_tab(indx).CASS_CERTIFIED_DATE,
                    L_new_resident_addr_tab(indx).PURPOSE_OF_USE_TYPE_ID) ;

         commit;

  	 VC_msg_step := 'Displaying row count';

  	 -- Rows count added
         FOR I IN 1..L_new_resident_addr_tab.COUNT LOOP
              VN_total_count := VN_total_count + SQL%BULK_ROWCOUNT(I);
         END LOOP;

         time_c := dbms_utility.get_time;
         VN_elapsed_time := ROUND((time_c - time_b)/100/60,2);

         DBMS_APPLICATION_INFO.set_client_info('Iteration# '|| gc_interation||' - Rows: '||VN_total_count ||' - '||VN_elapsed_time||' min');

         ADRMGT.Job_Process_Log_Sp(PC_job_name => gc_program_name,
	                                 PD_job_log_date => SYSDATE,
	                                 PC_job_status => 'INFORMATION - Iteration: '|| gc_interation,
	                                 PC_job_log_message => 'Rows Added: '||VN_total_count||' - Elap(min): '||VN_elapsed_time,
	                                 PD_date1 => SYSDATE
                            );

         EXIT WHEN L_new_resident_addr_tab.COUNT = 0;


    END LOOP;

    CLOSE GetPermAddrInfo_C;

    DBMS_APPLICATION_INFO.set_client_info('Total Iterations '|| gc_interation||' - Row Added"  '||VN_total_count ||' - Elapsed: '||TO_CHAR(SYSDATE,'MM/DD/YY HH24:MI:SS'));

    time_d := dbms_utility.get_time;

    VN_elapsed_time := ROUND((time_d - time_a)/100/60,2);


    ADRMGT.Job_Process_Log_Sp(PC_job_name => gc_program_name,
    	                                 PD_job_log_date => SYSDATE,
    	                                 PC_job_status => 'INFORMATION - Process Ended - Elap(min): '||VN_elapsed_time,
    	                                 PC_job_log_message => 'Total Rows Added/Rows Processed: '||VN_total_count||'/'||VN_rowcnt_fetched,
    	                                 PD_date1 => SYSDATE
                            );


  EXCEPTION

    WHEN dml_errors THEN

      VN_EXCEPTION_CNT := SQL%BULK_EXCEPTIONS.COUNT;

      FOR I IN 1 .. VN_EXCEPTION_CNT LOOP
          VC_error_msg :=
              'ERROR BULK EXCEPTION: ' || VC_msg_step||CHR(10) || SQL%BULK_EXCEPTIONS(I).ERROR_INDEX ||
              ' Person ID: ' || L_new_resident_addr_tab(I).person_id ||
              ' Error Msg: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);

              ADRMGT.Job_Process_Log_Sp(PC_job_name => gc_program_name,
    	                                 PD_job_log_date => SYSDATE,
    	                                 PC_job_status => 'ERROR BULK EXCEPTION - Row# '||SQL%BULK_EXCEPTIONS(I).ERROR_INDEX,
    	                                 PC_job_log_message => 'Total Rows Added/Rows Processed: '||VN_total_count||'/'||VN_rowcnt_fetched||' Elapsed: '||VN_elapsed_time,
    	                                 PC_text1 => SUBSTR(VC_error_msg,1,200),
    	                                 PD_date1 => SYSDATE
                            );

           DBMS_OUTPUT.put_line(VC_error_msg);

      END LOOP;

   WHEN OTHERS THEN

      VC_error_msg :='ERROR EXCEPTION - '||VC_msg_step||CHR(10)|| SUBSTR('Unkown Error: '||SQLERRM,1,200);

      ADRMGT.Job_Process_Log_Sp(PC_job_name => gc_program_name,
          	                                 PD_job_log_date => SYSDATE,
          	                                 PC_job_status => 'ERROR EXCEPTION',
          	                                 PC_job_log_message => 'Total Rows Added/Rows Processed: '||VN_total_count||'/'||VN_rowcnt_fetched||' Elap(min): '||VN_elapsed_time,
          	                                 PC_text1 => SUBSTR(VC_error_msg,1,200),
          	                                 PD_date1 => SYSDATE
                            );

       DBMS_OUTPUT.PUT_LINE(VC_error_msg);


END LOAD_MISSING_RESIDENT_ADDR_SP;
/


CREATE OR REPLACE PUBLIC SYNONYM LOAD_MISSING_RESIDENT_ADDR_SP FOR ADR.LOAD_MISSING_RESIDENT_ADDR_SP;
DROP PROCEDURE ADR.PERSON_CHG_LOG_CLEANUP_SP;

CREATE OR REPLACE PROCEDURE ADR.Person_Chg_Log_Cleanup_Sp
  (PB_run_standalone
     IN     BOOLEAN DEFAULT FALSE,
   PB_allow_rpt_inst
     IN     BOOLEAN DEFAULT FALSE,
   PN_rows_to_process
     IN     NUMBER DEFAULT NULL
  )
IS

-- ***************************************************************************
-- * Procedure: ADR.Person_Chg_Log_Cleanup_Sp
-- *
-- *   Update the ADR.Person_Change_Log table to remove rows where the
-- *   submission date is older than the days defined in the
-- *   ADRMGT.Adr_Parameter table. Initially this was set to 31 days.
-- *
-- * Parameters:
-- *
-- *  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
-- *
-- ***************************************************************************
--
-- ===========================================================================
-- Date        Author            Description
-- ===========================================================================
-- 06/23/2010  Craig Wood        Created for CCR 1175.
-- 10/13/2014  Craig Wood        CCR 2152 - Fix exception handler routine in
--                                          main processing loop to close the
--                                          cursor and write to error table
--                                          instead of log table. Also, exit
--                                          job if too many errors occur.
-- ===========================================================================

-- CONSTANTS

  CC_no                                VARCHAR2(1) := 'N';
  CC_parm_application_name    CONSTANT VARCHAR2(80) := 'PERSON_CHG_LOG_CLEANUP_SP';
  CC_parm_log_file_age        CONSTANT VARCHAR2(50) := 'OLDEST LOG FILE DAYS KEPT';
  CC_parm_rows_to_process     CONSTANT VARCHAR2(50) := 'ROWS TO PROCESS';
  CC_parm_submitted_age       CONSTANT VARCHAR2(50) := 'OLDEST SUBMITTED DATE KEPT';
  CC_process_name             CONSTANT VARCHAR2(60) := 'Person_Change_Log Cleanup';
  CC_sp_name                  CONSTANT VARCHAR2(35) := 'Person_Chg_Log_Cleanup_Sp';
  CC_yes                      CONSTANT VARCHAR2(1) := 'Y';

  CN_max_error_count          CONSTANT NUMBER := 500; -- CCR 2152

-- 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(25);
  VC_run_standalone                    VARCHAR2(5) := NULL;
  VC_sql_statement                     VARCHAR2(2000);

  VD_log_date                          DATE;
  VD_process_end_date                  DATE;
  VD_process_start_date                DATE;
  VD_rec_group_date                    DATE;

  VN_error_count                       NUMBER := 0; -- CCR 2152
  VN_job_count                         NUMBER := 0;
  VN_job_number                        NUMBER;
  VN_parm_log_file_age                 NUMBER;
  VN_parm_rows_to_process              NUMBER;
  VN_parm_submitted_age                NUMBER;
  VN_pass_count                        NUMBER := 0;
  VN_rec_count                         NUMBER := 0;
  VN_update_count                      NUMBER := 0;

-- CURSORS

  CURSOR C_prsn_chg_rec IS
    SELECT    Person_change_log_id
    FROM      ADR.Person_Change_Log
    WHERE     Submitted_date < SYSDATE - VN_parm_submitted_age
    AND       ROWNUM <= VN_parm_rows_to_process;

-- TYPES

  TYPE TT_prsn_chg_id IS
    TABLE OF ADR.Person_Change_Log.Person_change_log_id%TYPE;

-- MEMORY TABLES

  T_prsn_chg_id                        TT_prsn_chg_id;

-- **************************************************************************
-- *
-- *  MAIN PROCEDURE:
-- *
-- **************************************************************************

BEGIN

  VC_sql_statement:='ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT = 128';
  EXECUTE IMMEDIATE VC_sql_statement;

  VC_sql_statement:='ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 6';
  EXECUTE IMMEDIATE VC_sql_statement;

  VC_sql_statement:='ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS';
  EXECUTE IMMEDIATE VC_sql_statement;

  VC_sql_statement:='ALTER SESSION SET SORT_AREA_SIZE = 8192000';
  EXECUTE IMMEDIATE VC_sql_statement;

  VD_rec_group_date := TRUNC(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('Process Initiation',1,60),
                            PD_date1 => VD_rec_group_date,
                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

-- ***************************************************************************
-- *
-- * Validate 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;

-- ***************************************************************************
-- *
-- * Retrieve processing parameters
-- *
-- ***************************************************************************

-- ***************************************************************************
-- * Process Parameter for OLDEST LOG FILE DAYS KEPT
-- ***************************************************************************

  BEGIN

    SELECT    Value
    INTO      VN_parm_log_file_age
    FROM      ADRMGT.Adr_Parameter
    WHERE     Application_name = CC_parm_application_name
    AND       Parameter_name = CC_parm_log_file_age;

    IF VN_parm_log_file_age IS NULL 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 - Job parameter',1,60),
                                PC_job_log_message => SUBSTR('Parameter '
                                                             || CC_parm_log_file_age
                                                             || 'is NULL. Terminating process.',1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

    END IF;

  EXCEPTION

    WHEN NO_DATA_FOUND 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 - Job parameter',1,60),
                                PC_job_log_message => 'Parameter for '
                                                      || CC_parm_log_file_age
                                                      || ' was not found. Terminating process.',
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      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 - Job parameter',1,60),
                                PC_job_log_message => SUBSTR('Unknown error retrieving parameter for '
                                                             || CC_parm_log_file_age
                                                             || '. Terminating process. '
                                                             || SQLERRM,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- ***************************************************************************
-- * Process Parameter for OLDEST SUBMITTED DATE KEPT
-- ***************************************************************************

  BEGIN

    SELECT    Value
    INTO      VN_parm_submitted_age
    FROM      ADRMGT.Adr_Parameter
    WHERE     Application_name = CC_parm_application_name
    AND       Parameter_name = CC_parm_submitted_age;

    IF VN_parm_submitted_age IS NULL 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 - Job parameter',1,60),
                                PC_job_log_message => SUBSTR('Parameter '
                                                             || CC_parm_submitted_age
                                                             || 'is NULL. Terminating process.',1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

    END IF;

  EXCEPTION

    WHEN NO_DATA_FOUND 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 - Job parameter',1,60),
                                PC_job_log_message => 'Parameter for '
                                                      || CC_parm_submitted_age
                                                      || ' was not found. Terminating process.',
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      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 - Job parameter',1,60),
                                PC_job_log_message => SUBSTR('Unknown error retrieving parameter for '
                                                             || CC_parm_submitted_age
                                                             || '. Terminating process. '
                                                             || SQLERRM,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- ***************************************************************************
-- * Process Parameter ROWS TO PROCESS
-- ***************************************************************************

  BEGIN

    SELECT    Value
    INTO      VN_parm_rows_to_process
    FROM      ADRMGT.Adr_Parameter
    WHERE     Application_name = CC_parm_application_name
    AND       Parameter_name = CC_parm_rows_to_process;

    IF VN_parm_rows_to_process IS NULL 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 - Job parameter',1,60),
                                PC_job_log_message => SUBSTR('Parameter '
                                                             || CC_parm_rows_to_process
                                                             || 'is NULL. Terminating process.',1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

    END IF;

  EXCEPTION

    WHEN NO_DATA_FOUND 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 - Job parameter',1,60),
                                PC_job_log_message => 'Parameter for '
                                                      || CC_parm_rows_to_process
                                                      || ' was not found. Terminating process.',
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      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 - Job parameter',1,60),
                                PC_job_log_message => SUBSTR('Unknown error retrieving parameter for '
                                                             || CC_parm_rows_to_process
                                                             || '. Terminating process. '
                                                             || SQLERRM,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

-- ***************************************************************************
-- *
-- * Write out Parameter log messages for submitted job
-- *
-- ***************************************************************************

-- ***************************************************************************
-- *  Job Submission Parameters
-- ***************************************************************************

  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 - Job submission parameters',1,60),
                            PC_job_log_message => SUBSTR(' Run standalone: ' || VC_run_standalone,1,2000),
                            PD_date1 => VD_rec_group_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 - Job submission parameters',1,60),
                            PC_job_log_message => SUBSTR(' Run on reporting databases: ' || VC_allow_rpt_inst,1,2000),
                            PD_date1 => VD_rec_group_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 - Job submission parameters',1,60),
                            PC_job_log_message => SUBSTR(' Rows to process each loop: ' || NVL(TO_CHAR(PN_rows_to_process),'NULL'),1,2000),
                            PD_date1 => VD_rec_group_date,
                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

-- ***************************************************************************
-- *  Job Table Parameters
-- ***************************************************************************

  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 - Job table parameters',1,60),
                            PC_job_log_message => SUBSTR(' ' || CC_parm_log_file_age || ': ' || VN_parm_log_file_age,1,2000),
                            PD_date1 => VD_rec_group_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 - Job table parameters',1,60),
                            PC_job_log_message => SUBSTR(' ' || CC_parm_submitted_age || ': ' || VN_parm_submitted_age,1,2000),
                            PD_date1 => VD_rec_group_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 - Job table parameters',1,60),
                            PC_job_log_message => SUBSTR(' '
                                                         || CC_parm_rows_to_process
                                                         || ' (in each loop): '
                                                         || TO_CHAR(VN_parm_rows_to_process),1,2000),
                            PD_date1 => VD_rec_group_date,
                            PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

-- If ROWS TO PROCESS parameter is overridden, write out a log message

  IF PN_rows_to_process IS NOT NULL THEN

    VN_parm_rows_to_process := PN_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('WARNING - Job table parameters',1,60),
                              PC_job_log_message => SUBSTR(' Processing parameter '
                                                           || CC_parm_rows_to_process
                                                           || ' (in each loop) has been overridden.'
                                                           || ' The override value is: '
                                                           || TO_CHAR(VN_parm_rows_to_process),1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  END IF;

-- ***************************************************************************
-- *
-- * 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.
-- *
-- ***************************************************************************

  IF NOT VB_allow_rpt_inst THEN

    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 - Checking if running on a reporting database',1,60),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      SELECT    Instance_name
      INTO      VC_instance_name
      FROM      V$instance;

      IF 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 - Processing Failed',1,60),
                                  PC_job_log_message => SUBSTR('Attempting to run on a reporting instance. Instance name: '
                                                            || VC_instance_name
                                                            || '. 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_rec_group_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 - Unable to retrieve instance name',1,60),
                                  PC_job_log_message => SUBSTR('Terminating process. ' || SQLERRM,1,2000),
                                  PD_date1 => VD_rec_group_date,
                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

        RETURN;

    END;

  END IF;

-- ***************************************************************************
-- *
-- * Check to see if the job has already run today
-- *  - If running in stand-alone mode, don't check.
-- *  - Otherwise, if job has already run today, then exit the job.
-- *
-- ***************************************************************************

  IF NOT PB_run_standalone THEN

    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 - Checking if process has already ran today',1,60),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      SELECT    COUNT(*)
      INTO      VN_rec_count
      FROM      ADRMGT.Job_Process_Logs
      WHERE     Job_name = CC_process_name
      AND       Date1 = VD_rec_group_date
      AND       Job_status LIKE 'SUCCESS%-%Processing Completed';

      IF VN_rec_count > 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('WARNING - This date has already been processed',1,60),
                                  PC_job_log_message => SUBSTR('Processing has already completed for this date. Exiting to job queue.',1,2000),
                                  PD_date1 => VD_rec_group_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 - Unable to verify if job has already run',1,60),
                                  PC_job_log_message => SUBSTR('Terminating process. ' || SQLERRM,1,2000),
                                  PD_date1 => VD_rec_group_date,
                                  PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

        RETURN;

    END;

  END IF;

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

  SELECT COUNT(*)
  INTO   VN_job_count
  FROM   SYS.DBA_JOBS_RUNNING R,
         SYS.DBA_JOBS J
  WHERE  J.JOB = R.JOB
  AND    UPPER(J.WHAT) LIKE '%' || UPPER(CC_sp_name) || '%';

  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('WARNING - Check for running jobs',1,60),
                              PC_job_log_message => SUBSTR('There is another '
                                                        || CC_sp_name
                                                        || ' job running.'
                                                        || ' Exiting job without running.',1,2000),
                              PD_date1 => VD_rec_group_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 - Starting in STANDALONE mode',1,60),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    ELSE

      SELECT    MAX(J.Job)
      INTO      VN_job_number
      FROM      SYS.DBA_JOBS_RUNNING R,
                SYS.DBA_JOBS J
      WHERE     J.JOB = R.JOB
      AND       UPPER(J.WHAT) LIKE '%' || UPPER(CC_sp_name) || '%';

      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 - Starting in DBMS_JOB mode',1,60),
                                PC_job_log_message => SUBSTR('DBMS_JOB Number: '
                                                             || VN_job_number,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    END IF;

  END IF;

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

  VN_pass_count := 0;
  VN_update_count := 0;
  VD_process_start_date := SYSDATE;

-- ***************************************************************************
-- * Remove old process log 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 - Removing old process logs',1,60),
                              PC_job_log_message => SUBSTR('Removing process logs over '
                                                           || VN_parm_log_file_age
                                                           || ' days old',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    DELETE
    FROM      ADRMGT.Job_Process_Logs
    WHERE     Job_name = CC_process_name
    AND       Date1 < TRUNC(SYSDATE) - VN_parm_log_file_age;

    COMMIT;

    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 - Removed old process logs',1,60),
                              PC_job_log_message => SUBSTR('Removed process logs over '
                                                           || VN_parm_log_file_age
                                                           || ' days old',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));


  EXCEPTION

    WHEN NO_DATA_FOUND THEN

      NULL;

    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 removing old process logs.',1,60),
                                PC_job_log_message => SUBSTR(SQLERRM,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  END;

-- ***************************************************************************
-- * Remove old process 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 - Removing old process errors',1,60),
                              PC_job_log_message => SUBSTR('Removing process errors over '
                                                           || VN_parm_log_file_age
                                                           || ' days old',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

    DELETE
    FROM      ADRMGT.Job_Process_Errs
    WHERE     Job_name = CC_process_name
    AND       Date1 < TRUNC(SYSDATE) - VN_parm_log_file_age;

    COMMIT;

    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 - Removed old process errors',1,60),
                              PC_job_log_message => SUBSTR('Removed process errors over '
                                                           || VN_parm_log_file_age
                                                           || ' days old',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  EXCEPTION

    WHEN NO_DATA_FOUND THEN

      NULL;

    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 removing old process errors.',1,60),
                                PC_job_log_message => SUBSTR(SQLERRM,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

  END;

-- ***************************************************************************
-- * Main record cursor logic
-- ***************************************************************************

  VN_pass_count := 0;
  VN_update_count := 0;

  LOOP

    BEGIN

      OPEN C_prsn_chg_rec;

      FETCH C_prsn_chg_rec
      BULK COLLECT INTO T_prsn_chg_id
      LIMIT VN_parm_rows_to_process;

      EXIT WHEN T_prsn_chg_id.COUNT = 0;

      FORALL I_prsn_chg_id IN T_prsn_chg_id.FIRST..T_prsn_chg_id.last
        DELETE
        FROM      ADR.Person_Change_Log
        WHERE     Person_change_log_id = T_prsn_chg_id(I_prsn_chg_id);

      COMMIT;

      VN_pass_count := VN_pass_count + 1;

      VN_update_count := VN_update_count + T_prsn_chg_id.COUNT;

      CLOSE C_prsn_chg_rec;

    EXCEPTION

      WHEN OTHERS THEN

        VN_error_count := VN_error_count + 1;  -- CCR 2152

        IF VN_error_count >= CN_max_error_count THEN  -- CCR 2152

          RETURN;  -- CCR 2152

        ELSE  -- CCR 2152

          IF C_prsn_chg_rec%ISOPEN THEN  -- CCR 2152

            CLOSE C_prsn_chg_rec;  -- CCR 2152

          END IF;  -- CCR 2152

          VD_log_date := SYSDATE;

          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 removing person change logs - '
                                                                || SQLERRM,1,2000),
                                    PD_date1 => VD_rec_group_date,
                                    PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));  -- CCR 2152

        END IF;  -- CCR 2152

    END;

  END LOOP;

-- ***************************************************************************
-- * Process completion information
-- ***************************************************************************

  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 - Process Statistics',1,60),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255),
                              PN_numeric2 => VN_pass_count,
                              PC_text2 => SUBSTR('Cursor Loop Count',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 - Process Statistics',1,60),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255),
                              PN_numeric2 => VN_update_count,
                              PC_text2 => SUBSTR('Deleted Records Count',1,255));

    VD_process_end_date := SYSDATE;
    VC_process_duration := LTRIM(TO_CHAR(TRUNC((VD_process_end_date - VD_process_start_date)*24),'00')) || ' Hrs '
                           || LTRIM(TO_CHAR(TRUNC((((VD_process_end_date - VD_process_start_date)*24)-
                              TRUNC(((VD_process_end_date - VD_process_start_date)*24))) * 60),'00')) || ' Mins '
                           || LTRIM(TO_CHAR(TRUNC((((VD_process_end_date - VD_process_start_date)*24*60)-
                              TRUNC(((VD_process_end_date - VD_process_start_date)*24*60))) * 60),'00')) || ' 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('SUCCESS - Processing Completed',1,60),
                              PC_job_log_message => SUBSTR('Processing Time: ' || VC_process_duration,1,2000),
                              PD_date1 => VD_rec_group_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 writing completion information.',1,60),
                                PC_job_log_message => SUBSTR(SQLERRM,1,2000),
                                PD_date1 => VD_rec_group_date,
                                PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

      RETURN;

  END;

EXCEPTION

  WHEN OTHERS THEN

    ROLLBACK;

    VD_log_date := SYSDATE;
    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 processing error in procedure Person_Chg_Log_Cleanup_Sp.'
                                                          || SQLERRM,1,2000),
                              PD_date1 => VD_rec_group_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('ERROR - Unknown processing error',1,60),
                              PC_job_log_message => SUBSTR('See error log table for details.',1,2000),
                              PD_date1 => VD_rec_group_date,
                              PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255));

END Person_Chg_Log_Cleanup_Sp;
/
DROP PROCEDURE ADR.REBLD_ADR_COM_MAIL_SEQ_SP;

CREATE OR REPLACE PROCEDURE ADR.REBLD_ADR_COM_MAIL_SEQ_SP
IS
BEGIN
  DECLARE

	sql_stmt		varchar2(200);
	error_code		varchar2(25);
	error_msg_text		varchar2(200);
	error_descr		varchar2(200);

  BEGIN		-- Procedure Body Begin
        sql_stmt := 'DROP SEQUENCE COM_MAILING_BARCODE_S';
	DBMS_OUTPUT.PUT_LINE (sql_stmt);
	EXECUTE IMMEDIATE sql_stmt;
	sql_stmt := 'CREATE SEQUENCE COM_MAILING_BARCODE_S';
	sql_stmt := sql_stmt ||' START WITH 1 ';
	sql_stmt := sql_stmt ||' NOMAXVALUE ';
	sql_stmt := sql_stmt ||' MINVALUE 1 ';
	sql_stmt := sql_stmt ||' NOCYCLE ';
	sql_stmt := sql_stmt ||' CACHE 25 ';
	sql_stmt := sql_stmt ||' ORDER ';
	DBMS_OUTPUT.PUT_LINE (sql_stmt);
	EXECUTE IMMEDIATE sql_stmt;
	sql_stmt := 'GRANT SELECT ON  COM_MAILING_BARCODE_S TO ADR_SEQ_R';
	DBMS_OUTPUT.PUT_LINE (sql_stmt);
	EXECUTE IMMEDIATE sql_stmt;


  EXCEPTION		-- Begin Person Trait Exception
    WHEN NOT_LOGGED_ON THEN
	BEGIN
	  error_code		:= SQLCODE ;
	  error_msg_text	:= substr(SQLERRM,1,64);
	  error_descr 		:= 'Not logged in, operation has failed: ';
	  DBMS_OUTPUT.PUT_LINE (error_descr);
	  DBMS_OUTPUT.PUT_LINE (error_code);
	  DBMS_OUTPUT.PUT_LINE (error_msg_text);
	END;

    WHEN TIMEOUT_ON_RESOURCE THEN
	BEGIN
	  error_code		:= SQLCODE ;
	  error_msg_text	:= substr(SQLERRM,1,64);
	  error_descr 		:= 'Resource unavailable, operation has failed: ';
	  DBMS_OUTPUT.PUT_LINE (error_descr);
	  DBMS_OUTPUT.PUT_LINE (error_code);
	  DBMS_OUTPUT.PUT_LINE (error_msg_text);
	END;

    WHEN OTHERS THEN
	BEGIN
	  error_code		:= SQLCODE ;
	  error_msg_text	:= substr(SQLERRM,1,64);
	  error_descr 		:= 'Error has occurred, operation has failed: ';
	  DBMS_OUTPUT.PUT_LINE (error_descr);
	  DBMS_OUTPUT.PUT_LINE (error_code);
	  DBMS_OUTPUT.PUT_LINE (error_msg_text);
	END;

  END;		-- End Person Trait PL/SQL Block


END REBLD_ADR_COM_MAIL_SEQ_SP;
/
DROP PROCEDURE ADR.UPD_QRTZTRG_CLOCK_ST_TIME_SP;

CREATE OR REPLACE PROCEDURE ADR.UPD_QRTZTRG_CLOCK_ST_TIME_SP (
   p_in_num_days       IN        NUMBER,
   p_in_num_hours      IN        NUMBER,
   p_in_vpid           IN        VARCHAR2,
   p_in_trigger_nm     IN        VARCHAR2,
   p_out_num_updated   OUT NUMBER,
   p_out_err_msg       OUT   VARCHAR2)
IS

   -- *****************************************************************************************
   -- * Procedure: UPD_QRTZTRG_CLOCK_ST_TIME_SP
   -- *
   -- *
   -- *  Description: - This procedure will be use to update the start time of any clock trigger
   -- *                           in QRTZ_TRIGGER table to mature the clock. This procedure will invoke by
   -- *                          clockTriggerBatchProcess for non produciton DBs.
   -- * Parameters:
   -- *
   -- *  pi_in_num_days          Day when trigger is going to expire.
   -- *  pi_in_num_hours        Hours when trigger is going to fire. It counts hours from midnight.
   -- *  pi_in_vpid                     VPID_VALUE - for which trigger time will change.
   -- *  pi_in_Trigger_nm         Name of the trigger.
   -- *  p_out_num_updated   Number of rows updated
   -- *  p_out_err_msg             Error message/Info message
   -- *
   -- Created Date           Created By
   -- ===========================================================================
   -- 04/25/2017              Mita Panda
   --
   --****************************************************************************************
   --- local variables
   v_vpid                     PERSON.VPID_VALUE%TYPE;
   v_start_time           NUMBER;
   v_person_id           NUMBER;
   v_trigger_name     QRTZ_TRIGGERS.TRIGGER_NAME%TYPE;
   v_new_start_time   NUMBER;
   v_new_date            VARCHAR2 (60);
   e_no_data_found    EXCEPTION;
BEGIN
   BEGIN
      SELECT person_id
        INTO v_person_id
        FROM person
       WHERE vpid_value = p_in_vpid;

   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN

         --DBMS_OUTPUT.PUT_LINE('This VPID does not exists in Person table' );
         RAISE e_no_data_found;
   END;


   --- Set the trigger name

   v_trigger_name := v_person_id || p_in_trigger_nm;

   --Set the trigger start time
   v_start_time :=
        (  TRUNC (SYSDATE + p_in_num_days)
         + p_in_num_hours / 24
         - TO_DATE ('01011970', 'DDMMYYYY'))
     	 * 60
      	* 60
      	* 24
      	* 1000;

   UPDATE qrtz_triggers
      SET START_TIME = v_start_time, NEXT_FIRE_TIME = v_start_time
    WHERE trigger_name = v_trigger_name;

   p_out_num_updated := SQL%ROWCOUNT;


   SELECT start_time,
          TO_CHAR (
               TO_DATE ('1970-01-01', 'yyyy-mm-dd hh:mi:ss')
             + (start_TIME / 86400000),
             'yyyy-mm-dd hh24:mi:ss am')
     INTO v_new_start_time, v_new_date
     FROM QRTZ_TRIGGERS
    WHERE trigger_name = v_trigger_name;

   --DBMS_OUTPUT.PUT_LINE(' TRIGGER NEW_DATE_TIME:- '||v_new_date||', NEW_START_TIME:-'||v_new_start_time);

   p_out_err_msg :=
         'No errors, For VPID:- '
      || p_in_vpid
      || ', For Trigger:-'
      || v_trigger_name
      || ' the NEW_DATE_TIME:- '
      || v_new_date;
   COMMIT;

EXCEPTION

   WHEN e_no_data_found
   THEN
      p_out_err_msg := 'This VPID does not exists in Person table';
      p_out_num_updated := 0;
   WHEN NO_DATA_FOUND

   THEN
      ---DBMS_OUTPUT.PUT_LINE('No trigger found for this VPID '||v_vpid||', Check may be the trigger already fired.');
      p_out_num_updated := 0;
      p_out_err_msg :=
         'No trigger found for this VPID in QRTZ_TRIGGERS table.';
   WHEN OTHERS
   THEN
      --  DBMS_OUTPUT.PUT_LINE(sqlcode||substr(sqlerrm,1,100));

      p_out_num_updated := 0;
      p_out_err_msg := SQLCODE || SUBSTR (SQLERRM, 1, 100);
END UPD_QRTZTRG_CLOCK_ST_TIME_SP;
/


CREATE OR REPLACE PUBLIC SYNONYM UPD_QRTZTRG_CLOCK_ST_TIME_SP FOR ADR.UPD_QRTZTRG_CLOCK_ST_TIME_SP;


GRANT EXECUTE ON ADR.UPD_QRTZTRG_CLOCK_ST_TIME_SP TO EEUSR;
DROP PROCEDURE ADR.WKF_CASE_H_DELETE_SP;

CREATE OR REPLACE PROCEDURE ADR.WKF_CASE_H_DELETE_SP
IS



  CURSOR c_wkf  IS SELECT WKF_CASE_H.wkf_case_id
       from ADR.wkf_case_H
      where WKF_CASE_H.transaction_type_code = 'D'
    AND WKF_CASE_H.WKF_CASE_TYPE_ID = 1616295;

  sql_stmt string(512);
  v_cnt_wkf number(20);
  VD_log_date                          DATE;
  VD_rec_group_date                    DATE;
  VC_process_name                      VARCHAR2(60) := 'ADR WKF_CASE_H DELETE';
  VN_error_recs_count                  NUMBER := 0;
   BEGIN
        select  count(wkf_case_id)
        into v_cnt_wkf
        from wkf_case_H
        where transaction_type_code = 'D'
        AND WKF_CASE_TYPE_ID = 1616295;
       if v_cnt_wkf > 0 THEN
            FOR this_sql in  c_wkf  LOOP
                sql_stmt :=  'DELETE FROM ADR.WKF_CASE_ASSIGNMENT_H  WHERE WKF_CASE_ID = ' ||this_sql.WKF_CASE_ID;
                EXECUTE IMMEDIATE sql_stmt;
                sql_stmt :=  'DELETE FROM ADR.WKF_CASE_COMMENTS_H  WHERE WKF_CASE_ID = ' ||this_sql.WKF_CASE_ID;
                EXECUTE IMMEDIATE sql_stmt;
                sql_stmt :=  'DELETE FROM ADR.WKF_CASE_STATUS_DETAIL_H   WHERE WKF_CASE_ID = ' ||this_sql.WKF_CASE_ID;
                EXECUTE IMMEDIATE sql_stmt;
                sql_stmt :=  'DELETE FROM ADR.WKF_MESSAGING_CASE_H   WHERE WKF_CASE_ID = ' ||this_sql.WKF_CASE_ID;
                EXECUTE IMMEDIATE sql_stmt;
                sql_stmt :=  'DELETE FROM ADR.WKF_CASE_H   WHERE WKF_CASE_ID = ' ||this_sql.WKF_CASE_ID;
                EXECUTE IMMEDIATE sql_stmt;
               commit;
          END LOOP;
      END IF;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
             NULL;
        WHEN OTHERS THEN
          VN_error_recs_count := VN_error_recs_count + 1;
          ROLLBACK;
          VD_log_date := SYSDATE;
          ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(VC_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 from ADR.Address row.'
                                                                || SQLERRM,1,2000),
                                    PD_date1 => VD_rec_group_date,
                                    PC_text1 => SUBSTR('Date1 column contains the daily process start date',1,255)
                                    );
END WKF_CASE_H_DELETE_SP;
/
