1 PACKAGE BODY HR_SECURITY_UTILS AS
2 /* $Header: hrscutl.pkb 115.1 99/07/17 16:59:38 porting 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 -- Bug#885806
39 -- dbms_output.put_line(p_message) ;
40 hr_utility.trace(p_message) ;
41 end if;
42
43 end output_line ;
44
45 --
46 -- PUBLIC FUNCTIONS AND PROCEDURES
47 --
48
49 PROCEDURE check_profile_options IS
50 --
51 type bg_sp is record (
52 business_group_id per_business_groups.business_group_id%type,
53 bg_name per_business_groups.name%type,
54 security_profile_id per_security_profiles.security_profile_id%type,
55 security_profile_name per_security_profiles.security_profile_name%type ) ;
56
57 l_site_value varchar2(80) ;
58 l_appl_value varchar2(80) ;
59 l_resp_value varchar2(80) ;
60 l_defined boolean := false ;
61 l_appl_defined boolean := false ;
62 l_resp_defined boolean := false ;
63
64 --
65 -- These records contain the bg and sp values derived from the given
66 -- profile option
67 --
68 bg_profile bg_sp ;
69 sp_profile bg_sp ;
70
71
72 --
73 -- Returns details of the business group and its associated view
74 -- all security profile option
75 --
76 procedure get_details ( p_sp_id in varchar2 default null,
77 p_bg_id in varchar2 default null ,
78 p_output out bg_sp ) is
79 cursor c1 is
80 select bg.business_group_id,
81 bg.name,
82 sp.security_profile_id,
83 sp.security_profile_name
84 from per_security_profiles sp,
85 per_business_groups bg
86 where sp.security_profile_id = nvl(p_sp_id,sp.security_profile_id)
87 and sp.business_group_id = nvl(p_bg_id,sp.business_group_id)
88 and sp.business_group_id = bg.business_group_id
89 and sp.view_all_flag = decode(bg.business_group_id,null,
90 sp.view_all_flag,'Y') ;
91
92 begin
93 open c1 ;
94 fetch c1 into p_output ;
95 close c1 ;
96 end ;
97
98
99 --
100 -- Gets the Responsibility Level setting of the given profile option
101 --
102 procedure get_resp_level ( option_name in varchar2,
103 appl_id in number,
104 resp_id in number,
105 option_value out varchar2 ,
106 defined out boolean ) is
107 cursor c1 is
108 select V.PROFILE_OPTION_VALUE
109 from FND_PROFILE_OPTIONS O, FND_PROFILE_OPTION_VALUES V
110 where O.PROFILE_OPTION_NAME = option_name
111 and V.LEVEL_ID = 10003 and V.LEVEL_VALUE = to_char(resp_id)
112 and V.LEVEL_VALUE_APPLICATION_ID = to_char(appl_id)
113 and O.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID
114 and O.APPLICATION_ID = V.APPLICATION_ID ;
115
116 begin
117
118 open c1 ;
119 fetch c1 into option_value ;
120 defined := c1%found ;
121 close c1 ;
122
123 end get_resp_level ;
124
125 --
126 -- Gets the Application Level setting of the given profile option
127 -- Note that the GET_ functions in the FND_PROFILE package does
128 -- not detect whether the profile option is set already
129 --
130 procedure get_app_level ( option_name in varchar2,
131 appl_id in number,
132 option_value out varchar2 ,
133 defined out boolean ) is
134 cursor c1 is
135 select V.PROFILE_OPTION_VALUE
136 from FND_PROFILE_OPTIONS O, FND_PROFILE_OPTION_VALUES V
137 where O.PROFILE_OPTION_NAME = option_name
138 and V.LEVEL_ID = 10002 and V.LEVEL_VALUE = to_char(appl_id)
139 and O.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID
140 and O.APPLICATION_ID = V.APPLICATION_ID ;
141
142 begin
143
144 open c1 ;
145 fetch c1 into option_value ;
146 defined := c1%found ;
147 close c1 ;
148
149 end get_app_level ;
150
151 begin
152
153 dbms_output.enable(1000000) ;
154
155 --
156 -- Get site level setting of business group. If it is set and the
157 -- security profile is not then set the security profile to be the
158 -- view all profile associated with that business group.
159 --
160
161 l_site_value := fnd_profile.value_specific(name =>'PER_BUSINESS_GROUP_ID');
162
163 if l_site_value is not null then
164
165 get_details ( p_bg_id => l_site_value , p_output => bg_profile ) ;
166
167 l_site_value := fnd_profile.value_specific(
168 name=>'PER_SECURITY_PROFILE_ID');
169 output_line('Site Level :business group profile is '||
170 bg_profile.bg_name) ;
171
172 if ( l_site_value is null or l_site_value = 0 ) then
173
174 output_line('Setting site level security profile',true) ;
175 if NOT fnd_profile.save ( 'PER_SECURITY_PROFILE_ID',
176 bg_profile.security_profile_id,
177 'SITE' )
178 then
179 raise_error('ERROR - Setting Security Profile at SITE level') ;
180 end if;
181
182 end if;
183
184 end if;
185
186 --
187 -- Loop through each application and responsibility.
188 --
189 -- 1. If the Business Group Profile option was set at Application level
190 -- then clear it setting this value for each linked responsibility
191 -- which does not already have a value set.
192 --
193 -- 2. If the Security Profile profile option is not set for a given
194 -- responsibility then set it to be the View All security profile
195 -- for the given business group
196 --
197 for rec in ( select application_id,
198 application_short_name,
199 application_name
200 from fnd_application_vl
201 order by application_name )
202 loop
203
204 output_line(rec.application_name||' ('||
205 rec.application_short_name||') ... ',true );
206 get_app_level (
207 option_name => 'PER_BUSINESS_GROUP_ID',
208 appl_id => rec.application_id,
209 option_value => l_appl_value,
210 defined => l_appl_defined ) ;
211
212 if ( l_appl_defined = true ) then
213
214 get_details(p_bg_id => l_appl_value , p_output => bg_profile ) ;
215 output_line(' Appl. '||rec.application_short_name ||
216 ' BG Value '||l_appl_value||'. '||
217 bg_profile.bg_name ) ;
218
219 end if;
220
221 for resp in ( select responsibility_id,
222 responsibility_name
223 from fnd_responsibility_vl
224 where application_id = rec.application_id
225 order by responsibility_name )
226 loop
227
228 get_resp_level (
229 option_name => 'PER_BUSINESS_GROUP_ID',
230 appl_id => rec.application_id,
231 resp_id => resp.responsibility_id,
232 option_value => l_resp_value,
233 defined => l_resp_defined ) ;
234
235 if ( l_resp_defined = true ) then
236
237 get_details(p_bg_id => l_resp_value , p_output => bg_profile);
238
239 output_line('resp. '||rpad(resp.responsibility_name,30) ||
240 ' BG Profile value '||l_resp_value||'.'||
241 bg_profile.bg_name ) ;
242
243 elsif ( l_appl_defined = true ) then
244
245 --
246 -- Set the business group profile to the application setting
247 --
248
249 output_line( 'Setting Business Group profile for resp '||
250 rpad(resp.responsibility_name,30),true) ;
251 if NOT fnd_profile.save ( 'PER_BUSINESS_GROUP_ID',
252 l_appl_value,
253 'RESP',
254 resp.responsibility_id,
255 rec.application_id )
256 then
257 raise_error('ERROR - Setting Business Group profile option'||
258 'at RESP level') ;
259 end if;
260
261 end if;
262
263 --
264 -- If the business group profile option was defined for this
265 -- responsibility then check that the security profile option
266 -- has been set.
267 --
268 if ( l_resp_defined or l_appl_defined ) then
269 get_resp_level (
270 option_name => 'PER_SECURITY_PROFILE_ID',
271 appl_id => rec.application_id,
272 resp_id => resp.responsibility_id,
273 option_value => l_resp_value,
274 defined => l_defined ) ;
275
276 if ( l_defined = true ) then
277
278 get_details ( p_sp_id => l_resp_value ,
279 p_output => sp_profile ) ;
280 output_line('Resp '||rpad(resp.responsibility_name,30)||
281 ' SP Value '||l_resp_value||'. '||
282 sp_profile.security_profile_name );
283
284 else
285
286 output_line( 'Setting Security Profile for Resp '||
287 rpad(resp.responsibility_name,30),true) ;
288 if NOT fnd_profile.save ( 'PER_SECURITY_PROFILE_ID',
289 bg_profile.security_profile_id,
290 'RESP',
291 resp.responsibility_id,
292 rec.application_id )
293 then
294 raise_error('ERROR - Setting Security Profile option '
295 ||'at RESP level') ;
296 end if;
297
298 end if;
299 end if;
300
301 end loop ;
302
303 --
304 -- Remove application level business group profile option setting
305 --
306
307 if ( l_appl_defined = true ) then
308
309 output_line('Clearing Application level Business Group profile '
310 ||'option value',true);
311 if NOT fnd_profile.save ('PER_BUSINESS_GROUP_ID',
312 NULL,
313 'APPL',
314 rec.application_id )
315 then
319
316 raise_error('ERROR - Clearing Business Group profile option at '
317 ||'APPL level') ;
318 end if;
320 end if;
321
322 output_line('',true);
323
324 end loop ;
325
326 end check_profile_options ;
327
328
329 function is_custom_schema return boolean is
330 begin
331 return custom_schema ;
332 end ;
333
334 procedure set_custom_schema is
335 begin
336 custom_schema := true ;
337 end ;
338
339 end HR_SECURITY_UTILS;
[Home] [Help]