Results for “hxt_weekly_hours_worked_v”

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

Overview

The HXT_WEEKLY_HOURS_WORKED_V view is a reporting object within the Oracle E-Business Suite Time and Labor (HXT) module. It aggregates hours worked by employee and assignment into a weekly summary, exposing a derived year number, week number, person identifier, earning category, and the total hours recorded for each combination. The view is documented in ETRM 12.2.2 as not implemented in the database for which the metadata was captured, which indicates that the object is shipped and defined by Oracle but is not always instantiated or populated in every environment. As a result, its presence and data availability can vary by installation.

The principal reporting role of this view is to support overtime determination and weekly-hours analysis. The embedded logic explicitly handles absence earnings, noting that only absence earnings designated to count toward hours worked before overtime eligibility should be included. This makes the view suitable for payroll and time compliance reporting where a weekly threshold is material.

Underlying Base Objects

The view is defined over a join of eight documented base objects: HXT_PAY_ELEMENT_TYPES_F_DDF_V (ELTV), PAY_ELEMENT_TYPES_F (ELT), HXT_EARNING_POLICIES (EGP), HXT_TIMECARDS (TIM), HXT_PER_AEI_DDF_V (AEIV), PER_ASSIGNMENTS_F (ASM), and HXT_DET_HOURS_WORKED (HRW), with an EXISTS subquery against HXT_EARN_GROUPS (ERG). The primary fact source is HXT_DET_HOURS_WORKED, which contributes the HOURS and DATE_WORKED values being summed. PER_ASSIGNMENTS_F supplies the PERSON_ID associated with the assignment, with date-effective join conditions restricting matching rows.

The detail hours are linked to timecards through TIM.ID = HRW.TIM_ID and to element types through ELT.ELEMENT_TYPE_ID = HRW.ELEMENT_TYPE_ID. Earning category information is derived from the DDF view HXT_PAY_ELEMENT_TYPES_F_DDF_V, while earning policy context is resolved through HXT_EARNING_POLICIES and the assignment-level descriptive flexfield view HXT_PER_AEI_DDF_V, filtered by the information type HXT_ASS_INFO. The EXPLAIN of the week computation normalizes the date_worked value to the ERP week start, identified in the view text by the inline comment ERP WEEK_START.

Key Columns

• YEAR_NO — the four-character year derived from the week-adjusted date_worked value.
• WEEK_NO — the two-character week number derived from the same week-adjusted date. Together with YEAR_NO it defines the grouping period.
• PERSON_ID — the person identifier obtained from PER_ASSIGNMENTS_F for the assignment owning the hours.
• EARNING_CATEGORY — the first ten characters of the earning category from the DDF view. Included values are 'REG', 'OVT', and, under qualifying absence conditions, 'ABS'.
• WEEKLY_HOURS — the summed HOURS from HXT_DET_HOURS_WORKED for the group.

Common Use Cases and Queries

Typical uses include overtime threshold analysis, reconciliation of regular versus overtime hours per week, and verification that absence earnings designated as counting toward hours worked are properly reflected. A representative query is:

SELECT YEAR_NO, WEEK_NO, PERSON_ID, EARNING_CATEGORY, WEEKLY_HOURS FROM HXT_WEEKLY_HOURS_WORKED_V WHERE YEAR_NO = '2024' ORDER BY WEEK_NO, PERSON_ID;

For a single employee, filter by PERSON_ID. Because the view is documented as not implemented in some databases, consumers should verify object existence and row counts before relying on it. Note that the view groups by derived year, derived week, person, and earning category, so weekly totals are segmented by category rather than consolidated into a single row per person-week.