Sql给参数文件(MO:Operting Unit) MO 业务实体赋值

方法一：

1。检索所有的org_id列表。
select t.organization_id, t.name from hr_organization_units t
确定你要在site上设置的org_id

2。 然后执行如下的脚本。

SELECT pro.profile_option_name,
       pro.user_profile_option_name,      
       lev.level_type TYPE,      
       lev.level_name,      
       prv.profile_option_value,      
       prv.ROWID
  FROM apps.fnd_profile_options_vl       pro,
       applsys.fnd_profile_option_values prv,      
       (SELECT 10001 level_id,              
               'Site' level_type,              
               0 level_value,              
               'Site' level_code,              
               'Site' level_name       
          FROM dual       
        UNION ALL
        SELECT 10002 level_id,              
               'App' level_type,
               app.application_id level_value,              
               app.application_short_name level_code,              
               app.application_name level_name       
          FROM apps.fnd_application_vl app
        UNION ALL       
        SELECT 10003 level_id,              
               'Resp' level_type,              
               resp.responsibility_id level_value,              
               resp.responsibility_key level_code,              
               resp.responsibility_name level_name       
          FROM apps.fnd_responsibility_vl resp       
        UNION ALL       
        SELECT 10004 level_id,              
               'User' level_type,              
               usr.user_id level_value,              
               usr.user_name level_code,              
               usr.user_name level_name       
          FROM applsys.fnd_user usr) lev
 WHERE pro.profile_option_id = prv.profile_option_id(+)     
   AND prv.level_id = lev.level_id(+)     
   AND prv.level_value = lev.level_value(+)     
   AND pro.user_profile_option_name LIKE 'MO：业务实体' --Profile名称        
   AND lev.level_type = 'Site'
 ORDER BY pro.profile_option_name, lev.level_type, lev.level_name

3。修改配置文件的值，执行如下的脚本
update fnd_profile_option_values t
   set t.profile_option_value = '第一步确定的orgid'
where t.rowid = '第二步确定的rowid'

假设第二步没有检索到记录，请先执行，先插入一条记录。

create table fnd_profile_option_values_bak as
SELECT * FROM fnd_profile_option_values;
INSERT INTO fnd_profile_option_values
  (application_id,
   profile_option_id,
   level_id,
   level_value,
   last_update_date,
   last_updated_by,
   creation_date,
   created_by,
   last_update_login,
   profile_option_value,
   level_value_application_id,
   level_value2)
VALUES
  (0, 1991, 10001, 0, SYSDATE, 1, SYSDATE, 1, 1, 0, NULL, NULL);

 

方法二：

--用FND_PROFILE.SAVE函数过程修复这个问题
declare
  -- Local variables here
  i integer;
begin
  -- Test statements here
  DECLARE
stat boolean;
BEGIN

stat := FND_PROFILE.SAVE('ORG_ID',82, 'SITE');
-- 82 是要地点层默认的组织ID
dbms_output.put_line( 'Stat = TRUE - profile updated' );
end;

 

--整理于： http://bbs.erp100.com/viewthread.php?tid=33525&highlight=MO

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24908207/viewspace-690287/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24908207/viewspace-690287/