Employee Ethnicity Information (Fusion HCM SQL Query)

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

This Employee Ethnicity Information query displays ethnicity details, assignment status, and organizational information for active primary employees, helping users to analyze workforce diversity and organizational distribution.

#sqlquery

SELECT  
     --SQL4Fusion (An Orbit Analytics Project)
     --Retrieve active employees assignment and ethnicity details.
     PAPF.PERSON_NUMBER,
	 PPNFV.FULL_NAME EMPLOYEE_NAME,
	 PAAM.ASSIGNMENT_NUMBER,
	 PAAM.ASSIGNMENT_STATUS_TYPE,
	 HAOU.NAME ORGANIZATION_NAME,
	 -- PE.ETHNICITY ETHNICITY_CODE,
	 FLVT.MEANING ETHNICITY
FROM 
	 PER_PERSON_NAMES_F_V PPNFV,
	 PER_ALL_ASSIGNMENTS_M PAAM,
	 HR_ALL_ORGANIZATION_UNITS HAOU,
	 PER_ALL_PEOPLE_F PAPF,
	 PER_ETHNICITIES PE,
	 FND_LOOKUP_VALUES_TL FLVT
WHERE 
	 PPNFV.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 = HAOU.ORGANIZATION_ID
	 AND PE.ETHNICITY=FLVT.LOOKUP_CODE
	 AND FLVT.LOOKUP_TYPE='PER_ETHNICITY'
	 AND FLVT.LANGUAGE='US'
	 AND PAAM.PRIMARY_FLAG = 'Y'
	 AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
	 AND PPNFV.PERSON_ID=PE.PERSON_ID
	 AND SYSDATE BETWEEN PPNFV.EFFECTIVE_START_DATE
	 AND PPNFV.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;