1. Model Tree Excel file layout

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:

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

Correct combination

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:

1. 1. Get the record from "@IDMS_S_SETUP" where the "U_IDMS_Key" = 'VehiclePriceList'
   2. 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:  

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

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. 

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

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.

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.