Results for “efp_attribute_category”

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

Overview

The table BEN.BEN_ELIG_FL_TM_PT_TM_PRTE_F is a core configuration table within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, available in both release 12.1.1 and 12.2.2. It stores the eligibility rules that identify full time / part time status for inclusion in, or exclusion from, a compensation object. In practical terms, each row represents a single eligibility criterion entry that determines whether a person's full time or part time employment status qualifies them for a given benefits compensation object, such as a plan, program, or eligibility profile.

The table is a date-tracked (effective-dated) entity. Every logical record is versioned by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, which allows Oracle EBS to preserve historical rule definitions while supporting future-dated changes. The ETRM metadata classifies this object heuristically as standalone under a Data Vault style model — that is, it is not documented as a dependent satellite or link with an enforced parent hub in the mined FK structure. This classification should be treated as a modeling suggestion rather than a definitive design constraint, since eligibility profile tables frequently participate in logical relationships that are not physically enforced through foreign keys.

Key Information Stored

The physical schema documents 47 columns, of which the following are the most operationally significant:

• ELIG_FL_TM_PT_TM_PRTE_ID — the surrogate primary key that uniquely identifies a full time / part time eligibility criterion record. This is the column users typically search for by the name elig_fl_tm_pt_tm_prte_id.
• EFFECTIVE_START_DATE and EFFECTIVE_END_DATE — the date-effective range that, together with the surrogate key, forms the composite primary key BEN_ELIG_FL_TM_PT_TM_PRTE_F_PK.
• ELIGY_PRFL_ID — the eligibility profile to which this criterion belongs, linking the rule to a parent eligibility definition.
• FL_TM_PT_TM_CD — the full time / part time status code being evaluated for inclusion or exclusion.
• EXCLD_FLAG — indicates whether the criterion operates as an inclusion or an exclusion rule.
• BUSINESS_GROUP_ID — the business group (HR security organization) that owns the record.
• ORDR_NUM — the sequencing number controlling the order in which criteria are evaluated.
• CRITERIA_SCORE and CRITERIA_WEIGHT — scoring and weighting values used where eligibility is assessed on a scored rather than binary basis.
• EFP_ATTRIBUTE_CATEGORY and EFP_ATTRIBUTE1 through EFP_ATTRIBUTE30 — the standard Oracle descriptive flexfield columns that carry extensible, customer-defined attributes for the criterion.
• OBJECT_VERSION_NUMBER — the optimistic locking column used by the Oracle Application Object Library (AOL) during concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE — the standard WHO audit columns present on almost all transactional and setup tables.

The primary key is the surrogate ELIG_FL_TM_PT_TM_PRTE_ID combined with the two effective dates. The documented unique index BEN_ELIG_FL_TM_PT_TM_PRTE_F_PK uses exactly these three columns, making them the business-key candidate for the table.

Common Use Cases and Queries

Consultants and developers typically query this table when diagnosing why an employee was included in or excluded from a benefits offering. A representative query retrieves all active criteria for a given eligibility profile:

SELECT elig_fl_tm_pt_tm_prte_id,
       fl_tm_pt_tm_cd,
       excld_flag,
       ordr_num,
       criteria_score,
       criteria_weight
FROM   ben_elig_fl_tm_pt_tm_prte_f
WHERE  eligy_prfl_id = :p_eligy_prfl_id
AND    TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Another common pattern is the date-effective lookup used to find the current version of a specific criterion record by its surrogate key:

SELECT *
FROM   ben_elig_fl_tm_pt_tm_prte_f
WHERE  elig_fl_tm_pt_tm_prte_id = :p_id
AND    TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Reporting use cases include auditing eligibility configuration before an open enrollment cycle, comparing inclusion versus exclusion rules across plans, and validating that full time / part time status codes are consistent with values held in the HR status lookup. Because the table is date tracked, all reports should filter on the effective date range to avoid returning obsolete versions.

Related Objects

• BEN_ELIGY_PRFL_F — the eligibility profile header. Joins on ELIGY_PRFL_ID and is the logical parent of this criterion table.
• BEN_ELIG_FL_TM_PT_TM_PRTE — the corresponding non-effective or alternate table/view variant in the BEN schema used by form and API layers.
• BEN_ELIG_FL_TM_PT_TM_PRTE_F_PK — the primary key constraint on ELIG_FL_TM_PT_TM_PRTE_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, which enforces uniqueness of each criterion version.
• FND_FLEX_VALUES / FND_FLEX_VALUES_TL — the flexfield value tables that resolve the EFP_ATTRIBUTE_CATEGORY and EFP_ATTRIBUTE columns where a descriptive flexfield has been configured.
• HR_LOOKUPS — resolves the FL_TM_PT_TM_CD status code to its display meaning.
• PER_BUSINESS_GROUPS — the parent of BUSINESS_GROUP_ID, establishing which enterprise owns the criterion.
• BEN_ELIG_F / BEN_ELIG_PRFL_F — related eligibility definition tables that may reference this criterion through the shared eligibility profile.
• BEN_ELIG_FL_TM_PT_TM_PRTE_API — the PL/SQL API package that Oracle provides for creating and maintaining rows programmatically rather than through direct DML.

Because the mined Data Vault classification is standalone, direct SQL access is possible, but modifications should always be made through the supported BEN APIs to preserve effective-dating integrity and avoid orphaned versions.