Results for “ben_elig_gndr_prte_d”

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

Overview

BEN_ELIG_GNDR_PRTE_D is a denormalized reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the Advanced Benefits (BEN) product. It presents gender-based eligibility participation restrictions — the rules that determine whether a person is included in or excluded from an eligibility profile based on their recorded sex. The view is a "descriptive" wrapper (indicated by the trailing _D) over the transactional restriction entity BEN_ELIG_GNDR_PRTE_F, collapsing foreign-key identifiers into human-readable lookup meanings and user names so that the data can be consumed directly in reports, extracts, and integration interfaces without additional joins.

Its principal value lies in the resolution of coded values. Rather than exposing the raw lookup code stored in the base table, the view resolves it through HR_LOOKUPS on the SEX lookup type, returning the descriptive meaning in the SEX_MEANING column. This is the attribute commonly sought when users search for "sex_meaning" in the context of gender eligibility restrictions, and it mirrors the naming convention Oracle applies across similar gender restriction views.

Underlying Base Objects

The view is defined over four referenced objects, combined with outer joins so that restriction rows are never lost when related descriptive data is missing:

• BEN_ELIG_GNDR_PRTE_F — the core transactional table holding the gender eligibility restriction rows. Aliased EGN, it supplies the primary key, effective dates, the sex lookup code, the exclusion flag, and audit columns.
• BEN_ELIGY_PRFL_F — the eligibility profile header, aliased ELP. Joined on ELIGY_PRFL_ID to retrieve the profile name (ELP.NAME, surfaced as ELIGY_PRFL_NAME).
• FND_USER — aliased FUSER. Outer-joined on LAST_UPDATED_BY to translate the updating user's ID into FUSER.USER_NAME.
• HR_LOOKUPS — aliased SEX. Outer-joined on LOOKUP_CODE equal to the restriction's SEX value, restricted to LOOKUP_TYPE = 'SEX', yielding the SEX_MEANING column.

HR_API (PACKAGE) is also documented as a referenced object, reflecting the standard HR security and utility routines used in the benefits lookup domain. The joins to FND_USER, BEN_ELIGY_PRFL_F, and HR_LOOKUPS are all outer joins, ensuring completeness of restriction records even where reference data is not yet populated.

Key Columns

The view exposes the following documented columns:

• ROW_ID — the row identifier of the underlying BEN_ELIG_GNDR_PRTE_F row.
• ELIG_GNDR_PRTE_ID — the primary key of the gender participation restriction record.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-effective range governing when the restriction is active, central to date-tracked benefits processing.
• ELIGY_PROFL_NAME (ELP.NAME) — the name of the parent eligibility profile to which the restriction belongs.
• EXCLD_FLAG — indicates whether the rule operates as an exclusion (Y) or inclusion (N) for the specified sex.
• SEX_MEANING — the descriptive meaning of the sex lookup code, resolved from HR_LOOKUPS where LOOKUP_TYPE = 'SEX'. This is the decoded value users typically seek.
• LAST_UPDATE_DATE — the date the restriction row was last changed.
• LAST_UPDATED_BY — the user name of the person who last updated the row, derived from FND_USER.

Common Use Cases and Queries

Typical uses include benefits configuration audits, eligibility rule documentation, data migration validation, and integration extracts that must present gender restrictions in descriptive form.

Listing active gender restrictions with their decoded meaning:

• SELECT elig_gndr_prte_id, eligy_profl_name, sex_meaning, excld_flag, effective_start_date, effective_end_date FROM ben_elig_gndr_prte_d WHERE SYSDATE BETWEEN effective_start_date AND effective_end_date ORDER BY eligy_profl_name, sex_meaning;

Identifying exclusion rules for a specific sex:

• SELECT eligy_profl_name, sex_meaning, last_update_date, last_updated_by FROM ben_elig_gndr_prte_d WHERE excld_flag = 'Y' AND sex_meaning = 'Male';

Auditing recent changes to gender restrictions:

• SELECT elig_gndr_prte_id, eligy_profl_name, sex_meaning, last_updated_by, last_update_date FROM ben_elig_gndr_prte_d WHERE last_update_date >= TRUNC(SYSDATE) - 30 ORDER BY last_update_date DESC;

Because the view performs the lookup and user resolution internally, these queries require no additional joins, making it well suited to ad hoc reporting and downstream interfaces.