Value Sets Access Issue in R12.2+

Issue Summary : Unable to view Value Set Values in Oracle EBS R12.2

- Oracle E-Business Suite (EBS) value sets do not appear in the List of Values (LOV) in the Values form FNDFFMSV after upgrading to Oracle E-Business Suite 12.2.2 or higher.
- Users are not able to insert or update any independent or dependent value set values after the upgrade
  
Analysis
 
- Release 12.2.2 includes a new security feature, flexfield value set security, to control who can create or modify flexfield values in the Flexfield Values setup form (FNDFFMSV).  Because this is a security feature, it is enabled by default.

- After the upgrade, users will not be able to create or modify any independent or dependent value sets in the Flexfield Values setup form (FNDFFMSV) until access is specifically granted to that user by setting up the feature.

1. Release 12.2 onwards Oracle has introduced a new feature of 'Flexfield value set security'. 

2. This features controls who can view, insert, or update values for a particular value set (by flexfield, report, or value set) in the Segment Values form (FNDFFMSV). Flexfield value set security affects independent and dependent value sets for flexfields and report parameters, including Independent, Translatable Independent, Dependent, and Translatable Dependent value set types. Flexfield value set security also affects parent values for Table Validated value sets where the "Allow Parent Values" flag is checked for the value set. 
3. The effect of flexfield value set security is that a user of the Segment Values form will only be able to view those value sets for which the user has been granted access. Further, the user will be able to insert or update/disable values in that value set if the user has been granted privileges to do so.   

Initial State of the Feature upon Upgrade 
    
When you initially install or upgrade to Release 12.2.2, no users are allowed to view, insert or update any value set values. You must explicitly set up access for specific users by enabling appropriate grants and roles for those users.
We recommend using flexfield value set security as part of a comprehensive Separation of Duties strategy. However, if you choose not to implement flexfield value set security upon upgrading to or installing Release 12.2, you have two simple options to give users access to all value sets for backwards compatibility:

1. Assign the seeded unlimited-access role ("Flexfield and Report Values: All privileges") directly to users, responsibilities, or other roles. With this option, users who have function security access to the Segment Values form and have this role either directly or indirectly can see, insert, and update values for any value set.
   
2. Create an "all-value-sets, all-privileges, all-users" grant (complete backwards compatibility, described later). With this option, any users who have function security access to the Segment Values form can view, insert, and update values for any value set. This option is very easy to set up, but it is not recommended because it defeats the purpose of the Separation of Duties feature.

If you decide later that you want to implement flexfield value set security as part of your Separation of Duties controls, you can delete such grants (revoke privileges) or end-date the role assignments or the grants.

Solution

1. You can have to create value set specific "Roles" and then grant those roles to your specific users. Once done, the users will be able to view or modify those specific value sets. This has to be done via 'User Management' responsibility.

2. As an alternate, Oracle has given the option of backword compatibility, which is a feature like releases prior to R12.2, wherein users could view all value sets and have the access to update them. To this this take the following steps:

2.1. Login as SYSADMIN and navigate to 'User Management' responsibility.
2.2. Query the user that you want to grant access to all value sets > and grant the following role 

• Role = Flexfield Value Set Security: All privileges
• Code = UMX|FND_FLEX_VSET_ALL_PRIVS_ROLE

2.3 This role provides view, insert and update privileges for the values of all independent and dependent value sets for flexfields and report parameters. This role should ONLY be assigned to users for backwards compatibility. The result of having this role is that if the user has access to the Values form (Key Flexfield, Descriptive Flexfield, and Validation) on a menu, the user can modify all values for all flexfields and report parameters.

Script to Provide the access from Backend(with apps user)

Begin
  WF_LOCAL_SYNCH.PROPAGATEUSERROLE(p_user_name => 'SREEKANTH',
                                   p_role_name => 'UMX|FND_FLEX_VSET_ALL_PRIVS_ROLE');
  commit;
end;

References

• http://docs.oracle.com/cd/E26401_01/doc.122/e22963/T354897T623930.htm
• Doc id 1612727.1 "Flexfield Value Sets Do Not Appear in List of Values (LOV) in Oracle E-Business Suite Release 12.2 on Segment Values Form FNDFFMSV"
• Oracle® E-Business Suite > Flexfields Guide Release 12.2 Part No. E22963-07 > Lesson 6 "Flexfield Value Set Security" > Initial State of the Feature upon Upgrade