ODBC Driver for Xero

Build 22.0.8479

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 本製品; the value of this field combines the index of the line item with the unique, Xero-generated CreditNoteId.

SELECT

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

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

INSERT

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

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

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

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

Update

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

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

Delete

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

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

Columns

Name Type ReadOnly Description
Id [KEY] String True

The line item index combined with the Id of the credit note.

CreditNoteId String False

The Id of the credit note.

CreditNoteNumber String False

A unique identifier for the credit note that identifies it to the user.

Reference String False

An optional field to store a reference.

Type String False

The credit note type. The valid values are ACCPAYCREDIT and ACCRECCREDIT.

Contact_ContactId String False

The Id of the contact.

Contact_Name String False

The name of the contact.

Date Date False

The date the credit note was created.

Status String False

The status of the credit note.

LineAmountTypes String False

The line amount type, which determines whether line amounts include tax (default). The valid values are Exclusive, Inclusive, and NoTax. Credit notes cannot be approved without this field being defined.

LineItem_LineItemId String False

The Xero generated identifier for a LineItem. If LineItemIDs are not included with line items in an update request then the line items are deleted and recreated.

LineItem_Description String False

The description for the line item of the credit note. This value must be at least one character. Invoices cannot be approved without these fields.

LineItem_Quantity Double False

The quantity of the subject of the line item. This value must be greater than or equal to 0. Invoices cannot be approved without these fields.

LineItem_UnitAmount Decimal False

The unit amount of the subject of the line item. Invoices cannot be approved without these fields.

LineItem_ItemCode String False

The user-defined item code.

LineItem_AccountCode String False

The account code. The account code must be active for the organisation. Invoices cannot be approved without these fields.

LineItem_Item_ItemId String False

The ID of the item the lineitem refers to.

LineItem_Item_Name String False

The name of the item the lineitem refers to.

LineItem_Item_Code String False

The code of the item the lineitem refers to.

LineItem_TaxType String False

The description for the line item of the credit note. This value must be at least one character. Invoices cannot be approved without these fields.

LineItem_TaxAmount Decimal False

The quantity of the subject of the line item. This value must be greater than or equal to 0. Invoices cannot be approved without these fields.

LineItem_LineAmount Decimal False

The total of the Quantity field multiplied by the UnitAmount field with discounts applied.

LineItem_TrackingCategory1_CategoryId String False

The Id of a tracking category. The API allows two optional tracking categories to be defined.

LineItem_TrackingCategory1_CategoryName String False

The name of a tracking category. The API allows two optional tracking categories to be defined.

LineItem_TrackingCategory1_OptionId String False

The Id of a tracking category. The API allows two optional tracking categories to be defined.

LineItem_TrackingCategory1_OptionName String False

The option of a tracking category. The API allows two optional tracking categories to be defined.

LineItem_TrackingCategory1_Option_Stat String False

The status of a tracking category. The API allows two optional tracking categories to be defined.

LineItem_TrackingCategory2_CategoryId String False

The Id of a tracking category. The API allows two optional tracking categories to be defined.

LineItem_TrackingCategory2_CategoryName String False

The name of a tracking category. The API allows two optional tracking categories to be defined.

LineItem_TrackingCategory2_OptionId String False

The Id of a tracking category. The API allows two optional tracking categories to be defined.

LineItem_TrackingCategory2_OptionName String False

The option of a tracking category. The API allows two optional tracking categories to be defined.

LineItem_TrackingCategory2_Option_Stat String False

The status of a tracking category. The API allows two optional tracking categories to be defined.

LineItem_DiscountRate Double False

The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type credit notes.

LineItemAggregate String False

Used to define LineItem rows using XML values. Should be provided on INSERT only.

SubTotal Decimal False

The subtotal of the credit note, excluding taxes.

TotalTax Decimal False

The total tax on the credit note.

Total Decimal False

The total of the credit note (the subtotal plus the total tax).

UpdatedDateUTC Datetime True

The date when the credit note was last updated.

CurrencyCode String False

The currency used for the credit note.

FullyPaidOnDate Date False

The date when the credit note was fully paid.

SentToContact Boolean False

This field indicates whether the credit note has been sent to the contact via the Xero app.

BrandingThemeId String False

The Id of the branding theme.

CurrencyRate Decimal False

Exchange rate to base currency when money is spent or received. For example, 0.7500. This field is used for only bank transactions not in the base currency. If this is not specified for non-base-currency accounts then either the user-defined rate or the XE.com daily rate will be used.

RemainingCredit Decimal False

The remaining credit for this credit note.

Allocation1_AppliedAmount Decimal True

The amount to be applied from this credit note to a given invoice.

Allocation1_Date Date True

The date the allocation was made.

Allocation1_InvoiceId String True

The Id of the invoice this allocation has been applied to.

Allocation1_InvoiceNumber String True

The Invoice this allocation has been applied to.

Allocation2_AppliedAmount Decimal True

The amount to be applied from this credit note to a given invoice.

Allocation2_Date Date True

The date the allocation was made.

Allocation2_InvoiceId String True

The Id of the invoice this allocation has been applied to.

Allocation2_InvoiceNumber String True

The Invoice this allocation has been applied to.

PaymentIds String True

A comma-delimited list of payment Ids associated with the credit notes.

HasAttachments Boolean True

This field indicates whether the invoice has an attachment.

TenantId String False

The ID of the tenant to query instead of the connection tenant

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8479