This Employee Contact Details query displays contact details (email and phone information) for all active employees and their associated organizations as of today, helping users maintain accurate and up-to-date employee communication records.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Retrieve active employee contact information and organizational details.
-- Employee Information
PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
PPNFV.FULL_NAME EMPLOYEE_NAME,
-- Organization
HAOU.NAME ORGANIZATION,
-- Email Details
PEA.EMAIL_TYPE,
PEA.EMAIL_ADDRESS,
PEA.ATTRIBUTE_CATEGORY EMAIL_CATEGORY,
PEA.DATE_FROM EMAIL_DATE_FROM,
PEA.DATE_TO EMAIL_DATE_TO,
-- Phone Details
PP.PHONE_TYPE,
PP.PHONE_NUMBER,
PP.ATTRIBUTE_CATEGORY PHONE_CATEGORY,
PP.DATE_FROM PHONE_DATE_FROM,
PP.DATE_TO PHONE_DATE_TO,
PP.LEGISLATION_CODE,
PP.AREA_CODE,
PP.COUNTRY_CODE_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 PPNFV,
PER_ALL_ASSIGNMENTS_M PAAM,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
PAPF.PERSON_ID = PPNFV.PERSON_ID
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAAM.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE
AND PAAM.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PPNFV.EFFECTIVE_START_DATE
AND PPNFV.EFFECTIVE_END_DATE
AND PAPF.PERSON_ID = PP.PERSON_ID
AND PAPF.PERSON_ID = PEA.PERSON_ID