Xero Connection

This documentation is based on version 21.0.8257 of the connector.

Getting Started

Xero Version Support

The Jitterbit Connector for Xero models Xero Accounting, Australian Payroll, Files, Assets and Projects objects as a relational database.

Establishing a Connection

Connecting to a Xero API

The connector supports the following Xero APIs:

Accounting API: Set the Schema connection property to ACCOUNTING
Australian Payroll API: Set the Schema connection property to PAYROLLAUS
Files API: Set the Schema connection property to FILES
Fixed Assets API: Set the Schema connection property to ASSETS
Projects API: Set the Schema connection property to PROJECTS

It is also recommended that you set the Tenant property, which can be the name or ID of a Xero organization. Xero allows you to authorize the connector to access multiple organizations. By default the connector will pick the first one, which may not be the one you expect if the connector is granted access to additional organizations.

If you need to confirm what organizations the connector has access to, you can connect without an Tenant and read the Tenants view. This will provide both the ID and the name of each connected organization. You should use the ID to set the Tenant property when possible since multiple organizations can have the same name.

Authenticating to Xero

All connections to Xero are authenticated using OAuth. The connector supports using PKCE applications and OAuth applications. For desktop applications, the connector's default application is the simplest way to authenticate. The only additional requirement is to set InitiateOAuth to GETANDREFRESH.

When the connector starts, it will open a browser and Xero will request your login information. The connector will use the credentials you provide to access your Xero data. These credentials will be saved and automatically refreshed as needed.

Using OAuth Authentication

Xero uses the OAuth authentication standard. The connector facilitates this in various ways as described below.

Embedded Credentials

See Embedded Credentials to connect with the connector's embedded credentials and skip creating a custom OAuth app.

Custom Credentials

See Creating a Custom OAuth App to register your application with Xero. Ths is only recommended if you need to cutomize the authorization screen itself, such as providing your own callback URL or branding.

Embedded Credentials

Desktop Authentication with the Embedded OAuth App

You can connect without setting any connection properties for your user credentials.

When you connect, the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process.

Extracts the access token from the callback URL and authenticates requests.
Obtains a new access token when the old one expires.
Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

Custom Credentials

Desktop Authentication with a Custom OAuth App

Follow the steps below to authenticate with the credentials for a custom OAuth app. See Creating a Custom OAuth App.

Get and Refresh the OAuth Access Token

After setting the following, you are ready to connect:

AuthScheme: Set this to OAuth, or PKCE if you configured a PKCE application.
InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
OAuthClientId: Set this to the client ID assigned when you registered your app.
OAuthClientSecret: OAuth only. Set this to the client secret assigned when you registered your app.
CallbackURL: Set this to the redirect URI defined when you registered your app.

When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:

Extracts the access token from the callback URL and authenticates requests.
Obtains a new access token when it expires.
Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

Headless Machines

Using OAuth on a Headless Machine

To create Xero data sources on headless servers or other machines on which the connector cannot open a browser, you need to authenticate from another machine. Authentication is a two-step process.

Instead of installing the connector on another machine, you can follow the steps below to obtain the OAuthVerifier value. Or, you can install the connector on another machine and transfer the OAuth authentication values, after you authenticate through the usual browser-based flow.
You can then configure the connector to automatically refresh the access token from the headless machine.

Authorize The Connection

Creating a custom OAuth auth code app is required for the following flow, as PKCE is not supported with it. See Creating a Custom OAuth App for a procedure. You can then follow the procedures below to authenticate and connect to data.

Set the following properties on the headless machine:

AuthScheme: Set this to OAuth.
InitiateOAuth: Set this to OFF.
OAuthClientId: Set this to the Client ID in your app settings.
OAuthClientSecret: Set this to the Client Secret in your app settings.

You can then follow the steps below to authenticate from another machine and obtain the OAuthVerifier connection property.

Call the GetOAuthAuthorizationURL stored procedure* with the CallbackURL input parameter set to the exact Redirect URI you specified in your app settings.
Open the returned URL in a browser. Log in and grant permissions to the connector. You are then redirected to the callback URL, which contains the verifier code.
Save the value of the verifier code. You will set this in the OAuthVerifier connection property.

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

AuthScheme: Set this to OAuth.
InitiateOAuth: Set this to REFRESH.
OAuthClientId: Set this to the consumer key in your app settings.
OAuthClientSecret: Set this to the consumer secret in your app settings.
OAuthVerifier: Set this to the verifier code.
OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified file.

Connect to Data

After the OAuth settings file is generated, set the following properties to connect to data:

AuthScheme: Set this to OAuth.
InitiateOAuth: Set this to REFRESH.
OAuthClientId: Set this to the consumer key in your app settings.
OAuthClientSecret: Set this to the consumer secret in your app settings.
OAuthSettingsLocation: Set this to the file containing the encrypted OAuth authentication values. Make sure this file gives read and write permissions to the provider to enable the automatic refreshing of the access token.

Transfer OAuth Settings

Instead of completing the above OAuth flow by hand, you may also follow the steps below to install the connector on another machine, authenticate, and then transfer the resulting OAuth values.

This flow does not require the use of a custom OAuth application.

On a second machine, install the connector and connect with the following properties set:

Set InitiateOAuth to GETANDREFRESH.
OAuthSettingsLocation: Set this to a writable text file.

Test the connection to authenticate. The resulting authentication values are written, encrypted, to the path specified by OAuthSettingsLocation. Once you have successfully tested the connection, copy the OAuth settings file to your headless machine. On the headless machine, set the following connection properties to connect to data:

InitiateOAuth: Set this to REFRESH.
OAuthSettingsLocation: Set this to the path to your OAuth settings file. Make sure this file gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Creating a Custom OAuth App

The following sections show how to create a custom OAuth app for use in the Custom Credentials authentication flow.

Register an OAuth Application: Desktop

Follow the steps below to register a public application to obtain the OAuth client credentials:

Log in to the Xero developer portal.
Click My Apps -> Add Application. Choose the Auth Code application type.
Enter a name for your application and the URL of your company. This information is displayed to users when they connect.
Register the Redirect URI appropriate for the product you are using.

After you click Save, you are shown your OAuth Client ID and can create a new OAuth Client Secret. You will set these to OAuthClientId and OAuthClientSecret in the authenticate guide, Custom Credentials.

Register a PKCE Application: Desktop

In addition to the default auth code application type, you can also create applications using PKCE. This is recommended for developers writing client side applications because PKCE does not require a client secret.

Registering a PKCE application is the similar to the process for registering an OAuth application. The only difference that Xero does not return an OAuthClientSecret because it is not used in the PKCE flow.

Xero API Limits

The Xero API has usage limitations that may be encountered while using the Jitterbit Connector for Xero. Note that all of these apply on a per-application and per-organization basis. An application may exceed these limits if it is querying multiple organizations as long as it obeys the limits within each organization.

Concurrent Limit

At most 5 API calls from the application may be active against the same organization at once. Typically this limit is not an issue because the connector will automatically retry the current request if this occurs.

Per-Minute Limit

At most 60 API calls per minute may be made from the application to the same organization. This limit is also not an issue as Xero reports a recommended delay time that the connector will use to avoid hitting the limit again.

Per-Day Limit

At most 5000 API calls per day may be made from the application to the same organization. Hitting this limit is rare but can happen with certain tables (such as history tables and some reports) or certain settings (such as AutoExpandDetails) are used, as they require the connector to make a single API call for each invoice/contact/etc. that is read.

If this limit is hit frequently then the first step should be avoiding the tables or settings that trigger it. It is also possible to convert the queries into versions which read fewer rows at one time. This is usually done by replacing simple selects with subqueries that pick out small groups of rows, and then using multiple versions of these queries at different times:

/* Retreives history for about 1/16 of the invoices */
SELECT * FROM HistoryInvoices WHERE InvoiceId IN (
  SELECT InvoiceId FROM Invoices WHERE InvoiceId LIKE '0%'
)

The RetryDailyLimit option is also available but its use is strongly discouraged, as in the worst cases the delays that Xero suggests can span several hours during which the connector will be unavailable. It is typically better to restructure how you request data to fit within the limits than to ignore the limits and execute queries that take multiple hours to run.

Important Notes

Stored Procedures

The stored procedures features described in this documentation are not currently supported.
Because stored procedures are not currently supported, any feature dependent on stored procedures is also currently not supported.

Configuration Files and Their Paths

All references to adding configuration files and their paths refer to files and locations on the Jitterbit Harmony Agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Xero connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using Private Agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

User Defined Views

The Jitterbit Connector for Xero allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

Create a JSON-formatted configuration file defining the views you want.
DDL statements.

Defining Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

Each root element defines the name of a view.
Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
	"MyView": {
		"query": "SELECT * FROM Contacts WHERE MyColumn = 'value'"
	},
	"MyView2": {
		"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
	}
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Working with User Defined Views

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customizing the SSL Configuration

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model

The Jitterbit Connector for Xero models five Xero APIs as relational databases: the Accounting, Australian Payroll, Files, Fixed Assets and Projects APIs. Set the Schema property and use the information in each section to select the proper API based on your project needs.

Using the Accounting API

The Accounting API contains the company's financial information, such as contacts, invoices and reporting. It also contains history tables which records changes to most kinds of entities.

See Accounting Data Model for the available entities in the Accounting API.

Using the Australian Payroll API

The Australian Payroll API contains information on the company's employees, such as timesheets, leave and tax declarations.

See Australian Payroll Data Model for the available entities in the Australian Payroll API.

Using the Files API

The Files API contains information on the files and attachments managed by Xero. It can be used to upload and download files, and attach them to accounting entities.

See Files Data Model for the available entities in the Files API.

Using the Fixed Assets API

The Fixed Assets API contains information on the company's fixed assets, such as registration, depreciation and resale.

See Fixed Assets Data Model for the available entities in the Fixed Assets API.

Using the Projects API

The Projects API contains information on the company's projects, such as billing and time tracking.

See Projects Data Model for the available entities in the Projects API.

Accounting Data Model

The Jitterbit Connector for Xero models the Xero Accounting API as relational tables, views, and stored procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

Tables describes the available tables.

Views

Views are tables that cannot be modified. Typically, data that are read-only and cannot be updated are shown as views.

Stored Procedures

NOTE: Stored procedures are not currently supported. See the above note for details.

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

Tables

The connector models the data in Xero into a list of tables that can be queried using standard SQL statements.

Generally, querying Xero tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Jitterbit Connector for Xero Tables


Name	Description
Accounts	Create, delete, and query accounts for a Xero organisation.
BankTransactions	Create, update, delete, and query bank transactions for a Xero organisation.
BankTransfers	Usage information for the operation BankTransfers.rsd.
BatchPayments	Create and query BatchPayments for a Xero organisation.
ContactGroups	Create, update, and query contact groups for a Xero organisation.
Contacts	Create, update, and query contacts for a Xero organisation.
CreditNotes	Create, update, delete, and query credit notes for a Xero organisation.
Employees	Create, update, and query employees for a Xero organisation.
ExpenseClaims	Create, update, and query expense claims for a Xero organisation.
HistoryBankTransactions	Create and query BankTransaction history and notes for a Xero organization.
HistoryBatchPayments	Create and query BatchPayment history and notes for a Xero organization.
HistoryContacts	Create and query Contact history and notes for a Xero organization.
HistoryCreditNotes	Create and query CreditNote history and notes for a Xero organization.
HistoryInvoices	Create and query Invoice history and notes for a Xero organization.
HistoryPurchaseOrders	Create and query PurchaseOrder history and notes for a Xero organization.
Invoices	Create, update, delete, and query Invoices for a Xero organisation.
Items	Create, update, delete, and query items for a Xero organisation.
LinkedTransactions	Create, update, and query LinkedTransactions for a Xero organisation.
ManualJournals	Create, update, and query manual journals for a Xero organisation.
Payments	Create and query payments for a Xero organisation.
PurchaseOrders	Create, update, delete, and query purchase orders for a Xero organisation.
Receipts	Create, update, delete, and query receipts for a Xero organisation.
TaxRates	Usage information for the operation TaxRates.rsd.

Accounts

Create, delete, and query accounts for a Xero organisation.

Table Specific Information

The Accounts table allows you to SELECT and INSERT accounts for a Xero organization.

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

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. The following fields are required for inserts: Code, Name, and Type.

INSERT INTO Accounts (Name,Code,Type) VALUES ('John Doe','2000','BANK')

Columns


Name	Type	ReadOnly	Description
AccountId [KEY]	String	True	The unique, Xero-assigned identifier of the account.
Code	String	False	Customer-defined alphanumeric account code. For example, 200 or SALES.
Name	String	False	Name of the account.
Type	String	False	The account type. Valid values are BANK, CURRENT, CURRLIAB, DEPRECIATN, DIRECTCOSTS, EQUITY, EXPENSE, FIXED, LIABILITY, NONCURRENT, OTHERINCOME, OVERHEADS, PREPAYMENT, REVENUE, SALES, TERMLIAB, PAYGLIABILITY, SUPERANNUATIONEXPENSE, SUPERANNUATIONLIABILITY, and WAGESEXPENSE.
Description	String	False	Description of the account. All accounts except bank accounts return this element.
TaxType	String	False	The tax type. For example, INPUT or NONE.
EnablePaymentsToAccount	Boolean	False	Describes whether the account can have payments applied to it.
ShowInExpenseClaims	Boolean	False	Describes whether the account code is available for use with expense claims.
Class	String	True	The account class type. For example, ASSET or EQUITY.
Status	String	False	The account status code.
SystemAccount	String	True	Returned only for system accounts, such as DEBTORS, CREDITORS, etc.
BankAccountNumber	String	False	The bank account number associated with the account. BANK account types only.
CurrencyCode	String	False	The currency code associated with the account. BANK account types only.
ReportingCode	String	True	The reporting code. Only available for NZ organisations.
ReportingCodeName	String	True	The reporting code name. Only available for NZ organisations.
UpdatedDateUTC	Datetime	True	Timestamp of the last change to the user record.
HasAttachments	Boolean	True	This field indicates whether the invoice has an attachment.
BankAccountType	String	False	Valid types are BANK, CREDITCARD, and PAYPAL
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

BankTransactions

Create, update, delete, and query bank transactions for a Xero organisation.

Table Specific Information

The BankTransactions table allows you to SELECT, INSERT, UPDATE, and DELETE bank transactions for a Xero organization.

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

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To create a new bank transaction record, the following fields are required in addition to at least one line item: Type, Contact_ContactName or Contact_ContactId, and BankAccount_AccountId or BankAccount_Code. To create a new line item, the following fields are required: LineItem_Description, LineItem_Quantity, LineItem_UnitAmount, and LineItem_AccountCode.

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 BankTransactions API for more details.

INSERT INTO BankTransactions(Type, Contact_ContactName, BankAccount_Code, LineItemAggregate) VALUES (
  'RECEIVE',
  'John Doe',
  '200',
  '<LineItem><Description>LineItem1Desc</Description><Quantity>1</Quantity><AccountCode>100</AccountCode></LineItem> <LineItem><Description>LineItem2Desc</Description><Quantity>2</Quantity><AccountCode>100</AccountCode></LineItem>'
)

You can also insert a new line item on an existing object by specifying the Xero-generated identifier of the existing record. For example, to add a line item to an existing bank transaction:
```
INSERT INTO BankTransactions (BankTransactionId, LineItem_Description, LineItem_Quantity) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'New Line Item Desc', 1)
```

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 a record from the BankTransactions table, you must update the Status field to an appropriate value.

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	The line item index combined with the ID of the bank transaction.
BankTransactionId	String	False	The ID of the bank transaction.
Type	String	False	The transaction type. Valid values are RECEIVE, SPEND, RECEIVE-OVERPAYMENT, RECEIVE-PREPAYMENT, RECEIVE-TRANSFER, SPEND-OVERPAYMENT, SPEND-PREPAYMENT, and SPEND-TRANSFER.
LineAmountTypes	String	False	How line item values are provided relative to tax. Can be either Exclusive, Inclusive or NoTax
Contact_ContactName	String	False	The name of the contact associated with the transaction.
Contact_ContactId	String	False	The ID of the contact associated with the transaction.
BankAccount_AccountId	String	False	The ID for the associated bank account.
BankAccount_AccountName	String	False	The Name for the associated bank account.
BankAccount_Code	String	False	The code for the associated bank account.
LineItem_LineItemId	String	False	The Xero-generated ID of the bank transaction line item
LineItem_Description	String	False	A description for the line item in the bank transaction.
LineItem_Quantity	Double	False	The quantity of the subject of the line item. This value must be greater than or equal to 0.
LineItem_UnitAmount	Decimal	False	The unit amount of the subject of the line item. This amount must be positive.
LineItem_AccountCode	String	False	The code for the referenced account. Including this field is recommended when posting.
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 code for the transaction. This field is used as an override for the default tax code for the selected account.
LineItem_TaxAmount	Decimal	False	The automatically calculated percentage of tax for the line amount, based on the tax rate.
LineItem_LineAmount	Decimal	False	The amount of the line item. If either Quantity or UnitAmount are omitted, Xero will calculate the missing value from LineAmount.
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.
LineItemAggregate	String	False	Used to define LineItem rows using XML values. Should be provided on INSERT only.
IsReconciled	Boolean	False	This field shows whether this transaction is reconciled.
Date	Date	False	The date of the transaction.
Reference	String	False	An internal Xero reference for the transaction.
CurrencyRate	Decimal	False	Exchange rate to base currency when money is spent or received. For example, 0.7500. This field is used for only bank transactions not in the base currency. If this is not specified for non-base-currency accounts then either the user-defined rate or the XE.com daily rate will be used.
URL	String	False	A link to a source document.
Status	String	False	The bank transaction status code. The valid values are AUTHORISED and DELETED.
SubTotal	Decimal	False	The total of the transaction, excluding taxes.
TotalTax	Decimal	False	The tax on the bank transaction.
Total	Decimal	False	The total of the transaction, including tax.
PrepaymentId	String	False	Xero generated unique identifier for a Prepayment. This will be returned on BankTransactions with a Type of SPEND-PREPAYMENT or RECEIVE-PREPAYMENT.
OverpaymentId	String	False	Xero generated unique identifier for an Overpayment. This will be returned on BankTransactions with a Type of SPEND-OVERPAYMENT or RECEIVE-OVERPAYMENT.
UpdatedDateUTC	Datetime	True	The date when the transaction was last updated.
FullyPaidOnDate	Date	False	The date when the transaction was fully paid.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

BankTransfers

Usage information for the operation BankTransfers.rsd.

Table Specific Information

The BankTransfers table allows you to SELECT and INSERT bank transfers for a Xero organization.

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

INSERT INTO BankTransfers (FromBankAccount_Code,ToBankAccount_Code,Amount) VALUES ('3d08fd48-434d-4c18-a57b-831663ab70d2','ceef65a5-a545-423b-9312-78a53caadbc4','1000.00')

Columns


Name	Type	ReadOnly	Description
BankTransferId [KEY]	String	True	The unique, Xero-assigned identifier for the bankTransfer.
FromBankAccount_Code	String	False	The bank account code that you will be sending from.
FromBankAccount_AccountId	String	False	The bank account account ID that you will be sending from.
FromBankAccount_Name	String	False	The bank account name that you will be sending from.
ToBankAccount_Code	String	False	The bank account code that you will be sending to.
ToBankAccount_AccountId	String	False	The bank account account ID that you will be sending to.
ToBankAccount_Name	String	False	The bank account name that you will be sending to.
Amount	Decimal	False	The amount of the money to transfer.
Date	Date	False	The date of the transfer YYYY-MM-DD.
CurrencyRate	Decimal	True	The currency rate.
FromBankTransactionID	String	True	The Bank Transaction ID for the source account.
ToBankTransactionID	String	True	The Bank Transaction ID for the destination account.
HasAttachments	Boolean	True	Boolean to indicate if a Bank Transfer has an attachment.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

BatchPayments

Create and query BatchPayments for a Xero organisation.

Table Specific Information

The BatchPayments table allows you to SELECT and INSERT batch payments for a Xero organization. The ID column is generated by the connector this value combines the index of the individual payment within the batch with the unique, Xero-generated BatchPaymentId

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

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To create a new batch payment, the AccountId and Date are required in addition to at least one payment item; the Payment_InvoiceId and Payment_Amount fields are required to insert a payment item.

You can also provide multiple payments using an XML aggregate which contains the data for each payment in the batch. The elements supported are the same as the names of the "Payment_" columns without the "Payment_" prefix.

INSERT INTO BatchPayments(AccountId, Date, PaymentAggregate) VALUES (
  'AAAAA-AAAAA-AAAAA-AAAAA',
  '2019-03-01',
  '<Payment><InvoiceID>BBBBB-...</InvoiceID><Amount>50.21</Amount></Payment> <Payment><InvoiceID>CCCCC-...</InvoiceID><Amount>12.96</Amount></Payment>'
)

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	The batch payment index combined with the ID of the batch payment.
BatchPaymentId	String	True	The ID of the invoice. This field is assigned by Xero.
AccountId	String	False	The ID of the account used to make the payment. It must either be a BANK account or have payments enabled.
Particulars	String	False	(NZ only) The particulars that will appear on the statement.
Code	String	False	(NZ only) The transaction code that will appear on the statement.
Reference	String	False	(NZ only) The transaction reference that will appear on the statement.
Details	String	False	(Non-NZ only) The details sent to the organization's bank as a reference for the bank transaction.
Narrative	String	False	(UK only) A description that is only visible in the Xero statement line
Date	Date	False	The date the payment is being made.
Payment_PaymentId	String	True	The ID of the Payment. This field is assigned by Xero.
Payment_InvoiceId	String	False	The ID of the Invoice the payment was made against
Payment_BankAccountNumber	String	False	The account number of the bank the payment is being made to.
Payment_Particulars	String	False	(NZ only) The particulars that will appear on the statement.
Payment_Code	String	False	(NZ only) The transaction code that will appear on the supplier's bank account.
Payment_Reference	String	False	(NZ only) The transaction reference that will appear on the supplier's bank account.
Payment_Details	String	False	The details sent to the supplier's bank as a reference for the bank transaction.
Payment_Amount	Decimal	False	The amount being paid.
PaymentAggregate	String	False	Used to define Payment rows using XML values. Should be provided on INSERT only.
Type	String	True	Either PAYBATCH for bill payments, or RECBATCH for sales payments.
Status	String	True	Either AUTHORISED or DELETED
TotalAmount	Decimal	True	The total of all the payments included in the batch
IsReconciled	Boolean	True	Whether the batch payment has been reconciled
UpdatedDateUTC	Datetime	True	The date when the batch payment was last updated.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

ContactGroups

Create, update, and query contact groups for a Xero organisation.

Table Specific Information

The ContactGroups table allows you to manage both contact groups as well as the members of those contact groups.

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

When inserting with a contact group, you can either insert a new contact group or add contacts into an existing group.

To insert a new contact group, only the Name field should be given:

INSERT INTO ContactGroups (Name) VALUES ('Contractors')

To add a contact into an existing group, both the ContactGroupId and the Contact_ContactId must be provided. The value for the Contact_ContactId field can be retrieved by querying the Contacts table.

INSERT INTO ContactGroups (ContactGroupId, Contact_ContactId) VALUES ('11111-11111-11111-11111', '22222-22222-22222-22222')

Multiple contacts may be provided using the ContactAggregate:

INSERT INTO ContactGroups (ContactGroupId, ContactAggregate) VALUES (
  '11111-11111-11111-11111', 
'<Contact><ContactID>22222-22222-22222-22222</ContactID></Contact> <Contact><ContactID>33333-33333-33333-33333</ContactID></Contact>')

UPDATE

When updating a contact group, the only fields that can be changed are the Name and Status field. Changing the Status field to DELETED will delete the contact group.

-- Renaming the group
UPDATE ContactGroup SET Name = 'External Contractors' WHERE ContactGroupId = '11111-11111-11111-1111'


-- Deleting the group
UPDATE ContactGroup SET Status = 'DELETED' WHERE ContactGroupId = '11111-11111-11111-1111'

DELETE

Deleting entries from a contact group will remove individual contacts from the group.

DELETE FROM ContactGroups WHERE ID = '3|11111-11111-11111-11111'

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	The line item index combined with the ID of the Contact Group
ContactGroupId	String	False	The unique Xero identifier for the contact group.
Name	String	False	The contact group name.
Status	String	False	The status of the contact group.
ContactAggregate	String	False	Used to define Contact rows using XML values. Only allowed when performing an INSERT into an existing ContactGroup.
Contact_ContactId	String	False	The unique identifier of the contact group member
Contact_Name	String	True	THe name of the contact group member
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Contacts

Create, update, and query contacts for a Xero organisation.

Table Specific Information

The Contacts table allows you to SELECT, INSERT, and UPDATE contacts for a Xero organization.

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

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. The Name field is required to add a new contact.

INSERT INTO Contacts (Name) VALUES ('John Doe')

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.

Columns


