Search Results element_span_usage_id

Overview

PAY_ELEMENT_SPAN_USAGES is a Payroll (PAY) module configuration table owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to support RetroPay functionality by determining which retro element should be used when the payroll engine processes retrospective adjustments. In practical terms, the table maps a retro component usage to a specific time span and adjustment type, and then points to the retro element that must be applied for that combination. This allows Oracle Payroll to select the correct element when recalculating prior-period earnings, deductions, or balances.

The table is classified as VALID and contains 14 documented columns. Under a heuristic Data Vault classification mined from its foreign key structure, PAY_ELEMENT_SPAN_USAGES is modeled as a standalone object rather than a conventional hub, link, or satellite. In practice, it functions as a link-style configuration entity: it associates a retro component usage with a time span, an adjustment type, and a business group, producing a composite business key. The classification should be treated as a modeling suggestion only, since the table carries descriptive attributes (retro element type, audit columns, edition name) that are satellite-like in nature.

Key Information Stored

The surrogate primary key is ELEMENT_SPAN_USAGE_ID, enforced by the PAY_ELEMENT_SPAN_USAGES_PK index. The business-key candidate is captured by the unique index PAY_ELEMENT_SPAN_USAGES_UK1, which spans RETRO_COMPONENT_USAGE_ID, TIME_SPAN_ID, ADJUSTMENT_TYPE, BUSINESS_GROUP_ID, LEGISLATION_CODE, and ZD_EDITION_NAME. This composite key guarantees that, within a business group and legislation, a given retro component usage is mapped only once per time span and adjustment type.

The most significant columns are:

• ELEMENT_SPAN_USAGE_ID — surrogate primary key and the primary join column to dependent records.
• RETRO_COMPONENT_USAGE_ID — foreign key to PAY_RETRO_COMPONENT_USAGES, identifying the retro component being configured.
• TIME_SPAN_ID — foreign key to PAY_TIME_SPANS, defining the time span (for example, period-to-date or year-to-date) over which the retro calculation applies.
• ADJUSTMENT_TYPE — indicates the category of adjustment for which this mapping is valid.
• RETRO_ELEMENT_TYPE_ID — identifies the actual retro element to be used during the recalculation.
• BUSINESS_GROUP_ID — the business group that owns the configuration row.
• LEGISLATION_CODE — the legislation under which the mapping applies, enabling localization.

Standard audit and concurrency columns complete the structure: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER. The presence of ZD_EDITION_NAME reflects the edition-based redefinition strategy used in Oracle EBS 12.2.2, allowing multiple editions of the same row to coexist.

Common Use Cases and Queries

The principal use case is diagnosing and controlling RetroPay behavior. Payroll administrators query this table to verify that a retro component resolves to the intended retro element for each time span and adjustment type. A typical query joins the table to PAY_TIME_SPANS and PAY_RETRO_COMPONENT_USAGES to produce a readable configuration report:

• Identifying all retro element mappings for a given business group and legislation.
• Detecting missing or duplicate mappings that could cause RetroPay to select an unintended element.
• Tracing why a retro calculation used a particular element during a recalculation run.
• Supporting migration or upgrade validation by comparing mappings across environments.

Because the table is keyed on RETRO_COMPONENT_USAGE_ID, TIME_SPAN_ID, and ADJUSTMENT_TYPE, effective queries filter on BUSINESS_GROUP_ID and LEGISLATION_CODE first to limit the result set, then join outward to time spans and retro component usages on their respective IDs.

Related Objects

The documented foreign keys identify the two tables on which PAY_ELEMENT_SPAN_USAGES directly depends, and both are central to interpreting its rows:

• PAY_TIME_SPANS — joined on TIME_SPAN_ID; defines the span semantics used by the mapping.
• PAY_RETRO_COMPONENT_USAGES — joined on RETRO_COMPONENT_USAGE_ID; defines the retro component being mapped.

Beyond these, the retro element referenced by RETRO_ELEMENT_TYPE_ID logically relates to the element definition tables (such as PAY_ELEMENT_TYPES_F and PAY_ELEMENTS), and the business group column aligns with HR business group definitions (PER_BUSINESS_GROUPS). RetroPay processing logic within the payroll engine consumes this table at calculation time, so reporting views and concurrent programs that summarize retro configuration should be read together with these related objects rather than in isolation.