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 | AN | Stores alphanumeric combinations and text. No symbols or other characters allowed with some basic exceptions which are dot,comma, colon, ampersand. | 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 |
EMl | Should be standard email format containing the @ | ||
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
|
TradeTerm | nvarchar(5) | Mandatory | Customs or Excise Goods - Usually A or B |
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 |