How to Refer or validate Oracle DFF based on other segments /Segments that depend on another field

Posted by & filed under , , , .

I have requirement to auto populate Oracle DFF segment value based other segment. As per bellow setup screenshot we have populated Employee’s Name in Segment11  based on employee number which is in Segment10

select GLOBAL_NAME from PER_ALL_PEOPLE_F Where EMPLOYEE_NUMBER = :$FLEX$.Employee_Number and sysdate between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE

—————————-Further references————————————-

There are few ways to make particular flexfield segment dependent on another segment from same flexfield. They are

  1. Attaching valuesets that are of type independent/dependent
  2. Using:$FLEX$ reference
  3. Using special valuesets
  4. Using :BLOCK.FIELD_NAME reference
  5. Other approaches like triggers, dynamic triggers etc that can kick in when record is saved.

This post explores these options further by discussing the implementation approach and the pros and cons of each one of them. The examples are from HRMS/HCM side of E-Business suite.

Attaching valuesets that are of type independent/dependent

Set up the flex and valueset as shown









Pros

  • Easy to implement, no coding involved. Handy when the number of values is small in number.
  • Works well via both via Forms (PUI) and Self-service (HTML) based pages
  • Dependent segment is not enabled till Independent segment value is entered.

    Cons

  • Potential duplicate values that need to be maintained in multiple dependent valuesets. For example, if values ‘C1′,’C2’ etc are available to both ‘A’ and ‘B’, they need to be entered against ‘A’ and again for ‘B’ in ‘XX_ET_SECOND_SEG_D’.
  • Using:$FLEX$ reference

    Use table validated valueset (or have a default clause)and use :$FLEX$ to refer to previous segment.  

    Start of with 2 segments. In order for segment to be referenced using :$FLEX$, rename the column name to FIRST_SEGMENT as shown.


     Create a table validated value set which will be attached to second segment.



    And reference the first segment in it as shown below.


    Attach the valueset to Second Segment and recompile the flexfield.


    Once done, navigate to Element screen, descriptive flexfield section.
    When selected A against first segment,  Second segment shows relevant values. 



    But when selected D, the second segment does not have valid value but remains mandatory. 




    In order to overcome this error, either make second segment as non-mandatory or follow approach below. 


    Attach a dummy segment and make it non-mandatory. Ensure its sequence is before second segment. For now, display this field. It can be hidden later.


    Provide a default value along the following lines

    Select 1 from dual                                             

    where exists 
               (select null from fnd_lookup_values_vl
                 where lookup_type=’XX_ET_SECOND_SEG_LOOKUP’
                 and  lookup_code like :$FLEX$.FIRST_SEGMENT || ‘%’
                ) 


    Modify the valueset attached to Second Segment to reference this dummy segment.


    Once done , the flexfield will behave the same way for value like ‘A’



    But when it comes to value like D for which there doesn’t exist value in second segment, the DUMMY field does not get populated and hence Second Segment remains disabled.

    Please note that this does not work with OAF pages.


    Pros 

  • Works for large number of values as well
  • Works via both forms and OAF 

    Cons

  • Can not access other fields from base form

    Using special valuesets

    Create a special valueset as shown. Please note that this does not associate List of Values with segment.





    Attach this valueset to Second Segment. 


    In Element screen, Second Segment remains disabled till value is entered for first.



    Enter number value for First Segment.



    For Second segment, when value is entered less than first segment, error is thrown.


    Pros

  • Provides programmatic control

    Cons

  • Does not work in HRMS’ OAF based pages 

    Using :BLOCK.FIELD_NAME reference

    This is similar to :$FLEX$ approach. I have used Default sql approach to explain its behaviour.


    Select :ELEMENT_TYPES.ELEMENT_NAME || :$FLEX$.FIRST_SEGMENT from dual



    Pros

  • Easy mechanism to default value from other fields , i.e fields that are part of base form but are not part of flex segments. Please note that it can also be used in where clause of table validated valueset.

    Cons

  • Does not work in OAF based pages. 

Leave a Reply

Your email address will not be published. Required fields are marked *