Legal Entity Table in Fusion HCM

Share

 

Introduction

When working with Legal Entity Table in Oracle Fusion HCM, many consultants initially assume it’s purely a functional setup concept. But in real implementations—especially reporting, integrations, and data migration—the Legal Entity table becomes a critical technical component.

In Oracle Fusion Cloud (26A release), understanding how Legal Entities are stored in backend tables is essential for:

  • OTBI / BI Publisher reporting
  • HDL and HCM Extract validations
  • OIC integrations with external payroll or finance systems
  • Debugging data inconsistencies

This blog explains the Legal Entity table structure in Oracle Fusion HCM, how it works technically, and how consultants use it in real projects.

What is Legal Entity in Oracle Fusion HCM?

A Legal Entity represents a legally recognized organization registered with government authorities. It is responsible for:

  • Payroll and taxation
  • Statutory reporting
  • Compliance with local labor laws

In Fusion HCM, Legal Entities are tightly integrated with:

  • Business Units
  • Legal Employers
  • Payroll Statutory Units (PSU)

From a technical perspective, Legal Entity data is not stored in a single table. Instead, it is distributed across multiple tables in the Trading Community Architecture (TCA) and HCM schema.

Core Legal Entity Tables in Oracle Fusion

As a consultant, you will primarily work with the following tables:

1. XLE_ENTITY_PROFILES

This is the primary Legal Entity table.

Purpose:
Stores core Legal Entity details.

Key Columns:

Column NameDescription
LEGAL_ENTITY_IDUnique identifier
NAMELegal Entity name
LEGAL_ENTITY_IDENTIFIERRegistration number
EFFECTIVE_FROM / TOValidity dates
GEOGRAPHY_IDLocation reference

2. HR_ORGANIZATION_UNITS_F

This table links Legal Entities with HCM organizational structure.

Purpose:
Stores organization hierarchy data including Legal Employers.

Key Columns:

Column NameDescription
ORGANIZATION_IDUnique org ID
NAMEOrganization name
TYPEType (Legal Employer, Department, etc.)
EFFECTIVE_START_DATEStart date
EFFECTIVE_END_DATEEnd date

3. PER_LEGAL_EMPLOYERS

This table defines Legal Employers mapped to Legal Entities.

Purpose:
Connects employees to legal structure.

4. HZ_PARTIES (TCA Table)

Legal Entities are also stored as parties.

Purpose:
Maintains party-level information like address and contact details.

5. HZ_ORGANIZATION_PROFILES

Contains additional organization attributes for Legal Entities.

Real-World Implementation Use Cases

Let’s look at how Legal Entity tables are used in actual projects.

Use Case 1: Payroll Integration with Third-Party Vendor

In one implementation, payroll processing was outsourced.

Requirement:
Send employee data grouped by Legal Entity.

Solution:

  • Extract data using:
    • PER_ALL_PEOPLE_F
    • PER_ASSIGNMENTS_F
    • XLE_ENTITY_PROFILES
  • Join using LEGAL_ENTITY_ID
  • Send JSON payload via OIC Gen 3

Use Case 2: BI Publisher Report for Compliance

Requirement:
Generate statutory report per Legal Entity.

Approach:

  • Use XLE_ENTITY_PROFILES for entity details
  • Join with HR_ORGANIZATION_UNITS_F
  • Fetch employee data

Use Case 3: Multi-Country Implementation

Scenario:
Client operates in India, US, and UK.

Each country has:

  • Different Legal Entities
  • Different Payroll Statutory Units

Challenge:
Ensure correct mapping of employees to Legal Entity.

Resolution:

  • Validate using:
    • PER_LEGAL_EMPLOYERS
    • HR_ORGANIZATION_UNITS_F

Architecture / Technical Flow

Here’s how Legal Entity data flows across Oracle Fusion:

  1. Legal Entity created in Setup and Maintenance
  2. Stored in:
    • XLE_ENTITY_PROFILES
    • HZ_PARTIES
  3. Linked to:
    • Legal Employer (PER_LEGAL_EMPLOYERS)
  4. Employees assigned via:
    • PER_ASSIGNMENTS_F

Flow Summary:

Legal Entity → Legal Employer → Employee Assignment

Prerequisites for Working with Legal Entity Tables

