EBS 通用常用脚本
前言
此文分享了EBS中非特定模块的一些通用脚本。
脚本清单
1. 模拟登录
DECLARE
/*
此脚本用于测试时，模拟运行环境
*/
l_user_name VARCHAR2(240) := 'XXX';
l_user_id NUMBER;
l_resp_name VARCHAR2(240) := 'CUX_XXX职责';
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
l_org_id NUMBER := 81;
BEGIN
SELECT fu.user_id
INTO l_user_id
FROM fnd_user fu
WHERE fu.user_name = l_user_name;
SELECT fr.responsibility_id
,fr.application_id
INTO l_resp_id
,l_resp_appl_id
FROM fnd_responsibility_vl fr
WHERE fr.responsibility_name = l_resp_name;
fnd_global.apps_initialize(user_id => l_user_id
,resp_id => l_resp_id
,resp_appl_id => l_resp_appl_id);
/*
mo_global.init('ONT'); --初始化模块
mo_global.set_policy_context(p_access_mode => 'S'
,p_org_id => l_org_id); --设置 ORG_ID，单业务实体模式
*/
END;
2. 查找代码
/*
此脚本用于根据关键词检索全局，查找相关代码
常用场景：标准模块提示错误时，可根据以下SQL查询错误消息对应的消息CODE
SELECT *
FROM fnd_new_messages fnm
WHERE fnm.message_text LIKE '%错误信息%';
然后根据消息CODE检索全局，定位代码位置
*/
SELECT *
FROM dba_source ds
WHERE upper(ds.text) LIKE upper('%&KEY%')
AND ds.name LIKE 'CUX%';
示例：根据错误信息查找消息代码，后根据消息代码定位代码位置：
DECLARE
l_message_name VARCHAR2(240);
l_search_text VARCHAR2(240) := '&SEARCH_TEXT';
BEGIN
FOR rec_message IN (SELECT fnm.message_name
,fnm.message_text
FROM fnd_new_messages fnm
WHERE fnm.message_text LIKE
'%' || l_search_text || '%') LOOP
dbms_output.put_line(rec_message.message_name || ':' ||
rec_message.message_text);
FOR rec IN (SELECT ds.name
,COUNT(1) cnt
FROM dba_source ds
WHERE upper(ds.text) LIKE
upper('%' || rec_message.message_name || '%')
GROUP BY ds.name) LOOP
dbms_output.put_line(rec.name || ':' || rec.cnt);
END LOOP;
END LOOP;
END;
3. 查看锁
--1.查看锁包
SELECT 'alter system kill session ' || '''' || ss.sid || ',' || ss.serial# ||
'''immediate;'
,ss.sid
,ss.serial#
,ddl.name
,ss.status
,ss.machine
,ss.port
,ss.module
,ss.username
,ss.client_identifier
,ss.logon_time
FROM dba_ddl_locks ddl
,gv$session ss
WHERE ddl.session_id = ss.sid
AND ddl.name LIKE upper('cux%')
--AND ss.STATUS = 'ACTIVE'
;
--2.查看锁表
SELECT 'alter system kill session ' || '''' || ss.sid || ',' || ss.serial# ||
'''immediate;'
,ss.sid
,ss.serial#
,do.object_name
,ss.status
,ss.machine
,ss.port
,ss.module
,ss.lockwait
,lo.locked_mode
,ss.username
,ss.client_identifier
,ss.logon_time
FROM gv$locked_object lo
,gv$session ss
,dba_objects do
WHERE lo.session_id = ss.sid
AND lo.object_id = do.object_id
AND do.object_name LIKE upper('cux%')
--AND ss.client_identifier LIKE '%21915%'
--AND ss.STATUS = 'ACTIVE'
;
--3.查看界面是否有人在用
SELECT 'alter system kill session ' || '''' || ss.sid || ',' || ss.serial# ||
'''immediate;'
,ss.sid
,ss.serial#
,ss.status
,ss.machine
,ss.port
,ss.module
,ss.lockwait
,ss.username
,ss.client_identifier
,ss.logon_time
FROM gv$session ss
WHERE 1 = 1
AND ss.module LIKE upper('%CUXWIPCOMP%');
--4.资源等待
WITH rslt_tbl AS
(SELECT decode(request
,0
,'Holder: '
,'Waiter: ') || sid sess
,id1
,id2
,lmode
,request
,TYPE
,a.sid
FROM gv$lock a
WHERE (id1, id2, TYPE) IN (SELECT id1
,id2
,TYPE
FROM gv$lock
WHERE request > 0
AND TYPE != 'HW'))
SELECT a.sess
,s.client_identifier
,a.*
,s.*
FROM rslt_tbl a
,v$session s
WHERE a.sid = s.sid(+)
ORDER BY a.id1
,a.request;
4. 查看并发请求定义
--查看并发请求定义
SELECT fcp.user_concurrent_program_name
,fcp.concurrent_program_name
,fef.execution_file_name
FROM fnd_concurrent_programs_vl fcp
,fnd_executables_form_v fef
WHERE 1 = 1
AND fcp.executable_id = fef.executable_id
AND fcp.user_concurrent_program_name LIKE 'CUX%'
AND upper(fef.execution_file_name) LIKE upper('%');
5.查看描述性弹性域设置
--说明性弹性域定义
SELECT fa.application_name "应用产品"
,fdfv.descriptive_flexfield_name "名称"
,fdfv.title "标题"
,fdfv.description "说明"
,fdfv.application_table_name "表名"
,fdfv.context_column_name "结构列"
,fdfv.form_context_prompt "上下文提示列"
,fdfv.concatenated_segs_view_name "DFV视图名称"
FROM fnd_descriptive_flexs_vl fdfv
,fnd_application_vl fa
WHERE fdfv.application_id = fa.application_id
AND fdfv.descriptive_flexfield_name = 'OE_LINE_ATTRIBUTES';
--说明性弹性段设置
SELECT fa.application_name "应用产品"
,fdfv.descriptive_flexfield_name "名称"
,fdfv.title "标题"
,fdfv.description "说明"
,fdfv.application_table_name "表名"
,fdfv.context_column_name "结构列"
,fdfv.form_context_prompt "上下文提示列"
,fdfv.concatenated_segs_view_name "DFV视图名称"
,fdfv.freeze_flex_definition_flag "冻结弹性域定义"
,fdfcv.descriptive_flex_context_code "上下文代码"
,fdfcv.descriptive_flex_context_name "上下文名称"
,fdfcv.description "上下文说明"
,fdfcuv.column_seq_num "序号"
,fdfcuv.end_user_column_name "段-名称"
,fdfcuv.form_left_prompt "段-窗口提示"
,fdfcuv.application_column_name "段-列"
,(SELECT ffvs.flex_value_set_name
FROM fnd_flex_value_sets ffvs
WHERE ffvs.flex_value_set_id = fdfcuv.flex_value_set_id) "段-值集"
,fdfcuv.enabled_flag "段-启用"
,fdfcuv.display_flag "段-显示"
FROM fnd_descriptive_flexs_vl fdfv
,fnd_application_vl fa
,fnd_descr_flex_contexts_vl fdfcv
,fnd_descr_flex_col_usage_vl fdfcuv
WHERE fdfv.application_id = fa.application_id
AND fdfv.application_id = fdfcv.application_id
AND fdfv.descriptive_flexfield_name = fdfcv.descriptive_flexfield_name
AND fdfcv.application_id = fdfcuv.application_id
AND fdfcv.descriptive_flexfield_name = fdfcuv.descriptive_flexfield_name
AND fdfcv.descriptive_flex_context_code =
fdfcuv.descriptive_flex_context_code
AND fdfv.descriptive_flexfield_name = 'OE_LINE_ATTRIBUTES'
ORDER BY fdfv.application_id
,fdfv.descriptive_flexfield_name
,decode(fdfcv.global_flag
,'Y'
,1
,2)
,fdfcv.descriptive_flex_context_code
,fdfcuv.column_seq_num;
6.查看键弹性域设置
--键弹性域定义
SELECT fa.application_name "应用产品"
,fif.id_flex_code "代码"
,fif.id_flex_name "标题"
,fif.description "说明"
,fif.application_table_name
,fif.unique_id_column_name "唯一标识列"
,fif.set_defining_column_name "结构列"
,fif.concatenated_segs_view_name "KFV视图名称"
FROM fnd_id_flexs fif
,fnd_application_vl fa
WHERE 1 = 1
AND fif.application_id = fa.application_id
AND fif.id_flex_code = 'GL#';
--键弹性域段设置
SELECT fav.application_name "应用产品"
,fif.id_flex_code "代码"
,fif.id_flex_name "标题"
,fif.description "说明"
,fif.application_table_name
,fif.unique_id_column_name "唯一标识列"
,fif.set_defining_column_name "结构列"
,fif.concatenated_segs_view_name "KFV视图名称"
,fifsv.id_flex_num "结构ID"
,fifsv.id_flex_structure_code "结构-代码"
,fifsv.id_flex_structure_name "结构-标题"
,fifsv.description "结构-说明"
,fifsm.segment_num "段-序号"
,fifsm.segment_name "段-名称"
,fifsm.form_left_prompt "段-窗口提示"
,fifsm.application_column_name "段-列"
,(SELECT ffvs.flex_value_set_name
FROM fnd_flex_value_sets ffvs
WHERE ffvs.flex_value_set_id = fifsm.flex_value_set_id) "段-值集"
,fifsm.enabled_flag "段-启用"
,fifsm.display_flag "段-显示"
FROM fnd_id_flexs fif
,fnd_application_vl fav
,fnd_id_flex_structures_vl fifsv
,fnd_id_flex_segments_vl fifsm
WHERE 1 = 1
AND fif.application_id = fav.application_id
AND fif.application_id = fifsv.application_id
AND fif.id_flex_code = fifsv.id_flex_code
AND fifsv.id_flex_num = fifsm.id_flex_num
AND fifsv.id_flex_code = fifsm.id_flex_code
AND fifsv.application_id = fifsm.application_id
AND fif.id_flex_code = 'GL#'
ORDER BY fifsv.application_id
,fifsv.id_flex_code
,fifsv.id_flex_structure_name
,fifsm.segment_num;
7.查询快码和值集，插入快码
--查询快码
SELECT flv.lookup_code
,flv.meaning
,flv.description
,flv.enabled_flag
FROM fnd_lookup_values_vl flv
WHERE flv.lookup_type = 'CUX_XXX';
--查询值集
SELECT ffv.flex_value
,ffv.description
,ffv.enabled_flag
,ffv.start_date_active
,ffv.end_date_active
,ffv.compiled_value_attributes "限定集" --不太用，具体看 fnd_flex_val_util.vtv_to_display_value(:value.flex_value_set_id, TRUE, :value.compiled_value_attributes, l_display_value);
FROM fnd_flex_value_sets ffvs
,fnd_flex_values_vl ffv
WHERE ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffvs.flex_value_set_name = 'CUX_XXX';
--插入快码
DECLARE
CURSOR cur_info IS
SELECT 'A' lookup_code
,'A1' meaning
,'A2' description
FROM dual
UNION ALL
SELECT 'B' lookup_code
,'B1' meaning
,'B2' description
FROM dual;
l_rowid VARCHAR2(80);
l_lookup_type VARCHAR2(80) := 'CUX_XXX_LOOKUP_TYPE';
l_security_group_id NUMBER;
l_view_application_id NUMBER;
l_count NUMBER;
BEGIN
-- API start
FOR rec_info IN cur_info LOOP
SELECT flv.security_group_id
,flv.view_application_id
INTO l_security_group_id
,l_view_application_id
FROM fnd_lookup_types flv
WHERE flv.lookup_type = l_lookup_type;
fnd_lookup_values_pkg.insert_row(x_rowid => l_rowid
,x_lookup_type => l_lookup_type
,x_security_group_id => l_security_group_id
,x_view_application_id => l_view_application_id
,x_lookup_code => rec_info.lookup_code
,x_tag => NULL
,x_attribute_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_enabled_flag => 'Y'
,x_start_date_active => trunc(SYSDATE)
,x_end_date_active => NULL
,x_territory_code => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL
,x_meaning => rec_info.meaning
,x_description => rec_info.description
,x_creation_date => SYSDATE
,x_created_by => fnd_global.user_id
,x_last_update_date => SYSDATE
,x_last_updated_by => fnd_global.user_id
,x_last_update_login => fnd_global.login_id);
SELECT COUNT(1)
INTO l_count
FROM fnd_lookup_values_vl flv
WHERE flv.lookup_type = l_lookup_type
AND flv.lookup_code = rec_info.lookup_code;
IF l_count = 0 THEN
RAISE no_data_found;
END IF;
END LOOP;
END;
8.版本视图新增字段后更新脚本
BEGIN
--对于版本视图，基表添加字段后，执行脚本，同步更新版本视图
ad_zd_table.upgrade(x_table_owner
,x_table_name);
END;
PS
更新版本视图存在bug，他本身会自动将触发器删除后重建，但是如果触发器内容字节长度4000，前4000字中包含中文，则会提示 ORA-06502 字符串缓冲区太小，这是因为更新脚本中，迁移表上触发器逻辑时，会调用 ad_zd_log.message，截取时没有考虑中文。
我们一般避免在表上写触发器，如果不得不写，注意下触发器内容，最好是单独封装逻辑，触发器再直接调用封装好的方法，减少触发器里的内容。
9.根据SID获取正在运行的SQL
DECLARE
CURSOR cur_sql(p_pre_sql_addr VARCHAR2) IS
SELECT t.sql_text
FROM v$sqltext_with_newlines t
WHERE t.address = p_pre_sql_addr
ORDER BY t.piece;
l_sql VARCHAR2(2400);
l_prev_sql_addr VARCHAR2(200);
BEGIN
BEGIN
SELECT v.prev_sql_addr
INTO l_prev_sql_addr
FROM v$session v
WHERE v.sid = &sid;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('get prev sql addr error,' || SQLCODE || ':' ||
SQLERRM);
END;
FOR rec IN cur_sql(l_prev_sql_addr) LOOP
l_sql := nvl(l_sql
,'') || rec.sql_text;
END LOOP;
dbms_output.put_line('sql为:');
dbms_output.put_line(l_sql);
END;
10.查找配置文件定义，以及分配的值
--查询配置文件定义
SELECT profile_option_name 配置文件名
,user_profile_option_name 用户配置文件名
,description 说明
,hierarchy_type 层次结构类型
,site_enabled_flag 地点可见
,site_update_allowed_flag 地点可更新
,app_enabled_flag 应用产品可见
,app_update_allowed_flag 应用产品可更新
,resp_enabled_flag 责任可见
,resp_update_allowed_flag 责任可更新
,server_enabled_flag 服务器可见
,server_update_allowed_flag 服务器可更新
,serverresp_enabled_flag 服务器职责可见
,serverresp_update_allowed_flag 服务器职责可更新
,org_enabled_flag 组织可见
,org_update_allowed_flag 组织可更新
,user_enabled_flag 用户可见
,user_update_allowed_flag 用户可更新
,start_date_active 有效起始日期
,end_date_active 有效截止日期
,user_visible_flag 用户访问可查看
,user_changeable_flag 用户访问可更新
,read_allowed_flag 可读
,write_allowed_flag 可写
,sql_validation sql验证
,profile_option_id 配置文件配置情况id
FROM apps.fnd_profile_options_vl
WHERE (user_profile_option_name = '&配置文件名');
--配置文件设置
SELECT op.profile_option_id
,tl.profile_option_name
,tl.user_profile_option_name
,lv.level_id
,lv.文件安全性
,va.level_value
,CASE
WHEN va.level_id = 10001 THEN
'地点'
WHEN va.level_id = 10002 THEN
(SELECT fav.application_name
FROM apps.fnd_application_vl fav
WHERE fav.application_id = va.level_value)
WHEN va.level_id = 10003 THEN
(SELECT /* $HEADER$ */
t.responsibility_name
FROM applsys.fnd_responsibility_tl t
,applsys.fnd_responsibility b
WHERE t.responsibility_id = va.level_value
AND t.responsibility_id = b.responsibility_id
AND b.application_id = t.application_id
AND nvl(b.end_date
,SYSDATE + 1) > SYSDATE
AND nvl(b.start_date
,SYSDATE - 1) < SYSDATE
AND t.language = 'ZHS'
--AND rownum = 1
)
WHEN va.level_id = 10004 THEN
(SELECT user_name
FROM applsys.fnd_user
WHERE user_name NOT IN
('*ANONYMOS*'
,'CONVERSION'
,'INITIAL SETUP'
,'FEEDER SYSTEM'
,'CONCURRENT MANAGER'
,'STANDALONE BATCH PROCESS')
AND user_id = va.level_value
AND nvl(end_date
,SYSDATE + 1) > SYSDATE
AND nvl(start_date
,SYSDATE - 1) < SYSDATE)
WHEN va.level_id = 10005 THEN
(SELECT node_name
FROM applsys.fnd_nodes
WHERE node_id = va.level_value)
WHEN va.level_id = 10006 THEN
(SELECT NAME
FROM apps.hr_operating_units
WHERE organization_id = va.level_value)
ELSE
''
END AS profile_level_value
,va.profile_option_value
FROM applsys.fnd_profile_options_tl tl
,applsys.fnd_profile_options op
,applsys.fnd_profile_option_values va
,(SELECT 10001 level_id
,'地点' 文件安全性
FROM dual
UNION
SELECT 10002 level_id
,'应用产品' 文件安全性
FROM dual
UNION
SELECT 10003 level_id
,'责任' 文件安全性
FROM dual
UNION
SELECT 10004 level_id
,'用户' 文件安全性
FROM dual
UNION
SELECT 10005 level_id
,'服务器' 文件安全性
FROM dual
UNION
SELECT 10006 level_id
,'组织' 文件安全性
FROM dual) lv
WHERE tl.language = 'ZHS'
AND tl.profile_option_name = op.profile_option_name
AND va.profile_option_id = op.profile_option_id
AND va.level_id = lv.level_id
AND tl.profile_option_name LIKE '&配置文件名CODE'
--AND tl.user_profile_option_name = '&配置文件名'
;
11.提交并发请求
DECLARE
CURSOR cur_info IS
SELECT t FROM dual;
l_request_id NUMBER;
l_user_name VARCHAR2(240) := 'SYSADMIN';
l_user_id NUMBER;
l_resp_name VARCHAR2(240) := 'CUX_客户化开发超级职责';
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
BEGIN
SELECT fu.user_id
INTO l_user_id
FROM fnd_user fu
WHERE fu.user_name = l_user_name;
SELECT fr.responsibility_id
,fr.application_id
INTO l_resp_id
,l_resp_appl_id
FROM fnd_responsibility_vl fr
WHERE fr.responsibility_name = l_resp_name;
fnd_global.apps_initialize(user_id => l_user_id
,resp_id => l_resp_id
,resp_appl_id => l_resp_appl_id);
FOR rec_info IN cur_info LOOP
l_request_id := fnd_request.submit_request(application => 'CUX' --应用产品
,program => 'CUXOMSHIPTS' --并发程序代码
,description => NULL
,start_time => NULL
,sub_request => FALSE
,argument1 => p_group_id);
IF l_request_id <> 0 THEN
dbms_output.put_line('l_request_id: ' || l_request_id);
ELSE
dbms_output.put_line('提交请求失败');
END IF;
END LOOP;
END;
12.动态关联模版，提交报表请求
以下是封装好的一个简单实例，传入并发请求简称，模板code，以及报表参数，提交请求：
/*==================================================
Procedure/Function Name :
print_report
Description:
动态关联模板，提交报表请求示例
Argument:
p_concurrent_program_name : 并发请求简称
p_template_code : 模板code
History:
1.00 20xx-xx-xx xxx Creation
==================================================*/
PROCEDURE print_report(p_concurrent_program_name IN VARCHAR2
,p_template_code IN VARCHAR2
,p_para1 IN VARCHAR2 --参数1
,p_para2 IN VARCHAR2 --参数2
,x_return_status OUT VARCHAR2
,x_msg_data OUT VARCHAR2) IS
/*
* CUX_PRINT_REPORT_M001 请求简称不能为空
* CUX_PRINT_REPORT_M002 模板code不能为空
* CUX_PRINT_REPORT_M003 获取 &TEMPLATE_CODE 模板信息发生错误: &ERROR_MESSAGE
* CUX_PRINT_REPORT_M004 关联模板发生错误
*/
l_default_language VARCHAR2(6);
l_default_territory VARCHAR2(6);
l_template_code VARCHAR2(200);
l_default_output_type VARCHAR2(10);
l_request_id NUMBER;
l_bl_result BOOLEAN;
BEGIN
--init
x_return_status := fnd_api.g_ret_sts_success;
--validate
IF p_concurrent_program_name IS NULL THEN
--请求简称不能为空
x_return_status := fnd_api.g_ret_sts_error;
fnd_message.set_name(application => 'CUX'
,NAME => 'CUX_PRINT_REPORT_M001');
x_msg_data := fnd_message.get;
RETURN;
END IF;
IF p_template_code IS NULL THEN
--模板code不能为空
x_return_status := fnd_api.g_ret_sts_error;
fnd_message.set_name(application => 'CUX'
,NAME => 'CUX_PRINT_REPORT_M002');
x_msg_data := fnd_message.get;
RETURN;
ELSE
--获取模板信息
BEGIN
SELECT xtv.template_code
,xtv.default_language
,xtv.default_territory
,xtv.default_output_type
INTO l_template_code
,l_default_language
,l_default_territory
,l_default_output_type
FROM xdo_templates_vl xtv
WHERE xtv.template_code = p_template_code
AND xtv.application_short_name = 'CUX';
EXCEPTION
WHEN OTHERS THEN
--获取模板信息发生错误
x_return_status := fnd_api.g_ret_sts_error;
fnd_message.set_name(application => 'CUX'
,NAME => 'CUX_PRINT_REPORT_M003');
fnd_message.set_token(token => 'TEMPLATE_CODE'
,VALUE => p_template_code);
fnd_message.set_token(token => 'ERROR_MESSAGE'
,VALUE => sql_error_message);
x_msg_data := fnd_message.get;
RETURN;
END;
END IF;
--动态设置设置模板的布局
l_bl_result := fnd_request.add_layout(template_appl_name => 'CUX'
,template_code => l_template_code
,template_language => l_default_language
,template_territory => l_default_territory
,output_format => l_default_output_type);
IF NOT l_bl_result THEN
--关联模板发生错误
x_return_status := fnd_api.g_ret_sts_error;
fnd_message.set_name(application => 'CUX'
,NAME => 'CUX_PRINT_REPORT_M004');
x_msg_data := fnd_message.get;
RETURN;
END IF;
l_request_id := fnd_request.submit_request(application => 'CUX'
,program => p_concurrent_program_name
,description => NULL
,start_time => NULL
,sub_request => FALSE
,argument1 => p_para1
,argument2 => p_para2);
IF l_request_id <= 0 THEN
--提交请求发送错误
x_return_status := fnd_api.g_ret_sts_error;
fnd_message.set_name(application => 'CUX'
,NAME => 'CUX_PRINT_REPORT_M005');
x_msg_data := fnd_message.get;
RETURN;
END IF;
EXCEPTION
WHEN OTHERS THEN
x_return_status := fnd_api.g_ret_sts_unexp_error;
x_msg_data := sql_error_message;
END print_report;
扫一扫
810
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
