Audit Trial

Parent Previous Next


Audit Trail is used to perform any of the tradition Header/Split type transactions in Sage such as batch sales invoices and credits. Although most of these loads can be found elsewhere in E2S with more fields, this is included for users who wish to upload many types quickly with the most common fields in one process. 


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

Type

n/a

n/a

nvarchar(2)

Used as transaction type to upload as. Available types are... BR/BP/CR/CP/JD/JC/SI/SC/SR/SP/SA/PI/PC/PR/PP/PA/VP/VR. 

AccountRef

Dependant on type

Various - Activity

nvarchar(8)


Date

Transaction Date

Various - Activity

datetime


Details

Line 1

Various - Activity

nvarchar(60)


Reference

Ref

Various - Activity

nvarchar(30)


ExtraReference

ExRef

Various - Activity

nvarchar(30)


UserName

User Name

Various - Activity

nvarchar(32)


Value

n/a Amount

Various - Activity

decimal(18, 2)


TaxCode

VAT Code

Various - Activity

nvarchar(2)


VATAmount

VAT Amount

Various - Activity

decimal(18, 2)


ExchangeRate

Exchange Rate

Various - Activity

decimal(18,2)

If using a foreign currency company and the transaction is for a foreign currency account the transaction uses this exchange rate if provided, and the rate for the account if not.

NomCode

Nominal Code

Various - Activity

nvarchar(8)


Dept

Dept

Various - Activity

nvarchar(2)


ProjectCode

Project Code

Various - Activity

nvarchar(8)


CostCode

Cost Code

Various - Activity

nvarchar(8)


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].[AuditTrail](

       [ID] [int] IDENTITY(1,1) NOT NULL,

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

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

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

       [UserName] [nvarchar](32) 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,

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

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

 CONSTRAINT [PK_AuditTrail] PRIMARY KEY CLUSTERED 

(

       [ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]