EBS中如何后台批量更改Profiles的值

blueice

　　EBS中如何后台批量更改Profiles的值
　　（版权声明，本人原创或者翻译的文章如需转载，如转载用于个人学习，请注明出处；否则请与本人联系，违者必究）
　　自己写来管理自己负责维护的EBS instance，经常有人设置了一些调试用的profile忘记关闭，结果导致服务器性能变差，可用空间变小，所以写了这个脚本。

　　1.如果要增加修改的profile, 需要同时在l_profNames 增加变量名， 在 l_profNewVal增加变量值。
　　2.l_profSpeUser， 如果只更改一个用户的profile值，就需要设置这个用户名，必须大写，而且这个值拥有最高优先级。
　　3.l_profSpeResp， 如果只更改一个职责的profile值，就需要设置这个职责名，而且这个值拥有第二高优先级。
　　4.l_profSpeApp， 如果只更改一个应用的profile值，就需要设置这个用户名，必须大写，而且这个值拥有第三最高优先级。
　　5.如果即不指明用户，职责和应用，那就对所有层次更新所有层次的profiles。
　　6.l_profExceptUser, 如果对所有层次更新profiles，那么这里指定的用户会被忽略，不会被更新，用户名要大写。
　　7.l_profIncludeSite，如果对所有层次更新profiles的时候，这个变量指明会不会更新站点层次的值。'N'是不更新,'Y'是更新。


/**
* Batch update profile values from back end. Mainly used to
* enable/disable debug profiles on dev instance
* Author:tavor
* Date:2014-01-08
* Revision:1
*******************************************************************************************
**** IF YOU ARE NOT INSTANCE OWNER/ADMINISTRATOR, PLEASE BE CAREFUL TO RUN THIS SCRIPT*****
*******************************************************************************************
* 1.If need to add more profile, need to specify profile name in l_profNames
* and new value in l_profNewVal.
* 2.l_profSpeUser is the user we just update profile for him/her, and this has highest priority.
* 3.l_profSpeResp is the resp we just update profile for it, and this has less higher priority.
* 4.l_profSpeApp is the App we just update profile for it, and this has less the third high priority.
* 5.If l_profSpeUser, l_profSpeResp and l_profSpeApp are all null, then we will update profiles for
* all resp, user and app level.
* 6.l_profExceptUser, If we update profiles for all levels, we will not update profile values
* for this user.
* 7.l_profIncludeSite, when we update profiels for all levels, this variable indidate if we also
* update profile values at site level. 'N', will not udpate for site for level, 'Y' for the opposite.
**/
DECLARE
--l_profCount NUMBER :=3;
TYPE HWDebugType IS VARRAY(10) OF VARCHAR2(240);
l_profNames HWDebugType;
l_profIDs HWDebugType;
l_profNewVal HWDebugType;
l_profIncSite HWDebugType;
l_profAllLevel HWDebugType;
l_profSpeUser VARCHAR2(20) := 'HONWEI1';--this should be in upper case
l_profSpeUserID NUMBER := NULL;
l_profSpeResp VARCHAR2(20) := NULL;
l_profSpeRespID NUMBER := NULL;
l_profSpeApp VARCHAR2(20) := NULL;
l_profSpeAppID NUMBER := NULL;
l_profExceptUser VARCHAR2(20) := NULL;--this should be in upper case
l_profExceptUserID NUMBER :=NULL;
l_profIncludeSite VARCHAR2(5) := 'N';
l_userLevel NUMBER := 10004;
l_respLevel NUMBER := 10003;
l_appLevel  NUMBER := 10002;
l_siteLevel NUMBER := 10001;
l_profileId NUMBER := NULL;
l_space     VARCHAR2(20)  :='   ';

CURSOR CURRENT_PROFILE_VALUES (p_profileName VARCHAR2) IS
SELECT  a.profile_option_id,t.user_profile_option_name cProfileOption,
decode(a.level_id,     10001,     'Site',
10002,     'Application',
10003,     'Responsibility',
10004,     'User') cLevel,
decode(a.level_id,     10001,     'Site',
10002,     b.application_short_name,
10003,     c.responsibility_key,
10004,     d.user_name) cLevelValue,
a.profile_option_value cProfileValue
FROM fnd_profile_option_values a,
fnd_application b,
fnd_responsibility c,
fnd_user d,
fnd_profile_options e,
fnd_profile_options_tl t
WHERE a.profile_option_id = e.profile_option_id
AND e.profile_option_name = p_profileName
AND a.level_value = b.application_id(+)
AND a.level_value = c.responsibility_id(+)
AND a.level_value = d.user_id(+)
AND t.profile_option_name = e.profile_option_name
AND t.LANGUAGE = 'US'
ORDER BY a.profile_option_id,e.profile_option_name, a.level_id DESC;
l_curProfileValues CURRENT_PROFILE_VALUES%ROWTYPE;
BEGIN
l_profNames := HWDebugType( 'INV_DEBUG_TRACE'    --
--,'AFLOG_ENABLED'     --
--,'ICX_FORMS_LAUNCHER'--
);
l_profNewVal := HWDebugType( '2'    --
--   ,'N'     --
--   ,NULL--
);
l_profIncSite := HWDebugType('Y'    --
,'Y'     --
,'N'--
);
l_profAllLevel := HWDebugType('Y'    --
,'Y'     --
,'Y'--
);
IF l_profSpeUser IS NOT NULL AND l_profSpeUserID IS NULL THEN
SELECT USER_ID
INTO   l_profSpeUserID
FROM   FND_USER
WHERE  user_name = l_profSpeUser;
END IF;
Dbms_Output.put_line('Specified User:'||l_profSpeUser||', ID:'||l_profSpeUserID);

