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;
No comments:
Post a Comment