Sales payments, receipts and receipts on account all use the same fields. The key field on this load is AccountRef. InvoiceRef is not used in the same way as all these records have one line only. It is used to allocate against an existing invoice if it exists in Sage, but is optional.
All these records will be loaded with tax code T9 and zero VAT.
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 |
Customer Activity |
nvarchar(8) |
The key field for this tab. This will be the A/C or account ref for the customer this receipt or payment is for. |
Date |
Date |
Customer Activity - Transaction Line |
datetime |
|
Details |
Details |
Customer Activity - Transaction Line |
nvarchar(60) |
|
Value |
Amount |
Customer Activity - Transaction Line |
decimal(18, 2) |
Combined with VATAmount as Amount in Sage |
NomCode |
N/C |
Customer Activity - Transaction Detail Line |
nvarchar(8) |
|
Dept |
Dept |
Customer Activity - Transaction Detail Line |
nvarchar(2) |
|
InvoiceRef |
Ref |
Customer Activity - Transaction Detail Line |
nvarchar(30) |
If invoice exists in Sage an allocation is made |
ProjectCode* |
n/a - Project Code |
n/a |
nvarchar(8) |
Not visible on Customer Activity transactions |
CostCode* |
n/a - Cost Code |
n/a |
nvarchar(8) |
Not visible on Customer Activity transactions |
Status |
N/A |
N/A |
nvarchar(8) |
Used internally and not uploaded to Sage. Set to 'New', 'Error' or 'Sent' normally |
** Sage versions prior to v24
Script to create the table...
CREATE TABLE [dbo].[SalesPayments](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AccountRef] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Date] [datetime] NULL,
[Details] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Value] [decimal](18, 2) NULL,
[NomCode] [nvarchar](8) 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,
[CostCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Ref] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fund] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_SalesPayments_Status] DEFAULT (N'New'),
CONSTRAINT [PK_SalesPayments] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
----
CREATE TABLE [dbo].[SalesReceipts](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AccountRef] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Date] [datetime] NULL,
[Details] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Value] [decimal](18, 2) NULL,
[TaxCode] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VATAmount] [decimal](18, 2) NULL,
[NomCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Dept] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvoiceRef] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProjectCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CostCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fund] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_SalesReceipts_Status] DEFAULT (N'New'),
CONSTRAINT [PK_SalesReceipts] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
----
CREATE TABLE [dbo].[SalesReceiptsOnAccount](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AccountRef] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Date] [datetime] NULL,
[Details] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Value] [decimal](18, 2) NULL,
[TaxCode] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VATAmount] [decimal](18, 2) NULL,
[NomCode] [nvarchar](8) 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,
[CostCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Ref] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fund] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_SalesReceiptsOnAccount_Status] DEFAULT (N'New'),
CONSTRAINT [PK_SalesReceiptsOnAccount] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]