Xero Connection

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.

  1. Extracts the access token from the callback URL and authenticates requests.
  2. Obtains a new access token when the old one expires.
  3. 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:

  1. Extracts the access token from the callback URL and authenticates requests.
  2. Obtains a new access token when it expires.
  3. 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.

  1. 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.
  2. 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.

  1. Call the GetOAuthAuthorizationURL stored procedure* with the CallbackURL input parameter set to the exact Redirect URI you specified in your app settings.
  2. 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.
  3. 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:

  1. Log in to the Xero developer portal.
  2. Click My Apps -> Add Application. Choose the Auth Code application type.
  3. Enter a name for your application and the URL of your company. This information is displayed to users when they connect.
  4. 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

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.
  1. 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>'
    )
  2. 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

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: FromBankAccount_Code, ToBankAccount_Code, and Amount

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.
  1. 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>'
    )
  2. 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.
  1. You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the Invoices API for more details.
    INSERT INTO Invoices (Type, ContactName, LineItemAggregate) VALUES (
      'ACCPAY',
      'Boom FM',
      '<LineItem><Description>LineItem1Desc</Description><Quantity>1</Quantity><UnitAmount>30</UnitAmount></LineItem> <LineItem><Description>LineItem2Desc</Description><Quantity>2</Quantity><UnitAmount>50</UnitAmount></LineItem>'
    )
  2. 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.

  1. 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>'
    )
  2. 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.
  1. 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 Receipts API for more details.
    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>'
    )
  2. 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.
  1. 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>'
    )
  2. 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.

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
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.
  1. 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>'
    )
  2. 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.
  1. 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>'
    )
  2. 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

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
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.

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
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

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 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.

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
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

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
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.

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
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

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 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.

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
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:

  1. AssetTypeId
  2. PurchaseDate
  3. PurchasePrice
  4. BookDepreciationSetting_DepreciationStartDate
  5. BookDepreciationSetting_AveragingMethod
  6. 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

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
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.

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
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

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 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.

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
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

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
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.

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
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

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 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:

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.

NameDescriptionPossible Values
AGGREGATE_FUNCTIONSSupported aggregation functions.AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNTWhether COUNT function is supported.YES, NO
IDENTIFIER_QUOTE_OPEN_CHARThe opening character used to escape an identifier.[
IDENTIFIER_QUOTE_CLOSE_CHARThe closing character used to escape an identifier.]
SUPPORTED_OPERATORSA list of supported SQL operators.=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BYWhether GROUP BY is supported, and, if so, the degree of support.NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
STRING_FUNCTIONSSupported 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_FUNCTIONSSupported 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_FUNCTIONSSupported 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_TABLESIndicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNSA 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_PATTERNString value indicating what string is valid for an identifier.
SUPPORT_TRANSACTIONIndicates if the provider supports transactions such as commit and rollback.YES, NO
DIALECTIndicates the SQL dialect to use.
KEY_PROPERTIESIndicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMASIndicates if multiple schemas may exist for the provider.YES, NO
SUPPORTS_MULTIPLE_CATALOGSIndicates if multiple catalogs may exist for the provider.YES, NO
DATASYNCVERSIONThe Data Sync version needed to access this driver.Standard, Starter, Professional, Enterprise
DATASYNCCATEGORYThe Data Sync category of this driver.Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQLWhether enhanced SQL functionality beyond what is offered by the API is supported.TRUE, FALSE
SUPPORTS_BATCH_OPERATIONSWhether batch operations are supported.YES, NO
SQL_CAPAll 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_OPTIONSA string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERYIndicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side.YES, NO
PSEUDO_COLUMNSA string array indicating the available pseudo columns.
MERGE_ALWAYSIf the value is true, The Merge Mode is forcibly executed in Data Sync.TRUE, FALSE
REPLICATION_MIN_DATE_QUERYA select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTIONAllows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATEAllows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERYA select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTIONAllows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATEA list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTIDIndicates whether the CheckCache statement should be done against the parent key column.TRUE, FALSE
CREATE_SCHEMA_PROCEDURESIndicates 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


PropertyDescription
AuthSchemeThe type of authentication to use when connecting to Xero.
SchemaThe Xero API you want to access as a database schema.
TenantSets the Xero organization to connect to. Can be a name or tenant ID.

OAuth


PropertyDescription
InitiateOAuthSet this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientIdThe client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecretThe client secret assigned when you register your application with an OAuth authorization server.
ScopeThis determines the scopes that the OAuth application requests from Xero.
CallbackURLThe OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
OAuthVerifierThe verifier code returned from the OAuth authorization URL.
OAuthRefreshTokenThe OAuth refresh token for the corresponding OAuth access token.
OAuthExpiresInThe lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestampThe Unix epoch timestamp in milliseconds when the current Access Token was created.

SSL


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

Schema


PropertyDescription
LocationA path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemasThis property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
TablesThis property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
ViewsRestricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Miscellaneous


