Results for “mar_status”

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

Overview

APPS.BEN_ELIG_MRTL_STS_PRTE_D is a reporting view in the Oracle E-Business Suite Advanced Benefits (BEN) module. It exposes the marital status eligibility criteria rows that drive benefit plan eligibility determination. The "_D" suffix denotes a denormalized, description-bearing view: whereas the underlying transactional table stores only coded foreign keys, this view resolves those codes into user-facing text. It surfaces the eligibility profile name, the translated meaning of the marital status lookup, the exclusion flag, and the audit identity of the last updater. The view is read-only and is not a primary data-entry interface; it exists to support inquiry, reporting, and integration extraction across releases 12.1.1 and 12.2.2, where the definition is materially identical.

Underlying Base Objects

The view is defined over four base objects, all referenced through APPS synonyms:

• BEN_ELIG_MRTL_STS_PRTE_F — the driving table, aliased EMP. Holds marital status eligibility criteria rows, including the profile reference and marital status code.
• BEN_ELIGY_PRFL_F — the eligibility profile header, aliased ELP, joined on ELIGY_PRFL_ID to supply the profile NAME.
• HR_LOOKUPS — a view over the lookup values, aliased MARITAL_STATUS, joined on LOOKUP_CODE = MARITAL_STATUS and LOOKUP_TYPE = 'MAR_STATUS' to resolve the code to its MEANING.
• FND_USER — aliased FUSER, joined on USER_ID = LAST_UPDATED_BY to expose the USER_NAME of the last modifier.

All three joins are outer joins (the (+) operator appears on FUSER, ELP, and the two MARITAL_STATUS predicates). A row therefore survives even when the profile, user, or lookup value is missing, in which case the corresponding descriptive column returns null. The documented object list also cites the HR_API package, which is not referenced in the view text but is a common dependency of the BEN eligibility API stack.

Key Columns

• ELIG_MRTL_STS_PRTE_ID — primary key of the eligibility criteria row.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-tracked validity window of the criteria record.
• NAME — the eligibility profile name, the most business-meaningful attribute.
• EXCLD_FLAG — indicates whether the criteria are used to include or exclude persons (Y typically means exclusion).
• MEANING — the descriptive text for the marital status lookup, resolved via LOOKUP_TYPE 'MAR_STATUS'. This is the column a search for "mar_status" most commonly targets.
• CRITERIA_SCORE / CRITERIA_WEIGHT — scoring attributes used for sequenced or weighted eligibility.
• LAST_UPDATE_DATE / USER_NAME — audit trail showing when and by whom the row was last changed.
• ROWID — physical row identifier, selectable for tooling or diagnostics purposes.

Common Use Cases and Queries

Typical usages include reconciling eligibility profiles, extracting marital status criteria for downstream systems, and auditing configuration changes. A representative query lists all profiles that use a given marital status:

SELECT name, meaning, excld_flag
FROM   apps.ben_elig_mrtl_sts_prte_d
WHERE  meaning = 'Married';

An audit-oriented query identifies recently modified criteria and their owners:

SELECT elig_mrtl_sts_prte_id, name, meaning,
       last_update_date, user_name
FROM   apps.ben_elig_mrtl_sts_prte_d
WHERE  last_update_date >= SYSDATE - 30
ORDER BY last_update_date DESC;

Because the view is denormalized and outer-joined, it is well suited to ad-hoc reporting and BI extracts, but it should not be used for transactional eligibility evaluation, which is performed by the BEN eligibility APIs. Queries filtering on MEANING should be aware that case and translation depend on the HR_LOOKUPS meaning for LOOKUP_TYPE 'MAR_STATUS'.