Flexfield Definition

This query gives complete definition of Qualifier context flexfield. By changing the conditions, one may view other fieldfields. One can view flexfield definition using application developer responsibility and using navigation Flexfield->Descriptive->Segments and it is better to view field in this way. Query could be useful if you are investigating a large flexfield such as qualifier context in oracle pricing and need to see how an attribute is used by different contexts

SELECT fat.application_name
     , fdft.descriptive_flexfield_name
     , fdft.title
     , fdf.application_table_name
     , fdfc.descriptive_flex_context_code
     , fdfc.global_flag
     , fdfc.enabled_flag
     , fdfct.descriptive_flex_context_name
     , fdfcu.column_seq_num
     , fdfcu.application_column_name 
     , fdfcu.end_user_column_name
     , fdfcut.description
     , ffvs.flex_value_set_name
     , fdfcut.form_left_prompt
     , fdfcu.enabled_flag
     , fdfcu.required_flag
     , fdfcu.default_value
  FROM fnd_descriptive_flexs        fdf
     , fnd_descriptive_flexs_tl     fdft
     , fnd_application              fa
     , fnd_application_tl           fat
     , fnd_descr_flex_contexts      fdfc
     , fnd_descr_flex_contexts_tl   fdfct
     , fnd_descr_flex_column_usages fdfcu
     , fnd_descr_flex_col_usage_tl  fdfcut
     , fnd_flex_value_sets          ffvs
 WHERE fdf.application_id                  = fdft.application_id
   AND fdf.descriptive_flexfield_name      = fdft.descriptive_flexfield_name
   AND fdft.language                       = USERENV('LANG')
   --
   AND fa.application_id                   = fdf.application_id
   --
   AND fat.application_id                  = fa.application_id
   AND fat.language                        = USERENV('LANG')
   --  flex field context
   AND fdfc.application_id                 = fdf.application_id
   AND fdfc.descriptive_flexfield_name     = fdf.descriptive_flexfield_name
   --
   AND fdfct.application_id                = fdfc.application_id
   AND fdfct.descriptive_flexfield_name    = fdfc.descriptive_flexfield_name
   AND fdfct.descriptive_flex_context_code = fdfc.descriptive_flex_context_code
   AND fdfct.LANGUAGE                      = USERENV ('LANG')
   --  flexfield context columns usage
   AND fdfcu.application_id                 (+) = fdfc.application_id
   AND fdfcu.descriptive_flexfield_name     (+) = fdfc.descriptive_flexfield_name
   AND fdfcu.descriptive_flex_context_code  (+) = fdfc.descriptive_flex_context_code
   --
   AND fdfcut.application_id                (+) = fdfcu.application_id
   AND fdfcut.descriptive_flexfield_name    (+) = fdfcu.descriptive_flexfield_name
   AND fdfcut.descriptive_flex_context_code (+) = fdfcu.descriptive_flex_context_code
   AND fdfcut.application_column_name       (+) = fdfcu.application_column_name
   AND fdfcut.LANGUAGE                      (+) = USERENV ('LANG')
   --  For some disabled context(s), segments may not be defined
   --  properly and value set might not be present. 
   AND ffvs.flex_value_set_id               (+) = fdfcu.flex_value_set_id
   --
   --
   --  Add conditions
   --  input value could be flexfield name or table name or
   --  something else. Just add to in clasue
   AND :input_value in ( fdf.descriptive_flexfield_name
                       , fdf.application_table_name
                       )
   -- 
   -- End of conditions
   --  
 ORDER BY fat.application_name
        , fdf.descriptive_flexfield_name
        , fdfct.descriptive_flex_context_name
        , fdfcu.column_seq_num