Search Results pay_dimension_routes




Overview
Explore in AI Chat

Overview

The PAY_DIMENSION_ROUTES table is a core technical repository within the Oracle E-Business Suite Payroll module (PAY). It serves as a configuration table that defines the hierarchical relationships between different balance dimensions. Its primary role is to govern how payroll balances are calculated and aggregated by establishing the permissible routes or paths from one dimension to another. This routing mechanism is fundamental to the payroll engine's ability to correctly accumulate and report on earnings, deductions, and other balance types across different timeframes and contexts, such as from an assignment-level dimension to a quarterly dimension.

Key Information Stored

The table's structure is designed to map a source dimension to a target dimension via a specific processing route. The primary key, a composite of BALANCE_DIMENSION_ID and PRIORITY, ensures a defined order of evaluation for multiple possible routes from a single source. Key columns include BALANCE_DIMENSION_ID, which identifies the starting or source balance dimension; RUN_DIMENSION_ID, which identifies the target or destination balance dimension for the route; ROUTE_ID, which links to the processing logic in the FF_ROUTES table that defines how to traverse between the dimensions; and PRIORITY, which dictates the sequence in which multiple potential routes from the same source are evaluated by the payroll calculation engine.

Common Use Cases and Queries

The primary use case is supporting the complex balance calculation process during payroll runs. When the engine needs to locate a value for a specific balance dimension, it queries this table to find the appropriate route to a dimension where data is stored. This is critical for balance initialization and aggregation. Common queries involve identifying all available routes from a given dimension or debugging balance calculation issues by examining the defined dimension hierarchy. A sample diagnostic query might be:

  • SELECT pbd1.DIMENSION_NAME AS SOURCE_DIM, pbd2.DIMENSION_NAME AS TARGET_DIM, pdr.PRIORITY, fr.ROUTE_NAME FROM PAY_DIMENSION_ROUTES pdr, PAY_BALANCE_DIMENSIONS pbd1, PAY_BALANCE_DIMENSIONS pbd2, FF_ROUTES fr WHERE pdr.BALANCE_DIMENSION_ID = pbd1.BALANCE_DIMENSION_ID AND pdr.RUN_DIMENSION_ID = pbd2.BALANCE_DIMENSION_ID AND pdr.ROUTE_ID = fr.ROUTE_ID AND pbd1.DIMENSION_NAME = 'Assignment Within Quarter to Date' ORDER BY pdr.PRIORITY;

Related Objects

PAY_DIMENSION_ROUTES is centrally connected to other key payroll data definition objects through foreign key constraints. It references the PAY_BALANCE_DIMENSIONS table twice: once via BALANCE_DIMENSION_ID to define the source dimension, and again via RUN_DIMENSION_ID to define the target dimension. It also references the FF_ROUTES table via ROUTE_ID to obtain the procedural logic for navigating the dimension relationship. These relationships are essential for the integrity of the payroll balance configuration, ensuring that every defined route points to valid dimensions and a valid processing routine.