Search Results pay_retro_notif_reports




Overview
Explore in AI Chat

Overview

The PAY_RETRO_NOTIF_REPORTS table is a payroll-module data object owned by the HR schema in Oracle E-Business Suite (validated across 12.1.1 and 12.2.2). It functions as the persistent staging and audit store for the RetroNotification Report, an output that informs payroll administrators, HR specialists, and employees of retrospective payroll adjustments — namely pay changes (retroactive increases, decreases, or recalculations) that were applied to an assignment after the original payroll period was processed. Rows are inserted into this table when the report is generated, so the table effectively captures a point-in-time snapshot of retroactive adjustments detected, the assignment and element entry affected, the payroll run context, and the dates on which the change was earned versus recognized.

From a Data Vault modeling perspective, the mined relationship structure classifies this object heuristically as standalone, meaning it holds no enforced foreign-key dependencies beyond the single documented reference to PAY_EVENT_GROUPS (EVENT_GROUP_ID). Analysts should treat this as a modeling suggestion rather than a physical constraint: because the table does not act as a true hub or link in a conformed warehouse sense, it is best modeled as a transactional or notification staging table rather than a normalized reference entity.

Key Information Stored

The table contains 11 documented columns. The most significant are grouped below.

  • REPORT_ID — surrogate identifier for the report run row; serves as the primary access key for this object.
  • PAYROLL_ID — identifies the payroll run context (payroll action) under which the retro adjustment was surfaced.
  • REPORT_DATE — the effective date of report generation; supports period-based retrieval and retention.
  • ASSIGNMENT_ID — business-key candidate linking the notification to the employee assignment whose retro adjustment triggered the row. Combined with PAYROLL_ID it provides a strong association key.
  • ELEMENT_ENTRY_ID — the specific element entry (earnings or deduction line) affected by the retroactive change.
  • EVENT_GROUP_ID — the sole documented foreign key, referencing PAY_EVENT_GROUPS; ties the notification to the event group that produced the retro calculation.
  • DATE_PROCESSED and DATE_EARNED — the recognition date versus the effective/original earn date, the pair that defines the retroactive interval.
  • CHANGE_TYPE — classifies the nature of the adjustment (for example, a rate change versus a recalculated amount), enabling filtered reporting.
  • ASSIGNMENT_SET_ID — the assignment set scope used when the report was executed, allowing results to be reproduced.
  • BUSINESS_GROUP_ID — the enterprise business group (legal employer) discriminator, essential for multi-organization security filtering.

No unique index beyond the surrogate REPORT_ID is documented in the supplied metadata.

Common Use Cases and Queries

Typical uses include auditing retroactive pay adjustments by payroll period, reconciling message counts produced by the RetroNotification process, and feeding downstream analytics on retro frequency by assignment or element. A representative query lists adjustments for a period:

  • SELECT assignment_id, element_entry_id, date_earned, date_processed, change_type FROM pay_retro_notif_reports WHERE payroll_id = :p_payroll_id AND business_group_id = :p_bg_id;
  • Joining to PAY_EVENT_GROUPS on EVENT_GROUP_ID to classify the originating event.
  • Aggregating by CHANGE_TYPE and REPORT_DATE to trend retro volume for auditing or compliance reporting.
  • Correlating ASSIGNMENT_SET_ID with the assignment set definition to reconstruct the population of an archived report run.

Related Objects

  • PAY_EVENT_GROUPS — referenced via EVENT_GROUP_ID; describes the event group driving the retro adjustment.
  • PAY_PAYROLL_ACTIONS / payroll run tables — resolve PAYROLL_ID to a payroll action and period.
  • PER_ALL_ASSIGNMENTS_F — resolves ASSIGNMENT_ID to the employee and terms.
  • PAY_ELEMENT_ENTRIES_F — resolves ELEMENT_ENTRY_ID to the affected earnings/deduction entry.
  • HR_ASSIGNMENT_SETS — resolves ASSIGNMENT_SET_ID to the report scope.
  • PER_BUSINESS_GROUPS — resolves BUSINESS_GROUP_ID for enterprise-level filtering.
  • RetroNotification Report concurrent program — the report process that populates and reads this table.