Invoices

Used to query, insert, and update invoices within Xero. Invoices are sent to customers for payment for goods or services provided.

Table Specific Information

The Invoices table allows you to SELECT, INSERT, UPDATE, and DELETE invoice line items for a Xero organization. The Id column is generated by the connector this value combines the index of the line item with the unique, Xero-generated InvoiceId.

Optimized Filters and High Volume Thresholds

The Xero API defines a list of optimized filters for certain tables and views. If any query filter not included in this list is applied to any such table or view, and the number of records available in that table or view exceeds a certain value (what Xero calls a "high volume threshold"), the Xero API will block the query.

To avoid the rejection of your queries by the Xero API, if the the number of records in your table or view exceed the high volume threshold, make sure to use filters from the list of optimized filters.

Note that the high volume threshold, as well as the exact list of optimized filters, is subject to change as Xero updates its API.

The Invoices table has a high volume threshold of 500,000. The Xero API lists the following optimized filters for this table:

Optimized columns (when used in WHERE clauses):
- Status
- Contact.ContactID
- Contact.Name
- Contact.ContactNumber
- Reference
- Date
- Type
- AmountDue
- AmountPaid
- DueDate
- SearchTerm
Optimized ORDER BY columns:
- InvoiceId
- UpdatedDateUTC
- Date

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. To create a new invoice, the Type and ContactName fields must be set in addition to at least one line item. Note that also at least the LineItem_LineAmount or LineItem_UnitAmount are also required fields for the LineItem aggregate.

In addition to inserting a single row, line item tables offer two additional ways to insert into a table.

You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the Invoices API for more details.

INSERT INTO Invoices (Type, ContactName, LineItemAggregate) VALUES (
  'ACCPAY',
  'Boom FM',
  '<LineItem>
	<ItemCode>12</ItemCode>
	<Description>Onsite project management </Description>
	<Quantity>1.0000</Quantity>
	<UnitAmount>1800.00</UnitAmount>
	<TaxType>OUTPUT</TaxType>
	<TaxAmount>225.00</TaxAmount>
	<LineAmount>1800.00</LineAmount>
	<AccountCode>200</AccountCode>
	<AccountId>4f2a3169-8454-4012-a642-05a88ef32982</AccountId>
	<Tracking>
		<TrackingCategory>
			<Name>Activity/Workstream</Name>
			<Option>Onsite consultancy</Option>
		</TrackingCategory>
	</Tracking>
	<LineItemID>52208ff9-528a-4985-a9ad-b2b1d4210e38</LineItemID>
   </LineItem>
   <LineItem>
	...
   </LineItem>'
)

Note: This method must be used for inserting multiple new Invoices in a single query.

You can also insert a new line item on an existing object by specifying the Xero-generated value of the existing record. For example, adding a line item to an existing invoice:
```
INSERT INTO Invoices (InvoiceId, LineItem_Description, LineItem_Quantity, LineItem_UnitAmount) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'New Line Item Desc', 1, 10)
```

Note: Inserting into existing records will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

DELETE

The Xero API has limited support for deleting any object. In order to delete objects, you must update their Status to an appropriate value.

Applicable values for this field may be state-dependent. For example, invoices can only have their status set to DELETED when the invoice status is currently DRAFT or SUBMITTED.

Discount Amounts, Discount Rates and Line Amounts

Invoices in Xero may be discounted either via a fixed amount using LineItem_DiscountAmount or with a percentage using LineItem_DiscountRate. These two methods are not compatible because discount amounts are more precise than discount rates. For example, a line item with the amount 2000.00 and a discount amount of 99.99 cannot be represented using the discount rate 4.999 because Xero allows only two digits of precision on discount rates. In addition, setting any form of discount means that LineItem_LineAmount becomes computed by Xero.

This means that, out the three fields LIneItem_DiscountAmount, LineItem_DiscountAmount and LineItem_DiscountRate, only one can be used as part of an INSERT or UDPATE query. For example, neither of the below queries are valid:

INSERT INTO Invoices (Invoice, LineItem_LineAmount, LineItem_DiscountRate) VALUES ('2bf5f07b-edf0-4b05-9200-cc2cd6b89b1e', 4999.99, 6.75)
UPDATE Invoices SET LineItem_DiscountRate = 12.50, LineItem_DiscountAmount = 99.99 WHERE Id = '1|2bf5f07b-edf0-4b05-9200-cc2cd6b89b1e'

