Search Results pa_exp_comments_ar




Overview
Explore in AI Chat

Overview

PA_EXP_COMMENTS_AR is an archive/purge companion table within the Oracle E-Business Suite Projects (PA) module. Its name carries the "_AR" suffix, which in Oracle EBS convention denotes an archive table that shadows a corresponding transactional table—in this case the expenditure comments table maintained by the Projects application. The table stores the same column structure as its transactional source, as the ETRM description explicitly states: "Pa_Expenditure_Comments_ar is a table for Archive/Purge. Refer to comments on the columns in the main table." Its purpose is to hold archival copies of expenditure comment records that have been swept out of the live expenditure tables by the Projects purge programs, allowing historical reporting and audit retention without burdening the online transaction tables.

The object resides in the PA schema and is documented as VALID in release 12.1.1 / 12.2.2. From a Data Vault modeling perspective, the metadata's heuristic classification places this table as standalone—that is, it is not itself a hub, link, or satellite, but is best modeled as a satellite-like historical store dependent on the live expenditure items it archives. Its foreign keys reference PA_PURGE_BATCHES_ALL and PA_EXPENDITURE_ITEMS_ALL, tying each archived row to both a specific purge execution and the original expenditure item it belonged to.

Key Information Stored

The physical schema documents 15 columns, with the purge and expenditure linkage forming the operational core:

The composite of EXPENDITURE_ITEM_ID and LINE_NUMBER acts as the natural business-key candidate, while PURGE_BATCH_ID provides the purge-instance linkage rather than a strict surrogate primary key.

Common Use Cases and Queries

Typical usage centers on audit and retention reporting: reconstructing comment history for purged expenditure items, validating which purge batch removed a given comment, and reconciling archive counts against live-table deletions.

  • Locate archived comments for a specific expenditure item: 
    SELECT line_number, expenditure_comment
FROM   pa.pa_exp_comments_ar
WHERE  expenditure_item_id = :item_id
ORDER BY line_number;
  • Report everything removed by a given purge batch: 
    SELECT a.expenditure_item_id, a.expenditure_comment, b.purge_batch_name
FROM   pa.pa_exp_comments_ar a,
       pa.pa_purge_batches_all b
WHERE  a.purge_batch_id = b.purge_batch_id
AND    a.purge_batch_id = :batch_id;
  • Confirm which concurrent program performed the archive: 
    SELECT request_id, program_id, program_update_date
FROM   pa.pa_exp_comments_ar
WHERE  purge_batch_id = :batch_id;

Related Objects

The table's integrity and utility depend on a small set of related objects:

  • PA_EXPENDITURE_ITEMS_ALL — joined via EXPENDITURE_ITEM_ID; the source transactional table whose comments are archived here.
  • PA_PURGE_BATCHES_ALL — joined via PURGE_BATCH_ID; defines each purge execution.
  • PA_EXP_COMMENTS (transactional counterpart) — the live table whose structure PA_EXP_COMMENTS_AR mirrors.
  • PA_PURGE_PROJECTS_ALL — purge scope definition related through PURGE_PROJECT_ID.
  • Projects Archive/Purge concurrent programs — the API-driven processes that populate this table via standard purge routines.