Employee Contact Relationships (SQL Script)

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

This Employee Contact Relationships query displays contact relationship details for employees within each organization as of the current date, helping HR teams manage personal, emergency, and dependent contact records for compliance and operational readiness.

#sqlquery

SELECT 
       --SQL4Fusion (An Orbit Analytics Project)
       --Retrieve employee contacts and their relationship types within the organization.
       HAOUT.NAME ORGANIZATION_NAME,
       PPNFV.FULL_NAME PERSON_NAME,
       PPNFVC.FULL_NAME CONTACT_PERSON_NAME,
	   PCRF.CONTACT_TYPE,
       PCRF.PERSONAL_FLAG,
       PCRF.PRIMARY_CONTACT_FLAG,
       PCRF.BENEFICIARY_FLAG,
       PCRF.STATUTORY_DEPENDENT,
       PCRF.EXISTING_PERSON,
       PCRF.BONDHOLDER_FLAG,
       PCRF.CONT_INFORMATION_CATEGORY,
       PCRF.DEPENDENT_FLAG,
       PCRF.EMERGENCY_CONTACT_FLAG,
       PCRF.EFFECTIVE_START_DATE,
       PCRF.EFFECTIVE_END_DATE
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 PPNFVC -- This table used to fetch the contact relation info
WHERE     1=1
       AND PAPF.PERSON_ID = PAAM.PERSON_ID
       AND PAPF.PERSON_ID = PPNFV.PERSON_ID
       AND PAPF.PERSON_ID = PCRF.PERSON_ID
       AND PPNFVC.PERSON_ID = PCRF.CONTACT_PERSON_ID
       AND PAAM.ORGANIZATION_ID = HAOUT.ORGANIZATION_ID
       AND HAOUT.LANGUAGE = USERENV ('LANG')
       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