PA Labor Cost Rates Listing By Organization

SELECT  o.organization_id
,        upper(decode(:sort_by,
		'EMPLOYEE NAME', p.last_name,
		'EMPLOYEE NUMBER', DECODE(p.current_employee_flag,'Y',p.employee_number,p.npw_number),      'ORGANIZATION'  ,substr(o.name,1,60),
                'JOB LEVEL'     ,jd.segment1,
                'JOB DISCIPLINE',jd.segment2,
		'COMP RULE SET' ,  PA_COST_RATE_PUB.GetEmpCostRateInfo(p.person_id,a.job_id,a.organization_id,nvl(:effective_date,sysdate),'RULE','R'),
			         p.last_name) )sort_by_value
,       p.full_name
,     DECODE(p.current_employee_flag,'Y',p.employee_number,p.npw_number) employee_number ,     substr('Organization:'||o.name,1,60) organization
,	j.name job
,	PA_COST_RATE_PUB.GetEmpCostRateInfo(p.person_id,a.job_id,a.organization_id,nvl(:effective_date,sysdate),'RULE','R') compensation_rule_set  ,PA_COST_RATE_PUB.GetEmpCostRateInfo(p.person_id,a.job_id,a.organization_id,nvl(:effective_date,sysdate),'CURR','R') rate_curr   ,PA_COST_RATE_PUB.GetEmpCostRate(p.person_id,a.job_id,a.organization_id,nvl(:effective_date,sysdate),'DENOM','R') hourly_cost_rate
,	      PA_COST_RATE_PUB.GetEmpCostRateInfo(p.person_id,a.job_id,a.organization_id,nvl(:effective_date,sysdate),'START','R')  start_date_active
,	PA_COST_RATE_PUB.GetEmpCostRateInfo(p.person_id,a.job_id,a.organization_id,nvl(:effective_date,sysdate),'END','R')  end_date_active
FROM
	per_job_definitions jd
,	per_jobs j
,	hr_organization_units o
,	per_assignments_f a
,	per_people_f  p
WHERE	PA_COST_RATE_PUB.GetEmpCostRate(p.person_id,a.job_id,a.organization_id,nvl(:effective_date,sysdate),'DENOM','R') >=0
and
(:p_compensation_rule_set is null or
  :p_compensation_rule_set       =   PA_COST_RATE_PUB.GetEmpCostRateInfo(p.person_id,a.job_id,a.organization_id,nvl(:effective_date,sysdate) ,'RULE', 'R'))
and     trunc(nvl(:effective_date,sysdate) )
	between p.effective_start_date
	and p.effective_end_date
and	p.person_id= a.person_id
and	a.assignment_Type IN  ('E','C') and	a.primary_flag = 'Y'
and	trunc(nvl(:effective_date,sysdate))
	between a.effective_start_date
	and a.effective_end_date
and	a.organization_id = o.organization_id
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 o.organization_id=:organization_id1
order by 5, 2, 3, 4, 6, 8, 7, 9, 10, 11
--	 9 	 ,7