/*******************************************************************************
   AHD#                          INFRA#
   Database Name :
   Schema   Name :

   This script will remove the duplicate records from the table
   cnsmr_notf_msg.

   Modification history:
   Rev#  Date     Author    Description
   ----- -------- --------- ----------------------------------------------------
   1.0  02/13/09 PMishra 501647 Initial Code.
*******************************************************************************/

SET SERVEROUTPUT ON SIZE 1000000
SET TERM ON
SET ECHO OFF

-- do not change the COLUMN line
COLUMN spool_file_name  NEW_VALUE xspool_file_name noprint format a1 trunc

SELECT sys_context('USERENV', 'DB_NAME') || '_'
       || 'date_fix_template_'
       || TO_CHAR(SYSDATE, 'YYYY_MM_DD_HH24_MI_SS') spool_file_name
  FROM dual;


-- do not change the SPOOL line
SPOOL &&xspool_file_name

SET ECHO ON
SELECT 'Start time ', TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS')
  FROM DUAL
/
-- Put preliminary queries and counts here.
DECLARE
   CURSOR cur_cnsmr_notf
      IS
      SELECT cnm.cnsmr_id,
             cnm.tran_ref_id, 
             cnm.snd_tran_Date
        FROM CNSMR_NOTF_MSG cnm,
             consumer c, 
             (
             SELECT MIN(c.create_date) create_date , 
                    cnm.tran_ref_id, 
                    cnm.snd_tran_Date
               FROM CNSMR_NOTF_MSG cnm,
                    consumer c,
                   (SELECT COUNT(*), 
                           tran_ref_id||'-'||to_char(snd_tran_date,'MMDDYYYYHH24MISS') tran_ref_snd 
                      FROM CNSMR_NOTF_MSG
                    WHERE tran_ref_id IS NOT NULL
                    GROUP BY tran_ref_id||'-'||to_char(snd_tran_date,'MMDDYYYYHH24MISS')
                    HAVING COUNT(*) > 1) t
              WHERE cnm.tran_ref_id||'-'||to_char(cnm.snd_tran_date,'MMDDYYYYHH24MISS') = t.tran_ref_snd
               AND cnm.cnsmr_id = c.cnsmr_id
         GROUP BY  cnm.tran_ref_id, cnm.snd_tran_Date 
           ) t1
    WHERE cnm.cnsmr_id = c.cnsmr_id
      AND cnm.tran_ref_id = t1.tran_ref_id
      AND cnm.snd_tran_Date = t1.snd_tran_Date
      AND c.create_date <> t1.create_date;         
      
   CURSOR cur_cnsmr_diff_notf
      IS
      SELECT cnm.cnsmr_id,
             cnm.cnsmr_notf_msg_id,
             cnm.tran_ref_id, 
             cnm.snd_tran_Date
        FROM CNSMR_NOTF_MSG cnm,             
             (
             SELECT MIN(cnm.cnsmr_notf_msg_id) cnsmr_notf_msg_id , 
                    cnm.tran_ref_id, 
                    cnm.snd_tran_Date
               FROM CNSMR_NOTF_MSG cnm,
                   (SELECT COUNT(*), 
                           tran_ref_id||'-'||to_char(snd_tran_date,'MMDDYYYYHH24MISS') tran_ref_snd 
                      FROM CNSMR_NOTF_MSG
                    WHERE tran_ref_id IS NOT NULL 
                    GROUP BY tran_ref_id||'-'||to_char(snd_tran_date,'MMDDYYYYHH24MISS')
                    HAVING COUNT(*) > 1) t
              WHERE cnm.tran_ref_id||'-'||to_char(cnm.snd_tran_date,'MMDDYYYYHH24MISS') = t.tran_ref_snd              
         GROUP BY  cnm.tran_ref_id, cnm.snd_tran_Date 
           ) t1
    WHERE cnm.tran_ref_id = t1.tran_ref_id
      AND cnm.snd_tran_Date = t1.snd_tran_Date
      AND cnm.cnsmr_notf_msg_id <> t1.cnsmr_notf_msg_id;      
               
   TYPE notf IS TABLE OF cur_cnsmr_notf%ROWTYPE
        INDEX BY PLS_INTEGER;
   TYPE diff_notf IS TABLE OF cur_cnsmr_diff_notf%ROWTYPE
        INDEX BY PLS_INTEGER;
        
   lv_notf          notf;     
   lv_diff_notf     diff_notf;            
   lv_limit         NUMBER := 500000;           
   lv_prcs_log_id   process_log_a.prcs_log_id%TYPE;
   lc_name          VARCHAR2(30) := 'PRC_UPD_CNSMR_NOTF_MSG';
   lv_notf_upd_cnt  NUMBER := 0;
   lv_status        gv.status;
  
