Results for “ext_rslt_dtl_id”

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

Overview

BEN_EXT_RSLT_DTL is a table in the BEN (Advanced Benefits) product schema of Oracle E-Business Suite, documented as VALID in both 12.1.1 and 12.2.2. Its documented description is "Extract result detail," which identifies it as the row-level detail store for Benefits extract processing. Where the parent extract result tables capture the outcome of a benefits extract run at a summary level, BEN_EXT_RSLT_DTL holds the individual extracted records produced for each participant, preserving the data values that were written to the extract output.

The table is owned by the BEN schema and is characterized by an exceptionally wide, generic column layout. The documented physical schema in ETRM 12.2.2 lists 324 columns, of which the overwhelming majority are sequentially named positional columns (VAL_01 through VAL_300). This design reflects the configurable nature of the Benefits extract framework, in which extract definitions map plan, program, and eligibility data into a fixed set of generic result slots rather than a fixed relational model.

Mined from its foreign key structure, the heuristic Data Vault classification for BEN_EXT_RSLT_DTL is standalone. As a modeling suggestion, this indicates the table is not a pure hub, link, or satellite in the Data Vault sense; it functions as a wide detail or effectivity table that derives its meaning from parent entities referenced through its foreign keys rather than through a normalized hub-and-link topology.

Key Information Stored

The table's structure separates identity, ownership, and positional result values.

• EXT_RSLT_DTL_ID — the surrogate primary key, enforced by the unique index BEN_EXT_RSLT_DTL_PK. This is the only documented unique index and therefore the only documented business-key candidate.
• EXT_RSLT_ID — foreign key to BEN_EXT_RSLT, tying each detail row to its parent extract result.
• EXT_RCD_ID — foreign key to BEN_EXT_RCD, identifying the extract record definition that governs the row.
• EXT_RCD_IN_FILE_ID — foreign key to BEN_EXT_RCD_IN_FILE, linking the row to its position/definition within the extract file.
• EXT_PER_BG_ID — identifier relating the detail row to the person's benefits group context.
• PERSON_ID — the participant to whom the extracted values belong.
• BUSINESS_GROUP_ID — the HR business group, providing the standard multi-tenant partition for the data.
• VAL_01 through VAL_300 — the positional value columns into which configured extract fields are populated; the extract definition determines which slot carries which attribute.
• GROUP_VAL_01 and GROUP_VAL_02 — grouped value columns used for aggregation-style output.
• PRMY_SORT_VAL, SCND_SORT_VAL, THRD_SORT_VAL — primary, secondary, and tertiary sort keys applied when ordering the extract output.
• TRANS_SEQ_NUM and RCRD_SEQ_NUM — sequence numbers used to order transactions and records within the extract file.
• OBJECT_VERSION_NUMBER — optimistic locking column.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns.
• PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID — concurrent program and request audit columns identifying the extract run that generated the row.

Common Use Cases and Queries

The primary use case is auditing and reconciling a benefits extract run: verifying what was produced for each participant and diagnosing why a participant appears or does not appear in the output. A typical query joins the detail rows back to the parent result and the person:

• Retrieve all detail rows for a given extract run by joining on EXT_RSLT_ID to BEN_EXT_RSLT and filtering by REQUEST_ID or creation date.
• Inspect positional output for a person by filtering PERSON_ID and selecting the VAL_0x columns relevant to the extract definition in use.
• Reconstruct file ordering using TRANS_SEQ_NUM, RCRD_SEQ_NUM, and the PRIMARY/SECONDARY/TERTIARY sort values.
• Join to BEN_EXT_RCD and BEN_EXT_RCD_IN_FILE to translate generic VAL_n slots back to their configured field names.
• Purge or archive historical extract detail by REQUEST_ID or date range to control table growth, given the 324-column width.

Related Objects

• BEN_EXT_RSLT — parent extract result; joined via BEN_EXT_RSLT_DTL.EXT_RSLT_ID = BEN_EXT_RSLT.EXT_RSLT_ID.
• BEN_EXT_RCD — extract record definition; joined via BEN_EXT_RSLT_DTL.EXT_RCD_ID = BEN_EXT_RCD.EXT_RCD_ID.
• BEN_EXT_RCD_IN_FILE — extract record position/definition within the file; joined via BEN_EXT_RSLT_DTL.EXT_RCD_IN_FILE_ID = BEN_EXT_RCD_IN_FILE.EXT_RCD_IN_FILE_ID.
• PER_ALL_PEOPLE_F — participant identity; joined via PERSON_ID.
• HR_OPERATING_UNITS / PER_BUSINESS_GROUPS — business group context via BUSINESS_GROUP_ID.
• FND_CONCURRENT_REQUESTS — concurrent request audit via REQUEST_ID for the generating extract run.