IF l_profExceptUser IS NOT NULL AND l_profExceptUserID IS NULL THEN
SELECT USER_ID
INTO   l_profExceptUserID
FROM   FND_USER
WHERE  user_name = l_profExceptUser;
END IF;
Dbms_Output.put_line('Except for User:'||l_profExceptUser||', ID:'||l_profExceptUserID);

IF l_profSpeResp IS NOT NULL AND l_profSpeRespID IS NULL THEN
SELECT responsibility_id
INTO   l_profSpeRespID
FROM   FND_RESPONSIBILITY_VL
WHERE  responsibility_name =l_profSpeResp;
END IF;
Dbms_Output.put_line('specified Responsibility:'||l_profSpeResp||', ID:'||l_profSpeRespID);

IF l_profSpeApp IS NOT NULL AND l_profSpeAppID IS NULL THEN
SELECT APPLICATION_ID
INTO   l_profSpeAppID
FROM   FND_APPLICATION_VL
WHERE  APPLICATION_NAME = l_profSpeApp;
END IF;
Dbms_Output.put_line('specified Application:'||l_profSpeApp||', ID:'||l_profSpeAppID);


FOR i IN 1..l_profNames.count loop
Dbms_Output.put_line('profile name:'||l_profNames(i));
Dbms_Output.put_line('Profile Option'||l_space||'Level'||l_space||'Level Value'||l_space||'Profile Value');
FOR currProf_rec IN CURRENT_PROFILE_VALUES(l_profNames(i)) LOOP
Dbms_Output.put_line(currProf_rec.cProfileOption||l_space||currProf_rec.cLevel||l_space||currProf_rec.cLevelValue||l_space||currProf_rec.cProfileValue);
END LOOP;
SELECT profile_option_id
INTO   l_profileId
FROM   fnd_profile_options
WHERE  profile_option_name = l_profNames(i);
if l_profSpeUserID is NOT null THEN
UPDATE fnd_profile_option_values
SET    profile_option_value = l_profNewVal(i) -- profile value
WHERE  profile_option_id = l_profileId        -- profile id
AND    level_id    = l_userLevel              -- level  id
AND    level_value = l_profSpeUserID          -- level value
AND    profile_option_value <> l_profNewVal(i);
Dbms_Output.put_line('Have set profiles at user level for userID:'||l_profSpeUserID);
elsif l_profSpeRespID is NOT null THEN
UPDATE fnd_profile_option_values
SET    profile_option_value = l_profNewVal(i) -- profile value
WHERE  profile_option_id = l_profileId        -- profile id
AND    level_id    = l_respLevel              -- level  id
AND    level_value = l_profSpeRespID          -- level value
AND    profile_option_value <> l_profNewVal(i);
Dbms_Output.put_line('Have set profiles at resp level for respID:'||l_profSpeRespID);
elsif l_profSpeAppID is NOT null THEN
UPDATE fnd_profile_option_values
SET    profile_option_value = l_profNewVal(i) -- profile value
WHERE  profile_option_id = l_profileId        -- profile id
AND    level_id    = l_appLevel               -- level  id
AND    level_value = l_profSpeAppID           -- level value
AND    profile_option_value <> l_profNewVal(i);
Dbms_Output.put_line('Have set profiles at app level for appID:'||l_profSpeAppID);
ELSE
UPDATE fnd_profile_option_values
SET    profile_option_value = l_profNewVal(i) -- profile value
WHERE  profile_option_id = l_profileId        -- profile id
AND    profile_option_value <> l_profNewVal(i)
AND    level_id <> Decode(l_profIncludeSite, 'Y', -1, l_siteLevel)
AND    (  (l_profExceptUserID IS NOT NULL AND (    (level_value <> l_profExceptUserID AND level_id = l_userLevel)
OR (level_id <> l_userLevel)))
OR l_profExceptUserID IS NULL);
Dbms_Output.put_line('Have set profiles for all level, l_profIncludeSite:'||l_profIncludeSite||',l_profExceptUserID:'||l_profExceptUserID);
end if;
Dbms_Output.put_line('After update profile value for profile name:'||l_profNames(i));
Dbms_Output.put_line('Profile Option'||l_space||'Level'||l_space||'Level Value'||l_space||'Profile Value');
FOR currProf_rec IN CURRENT_PROFILE_VALUES(l_profNames(i)) LOOP
Dbms_Output.put_line(currProf_rec.cProfileOption||l_space||currProf_rec.cLevel||l_space||currProf_rec.cLevelValue||l_space||currProf_rec.cProfileValue);
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
Dbms_Output.put_line('SQLCODE:'||SQLCODE||', SQLERRM:'||SQLERRM);
END;