CREATE OR REPLACE PROCEDURE COLNG.GetCOLreport
-- ( 
-- p_date_from IN varchar2,
--                                        p_date_to IN varchar2
--                                        p_metrics OUT TYPES.cursorType
--                                        )
  IS
    v_msg  varchar2(500);
    v_date_from date;
    v_date_to date;
    
     BEGIN

     v_date_from := to_date('01/01/2010','mm/dd/yyyy');
     v_date_to := to_date('11/01/2011','mm/dd/yyyy');
     
     execute immediate 'truncate table temp_COL_timeline';

           for each_day in (
                 with day_by  as
                      ( select v_date_from + rownum -1 as timeline
                            from raw_cascade_header 
                            where rownum <= 
                                   v_date_to - v_date_from+1
                      )
                 select db.*
                 from day_by db
                   ) loop
                

                    insert into temp_COL_timeline
                                    (TIMELINE, UPC, INITIAL_DT, INITIAL_DTG, cascadeCOR_DT, report_NUM, cascadeNUM)
                          select each_day.timeline, a.UPC,a.initial_dt, a.initial_dtg, a.cascadecor_dt, a.cascadenum, a.cascadenum
                          from raw_cascade_header A
                          --VW_cascade_FOR_TIMELINE a
                          where (a.initial_dt < to_date(to_char(each_day.timeline, 'yyyymmdd')||'2359','yyyymmddhh24mi') and (a.cascadecor_dt is null or a.cascadecor_dt > to_date(to_char(each_day.timeline, 'yyyymmdd')||'2359','yyyymmddhh24mi'))
                                     OR
                                     (a.total_updates=1 and a.current_update_no in (101,102) and trunc(a.initial_dt) = trunc(each_day.timeline))
                                     OR
                                     (trunc(a.initial_dt)=trunc(a.cascadecor_dt) and a.initial_dt < to_date(to_char(each_day.timeline, 'yyyymmdd')||'2359','yyyymmddhh24mi') )
                                    
                                     )
                           and UPC in (select UPC from daily_ship)
--                           and exists (select 1
--                           from raw_cascade_version where UPC=a.UPC and initial_dtg=a.initial_dtg
--                           and cascadenum=a.cascadenum and severity_cd in (3,4)) 
                           ;
                                                      
           
           end loop;
           
           for rec in (select* from temp_COL_timeline) loop
             If  trunc(rec.initial_dt)=trunc(rec.cascadecor_dt) and trunc(rec.initial_dt)=rec.timeline Then
                  UPDATE temp_COL_timeline A
                   SET A.LAST_SEVERITY = 
                                    (SELECT max(SEVERITY_CD)
                                     FROM raw_cascade_version
                         WHERE  UPC=REC.UPC and
                                     initial_dtg=rec.initial_dtg and
                                     cascadenum=rec.cascadenum
                            AND (report_update_no=101 or
                                     report_update_no=102)
                                     )
                        WHERE a.UPC = rec.UPC  and a.initial_dtg=rec.initial_dtg
                           and a.cascadenum=rec.cascadenum
                          and a.timeline=rec.timeline;
             ElsIf rec.cascadecor_dt is null or rec.cascadecor_dt > TO_DATE(TO_CHAR(rec.TIMELINE, 'YYYYMMDD')||'2359', 'YYYYMMDDHH24MI') Then
             --  v_msg := 'cascadenum '||rec.report_num||' timeline '||rec.timeline;
               
              -- v_date := null;
--           SELECT MAX(report_update_dt) into v_date
--            FROM report_version
--             WHERE report_id=rec.report_id
--              AND report_update_dt <= TO_DATE(TO_CHAR(rec.TIMELINE, 'YYYYMMDD')||'2359', 'YYYYMMDDHH24MI');
                             
                     UPDATE temp_COL_timeline  A
                     SET
                      A.LAST_MSG_DT = 
                            (SELECT MAX(msg_date) 
                             FROM raw_cascade_version
                             WHERE UPC=REC.UPC and
                                     initial_dtg=rec.initial_dtg and
                                     cascadenum=rec.cascadenum
                             AND msg_date <= TO_DATE(TO_CHAR(rec.TIMELINE, 'YYYYMMDD')||'2359', 'YYYYMMDDHH24MI')
                          )
                          WHERE a.UPC = rec.UPC  and a.initial_dtg=rec.initial_dtg
                           and a.cascadenum=rec.cascadenum
                          and a.timeline=rec.timeline;
