Results for “benbv_oipl_v”

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

Overview

BENBV_OIPL_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, belonging to the Advanced Benefits (BEN) product family. It identifies the processing characteristics of an option that is provided by a plan. In the Advanced Benefits data model, a plan (PL) may offer one or more options (OPT), and the relationship between a plan and an option — together with the rules governing how that option is processed during enrollment — is stored in the underlying option-in-plan table. BENBV_OIPL_V exposes that relationship in a flattened, report-friendly form.

The view is designated with the "BV" naming convention typical of Oracle EBS business views. These views are intended for use in reports, Discoverer workbooks, concurrent program queries, and integration interfaces rather than for direct transactional updates. Because it presents decode values alongside raw codes, it allows report authors and integration developers to display human-readable descriptions of enrollment behavior without repeatedly joining to lookup tables or invoking the HR_BIS package manually. The view is documented as VALID and appears in ETRM metadata for both Oracle EBS 12.1.1 and 12.2.2.

Underlying Base Objects

According to the ETRM 12.2.2 metadata, BENBV_OIPL_V is defined over two documented objects: the synonym BEN_OIPL_F and the package HR_BIS. BEN_OIPL_F is the underlying base table (referenced through a synonym) that stores option-in-plan processing attributes. Each row in BEN_OIPL_F corresponds to a specific option offered within a specific plan, subject to the standard effective-dating pattern used throughout Oracle Advanced Benefits (EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns).

The second referenced object, HR_BIS, is the Oracle HR Business Intelligence System package. Within the view definition, calls are made to HR_BIS.BIS_DECODE_LOOKUP, which resolves coded values into their decoded meanings. This is the mechanism that allows the view to return both the stored code and its descriptive text for each attribute. The view therefore does not join to separate lookup tables directly; it relies on the BIS decode function to translate codes at runtime. The view additionally references the descriptive flexfield marker '_DF:BEN:BEN_OIPL_F:COP', indicating that the option-in-plan descriptive flexfield segments are surfaced through the standard business view flexfield convention.

Key Columns

The view returns effective-dating columns (EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) and identifying columns including OIPL_ID (the option-in-plan primary key), PL_ID (plan), OPT_ID (option), BUSINESS_GROUP_ID, ACTL_PREM_ID, and LEGIS. Processing attributes include IVR_IDENT, ORDR_NUM, SHORT_CODE, SHORT_NAME, and URL_REF_NAME.

Enrollment behavior is governed by columns such as AUTO_ENRT_MTHD_RL, DFLT_ENRT_DET_RL, RQD_PERD_ENRT_NENRT_UOM, RQD_PERD_ENRT_NENRT_VAL, RQD_PERD_ENRT_NENRT_RL, MNDTRY_RL, POSTELCN_EDIT_RL, VRFY_FMLY_MMBR_RL, and ENRT_RL. Coded attributes are presented in paired form — a raw code column and its decoded equivalent. For example, OIPL_STAT_CD is decoded via the BEN_STAT lookup, DFLT_ENRT_CD via BEN_DFLT_ENRT, PER_CVRD_CD via BEN_PER_CVRD, VRFY_FMLY_MMBR_CD via BEN_FMLY_MMBR, ENRT_CD via BEN_ENRT, and PCP_DSGN_CD and PCP_DPNT_DSGN_CD via BEN_PCP_DSGN. Yes/No flags decoded through the YES_NO lookup include MNDTRY_FLAG, DFLT_FLAG, ELIG_APLS_FLAG, TRK_INELIG_PER_FLAG, DRVBL_FCTR_PRTN_ELIG_FLAG, PRTN_ELIG_OVRID_ALWD_FLAG, DRVBL_FCTR_APLS_RTS_FLAG, AUTO_ENRT_FLAG, and HIDDEN_FLAG.

Common Use Cases and Queries

Typical uses include reporting on the processing configuration of plan options, validating setup during implementations, and feeding downstream integrations that require decoded option characteristics.

• Listing all active options for a given plan with their mandatory and default indicators.
• Identifying options flagged as hidden, mandatory, or auto-enrolled for a benefits offering.
• Extracting decoded enrollment, designation, and coverage attributes for reconciliation or audit reports.

A representative query selects the option, plan, order number, status, and key flags for a specific plan:

SELECT oipl_id, pl_id, opt_id, ord_num, short_code, short_name, oipl_stat_cd, mndtry_flag, dflt_flag, auto_enrt_flag, hidden_flag
FROM apps.benbv_oipl_v
WHERE pl_id = :p_plan_id
ORDER BY ord_num;

Because decode logic is embedded via HR_BIS.BIS_DECODE_LOOKUP, returned flag and status columns are already human-readable, making the view suitable for direct display in BI Publisher reports, Oracle Reports, and Discoverer workbooks without additional lookup joins.