Product Invoices/Credits

Parent Previous Next


Product Invoice/Credit and Credits use the same fields. Again the combination of AccountRef/InvRef controls the creation of each new inv/credit with identical items appearing as detail lines. Note that if the UnitPrice field is blank then this  will be looked up from the stock record and Value and VATAmount calculated accordingly.


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

Product Invoice/Credit - Details

nvarchar(8)

The customer account ref this invoice is for

InvRef

Inv. No.

Product 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 grouped on an invoice/credit as detail lines. 

OrderNo

Order No.


nvarchar(7)


CustOrderNo

Customer Order No.

Product Invoice/Credit - Order Details - Customer Order Details

nvarchar(60)


Date

Date

Product Invoice/Credit - Details

datetime


Product

Product Code

Product Invoice/Credit - Details - Item Lines

nvarchar(30)


Qty

Quantity

Product Invoice/Credit - Details - Item Lines

decimal(18, 2)


UnitPrice

Price

Product Invoice/Credit - Details - Item Lines

decimal(18, 2)


Value

Net Amount

Product Invoice/Credit - Details

decimal(18, 2)


DiscountAmount

Discount Amount

Product Invoice/Credit - Details - Values

decimal(18, 2)


TaxCode

Tax Code

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

nvarchar(2)


VATAmount

VAT Amount

Product 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)

NomCode

Nominal Code

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

nvarchar(8)


NetValueDiscount

Net £

Product Invoice/Credit - Details - Deduction

8


NetValueDescription

Description

Product Invoice/Credit - Details - Deduction

60


Notes1

Notes Line 1

Product Invoice/Credit - Order Details - Notes

nvarchar(60)


Notes2

Notes Line 2

Product Invoice/Credit - Order Details - Notes

nvarchar(60)


Notes3

Notes Line 3

Product Invoice/Credit - Order Details - Notes

nvarchar(60)


Analysis1

Analysis1

Product Invoice/Credit - Order Details - Custom Fields

nvarchar(30)


Analysis2

Analysis2

Product Invoice/Credit - Order Details - Custom Fields

nvarchar(30)


Analysis3

Analysis3

Product Invoice/Credit - Order Details - Custom Fields

nvarchar(30)


TakenBy

Order Taken By

Product Invoice/Credit - Order Details - Customer Order Details

nvarchar(30)


Dept

Department

Product Invoice/Credit - Footer Details - Global

nvarchar(2)


ProjectCode

Project Ref

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

nvarchar(8)


Comment1

Comment 1

Product Invoice/Credit - Details - Item Line

nvarchar(60)

Not special items

Comment2

Comment 2

Product Invoice/Credit - Details - Item Line

nvarchar(60)

Not special items

LineInfo

Line Information

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

nvarchar(60)

Not message items

Units

Units

Product Invoice/Credit - Details - Item Line

nvarchar(8)

Not message items

Description (for message/special lines)

Description

Product Invoice/Credit - (Details) - Item Line

nvarchar(60)

Special/message items only

Details (for special items)

Details

Product Invoice/Credit - Details - Item Line

nvarchar(240)

Special items only

DelName

Delivery Name

Product Invoice/Credit - Order Details - Delivery Address

nvarchar(60)


DelAdd1

Del Address 1

Product Invoice/Credit - Order Details - Delivery Address

nvarchar(60)


DelAdd2

Del Address 2

Product Invoice/Credit - Order Details - Delivery Address

nvarchar(60)


DelAdd3

Del Address 3

Product Invoice/Credit - Order Details - Delivery Address

nvarchar(60)


DelAdd4

Del Address 4

Product Invoice/Credit - Order Details - Delivery Address

nvarchar(60)


DelAdd5

Del Address 5

Product Invoice/Credit - Order Details - Delivery Address

nvarchar(60)


Name

Address Line 1


nvarchar(60)

Customer default address used if all blank

Add1

Address Line 2

Product Invoice/Credit - Details

nvarchar(60)

"

Add2

Address Line 3

Product Invoice/Credit - Details

nvarchar(60)

"

Add3

Address Line 4

Product Invoice/Credit - Details

nvarchar(60)

"

Add4

Address Line 5

Product Invoice/Credit - Details

nvarchar(60)

"

Add5

Address Line 6

Product Invoice/Credit - Details

nvarchar(60)

"

Contact

Customer Contact

Product Invoice/Credit - Order Details - Customer Order Details

nvarchar(30)


Telephone

Customer Tel. No

Product Invoice/Credit - Order Details - Customer Order Details

nvarchar(30)


Proforma



nvarchar(3)

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

Product Invoice/Credit - Footer - Carriage

nvarchar(8)


CarriageTaxCode

Tax Code

Product Invoice/Credit - Footer - Carriage

nvarchar(2)


CarriageNomCode

N/C

Product Invoice/Credit - Footer - Carriage

nvarchar(8)


CarriageDept

Department

Product Invoice/Credit - Footer - Carriage

nvarchar(2)


DueDate

Due On

Product Invoice/Credit - Details

datetime


Status

N/A

N/A

nvarchar(8)

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

PaymentRef

Payment Ref

Product Invoice - Payment - Payment Details

nvarchar(30)

Invoices Only

PaymentBankCode

Bank Code

Product Invoice - Payment - Payment Details

nvarchar(8)

"

PaymentAmount

Payment Amount

Product Invoice - Payment - Payment Details

decimal(18, 2)

"

PaymentType


Product Invoice - Payment Type

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)...


USE [E2S50Data]

GO

/****** Object:  Table [dbo].[ProductInvoices]    Script Date: 07/16/2015 15:35:11 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[ProductInvoices](

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

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

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

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

       [Value] [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](30) 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,

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

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

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

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

       [Description (for message/special lines)] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [Details (for special items)] [nvarchar](240) 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,

       [ConsignNo] [nvarchar](30) 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_ProductInvoices_Status]  DEFAULT (N'New'),

 CONSTRAINT [PK_productInvoices] PRIMARY KEY CLUSTERED 

(

       [ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]



----


CREATE TABLE [dbo].[ProductCredits](

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

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

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

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

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

       [Value] [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](30) 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,

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

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

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

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

       [Description (for message/special lines)] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [Details (for special items)] [nvarchar](240) 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,

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

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

 CONSTRAINT [PK_productCredits] PRIMARY KEY CLUSTERED 

(

       [ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]