Important SQL Queries of Flex Fields

--Table type has values from TABLE_TYPE lookup:
SELECT  lookup_type,
        lookup_code,
     meaning,
        description 
FROM    fnd_lookup_values 
WHERE   lookup_type = 'TABLE_TYPE';

--Check registered tables using:
SELECT  application_id, 
        table_id, 
        table_name, 
        user_table_name, 
        table_type, 
        description
FROM    fnd_tables 
WHERE   table_name = 'GL_CODE_COMBINATIONS';

--Check columns of registered tables using:
SELECT  column_sequence, 
        column_name, 
        user_column_name, 
        column_type, 
        width, 
        description, 
        precision, 
        scale
FROM    fnd_columns
WHERE   table_id = 584
ORDER BY column_sequence;

--Column type has values from COLUMN_TYPE lookup:
SELECT  lookup_type,
        lookup_code,
     meaning,
        description 
FROM    fnd_lookup_values 
WHERE   lookup_type  = 'COLUMN_TYPE';

--Below SQL statement shows important details about the Accounting Flexfield: 
SELECT
      a.application_id,
      b.application_name,
      a.id_flex_code, 
      a.id_flex_name,
      a.description,
      a.application_table_name,      
      a.unique_id_column_name,      
      a.set_defining_column_name,
      a.concatenated_segs_view_name
FROM 
      fnd_id_flexs a,
      fnd_application_tl b
WHERE 
      a.application_id    = b.application_id 
      AND a.id_flex_code  = 'GL#';


--Below SQL statement shows details about the flex structure: 
SELECT 
      a.application_id, 
      a.id_flex_code,
      a.id_flex_num, --chart of account id
      a.id_flex_structure_code,
      b.id_flex_structure_name, 
      b.description
FROM 
      fnd_id_flex_structures a,
      fnd_id_flex_structures_tl b
WHERE
      a.id_flex_num  = b.id_flex_num
      AND a.application_id = '101' 
      AND a.id_flex_code = 'GL#'
      AND a.id_flex_num  = '50309';


--Below SQL statement shows details about the flex segments:  
SELECT 
      a.application_id, 
      a.id_flex_code, 
      a.id_flex_num, 
      a.application_column_name, 
      a.segment_name,
      a.segment_num,
      a.flex_value_set_id,
      b.description

FROM 
      fnd_id_flex_segments a,
      fnd_id_flex_segments_tl b
WHERE
      a.id_flex_num             = b.id_flex_num AND
      a.application_column_name = b.application_column_name
      AND a.application_id      = '101' 
      AND a.id_flex_code        = 'GL#'
      AND a.id_flex_num         = '50309';

--Below SQL statement shows values present in the value set:
SELECT 
       a.flex_value_set_id, 
       a.flex_value_id, 
       a.flex_value, 
       b.flex_value_meaning,
       a.enabled_flag       
FROM 
      fnd_flex_values a,
      fnd_flex_values_tl b
WHERE 
      a.flex_value_id=b.flex_value_id
      AND flex_value_set_id ='1012802';

--Below SQL statement shows validation types available for value sets:
SELECT  lookup_type,
        lookup_code,
     meaning,
        description 
FROM    fnd_lookup_values 
WHERE   lookup_type='SEG_VAL_TYPES';
      
--Below SQL shows the list of all value sets available
SELECT *
FROM  fnd_flex_value_sets;