Search Results pqp_exception_reports_tl




Overview
Explore in AI Chat

Overview

PQP_EXCEPTION_REPORTS_TL is the translation (TL) table for exception report definitions within the Oracle EBS Public Sector Payroll (PQP) product. Exception reports identify payroll conditions that fall outside defined tolerances or business rules — for example, pay elements exceeding expected ranges, employees missing required deductions, or unusual period-to-period variances — and are surfaced to payroll administrators for review during a payroll run. The base definition of each exception report resides in the corresponding PQP_EXCEPTION_REPORTS_B table; the _TL table stores the language-specific, translatable attributes of those definitions, keyed by language code.

The table is owned by the HR schema and is documented as VALID in ETRM 12.1.1 and 12.2.2. Its documented primary key is PQP_EXCEPTION_REPORTS_TL_PK, defined over (EXCEPTION_REPORT_ID, LANGUAGE). From a Data Vault modeling perspective, the metadata's heuristic classification is standalone. Practically, this object behaves as a translation satellite: it is dependent on the base entity (EXCEPTION_REPORT_ID is inherited from the base table) and its granularity is one row per exception report per installed language, with transacted attributes carrying the effective-dated audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE).

Key Information Stored

The table has nine documented columns. The most significant are:

  • EXCEPTION_REPORT_ID — surrogate identifier inherited from the base table; part of both the primary key and the business-key unique index.
  • LANGUAGE — the NLS language code qualifying each translated row; the second component of the primary key.
  • SOURCE_LANG — the language in which the report name/description was originally entered; the source language row is the authoritative non-translated value.
  • EXCEPTION_REPORT_NAME — the translated name of the exception report displayed to users in their session language.
  • CREATED_BY, CREATION_DATE — who created and when the row was inserted.
  • LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit trail of the most recent change.

The surrogate primary key is PQP_EXCEPTION_REPORTS_TL_PK (EXCEPTION_REPORT_ID, LANGUAGE). The metadata also lists a unique index named PEL_PK over the same two columns; this is the business-key candidate, confirming that the pair defines uniqueness. There is no separate single-column surrogate.

Common Use Cases and Queries

Translation tables are consulted whenever a report label, parameter prompt, or lookup value must be rendered in the user's language. Typical reporting and validation queries include:

  • Retrieving the localized display name for a report: SELECT EXCEPTION_REPORT_NAME FROM HR.PQP_EXCEPTION_REPORTS_TL WHERE EXCEPTION_REPORT_ID = :p_id AND LANGUAGE = USERENV('LANG');
  • Listing all translations for a report: SELECT LANGUAGE, SOURCE_LANG, EXCEPTION_REPORT_NAME FROM HR.PQP_EXCEPTION_REPORTS_TL WHERE EXCEPTION_REPORT_ID = :p_id ORDER BY LANGUAGE;
  • Validating translation completeness by comparing counts against the base table and flagging reports where the required language row is missing.
  • Auditing recent changes to report names using LAST_UPDATE_DATE and LAST_UPDATED_BY.
  • Building extract files for documentation or metamorphic reconciliation between the _B and _TL tables.

Because this table holds only translatable descriptive attributes, it is not used for transactional payroll calculations; it supports the user interface and reporting presentation layer.

Related Objects

  • PQP_EXCEPTION_REPORTS_B — the base table; joins on EXCEPTION_REPORT_ID. It stores the non-translatable definition attributes.
  • PQP_EXCEPTION_REPORTS_VL — the MLS view that outer-joins the base and translation tables, filtered by the current language, and is the object most application code and reports should query.
  • FND_LANGUAGES — validates the LANGUAGE and SOURCE_LANG values against installed languages.
  • FND_APPLICATION / FND_PRODUCT_INSTALLATIONS — determines whether the PQP application is licensed and available in a given language.
  • HR_ALL_ORGANIZATION_UNITS and related HR validation tables — used where exception reports are scoped to specific payroll organizations or business groups.
  • PAY_ / PQP exception-report runtime tables — consume EXCEPTION_REPORT_ID to record generated exceptions during processing.

All joins should use EXCEPTION_REPORT_ID, and language filtering should normally be delegated to the _VL view to avoid hand-coded NLS logic. Oracle proprietary and confidential; retransmission is prohibited.