Employee Assignment query in oracle apps
In this post , We will be discuss about employee Assignment query in oracle apps. Employee Assignment query helps to extract the Employee details in oracle apps. Using this Query we will be able to extract the Employee Header and Employee assignment details level information in oracle apps. Employee Assignment query is one of the Important SQL query related to Employee Information in Oracle apps. Here below , I will share the detail list of Employee Assignment query in oracle apps and important tables used in this query.
Important Table Used by Employee Assignment query in oracle apps
1.per_all_people_f
2.pep_all_assignments_f
3.per_person_types_tl
4.per_jobs
5.per_all_positions
6.per_grades_tl
7.per_business_groups
2.pep_all_assignments_f
3.per_person_types_tl
4.per_jobs
5.per_all_positions
6.per_grades_tl
7.per_business_groups
Detail Employee Assignment query in oracle apps
Here below is the complete sql query , to extract the employee assignment informations in oracle apps r12 instance.
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,
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
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
