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


-- ESR CR 8564 Update all letters in Sent to AAC status to Reject at HEC

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.put_line (' ');
   DBMS_OUTPUT.put_line ('ESR CR 8564 Cleanup script to update all letters in Sent to AAC status to Reject at HEC .');
   DBMS_OUTPUT.put_line (' ');
END;
/

SET TERMOUT OFF

-- Set all letters that are Sent to AAC to Rejected at HEC.
-- 1615179 = 'Reject at HEC'
-- 1615177 = 'Sent to AAC'

UPDATE COM_MAILING_STATUS_DETAIL m
SET COM_MAILING_STATUS_TYPE_ID = 1615179 
WHERE COM_MAILING_STATUS_TYPE_ID = 1615177
AND com_mailing_status_detail_id IN (
          SELECT   MAX (com_mailing_status_detail_id)
              FROM com_mailing_status_detail md
             WHERE m.com_mailing_id = md.com_mailing_id
          GROUP BY md.com_mailing_id);


SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Update all letters in Sent to AAC status to Reject at HEC.');
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
