Comparing LOVs between Siebel Environments

Anyone that has worked on projects with several different environments knows the pain involved with getting identical LOVs. You might think: how difficult can it be? 

Try working with 40-50 developers, tight deadlines, multiple builds, corrections after testing cycles etc, and chances are big the LOVs will be out of synch.


Of course, if the project has a process in place to move data around wih EIM, COM objects, or some other process, that is good news for you and to the envy of many projects around the world. If not, here's a simple method of comparing LOVs between environments (until you have time to build a better process):

The method involves exporting all the LOVs from the two environments you want to compare, import this into Access, and then use the "Find unmatched query wizard" in Access. It's fairly straighforward, and here are some steps to get started (this is not intended to be a complete list of steps, but something to get you started):


1. Siebel Application | List of Values (do this for each environment that is to be compared)

a. Columns Displayed

  •   Type
  •   Display Value
  •   Language-Independent Code
  •   Parent LIC
  •   Parent Type
  •   Order
  •   High
  •   Low
  •   Description
  •   Active

b.  Menu | Export…

  •  All Rows In Current Query
  •  Visible Columns
  •  Tab Delimited Text File
  •  Next
  •  Open
  •  After file opens in Excel, do the following: 

Ø Highlight Parent LIC, Parent Type, High, Low, and Description columns.

Ø Edit | Replace…

Ø Find what: (leave blank), Replace with: Blank (we just want to put the word “Blank” in these columns if there is no value

Ø Replace All

Ø Highlight Order column

Ø Edit | Replace…

Ø Find what: (leave blank), Replace with: 0 (we just want to put the number “0” in this column if there is no value

Ø Save the file as an Excel file, e.g. DEV1.xls

Ø Close Excel 

  • Close the export window


c.       We should now have all the List of Values in excel files from the environments we want to compare


2. MS Access (do this for each environment that is to be compared)

a.       File | Get External Data | Import…

  •     Select file exported in step 1
  •     First Row Contains Column Headings (should be checked)
  •     Next
  •     In a New Table
  •     Next
  •     Next
  •     No primary key
  •     Next
  •     Import to Table: <environment_name>

b.  We should now have LOV tables in  Access


3. MS Access | Queries

a.   New

  •        Find Unmatched Query Wizard
  •        Select table to compare
  •        Next
  •        Select table to compare against
  •        Next
  •        Highlight Display Value in both tables and click <=> button
  •        Move all available fields over to selected fields
  •        Next
  •        Finish

b.   The resulting query returns all LOV’s in table 1 that were not found in table 2.

You probably want to modify this a bit to exclude LOVs that are unique in each environment and LOV Types you don't care about. You can easily do this in Access by modifying the query to your needs.


Post by Paul Groettjord

6 comments on “Comparing LOVs between Siebel Environments”

  1. David Reply

    Thank you – very useful.  This approach definitely seems simpler than going crazy with V-Lookups in Excel.  An  add-on modification to the spreadsheet could be a macro that takes care of the finds/replacements.

  2. Arnab Chanda Reply

    Siebel provides ADM to load LOVs and similar items. However, I have been COM objects to load LOVs for the last 5+ years. It works like a charm. The obvious advantages are
    1. LOVs can be directly exported out of Siebel into csv and copied into an excel. This excel houses a macro with COM object.
    2. Once you build a COM excel, you can use it in different environments by changing the datasource.
    3. It only takes few minutes to load LOVs in this process, and it can load up to 65k+ data at a time.
    Siebel bookshelf has some documents on how to build COM objects. That will get you started. If you need further help, feel free to reach out.

  3. Ashish Kumar Ashish Kumar Reply

    Really Arnab has provided excellent way. I have checked, Its works wondeful.

    Please wait for next article, we will publish you Arnab's recommendation in detail.

  4. Ajay Reply

    However what approach are you following for the LOVs which have updates as well as new inserts??

  5. Arnab Chanda Reply

    I use a mode called "Upsert". You can program the script inside COM in a way that it first searches for the LOV Type – LIC code combination before attempting to insert a new record. Between, "Upsert" and "Delete", you can practically do anything with the these LOV load.

Leave A Reply

Your email address will not be published. Required fields are marked *

error: Content is protected !!