Results for “sys_il0000042452c00013”

5 results




Overview
Explore in AI Chat

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

Overview

PAY_SHADOW_FORMULAS is a payroll configuration table owned by the HR schema in Oracle E-Business Suite (validated against 12.1.1 and 12.2.2). It stores the "shadow" or template-level copies of Oracle Fast Formula definitions that are attached to element templates rather than to live payroll elements. When a payroll administrator defines an element template, the formulas that drive the template's calculation, validation, or balance rules are held in this table until the template is instantiated. The shadow formulas therefore act as design-time blueprints that are later cloned into the production formula definitions referenced by concrete elements. The table is a core component of the Payroll element template framework and is critical to the Oracle Payroll setup migration path.

Based on the foreign key topology mined from the ETRM metadata, PAY_SHADOW_FORMULAS is classified heuristically as hub-leaning. That classification is a modeling suggestion: the table behaves as a central, relatively stable entity (a hub) whose surrogate key is referenced by dependent tables, rather than as a link table that merely resolves a many-to-many relationship or a satellite that carries descriptive history.

Key Information Stored

The table is physically documented in ETRM 12.2.2 with fifteen columns. The most significant are:

  • FORMULA_ID — the surrogate primary key, forming part of the unique index PAY_SHADOW_FORMULAS_PK. It uniquely identifies each shadow formula record.
  • ZD_EDITION_NAME — the editioning column, also part of PAY_SHADOW_FORMULAS_PK. Together with FORMULA_ID, it defines the documented business-key candidate (FORMULA_ID, ZD_EDITION_NAME).
  • FORMULA_NAME — the user-facing name of the shadow formula, used in template maintenance and lookup queries.
  • FORMULA_TEXT — the actual Fast Formula source text. This is the substantive payload of the row and is what gets copied when the template is instantiated onto a real element.
  • FORMULA_TYPE_NAME — identifies the formula class (for example, element skip rule, element input validation, or payroll calculation).
  • TEMPLATE_TYPE — indicates which element template family the shadow formula belongs to.
  • LEGISLATION_CODE — restricts the formula to a specific legislative jurisdiction, driving localization behavior.
  • BUSINESS_GROUP_ID — the enterprise or legislative business group that owns the definition, key to multi-organization filtering.
  • DESCRIPTION — free-text narrative of the formula's purpose.
  • OBJECT_VERSION_NUMBER — the optimistic-locking token used by the OAF/BC4J layer to prevent concurrent update conflicts.
  • LAST_UPDATE_DATE / LAST_UPDATED_BY / CREATION_DATE / CREATED_BY / LAST_UPDATE_LOGIN — the standard WHO audit columns that record row provenance for audit and reconciliation reporting.

Common Use Cases and Queries

Typical scenarios include identifying all shadow formulas attached to a given element template, auditing formula text before a payroll upgrade, and confirming that a shadow formula's legislation matches the target business group.

  • Retrieve the shadow formula tied to a template payroll formula: SELECT f.FORMULA_ID, f.FORMULA_NAME, f.FORMULA_TYPE_NAME FROM PAY_SHADOW_FORMULAS f WHERE f.FORMULA_ID = :payroll_formula_id;
  • List shadow formulas for a business group and legislation: SELECT FORMULA_NAME, TEMPLATE_TYPE FROM PAY_SHADOW_FORMULAS WHERE BUSINESS_GROUP_ID = :bg AND LEGISLATION_CODE = :leg;
  • Audit recent changes: SELECT FORMULA_NAME, LAST_UPDATED_BY, LAST_UPDATE_DATE FROM PAY_SHADOW_FORMULAS ORDER BY LAST_UPDATE_DATE DESC;
  • Confirm join integrity to usages: SELECT u.*, f.FORMULA_NAME FROM PAY_TEMPLATE_FF_USAGES u JOIN PAY_SHADOW_FORMULAS f ON u.FORMULA_ID = f.FORMULA_ID;

Related Objects

Two foreign key relationships are documented in the ETRM metadata:

  • PAY_SHADOW_ELEMENT_TYPES — referenced via PAY_SHADOW_ELEMENT_TYPES.PAYROLL_FORMULA_ID pointing to PAY_SHADOW_FORMULAS. This links a shadow element type to the payroll formula that drives it.
  • PAY_TEMPLATE_FF_USAGES — referenced via PAY_TEMPLATE_FF_USAGES.FORMULA_ID pointing to PAY_SHADOW_FORMULAS. This records where each shadow formula is used within the element template structure.

In practice, these two tables are the principal consumers of the FORMULA_ID key and should be joined on that column when tracing template-to-formula dependencies.