Name	Type	ReadOnly	Description
ContactId [KEY]	String	True	The unique Xero identifier for the contact.
Name	String	False	The contact name.
AccountNumber	String	False	The account number associated with the contact.
ContactNumber	String	False	An identifier from an external system. Although this field is read-only on the Xero contact screen, it can be updated via the API.
ContactStatus	String	False	The status of the contact. Available status types are ACTIVE or ARCHIVED.
FirstName	String	False	The first name of the contact. The maximum length of this field is 255 characters.
LastName	String	False	The last name of the contact. The maximum length of this field is 255 characters.
EmailAddress	String	False	The email address of the contact. The maximum length of this field is 500 characters.
SkypeUserName	String	False	The Skype username of the contact.
ContactPerson1_FirstName	String	False	First name of contact person.
ContactPerson1_LastName	String	False	Last name of contact person.
ContactPerson1_EmailAddress	String	False	Email address name of contact person.
ContactPerson1_IncludeInEmails	Boolean	False	Whether to include contact person in emails.
ContactPerson2_FirstName	String	False	First name of contact person.
ContactPerson2_LastName	String	False	Last name of contact person.
ContactPerson2_EmailAddress	String	False	Email address name of contact person.
ContactPerson2_IncludeInEmails	Boolean	False	Whether to include contact person in emails.
ContactPerson3_FirstName	String	False	First name of contact person.
ContactPerson3_LastName	String	False	Last name of contact person.
ContactPerson3_EmailAddress	String	False	Email address name of contact person.
ContactPerson3_IncludeInEmails	Boolean	False	Whether to include contact person in emails.
ContactPerson4_FirstName	String	False	First name of contact person.
ContactPerson4_LastName	String	False	Last name of contact person.
ContactPerson4_EmailAddress	String	False	Email address name of contact person.
ContactPerson4_IncludeInEmails	Boolean	False	Whether to include contact person in emails.
BankAccountDetails	String	False	The bank account number of the contact.
TaxNumber	String	False	The region-specific tax number of the contact: the ABN (Australia), GST (New Zealand), VAT (UK), or Tax ID Number (US and global).
AccountsReceivableTaxType	String	False	The default tax type used for the contact on accounts-receivable invoices.
AccountsPayableTaxType	String	False	The default tax type used for the contact on accounts-payable invoices.
Street_AddressLine1	String	False	Line 1 of the address.
Street_AddressLine2	String	False	Line 2 of the address.
Street_AddressLine3	String	False	Line 3 of the address.
Street_AddressLine4	String	False	Line 4 of the address.
Street_City	String	False	The city.
Street_Region	String	False	The region.
Street_PostalCode	String	False	The postal code.
Street_Country	String	False	The country.
Street_AttentionTo	String	False	The attention-to line of the address.
POBox_AddressLine1	String	False	Line 1 of the address.
POBox_AddressLine2	String	False	Line 2 of the address.
POBox_AddressLine3	String	False	Line 3 of the address.
POBox_AddressLine4	String	False	Line 4 of the address.
POBox_City	String	False	The city.
POBox_Region	String	False	The region.
POBox_PostalCode	String	False	The postal code.
POBox_Country	String	False	The country.
POBox_AttentionTo	String	False	The attention-to line of the address.
DDI_PhoneNumber	String	False	The phone number without area or country code.
DDI_PhoneAreaCode	String	False	The area code.
DDI_PhoneCountryCode	String	False	The country code.
Default_PhoneNumber	String	False	The phone number without area or country code.
Default_PhoneAreaCode	String	False	The area code.
Default_PhoneCountryCode	String	False	The country code.
Fax_PhoneNumber	String	False	The phone number without area or country code.
Fax_PhoneAreaCode	String	False	The area code.
Fax_PhoneCountryCode	String	False	The country code.
Mobile_PhoneNumber	String	False	The phone number without area or country code.
Mobile_PhoneAreaCode	String	False	The area code.
Mobile_PhoneCountryCode	String	False	The country code.
UpdatedDateUTC	Datetime	True	The timestamp of when the contact was last updated.
ContactGroupId	String	True	Displays which contact groups a contact is included in.
IsSupplier	Boolean	True	This field shows whether a contact has any accounts-payable (AP) invoices. This field is set automatically when an AP invoice is generated for a contact.
IsCustomer	Boolean	True	This field shows whether a contact has any accounts-receivable (AR) invoices. This field is set automatically when an AR invoice is generated for a contact.
DefaultCurrency	String	False	The default currency for the contact.
Website	String	True	The website of the contact.
BrandingThemeId	String	True	The ID of the branding theme.
BrandingThemeName	String	True	The Name of the branding theme.
PurchasesDefaultAccountCode	String	False	The default purchases account code for contacts.
SalesDefaultAccountCode	String	False	The default sales account code for contacts.
BatchPayments_BankAccountNumber	String	True	Batch payment bank account number for the contact.
BatchPayments_BankAccountName	String	True	Batch payment bank account name for the contact.
BatchPayments_Details	String	True	Batch payment details for the contact.
Discount	Decimal	True	The default discount rate for the contact.
Balances_AccountsReceivable_Outstanding	Decimal	True	The outstanding and overdue amounts for sales invoices (accounts receivable).
Balances_AccountsReceivable_Overdue	Decimal	True	The outstanding and overdue amounts for bills (accounts payable).
Balances_AccountsPayable_Outstanding	Decimal	True	The outstanding and overdue amounts for sales invoices (accounts receivable).
Balances_AccountsPayable_Overdue	Decimal	True	The outstanding and overdue amounts for bills (accounts payable).
PaymentTerms_Bills_Day	Int	True	The default payment terms for the contact.
PaymentTerms_Bills_Type	String	True	The default payment terms for the contact.
PaymentTerms_Sales_Day	Int	True	The default payment terms for the contact.
PaymentTerms_Sales_Type	String	True	The default payment terms for the contact.
SalesTrackingCategory1_Name	String	False	The default sales tracking category name for contacts.
SalesTrackingOption1_Name	String	False	The default sales tracking option name for contacts.
SalesTrackingCategory2_Name	String	False	The default sales tracking category name for contacts.
SalesTrackingOption2_Name	String	False	The default sales tracking option name for contacts.
PurchaseTrackingCategory1_Name	String	False	The default purchase tracking category name for contacts.
PurchaseTrackingOption1_Name	String	False	The default purchase tracking option name for contacts.
PurchaseTrackingCategory2_Name	String	False	The default purchase tracking category name for contacts.
PurchaseTrackingOption2_Name	String	False	The default purchase tracking option name for contacts.
HasAttachments	Boolean	True	This field indicates whether the invoice has an attachment.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

CreditNotes

Create, update, delete, and query credit notes for a Xero organisation.

Table Specific Information

The CreditNotes table allows you to SELECT, INSERT, UPDATE, and DELETE credit note line items for a Xero organization. The ID column is generated by the connector; the value of this field combines the index of the line item with the unique, Xero-generated CreditNoteId.

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

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new credit note, the Contact_Name field is required. For a credit note to be approved, the following fields must be defined in addition to at least one line item: Contact_Name, Date, and LineAmountTypes.

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

You can also 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 CreditNotes API for more details.
```
INSERT INTO CreditNotes (Type, Contact_Name, LineItemAggregate) VALUES (
  'ACCPAYCREDIT',
  'John Doe',
  '<LineItem><Description>LineItemDesc1</Description><Quantity>1</Quantity></LineItem> <LineItem><Description>LineItemDesc2</Description><Quantity>2</Quantity></LineItem>'
)
```
You can insert a new line item on an existing object by specifying the Xero-generated identifier of the existing record. For example, to add a line item to an existing credit note:
```
INSERT INTO CreditNotes (CreditNoteId, LineItem_Description, LineItem_Quantity) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'New Line Item Desc', 1)
```

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.

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	The line item index combined with the ID of the credit note.
CreditNoteId	String	False	The ID of the credit note.
CreditNoteNumber	String	False	A unique identifier for the credit note that identifies it to the user.
Reference	String	False	An optional field to store a reference.
Type	String	False	The credit note type. The valid values are ACCPAYCREDIT and ACCRECCREDIT.
Contact_ContactId	String	False	The ID of the contact.
Contact_Name	String	False	The name of the contact.
Date	Date	False	The date the credit note was created.
Status	String	False	The status of the credit note.
LineAmountTypes	String	False	The line amount type, which determines whether line amounts include tax (default). The valid values are Exclusive, Inclusive, and NoTax. Credit notes cannot be approved without this field being defined.
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 of the credit note. This value must be at least one character. Invoices cannot be approved without these fields.
LineItem_Quantity	Double	False	The quantity of the subject of the line item. This value must be greater than or equal to 0. Invoices cannot be approved without these fields.
LineItem_UnitAmount	Decimal	False	The unit amount of the subject of the line item. Invoices cannot be approved without these fields.
LineItem_ItemCode	String	False	The user-defined item code.
LineItem_AccountCode	String	False	The account code. The account code must be active for the organisation. Invoices cannot be approved without these fields.
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 description for the line item of the credit note. This value must be at least one character. Invoices cannot be approved without these fields.
LineItem_TaxAmount	Decimal	False	The quantity of the subject of the line item. This value must be greater than or equal to 0. Invoices cannot be approved without these fields.
LineItem_LineAmount	Decimal	False	The total of the Quantity field multiplied by the UnitAmount field with 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 credit notes.
LineItemAggregate	String	False	Used to define LineItem rows using XML values. Should be provided on INSERT only.
SubTotal	Decimal	False	The subtotal of the credit note, excluding taxes.
TotalTax	Decimal	False	The total tax on the credit note.
Total	Decimal	False	The total of the credit note (the subtotal plus the total tax).
UpdatedDateUTC	Datetime	True	The date when the credit note was last updated.
CurrencyCode	String	False	The currency used for the credit note.
FullyPaidOnDate	Date	False	The date when the credit note was fully paid.
SentToContact	Boolean	False	This field indicates whether the credit note has been sent to the contact via the Xero app.
BrandingThemeId	String	False	The ID of the branding theme.
CurrencyRate	Decimal	False	Exchange rate to base currency when money is spent or received. For example, 0.7500. This field is used for only bank transactions not in the base currency. If this is not specified for non-base-currency accounts then either the user-defined rate or the XE.com daily rate will be used.
RemainingCredit	Decimal	False	The remaining credit for this credit note.
Allocation1_AppliedAmount	Decimal	True	The amount to be applied from this credit note to a given invoice.
Allocation1_Date	Date	True	The date the allocation was made.
Allocation1_InvoiceId	String	True	The ID of the invoice this allocation has been applied to.
Allocation1_InvoiceNumber	String	True	The Invoice this allocation has been applied to.
Allocation2_AppliedAmount	Decimal	True	The amount to be applied from this credit note to a given invoice.
Allocation2_Date	Date	True	The date the allocation was made.
Allocation2_InvoiceId	String	True	The ID of the invoice this allocation has been applied to.
Allocation2_InvoiceNumber	String	True	The Invoice this allocation has been applied to.
PaymentIds	String	True	A comma-delimited list of payment Ids associated with the credit notes.
HasAttachments	Boolean	True	This field indicates whether the invoice has an attachment.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Employees

Create, update, and query employees for a Xero organisation.

Table Specific Information

The Employees table allows you to SELECT, INSERT, and UPDATE employees for a Xero organization.

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

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new employee record, the following fields are required: FirstName and LastName.

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.

Columns


Name	Type	ReadOnly	Description
EmployeeId [KEY]	String	True	The unique identifier of the employee. This field is assigned by Xero.
Status	String	False	The status of the employee. Valid values are ACTIVE and DELETED.
FirstName	String	False	The first name of the employee.
LastName	String	False	The last name of the employee.
ExternalLink_URL	String	False	A URL to an external resource.
ExternalLink_Description	String	False	A description for the external link.
UpdatedDateUTC	Datetime	True	Timestamp of the last change to the employee record.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

ExpenseClaims

Create, update, and query expense claims for a Xero organisation.

Table Specific Information

The ExpenseClaims table allows you to SELECT, INSERT, and UPDATE expense claims for a Xero organization.

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 create a new expense claim record, the UserId field must be defined; in addition, at least one ReceiptId must be specified. One can be provided either via the ReceiptId column:

INSERT INTO ExpenseClaims(UserId, ReceiptId) VALUES (
  'XXXXX-XXXXX-XXXXX-XXXXX', 'YYYYY-YYYYY-YYYYY-YYYYY'
)

Multiple may also be specified by including them in the ReceiptAggregate:

INSERT INTO ExpenseClaims(UserId, ReceiptAggregate) VALUES (
  'XXXXX-XXXXX-XXXXX-XXXXX',
  '<Receipt><ReceiptID>YYYYY-YYYYY-YYYYY-YYYYY</ReceiptID></Receipt> <Receipt><ReceiptID>ZZZZZ-ZZZZZ-ZZZZZ-ZZZZZ</ReceiptID></Receipt>'
)

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.

Columns


Name	Type	ReadOnly	Description
ExpenseClaimId [KEY]	String	True	The unique identifier of the expense claim.
UserId	String	False	The ID of the associated user.
ReceiptAggregate	String	False	Used to define Receipt rows using XML values. Should be provided on INSERT only.
ReceiptId	String	False	The ID of the receipt.
Status	String	True	The status code for the expense claim. The valid values are SUBMITTED, AUTHORISED, and PAID.
UpdatedDateUTC	Datetime	True	The date when the expense claim was last updated.
Total	Decimal	True	The total amount of an expense claim being paid.
AmountDue	Decimal	True	The amount due to be paid for an expense claim.
AmountPaid	Decimal	True	The amount still to pay for an expense claim.
PaymentDueDate	Date	True	The date when the expense claim is due to be paid.
ReportingDate	Date	True	The date the expense claim will be reported in Xero.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

HistoryBankTransactions

Create and query BankTransaction history and notes for a Xero organization.

Table Specific Information

The HistoryBankTransactions table allows you to read the notes created on a transaction, and the history of changes to that transaction. If a BankTransactionId is not provided, the history of all BankTransactions will be retrieved.

A BankTransactionId and Details is required for inserting a note:

INSERT INTO HistoryBankTransactions(BankTransactionId, Details) VALUES ('...', 'Refund for overpayment'),

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	A combination of the position of the history item and when it occurred.
BankTransactionId	String	True	The ID of the bank transaction the history item belongs to.
Changes	String	True	What type of change happened on the bank transactions
Date	Datetime	True	When the change occurred
User	String	True	The name of the user that made the change
Details	String	False	The change that was made
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

HistoryBatchPayments

Create and query BatchPayment history and notes for a Xero organization.

Table Specific Information

The HistoryBatchPayments table allows you to read the notes created on a batch payment, and the history of changes to that batch payment. If a BatchPaymentId is not provided, the history of all batch payments will be retrieved.

An BatchPaymentId and Details is required for inserting a note:

INSERT INTO HistoryBatchPayments(BatchPaymentId, Details) VALUES ('...', 'Batch submitted on 3/1/2019')

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	A combination of the position of the history item and when it occurred.
BatchPaymentId	String	True	The ID of the item the history item belongs to.
Changes	String	True	What type of change happened on the item
Date	Datetime	True	When the change occurred
User	String	True	The name of the user that made the change
Details	String	False	The change that was made
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

HistoryContacts

Create and query Contact history and notes for a Xero organization.

Table Specific Information

The HistoryContacts table allows you to read the notes created on a contact, and the history of changes to that contact. If a ContactId is not provided, the history of all Contacts will be retrieved.

A ContactId and Details is required for inserting a note:

INSERT INTO HistoryContacts(ContactId, Details) VALUES ('...', 'Reliable customer'),

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	A combination of the position of the history item and when it occurred.
ContactId	String	True	The ID of the contact the history item belongs to.
Changes	String	True	What type of change happened on the contact
Date	Datetime	True	When the change occurred
User	String	True	The name of the user that made the change
Details	String	False	The change that was made
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

HistoryCreditNotes

Create and query CreditNote history and notes for a Xero organization.

Table Specific Information

The HistoryCreditNotes table allows you to read the notes created on a credit note, and the history of changes to that credit note. If a CreditNoteId is not provided, the history of all CreditNotes will be retrieved.

SELECT * FROM HistoryCreditNotes WHERE CreditNoteId = '...'

A CreditNoteId and Details is required for inserting a note:

INSERT INTO HistoryCreditNotes(CreditNoteId, Details) VALUES ('...', 'Offered 12% discount'),

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	A combination of the position of the history item and when it occurred.
CreditNoteId	String	True	The ID of the credit note the history item belongs to.
Changes	String	True	What type of change happened on the credit note
Date	Datetime	True	When the change occurred
User	String	True	The name of the user that made the change
Details	String	False	The change that was made
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

HistoryInvoices

Create and query Invoice history and notes for a Xero organization.

Table Specific Information

The HistoryInvoices table allows you to read the notes created on a invoice, and the history of changes to that invoice. If a InvoiceId is not provided, the history of all Invoices will be retrieved.

An InvoiceId and Details is required for inserting a note:

INSERT INTO HistoryInvoices(InvoiceId, Details) VALUES ('...', 'Payment expected on December 1st'),

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	A combination of the position of the history item and when it occurred.
InvoiceId	String	True	The ID of the invoice the history item belongs to.
Changes	String	True	What type of change happened on the invoice
Date	Datetime	True	When the change occurred
User	String	True	The name of the user that made the change
Details	String	False	The change that was made
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

HistoryPurchaseOrders

Create and query PurchaseOrder history and notes for a Xero organization.

Table Specific Information

The HistoryPurchaseOrders table allows you to read the notes created on a purchase order, and the history of changes to that purchase order. If a PurchaseOrderId is not provided, the history of all PurchaseOrders will be retrieved.

A PurchaseOrderId and Details is required for inserting a note:

INSERT INTO HistoryPurchaseOrders(PurchaseOrderId, Details) VALUES ('...', 'Rescheduled delivery for October 9th'),

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	A combination of the position of the history item and when it occurred.
PurchaseOrderId	String	True	The ID of the purchase order the history item belongs to.
Changes	String	True	What type of change happened on the purchase order
Date	Datetime	True	When the change occurred
User	String	True	The name of the user that made the change
Details	String	False	The change that was made
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Invoices

Create, update, delete, and query 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.

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

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be 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.

