AnsweredAssumed Answered

Determining data type of a dynamic lookup for XOG attribute creation

Question asked by beekerc2 on Oct 30, 2009
Latest reply on Nov 2, 2009 by Dave
I have a project where we are buliding lots of custom objects and attributes (in the hundreds).   To make life easier, I have built a spreadsheet to define the attributes and I use Excel formulas to build XML for XOG.   (Bless the developer who put the XOG GUI into v12).   The tricky part was determining the data type (string or number) of a lookup.   Static and Static Dependent was pretty straight forward, I evaluated the LOOKUP_CODE values (by querying CMN_LOOKUPS and CMN_LOOKUP_TYPES) as to whether they were numeric or textual.   The trick is with dynamic lookups.   The LOOKUP_CODE is NULL (for obvious reasons), however, Clarity must somehow know what data type a dynamic lookup returns because (when creating the attribute in the GUI) once you specify the lookup name, it determines whether the attribute is a lookup-string or lookup-numeric.    This is the code that I currently use to build a table that my Excel forumlas referece by VLOOKUP.   I use "Z_" to prefix all the lookups that I build.   The reference to 'RIM_PRIORITY' is a list of stock lookups that I use.   I need all the lookups that my custom attributes use in the list so every entry in the spreadsheet has all the XML it needs.  select distinct capt.name, lk.lookup_type,
            case is_number (lk.lookup_code) when 1 then 'number' else 'string' end case,
            case substr (lk.lookup_type, 1, 2) when 'Z_' then 'Custom' else 'STOCK' end case
from cmn_lookups lk
      left outer join cmn_lookup_types lkt on lk.lookup_type = lkt.lookup_type
      left outer join CMN_CAPTIONS_NLS capt on lkt.id = capt.pk_id
 where (substr (lkt.lookup_type, 1, 2) = 'Z_' or lkt.lookup_type in ('RIM_PRIORITY'))
    and lk.lookup_level = 1
    and capt.table_name = 'CMN_LOOKUP_TYPES'
    and capt.language_code = 'en'
 order by capt.name
 So my question is, what other tables do i need to add to this query to determine the data type of dynamic queries so my lookup list will be complete?If this needs any further clarification, please let me know.Thanks in advanceBeekerC

Outcomes