Purchase Receipts/Payments + On A/C
Purchase payments, receipts and receipts on account all use the same fields. The key field on this load is AccountRef.
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 |
Supplier Activity |
nvarchar(8) |
The key field for this tab. This will be the A/C or account ref for the Supplier this receipt or payment is for. |
|
|
Date |
Date |
Supplier Activity - Transaction Line |
datetime |
|
|
|
Details |
Details |
Supplier Activity - Transaction Line |
nvarchar(60) |
|
|
|
Value |
Amount |
Supplier Activity - Transaction Line |
decimal(18, 2) |
Combined with VATAmount as Amount in Sage |
|
|
DiscountAmount* |
Amount* |
Supplier Activity - Transaction Line |
decimal(18, 2) |
If an amount is in this field on a PurchasePayment record a separate PD discount record is created with this as the amount |
|
|
NomCode |
N/C |
Supplier Activity - Transaction Detail Line |
nvarchar(8) |
|
|
|
Dept |
Dept |
Supplier Activity - Transaction Detail Line |
nvarchar(2) |
|
|
|
ProjectCode** |
n/a - Project Code |
n/a |
nvarchar(8) |
Not visible on Supplier Activity transactions |
|
|
CostCode** |
n/a - Cost Code |
n/a |
nvarchar(8) |
Not visible on Supplier Activity transactions |
|
|
Status |
N/A |
N/A |
nvarchar(8) |
|
* Purchase Payments only
** Sage versions prior to v24
Script to create the table(s)...
CREATE TABLE [dbo].[PurchasePayments](
[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,
[DiscountAmount] [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_PurchasePayments_Status] DEFAULT (N'New'),
CONSTRAINT [PK_PurchasePayments] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
----
CREATE TABLE [dbo].[PurchaseReceipts](
[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_PurchaseReceipts_Status] DEFAULT (N'New'),
CONSTRAINT [PK_PurchaseReceipts] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
----
CREATE TABLE [dbo].[PurchasePaymentsOnAccount](
[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_PurchaseReceiptsOnAccount_Status] DEFAULT (N'New'),
CONSTRAINT [PK_PurchaseReceiptsOnAccount] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]