Search Results ben_oiplip_f_pk

Overview

BEN_OIPLIP_F is a transactional table in the BEN schema, the Advanced Benefits (OAB) module of Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented description, "Option in plan in program," identifies the table's core role: it records the association between a benefit option (an option belonging to a plan) and a program, and preserves that association as a dated, effective-dated relationship. Because plans and options are frequently reconfigured during annual enrollment or mid-year plan changes, the table is designed as a datetracked (non-"_TL") entity keyed on an effective date range rather than a simple static mapping.

The ETRM metadata classifies this table, heuristically mined from its foreign key structure, as satellite-leaning in Data Vault terms. This classification is a modeling suggestion: the table attaches descriptive and surrogate attribute data to a core relationship (plan-option-program lineage) rather than representing a standalone business hub or a pure many-to-many link. In practice it behaves as an effectiveness/versioning satellite over the plan-in-program and option-in-plan constructs.

Key Information Stored

The table is documented with 45 columns. The primary key is BEN_OIPLIP_F_PK, composed of OIPLIP_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. OIPLIP_ID serves as the surrogate identifier for each option-in-plan-in-program record, while the two date columns implement the datetracking boundaries that let Oracle represent historical, current, and future configurations simultaneously.

The principal foreign keys are OIPL_ID (the option-in-plan reference) and PLIP_ID (the plan-in-program reference), which together anchor the record to its parent constructs in the plan/program hierarchy. BUSINESS_GROUP_ID is a foreign key to HR_ALL_ORGANIZATION_UNITS and scopes each row to the correct business group, enabling multi-organization separation. The table also carries a flexible descriptive flexfield implemented as OPP_ATTRIBUTE_CATEGORY plus thirty segments (OPP_ATTRIBUTE1 through OPP_ATTRIBUTE30). Audit and concurrency columns include LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, and OBJECT_VERSION_NUMBER (used for optimistic locking). Legislative context is captured by LEGISLATION_CODE and LEGISLATION_SUBGROUP, which drive localization and statutory reporting behavior.

Common Use Cases and Queries

Typical usage is configuration and enrollment reporting. Analysts query the table to determine which options are available under a given program as of a specific date, honoring the effective dating. A representative query joining to the parent plan-in-program structure is:

SELECT oiplip_id, oipl_id, plip_id, legislation_code
FROM   ben_oiplip_f
WHERE  business_group_id = :p_bg_id
AND    :p_as_of_date BETWEEN effective_start_date AND effective_end_date;

Because the flexfield columns are free-form, reporting on custom attributes requires selecting the relevant OPP_ATTRIBUTE column together with OPP_ATTRIBUTE_CATEGORY, then joining the category to its flexfield definition. A standard pattern for point-in-time version comparison is to retrieve all rows for a given OIPLIP_ID ordered by EFFECTIVE_START_DATE, which exposes the full history of a configuration change and supports audit and reconciliation of enrollment eligibility decisions.

Related Objects

• HR_ALL_ORGANIZATION_UNITS — referenced by BEN_OIPLIP_F.BUSINESS_GROUP_ID; provides the business group context.
• BEN_OIPLIP_F_PK — the unique index enforcing the composite primary key (OIPLIP_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE).
• Plan-in-program and option-in-plan parent entities (referenced via PLIP_ID and OIPL_ID) — the hierarchical tables that define the plan/program and option/plan relationships this table dates.
• Advanced Benefits setup and enrollment APIs — Oracle's BEN configuration and enrollment interfaces that create, update, and end-date option-in-plan-in-program records.
• Descriptive flexfield definition tables — resolved through OPP_ATTRIBUTE_CATEGORY to render OPP_ATTRIBUTE1–30 in reports.