Prerequisites related to the file format
- Every file needs to be compatible with the latest version before the import to DSW. Every new version of the file is available in the ticket: ODP-2113
- The file must be in xls format.
- All data must be in text format.
Excel sheets detailed description
a. Models
This is the basic sheet in the Model Tree excel file.
a.1. Mandatory fields in the models sheet
Model Level
The levels supported in the model tree are:
- 0 level used for the Make eg BMW
- 1 level used for the family eg Series 1
- 2 level used for the models eg 116d
DMSCode
The DMS code represents the code used by the external DMS system. It is a unique code for every record. For every DMS code one OD code is generated during the import process, meaning that the relationship is one-to-one.
About the uniqueness of DMS Code
Make: The DMS codes must be unique for the entire dataset of Makes
Family: The DMS codes must be unique for each Make. For example the following scenario is valid:
Family | Make | DMScode |
---|---|---|
Golf | VW | 123 |
Auris | Toyota | 123 |
Model: the logic is exactly the same as above. This means that duplicate DMS Codes can exist within the table MDLS, but only when the models are related to different Parents (Families).
Wrong combination
Model | Parent | DMScode |
---|---|---|
5d GTI | Golf | 123 |
5d TDI | Golf | 123 |
Correct combination
Model | Parent | DMScode |
---|---|---|
GTI | Polo | 123 |
GTI | Golf | 123 |
MakeCode
The DMS code of the Manufacturer. This code should always exist in sheet Makes located in the Model tree excel file.
FamilyCode
The DMS code of the Family. This code should always exist in sheet Families located in the Model tree excel file.
ModelParentCode, KitsParentModelCode
The DMS code for the parent.
If the level is 0 (Make) then these two fields should be empty.
If the level is 1 (Family) then these two fields must have the DMS code of the relevant Make.
If the level is 2 (Model) then these two fields must have the DMS code of the relevant Family.
Model Description
The name of the Make (in case of level 0), the name of the Family (in case of level 1), the name of the Model (in case of level 2). The description appears in leads , opportunities ,reports and generally everywhere that there is reference to the specific model level.
VATProductGroup
This field must have a value that exists in the table: IDMS_VATPRODGROUP
SHOW_IN_CONF
The possible values are 1 or 0. When the value is 1,the models will appear in the LH Configurator. When the Value is 0 the models will not be displayed in the Configurator.
FuelTypeCode
The values should exist in the sheet : FuelTypeCode. Depending on the value of the field, there is a different calculation of the VAT amount in Stock Locator. Also, the value of this field is used to decide which offer template will be used in case of AWT Tenant.
TaxGroupCode
The possible values should exist in the sheet TaxGroupCode (the field is mandatory only in coupled environments)
LaborType
Used by the DASW product.
SHOW_IN_B2C
For a Make, Family, Model to be available in the relevant OSM fields, the combination should be:
- IsEnable = YES
- Display In B2C = YES
IsEnable
This field should take the value Y, for all levels (Make, Family, or Model) when the relevant values must be available for selection in the Model Tree Control.
a.2. Non-mandatory fields in the models sheet
In the models sheet of the Excel file, the following fields are not mandatory and can be filled out based on the customer's decision. If used the values must exist in the related tables
- DriveTypeCode: The possible values should exist in the sheet DriveTypeCode
- TransmissionCode: The possible values should exist in the sheet TransmissionCode
- BodystyleCode: The possible values should exist in the sheet BodystyleCode
- Emission Class: The possible values should exist in the sheet Emission Class
- Emission Sticker: The possible values should exist in the sheet Emission Sticker
a.3. Other non-mandatory fields
Other fields that are included in the models sheet but do not have any relational sheets and are all optional are listed below:
- PresentationCode
- Model Year
- EngineCode
- Country Manufacturered
- HSN
- EngineDisplacement
- FuelConsumptionCombined
- FuelConsumptionUrban
- FuelConsumptionExtraUrban
- NumberOfCylinders
- TireSize
- NumberOfDoorsCode
- CO2EmissionCombined
- CO2EmissionCity
- CO2EmissionLongDistance
- SeatsNumber
- Axles
- TopSpeed
- Acceleration
- PowerKW
- PowerPS
- PowerHP
- Orderable
- Trunk Volume
- Towing Capacity
- Width
- Height
- Length
- Battery Capacity
- Voltage
- Drive Train
- Range on a single charge
- L1 Charge
- L2 Charge
- L3 Charge
- ModelPictureFileName
- MARKETING_DESCRIPTION
- MaxWeight
- Chassis
- EmptyWeight
- WheelBase
- MaxTorque
- Payload
- TechnicallyPermissibleMaxWeight
- TowLoad
- Length
- SortOrder
The values of this column (number) will affect the Display Order in Light House configurators.
- U_IDMS_StockLocatorExcluded
b. ModelPrices
All the fields in this sheet are mandatory
Modellevel
Usually, the value here is 2 , because the prices are provided on the level of model.
DMSCode
The code that is used by an external DMS system. The code must exist in the sheet Models.
PriceList
The price list code as it has been configured in the relevant set-up page: /item/PricelistsGridList (table: OPLN).
To fill the ModelPrices PriceList column you must do the following:
- Get the record from "@IDMS_S_SETUP" where the "U_IDMS_Key" = 'VehiclePriceList'
- Use the Value from the field "U_IDMS_Value" of this record to fill the PriceList field in Excel.
Currency
The currency code as it has been configured in the relevant set-up page: /administration/currencies
Price
The retail price.
EffectiveDateFrom
The DateTime which the price will be effective from and onwards.
This field has a string format ('YYYY-MM-DD). A valid value should be entered like '2023-01-18 with the apostrophe character in front.
If the "Effective Date From" is empty, then consider as effective date the current date. If the "Effective Date From" is NOT empty, then consider the mentioned date to apply the price.
c. Options
The following fields are mandatory:
DMSCode
The code that is used by an external DMS system. It is a unique code for every record. For every DMS code an OD code is generated , so the relationship is 1-1.
Name
The description of the option.
ModelCode
The DMS code related to this Option. Usually, it is provided on the Model level. The ModelCode must exist in the sheet Models.
IsStandard
When the value is Y is by default selected in the Configuration page of the model. If the value is N then it is optional.
IsChangeable
- When an option has IsStandard = Y and IsChangeable = Y then the field MinQuanity = 0
- When an option as IsStandard = Y & IsChangeable = N or empty then the field MinQuanity = 1
IsPackage
When the value is Y then the option is a package and parent to all the options that are included to this package. In this case the package's DMS code must be always included in the column ParentCode for all the options that are included in the package.
OptionType
Here only the following 3 types of options can be entered:
1 | Trim |
2 | Option |
3 | Color |
TaxGroupCode (coupled versions)
The values here are DMS Codes from the sheet Taxgroups (only in coupled versions)
Option Category
This field must be filled with values, and related values must exist in the sheet Option_Category
Model Tree Level
Possible values are:
Make level=0
Family level=1
Model level=2
DisplayInConfigurator
The possible values are Y or N. When the value is Y, the Options will be displayed in LH Configurator. When the value is N the Options will not be displayed in the Configurator.
VATProductGroup (decoupled versions)
The values are the Codes from the sheet VATProductGroup.
These values can be found:
/SetupEntities/List/SalesAndLeads/IDMS_VATPRODGROUP
ParentCode
To group Options under one package, the parent code value must be the DMS code of the Option that represents a Package.
OptionDefault
Possible values "Y" or "N"
CostPrice
The cost price of an option (decimal)
EffectiveDateFrom
string format ('yyyy-MM-dd) with the apostrophe character is front
d. OptionPackages
This sheet is used to relate the options with the packages.
The mandatory fields are:
PackageDMSCode
The DMScode of the package
OptionsDMSCode
The DMS code of the option. The code must exist in the sheet: Options.
ModelDMSCode
To support the combination of the same Option Package DMS code and Option DMS code for different models fields ModelDMSCode was added.
It is Mandatory if there is the same Option package for different models.
ModelLevel
To support the combination of the same Option Package DMS code and Option DMS code for different models field ModelLevel was added.
It is Mandatory if there is the same Option package for different models.
e. OptionRules
The three columns that are mandatory:
- OptionDMSCode: This is the option of DMS that gives the option to the user to create a rule, i.e. Navigation system
- LinkedOptionDMSCode: The DMS Code of the option that will be related with another option eg 8" TFT screen
- RelationType: Which is the type of the relation
The first one is the option of DMS that we want to create a rule, let’s say Navigation system, the second one is the one that we want to relate to, eg 8” TFT screen and the RelationType controls this relation.
The values it can get are.
- 1 = Option 1 IS NOT COMPATIBLE with Option 2
- 2 = Option 1 REQUIRES with Option 2
- 3 = Option 1 REQUIRES AND REQUIRED BY Option 2
- 4 = Option 1 REQUIRES ANY Option 2
- 5 = Selection of Option 1 selects AUTOMATICALLY Option 2
Example
OptionDMSCode | LinkedOptionDMSCode | RelationType |
Navigation system | 8” TFT screen | 1 |
In this example both the values 'Navigation system' and '8" TFT screen' have to be selected. In RelationType 2 , Navigation system requires 8" TFT screen .
In RelationType 3 , the relations is bi directional .
In RelationType 4, the user can add as many options for Option 2
In RelationType 5 Option 2 is automatically selected when Option 1 is selected.
f. OptionCategory
The following fields are mandatory:
DMSCode
The code that is used by an external DMS system. It is a unique code for every record. The option category is used in the sheet Options to classify each option under a specific option category. This classification is used by the LH Configurator.
Description
A general description about the category
Make
The DMScode of the Make that may have this category of options.
Sort Order
The order that the option categories will be listed in the LH Configurator.
MinQuant
This field will be filled in with the minimum value (numeric) a category can have.
MaxQuant
This field will be filled in with the maximum value (numeric) a category can have.
g. Makes
All the fields are mandatory:
DMSCode
The code that is used by an external DMS system. It is a unique code for every record. For every DMS code an OD code is generated, so the relationship is 1-1.
Name
The Name of the make, i.e. OPEL
h. Families
The following fields are mandatory:
DMSCode
The code that is used by an external DMS system. It is a unique code for the same Make DMS Code. This means that the same Family DMS code can exist more than once but in combination with different Make DMS Code.
Name
The Name of the Family.
Make DMS Code
This value is the Makecode and must exist in sheet: Makes, and in sheet: Models.
Family Group code
It is not a mandatory field. It is used to group the families under a specific group. i.e. 1-Series family group may include F10, F11, F12 families.
i. Family groups
Code
the code for the family group
Name
the name (description) of the family group
j. Fuel_Types
All the fields in this sheet are mandatory.
Code
the code of the fuel type. Can be any value set by the creator of the file. If fuel types are used in the Models sheet, the relevant code must exist in this sheet.
Name
The name (description) of the fuel type.
Business value
Predefined values as listed below.
Business value | Type |
1 | Diesel |
2 | Petrol |
3 | Electric |
4 | Gas |
5 | Hybrid |
6 | PlugInHybrid |
k. Engine_Types
Code
The code of the engine type as set by the creator of the file. If used in the sheet Models, the code must also exist in this sheet.
Name
The name (description) of the engine type
l. Drive_Types
Code
The drive type code as set by the creator of the file. It is not a mandatory field. If used in the Models sheet, the code should also exist in this sheet.
Name
The name (description) of the drive type
Example: Code: 1, Name: Four Wheel Drive (4WD)
m. Car_Types
Code
the code of the car type. Can be any value set by the creator of the file. If car types are used in the Models sheet, the relevant code must exist in this sheet.
Name
The name (description) of the car type.
Business value
Any of the predefined values as listed in the following matrix
Business Value | Name |
0 | Passenger Car |
1 | Motorbike |
2 | Truck |
3 | Van |
n. Transmission
Code
the code of the transmission as set by the creator of the file. If used in the Models sheet, the code must also exist in this sheet.
Name
The name (description) of the transmission.
example: Code: 1, Name: Automatic
o. Bodystyle
Code
the code of the bodystyle as set by the creator of the file. If used in the Models sheet, the code must also exist in this sheet.
Name
The name (description) of the body style.
example: Code: 1, Name: Coupe
p. VATProductGroup (decoupled version only)
It represents the VAT category of the model that in combination with the Business Vat Group and the Financial Group defines the VAT % that will be calculated in the sales lead offer. Examples of the VAT posting group are Tax normal, low, and exempt. These values should exist in sheets Model and Options. If the VAT % does not exist, then the default % will be used while calculating the VAT in the sales lead offer.
Code
As predefined in the table: VAT_Product_Group
Description
As predefined in the table: VAT_Product_Group
q. Emission_Sticker
If this code exists in the sheet Models, must also exist in this sheet.
Code
The code as set by the creator of the file.
Name
The name (description) as set by the creator of the file.
Example: Code: 781, Name: EURO 1
r. Emission_Class
If this code exists in the sheet Models, must also exist in this sheet.
Code
The code as set by the creator of the file.
Name
The name (description) as set by the creator of the file.
Example: Code: 2, Name: A+
s. FullOrDelta
The value of the field "IsFullUpdate" controls the behavior of the import of the file. In this sheet the fields 'Entity' and 'Full Update' are mandatory.
Entity | IsFullUpdate | MakeDMSCode |
Models | N | TY |
Options | Y | TY |
Models | Y | HY |
Options | N | HY |
The above will have as a result set the Display in configurator = 'N' for the Options with Make DMSCode = 'TY' and for the Models with Make DMSCode = 'HY'
Here the entity remains always the same and we update the fields: "IsFullUpdate" and "MakeDMSCode"
- If the field "IsFullUpdate" = Y
Before importing, all data of the specified entity ('Models' and/or 'Options'), which defines if the entity will be shown in the configurator, will be updated to 'N'. The import file must contain a full update of the specified entity with the IsFullUpdate=Y - If IsFullUpdate is N
No update of the current data will take place before importing.
Last Version of Import file
The last version of the import file is 1.32
0 Comments