Search Results ap_mc_invoice_dists_pk

Overview

AP_MC_INVOICE_DISTS is a Payables (AP) module table owned by the AP schema that stores invoice distribution line information expressed in an MRC (Multiple Reporting Currency) defined reporting currency. It is the reporting-currency counterpart to the primary AP_INVOICE_DISTRIBUTIONS table, which holds distributions in the functional (primary set of books) currency. In Oracle EBS 12.1.1 and 12.2.2, the Multiple Reporting Currency feature allows a single transaction to be accounted and reported in more than one currency, so this table preserves the distribution-level amounts as translated into the reporting set of books.

Each row corresponds to a distribution line of a specific invoice for a specific reporting set of books. The table is populated during invoice accounting and translation processes, and its rows are read by reporting, reconciliation, and posting logic that operates against the reporting set of books rather than the primary ledger. The ETRM metadata classifies this object heuristically as standalone in Data Vault terms, suggesting that it may be modeled as an independent structure keyed by business attributes (invoice, distribution line, and set of books) rather than as a strict satellite of a parent hub.

Key Information Stored

The table contains 22 documented columns. The most significant are:

• INVOICE_ID — Identifier of the invoice to which the distribution belongs; part of the primary key.
• DISTRIBUTION_LINE_NUMBER — Sequence number of the distribution line within the invoice; part of the primary key.
• SET_OF_BOOKS_ID — Identifies the reporting set of books (ledger) for which the amounts are expressed; part of the primary key and central to the MRC context.
• INVOICE_DISTRIBUTION_ID — The corresponding distribution identifier in the primary currency table; captured by unique index AP_MC_INVOICE_DISTS_U2.
• DIST_CODE_COMBINATION_ID — Accounting flexfield combination for the distribution line.
• AMOUNT — Distribution amount in the reporting currency.
• BASE_AMOUNT — Distribution amount in the base currency of the reporting set of books.
• EXCHANGE_RATE, EXCHANGE_DATE, EXCHANGE_RATE_TYPE — Conversion parameters used to translate the distribution into the reporting currency.
• LINE_TYPE_LOOKUP_CODE — Type of distribution line (for example, item, tax, or freight).
• QUANTITY_INVOICED — Quantity associated with the distribution.
• AMOUNT_TO_POST, BASE_AMOUNT_TO_POST — Amounts flagged for posting to the general ledger.
• POSTED_AMOUNT, POSTED_BASE_AMOUNT — Amounts already posted, used for reconciliation.

The surrogate primary key AP_MC_INVOICE_DISTS_PK comprises (INVOICE_ID, DISTRIBUTION_LINE_NUMBER, SET_OF_BOOKS_ID). The unique indexes AP_MC_INVOICE_DISTS_U1 and AP_MC_INVOICE_DISTS_U2 represent business-key candidates over the same three columns and over (INVOICE_DISTRIBUTION_ID, SET_OF_BOOKS_ID) respectively.

Common Use Cases and Queries

Principal uses include MRC reporting, audit of translated distributions, reconciliation of functional versus reporting currency amounts, and validation of exchange rate application. A typical query joins the reporting-currency table to its primary-currency counterpart:

• Compare reporting-currency and primary-currency distribution amounts:
  SELECT m.invoice_id, m.distribution_line_number, m.set_of_books_id, m.amount, d.amount
FROM ap_mc_invoice_dists m, ap_invoice_distributions_all d
WHERE m.invoice_distribution_id = d.invoice_distribution_id
AND m.set_of_books_id = :reporting_sob_id;
• Summarize distributions by reporting set of books and line type:
  SELECT set_of_books_id, line_type_lookup_code, SUM(amount)
FROM ap_mc_invoice_dists
GROUP BY set_of_books_id, line_type_lookup_code;
• Identify unposted or partially posted amounts for reconciliation using AMOUNT_TO_POST versus POSTED_AMOUNT.
• Verify exchange rate application by examining EXCHANGE_RATE, EXCHANGE_DATE, and RATE_VAR_CODE_COMBINATION_ID.

Related Objects

The following objects are most significant in relation to this table:

• AP_INVOICE_DISTRIBUTIONS_ALL — Primary-currency distribution table; linked via INVOICE_DISTRIBUTION_ID and SET_OF_BOOKS_ID.
• AP_INVOICES_ALL — Invoice header; linked via INVOICE_ID.
• AP_INVOICE_LINES_ALL — Invoice lines underlying the distributions.
• GL_SETS_OF_BOOKS — Defines the reporting set of books referenced by SET_OF_BOOKS_ID.
• GL_CODE_COMBINATIONS — Accounting flexfield combinations referenced by DIST_CODE_COMBINATION_ID and RATE_VAR_CODE_COMBINATION_ID.
• AP_MC_INVOICES — Invoice-level MRC counterpart to AP_INVOICES_ALL.
• XLA / GL interface tables — Consume posted amounts for subledger accounting transfer.