PA Labor Cost Rates Listing

SELECT 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,trunc(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(o.name,1,60) organization
,	j.name job
,PA_COST_RATE_PUB.GetEmpCostRateInfo(p.person_id,a.job_id,a.organization_id,trunc(nvl(:effective_date,sysdate)),'RULE','R')   compensation_rule_set
,PA_COST_RATE_PUB.GetEmpCostRateInfo(p.person_id,a.job_id,a.organization_id,trunc(nvl(:effective_date,sysdate)),'CURR','R') rate_curr ,PA_COST_RATE_PUB.GetEmpCostRate(p.person_id,a.job_id,a.organization_id,trunc(nvl(:effective_date,sysdate)),'DENOM','R') hourly_cost_rate
, PA_COST_RATE_PUB.GetEmpCostRateInfo(p.person_id,a.job_id,a.organization_id,trunc(nvl(:effective_date,sysdate)),'START','R')  start_date_active
,PA_COST_RATE_PUB.GetEmpCostRateInfo(p.person_id,a.job_id,a.organization_id,trunc(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,trunc(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,trunc(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))
order by 1, 2, 3, 4, 5, 7, 6, 8
--	  p.full_name,8 	 ,6