Search Results ben_ext_fld




Overview
Explore in AI Chat

Overview

BEN_EXT_FLD is a table within the BEN (Advanced Benefits) product schema in Oracle E-Business Suite, documented as "Extract field." It serves as the metadata repository that defines the individual fields available for extraction from the Advanced Benefits system. Extract fields represent the discrete data elements — such as plan enrollments, eligibility attributes, coverage amounts, or personal identifiers — that administrators configure when building benefit extracts for external carriers, payroll providers, third-party administrators, or downstream HR systems. Each row in BEN_EXT_FLD describes one such field, including its presentation format, level of applicability, and the legislative context under which it is valid.

From a Data Vault modeling perspective, the FK metadata suggests a hub-leaning classification for BEN_EXT_FLD. The table acts as a business entity hub keyed on its unique identifier, with BEN_EXT_DATA_ELMT referencing it as a dependent satellite-like structure. This is a heuristic suggestion derived from the foreign key topology rather than a prescribed implementation design.

Key Information Stored

BEN_EXT_FLD contains 18 documented columns. The most significant are:

  • EXT_FLD_ID — the surrogate primary key, uniquely identifying each extract field. It is part of the BEN_EXT_FLD_PK unique index.
  • ZD_EDITION_NAME — an editioning column, also part of the BEN_EXT_FLD_PK unique index (EXT_FLD_ID, ZD_EDITION_NAME). This composite forms the true business-key candidate and supports Oracle's edition-based redefinition model for online patching, particularly relevant in EBS 12.2.2.
  • SHORT_NAME — a concise reference name used in extract definitions and user interfaces.
  • NAME — the descriptive display name of the extract field.
  • FRMT_MASK_TYP_CD — the format mask type code governing how the extracted value is rendered (for example, date, numeric, or text formatting).
  • DECD_FLAG — a decoded flag indicating whether the field is stored in coded form and requires decoding during extract generation.
  • CSR_CD — the cursor or concatenation source code referencing the underlying data retrieval logic.
  • LVL_CD — the level code indicating the organizational level at which the field applies (person, assignment, plan, or enrollment).
  • GROUP_LVL_CD — the group level code used when the field pertains to a grouping construct.
  • ALWD_IN_RCD_CD — the code determining whether the field is allowed within a record type.
  • BUSINESS_GROUP_ID — the business group (enterprise) that owns the field definition, enabling multi-tenant separation.
  • LEGISLATION_CODE — the legislative context, allowing fields to be scoped to specific country or regional requirements.
  • Standard audit columns including LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER, which support concurrency control and auditability.

Common Use Cases and Queries

The principal use case is the definition and maintenance of benefit extracts. Reporting queries typically resolve field metadata for a business group and legislation before joining to data element definitions.

A representative query retrieving active field definitions for a business group:

  • SELECT EXT_FLD_ID, SHORT_NAME, NAME, FRMT_MASK_TYP_CD, LVL_CD, DECD_FLAG FROM BEN.BEN_EXT_FLD WHERE BUSINESS_GROUP_ID = :p_bg_id AND LEGISLATION_CODE = :p_leg_code ORDER BY SHORT_NAME;

A join from the dependent data element table:

  • SELECT f.EXT_FLD_ID, f.NAME, d.* FROM BEN.BEN_EXT_FLD f JOIN BEN.BEN_EXT_DATA_ELMT d ON d.EXT_FLD_ID = f.EXT_FLD_ID WHERE f.ZD_EDITION_NAME = 'ORA$BASE';

Because the primary key is composite in the editioned environment, joins and filters should always constrain ZD_EDITION_NAME to avoid duplicate rows across editions. This is especially important in 12.2.2, where online patching creates non-default editions.

Key data integrity checks include verifying that SHORT_NAME is unique within a business group and legislation, confirming DECD_FLAG is consistent with the format mask, and confirming no data elements reference a deleted or invalid field.

Related Objects

  • BEN_EXT_DATA_ELMT — the primary dependent table; its EXT_FLD_ID foreign key references BEN_EXT_FLD.EXT_FLD_ID, linking each data element to its defining extract field.
  • BEN_EXT_RCD_DFN — record definitions that determine which fields are included in a given extract record; populated with fields sourced from BEN_EXT_FLD.
  • BEN_EXT_RCD_DFN_F — the association table mapping records to their constituent fields, joined via the field identifier.
  • BEN_EXT_LOV_VL — the descriptive flexfield or LOV view exposing field values for validation and selection.
  • BEN_EXT_FLD_CRIT — field criteria definitions used to filter or constrain extraction output.
  • Benefits extract APIs, such as the extract generation concurrent programs, read this metadata to construct the SQL that populates extract output files. These programs treat BEN_EXT_FLD and BEN_EXT_DATA_ELMT as the authoritative source of extract structure.
  • Standard EBS views in the BEN schema (for example, field-level validation views) inherit the same EXT_FLD_ID key structure and are frequently joined for administrative reporting.