PA Employee Assignments By Organization

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