Search Results ben_asnt_set

Overview

BEN.BEN_ELIG_ASNT_SET_PRTE_F is a transaction data table within the Oracle E-Business Suite Benefits (BEN) schema. It functions as an eligibility criteria table that identifies which assignment sets are either included in or excluded from a given eligibility profile. Assignment sets, defined in Oracle Human Resources through the PER_ASSIGNMENT_SETS entity, group workers according to shared assignment attributes. When attached to an eligibility profile, they allow a compensation or benefits object—such as a program, plan, or option—to restrict or grant participation based on assignment-set membership.

The table is a date-tracked (effective-dated) intersection entity. It resolves the many-to-many relationship between BEN_ELIGY_PRFL_F and BEN_ASNT_SET, carrying its own surrogate primary key and a pair of effective dates that define row validity over time. From a Data Vault modeling perspective, this object is best understood as a link table connecting an eligibility profile hub to an assignment set hub, with the effective-dated attributes (such as the exclude flag and scoring columns) behaving as link-satellite content. This classification is a heuristic derived from the foreign key structure and should be treated as a modeling suggestion rather than a declared ETRM attribute.

Key Information Stored

The physical schema in 12.2.2 documents 47 columns. The most significant are:

• ELIG_ASNT_SET_PRTE_ID — system-generated surrogate primary key (NUMBER 15).
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the effective-dating pair; together with the ID these form the unique primary key BEN_ELIG_ASNT_SET_PRTE_F_PK.
• ASSIGNMENT_SET_ID — foreign key to PER_ASSIGNMENT_SETS; identifies the assignment set being included or excluded. Indexed by BEN_ELIG_ASNT_SET_PRTE_F_FK1.
• ELIGY_PRFL_ID — foreign key to BEN_ELIGY_PRFL_F; the parent eligibility profile. Indexed by BEN_ELIG_ASNT_SET_PRTE_F_N1.
• EXCLD_FLAG — VARCHAR2(30) indicating whether the assignment set is excluded (Y) or included (N).
• BUSINESS_GROUP_ID — foreign key to HR_ORGANIZATION_UNITS, scoping the row to a business group. Indexed by BEN_ELIG_ASNT_SET_PRTE_F_FK3.
• ORDR_NUM, CRITERIA_SCORE, CRITERIA_WEIGHT — sequencing and criteria weighting used when multiple eligibility criteria are evaluated for a profile.
• EAN_ATTRIBUTE_CATEGORY and EAN_ATTRIBUTE1–30 — descriptive flexfield structure and segment columns for customer-defined extensions.
• OBJECT_VERSION_NUMBER — used for optimistic locking by the OAF/BC4J framework.
• WHO audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) — standard EBS audit tracking.

The unique index (ELIG_ASNT_SET_PRTE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) is the business-key candidate; ASSIGNMENT_SET_ID and ELIGY_PRFL_ID are non-unique foreign keys rather than business keys.

Common Use Cases and Queries

The primary reporting scenario is determining which assignment sets drive eligibility for a given profile and whether each acts as an inclusion or exclusion. A typical query joins this table to the parent profile and to the HR assignment set definition:

• List all assignment-set criteria for a profile: SELECT ASSIGNMENT_SET_ID, EXCLD_FLAG FROM BEN_ELIG_ASNT_SET_PRTE_F WHERE ELIGY_PRFL_ID = :p AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE.
• Find profiles excluding a specific assignment set: filter by ASSIGNMENT_SET_ID and EXCLD_FLAG = 'Y'.
• Point-in-time audit: query using the effective dates to reconstruct the eligibility configuration as of a past date, important for benefits open-enrollment reconciliation.
• Joining to PER_ASSIGNMENT_SETS to resolve the human-readable set name and to BEN_ELIGY_PRFL to obtain the profile name for end-user reports.

Because the table is effective-dated and versioned, reports should always constrain the effective dates to avoid returning duplicate historical rows.

Related Objects

• BEN_ELIGY_PRFL_F — parent eligibility profile; joined on ELIGY_PRFL_ID.
• PER_ASSIGNMENT_SETS — HR assignment set definition; joined on ASSIGNMENT_SET_ID.
• BEN_ASNT_SET — the Benefits assignment set entity referenced in the intersection definition.
• HR_ORGANIZATION_UNITS — business group; joined on BUSINESS_GROUP_ID.
• BEN_ELIG_ASNT_SET_PRTE_F_PK / _FK1 / _FK3 / _N1 — the unique and non-unique indexes enforcing integrity and supporting queries.

Application logic that populates this table is typically driven through the Benefits eligibility profile setup UI and the associated eligibility criteria APIs, which insert effective-dated rows as profiles are configured.