Search Results pay_report_variables_pk

Overview

HR.PAY_REPORT_VARIABLES is a transactional configuration table in the Oracle E-Business Suite HR (Payroll) schema that stores the individual variables belonging to Oracle Payroll report definitions. Each row represents a named variable — with an assigned definition type and a stored value — that a report definition uses to drive its runtime behavior, parameter substitution, and layout logic. In EBS 12.1.1 and 12.2.2 the table is owned by the HR schema, carries the FND design data reference PAY.PAY_REPORT_VARIABLES, and resides in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10. Its documentation describes it as containing "the valid templates for various report definitions."

The metadata's heuristic Data Vault classification identifies this object as standalone. In Data Vault modeling terms, it behaves as a hub-like keyed entity: it holds a surrogate identity keyed by REPORT_VARIABLE_ID, but it is not modelled as a link or a pure satellite within the documented FK structure. The classification is a modeling suggestion rather than a schema-enforced artifact, and the table's actual role is a child configuration table of PAY_REPORT_DEFINITIONS.

Key Information Stored

The surrogate primary key is REPORT_VARIABLE_ID (NUMBER(15)), enforced by the unique index PAY_REPORT_VARIABLES_PK. In the 12.2.2 documented physical schema, that unique index is composite, spanning (REPORT_VARIABLE_ID, ZD_EDITION_NAME); the primary key definition itself is listed as PAY_REPORT_VARIABLES_PK over REPORT_VARIABLE_ID with ZD_EDITION_NAME. The most significant columns are:

• REPORT_VARIABLE_ID — surrogate unique key for the variable row.
• REPORT_DEFINITION_ID — foreign key to PAY_REPORT_DEFINITIONS; identifies the parent report definition to which the variable belongs.
• DEFINITION_TYPE — VARCHAR2(30); classifies the nature of the variable (e.g. the kind of value being defined).
• NAME — VARCHAR2(80); the variable name used by the report definition.
• VALUE — VARCHAR2(250); the value assigned to that variable.
• LEGISLATION_CODE — VARCHAR2(150); restricts the variable to a specific legislation/territory.
• BUSINESS_GROUP_ID — NUMBER(15); scopes the row to a particular business group.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the ORM layer.
• ZD_EDITION_NAME — VARCHAR2(30); the edition identifier that supports EBS 12.2.x edition-based redefinition and online patching.
• WHO columns — LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE.

Altogether the documented physical schema exposes 14 columns; the surrogate key (REPORT_VARIABLE_ID) should be distinguished from the edition-aware business key expressed through the composite unique index.

Common Use Cases and Queries

The table is primarily consumed when diagnosing why a report definition resolves particular values, and when extracting configuration for migration, audit, or comparison across environments. Typical work includes listing all variables for a given report definition, locating variables by name, and filtering by legislation or business group.

• List variables for one report definition:

  SELECT REPORT_VARIABLE_ID, NAME, VALUE, DEFINITION_TYPE
  FROM   HR.PAY_REPORT_VARIABLES
  WHERE  REPORT_DEFINITION_ID = :p_report_definition_id
  ORDER  BY NAME;

• Find a variable by name within a legislation:

  SELECT pv.NAME, pv.VALUE, pv.DEFINITION_TYPE
  FROM   HR.PAY_REPORT_VARIABLES pv
  WHERE  pv.NAME = :p_name
  AND    pv.LEGISLATION_CODE = :p_legislation;

• Configuration extraction joining the parent definition:

  SELECT pd.NAME AS definition_name, pv.NAME, pv.VALUE
  FROM   HR.PAY_REPORT_DEFINITIONS pd,
         HR.PAY_REPORT_VARIABLES  pv
  WHERE  pv.REPORT_DEFINITION_ID = pd.REPORT_DEFINITION_ID;

Because the table is edition-enabled in 12.2.2, queries should be executed against the editioned synonym/view exposed in the APPS schema rather than assuming a single uneditioned row set.

Related Objects

• HR.PAY_REPORT_DEFINITIONS — referenced by this table through REPORT_DEFINITION_ID; the parent entity that groups the variables.
• HR.PAY_REPORT_RECORD_TEMPLATES — references this table through REPORT_VARIABLE_ID; record templates that bind a variable into a generated report record.
• HR.PAY_REPORT_VARIABLES# — the underlying editioning object that supports edition-based redefinition of this table.
• PAY_REPORT_VARIABLES_PK — the unique index on APPS_TS_TX_IDX enforcing the key over REPORT_VARIABLE_ID and ZD_EDITION_NAME.
• FND design data PAY.PAY_REPORT_VARIABLES — the application definition mapping the table into the EBS data model.
• APPS synonym PAY_REPORT_VARIABLES — the application-facing access path used by forms, concurrent programs, and reporting tools.

Taken together, these relationships establish PAY_REPORT_VARIABLES as the variable-level configuration layer beneath report definitions and above record templates, with no additional outbound foreign keys documented in the supplied metadata.