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

DEFINE ThisUOW = &1

-- ESR CR 8529 Cleanup script.
-- Change SSN Verification Status from In-process to New to resend them to SSA for verification

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.put_line (' ');
   DBMS_OUTPUT.put_line ('ESR CR 8529 Reprocess SSN Verifications.');
   DBMS_OUTPUT.put_line (' ');
END;
/

update SSN set 
			SSN_VRFN_STATUS_TYPE_ID = 1507631,
			RECORD_MODIFIED_BY = 'APII-ReprocessSSN',
			RECORD_MODIFIED_DATE = sysdate,
			RECORD_MODIFIED_COUNT = RECORD_MODIFIED_COUNT + 1
where SSN_VRFN_STATUS_TYPE_ID = 1507632
    and PERSON_RELATION_ID in 
    	(select PERSON_RELATION_ID from PERSON_RELATION 
    	 where PRSN_RELATION_MAPPING_TYPE_CD = 'SP'); 
 
/
     
SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Updated SSNs for reverification.');
END;
/
SET TERMOUT OFF

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Calling uow.sql &1');
END;
/

SET TERMOUT OFF
@@uow.sql &ThisUOW

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Done.');
END;
/
SET TERMOUT OFF
-- Reset environment parameters
UNDEFINE 1
UNDEFINE ThisUOW
SET FEEDBACK ON
SET VERIFY ON
SET TERMOUT ON