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 | 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 | |
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 | False |
Indicates if the purchase invoice was migrated from another system. | |
DueDate | Datetime | True |
The due date of 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 | |
OutstandingAmount | String | False |
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 | False |
The name of the resource | |
TaxAddressRegionLegacyId | Int | False |
The legacy ID for the item | |
TaxAddressRegionId | String | False |
The unique identifier for the item | |
TaxCalculationMethod | String | False |
The tax calculation method, if applicable, for this purchase invoice, returns invoice or cash. | |
PaymentsAllocationsAggregate | String | False |
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 | False |
The legacy ID for the item | |
TaxAnalysisAggregate | String | False |
The invoice tax analysis (Optional for Spain, restricted for all other regions) | |
PurchaseCorrectiveInvoiceAggregate | String | False |
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 | False |
Transactions.Id |
The unique identifier for the item |
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 |
The unique identifier for the item | |
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 purchase invoice is tax reconciled or not. | |
Editable | Bool | False |
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 | False |
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 | False |
The outstanding amount of the invoice in base currency | |
ShowPaymentsAllocations | String | False | ||
ShowCorrections | String | False | ||
ShowPaymentsAllocations | String | False | ||
Search | String | False | ||
FromDate | Datetime | True | ||
ToDate | Datetime | True | ||
DeletedSince | Datetime | True | ||
HasAttachments | String | True | ||
VatExemptConsignment | Bool | True |
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 | True | ||
BaseCurrencyCISApplicableAmount | String | True | ||
TotalAfterCISDeduction | String | True | ||
BaseCurrencyTotalAfterCISDeduction | String | True | ||
HasCISLabour | Bool | True | ||
HasCISMaterials | Bool | True |