Search Results ben_ext_rslt_dtl




Overview
Explore in AI Chat

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.

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.