Search Results pa_mc_draft_inv_details_all




Overview
Explore in AI Chat

Overview

PA_MC_DRAFT_INV_DETAILS_ALL is a Projects (PA) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores invoice detail information for cross-charged transactions, maintained separately for each reporting set of books. Its name carries the _ALL suffix, indicating that the table is partitioned or organized by operating unit and set of books, which is consistent with its composite primary key. The table supports the Multi-Currency / cross-charge invoicing flow within Oracle Projects, where costs incurred in one organization or project are billed to another and must be accounted for and revalued in the reporting ledger's currency.

From a dimensional-modeling perspective, the mined foreign-key structure suggests classifying this table as a link table: it resolves the many-to-many relationship between draft invoice details (PA_DRAFT_INVOICE_DETAILS_ALL) and projects (PA_PROJECTS_ALL), with currency conversions governed by FND_CURRENCIES and GL_DAILY_CONVERSION_TYPES. This is a heuristic suggestion rather than a documented Data Vault design.

Key Information Stored

The table contains 13 documented columns. The physical primary key is PA_MC_DRAFT_INV_DETAILS_PK, defined on the composite of SET_OF_BOOKS_ID and DRAFT_INVOICE_DETAIL_ID. A parallel unique index, PA_MC_DRAFT_INV_DETAILS_U1, enforces the same pair (SET_OF_BOOKS_ID, DRAFT_INVOICE_DETAIL_ID), confirming that this combination is the business-key candidate: one cross-charge invoice detail row per reporting set of books.

  • SET_OF_BOOKS_ID — Identifies the reporting ledger in which the cross-charged amount is accounted; part of the primary key and business key.
  • DRAFT_INVOICE_DETAIL_ID — Foreign key to PA_DRAFT_INVOICE_DETAILS_ALL, linking each row to the originating draft invoice line; part of the primary key and business key.
  • PROJECT_ID — Foreign key to PA_PROJECTS_ALL, identifying the project associated with the cross-charged transaction.
  • INVOICED_FLAG — Indicator of whether the draft invoice detail has been invoiced or is still pending.
  • BILL_AMOUNT — The amount to be billed for the cross-charged transaction.
  • ACCT_CURRENCY_CODE — Foreign key to FND_CURRENCIES, the accounting (ledger) currency into which the amount is converted.
  • ACCT_RATE_TYPE — Foreign key to GL_DAILY_CONVERSION_TYPES, the daily conversion rate type applied for the accounting currency conversion.
  • ACCT_RATE_DATE — The date on which the conversion rate is effective.
  • ACCT_EXCHANGE_RATE — The actual conversion rate used to translate the cross-charge amount into the accounting currency.
  • REQUEST_ID — The concurrent request identifier that created or last processed the row, useful for traceability and re-run diagnostics.
  • PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Standard Who columns identifying the concurrent program and timestamp of the last update.

Common Use Cases and Queries

Typical reporting scenarios include reconciling cross-charge invoice amounts per ledger, auditing currency conversion rates applied to intercompany or inter-project billing, and identifying draft invoice details that have not yet been invoiced. A representative query joining the table to its parent and project master might appear as follows:

  • List uninvoiced cross-charge lines by ledger: SELECT d.draft_invoice_detail_id, d.project_id, d.bill_amount, d.acct_currency_code FROM pa_mc_draft_inv_details_all d WHERE d.set_of_books_id = :p_sob AND d.invoiced_flag = 'N';
  • Currency conversion audit: join ACCT_RATE_TYPE to GL_DAILY_CONVERSION_TYPES and ACCT_CURRENCY_CODE to FND_CURRENCIES to validate rate types and currencies used.
  • Traceability by concurrent request: filter on REQUEST_ID to isolate rows produced by a specific run of the cross-charge invoice generation program.
  • Project-level cross-charge reporting: join PROJECT_ID to PA_PROJECTS_ALL to aggregate bill amounts by project, customer, or organization.

Because the table is ledger-partitioned, queries should almost always include a SET_OF_BOOKS_ID predicate to exploit the key and avoid full-table scans in multi-organization environments.

Related Objects

  • PA_DRAFT_INVOICE_DETAILS_ALL — Parent table joined on DRAFT_INVOICE_DETAIL_ID; supplies the core draft invoice detail attributes.
  • PA_PROJECTS_ALL — Joined on PROJECT_ID; provides project name, number, and organization context.
  • FND_CURRENCIES — Joined on ACCT_CURRENCY_CODE; validates and describes the accounting currency.
  • GL_DAILY_CONVERSION_TYPES — Joined on ACCT_RATE_TYPE; defines the conversion rate type used for accounting translation.
  • PA_DRAFT_INVOICES_ALL — Through the draft invoice detail hierarchy, provides the parent draft invoice header for reconciliation.
  • PA_MC_DRAFT_INVOICES_ALL — The companion multi-currency draft invoice header table, if present in a given release.