Employee Missing Email Address (Fusion SQL Script)

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

This Employee Missing Email Address sql query displays details for active employees who have missing email address information as of today, helping HR identify incomplete email address records for follow-up.

#sqlquery

SELECT 
       --SQL4Fusion (An Orbit Analytics Project)
	   -- Retrieves the list of employees with missing address
       HAOU.NAME ORGANIZATION,
       PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
       PPNF.FULL_NAME EMPLOYEE_NAME,
       PEA.EMAIL_ADDRESS,
       PP.PHONE_NUMBER,
       PP.EXTENSION,
       PP.SPEED_DIAL_NUMBER
FROM   PER_PHONES PP,
       PER_EMAIL_ADDRESSES PEA,
       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 = PPNF.PERSON_ID
AND    PAPF.PERSON_ID = PAAF.PERSON_ID
AND    PAPF.MAILING_ADDRESS_ID IS NULL
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
AND    PAPF.PERSON_ID = PP.PERSON_ID
AND    PAPF.PERSON_ID = PEA.PERSON_ID(+)