Search Results ben_ext_dfn




Overview
Explore in AI Chat

Overview

BEN_EXT_DFN is a configuration table within the Oracle Advanced Benefits (BEN) module that stores extract definitions. Each row defines how a benefits extract should be generated — the criteria to select the population, the output file characteristics, the post-processing rules, and the global or legislation-specific behavior applied to the extraction. The table belongs to the BEN schema, carries a VALID status in Oracle E-Business Suite 12.1.1 and 12.2.2, and contains 63 documented columns in the 12.2.2 physical model.

From a heuristic Data Vault modeling perspective, BEN_EXT_DFN presents as satellite-leaning: it is a descriptive, attribute-rich entity that hangs off central business concepts rather than acting as a pure hub or link. Its foreign keys anchor it to an extract criteria profile and an extract file definition, while dependents reference it for extract results.

Key Information Stored

The surrogate primary key is EXT_DFN_ID, enforced by the unique index BEN_EXT_DFN_PK. In the documented 12.2.2 schema, the business-key candidate unique index is BEN_EXT_DFN_PK (EXT_DFN_ID, ZD_EDITION_NAME), reflecting the edition-based redefinition pattern used in this release line.

Common Use Cases and Queries

Typical use cases include auditing configured extract definitions, tracing which criteria profile and file layout a given extract uses, and preparing operational reports on legislative or global scope. A representative join query:

SELECT d.EXT_DFN_ID, d.NAME, d.LEGISLATION_CODE,
       c.NAME AS CRIT_PRFL_NAME, f.FILE_NAME
FROM   BEN.BEN_EXT_DFN d,
       BEN.BEN_EXT_CRIT_PRFL c,
       BEN.BEN_EXT_FILE f
WHERE  d.EXT_CRIT_PRFL_ID = c.EXT_CRIT_PRFL_ID
AND    d.EXT_FILE_ID      = f.EXT_FILE_ID
AND    TRUNC(SYSDATE) BETWEEN d.STRT_DT AND NVL(d.END_DT, SYSDATE);

To locate dependents, join to BEN_EXT_RSLT on EXT_DFN_ID. In 12.2.2, always include ZD_EDITION_NAME predicates when querying across editions to avoid duplicate row returns.

Related Objects

  • BEN_EXT_CRIT_PRFL — referenced via BEN_EXT_DFN.EXT_CRIT_PRFL_ID; defines population selection criteria.
  • BEN_EXT_FILE — referenced via BEN_EXT_DFN.EXT_FILE_ID; defines output file characteristics.
  • BEN_EXT_RSLT — references BEN_EXT_DFN via EXT_DFN_ID; stores generated extract results.
  • BEN_EXT_DFN_PK — primary unique index on EXT_DFN_ID (12.2.2: EXT_DFN_ID, ZD_EDITION_NAME).
  • Advanced Benefits concurrent programs and XML Publisher templates (via XDO_TEMPLATE_ID) consume these rows to produce output.