Search Results ben_ext_rcd_in_file




Overview
Explore in AI Chat

Overview

BEN_EXT_RCD_IN_FILE is a core configuration table within the Oracle Advanced Benefits (BEN) module, storing extract record definitions as they are associated with extract files. In Oracle EBS 12.1.1 and 12.2.2, the Advanced Benefits extract framework uses this table to define which extract records are included in a given extract file, along with sort order, selection criteria, and record-level flags that govern how the extract process assembles output data.

The object is owned by the BEN schema and is documented as VALID. Its physical definition in ETRM 12.2.2 comprises 22 columns and a composite unique index, BEN_EXT_RCD_IN_FILE_PK, spanning EXT_RCD_IN_FILE_ID and ZD_EDITION_NAME. From a Data Vault modeling perspective, the FK structure suggests a satellite-leaning classification: the table carries descriptive attributes tied to parent extract file and extract record entities rather than acting as a pure hub or link.

Key Information Stored

The surrogate primary key is EXT_RCD_IN_FILE_ID, which uniquely identifies each row in the legacy (non-editioned) sense; at the physical level the unique index also includes ZD_EDITION_NAME, reflecting the edition-aware design introduced in 12.2.2. The most significant columns are:

Common Use Cases and Queries

Functional and technical consultants query this table when diagnosing extract output, verifying which records are attached to a file, and tracing sort or suppression logic. A typical diagnostic query joins to the parent file and record:

  • List records for a file: SELECT r.EXT_RCD_IN_FILE_ID, r.EXT_FILE_ID, r.EXT_RCD_ID, r.SEQ_NUM, r.RQD_FLAG FROM BEN_EXT_RCD_IN_FILE r WHERE r.EXT_FILE_ID = :p_file_id ORDER BY r.SEQ_NUM;
  • Identify sort keys: select SORT1_DATA_ELMT_IN_RCD_ID through SORT4 and join to BEN_EXT_DATA_ELMT_IN_RCD.
  • Extract configuration reporting: join to BEN_EXT_FILE and BEN_EXT_RCD to produce a catalogue of records per file for audit or migration.
  • Suppression analysis: filter on SPRS_CD, HIDE_FLAG and RQD_FLAG to explain why a record is absent from output.

Related Objects

BEN_EXT_RCD_IN_FILE sits at the centre of a dense FK web. It references BEN_EXT_FILE (EXT_FILE_ID), BEN_EXT_RCD (EXT_RCD_ID), and BEN_EXT_DATA_ELMT_IN_RCD (via SORT1 through SORT4). It is referenced by BEN_EXT_INCL_CHG, BEN_EXT_INCL_DATA_ELMT, BEN_EXT_WHERE_CLAUSE and BEN_EXT_RSLT_DTL (all through EXT_RCD_IN_FILE_ID), and by BEN_EXT_FILE. These relationships make it the hub of extract record configuration, inclusion criteria, and result-detail processing within Advanced Benefits.