Results for “change_comment”

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

Overview

PA_OBJ_STATUS_CHANGES is a Projects (PA) module table in Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2, that records the changes of the status of the objects. It functions as an audit and history repository, capturing each transition of a project-related object from an old status to a new status. Rather than storing the current status alone, the table persists the sequence of status movements so that downstream processes, reporting, and reconciliation logic can reconstruct how an object arrived at its present state.

From a Data Vault modeling perspective, the mined foreign-key structure suggests a satellite-leaning classification. The table carries a single foreign key to PA_CONTROL_ITEMS, which behaves like the parent or hub reference, while the descriptive attributes capture changing status values over time. This pattern is characteristic of a satellite attached to a business key rather than an independent hub or a pure link table.

Key Information Stored

The table is owned by the PA schema and contains 14 documented columns. The most significant are:

• OBJ_STATUS_CHANGE_ID — the surrogate primary key (POE_PK) and the first column of unique index PA_OBJ_STATUS_CHANGES_U1, uniquely identifying each status change event.
• OBJECT_TYPE and OBJECT_ID — together identify the object whose status changed; both participate in unique index PA_OBJ_STATUS_CHANGES_U2.
• STATUS_TYPE — classifies the nature of the status being tracked; also part of the U2 business-key candidate.
• NEW_PROJECT_STATUS_CODE — the status value after the change; the final component of unique index PA_OBJ_STATUS_CHANGES_U2.
• OLD_PROJECT_STATUS_CODE — the status value prior to the change, enabling before/after comparison.
• NEW_PROJECT_SYSTEM_STATUS_CODE and OLD_PROJECT_SYSTEM_STATUS_CODE — system-level counterparts to the user-facing status codes.
• CHANGE_COMMENT — free-text annotation describing the reason or context of the change.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard Oracle EBS audit columns capturing who created and last modified each row. LAST_UPDATE_DATE is part of unique index U2, confirming its role as a temporal discriminator.

The surrogate primary key (OBJ_STATUS_CHANGE_ID) is distinct from the documented business-key candidates, which combine object identity, status type, and date to guarantee uniqueness of a meaningful event.

Common Use Cases and Queries

Typical uses include status-transition auditing, project lifecycle reporting, and troubleshooting unexpected status values. A representative query retrieving the full change history for a given object is:

• SELECT OBJECT_TYPE, OBJECT_ID, OLD_PROJECT_STATUS_CODE, NEW_PROJECT_STATUS_CODE, CHANGE_COMMENT, LAST_UPDATE_DATE FROM PA_OBJ_STATUS_CHANGES WHERE OBJECT_ID = :p_object_id ORDER BY LAST_UPDATE_DATE;
• Reconciling a control item against its status history by joining on PCM_CI_ID to PA_CONTROL_ITEMS.
• Reporting the number of transitions per STATUS_TYPE within a date range, useful for lifecycle analytics.
• Identifying objects that reached a terminal or blocked status by filtering on NEW_PROJECT_STATUS_CODE and NEW_PROJECT_SYSTEM_STATUS_CODE.

Related Objects

• PA_CONTROL_ITEMS — referenced through foreign key PA_OBJ_STATUS_CHANGES.PCM_CI_ID, the primary parent linkage for this satellite.
• PA_PROJECTS_ALL — the principal project master, commonly joined via OBJECT_ID when OBJECT_TYPE denotes a project.
• PA_PROJECT_STATUSES — supplies the valid project status codes used by NEW_PROJECT_STATUS_CODE and OLD_PROJECT_STATUS_CODE.
• PA_PROJECT_STATUS_HISTORY — a related history construct for project-level status tracking distinct from generic object changes.
• PA_OBJECT_STATUS_PUB — the public API layer that typically drives inserts into this table during status transitions.