Invoices
Query, insert and update Invoices for a Xero organisation.
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>' )
- 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 |
The line item index combined with the Id of the invoice. |
InvoiceId | String | False |
The Id of the invoice. This field is assigned by Xero. |
Type | String | False |
The type of the invoice. The valid values are ACCPAY and ACCREC. |
ContactId | String | False |
The Id of the contact. |
ContactName | String | False |
The name of the contact. This value is required when inserting. |
LineItem_LineItemId | String | False |
The Xero generated identifier for a LineItem. If LineItemIDs are not included with line items in an update request then the line items are deleted and recreated. |
LineItem_Description | String | False |
The description for the line item. This field must contain at least 1 character and is required to create an approved invoice. |
LineItem_Quantity | Double | False |
The quantity of the associated item in the line item. This field is required for invoice approval and must be zero or positive. |
LineItem_UnitAmount | Decimal | False |
The unit amount of the associated item in the line item. This field is required for invoice approval. |
LineItem_ItemCode | String | False |
The code that identifies the associated item. |
LineItem_AccountCode | String | False |
The account code. This must be active for the organisation. This field is required for invoice approval. |
LineItem_Item_ItemId | String | False |
The ID of the item the lineitem refers to. |
LineItem_Item_Name | String | False |
The name of the item the lineitem refers to. |
LineItem_Item_Code | String | False |
The code of the item the lineitem refers to. |
LineItem_TaxType | String | False |
The tax type. This field is used to override AccountCode, the default tax code for the selected account. |
LineItem_TaxAmount | Decimal | False |
The automatically calculated percentage of tax, based on the tax rate, for the line amount. |
LineItem_LineAmount | Decimal | False |
The total of the quantity multiplied by the unit amount with any discounts applied. |
LineItem_TrackingCategory1_CategoryId | String | False |
The Id of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_CategoryName | String | False |
The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionId | String | False |
The Id of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionName | String | False |
The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_Option_Stat | String | False |
The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryId | String | False |
The Id of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryName | String | False |
The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionId | String | False |
The Id of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionName | String | False |
The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_Option_Stat | String | False |
The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_DiscountRate | Double | False |
The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type (sales) invoices. |
LineItem_DiscountAmount | Decimal | False |
The discount amount being applied to the line item. Discounts are only supported on ACCREC-type (sales) invoices. |
LineItemAggregate | String | False |
Used to define LineItem rows using XML values. Should be provided on INSERT only. |
Date | Date | False |
The date the invoice was created. |
DueDate | Date | False |
The date the invoice is due. |
LineAmountTypes | String | False |
This field specifies whether line amounts include tax (default). When this field is not specified, line amount totals do not include tax. The valid values are Exclusive, Inclusive, and NoTax. |
InvoiceNumber | String | False |
The unique, alphanumeric code identifying the invoice. For ACCREC-type invoices. When this field is missing it is automatically generated from the Organisation Invoice Settings. For ACCPAY-type invoices, this field stores a nonunique alphanumeric code identifying the invoice. |
Reference | String | False |
An additional reference number for ACCREC-type invoices. |
BrandingThemeId | String | False |
The Id of the branding theme. |
URL | String | False |
A URL to a source document. |
CurrencyCode | String | False |
The ISO 4217 currency code. |
CurrencyRate | Decimal | False |
The currency rate for a multicurrency invoice. If no rate is specified, the XE.com day rate is used. |
Status | String | False |
The status code for the invoice. The valid values are DRAFT, SUBMITTED, and AUTHORISED. |
SentToContact | Boolean | False |
This field indicates whether the invoice should be marked as sent. This field can be set on only invoices that have been approved. |
ExpectedPaymentDate | Date | True |
The expected payment date. This field is shown on sales invoices (accounts receivable). |
PlannedPaymentDate | Date | True |
The planned payment date. This field is shown on bills (accounts payable). |
SubTotal | Decimal | True |
The subtotal for the invoice, which excludes taxes. |
TotalTax | Decimal | True |
The total tax for the invoice. |
Total | Decimal | True |
The total for the invoice, including tax -- the sum of SubTotal and TotalTax. |
TotalDiscount | Decimal | True |
Total of discounts applied on the invoice line items. |
HasAttachments | Boolean | True |
This field indicates whether the invoice has an attachment. |
PaymentIds | String | True |
A comma-delimited list of payment Ids associated with the invoice. |
PrePaymentIds | String | True |
A comma-delimited list of prepayment Ids associated with the invoice. |
OverPaymentIds | String | True |
A comma-delimited list of overpayment Ids associated with the invoice. |
CreditNoteIds | String | True |
A comma-delimited list of credit note Ids applied to the invoice. |
AmountDue | Decimal | True |
The amount remaining to be paid on the invoice. |
AmountPaid | Decimal | True |
The sum of payments received for the invoice. |
CISDeduction | Decimal | True |
The deduction withheld by a contractor to be paid to HMRC. Only available for UK organizations. |
FullyPaidOnDate | Date | True |
The date the invoice was fully paid. Only returned on fully paid invoices. |
AmountCredited | Decimal | True |
The sum of all credit notes, overpayments, and prepayments applied to the invoice. |
UpdatedDateUTC | Datetime | True |
The date when the invoice was last updated. |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |