AP Invoice Upload

➜ Back to Library
XLSExcel example XMLXML import file

Description

AP Invoice Upload creates new Oracle Payables invoices – headers and lines, with optional PO matching, project and asset details, tax lines, descriptive flexfields and attachments – from Excel. It stages the invoices in the Payables open interface, runs the standard Payables Open Interface Import for each operating unit and can validate each imported invoice immediately. It creates new invoices and adds lines to existing unpaid invoices; it does not change or delete existing lines.

When to use it

  • Mass-load new supplier invoices instead of keying them in the Invoice Workbench.
  • Load PO-matched invoices by referencing the PO line, shipment, distribution or receipt.
  • Load non-PO invoices coded to a distribution account, an account alias or a distribution set.
  • Split an invoice line across several distribution accounts by percent or amount.
  • Create project-related or asset-tracked invoice lines.
  • Enter invoices with manual tax lines, or attach a document to each invoice.
  • Load and immediately validate invoices in one step.
  • Add lines to an existing unpaid invoice.

Before you start

  • Your responsibility has access to the operating unit.
  • The supplier, an active pay site and the invoice source exist.
  • The Payables period of the invoice GL date is open.
  • For PO-matched lines, the purchase order or receipt exists and is open for matching.

Choose a template

TemplateUse it for
Default (default)Standard invoices: header, lines, distribution account or set, distribution splits, PO matching, item, asset, project, tax driver and remittance columns.
Default (with attachment)The Default columns plus the invoice, line and distribution descriptive flexfields and the attachment columns (Attachment Category, Title, Description, Type, Content).
Manual Tax Lines TemplateInvoices with explicit tax lines: adds Taxation Country, Tax Regime, Tax, Tax Status, Tax Jurisdiction, Tax Rate Name, Tax Rate, Line Prorate Group and Prorate Flag, plus the descriptive flexfields.
Add Lines to Existing InvoiceAdding lines to existing invoices: the columns that identify the invoice, Add To Existing Invoice, Invoice Date, GL Date, Invoice Amount and the line, distribution, project, asset and tax driver columns.

Step 1 – Set the parameters

Open AP Invoice Upload in Blitz Report, choose the template and set the parameters:

ParameterMeaning
Operating UnitDefault operating unit for new rows. Each row can carry its own operating unit.
Invoice SourceThe Payables invoice source the invoices are imported under (required).
Batch NameBatch name passed to the Payables Open Interface Import; the upload request id is appended to it (required, defaults to N/A when invoice batch control is not used).
GL DateDefault GL Date for new rows and GL date of the import.
Submit Invoice ValidationYes (default) validates each imported invoice immediately.
Default Line Amount?Yes defaults a blank Line Amount to the Invoice Amount, for invoices with a single line. Blank by default.
Blitz Report run screen for AP Invoice Upload with Operating Unit Vision Operations, Invoice Source Import, Batch Name, GL Date 15-DEC-2012 and Submit Invoice Validation Yes

Step 2 – Run to download the Excel file

Click Run. An empty Excel sheet downloads and opens, ready for new invoices.

Downloaded Excel file with an empty upload sheet for new invoices

Step 3 – Enter the invoices

Enter one row per invoice line and repeat the header values on every line of the same invoice. Rows with the same Operating Unit, Supplier Name, Supplier Number, Supplier Site and Invoice Number form one invoice, and all their header columns (Invoice Type, Invoice Date, GL Date, Invoice Amount, Invoice Currency, Terms, exchange rate and payment columns) must be identical.

  • Header: Invoice Type (defaults to Standard), Invoice Number, Invoice Date, Invoice Amount and Invoice Currency are required; Terms, Payment Method, exchange rate and the other header columns are optional and otherwise default from the supplier site as in Payables. Supplier Site defaults to the supplier’s primary pay site in the operating unit, or to its only active pay site there.
  • Lines: Line Number, Line Type (defaults to Item) and Line Amount are required. With Default Line Amount? set to Yes, a blank Line Amount defaults to the Invoice Amount.
  • Code a non-PO line with a Distribution Account, a Distribution Account Alias (fills the Distribution Account) or a Distribution Set.
  • To split a line across several accounts, enter one row per distribution with the same Line Number and line values, and on every row a Distribution Line Number, a Distribution Account and either a Distribution Percent (adding up to 100) or a Distribution Amount (adding up to the Line Amount). Percent amounts are rounded to the currency, with the remainder on the largest distribution. Item, Freight and Miscellaneous lines can be split, except lines matched to a PO or receipt, lines using a distribution set and prorated lines; a project line only when the profile PA: Allow Override of PA Distributions in AP/PO is Yes.
  • Match a line with PO Number and PO Line Num (optionally PO Release Num, Po Shipment Num, PO Distribution Num) or Po Receipt Num and Po Receipt Line Num; Line Description, UOM and Inventory Item default from the PO line.
  • Optional line details: Quantity and Unit Price, the project columns (Project, Task, Expenditure Type, Expenditure Organization, Expenditure Item Date), the asset columns (Track As Asset, Asset Book, Asset Category) and the tax driver columns (Tax Classification, Ship To Location, Product Fiscal Classification and others).
AP Invoice Upload sheet with invoice line 1 entered on two rows, split 60 and 40 percent across two distribution accounts

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values and saves the file. Correct any rows it flags before uploading.

Both invoice lines show status Valid after Validate and Save

Step 5 – Upload the file

In Blitz Report click Upload and select the saved file. This submits the upload request, which checks each row, stages the invoices, runs the Payables Open Interface Import for each operating unit and, if requested, validates the imported invoices.

File Upload page with the saved Excel file selected for upload

Step 6 – Review the result report

When the request completes, a result report opens listing every uploaded row with its status and message. An imported line shows one row per distribution, including the distributions Payables creates from a distribution set, a purchase order match or a prorated Freight or Miscellaneous line. Rows of imported invoices show Success; rows rejected by the upload or by the import show Error with the reason, including the Payables interface rejection reasons.

Result report showing both lines of invoice OSI-121215 with status Success and the payment terms defaulted from the supplier site

Adding lines to an existing invoice

Choose the Add Lines to Existing Invoice template and enter one row per new line, with the Operating Unit, Supplier, Supplier Site, Invoice Type, Invoice Number and Invoice Currency of the existing invoice. Add To Existing Invoice is Yes on every new row and is accepted only when the invoice exists, so a mistyped invoice number is rejected instead of creating a new invoice.

  • Invoice Amount is the amount added to the invoice, including the tax of the added lines, the same on every row of the invoice. The invoice’s Invoice Amount is raised by it and its payment schedules are recalculated; the result report shows the new Invoice Amount.
  • The other invoice details are not changed. GL Date is the accounting date of the new lines, the invoice’s GL Date when left blank; a date in a closed period moves to the next open one.
  • Item, Freight and Miscellaneous lines can be added, coded with a Distribution Account, a Distribution Set or a split across several distributions, with project, asset and tax driver details. Lines matched to a purchase order or receipt, prorated lines and India Localization taxes cannot be added.
  • The invoice must be unpaid, not cancelled, not selected for payment and not in the approval workflow, and its Invoice Amount must be changeable in the Invoice Workbench.
  • All new lines of an invoice are added together or not at all. The invoice is then validated as set by Submit Invoice Validation.
AP Invoice Upload sheet with the Add Lines to Existing Invoice template, two rows adding lines to an existing invoice

India Localization (GST and TDS)

On instances with Oracle India Localization installed, the upload has two additional columns: Section Code (the TDS section for the line, for example 194C) and Tax Category (the GST tax category for the line). Enter them on the invoice line row; they apply to all distributions of that line. Left blank, the standard India Localization defaulting applies. After import and validation the values are applied and the India Localization taxes are regenerated, as for an invoice entered in the Invoice Workbench. Values are accepted only for operating units registered for India Localization. The outcome is written to the log of the upload request.

What’s produced

  • New Payables invoices with their lines, distributions, tax, flexfields and attachments as entered, validated if Submit Invoice Validation is Yes.
  • Lines added to existing invoices, with the Invoice Amount and payment schedules updated.
  • On India Localization instances, the entered Section Code and Tax Category applied to the distributions and the GST/TDS taxes regenerated.
  • A result report listing every row with a status and a message, with one row per distribution of each imported line.

Common questions

Can I update or delete existing invoices with this upload?
No. It creates new invoices and adds lines to existing unpaid invoices, but does not change or delete existing lines.

What happens if one line of an invoice fails?
The whole invoice is held back. Its other lines show “One or more lines in the invoice have errors”; correct the failing line and upload the complete invoice again.

What stops a duplicate invoice?
A row is rejected when an invoice with the same Operating Unit, Supplier, Supplier Site and Invoice Number already exists, unless Add To Existing Invoice is Yes.

Does it validate the invoice after import?
Yes if Submit Invoice Validation is Yes, and always when distribution flexfield values or India Localization values were entered. Otherwise the invoices are imported unvalidated.

Why does a successfully uploaded invoice have a Line Variance or Cannot Execute Allocation hold?
Payables calculated tax lines from your tax setup and the Invoice Amount does not include that tax. Enter the Invoice Amount including tax, or release the Line Variance hold in Payables. A Freight or Miscellaneous line with Prorate Flag Yes is not prorated in that case and gets no distributions, which places the Cannot Execute Allocation and Distribution Variance holds: correct the Invoice Amount in the Invoice Workbench and validate the invoice again. Holds placed by validation do not appear in the result report.

Do I have to enter tax lines?
No. Payables calculates tax from your tax setup. Use the Manual Tax Lines Template only when you need to enter tax lines explicitly.

How do I attach a document to an invoice?
Use the Default (with attachment) template, fill Attachment Category and Attachment Type, and enter the text, URL or file path in Attachment Content. For a file, double-click the cell to pick it, then click Upload Attachments before uploading.

Troubleshooting

MessageCauseWhat to do
This upload does not support an Update ActionA row’s Action is not Create.Use the upload only to create new invoices.
Invoice Number already exists for this Operating Unit, Supplier, and Supplier Site.The invoice already exists in Payables.Correct the invoice number, or add the lines with the Add Lines to Existing Invoice template.
Add To Existing Invoice is invalidNo invoice with this Operating Unit, Supplier, Supplier Site and Invoice Number exists.Correct the invoice columns, or create the invoice with the Default template.
Lines can be added only to an unpaid invoice.The invoice is paid or partly paid.Enter the lines on a new invoice.
Line Number … already exists on the invoice.The invoice already has a line with this number, for example from an earlier upload of the same rows.Use line numbers the invoice does not have yet.
Invoice Amount differs from the first row for this Invoice.Rows adding lines to the same invoice carry different Invoice Amounts.Enter the total amount added on every row of the invoice.
… cannot be added to an existing invoice. / Only Item, Freight and Miscellaneous lines can be added to an existing invoice.The line is matched to a purchase order or receipt, prorated, of another line type, or carries India Localization taxes or line reference keys.Enter the line in the Invoice Workbench.
Lines cannot be added while the invoice is in the approval workflow.Invoice approval has started.Upload the rows again once the approval has completed.
Could not reserve record.Another user or program has the invoice locked.Upload the rows again once the invoice is free.
Operating Unit is invalid or not accessible to this Responsibility.The operating unit is not granted to your responsibility.Upload from a responsibility with access to that operating unit.
Invoice Source is invalid.No invoice source, or an expense report source.Pick an Invoice Source from the list.
Distribution Account ErrorThe Distribution Account combination does not validate.Correct the account, or pick it from the list.
The Distribution Percent of line … adds up to … It must add up to 100. / The Distribution Amount of line … adds up to … It must add up to the Line Amount …The distributions of a split line do not add up.Correct the percentages or amounts on the rows of that line.
Line … is split across several distributions: enter a Distribution Percent or a Distribution Amount on each of its rows.Several rows carry the same line number without a percent or amount.Enter a percent or an amount on each row, or give the rows their own line numbers.
Line Data differs from the first row for this LineRows of the same split line carry different line values.Make the line columns identical on every row of the line.
Distribution …: …Payables or Projects rejected a distribution after the import. The invoice is imported without distributions for that line and validation places a Distribution Variance hold.Enter the line’s distributions in the Invoice Workbench and validate the invoice.
Invoice DFF / Line DFF / Distribution DFF: …A descriptive flexfield value does not validate.Correct the flexfield values named in the message.
Invoice Header Data differs from the first row for this InvoiceLines of the same invoice carry different header values.Make the header columns identical on every line of the invoice.
One or more lines in the invoice have errorsAnother line of the same invoice failed.Fix the failing line; the invoice is processed as a whole.
Payables Open Interface Import did not import this line. Please check the Payables Open Interface Import requestThe import did not process the invoice.Check the log and output of the Payables Open Interface Import request.

SQL 343 lines

Parameters 6

ParameterTypeList of values SQL
Operating UnitLOV
Invoice SourceLOV
Batch NameChar
GL DateDate
Submit Invoice ValidationLOV Oracle
Default Line Amount?LOV