Search Results pay_trigger_declarations_fk

Overview

HR.PAY_TRIGGER_DECLARATIONS is a setup and metadata table within the Oracle E-Business Suite HR (Human Resources) schema that stores the definitions of variables declared locally within the body of a generated payroll trigger. It is a component of the Payroll Trigger framework, which allows implementers to extend and customize the behavior of database triggers that respond to payroll-related events. Each row in this table represents a single variable declaration that will be emitted as a local (l_ prefixed) declaration when the parent trigger is regenerated by the trigger generation form.

The table plays a narrow but structurally significant role: it is a child of PAY_TRIGGER_EVENTS, meaning every declaration belongs to a specific trigger event definition. The documented foreign key column EVENT_ID enforces this parent-child relationship. From a Data Vault classification perspective, the mined metadata suggests this object is satellite-leaning — it stores descriptive, low-volatility attributes (variable name, data type, size) that depend on a parent key (EVENT_ID), which is characteristic of satellite behavior attached to a business key. The table is stored in the APPS_TS_TX_DATA tablespace with a 10 percent PCTFREE setting.

Key Information Stored

The table comprises six documented columns in the ETRM 12.2.2 schema. The most significant columns are:

• DECLARATION_ID — A system-generated NUMBER(15) surrogate primary key that uniquely identifies each declaration. This column forms the PAY_TRIGGER_DECLARATIONS_PK unique index.
• EVENT_ID — A NUMBER(15) foreign key to PAY_TRIGGER_EVENTS, linking the declaration to its owning trigger event. Indexed non-uniquely by PAY_TRIGGER_DECLARATIONS_FK.
• VARIABLE_NAME — VARCHAR2(60) storing the name of the variable. The system automatically prepends l_, and inline initialization is not permitted.
• DATA_TYPE — The declared type, one of C (Character/VARCHAR2), N (Number), or D (Date).
• VARIABLE_SIZE — NUMBER specifying the size, applicable only to Character type variables.
• ZD_EDITION_NAME — The editioning column supporting Oracle EBS 12.2 online patching (Edition-Based Redefinition). The unique index PAY_TRIGGER_DECLARATIONS_PK is documented as (DECLARATION_ID, ZD_EDITION_NAME).

The surrogate primary key is DECLARATION_ID, while the unique index that includes ZD_EDITION_NAME serves as the edition-aware business-key candidate. Notably, the legislation and business_group_id variables are handled implicitly by the trigger framework rather than being stored as ordinary rows, and the payroll ID column is processed similarly.

Common Use Cases and Queries

Typical usage centers on auditing and reporting on trigger customizations. Developers and administrators often query declarations to review what variables a trigger will emit before regeneration. A common pattern joins declarations to their parent events:

SELECT d.declaration_id, d.variable_name, d.data_type, d.variable_size, e.event_id
FROM   pay_trigger_declarations d,
       pay_trigger_events      e
WHERE  d.event_id = e.event_id
AND    d.variable_name LIKE 'l_%';

Another frequent scenario identifies all character declarations that require size validation, or lists declarations belonging to a specific event for migration between environments. Because modifying rows in this table through the trigger generation form invalidates the parent trigger, reporting queries are also used to determine which triggers require regeneration following a bulk change. Edition-aware queries must filter on ZD_EDITION_NAME to target the correct edition in a 12.2 environment.

Related Objects

• HR.PAY_TRIGGER_EVENTS — Parent table; joined via PAY_TRIGGER_DECLARATIONS.EVENT_ID = PAY_TRIGGER_EVENTS.EVENT_ID.
• PAY_TRIGGER_EVENTS (trigger framework) — Provides the event context against which declarations are generated.
• JE_BE_VAT_ALLOCATIONS — References this table via DECLARATION_ID.
• JE_BE_ESL_SUMMARIES — References this table via DECLARATION_ID.
• Payroll Trigger Generation Form — The primary UI that reads and writes declaration rows and controls trigger regeneration.
• FND Design Data: PAY.PAY_TRIGGER_DECLARATIONS — The design data registration that governs installation and upgrade of the object.

Collectively, these relationships position PAY_TRIGGER_DECLARATIONS as a dependent metadata store whose lifecycle is tightly bound to the trigger events it describes.