INSERT INTO Invoices (Type, ContactName, LineItemAggregate) VALUES (
  'ACCPAY',
  'Boom FM',
  '<LineItem><Description>LineItem1Desc</Description><Quantity>1</Quantity><UnitAmount>30</UnitAmount></LineItem> <LineItem><Description>LineItem2Desc</Description><Quantity>2</Quantity><UnitAmount>50</UnitAmount></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

Items

Create, update, delete, and query items for a Xero organisation.

Table Specific Information

The Items table allows you to SELECT, INSERT, and UPDATE items for a Xero organization.

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

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new item record, the Code field must be defined.

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.

Columns


Name	Type	ReadOnly	Description
ItemId [KEY]	String	True	The unique, Xero-assigned identifier for the item.
Code	String	False	A user-defined code that identifies the item.
InventoryAssetAccountCode	String	False	The inventory asset account for the item. The account must be of type INVENTORY. The COGSAccountCode in PurchaseDetails is also required to create a tracked item.
Name	String	False	The name of the item.
PurchaseDescription	String	False	The purchase description of the item.
Description	String	False	A description of the item.
PurchaseDetails_UnitPrice	String	False	The unit Price of the item.
PurchaseDetails_COGSAccountCode	String	False	Cost of goods sold account. Only applicable to the purchase details of tracked items.
PurchaseDetails_AccountCode	String	False	The code of the account to be used for the purchased item.
PurchaseDetails_TaxType	String	False	The type of the tax rate, which varies by country. User-defined tax rates have a TaxType of TAX001, TAX002, TAX003, etc.
SalesDetails_UnitPrice	String	False	The unit price of the sold item.
SalesDetails_COGSAccountCode	String	False	Cost of goods sold account. Only applicable to the purchase details of tracked items.
SalesDetails_AccountCode	String	False	The code of the account to be used for the sold item.
SalesDetails_TaxType	String	False	The type of the tax rate, which varies by country. User-defined tax rates have a TaxType of TAX001, TAX002, TAX003, etc.
IsTrackedAsInventory	Boolean	False	True for items that are tracked as inventory. An item will be tracked as inventory if the InventoryAssetAccountCode and COGSAccountCode are set.
TotalCostPool	Decimal	True	The value of the item on hand. Calculated using average cost accounting.
QuantityOnHand	Double	True	The quantity of the item on hand.
IsSold	Boolean	False	Boolean value, defaults to true. When IsSold is true the item will be available on sales transactions in the Xero UI. If IsSold is updated to false then Description and SalesDetails values will be nulled.
IsPurchased	Boolean	False	Boolean value, defaults to true. When IsPurchased is true the item is available for purchase transactions in the Xero UI. If IsPurchased is updated to false then PurchaseDescription and PurchaseDetails values will be nulled.
UpdatedDateUTC	Datetime	True	Timestamp of the last change to the item record.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

LinkedTransactions

Create, update, and query LinkedTransactions for a Xero organisation.

Columns


Name	Type	ReadOnly	Description
LinkedTransactionID [KEY]	String	True	The Xero identifier for an Linked Transaction
SourceTransactionID	String	False	The identifier of the source transaction (the purchase component of a billable expense). Currently, only invoices with a type of ACCPAY can be a source transaction.
SourceLineItemID	String	False	The line item identifier from the source transaction.
ContactID	String	False	The identifier for the contact on the target transaction i.e. the customer that the expense is being billed on to.
TargetTransactionID	String	False	The identifier of the target transaction (the sale component of a billable expense). Currently, only invoices with a type of ACCREC can be a target transaction.
TargetLineItemID	String	False	The line item identifier from the target transaction. When allocating a billable expense to a target transaction the TargetLineItemID is optional.
Status	String	False	The status of the linked transaction. This is derived from the statuses of the source and target transactions and cannot be explicitly set/updated.
Type	String	False	This will always be BILLABLEEXPENSE. More types may be added in future.
UpdatedDateUTC	Datetime	True	The last modified date in UTC format
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

ManualJournals

Create, update, and query manual journals for a Xero organisation.

Table Specific Information

The ManualJournals table allows you to SELECT, INSERT, and UPDATE manual journals journal lines for a Xero organization. The ID column is generated by the connector; it combines the index of the line item with the unique, Xero-generated ManualJournalId.

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

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new manual journal record, the Narration field is required in addition to at least two line items. A journal record must contain at least one credit and one debit, so that the record as a whole is balanced.

You can insert a new object with multiple line items using an XML aggregate. The elements supported here are the same as the JournalLine columns without the "JournalLine_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the ManualJournals API for more details.
```
INSERT INTO ManualJournals (Narration, JournalLineAggregate) VALUES (
  'Accrued expenses',
  '<JournalLine><LineAmount>55.00</LineAmount><AccountCode>200</AccountCode></JournalLine> <JournalLine><LineAmount>-55.00</LineAmount><AccountCode>201</AccountCode></JournalLine>'
)
```
You can also insert a new line item on an existing object by specifying the Xero-generated value of the existing record. For example, to add a line item to an existing invoice:
```
INSERT INTO ManualJournals (ManualJournalId, JournalLine_LineAmount, JournalLine_AccountCode) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 55.00, '200')
```

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.

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	The journal line index combined with the ID of the manual journal.
ManualJournalId	String	False	The ID of the manual journal.
Narration	String	False	A description of the journal being posted.
JournalLine_LineAmount	Decimal	False	The total for the line item. This value is positive for debits and negative for credits.
JournalLine_AccountCode	String	False	The account code that identifies the account.
JournalLine_Description	String	False	The description for the manual journal.
JournalLine_TaxType	String	False	This field can be used to override the default tax code for the selected account.
JournalLine_TrackingCategory1_CategoryId	String	False	The ID of a tracking category. The API allows two optional tracking categories to be defined.
JournalLine_TrackingCategory1_CategoryName	String	False	The name of a tracking category. The API allows two optional tracking categories to be defined.
JournalLine_TrackingCategory1_OptionId	String	False	The ID of a tracking category. The API allows two optional tracking categories to be defined.
JournalLine_TrackingCategory1_OptionName	String	False	The option of a tracking category. The API allows two optional tracking categories to be defined.
JournalLine_TrackingCategory1_Option_Stat	String	False	The status of a tracking category. The API allows two optional tracking categories to be defined.
JournalLine_TrackingCategory2_CategoryId	String	False	The ID of a tracking category. The API allows two optional tracking categories to be defined.
JournalLine_TrackingCategory2_CategoryName	String	False	The name of a tracking category. The API allows two optional tracking categories to be defined.
JournalLine_TrackingCategory2_OptionId	String	False	The ID of a tracking category. The API allows two optional tracking categories to be defined.
JournalLine_TrackingCategory2_OptionName	String	False	The option of a tracking category. The API allows two optional tracking categories to be defined.
JournalLine_TrackingCategory2_Option_Stat	String	False	The status of a tracking category. The API allows two optional tracking categories to be defined.
JournalLineAggregate	String	False	Used to define JournalLine rows using XML values. Should be provided on INSERT only.
Date	Date	False	The date the journal was posted.
Status	String	False	The status code for the manual journal. The valid values are VALID, POSTED, DELETED, and VOIDED.
URL	String	False	A link to a source document.
ShowOnCashBasisReports	Boolean	False	This field indicates whether this journal should be included on cash-basis reports. The default value is true.
LineAmountTypes	String	False	The line amount type, which 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.
UpdatedDateUTC	Datetime	True	The date when the manual journal was last updated.
HasAttachments	Boolean	True	This field indicates whether the invoice has an attachment.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Payments

Create and query payments for a Xero organisation.

Table Specific Information

The Payments table allows you to SELECT and INSERT payments for a Xero organization.

Note that this tables does not include payments on expense claims - to retrieve those, you will need to read from the ExpenseClaimPayments view.

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

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new payment record, the InvoiceNumber and AccountCode fields are required.

Columns


Name	Type	ReadOnly	Description
PaymentId [KEY]	String	True	The ID of the payment.
Date	Date	False	The date the payment is being made.
Amount	Decimal	False	The amount of the payment. This value must be less than or equal to the outstanding amount owing on the invoice.
CurrencyRate	Decimal	False	The exchange rate when the payment is received. This field is only used for non-base-currency invoices and credit notes.
Reference	String	False	A optional description for the payment.
PaymentType	String	False	The payment type.
Status	String	False	The status of the payment.
UpdatedDateUTC	Datetime	False	The date when the payment was last updated.
IsReconciled	Boolean	False	This field indicates whether the payment has been reconciled. Only available when querying for one payment at a time.
BatchPaymentId	String	True	If the payment was submitted through BatchPayments, this is the ID of the batch it was provided with.
InvoiceId	String	False	The ID of the invoice. When applying a payment, specify either the InvoiceId or the InvoiceNumber.
InvoiceNumber	String	False	The invoice number of the invoice or credit note. When applying a payment, specify either the InvoiceId or the InvoiceNumber.
CreditNoteId	String	False	The ID of the credit note. When applying a payment, specify either the CreditNoteId or the CreditNoteNumber.
CreditNoteNumber	String	False	The credit note number. When applying a payment, specify either the CreditNoteId or the CreditNoteNumber.
AccountId	String	False	The ID of the account used to make the payment. The account type must be BANK or payments to the account must be enabled.
AccountCode	String	False	The account code of the account used to make the payment.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

PurchaseOrders

Create, update, delete, and query purchase orders for a Xero organisation.

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	The line item index combined with the ID of the purchase order.
PurchaseOrderId	String	False	The ID of the purchase order.
PurchaseOrderNumber	String	False	A unique identifier for the purchase order that identifies it to the user.
Reference	String	False	An optional field to store a reference.
Type	String	False	The purchase order type. The valid values are ACCPAYCREDIT and ACCRECCREDIT.
Contact_ContactId	String	False	The ID of the contact.
Contact_Name	String	False	The name of the contact.
DeliveryDate	Date	False	The date the purchase order is to be delivered.
ExpectedArrivalDate	Date	False	The date the purchase is expected to arrive.
DeliveryAddress	String	False	The address the goods are to be delivered to.
AttentionTo	String	False	The person the delivery is going to.
Telephone	String	False	The phone number of the person accepting the delivery.
DeliveryInstructions	String	False	Free-form field for information needed to complete the delivery.
Date	Date	False	The date the purchase order was created.
Status	String	False	The status of the purchase order.
LineAmountTypes	String	False	The line amount type, which determines whether line amounts include tax (default). The valid values are Exclusive, Inclusive, and NoTax. Purchase orders cannot be approved without this field being defined.
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 of the purchase order. This value must be at least one character. Invoices cannot be approved without these fields.
LineItem_Quantity	Double	False	The quantity of the subject of the line item. This value must be greater than or equal to 0. Invoices cannot be approved without these fields.
LineItem_UnitAmount	Decimal	False	The unit amount of the subject of the line item. Invoices cannot be approved without these fields.
LineItem_ItemCode	String	False	The user-defined item code.
LineItem_AccountCode	String	False	The account code. The account code must be active for the organisation. Invoices cannot be approved without these fields.
LineItem_TaxType	String	False	The description for the line item of the purchase order. This value must be at least one character. Invoices cannot be approved without these fields.
LineItem_TaxAmount	Decimal	False	The quantity of the subject of the line item. This value must be greater than or equal to 0. Invoices cannot be approved without these fields.
LineItem_LineAmount	Decimal	False	The total of the Quantity field multiplied by the UnitAmount field with discounts applied.
LineItem_DiscountRate	Double	False	The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type purchase orders.
LineItemAggregate	String	False	Used to define LineItem rows using XML values. Should be provided on INSERT only.
SubTotal	Decimal	False	The subtotal of the purchase order, excluding taxes.
TotalTax	Decimal	False	The total tax on the purchase order.
Total	Decimal	False	The total of the purchase order (the subtotal plus the total tax).
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.
UpdatedDateUTC	Datetime	True	The date when the purchase order was last updated.
HasAttachments	Boolean	True	Indicates if the PurchaseOrder has a file attached.
CurrencyCode	String	False	The currency used for the purchase order.
BrandingThemeId	String	False	The ID of the branding theme.
CurrencyRate	Decimal	False	Exchange rate to base currency when money is spent or received. For example, 0.7500. This field is used for only bank transactions not in the base currency. If this is not specified for non-base-currency accounts then either the user-defined rate or the XE.com daily rate will be used.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Receipts

Create, update, delete, and query receipts for a Xero organisation.

Table Specific Information

The Receipts table allows you to SELECT, INSERT, UPDATE, and DELETE receipt line items for a Xero organization. The ID column is generated by the connector; it combines the index of the line item with the unique, Xero-generated ReceiptId.

Note that this table only includes draft receipts - to retrieve receipts that have already been claimed, you will need to read from the ExpenseClaimReceipts view.

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

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a receipt record, the following fields are required in addition to at least one line item: Date, Contact_Name, and User_UserId. To add a new line item, the following fields are required: LineItem_Description, LineItem_UnitAmount, and LineItem_AccountCode.

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

INSERT INTO Receipts (Date, Contact_Name, User_UserId, LineItemAggregate) VALUES (
  '4/19/2006 9:16:48 AM',
  'John Doe',
  'c81045b2-5740-4aea-bf8a-3956941af387',
  '<LineItem><Description>Coffee with client</Description><UnitAmount>13.00</UnitAmount><AccountCode>200</AccountCode></LineItem> <LineItem><Description>Bagel</Description><UnitAmount>2.00</UnitAmount><AccountCode>200</AccountCode></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, to add a line item to an existing invoice:
```
INSERT INTO Receipts (ReceiptId, LineItem_Description, LineItem_UnitAmount) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'Coffee with client', 13.80)
```

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.

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	The line item index combined with the unique, Xero-generated identifier for the receipt.
ReceiptId	String	False	The unique, Xero-generated identifier for the script.
Date	Date	False	The date of the receipt.
Contact_ContactId	String	False	The ID of the contact. A contact must be specified when creating a receipt.
Contact_Name	String	False	The name of the contact.
LineItem_Description	String	False	The description for the item.
LineItem_UnitAmount	Decimal	False	The unit amount of the item.
LineItem_AccountCode	String	False	The code for the associated account.
LineItem_Quantity	Double	False	The quantity of the item.
LineItem_TaxType	String	False	The tax type. This field can be used to override the default tax code for the selected account.
LineItem_LineAmount	Decimal	False	The total of the unit amount multiplied by the quantity.
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 percentage discount being applied to the line item.
LineItemAggregate	String	False	Used to define LineItem rows using XML values. Should be provided on INSERT only.
User_UserId	String	False	The user in the organisation that the expense claim receipt is for.
Reference	String	False	Additional reference number.
LineAmountTypes	String	False	This field specifies whether line amounts include tax (default). When this field is not specified, line amounts totals do not include tax. The valid values are Exclusive, Inclusive, and NoTax.
SubTotal	Decimal	False	The total, excluding taxes, of the receipt.
TotalTax	Decimal	False	The total tax on the receipt.
Total	Decimal	False	The total of the receipt -- the sum of SubTotal plus TotalTax.
Status	String	True	The current status of the receipt. The valid status types are DRAFT, SUBMITTED, AUTHORISED, and DECLINED.
ReceiptNumber	String	True	The Xero-generated number of the receipt in sequence in the current claim for the given user.
UpdatedDateUTC	Datetime	True	The date when the receipt was last updated.
HasAttachments	Boolean	True	This field indicates whether the receipt has attachments.
URL	String	True	A link to a source document.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

TaxRates

Usage information for the operation TaxRates.rsd.

Table Specific Information

The TaxRates table allows you to SELECT, INSERT, and UPDATE tax components for tax rates in 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 TaxType.

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

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To create a new tax rate, the Name field must be set in addition to at least one tax component; the TaxComponent_Name, TaxComponent_Rate, and TaxComponent_IsCompound fields are required to insert a new line item.

In addition to inserting a single row, this table offers 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 TaxComponent columns without the "TaxComponent_" prefix.

INSERT INTO TaxRates (Name, TaxComponentAggregate) VALUES (
  'My Tax Rate',
  '<TaxComponent><Name>customTax1</Name><Rate>1.0000</Rate><IsCompound>false</IsCompound></TaxComponent> <TaxComponent><Name>customTax2</Name><Rate>2.0000</Rate><IsCompound>false</IsCompound></TaxComponent>'
)

You can also insert a new tax component on an existing object by specifiying the Xero-generated value for the existing record. For example, adding a tax component to an existing tax rate:
```
INSERT INTO TaxRates (TaxType, TaxComponent_Name, TaxComponent_Rate, TaxComponent_IsCompound) VALUES ('TAX001', 'customTax2', '2.0000', 'False')
```

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.

Note: You cannot update predefined tax rates through the Xero API.

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.

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	The tax component index combined with the TaxType.
TaxType	String	True	The tax type for the tax rate. There are default tax rates, additionally new tax rates can be setup for a Xero organisation. All new tax rates added have a TaxType of the format TAX001, TAX002 etc.
Name	String	False	The name for the tax rate.
TaxComponent_Name	String	False	The name of Tax Component
TaxComponent_Rate	Double	False	The tax Rate (up to 4dp).
TaxComponent_IsCompound	Boolean	False	Boolean to describe if Tax rate is compounded.
TaxComponentAggregate	String	False	Used to define TaxComponent rows using XML values. Should be provided on INSERT only.
Status	String	False	The Status of the Tax Rate e.g. ACTIVE, DELETED
ReportTaxType	String	False	The report tax type. Required for AU, NZ, and UK organisations (US and Global do not support this element).
CanApplyToAssets	Boolean	True	Boolean to describe if tax rate can be used for asset accounts.
CanApplyToEquity	Boolean	True	Boolean to describe if tax rate can be used for equity accounts.
CanApplyToExpenses	Boolean	True	Boolean to describe if tax rate can be used for expense accounts.
CanApplyToLiabilities	Boolean	True	Boolean to describe if tax rate can be used for liability accounts.
CanApplyToRevenue	Boolean	True	Boolean to describe if tax rate can be used for revenue accounts.
DisplayTaxRate	Double	True	Tax Rate (decimal to 4dp) e.g 12.5000.
EffectiveRate	Double	True	Effective Tax Rate (decimal to 4dp) e.g 12.5000.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Views

Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.

Jitterbit Connector for Xero Views


Name	Description
AgedPayablesByContact	Returns Aged Payables for a Contact.
AgedReceivablesByContact	Returns Aged Receivables for a Contact.
BalanceSheet	Returns a monthly Balance Sheet.
BankStatement	Returns the transactions listed on the account.
BankSummary	Returns the balances and cash movements for each bank account.
BrandingThemes	Returns the Branding Themes defined for the Xero organisation.
BudgetLines	Query Budget line items for a Xero organisation
Budgets	Query Budgets for a Xero organisation
BudgetSummary	Returns a monthly Budget Summary.
ContactCISSettings	Query CIS settings for contacts in a Xero organisation.
CreditNoteAllocations	Query credit note allocations for a Xero organisation.
Currencies	Retrieves the Currencies for a Xero organisation.
ExecutiveSummary	Returns a summary of changes between a month and the previous month
ExpenseClaimPayments	Create and query payments for a Xero organisation.
ExpenseClaimReceipts	Create, update, delete, and query claimed receipts for a Xero organisation.
HistoryBankTransfers	Create and query BankTransfer history and notes for a Xero organization.
HistoryExpenseClaims	Create and query ExpenseClaim history and notes for a Xero organization.
HistoryItems	Create and query Item history and notes for a Xero organization.
HistoryOverpayments	Create and query Overpayment history and notes for a Xero organization.
HistoryPayments	Create and query Payment history and notes for a Xero organization.
HistoryPrepayments	Create and query Prepayment history and notes for a Xero organization.
HistoryReceipts	Create and query Receipt history and notes for a Xero organization.
HistoryRepeatingInvoices	Create and query RepeatingInvoice history and notes for a Xero organization.
Journals	Query the line items in journals for a Xero organisation.
Organisation	Query organisation data for a Xero organisation.
OrganisationActions	Query organisation data for a Xero organisation.
OverpaymentAllocations	Query overpayment allocations for a Xero organisation.
Overpayments	Create and query Overpayments for a Xero organisation.
PrepaymentAllocations	Query prepayment allocations for a Xero organisation.
Prepayments	Create and query Prepayments for a Xero organisation.
ProfitAndLoss	Returns a Profit and Loss report over a date range.
Quotes	Query quotes for a Xero organization
RepeatingInvoices	Query Repeating Invoice templates.
Tenants	Query the list of organizations connected to your Xero account.
TrackingCategories	Query Tracking Categories for a Xero organisation.
TrialBalance	Retrieve a monthly Trial Balance.
Users	Query users for a Xero organisation.

AgedPayablesByContact

Returns Aged Payables for a Contact.

Table-Specific Information

Select

If the ContactId is not provided, the Aged Payables report for all contacts will be returned, one after the other.

Columns


Name	Type	Description
ID [KEY]	String	The row number, combined with the contact ID.
ContactId	String	The ID of the contact this is reporting on.
Date	Datetime	The Date the bill is created.
Reference	String	Purchase order reference for this bill.
DueDate	Datetime	Date the amount is due.
Overdue	Decimal	Amount due passed due date.
Currency	String	The currency used for all fields but DueLocal, or empty if the default currency is used
Total	Decimal	Total amount due.
Paid	Decimal	Amount paid.
Credited	Decimal	Amount credited.
Due	Decimal	Amount due.
DueLocal	Decimal	Amount due in the default organisation currency.
InvoiceId	String	The invoice for this bill.
TenantId	String	The ID of the tenant to query instead of the connection tenant

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
fromDate	Datetime	Show all payable invoices from this date for contact.
toDate	Datetime	Show all payable invoices to this date for the contact.

AgedReceivablesByContact

Returns Aged Receivables for a Contact.

Table-Specific Information

Select

If the ContactId is not provided, the Aged Payables report for all contacts will be returned, one after the other.

Columns


Name	Type	Description
ID [KEY]	String	The row number, combined with the contact ID.
ContactId	String	The ID of the contact this is reporting on.
Date	Datetime	The Date the invoice is created.
Reference	String	Sale order reference for this bill.
DueDate	Datetime	Date the amount is due.
Overdue	Decimal	Amount due passed due date.
Currency	String	The currency used for all fields but DueLocal. Is empty if multi-currency is not in use.
Total	Decimal	Total amount due.
Paid	Decimal	Amount paid.
Credited	Decimal	Amount credited.
Due	Decimal	Amount due.
DueLocal	Decimal	Amount due in the default organisation currency.
TenantId	String	The ID of the tenant to query instead of the connection tenant

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
fromDate	Datetime	Show all payable invoices from this date for contact.
toDate	Datetime	Show all payable invoices to this date for the contact.

BalanceSheet

Returns a monthly Balance Sheet.

Columns


Name	Type	Description
ID [KEY]	Integer	The row number.
Label1	String	The Label column of the BalanceSheet report.
Label2	String	The Value column of the BalanceSheet report.
Label3	String	The Label column of the BalanceSheet report.
Label4	String	The Value column of the BalanceSheet report.
Label5	String	The Label column of the BalanceSheet report.
Label6	String	The Value column of the BalanceSheet report.
Label7	String	The Label column of the BalanceSheet report.
Label8	String	The Value column of the BalanceSheet report.
Label9	String	The Label column of the BalanceSheet report.
Label10	String	The Value column of the BalanceSheet report.
Label11	String	The Label column of the BalanceSheet report.
Label12	String	The Value column of the BalanceSheet report.
Label13	String	The Value column of the BalanceSheet report.
AccountId	String	The ID of the row's account from the BalanceSheet report.
TenantId	String	The ID of the tenant to query instead of the connection tenant

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Date	Datetime	As at date.
Periods	Int	The number of periods to go back from ToDate.
Timeframe	String	How big each period should be (MONTH, QUARTER or YEAR)
TrackingOptionID1	String	Show all payable invoices to this date for the contact.
TrackingOptionID2	String	Show all payable invoices to this date for the contact.
PaymentsOnly	Boolean	Set this to true to get cash transactions only.
StandardLayout	Boolean	If you set this parameter to true then no custom report layouts will be applied to response.

BankStatement

Returns the transactions listed on the account.

Table Specific Information

Select

A bankAccountId may be provided when querying this view. If one is not given, then this report will be run for each bank account stored in Xero.

Columns


Name	Type	Description
BankAccountId	String	The ID of the bank account this is reporting on
Date	Date	The date when the transaction occurred
Description	String	The transaction's description
Reference	String	The transaction's reference value
Reconciled	String	Whether the transaction has been reconciled ('Yes' or 'No')
Source	String	Where the transaction came from
Amount	Float	The transaction's value
Balance	Float	The statement's running total
TenantId	String	The ID of the tenant to query instead of the connection tenant

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
fromDate	Date	The start date of the statement
toDate	Date	The end date of the statement. Must be within 365 days of the start.

BankSummary

Returns the balances and cash movements for each bank account.

Columns


Name	Type	Description
ID [KEY]	Integer	The row number.
BankAccount	String	The Bank account number.
OpeningBalance	Decimal	Amount of funds in the account at the beginning of the financial period.
CashReceived	Decimal	Amount added to the account.
CashSpent	Decimal	Amount subtracted from the account.
FXGain	Decimal	Changes in the account caused by currency conversions
ClosingBalance	Decimal	Amount of funds in the account at the end of the financial period.
TenantId	String	The ID of the tenant to query instead of the connection tenant

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
toDate	Datetime	Starting date.
fromDate	Datetime	Closing date.
PaymentsOnly	Boolean	Set this to true to get cash transactions only.

BrandingThemes

Returns the Branding Themes defined for the Xero organisation.

Table Specific Information

The BrandingThemes view allows you to SELECT branding themes for a Xero organization.

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.

Columns


Name	Type	Description
BrandingThemeId [KEY]	String	The unique Xero identifier for the branding theme.
Name	String	The name of the branding theme.
SortOrder	Int	The ranked order of the branding theme. The value for the default branding theme is 0.
CreatedDateUTC	Datetime	The timestamp when the branding theme was created.
TenantId	String	The ID of the tenant to query instead of the connection tenant

BudgetLines

Query Budget line items for a Xero organisation

Columns


Name	Type	Description
Id [KEY]	String	The line item index combined with the ID of the account.
BudgetId	String	The ID of the invoice. This field is assigned by Xero.
AccountId	String	The account that applies to this budget line item.
AccountCode	String	The code of this budget line item account.
Period	String	The year and month that the balance applies to.
Amount	Decimal	The amount budgeted to the account for the period.
Notes	String	Any notes that apply to the balance.
TenantId	String	The ID of the tenant to query instead of the connection tenant

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
DateFrom	Datetime	Includes budget periods starting from this date.
DateTo	Datetime	Includes budget periods until this date.

Budgets

Query Budgets for a Xero organisation

Columns


Name	Type	Description
BudgetId [KEY]	String	The ID of the budget. This field is assigned by Xero.
Type	String	The type of the budget, either OVERALL or TRACKING.
Description	String	The budget description.
TrackingCategory1_CategoryId	String	The ID of a tracking category.
TrackingCategory1_OptionId	String	The ID a a tracking category option.
TrackingCategory1_Name	String	The name of the tracking category.
TrackingCategory1_Option	String	The value of the tracking category option.
TrackingCategory2_CategoryId	String	The ID of a tracking category.
TrackingCategory2_OptionId	String	The ID a a tracking category option.
TrackingCategory2_Name	String	The name of the tracking category.
TrackingCategory2_Option	String	The value of the tracking category option.
UpdatedDateUTC	Datetime	The date when the budget was last updated.
TenantId	String	The ID of the tenant to query instead of the connection tenant

BudgetSummary

Returns a monthly Budget Summary.

Columns


Name	Type	Description
ID [KEY]	Integer	The row number.
BankAccount	String	The Bank account number.
Period1	String	Period 1 of the report
Period2	String	Period 2 of the report
Period3	String	Period 3 of the report
Period4	String	Period 4 of the report
Period5	String	Period 5 of the report
Period6	String	Period 6 of the report
Period7	String	Period 7 of the report
Period8	String	Period 8 of the report
Period9	String	Period 9 of the report
Period10	String	Period 10 of the report
Period11	String	Period 11 of the report
Period12	String	Period 12 of the report
TenantId	String	The ID of the tenant to query instead of the connection tenant

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Date	Datetime	As at date.
Periods	Integer	The number of periods to compare (integer between 1 and 12).
Timeframe	Integer	The period size to compare to: Specify 1 for month, 3 for quarter, or 12 for year).

ContactCISSettings

Query CIS settings for contacts in a Xero organisation.

Columns


Name	Type	Description
ContactId [KEY]	String	The unique Xero identifier for the contact.
CISEnabled	Boolean	Whether the contact is currently a CIS subcontractor
Rate	Int	The CIS deduction rate for the contact, in percent
TenantId	String	The ID of the tenant to query instead of the connection tenant

CreditNoteAllocations

Query credit note allocations for a Xero organisation.

Columns


Name	Type	Description
Id [KEY]	String	The allocation index combined with the ID of the credit note.
CreditNoteId	String	The ID of the credit note.
Allocation_AppliedAmount	Decimal	The amount to be applied from this credit note to a given invoice.
Allocation_Date	Date	The date the allocation was made.
Allocation_InvoiceId	String	The ID of the invoice this allocation has been applied to.
Allocation_InvoiceNumber	String	The Invoice this allocation has been applied to.
TenantId	String	The ID of the tenant to query instead of the connection tenant

Currencies

Retrieves the Currencies for a Xero organisation.

Table Specific Information

The Currencies table allows you to SELECT currencies for a Xero organization.

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.

Columns


Name	Type	Description
Code [KEY]	String	The ISO 4217 currency code.
Description	String	A name that identifies the currency.
TenantId	String	The ID of the tenant to query instead of the connection tenant

ExecutiveSummary

Returns a summary of changes between a month and the previous month

Table Specific Information

Select

A date may be provided when querying this view. If it is not provided, then this report will compare the current month with the previous month.

Columns


Name	Type	Description
Metric	String	The value that is being compared between months
ThisMonth	Decimal	The value of the metric at this month
PreviousMonth	Decimal	The value of the metric at the previous month
Variance	Decimal	The percent difference between the previous month and this month.
TenantId	String	The ID of the tenant to query instead of the connection tenant

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
date	Date	The month to compare with the previous month.

ExpenseClaimPayments

Create and query payments for a Xero organisation.

View Specific Information

The ExpenseClaimPayments view allows you to SELECT payments to different expense claims for a Xero organization. The ID column is generated by the connector; it combines the index of the line item with the unique, Xero-generated ExpenseClaimID.

Note that this table does not include other types of Payments - to retrieve those, you will need to read from the Payments table.

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.

Columns


Name	Type	Description
Id [KEY]	String	The line item index combined with the unique, Xero-generated identifier for the expense claim.
ExpenseClaimId	String	The ID of the expense claim that this payment belongs to
Date	Date	The date the payment is being made.
Amount	Decimal	The amount of the payment. This value must be less than or equal to the outstanding amount owing on the expense claim.
Reference	String	A optional description for the payment.
AccountId	String	The ID of the account used to make the payment. The account type must be BANK or payments to the account must be enabled.
AccountCode	String	The account code of the account used to make the payment.
AccountName	String	The account code of the account used to make the payment.
TenantId	String	The ID of the tenant to query instead of the connection tenant

ExpenseClaimReceipts

Create, update, delete, and query claimed receipts for a Xero organisation.

View Specific Information

The ExpenseClaimReceipts view allows you to SELECT claimed receipt line items for a Xero organization. The ID column is generated by the connector; it combines the index of the line item with the unique, Xero-generated ExpenseClaimID.

Note that this table does not include draft receipts - to retrieve those, you will need to read from the Receipts table.

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.

Columns


Name	Type	Description
Id [KEY]	String	The line item index combined with the unique, Xero-generated identifier for the expense claim.
ExpenseClaimId	String	The ID of the expense claim that this receipt belongs to
ReceiptId	String	The unique, Xero-generated identifier for the receipt.
Date	Date	The date of the receipt.
Contact_ContactId	String	The ID of the contact. A contact must be specified when creating a receipt.
Contact_Name	String	The name of the contact.
LineItem_Description	String	The description for the item.
LineItem_UnitAmount	Decimal	The unit amount of the item.
LineItem_AccountCode	String	The code for the associated account.
LineItem_Quantity	Double	The quantity of the item.
LineItem_TaxType	String	The tax type. This field can be used to override the default tax code for the selected account.
LineItem_LineAmount	Decimal	The total of the unit amount multiplied by the quantity.
LineItem_TrackingCategory1_CategoryId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName	String	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName	String	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat	String	The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName	String	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName	String	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat	String	The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_DiscountRate	Double	The percentage discount being applied to the line item.
User_UserId	String	The user in the organisation that the expense claim receipt is for.
Reference	String	Additional reference number.
LineAmountTypes	String	This field specifies whether line amounts include tax (default). When this field is not specified, line amounts totals do not include tax. The valid values are Exclusive, Inclusive, and NoTax.
SubTotal	Decimal	The total, excluding taxes, of the receipt.
TotalTax	Decimal	The total tax on the receipt.
Total	Decimal	The total of the receipt -- the sum of SubTotal plus TotalTax.
Status	String	The current status of the receipt. The valid status types are DRAFT, SUBMITTED, AUTHORISED, and DECLINED.
ReceiptNumber	String	The Xero-generated number of the receipt in sequence in the current claim for the given user.
UpdatedDateUTC	Datetime	The date when the receipt was last updated.
HasAttachments	Boolean	This field indicates whether the receipt has attachments.
URL	String	A link to a source document.
TenantId	String	The ID of the tenant to query instead of the connection tenant

HistoryBankTransfers

Create and query BankTransfer history and notes for a Xero organization.

View Specific Information

The HistoryBankTransfers view allows you to read the notes created on a transfer, and the history of changes to that transfer. If a BankTransferId is not provided, the history of all BankTransfers will be retrieved.

Columns


Name	Type	Description
Id [KEY]	String	A combination of the position of the history item and when it occurred.
BankTransferId	String	The ID of the bank transfer the history item belongs to.
Changes	String	What type of change happened on the bank transfers
Date	Datetime	When the change occurred
User	String	The name of the user that made the change
Details	String	The change that was made
TenantId	String	The ID of the tenant to query instead of the connection tenant

HistoryExpenseClaims

Create and query ExpenseClaim history and notes for a Xero organization.

View Specific Information

The HistoryExpenseClaims view allows you to read the notes created on a expense claim, and the history of changes to that expense claim. If a ExpenseClaimId is not provided, the history of all ExpenseClaims will be retrieved.

Columns


Name	Type	Description
Id [KEY]	String	A combination of the position of the history item and when it occurred.
ExpenseClaimId	String	The ID of the expense claim the history item belongs to.
Changes	String	What type of change happened on the expense claim
Date	Datetime	When the change occurred
User	String	The name of the user that made the change
Details	String	The change that was made
TenantId	String	The ID of the tenant to query instead of the connection tenant

HistoryItems

Create and query Item history and notes for a Xero organization.

View Specific Information

The HistoryItems view allows you to read the notes created on a item, and the history of changes to that item. If a ItemId is not provided, the history of all Items will be retrieved.

Columns


Name	Type	Description
Id [KEY]	String	A combination of the position of the history item and when it occurred.
ItemId	String	The ID of the item the history item belongs to.
Changes	String	What type of change happened on the item
Date	Datetime	When the change occurred
User	String	The name of the user that made the change
Details	String	The change that was made
TenantId	String	The ID of the tenant to query instead of the connection tenant

HistoryOverpayments

Create and query Overpayment history and notes for a Xero organization.

View Specific Information

The HistoryOverpayments view allows you to read the notes created on a overpayment, and the history of changes to that overpayment. If a OverpaymentId is not provided, the history of all Overpayments will be retrieved.

Columns


Name	Type	Description
Id [KEY]	String	A combination of the position of the history item and when it occurred.
OverpaymentId	String	The ID of the overpayment the history item belongs to.
Changes	String	What type of change happened on the overpayment
Date	Datetime	When the change occurred
User	String	The name of the user that made the change
Details	String	The change that was made
TenantId	String	The ID of the tenant to query instead of the connection tenant

HistoryPayments

Create and query Payment history and notes for a Xero organization.

Columns


Name	Type	Description
Id [KEY]	String	A combination of the position of the history item and when it occurred.
PaymentId	String	The ID of the payment the history item belongs to.
Changes	String	What type of change happened on the payment
Date	Datetime	When the change occurred
User	String	The name of the user that made the change
Details	String	The change that was made
TenantId	String	The ID of the tenant to query instead of the connection tenant

HistoryPrepayments

Create and query Prepayment history and notes for a Xero organization.

View Specific Information

The HistoryPrepayments view allows you to read the notes created on a prepayment, and the history of changes to that prepayment. If a PrepaymentId is not provided, the history of all Prepayments will be retrieved.

Columns


Name	Type	Description
Id [KEY]	String	A combination of the position of the history item and when it occurred.
PrepaymentId	String	The ID of the prepayment the history item belongs to.
Changes	String	What type of change happened on the prepayment
Date	Datetime	When the change occurred
User	String	The name of the user that made the change
Details	String	The change that was made
TenantId	String	The ID of the tenant to query instead of the connection tenant

HistoryReceipts

Create and query Receipt history and notes for a Xero organization.

View Specific Information

The HistoryReceipts view allows you to read the notes created on a receipt, and the history of changes to that receipt. If a ReceiptId is not provided, the history of all Receipts will be retrieved.

Columns


Name	Type	Description
Id [KEY]	String	A combination of the position of the history item and when it occurred.
ReceiptId	String	The ID of the receipt the history item belongs to.
Changes	String	What type of change happened on the receipt
Date	Datetime	When the change occurred
User	String	The name of the user that made the change
Details	String	The change that was made
TenantId	String	The ID of the tenant to query instead of the connection tenant

HistoryRepeatingInvoices

Create and query RepeatingInvoice history and notes for a Xero organization.

View Specific Information

The HistoryRepeatingInvoices view allows you to read the notes created on a repeating invoice, and the history of changes to that repeating invoice. If a RepeatingInvoiceId is not provided, the history of all RepeatingInvoices will be retrieved.

Columns


Name	Type	Description
Id [KEY]	String	A combination of the position of the history item and when it occurred.
RepeatingInvoiceId	String	The ID of the repeating invoice the history item belongs to.
Changes	String	What type of change happened on the repeating invoice
Date	Datetime	When the change occurred
User	String	The name of the user that made the change
Details	String	The change that was made
TenantId	String	The ID of the tenant to query instead of the connection tenant

Journals

Query the line items in journals for a Xero organisation.

Table Specific Information

The Journals view allows you to SELECT the journal lines in journals 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 JournalId.

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.

Columns


Name	Type	Description
Id [KEY]	String	The journal line index combined with the unique Xero identifier of the journal.
JournalId	String	The unique Xero identifier of the journal.
JournalDate	Date	The date the journal was posted.
JournalNumber	Integer	The journal number, assigned by Xero.
CreatedDateUTC	Datetime	The date the journal was entered in the system.
Reference	String	A reference value.
SourceId	String	The identifier for the source transaction (e.g. InvoiceId).
SourceType	String	The journal source type. The type of transaction that created the journal. (e.g ACCREC, ACCPAY, ACCRECCREDIT)
JournalLine_JournalLineId	String	The unique Xero identifier of the journal line item.
JournalLine_Description	String	The description of the journal line item.
JournalLine_AccountId	String	The account associated with the line item in the journal.
JournalLine_AccountCode	String	Customer-defined alphanumeric account code; e.g, 200 or SALES.
JournalLine_AccountType	String	The type of the account; e.g., BANK, CURRENT, or CRRLIAB.
JournalLine_AccountName	String	The name of the account.
JournalLine_NetAmount	Decimal	The net amount of the line item. This value is positive for debits and negative for credits.
JournalLine_GrossAmount	Decimal	The net amount plus the tax amount.
JournalLine_TaxAmount	Decimal	The total tax on a journal line.
JournalLine_TaxType	String	The type of the tax rate, which varies by country. User-defined tax rates have a TaxType of TAX001, TAX002, etc.
JournalLine_TaxName	String	A name that identifies the tax type.
LineItem_TrackingCategory1_CategoryId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName	String	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName	String	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat	String	The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName	String	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName	String	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat	String	The status of a tracking category. The API allows two optional tracking categories to be defined.
TenantId	String	The ID of the tenant to query instead of the connection tenant

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
ModifiedAfter	String	If set, only journals created or modified since this timestamp will be returned e.g. 2009-11-12T00:00:00.
PaymentsOnly	Boolean	Set this to true to get cash transactions only.
Offset	Int	If provided, only journals with a higher JournalNumber will be returned.

Organisation

Query organisation data for a Xero organisation.

Table Specific Information

The organization view allows you to SELECT organization data for a Xero organization.

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.

Columns


Name	Type	Description
ShortCode [KEY]	String	A unique identifier for the organisation.
APIKey	String	Unique APIKey for Xero-to-Xero transactions.
Name	String	Display name of the organisation shown in Xero.
LegalName	String	Organisation name shown on reports.
PaysTax	Boolean	Whether the organisation is registered with a local tax authority.
Version	String	The version of Xero used by the organisation.
BaseCurrency	String	Default currency for the organisation.
CountryCode	String	Country code for the organisation.
IsDemoCompany	String	Whether the organisation is a demo company.
OrganisationStatus	String	Will be set to ACTIVE if you can connect to the organisation via the Xero API.
RegistrationNumber	String	The registration number for NZ, AU, and UK organisations.
TaxNumber	String	Tax number of the organisation.
FinancialYearEndDay	String	Financial year end day of the organisation. For example, 1-31.
FinancialYearEndMonth	String	Financial year end month of the organisation. For example, 1-12.
PeriodLockDate	Date	Period lock date of the organisation.
EndOfYearLockDate	Date	End of year lock date of the organisation.
CreatedDateUTC	Datetime	Timestamp of when the organisation was created in Xero.
UpdatedDateUTC	Datetime	Timestamp of the last change to the user record.
OrganisationEntityType	String	The entity type of the organisation. For example, COMPANY.
Timezone	String	The time zone for the organisation.
Edition	String	BUSINESS or PARTNER. Parnter edition organizations have restricted functionality.
Class	String	One of: DEMO, TRIAL, STARTER, STANDARD, PREMIUM, PREMIUM_20, PREMIUM_50, PREMIUM_100, LEDGER, GST_CASHBOOK, NON_GST_CASHBOOK.
LineOfBusiness	String	Description of business type of the organisation.
Street_AddressLine1	String	The street address line 1.
Street_AddressLine2	String	The street address line 2.
Street_AddressLine3	String	The street address line 3.
Street_AddressLine4	String	The street address line 4.
Street_City	String	The street address city.
Street_PostalCode	String	The street address postal code.
Street_Country	String	The street address country.
Street_Region	String	The street address region.
Street_AttentionTo	String	The street address attention-to line.
POBox_AddressLine1	String	The PO box address line 1.
POBox_AddressLine2	String	The PO box address line 2.
POBox_AddressLine3	String	The PO box address line 3.
POBox_AddressLine4	String	The PO box address line 4.
POBox_City	String	The PO box address city.
POBox_PostalCode	String	The PO box address postal code.
POBox_Country	String	The PO box address country.
POBox_Region	String	The PO box address region.
POBox_AttentionTo	String	The PO box address attention-to line.
DDI_PhoneNumber	String	The DDI phone number.
DDI_PhoneAreaCode	String	The DDI area code.
DDI_PhoneCountryCode	String	The DDI country code.
Default_PhoneNumber	String	The default phone number.
Default_PhoneAreaCode	String	The default area code.
Default_PhoneCountryCode	String	The default country code.
Fax_PhoneNumber	String	The fax phone number.
Fax_PhoneAreaCode	String	The fax area code.
Fax_PhoneCountryCode	String	The fax country code.
Mobile_PhoneNumber	String	The mobile phone number.
Mobile_PhoneAreaCode	String	The mobile area code.
Mobile_PhoneCountryCode	String	The mobile country code.
Facebook_URL	String	The URL to the Facebook page of the organisation.
Twitter_URL	String	The URL to the Twitter page of the organisation.
GooglePlus_URL	String	The URL to the Google Plus page of the organisation.
LinkedIn_URL	String	The URL to the LinkedIn page of the organisation.
Website_URL	String	A URL to the website of the organisation.
PaymentTerms_Bills_Day	String	Default payment terms for bills (days).
PaymentTerms_Bills_Type	String	Default payment terms for bills (type).
PaymentTerms_Sales_Day	String	Default payment terms for sales (days).
PaymentTerms_Sales_Type	String	Default payment terms for sales (type).
TenantId	String	The ID of the tenant to query instead of the connection tenant

OrganisationActions

Query organisation data for a Xero organisation.

Columns


Name	Type	Description
Name [KEY]	String	The name of the permission.
Status	Bool	Whether the action is permitted to this app.
TenantId	String	The ID of the tenant to query instead of the connection tenant

OverpaymentAllocations

Query overpayment allocations for a Xero organisation.

Columns


Name	Type	Description
Id [KEY]	String	The allocation index combined with the ID of the overpayment.
OverpaymentId	String	The ID of the overpayment.
Allocation_AppliedAmount	Decimal	The amount to be applied from this overpayment to a given invoice.
Allocation_Date	Date	The date the allocation was made.
Allocation_InvoiceId	String	The ID of the invoice this allocation has been applied to.
Allocation_InvoiceNumber	String	The Invoice this allocation has been applied to.
TenantId	String	The ID of the tenant to query instead of the connection tenant

Overpayments

Create and query Overpayments for a Xero organisation.

Columns


Name	Type	Description
OverpaymentId [KEY]	String	The ID of the overpayment.
Date	Date	The date the overpayment was made.
Type	String	The overpayment type. The valid values are RECEIVE-OVERPAYMENT and SPEND-OVERPAYMENT.
RemainingCredit	Decimal	The remaining credit balance on the overpayment.
Contact_ContactId	String	The ID of the contact.
Contact_Name	String	The name of the contact.
Status	String	The status of the overpayment. Valid values are AUTHORISED, PAID, and VOIDED.
SubTotal	Decimal	The subtotal for the overpayment, which excludes taxes.
TotalTax	Decimal	The total tax for the overpayment.
Total	Decimal	The total for the overpayment, including tax -- the sum of SubTotal and TotalTax.
LineItem_LineItemId	String	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	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	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	The unit amount of the associated item in the line item. This field is required for invoice approval.
LineItem_ItemCode	String	The code that identifies the associated item.
LineItem_AccountCode	String	The account code. This must be active for the organisation. This field is required for invoice approval.
LineItem_TaxType	String	The tax type. This field is used to override AccountCode, the default tax code for the selected account.
LineItem_TaxAmount	Decimal	The automatically calculated percentage of tax, based on the tax rate, for the line amount.
LineItem_LineAmount	Decimal	The total of the quantity multiplied by the unit amount with any discounts applied.
LineItem_TrackingCategory1_CategoryId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName	String	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName	String	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat	String	The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName	String	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName	String	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat	String	The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_DiscountRate	Double	The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type (sales) invoices.
LineAmountTypes	String	The LineAmount Type of the overpayment. Valid values are Exclusive, Inclusive, and NoTax.
CurrencyCode	String	Currency used for the overpayment.
CurrencyRate	String	The currency rate for a multicurrency overpayment. If no rate is specified, the XE.com day rate is used.
UpdatedDateUTC	Datetime	The date when the overpayment was last updated.
Allocation1_AppliedAmount	Decimal	The amount to be applied from this credit note to a given invoice.
Allocation1_Date	Date	The date the allocation was made.
Allocation1_InvoiceId	String	The ID of the invoice this allocation has been applied to.
Allocation1_InvoiceNumber	String	The Invoice this allocation has been applied to.
Allocation2_AppliedAmount	Decimal	The amount to be applied from this credit note to a given invoice.
Allocation2_Date	Date	The date the allocation was made.
Allocation2_InvoiceId	String	The ID of the invoice this allocation has been applied to.
Allocation2_InvoiceNumber	String	The Invoice this allocation has been applied to.
PaymentIds	String	A comma-delimited list of payment Ids associated with the overpayment.
HasAttachments	Boolean	Boolean to indicate if a overpayment has an attachment.
TenantId	String	The ID of the tenant to query instead of the connection tenant

PrepaymentAllocations

Query prepayment allocations for a Xero organisation.

Columns


Name	Type	Description
Id [KEY]	String	The allocation index combined with the ID of the prepayment.
PrepaymentId	String	The ID of the prepayment.
Allocation_AppliedAmount	Decimal	The amount to be applied from this prepayment to a given invoice.
Allocation_Date	Date	The date the allocation was made.
Allocation_InvoiceId	String	The ID of the invoice this allocation has been applied to.
Allocation_InvoiceNumber	String	The Invoice this allocation has been applied to.
TenantId	String	The ID of the tenant to query instead of the connection tenant

Prepayments

Create and query Prepayments for a Xero organisation.

Columns


Name	Type	Description
PrepaymentId [KEY]	String	The ID of the prepayment.
Date	Date	The date the prepayment was made.
Reference	String	A optional description for the payment.
Type	String	The prepayment type. The valid values are RECEIVE-PREPAYMENT and SPEND-PREPAYMENT.
RemainingCredit	Decimal	The remaining credit balance on the prepayment.
Contact_ContactId	String	The ID of the contact.
Contact_Name	String	The name of the contact.
Status	String	The status of the prepayment. Valid values are AUTHORISED, PAID, and VOIDED.
SubTotal	Decimal	The subtotal for the prepayment, which excludes taxes.
TotalTax	Decimal	The total tax for the prepayment.
Total	Decimal	The total for the prepayment, including tax -- the sum of SubTotal and TotalTax.
LineItem_LineItemId	String	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	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	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	The unit amount of the associated item in the line item. This field is required for invoice approval.
LineItem_ItemCode	String	The code that identifies the associated item.
LineItem_AccountCode	String	The account code. This must be active for the organisation. This field is required for invoice approval.
LineItem_TaxType	String	The tax type. This field is used to override AccountCode, the default tax code for the selected account.
LineItem_TaxAmount	Decimal	The automatically calculated percentage of tax, based on the tax rate, for the line amount.
LineItem_LineAmount	Decimal	The total of the quantity multiplied by the unit amount with any discounts applied.
LineItem_TrackingCategory1_CategoryId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName	String	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName	String	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat	String	The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName	String	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName	String	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat	String	The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_DiscountRate	Double	The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type (sales) invoices.
LineAmountTypes	String	The LineAmount Type of the prepayment. Valid values are Exclusive, Inclusive, and NoTax.
CurrencyCode	String	Currency used for the prepayment.
CurrencyRate	String	The currency rate for a multicurrency prepayment. If no rate is specified, the XE.com day rate is used.
UpdatedDateUTC	Datetime	The date when the prepayment was last updated.
Allocation1_AppliedAmount	Decimal	The amount to be applied from this credit note to a given invoice.
Allocation1_Date	Date	The date the allocation was made.
Allocation1_InvoiceId	String	The ID of the invoice this allocation has been applied to.
Allocation1_InvoiceNumber	String	The Invoice this allocation has been applied to.
Allocation2_AppliedAmount	Decimal	The amount to be applied from this credit note to a given invoice.
Allocation2_Date	Date	The date the allocation was made.
Allocation2_InvoiceId	String	The ID of the invoice this allocation has been applied to.
Allocation2_InvoiceNumber	String	The Invoice this allocation has been applied to.
PaymentIds	String	A comma-delimited list of payment Ids associated with the prepayment.
HasAttachments	Boolean	Boolean to indicate if a prepayment has an attachment.
TenantId	String	The ID of the tenant to query instead of the connection tenant

ProfitAndLoss

Returns a Profit and Loss report over a date range.

Table-Specific Information

Select

There are a few different ways the range of dates can be specified when using this report:

1. When no parameters are specified, the report is run for the current month:

SELECT * FROM ProfitAndLoss

2. When just FromDate is specified, the report is run from FromDate to today:

SELECT * FROM ProfitAndLoss WHERE FromDate = '2018-01-01'

In this form, FromDate must be within a year of the current date.

3. When FromDate and ToDate are specified, the report is run from FromDate to ToDate:

SELECT * FROM ProfitAndLoss WHERE FromDate = '2018-01-01' AND ToDate = '2018-04-01'

In this form, FromDate must be within a year of ToDate.

4. When Periods and Timeframe are specified, the report is run from today back to the start of the Timeframe:

SELECT * FROM ProfitAndLoss WHERE Periods = 2 AND Timeframe = 'MONTH'

Note: When specifying the 'Month' timeframe, the Xero endpoint may generate a report for the last 30 days from the start of the specified month, even for the months with 31 days. As a workaround, you should specify a 31-day month in the ToDate column as an additional query criteria.

SELECT * FROM ProfitAndLoss WHERE Periods = 11 AND Timeframe = 'MONTH' AND ToDate='2020-12-31'

5. When Periods, Timeframe and ToDate are specified, the report is run from ToDate back to the start of the Timeframe:

SELECT * FROM ProfitAndLoss WHERE Periods = 2 AND Timeframe = 'MONTH' AND ToDate = '2018-04-01'

Columns


Name	Type	Description
ID [KEY]	Integer	The row number.
Label1	String	The Label column of the ProfitAndLossStandard report.
Label2	String	The Value column of the ProfitAndLossStandard report.
Label3	String	The Label column of the ProfitAndLossStandard report.
Label4	String	The Value column of the ProfitAndLossStandard report.
Label5	String	The Label column of the ProfitAndLossStandard report.
Label6	String	The Value column of the ProfitAndLossStandard report.
Label7	String	The Label column of the ProfitAndLossStandard report.
Label8	String	The Value column of the ProfitAndLossStandard report.
Label9	String	The Label column of the ProfitAndLossStandard report.
Label10	String	The Value column of the ProfitAndLossStandard report.
Label11	String	The Label column of the ProfitAndLossStandard report.
Label12	String	The Value column of the ProfitAndLossStandard report.
Label13	String	The Value column of the ProfitAndLossStandard report.
AccountId	String	The ID of the row's account from the ProfitAndLossStandard report.
TenantId	String	The ID of the tenant to query instead of the connection tenant

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
FromDate	Datetime	Show all payable invoices from this date for contact.
ToDate	Datetime	Show all payable invoices to this date for the contact.
Periods	Int	The number of periods to go back from ToDate.
Timeframe	String	How big each period should be (MONTH, QUARTER or YEAR)
TrackingCategoryID	String	If you specify the trackingCategoryID parameter then the Profit and Loss Report will show figures for each of the options in the category as separate columns.
TrackingOptionID	String	If you specify this parameter in addition to the trackingCategoryID then just one option will be returned (i.e. 1 column only)
TrackingCategoryID2	String	If you specify a second trackingCategoryID parameter then the Profit and Loss Report will show figures for each combination of options from the two categories as separate columns.
TrackingOptionID2	String	If you specify this parameter in addition to a second trackingCategoryID then just one option will be returned combined with the option/s from the first tracking category.
PaymentsOnly	Boolean	Set this to true to get cash transactions only.
StandardLayout	Boolean	If you set this parameter to true then no custom report layouts will be applied to response.

Quotes

Query quotes for a Xero organization

Columns


Name	Type	Description
Id [KEY]	String	The line item index combined with the ID of the Quote.
QuoteId	String	The ID of the Quote. This field is assigned by Xero.
ContactId	String	The ID of the contact.
ContactName	String	The name of the contact.
Date	Date	The date the quote was issued.
ExpiryDate	Date	The date the quote expires.
Status	String	The status of the quote.
LineItem_LineItemId	String	The Xero generated identifier for a line item.
LineItem_Description	String	The description of the line item.
LineItem_Quantity	Double	The quantity of the associated item in the line item.
LineItem_UnitAmount	Decimal	The unit amount of the associated item in the line item.
LineItem_ItemCode	String	The code that identifies the associated item.
LineItem_AccountCode	String	The account code. This must be active for the organisation.
LineItem_TaxType	String	The tax type. This field is used to override AccountCode, the default tax code for the selected account.
LineItem_TaxAmount	Decimal	The automatically calculated percentage of tax, based on the tax rate, for the line amount.
LineItem_LineAmount	Decimal	The total of the quantity multiplied by the unit amount with any discounts applied.
LineItem_DiscountRate	Double	The discount percentage being applied to the line item.
LineItem_TrackingCategory1_CategoryId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName	String	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName	String	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName	String	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName	String	The option of a tracking category. The API allows two optional tracking categories to be defined.
QuoteNumber	String	The unique, alphanumeric code identifying the quote.
LineAmountTypes	String	This field specifies whether line amounts include tax. The valid values are Exclusive, Inclusive, and NoTax.
Reference	String	An additional reference number for ACCREC-type invoices.
CurrencyCode	String	The ISO 4217 currency code.
CurrencyRate	Decimal	The currency rate for a multicurrency invoice. If no rate is specified, the XE.com day rate is used.
SubTotal	Decimal	The subtotal for the invoice, which excludes taxes.
TotalTax	Decimal	The total tax for the invoice.
Total	Decimal	The total for the invoice, including tax -- the sum of SubTotal and TotalTax.
TotalDiscount	Decimal	Total of discounts applied on the invoice line items.
BrandingThemeId	String	The ID of the branding theme.
Terms	String	The terms of the quote.
UpdatedDateUTC	Datetime	The date when the invoice was last updated.
TenantId	String	The ID of the tenant to query instead of the connection tenant

RepeatingInvoices

Query Repeating Invoice templates.

Table Specific Information

The RepeatingInvoice view allows you to SELECT the line items in repeating invoices 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 RepeatingInvoiceId.

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.

Columns


Name	Type	Description
Id [KEY]	String	The line item index combined with the ID of the invoice.
RepeatingInvoiceId	String	The ID of the invoice. This field is assigned by Xero.
Type	String	The type of the invoice. The valid values are ACCPAY and ACCREC.
ContactId	String	The ID of the contact. This value is required when inserting.
ContactName	String	The name of the contact. This value is required when inserting.
Schedule_Period	Integer	Integer used with the unit for the scheduled period. e.g. 1 (every 1 week).
Schedule_Unit	String	the schedule unit. The valid values are WEEKLY or MONTHLY.
Schedule_DueDate	Integer	Integer used with the due date type for the scheduled period. e.g. 20 (of following month).
Schedule_DueDateType	String	The due date type of the schedule. The valid values are DAYSAFTERBILLDATE, DAYSAFTERBILLMONTH, OFCURRENTMONTH, and OFFOLLOWINGMONTH
Schedule_StartDate	Date	Invoice date the first invoice in the repeating schedule.
Schedule_NextScheduledDate	Date	The calendar date of the next invoice in the schedule to be generated.
Schedule_EndDate	Date	Invoice end date. Only returned if the template has an end date set.
LineItem_LineItemId	String	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	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	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	The unit amount of the associated item in the line item. This field is required for invoice approval.
LineItem_ItemCode	String	The code that identifies the associated item.
LineItem_AccountCode	String	The account code. This must be active for the organisation. This field is required for invoice approval.
LineItem_TaxType	String	The tax type. This field is used to override AccountCode, the default tax code for the selected account.
LineItem_TaxAmount	Decimal	The automatically calculated percentage of tax, based on the tax rate, for the line amount.
LineItem_LineAmount	Decimal	The total of the quantity multiplied by the unit amount with any discounts applied.
LineItem_TrackingCategory1_CategoryId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName	String	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName	String	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat	String	The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName	String	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId	String	The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName	String	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat	String	The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_DiscountRate	Double	The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type (sales) invoices.
LineAmountTypes	String	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.
Reference	String	An additional reference number for ACCREC-type invoices.
BrandingThemeId	String	The ID of the branding theme.
CurrencyCode	String	The ISO 4217 currency code.
Status	String	The status code for the invoice. The valid values are DRAFT, SUBMITTED, and AUTHORISED.
SubTotal	Decimal	The subtotal for the invoice, which excludes taxes.
TotalTax	Decimal	The total tax for the invoice.
Total	Decimal	The total for the invoice, including tax -- the sum of SubTotal and TotalTax.
HasAttachments	Boolean	This field indicates whether the invoice has an attachment.
TenantId	String	The ID of the tenant to query instead of the connection tenant

Tenants

Query the list of organizations connected to your Xero account.

Columns


Name	Type	Description
TenantId [KEY]	String	A unique identifier for the tenant.
Name	String	The name of the organization or practice
Type	String	Whether the tenant is an organization or a practice
IsActive	Boolean	Whether the connection is currently using this tenant

TrackingCategories

Query Tracking Categories for a Xero organisation.

Table Specific Information

The TrackingCategories view allows you to SELECT tracking categories for a Xero organization. The ID column is generated by the connector; it combines the index of the line item with the unique, Xero-generated TrackingCategoryId.

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.

Columns


Name	Type	Description
Id [KEY]	String	The tracking option index combined with the unique Xero identifier of the tracking category.
TrackingCategoryId	String	The unique Xero identifier of the tracking category.
Name	String	The name of the tracking category. For example, Department or Region.
Status	String	The status of a tracking category. This will always be ACTIVE.
TrackingOptionId	String	The unique Xero identifier of the tracking option.
OptionName	String	The name of the tracking category option. For example, North or South.
TenantId	String	The ID of the tenant to query instead of the connection tenant

TrialBalance

Retrieve a monthly Trial Balance.

Columns


Name	Type	Description
ID [KEY]	Integer	The row number.
Account	String	Account name.
Debit	String	Debit trial balance for current month up to the date specified.
Credit	String	Credit trial balance for current month up to the date specified.
YTDDebit	String	Year to Date Debit.
YTDCredit	String	Year to Date Credit.
AccountId	String	Account ID
TenantId	String	The ID of the tenant to query instead of the connection tenant

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Date	Datetime	As at date.
PaymentsOnly	Boolean	Set this to true to get cash transactions only.

Users

Query users for a Xero organisation.

Table Specific Information

The Users table allows you to SELECT users for a Xero organization.

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.

Columns


Name	Type	Description
UserId [KEY]	String	The unique Xero identifier of the user.
FirstName	String	First name of the user.
LastName	String	Last name of the user.
UpdatedDateUTC	Datetime	Timestamp of the last change to the user record.
IsSubscriber	Boolean	Indicates if the user is the subscriber.
OrganisationRole	String	Organisation role of the user, values include READONLY, INVOICEONLY, STANDARD, FINANCIALADVISER, MANAGEDCLIENT, and CASHBOOKCLIENT.
TenantId	String	The ID of the tenant to query instead of the connection tenant

Stored Procedures

NOTE: Stored procedures are not currently supported. See the above note for details.

Stored procedures* are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure* because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures* take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.

Jitterbit Connector for Xero Stored Procedures


Name	Description
AllocatetoInvoice	Allocates a document against an Invoice.
DownloadAttachment	Obtains an attachment from a specified document in Xero.
GetLimitInfo	Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant.
GetOAuthAccessToken	Obtains the OAuth access token to be used for authentication with Xero.
GetOAuthAuthorizationURL	Obtains the OAuth authorization URL for authentication with various Xero services.
ListAttachments	Retrieves a list of attachments that have been uploaded against a document in Xero.
RefreshOAuthAccessToken	Refreshes the OAuth access token used for authentication with various Xero services.
RevokeTenant	Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.
SendInvoiceEmail	Sends an email to the contacts of a sales invoice
SwitchTenant	Changes the tenant this connection fetches data from.
UploadAttachment	Obtains a list of attachments an item has in Xero.

AllocatetoInvoice

Allocates a document against an Invoice.

Input


Name	Type	Required	Description
Type	String	True	The type of document to allocate. The allowed values are CreditNotes, Overpayments, Prepayments.
DocumentId	String	True	The ID of the document to be allocated.
InvoiceId	String	True	The invoice this document is being allocated against.
AppliedAmount	String	True	The amount being applied to the invoice.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant
Date	String	False	The date the overpayment is applied.

Result Set Columns


Name	Type	Description
Success	String	A boolean indicating if the operation was successful.

DownloadAttachment

Obtains an attachment from a specified document in Xero.

Input


Name	Type	Required	Description
Table	String	True	The table containing the document with the attachment. The allowed values are Invoices, Receipts, CreditNotes, BankTransactions, BankTransfers, Contacts, Accounts, ManualJournals.
ObjectId	String	True	The unique ID of the item to obtain an attachment from.
Filename	String	True	The name of the attachment to retrieve.
DownloadPath	String	False	The path of the file to be saved. Must be a path to a file, not just a directory.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Result Set Columns


Name	Type	Description
Success	String	A boolean indicating if the operation was successful.
FileData	String	Base 64 encoded file data for the attachment received.

GetLimitInfo

Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant.

Procedure-Specific Information

This procedure reports information that the connector caches after each API request. If no requests have been made to a tenant on a connection than this procedure will report 0 rows. Otherwise, the LastModified field reports the last time the information in the row was updated.

If the last modified time is too long ago for your application to use, then this table may be refreshed by executing a query. The query must read at least one row to ensure that an API request is triggered:

SELECT * FROM Organisation LIMIT 1

Result Set Columns


Name	Type	Description
TenantId	String	ID of the tenant that the limit applies to. The output contains a row for each tenant queried during the connection. Usually there is only one.
AppRemaining	Integer	The number of API calls the current OAuth app has this minute (max 10000). This applies across all tenants.
MinuteRemaining	Integer	The number of API calls the current OAuth app has this minute (max 60). This applies only to this tenant.
DayRemaining	Integer	The number of API calls the current OAuth app has today (max 5000). This applies only to this tenant.
RetryAfter	Integer	The suggested retry time, if one of the above limits was 0 on the most recent API call to the tenant.
LastModified	Datetime	When this information was last updated. The values in this table are only refreshed when performing API calls, so this may be older than the current time.

GetOAuthAccessToken

Obtains the OAuth access token to be used for authentication with Xero.

Input


Name	Type	Required	Description
AuthMode	String	True	The type of authentication mode to use. The allowed values are APP, WEB. The default value is WEB.
AuthToken	String	False	The authentication token returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
AuthKey	String	False	The AuthKey returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
CallbackURL	String	False	This field determines where the response is sent. The value of this parameter must exactly match one of the values registered in the Developer Portal, including the HTTP or HTTPS schemes, capitalization, and trailing forward slash ('/').
Verifier	String	False	The verifier token returned by Xero after using the URL obtained with GetOAuthAuthorizationURL. Required for only the Web AuthMode.
PKCEVerifier	String	False	The PKCEVerifier returned by GetOAuthAuthorizationURL.

Result Set Columns


Name	Type	Description
OAuthAccessToken	String	The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthAccessTokenSecret	String	The authentication token secret returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken	String	A token that may be used to obtain a new access token.
ExpiresIn	String	The remaining lifetime on the access token.

GetOAuthAuthorizationURL

Obtains the OAuth authorization URL for authentication with various Xero services.

Input


Name	Type	Required	Description
CallbackURL	String	False	This field determines where the response is sent. This value is only necessary if you have registered your own app. This URL must have the same domain as the OAuth callback domain specified in the Xero Developer Portal.
Scope	String	False	The space-separated list of scopes you are requesting from Xero. By default all scopes are requested.
State	String	False	A state token to include in the authorization URL. Not included by default.

Result Set Columns


Name	Type	Description
URL	String	The URL to complete user authentication.
AuthToken	String	A token used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token.
AuthKey	String	A key used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token.
PKCEVerifier	String	A random value used as input for GetOAuthAccessToken in the PKCE flow.

ListAttachments

Retrieves a list of attachments that have been uploaded against a document in Xero.

Input


Name	Type	Required	Description
Table	String	True	The table containing the document to retrieve a list of attachments from. The allowed values are Invoices, Receipts, CreditNotes, BankTransactions, BankTransfers, Contacts, Accounts, ManualJournals.
ObjectId	String	False	The unique ID of the document to retrieve a list of attachments from.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Result Set Columns


Name	Type	Description
AttachmentId	String	The ID of the attachment.
FileName	String	The file name of the attachment.
URL	String	The URL of the attachment.
MimeType	String	The Internet media type of the attachment.
ContentLength	String	The size of the attachment in bytes.

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with various Xero services.

Input


Name	Type	Required	Description
OAuthRefreshToken	String	True	The refresh token returned from the original authorization code exchange.

Result Set Columns


Name	Type	Description
OAuthAccessToken	String	The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken	String	A token that may be used to obtain a new access token.
ExpiresIn	String	The remaining lifetime on the access token.

RevokeTenant

Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.

Input


Name	Type	Required	Description
TenantId	String	False	The ID of the tenant to revoke, as given in the Tenants view

Result Set Columns


Name	Type	Description
Success	String	Whether the connection was successfully revoked

SendInvoiceEmail

Sends an email to the contacts of a sales invoice

Input


Name	Type	Required	Description
InvoiceId	String	True	The InvoiceId of the invoice to mail
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Result Set Columns


Name	Type	Description
Success	String	A boolean indicating if the operation was successful.

SwitchTenant

Changes the tenant this connection fetches data from.

Input


Name	Type	Required	Description
TenantId	String	False	The ID of the tenant to attach to. If empty, the current tenant is not changed.

Result Set Columns


Name	Type	Description
ActiveTenant	String	The ID of the tenant that is currently active.

UploadAttachment

Obtains a list of attachments an item has in Xero.

Input


Name	Type	Required	Description
Table	String	True	The table containing the document with the attachment. The allowed values are Invoices, Receipts, CreditNotes, BankTransactions, BankTransfers, Contacts, Accounts, ManualJournals.
ObjectId	String	True	The unique ID of the item to obtain an attachment from.
FilePath	String	False	The location of the file to attach.
FileData	String	False	Base-64 encoded data for the file. Required if FilePath is not defined.
FileStream	String	False	Stream of input data. Required if Filename and FileData are not defined.
Filename	String	False	The name of the file to attach. Required if FilePath is not defined.
ContentType	String	False	The type of file to attach.
IncludeOnline	String	False	Sets an attachment to be included with the invoice when viewed online. Set false by default.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Result Set Columns


Name	Type	Description
Success	String	A boolean indicating if the operation was successful.
AttachmentId	String	The ID of the attachment.
FileName	String	The file name of the attachment.
URL	String	The URL of the attachment.
MimeType	String	The Internet media type of the attachment.
ContentLength	String	The size of the attachment in bytes.
IncludeOnline	String	A boolean indicating if IncludeOnline was set to true.

Australian Payroll Data Model

The Jitterbit Connector for Xero models the Xero Australian Payroll API as relational tables, Views, and stored procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

However, note that deletes are not supported by the Australian Payroll API.

Tables

Tables describes the available tables.

Views

Views are tables that cannot be modified. Typically, data that are read-only and cannot be updated are shown as views.

Stored Procedures

NOTE: Stored procedures are not currently supported. See the above note for details.

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

Tables

The connector models the data in Xero into a list of tables that can be queried using standard SQL statements.

Jitterbit Connector for Xero Tables


Name	Description
Employees	Create, update, and query employees for a Xero organisation.
LeaveApplications	Create, update, and query Leave Applications for a Xero organisation.
PayItemsDeductions	Create, update, and query PayItems for a Xero organisation.
PayItemsEarnings	Create, update, and query PayItems for a Xero organisation.
PayItemsLeave	Create, update, and query PayItems for a Xero organisation.
PayItemsReimbursements	Create, update, and query PayItems for a Xero organisation.
PayrollCalendars	Create, update, and query Payroll Calendars for a Xero organisation.
PayRuns	Create, update, and query payruns for a Xero organisation.
SuperFunds	Retrieve, add and update Payroll Super Funds in a Xero organisation.
Timesheets	Create, update, and query Timesheets for a Xero organisation.

Employees

Create, update, and query employees for a Xero organisation.

Columns


Name	Type	ReadOnly	Filterable	Description
EmployeeID [KEY]	String	True		The unique identifier of the employee. This field is assigned by Xero.
Status	String	False		The status of the employee. Valid values are ACTIVE and TERMINATED.
Title	String	False		The title of the employee.
FirstName	String	False		The first name of the employee.
MiddleNames	String	False		The middle names of the employee.
LastName	String	False		The last name of the employee.
DateOfBirth	Date	False		The employee's birthday.
JobTitle	String	False		The job title of the employee.
Gender	String	False		The gender of the employee.
Phone	String	False		The employee's home phone number.
Mobile	String	False		The employee's mobile phone number.
Email	String	False		The employee's email address.
StartDate	Date	False		The employee's hire date.
TerminationDate	Date	False		The employee's termination date.
TerminationReason	String	False		The reasion for the eompleeoy's termination, as a one-letter code.
OrdinaryEarningsRateID	String	False		A reference to the employee's pay schedule
PayrollCalendarID	String	False		A reference to the employee's pay schedule
IsAuthorisedToApproveLeave	Boolean	False		Whether the employee is authorised to approve timeoff.
IsAuthorisedToApproveTimesheets	Boolean	False		Whether the employee is authorised to approve timesheets.
EmployeeGroupName	String	False		The name of the tracking category the employee belongs to, if one is assigned.
UpdatedDateUTC	Datetime	True		Timestamp of the last change to the employee record.
HomeAddress_AddressLine1	String	False		Address Line 1 for employee home address.
HomeAddress_AddressLine2	String	False		Address Line 2 for employee home address.
HomeAddress_City	String	False		Suburb for employee home address.
HomeAddress_Region	String	False		State abbreviation for employee home address.
HomeAddress_PostalCode	String	False		PostalCode for employee home address.
HomeAddress_Country	String	False		Country of HomeAddress.
TaxDeclaration_EmploymentBasis	String	False		One of FULLTIME, PARTTIME, CASUAL, LABOURHIRE or SUPERINCOMESTREAM
TaxDeclaration_TFNExemptionType	String	False		If the employee has no TFN, this may be one of NOTQUOTED, PENDING, PENSIONER or UNDER18
TaxDeclaration_TaxFileNumber	String	False		The last three digits of the employee's TFN
TaxDeclaration_AustralianResidentForTaxPurposes	Boolean	False		Whether the employee is an Australian resident for tax purposes
TaxDeclaration_ResidencyStatus	String	False		One of AUSTRALIANRESIDENT, FOREIGNRESIDENT or WORKINGHOLIDAYMAKER
TaxDeclaration_TaxFreeThresholdClaimed	Boolean	False		Whether the employee claims to make below the tax free threshold
TaxDeclaration_TaxOffsetEstimatedAmount	Decimal	False		Any other tax offsets the employee has claimed
TaxDeclaration_HasHELPDebt	Boolean	False		Whether the employee has HECS or HELP debt
TaxDeclaration_HasSFSSDebt	Boolean	False		Whether the employee has financial supplement debt
TaxDeclaration_HasTradeSupportLoanDebt	Boolean	False		Whether the employee has a trade support loan
TaxDeclaration_UpwardVariationTaxWithholdingAmount	Decimal	False		The extra withholding the employee has requested, in percent
TaxDeclaration_EligibleToReceiveLeaveLoading	Boolean	False		If the employee is eligible to receive extra earnings when they take leave
TaxDeclaration_ApprovedWithholdingVariationPercentage	Decimal	False		The extra withholding the employee has been granted, in percent
BankAccounts	String	True		The Bank accounts for the employee in XML format.
PayTemplate	String	True		The pay template for the employee in XML format.
OpeningBalances	String	True		The opening balances for the employee in XML format.
LeaveBalances	String	True		The leave balances for the empoloyee in XML format.
SuperMemberships	String	True		The Super memberships for the employee in XML format.
TenantId	String	False		The ID of the tenant to query instead of the connection tenant

LeaveApplications

Create, update, and query Leave Applications for a Xero organisation.

Table Specific Information

The Timesheets table allows you to SELECT and INSERT leave applications for a Xero organization. The ID column is generated by the connector; the value of this field combines the index of the line item with the unique, Xero-generated LeaveApplicationID.

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

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new leave application, the EmployeeID, LeaveTypeID, Title, StartDate and EndDate are required. In addition leave periods may also be provided.

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 Line columns with the LeavePeriods_LeavePeriod_ prefix removed.

INSERT INTO LeaveApplications(EmployeeID, LeaveTypeID, Title, StartDate, EndDate, LeavePeriodAggregate) VALUES (
  'XXXXX-XXXXX-XXXXX-XXXXX',
  'YYYYY-YYYYY-YYYYY-YYYYY',
  'Visit relatives',
  '2021-01-01',
  '2021-01-07',
  '<LeavePeriod><NumberOfUnits>8</NumberOfUnits></LeavePeriod> <LeavePeriod><NumberOfUnits>32</NumberOfUnits></LeavePeriod>'
)

You can insert a new period on an existing object by specifying the Xero-generated identifier of the existing record. For example, to add a period to an existing leave application:
```
INSERT INTO LeaveApplications(LeaveApplicationID, LeavePeriods_LeavePeriod_NumberOfUnits) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'YYYYY-YYYYY-YYYYY-YYYYY', 32)
```

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.

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	String	True		The leave period line-item combined with the unique Xero identifier of the leave application.
LeaveApplicationID	String	True		The unique identifier of the Leave Application. This field is assigned by Xero.
EmployeeID	String	False		The Xero identifier for Payroll Employee.
LeaveTypeID	String	False		The Xero identifier for Leave Type.
Title	String	False		The title of the leave (max length = 50).
StartDate	String	False		Start date of the leave (YYYY-MM-DD).
EndDate	String	False		End date of the leave (YYYY-MM-DD).
Description	String	False		The Description of the Leave (max length = 200).
LeavePeriodAggregate	String	False		Used to define LeavePeriod rows using XML values. Should be provided on INSERT only.
LeavePeriods_LeavePeriod_PayPeriodStartDate	String	False		The start of the payperiod the leave is being drawn from
LeavePeriods_LeavePeriod_PayPeriodEndDate	String	False		The end of the payperiod the leave is being drawn from
LeavePeriods_LeavePeriod_LeavePeriodStatus	String	False		Either SCHEDULED or PROCESSED
LeavePeriods_LeavePeriod_NumberOfUnits	String	False		Amount of leave consumed from this payperiod
TenantId	String	False		The ID of the tenant to query instead of the connection tenant

PayItemsDeductions

Create, update, and query PayItems for a Xero organisation.

Columns


Name	Type	ReadOnly	Filterable	Description
DeductionTypeID [KEY]	String	True		The unique identifier of the PayItem. This field is assigned by Xero.
Name	String	False		Name of the deduction type.
DeductionCategory	String	False		Category of the deduction.
AccountCode	String	False		Customer defined alpha numeric account code.
ReducesTax	String	False		Indicates that this is a pre-tax deduction.
ReducesSuper	String	False		Indicates that this reduces your superannuation guarantee contribution liability.
UpdatedDateUTC	Datetime	True		Timestamp of the last change to the PayItem record.
TenantId	String	False		The ID of the tenant to query instead of the connection tenant

PayItemsEarnings

Create, update, and query PayItems for a Xero organisation.

Columns


Name	Type	ReadOnly	Filterable	Description
EarningsRateID [KEY]	String	True		The unique identifier of the PayItem. This field is assigned by Xero.
Name	String	False		Name of the earnings rate.
DisplayName	String	False		Display name of the earnings rate, as it will appear on payslips.
AccountCode	String	False		Customer defined alpha numeric account code.
TypeOfUnits	String	False		Type of units used to record earnings.
IsExemptFromTax	String	False		You should only set this value if you are sure that a payment is exempt from PAYG withholding.
IsExemptFromSuper	String	False		See the ATO website for details of which payments are exempt from SGC.
IsReportableAsW1	Bool	False		Whether the earnings rate is subject to W1 witholding.
EarningsType	String	False		The type of Pay Items earning. Valid values are FIXED, ORDINARYTIMEEARNINGS, OVERATIMEEARNINGS, ALLOWANCE and LUMPSUMD.
AllowanceType	String	False		When EarningsType is ALLOWANCE, this reports what the allowance is paid for.
AllowanceCategory	String	False		Determines the kind of allowance when AllowanceType is OTHER.
RateType	String	False		The rate type of the earning. Valid values are FIXEDAMOUNT, MULTIPLE, RATEPERUNIT
RatePerUnit	String	False		Only applicable if RateType is RATEPERUNIT.
Multiplier	Double	False		This is the multiplier used to calculate the rate per unit, based on the employee's ordinary earnings type. Only applicable if RateType is MULTIPLE.
AccrueLeave	String	False		Indicates that this earnings rate should accrue leave. Only applicable if RateType is MULTIPLE.
Amount	String	False		Option Amount for FIXEDAMOUNT RateType EarningsRate.
CurrentRecord	Bool	False		Wehther the earnings rate may still be used.
UpdatedDateUTC	Datetime	True		Timestamp of the last change to the PayItem record.
TenantId	String	False		The ID of the tenant to query instead of the connection tenant

PayItemsLeave

Create, update, and query PayItems for a Xero organisation.

Columns


Name	Type	ReadOnly	Filterable	Description
LeaveTypeID [KEY]	String	True		The unique identifier of the PayItem. This field is assigned by Xero.
Name	String	False		Name of the leave type.
TypeOfUnits	String	False		The type of units by which leave entitlements are normally tracked.
IsPaidLeave	String	False		Set this to indicate that an employee will be paid when taking this type of leave.
ShowOnPaySlip	String	False		Set this if you want a balance for this leave type to be shown on your employee's payslips.
NormalEntitlement	String	False		The number of units the employee is entitled to each year.
LeaveLoadingRate	String	False		Enter an amount here if your organisation pays an additional percentage on top of ordinary earnings when your employees take leave (typically 17.5%).
UpdatedDateUTC	String	False		The Date time this row was last updated.
TenantId	String	False		The ID of the tenant to query instead of the connection tenant

PayItemsReimbursements

Create, update, and query PayItems for a Xero organisation.

Columns


Name	Type	ReadOnly	Filterable	Description
ReimbursementTypeID [KEY]	String	True		The unique identifier of the PayItem. This field is assigned by Xero.
Name	String	False		Name of the reimbursement type.
AccountCode	String	False		Customer defined alpha numeric account code.
UpdatedDateUTC	Datetime	True		Timestamp of the last change to the PayItem record.
TenantId	String	False		The ID of the tenant to query instead of the connection tenant

PayrollCalendars

Create, update, and query Payroll Calendars for a Xero organisation.

Columns


Name	Type	ReadOnly	Filterable	Description
PayrollCalendarID [KEY]	String	True		The unique identifier of the PaySchedule. This field is assigned by Xero.
Name	String	False		The name of the Payroll Calendar.
CalendarType	String	False		The type of payroll calendar.
StartDate	String	False		The Start date of the upcoming pay period.
PaymentDate	String	False		The date on which employees will be paid for the upcomming pay period.
TenantId	String	False		The ID of the tenant to query instead of the connection tenant

PayRuns

Create, update, and query payruns for a Xero organisation.

Columns


Name	Type	ReadOnly	Filterable	Description
PayRunID [KEY]	String	True		The unique identifier of the payrun. This field is assigned by Xero.
PayrollCalendarID	String	False		The status of the payrun. Valid values are ACTIVE and DELETED.
PayRunPeriodStartDate	String	False		Period Start Date for the PayRun.
PayRunPeriodEndDate	String	False		Period End Date for the PayRun.
PayRunStatus	String	False		The status of the payrun.
PaymentDate	String	False		Payment Date for the PayRun.
PayslipMessage	String	False		Payslip message for the PayRun.
Wages	Decimal	False		Total Wages for the PayRun.
Deductions	Decimal	False		Total Deduction for the PayRun.
Tax	Decimal	False		Total Tax for the PayRun.
Super	Decimal	False		Total Super for the PayRun.
Reimbursement	Decimal	False		Total Reimbursement for the PayRun.
NetPay	Decimal	False		Total NetPay for the PayRun.
UpdatedDateUTC	Datetime	True		Timestamp of the last change to the payrun record.
Payslip	String	False		The payslip for this pay run in XML format.
TenantId	String	False		The ID of the tenant to query instead of the connection tenant

SuperFunds

Retrieve, add and update Payroll Super Funds in a Xero organisation.

Columns


Name	Type	ReadOnly	Filterable	Description
SuperFundID [KEY]	String	True		Xero Identifier.
Type	String	True		REGULATED, or SMSF.
Name	String	False		Name of the Super fund.
ABN	String	False		ABN of the Super Fund.
USI	String	False		USI of the Regulated Super Fund.
BSB	String	False		BSB of the Self Managed Super Fund.
AccountNumber	String	False		The account number for the self managed super fund.
AccountName	String	False		The account name for the self managed super fund.
ElectronicServiceAddress	String	False		The electronic service address for the self managed super fund.
EmployerNumber	String	False		Some funds assign a unique number to each employer.
SPIN	String	False		The SPIN of the Regulated SuperFund (deprecated).
UpdatedDateUTC	String	False		The date of the last update to this row
TenantId	String	False		The ID of the tenant to query instead of the connection tenant

Timesheets

Create, update, and query Timesheets for a Xero organisation.

Table Specific Information

The Timesheets table allows you to SELECT, INSERT and UPDATE timesheet lines for a Xero organization. The ID column is generated by the connector; the value of this field combines the index of the line item with the unique, Xero-generated TimesheetID.

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

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new timesheet, the EmployeeID, StartDate and EndDate fields are required.

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 Line columns with the Line_ prefix removed, except when providing numbers of units. See the Xero documentation for the Timesheets API endpoint for more details.

INSERT INTO Timesheets(EmployeeID, StartDate, EndDate, TimesheetLineAggregate) VALUES (
  'XXXXX-XXXXX-XXXXX-XXXXX',
  '2021-01-01',
  '2021-01-07',
  '<TimesheetLine><EarningsRateID>YYYYY-YYYYY-YYYYY-YYYYY</EarningsRateID><NumberOfUnits><NumberOfUnit>8</NumberOfUnit><NumberOfUnit>7</NumberOfUnit><NumberOfUnit>8</NumberOfUnit><NumberOfUnit>6</NumberOfUnit><NumberOfUnit>9</NumberOfUnit></TimesheetLine> <TimesheetLine><EarningsRateID>ZZZZZ-ZZZZZ-ZZZZZ-ZZZZZ</EarningsRateID><NumberOfUnits><NumberOfUnit>7</NumberOfUnit><NumberOfUnit>7</NumberOfUnit><NumberOfUnit>7</NumberOfUnit><NumberOfUnit>6</NumberOfUnit><NumberOfUnit>9</NumberOfUnit></TimesheetLine>'
)

You can insert a new line item on an existing object by specifying the Xero-generated identifier of the existing record. For example, to add a line item to an existing timesheet:

INSERT INTO Timesheets(TimesheetID, Line_EarningsRateID, Line_NumberOfUnits_NumberOfUnit1, Line_NumberOfUnits_NumberOfUnit2, ...) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'YYYYY-YYYYY-YYYYY-YYYYY', 8, 7, ...)

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.

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	String	True		The timesheet line-item combined with the unique Xero identifier of the timesheet.
TimesheetID	String	True		The unique identifier of the Timesheet. This field is assigned by Xero.
EmployeeID	String	False		The Xero identifier for an employee.
StartDate	Date	False		Period start date.
EndDate	Date	False		Period end date.
Status	String	False		The status code for the timesheet. The valid values are DRAFT, PROCESSED, and APPROVED.
Hours	Double	False		Timesheet total hours.
Line_EarningsRateId	String	False		The Xero identifier for an Earnings Type.
Line_TrackingItemId	String	False		The Xero identifier for a Tracking Category TrackingOptionID. The TrackingOptionID must belong to the TrackingCategory selected as TimesheetCategories under Payroll Settings.
Line_WorkLocationId	String	False		The Xero identifier for a Work Location.
Line_NumberOfUnits_NumberOfUnit1	String	False		Number of units of a Timesheet line.
Line_NumberOfUnits_NumberOfUnit2	String	False		Number of units of a Timesheet line.
Line_NumberOfUnits_NumberOfUnit3	String	False		Number of units of a Timesheet line.
Line_NumberOfUnits_NumberOfUnit4	String	False		Number of units of a Timesheet line.
Line_NumberOfUnits_NumberOfUnit5	String	False		Number of units of a Timesheet line.
Line_NumberOfUnits_NumberOfUnit6	String	False		Number of units of a Timesheet line.
Line_NumberOfUnits_NumberOfUnit7	String	False		Number of units of a Timesheet line.
Line_UpdatedDateUTC	Datetime	True		Timestamp of the last change to the Timesheet Line record.
TimesheetLineAggregate	String	False		Used to define TimesheetLine rows using XML values. Should be provided on INSERT only.
TenantId	String	False		The ID of the tenant to query instead of the connection tenant

