Purchase Invoices/Credits

The data uploaded for Purchase Invoices and Credits are identical. You must provide a valid AccountRef to load the invoice/credit against. Another required field is InvRef. This is used to tell the system when a new invoice/credit is reached. If every line has a different InvRef they will all be single line invoice/credits. If you use the same InvRef on 2 lines (for example) this will create one invoice/credit with 2 detail lines as long as they are both for the same AccountRef.

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 Supplier account ref this inv/credit is for
InvRef	Ref	Supplier Activity - Transaction Line	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 inv/credit as detail lines.
ExtRef	Ex Ref	Supplier Activity - Transaction Detail Line	nvarchar(30)
Date	Date	Supplier Activity - Transaction Line	datetime
TaxCode	n/a Tax Code	n/a	nvarchar(2)	Not visible on Supplier Activity or Nominal Ledger
Value	Amount	Supplier Activity - Transaction Line	decimal(18, 2)	Combined with VATAmount as Amount in Sage
VATAmount	Combined with above	Supplier Activity - Transaction Line	decimal(18, 2)	Combined with Amount as Amount in Sage
ExchRate	N/A	N/A	decimal(18, 2)	Used to give an exch rate other than default if required on foreign currency Sage companies
Details	Details	Supplier Activity - Transaction Line	nvarchar(60)
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
Fund	Funds	Supplier Activity - Transaction Detail Line	nvarchar(4)	Also visible in Funds Activity screen
DueDate	Due On	Supplier Activity - Transaction Line	datetime
Status	N/A	N/A	nvarchar(8)	Used internally and not uploaded to Sage. Set to 'New', 'Error' or 'Sent' normally

Script to create the table...

CREATE TABLE [dbo].[PurchaseInvoices](

[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,

[ExtRef] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Date] [datetime] NULL,

[TaxCode] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Value] [decimal](18, 2) NULL,

[VATAmount] [decimal](18, 2) NULL,

[ExchRate] [decimal](18, 2) NULL,

[Details] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS 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,

[Fund] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DueDate] [datetime] NULL,

[Status] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_PurchaseInvoices_Status] DEFAULT (N'New'),

CONSTRAINT [PK_PurchaseInvoices] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

----

CREATE TABLE [dbo].[PurchaseCredits](