Results for “pa_expenditure_items_ar”

34 results




Overview
Explore in AI Chat

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PA_EXPENDITURE_ITEMS_AR is the archive companion table to PA_EXPENDITURE_ITEMS_ALL in the Oracle E-Business Suite Projects (PA) module. It exists to support the Archive and Purge process, which is the mechanism Oracle provides to move closed or obsolete project transaction data out of the active transactional tables and into shadow archive tables before the corresponding rows are permanently deleted. In EBS 12.1.1 and 12.2.2, the table resides in the PA schema and is documented as VALID with 201 columns in the 12.2.2 physical schema, mirroring the column layout of the main expenditure items table so that a row can be moved wholesale between the two structures. Its role is therefore lifecycle-management rather than day-to-day transaction processing; application logic writes to it only during archive and purge runs.

From a Data Vault modeling perspective, the FK structure suggests this object behaves as a link: it sits at the intersection of project expenditure events, purchase order lines, WIP resources, capital events, and purge batches. Treat that classification as a heuristic suggestion derived from the foreign key pattern rather than a documented architectural statement.

Key Information Stored

The most business-significant columns are those that identify the archived transaction and its context. EXPENDITURE_ITEM_ID is the surrogate primary key inherited from PA_EXPENDITURE_ITEMS_ALL, and EXPENDITURE_ID is the parent expenditure reference. TASK_ID, PROJECT_ID, EXPENDITURE_ITEM_DATE, EXPENDITURE_TYPE, and ORGANIZATION_ID establish which project, task, date, and expenditure type the archived item relates to. Financial measures carried over include RAW_COST, BURDEN_COST, QUANTITY, BILL_RATE, RAW_REVENUE, ACCRUED_REVENUE, and ADJUSTED_REVENUE, each accompanied by denormalized currency and rate columns such as DENOM_RAW_COST, ACCT_BURDENED_COST, PROJECT_EXCHANGE_RATE, and ACCT_EXCHANGE_RATE.

Status and control columns include COST_DISTRIBUTED_FLAG, REVENUE_DISTRIBUTED_FLAG, BILLABLE_FLAG, BILL_HOLD_FLAG, and the various rejection-code fields (COST_DIST_REJECTION_CODE, REV_DIST_REJECTION_CODE, INV_GEN_REJECTION_CODE). Purge-specific keys — PURGE_BATCH_ID, PURGE_RELEASE, and PURGE_PROJECT_ID — are unique to this archive structure. Foreign-key candidates documented for this table include EXPENDITURE_ITEM_ID, EXPENDITURE_ID, PO_LINE_ID, WIP_RESOURCE_ID, and CAPITAL_EVENT_ID. The column set is a full clone of the base table, so all 201 attributes are archived together; standard WHO audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and the ATTRIBUTE1–ATTRIBUTE10 DFF columns are preserved. No unique index separate from the inherited key is documented in the supplied metadata.

Common Use Cases and Queries

The primary use case is reconciling what the archive and purge process moved. DBAs and auditors query the table using PURGE_BATCH_ID to confirm the scope of a purge run before and after release:

  • Verification of a batch: SELECT expenditure_item_id, expenditure_id, raw_cost, burden_cost FROM pa.pa_expenditure_items_ar WHERE purge_batch_id = :batch;
  • Cross-checking archived versus purged amounts: join PA_EXPENDITURE_ITEMS_AR to PA_PURGE_BATCHES_ALL on PURGE_BATCH_ID to confirm run metadata and release status.
  • Historical cost reporting for closed projects whose items have been archived out of PA_EXPENDITURE_ITEMS_ALL, aggregating RAW_COST and BURDEN_COST by PROJECT_ID and EXPENDITURE_TYPE.
  • Recovering a specific transaction: filtering by TRANSACTION_SOURCE, TRANSACTION_REFERENCE (ORIG_TRANSACTION_REFERENCE), or SOURCE_EXPENDITURE_ITEM_ID to trace where an item originated before purging.
  • Inventory and purchasing reconciliation, joining PO_LINE_ID to PO_LINES_ALL and INVENTORY_ITEM_ID to inventory tables for archived procurement-related expenditure.

Related Objects

The table's FK metadata defines its principal dependencies:

  • PA_EXPENDITURE_ITEMS_ALL — the live source table; EXPENDITURE_ITEM_ID is the migration key.
  • PA_EXPENDITURES_ALL — parent expenditure header via EXPENDITURE_ID.
  • PA_PURGE_BATCHES_ALL — controls each archive/purge run via PURGE_BATCH_ID.
  • PA_CAPITAL_EVENTS — capital event linkage via CAPITAL_EVENT_ID.
  • PO_LINES_ALL — purchasing line reference via PO_LINE_ID.
  • BOM_RESOURCES — WIP resource reference via WIP_RESOURCE_ID.
  • PA_PROJECTS_ALL / PA_TASKS — indirectly through PROJECT_ID and TASK_ID for project reporting.