Search Results pay_time_spans




Overview
Explore in AI Chat

Overview

PAY_TIME_SPANS is a payroll configuration table owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It defines reusable time span definitions — bounded intervals of time expressed as a start point and an end point — that payroll processing logic uses to calculate durations, prorate values, or determine the effective window over which an element or rule applies. Time spans are fundamental to payroll because proration, retroactive adjustments, and accrual calculations all depend on a clearly delimited interval.

Under the heuristic Data Vault classification mined from its foreign key structure, PAY_TIME_SPANS is modeled as a standalone entity. This designation is a modeling suggestion rather than a physical constraint: the table has no outbound foreign keys, and it is referenced by only one dependent table. Its role is closer to that of a reference or definition table than to a hub, link, or satellite in the classic Data Vault sense. In ETRM 12.2.2 the table is documented with six columns and carries a multitenant-aware edition column (ZD_EDITION_NAME), reflecting the 12.2 online patching architecture.

Key Information Stored

Each row represents one named time span definition. The most significant columns are:

  • TIME_SPAN_ID — the surrogate primary key, uniquely identifying each time span definition. It is the column referenced by dependent tables.
  • CREATOR_ID — the identifier of the object that owns or creates the time span, such as an element, rule, or definition record.
  • CREATOR_TYPE — a discriminator that identifies the class of entity named in CREATOR_ID, allowing a single time span table to serve multiple creator types.
  • START_TIME_DEF_ID — the identifier of the time definition that establishes the beginning boundary of the span.
  • END_TIME_DEF_ID — the identifier of the time definition that establishes the ending boundary of the span.
  • ZD_EDITION_NAME — the edition discriminator used by 12.2 online patching; in 12.1.1 this concept does not apply.

Two indexes are documented. PAY_TIME_SPANS_PK (TIME_SPAN_ID, ZD_EDITION_NAME) is the surrogate primary key. PAY_TIME_SPANS_UK1 (CREATOR_ID, CREATOR_TYPE, START_TIME_DEF_ID, END_TIME_DEF_ID, ZD_EDITION_NAME) is the business-key candidate, enforcing that a given creator cannot define two identical start/end boundary pairs.

Common Use Cases and Queries

Time spans are consumed primarily by payroll element configuration, where they determine the period over which an element's value is calculated or prorated. The most common access pattern is a lookup by creator, resolving the boundary definitions into usable dates.

  • Retrieving all time spans for a specific element or creator: SELECT time_span_id, start_time_def_id, end_time_def_id FROM pay_time_spans WHERE creator_id = :creator_id AND creator_type = :creator_type;
  • Validating configuration integrity by joining the start and end boundaries to their time definition tables to verify that each span resolves to concrete dates.
  • Impact analysis before changing a creator definition, since any modification to a creator invalidates the UK1 business key.
  • Diagnosing proration errors by comparing the span boundaries used in a payroll run against expected period dates.
  • Reporting on payroll rule coverage, listing which creators have defined spans and which rely on defaults.

Because the table is small and reference-oriented, queries should filter on CREATOR_ID and CREATOR_TYPE rather than scanning, and reporting extracts should always include ZD_EDITION_NAME in 12.2 environments to avoid cross-edition duplication.

Related Objects

The documented foreign key relationship identifies one directly dependent table, supplemented here by the boundary definitions the table references and the payroll element framework that consumes it.

  • PAY_ELEMENT_SPAN_USAGES — the primary dependent table; its TIME_SPAN_ID column references PAY_TIME_SPANS, associating element usages with a specific time span.
  • PAY_ELEMENTS — element definitions that ultimately drive span consumption through element span usages.
  • PAY_ELEMENT_TYPES — classification of elements whose proration rules may invoke time spans.
  • Time definition tables referenced by START_TIME_DEF_ID and END_TIME_DEF_ID, which resolve span boundaries to actual dates and times.
  • PAY_PAYROLL_ACTIONS and related run tables, where span logic influences calculated results.

Together these objects form the payroll element configuration chain: element type, element, element span usage, and time span. PAY_TIME_SPANS sits at the end of that chain as a reusable definition, which is why it has no outbound foreign keys and only one documented dependent.