Oracle Fusion Time and Labor Tables Complete Guide

1. Time & Labor Module Overview

Oracle Fusion Cloud Time and Labor uses the HWM_ prefix (Historical Workforce Management) for its core tables. The module handles employee time entry, approval workflows, time calculation rules, and the transfer of approved hours to payroll for earnings processing.

The architecture splits into three layers:

Time Entry: Employees record hours via timecards (HWM_TM_TIME_CARDS + HWM_TM_REP_TIMES)
Time Calculation: Rules engine applies overtime thresholds, shift differentials, and break deductions (HWM_TM_RULE_SETS + HWM_TM_DURATIONS)
Payroll Transfer: Approved, calculated time maps to payroll element entries for processing

ℹ️

Access note: In Oracle Fusion Cloud, time data is typically reported through OTBI subject areas (like Workforce Management — Reported Time Real Time) rather than direct SQL. Direct SQL access requires BI Publisher data models or HCM Extracts with appropriate roles.

2. Core HWM_ Tables Reference

Here are the 12 most important Time and Labor tables, grouped by function:

Timecard & Time Entry Tables

Table	Purpose	Key Columns
`HWM_TM_TIME_CARDS`	Timecard header/summary — one row per timecard submission	TIME_CARD_ID, PERSON_ID, TIMECARD_PERIOD_ID, TIMECARD_STATUS, SUBMITTED_DATE, APPROVED_DATE
`HWM_TM_REP_TIMES`	Individual time entries (the main detail table) — one row per time block	TIME_ID, PERSON_ID, ASSIGNMENT_ID, TIMECARD_ID, START_DATE, END_DATE, DURATION_HOURS, TIME_CATEGORY_CODE, TIME_APPROVAL_STATUS
`HWM_TM_DURATIONS`	Calculated durations after rule processing (post-rules output)	DURATION_ID, TIME_ID, DURATION_MINUTES, SHIFT_TYPE_ID
`HWM_TIMECARD_PERIODS`	Timecard period definitions (weekly, biweekly, semi-monthly boundaries)	TIMECARD_PERIOD_ID, PERIOD_START_DATE, PERIOD_END_DATE, PERIOD_STATUS

Work Schedule Tables

Table	Purpose	Key Columns
`HWM_WORK_SCHEDULES`	Work schedule definitions (8-hour day, 4x10, rotating shifts)	WORK_SCHEDULE_ID, WORK_SCHEDULE_NAME, SCHEDULE_START_DATE, SCHEDULE_END_DATE
`HWM_WORK_SCHEDULE_ASSIGNMENTS`	Links schedules to workers (date-effective)	SCHEDULE_ASSIGNMENT_ID, WORK_SCHEDULE_ID, PERSON_ID, ASSIGNMENT_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
`HWM_WORK_SCHEDULE_DETAILS`	Daily patterns within a schedule (Mon=8h, Tue=8h, etc.)	SCHEDULE_DETAIL_ID, WORK_SCHEDULE_ID, DAY_OF_WEEK, START_TIME, END_TIME, SCHEDULED_HOURS

Time Rules & Configuration Tables

Table	Purpose	Key Columns
`HWM_TM_RULE_SETS`	Time calculation rule sets (overtime thresholds, break rules)	RULE_SET_ID, RULE_SET_NAME, RULE_SET_CODE
`HWM_TM_RULES`	Individual rules within a rule set	RULE_ID, RULE_SET_ID, RULE_TYPE, THRESHOLD_HOURS, MULTIPLIER
`HWM_TM_TIME_CATEGORIES`	Time category definitions (Regular, Overtime, Holiday, etc.)	TIME_CATEGORY_ID, TIME_CATEGORY_CODE, TIME_CATEGORY_NAME
`HWM_TM_APPROVAL_CHAINS`	Approval workflow routing for timecards	APPROVAL_CHAIN_ID, APPROVER_PERSON_ID, APPROVAL_SEQUENCE
`HWM_TM_TRANSFER_BATCHES`	Tracks batches of approved time transferred to payroll	TRANSFER_BATCH_ID, TRANSFER_DATE, BATCH_STATUS, PERIOD_START_DATE, PERIOD_END_DATE

3. Timecard Processing Flow

Understanding how Oracle processes timecards maps directly to which tables are populated at each stage:

Employee Enters TimeCreates rows in HWM_TM_REP_TIMES with START_DATE, END_DATE, DURATION_HOURS. A header row is created in HWM_TM_TIME_CARDS with TIMECARD_STATUS = 'OPEN'.

Submit for ApprovalTIMECARD_STATUS changes to 'SUBMITTED'. TIME_APPROVAL_STATUS in HWM_TM_REP_TIMES is set to 'SUBMITTED'. SUBMITTED_DATE is populated.

