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
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
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
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
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