Results for “ben_cmbn_ptip_opt_v”

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

Overview

BEN_CMBN_PTIP_OPT_V is a secure, effective-date-aware view owned by the APPS schema within the Oracle E-Business Suite Advanced Benefits (BEN) module. It exposes combination plan type option data — the intersection of a plan type (PTIP) with a specific option (OPT) — and is primarily used to surface descriptive flexfield (DFF) attribute values and other configuration metadata associated with those combinations. The view is documented as "Retrofitted," indicating it was converted into the modern, user-environment-driven security model that governs most R12 HRMS/BEN objects, replacing an older, less secure formulation.

Importantly, the view filters rows through FND_SESSIONS using the effective date associated with the current user's session (USERENV('SESSIONID')). This ensures that queries executed against BEN_CMBN_PTIP_OPT_V automatically return only the rows valid as of the effective date established for the runtime session, avoiding the need for callers to supply their own date bounds.

Underlying Base Objects

The view is defined over two objects:

• BEN_CMBN_PTIP_OPT_F — the core combination plan type/option entity table. An inner join is performed against this table (aliased CPT) to obtain the option definition, its effective dating, and its 30 descriptive flexfield attribute columns plus the attribute category.
• FND_SESSIONS — the Applications session table (aliased SE). The join predicate SE.SESSION_ID = USERENV('SESSIONID') combined with SE.EFFECTIVE_DATE BETWEEN CPT.EFFECTIVE_START_DATE AND CPT.EFFECTIVE_END_DATE restricts output to the currently valid date-tracked row.

The view is a straight projection of the base table's columns, with the addition of a derived ROW_ID obtained via CPT.ROWID, which enables row-level addressing for downstream processing.

Key Columns

• ROW_ID — the physical rowid of the base row; useful for uniquely identifying a record in update or drill-down logic.
• CMBN_PTIP_OPT_ID — primary key of the combination plan type option.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-tracked validity range of the row.
• PTIP_ID, PGM_ID, OPT_ID — foreign keys to the plan type, program, and option definitions.
• NAME, BUSINESS_GROUP_ID — descriptive identifier and the business group owning the row.
• CPT_ATTRIBUTE_CATEGORY — the DFF context (structure) that determines which attribute segments are meaningful.
• CPT_ATTRIBUTE1 … CPT_ATTRIBUTE30 — the 30 descriptive flexfield segments. This is the set that includes the column cpt_attribute1, one of the most frequently referenced segments for customer-defined context-sensitive data. Values here are populated only when the matching segment is enabled in the flexfield definition.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER — standard who-columns and the OVN used for optimistic locking.

Common Use Cases and Queries

The view is typically queried for reporting, extracts, and integration where combination plan type option flexfield values must be joined to enrollment or configuration data. Because of the session-based effective-date filter, the caller must be running in a session whose effective date is set, otherwise no rows are returned.

A basic lookup of the first attribute for a combination:

• SELECT CMBN_PTIP_OPT_ID, PTIP_ID, OPT_ID, CPT_ATTRIBUTE_CATEGORY, CPT_ATTRIBUTE1 FROM APPS.BEN_CMBN_PTIP_OPT_V WHERE PGM_ID = :pgm_id;

Filtering on a specific flexfield value across the attribute set:

• SELECT CMBN_PTIP_OPT_ID, NAME, CPT_ATTRIBUTE1 FROM APPS.BEN_CMBN_PTIP_OPT_V WHERE CPT_ATTRIBUTE_CATEGORY = :category AND CPT_ATTRIBUTE1 = :value;

Joining to flexfield value descriptions to translate an attribute into its meaning:

• SELECT v.CMBN_PTIP_OPT_ID, v.CPT_ATTRIBUTE1, t.DESCRIPTION FROM APPS.BEN_CMBN_PTIP_OPT_V v, FND_FLEX_VALUES_TL t WHERE t.FLEX_VALUE = v.CPT_ATTRIBUTE1 AND t.LANGUAGE = USERENV('LANG');

Typical scenarios include benefits extract generation, custom eligibility reporting, and troubleshooting when cpt_attribute1 or another segment does not appear as expected — in such cases the value of CPT_ATTRIBUTE_CATEGORY should be validated, since flexfield segments are only populated for the enabled category.