Sunday, June 24, 2012

Query to know the Attributes(DFF Fields) Actual Column Names in Oracle tables



SELECT
b.APPLICATION_TABLE_NAME,
b.DESCRIPTIVE_FLEXFIELD_NAME,
a.DESCRIPTIVE_FLEX_CONTEXT_CODE,
a.APPLICATION_COLUMN_NAME,
a.END_USER_COLUMN_NAME
FROM
FND_DESCR_FLEX_COLUMN_USAGES a,
FND_DESCRIPTIVE_FLEXS_vl b
WHERE
b.APPLICATION_TABLE_NAME in (UPPER('<table_name>')) AND
b.DESCRIPTIVE_FLEXFIELD_NAME=a.DESCRIPTIVE_FLEXFIELD_NAME AND b.APPLICATION_ID=a.APPLICATION_ID

Posted by at
Labels:

2 comments:

  1. UnknownApril 24, 2016 at 8:37 AM

    This comment has been removed by the author.

    ReplyDelete
  2. UnknownApril 24, 2016 at 8:39 AM

    Here is the query for Unique Custom DFFs

    SELECT b.application_table_name
    , b.descriptive_flexfield_name
    , a.application_column_name
    , a.end_user_column_name
    FROM fnd_descr_flex_column_usages a
    , fnd_descriptive_flexs_vl b
    WHERE 1 = 1
    AND b.descriptive_flexfield_name = a.descriptive_flexfield_name
    AND b.application_id = a.application_id
    AND A.CREATED_BY not in (0, 1,2, 5, 120, 121)
    AND b.application_table_name != 'FND_SRS_MASTER'
    GROUP BY b.application_table_name
    , b.descriptive_flexfield_name
    , a.application_column_name
    , a.end_user_column_name
    ORDER BY 1,4

    Here is the query for custom DFFs with context values...

    SELECT b.application_table_name
    , b.descriptive_flexfield_name
    , a.application_column_name
    , a.end_user_column_name
    , a.descriptive_flex_context_code
    FROM fnd_descr_flex_column_usages a
    , fnd_descriptive_flexs_vl b
    WHERE 1 = 1
    AND b.descriptive_flexfield_name = a.descriptive_flexfield_name
    AND b.application_id = a.application_id
    AND A.CREATED_BY not in (0, 1,2, 5, 120, 121)
    AND b.application_table_name != 'FND_SRS_MASTER'
    ORDER BY 1,4

    ReplyDelete

Subscribe to: Post Comments (Atom)