select
decode(:sort_by,
'ORGANIZATION' ,substr(o.name,1,60),
'JOB' ,j.name,
'JOB LEVEL' ,jd.segment1,
'JOB DISCIPLINE',jd.segment2,
p.last_name) sort_by_value
, p.full_name full_name
, NVL( p.employee_number, p.npw_number ) employee_number
, substr(o.name,1,60) Organization
, j.name Job
, a.title billing_title
, a.effective_start_date
, a.effective_end_date
from per_job_definitions jd
, per_jobs j
, pa_implementations i
, hr_organization_units o
, per_all_people_f p
, per_assignments_f a
where
a.job_id = j.job_id(+)
and j.job_definition_id = jd.job_definition_id(+)
&P_PARA_SQL
and a.primary_flag = 'Y'
and a.assignment_type in ( 'E' , 'C' )
and p.person_id = a.person_id
and ((current_employee_flag='Y' and employee_number is not null) or (current_npw_flag='Y' and
npw_number is not null))
and a.organization_id = o.organization_id
and a.business_group_id = i.business_group_id
ORDER BY 1 ASC,2 ASC,3 ASC,4 ASC,5 ASC , a.effective_start_date |