Search Results assignment_set




Overview
Explore in AI Chat

Overview

APPS.BEN_ELIG_ASNT_SET_PRTE_D is a denormalized reporting view within the Oracle EBS Advanced Benefits (BEN) module. It exposes the eligibility criteria rows that link a participant eligibility profile to an assignment set, resolving the underlying foreign keys into human-readable meaning. Specifically, the view returns the assignment set participation criterion records stored in BEN_ELIG_ASNT_SET_PRTE_F, decorated with the assignment set name, the eligibility profile name, the exclusion lookup meaning, and audit user information. The "_D" suffix conventionally denotes a "descriptive" or denormalized view intended for inquiry, reporting, and integration rather than for transactional DML. In Release 12.1.1 and 12.2.2 the object is owned by APPS and is exposed as a synonym within the E-Business Suite schema; it is typically consumed by concurrent programs, Oracle Reports, OBIEE/BIP extracts, and custom PL/SQL that needs to report which assignment sets are included in or excluded from an eligibility profile.

The view is a critical bridge between the eligibility engine and the assignment set population mechanism: eligibility profiles determine whether a person qualifies for a program or plan, and assignment sets determine which population a program or plan is offered to. This view surfaces the intersection of those two concepts.

Underlying Base Objects

The view is defined over the following documented base objects, all referenced through APPS synonyms unless noted:

  • BEN_ELIG_ASNT_SET_PRTE_F — the driving table (aliased EAN), holding the assignment set participation criterion rows, effective dating, order number, criteria score/weight, and the foreign keys ASSIGNMENT_SET_ID, ELIGY_PRFL_ID, EXCLD_FLAG, and LAST_UPDATED_BY.
  • BEN_ELIGY_PRFL_F — the eligibility profile header (aliased ELIGY_PRFL), joined on ELIGY_PRFL_ID to supply the profile NAME. The join includes a date-range predicate requiring the criterion's EFFECTIVE_START_DATE to fall between the profile's effective start and end dates.
  • HR_ASSIGNMENT_SETS — the assignment set definition (aliased ASSIGNMENT_SET), joined on ASSIGNMENT_SET_ID to supply ASSIGNMENT_SET_NAME.
  • HR_LOOKUPS — a lookup view (aliased EXCLD) filtered with LOOKUP_TYPE = 'YES_NO', joined on EXCLD_FLAG to resolve the exclusion indicator into its MEANING (e.g., Yes/No).
  • FND_USER — joined on LAST_UPDATED_BY to attribute the last change to a named application user.
  • HR_API — listed as a referenced package dependency, reflecting the HR security/utility layer used across HRMS-based objects.

All joins except the driving table are outer joins (denoted by the (+) operator), so criterion rows are returned even when the profile, assignment set, lookup, or user reference is null.

Key Columns

  • ROW_ID — the ROWID of the underlying BEN_ELIG_ASNT_SET_PRTE_F row, useful as a unique identifier.
  • ELIG_ASNT_SET_PRTE_ID — primary key of the participation criterion record.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-tracked validity window of the criterion.
  • ORDR_NUM — sequencing of the criterion within the profile.
  • EXCLD.MEANING — resolved YES/NO meaning of the EXCLD_FLAG, indicating whether the assignment set is included in or excluded from eligibility.
  • ASSIGNMENT_SET_NAME — descriptive name of the assignment set associated with the criterion, the field most commonly searched ("assignment_set").
  • ELIGY_PRFL.NAME — name of the eligibility profile the criterion belongs to.
  • CRITERIA_SCORE / CRITERIA_WEIGHT — scoring and weighting values used when multiple criteria combine to determine eligibility.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY — audit columns identifying when and by whom the record was last modified.

Common Use Cases and Queries

Typical uses include auditing which eligibility profiles reference a given assignment set, and exporting the population definitions attached to a benefit program for reporting. For example, to list assignment sets per profile:

SELECT eligy_prfl_name, assignment_set_name, excl_excluded
FROM   apps.ben_elig_asnt_set_prte_d
WHERE  assignment_set_name LIKE :assignment_set
AND    SYSDATE BETWEEN effective_start_date AND effective_end_date
ORDER BY eligy_prfl_name, ordr_num;

To identify exclusions only, add WHERE excl_excluded = 'Yes'. To trace the most recently changed criteria, order by LAST_UPDATE_DATE DESC. Because the view is not date-tracked as a WHOLE, always constrain EFFECTIVE_START_DATE and EFFECTIVE_END_DATE to the reporting date to avoid returning historical versions.