In Oracle HRMS, it becomes necessary to fetch employee details for some tasks.
The employee record details query is extremely helpful if bookmarked.
Below is the query to get employee details in Oracle Apps which you can add as favorites in your browser.
Query to Fetch Employee Details
SELECT pad.primary_flag, papf.employee_number "Employee Number", papf.title "Title", papf.first_name "First Name", papf.last_name "Last Name", TO_CHAR(papf.date_of_birth, 'DD-MON-RRRR') "Birth Date", TRUNC(MONTHS_BETWEEN(SYSDATE, papf.date_of_birth) / 12) "Age", hrlsex.meaning "Gender", ppt.user_person_type "Person Type", papf.national_identifier "National Identifier", hrlnat.meaning "Nationality", hrlms.meaning "Marital Status", papf.email_address "E-mail", TO_CHAR(papf.effective_start_date, 'DD-MON-RRRR') "Start Date", TO_CHAR(papf.effective_end_date, 'DD-MON-RRRR') "End Date", TO_CHAR(papf.original_date_of_hire, 'DD-MON-RRRR') "Hire Date", pjobs.name "Job", ppos.name "Position", pgrade.name "Grade", haou.name "Organization", pbus.name "Business Group", hrlat.meaning "Address Type", pad.address_line1 || CHR(10) || pad.address_line2 || CHR(10) || pad.address_line3 "Address", pad.postal_code "Postal Code", ftt.territory_short_name "Country", ftt.description "Full Country Name", hrleg.meaning "Ethnic Origin" FROM per_all_people_f papf, per_all_assignments_f paaf, per_person_types_tl ppt, hr_lookups hrlsex, hr_lookups hrlnat, hr_lookups hrlms, hr_lookups hrleg, hr_lookups hrlat, per_jobs pjobs, per_all_positions ppos, per_addresses pad, per_grades_tl pgrade, per_business_groups pbus, hr_all_organization_units haou, fnd_territories_tl ftt WHERE 1 = 1 AND hrlat.lookup_code(+) = pad.address_type AND hrlat.lookup_type(+) = 'ADDRESS_TYPE' AND hrlsex.lookup_code(+) = papf.sex AND hrlsex.lookup_type(+) = 'SEX' AND hrlnat.lookup_code(+) = papf.nationality AND hrlnat.lookup_type(+) = 'NATIONALITY' AND hrlms.lookup_code(+) = papf.marital_status AND hrlms.lookup_type(+) = 'MAR_STATUS' AND hrleg.lookup_code(+) = papf.per_information1 AND hrleg.lookup_type(+) = 'US_ETHNIC_GROUP' AND ftt.territory_code(+) = pad.country AND pad.business_group_id(+) = papf.business_group_id AND pad.date_to IS NULL AND pad.person_id(+) = papf.person_id AND pgrade.grade_id(+) = paaf.grade_id AND haou.organization_id(+) = paaf.organization_id AND haou.business_group_id(+) = paaf.business_group_id AND pbus.business_group_id(+) = paaf.business_group_id AND ppos.position_id(+) = paaf.position_id AND pjobs.job_id(+) = paaf.job_id AND ppt.person_type_id(+) = papf.person_type_id AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND paaf.person_id = papf.person_id AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date;
Per_all_people_f and per_all_assignments_f fetch the employee and assignment record of an employee.
Per_jobs table contains the job-related information of an employee.
The table per_all_positions retrieves position at the job of an employee.
Address is available in per_addresses table.
The per_grades table fetches grades data.
Organization data is queried from hr_all_organization_units table.
The country is derived from the fnd_territories_tl table.
I hope you like the page and find it useful. We highly appreciate comments and feedback.
Hi I am Paras.
Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at email@example.com. Again I appreciate your visit. Hope to see you again and again!