Query to get employee position in oracle apps

Query to get employee position in oracle apps

In this post, We will be discuss about the SQL query to get the employee position in oracle apps. I will share the query which helps to extract the detail of all Employees with their Job, Organization and Positions Information’s in oracle apps. We know that, We do create employee Positions in HRMS and Employee Positions is the combination of Job and Organization. So to create any Position, we need to attach the Organization and the Job so that is the reason , I am sharing the sql query which will be extract all employee related information’s with Employee Position , Job and Organization. Here below is the complete detail SQL query to get employee position in oracle apps having Important Employee tables.

Here Below is the Important DB Tables to write the sql query to get employee position in oracle apps

per_all_people_f

per_all_assignments_f

per_person_types_tl

per_jobs

per_all_positions

Detail SQL query to get the Employee Position Details in Oracle Apps

SELECT papf.employee_number "Employee Number",

papf.title "Title",

papf.first_name "First Name",

papf.last_name "Last Name",

papf.date_of_birth,

hrlsex.meaning "Gender",

ppt.user_person_type ,

papf.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_Name",

pgrade.name "Employee_Grade",

haou.name "Employee_Organization",

pbus.name "Business Group",

hrlat.meaning "Address Type",

pad.address_line1 || CHR(10) || pad.address_line2 || CHR(10) ||

pad.address_line3 "Employee Address",

pad.postal_code ,

ftt.territory_short_name "Country_Name"

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 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 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 ppos.position_id(+) = paaf.position_id

AND pjobs.job_id(+) = paaf.job_id

AND ppt.person_type_id(+) = papf.person_type_id

AND paaf.effective_end_date >=sysdate

AND paaf.person_id = papf.person_id

AND paaf.effective_end_date >=sysdate