Results for “hxc_approval_status”

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

Overview

APPS.HXC_RESOURCE_TOTAL_TIME_V is a reporting view in the Oracle E-Business Suite Time and Labor (OTL) module, part of the HXC (Time and Labor) schema family available in releases 12.1.1 and 12.2.2. Its purpose is to present aggregated, resource-level time totals derived from the Time and Labor timecard hierarchy, expressed at the day level. The view collapses the internal three-tier "time building block" structure (timecard, day, and detail scopes) into a single row per day and detail combination, exposing the summed hours worked for a person resource together with the approval status of the parent timecard.

Because time data in Oracle Time and Labor is stored in a recursive, self-referencing table rather than in a flat transactional table, this view serves as a convenient abstraction layer for reporting tools such as Oracle Discoverer, BI Publisher, and custom SQL extracts. It is a join-only, aggregation view with no database triggers or materialization, so results are always computed at query time.

Underlying Base Objects

The view is defined over three documented references: the synonym HXC_TIME_BUILDING_BLOCKS, the package HXC_TIME_CATEGORY_UTILS_PKG, and the package HR_GENERAL.

• HXC_TIME_BUILDING_BLOCKS (HTB, HTB_DAY, HTB_DETAIL) — the core Time and Labor table, joined to itself three times. HTB represents the TIMECARD-scope row (scope='TIMECARD', type='RANGE', resource_type='PERSON'); HTB_DAY is the DAY-scope child (scope='DAY', type='RANGE'); HTB_DETAIL is the DETAIL-scope row (scope='DETAIL'). Parent-child linkage is established via PARENT_BUILDING_BLOCK_ID, with the current row marker DATE_TO = HR_GENERAL.END_OF_TIME. Rows for invalidated versions are therefore excluded.
• HR_GENERAL — supplies the END_OF_TIME constant used in the date filter and the DECODE_LOOKUP function that translates the lookup code HXC_APPROVAL_STATUS into a readable status name.
• HXC_TIME_CATEGORY_UTILS_PKG — invoked through CATEGORY_DETAIL_HRs, which converts a detail building block into hours worked, with NVL applied to return zero where no value is derived.

The view is the result of a single inner-join-and-group query; there are no external tables, no synonyms beyond the HXC synonym, and no bind parameters.

Key Columns

• TIME_ID — the time building block identifier of the timecard scope row.
• RESOURCE_ID — the person (resource) identifier, matching PER_PEOPLE_F or the resource master.
• DAY_ID — the building block identifier of the day scope row.
• DETAIL_ID — the building block identifier of the detail scope row; this appears in the SELECT but not in the GROUP BY, so the aggregation is effectively keyed by the other grouped columns.
• STATUS_CODE — the approval status code of the parent timecard (for example WORKING, SUBMITTED, APPROVED).
• STATUS_NAME — the decoded approval status text, truncated to 80 characters.
• START_DATE and STOP_DATE — the day boundaries, normalised to DD-MON-YYYY.
• HOURS_WORKED — SUM of the detail-level category hours, used as the primary measure.

Common Use Cases and Queries

Typical usage includes approved-hours reporting by person and day, reconciliation of timecard totals against payroll, and dashboard extracts. To retrieve hours by resource over a date span:

• SELECT resource_id, start_date, SUM(hours_worked) FROM apps.hxc_resource_total_time_v WHERE start_date BETWEEN :p_from AND :p_to GROUP BY resource_id, start_date;
• SELECT resource_id, status_name, SUM(hours_worked) FROM apps.hxc_resource_total_time_v GROUP BY resource_id, status_name;
• The search term "htb_day" maps directly to the internal alias HTB_DAY used in the view text and will resolve to DAY-scope building blocks, confirming that day-level columns DAY_ID, START_DATE, and STOP_DATE are the correct filters for date-based reporting.

No database links or remote dependencies are documented, and the referenced base objects are all local to the APPS schema.