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