PropertyDescription
AutoExpandDetailsSet this property to true automatically include extra details on BankTransfers, ContactGroups, ExpenseClaims, ExpenseClaimPayments, ExpenseClaimReceipts and Receipts.
IncludeArchivedSet this property to include archived records.
MaxRowsLimits 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.
OtherThese hidden properties are used only in specific use cases.
PseudoColumnsThis property indicates whether or not to include pseudo columns as columns to the table.
RetryDailyLimitWhether to wait when Xero reports that the provider has hit its daily limit.
RetryWaitTimeHow long to wait when a request hits the Xero API limit, in milliseconds.
TimeoutThe 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.


PropertyDescription
AuthSchemeThe type of authentication to use when connecting to Xero.
SchemaThe Xero API you want to access as a database schema.
TenantSets 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.


PropertyDescription
InitiateOAuthSet this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientIdThe client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecretThe client secret assigned when you register your application with an OAuth authorization server.
ScopeThis determines the scopes that the OAuth application requests from Xero.
CallbackURLThe OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
OAuthVerifierThe verifier code returned from the OAuth authorization URL.
OAuthRefreshTokenThe OAuth refresh token for the corresponding OAuth access token.
OAuthExpiresInThe lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestampThe 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:

  1. OFF: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.
  2. 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.
  3. 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.

OAuthClientId

The client ID assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.

OAuthClientSecret

The client secret assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.

Scope

This determines the scopes that the OAuth application requests from Xero.

Data Type

string

Default Value

""

Remarks

By default the connector will request that the user authorize all available scopes. If you want to override this, you can set this property to a space-separated list of OAuth scopes.

CallbackURL

The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.

Data Type

string

Default Value

""

Remarks

During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.

OAuthVerifier

The verifier code returned from the OAuth authorization URL.

Data Type

string

Default Value

""

Remarks

The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.

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.

Once the OAuth settings file has been generated, you can remove OAuthVerifier from the connection properties and connect with OAuthSettingsLocation set.

To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.

OAuthRefreshToken

The OAuth refresh token for the corresponding OAuth access token.

Data Type

string

Default Value

""

Remarks

The OAuthRefreshToken property is used to refresh the OAuthAccessToken when using OAuth authentication.

OAuthExpiresIn

The lifetime in seconds of the OAuth AccessToken.

Data Type

string

Default Value

""

Remarks

Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.

OAuthTokenTimestamp

The Unix epoch timestamp in milliseconds when the current Access Token was created.

Data Type

string

Default Value

""

Remarks

Pair with OAuthExpiresIn to determine when the AccessToken will expire.

SSL

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


PropertyDescription
SSLServerCertThe 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.


PropertyDescription
LocationA path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemasThis property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
TablesThis property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
ViewsRestricts 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`.

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.

Miscellaneous

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


PropertyDescription
AutoExpandDetailsSet this property to true automatically include extra details on BankTransfers, ContactGroups, ExpenseClaims, ExpenseClaimPayments, ExpenseClaimReceipts and Receipts.
IncludeArchivedSet this property to include archived records.
MaxRowsLimits 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.
OtherThese hidden properties are used only in specific use cases.
PseudoColumnsThis property indicates whether or not to include pseudo columns as columns to the table.
RetryDailyLimitWhether to wait when Xero reports that the provider has hit its daily limit.
RetryWaitTimeHow long to wait when a request hits the Xero API limit, in milliseconds.
TimeoutThe 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 = '...'

IncludeArchived

Set this property to include archived records.

Possible Values

True, False

Data Type

string

Default Value

"False"

Remarks

Possible values include True and False.

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.

Data Type

int

Default Value

-1

Remarks

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.

Data Type

string

Default Value

""

Remarks

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

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

PseudoColumns

This property indicates whether or not to include pseudo columns as columns to the table.

Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

RetryDailyLimit

Whether to wait when Xero reports that the provider has hit its daily limit.

Data Type

bool

Default Value

false

Remarks

Use of this option is strongly discouraged. See Xero API Limits for more details.

By default the connector will fail if it hits the daily API limit to avoid excessive wait times. If this is enabled and the connector hits the daily API limit, it will instead delay and retry after whatever length of time Xero recommends. This can potentially lead to the connector delaying for several hours.

RetryWaitTime

How long to wait when a request hits the Xero API limit, in milliseconds.

Data Type

string

Default Value

"10000"

Remarks

When connector issues more requests then Xero allows, this option determines how long the connector will wait before issuing another request. Increasing this value is recommended when multiple driver connections are active, such as when using Power BI.

This value is in milliseconds, so to use a delay of 10 seconds this property must be set to 10000.

Timeout

The value in seconds until the timeout error is thrown, canceling the operation.

Data Type

int

Default Value

60

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.

Copyright © 2022-2023 Jitterbit, Inc. All rights reserved.