Search Results handle_project_status_change

Overview

PA_PROJECT_STUS_UTILS is a PL/SQL package body in the APPS schema that centralizes the business logic governing project status transitions within Oracle Projects. It acts as the validation and orchestration layer between the user interface, the workflow engine, and the project status data model. The package determines which statuses a project may move to, whether a status change or deletion is permissible, whether a project is considered closed or in a purge state, and whether a Workflow process must be initiated as part of the transition.

The package is classified under the OTHER API category in the ETRM documentation and carries a VALID status in both 12.1.1 and 12.2.2. It is a heavily reused utility: the metadata records that it is referenced by 29 other database objects and that it references 16 documented procedures and functions internally. This dependency density confirms its role as a foundational component of the Oracle Projects status framework rather than a standalone user-facing API.

Key Procedures and Functions

The documented procedures and functions address four functional areas: status queries, transition validation, workflow coordination, and maintenance of the allowed-next-status configuration.

• Status query functions: IS_PROJECT_CLOSED, IS_PROJECT_STATUS_CLOSED, IS_ARPR_PROJECT_STATUS_CLOSED, and IS_PROJECT_IN_PURGE_STATUS evaluate whether a project or a given status represents a closed or purge-eligible condition. These are consumed by other packages that must branch behavior based on project lifecycle state.
• Transition handling: HANDLE_PROJECT_STATUS_CHANGE performs the core status change operation. IS_STARTING_STATUS and GET_DEFAULT_STARTING_STATUS identify and resolve the initial status assigned to a project. ALLOW_STATUS_CHANGE determines whether a proposed transition is legal.
• Workflow integration: CHECK_WF_ENABLED verifies whether Workflow is active for the project status process, and GET_WF_SUCCESS_FAILURE_STATUS resolves the outcome statuses used by the workflow. These support the WF_CORE dependency.
• Configuration maintenance: ALLOW_STATUS_DELETION, DELETE_FROM_NEXT_STATUS, and INSERT_INTO_NEXT_STATUS manage the allowed-next-status matrix. CHECK_STATUS_NAME_OR_CODE, CHECK_DELETE_PHASE_OK, and IS_STATUS_USED_IN_PROJ_TYPE enforce referential and name/code uniqueness rules before a status may be modified or removed.

Tables Accessed

The package operates against the core Oracle Projects status tables, accessed through APPS synonyms:

• PA_PROJECTS_ALL — the project master, read to establish the current status and project attributes subject to transition.
• PA_PROJECT_STATUSES — the definition of valid statuses, including closed and starting flags.
• PA_NEXT_ALLOW_STATUSES — the matrix defining which status-to-status transitions are permitted; written by INSERT_INTO_NEXT_STATUS and DELETE_FROM_NEXT_STATUS.
• PA_PROJECT_TYPES and PA_PROJECT_TYPES_ALL — project type definitions used to validate status usage per type.
• PA_PROJ_ELEMENTS — project elements consulted during phase and status validation.
• DUAL and PLITBLM — utility references for scalar evaluation and PL/SQL table handling.

Usage Notes

PA_PROJECT_STUS_UTILS is invoked primarily from the Oracle Projects forms layer when a user initiates a project status change, and from concurrent programs and workflow activities that automate lifecycle transitions. Because the package is referenced by 29 other objects, customizations should treat it as an internal dependency rather than a documented public API; direct calls are appropriate only where the calling code already participates in the project status framework. Extensions to status behavior should be implemented through PA_CLIENT_EXTN_PROJ_STATUS, the client extension hook the package depends on, which allows site-specific logic to be injected without modifying the seeded utility itself. The package supports both 12.1.1 and 12.2.2 releases without documented signature changes.