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 本製品 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 |
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 | False |
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 |