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


-- ESR CR 7619 script.
-- Database script to close merge records where neither person has enrollment record.


SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.put_line (' ');
   DBMS_OUTPUT.put_line ('ESR CR 7619 Close merge records where neither person has enrollemnt record.');
   DBMS_OUTPUT.put_line (' ');
END;
/
SET TERMOUT OFF

-- close merge script

update person_merge 
set MERGE_END_DATE = SYSDATE,
	   RECORD_MODIFIED_BY = 'CCR7619- CLOSE WHERE NO ENROLL',
       RECORD_MODIFIED_DATE = SYSDATE,
       RECORD_MODIFIED_COUNT = RECORD_MODIFIED_COUNT + 1 
where person_merge_id in (select   c.person_merge_id   
         from    (select   a.person_merge_id 
                ,a.primary_person_id person_id 
         from   person_merge a  
         where  a.merge_end_date is null 
         union all 
         select   b.person_merge_id 
                ,b.deprecated_person_id  person_id 
         from   person_merge b 
         where  b.merge_end_date is null 
        ) c 
where   not exists 
        (select  null 
         from    enrollment_determination d 
         where  c.person_id = d.person_id 
        ) 
group by c.person_merge_id 
having count(c.person_id) = 2); 


SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Closed merge records');
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
