PER French D2

            select per.person_id   pid,
               per.last_name   surname,
               per.first_name   first_name,
               hr_general.decode_lookup('SEX',per.sex)   gender,
               to_char(per.date_of_birth,'YYYY')   year_of_birth,
               --trunc(to_number(months_between(:pc_31dec_m,per.date_of_birth)/12))   age,
               trunc(months_between(:pc_31dec,per.date_of_birth)/12)   age,
               decode(pdf.category,'A','A','B','B','C','C') cotorep_category,
               pdf.dis_information2  previous_cotorep,
               decode(pdf.reason,'OCC_INC','Y','N')        due_to_wa,
               pdf.degree                  disability_rate,
               hr_general.decode_lookup('FR_INVALIDITY_CLASS',pdf.dis_information1)   disability_class,
               pdf.dis_information1   disability_class_code,
               pdf.effective_start_date      disability_start_date,
               pdf.effective_end_date        disability_end_date,
                PER_PERFRD2R_XMLP_PKG.fc_set_job_infoformula(per.person_id) FC_SET_JOB_INFO,
                PER_PERFRD2R_XMLP_PKG.PC_HIRE_YEAR_p PC_HIRE_YEAR,
                PER_PERFRD2R_XMLP_PKG.PC_YEAR_BECAME_PERMANENT_p PC_YEAR_BECAME_PERMANENT,
                PER_PERFRD2R_XMLP_PKG.PC_JOB_TITLE_p PC_JOB_TITLE,
                PER_PERFRD2R_XMLP_PKG.PC_HOURS_TRAINING_p PC_HOURS_TRAINING,
                PER_PERFRD2R_XMLP_PKG.PC_PCS_CODE_p PC_PCS_CODE,
                --PER_PERFRD2R_XMLP_PKG.c_set_unitsformula(decode ( pdf.category , 'A' , 'A' , 'B' , 'B' , 'C' , 'C' ), trunc(to_number(months_between (:pc_31dec_m , per.date_of_birth ) / 12)), pdf.dis_information2, pdf.degree, decode ( pdf.reason , 'OCC_INC' , 'Y' , 'N' ), pdf.dis_information1, per.person_id) FC_SET_UNITS,
                PER_PERFRD2R_XMLP_PKG.c_set_unitsformula(decode ( pdf.category , 'A' , 'A' , 'B' , 'B' , 'C' , 'C' ), trunc(months_between (:pc_31dec , per.date_of_birth ) / 12), pdf.dis_information2, pdf.degree, decode ( pdf.reason , 'OCC_INC' , 'Y' , 'N' ), pdf.dis_information1, per.person_id) FC_SET_UNITS,
                PER_PERFRD2R_XMLP_PKG.PC_UNITS_TOTAL_p PC_UNITS_TOTAL,
                PER_PERFRD2R_XMLP_PKG.PC_UNITS_COEF_p PC_UNITS_COEF,
                PER_PERFRD2R_XMLP_PKG.PC_UNITS_ACTUAL_p PC_UNITS_ACTUAL
            from per_all_people_f per,
               per_disabilities_f     pdf
            where pdf.person_id = per.person_id
            and per.effective_start_date = (select max(effective_start_date)
                                                            from per_all_people_f
                                                            where person_id = per.person_id
                                                            and effective_start_date <= :pc_31dec)
            &pc_disabled_where_clause
            and (pdf.category in ('A','B','C') or
                    pdf.reason = 'OCC_INC' or
                    pdf.dis_information1 in ('CIVIL','MILITARY','MILITARY_EQUIVALENT'))
            and pdf.DIS_INFORMATION_CATEGORY='FR'
            /*order by disability_end_date*/
            order by per.last_name, per.first_name, pdf.effective_end_date