Results for “coverage_type”

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

Overview

The BEN_BENEFIT_CONTRIBUTIONS_D view is a datetracked (date-effective) reporting object owned by the APPS schema within the PER – Human Resources product family of Oracle E-Business Suite 12.1.1 and 12.2.2. As its description states, it is "Used by Datetrack History," meaning it exposes the historical, effective-dated versions of benefit contribution records rather than only the current active row. The suffixed "_D" convention in Oracle HRMS indicates a datetrack view, which joins the base "_F" (date-tracked) table to its historical rows so that changes over time remain queryable.

The view resolves the foreign-key identifiers stored on the benefit contribution record — formula, element type, user, and coverage type lookup — into their human-readable names. This makes it suitable for reporting, audits, and integration extracts where contribution amounts must be tied to a named formula or payroll element rather than a surrogate ID.

Underlying Base Objects

The ETRM metadata documents the following referenced base objects, all resolved through APPS synonyms (except the HR_LOOKUPS view and the HR_API package):

• BEN_BENEFIT_CONTRIBUTIONS_F — the primary date-tracked benefit contributions table, supplying the contribution amounts, IDs, and effective dates.
• FF_FORMULAS_F — the FastFormula repository; joined on FORMULA_ID to resolve FORMULA_NAME. This is the object most associated with the user's search term ff_formulas_F.
• PAY_ELEMENT_TYPES_F — the payroll element type definition; joined on ELEMENT_TYPE_ID to resolve ELEMENT_NAME.
• FND_USER — joined on LAST_UPDATED_BY to resolve the USER_NAME of the last updater.
• HR_LOOKUPS — joined on LOOKUP_TYPE = 'COVERAGE_TYPE' to decode the coverage type into its MEANING.
• HR_API — the HR datetrack API package supporting effective-date logic.

Crucially, every join to the dimension tables (FF_FORMULAS_F, PAY_ELEMENT_TYPES_F, FND_USER, HR_LOOKUPS) uses an outer-join (+) syntax on the contributing table side, and the effective-date predicates use NVL bounds. This guarantees the contribution row is retained even when a matching formula, element, user, or lookup does not exist.

Key Columns

• BENEFIT_CONTRIBUTION_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE — the datetrack primary key and validity window of each contribution record.
• FORMULA_NAME — resolved from FF_FORMULAS_F; the payroll formula driving the contribution calculation.
• ELEMENT_NAME — resolved from PAY_ELEMENT_TYPES_F; the element to which the contribution posts.
• COVERAGE_TYPE / MEANING — the coverage type lookup code and its decoded meaning.
• EMPLOYEE_CONTRIBUTION, EMPLOYER_CONTRIBUTION — the actual contribution values.
• MAXIMUM_EMPLOYEE_CONTRIBUTION, MAXIMUM_EMPLOYER_CONTRIBUTION, MINIMUM_EMPLOYEE_CONTRIBUTION, MINIMUM_EMPLOYER_CONTRIBUTION — the defined bounds on contributions.
• LAST_UPDATE_DATE, USER_NAME — auditing information identifying when and by whom the record was last changed.

Common Use Cases and Queries

Typical scenarios include auditing historical contribution changes, validating formula assignments, and extracting contribution bands for downstream payroll reporting. A representative query, filtering to a specific formula, appears below:

SELECT benefit_contribution_id,
       effective_start_date,
       effective_end_date,
       formula_name,
       element_name,
       meaning AS coverage_type,
       employee_contribution,
       employer_contribution
FROM   apps.ben_benefit_contributions_d
WHERE  formula_name = :p_formula_name
AND    TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date - 1;

To inspect the full datetrack history of a single contribution, the query omits the current-date predicate and orders by EFFECTIVE_START_DATE. Because the view already decodes formula, element, and coverage type, it removes the need to re-join FF_FORMULAS_F and PAY_ELEMENT_TYPES_F in reporting SQL.