Results for “ap_inv_excp”

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

Overview

APPS.PA_AP_INV_EXCEPTIONS_SUM_V is an Oracle E-Business Suite reporting view that exposes Payables invoice distributions flagged as exceptions during Project Accounting (PA) processing. Its distinguishing characteristic is that it is not a simple join projection: it invokes PL/SQL functions at query time to derive exception classification, date-profile and period-name values. The view therefore serves as the authoritative source for diagnosing why Payables distributions failed to transfer into Project Accounting, and for presenting the corrective action associated with each exception code.

Unlike the standard PA exception report, which is a formatted concurrent program output, this view returns exception data in a relational, query-able form. That makes it suitable for embedded analytics, custom OAF or Forms pages, XML Publisher data templates, and data extraction into external warehouses. It is exposed under the APPS schema and is typically queried either directly by technical users or indirectly by the Payables-to-Projects interface diagnostics.

Underlying Base Objects

The view is defined over a mixture of Payables, Purchasing, General Ledger, and Project Accounting objects. The principal driving table is AP_INVOICE_DISTRIBUTIONS_ALL (aliased INVDIST), joined to AP_INVOICES_ALL (INV) and AP_INVOICE_LINES_ALL. Vendor information is obtained from PO_VENDORS, which itself is a view rather than a base table. The set of books context is supplied by GL_LEDGERS (aliased GLSOB), while PA_IMPLEMENTATIONS_ALL provides the same-period indicator (SAME_PA_GL_PERIOD) and set_of_books_id.

Supporting objects include AP_CHECKS_ALL, AP_INVOICE_PAYMENTS_ALL, AP_PAYMENT_HISTORY_ALL, AP_PAYMENT_HIST_DISTS, AP_SELF_ASSESSED_TAX_DIST_ALL, MTL_SYSTEM_ITEMS, PO_DISTRIBUTIONS, PO_DISTRIBUTIONS_ALL, PO_LINES_ALL, and PO_LINE_LOCATIONS_ALL, which supply payment, receipt, tax, and item context. Three packages are referenced: PA_EXCEPTION_REASONS_PUB (which resolves exception text and corrective action), PA_EXPENDITURES_UTILS (getorgtlname), and PA_UTILS2 (pa_date_profile, pa_period_name_profile). PA_NL_INSTALLED is also referenced. All AP, PO, GL, and PA tables are accessed through APPS synonyms.

Key Columns

• PROJECT_ID, TASK_ID — the project and task to which the distribution is attributed; NULL where the distribution could not be mapped.
• INVOICE_ID, INVOICE_NUMBER, INVOICE_LINE_NUMBER, DISTRIBUTION_LINE_NUMBER, INVOICE_DISTRIBUTION_ID — full lineage back to the Payables document.
• DENOM_CURRENCY_CODE, DENOM_AMOUNT, ACCT_CURRENCY_CODE, AMOUNT — entered versus accounted currency values; AMOUNT falls back to DENOM_AMOUNT where BASE_AMOUNT is null.
• ACCT_RATE_DATE, ACCT_RATE_TYPE, ACCT_EXCHANGE_RATE — populated conditionally via DECODE, and nulled when the invoice and ledger currencies match.
• EXCEPTION_CODE — sourced from PA_ADDITION_FLAG, the raw exception identifier.
• EXCEPTION_REASON, CORRECTIVE_ACTION — resolved by PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT using the AP_INV_EXCP reason set.
• PA_DATE, PERIOD_NAME — derived by PA_UTILS2.pa_date_profile and pa_period_name_profile; GL_PERIOD_NAME holds the original Payables accounting period.
• POSTED_FLAG — displays an asterisk where the distribution has been posted to GL.
• EXPENDITURE_TYPE, EXPENDITURE_ITEM_DATE, QUANTITY, GL_DATE, ORG_ID, EXPENDITURE_ORGANIZATION_ID — expenditure attributes used when the distribution is successfully transferred.
• VENDOR_NUMBER, VENDOR_NAME, SAME_PA_GL_PERIOD, set_of_books_id — reporting context; PAYMENT_NUM and CHECK_NUMBER are returned as NULL placeholders.

Common Use Cases and Queries

The primary use case is exception triage: identifying distributions that will not interface to PA and determining the required correction. Note that PERIOD_NAME is produced by pa_period_name_profile, which resolves the PA period from the derived expenditure item date and accounting date rather than from stored columns.

A representative query lists unresolved exceptions for a project:

• SELECT invoice_number, distribution_line_number, exception_code, exception_reason, corrective_action, denom_amount, period_name FROM apps.pa_ap_inv_exceptions_sum_v WHERE project_id = :p_project_id ORDER BY invoice_date, invoice_number;

Aggregation by exception type supports root-cause analysis across a period:

• SELECT exception_code, exception_reason, COUNT(*) exception_count, SUM(amount) total_amount FROM apps.pa_ap_inv_exceptions_sum_v WHERE gl_date BETWEEN :p_start AND :p_end GROUP BY exception_code, exception_reason ORDER BY exception_count DESC;

Because the view calls PL/SQL per row, performance is best when filtered by PROJECT_ID, INVOICE_ID, or ORG_ID. The same-period indicator allows reconciliation queries that compare Payables and Project Accounting periods, and the vendor columns support supplier-level reporting on recurring interface failures.