Before querying or using Legal Entity data, ensure:

  • Access to BI Publisher / OTBI
  • Knowledge of Fusion table joins
  • Role with SQL access (PaaS / BICC / OAC)
  • Understanding of HCM data model

Step-by-Step: Querying Legal Entity Data

Step 1 – Identify Legal Entity

 
SELECT LEGAL_ENTITY_ID, NAME
FROM XLE_ENTITY_PROFILES;
 

Step 2 – Link to Organization

 
SELECT hou.ORGANIZATION_ID, hou.NAME
FROM HR_ORGANIZATION_UNITS_F hou;
 

Step 3 – Join with Employees

 
SELECT papf.PERSON_ID,
papf.FULL_NAME,
xep.NAME AS LEGAL_ENTITY_NAME
FROM PER_ALL_PEOPLE_F papf
JOIN PER_ASSIGNMENTS_F paaf
ON papf.PERSON_ID = paaf.PERSON_ID
JOIN HR_ORGANIZATION_UNITS_F hou
ON paaf.ORGANIZATION_ID = hou.ORGANIZATION_ID
JOIN XLE_ENTITY_PROFILES xep
ON hou.LEGAL_ENTITY_ID = xep.LEGAL_ENTITY_ID;
 

Step 4 – Validate Output

Check:

  • Correct Legal Entity mapping
  • No duplicate records
  • Effective dates alignment

Testing the Setup

Test Scenario

Create a new employee under a specific Legal Employer.

Validation Steps

  1. Navigate to:
    Navigator → My Client Groups → Hire an Employee
  2. Select:
    • Legal Employer
    • Business Unit
  3. Save transaction
  4. Run query:
 
SELECT *
FROM PER_ASSIGNMENTS_F
WHERE PERSON_ID = :employee_id;
 

Expected Result

  • Assignment linked to correct Legal Entity
  • Data visible in XLE_ENTITY_PROFILES

Common Implementation Challenges

1. Missing Legal Entity Mapping

Issue:
Employees not linked correctly.

Cause:
Incorrect Legal Employer setup.

2. Data Duplication in Reports

Issue:
Multiple rows per employee.

Cause:
Improper joins between HCM and TCA tables.

3. Effective Date Issues

Issue:
Data mismatch in reports.

Cause:
Ignoring EFFECTIVE_START_DATE and END_DATE.

4. Integration Failures

Issue:
Incorrect Legal Entity in payload.

Cause:
Wrong join logic in OIC mapping.

Best Practices from Real Projects

1. Always Use Effective Dating

 
WHERE SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
 

2. Avoid Direct Joins with TCA Unless Needed

HZ tables can complicate joins. Use only when required.

3. Use Views Instead of Tables

Whenever possible:

  • Use secured views (e.g., PER_PERSON_SECURED_LIST_V)
  • Avoid direct base tables in reports

4. Standardize Legal Entity Codes

Define naming conventions:

  • LE_INDIA_HYD
  • LE_US_NY

This helps in integrations.

5. Validate During Data Migration

During HDL loads:

  • Validate Legal Entity existence
  • Ensure correct IDs

Summary

The Legal Entity Table in Oracle Fusion HCM is not just a backend object—it’s a critical component that drives:

  • Employee assignments
  • Payroll processing
  • Compliance reporting
  • Integrations

Understanding tables like:

  • XLE_ENTITY_PROFILES
  • HR_ORGANIZATION_UNITS_F
  • PER_LEGAL_EMPLOYERS

is essential for any consultant working on:

  • Reports
  • Integrations
  • Data migration

In real implementations, most issues related to payroll or reporting often trace back to incorrect Legal Entity mapping—so mastering this area gives you a strong advantage.

For deeper reference, always consult Oracle’s official documentation:
https://docs.oracle.com/en/cloud/saas/index.html

FAQs

1. What is the main Legal Entity table in Oracle Fusion HCM?

The primary table is XLE_ENTITY_PROFILES, which stores core Legal Entity information.

2. How is Legal Entity linked to employees?

Through:

  • HR_ORGANIZATION_UNITS_F
  • PER_ASSIGNMENTS_F

3. Can we directly use XLE_ENTITY_PROFILES in OTBI?

No, OTBI uses subject areas. For direct SQL access, use BI Publisher or BICC.


Share

Leave a Reply

Your email address will not be published. Required fields are marked *