How to Identify Duplicate Email Addresses in Oracle HCM

1. To View the Email Address for a Specific Employee:

SELECT * 
FROM PER_EMAIL_ADDRESSES
WHERE PERSON_ID IN (
    SELECT PERSON_ID 
    FROM PER_ALL_PEOPLE_F 
    WHERE PERSON_NUMBER = 'XXXX'
)

2. To View Email Addresses for All Employees:

SELECT * FROM PER_EMAIL_ADDRESSES

3. To Find Duplicate Email Addresses (Across All Employees):

SELECT EMAIL_ADDRESS, COUNT(*) AS Occurrences
FROM PER_EMAIL_ADDRESSES
GROUP BY EMAIL_ADDRESS
HAVING COUNT(*) > 1

This shows email addresses used more than once in the system.

4. To Get Employee Details for Duplicate Emails:

SELECT pea.PERSON_ID, papf.PERSON_NUMBER, pea.EMAIL_ADDRESS
FROM PER_EMAIL_ADDRESSES pea
JOIN PER_ALL_PEOPLE_F papf ON pea.PERSON_ID = papf.PERSON_ID
WHERE pea.EMAIL_ADDRESS IN (
    SELECT EMAIL_ADDRESS
    FROM PER_EMAIL_ADDRESSES
    GROUP BY EMAIL_ADDRESS
    HAVING COUNT(*) > 1
)
ORDER BY pea.EMAIL_ADDRESS

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