PA Job Listing

SELECT
	decode(:p_sort_by,
		'JOB LEVEL', jd.segment1,
		'JOB DISCIPLINE', jd.segment2,
		j.name) sort_by_value,
        jd.segment1 job_level,
        jd.segment2 job_discipline,
	j.name,
	j.comments,
	j.date_from,
	j.date_to
FROM
	per_jobs j,
	per_job_definitions jd,
        pa_implementations pi
  WHERE j.business_group_id = pi.business_group_id and j.job_definition_id = jd.job_definition_id and nvl ( jd.segment1 , 'zzz' ) = nvl ( :p_job_level , nvl ( jd.segment1 , 'zzz' ) ) and nvl ( jd.segment2 , 'zzz' ) = nvl ( :p_job_discipline , nvl ( jd.segment2 , 'zzz' ) )
  ORDER BY 1 ASC,2 ASC , j.name