Search Results pji_fm_extr_drevn




Overview
Explore in AI Chat

Overview

PJI_FM_EXTR_DREVN is a table owned by the PJI schema within the Oracle E-Business Suite Project Intelligence (PJI) product. As documented in the ETRM metadata for release 12.1.1 and 12.2.2, the object is classified as an intermediate summarization table. In practice, this means it is not a transactional base table but rather a staging or aggregation artifact populated during extraction and summarization routines that feed Project Intelligence reporting and analytics. Its internal EBS status is recorded as VALID.

The physical schema for 12.2.2 defines nineteen columns on the object. A heuristic Data Vault classification, mined from the foreign-key structure, places PJI_FM_EXTR_DREVN as a standalone entity. This classification is a modeling suggestion only: the table does not appear to function as a persistent hub or link with durable business keys. Instead, it behaves like a transient summarization satellite carrying draft revenue extraction data keyed by a surrogate identifier. Because the table is intermediate, its contents are typically rebuilt or refreshed by batch processes rather than maintained as master data.

Key Information Stored

The most significant columns documented in the ETRM physical schema include:

  • ROW_ID — The surrogate primary key column. The provided relationship data indicates a foreign-key reference from ROW_ID to CS_SYSTEMS_ALL_B_TEMP, which is consistent with system-generated identifier assignment patterns.
  • BATCH_ID — Identifies the extraction or summarization batch that produced the row, enabling run-level traceability and reprocessing.
  • DRAFT_REVENUE_NUM — References the draft revenue document being summarized, making it a strong business-key candidate for retrieval by document.
  • PROJECT_ID and PROJECT_ORG_ID / PROJECT_ORGANIZATION_ID — Identify the project and its owning organization context.
  • AGREEMENT_ID and CUSTOMER_ID — Tie the summarized revenue to the contract and customer dimensions.
  • LINE_SOURCE_TYPE — Classifies the origin of the summarized line data.
  • POU_UBR and POU_UER — Project organization unit utilization attributes used in revenue and effort distribution.
  • PA_DATE, PA_PERIOD_NAME, GL_DATE, and GL_PERIOD_NAME — Provide the Project Accounting and General Ledger date and period context.
  • LOG_EVENT_ID — Links the row to the event that triggered summarization.
  • PJI_SUMMARIZED_FLAG — Indicates whether the row has completed summarization processing.
  • PROJECT_TYPE_CLASS — Carries the project type classification used for filtering revenue by project category.
  • WORKER_ID — Identifies the processing worker associated with the row.

ROW_ID serves as the surrogate key; DRAFT_REVENUE_NUM is the most plausible documented business-key candidate for lookups.

Common Use Cases and Queries

Primary use cases center on validating and troubleshooting Project Intelligence draft revenue summarization. Typical query patterns filter by BATCH_ID to inspect a single extraction run, or by DRAFT_REVENUE_NUM to trace an individual document's summarized lines. Period-based reconciliation joins rows to Project Accounting and GL period data using PA_PERIOD_NAME and GL_PERIOD_NAME.

Representative SQL pattern:

  • SELECT ROW_ID, DRAFT_REVENUE_NUM, PROJECT_ID, CUSTOMER_ID, GL_PERIOD_NAME, PJI_SUMMARIZED_FLAG FROM PJI.PJI_FM_EXTR_DREVN WHERE BATCH_ID = :batch AND PJI_SUMMARIZED_FLAG = 'Y';
  • Aggregate summarized amounts by PROJECT_ORGANIZATION_ID and PA_PERIOD_NAME for revenue reporting.
  • Compare PA_DATE and GL_DATE ranges to detect period mismatches between accounting and ledger views.

Related Objects

Documented relationship data identifies CS_SYSTEMS_ALL_B_TEMP as the referenced table via ROW_ID. Beyond that, the following PJI and Project Accounting objects are most significant for joins and dependency analysis: PA_DRAFT_REVENUES (via DRAFT_REVENUE_NUM), PA_PROJECTS_ALL (via PROJECT_ID), PA_AGREEMENTS_ALL (via AGREEMENT_ID), RA_CUSTOMERS (via CUSTOMER_ID), HR_ALL_ORGANIZATION_UNITS (via PROJECT_ORGANIZATION_ID), GL_PERIODS (via GL_PERIOD_NAME), and the PJI extraction/summarization concurrent programs that populate this table. Reports in the Project Intelligence module consume PJI_FM_EXTR_DREVN indirectly through its downstream summarized structures.