在ebs的有关采购的报表中,经常需要进行数据安全控制,实现的方法有多种。
不同的控制需求有不同的方式,如下文是通过职位层次结构中定义的安全性层次结构进行控制的方式。
采购超级用户-->设置-->人员-->职位层次结构,可查看已定义层次结构。
通过表可以看见所有的层次结构,如下:
与层次结构相关的表、视图主要有:
per_all_people_f 职工姓名表(基本信息表)
per_all_positions 职位表
per_all_assignments_f
per_pos_structure_elements
hr_all_positions_f
在报表的主SQL中,加上如下的控制代码便可实现目的:
*************************************************************************************************************************************************************************
--按照‘***_安全性层次结构’控制权限,条件
AND pha.agent_id IN (
SELECT DISTINCT paa.person_id --查看下层采购员数据
FROM per_all_assignments_f paa,
(SELECT *
FROM fnd_user
WHERE SYSDATE BETWEEN start_date AND NVL(end_date, SYSDATE)) FU
WHERE 1=1
AND SYSDATE BETWEEN paa.effective_start_date AND paa.effective_end_date
AND paa.person_id = fu.employee_id(+)
AND paa.position_id IN (SELECT pp1.subordinate_position_id
FROM (SELECT ppse.subordinate_position_id,
ppse.parent_position_id
FROM per_pos_structure_elements ppse,
per_position_structures pps
WHERE 1=1
AND ppse.pos_structure_version_id = pps.position_structure_id
AND pps.name = '*****_安全性层次结构') PP1
START WITH pp1.parent_position_id = get_user_position(fnd_global.user_id) --获取用户职位信息
CONNECT BY pp1.parent_position_id = PRIOR
pp1.subordinate_position_id)
UNION
SELECT employee_id --可查看同组采购组数据
FROM fnd_user fu
WHERE fu.user_id = fnd_global.user_id)
*******************************************************************************************************************************************************************
上述代码中的et_user_position(fnd_global.user_id) --获取用户职位信息,如下:
/******************************************************************************************************************************************************************
* get_user_position(p_user_id IN NUMBER)
* created by 二楼 on 2015-11-16
* 参数:
* p_user_id 用户 user_id
* 功能:
* 获取当前用户在‘***_安全性层次结构’下的职位信息,用于报表权限控制
* 使用参考:
* 报表:******************
*
*************************************************************************/
FUNCTION get_user_position(p_user_id IN NUMBER) RETURN NUMBER IS
l_position_id NUMBER;
g_structure_name CONSTANT VARCHAR2(100) := '****_安全性层次结构';
l_position_name VARCHAR2(100);
BEGIN
BEGIN
--上层(顶层)
SELECT DISTINCT ppse.parent_position_id, hap.name
INTO l_position_id, l_position_name
FROM per_pos_structure_elements ppse,
per_position_structures pps,
hr_all_positions_f hap
WHERE 1=1
AND ppse.pos_structure_version_id = pps.position_structure_id
AND ppse.business_group_id = pps.business_group_id
AND pps.name = g_structure_name
AND hap.position_id = ppse.parent_position_id --上层
AND EXISTS(SELECT 1
FROM per_all_assignments_f paaf, fnd_user fu
WHERE 1=1
AND paaf.person_id = fu.employee_id
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND fu.user_id = p_user_id
AND ppse.parent_position_id = paaf.position_id);
EXCEPTION
WHEN OTHERS THEN
-- 是否下层
BEGIN
SELECT ppse.subordinate_position_id, hap.name
INTO l_position_id, l_position_name
FROM per_pos_structure_elements ppse,
per_position_structures pps,
hr_all_positions_f hap
WHERE 1=1
AND ppse.pos_structure_version_id = pps.position_structure_id
AND ppse.business_group_id = pps.business_group_id
AND pps.name = g_structure_name
AND hap.position_id = ppse.subordinate_position_id --下层
AND EXISTS(SELECT 1
FROM per_all_assignments_f paaf,
fnd_user fu
WHERE 1=1
AND paaf.person_id = fu.employee_id
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND fu.user_id = p_user_id
AND ppse.subordinate_position_id = paaf.position_id);
EXCEPTION
WHEN OTHERS THEN
l_position_id := NULL;
l_position_name := NULL;
END;
END;
RETURN l_position_id;
END get_user_position;
******************************************************************************************************************************************************************
以上是实现EBS报表中使用安全性层次结构控制的方式,贴上供路人参考。