• Reports Conversion
  • Oracle HCM Analytics
  • Oracle Health Analytics
  • Services
    • ETL SolutionsETL Solutions
    • Performed multiple ETL pipeline building and integrations.

    • Data Lake IconData Lake
    • Experienced in building Data Lakes with Billions of records.

    • e-flyer migration solutions menuMigration Solutions
    • Modernize BI reporting with automated migration.

    • BI Products MenuBI products
    • Successfully delivered multiple BI product-based projects.

    • Legacy Scripts MenuLegacy scripts
    • Successfully transitioned legacy scripts from Mainframes to Cloud.

    • AI/ML Solutions MenuAI ML Consulting
    • Expertise in building innovative AI/ML-based projects.

  • Contact Us
  • Blogs
  • Oracle HCM Insights
  • Oracle HCM Tables And Views Reporting Analysis

Contents

1/9
What Are Oracle HCM Tables and Views Important Oracle HCM Tables for Reporting Oracle HCM Tables by Functional Module Oracle HCM Table Relationships and Joins Understanding Effective-Dated Oracle HCM Tables Oracle HCM Reporting Methods Practical Oracle HCM Reporting Examples Common Oracle HCM Reporting Challenges and Best Practices Frequently Asked Questions
  • 02 Nov 2026

Oracle HCM Tables and Views: A Complete Guide to Reporting and Analysis

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.

Quick Summary

Oracle HCM tables store transactional and workforce data, while views provide logical ways to access and present information from one or more underlying tables. For example, PER_ALL_PEOPLE_F stores person-related information, while PER_ALL_ASSIGNMENTS_M contains employment and assignment details such as department, job, position, and location.

Accurate reporting requires more than identifying the correct table. You must also understand table relationships, effective-dated records, assignment types, data security, and the reporting interface being used. Oracle provides an official Tables and Views for HCM reference to help identify supported objects, columns, keys, and relationships.
Oracle HCM tables and views
  • Share Post:
  • LinkedIn Icon
  • Twitter Icon

What Are Oracle HCM Tables and Views?

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
PurposeStore application dataPresent data for access and reporting
StructureRows and columnsLogical representation of data
Data storageStore underlying recordsGenerally retrieve data from underlying objects
Reporting useSource of detailed recordsCan simplify access to related information
ExamplePER_ALL_ASSIGNMENTS_MOracle-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.

Important Oracle HCM Tables for Reporting

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_FGlobal HRPerson and worker-related information
PER_PERSON_NAMES_FGlobal HREffective-dated person names
PER_PERSONSGlobal HRCore person information
PER_ALL_ASSIGNMENTS_MGlobal HREmployment terms and assignment details
PER_EMAIL_ADDRESSESGlobal HRPerson email information
PER_PHONESGlobal HRPerson phone information
PER_PERIODS_OF_SERVICEWorkforce ManagementEmployment period information
PAY_PAYROLL_ACTIONSPayrollPayroll process and action information
PAY_COSTING_RESULTSPayrollPayroll costing information

2.1 PER_ALL_PEOPLE_F – Person 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:

  • PERSON_ID – Person identifier.
  • PERSON_NUMBER – Person number.
  • EFFECTIVE_START_DATE – Beginning of the record's effective period.
  • EFFECTIVE_END_DATE – End of the record's effective period.

Reporting use cases:

  • Employee and worker directories.
  • Person-level workforce reports.
  • Employee information analysis.
  • Person record history.

Do not assume that PERSON_ID alone identifies one unique row across all effective-dated records.

2.2 PER_PERSON_NAMES_F – Person Names

This table stores person name information across effective periods. It can be related to person records using PERSON_ID.

Reporting use cases:

  • Employee name displays.
  • Workforce directories.
  • Person information reports.

Name types and effective dates should be considered when selecting the appropriate name record.

2.3 PER_ALL_ASSIGNMENTS_M – Employment and Assignment Details

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:

  • ASSIGNMENT_ID – Assignment identifier.
  • PERSON_ID – Person associated with the assignment.
  • ASSIGNMENT_TYPE – Identifies the type of assignment record.
  • ASSIGNMENT_NUMBER – Assignment or terms number.
  • ORGANIZATION_ID – Department or organization identifier.
  • JOB_ID – Job identifier.
  • LOCATION_ID – Location identifier.
  • POSITION_ID – Position identifier.
  • EFFECTIVE_START_DATE and EFFECTIVE_END_DATE – Effective period.

Reporting use cases:

  • Department-wise employee reports.
  • Job and position analysis.
  • Workforce movement reporting.
  • Assignment status analysis.
  • Employee assignment history.

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.

