SELECT Statement to retrieve Oracle AOL Descriptive Flexfield Segment(DFF) Details

SELECT Statement to retrieve Oracle AOL Descriptive Flexfield Segment(DFF) Details

-- Application Developer > Navigate > Flexfields > Descriptive > Segments
-- Search for relevant Title, second half of the screen, under "Context Field Values"
-- lists the main parts of the Flexfield
-- Click into a name on the "Context Field Values" section in the lower part of the
-- screen, and click "Segments"
-- This lists the bits users see in Core Applications when they click into the DFF
-- plus shows if there is a LOV linked to the field
-- Following SELECT statement retrieves DFF details related to an application,DFF
-- title, DFF Context or
-- the entire application if no parameter is passed

-- Optional Parameters:-
-- P_TITLE -- DFF Title
-- P_APPLICATION -- Application Name
-- P_DESC_FLEX_CD, DFF Context 


  SELECT fat.application_name
       , fdfv.title
       , fdfcv.descriptive_flex_context_code context
       , fdfcuv.column_seq_num num
       , fdfcuv.end_user_column_name name
       , fdfcuv.application_column_name column_
       , ffvs.flex_value_set_name value_set
       , ffvs.description value_set_description
       , fdfcuv.required_flag
       , fdfcuv.display_flag
       , fdfcuv.enabled_flag
       , fdfcuv.security_enabled_flag
       , fdfcuv.default_value
    FROM apps.fnd_descriptive_flexs_vl fdfv
       , applsys.fnd_application_tl fat
       , apps.fnd_descr_flex_contexts_vl fdfcv
       , apps.fnd_descr_flex_col_usage_vl fdfcuv
       , applsys.fnd_flex_value_sets ffvs
   WHERE fdfv.application_id = fat.application_id
     AND fdfcv.descriptive_flexfield_name = fdfv.descriptive_flexfield_name
     AND fdfcv.descriptive_flexfield_name = fdfcuv.descriptive_flexfield_name
     AND fdfcv.descriptive_flex_context_code =  
                                       fdfcuv.descriptive_flex_context_code
     AND fdfcuv.flex_value_set_id = ffvs.flex_value_set_id(+)
     AND fdfv.title = NVL(:P_TITLE,fdfv.title)
     AND fat.application_name = NVL(:P_APPLICATION,fat.application_name)
     AND fdfcv.descriptive_flex_context_code =
                         NVL(:P_DESC_FLEX_CD,fdfcv.descriptive_flex_context_code)

ORDER BY fat.application_name

       , fdfv.title

       , fdfcv.descriptive_flex_context_code

       , fdfcuv.column_seq_num;