Query: People Assignment Salary - Peer Avg

select     /*+ leading(sal.day) */
    percentile_cont (0.5)
    within group (order by sal.salary)         "Salary",
    sal.currency_code                          "Currency",
    round (
    avg (
    (sal.salary
    / case
         when sal.grade_mid_value = 0 then 1
         else sal.grade_mid_value
      end)
    * 100),
    2)                                          "COMPARATIO",
    trunc (
   avg (
   case
      when sal.salary <= sal.grade_min
      then
         0
      when sal.salary >= sal.grade_max
      then
         5
      when sal.salary <= (sal.grade_mid_value + sal.grade_min) / 2
      then
         1
      when sal.salary < sal.grade_mid_value
      then
         2
      when sal.salary >= (sal.grade_mid_value + sal.grade_max) / 2
      then
         4
      when sal.salary >= sal.grade_mid_value
      then
         3
    end),
    1)                                            "QUARTILE",                        -- lookup: ben_cwb_quartile
    round (
    avg (
    ( (sal.salary - sal.grade_min)
    / case
         when (sal.grade_max - sal.grade_min) = 0 then 1
         else (sal.grade_max - sal.grade_min)
      end)
    * 100),
    2)
range_position
from
    (select day.effective_date effective_start_date,
    add_months (day.effective_date, 12) - 1 effective_end_date,
    asg.business_group_id,
    asg.assignment_id,
    asg.job_id job_id,
    loc.country country,
    asg.pay_basis_id,
    asg.grade_id,
    pgr.rate_id,
    hri_bpl_abv.calc_abv (asg.assignment_id,
            asg.business_group_id,
            'HEAD',
            pro.change_date)  headcount,
                 case
                    when ppb.pay_basis = 'HOURLY'
                         or fnd_profile.value ('PER_ANNUAL_SALARY_ON_FTE') =
                               'N'
                    then
                       (ppb.pay_annualization_factor * pro.proposed_salary_n)
                    else
                       (ppb.pay_annualization_factor * pro.proposed_salary_n)
                       / case
                            when per_saladmin_utility.get_fte_factor (
                                    asg.assignment_id,
                                    day.effective_date) = 0
                            then
                               1
                            else
                               per_saladmin_utility.get_fte_factor (
                                  asg.assignment_id,
                                  day.effective_date)
                         end
                 end
                    salary,
                 pgr.currency_code grade_currency,
                 case
                    when     pet.input_currency_code is not null
                         and pgr.currency_code is not null
                         and pgr.currency_code != pet.input_currency_code
                    then
                         per_saladmin_utility.get_currency_rate (
                            pgr.currency_code,
                            pet.input_currency_code,
                            day.effective_date,
                            asg.business_group_id)
                       * pgr.minimum
                       * ppb.grade_annualization_factor
                    else
                       pgr.minimum * ppb.grade_annualization_factor
                 end
                    grade_min,
                 case
                    when     pet.input_currency_code is not null
                         and pgr.currency_code is not null
                         and pgr.currency_code != pet.input_currency_code
                    then
                         per_saladmin_utility.get_currency_rate (
                            pgr.currency_code,
                            pet.input_currency_code,
                            day.effective_date,
                            asg.business_group_id)
                       * pgr.maximum
                       * ppb.grade_annualization_factor
                    else
                       pgr.maximum * ppb.grade_annualization_factor
                 end
                    grade_max,
                 case
                    when     pet.input_currency_code is not null
                         and pgr.currency_code is not null
                         and pgr.currency_code != pet.input_currency_code
                    then
                         per_saladmin_utility.get_currency_rate (
                            pgr.currency_code,
                            pet.input_currency_code,
                            day.effective_date,
                            asg.business_group_id)
                       * pgr.mid_value
                       * ppb.grade_annualization_factor
                    else
                       pgr.mid_value * ppb.grade_annualization_factor
                 end
                    grade_mid_value,
                 pet.input_currency_code currency_code
            from per_all_assignments_f asg,
            per_all_people_f ppf,
                 per_pay_bases ppb,
                 per_pay_proposals pro,
                 pay_input_values_f piv,
                 pay_element_types_f pet,
                 hr_locations_all loc,
                 pay_grade_rules_f pgr,
                 (select trunc (sysdate) effective_date from dual
                  union all
                  select add_months (trunc (sysdate), -12) effective_date
                    from dual
                  union all
                  select add_months (trunc (sysdate), -24) effective_date
                    from dual
                  union all
                  select add_months (trunc (sysdate), -36) effective_date
                    from dual
                  union all
                  select add_months (trunc (sysdate), -48) effective_date
                    from dual) day
           where day.effective_date between asg.effective_start_date
                                        and asg.effective_end_date
                 and asg.job_id is not null
                 and asg.person_id = ppf.person_id
                 and asg.grade_id = pgr.grade_or_spinal_point_id
                 and ppb.rate_id = pgr.rate_id
                 and asg.assignment_id = pro.assignment_id
                 and pro.change_date =
                        (select max (change_date)
                           from per_pay_proposals pro2
                          where     pro2.assignment_id = pro.assignment_id
                                and pro2.change_date <= day.effective_date
                                and pro2.approved = 'Y')
                 and asg.pay_basis_id = ppb.pay_basis_id
                 and ppb.input_value_id = piv.input_value_id
                 and piv.element_type_id = pet.element_type_id
                 and loc.location_id = asg.location_id
                 and pro.change_date between piv.effective_start_date
                                         and piv.effective_end_date
                 and pro.change_date between pet.effective_start_date
                                         and pet.effective_end_date
                 and pro.change_date between pgr.effective_start_date
                                         and pgr.effective_end_date
                 and ppf.employee_number = '1309') sal
where
    1=1
and sysdate between sal.effective_start_date and sal.effective_end_date
group by
    sal.effective_start_date,
    sal.effective_end_date,
    sal.job_id,
    sal.country,
    sal.pay_basis_id,
    sal.currency_code;