Search Results ben_ext_rcd_pk

Overview

BEN.BEN_EXT_RCD is the extract record table within the Oracle E-Business Suite Advanced Benefits (BEN) module. It stores the individual record-level definitions that drive the Benefits extract engine — the mechanism by which benefits enrollment, plan, and participant data are formatted and written to outbound extract files for carriers, payroll interfaces, and third-party administrators. Each row in BEN_EXT_RCD represents a single logical extract record type that participates in one or more benefits extracts.

The table resides in the BEN schema and is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2. The physical schema in 12.2.2 exposes 45 columns, a substantial portion of which are the seeded descriptive flexfield attribute columns (XRC_ATTRIBUTE1 through XRC_ATTRIBUTE30). This pattern is consistent with the extract framework's use of a generic record container that can carry carrier-specific or interface-specific payload fields without schema changes.

Based on the foreign key topology mined from the ETRM metadata, BEN_EXT_RCD is a hub-leaning entity in Data Vault terms: it is referenced by multiple dependent tables and is not itself defined solely by an upstream parent. Treat this classification as a modeling suggestion rather than a physical constraint — in the operational schema it functions as a parent (master) record whose children define the field-level contents and file placement of the extract output.

Key Information Stored

The primary key of the table is BEN_EXT_RCD_PK, defined on the surrogate column EXT_RCD_ID. The documented unique index also includes ZD_EDITION_NAME, indicating the table participates in Oracle's edition-based redefinition (EBR) model as used in 12.2.x. ZD_EDITION_NAME should therefore be treated as part of the effective business key when querying in a multi-edition environment.

Among the most significant columns are:

• EXT_RCD_ID — surrogate primary key identifying the extract record.
• NAME — the user-facing name of the extract record.
• RCD_TYPE_CD — code classifying the record type (for example, header, detail, or trailer semantics).
• LOW_LVL_CD — low-level code controlling ordering/hierarchy of records during extract generation.
• LEGISLATION_CODE — the legislation under which the record applies, supporting multi-country benefit extracts.
• XML_TAG_NAME — the tag used when the record is rendered into XML-formatted extract output.
• BUSINESS_GROUP_ID — the HR business group (operating unit) scoping the record.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the OAF/BC4J layer.
• XRC_ATTRIBUTE1 … XRC_ATTRIBUTE30 — descriptive flexfield segments capturing interface-specific attributes.
• XRC_ATTRIBUTE_CATEGORY — the flexfield context determining which attribute segments are meaningful.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns.

Common Use Cases and Queries

The dominant use case is diagnosing and validating benefits extract output. Administrators and technical consultants query BEN_EXT_RCD to confirm which record types exist, how they are ordered, and how they map to element-level definitions. A minimal lookup by name or type is typical:

SELECT ext_rcd_id, name, rcd_type_cd, low_lvl_cd, legislation_code
FROM ben.ben_ext_rcd
WHERE legislation_code = :leg_code
AND (zd_edition_name IS NULL OR zd_edition_name = 'ORA$BASE');

A second pattern joins to child tables to reconstruct the full field mapping of a record, which is essential when a carrier reports a malformed extract file. Reporting queries frequently aggregate counts of records per type and legislation for documentation purposes. Because the table carries 30 flexfield attributes, ad hoc queries should resolve XRC_ATTRIBUTE_CATEGORY first to avoid interpreting unused segments. In 12.2.x environments, always constrain or explicitly handle ZD_EDITION_NAME to prevent duplicate rows appearing across editions.

Related Objects

BEN_EXT_RCD sits at the center of the benefits extract record model. The documented foreign key relationships identify the following dependent objects, each referencing this table through EXT_RCD_ID:

• BEN_EXT_DATA_ELMT_IN_RCD — defines which extract data elements (fields) are included in each record; joined on BEN_EXT_DATA_ELMT_IN_RCD.EXT_RCD_ID = BEN_EXT_RCD.EXT_RCD_ID. This is the principal child table for field-level composition.
• BEN_EXT_RCD_IN_FILE — positions extract records within a given output file; joined on BEN_EXT_RCD_IN_FILE.EXT_RCD_ID = BEN_EXT_RCD.EXT_RCD_ID.
• BEN_EXT_RSLT_DTL — stores generated extract result detail rows; joined on BEN_EXT_RSLT_DTL.EXT_RCD_ID = BEN_EXT_RCD.EXT_RCD_ID, linking record definitions to actual extracted output.

Together these tables form the extract definition-to-output chain: BEN_EXT_RCD declares the record, BEN_EXT_DATA_ELMT_IN_RCD declares its fields, BEN_EXT_RCD_IN_FILE places it in the file, and BEN_EXT_RSLT_DTL holds the materialized results. When troubleshooting extract failures, these four objects are typically examined in sequence.