Results for “ben_fmly_mmbr”

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

Overview

APPS.BENBV_OIPL_V is a business view in the Oracle E-Business Suite Benefits (BEN) module, exposed under the APPS schema. It presents configuration data for the "Other Input Plan List" (OIPL), the setup construct used to define the plan options that an employee, participant, or administrator may select during enrollment. The view is part of the BENBV ("Benefits View") family, which supplies flattened, decode-enhanced representations of Benefits setup tables for reporting, integration, and user-facing lookups. The suffix "OIPL" refers to the OIPL entity, whose root table is BEN_OIPL_F.

The view is documented in ETRM for both Oracle EBS 12.1.1 and 12.2.2 with identical base-object dependencies, which indicates that its definition and interfaces are stable across these two releases. Its principal role is to make OIPL configuration rows readable: coded columns are supplemented with decoded meaning through calls to the HR_BIS package, and descriptive flexfield context is passed through for downstream use. This makes the view well suited to ad hoc reporting, data extraction for interfaces, and diagnostic analysis of enrollment behavior.

Underlying Base Objects

The view is defined over a single base table, BEN_OIPL_F, which is referenced through the BEN_OIPL_F synonym. BEN_OIPL_F is the OIPL entity table in the Benefits schema and stores one row per plan-list configuration record, including its effective-dating columns (EFFECTIVE_START_DATE and EFFECTIVE_END_DATE) and its business-group scoping column (BUSINESS_GROUP_ID). The view aliases this table as COP and projects its columns with minimal transformation, preserving the effective-dated structure of the source. Because BEN_OIPL_F is date-tracked, queries must apply appropriate effective-date predicates to avoid returning historical or future-dated rows.

The second referenced object is the HR_BIS package, a Benefits utility package that provides the BIS_DECODE_LOOKUP function. That function is invoked repeatedly in the view to resolve lookup codes into their display meanings. Both dependencies are documented in the ETRM metadata: BEN_OIPL_F (SYNONYM) and HR_BIS (PACKAGE). The view does not introduce additional joins to the underlying tables; de-normalization occurs through the decode lookups rather than through relational joins.

Key Columns

The view exposes the full OIPL column set, including primary and foreign key identifiers (OIPL_ID, PL_ID, OPT_ID, ACTL_PREM_ID, BUSINESS_GROUP_ID), legislative attributes (LEGISLATION_CODE, LEGISLATION_SUBGROUP), ordering and identification fields (IVR_IDENT, ORDR_NUM, SHORT_CODE, SHORT_NAME), and audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE).

• PER_CVRD_CD and its decoded counterpart – PER_CVRD_CD is resolved through HR_BIS.BIS_DECODE_LOOKUP('BEN_PER_CVRD', COP.PER_CVRD_CD). This column identifies the persons covered by the plan option, which is directly relevant to the search term "ben_per_cvrd". Applications and reports frequently reference this lookup type when determining eligibility of dependents and other covered individuals.
• Enrollment controls – AUTO_ENRT_FLAG, AUTO_ENRT_MTHD_RL, DFLT_ENRT_CD, DFLT_ENRT_DET_RL, ENRT_CD, and ENRT_RL define whether and how automatic or default enrollment occurs for the option.
• Mandatory and verification rules – MNDTRY_FLAG/MNDTRY_RL, VRFY_FMLY_MMBR_CD, POSTELCN_EDIT_RL, RQD_PERD_ENRT_NENRT_UOM, and RQD_PERD_ENRT_NENRT_VAL govern whether enrollment is required, whether family member verification is enforced, and the required period parameters.
• Participant designation – PCP_DSGN_CD and PCP_DPNT_DSGN_CD, both decoded through the BEN_PCP_DSGN lookup, control participant and dependent designation rules.
• Eligibility and rate applicability flags – ELIG_APLS_FLAG, TRK_INELIG_PER_FLAG, DRVBL_FCTR_PRTN_ELIG_FLAG, PRTN_ELIG_OVRID_ALWD_FLAG, and DRVBL_FCTR_APLS_RTS_FLAG, each decoded through the YES_NO lookup.
• Descriptive flexfield – the literal '_DF:BEN:BEN_OIPL_F:COP' is returned to signal the DFF context for the OIPL entity.

Common Use Cases and Queries

Typical uses include extracting OIPL configuration for a business group, auditing enrollment rules, and joining the view to plan and option tables to produce readable plan-list documentation. A basic query filtering to current effective rows might read:

SELECT oipl_id, pl_id, opt_id, short_name, per_cvrd_cd,
       mndtry_flag, auto_enrt_flag, dflt_enrt_cd
FROM   apps.benbv_oipl_v
WHERE  business_group_id = :p_bg_id
AND    TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

To report on the coverage rule specifically, a query on PER_CVRD_CD can be used, and joining to BEN_PL_F and BEN_OPT_F on PL_ID and OPT_ID provides plan and option descriptions. Because decoded values are generated by HR_BIS lookups at query time, results reflect the current lookup configuration, so care should be taken when the output is used for point-in-time reconciliation. In all cases, the effective-dating predicates and business group filter are essential to return deterministic results.