Search Results reason_description




Overview
Explore in AI Chat

Overview

The PA_RELATED_ITEMS_V view, owned by the APPS schema, is a reporting and integration object within the Oracle E-Business Suite Projects (PA) module. Its documented purpose is to provide all the control items which can be included, presenting a denormalized, security-filtered projection of the PA_CONTROL_ITEMS table joined to the surrounding project, status, owner, and classification reference data. In EBS 12.1.1 and 12.2.2 the view is delivered with a status of VALID and serves as the standard access point for control item data used in project issue tracking and change management reporting.

The view is a critical integration surface because it enforces row-level security at the database level through the PA_CI_SECURITY_PKG.CHECK_VIEW_ACCESS function, which is invoked in the WHERE clause. Only control items the current user is authorized to view are returned, making the view safe to expose to project managers and other business users without granting direct access to the base tables.

Underlying Base Objects

The view is defined over eight referenced base objects. The primary table is PA_CONTROL_ITEMS (aliased CI), which holds the control item records themselves. PA_CI_TYPES_B and PA_CI_TYPES_TL supply the control item type definition and its translated name and short name; the TL table is joined with LANGUAGE = USERENV('LANG') to return the session-appropriate translation. PA_PROJECTS_ALL provides the owning project, and PA_PROJECT_STATUSES supplies the status description where STATUS_TYPE is restricted to CONTROL_ITEM. HZ_PARTIES resolves the owner party name, PA_CLASS_CODES is outer-joined twice to supply the reason and classification descriptions, and the PA_CI_SECURITY_PKG package applies the access check. The PA_PROJ_ELEMENTS_UTILS package is used in a DECODE expression to resolve the element name for the OBJECT_ID.

Key Columns

The view exposes twenty-one columns. PROJECT_ID and PROJECT_NAME_NUMBER identify the project, the latter formatted as name concatenated with segment1 in parentheses. CI_ID, CI_NUMBER, and CI_TYPE_ID identify the control item and its type. SUMMARY carries the item text. STATUS_CODE, STATUS_DESCRIPTION, and SYSTEM_STATUS_CODE convey workflow and system status. OWNER_ID and OWNER_NAME identify the responsible party. OBJECT_TYPE, OBJECT_ID, and OBJECT_NAME link the item to a project element. CLASSIFICATION_DESCRIPTION and REASON_DESCRIPTION are the class code descriptions, and REASON_DESCRIPTION is of particular interest to users searching on reason_description, since it is the only column in this view carrying the reason text. CI_TYPE_CLASS_CODE, CI_TYPE_NAME, CI_TYPE_SHORT_NAME, CI_TYPE_NUMBER, and RECORD_VERSION_NUMBER complete the type and concurrency metadata.

Common Use Cases and Queries

Typical use cases include control item registers by project, aging and status reports, and reason-code analysis. The following query lists open control items with their reason descriptions:

  • SELECT project_name_number, ci_number, ci_type_name, status_description, reason_description FROM apps.pa_related_items_v WHERE status_code <> 'CLOSED' ORDER BY project_id, ci_number;
  • SELECT reason_description, COUNT(*) FROM apps.pa_related_items_v GROUP BY reason_description ORDER BY 2 DESC;
  • SELECT ci_id, ci_number, ci_type_number, owner_name FROM apps.pa_related_items_v WHERE project_id = :project_id;

Because REASON_DESCRIPTION is derived from an outer join to PA_CLASS_CODES, it may be null when no reason code is assigned. Queries intended for BI Publisher or OAF integration should filter on CI_TYPE_CLASS_CODE where only specific control item classes are required, and must account for the implicit security predicate already applied by the view.