Apply Time RulesOracle evaluates HWM_TM_RULE_SETS against the time entries. Overtime thresholds, break deductions, and shift differentials are calculated. Results written to HWM_TM_DURATIONS with categorized hours (REG, OT, DIFF).

Manager ApprovalApproval workflow routes to manager per HWM_TM_APPROVAL_CHAINS. On approve: TIMECARD_STATUS → 'APPROVED', APPROVED_DATE populated. On reject: status → 'REJECTED', returned to employee.

Transfer to PayrollApproved time is batched in HWM_TM_TRANSFER_BATCHES and mapped to payroll element entries in PAY_ELEMENT_ENTRIES_F. TIME_CATEGORY_CODE maps to element types (Regular Earnings, Overtime, Shift Premium).

Payroll ProcessingPayroll run picks up element entries, applies pay rates, and generates PAY_RUN_RESULTS with calculated earnings (Hours × Rate = Amount).

4. Work Schedules & Assignments

Work schedules define when employees are expected to work. They drive overtime calculations (hours beyond scheduled = OT) and are used for absence accrual rules.

Schedule Architecture

HWM_WORK_SCHEDULES defines the template (e.g., "Standard 40-Hour Week")
HWM_WORK_SCHEDULE_DETAILS defines daily patterns (Mon: 9am–5pm = 8h)
HWM_WORK_SCHEDULE_ASSIGNMENTS links a schedule to a person/assignment with date-effectiveness

⚠️

Date-effective joins: When joining schedule assignments to time entries, always filter on hwm.START_DATE BETWEEN wsa.EFFECTIVE_START_DATE AND wsa.EFFECTIVE_END_DATE. Employees can change schedules mid-period (e.g., transferring from day shift to night shift).

Common Schedule Types

Schedule Name	Pattern	Weekly Hours	OT Trigger
Standard 5x8	Mon–Fri, 8h/day	40	>8h/day or >40h/week
Compressed 4x10	Mon–Thu, 10h/day	40	>10h/day or >40h/week
Rotating Shift	Variable pattern (e.g., 4-on/3-off)	Varies	Per schedule rules
Part-Time	Mon/Wed/Fri, 6h/day	18	>6h/day (if configured)

5. OTBI Subject Areas

Oracle provides several OTBI subject areas for Time and Labor reporting. These are the most useful for common reporting needs:

Subject Area	Best For	Key Dimensions	Key Measures
`Workforce Management — Reported Time Real Time`	Individual time entries, hours by category, approval tracking	Worker, Period, Time Category, Schedule, Status	Hours Worked, Duration, Start/End Time
`Workforce Management — Time Card Real Time`	Timecard-level reporting, submission and approval status	Worker, Assignment, Period, Status	Total Hours, Submission Date, Approval Date
`Workforce Management — Work Schedule Real Time`	Schedule definitions and worker assignments	Schedule, Effective Dates, Assignment	Scheduled Hours, Pattern
`Time and Labor — Time Categories`	Time category breakdown and configuration	Category Code, Category Name	Hours per Category

💡

Tip: Use Workforce Management — Reported Time Real Time as your primary subject area for most time reports. It includes worker demographics, department, time details, and approval status all in one subject area without needing cross-subject joins.

6. Six Production SQL Queries

All queries filter on TIME_APPROVAL_STATUS = 'APPROVED' by default. Remove this filter if you need pending/submitted time for dashboard views.

Query 1: Hours by Department (Last 6 Months)

Department Hours Summary

SELECT dept_tl.name                     AS department,
       SUM(hwm.duration_hours)          AS total_hours,
       ROUND(AVG(hwm.duration_hours), 2) AS avg_hours_per_entry,
       COUNT(DISTINCT hwm.time_id)      AS num_entries,
       COUNT(DISTINCT hwm.person_id)    AS num_employees
FROM   hwm_tm_rep_times hwm
JOIN   per_all_assignments_m paam
       ON hwm.assignment_id = paam.assignment_id
       AND hwm.start_date BETWEEN paam.effective_start_date
                                    AND paam.effective_end_date
JOIN   hr_all_organization_units_f_tl dept_tl
       ON paam.organization_id = dept_tl.organization_id
       AND dept_tl.language = USERENV('LANG')
       AND hwm.start_date BETWEEN dept_tl.effective_start_date
                                    AND dept_tl.effective_end_date
WHERE  hwm.start_date >= TRUNC(SYSDATE) - 180
  AND  hwm.time_approval_status = 'APPROVED'
GROUP BY dept_tl.name
ORDER BY total_hours DESC;

Query 2: Overtime Analysis (Last 30 Days)

Overtime Hours by Employee

