Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 8 Next »

Use

Stock Import, a product has multiple stock records, each record should have a unique rotation number.

Limitations

The following has to exist already

  • Customer

  • Product

Optionally the following may be needed

  • Reserve Customer

Notes

This import originated from an older system so some of the fields are not relevant but remain as part of the standard import tables.        

[Item could be derived from Product if needed]

[Item only optional not mandatory]

Fields

Data Type

Data Code

Description

Example Data

Alpha

A

Alpha Only, no numeric allowed at all, must only be characters.

BLUE

Alpha Numeric

Also known as
Char (xx)
varchar (xx)
nvarchar (xx)

AN

Stores alphanumeric combinations and text.

No symbols or other characters allowed with some basic exceptions which are dot,comma, colon, ampersand.   
Remember some of these have special codes for XML

Bytesize 1234

String

ST

String is really a group of characters stored together as one it can contain numbers, letters and symbols.

Hello World ! [2023]

Bit

B

Boolean data types are very straightforward. A Boolean data type is simply something that only has two possible values, true or false.

The computer will store true as a 1 and false as a 0.

Y or N 

0 or 1

Character

CH

Stores a single character which can be a letter, number or symbol

£

Currency

CU

Same as decimal but to 2dp is required

17.01

Date

DT

dd/mm/yy or dd/mm/yyyy

18/05/18

Decimal

DC

Stores numbers that contain decimal places/values and can also store integers

known as REAL, SINGLE, DOUBLE or FLOAT instead.

17.65665

Email

EMl

Should be standard email format containing the @

sales@here.uk

Integer

IN

Stores positive or negative whole numbers

17

Numeric

N

Numbers only, including phone numbers

01324 722663

Time

TM

hh:mm

11:00

Field Name

Field Type

Nulls

Notes

RotationNumber

nvarchar(10)

Mandatory

The format of the rotation number is

YY/D000000

YY is the year

D is the duty indicator at the time of receipt  D = Duty Paid 0 = Underbond

000000 is a sequential number starting at 0

RotationLine

int

Line number can be blank and then starting from 1 upwards.

If using rotation lines the underlying Product must be the same

CustomerRef

nvarchar(6)

Mandatory

Must exist in the customer table

ProductCode

nvarchar(50)

Mandatory

Must exist in the product table

DateReceived

date

Mandatory

Format dd/mm/yyyy or dd/mm/yy

ReceiptType

nvarchar(2)

Mandatory

Receipt Type we are looking for the CODE in RED

InBondValue

decimal(8.2)

Conditional

Must be provided for underbond goods but optional for all others

InBondValueCurrency

nvarchar(3)

Conditional

https://visionsoftware.atlassian.net/wiki/spaces/IMP/pages/3183116289/WMS+Import+Code+Lists#Currencies we are looking for the CODE in RED

InsuranceValue

decimal(8.2)

NULL

InsuranceValueCurrency

nvarchar(3)

NULL

https://visionsoftware.atlassian.net/wiki/spaces/IMP/pages/3183116289/WMS+Import+Code+Lists#Currencies we are looking for the CODE in RED

FreightValue

decimal(8.2)

NULL

FreightValueCurrency

nvarchar(3)

NULL

https://visionsoftware.atlassian.net/wiki/spaces/IMP/pages/3183116289/WMS+Import+Code+Lists#Currencies we are looking for the CODE in RED

Strength

decimal (3.2)

NULL

ABV%

LOL

decimal (8.5)

Mandatory

Litres of Liquid / Case - where this is no a liquid then use 1

LOA

decimal (8.5)

Conditional

Litres of Alcohol / Case - must be provided if strength abv provided

1000perCase

int

Conditional

Integer field whole numbers only, for cigarettes

KgPerCase

decimal (8.5)

Conditional

Decimal 18,5   for tobacco

FreeCirculation

bit

Mandatory

UKMade

bit

Mandatory

RentDate

date

Rent start date.  Only applicable if an advanced date is required for the start of rent charging

FAFSatisfied

bit

No longer used

SinglesPerCase

int

Mandatory

must be > 1

ChangeofOwnership

bit

Mandatory

DutyPaidStock

bit

Mandatory

CustomsDutyPaidUK

bit

Mandatory

QuotaNumber

nvarchar(50)

 

COO

nvarchar(2)

Mandatory

ISO 3166 alpha-2 code required for the country of origin

CWC

nvarchar(2)

Mandatory

ISO 3166 alpha-2 code required for the country whence consigned

ReceiptDetails1

nvarchar(50)

Free Text -Arc or Warrant

ReceiptDetails2

nvarchar(50)

Free Text – Ship

ReceiptDetails3

nvarchar(50)

Free Text

ReserveCustomer

nvarchar(8)

Must exist in the reserve customers table

BestBeforeDate

date

 

ReceiptDeliveryType

nvarchar(3)

Mandatory

Receipt Delivery Types we are looking for the CODE in RED

TaxCode

nvarchar(3)

DutyStampsApplied

bit

Mandatory

CommodityCode

nvarchar(20)

Mandatory

ProductType

nvarchar(1)

Yes

Product Types we are looking for the CODE in RED

CustomerBatchNo

nvarchar(50)

Batch number

SellByDate

date

 

GrossWeight

decimal(8,2)

 Weight in kg for the case

Vintage

nvarchar(10)

Mandatory

 Options are one of

  • NV

  • NA

  • 4 digit year

TradeTerm

nvarchar(5)

Mandatory

Customs or Excise Goods - Usually A or B
Determines whether Product OR PIF (CIF) is needed
https://visionsoftware.atlassian.net/wiki/spaces/IMP/pages/3183116289/WMS+Import+Code+Lists#Trade-Terms we are looking for the CODE in RED

CigarettesPerUnit

int

Conditional

 Cigarettes only - depends on what unit of measure you intend to use for this type of product.

FreeRent

bit

Mandatory

PreferenceCertificateNumber

nvarchar(50)

 

PackagingType

nvarchar(8)

Mandatory

 Packaging Types we are looking for the CODE in RED

DegreePlato

decimal(5,2)

 

CFSPDocumentCode

nvarchar(2)

 

OriginalReceiptDate

date

 

VICertificateNumber

nvarchar(50)

 

VICertificateFKCFSPDocumentCode

nvarchar(50)

 

FirstDateReceivedToStore

date

 

Currency_VATValueAdjustment

nvarchar(3)

https://visionsoftware.atlassian.net/wiki/spaces/IMP/pages/3183116289/WMS+Import+Code+Lists#Currencies we are looking for the CODE in RED

LicenseRequired

bit

Mandatory

NilVATApplicable

nvarchar(50)

Mandatory

PresentBottles

int

Integer Value – do not include frozen quantity within this

PresentCases

int

Integer Value – do not include frozen quantity within this

VATValueAdjustment

decimal (8.2)

BottlesFrozen

int

SiteCode

nvarchar(2)

Mandatory

Must be one of the pre-agreed site codes

SingleSize

decimal(8.5)

Mandatory

Where no applicable use 1.00 otherwise Single size, in LITRES, must be greater than 0

FKSupplierOption

int

NULL

Integer Value assigning to tblStock.FKSupplierOption – pointing to systblSupplierOption.PKSupplierOption

  • No labels