Views

Jitterbit Connector for Xero Views


Name	Description
EmployeeBankAccounts	Retrieve the bank accounts for an Employee in a Xero organisation.
EmployeePayTemplateDeductions	Retrieve the pay template deduction lines for an Employee in a Xero organisation.
EmployeePayTemplateEarnings	Retrieve the pay template earnings lines for an Employee in a Xero organisation.
EmployeePayTemplateLeave	Retrieve the pay template leave lines for an Employee in a Xero organisation.
EmployeePayTemplateReimbursements	Retrieve the pay template reimbursement lines for an Employee in a Xero organisation.
EmployeePayTemplateSupers	Retrieve the pay template super lines for an Employee in a Xero organisation.
LeaveBalances	Retrieve the Leave Balance for an Employee in a Xero organisation.
PaySlipDeductions	Query payslip deduction line-items for a Xero organisation.
PaySlipEarnings	Query payslip earning line-items for a Xero organisation.
PaySlipLeaveAccrual	Query payslip leave accrual line-items for a Xero organisation.
PaySlipLeaveEarnings	Query payslip leave earnings line-items for a Xero organisation.
PaySlipReimbursements	Query payslip reimbursements line-items for a Xero organisation.
PaySlips	Create, update, and query payslips for a Xero organisation.
PaySlipSuperannuations	Query payslip superannuation line-items for a Xero organisation.
PaySlipTaxes	Query payslip tax line-items for a Xero organisation.
PaySlipTimesheetEarnings	Query payslip timesheet-earnings line-items for a Xero organisation.
Tenants	Query the list of organisations connected to your Xero account.

