Results for “psb_constraint_formulas”

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

Overview

PSB_CONSTRAINT_FORMULAS is a table within the Public Sector Budgeting (PSB) product family of Oracle E-Business Suite. As indicated by the ETRM documentation, this table stores constraint formula definitions — the step-based rules that govern how budget constraint checks are evaluated in public sector budgeting workflows. Each row represents one step of a constraint formula, combining a constraint reference, a budget year type, optional account segment criteria, arithmetic operators, and an amount or element value.

The ETRM metadata notes that PSB is marked Obsolete and that the table is not implemented in the current database. It remains documented for reference purposes in EBS 12.1.1 and 12.2.2, meaning it is primarily encountered in legacy environments and migration analyses rather than in active implementations.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign-key structure is link. This suggests the table functions less as a standalone master entity and more as an associative construct connecting constraints, budget year types, pay elements, and pay element options. Modelers designing a Data Vault representation would treat CONSTRAINT_FORMULA_ID as a link-style key, with the referenced entities serving as hubs.

Key Information Stored

The documented schema contains 64 columns. The most important are summarized below:

• CONSTRAINT_FORMULA_ID — Surrogate primary key, enforced by PSB_CONSTRAINT_FORMULAS_PK1. A unique index, PSB_CONSTRAINT_FORMULAS_U1, also exists on this column, making it the sole business-key candidate documented in the metadata.
• CONSTRAINT_ID — Foreign key to PSB_ENTITY, identifying the parent constraint to which the formula belongs.
• STEP_NUMBER — Ordinal position of the formula step within the overall constraint formula sequence.
• BUDGET_YEAR_TYPE_ID — Foreign key to PSB_BUDGET_YEAR_TYPES, scoping the formula step to a specific budget year classification.
• BALANCE_TYPE and CURRENCY_CODE — Define which balance is evaluated and in which currency.
• TEMPLATE_ID — Reference to the formula template from which this step was instantiated.
• CONCATENATED_SEGMENTS and SEGMENT1 through SEGMENT30 — The account segment criteria against which the constraint step is matched.
• AMOUNT, PREFIX_OPERATOR, POSTFIX_OPERATOR — The numeric operand and surrounding arithmetic operators for the step.
• PAY_ELEMENT_ID and PAY_ELEMENT_OPTION_ID — Foreign keys used when the step references payroll elements rather than fixed amounts.
• ELEMENT_VALUE and ELEMENT_VALUE_TYPE — The element-derived value and its type classification.
• ALLOW_MODIFY — Flag controlling whether downstream users may amend the formula step.
• EFFECTIVE_START_DATE and EFFECTIVE_END_DATE — Date-range keys supporting temporal validity of the formula definition.
• ATTRIBUTE1–ATTRIBUTE10 and CONTEXT — Standard EBS descriptive flexfield columns.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard Who columns for auditability.

Common Use Cases and Queries

Because the table is obsolete, use cases center on migration, archival reporting, and validation of legacy budget constraint configurations. A typical query retrieves all steps for a given constraint in evaluation order:

• Listing formula steps by constraint: SELECT constraint_formula_id, step_number, balance_type, prefix_operator, amount, postfix_operator FROM psb_constraint_formulas WHERE constraint_id = :p_constraint_id ORDER BY step_number;
• Filtering by budget year type: join to PSB_BUDGET_YEAR_TYPES on BUDGET_YEAR_TYPE_ID to report which year classifications a constraint applies to.
• Identifying payroll-driven steps: query rows where PAY_ELEMENT_ID or PAY_ELEMENT_OPTION_ID is not null to isolate element-based constraint logic.
• Temporal validation: constrain on EFFECTIVE_START_DATE and EFFECTIVE_END_DATE to reconstruct the formula in force at a historical point in time.
• Segment-level reporting: use CONCATENATED_SEGMENTS or individual SEGMENTn columns to group constraint steps by account range.

Related Objects

The documented foreign keys anchor PSB_CONSTRAINT_FORMULAS to several parent tables, and the following relationships are most significant:

• PSB_ENTITY — Referenced via CONSTRAINT_ID; the parent constraint definition owning the formula.
• PSB_BUDGET_YEAR_TYPES — Referenced via BUDGET_YEAR_TYPE_ID; scopes formula applicability by year type.
• PSB_PAY_ELEMENTS — Referenced via PAY_ELEMENT_ID; source of payroll-driven amounts.
• PSB_PAY_ELEMENT_OPTIONS — Referenced via PAY_ELEMENT_OPTION_ID; refines element-based values.
• PSB_CONSTRAINT_FORMULAS_PK1 — Primary key constraint enforcing CONSTRAINT_FORMULA_ID uniqueness.
• PSB_CONSTRAINT_FORMULAS_U1 — Unique index on CONSTRAINT_FORMULA_ID, the documented business-key candidate.

Together these objects form the constraint-definition layer of Public Sector Budgeting, with PSB_CONSTRAINT_FORMULAS acting as the associative link between constraint, budget year type, and payroll element entities.