Search Results hxt_det_hours_worked_fmv




Overview
Explore in AI Chat

Overview

HXT_DET_HOURS_WORKED_FMV is an APPS-owned database view in the Oracle E-Business Suite Time and Labor (HXT) module, documented as VALID in ETRM for releases 12.1.1 and 12.2.2. The suffix "FMV" denotes a "filtered materialized-style" view pattern: rather than acting as a simple pass-through, the view applies a deterministic WHERE clause to the base detail table so that only rows that carry real payroll or billing significance are exposed. This view presents detail hours worked lines that satisfy at least one of three conditions: reported hours are greater than zero, a monetary amount is greater than zero, or a RETRO_BATCH_ID is populated.

The third condition is directly relevant to the search term "retro_batch_id." The column RETRO_BATCH_ID identifies the retroactive processing batch to which a recomputed or adjusted time line belongs. By including rows where RETRO_BATCH_ID IS NOT NULL even when HOURS and AMOUNT are zero, the view ensures that retro-adjustment lines remain visible for auditing, payroll costing, and downstream BEE (Batch Element Entry) processing rather than being filtered out as zero-value records.

The view additionally enforces date-effectivity by comparing EFFECTIVE_START_DATE and EFFECTIVE_END_DATE against the effective date of the current session, retrieved from FND_SESSIONS via USERENV('SESSIONID'). This guarantees that consumers see only the version of each hours line that is valid for the session's effective date, an essential behavior in a date-tracked (DATE_TRACKED) time store.

Underlying Base Objects

The view is defined over two documented referenced objects, both resolved through APPS synonyms:

  • HXT_DET_HOURS_WORKED_F — the primary base table holding detail hours worked records, including regular, overtime, and retro lines, and carrying the date-tracked effectivity columns.
  • FND_SESSIONS — the Applications session table, used in the subquery that returns the EFFECTIVE_DATE for the current USERENV('SESSIONID').

No joins to external descriptive tables are performed; the view is a single-table projection with an inline session-date scalar subquery. Because the view references FND_SESSIONS, its result set is session-sensitive and must be queried from within an active EBS session context.

Key Columns

Common Use Cases and Queries

Typical uses include retro-pay auditing, time-to-payroll reconciliation, costing review, and extraction feeds. A query to isolate retro lines is straightforward:

  • Retro batch review: SELECT assignment_id, date_worked, hours, amount, retro_batch_id FROM hxt_det_hours_worked_fmv WHERE retro_batch_id IS NOT NULL ORDER BY retro_batch_id, date_worked;
  • Hours-to-amount reconciliation: SELECT date_worked, SUM(hours), SUM(amount) FROM hxt_det_hours_worked_fmv WHERE assignment_id = :p_assignment GROUP BY date_worked;
  • Retro versus current comparison: grouping by CASE WHEN retro_batch_id IS NULL THEN 'CURRENT' ELSE 'RETRO' END to measure the retro impact.

Because the underlying table is date-tracked, all queries should be issued within a correctly initialized session; direct SQL from a non-EBS tool may fail the FND_SESSIONS subquery or return no rows.