Employee Address Details (SQL Script)

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

This Employee Address Details query displays current mailing address details for active employees along with their organization, helping HR teams verify contact information and support organizational reporting.

#sqlquery

SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This SQL query retrieves current employee address and organizational details
  HAOU.NAME ORGANIZATION,
  PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
  PPNF.DISPLAY_NAME EMPLOYEE_NAME,
  PAF.ADDRESS_LINE_1,
  PAF.ADDRESS_LINE_2,
  PAF.ADDRESS_LINE_3,
  PAF.TOWN_OR_CITY "TOWN / CITY",
  PAF.REGION_1,
  PAF.REGION_2,
  PAF.COUNTRY,
  PAF.POSTAL_CODE
FROM
  PER_ALL_PEOPLE_F PAPF,
  (SELECT * FROM PER_PERSON_NAMES_F_V PPNF WHERE TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE) PPNF,
  (SELECT * FROM PER_ADDRESSES_F PAF WHERE TRUNC(SYSDATE) BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE) PAF,
  (SELECT * FROM PER_ALL_ASSIGNMENTS_F PAAF WHERE TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE) PAAF,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  1 = 1
  AND PAPF.PERSON_ID = PPNF.PERSON_ID(+)
  AND PAPF.MAILING_ADDRESS_ID = PAF.ADDRESS_ID(+)
  AND PAAF.PERSON_ID(+) = PAPF.PERSON_ID
   AND PAAF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID(+)
  AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
  AND PAAF.PERSON_ID = PAPF.PERSON_ID
  AND PAAF.ASSIGNMENT_TYPE = 'E'      --Identifies the type of record: either assignment (employee, CWK, applicant, non-workers) or a set of Terms.
  AND PAAF.PRIMARY_FLAG = 'Y'         --If set to 'Yes', record represents the primary assignment associated to the primary Work Relationship and primary set of Employment/Placement Terms.
  AND ASSIGNMENT_STATUS_TYPE = 'ACTIVE'