PA Labor Cost Rates Listing By Organization

Description
Categories: BI Publisher
Application: Projects
Source: IMP: Labor Cost Rates Listing By Organization (XML)
Short Name: PAXRWLCO_XML
DB package: PA_PAXRWLCO_XMLP_PKG
Run PA Labor Cost Rates Listing By Organization and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Sort Employees By
 
LOV Oracle
Top Organization
 
LOV Oracle
Effective Date
 
Date
Job Level
 
LOV Oracle
Job Discipline
 
LOV Oracle
Labor Costing Rule
 
LOV Oracle
Blitz Report™