SELECT papf.person_number,
       ppnf.full_name,
       hwm.time_category_code,
       SUM(hwm.duration_hours)    AS total_hours,
       COUNT(*)                    AS num_entries,
       MIN(hwm.start_date)         AS first_entry,
       MAX(hwm.start_date)         AS last_entry
FROM   hwm_tm_rep_times hwm
JOIN   per_all_people_f papf
       ON hwm.person_id = papf.person_id
       AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date
                                        AND papf.effective_end_date
JOIN   per_person_names_f ppnf
       ON papf.person_id = ppnf.person_id
       AND ppnf.name_type = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date
                                        AND ppnf.effective_end_date
WHERE  hwm.time_approval_status = 'APPROVED'
  AND  hwm.time_category_code = 'OT'
  AND  hwm.start_date >= TRUNC(SYSDATE) - 30
GROUP BY papf.person_number, ppnf.full_name, hwm.time_category_code
ORDER BY total_hours DESC;

Query 3: Unapproved Timecards (Pending Manager Action)

Timecards Awaiting Approval

SELECT papf.person_number,
       ppnf.full_name,
       dept_tl.name                    AS department,
       htc.timecard_status,
       COUNT(hwm.time_id)              AS pending_entries,
       SUM(hwm.duration_hours)          AS total_pending_hours,
       MIN(hwm.start_date)              AS oldest_entry
FROM   hwm_tm_rep_times hwm
JOIN   hwm_tm_time_cards htc
       ON hwm.timecard_id = htc.time_card_id
JOIN   per_all_people_f papf
       ON hwm.person_id = papf.person_id
       AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date
                                        AND papf.effective_end_date
JOIN   per_person_names_f ppnf
       ON papf.person_id = ppnf.person_id
       AND ppnf.name_type = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date
                                        AND ppnf.effective_end_date
JOIN   per_all_assignments_m paam
       ON hwm.assignment_id = paam.assignment_id
       AND hwm.start_date BETWEEN paam.effective_start_date
                                    AND paam.effective_end_date
LEFT JOIN hr_all_organization_units_f_tl dept_tl
       ON paam.organization_id = dept_tl.organization_id
       AND dept_tl.language = USERENV('LANG')
       AND hwm.start_date BETWEEN dept_tl.effective_start_date
                                    AND dept_tl.effective_end_date
WHERE  hwm.time_approval_status IN ('SUBMITTED', 'PENDING')
GROUP BY papf.person_number, ppnf.full_name, dept_tl.name, htc.timecard_status
ORDER BY oldest_entry;

Query 4: Weekly Hours Trend (Regular vs Overtime)

Weekly Hours Breakdown by Category

SELECT TRUNC(hwm.start_date, 'IW')    AS week_start,
       hwm.time_category_code,
       SUM(hwm.duration_hours)          AS total_hours,
       COUNT(DISTINCT hwm.person_id)    AS num_employees
FROM   hwm_tm_rep_times hwm
WHERE  hwm.time_approval_status = 'APPROVED'
  AND  hwm.start_date >= TRUNC(SYSDATE) - 90
GROUP BY TRUNC(hwm.start_date, 'IW'), hwm.time_category_code
ORDER BY week_start DESC, hwm.time_category_code;

Query 5: Schedule Compliance (Actual vs Scheduled)

Variance: Worked Hours vs Scheduled Hours

SELECT papf.person_number,
       ppnf.full_name,
       ws.work_schedule_name,
       SUM(hwm.duration_hours)       AS actual_hours,
       SUM(wsd.scheduled_hours)      AS scheduled_hours,
       SUM(hwm.duration_hours) - SUM(wsd.scheduled_hours)
                                      AS variance_hours
FROM   hwm_tm_rep_times hwm
JOIN   per_all_people_f papf
       ON hwm.person_id = papf.person_id
       AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date
                                        AND papf.effective_end_date
JOIN   per_person_names_f ppnf
       ON papf.person_id = ppnf.person_id
       AND ppnf.name_type = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date
                                        AND ppnf.effective_end_date
JOIN   hwm_work_schedule_assignments wsa
       ON hwm.person_id = wsa.person_id
       AND hwm.start_date BETWEEN wsa.effective_start_date
                                    AND wsa.effective_end_date
JOIN   hwm_work_schedules ws
       ON wsa.work_schedule_id = ws.work_schedule_id
JOIN   hwm_work_schedule_details wsd
       ON ws.work_schedule_id = wsd.work_schedule_id
       AND wsd.day_of_week = TO_CHAR(hwm.start_date, 'DY')
WHERE  hwm.time_approval_status = 'APPROVED'
  AND  hwm.start_date >= TRUNC(SYSDATE) - 30