It also means that the connector will remove the two other values when assigning any one of these fields. For example, if you were to assign a LineItem_LineAmount to an Invoice, the connector would remove any discount values attached to that Invoice.

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	A unique identifier for the line item, combining its position in the history log with the Xero-generated identifier for the invoice. This ensures that each line item is distinctly identifiable for tracking changes made to it.
InvoiceId	String	False	The unique identifier of the invoice to which this line item belongs. This field links the line item to a specific invoice, ensuring that changes to the invoice can be traced and tracked.
Type	String	False	The type of invoice. Valid values are 'ACCPAY' (Accounts Payable) and 'ACCREC' (Accounts Receivable), indicating whether the invoice pertains to an expense (ACCPAY) or income (ACCREC).
ContactId	String	False	The unique identifier of the contact associated with the invoice. This field links the invoice to a specific customer or supplier.
ContactName	String	False	The name of the contact associated with the invoice. This value is required when creating an invoice, as it identifies the individual or business to whom the invoice is issued.
LineItem_LineItemId	String	False	The unique Xero-generated identifier for the line item. If 'LineItemIDs' are not included in an update request, the existing line items are deleted and recreated.
LineItem_Description	String	False	A description for the line item. This field must contain at least one character and is required for creating an approved invoice, providing context for the goods or services being billed.
LineItem_Quantity	Double	False	The quantity of the item listed in the line item. This field is required for invoice approval and must be zero or positive.
LineItem_UnitAmount	Decimal	False	The unit amount for the item in the line item. This field is required for invoice approval and reflects the price per unit of the item or service.
LineItem_ItemCode	String	False	The unique code that identifies the item. This is typically used for categorizing products or services in the system.
LineItem_AccountCode	String	False	The account code associated with the line item. This must be an active account in the organization's chart of accounts, and it is required for invoice approval.
LineItem_Item_ItemId	String	False	The unique ID of the item the line item refers to. This ID helps link the line item to the actual product or service in the system.
LineItem_Item_Name	String	False	The name of the item the line item refers to. This provides a human-readable reference for the item being billed.
LineItem_Item_Code	String	False	The code of the item the line item refers to. This field helps identify the product or service linked to the invoice line.
LineItem_TaxType	String	False	The tax type applied to the line item. This field can override the default tax code for the selected account, allowing for different tax treatments.
LineItem_TaxAmount	Decimal	False	The calculated tax for the line item, based on the tax rate. This value is automatically generated based on the tax type and line amount.
LineItem_LineAmount	Decimal	False	The total amount for the line item, calculated by multiplying the 'Quantity' by the 'UnitAmount', adjusted for any discounts or tax treatments.
LineItem_TrackingCategory1_CategoryId	String	False	The ID of the first tracking category for the line item. Xero allows up to two optional tracking categories, used to further categorize transactions for detailed reporting.
LineItem_TrackingCategory1_CategoryName	String	False	The name of the first tracking category. This provides a label for the category, helping users understand how the line item is classified in financial reports.
LineItem_TrackingCategory1_OptionId	String	False	The ID of the selected option within the first tracking category. This helps specify the exact classification option chosen for the line item.
LineItem_TrackingCategory1_OptionName	String	False	The name of the selected option within the first tracking category. This provides clarity on the option selected for further classification.
LineItem_TrackingCategory1_Option_Stat	String	False	The status of the first tracking category option, which indicates whether it is active or inactive for reporting purposes.
LineItem_TrackingCategory2_CategoryId	String	False	The ID of the second tracking category for the line item. This allows for further categorization using two tracking categories in Xero.
LineItem_TrackingCategory2_CategoryName	String	False	The name of the second tracking category. This provides a label for the category, helping classify the line item for more detailed reporting.
LineItem_TrackingCategory2_OptionId	String	False	The ID of the selected option within the second tracking category, used to specify which option the line item belongs to.
LineItem_TrackingCategory2_OptionName	String	False	The name of the selected option within the second tracking category. This provides more context on how the line item is categorized.
LineItem_TrackingCategory2_Option_Stat	String	False	The status of the second tracking category option, indicating whether the option is active or inactive for reporting.
LineItem_DiscountRate	Double	False	The discount rate applied to the line item. This percentage reduces the amount billed for the item. Discounts are only supported on 'ACCREC' (sales) invoices.
LineItem_DiscountAmount	Decimal	False	The total discount applied to the line item, calculated based on the discount rate and the line item's amount. Discounts are only supported on 'ACCREC' (sales) invoices.
LineItemAggregate	String	False	Used to define multiple line item rows using XML values. This field should only be provided during an 'INSERT' operation to create new line items for the invoice.
Date	Date	False	The date the invoice was created. This field indicates when the invoice was issued, which can be used for aging reports and other financial analyses.
DueDate	Date	False	The date the invoice is due for payment. This field helps determine when payment is expected and is used to track overdue invoices.
LineAmountTypes	String	False	This field specifies whether line amounts include tax. The valid values are 'Exclusive' (tax is excluded), 'Inclusive' (tax is included), and 'NoTax' (no tax is applied). If this field is not specified, line amounts are treated as exclusive by default.
InvoiceNumber	String	False	The unique alphanumeric code identifying the invoice. For 'ACCREC' invoices, this field is automatically generated unless specified. For 'ACCPAY' invoices, it stores a non-unique identifier for the invoice.
Reference	String	False	An additional reference number for 'ACCREC' invoices. This field is used to store extra identifying information related to the invoice.
BrandingThemeId	String	False	The unique identifier of the branding theme used for the invoice. This field allows users to apply custom branding and templates to the invoice.
URL	String	False	A URL linking to a source document associated with the invoice, such as a scanned copy or a related external document.
CurrencyCode	String	False	The ISO 4217 currency code used for the invoice. This specifies the currency in which the invoice is issued.
CurrencyRate	Decimal	False	The exchange rate applied to the invoice if it is a multicurrency invoice. If not provided, the XE.com daily rate is used for currency conversion.
Status	String	False	The current status of the invoice. The valid status values are 'DRAFT', 'SUBMITTED', and 'AUTHORISED', indicating whether the invoice is in draft, submitted for approval, or fully authorized.
SentToContact	Boolean	False	Indicates whether the invoice has been sent to the contact. This field is only available for invoices that have been approved and ready to be sent to the customer or supplier.
ExpectedPaymentDate	Date	True	The expected payment date for sales invoices ('ACCREC'). This date indicates when the payment is expected from the customer.
PlannedPaymentDate	Date	True	The planned payment date for bills ('ACCPAY'). This field indicates when the payment is scheduled to be made to a supplier.
SubTotal	Decimal	True	The subtotal of the invoice, excluding taxes. This represents the sum of the line items before tax is applied.
TotalTax	Decimal	True	The total tax applied to the invoice. This is calculated based on the tax rates for each line item.
Total	Decimal	True	The total amount of the invoice, including tax. This is the sum of 'SubTotal' and 'TotalTax'.
TotalDiscount	Decimal	True	The total discount applied to the invoice. This is the sum of all discounts provided on line items, if applicable.
HasAttachments	Boolean	True	Indicates whether the invoice has any attachments, such as scanned documents or supporting files.
PaymentIds	String	True	A comma-delimited list of payment IDs associated with the invoice. This links the invoice to the payments made towards it.
PrePaymentIds	String	True	A comma-delimited list of prepayment IDs applied to the invoice. This links any prepayments to the invoice.
OverPaymentIds	String	True	A comma-delimited list of overpayment IDs applied to the invoice. This links any overpayments to the invoice.
CreditNoteIds	String	True	A comma-delimited list of credit note IDs applied to the invoice. This shows which credit notes have been used to reduce the invoice amount.
AmountDue	Decimal	True	The remaining amount due on the invoice, which is the difference between the total invoice amount and any payments, prepayments, or credits applied.
AmountPaid	Decimal	True	The total amount of payments received for the invoice.
CISDeduction	Decimal	True	The Construction Industry Scheme (CIS) deduction withheld from the payment, applicable only to UK organizations.
FullyPaidOnDate	Date	True	The date the invoice was fully paid. This is only returned for invoices that have been marked as fully paid.
AmountCredited	Decimal	True	The total amount credited to the invoice from credit notes, overpayments, or prepayments.
UpdatedDateUTC	Datetime	True	The timestamp of when the invoice was last updated. This field provides the last modification time for the invoice.
TenantId	String	False	The unique identifier of the tenant. This ensures the query retrieves data specific to a particular tenant, ensuring proper data isolation in multi-tenant environments.

CData Python Connector for Xero