-- v_msg := 'cascadenum '||rec.report_num||' timeline '||rec.timeline;
 
                   UPDATE temp_COL_timeline A
                   SET A.LAST_SEVERITY = 
                                    (SELECT MAX(SEVERITY_CD)
                                     FROM raw_cascade_version
                         WHERE   UPC=REC.UPC and
                                     initial_dtg=rec.initial_dtg and
                                     cascadenum=rec.cascadenum
                            AND msg_date= A.LAST_MSG_DT
                                     )
                            WHERE a.UPC = rec.UPC  and a.initial_dtg=rec.initial_dtg
                           and a.cascadenum=rec.cascadenum
                          and a.timeline=rec.timeline                   ;
                      --     v_msg := 'cascadenum '||rec.report_num||' timeline '||rec.timeline;
              End If; -- if report was still open on that day             
           end loop;
              commit;
                INSERT INTO
                 TEST_COL_TIMELINE (TIMELINE,  UPC  ,  INITIAL_DTG,  cascadeNUM  ,  INITIAL_DT ,
                                              cascadeCOR_DT,  report_NUM ,  LAST_MSG_DT,  LAST_SEVERITY)
                (SELECT* FROM TEMP_COL_TIMELINE);
                COMMIT;
                
           --  open p_metrics for
          --   select* from temp_COL_timeline;
--                select A.*, B.SHIP_TYPE, B.HOMEPORT_COAST
--                 from temp_COL_timeline A
--                JOIN                    daily_ship b on a.UPC=b.UPC
--                WHERE LAST_SEVERITY IN (3,4)
--                AND SHIP_TYPE='FFG'
--                AND HOMEPORT_COAST='L'
--                ORDER BY SHIP_TYPE ;
              
                
--                SELECT A.TIMELINE, A.LAST_SEVERITY, COUNT(*)
--                  FROM temp_COL_timeline A
--                  join daily_ship b on a.UPC=b.UPC
--                  WHERE   
--                      b.HOMEPORT_COAST = 'L'
--                           AND b.SHIP_TYPE = 'DDG'
-- AND 
--                         a.LAST_SEVERITY IN (3,4)
--                          GROUP BY A.TIMELINE, A.LAST_SEVERITY
--                            ORDER BY 1;
 
  EXCEPTION
     WHEN OTHERS THEN
         v_msg := substr(sqlerrm, 1, 500);
  END GetCOLreport;
/


I am creating raw_cascade_header and raw_cascade_version with the below procedures

DROP TABLE COLNG.RAW_cascade_HEADER;

CREATE TABLE COLNG.RAW_cascade_HEADER
(
  SHIP_TYPE          VARCHAR2(10 BYTE),  HULL_NUM_ID        VARCHAR2(10 BYTE),
  UNIT_ID            VARCHAR2(12 BYTE),  UPC                VARCHAR2(5 BYTE),
  INITIAL_DTG        VARCHAR2(12 BYTE),  cascadeNUM             CHAR(7),
  CURRENT_UPDATE_NO  NUMBER(3),  cascadeCOR_DT          DATE,
  IS_OPEN            NUMBER(1),  IS_CANCELLED       NUMBER(1),
  INITIAL_DT         DATE,
  TOTAL_UPDATES NUMBER(5)
  
);
        
        insert into raw_cascade_header
        (ship_type, hull_num_id, unit_id,
         initial_dt, cascadenum)
        select a.unit_type, a.hull_number, a.unit_type||' '|| a.hull_number,
         to_date(a.initial_date,'YYYY-MM-DD HH24:MI:SS') , 
        a.cascadenum
        from rawtrms_report_2 a
        group by a.unit_type, a.hull_number, a.unit_type||' '|| a.hull_number,
         to_date(a.initial_date,'YYYY-MM-DD HH24:MI:SS') , 
        a.cascadenum;
        
        COMMIT;
                update raw_cascade_header
        set initial_dtg=to_char(initial_dt, 'yyyymmddhh24mi');
        
        update raw_cascade_header a
        set a.UPC = (select UPC from daily_ship where ship_type=a.ship_type and ship_nbr=a.hull_num_id);
        
        delete from raw_cascade_header where UPC is null;


        CREATE TABLE RAW_cascade_VERSION as
                select a.unit_type, a.hull_number,
                    to_date(a.initial_date,'YYYY-MM-DD HH24:MI:SS') as initial_date , 
        a.cascadenum, to_date(a.msg_date,'YYYY-MM-DD HH24:MI:SS') as msg_date,
        a.cascadecat , a.updatenum
        from rawtrms_report_2 a
        group by 
                         a.unit_type, a.hull_number,
                    to_date(a.initial_date,'YYYY-MM-DD HH24:MI:SS')  , 
        a.cascadenum, to_date(a.msg_date,'YYYY-MM-DD HH24:MI:SS') ,
        a.cascadecat   , a.updatenum
        ;
