SELECT o.organization_id
, upper(decode(:sort_by,
'EMPLOYEE NAME', p.last_name,
'EMPLOYEE NUMBER', DECODE(p.current_employee_flag,'Y',p.employee_number,p.npw_number), 'ORGANIZATION' ,substr(o.name,1,60),
'JOB LEVEL' ,jd.segment1,
'JOB DISCIPLINE',jd.segment2,
'COMP RULE SET' , PA_COST_RATE_PUB.GetEmpCostRateInfo(p.person_id,a.job_id,a.organization_id,nvl(:effective_date,sysdate),'RULE','R'),
p.last_name) )sort_by_value
, p.full_name
, DECODE(p.current_employee_flag,'Y',p.employee_number,p.npw_number) employee_number , substr('Organization:'||o.name,1,60) organization
, j.name job
, PA_COST_RATE_PUB.GetEmpCostRateInfo(p.person_id,a.job_id,a.organization_id,nvl(:effective_date,sysdate),'RULE','R') compensation_rule_set ,PA_COST_RATE_PUB.GetEmpCostRateInfo(p.person_id,a.job_id,a.organization_id,nvl(:effective_date,sysdate),'CURR','R') rate_curr ,PA_COST_RATE_PUB.GetEmpCostRate(p.person_id,a.job_id,a.organization_id,nvl(:effective_date,sysdate),'DENOM','R') hourly_cost_rate
, PA_COST_RATE_PUB.GetEmpCostRateInfo(p.person_id,a.job_id,a.organization_id,nvl(:effective_date,sysdate),'START','R') start_date_active
, PA_COST_RATE_PUB.GetEmpCostRateInfo(p.person_id,a.job_id,a.organization_id,nvl(:effective_date,sysdate),'END','R') end_date_active
FROM
per_job_definitions jd
, per_jobs j
, hr_organization_units o
, per_assignments_f a
, per_people_f p
WHERE PA_COST_RATE_PUB.GetEmpCostRate(p.person_id,a.job_id,a.organization_id,nvl(:effective_date,sysdate),'DENOM','R') >=0
and
(:p_compensation_rule_set is null or
:p_compensation_rule_set = PA_COST_RATE_PUB.GetEmpCostRateInfo(p.person_id,a.job_id,a.organization_id,nvl(:effective_date,sysdate) ,'RULE', 'R'))
and trunc(nvl(:effective_date,sysdate) )
between p.effective_start_date
and p.effective_end_date
and p.person_id= a.person_id
and a.assignment_Type IN ('E','C') and a.primary_flag = 'Y'
and trunc(nvl(:effective_date,sysdate))
between a.effective_start_date
and a.effective_end_date
and a.organization_id = o.organization_id
and a.job_id = j.job_id(+)
and j.job_definition_id = jd.job_definition_id(+)
and (:job_level is null
or (:job_level is not null
and jd.segment1 = :job_level))
and (:job_discipline is null
or (:job_discipline is not null
and jd.segment2 = :job_discipline))
and o.organization_id=:organization_id1
order by 5, 2, 3, 4, 6, 8, 7, 9, 10, 11
-- 9 ,7 |
