Results for “pa_control_items_u1”

12 results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PA.PA_CONTROL_ITEMS is a transactional table in the Oracle Projects (PA) schema that stores user-defined control items associated with projects. A control item represents a tracked issue, action, change, or risk record that project managers raise, monitor, and resolve throughout the project lifecycle. Each row belongs to a control item type identified by CI_TYPE_ID, which determines the classification behavior, numbering, and processing rules applied to the item. Control items are further categorized through classification, reason, priority, and effort level attributes, and they carry workflow-style status, resolution, and ownership information.

The ETRM documentation classifies this table as a satellite-leaning structure from a Data Vault modeling perspective. This is a heuristic suggestion rather than a physical design statement: PA_CONTROL_ITEMS behaves as a descriptive satellite attached to project and control-item-type hubs, with CI_ID acting as the durable business key carried across related transactional tables. Its 69 columns include extensive descriptive and audit attributes typical of a satellite, while foreign keys to PA_PROJECTS_ALL and PA_CI_TYPES_B provide the hub references.

Key Information Stored

The surrogate primary key is CI_ID, enforced by PA_CONTROL_ITEMS_PK and by the unique index PA_CONTROL_ITEMS_U1. CI_ID is the system-generated number displayed in the user interface as the System Number. A second unique index, PA_CONTROL_ITEMS_U2, covers the combination of ORIGINAL_CI_ID and VERSION_NUMBER, which supports the versioning model applied to control items; CURRENT_VERSION_FLAG identifies the active version of a versioned chain.

The most significant columns include:

Common Use Cases and Queries

Control items are central to project issue and change management reporting. Typical queries retrieve open items by project, owner, priority, or required date. A representative pattern joins PA_CONTROL_ITEMS to PA_PROJECTS_ALL for project context and filters on STATUS_CODE or CURRENT_VERSION_FLAG:

  • Open control item listing: SELECT ci.ci_number, ci.summary, ci.status_code, ci.date_required FROM pa.pa_control_items ci WHERE ci.project_id = :project_id AND ci.current_version_flag = 'Y' ORDER BY ci.date_required;
  • Workload by owner: aggregate CI_ID counts grouped by OWNER_ID and CI_TYPE_ID, joining HZ_PARTIES for party names.
  • Overdue analysis: compare DATE_REQUIRED against the current date where DATE_CLOSED is null.
  • Status and progress tracking across types: use the PA_CONTROL_ITEMS_N3 and PA_CONTROL_ITEMS_N11 index columns (STATUS_CODE, CI_TYPE_ID, OWNER_ID) to drive efficient filtering.
  • Financial exposure: sum PRICE grouped by PRICE_CURRENCY_CODE for items tied to funding records.

Because the table carries versioning columns, reports should generally filter on CURRENT_VERSION_FLAG to avoid double-counting historical versions.

Related Objects

PA_CONTROL_ITEMS sits at the center of a set of child transactional tables, each joined on CI_ID:

Parent lookups include PA_PROJECTS_ALL (PROJECT_ID), PA_CI_TYPES_B (CI_TYPE_ID), PA_PROJECT_STATUSES (STATUS_CODE, PROGRESS_STATUS_CODE), HZ_PARTIES (OWNER_ID, LAST_MODIFIED_BY_ID, CLOSED_BY_ID), and FND_CURRENCIES (PRICE_CURRENCY_CODE). These relationships define both the referential integrity and the principal join paths used in project control reporting.