select o.organization_id organization_id1
, substr('Organization:'||o.name,1,60) Organization
, p.person_id
, p.full_name full_name
, DECODE(p.current_employee_flag,'Y',p.employee_number,p.npw_number) employee_number
, j.name Job
, to_char(a.effective_start_date,'DD-MON-YYYY') effective_start_date
, decode(a.effective_end_date,to_date('47121231', 'YYYYMMDD'),'',
to_char(a.effective_end_date,'DD-MON-YYYY')) effective_end_date
from per_job_definitions jd
, per_jobs j
, pa_implementations i
, hr_organization_units o
, per_people_f p
, per_assignments_f a
where a.organization_id
between decode(:organization_id,null,-99999,:organization_id)
and decode(:organization_id,null,999999999999999,:organization_id)
and nvl(:job_id,nvl(a.job_id,-1)) = nvl(a.job_id,-1)
and nvl(:effective_date,sysdate)
between a.effective_start_date
and a.effective_end_date
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 a.primary_flag = 'Y'
and a.assignment_type IN ('E' ,'C')
and p.person_id = a.person_id
and NVL(:effective_date,sysdate)
between p.effective_start_date
and p.effective_end_date
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
and o.organization_id=:organization_id
order by
decode(:sort_by,
'ORGANIZATION' ,o.name,
'JOB' ,j.name,
'JOB LEVEL' ,jd.segment1,
'JOB DISCIPLINE',jd.segment2,
p.last_name),
p.last_name,
p.first_name,
DECODE(p.current_employee_flag,'Y',p.employee_number,p.npw_number) ,
a.effective_start_date,
decode(:sort_by,
'ORGANIZATION',j.name,
'JOB', o.name,
null),
o.name,
j.name |
