Search Results pay_report_variables




Overview
Explore in AI Chat

Overview

PAY_REPORT_VARIABLES is a payroll module table owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the individual variable definitions associated with a payroll report definition, allowing a report to expose a set of named, parameterized values that are resolved at runtime. Each row captures one report variable, its parent report definition, its definition type, its literal or default value, and the legislative and business group context under which the variable applies. The table therefore acts as the configuration layer that drives parameterization of payroll report output, joining a report definition to the specific variables used by that report and, downstream, to the record templates that place those variables within printed output.

Under a heuristic Data Vault classification mined from its foreign key structure, PAY_REPORT_VARIABLES is assessed as a standalone object rather than a classical hub, link, or satellite. It does hold a foreign key to PAY_REPORT_DEFINITIONS, but the mined classification treats it as independent, reflecting that its grain is defined primarily by its own primary key and edition rather than by a multi-parent relationship. This classification should be treated as a modeling suggestion only; functionally the table behaves as a configuration or attribute-holding entity that qualifies its parent report definition.

Key Information Stored

The documented physical schema lists 14 columns. The most significant are:

  • REPORT_VARIABLE_ID — the surrogate primary key, uniquely identifying each report variable row and enforced through the PAY_REPORT_VARIABLES_PK constraint.
  • REPORT_DEFINITION_ID — foreign key to PAY_REPORT_DEFINITIONS, establishing which report owns the variable.
  • DEFINITION_TYPE — classifies the variable's nature or source, governing how the value is interpreted or resolved.
  • NAME — the identifier of the variable as referenced within the report and its templates.
  • VALUE — the literal or default value assigned to the variable.
  • LEGISLATION_CODE — restricts the variable to a specific legislative context, enabling country-specific report behavior.
  • BUSINESS_GROUP_ID — scopes the variable to a business group, supporting multi-organization isolation.
  • OBJECT_VERSION_NUMBER — supports optimistic locking for concurrent updates.
  • LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — the standard WHO audit columns recording creation and last modification.
  • ZD_EDITION_NAME — the editioning column used by EBS 12.2.2 online patching, forming part of the unique index.

The unique index PAY_REPORT_VARIABLES_PK covers REPORT_VARIABLE_ID together with ZD_EDITION_NAME, while REPORT_VARIABLE_ID alone serves as the surrogate key. No separate business-key unique index beyond this composite is documented.

Common Use Cases and Queries

Typical usage centers on retrieving or validating the variables configured for a given payroll report before submission. A common query pattern joins the variable table to its parent definition:

  • Listing all variables for a report: SELECT v.NAME, v.VALUE, v.DEFINITION_TYPE FROM HR.PAY_REPORT_VARIABLES v WHERE v.REPORT_DEFINITION_ID = :report_id AND v.ZD_EDITION_NAME = 'ORA$BASE';
  • Filtering by legislation and business group: SELECT v.NAME, v.VALUE FROM HR.PAY_REPORT_VARIABLES v WHERE v.LEGISLATION_CODE = :leg AND v.BUSINESS_GROUP_ID = :bg_id;
  • Identifying which record templates consume a variable: join PAY_REPORT_RECORD_TEMPLATES on REPORT_VARIABLE_ID.
  • Auditing configuration changes: order by LAST_UPDATE_DATE to review recently modified variables.

These patterns support report setup validation, migration comparisons between environments, and impact analysis before changing a shared variable value.

Related Objects

The documented relationships identify the following dependent objects:

  • PAY_REPORT_DEFINITIONS — parent table; joined via REPORT_DEFINITION_ID.
  • PAY_REPORT_RECORD_TEMPLATES — references PAY_REPORT_VARIABLES via REPORT_VARIABLE_ID, linking variables to the template records that render them.

Beyond the documented links, the table is typically queried alongside payroll report definition and template views to assemble complete report configuration, since the report definition supplies the report identity and the record templates supply the layout in which the variable values appear.