Service Invoices/Credits

Service Invoice/Credit and Credits use the same fields. Again, the combination of AccountRef/InvRef controls the creation of each new invoice/credit with identical items appearing as detail lines.

Field Mapping (mandatory fields in RED)

E2S Field

SAGE Field

Location within Sage

Data Type

Notes

N/A

int

Used internally and not uploaded to Sage

AccountRef

A/C

Service Invoice/Credit - Details

nvarchar(8)

The customer account ref this invoice is for

InvRef

Inv. No.

Service Invoice/Credit - Details

nvarchar(7)

The key field for this tab. This controls the creation of each header record. Lines with identical AccountRef/InvRef will be groups on an order as detail lines.

OrderNo

Order No.

nvarchar(7)

CustOrderNo

Customer Order No.

Service Invoice/Credit - Order Details - Customer Order Details

nvarchar(60)

Date

Service Invoice/Credit - Details

datetime

Details

Service Invoice/Credit - Details - Item Lines

nvarchar(60)

Description

Service Invoice/Credit - Details - Item Lines

nvarchar(60)

Qty

Service Invoice/Credit - Details - Item Lines

decimal(18, 2)

If blank a value of 1 will be used

UnitPrice

Net Amount

Service Invoice/Credit - Details / Item Lines

decimal(18, 2)

DiscountAmount

Product Invoice/Credit - Details - Values

TaxCode

Tax Code

Service Invoice/Credit - Details - Line Item - Posting Details

nvarchar(2)

VATAmount

VAT Amount

Service Invoice/Credit - Details - Item Line

decimal(18, 2)

ExchRate

Exchange Rate

decimal(18, 2)

If left blank will use Sage default exchange rate (where Foreign Trader enabled in Sage)

NetValueDiscount

Net £

Product Invoice/Credit - Details - Deduction

decimal(18, 2)

NetValueDescription

Description

Product Invoice/Credit - Details - Deduction

nvarchar(60)

NomCode

Nominal Code

Service Invoice/Credit - Details - Line Item - Posting Details

nvarchar(8)

Notes1

Notes Line 1

Service Invoice/Credit - Order Details - Notes

nvarchar(60)

Notes2

Notes Line 2

Service Invoice/Credit - Order Details - Notes

nvarchar(60)

Notes3

Notes Line 3

Service Invoice/Credit - Order Details - Notes

nvarchar(60)

Analysis1

Service Invoice/Credit - Order Details - Custom Fields

nvarchar(30)

Analysis2

Service Invoice/Credit - Order Details - Custom Fields

nvarchar(30)

Analysis3

Service Invoice/Credit - Order Details - Custom Fields

nvarchar(30)

TakenBy

Order Taken By

Service Invoice/Credit - Order Details - Customer Order Details

nvarchar(60)

Dept

Department

Service Invoice/Credit - Footer Details - Global

nvarchar(2)

LineInfo

Line Information

Service Invoice/Credit - Details - Item Line - Additional Information

nvarchar(60)

Not message items

ProjectCode

Project

Service Invoice/Credit - Details - Item Line - Project Details

nvarchar(8)

DelName

Delivery Name

Service Invoice/Credit - Order Details - Delivery Address

nvarchar(60)

DelAdd1

Del Address 1

Service Invoice/Credit - Order Details - Delivery Address

nvarchar(60)

DelAdd2

Del Address 2

Service Invoice/Credit - Order Details - Delivery Address

nvarchar(60)

DelAdd3

Del Address 3

Service Invoice/Credit - Order Details - Delivery Address

nvarchar(60)

DelAdd4

Del Address 4

Service Invoice/Credit - Order Details - Delivery Address

nvarchar(60)

DelAdd5

Del Address 5

Service Invoice/Credit - Order Details - Delivery Address

nvarchar(60)

Name

Add Line 1

Service Invoice/Credit - Details

nvarchar(60)

Customer default address used if all blank

Add1

Add Line 2

Service Invoice/Credit - Details

nvarchar(60)

Add2

Add Line 3

Service Invoice/Credit - Details

nvarchar(60)

Add3

Add Line 4

Service Invoice/Credit - Details

nvarchar(60)

Add4

Add Line 5

Service Invoice/Credit - Details

nvarchar(60)

Add5

Add Line 6

Service Invoice/Credit - Details

nvarchar(60)

Contact

Customer Contact

Service Invoice/Credit - Order Details - Customer Order Details

nvarchar(30)

Telephone

Customer Tel. No

Service Invoice/Credit - Order Details - Customer Order Details

nvarchar(30)

Proforma

On invoices only - defines whether this order will be entered as a Pro-forma rather than standard invoice. Note that if any line on an order has Yes in this field the invoice will be entered as a Pro-forma.

CarriageNet

Net

Service Invoice/Credit - Footer - Carriage

nvarchar(8)

CarriageTAxCode

Tax Code

Service Invoice/Credit - Footer - Carriage

nvarchar(2)

CarriageNomCode

N/C

Service Invoice/Credit - Footer - Carriage

nvarchar(8)

CarriegDept

Department

Service Invoice/Credit - Footer - Carriage

nvarchar(2)

DueDate

Due On

Service Invoice/Credit - Details

datetime

Status

N/A

nvarchar(8)

Used internally and not uploaded to Sage. Set to 'New', 'Error' or 'Sent' normally

PaymentRef

Payment Ref

nvarchar(30)

Invoices only

PaymentBankCode

Bank Code

nvarchar(8)

PaymentAmount

Payment Amount

decimal(18, 2)

PaymentType

nvarchar(2)

If you have selected 'Update Ledgers' in Configuration then invoices/credit notes will be posted to the ledger if possible

Payment details are for invoices only. The highest amount for any Invoice will be used (MAX function) and the total used will include any carriage and discount amounts on the gross amount for the invoice.

Script to create the table(s)...

CREATE TABLE [dbo].[ServiceInvoices](

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

[AccountRef] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[InvRef] [nvarchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[OrderNo] [nvarchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CustOrderNo] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Date] [datetime] NULL,

[Details] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Description] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

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

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

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

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

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

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

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

[NetValueDescription] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[NomCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Notes1] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Notes2] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Notes3] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

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

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

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

[TakenBy] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

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

[LineInfo] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ProjectCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DelName] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DelAdd1] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DelAdd2] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DelAdd3] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DelAdd4] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DelAdd5] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Name] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Add1] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Add2] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Add3] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Add4] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Add5] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

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

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

[Proforma] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

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

[CarriageTaxCode] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CarriageNomCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

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

[DueDate] [datetime] NULL,

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

[PaymentBankCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

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

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

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

CONSTRAINT [PK_ServiceInvoices] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

----

CREATE TABLE [dbo].[ServiceCredits](