PER Employee Assignments

Description
Categories: Enginatics, Toolkit - Operations
Repository: Github Columns: Business Group, Title, First Name, Last Name, Full Name, User Name, Gender, Person Type, Employee Number, Social Security ...
Master data report showing the employee demographic information including name, gender SSN, birthdate, age, nationality, effective start date, and additional profile information.
select
haouv.name business_group,
papf.title,
papf.first_name,
papf.last_name,
papf.full_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 papf.person_id=fu.employee_id) user_name,
xxen_util.meaning(papf.sex,'SEX',3) gender,
pptv.user_person_type person_type,
papf.employee_number,
papf.national_identifier social_security,
papf.date_of_birth birth_date,
trunc(months_between(sysdate,papf.date_of_birth)/12) age,
xxen_util.meaning(papf.marital_status,'MAR_STATUS',3) marital_status,
xxen_util.meaning(papf.nationality,'NATIONALITY',3) nationality,
papf.effective_start_date start_date,
decode(papf.effective_end_date,to_date('31.12.4712','DD.MM.YYYY'),null,papf.effective_end_date) end_date,
papf.original_date_of_hire hire_date,
trunc(months_between(sysdate,papf.start_date)/12) service_yrs,
round(mod(months_between(sysdate,papf.start_date),12)) service_months,
papf.email_address,
(
select distinct
listagg(xxen_util.meaning(pp.phone_type,'PHONE_TYPE',3)||': '||pp.phone_number,chr(10)) within group (order by pp.phone_type) over (partition by pp.parent_id) phone_number
from
per_phones pp
where
papf.person_id=pp.parent_id and
pp.parent_table='PER_ALL_PEOPLE_F' and
:effective_date between pp.date_from and nvl(pp.date_to,sysdate)
) phone_number,
papf2.full_name supervisor_name,
paaf.assignment_number,
paaf.effective_start_date assignment_start_date,
decode(paaf.effective_end_date,to_date('31.12.4712','DD.MM.YYYY'),null,paaf.effective_end_date) assignment_end_date,
paaf.normal_hours,
xxen_util.meaning(paaf.frequency,'FREQUENCY',3) Frequency,
paaf.time_normal_start,
paaf.time_normal_finish,
pjv.name job,
hapft.name position,
pgv.name grade,
(select ppb.name from per_pay_bases ppb where paaf.pay_basis_id=ppb.pay_basis_id) salary_basis,
paypf.payroll_name,
haouv2.name assignment_organization,
gl.name ledger,
xxen_util.concatenated_segments(paaf.default_code_comb_id) expense_account,
xxen_util.segments_description(paaf.default_code_comb_id) expense_account_description,
hla.location_code,
hla.description location_desc,
hla.region_1,
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,
xxen_util.meaning(paaf.manager_flag,'YES_NO',0) manager,
xxen_util.meaning(paaf.employment_category,'EMP_CAT',3) employment_category,
xxen_util.meaning(paaf.employee_category,'EMPLOYEE_CATG',3) employee_category,
paaf.probation_period,
xxen_util.meaning(paaf.probation_unit,'QUALIFYING_UNITS',3) probation_uom,
paaf.date_probation_end,
paaf.notice_period,
xxen_util.meaning(paaf.notice_period_uom,'QUALIFYING_UNITS',3) notice_period_uom,
paaf.internal_address_line,
xxen_util.meaning(paaf.change_reason,'EMP_ASSIGN_REASON',3) assignment_change_reason,
&flexfield_columns
xxen_util.meaning(pa.address_type,'ADDRESS_TYPE',3) address_type,
pa.address_line1,
pa.address_line2,
pa.address_line3,
pa.postal_code,
ftv.territory_short_name country,
pa.country country_code,
papf.per_information_category,
decode(papf.per_information_category,'ZA',papf.per_information1) za_income_tax_number,
decode(papf.per_information_category,'ZA',papf.per_information2) za_passport_number,
decode(papf.per_information_category,'ZA',papf.per_information10) za_country_of_passport_issue,
decode(papf.per_information_category,'ZA',xxen_util.meaning(papf.per_information9,'YES_NO',0)) za_foreign_national,
decode(papf.per_information_category,'ZA',papf.per_information3) za_work_permit_number,
decode(papf.per_information_category,'ZA',fnd_date.canonical_to_date(papf.per_information8)) za_work_permit_expiry_date,
decode(papf.per_information_category,'ZA',fnd_date.canonical_to_date(papf.per_information11)) za_date_of_naturalization,
decode(papf.per_information_category,'ZA',xxen_util.meaning(papf.per_information4,'ZA_RACE',3)) za_race,
decode(papf.per_information_category,'ZA',papf.per_information5) za_ethnic_origin,
decode(papf.per_information_category,'ZA',papf.per_information6) za_language_preference,
decode(papf.per_information_category,'ZA',papf.per_information7) za_religion,
decode(papf.per_information_category,'ZA',papf.per_information12) za_nature_of_person,
xxen_util.user_name(papf.created_by) person_created_by,
xxen_util.client_time(papf.creation_date) person_creation_date,
xxen_util.user_name(papf.last_updated_by) person_last_updated_by,
xxen_util.client_time(papf.last_update_date) person_last_update_date,
xxen_util.user_name(paaf.created_by) assignment_created_by,
xxen_util.client_time(paaf.creation_date) asssignment_creation_date,
xxen_util.user_name(paaf.last_updated_by) asssignment_last_updated_by,
xxen_util.client_time(paaf.last_update_date) assignment_last_update_date
from
hr_all_organization_units_vl haouv,
(select papf.* from per_all_people_f papf where :effective_date between papf.effective_start_date and papf.effective_end_date) papf,
(select papf.* from per_all_people_f papf where :effective_date between papf.effective_start_date and papf.effective_end_date) papf2,
per_person_types_v pptv,
(select paaf.* from per_all_assignments_f paaf where :effective_date between paaf.effective_start_date and paaf.effective_end_date) paaf,
per_assignment_status_types_v pastv,
hr_all_organization_units_vl haouv2,
per_jobs_vl pjv,
per_all_positions pap,
hr_all_positions_f_tl hapft,
per_grades_vl pgv,
hr_locations_all hla,
gl_ledgers gl,
(select pa.* from per_addresses pa where pa.primary_flag='Y' and :effective_date between pa.date_from and nvl(pa.date_to,:effective_date)) pa,
fnd_territories_vl ftv,
(select paypf.* from pay_all_payrolls_f paypf where :effective_date between paypf.effective_start_date and paypf.effective_end_date) paypf
where
1=1 and
papf.person_type_id=pptv.person_type_id and
papf.business_group_id=haouv.organization_id and
papf.person_id=paaf.person_id(+) and
paaf.assignment_status_type_id=pastv.assignment_status_type_id(+) and
paaf.organization_id=haouv2.organization_id(+) and
paaf.job_id=pjv.job_id(+) and
paaf.position_id=pap.position_id(+) and
pap.position_id=hapft.position_id(+) and
hapft.language(+)=userenv('lang') and
paaf.grade_id=pgv.grade_id(+) and
paaf.supervisor_id=papf2.person_id(+) and
paaf.location_id=hla.location_id(+) and
paaf.set_of_books_id=gl.ledger_id(+) and
papf.business_group_id=pa.business_group_id(+) and
papf.person_id=pa.person_id(+) and
pa.country=ftv.territory_code(+) and
paaf.payroll_id=paypf.payroll_id(+)
order by
haouv.name,
papf.last_name,
papf.first_name,
paaf.assignment_number desc
Parameter Name SQL text Validation
User Name
papf.person_id in (select fu.employee_id from fnd_user fu where fu.user_name=:user_name)
LOV
Business Group
haouv.name=:business_group
LOV
Person Full Name
papf.full_name=:full_name
LOV
Person Type
pptv.user_person_type=:person_type
LOV
Show Assignment Flexfield Columns
select
'paaf.'||lower(fdfcuv.application_column_name)||' "'||fdfcuv.form_left_prompt||'",' column_text
from
fnd_descr_flex_col_usage_vl fdfcuv
where
fdfcuv.application_id=800 and
fdfcuv.descriptive_flexfield_name='PER_ASSIGNMENTS' and
fdfcuv.enabled_flag='Y' and
fdfcuv.display_flag='Y' and
fdfcuv.descriptive_flex_context_code='Global Data Elements'
order by
fdfcuv.column_seq_num
Assignment Organization
papf.person_id in
(
select
paaf.person_id
from
hr_all_organization_units_vl haouv,
per_all_assignments_f paaf
where
haouv.name=:assignment_organization and
haouv.organization_id=paaf.organization_id and
sysdate between nvl(paaf.effective_start_date,sysdate) and nvl(paaf.effective_end_date,sysdate)
)
LOV
Effective Date
 
Date