
--
-- Script to delete the closed items from the work flow tables
-- Deletes the data from below tables.
-- WKF_CASE_COMMENTS, WKF_CASE_ASSIGNMENT, WKF_CASE_STATUS_DETAIL and WKF_CASE
-- DELETES FROM WKF_CASE_COMMENTS, WKF_CASE_ASSIGNMENT, WKF_CASE_STATUS_DETAIL based on the closed status
-- Issues commit after delete from each table.
-- finally deletes from the WKF_CASE tale if there is no corresponding entry from the status table
--

SET SERVEROUTPUT ON
SET FEEDBACK ON
SET TERMOUT ON
SET SERVEROUTPUT ON


DECLARE
   v_numdays   NUMBER := &1;
BEGIN
   DELETE FROM WKF_CASE_COMMENTS
         WHERE WKF_CASE_ID IN (
                  SELECT WKF_CASE_ID
                    FROM WKF_CASE_STATUS_DETAIL
                   WHERE WKF_CASE_STATUS_TYPE_ID = 1507204           --CLOSED
                     AND (SYSDATE - RECORD_MODIFIED_DATE) > V_NUMDAYS);
					 
 DBMS_OUTPUT.PUT_LINE('# of rows deleted from wkf_case_comments: ' ||    SQL%ROWCOUNT);					 
   COMMIT;
DBMS_OUTPUT.PUT_LINE('# COMMITED wkf_case_comments ');
   

   DELETE FROM WKF_CASE_ASSIGNMENT
         WHERE WKF_CASE_ID IN (
                  SELECT WKF_CASE_ID
                    FROM WKF_CASE_STATUS_DETAIL
                   WHERE WKF_CASE_STATUS_TYPE_ID = 1507204            --CLOSED
                     AND (SYSDATE - RECORD_MODIFIED_DATE) > V_NUMDAYS);
					 
DBMS_OUTPUT.PUT_LINE('# of rows deleted from wkf_case_assignment: ' ||    SQL%ROWCOUNT);
   COMMIT;
DBMS_OUTPUT.PUT_LINE('COMMITED wkf_case_assignment ');

   DELETE FROM WKF_CASE_STATUS_DETAIL
         WHERE WKF_CASE_STATUS_TYPE_ID = 1507204                      --CLOSED
           AND (SYSDATE - RECORD_MODIFIED_DATE) > V_NUMDAYS;

 DBMS_OUTPUT.PUT_LINE('# of rows deleted from wkf_case_status_detail: ' ||    SQL%ROWCOUNT);
 COMMIT;
DBMS_OUTPUT.PUT_LINE('COMMITED wkf_case_status_detail ');

-- DELETE FROM WORK CASE IF THERE IS NO ENTRY IN THE  WKF_CASE_STATUS_DETAIL
   DELETE FROM WKF_CASE 
         WHERE WKF_CASE_ID IN (SELECT WC.WKF_CASE_ID
                                 FROM WKF_CASE WC LEFT OUTER JOIN WKF_CASE_STATUS_DETAIL WS ON (WC.WKF_CASE_ID =
                                                                                                   WS.WKF_CASE_ID
                                                                                               )
                                WHERE WS.WKF_CASE_ID IS NULL);
								
 DBMS_OUTPUT.PUT_LINE('# of rows deleted from wkf_case : ' ||    SQL%ROWCOUNT);
   COMMIT;
DBMS_OUTPUT.PUT_LINE('COMMITED wkf_case END ');
END;
/