1 PACKAGE BODY IGS_SC_GRANTS_PVT AS
2 /* $Header: IGSSC01B.pls 120.11 2006/01/27 00:16:18 skpandey noship $ */
3
4 /******************************************************************
5
6 Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
7 All rights reserved.
8
9 Created By : Arkadi Tereshenkov
10
11 Date Created By : Oct-01-2002
12
13 Purpose : Grant processing package
14
15 remarks : None
16
17 Change History
18
19 Who When What
20 -----------------------------------------------------------
21 Arkadi Tereshenkov Apr-10-2002 New Package created.
22 mmkumar 05-Jul-2005 Changed the build grant
23 gmaheswa 26-Jul-2005 Fnd Logging
24 ******************************************************************/
25
26
27 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGS_SC_GRANTS_PVT';
28 g_debug_level NUMBER(1) := 0;
29 g_user_orig_system CONSTANT VARCHAR2(25) := 'FND_USR';
30 g_alias_number NUMBER(5) :=0;
31 g_user_id NUMBER(14);
32 g_current_user_id NUMBER(15);
33 g_current_party_id NUMBER(15);
34
35 --code added by mmkumar
36 l_grant_text VARCHAR2(400);
37 l_bodmas_grant_text VARCHAR2(400);
38 p_current_type VARCHAR2(20) := 'START';
39 p_previous_type VARCHAR2(20) := 'START';
40 l_string VARCHAR2(400) :='';
41 l_final_result VARCHAR2(400) := ' ';
42 l_operator_temp VARCHAR2(10) := '';
43 onlyZTypeAttributes BOOLEAN := true;
44 --code added by mmkumar ends
45
46 l_prog_label CONSTANT VARCHAR2(500) :='igs.plsql.igs_sc_grants_pvt';
47 l_label VARCHAR2(4000);
48 l_debug_str VARCHAR2(32000);
49
50 TYPE g_grant_cond_rec IS RECORD (
51 obj_attrib_id igs_sc_grant_conds.obj_attrib_id%TYPE,
52 user_attrib_id igs_sc_grant_conds.user_attrib_id%TYPE,
53 condition igs_sc_grant_conds.condition%TYPE,
54 text_value igs_sc_grant_conds.text_value%TYPE,
55 user_attrib_value igs_sc_usr_att_vals.attr_value%TYPE,
56 obj_attrib_value igs_sc_obj_att_vals.attr_value%TYPE,
57 cond_text VARCHAR2(2000),
58 z_typ_flag VARCHAR2(1) --mmkumar
59 );
60
61
62 TYPE g_grant_conds_t IS TABLE OF g_grant_cond_rec INDEX BY BINARY_INTEGER;
63
64
65
66 TYPE g_object_rec IS RECORD
67 ( obj_group_id igs_sc_objects.obj_group_id%TYPE,
68 object_id igs_sc_objects.object_id%TYPE,
69 table_name fnd_objects.database_object_name%TYPE,
70 default_policy_type igs_sc_obj_groups.default_policy_type%TYPE,
71 pk1_column_name fnd_objects.pk1_column_name%TYPE,
72 pk2_column_name fnd_objects.pk2_column_name%TYPE,
73 pk3_column_name fnd_objects.pk3_column_name%TYPE,
74 pk4_column_name fnd_objects.pk4_column_name%TYPE,
75 pk5_column_name fnd_objects.pk5_column_name%TYPE,
76 pk1_column_type fnd_objects.pk1_column_type%TYPE,
77 pk2_column_type fnd_objects.pk2_column_type%TYPE,
78 pk3_column_type fnd_objects.pk3_column_type%TYPE,
79 pk4_column_type fnd_objects.pk4_column_type%TYPE,
80 pk5_column_type fnd_objects.pk5_column_type%TYPE
81
82 );
83
84 TYPE g_grant_rec IS RECORD
85 ( grant_id igs_sc_grants.grant_id%TYPE,
86 grant_text igs_sc_grants.grant_text%TYPE
87 );
88
89
90
91 PROCEDURE Put_Log_Msg (
92 p_message IN VARCHAR2,
93 p_level IN NUMBER
94 );
95
96 PROCEDURE insert_user_attrib(
97 p_user_id IN NUMBER ,
98 p_party_id IN NUMBER ,
99 p_user_attrib_id IN NUMBER ,
100 p_user_attrib_name IN VARCHAR2,
101 p_user_attrib_type IN VARCHAR2,
102 p_static_type IN VARCHAR2,
103 p_select_text IN VARCHAR2
104 );
105
106
107 PROCEDURE build_grant(
108 p_group_rec IN OUT NOCOPY g_object_rec,
109 p_grants_rec IN OUT NOCOPY g_grant_rec
110 ) ;
111
112 --added by mmkumar
113 FUNCTION isSingleGrantCond(p_grant_text VARCHAR2) RETURN BOOLEAN
114 IS
115 BEGIN
116 IF (INSTR(UPPER(p_grant_text),'OR') > 0 OR INSTR(UPPER(p_grant_text),'AND') > 0) THEN
117 RETURN false;
118 ELSE
119 RETURN true;
120 END IF;
121 END isSingleGrantCond;
122
123 FUNCTION get_alias
124 RETURN VARCHAR2
125 IS
126 BEGIN
127
128 g_alias_number :=g_alias_number+1;
129 RETURN 'sc'||g_alias_number;
130
131 END get_alias;
132
133 --added by mmkumar
134 FUNCTION isAnotherOperand(p_cond VARCHAR2, loc NUMBER) RETURN BOOLEAN IS
135 BEGIN
136 IF INSTR(SUBSTR(p_cond,loc), ':') >= 1 THEN
137 RETURN true;
138 ELSE
139 RETURN false;
140 END IF;
141 END isAnotherOperand;
142
143 --added by mmkumar
144 PROCEDURE handler(p_char VARCHAR2,p_cond VARCHAR2, loc NUMBER) is
145 p_temp VARCHAR2(10) := '';
146 BEGIN
147 IF p_char IN ('A' , 'N' , 'D' , 'O' , 'R') THEN
148 IF p_char IN ('A', 'O') THEN
149 l_operator_temp := '';
150 END IF;
151 l_operator_temp := l_operator_temp || p_char;
152 ELSE
153 IF p_char IN (':','(') THEN
154 if TRIM(l_operator_temp) IN ('AND','OR') and p_current_type = 'OPERAND' then
155 IF isAnotherOperand(l_string,loc) THEN
156 l_final_result := l_final_result || ' ' || l_operator_temp || ' ';
157 p_previous_type := p_current_type;
158 p_current_type := 'OPERATOR';
159 END IF;
160 end if;
161 l_operator_temp := '';
162 IF p_char = ':' THEN
163 p_previous_type := p_current_type;
164 p_current_type := 'OPERAND';
165 END IF;
166 l_final_result := l_final_result || p_char;
167 ELSIF p_char <> ' ' THEN
168 l_final_result := l_final_result || p_char;
169 END IF;
170 END IF;
171
172 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
173 l_label := 'igs.plsql.igs_sc_grants_pvt.handler.end';
174 l_debug_str := 'Final Result :'|| l_final_result;
175 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
176 END IF;
177
178 END handler;
179
180 --added by mmkumar
181 FUNCTION getBodmasCondition(p_cond VARCHAR2) RETURN VARCHAR2
182 IS
183 --l_string VARCHAR2(400);
184 l_current_index number := 1;
185 l_single_char VARCHAR2(1) := 'Q';
186 l_current_type VARCHAR2(20) := 'START';
187 l_result VARCHAR2(400);
188 BEGIN
189 l_final_result := '';
190 --l_string := REPLACE(UPPER(p_cond),':Z','');
191 l_string := UPPER((p_cond));
192 l_single_char := SUBSTR(l_string,l_current_index,1);
193 l_operator_temp := '';
194 p_current_type := 'START';
195 p_previous_type := 'START';
196
197 LOOP
198 IF l_single_char = '' OR l_single_char = 'Q' or l_single_char is null THEN
199 EXIT;
200 END IF;
201 handler(l_single_char,l_string,l_current_index);
202 l_current_index := l_current_index + 1;
203 l_single_char := SUBSTR(l_string,l_current_index,1);
204 END LOOP;
205 return replace(l_final_result,'()','');
206 END getBodmasCondition;
207
208
209 PROCEDURE set_ctx(
210 p_name VARCHAR2,
211 p_val VARCHAR2
212 )
213 IS
214 BEGIN
215 dbms_session.set_context( 'igsscctx', p_name, p_val );
216 END set_ctx;
217
218 -- This function checks if we need to transform operation for user attribute to the opposite
219
220 FUNCTION check_operation (p_operation VARCHAR2
221 ) RETURN VARCHAR2
222 IS
223 l_operation VARCHAR2(25);
224
225 BEGIN
226
227 l_operation:= p_operation;
228
229 IF p_operation = '>' THEN
230 l_operation := '<';
231
232 ELSIF p_operation ='<' THEN
233 l_operation := '>';
234
235 ELSIF p_operation = '>=' THEN
236 l_operation := '<=';
237
238 ELSIF p_operation = '<=' THEN
239 l_operation := '>=';
240 END IF;
241
242 RETURN l_operation;
243
244 END check_operation;
245
246
247 FUNCTION replace_string(
248 p_string IN VARCHAR2,
249 p_from_pattern IN VARCHAR2,
250 p_to_pattern IN VARCHAR2
251 ) RETURN VARCHAR2
252 IS
253
254 l_out_string VARCHAR2(4000);
255 l_upper_string VARCHAR2(4000);
256 l_occurence NUMBER(10) := 0;
257 l_len NUMBER(5);
258
259 BEGIN
260
261 IF upper(p_from_pattern) = upper(p_to_pattern) THEN
262
263 --check for being the same value, infinite loop
264 RETURN p_string;
265
266 END IF;
267
268 -- delete all values for the current user
269
270 l_out_string := p_string;
271 l_upper_string := UPPER(l_out_string);
272
273 l_len := length(p_from_pattern);
274
275 l_occurence := INSTR(l_upper_string,p_from_pattern,1,1);
276
277 LOOP
278
279 IF l_occurence = 0 THEN
280 -- no more found exit
281 EXIT;
282
283 END IF;
284
285 l_out_string := SUBSTR(l_out_string,1,l_occurence-1)||p_to_pattern||SUBSTR(l_out_string,l_occurence+l_len,32000);
286
287 l_upper_string := UPPER(l_out_string);
288
289 -- find next
290 l_occurence := INSTR(l_upper_string,p_from_pattern,1,1);
291
292 END LOOP;
293
294 RETURN l_out_string;
295
296 END replace_string;
297
298 FUNCTION check_grant_text (
299 p_table_name VARCHAR2,
300 p_select_text VARCHAR2)
301 RETURN BOOLEAN IS
302 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_GRANT_TEXT';
303 l_val NUMBER(20);
304 l_select_text VARCHAR(32000);
305 BEGIN
306 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
307 l_label := 'igs.plsql.igs_sc_grants_pvt.check_grant_text.begin';
308 l_debug_str := 'Table Name: '||p_table_name||','||'Select Text: '||p_select_text;
309 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
310 END IF;
311
312 l_select_text := replace_string(ltrim(p_select_text),':PARTY_ID','igs_sc_vars.get_partyid');
313
314 l_select_text := replace_string(ltrim(l_select_text),':USER_ID','igs_sc_vars.get_userid');
315
316 l_select_text := replace_string(ltrim(l_select_text),':TBL_ALIAS','tstal');
317
318 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
319 l_label := 'igs.plsql.igs_sc_grants_pvt.check_grant_text.before_execute';
320 l_debug_str := 'Final Select: '||'SELECT 1 FROM ('||' SELECT 1 FROM '||p_table_name||' WHERE '||l_select_text||' )';
321 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
322 END IF;
323
324 -- EXECUTE IMMEDIATE 'SELECT count(*) FROM ('||' SELECT 1 FROM '||p_table_name||' WHERE '||l_select_text||' )'
325 --simrans change
326 EXECUTE IMMEDIATE 'SELECT 1 FROM ('||' SELECT 1 FROM '||p_table_name||' WHERE '||l_select_text||' )' ;
327 --INTO l_val;
328
329 RETURN TRUE;
330
331 EXCEPTION
332 WHEN OTHERS THEN
333
334 -- add error to the stack and return false
335 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
336 l_label := 'igs.plsql.igs_sc_grants_pvt.check_grant_text.execption';
337 l_debug_str := 'Exception: '||SQLERRM;
338 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
339 END IF;
340
341 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
342
343 RETURN FALSE;
344
345 END check_grant_text;
346
347 PROCEDURE construct_grant(
348 p_api_version IN NUMBER,
349 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
350 p_commit IN VARCHAR2 := FND_API.G_FALSE,
351 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
352 p_grant_id IN NUMBER,
353 x_return_status OUT NOCOPY VARCHAR2,
354 x_msg_count OUT NOCOPY NUMBER,
355 x_msg_data OUT NOCOPY VARCHAR2
356 )
357 IS
358
359
360 l_api_name CONSTANT VARCHAR2(30) := 'CONSTRUCT_GRANT';
361 l_api_version CONSTANT NUMBER := 1.0;
362 l_return_status VARCHAR2(1);
363 l_msg_count NUMBER;
364 l_msg_data VARCHAR2(2000);
365
366 l_group_rec g_object_rec;
367 l_grant_rec g_grant_rec;
368 l_user_id NUMBER(15);
369 l_stat_found BOOLEAN := false;
370 l_alias_name VARCHAR2(8);
371 l_grant_where VARCHAR2(4000);
372
373 --skpandey, Bug#4937960: Changed c_group cursor definition to optimize query
374 -- Object data cursor
375 CURSOR c_group(cp_grant_id igs_sc_grants.grant_id%TYPE) IS
376 SELECT sco.obj_group_id,
377 sco.object_id,
378 fnd.obj_name,
379 grp.default_policy_type,
380 pk1_column_name ,
381 pk2_column_name ,
382 pk3_column_name ,
383 pk4_column_name ,
384 pk5_column_name ,
385 pk1_column_type ,
386 pk2_column_type ,
387 pk3_column_type ,
388 pk4_column_type ,
389 pk5_column_type
390 FROM igs_sc_objects sco,
391 fnd_objects fnd,
392 igs_sc_grants grn,
393 igs_sc_obj_groups grp
394 WHERE (application_id IN (8405,8406))
395 AND fnd.object_id = sco.object_id
396 AND sco.obj_group_id = grn.obj_group_id
397 AND grn.grant_id = cp_grant_id
398 AND grp.obj_group_id = grn.obj_group_id
399 AND sco.active_flag = 'Y';
400
401
402 -- Returns all grants for a given user and object group
403
404 CURSOR c_grants IS
405 SELECT grant_id,
406 grant_text
407 FROM igs_sc_grants gr
408 WHERE grant_id = p_grant_id;
409
410
411 BEGIN
412 -- Standard Start of API savepoint
413 SAVEPOINT construct_grant;
414
415 -- Standard call to check for call compatibility.
416 IF NOT FND_API.Compatible_API_Call (l_api_version,
417 p_api_version,
418 l_api_name,
419 G_PKG_NAME)
420 THEN
421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
422 END IF;
423
424 -- Initialize message list if p_init_msg_list is set to TRUE.
425
426 x_return_status := FND_API.G_RET_STS_SUCCESS;
427
428 IF FND_API.to_Boolean (p_init_msg_list) THEN
429 FND_MSG_PUB.initialize;
430 END IF;
431
432 -- API body
433 OPEN c_grants;
434 FETCH c_grants INTO l_grant_rec;
435 CLOSE c_grants;
436
437 FOR c_group_rec IN c_group(p_grant_id) LOOP
438
439 -- Construct grant for each object in the grant group
440
441 Put_Log_Msg ('Generating grant for object: '||c_group_rec.object_id,0);
442
443 build_grant (c_group_rec, l_grant_rec);
444
445 END LOOP;
446
447
448 -- End of API body.
449 -- Standard check of p_commit.
450
451 IF FND_API.To_Boolean( p_commit ) THEN
452 COMMIT WORK;
453 END IF;
454
455 -- Standard call to get message count and if count is 1, get message info.
456 FND_MSG_PUB.Count_And_Get
457 ( p_count => x_msg_count ,
458 p_data => x_msg_data
459 );
460
461 EXCEPTION
462 WHEN FND_API.G_EXC_ERROR THEN
463
464 ROLLBACK TO construct_grant;
465 x_return_status := FND_API.G_RET_STS_ERROR;
466 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
467 p_data => x_msg_data );
468
469 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
470
471 ROLLBACK TO construct_grant;
472 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
473 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
474 p_data => x_msg_data );
475
476 WHEN OTHERS THEN
477
478 ROLLBACK TO construct_grant;
479 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
480
481 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
482 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
483 END IF;
484 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
485 p_data => x_msg_data );
486
487
488 END construct_grant;
489
490
491
492
493 PROCEDURE generate_grant(
494 p_api_version IN NUMBER,
495 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
496 p_commit IN VARCHAR2 := FND_API.G_FALSE,
497 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
498 p_object_name IN VARCHAR2,
499 p_function_type IN VARCHAR2,
500 x_where_clause OUT NOCOPY VARCHAR2,
501 x_return_status OUT NOCOPY VARCHAR2,
502 x_msg_count OUT NOCOPY NUMBER,
503 x_msg_data OUT NOCOPY VARCHAR2
504 )
505 IS
506
507
508 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_GRANT';
509 l_api_version CONSTANT NUMBER := 1.0;
510 l_return_status VARCHAR2(1);
511 l_msg_count NUMBER;
512 l_msg_data VARCHAR2(2000);
513
514
515 BEGIN
516
517 -- Standard call to check for call compatibility.
518 IF NOT FND_API.Compatible_API_Call (l_api_version,
519 p_api_version,
520 l_api_name,
521 G_PKG_NAME)
522 THEN
523 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
524 END IF;
525
526 -- Initialize message list if p_init_msg_list is set to TRUE.
527
528 x_return_status := FND_API.G_RET_STS_SUCCESS;
529
530 IF FND_API.to_Boolean (p_init_msg_list) THEN
531 FND_MSG_PUB.initialize;
532 END IF;
533
534 x_where_clause:= generate_grant (
535 p_object_name => p_object_name,
536 p_function_type => p_function_type);
537
538 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
539 l_label := 'igs.plsql.igs_sc_grants_pvt.generate_grant';
540 l_debug_str := 'Table Name: '||p_object_name||','||' Function type: '||p_function_type||','
541 ||'Pridicate Where Clause: '||x_where_clause;
542 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
543 END IF;
544
545 -- API body
546
547
548 -- End of API body.
549 -- Standard check of p_commit.
550
551 IF FND_API.To_Boolean( p_commit ) THEN
552 COMMIT WORK;
553 END IF;
554
555 -- Standard call to get message count and if count is 1, get message info.
556 FND_MSG_PUB.Count_And_Get
557 ( p_count => x_msg_count ,
558 p_data => x_msg_data
559 );
560
561 EXCEPTION
562 WHEN FND_API.G_EXC_ERROR THEN
563
564 x_return_status := FND_API.G_RET_STS_ERROR;
565 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
566 p_data => x_msg_data );
567
568 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
569 l_label := 'igs.plsql.igs_sc_grants_pvt.generate_grant.ex_error';
570 l_debug_str := 'Handled Exception: Table Name: '||p_object_name||','||' Function type: '||p_function_type||','
571 ||'Error Message: '||x_msg_data;
572 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
573 END IF;
574
575 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
576
577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
578 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
579 p_data => x_msg_data );
580
581 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
582 l_label := 'igs.plsql.igs_sc_grants_pvt.generate_grant.ex_un';
583 l_debug_str := 'Unhandled Exception: Table Name: '||p_object_name||','||' Function type: '||p_function_type||','
584 ||'Error Message: '||x_msg_data;
585 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
586 END IF;
587
588 WHEN OTHERS THEN
589
590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
591
592 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
593 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
594 END IF;
595 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
596 p_data => x_msg_data );
597
598 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
599 l_label := 'igs.plsql.igs_sc_grants_pvt.generate_grant.ex_other';
600 l_debug_str := 'Other Exceptions: Table Name: '||p_object_name||','||' Function type: '||p_function_type||','
601 ||'Error Message: '||x_msg_data;
602 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
603 END IF;
604
605 END generate_grant;
606
607 PROCEDURE build_grant(
608 p_group_rec IN OUT NOCOPY g_object_rec,
609 p_grants_rec IN OUT NOCOPY g_grant_rec
610 )
611 IS
612 l_statment VARCHAR2(4000); -- Grant statment
613 l_stat_found BOOLEAN := false;
614 l_grant_cond g_grant_conds_t;
615 l_user_attrib igs_sc_usr_attribs%ROWTYPE;
616 l_obj_attrib igs_sc_obj_att_mths%ROWTYPE;
617 l_cur_pos NUMBER(10);
618 l_found_pos NUMBER(10);
619 l_cur_num NUMBER(1);
620 l_alias_name VARCHAR2(8);
621 l_part_grant VARCHAR2(4000);
622 l_part_grant2 VARCHAR2(4000);
623 l_attr_select VARCHAR2(4000);
624 l_usr_select VARCHAR2(4000);
625 l_obj_select VARCHAR2(4000);
626 l_obj_const VARCHAR2(4000);
627 l_usr_const VARCHAR2(4000);
628 l_obj_alias VARCHAR2(25);
629 l_usr_alias VARCHAR2(25);
630 l_column_name VARCHAR2(255);
631
632
633 l_post_grant VARCHAR2(255);
634
635
636 -- Select of all conditions for a grant
637
638 CURSOR c_grant_where (s_grant_id NUMBER, s_obj_id NUMBER) IS
639 SELECT grant_where
640 FROM igs_sc_obj_grants
641 WHERE object_id = s_obj_id
642 AND grant_id = s_grant_id;
643 --FOR UPDATE OF grant_where;
644
645 CURSOR c_grant_cond (s_grant_id NUMBER )IS
646 SELECT grant_id,
647 grant_cond_num,
648 obj_attrib_id,
649 user_attrib_id,
650 condition,
651 text_value
652 FROM igs_sc_grant_conds
653 WHERE grant_id = s_grant_id;
654
655 CURSOR c_user_attrib (s_attrib_id NUMBER) IS
656 SELECT *
657 FROM igs_sc_usr_attribs
658 WHERE user_attrib_id = s_attrib_id;
659
660 CURSOR c_obj_attrib (s_attrib_id NUMBER, s_object_id NUMBER) IS
661 SELECT *
662 FROM igs_sc_obj_att_mths
663 WHERE obj_attrib_id = s_attrib_id
664 AND object_id = s_object_id;
665
666 l_table_column VARCHAR2(30);
667
668 BEGIN
669
670 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
671 l_label := 'igs.plsql.igs_sc_grants_pvt.build_grant.begin';
672 l_debug_str := 'Grant ID: '||p_grants_rec.grant_id||','||' Grant text: '||p_grants_rec.grant_text;
673 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
674 END IF;
675
676 onlyZTypeAttributes := TRUE;
677
678 Put_Log_Msg ('Grant found: '||p_grants_rec.grant_id,0);
679 Put_Log_Msg ('Grant text: '||p_grants_rec.grant_text,0);
680
681 -- For each condition in grant
682 --code added by mmkumar
683 FOR c_grant_cond_rec IN c_grant_cond(p_grants_rec.grant_id) LOOP
684 IF c_grant_cond_rec.obj_attrib_id IS NOT NULL THEN
685 OPEN c_obj_attrib ( c_grant_cond_rec.obj_attrib_id ,p_group_rec.object_id );
686 FETCH c_obj_attrib INTO l_obj_attrib;
687
688 IF c_obj_attrib%NOTFOUND THEN
689 -- Method for the table not found
690 close c_obj_attrib;
691 FND_MESSAGE.SET_NAME('IGS', 'IGS_SC_MTHD_NOT_FOUND');
692 FND_MESSAGE.SET_TOKEN('ATTRIB_ID', l_grant_cond(c_grant_cond_rec.grant_cond_num).obj_attrib_id);
693 FND_MESSAGE.SET_TOKEN('TABLE_NAME',p_group_rec.table_name );
694 FND_MSG_PUB.Add;
695
696 RAISE FND_API.G_EXC_ERROR;
697
698 ELSIF l_obj_attrib.NULL_ALLOW_FLAG IN ('Y','N') THEN
699 onlyZTypeAttributes := false;
700 close c_obj_attrib;
701 EXIT;
702 END IF;
703
704 if c_obj_attrib%isopen then
705 close c_obj_attrib;
706 end if;
707 END IF;
708 END LOOP;
709 --code added by mmkumar ends
710
711 FOR c_grant_cond_rec IN c_grant_cond(p_grants_rec.grant_id) LOOP
712
713 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
714 l_label := 'igs.plsql.igs_sc_grants_pvt.build_grant.grant_cond_loop';
715 l_debug_str := 'Condition Number: '||c_grant_cond_rec.grant_cond_num||','||'Object Attribute ID: '||c_grant_cond_rec.obj_attrib_id
716 ||','||'User Attrib ID: '||c_grant_cond_rec.user_attrib_id||','||'Condition Text Value: '||c_grant_cond_rec.text_value;
717 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
718 END IF;
719
720 Put_Log_Msg ('| Condition found: '||c_grant_cond_rec.grant_cond_num,0);
721
722 l_grant_cond(c_grant_cond_rec.grant_cond_num).obj_attrib_id := c_grant_cond_rec.obj_attrib_id;
723 l_grant_cond(c_grant_cond_rec.grant_cond_num).user_attrib_id := c_grant_cond_rec.user_attrib_id;
724 l_grant_cond(c_grant_cond_rec.grant_cond_num).condition := c_grant_cond_rec.condition;
725 l_grant_cond(c_grant_cond_rec.grant_cond_num).text_value := c_grant_cond_rec.text_value;
726 l_obj_select := '';
727 l_usr_select := '';
728 l_obj_const := '';
729 l_usr_const := '';
730 l_obj_alias := 'sc'||p_grants_rec.grant_id||'o'||c_grant_cond_rec.grant_cond_num||'a';
731 l_usr_alias := 'sc'||p_grants_rec.grant_id||'u'||c_grant_cond_rec.grant_cond_num||'a';
732
733 -- Construct condition grant
734
735 IF l_grant_cond(c_grant_cond_rec.grant_cond_num).obj_attrib_id IS NOT NULL THEN
736 -- Process object attribute
737
738 -- Fetch definition
739
740 OPEN c_obj_attrib ( l_grant_cond(c_grant_cond_rec.grant_cond_num).obj_attrib_id ,p_group_rec.object_id );
741 FETCH c_obj_attrib INTO l_obj_attrib;
742
743 IF c_obj_attrib%NOTFOUND THEN
744
745 -- Method for the table not found
746 CLOSE c_obj_attrib;
747 FND_MESSAGE.SET_NAME('IGS', 'IGS_SC_MTHD_NOT_FOUND');
748 FND_MESSAGE.SET_TOKEN('ATTRIB_ID', l_grant_cond(c_grant_cond_rec.grant_cond_num).obj_attrib_id);
749 FND_MESSAGE.SET_TOKEN('TABLE_NAME',p_group_rec.table_name );
750 FND_MSG_PUB.Add;
751 RAISE FND_API.G_EXC_ERROR;
752
753 END IF;
754
755
756 CLOSE c_obj_attrib;
757
758 Put_Log_Msg ('| Attribute fetched: '||l_obj_attrib.obj_attrib_id||' '||l_obj_attrib.obj_attrib_type ,0);
759 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
760 l_label := 'igs.plsql.igs_sc_grants_pvt.build_grant.grant_attrib';
761 l_debug_str := 'Attribute fetched: '||l_obj_attrib.obj_attrib_id||','||l_obj_attrib.obj_attrib_type ;
762 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
763 END IF;
764
765 --code added by mmkumar
766 l_grant_cond(c_grant_cond_rec.grant_cond_num).z_typ_flag := l_obj_attrib.NULL_ALLOW_FLAG;
767 --
768
769 -- T Table column name, S select statement, F - function call
770
771 IF l_obj_attrib.obj_attrib_type = 'T' THEN
772
773 -- If attribute column name -then just add column name to the grant text
774 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_obj_attrib.select_text||' ';
775
776 l_table_column := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text;
777
778 ELSIF l_obj_attrib.obj_attrib_type = 'F' THEN
779
780 l_obj_const := l_obj_attrib.select_text;
781
782 ELSIF l_obj_attrib.obj_attrib_type IN ('M','S') AND l_obj_attrib.static_type IN ('S','D') THEN
783
784 -- Replace table alias value with our alias, generated based on attribute id
785
786 l_obj_select := replace_string(ltrim(l_obj_attrib.select_text),':TBL_ALIAS',l_obj_alias);
787
788
789 ELSIF l_obj_attrib.obj_attrib_type IN ('S','F') AND l_obj_attrib.static_type IN ('C') THEN
790
791 -- Attribute static and Select or function type - use select from attributes table
792 -- final will look like (pk_column1,pk_column2,..) IN
793 -- (select pk1_value,pk2_value,.. from igs_sc_obj_att_vals where object_id= ..
794 -- AND obj_attrib_id = ... AND value =..)
795 -- Construct first part with PK
796 l_alias_name := get_alias;
797
798 l_part_grant := '('||p_group_rec.pk1_column_name;
799 l_part_grant2 := ') IN ( SELECT '||l_alias_name||'.pk1_value';
800
801 -- Check if more then one PK column is present
802
803 IF p_group_rec.pk2_column_name IS NOT NULL THEN
804 l_part_grant := l_part_grant|| ','||p_group_rec.pk2_column_name;
805 l_part_grant2 := l_part_grant2||','||l_alias_name||'.pk2_value';
806
807 IF p_group_rec.pk3_column_name IS NOT NULL THEN
808 l_part_grant := l_part_grant|| ','||p_group_rec.pk2_column_name;
809 l_part_grant2 := l_part_grant2||','||l_alias_name||'.pk2_value';
810
811 IF p_group_rec.pk4_column_name IS NOT NULL THEN
812 l_part_grant := l_part_grant|| ','||p_group_rec.pk2_column_name;
813 l_part_grant2 := l_part_grant2||','||l_alias_name||'.pk2_value';
814
815 IF p_group_rec.pk5_column_name IS NOT NULL THEN
816 l_part_grant := l_part_grant|| ','||p_group_rec.pk2_column_name;
817 l_part_grant2 := l_part_grant2||','||l_alias_name||'.pk2_value';
818
819 END IF;
820
821 END IF;
822
823 END IF;
824
825 END IF;
826
827
828 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_part_grant||l_part_grant2||' FROM igs_sc_obj_att_vals '||l_alias_name
829 ||' WHERE '||l_alias_name||'.object_id='||p_group_rec.object_id
830 ||' AND '||l_alias_name||'.obj_attrib_id='||l_grant_cond(c_grant_cond_rec.grant_cond_num).obj_attrib_id ||' AND '
831 ||l_alias_name||'.attr_value '||l_grant_cond(c_grant_cond_rec.grant_cond_num).condition||' ';
832
833 l_post_grant := ')';
834
835 ELSE -- F
836
837 -- Not supported combination
838 FND_MESSAGE.SET_NAME('IGS', 'IGS_SC_NOT_SPRT_COMB');
839 FND_MSG_PUB.Add;
840 RAISE FND_API.G_EXC_ERROR;
841
842 END IF;
843
844 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
845 l_label := 'igs.plsql.igs_sc_grants_pvt.build_grant.condition_text';
846 l_debug_str := 'Attribute condition: '||l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text ;
847 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
848 END IF;
849
850 Put_Log_Msg ('| Attribute condition: '|| l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text,0);
851
852 ELSE --Add Text instead of parameter
853
854 l_obj_const :=l_grant_cond(c_grant_cond_rec.grant_cond_num).text_value;
855
856 END IF;
857
858 IF c_grant_cond_rec.user_attrib_id IS NOT NULL THEN
859
860 -- read attribute definition
861 OPEN c_user_attrib ( l_grant_cond(c_grant_cond_rec.grant_cond_num).user_attrib_id );
862 FETCH c_user_attrib INTO l_user_attrib;
863 CLOSE c_user_attrib;
864
865 Put_Log_Msg ('| User Attribute found: '|| l_grant_cond(c_grant_cond_rec.grant_cond_num).user_attrib_id ,0);
866
867 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
868 l_label := 'igs.plsql.igs_sc_grants_pvt.build_grant.user_attrib';
869 l_debug_str := ' User Attribute found: '||l_grant_cond(c_grant_cond_rec.grant_cond_num).user_attrib_id ;
870 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
871 END IF;
872
873
874 -- If dynamic attribute - get value for dynamic C - constant, S - static, D - dynamic
875
876 -- Check for being multi-value attribute. T Table column name, S select statement, F Function call, M - multy values - select only
877 IF l_user_attrib.static_type = 'D' AND l_user_attrib.user_attrib_type <> 'F' THEN
878
879 -- Dynamic attribute - we need to append the actual select for this attribute
880
881 l_attr_select := replace_string(ltrim(l_user_attrib.select_text),':PARTY_ID','igs_sc_vars.get_partyid');
882
883 l_attr_select := replace_string(ltrim(l_attr_select),':USER_ID','igs_sc_vars.get_userid');
884
885 -- Replace table alias value with our alias, generated based on attribute id
886
887 l_usr_select := replace_string(ltrim(l_attr_select),':TBL_ALIAS',l_usr_alias);
888
889
890 ELSIF l_user_attrib.static_type = 'D' AND l_user_attrib.user_attrib_type = 'F' THEN
891
892 l_attr_select := replace_string(ltrim(l_user_attrib.select_text),':PARTY_ID','igs_sc_vars.get_partyid');
893
894 l_usr_const := replace_string(ltrim(l_attr_select),':USER_ID','igs_sc_vars.get_userid');
895
896 ELSIF l_user_attrib.user_attrib_type = 'M' THEN
897
898 -- If yes then construct for multi-value attribute
899 -- Add select from values table
900
901 l_usr_select :='SELECT '||l_usr_alias||'.attr_value FROM igs_sc_usr_att_vals '||l_usr_alias||' WHERE '||l_usr_alias||'.user_id=igs_sc_vars.get_userid AND '
902 ||l_usr_alias||'.user_attrib_id='||c_grant_cond_rec.user_attrib_id;
903
904 ELSE
905 --Simply get value for an attribute using API and append
906
907 l_usr_const := 'igs_sc_vars.get_att('||c_grant_cond_rec.user_attrib_id||')';
908
909
910 END IF; -- Multy value attribute end
911
912 Put_Log_Msg ('| Attribute condition: '|| l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text ,0);
913
914 ELSE --Add Text instead of parameter
915
916 l_usr_const := l_grant_cond(c_grant_cond_rec.grant_cond_num).text_value;
917
918 END IF; -- User parameter id null
919
920 -- l_usr_const, l_obj_const - function or text value of any kind
921 -- l_obj_select, l_usr_select - select statments.
922
923 -- Add post grant condition
924
925 IF l_obj_select IS NULL THEN
926 -- --code added my mmkumar
927 IF l_obj_attrib.NULL_ALLOW_FLAG = 'Z' THEN
928 --IF isSingleGrantCond(p_grants_rec.grant_text) THEN
929 IF onlyZTypeAttributes THEN
930 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' 1=1 ';
931 ELSE
932 NULL;
933 END IF;
934
935 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
936 l_label := 'igs.plsql.igs_sc_grants_pvt.build_grant.Obj_select_1';
937 l_debug_str := ' Object select NULL, and Z type attrib. Condition text '||l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text ;
938 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
939 END IF;
940
941
942 ELSE
943 --code added by mmkumar ends
944 -- its not Z
945 IF l_obj_const IS NOT NULL AND l_usr_select IS NOT NULL THEN
946 -- User attribute is select of any kind and object attribute is not select
947 -- User Select = Obj CONST
948 l_found_pos := INSTR(UPPER(ltrim(l_usr_select)),'FROM',1,1);
949 l_column_name := substr(ltrim(l_usr_select),8,l_found_pos-9); -- 8 position 'select ' found -9 ' FROM'
950
951 --grant text ' EXISTS (object_select AND Column Condition ( user attrr select))
952
953 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text
954 ||' EXISTS ('||l_usr_select||' AND '||l_column_name||' '||check_operation(l_grant_cond(c_grant_cond_rec.grant_cond_num).condition)||' '||l_obj_const||' )';
955
956 ELSIF l_obj_const IS NULL AND l_usr_select IS NOT NULL THEN
957 -- Colunmn name = User Select
958 IF l_obj_attrib.NULL_ALLOW_FLAG = 'Y' THEN -- Nullable column
959 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := '('||l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' '||
960 l_grant_cond(c_grant_cond_rec.grant_cond_num).condition ||' (' ||l_usr_select||') OR '||l_table_column ||' IS NULL) '||l_post_grant;
961 ELSE
962 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' '||
963 l_grant_cond(c_grant_cond_rec.grant_cond_num).condition ||' (' ||l_usr_select||')'||l_post_grant;
964 END IF;
965 ELSIF l_obj_const IS NULL AND l_usr_select IS NULL THEN
966 -- Column name = User CONST
967 IF l_obj_attrib.NULL_ALLOW_FLAG = 'Y' THEN -- Nullable column
968 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := '('||l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' '||
969 l_obj_const||' '||l_grant_cond(c_grant_cond_rec.grant_cond_num).condition||' '||l_usr_const||' OR '||l_table_column ||' IS NULL)'||l_post_grant;
970 ELSE
971 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' '||
972 l_obj_const||' '||l_grant_cond(c_grant_cond_rec.grant_cond_num).condition||' '||l_usr_const||l_post_grant;
973 END IF;
974 ELSE
975 -- Object Const = User CONST
976 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' '||
977 l_obj_const||' '||l_grant_cond(c_grant_cond_rec.grant_cond_num).condition||' '||l_usr_const||l_post_grant;
978
979 END IF;
980
981 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
982 l_label := 'igs.plsql.igs_sc_grants_pvt.build_grant.Obj_select_2';
983 l_debug_str := ' Object select is NULL. Non Z. Grant condition text '||l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text ;
984 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
985 END IF;
986 END IF; --mmkumar
987 ELSE --Object select is not null
988
989 -- we need to change select so we'll have 2 select statments
990
991
992 --find the name of the select coulmn for attribute
993 l_found_pos := INSTR(UPPER(ltrim(l_obj_select)),'FROM',1,1);
994 l_column_name := substr(ltrim(l_obj_select),8,l_found_pos-9); -- 8 position 'select ' found -9 ' FROM'
995 --grant text ' EXISTS (object_select AND Column Condition ( user attrr select))
996
997 --code added my mmkumar
998 IF l_obj_attrib.NULL_ALLOW_FLAG = 'Z' THEN
999 --IF isSingleGrantCond(p_grants_rec.grant_text) THEN
1000 IF onlyZTypeAttributes THEN
1001 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' 1=1 ';
1002 ELSE
1003 NULL;
1004 END IF;
1005
1006 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1007 l_label := 'igs.plsql.igs_sc_grants_pvt.build_grant.Obj_select_3';
1008 l_debug_str := ' Object select NOT NULL, and Z type attrib. Condition text '||l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text ;
1009 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1010 END IF;
1011
1012 ELSE
1013 -- its not Z
1014 --
1015 IF l_usr_select IS NOT NULL THEN
1016 --Add user select
1017 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' EXISTS ('||l_obj_select||' AND '||l_column_name||' '||
1018 l_grant_cond(c_grant_cond_rec.grant_cond_num).condition||' ('||l_usr_select||' )';
1019 ELSE --Add user constant
1020 IF l_grant_cond(c_grant_cond_rec.grant_cond_num).condition IS NULL THEN
1021 --operator in the grant text - don't add anything but Object select
1022 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_obj_select;
1023 ELSE
1024 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' EXISTS ('||l_obj_select||' AND '||l_column_name||' '||
1025 l_grant_cond(c_grant_cond_rec.grant_cond_num).condition||' '||l_usr_const;
1026 END IF;
1027 END IF;
1028
1029 IF l_obj_attrib.NULL_ALLOW_FLAG = 'Y' THEN
1030 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' OR NOT EXISTS (' ||l_obj_select||'))';
1031 ELSE
1032 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||')';
1033 END IF;
1034
1035 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1036 l_label := 'igs.plsql.igs_sc_grants_pvt.build_grant.Obj_select_4';
1037 l_debug_str := ' Object select NOT NULL, and Non Z type attrib. Condition text '||l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text ;
1038 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1039 END IF;
1040 END IF; --mmkumar
1041 END IF;
1042
1043 END LOOP;
1044
1045
1046 Put_Log_Msg ('| Analyzing grant structure, current grant: '|| l_statment ,0);
1047
1048
1049 --code added by mmkumar
1050 l_cur_pos :=1;
1051 l_found_pos := 0;
1052
1053 l_grant_text := p_grants_rec.grant_text;
1054 LOOP
1055 l_found_pos := INSTR(l_grant_text,':',l_cur_pos,1);
1056
1057 IF l_found_pos =0 THEN -- End loop no occurences found anymore
1058 EXIT;
1059 END IF;
1060
1061 Put_Log_Msg ('| Found new condition, at position: '|| l_found_pos ,0);
1062
1063 -- Find number of predicate - total numbers is limited to 9 so far.
1064
1065 l_cur_num := SUBSTR(l_grant_text,l_found_pos+1,1); --Just one character
1066 IF l_grant_cond(l_cur_num).z_typ_flag = 'Z' AND NOT onlyZTypeAttributes THEN
1067 l_grant_text := REPLACE(l_grant_text, ':' || l_cur_num,'');
1068 END IF;
1069 l_cur_pos := l_found_pos + 2;
1070 END LOOP;
1071
1072 l_bodmas_grant_text := getBodmasCondition(l_grant_text);
1073
1074 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1075 l_label := 'igs.plsql.igs_sc_grants_pvt.build_grant.bodmas_return_1';
1076 l_debug_str := ' Grant text after Bodmas call '||l_bodmas_grant_text ;
1077 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1078 END IF;
1079
1080 -- Analize grant structure and construct grant, example "(:1 AND (:2 OR :3))"
1081 l_cur_pos :=1;
1082 l_found_pos := 0;
1083 -- First pass - check if there is anything before the first grant condition
1084 l_found_pos := INSTR(l_bodmas_grant_text,':',l_cur_pos,1);
1085
1086 IF l_found_pos >1 THEN
1087 IF l_statment IS NOT NULL THEN -- not first grant, we need to add OR
1088 l_statment := l_statment||' OR ';
1089 END IF;
1090
1091 -- concatenate with previous statment and add first part of condition text
1092 -- l_statment := l_statment||substr(p_grants_rec.grant_text,1,l_found_pos-1);
1093 Put_Log_Msg (' First part added, current grant: '|| l_statment ,0);
1094
1095 END IF;
1096
1097 -- IF onlyZTypeAttributes THEN
1098 -- l_statment := l_statment||'1=1';
1099 -- ELSE
1100
1101 LOOP
1102 -- Find next occurence of :
1103 l_found_pos := INSTR(l_bodmas_grant_text,':',l_cur_pos,1);
1104 IF l_found_pos =0 THEN -- End loop no occurences found anymore
1105 EXIT;
1106 END IF;
1107
1108 IF l_grant_cond(l_cur_num).z_typ_flag = 'Z' AND onlyZTypeAttributes THEN
1109 l_statment := l_statment || '1 = 1';
1110 EXIT;
1111 END IF;
1112
1113 Put_Log_Msg ('| Found new condition, at position: '|| l_found_pos ,0);
1114 -- Find number of predicate - total numbers is limited to 9 so far.
1115 l_cur_num := SUBSTR(l_bodmas_grant_text,l_found_pos+1,1); --Just one character
1116 l_statment := l_statment||SUBSTR(l_bodmas_grant_text,l_cur_pos, (l_found_pos - l_cur_pos));
1117 Put_Log_Msg ('| New statment: '|| l_statment ,0);
1118
1119 -- Add condition from found grant number to statement
1120 l_statment := l_statment || l_grant_cond(l_cur_num).cond_text;
1121 Put_Log_Msg ('| Statment with condition added: '|| l_statment ,0);
1122
1123 l_cur_pos := l_found_pos + 2;
1124
1125 END LOOP;
1126 -- Add last part of condition
1127 IF NOT (l_grant_cond(l_cur_num).z_typ_flag = 'Z' AND onlyZTypeAttributes) THEN
1128 l_statment := l_statment||substr(l_bodmas_grant_text,l_cur_pos);
1129 END IF;
1130
1131 Put_Log_Msg ('| Last part of condition added: '|| l_statment ,0);
1132 --END IF; --mmkumar
1133
1134 --code added by mmkumar ends
1135
1136 --gmaheswa fnd logging
1137 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1138 l_label := 'igs.plsql.igs_sc_grants_pvt.build_grant_end';
1139 l_debug_str := 'Final where Clause: Table Name: '||p_group_rec.table_name||','||'Pridicate Where: '||l_statment;
1140 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1141 END IF;
1142
1143
1144
1145 --check grant text
1146 IF NOT check_grant_text ( p_group_rec.table_name, l_statment) THEN
1147
1148 FND_MESSAGE.SET_NAME('IGS', 'IGS_SC_GRNT_TXT_ERR');
1149 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_group_rec.table_name );
1150 FND_MESSAGE.SET_TOKEN('GRNT_TEXT', l_statment);
1151 FND_MSG_PUB.Add;
1152 RAISE FND_API.G_EXC_ERROR;
1153
1154 END IF;
1155
1156
1157 -- Insert or update grant for an object
1158
1159 IF l_statment IS NULL THEN
1160
1161 DELETE FROM igs_sc_obj_grants
1162 WHERE object_id = p_group_rec.object_id AND grant_id = p_grants_rec.grant_id;
1163
1164 END IF;
1165
1166 OPEN c_grant_where( p_grants_rec.grant_id,p_group_rec.object_id) ;
1167 FETCH c_grant_where INTO l_part_grant;
1168
1169 IF c_grant_where%NOTFOUND THEN
1170
1171 -- Insert new row
1172 INSERT INTO igs_sc_obj_grants (
1173 grant_id,
1174 object_id,
1175 grant_where,
1176 creation_date,
1177 created_by,
1178 last_updated_by,
1179 last_update_date,
1180 last_update_login)
1181 VALUES (
1182 p_grants_rec.grant_id,
1183 p_group_rec.object_id,
1184 l_statment,
1185 SYSDATE,
1186 NVL(FND_GLOBAL.user_id,-1),
1187 NVL(FND_GLOBAL.user_id,-1),
1188 SYSDATE,
1189 NVL(FND_GLOBAL.login_id, -1) ) ;
1190 ELSE
1191 null; --simran
1192 --update existing
1193 -- UPDATE igs_sc_obj_grants SET grant_where = l_statment WHERE CURRENT OF c_grant_where;
1194
1195 END IF;
1196
1197 CLOSE c_grant_where;
1198
1199 END build_grant ;
1200
1201
1202 /* This function concatenates all grants for a given user using link between groups and users.*/
1203
1204 FUNCTION generate_grant(
1205 p_object_name IN VARCHAR2,
1206 p_function_type IN VARCHAR2
1207 ) RETURN VARCHAR2
1208 IS
1209 l_statment VARCHAR2(32000); -- Grant statment
1210 l_group_rec g_object_rec;
1211 l_user_id NUMBER(15);
1212 l_stat_found BOOLEAN := false;
1213 l_alias_name VARCHAR2(8);
1214
1215 --skpandey, Bug#4937960: Changed c_group cursor definition to optimize query
1216 -- Object data cursor
1217 CURSOR c_group(cp_obj_name fnd_objects.obj_name%TYPE) IS
1218 SELECT sco.obj_group_id,
1219 sco.object_id,
1220 fnd.obj_name,
1221 grp.default_policy_type,
1222 pk1_column_name ,
1223 pk2_column_name ,
1224 pk3_column_name ,
1225 pk4_column_name ,
1226 pk5_column_name ,
1227 pk1_column_type ,
1228 pk2_column_type ,
1229 pk3_column_type ,
1230 pk4_column_type ,
1231 pk5_column_type
1232 FROM igs_sc_objects sco,
1233 fnd_objects fnd,
1234 igs_sc_obj_groups grp
1235 WHERE application_id IN (8405,8406)
1236 AND fnd.obj_name = cp_obj_name
1237 AND grp.obj_group_id = sco.obj_group_id
1238 AND fnd.object_id = sco.object_id;
1239
1240 -- Returns all grants for a given user and object group
1241
1242 CURSOR c_grants (s_group_id NUMBER,s_object_id NUMBER, s_user_id NUMBER) IS
1243 SELECT gr.grant_id,
1244 objgr.grant_where
1245 FROM igs_sc_grants gr,
1246 igs_sc_obj_grants objgr,
1247 wf_local_user_roles rls
1248 WHERE rls.user_orig_system =g_user_orig_system
1249 AND rls.user_orig_system_id = s_user_id
1250 AND rls.role_orig_system = 'IGS'
1251 AND rls.role_orig_system_id = gr.user_group_id
1252 AND objgr.object_id = s_object_id
1253 AND objgr.grant_id = gr.grant_id
1254 AND SYSDATE BETWEEN NVL(rls.start_date,sysdate-1) AND NVL(rls.expiration_date,sysdate+1)
1255 AND gr.obj_group_id = s_group_id
1256 AND gr.locked_flag ='Y'
1257 AND decode(p_function_type,'S',gr.grant_select_flag,'I',gr.grant_insert_flag,'D',gr.grant_delete_flag,'U',gr.grant_update_flag,'N')='Y';
1258
1259 CURSOR c_def_grant (s_group_id NUMBER,s_object_id NUMBER) IS
1260 SELECT objgr.grant_where
1261 FROM igs_sc_grants gr,
1262 igs_sc_obj_grants objgr
1263 WHERE objgr.object_id = s_object_id
1264 AND objgr.grant_id = gr.grant_id
1265 AND gr.obj_group_id = s_group_id
1266 AND gr.locked_flag ='Y'
1267 AND upper(gr.grant_name)='DEFAULT';
1268
1269
1270 BEGIN
1271
1272 --gmaheswa fnd logging
1273 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1274 l_label := 'igs.plsql.igs_sc_grants_pvt.generate_grant.local';
1275 l_debug_str := 'Table Name: '||p_object_name||','||'Function Type: '||p_function_type;
1276 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1277 END IF;
1278
1279 g_alias_number :=0;
1280 l_user_id := igs_sc_vars.get_userid;
1281 g_user_id := l_user_id;
1282 -- Check if object exists in the tables and assigned to a group
1283
1284
1285 OPEN c_group(p_object_name);
1286 FETCH c_group INTO l_group_rec;
1287
1288 IF c_group%NOTFOUND THEN
1289
1290 -- Object is not found - return exception
1291 CLOSE c_group;
1292
1293 FND_MESSAGE.SET_NAME('IGS', 'IGS_SC_NO_OBJECT_FOUND');
1294 FND_MSG_PUB.Add;
1295
1296 -- Return nothing ofr a table
1297 RAISE FND_API.G_EXC_ERROR;
1298
1299 END IF;
1300
1301 -- Find all grant Ids for all groups user belongs to
1302
1303
1304 CLOSE c_group;
1305
1306 -- Check for each group grants
1307
1308 IF l_user_id = -1 THEN
1309 -- Apply default grant, if found
1310
1311 IF p_function_type = 'S' THEN
1312 -- Select only operation. If others - don't do anything
1313 OPEN c_def_grant (l_group_rec.obj_group_id,l_group_rec.object_id) ;
1314 FETCH c_def_grant INTO l_statment;
1315 CLOSE c_def_grant;
1316
1317 IF l_statment IS NOT NULL THEN
1318 l_stat_found := true;
1319 END IF;
1320
1321 END IF;
1322
1323 ELSE
1324
1325 FOR c_grants_rec IN c_grants(l_group_rec.obj_group_id,l_group_rec.object_id,l_user_id) LOOP
1326
1327 l_stat_found := true;
1328
1329 Put_Log_Msg ('Grant found: '||c_grants_rec.grant_id,0);
1330 Put_Log_Msg ('Grant text: '||c_grants_rec.grant_where,0);
1331
1332 -- Add last part of condition
1333 IF l_statment IS NULL THEN
1334
1335 l_statment :=c_grants_rec.grant_where;
1336
1337 ELSE
1338
1339 l_statment := l_statment||' OR '||c_grants_rec.grant_where;
1340
1341 END IF;
1342
1343 Put_Log_Msg ('| Condition added: '|| l_statment ,0);
1344
1345 END LOOP;
1346
1347 END IF;
1348
1349 IF NOT(l_stat_found) THEN
1350
1351 -- Nothing is granted for a group. Apply default policy
1352
1353 -- Default policy G - grant all, R - restrict all
1354 IF l_group_rec.default_policy_type ='G' THEN
1355 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1356 l_label := 'igs.plsql.igs_sc_grants_pvt.generate_grant.default1';
1357 l_debug_str := 'Table Name: '||p_object_name||','||'Function Type: '||p_function_type||','||' User ID: '
1358 ||l_user_id||','||'Default Policy: G';
1359 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1360 END IF;
1361 RETURN '';
1362
1363 ELSE
1364 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1365 l_label := 'igs.plsql.igs_sc_grants_pvt.generate_grant.default2';
1366 l_debug_str := 'Table Name: '||p_object_name||','||'Function Type: '||p_function_type||','||' User ID: '
1367 ||l_user_id||','||'Default Policy: R';
1368 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1369 END IF;
1370
1371 RETURN '1=2';
1372
1373 END IF;
1374
1375
1376 END IF;
1377
1378 --gmaheswa fnd logging
1379 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1380 l_label := 'igs.plsql.igs_sc_grants_pvt.generate_grant.final_return';
1381 l_debug_str := 'Table Name: '||p_object_name||','||'Function Type: '||p_function_type||','||' User ID: '
1382 ||l_user_id||','||'Final Select: '||l_statment;
1383 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1384 END IF;
1385
1386
1387 RETURN l_statment;
1388
1389 END generate_grant ;
1390
1391
1392 PROCEDURE populate_user_attrib(
1393 p_api_version IN NUMBER,
1394 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1395 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1396 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1397 p_attrib_id IN NUMBER := NULL,
1398 p_user_id IN NUMBER := NULL,
1399 p_all_attribs IN VARCHAR2 :='N',
1400 x_return_status OUT NOCOPY VARCHAR2,
1401 x_msg_count OUT NOCOPY NUMBER,
1402 x_msg_data OUT NOCOPY VARCHAR2
1403 ) IS
1404
1405 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE_USER_ATTRIB';
1406 l_api_version CONSTANT NUMBER := 1.0;
1407 l_return_status VARCHAR2(1);
1408 l_msg_count NUMBER;
1409 l_msg_data VARCHAR2(2000);
1410
1411 l_user_id NUMBER;
1412 l_party_id NUMBER;
1413
1414 -- This cursor is used when attrib is not specified -select all static attribs
1415
1416 CURSOR c_all_attribs IS
1417 SELECT user_attrib_id ,
1418 user_attrib_name ,
1419 user_attrib_type ,
1420 static_type ,
1421 select_text
1422 FROM igs_sc_usr_attribs
1423 WHERE user_attrib_id = p_attrib_id
1424 OR ( p_attrib_id IS NULL AND
1425 (static_type = 'S'
1426 OR (static_type = 'C' AND p_all_attribs = 'Y') )
1427 )
1428 ORDER BY user_attrib_id;
1429
1430 CURSOR c_attrib IS
1431 SELECT user_attrib_id ,
1432 user_attrib_name ,
1433 user_attrib_type ,
1434 static_type ,
1435 select_text
1436 FROM igs_sc_usr_attribs
1437 WHERE user_attrib_id = p_attrib_id;
1438
1439 -- skpandey, Bug#4937960: Changed c_user definition to optimize query
1440 CURSOR c_users IS
1441 SELECT user_orig_system_id user_id
1442 FROM wf_local_user_roles rls
1443 WHERE rls.user_orig_system = 'FND_USR'
1444 AND rls.role_orig_system = 'IGS'
1445 AND (rls.EXPIRATION_DATE IS NULL OR rls.EXPIRATION_DATE > SYSDATE)
1446 AND rls.parent_orig_system = 'IGS'
1447 AND rls.parent_orig_system_id = rls.role_orig_system_id
1448 AND rls.partition_id = 0;
1449
1450
1451 CURSOR c_party_value (s_user_id NUMBER) IS
1452 SELECT attr_value
1453 FROM igs_sc_usr_att_vals
1454 WHERE user_attrib_id = 1
1455 AND user_id = s_user_id;
1456
1457
1458 BEGIN
1459 -- Standard Start of API savepoint
1460 SAVEPOINT populate_user_attrib;
1461
1462 -- Standard call to check for call compatibility.
1463 IF NOT FND_API.Compatible_API_Call (l_api_version,
1464 p_api_version,
1465 l_api_name,
1466 G_PKG_NAME)
1467 THEN
1468 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1469 END IF;
1470
1471 -- Initialize message list if p_init_msg_list is set to TRUE.
1472
1473 x_return_status := FND_API.G_RET_STS_SUCCESS;
1474
1475 IF FND_API.to_Boolean (p_init_msg_list) THEN
1476 FND_MSG_PUB.initialize;
1477 END IF;
1478
1479 -- API body
1480
1481
1482 FOR c_attrib_rec IN c_all_attribs LOOP
1483
1484 IF p_user_id IS NULL THEN
1485
1486 OPEN c_users;
1487 FETCH c_users INTO l_user_id;
1488
1489 ELSE
1490
1491 l_user_id := p_user_id;
1492
1493 END IF;
1494
1495 LOOP
1496
1497 -- Get party id
1498
1499 OPEN c_party_value (l_user_id);
1500 FETCH c_party_value INTO l_party_id;
1501 CLOSE c_party_value;
1502
1503 -- populate attrib value for a given user
1504 insert_user_attrib(
1505 p_user_id => l_user_id ,
1506 p_party_id => l_party_id ,
1507 p_user_attrib_id => c_attrib_rec.user_attrib_id ,
1508 p_user_attrib_name => c_attrib_rec.user_attrib_name ,
1509 p_user_attrib_type => c_attrib_rec.user_attrib_type ,
1510 p_static_type => c_attrib_rec.static_type ,
1511 p_select_text => c_attrib_rec.select_text );
1512
1513 IF p_user_id IS NOT NULL THEN
1514 -- if only one user - end loop
1515 EXIT;
1516
1517 ELSE
1518
1519 FETCH c_users INTO l_user_id;
1520
1521 IF c_users%NOTFOUND THEN
1522
1523 -- if no more users to fetch exit
1524 EXIT;
1525
1526 END IF;
1527
1528 END IF;
1529
1530 END LOOP;
1531
1532 -- check if cursor is open then close
1533
1534 IF c_users%ISOPEN THEN
1535
1536 CLOSE c_users;
1537
1538 END IF;
1539 END LOOP;
1540
1541
1542 -- End of API body.
1543 -- Standard check of p_commit.
1544
1545 IF FND_API.To_Boolean( p_commit ) THEN
1546 COMMIT WORK;
1547 END IF;
1548
1549 -- Standard call to get message count and if count is 1, get message info.
1550 FND_MSG_PUB.Count_And_Get
1551 ( p_count => x_msg_count ,
1552 p_data => x_msg_data
1553 );
1554
1555 EXCEPTION
1556 WHEN FND_API.G_EXC_ERROR THEN
1557
1558 ROLLBACK TO populate_user_attrib;
1559 x_return_status := FND_API.G_RET_STS_ERROR;
1560 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1561 p_data => x_msg_data );
1562
1563 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1564
1565 ROLLBACK TO populate_user_attrib;
1566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1567 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1568 p_data => x_msg_data );
1569
1570 WHEN OTHERS THEN
1571
1572 ROLLBACK TO populate_user_attrib;
1573 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1574
1575 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1576 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1577 END IF;
1578 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1579 p_data => x_msg_data );
1580
1581 END populate_user_attrib;
1582
1583 FUNCTION get_current_user
1584 RETURN NUMBER
1585 IS
1586 BEGIN
1587
1588 return g_current_user_id;
1589
1590 END;
1591
1592 FUNCTION get_current_party
1593 RETURN NUMBER
1594 IS
1595 BEGIN
1596
1597 return g_current_party_id;
1598
1599 END;
1600
1601 PROCEDURE insert_user_attrib(
1602 p_user_id IN NUMBER ,
1603 p_party_id IN NUMBER ,
1604 p_user_attrib_id IN NUMBER ,
1605 p_user_attrib_name IN VARCHAR2,
1606 p_user_attrib_type IN VARCHAR2,
1607 p_static_type IN VARCHAR2,
1608 p_select_text IN VARCHAR2
1609 ) IS
1610
1611 l_select VARCHAR2(32000);
1612 l_select_text VARCHAR2(2000);
1613 l_party_id_arg NUMBER(10) := 0;
1614 l_user_id_arg NUMBER(10) := 0;
1615
1616 BEGIN
1617
1618 -- delete all values for the current user
1619
1620 DELETE FROM igs_sc_usr_att_vals
1621 WHERE user_id = p_user_id
1622 AND user_attrib_id = p_user_attrib_id;
1623
1624 l_select := 'INSERT INTO igs_sc_usr_att_vals (USER_ID,USER_ATTRIB_ID,CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_DATE,ATTR_VALUE )';
1625
1626 -- find is :USER_ID or :PARTY_ID is used in the text
1627 -- replace all PARTY_ID with call to a IGS_SC_GRANTS_PVT.get_current_party function
1628
1629 l_select_text := replace_string(ltrim(p_select_text),':PARTY_ID','IGS_SC_GRANTS_PVT.get_current_party');
1630
1631 l_select_text := replace_string(ltrim(l_select_text),':USER_ID','IGS_SC_GRANTS_PVT.get_current_user');
1632
1633 -- Replace table alias value with our alias, generated based on attribute id
1634
1635 l_select_text := replace_string(ltrim(l_select_text),':TBL_ALIAS','sc'||p_user_attrib_id||'u');
1636
1637 g_current_user_id := p_user_id;
1638
1639 g_current_party_id := p_party_id;
1640
1641
1642 IF p_user_attrib_type = 'F' THEN
1643
1644 l_select := l_select||' SELECT :V1, :V2, sysdate , -1 , -1, sysdate ,'||l_select_text||' FROM dual ';
1645
1646 ELSE --Select of M-valued
1647 -- Remove all spaces from text and remove SELECT word from a text
1648
1649 l_select := l_select||' SELECT :V1, :V2, sysdate , -1 , -1, sysdate ,'||substr(l_select_text,7,32000);
1650
1651 END IF;
1652
1653 Put_Log_Msg ('Executed text is: '||IGS_SC_GRANTS_PVT.get_current_user||' : '||IGS_SC_GRANTS_PVT.get_current_party||':'|| l_select ,0);
1654
1655 -- Execute as it is
1656 EXECUTE IMMEDIATE l_select USING p_user_id,p_user_attrib_id;
1657
1658 END insert_user_attrib;
1659
1660
1661
1662 /******************************************************************
1663 Created By : Arkadi Tereshenkov
1664
1665 Date Created By : Oct 14, 2002
1666
1667 Purpose : Used to generate messages that are to be output
1668 into the concurrent request log.
1669
1670 Remarks :
1671
1672 Change History
1673 Who When What
1674 ------------------------------------------------------------------------
1675
1676 ******************************************************************/
1677 PROCEDURE Generate_Message
1678 IS
1679
1680 l_msg_count NUMBER;
1681 l_msg_data VARCHAR2(2000);
1682
1683 BEGIN
1684
1685 FND_MSg_PUB.Count_And_Get ( p_count => l_msg_count,
1686 p_data => l_msg_data );
1687
1688 IF (l_msg_count > 0) THEN
1689
1690 l_msg_data := '';
1691
1692 FOR l_cur IN 1..l_msg_count LOOP
1693
1694 l_msg_data := FND_MSg_PUB.GET(l_cur, FND_API.g_FALSE);
1695 Put_Log_Msg(l_msg_data,1);
1696 END LOOP;
1697
1698 ELSE
1699
1700 l_msg_data := 'Error Returned but Error stack has no data';
1701 Put_Log_Msg(l_msg_data,1);
1702
1703 END IF;
1704
1705 END Generate_Message;
1706
1707
1708
1709 /******************************************************************
1710 Created By : Arkadi Tereshenkov
1711
1712 Date Created By : Oct 14, 2002
1713
1714 Purpose : Place the messages that have been generated into
1715 the appropriate log file for viewing.
1716
1717 Remarks :
1718
1719 Change History
1720 Who When What
1721 ------------------------------------------------------------------------
1722
1723 ******************************************************************/
1724 PROCEDURE Put_Log_Msg (
1725 p_message IN VARCHAR2,
1726 p_level IN NUMBER
1727 ) IS
1728
1729 l_api_name CONSTANT VARCHAR2(30) := 'Put_Log_Msg';
1730 l_len NUMBER(10) := 1;
1731 BEGIN
1732
1733 -- This procedure outputs messages into the log file Level 0 - System messages. 1 - user messages
1734 IF p_level >= g_debug_level THEN
1735
1736 -- fnd_file.put_line (FND_FILE.LOG,p_message);
1737 LOOP
1738 IF length(p_message) <l_len THEN
1739 EXIT;
1740 END IF;
1741 l_len:=l_len+255;
1742 END LOOP;
1743 END IF;
1744
1745 END Put_Log_Msg;
1746
1747
1748 PROCEDURE unlock_all_grants(
1749 p_api_version IN NUMBER,
1750 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1751 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1752 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1753 p_conc_program_flag IN VARCHAR2 := FND_API.G_FALSE,
1754 p_disable_security IN VARCHAR2 := FND_API.G_FALSE,
1755 p_obj_group_id IN NUMBER,
1756 x_return_status OUT NOCOPY VARCHAR2,
1757 x_msg_count OUT NOCOPY NUMBER,
1758 x_msg_data OUT NOCOPY VARCHAR2
1759 )IS
1760
1761 l_api_name CONSTANT VARCHAR2(30) := 'UNLOCK_ALL_GRANTS';
1762 l_api_version CONSTANT NUMBER := 1.0;
1763 l_return_status VARCHAR2(1);
1764 l_msg_count NUMBER;
1765 l_msg_data VARCHAR2(2000);
1766
1767
1768 CURSOR c_grants IS
1769 SELECT grant_id
1770 FROM igs_sc_grants
1771 WHERE ( obj_group_id = p_obj_group_id OR p_obj_group_id IS NULL)
1772 AND locked_flag ='Y';
1773
1774
1775
1776 BEGIN
1777 -- Standard Start of API savepoint
1778 SAVEPOINT unlock_all_grants;
1779
1780 -- Standard call to check for call compatibility.
1781 IF NOT FND_API.Compatible_API_Call (l_api_version,
1782 p_api_version,
1783 l_api_name,
1784 G_PKG_NAME)
1785 THEN
1786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1787 END IF;
1788
1789 -- Initialize message list if p_init_msg_list is set to TRUE.
1790
1791 x_return_status := FND_API.G_RET_STS_SUCCESS;
1792
1793 IF FND_API.to_Boolean (p_init_msg_list) THEN
1794 FND_MSG_PUB.initialize;
1795 END IF;
1796
1797 -- API body
1798
1799
1800 FOR c_grants_rec IN c_grants LOOP
1801
1802 IGS_SC_DATA_SEC_APIS_PKG.Unlock_Grant (
1803 p_api_version => 1.0,
1804 p_grant_id => c_grants_rec.grant_id,
1805 x_return_status => l_return_status,
1806 x_return_message => l_msg_data );
1807
1808 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1809 raise FND_API.G_EXC_ERROR;
1810 END IF;
1811
1812 END LOOP;
1813
1814 IF FND_API.to_Boolean (p_disable_security) THEN
1815
1816 -- Change default access level for all groups
1817 UPDATE igs_sc_obj_groups
1818 SET default_policy_type = 'G';
1819
1820 END IF;
1821
1822 -- End of API body.
1823 -- Standard check of p_commit.
1824
1825 IF FND_API.To_Boolean( p_commit ) THEN
1826 COMMIT WORK;
1827 END IF;
1828
1829 -- Standard call to get message count and if count is 1, get message info.
1830 FND_MSG_PUB.Count_And_Get
1831 ( p_count => x_msg_count ,
1832 p_data => x_msg_data
1833 );
1834
1835 EXCEPTION
1836 WHEN FND_API.G_EXC_ERROR THEN
1837
1838 ROLLBACK TO unlock_all_grants;
1839 x_return_status := FND_API.G_RET_STS_ERROR;
1840 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1841 p_data => x_msg_data );
1842
1843 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1844
1845 ROLLBACK TO unlock_all_grants;
1846 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1847 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1848 p_data => x_msg_data );
1849
1850 WHEN OTHERS THEN
1851
1852 ROLLBACK TO unlock_all_grants;
1853 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1854
1855 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1856 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1857 END IF;
1858 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1859 p_data => x_msg_data );
1860
1861 END unlock_all_grants;
1862
1863 PROCEDURE lock_all_grants(
1864 p_api_version IN NUMBER,
1865 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1866 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1867 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1868 p_conc_program_flag IN VARCHAR2 := FND_API.G_FALSE,
1869 p_obj_group_id IN NUMBER,
1870 x_return_status OUT NOCOPY VARCHAR2,
1871 x_msg_count OUT NOCOPY NUMBER,
1872 x_msg_data OUT NOCOPY VARCHAR2
1873 ) IS
1874
1875 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_ALL_GRANTS';
1876 l_api_version CONSTANT NUMBER := 1.0;
1877 l_return_status VARCHAR2(1);
1878 l_msg_count NUMBER;
1879 l_msg_data VARCHAR2(2000);
1880
1881
1882 CURSOR c_grants IS
1883 SELECT grant_id
1884 FROM igs_sc_grants
1885 WHERE ( obj_group_id = p_obj_group_id OR p_obj_group_id IS NULL)
1886 AND locked_flag = 'N';
1887
1888
1889
1890 BEGIN
1891 -- Standard Start of API savepoint
1892 SAVEPOINT lock_all_grants;
1893
1894 -- Standard call to check for call compatibility.
1895 IF NOT FND_API.Compatible_API_Call (l_api_version,
1896 p_api_version,
1897 l_api_name,
1898 G_PKG_NAME)
1899 THEN
1900 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1901 END IF;
1902
1903 -- Initialize message list if p_init_msg_list is set to TRUE.
1904
1905 x_return_status := FND_API.G_RET_STS_SUCCESS;
1906
1907 IF FND_API.to_Boolean (p_init_msg_list) THEN
1908 FND_MSG_PUB.initialize;
1909 END IF;
1910
1911 -- API body
1912
1913
1914 FOR c_grants_rec IN c_grants LOOP
1915
1916 IGS_SC_DATA_SEC_APIS_PKG.Lock_Grant (
1917 p_api_version => 1.0,
1918 p_grant_id => c_grants_rec.grant_id,
1919 x_return_status => l_return_status,
1920 x_return_message => l_msg_data );
1921
1922 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1923 raise FND_API.G_EXC_ERROR;
1924 END IF;
1925
1926 END LOOP;
1927
1928
1929 -- End of API body.
1930 -- Standard check of p_commit.
1931
1932 IF FND_API.To_Boolean( p_commit ) THEN
1933 COMMIT WORK;
1934 END IF;
1935
1936 -- Standard call to get message count and if count is 1, get message info.
1937 FND_MSG_PUB.Count_And_Get
1938 ( p_count => x_msg_count ,
1939 p_data => x_msg_data
1940 );
1941
1942 EXCEPTION
1943 WHEN FND_API.G_EXC_ERROR THEN
1944
1945 ROLLBACK TO lock_all_grants;
1946 x_return_status := FND_API.G_RET_STS_ERROR;
1947 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1948 p_data => x_msg_data );
1949
1950 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1951
1952 ROLLBACK TO lock_all_grants;
1953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1954 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1955 p_data => x_msg_data );
1956
1957 WHEN OTHERS THEN
1958
1959 ROLLBACK TO lock_all_grants;
1960 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1961
1962 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1963 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1964 END IF;
1965 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1966 p_data => x_msg_data );
1967 END lock_all_grants;
1968
1969
1970 FUNCTION admin_mode
1971 RETURN VARCHAR2 IS
1972 BEGIN
1973 RETURN 'N';
1974 END admin_mode;
1975
1976
1977 PROCEDURE put_d_message (
1978 p_message IN VARCHAR2,
1979 p_level IN NUMBER,
1980 l_file_ptr UTL_FILE.FILE_TYPE
1981 ) IS
1982
1983 BEGIN
1984
1985 IF p_level <= g_debug_level THEN
1986
1987 UTL_FILE.PUT_LINE ( l_file_ptr, p_message);
1988 UTL_FILE.FFLUSH ( l_file_ptr );
1989
1990 END IF;
1991
1992 END put_d_message;
1993
1994 FUNCTION format_string (
1995 col1 IN VARCHAR2 :='',
1996 len1 IN NUMBER :=0,
1997 col2 IN VARCHAR2 :='',
1998 len2 IN NUMBER :=0,
1999 col3 IN VARCHAR2 :='',
2000 len3 IN NUMBER :=0,
2001 col4 IN VARCHAR2 :='',
2002 len4 IN NUMBER :=0,
2003 col5 IN VARCHAR2 :='',
2004 len5 IN NUMBER :=0,
2005 col6 IN VARCHAR2 :='',
2006 len6 IN NUMBER :=0,
2007 col7 IN VARCHAR2 :='',
2008 len7 IN NUMBER :=0,
2009 col8 IN VARCHAR2 :='',
2010 len8 IN NUMBER :=0,
2011 col9 IN VARCHAR2 :='',
2012 len9 IN NUMBER :=0,
2013 col10 IN VARCHAR2 :='',
2014 len10 IN NUMBER :=0
2015 ) RETURN VARCHAR2 IS
2016 l_space VARCHAR2(80):=' ';
2017 BEGIN
2018
2019 RETURN substr(col1||l_space,1,len1)||substr(col2||l_space,1,len2)||substr(col3||l_space,1,len3)||substr(col4||l_space,1,len4)||
2020 substr(col5||l_space,1,len5)||substr(col6||l_space,1,len6)||substr(col7||l_space,1,len7)||substr(col8||l_space,1,len8)
2021 ||substr(col9||l_space,1,len9)||substr(col10||l_space,1,len10);
2022
2023 END format_string;
2024
2025 PROCEDURE run_diagnostic(
2026 p_dirpath IN VARCHAR2,
2027 p_file_name IN VARCHAR2,
2028 p_log_level IN VARCHAR2,
2029 p_user_id IN NUMBER,
2030 x_return_status OUT NOCOPY VARCHAR2,
2031 x_msg_count OUT NOCOPY NUMBER,
2032 x_msg_data OUT NOCOPY VARCHAR2
2033 ) IS
2034 /*******************************************************************************
2035 Change History: (who, when, what: NO CREATION RECORDS HERE!)
2036 Who When What
2037 skpandey 12-JAN-2006 Bug#4937960
2038 Stubbed the procedure as it is never used
2039 *******************************************************************************/
2040 BEGIN
2041
2042 Null;
2043
2044 END run_diagnostic;
2045
2046
2047
2048 FUNCTION check_attrib_text (
2049 p_table_name VARCHAR2,
2050 p_select_text VARCHAR2,
2051 p_obj_attrib_type VARCHAR2 )
2052
2053 RETURN BOOLEAN IS
2054 l_select_text VARCHAR2(32000);
2055 BEGIN
2056 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2057 l_label := 'igs.plsql.igs_sc_grants_pvt.check_attrib_text';
2058 l_debug_str := 'Table Name: '||p_table_name||','||'Select Text: '||p_select_text||','||'Object Attribute Type: '||p_obj_attrib_type;
2059 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
2060 END IF;
2061
2062 IF p_obj_attrib_type = 'F' THEN
2063
2064 l_select_text := ' EXISTS ( SELECT '||p_select_text||' FROM DUAL ) ';
2065
2066 ELSIF p_obj_attrib_type = 'T' THEN
2067
2068 l_select_text := p_select_text||' IS NOT NULL ';
2069
2070 ELSE
2071 -- Select statment. Add EXIST
2072 l_select_text := ' EXISTS ( '||p_select_text||' )';
2073
2074 END IF;
2075
2076 RETURN check_grant_text ( p_table_name, l_select_text );
2077
2078 END check_attrib_text;
2079
2080 END IGS_SC_GRANTS_PVT;
2081
[Home] [Help]