Oracle Applications : Descriptive Flexfields Overview and the SQL to find the Columns & Details.

Descriptive flexfields let you satisfy different groups of users without having to reprogram your application, by letting you provide customizable “expansion space”? on your forms.

For example, suppose you have a retail application that keeps track of customers. Your Customers form would normally include fields such as Name, Address, State, Customer Number, and so on. However, your form might not include extra fields to keep track of customer clothing size and color preferences, or regular salesperson, since these are attributes of the customer entity that depend on how your users use your application.

For example, if your retail application is used for a tool company, a field for clothing size would be undesirable. Even if you initially provide all the fields your users need, your users might later identify even more customer attributes that they want to keep track of. You add a descriptive flexfield to your form so that your users have the desired expansion space. Your users can also take advantage of the fact that descriptive flexfields can be context sensitive, where the information your application stores depends on other values your users enter in other parts of the form.

A descriptive flexfield describes an application entity, providing form and database expansion space that you can customize. Each descriptive segment has a name you assign. You can specify valid segment values or set up criteria to validate the entry of any value.

Oracle General Ledger includes a descriptive flexfield in its journal entry form to allow end users to add information of their own choosing. For example, end users might want to capture additional information about each journal entry, such as source document number or the name of the person who prepared the entry. You could use a descriptive flexfield in a fixed assets application you build to allow further description of a fixed asset. You could let the structure of your assets flexfield depend on the value of an asset type field. For example, if asset type were “desk”, your descriptive flexfield could prompt for style, size and wood type. If asset type were “computer”, your descriptive flexfield could prompt for CPU chip and memory size.

SELECT fdfv.title,
       fdfv.application_table_name,
           fdfv.context_column_name,
           fdfcu.descriptive_flexfield_name,
       fdfcu.descriptive_flex_context_code,
           fdfcu.column_seq_num,
           fdfcu.application_column_name,
           fdfcu.end_user_column_name
FROM  fnd_descr_flex_col_usage_vl fdfcu,
      fnd_descriptive_flexs_vl    fdfv
WHERE fdfv.title = ‘Line Transaction Flexfield’
AND   fdfcu.descriptive_flexfield_name  = fdfv.descriptive_flexfield_name 
AND   fdfcu.application_id = fdfv.application_id 
ORDER BY fdfcu.descriptive_flexfield_name,  fdfcu.descriptive_flex_context_code,fdfcu.column_seq_num

SELECT fdfv.title,
       fdfv.application_table_name,
           fdfv.context_column_name,
           fdfcu.descriptive_flexfield_name,
       fdfcu.descriptive_flex_context_code,
           fdfcu.column_seq_num,
           fdfcu.application_column_name,
           fdfcu.end_user_column_name
FROM  fnd_descr_flex_col_usage_vl fdfcu,
      fnd_descriptive_flexs_vl    fdfv
WHERE fdfv.title = ‘Invoice Transaction Flexfield’
AND   fdfcu.descriptive_flexfield_name  = fdfv.descriptive_flexfield_name 
AND   fdfcu.application_id = fdfv.application_id 
ORDER BY fdfcu.descriptive_flexfield_name, fdfcu.descriptive_flex_context_code,fdfcu.column_seq_num

SELECT fdfv.title,
       fdfv.application_table_name,
           fdfv.context_column_name,
           fdfcu.descriptive_flexfield_name,
       fdfcu.descriptive_flex_context_code,
           fdfcu.column_seq_num,
           fdfcu.application_column_name,
           fdfcu.end_user_column_name
FROM  fnd_descr_flex_col_usage_vl fdfcu,
      fnd_descriptive_flexs_vl    fdfv
WHERE fdfv.title = ‘Link-to Transaction Flexfield’
AND   fdfcu.descriptive_flexfield_name  = fdfv.descriptive_flexfield_name 
AND   fdfcu.application_id = fdfv.application_id 
ORDER BY fdfcu.descriptive_flexfield_name, fdfcu.descriptive_flex_context_code,fdfcu.column_seq_num

SELECT fdfv.title,
       fdfv.application_table_name,
           fdfv.context_column_name,
           fdfcu.descriptive_flexfield_name,
       fdfcu.descriptive_flex_context_code,
           fdfcu.column_seq_num,
           fdfcu.application_column_name,
           fdfcu.end_user_column_name
FROM  fnd_descr_flex_col_usage_vl fdfcu,
      fnd_descriptive_flexs_vl    fdfv
WHERE fdfv.title = ‘Reference Transaction Flexfield’
AND   fdfcu.descriptive_flexfield_name  = fdfv.descriptive_flexfield_name 
AND   fdfcu.application_id = fdfv.application_id 
ORDER BY fdfcu.descriptive_flexfield_name, fdfcu.descriptive_flex_context_code,fdfcu.column_seq_num