[Home] [Help]
PACKAGE BODY: APPS.IGS_SC_GEN_001
Source
1 PACKAGE BODY IGS_SC_GEN_001 AS
2 /* $Header: IGSSC06B.pls 120.7 2006/05/30 10:02:47 vskumar noship $ */
3
4 /******************************************************************
5 Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 All rights reserved.
7 Created By : Uma Maheswari
8 Date Created By : May 16, 2005
9 Purpose : This package is to be used for the processing of the security process for Oracle
10 Student System.
11 remarks : None
12 Change History
13
14 Who When What
15 mmkumar 24-jun-2005 modified check_ins_security to use bind variables
16 mmkumar 26-JUL-2005 Modified check_ins_security to remove extra bracket and removed the quotes around hard-coded value
17 gmaheswa 4-May-2006 Modified size of var l_out_string, l_upper_string in Replace String Method to 32000, as select stmt can be 32000.
18 -----------------------------------------------------------
19
20 ******************************************************************/
21 -- -----------------------------------------------------------------
22 -- Define the global variables to be used in this package.
23 -- -----------------------------------------------------------------
24
25 TYPE g_grant_cond_rec IS RECORD (obj_attrib_id igs_sc_grant_conds.obj_attrib_id%TYPE,
26 user_attrib_id igs_sc_grant_conds.user_attrib_id%TYPE,
27 condition igs_sc_grant_conds.condition%TYPE,
28 text_value igs_sc_grant_conds.text_value%TYPE,
29 user_attrib_value igs_sc_usr_att_vals.attr_value%TYPE,
30 obj_attrib_value igs_sc_obj_att_vals.attr_value%TYPE,
31 cond_text VARCHAR2(4000),
32 obj_const VARCHAR2(4000),
33 close_part VARCHAR2(100),
34 z_typ_flag VARCHAR2(1) --mmkumar
35 );
36 TYPE g_grant_conds_t IS TABLE OF g_grant_cond_rec INDEX BY BINARY_INTEGER;
37 g_debug_level NUMBER(1) := 0;
38
39 l_prog_label CONSTANT VARCHAR2(500) :='igs.plsql.igs_sc_gen_001';
40 l_request_id NUMBER;
41 l_label VARCHAR2(4000);
42 l_debug_str VARCHAR2(32000);
43 --mmkumar,
44 onlyZTypeAttributes BOOLEAN := true;
45 l_grant_text VARCHAR2(4000);
46 l_bodmas_grant_text VARCHAR2(4000);
47
48 FUNCTION check_operation (p_operation VARCHAR2
49 ) RETURN VARCHAR2
50 IS
51 l_operation VARCHAR2(25);
52 BEGIN
53 l_operation:= p_operation;
54 IF p_operation = '>' THEN
55 l_operation := '<';
56 ELSIF p_operation ='<' THEN
57 l_operation := '>';
58 ELSIF p_operation = '>=' THEN
59 l_operation := '<=';
60 ELSIF p_operation = '<=' THEN
61 l_operation := '>=';
62 END IF;
63 RETURN l_operation;
64 END check_operation;
65
66 FUNCTION replace_string(p_string IN VARCHAR2,
67 p_from_pattern IN VARCHAR2,
68 p_to_pattern IN VARCHAR2
69 ) RETURN VARCHAR2
70 IS
71 l_out_string VARCHAR2(32000);
72 l_upper_string VARCHAR2(32000);
73 l_occurence NUMBER(10) := 0;
74 l_len NUMBER(5);
75 BEGIN
76 IF UPPER(p_from_pattern) = UPPER(p_to_pattern) THEN
77 --check for being the same value, infinite loop
78 RETURN p_string;
79 END IF;
80 -- delete all values for the current user
81 l_out_string := p_string;
82 l_upper_string := UPPER(l_out_string);
83 l_len := LENGTH(p_from_pattern);
84 l_occurence := INSTR(l_upper_string,p_from_pattern,1,1);
85 LOOP
86 IF l_occurence = 0
87 THEN
88 -- no more found exit
89 EXIT;
90 END IF;
91 l_out_string := SUBSTR(l_out_string,1,l_occurence-1)||p_to_pattern||SUBSTR(l_out_string,l_occurence+l_len,32000);
92 l_upper_string := UPPER(l_out_string);
93 -- find next
94 l_occurence := INSTR(l_upper_string,p_from_pattern,1,1);
95 END LOOP;
96 RETURN l_out_string;
97 END replace_string;
98
99 FUNCTION check_grant_text (p_table_name VARCHAR2,
100 p_select_text VARCHAR2
101 )RETURN BOOLEAN
102 IS
103 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_GRANT_TEXT';
104 l_val NUMBER(20);
105 l_select_text VARCHAR(32000);
106
107 BEGIN
108 l_select_text := replace_string(LTRIM(p_select_text),':PARTY_ID','igs_sc_vars.get_partyid');
109 l_select_text := replace_string(LTRIM(l_select_text),':USER_ID','igs_sc_vars.get_userid');
110 l_select_text := replace_string(LTRIM(l_select_text),':TBL_ALIAS','tstal');
111 EXECUTE IMMEDIATE 'SELECT count(*) FROM ('||' SELECT 1 FROM '||p_table_name||' WHERE '||l_select_text||' )' INTO l_val;
112 RETURN TRUE;
113
114 EXCEPTION
115 WHEN OTHERS THEN
116 RETURN FALSE;
117 END check_grant_text;
118
119 FUNCTION check_ins_security(
120 p_bo_name IN VARCHAR2,
121 p_object_name IN VARCHAR2,
122 p_attrib_tab IN attrib_rec,
123 p_msg_data OUT NOCOPY VARCHAR2)
124 RETURN BOOLEAN IS
125 ------------------------------------------------------------------
126 --Updated by : gmaheswa, Oracle India
127 --Date created: 27-MAY-2001
128 --
129 --Purpose: code replication of generate/build grant in SC02.
130 -- need to form the sql of predicate clause, evaluate that by replacing the values for attribs passed.
131 -- p_attrib_tab has all user attibs passed, ...
132
133 --Change History:
134 ------------------------------------------------------------------
135 l_statment VARCHAR2(32000); -- Grant statment
136 l_stat_found BOOLEAN := FALSE;
137 l_grant_cond g_grant_conds_t;
138 l_user_attrib igs_sc_usr_attribs%ROWTYPE;
139 l_obj_attrib igs_sc_obj_attribs%ROWTYPE;
140 l_cur_pos NUMBER(10);
141 l_found_pos NUMBER(10);
142 l_cur_num NUMBER(1);
143 l_alias_name VARCHAR2(8);
144 l_attr_select VARCHAR2(32000);
145 l_usr_select VARCHAR2(32000);
146 l_obj_select VARCHAR2(32000);
147 l_obj_const VARCHAR2(32000);
148 l_usr_const VARCHAR2(32000);
149 l_obj_alias VARCHAR2(25);
150 l_usr_alias VARCHAR2(25);
151 l_column_name VARCHAR2(255);
152 l_finally VARCHAR2(32000);
153 l_post_grant VARCHAR2(255);
154 l_select VARCHAR2(32000);
155 l_where VARCHAR2(32000);
156 l_Ext_Cursor NUMBER;
157 l_SelectStatement VARCHAR2(32000);
158 l_output NUMBER;
159 lnRows NUMBER(5);
160
161 CURSOR c_get_grant( cp_object VARCHAR2, cp_user_id NUMBER) IS
162 SELECT gr.grant_id ,
163 obj.object_id,
164 obj.obj_group_id ,
165 gr.grant_text
166 FROM igs_sc_grants gr,
167 igs_sc_objects obj,
168 fnd_objects fnd,
169 wf_local_user_roles rls
170 WHERE gr.grant_insert_flag='Y'
171 AND gr.locked_flag ='Y'
172 AND obj.obj_group_id = gr.obj_group_id
173 AND obj.object_id = fnd.object_id
174 AND fnd.application_id IN (8405,8406)
175 AND UPPER( fnd.obj_name) = cp_object
176 AND rls.user_orig_system_id = cp_user_id
177 AND rls.role_orig_system = 'IGS'
178 AND rls.role_orig_system_id = gr.user_group_id
179 AND SYSDATE BETWEEN NVL(rls.start_date,SYSDATE-1) AND NVL(rls.expiration_date,SYSDATE+1)
180 ORDER BY gr.grant_id ;
181
182 CURSOR c_grant_where (s_grant_id NUMBER, s_obj_id NUMBER) IS
183 SELECT grant_where
184 FROM igs_sc_obj_grants
185 WHERE object_id = s_obj_id
186 AND grant_id = s_grant_id
187 FOR UPDATE OF grant_where;
188
189 -- Select of all conditions for a grant
190 CURSOR c_grant_cond (s_grant_id NUMBER )IS
191 SELECT grant_id,
192 grant_cond_num,
193 obj_attrib_id,
194 user_attrib_id,
195 condition,
196 text_value
197 FROM igs_sc_grant_conds
198 WHERE grant_id = s_grant_id;
199
200 -- Get user attributes.
201 CURSOR c_user_attrib (s_attrib_id NUMBER) IS
202 SELECT *
203 FROM igs_sc_usr_attribs
204 WHERE user_attrib_id = s_attrib_id;
205
206 CURSOR c_obj_attrib ( s_obj_attrib_id NUMBER, s_obj_group_id NUMBER) IS
207 SELECT *
208 FROM igs_sc_obj_attribs
209 WHERE obj_group_id = s_obj_group_id
210 AND obj_attrib_id= s_obj_attrib_id;
211
212 CURSOR def_grnt(p_obj_name VARCHAR2)
213 IS
214 SELECT b.DEFAULT_POLICY_TYPE
215 FROM igs_sc_objects a,
216 igs_sc_obj_groups b,
217 fnd_objects c
218 WHERE c.obj_name = p_obj_name
219 AND c.object_id = a.object_id
220 AND b.obj_group_id = a.obj_group_id;
221
222 CURSOR c_null_allow_flag (cp_object_id number,cp_obj_attrib_id number) IS
223 SELECT null_allow_flag
224 FROM igs_sc_obj_att_mths
225 WHERE object_id = cp_object_id
226 AND obj_attrib_id = cp_obj_attrib_id;
227
228 cnt NUMBER;
229 l_val NUMBER;
230 l_grants_exist BOOLEAN := FALSE;
231 l_def_gr VARCHAR2(1);
232 l_user_id NUMBER;
233 L_NULL_FLAG VARCHAR2(1) := 'N';
234 l_null_allow varchar2(1);
235
236
237 lv_obj_constant varchar2(4000);
238
239 BEGIN
240
241 fnd_dsql.init;
242
243 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
244 l_label := 'igs.plsql.igs_sc_gen_001.check_ins_security';
245 l_debug_str := P_ATTRIB_TAB.ADVISOR||','||
246 P_ATTRIB_TAB.ADVISOR_PERSON_ID||','||
247 P_ATTRIB_TAB.APPLICATION_PROGRAM_CODE||','||
248 P_ATTRIB_TAB.APPLICATION_TYPE||','||
249 P_ATTRIB_TAB.INSTRUCTOR_ID||','||
250 P_ATTRIB_TAB.INSTRUCTOR_PERSON_ID||','||
251 P_ATTRIB_TAB.LOCATION||','||
252 P_ATTRIB_TAB.NOMINATED_COURSE_CODE||','||
253 P_ATTRIB_TAB.ORGANIZATIONAL_UNIT_CODE||','||
254 P_ATTRIB_TAB.OWNING_ORG_UNIT_CODE||','||
255 P_ATTRIB_TAB.PERSON_ID||','||
256 P_ATTRIB_TAB.PERSON_TYPE||','||
257 P_ATTRIB_TAB.PROGRAM_ATTEMPT_ADVISOR||','||
258 P_ATTRIB_TAB.PROGRAM_ATTEMPT_LOCATION||','||
259 P_ATTRIB_TAB.PROGRAM_ATT_OWNING_ORG_UNIT_CD||','||
260 P_ATTRIB_TAB.PROGRAM_ATT_RESP_ORG_UNIT_CD||','||
261 P_ATTRIB_TAB.PROGRAM_ATTEMPT_TYPE||','||
262 P_ATTRIB_TAB.PROGRAM_OWNING_ORG_UNIT_CODE||','||
263 P_ATTRIB_TAB.PROGRAM_RESP_ORG_UNIT_CODE||','||
264 P_ATTRIB_TAB.PROGRAM_TYPE||','||
265 P_ATTRIB_TAB.RESPONSIBLE_ORG_UNIT_CODE||','||
266 P_ATTRIB_TAB.TEACHING_ORG_UNIT_CODE||','||
267 P_ATTRIB_TAB.UNIT_LOCATION||','||
268 P_ATTRIB_TAB.UNIT_MODE||','||
269 P_ATTRIB_TAB.UNIT_ATT_ORG_UNIT_CODE||','||
270 P_ATTRIB_TAB.UNIT_ATTEMPT_LOCATION||','||
271 P_ATTRIB_TAB.UNIT_ATTEMPT_INSTRUCTOR||','||
272 P_ATTRIB_TAB.UNIT_ATTEMPT_MODE||','||
273 P_ATTRIB_TAB.OTHER_UNIT_ORG_UNIT_CODE||','||
274 P_ATTRIB_TAB.OTHER_UNIT_LOCATION||','||
275 P_ATTRIB_TAB.OTHER_UNIT_INSTRUCTOR||','||
276 P_ATTRIB_TAB.OTHER_UNIT_MODE;
277 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
278 END IF;
279
280 l_user_id := FND_GLOBAL.USER_ID;
281 --l_statment := 'SELECT 1 FROM DUAL WHERE ';
282 fnd_dsql.add_text('SELECT 1 FROM DUAL WHERE ');
283
284 -- For the object, pick up all the grants.
285 FOR grants_rec IN c_get_grant(p_object_name,l_user_id)
286 LOOP
287 l_grants_exist := TRUE;
288 l_finally := '';
289 onlyZTypeAttributes := TRUE;
290 --
291 --
292 -- For each condition in grant
293 --code added by mmkumar
294 FOR cc_grant_cond_rec IN c_grant_cond(grants_rec.grant_id) LOOP
295 IF cc_grant_cond_rec.obj_attrib_id IS NOT NULL THEN
296 OPEN c_null_allow_flag( grants_rec.object_id, cc_grant_cond_rec.obj_attrib_id);
297 FETCH c_null_allow_flag INTO l_null_allow;
298
299 IF c_null_allow_flag%NOTFOUND THEN
300 -- Method for the table not found
301 close c_null_allow_flag;
302 ELSIF l_null_allow IN ('Y','N') THEN
303 onlyZTypeAttributes := false;
304 close c_null_allow_flag;
305 EXIT;
306 END IF;
307
308 IF C_NULL_ALLOW_FLAG%ISOPEN THEN
309 CLOSE c_null_allow_flag;
310 END IF;
311 END IF;
312 END LOOP;
313 --code added by mmkumar ends
314 --
315 --
316
317 --** Statement level logging.
318 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
319 l_label := 'igs.plsql.igs_sc_gen_001.check_ins_security';
320 if onlyZTypeAttributes then
321 l_debug_str := 'onlyZTypeAttributes : true ';
322 else
323 l_debug_str := 'onlyZTypeAttributes : false ';
324 end if;
325 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
326 END IF;
327 --**
328
329 FOR c_grant_cond_rec IN c_grant_cond(grants_rec.grant_id) LOOP
330 l_grant_cond(c_grant_cond_rec.grant_cond_num).obj_attrib_id := c_grant_cond_rec.obj_attrib_id;
331 l_grant_cond(c_grant_cond_rec.grant_cond_num).user_attrib_id := c_grant_cond_rec.user_attrib_id;
332 l_grant_cond(c_grant_cond_rec.grant_cond_num).condition := c_grant_cond_rec.condition;
333 l_grant_cond(c_grant_cond_rec.grant_cond_num).text_value := c_grant_cond_rec.text_value;
334 l_grant_cond(c_grant_cond_rec.grant_cond_num).obj_const := null;
335 l_usr_select := '';
336 l_obj_const := '';
337 l_usr_const := '';
338 l_usr_alias := 'sc'||grants_rec.grant_id||'u'||c_grant_cond_rec.grant_cond_num||'a';
339 L_OBJ_SELECT := '';
340
341 -- Construct select stmt from the fetched grant condition
342 IF l_grant_cond(c_grant_cond_rec.grant_cond_num).obj_attrib_id IS NOT NULL THEN
343 -- Get the object attribute name for the object attribute ID.
344 OPEN c_obj_attrib (l_grant_cond(c_grant_cond_rec.grant_cond_num).obj_attrib_id ,grants_rec.obj_group_id );
345 FETCH c_obj_attrib INTO l_obj_attrib;
346 CLOSE c_obj_attrib;
347
348 --code added by mmkumar
349 OPEN c_null_allow_flag( grants_rec.object_id, c_grant_cond_rec.obj_attrib_id);
350 FETCH c_null_allow_flag INTO l_null_allow;
351 l_grant_cond(c_grant_cond_rec.grant_cond_num).z_typ_flag := l_null_allow;
352 CLOSE c_null_allow_flag;
353 -- code added by mmkumar ends
354
355 --Read the PL/SQL table and match the attribute name.
356 IF UPPER(l_obj_attrib.obj_attrib_name) = 'ADVISOR' THEN
357 IF(INSTR(P_ATTRIB_TAB.ADVISOR,'SELECT') > 0) THEN
358 L_OBJ_SELECT := P_ATTRIB_TAB.ADVISOR;
359 ELSE
360 L_OBJ_CONST := P_ATTRIB_TAB.ADVISOR;
361 END IF;
362 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'ADVISOR PERSON ID' THEN
363 IF(INSTR(P_ATTRIB_TAB.ADVISOR_PERSON_ID,'SELECT') > 0) THEN
364 L_OBJ_SELECT := P_ATTRIB_TAB.ADVISOR_PERSON_ID;
365 ELSE
366 L_OBJ_CONST := P_ATTRIB_TAB.ADVISOR_PERSON_ID;
367 END IF;
368 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'APPLICATION PROGRAM CODE' THEN
369 IF(INSTR(P_ATTRIB_TAB.APPLICATION_PROGRAM_CODE,'SELECT') > 0) THEN
370 L_OBJ_SELECT := P_ATTRIB_TAB.APPLICATION_PROGRAM_CODE;
371 ELSE
372 L_OBJ_CONST := P_ATTRIB_TAB.APPLICATION_PROGRAM_CODE;
373 END IF;
374 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'APPLICATION TYPE' THEN
375 IF(INSTR(P_ATTRIB_TAB.APPLICATION_TYPE,'SELECT') > 0) THEN
376 L_OBJ_SELECT := P_ATTRIB_TAB.APPLICATION_TYPE;
377 ELSE
378 L_OBJ_CONST := P_ATTRIB_TAB.APPLICATION_TYPE;
379 END IF;
380 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'INSTRUCTOR ID' THEN
381 IF(INSTR(P_ATTRIB_TAB.INSTRUCTOR_ID,'SELECT') > 0) THEN
382 L_OBJ_SELECT := P_ATTRIB_TAB.INSTRUCTOR_ID;
383 ELSE
384 L_OBJ_CONST := P_ATTRIB_TAB.INSTRUCTOR_ID;
385 END IF;
386 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'INSTRUCTOR PERSON ID' THEN
387 IF(INSTR(P_ATTRIB_TAB.INSTRUCTOR_PERSON_ID,'SELECT') > 0) THEN
388 L_OBJ_SELECT := P_ATTRIB_TAB.INSTRUCTOR_PERSON_ID;
389 ELSE
390 L_OBJ_CONST := P_ATTRIB_TAB.INSTRUCTOR_PERSON_ID;
391 END IF;
392 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'LOCATION' THEN
393 IF(INSTR(P_ATTRIB_TAB.LOCATION,'SELECT') > 0) THEN
394 L_OBJ_SELECT := P_ATTRIB_TAB.LOCATION;
395 ELSE
396 L_OBJ_CONST := P_ATTRIB_TAB.LOCATION;
397 END IF;
398 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'NOMINATED COURSE CODE' THEN
399 IF(INSTR(P_ATTRIB_TAB.NOMINATED_COURSE_CODE,'SELECT') > 0) THEN
400 L_OBJ_SELECT := P_ATTRIB_TAB.NOMINATED_COURSE_CODE;
401 ELSE
402 L_OBJ_CONST := P_ATTRIB_TAB.NOMINATED_COURSE_CODE;
403 END IF;
404 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'ORGANIZATIONAL UNIT CODE' THEN
405 IF(INSTR(P_ATTRIB_TAB.ORGANIZATIONAL_UNIT_CODE,'SELECT') > 0) THEN
406 L_OBJ_SELECT := P_ATTRIB_TAB.ORGANIZATIONAL_UNIT_CODE;
407 ELSE
408 L_OBJ_CONST := P_ATTRIB_TAB.ORGANIZATIONAL_UNIT_CODE;
409 END IF;
410 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'OWNING ORGANIZATIONAL UNIT CODE' THEN
411 IF(INSTR(P_ATTRIB_TAB.OWNING_ORG_UNIT_CODE,'SELECT') > 0) THEN
412 L_OBJ_SELECT := P_ATTRIB_TAB.OWNING_ORG_UNIT_CODE;
413 ELSE
414 L_OBJ_CONST := P_ATTRIB_TAB.OWNING_ORG_UNIT_CODE;
415 END IF;
416 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'PERSON ID' THEN
417 IF(INSTR(P_ATTRIB_TAB.PERSON_ID,'SELECT') > 0) THEN
418 L_OBJ_SELECT := P_ATTRIB_TAB.PERSON_ID;
419 ELSE
420 L_OBJ_CONST := P_ATTRIB_TAB.PERSON_ID;
421 END IF;
422 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'PERSON TYPE' THEN
423 IF(INSTR(P_ATTRIB_TAB.PERSON_TYPE,'SELECT') > 0) THEN
424 L_OBJ_SELECT := P_ATTRIB_TAB.PERSON_TYPE;
425 ELSE
426 L_OBJ_CONST := P_ATTRIB_TAB.PERSON_TYPE;
427 END IF;
428 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'PROGRAM ATTEMPT ADVISOR' THEN
429 IF(INSTR(P_ATTRIB_TAB.PROGRAM_ATTEMPT_ADVISOR,'SELECT') > 0) THEN
430 L_OBJ_SELECT := P_ATTRIB_TAB.PROGRAM_ATTEMPT_ADVISOR;
431 ELSE
432 L_OBJ_CONST := P_ATTRIB_TAB.PROGRAM_ATTEMPT_ADVISOR;
433 END IF;
434 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'PROGRAM ATTEMPT LOCATION' THEN
435 IF(INSTR(P_ATTRIB_TAB.PROGRAM_ATTEMPT_LOCATION,'SELECT') > 0) THEN
436 L_OBJ_SELECT := P_ATTRIB_TAB.PROGRAM_ATTEMPT_LOCATION;
437 ELSE
438 L_OBJ_CONST := P_ATTRIB_TAB.PROGRAM_ATTEMPT_LOCATION;
439 END IF;
440 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'PROGRAM ATTEMPT OWNING ORGANIZATIONAL UNIT CODE' THEN
441 IF(INSTR(P_ATTRIB_TAB.PROGRAM_ATT_OWNING_ORG_UNIT_CD,'SELECT') > 0) THEN
442 L_OBJ_SELECT := P_ATTRIB_TAB.PROGRAM_ATT_OWNING_ORG_UNIT_CD;
443 ELSE
444 L_OBJ_CONST := P_ATTRIB_TAB.PROGRAM_ATT_OWNING_ORG_UNIT_CD;
445 END IF;
446 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'PROGRAM ATTEMPT RESPONSIBLE ORGANIZATIONAL UNIT CODE' THEN
447 IF(INSTR(P_ATTRIB_TAB.PROGRAM_ATT_RESP_ORG_UNIT_CD,'SELECT') > 0) THEN
448 L_OBJ_SELECT := P_ATTRIB_TAB.PROGRAM_ATT_RESP_ORG_UNIT_CD;
449 ELSE
450 L_OBJ_CONST := P_ATTRIB_TAB.PROGRAM_ATT_RESP_ORG_UNIT_CD;
451 END IF;
452 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'PROGRAM ATTEMPT TYPE' THEN
453 IF(INSTR(P_ATTRIB_TAB.PROGRAM_ATTEMPT_TYPE,'SELECT') > 0) THEN
454 L_OBJ_SELECT := P_ATTRIB_TAB.PROGRAM_ATTEMPT_TYPE;
455 ELSE
456 L_OBJ_CONST := P_ATTRIB_TAB.PROGRAM_ATTEMPT_TYPE;
457 END IF;
458 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'PROGRAM OWNING ORGANIZATIONAL UNIT CODE' THEN
459 IF(INSTR(P_ATTRIB_TAB.PROGRAM_OWNING_ORG_UNIT_CODE,'SELECT') > 0) THEN
460 L_OBJ_SELECT := P_ATTRIB_TAB.PROGRAM_OWNING_ORG_UNIT_CODE;
461 ELSE
462 L_OBJ_CONST := P_ATTRIB_TAB.PROGRAM_OWNING_ORG_UNIT_CODE;
463 END IF;
464 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'PROGRAM RESPONSIBLE ORGANIZATIONAL UNIT CODE' THEN
465 IF(INSTR(P_ATTRIB_TAB.PROGRAM_RESP_ORG_UNIT_CODE,'SELECT') > 0) THEN
466 L_OBJ_SELECT := P_ATTRIB_TAB.PROGRAM_RESP_ORG_UNIT_CODE;
467 ELSE
468 L_OBJ_CONST := P_ATTRIB_TAB.PROGRAM_RESP_ORG_UNIT_CODE;
469 END IF;
470 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'PROGRAM TYPE' THEN
471 IF(INSTR(P_ATTRIB_TAB.PROGRAM_TYPE,'SELECT') > 0) THEN
472 L_OBJ_SELECT := P_ATTRIB_TAB.PROGRAM_TYPE;
473 ELSE
474 L_OBJ_CONST := P_ATTRIB_TAB.PROGRAM_TYPE;
475 END IF;
476 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'RESPONSIBLE ORGANIZATIONAL UNIT CODE' THEN
477 IF(INSTR(P_ATTRIB_TAB.RESPONSIBLE_ORG_UNIT_CODE,'SELECT') > 0) THEN
478 L_OBJ_SELECT := P_ATTRIB_TAB.RESPONSIBLE_ORG_UNIT_CODE;
479 ELSE
480 L_OBJ_CONST := P_ATTRIB_TAB.RESPONSIBLE_ORG_UNIT_CODE;
481 END IF;
482 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'TEACHING ORGANIZATIONAL UNIT CODE' THEN
483 IF(INSTR(P_ATTRIB_TAB.TEACHING_ORG_UNIT_CODE,'SELECT') > 0) THEN
484 L_OBJ_SELECT := P_ATTRIB_TAB.TEACHING_ORG_UNIT_CODE;
485 ELSE
486 L_OBJ_CONST := P_ATTRIB_TAB.TEACHING_ORG_UNIT_CODE;
487 END IF;
488 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'UNIT LOCATION' THEN
489 IF(INSTR(P_ATTRIB_TAB.UNIT_LOCATION,'SELECT') > 0) THEN
490 L_OBJ_SELECT := P_ATTRIB_TAB.UNIT_LOCATION;
491 ELSE
492 L_OBJ_CONST := P_ATTRIB_TAB.UNIT_LOCATION;
493 END IF;
494 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'UNIT MODE' THEN
495 IF(INSTR(P_ATTRIB_TAB.UNIT_MODE,'SELECT') > 0) THEN
496 L_OBJ_SELECT := P_ATTRIB_TAB.UNIT_MODE;
497 ELSE
498 L_OBJ_CONST := P_ATTRIB_TAB.UNIT_MODE;
499 END IF;
500 ELSIF UPPER(L_OBJ_ATTRIB.OBJ_ATTRIB_NAME) = 'UNIT ATTEMPT ORGANIZATIONAL UNIT CODE' THEN
501 IF(INSTR(P_ATTRIB_TAB.UNIT_ATT_ORG_UNIT_CODE,'SELECT') > 0) THEN
502 L_OBJ_SELECT := P_ATTRIB_TAB.UNIT_ATT_ORG_UNIT_CODE;
503 ELSE
504 L_OBJ_CONST := P_ATTRIB_TAB.UNIT_ATT_ORG_UNIT_CODE;
505 END IF;
506 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'UNIT ATTEMPT LOCATION' THEN
507 IF(INSTR(P_ATTRIB_TAB.UNIT_ATTEMPT_LOCATION,'SELECT') > 0) THEN
508 L_OBJ_SELECT := P_ATTRIB_TAB.UNIT_ATTEMPT_LOCATION;
509 ELSE
510 L_OBJ_CONST := P_ATTRIB_TAB.UNIT_ATTEMPT_LOCATION;
511 END IF;
512 ELSIF UPPER(L_OBJ_ATTRIB.OBJ_ATTRIB_NAME) = 'UNIT ATTEMPT INSTRUCTOR' THEN
513 IF(INSTR(P_ATTRIB_TAB.UNIT_ATTEMPT_INSTRUCTOR,'SELECT') > 0) THEN
514 L_OBJ_SELECT := P_ATTRIB_TAB.UNIT_ATTEMPT_INSTRUCTOR;
515 ELSE
516 L_OBJ_CONST := P_ATTRIB_TAB.UNIT_ATTEMPT_INSTRUCTOR;
517 END IF;
518 ELSIF UPPER(L_OBJ_ATTRIB.OBJ_ATTRIB_NAME) = 'UNIT ATTEMPT MODE' THEN
519 IF(INSTR(P_ATTRIB_TAB.UNIT_ATTEMPT_MODE,'SELECT') > 0) THEN
520 L_OBJ_SELECT := P_ATTRIB_TAB.UNIT_ATTEMPT_MODE;
521 ELSE
522 L_OBJ_CONST := P_ATTRIB_TAB.UNIT_ATTEMPT_MODE;
523 END IF;
524 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'OTHER UNIT ORGANIZATIONAL UNIT CODE' THEN
525 IF(INSTR(P_ATTRIB_TAB.OTHER_UNIT_ORG_UNIT_CODE,'SELECT') > 0) THEN
526 L_OBJ_SELECT := P_ATTRIB_TAB.OTHER_UNIT_ORG_UNIT_CODE;
527 ELSE
528 L_OBJ_CONST := P_ATTRIB_TAB.OTHER_UNIT_ORG_UNIT_CODE;
529 END IF;
530 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'OTHER UNIT LOCATION' THEN
531 IF(INSTR(P_ATTRIB_TAB.OTHER_UNIT_LOCATION,'SELECT') > 0) THEN
532 L_OBJ_SELECT := P_ATTRIB_TAB.OTHER_UNIT_LOCATION;
533 ELSE
534 L_OBJ_CONST := P_ATTRIB_TAB.OTHER_UNIT_LOCATION;
535 END IF;
536 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'OTHER UNIT INSTRUCTOR' THEN
537 IF(INSTR(P_ATTRIB_TAB.OTHER_UNIT_INSTRUCTOR,'SELECT') > 0) THEN
538 L_OBJ_SELECT := P_ATTRIB_TAB.OTHER_UNIT_INSTRUCTOR;
539 ELSE
540 L_OBJ_CONST := P_ATTRIB_TAB.OTHER_UNIT_INSTRUCTOR;
541 END IF;
542 ELSIF UPPER(l_obj_attrib.obj_attrib_name) = 'OTHER UNIT MODE' THEN
543 IF(INSTR(P_ATTRIB_TAB.OTHER_UNIT_MODE,'SELECT') > 0) THEN
544 L_OBJ_SELECT := P_ATTRIB_TAB.OTHER_UNIT_MODE;
545 ELSE
546 L_OBJ_CONST := P_ATTRIB_TAB.OTHER_UNIT_MODE;
547 END IF;
548 END IF;
549
550 IF l_obj_const IS NOT NULL THEN l_obj_const := ''''||l_obj_const||''''; END IF;
551
552 ELSE
553 -- Object attr is null, use the text_Value as the default one.
554 l_obj_const := l_grant_cond(c_grant_cond_rec.grant_cond_num).text_value;
555 END IF;
556
557 IF c_grant_cond_rec.user_attrib_id IS NOT NULL THEN
558 -- read attribute definition
559 OPEN c_user_attrib ( l_grant_cond(c_grant_cond_rec.grant_cond_num).user_attrib_id );
560 FETCH c_user_attrib INTO l_user_attrib;
561 CLOSE c_user_attrib;
562
563 -- If dynamic attribute - get value for dynamic C - constant, S - static, D - dynamic
564 -- Check for being multi-value attribute. T Table column name, S select statement, F Function call, M - multy values - select only
565 IF l_user_attrib.static_type = 'D' AND l_user_attrib.user_attrib_type <> 'F' THEN
566 -- Dynamic attribute - we need to append the actual select for this attribute
567 l_attr_select := replace_string(ltrim(l_user_attrib.select_text),':PARTY_ID','igs_sc_vars.get_partyid');
568 l_attr_select := replace_string(ltrim(l_attr_select),':USER_ID','igs_sc_vars.get_userid');
569 -- Replace table alias value with our alias, generated based on attribute id
570 l_usr_select := replace_string(ltrim(l_attr_select),':TBL_ALIAS',l_usr_alias);
571
572 ELSIF l_user_attrib.static_type = 'D' AND l_user_attrib.user_attrib_type = 'F' THEN
573 l_attr_select := replace_string(ltrim(l_user_attrib.select_text),':PARTY_ID','igs_sc_vars.get_partyid');
574 l_usr_const := replace_string(ltrim(l_attr_select),':USER_ID','igs_sc_vars.get_userid');
575
576 ELSIF l_user_attrib.user_attrib_type = 'M' THEN
577 -- If yes then construct for multi-value attribute
578 -- Add select from values table
579 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 '
580 ||l_usr_alias||'.user_attrib_id='||c_grant_cond_rec.user_attrib_id;
581 ELSE
582 --Simply get value for an attribute using API and append
583 l_usr_const := 'igs_sc_vars.get_att('||c_grant_cond_rec.user_attrib_id||')';
584 END IF; -- Multy value attribute end
585
586 ELSE --Add Text instead of parameter
587 l_usr_const := l_grant_cond(c_grant_cond_rec.grant_cond_num).text_value;
588
589 END IF; -- User parameter id null
590
591 -- l_usr_const, l_obj_const - function or text value of any kind
592 -- l_obj_select, l_usr_select - select statments.
593
594 --** Statement level logging.
595 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
596 l_label := 'igs.plsql.igs_sc_gen_001.check_ins_security';
597 l_debug_str := 'l_obj_select is : '|| l_obj_select || ' and l_obj_const is ' || l_obj_const;
598 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
599 END IF;
600 --**
601
602
603 --** Statement level logging.
604 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
605 l_label := 'igs.plsql.igs_sc_gen_001.check_ins_security';
606 l_debug_str := 'l_usr_select is : '|| l_usr_select || ' and l_usr_const is ' || l_usr_const;
607 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
608 END IF;
609 --**
610
611 OPEN c_null_allow_flag(grants_rec.OBJECT_ID,l_grant_cond(c_grant_cond_rec.grant_cond_num).obj_attrib_id);
612 FETCH c_null_allow_flag INTO L_NULL_FLAG;
613 CLOSE c_null_allow_flag;
614
615
616 -- Add post grant condition
617 IF l_obj_select IS NULL THEN
618 --code added my mmkumar
619 IF L_NULL_FLAG = 'Z' THEN
620 --IF isSingleGrantCond(p_grants_rec.grant_text) THEN
621 IF onlyZTypeAttributes THEN
622 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 ';
623 ELSE
624 NULL;
625 END IF;
626 ELSE
627
628 --** Statement level logging.
629 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
630 l_label := 'igs.plsql.igs_sc_gen_001.check_ins_security';
631 l_debug_str := 'l_obj_select is not null : '|| l_obj_select;
632 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
633 END IF;
634 --**
635
636 --code added by mmkumar ends
637 IF l_obj_const IS NOT NULL AND l_usr_select IS NOT NULL THEN
638 -- User attribute is select of any kind and object attribute is not select
639 -- User Select = Obj CONST
640 l_found_pos := INSTR(UPPER(ltrim(l_usr_select)),'FROM',1,1);
641 l_column_name := substr(ltrim(l_usr_select),8,l_found_pos-9); -- 8 position 'select ' found -9 ' FROM'
642 --grant text ' EXISTS (object_select AND Column Condition ( user attrr select))
643 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text
644 ||' EXISTS ('||l_usr_select||' AND '||l_column_name||' '||check_operation(l_grant_cond(c_grant_cond_rec.grant_cond_num).condition)||' '; -- ||l_obj_const||' ))';
645 l_grant_cond(c_grant_cond_rec.grant_cond_num).obj_const := l_obj_const;
646 l_grant_cond(c_grant_cond_rec.grant_cond_num).close_part := ' )'; --mmkumar, removed extra bracket
647 ELSIF l_obj_const IS NULL AND l_usr_select IS NOT NULL THEN
648 IF L_NULL_FLAG = 'Y' THEN
649 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 '; --mmmkumar, replaced return true
650 END IF;
651 -- Colunmn name = User Select
652 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' '||
653 l_grant_cond(c_grant_cond_rec.grant_cond_num).condition ||' (' ||l_usr_select||') '||l_post_grant;
654 ELSE
655 -- Column name or Object Const = User CONST
656 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' '||
657 l_obj_const||' '||l_grant_cond(c_grant_cond_rec.grant_cond_num).condition||' '||l_usr_const||l_post_grant;
658 END IF;
659 END IF; -- added by mmkumar
660 ELSE --Object select is not null
661 --find the name of the select coulmn for attribute
662 l_found_pos := INSTR(UPPER(ltrim(l_obj_select)),'FROM',1,1);
663 l_column_name := substr(ltrim(l_obj_select),8,l_found_pos-9); -- 8 position 'select ' found -9 ' FROM'
664 --grant text ' EXISTS (object_select AND Column Condition ( user attrr select))
665
666
667 --code added my mmkumar
668 IF L_NULL_FLAG = 'Z' THEN
669 IF onlyZTypeAttributes THEN
670 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 ';
671 ELSE
672 NULL;
673 END IF;
674 ELSE
675 -- its not Z
676 IF l_usr_select IS NOT NULL THEN
677 --Add user select
678 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||' '||
679 l_grant_cond(c_grant_cond_rec.grant_cond_num).condition||' ('||l_usr_select||' )';
680 IF L_NULL_FLAG <> 'Y' THEN
681 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' ) ';
682 END IF;
683 ELSE --Add user constant
684 IF l_grant_cond(c_grant_cond_rec.grant_cond_num).condition IS NULL THEN
685 --operator in the grant text - don't add anything but Object select
686 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_obj_select;
687 ELSE
688 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||' '||
689 l_grant_cond(c_grant_cond_rec.grant_cond_num).condition||' '||l_usr_const;
690 IF L_NULL_FLAG <> 'Y' THEN
691 l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' ) ';
692 END IF;
693 END IF;
694 END IF;
695
696 IF L_NULL_FLAG = 'Y' THEN
697 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||'))';
698 END IF;
699 END IF; --mmkumar
700 END IF;
701 END LOOP;
702
703 --
704 --
705 --code added by mmkumar
706 l_cur_pos :=1;
707 l_found_pos := 0;
708
709 l_grant_text := grants_rec.grant_text;
710 LOOP
711 l_found_pos := INSTR(l_grant_text,':',l_cur_pos,1);
712
713 IF l_found_pos =0 THEN -- End loop no occurences found anymore
714 EXIT;
715 END IF;
716
717 -- Find number of predicate - total numbers is limited to 9 so far.
718
719 l_cur_num := SUBSTR(l_grant_text,l_found_pos+1,1); --Just one character
720 IF l_grant_cond(l_cur_num).z_typ_flag = 'Z' AND NOT onlyZTypeAttributes THEN
721 l_grant_text := REPLACE(l_grant_text, ':' || l_cur_num,'');
722 END IF;
723 l_cur_pos := l_found_pos + 2;
724 END LOOP;
725
726 l_bodmas_grant_text := IGS_SC_GRANTS_PVT.getBodmasCondition(l_grant_text);
727
728 --** Statement level logging.
729 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
730 l_label := 'igs.plsql.igs_sc_gen_001.check_ins_security';
731 l_debug_str := 'Got string from bodmas : '|| l_bodmas_grant_text; --l_statment;
732 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
733 END IF;
734 --**
735
736
737 --code added by mmkumar
738 --
739 --
740
741
742
743
744 -- Analize grant structure and construct grant, example "(:1 AND (:2 OR :3))"
745 l_cur_pos :=1;
746 l_found_pos := 0;
747 -- First pass - check if there is anything before the first grant condition
748 l_found_pos := INSTR(l_bodmas_grant_text,':',l_cur_pos,1);
749
750 IF l_found_pos >1 THEN
751 --IF l_statment IS NOT NULL THEN -- not first grant, we need to add OR
752 --l_statment := l_statment||' OR ';
753 fnd_dsql.add_text(' OR ');
754 --END IF;
755 END IF;
756
757 LOOP
758
759 -- Find next occurence of :
760
761 l_found_pos := INSTR(l_bodmas_grant_text,':',l_cur_pos,1);
762
763 IF l_found_pos =0 THEN -- End loop no occurences found anymore
764 EXIT;
765 END IF;
766
767 --added by mmkumar
768 IF l_grant_cond(l_cur_num).z_typ_flag = 'Z' AND onlyZTypeAttributes THEN
769 fnd_dsql.add_text('1 = 1');
770 --l_statment := l_statment || '1 = 1';
771 EXIT;
772 END IF;
773 --added by mmkumar
774
775 -- Find number of predicate - total numbers is limited to 9 so far.
776
777 l_cur_num := SUBSTR(l_bodmas_grant_text,l_found_pos+1,1); --Just one character
778 --l_statment := l_statment||SUBSTR(grants_rec.grant_text,l_cur_pos, (l_found_pos - l_cur_pos));
779 fnd_dsql.add_text(SUBSTR(l_bodmas_grant_text,l_cur_pos, (l_found_pos - l_cur_pos)));
780 -- Add condition from found grant number to statement
781 --l_statment := l_statment || l_grant_cond(l_cur_num).cond_text;
782 fnd_dsql.add_text(l_grant_cond(l_cur_num).cond_text);
783
784 if l_grant_cond(l_cur_num).obj_const is not null then
785
786 lv_obj_constant := l_grant_cond(l_cur_num).obj_const;
787 l_grant_cond(l_cur_num).obj_const := replace(l_grant_cond(l_cur_num).obj_const,'''','');
788
789
790 fnd_dsql.add_bind(l_grant_cond(l_cur_num).obj_const);
791 fnd_dsql.add_text(l_grant_cond(l_cur_num).close_part);
792 end if;
793
794 l_cur_pos := l_found_pos + 2;
795
796 END LOOP;
797
798
799
800
801
802
803 -- Add last part of condition
804 IF NOT (l_grant_cond(l_cur_num).z_typ_flag = 'Z' AND onlyZTypeAttributes) THEN
805 --l_statment := l_statment||substr(grants_rec.grant_text,l_cur_pos);
806 fnd_dsql.add_text(substr(l_bodmas_grant_text,l_cur_pos));
807 END IF;
808 --** Statement level logging.
809 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
810 l_label := 'igs.plsql.igs_sc_gen_001.check_ins_security';
811 l_debug_str := 'Final Select: '|| fnd_dsql.get_text(); --l_statment;
812 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
813 END IF;
814 --**
815 ---now evaluate the grant, as this is pre-evaulation
816 BEGIN
817 l_val :=0;
818 l_SelectStatement := fnd_dsql.get_text(FALSE);
819
820 --** Statement level logging.
821 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
822 l_label := 'igs.plsql.igs_sc_gen_001.check_ins_security';
823 l_debug_str := 'statement to be executed : '|| l_SelectStatement;
824 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
825 END IF;
826 --**
827
828 l_ext_cursor := DBMS_SQL.OPEN_CURSOR;
829 fnd_dsql.set_cursor(l_ext_cursor);
830 DBMS_SQL.PARSE (l_ext_cursor, l_SelectStatement, DBMS_SQL.V7);
831 fnd_dsql.do_binds;
832 dbms_sql.define_column(l_ext_cursor, 1, l_output);
833 lnRows := DBMS_SQL.EXECUTE (l_ext_cursor);
834 IF dbms_sql.fetch_rows(l_ext_cursor) > 0 THEN
835 dbms_sql.column_value(l_ext_cursor, 1, l_output);
836 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
837 l_label := 'igs.plsql.igs_sc_gen_001.check_ins_security.finalreturn';
838 l_debug_str := 'Final Select: TRUE ';
839 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
840 END IF;
841 RETURN TRUE;
842 END IF;
843
844 /*
845 EXECUTE IMMEDIATE l_statment INTO l_val;
846
847 IF (l_val = 1) THEN
848 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
849 l_label := 'igs.plsql.igs_sc_gen_001.check_ins_security.finalreturn';
850 l_debug_str := 'Final Select: TRUE ';
851 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
852 END IF;
853
854 RETURN TRUE;
855 END IF;
856 */
857 EXCEPTION
858 WHEN NO_DATA_FOUND THEN
859 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
860 l_label := 'igs.plsql.igs_sc_gen_001.check_ins_security.finalreturn';
861 l_debug_str := 'Final Select: FALSE ';
862 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
863 END IF;
864
865 RETURN FALSE;
866 WHEN OTHERS THEN
867 --** Statement level logging.
868 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
869 l_label := 'igs.plsql.igs_sc_gen_001.check_ins_security.Exception';
870 l_debug_str := 'Exception: '||SQLERRM;
871 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
872 END IF;
873 --**
874 p_msg_data := SQLERRM;
875 RETURN FALSE;
876 END;
877 END LOOP;
878
879 -- if no grant statement exists, evaluate the default policy..if grant then return TRUE
880 IF NOT L_GRANTS_EXIST THEN
881 OPEN DEF_GRNT(P_OBJECT_NAME);
882 FETCH DEF_GRNT INTO l_def_gr;
883 IF l_def_gr = 'G' THEN
884 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
885 l_label := 'igs.plsql.igs_sc_gen_001.check_ins_security.DefaultPolicy';
886 l_debug_str := 'DefaultPolicy: TRUE ';
887 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
888 END IF;
889 RETURN TRUE;
890 ELSE
891 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
892 l_label := 'igs.plsql.igs_sc_gen_001.check_ins_security.DefaultPolicy';
893 l_debug_str := 'DefaultPolicy: FALSE ';
894 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
895 END IF;
896 RETURN FALSE;
897 END IF;
898 END IF;
899
900 --** Statement level logging.
901 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
902 l_label := 'igs.plsql.igs_sc_gen_001.check_ins_security';
903 l_debug_str := 'Returing False after setting it null : ';
904 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
905 END IF;
906 --**
907
908 p_msg_data := NULL;
909 RETURN FALSE;
910 EXCEPTION
911 WHEN OTHERS THEN
912 --** Statement level logging.
913 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
914 l_label := 'igs.plsql.igs_sc_gen_001.check_ins_security';
915 l_debug_str := 'inside exception section : ' || SQLERRM;
916 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
917 END IF;
918 --**
919
920 p_msg_data := SQLERRM;
921 RETURN FALSE;
922 END check_ins_security;
923
924 PROCEDURE set_ctx(
925 p_name VARCHAR2
926 ) IS
927 ------------------------------------------------------------------
928 --Updated by : gmaheswa, Oracle India
929 --Date created: 27-MAY-2001
930 --
931 --Purpose: FGAC-- set context --this makes everything securre---so dba policy predicate would
932 -- get appened to all sqls.
933 --
934 --Change History:
935 ------------------------------------------------------------------
936 BEGIN
937
938 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
939 l_label := 'igs.plsql.igs_sc_gen_001.set_ctx';
940 l_debug_str := 'Context set User ID: '||FND_GLOBAL.USER_ID||','||'Responsibility ID'||FND_GLOBAL.RESP_ID;
941 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
942 END IF;
943
944 IF(SYS_CONTEXT('OSS_APP_CTX','SECURITY') IS NULL) THEN
945 dbms_session.set_context( 'OSS_APP_CTX', 'SECURITY', UPPER(P_NAME));
946 END IF;
947 END set_ctx;
948
949 PROCEDURE unset_ctx(
950 p_name VARCHAR2
951 ) IS
952 ------------------------------------------------------------------
953 --Updated by : gmaheswa, Oracle India
954 --Date created: 27-MAY-2001
955 --
956 --Purpose: FGAC-- unset context --this makes it unrestricted...dba_policy predicate would not get appeneded.
957 --
958 --Change History:
959 ------------------------------------------------------------------
960 BEGIN
961 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
962 l_label := 'igs.plsql.igs_sc_gen_001.unset_ctx';
963 l_debug_str := 'Context UNset User ID: '||FND_GLOBAL.USER_ID||','||'Responsibility ID'||FND_GLOBAL.RESP_ID;
964 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
965 END IF;
966
967 IF(SYS_CONTEXT('OSS_APP_CTX','SECURITY') IS NOT NULL) THEN
968 dbms_session.set_context( 'OSS_APP_CTX', 'SECURITY', '' );
969 END IF;
970 END unset_ctx;
971
972 FUNCTION CHECK_SEL_UPD_DEL_SECURITY (
973 P_Tab_Name IN VARCHAR2,
974 P_Rowid IN ROWID,
975 P_Action IN VARCHAR2, --(U/D - Update/Delete)
976 P_Msg_data OUT NOCOPY VARCHAR2) -- return the error message in case of any exceptions.
977 RETURN BOOLEAN IS -- TRUE if update/delete privileges are there else return FALSE
978 ------------------------------------------------------------------
979 --Updated by : gmaheswa, Oracle India
980 --Date created: 27-MAY-2001
981 --
982 --Purpose:main wrapper for pre-check security, (only for select,upd, del)
983 -- different for insert, as evaluation for insert is different.
984 --
985 --Change History:
986 ------------------------------------------------------------------
987
988 l_obj_exists_cur VARCHAR2(4000);
989 L_WHERE_CLAUSE VARCHAR2(32000);
990 L_SELECT_STMT VARCHAR2(32000);
991 l_obj_found VARCHAR2(1);
992 l_grant VARCHAR2(1);
993 l_operation VARCHAR2(10);
994
995 TYPE GetGrantCurTyp IS REF CURSOR;
996 Grant_cv GetGrantCurTyp;
997
998 BEGIN
999 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1000 l_label := 'igs.plsql.igs_sc_gen_001.check_sel_upd_del_security';
1001 l_debug_str := 'Table Name: '||P_Tab_Name||','||'RowID: '||P_Rowid||','||'Action: '||P_Action;
1002 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1003 END IF;
1004
1005 IF P_Tab_Name <> 'IGS_PS_UNIT_OFR_OPT_SV' THEN
1006 --validate whether the table is registered for the particular operation or not.
1007 l_obj_exists_cur := 'SELECT 1 FROM IGS_SC_OBJECTS SC, FND_OBJECTS FND WHERE SC.OBJECT_ID = FND.OBJECT_ID AND FND.OBJ_NAME = UPPER(:P_Tab_Name) AND ';
1008 IF P_ACTION = 'S' THEN
1009 L_OBJ_EXISTS_CUR := L_OBJ_EXISTS_CUR||' SC.SELECT_FLAG = ''Y''';
1010 l_operation := 'SELECT';
1011 ELSIF P_ACTION = 'U' THEN
1012 L_OBJ_EXISTS_CUR := L_OBJ_EXISTS_CUR||' SC.UPDATE_FLAG = ''Y''';
1013 l_operation := 'UPDATE';
1014 ELSIF P_ACTION = 'D' THEN
1015 L_OBJ_EXISTS_CUR := L_OBJ_EXISTS_CUR||' SC.DELETE_FLAG = ''Y''';
1016 l_operation := 'DELETE';
1017 END IF;
1018
1019 --** Statement level logging.
1020 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1021 l_label := 'igs.plsql.igs_sc_gen_001.check_sel_upd_del_security';
1022 l_debug_str := 'executing statement : '|| l_obj_exists_cur;
1023 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1024 END IF;
1025 --**
1026
1027 EXECUTE IMMEDIATE l_obj_exists_cur INTO l_obj_found USING P_Tab_Name;
1028 IF l_obj_found IS NOT NULL THEN
1029 --get the main security predicate clause
1030 L_WHERE_CLAUSE := IGS_SC_GRANTS_PVT.GENERATE_GRANT(P_Tab_name, P_Action );
1031 ELSE
1032 FND_MESSAGE.SET_NAME('IGS','IGS_SC_TAB_NOT_REG');
1033 FND_MESSAGE.SET_TOKEN('OPERATION',l_operation);
1034 p_msg_data := fnd_message.get;
1035 RETURN FALSE;
1036 END IF;
1037 ELSE
1038 L_WHERE_CLAUSE := IGS_SC_GRANTS_PVT.GENERATE_GRANT(P_Tab_name, P_Action );
1039 END IF;
1040
1041 --** Statement level logging.
1042 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1043 l_label := 'igs.plsql.igs_sc_gen_001.check_sel_upd_del_security';
1044 l_debug_str := 'L_WHERE_CLAUSE is : '|| L_WHERE_CLAUSE;
1045 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1046 END IF;
1047 --**
1048
1049 --evaluate the predicated against the passed table.
1050 IF L_WHERE_CLAUSE IS NOT NULL THEN
1051 L_SELECT_STMT := 'SELECT 1 FROM '||P_Tab_Name||' WHERE ROWID = :1 AND ('||L_WHERE_CLAUSE||')';
1052
1053 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1054 l_label := 'igs.plsql.igs_sc_gen_001.check_sel_upd_del_security.finalselect';
1055 l_debug_str := 'Final Select: '||L_SELECT_STMT;
1056 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1057 END IF;
1058
1059 OPEN Grant_cv FOR L_SELECT_STMT USING P_ROWID;
1060 FETCH Grant_cv INTO l_grant;
1061 IF Grant_cv%FOUND THEN
1062 close grant_cv;
1063 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1064 l_label := 'igs.plsql.igs_sc_gen_001.check_sel_upd_del_security.finalreturn';
1065 l_debug_str := 'Final Select: TRUE ';
1066 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1067 END IF;
1068 RETURN TRUE;
1069 ELSE
1070 close grant_cv;
1071 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1072 l_label := 'igs.plsql.igs_sc_gen_001.check_sel_upd_del_security.finalreturn';
1073 l_debug_str := 'Final Select: FALSE ';
1074 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1075 END IF;
1076 RETURN FALSE;
1077 END IF;
1078 ELSE
1079 RETURN TRUE;
1080 END IF;
1081 EXCEPTION
1082 WHEN OTHERS THEN
1083 IF grant_cv%ISOPEN THEN
1084 close grant_cv ;
1085 END IF;
1086
1087 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1088 l_label := 'igs.plsql.igs_sc_gen_001.check_sel_upd_del_security.Exception';
1089 l_debug_str := 'Exception: '||SQLERRM;
1090 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1091 END IF;
1092 P_MSG_DATA := SQLERRM;
1093 RETURN FALSE;
1094 END CHECK_SEL_UPD_DEL_SECURITY;
1095
1096 FUNCTION CHECK_PERSON_SECURITY (
1097 P_Table_Name IN VARCHAR2,
1098 P_Person_id IN NUMBER,
1099 P_Action IN VARCHAR2, --(S/U - Select/Update)
1100 P_Msg_data OUT NOCOPY VARCHAR2) -- return the error message in case of any exceptions.
1101 ------------------------------------------------------------------
1102 --Updated by : gmaheswa, Oracle India
1103 --Date created: 27-MAY-2001
1104 --
1105 --Purpose:main wrapper for person security used by UIs
1106 --
1107 --Change History:
1108 ------------------------------------------------------------------
1109 RETURN BOOLEAN IS
1110
1111 L_ROWID ROWID;
1112 GET_ROW_ID_CUR varchar2(4000);
1113 TYPE GetRowidCurTyp IS REF CURSOR;
1114 rowid_cv GetRowidCurTyp;
1115 l_msg_data varchar2(4000);
1116 L_WHERE_CLAUSE VARCHAR2(32000);
1117 L_SELECT_STMT VARCHAR2(32000);
1118
1119 TYPE GetGrantCurTyp IS REF CURSOR;
1120 Grant_cv GetGrantCurTyp;
1121 l_grant VARCHAR2(1);
1122
1123 BEGIN
1124 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1125 l_label := 'igs.plsql.igs_sc_gen_001.check_person_security';
1126 l_debug_str := 'Table Name: '||P_Table_Name||','||'Person ID: '||P_Person_id||','||'Action: '||P_Action;
1127 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1128 END IF;
1129
1130 get_row_id_cur := 'SELECT ROWID FROM '||P_Table_Name||' WHERE PARTY_ID = :P_Person_id';
1131
1132 OPEN rowid_cv FOR get_row_id_cur using P_Person_id;
1133 FETCH rowid_cv INTO L_ROWID;
1134 IF rowid_cv%NOTFOUND THEN
1135 --** Statement level logging.
1136 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1137 l_label := 'igs.plsql.igs_sc_gen_001.check_person_security';
1138 l_debug_str := 'NON-OSS Person';
1139 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1140 END IF;
1141 --**
1142 CLOSE rowid_cv;
1143 L_WHERE_CLAUSE := IGS_SC_GRANTS_PVT.GENERATE_GRANT(P_Table_Name, P_Action );
1144
1145 --** Statement level logging.
1146 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1147 l_label := 'igs.plsql.igs_sc_gen_001.check_person_security';
1148 l_debug_str := 'L_WHERE_CLAUSE is : '|| L_WHERE_CLAUSE;
1149 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1150 END IF;
1151 --**
1152 IF L_WHERE_CLAUSE IS NOT NULL THEN
1153 -- As person Security is always evaluated on isg_pe_hz_parties and igs_pe_hz_parties_sv, replace them with igs_pe_person_base_v
1154 L_WHERE_CLAUSE := replace_string(L_WHERE_CLAUSE,'IGS_PE_HZ_PARTIES.PARTY_ID','IGS_PE_PERSON_BASE_V.PERSON_ID');
1155 L_WHERE_CLAUSE := replace_string(L_WHERE_CLAUSE,'IGS_PE_HZ_PARTIES_SV.PARTY_ID','IGS_PE_PERSON_BASE_V.PERSON_ID');
1156 L_WHERE_CLAUSE := replace_string(L_WHERE_CLAUSE,' PARTY_ID',' PERSON_ID');
1157
1158 IF L_WHERE_CLAUSE IS NOT NULL THEN
1159 L_SELECT_STMT := 'SELECT 1 FROM IGS_PE_PERSON_BASE_V WHERE PERSON_ID = :1 AND ('||L_WHERE_CLAUSE||')';
1160
1161 --** Statement level logging.
1162 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1163 l_label := 'igs.plsql.igs_sc_gen_001.check_person_security';
1164 l_debug_str := 'L_SELECT_STMT is : '|| L_SELECT_STMT;
1165 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1166 END IF;
1167 --**
1168
1169 OPEN Grant_cv FOR L_SELECT_STMT USING P_Person_id;
1170 FETCH Grant_cv INTO l_grant;
1171 IF Grant_cv%FOUND THEN
1172 CLOSE grant_cv;
1173 --** Statement level logging.
1174 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1175 l_label := 'igs.plsql.igs_sc_gen_001.check_person_security';
1176 l_debug_str := 'Privilege Exists: Return True';
1177 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1178 END IF;
1179 --**
1180 RETURN TRUE;
1181 ELSE
1182 CLOSE grant_cv;
1183 --** Statement level logging.
1184 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1185 l_label := 'igs.plsql.igs_sc_gen_001.check_person_security';
1186 l_debug_str := 'Privilege Does not Exists: Return False';
1187 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1188 END IF;
1189 --**
1190 RETURN FALSE;
1191 END IF;
1192 ELSE
1193 --** Statement level logging.
1194 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1195 l_label := 'igs.plsql.igs_sc_gen_001.check_person_security';
1196 l_debug_str := 'Privilege Exists: Default Policy: Return True';
1197 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1198 END IF;
1199 --**
1200 RETURN TRUE;
1201 END IF;
1202 ELSE
1203 RETURN TRUE;
1204 END IF;
1205 ELSE
1206 CLOSE rowid_cv;
1207 RETURN(CHECK_SEL_UPD_DEL_SECURITY(
1208 P_Tab_Name => P_Table_Name,
1209 P_Rowid => L_ROWID,
1210 P_Action => P_Action,
1211 P_Msg_data => p_Msg_data));
1212 END IF;
1213 EXCEPTION
1214 WHEN OTHERS THEN
1215 IF rowid_cv%ISOPEN THEN
1216 CLOSE rowid_cv ;
1217 END IF;
1218 IF l_msg_data IS NOT NULL THEN --exception came from check_sel call
1219 P_MSG_DATA := l_Msg_data;
1220 ELSE
1221 P_MSG_DATA := sqlerrm; -- couldnt handle the exception.
1222 END IF;
1223 RETURN FALSE;
1224
1225 END CHECK_PERSON_SECURITY;
1226
1227 FUNCTION check_user_policy
1228 ( P_BUSINESS_OBJECT IN varchar2, -- BO name
1229 P_ACTION IN varchar2 , -- S,I,D,U
1230 P_USER_ID IN number DEFAULT NULL) -- fnd user id)
1231 RETURN VARCHAR2 IS
1232 ------------------------------------------------------------------
1233 --Updated by : ssawhney, Oracle India
1234 --Date created: 27-MAY-2001
1235 --
1236 --Purpose:required by EN..check whether loged in user has any access on the BO-table
1237 --
1238 --Change History:
1239 --WHO WHEN WHAT
1240 --skpandey 10-JAN-2006 Bug#4937960
1241 -- Changed c_user cursor definition to optimize query
1242 ------------------------------------------------------------------
1243
1244 l_user_id fnd_user.user_id%type;
1245 l_return varchar2(4000); --incase sql errmr then 4000
1246 l_default IGS_SC_OBJ_GROUPS.default_policy_type%type ;
1247 l_obj_grp_id IGS_SC_OBJ_GROUPS.obj_group_id%type;
1248 l_role_id NUMBER;
1249
1250 CURSOR c_user (cp_igs varchar, cp_fnd varchar, cp_fnd_user fnd_user.user_id%type) IS
1251 SELECT role_orig_system_id
1252 FROM wf_local_user_roles role, fnd_user use
1253 WHERE role_orig_system = cp_igs
1254 AND user_orig_system = cp_fnd
1255 AND user_orig_system_id = cp_fnd_user
1256 AND role.user_name = use.user_name
1257 AND use.user_id = cp_fnd_user;
1258
1259
1260 CURSOR c_bo (cp_bo_name varchar) IS
1261 SELECT default_policy_type, obj_group_id
1262 FROM IGS_SC_OBJ_GROUPS
1263 WHERE obj_group_name = cp_bo_name;
1264
1265 CURSOR c_policy (cp_role_id NUMBER, cp_bo_id NUMBER) IS
1266 SELECT grant_id,locked_flag ,grant_select_flag,grant_update_flag,grant_insert_flag,grant_delete_flag
1267 FROM IGS_SC_GRANTS
1268 WHERE user_group_id = cp_role_id
1269 AND obj_group_id =cp_bo_id
1270 AND locked_flag= 'Y';
1271
1272 policy_rec c_policy%rowtype;
1273
1274 BEGIN
1275
1276 -- get logged in USER
1277 IF p_user_id IS NULL THEN
1278 l_user_id := FND_GLOBAL.USER_ID;
1279 ELSE
1280 l_user_id := p_user_id;
1281 END IF;
1282
1283 OPEN c_bo(p_business_object);
1284 FETCH c_bo INTO l_default, l_obj_grp_id;
1285 CLOSE c_bo;
1286
1287 --get the default policies.
1288 IF l_default ='R' THEN
1289 l_return := 'DEFAULT_RESTRICT';
1290 ELSE
1291 l_return := 'DEFAULT_GRANT';
1292 END IF;
1293
1294 --get the policy for the user-role combination
1295 OPEN c_user ('IGS','FND_USR', l_user_id);
1296 FETCH c_user INTO l_role_id;
1297 CLOSE c_user;
1298
1299
1300 OPEN c_policy(l_role_id, l_obj_grp_id);
1301 FETCH c_policy INTO policy_rec;
1302 IF c_policy%NOTFOUND THEN
1303 CLOSE c_policy;
1304 RETURN l_return; --retrun from here..no need to go further this will return default BO.
1305 ELSE
1306 CLOSE c_policy;
1307 END IF;
1308
1309 IF p_action='S' AND ( policy_rec.grant_select_flag='Y') THEN
1310 l_return:= 'POLICY_EXIST';
1311 ELSIF p_action='I' AND policy_rec.grant_insert_flag='Y' THEN
1312 l_return:= 'POLICY_EXIST';
1313 ELSIF p_action='U' AND policy_rec.grant_update_flag='Y' THEN
1314 l_return:= 'POLICY_EXIST';
1315 ELSIF p_action='D' AND policy_rec.grant_delete_flag='Y' THEN
1316 l_return:= 'POLICY_EXIST';
1317 END IF;
1318
1319 RETURN l_return;
1320
1321 EXCEPTION
1322 WHEN OTHERS THEN
1323 l_return := SQLERRM;
1324 RETURN l_return;
1325
1326 END check_user_policy;
1327
1328 END IGS_SC_GEN_001;