create or replace PROCEDURE proc1 AUTHID current_user AS
       
CURSOR c1 IS
  SELECT DISTINCT col1   as col1_temp
        ,col2  as col2_temp
        ,col3
        ,col4
  FROM  tab1
   WHERE process_dt   IS NULL
     AND col5     = 3 
     AND nvl(col4,'C') <> 'C'
  ORDER by col1_temp, col4
        ;
        
CURSOR c3(v2 INTEGER) IS  
    SELECT distinct col6, col7                                                                                           
    FROM  tab2 WHERE col8 = v2
    ;
    
v1     INTEGER ;
v2   INTEGER ;
v3  INTEGER ; 
v4    INTEGER ; 
v5     INTEGER ; 
                          
v6   INTEGER;
v7 INTEGER;
v8   INTEGER;
v9     INTEGER;
v10 INTEGER;  
error_flag      NUMBER := 0;  /* 0=Normal
                                 1=Abnormal */

v_name        err_log_tbl.name%TYPE := 'Update Order transactions';
v_class       err_log_tbl.class_code%TYPE  := 'ODS';
v_text        err_log_tbl.COMMENT_TEXT%TYPE := null;
v_total       INTEGER := 0 ;

BEGIN

   v_text := 'Program Just Started';
   err_tab(  v_name,'Initial',v_text ,v_class);

FOR c1_rec  IN c1  LOOP

 if c1_rec.col4 = 'I' then

  SELECT col2_temp
  INTO   v2
  FROM   tab3
  WHERE  col3     = c1_rec.col3
  AND    col1_temp = c1_rec.col1_temp
    ;

  v1 := c1_rec.col2_temp;

  UPDATE  tab3
  SET     col2_temp    = c1_rec.col2_temp
  WHERE   col3      = c1_rec.col3
  AND     col1_temp  = c1_rec.col1_temp
    ;
 
  if SQL%rowcount != 1 then
    v_text := 'Error in Updating tab3 record ' || to_char(c1_rec.col1_temp) || '  ' || SQLERRM;
    error_flag := 1;
    exit;
  end if;
    
  pkg1.p1(v2
        ,v1
        ,error_flag
        ,v_text);
      
    IF error_flag > 0 THEN
      exit;
    END IF;                 
    
      
  BEGIN
    v5:= 0;
    
  FOR c3_rec IN c3(v2) LOOP     

    BEGIN
      SELECT  tab2_id 
        INTO  v4
      FROM    tab2  
      WHERE p_id = c3_rec.col6
        AND col2_temp  = v1
      ;
      EXCEPTION when no_data_found then
        v4  := null;
    
    END;        

    IF v4 is not null THEN

      tab2_pkg.del_from_tab2(c3_rec.tab2_id
            ,v4
            ,error_flag
            ,v_text);
  
      if error_flag <> 0 then
        exit;
      end if;         
      
    ELSIF v4 is null THEN

      v6 := tab2_pkg.v6_fac (v1  
                  ,v2  
                  ,c3_rec.tab2_id
                  ,error_flag
                  ,v_text);
                
        IF error_flag <> 0 then
          exit;
        END IF;

      if v6  > 0 then
        
        UPDATE  tab_cl tc
        SET     tab2_ver_id = (SELECT tab2_ver_id
                                 FROM tab2_VER
                                WHERE tab2_id = c3_rec.tab2_id
                                  AND greatest(tc.beg_dt,to_date('12-DEC-99'))
                                        BETWEEN tab2_ver_beg_dt AND tab2_ver_end_dt
                               )
        WHERE  tab2_ver_id in (SELECT tab2_ver_id
                                 FROM tab2_VER
                                WHERE tab2_id = c3_rec.tab2_id
                               )
        ;
    
      end if;   

      tab2_pkg.p1(c3_rec.tab2_id
                ,v2
                ,v1
                ,error_flag
                ,v_text)
                ;
       
        IF error_flag <> 0 then
          exit;
        END IF;
    END IF; /* End of IF v4 is not null */

   
    v5:= v5+ 1 ;

  END LOOP;  
  END ;

    if error_flag <> 0 then
      exit;
    end if;         

  DELETE tab4 WHERE col2_temp = v2;

  DELETE tab6 WHERE col2_temp = v2;
  

  if SQL%rowcount != 1 then
    v_text := 'Error in deleting record ' || to_char(v2) || '  ' || SQLERRM;
    error_flag := 1;
    exit;
  end if;

   UPDATE  tab1
       SET  PROCESS_DT =  trunc(sysdate)
           ,del_col2    = v2
     WHERE col1   = c1_rec.col1_temp
       AND col2       = c1_rec.col2_temp
       AND col3          = c1_rec.col3
       AND col5 = 3
       AND col4       = 'A';

  if SQL%rowcount != 1 then
    v_text := 'Error Found in Update'|| to_char(c1_rec.col1_temp) || '  ' || SQLERRM;
    error_flag := 1;
    exit;
  end if;
  
  elsif c1_rec.col4 = 'R' then 

  UPDATE  tab1
     SET PROCESS_DT =  trunc(sysdate)
   WHERE col1   = c1_rec.col1_temp
     AND col2       = c1_rec.col2_temp
     AND col3          = c1_rec.col3
     AND col5 = 3
     AND col4       = 'R';

 
  if SQL%rowcount != 1 then
    v_text := 'Error Found in Update'|| to_char(c1_rec.col1_temp) || '  ' || SQLERRM;
    error_flag := 1;
    exit;
  end if;

  UPDATE tab6
     SET fac_temp_flag ='N'
   WHERE col2_temp in (SELECT col2_temp
                      FROM tab3
                     WHERE col1_temp = c1_rec.col1_temp
                       AND col3     = c1_rec.col3
                    )
  ;

  end if; 

  if error_flag = 1 then
    exit;
  end if;
  
  v_total := v_total + 1;
  
  IF mod(v_total,100) = 0 then
    v_text  := 'Processed ' || to_char(v_total) || ' succesfully ' ;
    err_tab(v_name ,'Running'  ,substr(v_text,1,120)  ,v_class);
    commit;          
  END IF;   
