This Employee Emergency Contacts query displays emergency contact details including organization name, person name, contact person name, email addresses, and phone numbers for employees currently assigned to organizations, helping HR and safety teams maintain up-to-date emergency contact information.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Retrieve emergency contacts information of an employee.
HAOUT.NAME ORGANIZATION_NAME,
PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
PPNFV.FULL_NAME EMPLOYEE_NAME,
PPNFVCI.FULL_NAME CONTACT_PERSON_NAME,
PEA.EMAIL_ADDRESS,
PEA.EMAIL_TYPE,
PEA.ATTRIBUTE_CATEGORY EMAIL_CATEGORY,
-- PEA.DATE_FROM EMAIL_DATE_FROM,
-- PEA.DATE_TO EMAIL_DATE_TO,
PP.PHONE_NUMBER,
PP.PHONE_TYPE,
PP.AREA_CODE,
PP.COUNTRY_CODE_NUMBER,
PP.EXTENSION,
PP.SPEED_DIAL_NUMBER
FROM
PER_CONTACT_RELSHIPS_F PCRF,
HR_ALL_ORGANIZATION_UNITS_TL HAOUT,
PER_PERSON_NAMES_F_V PPNFV,
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_M PAAM,
PER_PERSON_NAMES_F_V PPNFVCI, -- This table used to fetch the contact info.
PER_PHONES PP,
PER_EMAIL_ADDRESSES PEA
WHERE
PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAPF.PERSON_ID = PPNFV.PERSON_ID
AND PCRF.EMERGENCY_CONTACT_FLAG='Y'
AND PAPF.PERSON_ID = PCRF.PERSON_ID
AND PPNFVCI.PERSON_ID = PCRF.CONTACT_PERSON_ID
AND PAAM.ORGANIZATION_ID = HAOUT.ORGANIZATION_ID
AND HAOUT.LANGUAGE = USERENV ('LANG')
AND PPNFVCI.PERSON_ID = PP.PERSON_ID
AND PPNFVCI.PERSON_ID = PEA.PERSON_ID(+)
AND PPNFV.NAME_TYPE = 'GLOBAL'
AND PPNFVCI.NAME_TYPE = 'GLOBAL'
AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PPNFV.EFFECTIVE_START_DATE
AND PPNFV.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE
AND PAAM.EFFECTIVE_END_DATE