5.2 Importing Invoices: File-Based Data Import, Import Payables Invoices and Corrections
Key Takeaways
The Payables Standard Invoice Import FBDI template is PayablesStandardInvoiceImportTemplate.xlsm; don't reorder or delete its columns, though unused ones can be hidden.
Load Interface File for Import loads the generated file into the open interface tables, and Import Payables Invoices then creates the invoices.
Import Payables Invoices takes the parameters Business Unit, Ledger, Source, Import Set, Invoice Group, Hold and Hold Reason, Accounting Date, Purge, Summarize Report and Calculate Tax During Import.
Each import request processes the source and import set combination you specify, so unique import sets let several imports for the same source run separately and faster.
If no accounting date is given as a parameter or on the invoice, import assigns one using the Accounting Date Basis invoice option.
Invoice import is part of Describe Supplier Invoices processing. It matters in practice for conversions (loading open invoices at go-live) and for integrations with third-party billing, EDI or OCR systems. Hands-on items may show a rejected record or a set of import parameters and ask what happened.
The FBDI template
Oracle provides the Payables Standard Invoice Import file-based data import (FBDI) template, PayablesStandardInvoiceImportTemplate.xlsm, from the File-Based Data Import for Oracle Financials Cloud guide. Oracle's rules for the worksheets:
- Enter the required information in each column. The tooltips on the column headers explain the columns.
- Don't change the column order. The load fails if you do.
- Don't delete columns. Hide or skip the ones you don't use; deleting them makes the load fail.
The template has two data worksheets that match the two open interface tables:
| Interface table | Holds | Key point |
|---|---|---|
| AP_INVOICES_INTERFACE | Invoice headers: invoice number, supplier and site, amount, currency, dates, terms, business unit, source and more | INVOICE_ID is a unique identifier that you give to the header and repeat on its lines |
| AP_INVOICE_LINES_INTERFACE | Lines and distribution data: line type, amount, PO matching references, distribution combination or distribution set, project data and more | Joined to the header through INVOICE_ID |
The template also accepts URL attachments (HTTP or HTTPS, several separated by delimiters). You can't attach files themselves through the template.
The two-step load
- Generate the file from the template's macro (a .zip of CSV files) and upload it, typically to the fin/payables/import account in the Oracle content repository.
- Run Load Interface File for Import with the import process Import Payables Invoices. This loads the file into AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE.
- Run Import Payables Invoices. It validates the interface data and creates the invoices. You can also schedule it, for example to import Supplier Portal, e-invoicing or IDR-sourced records regularly.
After import, you search for the invoices in Payables, then validate and approve them like any others.
Import Payables Invoices parameters
| Parameter | Effect |
|---|---|
| Business Unit, Ledger | Limit the import |
| Source | The invoice source to import. For FBDI files, select External. Other sources include Spreadsheet and e-invoicing sources. |
| Import Set | Limits the import to records with that import set. You can import the same source with a unique import set for each request, which reduces processing time. Each run processes the source and import set combination you specify. |
| Invoice Group | Assigns imported invoices to an invoice group. For the Spreadsheet source, enter the spreadsheet invoice group name. |
| Hold and Hold Reason | Place a named hold on every imported invoice, for example to review a conversion batch before payment |
| Accounting Date | Used for any invoice without an accounting date on the header or line. If none is given anywhere, import uses the Accounting Date Basis invoice option. |
| Purge | Deletes successfully imported records that match the business unit, source and import set, including records from earlier runs. Records whose invoices weren't created stay in the tables. |
| Summarize Report | Summarizes the audit section of the import report. Without it, a large import produces a very long report. |
| Calculate Tax During Import | N accepts manual tax lines from the file. Y rejects invoices that contain tax line information and lets Oracle Fusion Tax calculate the tax. |
The Import Payables Invoices Report is generated automatically. It lists the invoices that imported and the reasons others failed.
Fixing rejected records
Rejected records stay in the interface tables with rejection reasons. Typical causes:
- an inactive or unknown supplier site;
- a site with no active assignment for the business unit;
- an invalid distribution combination;
- a PO from a different legal entity or one that isn't open;
- a duplicate invoice number for the supplier;
- header and line amounts that don't agree.
To correct them, use the import corrections spreadsheet (Correct Import Errors in the Invoices work area). It downloads the rejected records, lets you edit them, and you resubmit the import. Oracle's period-close checklist includes this step: run Import Payables Invoices, and if records are rejected, review the import corrections spreadsheet and resubmit.
How import interacts with other setups
- Invoice options still apply. Payment terms, terms date basis, pay group and accounting date basis default as for manual invoices. Supplier site values take precedence (section 3.1).
- Approval. Imported invoices go through invoice approval unless their source is set to skip approval with the AP_SKIP_SOA_APPROVAL lookup (section 4.2). That lookup doesn't affect manual or REST invoices.
- User-defined holds are evaluated for imported invoices like any other source (section 4.1).
- Conversion design.
- Load only open invoices, unpaid and partly paid, with the remaining amount, and load the history as general ledger balances.
- Use an import Hold to stop the batch being paid before review.
- Use unique import sets to split large volumes into separate requests.
- Payment requests use their own import process (Import Payables Payment Requests) and template, because the payees aren't suppliers.
Exam pattern
A conversion team loads 50,000 invoices with Source External and import set CONV1, and 120 records are rejected for invalid accounts. The fix is to correct the records with the corrections spreadsheet and rerun the import for the same source and import set, not to reload the whole file. Setting Purge to Yes then deletes the successfully imported CONV1 records from the interface tables, while the rejected ones stay until they import.
A conversion team edits the PayablesStandardInvoiceImportTemplate.xlsm workbook. Which change will make the load process fail?
Hiding columns the conversion doesn't use
Leaving optional columns blank
Deleting unused columns or changing the column order
Entering URL attachments separated by delimiters
An integration loads invoices with Source External and runs four Import Payables Invoices requests at once. Each request must process only its own batch. Which parameter does this?
Invoice Group, because it controls which interface rows each request selects
Import Set, with a unique value so each request takes only its own batch
Summarize Report, because it splits the audit output into separate requests
Calculate Tax During Import, because it partitions records by tax line
Imported invoices have no accounting date on the header or lines, and the Accounting Date parameter is left blank. How does Import Payables Invoices assign the accounting date?
It uses the system date in every case
It rejects the invoices because the accounting date is required in the file
It uses the first day of the current open period
It uses the Accounting Date Basis set in Manage Invoice Options for the business unit
Sections you finish are checked off in the contents.