Search Results all_except_closed

Overview

APPS.PA_PROJ_STATUSES_V is a project status enumeration view in Oracle EBS 12.1.1 and 12.2.2 that consolidates two distinct categories of status values used throughout the Projects (PA) module. The view presents the full set of configurable project statuses stored in the transactional status table alongside the two synthetic system-level status codes maintained as lookup values. Its primary role is to provide a single, uniform source of status codes and descriptions for reporting, concurrent programs, workflow-driven status transitions, and integrations that must select or validate project statuses without querying separate sources.

The name of the view reflects its consumer-facing function: it answers the question "what status values can a project carry, and which of them are real user-defined statuses versus control flags?" Because the view unions the status master with lookup definitions, it guarantees that system-level placeholders such as ALL and ALL_EXCEPT_CLOSED are visible in the same result set as business statuses.

Underlying Base Objects

The view is defined over two documented base objects: PA_PROJECT_STATUSES and PA_LOOKUPS.

• PA_PROJECT_STATUSES — contributes all rows where STATUS_TYPE = 'PROJECT'. These are the business-meaningful statuses maintained by the application, including flags controlling whether a status is predefined, a starting status, and whether workflow is enabled.
• PA_LOOKUPS — filtered by LOOKUP_TYPE = 'PROJECT STATUS' and LOOKUP_CODE in ('ALL','ALL_EXCEPT_CLOSED'). These rows are mapped into the same column shape as the status rows, with STARTING_STATUS_FLAG and ENABLE_WF_FLAG hard-coded to 'N' and workflow and icon columns set to NULL.

The union of these two sets means every row of the view carries STATUS_TYPE = 'PROJECT'. The documented metadata identifies PA_LOOKUPS as a view and PA_PROJECT_STATUSES as a synonym in the APPS schema, so consumers should be aware that the underlying structure is itself layered.

Key Columns

• PROJECT_STATUS_CODE / PROJECT_STATUS_NAME — the status identifier and its display name.
• PROJECT_SYSTEM_STATUS_CODE — the system-level code; for lookup-derived rows this equals the lookup code (for example ALL_EXCEPT_CLOSED).
• DESCRIPTION — explanatory text for the status.
• START_DATE_ACTIVE / END_DATE_ACTIVE — the effective period during which the status may be assigned.
• PREDEFINED_FLAG — indicates whether the status shipped with the application.
• STARTING_STATUS_FLAG — marks statuses valid as a project's initial state.
• ENABLE_WF_FLAG, WORKFLOW_ITEM_TYPE, WORKFLOW_PROCESS, WF_SUCCESS_STATUS_CODE, WF_FAILURE_STATUS_CODE — workflow integration attributes.
• STATUS_TYPE — always 'PROJECT' in this view.
• NEXT_ALLOWABLE_STATUS_FLAG — relates to permitted status transitions.
• STATUS_ICON_IND, STATUS_ICON_ACTIVE_IND, PROJECT_STATUS_WEIGHT — presentation and ordering attributes.
• ATTRIBUTE1..15 — descriptive flexfield storage.

Common Use Cases and Queries

The most frequent use is enumerating statuses in reporting or LOV definitions. The literal value ALL_EXCEPT_CLOSED that prompted this lookup is a system pseudo-status used by program parameters and queries to mean "every status except a closed one":

SELECT project_status_code, project_status_name
FROM   apps.pa_proj_statuses_v
WHERE  project_status_code = 'ALL_EXCEPT_CLOSED';

Validating an assignment target requires checking activity dates and starting behavior:

SELECT project_status_code, project_status_name, starting_status_flag
FROM   apps.pa_proj_statuses_v
WHERE  TRUNC(SYSDATE) BETWEEN NVL(start_date_active, SYSDATE)
                          AND NVL(end_date_active, SYSDATE);

Workflow-enabled transitions are inspected via:

SELECT project_status_code, workflow_item_type, wf_success_status_code
FROM   apps.pa_proj_statuses_v
WHERE  enable_wf_flag = 'Y';

Developers should remember that APPS.PA_PROJ_STATUSES_V is a read-only construct; inserts, updates and deletes against business statuses must be performed on the base status table, never through the view.