ALTER TABLE RAW_cascade_VERSION ADD
(INITIAL_DTG VARCHAR2(12),
UPC VARCHAR2(5)
);

UPDATE RAW_cascade_VERSION SET
(INITIAL_DTG)=TO_CHAR(INITIAL_DATE, 'YYYYMMDDHH24MI');

        update raw_cascade_version a
        set a.UPC = (select UPC from daily_ship where ship_type=a.unit_type and ship_nbr=a.hull_number);
        
        
   delete from raw_cascade_version where UPC is null;
   commit;
   
   delete from raw_cascade_header where initial_dt < '01-jan-2010';
   delete from raw_cascade_version where initial_date < '01-jan-2010';
   
   alter table raw_cascade_version add
   (report_update_no number(3));
   
   update raw_cascade_version
   set report_update_no=
    (cascadeE WHEN UPDATENUM= 'CA' THEN 102 
                                        WHEN UPDATENUM='CO' THEN 101
                                        ELSE TO_NUMBER(UPDATENUM)
                                        END);
 alter table raw_cascade_version add
   (severity_cd number(1));
   
   -- check for invalid severity before running this update
      update raw_cascade_version
   SET SEVERITY_CD =TO_NUMBER(cascadeCAT)
WHERE cascadeCAT IN('1','2','3','4');
COMMIT;


CREATE INDEX COLNG.PK_RAW_cascade_HEAD ON COLNG.RAW_cascade_HEADER
(UPC, INITIAL_DTG, cascadeNUM)
LOGGING
NOPARALLEL;


CREATE INDEX COLNG.PK_RAW_cascade_VERSION ON COLNG.RAW_cascade_VERSION
(UPC, INITIAL_DTG, cascadeNUM, report_UPDATE_NO)
LOGGING
NOPARALLEL;

  UPDATE RAW_cascade_HEADER a
          set a.current_update_no =
               (select max(report_UPDATE_NO)
                 from RAW_cascade_VERSION
                 where UPC = a.UPC and initial_dtg = a.initial_dtg and cascadenum = a.cascadenum)
         ;
COMMIT;
          
        UPDATE RAW_cascade_HEADER a
          set a.cascadecor_dt =
               (select MSG_DATE
                 from RAW_cascade_VERSION
                 where UPC = a.UPC and initial_dtg = a.initial_dtg and cascadenum = a.cascadenum and report_UPDATE_NO=101
                )
          ;

        UPDATE RAW_cascade_HEADER a
          set a.cascadecor_dt =
               (select MSG_DATE
                 from RAW_cascade_VERSION
                 where UPC = a.UPC and initial_dtg = a.initial_dtg and cascadenum = a.cascadenum and report_UPDATE_NO=102
                )
         WHERE A.cascadeCOR_DT IS NULL ;

               UPDATE RAW_cascade_HEADER a
          set  a.is_open =0
          where 
           exists
               (select 1
                 from RAW_cascade_VERSION
                 where UPC = a.UPC and initial_dtg = a.initial_dtg and cascadenum = a.cascadenum and
                           report_UPDATE_NO IN (101,102)) ;    

   update raw_cascade_header a
   set a.total_updates=(select count(*) from raw_cascade_version
            where UPC=A.UPC AND INITIAL_DTG=A.INITIAL_DTG AND cascadeNUM=A.cascadeNUM);
            COMMIT;