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 8299 Cleanup script.
-- Update empty country code to 'USA'

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.put_line (' ');
   DBMS_OUTPUT.put_line ('ESR CR 8299 Update empty country code.');
   DBMS_OUTPUT.put_line (' ');
END;
/

declare
    counter NUMBER;
    case_id WKF_CASE.WKF_CASE_ID%TYPE;
    case_status_id WKF_CASE_STATUS_DETAIL.WKF_CASE_STATUS_TYPE_ID%TYPE;
    error_text WKF_CASE.ERROR_MESSAGE_TEXT%TYPE;
    addr_id ADDRESS.ADDRESS_ID%TYPE;
    addr_modified_by ADDRESS.RECORD_MODIFIED_BY%TYPE;
    cursor cur is
        select  WKF_CASE.WKF_CASE_ID, WKF_CASE_STATUS_TYPE_ID, ERROR_MESSAGE_TEXT, ADDRESS_ID, ADDRESS.RECORD_MODIFIED_BY
             from ADDRESS, WKF_CASE, WKF_CASE_STATUS_DETAIL
             where WKF_CASE.PERSON_ID = ADDRESS.PERSON_ID
             and WKF_CASE.WKF_CASE_ID = WKF_CASE_STATUS_DETAIL.WKF_CASE_ID
             and ERROR_MESSAGE_TEXT like 'ORUZ05-S: %COUNTRY_REQUIRED%'
             and STD_ADDRESSTYPE_ID = 1449012 --permanent address
             and (COUNTRY_CODE is null or (ADDRESS.RECORD_MODIFIED_BY = 'Job-UpdateCountryCodeProcess' and WKF_CASE_STATUS_TYPE_ID != 1507204))
             and STATE_CODE in (select distinct POSTALNAME
                                       from STD_STATE 
                                       where COUNTRY_ID = 1006840 
                                             and fipscode < 60);
begin
    open cur;
    counter := 0;
    loop
        fetch cur into case_id, case_status_id, error_text, addr_id, addr_modified_by;
        exit when cur%NOTFOUND;
        if addr_modified_by != 'Job-UpdateCountryCodeProcess' then
	        update ADDRESS set COUNTRY_CODE = 'USA',
	                               RECORD_MODIFIED_DATE = sysdate,
	                               ADDRESS_CHANGE_EFFECTIVE_DATE = sysdate,
	                               ADDRESS_CHANGE_SRC_TYPE_ID = 1466951,
	                               RECORD_MODIFIED_BY = 'Job-UpdateCountryCodeProcess',
	                               RECORD_MODIFIED_COUNT = RECORD_MODIFIED_COUNT + 1
	                           where ADDRESS_ID = addr_id;
		end if;
		        
        if error_text = 'ORUZ05-S: COUNTRY_REQUIRED, ' then
            update WKF_CASE_STATUS_DETAIL set WKF_CASE_STATUS_TYPE_ID = 1507204,
                                              RECORD_MODIFIED_DATE = sysdate,
                                              RECORD_MODIFIED_BY = 'Job-UpdateCountryCodeProcess',
                                              RECORD_MODIFIED_COUNT = RECORD_MODIFIED_COUNT + 1
                                          where WKF_CASE_ID = case_id;
        end if;
        
        counter := counter + 1;
        --periodically commit so we won't lose what're already processed
        if counter = 2048 then 
           IF UPPER('&ThisUOW') = 'COMMIT' THEN
              COMMIT;
              DBMS_OUTPUT.PUT_LINE(' ');
              DBMS_OUTPUT.PUT_LINE('COMMIT ISSUED!');
              DBMS_OUTPUT.PUT_LINE(' ');
           ELSIF UPPER('&ThisUOW') = 'NONE' THEN
              DBMS_OUTPUT.PUT_LINE(' ');
              DBMS_OUTPUT.PUT_LINE('NO COMMIT OR ROLLBACK ISSUED because NONE was requested.');
              DBMS_OUTPUT.PUT_LINE(' ');
           ELSE
              ROLLBACK;
              DBMS_OUTPUT.PUT_LINE(' ');
              DBMS_OUTPUT.PUT_LINE('ROLLBACK ISSUED!');
              DBMS_OUTPUT.PUT_LINE(' ');
           END IF;
           counter := 0;
        end if;        
    end loop;
    close cur;
end;    
/
     
SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Updated empty-coutry-code addresses.');
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