接続の確立

Xero への接続を追加

Xero への接続を追加するには：

アプリケーションコンソールで、接続ページに移動します。
接続の追加パネルで、追加したい接続のアイコンを選択します。
Xero アイコンが利用できない場合は、Add More アイコンをクリックしてCData サイトからXero コネクタをダウンロードおよびインストールします。

必須プロパティについては、設定タブを参照してください。

通常必須ではない接続プロパティについては、高度な設定タブを参照してください。

Connecting to Xero

The Sync App supports using PKCE applications and OAuth applications. Both PKCE and OAuth connections to Xero are authenticated using OAuth.

When the Sync App starts, it opens a browser and requests your login information. The Sync App uses the credentials you provide to access your Xero data. These credentials are saved and automatically refreshed as needed.

To enable this authentication from all OAuth flows, you must set AuthScheme to OAuth, and you must create a custom OAuth application.

The following subsections describe how to authenticate to Xero from three common authentication flows:

Desktop: a connection to a server on the user's local machine, frequently used for testing and prototyping.
Web: access to data via a shared website.
Headless Server: a dedicated computer that provides services to other computers and their users, which is configured to operate without a monitor and keyboard.

For information about how to create a custom OAuth application, see カスタム認証アプリケーションの作成.

For a complete list of connection string properties available in Xero, see Connection.

Automatic refresh of the OAuth access token:

To have the Sync App automatically refresh the OAuth access token, do the following:

Before connecting to data for the first time, set the following connection parameters:
- InitiateOAuth = REFRESH.
- OAuthClientId = the client Id in your application settings.
- OAuthClientSecret = the client secret in your application settings.
- OAuthAccessToken = the access token returned by GetOAuthAccessToken.
- OAuthSettingsLocation = the path where you want the Sync App to save the OAuth values, which persist across connections.
On subsequent data connections, set the following:
- InitiateOAuth
- OAuthSettingsLocation

Manual refresh of the OAuth access token:

The only value needed to manually refresh the OAuth access token is the OAuth refresh token.

To manually refresh the OAuthAccessToken after the ExpiresIn period (returned by GetOAuthAccessToken) has elapsed, call the GetOAuthAccessToken stored procedure.
Set the following connection properties:
- OAuthClientId = the Client Id in your application settings.
- OAuthClientSecret = the Client Secret in your application settings.
Call GetOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken.
After the new tokens have been retrieved, set the OAuthAccessToken property to the value returned by GetOAuthAccessToken. This opens a new connection.

Store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Connecting to a Xero API

The Sync App supports several Xero APIs. To connect to them, set the appropriate connection property:

For the Accounting API, set Schema to ACCOUNTING.
For the Australian Payroll API, set Schema to PAYROLLAUS.
For the Files API, set Schema to FILES.
For the Fixed Assets API set Schema to ASSETS.
For the Projects API, set Schema to PROJECTS.

Specifying Permitted Organization Access

For any of the above APIs, we also recommend setting the Tenant property, which enables you to authorize the Sync App to access multiple organizations. Tenant is normally set to identify one or more organizations that the Sync App can access. Legal entries for Tenant can be either the name of the organization or its ID. (Since multiple organizations can have the same name, we recommend using the ID to set the Tenant property.)

Xero API の制限

Xero API Limits

The Xero API has usage limitations that may be encountered while using the CData Sync App. 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 Sync App 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 Sync App will use to avoid hitting the limit again.

Uncertified Connectors Limit

Uncertified OAuth applications are limited to 25 connections. There is no limit on connections for certified apps.

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

高度な機能

このセクションでは、Xero Sync App の高度な機能を厳選して説明します。

ユーザー定義ビュー

Sync App を使用すると、事前設定されたクエリによって内容が決定されるユーザー定義ビューと呼ばれる仮想テーブルを定義できます。このビューは、ドライバーに発行されるクエリを直接制御できない場合に有効です。カスタムビューの作成と設定の概要については、ユーザー定義ビューを参照してください。

SSL の設定

SSL の設定を使用して、Sync App が証明書のネゴシエーションをどのように扱うかを調整します。さまざまな証明書形式を選択できます。詳しくは、接続文字列オプションにあるSSLServerCert プロパティを参照してください。

ファイアウォールとプロキシ

Windows プロキシとHTTP プロキシを含むファイアウォールとプロキシに合致するようSync App を設定します。トンネル接続を設定することもできます。

クエリ処理

Sync App は、Xero にできるだけ多くのSELECT ステートメント処理をオフロードし、残りのクエリをクライアント側のインメモリで処理します。

詳しくはクエリ処理を参照してください。

ログ

CData ログを調整するために使用可能な設定の概要については、ログを参照してください。基本的なロギングでは、次の2つの接続プロパティを設定するだけです。LogModules 接続プロパティを使用してログに記録する情報のサブセットを選択できる、より洗練されたロギングをサポートする多数の機能があります。

SSL の設定

SSL 設定のカスタマイズ

デフォルトでは、Sync App はサーバーの証明書をシステムの信頼できる証明書ストアと照合してSSL / TLS のネゴシエーションを試みます。

別の証明書を指定するには、利用可能なフォーマットについてSSLServerCert プロパティを参照してください。

ファイアウォールとプロキシ

Firewall またはProxy 経由の接続

HTTP プロキシ

Windows のシステムプロキシ経由の接続では、接続プロパティを追加で設定する必要はありません。他のプロキシに接続するには、ProxyAutoDetect をfalse に設定します。

さらにHTTP プロキシへの認証には、ProxyServer とProxyPort に加えてProxyAuthScheme、ProxyUser、およびProxyPassword を設定します。

その他のプロキシ

次のプロパティを設定します。

プロキシベースのファイヤーウォールを使用するには、FirewallType、FirewallServer、およびFirewallPort を設定します。
接続をトンネルするには、FirewallType をTUNNEL に設定します。
認証するには、FirewallUser とFirewallPassword を設定します。
SOCKS プロキシへの認証には、さらにFirewallType をSOCKS5 に設定します。

データモデル

The CData Sync App 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.

INSERTs to specific tables which model line items in the Accounting API have batch support enabled. The recommended setting of BatchSize for maximum performance is 50.

See 会計データモデル 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.

INSERTs to specific tables which model line items in the Australian Payroll API have batch support enabled. The recommended setting of BatchSize for maximum performance is 50.

See オーストラリア給与データモデル 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 ファイルデータモデル 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 固定資産データモデル 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 プロジェクトデータモデル for the available entities in the Projects API.

会計データモデル

The CData Sync App 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

テーブル describes the available tables.

Views

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

Stored Procedures

ストアドプロシージャ are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

テーブル

Sync App はXero のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。

Xero Connector for CData Sync テーブル


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

Accounts

Query, insert, update and delete 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

To insert a single row, set the required fields. A new table object is 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

Query, insert and update 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

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

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

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

