Upload Bill Reconciliation
Overview
The Batch Upload Reconcile Bills module allows users to reconcile multiple vendor bills in bulk using a CSV file. Instead of reconciling bills individually, users can download a predefined template, populate the required reconciliation details, and upload the file to process multiple records at once.
This feature simplifies reconciliation workflows, reduces manual effort, and improves accuracy when handling large volumes of bills.
Uploading Reconcile Bills
Reconcile bills in bulk by following the steps below:
Download the latest CSV template from the “Batch Upload Reconcile Bills” module by clicking on the Download Template option.
Copy reconciliation details into the CSV template.
Upload the CSV template into Xoro.
Downloading the CSV Template
Navigate to Menu → Utilities → Data Imports → Upload Bill Reconciliation → Batch Upload Reconcile Bills.
Click on “Download Template”.
Copying the Information into the Template
Once you’ve downloaded the template file, you can copy your data using any spreadsheet software. There are columns that you must complete and others that are optional. Mandatory fields are marked with a double asterisk (**) prefix.
Important Notes
The file must be a CSV (comma-separated values) file. The file name must end with .csv.
Do not change any column headings from row 1, as these are required for successful import.
All date fields must be in MM-DD-YYYY format.
Be mindful of leading zeros, as some spreadsheet tools may remove them automatically.
Ensure long numeric values (e.g., UPC) are not converted into scientific notation.
If importing more than 5,000 records, split the file into multiple uploads.
Batch Upload Reconcile Bills Template Fields
**BillNumber
Text
Enter the Bill Number to be reconciled.
**Date
Date
Enter the reconciliation date.
**ReconcileItemNumber
Text
Specifies the item number used for reconciliation.
ReconcileQty
Numeric
Enter the quantity to reconcile.
ReconcileTaxCodeName
Text
Defines the tax code applicable for reconciliation.
ReconcileCurrency
Text
Enter the currency used for reconciliation.
ExchangeRate
Numeric
Enter the exchange rate. If not provided, the system automatically retrieves it based on the Currency Handling Logic defined below.
**ReconcileItemPrice
Numeric
Enter the price for the reconciliation item.
BillItemLineId
Numeric
Enter the Bill Item Line ID. This value can be retrieved by downloading the required Bill Item Line from the Data Export Centre using the "Search By: Bill Items Detail" option.
**BillItemNumber
Text
This field is required only if BillItemLineId is not provided. For bills with duplicate Item Numbers, the first item will be selected by default.
ReconcileMethod
Text
Enter the reconciliation method (Amount, Cost, Qty).
AddToVendorBill
Boolean
Indicates whether the reconciliation entry should be added to the Vendor Bill. Enter TRUE to add it to the Vendor Bill, or FALSE to exclude it.
Vendor
Text
Enter the vendor name associated with the bill.
AccrualAccount
Text
Enter the accrual account name.
CreateExpenseBill
Boolean
Enter TRUE or FALSE to control expense bill creation. The final behavior depends on the “Create Expense Bills in Item Reconciliation” app configuration, as explained below.
VendorBillNumber
Text
Enter the vendor bill reference number.
ImportError
Text
Displays errors (if any) after upload.
Uploading the Template
Once you have filled out the CSV file with your data:
Click Browse to select the saved CSV template file.
Click Upload to start the import process.
During upload:
The system validates each record.
Reconciliation is processed based on provided data and system configurations.
After completion:
A success or error message is displayed.
If errors exist, download the error file, correct the issues, and re-upload.
Currency Handling Logic
The system determines exchange rate usage and validation based on the relationship between Bill Currency, Reconcile Currency, and Home Currency.
1. Bill Currency ≠ Reconcile Currency and Reconcile Currency = Home Currency
If an Exchange Rate is provided in the upload file, the system uses the entered exchange rate for reconciliation.
If the Exchange Rate is not provided, the system automatically retrieves the exchange rate for the Bill Currency.
The reconciliation amount is then converted to Home Currency using this exchange rate.
2. Bill Currency ≠ Home Currency
The Reconcile Currency can only be either Bill Currency or Home Currency.
If an Exchange Rate is provided in the upload file, the system uses the entered value during reconciliation.
If the Exchange Rate is not provided, the system automatically retrieves the exchange rate for the selected currency and applies it during reconciliation.
If any other currency (other than Bill Currency or Home Currency) is entered as the Reconcile Currency, the system throws an error stating that the Reconcile Currency must be either the Bill Currency or the Home Currency.
3. Bill Currency = Reconcile Currency
If the Exchange Rate is not provided, the system automatically assigns a value of 1.
If a different exchange rate is entered, the reconciliation details will display the entered exchange rate. However, all accounting transactions related to the bill will continue to use the original bill exchange rate. This may lead to discrepancies between reconciliation data and accounting records.
Expense Bill Creation Logic
This behavior depends on the app configuration “Create Expense Bills in Item Reconciliation.”
When enabled, an Expense Bill is always created.
When disabled, the system follows the value provided in the uploaded file: if the “CreateExpenseBill” field is set to TRUE, an Expense Bill is created; if set to FALSE, a Liability is created.
Additional Notes
Reconcile Tax Code: If not provided, the system defaults to the vendor’s default tax code. Taxes will apply only if the selected Reconcile Item is taxable.
Exchange Rate: If not entered, the system automatically retrieves the exchange rate as per the Currency Handling Logic. The exchange rate is applied only when the Reconcile Currency differs from the Bill Currency.
Reconcile Method: Defaults to Amount if not specified. Other available options include Cost, CBM, Qty, and Amount.
AddToVendorBill: Defaults to TRUE if no value is provided.
Reconcile Currency: Defaults to the vendor’s currency if not specified, ensuring consistency with the vendor’s billing currency.
Last updated
Was this helpful?