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

-- ESR CR 7667: The HEC-ESR Data Sync file contains the 
-- entire "Zip+4" in the zip+4 field.  ESR is uploading 
-- the whole thing as well as the zip in the previous field. 
-- This script removes the zip from the zip_plus_4 field, leaving
-- just the last 4 digits.


SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.put_line (' ');
   DBMS_OUTPUT.put_line ('ESR CR 7667: removing zip code from zip_plus_4.');
   DBMS_OUTPUT.put_line (' ');
END;
/

SET TERMOUT OFF

UPDATE address
   SET zip_plus_4 = SUBSTR(zip_plus_4, -4, 4),
		RECORD_MODIFIED_BY = 'ESR_CCR7667',
        RECORD_MODIFIED_DATE = SYSDATE,
        RECORD_MODIFIED_COUNT = RECORD_MODIFIED_COUNT + 1
 WHERE zip_plus_4 LIKE '%-____';

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Cleanedup zip_plus_4 to remove the zip code.');
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
