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.