One of my customers is on its journey to enable their traditionally unilingual developed Siebel application, towards true multilingual. With 15+ years of developments, not a simple undertaking! One of the key challenges is enabling MLOV, storing the LIC instead of the Display value. Again; having your LOV values translated in different languages does make it an MLOV as such.
Loose from the fact that we should not blindly migrate just any LOV towards MLOV, I wanted to get (some) grip on the data quality. Why? Because this particular customer did implement a number of unshipped languages, running under ENU as resource language with just a localized SRF. So basically just translating the UI for a (small but) relevant portion of the application which gets exposed to business users in different countries. The core of the application is provided in plain English in the centralized call center. Transactional data could store either pure ENU LOV values, or a mixture of different languages. At the same time, it would be a some sort of health check which would provide pointers to potential data issues.
Primary goal was to get some grip on the data quality, and to see if there would be columns with picklists associated, which would store non-ENU values. Sure, I could have reverted to Siebel’s MLOV converter but my only goal was to analyze the status-quo.
My approach was to generate using Siebel’s Repository meta-data dynamic SQL. If you don’t know the concept: “Dynamic SQL enables you to write programs that reference SQL statements whose full text is not known until run-time”. Basically using SQL to generate SQL.
How I went about? Well by selecting across the repository all active fields which have a static picklist associated (beware, that are many). Based on that output, selecting the distinct values from the corresponding base table / column and matching those against the active English language independent code. Why only English and why the LIC? Because I would like to check whether I could easily convert from LOV to MLOV. With the assumption that most customers use the standard approach ENU value equals the Language independent code, that would help.I excluded those columns already configured for MLOV (e.g. with the Translation Table set to S_LST_OF_VAL).
I came up with the piece of SQL:
Its output would be like:
Running against the Sample database, it outputs about 1MB of dynamic SQL 🙂 Lot faster than I could write it. Scripting bit and pieces together:
The final output (lov_unmatched.out) will indicate where additional review might be required. In the below case for example it indicates that S_ADDR_PER.ADDR_TYPE_CD contains 6 unique values which have no matching value in S_LST_OF_VAL with type PUB_LOCATION_TYPE. Issue could be with just a couple of addresses (e.g. bad seed data in this case) or worse…
Want the script? Get it here.