2.4 Contact and Employment Tables

Additional tables support more specific reporting requirements:

  • PER_EMAIL_ADDRESSES – Email-related information.
  • PER_PHONES – Phone-related information.
  • PER_PERIODS_OF_SERVICE – Employment period information.

These objects can enrich workforce reports, but you must verify the correct join conditions, effective dates, and access permissions before combining them.

2.5 Payroll Tables

Payroll reporting uses a separate set of objects to support payroll processing and costing analysis.

Examples include:

  • PAY_PAYROLL_ACTIONS – Payroll action and process information.
  • PAY_COSTING_RESULTS – Payroll costing information.

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 HCM Tables by Functional Module

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 ResourcesPerson, employment, assignment, department, jobEmployee directories, headcount, workforce structure
Workforce ManagementWorker and assignment informationWorkforce status and organizational analysis
Global PayrollPayroll actions, costing, payroll resultsPayroll summaries and costing reports
BenefitsBenefit plans and enrollment informationEnrollment and benefits analysis
Absence ManagementAbsence and leave recordsLeave balances, absence trends
CompensationSalary and compensation informationCompensation reviews and pay analysis
RecruitingCandidate, requisition, and hiring informationRecruitment funnel and time-to-hire
Talent ManagementGoals, profiles, and talent informationTalent reviews and development analysis
Performance ManagementPerformance documents and evaluationsPerformance review reporting
Time and LaborTime entries and time-related informationAttendance 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 Table Relationships and Joins

Oracle HCM tables connect through identifiers that let you combine related information for reporting.

Some commonly used identifiers include:

Identifier Reporting relationship
PERSON_IDConnects person information with related person and workforce records
ASSIGNMENT_IDIdentifies an employment or assignment record
ORGANIZATION_IDIdentifies an organization or department
JOB_IDIdentifies a job
LOCATION_IDIdentifies a work location
PERIOD_OF_SERVICE_IDIdentifies 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.

Example: Person and Assignment Relationship

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.

Understanding Effective-Dated Oracle HCM Tables

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:

  • EFFECTIVE_START_DATE
  • EFFECTIVE_END_DATE

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.

Why Effective-Date Filters Matter

Without appropriate date conditions, a report may include multiple historical versions of the same record. This can result in:

  • Inflated employee counts.
  • Duplicate assignment records.
  • Incorrect department totals.
  • Inaccurate workforce movement analysis.
  • Inconsistent historical reporting.

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.

Oracle HCM Reporting Methods

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
OTBIBusiness-user reporting and analysisWorkforce dashboards and operational reports
BI PublisherFormatted and parameterized reportsDetailed HR, payroll, and document-style reports
HCM ExtractsStructured data extractionDownstream integrations and scheduled data feeds
SQL-based reportingTechnical data analysis where supportedCustom data analysis and reporting solutions
Oracle Fusion Data Intelligence (FDI)Enterprise analyticsCross-functional analytics and prebuilt analytical models

OTBI

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

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

HCM Extracts support the extraction of HCM data for downstream systems, integrations, and scheduled data delivery.

SQL-Based Reporting

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

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.

Practical Oracle HCM Reporting Examples

The following examples illustrate common reporting requirements and the types of data involved.

Employee Headcount Report

Objective: Calculate the number of workers within a defined reporting population.

Relevant data:

  • Person identifiers.
  • Assignment identifiers.
  • Assignment types.
  • Assignment status.
  • Effective dates.
  • Primary assignment indicators.

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.

Department-Wise Employee Report

Objective: Analyze workforce distribution across departments.

Relevant data:

  • Person information.
  • Assignment information.
  • Organization or department identifiers.
  • Effective-dated department details.

The report should use the assignment effective on the selected reporting date and apply the correct department relationship.

Employee Contact Report

Objective: Create an authorized employee directory.

Relevant data:

  • Person identifiers.
  • Person names.
  • Email information.
  • Phone information.

Apply appropriate access restrictions because contact and personal information may be sensitive.

Payroll Cost Analysis

Objective: Analyze payroll processing and costing information.

Relevant data:

  • Payroll actions.
  • Costing results.
  • Relevant payroll and employment relationships.

Validate payroll calculations and reporting definitions with the organization's payroll team.

Recruitment Analysis

Objective: Monitor recruitment activity and hiring performance.

Relevant data may include:

  • Requisitions.
  • Candidates.
  • Job applications.
  • Offers.
  • Hiring events and dates.

The exact objects depend on the Recruiting configuration and the reporting interface.

