Excel Add-In for Sage Business Cloud Accounting

Build 24.0.9062

SalesInvoices

Query SalesInvoices in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesInvoices

Select

The driver uses the Sage Accounting API to process search criteria that refer to ContactId, StatusId, ShowPaymentsAllocations, ShowCorrections, MarkAsSent, 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.
  • MarkAsSent 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 SalesInvoices WHERE ContactId = 'abc'
SELECT * FROM SalesInvoices WHERE StatusId = 'abc'
SELECT * FROM SalesInvoices WHERE ShowPaymentsAllocations = 'abc'
SELECT * FROM SalesInvoices WHERE ShowCorrections = 'abc'
SELECT * FROM SalesInvoices WHERE MarkAsSent = 'abc'
SELECT * FROM SalesInvoices WHERE ShowPaymentsAllocations = 'abc'
SELECT * FROM SalesInvoices WHERE Search = 'abc'
SELECT * FROM SalesInvoices WHERE FromDate = '2018-12-15'
SELECT * FROM SalesInvoices WHERE ToDate = '2018-12-15'
SELECT * FROM SalesInvoices WHERE UpdatedOrCreatedSince = '2018-12-15'
SELECT * FROM SalesInvoices WHERE DeletedSince = '2018-12-15'
SELECT * FROM SalesInvoices WHERE HasAttachments = 'abc'

Insert

Create a simple sales invoice with a JSON aggregate value for SalesInvoiceLineItemAggregate. 

INSERT INTO SalesInvoices (ContactId, Date, SalesInvoiceLineItemAggregate) VALUES ('0603375a7e29443b8d248239b007e72c', '2022-11-28', '{"description":"In Rainbows LP","ledger_account_id":"35efa3bc698311eda8c40ef4cf562701","unit_price":35.0,"quantity":2.0,"tax_rate_id":"24d66d90e1d64620a5d8b13cfca85b81"}')

Create a simple sales invoice by using temp tables. 

INSERT INTO SalesInvoiceLineItemAggregate#TEMP (Description, LedgerAccountId, UnitPrice, Quantity, TaxRateId) VALUES ('In Rainbows LP', '35efa3bc698311eda8c40ef4cf562701', '35.0', '2.0', '24d66d90e1d64620a5d8b13cfca85b81')
INSERT INTO SalesInvoiceLineItemAggregate#TEMP (Description, LedgerAccountId, UnitPrice, Quantity, TaxRateId) VALUES ('Kid A LP', '35efa3bc698311eda8c40ef4cf562701', '45.0', '4.0', '24d66d90e1d64620a5d8b13cfca85b81')
INSERT INTO SalesInvoices (ContactId, Date, SalesInvoiceLineItemAggregate) VALUES ('0603375a7e29443b8d248239b007e72c', '2022-11-28', 'SalesInvoiceLineItemAggregate#TEMP')

Update

Updates are performed based on Id. 

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

Delete

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

DELETE FROM SalesInvoices WHERE id = '123'

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

DisplayedAs String False

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

MainAddressId String False

Addresses.Id

The unique identifier for the item

BaseCurrencyNetAmount String False

The net amount of the invoice in base currency

BaseCurrencyShippingTaxBreakdownAggregate String False

The shipping tax breakdown for the invoice in base currency

ContactName String False

The name of the contact when the invoice was created

SentByEmail Bool False

Indicates whether the invoice has been emailed

ShippingTaxBreakdownAggregate String False

The shipping tax breakdown for the invoice

Migrated Bool False

Indicates if the sales invoice was migrated from another system.

DueDate Datetime True

The due date of the invoice

TotalDiscountAmount String False

The discount amount on the invoice

TaxAmount String False

The tax amount of the invoice

LastPaid String False

The date of the last payment

PaymentsAllocationsTotalDiscount String False

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

InvoiceNumberPrefix String False

The invoice number prefix

OutstandingAmount String False

The outstanding amount of the invoice

ContactId String False

Contacts.Id

The unique identifier for the item

TaxAddressRegionDisplayedAs String False

The name of the resource

TaxAddressRegionLegacyId Int False

The legacy ID for the item

TaxAddressRegionId String False

The unique identifier for the item

DeliveryPerformanceDate String False

Delivery/Performance Date (Germany only)

TaxCalculationMethod String False

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

TermsAndConditions String False

Invoice terms and conditions

