CREATE OR REPLACE PROCEDURE prc_mine IS

     appidNUMBER(10);
     appname VARCHAR2(250);
     v_id NUMBER(10);
     v_new NUMBER(10);
     v_new_name VARCHAR2(250);
     v_old DATE;
     v_dt DATE;
     cnt NUMBER;

     CURSOR c_state_compare IS
     SELECT current_status.app_id,
            current_status.app_name,
            updateable.app_state_id old_state_id,
            current_status.app_state_id new_state_id,
            current_status.app_state_name new_state_name,
            updateable.app_state_begin begin_date,
            current_status.app_state_dt state_date
     FROM (SELECT h.app_id, h.app_state_id, h.app_state_begin
           FROM (SELECT app_id, MAX(app_state_begin) app_state_begin
                 FROM gt_temp h
                 WHERE h.app_state_end IS NULL
                 GROUP BY app_id) 
	   open_status, gt_temp h
           WHERE h.app_id = open_status.app_id
             AND h.app_state_begin = open_status.app_state_begin) 
     updateable,
          (SELECT a.app_id, a.app_name, sc.app_state_id, sn.app_state_name, sc.app_state_dt
           FROM t_app_state_current sc,
                t_application a,
                t_application_state sn
           WHERE a.app_id = sc.app_id
             AND sc.app_state_id = sn.app_state_id) 
     current_status
     WHERE updateable.app_id = current_status.app_id;

BEGIN

     INSERT INTO gt_temp
     SELECT *
     FROM table_mine;

     cnt := 0;
     OPEN c_state_compare;
     LOOP
          FETCH c_state_compare INTO v_app_id,
                                     appname,
                                     v_id,
                                     v_new,
                                     v_new_name,
                                     v_old,
                                     v_dt;

          EXIT WHEN c_state_compare%NOTFOUND;

          IF v_id != v_new  and v_old != v_dt THEN
               UPDATE gt_temp
               SET app_state_end = v_dt
               WHERE app_id = v_app_id
                 AND app_state_begin = v_old
                 AND app_state_id = v_id;

               INSERT INTO gt_temp
               (app_id, app_name, app_state_id, app_state_name, app_state_begin, app_state_end)
               VALUES
               (v_app_id, appname, v_new, v_new_name, v_dt, NULL);

               cnt := cnt + 1;
          END IF;

     END LOOP;
     CLOSE c_state_compare;

     EXECUTE IMMEDIATE
     'TRUNCATE TABLE table_mine';

     INSERT INTO table_mine
     SELECT *
     FROM gt_temp;

     COMMIT;

     dbms_output.put_line(cnt || ' RECORDS UPDATED');
END prc_mine;