Saturday, August 1, 2020

Query to Fetch Employee Details in Oracle Apps R12 (PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_F, PER_JOBS, PER_ALL_POSITIONS, PER_PHONES)












Below query will help you to get basic employee details and joins between key employee tables.

Tables Involved:

  • per_all_people_f
  • per_all_assignments_f
  • gl_ledgers
  • gl_code_combinations_kfv
  • per_all_positions
  • per_jobs
  • per_phones
  • hr_locations_all
Query:
SELECT (SELECT name FROM hr_all_organization_units WHERE organization_id = paaf.business_group_id ) business_group, papf1.employee_number, papf1.first_name, papf1.last_name, papf1.full_name, papf1.sex gender, papf1.effective_start_date, papf1.effective_end_date, papf1.email_address, (SELECT hloc.location_code FROM hr_locations_all hloc WHERE hloc.location_id = paaf.location_id ) employee_location, papf2.employee_number supervisor_employee_number, papf2.full_name supervisor_name, ( SELECT pp.phone_number FROM per_phones pp WHERE papf1.person_id = pp.parent_id AND pp.parent_table = 'PER_ALL_PEOPLE_F' AND pp.date_from = papf1.effective_start_date AND NVL(pp.date_to, papf1.effective_end_date ) = papf1.effective_end_date ) work_telephone_number, (SELECT name FROM per_jobs WHERE job_id = paaf.job_id ) job_name, (SELECT name FROM per_all_positions WHERE position_id = paaf.position_id ) position_name, (SELECT concatenated_segments FROM gl_code_combinations_kfv WHERE code_combination_id = paaf.default_code_comb_id ) default_charge_account, (SELECT name FROM gl_ledgers WHERE ledger_id = paaf.set_of_books_id ) ledger_name FROM per_all_people_f papf1, per_all_people_f papf2, per_all_assignments_f paaf WHERE papf1.person_id(+) = paaf.person_id AND papf2.person_id(+) = paaf.supervisor_id AND paaf.primary_flag = 'Y' AND papf1.current_employee_flag = 'Y' AND papf2.current_employee_flag = 'Y'

0 Responses to “Query to Fetch Employee Details in Oracle Apps R12 (PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_F, PER_JOBS, PER_ALL_POSITIONS, PER_PHONES)”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.