Search Results hxt_det_hours_worked




Overview
Explore in AI Chat

Overview

HXT_DET_HOURS_WORKED is a public synonym exposed in the APPS schema that resolves to the view of the same name, itself defined over the HXT_DET_HOURS_WORKED_F table. Within Oracle Time and Labor (module HXT), the object presents detailed hourly timecard line information at the finest level of granularity: one row for each individual time entry an employee or contingent worker reports, including the hours recorded, the date and assignment against which they were worked, and the associated payroll, costing, and project attributes. Because the view joins the underlying detail table to FND_SESSIONS and filters on the session effective date, it returns only the rows that are effective for the current application session, giving reporting tools, concurrent programs, and integrations a stable, date-effective projection of timecard detail rather than the raw versioned table.

Its role in Oracle EBS reporting and integration is primarily analytical and downstream-oriented. Timecard detail does not stay in Time and Labor; it is retrieved by processes such as payroll costing, project costing, and interface programs that generate BEE (Batch Element Entry) or equivalent payroll and project accounting entries. HXT_DET_HOURS_WORKED provides the detailed row set those processes and custom reports consume, while the header-level and summary objects provide aggregated views of the same data.

Underlying Base Objects

The ETRM metadata documents two referenced base objects, both reached through synonyms: HXT_DET_HOURS_WORKED_F and FND_SESSIONS. The _F suffix indicates the base detail table, which carries the full column list shown in the view definition. The view selects every column of that table without transformation, so its structure mirrors the base table exactly. The single behavioral addition is the temporal filter, which is expressed as two correlated subqueries against FND_SESSIONS:

The result is an and-capped date-effective window. Historical versions of a timecard line, and future-dated corrections, are excluded from the result set unless the session effective date falls within their effective range. This pattern is standard in Oracle EBS date-effective (HRMS-style) tables such as HXT tables, which use effective start and end dates plus an object version number for optimistic locking. Reports that do not use a normal FND session (for example, some ad hoc SQL run without initializing a session) may see no rows or unexpected results, which is a frequently reported symptom in this object's community threads.

Key Columns

The identity and relationship columns establish each row's position in the timecard hierarchy. ID is the primary key of the detail line; PARENT_ID links it to a parent record; TIM_ID ties the row to the Time Information record (the timecard header); ASSIGNMENT_ID identifies the employee assignment the hours belong to; and DATE_WORKED is the calendar date being reported. LINE_STATUS tracks the processing state of the line through the timecard and transfer cycle, while SEQNO orders lines within their parent.

Time and quantity columns carry the actual measurement: HOURS is the reported quantity, with TIME_IN, TIME_OUT, ACTUAL_TIME_IN, and ACTUAL_TIME_OUT recording the scheduled versus actual clock boundaries for start and end time entry. Payroll and costing attributes drive downstream monetary treatment. ELEMENT_TYPE_ID references the payroll element used for the entry; EARN_POL_ID identifies the earnings policy; FFV_RATE_CODE_ID, RATE_MULTIPLE, HOURLY_RATE, and AMOUNT determine how the hours are valued. FFV_COST_CENTER_ID, FFV_LABOR_ACCOUNT_ID, and TAS_ID supply the distribution and cost-accounting context, while PROJECT_ID, RETRO_PBL_LINE_ID, PBL_LINE_ID, and RETRO_BATCH_ID tie the line to project costing and payroll batch (PBL) processing, including retroactive adjustments.

Status and location columns complete the picture: PAY_STATUS and PA_STATUS indicate payroll and project accounting transfer states; STATE_NAME, COUNTY_NAME, CITY_NAME, and ZIP_CODE provide work location geography frequently used for state and local tax determination; JOB_ID, LOCATION_ID, SHT_ID, and HRW_COMMENT carry descriptive attributes; and the standard audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER) plus EFFECTIVE_START_DATE and EFFECTIVE_END_DATE support auditing and date-effective querying of the line.

Common Use Cases and Queries

The most common scenario is extracting detailed timecard lines for a payroll or costing cycle, filtered by date worked and assignment. A typical query joins to the timecard header and the employee assignment to obtain names and numbers:

  • Detail extract by period: select ID, TIM_ID, DATE_WORKED, HOURS, AMOUNT from APPS.HXT_DET_HOURS_WORKED where DATE_WORKED between :p_start and :p_end order by ASSIGNMENT_ID, DATE_WORKED.
  • Reconciliation of transfer status: group by PAY_STATUS and PA_STATUS to identify lines that have not yet been transferred or that failed interface validation.
  • Cost distribution analysis: join FFV_COST_CENTER_ID and FFV_LABOR_ACCOUNT_ID to the flexfield views to report labor costs by cost center and account for a period.
  • Project labor review: filter on PROJECT_ID and PBL_LINE_ID to trace timecard detail into project costing and billing, including retroactive lines identified by RETRO_PBL_LINE_ID and RETRO_BATCH_ID.

Because the view is date-effective and depends on the session effective date, custom reports should be run from within a normal EBS session or should explicitly supply an effective date when querying the base table instead. When production of a raw, unfiltered extract is required, query HXT_DET_HOURS_WORKED_F directly and apply an explicit effective-date predicate; the view itself remains the preferred interface for standard reporting and integration, since it enforces the same effective-dating semantics used by Oracle's own Time and Labor programs.