In my last post, I walked through the process for restricting specific responsibilities from creating or updating Key Flexfield Segment Values. For smaller companies with the same people doing multiple functions, this could be enough to meet this requirement. For my client, this restriction wasn’t enough. They needed to prevent specific responsibilities from seeing specific Flexfields, Titles, Structures, and Segments.
The first issue we experience is the launch screen for Key Flexfield Values. While there are four methods to find Segment Values, there is no way to default a method other than “Find Values by Key Flexfield”.
The first step towards controlling what the responsibility has access to is to force them to “Find Values by” Key Flexfield. This means disabling the other radio buttons. In total, there are eight radio buttons (four on the find screen, four on the “Segment Values” screen), so we will need to disable six of them, leaving just the “Key Flexfield” options active. Once you change the Object Type to Radio Button, the list of values for Target Object will show all eight radio buttons (and their descriptions). We want to disable all that don’t show a prompt of “Key Flexfields”.
After disabling all six radio buttons, we can see the only option we have upon opening this form is “Key Flexfield Values”
The next item we need to restrict is the Application Name. My client wanted to restrict this responsibility to only see General Ledger titles, structures, and segments. To meet this requirement, I can default “General Ledger” as the Application Name, and restrict the ability to update it.
So far, everything has been easy – disabling radio buttons, defaulting value for a field, and preventing that value from being changed. However, if we want to give our users a limited selection of Key Flexfield Titles, Structures and Segments, we will need some help from a technical resource.
To change the List of Values available on a form, we first need to know the SQL that is behind the List of Values, which is where our technical resource comes in. Using Forms Builder, they can get you the SQL code behind the List of Values. We can then modify that same SQL code and store it in a record group, and use Forms Personalization to call the record group, rather than the SQL.
As a caveat, all the SQL shown in this blog entry is for illustrative purposes only, and should not be used on your instance.
The SQL that sits behind the List of Values for Title is:
select id_flex_name, id_flex_code
from fnd_id_flexs
where ((:key_qf.application_id is null) or
(application_id = :key_qf.application_id))
order by id_flex_name
This SQL is pulling back either all the key flexfields, or the flexfields associated with a specific application (if an application has been selected). The flex_code_name for the “Accounting Flexfield” is ‘GL#’, so the updated SQL would say:
select id_flex_name, id_flex_code
from fnd_id_flexs
where ((:key_qf.application_id is null) or
(application_id = :key_qf.application_id))
and id_flex_code = 'GL#'
order by id_flex_name
We can now take this code, and insert it into a Record Group, then call this record group from the List of Values.
Now we know how to restrict List of Values, we can use the same approach to restrict the Flexfield Structure. I only want to show users the “Operations Accounting Flexfield” (ID_FLEX_NUM=101) and the “Progress UK Accounting Flexfield” (ID_FLEX_NUM=51231). My technical analyst has retrieved the SQL behind the List of Values as follows:
select id_flex_structure_name, id_flex_num
from fnd_id_flex_structures_vl
where ((:key_qf.application_id is null) or
(application_id = :key_qf.application_id))
and ((:key_qf.id_flex_code is null) or
(id_flex_code = :key_qf.id_flex_code))
order by id_flex_structure_name
My modified SQL, which I will use in another Record Group will be:
select id_flex_structure_name, id_flex_num
from fnd_id_flex_structures_vl
where ((:key_qf.application_id is null) or
(application_id = :key_qf.application_id))
and ((:key_qf.id_flex_code is null) or
(id_flex_code = :key_qf.id_flex_code))
and id_flex_num in (101, 51231)
order by id_flex_structure_name
Using this previous example, we could also restrict the segments themselves (so responsibility A can only see Cost Centers, for example).
All of the restrictions we’ve put in place so far will affect the ‘Find Screen”. However, nothing we’ve designed so far will restrict the determined user who tries to run a query directly from the Segment Values screen.
By default, the form will allow a user to query for value sets with a Validation Type of Independent, Dependent, Translatable Independent, Translatable Dependent, or Table-validated value sets which allow Summary display, and behind the scenes, this block includes code to restrict this.
Our technical resource retrieved the SQL to show:
((validation_type IN ('I', 'D', 'X', 'Y')
OR (validation_type = 'F' AND summary_allowed_flag = 'Y'))
I created a DEFAULT_WHERE personalization to update the SQL to show:
((validation_type IN ('I', 'D', 'X', 'Y')
OR (validation_type = 'F' AND summary_allowed_flag = 'Y')) AND
flex_value_set_id in (1002470, 1002471, 1002472, 1002473, 1002474))
<img class="size-medium wp-image-528" src="http://www.biztech.com/blog/wp-content/uploads/10-300x179.jpg" alt="" width="300" height="179" />
Now, users with this responsibility will only be able to see 5 segments, regardless of how the try to access the form.
