Search Results assignment_set

Overview

BEN_ASNT_SET_RT_D is a read-only view in the APPS schema that presents assignment set routing configuration within the Oracle Advanced Benefits (BEN) module. It exposes the intersection between assignment sets and variable rate profiles, providing a denormalized, reporting-friendly representation of how benefit variable rate profiles are sequenced and selectively applied to specific populations of employees. In Oracle EBS 12.1.1 and 12.2.2, this view is typically consumed by configuration reports, data extracts, and integration scripts that need to enumerate the routing rules governing Advanced Benefits processing rather than querying the underlying _F tables directly.

The view is defined over four base objects and performs outer and inner joins to resolve surrogate identifiers into human-readable names. Its status is VALID, and it is owned by APPS, meaning it is accessible to any responsibility or concurrent program with the appropriate grants.

Underlying Base Objects

The view is constructed from the following documented base objects, all referenced through APPS synonyms:

• BEN_ASNT_SET_RT_F — the primary driving table (aliased ASR), holding assignment set route definitions, effective dates, order numbers, and exclusion flags.
• BEN_VRBL_RT_PRFL_F — the variable rate profile table (aliased VPR), joined on VRBL_RT_PRFL_ID to resolve the profile name.
• HR_ASSIGNMENT_SETS — the assignment set definition table (aliased HSA), joined on ASSIGNMENT_SET_ID to resolve the assignment set name.
• FND_USER — the application user table (aliased FUSER), joined via LAST_UPDATED_BY to identify who last modified the record.

The joins are largely inner joins, except FND_USER, which is outer-joined using (+) on USER_ID. This means a route row will still be returned even if the updating user record cannot be resolved.

Key Columns

The view exposes the following columns, drawn directly from the SELECT list or documented column metadata:

• ASNT_SET_RT_ID — primary key of the assignment set route record.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-effective range governing when the route is active.
• VRBL_RT_PRFL_ID — the variable rate profile identifier referenced by the route.
• NAME — the variable rate profile name resolved from BEN_VRBL_RT_PRFL_F.
• ASSIGNMENT_SET_NAME — the descriptive name of the assignment set from HR_ASSIGNMENT_SETS.
• ORDR_NUM — the sequence in which the route is evaluated relative to other routes.
• EXCLD_FLAG — indicates whether the assignment set is included or excluded by the route.
• LAST_UPDATE_DATE / LAST_UPDATED_BY — audit columns recording the last modification.
• USER_NAME — the application user name resolved from FND_USER.

Common Use Cases and Queries

Typical usage includes auditing which variable rate profiles apply to which assignment sets, verifying ordering and exclusion logic, and exporting configuration for migration or documentation. A basic listing of active routes is shown below:

• SELECT asnt_set_rt_id, name, assignment_set_name, ordr_num, excld_flag, effective_start_date, effective_end_date FROM ben_asnt_set_rt_d WHERE SYSDATE BETWEEN effective_start_date AND effective_end_date ORDER BY assignment_set_name, ordr_num;
• SELECT name, assignment_set_name, ordr_num, user_name, last_update_date FROM ben_asnt_set_rt_d WHERE excld_flag = 'Y';
• SELECT assignment_set_name, COUNT(*) FROM ben_asnt_set_rt_d GROUP BY assignment_set_name;

Because the view joins only four objects, it is efficient for ad-hoc queries, but it should be filtered by effective dates where current configuration is required, since historical and future-dated route versions are also returned. Users searching for "assignment_set" will find this view useful for reviewing how assignment sets participate in Advanced Benefits variable rate routing.