Search Results pay_time_spans_uk1

Overview

HR.PAY_TIME_SPANS is a seed data table in the Oracle E-Business Suite HR (Payroll) schema that defines named time span constructs used by Oracle Payroll. A time span represents a bounded interval — characterized by a start definition and an end definition — that the payroll engine uses to evaluate element eligibility, proration, retroactive processing, and span-based element calculations. The table's physical storage resides in the APPS_TS_SEED tablespace with a PCTFREE of 10, reflecting its role as relatively static reference data rather than high-volume transactional data. The FND Design Data reference is PAY.PAY_TIME_SPANS.

Under the heuristic Data Vault classification mined from its foreign key structure, the object is classified as standalone, with no outgoing dependencies on other database objects. This suggests a modeling approach where PAY_TIME_SPANS can be treated as an independent reference dimension rather than a hub or link requiring upstream lineage tracing. The classification is a modeling suggestion only; the table functions in practice as a definition repository consumed by downstream span usage records.

Key Information Stored

The table contains six documented columns. The most significant are summarized below.

• TIME_SPAN_ID (NUMBER): The surrogate primary key, enforced by PAY_TIME_SPANS_PK. It uniquely identifies each time span definition and is the column referenced by dependent tables.
• CREATOR_ID (NUMBER, length 15): Identifies the entity that owns or created the time span. Together with CREATOR_TYPE, it forms part of the first business-key candidate.
• CREATOR_TYPE (VARCHAR2, length 30): Characterizes the creator context (for example, an element or a payroll object type), qualifying the CREATOR_ID value.
• START_TIME_DEF_ID (NUMBER): References the time definition that marks the beginning boundary of the span.
• END_TIME_DEF_ID (NUMBER): References the time definition that marks the closing boundary of the span.
• ZD_EDITION_NAME: The editioning column present in the 12.2.2 documented physical schema, supporting Online Patching edition-based redefinition.

The business-key candidate is captured by the unique index PAY_TIME_SPANS_UK1, which spans CREATOR_ID, CREATOR_TYPE, START_TIME_DEF_ID, END_TIME_DEF_ID, and ZD_EDITION_NAME. This composite uniqueness ensures that a given creator cannot define duplicate span boundaries. In 12.2.2, both PAY_TIME_SPANS_PK and PAY_TIME_SPANS_UK1 include ZD_EDITION_NAME, whereas the 12.1.1 schema documents the unique key without the editioning column.

Common Use Cases and Queries

Typical usage centers on resolving a span definition for a given creator and then joining forward to span usages. A basic lookup retrieves all span definitions for a specific creator:

SELECT TIME_SPAN_ID, CREATOR_ID, CREATOR_TYPE, START_TIME_DEF_ID, END_TIME_DEF_ID FROM HR.PAY_TIME_SPANS WHERE CREATOR_ID = :p_creator_id AND CREATOR_TYPE = :p_creator_type;

Because PAY_TIME_SPANS_UK1 is unique on the creator and boundary columns, the preceding query returns at most one row per creator/boundary combination, making it suitable for validation routines and data-integrity checks. Reporting use cases include auditing which element definitions have span logic configured, reconciling start and end time definitions against their source definitions, and diagnosing proration or retroactive calculation anomalies. Administrators may also query the table to confirm seed data integrity after patching or upgrades.

Related Objects

The documented relationship data identifies one dependent table and the APPS synonyms that expose this object.

• PAY_ELEMENT_SPAN_USAGES: References HR.PAY_TIME_SPANS via the TIME_SPAN_ID foreign key (PAY_ELEMENT_SPAN_USAGES.TIME_SPAN_ID → PAY_TIME_SPANS.TIME_SPAN_ID). This is the primary consuming object and the principal join target for span-based element queries.
• APPS.PAY_TIME_SPANS: The APPS-schema synonym providing application-level access.
• PUBLIC.PAY_TIME_SPANS: The public synonym exposing the table to session-level queries.

Beyond these, PAY_TIME_SPANS does not reference any database object, confirming its standalone classification. Analysts joining span definitions to consuming element logic should always route through PAY_ELEMENT_SPAN_USAGES, since no other documented foreign key relationship exists.