BatchPayments

Queries and inserts batch payments in Xero. Batch payments group multiple payments together, making it easier to process large sets of payments to vendors or suppliers.

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 server this value combines the index of the individual payment within the batch with the unique, Xero-generated BatchPaymentId

Optimized Filters and High Volume Thresholds

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

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

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

The Xero API does not list a high volume threshold for the BatchPayments table. The Xero API lists the following optimized filters for this table:

Optimized columns (when used in WHERE clauses):
- Type
- Account.AccountId
- Status
Optimized ORDER BY columns:
- BatchPaymentID
- UpdatedDateUTC
- Date

SELECT

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

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

INSERT

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

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.

In order to delete a record from the BatchPayments table, the BatchPaymentId column should be set:

DELETE FROM BatchPayments WHERE BatchPaymentId='79f60299-7138-4d67-ae3c-50f1d4231dde'

Note: This will only set the 'Status' column to 'DELETED' for the given record. The record will still be visible in an unfiltered result.

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	A unique identifier for the batch payment, combining the batch payment index with the Xero-assigned ID for the batch payment.
BatchPaymentId	String	True	The unique identifier assigned by Xero to the batch payment. This ID links the batch payment to its corresponding record in the system.
AccountId	String	False	The ID of the account from which the payment is made. It must be either a bank account or an account with payments enabled, ensuring the payment can be processed.
AccountCurrencyCode	String	False	The code of the currency used by the account.
Particulars	String	False	(NZ only) The particulars field for New Zealand transactions, representing specific transaction details that appear on the bank statement for the payment.
Code	String	False	(NZ only) The transaction code for New Zealand transactions that appears on the bank statement, providing additional information about the payment type.
Reference	String	False	(NZ only) The transaction reference for New Zealand transactions that appears on the statement, typically used for identification purposes.
Details	String	False	(Non-NZ only) The reference details for non-New Zealand payments that are sent to the organization's bank. This is used to provide context for the bank transaction.
Narrative	String	False	(UK only) A description that appears on the Xero statement line for UK users. This field is only visible within Xero and is not included in the bank statement.
Date	Date	False	The date on which the payment is made. This marks the transaction date for processing and reporting purposes.
Payment_PaymentId	String	True	The unique ID assigned by Xero to the payment made within the batch. This ID links the payment to the corresponding invoice or record.
Payment_InvoiceId	String	False	The unique ID of the invoice that the payment is made against. This ensures that the payment is properly allocated to the correct invoice.
Payment_InvoiceCurrencyCode	String	False	The code of the currency used in the invoice that the payment is made against.
Payment_BankAccountNumber	String	False	The account number of the bank where the payment is being made. This field is used to specify the destination account for the payment.
Payment_BankAmount	Decimal	False	The bank amount of this particular payment line.
Payment_Particulars	String	False	(NZ only) The particulars that appear on the statement for New Zealand transactions, providing a reference to the payment details.
Payment_Code	String	False	(NZ only) The transaction code that will appear on the supplier's bank account for New Zealand transactions, helping to identify the transaction.
Payment_Reference	String	False	(NZ only) The reference that will appear on the supplier's bank account for New Zealand transactions. This helps identify the transaction on the bank statement.
Payment_Details	String	False	The reference details sent to the supplier's bank for non-NZ transactions. This serves as the bank's reference for the transaction.
Payment_Amount	Decimal	False	The amount being paid in this particular payment line. This value represents the monetary value transferred to the recipient.
PaymentAggregate	String	False	Used to define the payment rows with XML values. This is typically provided during an INSERT operation to specify how the payment rows should be structured.
Type	String	True	Specifies the type of batch payment. 'PAYBATCH' refers to bill payments, and 'RECBATCH' refers to sales payments, distinguishing between the payment types in the batch.
Status	String	False	Indicates the current status of the batch payment. Valid values are 'AUTHORISED' (payment is approved) or 'DELETED' (payment has been voided or removed).
TotalAmount	Decimal	True	The total amount of all payments included in the batch. This is the sum of all individual payment amounts within the batch.
IsReconciled	Boolean	True	Indicates whether the batch payment has been reconciled with the bank statement. 'True' means the payment is reconciled, while 'false' means it has not yet been reconciled.
UpdatedDateUTC	Datetime	True	The date and time when the batch payment was last updated. This timestamp reflects the most recent modification made to the payment record.
TenantId	String	False	The unique identifier of the tenant, allowing queries to access data for a specific tenant other than the default connection tenant.