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:
- CI_TYPE_ID — identifies the control item type from PA_CI_TYPES_B, driving numbering and behavior.
- SUMMARY — a user-defined short description (up to 240 characters) of the control item.
- PROJECT_ID — the owning project, referencing PA_PROJECTS_ALL.
- OBJECT_TYPE / OBJECT_ID — the generic reference to the project entity the item is attached to.
- CI_NUMBER — the user-facing control item number within its type.
- STATUS_CODE and PROGRESS_STATUS_CODE — workflow and progress states validated against PA_PROJECT_STATUSES.
- OWNER_ID — the party responsible for the item, referencing HZ_PARTIES.
- CLASSIFICATION_CODE_ID, REASON_CODE_ID, RESOLUTION_CODE_ID, PRIORITY_CODE, EFFORT_LEVEL_CODE — categorization attributes used for triage and reporting.
- DATE_REQUIRED, DATE_CLOSED, CLOSED_BY_ID — required and closure dates plus the closing party.
- DESCRIPTION, RESOLUTION, STATUS_OVERVIEW — long text fields holding detail and resolution narrative.
- PRICE, PRICE_CURRENCY_CODE — optional monetary impact, with currency validated against FND_CURRENCIES.
- CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_MODIFICATION_DATE, LAST_MODIFIED_BY_ID — standard audit columns and their denormalized party references.
- ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard Oracle EBS descriptive flexfield columns.
- ORIG_SYSTEM_CODE, ORIG_SYSTEM_REFERENCE, SOURCE_CI_ID — provenance columns supporting integration and item origination.
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:
- PA_CI_ACTIONS — actions recorded against a control item.
- PA_CI_COMMENTS — discussion and comment history.
- PA_CI_IMPACTS — impact assessments associated with the item.
- PA_CI_SUPPLIER_DETAILS — supplier-related detail lines.
- PA_BUDGET_VERSIONS — budget versions linked to control items.
- PA_PROJECT_FUNDINGS — funding records referencing the item, joined on CI_ID.
- PA_OBJECT_RELATIONSHIPS — relationships where the item appears as OBJECT_ID_FROM1 or OBJECT_ID_TO1.
- PA_OBJ_STATUS_CHANGES — status change history via PCM_CI_ID.
- PA_FP_MERGED_CTRL_ITEMS — merged control item references.
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.
-
TABLE: PA.PA_CONTROL_ITEMS 12.1.1
-
PA_CONTROL_ITEMS stores user-defined control items in a project. Each item is of an item type as specified by the CI_TYPE_ID column. The items are categorized using the Classification, Reason and Priority attributes
-
PA_CONTROL_ITEMS stores user-defined control items in a project. Each item is of an item type as specified by the CI_TYPE_ID column. The items are categorized using the Classification, Reason and Priority attributes
-
TABLE: PA.PA_CONTROL_ITEMS 12.2.2
-
12.1.1 DBA Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
12.1.1 DBA Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
eTRM - PA Tables and Views 12.1.1
-
eTRM - PA Tables and Views 12.2.2