Oracle Fusion Cloud Human Capital Management (HCM) stores workforce information across interconnected tables and views covering employee records, assignments, departments, payroll, benefits, recruitment, and talent management. Understanding how these objects relate is essential for building accurate HR reports, workforce dashboards, payroll analysis, and business intelligence solutions.
This guide explains the important Oracle HCM tables and views, their reporting purposes, common relationships, effective-date considerations, and practical approaches to extracting and analyzing HCM data.
Oracle Fusion HCM uses a structured data model to store and organize information generated by HR processes.
Oracle HCM tables store data in rows and columns. They contain records relating to workers, assignments, payroll, departments, benefits, recruitment, and other HR functions.
Oracle HCM views provide a logical representation of data from one or more tables. They can simplify reporting by presenting related information through a defined database object.
| Aspect | Tables | Views |
|---|---|---|
| Purpose | Store application data | Present data for access and reporting |
| Structure | Rows and columns | Logical representation of data |
| Data storage | Store underlying records | Generally retrieve data from underlying objects |
| Reporting use | Source of detailed records | Can simplify access to related information |
| Example | PER_ALL_ASSIGNMENTS_M | Oracle-delivered reporting or secured views |
The appropriate object depends on the reporting requirement, available access, and Oracle's supported reporting mechanisms. Not every underlying application table is directly available through every reporting interface.
Oracle's official Tables and Views for HCM documentation provides module-wise reference information, including object definitions and technical details.
The following tables are useful starting points when exploring common workforce reporting requirements. Always confirm the current object definition and available columns in the documentation for your Oracle release.
| Table | Functional area | Reporting purpose |
|---|---|---|
| PER_ALL_PEOPLE_F | Global HR | Person and worker-related information |
| PER_PERSON_NAMES_F | Global HR | Effective-dated person names |
| PER_PERSONS | Global HR | Core person information |
| PER_ALL_ASSIGNMENTS_M | Global HR | Employment terms and assignment details |
| PER_EMAIL_ADDRESSES | Global HR | Person email information |
| PER_PHONES | Global HR | Person phone information |
| PER_PERIODS_OF_SERVICE | Workforce Management | Employment period information |
| PAY_PAYROLL_ACTIONS | Payroll | Payroll process and action information |
| PAY_COSTING_RESULTS | Payroll | Payroll costing information |
PER_ALL_PEOPLE_F is a key table for person-related reporting. It contains person records and uses effective dating, meaning a person can have multiple records representing different effective periods.
Commonly relevant columns include:
Reporting use cases:
Do not assume that PERSON_ID alone identifies one unique row across all effective-dated records.
This table stores person name information across effective periods. It can be related to person records using PERSON_ID.
Reporting use cases:
Name types and effective dates should be considered when selecting the appropriate name record.
PER_ALL_ASSIGNMENTS_M stores employment and placement terms, as well as assignment-level information. It is date-tracked and supports multiple changes within a day.
Important columns include:
Reporting use cases:
The assignment table's primary key includes multiple columns, including ASSIGNMENT_ID, effective dates, EFFECTIVE_LATEST_CHANGE, and EFFECTIVE_SEQUENCE. This is important when building queries that must distinguish between historical and current records. Oracle's table reference provides the detailed definition.
Additional tables support more specific reporting requirements:
These objects can enrich workforce reports, but you must verify the correct join conditions, effective dates, and access permissions before combining them.
Payroll reporting uses a separate set of objects to support payroll processing and costing analysis.
Examples include:
Payroll reporting may involve additional objects for payroll relationships, element entries, balances, and payment information. The exact objects depend on the report, payroll configuration, and reporting interface.
Oracle's HCM data model covers multiple functional areas. The official documentation organizes its table and view reference by modules, including Global Human Resources, Global Payroll, Benefits, Recruiting, Performance Management, and Time and Labor. View Oracle's module-wise reference.
| HCM module | Data commonly analyzed | Reporting requirements |
|---|---|---|
| Global Human Resources | Person, employment, assignment, department, job | Employee directories, headcount, workforce structure |
| Workforce Management | Worker and assignment information | Workforce status and organizational analysis |
| Global Payroll | Payroll actions, costing, payroll results | Payroll summaries and costing reports |
| Benefits | Benefit plans and enrollment information | Enrollment and benefits analysis |
| Absence Management | Absence and leave records | Leave balances, absence trends |
| Compensation | Salary and compensation information | Compensation reviews and pay analysis |
| Recruiting | Candidate, requisition, and hiring information | Recruitment funnel and time-to-hire |
| Talent Management | Goals, profiles, and talent information | Talent reviews and development analysis |
| Performance Management | Performance documents and evaluations | Performance review reporting |
| Time and Labor | Time entries and time-related information | Attendance and workforce time analysis |
This is a functional overview, not a complete table inventory. For implementation, identify the exact objects and columns in Oracle's documentation for the relevant module and release.
Oracle HCM tables connect through identifiers that let you combine related information for reporting.
Some commonly used identifiers include:
| Identifier | Reporting relationship |
|---|---|
| PERSON_ID | Connects person information with related person and workforce records |
| ASSIGNMENT_ID | Identifies an employment or assignment record |
| ORGANIZATION_ID | Identifies an organization or department |
| JOB_ID | Identifies a job |
| LOCATION_ID | Identifies a work location |
| PERIOD_OF_SERVICE_ID | Identifies an employment period |
For example, an employee report may combine person information with assignment details using PERSON_ID. You can then incorporate department, job, and location information through the corresponding identifiers.
The following is a simplified SQL pattern for examining person and assignment records:
SELECT
papf.person_id,
papf.person_number,
paam.assignment_id,
paam.assignment_number,
paam.assignment_type,
paam.organization_id,
paam.job_id,
paam.location_id
FROM
per_all_people_f papf
JOIN
per_all_assignments_m paam
ON papf.person_id = paam.person_id
WHERE
TRUNC(SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date
AND paam.effective_end_date;
This example demonstrates the person-to-assignment relationship. It is not a complete production headcount query. Assignment type, status, primary assignment, latest-change logic, and security requirements must be added according to the reporting definition.
Effective dating is one of the most important concepts in Oracle HCM reporting.
A date-tracked table can contain multiple records for the same person or assignment because workforce information changes over time. For example, an employee may change departments, jobs, locations, or employment status.
Each effective-dated record generally has:
To retrieve a record effective on the current date, a common starting filter is:
TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE
For a historical report, replace SYSDATE with the reporting date or a suitable date parameter.
Without appropriate date conditions, a report may include multiple historical versions of the same record. This can result in:
Important: The date-range condition is a simplified pattern. Some tables, particularly assignment-related objects, may require additional logic involving assignment type, EFFECTIVE_LATEST_CHANGE, EFFECTIVE_SEQUENCE, primary assignment, and status. The correct logic depends on whether the report needs the record effective on a particular date, the latest entered change, or a historical transaction view.
Oracle's assignment table documentation describes its date-tracked structure and multiple changes within a day. Review the official assignment table definition.
Identifying the right table is only one part of building a report. You must also choose a reporting method that supports the required data, level of detail, access controls, and output format.
| Reporting method | Best suited for | Typical use |
|---|---|---|
| OTBI | Business-user reporting and analysis | Workforce dashboards and operational reports |
| BI Publisher | Formatted and parameterized reports | Detailed HR, payroll, and document-style reports |
| HCM Extracts | Structured data extraction | Downstream integrations and scheduled data feeds |
| SQL-based reporting | Technical data analysis where supported | Custom data analysis and reporting solutions |
| Oracle Fusion Data Intelligence (FDI) | Enterprise analytics | Cross-functional analytics and prebuilt analytical models |
Oracle Transactional Business Intelligence (OTBI) provides subject areas for business users to create analyses and dashboards. It is useful for operational reporting without requiring users to build SQL queries against underlying tables.
BI Publisher supports formatted reports, parameterized queries, and scheduled report delivery. It is useful when organizations need detailed layouts, complex report outputs, or structured documents.
HCM Extracts support the extraction of HCM data for downstream systems, integrations, and scheduled data delivery.
SQL can support detailed data analysis where the relevant reporting environment provides authorized access. Direct access to application database tables should not be assumed in Oracle Fusion Cloud. The available objects and permitted query methods depend on the environment and Oracle-supported interfaces.
Oracle Fusion Data Intelligence supports broader analytical use cases by bringing enterprise application data into an analytics environment. It can help organizations build workforce insights and combine information across business functions.
The following examples illustrate common reporting requirements and the types of data involved.
Objective: Calculate the number of workers within a defined reporting population.
Relevant data:
The reporting definition must specify whether to include contractors, pending workers, and multiple assignments. Use a distinct person count where the metric is intended to represent people rather than assignments.
Objective: Analyze workforce distribution across departments.
Relevant data:
The report should use the assignment effective on the selected reporting date and apply the correct department relationship.
Objective: Create an authorized employee directory.
Relevant data:
Apply appropriate access restrictions because contact and personal information may be sensitive.
Objective: Analyze payroll processing and costing information.
Relevant data:
Validate payroll calculations and reporting definitions with the organization's payroll team.
Objective: Monitor recruitment activity and hiring performance.
Relevant data may include:
The exact objects depend on the Recruiting configuration and the reporting interface.
Working with Oracle HCM data can become complex when reports combine multiple modules, effective-dated records, and security rules.
| Common challenge | Recommended practice |
|---|---|
| Duplicate employee records | Apply effective-date and assignment filters |
| Incorrect headcount | Define the worker population and count people rather than assignments when appropriate |
| Complex table relationships | Document join keys and validate record counts |
| Historical reporting errors | Use the correct reporting date and effective-date logic |
| Inconsistent report results | Standardize business definitions and filters |
| Restricted data access | Use authorized reporting interfaces and security roles |
| Changing Oracle data structures | Validate objects against the documentation for the current release |
Oracle HCM contains confidential employee and payroll information. Access to reporting data can depend on roles, privileges, security profiles, and the reporting interface.
Do not assume that an object or column is available to every user simply because it appears in a technical reference. Confirm the relevant security configuration and use approved access methods.
The Oracle HCM Tables and Views documentation should be a primary reference when validating:
This reduces the risk of building reports using incorrect assumptions or outdated table information.
Organizations often need to combine Oracle HCM data with enterprise reporting platforms, modern analytics environments, and other business applications. This can involve report modernization, data integration, migration planning, and dashboard development.
DataTerrain helps organizations modernize legacy reporting environments through automated migration solutions. Its automated BI migration solutions support migrations across different source and target platforms, focusing on preserving business logic and validating converted reports before deployment.
For organizations modernizing reporting environments, DataTerrain's automated BI report conversion services can support BI report migration initiatives.
Explore DataTerrain's data and analytics solutions to understand its broader capabilities.
If you are evaluating Oracle HCM reporting, analytics modernization, or migration requirements, contact DataTerrain to discuss your project needs.
Oracle HCM tables and views provide the foundation for workforce reporting, payroll analysis, recruitment insights, and enterprise HR analytics. Accurate reporting depends on understanding the data model, identifying the right objects, joining related records correctly, applying effective-date logic, and respecting data security.
By using Oracle's official Tables and Views reference and selecting the right reporting method, organizations can build more consistent, reliable HCM reports. For organizations looking to modernize their reporting and analytics environments, DataTerrain can help evaluate reporting conversion and data modernization requirements.
Explore these DataTerrain resources to learn more about Oracle HCM reporting and analytics: