Here are a few SQLs to help working with Oracle EBS (E-Business
Suite) Descriptive Flex Fields. You may have to make appropriate changes for
your environment.
All custom DFFs - contexts,
fields, values by module/application
SELECT
APL.APPLICATION_ID, APL.APPLICATION_NAME,
FDF.TITLE "DFF
TITLE",
FDF.CONTEXT_COLUMN_NAME "CONTEXT COLUMN NAME",
FDF.APPLICATION_TABLE_NAME "APPLICATION TABLE",
FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE "DFF CONTEXT CODE",
FUSG.COLUMN_SEQ_NUM "SEQUENCE",
FUSG.END_USER_COLUMN_NAME "SEGMENT NAME",
FUSG.APPLICATION_COLUMN_NAME "COLUMN NAME",
FFV.FLEX_VALUE_SET_NAME "VALUE SET NAME",
FUSG.ENABLED_FLAG -- , APL.*
FROM FND_DESCR_FLEX_COL_USAGE_VL
FUSG, FND_DESCRIPTIVE_FLEXS_VL FDF, FND_FLEX_VALUE_SETS FFV, FND_APPLICATION_VL
APL
WHERE
FUSG.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID(+)
-- AND FDF.TITLE = 'Employee Confirmation
Date' -- Replace this with Flex Field
Title
-- AND FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE =
'US'
-- AND FUSG.ENABLED_FLAG = 'Y'
AND FUSG.CREATED_BY >= 1000
AND FDF.APPLICATION_ID = APL.APPLICATION_ID
AND FUSG.DESCRIPTIVE_FLEXFIELD_NAME =
FDF.DESCRIPTIVE_FLEXFIELD_NAME
AND FUSG.APPLICATION_ID = FDF.APPLICATION_ID
ORDER BY
APL.APPLICATION_NAME, FDF.TITLE, FUSG.DESCRIPTIVE_FLEXFIELD_NAME,
FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE, FUSG.COLUMN_SEQ_NUM;
All DFF definitions in the
system, includes lot of standard fields
This may be useful, if you are
more comfortable to filter in Excel
SELECT
FDF.APPLICATION_TABLE_NAME
"APPLICATION TABLE",
FDF.TITLE "DFF
TITLE",
FDF.CONTEXT_COLUMN_NAME "CONTEXT COLUMN NAME",
FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE "DFF CONTEXT CODE",
FUSG.COLUMN_SEQ_NUM "SEQUENCE",
FUSG.END_USER_COLUMN_NAME "SEGMENT NAME",
FUSG.APPLICATION_COLUMN_NAME "COLUMN NAME",
FFV.FLEX_VALUE_SET_NAME "VALUE SET NAME",
FUSG.ENABLED_FLAG
FROM
FND_DESCR_FLEX_COL_USAGE_VL FUSG,
FND_DESCRIPTIVE_FLEXS_VL FDF,
FND_FLEX_VALUE_SETS FFV
WHERE
FUSG.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID(+)
-- AND
FDF.TITLE = 'Employee Confirmation Date'
-- Replace this with Flex Field Title
-- AND
FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'US'
-- AND
FUSG.ENABLED_FLAG = 'Y'
AND
FUSG.DESCRIPTIVE_FLEXFIELD_NAME = FDF.DESCRIPTIVE_FLEXFIELD_NAME
AND
FUSG.APPLICATION_ID = FDF.APPLICATION_ID
ORDER BY
FDF.APPLICATION_TABLE_NAME, FDF.TITLE, FUSG.DESCRIPTIVE_FLEXFIELD_NAME,
FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE, FUSG.COLUMN_SEQ_NUM;
Query to find DFF information
SELECT
FDF.TITLE
"DFF TITLE",
FDF.APPLICATION_TABLE_NAME "APPLICATION TABLE",
FDF.CONTEXT_COLUMN_NAME "CONTEXT COLUMN NAME",
FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE "DFF CONTEXT CODE",
FUSG.COLUMN_SEQ_NUM "SEQUENCE",
FUSG.END_USER_COLUMN_NAME "SEGMENT NAME",
FUSG.APPLICATION_COLUMN_NAME "COLUMN NAME",
FFV.FLEX_VALUE_SET_NAME "VALUE SET NAME"
FROM
FND_DESCR_FLEX_COL_USAGE_VL FUSG,
FND_DESCRIPTIVE_FLEXS_VL FDF,
FND_FLEX_VALUE_SETS FFV
WHERE FDF.TITLE
= 'Employee Confirmation Date' --
Replace this with Flex Field Title
AND
FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'US'
AND
FUSG.ENABLED_FLAG = 'Y'
AND
FUSG.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
AND
FUSG.DESCRIPTIVE_FLEXFIELD_NAME = FDF.DESCRIPTIVE_FLEXFIELD_NAME
AND
FUSG.APPLICATION_ID = FDF.APPLICATION_ID
ORDER BY
FUSG.DESCRIPTIVE_FLEXFIELD_NAME,
FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE,
FUSG.COLUMN_SEQ_NUM;
