Sales Payments/Receipts + On A/C

Parent Previous Next


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]