--
-- Script to close the older worklist items if there is a newer worklist item
-- for the same person , same case type and same error message text
-- Query first gets the list of max(wkf_case_id), person_id, error_message_text for all the new status
-- case items. Then join this with work case and work case status table with person , error text and error 
-- case type by eliminating the max case id(latest case record).
--


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

UPDATE WKF_CASE_STATUS_DETAIL
SET    WKF_CASE_STATUS_TYPE_ID = 1507204
WHERE  WKF_CASE_ID            IN
                                  ( SELECT wc1.wkf_case_id
                                  FROM    wkf_case wc1
                                          INNER JOIN wkf_case_status_detail ws1
                                          ON
                                                  (
                                                          wc1.wkf_case_id             = ws1.wkf_case_id
                                                      AND ws1.wkf_case_status_type_id =1507210
                                                  )
                                          INNER JOIN
                                                  (SELECT  MAX (wc.wkf_case_id) AS caseid  ,
                                                           person_id            AS personid,
                                                           wkf_case_type_id     AS casetype,
                                                           error_message_text   AS errortext
                                                  FROM     wkf_case wc,
                                                           wkf_case_status_detail ws
                                                  WHERE    wc.wkf_case_id             = ws.wkf_case_id
                                                       AND ws.wkf_case_status_type_id = 1507210
                                                  GROUP BY person_id       ,
                                                           wkf_case_type_id,
                                                           error_message_text
                                                  HAVING   COUNT (wc.wkf_case_id) > 1
                                                  ) dups
                                          ON
                                                  (
                                                          wc1.person_id          =dups.personid
                                                      AND wc1.wkf_case_type_id   = dups.casetype
                                                      AND wc1.error_message_text = dups.errortext
                                                      AND ws1.wkf_case_id       <> dups.caseid
                                                  )
                                  );
								  
								  
ROLLBACK;