Results for “cpt_attribute_category”

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

Overview

BEN_CMBN_PTIP_OPT_F is a table within the BEN (Advanced Benefits) product schema in Oracle E-Business Suite, documented as valid in both 12.1.1 and 12.2.2. Its business description states that it stores the combination of plan types in programs by option that may provide credits to enrolling participants. In functional terms, the table defines which plan type combinations within a program, and against a specific option, are eligible to generate credits — for example, credits offered when a participant enrolls in a specific combination of plans. This is a configuration and setup object rather than a transactional enrollment record, and it underpins the credit-eligibility rules evaluated during the Benefits enrollment cycle.

The primary key is defined by BEN_CMBN_PTIP_OPT_F_PK over (CMBN_PTIP_OPT_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE), confirming that the table is date-tracked and uses an effective-dated surrogate identifier. The metadata's heuristic Data Vault classification is standalone, meaning no foreign-key relationships were mined from the physical structure. As a modeling suggestion, this object is best treated as a reference or satellite-style entity: it carries descriptive attributes (including a 30-column CPT_ATTRIBUTE1..30 descriptive flexfield) and is versioned by effective dates rather than participating in a rigid hub-and-link network.

Key Information Stored

The documented physical schema contains 45 columns. The most significant are:

• CMBN_PTIP_OPT_ID — the surrogate identifier for the plan-type-in-program-by-option combination; it forms the leading column of the primary key.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-tracked validity window; together with CMBN_PTIP_OPT_ID they constitute the full primary key and the unique business-key candidate (BEN_CMBN_PTIP_OPT_F_PK).
• BUSINESS_GROUP_ID — the enterprise or business group that owns the setup row, used to partition configuration across legal entities or operating units.
• PTIP_ID — references the plan-type-in-program definition, identifying which plan type grouping the credit rule applies to.
• PGM_ID — identifies the benefits program within which the combination is defined.
• OPT_ID — identifies the specific option to which the credit-eligible combination is attached.
• NAME — the user-facing name of the combination record.
• CPT_ATTRIBUTE_CATEGORY and CPT_ATTRIBUTE1 through CPT_ATTRIBUTE30 — a descriptive flexfield used to capture customer-specific attributes on the combination.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the Oracle ADF/Forms framework for concurrent update control.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — the standard EBS "Who" audit columns.

The surrogate key (CMBN_PTIP_OPT_ID plus the two effective dates) uniquely identifies a versioned configuration row, while the combination of PGM_ID, PTIP_ID and OPT_ID functions as the practical business identifier for the rule being defined.

Common Use Cases and Queries

Typical usage involves validating credit eligibility during enrollment, reviewing benefit program configuration, and auditing changes to credit rules. A representative query resolves the effective combination for a given program:

• SELECT cmbn_ptip_opt_id, pgm_id, ptip_id, opt_id, name, effective_start_date, effective_end_date FROM ben_cmbn_ptip_opt_f WHERE pgm_id = :pgm_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
• SELECT * FROM ben_cmbn_ptip_opt_f WHERE opt_id = :opt_id AND business_group_id = :bg_id;
• Audit reporting joins the "Who" columns (LAST_UPDATED_BY, LAST_UPDATE_DATE) to identify configuration changes over time.
• Flexfield reporting evaluates CPT_ATTRIBUTE1..30 for customer-specific credit-eligibility logic.

Because effective dating is mandatory, almost all queries should constrain SYSDATE between effective_start_date and effective_end_date to return the correct configuration version.

Related Objects

Although the mined classification is standalone, functional dependencies are implied by the documented columns, and joins are typically formed on those keys:

• BEN_PTIP_F — plan-type-in-program definitions; join on PTIP_ID.
• BEN_PGM_F — benefit program definitions; join on PGM_ID.
• BEN_OPT_F — benefit option definitions; join on OPT_ID.
• BEN_CMBN_PTIP_OPT_F_PK — the primary key index that enforces uniqueness across CMBN_PTIP_OPT_ID, EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
• Benefits configuration views and the enrollment/credit calculation logic that consume PTIP_ID, PGM_ID and OPT_ID to determine eligible credits.

These relationships should be treated as functional joins rather than enforced foreign keys, consistent with the standalone classification reported in the metadata.