[Home] [Help]
PACKAGE BODY: APPS.QP_SECURITY
Source
1 PACKAGE BODY QP_SECURITY AS
2 /* $Header: QPXSECUB.pls 120.3.12010000.2 2008/08/29 10:47:21 hmohamme ship $ */
3 G_MENU_MAINTAIN_ID NUMBER := null;
4 FUNCTION GET_OBJECT_ID_FOR_INSTANCE(p_instance_type IN VARCHAR2 default null)
5 RETURN NUMBER IS
6 v_object_id NUMBER := null;
7 v_object_name VARCHAR2(30) := null;
8 v_instance_type qp_grants.instance_type%TYPE; --VARCHAR2(5);
9 BEGIN
10
11 v_instance_type := nvl(p_instance_type, 'ALL');
12
13 IF(G_INSTANCE_TYPE_CACHE = v_instance_type) THEN
14 v_object_id := G_OBJECT_ID_CACHE;
15 ELSE
16 BEGIN
17 IF(G_PRICELIST_OBJECT = v_instance_type OR
18 G_MODIFIER_OBJECT = v_instance_type OR
19 G_AGREEMENT_OBJECT = v_instance_type OR
20 v_instance_type = 'ALL') THEN
21 v_object_name := 'QP_LIST_HEADERS';
22 ELSIF(G_FORMULA_OBJECT = v_instance_type) THEN
23 v_object_name := 'QP_PRICE_FORMULAS';
24 END IF;
25
26 SELECT object_id
27 INTO v_object_id
28 FROM FND_OBJECTS
29 WHERE obj_name = v_object_name and application_id=661;
30
31 G_INSTANCE_TYPE_CACHE := v_instance_type;
32 G_OBJECT_ID_CACHE := v_object_id;
33 END;
34 END IF;
35
36 RETURN v_object_id;
37 END;
38
39 FUNCTION GET_OBJECT_NAME_FOR_INSTANCE(p_instance_type IN VARCHAR2)
40 RETURN VARCHAR2 IS
41 v_object_name VARCHAR2(30) := null;
42 v_instance_type qp_grants.instance_type%TYPE;--varchar2(10);
43 BEGIN
44 v_instance_type := nvl(p_instance_type, 'ALL');
45
46 IF(G_PRICELIST_OBJECT = v_instance_type OR
47 G_MODIFIER_OBJECT = v_instance_type OR
48 G_AGREEMENT_OBJECT = v_instance_type OR
49 v_instance_type = 'ALL') THEN
50 v_object_name := 'QP_LIST_HEADERS';
51 ELSIF(G_FORMULA_OBJECT = v_instance_type) THEN
52 v_object_name := 'QP_PRICE_FORMULAS';
53 END IF;
54 RETURN v_object_name;
55 END;
56
57 FUNCTION GET_FUNCTION_ID(p_function_name IN VARCHAR2)
58 RETURN NUMBER IS
59 v_function_id NUMBER;
60
61 BEGIN
62 IF(p_function_name = G_FUNCTION_NAME_CACHE) THEN
63 v_function_id := G_FUNCTION_ID_CACHE; /*we have it cached,use it*/
64 ELSE /*didn't cached, hit db*/
65 SELECT function_id
66 INTO v_function_id
67 FROM FND_FORM_FUNCTIONS
68 WHERE function_name = p_function_name;
69 /*store in cache*/
70 g_function_name_cache := p_function_name;
71 g_function_id_cache := v_function_id;
72 END IF;
73 RETURN v_function_id;
74 END;
75
76 FUNCTION GET_USER_ID(l_user_name IN VARCHAR2)
77 RETURN NUMBER IS
78 v_user_id NUMBER;
79 BEGIN
80 /* commented for FP 7310389 If G_USER_ID Is NULL or l_user_name <> G_USER_NAME
81 Then*/
82 SELECT USER_ID
83 INTO G_USER_ID
84 FROM FND_USER
85 WHERE USER_NAME = l_user_name;
86 G_USER_NAME := l_user_name;
87 --End If;
88
89 RETURN G_USER_ID;
90 EXCEPTION
91 WHEN NO_DATA_FOUND THEN
92 return -1;
93 END;
94 /*+----------------------------------------------------------------------
95 | Function check_function
96 | API name: check_function
97 | TYPE: Public
98 | FUNCTION: Determines whether user is granted a particular
99 | function for a particular pricing entity
100 | Input: p_function_name ->
101 | QP_SECU_VIEW
102 | QP_SECU_UPDATE
103 | Input: p_instance_type ->
104 | 'PRL' for standard pricelist
105 | 'MOD' for modifier
106 | 'AGR' for agreement pricelist
107 | 'FOR' for formula -- not used yet
108 | Input: p_instance_pk1_value -> list_header_id in qp_list_headers_b
109 | other primary key for formulas, qualifiers...
110 | p_instance_pk2_value -> optional, not used currently
111 | p_instance_pk3_vaue -> optional, not used currently
112 | Input: p_user_name -> optional, default is current user
113 | p_resp_id -> optional, default is current logged in responsibility
114 | p_org_id -> optional,default is current logged in operating unit
115 | Output: varchar2 ->
116 | G_AUTHORIZED='T': authorized
117 | G_DENIED='F':denied
118 | G_ERROR='E': error happened
119 +----------------------------------------------------------------------
120 */
121 FUNCTION check_function(
122 p_function_name IN VARCHAR2,
123 p_instance_type IN VARCHAR2,
124 p_instance_pk1 IN NUMBER,
125 p_instance_pk2 IN NUMBER default null,
126 p_instance_pk3 IN NUMBER default null,
127 p_user_name IN VARCHAR2 default null,
128 p_resp_id IN NUMBER default null,
129 p_org_id IN NUMBER default null
130 ) RETURN VARCHAR2 IS
131 E_ROUTINE_ERROR EXCEPTION;
132 l_security_control varchar2(5);
133 l_user_name varchar2(80);
134 l_sysdate DATE := sysdate;
135 l_user_id NUMBER;
136 l_resp_id NUMBER;
137 l_org_id NUMBER;
138 l_result VARCHAR2(1);
139 l_status VARCHAR2(1);
140 l_object_name VARCHAR2(30);
141 l_function_id NUMBER;
142 l_object_id NUMBER;
143 error_message VARCHAR2(1000);
144 l_mo_access_mode VARCHAR2(30);
145 CURSOR qp_grants_c ( cp_user_id NUMBER,
146 cp_resp_id NUMBER,
147 cp_org_id NUMBER,
148 cp_function_id NUMBER,
149 cp_object_id NUMBER,
150 cp_instance_id NUMBER,
151 cp_mo_access_mode VARCHAR2
152 ) IS
153 SELECT 'X'
154 FROM qp_grants g
155 WHERE rownum = 1
156 AND g.object_id = cp_object_id
157 AND ( ( g.grantee_type = 'USER' AND
158 g.grantee_id = cp_user_id) OR
159 ( g.grantee_type = 'RESP' AND
160 g.grantee_id = cp_resp_id) OR
161 ( g.grantee_type = 'GLOBAL' AND
162 g.grantee_id = -1) OR
163 ( g.grantee_type = 'OU' AND
164 ((cp_mo_access_mode = 'S' and g.grantee_id = sys_context('multi_org2','current_org_id'))
165 or (cp_mo_access_mode = 'M' and mo_global.check_access(g.grantee_id) = 'Y')
166 or (cp_mo_access_mode = 'A')))
167 -- g.grantee_id = cp_org_id)
168 ) AND
169 g.menu_id IN
170 (select cmf.menu_id
171 from fnd_compiled_menu_functions cmf
172 where cmf.function_id = cp_function_id)
173 AND (G.instance_id = cp_instance_id)
174 AND ( g.end_date IS NULL OR g.end_date >= l_sysdate )
175 AND g.start_date <= l_sysdate;
176
177 BEGIN
178 --added for moac
179 l_mo_access_mode := MO_GLOBAL.get_access_mode;
180
181 -- the global_flag in qp_list_headers_b table is for run-time engine use
182 -- only. For setup time, we use 'GLOBAL' as a grantee, and if a pricing
183 -- entity is granted to 'GLOBAL' with a specific function, it becomes
184 -- globally accessed with such function.
185
186 l_security_control := nvl(FND_PROFILE.value(G_SECURITY_CONTROL_PROFILE), G_SECURITY_OFF);
187 IF(l_security_control = G_SECURITY_OFF) THEN /*always return authorized*/
188 RETURN G_AUTHORIZED;
189 ELSE /*security is on*/
190 IF(( p_function_name is NULL) or (p_instance_type is NULL)
191 or (p_instance_pk1 is NULL)) THEN
192 RAISE E_ROUTINE_ERROR;
193 END IF;
194
195 -- Default the user name if not passed in
196 IF( p_user_name is NULL) THEN
197 l_user_name := FND_GLOBAL.USER_NAME;
198 ELSE
199 l_user_name := p_user_name;
200 END IF;
201
202 l_user_id := GET_USER_ID(l_user_name);
203
204 --GET_ORIG_KEY(l_user_name, l_orig_system, l_orig_system_id);
205
206 -- Default the responsibility id if not passed in
207 IF( p_resp_id is NULL) THEN
208 l_resp_id := FND_GLOBAL.RESP_ID;
209 ELSE
210 l_resp_id := p_resp_id;
211 END IF;
212 --CHECK_RESP_FOR_USER(l_user_name, l_resp_id);
213
214 -- Default the operating unit id if not passed in
215 /*
216 IF( p_org_id is NULL) THEN
217 l_org_id := FND_PROFILE.VALUE('ORG_ID');
218 ELSE
219 l_org_id := p_org_id;
220 END IF;
221 */
222 --added for MOAC to populate operating_unit
223 l_org_id := nvl(p_org_id, QP_UTIL.get_org_id);
224
225 --CHECK_ORG_FOR_USER(l_user_name, l_org_id);
226
227 -- get object_id from instance_type
228 l_object_id := GET_OBJECT_ID_FOR_INSTANCE(p_instance_type);
229 l_object_name := GET_OBJECT_NAME_FOR_INSTANCE(p_instance_type);
230 -- get function_id from function_name
231 l_function_id := GET_FUNCTION_ID(p_function_name);
232
233 /* check qp_grants table and see if the fnd_menu_id for the object
234 includes the ask-for function or not
235 if no data found, which means no any access on the object,
236 return DENIED
237 or ask for a function which doesn't be included in the object role,
238 return DENIED;
239 */
240 BEGIN
241 -----------------open cursor here
242 OPEN qp_grants_c(l_user_id,
243 l_resp_id,
244 l_org_id,
245 l_function_id,
246 l_object_id,
247 p_instance_pk1, l_mo_access_mode);
248
249 FETCH qp_grants_c INTO l_result;
250 CLOSE qp_grants_c;
251
252 IF ( l_result = 'X') THEN
253 RETURN G_AUTHORIZED;
254 END IF;
255 EXCEPTION
256 WHEN NO_DATA_FOUND THEN
257 RETURN G_DENIED;
258 END;
259
260 /* check fnd_grants table and see the access_role for the object
261 instead of calling FND_DATA_SECURITY.check_function,
262 call get_security_predicate() could improve performance
263 */
264 FND_MSG_PUB.initialize;
265 l_status := FND_DATA_SECURITY.check_function(
266 p_api_version => 1.0,
267 p_function => p_function_name,
268 p_object_name => l_object_name,
269 p_instance_pk1_value => p_instance_pk1,
270 p_instance_pk2_value => null,
271 p_instance_pk3_value => null,
272 p_instance_pk4_value => null,
273 p_instance_pk5_value => null,
274 p_user_name =>l_user_name );
275 IF(l_status = 'F') THEN
276 RETURN G_DENIED;
277 ELSIF(l_status = 'T') THEN
278 RETURN G_AUTHORIZED;
279 ELSE
280 RAISE E_ROUTINE_ERROR;
281 END IF;
282
283 RETURN G_DENIED;
284 END IF; /*end security is on*/
285 EXCEPTION
286 WHEN E_ROUTINE_ERROR THEN
287 error_message := FND_MESSAGE.GET_ENCODED;
288 RETURN G_ERROR;
289 END;
290
291
292 /*+----------------------------------------------------------------------
293 | Function auth_instances
294 | API name: auth_instances
295 | TYPE: Public
296 | FUNCTION: Get all pricing entities ids which can be accessed by
297 | the use with the particular function
298 | Input: p_function_name ->
299 | 'QP_SECU_VIEW' for view, 'QP_SECU_UPDATE' for update
300 | Input: p_instance_type ->
301 | 'PRL' for standard pricelists,
302 | 'MOD' for modifier,
303 | 'AGR' for agreement pricelists,
304 | 'FOR' for formulas --not used yet
305 | null for all list headers
306 | Input: p_user_name -> optional, default is current user
307 | p_resp_id -> optional, default is current logged in
308 | responsibility
309 | p_org_id -> optional, default is current logged in
310 | operating unit
311 | Output: system.qp_inst_pk_vals ->
312 | all the object ids which can be accessed with the specific
313 | function for current user
314 +----------------------------------------------------------------------
315 */
316
317 FUNCTION auth_instances(
318 p_function_name IN VARCHAR2,
319 p_instance_type IN VARCHAR2 default null,
320 p_user_name IN VARCHAR2 default G_USER_NAME,
321 p_resp_id IN NUMBER default G_RESP_ID,
322 p_org_id IN NUMBER default G_ORG_ID
323 ) RETURN system.qp_inst_pk_vals IS
324 l_instance_pk1_value fnd_grants.instance_pk1_value%TYPE;
325 l_instance_pk2_value fnd_grants.instance_pk2_value%TYPE;
326 l_instance_pk_values system.qp_inst_pk_vals := system.qp_inst_pk_vals();
327 TYPE qp_inst_pk_vals_cur_type IS REF CURSOR;
328 qp_inst_pk_vals_cur qp_inst_pk_vals_cur_type;
329 err_msg varchar2(256);
330 count_num number := 0;
331
332 l_database_object_name fnd_objects.database_object_name%TYPE;
333 l_pk1_column_name fnd_objects.pk1_column_name%TYPE;
334 l_pk2_column_name fnd_objects.pk2_column_name%TYPE;
335 l_predicate varchar2(32767);
336 l_return_status varchar2(1);
337 l_sql_stmt varchar2(32767);
338
339 l_security_control varchar2(5);
340 l_grantee_type varchar2(10);
341 l_grantee_id number;
342 E_ROUTINE_ERROR EXCEPTION;
343 l_user_id NUMBER;
344 l_user_name VARCHAR2(80);
345 l_resp_id NUMBER;
346 l_org_id NUMBER;
347 l_sysdate DATE := sysdate;
348 l_object_id number;
349 l_function_id number;
350 l_object_name varchar2(30);
351 error_message varchar2(1000);
352
353 CURSOR l_pricelist_id_cur IS
354 SELECT list_header_id
355 FROM qp_list_headers_b
356 WHERE list_type_code = G_PRICELIST_TYPE;
357
358 CURSOR l_modifier_id_cur IS
359 SELECT list_header_id
360 FROM qp_list_headers_b
361 WHERE list_type_code in (G_MODIFIER_SUR,G_MODIFIER_PRO,
362 G_MODIFIER_DLT,G_MODIFIER_DEL,G_MODIFIER_CHARGES);
363
364 CURSOR l_agreement_id_cur IS
365 SELECT list_header_id
366 FROM qp_list_headers_b
367 WHERE list_type_code = G_AGREEMENT_TYPE;
368
369 CURSOR l_list_headers_id_cur IS
370 SELECT list_header_id
371 FROM qp_list_headers_b;
372
373 CURSOR l_grants_list_headers_id_cur(cp_user_id NUMBER,
374 cp_resp_id NUMBER,
375 cp_org_id NUMBER,
376 cp_function_id NUMBER,
377 cp_object_id NUMBER
378 ) IS
379 SELECT g.instance_id
380 FROM qp_grants g
381 WHERE ((g.grantee_type = 'USER' AND
382 g.grantee_id = cp_user_id) OR
383 (g.grantee_type = 'RESP' AND
384 g.grantee_id = cp_resp_id) OR
385 (g.grantee_type = 'OU' AND
386 g.grantee_id = cp_org_id) OR
387 (g.grantee_type = 'GLOBAL' AND
388 g.grantee_id = -1)) AND
389 g.object_id = cp_object_id AND
390 g.menu_id IN
391 (SELECT cmf.menu_id
392 FROM fnd_compiled_menu_functions cmf
393 WHERE cmf.function_id = cp_function_id) AND
394 (g.end_date IS NULL OR g.end_date >= l_sysdate) AND
395 g.start_date <= l_sysdate;
396
397 CURSOR l_grants_entities_id_cur(cp_user_id NUMBER,
398 cp_resp_id NUMBER,
399 cp_org_id NUMBER,
400 cp_function_id NUMBER,
401 cp_object_id NUMBER,
402 cp_instance_type VARCHAR2
403 ) IS
404 SELECT g.instance_id
405 FROM qp_grants g
406 WHERE ((g.grantee_type = 'USER' AND
407 g.grantee_id = cp_user_id) OR
408 (g.grantee_type = 'RESP' AND
409 g.grantee_id = cp_resp_id) OR
410 (g.grantee_type = 'OU' AND
411 g.grantee_id = cp_org_id) OR
412 (g.grantee_type = 'GLOBAL' AND
413 g.grantee_id = -1)) AND
414 g.object_id = cp_object_id AND
415 g.instance_type = cp_instance_type AND
416 g.menu_id IN
417 (SELECT cmf.menu_id
418 FROM fnd_compiled_menu_functions cmf
419 WHERE cmf.function_id = cp_function_id) AND
420 (g.end_date IS NULL OR g.end_date >= l_sysdate) AND
421 g.start_date <= l_sysdate;
422 BEGIN
423 /* we have three profile for security
424 QP_SECURITY_CONTROL: ON/OFF
425 QP_SECUIRTY_DEFAULT_VIEW: NONE, OU, RESP, USER
426 QP_SECURITY_DEFAULT_UPDATE: NONE, OU, RESP, USER
427 */
428 /* check 'QP_SECURITY_CONTROL' profile to see security is on/off.
429 In case of 'OFF', return all object ids
430 */
431 l_security_control := nvl(FND_PROFILE.value(G_SECURITY_CONTROL_PROFILE), G_SECURITY_OFF);
432
433 IF(l_security_control = G_SECURITY_OFF) THEN
434 /*return all list_headers_id */
435 IF( p_instance_type = G_PRICELIST_OBJECT) THEN
436 OPEN l_pricelist_id_cur;
437 LOOP
438 FETCH l_pricelist_id_cur
439 INTO l_instance_pk1_value;
440 EXIT WHEN l_pricelist_id_cur%NOTFOUND;
441 l_instance_pk2_value := null;
442
443 l_instance_pk_values.extend;
444 count_num:= count_num + 1;
445 l_instance_pk_values(l_instance_pk_values.last):=
446 system.qp_inst_pk_vals_object(l_instance_pk1_value,
447 l_instance_pk2_value);
448 END LOOP;
449 CLOSE l_pricelist_id_cur;
450 ELSIF( p_instance_type = G_MODIFIER_OBJECT) THEN
451 OPEN l_modifier_id_cur;
452 LOOP
453 FETCH l_modifier_id_cur
454 INTO l_instance_pk1_value;
455 EXIT WHEN l_modifier_id_cur%NOTFOUND;
456 l_instance_pk2_value := null;
457
458 l_instance_pk_values.extend;
459 count_num:= count_num + 1;
460 l_instance_pk_values(l_instance_pk_values.last):=
461 system.qp_inst_pk_vals_object(l_instance_pk1_value,
462 l_instance_pk2_value);
463 END LOOP;
464 CLOSE l_modifier_id_cur;
465 ELSIF( p_instance_type = G_AGREEMENT_OBJECT) THEN
466 OPEN l_agreement_id_cur;
467 LOOP
468 FETCH l_agreement_id_cur
469 INTO l_instance_pk1_value;
470 EXIT WHEN l_agreement_id_cur%NOTFOUND;
471 l_instance_pk2_value := null;
472
473 l_instance_pk_values.extend;
474 count_num:= count_num + 1;
475 l_instance_pk_values(l_instance_pk_values.last):=
476 system.qp_inst_pk_vals_object(l_instance_pk1_value,
477 l_instance_pk2_value);
478 END LOOP;
479 CLOSE l_agreement_id_cur;
480 ELSE /*null for all*/
481 OPEN l_list_headers_id_cur;
482 LOOP
483 FETCH l_list_headers_id_cur
484 INTO l_instance_pk1_value;
485 EXIT WHEN l_list_headers_id_cur%NOTFOUND;
486 l_instance_pk2_value := null;
487
488 l_instance_pk_values.extend;
489 count_num:= count_num + 1;
490 l_instance_pk_values(l_instance_pk_values.last):=
491 system.qp_inst_pk_vals_object(l_instance_pk1_value,
492 l_instance_pk2_value);
493 END LOOP;
494 CLOSE l_list_headers_id_cur;
495 END IF;
496
497 RETURN l_instance_pk_values;
498 ELSE /*security is on*/
499 IF( p_function_name is NULL ) THEN
500 RAISE E_ROUTINE_ERROR;
501 END IF;
502
503 -- Default the user name if not passed in
504 IF( p_user_name is NULL) THEN
505 l_user_name := FND_GLOBAL.USER_NAME;
506 ELSE
507 l_user_name := p_user_name;
508 END IF;
509
510 l_user_id := GET_USER_ID(l_user_name);
511 --l_user_id := FND_GLOBAL.USER_ID;
512
513 --GET_ORIG_KEY(l_user_name, l_orig_system, l_orig_system_id);
514
515 -- Default the responsibility id if not passed in
516 IF( p_resp_id is NULL) THEN
517 l_resp_id := FND_GLOBAL.RESP_ID;
518 ELSE
519 l_resp_id := p_resp_id;
520 END IF;
521 --CHECK_RESP_FOR_USER(l_user_name, l_resp_id);
522
523 -- Default the operating unit id if not passed in
524 /*
525 IF( p_org_id is NULL) THEN
526 l_org_id := to_number(FND_PROFILE.VALUE('ORG_ID'));
527 ELSE
528 l_org_id := p_org_id;
529 END IF;
530 */
531 --added for MOAC
532 l_org_id := nvl(p_org_id, QP_UTIL.get_org_id);
533
534 --CHECK_ORG_FOR_USER(l_user_name, l_org_id);
535
536 l_object_id := GET_OBJECT_ID_FOR_INSTANCE(p_instance_type);
537 l_object_name := GET_OBJECT_NAME_FOR_INSTANCE(p_instance_type);
538 l_function_id := GET_FUNCTION_ID(p_function_name);
539
540 /*check qp_grants INSTANCE table to get all list_header_ids which
541 can be accessed with the specific function*/
542 IF ( p_instance_type is null) THEN /*get list_headers_id for all types*/
543 OPEN l_grants_list_headers_id_cur(l_user_id,
544 l_resp_id,
545 l_org_id,
546 l_function_id,
547 l_object_id);
548 LOOP
549 FETCH l_grants_list_headers_id_cur
550 INTO l_instance_pk1_value;
551 EXIT WHEN l_grants_list_headers_id_cur%NOTFOUND;
552 l_instance_pk2_value := null;
553 l_instance_pk_values.extend;
554 count_num:= count_num + 1;
555
556 l_instance_pk_values(l_instance_pk_values.last):=
557 system.qp_inst_pk_vals_object(l_instance_pk1_value,
558 l_instance_pk2_value);
559 END LOOP;
560 CLOSE l_grants_list_headers_id_cur;
561 ELSIF( p_instance_type = G_PRICELIST_OBJECT or
562 p_instance_type = G_MODIFIER_OBJECT or
563 p_instance_type = G_AGREEMENT_OBJECT or
564 p_instance_type = G_FORMULA_OBJECT) THEN
565 OPEN l_grants_entities_id_cur(l_user_id,
566 l_resp_id,
567 l_org_id,
568 l_function_id,
569 l_object_id,
570 p_instance_type);
571 LOOP
572 FETCH l_grants_entities_id_cur
573 INTO l_instance_pk1_value;
574 EXIT WHEN l_grants_entities_id_cur%NOTFOUND;
575 l_instance_pk2_value := null;
576 l_instance_pk_values.extend;
577 count_num:= count_num + 1;
578
579 l_instance_pk_values(l_instance_pk_values.last):=
580 system.qp_inst_pk_vals_object(l_instance_pk1_value,
581 l_instance_pk2_value);
582 END LOOP;
583 CLOSE l_grants_entities_id_cur;
584 END IF;
585
586 ----------------------------------------------------------------------
587 -- check fnd_grants SET table to get all list_header_ids which
588 -- can be accessed
589 ------------------------------------------------------------------------
590 --get database_object_name and primary key column name from FND_OBJECTS
591 SELECT database_object_name,
592 pk1_column_name,
593 pk2_column_name
594 INTO l_database_object_name,
595 l_pk1_column_name,
596 l_pk2_column_name
597 FROM fnd_objects
598 WHERE obj_name = l_object_name;
599
600
601 --get security predicate to filter authorized instances
602 FND_DATA_SECURITY.get_security_predicate(
603 p_api_version => 1.0,
604 p_function => p_function_name,
605 p_object_name => l_object_name,
606 p_grant_instance_type =>'SET',
607 p_user_name => p_user_name,
608 x_predicate => l_predicate,
609 x_return_status => l_return_status);
610
611 IF(l_return_status = 'T') THEN
612 --dynamically contruct a sql statement
613 l_sql_stmt := 'Select ' ||
614 nvl(l_pk1_column_name, 'NULL') ||
615 ' from ' || l_database_object_name ||
616 ' where ' || l_predicate;
617 -- return collection of authorized objects ids
618 OPEN qp_inst_pk_vals_cur FOR l_sql_stmt;
619 LOOP
620 FETCH qp_inst_pk_vals_cur
621 INTO l_instance_pk1_value;
622 EXIT WHEN qp_inst_pk_vals_cur%NOTFOUND;
623 l_instance_pk2_value := null;
624 l_instance_pk_values.extend;
625 count_num:= count_num + 1;
626
627 l_instance_pk_values(l_instance_pk_values.last) :=
628 system.qp_inst_pk_vals_object(l_instance_pk1_value,
629 l_instance_pk2_value);
630 END LOOP;
631 CLOSE qp_inst_pk_vals_cur;
632 ELSIF(l_return_status = 'E' or
633 l_return_status = 'U' or
634 l_return_status = 'L') THEN /*E, U, L*/
635 error_message := FND_MESSAGE.GET_ENCODED;
636 RAISE E_ROUTINE_ERROR;
637 END IF;
638
639 RETURN l_instance_pk_values;
640
641 END IF; /*end security is on*/
642 /* catching general exceptions is necessary because oracle 8i terminates
643 a connection upon encountering a pl_sql error in function
644 */
645 EXCEPTION
646 WHEN E_ROUTINE_ERROR THEN
647 return null;
648 WHEN OTHERS THEN
649 err_msg := fnd_message.get;
650 END auth_instances;
651
652 FUNCTION get_menu_id(p_menu_name IN VARCHAR2)
653 RETURN NUMBER IS
654 v_menu_id number;
655 BEGIN
656 SELECT menu_id
657 INTO v_menu_id
658 FROM fnd_menus
659 WHERE menu_name = p_menu_name;
660
661 RETURN v_menu_id;
662 EXCEPTION
663 WHEN NO_DATA_FOUND THEN
664 RETURN null;
665 END get_menu_id;
666
667 /*+----------------------------------------------------------------------
668 | Procedure create_default_grants
669 | API name: create_default_grants
670 | TYPE: Public
671 | PROCEDURE Create default grants for newly created pricing entitiy
672 | based on the defaulting security profile settings.
673 | IMPORANT NOTE: This API should be called ONLY when creating a NEW pricing entity.
674 | Input: p_instance_type ->
675 | 'PRL' for standard pricelists,
676 | 'MOD' for modifier,
677 | 'AGR' for agreement pricelists,
678 | 'FOR' for formulas --not used yet
679 | Input: p_instance_pk1 -> id of the newly created pricing entity
680 | p_instance_pk2 -> not used in current release
681 | p_instance_pk3 -> not used in current release
682 | Input: p_user_name -> optional, default is current user
683 | p_resp_id -> optional, default is current logged in
684 | responsibility
685 | p_org_id -> optional, default is current logged in
686 | operating unit
687 | Output: x_return_status ->
688 | 'S'-> successfully done
689 | 'E'-> error, possible reason is: p_instance_pk1 is already existing in qp_list_headers_b
690 | as one list_header_id. Make sure you didn't call commit()
691 | before you call this API when creating a new pricing entity.
692 +----------------------------------------------------------------------
693 */
694 PROCEDURE create_default_grants(p_instance_type IN VARCHAR2,
695 p_instance_pk1 IN NUMBER,
696 p_instance_pk2 IN NUMBER default null,
697 p_instance_pk3 IN NUMBER default null,
698 p_user_name IN VARCHAR2 default null,
699 p_resp_id IN NUMBER default null,
700 p_org_id IN NUMBER default null,
701 x_return_status OUT NOCOPY VARCHAR2) IS
702 l_security_control varchar2(5);
703 l_security_default_viewonly varchar2(10);
704 l_security_default_maintain varchar2(10);
705 l_grantee_type varchar2(10);
706 l_grantee_id number;
707 l_user_name varchar2(30);
708 l_grant_id number;
709 l_object_id number;
710 l_menu_id number;
711 l_start_date date;
712 l_end_date date;
713 l_last_update_date date;
714 l_last_updated_by number;
715 l_creation_date number;
716 l_created_by number;
717 l_last_update_login number;
718 l_list_type_code varchar2(30);
719 CURSOR list_header_id_exist_cur(cp_instance_id number) IS
720 SELECT list_type_code
721 FROM qp_list_headers_b
722 WHERE list_header_id = cp_instance_id;
723 l_dummy varchar2(1);
724 CURSOR grant_exist_cur(cp_grantee_type varchar2,
725 cp_grantee_id number,
726 cp_instance_type varchar2,
727 cp_instance_id number,
728 cp_object_id number) IS
729 SELECT 'X'
730 FROM qp_grants
731 WHERE grantee_type = cp_grantee_type AND
732 grantee_id = cp_grantee_id AND
733 instance_type = cp_instance_type AND
734 instance_id = cp_instance_id AND
735 object_id = cp_object_id ;
736 BEGIN
737 /* QP_SECUIRTY_DEFAULT_VIEW: GLOBAL, OU, RESP, USER, NONE
738 QP_SECURITY_DEFAULT_UPDATE: GLOBAL, OU, RESP, USER, NONE
739 */
740 /* validate p_instance_pk1
741 */
742 OPEN list_header_id_exist_cur(p_instance_pk1);
743 FETCH list_header_id_exist_cur into l_list_type_code;
744 IF list_header_id_exist_cur%FOUND THEN
745 IF(l_list_type_code <> p_instance_type) THEN
746 IF(l_list_type_code not in ('CHARGES', 'DEL', 'DLT', 'PRO', 'SLT')
747 or p_instance_type <> 'MOD') THEN
748 x_return_status := 'E';
749 CLOSE list_header_id_exist_cur;
750 RETURN;
751 END IF;/*else, 'CHARGES','DEL','DLT','PRO','SLT' matches 'MOD'. pass valdiation*/
752 END IF; /*else 'AGR'='AGR' || 'PRL'='PRL'. pass validation*/
753 END IF; /*else, new id*/
754 CLOSE list_header_id_exist_cur;
755
756 /* check 'QP_SECURITY_CONTROL' profile to see security is on/off.
757 In case of 'OFF', return default is GLOBAL MAINTAIN
758 */
759 l_security_control := nvl(FND_PROFILE.value(G_SECURITY_CONTROL_PROFILE), G_SECURITY_OFF);
760 IF(l_security_control = G_SECURITY_OFF) THEN
761 /*security is off, do nothing*/
762 l_security_default_viewonly := G_SECURITY_LEVEL_NONE;
763 l_security_default_maintain := G_SECURITY_LEVEL_NONE;
764 ELSE/*security is on*/
765 l_security_default_viewonly := nvl(FND_PROFILE.value(G_SECURITY_DEFAULT_VIEWONLY), G_SECURITY_LEVEL_GLOBAL);
766 l_security_default_maintain := nvl(FND_PROFILE.value(G_SECURITY_DEFAULT_MAINTAIN), G_SECURITY_LEVEL_GLOBAL);
767 END IF;
768
769 --in case l_security_default_viewonly == l_security_default_maintain,
770 -- we only take l_security_default_maintain privilege
771 IF(l_security_default_viewonly <> G_SECURITY_LEVEL_NONE and
772 l_security_default_viewonly <> l_security_default_maintain) THEN
773 BEGIN
774 IF(l_security_default_viewonly = G_SECURITY_LEVEL_GLOBAL)THEN
775 l_grantee_type := 'GLOBAL';
776 l_grantee_id := -1;
777 ELSIF(l_security_default_viewonly = G_SECURITY_LEVEL_OU) THEN
778 l_grantee_type := 'OU';
779 --l_grantee_id :=nvl(p_org_id, FND_PROFILE.VALUE('ORG_ID'));
780 --added for MOAC
781 l_grantee_id := nvl(p_org_id, QP_UTIL.get_org_id);
782 ELSIF(l_security_default_viewonly = G_SECURITY_LEVEL_RESP) THEN
783 l_grantee_type := 'RESP';
784 l_grantee_id :=nvl(p_resp_id, FND_GLOBAL.RESP_ID);
785 ELSIF(l_security_default_viewonly = G_SECURITY_LEVEL_USER) THEN
786 l_grantee_type := 'USER';
787 l_user_name := nvl(p_user_name, FND_GLOBAL.USER_NAME);
788 l_grantee_id :=GET_USER_ID(l_user_name);
789 END IF;
790
791 /*make sure the wanted privilege is not existing already
792 If already, update the privilege; otherwise, insert new.*/
793
794 l_object_id := GET_OBJECT_ID_FOR_INSTANCE(p_instance_type);
795 l_menu_id := GET_MENU_ID('QP_SECU_VIEWONLY');
796 --l_start_date := sysdate;
797 --l_end_date := null;
798 --l_last_update_date := sysdate;
799 --l_last_updated_by := GET_USER_ID(nvl(p_user_name, FND_GLOBAL.USER_NAME));
800 l_last_updated_by := FND_GLOBAL.USER_ID;
801 --l_creation_date := sysdate;
802 l_created_by := l_last_updated_by;
803 l_last_update_login := FND_GLOBAL.LOGIN_ID ;
804
805 /*if not existing, insert new*/
806 OPEN grant_exist_cur(l_grantee_type, l_grantee_id,
807 p_instance_type, p_instance_pk1, l_object_id);
808 FETCH grant_exist_cur INTO l_dummy;
809 IF(grant_exist_cur%NOTFOUND) THEN
810 SELECT QP_GRANTS_S.nextval INTO l_grant_id FROM DUAL;
811 INSERT INTO qp_grants
812 (GRANT_ID,
813 OBJECT_ID,
814 INSTANCE_TYPE,
815 INSTANCE_ID,
816 GRANTEE_TYPE,
817 GRANTEE_ID,
818 MENU_ID,
819 START_DATE,
820 END_DATE,
821 LAST_UPDATE_DATE,
822 LAST_UPDATED_BY,
823 CREATION_DATE,
824 CREATED_BY,
825 LAST_UPDATE_LOGIN)
826 VALUES
827 (l_grant_id,
828 l_object_id,
829 p_instance_type,
830 p_instance_pk1,
831 l_grantee_type,
832 l_grantee_id,
833 l_menu_id,
834 sysdate, --l_start_date,
835 null, --l_end_date,
836 sysdate, --l_last_update_date,
837 l_last_updated_by,
838 sysdate, --l_creation_date,
839 l_created_by,
840 l_last_update_login );
841 ELSE /*update the existing one*/
842 UPDATE qp_grants
843 SET MENU_ID = l_menu_id,
844 START_DATE = sysdate ,
845 END_DATE = null,
846 LAST_UPDATE_DATE = sysdate,
847 LAST_UPDATED_BY = l_created_by,
848 LAST_UPDATE_LOGIN = l_last_update_login
849 WHERE GRANT_ID = (select grant_id
850 FROM qp_grants
851 WHERE grantee_type = l_grantee_type AND
852 grantee_id = l_grantee_id AND
853 instance_type = p_instance_type AND
854 instance_id = p_instance_pk1 AND
855 object_id = l_object_id AND
856 ROWNUM = 1);
857 END IF;
858 CLOSE grant_exist_cur;
859 END;
860 END IF; /*else do nothing*/
861
862 IF(l_security_default_maintain <> G_SECURITY_LEVEL_NONE) THEN
863 BEGIN
864 IF(l_security_default_maintain = G_SECURITY_LEVEL_GLOBAL)THEN
865 l_grantee_type := 'GLOBAL';
866 l_grantee_id := -1;
867 ELSIF(l_security_default_maintain = G_SECURITY_LEVEL_OU) THEN
868 l_grantee_type := 'OU';
869 --l_grantee_id :=nvl(p_org_id, FND_PROFILE.VALUE('ORG_ID'));
870 --added for MOAC
871 l_grantee_id := nvl(p_org_id, QP_UTIL.get_org_id);
872 ELSIF(l_security_default_maintain = G_SECURITY_LEVEL_RESP) THEN
873 l_grantee_type := 'RESP';
874 l_grantee_id :=nvl(p_resp_id, FND_GLOBAL.RESP_ID);
875 ELSIF(l_security_default_maintain = G_SECURITY_LEVEL_USER) THEN
876 l_grantee_type := 'USER';
877 l_user_name := nvl(p_user_name, FND_GLOBAL.USER_NAME);
878 l_grantee_id :=GET_USER_ID(l_user_name);
879 END IF;
880
881 l_object_id := GET_OBJECT_ID_FOR_INSTANCE(p_instance_type);
882 l_menu_id := GET_MENU_ID('QP_SECU_MAINTAIN');
883 --l_start_date := sysdate;
884 l_end_date := null;
885 --l_last_update_date := sysdate;
886 --l_last_updated_by := GET_USER_ID(nvl(p_user_name, FND_GLOBAL.USER_NAME));
887 l_last_updated_by := FND_GLOBAL.USER_ID;
888 --l_creation_date := sysdate;
889 l_created_by := l_last_updated_by;
890 l_last_update_login := FND_GLOBAL.LOGIN_ID ;
891
892 /*if not existing, insert new*/
893 OPEN grant_exist_cur(l_grantee_type, l_grantee_id,
894 p_instance_type, p_instance_pk1, l_object_id);
895 FETCH grant_exist_cur INTO l_dummy;
896 IF(grant_exist_cur%NOTFOUND) THEN
897 SELECT QP_GRANTS_S.nextval INTO l_grant_id FROM DUAL;
898 INSERT INTO qp_grants
899 (GRANT_ID,
900 OBJECT_ID,
901 INSTANCE_TYPE,
902 INSTANCE_ID,
903 GRANTEE_TYPE,
904 GRANTEE_ID,
905 MENU_ID,
906 START_DATE,
907 END_DATE,
908 LAST_UPDATE_DATE,
909 LAST_UPDATED_BY,
910 CREATION_DATE,
911 CREATED_BY,
912 LAST_UPDATE_LOGIN)
913 VALUES
914 (l_grant_id,
915 l_object_id,
916 p_instance_type,
917 p_instance_pk1,
918 l_grantee_type,
919 l_grantee_id,
920 l_menu_id,
921 sysdate, --l_start_date,
922 null, --l_end_date,
923 sysdate, --l_last_update_date,
924 l_last_updated_by,
925 sysdate, --l_creation_date,
926 l_created_by,
927 l_last_update_login );
928 ELSE /*update the existing one*/
929 UPDATE qp_grants
930 SET MENU_ID = l_menu_id,
931 START_DATE = sysdate ,
932 END_DATE = null,
933 LAST_UPDATE_DATE = sysdate,
934 LAST_UPDATED_BY = l_created_by,
935 LAST_UPDATE_LOGIN = l_last_update_login
936 WHERE GRANT_ID = (select grant_id
937 FROM qp_grants
938 WHERE grantee_type = l_grantee_type AND
939 grantee_id = l_grantee_id AND
940 instance_type = p_instance_type AND
941 instance_id = p_instance_pk1 AND
942 object_id = l_object_id AND
943 ROWNUM = 1);
944 END IF;
945 CLOSE grant_exist_cur;
946 END;
947 END IF;/*end of security is on*/
948
949 x_return_status := 'S';
950
951 RETURN;
952 EXCEPTION
953 When OTHERS THEN
954 x_return_status := 'E';
955 END create_default_grants;
956
957 function security_on
958 RETURN VARCHAR2 IS
959 l_security_control varchar2(5);
960 BEGIN
961 l_security_control :=
962 nvl(FND_PROFILE.value(G_SECURITY_CONTROL_PROFILE), G_SECURITY_OFF);
963 IF(l_security_control = G_SECURITY_OFF) THEN
964 RETURN 'N';
965 ELSE
966 RETURN 'Y';
967 END IF;
968 END security_on;
969
970 Procedure Set_Grants(p_user_name IN VARCHAR2,
971 p_resp_id IN NUMBER,
972 p_org_id IN NUMBER
973 )
974 IS
975 BEGIN
976
977 G_USER_NAME := p_user_name;
978 G_RESP_ID := p_resp_id;
979 G_ORG_ID := p_org_id;
980
981 END Set_Grants;
982
983 FUNCTION qp_v_sec(owner varchar2, objname varchar2)
984 RETURN varchar2 IS
985 l_predicate varchar2(32767);
986 l_return_status varchar2(1);
987 l_user_name varchar2(80);
988 l_security_control varchar2(5);
989 BEGIN
990 l_security_control := nvl(FND_PROFILE.value(G_SECURITY_CONTROL_PROFILE), G_SECURITY_OFF);
991 IF(l_security_control = G_SECURITY_OFF) THEN /*always return authorized*/
992 RETURN '(1=1)';
993 ELSE /*security is on*/
994 FND_DATA_SECURITY.get_security_predicate(
995 p_api_version => 1.0,
996 p_function => 'QP_SECU_VIEW',
997 p_object_name => 'QP_LIST_HEADERS',
998 p_grant_instance_type =>'SET',
999 p_user_name => qp_security.g_user_name,
1000 x_predicate => l_predicate,
1001 x_return_status => l_return_status);
1002
1003 IF(l_return_status = 'T') THEN
1004 RETURN l_predicate;
1005 ELSIF(l_return_status = 'E' or
1006 l_return_status = 'U' or
1007 l_return_status = 'L') THEN /*E, U, L*/
1008 FND_MESSAGE.CLEAR();
1009 --error_message := FND_MESSAGE.GET_ENCODED;
1010 --RAISE E_ROUTINE_ERROR;
1011 l_predicate := '(1=2)';
1012 RETURN l_predicate;
1013 END IF;
1014 END IF;
1015 EXCEPTION
1016 WHEN OTHERS THEN
1017 l_predicate := '(1=2)';
1018 RETURN l_predicate;
1019 END qp_v_sec;
1020
1021 FUNCTION qp_vl_sec(owner varchar2, objname varchar2)
1022 RETURN varchar2 IS
1023 l_predicate varchar2(32767);
1024 l_return_status varchar2(1);
1025 l_user_name varchar2(80);
1026 l_security_control varchar2(5);
1027 BEGIN
1028 l_security_control := nvl(FND_PROFILE.value(G_SECURITY_CONTROL_PROFILE), G_SECURITY_OFF);
1029 IF(l_security_control = G_SECURITY_OFF) THEN /*always return authorized*/
1030 RETURN '(1=1)';
1031 ELSE /*security is on*/
1032 FND_DATA_SECURITY.get_security_predicate(
1033 p_api_version => 1.0,
1034 p_function => 'QP_SECU_VIEW',
1035 p_object_name => 'QP_LIST_HEADERS',
1036 p_grant_instance_type =>'SET',
1037 p_user_name => qp_security.g_user_name,
1038 x_predicate => l_predicate,
1039 x_return_status => l_return_status);
1040
1041 IF(l_return_status = 'T') THEN
1042 RETURN l_predicate;
1043 ELSIF(l_return_status = 'E' or
1044 l_return_status = 'U' or
1045 l_return_status = 'L') THEN /*E, U, L*/
1046 FND_MESSAGE.CLEAR();
1047 --error_message := FND_MESSAGE.GET_ENCODED;
1048 --RAISE E_ROUTINE_ERROR;
1049 l_predicate := '(1=2)';
1050 RETURN l_predicate;
1051 END IF;
1052 END IF;
1053 EXCEPTION
1054 WHEN OTHERS THEN
1055 l_predicate := '(1=2)';
1056 RETURN l_predicate;
1057 END qp_vl_sec;
1058
1059 FUNCTION GET_RESP_ID
1060 RETURN NUMBER IS
1061 BEGIN
1062 /* commented for FP 7310389 If G_RESP_ID Is NULL
1063 Then*/
1064 G_RESP_ID := FND_GLOBAL.RESP_ID;
1065 --End If;
1066
1067 RETURN G_RESP_ID;
1068 END GET_RESP_ID;
1069
1070 FUNCTION GET_ORG_ID
1071 RETURN NUMBER IS
1072 BEGIN
1073 If G_ORG_ID Is NULL
1074 Then
1075 --G_ORG_ID := FND_PROFILE.VALUE ('ORG_ID');
1076 --added for MOAC
1077 G_ORG_ID := QP_UTIL.get_org_id;
1078 End If;
1079
1080 RETURN G_ORG_ID;
1081 END GET_ORG_ID;
1082
1083 FUNCTION GET_USER_NAME
1084 RETURN VARCHAR2 IS
1085 BEGIN
1086 If G_USER_NAME Is NULL
1087 Then
1088 G_USER_NAME := FND_GLOBAL.USER_NAME;
1089 End If;
1090
1091 RETURN G_USER_NAME;
1092 END GET_USER_NAME;
1093
1094 FUNCTION GET_MENU_MAINTAIN_ID
1095 RETURN NUMBER IS
1096
1097 l_menu_id NUMBER;
1098 BEGIN
1099 IF G_MENU_MAINTAIN_ID Is NULL
1100 THEN
1101 select function_id into l_menu_id from fnd_form_functions where function_name = 'QP_SECU_UPDATE';
1102 G_MENU_MAINTAIN_ID := l_menu_id;
1103 ELSE
1104 l_menu_id := G_MENU_MAINTAIN_ID;
1105 END IF;
1106 RETURN l_menu_id;
1107 END GET_MENU_MAINTAIN_ID;
1108
1109
1110 FUNCTION GET_UPDATE_ALLOWED (p_object_name IN VARCHAR2, p_list_header_id IN NUMBER)
1111 RETURN VARCHAR2 IS
1112
1113 l_result VARCHAR2(1);
1114 CURSOR qp_grants_c ( cp_user_id NUMBER,
1115 cp_resp_id NUMBER,
1116 cp_org_id NUMBER,
1117 cp_function_id NUMBER,
1118 cp_instance_id NUMBER
1119 ) IS
1120 SELECT 'X'
1121 FROM qp_grants g
1122 WHERE rownum = 1
1123 AND ( ( g.grantee_type = 'USER' AND
1124 g.grantee_id = cp_user_id) OR
1125 ( g.grantee_type = 'RESP' AND
1126 g.grantee_id = cp_resp_id) OR
1127 ( g.grantee_type = 'GLOBAL' AND
1128 g.grantee_id = -1) OR
1129 ( g.grantee_type = 'OU' AND
1130 (mo_global.get_access_mode = 'S' and sys_context('multi_org2', 'current_org_id') = g.grantee_id)
1131 OR (mo_global.get_access_mode = 'A')
1132 OR (mo_global.get_access_mode = 'M' and mo_global.check_access(g.grantee_id) = 'Y'))
1133 ) AND
1134 g.menu_id IN
1135 (select cmf.menu_id
1136 from fnd_compiled_menu_functions cmf
1137 where cmf.function_id = cp_function_id)
1138 AND (G.instance_id = cp_instance_id)
1139 AND ( g.end_date IS NULL OR g.end_date >= sysdate )
1140 AND g.start_date <= sysdate;
1141
1142 BEGIN
1143
1144 if(FND_PROFILE.value('QP_SECURITY_CONTROL') = 'OFF') then
1145 l_result := 'Y';
1146 return l_result;
1147 end if;
1148
1149 OPEN qp_grants_c(qp_SECURITY.get_user_id ,
1150 qp_SECURITY.get_resp_id ,
1151 qp_SECURITY.get_org_id ,
1152 qp_SECURITY.get_menu_maintain_id,
1153 p_list_header_id);
1154
1155 FETCH qp_grants_c INTO l_result;
1156 CLOSE qp_grants_c;
1157
1158 IF ( l_result = 'X') THEN
1159 RETURN 'Y';
1160 END IF;
1161
1162 l_result := FND_DATA_SECURITY.check_function(
1163 p_api_version => 1.0,
1164 p_function => 'QP_SECU_UPDATE',
1165 p_object_name => p_object_name,
1166 p_instance_pk1_value => p_list_header_id,
1167 p_instance_pk2_value => null,
1168 p_instance_pk3_value => null,
1169 p_instance_pk4_value => null,
1170 p_instance_pk5_value => null,
1171 p_user_name => qp_SECURITY.g_user_name );
1172 IF(l_result = 'F') THEN
1173 RETURN 'N';
1174 ELSIF(l_result = 'T') THEN
1175 RETURN 'Y';
1176 ELSE
1177 RETURN 'N';
1178 END IF;
1179
1180 RETURN 'N';
1181
1182 EXCEPTION
1183 WHEN NO_DATA_FOUND THEN
1184 RETURN 'N';
1185 END GET_UPDATE_ALLOWED;
1186
1187
1188 -------------moac vpd --------------
1189 --added for MOAC
1190 --this will be the VPD policy for the secured synonym qp_list_headers_b
1191 --
1192 -- Name
1193 -- qp_org_security
1194 --
1195 -- Purpose
1196 -- This function implements the security policy for the Multi-Org
1197 -- Access Control mechanism for QP_LIST_HEADERS_B.
1198 -- It is automatically called by the oracle
1199 -- server whenever a secured table or view is referenced by a SQL
1200 -- statement. Products should not call this function directly.
1201 --
1202 -- The security policy function is expected to return a predicate
1203 -- (a WHERE clause) that will control which records can be accessed
1204 -- or modified by the SQL statement. After incorporating the
1205 -- predicate, the server will parse, optimize and execute the
1206 -- modified statement.
1207 --
1208 -- Arguments
1209 -- obj_schema - the schema that owns the secured object
1210 -- obj_name - the name of the secured object
1211 --
1212
1213 FUNCTION QP_ORG_SECURITY(obj_schema VARCHAR2,
1214 obj_name VARCHAR2) RETURN VARCHAR2
1215 IS
1216 l_access_mode VARCHAR2(10);
1217 BEGIN
1218
1219 l_access_mode := MO_GLOBAL.get_access_mode;
1220
1221 -- IF PRICING SECURITY IS ON
1222 -- Returns different predicates based on the access_mode
1223 -- The codes for access_mode are
1224 -- M - Multiple OU Access
1225 -- A - All OU Access
1226 -- S - Single OU Access
1227 -- Null - Backward Compatibility - CLIENT_INFO case
1228 --
1229
1230 -- IF PRICING SECURITY IS OFF
1231 -- Returns Null
1232
1233 -- The Predicates will be appended to Multi-Org synonyms
1234
1235 IF Security_On = 'Y' THEN
1236 IF l_access_mode IS NOT NULL THEN
1237 IF l_access_mode = 'M' THEN
1238 RETURN 'global_flag = ''Y''
1239 or EXISTS (SELECT 1
1240 FROM mo_glob_org_access_tmp oa
1241 WHERE oa.organization_id = orig_org_id)';
1242 ELSIF l_access_mode = 'A' THEN -- for future use
1243 RETURN NULL;
1244 ELSIF l_access_mode = 'S' THEN -- this is for backward compatibility to MO: Operating Unit
1245 RETURN 'global_flag = ''Y'' or orig_org_id = sys_context(''multi_org2'',''current_org_id'')';
1246 END IF;
1247 ELSE
1248 return null;
1249 END IF;
1250 ELSE -- Pricing Security OFF
1251 return null;
1252 END IF;
1253 EXCEPTION
1254 When OTHERS Then
1255 return null;
1256 END QP_ORG_SECURITY;
1257
1258 END QP_SECURITY;