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

-- ESR CR 7219 Deprecated PSIM records Cleanup script.
-- This script should be run after Data Migration and before Bulk E&E. 
-- It fixes records in ADR where PS_PERSON_VPID.ID_STATE_IND = 'D' 
-- and PS_PERSON_CORRELATION.STATUS_IND = 'A' by setting STATUS_IND = 'D'

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.put_line (' ');
   DBMS_OUTPUT.put_line ('ESR CR 7219: Inconsistent records in PSIM cause errors in ESR (deprecated without surviving record).');
   DBMS_OUTPUT.put_line (' ');
END;
/

SET TERMOUT OFF

UPDATE PSIM.PS_PERSON_CORRELATION
SET STATUS_IND='D'
WHERE person_vpid_id IN (SELECT ps_personvpid_id FROM ps_person_vpid WHERE id_state_ind = 'D');

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Changed PS_PERSON_CORRELATION.STATUS_IND.');
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
