Results for “hxt_daily_hours_worked_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

HXT_DAILY_HOURS_WORKED_V is a reporting view owned by the APPS schema within the Oracle E-Business Suite Time and Labor (HXT) module. Its purpose is to present a consolidated, day-by-day aggregation of hours worked by an assignment, keyed by the Time Information Management identifier (TIM_ID) and the calendar date on which the work occurred. Rather than exposing the granular, transaction-level rows stored in the underlying detail tables, the view collapses multiple detail records into a single daily total per TIM_ID and WORK_DATE, expressed in the DAILY_HOURS column.

In the EBS 12.1.1 and 12.2.2 architectures, this view functions primarily as a reporting and integration artifact. It is well suited for payroll validation, timecard reconciliation, downstream data extracts, and custom concurrent programs or BI Publisher reports that require a straightforward daily hours figure without re-implementing the complex join and date-effectivity logic used internally by Time and Labor.

Underlying Base Objects

The view is defined over several base objects that together enforce both the earning-policy rules and the effective-dating requirements of EBS. The documented base objects referenced are:

• HXT_DET_HOURS_WORKED (VIEW) — the source of the hour detail rows (aliased HRW), providing TIM_ID, ASSIGNMENT_ID, DATE_WORKED, HOURS, and ELEMENT_TYPE_ID.
• PER_ASSIGNMENTS_F (VIEW) — the assignment effectivity view (ASM), joined so that the date worked falls within the assignment's effective start and end dates.
• HXT_PER_AEI_DDF_V (VIEW) — the assignment/earning-policy association view (AEIV), also constrained by date effectivity.
• HXT_EARNING_POLICIES (SYNONYM) — the earning policy definition (EGP), joined to the AEI view by policy ID.
• HXT_EARN_GROUPS (SYNONYM) — the earning group (ERG), linked to the policy by EGT_ID and to the element type.
• HXT_PAY_ELEMENT_TYPES_F_DDF_V (VIEW) and PAY_ELEMENT_TYPES_F (SYNONYM) — the element type definitions (ELTV and ELT), matched on ELEMENT_TYPE_ID and bounded by effectivity dates.
• HR_GENERAL (PACKAGE) and HR_SECURITY (PACKAGE) — the standard security and date-handling packages invoked by the referenced views.

Every join is guarded by a BETWEEN clause on DATE_WORKED, ensuring that only valid, effective-dated combinations contribute to the daily total.

Key Columns

• TIM_ID — the Time Information Management identifier for the time record, passed through from HXT_DET_HOURS_WORKED and used as the primary grouping key.
• WORK_DATE — the date on which the hours were worked (HRW.DATE_WORKED), forming the second grouping key.
• DAILY_HOURS — the summed hours (SUM(HRW.HOURS)) for the given TIM_ID and WORK_DATE, representing the aggregated daily total.

Common Use Cases and Queries

Typical uses include daily hours extracts for payroll interfaces, audit and reconciliation reports, and custom reporting on hours by date.

• Retrieving recent daily totals:

  SELECT tim_id, work_date, daily_hours FROM hxt_daily_hours_worked_v WHERE work_date >= TRUNC(SYSDATE)-30 ORDER BY work_date, tim_id;

• Aggregating hours over a period:

  SELECT tim_id, SUM(daily_hours) FROM hxt_daily_hours_worked_v WHERE work_date BETWEEN :p_start AND :p_end GROUP BY tim_id;

• Screening for heavy daily loads:

  SELECT tim_id, work_date, daily_hours FROM hxt_daily_hours_worked_v WHERE daily_hours > 12;

Because the view relies on HR_SECURITY and effectivity joins, queries return data consistent with the security profile and assignment dates of the querying user.