Search Results pa_fp_merged_ctrl_items_u1

Overview

PA.PA_FP_MERGED_CTRL_ITEMS is a transactional table in the Oracle Projects (PA) schema that records the merge history between change documents and project financial plan versions. When the financial impact of a change document—stored as its own plan version in PA_BUDGET_VERSIONS—is incorporated into a working version of a project's financial plan, a row is inserted here to memorialize that action. Each row therefore acts as a linkage record tying together a project, the financial plan version that absorbed the change, and the change document's own plan version that supplied the financial impact.

From a Data Vault modeling perspective, the FK structure mined from this table suggests a link classification: the object primarily resolves many-to-many associations among PA_PROJECTS_ALL, PA_BUDGET_VERSIONS, PA_CONTROL_ITEMS, and HZ_PARTIES rather than holding a single descriptive business entity. This makes it a junction-style record whose grain is one merge event per project, plan version, change document, change-document plan version, and version type.

The table resides in the APPS_TS_TX_DATA tablespace with PCT FREE 10, and its indexes live in APPS_TS_TX_IDX, reflecting a standard high-volume transactional design in EBS 12.1.1 and 12.2.2.

Key Information Stored

The table contains 22 documented columns. The most significant are:

• PROJECT_ID – Unique identifier of the project; part of the unique business key.
• PLAN_VERSION_ID – The financial plan version into which the change document's impact was merged.
• CI_ID – Unique identifier of the change document (control item).
• CI_PLAN_VERSION_ID – The change document's plan version that supplied the merged financial impact.
• VERSION_TYPE – Discriminator included in the unique key, distinguishing plan version categories.
• INCLUSION_METHOD_CODE – The inclusion method applied when merging the change.
• INCLUDED_BY_PERSON_ID – Person (HZ_PARTIES) who performed the inclusion; supports the N1 nonunique index.
• IMPL_PROJ_FUNC_RAW_COST / IMPL_PROJ_FUNC_BURDENED_COST / IMPL_PROJ_FUNC_REVENUE – Functional-currency raw cost, burdened cost, and revenue impact of the implementation.
• IMPL_PROJ_RAW_COST / IMPL_PROJ_BURDENED_COST / IMPL_PROJ_REVENUE – Project-currency raw cost, burdened cost, and revenue impact.
• IMPL_QUANTITY / IMPL_EQUIPMENT_QUANTITY / IMPL_AGR_REVENUE – Implemented quantity, equipment quantity, and agreement revenue impact.
• RECORD_VERSION_NUMBER – Internally generated number for optimistic record locking.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN – Standard Who columns.

The surrogate-style unique key is enforced by PA_FP_MERGED_CTRL_ITEMS_U1 on (PROJECT_ID, PLAN_VERSION_ID, CI_ID, CI_PLAN_VERSION_ID, VERSION_TYPE), which serves as the primary business-key candidate. No separate single-column surrogate PK is documented.

Common Use Cases and Queries

Typical reporting and reconciliation scenarios include identifying which change documents contributed to a financial plan version, tracing the audit trail of who merged a change and when, and reporting implemented cost, revenue, and quantity impacts by project.

Sample query—change documents merged into a plan version:

• SELECT m.CI_ID, m.CI_PLAN_VERSION_ID, m.INCLUSION_METHOD_CODE, m.IMPL_PROJ_BURDENED_COST FROM PA.PA_FP_MERGED_CTRL_ITEMS m WHERE m.PROJECT_ID = :project_id AND m.PLAN_VERSION_ID = :plan_version_id;

Audit query—who included changes and under what method:

• SELECT m.INCLUDED_BY_PERSON_ID, m.INCLUSION_METHOD_CODE, m.CREATION_DATE FROM PA.PA_FP_MERGED_CTRL_ITEMS m WHERE m.PROJECT_ID = :project_id ORDER BY m.CREATION_DATE;

Aggregate impact query—summarize merged cost impact by change document:

• SELECT m.CI_ID, SUM(m.IMPL_PROJ_RAW_COST), SUM(m.IMPL_PROJ_BURDENED_COST), SUM(m.IMPL_PROJ_REVENUE) FROM PA.PA_FP_MERGED_CTRL_ITEMS m GROUP BY m.CI_ID;

These patterns support change-management reporting, financial plan version comparison, and variance analysis between the original change document impact and the merged result.

Related Objects

The following objects are directly referenced by foreign keys or are central to interpreting this table:

• PA.PA_BUDGET_VERSIONS – Referenced twice, via PLAN_VERSION_ID and CI_PLAN_VERSION_ID, linking the target and source plan versions.
• PA.PA_PROJECTS_ALL – Referenced via PROJECT_ID; provides project context.
• PA.PA_CONTROL_ITEMS – Referenced via CI_ID; the change document (control item) itself.
• HZ.HZ_PARTIES – Referenced via INCLUDED_BY_PERSON_ID; the party who performed the merge.

Because each merge operation touches a plan version and a change document simultaneously, these four parents constitute the essential join path for nearly every query against PA_FP_MERGED_CTRL_ITEMS. The unique index PA_FP_MERGED_CTRL_ITEMS_U1 and the nonunique index PA_FP_MERGED_CTRL_ITEMS_N1 should be considered when designing joins and predicates against PROJECT_ID, PLAN_VERSION_ID, CI_ID, CI_PLAN_VERSION_ID, VERSION_TYPE, and INCLUDED_BY_PERSON_ID.