AR Transaction Upload

➜ Back to Library
XMLXML import file

Description

AR Transaction Upload creates Oracle Receivables invoices, debit memos and credit memos from Excel, with their lines, manual tax lines, freight lines, GL distributions and sales credits. It also creates credit memos against existing invoices and applies them in the same step. Each upload produces a result report showing every row with its status and message.

When to use it

  • Mass-create customer invoices from an external billing system or spreadsheet.
  • Load on-account credit memos or debit memos in bulk.
  • Credit existing invoices in full, in part or by line, and apply the credit memo directly.
  • Migrate historical transactions without tax calculation.
  • Create multi-line invoices with manual tax lines, freight lines, several distributions or split sales credits per line.

Before you start

  • The Receivables period of your GL date is open.
  • A transaction batch source and transaction types are set up for the operating unit.
  • The customer account is active and has an active bill-to site in the operating unit. Inactive accounts are not offered in the list of values.
  • AutoAccounting can derive the revenue and receivable accounts from the values you enter (transaction type, salesperson, item or standard memo line), or you supply the accounts on the rows.

Choose a template

TemplateUse it for
Default (default)Invoices, debit memos and on-account credit memos, with tax calculated by E-Business Tax.
Default (with attachment)The same, plus attachment columns to attach a file, URL or text to each transaction.
Historical Transactions UploadHistorical or migration loads. Sets Historical Transaction Load? to Yes, so no tax is calculated for the uploaded transactions.
Applied Credit MemoCrediting existing invoices at header or line level and applying the credit memo. Adds the Credit Against Transaction, Credit Against Line and credit amount columns and the credit method parameters.

Step 1 – Set the parameters

Open AR Transaction Upload in Blitz Report, choose the template and set the parameters:

ParameterMeaning
Operating UnitThe operating unit the transactions are created in.
SourceThe transaction batch source. It decides whether transaction numbers are assigned automatically or entered manually.
Default Transaction TypeTransaction type defaulted on new rows, initially the batch source’s default type.
Default Transaction CurrencyCurrency defaulted on new rows, initially the ledger currency.
Exchange Rate TypeRate type defaulted for foreign currency transactions.
Default Transaction DateTransaction date defaulted on new rows, initially today.
Default GL DateGL date defaulted on new rows: today, or the end of the latest open Receivables period if that is earlier.
Use SalesPersonsApplies only when the Receivables system options do not require a salesperson. Yes defaults a salesperson for the transaction; No creates it without one.
Derive Applied CM Batch SourceApplied Credit Memo template. Yes takes the credit memo batch source from the credited invoice; No uses the Source parameter.
Credit Method for RulesApplied Credit Memo template. How the credit is spread over the accounting rule periods of the credited invoice: LIFO, PRORATE (default) or UNIT.
Credit Method for InstallmentsApplied Credit Memo template. How the credit is spread over the installments of the credited invoice: LIFO (default), FIFO or PRORATE.
Historical Transaction Load?Historical Transactions Upload template. Yes bypasses tax calculation.
Blitz Report run screen for AR Transaction Upload with the Default template, Operating Unit Vision Operations, Source Manual and Default Transaction Type Invoice

Step 2 – Run to download the Excel file

Click Run. The Excel file downloads and opens with an empty sheet for the new transactions.

Excel file opened with an empty sheet for new transactions

Step 3 – Enter the transactions

Each row is one transaction line. Rows with the same Upload Trx Identifier make up one transaction, so repeat the identifier and the header values on every row of the transaction.

  • Header: Bill To Customer Name, Bill To Customer Number and Bill To Site are required. Trx Type, Trx Date, GL Date and Trx Currency default from the parameters; Terms and Salesperson default from the customer.
  • Transaction number: with automatic numbering, leave Trx Number blank. With manual numbering it is copied from the Upload Trx Identifier, and you can overwrite it. If the transaction type uses a manual document sequence, the Upload Trx Identifier becomes the document sequence value and must be numeric.
  • Standard lines: Line Type Line with a Line Item or Line Standard Memo, a Quantity and a Unit Price. The Line Amount is calculated.
  • Freight lines: Line Type Freight with a Line Amount, or a Quantity and Unit Price. Leave Link To Line Number blank for header freight, or enter the line number of the standard line for line freight.
  • Manual tax lines: Line Type Tax, linked to a standard line with Link To Line Number, with Tax Regime Code, Tax, Tax Jurisdiction, Tax Status, Tax Rate Name and Tax Rate. A blank Line Amount is calculated from the rate and the linked line. Tax and line freight rows must come after the line they link to.
  • Accounts: Distribution Account and Receivables Account override the accounts AutoAccounting derives. To split a line over several accounts, enter one row per distribution with a Distribution Percent or Distribution Amount. A line with an invoicing rule takes one distribution row per accounting class (Revenue plus Unearned Revenue or Unbilled Receivable).
  • Sales credits: Line Salesperson with a Line Salescredit Revenue Pct or Amt overrides the header salesperson for a line.
Two rows with the same Upload Trx Identifier forming one invoice for Hilman and Associates, with two consulting lines using standard memo lines, quantities and unit prices

Credit an existing invoice

