Search Results hxc_retrieval_ranges_pk

Overview

HXC_RETRIEVAL_RANGES is a table in the HXC schema belonging to the Oracle Time and Labor Engine (HXC) product family, available in Oracle E-Business Suite 12.1.1 and 12.2.2. The table stores "Retrieval Ranges" — the definitions that drive how the Time and Labor retrieval process gathers timecard and time entry records from the underlying time store for downstream processing. Each retrieval range represents a bounded unit of data collection (governed by start and stop boundaries and an ordering sequence) that is owned by a retrieval process and further decomposed into resource-level detail.

Following Data Vault modeling heuristics inferred from its foreign key structure, the table is classified as hub-leaning. In practice this means it functions as a central entity whose identity is anchored by the surrogate key RETRIEVAL_RANGE_ID, while its transactional and process context is captured through the referenced RETRIEVAL_PROCESS_ID. The table therefore sits between the process definition level and the lowest-level retrieval resource records, acting as the principal definition point for a single range of retrieval activity.

Key Information Stored

The fifteen documented columns carry the following responsibilities:

• RETRIEVAL_RANGE_ID — surrogate primary key (HXC_RETRIEVAL_RANGES_PK); the unique identifier of the range and the join key to dependent tables.
• RETRIEVAL_PROCESS_ID — foreign key to HXC_RETRIEVAL_PROCESSES; identifies the parent retrieval process under which the range is defined.
• RANGE_START and RANGE_STOP — the boundary values that delimit the range, defining the scope of retrieval records included.
• SEQ — sequence number establishing the ordering of the range within its parent process.
• TRANSACTION_ID — associates the range with a specific transaction context, supporting traceability.
• WHERE_CLAUSE — a dynamic filter expression applied when selecting time records for the range.
• UNIQUE_PARAMS — parameter values that further qualify or distinguish the range definition.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle EBS audit columns recording who created and last modified each row.
• CONC_REQUEST_ID — links the row to the concurrent request that created or processed it, enabling batch traceability.

The only unique index listed is HXC_RETRIEVAL_RANGES_PK on RETRIEVAL_RANGE_ID, confirming the surrogate key as the sole documented candidate for uniqueness.

Common Use Cases and Queries

Typical usage involves diagnosing or reporting on how Time and Labor retrieval processes partition their work. Common patterns include:

• Listing all ranges defined for a given retrieval process, ordered by sequence, to understand the collection sequence:

  SELECT r.retrieval_range_id, r.seq, r.range_start, r.range_stop, r.where_clause
  FROM hxc.hxc_retrieval_ranges r
  WHERE r.retrieval_process_id = :process_id
  ORDER BY r.seq;

• Tracing which concurrent request generated a range definition using CONC_REQUEST_ID.
• Auditing changes by filtering on LAST_UPDATE_DATE and LAST_UPDATED_BY within a reporting period.
• Joining to HXC_RETRIEVAL_RANGE_RESOURCES to see which resources fall within each range.

These queries support troubleshooting incomplete or mis-scoped timecard retrieval, as well as performance analysis of the retrieval engine.

Related Objects

The following objects are most significant based on the documented relationships:

• HXC_RETRIEVAL_PROCESSES — referenced through RETRIEVAL_PROCESS_ID; the parent process definition.
• HXC_RETRIEVAL_RANGE_RESOURCES — references this table via RETRIEVAL_RANGE_ID and holds the resource-level breakdown of each range.
• HXC_RETRIEVAL_RANGES_PK — the primary key constraint/index enforcing uniqueness of RETRIEVAL_RANGE_ID.

Because HXC_RETRIEVAL_RANGE_RESOURCES references HXC_RETRIEVAL_RANGES.RETRIEVAL_RANGE_ID, any deletion or rekeying of range rows must respect that dependency. Collectively, these objects form the retrieval definition hierarchy: process → range → resources.