Results for “ben_comptncy_rt_x”

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

Overview

BEN_COMPTNCY_RT_X is a date-effective (incremental) view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It is part of the BEN — Advanced Benefits product family and presents the competency rating rules that govern how participant competencies are evaluated against rating levels within a benefits configuration. The view is defined over the BEN_COMPTNCY_RT_F table and applies a row-level filter based on the effective dating framework, exposing only rows whose effective date range encompasses the current system date.

Because the view returns current, valid data and includes the ROWID pseudocolumn, it serves as a standard integration and reporting surface for external systems, concurrent programs, and OAF-based pages that require accurate, effective-dated competency rating information without needing to replicate the truncation logic themselves. The user-facing search term "cty_attribute_category" corresponds to CTY_ATTRIBUTE_CATEGORY, one of the descriptive flexfield columns surfaced by this view.

Underlying Base Objects

The view is defined over a single documented base object: BEN_COMPTNCY_RT_F (exposed in the view definition through the synonym or table alias CTY). BEN_COMPTNCY_RT_F is the base, non-incremental table storing competency rating rules across the full history of effective dates. The "_F" suffix denotes a table that holds dated records without filtering, while the "_X" suffix on the view indicates an "incremental" or current view that filters those records.

The predicate applied is TRUNC(SYSDATE) BETWEEN CTY.EFFECTIVE_START_DATE AND CTY.EFFECTIVE_END_DATE. This confines the result set to a single logical snapshot of each rule valid as of today. No joins to other tables are performed in the view text, so all columns are carried straight through from the base table, including audit columns and the OBJECT_VERSION_NUMBER used for optimistic locking.

Key Columns

• ROW_ID (ROWID): The physical row identifier of the underlying BEN_COMPTNCY_RT_F record, useful for direct row addressing or updates.
• COMPTNCY_RT_ID: The unique primary identifier for a competency rating rule.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE: The date range governing validity; used by the view's filter.
• ORDR_NUM: Controls the sequence or order in which the rating rule is evaluated.
• EXCLD_FLAG: Indicates whether the competency rating is excluded from the associated processing.
• COMPETENCE_ID: The competency being rated, linked conceptually to the competencies setup.
• RATING_LEVEL_ID: The rating level assigned to the competency.
• VRBL_RT_PRFL_ID: The variable rate profile associated with the competency rating.
• BUSINESS_GROUP_ID: The operating business group (enterprise) that owns the record; critical for multi-tenant (multi-business-group) data isolation.
• CTY_ATTRIBUTE_CATEGORY and CTY_ATTRIBUTE1–30: Descriptive flexfield (DFF) context and attribute segments, allowing customers to capture additional implementation-specific information.
• Audit columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE record who created and last changed the row.

Common Use Cases and Queries

Typical uses include verifying which competency rating rules are active for a business group, extracting DFF context information for downstream reporting, and feeding integrations that must respect effective dating.

• List current competency rating rules for a business group:

  SELECT COMPTNCY_RT_ID, COMPETENCE_ID, RATING_LEVEL_ID, ORDR_NUM
  FROM   BEN_COMPTNCY_RT_X
  WHERE  BUSINESS_GROUP_ID = :p_bg_id
  ORDER  BY ORDR_NUM;

• Interrogate the DFF context and attributes (the object of the "cty_attribute_category" search):

  SELECT COMPTNCY_RT_ID, CTY_ATTRIBUTE_CATEGORY,
         CTY_ATTRIBUTE1, CTY_ATTRIBUTE2
  FROM   BEN_COMPTNCY_RT_X
  WHERE  CTY_ATTRIBUTE_CATEGORY IS NOT NULL
  AND    BUSINESS_GROUP_ID = :p_bg_id;

• Locate rows updated during a period for auditing or replication:

  SELECT COMPTNCY_RT_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY
  FROM   BEN_COMPTNCY_RT_X
  WHERE  LAST_UPDATE_DATE >= :p_from_date;

As with all date-effective "_X" views, queries written against BEN_COMPTNCY_RT_X return only today's valid image; historical or future-dated rule records must be retrieved from BEN_COMPTNCY_RT_F directly.