CREATE PROCEDURE REP14 (
    C_DATE DATE)
RETURNS (
    W_TYPE INTEGER,
    REC_1 INTEGER,
    REC_2 INTEGER,
    REC_3 INTEGER,
    REC_4 INTEGER,
    REC_5 INTEGER,
    REC_6 INTEGER,
    REC_7 INTEGER,
    REC_ALL INTEGER)
AS
DECLARE VARIABLE ID_TMP INTEGER;
DECLARE VARIABLE W_STAGE1 INTEGER;
DECLARE VARIABLE W_STAGE5 INTEGER;
DECLARE VARIABLE W_TYPE1 INTEGER;
DECLARE VARIABLE W_TYPE5 INTEGER;
BEGIN
     /**/ 
    id_tmp = gen_id(tmp_id_gen,   1 );
     /**/ 
    insert into tmp_stages (id, w_stage1, w_stage5, w_type1, w_type5)
    select :id_tmp, w1.years, w5.years,
        case
            when w1.years <  1  then  1 
            when w1.years between  1  and  2  then  2 
            when w1.years between  3  and  4  then  3 
            when w1.years between  5  and  9  then  4 
            when w1.years between  10  and  14  then  5 
            when w1.years between  15  and  19  then  6 
            when w1.years >=  20  then  7 
            else  0 
        end,
        case
            when w5.years <  1  then  1 
            when w5.years between  1  and  2  then  2 
            when w5.years between  3  and  4  then  3 
            when w5.years between  5  and  9  then  4 
            when w5.years between  10  and  14  then  5 
            when w5.years between  15  and  19  then  6 
            when w5.years >=  20  then  7 
            else  0 
        end
    from workers w
    left join get_worker_stage1(w.id_people, :c_date) w1 on  1  =  1 
    left join get_worker_stage5(w.id_people, :c_date) w5 on  1  =  1 
    where w.w_status in ( 1 );
     /**/ 
    w_type =  1 ;
    while (w_type <  8 ) do
    begin
        select count(*) from tmp_stages where id = :id_tmp and w_type1 = :w_type
            and w_type5 =  1  into :rec_1;
        select count(*) from tmp_stages where id = :id_tmp and w_type1 = :w_type
            and w_type5 =  2  into :rec_2;
        select count(*) from tmp_stages where id = :id_tmp and w_type1 = :w_type
            and w_type5 =  3  into :rec_3;
        select count(*) from tmp_stages where id = :id_tmp and w_type1 = :w_type
            and w_type5 =  4  into :rec_4;
        select count(*) from tmp_stages where id = :id_tmp and w_type1 = :w_type
            and w_type5 =  5  into :rec_5;
        select count(*) from tmp_stages where id = :id_tmp and w_type1 = :w_type
            and w_type5 =  6  into :rec_6;
        select count(*) from tmp_stages where id = :id_tmp and w_type1 = :w_type
            and w_type5 =  7  into :rec_7;
        rec_all = :rec_1 + rec_2 + rec_3 + rec_4 + rec_5 + rec_6 + rec_7;
         /**/ 
        w_type = w_type +  1 ;
         /**/ 
        suspend;
    end
     /**/ 
    delete from tmp_stages where id = :id_tmp;
END

    for select
            s.w_type1,
            count(s1.id),
            count(s2.id),
            count(s3.id),
            count(s4.id),
            count(s5.id),
            count(s6.id),
            count(s7.id)
        from tmp_stages s
        left join tmp_stages s1 on s1.id = :id_tmp and s1.w_type1 = s.w_type1
            and s1.w_type5 =  1 
        left join tmp_stages s2 on s2.id = :id_tmp and s2.w_type1 = s.w_type1
            and s2.w_type5 =  2 
        left join tmp_stages s3 on s3.id = :id_tmp and s3.w_type1 = s.w_type1
            and s3.w_type5 =  3 
        left join tmp_stages s4 on s4.id = :id_tmp and s4.w_type1 = s.w_type1
            and s4.w_type5 =  4 
        left join tmp_stages s5 on s5.id = :id_tmp and s5.w_type1 = s.w_type1
            and s5.w_type5 =  5 
        left join tmp_stages s6 on s6.id = :id_tmp and s6.w_type1 = s.w_type1
            and s6.w_type5 =  6 
        left join tmp_stages s7 on s7.id = :id_tmp and s7.w_type1 = s.w_type1
            and s7.w_type5 =  7 
        where s.id = :id_tmp
        group by s.w_type1
        into :w_type, :rec_1,  :rec_2, :rec_3, :rec_4, :rec_5, :rec_6, :rec_7
    do suspend;