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]
Fields
Field Name | Field Type | Nulls | Notes | |||||
RotationNumber | nvarchar(5010) | NULLMandatory | 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) | NULLint | Line number can be blank and then starting from 1 upwards. If using rotation lines the underlying Product must be the same | |||||
CustomerRef | nvarchar(506) | NULLMandatory | Must exist in the customer table | |||||
ProductCode | nvarchar(50) | NULLMandatory | Must exist in the product table | |||||
DateReceived | nvarchar(50) | NULLdate | Mandatory | Format dd/mm/yyyy or dd/mm/yy | ||||
ReceiptType | nvarchar(502) | NULL | HMRC 2 digit receipt type code or Mandatory | |||||
InBondValue | decimal(8.2) | NULL | Money value 0.00Conditional | Must be provided for underbond goods but optional for all others | ||||
InBondValueCurrency | nvarchar(3) | NULL | Three digit ISO code e.g. USD, GBP, EURConditional | |||||
InsuranceValue | decimal(8.2) | NULL | Money value 0.00 | |||||
InsuranceValueCurrency | nvarchar(3) | NULLThree digit ISO code e.g. USD, GBP, EUR | ||||||
FreightValue | decimal(8.2) | NULL | Money value 0.00 | |||||
FreightValueCurrency | nvarchar(3) | 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) | |||||||
Strength | decimal (3.2) | NULL | ABV% | |||||
LOL | decimal (8.5) | Mandatory | All items if no Litres of Liquid then 1 | |||||
LOA | decimal (8.5) | Conditional | If strength abv provided | |||||
1000perCase | int | Conditional | Integer field whole numbers only, for cigarettes | |||||
KgPerCase | nvarchardecimal (508.5) | NULLConditional | Decimal 18,5 for tobacco | |||||
FreeCirculation | nvarchar(50) | NULL | true / false (case sensitive!)bit | Mandatory | ||||
UKMade | nvarchar(50) | NULL | True / False | RentDate | nvarchar(50) | NULLbit | Mandatory | |
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 | |||||
bit | No longer used | |||||||
SinglesPerCase | int | Mandatory | must be > 1 | |||||
ChangeofOwnership | bit | Mandatory | ||||||
DutyPaidStock | bit | Mandatory | ||||||
CustomsDutyPaidUK | bit | Mandatory | ||||||
QuotaNumber | nvarchar(50)NULL |
| ||||||
COO | nvarchar(502) | NULLMandatory | ISO 3166 alpha-2 code required for the country of origin | |||||
CWC | nvarchar(502) | NULLMandatory | ISO 3166 alpha-2 code required for the country whence consigned | |||||
ReceiptDetails1 | nvarchar(50)NULL | Free Text -Arc or Warrant | ||||||
ReceiptDetails2 | nvarchar(50) | NULL | Free Text – Ship | |||||
ReceiptDetails3 | nvarchar(50)NULL | Free Text | OriginalRotationNonvarchar(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(508)NULL | Must exist in the reserve customers table | CustomsPaymentDocumentReference | nvarchar(50)NULL | If known | |||
CustomsPayDate | nvarchar(50) | NULL | If known | |||||
BestBeforeDate | nvarchar(50) | NULL |
| |||||
SupplierCode | nvarchar(50) | NULL |
| |||||
AlternativeProduct | nvarchar(50) | NULL |
| |||||
LotMark | nvarchar(50) | NULL | Not used | |||||
BestBeforeDate | date |
| ||||||
ReceiptDeliveryType | nvarchar(503) | 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 Mandatory | |
TaxCode | nvarchar(503) | NULL | Is required | |||||
DutyStampsApplied | nvarchar(50) | NULL | true / false (case sensitive!) | bit | Mandatory | |||
CommodityCode | nvarchar(5020) | NULL | Is required | |||||
DutyCategory | nvarchar(50) | NULL | HMRC Duty Category | |||||
Mandatory | ||||||||
ProductType | nvarchar(501) | NULL | As per the product - B / T/ W / S / I / Z / C etcYes | |||||
CustomerBatchNo | nvarchar(50) | NULL | Batch number | |||||
SellByDate | datetimedate | NULL |
| |||||
GrossWeight | nvarchardecimal(508,2)NULL |
| ||||||
Vintage | nvarchar(10) | NULL |
| Mandatory | Options are one of
| |||
TradeTerm | nvarchar(5) | NULL | A/ B / E | |||||
CigarettesPerUnit | nvarchar(50) | NULL |
| |||||
FreeRent | nvarchar(10) | NOT NULLMandatory | Customs or Excise Goods - Usually A or B | |||||
CigarettesPerUnit | int | Conditional | Cigarettes only | |||||
FreeRent | bit | Mandatory | true / false (case sensitive!) | |||||
PreferenceCertificateNumber | nvarchar(50)NULL |
| ||||||
PackagingType | nvarchar(58) | NULLMandatory | ||||||
DegreePlato | decimal(5,2) | NULL |
| |||||
CFSPDocumentCode | nvarchar(2)NULL |
| ||||||
OriginalReceiptDate | nvarchar(50) | NULLdate |
| |||||
VICertificateNumber | nvarchar(50) | NULL |
| |||||
VICertificateFKCFSPDocumentCode | nvarchar(50) | NULL |
| |||||
FirstDateReceivedToStore | nvarchar(50) | NULLdate |
| |||||
Currency_VATValueAdjustment | nvarchar(503) | NULL | Three digit ISO code e.g. USD, GBP, EUR | LicenseRequired | nvarchar(50) | NULL | true / false (case sensitive!)||
LicenseRequired | bit | Mandatory | ||||||
NilVATApplicable | nvarchar(50) | NULL | True / FalseMandatory | |||||
PresentBottles | nvarchar(50) | NULLint | Integer Value – do not include frozen quantity within this | |||||
PresentCases | nvarchar(50) | NULLint | Integer Value – do not include frozen quantity within this | ReceiptDocRef | nvarchar(50)NULL |
| ||
UCRReference | nvarchar(50) | NULL |
| |||||
UCRSubReference | nvarchar(50) | NULL |
| |||||
VATValueAdjustment | nvarchardecimal (508.2)NULL | Decimal value | ||||||
BottlesFrozen | nvarchar(50) | NULL | Integer Valueint | |||||
SiteCode | nvarchar(502) | NOT NULLMandatory | Must be one of the pre-agreed site codes | |||||
SingleSize | nvarchardecimal(508.5) | NULL | Single (bottle) 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 |
...