JDBC Driver for Sage 50 UK

Build 22.0.8462

SalesCredits

Query SalesCredits within Sage UK 50 Accounts.

Table Specific Information

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

Select

The SalesCredits table returns the sales credits for a Sage 50 UK company dataset.

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

Insert

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

Sales Credit 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 SalesCreditLineItems table.

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

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

Update

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

When updating a SalesCredits record, PostalAddress1UUID or PostalAddress2UUID are required when updating the respective postal address. Note that sales credit line items cannot be updated nor deleted.


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

Delete

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


DELETE FROM SalesCredits WHERE SalesCreditUUID='392235af-f45a-4c43-a275-897130e4fdc4'

Columns

Name Type ReadOnly Description
SalesCreditUUID [KEY] String True

SalesCredit UUID

TradingAccountUUID String False

SalesCredit Trading Account UUID

ContactUUID String True

SalesCredit Buyer Contact UUID

CreditNumber String False

SalesCredit Reference

SalesOrderNumber String False

SalesCredit Invoice Reference

Status String False

SalesCredit Status

Type String False

SalesCredit Type

CustomerOrderNumber String False

SalesCredit Customer Reference

CopyFlag Boolean False

SalesCredit Copy Flag

Date Date False

SalesCredit Date

TaxDate Date False

SalesCredit Tax Date

CarrierNetPrice Decimal False

SalesCredit Carrier Net Price

CarrierTaxPrice Decimal False

SalesCredit Carrier Tax Price

CarrierTotalPrice Decimal False

SalesCredit Carrier Gross Price

Currency String False

SalesCredit Currency

OCCurrencyExchangeRate Decimal False

SalesCredit OperatingCompany Currency Exchange Rate

OCCurrencyExchangeRateOperator String False

SalesCredit OperatingCompany Currency Exchange Rate Operator

LineCount Decimal False

SalesCredit Line Count

SettlementDiscountAmount Decimal False

SalesCredit Settlement Discount Amount

SettlementDiscountPercent Decimal False

SalesCredit Settlement Discount Percent

SettlementDiscountIncludedInTotal Boolean False

SalesCredit Settlement Discount Included In Total

InvoiceDiscountAmount Decimal False

SalesCredit Invoice Discount Amount

InvoiceDiscountPercent Decimal False

SalesCredit Invoice Discount Percent

NetTotal Decimal True

SalesCredit Net Total

TaxTotal Decimal True

SalesCredit Tax Total

GrossTotal Decimal True

SalesCredit Gross Total

Text1 String False

SalesCredit Text1

Text2 String False

SalesCredit Text2

User String False

SalesCredit User

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

SalesCredit PostalAddress UUID

PostalAddress1Active Boolean False

SalesCredit PostalAddress Active Flag

PostalAddress1Reference String False

SalesCredit PostalAddress Reference

PostalAddress1Name String False

SalesCredit PostalAddress Name

PostalAddress1Description String False

SalesCredit PostalAddress Description

PostalAddress1Address1 String False

SalesCredit PostalAddress Address 1

PostalAddress1Address2 String False

SalesCredit PostalAddress Address 2

PostalAddress1Address3 String False

SalesCredit PostalAddress Address 3

PostalAddress1Address4 String False

SalesCredit PostalAddress Address 4

PostalAddress1TownCity String False

SalesCredit PostalAddress TownCity

PostalAddress1County String False

SalesCredit PostalAddress County

PostalAddress1StateRegion String False

SalesCredit PostalAddress StateRegion

PostalAddress1ZipPostCode String False

SalesCredit PostalAddress ZipPost Code

PostalAddress1Country String False

SalesCredit PostalAddress Country

PostalAddress1PrimacyIndicator Boolean False

SalesCredit PostalAddress Primacy Indicator

PostalAddress1Type String False

SalesCredit PostalAddress Type. Sample values: Billing, Shipping.

PostalAddress2UUID String True

SalesCredit PostalAddress UUID

PostalAddress2Active Boolean False

SalesCredit PostalAddress Active Flag

PostalAddress2Reference String False

SalesCredit PostalAddress Reference

PostalAddress2Name String False

SalesCredit PostalAddress Name

PostalAddress2Description String False

SalesCredit PostalAddress Description

PostalAddress2Address1 String False

SalesCredit PostalAddress Address 1

PostalAddress2Address2 String False

SalesCredit PostalAddress Address 2

PostalAddress2Address3 String False

SalesCredit PostalAddress Address 3

PostalAddress2Address4 String False

SalesCredit PostalAddress Address 4

PostalAddress2TownCity String False

SalesCredit PostalAddress TownCity

PostalAddress2County String False

SalesCredit PostalAddress County

PostalAddress2StateRegion String False

SalesCredit PostalAddress StateRegion

PostalAddress2ZipPostCode String False

SalesCredit PostalAddress ZipPost Code

PostalAddress2Country String False

SalesCredit PostalAddress Country

PostalAddress2PrimacyIndicator Boolean False

SalesCredit PostalAddress Primacy Indicator

PostalAddress2Type String False

SalesCredit 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