Results for “ben_ovrid_rsn”

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

Overview

APPS.BEN_ELIG_PER_OPT_D is a reporting and integration view within the Oracle E-Business Suite Advanced Benefits (BEN) module. It presents a denormalized, human-readable projection of the eligibility participation option definitions stored in the base table BEN_ELIG_PER_OPT_F. Rather than exposing raw lookup codes and foreign key identifiers, the view resolves those codes into descriptive meanings through joins to HR_LOOKUPS, and resolves the owning plan option name through BEN_OPT_F. This makes the view suitable for concurrent program output, Oracle Reports, OBIEE/XML Publisher extracts, and ad hoc inquiry by functional users who require interpretation of the underlying configuration without knowledge of the code values.

The view is defined in the APPS schema and is exposed to end users and integrators exactly as documented in ETRM for release 12.2.2, with equivalent behavior in 12.1.1. Because it carries the ROWID of the base row as ROW_ID, it can also be used as the driving query for a custom form or an OAF-based maintenance page, although Oracle's supported maintenance path for these records remains the standard Benefits setup forms.

Underlying Base Objects

The view is defined over the following documented objects:

• BEN_ELIG_PER_OPT_F (synonym) — the primary base table holding eligibility participation option rows; aliased EPO in the view text.
• BEN_OPT_F (synonym) — the plan option definition table, aliased OPT, providing the option NAME. The join is outer (OPT.OPT_ID (+) = epo.OPT_ID).
• FND_USER (synonym) — the application user directory, aliased FUSER, supplying USER_NAME for the LAST_UPDATED_BY audit column via an outer join.
• HR_LOOKUPS (view) — joined five times, aliased ELIG, OVRIDN, NO_MX_PRTN_OVRID_THRU, PRTN_OVRIDN_RSN, and ONCE_R_CNTNG, to translate stored flags and reason codes into meanings.
• HR_API (package) — documented as a referenced object, reflecting the HR security/utility dependency chain that HR_LOOKUPS itself carries.

Because BEN_ELIG_PER_OPT_F is a date-effective (_F) table, the view inherits EFFECTIVE_START_DATE and EFFECTIVE_END_DATE and therefore returns multiple rows per logical configuration across its date-tracked history.

Key Columns

• ROW_ID — the ROWID of the underlying BEN_ELIG_PER_OPT_F row; useful as a unique handle.
• ELIG_PER_OPT_ID — the primary key of the eligibility participation option record.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-effective range of the row.
• OPT_NAME — the resolved plan option name from BEN_OPT_F.
• ELIG_MEANING — decoded YES/NO meaning of ELIG_FLAG, indicating whether the option is eligible.
• WAIT_PERD_CMPLTN_DT, WAIT_PERD_CMPLTN_DATE, WAIT_PERD_STRT_DT — waiting-period completion and start values.
• PRTN_STRT_DT / PRTN_END_DT — the participation start and end dates for the option.
• OVRIDN_MEANING — decoded YES/NO meaning of PRTN_OVRIDN_FLAG, indicating whether participation dates are overridden.
• NO_MX_PRTN_OVRID_THRU_MEANING — the column the user searched for; it exposes the decoded YES/NO meaning of NO_MX_PRTN_OVRID_THRU_FLAG via HR_LOOKUPS with lookup_type 'YES_NO'.
• HRS_WKD_QUAL_MEANING — decoded meaning of ONCE_R_CNTUG_CD from lookup type 'BEN_ONCE_R_CNTNG', governing hours-worked qualification counting.
• PRTN_OVRIDN_THRU_DT — the date through which participation is overridden.
• PRTN_OVRIDN_RSN_MEANING — decoded override reason from lookup type 'BEN_OVRID_RSN'.
• LAST_UPDATED_BY / LAST_UPDATE_DATE — audit trail resolved to a user name.

Common Use Cases and Queries

Typical usage includes auditing participation-date overrides, verifying the No Max Participation Override Through flag for a given option, and feeding downstream extracts. A representative query filtering on the searched flag follows:

SELECT elig_per_opt_id, opt_name, elig_meaning,
       no_mx_prtn_ovrid_thru_meaning,
       prtn_strt_dt, prtn_end_dt, prtn_ovridn_thru_dt,
       prtn_ovridn_rsn_meaning
  FROM apps.ben_elig_per_opt_d
 WHERE SYSDATE BETWEEN effective_start_date AND effective_end_date
   AND no_mx_prtn_ovrid_thru_meaning = 'Yes';

Because the flag column is stored as a lookup code, any query must compare against the decoded MEANING rather than the code, unless HR_LOOKUPS is joined independently on the base table. Practitioners should also apply the date-effective predicate to avoid retrieving historical rows, and should be aware that the outer joins on BEN_OPT_F and FND_USER can yield nulls for orphaned or purged references.