Results for “pji_fp_aggr_pjp0”

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

Overview

PJI.PJI_FP_AGGR_PJP0 is a Project Intelligence (PJI) aggregate fact table that stores pre-summarized project cost, revenue, commitment, and effort metrics. It is one of the denormalized aggregation tables used by Oracle Project Intelligence to support high-performance multidimensional reporting over Project Accounting (PA) transactional data without querying the base PA tables directly. The table contains 40 documented columns in ETRM 12.2.2 and is owned by the PJI schema.

The heuristic Data Vault classification mined from the foreign key structure is standalone. From a modeling perspective, this suggests the table behaves as a self-contained aggregate or fact structure rather than a pure hub, link, or satellite. Its identity is defined by a combination of transactional, organizational, and dimensional attributes rather than by a single chained business key, and it should be modeled as a dimensional aggregate fact in a Data Vault or star-schema translation.

Key Information Stored

The most significant columns in PJI_FP_AGGR_PJP0 fall into three groups: identity/dimension keys, plan and currency context, and aggregated measures.

• WORKER_ID and TXN_ACCUM_HEADER_ID — principal surrogate-style identifiers linking the aggregate row back to the worker and the transaction accumulation header that produced it.
• PROJECT_ID, PROJECT_ORG_ID, PROJECT_ORGANIZATION_ID, PROJECT_ELEMENT_ID — project, organization, and project element business-key candidates that scope the aggregate to a specific project/element combination.
• TIME_ID, PERIOD_TYPE_ID, CALENDAR_TYPE — the time dimension context, defining the period over which amounts are accumulated.
• RBS_ELEMENT_ID, RBS_VERSION_ID, PLAN_VERSION_ID, PLAN_TYPE_ID, RBS_AGGR_LEVEL — reporting/versioning context; the RBS columns are the documented foreign keys to PA_RBS_ELEMENTS and PA_RBS_VERSIONS_B.
• CURRENCY_CODE and CURR_RECORD_TYPE_ID — currency and record-type qualifiers for monetary measures.
• RAW_COST, BRDN_COST, REVENUE — core cost and revenue aggregates (raw and burdened).
• BILL_RAW_COST, BILL_BRDN_COST and BILL_LABOR_RAW_COST, BILL_LABOR_BRDN_COST, BILL_LABOR_HRS — billable and labor-specific rollups.
• LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE — labor effort and value measures.
• EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS — equipment cost and utilization measures.
• SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST — commitment amounts by source (supplier invoice, purchase order, requisition, other).
• CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST — capitalizable cost rollups.

No unique index or primary-key constraint is documented in the supplied metadata; identity is therefore composite, constructed from the dimensional and header keys listed above.

Common Use Cases and Queries

The table is primarily consumed by Project Intelligence dashboards and by custom reporting that requires summarized project performance without hitting PA transaction tables. Typical scenarios include project cost versus revenue variance analysis, labor and equipment utilization reporting, and commitment reporting against purchase orders, requisitions, and supplier invoices.

A representative query joining the documented RBS foreign keys is:

• SELECT a.PROJECT_ID, a.WORKER_ID, a.RAW_COST, a.BRDN_COST, a.REVENUE FROM PJI.PJI_FP_AGGR_PJP0 a JOIN PA_RBS_ELEMENTS e ON a.RBS_ELEMENT_ID = e.RBS_ELEMENT_ID JOIN PA_RBS_VERSIONS_B v ON a.RBS_VERSION_ID = v.RBS_VERSION_ID;
• Period-based rollups can be built by grouping on TIME_ID, PERIOD_TYPE_ID, and CALENDAR_TYPE.
• Commitment analysis filters on PO_COMMITTED_COST, PR_COMMITTED_COST, and SUP_INV_COMMITTED_COST by project and period.
• Currency-normalized reports group by CURRENCY_CODE and CURR_RECORD_TYPE_ID.

Related Objects

The documented foreign keys establish the following principal relationships:

• PA_RBS_ELEMENTS — referenced via RBS_ELEMENT_ID; supplies the reporting breakdown element definition.
• PA_RBS_VERSIONS_B — referenced via RBS_VERSION_ID; supplies the RBS version context for the aggregate.
• PA_PROJECTS_ALL / project tables — joined via PROJECT_ID and PROJECT_ORGANIZATION_ID for project attributes.
• PA_PROJECT_ELEMENTS (or equivalent element tables) — joined via PROJECT_ELEMENT_ID.
• PER_TIME_PERIODS / PJI time dimension — joined via TIME_ID, PERIOD_TYPE_ID, and CALENDAR_TYPE.
• PA_PLAN_VERSIONS and plan-type reference tables — joined via PLAN_VERSION_ID and PLAN_TYPE_ID.
• Other PJI FP_AGGR fact tables — consumed together for cross-dimensional Project Intelligence reporting.

These relationships position PJI_FP_AGGR_PJP0 as a central aggregate fact within the Project Intelligence reporting layer, joining outward to the RBS and project dimensions that define its analytical grain.