ODBC Driver for Sage Business Cloud Accounting

Build 26.0.9655

PurchaseInvoices

Query PurchaseInvoices in Sage Business Cloud Accounting.

Table Specific Information

Query the available PurchaseInvoices

Select

The driver uses the Sage Accounting API to process search criteria that refer to ContactId,StatusId,ShowPaymentsAllocations,ShowCorrections,ShowPaymentsAllocations,Search,FromDate,ToDate,UpdatedOrCreatedSince,DeletedSince,HasAttachments columns. The driver processes other filters client-side within the driver.

  • ContactId supports the '=' operator.
  • StatusId supports the '=' operator.
  • ShowPaymentsAllocations supports the '=' operator.
  • ShowCorrections supports the '=' operator.
  • ShowPaymentsAllocations supports the '=' operator.
  • Search supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • HasAttachments supports the '=' operator.

For example, the following queries are processed server side: 

SELECT * FROM PurchaseInvoices WHERE ContactId = 'abc'
SELECT * FROM PurchaseInvoices WHERE StatusId = 'abc'
SELECT * FROM PurchaseInvoices WHERE ShowPaymentsAllocations = 'abc'
SELECT * FROM PurchaseInvoices WHERE ShowCorrections = 'abc'
SELECT * FROM PurchaseInvoices WHERE ShowPaymentsAllocations = 'abc'
SELECT * FROM PurchaseInvoices WHERE Search = 'abc'
SELECT * FROM PurchaseInvoices WHERE FromDate = '2018-12-15'
SELECT * FROM PurchaseInvoices WHERE ToDate = '2018-12-15'
SELECT * FROM PurchaseInvoices WHERE UpdatedOrCreatedSince = '2018-12-15'
SELECT * FROM PurchaseInvoices WHERE DeletedSince = '2018-12-15'
SELECT * FROM PurchaseInvoices WHERE HasAttachments = 'abc'

Insert

Create a simple purchase invoice. 

INSERT INTO [PurchaseInvoices] (ContactName, TotalQuantity, DisplayedAs) VALUES ('test', '1', 'test')

Update

Updates are performed based on Id. 

UPDATE PurchaseInvoices SET Reference = 'abc' WHERE Id = '123'

Delete

You must specify the Id of the PurchaseInvoices to delete it. 

DELETE FROM PurchaseInvoices WHERE id = '123'

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

DisplayedAs String True

The name of the resource

Reference String False

The reference for the invoice

BaseCurrencyTotalAmount String False

The total amount of the invoice in base currency

BaseCurrencyNetAmount String False

The net amount of the invoice in base currency

ContactName String False

The name of the contact when the invoice was created

Migrated Bool True

Indicates if the purchase invoice was migrated from another system.

DueDate Datetime False

The due date of the invoice

TaxAmount String False

The tax amount of the invoice

LastPaid String True

The date of the last payment

PaymentsAllocationsTotalDiscount String True

The total discount of all payments and allocations

WithholdingTaxRate String False

IRPF withheld Tax Rate (Spain only)

TotalAmount String False

The total amount of the invoice

ExchangeRate String False

The exchange rate for the invoice

OutstandingAmount String True

The outstanding amount of the invoice

ContactId String False

Contacts.Id

The unique identifier for the item

NetAmount String False

The net amount of the invoice

ContactReference String False

The reference of the contact when the invoice was created

TaxAddressRegionDisplayedAs String True

The name of the resource

TaxAddressRegionLegacyId Int True

The legacy ID for the item

TaxAddressRegionId String False

The unique identifier for the item

TaxCalculationMethod String True

The tax calculation method, if applicable, for this purchase invoice, returns invoice or cash.

PaymentsAllocationsAggregate String True

The associated payments and allocations

BaseCurrencyWithholdingTaxAmount String False

IRPF withheld Tax Amount (Spain only) in the base currency

WithholdingTaxAmount String False

IRPF withheld Tax Amount (Spain only)

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int True

The legacy ID for the item

TaxAnalysisAggregate String False

The invoice tax analysis (Optional for Spain, restricted for all other regions)

PurchaseCorrectiveInvoiceAggregate String True

The corrective entries associated with the invoice

InverseExchangeRate String False

The inverse exchange rate for the invoice

TotalQuantity String False

The total quantity of the invoice

TransactionId String True

Transactions.Id

The unique identifier for the item

StatusDisplayedAs String True

The name of the resource

StatusLegacyId Int True

The legacy ID for the item

StatusId String False

The unique identifier for the item

DetailedTaxAnalysisBaseCurrencyTotalGoodsAmount String True

The total base currency amount relating to goods for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalTax String True

The base currency total tax amount for all tax rates

DetailedTaxAnalysisTotalRetailerTax String True

The total retailer tax amount for all tax rates

DetailedTaxAnalysisBaseCurrencyTotal String True

The total base currency amount for all tax rates

DetailedTaxAnalysisTotalGoodsAmount String True

The total amount relating to goods for all tax rates

DetailedTaxAnalysisTotalNet String True

The total net amount for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalServicesAmount String True

The total base currency amount relating to services for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalNet String True

The base currency total net amount for all tax rates

DetailedTaxAnalysisTotalServicesAmount String True

The total amount relating to services for all tax rates

DetailedTaxAnalysisTotal String True

The total amount for all tax rates

DetailedTaxAnalysisTotalTax String True

The total tax amount for all tax rates

TransactionTypeId String True

TransactionTypes.Id

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

PaymentsAllocationsTotalAmount String True

The total amount of all payments and allocations

VoidReason String True

The reason the invoice was voided

TaxReconciled Bool True

Indicates if the purchase invoice is tax reconciled or not.

Editable Bool True

Indicates whether artefact can be edited

VendorReference String False

The vendor reference for the invoice

CurrencyId String False

Currencies.Id

The unique identifier for the item

PurchaseInvoiceLineItemAggregate String False

The invoice lines of the invoice

Notes String False

Invoice notes

TotalPaid String True

The total paid amount of the invoice including any payments, allocations and discounts

DeletedAt Datetime True

The datetime when the item was deleted

BaseCurrencyTaxAmount String False

The tax amount of the invoice in base currency

Date String False

The date of the invoice

BaseCurrencyOutstandingAmount String True

The outstanding amount of the invoice in base currency

ShowPaymentsAllocations String False

ShowCorrections String False

Search String False

FromDate Datetime True

ToDate Datetime True

DeletedSince Datetime True

HasAttachments String True

VatExemptConsignment Bool False

indicate when consignment values are under £135 and you have agreed with your supplier that you will account for VAT on your VAT return using Reverse Charge.

IsCIS Bool True

Determines if this is a CIS transaction.

CISApplicableAmount String False

BaseCurrencyCISApplicableAmount String False

TotalAfterCISDeduction String False

BaseCurrencyTotalAfterCISDeduction String False

HasCISLabour Bool True

HasCISMaterials Bool True

Copyright (c) 2026 CData Software, Inc. - All rights reserved.
Build 26.0.9655