Search Results ap_invoice_extract_details_v




Overview
Explore in AI Chat

Overview

AP_INVOICE_EXTRACT_DETAILS_V is a Payables (AP) reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents a denormalized, line-and-distribution-level extract of invoice accounting data intended for external reporting, data warehousing, and subledger integration. The view joins the invoice distribution accounting lines to their parent invoice lines and combines them with withholding tax group information and a number of derived flags. Its scope corresponds to the data used by the Payables accounting extraction process, which produces accounting entries for transfer to the General Ledger. The view is therefore most commonly consumed by downstream extraction programs, custom reports, and reconciliation queries that need a flat, queryable representation of invoice distributions, their associated accounting events, accounting dates, and amounts, together with deferred accounting and cancellation attributes. Because it is a view rather than a table, it provides read-only access and inherits the security and performance characteristics of its underlying base objects.

Underlying Base Objects

The view is defined over several APPS synonyms that reference the core Payables and related application tables. Documented referenced objects include AP_INVOICES_ALL, AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL, AP_AWT_GROUPS, AP_SELF_ASSESSED_TAX_DIST_ALL, FINANCIALS_SYSTEM_PARAMS_ALL, GL_ENCUMBRANCE_TYPES, PO_DISTRIBUTIONS_ALL, PO_HEADERS_ALL, RCV_TRANSACTIONS, and the package PSA_BC_XLA_PUB. The principal source is AP_INVOICE_DISTRIBUTIONS_ALL, aliased AID, which supplies the accounting event identifier, distribution identifier, accounting date, amount, statistical amount, and distribution-level attributes. AP_INVOICE_LINES_ALL (AIL) supplies the line-level context, including the deferred accounting flag and dates, line type, line source, line group number, item description, and asset book information. AP_AWT_GROUPS (AWTG) provides the withholding tax group name. Reference to PSA_BC_XLA_PUB indicates the view participates in the Subledger Accounting (XLA) extraction framework, consistent with its role in generating and reporting accounting events. The remaining tables—purchase order distributions and headers, receiving transactions, self-assessed tax distributions, encumbrance types, and financials system parameters—support supplementary attributes such as encumbrance and tax context.

Key Columns

The view exposes identity and accounting columns including ACCOUNTING_EVENT_ID and INVOICE_DISTRIBUTION_ID, which link each row to the Subledger Accounting event and the originating distribution. ACCOUNTING_DATE, PERIOD_NAME, and AMOUNT describe the accounting period and monetary value of the distribution. DIST_CODE_COMBINATION_ID identifies the accounting flexfield combination, while DISTRIBUTION_LINE_NUMBER and LINE_GROUP_NUMBER provide sequencing. Deferred accounting is represented by DEFERRED_ACCTG_FLAG, DEF_ACCTG_START_DATE, DEF_ACCTG_END_DATE, DEF_ACCTG_PERIOD_TYPE, and DEF_ACCTG_NUMBER_OF_PERIODS. A derived reversal indicator is computed using DECODE logic over HISTORICAL_FLAG, CANCELLATION_FLAG, and PARENT_REVERSAL_ID, marking rows that represent reversals. Line-level attributes include LINE_TYPE_LOOKUP_CODE, LINE_SOURCE, ITEM_DESCRIPTION, ASSET_BOOK_TYPE_CODE, EXPENSE_GROUP, and various segment columns (ACCOUNT_SEGMENT, BALANCING_SEGMENT, COST_CENTER_SEGMENT). Withholding tax group name is supplied by AWTG.NAME. Tax and 1099 attributes include TAX_RECOVERABLE_FLAG, INCOME_TAX_REGION, and TYPE_1099. Descriptive flexfield columns (ATTRIBUTE1 through ATTRIBUTE15, ATTRIBUTE_CATEGORY) and global flexfield columns (GLOBAL_ATTRIBUTE_CATEGORY, GLOBAL_ATTRIBUTE1 through GLOBAL_ATTRIBUTE15) are present for both distribution and line contexts, enabling reporting on client-specific configurations.

Common Use Cases and Queries

Typical uses include extracting invoice distribution accounting for a given accounting period, reconciling Payables to the General Ledger, and feeding external reporting systems. A representative query selects the accounting event, distribution, accounting date, and amount for a period:

  • SELECT accounting_event_id, invoice_distribution_id, accounting_date, period_name, amount FROM ap_invoice_extract_details_v WHERE period_name = :period;
  • SELECT invoice_distribution_id, dist_code_combination_id, amount, line_type_lookup_code FROM ap_invoice_extract_details_v WHERE accounting_event_id = :event_id;
  • SELECT invoice_distribution_id, amount, name FROM ap_invoice_extract_details_v WHERE type_1099 IS NOT NULL;

Deferred accounting analysis can filter on DEFERRED_ACCTG_FLAG to identify distributions subject to deferral and inspect their start and end dates. Cancellation and reversal analysis can leverage the derived reversal column. Because the view is built on transaction tables, extraction queries should be constrained by accounting date or period to limit volume, and joins to AP_INVOICES_ALL or GL_CODE_COMBINATIONS are typically required to resolve invoice number and account descriptions for presentation.