Sales Orders
Sales Orders come in two types (order and quote) and have three types of detail lines (Product, Special and Message). Some fields are used only for some or one of these combinations. Again, the InvRef (within AccountRef) is the key field controlling what lines are on what orders/quotes.
If the price/value fields are not filled in the best customer price (price list if valid, stock price if not) is used and the tax is calculated for you.
Field Mapping (mandatory fields in RED)
|
E2S Field |
SAGE Field |
Location within Sage |
Data Type |
Notes |
|
ID |
N/A |
N/A |
int |
Used internally and not uploaded to Sage |
|
AccountRef |
A/C |
Product Sales Order - Details |
nvarchar(8) |
The customer account ref this inv/credit is for. |
|
InvRef |
Inv Ref |
Product Sales Order - Details |
nvarchar(30) |
The key field for this tab. This controls the creation of each header record. Lines with identical AccountRef/InvRef will be groups on an order as detail lines. |
|
CustOrderNo |
Customer Order No |
Product Sales Order - Order Details - Customer Order Details |
nvarchar(60) |
|
|
GlobalNomCode |
N/C |
Product Sales Order - Footer Details - Global |
nvarchar(8) |
Defaults to customer default if not supplied. |
|
Date |
Date |
Product Sales Order - Details |
datetime |
|
|
OrderDueDate |
Due |
Product Sales Order - Order Details - Customer Order Details |
datetime |
|
|
Product |
Product Code |
Product Sales Order - Details - Item Lines |
nvarchar(30) |
|
|
Description |
Description |
Product Sales Order - Details - Item Lines |
nvarchar(60) |
If blank stock record description used |
|
Qty |
Quantity |
Product Sales Order - Details - Item Lines |
decimal(18, 2) |
|
|
UnitPrice |
Price |
Product Sales Order - Details - Item Lines |
decimal(18, 2) |
|
|
Value |
Net Amount |
Product Sales Order - Details |
decimal(18, 2) |
|
|
DiscountAmount |
Disc. (Curr) |
Product Sales Order - Details |
decimal(18, 2) |
|
|
TaxCode |
Tax Code |
n/a |
nvarchar(2) |
Not visible on Product Sales Order. |
|
VATAmount |
VAT Amount |
Product Sales Order - Details - Item Line |
decimal(18, 2) |
|
|
ExchRate |
Exchange Rate |
|
decimal(18, 2) |
If left blank will use Sage default exchange rate (where Foreign Trader enabled in Sage) |
|
NetValueDiscount |
Net £ |
Sales Order - Details - Deduction |
decimal(18, 2) |
|
|
NetValueDescription |
Description |
Sales Order - Details - Deduction |
nvarchar(60) |
|
|
NomCode |
N/C |
Product Sales Order - Details - Item Line - Posting Details |
|
Defaults to stock record default (stock items) or customer default (special items) if not supplied. |
|
DueDate |
Due Date |
Product Sales Order - Details - Item Line - Item Status |
datetime |
|
|
Notes1 |
Notes Line 1 |
Product Sales Order - Order Details - Notes |
nvarchar(60) |
|
|
Notes2 |
Notes Line 2 |
Product Sales Order - Order Details - Notes |
nvarchar(60) |
|
|
Notes3 |
Notes Line 3 |
Product Sales Order - Order Details - Notes |
nvarchar(60) |
|
|
Analysis1 |
Analysis1 |
Product Sales Order - Order Details - Custom Fields |
nvarchar(30) |
|
|
Analysis2 |
Analysis2 |
Product Sales Order - Order Details - Custom Fields |
nvarchar(30) |
|
|
Analysis3 |
Analysis3 |
Product Sales Order - Order Details - Custom Fields |
nvarchar(30) |
|
|
TakenBy |
Order Taken By |
Product Sales Order - Order Details - Customer Order Details |
nvarchar(60) |
|
|
Dept |
Department |
Product Sales Order - Footer Details - Global |
nvarchar(2) |
|
|
ProjectCode |
|
Product Sales Order - Details - Item Line - Project Ref |
nvarchar(8) |
|
|
DelName |
Delivery Add Line 1 |
Product Sales Order - Order Details - Delivery Address |
nvarchar(60) |
|
|
DelAdd1 |
Delivery Add Line 2 |
Product Sales Order - Order Details - Delivery Address |
nvarchar(60) |
|
|
DelAdd2 |
Delivery Add Line 3 |
Product Sales Order - Order Details - Delivery Address |
nvarchar(60) |
|
|
DelAdd3 |
Delivery Add Line 4 |
Product Sales Order - Order Details - Delivery Address |
nvarchar(60) |
|
|
DelAdd4 |
Delivery Add Line 5 |
Product Sales Order - Order Details - Delivery Address |
nvarchar(60) |
|
|
DelAdd5 |
Delivery Add Line 6 |
Product Sales Order - Order Details - Delivery Address |
nvarchar(60) |
|
|
Name |
Add Line 1 |
Product Sales Order - Details |
nvarchar(60) |
Customer default address used if all blank |
|
Add1 |
Add Line 2 |
Product Sales Order - Details |
nvarchar(60) |
" |
|
Add2 |
Add Line 3 |
Product Sales Order - Details |
nvarchar(60) |
" |
|
Add3 |
Add Line 4 |
Product Sales Order - Details |
nvarchar(60) |
" |
|
Add4 |
Add Line 5 |
Product Sales Order - Details |
nvarchar(60) |
" |
|
Add5 |
Add Line 6 |
Product Sales Order - Details |
nvarchar(60) |
" |
|
Contact |
Customer Contact |
Product Sales Order - Order Details - Customer Order Details |
nvarchar(30) |
|
|
Telephone |
Customer Tel. No |
Product Sales Order - Order Details - Customer Order Details |
nvarchar(30) |
|
|
Quote |
Type - Quote SOP |
Product Quote SOP - Quote Details |
nvarchar(3) |
Yes/No |
|
Quote Date |
Date of Quote |
Product Quote SOP - Quote Details |
datetime |
|
|
DUNS |
DUNS Number |
Product Sales Order - Order Details - Notes |
nvarchar(9) |
|
|
ConsignNo |
Consign. No |
Product Sales Order - Footer Details - Carriage |
nvarchar(30) |
|
|
Comment1 |
Comment 1 |
Product Sales Order - Details - Item Line |
nvarchar(60) |
Not special items |
|
Comment2 |
Comment 2 |
Product Sales Order - Details - Item Line |
nvarchar(60) |
Not special items |
|
LineInfo |
Line Information |
Product Sales Order - Details - Item Line - Additional Information |
nvarchar(60) |
Not message items |
|
Units |
Units |
Product Sales Order - Details - Item Line |
nvarchar(8) |
Not message items |
|
OrderRef |
Order Reference |
Product Sales Order - Details - Item Line |
nvarchar(30) |
Not message items |
|
OrderLineRef |
Order Line Reference |
Product Sales Order - Details |
int |
Not message items |
|
Description (for message/special lines) |
Description |
Product Sales Order - (Details) - Item Line |
nvarchar(60) |
Special/message items only |
|
Details (for special items) |
Details |
Product Sales Order - Details - Item Line |
nvarchar(240) |
Special items only |
|
CarriageNet |
Net |
Product Sales Order - Footer - Carriage |
nvarchar(8) |
|
|
CarriageTaxCode |
Tax Code |
Product Sales Order - Footer - Carriage |
nvarchar(2) |
|
|
CarriageNomCode |
N/C |
Product Sales Order - Footer - Carriage |
nvarchar(8) |
|
|
CarriageDept |
Department |
Product Sales Order - Footer - Carriage |
nvarchar(2) |
|
|
PaymentRef |
Payment Ref |
Product Sales Order - Payment - Payment Details |
nvarchar(30) |
|
|
PaymentBankCode |
Bank Code |
Product Sales Order - Payment - Payment Details |
nvarchar(8) |
|
|
PaymentAmount |
Payment Amount |
Product Sales Order - Payment - Payment Details |
decimal(18, 2) |
|
|
PaymentType |
|
Product Sales Order - Payment Type |
nvarchar(2) |
SA only |
|
Proforma |
Type - Proforma SOP |
Product Proforma SOP - Proforma Details |
nvarchar(2) |
Yes/No |
|
Status |
N/A |
N/A |
nvarchar(8) |
Used internally and not uploaded to Sage. Set to 'New', 'Error' or 'Sent' normally |
NB: For Payment details the highest amount for any Order will be used (MAX function) and the total used will include any carriage and discount amounts on the gross amount for the invoice.
Script to create the table...
CREATE TABLE [dbo].[SalesOrders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AccountRef] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvRef] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustOrderNo] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GlobalNomCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Date] [datetime] NULL,
[OrderDueDate] [datetime] NULL,
[Product] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Qty] [decimal](18, 2) NULL,
[UnitPrice] [decimal](18, 2) NULL,
[Value] [decimal](18, 2) NULL,
[DiscountAmount] [decimal](18, 2) NULL,
[TaxCode] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VATAmount] [decimal](18, 2) NULL,
[ExchRate] [decimal](18, 2) NULL,
[NomCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DueDate] [datetime] NULL,
[Notes1] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Notes2] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Notes3] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Analysis1] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Analysis2] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Analysis3] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TakenBy] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Dept] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProjectCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DelName] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DelAdd1] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DelAdd2] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DelAdd3] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DelAdd4] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DelAdd5] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Name] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Add1] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Add2] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Add3] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Add4] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Add5] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Contact] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Telephone] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Quote] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[QuoteDate] [datetime] NULL,
[DUNS] [nvarchar](9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ConsignNo] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Comment1] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Comment2] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LineInfo] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Units] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrderRef] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrderLineRef] [int] NULL,
[Description (for message/special lines)] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Details (for special items)] [nvarchar](240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CarriageNet] [decimal](18, 2) NULL,
[CarriageTaxCode] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CarriageNomCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CarriageDept] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PaymentRef] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PaymentBankCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PaymentAmount] [decimal](18, 2) NULL,
[PaymentType] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Proforma] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_SalesOrders_Status] DEFAULT (N'New'),
CONSTRAINT [PK_SalesOrders] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]