Results for “mx_enrl_alwd_num”

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

Overview

BENBV_PL_TYP_V is a read-only Oracle EBS Advanced Benefits (BEN) view owned by the APPS schema. It categorizes benefits plans according to the type of compensation benefit they provide, and enforces the fundamental business rule that a plan may be associated with only one plan type. The view is a "BV" (business view) object, meaning it is intended for reporting, integration, and end-user query access rather than for transactional data manipulation. The underlying definition carries a WITH READ ONLY clause, so consumers cannot issue DML directly against it.

For users searching on opt_dsply_fmt_cd, the relevance is direct: OPT_DSPLY_FMT_CD is one of the coded columns exposed by this view. It stores the option display format code for each plan type, and BENBV_PL_TYP_V additionally supplies a decoded, user-facing value of that code via a lookup call to HR_BIS.BIS_DECODE_LOOKUP('BEN_OPT_DSPLY_FMT', ...). This allows report authors to present either the raw code or the translated meaning without writing their own decode logic.

Underlying Base Objects

The view is defined over a single base object, BEN_PL_TYP_F (referenced in the metadata as a synonym), aliased PTP. This is the plan type base table in the BEN module, which holds the master definition of each plan type, including its effective-dated attributes and descriptive flexfield context.

The second documented dependency is the HR_BIS package, which provides the BIS_DECODE_LOOKUP function used to translate code values into their lookup meanings, and GET_SEC_PROFILE_BG_ID, which is used in the WHERE clause to enforce business group security. The view filter is:

• WHERE PTP.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, PTP.BUSINESS_GROUP_ID) — restricting rows to the current security profile's business group when one is set, and returning all groups otherwise.

The view is therefore a thin presentation layer: it does not join multiple tables, does not aggregate, and does not transform the base row set beyond adding decoded lookup columns, a descriptive flexfield placeholder token, and column aliasing.

Key Columns

The columns fall into several logical groups: effective dating, identity, enrollment constraints, coding with decodes, and WHO/audit columns, plus the identifier columns.

• EFFECTIVE_START_DATE, EFFECTIVE_END_DATE — the date range for which the plan type definition is valid.
• NAME, SHORT_NAME, SHORT_CODE, IVR_IDENT — identification attributes, including the IVR identifier used in interactive voice response enrollment.
• MX_ENRL_ALWD_NUM, MN_ENRL_RQD_NUM — maximum enrollments allowed and minimum enrollments required.
• NO_MX_ENRL_NUM_DFND_FLAG, NO_MN_ENRL_NUM_DFND_FLAG — flags (decoded via YES_NO) indicating whether a maximum/minimum enrollment number is defined.
• LEGISLATION_CODE, LEGISLATION_SUBGROUP — legislative context of the plan type.
• OPT_DSPLY_FMT_CD and its decoded companion OPT_DSPLY_FMT_CD_M — the option display format code and its lookup meaning (BEN_OPT_DSPLY_FMT).
• COMP_TYP_CD / COMP_TYP_CD_M — compensation type code and decode (BEN_COMP_TYP).
• PL_TYP_STAT_CD / PL_TYP_STAT_CD_M — plan type status code and decode (BEN_STAT).
• OPT_TYP_CD / OPT_TYP_CD_M — option type code and decode (BEN_OPT_TYP).
• "_DF:PTP" — a descriptive flexfield placeholder token used by the framework to surface DFF segments.
• PL_TYP_ID, BUSINESS_GROUP_ID — primary and foreign key identifiers.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE — standard WHO audit columns.

Common Use Cases and Queries

This view is commonly used in benefits configuration reports, plan type validation extracts, and integration interfaces. A frequent use is reporting on the option display format alongside the plan type identity:

• Listing active plan types with their compensation type and status meanings for a configuration review.
• Filtering plan types by OPT_DSPLY_FMT_CD to identify how options are presented to participants.
• Joining decoded columns to build multi-language or user-friendly extracts without embedding lookup SQL in the report.
• Feeding downstream interfaces where a stable, security-filtered list of plan types is required.

Sample query:

SELECT pl_typ_id, name, short_code, opt_dsply_fmt_cd, opt_dsply_fmt_cd_m, comp_typ_cd_m, pl_typ_stat_cd_m FROM apps.benbv_pl_typ_v WHERE opt_dsply_fmt_cd = :p_format ORDER BY name;

Because the view is read-only and already applies business group security, it is safe to expose to reporting users. Note the dependency on HR_BIS for decoding; if a lookup value is missing, the _M column may return null while the raw _CD value remains present, so reports should handle both.