Thought I’d share some code I put together here to check groups in BO.
Client needed a secure object so that a value would be displayed based upon a level of users.
What I did was build an Oracle function that would call the BO repository and return an indicator whether the user belong to that group.
Here’s the code:
CREATE OR REPLACE FUNCTION FIND_CNO
(in_bouser varchar2 <----------------------- @Variable('BOUSER') goes here
)
RETURN number
IS
-- PL/SQL Specification
l_return_value number := 0;
-- PL/SQL Block
begin
select count(*)
into l_return_value
from borepdw.obj_m_actor a1,
borepdw.obj_m_actor a2,
borepdw.obj_m_actorlink l1,
borepdw.obj_m_actorlink l2
where a1.m_actor_n_id=l2.m_actl_n_actorid
and a1.m_actor_n_type = '16'
and a2.m_actor_n_type='1'
and l1.m_actl_n_id = l2.m_actl_n_fatlinkid
and a2.m_actor_n_id = l1.m_actl_n_actorid
and a2.m_actor_c_name = 'CNO' <--- BO Group
and a1.m_actor_c_name = in_bouser; <--- BO User
dbms_output.put_line('Total count = ' || to_char(l_return_value) );
RETURN l_return_value;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
end find_cno;
/
The object built was this:
DECODE(find_cno(@Variable('BOUSER')), 0,@Select(EVENT FACTS\SSN - Last 6 digits ONLY),@Select(EVENT FACTS\SSN))
This has been built in PL/SQL, but could be translated into other SQL code.
This trick eliminates the problem where query don’t work with object level security.
dmorgan28202 (BOB member since 2003-04-01)