JDBC Driver for Sage 50 UK

Build 22.0.8462

SalesInvoices

Query SalesInvoices within Sage UK 50 Accounts.

Table Specific Information

The SalesInvoices table allows you to select, insert, update, and delete sales invoices within a Sage 50 UK company dataset.

Select

The SalesInvoices table returns the sales invoices for a Sage 50 UK company dataset.

SELECT * FROM SalesInvoices WHERE Date >= '1/1/2015' AND Date < '2/1/2015'

Insert

To create a new SalesInvoices record, the TradingAccountUUID field is required.

Sales Invoice Line Items are specified via an XML aggregate within the ItemsAggregate column. The columns that may be used in these aggregates are defined in the SalesInvoiceLineItems table.
Note: For text column in SalesInvoiceLineItems, length of the string must be equal or less than 61 characters, otherwise driver will use first 61 characters.

The following example demonstrates how to insert a new SalesInvoice with two line items:

INSERT INTO SalesInvoices (TradingAccountUUID, Type, PostalAddress1Name, PostalAddress1Type, PostalAddress1Address1, PostalAddress1TownCity, PostalAddress1StateRegion, PostalAddress2Name, PostalAddress2Type, PostalAddress2Address1, PostalAddress2TownCity, PostalAddress2StateRegion, ItemsAggregate) 
VALUES ('b83c940f-9d2b-4a35-bf42-28bbe07ef994', 'Product Invoice', 'Sales Order Delivery Address', 'Shipping', '123 Main St', 'Chapel Hill', 'NC', 'Sales Order Billing Address', 'Billing', '987 Center Road', 'Chapel Hill', 'NC', '<SalesInvoiceLineItems> <Row><CommodityUUID>03ecf74f-0f08-4667-8a59-07e6338a8b32</CommodityUUID><Quantity>2</Quantity><ActualPrice>2.99</ActualPrice></Row> <Row><CommodityUUID>0a47b43d-7115-4863-9689-1a53666e67c8</CommodityUUID><Quantity>1</Quantity><ActualPrice>4.99</ActualPrice></Row>
</SalesInvoiceLineItems>')

Update

Any field that is not read-only can be updated.

When updating a SalesInvoices record, PostalAddress1UUID and PostalAddress2UUID are required when updating a postal address (respectively). Note that sales invoice line items cannot be updated nor deleted.


UPDATE SalesInvoices SET PostalAddress2ZipPostCode='12345'
WHERE SalesInvoiceUUID='392235af-f45a-4c43-a275-897130e4fdc4' AND PostalAddress2UUID='28915e09-b2a1-47e1-b193-741b32ba8ba0'

Delete

Deleting a SalesInvoice record will also delete all line items and postal addresses associated with the SalesInvoice.


DELETE FROM SalesInvoices WHERE SalesInvoiceUUID='392235af-f45a-4c43-a275-897130e4fdc4'

Columns

Name Type ReadOnly Description
SalesInvoiceUUID [KEY] String True

SalesInvoice UUID

TradingAccountUUID String False

SalesOrder Trading Account UUID

ContactUUID String True

SalesOrder Buyer Contact UUID

InvoiceNumber String False

SalesInvoice Reference

SalesOrderNumber String False

SalesInvoice Reference2

Status String False

SalesInvoice Status

Type String False

SalesInvoice Type

CustomerOrderNumber String False

SalesInvoice Customer Reference

Date Date False

SalesInvoice Date

TaxDate Date False

SalesInvoice Tax Date

CarrierNetPrice Decimal False

SalesInvoice Carrier Net Price

CarrierTaxPrice Decimal False

SalesInvoice Carrier Tax Price

CarrierTotalPrice Decimal False

SalesInvoice Carrier Gross Price

Currency String False

SalesInvoice Currency

OCCurrencyExchangeRate Decimal False

SalesInvoice Operating Company Currency Exchange Rate

OCCurrencyExchangeRateOperator String False

SalesInvoice Operating Company Currency Exchange Rate Operator

LineCount Decimal True

SalesInvoice Line Count

SettlementDiscountAmount Decimal False

SalesInvoice Settlement Discount Amount

SettlementDiscountPercent Decimal False

SalesInvoice Settlement Discount Percent

SettlementDiscountIncludedInTotal Boolean False

SalesInvoice Settlement Discount Included In Total

InvoiceDiscountAmount Decimal False

SalesInvoice Invoice Discount Amount

InvoiceDiscountPercent Decimal False

SalesInvoice Invoice Discount Percent

NetTotal Decimal True

SalesInvoice Net Total

TaxTotal Decimal True

SalesInvoice Tax Total

GrossTotal Decimal True

SalesInvoice Gross Total

Text1 String False

SalesInvoice Text1

Text2 String False

SalesInvoice Text2

User String False

SalesInvoice User

PaymentDue Date True

SalesInvoice Payment Due

ItemsAggregate String False

An aggregate of the line item data, which can be used for adding a purchase order and its line items.

PostalAddress1UUID String True

SalesOrder PostalAddress UUID

PostalAddress1Active Boolean False

SalesOrder PostalAddress Active Flag

PostalAddress1Reference String False

SalesOrder PostalAddress Reference

PostalAddress1Name String False

SalesOrder PostalAddress Name

PostalAddress1Description String False

SalesOrder PostalAddress Description

PostalAddress1Address1 String False

SalesOrder PostalAddress Address 1

PostalAddress1Address2 String False

SalesOrder PostalAddress Address 2

PostalAddress1Address3 String False

SalesOrder PostalAddress Address 3

PostalAddress1Address4 String False

SalesOrder PostalAddress Address 4

PostalAddress1TownCity String False

SalesOrder PostalAddress TownCity

PostalAddress1County String False

SalesOrder PostalAddress County

PostalAddress1StateRegion String False

SalesOrder PostalAddress StateRegion

PostalAddress1ZipPostCode String False

SalesOrder PostalAddress ZipPost Code

PostalAddress1Country String False

SalesOrder PostalAddress Country

PostalAddress1PrimacyIndicator Boolean False

SalesOrder PostalAddress Primacy Indicator

PostalAddress1Type String False

SalesOrder PostalAddress Type. Sample values: Billing, Shipping.

PostalAddress2UUID String True

SalesOrder PostalAddress UUID

PostalAddress2Active Boolean False

SalesOrder PostalAddress Active Flag

PostalAddress2Reference String False

SalesOrder PostalAddress Reference

PostalAddress2Name String False

SalesOrder PostalAddress Name

PostalAddress2Description String False

SalesOrder PostalAddress Description

PostalAddress2Address1 String False

SalesOrder PostalAddress Address 1

PostalAddress2Address2 String False

SalesOrder PostalAddress Address 2

PostalAddress2Address3 String False

SalesOrder PostalAddress Address 3

PostalAddress2Address4 String False

SalesOrder PostalAddress Address 4

PostalAddress2TownCity String False

SalesOrder PostalAddress TownCity

PostalAddress2County String False

SalesOrder PostalAddress County

PostalAddress2StateRegion String False

SalesOrder PostalAddress StateRegion

PostalAddress2ZipPostCode String False

SalesOrder PostalAddress ZipPost Code

PostalAddress2Country String False

SalesOrder PostalAddress Country

PostalAddress2PrimacyIndicator Boolean False

SalesOrder PostalAddress Primacy Indicator

PostalAddress2Type String False

SalesOrder PostalAddress Type. Sample values: Billing, Shipping.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Updated Datetime

An input used internally to filter records updated at a specific datetime.

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