Search Results pay_dated_tables

Overview

The PAY_DATED_TABLES table is a core metadata table within the Oracle E-Business Suite Payroll (PAY) module, specifically for versions 12.1.1 and 12.2.2. It functions as a central registry for datetracked tables. Datetracking is a fundamental Oracle HRMS and Payroll mechanism that enables the maintenance of historical, current, and future-dated information for key transactional entities, such as assignments or salary elements. This table does not store application data itself; instead, it catalogs which tables and columns within the Payroll schema are subject to datetracking rules. Its primary role is to support the integrity and operation of the datetracking framework by providing a reference point for other system components that manage or react to changes in datetracked data.

Key Information Stored

The table's structure is designed to uniquely identify and describe datetracked entities. The primary information stored includes a unique system identifier and the physical table name. The documented columns central to its function are:

• DATED_TABLE_ID: A unique numeric identifier (Primary Key: PAY_DATED_TABLES_PK) for each registered datetracked table. This ID is used as a foreign key in related event tables.
• TABLE_NAME: The actual name of the database table that is datetracked (e.g., PER_ALL_ASSIGNMENTS_F, PAY_ELEMENT_ENTRIES_F). This column has a unique constraint (PAY_DATED_TABLES_UK). While the ETRM excerpt explicitly names these two columns, typical implementations of such a metadata table may also include columns to specify the effective start date and effective end date column names for the registered table, though these are not detailed in the provided metadata.

Common Use Cases and Queries

This table is primarily accessed by the system's internal event and procedural logic, not for routine transactional reporting. A key use case is during the setup or debugging of FastFormula or custom PL/SQL logic that responds to datetracked updates. For instance, a developer might need to identify the correct DATED_TABLE_ID to reference in an event procedure. A common administrative query would be to list all tables registered in the datetracking system:

SELECT dated_table_id, table_name FROM pay_dated_tables ORDER BY table_name;

Another critical use case is tracing the relationships between a datetracked table and the events defined for it. A join to the PAY_DATETRACKED_EVENTS table is essential for this analysis:

SELECT pdt.table_name, pde.event_name
FROM pay_dated_tables pdt, pay_datetracked_events pde
WHERE pdt.dated_table_id = pde.dated_table_id
ORDER BY pdt.table_name;

Related Objects

As per the documented foreign key relationships, the PAY_DATED_TABLES table is a parent table to several key event management tables in the Payroll module. These relationships use the DATED_TABLE_ID column as the join condition:

• PAY_DATETRACKED_EVENTS: Links defined system events (e.g., updates, deletions) to specific datetracked tables via PAY_DATETRACKED_EVENTS.DATED_TABLE_ID.
• PAY_EVENT_PROCEDURES: Associates custom PL/SQL procedures with datetracked tables for event-driven processing via PAY_EVENT_PROCEDURES.DATED_TABLE_ID.
• PAY_EVENT_UPDATES: Tracks specific column updates within datetracked tables that should trigger events via PAY_EVENT_UPDATES.DATED_TABLE_ID.

This network of relationships positions PAY_DATED_TABLES as a cornerstone of the Payroll module's event-driven architecture, enabling automated and audited responses to changes in critical datetracked information.