Vehicles

SSIS Control Flow Chart

Load all tables to be used in this process to a cache
Import data from cache to MT Tables, after making sure they are empty. The MT Tables serve as temporary database.
Import excel data

ODVehiclesV1_1.xls Content

		Database Mapping			Lookup
Column	Description	Table	Field	DB Type	Table	Field
DMS Code	DMS Vehicle Code	@XIS_CARS	U_IDMS_DMSCode	NVARCHAR(100)
Vehicle Code	Vehicle Code	@XIS_CARS	Code	NVARCHAR(50)
U_IDMS_DIM_Company	Dimension Company	@XIS_CARS	U_IDMS_DIM_COMPANY	NVARCHAR(30)	"IDMS_COMMONDEV211"."COMPANY"
U_IDMS_DIM_Branch	Dimension Branch	@XIS_CARS	U_IDMS_DIM_Branch	NVARCHAR(30)	"IDMS_COMMONDEV211"."BRANCH"
U_IDMS_DIM_Location	Dimension Location	@XIS_CARS	U_IDMS_DIM_Location	NVARCHAR(30)	"IDMS_COMMONDEV211"."LOCATION"
U_IDMS_DIM_Make	Dimension Make	@XIS_CARS	U_IDMS_DIM_Make	NVARCHAR(30)
U_Mdl Code	Model Code	@XIS_CARS	U_MdlCode	NVARCHAR(50)	@XIS_MDLS	Code
Make	Make Code	@XIS_CARS	U_Mnfctr	NVARCHAR(50)	@XIS_MFCTCD	Code
Family	Famliy Code	@XIS_CARS	U_Grp	NVARCHAR(50)	@XIS_FMLYCR	Code
Model	Model Description	@XIS_CARS		NVARCHAR(100)
Model as a comment	Model Description jus as a comment	@XIS_CARS	U_IDMS_ModelAsAComment	NVARCHAR(254)
VIN	VIN	@XIS_CARS	U_VCode	NVARCHAR(50)
U_OwnBP	Owner BP Code	@XIS_CARS	U_OwnBP	NVARCHAR(15)	OCRD	CardCode
License plate	License Number	@XIS_CARS	U_LicNum	NVARCHAR(20)
Engine Number	Engine Number	@XIS_CARS	U_ECode	NVARCHAR(20)
Gear Number	Gear Number	@XIS_CARS	U_GCode	NVARCHAR(20)
First Registration Date	First Registration Date	@XIS_CARS	U_FstRegDate	TIMESTAMP
KBA Key	KBA Key	@XIS_CARS	U_KBAKey	NVARCHAR(100)
Interior Color - ODInteriorColor	Interior Color - IDInteriorColor	@XIS_CARS	U_IntrColor	NVARCHAR(254)	@XIS_INTRCLR	Code
Manufacturer Interior Color Code	Manufacturer Interior Color Code
Manufacturer Interior Color Name	Manufacturer Interior Color Name
Interior Type - ODInteriorType	Interior Type - ODInteriorType	@XIS_CARS	U_IntrType	NVARCHAR(254)	@XIS_INTRTYPE	Code
Exterior Color - ODExteriorColor	Exterior Color - ODExteriorColor	@XIS_CARS	U_CarColor	NVARCHAR(30)	@XIS_CAR_COLORS	Code
Vehicle Status - ODVehicleStatus	Exterior Color - ODExteriorColor	@XIS_CARS	U_Status	NVARCHAR(50)	@XIS_CARSTATUS @IDMS_CUSTAPP_PARAMS	Code U_DfltValue
Vehicle Type - ODCarType	Vehicle Type - ODCarType	@XIS_CARS	U_CarType	NVARCHAR(50)	@XIS_CARTYPES	Code
Mileage (km)	Mileage (km)	@XIS_CARS	U_LastKm	DECIMAL (21,6)
Production Year	Production Year	@XIS_CARS	U_ProdYear	NVARCHAR(4)
Drive Side - 1 or 0 numeric1	Drive Side - 1 or 2 1=Left \| 2=Right	@XIS_CARS	U_DriveSide	NVARCHAR(1)
Warranty End Date	Warranty End Date	@XIS_CARS	U_WrntyDt	TIMESTAMP
Warranty Limitation (km)	Warranty Limitation (km)	@XIS_CARS	U_WrntyKm	INTEGER
Damaged	Damaged (Y/N)	@XIS_CARS	U_Damaged	NVARCHAR(1)
Engine Displacement	Engine Displacement	@XIS_CARS	U_EngDsplt	NVARCHAR(10)	@XIS_ENGINE_DSPLT	Code
Gear Type - ODGearType	Gear Type - ODGearType	@XIS_CARS	U_GearType	NVARCHAR(50)	@XIS_GEAR_TYPE	Code
Fuel Type - ODFuelTypeCode	Fuel Type - ODFuelTypeCode	@XIS_CARS	U_FuelType	NVARCHAR(30)	@XIS_FUEL_TYPES	Code
Fuel Consumption (Combined)	Fuel Consumption (Combined)	@XIS_CARS	U_FulConCom	DECIMAL(21,6)
Fuel Consumption (Urban)	Fuel Consumption (Urban)	@XIS_CARS	U_CtyCycCon	DECIMAL(21,6)
Fuel Consumption (Extra Urban)	Fuel Consumption (Extra Urban)	@XIS_CARS	U_LngDisCon	DECIMAL(21,6)
Number Of Axles	Number Of Axles	@XIS_CARS	U_Axles	INTEGER
Cylinders	Cylinders	@XIS_CARS	U_Cylindrs	NVARCHAR(8)
Power (PS)	Power (PS)	@XIS_CARS	U_PowerPS	INTEGER
Top Speed	Top Speed	@XIS_CARS	U_TopSpeed	INTEGER
Acceleration	Acceleration	@XIS_CARS	U_Acclrtn	NVARCHAR(50)
Combined CO2 emissions (gr/km)	Combined CO2 emissions (gr/km)	@XIS_CARS	U_CO2EmsCom	DECIMAL(21,6)
Emissions Sticker	Emissions Sticker	@XIS_CARS	U_EXT_EmisSticker	NVARCHAR(50)	@XIS_EMISSTICKER	Code
Number of Vehicles Owners	Number of Vehicles Owners	@XIS_CARS	U_EXT_NmOfPrevOwnr	NVARCHAR(15)
Has Full Service History - valid values Y N	Has Full Service History - valid values Y N	@XIS_CARS	U_EXT_FullServHis	NVARCHAR(1)
Last Maintenance Date	Last Maintenance Date	@XIS_CARS	U_LstMntncDt	TIMESTAMP
Last Maintenance Odometer	Last Maintenance Odometer	@XIS_CARS	U_LstMntncOd	DECIMAL(21,6)
Next Technical Inspection Date	Next Technical Inspection Date	@XIS_CARS	U_NxtTechInsDt	TIMESTAMP
Tax Group - ODTaxGroupCode	Tax Group - ODTaxGroupCode	@XIS_CARS	U_IDMS_VatProductGroupCode	NVARCHAR(50)	@IDMS_VATPRODGROUP	Code
Has Climatisation - U_Climatisation(15)	Has Climatisation - U_Climatisation(15)	@XIS_CARS	U_Climatisation	NVARCHAR(15)
Has Park Assistance - U_ParkAssnts(15)	Has Park Assistance - U_ParkAssnts(15)	@XIS_CARS	U_ParkAssnts	NVARCHAR(15)
Metallic Colour - Y or N	Metallic Colour - Y or N	@XIS_CARS	U_IsClrMetallic	NVARCHAR(15)
Flags	Flags
Vehicle Availability status	Vehicle Availability status	@XIS_CARS	U_IDMS_AvailabilityStatus	NVARCHAR(50)
Body style - ODBodyStyleCode	Body style - ODBodyStyleCode	@XIS_CARS	U_BdyStyle	NVARCHAR(50)	@XIS_BODYSTYLE	Code
Drive type - U_4WDType	Drive type - U_4WDType	@XIS_CARS	U_4WDType	NVARCHAR(50)
Vehicle Length - U_LengthMM	Vehicle Length - U_LengthMM	@XIS_CARS	U_LengthMM	INTEGER
Vehicle Width - U_WidthMM	Vehicle Width - U_WidthMM	@XIS_CARS	U_WidthMM	INTEGER
Vehicle Height - U_HeightMM	Vehicle Height - U_HeightMM	@XIS_CARS	U_HightMM	INTEGER
Purchase date	Purchase Date	@XIS_CARS	U_PrchDate	TIMESTAMP
No of Doors	No of Doors	@XIS_CARS	U_DoorsNum	NVARCHAR(10)
No of Keys	No of Keys	@XIS_CARS	U_IDMS_NumOfKeys	INTEGER
Salable - Y or N valid values	Salable - Y or N valid values (Y/N)	@XIS_CARS	U_Salable	NVARCHAR(1)
Extended Warranty - 1 or 0 numeric1	Extended Warranty - 1 or 0 numeric1 1=Yes \| 0=No	@XIS_CARS	U_IDMS_ExtendedWarranty	SMALLINT
A/C Cleaning - 1 or 0 numeric1	A/C Cleaning - 1 or 0 numeric1 1=Yes \| 0=No	@XIS_CARS	U_IDMS_ACCleaning	SMALLINT
Road Assistance - 1 or 0 numeric1	Road Assistance - 1 or 0 numeric1 1=Yes \| 0=No	@XIS_CARS	U_IDMS_RoadAssistance	SMALLINT
Emission Check - 1 or 0 numeric1	Emission Check - 1 or 0 numeric1 1=Yes \| 0=No
Available Only For Dealer - 1 or 0 numeric1	Available Only For Dealer - 1 or 0 numeric1 1=Yes \| 0=No
VehicleImage	Vehicle image (p.e. veh1.jpg)
Branch	Branch	@XIS_CARS	U_IDMS_Branch	NVARCHAR(100)
Location	Location	@XIS_CARS	U_IDMS_Location	NVARCHAR(100)
MarketPlaceDescription		@XIS_CARS	U_IDMS_MarketPlaceDesc	NVARCHAR(4000)
Variant	MarketPlace Variant	@XIS_CARS	U_IDMS_MarketPlaceVariant	NVARCHAR(50)
EUEmissionStandard		@XIS_CARS	U_EmdStdCd	NVARCHAR(254)	@IDMS_OPTIONFIELD	WHERE U_IDMS_Entity = 'Vehicle' AND U_IDMS_Field = 'EuroClassType'

