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');  

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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 .


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.