1 PACKAGE BODY HR_SEC3_UPDATE AS
2 /* $Header: hrsec3.pkb 115.3 2002/12/05 12:49:17 apholt ship $ */
3
4 --
5 -- PRIVATE FUNCTIONS AND PROCEDURES
6 --
7
8 --
9 debug_mode_on boolean := FALSE ;
10
11 --
12 custom_schema boolean := FALSE ;
13 -- Raise an internal error. Not translated.
14 --
15 procedure raise_error( p_message in varchar2 ) is
16 begin
17 raise_application_error(-20001, p_message) ;
18 end raise_error;
19
20 procedure debug_on is
21 begin
22 debug_mode_on := TRUE ;
23 end debug_on ;
24
25 procedure debug_off is
26 begin
27 debug_mode_on := FALSE;
28 end debug_off ;
29
30 --
31 -- Outputs a line if debug mode on or if force output
32 --
33 procedure output_line (p_message in varchar2,
34 p_always_output in boolean default false ) is
35 begin
36
37 -- if ( p_always_output or debug_mode_on ) then
38 hr_utility.trace(p_message) ;
39 -- end if;
40
41 end output_line ;
42
43 --
44 -- PUBLIC FUNCTIONS AND PROCEDURES
45 --
46
47 PROCEDURE set_profile_options IS
48 --
49 type bg_sp is record (
50 business_group_id per_business_groups.business_group_id%type,
51 bg_name per_business_groups.name%type,
52 security_profile_id per_security_profiles.security_profile_id%type,
53 security_profile_name per_security_profiles.security_profile_name%type ) ;
54
55 l_site_value varchar2(80) ;
56 l_appl_value varchar2(80) ;
57 l_resp_value varchar2(80) ;
58 l_defined boolean := false ;
59 l_appl_defined boolean := false ;
60 l_resp_defined boolean := false ;
61 l_spa_defined boolean := false ;
62 l_sp_id number(15);
63 --
64 -- These records contain the bg and sp values derived from the given
65 -- profile option
66 --
67 bg_profile bg_sp ;
68 sp_profile bg_sp ;
69
70
71 --
72 -- Returns details of the business group and its associated view
73 -- all security profile option
74 --
75 procedure get_details ( p_sp_id in varchar2 default null,
76 p_bg_id in varchar2 default null ,
77 p_output out nocopy bg_sp ) is
78 cursor c1 is
79 select bg.business_group_id,
80 bg.name,
81 sp.security_profile_id,
82 sp.security_profile_name
83 from per_security_profiles sp,
84 per_business_groups bg
85 where sp.security_profile_id = nvl(p_sp_id,sp.security_profile_id)
86 and sp.business_group_id = nvl(p_bg_id,sp.business_group_id)
87 and sp.business_group_id = bg.business_group_id
88 and sp.view_all_flag = decode(bg.business_group_id,null,
89 sp.view_all_flag,'Y') ;
90
91 begin
92 open c1 ;
93 fetch c1 into p_output ;
94 close c1 ;
95 end ;
96 --
97 --
98 procedure get_sec_profile_assignment ( p_resp_id in varchar2 default null,
99 p_sp_id out nocopy number,
100 defined out nocopy boolean) is
101 cursor c1 is
102 select distinct (responsibility_id),
103 security_profile_id
104 from per_sec_profile_assignments
105 where responsibility_id = p_resp_id;
106 l_resp1 number;
107
108 begin
109 open c1 ;
110 fetch c1 into l_resp1, p_sp_id ;
111 defined := c1%found;
112 close c1 ;
113 end ;
114 --
115 --
116 -- Gets the Responsibility Level setting of the given profile option
117 --
118 procedure get_resp_level ( option_name in varchar2,
119 appl_id in number,
120 resp_id in number,
121 option_value out nocopy varchar2 ,
122 defined out nocopy boolean ) is
123 cursor c1 is
124 select V.PROFILE_OPTION_VALUE
125 from FND_PROFILE_OPTIONS O, FND_PROFILE_OPTION_VALUES V
126 where O.PROFILE_OPTION_NAME = option_name
127 and V.LEVEL_ID = 10003 and V.LEVEL_VALUE = to_char(resp_id)
128 and V.LEVEL_VALUE_APPLICATION_ID = to_char(appl_id)
129 and O.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID
130 and O.APPLICATION_ID = V.APPLICATION_ID ;
131
132 begin
133
134 open c1 ;
135 fetch c1 into option_value ;
136 defined := c1%found ;
137 close c1 ;
138
139 end get_resp_level ;
140
141 --
142 --
143 begin
144
145 hr_utility.set_trace_options('TRACE_DEST:DBMS_OUTPUT');
146 hr_utility.trace_on;
147 --
148 -- Get site level setting of business group. If it is set and the
149 -- security profile is not then set the security profile to be the
150 -- view all profile associated with that business group.
151 --
152 --
153 -- Loop through each responsibility.
154 --
155 -- If the Business Group Profile option was set at Responsibility
156 -- then do the following:
157 -- 1. Does a restricted security profile assignment exists
158 -- for the responsibility.
159 -- If yes then use this SP to set the SP profile option for
160 -- the responsibility
161 -- else
162 -- use the View ALL SP for and set the SP profile option.
163 -- end if;
164 --
165 --
166 for resp in ( select responsibility_id,
167 responsibility_name,
168 application_id
169 from fnd_responsibility_vl
170 order by responsibility_name )
171 loop
172
173 get_resp_level (
174 option_name => 'PER_BUSINESS_GROUP_ID',
175 appl_id => resp.application_id,
176 resp_id => resp.responsibility_id,
177 option_value => l_resp_value,
178 defined => l_resp_defined ) ;
179
180 if ( l_resp_defined = true ) then
181
182 get_details(p_bg_id => l_resp_value , p_output => bg_profile);
183
184 output_line('resp. '||rpad(resp.responsibility_name,30) ||
185 ' BG Profile value '||l_resp_value||'.'||
186 bg_profile.bg_name ) ;
187 --
188 -- Get restricted security profile assignments if any.
189 --
190 get_sec_profile_assignment(p_resp_id => resp.responsibility_id,
191 p_sp_id => l_sp_id,
192 defined => l_spa_defined);
193 --
194 -- If a restricted SP to set at this at responsibility else
195 -- use the View ALL for the BG.
196 --
197 if NOT l_spa_defined then
198 l_sp_id := bg_profile.security_profile_id;
199 end if;
200 --
201 -- Set the security profile to the responsibility setting
202 --
203 output_line( 'Setting Security Profile for Resp '||
204 rpad(resp.responsibility_name,30),true) ;
205 --
206 if NOT fnd_profile.save ( 'PER_SECURITY_PROFILE_ID',
207 l_sp_id,
208 'RESP',
209 resp.responsibility_id,
210 resp.application_id )
211 then
212 raise_error('ERROR - Setting Security Profile option '
213 ||'at RESP level') ;
214 end if;
215 --
216 /* -- Remove the business group responsibility setting.
217 --
218 output_line( 'Removing Business Group Profile for Resp '||
219 rpad(resp.responsibility_name,30),true) ;
220 --
221 --
222 if NOT fnd_profile.save ( 'PER_BUSINESS_GROUP_ID',
223 null,
224 'RESP',
225 resp.responsibility_id,
226 resp.application_id )
227 then
228 raise_error('ERROR - Setting Security Profile option '
229 ||'at RESP level') ;
230 end if;
231 */
232 end if;
233
234 end loop ;
235
236 end set_profile_options ;
237 --
238 end HR_SEC3_UPDATE;
[Home] [Help]