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


-- ESR CR 7506 Cleanup script.
-- Close open PH work items when PH status is confirmed or rejected and remark is not null.


SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.put_line (' ');
   DBMS_OUTPUT.put_line ('ESR CR 7506 Close completed PH Work Items.');
   DBMS_OUTPUT.put_line (' ');
END;
/
SET TERMOUT ON
SET FEEDBACK ON
SET FEEDBACK 10


SELECT * FROM  WKF_CASE_STATUS_DETAIL 
    where WKF_CASE_STATUS_DETAIL_ID in 
        (select WKF_CASE_STATUS_DETAIL_ID
         from 
             WKF_CASE,
             WKF_CASE_STATUS_DETAIL,
             PURPLE_HEART_REGISTRY
         where
             WKF_CASE.PERSON_ID = PURPLE_HEART_REGISTRY.PERSON_ID and
             WKF_CASE.WKF_CASE_TYPE_ID = 1507491 and --PH Work Items
             (PURPLE_HEART_REGISTRY.STD_REJECTEDREMARKS_ID is not null or PURPLE_HEART_REGISTRY.PH_STATUS_TYPE_ID = 1504903) and --confirmed PH or null Rejected Remark
             WKF_CASE_STATUS_DETAIL.WKF_CASE_ID = WKF_CASE.WKF_CASE_ID and
             WKF_CASE_STATUS_DETAIL.WKF_CASE_STATUS_TYPE_ID in (1617828, 1507210)) ;
             
update WKF_CASE_STATUS_DETAIL 
    set WKF_CASE_STATUS_TYPE_ID = 1507204, 
        RECORD_MODIFIED_BY = 'CCR7506-ClosePHWorkItems',
        RECORD_MODIFIED_DATE = SYSDATE,
        RECORD_MODIFIED_COUNT = RECORD_MODIFIED_COUNT + 1
    where WKF_CASE_STATUS_DETAIL_ID in 
        (select WKF_CASE_STATUS_DETAIL_ID
         from 
             WKF_CASE,
             WKF_CASE_STATUS_DETAIL,
             PURPLE_HEART_REGISTRY
         where
             WKF_CASE.PERSON_ID = PURPLE_HEART_REGISTRY.PERSON_ID and
             WKF_CASE.WKF_CASE_TYPE_ID = 1507491 and --PH Work Items
             (PURPLE_HEART_REGISTRY.STD_REJECTEDREMARKS_ID is not null or PURPLE_HEART_REGISTRY.PH_STATUS_TYPE_ID = 1504903) and --confirmed PH or null Rejected Remark
             WKF_CASE_STATUS_DETAIL.WKF_CASE_ID = WKF_CASE.WKF_CASE_ID and
             WKF_CASE_STATUS_DETAIL.WKF_CASE_STATUS_TYPE_ID in (1617828, 1507210));
             
             
             
	SELECT COUNT(*) FROM 
	   WKF_CASE_STATUS_DETAIL 
	WHERE   RECORD_MODIFIED_BY = 'CCR7506-ClosePHWorkItems';	             

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Closed PH Work Items');
END;
/
SET TERMOUT OFF

SET TERMOUT ON
DEFINE UOW = &1

SET TERMOUT ON
BEGIN
   IF UPPER('&UOW') = 'COMMIT' THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('COMMIT ISSUED!');
      DBMS_OUTPUT.PUT_LINE(' ');
   ELSIF UPPER('&UOW') = '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;
END;
/


SET TERMOUT OFF
-- Reset environment parameters
SET FEEDBACK ON
SET VERIFY ON
SET TERMOUT ON