To get Extensible FlexFlields (EFF) information in Oracle fusion

 We can get the EFF information by using the below SQL

EFF- The Values returned in:

fdcb.CONTEXT_IDENTIFIER,

fdsb.SEGMENT_IDENTIFIER

are used when working with extensions and service mappings in pricing.

SELECT   fdcb.context_code ,

           fdcb.context_identifier ,
           fdcb.enabled_flag ,
           fdsb.segment_code ,
           fdsb.segment_identifier ,
           fdsb.column_name
  FROM     fusion.fnd_df_contexts_tl fdct,
           fusion.fnd_df_contexts_b fdcb ,
           fusion.fnd_df_segments_tl fdst,
           fusion.fnd_df_segments_b fdsb
  WHERE    fdct.context_code = fdcb.context_code
  AND      fdcb.context_code = fdsb.context_code
  AND      fdst.context_code = fdsb.context_code
  AND      fdst.segment_code = fdsb.segment_code
  AND      fdct.application_id = fdcb.application_id
  AND      fdct.language = Userenv('Lang')
  AND      fdst.language = Userenv('Lang')
  AND      fdct.descriptive_flexfield_code = 'DOO_FULFILL_LINES_ADD_INFO'
  ORDER BY fdcb.context_identifier,
           fdsb.segment_identifier
  ---------------------------------------------------------------
  /*Select ALL Segments (Fields defined in a Contexts)*/SELECT   fdst.descriptive_flexfield_code ,
           fdst.context_code ,
           fdst.segment_code ,
           fdst.NAME ,
           fdsb.application_id ,
           fdsb.descriptive_flexfield_code ,
           fdsb.context_code ,
           fdsb.segment_code ,
           fdsb.segment_identifier ,
           fdsb.column_name ,
           fdsb.sequence_number ,
           fdsb.enabled_flag ,
           fdsb.required_flag ,
           fdsb.value_set_id ,
           fdsb.default_type ,
           fdsb.default_value ,
           fdsb.derivation_value ,
           fdsb.range_type ,
           fdsb.read_only_flag ,
           fdsb.display_type ,
           fdsb.display_width ,
           fdsb.display_height ,
           fdsb.checkbox_checked_value ,
           fdsb.checkbox_unchecked_value
  FROM     fusion.fnd_df_segments_tl fdst,
           fusion.fnd_df_segments_b fdsb
  WHERE    fdst.application_id = fdsb.application_id
  AND      fdst.enterprise_id = fdsb.enterprise_id
  AND      fdst.descriptive_flexfield_code = fdsb.descriptive_flexfield_code
  AND      fdst.context_code = fdsb.context_code
  AND      fdst.segment_code = fdsb.segment_code
  AND      fdst.language = 'US'
  AND      fdst.descriptive_flexfield_code = 'DOO_FULFILL_LINES_ADD_INFO'
  ORDER BY fdst.context_code,
           fdsb.sequence_number
  --------------------------------------------------------------------------------
  /*Select All Categories*/SELECT application_id ,
         descriptive_flexfield_code ,
         context_code ,
         category_code
  FROM   fusion.fnd_ef_category_contexts
  WHERE  descriptive_flexfield_code = 'DOO_FULFILL_LINES_ADD_INFO'
  --------------------------------------------------------------------------------
  /*EFF Miscellaneous - 1*/SELECT   feupb.application_id ,
           feupb.descriptive_flexfield_code ,
           feupb.flexfield_usage_code ,
           feupb.category_code ,
           feupb.page_code ,
           feupt.NAME ,
           feupb.sequence_number ,
           feupb.mds_document_name
  FROM     fusion.fnd_ef_ui_pages_b feupb,
           fusion.fnd_ef_ui_pages_tl feupt
  WHERE    feupt.descriptive_flexfield_code = 'DOO_FULFILL_LINES_ADD_INFO'
  AND      feupt.application_id = feupb.application_id
  AND      feupt.descriptive_flexfield_code = feupb.descriptive_flexfield_code
  AND      feupt.flexfield_usage_code = feupb.flexfield_usage_code
  AND      feupt.category_code = feupb.category_code
  AND      feupt.page_code = feupb.page_code
  AND      feupt.language = 'US'
  ORDER BY feupb.descriptive_flexfield_code ,
           feupb.flexfield_usage_code ,
           feupb.category_code ,
           feupb.page_code ,
           feupb.sequence_number
  ----------------------------------------------------------------
  /*EFF Miscellaneous – 2*/SELECT application_id ,
         descriptive_flexfield_code ,
         flexfield_usage_code ,
         category_code ,
         page_code ,
         context_code ,
         context_category_code ,
         sequence_number ,
         mds_document_name
  FROM   fusion.fnd_ef_ui_page_task_flows
  WHERE  descriptive_flexfield_code = 'DOO_FULFILL_LINES_ADD_INFO'
  ------------------------------------------------------------------