Search Results pa_status_lists_u2




Overview
Explore in AI Chat

Overview

PA.PA_STATUS_LISTS is a foundational Oracle Projects reference table that stores the attributes of a project status list. Each row defines a named, dated container of statuses that governs how Oracle Projects and its sub-ledgers track the lifecycle of projects, tasks, and related objects. The table resides in the PA schema, is owned within the APPS design data group (PA.PA_STATUS_LISTS), and is stored in the APPS_TS_TX_DATA tablespace with PCT Free 10. Its status is VALID in both Oracle EBS 12.1.1 and 12.2.2.

Because a status list acts as a reusable definition referenced by many transactional and configuration objects, the table behaves as a reference entity within the broader Projects model. Under the heuristic Data Vault classification supplied in the metadata, PA.PA_STATUS_LISTS is described as hub-leaning: it carries a stable surrogate key (STATUS_LIST_ID) that is referenced by multiple downstream tables, which is consistent with a hub pattern. Analysts modeling the Projects domain in a Data Vault should treat STATUS_LIST_ID as the hub business key candidate, with the descriptive and Who columns as satellite attributes.

Key Information Stored

The table contains fourteen documented columns. The most operationally significant are:

  • STATUS_LIST_ID — NUMBER(15), mandatory. The unique surrogate identifier of the status list and the primary key (documented as PST_PK in the physical schema).
  • STATUS_TYPE — VARCHAR2(30). Classifies the list (for example, by the entity the list applies to), and forms part of the business-key unique index.
  • NAME — VARCHAR2(150). The user-facing name of the status list; together with STATUS_TYPE it constitutes the business key.
  • DEFAULT_STARTING_STATUS_CODE — VARCHAR2(30). The default status returned in the list of values when a new item is created.
  • START_DATE_ACTIVE / END_DATE_ACTIVE — DATE. The effective window during which the list may be used; these drive the dated lookup behaviour in Projects forms.
  • DESCRIPTION — VARCHAR2(2000). Free-text description of the list.
  • RECORD_VERSION_NUMBER — NUMBER(15). Used for optimistic record locking in self-service applications.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard Who columns for audit and concurrent-program tracking.
  • ZD_EDITION_NAME — present only in the 12.2.2 documented physical schema; it supports edition-based redefinition and appears in both unique indexes.

The surrogate primary key is STATUS_LIST_ID. Two unique indexes represent business-key candidates: PA_STATUS_LISTS_U1 on (STATUS_LIST_ID, ZD_EDITION_NAME) — the guidance the user searched for, PA_STATUS_LISTS_U2 — and PA_STATUS_LISTS_U2 on (STATUS_TYPE, NAME, ZD_EDITION_NAME). A non-unique index, PA_STATUS_LISTS_N1, supports queries by (STATUS_TYPE, START_DATE_ACTIVE, END_DATE_ACTIVE).

Common Use Cases and Queries

Typical usage centres on resolving a status list identifier from its type and name, and enumerating the statuses it contains.

  • Resolve a list by business key: SELECT status_list_id, default_starting_status_code FROM pa.pa_status_lists WHERE status_type = :p_type AND name = :p_name AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE);
  • Populate LOVs for project and task definition screens, filtering on the active date window so only currently valid lists appear.
  • Audit recently modified configurations: SELECT name, status_type, last_update_date, last_updated_by FROM pa.pa_status_lists ORDER BY last_update_date DESC;
  • Reporting on the default starting status used per list, and cross-checking against PA_STATUS_LIST_ITEMS to confirm the default code is a member of the list.
  • Detecting records with missing or overlapping effective dates, which can cause a status list to be unexpectedly unavailable to end users.

Related Objects

The table is referenced by several Projects configuration tables through the STATUS_LIST_ID column:

  • PA.PA_STATUS_LIST_ITEMS — the child detail table holding individual status entries; joins on STATUS_LIST_ID (documented twice via PST_STATUS_LIST_ID and STATUS_LIST_ID relationships).
  • PA.PA_OBJ_STATUS_LISTS — maps status lists to Projects object types; joins on STATUS_LIST_ID.
  • APPS.PA_STATUS_LISTS — the APPS-layer synonym/view used by application code and reports.

No foreign keys are declared from PA_STATUS_LISTS to other database objects, so the table is self-contained at the top of its dependency chain.