Use the Applied Credit Memo template and enter the invoice in Credit Against Transaction, together with a Credit Memo Reason. The credit memo type and date are derived from the credited invoice.

  • Header level: one row with Credit Against Line blank. Leave Credit Line Amount, Credit Tax Amount and Credit Freight Amount blank to credit the full outstanding balance, or enter negative amounts for a partial credit.
  • Line level: one row per credited invoice line, identified in Credit Against Line, with a negative Credit Line Amount (it defaults to the full line amount). Credit Tax Amount and Credit Freight Amount stay blank; the credit method spreads the tax.

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values and runs the upload’s row checks (line type rules, tax columns, salesperson requirement, AutoAccounting, credit memo amounts), then saves the file. Correct any rows it flags before uploading.

Both invoice rows show status Valid after Validate and Save, with the line amounts calculated

Step 5 – Upload the file

In Blitz Report click Upload and select the saved file. This submits the upload request, which creates the transactions through the Receivables invoice API and completes them.

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, and the created Trx Number, Terms, Trx Amount and accounts.

Result report showing both lines with status Success, the created transaction number 12024, terms, transaction amount and revenue account

What’s produced

  • Completed Receivables invoices, debit memos and credit memos with their lines, tax, freight, distributions and sales credits.
  • Credit memos applied to the credited invoices.
  • A result report listing every row with a status (success or error) and a message.

Common questions

How do I change an existing transaction?
Use the AR Transaction Update Upload.

How do I put several lines on one invoice?
Give every row of the invoice the same Upload Trx Identifier and a unique Line Number.

What happens if one line of a transaction fails?
The whole transaction is not created. The failing row shows the error, and the other rows of the transaction show “One or more lines in the Invoice have errors”. Correct the row and upload the whole transaction again.

Do I have to enter a salesperson?
Only when the Receivables system options require one. Otherwise the Use SalesPersons parameter decides whether a salesperson is defaulted. AutoAccounting may still need a salesperson if it takes revenue account segments from it.

Why is my customer not in the list?
The list shows only active customer accounts with an active bill-to site in the selected operating unit.

Troubleshooting

MessageCauseWhat to do
Quantity and Unit Price are required on an Invoice LineA Line row without Quantity or Unit Price.Enter both; the line amount is calculated.
Link to Line Number cannot be specified for on an Invoice LineA Line row has a Link To Line Number.Clear it; only Tax and line Freight rows link to a line.
Amount and/or Quantity and Unit Price are required for Freight LinesA Freight row without an amount.Enter a Line Amount, or a Quantity and Unit Price.
Link to Line Number is required for Tax LinesA Tax row not linked to a standard line.Enter the Line Number of the standard line it belongs to.
Tax Regime Code, Tax, Tax Status, Tax Rate Name, and Tax Rate are required for Tax LinesA manual tax row with missing tax columns.Fill all tax columns.
Invalid Link To Line NumberThe linked line does not exist in the transaction, or comes after the Tax or Freight row.Correct the number, or move the row below the line it links to.
Line Number is not uniqueTwo rows of a transaction have the same Line Number.Number the lines uniquely.
Salesperson is required for this operating unitThe system options require a salesperson.Enter a Salesperson.
Please correct the revenue account assignmentAutoAccounting cannot build the revenue account, for example the salesperson, transaction type, item or memo line has no revenue account.Enter a salesperson, item or memo line that carries the account, or enter a Distribution Account.
A Payment Term cannot be derived from the Customers Bill To Site or Account. In this case the Payment Term must be specified on the invoice.Neither the bill-to site nor the account has payment terms.Enter Terms.
Unique Trx Identifier must be numeric when Manual Document Sequence Numbers are used.The transaction type uses a manual document sequence.Use numeric Upload Trx Identifiers.
Accounting Rule, Rule Duration, Rule Start Date and Rule End Date require an Invoicing RuleRule columns without an Invoicing Rule.Enter the Invoicing Rule, or clear the rule columns.
The GL Date is not in an open Receivables period.The credit memo GL date is in a closed period.Use a GL date in an open period.
A Credit Memo Reason is required.An applied credit memo without a reason.Enter a Credit Memo Reason.
The transaction to be credited has no outstanding balance to credit.The invoice is fully paid or credited.Check the invoice balance.
A header-level applied credit memo must be a single row. Use Credit Against Line to credit specific invoice lines.Several rows for one header-level credit.Keep one row, or credit by line with Credit Against Line on every row.
Credit Line Amount must be negative for a line-level creditA zero or positive Credit Line Amount with Credit Against Line.Enter a negative amount.
One or more lines in the Invoice have errorsAnother row of the same transaction failed.Correct the failing row and upload the transaction again.

SQL 464 lines

Parameters 12

ParameterTypeList of values SQL
Operating UnitLOV
SourceLOV
Default Transaction TypeLOV
Default Transaction CurrencyLOV
Exchange Rate TypeLOV
Default Transaction DateDate
Default GL DateDate
Use SalesPersonsLOV
Derive Applied CM Batch SourceLOV
Credit Method for RulesLOV
Credit Method for InstallmentsLOV
Historical Transaction Load?LOV