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


-- ESR CR 7265 Registry SSN Cleanup script. 
-- This script fixes records in ADR where the SSN in the Purple Heart,
-- POW or SHAD registry is less than 9 digits.

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.put_line (' ');
   DBMS_OUTPUT.put_line ('ESR CR 7265 Cleanup script to correct SSN in registry.');
   DBMS_OUTPUT.put_line (' ');
END;
/

SET TERMOUT OFF

update registry_trait
set ssn = concat('0', ssn),
record_modified_by = 'ADD 1 LEAD 0 - 8 DIGIT SSNs',
record_modified_date = SYSDATE,
record_modified_count = record_modified_count + 1
where length(ssn) = 8;

update registry_trait
set ssn = concat('00', ssn),
record_modified_by = 'ADD 2 LEAD 0 - 7 DIGIT SSNs',
record_modified_date = SYSDATE,
record_modified_count = record_modified_count + 1
where length(ssn) = 7;

update registry_trait
set ssn = concat('000', ssn),
record_modified_by = 'ADD 3 LEAD 0 - 6 DIGIT SSNs',
record_modified_date = SYSDATE,
record_modified_count = record_modified_count + 1
where length(ssn) = 6;

update registry_trait
set ssn = concat('0000', ssn),
record_modified_by = 'ADD 4 LEAD 0 - 5 DIGIT SSNs ',
record_modified_date = SYSDATE,
record_modified_count = record_modified_count + 1
where length(ssn) = 5;

update registry_trait
set ssn = concat('00000', ssn),
record_modified_by = 'ADD 5 LEAD 0 - 4 DIGIT SSNs',
record_modified_date = SYSDATE,
record_modified_count = record_modified_count + 1
where length(ssn) = 4;

update registry_trait
set ssn = concat('000000', ssn),
record_modified_by = 'ADD 6 LEAD 0 - 3 DIGIT SSNs',
record_modified_date = SYSDATE,
record_modified_count = record_modified_count + 1
where length(ssn) = 3;

update registry_trait
set ssn = concat('0000000', ssn),
record_modified_by = 'ADD 7 LEAD 0 - 2 DIGIT SSNs',
record_modified_date = SYSDATE,
record_modified_count = record_modified_count + 1
where length(ssn) = 2;

update registry_trait
set ssn = concat('00000000', ssn),
record_modified_by = 'ADD 8 LEAD 0 - 1 DIGIT SSNs ',
record_modified_date = SYSDATE,
record_modified_count = record_modified_count + 1
where length(ssn) = 1;

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Added Leading zeros to SSNs in the PH/POW/SHAD registry where the SSN had less than 9 digits.');
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
