OTL Tables in Oracle Fusion

OTL Tables in Oracle Fusion HCM are a critical area for any consultant working with Time and Labor, payroll integration, or reporting. In real-world projects, especially during implementations or support, understanding these tables becomes essential for debugging issues, building custom reports, and validating time data flows.

In Oracle Fusion HCM (26A), Time and Labor (OTL) is tightly integrated with Payroll, Absence Management, and Project Costing. While the UI provides a user-friendly interface, the backend tables are where actual transactions, approvals, and calculations are stored.

This article provides a practical, consultant-level deep dive into OTL tables, including real implementation scenarios, table relationships, and troubleshooting strategies.

What are OTL Tables in Oracle Fusion HCM?

OTL (Oracle Time and Labor) tables store all data related to:

• Time entries (reported time)
• Time calculations
• Approval workflows
• Time card statuses
• Payroll transfer data

Unlike EBS, Fusion uses a modernized data model, but the core concept remains the same — capturing, validating, and transferring time data.

In Fusion, OTL data is stored primarily in:

• HWM (Workforce Management) schema tables
• Supporting HCM tables for person and assignment data

Key OTL Tables in Oracle Fusion HCM

Below are the most important tables every consultant should know:

1. Time Entry Tables

Real insight:
When users modify a time card, a new version is created, not overwritten. This is critical during audit scenarios.

2. Time Attributes Tables

Example:

• Payroll Time Type → Regular Hours / Overtime
• Project Number → Used in costing integrations

3. Time Calculation Tables

Consultant Tip:
If overtime is not calculating correctly, always check these tables.

4. Approval Tables

5. Time Card Status Tables

6. Payroll Integration Tables

Real-World Business Use Cases

Use Case 1 – Overtime Not Calculating

A client reports that employees are not getting overtime.

Consultant Approach:

1. Check time entries → HWM_TM_REC
2. Verify attributes → HWM_TM_REC_ATTRS
3. Check calculation → HWM_TM_CALC_RESULTS

Root Cause Example:
Payroll Time Type not mapped correctly.

Use Case 2 – Time Card Not Transferred to Payroll

Employee submitted time, but payroll doesn’t see it.

Steps:

• Verify status in HWM_TM_STATUSES
• Check approval in HWM_TM_APPROVAL_STATUS
• Validate payroll interface in PAY_TIME_ENTRIES

Use Case 3 – Audit Requirement

Client wants to track changes to time cards.

Solution:
Use HWM_TM_REC_VERSION to track:

• Who changed time
• When changes were made
• What was modified

Architecture / Technical Flow of OTL

Understanding the flow is crucial:

1. Employee enters time → UI
2. Data stored in → HWM_TM_REC
3. Attributes stored → HWM_TM_REC_ATTRS
4. Calculation engine runs → HWM_TM_CALC_RESULTS
5. Approval workflow → HWM_TM_APPROVAL_STATUS
6. Transfer to payroll → PAY_TIME_ENTRIES

Important Insight:
Each stage creates separate records, which helps in debugging.

Prerequisites for Working with OTL Tables

Before querying or working with these tables:

• Access to BI Publisher / OTBI / SQL Developer
• Knowledge of:
  • Person ID (PERSON_ID)
  • Assignment ID (ASSIGNMENT_ID)
• Security roles:
  • HCM Data Role
  • BI Access Role

Step-by-Step: Querying OTL Tables (Practical Example)

Step 1 – Identify Employee

Get PERSON_ID:

Step 2 – Fetch Time Card

Step 3 – Get Time Attributes

Step 4 – Check Calculations

Step 5 – Verify Approval Status

Testing the OTL Data Flow

Example Scenario

Employee submits 10 hours for a day.

Validation Steps

1. Check time entry:
   • Exists in HWM_TM_REC
2. Validate attributes:
   • Correct payroll time type
3. Check calculation:
   • Overtime calculated for 2 hours
4. Approval:
   • Status = Approved
5. Payroll:
   • Entry available in PAY_TIME_ENTRIES

Common Implementation Challenges

1. Data Not Visible in Reports

Cause:
Wrong joins between tables.

Fix:
Use TM_REC_ID as primary link.

2. Duplicate Time Records

Cause:
Multiple versions of time cards.

Fix:
Filter using latest version in HWM_TM_REC_VERSION.

3. Performance Issues

Cause:
Large volume of time data.

Fix:

• Use indexed columns
• Avoid full table scans

4. Incorrect Overtime Calculation

Cause:
Time calculation rules not applied.

Fix:
Check:

• Calculation groups
• Time attributes

Best Practices for Working with OTL Tables

1. Always Use Latest Version

Time cards are versioned. Always fetch latest record.

2. Avoid Direct Table Updates

Never update OTL tables manually — use HDL or UI.

3. Use Proper Joins

Key joins:

• HWM_TM_REC → HWM_TM_REC_ATTRS
• HWM_TM_REC → HWM_TM_CALC_RESULTS

4. Build Reusable Queries

Create reusable SQL scripts for:

• Time card validation
• Payroll reconciliation

5. Validate Before Payroll Run

Always validate:

• Approved status
• Calculation results

Real Consultant Tips

• Always check time attributes first — most issues originate here
• Use BI Publisher reports for validation instead of raw SQL for business users
• Keep a mapping document of time types vs payroll elements
• During UAT, test:
  • Regular time
  • Overtime
  • Absence entries

Summary

Understanding OTL Tables in Oracle Fusion HCM is essential for any consultant dealing with Time and Labor, payroll integrations, or reporting.

Key takeaways:

• Core tables include HWM_TM_REC, HWM_TM_REC_ATTRS, and HWM_TM_CALC_RESULTS
• Time data flows through multiple stages — entry, calculation, approval, payroll
• Real-world troubleshooting always involves backend table validation
• Versioning is critical — always use latest records

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

FAQs

1. What is the main table for time entries in OTL?

The primary table is HWM_TM_REC, which stores time card records.

2. How is overtime stored in Oracle Fusion OTL?

Overtime is stored in HWM_TM_CALC_RESULTS after the calculation engine processes time entries.

3. How do I track time card changes?

Use HWM_TM_REC_VERSION to track version history and changes.