Vehicle Prices

		Database Mapping			Lookup
Column	Description	Table	Field	Data Type	Table	Field
Vehicle Code		ITM1	ItemCode	NVARCHAR(50)	OITM	ItemCode
Currency	Currency Code	ITM1	Currency
Price1		ITM1	PriceList	DECIMAL(21,6)
Price2		ITM1	PriceList	DECIMAL(21,6)
Price3		ITM1	PriceList	DECIMAL(21,6)

Options

		Database Mapping			Lookup
Column	Description	Table	Field	Data Type	Table	Field
DMSCode	Vehicle Code	@XIS_DESGINCODES	U_IDMS_DMSCode	NVARCHAR(50)
DMSCode		@XIS_DESGINCODES	Code
DMSCode		@XIS_DESGINCODES	Name
Name	Currency Code
ModelCode	Model Code
IsStandard	Is Standard Option (Y/N)
IsChangeable	Is Changeable (Y/N)	@XIS_DESGINCODES	U_IDMS_IsChangeable	NVARCHAR(1)
IsPackage	Is Package (0/1)
IncludedOptions	Included Option
CO2Adjustment	CO2 Adjustment
OptionType	OptionType	@XIS_DESGINCODES	U_IDMS_EXT_OptionType	NVARCHAR(50)
OptionCategory	Option Category	@XIS_DESGINCODES	U_IDMS_OptionCategory	NVARCHAR(50)	@IDMS_OPTIONCATEGORY	Code
TaxGroupCode	Tax Group Code	@XIS_DESGINCODES	U_IDMS_EXT_TaxGroupCode	NVARCHAR(50)
VATProductGroup	Vat Product Group
ModelTreeLevel	Model Tree Level
NetPrice	Net Price	@XIS_DESGINCODES	U_IDMS_EXT_MsrpPrice	DECIMAL(21,6)
GrossPrice	Gross Price	@XIS_DESGINCODES	U_IDMS_EXT_DlntPrice	DECIMAL(21,6)
Weight	Weight	@XIS_DESGINCODES	U_IDMS_Weight	DECIMAL(21,6)
IsAttributeLHAC	Is Attribute in LH Configuration (0/1)	@XIS_DESGINCODES	U_IDMS_IsAttributeLHAC	INTEGER
RegistrationFee	Registration Fee	@XIS_DESGINCODES	U_IDMS_RegistrationFee	DECIMAL(21,6)
ParentCode	Parent Code	@XIS_DESGINCODES	U_IDMS_ParentCode	NVARCHAR(254)
DisplayInConfigurator	Display In Configuration	@XIS_DESGINCODES	U_IDMS_ShowInConfigurator	NVARCHAR(1)
OptionDefault	Option Default (Y/N)	@XIS_DESGINCODES	U_IDMS_OptionDefault	NVARCHAR(1)
CostPrice	Cost Price	@XIS_DESGINCODES	U_IDMS_Cost	DECIMAL(21,6)

