HRMS - Employee Anniversaries and Birthdays Detail: Organization Hierarchy

Description
Categories: Discoverer
Imported Discoverer folders:
HRFG_EMPLOYEE_ASSIGNMENTS
HRIBG_ORG_HRCHY
HRFG_PERSONAL_DETAILS
Object IDs: 101234.101281.101296
Run HRMS - Employee Anniversaries and Birthdays Detail: Organization Hierarchy and other Oracle EBS reports with Blitz Report™ on our demo environment
select
hea.job_name,
hea.original_date_of_hire,
hea.supervisor_name,
hoh.hierarchy_name,
hoh.hierarchy_version,
hoh.hierarchy_version_date_from,
hoh.hierarchy_version_date_to,
hoh.hrchy_primary_flag,
hoh.organization_name,
hpd.date_of_birth,
hpd.email_address,
hpd.employee_number,
hpd.hire_date,
hpd.person_name
from
(
select bgrt.name business_group_name,peo.full_name person_name,peo.pre_name_adjunct prefix,peo.suffix suffix,peo.first_name first_name,peo.last_name last_name,peo.known_as preferred_name,peo.middle_names middle_names,peo.title title,pca.name collective_agreement_name,ass.assignment_number assignment_number,ass.time_normal_start normal_start_time,ass.time_normal_finish normal_end_time,hr_bis.bis_decode_lookup('YES_NO',ass.primary_flag) primary_flag,ass.internal_address_line internal_address,hr_bis.bis_decode_lookup('YES_NO',ass.manager_flag) manager_flag,hr_bis.bis_decode_lookup('EMP_CAT',ass.employment_category) employment_category,ass.assignment_type assignment_type_code,hr_bis.bis_decode_lookup('EMP_APL',ass.assignment_type) assignment_type,ass.normal_hours working_hours_amount,hr_bis.bis_decode_lookup('FREQUENCY',ass.frequency) working_hours_frequency,peo.employee_number employee_number,pps.date_start hire_date,peo.original_date_of_hire original_date_of_hire,phn.phone_number work_telephone_number,sup.full_name supervisor_name,sup.employee_number supervisor_number,ppb.name salary_basis,orgt.name organization_name,pft.name position_name,jbt.name job_name,loct.location_code location_name,gdt.name grade_name,pgr.group_name people_group,pay.payroll_name payroll_name,rac.name recruitment_activity_name,vac.name vacancy_name,nvl(astat.user_status,astt.user_status) user_assignment_status,hr_bis.bis_decode_lookup('PER_ASS_SYS_STATUS',ast.per_system_status) per_system_assignment_status,hr_bis.bis_decode_lookup('PAY_ASS_SYS_STATUS',nvl(asta.pay_system_status,ast.pay_system_status)) pay_system_assignment_status,ass.primary_flag primary_flag_code,ass.manager_flag manager_flag_code,ass.employment_category employment_category_code,ass.frequency frequency_code,ast.per_system_status per_system_status_code,nvl(asta.pay_system_status,ast.pay_system_status) pay_system_status_code,decode(pgr.id_flex_num,101,pgr.segment3 || '.' || pgr.segment6 || '.' || pgr.segment9 || '.' || pgr.segment20 || '.' || pgr.segment12 || '.' || pgr.segment15 || '.' || pgr.segment18 || '.' || pgr.segment21,50295,pgr.segment1,50341,pgr.segment1 || '.' || pgr.segment2 || '.' || pgr.segment3 || '.' || pgr.segment4 || '.' || pgr.segment5,50342,pgr.segment1 || '.' || pgr.segment2 || '.' || pgr.segment3 || '.' || pgr.segment4 || '.' || pgr.segment5 || '.' || pgr.segment6 || '.' || pgr.segment7,50418,pgr.segment1 || '.' || pgr.segment11 || '.' || pgr.segment12 || '.' || pgr.segment13 || '.' || pgr.segment14,50523,pgr.segment1 || '.' || pgr.segment2 || '.' || pgr.segment3 || '.' || pgr.segment4 || '.' || pgr.segment5 || '.' || pgr.segment6,50566,pgr.segment1,50567,pgr.segment1,50575,pgr.segment1 || '.' || pgr.segment2,50582,pgr.segment1,50587,pgr.segment1 || '.' || pgr.segment2 || '.' || pgr.segment3 || '.' || pgr.segment4,50613,pgr.segment1,50618,pgr.segment1,50718,pgr.segment1,50726,pgr.segment1,50727,pgr.segment1 || '.' || pgr.segment2 || '.' || pgr.segment3 || '.' || pgr.segment4,50735,pgr.segment30,51109,pgr.segment1 || '.' || pgr.segment2 || '.' || pgr.segment3,51152,pgr.segment3 || '.' || pgr.segment5,51277,pgr.segment1 || '.' || pgr.segment2 || '.' || pgr.segment3 || '.' || pgr.segment4,51283,pgr.segment6 || '|' || pgr.segment9 || '|' || pgr.segment2 || '|' || pgr.segment5 || '|' || pgr.segment3 || '|' || pgr.segment7 || '|' || pgr.segment8 || '|' || pgr.segment4,51308,pgr.segment1 || '.' || pgr.segment2 || '.' || pgr.segment3 || '.' || pgr.segment4 || '.' || pgr.segment5 || '.' || pgr.segment6 || '.' || pgr.segment7 || '.' || pgr.segment8 || '.' || pgr.segment9,51340,pgr.segment1,51370,pgr.segment1,51415,pgr.segment1 || '.' || pgr.segment2 || '.' || pgr.segment3,51539,pgr.segment1 || '.' || pgr.segment2 || '.' || pgr.segment3,51613,pgr.segment1 || '.' || pgr.segment2 || '.' || pgr.segment3 || '.' || pgr.segment4 || '.' || pgr.segment5,51617,pgr.segment1 || '.' || pgr.segment2,51622,pgr.segment1,51685,pgr.segment1,51789,pgr.segment1 || '.' || pgr.segment2,51849,pgr.segment1 || '.' || pgr.segment2,52123,pgr.segment1 || '.' || pgr.segment2 || '.' || pgr.segment3 || '.' || pgr.segment4,52324,pgr.segment1,52366,pgr.segment1,52638,pgr.segment1,52700,pgr.segment1,52721,pgr.segment1,52726,pgr.segment1,52764,pgr.segment1,52773,pgr.segment1,52774,pgr.segment1,52782,pgr.segment1,52824,pgr.segment1,52865,pgr.segment1,52886,pgr.segment1,52945,pgr.segment1 || '.' || pgr.segment2 || '.' || pgr.segment3 || '.' || pgr.segment4,52986,pgr.segment1,53072,pgr.segment1,53131,pgr.segment1,53403,pgr.segment1,53485,pgr.segment1,null) peo_grp,pgr.segment3 benefit_program_101,pgr.segment6 benefit_region_101,pgr.segment9 highly_compensated_101,pgr.segment20 team_101,pgr.segment12 pension_type_101,pgr.segment15 union_name_101,pgr.segment18 national_bargaining_unit_101,pgr.segment21 flag_101,pgr.segment1 pension_50295,pgr.segment1 pension_50341,pgr.segment2 paypoint_50341,pgr.segment3 prp_50341,pgr.segment4 union_50341,pgr.segment5 flexi_time_50341,pgr.segment1 pension_50342,pgr.segment2 paypoint_50342,pgr.segment3 prp_50342,pgr.segment4 union_50342,pgr.segment5 car_user_50342,pgr.segment6 car_capacity_50342,pgr.segment7 flexi_time_50342,pgr.segment1 handicap_level_50418,pgr.segment11 union_member_50418,pgr.segment12 union_delegate_50418,pgr.segment13 work_council_member_50418,pgr.segment14 elected_date_50418,pgr.segment1 benefit_region_50523,pgr.segment2 highly_compensated_50523,pgr.segment3 team_50523,pgr.segment4 pension_type_50523,pgr.segment5 union_50523,pgr.segment6 group_50523,pgr.segment1 people_group_50566,pgr.segment1 people_group_50567,pgr.segment1 pension_50575,pgr.segment2 internal_union_50575,pgr.segment1 people_group_50582,pgr.segment1 club_50587,pgr.segment2 bonus_yn_50587,pgr.segment3 fse_yn_50587,pgr.segment4 txt_50587,pgr.segment1 bonus_calc_code_50613,pgr.segment1 bonus_calc_code_50618,pgr.segment1 people_group_50718,pgr.segment1 people_group_50726,pgr.segment1 people_group_50727,pgr.segment2 certified_bilingual_50727,pgr.segment3 nominated_50727,pgr.segment4 superior_function_50727,pgr.segment30 empresa_50735,pgr.segment1 pension_plan_type_51109,pgr.segment2 vesting_date_51109,pgr.segment3 vested_in_plan_51109,pgr.segment3 team_51152,pgr.segment5 union_51152,pgr.segment1 au_team_51277,pgr.segment2 au_leave_group_51277,pgr.segment3 au_award_51277,pgr.segment4 au_type_51277,pgr.segment6 payment_basis_51283,pgr.segment9 increment_month_51283,pgr.segment2 pension_51283,pgr.segment5 pension_scheme_51283,pgr.segment3 union_51283,pgr.segment7 hours_51283,pgr.segment8 weeks_per_year_51283,pgr.segment4 person_usage_51283,pgr.segment1 faculty_type_51308,pgr.segment2 faculty_contract_51308,pgr.segment3 contract_start_date_51308,pgr.segment4 contract_end_date_51308,pgr.segment5 bargaining_unit_51308,pgr.segment6 benefit_program_51308,pgr.segment7 highly_compensated_51308,pgr.segment8 team_51308,pgr.segment9 union_name_51308,pgr.segment1 uic_51340,pgr.segment1 gehaltszettel_an_51370,pgr.segment1 uic_51415,pgr.segment2 special_category_51415,pgr.segment3 skill_type_51415,pgr.segment1 business_unit_51539,pgr.segment2 overtime_eligibility_51539,pgr.segment3 employee_type_51539,pgr.segment1 nz_team_51613,pgr.segment2 nz_leave_group_51613,pgr.segment3 nz_flexi_time_51613,pgr.segment4 nz_car_provided_51613,pgr.segment5 nz_car_park_provided_51613,pgr.segment1 hk_pension_fund_51617,pgr.segment2 hk_medical_plan_51617,pgr.segment1 p_fund_51622,pgr.segment1 team_51685,pgr.segment1 statut_juridique_51789,pgr.segment2 fonction_publique_51789,pgr.segment1 paypoint_51849,pgr.segment2 union_51849,pgr.segment1 union_52123,pgr.segment2 pay_point_52123,pgr.segment3 medical_52123,pgr.segment4 pension_52123,pgr.segment1 team_52324,pgr.segment1 people_group_52366,pgr.segment1 people_group_52638,pgr.segment1 people_group_52700,pgr.segment1 people_group_52721,pgr.segment1 people_group_52726,pgr.segment1 people_group_52764,pgr.segment1 people_group_52773,pgr.segment1 people_group_52774,pgr.segment1 people_group_52782,pgr.segment1 people_group_52824,pgr.segment1 people_group_52865,pgr.segment1 people_group_52886,pgr.segment1 people_group_name_52945,pgr.segment2 certified_bilingual_52945,pgr.segment3 appointment_52945,pgr.segment4 superior_function_52945,pgr.segment1 people_group_52986,pgr.segment1 people_group_53072,pgr.segment1 people_group_53131,pgr.segment1 people_group_53403,pgr.segment1 people_group_53485,ass.ass_attribute_category context,ass.ass_attribute1 pa_billing_titles,ass.ass_attribute2 business_group,ass.ass_attribute30 move_employee,ass.ass_attribute10 alt_business_group_supervisor,ass.assignment_id assignment_id,ass.business_group_id business_group_id,gdt.grade_id grade_id,jbt.job_id job_id,loct.location_id location_id,ass.organization_id organization_id,pay.payroll_id payroll_id,ass.period_of_service_id period_of_service_id,pgr.people_group_id people_group_id,ass.person_id person_id,pft.position_id position_id,rac.recruitment_activity_id recruitment_activity_id,ppb.pay_basis_id salary_basis_id,ass.supervisor_id supervisor_id,vac.vacancy_id vacancy_id,pca.collective_agreement_id collective_agreement_id from per_all_people_f peo,per_all_people_f sup,hr_all_organization_units_tl orgt,hr_all_organization_units_tl bgrt,per_recruitment_activities rac,per_all_vacancies vac,per_pay_bases ppb,hr_all_positions_f_tl pft,pay_all_payrolls_f pay,per_jobs_tl jbt,hr_locations_all_tl loct,per_grades_tl gdt,pay_people_groups pgr,per_assignments_f ass,per_assignment_status_types ast,per_assignment_status_types_tl astt,per_ass_status_type_amends asta,per_ass_status_type_amends_tl astat,per_phones phn,per_periods_of_service pps,per_collective_agreements pca where ass.organization_id=orgt.organization_id and orgt.language=userenv('LANG') and ass.position_id=pft.position_id(+) and pft.language (+)=userenv('LANG') and ass.payroll_id=pay.payroll_id(+) and ass.job_id=jbt.job_id(+) and jbt.language (+)=userenv('LANG') and ass.location_id=loct.location_id(+) and loct.language (+)=userenv('LANG') and ass.grade_id=gdt.grade_id(+) and gdt.language (+)=userenv('LANG') and ass.supervisor_id=sup.person_id(+) and ass.people_group_id=pgr.people_group_id(+) and ass.pay_basis_id=ppb.pay_basis_id (+) and ass.business_group_id=bgrt.organization_id and bgrt.language=userenv('LANG') and ass.person_id=peo.person_id and peo.current_employee_flag='Y' and ass.vacancy_id=vac.vacancy_id (+) and ass.recruitment_activity_id=rac.recruitment_activity_id (+) and ass.assignment_type='E' and ast.assignment_status_type_id=ass.assignment_status_type_id and ast.assignment_status_type_id=astt.assignment_status_type_id and astt.language=userenv('LANG') and ass.assignment_status_type_id=asta.assignment_status_type_id (+) and ass.business_group_id=asta.business_group_id (+) and asta.ass_status_type_amend_id=astat.ass_status_type_amend_id (+) and nvl(astat.language,userenv('LANG'))=userenv('LANG') and ass.person_id=phn.parent_id (+) and phn.parent_table (+)='PER_ALL_PEOPLE_F' and phn.phone_type (+)='W1' and peo.person_id=pps.person_id (+) and ass.collective_agreement_id=pca.collective_agreement_id (+) and ((peo.employee_number is null) or (peo.employee_number is not null and pps.date_start=(select max(pps1.date_start) from per_periods_of_service pps1 where pps1.person_id=peo.person_id and pps1.date_start<=peo.effective_end_date))) and trunc(sysdate) between phn.date_from (+) and nvl(phn.date_to (+),hr_general.end_of_time) and trunc(sysdate) between sup.effective_start_date (+) and sup.effective_end_date (+) and trunc(sysdate) between pay.effective_start_date (+) and pay.effective_end_date (+) and trunc(sysdate) between peo.effective_start_date and peo.effective_end_date and trunc(sysdate) between ass.effective_start_date and ass.effective_end_date and ass.business_group_id=nvl(hr_bis.get_sec_profile_bg_id,ass.business_group_id)
) hea,
(
select ost.name hierarchy_name,hr_bis.bis_decode_lookup('YES_NO',ost.primary_structure_flag) hrchy_primary_flag,ost.primary_structure_flag hrchy_primary_flag_code,osv.version_number hierarchy_version,osv.date_from hierarchy_version_date_from,osv.date_to hierarchy_version_date_to,orgt.name organization_name,osh.organization_level organization_level,sorgt.name sub_organization_name,osh.sub_organization_level sub_organization_level,nvl(osv.date_to,hr_general.end_of_time) hrchy_version_date_to_nn,ost.organization_structure_id org_hierarchy_id,osv.org_structure_version_id org_hrchy_version_id,osh.org_business_group_id org_business_group_id,osh.organization_id organization_id,osh.sub_org_business_group_id sub_org_business_group_id,osh.sub_organization_id sub_organization_id from per_organization_structures ost,per_org_structure_versions osv,hri_org_hrchy_summary osh,hr_all_organization_units_tl orgt,hr_all_organization_units_tl sorgt where ost.organization_structure_id=osv.organization_structure_id and osv.org_structure_version_id=osh.org_structure_version_id and osh.organization_id=orgt.organization_id and orgt.language=userenv('LANG') and osh.sub_organization_id=sorgt.organization_id and sorgt.language=userenv('LANG')
) hoh,
(
select bgrt.name business_group_name,peo.full_name person_name,peo.employee_number employee_number,hr_bis.bis_decode_lookup('TITLE',peo.title) title,peo.middle_names middle_names,peo.known_as preferred_name,peo.first_name first_name,peo.last_name last_name,peo.pre_name_adjunct prefix,peo.suffix suffix,hr_bis.bis_decode_lookup('SEX',peo.sex) gender,peo.previous_last_name previous_last_name,hr_bis.bis_decode_lookup('YES_NO',peo.registered_disabled_flag) registered_disabled,peo.date_of_birth date_of_birth,decode(date_of_birth,null,null,floor(months_between(sysdate,date_of_birth)/12)) age,peo.town_of_birth town_of_birth,peo.region_of_birth region_of_birth,ftrt.territory_short_name country_of_birth,decode(peo.marital_status,null,null,hr_bis.bis_decode_lookup('MAR_STATUS',peo.marital_status)) marital_status,decode(peo.nationality,null,null,hr_bis.bis_decode_lookup('NATIONALITY',peo.nationality)) nationality,peo.national_identifier national_identifier,peo.applicant_number applicant_number,peo.email_address email_address,decode(peo.expense_check_send_to_address,null,null,hr_bis.bis_decode_lookup('HOME_OFFICE',peo.expense_check_send_to_address)) mail_destination,decode(peo.second_passport_exists,null,null,hr_bis.bis_decode_lookup('YES_NO',peo.second_passport_exists)) second_passport,peo.office_number office_number,peo.internal_location internal_location,peo.mailstop mailstop,peo.work_schedule work_schedule,peo.fte_capacity fte_capacity,decode(peo.resume_exists,null,null,hr_bis.bis_decode_lookup('YES_NO',peo.resume_exists)) resume_exists,peo.resume_last_updated resume_last_updated,hr_bis.bis_decode_lookup('YES_NO',nvl(peo.current_applicant_flag,'N')) current_applicant_flag,hr_bis.bis_decode_lookup('YES_NO',nvl(peo.current_employee_flag,'N')) current_employee_flag,peo.date_employee_data_verified date_employee_data_verified,peo.date_of_death date_of_death,decode(peo.fast_path_employee,null,null,hr_bis.bis_decode_lookup('YES_NO',peo.fast_path_employee)) fast_path_employee,peo.hold_applicant_date_until hold_application_until,peo.honors honors,decode(peo.on_military_service,null,null,hr_bis.bis_decode_lookup('YES_NO',peo.on_military_service)) on_military_service,peo.projected_start_date projected_start_date,decode(peo.student_status,null,null,hr_bis.bis_decode_lookup('STUDENT_STATUS',peo.student_status)) student_status,decode(peo.current_employee_flag,'Y',pps.date_start,null) hire_date,peo.original_date_of_hire original_date_of_hire,peo.sex gender_code,peo.registered_disabled_flag registered_disabled_flag_code,peo.marital_status marital_status_code,peo.nationality nationality_code,peo.expense_check_send_to_address expense_check_send_addr_code,peo.second_passport_exists second_passport_exists_code,peo.resume_exists resume_exists_code,peo.current_applicant_flag current_applicant_flag_code,peo.current_employee_flag current_employee_flag_code,peo.fast_path_employee fast_path_employee_code,peo.on_military_service on_military_service_code,peo.student_status student_status_code,pptt.user_person_type person_type,hri_bpl_person_type.get_emp_user_person_type (trunc(sysdate),peo.person_id) employee_person_type,hri_bpl_person_type.get_apl_user_person_type (trunc(sysdate),peo.person_id) applicant_person_type,hri_bpl_person_type.get_concat_user_person_type (trunc(sysdate),peo.person_id) user_concat_person_type,pp.phone_number work_telephone_number,lang.nls_language correspondence_language,ppt.system_person_type system_person_type_code,peo.attribute_category context_value,peo.attribute30 emea_ads_hrms_core_id_1,peo.attribute1 current_employer_4,peo.attribute2 "EMPLOYER'S_PHONE_4",peo.attribute3 verification_check_date_4,peo.attribute4 verification_checked_by_4,peo.attribute5 reference_check_date_4,peo.attribute6 referenced_checked_by_4,peo.attribute7 pre_employment_physical_date_4,peo.attribute1 drivers_license_jurisdiction_5,peo.attribute2 drivers_license_number_5,peo.attribute10 global_id_5,peo.attribute11 country_of_origin_5,peo.attribute3 security_clearance_5,peo.attribute6 badge_number_5,peo.attribute4 "BONDED?_5",peo.attribute5 hired_via_acquisition_5,peo.attribute7 acquired_company_5,peo.attribute8 original_hire_date_5,peo.attribute12 proposed_retirement_date_5,peo.attribute13 proposed_retirement_reason_5,peo.attribute14 service_start_date_5,peo.attribute9 union_seniority_date_5,peo.business_group_id business_group_id,peo.person_id person_id,ppt.person_type_id person_type_id,peo.last_update_date peo_last_update_date from fnd_languages lang,hr_all_organization_units_tl bgrt,per_person_types ppt,per_person_types_tl pptt,per_phones pp,per_people_f peo,per_periods_of_service pps,fnd_territories_tl ftrt where peo.person_type_id=ppt.person_type_id and ppt.person_type_id=pptt.person_type_id and pptt.language=userenv('LANG') and pp.parent_table (+)='PER_ALL_PEOPLE_F' and pp.phone_type (+)='W1' and peo.person_id=pp.parent_id(+) and trunc(sysdate) between peo.effective_start_date and peo.effective_end_date and trunc(sysdate) between pp.date_from (+) and pp.date_to (+) and peo.person_id=pps.person_id (+) and trunc(sysdate) between pps.date_start (+) and nvl(pps.actual_termination_date (+),trunc(sysdate)) and peo.correspondence_language=lang.language_code (+) and peo.business_group_id=bgrt.organization_id and bgrt.language=userenv('LANG') and peo.country_of_birth=ftrt.territory_code(+) and ftrt.language (+)=userenv('LANG') and peo.business_group_id=nvl(hr_bis.get_sec_profile_bg_id,peo.business_group_id)
) hpd
where
2=2 and
hoh.sub_organization_id=hea.organization_id and
hpd.person_id=hea.person_id
Parameter Name SQL text Validation
Hierarchy Version Date From From
hoh.hierarchy_version_date_from>=:hierarchy_version_date_fr_from
Date
Hierarchy Version Date From To
hoh.hierarchy_version_date_from<=:hierarchy_version_date_fr_to
Date
Hierarchy Version Date To From
hoh.hierarchy_version_date_to>=:hierarchy_version_date_to_from
Date
Hierarchy Version Date To To
hoh.hierarchy_version_date_to<=:hierarchy_version_date_to_to
Date
Download
Blitz Report™

Blitz Report™ provides multiple benefits:

Blitz Report™