EmployeeBankAccounts

Retrieve the bank accounts for an Employee in a Xero organisation.

Columns


Name	Type	Filterable	Description
EmployeeID	String		The ID of the Employee. This field is assigned by Xero.
StatementText	String		The text that will appear on the employee's bank statement
AccountName	String		The name of the account
BSB	String		The BSB number of the account
AccountNumber	String		The account number
Remainder	Boolean		If the remaining part of the employee's salary should be transferred to this account
Amount	Decimal		A fixed amount of the employee's salary which is transferred to this account
TenantId	String		The ID of the tenant to query instead of the connection tenant

EmployeePayTemplateDeductions

Retrieve the pay template deduction lines for an Employee in a Xero organisation.

Columns


Name	Type	Filterable	Description
EmployeeID	String		The ID of the Employee. This field is assigned by Xero.
DeductionTypeID	String		The ID of the Deductions Rate used for this line item
CalculationType	String		One of FIXEDAMOUNT, PRETAX or POSTTAX
Percentage	Decimal		The deduction rate, for PRETAX or POSTTAX deductions
Amount	Decimal		The deduction amount, for FIXEDAMOUNT deductions
TenantId	String		The ID of the tenant to query instead of the connection tenant

EmployeePayTemplateEarnings

Retrieve the pay template earnings lines for an Employee in a Xero organisation.

