[Home] [Help]
PACKAGE BODY: APPS.AMW_VALID_INSTANCE_SET_GRANTS
Source
1 PACKAGE BODY AMW_VALID_INSTANCE_SET_GRANTS as
2 /*$Header: amwisgrb.pls 120.3 2006/05/30 12:35:30 dpatel noship $*/
3
4 PROCEDURE GET_VALID_INSTANCE_SETS(p_obj_name IN VARCHAR2,
5 p_grantee_type IN VARCHAR2,
6 p_parent_obj_sql IN VARCHAR2,
7 p_bind1 IN VARCHAR2,
8 p_bind2 IN VARCHAR2,
9 p_bind3 IN VARCHAR2,
10 p_bind4 IN VARCHAR2,
11 p_bind5 IN VARCHAR2,
12 p_obj_ids IN VARCHAR2,
13 x_guids OUT NOCOPY varchar2) IS
14 CURSOR inst_set_preds IS
15 select grants.GRANT_GUID grant_guid,
16 obj.database_object_name database_object_name,
17 grants.parameter1 parameter1,
18 grants.parameter2 parameter2,
19 grants.parameter3 parameter3,
20 grants.parameter4 parameter4,
21 grants.parameter5 parameter5,
22 grants.parameter6 parameter6,
23 grants.parameter7 parameter7,
24 grants.parameter8 parameter8,
25 grants.parameter9 parameter9,
26 grants.parameter10 parameter10,
27 sets.instance_set_id instance_set_id,
28 sets.predicate predicate
29 from
30 fnd_grants grants,
31 fnd_object_instance_sets sets,
32 fnd_objects obj
33 where obj.obj_name = p_obj_name
34 AND grants.object_id = obj.object_id
35 AND grants.instance_type='SET'
36 AND nvl(grants.end_date, sysdate+1) >= trunc(sysdate)
37 AND grants.grantee_type = p_grantee_type
38 AND sets.instance_set_id = grants.instance_set_id;
39
40 CURSOR obj_meta_data IS
41 select DATABASE_OBJECT_NAME,
42 PK1_COLUMN_NAME,PK2_COLUMN_NAME,
43 PK3_COLUMN_NAME,PK4_COLUMN_NAME,
44 PK5_COLUMN_NAME from fnd_objects where OBJ_NAME = p_obj_name;
45 obj_meta_data_rec obj_meta_data%ROWTYPE;
46 i NUMBER := 1;
47 -- bug 3748547 setting varchar2 fields to maximum size
48 query_to_exec VARCHAR2(32767);
49 obj_std_pkq VARCHAR2(32767);
50 prim_key_str VARCHAR2(32767);
51 guids VARCHAR2(32767);
52 mod_pred VARCHAR2(32767);
53 cursor_select INTEGER;
54 cursor_execute INTEGER;
55 BEGIN
56 OPEN obj_meta_data;
57 FETCH obj_meta_data INTO obj_meta_data_rec;
58 obj_std_pkq := '(SELECT ' || obj_meta_data_rec.PK1_COLUMN_NAME;
59 prim_key_str := obj_meta_data_rec.PK1_COLUMN_NAME;
60 IF obj_meta_data_rec.PK2_COLUMN_NAME IS NOT NULL THEN
61 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK2_COLUMN_NAME;
62 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK2_COLUMN_NAME;
63 END IF;
64 IF obj_meta_data_rec.PK3_COLUMN_NAME IS NOT NULL THEN
65 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK3_COLUMN_NAME;
66 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK3_COLUMN_NAME;
67 END IF;
68 IF obj_meta_data_rec.PK4_COLUMN_NAME IS NOT NULL THEN
69 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK4_COLUMN_NAME;
70 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK4_COLUMN_NAME;
71 END IF;
72 IF obj_meta_data_rec.PK5_COLUMN_NAME IS NOT NULL THEN
73 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK5_COLUMN_NAME;
74 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK5_COLUMN_NAME;
75 END IF;
76 obj_std_pkq := obj_std_pkq || ' FROM ' || obj_meta_data_rec.DATABASE_OBJECT_NAME;
77 CLOSE obj_meta_data;
78
79 FOR inst_set_preds_rec IN inst_set_preds
80 LOOP
81 mod_pred := pred_aft_token_subst(inst_set_preds_rec.predicate,
82 inst_set_preds_rec.database_object_name,
83 inst_set_preds_rec.parameter1,
84 inst_set_preds_rec.parameter2,
85 inst_set_preds_rec.parameter3,
86 inst_set_preds_rec.parameter4,
87 inst_set_preds_rec.parameter5,
88 inst_set_preds_rec.parameter6,
89 inst_set_preds_rec.parameter7,
90 inst_set_preds_rec.parameter8,
91 inst_set_preds_rec.parameter9,
92 inst_set_preds_rec.parameter10);
93
94 IF p_obj_ids IS NOT NULL THEN
95 query_to_exec := 'SELECT 1 from dual WHERE (' || p_obj_ids || ') IN (' || obj_std_pkq;
96 query_to_exec := query_to_exec || ' WHERE ' || mod_pred || ' ))';
97 ELSIF p_parent_obj_sql IS NOT NULL THEN
98 query_to_exec := 'SELECT 1 from dual WHERE EXISTS( ' || obj_std_pkq || ' WHERE ';
99 query_to_exec := query_to_exec || inst_set_preds_rec.predicate || ' AND (';
100 query_to_exec := query_to_exec || prim_key_str || ') IN (' || p_parent_obj_sql || '))';
101 END IF;
102
103 cursor_select := DBMS_SQL.OPEN_CURSOR;
104 DBMS_SQL.PARSE(cursor_select, query_to_exec, DBMS_SQL.NATIVE);
105 IF INSTR(mod_pred, ':parameter1') <> 0 THEN
106 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameter1', inst_set_preds_rec.parameter1);
107 END IF;
108 IF INSTR(mod_pred, ':parameter2') <> 0 THEN
109 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameter2', inst_set_preds_rec.parameter2);
110 END IF;
111 IF INSTR(mod_pred, ':parameter3') <> 0 THEN
112 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameter3', inst_set_preds_rec.parameter3);
113 END IF;
114 IF INSTR(mod_pred, ':parameter4') <> 0 THEN
115 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameter4', inst_set_preds_rec.parameter4);
116 END IF;
117 IF INSTR(mod_pred, ':parameter5') <> 0 THEN
118 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameter5', inst_set_preds_rec.parameter5);
119 END IF;
120 IF INSTR(mod_pred, ':parameter6') <> 0 THEN
121 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameter6', inst_set_preds_rec.parameter6);
122 END IF;
123 IF INSTR(mod_pred, ':parameter7') <> 0 THEN
124 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameter7', inst_set_preds_rec.parameter7);
125 END IF;
126 IF INSTR(mod_pred, ':parameter8') <> 0 THEN
127 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameter8', inst_set_preds_rec.parameter8);
128 END IF;
129 IF INSTR(mod_pred, ':parameter9') <> 0 THEN
130 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameter9', inst_set_preds_rec.parameter9);
131 END IF;
132 IF INSTR(mod_pred, ':parameterX') <> 0 THEN
133 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameterX', inst_set_preds_rec.parameter10);
134 END IF;
135
136
137 cursor_execute := DBMS_SQL.EXECUTE(cursor_select);
138 IF DBMS_SQL.FETCH_ROWS(cursor_select) > 0 THEN
139 IF i = 1 THEN
140 guids := to_char(inst_set_preds_rec.grant_guid);
141 i := 2;
142 ELSE
143 guids := guids || ',' || inst_set_preds_rec.grant_guid;
144 END IF;
145 END IF;
146 DBMS_SQL.CLOSE_CURSOR(cursor_select);
147 END LOOP;
148 IF guids IS NOT NULL THEN
149 x_guids := guids; /**** list of valid guids ****/
150 ELSE
151 x_guids := '-1';
152 END IF;
153 END;
154
155 /*
156 function pred_aft_token_subst(p_int_set_id in number,
157 p_obj_name IN VARCHAR2,
158 p_grantee_type IN VARCHAR2) return varchar2 is
159 l_chg_pred varchar2(4000);
160 l_pred varchar2(4000);
161 l_datobj_name varchar2(30);
162 l_param1 VARCHAR2(256);
163 l_param2 VARCHAR2(256);
164 l_param3 VARCHAR2(256);
165 l_param4 VARCHAR2(256);
166 l_param5 VARCHAR2(256);
167 l_param6 VARCHAR2(256);
168 l_param7 VARCHAR2(256);
169 l_param8 VARCHAR2(256);
170 l_param9 VARCHAR2(256);
171 l_param10 VARCHAR2(256);
172
173 begin
174 select sets.predicate,
175 obj.database_object_name,
176 grants.parameter1,
177 grants.parameter2,
178 grants.parameter3,
179 grants.parameter4,
180 grants.parameter5,
181 grants.parameter6,
182 grants.parameter7,
183 grants.parameter8,
184 grants.parameter9,
185 grants.parameter10
186 into l_pred,
187 l_datobj_name,
188 l_param1,
189 l_param2,
190 l_param3,
191 l_param4,
192 l_param5,
193 l_param6,
194 l_param7,
195 l_param8,
196 l_param9,
197 l_param10
198 from
199 fnd_grants grants,
200 fnd_object_instance_sets sets,
201 fnd_objects obj
202 where obj.obj_name = p_obj_name
203 AND grants.object_id = obj.object_id
204 AND grants.instance_type='SET'
205 AND nvl(grants.end_date, sysdate+1) >= trunc(sysdate)
206 AND grants.grantee_type = p_grantee_type
207 AND sets.instance_set_id = grants.instance_set_id
208 and sets.instance_set_id = p_int_set_id;
209
210 l_chg_pred := l_pred;
211
212 if INSTR(l_chg_pred, '&TABLE_ALIAS') <> 0 then
213 select replace (l_chg_pred, '&TABLE_ALIAS', l_datobj_name) into l_chg_pred from dual;
214 end if;
215 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER1') <> 0 then
216 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER1', l_param1) into l_chg_pred from dual;
217 end if;
218 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER2') <> 0 then
219 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER2', l_param2) into l_chg_pred from dual;
220 end if;
221 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER3') <> 0 then
222 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER3', l_param3) into l_chg_pred from dual;
223 end if;
224 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER4') <> 0 then
225 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER4', l_param4) into l_chg_pred from dual;
226 end if;
227 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER5') <> 0 then
228 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER5', l_param5) into l_chg_pred from dual;
229 end if;
230 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER6') <> 0 then
231 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER6', l_param6) into l_chg_pred from dual;
232 end if;
233 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER7') <> 0 then
234 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER7', l_param7) into l_chg_pred from dual;
235 end if;
236 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER8') <> 0 then
237 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER8', l_param8) into l_chg_pred from dual;
238 end if;
239 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER9') <> 0 then
240 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER9', l_param9) into l_chg_pred from dual;
241 end if;
242 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER10') <> 0 then
243 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER10', l_param10) into l_chg_pred from dual;
244 end if;
245 return l_chg_pred;
246 end ;
247 */
248
249
250 function pred_aft_token_subst(l_pred in VARCHAR2,
251 l_datobj_name in VARCHAR2,
252 l_param1 in VARCHAR2,
253 l_param2 in VARCHAR2,
254 l_param3 in VARCHAR2,
255 l_param4 in VARCHAR2,
256 l_param5 in VARCHAR2,
257 l_param6 in VARCHAR2,
258 l_param7 in VARCHAR2,
259 l_param8 in VARCHAR2,
260 l_param9 in VARCHAR2,
261 l_param10 in VARCHAR2) return varchar2 is
262 l_chg_pred varchar2(32767);
263 begin
264
265 l_chg_pred := upper(l_pred);
266
267 if INSTR(l_chg_pred, '&TABLE_ALIAS') <> 0 then
268 select replace (l_chg_pred, '&TABLE_ALIAS', l_datobj_name) into l_chg_pred from dual;
269 end if;
270 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER1') <> 0 then
271 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER1', ':parameter1') into l_chg_pred from dual;
272 end if;
273 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER2') <> 0 then
274 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER2', ':parameter2') into l_chg_pred from dual;
275 end if;
276 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER3') <> 0 then
277 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER3', ':parameter3') into l_chg_pred from dual;
278 end if;
279 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER4') <> 0 then
280 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER4', ':parameter4') into l_chg_pred from dual;
281 end if;
282 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER5') <> 0 then
283 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER5', ':parameter5') into l_chg_pred from dual;
284 end if;
285 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER6') <> 0 then
286 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER6', ':parameter6') into l_chg_pred from dual;
287 end if;
288 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER7') <> 0 then
289 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER7', ':parameter7') into l_chg_pred from dual;
290 end if;
291 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER8') <> 0 then
292 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER8', ':parameter8') into l_chg_pred from dual;
293 end if;
294 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER9') <> 0 then
295 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER9', ':parameter9') into l_chg_pred from dual;
296 end if;
297 if INSTR(l_chg_pred, '&GRANT_ALIAS.PARAMETER10') <> 0 then
298 select replace (l_chg_pred, '&GRANT_ALIAS.PARAMETER10', ':parameterX') into l_chg_pred from dual;
299 end if;
300
301 return l_chg_pred;
302
303 end pred_aft_token_subst;
304
305
306 function check_grant_validity (p_guid in varchar2,
307 p_pk1 in varchar2,
308 p_pk2 in varchar2,
309 p_pk3 in varchar2,
310 p_pk4 in varchar2,
311 p_pk5 in varchar2,
312 p_object_name in varchar2
313 ) return number is
314
315 CURSOR obj_meta_data IS
316 select DATABASE_OBJECT_NAME,
317 PK1_COLUMN_NAME,PK2_COLUMN_NAME,
318 PK3_COLUMN_NAME,PK4_COLUMN_NAME,
319 PK5_COLUMN_NAME from fnd_objects where OBJ_NAME = p_object_name;
320
321 obj_meta_data_rec obj_meta_data%ROWTYPE;
322 i NUMBER := 1;
323 j NUMBER := 1;
324 p_obj_ids varchar2(2000);
325 inst_id number;
326 query_to_exec VARCHAR2(32767);
327 obj_std_pkq VARCHAR2(32767);
328 prim_key_str VARCHAR2(32767);
329 guids VARCHAR2(32767);
330 mod_pred VARCHAR2(32767);
331 pred VARCHAR2(32767);
332 db_obj_name varchar2(240);
333 param1 varchar2(256);
334 param2 varchar2(256);
335 param3 varchar2(256);
336 param4 varchar2(256);
337 param5 varchar2(256);
338 param6 varchar2(256);
339 param7 varchar2(256);
340 param8 varchar2(256);
341 param9 varchar2(256);
342 param10 varchar2(256);
343 cursor_select INTEGER;
344 cursor_execute INTEGER;
345 ret_value number := 2;
346 BEGIN
347
348 p_obj_ids := null;
349 if p_pk1 <> '*NULL*' then
350 p_obj_ids := p_pk1;
351 end if;
352 if p_pk2 <> '*NULL*' then
353 p_obj_ids := p_obj_ids || ',' || p_pk2;
354 end if;
355 if p_pk3 <> '*NULL*' then
356 p_obj_ids := p_obj_ids || ',' || p_pk3;
357 end if;
358 if p_pk4 <> '*NULL*' then
359 p_obj_ids := p_obj_ids || ',' || p_pk4;
360 end if;
361 if p_pk5 <> '*NULL*' then
362 p_obj_ids := p_obj_ids || ',' || p_pk5;
363 end if;
364
365 select grants.parameter1,
366 grants.parameter2,
367 grants.parameter3,
368 grants.parameter4,
369 grants.parameter5,
370 grants.parameter6,
371 grants.parameter7,
372 grants.parameter8,
373 grants.parameter9,
374 grants.parameter10,
375 sets.instance_set_id,
376 sets.predicate,
377 obj.database_object_name
378 into
379 param1,
380 param2,
381 param3,
382 param4,
383 param5,
384 param6,
385 param7,
386 param8,
387 param9,
388 param10,
389 inst_id,
390 pred,
391 db_obj_name
392 from
393 fnd_grants grants,
394 fnd_object_instance_sets sets,
395 fnd_objects obj
396 where grants.grant_guid = p_guid
397 AND sets.instance_set_id = grants.instance_set_id
398 and obj.obj_name = p_object_name;
399
400 OPEN obj_meta_data;
401 FETCH obj_meta_data INTO obj_meta_data_rec;
402 obj_std_pkq := '(SELECT ' || obj_meta_data_rec.PK1_COLUMN_NAME;
403 prim_key_str := obj_meta_data_rec.PK1_COLUMN_NAME;
404 IF obj_meta_data_rec.PK2_COLUMN_NAME IS NOT NULL THEN
405 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK2_COLUMN_NAME;
406 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK2_COLUMN_NAME;
407 END IF;
408 IF obj_meta_data_rec.PK3_COLUMN_NAME IS NOT NULL THEN
409 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK3_COLUMN_NAME;
410 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK3_COLUMN_NAME;
411 END IF;
412 IF obj_meta_data_rec.PK4_COLUMN_NAME IS NOT NULL THEN
413 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK4_COLUMN_NAME;
414 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK4_COLUMN_NAME;
415 END IF;
416 IF obj_meta_data_rec.PK5_COLUMN_NAME IS NOT NULL THEN
417 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK5_COLUMN_NAME;
418 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK5_COLUMN_NAME;
419 END IF;
420 obj_std_pkq := obj_std_pkq || ' FROM ' || obj_meta_data_rec.DATABASE_OBJECT_NAME;
421 CLOSE obj_meta_data;
422
423 mod_pred := pred_aft_token_subst(pred,
424 db_obj_name,
425 param1,
426 param2,
427 param3,
428 param4,
429 param5,
430 param6,
431 param7,
432 param8,
433 param9,
434 param10);
435
436 IF p_obj_ids IS NOT NULL THEN
437 query_to_exec := 'SELECT 1 from dual WHERE (' || p_obj_ids || ') IN (' || obj_std_pkq;
441 DBMS_SQL.PARSE(cursor_select, query_to_exec, DBMS_SQL.NATIVE);
438 query_to_exec := query_to_exec || ' WHERE ' || mod_pred || ' ))';
439 END IF;
440 cursor_select := DBMS_SQL.OPEN_CURSOR;
442
443 IF INSTR(mod_pred, ':parameter1') <> 0 THEN
444 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameter1', param1);
445 END IF;
446 IF INSTR(mod_pred, ':parameter2') <> 0 THEN
447 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameter2', param2);
448 END IF;
449 IF INSTR(mod_pred, ':parameter3') <> 0 THEN
450 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameter3', param3);
451 END IF;
452 IF INSTR(mod_pred, ':parameter4') <> 0 THEN
453 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameter4', param4);
454 END IF;
455 IF INSTR(mod_pred, ':parameter5') <> 0 THEN
456 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameter5', param5);
457 END IF;
458 IF INSTR(mod_pred, ':parameter6') <> 0 THEN
459 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameter6', param6);
460 END IF;
461 IF INSTR(mod_pred, ':parameter7') <> 0 THEN
462 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameter7', param7);
463 END IF;
464 IF INSTR(mod_pred, ':parameter8') <> 0 THEN
465 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameter8', param8);
466 END IF;
467 IF INSTR(mod_pred, ':parameter9') <> 0 THEN
468 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameter9', param9);
469 END IF;
470 IF INSTR(mod_pred, ':parameterX') <> 0 THEN
471 DBMS_SQL.BIND_VARIABLE(cursor_select, ':parameterX', param10);
472 END IF;
473
474 cursor_execute := DBMS_SQL.EXECUTE(cursor_select);
475 IF DBMS_SQL.FETCH_ROWS(cursor_select) > 0 THEN
476 ret_value := 1;
477 else
478 ret_value := 2;
479 END IF;
480 DBMS_SQL.CLOSE_CURSOR(cursor_select);
481
482 return ret_value;
483
484 exception
485 when others then
486 return 2;
487
488 END;
489
490 function get_amw_grantees (p_pk1 in varchar2,
491 p_pk2 in varchar2,
492 p_pk3 in varchar2,
493 p_pk4 in varchar2,
494 p_pk5 in varchar2,
495 p_object_name in varchar2
496 ) return varchar2 is
497 sql_string varchar(32767);
498 lp_pk1 varchar2(256);
499 lp_pk2 varchar2(256);
500 lp_pk3 varchar2(256);
501 lp_pk4 varchar2(256);
502 lp_pk5 varchar2(256);
503
504 begin
505
506 if p_pk1 is null then
507 lp_pk1 :='''*NULL*''';
508 else
509 lp_pk1 := p_pk1;
510 end if;
511 if p_pk2 is null then
512 lp_pk2 :='''*NULL*''';
513 else
514 lp_pk2 := p_pk2;
515 end if;
516 if p_pk3 is null then
517 lp_pk3 :='''*NULL*''';
518 else
519 lp_pk3 := p_pk3;
520 end if;
521 if p_pk4 is null then
522 lp_pk4 :='''*NULL*''';
523 else
524 lp_pk4 := p_pk4;
525 end if;
526 if p_pk5 is null then
527 lp_pk5 :='''*NULL*''';
528 else
529 lp_pk5 := p_pk5;
530 end if;
531
532 sql_string :=
533 ' select '||''''''||' PERSON_NAME, to_number(ltrim(gr.grantee_key, '||''''||'HZ_PARTY:'||''''||')) PERSON_ID, '||''''''||' COMPANY_NAME, gr.menu_id ROLE_ID ' ||
534 /**05.03.2006 npanandi: appending below in the SELECT clause for PLM's R12 SQL change**/
535 ',gr.object_id '||
536 /**05.03.2006 npanandi: ends**/
537 ' from fnd_grants gr, fnd_object_instance_sets inst, fnd_objects fo ' ||
538 ' where gr.object_id = fo.object_id ' ||
539 ' and fo.obj_name = ' || ''''||p_object_name ||''''||
540 ' and gr.instance_type = '||''''||'SET'||'''' ||
541 ' and grantee_type = '||''''||'USER'||'''' ||
542 ' and nvl(end_date, sysdate+1) >= trunc(sysdate) ' ||
543 ' and gr.instance_set_id = inst.instance_set_id ' ||
544 ' and 1 = AMW_VALID_INSTANCE_SET_GRANTS.check_grant_validity' ||
545 '(gr.grant_guid, ' ||lp_pk1||', '||lp_pk2||', '||lp_pk3||', '||lp_pk4||', '||lp_pk5||','||
546 ' fo.obj_name) ';
547
548 return sql_string;
549
550 end;
551
552 end AMW_VALID_INSTANCE_SET_GRANTS;