30. Products

Owned by Alan Gilchrist
Last updated: Jun 09, 2023
5 min read

Products are the items which you sell

Template Download

Some files contain example data in them, you should remove this before submitting back to us for loading.

Template / Example File

 

Key Field Information

Further Reading about field types and notes are contained in the expand sections below, expand if you need to know more details.

Field Notes

  • Default will be used unless you enter something in a field marked as DEFAULT

  • The CL column indicates the code list that this list can be found in within the document.

  • Mandatory fields are marked on the excel spreadsheets and in RED below

  • The field names in the import document can not be changed as these are used for matching.

Field Type Information

Name

Description

BIT

0 is NO / 1 is YES
BIT fields must have an entry and can not be blank

Varchar(50)

Is an alpha numeric field of length 50, this can accept letters, number and symbols

INT

Is a single number field   (no decimals)  e.g. 1,2,3,4,5
Fields marked as INT must have an entry

DECIMAL (8,5)

Is a decimal field which can contain 8 before and 5 after the .dot separator

 

Data Field Definition

Name

Type and Limitations

Mandatory

CodeList

Notes

ProductCode

[varchar](32)

YES UNIQUE

 

 The SKU or Part number for the product

LWIN18

[varchar](18)

 

 

optional LWIN Product Code
 LWIN18

ProductGroup

[int]

YES

12

See Code List

VATCode

[tinyint]

YES

1

Use S for Standard

Otherwise see Code list

CommodityCode

[int]

YES

13

Use Code List

CountryOfOrigin

[int]

YES

2

ISO  2 digit codes for the country e.g. GB, US, FR, CH

FKRegion

[int]

YES

14

Use Code List

FKUnitCode

[varchar]

YES

15

Default ‘U01’

DutyRatesHeader

[int]

YES

16

Use code list

FKProductStyle

[int]

 YES

17

This is part of the initial import for Product Analysis.  You should only enter the name you specified on the product group table.

FKProductTypeAnalysis

[int]

YES 

18

This is part of the initial import for Product Analysis.  You should only enter the name you specified on the product group table.

Vintage

OPTIONS ->

YES

 

The options are
NV - Non Vintage
YYYY - The year or vintage
NA - Not applicable

FKProducer

[bigint]

YES

LU

Enter your producers code here, this is part of an earlier import

ProductDescription

[varchar](50)

YES

 

 

NonEnglishDescription

[varchar](50)

 

 

Used for non English character sets

SinglesPerUnit

[smallint]

YES

 

The minimum is 1

SingleSize

[decimal](18 5) 

YES

 

Anything not measured should be set to 1

UnitsPerPallet

[smallint]

YES

 

Please set to 1 unless you know the real figure

UnitsPerLayer

[smallint]

YES

 

Set to 1 if not known

Height

[decimal](18 5)

YES

 

Default to 0.00 if not being used

NetWeightKG

[decimal](18 5)

YES

 

REQUIRED - Use (Single Size x Singles per Unit)

GrossWeightKG

[decimal](18 5)  DEFAULT ((0))

YES

 

REQUIRED  - General consensus for wine / spirits is cardboard cases are netweight x 1.7 and wood case net weight x 2.2

StrengthABV

[decimal](18 5) 

CONDITION

 

required for liquids with alcohol

LitresLiquidPerUnit

[decimal](18 5) 

YES

 

Single Size x Singles per unit

LitresAlcoholPerUnit

[decimal](18 5) 

YES

 

Litres of Liquid x Strength

ContainerQuantity

[smallint]

YES

 

Set to 0 if not being used

MinStockLevel

[int]

YES

 

In single bottles

Set to 0 if not being used

MaxStockLevel

[bigint] 

YES

 

In single bottles

Set to 0 if not being used

MinPurchaseOrderQuantity

[smallint]

YES

 

In single bottles

Set to 0 if not being used

MinSalesOrderQuantity

[smallint]

YES

 

In Single bottles

Set to 0 if not being used

ProductLife

[int] 

YES

 

In Days

Set to 0 if not being used

LeadTime

[int]

YES

 

In Days

Set to 0 if not being used

CigarettesPerUnit

[int] 

YES

 

required if cigarettes or tobacco

If not used set to 0

CigarettesSellingPricePerUnit

[decimal](18 5)

YES

 

MANDATORY if cigarettes or tobacco

If not used set to 0.00

KilogramsPerUnit

[decimal](18 5)

YES

 

Mandatory for Tobacco products

If not used set to 0.00

PriceIncVAT

[bit] 

YES

 

Sales Prices include Sales Tax
DO NOT USE unless a pure retail operation

Otherwise set to 0

SalesPricePerSingle

[decimal](18 5) 

YES

 

Has to have 0.00 as minimum

 This is the duty paid sales price of a bottle / single item, generally the highest price that would be paid

SalesPricePerUnit

[decimal](18 5) 

YES

 

Has to have 0.00 as minimum

 This is the duty paid sales price of a case, generally the highest price that would be paid.

