Results for “hr_organizations”

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

Overview

HR.PAY_SHADOW_FORMULAS is a transactional table within the Oracle E-Business Suite HR schema that models the FF_FORMULAS_F entity in the element template shadow schema. Its purpose is to hold template payroll calculation formulas, which are assumed to be of type "Oracle Payroll." In practical terms, the table stores the reusable formula definitions that Oracle Payroll attaches to element templates, allowing legislative and business-group-specific payroll calculations to be generated and versioned independently of the live formula definitions.

The table is registered under FND Design Data as PAY.PAY_SHADOW_FORMULAS and resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. From a dimensional-modeling perspective, the mined foreign-key structure suggests a hub-leaning classification, since the table carries its own surrogate primary key, STANDARD WHO columns, and is referenced by several dependent child tables rather than merely enriching a parent entity. This classification should be treated as a heuristic modeling suggestion rather than an enforced Oracle constraint.

Key Information Stored

The table is defined with 15 documented columns. The most significant include:

• FORMULA_ID — the system-generated surrogate primary key, forming part of the composite unique index PAY_SHADOW_FORMULAS_PK along with ZD_EDITION_NAME.
• ZD_EDITION_NAME — the edition discriminator that, combined with FORMULA_ID, constitutes the documented business-key candidate via the unique index PAY_SHADOW_FORMULAS_PK.
• TEMPLATE_TYPE — identifies the element template type the formula is used with.
• FORMULA_NAME and DESCRIPTION — the human-readable identity of the formula and its explanatory text.
• FORMULA_TEXT — a CLOB holding the actual formula body.
• FORMULA_TYPE_NAME — the formula type designation; Oracle Payroll formulas are the assumed case.
• LEGISLATION_CODE — foreign key to FND_TERRITORIES, scoping the formula to a legislative context.
• BUSINESS_GROUP_ID — foreign key to HR_ORGANIZATIONS, tying the formula to the owning business group.
• OBJECT_VERSION_NUMBER — optimistic locking and versioning column.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns.

A secondary unique LOB index, SYS_IL0000042452C00013$$, supports the CLOB column in APPS_TS_TX_DATA.

Common Use Cases and Queries

Typical scenarios include auditing which formulas back a given element template, reporting formula coverage per legislation or business group, and tracing formula versions across editions. A basic retrieval query is:

• SELECT FORMULA_ID, FORMULA_NAME, TEMPLATE_TYPE, LEGISLATION_CODE FROM PAY_SHADOW_FORMULAS WHERE BUSINESS_GROUP_ID = :bg_id;
• Filtering by legislation: WHERE LEGISLATION_CODE = 'US' AND TEMPLATE_TYPE = :template_type.
• Joining to the business group: FROM PAY_SHADOW_FORMULAS f, HR_ORGANIZATIONS o WHERE f.BUSINESS_GROUP_ID = o.ORGANIZATION_ID.

Related Objects

The documented foreign-key relationships and dependencies include:

• PAY_SHADOW_ELEMENT_TYPES — references this table via PAYROLL_FORMULA_ID, associating each shadow element type with its payroll formula.
• PAY_TEMPLATE_FF_USAGES — references this table via FORMULA_ID, tracking where a formula is used by templates.
• HR_ORGANIZATIONS — the parent of BUSINESS_GROUP_ID.
• FND_TERRITORIES — the parent of LEGISLATION_CODE.
• FND_USER and FND_LOGINS — referenced by the WHO audit columns.