Using $FLEX$ in Value sets in Oracle

6 comments Saturday, December 01, 2012
$FLEX$ is used in value sets to get the selected value of some other value set on a field on the form.

So you could compare that value with a column in second value set and get the filtered result.

Let me show you through an example if you could not understand :)

Lets create two independent value sets.

Value set 1

Using $FLEX$ in value sets

Enter values 

Using $FLEX$ in value sets


Value set 2

Enter values


Scenario: I attached value set1 to form field 1 and created a Table validated value set and fetched the values from value set 2 on the basis of value selected in value set 1

How to create Table Validated Value set

Here are creation details of Table validated value set


Table Validated Value set for $FLEX$

Press Edit Information


TABLE NAME: fnd_flex_values_vl ffvv , FND_FLEX_VALUE_SETS ffvs

WHERE CLAUSE: 

where ffvv.flex_value_set_id = ffvs.flex_value_set_id
and ffvs.flex_value_set_name = 'XX_FLEX_2_VALUE_SET'
and ffvv.description = :$FLEX$.XX_FLEX_1_VALUE_SET

Value selected in field 1 is matched to description in XX_FLEX_2_VALUE_SET.

I attached value set 1 to form filed 1 and XX_FLEX_USAGE to form filed 2.

Here is the result.

Using $FLEX$ in Value sets

Bingo...We achieved the result...

Value Sets in Oracle Apps

Kindly provide your feedback.

Leave a comment for any query.


6 comments:

  1. Mayank1 August 2014 at 18:39

    My two value sets defined were
    Flex VS 1
    Flex VS 2

    so my where clause changed into

    where ffvv.flex_value_set_id = ffvs.flex_value_set_id
    and ffvs.flex_value_set_name = 'Flex VS 2'
    and ffvv.description = :$FLEX$.Flex VS 1


    Did everything as your screenshot suggested but when i tried to save the valueset with the table validation, I got this error

    Test of value set XX_Flex_Usage failed with following error message :
    ORA-00933: SQL command not properly ended
    select ffvv.flex_value VALUE from fnd_flex_values_vl ffvv , FND_FLEX_VALUE_SETS ffvs where ffvv.flex_value_set_id = ffvs.flex_value_set_id and ffvs.flex_value_set_name = 'Flex VS 2' and ffvv.description = null VS 1


    Also the last step is also not clear. How do we attach it to the form? I was trying to use the dependant and independent value set to work with the DFF's so that upon selection of the first attribute, second attribute values can be restricted.

    ReplyDelete
    Replies
    1. Muhammad Waqas1 August 2014 at 18:44

      Hi Mayank,

      Do not keep spaces while defining the names of the value sets.

      We attach the value sets to the attributes of DFF. Independent is attached before dependent value set.

      Regards,
      Waqas

      Delete
  2. Unknown23 October 2014 at 10:20

    Hi ,

    I have a concurrent program with three parameters.My requirement is make 3rd parameter optional/mandatory
    depend on value in 1st parameter.for this i have created hidden parameter and assign special valueset with below
    sql.As per my observation ':$FLEX$.XX_PRIMA_FORM_NAMES_VS' IS NULL is not working but IS NOT NULL.
    Please help on this.

    FND PLSQL "BEGIN
    IF ':$FLEX$.XX_B2B_ORDER_PROCESS_MODES'='CUSTOM' AND ':$FLEX$.XX_PRIMA_FORM_NAMES_VS' IS NULL THEN
    fnd_message.set_name('XXXPO', 'Please select Form name');
    fnd_message.raise_error;
    END IF;
    END;"

    Thanks,
    Charith.

    ReplyDelete
  3. Unknown25 February 2015 at 20:31

    Is there a way that you can enter a value in Using Flex 2 without having to entere a value into Using Flex 1?? At the moment for my development I have to enter a value in Flex 1 before I can enter a value in flex 2 - I want Flex 2 to be optional, however if Flex is populated to reference Flex 1

    Thanks

    ReplyDelete
  4. Unknown25 February 2015 at 20:33

    Is there a way that you can enter a value in Using Flex 2 without having to entere a value into Using Flex 1?? At the moment for my development I have to enter a value in Flex 1 before I can enter a value in flex 2 - I want Flex 2 to be optional, however if Flex is populated to reference Flex 1

    Thanks

    ReplyDelete
  5. Adouiri27 December 2016 at 21:46

    Hi, this dosn't work on OAF pages :(

    ReplyDelete

Subscribe to: Post Comments (Atom)