Results for “ben_rt_trtmt”

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

Overview

APPS.BENBV_VRBL_RT_RL_V is a reporting view delivered in the Oracle E-Business Suite Advanced Benefits (BEN) module. It exposes the definition of variable rate rules, which govern how a rate or compensation amount is adjusted at runtime based on one or more variable factors, a formula, and an activity base rate. The view is a business-group-secured, read-only projection of the base table BEN_VRBL_RT_RL_F and is intended for use by concurrent programs, extracts, Oracle Business Intelligence (OBIEE/OBIA) content, and custom reporting rather than for online transaction processing.

The name encodes its lineage: "VRBL_RT_RL" denotes the Variable Rate Rule entity, and "_V" indicates that the object is a view. The BV prefix in the view name identifies it as an Oracle-supplied business view made available to the APPS schema. Because the view includes an inline security predicate on BUSINESS_GROUP_ID, it is safe for multi-organization and multi-business-group deployments, as each session sees only rows belonging to the business group resolved by the security profile.

Underlying Base Objects

The view is defined over two documented objects: the synonym BEN_VRBL_RT_RL_F, which resolves to the base table holding variable rate rule definitions, and the package HR_BIS, which supplies the decoding and security functions used in the select list and the WHERE clause.

• BEN_VRBL_RT_RL_F — the primary source of all columns. The view aliases this table as VRR and selects its columns without aggregation or joins, so the view is row-for-row equivalent to the filtered base table.
• HR_BIS.BIS_DECODE_LOOKUP — called twice in the select list to translate the coded values RT_TRTMT_CD (lookup type BEN_RT_TRTMT) and DRVBL_FCTR_APLS_FLAG (lookup type YES_NO) into their user-facing meanings.
• HR_BIS.GET_SEC_PROFILE_BG_ID — invoked in the WHERE clause so that BUSINESS_GROUP_ID must equal the business group identifier returned by the security profile, or the predicate falls back to the column itself through NVL when no business group is resolved.

The view is created WITH READ ONLY, meaning Oracle will reject any DML attempted against the view directly. Changes to variable rate rule definitions must therefore be made through the corresponding BEN form or the base table's supported APIs, not through this view.

Key Columns

• VRBL_RT_RL_ID — the surrogate primary key of the variable rate rule, used to join to dependent entities.
• RT_TRTMT_CD — the rate treatment code. The user-facing description is supplied by the adjacent decoded column using lookup BEN_RT_TRTMT, which is directly relevant to searches on "ben_rt_trtmt"; the value indicates how the calculated rate is treated, for example as a flat amount or a percentage.
• DRVBL_FCTR_APLS_FLAG — a Y/N indicator showing whether the derivable factor logic applies to the rule, decoded through lookup YES_NO.
• ORDR_TO_APLY_NUM — the numeric order in which this rule is applied relative to other rules on the same entity.
• FORMULA_ID — identifies the formula used to compute the adjusted rate.
• ACTY_BASE_RT_ID — identifies the activity base rate to which the rule applies.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-effective bounds, essential for point-in-time queries.
• BUSINESS_GROUP_ID — the owning business group, enforcing the security predicate.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE — standard WHO audit columns, useful for incremental and delta extracts.

The select list also contains the literal '_DF:BEN:BEN_VRBL_RT_RL_F:VRR', which signals to the Oracle Forms-based framework that the descriptive flexfield defined on BEN_VRBL_RT_RL_F should be rendered at that position in the record.

Common Use Cases and Queries

The view is typically used to report on rate treatment configuration, to audit rule ordering and formula assignment, and to feed downstream compensation calculations. A basic query listing active variable rate rules decoded for readability is:

• SELECT vrbl_rt_rl_id, rt_trtmt_cd, rt_trtmt_cd AS rt_trtmt_meaning, drvbl_fctr_apls_flag, ordr_to_aply_num, formula_id, acty_base_rt_id FROM apps.benbv_vrbl_rt_rl_v WHERE effective_start_date <= TRUNC(SYSDATE) AND effective_end_date >= TRUNC(SYSDATE);
• To isolate rules by rate treatment, restrict with WHERE rt_trtmt_cd = :p_rt_trtmt_cd after resolving the desired lookup value from BEN_RT_TRTMT.
• For delta extracts, filter on LAST_UPDATE_DATE > :p_last_run_date and order by ORDR_TO_APLY_NUM.

Because HR_BIS.GET_SEC_PROFILE_BG_ID is evaluated at query time, reports run under a restricted security profile automatically return only the rows for the operator's business group, eliminating the need for callers to add their own business group predicate. Joins to BEN_VRBL_RT_RL_F dependents should use VRBL_RT_RL_ID, and any query referencing the descriptive flexfield must read from the base table or the flexfield views, since the view itself exposes only the flexfield placeholder token.