SET ECHO OFF
SET AUTOCOMMIT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET TERMOUT OFF
SET TRIMSPOOL OFF
SET SHOW OFF

-- ESR CR 7520 Override Cleanup script.
-- This script resets override indicators on records updated by data sync 
-- during period of time data sync was producing faulty EE results.

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.put_line (' ');
   DBMS_OUTPUT.put_line ('ESR CR 7530: Cleanup Override indicators.');
   DBMS_OUTPUT.put_line (' ');
END;
/

SET TERMOUT OFF

DECLARE
   CURSOR overridecleanup
   IS
--SELECT person_id, enrollment_determination_id, enrollment_overridden_flag
      SELECT person_id
        FROM enrollment_determination en
       WHERE record_modified_by = 'Job-HECLegacyDataSyncConsumer'
         AND enrollment_overridden_flag = 1
         AND (   (    std_enrollmentstatus_id = 1504162
                  AND en.enrollment_determination_id IN (
                         SELECT enrollment_determination_id
                           FROM (SELECT RANK () OVER (PARTITION BY e.enrollment_determination_id ORDER BY e.enrollment_determination_h_id DESC)
                                                                   AS en_rank,
                                        e.enrollment_determination_id,
                                        e.enrollment_overridden_flag,
                                        e.std_enrollmentstatus_id
                                   FROM enrollment_determination_h e)
                          WHERE en_rank = 2
                            AND en.enrollment_determination_id =
                                                   enrollment_determination_id
                            AND std_enrollmentstatus_id = 1504162
                            AND enrollment_overridden_flag = 0)
                 )
              OR (    std_enrollmentstatus_id = 1504142
                  AND en.enrollment_determination_id IN (
                         SELECT enrollment_determination_id
                           FROM (SELECT RANK () OVER (PARTITION BY e.enrollment_determination_id ORDER BY e.enrollment_determination_h_id DESC)
                                                                   AS en_rank,
                                        e.enrollment_determination_id,
                                        e.enrollment_overridden_flag,
                                        e.std_enrollmentstatus_id
                                   FROM enrollment_determination_h e)
                          WHERE en_rank = 2
                            AND en.enrollment_determination_id =
                                                   enrollment_determination_id
                            AND std_enrollmentstatus_id = 1504142
                            AND enrollment_overridden_flag = 0)
                 )
             );

   v_person_id                     enrollment_determination.person_id%TYPE;
   v_enrollment_determination_id   enrollment_determination.enrollment_determination_id%TYPE;
   v_hec_ien                       person.hec_ien%TYPE;
BEGIN
   OPEN overridecleanup;

   LOOP
      FETCH overridecleanup
       INTO v_person_id;                    -- fetches columns into variables

      EXIT WHEN overridecleanup%NOTFOUND;

      SELECT hec_ien
        INTO v_hec_ien
        FROM person
       WHERE person.person_id = v_person_id;

      DBMS_OUTPUT.put_line (   'PERSON ID = '
                            || v_person_id
                            || '  HEC IEN ='
                            || v_hec_ien
                           );

      UPDATE enrollment_determination
         SET enrollment_overridden_flag = 0,
             record_modified_by = 'CCR7520_OverrideCleanup',
             record_modified_date = SYSDATE,
             record_modified_count = record_modified_count + 1
       WHERE enrollment_determination.person_id = v_person_id;

      UPDATE enrollment_override
         SET override_flag = 0,
             override_date = NULL,
             std_enrollmentoverridersn_id = NULL,
             remarks = NULL,
             record_modified_by = 'CCR7520_OverrideCleanup',
             record_modified_date = SYSDATE,
             record_modified_count = record_modified_count + 1
       WHERE enrollment_override.person_id = v_person_id;
   --<<END_OF_LOOP>>
   END LOOP;

   CLOSE overridecleanup;

   DBMS_OUTPUT.put_line ('Completed the cleanup.');
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('NO DATA FOUND : ');
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('UNKNOWN EXCEPTION : ');
END;
/

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Changed Override and Enrollment override flags.');
END;
/
SET TERMOUT OFF

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Calling uow.sql &1');
  
END;
/
SET TERMOUT OFF
@@uow.sql &1

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Done.');
END;
/
SET TERMOUT OFF
-- Reset environment parameters
SET FEEDBACK ON
SET VERIFY ON
SET TERMOUT ON
