ERP开发中惯用的一些工具代码
ERP开发中常用的一些工具代码
--查询功能所属的菜单
SELECT fffv.function_name, fm.menu_name, fr.responsibility_key
FROM fnd_form_functions_vl fffv, fnd_menu_entries_vl fmev, fnd_menus fm, fnd_responsibility fr
WHERE fffv.function_id = fmev.function_id
AND fmev.menu_id = fm.menu_id
AND fmev.menu_id = fr.menu_id(+)
AND fffv.function_name LIKE '&function_name'
;
--查找运行请求时间，参数等（可以是某用户的，某个报表）
SELECT c.user_name
,papf.full_name
,b.user_concurrent_program_name
,a.request_date
,a.argument_text
,(a.actual_completion_date - a.actual_start_date) * 24 * 60 minutes
,a.actual_start_date
,a.actual_completion_date
,a.request_id
,a.outfile_name
FROM fnd_concurrent_requests a, fnd_concurrent_programs_vl b, fnd_user c, per_all_people_f papf
WHERE a.concurrent_program_id = b.concurrent_program_id
AND a.requested_by = c.user_id
AND c.user_name = papf.employee_number(+)
AND a.actual_completion_date IS NOT NULL
AND b.user_concurrent_program_name = '你的程序名称' --- like '%XXX%'
AND c.user_name = ' 你要找的用户的'
AND a.request_date <= to_date('2005-03-01 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
AND a.request_date >= to_date('2005-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND a.request_id > 2254198 ---为了提高速度，选一个合适时间点的ID
;
/*根据描述性弹性域的标题查找描述性弹性域表和列*/ --
SELECT fnd_dfv.title
,fnd_dfv.descriptive_flexfield_name
,fnd_dfv.application_table_name
,fnd_dfu.application_column_name
,fnd_dfu.form_left_prompt
,fnd_dfu.form_above_prompt
FROM fnd_descriptive_flexs_vl fnd_dfv, fnd_descr_flex_col_usage_vl fnd_dfu
WHERE 1 = 1
AND fnd_dfv.title LIKE '%&title%' --如：物料'%附加题头信息%'
AND fnd_dfu.descriptive_flexfield_name = fnd_dfv.descriptive_flexfield_name
;
/* 根据窗口名称查找关键字弹性域用到的表，列等信息*/ --
SELECT c.id_flex_name
,a.id_flex_structure_name
,b.form_left_prompt
,c.application_table_name
,b.application_column_name
,b.flex_value_set_id
FROM fnd_id_flex_structures_vl a, fnd_id_flex_segments_vl b, fnd_id_flexs c
WHERE a.id_flex_structure_name LIKE '%&帐户别名%' --用你自己要查的代替，就是Form窗口的标题
AND a.application_id = b.application_id
AND a.id_flex_code = b.id_flex_code
AND a.id_flex_num = b.id_flex_num
AND a.application_id = c.application_id
AND a.id_flex_code = c.id_flex_code
;
--根据上面FLEX_VALUE_SET_ID查弹性域的数据
SELECT * FROM fnd_flex_values_vl t WHERE t.flex_value_set_id = 1005982; -- FLEX_VALUE_SET_ID
--具体某一数据
SELECT *
FROM fnd_flex_values_vl t
WHERE t.flex_value_set_id = 1005982
AND t.flex_value = '720611'
;
--查找在菜单里提交的报表所在职责
SELECT a.responsibility_name, b.prompt, f.user_concurrent_program_name
FROM fnd_responsibility_vl a
,fnd_menu_entries_vl b
,fnd_form_functions_vl c
,fnd_request_groups d
,fnd_request_group_units e
,fnd_concurrent_programs_vl f
WHERE a.menu_id = b.menu_id
AND b.function_id = c.function_id
AND c.parameters LIKE '%' || d.request_group_code || '%'
AND d.application_id = e.application_id
AND d.request_group_id = e.request_group_id
AND e.unit_application_id = f.application_id
AND e.request_unit_id = f.concurrent_program_id
AND f.user_concurrent_program_name LIKE '%杂项出入库报表%'
;
--查找在标准请求组里提交的报表所在的职责
SELECT a.responsibility_name, b.user_concurrent_program_name, b.concurrent_program_name
FROM fnd_responsibility_vl a, fnd_concurrent_programs_vl b, fnd_request_group_units c
WHERE a.application_id = c.application_id
AND a.request_group_id = c.request_group_id
AND b.application_id = c.unit_application_id
AND b.concurrent_program_id = c.request_unit_id
AND b.user_concurrent_program_name LIKE '%杂项出入库报表%'
;
--根据报表文件名称关键字查找报表的执行文件名称等信息
SELECT a.user_concurrent_program_name
,a.concurrent_program_name
,a.output_file_type
,b.execution_file_name
,b.executable_name
,fnd_l.meaning
,b.user_executable_name
,b.description
FROM fnd_concurrent_programs_vl a, fnd_executables_vl b, fnd_lookups fnd_l
WHERE a.application_id = b.application_id
AND a.executable_id = b.executable_id
AND b.execution_method_code = fnd_l.lookup_code(+)
AND fnd_l.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND a.user_concurrent_program_name LIKE 'C%杂项出入库报表%'
--查询功能所属的菜单
SELECT fffv.function_name, fm.menu_name, fr.responsibility_key
FROM fnd_form_functions_vl fffv, fnd_menu_entries_vl fmev, fnd_menus fm, fnd_responsibility fr
WHERE fffv.function_id = fmev.function_id
AND fmev.menu_id = fm.menu_id
AND fmev.menu_id = fr.menu_id(+)
AND fffv.function_name LIKE '&function_name'
;
--查找运行请求时间，参数等（可以是某用户的，某个报表）
SELECT c.user_name
,papf.full_name
,b.user_concurrent_program_name
,a.request_date
,a.argument_text
,(a.actual_completion_date - a.actual_start_date) * 24 * 60 minutes
,a.actual_start_date
,a.actual_completion_date
,a.request_id
,a.outfile_name
FROM fnd_concurrent_requests a, fnd_concurrent_programs_vl b, fnd_user c, per_all_people_f papf
WHERE a.concurrent_program_id = b.concurrent_program_id
AND a.requested_by = c.user_id
AND c.user_name = papf.employee_number(+)
AND a.actual_completion_date IS NOT NULL
AND b.user_concurrent_program_name = '你的程序名称' --- like '%XXX%'
AND c.user_name = ' 你要找的用户的'
AND a.request_date <= to_date('2005-03-01 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
AND a.request_date >= to_date('2005-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND a.request_id > 2254198 ---为了提高速度，选一个合适时间点的ID
;
/*根据描述性弹性域的标题查找描述性弹性域表和列*/ --
SELECT fnd_dfv.title
,fnd_dfv.descriptive_flexfield_name
,fnd_dfv.application_table_name
,fnd_dfu.application_column_name
,fnd_dfu.form_left_prompt
,fnd_dfu.form_above_prompt
FROM fnd_descriptive_flexs_vl fnd_dfv, fnd_descr_flex_col_usage_vl fnd_dfu
WHERE 1 = 1
AND fnd_dfv.title LIKE '%&title%' --如：物料'%附加题头信息%'
AND fnd_dfu.descriptive_flexfield_name = fnd_dfv.descriptive_flexfield_name
;
/* 根据窗口名称查找关键字弹性域用到的表，列等信息*/ --
SELECT c.id_flex_name
,a.id_flex_structure_name
,b.form_left_prompt
,c.application_table_name
,b.application_column_name
,b.flex_value_set_id
FROM fnd_id_flex_structures_vl a, fnd_id_flex_segments_vl b, fnd_id_flexs c
WHERE a.id_flex_structure_name LIKE '%&帐户别名%' --用你自己要查的代替，就是Form窗口的标题
AND a.application_id = b.application_id
AND a.id_flex_code = b.id_flex_code
AND a.id_flex_num = b.id_flex_num
AND a.application_id = c.application_id
AND a.id_flex_code = c.id_flex_code
;
--根据上面FLEX_VALUE_SET_ID查弹性域的数据
SELECT * FROM fnd_flex_values_vl t WHERE t.flex_value_set_id = 1005982; -- FLEX_VALUE_SET_ID
--具体某一数据
SELECT *
FROM fnd_flex_values_vl t
WHERE t.flex_value_set_id = 1005982
AND t.flex_value = '720611'
;
--查找在菜单里提交的报表所在职责
SELECT a.responsibility_name, b.prompt, f.user_concurrent_program_name
FROM fnd_responsibility_vl a
,fnd_menu_entries_vl b
,fnd_form_functions_vl c
,fnd_request_groups d
,fnd_request_group_units e
,fnd_concurrent_programs_vl f
WHERE a.menu_id = b.menu_id
AND b.function_id = c.function_id
AND c.parameters LIKE '%' || d.request_group_code || '%'
AND d.application_id = e.application_id
AND d.request_group_id = e.request_group_id
AND e.unit_application_id = f.application_id
AND e.request_unit_id = f.concurrent_program_id
AND f.user_concurrent_program_name LIKE '%杂项出入库报表%'
;
--查找在标准请求组里提交的报表所在的职责
SELECT a.responsibility_name, b.user_concurrent_program_name, b.concurrent_program_name
FROM fnd_responsibility_vl a, fnd_concurrent_programs_vl b, fnd_request_group_units c
WHERE a.application_id = c.application_id
AND a.request_group_id = c.request_group_id
AND b.application_id = c.unit_application_id
AND b.concurrent_program_id = c.request_unit_id
AND b.user_concurrent_program_name LIKE '%杂项出入库报表%'
;
--根据报表文件名称关键字查找报表的执行文件名称等信息
SELECT a.user_concurrent_program_name
,a.concurrent_program_name
,a.output_file_type
,b.execution_file_name
,b.executable_name
,fnd_l.meaning
,b.user_executable_name
,b.description
FROM fnd_concurrent_programs_vl a, fnd_executables_vl b, fnd_lookups fnd_l
WHERE a.application_id = b.application_id
AND a.executable_id = b.executable_id
AND b.execution_method_code = fnd_l.lookup_code(+)
AND fnd_l.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND a.user_concurrent_program_name LIKE 'C%杂项出入库报表%'