Search Results pqp_exception_report_suffix




Overview
Explore in AI Chat

Overview

PQP_EXCEPTION_REPORT_SUFFIX is a Public Sector Payroll (PQP) configuration table owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to store the balance dimension database suffix together with its equivalent reporting period. In practice, the table maps an internal balance dimension identifier — represented as a database item suffix — to a reporting period used by exception reporting logic, allowing payroll exception reports to resolve which balance dimension to interrogate for a given legislative context and reporting window.

From a Data Vault modeling perspective, the mined FK structure classifies this object as standalone. It therefore behaves less like a classic hub, link, or satellite and more like a reference or lookup table. Where a Data Vault treatment is suggested, the combination of DATABASE_ITEM_SUFFIX and LEGISLATION_CODE would naturally act as a business key within a hub, with descriptive attributes such as EXCEPTION_REPORT_PERIOD and YEAR_BEGIN_DATE modeled as satellite context. This is a modeling suggestion, not a documented classification.

Key Information Stored

The table is documented with 11 columns. The most significant are:

  • DATABASE_ITEM_SUFFIX — the balance dimension database suffix; part of the composite primary key and a core business identifier.
  • LEGISLATION_CODE — the legislation (country/regulatory context) to which the suffix applies; also part of the primary key.
  • EXCEPTION_REPORT_PERIOD — the reporting period equivalent associated with the suffix; the principal descriptive attribute.
  • YEAR_BEGIN_DATE — the beginning date of the year used for reporting-period calculations.
  • ZD_EDITION_NAME — editioning metadata column, included in the unique index.
  • OBJECT_VERSION_NUMBER — optimistic locking / concurrency control.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns.

The primary key constraint is PQP_EXCEPTION_REPORT_SUFFIX_PK, defined over (DATABASE_ITEM_SUFFIX, LEGISLATION_CODE). The documented unique index additionally lists ZD_EDITION_NAME, giving the full business-key candidate set (DATABASE_ITEM_SUFFIX, LEGISLATION_CODE, ZD_EDITION_NAME). There is no separate single-column surrogate key documented; the composite key serves as the row identifier.

Common Use Cases and Queries

Typical usage involves resolving the reporting period for a given balance dimension suffix and legislation before generating exception reports, or validating that a configured suffix has a corresponding period mapping. A representative query retrieves the mapping for a specific legislation:

  • SELECT DATABASE_ITEM_SUFFIX, LEGISLATION_CODE, EXCEPTION_REPORT_PERIOD, YEAR_BEGIN_DATE FROM HR.PQP_EXCEPTION_REPORT_SUFFIX WHERE LEGISLATION_CODE = :legislation_code;
  • To find all suffix mappings for a specific legislative reporting cycle, filter additionally on EXCEPTION_REPORT_PERIOD.
  • To audit recent changes, order by LAST_UPDATE_DATE and inspect LAST_UPDATED_BY.

Because the table is small and configuration-oriented, it is frequently joined to balance-dimension and payroll reporting logic to translate internal suffixes into human-readable periods. It is not a high-volume transactional table.

Related Objects

The metadata indicates a standalone classification with no documented foreign-key dependencies to external tables. Related objects are therefore inferable from functional context rather than FK data:

  • Balance dimension definitions referenced via DATABASE_ITEM_SUFFIX.
  • Legislation records in HR legislative tables referenced via LEGISLATION_CODE.
  • Exception report generation logic within PQP Public Sector Payroll that consumes EXCEPTION_REPORT_PERIOD.
  • Standard WHO/audit infrastructure referenced through CREATED_BY and LAST_UPDATED_BY.
  • Editioning metadata associated with ZD_EDITION_NAME.

As the documented schema is limited, these relationships should be confirmed against the specific implementation before being relied upon for integration design.