Results for “status_list_id”

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

Overview

PA_STATUS_LIST_ITEMS is a Projects (PA) module table in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the individual statuses that belong to a status list. In Oracle Projects, a status list is a reusable grouping of project or project-related statuses that controls which lifecycle states are available to a given context, such as a project type, project template, or a specific business flow. Each row in PA_STATUS_LIST_ITEMS represents one status entry (one list item) within a parent status list defined in PA_STATUS_LISTS.

The table therefore acts as the intersection between two master entities: the status list (PA_STATUS_LISTS) and the status definition (PA_PROJECT_STATUSES). Under a heuristic Data Vault classification mined from the foreign-key structure, this object is best modeled as a link table, since its core purpose is to associate statuses with status lists rather than to hold descriptive attributes of a single business entity.

Key Information Stored

The documented physical schema contains 10 columns. The most significant are:

• STATUS_LIST_ITEM_ID — the surrogate primary key for the row, enforced by the PSM_PK constraint.
• STATUS_LIST_ID — the parent status list this item belongs to; a business-key candidate forming part of the unique index PA_STATUS_LIST_ITEMS_U2 alongside PROJECT_STATUS_CODE.
• PROJECT_STATUS_CODE — the status code assigned to this list item, joining to PA_PROJECT_STATUSES.
• RECORD_VERSION_NUMBER — optimistic locking / concurrency control for the row, supporting Oracle Projects' multi-user update model.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard EBS WHO columns capturing audit and user context.
• ZD_EDITION_NAME — the editioning column used by Online Patching (adopted in 12.2.x), which participates in both unique indexes.

Two unique indexes are documented. PA_STATUS_LIST_ITEMS_U1 covers (STATUS_LIST_ITEM_ID, ZD_EDITION_NAME), confirming the surrogate key. PA_STATUS_LIST_ITEMS_U2 covers (STATUS_LIST_ID, PROJECT_STATUS_CODE, ZD_EDITION_NAME), which is the true business-key candidate: it prevents the same status from being added twice to the same status list. The metadata also references additional status columns (PPE_PROJECT_STATUS_CODE, PPE_PROJECT_STATUS_CODE2, PPE_PROJECT_STATUS_CODE3) in the foreign-key listing, indicating support for multiple associated status slots per item.

Common Use Cases and Queries

Typical usage centers on resolving which statuses are valid for a given list. To enumerate the statuses in a list:

• SELECT i.STATUS_LIST_ITEM_ID, i.PROJECT_STATUS_CODE, s.STATUS_NAME FROM pa_status_list_items i JOIN pa_project_statuses s ON s.PROJECT_STATUS_CODE = i.PROJECT_STATUS_CODE WHERE i.STATUS_LIST_ID = :list_id.
• Reverse lookup — find which status lists contain a status: query by PROJECT_STATUS_CODE against PA_STATUS_LISTS.
• Referential integrity audits — detect orphaned items or duplicate statuses violating the intent of U2.
• Reporting and extensions — reporting tools and custom PL/SQL frequently query this table to drive status-selection lists (LOVs) in project entry forms.

Note that in 12.2.x queries should account for ZD_EDITION_NAME to avoid returning superseded edition rows after online patching.

Related Objects

• PA_STATUS_LISTS — parent header; join via STATUS_LIST_ID (and PST_STATUS_LIST_ID).
• PA_PROJECT_STATUSES — status master; join via PROJECT_STATUS_CODE and the PPE_PROJECT_STATUS_CODE(n) columns.
• PA_PROJECTS / PA_PROJECT_TYPES — consume status lists to constrain valid project statuses.
• PA_STATUS_LIST_ITEMS_U1 / U2 — unique indexes enforcing key and business-key uniqueness.
• PSM_PK — primary-key constraint on STATUS_LIST_ITEM_ID.
• Oracle Projects public APIs and concurrent programs that validate project status transitions rely indirectly on these list memberships.