Search Results pa_ci_list_v




Overview
Explore in AI Chat

Overview

PA_CI_LIST_V is an APPS-owned database view in the Oracle E-Business Suite Projects (PA) module that consolidates project control item (issue) records into a single, denormalized reporting structure. Control items represent project-related issues, action items, change requests, and similar tracked events that require ownership, classification, priority assignment, and resolution. The view joins the core control item transaction table PA_CONTROL_ITEMS to project, status, lookup, currency, and party reference data, and applies the security rules defined in PA_CI_SECURED_V. Its principal role is to serve as the read-only data source behind Oracle Projects control item inquiry screens, Oracle Answers/Daily Business Intelligence (OBIEE) reporting, and custom integrations that must enforce the same security and lookup semantics as the standard application. Because the view resolves foreign keys to their descriptive meanings in-line, it eliminates the need for downstream queries to re-implement the extensive decode logic that Oracle embeds in the underlying form.

Underlying Base Objects

The documented base objects span three functional groupings. Transactional data originates from PA_CONTROL_ITEMS (a synonym), which supplies the CI_ID, CI_NUMBER, summary, description, status, owner, price, source, and date attributes, together with PA_CI_TYPES_B and PA_CI_TYPES_TL for control item type code, class, and translated name/short name. Reference and validation data comes from PA_PROJECTS_ALL, PA_PROJECT_STATUSES, PA_CLASS_CODES, and PA_LOOKUPS/FND_LOOKUPS, which provide project name and number, the project and progress status names and system status codes, resolution and reason class codes, priority and effort-level meanings, and source type meanings. Security and derived data are handled by PA_CI_SECURED_V in conjunction with PA_CI_SECURITY_PKG and FND_GLOBAL, which restrict rows and resolve the current user's security profile; PA_FP_CI_AMOUNTS_V supplies financial-plan amounts; PA_PROJ_ELEMENTS_UTILS resolves the referenced WBS object to its element name, number, and concatenated identifier via GET_ELEMENT_NAME, GET_ELEMENT_NUMBER, and GET_ELEMENT_NAME_NUMBER; HZ_PARTIES resolves owner and closer identities to party names; and FND_CURRENCIES_VL provides currency descriptions. The view therefore layers security enforcement, multi-table decoding, and package-driven derivation over a single control item row.

Key Columns

Common Use Cases and Queries

The view supports open-item aging reports, ownership dashboards, resolution and root-cause analysis, and integration extracts. Because security is inherited from PA_CI_SECURED_V, queries automatically return only items visible to the connected user. A typical open control item listing is:

SELECT ci_number, name, summary, party_name AS owner,
       meaning AS priority, date_required,
       TRUNC(date_required) - TRUNC(SYSDATE) AS days_remaining
FROM   apps.pa_ci_list_v
WHERE  status_code NOT IN ('CI_CLOSED','CI_CANCELED')
AND    project_id = :p_project_id
ORDER BY date_required;

For resolution trend analysis:

SELECT TRUNC(date_closed,'MM') AS closed_month,
       resolution_class_code, COUNT(*) AS item_count
FROM   apps.pa_ci_list_v
WHERE  status_code = 'CI_CLOSED'
AND    date_closed >= ADD_MONTHS(SYSDATE,-12)
GROUP BY TRUNC(date_closed,'MM'), resolution_class_code
ORDER BY 1,2;

Because several columns are resolved through packages and secured views, performance-sensitive extracts should restrict PROJECT_ID and STATUS_CODE predicates, and callers should avoid wrapping the view in further scalar decodes already performed here.