Flags

		Database Mapping			Lookup
Column	Description	Table	Field	Data Type	Table	Field
Code	Cost	@IDMS_FLAG	Code	NVARCHAR(50)
Name	Name	@IDMS_FLAG	Name	NVARCHAR(100)

Vehicle Standard Options

		Database Mapping			Lookup
Column	Description	Table	Field	Data Type	Table	Field
Code	Vehicle Code	@XIS_CARSMDLSPECIF	Code	NVARCHAR(50)	@XIS_CARS	Code
LineId	Line Id	@XIS_CARSMDLSPECIF	LineId	INTEGER
U_DesignCd	Option Code	@XIS_CARSMDLSPECIF	U_DesignCd	NVARCHAR(50)
U_DesignDs	Option Description	@XIS_CARSMDLSPECIF	U_DesignDs	NVARCHAR(254)
U_IDMS_DMSCode	Option DMS Code	@XIS_CARSMDLSPECIF	U_IDMS_DMSCode	NVARCHAR(50)
U_IDMS_EXT_OptionType	Option Type Valid values{Trim,Color,Option}	@XIS_CARSMDLSPECIF	U_IDMS_EXT_OptionType	NVARCHAR(50)
U_UnitPrice	Unit Price	@XIS_CARSMDLSPECIF	U_ListPrice	DECIMAL(21,6)
U_IDMS_Cost	Cost
U_IDMS_OptionCategory	Option Category	@XIS_CARSMDLSPECIF	U_IDMS_OptionCategory	NVARCHAR(50)	@IDMS_OPTIONCATEGORY	Code

