Results for “us_benefit_coverage”

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

Overview

APPS.HRFV_DEF_BENEFIT_CONTRIBUTIONS is a read-only Oracle EBS view that exposes the definition of benefit contribution elements configured within Oracle Advanced Benefits (OAB). It presents one row per effective-dated benefit contribution record, joining the contribution definition to its parent benefit element type, its benefit classification, and the business group (organization) that owns it. The view is commonly surfaced in HRMS benefit administration and reporting, and is frequently referenced by users exploring identifiers such as us_benefit_coverage, which appears in the view as a lookup type used to decode coverage types into a readable label.

The view carries the WITH READ ONLY clause and is defined over the APPS schema. It applies the hr_bis.get_sec_profile_bg_id security profile to restrict output to business groups the querying user is authorized to see, and it filters translated tables (PAY_ELEMENT_TYPES_F_TL, HR_ALL_ORGANIZATION_UNITS_TL) by the current session language. This makes it suitable both for ad hoc reporting and as a controlled, security-aware data source for downstream extracts and integrations.

Underlying Base Objects

The view is defined over the following documented base objects:

• BEN_BENEFIT_CONTRIBUTIONS_F — the primary benefit contribution definition table, supplying effective dates, coverage type, employee and employer contribution amounts, and the formula reference.
• BEN_BENEFIT_CLASSIFICATIONS — provides the classification name and the COBRA and dependents-allowed flags used to derive eligibility attributes.
• PAY_ELEMENT_TYPES_F — supplies the benefit element and its linked benefit classification.
• PAY_ELEMENT_TYPES_F_TL — the translated element names and descriptions, filtered by userenv('LANG').
• HR_ALL_ORGANIZATION_UNITS_TL — the translated business group name, joined on business_group_id.
• HR_BIS (package) — invoked as bis_decode_lookup to decode coverage type and the YES/NO flags.
• HR_DISCOVERER (package) — invoked as check_end_date to normalize the effective end date.

Joins are made on element_type_id between the contribution and element type, on benefit_classification_id between the element type and classification, and on business_group_id between the contribution and the organization.

Key Columns

• business_group_name — the owning business group, from the translated organization name.
• benefit_name — the translated benefit element name.
• benefit_classification_name — the classification grouping the benefit.
• description — the element description from the translated table.
• effective_start_date / effective_end_date — the dated range; the end date is passed through HR_DISCOVERER.CHECK_END_DATE.
• coverage_type — decoded via bis_decode_lookup('US_BENEFIT_COVERAGE', coverage_type), resolving codes such as the us_benefit_coverage lookup values.
• employee_contribution / employer_contribution / total_contribution — numeric amounts converted with TO_NUMBER(..., '999999999D999'); total is the arithmetic sum.
• cobra_eligible, dependents_covered_flag — decoded YES/NO attributes from the classification.
• benefit_contribution_id, business_group_id, element_type_id, formula_id — the identifying surrogate keys and foreign keys linking to the underlying definitions.

Common Use Cases and Queries

Typical scenarios include auditing contribution rates per business group, validating coverage types against the US_BENEFIT_COVERAGE lookup, and feeding benefit data into extracts. The security profile and language filters are applied automatically, so queries need not add them.

• List all contributions with readable coverage and COBRA attributes:
  SELECT benefit_name, coverage_type, employee_contribution, employer_contribution, cobra_eligible FROM apps.hrfv_def_benefit_contributions;
• Total contributions by business group as of a date:
  SELECT business_group_name, SUM(total_contribution) FROM apps.hrfv_def_benefit_contributions WHERE SYSDATE BETWEEN effective_start_date AND effective_end_date GROUP BY business_group_name;
• Locate a specific benefit by element type:
  SELECT benefit_name, formula_id, dependents_covered_flag FROM apps.hrfv_def_benefit_contributions WHERE element_type_id = :p_element_type_id;