Search Results hxc_resource_total_time_v




Overview
Explore in AI Chat

Overview

HXC_RESOURCE_TOTAL_TIME_V is a reporting view owned by the APPS schema within the HXC – Time and Labor Engine product of Oracle E-Business Suite. It presents an aggregated, denormalized projection of timecard data, flattening the three-level hierarchy of a timecard (timecard, day, and detail building blocks) into a single row per day-level building block. Each row exposes the associated resource (person), the approval status of the parent timecard, the calendar start and stop dates of the day, and the total hours worked, aggregated across all detail-level time building blocks belonging to that day.

Because the view resolves the parent-child relationships that are otherwise stored implicitly in HXC_TIME_BUILDING_BLOCKS, it serves as a convenient foundation for reporting, extracts, and integrations that require person-level time totals without navigating the recursive timecard structure. It is particularly relevant to operational reporting on submitted and approved time, downstream payroll or costing feeds, and dashboards that summarize effort by resource and day.

Underlying Base Objects

The view is defined over the following documented objects:

The join predicates restrict rows to those where HTB_DETAIL.SCOPE = 'DETAIL', HTB_DAY.SCOPE = 'DAY' and TYPE = 'RANGE', and HTB.SCOPE = 'TIMECARD', HTB.RESOURCE_TYPE = 'PERSON' and TYPE = 'RANGE'. In effect, only current-dated person timecards of range type are surfaced.

Key Columns

  • TIME_ID — the TIME_BUILDING_BLOCK_ID of the parent timecard block; identifies the timecard itself.
  • RESOURCE_ID — the person resource identifier on the timecard; the individual whose time is being reported.
  • DAY_ID — the TIME_BUILDING_BLOCK_ID of the day-level block.
  • DETAIL_ID — the TIME_BUILDING_BLOCK_ID of the detail-level block contributing hours.
  • STATUS_CODE / STATUS_NAME — the timecard approval status code and its decoded display name (up to 80 characters) from the HXC_APPROVAL_STATUS lookup.
  • START_DATE / STOP_DATE — the day's start and stop timestamps normalized to date values (time portion stripped) for the day-level block.
  • HOURS_WORKED — the sum, across detail blocks, of the hours derived by CATEGORY_DETAIL_HRS; this is the primary quantitative measure returned by the view.

Common Use Cases and Queries

Typical usage includes reporting approved time by person and day, reconciling timecard approval volumes, and feeding downstream cost or payroll calculations. A representative query returning total hours per resource is:

SELECT v.resource_id, v.start_date, v.status_name, SUM(v.hours_worked) total_hours
FROM apps.hxc_resource_total_time_v v
WHERE v.start_date BETWEEN :p_start AND :p_stop
GROUP BY v.resource_id, v.start_date, v.status_name
ORDER BY v.resource_id, v.start_date;

To isolate only approved time, add a predicate on the decoded status, for example WHERE v.status_name LIKE 'APPROVED%'. Because the view returns one row per day and detail combination, callers should always aggregate (SUM) HOURS_WORKED when a single total per resource or per day is required, and join RESOURCE_ID to HR per-person tables (or PER_ALL_PEOPLE_F) to resolve names. When composing such joins, filter on the current-dated effective rows to align with the date logic embedded in the view.