Search Results ci_action_open

Overview

APPS.PA_CI_COMMENTS_V is a reporting view within the Oracle E-Business Suite Projects (PA) module that consolidates comment and action records associated with project control items. The view is constructed as a UNION ALL of two SELECT statements: the first returns comments that are linked to a specific control item action, while the second returns comments that exist independently of any action. This design allows the view to present a unified, chronological picture of all commentary activity on a control item, whether that commentary is tied to a formal action or stands alone.

In Oracle EBS 12.1.1 and 12.2.2, the view is owned by APPS and is referenced by applications such as Project Management and issue-tracking functionality that rely on control items to coordinate change, issue, or action workflows. The view is not a base table and holds no data of its own; it is a read-only projection intended for querying and reporting. A notable element of the definition is the use of the PA_CI_ACTIONS_UTIL.action_with_reply function within a DECODE expression, which determines the child_exists flag. The view also exposes derived attributes such as comment author names, assignee and reassignee names, action type meanings, and the associated project identifier.

Underlying Base Objects

The view is defined over the following documented base objects:

• PA_CI_COMMENTS (synonym) — the primary source of comment records, supplying comment text, type code, comment identifier, and creation metadata.
• PA_CI_ACTIONS (synonym) — joined twice: once as the primary action (a) and once as a self-join (a2) through source_ci_action_id to expose reassignment relationships.
• PA_CONTROL_ITEMS (synonym) — supplies the project_id association for each control item.
• HZ_PARTIES (synonym) — joined multiple times to resolve party names for the comment author (commentor), the assignee, and the reassignee, restricted to party_type = 'PERSON'.
• PA_LOOKUPS (view) — provides the translated action_type meaning from lookup type PA_CI_ACTION_TYPES.
• PA_UTILS (package) — used via PA_UTILS.get_party_id to map the creating user to a party identifier.
• PA_CI_ACTIONS_UTIL (package) — invoked through action_with_reply to compute the child_exists indicator.

Key Columns

• ci_comment_id — unique identifier of the comment record.
• ci_id — the control item to which the comment belongs.
• ci_action_id — the action associated with the comment; null in the second UNION branch where comments stand alone.
• comment_text — the actual body of the comment.
• message_type_code / type_code — classification of the comment (for example, REQUESTOR).
• action_type / action_type_code — the decoded action type meaning and its underlying code.
• status_code — action status; the literal CI_ACTION_OPEN is significant to the child_exists calculation.
• comment_created_by_name, assignee_name, reassignee_name, requestor_name — party names resolved from HZ_PARTIES.
• child_exists — Y/N flag indicating whether a requestor comment on an open action has a reply.
• project_id — the project associated with the control item.
• reassign_ci_action_id — identifier of the action to which the item was reassigned.

Common Use Cases and Queries

A frequent requirement is locating open actions that still await a requestor response, which is precisely the condition encoded in the child_exists logic. The following query returns comments tied to open actions:

SELECT ci_comment_id, ci_id, ci_action_id, comment_text, action_type, status_code
FROM apps.pa_ci_comments_v
WHERE status_code = 'CI_ACTION_OPEN'
AND message_type_code = 'REQUESTOR';

To review the full comment history for a given control item, filtering by ci_id produces a unified timeline across both UNION branches. Reporting on reassignment activity is supported through reassign_ci_action_id and reassignee_name, while project-level rollups can aggregate on project_id. Because the view calls PA_CI_ACTIONS_UTIL.action_with_reply and PA_UTILS.get_party_id, queries are best scoped with filters to limit row volume and avoid unnecessary function execution across large result sets.