Use
Stock Import, a product has multiple stock records, each record should have a unique rotation number.
Limitations
The following has to exist already
...
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 only optional not mandatory]
You can copy the section below and paste this into an excel spreadsheet to populate the columns, any additional columns shown when pasting which are not in the definition below are surplus to requirement and not needed, you can leave those or simply remove them from the spreadsheet.
SiteCode | CustomerCode | ProductCode | RotationNumber | RotationLineNo | DateReceived | FKReceiptCode | ProductCostperUnit | ProductCostCurrency | InsuranceCostperUnit | InsuranceCostCurrency | FreightCostperUnit | FreightCostCurrency | StrengthABV | UnitsPerPallet | ISFreeCirculation | RentDate | SinglesPerUnit | HasOwnerChangeinUK | ISExciseDutyPaidinUK | CustomsDutyPaidUK | QuotaNumber | CWC | ReceiptDetails1 | ReceiptDetails2 | ReceiptDetails3 | ReserveCustomer | BestBeforeDate | FKDeliveryType | TaxCode | DutyStampsApplied | CommodityCode | CustomerBatchNo | GrossWeight | Vintage | TradeTerm | CigarettesPerUnit | FreeRent | ProductionDate | PackagingType | DegreePlato | OriginalReceiptDate | FirstDateReceivedToStore | Currency_VATValueAdjustment | NilVATApplicable | VATValueAdjustment | SingleSize |
Fields
| Include Page | ||||
|---|---|---|---|---|
|
Field Name | Field Type | Nulls | Notes |
|---|
[RotationNumber]
[nvarchar](50)
SiteCode | nvarchar(2) | Mandatory | Must be one of the pre-agreed site codes |
CustomerCode | nvarchar(6) | Mandatory | Must exist in the customer table |
ProductCode | nvarchar(50) | Mandatory | Must exist in the product table |
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]
[nvarchar](50)
NULL
RotationLineNo | 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](50)
NULL
Must exist in the customer table
[nvarchar](50)
NULL
Must exist in the product table
DateReceived |
[nvarchar](50)
date | Mandatory | Format dd/mm/yyyy or dd/mm/yy |
FKReceiptCode |
nvarchar |
( |
2) |
NULL
HMRC 2 digit receipt type code or
[InBondValue]
[nvarchar](50)
NULL
Money value 0.00
[InBondValueCurrency]
[nvarchar](50)
NULL
Three digit ISO code e.g. USD, GBP, EUR
[InsuranceValue]
[nvarchar](50)
NULL
Money value 0.00
[InsuranceValueCurrency]
[nvarchar](50)
NULL
Three digit ISO code e.g. USD, GBP, EUR
[FreightValue]
[nvarchar](50)
NULL
Money value 0.00
[FreightValueCurrency]
[nvarchar](50)
NULL
Three digit ISO code e.g. USD, GBP, EUR
[Strength]
[nvarchar](50)
NULL
Decimal field 8,3
[LOL]
[nvarchar](50)
NULL
Decimal field 18,5
[LOA]
[nvarchar](50)
NULL
Decimal field 18,5
[1000perCase]
[nvarchar](50)
NULL
Integer field whole numbers only, for cigarettes
[KgPerCase]
[nvarchar](50)
NULL
Decimal 18,5 for tobacco
[FreeCirculation]
[nvarchar](50)
NULL
true / false (case sensitive!)
[UKMade]
[nvarchar](50)
NULL
True / False
[RentDate]
[nvarchar](50)
Mandatory | Receipt Type we are looking for the CODE in RED | ||
ProductCostperUnit | decimal(8.2) | Conditional | Must be provided for underbond goods but optional for all others |
ProductCostCurrency | 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 |
InsuranceCostperUnit | decimal(8.2) | NULL | |
InsuranceCostCurrency | 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 |
FreightCostperUnit | decimal(8.2) | NULL | |
FreightCostCurrency | 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 |
StrengthABV | decimal (3.2) | NULL | ABV% |
UnitsPerPallet | int | Conditional | Integer field whole numbers only, needed if your using pallets otherwise set to 1 |
ISFreeCirculation | bit | Mandatory | Has customs duty been paid, if yes then 1 otherwise 0 |
RentDate | date | Rent start date. Only applicable if an advanced date is required for the start of rent charging |
[FAFSatisfied]
[nvarchar](50)
NULL
True / False
[BottlesPerCase]
[nvarchar](50)
NULL
Integer field
[ChangeofOwnership]
[nvarchar](50)
NULL
true / false (case sensitive!)
[DutyPaidStock]
[nvarchar](50)
NULL
true / false (case sensitive!)
[CustomsDutyPaidUK]
[nvarchar](50)
NULL
True / False
[QuotaNumber]
[nvarchar](50)
NULL
[COO]
[nvarchar](50)
NULL
ISO 3166 alpha-2 code required for the country of origin
[CWC]
[nvarchar](50)
SinglesPerUnit | int | Mandatory | must be > 1 (Unit = Case) |
HasOwnerChangeinUK | bit | Mandatory | 1 is YES and 0 is NO |
ISExciseDutyPaidinUK | bit | Mandatory | 1 is YES and 0 is NO |
CustomsDutyPaidUK | bit | Mandatory | 1 is YES and 0 is NO |
QuotaNumber | nvarchar(50) |
| |
CWC | nvarchar(2) | Mandatory | ISO 3166 alpha-2 code required for the country whence consigned |
ReceiptDetails1 |
nvarchar |
(50) |
NULL
Header Level - Free Text -Arc or Warrant |
ReceiptDetails2 |
nvarchar |
(50) |
NULL
Header Level Free Text – Ship |
ReceiptDetails3 |
nvarchar |
(50) |
NULL
Free Text
[OriginalRotationNo]
[nvarchar](50)
NULL
Only use if you have the original rotation available.
[OriginalRotationLine]
[nvarchar](50)
NULL
[UKCustomsValue]
[nvarchar](50)
NULL
For vat value adjustment
[ReserveCustomer]
[nvarchar](50)
Header Level Free Text | |||
ReserveCustomer | nvarchar(8) | Must exist in the reserve customers table |
[CustomsPaymentDocumentReference]
[nvarchar](50)
NULL
[CustomsPayDate]
[nvarchar](50)
NULL
If known
BestBeforeDate |
[nvarchar](50)
NULL
[SupplierCode]
[nvarchar](50)
NULL
[AlternativeProduct]
[nvarchar](50)
NULL
[LotMark]
[nvarchar](50)
NULL
Not used
[ReceiptDeliveryType]
[nvarchar](50)
NULL
Was this a PUR – Purchase RET- Return - COL- Collection
If not known then use DTO – Data Take On
[NoOfPallets]
[nvarchar](50)
NULL
Cases per Pallet
[TaxCode]
[nvarchar](50)
NULL
Is required
[DutyStampsApplied]
[nvarchar](50)
NULL
true / false (case sensitive!)
[CommodityCode]
[nvarchar](50)
NULL
Is required
[DutyCategory]
[nvarchar](50)
NULL
HMRC Duty Category
[ProductType]
[nvarchar](50)
NULL
As per the product - B / T/ W / S / I / Z / C etc
[CustomerBatchNo]
[nvarchar](50)
NULL
Batch number
[SellByDate]
[datetime]
NULL
[GrossWeight]
[nvarchar](50)
NULL
[Vintage]
[nvarchar](10)
NULL
[TradeTerm]
[nvarchar](5)
NULL
A/ B / E
[CigarettesPerUnit]
[nvarchar](50)
NULL
[FreeRent]
[nvarchar](10)
NOT NULL
true / false (case sensitive!)
[PreferenceCertificateNumber]
[nvarchar](50)
NULL
[PackagingType]
[nvarchar](5)
NULL
[DegreePlato]
[decimal](5,2)
NULL
[CFSPDocumentCode]
[nvarchar](2)
NULL
[OriginalReceiptDate]
[nvarchar](50)
NULL
[VICertificateNumber]
[nvarchar](50)
NULL
[VICertificateFKCFSPDocumentCode]
[nvarchar](50)
NULL
[FirstDateReceivedToStore]
[nvarchar](50)
NULL
[Currency_VATValueAdjustment]
[nvarchar](50)
NULL
Three digit ISO code e.g. USD, GBP, EUR
[LicenseRequired]
[nvarchar](50)
NULL
true / false (case sensitive!)
[NilVATApplicable]
[nvarchar](50)
NULL
True / False
[PresentBottles]
[nvarchar](50)
NULL
Integer Value – do not include frozen quantity within this
[PresentCases]
[nvarchar](50)
NULL
Integer Value – do not include frozen quantity within this
[ReceiptDocRef]
[nvarchar](50)
NULL
[UCRReference]
[nvarchar](50)
NULL
[UCRSubReference]
[nvarchar](50)
NULL
[VATValueAdjustment]
[nvarchar](50)
NULL
Decimal value
[BottlesFrozen]
[nvarchar](50)
NULL
Integer Value
[SiteCode]
[nvarchar](50)
NOT NULL
Must be one of the pre-agreed site codes
[SingleSize]
[nvarchar](50)
NULL
date |
| ||
FKDeliveryType | nvarchar(3) | Mandatory | Receipt Delivery Types we are looking for the CODE in RED |
TaxCode | nvarchar(3) | ||
DutyStampsApplied | bit | Mandatory | 1 is YES and 0 is NO |
CommodityCode | nvarchar(20) | Mandatory | |
CustomerBatchNo | nvarchar(50) | Batch number | |
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 | |
ProductionDate | date |
| |
PackagingType | nvarchar(8) | Mandatory | Packaging Types we are looking for the CODE in RED |
DegreePlato | decimal(5,2) |
| |
OriginalReceiptDate | date |
| |
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 | |
NilVATApplicable | nvarchar(50) | Mandatory | |
VATValueAdjustment | decimal (8.2) | ||
SingleSize | decimal(8.5) | Mandatory | Where no applicable use 1.00 otherwise Single size, in LITRES, must be greater than 0 |
[FKSupplierOption]
[int]
NULL