How to bulk load Profile Option values?
How to bulk load Profile Option values?

 865 total views

In this article we will look into how to bulk load profile option values into Fusion HCM.

With every new quarterly release 20A, 20B etc. Oracle releases set of Profile Options that needs to be enabled for Responsive UI. It will be a time consuming task to open each profile option code and set the value to Y/N.

In order to address this issue, we can prepare an input file with the list of all profile option values that we intend to load in a specific format and then use the import option to load them. If a value already exists, it will update. If an option value doesn’t exist, it will create a new one.

Let’s get started on how to do it.

Navigate to Setup and Maintenance > Manage Administrator Profile Values task.

For example we will take the RUI Profile Option Code – PER_MY_TEAM_RESPONSIVE_ENABLED and Read Only Profile Option Code – FND_READ_ONLY_MODE.

We can make a user having administrator access to read only access with a simple setting. Check out How to make a User Read Only using Administrator Profile Value?

PER_MY_TEAM_RESPONSIVE_ENABLED Current Value:

Profile Option Value
Profile Option Value

We can see that the Profile Option Value is set to Y at site level.

Let’s look at the profile option FND_READ_ONLY_MODE

Read only profile option
Profile values doesn’t exist for Read Only Option

We can see that there are no profile option values for this profile option.

Now, we will prepare a file to load profile option values into read only mode and change the value of profile value for PER_MY_TEAM_RESPONSIVE_ENABLED profile option.

File Format:

ProfileOptionCode|LevelName|UserName|ProfileOptionValue

This is the header for the file. All fields should be separated by pipe delimiter(|).

  • ProfileOptionCode – This is the profile option code value for which we want to load the values.
  • LevelName – This is the level at which we want to load the values. SITE or USER. There should be one more level for PRODUCT but it’s mostly not used.
  • UserName – The exact username of the user. It is Case Sensitive.
  • ProfileOptionValue – Y/N. For some options it is Yes/No and others it is Enabled/Disabled. Check in the application first and then fill in data in the input file.

The sample file for these profile options upload will look like:

ProfileOptionCode|LevelName|UserName|ProfileOptionValue
PER_MY_TEAM_RESPONSIVE_ENABLED|SITE||N
FND_READ_ONLY_MODE|USER|TEST.USER|Enabled

We are changing the profile option value for PER_MY_TEAM_RESPONSIVE_ENABLED to N at site level and making the TEST.USER as Read only using the FND_READ_ONLY_MODE option.

Save the file name as ProfileOptions_load.txt (Any name is fine).

Now Navigate to Tools > File Import and Export and upload this file to UCM.

Upload the input file to UCM
Upload the created file to UCM

Browse the input file and select the Account as hcm\dataloader\import and click Save and Close. The file will get uploaded to UCM.

We can also use SOAP services to upload the file to UCM

Now Navigate to Setup and Maintenance > Manage Administrator Profile Values task.

Under the Profile Option: Profile Values task, click on the Actions button and select Import

Select the import option from Actions
Select the import option from Actions

Once you select the import a popup appears to select the Account and Profile Options File name.

Select the Account and provide the input file name and click upload
Select the Account and provide the input file name and click upload

Select the Account as hcm\dataloader\import and provide the Profile Values File as “ProfileOptions_load.txt“.. We need to provide the exact file name that we have uploaded including the file extension. Click Upload.

It will process for some time and provides a success message and a link to download the log file. It’s important to download the log file and check the summary of the processed counts and error messages if any.

Success message
Click on the Download Log File
Log file details
Log file details

We can clearly see that 1 new record has been created and 1 has been updated. The Read only profile value has been created as this user did not have any row earlier and the RUI profile option has been updated.

RUI profile option successfully updated to N
RUI profile option successfully updated to N
TEST.USER has Read only Enabled now
TEST.USER has Read only Enabled now

In this way we can load any number of profile option values. This process is handy when you have to make lot of users Read-only or if there are many profile option values that need to set.

We can quickly verify the values in the profile options values using a SQL Query.

SQL Query:

SELECT pfob.profile_option_name ,fpov.LEVEL_NAME ,fpov.LEVEL_VALUE ,fpov.profile_option_value, 
decode(fpov.LEVEL_NAME,'USER', (select username from per_users where user_guid = fpov.LEVEL_VALUE),' ') Username
FROM fnd_profile_option_values fpov ,fnd_profile_options_b pfob WHERE fpov.profile_option_id = pfob.profile_option_id --and pfob.profile_option_name in ('List of Values') 
ORDER BY pfob.profile_option_name ,fpov.profile_option_value

Output will look like:

Profile Option Values report
Profile Option Values report

If you have any questions, please feel free to reach out to me by posting in comments section.

If you are interested in learning all Fusion Technical tools go through this article.

If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 3 Comments

  1. Harish Vummidi

    Hi.

    When i run the SQL Query i got a error msg “Duplicate data set name, name must be unique”.

  2. Harish Vummidi

    Hi ..

    Do you have a list of all the profile values ?

  3. Sricharan

    Hi Harish,
    Create a new data model and add this SQL. I think you are creating a data set with same name and hence duplicate data set error..
    This sql query will get you the list of all profile values available in your system.
    Thanks,
    Sricharan

Leave a Reply