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

SET serveroutput ON SIZE 100000

DEFINE JobUOW = &1

-- ESR 3.1 Deployment script.

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE(' ');
   DBMS_OUTPUT.PUT_LINE('ESR 3.1 deployment script.');
   DBMS_OUTPUT.PUT_LINE(' ');
END;
/
SET TERMOUT OFF

-- 1.  Populate the VOA_IND column, set it to 0 (false) there is
-- existing enrollment determination for a given person

UPDATE person
   SET voa_ind = 0,
       record_modified_by = 'CCR7423_VOAInd',
             record_modified_date = SYSDATE,
             record_modified_count = record_modified_count + 1
 WHERE EXISTS (SELECT e.person_id
                 FROM enrollment_determination e
                WHERE e.person_id = person.person_id);

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Updated VOA_IND to 0 where enrollment determination exists.');
END;
/

SET TERMOUT OFF

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

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Done. Now verifying data.');
END;
/

DECLARE cnt INTEGER := 0;
BEGIN
	cnt := 0;
	
	SELECT count(*)
	INTO cnt
	FROM (SELECT p.person_id, voa_ind
          FROM person p
         WHERE EXISTS (SELECT e.person_id
                         FROM enrollment_determination e
                        WHERE e.person_id = p.person_id)
           AND (p.voa_ind IS NULL OR p.voa_ind = 1));

	IF (cnt > 0) 
		THEN
			DBMS_OUTPUT.PUT_LINE('ERROR - Records found in the person table where the person has existing enrollment record but VOA_ind is null or 1.');
		ELSE
			DBMS_OUTPUT.PUT_LINE('ESR 3.1 VOAInd script results verified, no records found.');			
	END IF;
END;
/

-- Reset environment parameters
UNDEFINE JobUOW
SET FEEDBACK ON
SET VERIFY ON
SET TERMOUT ON