INSERT INTO BankTransactions (Type, Contact_ContactName, BankAccount_Code, LineItemAggregate) VALUES (
  'RECEIVE',
  'John Doe',
  '200',
  '<LineItem>
		<Description>Monthly account fee</Description>
		<UnitAmount>49.90</UnitAmount>
		<TaxType>NONE</TaxType>
		<TaxAmount>0.00</TaxAmount>
		<LineAmount>49.90</LineAmount>
		<AccountCode>404</AccountCode>
		<Quantity>1.0000</Quantity>
   </LineItem>'
)

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

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

UPDATE

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

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

Delete

The Xero API has limited support for deleting any object. In order to delete a record from the BankTransactions table, you must update the Status field to an appropriate value.

Columns


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

BankTransfers

Usage information for the operation BankTransfers.rsd.

Table Specific Information

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

SELECT

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

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

INSERT

To insert a single row, set the required fields. A new table object is 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

Query and insert 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 Sync App 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

To insert a single row, set the required fields. A new table object is created as a single entry. To create a new batch payment, the AccountId, Date and Reference 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. Please refer to the Xero API documentation for more details on what elements are supported within each payment entry.

INSERT INTO BatchPayments (AccountId, Reference, Date, PaymentAggregate) VALUES (
  'AAAAA-AAAAA-AAAAA-AAAAA',
  '2019-03-01',
  '<Payment>
		<Invoice>
			<InvoiceID>5aa9451d-95d1-4f95-a966-bbab2573f71c</InvoiceID>
			<HasErrors>false</HasErrors>
			<IsDiscounted>false</IsDiscounted>
		</Invoice>
		<PaymentID>a22a64cb-364e-43fa-9a1f-bb2cd1f4adde</PaymentID>
		<Reference>ref/cheque</Reference>
		<Amount>913.55</Amount>
	</Payment>
	<Payment>
		...
	</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

Query, insert, update and delete 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

Query, insert and update 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

To insert a single row, set the required fields. A new table object is 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.
CompanyNumber	String	False	The registration number of the company. The maximum length of this field is 50 characters.
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.
MergedToContactID	String	True	This field displays the destination ID of a contact that has been merged.
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.
PurchasesDefaultLineAmountType	String	True	The default purchases line amount type for contacts. Possible values are: INCLUSIVE, EXCLUSIVE, or NONE.
SalesDefaultAccountCode	String	False	The default sales account code for contacts.
SalesDefaultLineAmountType	String	True	The default sales line amount type for contacts. Possible values are: INCLUSIVE, EXCLUSIVE, or NONE.
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

Query, insert and update 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 Sync App; 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

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

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

You can also insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the CreditNotes API for more details.
```
INSERT INTO CreditNotes (Type, Contact_Name, LineItemAggregate) VALUES (
  'ACCPAYCREDIT',
  'John Doe',
  '<LineItem>
	<Description>LineItemDesc1</Description>
	<Quantity>1</Quantity>
  </LineItem>
  <LineItem>
	<Description>LineItemDesc2</Description>
	<Quantity>2</Quantity>
  </LineItem>'
)
```
You can insert a new line item on an existing object by specifying the Xero-generated identifier of the existing record. For example, to add a line item to an existing credit note:
```
INSERT INTO CreditNotes (CreditNoteId, LineItem_Description, LineItem_Quantity) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'New Line Item Desc', 1)
```

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

UPDATE

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

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

DELETE

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

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

Columns


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

Employees

Query, insert and update 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

To insert a single row, set the required fields. A new table object is 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

Query, insert and update 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

Query and insert 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

Query and insert 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

Query and insert 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

Query and insert 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

Query and insert 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

Query and insert 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

Query, insert and update Invoices for a Xero organisation.

Table Specific Information

The Invoices table allows you to SELECT, INSERT, UPDATE, and DELETE invoice line items for a Xero organization. The Id column is generated by the Sync App 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

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

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

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

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

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

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

UPDATE

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

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

DELETE

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

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

Discount Amounts, Discount Rates and Line Amounts

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

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

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

It also means that the Sync App 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 Sync App 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

Query, insert, update and delete 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

To insert a single row, set the required fields. A new table object is 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

Query, insert, update and delete 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

Query, insert and update 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 Sync App; 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

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

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

INSERT INTO ManualJournals (Narration, JournalLineAggregate) VALUES (
  'Accrued expenses',
  '<JournalLine>
		<Description>Coded incorrectly Office Equipment should be Computer Equipment</Description>
		<TaxType>NONE</TaxType>
		<LineAmount>-2569</LineAmount>
		<AccountCode>720</AccountCode>
	</JournalLine>
	<JournalLine>
		...
	</JournalLine>'
)

You can also insert a new line item on an existing object by specifying the Xero-generated value of the existing record. For example, to add a line item to an existing invoice:
```
INSERT INTO ManualJournals (ManualJournalId, JournalLine_LineAmount, JournalLine_AccountCode) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 55.00, '200')
```

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

UPDATE

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

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

Columns


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

Payments

Query, insert and delete 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

To insert a single row, set the required fields. A new table object is 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

Query, insert and update 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

Quotes

Query quotes for a Xero organization

Table Specific Information

The Quotes table allows you to SELECT, INSERT, and UPDATE Quote lines for a Xero organization. The Id column is generated by the Sync App; it combines the index of the line item with the unique, Xero-generated QuoteId.

SELECT

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

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

INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. To insert a new Quote, the ContactId and Date fields are required in addition to at least one line item. A Quote line item must at minimum contain a description (LineItem_Description).

You can insert a new Quote with multiple line items using an XML aggregate. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix. Please refer to the Xero documentation of the Quotes API for more details.
```
INSERT INTO Quotes (ContactId, Date, LineItemAggregate) VALUES (
  '883628bc-7f22-4234-8a4c-81571816a8a1',
  '2023-11-21',
  '<LineItem>
		<Description>Example Description</Description>
	</LineItem>'
)
```

You can also create multiple Quotes in a single query by using a bulk insert. To do this, you should not include a QuoteId in your query, then specify the values for the first line item of each Quote to add.

INSERT INTO Quotes (ContactId, Date, LineItem_Description)
  VALUES
('883628bc-7f22-4234-8a4c-81571816a8a1', '2023-11-21', 'Example Description'),
('883628bc-7f22-4234-8a4c-81571816a8a1', '2023-11-21', 'Example Description')

You can also insert new line items on an existing object by specifying the Xero-generated value of the existing record. For example, to add 2 new line items to an existing Quote:
```
INSERT INTO Quotes (QuoteId, LineItem_Description)
  VALUES
('fbb38812-dcc4-4e07-9ebd-2c38e1409b18', 'newItem1'),
('fbb38812-dcc4-4e07-9ebd-2c38e1409b18', 'newItem2')
```

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	False	The line item index combined with the Id of the Quote.
QuoteId	String	False	The Id of the Quote. This field is assigned by Xero.
ContactId	String	False	The Id of the contact.
ContactName	String	False	The name of the contact.
Date	Date	False	The date the quote was issued.
ExpiryDate	Date	False	The date the quote expires.
Status	String	False	The status of the quote.
LineItem_LineItemId	String	False	The Xero generated identifier for a line item.
LineItem_Description	String	False	The description of the line item.
LineItem_Quantity	Double	False	The quantity of the associated item in the line item.
LineItem_UnitAmount	Decimal	False	The unit amount of the associated item in the line item.
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.
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_DiscountRate	Double	False	The discount percentage being applied to the line item.
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_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.
QuoteNumber	String	False	The unique, alphanumeric code identifying the quote.
LineAmountTypes	String	False	This field specifies whether line amounts include tax. The valid values are Exclusive, Inclusive, and NoTax.
Reference	String	False	An additional reference number for ACCREC-type invoices.
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.
SubTotal	Decimal	False	The subtotal for the invoice, which excludes taxes.
TotalTax	Decimal	False	The total tax for the invoice.
Total	Decimal	False	The total for the invoice, including tax -- the sum of SubTotal and TotalTax.
TotalDiscount	Decimal	False	Total of discounts applied on the invoice line items.
BrandingThemeId	String	False	The Id of the branding theme.
Terms	String	False	The terms of the quote.
UpdatedDateUTC	Datetime	False	The date when the invoice was last updated.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant
LineItemAggregate	String	False	Used to define Quote LineItem rows using XML values. Should be provided on INSERT only.

Receipts

Query, insert and update 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 Sync App; 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

To insert a single row, set the required fields. A new table object is 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.

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

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

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

In addition to inserting a single row, this table offers two additional ways to insert into a table.

You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the TaxComponent columns without the "TaxComponent_" prefix.

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

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

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

UPDATE

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

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

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

DELETE

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

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

Columns


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

ビュー

ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。

クエリは、ビューに対して通常のテーブルと同様に実行することができます。

Xero Connector for CData Sync ビュー


Name	Description
AgedPayablesByContact	Query Aged Payables for a Contact.
AgedReceivablesByContact	Query Aged Receivables for a Contact.
BalanceSheet	Query a monthly Balance Sheet.
BankStatement	Query the transactions listed on the account.
BankSummary	Query the balances and cash movements for each bank account.
BrandingThemes	Query 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	Query a monthly Budget Summary.
ContactCISSettings	Query CIS settings for contacts in a Xero organisation.
CreditNoteAllocations	Query credit note allocations for a Xero organisation.
Currencies	Query the Currencies for a Xero organisation.
ExecutiveSummary	Query a summary of changes between a month and the previous month
ExpenseClaimPayments	Query payments for a Xero organisation.
ExpenseClaimReceipts	Query claimed receipts for a Xero organisation.
HistoryBankTransfers	Query BankTransfer history and notes for a Xero organization.
HistoryExpenseClaims	Query ExpenseClaim history and notes for a Xero organization.
HistoryItems	Query Item history and notes for a Xero organization.
HistoryOverpayments	Query Overpayment history and notes for a Xero organization.
HistoryPayments	Query Payment history and notes for a Xero organization.
HistoryPrepayments	Query Prepayment history and notes for a Xero organization.
HistoryReceipts	Query Receipt history and notes for a Xero organization.
HistoryRepeatingInvoices	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	Query Overpayments for a Xero organisation.
PrepaymentAllocations	Query prepayment allocations for a Xero organisation.
Prepayments	Query Prepayments for a Xero organisation.
ProfitAndLoss	Query a Profit and Loss report over a date range.
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	Query a monthly Trial Balance.
Users	Query users for a Xero organisation.

AgedPayablesByContact

Query 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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。


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

Query 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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。


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

Query 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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。


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

Query 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	Decimal	The transaction's value
Balance	Decimal	The statement's running total
TenantId	String	The ID of the tenant to query instead of the connection tenant

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。


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

Query 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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。


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

BrandingThemes

Query 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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。


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

Query 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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。


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

Query 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

Query 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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。


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

ExpenseClaimPayments

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 Sync App; 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

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 Sync App; 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

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

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

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

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

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

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

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

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 Sync App; 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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。


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

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

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

Query 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 spans the current month:

SELECT * FROM ProfitAndLoss

2. When just FromDate is specified, the report spans from FromDate to the current date:

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

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

Note: FromDate must be within a year of ToDate.

4. When Periods and Timeframe are specified, the report spans from the current date to the start of the specified Timeframe:

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

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

SELECT * FROM ProfitAndLoss WHERE Periods = 11 AND Timeframe = 'MONTH' AND FromDate= '2023-10-01' AND ToDate= '2023-10-31'

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

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

Note: The report start date is set as the first of the current month. ToDate must be set as a later date, within 365 days of the first of the current month.

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。


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.

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 Sync App; 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 Sync App; 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

Query 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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。


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.
EmailAddress	String	Email address 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

オーストラリア給与データモデル

The CData Sync App 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

テーブル describes the available tables.

Views

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

Stored Procedures

ストアドプロシージャ are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

テーブル

Sync App はXero のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。

Xero Connector for CData Sync テーブル


Name	Description
Employees	Query, insert and update employees for a Xero organisation.
LeaveApplications	Query, insert and update Leave Applications for a Xero organisation.
PayItemsDeductions	Query, insert and update PayItems for a Xero organisation.
PayItemsEarnings	Query, insert and update PayItems for a Xero organisation.
PayItemsLeave	Query, insert and update PayItems for a Xero organisation.
PayItemsReimbursements	Query, insert and update PayItems for a Xero organisation.
PayrollCalendars	Query, insert and update Payroll Calendars for a Xero organisation.
PayRuns	Query, insert and update payruns for 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.
PaySlipReimbursements	Query payslip reimbursements line-items 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.
SuperFunds	Query, insert and update add and update Payroll Super Funds in a Xero organisation.
Timesheets	Query, insert and update Timesheets for a Xero organisation.

Employees

Query, insert and update 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

Query, insert and update 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 Sync App; 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

To insert a single row, set the required fields. A new table object is created as a single entry. To insert a new leave application, the EmployeeID, LeaveTypeID, Title, StartDate and EndDate are required. In addition leave periods may also be provided.

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

You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the Line columns with the LeavePeriods_LeavePeriod_ prefix removed.

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

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

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

UPDATE

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

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

DELETE

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

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

Columns


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

PayItemsDeductions

Query, insert and update 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

Query, insert and update 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

Query, insert and update 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

Query, insert and update 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

Query, insert and update 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

Query, insert and update 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

PaySlipDeductions

Query payslip deduction line-items for a Xero organisation.

Table Specific Information

This table supports the following operators server-side: =,IN

INSERT

To insert a single row, set the required fields. The Sync App adds a new deduction line to the payslip.

INSERT INTO PaySlipDeductions (PayslipID, DeductionLine_DeductionTypeId, DeductionLine_CalculationType, DeductionLine_Amount) VALUES ('ea5aaaa7-c330-41d8-bfae-98ddc1f37680', 'b78b5ddd-6a20-4992-8e77-1243a122265d', 'FIXEDAMOUNT', 50)

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.

UPDATE PaySlipDeductions SET DeductionLine_Amount=5 WHERE Id='2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

DELETE

Supply the Id to delete payslip deduction lines.

DELETE FROM PaySlipDeductions WHERE Id = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	String	True		The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID	String	False		The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID	String	True		The unique identifier of the payslip's employee.
DeductionLine_DeductionTypeId	String	False		The unique identifier of the line-item's deduction type
DeductionLine_CalculationType	String	False		How the deduction is applied to earnings, either PRETAX, POSTTAX or FIXEDAMOUNT
DeductionLine_Amount	Decimal	False		The total amount of the deduction
DeductionLine_Percentage	Double	False		The percentage of income this deduction applies to
TenantId	String	False		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

This table supports the following operators server-side: =,IN

INSERT

To insert a single row, set the required fields. The Sync App adds a new earnings line to the payslip.

INSERT INTO PaySlipEarnings (PaySlipID, EarningsLine_EarningsRateId, EarningsLine_RatePerUnit, EarningsLine_NumberOfUnits) VALUES ('ea5aaaa7-c330-41d8-bfae-98ddc1f37680', 'fc394b8d-ff2e-4f10-992b-2a41390ad2c4', 4, 20)

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.

UPDATE PaySlipEarnings SET EarningsLine_RatePerUnit = 5 WHERE Id = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

DELETE

Supply the Id to delete payslip earnings lines.

DELETE FROM PaySlipEarnings WHERE Id = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

Columns


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

This table supports the following operators server-side: =,IN

INSERT

To insert a single row, set the required fields. The Sync App adds a new leave accrual line to the payslip.

INSERT INTO PaySlipLeaveAccrual (PaySlipID, LeaveAccrualLine_LeaveTypeId, LeaveAccrualLine_NumberOfUnits, LeaveAccrualLine_AutoCalculate) VALUES ('ea5aaaa7-c330-41d8-bfae-98ddc1f37680', '73f37030-b1ed-45fe-b7a2-f704a3a28ad4', 8, false)

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.

UPDATE PaySlipLeaveAccrual SET LeaveAccrualLine_NumberOfUnits = 5 WHERE Id = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

DELETE

Supply the Id to delete leave accrual lines.

DELETE FROM PaySlipLeaveAccrual WHERE Id = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'
PaySlipReimbursements

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	String	True		The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID	String	False		The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID	String	True		The unique identifier of the payslip's employee.
LeaveAccrualLine_LeaveTypeId	String	False		The unique identifier of the line-item's leave type
LeaveAccrualLine_NumberOfUnits	Double	False		How much leave time was added
LeaveAccrualLine_AutoCalculate	String	False		Whether or not to automatically calculate leave
TenantId	String	False		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

This table supports the following operators server-side: =,IN

INSERT

To insert a single row, set the required fields. The Sync App adds a new reimbursement line to the payslip.

INSERT INTO PaySlipReimbursements (PaySlipID, ReimbursementLine_ReimbursementTypeId, ReimbursementLine_Amount) VALUES ('ea5aaaa7-c330-41d8-bfae-98ddc1f37680', '3432120f-4d83-4bc2-8b5d-5a116838229e', 5)

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.

UPDATE PaySlipReimbursements SET ReimbursementLine_Amount = 6 WHERE Id = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

DELETE

Supply the Id to delete payslip reimbursement lines.

DELETE FROM PaySlipReimbursements WHERE Id = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	String	True		The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID	String	False		The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID	String	True		The unique identifier of the payslip's employee.
ReimbursementLine_ReimbursementTypeId	String	False		The unique identifier of the line-item's reimbursement type
ReimbursementLine_Description	String	False		A description of the reimbursement
ReimbursementLine_ExpenseAccount	String	False		The account the reimbursement is paid from
ReimbursementLine_Amount	Decimal	False		The amount of the reimbursement
TenantId	String	False		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

This table supports the following operators server-side: =,IN

INSERT

To insert a single row, set the required fields. The Sync App adds a new superannuation line to the payslip.

  INSERT INTO PaySlipSuperannuations (PayslipID, SuperannuationLine_SuperMembershipId, SuperannuationLine_ContributionType, SuperannuationLine_CalculationType, SuperannuationLine_Amount, SuperannuationLine_ExpenseAccountCode, SuperannuationLine_LiabilityAccountCode, SuperannuationLine_Percentage, SuperannuationLine_PaymentDateForThisPeriod) VALUES ('ea5aaaa7-c330-41d8-bfae-98ddc1f37680', '045ef1ba-1399-4b49-b324-8c8b70d05607', 'SGC', 'PERCENTAGEOFEARNINGS', 9, '478', '826', 9.0, '2023-06-28')

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.

UPDATE PaySlipSuperannuations SET SuperannuationLine_Percentage = 10 WHERE Id = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

DELETE

Supply the Id to delete payslip superannuation lines.

 DELETE FROM PaySlipSuperannuations WHERE Id = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

Columns


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

This table supports the following operators server-side: =,IN

INSERT

To insert a single row, set the required fields. The Sync App adds a new tax line to the payslip.

INSERT INTO PaySlipTaxes (PayslipID, TaxLine_Amount) VALUES ('ea5aaaa7-c330-41d8-bfae-98ddc1f37680', 5)

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.

UPDATE PaySlipTaxes SET TaxLine_Amount = 10 WHERE Id = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

DELETE

Supply the Id to delete payslip tax lines.

DELETE FROM PaySlipTaxes WHERE Id = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	String	True		The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID	String	False		The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID	String	True		The unique identifier of the payslip's employee.
TaxLine_TaxTypeName	String	False		The name of the line-item's tax type
TaxLine_Description	String	False		The description of the tax
TaxLine_Amount	Decimal	False		The tax's amount
TaxLine_LiabilityAccount	String	False		The liability account used to pay the tax
TenantId	String	False		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

This table supports the following operators server-side: =,IN

INSERT

To insert a single row, set the required fields. The Sync App adds a new timesheet earnings line to the payslip.

INSERT INTO PaySlipTimesheetEarnings (PayslipID, TimesheetEarningsLine_EarningsRateId, TimesheetEarningsLine_RatePerUnit, TimesheetEarningsLine_NumberOfUnits) VALUES ('ea5aaaa7-c330-41d8-bfae-98ddc1f37680', 'c8602c7c-5f6f-433e-b7a7-bcbbd8386818', 32, 10)

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.

UPDATE PaySlipTimesheetEarnings SET TimesheetEarningsLine_NumberOfUnits = 5 WHERE Id = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

DELETE

Supply the Id to delete timesheet earnings lines.

DELETE FROM PaySlipTimesheetEarnings WHERE Id = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

Columns


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

SuperFunds

Query, insert and update 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

Query, insert and update 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 Sync App; 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

To insert a single row, set the required fields. A new table object is created as a single entry. To insert a new timesheet, the EmployeeID, StartDate and EndDate fields are required.

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

You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the Line columns with the Line_ prefix removed, except when providing numbers of units. See the Xero documentation for the Timesheets API endpoint for more details.

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

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

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

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

UPDATE

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

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

DELETE

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

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

Columns


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

ビュー

ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。

クエリは、ビューに対して通常のテーブルと同様に実行することができます。

Xero Connector for CData Sync ビュー


Name	Description
EmployeeBankAccounts	Query the bank accounts for an Employee in a Xero organisation.
EmployeePayTemplateDeductions	Query the pay template deduction lines for an Employee in a Xero organisation.
EmployeePayTemplateEarnings	Query the pay template earnings lines for an Employee in a Xero organisation.
EmployeePayTemplateLeave	Query the pay template leave lines for an Employee in a Xero organisation.
EmployeePayTemplateReimbursements	Query the pay template reimbursement lines for an Employee in a Xero organisation.
EmployeePayTemplateSupers	Query the pay template super lines for an Employee in a Xero organisation.
LeaveBalances	Query the Leave Balance for an Employee in a Xero organisation.
PaySlipLeaveEarnings	Query payslip leave earnings line-items for a Xero organisation.
PaySlips	Query payslips for a Xero organisation.
Tenants	Query the list of organisations connected to your Xero account.

EmployeeBankAccounts

Query 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

Query 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

Query 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

Query 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

Query 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

Query 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

Query 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

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

PaySlips

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

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

ファイルデータモデル

The CData Sync App 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

テーブル describes the available tables.

Stored Procedures

ストアドプロシージャ are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

テーブル

Sync App はXero のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。

Xero Connector for CData Sync テーブル


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

Associations

Query, insert 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

Query, insert, 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

ビュー

ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。

クエリは、ビューに対して通常のテーブルと同様に実行することができます。

Xero Connector for CData Sync ビュー


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

固定資産データモデル

The CData Sync App 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

テーブル describes the available tables.

Views

ビュー describes the available views.

Stored Procedures

ストアドプロシージャ are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

テーブル

Sync App はXero のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。

Xero Connector for CData Sync テーブル


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

Assets

Query, insert and update assets for a Xero organization.

Table-Specific Information

Creating Assets

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

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

Updating Assets

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

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

Registering Assets

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

AssetTypeId
PurchaseDate
PurchasePrice
BookDepreciationSetting_DepreciationStartDate
BookDepreciationSetting_AveragingMethod
Either a BookDepreciationSetting_DepreciationRate or a BookDepreciationSetting_EffectiveLifeYears

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

Disposing Assets

Disposing of assets is not supported through the API.

Columns


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

AssetTypes

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

Table-Specific Information

Creating Asset Types

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

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

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

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

Updating Asset Types

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

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

Columns


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

ビュー

ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。

クエリは、ビューに対して通常のテーブルと同様に実行することができます。

Xero Connector for CData Sync ビュー


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

プロジェクトデータモデル

The CData Sync App 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

テーブル describes the available tables.

Views

ビュー describes the available views.

Stored Procedures

ストアドプロシージャ are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

テーブル

Sync App はXero のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。

Xero Connector for CData Sync テーブル


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

Projects

Query, insert 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, insert, 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, insert, 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

ビュー

ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。

クエリは、ビューに対して通常のテーブルと同様に実行することができます。

Xero Connector for CData Sync ビュー


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

接続文字列オプション

接続文字列プロパティは、接続を確立するために使用できるさまざまなオプションです。このセクションでは、本プロバイダーの接続文字列で設定できるオプションの一覧を示します。詳細については各リンクをクリックしてください。

接続を確立する方法について詳しくは、接続の確立を参照してください。

Authentication

プロパティ	説明
AuthScheme	The type of authentication to use when connecting to Xero.
Schema	The Xero API you want to access as a database schema.
Tenant	Sets the Xero organization to connect to. Can be a name or tenant ID.

OAuth

プロパティ	説明
OAuthClientId	OAuth 認証サーバーを使用してアプリケーションを登録する場合に割り当てられたクライアントId。
OAuthClientSecret	OAuth 認証サーバーにアプリケーションを登録する場合に割り当てられたクライアントシークレット。
OAuthAccessToken	OAuth を使用して接続するためのアクセストークン。

SSL

プロパティ	説明
SSLServerCert	TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。

Firewall

プロパティ	説明
FirewallType	プロキシベースのファイアウォールで使われるプロトコル。
FirewallServer	プロキシベースのファイアウォールの名前もしくはIP アドレス。
FirewallPort	プロキシベースのファイアウォールのTCP ポート。
FirewallUser	プロキシベースのファイアウォールに認証するために使うユーザー名。
FirewallPassword	プロキシベースのファイアウォールへの認証に使われるパスワード。

Proxy

プロパティ	説明
ProxyAutoDetect	これは、システムプロキシ設定を使用するかどうかを示します。
ProxyServer	HTTP トラフィックをルートするためのプロキシのホストネームもしくはIP アドレス。
ProxyPort	ProxyServer プロキシが起動しているTCP ポート。
ProxyAuthScheme	ProxyServer プロキシへの認証で使われる認証タイプ。
ProxyUser	ProxyServer プロキシへの認証に使われるユーザー名。
ProxyPassword	ProxyServer プロキシへの認証に使われるパスワード。
ProxySSLType	ProxyServer プロキシへの接続時に使用するSSL タイプ。
ProxyExceptions	ProxyServer 経由での接続が免除される宛先ホスト名またはIP のセミコロン区切りのリスト。

Logging

プロパティ	説明
LogModules	ログファイルに含めるコアモジュール。

Schema

プロパティ	説明
Location	テーブル、ビュー、およびストアドプロシージャを定義するスキーマファイルを格納するディレクトリへのパス。
BrowsableSchemas	このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。
Tables	このプロパティは、使用可能なテーブルのサブセットにレポートされるテーブルを制限します。例えば、Tables=TableA,TableB,TableC です。
Views	使用可能なテーブルのサブセットにレポートされるビューを制限します。例えば、Views=ViewA,ViewB,ViewC です。

Miscellaneous

プロパティ	説明
AutoExpandDetails	Set this property to true automatically include extra details on BankTransfers, ContactGroups, ExpenseClaims, ExpenseClaimPayments, ExpenseClaimReceipts and Receipts.
IncludeArchived	Set this property to include archived records
MaxRows	クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。
Other	これらの隠しプロパティは特定のユースケースでのみ使用されます。
PseudoColumns	このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。
RetryDailyLimit	Whether to wait when Xero reports that the provider has hit its daily limit.
RetryWaitTime	How long to wait when a request hits the Xero API limit, in milliseconds.
Timeout	タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。
UserDefinedViews	カスタムビューを含むJSON コンフィギュレーションファイルを指すファイルパス。

Authentication

このセクションでは、本プロバイダーの接続文字列で設定可能なAuthentication プロパティの全リストを提供します。

プロパティ	説明
AuthScheme	The type of authentication to use when connecting to Xero.
Schema	The Xero API you want to access as a database schema.
Tenant	Sets the Xero organization to connect to. Can be a name or tenant ID.

AuthScheme

The type of authentication to use when connecting to Xero.

解説

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.
OAuthClient: Authenticates you using client credentials with a client ID and secret. Can only be used if you have purchased a Custom Connection license from Xero.
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.

解説

The Sync App 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.

解説

Xero apps may be authorized by for multiple organizatins at once. By default the Sync App 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

このセクションでは、本プロバイダーの接続文字列で設定可能なOAuth プロパティの全リストを提供します。

プロパティ	説明
OAuthClientId	OAuth 認証サーバーを使用してアプリケーションを登録する場合に割り当てられたクライアントId。
OAuthClientSecret	OAuth 認証サーバーにアプリケーションを登録する場合に割り当てられたクライアントシークレット。
OAuthAccessToken	OAuth を使用して接続するためのアクセストークン。

OAuthClientId

OAuth 認証サーバーを使用してアプリケーションを登録する場合に割り当てられたクライアントId。

解説

OAuth アプリケーションの登録の一環として、コンシューマキーとも呼ばれるOAuthClientId 値、およびクライアントシークレットOAuthClientSecret が提供されます。

OAuthClientSecret

OAuth 認証サーバーにアプリケーションを登録する場合に割り当てられたクライアントシークレット。

解説

OAuth アプリケーションの登録の一環として、コンシューマキーとも呼ばれるOAuthClientId が提供されます。また、コンシューマーシークレットと呼ばれるクライアントシークレットも提供されます。クライアントシークレットをOAuthClientSecret プロパティに設定します。

OAuthAccessToken

OAuth を使用して接続するためのアクセストークン。

解説

OAuth を使用して接続するには、OAuthAccessToken プロパティが使用されます。認証プロセスにおいてOAuth サーバーからOAuthAccessToken が取得されます。このプロパティは、サーバーに依存するタイムアウトがあり、要求の間で再利用することができます。

アクセストークンは、ユーザー名とパスワードの代わりに使用されます。サーバー上で維持することで、認証情報が保護されます。

SSL

このセクションでは、本プロバイダーの接続文字列で設定可能なSSL プロパティの全リストを提供します。

プロパティ	説明
SSLServerCert	TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。

SSLServerCert

TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。

解説

TLS/SSL 接続を使用する場合は、このプロパティを使用して、サーバーが受け入れるTLS/SSL 証明書を指定できます。コンピュータによって信頼されていない他の証明書はすべて拒否されます。

このプロパティは、次のフォームを取ります：


説明	例
フルPEM 証明書（例では省略されています）	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
証明書を保有するローカルファイルへのパス。	C:\cert.cer
公開鍵（例では省略されています）	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
MD5 Thumbprint （hex 値はスペースおよびコロン区切り）	ecadbdda5a1529c58a1e9e09828d70e4
SHA1 Thumbprint （hex 値はスペースおよびコロン区切り）	34a929226ae0819f2ec14b4a3d904f801cbb150d

これを指定しない場合は、マシンが信用するすべての証明書が受け入れられます。

すべての証明書の受け入れを示すには、'*'を使用します。セキュリティ上の理由から、これはお勧めできません。

Firewall

このセクションでは、本プロバイダーの接続文字列で設定可能なFirewall プロパティの全リストを提供します。

プロパティ	説明
FirewallType	プロキシベースのファイアウォールで使われるプロトコル。
FirewallServer	プロキシベースのファイアウォールの名前もしくはIP アドレス。
FirewallPort	プロキシベースのファイアウォールのTCP ポート。
FirewallUser	プロキシベースのファイアウォールに認証するために使うユーザー名。
FirewallPassword	プロキシベースのファイアウォールへの認証に使われるパスワード。

FirewallType

プロキシベースのファイアウォールで使われるプロトコル。

解説

このプロパティは、Sync App がFirewallServer プロキシ経由でトンネルトラフィックを使うためのプロトコルを指定します。デフォルトでは、Sync App はシステムプロキシに接続します。この動作を無効化し次のプロキシタイプのどれかで接続するには、ProxyAutoDetect をfalse に設定します。


タイプ	デフォルトポート	説明
TUNNEL	80	これが設定されている場合、Sync App はXero への接続を開き、プロキシを経由して通信が行われます。
SOCKS4	1080	これが設定されている場合、Sync App はデータをFirewallServer およびFirewallPort で指定されたSOCS 4 プロキシ経由で送信し、接続リクエストが許容されるかどうかを決定します。
SOCKS5	1080	これが設定されている場合、Sync App はデータをFirewallServer およびFirewallPort で指定されたSOCS 5 プロキシ経由で送信します。プロキシに認証が必要な場合には、FirewallUser およびFirewallPassword をプロキシが認識する認証情報に設定します。

HTTP プロキシへの接続には、ProxyServer およびProxyPort ポートを使ってください。HTTP プロキシへの認証には、ProxyAuthScheme、ProxyUser、およびProxyPassword を使ってください。

FirewallServer

プロキシベースのファイアウォールの名前もしくはIP アドレス。

解説

ファイアウォールトラバーサルを許容するために設定するIP アドレス、DNS 名、もしくはプロキシホスト名を指定するプロパティです。プロトコルはFirewallType で指定されます。このプロパティとFirewallServer を使って、SOCKS 経由での接続、もしくはトンネリングが可能です。HTTP プロキシへの接続には、ProxyServer を使用します。

Sync App はデフォルトでシステムプロキシを使うので注意してください。他のプロキシを使う場合には、ProxyAutoDetect をfalse に設定してください。

FirewallPort

プロキシベースのファイアウォールのTCP ポート。

解説

ファイアウォールトラバーサルを許容するために設定するプロキシベースのファイアウォールのTCP ポート。名前もしくはIP アドレスを指定するには、FirewallServer を使います。FirewallType でプロトコルを指定します。

FirewallUser

プロキシベースのファイアウォールに認証するために使うユーザー名。

解説

FirewallUser およびFirewallPassword プロパティは、FirewallType により指定された認証方式に則り、FirewallServer、およびFirewallPort で指定されたプロキシに対しての認証に使われます。

FirewallPassword

プロキシベースのファイアウォールへの認証に使われるパスワード。

解説

このプロパティは、FirewallType により指定された認証メソッドに則り、FirewallServer およびFirewallPort で指定されたプロキシに渡されます。

Proxy

このセクションでは、本プロバイダーの接続文字列で設定可能なProxy プロパティの全リストを提供します。

プロパティ	説明
ProxyAutoDetect	これは、システムプロキシ設定を使用するかどうかを示します。
ProxyServer	HTTP トラフィックをルートするためのプロキシのホストネームもしくはIP アドレス。
ProxyPort	ProxyServer プロキシが起動しているTCP ポート。
ProxyAuthScheme	ProxyServer プロキシへの認証で使われる認証タイプ。
ProxyUser	ProxyServer プロキシへの認証に使われるユーザー名。
ProxyPassword	ProxyServer プロキシへの認証に使われるパスワード。
ProxySSLType	ProxyServer プロキシへの接続時に使用するSSL タイプ。
ProxyExceptions	ProxyServer 経由での接続が免除される宛先ホスト名またはIP のセミコロン区切りのリスト。

ProxyAutoDetect

これは、システムプロキシ設定を使用するかどうかを示します。

解説

これは他のプロキシ設定よりも優先されるため、カスタムプロキシ設定を使用するにはProxyAutoDetect をFALSE に設定する必要があります。

HTTP プロキシへの接続には、ProxyServer を参照してください。SOCKS やトンネリングなどの他のプロキシには、FirewallType を参照してください。

ProxyServer

HTTP トラフィックをルートするためのプロキシのホストネームもしくはIP アドレス。

解説

HTTP トラフィックをルートするためのプロキシのホストネームもしくはIP アドレス。HTTP プロキシへの認証には、Sync App はHTTP、Windows(NTLM)、もしくはKerberos 認証タイプを使用することができます。

SOCKS プロキシを経由して接続する、もしくは接続をトンネルするには、FirewallType を参照してください。

デフォルトで、Sync App はsystem プロキシを使います。他のプロキシを使う場合には、ProxyAutoDetect をfalse に設定します。

ProxyPort

ProxyServer プロキシが起動しているTCP ポート。

解説

HTTP トラフィックをリダイレクトするHTTP プロキシが実行されているポート。ProxyServer でHTTP プロキシを指定します。その他のプロキシタイプについては、FirewallType を参照してください。

ProxyAuthScheme

ProxyServer プロキシへの認証で使われる認証タイプ。

解説

この値は、ProxyServer およびProxyPort で指定されるHTTP プロキシに認証するために使われる認証タイプを指定します。

Sync App は、デフォルトでsystem proxy settings を使い、追加での設定が不要です。他のプロキシへの接続をする場合には、ProxyServer およびProxyPort に加え、ProxyAutoDetect をfalse に設定します。認証するには、ProxyAuthScheme を設定し、必要な場合にはProxyUser およびProxyPassword を設定します。

認証タイプは、次のどれかになります。

BASIC： Sync App はHTTP BASIC 認証を行います。
DIGEST： Sync App はHTTP DIGEST 認証を行います。
NEGOTIATE： Sync App は認証において有効なプロトコルに応じて、NTLM もしくはKerberos トークンを取得します。
PROPRIETARY： Sync App はNTLM もしくはKerberos トークンを発行しません。このトークンを、HTTP リクエストのAuthorization ヘッダーに含める必要があります。

SOCKS 5 認証のような他の認証タイプを使用するには、FirewallType を参照してください。

ProxyUser

ProxyServer プロキシへの認証に使われるユーザー名。

解説

ProxyUser および ProxyPassword オプションは、ProxyServer で指定されたHTTP プロキシに対して接続および認証するために使用されます。

ProxyAuthScheme で使用可能な認証タイプを選択することができます。HTTP 認証を使う場合、これをHTTP プロキシで識別可能なユーザーのユーザー名に設定します。Windows もしくはKerberos 認証を使用する場合、このプロパティを次の形式のどれかでユーザー名に設定します。

user@domain
domain\user

ProxyPassword

ProxyServer プロキシへの認証に使われるパスワード。

解説

このプロパティは、NTLM(Windows)、Kerberos、もしくはHTTP 認証をサポートするHTTP プロキシサーバーに認証するために使われます。HTTP プロキシを指定するためには、ProxyServer およびProxyPort を設定します。認証タイプを指定するためにはProxyAuthScheme を設定します。

HTTP 認証を使う場合、さらにHTTP プロキシにProxyUser およびProxyPassword を設定します。

NTLM 認証を使う場合、Windows パスワードにProxyUser およびProxyPassword を設定します。Kerberos 認証には、これらを入力する必要があります。

SOCKS 5 認証もしくは、トンネリングは、FirewallType を参照してください。

デフォルトで、Sync App はsystem プロキシを使います。他のプロキシに接続する場合には、これをfalse に設定します。

ProxySSLType

ProxyServer プロキシへの接続時に使用するSSL タイプ。

解説

このプロパティは、ProxyServer で指定されたHTTP プロキシへの接続にSSL を使用するかどうかを決定します。この値は、AUTO、ALWAYS、NEVER、TUNNEL のいずれかです。有効な値は次のとおりです。


AUTO	デフォルト設定。URL がHTTPS URL の場合、Sync App は、TUNNEL オプションを使います。URL がHTTP URL の場合、コンポーネントはNEVER オプションを使います。
ALWAYS	接続は、常にSSL 有効となります。
NEVER	接続は、SSL 有効になりません。
TUNNEL	接続は、トンネリングプロキシを経由します。プロキシサーバーがリモートホストへの接続を開き、プロキシを経由して通信が行われます。

ProxyExceptions

ProxyServer 経由での接続が免除される宛先ホスト名またはIP のセミコロン区切りのリスト。

解説

ProxyServer は、このプロパティで定義されたアドレスを除くすべてのアドレスに使用されます。セミコロンを使用してエントリを区切ります。

Sync App は、追加設定なしにデフォルトでシステムのプロキシ設定を使います。この接続のプロキシ例外を明示的に構成するには、ProxyAutoDetect をfalse に設定して、ProxyServer およびProxyPort を設定する必要があります。認証するには、ProxyAuthScheme を設定し、必要な場合にはProxyUser およびProxyPassword を設定します。

Logging

このセクションでは、本プロバイダーの接続文字列で設定可能なLogging プロパティの全リストを提供します。

プロパティ	説明
LogModules	ログファイルに含めるコアモジュール。

LogModules

ログファイルに含めるコアモジュール。

解説

指定された（';' で区切られた）モジュールのみがログファイルに含まれます。デフォルトではすべてのモジュールが含まれます。

概要はログページを参照してください。

Schema

このセクションでは、本プロバイダーの接続文字列で設定可能なSchema プロパティの全リストを提供します。

プロパティ	説明
Location	テーブル、ビュー、およびストアドプロシージャを定義するスキーマファイルを格納するディレクトリへのパス。
BrowsableSchemas	このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。
Tables	このプロパティは、使用可能なテーブルのサブセットにレポートされるテーブルを制限します。例えば、Tables=TableA,TableB,TableC です。
Views	使用可能なテーブルのサブセットにレポートされるビューを制限します。例えば、Views=ViewA,ViewB,ViewC です。

Location

テーブル、ビュー、およびストアドプロシージャを定義するスキーマファイルを格納するディレクトリへのパス。

解説

Sync App のスキーマファイル（テーブルとビューの場合は.rsd ファイル、ストアドプロシージャの場合は.rsb ファイル）を含むディレクトリへのパス。このフォルダの場所は、実行ファイルの場所からの相対パスにすることができます。Location プロパティは、定義をカスタマイズしたり（例えば、カラム名を変更する、カラムを無視するなど）、新しいテーブル、ビュー、またはストアドプロシージャでデータモデルを拡張する場合にのみ必要です。

Note： このSync App は複数のスキーマをサポートするため、Xero カスタムスキーマファイルの構造は以下のようになります。

各スキーマには、そのスキーマ名に対応するフォルダが作られます。
これらのスキーマフォルダは親フォルダに含まれています。
親フォルダは、個々のスキーマのフォルダではなく、Location として設定する必要があります。

指定しない場合、デフォルトの場所は"%APPDATA%\\CData\\Xero Data Provider\\Schema" となり、%APPDATA% はユーザーのコンフィギュレーションディレクトリに設定されます：


Platform	%APPDATA%
Windows	APPDATA 環境変数の値
Linux	~/.config

BrowsableSchemas

このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。

解説

スキーマをデータベースからリストすると、負荷がかかる可能性があります。接続文字列でスキーマのリストを提供すると、パフォーマンスが向上します。

Tables

このプロパティは、使用可能なテーブルのサブセットにレポートされるテーブルを制限します。例えば、Tables=TableA,TableB,TableC です。

解説

テーブルを複数のデータベースからリストすると、負荷がかかる可能性があります。接続文字列でテーブルのリストを提供すると、Sync App のパフォーマンスが向上します。

このプロパティは、作業したいビューがすでにわかっていて、ビューが多すぎる場合に、ビューを自動的にリストする代わりに使用することもできます。

カンマ区切りのリストで使用したいテーブルを指定します。各テーブルは、角かっこ、二重引用符、またはバッククオートを使用してエスケープされた特殊文字列を含む有効なSQL 識別子である必要があります。例えば、Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space` です。

複数のスキーマまたはカタログを持つデータソースに接続する場合は、複数のカタログやスキーマに存在するテーブル間の曖昧さを避けるため、最後の例のように、このプロパティにテーブルの完全修飾名を指定する必要があることに注意してください。

Views

使用可能なテーブルのサブセットにレポートされるビューを制限します。例えば、Views=ViewA,ViewB,ViewC です。

解説

ビューを複数のデータベースからリストすると、負荷がかかる可能性があります。接続文字列でビューのリストを提供すると、Sync App のパフォーマンスが向上します。

このプロパティは、作業したいビューがすでにわかっていて、ビューが多すぎる場合に、ビューを自動的にリストする代わりに使用することもできます。

カンマ区切りのリストで使用したいビューを指定します。各ビューは、角かっこ、二重引用符、またはバッククオートを使用してエスケープされた特殊文字列を含む有効なSQL 識別子である必要があります。例えば、Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space` です。

複数のスキーマまたはカタログを持つデータソースに接続する場合は、複数のカタログやスキーマに存在するテーブル間の曖昧さを避けるため、最後の例のように、このプロパティにテーブルの完全修飾名を指定する必要があることに注意してください。

Miscellaneous

このセクションでは、本プロバイダーの接続文字列で設定可能なMiscellaneous プロパティの全リストを提供します。

プロパティ	説明
AutoExpandDetails	Set this property to true automatically include extra details on BankTransfers, ContactGroups, ExpenseClaims, ExpenseClaimPayments, ExpenseClaimReceipts and Receipts.
IncludeArchived	Set this property to include archived records
MaxRows	クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。
Other	これらの隠しプロパティは特定のユースケースでのみ使用されます。
PseudoColumns	このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。
RetryDailyLimit	Whether to wait when Xero reports that the provider has hit its daily limit.
RetryWaitTime	How long to wait when a request hits the Xero API limit, in milliseconds.
Timeout	タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。
UserDefinedViews	カスタムビューを含むJSON コンフィギュレーションファイルを指すファイルパス。

AutoExpandDetails

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

解説

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 include True and False.

MaxRows

クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。

解説

クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。

Other

これらの隠しプロパティは特定のユースケースでのみ使用されます。

解説

以下にリストされているプロパティは、特定のユースケースで使用可能です。通常のドライバーのユースケースおよび機能では、これらのプロパティは必要ありません。

複数のプロパティをセミコロン区切りリストで指定します。

統合およびフォーマット


DefaultColumnSize	データソースがメタデータにカラムの長さを提供しない場合に、文字列フィールドのデフォルトの長さを設定します。デフォルト値は2000です。
ConvertDateTimeToGMT	日時の値を、マシンのローカルタイムではなくGMT グリニッジ標準時に変換するかどうかを決定します。
RecordToFile=filename	基底のソケットデータ転送を指定のファイルに記録します。

PseudoColumns

このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。

解説

Entity Framework ではテーブルカラムでない疑似カラムに値を設定できないため、この設定はEntity Framework で特に便利です。この接続設定の値は、"Table1=Column1, Table1=Column2, Table2=Column3" の形式です。"*=*" のように"*" 文字を使用して、すべてのテーブルとすべてのカラムを含めることができます。

RetryDailyLimit

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

解説

Use of this option is strongly discouraged. See Xero API の制限 for more details.

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

RetryWaitTime

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

解説

When Sync App issues more requests then Xero allows, this option determines how long the Sync App 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

タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。

解説

Timeout が0に設定されている場合は、操作がタイムアウトしません。処理が正常に完了するか、エラー状態になるまで実行されます。

Timeout の有効期限が切れても処理が完了していない場合は、Sync App は例外をスローします。

UserDefinedViews

カスタムビューを含むJSON コンフィギュレーションファイルを指すファイルパス。

解説

ユーザー定義ビューは、UserDefinedViews.json というJSON 形式のコンフィギュレーションファイルで定義されています。Sync App は、このファイルで指定されたビューを自動的に検出します。

また、複数のビュー定義を持ち、UserDefinedViews 接続プロパティを使用して制御することも可能です。このプロパティを使用すると、指定されたビューのみがSync App によって検知されます。

このユーザー定義ビューのコンフィギュレーションファイルは、次のようにフォーマットされています。

各ルートエレメントはビューの名前を定義します。
各ルートエレメントには、query と呼ばれる子エレメントが含まれており、この子エレメントにはビューのカスタムSQL クエリが含まれています。

次に例を示します。

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

UserDefinedViews 接続プロパティを使用して、JSON コンフィギュレーションファイルの場所を指定します。次に例を示します。

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

指定されたパスは引用符で囲まれていないことに注意してください。

Xero Connector for CData Sync

Xero Connector for CData Sync

概要

Xero バージョンサポート

接続の確立

Xero への接続を追加

Connecting to Xero

Connecting to a Xero API

Xero API の制限

Xero API Limits

Concurrent Limit

Per-Minute Limit

Uncertified Connectors Limit

Per-Day Limit

高度な機能

ユーザー定義ビュー

SSL の設定

ファイアウォールとプロキシ

クエリ処理

ログ

SSL の設定

SSL 設定のカスタマイズ

ファイアウォールとプロキシ

Firewall またはProxy 経由の接続

HTTP プロキシ

その他のプロキシ

データモデル

Using the Accounting API

Using the Australian Payroll API

Using the Files API

Using the Fixed Assets API

Using the Projects API

会計データモデル

Tables

Views

Stored Procedures

テーブル

Xero Connector for CData Sync テーブル

Accounts

Table Specific Information

SELECT

INSERT

Columns

BankTransactions

Table Specific Information

SELECT

INSERT

UPDATE

Delete

Columns

BankTransfers

Table Specific Information

SELECT

INSERT

Columns

BatchPayments

Table Specific Information

SELECT

INSERT

Columns

ContactGroups

Table Specific Information

SELECT

INSERT

UPDATE

DELETE

Columns

Contacts

Table Specific Information

SELECT

INSERT

UPDATE

Columns

CreditNotes

Table Specific Information

SELECT

INSERT

UPDATE

DELETE

Columns