Results for “ben_oipl_f”

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

Overview

BEN_OIPL_F is a table in the BEN schema (Oracle Advanced Benefits) within Oracle E-Business Suite 12.1.1 and 12.2.2. Its description, "Option in plan," identifies it as the intersection that associates a benefit option (from the option definition) with a benefit plan (the plan under which that option may be offered). It effectively answers the question, "Which options are available in which plans?" and carries the configuration flags that govern how each option behaves in that plan context — mandatory or default enrollment, eligibility applicability, auto-enrollment, and similar control attributes.

The table is date-tracked. Its primary key, BEN_OIPL_F_PK, is a three-part composite of OIPL_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. The presence of effective start and end dates in the PK indicates DATE_TRACKED (datetrack) behavior, where each row represents a version of the option-in-plan relationship valid over a specific effective period. In ETRM terms this is also documented as a unique business key, so OIPL_ID plus the effective dates uniquely identify a row.

The metadata reports no foreign-key relationships (Data Vault classification heuristic: standalone). From a data-modeling standpoint, this is best treated as a link (an intersection between option and plan) that also carries descriptive and control attributes, making it behave somewhat like a satellite attached to that link.

Key Information Stored

The 79 documented columns include identity, control flags, date-track columns, and the standard audit/attribute block. The most significant are:

• OIPL_ID — surrogate identifier for the option-in-plan row; part of BEN_OIPL_F_PK.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — datetrack columns; also part of BEN_OIPL_F_PK and the unique business key.
• PL_ID — plan identifier; the plan to which the option is attached.
• OPT_ID — option identifier; the option being offered under the plan.
• BUSINESS_GROUP_ID — the HR business group (legislative/enterprise context) owning the configuration.
• OIPL_STAT_CD — status code of the option-in-plan record (e.g., active/inactive).
• MNDTRY_FLAG — whether the option is mandatory for the plan.
• DFLT_FLAG — whether the option is defaulted.
• DFLT_ENRT_CD — default enrollment indicator for the option.
• ENRT_CD — enrollment code controlling how enrollment into the option occurs.
• AUTO_ENRT_FLAG — whether automatic enrollment is enabled.
• ELIG_APLS_FLAG — whether eligibility applies to the option.
• HIDDEN_FLAG — whether the option is hidden from the enrollment user interface.
• SHORT_CODE / SHORT_NAME — short display code and name for the option in plan.
• COP_ATTRIBUTE_CATEGORY / COP_ATTRIBUTE1..30 — flexfield context and descriptive flexfield attributes.

Standard audit columns are present: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, and OBJECT_VERSION_NUMBER for optimistic locking.

Common Use Cases and Queries

Typical reporting and configuration queries select the options attached to a plan and their control flags.

• List all active options in a given plan as of a date:

SELECT oipl_id, opt_id, pl_id, short_name, mndtry_flag, dflt_flag
FROM ben.ben_oipl_f
WHERE pl_id = :p_plan_id
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

• Identify options flagged mandatory or auto-enrolled within a business group:

SELECT oipl_id, pl_id, opt_id
FROM ben.ben_oipl_f
WHERE business_group_id = :p_bg_id
AND mndtry_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

• Join to plan and option definition tables to produce a configuration report of plans and their options, including enrollment rules. Reporting generally uses the "as of" effective-date filter because historical datetrack rows exist.
• Superseded/audit reporting by selecting all versions of a given OIPL_ID to track configuration changes over time.

Related Objects

Even though the metadata classes this table as standalone, in practice it is referenced by plan and option definitions and by enrollment/eligibility logic:

• BEN_PL_F — the benefit plan definition. Join: BEN_OIPL_F.PL_ID = BEN_PL_F.PL_ID.
• BEN_OPT_F — the benefit option definition. Join: BEN_OIPL_F.OPT_ID = BEN_OPT_F.OPT_ID.
• BEN_PL_OPT_F — plan/option configuration, typically the parent relationship for OIPL records.
• BEN_OIPL_RL_F — option-in-plan rules (eligibility, enrollment, and mandatory logic referencing OIPL rows).
• BEN_OIPL_ENRT_RL_F — enrollment rules attached to the option in plan.
• BEN_OIPL_ELIG_RSLT_F — eligibility results evaluated against option-in-plan rows.
• BEN_PTIPL_F / BEN_PLTP_* — related program/plan/type-in-plan tables in the same configuration hierarchy.
• HR_ALL_ORGANIZATION_UNITS — business group / organization context via BUSINESS_GROUP_ID.

The BEN Advanced Benefits PL/SQL APIs (for example, the OIPL maintenance routines in the BEN_* API package family) are the supported programmatic route for creating and maintaining these rows, rather than direct DML.