Results for “dce_desc”

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

Overview

BEN_DPNT_CVG_ELIGY_PRFL_D is an APPS-owned database view within the Oracle Advanced Benefits (BEN) product family. It exposes dependent coverage eligibility profile definitions in a denormalized, human-readable form suitable for reporting, concurrent program output, and integration extracts. The view is defined over the effective-dated base table BEN_DPNT_CVG_ELIGY_PRFL_F and enriches each profile row with translated lookup meanings, formula names, and user audit information. The "_D" suffix follows the Oracle EBS convention for a descriptive or denormalized view of an "_F" (frozen/effective-dated) table. In EBS 12.1.1 and 12.2.2 the object resides in the APPS schema and is documented as VALID with the description "Retrofitted," indicating the view was aligned to the current BEN data model during a prior release upgrade. Because it is a view rather than a table, it carries no independent storage; all data is resolved at query time from its referenced objects, and it inherits the security and effective-dating semantics of the underlying eligibility profile table.

Underlying Base Objects

The view text joins four documented base objects, all referenced through APPS synonyms:

• BEN_DPNT_CVG_ELIGY_PRFL_F — the primary effective-dated table holding dependent coverage eligibility profile definitions. It supplies the profile identifier, name, status code, effective dates, DCE_DESC, and the rollup reference to an eligibility determination formula.
• FF_FORMULAS_F — joined on FORMULA_ID to the profile's DPNT_CVG_ELIG_DET_RL, returning the formula name associated with the eligibility determination rule.
• FND_USER — an outer join on LAST_UPDATED_BY that resolves the user identifier for the last modification.
• HR_LOOKUPS — an outer join restricted to LOOKUP_TYPE 'BEN_STAT' that translates DPNT_CVG_ELIGY_PRFL_STAT_CD into the display status meaning.

The formula join additionally restricts on effective dates, matching the profile's EFFECTIVE_START_DATE against the formula's effective range. Documented metadata also lists HR_API (PACKAGE) among referenced objects, reflecting validation or derivation logic applied through the BEN API layer rather than a direct join in the view text. All three non-primary joins are outer joins, so profiles without a matching formula, status lookup, or user record still return rows.

Key Columns

• ROW_ID — the ROWID of the underlying BEN_DPNT_CVG_ELIGY_PRFL_F row, usable as a unique row locator.
• DPNT_CVG_ELIGY_PRFL_ID — the surrogate primary key of the dependent coverage eligibility profile.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the effective-dating window that governs when the profile definition is valid; queries must constrain against these for date-tracked results.
• NAME — the user-defined name of the eligibility profile.
• DPNT_CVG_ELIGY_PRFL_STAT_MEAN — the decoded status meaning obtained from HR_LOOKUPS for lookup type BEN_STAT.
• DCE_DESC — the description attribute of the eligibility profile record; this is the column most commonly located through a search for "dce_desc."
• DPNT_CVG_ELIG_DET_NAME — the name of the eligibility determination formula resolved from FF_FORMULAS_F.
• LAST_UPDATE_DATE / LAST_UPDATED_BY — audit columns; the view resolves the user identifier through FND_USER, though the final projection returns the numeric user id rather than the user name.

Common Use Cases and Queries

The view is typically used to report on the eligibility profiles that control dependent coverage, to audit descriptions and status values, and to feed downstream extracts. A basic listing of current profiles follows:

• SELECT dpnt_cvg_eligy_prfl_id, name, dce_desc, dpnt_cvg_eligy_prfl_stat_mean, dpnt_cvg_elig_det_name FROM ben_dpnt_cvg_eligy_prfl_d WHERE TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
• Searching by description: SELECT * FROM ben_dpnt_cvg_eligy_prfl_d WHERE UPPER(dce_desc) LIKE '%DEPENDENT%';
• Audit reporting: join LAST_UPDATED_BY to FND_USER.USER_ID to obtain the user name, since the view returns only the numeric identifier.
• Integration extracts: select the profile identifier, name, status meaning, and formula name to reconcile eligibility configuration against external benefit administration systems.

Because effective dating is central to this object, any report or interface must apply date predicates to return the correct profile version, and should account for NULL formula names arising from the outer join where no eligibility determination formula is attached.