Results for “dcd_val”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BEN_EXT_DATA_ELMT_DECD is a table in the BEN (Advanced Benefits) product schema of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. Its documented description is "Extract data element decoded," which indicates that it stores the decoded or resolved values associated with an extract data element defined in the parent table BEN_EXT_DATA_ELMT. Extract data elements are the building blocks used by the Advanced Benefits extract process to select and format data for outbound interfaces such as carriers, payroll providers, and third-party benefits administrators. This table therefore holds the per-element decoding logic and value mappings that translate an element identifier into the concrete value emitted in an extract file.

The metadata's heuristic Data Vault classification is satellite-leaning. As a modeling suggestion, this positions BEN_EXT_DATA_ELMT_DECD as a descriptive satellite hanging off the BEN_EXT_DATA_ELMT hub, joining on the EXT_DATA_ELMT_ID foreign key and carrying the context-dependent decoded value plus audit and edition columns. This classification reflects the narrow, attribute-like nature of the table rather than a hub or link role.

Key Information Stored

The table is documented with 14 columns. The most significant are:

• EXT_DATA_ELMT_DECD_ID — surrogate primary key, backed by the unique index BEN_EXT_DATA_ELMT_DECD_PK.
• EXT_DATA_ELMT_ID — foreign key to BEN_EXT_DATA_ELMT; identifies the parent extract data element being decoded.
• BUSINESS_GROUP_ID — multi-tenancy discriminator, scoping rows to a specific business group.
• VAL — the source or lookup value to be decoded.
• DCD_VAL — the decoded value returned for the matching source value.
• LEGISLATION_CODE — the legislative context under which the decoding applies.
• CHG_EVT_SOURCE — indicates the source of the change event that produced or updated the row.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the Oracle framework.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit columns recording who last touched the row and when.
• CREATED_BY, CREATION_DATE — standard audit columns recording row creation.
• ZD_EDITION_NAME — editioning column that forms part of the composite unique key BEN_EXT_DATA_ELMT_DECD_PK alongside EXT_DATA_ELMT_DECD_ID, supporting Oracle's Edition-Based Redefinition and the Online Patching model in 12.2.2.

The surrogate key (EXT_DATA_ELMT_DECD_ID) should not be confused with the business-key candidate combination of EXT_DATA_ELMT_DECD_ID plus ZD_EDITION_NAME, which the documentation identifies as the unique index. The pairing of VAL and DCD_VAL is the substance of the decoding function, with LEGISLATION_CODE and BUSINESS_GROUP_ID providing scoping.

Common Use Cases and Queries

Typical uses of this table involve tracing how an extract data element produces its final output value, and joining decoded values to the element definition for extract validation. A representative query joins the parent element to its decoded entries scoped by business group:

• SELECT de.ext_data_elmt_id, de.val, de.dcd_val FROM ben.ben_ext_data_elmt_decd de WHERE de.ext_data_elmt_id = :p_element_id AND de.business_group_id = :p_bg_id;
• Reporting on legislation-specific decoding: filter on LEGISLATION_CODE to reproduce the values a particular legislative context expects.
• Audit reconciliation: compare LAST_UPDATE_DATE and LAST_UPDATED_BY to determine when a decoding mapping was last changed.
• Data migration and setup validation: confirm that every active extract data element has at least one decoded row before an extract run.

Because the table participates in the editioning model, queries in 12.2.2 should be aware of ZD_EDITION_NAME when running against patched or editioned data; standard runtime queries resolve the current edition automatically.

Related Objects

The principal related objects, grounded in the documented foreign-key and primary-key relationships, are:

• BEN_EXT_DATA_ELMT — parent table; BEN_EXT_DATA_ELMT_DECD.EXT_DATA_ELMT_ID references it. This is the dominant join and defines the satellite-to-hub relationship.
• BEN.BEN_EXT_DATA_ELMT_DECD — the fully qualified object, owner BEN.
• BEN_EXT_DATA_ELMT_DECD_PK — the primary key constraint and unique index on EXT_DATA_ELMT_DECD_ID and ZD_EDITION_NAME.

Beyond these documented relationships, extract processing in Advanced Benefits also relies on the surrounding BEN extract tables and the concurrent programs that generate extract output; those depend on this table indirectly through the element definitions in BEN_EXT_DATA_ELMT. Practitioners should treat BEN_EXT_DATA_ELMT_DECD as a dependent satellite of BEN_EXT_DATA_ELMT and join through EXT_DATA_ELMT_ID when reconciling decoded extract values.