Results for “ben_pstn_rt_f”

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

Overview

BEN_PSTN_RT_F is a table within the BEN schema (Oracle Advanced Benefits) that stores the rates applicable to a position. In Oracle EBS 12.1.1 and 12.2.2, this object belongs to the Benefits module and supports the configuration of position-based rate definitions used by the benefits engine when calculating eligibility and cost for compensation objects linked to a specific position.

The table is date-tracked, carrying EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns as part of its primary key. This datetracked design allows multiple historical and future versions of a rate definition to coexist for the same position, enabling point-in-time reconstruction of benefit rates. The documented physical schema lists 45 columns, including a large block of descriptive flexfield (DFF) attribute columns.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign key structure is standalone. This suggests the table functions largely as a hub-like or independent entity rather than a link or satellite, meaning it does not exhibit the dense FK relationships typical of junction or child tables. Its relationships are anchored primarily through its own business key (PSTN_RT_ID combined with the effective dates) rather than through a network of dependent foreign keys.

Key Information Stored

The following columns represent the most significant data elements documented in the ETRM schema:

• PSTN_RT_ID — the surrogate primary key identifying a specific position rate record. It forms the first column of the composite unique index BEN_PSTN_RT_F_PK (PSTN_RT_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE).
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the datetrack columns. Together with PSTN_RT_ID they constitute the business-key candidate defined by the unique index.
• BUSINESS_GROUP_ID — the operating business group (HR security group) that owns the record.
• VRBL_RT_PRFL_ID — reference to the variable rate profile that supplies the rate values applied to the position.
• POSITION_ID — the position to which the rate applies; the central business reference of the table.
• EXCLD_FLAG — indicates whether the position is excluded from the associated rate profile.
• ORDR_NUM — ordering sequence used when multiple rate entries are resolved for a position.
• PST_ATTRIBUTE_CATEGORY and PST_ATTRIBUTE1–PST_ATTRIBUTE30 — a descriptive flexfield structure carrying client-specific extensibility attributes attached to the position rate.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns tracking creation and modification.
• OBJECT_VERSION_NUMBER — the optimistic locking column used by the OAF/BC4J framework to detect concurrent updates.

Common Use Cases and Queries

The primary use case is resolving which variable rate profile applies to a given position at a point in time. Typical queries constrain on position, business group, and the effective date range. The following pattern returns the currently effective rate definition for a position:

• SELECT pstn_rt_id, position_id, vrbl_rt_prfl_id, ordr_num, exclud_fl_flag FROM ben.ben_pstn_rt_f WHERE position_id = :position_id AND business_group_id = :bg_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Reporting scenarios include auditing rate profile assignments across positions, identifying positions explicitly excluded from a rate profile (EXCLD_FLAG = 'Y'), and reconciling historical rate changes using the datetrack columns for date-effective reporting. Integration scenarios use PSTN_RT_ID as the correlation key when loading or extracting position rate data during benefits configuration migration. Because the table is datetracked, any query intended for point-in-time analysis should always supply or default the effective dates rather than relying on a single-row lookup.

Related Objects

The ETRM relationship data classifies the object as standalone, so few hard foreign keys are documented. The following are the most significant related objects, inferred from the column semantics:

• HR_ALL_POSITIONS_F / PER_ALL_POSITIONS — joined via POSITION_ID to resolve the position name and attributes.
• BEN_VRBL_RT_PRFL_F — joined via VRBL_RT_PRFL_ID to obtain the variable rate profile definition that supplies the applicable rates.
• FND_FLEX_VALUES / FND_DESCR_FLEX_COL_USAGE — describe the PST_ATTRIBUTE_CATEGORY and PST_ATTRIBUTE1–30 flexfield structure.
• HR_OPERATING_UNITS / PER_BUSINESS_GROUPS — resolve BUSINESS_GROUP_ID to the owning organization.
• FND_OBJECTS / BEN_PSTN_RT — where a base table or view of the same business entity exists, it shares the PSTN_RT_ID key.

Because FK enforcement is not documented, joins should be validated against HR MS and Benefits setup data rather than assumed referential integrity.