Product Invoice/Credit and Credits use the same fields. Again the combination of AccountRef/InvRef controls the creation of each new inv/credit with identical items appearing as detail lines. Note that if the UnitPrice field is blank then this will be looked up from the stock record and Value and VATAmount calculated accordingly.
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 Invoice/Credit - Details |
nvarchar(8) |
The customer account ref this invoice is for |
InvRef |
Inv. No. |
Product Invoice/Credit - Details |
nvarchar(7) |
The key field for this tab. This controls the creation of each header record. Lines with identical AccountRef/InvRef will be grouped on an invoice/credit as detail lines. |
OrderNo |
Order No. |
|
nvarchar(7) |
|
CustOrderNo |
Customer Order No. |
Product Invoice/Credit - Order Details - Customer Order Details |
nvarchar(60) |
|
Date |
Date |
Product Invoice/Credit - Details |
datetime |
|
Product |
Product Code |
Product Invoice/Credit - Details - Item Lines |
nvarchar(30) |
|
Qty |
Quantity |
Product Invoice/Credit - Details - Item Lines |
decimal(18, 2) |
|
UnitPrice |
Price |
Product Invoice/Credit - Details - Item Lines |
decimal(18, 2) |
|
Value |
Net Amount |
Product Invoice/Credit - Details |
decimal(18, 2) |
|
DiscountAmount |
Discount Amount |
Product Invoice/Credit - Details - Values |
decimal(18, 2) |
|
TaxCode |
Tax Code |
Product Invoice/Credit - Details - Line Item - Posting Details |
nvarchar(2) |
|
VATAmount |
VAT Amount |
Product Invoice/Credit - 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) |
NomCode |
Nominal Code |
Product Invoice/Credit - Details - Line Item - Posting Details |
nvarchar(8) |
|
NetValueDiscount |
Net £ |
Product Invoice/Credit - Details - Deduction |
8 |
|
NetValueDescription |
Description |
Product Invoice/Credit - Details - Deduction |
60 |
|
Notes1 |
Notes Line 1 |
Product Invoice/Credit - Order Details - Notes |
nvarchar(60) |
|
Notes2 |
Notes Line 2 |
Product Invoice/Credit - Order Details - Notes |
nvarchar(60) |
|
Notes3 |
Notes Line 3 |
Product Invoice/Credit - Order Details - Notes |
nvarchar(60) |
|
Analysis1 |
Analysis1 |
Product Invoice/Credit - Order Details - Custom Fields |
nvarchar(30) |
|
Analysis2 |
Analysis2 |
Product Invoice/Credit - Order Details - Custom Fields |
nvarchar(30) |
|
Analysis3 |
Analysis3 |
Product Invoice/Credit - Order Details - Custom Fields |
nvarchar(30) |
|
TakenBy |
Order Taken By |
Product Invoice/Credit - Order Details - Customer Order Details |
nvarchar(30) |
|
Dept |
Department |
Product Invoice/Credit - Footer Details - Global |
nvarchar(2) |
|
ProjectCode |
Project Ref |
Product Invoice/Credit - Details - Item Line - Project Details |
nvarchar(8) |
|
Comment1 |
Comment 1 |
Product Invoice/Credit - Details - Item Line |
nvarchar(60) |
Not special items |
Comment2 |
Comment 2 |
Product Invoice/Credit - Details - Item Line |
nvarchar(60) |
Not special items |
LineInfo |
Line Information |
Product Invoice/Credit - Details - Item Line - Additional Information |
nvarchar(60) |
Not message items |
Units |
Units |
Product Invoice/Credit - Details - Item Line |
nvarchar(8) |
Not message items |
Description (for message/special lines) |
Description |
Product Invoice/Credit - (Details) - Item Line |
nvarchar(60) |
Special/message items only |
Details (for special items) |
Details |
Product Invoice/Credit - Details - Item Line |
nvarchar(240) |
Special items only |
DelName |
Delivery Name |
Product Invoice/Credit - Order Details - Delivery Address |
nvarchar(60) |
|
DelAdd1 |
Del Address 1 |
Product Invoice/Credit - Order Details - Delivery Address |
nvarchar(60) |
|
DelAdd2 |
Del Address 2 |
Product Invoice/Credit - Order Details - Delivery Address |
nvarchar(60) |
|
DelAdd3 |
Del Address 3 |
Product Invoice/Credit - Order Details - Delivery Address |
nvarchar(60) |
|
DelAdd4 |
Del Address 4 |
Product Invoice/Credit - Order Details - Delivery Address |
nvarchar(60) |
|
DelAdd5 |
Del Address 5 |
Product Invoice/Credit - Order Details - Delivery Address |
nvarchar(60) |
|
Name |
Address Line 1 |
|
nvarchar(60) |
Customer default address used if all blank |
Add1 |
Address Line 2 |
Product Invoice/Credit - Details |
nvarchar(60) |
" |
Add2 |
Address Line 3 |
Product Invoice/Credit - Details |
nvarchar(60) |
" |
Add3 |
Address Line 4 |
Product Invoice/Credit - Details |
nvarchar(60) |
" |
Add4 |
Address Line 5 |
Product Invoice/Credit - Details |
nvarchar(60) |
" |
Add5 |
Address Line 6 |
Product Invoice/Credit - Details |
nvarchar(60) |
" |
Contact |
Customer Contact |
Product Invoice/Credit - Order Details - Customer Order Details |
nvarchar(30) |
|
Telephone |
Customer Tel. No |
Product Invoice/Credit - Order Details - Customer Order Details |
nvarchar(30) |
|
Proforma |
|
|
nvarchar(3) |
On invoices only - defines whether this order will be entered as a Pro-forma rather than standard invoice. Note that if any line on an order has Yes in this field the invoice will be entered as a Pro-forma. |
CarriageNet |
Net |
Product Invoice/Credit - Footer - Carriage |
nvarchar(8) |
|
CarriageTaxCode |
Tax Code |
Product Invoice/Credit - Footer - Carriage |
nvarchar(2) |
|
CarriageNomCode |
N/C |
Product Invoice/Credit - Footer - Carriage |
nvarchar(8) |
|
CarriageDept |
Department |
Product Invoice/Credit - Footer - Carriage |
nvarchar(2) |
|
DueDate |
Due On |
Product Invoice/Credit - Details |
datetime |
|
Status |
N/A |
N/A |
nvarchar(8) |
Used internally and not uploaded to Sage. Set to 'New', 'Error' or 'Sent' normally |
PaymentRef |
Payment Ref |
Product Invoice - Payment - Payment Details |
nvarchar(30) |
Invoices Only |
PaymentBankCode |
Bank Code |
Product Invoice - Payment - Payment Details |
nvarchar(8) |
" |
PaymentAmount |
Payment Amount |
Product Invoice - Payment - Payment Details |
decimal(18, 2) |
" |
PaymentType |
|
Product Invoice - Payment Type |
nvarchar(2) |
" |
If you have selected 'Update Ledgers' in Configuration then invoices/credit notes will be posted to the ledger if possible.
Payment details are for invoices only. The highest amount for any Invoice 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(s)...
USE [E2S50Data]
GO
/****** Object: Table [dbo].[ProductInvoices] Script Date: 07/16/2015 15:35:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductInvoices](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AccountRef] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvRef] [nvarchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrderNo] [nvarchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustOrderNo] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Date] [datetime] NULL,
[Product] [nvarchar](30) 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,
[NetValueDiscount] [decimal](18, 2) NULL,
[NetValueDescription] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NomCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS 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](30) 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,
[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,
[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,
[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,
[Proforma] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ConsignNo] [nvarchar](30) 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,
[DueDate] [datetime] 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,
[Status] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_ProductInvoices_Status] DEFAULT (N'New'),
CONSTRAINT [PK_productInvoices] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
----
CREATE TABLE [dbo].[ProductCredits](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AccountRef] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvRef] [nvarchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrderNo] [nvarchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustOrderNo] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Date] [datetime] NULL,
[Product] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Qty] [decimal](18, 2) NULL,
[UnitPrice] [decimal](18, 2) NULL,
[DiscountAmount] [decimal](18, 2) NULL,
[Value] [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,
[NetValueDiscount] [decimal](18, 2) NULL,
[NetValueDescription] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NomCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS 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](30) 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,
[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,
[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,
[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,
[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,
[DueDate] [datetime] NULL,
[Status] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_ProductCredits_Status] DEFAULT (N'New'),
CONSTRAINT [PK_productCredits] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]