Search Results pa_ei_denorm_ar




Overview
Explore in AI Chat

Overview

PA_EI_DENORM_AR is a denormalized archive table residing in the PA (Projects) schema of Oracle E-Business Suite, documented as VALID in both Release 12.1.1 and 12.2.2. Its official description identifies it as a table "for Archive/Purge," with column semantics inherited directly from the main denormalized expenditure inquiry table. In practice, PA_EI_DENORM_AR holds a flattened, wide-row projection of expenditure data that Oracle Projects uses during archive and purge processing, allowing the concurrent purge programs to stage, retain, or eliminate historical expenditure records without repeatedly traversing the highly normalized transaction model.

From a modeling perspective, the heuristic Data Vault classification mined from its foreign key structure is standalone. This suggests the table functions as neither a pure hub nor a link, but rather as a wide satellite-style or staging structure anchored to a purge driver. It carries 203 documented columns, an unusually large count that reflects the denormalization strategy: expenditure attributes normally spread across PA_EXPENDITURES_ALL and its satellites are collapsed into repeating column groups within a single row.

Key Information Stored

The table's identity is anchored by EXPENDITURE_ID, which is a foreign key to PA_EXPENDITURES_ALL and serves as the principal business-key candidate linking the row back to the live expenditure. PURGE_BATCH_ID references PA_PURGE_BATCHES_ALL and ties each row to a specific archive/purge run. DENORM_ID and ADJUSTED_DENORM_ID provide surrogate-style grouping identifiers that associate original and adjusted denormalized records.

Common Use Cases and Queries

The primary operational scenario is the PA Archive and Purge process. Administrators query this table to reconcile purge runs, confirm what was archived before underlying rows are deleted, and produce audit evidence. A representative join to the purge driver is:

SELECT e.expenditure_id, e.denorm_total_amount, b.purge_release
FROM   pa.pa_ei_denorm_ar e,
       pa.pa_purge_batches_all b
WHERE  e.purge_batch_id = b.purge_batch_id
AND    e.project_id = :project_id;

Reporters also use the denormalized repeating item columns to reconstruct expenditure details without joining PA_EXPENDITURES_ALL, particularly for performance-sensitive historical reporting. Because the table is populated only during archive/purge activity, queries should always filter on PURGE_BATCH_ID to bound the result set.

Related Objects

  • PA_EXPENDITURES_ALL — referenced via EXPENDITURE_ID; the live source of archived expenditure data.
  • PA_PURGE_BATCHES_ALL — referenced via PURGE_BATCH_ID; the controlling purge batch record.
  • PA_EI_DENORM — the main denormalized inquiry table whose column comments this archive table inherits.
  • PA_EXPENDITURE_ITEMS — underlying item-level detail reflected in the _1 through _7 repeating columns.
  • PA_PROJECTS_ALL, PA_TASKS, PER_ALL_PEOPLE_F — dimension sources for PROJECT_ID, TASK_ID, and PERSON_ID.
  • PA Archive and Purge concurrent programs — the processes that read and write this table.