Query to get Flexfield Prompts using table name

Query to get Flexfield Prompts

SELECT params.application_id,

       params.descriptive_flexfield_name,

       params.descriptive_flex_context_code,

       params.application_column_name,

       params.end_user_column_name,

       params.form_left_prompt,

       params.description,

       params.column_seq_num,

       dflx.APPLICATION_TABLE_NAME,

       params.required_flag,

       params.enabled_flag,

       params.display_flag,

       params.display_size,

       params.flex_value_set_id

  FROM apps.FND_DESCR_FLEX_COL_USAGE_VL  params,

       apps.FND_DESCRIPTIVE_FLEXS_vl     dflx

 WHERE     1 = 1

       --and params.descriptive_flexfield_name  like 'Global%'

       --and upper(params.form_left_prompt)  upper('%Name%')

       AND dflx.application_id = params.application_id

       AND dflx.application_table_name = UPPER ('ra_customer_trx_lines_all')

       --AND params.descriptive_flex_context_code='RA_CUSTOMER_TRX_LINES'

       --and params.application_column_name='ATTRIBUTE6'

       --And application_column_name='ATTRIBUTE5'

       AND params.DESCRIPTIVE_FLEXFIELD_NAME =

           dflx.DESCRIPTIVE_FLEXFIELD_NAME;

/

Query to get DFF segments for a given table name

Query to get DFF segments for a given table name

SELECT dlfx_col.application_id,
       dlfx_col.descriptive_flexfield_name,
       dlfx_col.descriptive_flex_context_code,
       dlfx_col.application_column_name,
       dlfx_col.end_user_column_name,
       dlfx_col.form_left_prompt,
       dlfx_col.description,
       dlfx_col.column_seq_num,
       dflx.APPLICATION_TABLE_NAME,
       dlfx_col.required_flag,
       dlfx_col.enabled_flag,
       dlfx_col.display_flag,
       dlfx_col.display_size,
       dlfx_col.flex_value_set_id
  FROM apps.FND_DESCR_FLEX_COL_USAGE_VL  dlfx_col,
       apps.FND_DESCRIPTIVE_FLEXS_vl     dflx
 WHERE     1 = 1
       AND dflx.application_id = dlfx_col.application_id
       AND dflx.application_table_name = UPPER (':P_TABLE_NAME') -- Input table name
       AND dlfx_col.descriptive_flex_context_code = :P_DFF_CONTEXT -- Input Context code, For global category attributes you can comment
       AND dlfx_col.DESCRIPTIVE_FLEXFIELD_NAME =
           dflx.DESCRIPTIVE_FLEXFIELD_NAME;

Defaulting value on a DFF Segment using sql

Here are the steps to default a value on a DFF segment:

1) Go to DFF definition screen and open the segment which you want to add/update:

2) Click on Open button, then you see following screen:

3)  For default type select “SQL Statement” and in the default value give your sql:       

4) If you want to reference other fields in the flex you can do by using :$FLEX$.segment1

Example:

Select  col1   From sk_temp xt

Where 1=1 And  :$FLEX$.segment1 = xt.col2

Use :$FLEX$.valueset name if segement1 is based on a value set