Results for “emp_attribute30”

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

Overview

BEN_ELIG_MRTL_STS_PRTE is an APPS-owned database view within the Oracle Advanced Benefits (BEN) module. It exposes the marital status eligibility rule configuration used by Oracle EBS benefits eligibility processing, and is available in both the 12.1.1 and 12.2.2 releases. Specifically, the view surfaces the "Marital Status" eligibility criterion — the rules that determine whether a participant qualifies for a benefit program or plan based on their recorded marital status (for example, Single, Married, Divorced, or Widowed).

The view is a "current date" wrapper over its underlying base table. Rather than returning all rows, it filters the base table to only those records whose effective date range encompasses the current database session date, resolved dynamically from FND_SESSIONS using USERENV('SESSIONID'). This behavior means the view presents the effective, point-in-time eligibility configuration applicable to the running session — the configuration the Benefits engine evaluates during eligibility determination. Because the row set is session-date sensitive, the view is well suited for real-time reporting and integration queries but not for retrieving historical or future-dated rule definitions, for which the base table should be queried directly.

Underlying Base Objects

The view is defined over two documented synonyms, both in APPS:

• BEN_ELIG_MRTL_STS_PRTE_F — the base table storing the marital status eligibility rule definitions. The view selects all of its columns and applies the effective-date predicate against it (aliased EMP).
• FND_SESSIONS — the Oracle Applications session table, used in the view text as a scalar subquery to retrieve the effective date (SS.EFFECTIVE_DATE) for the current SESSION_ID returned by USERENV('SESSIONID').

The WHERE clause restricts rows so that EMP.EFFECTIVE_START_DATE is less than or equal to the session effective date and EMP.EFFECTIVE_END_DATE is greater than or equal to it, yielding only rows effective on the session date. Filtering is applied symmetrically via two identical subqueries against FND_SESSIONS.

Key Columns

• ELIG_MRTL_STS_PRTE_ID — primary identifier for the marital status eligibility rule row.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date range defining rule validity; these drive the view's session-date filtering.
• BUSINESS_GROUP_ID — identifies the business group (legislative context) to which the rule belongs.
• ELIGY_PRFL_ID — the eligibility profile that owns or references this marital status criterion, linking the rule to a broader eligibility definition.
• EXCLD_FLAG — indicates whether the criterion is used to exclude (Y) rather than include participants matching the specified marital status.
• MARITAL_STATUS — the marital status value evaluated by the rule.
• EMP_ATTRIBUTE_CATEGORY and EMP_ATTRIBUTE1 through EMP_ATTRIBUTE30 — the descriptive flexfield context and attribute segments attached to the rule, enabling organization-specific qualification criteria.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard audit columns.
• OBJECT_VERSION_NUMBER — optimistic locking version for the underlying row.

Common Use Cases and Queries

Typical uses include validating and auditing how marital status eligibility is configured, troubleshooting why a participant did or did not qualify for a plan, and extracting rule definitions for integration or data migration. The following query lists all currently effective marital status rules for a given business group:

• SELECT elig_mrtl_sts_prte_id, eligy_prfl_id, excld_flag, marital_status, effective_start_date, effective_end_date FROM ben_elig_mrtl_sts_prte WHERE business_group_id = :p_business_group_id ORDER BY eligy_prfl_id, effective_start_date;

To identify exclusion rules (those that disqualify participants on marital status grounds), filter on the exclusion flag:

• SELECT p.elig_mrtl_sts_prte_id, p.eligy_prfl_id, p.marital_status FROM ben_elig_mrtl_sts_prte p WHERE p.excld_flag = 'Y' AND p.business_group_id = :p_business_group_id;

To inspect flexfield-based criteria, select the attribute category and segments alongside the core columns. Because the view always returns the session-effective snapshot, no additional date predicate is normally required for real-time reporting; for a historical as-of-date analysis, query the base table BEN_ELIG_MRTL_STS_PRTE_F directly with an explicit date range on EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.