Vehicle Images

		Database Mapping			Lookup
Column	Description	Table	Field	Data Type	Table	Field
VehicleCode		@XIS_CARS5	Code	NVARCHAR(50)
VehicleImage		@XIS_CARS5	U_FileLink	NVARCHAR(max)
SortOrder		@XIS_CARS5	U_SortOreder	INTEGER
ImageGroup		@XIS_CARS5	U_IDMS_Group	NVARCHAR(50)

Vehicle Optional Options

		Database Mapping			Lookup
Column	Description	Table	Field	Data Type	Table	Field
Code	Vehicle Code	@XIS_CARSPECIF	Code	NVARCHAR(50)	@XIS_CARS	Code
LineId	Line Id	@XIS_CARSPECIF	LineId	INTEGER
U_DesignCd	Option Code	@XIS_CARSPECIF	U_DesignCd	NVARCHAR(50)
U_DesignDs	Option Description	@XIS_CARSPECIF	U_DesignDs	NVARCHAR(254)
U_IDMS_DMSCode	Option DMS Code	@XIS_CARSPECIF	U_IDMS_DMSCode	NVARCHAR(50)
U_IDMS_EXT_OptionType	Option Type Valid values{Trim,Color,Option}	@XIS_CARSPECIF	U_IDMS_EXT_OptionType	NVARCHAR(50)
U_UnitPrice	Unit Price	@XIS_CARSPECIF	U_ListPrice	DECIMAL(21,6)
U_IDMS_Cost	Cost	@XIS_CARSPECIF
U_EquipmentGroup	Equipment Group	@XIS_CARSPECIF	U_EquipmentGroup	NVARCHAR(100)
U_IDMS_IsPackage	Is Package (0/1)	@XIS_CARSPECIF	U_IDMS_IsPackage	INTEGER
U_IDMS_OptionCategory	Option Category	@XIS_CARSPECIF	U_IDMS_OptionCategory	NVARCHAR(50)	@IDMS_OPTIONCATEGORY	Code

Template Excel File

ODVehiclesV1_2.xls

Page tree

SSIS Control Flow Chart

ODVehiclesV1_1.xls Content

Vehicles

Vehicle Prices

Options

Flags

Vehicle Standard Options

Vehicle Images

Vehicle Optional Options

Template Excel File

0 Comments