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


-- ESR CR 7649 Cleanup script.

SET SERVEROUTPUT ON SIZE 1000000
SET TERMOUT ON

BEGIN
   DBMS_OUTPUT.put_line (' ');
   DBMS_OUTPUT.put_line
           ('ESR CR 7649 Cleanup script to correct Data in INCOME_TEST table');
   DBMS_OUTPUT.put_line (' ');
END;
/

DECLARE
   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);

   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
   OPEN futureyearscur;

   DBMS_OUTPUT.put_line
      ('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_f_income_test_effective_date' );

   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 ;
     

      --find the current test for this person
	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 := v_debug || 'STEP2 for :' || v_f_income_test_id ;
         
         
      -- 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' ;
            
            v_debug := v_debug || 'STEP3 [income_test_h]for : v_f_income_test_id :' || v_f_income_test_id  ;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            DBMS_OUTPUT.put_line
                               (   'NO DATA FOUND IN THE HISTORY TABLE FOR :'
                                || v_f_income_test_id
                               );
            GOTO  END_OF_LOOP;
            WHEN TOO_MANY_ROWS THEN
	    		DBMS_OUTPUT.put_line
	                            (   'TOO MANY ROWS  :'
	                             || v_income_test_detail_id || 'v_debug :' || v_debug
                        );
	      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 = 'CCR7649 DataCleanup',
             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 := v_debug || 'STEP4 UPDATE finished';
      --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 :=	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;
                            	     


         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 <> 'CCR7649 DataCleanup' ;

            DBMS_OUTPUT.put_line (   'Fixing the detail :'
                                  || v_income_test_detail_id
                                  || ' With date :'
                                  || vh_income_test_completed_date
                                 );

            UPDATE income_test_detail
               SET income_test_completed_date = vh_income_test_completed_date,
		           record_modified_by = 'CCR7649 DataCleanup',
                   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
               DBMS_OUTPUT.put_line
                        (   'NO DETAIL DATA FOUND IN THE HISTORY TABLE FOR :'
                         || v_income_test_detail_id
                        );
            WHEN TOO_MANY_ROWS THEN
		DBMS_OUTPUT.put_line
                        (   'TOO MANY ROWS for income_test_detail :'
                         || v_income_test_detail_id || 'v_debug :' || v_debug
                        );
             
		                         
         END;
      END LOOP;

      CLOSE detailcursor;

	<<END_OF_LOOP>>
      DBMS_OUTPUT.put_line (  '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
                           );

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

   DBMS_OUTPUT.put_line
      (   'Completed the cleanup. Total Number of records still need to be fixed [SHOULD BE 0]:'
       || v_count
      );
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('NO DATA FOUND : ' || v_debug);
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('UNKNOWN EXCEPTION : ');
      dbms_output.put_line('error code is : '||sqlcode );
	  dbms_output.put_line('error code message is : '||SQLERRM );
         DBMS_OUTPUT.put_line (  '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
                           );	  
	 DBMS_OUTPUT.put_line ('STACK  : ' || v_debug);                           

END;
/

DEFINE UOW = &1

SET TERMOUT ON
BEGIN
   IF UPPER('&UOW') = 'COMMIT' THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('COMMIT ISSUED!');
      DBMS_OUTPUT.PUT_LINE(' ');
   ELSIF UPPER('&UOW') = 'NONE' THEN
      DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('NO COMMIT OR ROLLBACK ISSUED because NONE was requested.');
      DBMS_OUTPUT.PUT_LINE(' ');
   ELSE
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('ROLLBACK ISSUED!');
      DBMS_OUTPUT.PUT_LINE(' ');
   END IF;
END;
/


SET TERMOUT OFF
-- Reset environment parameters
SET FEEDBACK ON
SET VERIFY ON
SET TERMOUT ON
/