SQL to retrieve a list of descriptive flexfields with column usages and value-set definitions
For a little project I needed to retrieve E-Business Suite Descriptive Flexfields for all applications together with the column usages and value set assignments and settings.
So, here you go...of course adjust the statement per your requirements.
SELECT
A.APPLICATION_NAME,
FDF.APPLICATION_TABLE_NAME,
FDFT.TITLE,
FDF.DESCRIPTIVE_FLEXFIELD_NAME,
FDF.FREEZE_FLEX_DEFINITION_FLAG,
FDCF.DESCRIPTIVE_FLEX_CONTEXT_CODE,
FDFCU.COLUMN_SEQ_NUM SEQUENCE_NUMBER,
FDFCU.END_USER_COLUMN_NAME PARAMETER_NAME,
FFVS.FLEX_VALUE_SET_NAME VALUE_SET,
FFVS.DESCRIPTION VALUE_SET_DESCRIPTION,
T.FORM_LEFT_PROMPT PROMPT,
FDFCU.DEFAULT_VALUE DEFAULT_VALUE,
FDFCU.ENABLED_FLAG,
FDFCU.REQUIRED_FLAG,
FDFCU.SECURITY_ENABLED_FLAG,
FDFCU.DISPLAY_FLAG,
FDFCU.DISPLAY_SIZE
FROM
FND_LOOKUP_VALUES FLVF,
FND_DESCRIPTIVE_FLEXS FDF,
FND_DESCRIPTIVE_FLEXS_TL FDFT,
FND_DESCR_FLEX_CONTEXTS FDCF,
FND_DESCR_FLEX_COLUMN_USAGES FDFCU,
FND_DESCR_FLEX_COL_USAGE_TL T,
FND_FLEX_VALUE_SETS FFVS,
FND_APPLICATION_TL A
WHERE
FDF.DESCRIPTIVE_FLEXFIELD_NAME = FDFT.DESCRIPTIVE_FLEXFIELD_NAME
AND FDF.DESCRIPTIVE_FLEXFIELD_NAME = FDCF.DESCRIPTIVE_FLEXFIELD_NAME(+)
AND FDCF.DESCRIPTIVE_FLEXFIELD_NAME = FDFCU.DESCRIPTIVE_FLEXFIELD_NAME(+)
AND FDCF.DESCRIPTIVE_FLEX_CONTEXT_CODE = FDFCU.DESCRIPTIVE_FLEX_CONTEXT_CODE(+)
AND FFVS.FLEX_VALUE_SET_ID(+) = FDFCU.FLEX_VALUE_SET_ID
AND T.LANGUAGE = 'US'
AND A.LANGUAGE = 'US'
AND FDFT.LANGUAGE = 'US'
AND FDF.APPLICATION_ID = A.APPLICATION_ID
AND FDFCU.DESCRIPTIVE_FLEXFIELD_NAME = T.DESCRIPTIVE_FLEXFIELD_NAME
AND FDFCU.DESCRIPTIVE_FLEX_CONTEXT_CODE = T.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND FDFCU.APPLICATION_COLUMN_NAME = T.APPLICATION_COLUMN_NAME
AND FLVF.LOOKUP_TYPE(+) = 'COLUMN_TYPE'
AND FLVF.LOOKUP_CODE(+) = FDFCU.DEFAULT_TYPE
ORDER BY
A.APPLICATION_NAME,
FDF.DESCRIPTIVE_FLEXFIELD_NAME,
FDFCU.COLUMN_SEQ_NUM
SQL to retrieve a list of descriptive flexfields with column usages and valueset definitions
- See more at: http://oracleebsapps.blogspot.com/2014/09/sql-to-retrieve-list-of-descriptive.html#sthash.QxKxfA7U.dpuf
Hi may I know what table stores information about window prompt and numbers in dff
ReplyDelete