This SQL query is used in Oracle HCM Cloud to retrieve comprehensive employee and person information from multiple core HCM tables. The query combines personal details, employment information, manager details, contact information, and address data into a single report output.
Consolidated Oracle HCM SQL Query
SELECT PAPF.PERSON_NUMBER, PPT.USER_PERSON_TYPE AS PERSON_TYPE, NAM.TITLE TITLE, NAM.LAST_NAME, nam.middle_names, NAM.FIRST_NAME, TO_CHAR (papf.start_date, 'DD/MM/YYYY') AS START_DATE, TO_CHAR (PPOS.DATE_START, 'DD/MM/YYYY') HIRING_DATE, ( SELECT terr.territory_short_name FROM fnd_territories_vl terr WHERE terr.territory_code = P_PER.COUNTRY_OF_BIRTH ) country_of_birth, TO_CHAR (P_PER.DATE_OF_BIRTH, 'DD/MM/YYYY') AS DATE_OF_BIRTH, ( SELECT MEANING FROM HCM_LOOKUPS SEX_LU WHERE SEX_LU.LOOKUP_TYPE = 'SEX' AND SEX_LU.LOOKUP_CODE = PPLF.SEX ) AS GENDER, ( SELECT MEANING FROM HCM_LOOKUPS MAR WHERE MAR.LOOKUP_TYPE = 'MAR_STATUS' AND MAR.LOOKUP_CODE = PPLF.marital_status ) AS MARITAL_STATUS, JOB.NAME AS JOB_NAME, LOC.TOWN_OR_CITY, HOU.NAME AS DEPARTMENT, ASG.NORMAL_HOURS AS WORKING_HOURS, SUP_NAM.LAST_NAME MGR_LAST_NAME, SUP_NAM.FIRST_NAME MGR_FIRST_NAME, addr.address_line_1, addr.address_line_2, addr.address_line_3, addr.address_line_4, addr.town_or_city address_town, addr.postal_code, addr.country, EMAIL_WORK.EMAIL_ADDRESS, PHONE_WORK.PHONE_NUMBER WORK_PHONE_NUMBER, PHONE_MOBILE.PHONE_NUMBER MOBILE_PHONE_NUMBER FROM PER_PERSONS P_PER, PER_ALL_PEOPLE_F PAPF, PER_PERSON_NAMES_F NAM, PER_PERIODS_OF_SERVICE PPOS, PER_ALL_ASSIGNMENTS_M ASG, PER_PERSON_TYPES_VL PPT, PER_PEOPLE_LEGISLATIVE_F PPLF, HR_ALL_ORGANIZATION_UNITS_X HOU, per_person_addresses_v addr, PER_JOBS_X JOB, HR_LOCATIONS_ALL_X LOC, PER_ASSIGNMENT_SUPERVISORS_F SUP, PER_PERSON_NAMES_F SUP_NAM, PER_EMAIL_ADDRESSES EMAIL_WORK, PER_EMAIL_ADDRESSES EMAIL_PER, PER_PHONES PHONE_WORK, PER_PHONES PHONE_MOBILE WHERE PPOS.PERSON_ID = PAPF.PERSON_ID AND PAPF.PERSON_ID = P_PER.PERSON_ID AND NAM.PERSON_ID = PAPF.PERSON_ID AND TRUNC (SYSDATE) BETWEEN NAM.EFFECTIVE_START_DATE AND NAM.EFFECTIVE_END_DATE AND NAM.NAME_TYPE = 'GLOBAL' AND ASG.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID AND ASG.WORK_TERMS_ASSIGNMENT_ID IS NOT NULL AND TRUNC (SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE AND ASG.EFFECTIVE_LATEST_CHANGE = 'Y' AND ASG.PRIMARY_ASSIGNMENT_FLAG = 'Y' AND PPT.PERSON_TYPE_ID = ASG.PERSON_TYPE_ID AND PPLF.PERSON_ID (+) = PAPF.PERSON_ID AND TRUNC (SYSDATE) BETWEEN PPLF.EFFECTIVE_START_DATE (+) AND PPLF.EFFECTIVE_END_DATE (+) AND addr.person_id (+)=papf.person_id AND addr.address_type(+)='HOME' AND PPLF.LEGISLATION_CODE (+) = PPOS.LEGISLATION_CODE AND HOU.ORGANIZATION_ID (+) = ASG.ORGANIZATION_ID AND TRUNC (SYSDATE) BETWEEN HOU.EFFECTIVE_START_DATE (+) AND HOU.EFFECTIVE_END_DATE (+) AND JOB.JOB_ID (+) = ASG.JOB_ID AND LOC.LOCATION_ID (+) = ASG.LOCATION_ID AND SUP.ASSIGNMENT_ID (+) = ASG.ASSIGNMENT_ID AND TRUNC (SYSDATE) BETWEEN SUP.EFFECTIVE_START_DATE (+) AND SUP.EFFECTIVE_END_DATE (+) AND SUP.MANAGER_TYPE (+) = 'LINE_MANAGER' AND SUP_NAM.PERSON_ID (+) = SUP.MANAGER_ID AND TRUNC (SYSDATE) BETWEEN SUP_NAM.EFFECTIVE_START_DATE (+) AND SUP_NAM.EFFECTIVE_END_DATE (+) AND SUP_NAM.NAME_TYPE (+) = 'GLOBAL' AND EMAIL_WORK.PERSON_ID (+) = PAPF.PERSON_ID AND TRUNC (SYSDATE) BETWEEN EMAIL_WORK.DATE_FROM (+) AND NVL ( EMAIL_WORK.DATE_TO (+), TO_DATE ('4712/12/31', 'YYYY/MM/DD') ) AND EMAIL_WORK.EMAIL_TYPE (+) = 'W1' AND EMAIL_PER.PERSON_ID (+) = PAPF.PERSON_ID AND TRUNC (SYSDATE) BETWEEN EMAIL_PER.DATE_FROM (+) AND NVL ( EMAIL_PER.DATE_TO (+), TO_DATE ('4712/12/31', 'YYYY/MM/DD') ) AND EMAIL_PER.EMAIL_TYPE (+) = 'H1' AND PHONE_WORK.PERSON_ID (+) = PAPF.PERSON_ID AND PHONE_WORK.PHONE_TYPE (+) = 'W1' AND PHONE_MOBILE.PERSON_ID (+) = PAPF.PERSON_ID AND PHONE_MOBILE.PHONE_TYPE (+) = 'WM' AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE AND TRUNC (SYSDATE) BETWEEN addr.EFFECTIVE_START_DATE(+) AND addr.EFFECTIVE_END_DATE(+)
Entity-Relationship Diagram
PER_EMAIL_ADDRESSES
(Work)
(Work)
PERSON_ID
EMAIL_ADDRESS
EMAIL_TYPE
PER_EMAIL_ADDRESSES
(Personal)
(Personal)
PERSON_ID
EMAIL_TYPE
PER_PERSON_ADDRESSES_V
PERSON_ID
ADDRESS_LINE_1
TOWN_OR_CITY
POSTAL_CODE
COUNTRY
PER_PHONES
(Work)
(Work)
PERSON_ID
PHONE_NUMBER
PHONE_TYPE
PER_PHONES
(Mobile)
(Mobile)
PERSON_ID
PHONE_NUMBER
PHONE_TYPE
PER_PERSONS
PERSON_ID
DATE_OF_BIRTH
COUNTRY_OF_BIRTH
PER_ALL_PEOPLE_F
PERSON_ID
PERSON_NUMBER
START_DATE
PER_PERSON_NAMES_F
(Employee)
(Employee)
PERSON_ID
TITLE
LAST_NAME
MIDDLE_NAMES
FIRST_NAME
PER_PERSON_TYPES_VL
PERSON_TYPE_ID
USER_PERSON_TYPE
PER_PERIODS_OF_SERVICE
PERIOD_OF_SERVICE_ID
PERSON_ID
DATE_START
LEGISLATION_CODE
PER_ALL_ASSIGNMENTS_M
ASSIGNMENT_ID
PERIOD_OF_SERVICE_ID
PERSON_TYPE_ID
ORGANIZATION_ID
JOB_ID
LOCATION_ID
NORMAL_HOURS
PER_PEOPLE_LEGISLATIVE_F
PERSON_ID
LEGISLATION_CODE
SEX
MARITAL_STATUS
HR_ALL_ORGANIZATION_UNITS_X
ORGANIZATION_ID
NAME
PER_JOBS_X
JOB_ID
NAME
HR_LOCATIONS_ALL_X
LOCATION_ID
TOWN_OR_CITY
PER_ASSIGNMENT_SUPERVISORS_F
ASSIGNMENT_ID
MANAGER_ID
PER_PERSON_NAMES_F
(Supervisor)
(Supervisor)
PERSON_ID
LAST_NAME
FIRST_NAME
What Information Does This SQL Retrieve?
The query returns the following employee details from Oracle HCM:
🪪
Employee Number👤
Person Type🏷️
Employee Name🎂
Date of Birth🚻
Gender💍
Marital Status🌍
Country of Birth📅
Hire Date🚀
Enterprise Start Date💼
Job Name🏢
Department📍
Work Location⏱️
Working Hours👔
Manager Name🏠
Home Address✉️
Work Email Address📞
Work Phone Number📱
Mobile NumberMain Tables Used in the Query
| Table Name | Description |
|---|---|
| 📊 PER_ALL_PEOPLE_F (PAPF) | This is the primary employee table in Oracle HCM. It stores core employee records and effective-dated person information. |
| 🧬 PER_PERSONS (P_PER) | Contains basic person-level details such as date of birth and country of birth. |
| 📝 PER_PERSON_NAMES_F (NAM) | Stores employee names including first name, middle name, last name, and title. |
| 🛠️ PER_ALL_ASSIGNMENTS_M (ASG) | Contains assignment information such as department, job, location, and working hours. |
| ⏳ PER_PERIODS_OF_SERVICE (PPOS) | Stores employment period details including hire date. |
| ⚖️ PER_PEOPLE_LEGISLATIVE_F (PPLF) | Stores legislative information such as gender and marital status. |
| 🏢 HR_ALL_ORGANIZATION_UNITS_X (HOU) | Used to fetch department or organization name. |
| 👔 PER_JOBS_X (JOB) | Used to retrieve employee job details. |
| 🗺️ HR_LOCATIONS_ALL_X (LOC) | Provides employee work location information. |
| 👥 PER_ASSIGNMENT_SUPERVISORS_F (SUP) | Stores reporting manager relationships. |
| 📧 PER_EMAIL_ADDRESSES | Used to retrieve work and personal email addresses. |
| 📞 PER_PHONES | Used to retrieve work phone and mobile numbers. |
| 🏠 PER_PERSON_ADDRESSES_V | Used to retrieve employee home address details. |
Why This SQL Is Useful
This Oracle HCM SQL query simplifies development and administration across a variety of crucial use cases:
- 📈 Employee master reports
- 📥 HR data extracts
- 📊 BI Publisher reports
- 🔄 HCM outbound integrations
- 🛡️ Audit reporting
- 📇 Employee directory reports
- 🔄 Data migration reconciliation
- ☁️ OIC outbound integrations
💡 Benefit: It helps developers avoid joining multiple Oracle HCM tables separately by providing a ready-to-use consolidated query.
