Search Results pay_formula_result_rules_d




Overview
Explore in AI Chat

Overview

The PAY_FORMULA_RESULT_RULES_D view is a DateTrack history (date-tracked) view owned by the APPS schema in Oracle E-Business Suite Payroll (PAY). It is documented in ETRM as a VALID view whose stated purpose is "Used by DateTrack History." In Oracle EBS, DateTrack is the Oracle HRMS mechanism that maintains multiple effective-dated versions of a row so that changes can be tracked, back-dated, and queried as of a specified effective date. The "_D" suffix follows the established Oracle naming convention for the DateTrack view corresponding to its "_F" (base) counterpart, PAY_FORMULA_RESULT_RULES_F.

Functionally, the view exposes the definition of formula result rules—the configuration records that determine how the outcome of a payroll formula is validated, interpreted, and mapped to a result (for example, an element, input value, or message severity) within a payroll run. Because it joins the effective-dated base table to element type, input value, and lookup translations, the view presents a denormalized, human-readable representation suitable for reporting, data extraction, and integration rather than for transaction entry.

Underlying Base Objects

Per the documented ETRM metadata, PAY_FORMULA_RESULT_RULES_D is defined over the following referenced base objects:

  • PAY_FORMULA_RESULT_RULES_F (SYNONYM) — the effective-dated base table holding the core result-rule records.
  • PAY_ELEMENT_TYPES_F and PAY_ELEMENT_TYPES_F_TL (SYNONYMS) — the element type definition and its translated element names.
  • PAY_INPUT_VALUES_F and PAY_INPUT_VALUES_F_TL (SYNONYMS) — input value definitions and their translated names.
  • HR_LOOKUPS (VIEW) — supplies the meaning for result-rule type, units of measure, and message severity.
  • FND_USER (SYNONYM) — resolves the user who last updated the record.
  • HR_API (PACKAGE) — the HRMS date-tracking API package underlying DateTrack behavior.

The view's SQL performs outer joins ((+)) to the translation tables, element, and input value tables so that rules are not lost when a translation or related definition is absent. The effective-date join condition constrains each joined definition to the same DateTracked interval as the rule: FR.EFFECTIVE_START_DATE BETWEEN NVL(EL.EFFECTIVE_START_DATE,...) AND NVL(EL.EFFECTIVE_END_DATE,...), and similarly for input values. Language filtering is handled via USERENV('LANG').

Key Columns

  • FORMULA_RESULT_RULE_ID — primary identifier of the result rule.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the DateTrack effective interval for the rule; the end date typically carries the high-date sentinel for the current version.
  • RESULT_NAME / NAME — the name of the result produced by the formula.
  • TYPE — the result-rule type, resolved from HR_LOOKUPS where LOOKUP_TYPE = 'RESULT_RULE_TYPE'.
  • ELEMENT — the element type name (ELTL.ELEMENT_NAME) to which the rule applies.
  • INPUT_VALUE — the input value name (IVTL.NAME) associated with the rule.
  • UNITS — unit of measure meaning (UT.MEANING), resolved from lookups of type UNITS.
  • MESSAGE_SEVERITY — severity meaning (SV.MEANING), resolved from lookups of type FORMULA_RESULT_MESSAGE_LEVEL.
  • LAST_UPDATE_DATE / LAST_UPDATED_BY — audit columns; the latter is joined to FND_USER for the update user name.

Common Use Cases and Queries

Typical uses include auditing which result rules apply to a given element or input value, reconciling formula outcomes against configured severities, and extracting configuration for migration or comparison between environments.

  • List all result rules for a specific element as of a date.
  • Report rules by severity level to identify error-raising configurations.
  • Extract the current (high-date) version of each rule for reconciliation.
  • Identify recently changed rules using audit columns.

Example query, filtered to the current effective version:

SELECT FORMULA_RESULT_RULE_ID, RESULT_NAME, TYPE, ELEMENT, INPUT_VALUE, MESSAGE_SEVERITY
FROM APPS.PAY_FORMULA_RESULT_RULES_D
WHERE EFFECTIVE_START_DATE = (SELECT MAX(EFFECTIVE_START_DATE) FROM APPS.PAY_FORMULA_RESULT_RULES_D) ;

Because the view preserves DateTrack history, all queries may be further constrained by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE to retrieve the rule set in force on any given date.