AP Invoice Payment Upload – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Records Manual and Quick payments of open Payables invoices and voids payments - the Excel equivalent of entering a payment and its invoices in the Payments window.

Each row is one invoice installment paid, with the payment columns repeated on every row. Rows with identical payment columns are paid by one payment. Amount Paid and Discount Taken default to the full amount remaining less the discount available at the Payment Date, and can be reduced to pay an installment partially.

Each payment is processed as a whole: when any of its rows fails validation, none of its rows are saved. A Manual payment records a document already issued and needs its Document Num. A Quick payment takes the next number of a printed Payment Document, and is submitted to Oracle Payments as a single payment, as from the Payments window.

Set the Open Invoices parameter to download the open installments of the selected supplier and operating unit as new rows to pay. In Create, Update mode existing Manual and Quick payments are downloaded; enter a Void Date to void one. Voiding reverses all invoice payments of the payment and leaves the invoices unpaid, unless Invoice Action puts them on hold or cancels them. Cancelling invoices is committed by Oracle Payables as it happens, as in the Payments window.

Invoices that would get withholding tax or an interest invoice at payment time cannot be paid by a Quick payment through the upload.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Upload Mode, Operating Unit, Trading Partner, Payment Date From, Payment Date To, Document Num, Open Invoices

Oracle EBS Tables Used

ap_checks_all, hr_all_organization_units_vl, ap_suppliers, ap_supplier_sites_all, ce_payment_documents, gl_daily_conversion_types, ap_invoice_payments_all, ap_invoices_all, ap_payment_schedules_all, mo_glob_org_access_tmp, ap_holds_all, ap_selected_invoices_all

Report Categories

Enginatics, R12 only, Upload

AP Accounted Invoice Aging, AP Invoices and Lines, IBY Payment Process Request Details, GL Account Distribution Analysis, PO Procure to Pay Tracker, AP Invoices and Lines 11i

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output Example
Blitz Report™ XML Import AP_Invoice_Payment_Upload.xml
SQL Source AP Invoice Payment Upload.sql
Full Report on Enginatics www.enginatics.com/reports/ap-invoice-payment-upload/

Case Study & Technical Analysis: AP Invoice Payment Upload

Executive Summary

The AP Invoice Payment Upload is an Excel upload that records Manual and Quick payments of open Oracle Payables invoices and voids payments, in the same way as the Payments window. Each row is one invoice installment paid, and rows with identical payment columns form one payment. Accounts Payable teams use it to pay large numbers of invoices, to void payments in bulk and to correct payment details before they are finalized. Blitz Report downloads the open installments and existing payments, and the upload writes the changes back through the Payables invoice and payment tables.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. Existing payments: AP_CHECKS_ALL is joined to AP_INVOICE_PAYMENTS_ALL on check id, and that row to its invoice and to the matching payment schedule. Reversal rows are excluded by the null reversal invoice payment id condition.
  2. Open installments: AP_INVOICES_ALL is joined to AP_PAYMENT_SCHEDULES_ALL and filtered to uncancelled, approved invoices whose installment is unpaid, not on hold and not in a check run.
  3. Exclusions: invoices with an unreleased hold in AP_HOLDS_ALL, or a row in AP_SELECTED_INVOICES_ALL, are left out of the open list.
  4. The two branches are combined with UNION ALL, so both have the same columns, with payment columns empty for open invoice rows.

Parameters & Filtering

Performance & Optimization

FAQ

Q: How do I pay open invoices from Excel in Oracle Payables? A: Set the Open Invoices parameter to Y with the supplier and operating unit. The open installments are downloaded as new rows, with Amount Paid filled in as the full amount remaining less the discount available at the Payment Date.

Q: How do I void an AP payment through the upload? A: Download the payment in Create, Update mode and enter a Void Date. Voiding reverses all the invoice payments of that payment, and the invoices become unpaid unless Invoice Action puts them on hold or cancels them.

© Enginatics GmbH