Search Results pay_report_definitions




Overview
Explore in AI Chat

Overview

PAY_REPORT_DEFINITIONS is a Payroll (PAY) module table residing in the HR schema. It stores the master definition records for payroll reports, magnetically encoded report layouts, and other reporting artifacts that Oracle Payroll generates or consumes. Each row represents a single report definition instance, keyed on a surrogate identifier and scoped by an edition name. In Oracle EBS 12.1.1 and 12.2.2, this table functions as configuration metadata that drives how payroll reports are grouped, categorized, and rendered.

The metadata classifies this object heuristically as standalone within a Data Vault modeling context. This suggests that, although the table carries foreign key references outward to configuration parents, it is not itself a pure transaction link or satellite; it behaves as a reference or definition entity. A dimensional modeler might treat it as a hub-like definition table with descriptive attributes, though the classification is a mining heuristic rather than an intrinsic property of the physical schema.

Key Information Stored

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

  • REPORT_DEFINITION_ID — the surrogate primary key, generated by the sequence behind constraint PAY_REPORT_DEFINITION_PK. This is the column referenced by all dependent child tables.
  • ZD_EDITION_NAME — the editioning column. Together with REPORT_DEFINITION_ID, it forms the unique business-key index PAY_REPORT_DEFINITION_PK in the 12.2.2 documented physical schema. This reflects Edition-Based Redefinition (EBR), which exists only in 12.2 and later releases.
  • REPORT_NAME — the user-facing name of the report definition, used for lookups and list-of-values presentation.
  • REPORT_TYPE — categorizes the kind of report (for example standard, magnetic media, or other classification values).
  • REPORT_LEVEL — indicates the reporting granularity or organizational scope to which the definition applies.
  • REPORT_GROUP_ID — foreign key to PAY_REPORT_GROUPS, placing the definition within a logical group of related reports.
  • MAGNETIC_BLOCK_ID — foreign key to PAY_MAGNETIC_BLOCKS, linking a definition to a magnetic-media block layout where applicable.
  • APPLICATION_SHORT_NAME — identifies the owning Oracle application (for example PAY), supporting multi-application coexistence of report definitions.
  • ALTERABLE — a flag indicating whether the definition may be modified by end users or is locked as seeded data.
  • LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER — the standard WHO audit columns and optimistic-locking version column.

Common Use Cases and Queries

The table is most commonly queried to enumerate available payroll report definitions, to join report categories and variables, and to audit changes to report configuration. A typical listing query retrieves definitions by group and type:

  • SELECT report_definition_id, report_name, report_type FROM pay_report_definitions WHERE report_group_id = :p_group_id ORDER BY report_name;
  • Join to grouping metadata: SELECT d.report_name, g.group_name FROM pay_report_definitions d, pay_report_groups g WHERE d.report_group_id = g.report_group_id;
  • In 12.2 environments, restrict to the active edition: WHERE zd_edition_name = :edition.
  • Audit trail queries filter on last_update_date or last_updated_by to identify recently changed definitions.

Reporting use cases include generating catalogues of seeded versus alterable reports, tracing which magnetic block layouts are attached to which reports, and supporting migration/version comparisons between instances.

Related Objects

The following objects reference or are referenced by PAY_REPORT_DEFINITIONS through documented foreign key relationships.

  • PAY_REPORT_GROUPS — parent of REPORT_GROUP_ID; groups related report definitions.
  • PAY_MAGNETIC_BLOCKS — parent of MAGNETIC_BLOCK_ID; defines magnetic-media block layouts used by report definitions.
  • PAY_REPORT_CATEGORY_COMPONENTS — child referencing REPORT_DEFINITION_ID; maps categories to report components.
  • PAY_REPORT_VARIABLES — child referencing REPORT_DEFINITION_ID; stores variable bindings used by the report at runtime.

These relationships establish PAY_REPORT_DEFINITIONS as a central definition anchor for payroll report configuration. Technical teams should treat the REPORT_DEFINITION_ID / ZD_EDITION_NAME combination as the authoritative uniqueness constraint in 12.2.x deployments, while recognizing that the edition column does not exist in 12.1.1, where REPORT_DEFINITION_ID alone governs uniqueness.