Common Oracle HCM Reporting Challenges and Best Practices

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 recordsApply effective-date and assignment filters
Incorrect headcountDefine the worker population and count people rather than assignments when appropriate
Complex table relationshipsDocument join keys and validate record counts
Historical reporting errorsUse the correct reporting date and effective-date logic
Inconsistent report resultsStandardize business definitions and filters
Restricted data accessUse authorized reporting interfaces and security roles
Changing Oracle data structuresValidate objects against the documentation for the current release

Consider Data Security

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.

Use Oracle's Official Data Dictionary

The Oracle HCM Tables and Views documentation should be a primary reference when validating:

  • Table and view definitions.
  • Column names and data types.
  • Primary keys and indexes.
  • Relationships and references.
  • Module-specific data structures.

This reduces the risk of building reports using incorrect assumptions or outdated table information.

How DataTerrain Supports Oracle HCM Analytics and Reporting

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.

Key Takeaways

  • Oracle HCM organizes workforce information across functional areas such as Global HR, Payroll, Benefits, Recruiting, and Talent Management.
  • PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_M are important starting points for many workforce reporting requirements.
  • Effective-date filters are essential when working with date-tracked HCM records.
  • Table relationships commonly use identifiers such as PERSON_ID, ASSIGNMENT_ID, and ORGANIZATION_ID.
  • OTBI, BI Publisher, HCM Extracts, and Oracle Fusion Data Intelligence support different reporting and data extraction requirements.
  • Data security and reporting access must be considered before designing queries or integrations.

Conclusion

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.

Related Resources

Explore these DataTerrain resources to learn more about Oracle HCM reporting and analytics:

  • Oracle HCM Analytics – Learn about analytics capabilities for turning Oracle HCM information into workforce insights.
  • Oracle HCM Recruitment Analytics: OTBI, Taleo & ORC – Explore recruitment reporting and the integration of Oracle HCM reporting data.
  • Oracle HCM Benefits Paid Report – Understand a practical Oracle HCM benefits reporting use case.

Frequently Asked Questions

1. What are Oracle HCM tables?
Oracle HCM tables are database objects that store workforce and HR-related information, including person records, employment assignments, payroll, benefits, recruitment, and talent data.
2. What is the difference between Oracle HCM tables and views?
Tables store underlying records in rows and columns. Views provide a logical representation of data, potentially combining information from multiple tables to simplify access and reporting.
3. Which Oracle HCM tables are commonly used for employee reporting?
PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_M are important starting points. Person names, contact information, organization, job, and location details may require additional related objects.
4. Why are effective dates important in Oracle HCM reporting?
HCM records can have multiple versions across different effective periods. Applying the correct effective-date conditions helps retrieve the record relevant to the reporting date and prevents historical records from inflating report results.
5. Can Oracle HCM tables be accessed directly using SQL?
Access depends on the Oracle Fusion environment, supported reporting interface, security permissions, and configuration. Do not assume that every underlying application table is directly queryable. OTBI, BI Publisher, HCM Extracts, and other supported methods serve different requirements.
6. Which reporting tools can be used with Oracle HCM?
Common options include OTBI, BI Publisher, HCM Extracts, supported SQL-based reporting environments, and Oracle Fusion Data Intelligence. The right choice depends on the required report format, data access, complexity, and analytics objectives.
Categories
  • All
  • BI Insights Hub
  • Data Analytics
  • ETL Tools
  • Oracle HCM Insights
  • Legacy Reports conversion
  • AI and ML Hub

Ready to initiate your BI Migration Journey?

Start Now
Customer Stories
  • All
  • Data Analytics
  • Reports conversion
  • Jaspersoft
  • Oracle HCM
Recent posts
  • Oracle HCM tables and views
    Oracle HCM Tables and Views: A Complete...
  • oracle cloud fusion hcm
    Oracle Cloud Fusion HCM: HR, Payroll...
  • Generative AI in Oracle HCM
    How Generative AI in Oracle HCM simplifies...
Connect with Us
  • About
  • Careers
  • Privacy Policy
  • Terms and condtions
Sources
  • Customer stories
  • Blogs
  • Tools
  • News
  • Videos
  • Events
Services
  • Reports Conversion
  • ETL Solutions
  • Migration Solutions
  • Data Lake
  • Legacy Scripts
  • Oracle HCM Analytics
  • BI Products
  • AI ML Consulting
  • Data Analytics
Get in touch
  • connect@dataterrain.com
  • +1 650-701-1100

Subscribe to newsletter

Enter your email address for receiving valuable newsletters.

logo

© 2026 Copyright by DataTerrain Inc.

  • twitter