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-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. |