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


-- ESR CR 6786 Cleanup script.
-- This script should be run before Bulk E&E.  It identifies records where
-- the Cancel/Decline Indicator is Yes and the enrollment category is
-- NOT "Not Enrolled" and changes the indicator to No.


SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.put_line (' ');
   DBMS_OUTPUT.put_line ('ESR CR 6786 Cleanup script to correct cancel/decline indicator values.');
   DBMS_OUTPUT.put_line (' ');
END;
/
SET TERMOUT OFF

UPDATE cancel_decline
   SET cancel_decline_ind = 0,
       cancel_decline_reason_type_id = NULL,
       cancel_decline_effective_date = NULL,
       remarks = NULL
 WHERE person_id IN (
          SELECT ed.person_id
            FROM enrollment_determination ed JOIN cancel_decline cd ON ed.person_id =
                                                                         cd.person_id
                 JOIN std_enrollmentstatus se ON ed.std_enrollmentstatus_id =
                                                                         se.ID
           WHERE cd.cancel_decline_ind = 1
                 AND se.enrollmentcat_id <> 1615233);

-- 1615233 = Not Enrolled

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Changed Cancel/Decline Indicator to No where the current value is Yes and the enrollment category is NOT "Not Enrolled".');
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
