Search Results hr_comments_pk

Overview

HR.HR_COMMENTS is a central repository within the Oracle HRMS (HR) schema that stores comments associated with various HRMS business objects. Its principal design objective is to provide a consolidated, space-efficient home for comments that do not require DateTrack (date-effective) treatment. By externalizing non-datetracked commentary into a single table, the application avoids duplicating the comment column across multiple transactional tables. A secondary and equally important benefit is that this centralized design facilitates the use of PL/SQL stored procedures for date-effective updates, since comment text is decoupled from the row being date-tracked.

The object is documented as VALID in ETRM for Oracle EBS 12.1.1 and 12.2.2, owned by the HR schema, with FND Design Data reference PER.HR_COMMENTS. It resides in the APPS_TS_TX_DATA tablespace with PCT FREE 10. The primary key is HR_COMMENTS_PK on the COMMENT_ID column, and the corresponding unique index is stored in the APPS_TS_TX_IDX tablespace. A LOB segment (SYS_IL0000043571C00008$$) is also documented for the CLOB payload. From a heuristic Data Vault modeling perspective, the table is classified as standalone, which suggests treating HR_COMMENTS as a satellite-style structure keyed by its surrogate COMMENT_ID, without a documented foreign-key-defined hub or link relationship to other vault entities.

Key Information Stored

The table contains eight documented columns. The most significant include:

• COMMENT_ID — A NUMBER(15) system-generated primary key, uniquely identifying each comment. This is the surrogate key and the sole column of the unique index HR_COMMENTS_PK; no separate business-key candidate is documented.
• SOURCE_TABLE_NAME — A VARCHAR2(30) column naming the table that holds the related object for the comment. This is the principal linkage mechanism, allowing a single comment store to serve many HRMS entities without a formal foreign key.
• COMMENT_TEXT — A CLOB(4000) column holding the actual comment text. It is the LOB that drives the documented SYS_IL index segment.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard Who columns providing audit and concurrency information.

Because the comment is keyed only by COMMENT_ID and qualified by SOURCE_TABLE_NAME, consumers must resolve the related business object themselves using the source table name and an application-specific identifier.

Common Use Cases and Queries

Typical uses include retrieving commentary for a specific HRMS entity, auditing who added or changed comments, and reporting on comment volume by source table. A basic retrieval query follows the documented Query Text:

• SELECT COMMENT_ID, SOURCE_TABLE_NAME, COMMENT_TEXT, CREATED_BY, CREATION_DATE FROM HR.HR_COMMENTS WHERE SOURCE_TABLE_NAME = :p_table;
• Audit reporting: join CREATED_BY and LAST_UPDATED_BY to FND_USER to identify authors.
• Volume analysis: SELECT SOURCE_TABLE_NAME, COUNT(*) FROM HR.HR_COMMENTS GROUP BY SOURCE_TABLE_NAME;
• Change tracking: filter on LAST_UPDATE_DATE for incremental extracts into a reporting store or data warehouse.

Because COMMENT_TEXT is a CLOB, reporting tools must handle large-object semantics; DBMS_LOB.SUBSTR or SUBSTR on the CLOB is commonly used when only a truncated preview is required.

Related Objects

The ETRM metadata states that HR.HR_COMMENTS does not reference any database object, so there are no outbound foreign keys. It is referenced by HR_COMMENTS# (the underlying base table object). Relationships to the wider HRMS model are therefore logical rather than enforced, driven by the SOURCE_TABLE_NAME value. Significant related objects include:

• HR.HR_COMMENTS# — The base table underlying the synonym/view referenced in the dependency list.
• PER.HR_COMMENTS — The FND Design Data definition associated with this table.
• FND_USER — Joined via CREATED_BY / LAST_UPDATED_BY for author attribution.
• FND_LOGINS — Joined via LAST_UPDATE_LOGIN for session-level auditing.
• Source tables named in SOURCE_TABLE_NAME — resolved dynamically at application level rather than by FK.

Because referential integrity is application-managed, any integration or reporting solution must validate SOURCE_TABLE_NAME values against the actual HRMS entities in use.