END LOOP; 

  if error_flag = 0 then
    commit ;
    v_text := 'Complete Processing of ' || to_char(v_total) || ' succesfully ' ;
    err_tab(v_name  ,'Done OK'  ,substr(v_text,1,120)   ,v_class);

  else
    rollback;
    v_text := 'Error Happened' || SQLERRM ;
    err_tab(v_name  ,'Done Err'   ,substr(v_text,1,120)     ,v_class);

  end if;
  
EXCEPTION when OTHERS then 
    rollback;
    v_text := v_text || SQLERRM;
    err_tab(v_name   ,'Done Err'   ,substr(v_text,1,120)   ,v_class);
END;

EXCEPTION when OTHERS then 
    rollback;
    v_text := v_text || SQLERRM;
    err_tab(v_name   ,'Done Err'   ,substr(v_text,1,120)   ,v_class);

   UPDATE  tab1
       SET  PROCESS_DT =  trunc(sysdate)
           ,del_col2    = v2
     WHERE col1   = c1_rec.col1_temp
       AND col2       = c1_rec.col2_temp
       AND col3          = c1_rec.col3
       AND col5 = 3
       AND col4       = 'A';

  if SQL%rowcount != 1 then
    v_text := 'Error Found in Update'|| to_char(c1_rec.col1_temp) || '  ' || SQLERRM;
    error_flag := 1;
    exit;
  end if;

   UPDATE  tab1
       SET  PROCESS_DT =  trunc(sysdate)
           ,del_col2    = v2
     WHERE col1   = c1_rec.col1_temp
       AND col2       = c1_rec.col2_temp
       AND col3          = c1_rec.col3
       AND col5 = 3
       AND col4       = 'A';

  if SQL%rowcount != 1 then
    raise_application_error(-20001, 'update <which one is it> updated '||SQL%rowcount||' rows');
  end if;

if SQL%rowcount != 1 then
    v_text := 'Error in Updating tab3 record ' || to_char(c1_rec.col1_temp) || '  ' || SQLERRM;
    error_flag := 1;
    exit;
  end if;

  if SQL%rowcount != 1 then
    v_text := 'Error in deleting record ' || to_char(v2) || '  ' || SQLERRM;
    error_flag := 1;
    exit;
  end if;

  if error_flag = 0 then
    commit ;
    v_text := 'Complete Processing of ' || to_char(v_total) || ' succesfully ' ;
    err_tab(v_name  ,'Done OK'  ,substr(v_text,1,120)   ,v_class);

  else
    rollback;
    v_text := 'Error Happened' || SQLERRM ;
    err_tab(v_name  ,'Done Err'   ,substr(v_text,1,120)     ,v_class);

  end if;

  

  if SQL%rowcount != 1 then
    raise_application_error(-20001, Error in deleting record ' || to_char(v2) || <some other useful info for logging>);
  end if;

    commit ;
    v_text := 'Complete Processing of ' || to_char(v_total) || ' succesfully ' ;
    err_tab(v_name  ,'Done OK'  ,substr(v_text,1,120)   ,v_class);

if error_flag = 0 then
    commit ;
    v_text := 'Complete Processing of ' || to_char(v_total) || ' succesfully ' ;
    err_tab(v_name  ,'Done OK'  ,substr(v_text,1,120)   ,v_class);

  else
    rollback;
    v_text := 'Error Happened' || SQLERRM ;
    err_tab(v_name  ,'Done Err'   ,substr(v_text,1,120)     ,v_class);

  end if;

tab2_pkg.del_from_tab2(c3_rec.tab2_id
            ,v4
            ,error_flag
            ,v_text);

Procedure proc_name(params)

Begin

Update statements

exception when others then
v_text := Error while update on Prov for Facility ' || to_char(from_prov_id)|| ' : '||SQLERRM;
error_flag :=1 ;

end;