Vehicle Data Upload – Migration of Vehicle Orders
The purpose of this document is to identify key elements during Vehicle Data Load during cut-over and to analyse possible strategies with their merits and demerits. Also the document will focus on various technical and functional aspects of the process.
In any VMS project, Vehicle data upload is an important activity. A large volume of vehicles need to be created in SAP as part of the data migration / cut-over activity.
Created (or migrated) vehicles need to be updated for several parameters e.g. stock, vehicle location, registration data, configuration of the vehicle, primary & secondary statuses etc. For an effective transition from a non-SAP system to VMS, vehicles will need to reflect the legacy world on go live date or at least must contain all the required data so that business can start transacting without losing any functionality and / or required information.
First let us discuss, what we need to load into SAP from a Vehicles perspective including various technical and functional aspects. Once we know what we are uploading, we shall discuss on various mechanisms / strategies on how to best go through the migration process.
Vehicle data upload can broadly be split into 2 parts – Vehicle Creation and Vehicle Data Update.
To be able to upload vehicle data, first the vehicle needs to be created in VMS. SAP has provided a BAPI to create vehicles – BAPI_VEHICLE_CREATE.
The pre-requisite for the BAPI to run is that Material Code (MATNR) and PLANT are available in SAP.
The action to be exported to the BAPI is CREA (or any other vehicle creation action as per the business requirement). The table GT_VEHICLE needs to be populated for MATERIALNUMBER (MATNR) and PLANT as bare minimum requirement for the BAPI to be able to create a vehicle. Upon successful run of the BAPI, table GT_VEHICLE will have the VGUID for the created vehicle, which can be used in onward process. The table RETURN will contain the success / failure messages.
As VGUID is an internal field to SAP, I suggest to populate VIN (VHVIN) and / or External Vehicle Number (VHCEX) while creating vehicles, as these can be used to search for the vehicle from VMS database (VLCVEHICLE) for updating or any other onward process.
Current release (as of 01.01.2012) of BAPI is capable of creating only 1 vehicle per call as per the parameter values passed within the standard code.
VEHICLE DATA UPDATE: VEHICLE CONFIGURATION
Once the vehicle is created, the vehicle data has to be enriched with configuration i.e. to load the characteristics values. Depending upon the business scenario this may not be needed. Usually, vehicles are configurable i.e. paint colour, tyres, trims, entertainment, satellite navigation systems, safety features, climate control mechanisms etc. can be selected by the customer on the top of a standard vehicle. There are numerous areas where a vehicle’s configuration can be used depending on business requirements. These chosen ‘options’ can be used for pricing i.e. a metallic colour can attract additional costs, or a 6 CD changer or an in-built Satellite Navigation System on the same vehicle model. Also the configuration can be used for various analytical purposes e.g. to analyse market demand for a newly introduced feature or what colours are more in demand etc.
Standard SAP provides the functionality via batch class and characteristics. Therefore for such vehicles, the configuration (characteristic values) are needed to be loaded on to the vehicle.
In VMS, action CMOD is used to configure the vehicle. The action calls function module VELO10_REAL_EXECUTE. The function is used for 2 purposes i.e. to convert a planned vehicle to a real vehicle (creating valuation class and batch for the vehicle) and to change the configuration of the vehicle. Action CMOD, skips the creation of Batch and Valuation Class and simply change the configuration of the vehicle.
To run CMOD action, BAPI_VEHICLE_CHANGE_MULTIPLE can be used. The BAPI can work with multiple vehicles so populate BAPI fields / tables according to the requirement. Vehicle Identifier will need to be passed on to BAPI to find the vehicle, which could either be VGUID, External Vehicle Number, VIN or Internal Vehicle Number.
The BAPI does not append to vehicle’s configuration, it loads the data whatever is passed to BAPI fields / tables. So, in case, the action is executed on such a vehicle where partial configuration is already loaded, then already loaded configuration is to be read and copied to BAPI fields along with the remaining values while firing the BAPI to have full configuration on the vehicle.
Also the BAPI will return the error message and will not load the configuration if master data is not created beforehand. Full set of passed characteristic values are need to be present in SAP to run the BAPI and load the configuration. None of the values will be loaded if there even one value missing in SAP.
VEHICLE DATA UPDATE: ADDITIONAL DATA
There are two types of additional data to a vehicles – Vehicle Attributes and Vehicle Qualifiers.
Attributes are pre-defined in the system e.g. External Vehicle Number, VIN, Usage, Sharing Level etc. The possible values for any attribute are defined in customizing.
Qualifiers are additional data for vehicles. The values could be from anything to everything e.g. Carbon Emission of a vehicle, Custom Clearance Document Number, Engine Number, Import Value, Customer Name etc. What values are to be stored, will depend on the business requirement. The Qualifiers can be defined in customizing as required.
Attributes and Qualifiers are assigned to desired actions in customizing, the mappings are stored in tables CVLC16 (Qualifier – Action) and CVLC17 (Attribute – Action). All actions, upon execution, fire the function VELO09_WRITE_VEHICLE_DATA which calls VELO09_WRITE_VLCADDVDATA for updating Attributes and VELO09_WRITE_VLCADDDATA for updating Qualifiers. The Attribute and / or Qualifier to be updated by any action is driven by the mapping done in customizing.
The vehicle data for attributes is stored in VLCVEHICLE table and for qualifiers is stored in VLACADDDATA table. One thing to note regarding qualifier update is, if any qualifier is duplicated in supplied data to BAPI, it will return an error and no qualifier update will be carried out, therefore before firing the BAPI, make sure to run a check for duplicate entries on the data supplied.
Once relevant configuration is in place, the migration mechanism can upload additional data to respective table by using BAPI_VEHICLE_CHANGE_MULTIPLE passing relevant action, attributes / qualifiers and Vehicle Identifier.
VEHICLE DATA UPDATE: PRIMARY AND SECONDARY STATUSES
The vehicle life-cycle is controlled and regulated by Primary and Secondary action matrices. These matrices are configured in customizing. They define the lifecycle point a vehicle attains once an action is executed.
The migrated vehicles need to be brought to the point where they are in legacy at the time of cut-over so that next business action / transaction / step / process can be carried out in SAP instead of legacy.
To correct the vehicle statuses VELOE transaction is to be used. Action CORR is used to denote the status change in vehicle history. This action can be executed using transaction VELOE. The transaction overrides the CVLC04 mapping (Action Matrices) and updates the status for the selection of vehicle from the input field.
As of note 445860 (which my current understanding is too), there is no program / screen attached to the action in customizing by default therefore either a custom development will be required to be able to run CORR action from VELO transaction. The BAPI_VEHICLE_CHANGE_MULTIPLE isn’t useful either in this case.
INITIAL STOCK UPDATE:
Stock balances have to be loaded into SAP during Cut-over to reconcile the FI books. IEID is the action is to be used to load the stock balances. The action triggers a 561 movement on the vehicle using batch number (Internal Vehicle Number) and loads the inventory. The action can be run manually from VELO (required necessary configuration in place).
Usually during the cut-over, posting date for initial stock may not be the date when action is being performed therefore using BAPI_VEHICLE_CHANGE_MULTIPLE may not help as posting date may not be manipulated while executing BAPI. So far I have not been able to execute IEID action using BAPI successfully. Instead I had used VELO17_IEID_EXECUTE (caution: FM is not released) along with couple more individual functions to load the vehicle inventory.
MORE OPERATIONS ON VEHICLE:
There could be a variety of data, assignments could be required for the vehicle as per the business requirements e.g. a percentage of the migrated vehicles will already have dealers (customers) assigned to them in legacy i.e. the NSC / Importer / Distributor will know which dealer they would be sold to. This has to be reflected in migrated data.
VMS has an action to link a vehicle to the dealer i.e. RSVN. The action can be executed using BAPI mentioned in earlier steps passing an appropriate Vehicle identifier and RSVN as action. The action will update VLCRESERVATION table.
Reservation can only be carried out only on unreserved vehicles. A vehicle, for which either a quote, sales order, reservation etc. exists, will return an error. A full list of exception can be checked in function VELO11_RSVN_PREPARE.
Similarly, there could be more actions required to be executed on the vehicle during the cut-over e.g. assignment to Vendor, End Customer, Location update, Sales Order Creation, etc., which will depend on business requirements.
The BAPI provided can be used to enrich vehicle data by using different actions as required.
However to be able to execute any action, the Action Matrices should be in place. Also, any action fired via BAPI to update vehicles, should be allowed as per action matrix configuration (table CVLC04). The material master, characteristics master, Customer Master, Vendor Master etc. should be created in SAP before the migration exercise.
All these above actions can be wrapped up into a single program. The program then can receive data and instruction from any feeding mechanism to migrate data to SAP. The various feeding mechanisms are discussed in the next section of the document.
STRATEGIES TO LOAD DATA
So far we have discussed what is to be loaded into SAP VMS and what are the various technical and functional aspects of using BAPI and other function modules. Now we need to determine how the data is to be supplied to the BAPI, Functions etc. There are various ways with which we can supply data to BAPI, program etc., most commonly used and effective ones are discussed below:
The data can be loaded via excel files. The file will have a pre-determined format (template) as governed by the load program / function. Legacy systems may not provide the data in exact required format. So the data would need to be converted into required excel files as per the templates.
This excel files give more clarity and control over data. One can see the data before load physically. However this method is recommended if the volume of data is not too large. Above templates can be combined together to execute more than 1 action using single file, which would depend upon data, volume and requirement.
Another reason excel upload is effective because one can keep the templates dynamic, if required, for updates like SMOD and CMOD. In my experience, a couple of times I have had the need of updating just one qualifier to a number of vehicles to have complete information or just couple of more options to the vehicles to correct the price even after the data migration (or Data Migration Agreement). In such cases, the dynamic templates help. The load program would read the column header to determine which qualifier or characteristic is to be populated by corresponding value from record. This gave a unique advantage of ploughing through only needed parts of the file. With fix format, all the qualifier will be read for blank values, thereby increasing the run time. Also with dynamic template, one can work with the full data set to prepare most effective load files keeping columns at optimum number.
In this case, the program will pick up the files from local machine and churn the data as per the design. However, there is a downside to this approach. If the data set is big, the system has to stay on-line till the process is finished.
To overcome this, the program can be made to run in background using file from application server. The files in csv format can be placed on application server and the program can read them and execute in background. This increases the effort of putting files on app server, but it relieves the machine from being engaged throughout the load.
The load program can generate log files indicating success, failure, information etc. for every record it processes in both front end and background processing modes. They will be a great help in fixing issues and re-loading the error records. While running he program in background, one would need to download the log files and then work them out. I preferred generating 2 log files for every file processed i.e. a summary log, indicating Pass or Fail for a vehicle record and a detailed log, capturing all the message in return table of BAPI if there is an Error or Warning with the vehicle identifier. The log can be generated in a format which can be worked upon in excel easily.
Using excel, I have managed loading a volume of 400,000 vehicles in 3-4 days’ time including error fixing and re-processing the failed records.
UPLOAD VIA INTERFACE:
It is one of the easiest and effective method in my opinion to load the vehicles into SAP. Usually, SAP VMS is not used as a front end system to create vehicles. In normal business scenario, the vehicle orders are received at dealers’ end which results in a vehicle creation in VMS. Since Dealer front end systems are not usually SAP, therefore most of the implementations will involve an interface between ordering system and SAP to run day to day activities and vehicle’s life-cycle. As part of the implementation, vehicle ordering / life-cycle interface would be developed.
So, instead of creating a separate program to load the vehicles, I found it easier to get the legacy system (if it has the capability) to trigger all the orders from its database to SAP over the interface as part of migration exercise. Generally an indicator reset within legacy does the trick.
Once the order bank from legacy is published, it will behave as normal vehicle’s life-cycle. So if order is not present in SAP, it will create a vehicle otherwise it will update the vehicle with to the latest life-cycle point as directed by the incoming data.
But this approach may not work depending upon the circumstances. My point is, if legacy system could be used to migrate vehicles then it is worth looking at the possibility, as it will save time, the data can be migrated in chunks as and when suited, reconciliation becomes easy as interface will undergo testing during development and will save development in SAP.
In my experience, I have enabled the vehicle ordering interface way before the actual go live, by transporting the required configuration well before the go-live date. This loads the vehicles, continues the life-cycle replicating the legacy action in SAP too and gives enough time data reconciliation and to fix any bugs in the interface.
As discussed above, there would usually be an interface through which SAP will receive the vehicle creation / life-cycle updates. If the legacy system cannot push data to SAP, then it might be worth looking at the middleware involved. The data can be inserted at middleware so that it is received in SAP as normal interface, if one is built as discussed above. Alternatively, the BAPI can be called by the middleware through RFC and data can be transferred to SAP.
This also saves an additional development to create a data upload mechanism in SAP. However, not all middleware systems are friendly enough to be able to cater to this requirement. So this could be a long shot but still worth exploring depending upon project governance and other constraints.
Of course the vehicles can be created directly in VELO transaction. But this way will not be feasible for even a data volume of few hundred vehicles. Loading configuration or qualifier (if they are too many – some of my projects had more than 500 qualifiers) manually will be a tedious task and prone to human errors.
Apart from above, there could be more methods to load the vehicles in SAP e.g. LSMW. However I have never explored these options as in my experience, the business requirements often are quite spread out and to achieve them a custom development is required. So I have always used either excel upload or interface load to migrate Vehicle data to SAP.
I am sure there would be many more ways to do the above. Any comments and suggestions are very welcome. Hope you have enjoyed the document and it helps. For any questions, please drop a message.