[Home] [Help]
PACKAGE BODY: APPS.IGI_SLS_OBJECTS_PKG
Source
1 PACKAGE BODY igi_sls_objects_pkg AS
2 --$Header: igislsob.pls 120.5.12000000.5 2007/11/08 07:32:09 vspuli 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.igislsob.igi_sls_objects_pkg.';
12
13
14 /*PROCEDURE write_to_log(p_message IN VARCHAR2)
15 IS
16
17 BEGIN
18 FND_FILE.put_line(FND_FILE.log,p_message);
19
20 END write_to_log;*/
21
22
23 PROCEDURE write_to_log(p_level IN NUMBER, p_path IN VARCHAR2, p_mesg IN VARCHAR2)
24 IS
25 BEGIN
26 IF (p_level >= l_debug_level ) THEN
27 FND_LOG.STRING (p_level , l_path || p_path , p_mesg );
28 END IF;
29 END write_to_log;
30
31
32 PROCEDURE create_sls_tab(sls_tab IN VARCHAR2,
33 schema_name IN VARCHAR2,
34 errbuf OUT NOCOPY VARCHAR2,
35 retcode OUT NOCOPY NUMBER)
36 IS
37
38 check_ts_mode varchar2(100);
39 check_tspace_exists varchar2(100);
40 physical_tspace_name varchar2(100);
41 l_sql_stat varchar2(300);
42 l_sql_grt VARCHAR2(300);
43 already_exists EXCEPTION;
44
45 l_user varchar2(5);
46 PRAGMA EXCEPTION_INIT(already_exists, -00955);
47
48 l_sql_mls_syn VARCHAR2(300);
49
50 BEGIN
51
52 l_user := 'IGI';
53 errbuf := 'Normal Completion';
54 retcode := 0;
55
56 ad_tspace_util.is_new_ts_mode(check_ts_mode);
57 ad_tspace_util.get_tablespace_name('IGI', 'TRANSACTION_TABLES', 'Y', check_tspace_exists, physical_tspace_name);
58
59 If (check_ts_mode = 'Y') and (check_tspace_exists = 'Y') THEN
60
61
62 l_sql_stat:='BEGIN ' || l_user || '.apps_ddl.apps_ddl('||'''CREATE TABLE '||sls_tab||'(SLS_ROWID ROWID CONSTRAINT '||sls_tab ||
63 '_PK PRIMARY KEY,SLS_SEC_GRP VARCHAR2(30),PREV_SLS_SEC_GRP VARCHAR2(30), CHANGE_DATE DATE) TABLESPACE '||
64 physical_tspace_name||''''||');END;';
65
66 Else
67
68 l_sql_stat:='BEGIN ' || l_user || '.apps_ddl.apps_ddl('||'''CREATE TABLE '||sls_tab||'(SLS_ROWID ROWID CONSTRAINT '||sls_tab ||'_PK PRIMARY KEY,SLS_SEC_GRP VARCHAR2(30),PREV_SLS_SEC_GRP VARCHAR2(30), CHANGE_DATE DATE)'''||');END;';
69
70 End If;
71
72 EXECUTE IMMEDIATE l_sql_stat;
73
74 l_sql_grt:= 'BEGIN ' || l_user || '.apps_ddl.apps_ddl ('||'''GRANT ALL ON '||sls_tab||' TO ' || schema_name || ' WITH GRANT OPTION'''||');END;';
75
76 EXECUTE IMMEDIATE l_sql_grt;
77
78
79 EXCEPTION
80
81 WHEN already_exists
82 THEN NULL;
83 igi_sls_objects_pkg.write_to_log(l_excep_level, 'create_sls_tab', 'END igi_sls_objects_pkg.create_sls_tab '||sls_tab||' TABLE ALREADY EXISTS');
84
85 WHEN OTHERS THEN
86 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
87 retcode := 2;
88 errbuf := Fnd_message.get;
89 igi_sls_objects_pkg .write_to_log(l_excep_level, 'create_sls_tab','END igi_sls_objects_pkg.create_sls_tab - failed with error ' || SQLERRM );
90
91 RETURN;
92
93 END create_sls_tab;
94
95 ---------------------------------------------------------------------------
96 -- Procedure to create index dynamically --
97 ---------------------------------------------------------------------------
98
99 PROCEDURE create_sls_inx(sls_tab IN VARCHAR2,
100 errbuf OUT NOCOPY VARCHAR2,
101 retcode OUT NOCOPY NUMBER)
102 IS
103
104 l_user varchar2(5);
105 check_ts_mode varchar2(100);
106 check_tspace_exists varchar2(100);
107 physical_tspace_name varchar2(100);
108 l_sql_inx VARCHAR2(500);
109
110 already_exists EXCEPTION;
111 col_indexed EXCEPTION;
112
113 PRAGMA EXCEPTION_INIT(already_exists, -00955);
114 PRAGMA EXCEPTION_INIT(col_indexed, -01408);
115
116 BEGIN
117
118 l_user := 'IGI';
119 errbuf :='Normal Completion';
120 retcode := 0;
121
122 ad_tspace_util.is_new_ts_mode(check_ts_mode);
123 ad_tspace_util.get_tablespace_name('IGI', 'TRANSACTION_INDEXES', 'Y', check_tspace_exists, physical_tspace_name);
124
125 If (check_ts_mode = 'Y') and (check_tspace_exists = 'Y') THEN
126
127 l_sql_inx:='BEGIN '||l_user||'.apps_ddl.apps_ddl ('||'''CREATE INDEX '||sls_tab||'_N1 ON '||l_user||'.'||sls_tab||' (SLS_SEC_GRP) TABLESPACE '||physical_tspace_name||''''||');END;';
128
129 Else
130
131 l_sql_inx:='BEGIN '||l_user||'.apps_ddl.apps_ddl ('||'''CREATE INDEX '||sls_tab||'_N1 ON '||l_user||'.'||sls_tab||' (SLS_SEC_GRP)'''||');END;';
132
133 End If;
134
135 BEGIN
136 EXECUTE IMMEDIATE l_sql_inx;
137
138 EXCEPTION
139 WHEN already_exists
140 THEN
141 NULL;
142 WHEN col_indexed
143 THEN
144 NULL;
145 END;
146
147 -- Added for Enhancement Request 2263845
148 -- Bidisha S, 14 mar 2002
149 -- Removed for bug 2257594. Instead of creating an unique index
150 -- primary key is being created for sls_rowid.
151 -- Bidisha S, 26 mar 2002
152 /*
153 l_sql_inx:='BEGIN igi.apps_ddl.apps_ddl ('||'''CREATE UNIQUE INDEX '||sls_tab||'_U1 ON igi.'||sls_tab||' (SLS_ROWID)'''||');END;';
154
155 BEGIN
156 EXECUTE IMMEDIATE l_sql_inx;
157
158 EXCEPTION
159 WHEN already_exists
160 THEN
161 NULL;
162 END;
163 */
164
165 EXCEPTION
166 WHEN OTHERS THEN
167 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
168 retcode := 2;
169 errbuf := Fnd_message.get;
170 igi_sls_objects_pkg .write_to_log(l_excep_level, 'create_sls_inx','END igi_sls_objects_pkg.create_sls_tab - failed with error ' || SQLERRM );
171
172 RETURN;
173
174 END create_sls_inx;
175
176 ------------------------------------------------------------------------------
177 -- Create APPS synonym
178 -----------------------------------------------------------------------------
179
180 PROCEDURE create_sls_apps_syn(sls_tab IN VARCHAR2,
181 schema_name IN VARCHAR2,
182 errbuf OUT NOCOPY VARCHAR2,
183 retcode OUT NOCOPY NUMBER)
184
185 IS
186
187 l_user varchar2(5);
188 l_sql_apps_syn VARCHAR2(300);
189 already_exists EXCEPTION;
190 PRAGMA EXCEPTION_INIT(already_exists, -00955);
191
192 BEGIN
193
194 l_user:='IGI';
195 errbuf :='Normal Completion';
196 retcode := 0;
197
198 l_sql_apps_syn:= 'BEGIN '||schema_name||'.apps_ddl.apps_ddl('||'''CREATE SYNONYM '||sls_tab||' FOR '||l_user||'.'||sls_tab||'''); END;';
199
200 EXECUTE IMMEDIATE l_sql_apps_syn;
201
202
203 EXCEPTION
204
205 WHEN already_exists
206 THEN NULL;
207 igi_sls_objects_pkg.write_to_log(l_excep_level, 'create_sls_apps_syn','END igi_sls_objects_pkg.create_sls_tab '||sls_tab||' SYNONYM ALREADY EXISTS ON THE '|| schema_name ||' SCHEMA');
208
209 WHEN OTHERS THEN
210 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
211 retcode := 2;
212 errbuf := Fnd_message.get;
213 igi_sls_objects_pkg.write_to_log(l_excep_level, 'create_sls_apps_syn','END igi_sls_objects_pkg.create_sls_tab - failed with error ' || SQLERRM );
214
215 RETURN;
216
217 END create_sls_apps_syn;
218
219 --------------------------------------------------------------------------
220 -- Procedure to create synonym for MLS schema --
221 --------------------------------------------------------------------------
222
223 PROCEDURE create_sls_mls_syn(sls_tab IN VARCHAR2,
224 mls_schemaname IN VARCHAR2,
225 errbuf OUT NOCOPY VARCHAR2,
226 retcode OUT NOCOPY NUMBER)
227
228 IS
229
230 l_user varchar2(5);
231 l_sql_mls_syn VARCHAR2(300);
232 already_exists EXCEPTION ;
233
234 PRAGMA EXCEPTION_INIT(already_exists, -00955);
235
236 BEGIN
237
238 l_user := 'IGI';
239 errbuf :='Normal Completion';
240 retcode := 0;
241
242 IF mls_schemaname IS NOT NULL
243 THEN l_sql_mls_syn := 'BEGIN '||mls_schemaname||'.apps_ddl.apps_ddl('||'''CREATE SYNONYM '||sls_tab||' FOR '||l_user||'.'||sls_tab||'''); END;';
244
245 EXECUTE IMMEDIATE l_sql_mls_syn;
246
247 END IF;
248
249
250 EXCEPTION
251
252 WHEN already_exists
253 THEN NULL;
254 igi_sls_objects_pkg.write_to_log(l_excep_level, 'create_sls_mls_syn','END igi_sls_objects_pkg.create_sls_tab '||sls_tab||' SYNONYM ALREADY EXISTS ON THE MLS SCHEMA');
255
256 WHEN OTHERS THEN
257 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
258 retcode := 2;
259 errbuf := Fnd_message.get;
260 igi_sls_objects_pkg .write_to_log(l_excep_level, 'create_sls_mls_syn','END igi_sls_objects_pkg.create_sls_tab - failed with error ' || SQLERRM );
261
262 RETURN;
263
264 END create_sls_mls_syn;
265
266
267 ------------------------------------------------------------------------------
268 -- Procedure to create mrc synonym dynamically
269 ------------------------------------------------------------------------------
270 PROCEDURE create_sls_mrc_syn(sls_tab IN VARCHAR2,
271 mrc_schemaname IN VARCHAR2,
272 errbuf OUT NOCOPY VARCHAR2,
273 retcode OUT NOCOPY NUMBER)
274
275 IS
276
277 l_user varchar2(5);
278 already_exists EXCEPTION;
279
280 PRAGMA EXCEPTION_INIT(already_exists, -00955);
281
282 l_sql_mrc_syn VARCHAR2(300);
283
284 BEGIN
285
286 l_user := 'IGI';
287 errbuf :='Normal Completion';
288 retcode := 0;
289
290 IF mrc_schemaname IS NOT NULL
291 THEN l_sql_mrc_syn := 'BEGIN '||mrc_schemaname||'.apps_ddl.apps_ddl('||'''CREATE SYNONYM '||sls_tab||' FOR '||l_user||'.'||sls_tab||'''); END;';
292
293 EXECUTE IMMEDIATE l_sql_mrc_syn;
294 END IF;
295
296
297 EXCEPTION
298
299 WHEN already_exists
300 THEN NULL;
301 igi_sls_objects_pkg.write_to_log(l_excep_level, 'create_sls_mrc_syn','END igi_sls_objects_pkg.create_sls_tab '||sls_tab||' SYNONYM ALREADY EXISTS ON THE MRC SCHEMA');
302
303 WHEN OTHERS THEN
304 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
305 retcode := 2;
306 errbuf := Fnd_message.get;
307 igi_sls_objects_pkg .write_to_log(l_excep_level, 'create_sls_mrc_syn','END igi_sls_objects_pkg.create_sls_tab - failed with error ' || SQLERRM );
308
309 RETURN;
310
311 END create_sls_mrc_syn;
312
313 ---------------------------------------------------------------
314 -- Procedure to drop sls table dynamically --
315 ---------------------------------------------------------------
316
317 PROCEDURE drop_sls_tab(sls_tab IN VARCHAR2,
318 errbuf OUT NOCOPY VARCHAR2,
319 retcode OUT NOCOPY NUMBER)
320 IS
321
322 l_sql_stat VARCHAR2(300);
323 no_table EXCEPTION;
324 PRAGMA EXCEPTION_INIT(no_table, -00942);
325
326 BEGIN
327
328 errbuf :='Normal Completion';
329 retcode := 0;
330
331
332 l_sql_stat:= 'BEGIN igi.apps_ddl.apps_ddl('||'''DROP TABLE '||sls_tab||''');END;';
333
334 EXECUTE IMMEDIATE l_sql_stat;
335
336
337 EXCEPTION
338
339 WHEN no_table
340 THEN NULL;
341 igi_sls_objects_pkg.write_to_log(l_excep_level, 'drop_sls_tab','END igi_sls_objects_pkg.drop_sls_tab '||sls_tab||' NO TABLE TO DROP');
342
343 WHEN OTHERS THEN
344 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
345 retcode := 2;
346 errbuf := Fnd_message.get;
347 igi_sls_objects_pkg .write_to_log(l_excep_level, 'drop_sls_tab','END igi_sls_objects_pkg.drop_sls_tab - failed with error ' || SQLERRM );
348
349 RETURN;
350
351 END drop_sls_tab;
352
353 ---------------------------------------------------------------------
354 -- Procedure to drop apps synonym dynamically
355 --------------------------------------------------------------------
356
357 PROCEDURE drop_sls_apps_syn(sls_tab IN VARCHAR2,
358 schema_name IN VARCHAR2,
359 errbuf OUT NOCOPY VARCHAR2,
360 retcode OUT NOCOPY NUMBER)
361 IS
362
363 l_sql_apps_syn VARCHAR2(300);
364
365 no_synonym EXCEPTION;
366 PRAGMA EXCEPTION_INIT(no_synonym, -01434);
367
368 BEGIN
369
370 errbuf :='Normal Completion';
371 retcode := 0;
372
373
374 l_sql_apps_syn:= 'BEGIN '||schema_name|| '.apps_ddl.apps_ddl('||'''DROP SYNONYM '||sls_tab||''');END;';
375
376 EXECUTE IMMEDIATE l_sql_apps_syn;
377
378
379 EXCEPTION
380
381 WHEN no_synonym
382 THEN NULL;
383 igi_sls_objects_pkg.write_to_log(l_excep_level, 'drop_sls_apps_syn','END igi_sls_objects_pkg.drop_sls_apps_syn '||sls_tab||' NO SYNONYM TO DROP FROM THE '|| schema_name || 'SCHEMA');
384
385 WHEN OTHERS THEN
386 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
387 retcode := 2;
388 errbuf := Fnd_message.get;
389 igi_sls_objects_pkg .write_to_log(l_excep_level, 'drop_sls_apps_syn','END igi_sls_objects_pkg.drop_sls_apps_syn - failed with error ' || SQLERRM );
390
391 RETURN;
392
393 END drop_sls_apps_syn;
394
395 ------------------------------------------------------------------
396 -- Procedure to drop mls synonym dynamically --
397 ------------------------------------------------------------------
398
399 PROCEDURE drop_sls_mls_syn(sls_tab IN VARCHAR2,
400 mls_schemaname IN VARCHAR2,
401 errbuf OUT NOCOPY VARCHAR2,
402 retcode OUT NOCOPY NUMBER)
403
404 IS
405
406 l_sql_mls_syn VARCHAR2(300);
407 no_synonym EXCEPTION;
408 PRAGMA EXCEPTION_INIT(no_synonym, -01434);
409
410 l_sql_mrc_syn VARCHAR2(300);
411
412 BEGIN
413
414 errbuf :='Normal Completion';
415 retcode := 0;
416
417 IF mls_schemaname IS NOT NULL
418 THEN l_sql_mls_syn:= 'BEGIN '||mls_schemaname||'.apps_ddl.apps_ddl('||'''DROP SYNONYM '||sls_tab||''');END;';
419
420 EXECUTE IMMEDIATE l_sql_mls_syn;
421
422 END IF;
423
424
425 EXCEPTION
426
427 WHEN no_synonym
428 THEN NULL;
429 igi_sls_objects_pkg.write_to_log(l_excep_level, 'drop_sls_mls_syn','END igi_sls_objects_pkg.drop_sls_mls_syn '||sls_tab||' NO SYNONYM TO DROP FROM THE MLS SCHEMA');
430
431 WHEN OTHERS THEN
432 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
433 retcode := 2;
434 errbuf := Fnd_message.get;
435 igi_sls_objects_pkg .write_to_log(l_excep_level, 'drop_sls_mls_syn','END igi_sls_objects_pkg. drop_sls_mls_syn - failed with error ' || SQLERRM );
436
437 RETURN;
438
439 END drop_sls_mls_syn;
440
441 ------------------------------------------------------------------
442 -- Procedure to drop mrc synonym dynamically
443 ------------------------------------------------------------------
444 PROCEDURE drop_sls_mrc_syn(sls_tab IN VARCHAR2,
445 mrc_schemaname IN VARCHAR2,
446 errbuf OUT NOCOPY VARCHAR2,
447 retcode OUT NOCOPY NUMBER)
448
449 IS
450
451 l_sql_mrc_syn VARCHAR2(300);
452
453 no_synonym EXCEPTION;
454 PRAGMA EXCEPTION_INIT(no_synonym, -01434);
455
456 BEGIN
457
458 errbuf :='Normal Completion';
459 retcode := 0;
460
461 IF mrc_schemaname IS NOT NULL
462 THEN l_sql_mrc_syn:= 'BEGIN '||mrc_schemaname||'.apps_ddl.apps_ddl('||'''DROP SYNONYM '||sls_tab||''');END;';
463
464 EXECUTE IMMEDIATE l_sql_mrc_syn;
465
466 END IF;
467
468
469 EXCEPTION
470
471 WHEN no_synonym
472 THEN NULL;
473 igi_sls_objects_pkg.write_to_log(l_excep_level, 'drop_sls_mrc_syn','END igi_sls_objects_pkg.drop_sls_mrc_syn '||sls_tab||' NO SYNONYM TO DROP FROM THE MRC SCHEMA');
474
475 WHEN OTHERS THEN
476 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
477 retcode := 2;
478 errbuf := Fnd_message.get;
479 igi_sls_objects_pkg .write_to_log(l_excep_level, 'drop_sls_mrc_syn','END igi_sls_objects_pkg.drop_sls_mrc_syn - failed with error ' || SQLERRM );
480
481 RETURN;
482
483 END drop_sls_mrc_syn;
484
485 ---------------------------------------------------------------
486 -- Procedure to Create sls trigger --
487 ---------------------------------------------------------------
488
489 PROCEDURE create_sls_trg(sls_tab IN VARCHAR2,
490 sec_tab IN VARCHAR2,
491 errbuf OUT NOCOPY VARCHAR2,
492 retcode OUT NOCOPY NUMBER)
493
494 IS
495
496 l_sql_trg VARCHAR2(5000);
497 l_user varchar2(5);
498
499 BEGIN
500
501 l_user := 'IGI';
502 errbuf :='Normal Completion';
503 retcode := 0;
504
505 -- Cannot use igi_sls_security_group_alloc because we want the trigger
506 -- to insert even if tha table is disabled.
507
508 l_sql_trg:= 'CREATE OR REPLACE TRIGGER '||sls_tab||'_TRG AFTER INSERT OR DELETE ON '||sec_tab ||' FOR EACH ROW
509
510 DECLARE
511
512 l_sec_grp VARCHAR2(30);
513 l_status VARCHAR2(5);
514 l_history VARCHAR2(30):='||'''IGI_SLS_MAINTAIN_HISTORY'''||';
515 l_value VARCHAR2(5);
516 l_valid1 NUMBER;
517 l_valid2 NUMBER;
518
519 CURSOR C1 (c_l_sec_grp VARCHAR2)IS
520 SELECT 1
521 FROM igi_sls_allocations
522 WHERE sls_group = c_l_sec_grp
523 AND sls_allocation = '||''''||sec_tab||''''||'
524 AND date_removed IS NULL ;
525
526 CURSOR C2 (c_l_sec_grp VARCHAR2)IS
527 SELECT 1
528 FROM igi_sls_allocations a,igi_sls_allocations b
529 WHERE a.sls_allocation = b.sls_group
530 AND a.sls_group = c_l_sec_grp
531 AND a.sls_group_type = '||'''S'''||'
532 AND b.sls_group_type = '||'''P'''||'
533 AND a.sls_allocation_type = '||'''P'''||'
534 AND b.sls_allocation_type = '||'''T'''||'
535 AND b.sls_allocation = '||''''||sec_tab||''''||'
536 AND a.date_removed IS NULL
537 AND b.date_removed IS NULL;
538
539 BEGIN
540 IF INSERTING THEN
541 BEGIN
542 IF SYS_CONTEXT('||'''IGI'''||','||'''SLS_RESPONSIBILITY'''||')='||'''Y'''||' THEN
543 l_sec_grp:=SYS_CONTEXT('||'''IGI'''||','||'''SLS_SECURITY_GROUP'''||');
544 IF l_sec_grp IS NOT NULL AND l_sec_grp != '||'''CEN'''||' THEN
545
546 OPEN C1(l_sec_grp);
547 FETCH C1 INTO l_valid1;
548 IF C1%NOTFOUND THEN
549 l_valid1 := 0;
550 END IF;
551 CLOSE C1;
552 IF l_valid1 = 0 THEN
553 OPEN C2(l_sec_grp);
554 FETCH C2 INTO l_valid2;
555 IF C2%NOTFOUND THEN
556 l_valid2 := 0;
557 END IF;
558 CLOSE C2;
559 END IF;
560
561 IF l_valid1 = 1 OR l_valid2 = 1 THEN
562
563 l_status:= SYS_CONTEXT('||'''IGI'''||','||'''SLS_GROUP_STATUS'''||');
564 IF l_status = '||'''N'''||' THEN
565 l_value := Nvl(FND_PROFILE.VALUE(l_history),'||'''N'''||');
566 END IF;
567 IF l_value = '||'''Y'''||' OR l_status = '||'''Y'''||' THEN
568 INSERT INTO '||l_user||'.'||sls_tab||' (SLS_ROWID, SLS_SEC_GRP)
569 VALUES(:new.ROWID,l_sec_grp);
570 END IF;
571 END IF; -- Secure Table
572 END IF; -- Not CEN Group
573 END IF; -- SLS Enabled
574 END;
575 END IF; -- Inserting
576 IF DELETING THEN
577 BEGIN
578 delete from '||l_user||'.'||sls_tab||' where sls_rowid=:old.rowid;
579 EXCEPTION
580 WHEN NO_DATA_FOUND THEN NULL;
581 END;
582 END IF;
583 END;';
584
585
586 EXECUTE IMMEDIATE l_sql_trg;
587
588
589 EXCEPTION
590 WHEN OTHERS THEN
591 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
592 retcode := 2;
593 errbuf := Fnd_message.get;
594 igi_sls_objects_pkg.write_to_log(l_excep_level, 'create_sls_trg','END igi_sls_objects_pkg.create sls trigger - failed with error ' || SQLERRM );
595
596 RETURN;
597
598 END create_sls_trg;
599
600 ---------------------------------------------------------------
601 -- Procedure drop the sls trigger --
602 ---------------------------------------------------------------
603 PROCEDURE drop_sls_trg(sls_tab IN VARCHAR2,
604 errbuf OUT NOCOPY VARCHAR2,
605 retcode OUT NOCOPY NUMBER)
606 IS
607 no_trigger EXCEPTION;
608 PRAGMA EXCEPTION_INIT(no_trigger, -04080);
609 l_sql_mrc_syn VARCHAR2(300);
610
611 BEGIN
612
613 errbuf :='Normal Completion';
614 retcode := 0;
615
616
617 EXECUTE IMMEDIATE 'DROP TRIGGER '||sls_tab||'_TRG';
618
619
620 EXCEPTION
621
622 WHEN no_trigger
623 THEN NULL;
624 igi_sls_objects_pkg.write_to_log(l_excep_level, 'drop_sls_trg','END igi_sls_objects_pkg.drop sls trigger'||sls_tab||'_TRG - NO TRIGGER TO DROP');
625 WHEN OTHERS THEN
626 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
627 retcode := 2;
628 errbuf := Fnd_message.get;
629 igi_sls_objects_pkg.write_to_log(l_excep_level, 'drop_sls_trg','END igi_sls_objects_pkg.drop sls trigger - failed with error ' || SQLERRM );
630
631 RETURN;
632
633 END drop_sls_trg;
634
635 -----------------------------------------------------------------------------
636 -- Procedure to create the policy function --
637 -----------------------------------------------------------------------------
638
639
640 PROCEDURE cre_pol_function(sec_tab IN VARCHAR2,
641 sls_tab IN VARCHAR2,
642 errbuf OUT NOCOPY VARCHAR2,
643 retcode OUT NOCOPY NUMBER)
644 IS
645
646 l_sql_stat VARCHAR2(2000);
647 l_sql_grant VARCHAR2(100);
648
649 BEGIN
650
651 errbuf :='Normal Completion';
652 retcode := 0;
653
654
655 l_sql_stat:=
656 'CREATE OR REPLACE FUNCTION '||sls_tab||'_FUN (D1 VARCHAR2, D2 VARCHAR2) RETURN VARCHAR2 AS
657 l_valid NUMBER;
658 d_predicate VARCHAR2(2000) := NULL;
659 l_enable VARCHAR2(10);
660 l_sls_security_group VARCHAR2(30);
661 l_status VARCHAR2(5);
662 l_sls_responsibility VARCHAR2(50);
663
664 CURSOR c1 (c_sls_security_group varchar2)
665 IS
666 SELECT 1 FROM igi_sls_security_group_alloc
667 WHERE table_name = '||''''||sec_tab||''''||'
668 AND sls_security_group = c_sls_security_group;
669
670 BEGIN
671
672 l_enable := NVL(SYS_CONTEXT('||'''IGI'''||','||'''SLS_RESPONSIBILITY'''||'),'||'''N'''||');
673
674 IF l_enable = '||'''Y'''||' THEN
675 l_sls_security_group :=SYS_CONTEXT('||'''IGI'''||','||'''SLS_SECURITY_GROUP'''||');
676 l_status:=SYS_CONTEXT('||'''IGI'''||','||'''SLS_GROUP_STATUS'''||');
677
678 IF l_status = '||'''Y'''||' THEN
679
680 IF l_sls_security_group != '||'''CEN'''||' and l_sls_security_group is not null THEN
681 OPEN c1(l_sls_security_group);
682 FETCH c1 INTO l_valid;
683 IF c1%NOTFOUND THEN
684 l_valid:= 0;
685 END IF;
686 CLOSE c1;';
687
688 IF sec_tab = 'AR_PAYMENT_SCHEDULES_ALL' THEN
689 l_sql_stat := l_sql_stat ||
690 'IF l_valid = 1 THEN
691 d_predicate:='||''' (payment_schedule_id < 0 OR ROWID = (SELECT SLS_ROWID FROM '||sls_tab||' WHERE sls_rowid = '||sec_tab||'.rowid and sls_sec_grp = '||'''||''''''''||l_sls_security_group||''''''''||'''||'))'';
692 END IF;';
693 ELSE
694 l_sql_stat := l_sql_stat ||
695 'IF l_valid = 1 THEN
696 d_predicate:='||''' ROWID = (SELECT SLS_ROWID FROM '||sls_tab||' WHERE sls_rowid = '||sec_tab||'.rowid and sls_sec_grp = '||'''||''''''''||l_sls_security_group||''''''''||'''||')'';
697 END IF;';
698 END IF;
699
700
701
702 l_sql_stat := l_sql_stat ||
703 ' ELSIF l_sls_security_group IS NULL THEN
704 d_predicate:= '||'''ROWNUM < 1'''||';
705 ELSIF l_sls_security_group = '||'''CEN'''||' THEN
706 d_predicate:= NULL;
707 END IF;
708 ELSE
709 d_predicate:='||'''ROWNUM < 1'''||';
710 END IF;
711 END IF;
712 RETURN d_predicate;
713 END;';
714
715
716 l_sql_grant:= 'GRANT EXECUTE ON '||sls_tab||'_FUN to PUBLIC';
717
718
719 EXECUTE IMMEDIATE l_sql_stat;
720 EXECUTE IMMEDIATE l_sql_grant;
721
722
723 EXCEPTION
724
725 WHEN OTHERS THEN
726 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
727 retcode := 2;
728 errbuf := Fnd_message.get;
729 igi_sls_objects_pkg .write_to_log(l_excep_level, 'cre_pol_function','END igi_sls_objects_pkg.create policy function - failed with error ' || SQLERRM );
730
731 RETURN;
732
733 END cre_pol_function;
734
735 ---------------------------------------------------------------
736 -- Now do the drop policy function procedure --
737 ---------------------------------------------------------------
738 PROCEDURE drop_pol_function(sls_tab IN VARCHAR2,
739 errbuf OUT NOCOPY VARCHAR2,
740 retcode OUT NOCOPY NUMBER)
741 IS
742
743 sql_stat VARCHAR2(2000);
744
745 no_function EXCEPTION;
746 PRAGMA EXCEPTION_INIT(no_function, -04043);
747
748 BEGIN
749
750 errbuf :='Normal Completion';
751 retcode := 0;
752
753
754 sql_stat:='DROP FUNCTION '||sls_tab||'_FUN';
755
756 EXECUTE IMMEDIATE sql_stat;
757
758
759 EXCEPTION
760
761 WHEN no_function
762 THEN NULL;
763 igi_sls_objects_pkg.write_to_log(l_excep_level, 'drop_pol_function','END igi_sls_objects_pkg.drop_pol_function'||sls_tab||'NO FUNCTION TO DROP');
764
765 WHEN OTHERS THEN
766 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
767 retcode := 2;
768 errbuf := Fnd_message.get;
769 igi_sls_objects_pkg .write_to_log(l_excep_level, 'drop_pol_function','END igi_sls_objects_pkg.drop_pol_function - failed with error ' || SQLERRM );
770
771 RETURN;
772
773 END drop_pol_function;
774
775 --------------------------------------------------------------
776 ---- Now do the Add policy procedure --
777 --------------------------------------------------------------
778
779 PROCEDURE sls_add_pol(object_schema IN VARCHAR2,
780 table_name IN VARCHAR2,
781 policy_name IN VARCHAR2,
782 function_owner IN VARCHAR2,
783 policy_function IN VARCHAR2,
784 statement_types IN VARCHAR2,
785 errbuf OUT NOCOPY VARCHAR2,
786 retcode OUT NOCOPY NUMBER)
787 IS
788
789 policy_exists EXCEPTION;
790 PRAGMA EXCEPTION_INIT(policy_exists, -28101);
791
792 BEGIN
793
794 errbuf :='Normal Completion';
795 retcode := 0;
796
797 DBMS_RLS.ADD_POLICY (object_schema,
798 table_name,
799 policy_name,
800 function_owner,
801 policy_function,
802 statement_types);
803
804
805 EXCEPTION
806
807 WHEN policy_exists
808 THEN NULL;
809 igi_sls_objects_pkg.write_to_log(l_excep_level, 'sls_add_pol','END igi_sls_objects_pkg.sls_add_pol '||table_name||'POLICY ALREADY EXISTS ON THIS TABLE');
810
811 WHEN OTHERS THEN
812 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
813 retcode := 2;
814 errbuf := Fnd_message.get;
815 igi_sls_objects_pkg.write_to_log(l_excep_level, 'sls_add_pol','END igi_sls_objects_pkg.sls_add_pol - failed with error ' || SQLERRM );
816
817 RETURN;
818
819 END sls_add_pol;
820
821 ------------------------------------------------------------------
822 -- Now add the drop policy procedure --
823 ------------------------------------------------------------------
824
825 PROCEDURE sls_drop_pol (object_schema IN VARCHAR2,
826 table_name IN VARCHAR2,
827 policy_name IN VARCHAR2,
828 errbuf OUT NOCOPY VARCHAR2,
829 retcode OUT NOCOPY NUMBER)
830 IS
831 no_policy EXCEPTION;
832 PRAGMA EXCEPTION_INIT(no_policy,-28102);
833
834 BEGIN
835
836 errbuf :='Normal Completion';
837 retcode := 0;
838
839 DBMS_RLS.DROP_POLICY (object_schema,
840 table_name,
841 policy_name);
842
843
844 EXCEPTION
845
846 WHEN no_policy THEN NULL;
847
848 WHEN OTHERS THEN
849 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
850 retcode := 2;
851 errbuf := Fnd_message.get;
852 igi_sls_objects_pkg.write_to_log(l_excep_level, 'sls_drop_pol','END igi_sls_objects_pkg.sls_drop_pol - failed with error ' || SQLERRM );
853
854 RETURN;
855
856 END sls_drop_pol;
857
858 ---------------------------------------------------------------
859 -- Now add the refresh policy --
860 ---------------------------------------------------------------
861
862 PROCEDURE sls_refresh_pol(object_schema IN VARCHAR2,
863 table_name IN VARCHAR2,
864 policy_name IN VARCHAR2,
865 errbuf OUT NOCOPY VARCHAR2,
866 retcode OUT NOCOPY NUMBER)
867 IS
868
869 no_policy EXCEPTION;
870 PRAGMA EXCEPTION_INIT(no_policy,-28102);
871
872 BEGIN
873
874 errbuf :='Normal Completion';
875 retcode := 0;
876
877 DBMS_RLS.REFRESH_POLICY (object_schema,
878 table_name,
879 policy_name);
880
881
882
883 EXCEPTION
884 WHEN OTHERS THEN
885 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
886 retcode := 2;
887 errbuf := Fnd_message.get;
888 igi_sls_objects_pkg.write_to_log(l_excep_level, 'sls_refresh_pol','END igi_sls_objects_pkg.sls_refresh_pol - failed with error ' || SQLERRM );
889
890 RETURN;
891
892 END sls_refresh_pol;
893
894 --------------------------------------------------------------
895 -- IF ACTION IS ENABLE THEN ENABLE THE POLICY --
896 --------------------------------------------------------------
897
898 PROCEDURE sls_enable_pol(object_schema IN VARCHAR2,
899 table_name IN VARCHAR2,
900 policy_name IN VARCHAR2,
901 enable IN BOOLEAN,
902 errbuf OUT NOCOPY VARCHAR2,
903 retcode OUT NOCOPY NUMBER)
904 IS
905
906 BEGIN
907
908 errbuf :='Normal Completion';
909 retcode := 0;
910
911 DBMS_RLS.ENABLE_POLICY (object_schema,
912 table_name,
913 policy_name,
914 enable);
915
916
917 EXCEPTION
918 WHEN OTHERS THEN
919 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
920 retcode := 2;
921 errbuf := Fnd_message.get;
922 igi_sls_objects_pkg.write_to_log(l_excep_level, 'sls_enable_pol','END igi_sls_objects_pkg.sls_enable_pol - failed with error ' || SQLERRM );
923
924 RETURN;
925
926
927 END sls_enable_pol;
928
929 ----------------------------------------------------------
930 -- Now do the disable policy procedure --
931 ----------------------------------------------------------
932
933 PROCEDURE sls_disable_pol
934 (object_schema IN VARCHAR2,
935 table_name IN VARCHAR2,
936 policy_name IN VARCHAR2,
937 enable IN BOOLEAN,
938 errbuf OUT NOCOPY VARCHAR2,
939 retcode OUT NOCOPY NUMBER)
940 IS
941
942 no_policy EXCEPTION;
943 PRAGMA EXCEPTION_INIT(no_policy,-28102);
944
945 BEGIN
946
947 errbuf :='Normal Completion';
948 retcode := 0;
949
950 DBMS_RLS.ENABLE_POLICY (object_schema,
951 table_name,
952 policy_name,
953 FALSE);
954
955 EXCEPTION
956 WHEN no_policy
957 THEN NULL;
958
959 WHEN OTHERS THEN
960 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
961 retcode := 2;
962 errbuf := Fnd_message.get;
963 igi_sls_objects_pkg.write_to_log(l_excep_level, 'sls_disable_pol','END igi_sls_objects_pkg.sls_disable_pol - failed with error ' || SQLERRM );
964
965 RETURN;
966
967 END sls_disable_pol;
968
969 -- Start of alernate solution Code
970 -- Bidisha , 27 Mar 2002
971 PROCEDURE create_sls_col (sec_tab IN VARCHAR,
972 schema_name IN VARCHAR2,
973 errbuf OUT NOCOPY VARCHAR2,
974 retcode OUT NOCOPY NUMBER)
975 IS
976 l_sql_stat varchar2(300);
977 already_exists EXCEPTION;
978 PRAGMA EXCEPTION_INIT(already_exists, -01430);
979
980
981 BEGIN
982
983 errbuf :='Normal Completion';
984 retcode := 0;
985
986 l_sql_stat := 'BEGIN '||schema_name||'.apps_ddl.apps_ddl('||'''ALTER TABLE '||schema_name||'.'||sec_tab||' ADD (IGI_SLS_SEC_GROUP VARCHAR2(30))'''||');END;';
987
988 EXECUTE IMMEDIATE l_sql_stat;
989
990
991 EXCEPTION
992
993 WHEN already_exists
994 THEN
995 NULL;
996
997 WHEN OTHERS
998 THEN
999 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
1000 retcode := 2;
1001 errbuf := Fnd_message.get;
1002 igi_sls_objects_pkg .write_to_log(l_excep_level, 'create_sls_col','END igi_sls_objects_pkg.create_sls_col - failed with error ' || SQLERRM );
1003
1004 RETURN;
1005
1006 END create_sls_col;
1007
1008 PROCEDURE create_sls_core_inx
1009 (sec_tab IN VARCHAR2,
1010 sls_tab IN VARCHAR2,
1011 schema_name IN VARCHAR2,
1012 errbuf OUT NOCOPY VARCHAR2,
1013 retcode OUT NOCOPY NUMBER)
1014
1015 IS
1016
1017 check_ts_mode varchar2(100);
1018 check_tspace_exists varchar2(100);
1019 physical_tspace_name varchar2(100);
1020 l_app_short_name varchar2(100);
1021 l_app_id number;
1022 l_sql_inx VARCHAR2(500);
1023 already_exists EXCEPTION;
1024 col_indexed EXCEPTION;
1025 PRAGMA EXCEPTION_INIT(already_exists, -00955);
1026 PRAGMA EXCEPTION_INIT(col_indexed, -01408);
1027
1028 BEGIN
1029
1030 errbuf :='Normal Completion';
1031 retcode := 0;
1032
1033 select fpi.application_id
1034 into l_app_id
1035 from fnd_product_installations fpi, fnd_oracle_userid foui
1036 where foui.oracle_id = fpi.oracle_id
1037 and foui.oracle_username = schema_name;
1038
1039 ad_tspace_util.is_new_ts_mode(check_ts_mode);
1040 l_app_short_name := ad_tspace_util.get_product_short_name(l_app_id);
1041 ad_tspace_util.get_tablespace_name(l_app_short_name, 'TRANSACTION_INDEXES', 'Y', check_tspace_exists, physical_tspace_name);
1042
1043 If (check_ts_mode = 'Y') and (check_tspace_exists = 'Y') THEN
1044
1045 l_sql_inx:= 'BEGIN '||schema_name||'.apps_ddl.apps_ddl ('||'''CREATE INDEX '||sls_tab||'_GRP_N1 ON '||schema_name||'.'||sec_tab||' (IGI_SLS_SEC_GROUP) TABLESPACE '||physical_tspace_name||''''||');END;';
1046
1047 Else
1048
1049 l_sql_inx:= 'BEGIN '||schema_name||'.apps_ddl.apps_ddl ('||'''CREATE INDEX '||sls_tab||'_GRP_N1 ON '||schema_name||'.'||sec_tab||' (IGI_SLS_SEC_GROUP)'''||');END;';
1050
1051 End If;
1052
1053 EXECUTE IMMEDIATE l_sql_inx;
1054
1055 EXCEPTION
1056 WHEN already_exists
1057 THEN
1058 NULL;
1059
1060 WHEN col_indexed
1061 THEN
1062 NULL;
1063 WHEN OTHERS THEN
1064 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
1065 retcode := 2;
1066 errbuf := Fnd_message.get;
1067 igi_sls_objects_pkg .write_to_log(l_excep_level, 'create_sls_core_inx','END igi_sls_objects_pkg.create_sls_core_inx - failed with error ' || SQLERRM );
1068
1069 RETURN;
1070
1071 END create_sls_core_inx;
1072
1073 PROCEDURE drop_sls_col (sec_tab IN VARCHAR,
1074 schema_name IN VARCHAR2,
1075 errbuf OUT NOCOPY VARCHAR2,
1076 retcode OUT NOCOPY NUMBER)
1077 IS
1078 l_sql_stat VARCHAR2(300);
1079 no_column EXCEPTION;
1080 PRAGMA EXCEPTION_INIT(no_column, -00904);
1081
1082 BEGIN
1083
1084 errbuf :='Normal Completion';
1085 retcode := 0;
1086
1087 l_sql_stat:= 'BEGIN '||schema_name||'.apps_ddl.apps_ddl('||'''ALTER TABLE '||schema_name||'.'||sec_tab||' DROP (IGI_SLS_SEC_GROUP)'');END;';
1088
1089 EXECUTE IMMEDIATE l_sql_stat;
1090
1091
1092 EXCEPTION
1093
1094 WHEN no_column
1095 THEN NULL;
1096
1097 WHEN OTHERS
1098 THEN
1099 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
1100 retcode := 2;
1101 errbuf := Fnd_message.get;
1102 igi_sls_objects_pkg .write_to_log(l_excep_level, 'drop_sls_col','END igi_sls_objects_pkg.drop_sls_col - failed with error ' || SQLERRM );
1103
1104 RETURN;
1105
1106 END drop_sls_col;
1107
1108 ---------------------------------------------------------------
1109 -- Procedure to Create sls trigger --
1110 ---------------------------------------------------------------
1111
1112 PROCEDURE create_sls_col_trg(sls_tab IN VARCHAR2,
1113 sec_tab IN VARCHAR2,
1114 errbuf OUT NOCOPY VARCHAR2,
1115 retcode OUT NOCOPY NUMBER)
1116
1117 IS
1118 l_user varchar2(5);
1119 l_sql_trg VARCHAR2(5000);
1120
1121 BEGIN
1122
1123 l_user := 'IGI';
1124 errbuf :='Normal Completion';
1125 retcode := 0;
1126
1127
1128 l_sql_trg:= 'CREATE OR REPLACE TRIGGER '||sls_tab||'_TRG BEFORE INSERT OR DELETE ON '||sec_tab ||' FOR EACH ROW
1129
1130 DECLARE
1131
1132 l_sec_grp VARCHAR2(30);
1133 l_status VARCHAR2(5);
1134 l_history VARCHAR2(30):='||'''IGI_SLS_MAINTAIN_HISTORY'''||';
1135 l_value VARCHAR2(5);
1136 l_valid1 NUMBER;
1137 l_valid2 NUMBER;
1138
1139 CURSOR C1 (c_l_sec_grp VARCHAR2)IS
1140 SELECT 1
1141 FROM igi_sls_allocations
1142 WHERE sls_group = c_l_sec_grp
1143 AND sls_allocation = '||''''||sec_tab||''''||'
1144 AND date_removed IS NULL ;
1145
1146 CURSOR C2 (c_l_sec_grp VARCHAR2)IS
1147 SELECT 1
1148 FROM igi_sls_allocations a,igi_sls_allocations b
1149 WHERE a.sls_allocation = b.sls_group
1150 AND a.sls_group = c_l_sec_grp
1151 AND a.sls_group_type = '||'''S'''||'
1152 AND b.sls_group_type = '||'''P'''||'
1153 AND a.sls_allocation_type = '||'''P'''||'
1154 AND b.sls_allocation_type = '||'''T'''||'
1155 AND b.sls_allocation = '||''''||sec_tab||''''||'
1156 AND a.date_removed IS NULL
1157 AND b.date_removed IS NULL;
1158 BEGIN
1159 IF INSERTING THEN
1160 BEGIN
1161 IF SYS_CONTEXT('||'''IGI'''||','||'''SLS_RESPONSIBILITY'''||')='||'''Y'''||' THEN
1162 l_sec_grp:=SYS_CONTEXT('||'''IGI'''||','||'''SLS_SECURITY_GROUP'''||');
1163 IF l_sec_grp IS NOT NULL AND l_sec_grp != '||'''CEN'''||' THEN
1164 OPEN C1(l_sec_grp);
1165 FETCH C1 INTO l_valid1;
1166 IF C1%NOTFOUND THEN
1167 l_valid1 := 0;
1168 END IF;
1169 CLOSE C1;
1170 IF l_valid1 = 0 THEN
1171 OPEN C2(l_sec_grp);
1172 FETCH C2 INTO l_valid2;
1173 IF C2%NOTFOUND THEN
1174 l_valid2 := 0;
1175 END IF;
1176 CLOSE C2;
1177 END IF;
1178 IF l_valid1 = 1 OR l_valid2 = 1 THEN
1179
1180 l_status:= SYS_CONTEXT('||'''IGI'''||','||'''SLS_GROUP_STATUS'''||');
1181 IF l_status = '||'''N'''||' THEN
1182 l_value := Nvl(FND_PROFILE.VALUE(l_history),'||'''N'''||');
1183 END IF;
1184 IF l_value = '||'''Y'''||' OR l_status = '||'''Y'''||' THEN
1185 :NEW.IGI_SLS_SEC_GROUP := l_sec_grp;
1186 END IF;
1187 END IF; -- Secure Table
1188 END IF; -- Not CEN group
1189 END IF; -- SLS Enabled / SLS Responsibility
1190 END;
1191 END IF; -- If Inserting
1192 IF DELETING THEN
1193 BEGIN
1194 delete from '||l_user||'.'||sls_tab||' where sls_rowid=:old.rowid;
1195 EXCEPTION
1196 WHEN NO_DATA_FOUND THEN NULL;
1197 WHEN OTHERS THEN NULL;
1198 END;
1199 END IF;
1200 END;'; -- If deleting
1201
1202
1203 EXECUTE IMMEDIATE l_sql_trg;
1204
1205
1206 EXCEPTION
1207 WHEN OTHERS THEN
1208 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
1209 retcode := 2;
1210 errbuf := Fnd_message.get;
1211 igi_sls_objects_pkg.write_to_log(l_excep_level, 'create_sls_col_trg','END igi_sls_objects_pkg.create sls col trigger - failed with error ' || SQLERRM );
1212
1213 RETURN;
1214
1215 END create_sls_col_trg;
1216
1217 PROCEDURE cre_ext_col_pol_func(sec_tab IN VARCHAR2,
1218 sls_tab IN VARCHAR2,
1219 errbuf OUT NOCOPY VARCHAR2,
1220 retcode OUT NOCOPY NUMBER)
1221
1222 IS
1223
1224 l_sql_stat VARCHAR2(2000);
1225 l_sql_grant VARCHAR2(100);
1226
1227 BEGIN
1228
1229 errbuf :='Normal Completion';
1230 retcode := 0;
1231
1232
1233 l_sql_stat :=
1234 'CREATE OR REPLACE FUNCTION '||sls_tab||'_FUN (D1 VARCHAR2, D2 VARCHAR2)
1235 RETURN VARCHAR2 AS
1236 l_valid NUMBER;
1237 d_predicate VARCHAR2(2000) := NULL;
1238 l_enable VARCHAR2(10);
1239 l_sls_security_group VARCHAR2(30);
1240 l_status VARCHAR2(5);
1241 l_sls_responsibility VARCHAR2(50);
1242
1243 CURSOR c1 (c_sls_security_group varchar2)
1244 IS
1245 SELECT 1
1246 FROM igi_sls_security_group_alloc
1247 WHERE table_name = '||''''||sec_tab||''''||'
1248 AND sls_security_group = c_sls_security_group;
1249
1250 BEGIN
1251
1252 l_enable := NVL(SYS_CONTEXT('||'''IGI'''||','||'''SLS_RESPONSIBILITY'''||'),'||'''N'''||');
1253
1254 IF l_enable = '||'''Y'''||' THEN
1255
1256 l_sls_security_group :=SYS_CONTEXT('||'''IGI'''||','||'''SLS_SECURITY_GROUP'''||');
1257 l_status:=SYS_CONTEXT('||'''IGI'''||','||'''SLS_GROUP_STATUS'''||');
1258
1259 IF l_status = '||'''Y'''||' THEN
1260
1261 IF l_sls_security_group != '||'''CEN'''||' and l_sls_security_group is not null THEN
1262 OPEN c1(l_sls_security_group);
1263 FETCH c1 INTO l_valid;
1264 IF c1%NOTFOUND THEN
1265 l_valid:= 0;
1266 END IF;
1267 CLOSE c1;
1268 ';
1269
1270
1271 IF sec_tab = 'AR_PAYMENT_SCHEDULES_ALL' THEN
1272 l_sql_stat := l_sql_stat ||
1273 'IF l_valid = 1 THEN
1274 d_predicate:= '||''' (payment_schedule_id < 0 OR IGI_SLS_SEC_GROUP = '''||'||''''''''||l_sls_security_group||''''''''||'||''')'''||' ;
1275 END IF;
1276 ';
1277 ELSE
1278 l_sql_stat := l_sql_stat ||
1279 'IF l_valid = 1 THEN
1280 d_predicate:='||''' IGI_SLS_SEC_GROUP = '||'''||''''''''||l_sls_security_group||'''''''';
1281 END IF;
1282 ';
1283 END IF;
1284
1285 l_sql_stat := l_sql_stat ||
1286 ' ELSIF l_sls_security_group IS NULL THEN
1287 d_predicate:= '||'''ROWNUM < 1'''||';
1288 ELSIF l_sls_security_group = '||'''CEN'''||' THEN
1289 d_predicate:= NULL;
1290 END IF;
1291 ELSE
1292 d_predicate:='||'''ROWNUM < 1'''||';
1293 END IF;
1294 END IF;
1295
1296 RETURN d_predicate;
1297 END;';
1298
1299 l_sql_grant:= 'GRANT EXECUTE ON '||sls_tab||'_FUN to PUBLIC';
1300
1301 EXECUTE IMMEDIATE l_sql_stat;
1302 EXECUTE IMMEDIATE l_sql_grant;
1303
1304 EXCEPTION
1305
1306 WHEN OTHERS THEN
1307 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
1308 retcode := 2;
1309 errbuf := Fnd_message.get;
1310 igi_sls_objects_pkg .write_to_log(l_excep_level, 'cre_ext_col_pol_func','END igi_sls_objects_pkg.create policy function - failed with error ' || SQLERRM );
1311
1312 RETURN;
1313
1314
1315 END cre_ext_col_pol_func;
1316
1317 END igi_sls_objects_pkg;