Search Results psb_formulas

Overview

The PSB_FORMULAS table is a core repository for PL/SQL formulas within the Oracle E-Business Suite Public Sector Budgeting (PSB) module. It serves as a centralized library of calculation logic specifically designed for pay element costing. In the context of government and public sector budgeting, accurately projecting personnel costs is critical. This table enables the definition of reusable, complex calculation rules that can be dynamically applied to various pay elements and rates, moving beyond simple fixed amounts or percentages. Its primary role is to provide the executable logic that drives automated and standardized cost computations during budget formulation and simulation.

Key Information Stored

While the provided metadata does not list individual columns, the primary key and foreign key relationships define its essential structure. The central column is FORMULA_ID, a unique identifier for each PL/SQL formula. The table stores the actual PL/SQL code block that constitutes the formula logic. This code is executed by the PSB engine, typically receiving parameters (like hours, base salary, or other factors) and returning a calculated cost amount. Other likely columns include metadata such as the formula name (FORMULA_NAME), an active/inactive status flag, a description, and timestamps for creation and last update. The integrity of the stored PL/SQL code is paramount, as it directly impacts financial projections.

Common Use Cases and Queries

The primary use case is the dynamic calculation of compensation costs for budget positions. For example, a formula might calculate overtime costs as a function of regular hours, a multiplier, and a pay rate, or compute fringe benefits as a complex tiered percentage of salary. Administrators use this table to maintain and audit calculation logic. Common queries include listing all active formulas, finding formulas used by a specific pay element, or validating formula parameters. A sample query to audit formula usage is:

• SELECT F.FORMULA_ID, F.FORMULA_NAME, COUNT(PE.PAY_ELEMENT_ID) AS ELEMENT_COUNT FROM PSB.PSB_FORMULAS F, PSB.PSB_PAY_ELEMENTS PE WHERE F.FORMULA_ID = PE.FORMULA_ID(+) GROUP BY F.FORMULA_ID, F.FORMULA_NAME;

This helps identify unused formulas or those critical to many pay elements. Troubleshooting often involves querying the formula text alongside its assigned parameters from related tables.

Related Objects

The PSB_FORMULAS table has defined relationships with several key PSB tables, as documented in the metadata. It is the parent table in the following foreign key relationships:

• PSB_FORMULA_PARAMETERS: Links via PSB_FORMULA_PARAMETERS.FORMULA_ID. This table stores the configurable input variables required by each PL/SQL formula stored in PSB_FORMULAS.
• PSB_PAY_ELEMENTS: Links via PSB_PAY_ELEMENTS.FORMULA_ID. This critical table defines compensation components (e.g., Base Salary, Overtime) and references a formula from PSB_FORMULAS to determine its cost.
• PSB_PAY_ELEMENT_RATES: Links via PSB_PAY_ELEMENT_RATES.FORMULA_ID. This table stores specific rate details for pay elements and can also utilize a formula for calculations, providing another layer of application.

These relationships illustrate that PSB_FORMULAS is a shared reference point, enabling a single formula to be parameterized and then applied across multiple pay elements and rate structures within the budgeting system.