Employee Religion Information (SQL Script)

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

This Employee Religion Information sql report lists active employees with their religion details, including whether the recorded religion is marked as primary. It also includes the employee’s name, number, assignment status, and associated organization. The report filters out non-standard assignments and ensures only current, valid, and primary employee records are included.

#sqlquery

SELECT 
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves assignment and religion details for active employees in Oracle Fusion HCM.
  PAPF.PERSON_NUMBER,
  PPNF.FULL_NAME EMPLOYEE_NAME,
  PAAM.ASSIGNMENT_NUMBER,
  PAAM.ASSIGNMENT_STATUS_TYPE,
  HAOOU.NAME ORGANIZATION_NAME,
  PR.RELIGION,
  PR.PRIMARY_FLAG RELIGION_PRIMARY_FLAG
FROM 
  PER_PERSON_NAMES_F_V PPNF,
  PER_ALL_ASSIGNMENTS_M PAAM,
  HR_ALL_ORGANIZATION_UNITS HAOOU,
  PER_ALL_PEOPLE_F PAPF,
  PER_RELIGIONS PR
WHERE 
  PPNF.PERSON_ID = PAPF.PERSON_ID
  AND PAPF.PERSON_ID = PAAM.PERSON_ID
  AND PAAM.ASSIGNMENT_TYPE NOT IN ('ET','CT','PT')
  AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
  AND PAAM.ORGANIZATION_ID = HAOOU.ORGANIZATION_ID
  AND PAAM.PRIMARY_FLAG = 'Y'
  AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
  AND PPNF.PERSON_ID = PR.PERSON_ID
  AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
  AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
  AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE;