Search Results ben_ext_file




Overview
Explore in AI Chat

Overview

BEN_EXT_FILE is a table in the BEN (Advanced Benefits) product schema of Oracle E-Business Suite, documented in ETRM as "Extract file." It is the metadata hub that defines extract file structures used by Oracle Advanced Benefits to transmit benefit enrollment, participant, and plan data to external parties such as carriers, third-party administrators, payroll providers, and government reporting agencies. In this role it anchors the configuration that downstream extract processes consume when generating outbound files.

Based on the foreign key topology, the table is best modeled in Data Vault terms as a hub-leaning construct. It carries the surrogate primary key EXT_FILE_ID, which is referenced by dependent tables (BEN_EXT_DFN and BEN_EXT_RCD_IN_FILE), a pattern characteristic of a hub or central reference entity rather than a transaction satellite. This classification is a heuristic mined from the FK structure and should be treated as a modeling suggestion, not a declarative fact.

Key Information Stored

The table contains 46 documented columns. The most significant include:

The unique index BEN_EXT_FILE_PK is defined on (EXT_FILE_ID, ZD_EDITION_NAME), indicating that the table participates in Oracle's edition-based redefinition (EBR) model. ZD_EDITION_NAME is therefore part of the effective business key in 12.2.x and must be considered in any direct query.

Common Use Cases and Queries

Typical usage centers on identifying and auditing configured extract files, verifying DFF usage, and tracing dependent record layouts.

  • Listing extract files by legislation or business group for a specific deployment.
  • Locating the record layout and data element mapping attached to a given file.
  • Auditing DFF usage across the XFI_ATTRIBUTE1–30 columns to determine which site-specific extensions are populated.
  • Reconciling extract output back to its definition during carrier interface troubleshooting.

A representative query is:

  • SELECT f.ext_file_id, f.name, f.legislation_code, f.xml_tag_name, r.name record_layout FROM ben.ben_ext_file f, ben.ben_ext_rcd_in_file r WHERE f.ext_rcd_in_file_id = r.ext_rcd_in_file_id AND f.business_group_id = :p_bg_id ORDER BY f.name;
  • SELECT ext_file_id, name, legislation_code FROM ben.ben_ext_file WHERE xfi_attribute_category IS NOT NULL;

In 12.2.x, queries should additionally filter or be aware of ZD_EDITION_NAME, since the editioning view surface may expose only the current edition by default.

Related Objects

  • BEN_EXT_RCD_IN_FILE — child table defining the record layout; join on EXT_FILE_ID (BEN_EXT_RCD_IN_FILE.EXT_FILE_ID → BEN_EXT_FILE.EXT_FILE_ID).
  • BEN_EXT_DFN — dependent definition table referencing BEN_EXT_FILE.EXT_FILE_ID, holding extracted definition detail.
  • BEN_EXT_RCD_IN_FILE (reverse reference) — BEN_EXT_FILE.EXT_RCD_IN_FILE_ID points back to the record-in-file definition.
  • Benefit extract concurrent programs and the BEN_EXT_FILE_PK unique index, which enforce uniqueness during DML.

All relationships above are documented in ETRM; no additional objects are asserted.