AP Invoice Payment Upload
Description
AP Invoice Payment Upload records Manual and Quick payments of open Payables invoices and voids existing payments from Excel, the equivalent of entering a payment and selecting its invoices in the Payments window. Each payment is created through the same Payables and Oracle Payments processing as the window, and a result report lists every row with its outcome.
When to use it
- Record a batch of payments already issued outside Payables (manual checks, wires) against open invoices.
- Pay one or several installments of a supplier by Quick payment, in full or partially.
- Void Manual or Quick payments, optionally putting their invoices on hold or cancelling them.
Before you start
- Oracle EBS R12. The upload is available from responsibilities with access to the Payables Payments window.
- The Payment Date (and the Void Date when voiding) must be in an open Payables period.
- The internal bank account must be enabled for Payables use in the operating unit, with a payment method and payment process profile applicable to it. A Quick payment with a printed payment process profile also needs a payment document on that bank account.
- Invoices to pay must be validated, approved, not on hold and not selected by a payment process request.
- The void invoice actions follow the Payments window: Put Invoices on Hold needs function Payment Invoice Holds, Cancel Invoices needs function Payment Invoice Cancel.
Choose a template
| Template | Use it to |
|---|---|
| Default (default) | Enter Manual and Quick payments of open invoices and void downloaded payments, with all fields of the Payments window. Upload Mode and Open Invoices can be set freely. |
| Pay Open Invoices | Download the open installments of a supplier as rows to pay. Upload Mode is fixed to Create and Open Invoices to Yes; the payment date and document number filters are hidden. Enter the payment columns on the rows to pay and delete the others. |
| Void Payments | Download existing Manual and Quick payments to void them. Upload Mode is fixed to Create, Update; Open Invoices is hidden. |
Step 1 — Select the template and parameters
Open the upload in Blitz Report, pick the template and set the parameters:
| Parameter | Effect |
|---|---|
| Upload Mode | Create gives an empty sheet (plus open installments when Open Invoices is set). Create, Update also downloads existing Manual and Quick payments, one row per invoice paid. |
| Operating Unit | Restricts downloaded payments and open installments to one operating unit and defaults it on new rows. |
| Trading Partner | Restricts downloaded payments and open installments to one supplier. |
| Payment Date From / To | Restricts the downloaded existing payments by payment date. |
| Document Num | Downloads the existing payment with this document number. |
| Open Invoices | Downloads the open installments of the Operating Unit and Trading Partner as new rows to pay. |
Step 2 — Run and open the Excel file
Click Run. The upload workbook (.xlsm) downloads and opens in Excel, with downloaded payments or open installments already filled in according to the parameters.
Step 3 — Enter the payments
Each row is one invoice installment paid, with the payment columns repeated on every row. Rows with identical payment columns (Operating Unit, Payment Type, Payment Date, Trading Partner, Supplier Site, Bank Account, Payment Method, Payment Process Profile, Payment Document, Document Num, Currency and the exchange rate columns) are paid by one payment.
- Payment Type: Manual records a payment issued outside Payables and needs its Document Num. Quick takes the next number of the Payment Document when Document Num is left blank, and is submitted to Oracle Payments as a single payment. Payment Document is required for a Quick payment with a printed payment process profile.
- Payment Date defaults to today. Supplier Site, Payment Method, Currency and Payment Num default from the invoice when they are unambiguous.
- Amount Paid defaults to the amount remaining less the discount available at the Payment Date, Discount Taken to that discount. Reduce them to pay an installment partially.
- For a foreign currency payment, Exchange Rate Type defaults from the Payables options and Exchange Date from the Payment Date; Exchange Rate is required for rate type User and derived from the daily rates otherwise.
- The Payment Process Profile list offers only profiles applicable to the operating unit, payment method, currency and bank account.
Step 4 — Void payments (optional)
Download existing payments with the Void Payments template (or Upload Mode Create, Update). On the rows of a payment to void:
- Enter a Void Date in an open period, not before the Payment Date. Voiding reverses all invoice payments of the payment and leaves the invoices unpaid.
- Optionally set Invoice Action: Put Invoices on Hold requires a Hold Name (user-releasable holds only; Hold Reason defaults from the hold), Cancel Invoices cancels the invoices of the payment.
Other columns of a downloaded payment cannot be changed.
Step 5 — Validate and Save
Use Validate and Save in the Blitz Report Excel add-in. It checks the entered rows for missing required values, marks them as valid and saves the workbook.
Step 6 — Upload and view the result
Back in Blitz Report, click Upload and select the saved file. This submits the Blitz Upload request; when it finishes, a result report opens listing every row as success or error with its message.
What’s produced
- New payments: the payment, its invoice payments and its accounting event, created as from the Payments window. A Quick payment is submitted to Oracle Payments as a single payment and gets its document number there. Every row of the payment shows Payment <number> created.
- Voided payments: the invoice payments are reversed and the payment gets status Voided with the Void Date. The rows show Payment <number> voided., followed by the count of cancelled and not cancelled invoices for Cancel Invoices.
- Downloaded payments left unchanged show No change.
- The result report shows the values stored in Payables, including the Payment Amount and Payment Status of each payment.
Common questions
How are rows grouped into payments?
Rows whose payment columns are identical form one payment. Change any of them, for example the Document Num, to start another payment.
What happens when one row of a payment fails?
Each payment is processed as a whole: none of its rows are saved, the failing row shows its error and the others show Not saved because of errors in other rows of this payment. Other payments in the file are not affected.
Can I pay an installment partially?
Yes, reduce Amount Paid (and Discount Taken). Their sum cannot exceed the amount remaining, and a prepayment must be paid in full.
Can I change an existing payment?
No. A downloaded payment can only be voided.
Why does a Quick payment reject some invoices?
Invoices that would get withholding tax or an interest invoice at payment time cannot be paid by a Quick payment through the upload. Pay them with a Manual payment or in the Payments window.
Are cancelled invoices rolled back if the void fails afterwards?
No. Cancelling invoices is committed by Oracle Payables as it happens, as in the Payments window.
Troubleshooting
| Message | Cause | What to do |
|---|---|---|
| Document Num is required for a Manual payment. | Manual payment without a document number. | Enter the number of the issued document. |
| Document Num <n> is already used by a payment from Bank Account “<x>”. | Another active payment from the bank account has this number. | Correct the number. |
| Invoice <x> installment <n> cannot be paid. It is paid, on hold, cancelled, not validated or not approved, or is selected by a payment process request. | The installment is not ready to pay. | Resolve the hold, validation or approval, or remove the row. |
| Amount Paid plus Discount Taken (<a>) exceeds the amount remaining (<b>) of invoice <x> installment <n>. | Overpayment of the installment. | Reduce Amount Paid or Discount Taken. |
| Invoice <x> installment <n> occurs more than once in this payment. | Duplicate row within one payment. | Combine the rows into one. |
| Invoice <x> is payable in <currency>, not in the payment currency <currency>. | Payment currency differs from the invoice payment currency. | Pay it in a payment of its own currency. |
| Invoice <x> must be paid alone. | The invoice is flagged for exclusive payment. | Give it its own payment (different payment columns). |
| Invoice <x> has a different remit-to supplier site than the other invoices of this payment. | Invoices of one payment must share the remit-to site. | Split them into separate payments. |
| Bank Account “<x>” does not allow payments in currency <c>. | Single-currency bank account. | Use a bank account in the payment currency or a multi-currency account. |
| An existing payment cannot be changed. Enter a Void Date to void it. | A downloaded payment row was edited. | Revert the change, or void the payment and enter a new one. |
| Invoice Action and Hold Name apply only when voiding a payment. | Invoice Action or Hold Name without a Void Date. | Enter a Void Date or clear the columns. |
| Void Date cannot be before the Payment Date. | Void Date earlier than the payment. | Correct the Void Date. |
| Payment <n> with status <status> cannot be voided. | Only negotiable, issued and stop-initiated payments can be voided. | Check the payment status in Payables. |
| Invoice Action “<x>” is not allowed for the current responsibility. | The responsibility lacks the hold or cancel function of the Payments window. | Use a responsibility with that function. |
| Not saved because of errors in other rows of this payment. | Another row of the same payment failed. | Fix the failing row and upload the payment again. |
select null action_, null status_, null message_, null modified_columns_, x.operating_unit, x.payment_type, x.payment_date, x.trading_partner, x.supplier_num, x.supplier_site, x.bank_account, x.payment_method, x.payment_process_profile, x.payment_document, x.document_num, x.currency, x.exchange_rate_type, x.exchange_date, x.exchange_rate, x.payment_amount, x.payment_status, x.void_date, x.invoice_action, x.hold_name, x.hold_reason, x.invoice_num, x.invoice_date, x.payment_num, x.due_date, x.amount_remaining, x.discount_available, x.amount_paid, x.discount_taken, x.check_id, x.invoice_payment_id, null upload_row from ( select haouv.name operating_unit, xxen_util.meaning(aca.payment_type_flag,'PAYMENT TYPE',200) payment_type, aca.check_date payment_date, aps.vendor_name trading_partner, aps.segment1 supplier_num, assa.vendor_site_code supplier_site, xxen_ap_upload.bank_account_name(aca.ce_bank_acct_use_id) bank_account, xxen_ap_upload.payment_method_name(aca.payment_method_code) payment_method, xxen_ap_upload.payment_profile_name(aca.payment_profile_id) payment_process_profile, cpd.payment_document_name payment_document, aca.check_number document_num, aca.currency_code currency, gdct.user_conversion_type exchange_rate_type, aca.exchange_date, aca.exchange_rate, aca.amount payment_amount, xxen_util.meaning(aca.status_lookup_code,'CHECK STATE',200) payment_status, aca.void_date, null invoice_action, null hold_name, null hold_reason, aia.invoice_num, aia.invoice_date, aipa.payment_num, apsa.due_date, apsa.amount_remaining, to_number(null) discount_available, aipa.amount amount_paid, aipa.discount_taken, aca.check_id, aipa.invoice_payment_id from ap_checks_all aca, hr_all_organization_units_vl haouv, ap_suppliers aps, ap_supplier_sites_all assa, ce_payment_documents cpd, gl_daily_conversion_types gdct, ap_invoice_payments_all aipa, ap_invoices_all aia, ap_payment_schedules_all apsa where 1=1 and aca.payment_type_flag in ('M','Q') and aca.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat) and aca.org_id=haouv.organization_id and aca.vendor_id=aps.vendor_id and aca.vendor_site_id=assa.vendor_site_id and aca.payment_document_id=cpd.payment_document_id(+) and aca.exchange_rate_type=gdct.conversion_type(+) and aca.check_id=aipa.check_id and aipa.reversal_inv_pmt_id is null and aipa.invoice_id=aia.invoice_id and aipa.invoice_id=apsa.invoice_id and aipa.payment_num=apsa.payment_num union all select haouv.name operating_unit, null payment_type, to_date(null) payment_date, aps.vendor_name trading_partner, aps.segment1 supplier_num, assa.vendor_site_code supplier_site, null bank_account, xxen_ap_upload.payment_method_name(apsa.payment_method_code) payment_method, null payment_process_profile, null payment_document, to_number(null) document_num, aia.payment_currency_code currency, null exchange_rate_type, to_date(null) exchange_date, to_number(null) exchange_rate, to_number(null) payment_amount, null payment_status, to_date(null) void_date, null invoice_action, null hold_name, null hold_reason, aia.invoice_num, aia.invoice_date, apsa.payment_num, apsa.due_date, apsa.amount_remaining, to_number(null) discount_available, to_number(null) amount_paid, to_number(null) discount_taken, to_number(null) check_id, to_number(null) invoice_payment_id from ap_invoices_all aia, hr_all_organization_units_vl haouv, ap_suppliers aps, ap_supplier_sites_all assa, ap_payment_schedules_all apsa where 2=2 and :open_invoices='Y' and aia.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat) and aia.org_id=haouv.organization_id and aia.vendor_id=aps.vendor_id and aia.vendor_site_id=assa.vendor_site_id and aia.invoice_id=apsa.invoice_id and aia.cancelled_date is null and nvl(aia.payment_status_flag,'N')<>'Y' and aia.wfapproval_status in ('WFAPPROVED','NOT REQUIRED','MANUALLY APPROVED') and nvl(apsa.payment_status_flag,'N')<>'Y' and nvl(apsa.hold_flag,'N')='N' and apsa.checkrun_id is null and aia.invoice_id not in (select aha.invoice_id from ap_holds_all aha where aha.release_lookup_code is null) and aia.invoice_id not in (select asia.invoice_id from ap_selected_invoices_all asia) and ap_invoices_pkg.get_approval_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code) not in ('NEVER APPROVED','NEEDS REAPPROVAL','UNAPPROVED') ) x |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Upload Mode |
| LOV | |
| Operating Unit |
| LOV | |
| Trading Partner |
| LOV | |
| Payment Date From |
| Date | |
| Payment Date To |
| Date | |
| Document Num |
| Number | |
| Open Invoices | LOV |