Results for “message_level”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The HR.HR_H2PI_MESSAGE_LINES table is a core repository within the Oracle E-Business Suite Human Resources (PER) product module. It stores information about all messages generated by the Oracle HR to Oracle Payroll Interface (H2PI). The H2PI process facilitates the transfer of employee, assignment, and payroll-related data from Oracle HRMS to Oracle Payroll, and HR_H2PI_MESSAGE_LINES captures the diagnostic and informational messages produced during that transfer. These messages are essential for auditing, troubleshooting, and validating the integrity of data moving between the HR and Payroll applications in EBS 12.1.1 and 12.2.2.

From a data modeling perspective, the ETRM metadata classifies this object heuristically as a standalone structure under a Data Vault classification scheme. This suggests it functions independently rather than as a conventional hub, link, or satellite, because its primary key is a composite business key rather than a single surrogate identifier. It does, however, reference HR_API_MODULES via a foreign key, indicating a dependency on the API module registry for message context.

Key Information Stored

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

• FROM_ID — Part of the composite primary key. Identifies the source record (for example, a person or assignment identifier) that the message pertains to.
• TABLE_NAME — Part of the composite primary key. Names the source table from which the H2PI message originated, providing context for the data being processed.
• TO_BUSINESS_GROUP_ID — Part of the composite primary key. Identifies the target business group receiving the payroll interface data, enforcing multi-org security boundaries.
• REQUEST_ID — The concurrent request identifier associated with the H2PI run, enabling correlation of messages to a specific execution of the interface program.
• MESSAGE_LEVEL — Indicates the severity or classification of the message (for example, error, warning, or informational).
• MESSAGE_NAME_ENCODED — The encoded message name or text, used to retrieve the actual message content from the message dictionary.
• API_MODULE_ID — Foreign key to HR_API_MODULES, identifying the API module responsible for generating the message.

The surrogate primary key is defined as HR_H2PI_MESSAGE_LINES_PK, composed of FROM_ID, TABLE_NAME, and TO_BUSINESS_GROUP_ID. Together these form the business-key candidate that uniquely identifies each message line.

Common Use Cases and Queries

Typical scenarios include diagnosing H2PI transfer failures, auditing data movement between HR and Payroll, and reporting on message volume by request or business group. A common query pattern joins this table to HR_API_MODULES to resolve the API module responsible for each message.

• Retrieving all error-level messages for a given concurrent request: filter on REQUEST_ID and MESSAGE_LEVEL.
• Auditing messages for a specific source record: filter on FROM_ID and TABLE_NAME.
• Reporting message counts by business group and API module using GROUP BY on TO_BUSINESS_GROUP_ID and API_MODULE_ID.

Sample SQL: SELECT m.FROM_ID, m.TABLE_NAME, m.MESSAGE_LEVEL, a.MODULE_NAME FROM HR.HR_H2PI_MESSAGE_LINES m, HR.HR_API_MODULES a WHERE m.API_MODULE_ID = a.API_MODULE_ID AND m.REQUEST_ID = :req_id;

Related Objects

The most significant related object is HR_API_MODULES, referenced through the API_MODULE_ID foreign key. This join provides the module name and context for each message. Because the table is classified as standalone, its relationships are limited, but it is functionally tied to concurrent request diagnostics and the H2PI process, and it shares the FROM_ID/TABLE_NAME pattern with the payroll interface messaging framework. Administrators should treat it alongside HR_API_MODULES when investigating H2PI errors.