BEGIN
   
   pkg_debug.prc_debug(lc_name,'Start',5);

   pkg_proc_log.prc_process_log_start
      (lv_prcs_log_id,
       lc_name,
       pkg_proc_log.gc_call_type_gui,
       '<'
       );

   DELETE FROM cnsmr_notf_msg cnm
    WHERE cnm.tran_ref_id IS NULL
       OR cnm.snd_tran_date IS NULL; 
   
   OPEN cur_cnsmr_notf;
   LOOP
        FETCH cur_cnsmr_notf 
            BULK COLLECT INTO lv_notf LIMIT lv_limit;

        FOR indx IN 1 .. lv_notf.COUNT 
        LOOP
        
           DELETE FROM cnsmr_notf_msg cnm
            WHERE cnm.tran_ref_id = lv_notf(indx).tran_ref_id
              AND cnm.snd_tran_date = lv_notf(indx).snd_tran_date
              AND cnm.cnsmr_id = lv_notf(indx).cnsmr_id;           
            
           lv_notf_upd_cnt := lv_notf_upd_cnt + 1;
                                       
        END LOOP;
        
        COMMIT;

        EXIT WHEN lv_notf.COUNT < lv_limit;

   END LOOP;
     
   CLOSE cur_cnsmr_notf;

   OPEN cur_cnsmr_diff_notf;
   LOOP
        FETCH cur_cnsmr_diff_notf 
            BULK COLLECT INTO lv_diff_notf LIMIT lv_limit;

        FOR indx IN 1 .. lv_diff_notf.COUNT 
        LOOP
        
           DELETE FROM cnsmr_notf_msg cnm
            WHERE cnm.tran_ref_id = lv_diff_notf(indx).tran_ref_id
              AND cnm.snd_tran_date = lv_diff_notf(indx).snd_tran_date
              AND cnm.cnsmr_notf_msg_id = lv_diff_notf(indx).cnsmr_notf_msg_id
              AND cnm.cnsmr_id = lv_diff_notf(indx).cnsmr_id;           
            
           lv_notf_upd_cnt := lv_notf_upd_cnt + 1;
                                       
        END LOOP;
        
        COMMIT;

        EXIT WHEN lv_diff_notf.COUNT < lv_limit;

   END LOOP;
     
   CLOSE cur_cnsmr_diff_notf;
   
   pkg_proc_log.prc_process_log_end
      (lv_prcs_log_id,
       iv_prcs_usr_msg  =>
          'cnsmr_notf_msg records updated  =>'   || lv_notf_upd_cnt
       || '<'
      );

   pkg_debug.prc_debug(lc_name,
      '<lv_notf_upd_cnt =>' || lv_notf_upd_cnt || '<', 5);


EXCEPTION
   WHEN OTHERS THEN
   
      pkg_proc_log.prc_process_log_end
         (lv_prcs_log_id,
          pkg_proc_log.gc_proc_status_abend,
          TO_CHAR(sqlcode) ||':'||sqlerrm||'<'
         );
      pkg_debug.prc_debug (lc_name, TO_CHAR(sqlcode) ||':'||sqlerrm||'<', 5);
      RAISE; 

END;
/
-- Put post fix queries and counts here.

SELECT 'Stop time ', TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS')
  FROM DUAL
/
SPOOL OFF
SET ECHO OFF