Results for “ben_cmbn_plip_d”

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

Overview

BEN_CMBN_PLIP_D is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the BEN (Advanced Benefits) product family. The view name follows the Oracle EBS convention in which the trailing "_D" designates a denormalized descriptive (translation-free) view over a "_F" base table. BEN_CMBN_PLIP_D resolves each row of BEN_CMBN_PLIP_F, the combined plan-in-program entity, into a flattened presentation that includes the derived program name and audit-user information. Its object type is VIEW and its status is VALID in both 12.1.1 and 12.2.2.

The view is primarily referenced in ETRM and data-model documentation for its surrogate primary key column, CMBN_PLIP_ID, which corresponds to the value returned by the CMBN_PLIP_ID sequence used to populate BEN_CMBN_PLIP_F. Because it joins directly to FND_USER and DEN_PGM_F, it removes the need for report developers and integration interfaces to perform separate lookups for the last-updated user name and the parent program name.

Underlying Base Objects

The view text documented in the ETRM metadata identifies three referenced base objects, each accessed through an APPS synonym:

• BEN_CMBN_PLIP_F — the primary combined plan-in-program base table aliased CPL. All row instances of the view originate from this table; its ROWID is exposed as ROW_ID.
• BEN_PGM_F — the program base table aliased PGM. It is outer-joined on PGM_ID = CPL.PGM_ID, so a combined plan-in-program row is retained even when its parent program record is missing or future-dated.
• FND_USER — the Oracle Applications user table aliased FUSER, outer-joined on USER_ID = CPL.LAST_UPDATED_BY to resolve the numeric user reference to an application user.

The outer join syntax (+)= confirms that neither the program lookup nor the user lookup filters the driving BEN_CMBN_PLIP_F result set, which makes the view safe for full-population extracts and incremental reconciliation against the base table.

Key Columns

Eight columns are projected by the view:

• ROW_ID — the ROWID of the underlying BEN_CMBN_PLIP_F row; useful for direct row-level updates against the base table.
• CMBN_PLIP_ID — the surrogate primary key of the combined plan-in-program entity. This is the column most frequently searched by implementers (the "cmbn_plip_id" query term) when tracing a plan-in-program reference from a report, interface table, or Oracle Advanced Benefits setup page.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the datetracked date range inherited from the _F table. These columns must be filtered against the run date to obtain a single correct row per combination.
• NAME — the descriptive name of the combined plan-in-program record; ambiguous against the PGM_NAME alias in raw DDL references.
• PGM_NAME — the name of the parent program from BEN_PGM_F, aliased to distinguish it from the local NAME column.
• LAST_UPDATE_DATE — the audit timestamp of the last modification to the base row.
• LAST_UPDATED_BY — the numeric user ID from the base table; this is the column joined to FND_USER, not a user name.

Common Use Cases and Queries

The view supports benefit plan-in-program reporting, configuration comparison between instances, and diagnostic lookups when an uncrossed reference to CMBN_PLIP_ID appears in an interface or staging table.

Resolve a specific plan-in-program record and its owning program:

SELECT cmbn_plip_id, name, pgm_name, effective_start_date, effective_end_date
FROM apps.ben_cmbn_plip_d
WHERE cmbn_plip_id = :p_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date;

Audit recent maintenance activity, resolving the user reference:

SELECT cmbn_plip_id, name, pgm_name, last_update_date,
(SELECT user_name FROM fnd_user u WHERE u.user_id = d.last_updated_by) updated_by
FROM apps.ben_cmbn_plip_d d
WHERE last_update_date >= TRUNC(SYSDATE) - 30;

Because the view is a simple join over three base objects and carries no product-specific security filters, data access must be restricted through the standard BEN responsibility menus or via custom views layered on top. Reports and concurrent programs that require only program names but not user resolution should query BEN_CMBN_PLIP_F directly to avoid the outer join overhead on large extracts.