CREATE OR REPLACE procedure proc_sales is

TYPE sales_rep_id_tp IS TABLE OF NUMBER;
v_sales_rep_id_tp sales_rep_id_tp := sales_rep_id_tp();
TYPE first_name_tp IS TABLE OF VARCHAR2(30);
v_first_name_tp first_name_tp := first_name_tp();
TYPE last_name_tp IS TABLE OF VARCHAR2(30);
v_last_name_tp last_name_tp := last_name_tp();

TYPE l_count_tp IS TABLE OF NUMBER;
v_l_count_tp l_count_tp := l_count_tp();
TYPE l_month_tp IS TABLE OF VARCHAR2(30);
v_l_month_tp l_month_tp := l_month_tp();
TYPE l_year_tp IS TABLE OF VARCHAR2(30);
v_l_year_tp l_year_tp := l_year_tp();

TYPE CUMM_SALES_tp IS TABLE OF NUMBER;
v_CUMM_SALES_tp CUMM_SALES_tp := CUMM_SALES_tp();

TYPE CUMM_COMM_tp IS TABLE OF NUMBER;
v_CUMM_COMM_tp CUMM_COMM_tp := CUMM_COMM_tp();


CURSOR C1 IS
select a.sales_rep_id,a.first_name,a.last_name,
count(product_id),to_char(sold_date,'month'),to_char(sold_date,'yyyy')
from sales_reps a,sales_info b
where active_flag='Y'
and a.SALES_REP_ID=b.SALES_REP_ID
group by a.sales_rep_id,a.first_name,a.last_name,
to_char(b.sold_date,'month'),to_char(b.sold_date,'yyyy');

begin

    OPEN C1;
    
    LOOP
    
         FETCH C1 bulk collect 
into v_sales_rep_id_tp,v_first_name_tp,v_last_name_tp,
v_l_count_tp,v_l_month_tp,v_l_year_tp;
        
          FOR i IN v_first_name_tp.FIRST .. v_first_name_tp.LAST
          LOOP
          v_CUMM_SALES_tp(i) := v_CUMM_SALES_tp(i) + v_l_count_tp(i);
          v_CUMM_COMM_tp(i) := CALC_COMM(v_CUMM_SALES_tp(i));
          END LOOP;
                                      
       
         IF (C1%NOTFOUND)
         THEN
            EXIT;
         END IF;
        
               
    END LOOP;
    
    for i in v_first_name_tp.first .. v_first_name_tp.last loop
    
    dbms_output.put_line('Sales Rep ID is ' || v_sales_rep_id_tp(i));
    dbms_output.put_line('First Name is ' || v_first_name_tp(i));
    dbms_output.put_line('Last Name is ' || v_last_name_tp(i));
    dbms_output.put_line('Monthly Sales ' || v_l_count_tp(i));
    dbms_output.put_line('Month is ' || v_l_month_tp(i));
    dbms_output.put_line('Year is ' || v_l_year_tp(i));
  
    end loop;
    
    FORALL i IN v_first_name_tp.FIRST .. v_first_name_tp.LAST
    insert into monthly_sales_info values 
(v_first_name_tp(i),v_last_name_tp(i),v_l_month_tp(i),
v_l_year_tp(i),v_l_count_tp(i),v_CUMM_SALES_tp(i),v_CUMM_COMM_tp(i));
    COMMIT;
          
 CLOSE C1;

end;