Search Results pa_mc_draft_inv_dets_ar

Overview

The PA_MC_DRAFT_INV_DETS_AR table resides in the PA (Projects) schema and serves as an archive and purge repository for draft invoice detail records within Oracle EBS 12.1.1 and 12.2.2. It is a companion table to the transactional PA_DRAFT_INVOICE_DETAILS_ALL table, capturing historical rows that have been selected for removal from the live application tables during scheduled purge cycles. In practice, this table is populated by the purge/archive concurrent programs associated with Project Billing, allowing organizations to preserve a defensible audit trail of draft invoice activity while keeping operational tables lean and performing at scale.

From a Data Vault modeling perspective, the heuristic classification for this object is standalone. It does not behave as a pure hub, link, or satellite; no upstream relationships are mined that would group it into a shared integration pattern. A modeling suggestion would therefore be to treat it as an independent archival entity, keyed largely by its own composite unique index, rather than collapsing it into a conformed dimensional structure with its live counterpart.

Key Information Stored

The table physically comprises 16 columns. The most operationally significant are listed below, with the surrogate and business-key distinctions noted.

• PURGE_BATCH_ID — Foreign key to PA_PURGE_BATCHES_ALL; identifies the purge run that archived the row.
• PURGE_RELEASE — Release identifier for the archived purge batch, useful for versioned retention policies.
• PURGE_PROJECT_ID — The project identifier associated with the purge operation.
• DRAFT_INVOICE_DETAIL_ID — The original draft invoice detail identifier from the live source table; forms part of the unique business key.
• SET_OF_BOOKS_ID — The ledger/set of books context for the invoice detail; part of the unique key.
• PROJECT_ID — The project owning the archived draft invoice line; part of the unique key.
• INVOICED_FLAG — Indicates whether the line had been invoiced prior to purge.
• ACCT_CURRENCY_CODE, ACCT_RATE_TYPE, ACCT_RATE_DATE, ACCT_EXCHANGE_RATE — The accounting currency and rate metadata captured at purge time.
• BILL_AMOUNT — The billed amount recorded for the detail line.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program audit columns identifying which job and program produced the archived row.

The composite unique index PA_MC_DRAFT_INV_DETS_AR_U1 on (SET_OF_BOOKS_ID, PROJECT_ID, DRAFT_INVOICE_DETAIL_ID) serves as the business-key candidate. No separate single-column surrogate primary key is documented; the unique index effectively serves this purpose.

Common Use Cases and Queries

Typical usage centers on purge reconciliation, audit reconstruction, and retention reporting. Analysts often query archive rows to confirm whether a specific draft invoice detail was purged and under which batch.

• Purging reconciliation by batch: SELECT PURGE_BATCH_ID, COUNT(*) FROM PA_MC_DRAFT_INV_DETS_AR GROUP BY PURGE_BATCH_ID;
• Reconstructing invoice detail history: SELECT * FROM PA_MC_DRAFT_INV_DETS_AR WHERE PROJECT_ID = :project_id AND DRAFT_INVOICE_DETAIL_ID = :detail_id;
• Retention auditing by program: SELECT PROGRAM_ID, PROGRAM_UPDATE_DATE, COUNT(*) FROM PA_MC_DRAFT_INV_DETS_AR GROUP BY PROGRAM_ID, PROGRAM_UPDATE_DATE;
• Joining to live tables to identify unarchived residual rows using the unique key columns.

Related Objects

The most significant related objects derive directly from the documented foreign keys and the shared column structure.

• PA_PURGE_BATCHES_ALL — Joined via PURGE_BATCH_ID; parent of the archive batch.
• PA_DRAFT_INVOICE_DETAILS_ALL — Joined via DRAFT_INVOICE_DETAIL_ID; the live transactional source.
• PA_DRAFT_INVOICES_ALL — Header table linking to draft invoice details.
• PA_PROJECTS_ALL — Referenced through PROJECT_ID for project context.
• PA_MC_DRAFT_INV_DETS_STG — Staging counterpart often used in purge/archive flows.
• GL_SETS_OF_BOOKS — Referenced via SET_OF_BOOKS_ID for ledger validation.
• FND_CONCURRENT_REQUESTS — Linked through REQUEST_ID for purge job tracing.
• PA_PURGE_ENTITIES — Defines purgeable entity definitions aligned to this table.