PA Employee Assignments By Organization

Description
Categories: BI Publisher
Application: Projects
Source: IMP: Employee Assignments By Organization (XML)
Short Name: PAXPEEMO_XML
DB package: PA_PAXPEEMO_XMLP_PKG
Run PA Employee Assignments By Organization and other Oracle EBS reports with Blitz Report™ on our demo environment
select  o.organization_id organization_id1
,      substr('Organization:'||o.name,1,60) Organization 
,       p.person_id
,       p.full_name full_name
,       DECODE(p.current_employee_flag,'Y',p.employee_number,p.npw_number) employee_number  
,       j.name Job
,   to_char(a.effective_start_date,'DD-MON-YYYY')  effective_start_date
,       decode(a.effective_end_date,to_date('47121231', 'YYYYMMDD'),'',
               to_char(a.effective_end_date,'DD-MON-YYYY'))   effective_end_date
from    per_job_definitions jd
,       per_jobs j
,       pa_implementations i
,       hr_organization_units o
,       per_people_f  p
,       per_assignments_f a
where   a.organization_id
        between decode(:organization_id,null,-99999,:organization_id)
        and     decode(:organization_id,null,999999999999999,:organization_id)
and     nvl(:job_id,nvl(a.job_id,-1)) = nvl(a.job_id,-1)
and     nvl(:effective_date,sysdate)       
        between a.effective_start_date
        and     a.effective_end_date
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     a.primary_flag = 'Y'
and     a.assignment_type  IN ('E' ,'C')  
and     p.person_id = a.person_id
and     NVL(:effective_date,sysdate)     
        between p.effective_start_date
        and     p.effective_end_date
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 
 and o.organization_id=:organization_id
order by
        decode(:sort_by,
                'ORGANIZATION'  ,o.name,
                'JOB'           ,j.name,
                'JOB LEVEL'     ,jd.segment1,
                'JOB DISCIPLINE',jd.segment2,
                                 p.last_name),
        p.last_name,
        p.first_name,
        DECODE(p.current_employee_flag,'Y',p.employee_number,p.npw_number) , 
        a.effective_start_date,
        decode(:sort_by,
                'ORGANIZATION',j.name,
                'JOB',         o.name,
                               null),
        o.name,
        j.name
Parameter Name SQL text Validation
Sort Assignments By
 
LOV Oracle
Effective Date
 
Date
Start Organization
 
LOV Oracle
Job
 
LOV Oracle
Job Level
 
LOV Oracle
Job Discipline
 
LOV Oracle
Blitz Report™