Search Results ap_xml_invoice2_level_v

Overview

AP_XML_INVOICE2_LEVEL_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 exposes the set of invoices that have been selected for a payment batch, together with the payment details associated with the selected checks. The name suggests it is part of the XML-based payment processing and reporting infrastructure, where payment batch data is flattened into a single relational row per invoice for downstream extraction, XML publishing, or integration into external payment files.

The view consolidates invoice header attributes with payment selection and check information, so a single query can report what was paid, how much, under which check run, and at what exchange rate. Because it filters on the pay-ready flags maintained by the payment batch process, it effectively shows only invoices that were eligible and approved for payment at the time the batch was built.

Underlying Base Objects

The view is defined over four base objects, all referenced through APPS synonyms:

• AP_INVOICES — the invoice header table, supplying currency, VAT code, tax amount, voucher number, and invoice type.
• AP_SELECTED_INVOICES — holds the invoice amounts, discount, due dates, amount paid, amount remaining, and payment priority as computed during selection.
• AP_SELECTED_INVOICE_CHECKS — holds the selected check records and the OK_TO_PAY_FLAG used to restrict output.
• AP_INVOICE_SELECTION_CRITERIA — holds the checkrun definition, including CHECKRUN_NAME and CHECKRUN_ID.

Joins are performed on CHECKRUN_NAME across the selection criteria and selected-check tables, on SELECTED_CHECK_ID against both PAY_SELECTED_CHECK_ID and PRINT_SELECTED_CHECK_ID in AP_SELECTED_INVOICES, and on INVOICE_ID between AP_SELECTED_INVOICES and AP_INVOICES. The WHERE clause requires OK_TO_PAY_FLAG in ('Y','F') on both the selected invoice and selected check records, which excludes invoices that were removed or blocked after selection.

Key Columns

The view projects a flat set of columns useful for payment reporting:

• INVOICE_ID / INVOICE_NUM — the invoice identifier and human-readable invoice number.
• INVOICE_AMOUNT / AMOUNT_PAID / AMOUNT_REMAINING — the invoiced amount and the paid and outstanding portions.
• INVOICE_CURRENCY_CODE / INVOICE_EXCHANGE_RATE — the invoice currency and the rate applied for payment.
• DISCOUNT_AMOUNT / DISCOUNT_AMOUNT_TAKEN / DISCOUNT_DATE — discount available and actually taken.
• DUE_DATE / PAYMENT_PRIORITY — scheduling attributes used by the payment batch.
• CHECKRUN_NAME / CHECKRUN_ID — identifies the payment batch.
• PAY_SELECTED_CHECK_ID / PRINT_SELECTED_CHECK_ID — the selected check record references.
• PROPOSED_PAYMENT_AMOUNT / WITHHOLDING_AMOUNT — the amount proposed for payment and any withholding applied.
• VAT_CODE / TAX_AMOUNT — tax attributes carried from AP_INVOICES.

Note that the view text aliases INVOICE_TYPE_LOOKUP_CODE to the INVOICE_TYPE column and CHECKRUN_NAME to BATCH_NAME in the documented column list.

Common Use Cases and Queries

Typical uses include reconciling a payment batch, validating which invoices were included in a check run, and feeding payment detail into XML or interface extracts. A representative query lists invoices and payments for a specific batch:

SELECT invoice_num,
       invoice_amount,
       amount_paid,
       amount_remaining,
       invoice_currency_code,
       checkrun_name
FROM   apps.ap_xml_invoice2_level_v
WHERE  checkrun_name = :p_checkrun_name
ORDER  BY payment_priority, due_date;

A second common query aggregates the total paid per batch for reporting:

SELECT checkrun_name, SUM(amount_paid) total_paid, COUNT(*) invoice_count
FROM   apps.ap_xml_invoice2_level_v
GROUP  BY checkrun_name;

Because the view already enforces the OK_TO_PAY_FLAG filter, queries against it return only invoices and checks that were in a payable state at selection time, making it well suited to operational and audit reporting. As with all AP_SECURITY-enabled applications, access should be granted through appropriate responsibilities so that row-level security on invoices and payments is respected.