Search Results pji_fm_aggr_act2

Overview

PJI_FM_AGGR_ACT2 is a table owned by the PJI schema in Oracle EBS Project Intelligence (PJI). The ETRM metadata describes it as an "intermediate summarization table." Its role is to hold pre-aggregated financial and funding measures for projects, tasks, and project organizations, materialized at defined GL and PA time/period intersections, so that downstream Project Intelligence reporting and analytics do not have to re-scan transaction-level detail each time a report is run.

Because the table carries both project- and task-level keys alongside GL/PA calendar identifiers, it functions as a staging and aggregation layer between operational subledger tables (Projects, Receivables, GL) and Project Intelligence dashboards. The aggregation spans multiple currency and accounting bases: project currency (PRJ_*), project organization unit currency (POU_*), global currency (GG_*, GG2_*), project currency at the reporting level (GP_*, GP2_*), and transaction currency (TXN_*). This multi-basis column family is the defining structural characteristic of the object.

Under a heuristic Data Vault classification mined from the FK structure, PJI_FM_AGGR_ACT2 is assessed as standalone. The only documented foreign key, ROW_ID, references CS_SYSTEMS_ALL_B_TEMP, which is a temporary system-surrogate source rather than a durable business entity. Modelers should therefore treat this object as neither a classic hub, link, nor satellite; it is better understood as a derived or aggregate construct whose grain is defined by its combination of PROJECT_ID, TASK_ID, and the GL/PA period columns rather than by a conventional business key.

Key Information Stored

The table exposes 151 documented columns in the ETRM 12.2.2 physical schema. The most significant are:

• ROW_ID — surrogate identifier; the only documented FK-bearing column (references CS_SYSTEMS_ALL_B_TEMP).
• PROJECT_ID, TASK_ID — project and task business-key candidates defining the reporting grain.
• PROJECT_ORG_ID, PROJECT_ORGANIZATION_ID — organization context for the aggregation.
• GL_PERIOD_NAME, PA_PERIOD_NAME, GL_TIME_ID, PA_TIME_ID, GL_CALENDAR_ID, PA_CALENDAR_ID — GL and PA time dimensions used to anchor the summary to accounting and project periods.
• TXN_CURRENCY_CODE — the transaction currency for the TXN_* measure family.
• PRJ_REVENUE, PRJ_FUNDING and the PRJ_AR_* family (invoice, cash applied, credit memo, unbilled receivables, unearned revenue, amount due, overdue) — project-level financial summary measures.
• GG_* and GP_* families — global and project reporting-currency equivalents of the same measures.
• Funding sub-components — PRJ_INITIAL_FUNDING_AMOUNT, PRJ_ADDITIONAL_FUNDING_AMOUNT, PRJ_CANCELLED_FUNDING_AMOUNT, PRJ_FUNDING_ADJUSTMENT_AMOUNT, plus matching count columns (INITIAL_FUNDING_COUNT, ADDITIONAL_FUNDING_COUNT, CANCELLED_FUNDING_COUNT, FUNDING_ADJUSTMENT_COUNT).
• AR count columns — AR_INVOICE_COUNT, AR_CASH_APPLIED_COUNT, AR_UNAPPR_INVOICE_COUNT, AR_APPR_INVOICE_COUNT, AR_COUNT_DUE, AR_COUNT_OVERDUE.
• Dangling rate/time flags — DANGLING_GL_RATE_FLAG, DANGLING_PA_RATE_FLAG, DANGLING_GL_RATE2_FLAG, DANGLING_PA_RATE2_FLAG, DANGLING_GL_TIME_FLAG, DANGLING_PA_TIME_FLAG, DANGLING_EN_TIME_FLAG — used to signal rows where FX rates or time dimensions could not be fully resolved during the aggregation pass.

Note that ROW_ID, not PROJECT_ID/TASK_ID, is the documented FK anchor, while PROJECT_ID, TASK_ID, and the calendar/period columns are the practical business-key candidates for joining into reporting queries. No unique index information is provided in the metadata excerpt, so uniqueness of the composite grain is inferred from the column semantics only.

Common Use Cases and Queries

Typical uses include funding burn-down reporting, revenue versus funding variance analysis, receivable aging by project organization, and multi-currency rollups for Project Intelligence dashboards. A basic aggregation query follows the natural grain:

SELECT PROJECT_ID, TASK_ID, GL_PERIOD_NAME,
       SUM(PRJ_REVENUE), SUM(PRJ_FUNDING), SUM(PRJ_AR_AMOUNT_DUE)
FROM   PJI.PJI_FM_AGGR_ACT2
WHERE  GL_PERIOD_NAME BETWEEN :p_from AND :p_to
GROUP  BY PROJECT_ID, TASK_ID, GL_PERIOD_NAME;

Data-quality screening should filter on the dangling flags before publishing results:

SELECT COUNT(*)
FROM   PJI.PJI_FM_AGGR_ACT2
WHERE  DANGLING_GL_RATE_FLAG = 'Y' OR DANGLING_PA_RATE_FLAG = 'Y';

Reporting beyond the stored grain (for example, splitting by customer or expenditure type) is not possible from this table alone and must join back to the source subledger detail.

Related Objects

The documented relationship surface is narrow. The following are the most significant related objects based on the metadata provided and the PJI aggregation lineage:

• CS_SYSTEMS_ALL_B_TEMP — referenced via PJI_FM_AGGR_ACT2.ROW_ID, the sole documented FK relationship.
• PA_PROJECTS_ALL — join on PROJECT_ID to resolve project name, number, and status.
• PA_TASKS — join on TASK_ID for task-level descriptions and hierarchy.
• HR_ALL_ORGANIZATION_UNITS / PA_ORGANIZATIONS_ALL — join on PROJECT_ORG_ID or PROJECT_ORGANIZATION_ID for organization naming.
• GL_PERIODS — join on GL_PERIOD_NAME and GL_CALENDAR_ID to obtain period start/end dates.
• PA_PERIODS — join on PA_PERIOD_NAME and PA_CALENDAR_ID for project period boundaries.
• PJI_FM_AGGR_ACT (and sibling PJI_FM_* tables) — closely related summarization objects used in the same Project Intelligence aggregation flow.

No public APIs on PJI_FM_AGGR_ACT2 are documented; the table is maintained as an internal summarization target rather than an integration endpoint.