Results for “ben_opt_typ”

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

Overview

APPS.BENBV_PL_TYP_V is a business-group-secured read-only view in Oracle E-Business Suite Advanced Benefits (Benefit) that exposes plan type definitions stored in the BEN_PL_TYP_F table. It is one of the standard "BV" (Business View) objects shipped with the product, intended primarily for reporting, integration, and inquiry against the underlying dated plan type entity. The view presents the effective-dated attributes of a plan type — including its name, short name, short code, enrollment minimums and maximums, IVR identity, legislation context, and the descriptive flexfield reference — alongside decoded (user-meaningful) values for the various code columns. Decoding is performed through calls to HR_BIS.BIS_DECODE_LOOKUP, which resolves lookup codes such as BEN_OPT_DSPLY_FMT into their displayed meanings, so consumers do not need to join the lookup tables themselves.

Underlying Base Objects

The view is defined over two referenced objects: the synonym BEN_PL_TYP_F (which resolves to the plan type table, the "F" suffix denoting a datetracked/effective-dated entity) and the package HR_BIS, whose BIS_DECODE_LOOKUP function provides the lookup translations. The view is strictly read-only (WITH READ ONLY) and enforces multi-tenant visibility through the predicate PTP.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, PTP.BUSINESS_GROUP_ID). This means a session with a business group security profile set sees only rows for its own business group; when no security profile business group is resolved, all business groups are returned. Because plan types are effective-dated, every row carries EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, and callers should apply appropriate effective-date filtering to obtain the correct version of a plan type.

Key Columns

• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Datetrack validity window for the plan type definition row.
• NAME, SHORT_NAME, SHORT_CODE, IVR_IDENT — Descriptive and cross-reference identifiers for the plan type.
• MX_ENRL_ALWD_NUM / MN_ENRL_RQD_NUM — Maximum enrollment allowed and minimum enrollment required, with the NO_MX_ENRL_NUM_DFND_FLAG and NO_MN_ENRL_NUM_DFND_FLAG indicating whether those numbers are defined (decoded through YES_NO).
• LEGISLATION_CODE / LEGISLATION_SUBGROUP — Legislative context governing the plan type.
• OPT_DSPLY_FMT_CD — The option display format code; the paired decoded value from lookup type BEN_OPT_DSPLY_FMT is the object of the searched term "ben_opt_dsply_fmt".
• COMP_TYP_CD, PL_TYP_STAT_CD, OPT_TYP_CD — Competition type, plan type status, and option type codes, each accompanied by a decoded lookup value (BEN_COMP_TYP, BEN_STAT, BEN_OPT_TYP).
• Descriptive flexfield — Exposed via the marker '_DF:BEN:BEN_PL_TYP_F:PTP' for DFF segment retrieval in reports.
• PL_TYP_ID, BUSINESS_GROUP_ID — Primary key and tenant identifier.
• WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE for audit.

Common Use Cases and Queries

Typical use cases include benefit plan type listings, validation of enrollment minimum/maximum configuration, and lookup-driven reporting where display formats (BEN_OPT_DSPLY_FMT) must be shown rather than coded. A simple listing query:

SELECT name, short_name, opt_dsply_fmt_cd, dsply_fmt_meaning
FROM   apps.benbv_pl_typ_v
WHERE  TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

To find plan types with no maximum enrollment defined:

SELECT pl_typ_id, name, mx_enrl_alwd_num, no_mx_enrl_num_dfnd_flag
FROM   apps.benbv_pl_typ_v
WHERE  no_mx_enrl_num_dfnd_flag = 'Y'
AND    TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Because the view already applies business group security and decode logic, it is well suited to BI Publisher data models and EBS integration extracts, reducing the need for direct joins to the base plan type table and lookup views.