1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to know Where My Value Set was used in oracle apps?

Discussion in 'General' started by jagadekara, Sep 26, 2013.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi viewers,
    I created one value set in oracle apps a year ago. But i need to change it now but i don't know where it was used actually may be some others are also using my value set. So how to find where my value set was used?

    Thanks & Regards,
    Jagadekara Reddy
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi

    Try with the below query.
    Code (SQL):
    SELECT
    fcpl.user_concurrent_program_name "Concurrent Program Name",
    fcp.concurrent_program_name "Short Name",
    fdfcuv.column_seq_num "Column Seq Number",
    fdfcuv.end_user_column_name "Parameter Name",
    fdfcuv.form_left_prompt "Prompt",
    fdfcuv.enabled_flag " Enabled Flag",
    fdfcuv.required_flag "Required Flag",
    fdfcuv.display_flag "Display Flag",
    fdfcuv.flex_value_set_id "Value Set Id",
    ffvs.flex_value_set_name "Value Set Name",
    flv.meaning "Default Type",
    fdfcuv.DEFAULT_VALUE "Default Value"
    FROM
    fnd_concurrent_programs fcp,
    fnd_concurrent_programs_tl fcpl,
    fnd_descr_flex_col_usage_vl fdfcuv,
    fnd_flex_value_sets ffvs,
    fnd_lookup_values flv
    WHERE
    fcp.concurrent_program_id = fcpl.concurrent_program_id
    --AND fcpl.user_concurrent_program_name = :conc_prg_name
    AND fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
    AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
    AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
    AND flv.lookup_code(+) = fdfcuv.default_type
    AND fcpl.LANGUAGE = USERENV ('LANG')
    AND flv.LANGUAGE(+) = USERENV ('LANG')
    AND ffvs.flex_value_set_name LIKE :P_VALU_SET_NAME
    ORDER BY fdfcuv.column_seq_num;
    Regards
    Samba
     
    jagadekara likes this.
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks sambuduk but this is only related concurrent programs. But that value set may attached to some dff also. so how can we find all?
     
  4. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Jagadekar,

    Try the below query for DFF.

    Code (SQL):
    SELECT  fdfcuv.descriptive_flex_context_code "DFF Context Field CODE" ,
            fdfcuv.descriptive_flexfield_name "Context Code",
            fdfcuv.column_seq_num "Column Seq Number",
            fdfcuv.end_user_column_name "Parameter Name",
            fdfcuv.form_left_prompt "Prompt",
            fdfcuv.enabled_flag " Enabled Flag",
            fdfcuv.required_flag "Required Flag",
            fdfcuv.display_flag "Display Flag",
            fdfcuv.flex_value_set_id "Value Set Id",
            ffvs.flex_value_set_name "Value Set Name",
            flv.meaning "Default Type",
            fdfcuv.DEFAULT_VALUE "Default Value"
    FROM
            fnd_descr_flex_col_usage_vl fdfcuv,
            fnd_descr_flex_contexts_tl fdf,
            fnd_flex_value_sets ffvs,
            fnd_lookup_values flv
    WHERE  fdfcuv.descriptive_flexfield_name = fdf.descriptive_flexfield_name
    AND fdf.LANGUAGE(+) = USERENV ('LANG')
    AND fdfcuv.descriptive_flex_context_code = fdf.descriptive_flex_context_code
    AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
    AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
    AND flv.lookup_code(+) = fdfcuv.default_type
    AND flv.LANGUAGE(+) = USERENV ('LANG')
    AND ffvs.flex_value_set_name LIKE :P_VALU_SET_NAME

    Rgds
    Samba
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    hai,
    The second query returns some values but those are not correct. I tested one value set which is not attached to any dff but your query giving dff context code as "Global Data Elements" and returns two rows.
     
  6. Ecor

    Ecor Active Member

    Messages:
    20
    Likes Received:
    3
    Trophy Points:
    90
    Hi Jagadekar,

    Do you got the answer? if yes then post it here.....
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Ecor,
    In value Set creation Window click on Usages Button there we can see where our value set was used. In below image we can see DFF Usages.
     

    Attached Files:

    • 1.JPG
      1.JPG
      File size:
      60.9 KB
      Views:
      35
    Ecor likes this.
  8. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    In this image we can see the concurrent programs and parameters where our value set was used.
     

    Attached Files:

    • 2.JPG
      2.JPG
      File size:
      84.1 KB
      Views:
      31
    Ecor likes this.
  9. shreya92

    shreya92 Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    I wanted to know some views on Oracle applications.
    Rolta been a very good company in terms of services and quality satisfying the consumers.
    So i want to contact them, so wanted to know if i m on a right path.