Oracle Fusion HCM - Query to Fetch Employee Citizenship Details

✅ Purpose:

To retrieve employee citizenship details in Oracle HCM using the relevant tables.

📌 Tables User

Column	Description
EMPLOYEE_NUMBER	Employee's person number
EMPLOYEE_NAME	Full name from PER_PERSON_NAMES_F
EMAIL	Work email address from PER_EMAIL_ADDRESSES
CITIZENSHIP_COUNTRY	Country of citizenship from PER_CITIZENSHIPS
CITIZENSHIP_START_DATE / END_DATE	Validity period of citizenship record
NATIONAL_ID	National Identifier (optional via PER_PERSONS if needed)

✅ Query:

SELECT PAPF.PERSON_NUMBER AS "Partner Employee id",
PPNF.DISPLAY_NAME as "Partner Name",
PC.CITIZENSHIP_ID,
PC.LEGISLATION_CODE,
PER_EXTRACT_UTILITY.GET_TERRITORY_NAME(PC.LEGISLATION_CODE) AS "Citzenship", 
TO_CHAR(PC.DATE_FROM,'MM/DD/YYYY') as "Date From",
TO_CHAR(PC.DATE_TO,'MM/DD/YYYY') AS "Date To",
PC.CITIZENSHIP_STATUS as "Citizenship Status",
pea.EMAIL_ADDRESS AS "Partner Work Email"

FROM PER_CITIZENSHIPS   PC,
PER_ALL_PEOPLE_F  PAPF,
PER_ALL_ASSIGNMENTS_M PAAM,
PER_PERSON_NAMES_F PPNF,
PER_EMAIL_ADDRESSES PEA

WHERE 1=1
  AND PC.PERSON_ID (+) = PAPF.PERSON_ID
  AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAPF.PERSON_ID = PPNF.PERSON_ID
  AND PPNF.NAME_TYPE = 'GLOBAL'
  AND PAAM.ASSIGNMENT_TYPE IN ('E')
AND PAAM.PRIMARY_FLAG = 'Y'
AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
  AND PAAM.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')
AND PAPF.person_id =PEA.person_id 
  and PEA.EMAIL_TYPE ='W1'
  AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE)
  AND TRUNC(SYSDATE) BETWEEN TRUNC(PPNF.EFFECTIVE_START_DATE) AND TRUNC(PPNF.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAAM.EFFECTIVE_START_DATE) AND TRUNC(PAAM.EFFECTIVE_END_DATE)