Columns


Name	Type	Filterable	Description
EmployeeID	String		The ID of the Employee. This field is assigned by Xero.
EarningsRateID	String		The ID of the Earnings Rate used for this line item
CalculationType	String		One of USEEARNINGSRATE, ENTEREARNINGSRATE or ANNUALSALARY
NumberOfUnitsPerWeek	Int		How many hours the employee works per week. Only used for ANNUALSALARY.
AnnualSalary	Decimal		The annual salary of the employee
RatePerUnit	Decimal		The rate per unit of the earnings line
NormalNumberOfUnits	Decimal		The units of time in the earnings line
TenantId	String		The ID of the tenant to query instead of the connection tenant

EmployeePayTemplateLeave

Retrieve the pay template leave lines for an Employee in a Xero organisation.

Columns


Name	Type	Filterable	Description
EmployeeID	String		The ID of the Employee. This field is assigned by Xero.
LeaveTypeID	String		The ID of the leave type applied to this line
CalculationType	String		One of FIXEDAMOUNTEACHPERIOD, ENTIRERATEINPAYTEMPLATE or BASEDONORDINARYEARNINGS
AnnualNumberOfUnits	Decimal		The hours of leave accrued each year
FullTimeNumberOfUnitsPerPeriod	Decimal		The hours of leave accrued per pay period
NumberOfUnits	Decimal		A fixed amount of leave
EntitlementFinalPayPayoutType	String		One of NOTPAIDOUT or PAIDOUT
EmploymentTerminationPaymentType	String		One of O or R, only valid if EntitlementFinalPayPaoutType is PAIDOUT
IncludeSuperannuationGuaranteeContribution	Boolean		Whether ETP leave earnings are subject to SGC
TenantId	String		The ID of the tenant to query instead of the connection tenant

EmployeePayTemplateReimbursements

Retrieve the pay template reimbursement lines for an Employee in a Xero organisation.

Columns


Name	Type	Filterable	Description
EmployeeID	String		The ID of the Employee. This field is assigned by Xero.
ReimbursementTypeID	String		The ID of the reimbursement type applied to this line
Description	String		The description of the reimbursement line
Amount	Decimal		The amount of the reimbursement line
TenantId	String		The ID of the tenant to query instead of the connection tenant

EmployeePayTemplateSupers

Retrieve the pay template super lines for an Employee in a Xero organisation.

Columns


Name	Type	Filterable	Description
EmployeeID	String		The ID of the Employee. This field is assigned by Xero.
SuperMembershipID	String		The ID of the superannuation fund this line contributes to
ContributionType	String		One of SGC, SALARYSACRIFICE, EMPLOYERADDITIONAL or EMPLOYEE
CalculationType	String		One of FIXEDAMOUNT, PERCENTAGEOFEARNINGS or STATUTORY
ExpenseAccountCode	String		The code for the expense account used by this line
LiabilityAccountCode	String		The code for the expense account used by this line
MinimumMonthlyEarnings	Decimal		The minimum the employee must make in a month for this line to take effect
Percentage	Decimal		The percentage of the super line
TenantId	String		The ID of the tenant to query instead of the connection tenant

LeaveBalances

Retrieve the Leave Balance for an Employee in a Xero organisation.

Columns


Name	Type	Filterable	Description
EmployeeID [KEY]	String		ID of the employee
LeaveTypeID [KEY]	String		ID of the leave type
LeaveName	String		The name of the leave type
NumberOfUnits	Double		The balance of the leave available
TypeOfUnits	String		The type of units
TenantId	String		The ID of the tenant to query instead of the connection tenant

PaySlipDeductions

Query payslip deduction line-items for a Xero organisation.

Table Specific Information

Queries

Equal operator

The equal operator is supported on the server side:

SELECT * FROM PaySlipDeductions WHERE PayslipID ='218db321-b72e-4aa1-9067-477d63705f95'

IN operator

The IN operator is supported on the client side:

SELECT * FROM PaySlipDeductions WHERE PayslipID IN ('1843c739-aa8b-4d58-956a-3e097708cd84','218db321-b72e-4aa1-9067-477d63705f95')

Columns


Name	Type	Filterable	Description
Id [KEY]	String		The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID	String		The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID	String		The unique identifier of the payslip's employee.
DeductionLine_DeductionTypeId	String		The unique identifier of the line-item's deduction type
DeductionLine_CalculationType	String		How the deduction is applied to earnings, either PRETAX, POSTTAX or FIXEDAMOUNT
DeductionLine_Amount	Decimal		The total amount of the deduction
DeductionLine_Percentage	Double		The percentage of income this deduction applies to
TenantId	String		The ID of the tenant to query instead of the connection tenant

PaySlipEarnings

Query payslip earning line-items for a Xero organisation.

Table Specific Information

Queries

Equal operator

The equal operator is supported on the server side:

SELECT * FROM PaySlipEarnings WHERE PayslipID ='218db321-b72e-4aa1-9067-477d63705f95'

IN operator

The IN operator is supported on the client side:

SELECT * FROM PaySlipEarnings WHERE PayslipID IN ('1843c739-aa8b-4d58-956a-3e097708cd84','218db321-b72e-4aa1-9067-477d63705f95')

Columns


Name	Type	Filterable	Description
Id [KEY]	String		The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID	String		The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID	String		The unique identifier of the payslip's employee.
EarningsLine_EarningsRateId	String		The unique identifier of the line-item's earnings rate
EarningsLine_RatePerUnit	Decimal		The line-item's wage per time unit (e.g. hours)
EarningsLine_NumberOfUnits	Double		The number of time units in this line-item (e.g. hours)
EarningsLine_FixedAmount	Decimal		The total earnings in this line-item. Only appears if the rate-type is FIXED.
EarningsLine_LumpSumETaxYear	String		The tax year that applies to these earnings. Only appears if the earnings type is LUMPSUME.
TenantId	String		The ID of the tenant to query instead of the connection tenant

PaySlipLeaveAccrual

Query payslip leave accrual line-items for a Xero organisation.

Table Specific Information

Queries

Equal operator

The equal operator is supported on the server side:

SELECT * FROM PaySlipLeaveAccrual WHERE PayslipID ='218db321-b72e-4aa1-9067-477d63705f95'

IN operator

The IN operator is supported on the client side:

SELECT * FROM PaySlipLeaveAccrual WHERE PayslipID IN ('1843c739-aa8b-4d58-956a-3e097708cd84','218db321-b72e-4aa1-9067-477d63705f95')

Columns


Name	Type	Filterable	Description
Id [KEY]	String		The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID	String		The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID	String		The unique identifier of the payslip's employee.
LeaveAccrualLine_LeaveTypeId	String		The unique identifier of the line-item's leave type
LeaveAccrualLine_NumberOfUnits	Double		How much leave time was added
LeaveAccrualLine_AutoCalculate	String		Whether or not to automatically calculate leave
TenantId	String		The ID of the tenant to query instead of the connection tenant

PaySlipLeaveEarnings

Query payslip leave earnings line-items for a Xero organisation.

Columns


Name	Type	Filterable	Description
Id [KEY]	String		The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID	String		The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID	String		The unique identifier of the payslip's employee.
LeaveEarningsLine_EarningsRateId	String		The unique identifier of the line-item's earnings rate
LeaveEarningsLine_RatePerUnit	Decimal		The line-item's hourly wage
LeaveEarningsLine_NumberOfUnits	Double		The number of hours in this line-item
TenantId	String		The ID of the tenant to query instead of the connection tenant

PaySlipReimbursements

Query payslip reimbursements line-items for a Xero organisation.

Table Specific Information

Queries

Equal operator

The equal operator is supported on the server side:

SELECT * FROM PaySlipReimbursements WHERE PayslipID ='218db321-b72e-4aa1-9067-477d63705f95'

IN operator

The IN operator is supported on the client side:

SELECT * FROM PaySlipReembursements WHERE PayslipID IN ('1843c739-aa8b-4d58-956a-3e097708cd84','218db321-b72e-4aa1-9067-477d63705f95')

Columns


Name	Type	Filterable	Description
Id [KEY]	String		The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID	String		The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID	String		The unique identifier of the payslip's employee.
ReimbursementLine_ReimbursementTypeId	String		The unique identifier of the line-item's reimbursement type
ReimbursementLine_Description	String		A description of the reimbursement
ReimbursementLine_ExpenseAccount	String		The account the reimbursement is paid from
ReimbursementLine_Amount	Decimal		The amount of the reimbursement
TenantId	String		The ID of the tenant to query instead of the connection tenant

PaySlips

Create, update, and query payslips for a Xero organisation.

Columns


Name	Type	Filterable	Description
PayRunID	String		The ID of the payrun that contains the payslips
PayslipID [KEY]	String		The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID	String		The unique identifier of the payslip's employee.
FirstName	String		The employee's first name.
LastName	String		The employee's first name.
EmployeeGroup	String		The name of the group the employee belongs to.
LastEdited	Datetime		When this payslip was last changed.
Wages	Decimal		The total wages included in the payslip.
Deductions	Decimal		The total deductions included in the payslip.
NetPay	Decimal		The total net pay included in the paysilp.
Tax	Decimal		The total tax paid included in the payslip.
Super	Decimal		The total superannuation included in the paysilp.
Reimbursements	Decimal		The total reimbursement included in the payslip.
TenantId	String		The ID of the tenant to query instead of the connection tenant

PaySlipSuperannuations

Query payslip superannuation line-items for a Xero organisation.

Table Specific Information

Queries

Equal operator

The equal operator is supported on the server side:

SELECT * FROM PaySlipTaxeSuperannuations WHERE PayslipID ='218db321-b72e-4aa1-9067-477d63705f95'

IN operator

The IN operator is supported on the client side:

SELECT * FROM PaySlipSuperAnnuations WHERE PayslipID IN ('1843c739-aa8b-4d58-956a-3e097708cd84','218db321-b72e-4aa1-9067-477d63705f95')

Columns


Name	Type	Filterable	Description
Id [KEY]	String		The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID	String		The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID	String		The unique identifier of the payslip's employee.
SuperannuationLine_SuperMembershipId	String		The unique identifier of the line-item's super fund.
SuperannuationLine_ContributionType	String		The super contribution type
SuperannuationLine_CalculationType	String		The super calculation type
SuperannuationLine_MinimumMonthlyEarnings	Decimal		The super minimum monthly earnings
SuperannuationLine_ExpenseAccountCode	String		The super expense account code
SuperannuationLine_LiabilityAccountCode	String		The super liability account code
SuperannuationLine_PaymentDateForThisPeriod	Date		The super payment date for this period
SuperannuationLine_Percentage	Double		The super percentage
SuperannuationLine_Amount	Decimal		The super amount
TenantId	String		The ID of the tenant to query instead of the connection tenant

PaySlipTaxes

Query payslip tax line-items for a Xero organisation.

Table Specific Information

Queries

Equal operator

The equal operator is supported on the server side:

SELECT * FROM PaySlipTaxes WHERE PayslipID ='218db321-b72e-4aa1-9067-477d63705f95'

IN operator

The IN operator is supported on the client side:

SELECT * FROM PaySlipTaxes WHERE PayslipID IN ('1843c739-aa8b-4d58-956a-3e097708cd84','218db321-b72e-4aa1-9067-477d63705f95')

Columns


Name	Type	Filterable	Description
Id [KEY]	String		The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID	String		The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID	String		The unique identifier of the payslip's employee.
TaxLine_TaxTypeName	String		The name of the line-item's tax type
TaxLine_Description	String		The description of the tax
TaxLine_Amount	Decimal		The tax's amount
TaxLine_LiabilityAccount	String		The liability account used to pay the tax
TenantId	String		The ID of the tenant to query instead of the connection tenant

PaySlipTimesheetEarnings

Query payslip timesheet-earnings line-items for a Xero organisation.

Table Specific Information

Queries

Equal operator

The equal operator is supported on the server side:

SELECT * FROM PaySlipTimesheetEarnings WHERE PayslipID ='218db321-b72e-4aa1-9067-477d63705f95'

IN operator

The IN operator is supported on the client side:

SELECT * FROM PaySlipTimesheetEarnings WHERE PayslipID IN ('1843c739-aa8b-4d58-956a-3e097708cd84','218db321-b72e-4aa1-9067-477d63705f95')

Columns


Name	Type	Filterable	Description
Id [KEY]	String		The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID	String		The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID	String		The unique identifier of the payslip's employee.
TimesheetEarningsLine_EarningsRateId	String		The unique identifier of the line-item's earnings rate
TimesheetEarningsLine_RatePerUnit	Decimal		The line-item's rate per time unit (e.g. hours)
TimesheetEarningsLine_NumberOfUnits	Decimal		The number of time units (e.g. hours)
TenantId	String		The ID of the tenant to query instead of the connection tenant

Tenants

Query the list of organisations connected to your Xero account.

Columns


Name	Type	Filterable	Description
TenantId [KEY]	String		A unique identifier for the tenant.
Name	String		The name of the organisation or practice
Type	String		Whether the tenant is an organisation or a practice
IsActive	Boolean		Whether the connection is currently using this tenant

Stored Procedures

NOTE: Stored procedures are not currently supported. See the above note for details.

Jitterbit Connector for Xero Stored Procedures


Name	Description
GetLimitInfo	Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant.
GetOAuthAccessToken	Obtains the OAuth access token to be used for authentication with Xero.
GetOAuthAuthorizationURL	Obtains the OAuth authorization URL for authentication with various Xero services.
RefreshOAuthAccessToken	Refreshes the OAuth access token used for authentication with various Xero services.
RevokeTenant	Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.
SwitchTenant	Changes the tenant this connection fetches data from.

GetLimitInfo

Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant.

Procedure-Specific Information

SELECT * FROM PayRuns LIMIT 1

Result Set Columns


Name	Type	Description
TenantId	String	ID of the tenant that the limit applies to. The output contains a row for each tenant queried during the connection. Usually there is only one.
AppRemaining	Integer	The number of API calls the current OAuth app has this minute (max 10000). This applies across all tenants.
MinuteRemaining	Integer	The number of API calls the current OAuth app has this minute (max 60). This applies only to this tenant.
DayRemaining	Integer	The number of API calls the current OAuth app has today (max 5000). This applies only to this tenant.
RetryAfter	Integer	The suggested retry time, if one of the above limits was 0 on the most recent API call to the tenant.
LastModified	Datetime	When this information was last updated. The values in this table are only refreshed when performing API calls, so this may be older than the current time.

GetOAuthAccessToken

Obtains the OAuth access token to be used for authentication with Xero.

Input


Name	Type	Required	Description
AuthMode	String	True	The type of authentication mode to use. The allowed values are APP, WEB. The default value is WEB.
AuthToken	String	False	The authentication token returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
AuthKey	String	False	The AuthKey returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
CallbackURL	String	False	This field determines where the response is sent. The value of this parameter must exactly match one of the values registered in the Developer Portal, including the HTTP or HTTPS schemes, capitalization, and trailing forward slash ('/').
Verifier	String	False	The verifier token returned by Xero after using the URL obtained with GetOAuthAuthorizationURL. Required for only the Web AuthMode.
PKCEVerifier	String	False	The PKCEVerifier returned by GetOAuthAuthorizationURL.

Result Set Columns


Name	Type	Description
OAuthAccessToken	String	The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthAccessTokenSecret	String	The authentication token secret returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken	String	A token that may be used to obtain a new access token.
ExpiresIn	String	The remaining lifetime on the access token.

GetOAuthAuthorizationURL

Obtains the OAuth authorization URL for authentication with various Xero services.

Input


