Search Results pa_expenditures_ar




Overview
Explore in AI Chat

Overview

PA_EXPENDITURES_AR is an archive table within the Oracle Projects (PA) module of Oracle E-Business Suite 12.1.1 and 12.2.2. As its name and the ETRM description indicate, it exists to support the Archive/Purge process for project expenditures. Rather than modifying or deleting records from the live transaction table, the archive and purge programs relocate eligible expenditure rows into PA_EXPENDITURES_AR, preserving them for audit, reconciliation, and historical reporting while reducing the storage footprint and performance overhead of the primary operating table. Its structure mirrors the main expenditure table, and column-level semantics follow the comments documented on that parent object.

The ETRM metadata records a heuristic Data Vault classification of standalone. Read as a modeling suggestion, this reflects the fact that PA_EXPENDITURES_AR is not intended to function as a conformed hub, link, or satellite in a warehouse design. It is a purpose-built staging/retention table whose dependencies are expressed through foreign keys to the archive control table and the source expenditure table rather than through normalized business entity relationships. The table is owned by the APPS-equivalent PA schema and is documented with 51 columns in the 12.2.2 schema.

Key Information Stored

The table carries the full expenditure payload plus archive control attributes. The most significant columns are:

PA_EXPENDITURES_AR references PA_EXPENDITURES_ALL via EXPENDITURE_ID and PA_PURGE_BATCHES_ALL via PURGE_BATCH_ID. The EA (entity–attribute) columns ATTRIBUTE_CATEGORY through ATTRIBUTE10 preserve descriptive flexfield context.

Common Use Cases and Queries

Typical usage centers on auditing what was purged and reconstructing expenditure history for closed projects.

  • Reconcile a purge run: SELECT COUNT(*), SUM(CONTROL_TOTAL_AMOUNT) FROM PA_EXPENDITURES_AR WHERE PURGE_BATCH_ID = :batch_id;
  • Trace an original expenditure: SELECT * FROM PA_EXPENDITURES_AR WHERE EXPENDITURE_ID = :exp_id;
  • Audit trail by program: SELECT PROGRAM_ID, REQUEST_ID, PURGE_RELEASE FROM PA_EXPENDITURES_AR WHERE PURGE_PROJECT_ID = :proj_id;
  • Reporting on archived amounts by class: aggregate CONTROL_TOTAL_AMOUNT grouped by EXPENDITURE_CLASS_CODE and ORG_ID.

Because archived rows are read-only after the purge, these queries are safe for historical reporting without affecting live transaction processing.

Related Objects

The most significant related objects, per the documented FK structure, are:

  • PA_EXPENDITURES_ALL — The live expenditure table; joined on EXPENDITURE_ID.
  • PA_PURGE_BATCHES_ALL — The purge batch control table; joined on PURGE_BATCH_ID.
  • PA_PURGE_BATCHES and associated purge program objects that drive the archive process.
  • PA_PROJECTS_ALL — Provides project context via PURGE_PROJECT_ID and the underlying project reference.
  • PA_EXPENDITURE_TYPES / PA_EXPENDITURE_CLASSES — Classification lookups for EXPENDITURE_CLASS_CODE.
  • PA_PERIODS_ALL — Period attributes referenced by EXPENDITURE_ENDING_DATE.

Together these objects define the archive dependency chain and support both the purge execution and subsequent historical analysis of project costs.