Employees Missing Address Data in Oracle Fusion HCM

Human Capital Management (HCM) Human Resources (HRMS)
1

Hey guys!

What is the best way to find employees in Oracle FUSION HCM who have missing or incomplete address information?

I’m using PER_ADDRESSES_F and PER_PERSON_ADDR_USAGES_F, but the results are not always the same.

Some employees don’t have a primary address, some have end-dated addresses that are still active, and others have address fields that are only partially filled out or that are not valid.

Because of this, some employees with missing data don’t show up at all, and others with inactive data show up as valid.

Has anyone else had this problem?

Do you have any suggestions or SQL best practices for finding employees who have missing or incomplete address information?

2

Thanks for raising this. The query below should help resolve it.

#sqlquery

SELECT     
--SQL4Fusion (An Orbit Analytics Project)
--Retrieve employees whose address information is missing or incomplete.
    ppnf.full_name            employee_name,
	haou.name organization_name,
    ppau.effective_end_date   address_usage_start_date,
    ppau.effective_start_date address_usage_end_date,
    pa.effective_start_date   address_start_date,
    pa.effective_end_date     address_end_date,
    papf.person_number employee_number,
    pa.address_line_1 address_line_1,
    pa.town_or_city town_or_city,
    pa.postal_code postal_code,
    pa.country country,
    ppau.address_type address_type
FROM
    per_person_addr_usages_f  ppau,
    per_addresses_f           pa,
    per_all_people_f          papf,
    per_person_names_f_v      ppnf,
    per_all_assignments_m     paaf,
    hr_all_organization_units haou
WHERE
        papf.person_id = ppau.person_id (+)
    AND trunc(sysdate) BETWEEN ppau.effective_start_date AND nvl(ppau.effective_end_date, sysdate + 5)
    AND ppau.address_id = pa.address_id (+)
    AND trunc(sysdate) BETWEEN pa.effective_start_date AND nvl(pa.effective_end_date, sysdate + 5)
    AND ( ppau.address_type IS NULL
          OR papf.mailing_address_id IS NULL
          OR pa.address_id IS NULL
          OR pa.address_line_1 IS NULL
          OR pa.town_or_city IS NULL
          OR pa.postal_code IS NULL
          OR pa.country IS NULL )
    AND papf.person_id = ppnf.person_id
    AND papf.person_id = paaf.person_id
    AND paaf.organization_id = haou.organization_id
    AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
    AND trunc(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
    AND trunc(sysdate) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date```