PaymentsAllocationsAggregate String False

The associated payments and allocations

ShippingTaxRateId String False

TaxRates.Id

The unique identifier for the item

Sent Bool False

Indicates whether the invoice has been sent

DeliveryAddressFreeForm String False

The free-form delivery address of the invoice

BaseCurrencyWithholdingTaxAmount String False

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

DeliveryAddressId String False

Addresses.Id

The unique identifier for the item

WithholdingTaxAmount String False

IRPF withheld Tax Amount (Spain only)

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

TaxAnalysisAggregate String False

The invoice tax analysis

SalesCorrectiveInvoiceAggregate String False

The corrective entries associated with the invoice

InverseExchangeRate String False

The inverse exchange rate for the invoice

BaseCurrencyShippingTotalAmount String False

The total shipping amount in base currency

TransactionId String False

Transactions.Id

The unique identifier for the item

TotalQuantity String False

The total quantity of the invoice

StatusDisplayedAs String False

The name of the resource

StatusLegacyId Int False

The legacy ID for the item

StatusId String False

The unique identifier for the item

DetailedTaxAnalysisBaseCurrencyTotalGoodsAmount String False

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

DetailedTaxAnalysisBaseCurrencyTotalTax String False

The base currency total tax amount for all tax rates

DetailedTaxAnalysisTotalRetailerTax String False

The total retailer tax amount for all tax rates

DetailedTaxAnalysisBaseCurrencyTotal String False

The total base currency amount for all tax rates

DetailedTaxAnalysisTotalGoodsAmount String False

The total amount relating to goods for all tax rates

DetailedTaxAnalysisTotalNet String False

The total net amount for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalServicesAmount String False

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

DetailedTaxAnalysisBaseCurrencyTotalNet String False

The base currency total net amount for all tax rates

DetailedTaxAnalysisTotalServicesAmount String False

The total amount relating to services for all tax rates

DetailedTaxAnalysisTotal String False

The total amount for all tax rates

DetailedTaxAnalysisTotalTax String False

The total tax amount for all tax rates

TransactionTypeId String False

TransactionTypes.Id

The unique identifier for the item

BaseCurrencyShippingTaxAmount String False

The tax shipping amount in base currency

ShippingTaxAmount String False

The tax shipping amount. NOTE: This is not required for POST/PUT requests as the shipping tax is calculated based on the shipping_net_amount and the shipping_tax_rate.

CreatedAt Datetime True

The datetime when the item was created

PaymentsAllocationsTotalAmount String False

The total amount of all payments and allocations

VoidReason String False

The reason the invoice was voided

TaxReconciled Bool False

Indicates if the sales invoice is tax reconciled or not.

Editable Bool False

Indicates whether artefact can be edited

ShippingTotalAmount String False

The total shipping amount

BaseCurrencyShippingNetAmount String False

The net shipping amount in base currency

ShippingNetAmount String False

The net shipping amount

CurrencyId String False

Currencies.Id

The unique identifier for the item

SalesInvoiceLineItemAggregate String False

The invoice lines of the invoice

InvoiceNumber String False

The generated invoice number

Notes String False

Invoice notes

MainAddressFreeForm String False

The free-form main address of the invoice

TotalPaid String False

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

DeletedAt Datetime True

The datetime when the item was deleted

Date String False

The date of the invoice

BaseCurrencyTaxAmount String False

The tax amount of the invoice in base currency

BaseCurrencyOutstandingAmount String False

The outstanding amount of the invoice in base currency

BaseCurrencyTotalDiscountAmount String False

The discount amount on the invoice in base currency

LinksAggregate String False

Links for the resource

OriginalQuoteEstimateDisplayedAs String False

The name of the resource

OriginalQuoteEstimateLegacyId Int False

The legacy ID for the item

OriginalQuoteEstimateId String False

The unique identifier for the item

ShowPaymentsAllocations String False

ShowCorrections String False

MarkAsSent String False

ShowPaymentsAllocations String False

Search String False

FromDate Datetime True

ToDate Datetime True

DeletedSince Datetime True

HasAttachments String True

IsCIS Bool True

Determines if this is a CIS transaction.

CISApplicableAmount String True

BaseCurrencyCISApplicableAmount String True

TotalAfterCISDeduction String True

BaseCurrencyTotalAfterCISDeduction String True

HasCISLabour Bool True

HasCISMaterials Bool True

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9062