GROUP BY papf.person_number, ppnf.full_name, ws.work_schedule_name
HAVING ABS(SUM(hwm.duration_hours) - SUM(wsd.scheduled_hours)) > 2
ORDER BY variance_hours DESC;

Query 6: Time-to-Payroll Transfer Audit

Approved Time Not Yet Transferred to Payroll

SELECT papf.person_number,
       ppnf.full_name,
       hwm.time_category_code,
       SUM(hwm.duration_hours)       AS approved_hours,
       MIN(hwm.start_date)            AS earliest_date,
       MAX(hwm.start_date)            AS latest_date,
       hwm.timecard_id
FROM   hwm_tm_rep_times hwm
JOIN   per_all_people_f papf
       ON hwm.person_id = papf.person_id
       AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date
                                        AND papf.effective_end_date
JOIN   per_person_names_f ppnf
       ON papf.person_id = ppnf.person_id
       AND ppnf.name_type = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date
                                        AND ppnf.effective_end_date
LEFT JOIN hwm_tm_transfer_batches tb
       ON hwm.start_date BETWEEN tb.period_start_date
                                    AND tb.period_end_date
       AND tb.batch_status = 'COMPLETE'
WHERE  hwm.time_approval_status = 'APPROVED'
  AND  tb.transfer_batch_id IS NULL  -- Not yet transferred
  AND  hwm.start_date >= TRUNC(SYSDATE) - 60
GROUP BY papf.person_number, ppnf.full_name,
         hwm.time_category_code, hwm.timecard_id
ORDER BY earliest_date;

7. Payroll Transfer Mapping

When approved time transfers to payroll, Oracle maps time categories to payroll elements. The mapping is configurable per business unit, but the standard pattern is:

Time Category Code	Payroll Element	Calculation
`REG`	Regular Earnings	Hours × Base Rate
`OT`	Overtime Earnings	Hours × Base Rate × 1.5 (or 2.0)
`DIFF`	Shift Differential	Hours × Differential Rate
`HOLIDAY`	Holiday Pay	Scheduled Hours × Base Rate (or premium)
`CALLBACK`	Callback Pay	Minimum guarantee + Hours × Rate

ℹ️

Transfer verification: After each payroll transfer, compare SUM(duration_hours) from HWM_TM_REP_TIMES (where transferred) against SUM(screen_entry_value) from PAY_ELEMENT_ENTRY_VALUES_F (where input_value = 'Hours') for the same period. Any mismatch indicates a mapping or rounding issue.

8. Reporting Tips & Gotchas

Critical Join Patterns

Always use date-effective joins — hwm.START_DATE BETWEEN x.EFFECTIVE_START_DATE AND x.EFFECTIVE_END_DATE for PER_ALL_ASSIGNMENTS_M, schedule assignments, and org units
Use TIME_APPROVAL_STATUS, not TIMECARD_STATUS — the timecard header can be approved while individual entries within it may still be pending (partial approval)
Join to PER_PERSON_NAMES_F with NAME_TYPE = 'GLOBAL' — this gives you the display name; 'LEGISLATION' gives the legal name

Common Pitfalls

⚠️

Duration vs. Calculated Duration: HWM_TM_REP_TIMES.DURATION_HOURS is the raw employee-entered value. HWM_TM_DURATIONS contains the post-rules calculated value (after break deductions, rounding, OT splitting). For accurate payroll reporting, always use the durations table.

Multi-day timecards: A single timecard can span multiple pay periods. Always filter by START_DATE range, not by TIMECARD_ID alone
Retroactive changes: Managers can approve corrections for past periods. Check LAST_UPDATED_DATE if you need to identify recent changes to old time entries
No direct OTBI cross-join: There's no OTBI subject area that joins Time entries directly to Payroll Run Results. You must use SQL (BI Publisher or HCM Extract) for time-to-payroll audit reports
Time categories are configurable: While REG, OT, DIFF are standard, organizations can define custom categories. Always check HWM_TM_TIME_CATEGORIES for the full list in your environment

Performance Tips

Index on HWM_TM_REP_TIMES(PERSON_ID, START_DATE) is the primary access path — always include both in WHERE clauses
For dashboard queries, filter on START_DATE first to limit the scan window before joining to other tables
Use TRUNC(start_date, 'IW') for ISO week grouping — avoids locale issues with 'WW'

💡

Quick reference: The FK chain for time reporting is: HWM_TM_REP_TIMES.PERSON_ID → PER_ALL_PEOPLE_F.PERSON_ID | HWM_TM_REP_TIMES.ASSIGNMENT_ID → PER_ALL_ASSIGNMENTS_M.ASSIGNMENT_ID | HWM_TM_REP_TIMES.TIMECARD_ID → HWM_TM_TIME_CARDS.TIME_CARD_ID