PA Employee Assignment

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