Results for “cmbn_ptip_opt”

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

Overview

APPS.BEN_OPT_D is a reporting and integration view within the Oracle E-Business Suite Advanced Benefits (Oracle Benefits) module. It exposes information about benefit options — the selectable choices available to participants within a benefit program — while resolving two foreign-key relationships that would otherwise require joins at the application or report layer. Specifically, the view presents option definitions from BEN_OPT_F and enriches them with the corresponding combination of plan type, plan, and option (CMBN_PTIP_OPT) name, as well as the user name of the last person to update the row. The view is documented in ETRM for both EBS 12.1.1 and 12.2.2 and is owned by the APPS schema.

The object is commonly referenced during data extraction, interface development, and ad hoc reporting where a denormalized view of benefit option setup data is preferable to the raw _F tables. The search term "cmbn_ptip_opt" corresponds directly to the BEN_CMBN_PTIP_OPT_F source object used in the view text, so users arriving at this view are typically investigating how plan-type/plan/option combinations are named and related.

Underlying Base Objects

The view is defined over three documented synonyms:

• BEN_OPT_F — the primary benefits option flex table, aliased as OPT. This is the driving table in the FROM clause and supplies the option identifier, dates, name, short name, short code, component reason, mapping details, and legislation attributes.
• BEN_CMBN_PTIP_OPT_F — the combination plan type/plan/option flex table, aliased as CMBN_PTIP_OPT. It contributes the descriptive NAME associated with the option's combination context.
• FND_USER — the Oracle Applications user table, aliased as FUSER, used to resolve the LAST_UPDATED_BY numeric user ID to a displayable user identity.

All three joins are outer joins. FND_USER is joined with the (+) operator on USER_ID = OPT.LAST_UPDATED_BY, and BEN_CMBN_PTIP_OPT_F is joined with the (+) operator on CMBN_PTIP_OPT_ID = OPT.CMBN_PTIP_OPT_ID. This ensures option rows are returned even when no matching FND_USER record or combination record exists, which is important because the CMBN_PTIP_OPT_ID on BEN_OPT_F is not guaranteed to be populated for every option definition.

Key Columns

• ROW_ID — the ROWID of the BEN_OPT_F row, exposed for update/integration purposes.
• OPT_ID — the primary key of the benefit option.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-tracked effective range of the option definition.
• NAME — the option name from BEN_OPT_F. Note the view also selects CMBN_PTIP_OPT.NAME without a distinct alias, so consumers should qualify or alias this column carefully to avoid ambiguity.
• LAST_UPDATE_DATE, LAST_UPDATED_BY — audit columns; LAST_UPDATED_BY is the FND_USER lookup key.
• SHORT_NAME, SHORT_CODE — abbreviated identifiers used in setup and enrollment flows.
• COMPONENT_REASON — the reason a component is treated as an option.
• MAPPING_TABLE_NAME, MAPPING_TABLE_PK_ID — identify the flexfield mapping target and primary-key value for the option.
• LEGISLATION_CODE, LEGISLATION_SUBGROUP — regulatory jurisdiction attributes controlling option availability.

Common Use Cases and Queries

Typical applications include extracting option setup for data conversion, validating combination/option relationships, and auditing recent changes by user.

Retrieve all options with their CMBN_PTIP_OPT name and last updater:

SELECT opt_id, name, short_name, legislation_code,
       last_update_date, last_updated_by
FROM   apps.ben_opt_d
WHERE  effective_end_date = TO_DATE('4712-12-31','YYYY-MM-DD');

Find options tied to a specific combination plan type:

SELECT o.opt_id, o.name, o.short_code
FROM   apps.ben_opt_d o
WHERE  o.cmbn_ptip_opt_id = :p_cmbn_ptip_opt_id;

Audit by user, joining FND_USER independently if an explicit user name is required:

SELECT o.opt_id, o.name, u.user_name
FROM   apps.ben_opt_d o, apps.fnd_user u
WHERE  u.user_id = o.last_updated_by;

Because two columns named NAME are projected, queries should reference the alias-qualified column — for example via the base table name — or wrap the view in an inline view with explicit column aliases before consuming it in a report. This is a common source of ORA-00918 errors when the view is queried directly. Consistent use of effective date predicates is also recommended, since BEN_OPT_F is date-tracked and multiple effective versions of the same OPT_ID may be present.