Name	Type	Required	Description
CallbackURL	String	False	This field determines where the response is sent. This value is only necessary if you have registered your own app. This URL must have the same domain as the OAuth callback domain specified in the Xero Developer Portal.
Scope	String	False	The space-separated list of scopes you are requesting from Xero. By default all scopes are requested.
State	String	False	A state token to include in the authorization URL. Not included by default.

Result Set Columns


Name	Type	Description
URL	String	The URL to complete user authentication.
AuthToken	String	A token used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token.
AuthKey	String	A key used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token.
PKCEVerifier	String	A random value used as input for GetOAuthAccessToken in the PKCE flow.

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with various Xero services.

Input


Name	Type	Required	Description
OAuthRefreshToken	String	True	The refresh token returned from the original authorization code exchange.

Result Set Columns


Name	Type	Description
OAuthAccessToken	String	The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken	String	A token that may be used to obtain a new access token.
ExpiresIn	String	The remaining lifetime on the access token.

RevokeTenant

Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.

Input


Name	Type	Required	Description
TenantId	String	False	The ID of the tenant to revoke, as given in the Tenants view

Result Set Columns


Name	Type	Description
Success	String	Whether the connection was successfully revoked

SwitchTenant

Changes the tenant this connection fetches data from.

Input


Name	Type	Required	Description
TenantId	String	False	The ID of the tenant to attach to. If empty, the current tenant is not changed.

Result Set Columns


Name	Type	Description
ActiveTenant	String	The ID of the tenant that is currently active.

Files Data Model

The Jitterbit Connector for Xero models the Xero Files API as relational tables and stored procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

Tables describes the available tables.

Stored Procedures

NOTE: Stored procedures are not currently supported. See the above note for details.

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

Tables

The connector models the data in Xero into a list of tables that can be queried using standard SQL statements.

Jitterbit Connector for Xero Tables


Name	Description
Associations	Create, query and delete Associations for a Xero organization.
Files	Query, update and delete Files for a Xero organization.
Folders	Create, query, update and delete Folders for a Xero organization.

Associations

Create, query and delete Associations for a Xero organization.

Table-Specific Information

To create an Association, everything but the ID and ObjectType must be provided:

INSERT INTO Associations(FileId, ObjectId, ObjectGroup)
VALUES ('2bf7b85c-7ed8-47b4-8559-d83f8f5f9b9c', '4819be2c-b3b7-43a2-80fe-ef479ad25351', 'Invoice')

Associations cannot be changed once they have been inserted. If you need to update an Association you must delete it and recreate it.

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	False	A unique combination of the file and object being associated
FileId	String	False	The FileId of the file that is being associated
ObjectId	String	False	The identifier of the object that is being associated to
ObjectGroup	String	False	The Accounting entity to which the object should be attached
ObjectType	String	True	A more specific category that the object belongs to
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Files

Query, update and delete Files for a Xero organization.

Table-Specific Information

If you need to interact with file contents (either uploading or downloading them), please see the Files stored procedures.

Columns


Name	Type	ReadOnly	Description
FileId [KEY]	String	True	The unique Xero-generated ID of the file
Name	String	False	The name of the file
FolderId	String	False	The FolderId of the folder containing the file
MimeType	String	True	What type of data the file contains
Size	Int	True	The size of the file in bytes
User_Id	String	True	The unique ID of the user who uploaded the file
User_Name	String	True	The username of the user who uploaded the file
User_FirstName	String	True	The first name of the user who uploaded the file
User_LastName	String	True	The last name of the user who uploaded the file
User_FullName	String	True	The full name of the user who uploaded the file
Created	Datetime	True	When the file was first uploaded
Updated	Datetime	True	When the file metadata was last changed
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Folders

Create, query, update and delete Folders for a Xero organization.

Table-Specific Information

A Name has to be provided when creating a Folder:

INSERT INTO Folders(Name) VALUE ('Legal Documents')

Columns


Name	Type	ReadOnly	Description
FolderId [KEY]	String	True	The unique Xero-generated ID of the folder
Name	String	False	The name of the folder
FileCount	Integer	True	How many files exist in the folder
IsInbox	Boolean	True	Whether this folder is the inbox. The inbox is automatically created by Xero and cannot be changed or deleted.
Email	String	True	The email address that accepts files and uploads them to this folder. Only available for the Inbox.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Views

Jitterbit Connector for Xero Views


Name	Description
Tenants	Query the list of organizations connected to your Xero account.

Tenants

Query the list of organizations connected to your Xero account.

Columns


Name	Type	Description
TenantId [KEY]	String	A unique identifier for the tenant.
Name	String	The name of the organization or practice
Type	String	Whether the tenant is an organization or a practice
IsActive	Boolean	Whether the connection is currently using this tenant

Stored Procedures

NOTE: Stored procedures are not currently supported. See the above note for details.

Jitterbit Connector for Xero Stored Procedures


Name	Description
DownloadFile	Downloads a file from the Xero Files service
GetLimitInfo	Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant.
GetOAuthAccessToken	Obtains the OAuth access token to be used for authentication with Xero.
GetOAuthAuthorizationURL	Obtains the OAuth authorization URL for authentication with various Xero services.
RefreshOAuthAccessToken	Refreshes the OAuth access token used for authentication with various Xero services.
RevokeTenant	Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.
SwitchTenant	Changes the tenant this connection fetches data from.
UploadFile	Uploads a file to the Xero Files service

DownloadFile

Downloads a file from the Xero Files service

Procedure-Specific Information

There are two different ways to download a file. If you provide a DownloadPath, then the file is downloaded and its contents are stored at that location:

EXECUTE DownloadFile @FileID = '...', @DownloadPath = 'c:/my/xero/files/form.docx'

If you do not provide a DownloadPath, then the contents of the file are base64 encoded and returned in the FileData output:

EXECUTE DownloadFile @FileID = '...'

Input


Name	Type	Required	Description
FileId	String	True	The ID of the file to download
DownloadPath	String	False	Where to write the downloaded file. If not provided, the file is returned as an output.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Result Set Columns


Name	Type	Description
FileData	String	If the DownloadPath is not provided, this contains the base64 encoded content of the file.
Success	String	This parameter sets whether the operation was successful or not.

GetLimitInfo

Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant.

Procedure-Specific Information

SELECT * FROM Files LIMIT 1

Result Set Columns


Name	Type	Description
TenantId	String	ID of the tenant that the limit applies to. The output contains a row for each tenant queried during the connection. Usually there is only one.
AppRemaining	Integer	The number of API calls the current OAuth app has this minute (max 10000). This applies across all tenants.
MinuteRemaining	Integer	The number of API calls the current OAuth app has this minute (max 60). This applies only to this tenant.
DayRemaining	Integer	The number of API calls the current OAuth app has today (max 5000). This applies only to this tenant.
RetryAfter	Integer	The suggested retry time, if one of the above limits was 0 on the most recent API call to the tenant.
LastModified	Datetime	When this information was last updated. The values in this table are only refreshed when performing API calls, so this may be older than the current time.

GetOAuthAccessToken

Obtains the OAuth access token to be used for authentication with Xero.

Input


Name	Type	Required	Description
AuthMode	String	True	The type of authentication mode to use. The allowed values are APP, WEB. The default value is WEB.
AuthToken	String	False	The authentication token returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
AuthKey	String	False	The AuthKey returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
CallbackURL	String	False	This field determines where the response is sent. The value of this parameter must exactly match one of the values registered in the Developer Portal, including the HTTP or HTTPS schemes, capitalization, and trailing forward slash ('/').
Verifier	String	False	The verifier token returned by Xero after using the URL obtained with GetOAuthAuthorizationURL. Required for only the Web AuthMode.
PKCEVerifier	String	False	The PKCEVerifier returned by GetOAuthAuthorizationURL.

Result Set Columns


Name	Type	Description
OAuthAccessToken	String	The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthAccessTokenSecret	String	The authentication token secret returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken	String	A token that may be used to obtain a new access token.
ExpiresIn	String	The remaining lifetime on the access token.

GetOAuthAuthorizationURL

Obtains the OAuth authorization URL for authentication with various Xero services.

Input


Name	Type	Required	Description
CallbackURL	String	False	This field determines where the response is sent. This value is only necessary if you have registered your own app. This URL must have the same domain as the OAuth callback domain specified in the Xero Developer Portal.
Scope	String	False	The space-separated list of scopes you are requesting from Xero. By default all scopes are requested.
State	String	False	A state token to include in the authorization URL. Not included by default.

Result Set Columns


Name	Type	Description
URL	String	The URL to complete user authentication.
AuthToken	String	A token used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token.
AuthKey	String	A key used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token.
PKCEVerifier	String	A random value used as input for GetOAuthAccessToken in the PKCE flow.

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with various Xero services.

Input


Name	Type	Required	Description
OAuthRefreshToken	String	True	The refresh token returned from the original authorization code exchange.

Result Set Columns


Name	Type	Description
OAuthAccessToken	String	The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken	String	A token that may be used to obtain a new access token.
ExpiresIn	String	The remaining lifetime on the access token.

RevokeTenant

Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.

Input


Name	Type	Required	Description
TenantId	String	False	The ID of the tenant to revoke, as given in the Tenants view

Result Set Columns


Name	Type	Description
Success	String	Whether the connection was successfully revoked

SwitchTenant

Changes the tenant this connection fetches data from.

Input


Name	Type	Required	Description
TenantId	String	False	The ID of the tenant to attach to. If empty, the current tenant is not changed.

Result Set Columns


Name	Type	Description
ActiveTenant	String	The ID of the tenant that is currently active.

UploadFile

Uploads a file to the Xero Files service

Procedure-Specific Information

There are a few different ways to upload a file:

1. Provide the full path to a local file. This will create a file with the same name and content in Xero:

EXECUTE UploadFile @Filename = 'c:/my/xero/files/form.docx

2. Provide the full path to a local file as well as a Name. This will still upload the content in the file, but lets you change the name of the file in Xero:

EXECUTE UploadFile @Filename = 'c:/my/xero/files/form.docx', @Name = 'legal-form.docx'

3. Provide the file's content in base64 and the name the file should receive in Xero:

EXECUTE UploadFile @Name = hello.txt', @FileData = 'SGVsbG8sIFdvcmxkIQo='

In addition, you can provide a ContentType to override what type Xero should use when classifying the file. Please note however that Xero only supports a limited number of MIME types, which are listed in the Xero Central article on managing the file library.

Input


Name	Type	Required	Description
Name	String	False	The name the file will receive when uploaded. If not given, it will be determined from Filename.
ContentType	String	False	This parameter explicitly sets the MIME type for the document. If left empty, it will be determined automatically.
FileData	String	False	Base-64 encoded data for the file. Required if Filename and FileStream are not defined.
FileStream	String	False	Stream of input data. Required if Filename and FileData are not defined.
Filename	String	False	The full path (including filename) of the file to be uploaded. Required if FileData and FileStream are not defined.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Result Set Columns


Name	Type	Description
Success	String	Whether the operation was successful or not.
FileId	String	The unique Xero-generated ID of the file
Name	String	The name of the uploaded file
FolderId	String	The FolderId the file was uploaded to
MimeType	String	What type of data the uploaded file contains
Size	Int	The size of the uploaded file in bytes
User_Id	String	The unique ID of the user who uploaded the file
User_Name	String	The username of the user who uploaded the file
User_FirstName	String	The first name of the user who uploaded the file
User_LastName	String	The last name of the user who uploaded the file
User_FullName	String	The full name of the user who uploaded the file
Created	Datetime	When the file was first uploaded
Updated	Datetime	When the file was last changed

Fixed Assets Data Model

The Jitterbit Connector for Xero models the Xero Assets API as relational tables, views and stored procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

Tables describes the available tables.

Views

Views describes the available views.

Stored Procedures

NOTE: Stored procedures are not currently supported. See the above note for details.

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

Tables

The connector models the data in Xero into a list of tables that can be queried using standard SQL statements.

Jitterbit Connector for Xero Tables


Name	Description
Assets	Query, insert and update assets for a Xero organization.
AssetTypes	Query, insert and update asset types for a Xero organization.

Assets

Query, insert and update assets for a Xero organization.

Table-Specific Information

Creating Assets

An asset can be created with only an AssetName and an AssetNumber. This will create a draft asset with blank settings for purchase date and price, serial number and all other values.

INSERT INTO Assets(AssetName, AssetNumber) VALUES ('Corporate van', 'FA-0042')

Updating Assets

Like asset types, an asset's value can be measured with either a depreciation rate or an estimated lifetime. Once these values have been set, you can only change between the two by setting the other to null:

UPDATE Assets
SET BookDepreciationSetting_DepreciationRate = 0.12,
    BookDepreciationSetting_EffectiveLifeYears = null
WHERE AssetId = '46c7f1f4-7cdb-47d3-8772-2796d0b83e87'

Registering Assets

To move an asset from the draft to the registered state, you must assign these properties on it:

AssetTypeId
PurchaseDate
PurchasePrice
BookDepreciationSetting_DepreciationStartDate
BookDepreciationSetting_AveragingMethod
Either a BookDepreciationSetting_DepreciationRate or a BookDepreciationSetting_EffectiveLifeYears

Then you can update the AssetStatus field to the value "Registered".

Disposing Assets

Disposing of assets is not supported through the API.

Columns


Name	Type	ReadOnly	Description
AssetId [KEY]	String	True	The unique Xero-generated ID of the asset
AssetName	String	False	The name of the asset
AssetNumber	String	False	A unique code for the asset
AssetStatus	String	False	Either DRAFT, REGISTERED or DISPOSED
Description	String	False	Free-form comments on the asset
PurchaseDate	Date	False	When the asset was purchased
PurchasePrice	Decimal	False	How much asset cost when it was purchased
DisposalDate	Date	False	When the asset was purchased
DisposalPrice	Decimal	False	What price the asset was disposed at
WarrantyExpiryDate	Date	False	When the asset's warranty expires
SerialNumber	String	False	The asset's serial number
AssetTypeId	String	False	The ID of the type of this asset
BookDepreciationSetting_DepreciationMethod	String	False	Either NoDepreciation, StraightLine, DiminishingValue100, DiminishingValue150, DiminishingValue200 or FullDepreciation
BookDepreciationSetting_AveragingMethod	String	False	Either ActualDays or FullMonth
BookDepreciationSetting_DepreciationRate	Double	False	The rate of depreciation in percent
BookDepreciationSetting_EffectiveLifeYears	Int	False	The effective life of the asset in years
BookDepreciationSetting_DepreciationCalculationMethod	String	False	Either Rate, Life or None
BookDepreciationDetail_CurrentCapitalGain	Decimal	False	Once the asset is disposed, this will be the sell price minus the purchase price
BookDepreciationDetail_CurrentGainLoss	Decimal	False	Once the asset is disposed, this will be the minimum of the sell and purchase price, minus the current book value
BookDepreciationDetail_DepreciationStartDate	Date	False	When the depreciation takes effect
BookDepreciationDetail_CostLimit	Decimal	False	The value of the asset to be depreciated, if this is less than the cost
BookDepreciationDetail_ResidualValue	Decimal	False	The value of the asset once it's been fully depreciated
BookDepreciationDetail_PriorAccumDepreciationAmount	Decimal	False	All depreciation prior to the current fiscal year
BookDepreciationDetail_CurrentAccumDepreciationAmount	Decimal	False	All depreciation occurring in the current financial year
CanRollBack	Boolean	True	Whether the asset's depreciation can be rolled back
AccountingBookValue	Decimal	True	The accounting value of the asset
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

AssetTypes

Query, insert and update asset types for a Xero organization.

Table-Specific Information

Creating Asset Types

An AssetType can be created with one of two methods of computing depreciation. The first is according to a fixed percentage (in this example, 0.15%):

INSERT INTO AssetTypes(
  AssetTypeName,
  FixedAssetAccountId,
  DepreciationExpenseAccountId,
  AccumulatedDepreciationAccountId,
  BookDepreciationSetting_DepreciationMethod,
  BookDepreciationSetting_AveragingMethod,
  BookDepreciationSetting_DepreciationRate
)
VALUES (
  'Vehicles',
  '8e968d1a-5e25-4dad-b6f9-c8f4d72c0cac',
  '20b36596-3d1d-4789-9f08-50c92d19d522',
  '2a9bb066-3398-40e8-a953-caaabed2b7a7',
  'StraightLine',
  'FullMonth',
  0.15
)

Alternatively, you could create an asset type that depreciates according to an effective lifetime:

INSERT INTO AssetTypes(
  AssetTypeName,
  FixedAssetAccountId,
  DepreciationExpenseAccountId,
  AccumulatedDepreciationAccountId,
  BookDepreciationSetting_DepreciationMethod,
  BookDepreciationSetting_AveragingMethod,
  BookDepreciationSetting_EffectiveLifeYears
)
VALUES (
  'Vehicles',
  '8e968d1a-5e25-4dad-b6f9-c8f4d72c0cac',
  '20b36596-3d1d-4789-9f08-50c92d19d522',
  '2a9bb066-3398-40e8-a953-caaabed2b7a7',
  'StraightLine',
  'FullMonth',
  12
)

Updating Asset Types

Since an AssetType must be either using a depreciation rate or an effective lifetime, you must set one field to NULL if you want to change the AssetType to use the other. For example, to change from an effective lifetime to a depreciation rate:

UPDATE AssetTypes
SET BookDepreciationSetting_DepreciationRate = 0.09,
    BookDepreciationSetting_EffectiveLifeYears = null
WHERE AssetTypeId = 'bf28b6ca-3cc6-4591-ac5a-2c1115f04b4b'

Columns


Name	Type	ReadOnly	Description
AssetTypeId [KEY]	String	True	The unique Xero-generated ID of the asset type
AssetTypeName	String	False	The name of the asset type
FixedAssetAccountId	String	False	The asset account used for assets of this type
DepreciationExpenseAccountId	String	False	The expense account used when assets of this type depreciate
AccumulatedDepreciationAccountId	String	False	The account used for accumulated depreciation of assets of this type
BookDepreciationSetting_DepreciationMethod	String	False	Either NoDepreciation, StraightLine, DiminishingValue100, DiminishingValue150, DiminishingValue200 or FullDepreciation
BookDepreciationSetting_AveragingMethod	String	False	Either ActualDays or FullMonth
BookDepreciationSetting_DepreciationRate	Decimal	False	The rate of depreciation in percent
BookDepreciationSetting_EffectiveLifeYears	Int	False	The effective life of the asset in years
BookDepreciationSetting_DepreciationCalculationMethod	String	True	Either Rate, Life or None
Locks	Int	True	The number of assets using this asset type
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Views

Jitterbit Connector for Xero Views


Name	Description
Settings	Query asset settings for a Xero organization.
Tenants	Query the list of organizations connected to your Xero account.

Settings

Query asset settings for a Xero organization.

Columns


Name	Type	Description
AssetNumberPrefix	String	The prefix used for generating fixed asset numbers
AssetNumberSequence	String	The next available asset number
AssetStartDate	Date	The date Xero started calculating depreciation for fixed assets
LastDepreciationDate	Date	The last depreciation date
DefaultGainOnDisposalAccountId	String	The default account that gains are posted to
DefaultLossOnDisposalAccountId	String	The default account that losses are posted to
DefaultCapitalGainOnDisposalAccountId	String	The default account that capital gains are posted to
OptInForTax	Boolean
TenantId	String	The ID of the tenant to query instead of the connection tenant

Tenants

Query the list of organizations connected to your Xero account.

Columns


Name	Type	Description
TenantId [KEY]	String	A unique identifier for the tenant.
Name	String	The name of the organization or practice
Type	String	Whether the tenant is an organization or a practice
IsActive	Boolean	Whether the connection is currently using this tenant

Stored Procedures

NOTE: Stored procedures are not currently supported. See the above note for details.

Jitterbit Connector for Xero Stored Procedures


Name	Description
GetLimitInfo	Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant.
GetOAuthAccessToken	Obtains the OAuth access token to be used for authentication with Xero.
GetOAuthAuthorizationURL	Obtains the OAuth authorization URL for authentication with various Xero services.
RefreshOAuthAccessToken	Refreshes the OAuth access token used for authentication with various Xero services.
RevokeTenant	Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.
SwitchTenant	Changes the tenant this connection fetches data from.

GetLimitInfo

Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant.

Procedure-Specific Information

SELECT * FROM AssetTypes LIMIT 1

Result Set Columns


Name	Type	Description
TenantId	String	ID of the tenant that the limit applies to. The output contains a row for each tenant queried during the connection. Usually there is only one.
AppRemaining	Integer	The number of API calls the current OAuth app has this minute (max 10000). This applies across all tenants.
MinuteRemaining	Integer	The number of API calls the current OAuth app has this minute (max 60). This applies only to this tenant.
DayRemaining	Integer	The number of API calls the current OAuth app has today (max 5000). This applies only to this tenant.
RetryAfter	Integer	The suggested retry time, if one of the above limits was 0 on the most recent API call to the tenant.
LastModified	Datetime	When this information was last updated. The values in this table are only refreshed when performing API calls, so this may be older than the current time.

GetOAuthAccessToken

Obtains the OAuth access token to be used for authentication with Xero.

Input


Name	Type	Required	Description
AuthMode	String	True	The type of authentication mode to use. The allowed values are APP, WEB. The default value is WEB.
AuthToken	String	False	The authentication token returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
AuthKey	String	False	The AuthKey returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
CallbackURL	String	False	This field determines where the response is sent. The value of this parameter must exactly match one of the values registered in the Developer Portal, including the HTTP or HTTPS schemes, capitalization, and trailing forward slash ('/').
Verifier	String	False	The verifier token returned by Xero after using the URL obtained with GetOAuthAuthorizationURL. Required for only the Web AuthMode.
PKCEVerifier	String	False	The PKCEVerifier returned by GetOAuthAuthorizationURL.

Result Set Columns


Name	Type	Description
OAuthAccessToken	String	The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthAccessTokenSecret	String	The authentication token secret returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken	String	A token that may be used to obtain a new access token.
ExpiresIn	String	The remaining lifetime on the access token.

GetOAuthAuthorizationURL

Obtains the OAuth authorization URL for authentication with various Xero services.

Input


Name	Type	Required	Description
CallbackURL	String	False	This field determines where the response is sent. This value is only necessary if you have registered your own app. This URL must have the same domain as the OAuth callback domain specified in the Xero Developer Portal.
Scope	String	False	The space-separated list of scopes you are requesting from Xero. By default all scopes are requested.
State	String	False	A state token to include in the authorization URL. Not included by default.

Result Set Columns


Name	Type	Description
URL	String	The URL to complete user authentication.
AuthToken	String	A token used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token.
AuthKey	String	A key used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token.
PKCEVerifier	String	A random value used as input for GetOAuthAccessToken in the PKCE flow.

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with various Xero services.

Input


Name	Type	Required	Description
OAuthRefreshToken	String	True	The refresh token returned from the original authorization code exchange.

Result Set Columns


Name	Type	Description
OAuthAccessToken	String	The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken	String	A token that may be used to obtain a new access token.
ExpiresIn	String	The remaining lifetime on the access token.

RevokeTenant

Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.

Input


Name	Type	Required	Description
TenantId	String	False	The ID of the tenant to revoke, as given in the Tenants view

Result Set Columns


Name	Type	Description
Success	String	Whether the connection was successfully revoked

SwitchTenant

Changes the tenant this connection fetches data from.

Input


Name	Type	Required	Description
TenantId	String	False	The ID of the tenant to attach to. If empty, the current tenant is not changed.

Result Set Columns


Name	Type	Description
ActiveTenant	String	The ID of the tenant that is currently active.

Projects Data Model

The Jitterbit Connector for Xero models the Xero Projects API as relational tables, views and stored procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

Tables describes the available tables.

Views

Views describes the available views.

Stored Procedures

NOTE: Stored procedures are not currently supported. See the above note for details.

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

Tables

The connector models the data in Xero into a list of tables that can be queried using standard SQL statements.

Jitterbit Connector for Xero Tables


Name	Description
Projects	Query, create and update projects for a Xero organisation.
Tasks	Query, create update and delete tasks for a Xero project.
Time	Query, create update and delete time entries for a Xero project.

Projects

Query, create and update projects for a Xero organisation.

Table-Specific Information

Creating Projects

A Project must be created with a Name and ContactId. The ContactId comes from the ContactId column in the Contacts table of the Accounting API. An EstimateValue may also be provided to set the initial estimate for the project:

INSERT INTO Projects(Name, ContactId, Deadline, EstimateValue) VALUES ('Construct Parking Lot', '1c5ac798-1e31-4c09-82ac-4781261ff20a', '2025-01-01', 250000)

Updating Projects

Once a project has been created, only its Deadline and EstimateValue may be updated:

UPDATE Projects SET Deadline = '2022-01-01', EstimateValue = 150000 WHERE ProjectId = '1de78bad-5a81-4cb8-ab53-5a1a3bc73b29'

Columns


