SQL Query to Retrieve Active US Employees' Home Address Details in Oracle HCM

SELECT
    papf.PERSON_NUMBER,
    ppnf.FULL_NAME,
    paaf.ASSIGNMENT_NUMBER,
    paaf.ASSIGNMENT_STATUS_TYPE,
    pad.ADDRESS_LINE_1,
    pad.ADDRESS_LINE_2,
    pad.TOWN_OR_CITY,
    pad.REGION_1 AS STATE,
    pad.POSTAL_CODE,
    pad.COUNTRY
FROM
    PER_ALL_PEOPLE_F papf
    JOIN PER_PERSON_NAMES_F ppnf
        ON papf.PERSON_ID = ppnf.PERSON_ID
        AND ppnf.NAME_TYPE = 'GLOBAL'
        AND TRUNC(SYSDATE) BETWEEN ppnf.EFFECTIVE_START_DATE AND ppnf.EFFECTIVE_END_DATE
    JOIN PER_ALL_ASSIGNMENTS_M paaf
        ON papf.PERSON_ID = paaf.PERSON_ID
        AND TRUNC(SYSDATE) BETWEEN paaf.EFFECTIVE_START_DATE AND paaf.EFFECTIVE_END_DATE
    JOIN PER_PERSON_ADDR_USAGES_F ppauf
        ON papf.PERSON_ID = ppauf.PERSON_ID
        AND ppauf.ADDRESS_TYPE = 'HOME'
        AND TRUNC(SYSDATE) BETWEEN ppauf.EFFECTIVE_START_DATE AND ppauf.EFFECTIVE_END_DATE
    JOIN PER_ADDRESSES_F pad
        ON ppauf.ADDRESS_ID = pad.ADDRESS_ID
        AND TRUNC(SYSDATE) BETWEEN pad.EFFECTIVE_START_DATE AND pad.EFFECTIVE_END_DATE
WHERE
    TRUNC(SYSDATE) BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
    AND paaf.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
    AND pad.COUNTRY = 'US'

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.