Search Results pa_status_list_items_u2

Overview

PA.PA_STATUS_LIST_ITEMS is a transactional configuration table in the Oracle E-Business Suite Projects (PA) schema that stores the individual statuses belonging to a project status list. Status lists drive the allowed lifecycle transitions of projects, and each row in this table defines one permitted status within a given list. In EBS 12.1.1 and 12.2.2 the table resides in the APPS_TS_TX_DATA tablespace, is registered as FND Design Data PA.PA_STATUS_LIST_ITEMS, and carries a VALID status in the ETRM repository.

The mined relationship data classifies this object heuristically as a link table. That modeling suggestion is consistent with its structure: it resolves the many-to-many association between status lists and project statuses, carrying a surrogate key plus two unique business keys rather than acting as a pure hub or a descriptive satellite. Under the 12.2.2 physical schema the table exposes ten columns, including the Online Patching edition column ZD_EDITION_NAME.

Key Information Stored

The most significant columns are:

• STATUS_LIST_ITEM_ID – Surrogate primary key of the row (PK constraint PSM_PK), defined as NUMBER(15). It uniquely identifies each status entry within a list.
• STATUS_LIST_ID – Foreign key to PA_STATUS_LISTS identifying the parent status list. Mandatory.
• PROJECT_STATUS_CODE – VARCHAR2(30) user status code attached to the list; the descriptive business value of the row.
• RECORD_VERSION_NUMBER – NUMBER(15) used for optimistic record locking in self-service (OA Framework) applications.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN – Standard Who audit columns maintained by EBS.
• ZD_EDITION_NAME – Editioning column supporting Online Patching in 12.2.x.

Two unique indexes define the business keys. PA_STATUS_LIST_ITEMS_U1 is unique on (STATUS_LIST_ITEM_ID, ZD_EDITION_NAME), and PA_STATUS_LIST_ITEMS_U2 — the index referenced in the user's search — is unique on (STATUS_LIST_ID, PROJECT_STATUS_CODE, ZD_EDITION_NAME). U2 enforces the rule that a given status code may appear only once per status list, and is the natural lookup path for existence checks and deduplication. Both indexes reside in APPS_TS_TX_IDX.

Common Use Cases and Queries

Typical scenarios include validating whether a status is allowed on a list, listing the statuses available for a project lifecycle, and reporting on status list configuration.

• Resolve statuses for a list: SELECT PROJECT_STATUS_CODE FROM PA.PA_STATUS_LIST_ITEMS WHERE STATUS_LIST_ID = :p_list_id;
• Existence check via U2: SELECT 1 FROM PA.PA_STATUS_LIST_ITEMS WHERE STATUS_LIST_ID = :p_list_id AND PROJECT_STATUS_CODE = :p_code;
• Join to the status list header: SELECT i.PROJECT_STATUS_CODE, l.NAME FROM PA.PA_STATUS_LIST_ITEMS i, PA.PA_STATUS_LISTS l WHERE i.STATUS_LIST_ID = l.STATUS_LIST_ID;
• Audit recent configuration changes using LAST_UPDATE_DATE and LAST_UPDATED_BY.

Because the table is edition-enabled, queries in 12.2.x should either run through the APPS synonym (which filters the edition) or explicitly account for ZD_EDITION_NAME.

Related Objects

• PA.PA_STATUS_LISTS – Parent header table; joined via STATUS_LIST_ID.
• PA.PA_PROJECT_STATUSES – Status code master; the table's FK metadata references PPE_PROJECT_STATUS_CODE, PPE_PROJECT_STATUS_CODE2, and PPE_PROJECT_STATUS_CODE3 against this object, joining on the status code.
• APPS.PA_STATUS_LIST_ITEMS – Synonym layer through which applications and reports access the base table.
• PA_STATUS_LIST_ITEMS_U2 – Unique index enforcing STATUS_LIST_ID plus PROJECT_STATUS_CODE, the primary lookup path.

The table does not reference any object outside the PA schema and is referenced primarily through the APPS synonym and the two unique indexes documented above.