Reports 2017-11-18T12:27:27+00:00

PER Employees and Assignments

Description
Categories: Enginatics, Human Resources
Human resources employees, assignments and supervisors

select
ppx.first_name,
ppx.last_name,
(select distinct listagg(xxen_util.user_name(fu.user_id),', ') within group (order by xxen_util.user_name(fu.user_id)) over (partition by fu.employee_id) user_name from fnd_user fu where ppx.person_id=fu.employee_id) user_name,
xxen_util.meaning(ppx.sex,'SEX',3) gender,
pptv.user_person_type person_type,
ppx.employee_number,
ppx.national_identifier social_security,
ppx.date_of_birth birth_date,
trunc(months_between(sysdate,ppx.date_of_birth)/12) age,
xxen_util.meaning(ppx.marital_status,'MAR_STATUS',3) status,
xxen_util.meaning(ppx.nationality,'NATIONALITY',3) nationality,
ppx.effective_start_date,
decode(ppx.effective_end_date,to_date('31.12.4712','DD.MM.YYYY'),null,ppx.effective_end_date) effective_end_date,
ppx.email_address,
haou2.name assignment_organization,
pjt.name job,
hla.location_code,
hla.description location_desc,
pastv.user_status assignment_status,
xxen_util.meaning(pastv.active_flag,'YES_NO',0) active,
xxen_util.meaning(paaf.primary_flag,'YES_NO',0) primary,
paaf.assignment_number,
xxen_util.meaning(paaf.employment_category,'EMP_CAT',3) employment_category,
xxen_util.meaning(paaf.employee_category,'EMPLOYEE_CATG',3) employee_category,
ppx2.full_name supervisor,
haou.name business_group
from
hr_all_organization_units haou,
per_people_x ppx,
per_people_x ppx2,
per_person_types_v pptv,
per_all_assignments_f paaf,
per_assignment_status_types_v pastv,
hr_all_organization_units haou2,
per_jobs_tl pjt,
hr_locations_all hla
where
1=1 and
ppx.person_type_id=pptv.person_type_id and
haou.organization_id=ppx.business_group_id and
ppx.person_id=paaf.person_id(+) and
sysdate between nvl(paaf.effective_start_date,sysdate) and nvl(paaf.effective_end_date,sysdate) and
paaf.assignment_status_type_id=pastv.assignment_status_type_id(+) and
paaf.organization_id=haou2.organization_id(+) and
paaf.job_id=pjt.job_id(+) and
pjt.language(+)=userenv('lang') and
paaf.supervisor_id=ppx2.person_id(+) and
paaf.location_id=hla.location_id(+)
order by
haou.name,
ppx.last_name,
ppx.first_name,
paaf.assignment_number desc

Parameter Name SQL text Validation
Person Full Name
ppx.full_name=:full_name
LOV
User Name
ppx.person_id in (select fu.employee_id from fnd_user fu where fu.user_name=:user_name)
LOV
Assignment Organization
ppx.person_id in
(
select
paaf.person_id
from
hr_all_organization_units haou,
per_all_assignments_f paaf
where
haou.name=:assignment_organization and
haou.organization_id=paaf.organization_id and
sysdate between nvl(paaf.effective_start_date,sysdate) and nvl(paaf.effective_end_date,sysdate)
)
LOV