Query to Get Employees Legal Employer Details in Oracle Fusion Cloud - Orapoint

In this post we will discuss on query to get Employee’s legal Employer details in Oracle Fusion Cloud Application, this query will get employer details which are linked with employee’s in HCM Module.

Important table of Employee’s in Oracle Fusion Cloud Application

HR_ORG_UNIT_CLASSIFICATIONS_F

HR_ALL_ORGANIZATION_UNITS_F

HR_ORGANIZATION_UNITS_F_TL

per_all_assignments_m

per_all_people_f papf

Query to Get Employee’s Legal Employer Details in Oracle Fusion Cloud Application

SELECT  papf.person_number,
        hauft.NAME LegalEmployer
FROM    HR_ORG_UNIT_CLASSIFICATIONS_F houcf, 
        HR_ALL_ORGANIZATION_UNITS_F haouf, 
        HR_ORGANIZATION_UNITS_F_TL hauft,
        per_all_assignments_m paam,
        per_all_people_f papf
WHERE   haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID 
    AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID 
    AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE 
    AND hauft.LANGUAGE = 'US'
    AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE 
    AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE 
    AND houcf.CLASSIFICATION_CODE = 'HCM_LEMP' 
    AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date
    AND hauft.organization_id = paam.legal_entity_id
    and paam.person_id = papf.person_id
    and paam.primary_assignment_flag = 'Y'
    and paam.assignment_type = 'E'
    and paam.effective_latest_change = 'Y'
    and sysdate between paam.effective_start_date and paam.effective_end_date
    and sysdate between papf.effective_start_date and papf.effective_end_date
    and papf.person_number = nvl(:personnumber,papf.person_number) 
order by papf.person_number asc,hauft.name asc nulls first;