[Home] [Help]
PACKAGE BODY: APPS.IGI_SLS_SECURITY_PKG
Source
1 PACKAGE BODY igi_sls_security_pkg AS
2 -- $Header: igislsdb.pls 120.12.12010000.2 2008/08/04 13:07:01 sasukuma ship $
3
4 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 l_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
6 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
8 l_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 l_error_level NUMBER := FND_LOG.LEVEL_ERROR;
10 l_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
11 l_path VARCHAR2(50) := 'IGI.PLSQL.igislsdb.igi_sls_security_pkg.';
12
13
14 /*-----------------------------------------------------------------
15 This procedure writes to the error log.
16 -----------------------------------------------------------------*/
17 /* PROCEDURE Write_To_Log ( p_message IN VARCHAR2) IS
18 BEGIN
19 FND_FILE.put_line( FND_FILE.log, p_message );
20 END Write_To_Log;*/
21
22
23 /*-----------------------------------------------------------------
24 This procedure writes to the error log.
25 -----------------------------------------------------------------*/
26 PROCEDURE Write_To_Log (p_level IN NUMBER, p_path IN VARCHAR2, p_mesg IN VARCHAR2) IS
27 BEGIN
28 IF (p_level >= l_debug_level ) THEN
29 FND_LOG.STRING (p_level , l_path || p_path , p_mesg );
30 END IF;
31 END Write_To_Log;
32
33
34 /*------------------------------------------------------------------
35 This procedure returns the SCHEMA name, which in most cases will be
36 APPS. Created for bug 1933950 by Bidisha on 29 Aug 2001
37 ------------------------------------------------------------------*/
38 PROCEDURE get_schema_name (p_schema_name IN OUT NOCOPY VARCHAR2,
39 errbuf IN OUT NOCOPY VARCHAR2,
40 retcode IN OUT NOCOPY NUMBER)
41 IS
42 CURSOR c_sch_name (p_resp_id NUMBER) IS
43 SELECT oracle_username
44 FROM fnd_data_group_units_v dgrp,
45 fnd_responsibility resp
46 WHERE dgrp.application_id = resp.application_id
47 AND dgrp.data_group_id = resp.data_group_id
48 AND resp.responsibility_id = p_resp_id;
49
50 l_resp_id NUMBER;
51 BEGIN
52
53 Fnd_Profile.Get('RESP_ID', l_resp_id);
54
55 OPEN c_sch_name (l_resp_id);
56 FETCH c_sch_name INTO p_schema_name;
57 CLOSE c_sch_name;
58
59 IF p_schema_name IS NULL
60 THEN
61 errbuf := NULL;
62 retcode := 2;
63 write_to_log (l_event_level, 'get_schema_name','END Procedure get_schema_name - failed. Schema name null' );
64 Raise_Application_Error (-20000,
65 'Procedure get_schema_name - failed. Schema name null' );
66 END IF;
67
68
69 EXCEPTION
70 WHEN OTHERS THEN
71
72 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
73 retcode := 2;
74 errbuf := Fnd_message.get;
75
76 write_to_log ( l_excep_level, 'get_schema_name','END Procedure get_schema_name - failed with error '|| SQLERRM );
77 RETURN;
78
79 END get_schema_name;
80
81 /*------------------------------------------------------------------
82 This proecdure gets the schema names for the Multilingual and
83 Multi currency application
84
85 Its is called from procedure IGI_SLS_SECURITY_PKG.APPLY_SECURITY only.
86 ------------------------------------------------------------------*/
87 PROCEDURE get_mrc_mls_schemanames (p_mls_schema_name IN OUT NOCOPY VARCHAR2,
88 p_mrc_schema_name IN OUT NOCOPY VARCHAR2,
89 errbuf IN OUT NOCOPY VARCHAR2,
90 retcode IN OUT NOCOPY NUMBER)
91 IS
92
93 CURSOR c_get_install_num IS
94 SELECT install_group_num
95 FROM fnd_oracle_userid
96 WHERE read_only_flag = 'U'
97 ORDER BY install_group_num;
98
99 CURSOR c_get_flag IS
100 SELECT NVL(multi_currency_flag, 'N') multi_currency_flag,
101 NVL(multi_lingual_flag, 'N') multi_lingual_flag
102 FROM fnd_product_groups;
103
104 CURSOR c_get_schema_name (p_install_group_num NUMBER,
105 p_read_only_flag VARCHAR2) IS
106 SELECT oracle_username
107 FROM fnd_oracle_userid
108 WHERE (install_group_num = p_install_group_num
109 OR install_group_num = (SELECT MIN (install_group_num)
110 FROM fnd_oracle_userid
111 WHERE 1=DECODE(p_install_group_num,0,1,2)
112 AND read_only_flag = p_read_only_flag))
113 AND read_only_flag = p_read_only_flag;
114
115 CURSOR c_chk_install (p_schema_name IN VARCHAR2) IS
116 SELECT COUNT(*)
117 FROM dba_objects
118 WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
119 AND object_name IN ('APPS_DDL', 'APPS_ARRAY_DDL')
120 AND status = 'VALID'
121 AND owner = UPPER (p_schema_name);
122
123 l_install_group_num NUMBER := 1;
124 l_count NUMBER := 0;
125 l_multi_currency_flag VARCHAR2(1);
126 l_multi_lingual_flag VARCHAR2(1);
127
128 BEGIN
129
130 p_mls_schema_name := NULL;
131 p_mrc_schema_name := NULL;
132
133 write_to_log (l_state_level, 'get_mrc_mls_schemanames', 'Get_Mrc_Mls_Schemanames, Checking if multi currency and lingual flags are set');
134 -- Check if Multi Currency , Multi Lingual flag is on.
135 OPEN c_get_flag;
136 FETCH c_get_flag INTO l_multi_currency_flag,
137 l_multi_lingual_flag;
138 CLOSE c_get_flag;
139
140 -- Get the installation number.
141 write_to_log ( l_state_level, 'get_mrc_mls_schemanames','Get_Mrc_Mls_Schemanames, Fetching the installation group number');
142 OPEN c_get_install_num;
143 FETCH c_get_install_num INTO l_install_group_num;
144 CLOSE c_get_install_num;
145
146
147 IF l_multi_lingual_flag = 'Y'
148 THEN
149 -- If multilingual flag is set , get the MLS Schema name.
150 write_to_log ( l_state_level, 'get_mrc_mls_schemanames','Get_Mrc_Mls_Schemanames, Getting the MLS Schema Name');
151 OPEN c_get_schema_name (l_install_group_num,
152 'M');
153 FETCH c_get_schema_name INTO p_mls_schema_name;
154 CLOSE c_get_schema_name;
155
156 -- Bug 5144650 .. Start
157 IF p_mls_schema_name is not null THEN
158 -- Bug 5144650 .. End
159 -- Check if it has installed properly
160 write_to_log (l_state_level, 'get_mrc_mls_schemanames', 'Get_Mrc_Mls_Schemanames, Checking if MLS Schema Name has been installed' );
161 OPEN c_chk_install (p_mls_schema_name);
162 FETCH c_chk_install INTO l_count;
163 CLOSE c_chk_install ;
164
165 IF l_count <> 4 THEN
166 errbuf := NULL;
167 retcode := 2;
168 write_to_log (l_state_level, 'get_mrc_mls_schemanames','APPS_DDL / APPS_ARRAY_DDL package(s) missing or invalid in '|| p_mls_schema_name);
169 Raise_Application_Error (-20000,
170 'APPS_DDL / APPS_ARRAY_DDL package(s) missing or invalid in '||
171 p_mls_schema_name);
172 END IF;
173 -- Bug 5144650 .. Start
174 END IF;
175 -- Bug 5144650 .. End
176 END IF; -- Multi lingual flag is set
177
178 IF l_multi_currency_flag = 'Y'
179 THEN
180 -- iF MULticurrency flag is set , get the MRC Schema name.
181 write_to_log ( l_state_level, 'get_mrc_mls_schemanames','Get_Mrc_Mls_Schemanames, Getting the MRC Schema Name');
182 OPEN c_get_schema_name (l_install_group_num,
183 'K');
184 FETCH c_get_schema_name INTO p_mrc_schema_name;
185 CLOSE c_get_schema_name;
186
187 -- Bug 5144650 .. Start
188 IF p_mrc_schema_name is not null THEN
189 -- Bug 5144650 .. End
190 -- Check if it has installed properly
191 l_count := 0;
192
193 write_to_log (l_state_level, 'get_mrc_mls_schemanames','Get_Mrc_Mls_Schemanames, Checking if MRC Schema Name has been installed' );
194 OPEN c_chk_install (p_mrc_schema_name);
195 FETCH c_chk_install INTO l_count;
196 CLOSE c_chk_install ;
197
198 IF l_count <> 4 THEN
199 errbuf := NULL;
200 retcode := 2;
201 write_to_log (l_state_level, 'get_mrc_mls_schemanames','APPS_DDL / APPS_ARRAY_DDL package(s) missing or invalid in '|| p_mrc_schema_name);
202 Raise_Application_Error (-20000,
203 'APPS_DDL / APPS_ARRAY_DDL package(s) missing or invalid in '||
204 p_mrc_schema_name);
205 END IF;
206 -- Bug 5144650 .. Start
207 END IF;
208 -- Bug 5144650 .. End
209 END IF; -- Multi lingual flag is set
210
211 EXCEPTION
212 WHEN OTHERS THEN
213
214 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
215 retcode := 2;
216 errbuf := Fnd_message.get;
217 write_to_log (l_excep_level, 'get_mrc_mls_schemanames', 'END Procedure Apply Security - failed with error '|| SQLERRM );
218 RETURN;
219
220 END get_mrc_mls_schemanames;
221
222 /*------------------------------------------------------------------
223 This function checks if allocations exist for the table
224
225 Its is called from procedure IGI_SLS_SECURITY_PKG.APPLY_SECURITY only.
226 ------------------------------------------------------------------*/
227 FUNCTION check_allocation_exists ( p_table_name IN igi_sls_secure_tables.table_name%TYPE)
228 RETURN BOOLEAN
229 IS
230
231 l_count NUMBER := 0;
232
233 BEGIN
234
235 -- Check if table is directly allocated to a Security Group
236 SELECT COUNT(*)
237 INTO l_count
238 FROM igi_sls_allocations
239 WHERE sls_allocation = p_table_name
240 AND sls_allocation_type = 'T'
241 AND sls_group_type = 'S'
242 AND date_disabled IS NULL
243 AND date_removed IS NULL;
244
245 IF l_count = 0
246 THEN
247 -- Check if table is indirectly allocated to a Security Group
248 SELECT COUNT(*)
249 INTO l_count
250 FROM igi_sls_allocations a,
251 igi_sls_allocations b
252 WHERE a.sls_group_type = 'S'
253 AND a.sls_allocation = b.sls_group
254 AND a.sls_allocation_type = 'P'
255 AND a.date_disabled IS NULL
256 AND a.date_removed IS NULL
257 AND b.sls_group_type = 'P'
258 AND b.sls_allocation = p_table_name
259 AND b.sls_allocation_type = 'T'
260 AND b.date_disabled IS NULL
261 AND b.date_removed IS NULL;
262
263 IF l_count = 0
264 THEN
265 write_to_log (l_state_level, 'check_allocation_exists', 'Table '||p_table_name ||
266 ' is not allocated to any group or the allocation is not enabled');
267 RETURN FALSE;
268 ELSE
269 RETURN TRUE;
270 END IF;
271 ELSE
272 RETURN TRUE;
273 END IF;
274
275 EXCEPTION
276 WHEN OTHERS
277 THEN
278 write_to_log (l_excep_level, 'check_allocation_exists', 'END Procedure Apply Security - failed with error '|| SQLERRM );
279
280 IF ( l_unexp_level >= l_debug_level ) THEN
281
282 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
283 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
284 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
285 FND_LOG.MESSAGE ( l_unexp_level,l_path || 'check_allocation_exists' , TRUE);
286 END IF;
287 Raise_Application_Error (-20000,
288 'Error encountered in check_allocation_exists');
289 END check_allocation_exists;
290
291
292 /*------------------------------------------------------------------
293 This proecdure creates , disables, drops the sls objects depending
294 on their status in the igi_sls_secure_tables.
295
296 Its is called from procedure IGI_SLS_SECURITY_PKG.APPLY_SECURITY only.
297 ------------------------------------------------------------------*/
298 PROCEDURE create_drop_sls_objects ( p_mls_schema_name IN VARCHAR2,
299 p_mrc_schema_name IN VARCHAR2,
300 errbuf IN OUT NOCOPY VARCHAR2,
301 retcode IN OUT NOCOPY NUMBER)
302 IS
303 CURSOR c_get_sectab IS
304 SELECT owner,
305 table_name,
306 sls_table_name,
307 date_enabled,
308 date_disabled,
309 date_removed,
310 date_security_applied,
311 date_object_created,
312 update_allowed,
313 NVL(optimise_sql,'N') optimise_sql
314 FROM igi_sls_secure_tables
315 WHERE date_security_applied IS NULL;
316
317 rt_c_get_sectab c_get_sectab%ROWTYPE;
318
319 l_table_count NUMBER := 0;
320 l_policy_type VARCHAR2(50);
321 l_policy_function VARCHAR2(50);
322 l_policy_name VARCHAR2(50);
323 l_schema_name VARCHAR2(50);
324
325 l_date_security_applied DATE ;
326
327 BEGIN
328
329 retcode := 0;
330 errbuf := 'Normal Completion';
331
332 get_schema_name (p_schema_name => l_schema_name,
333 errbuf => errbuf,
334 retcode => retcode);
335
336 FOR rt_c_get_sectab IN c_get_sectab
337 LOOP
338
339 write_to_log (l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Processing Table ' || rt_c_get_sectab.table_name);
340
341 l_policy_function := rt_c_get_sectab.sls_table_name||'_FUN';
342 l_policy_name := rt_c_get_sectab.sls_table_name||'_POL';
343
344 IF rt_c_get_sectab.update_allowed = 'Y'
345 THEN
346 l_policy_type := 'SELECT,UPDATE';
347 ELSE
348 l_policy_type := 'SELECT';
349 END IF;
350
351 l_date_security_applied := NULL;
352
353 -- Security has been enabled, objects have not been created.
354 IF rt_c_get_sectab.date_object_created IS NULL
355 AND rt_c_get_sectab.date_disabled IS NULL
356 AND rt_c_get_sectab.date_removed IS NULL
357 AND check_allocation_exists (rt_c_get_sectab.table_name)
358 THEN
359 IF rt_c_get_sectab.optimise_sql = 'N'
360 THEN
361 -- Call Procedure to create SLS Table
362 write_to_log (l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Creating SLS Table ' ||
363 rt_c_get_sectab.sls_table_name);
364
365 igi_sls_objects_pkg.create_sls_tab
366 (sls_tab => rt_c_get_sectab.sls_table_name,
367 schema_name => l_schema_name,
368 errbuf => errbuf,
369 retcode => retcode);
370
371 ELSE
372 -- Call Procedure to create SLS Colmn
373 write_to_log (l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Creating SLS Col ');
374
375 igi_sls_objects_pkg.create_sls_col
376 (sec_tab => rt_c_get_sectab.table_name,
377 schema_name => rt_c_get_sectab.owner,
378 errbuf => errbuf,
379 retcode => retcode);
380
381 END IF;
382
383 IF retcode = 0
384 THEN
385 IF rt_c_get_sectab.optimise_sql = 'N'
386 THEN
387 -- Call Procedure to create SLS Table
388 write_to_log (l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Creating index for SLS Table ' ||
389 rt_c_get_sectab.sls_table_name);
390
391 igi_sls_objects_pkg.create_sls_inx
392 (sls_tab => rt_c_get_sectab.sls_table_name,
393 errbuf => errbuf,
394 retcode => retcode);
395
396 ELSE
397 igi_sls_objects_pkg.create_sls_core_inx
398 (sec_tab => rt_c_get_sectab.table_name,
399 sls_tab => rt_c_get_sectab.sls_table_name,
400 schema_name => rt_c_get_sectab.owner,
401 errbuf => errbuf,
402 retcode => retcode);
403 END IF;
404 END IF;
405
406 IF retcode = 0
407 AND rt_c_get_sectab.optimise_sql = 'N'
408 THEN
409 write_to_log ( l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Creating synonym for SLS Table ' ||
410 rt_c_get_sectab.sls_table_name );
411
412 igi_sls_objects_pkg.create_sls_apps_syn
413 (sls_tab => rt_c_get_sectab.sls_table_name,
414 schema_name => l_schema_name,
415 errbuf => errbuf,
416 retcode => retcode);
417
418 END IF;
419
420 /* Commented out NOCOPY MRC, MLS related code as per Atuls instructions - 29 Sep 2000.
421 IF retcode = 0
422 AND p_mls_schema_name IS NOT NULL
423 THEN
424 write_to_log (l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Creating synonym for SLS Table ' ||
425 rt_c_get_sectab.sls_table_name || ' in '|| p_mls_schema_name);
426
427 igi_sls_objects_pkg.create_sls_mls_syn
428 (sls_tab => rt_c_get_sectab.sls_table_name,
429 mls_schemaname => p_mls_schema_name,
430 errbuf => errbuf,
431 retcode => retcode);
432
433 END IF;
434
435 IF retcode = 0
436 AND p_mrc_schema_name IS NOT NULL
437 THEN
438 write_to_log (l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Creating synonym for SLS Table ' ||
439 rt_c_get_sectab.sls_table_name || ' in '|| p_mrc_schema_name);
440
441 igi_sls_objects_pkg.create_sls_mrc_syn
442 (sls_tab => rt_c_get_sectab.sls_table_name,
443 mrc_schemaname => p_mrc_schema_name,
444 errbuf => errbuf,
445 retcode => retcode);
446
447 END IF;
448 */
449
450 IF retcode = 0
451 THEN
452 -- Call Procedure to create Database Trigger
453 write_to_log (l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Creating trigger '||
454 rt_c_get_sectab.sls_table_name||'_TRG' ||
455 ' on table ' || rt_c_get_sectab.table_name);
456
457 IF rt_c_get_sectab.optimise_sql = 'N'
458 THEN
459 write_to_log(l_state_level, 'create_drop_sls_objects', 'New table created, Please run Maintain APPS_MRC,APPS_MLS,if any, and any other customer schema');
460 -- Call Procedure to create SLS Table
461 igi_sls_objects_pkg.create_sls_trg
462 (sls_tab => rt_c_get_sectab.sls_table_name,
463 sec_tab => rt_c_get_sectab.table_name,
464 errbuf => errbuf,
465 retcode => retcode);
466
467 ELSE
468 igi_sls_objects_pkg.create_sls_col_trg
469 (sls_tab => rt_c_get_sectab.sls_table_name,
470 sec_tab => rt_c_get_sectab.table_name,
471 errbuf => errbuf,
472 retcode => retcode);
473 END IF;
474
475 END IF;
476
477 IF retcode = 0
478 THEN
479 -- Call Procedure to create Policy Function
480 write_to_log (l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Creating policy function ' ||l_policy_function);
481
482 IF rt_c_get_sectab.optimise_sql = 'N'
483 THEN
484 -- Call Procedure to create SLS Table
485 igi_sls_objects_pkg.cre_pol_function
486 (sec_tab => rt_c_get_sectab.table_name,
487 sls_tab => rt_c_get_sectab.sls_table_name,
488 errbuf => errbuf,
489 retcode => retcode);
490 ELSE
491 igi_sls_objects_pkg.cre_ext_col_pol_func
492 (sec_tab => rt_c_get_sectab.table_name,
493 sls_tab => rt_c_get_sectab.sls_table_name,
494 errbuf => errbuf,
495 retcode => retcode);
496
497 END IF;
498
499
500 END IF;
501
502 IF retcode = 0
503 THEN
504 write_to_log (l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Creating policy'||
505 ' on table ' || rt_c_get_sectab.table_name);
506
507 -- Call Procedure to create Policy
508 igi_sls_objects_pkg.sls_add_pol
509 (object_schema => rt_c_get_sectab.owner,
510 table_name => rt_c_get_sectab.table_name,
511 policy_name => l_policy_name,
512 function_owner => l_schema_name,
513 policy_function => l_policy_function,
514 statement_types => l_policy_type,
515 errbuf => errbuf,
516 retcode => retcode);
517 END IF;
518
519 IF retcode = 0
520 THEN
521 write_to_log ( l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, All objects created successfully for '||
522 rt_c_get_sectab.table_name ||' updating igi_sls_secure_tables');
523
524 UPDATE igi_sls_secure_tables
525 SET date_object_created = SYSDATE,
526 last_update_login = to_number(fnd_profile.value('LOGIN_ID')),
527 last_update_date = SYSDATE,
528 last_updated_by = to_number(fnd_profile.value('USER_ID'))
529 WHERE table_name = rt_c_get_sectab.table_name
530 AND owner = rt_c_get_sectab.owner;
531
532 l_date_security_applied := SYSDATE;
533 END IF;
534
535 -- End of processing for New table defined and is enabled
536
537 ELSIF rt_c_get_sectab.date_object_created IS NOT NULL
538 AND rt_c_get_sectab.date_removed IS NULL
539 AND rt_c_get_sectab.date_disabled IS NULL
540 THEN
541 -- We need to drop and recreate the policy just in case user has changed the
542 -- update_allowed flag
543 IF retcode = 0
544 THEN
545 -- CALL Procedure to drop Policy
546 write_to_log (l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Dropping policy '||l_policy_name);
547 igi_sls_objects_pkg.sls_drop_pol
548 (object_schema => rt_c_get_sectab.owner,
549 table_name => rt_c_get_sectab.table_name,
550 policy_name => l_policy_name,
551 errbuf => errbuf,
552 retcode => retcode);
553 END IF;
554
555 IF retcode = 0
556 THEN
557 write_to_log (l_state_level, 'create_drop_sls_objects', 'Refresh_SLS_Objects, Creating policy ' ||l_policy_name ||
558 ' on table ' || rt_c_get_sectab.table_name);
559
560 -- Call Procedure to create Policy
561 igi_sls_objects_pkg.sls_add_pol
562 (object_schema => rt_c_get_sectab.owner,
563 table_name => rt_c_get_sectab.table_name,
564 policy_name => l_policy_name,
565 function_owner => l_schema_name,
566 policy_function => l_policy_function,
567 statement_types => l_policy_type,
568 errbuf => errbuf,
569 retcode => retcode);
570 END IF;
571
572 IF retcode = 0
573 THEN
574 l_date_security_applied := SYSDATE;
575 END IF;
576 -- End of Processing for Re Enabled
577
578 ELSIF rt_c_get_sectab.date_object_created IS NOT NULL
579 AND rt_c_get_sectab.date_disabled IS NOT NULL
580 AND rt_c_get_sectab.date_removed IS NULL
581 THEN
582 -- Security has been disabled
583 write_to_log (l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Disabling policy '||l_policy_name);
584 igi_sls_objects_pkg.sls_disable_pol
585 (object_schema => rt_c_get_sectab.owner,
586 table_name => rt_c_get_sectab.table_name,
587 policy_name => l_policy_name,
588 enable => FALSE,
589 errbuf => errbuf,
590 retcode => retcode);
591
592 IF retcode = 0
593 THEN
594 l_date_security_applied := SYSDATE;
595 END IF;
596
597 -- End of Processing for Disabled
598
599 ELSIF rt_c_get_sectab.date_object_created IS NOT NULL
600 AND rt_c_get_sectab.date_removed IS NOT NULL
601 THEN
602 -- Security has been deleted
603 write_to_log (l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Dropping policy '||l_policy_name);
604
605 igi_sls_objects_pkg.sls_drop_pol
606 (object_schema => rt_c_get_sectab.owner,
607 table_name => rt_c_get_sectab.table_name,
608 policy_name => l_policy_name,
609 errbuf => errbuf,
610 retcode => retcode);
611
612 IF retcode = 0
613 THEN
614 -- CALL Procedure to drop Policy Function
615 write_to_log (l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Dropping policy function '||l_policy_function);
616
617 igi_sls_objects_pkg.drop_pol_function
618 (sls_tab => rt_c_get_sectab.sls_table_name,
619 errbuf => errbuf,
620 retcode => retcode);
621
622 END IF;
623
624 IF retcode = 0
625 THEN
626 -- CALL Procedure to drop DB Trigger
627 write_to_log ( l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Dropping trigger on table '||
628 rt_c_get_sectab.table_name);
629 igi_sls_objects_pkg.drop_sls_trg
630 (sls_tab => rt_c_get_sectab.sls_table_name,
631 errbuf => errbuf,
632 retcode => retcode);
633 END IF;
634
635 IF retcode = 0
636 THEN
637 IF rt_c_get_sectab.optimise_sql = 'N'
638 THEN
639 -- CALL Procedure to drop SLS Table
640 write_to_log (l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Dropping table '||
641 rt_c_get_sectab.sls_table_name);
642 igi_sls_objects_pkg.drop_sls_tab
643 (sls_tab => rt_c_get_sectab.sls_table_name,
644 errbuf => errbuf,
645 retcode => retcode);
646
647 ELSE
648 igi_sls_objects_pkg.drop_sls_col
649 (sec_tab => rt_c_get_sectab.table_name,
650 schema_name => rt_c_get_sectab.owner,
651 errbuf => errbuf,
652 retcode => retcode);
653
654 -- The table should also be dropped.
655 igi_sls_objects_pkg.drop_sls_tab
656 (sls_tab => rt_c_get_sectab.sls_table_name,
657 errbuf => errbuf,
658 retcode => retcode);
659
660 END IF;
661
662 END IF;
663
664 IF retcode = 0
665 THEN
666 -- Drop the synonym even though the optimise sql = 'Y'
667 -- CALL Procedure to drop SLS APPS Synonyms
668 write_to_log (l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Dropping synonym table '||
669 rt_c_get_sectab.sls_table_name );
670 igi_sls_objects_pkg.drop_sls_apps_syn
671 (sls_tab => rt_c_get_sectab.sls_table_name,
672 schema_name => l_schema_name,
673 errbuf => errbuf,
674 retcode => retcode);
675 END IF;
676
677 /* Commented out NOCOPY MRC, MLS code as per Atuls, instructions. 29-Sep-2000.
678 IF retcode = 0
679 AND p_mls_schema_name IS NOT NULL
680 THEn
681 -- CALL Procedure to drop SLS MLS Synonyms
682 write_to_log (l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Dropping synonym table '||
683 rt_c_get_sectab.sls_table_name || ' in '||p_mls_schema_name);
684 igi_sls_objects_pkg.drop_sls_mls_syn
685 (sls_tab => rt_c_get_sectab.sls_table_name,
686 mls_schemaname => p_mls_schema_name,
687 errbuf => errbuf,
688 retcode => retcode);
689 END IF;
690
691 IF retcode = 0
692 AND p_mrc_schema_name IS NOT NULL
693 THEN
694 -- CALL Procedure to drop SLS MRC Synonyms
695 write_to_log (l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Dropping synonym on table '||
696 rt_c_get_sectab.sls_table_name || ' in '||p_mrc_schema_name);
697 igi_sls_objects_pkg.drop_sls_mrc_syn
698 (sls_tab => rt_c_get_sectab.sls_table_name,
699 mrc_schemaname => p_mrc_schema_name,
700 errbuf => errbuf,
701 retcode => retcode);
702 END IF;
703 */
704
705
706 IF retcode = 0
707 THEN
708 l_date_security_applied := SYSDATE;
709 END IF;
710
711 -- End of Processing for Deleted.
712
713 END IF; -- (Enabled / Disabled / Re-enabled / Deleted)
714
715 IF retcode = 0
716 THEN
717 -- All objects successfully created for the table.
718 write_to_log (l_state_level, 'create_drop_sls_objects', 'Create_Drop_SLS_Objects, Updating igi_sls_secure_tables.date_security_applied '||
719 ' for '|| rt_c_get_sectab.sls_table_name);
720
721 UPDATE igi_sls_secure_tables
722 SET date_security_applied = l_date_security_applied,
723 last_update_login = to_number(fnd_profile.value('LOGIN_ID')),
724 last_update_date = SYSDATE,
725 last_updated_by = to_number(fnd_profile.value('USER_ID'))
726 WHERE table_name = rt_c_get_sectab.table_name
727 AND owner = rt_c_get_sectab.owner;
728
729 -- Update the audit table only if the current row is
730 -- enabled or disabled.
731 -- If in future we decide to maintain an audit history
732 -- of all actions then this IF condition will have to go.
733 IF rt_c_get_sectab.date_disabled IS NOT NULL
734 OR rt_c_get_sectab.date_removed IS NOT NULL
735 THEN
736 UPDATE igi_sls_secure_tables_audit a
737 SET a.date_security_applied = SYSDATE
738 WHERE a.date_security_applied IS NULL
739 AND ROWID = (SELECT MAX(ROWID) b
740 FROM igi_sls_secure_tables_audit b
741 WHERE a.table_name = b.table_name
742 AND a.owner = b.owner)
743 AND table_name = rt_c_get_sectab.table_name
744 AND owner = rt_c_get_sectab.owner;
745
746 END IF;
747
748 END IF;
749
750 END LOOP; -- For each record in igi_sls_secure_tables (c_get_sectab)
751
752 COMMIT;
753
754 EXCEPTION
755 WHEN OTHERS THEN
756
757 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
758 retcode := 2;
759 errbuf := Fnd_message.get;
760
761 write_to_log (l_excep_level, 'create_drop_sls_objects', 'END Procedure Apply Security - failed with error '|| SQLERRM || ' in create_drop_sls_objects' );
762 ROLLBACK;
763 RETURN;
764
765 END create_drop_sls_objects;
766
767 /*------------------------------------------------------------------
768 This proecdure re-compiles i.e refreshes the triggers and procedures
769 for only the enabled tables in the igi_sls_secure_tables
770
771 Its is called from procedure IGI_SLS_SECURITY_PKG.APPLY_SECURITY only.
772 ------------------------------------------------------------------*/
773 PROCEDURE refresh_sls_objects ( p_mls_schema_name IN VARCHAR2,
774 p_mrc_schema_name IN VARCHAR2,
775 errbuf IN OUT NOCOPY VARCHAR2,
776 retcode IN OUT NOCOPY NUMBER)
777 IS
778 -- Cursor to select only those records which have had security applied earlier ie are not
779 -- new objects
780 CURSOR c_get_enab_sectab IS
781 SELECT owner,
782 table_name,
783 sls_table_name,
784 date_enabled,
785 date_disabled,
786 date_removed,
787 date_security_applied,
788 update_allowed,
789 Nvl(optimise_sql,'N') optimise_sql
790 FROM igi_sls_secure_tables
791 WHERE date_removed IS NULL
792 AND date_object_created IS NOT NULL;
793
794 l_policy_type VARCHAR2(50);
795 l_policy_function VARCHAR2(50);
796 l_policy_name VARCHAR2(50);
797 l_schema_name VARCHAR2(50);
798
799 BEGIN
800
801 retcode := 0;
802 errbuf := 'Normal Completion';
803
804 get_schema_name (p_schema_name => l_schema_name,
805 errbuf => errbuf,
806 retcode => retcode);
807
808
809 FOR rt_c_get_enab_sectab IN c_get_enab_sectab
810 LOOP
811 write_to_log ( l_state_level, 'refresh_sls_objects', 'Refresh_SLS_Objects, Processing table '|| rt_c_get_enab_sectab.table_name);
812
813 l_policy_function := rt_c_get_enab_sectab.sls_table_name||'_FUN';
814 l_policy_name := rt_c_get_enab_sectab.sls_table_name||'_POL';
815
816 IF rt_c_get_enab_sectab.update_allowed = 'Y'
817 THEN
818 l_policy_type := 'SELECT,UPDATE';
819 ELSE
820 l_policy_type := 'SELECT';
821 END IF;
822
823 IF rt_c_get_enab_sectab.optimise_sql = 'Y'
824 THEN
825 -- Call procedure to create additional column
826 igi_sls_objects_pkg.create_sls_col
827 (sec_tab => rt_c_get_enab_sectab.table_name,
828 schema_name => rt_c_get_enab_sectab.owner,
829 errbuf => errbuf,
830 retcode => retcode);
831 END IF;
832
833 -- Security has been enabled
834 IF rt_c_get_enab_sectab.date_disabled IS NULL
835 THEN
836 -- Call Procedure to create Database Trigger
837 write_to_log (l_state_level, 'refresh_sls_objects', 'Refresh_SLS_Objects, Re-Creating trigger '||
838 rt_c_get_enab_sectab.sls_table_name||'_TRG' ||
839 ' on table ' || rt_c_get_enab_sectab.table_name);
840
841 IF retcode = 0
842 THEN
843 IF rt_c_get_enab_sectab.optimise_sql = 'N'
844 THEN
845 igi_sls_objects_pkg.create_sls_trg
846 (sls_tab => rt_c_get_enab_sectab.sls_table_name,
847 sec_tab => rt_c_get_enab_sectab.table_name,
848 errbuf => errbuf,
849 retcode => retcode);
850
851 ELSE
852 igi_sls_objects_pkg.create_sls_col_trg
853 (sls_tab => rt_c_get_enab_sectab.sls_table_name,
854 sec_tab => rt_c_get_enab_sectab.table_name,
855 errbuf => errbuf,
856 retcode => retcode);
857 END IF;
858 END IF;
859
860 IF retcode = 0
861 THEN
862 -- Call Procedure to create Policy Function
863 write_to_log (l_state_level, 'refresh_sls_objects', 'Refresh_SLS_Objects, Re-Creating policy function ' ||l_policy_function);
864
865 IF rt_c_get_enab_sectab.optimise_sql = 'N'
866 THEN
867 igi_sls_objects_pkg.cre_pol_function
868 (sec_tab => rt_c_get_enab_sectab.table_name,
869 sls_tab => rt_c_get_enab_sectab.sls_table_name,
870 errbuf => errbuf,
871 retcode => retcode);
872 ELSE
873 igi_sls_objects_pkg.cre_ext_col_pol_func
874 (sec_tab => rt_c_get_enab_sectab.table_name,
875 sls_tab => rt_c_get_enab_sectab.sls_table_name,
876 errbuf => errbuf,
877 retcode => retcode);
878
879 END IF;
880 END IF;
881
882 -- We need to drop and recreate the policy just in case user has changed the
883 -- update_allowed flag
884 IF retcode = 0
885 THEN
886 -- CALL Procedure to drop Policy
887 write_to_log (l_state_level, 'refresh_sls_objects', 'Create_Drop_SLS_Objects, Dropping policy '||l_policy_name);
888 igi_sls_objects_pkg.sls_drop_pol
889 (object_schema => rt_c_get_enab_sectab.owner,
890 table_name => rt_c_get_enab_sectab.table_name,
891 policy_name => l_policy_name,
892 errbuf => errbuf,
893 retcode => retcode);
894 END IF;
895
896 IF retcode = 0
897 THEN
898 write_to_log (l_state_level, 'refresh_sls_objects', 'Refresh_SLS_Objects, Creating policy ' ||l_policy_name ||
899 ' on table ' || rt_c_get_enab_sectab.table_name);
900
901 -- Call Procedure to create Policy
902 igi_sls_objects_pkg.sls_add_pol
903 (object_schema => rt_c_get_enab_sectab.owner,
904 table_name => rt_c_get_enab_sectab.table_name,
905 policy_name => l_policy_name,
906 function_owner => l_schema_name,
907 policy_function => l_policy_function,
908 statement_types => l_policy_type,
909 errbuf => errbuf,
910 retcode => retcode);
911 END IF;
912
913 -- End of processing for enabled records
914
915 ELSIF rt_c_get_enab_sectab.date_disabled IS NOT NULL
916 THEN
917 -- Security has been disabled
918 write_to_log ( l_state_level, 'refresh_sls_objects','Refresh_SLS_Objects, Disabling policy '||l_policy_name);
919 igi_sls_objects_pkg.sls_disable_pol
920 (object_schema => rt_c_get_enab_sectab.owner,
921 table_name => rt_c_get_enab_sectab.table_name,
922 policy_name => l_policy_name,
923 enable => FALSE,
924 errbuf => errbuf,
925 retcode => retcode);
926
927 IF retcode = 0
928 THEN
929 write_to_log (l_state_level, 'refresh_sls_objects', 'Refresh_SLS_Objects, Re-Creating trigger '||
930 rt_c_get_enab_sectab.sls_table_name||'_TRG' ||
931 ' on table ' || rt_c_get_enab_sectab.table_name);
932
933 IF rt_c_get_enab_sectab.optimise_sql = 'N'
934 THEN
935 igi_sls_objects_pkg.create_sls_trg
936 (sls_tab => rt_c_get_enab_sectab.sls_table_name,
937 sec_tab => rt_c_get_enab_sectab.table_name,
938 errbuf => errbuf,
939 retcode => retcode);
940
941 ELSE
942 igi_sls_objects_pkg.create_sls_col_trg
943 (sls_tab => rt_c_get_enab_sectab.sls_table_name,
944 sec_tab => rt_c_get_enab_sectab.table_name,
945 errbuf => errbuf,
946 retcode => retcode);
947 END IF;
948 END IF;
949
950 IF retcode = 0
951 THEN
952 -- Call Procedure to create Policy Function
953 write_to_log ( l_state_level, 'refresh_sls_objects','Refresh_SLS_Objects, Re-Creating policy function ' ||l_policy_function);
954
955 IF rt_c_get_enab_sectab.optimise_sql = 'N'
956 THEN
957 igi_sls_objects_pkg.cre_pol_function
958 (sec_tab => rt_c_get_enab_sectab.table_name,
959 sls_tab => rt_c_get_enab_sectab.sls_table_name,
960 errbuf => errbuf,
961 retcode => retcode);
962 ELSE
963 igi_sls_objects_pkg.cre_ext_col_pol_func
964 (sec_tab => rt_c_get_enab_sectab.table_name,
965 sls_tab => rt_c_get_enab_sectab.sls_table_name,
966 errbuf => errbuf,
967 retcode => retcode);
968
969 END IF;
970 END IF;
971
972 -- End of processing for disabled records
973
974 END IF; -- (Disabled / Re-Enabled)
975
976
977 IF rt_c_get_enab_sectab.optimise_sql = 'N'
978 THEN
979 -- Recreate the index if absent
980 igi_sls_objects_pkg.create_sls_inx
981 (sls_tab => rt_c_get_enab_sectab.sls_table_name,
982 errbuf => errbuf,
983 retcode => retcode);
984 ELSE
985 igi_sls_objects_pkg.create_sls_core_inx
986 (sec_tab => rt_c_get_enab_sectab.table_name,
987 sls_tab => rt_c_get_enab_sectab.sls_table_name,
988 schema_name => rt_c_get_enab_sectab.owner,
989 errbuf => errbuf,
990 retcode => retcode);
991 END IF;
992
993 END LOOP; -- For every record in c_get_enab_sectab
994
995 EXCEPTION
996 WHEN OTHERS THEN
997
998 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
999 retcode := 2;
1000 errbuf := Fnd_message.get;
1001 write_to_log ( l_excep_level, 'refresh_sls_objects','END Procedure Apply Security - failed with error '|| SQLERRM );
1002
1003 END refresh_sls_objects;
1004
1005
1006 /*------------------------------------------------------------------
1007 This proecdure populates the igi_sls_security_group_alloc table with the most
1008 uptodate data
1009
1010 Its is called from procedure IGI_SLS_SECURITY_PKG.APPLY_SECURITY only.
1011 ------------------------------------------------------------------*/
1012 PROCEDURE populate_group_alloc ( errbuf IN OUT NOCOPY VARCHAR2,
1013 retcode IN OUT NOCOPY NUMBER)
1014 IS
1015
1016 l_sql_stmt VARCHAR2(500);
1017
1018 BEGIN
1019
1020 write_to_log (l_state_level, 'populate_group_alloc', 'Populate_Group_Alloc, Truncating table igi_sls_security_group_alloc ');
1021 l_sql_stmt := 'BEGIN igi.apps_ddl.apps_ddl('||'''TRUNCATE TABLE igi_sls_security_group_alloc'''||');END;';
1022
1023 EXECUTE IMMEDIATE l_sql_stmt;
1024
1025 write_to_log (l_state_level, 'populate_group_alloc', 'Populate_Group_Alloc, Inserting into table igi_sls_security_group_alloc ');
1026 INSERT INTO igi_sls_security_group_alloc
1027 (SLS_SECURITY_GROUP
1028 ,TABLE_NAME
1029 )
1030 SELECT DISTINCT sls_group,
1031 table_name
1032 FROM igi_sls_enabled_alloc_v;
1033
1034 COMMIT;
1035
1036 EXCEPTION
1037 WHEN OTHERS THEN
1038
1039 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
1040 retcode := 2;
1041 errbuf := Fnd_message.get;
1042 write_to_log (l_excep_level, 'populate_group_alloc', 'END Procedure Apply Security - failed with error '|| SQLERRM ||
1043 ' in populate_group_alloc');
1044 ROLLBACK;
1045 RETURN;
1046
1047 END populate_group_alloc;
1048
1049
1050 /*------------------------------------------------------------------
1051 This proecdure cleans up the data in the security tables after all
1052 the objets have been created.
1053
1054 Its is called from procedure IGI_SLS_SECURITY_PKG.APPLY_SECURITY only.
1055 ------------------------------------------------------------------*/
1056 PROCEDURE cleanup_data ( errbuf IN OUT NOCOPY VARCHAR2,
1057 retcode IN OUT NOCOPY NUMBER)
1058 IS
1059
1060 CURSOR c_del_table IS
1061 SELECT table_name,
1062 date_removed
1063 FROM igi_sls_secure_tables
1064 WHERE date_removed IS NOT NULL;
1065
1066 rt_c_del_table c_del_table%ROWTYPE;
1067
1068 CURSOR c_del_group IS
1069 SELECT sls_group,
1070 sls_group_type,
1071 date_removed
1072 FROM igi_sls_groups
1073 WHERE date_removed IS NOT NULL;
1074
1075 rt_c_del_group c_del_group%ROWTYPE;
1076
1077 no_table_exists EXCEPTION;
1078 PRAGMA EXCEPTION_INIT (no_table_exists, -00942);
1079
1080
1081 CURSOR c_del_alloc IS
1082 (SELECT a.sls_group sls_group,
1083 a.sls_allocation table_name
1084 FROM igi_sls_allocations a
1085 WHERE a.sls_group_type = 'S'
1086 AND a.sls_allocation_type = 'T'
1087 AND a.date_removed IS NOT NULL
1088 UNION
1089 SELECT a.sls_group sls_group,
1090 d.sls_allocation table_name
1091 FROM igi_sls_aLlocations a,
1092 igi_sls_allocations d
1093 WHERE a.sls_allocation = d.sls_group
1094 AND a.sls_group_type = 'S'
1095 AND a.sls_allocation_type = 'P'
1096 AND d.sls_group_type = 'P'
1097 AND d.sls_allocation_type = 'T'
1098 AND (a.date_removed IS NOT NULL or d.date_removed IS NOT NULL))
1099 MINUS
1100 SELECT sls_security_group sls_group,
1101 table_name table_name
1102 FROM igi_sls_security_group_alloc;
1103
1104 rt_c_del_alloc c_del_alloc%ROWTYPE;
1105
1106 CURSOR c_get_sls_tabname (p_table_name VARCHAR2) IS
1107 SELECT sls_table_name,
1108 date_removed,
1109 table_name,
1110 NVL(optimise_sql,'N') optimise_sql
1111 FROM igi_sls_secure_tables
1112 WHERE table_name = p_table_name;
1113
1114 l_sls_table_name igi_sls_secure_tables.sls_table_name%TYPE;
1115 l_date_removed igi_sls_secure_tables.date_removed%TYPE;
1116 l_table_name igi_sls_secure_tables.table_name%TYPE;
1117 l_optimise_sql igi_sls_secure_tables.OPTIMISE_SQL%TYPE;
1118 l_sql_stmt VARCHAR2(1000);
1119
1120 BEGIN
1121
1122 -- for every table, mark the allocations as deleted.
1123 write_to_log (l_state_level, 'cleanup_data', 'Cleanup_Data, Updating deleted tables in igi_sls_allocations');
1124 FOR rt_c_del_table IN c_del_table
1125 LOOP
1126 UPDATE igi_sls_allocations
1127 SET date_removed = rt_c_del_table.date_removed,
1128 date_disabled = Nvl(date_disabled, rt_c_del_table.date_removed),
1129 last_update_login = to_number(fnd_profile.value('LOGIN_ID')),
1130 last_update_date = SYSDATE,
1131 last_updated_by = to_number(fnd_profile.value('USER_ID'))
1132 WHERE sls_allocation = rt_c_del_table.table_name
1133 AND sls_allocation_type = 'T'
1134 AND date_removed IS NULL;
1135
1136
1137 -- Insert into the allocations audit table, the history for the the
1138 -- record that is about to be deleted.
1139 -- Insert the record only if it has not already been done earlier.
1140 INSERT INTO igi_sls_allocations_audit
1141 (sls_group,
1142 sls_group_type,
1143 sls_allocation,
1144 sls_allocation_type,
1145 date_enabled,
1146 date_disabled,
1147 date_removed ,
1148 date_security_applied,
1149 creation_date,
1150 created_by,
1151 last_update_login,
1152 last_update_date,
1153 last_updated_by)
1154 SELECT
1155 sls_group,
1156 sls_group_type,
1157 sls_allocation,
1158 sls_allocation_type,
1159 date_enabled,
1160 date_disabled,
1161 date_removed ,
1162 SYSDATE,
1163 creation_date,
1164 created_by,
1165 last_update_login,
1166 last_update_date,
1167 last_updated_by
1168 FROM igi_sls_allocations a
1169 WHERE a.sls_allocation = rt_c_del_table.table_name
1170 AND a.date_removed = rt_c_del_table.date_removed
1171 AND NOT EXISTS (SELECT 'X'
1172 FROM igi_sls_allocations_audit b
1173 WHERE a.sls_allocation = b.sls_allocation
1174 AND a.sls_group = b.sls_group
1175 AND a.date_enabled = b.date_enabled
1176 AND a.date_removed = b.date_removed);
1177
1178 END LOOP ; -- for each deleted table
1179
1180 -- for every group, mark the allocations as deleted.
1181 write_to_log (l_state_level, 'cleanup_data', 'Cleanup_Data, Updating deleted group in igi_sls_allocations');
1182 FOR rt_c_del_group IN c_del_group
1183 LOOP
1184 UPDATE igi_sls_allocations
1185 SET date_removed = rt_c_del_group.date_removed,
1186 date_disabled = Nvl(date_disabled, rt_c_del_group.date_removed),
1187 last_update_login = to_number(fnd_profile.value('LOGIN_ID')),
1188 last_update_date = SYSDATE,
1189 last_updated_by = to_number(fnd_profile.value('USER_ID'))
1190 WHERE sls_group = rt_c_del_group.sls_group
1191 AND sls_group_type = rt_c_del_group.sls_group_type
1192 AND date_removed IS NULL;
1193
1194 -- Insert into the allocations audit table, the history for the the
1195 -- record that is about to be deleted.
1196 -- Insert the record only if it has not already been done earlier.
1197 INSERT INTO igi_sls_allocations_audit
1198 (sls_group,
1199 sls_group_type,
1200 sls_allocation,
1201 sls_allocation_type,
1202 date_enabled,
1203 date_disabled,
1204 date_removed ,
1205 date_security_applied,
1206 creation_date,
1207 created_by,
1208 last_update_login,
1209 last_update_date,
1210 last_updated_by)
1211 SELECT
1212 sls_group,
1213 sls_group_type,
1214 sls_allocation,
1215 sls_allocation_type,
1216 date_enabled,
1217 date_disabled,
1218 date_removed ,
1219 SYSDATE,
1220 creation_date,
1221 created_by,
1222 last_update_login,
1223 last_update_date,
1224 last_updated_by
1225 FROM igi_sls_allocations a
1226 WHERE a.sls_group = rt_c_del_group.sls_group
1227 AND a.sls_group_type = rt_c_del_group.sls_group_type
1228 AND a.date_removed = rt_c_del_group.date_removed
1229 AND NOT EXISTS (SELECT 'X'
1230 FROM igi_sls_allocations_audit b
1231 WHERE a.sls_allocation = b.sls_allocation
1232 AND a.sls_group = b.sls_group
1233 AND a.sls_group_type = b.sls_group_type
1234 AND a.date_enabled = b.date_enabled
1235 AND a.date_removed = b.date_removed);
1236
1237 IF rt_c_del_group.sls_group_type = 'P'
1238 THEN
1239 UPDATE igi_sls_allocations
1240 SET date_removed = rt_c_del_group.date_removed,
1241 date_disabled = Nvl(date_disabled, rt_c_del_group.date_removed),
1242 last_update_login = to_number(fnd_profile.value('LOGIN_ID')),
1243 last_update_date = SYSDATE,
1244 last_updated_by = to_number(fnd_profile.value('USER_ID'))
1245 WHERE sls_allocation = rt_c_del_group.sls_group
1246 AND sls_group_type = rt_c_del_group.sls_group_type
1247 AND date_removed IS NULL;
1248
1249 -- Insert into the allocations audit table, the history for the the
1250 -- record that is about to be deleted.
1251 -- Insert the record only if it has not already been done earlier.
1252 INSERT INTO igi_sls_allocations_audit
1253 (sls_group,
1254 sls_group_type,
1255 sls_allocation,
1256 sls_allocation_type,
1257 date_enabled,
1258 date_disabled,
1259 date_removed ,
1260 date_security_applied,
1261 creation_date,
1262 created_by,
1263 last_update_login,
1264 last_update_date,
1265 last_updated_by)
1266 SELECT
1267 sls_group,
1268 sls_group_type,
1269 sls_allocation,
1270 sls_allocation_type,
1271 date_enabled,
1272 date_disabled,
1273 date_removed ,
1274 SYSDATE,
1275 creation_date,
1276 created_by,
1277 last_update_login,
1278 last_update_date,
1279 last_updated_by
1280 FROM igi_sls_allocations a
1281 WHERE a.sls_allocation = rt_c_del_group.sls_group
1282 AND a.date_removed = rt_c_del_group.date_removed
1283 AND NOT EXISTS (SELECT 'X'
1284 FROM igi_sls_allocations_audit b
1285 WHERE a.sls_allocation = b.sls_allocation
1286 AND a.sls_group = b.sls_group
1287 AND a.sls_group_type = b.sls_group_type
1288 AND a.date_enabled = b.date_enabled
1289 AND a.date_removed = b.date_removed);
1290
1291 END IF;
1292 END LOOP ; -- for each deleted group
1293
1294 -- For every record marked for deletion in igi_sls_allocations
1295 FOR rt_c_del_alloc IN c_del_alloc
1296 LOOP
1297 OPEN c_get_sls_tabname (rt_c_del_alloc.table_name);
1298 FETCH c_get_sls_tabname INTO l_sls_table_name,
1299 l_date_removed, l_table_name, l_optimise_sql;
1300 CLOSE c_get_sls_tabname;
1301
1302 IF l_sls_table_name IS NOT NULL
1303 AND l_date_removed IS NULL -- If table is deleted, then the extended table will have been
1304 -- dropped by now.
1305 THEN
1306
1307 IF l_optimise_sql = 'N'
1308 THEN
1309 write_to_log (l_state_level, 'cleanup_data', 'Cleanup_Data, Deleting records from table '||l_sls_table_name ||
1310 ' for group '|| rt_c_del_alloc.sls_group );
1311
1312 BEGIN
1313 l_sql_stmt := ' DELETE FROM '|| l_sls_table_name ||
1314 ' WHERE sls_sec_grp = :sls_group';
1315 -- ' WHERE sls_sec_grp = '''|| rt_c_del_alloc.sls_group || '''';
1316 write_to_log (l_state_level, 'cleanup_data', 'l_sls_table_name '|| l_sls_table_name);
1317 write_to_log (l_state_level, 'cleanup_data', 'sls_group' || rt_c_del_alloc.sls_group);
1318
1319 -- Bug 2972984, Use bind variables
1320 EXECUTE IMMEDIATE l_sql_stmt USING rt_c_del_alloc.sls_group;
1321 EXCEPTION
1322 WHEN no_table_exists THEN
1323 NULL;
1324 END ;
1325
1326 ELSE
1327 BEGIN
1328 write_to_log (l_state_level, 'cleanup_data', 'Cleanup_Data, Deleting records from table '|| rt_c_del_alloc.table_name ||
1329 ' for group '|| rt_c_del_alloc.sls_group );
1330
1331 l_sql_stmt := ' UPDATE ' || l_table_name ||
1332 ' SET igi_sls_sec_group = NULL ' ||
1333 ' WHERE igi_sls_sec_group = :sls_group';
1334 -- ' WHERE sls_sec_grp = '''|| rt_c_del_alloc.sls_group || '''';
1335 write_to_log (l_state_level, 'cleanup_data', 'l_sls_table_name '|| l_sls_table_name);
1336 write_to_log (l_state_level, 'cleanup_data', 'sls_group' || rt_c_del_alloc.sls_group);
1337 write_to_log (l_state_level, 'cleanup_data', 'table_name '|| rt_c_del_alloc.table_name);
1338 write_to_log (l_state_level, 'cleanup_data', 'l_table_name '|| l_table_name);
1339 write_to_log (l_state_level, 'cleanup_data', 'l_sql_stmt '|| l_sql_stmt);
1340 -- Bug 2972984, Use bind variables
1341 EXECUTE IMMEDIATE l_sql_stmt USING rt_c_del_alloc.sls_group;
1342
1343 write_to_log (l_state_level, 'cleanup_data', 'After Exec. Immediate');
1344
1345 delete from FND_PROFILE_OPTION_VALUES
1346 where PROFILE_OPTION_ID = (select profile_option_id from fnd_profile_options where
1347 profile_option_name = 'IGI_SLS_SECURITY_GROUP')
1348 and APPLICATION_ID = ( select application_id from fnd_application_vl where
1349 application_short_name = 'IGI' )
1350 and profile_option_value = rt_c_del_alloc.sls_group ;
1351
1352 write_to_log (l_state_level, 'cleanup_data', 'After Delete stmt. Immediate' || rt_c_del_alloc.sls_group);
1353 EXCEPTION
1354 WHEN no_table_exists THEN
1355 NULL;
1356 END ;
1357 END IF;
1358
1359 END IF;
1360
1361 END LOOP;
1362
1363 -- Delete all records marked for deletion
1364 write_to_log (l_state_level, 'cleanup_data', 'Cleanup_Data, Deleting ALL marked records from igi_sls_allocations');
1365 DELETE FROM igi_sls_allocations
1366 WHERE date_removed IS NOT NULL;
1367
1368 write_to_log (l_state_level, 'cleanup_data', 'Cleanup_Data, Deleting ALL marked records from igi_sls_secure_tables');
1369 DELETE FROM igi_sls_secure_tables
1370 WHERE date_removed IS NOT NULL;
1371
1372 write_to_log (l_state_level, 'cleanup_data', 'Cleanup_Data, Deleting ALL marked records from igi_sls_groups');
1373 DELETE FROM igi_sls_groups
1374 WHERE date_removed IS NOT NULL;
1375
1376 COMMIT;
1377
1378 EXCEPTION
1379 WHEN no_table_exists THEN
1380 NULL;
1381
1382 WHEN OTHERS THEN
1383
1384 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
1385 retcode := 2;
1386 errbuf := Fnd_message.get;
1387 write_to_log (l_excep_level, 'cleanup_data', 'END Procedure Apply Security - failed with error '|| SQLERRM ||
1388 ' when cleaning up data ');
1389 ROLLBACK;
1390
1391 END cleanup_data;
1392
1393
1394
1395 /*------------------------------------------------------------------
1396 This proecdure stamps the data with date_security_applied on the
1397 group and allocations table
1398
1399 Its is called from procedure IGI_SLS_SECURITY_PKG.APPLY_SECURITY only.
1400 ------------------------------------------------------------------*/
1401 PROCEDURE stamp_records ( errbuf IN OUT NOCOPY VARCHAR2,
1402 retcode IN OUT NOCOPY NUMBER)
1403 IS
1404
1405 CURSOR c_all_group IS
1406 SELECT sls_group,
1407 date_removed,
1408 date_disabled
1409 FROM igi_sls_groups
1410 WHERE date_security_applied IS NULL
1411 FOR UPDATE OF date_security_applied;
1412
1413 rt_c_all_group c_all_group%ROWTYPE;
1414
1415 CURSOR c_all_alloc IS
1416 SELECT sls_group,
1417 sls_allocation,
1418 date_disabled,
1419 date_removed
1420 FROM igi_sls_allocations
1421 WHERE date_security_applied IS NULL
1422 FOR UPDATE OF date_security_applied;
1423
1424 rt_c_all_alloc c_all_alloc%ROWTYPE;
1425
1426 BEGIN
1427
1428 write_to_log (l_state_level, 'stamp_records', 'Populate_Group_Alloc, Updating table igi_sls_groups.date_security_applied ');
1429 FOR rt_c_all_group IN c_all_group
1430 LOOP
1431 UPDATE igi_sls_groups
1432 SET date_security_applied = SYSDATE,
1433 last_update_login = to_number(fnd_profile.value('LOGIN_ID')),
1434 last_update_date = SYSDATE,
1435 last_updated_by = to_number(fnd_profile.value('USER_ID'))
1436 WHERE CURRENT OF c_all_group;
1437
1438 -- Update the audit table only if the current row is
1439 -- enabled or disabled.
1440 -- If in future we decide to maintain an audit history
1441 -- of all actions then this IF condition will have to go.
1442 IF rt_c_all_group.date_disabled IS NOT NULL
1443 OR rt_c_all_group.date_removed IS NOT NULL
1444 THEN
1445 UPDATE igi_sls_groups_audit a
1446 SET a.date_security_applied = SYSDATE
1447 WHERE date_security_applied IS NULL
1448 AND ROWID = (SELECT MAX(ROWID) b
1449 FROM igi_sls_groups_audit b
1450 WHERE a.sls_group = b.sls_group)
1451 AND sls_group = rt_c_all_group.sls_group;
1452
1453 END IF;
1454 END LOOP;
1455
1456 write_to_log (l_state_level, 'stamp_records', 'Populate_Group_Alloc, Updating table igi_sls_allocations.date_security_applied ');
1457 FOR rt_c_all_alloc IN c_all_alloc
1458 LOOP
1459 UPDATE igi_sls_allocations
1460 SET date_security_applied = SYSDATE,
1461 last_update_login = to_number(fnd_profile.value('LOGIN_ID')),
1462 last_update_date = SYSDATE,
1463 last_updated_by = to_number(fnd_profile.value('USER_ID'))
1464 WHERE CURRENT OF c_all_alloc;
1465
1466 -- Update the audit table only if the current row is
1467 -- enabled or disabled.
1468 -- If in future we decide to maintain an audit history
1469 -- of all actions then this IF condition will have to go.
1470 IF rt_c_all_alloc.date_disabled IS NOT NULL
1471 OR rt_c_all_alloc.date_removed IS NOT NULL
1472 THEN
1473 UPDATE igi_sls_allocations_audit a
1474 SET a.date_security_applied = SYSDATE
1475 WHERE a.date_security_applied IS NULL
1476 AND ROWID = (SELECT MAX(ROWID) b
1477 FROM igi_sls_allocations_audit b
1478 WHERE a.sls_allocation = b.sls_allocation
1479 AND a.sls_group = b.sls_group)
1480 AND sls_allocation = rt_c_all_alloc.sls_allocation
1481 AND sls_group = rt_c_all_alloc.sls_group;
1482 END IF;
1483
1484 END LOOP;
1485
1486 -- Records to be commited after the cleanup exercise
1487 EXCEPTION
1488 WHEN OTHERS THEN
1489
1490 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
1491 retcode := 2;
1492 errbuf := Fnd_message.get;
1493 write_to_log ( l_excep_level, 'stamp_records','END Procedure Apply Security - failed with error '|| SQLERRM ||
1494 ' when stamping records with the date_security_applied ');
1495 ROLLBACK;
1496
1497 END stamp_records;
1498
1499 /*---------------------------------------------------------------------
1500 This procedure contains the consolidatation of groups requirement to
1501 implement phase 2 of SLS.
1502 It is called only from apply_security.
1503 ---------------------------------------------------------------------*/
1504 PROCEDURE consolidate_groups ( errbuf IN OUT NOCOPY VARCHAR2,
1505 retcode IN OUT NOCOPY NUMBER)
1506 IS
1507
1508 CURSOR c_cons_recs IS
1509 SELECT from_sls_security_group,
1510 to_sls_security_group
1511 FROM igi_sls_consolidate_groups
1512 WHERE date_security_applied IS NULL;
1513
1514 CURSOR c_grp_alloc (p_sls_group igi_sls_groups.sls_group%TYPE) IS
1515 SELECT sls_group,
1516 sls_group_type,
1517 sls_allocation,
1518 sls_allocation_type,
1519 date_enabled,
1520 date_disabled,
1521 date_removed,
1522 date_security_applied,
1523 creation_date,
1524 created_by,
1525 last_update_login,
1526 last_update_date,
1527 last_updated_by
1528 FROM igi_sls_allocations
1529 WHERE sls_group = p_sls_group
1530 AND date_removed IS NULL;
1531
1532 CURSOR c_prcgrp_alloc (p_sls_group igi_sls_groups.sls_group%TYPE) IS
1533 SELECT sls_allocation
1534 FROM igi_sls_allocations
1535 WHERE sls_group = p_sls_group
1536 AND sls_allocation_type = 'T'
1537 AND date_removed IS NULL;
1538
1539 CURSOR c_sls_tname (p_table_name igi_sls_secure_tables.table_name%TYPE) IS
1540 SELECT sls_table_name,
1541 -- Bug 5144650 .. Start
1542 NVL(optimise_sql,'N') optimise_sql
1543 -- Bug 5144650 .. End
1544 FROM igi_sls_secure_tables
1545 WHERE table_name = p_table_name;
1546
1547 l_sls_tabname igi_sls_secure_tables.sls_table_name%TYPE;
1548 l_sql_stmt VARCHAR2(1000);
1549 l_alloc_count NUMBER;
1550 l_text VARCHAR2(500);
1551 l_enab_rec_count NUMBER;
1552 l_date_disabled DATE;
1553 l_sysdate DATE := SYSDATE;
1554
1555 -- Bug 5144650 .. Start
1556 l_optimise_sql igi_sls_secure_tables.optimise_sql%TYPE;
1557 -- Bug 5144650 .. End
1558 no_table_exists EXCEPTION;
1559 PRAGMA EXCEPTION_INIT (no_table_exists, -00942);
1560
1561 BEGIN
1562
1563 -- Get all the groups that need to be merged / transferred.
1564 FOR rt_c_cons_recs IN c_cons_recs
1565 LOOP
1566 l_text := 'consolidate_groups, Consolidating group ' ||rt_c_cons_recs.from_sls_security_group||
1567 ' with ' || rt_c_cons_recs.to_sls_security_group;
1568
1569 write_to_log (l_state_level, 'consolidate_groups', l_text);
1570
1571 FOR rt_c_grp_alloc IN c_grp_alloc(rt_c_cons_recs.from_sls_security_group)
1572 LOOP
1573
1574 IF rt_c_grp_alloc.sls_allocation_type = 'T'
1575 THEN
1576 OPEN c_sls_tname (rt_c_grp_alloc.sls_allocation);
1577 -- Bug 5144650 .. Start
1578 FETCH c_sls_tname INTO l_sls_tabname, l_optimise_sql;
1579 -- Bug 5144650 .. End
1580 CLOSE c_sls_tname;
1581
1582 -- Bug 2972984, Use bind variables
1583 /*
1584 l_sql_stmt := ' UPDATE ' ||l_sls_tabname ||
1585 ' SET sls_sec_grp = '''||rt_c_cons_recs.to_sls_security_group ||''',' ||
1586 ' prev_sls_sec_grp = '''||rt_c_cons_recs.from_sls_security_group||''','||
1587 ' change_date = SYSDATE ' ||
1588 ' WHERE sls_sec_grp = '''||rt_c_cons_recs.from_sls_security_group||'''';
1589 */
1590
1591 l_sql_stmt := ' UPDATE ' ||l_sls_tabname ||
1592 ' SET sls_sec_grp = :to_sls_security_group ,'||
1593 ' prev_sls_sec_grp = :from_sls_security_group,'||
1594 ' change_date = SYSDATE ' ||
1595 ' WHERE sls_sec_grp = :from_sls_security_group';
1596 BEGIN
1597 EXECUTE IMMEDIATE l_sql_stmt
1598 USING rt_c_cons_recs.to_sls_security_group,
1599 rt_c_cons_recs.from_sls_security_group,
1600 rt_c_cons_recs.from_sls_security_group;
1601 EXCEPTION
1602 WHEN no_table_exists THEN
1603 NULL;
1604 END;
1605
1606 ELSIF rt_c_grp_alloc.sls_allocation_type = 'P'
1607 THEN
1608
1609 FOR rt_c_pgrp_alloc IN c_prcgrp_alloc (rt_c_grp_alloc.sls_allocation)
1610 LOOP
1611 OPEN c_sls_tname (rt_c_pgrp_alloc.sls_allocation);
1612 -- Bug 5144650 .. Start
1613 FETCH c_sls_tname INTO l_sls_tabname, l_optimise_sql;
1614 -- Bug 5144650 .. End
1615 CLOSE c_sls_tname;
1616 -- Bug 2972984, Use bind variables
1617 /*
1618 l_sql_stmt := ' UPDATE ' ||l_sls_tabname ||
1619 ' SET sls_sec_grp = '''||rt_c_cons_recs.to_sls_security_group ||''',' ||
1620 ' prev_sls_sec_grp = '''||rt_c_cons_recs.from_sls_security_group||''','||
1621 ' change_date = SYSDATE ' ||
1622 ' WHERE sls_sec_grp = '''||rt_c_cons_recs.from_sls_security_group||'''';
1623
1624 */
1625 l_sql_stmt := ' UPDATE ' ||l_sls_tabname ||
1626 ' SET sls_sec_grp = :to_sls_security_group ,'||
1627 ' prev_sls_sec_grp = :from_sls_security_group,'||
1628 ' change_date = SYSDATE ' ||
1629 ' WHERE sls_sec_grp = :from_sls_security_group';
1630 BEGIN
1631 EXECUTE IMMEDIATE l_sql_stmt
1632 USING rt_c_cons_recs.to_sls_security_group,
1633 rt_c_cons_recs.from_sls_security_group,
1634 rt_c_cons_recs.from_sls_security_group;
1635
1636 EXCEPTION
1637 WHEN no_table_exists THEN
1638 NULL;
1639 END;
1640
1641 END LOOP;
1642 END IF; -- Allocation type = 'P' or 'T'
1643
1644 -- Bug 5144650 .. Start
1645 If l_optimise_sql = 'Y' Then
1646 write_to_log (l_excep_level, 'consolidate_groups', 'optimise sql flag for table' || rt_c_grp_alloc.sls_allocation || ' is set to ' || l_optimise_sql);
1647 l_sql_stmt := ' UPDATE ' || rt_c_grp_alloc.sls_allocation ||
1648 ' SET igi_sls_sec_group = :to_sls_security_group '||
1649 ' WHERE igi_sls_sec_group = :from_sls_security_group';
1650 Begin
1651 Execute Immediate l_sql_stmt
1652 USING rt_c_cons_recs.to_sls_security_group,
1653 rt_c_cons_recs.from_sls_security_group;
1654 Exception
1655 When no_table_exists Then
1656 Null;
1657 When others then
1658 Raise;
1659 End;
1660 End If;
1661 -- Bug 5144650 .. End
1662
1663 -- Check if the allocation already exists in the target group.
1664 SELECT COUNT(*)
1665 INTO l_alloc_count
1666 FROM igi_sls_allocations
1667 WHERE sls_allocation = rt_c_grp_alloc.sls_allocation
1668 AND sls_group = rt_c_cons_recs.to_sls_security_group
1669 AND sls_allocation_type = rt_c_grp_alloc.sls_allocation_type;
1670
1671 IF l_alloc_count = 0
1672 THEN
1673
1674 -- Check for the status of the allocation in all the groups involved.
1675 -- Mark it disabled only if it is disabled in all groups, else
1676 -- Mark it enabled.
1677 SELECT COUNT(*)
1678 INTO l_enab_rec_count
1679 FROM igi_sls_allocations a,
1680 igi_sls_consolidate_groups b
1681 WHERE a.sls_group = b.from_sls_security_group
1682 AND a.date_disabled IS NULL
1683 AND a.date_removed IS NULL
1684 AND a.sls_allocation = rt_c_grp_alloc.sls_allocation
1685 AND b.to_sls_security_group = rt_c_cons_recs.to_sls_security_group;
1686
1687 IF l_enab_rec_count = 0
1688 THEN
1689 -- In all the groups that need to be consolidatd, the
1690 -- allocation is not enabled anywhere.
1691 l_date_disabled := SYSDATE;
1692 ELSE
1693 -- The allocation is enabled atleast in one group
1694 l_date_disabled := NULL;
1695 END IF;
1696
1697 -- Since Allocation does not exist, insert into the table.
1698 INSERT INTO igi_sls_allocations
1699 (sls_group,
1700 sls_group_type,
1701 sls_allocation,
1702 sls_allocation_type,
1703 date_enabled,
1704 date_disabled,
1705 date_removed ,
1706 date_security_applied,
1707 creation_date,
1708 created_by,
1709 last_update_login,
1710 last_update_date,
1711 last_updated_by)
1712 VALUES
1713 (rt_c_cons_recs.to_sls_security_group,
1714 'S',
1715 rt_c_grp_alloc.sls_allocation,
1716 rt_c_grp_alloc.sls_allocation_type,
1717 SYSDATE,
1718 l_date_disabled,
1719 NULL,
1720 NULL,
1721 SYSDATE,
1722 to_number(fnd_profile.value('USER_ID')),
1723 to_number(fnd_profile.value('LOGIN_ID')),
1724 SYSDATE,
1725 to_number(fnd_profile.value('USER_ID')));
1726 END IF; -- record with the same allocation does not already exist.
1727
1728 END LOOP; -- rt_c_grp_alloc
1729
1730 -- Mark the group for deletion,
1731 -- The allocation and the audit table population will be done in the
1732 -- cleanup_data procedure. Hence, date_security_applied is set to null.
1733 UPDATE igi_sls_groups
1734 SET date_disabled = Nvl(date_disabled, l_sysdate),
1735 date_removed = SYSDATE,
1736 date_security_applied = NULL,
1737 last_update_login = to_number(fnd_profile.value('LOGIN_ID')),
1738 last_update_date = SYSDATE,
1739 last_updated_by = to_number(fnd_profile.value('USER_ID'))
1740 WHERE sls_group = rt_c_cons_recs.from_sls_security_group
1741 AND date_removed IS NULL;
1742
1743 -- Since the group is being marked for deletion, enter a record
1744 -- into the audit table.
1745 INSERT INTO igi_sls_groups_audit
1746 (sls_group,
1747 sls_group_type,
1748 description,
1749 date_enabled,
1750 date_disabled,
1751 date_removed,
1752 date_security_applied,
1753 creation_date,
1754 created_by,
1755 last_update_login,
1756 last_update_date,
1757 last_updated_by)
1758 SELECT
1759 sls_group,
1760 sls_group_type,
1761 description,
1762 date_enabled,
1763 date_disabled,
1764 date_removed,
1765 date_security_applied,
1766 creation_date,
1767 created_by,
1768 last_update_login,
1769 last_update_date,
1770 last_updated_by
1771 FROM igi_sls_groups
1772 WHERE sls_group = rt_c_cons_recs.from_sls_security_group
1773 AND date_removed = l_sysdate;
1774
1775 UPDATE igi_sls_consolidate_groups
1776 SET date_security_applied = SYSDATE,
1777 last_update_login = to_number(fnd_profile.value('LOGIN_ID')),
1778 last_update_date = SYSDATE,
1779 last_updated_by = to_number(fnd_profile.value('USER_ID'))
1780 WHERE date_security_applied IS NULL;
1781
1782 COMMIT;
1783
1784 END LOOP; -- rt_c_cons_recs
1785
1786 EXCEPTION
1787 WHEN OTHERS THEN
1788
1789 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
1790 retcode := 2;
1791 errbuf := Fnd_message.get;
1792 write_to_log (l_excep_level, 'consolidate_groups', 'END Procedure Apply Security - failed with error '|| SQLERRM ||
1793 ' when consolidating groups ');
1794 ROLLBACK;
1795
1796
1797 END consolidate_groups;
1798
1799
1800 /*---------------------------------------------------------------------
1801 This procedure calls the various procedures to implement security on
1802 the secure tables defined.
1803 Parameters :
1804 Input : p_mode - CREATE - Create the SLS objects
1805 REFRESH - refresh the SLS objects
1806 Output : errbuf - Exit error message
1807 retcode - Return code for the procedure.
1808 0 - Success
1809 1 - Warning
1810 2 - Failure
1811 ---------------------------------------------------------------------*/
1812 PROCEDURE apply_security ( errbuf IN OUT NOCOPY VARCHAR2,
1813 retcode IN OUT NOCOPY NUMBER,
1814 p_mode IN VARCHAR2)
1815 IS
1816
1817 p_mrc_schema_name VARCHAR2(30);
1818 p_mls_schema_name VARCHAR2(30);
1819
1820 BEGIN
1821 errbuf := NULL;
1822 retcode := 0;
1823
1824 write_to_log (l_state_level, 'apply_security', 'BEGIN Apply Security - Parameter passed in - '|| p_mode );
1825
1826 -- Get the MRC, MLS schema names.
1827
1828 get_mrc_mls_schemanames ( p_mls_schema_name,
1829 p_mrc_schema_name,
1830 errbuf,
1831 retcode );
1832
1833
1834
1835 IF p_mode = 'CREATE'
1836 AND retcode = 0
1837 THEN
1838 -- Call Procedure to create and drop objects
1839 create_drop_sls_objects ( p_mls_schema_name,
1840 p_mrc_schema_name,
1841 errbuf,
1842 retcode );
1843
1844
1845 -- If successful, call procedure to consolidate groups
1846 IF retcode = 0
1847 THEN
1848 consolidate_groups (errbuf,
1849 retcode);
1850 END IF;
1851
1852 -- If successful, call procedure to populate the allocation table
1853 IF retcode = 0
1854 THEN
1855 populate_group_alloc (errbuf,
1856 retcode);
1857 END IF;
1858
1859 -- If successful, call procedure to stamp records with the date_security_applied
1860 IF retcode = 0
1861 THEN
1862 stamp_records (errbuf,
1863 retcode);
1864 END IF;
1865
1866 -- If successful, call procedure to clean up data in the tables.
1867 IF retcode = 0
1868 THEN
1869 cleanup_data (errbuf,
1870 retcode);
1871 END IF;
1872
1873 ELSIF p_mode = 'REFRESH'
1874 AND retcode = 0
1875 THEN
1876 -- call procedure to refresh objects
1877 refresh_sls_objects ( p_mls_schema_name,
1878 p_mrc_schema_name,
1879 errbuf,
1880 retcode);
1881 END IF;
1882
1883 write_to_log (l_state_level, 'apply_security', 'END Apply Security - Completed');
1884
1885 END apply_security ;
1886
1887
1888
1889 /*---------------------------------------------------------------------
1890 This procedure secures existing data
1891 Written for Enhancement Request 2263845
1892 Parameters :
1893 Input : p_sls_group - SLS group for which this process needs to run
1894 Output : errbuf - Exit error message
1895 retcode - Return code for the procedure.
1896 0 - Success
1897 1 - Warning
1898 2 - Failure
1899 ---------------------------------------------------------------------*/
1900 PROCEDURE secure_existing_data ( errbuf IN OUT NOCOPY VARCHAR2,
1901 retcode IN OUT NOCOPY NUMBER,
1902 p_sec_grp IN VARCHAR2)
1903 IS
1904
1905 CURSOR c_sec_dat IS
1906 SELECT a.sls_table_name,
1907 b.table_name,
1908 b.sls_security_group,
1909 a.owner,
1910 Nvl(a.optimise_sql,'N') optimise_sql
1911 FROM igi_sls_secure_tables a,
1912 igi_sls_security_group_alloc b
1913 WHERE a.table_name = b.table_name
1914 AND b.sls_security_group = Nvl(p_sec_grp , b.sls_security_group);
1915
1916 CURSOR c_chk_tab IS
1917 SELECT DISTINCT a.table_name
1918 FROM igi_sls_security_group_alloc a
1919 WHERE a.table_name in (SELECT table_name
1920 FROM igi_sls_security_group_alloc
1921 GROUP BY table_name
1922 HAVING COUNT(*) > 1)
1923 AND a.sls_security_group = Nvl(p_sec_grp , a.sls_security_group);
1924
1925
1926 l_count NUMBER := 0;
1927 l_dup_tabs_exist BOOLEAN := FALSE;
1928 l_sql_stmt VARCHAR2(2000);
1929
1930 l_schema fnd_oracle_userid.oracle_username%TYPE;
1931 l_prod_status fnd_product_installations.status%TYPE;
1932 l_industry fnd_product_installations.industry%TYPE;
1933
1934
1935 -- Exceptions
1936 igi_sls_sec_not_applied_excep EXCEPTION;
1937
1938
1939 BEGIN
1940 IF NOT fnd_installation.get_app_info (application_short_name => 'IGI',
1941 status => l_prod_status,
1942 industry => l_industry,
1943 oracle_schema => l_schema)
1944 THEN
1945 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1946 END IF;
1947
1948 -- Check if Security has been applied
1949 SELECT COUNT(*)
1950 INTO l_count
1951 FROM igi_sls_secure_tables a,
1952 all_objects b
1953 WHERE a.sls_table_name = b.object_name
1954 AND b.owner = l_schema; -- Bug 3431843 hkaniven
1955
1956 IF l_count = 0
1957 THEN
1958 RAISE igi_sls_sec_not_applied_excep;
1959 END IF;
1960
1961 -- Check if there are tables that belong to more than 1
1962 -- security group. If there are , print all of them onto
1963 -- the log and then raise exception.
1964 FOR l_chk_tab IN c_chk_tab
1965 LOOP
1966 l_dup_tabs_exist := TRUE;
1967
1968 fnd_message.set_name('IGI','IGI_SLS_DUP_ALLOC_EXISTS');
1969 fnd_message.set_token('TAB_NAME',l_chk_tab.table_name);
1970 Write_to_log(l_state_level, 'secure_existing_data',Fnd_Message.Get);
1971
1972 END LOOP ;
1973
1974 -- Get the tables for which the data needs to be made secure
1975 write_to_log (l_state_level, 'secure_existing_data','Securing existing data for all enabled tables .. ');
1976 FOR l_sec_dat IN c_sec_dat
1977 LOOP
1978 write_to_log (l_state_level, 'secure_existing_data','Processing '||l_sec_dat.table_name);
1979
1980 IF l_sec_dat.optimise_sql = 'N'
1981 THEN
1982 -- All Validations passed, insert data.
1983 /*
1984 l_sql_stmt := ' INSERT INTO '||l_sec_dat.sls_table_name ||
1985 ' (sls_rowid, sls_sec_grp) ' ||
1986 ' SELECT rowid, ' ||
1987 ''''||l_sec_dat.sls_security_group||'''' ||
1988 ' FROM '|| l_sec_dat.table_name || ' a ' ||
1989 ' WHERE NOT EXISTS (SELECT ''X''' ||
1990 ' FROM '||l_sec_dat.sls_table_name ||' b' ||
1991 ' WHERE a.rowid = b.sls_rowid )';
1992
1993 */
1994 l_sql_stmt := ' INSERT INTO '||l_sec_dat.sls_table_name ||
1995 ' (sls_rowid, sls_sec_grp) ' ||
1996 ' SELECT rowid, ' ||
1997 ' :sls_security_group' ||
1998 ' FROM '|| l_sec_dat.table_name || ' a ' ||
1999 ' WHERE NOT EXISTS (SELECT ''X''' ||
2000 ' FROM '||l_sec_dat.sls_table_name ||' b' ||
2001 ' WHERE a.rowid = b.sls_rowid )';
2002
2003 EXECUTE IMMEDIATE l_sql_stmt USING l_sec_dat.sls_security_group;
2004 ELSE
2005 -- User should have disabled SLS before they did this.
2006 -- Else, the update wont work.
2007 /*
2008 l_sql_stmt := ' UPDATE ' || l_sec_dat.table_name ||
2009 ' SET igi_sls_sec_group = '||''''||l_sec_dat.sls_security_group||''''||
2010 ' WHERE igi_sls_sec_group IS NULL ';
2011 */
2012 l_sql_stmt := ' UPDATE ' || l_sec_dat.table_name ||
2013 ' SET igi_sls_sec_group = :sls_security_group'||
2014 ' WHERE igi_sls_sec_group IS NULL ';
2015
2016 EXECUTE IMMEDIATE l_sql_stmt USING l_sec_dat.sls_security_group;
2017
2018 END IF;
2019
2020 IF l_sec_dat.optimise_sql = 'N'
2021 THEN
2022 -- Create Index, as they might not be present if User had already
2023 -- installed SLS
2024 igi_sls_objects_pkg.create_sls_inx
2025 (sls_tab => l_sec_dat.sls_table_name,
2026 errbuf => errbuf,
2027 retcode => retcode);
2028 ELSE
2029 igi_sls_objects_pkg.create_sls_core_inx
2030 (sec_tab => l_sec_dat.table_name,
2031 sls_tab => l_sec_dat.sls_table_name,
2032 schema_name => l_sec_dat.owner,
2033 errbuf => errbuf,
2034 retcode => retcode);
2035 END IF;
2036
2037 COMMIT;
2038 END LOOP;
2039
2040 EXCEPTION
2041
2042 WHEN igi_sls_sec_not_applied_excep
2043 THEN
2044 fnd_message.set_name('IGI','IGI_SLS_SEC_NOT_APPLIED');
2045 errbuf := fnd_message.get;
2046 write_to_log(l_excep_level, 'secure_existing_data',errbuf);
2047 retcode := 2;
2048 RETURN;
2049
2050 WHEN OTHERS
2051 THEN
2052
2053 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
2054 errbuf := Fnd_message.get;
2055
2056 write_to_log ( l_excep_level, 'secure_existing_data','END Procedure Secure Existing data - failed with error '|| SQLERRM );
2057 ROLLBACK;
2058 retcode := 2;
2059 RETURN;
2060
2061 END secure_existing_data;
2062
2063
2064
2065 END igi_sls_security_pkg ;