• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5407
  • Last Modified:

fnd_profile_option_values update/insert

here the scenerio...I have numerous profiles to change at the responsibility level.  So I'm trying to do this via a script.

Basically there will be a responsibility_name and a profile_name passed to the script and the value that you want it changed to wll also be passed.  There is no problem if the value is already set, because there is now an entry in the fnd_profile_option_values table and everything works fine, BUT if there is no value set then there is no entry in the fnd_profile_option_values table to update.  So I was thinking you must have to insert a line into that table.  The problem is that I'm not sure where to get the information to insert into the table.

What I'm looking for is a pl/sql block that will try the update, if that fails then do an insert.

here's the sql that I have that works for an update, but I'm struggling with the insert.  Any assistance would be appreciated.

select r.responsibility_name, po.user_profile_option_name, pov.profile_option_value
from fnd_profile_option_values pov,
   fnd_responsibility r,
   fnd_profile_options po
where pov.application_id = po.application_id
and pov.level_value = r.responsibility_id
and pov.profile_option_id = po.profile_option_id
and po.user_profile_option_name = '&profile_name'
and r.responsibility_name = '&responsibility_name'
order by po.user_profile_option_name, r.responsibility_name;

update fnd_profile_option_values
set    profile_option_value = '&value'
where  (application_id, profile_option_id) in (
   select po.application_id, po.profile_option_id
   from   fnd_profile_options po,
    fnd_profile_option_values pov,
    fnd_responsibility r
where pov.application_id = po.application_id
and pov.level_value = r.responsibility_id
and pov.profile_option_id = po.profile_option_id
and po.user_profile_option_name = '&profile_name'
and r.responsibility_name = '&responsibility_name');  

1 Solution
I'm not providing a PL/SQL block for you, so I am just submitting this a few quick questions and ideas.  If not of these help you out, please reject the answer and give a few more details (and I'll see if I can help out).  If a row is not found for an update, it will not fail.  It will always succeed (usually with a 0 rows updated feedback line).  If you have referential integrity on your table so that you cannot INSERT if that row already exists (primary or unique key), try an exception block in PL/SQL like such



Since the INSERT will fail with a DUP_VAL_ON_INDEX exception when the unique constraint is violated, it will automatically go to the update code.
Mark GeerlingsDatabase AdministratorCommented:
DarkOra's proposed solution should work if your table has a primary or unique key.

A slight variation in PL\SQL would be to have a cursor that selects the rowid, if the row exists.  Then if a rowid is found do the update of that row, if not do an insert.  If in most cases the record already exists, this will be faster than DarkOra's approach of using an exception to trigger the update.
mbossAuthor Commented:
I guess I was kind vague when I read my question again,  What I really need is to know where the inforation in fnd_profile_option_values come from.  I just done want to go inserting any information into an Oracle base table.  I would like to insert this information as close to the way a form would do it as possible.

Here are the table fields

application_id, ---Where to get this
profile_option_id, ---Where to get this
level_id, ---Where to get this
level_value,---Where to get this
last_update_date,  ---don't worry about it
last_updated_by, ---don't worry about it
creation_date,  ---don't worry about it
created_by,  ---don't worry about it
last_update_login,  ---don't worry about it
profile_option_value,  --passed in
level_value_application_id  ---Where to get this

also, if responsibility_name and user_profile_option_name are passed into the procedure will that be enough to gaurantee singularity, or will a cursor be needed for multiple rows?

Sorry for the confusion, but Oracle's fnd tables really has me flustered on this one.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

mbossAuthor Commented:
just to clarify...these are FND tables, they're not my own creation.

fnd responsibilty

I'm not sure if all these tables are needed, but most are I think.
to insert a Profile Option u shud do the following :


2. Then you should also insert into the Transaltion Tables.
FND_PROFILE_OPTIONS_TL where u have a Language Column which is American for English.

For Update also these two tables are involved.

Note : while you post the question i think its better if you tell the team that its Oracle Financials question .

Yep, missed that it was a financials question, I mostly was commenting on the fact that an update generally won't fail.  Looks like bkesarla has the responded with some good info for you.  Good luck!
I agree with Bkesarla
mbossAuthor Commented:
thanks....sorry for not pointing out it was financials.  I'm still kinda new to being a DBA
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now