UnderBondSalesPricePerUnit

[decimal](18 5) 

 

 

Has to have 0.00 as minimum

Not used but needs to be 0.00

PurchaseCostPerUnit

[decimal](18 5) 

 

 

Recommended

Has to have 0.00 as minimum

The usual Underbond Buying price of this product per case (unit)

If this is not an underbond product then it will be the same as the “duty paid purchase costs per unit” field

PurchaseCostPerSingle

[decimal](18 5) 

 

 

Has to have 0.00 as minimum

The usual Underbond buying price of this product per bottle / single

 If this is not an underbond product then it will be the same as the “duty paid purchase costs per single” field

DutyPaidPurchaseCostPerUnit

[decimal](18 5)

 

 

Has to have 0.00 as minimum

The product cost per unit plus the duty element. 

DutyPaidPurchaseCostPerSingle

[decimal](18 5)

 

 

Has to have 0.00 as minimum

The product cost per single plus the duty element. 

WebExclusion

[bit] 

YES

 

Web Export flag – on web csv
Flag to exclude from the website  (e.g. may be a special order product or EP which you do not want in general web sales

Set to 0 to include this in a web extract

1 will exclude it

IncludeIfZeroStock

[bit] 

YES

 

Web Export flag – on web csv
E.g. flag to show whether to include this item if there is no stock available

0 – will not include it / 1 – will include it

WebSingles

[bit] 

YES

 

Web Export flag – on web csv
Flag to show whether to sell this item in single (bottles) otherwise it will only sell by the unit (case)

0 – will specify singles not sold / 1 – specify single bottles will be sold

VisionSupplier

[int]

 

 

Enter the unique code for the supplier.  If there is no default then this can be left empty

EANCode

[varchar](50)

 

 

optional   ANA EAN Code

EANSingleCode

[varchar](50)

 

 

optional   ANA EAN Code

WhseSubmissionRequired

[bit]

YES

 

Does this product need to be sent to the warehouse in the product extract

Default should be 0

SORotationMandatory

[bit]

YES

 

Does this product ALWAYS ask for a rotation number when placed onto an order

Default should be 0

CaseBreakSalesAllowed

[bit]

YES

 

Does this product allow sales of single items from the case e.g. Open the Case

Default should be 1

IF this is a mixed case then it should be 0

UsePackageQuantity

[bit]

YES

 

Not currently in use

Default is however required which is 0

ISRetailProduct

[bit]

YES

 

Only set this to YES when the product is not a stocked controlled product.

Primary use was for Horizon, it would be a non stocked item which you can sell in ePOS

Default would be 0

AutoGeneratedProductCode

[bit]

YES

 

Default to 0

As you are providing all the codes for the products

UseEPOSCaseMethod

[bit]

YES

 

Reserved for Future use with Horizon ePOS solution.

Default is 0

HasChanges

[bit]

YES

 

Default to 0

GrowersAlloc

[bit]

YES

 

Default to 0

Unless you have a specific quantity that you are allowed to buy from the producer

IsSerialNumberRequired

[bit]

YES

 

RETAIL HORIZON USE

Requires the bottle number / batch to be recorded when the sale is made

Defaults to 0

CarriagePriceperUnit

Decimal (18,2)

YES

 

No longer used

Set to 0

CIFPriceperUnit

Decimal (18,2)

YES

 

No longer used

Set to 0

LabourCostperUnit

Decimal (18,2)

YES

 

Used for MIXED product build (BOM)

If not used set to 0.00

OverheadCostperUnit

Decimal (18,2)

YES

 

Used for MIXED product build (BOM)

If not used set to 0.00

Delisted

bit

YES

 

Default is 0

Set to 1 if this product is delisted

UnitorSingle

bit

YES

 

This field is reserved for future use, for the moment please insert a 0

PackPrice

Decimal (18,2)

YES

 

BOM Products only, the pack price

If not known then use 0.00 or if not used then use 0.00

BuyingPriceperUnit

Decimal (18,2)

YES

 

If you have a specific supplier, this is the price you would poy underbond per case for the product in the suppliers currency

Enter 0.00 if not used 

BuyingPriceperSingle

Decimal (18,2)

YES

 

If you have a specific supplier, this is the price you would poy underbond per single for the product in the suppliers currency

Enter 0.00 if not used

UPCCaseCode

[varchar](50)

 

 

optional UPC Code

UPCSingleCode

[varchar](50)

 

 

optional UPC Code

UDFAnalysis1

 [varchar][8]

 

19

These are only included with the Enterprise Implementation

 

These are all values you may have set at configuration time if you wanted additional analysis for your products.

 

You need to use the code list that you supplied.

UDFAnalysis2

  [varchar][8]

 

 

UDFAnalysis3

  [varchar][8]

 

 

UDFAnalysis4

  [varchar][8]

 

 

UDFAnalysis5

  [varchar][8]

 

 

Copyright Ontech Solutions 2017-2024. All rights reserved, no part may be replicated or distributed without the express permission of the owner.