Results for “ben_acty_rt_ded_sched_f_pk”

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

Overview

BEN_ACTY_RT_DED_SCHED_F is a date-tracked (effective-dated) table in the BEN schema of Oracle Advanced Benefits (Oracle EBS 12.1.1 and 12.2.2). It stores the deduction schedule associated with an activity rate, allowing a benefit plan configuration to define how and when a calculated activity rate is deducted from a participant. Activity rates in Advanced Benefits support rate structures driven by user-defined calculation logic; the deduction schedule attached through this table determines the payroll frequency and timing relationship applied to those derived amounts.

Because the table carries EFFECTIVE_START_DATE and EFFECTIVE_END_DATE as part of its primary key, it is a date-effective (datetrack) entity, consistent with the corporate datetrack pattern used throughout the BEN schema. Under the heuristic Data Vault classification derived from the foreign key structure, this object is satellite-leaning: it holds descriptive, time-variant attributes describing the relationship between an activity rate and a deduction schedule, rather than acting as a pure hub or link. This classification should be treated as a modeling suggestion, not an authoritative DBA designation.

Key Information Stored

The table is documented with 45 columns. The most significant are the following.

• ACTY_RT_DED_SCHED_ID — Surrogate identifier for the deduction schedule row. It is the leading column of the primary key and, in practice, the value assigned from the BEN_ACTY_RT_DED_SCHED_F_S sequence.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Datetrack boundaries. Together with the surrogate ID they form the composite primary key BEN_ACTY_RT_DED_SCHED_F_PK, which is also the documented business-key candidate unique index. Any point-in-time query must constrain on these dates.
• ACTY_BASE_RT_ID — Foreign reference to the parent activity base rate to which this deduction schedule applies. This is the principal linkage back to the rate configuration.
• DED_SCHED_PY_FREQ_ID — The documented foreign key to BEN_DED_SCHED_PY_FREQ, identifying the payroll frequency definition used when the deduction is processed.
• DED_SCHED_CD / DED_SCHED_RL — The deduction schedule code and the schedule rule that govern the deduction cadence.
• BUSINESS_GROUP_ID — Multi-tenant discriminator identifying the legislative business group owning the row.
• ADS_ATTRIBUTE_CATEGORY and ADS_ATTRIBUTE1 through ADS_ATTRIBUTE30 — The standard Oracle descriptive flexfield (DFF) segment set, enabling customer-specific extensions without schema change. ATTRIBUTE_CATEGORY selects the context; the numbered segments carry the user-defined values.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER — Standard WHO audit columns; OBJECT_VERSION_NUMBER provides optimistic locking for concurrent updates.

Common Use Cases and Queries

Typical usage centers on point-in-time reporting of deduction schedule assignments for activity rates, and on integration extracts feeding payroll or benefits administration. A common pattern resolves the effective row for a given surrogate identifier on a specific date:

• Point-in-time lookup: SELECT * FROM ben.ben_acty_rt_ded_sched_f WHERE acty_rt_ded_sched_id = :p_id AND TRUNC(:p_date) BETWEEN effective_start_date AND effective_end_date;
• Current configuration by activity rate: join ACTY_BASE_RT_ID to the activity base rate table and filter SYSDATE BETWEEN effective_start_date AND effective_end_date, also scoping by BUSINESS_GROUP_ID.
• Frequency audit: join DED_SCHED_PY_FREQ_ID to BEN_DED_SCHED_PY_FREQ to report the payroll frequency driving each activity rate deduction.
• DFF reporting: pivot ADS_ATTRIBUTE1–ADS_ATTRIBUTE30 by ADS_ATTRIBUTE_CATEGORY for customers who have configured the flexfield.
• Change tracking: order by EFFECTIVE_START_DATE to reconstruct the history of schedule assignments, or compare OBJECT_VERSION_NUMBER across extracts to detect concurrent modification.

Related Objects

• BEN_DED_SCHED_PY_FREQ — Referenced through BEN_ACTY_RT_DED_SCHED_F.DED_SCHED_PY_FREQ_ID; supplies the deduction schedule payroll frequency.
• Activity base rate table (BEN_ACTY_BASE_RT_F and its related effective-dated tables) — Parent of ACTY_BASE_RT_ID; defines the rate to which the deduction schedule is attached.
• BEN_ACTY_RT_DED_SCHED_F_PK / its backing unique index — The composite unique index over ACTY_RT_DED_SCHED_ID, EFFECTIVE_START_DATE and EFFECTIVE_END_DATE that enforces datetrack integrity.
• BEN_ACTY_RT_DED_SCHED_F_S — The sequence conventionally used to populate ACTY_RT_DED_SCHED_ID.
• BEN_DED_SCHED_RULE_F and deduction schedule setup tables — Related configuration objects that define DED_SCHED_CD and DED_SCHED_RL semantics.
• PER_BUSINESS_GROUPS — Referenced through BUSINESS_GROUP_ID for business group scoping.
• FND_DF_FLEXFIELDS / FND_FLEX_VALUES — Metadata backing the ADS_ATTRIBUTE descriptive flexfield segments.
• FND_USER — Referenced through CREATED_BY and LAST_UPDATED_BY for audit reporting.