Results for “loa_rsn_rt_id”

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

Overview

BEN_LOA_RSN_RT_D is a reporting view owned by the APPS schema within the Advanced Benefits (BEN) product family of Oracle E-Business Suite, documented as retrofitted in ETRM 12.2.2. The view presents a denormalized, human-readable projection of the length-of-absence (LOA) reason rate configuration held in BEN_LOA_RSN_RT_F. Its purpose is to translate the numeric and coded identifiers stored in the base table into descriptive text: the absence reason, the variable rate profile name, the exclusion indicator, and the identity of the user who last maintained the row.

Because it resolves lookup codes and joins user information in a single object, the view serves as a convenient read-only source for reporting, extracts, and integrations that must display LOA reason rate data without embedding lookup joins and outer-join logic in every consumer query. It is not a maintenance entity; rows are queried, not inserted, updated, or deleted through the view.

Underlying Base Objects

Per the documented metadata, the view is constructed over the following base objects: BEN_LOA_RSN_RT_F, BEN_VRBL_RT_PRFL_F, FND_USER, HR_LOOKUPS, and the HR_API package. BEN_LOA_RSN_RT_F is the primary driver table and carries the LOA reason rate identifier, effective dating, order number, the absence reason code (LOA_RSN_CD), the variable rate profile reference (VRBL_RT_PRFL_ID), the exclusion flag (EXCLD_FLAG), and audit columns. BEN_VRBL_RT_PRFL_F supplies the variable rate profile name, joined on VRBL_RT_PRFL_ID and constrained by the effective dates of the profile row to the effective start date of the rate record. FND_USER supplies the user name for the last updated by column. HR_LOOKUPS is referenced twice: once against LOA_RSN_CD with LOOKUP_TYPE = 'ABSENCE_REASON' to resolve the reason description, and once against EXCLD_FLAG with LOOKUP_TYPE = 'YES_NO' to resolve the exclusion meaning.

All joins to FND_USER, BEN_VRBL_RT_PRFL_F, and both HR_LOOKUPS aliases are outer joins, so a rate row is returned even when the profile, user, or lookup values are missing. The reference to HR_API indicates the view participates in the standard HR/BEN security and date-tracked API conventions, though HR_API is not projected in the view's column list.

Key Columns

• ROW_ID — the ROWID of the underlying BEN_LOA_RSN_RT_F row, useful for uniquely identifying the source record.
• LOA_RSN_RT_ID — the primary surrogate identifier of the LOA reason rate record.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-tracked validity window of the rate row.
• ORDR_NUM — the display or evaluation sequence of the rate entry.
• LOA_RSN_MEANING — the descriptive text of the absence reason, resolved from HR_LOOKUPS via LOOKUP_TYPE 'ABSENCE_REASON'. This is the column most relevant to searches on absence_reason.
• VRBL_RT_PRFL_NAME — the name of the associated variable rate profile.
• EXCLD_MEANING — the Yes/No meaning of the exclusion flag, resolved from HR_LOOKUPS via LOOKUP_TYPE 'YES_NO'.
• LAST_UPDATE_DATE — the audit timestamp of the most recent change.
• LAST_UPDATED_BY — the FND_USER user name of the last updater.

Common Use Cases and Queries

Typical uses include auditing absence reason rate configuration, validating that each reason maps to a valid ABSENCE_REASON lookup, and driving extracts that feed downstream absence or accrual processing. A representative query retrieving current rates by absence reason is:

SELECT loa_rsn_rt_id, loa_rsn_meaning, vrbl_rt_prfl_name, ordr_num, excld_meaning, last_update_date, last_updated_by FROM apps.ben_loa_rsn_rt_d WHERE loa_rsn_meaning = :absence_reason AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date ORDER BY ordr_num;

To list all distinct absence reasons configured with rate detail:

SELECT DISTINCT loa_rsn_meaning FROM apps.ben_loa_rsn_rt_d WHERE loa_rsn_meaning IS NOT NULL ORDER BY 1;

To inspect exclusions for a given reason:

SELECT loa_rsn_meaning, excld_meaning, COUNT(*) FROM apps.ben_loa_rsn_rt_d GROUP BY loa_rsn_meaning, excld_meaning;

Because the joins to the lookup tables are outer joins, rows with an unresolved absence reason yield a NULL LOA_RSN_MEANING; queries intended to validate configuration should therefore include an explicit check for NULL values.