Name	Type	ReadOnly	Description
ProjectId [KEY]	String	True	The unique Xero identifier of the project.
ContactId	String	False	The ID of the Contact the project was created for
Name	String	False	The name of the project.
CurrencyCode	String	True	The currency used within the project.
MinutesLogged	Int	True	The total minutes logged against all tasks in the project
TotalTaskAmountValue	Decimal	True	The total actuals amount of each project task
TotalTaskAmountCurrency	String	True	The currency of the TotalTaskAmountValue
TotalExpenseAmountValue	Decimal	True	The total actuals amount of each project expense
TotalExpenseAmountCurrency	String	True	The currency of the TotalExpenseAmountValue
MinutesToBeInvoiced	Int	True	The total minutes across all tasks which have not been invoiced
TaskAmountToBeInvoicedValue	Decimal	True	The total AmountToBeInvoicedValue for each task in the project
TaskAmountToBeInvoicedCurrency	String	True	The currency of the TaskAmountToBeInvoicedValue
TaskAmountInvoicedValue	Decimal	True	The total AmountInvoicedValue for each task in the project
TaskAmountInvoicedCurrency	String	True	The currency of the TaskAmountInvoicedValue
ExpenseAmountToBeInvoicedValue	Decimal	True	The total of all the expenses in the project which have not been invoiced
ExpenseAmountToBeInvoicedCurrency	String	True	The currency of the ExpenseAmountToBeInvoicedValue
ExpenseAmountInvoicedValue	Decimal	True	The total of all the expenses in the project which have been invoiced
ExpenseAmountInvoicedCurrency	String	True	The currency of the ExpenseAmountInvoicedValue
ProjectAmountInvoicedValue	Decimal	True	The total amount that has been invoiced across the whole project
ProjectAmountInvoicedCurrency	String	True	The currency of the ProjectAmountInvoicedValue
DepositValue	Decimal	True	The deposit for the project
DepositCurrency	String	True	The currency of the DepositValue
DepositAppliedValue	Decimal	True	The amount of the deposit which have been credited to project invoices.
DepositAppliedCurrency	String	True	The currency of the DepositAppliedValue
CreditNoteValue	Decimal	True	The total amount of credit notes in the project
CreditNoteCurrency	String	True	The currency of the CreditNoteValue
Deadline	Date	False	The currency of the CreditNoteValue
TotalInvoicedValue	Decimal	True	The total amount invoiced to the project across all categories
TotalInvoicedCurrency	String	True	The currency of the TotalInvoicedValue
TotalToBeInvoicedValue	Decimal	True	The total amount for the project that is yet to be invoiced, across all categories
TotalToBeInvoicedCurrency	String	True	The currency of the TotalToBeInvoicedValue
EstimateValue	Decimal	False	The estimate for the project
EstimateCurrency	String	True	The currency of the EstimateValue
Status	String	True	Either INPROGRESS or CLOSED
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Tasks

Query, create update and delete tasks for a Xero project.

Table-Specific Information

Creating Tasks

A Task must be created with a ProjectId, Name, RateCurrency, RateValue, and ChargeType. The ProjectId and RateCurrency must come from the project (ProjectId and CurrencyCode respectively). ChargeType determines how the RateValue is applied to the cost of the project:

TIME charges the project the amount in RateValue every hour.
FIXED charges the project the amount in RateValue once.
NON_CHARGEABLE does not charge the project. RateValue must be set to 0.

The EstimateMinutes may also be provided to set the time estimate for the task:

INSERT INTO Tasks(ProjectId, Name, RateCurrency, RateValue, ChargeType, EstimateMinutes) VALUES ('1de78bad-5a81-4cb8-ab53-5a1a3bc73b29', 'Pave Sidewalks', 'USD', 25000, 'FIXED', 1000)

Updating Tasks

Once a Task has been created, its RateCurrency, RateValue, ChargeType or EstimateMinutes may be updated:

UPDATE Tasks SET RateValue = 250, ChargeType = 'TIME', EstimateMinutes = 2000 WHERE ID = '1de78bad-5a81-4cb8-ab53-5a1a3bc73b29/d15810a1-9324-4765-a357-80d160a0b87c'

Deleting Tasks

Tasks may also be deleted by specifying their Id:

DELETE FROM Tasks WHERE ID = '1de78bad-5a81-4cb8-ab53-5a1a3bc73b29/d15810a1-9324-4765-a357-80d160a0b87c'

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	False	A unique combination of the project and task identifiers
TaskId	String	True	The unique Xero identifier of the task.
ProjectId	String	False	The ID of the project
Name	String	False	The name of the task
ChargeType	String	False	One of TIME, FIXED or NON_CHARGEABLE
RateValue	Decimal	False	The per-hour rate billed for the task
RateCurrency	String	False	The currency of the RateValue
EstimateMinutes	Int	False	The estimated number of minutes to complete the task
TotalMinutes	Int	True	The total number of minutes logged against the task
TotalAmountValue	Decimal	True	The total value of the task, TotalMinutes multiplied by RateValue
TotalAmountCurrency	String	True	The currency of the TotalAmountValue
MinutesToBeInvoiced	Int	True	The minutes on this task that have not been invoiced
MinutesInvoiced	Int	True	The minutes on this task that have been invoiced
NonChargeableMinutes	Int	True	The minutes on this task that cannot be charged
AmountToBeInvoicedValue	Decimal	True	The total value of the task that has not been invoiced, MinutesToBeInvoiced multiplied by RateValue
AmountToBeInvoicedCurrency	String	True	The currency of the AmountToBeInvoicedValue
AmountInvoicedValue	Decimal	True	The total value of the task that has been invoiced, MinutesInvoiced multiplied by RateValue
AmountInvoicedCurrency	String	True	The currency of the AmountInvoicedValue
Status	String	True	One of ACTIVE, INVOICED or LOCKED
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Time

Query, create update and delete time entries for a Xero project.

Table-Specific Information

Creating Time

A Time must be created with a ProjectId, TaskId, UserId, Date and Duration. An optional description may also be provided:

INSERT INTO Time(ProjectId, TaskId, UserId, Date, Duration, Description) VALUES ('1de78bad-5a81-4cb8-ab53-5a1a3bc73b29', 'd15810a1-9324-4765-a357-80d160a0b87c', '312b124b-f33f-4cfe-a831-20eb290610d6', '2019-10-01', 250, 'Paved northwest section')

Updating Time

Once a Time has been created, its UserId, Date, Duration or Description may be updated:

UPDATE Time SET Date = '2019-11-01', Duration = 350 WHERE ID = '1de78bad-5a81-4cb8-ab53-5a1a3bc73b29/cb53829a-31ca-4760-a2a7-5a2a46e67cfb'

Deleting Time

Time may also be deleted by specifying their Id:

DELETE FROM Time WHERE ID = '1de78bad-5a81-4cb8-ab53-5a1a3bc73b29/cb53829a-31ca-4760-a2a7-5a2a46e67cfb'

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	False	A unique combination of the project and time entry identifiers
TimeEntryId	String	True	The unique Xero identifier of the time entry.
UserId	String	False	The ID of the user who logged the time entry
ProjectId	String	False	The ID of the project the task belongs to
TaskId	String	False	The ID of the task the time is logged for
Date	Date	False	The day the time was logged for
DateEntered	Datetime	True	When the time entry was created
Duration	Int	False	When the time entry was created
Description	String	False	The description of the time entry
Status	String	True	One of ACTIVE, LOCKED or INVOICED
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Views

Jitterbit Connector for Xero Views


Name	Description
Tenants	Query the list of organizations connected to your Xero account.
Users	Query project users for a Xero organisation.

Tenants

Query the list of organizations connected to your Xero account.

Columns


Name	Type	Description
TenantId [KEY]	String	A unique identifier for the tenant.
Name	String	The name of the organization or practice
Type	String	Whether the tenant is an organization or a practice
IsActive	Boolean	Whether the connection is currently using this tenant

Users

Query project users for a Xero organisation.

Columns


Name	Type	Description
UserId [KEY]	String	The unique Xero identifier of the user.
Name	String	The full name of the user.
Email	String	The email of the user.
TenantId	String	The ID of the tenant to query instead of the connection tenant

Stored Procedures

NOTE: Stored procedures are not currently supported. See the above note for details.

Jitterbit Connector for Xero Stored Procedures


Name	Description
GetLimitInfo	Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant.
GetOAuthAccessToken	Obtains the OAuth access token to be used for authentication with Xero.
GetOAuthAuthorizationURL	Obtains the OAuth authorization URL for authentication with various Xero services.
RefreshOAuthAccessToken	Refreshes the OAuth access token used for authentication with various Xero services.
RevokeTenant	Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.
SwitchTenant	Changes the tenant this connection fetches data from.

GetLimitInfo

Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant.

Procedure-Specific Information

SELECT * FROM Projects LIMIT 1

Result Set Columns


Name	Type	Description
TenantId	String	ID of the tenant that the limit applies to. The output contains a row for each tenant queried during the connection. Usually there is only one.
AppRemaining	Integer	The number of API calls the current OAuth app has this minute (max 10000). This applies across all tenants.
MinuteRemaining	Integer	The number of API calls the current OAuth app has this minute (max 60). This applies only to this tenant.
DayRemaining	Integer	The number of API calls the current OAuth app has today (max 5000). This applies only to this tenant.
RetryAfter	Integer	The suggested retry time, if one of the above limits was 0 on the most recent API call to the tenant.
LastModified	Datetime	When this information was last updated. The values in this table are only refreshed when performing API calls, so this may be older than the current time.

GetOAuthAccessToken

Obtains the OAuth access token to be used for authentication with Xero.

Input


Name	Type	Required	Description
AuthMode	String	True	The type of authentication mode to use. The allowed values are APP, WEB. The default value is WEB.
AuthToken	String	False	The authentication token returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
AuthKey	String	False	The AuthKey returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
CallbackURL	String	False	This field determines where the response is sent. The value of this parameter must exactly match one of the values registered in the Developer Portal, including the HTTP or HTTPS schemes, capitalization, and trailing forward slash ('/').
Verifier	String	False	The verifier token returned by Xero after using the URL obtained with GetOAuthAuthorizationURL. Required for only the Web AuthMode.
PKCEVerifier	String	False	The PKCEVerifier returned by GetOAuthAuthorizationURL.

Result Set Columns


Name	Type	Description
OAuthAccessToken	String	The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthAccessTokenSecret	String	The authentication token secret returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken	String	A token that may be used to obtain a new access token.
ExpiresIn	String	The remaining lifetime on the access token.

GetOAuthAuthorizationURL

Obtains the OAuth authorization URL for authentication with various Xero services.

Input


Name	Type	Required	Description
CallbackURL	String	False	This field determines where the response is sent. This value is only necessary if you have registered your own app. This URL must have the same domain as the OAuth callback domain specified in the Xero Developer Portal.
Scope	String	False	The space-separated list of scopes you are requesting from Xero. By default all scopes are requested.
State	String	False	A state token to include in the authorization URL. Not included by default.

Result Set Columns


Name	Type	Description
URL	String	The URL to complete user authentication.
AuthToken	String	A token used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token.
AuthKey	String	A key used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token.
PKCEVerifier	String	A random value used as input for GetOAuthAccessToken in the PKCE flow.

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with various Xero services.

Input


Name	Type	Required	Description
OAuthRefreshToken	String	True	The refresh token returned from the original authorization code exchange.

Result Set Columns


Name	Type	Description
OAuthAccessToken	String	The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken	String	A token that may be used to obtain a new access token.
ExpiresIn	String	The remaining lifetime on the access token.

RevokeTenant

Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.

Input


Name	Type	Required	Description
TenantId	String	False	The ID of the tenant to revoke, as given in the Tenants view

Result Set Columns


Name	Type	Description
Success	String	Whether the connection was successfully revoked

SwitchTenant

Changes the tenant this connection fetches data from.

Input


Name	Type	Required	Description
TenantId	String	False	The ID of the tenant to attach to. If empty, the current tenant is not changed.

Result Set Columns


Name	Type	Description
ActiveTenant	String	The ID of the tenant that is currently active.

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Xero:

sys_catalogs: Lists the available databases.
sys_schemas: Lists the available schemas.
sys_tables: Lists the available tables and views.
sys_tablecolumns: Describes the columns of the available tables and views.
sys_procedures: Describes the available stored procedures.
sys_procedureparameters: Describes stored procedure* parameters.
sys_keycolumns: Describes the primary and foreign keys.
sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

sys_connection_props: Returns information on the available connection properties.
sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries:

sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns


Name	Type	Description
CatalogName	String	The database name.

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas

Columns


Name	Type	Description
CatalogName	String	The database name.
SchemaName	String	The schema name.

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables

Columns


Name	Type	Description
CatalogName	String	The database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view.
TableType	String	The table type (table or view).
Description	String	A description of the table or view.
IsUpdateable	Boolean	Whether the table can be updated.

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Contacts table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Contacts'

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view containing the column.
ColumnName	String	The column name.
DataTypeName	String	The data type name.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The storage size of the column.
DisplaySize	Int32	The designated column's normal maximum width in characters.
NumericPrecision	Int32	The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The column scale or number of digits to the right of the decimal point.
IsNullable	Boolean	Whether the column can contain null.
Description	String	A brief description of the column.
Ordinal	Int32	The sequence number of the column.
IsAutoIncrement	String	Whether the column value is assigned in fixed increments.
IsGeneratedColumn	String	Whether the column is generated.
IsHidden	Boolean	Whether the column is hidden.
IsArray	Boolean	Whether the column is an array.

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures

Columns


Name	Type	Description
CatalogName	String	The database containing the stored procedure.
SchemaName	String	The schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure.
Description	String	A description of the stored procedure.
ProcedureType	String	The type of the procedure, such as PROCEDURE or FUNCTION.

sys_procedureparameters

Describes stored procedure* parameters.

The following query returns information about all of the input parameters for the RefreshOAuthAccessToken stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='RefreshOAuthAccessToken' AND Direction=1 OR Direction=2

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the stored procedure.
SchemaName	String	The name of the schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure* containing the parameter.
ColumnName	String	The name of the stored procedure* parameter.
Direction	Int32	An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName	String	The name of the data type.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision	Int32	The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The number of digits to the right of the decimal point in numeric data.
IsNullable	Boolean	Whether the parameter can contain null.
IsRequired	Boolean	Whether the parameter is required for execution of the procedure.
IsArray	Boolean	Whether the parameter is an array.
Description	String	The description of the parameter.
Ordinal	Int32	The index of the parameter.

sys_keycolumns

Describes the primary and foreign keys. The following query retrieves the primary key for the Contacts table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Contacts'

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
IsKey	Boolean	Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey	Boolean	Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.

sys_foreignkeys

Describes the foreign keys. The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.
ForeignKeyType	String	Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the index.
SchemaName	String	The name of the schema containing the index.
TableName	String	The name of the table containing the index.
IndexName	String	The index name.
ColumnName	String	The name of the column associated with the index.
IsUnique	Boolean	True if the index is unique. False otherwise.
IsPrimary	Boolean	True if the index is a primary key. False otherwise.
Type	Int16	An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder	String	The sort order: A for ascending or D for descending.
OrdinalPosition	Int16	The sequence number of the column in the index.

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:xero:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns


Name	Type	Description
Name	String	The name of the connection property.
ShortDescription	String	A brief description.
Type	String	The data type of the connection property.
Default	String	The default value if one is not explicitly set.
Values	String	A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value	String	The value you set or a preconfigured default.
Required	Boolean	Whether the property is required to connect.
Category	String	The category of the connection property.
IsSessionProperty	String	Whether the property is a session property, used to save information about the current connection.
Sensitivity	String	The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName	String	A camel-cased truncated form of the connection property name.
Ordinal	Int32	The index of the parameter.
CatOrdinal	Int32	The index of the parameter category.
Hierarchy	String	Shows dependent properties associated that need to be set alongside this one.
Visible	Boolean	Informs whether the property is visible in the connection UI.
ETC	String	Various miscellaneous information about the property.

sys_sqlinfo

Describes the SELECT query processing that the connector can offload to the data source.

Collaborative Query Processing

When working with data sources that do not support SQL-92, you can query the sys_sqlinfo view to determine the query capabilities of the underlying APIs, expressed in SQL syntax. The connector offloads as much of the SELECT statement processing as possible to the server and then processes the rest of the query in memory.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. The following result set indicates the SELECT functionality that the connector can offload to the data source or process client side. Your data source may support additional SQL syntax. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.


Name	Description	Possible Values
AGGREGATE_FUNCTIONS	Supported aggregation functions.	AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT	Whether COUNT function is supported.	YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR	The opening character used to escape an identifier.	[
IDENTIFIER_QUOTE_CLOSE_CHAR	The closing character used to escape an identifier.	]
SUPPORTED_OPERATORS	A list of supported SQL operators.	=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY	Whether GROUP BY is supported, and, if so, the degree of support.	NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
STRING_FUNCTIONS	Supported string functions.	LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS	Supported numeric functions.	ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS	Supported date/time functions.	NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES	Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS	A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN	String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION	Indicates if the provider supports transactions such as commit and rollback.	YES, NO
DIALECT	Indicates the SQL dialect to use.
KEY_PROPERTIES	Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS	Indicates if multiple schemas may exist for the provider.	YES, NO
SUPPORTS_MULTIPLE_CATALOGS	Indicates if multiple catalogs may exist for the provider.	YES, NO
DATASYNCVERSION	The Data Sync version needed to access this driver.	Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY	The Data Sync category of this driver.	Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL	Whether enhanced SQL functionality beyond what is offered by the API is supported.	TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS	Whether batch operations are supported.	YES, NO
SQL_CAP	All supported SQL capabilities for this driver.	SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS	A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY	Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side.	YES, NO
PSEUDO_COLUMNS	A string array indicating the available pseudo columns.
MERGE_ALWAYS	If the value is true, The Merge Mode is forcibly executed in Data Sync.	TRUE, FALSE
REPLICATION_MIN_DATE_QUERY	A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION	Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE	Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY	A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION	Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE	A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID	Indicates whether the CheckCache statement should be done against the parent key column.	TRUE, FALSE
CREATE_SCHEMA_PROCEDURES	Indicates stored procedures* that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name='SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns


Name	Type	Description
NAME	String	A component of SQL syntax, or a capability that can be processed on the server.
VALUE	String	Detail on the supported SQL or SQL syntax.

sys_identity

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

SELECT * FROM sys_identity

Columns


Name	Type	Description
Id	String	The database-generated ID returned from a data modification operation.
Batch	String	An identifier for the batch. 1 for a single operation.
Operation	String	The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message	String	SUCCESS or an error message if the update in the batch failed.

Advanced Configurations Properties

The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.

Authentication

Property	Description
AuthScheme	The type of authentication to use when connecting to Xero.
Schema	The Xero API you want to access as a database schema.
Tenant	Sets the Xero organization to connect to. Can be a name or tenant ID.

OAuth

Property	Description
InitiateOAuth	Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientId	The client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecret	The client secret assigned when you register your application with an OAuth authorization server.
Scope	This determines the scopes that the OAuth application requests from Xero.
CallbackURL	The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
OAuthVerifier	The verifier code returned from the OAuth authorization URL.
OAuthRefreshToken	The OAuth refresh token for the corresponding OAuth access token.
OAuthExpiresIn	The lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestamp	The Unix epoch timestamp in milliseconds when the current Access Token was created.

SSL

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

Schema

Property	Description
Location	A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables	This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views	Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Miscellaneous

Property	Description
AutoExpandDetails	Set this property to true automatically include extra details on BankTransfers, ContactGroups, ExpenseClaims, ExpenseClaimPayments, ExpenseClaimReceipts and Receipts.
IncludeArchived	Set this property to include archived records.
MaxRows	Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
Other	These hidden properties are used only in specific use cases.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
RetryDailyLimit	Whether to wait when Xero reports that the provider has hit its daily limit.
RetryWaitTime	How long to wait when a request hits the Xero API limit, in milliseconds.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.

Authentication

This section provides a complete list of authentication properties you can configure.

Property	Description
AuthScheme	The type of authentication to use when connecting to Xero.
Schema	The Xero API you want to access as a database schema.
Tenant	Sets the Xero organization to connect to. Can be a name or tenant ID.

AuthScheme

The type of authentication to use when connecting to Xero.

Possible Values

Auto, OAuth, PKCE

Data Type

string

Default Value

"PKCE"

Remarks

Current authentication methods supported for new apps:

OAuth: Authenticates you using OAuth with a client ID and secret. Should be used with server-side applications.
PKCE: Authenticates you using OAuth with only a client ID. Should be used with client-side applications.
Auto: Uses the value of the XeroAppAuthentication property to determine the authentication mode. XeroAppAuthentication is included only for backwards compatibility and all new connections should use a different AuthScheme property.

Schema

The Xero API you want to access as a database schema.

Possible Values

ACCOUNTING, ASSETS, PAYROLLAUS, FILES, PROJECTS

Data Type

string

Default Value

"ACCOUNTING"

Remarks

The connector supports the Accounting API, the Assets API, the Australian Payroll API, the Files API and the Projects API. Set the Schema connection property to Accounting, Assets, PayrollAUS, Files or Projects to connect to one of the corresponding APIs.

Tenant

Sets the Xero organization to connect to. Can be a name or tenant ID.

Data Type

string

Default Value

Remarks

Xero apps may be authorized by for multiple organizatins at once. By default the connector will connect to the first available tenant. To control which organization your are connected to, set this option.

This option can be set to either the name of your organization or to the tenant ID. If you have multiple organizations with the same name, then you must use the tenant ID. You can find the tenant ID by connecting with this option unset and then querying the Tenants view.

OAuth

This section provides a complete list of OAuth properties you can configure.

Property	Description
InitiateOAuth	Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientId	The client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecret	The client secret assigned when you register your application with an OAuth authorization server.
Scope	This determines the scopes that the OAuth application requests from Xero.
CallbackURL	The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
OAuthVerifier	The verifier code returned from the OAuth authorization URL.
OAuthRefreshToken	The OAuth refresh token for the corresponding OAuth access token.
OAuthExpiresIn	The lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestamp	The Unix epoch timestamp in milliseconds when the current Access Token was created.

InitiateOAuth

Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.

Possible Values

OFF, GETANDREFRESH, REFRESH

Data Type

string

Default Value

"OFF"

Remarks

The following options are available:

OFF: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.
GETANDREFRESH: Indicates that the entire OAuth Flow will be handled by the connector. If no token currently exists, it will be obtained by prompting the user via the browser. If a token exists, it will be refreshed when applicable.
REFRESH: Indicates that the connector will only handle refreshing the OAuthAccessToken. The user will never be prompted by the connector to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.

Authentication on Headless Machines

See to obtain the OAuthVerifier value.

Set OAuthSettingsLocation along with OAuthVerifier. When you connect, the connector exchanges the OAuthVerifier for the OAuth authentication tokens and saves them, encrypted, to the specified file. Set InitiateOAuth to GETANDREFRESH automate the exchange.

Remarks

Pair with OAuthExpiresIn to determine when the AccessToken will expire.

SSL

This section provides a complete list of SSL properties you can configure.

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

SSLServerCert

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:


Description	Example
A full PEM Certificate (example shortened for brevity)	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate	C:\cert.cer
The public key (example shortened for brevity)	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated)	ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated)	34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Schema

This section provides a complete list of schema properties you can configure.

Property	Description
Location	A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables	This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views	Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Data Type

string

Default Value

"%APPDATA%\\Xero Data Provider\Schema"

Remarks

The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

Note: Given that this connector supports multiple schemas, the structure for Xero custom schema files is as follows:

Each schema is given a folder corresponding to that schema name.
These schema folders are contained in a parent folder.
The parent folder should be set as the Location, not an individual schema's folder.

If left unspecified, the default location is "%APPDATA%\\Xero Data Provider\Schema" with %APPDATA% being set to the user's configuration directory:


Platform	%APPDATA%
Windows	The value of the APPDATA environment variable
Mac	~/Library/Application Support
Linux	~/.config

BrowsableSchemas

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Tables

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Data Type

string

Default Value

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Views

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Data Type

string

Default Value

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Miscellaneous

This section provides a complete list of miscellaneous properties you can configure.

Property	Description
AutoExpandDetails	Set this property to true automatically include extra details on BankTransfers, ContactGroups, ExpenseClaims, ExpenseClaimPayments, ExpenseClaimReceipts and Receipts.
IncludeArchived	Set this property to include archived records.
MaxRows	Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
Other	These hidden properties are used only in specific use cases.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
RetryDailyLimit	Whether to wait when Xero reports that the provider has hit its daily limit.
RetryWaitTime	How long to wait when a request hits the Xero API limit, in milliseconds.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.

AutoExpandDetails

Set this property to true automatically include extra details on BankTransfers, ContactGroups, ExpenseClaims, ExpenseClaimPayments, ExpenseClaimReceipts and Receipts.

Possible Values

True, False

Data Type

string

Default Value

"False"

Remarks

Possible values include True and False. When set to true, this makes some columns contain data that are otherwise NULL, but it will make reads from those tables slower than they are by default.

When set to false (the default), the only way to get extra details from those tables is to explicitly select single items:

SELECT * FROM ExpenseClaims WHERE ExpenseClaimId = '...'

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting


DefaultColumnSize	Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMT	Determines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename	Records the underlying socket data transfer to the specified file.

Data Type

int

Default Value

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the connector throws an exception.

➤