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-1a53123e67c8</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.