[Home] [Help]
PACKAGE BODY: APPS.QA_SECURITY_PKG
Source
1 PACKAGE BODY QA_SECURITY_PKG AS
2 /* $Header: qltsecb.plb 120.5 2006/08/02 07:52:40 ntungare noship $ */
3
4 --
5 -- R12 ER 2648803. Rewritten to use TCA V2 APIs.
6 -- A new constant is required to stand for created_by_module.
7 -- This has been registered with TCA and they will validate
8 -- all API calls are invoked by registered modules.
9 -- bso Thu Jul 14 13:55:27 PDT 2005
10 --
11
12 c_module_name CONSTANT VARCHAR2(6) := 'QLTSEC';
13 c_app_name CONSTANT VARCHAR2(2) := 'QA';
14
15
16 /* There are three private procedures QA_GRANTS and QA_REVOKE
17 CALL_GRANTS */
18 ---------------------------------------------------------------
19 Procedure QA_GRANTS(p1_grantee_type in varchar2,
20 p1_grantee_key in varchar2,
21 p_plan_id in number,
22 p1_menu_name in varchar2,
23 x1_grant_guid out NOCOPY raw) IS
24 ---------------------------------------------------------------
25
26 result VARCHAR2(5);
27 errorcode NUMBER;
28 begin
29 --test_mesg1('p1 grantee type :'||p1_grantee_type||' p_plan_id :'||to_char(p_plan_id));
30 --test_mesg1('p1_grantee_key :'||p1_grantee_key||' p1_menu_name :'||p1_menu_name);
31
32 fnd_grants_pkg.grant_function(
33 p_api_version => 1.0,
34 p_menu_name => p1_menu_name,
35 p_object_name => 'QA_PLANS',
36 p_instance_type => 'INSTANCE',
37 p_instance_pk1_value => to_char(p_plan_id),
38 p_grantee_type => p1_grantee_type,
39 p_grantee_key => p1_grantee_key,
40 p_start_date => sysdate,
41 p_end_date => null,
42 x_grant_guid => x1_grant_guid,
43 x_success => result,
44 x_errorcode => errorcode);
45
46 IF (result = 'F') THEN
47 APP_EXCEPTION.RAISE_EXCEPTION;
48 END IF;
49
50 --test_mesg1('grant_id: '||x1_grant_guid);
51 --test_mesg1('result : '||result);
52 --test_mesg1('errorcode: '||errorcode);
53
54 end qa_grants;
55
56
57 ---------------------------------------------------------------
58 Procedure qa_revoke(x1_grant_guid in out NOCOPY raw) IS
59 ---------------------------------------------------------------
60
61 result VARCHAR2(5);
62 errorcode NUMBER;
63 begin
64 --test_mesg1('Inside Revoke : '||x1_grant_guid);
65
66 fnd_grants_pkg.revoke_grant(
67 p_api_version => 1.0,
68 p_grant_guid => x1_grant_guid,
69 x_success => result,
70 x_errorcode => errorcode);
71
72 IF (result = 'F') THEN
73 APP_EXCEPTION.RAISE_EXCEPTION;
74 END IF;
75
76 x1_grant_guid := null;
77 --test_mesg1('After Revoke : '||x1_grant_guid);
78
79 end qa_revoke;
80
81
82 ------------------------------------------------------------------------
83 Procedure CALL_GRANTS(p_event in varchar2,
84 p_menu_name in varchar2,
85 p_grantee_id in number,
86 p_plan_id in number,
87 p_flag in varchar2,
88 x_guid in out NOCOPY raw) IS
89 ------------------------------------------------------------------------
90 v_grantee_type FND_GRANTS.GRANTEE_TYPE%TYPE;
91 v_grantee_key FND_GRANTS.GRANTEE_KEY%TYPE;
92
93 Begin
94
95 v_grantee_type := 'GROUP';
96 v_grantee_key := 'HZ_GROUP:'||to_char(p_grantee_id);
97
98 If ((p_flag = 'Y') AND (p_event = 'ON-INSERT')) then
99 qa_grants(v_grantee_type, v_grantee_key, p_plan_id, p_menu_name, x_guid);
100
101 Elsif ((p_flag = 'Y') AND (p_event = 'ON-UPDATE') AND (x_guid IS NULL)) then
102 qa_grants(v_grantee_type, v_grantee_key, p_plan_id, p_menu_name, x_guid);
103
104 Elsif ((p_flag <> 'Y') AND (p_event = 'ON-UPDATE') AND (x_guid IS NOT NULL)) then
105 qa_revoke(x_guid);
106
107 Elsif ((x_guid IS NOT NULL) AND (p_event = 'ON-DELETE')) then
108 qa_revoke(x_guid);
109
110 end if;
111
112 End Call_grants;
113
114 /* All the other procedures are public procedures and are mentioned in the
115 specification */
116
117 ----------------------------------------------------------------------------------
118 Procedure Create_Grant(EVENT in varchar2,
119 p_grantee_id in number, p_plan_id in number,
120 p_setup_flag in varchar2, x_setup_guid in out NOCOPY raw,
121 p_enter_flag in varchar2, x_enter_guid in out NOCOPY raw,
122 p_view_flag in varchar2, x_view_guid in out NOCOPY raw,
123 p_update_flag in varchar2, x_update_guid in out NOCOPY raw,
124 p_delete_flag in varchar2, x_delete_guid in out NOCOPY raw) IS
125 ----------------------------------------------------------------------------------
126
127 begin
128
129 CALL_GRANTS(EVENT, 'QA_PLANS_SETUP_USER', p_grantee_id, p_plan_id,
130 p_setup_flag, x_setup_guid);
131
132 CALL_GRANTS(EVENT, 'QA_RESULTS_ENTER_USER', p_grantee_id, p_plan_id,
133 p_enter_flag, x_enter_guid);
134
135 CALL_GRANTS(EVENT, 'QA_RESULTS_VIEW_USER', p_grantee_id, p_plan_id,
136 p_view_flag, x_view_guid);
137
138 CALL_GRANTS(EVENT, 'QA_RESULTS_UPDATE_USER', p_grantee_id, p_plan_id,
139 p_update_flag, x_update_guid);
140
141 CALL_GRANTS(EVENT, 'QA_RESULTS_DELETE_USER', p_grantee_id, p_plan_id,
142 p_delete_flag, x_delete_guid);
143
144 End Create_Grant;
145
146 -----------------------------------------------------------------------
147 PROCEDURE security_predicate(p1_function in varchar2,
148 p1_object_name in varchar2,
149 p1_user_name in varchar2,
150 x1_predicate out NOCOPY varchar2,
151 x1_return_status out NOCOPY varchar2) IS
152 -----------------------------------------------------------------------
153 begin
154 --test_mesg1('Inside security predicate');
155
156 -- Bug 4465241
157 -- ATG Mandatory Fix: Deprecated API
158 -- removing p_user_name
159 -- saugupta Mon, 27 Jun 2005 06:03:12 -0700 PDT
160 fnd_data_security.get_security_predicate(
161 p_api_version => 1.0,
162 p_function => p1_function,
163 p_object_name => p1_object_name,
164 x_predicate => x1_predicate,
165 x_return_status => x1_return_status);
166
167 -- Bug 2691739. get_security_predicate() returns a status 'F'
168 -- along with a predicate '1=2', if no grants exists. This should
169 -- not be treated as a error condition.
170 -- This change in the FND API was made in AFSCDSCB.pls version 115.53
171 -- abd corrected in 115.69. See the bug for more info. - kabalakr.
172
173 IF (x1_return_status IN ('E','U','L')) THEN
174 APP_EXCEPTION.RAISE_EXCEPTION;
175 END IF;
176
177 --test_mesg1('return_status: '|| v_return_status);
178 end;
179
180
181 -----------------------------------------------------------------------
182 Function child_security( p_function_name IN VARCHAR2,
183 p_user IN VARCHAR2,
184 x_child_plan_ids IN OUT NOCOPY VARCHAR2,
185 p_parent_plan_id IN NUMBER,
186 p_check_immediate IN BOOLEAN)
187 RETURN VARCHAR2 IS
188 -----------------------------------------------------------------------
189
190 -- Added this function for Bug 2329413.
191 -- This function can return three values.
192 -- 'F' - If security not enabled OR If User have Full access to all IMMEADIATE
193 -- child plans.
194 -- 'P' - If User have partial access to IMMEADIATE child plans.
195 -- 'N' - If user have No access to any of the IMMEADIATE child plans
196 -- kabalakr 30 APR 2002.
197
198 -- Bug 2379185. Changed the signature of the function
199 -- p_check_immediate is true only in eqr->eqr case.
200 -- For this make a check for immediate plans
201 -- If the user does not have access to any/some of
202 -- immediate plans then child eqr form should not be opened only in eqr->eqr scanario.
203
204 -- All the applicable child plan ids are passed in through x_child_plan_id
205 -- Example x_child_plan_id will have 101,102,103,104. Out of which if
206 -- child_plan_id 102 does not have security permission, then
207 -- x_child_plan_id will have 101,103,104 when the function returns.
208 -- rponnusa Thu May 16 19:25:20 PDT 2002
209
210 -- Cursor to find whether the child plan is of type IMMEADIATE or not.
211
212 CURSOR C_IMM(l_child_plan_id NUMBER) IS
213 SELECT child_plan_id,data_entry_mode
214 FROM qa_pc_plan_relationship
215 WHERE parent_plan_id = p_parent_plan_id
216 AND child_plan_id = l_child_plan_id;
217
218 l_child_id_array ChildPlanArray;
219 l_child_plan_id NUMBER;
220 l_partial NUMBER := 1;
221 l_full NUMBER := 0;
222 l_plan_id NUMBER;
223 l_result VARCHAR2(10);
224
225 -- Unable to call parse_list, hence ported the code for the same.
226
227 value VARCHAR2(2000);
228 c VARCHAR2(10);
229 separator CONSTANT VARCHAR2(1) := ',';
230 arr_index INTEGER := 1;
231 p INTEGER := 1;
232 n INTEGER;
233
234 -- Bug 2379185. Variables declared
235 sec_child_plan_ids VARCHAR2(10000) := null; -- this will hold the child plan_ids
236 -- which the user has access to
237 l_append BOOLEAN := FALSE;
238 l_data_entry_mode NUMBER;
239
240 BEGIN
241
242 -- Unable to call parse_list, hence ported the code for the same.
243
244 n := length(x_child_plan_ids);
245
246 WHILE p <= n LOOP
247 c := substr(x_child_plan_ids, p, 1);
248 p := p + 1;
249 IF (c = separator) THEN
250 l_child_id_array(arr_index) := value;
251 arr_index := arr_index + 1;
252 value := '';
253 ELSE
254 value := value || c;
255 END IF;
256
257 END LOOP;
258
259 l_child_id_array(arr_index) := value;
260
261 FOR i IN 1..l_child_id_array.COUNT LOOP
262 l_child_plan_id := l_child_id_array(i);
263
264 OPEN C_IMM(l_child_plan_id);
265 FETCH C_IMM INTO l_plan_id,l_data_entry_mode;
266
267 -- Find whether the user have access to the child plan.
268 -- Bug 4465241
269 -- ATG Mandatory Fix: Deprecated API
270 -- removing p_user_name
271 -- saugupta Mon, 27 Jun 2005 06:04:34 -0700 PDT
272 l_result := fnd_data_security.check_function(
273 p_api_version => 1.0,
274 p_function => p_function_name,
275 p_object_name => 'QA_PLANS',
276 p_instance_pk1_value => l_child_plan_id);
277 -- p_user_name => p_user);
278
279 IF (l_result = 'F') THEN
280 -- User does not have acess. Set l_full flag and l_partial flag.
281 IF(p_check_immediate AND l_data_entry_mode = 1) THEN
282
283 l_full := l_full + 1;
284 IF (l_partial = 1) THEN
285 l_partial := 2;
286 END IF;
287 END IF;
288 ELSE
289 -- Bug 2379185. We are here since child plan has access rights.
290 -- Formulate string which contains only security priviliaged child plans
291 IF(l_append) THEN
292 sec_child_plan_ids := sec_child_plan_ids || separator;
293 END IF;
294 l_append := TRUE;
295 sec_child_plan_ids := sec_child_plan_ids || l_child_plan_id;
296
297 END IF; -- l_result
298 CLOSE C_IMM;
299 END LOOP;
300
301 -- Bug 2379185. now we build child plans which are applicable for the
302 -- current record and user has enough priviliage.
303
304 x_child_plan_ids := sec_child_plan_ids;
305
306 IF p_check_immediate THEN
307 IF (l_full = l_child_id_array.COUNT) THEN
308 RETURN 'N' ;
309 ELSIF (l_partial = 2) THEN
310 RETURN 'P' ;
311 ELSIF ((l_full = 0) AND (l_partial = 1)) THEN
312 RETURN 'F' ;
313 END IF;
314 ELSE
315 -- Bug 2379185. Always return 'F' if this function is not called by eqr->eqr case.
316 return 'F';
317 END IF;
318
319
320 END child_security;
321
322
323 ---------------------------------------------------------------
324 PROCEDURE Delete_Relationship(p_relationship_id in number) IS
325 ---------------------------------------------------------------
326
327 begin
328 --Put No Op
329 NULL;
330
331 /*
332 HZ_RELATIONSHIPS_PKG.Delete_Row(
333 x_relationship_id => p_relationship_id);
334 */
335
336 end Delete_Relationship;
337
338 ---------------------------------------------------------------
339 PROCEDURE Update_Person(p_fname in varchar2,
340 p_lname in varchar2,
341 p_party_id in number,
342 p_date in date,
343 x1_msg_data out NOCOPY varchar2,
344 x1_return_status out NOCOPY varchar2) IS
345 ---------------------------------------------------------------
346 --
347 -- R12 ER 2648803. Rewritten to use TCA V2 APIs.
348 -- Parameter p_date is no longer used in V2.
349 --
350 -- bso Thu Jul 14 13:55:27 PDT 2005
351 --
352 v_object_version number;
353 v_person hz_party_v2pub.person_rec_type;
354 v_profile_id number;
355 v_msg_count number;
356
357 begin
358
359 v_person.person_last_name := p_lname;
360 v_person.person_first_name := p_fname;
361 v_person.party_rec.party_id := p_party_id;
362
363 SELECT object_version_number INTO v_object_version
364 FROM hz_parties
365 WHERE party_id = p_party_id;
366
367 hz_party_v2pub.update_person (
368 p_person_rec => v_person,
369 p_party_object_version_number => v_object_version,
370 x_profile_id => v_profile_id,
371 x_return_status => x1_return_status,
372 x_msg_count => v_msg_count,
373 x_msg_data => x1_msg_data);
374
375 end Update_Person;
376
377 ---------------------------------------------------------------
378 PROCEDURE Update_Group(p_group_name in varchar2,
379 p_party_id in number,
380 p_date in date,
381 x1_msg_data out NOCOPY varchar2,
382 x1_return_status out NOCOPY varchar2) IS
383 ---------------------------------------------------------------
384 --
385 -- R12 ER 2648803. Rewritten to use TCA V2 APIs.
386 -- Parameter p_date is no longer used in V2.
387 --
388 -- bso Thu Jul 14 13:55:27 PDT 2005
389 --
390 v_object_version number;
391 v_grp hz_party_v2pub.group_rec_type;
392 v_msg_count number;
393
394 begin
395
396 v_grp.group_name := p_group_name;
397 v_grp.group_type := c_app_name;
398 v_grp.party_rec.party_id := p_party_id;
399
400 SELECT object_version_number INTO v_object_version
401 FROM hz_parties
402 WHERE party_id = p_party_id;
403
404 hz_party_v2pub.update_group (
405 p_group_rec => v_grp,
406 p_party_object_version_number => v_object_version,
407 x_return_status => x1_return_status,
408 x_msg_count => v_msg_count,
409 x_msg_data => x1_msg_data);
410
411 end Update_Group;
412
413 -----------------------------------------------------------------------
414 PROCEDURE Create_Group(p_group_name in varchar2,
415 x1_msg_data out NOCOPY varchar2,
416 x1_return_status out NOCOPY varchar2,
417 x1_party_id out NOCOPY number) IS
418 -----------------------------------------------------------------------
419 --
420 -- R12 ER 2648803. Rewritten to use TCA V2 APIs.
421 -- bso Thu Jul 14 13:55:27 PDT 2005
422 --
423 v_grp hz_party_v2pub.group_rec_type;
424 v_msg_count NUMBER;
425 v_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
426
427 begin
428
429 v_grp.group_name := p_group_name;
430 v_grp.group_type := c_app_name;
431 v_grp.created_by_module := c_module_name;
432
433 hz_party_v2pub.create_group(
434 p_group_rec => v_grp,
435 x_return_status => x1_return_status,
436 x_msg_count => v_msg_count,
437 x_msg_data => x1_msg_data,
438 x_party_id => x1_party_id,
439 x_party_number => v_party_number);
440
441 end Create_Group;
442
443 ---------------------------------------------------------------
444 PROCEDURE Create_Person( p_fname in varchar2,
445 p_lname in varchar2,
446 x1_msg_data out NOCOPY varchar2,
447 x1_return_status out NOCOPY varchar2,
448 x1_party_id out NOCOPY number) IS
449 ---------------------------------------------------------------
450 --
451 -- R12 ER 2648803. Rewritten to use TCA V2 APIs.
452 -- bso Thu Jul 14 13:55:27 PDT 2005
453 --
454 v_person hz_party_v2pub.person_rec_type;
455 v_msg_count NUMBER;
456 v_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
457 v_profile_id NUMBER;
458 begin
459
460 v_person.created_by_module := c_module_name;
461 v_person.person_first_name := p_fname;
462 v_person.person_last_name := p_lname;
463
464 hz_party_v2pub.create_person(
465 p_person_rec => v_person,
466 x_return_status => x1_return_status,
467 x_msg_count => v_msg_count,
468 x_msg_data => x1_msg_data,
469 x_party_id => x1_party_id,
470 x_party_number => v_party_number,
471 x_profile_id => v_profile_id);
472
473 end Create_Person;
474
475 -----------------------------------------------------------------------
476 PROCEDURE Create_Relationship( p_subject_id in number,
477 p_object_id in number,
478 x1_msg_data out NOCOPY varchar2,
479 x1_return_status out NOCOPY varchar2,
480 x1_party_id out NOCOPY number,
481 x1_relationship_id out NOCOPY number) IS
482 -----------------------------------------------------------------------
483 -- R12 ER 2648803/4614568. Rewritten to use TCA V2 APIs.
484 -- bso Fri Sep 16 13:33:01 PDT 2005
485 v_rel hz_relationship_v2pub.relationship_rec_type;
486 v_msg_count NUMBER;
487 v_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
488 v_profile_id NUMBER;
489 begin
490
491 v_rel.subject_id := P_SUBJECT_ID;
492 v_rel.subject_type := 'PERSON';
493 v_rel.subject_table_name := 'HZ_PARTIES';
494 v_rel.object_id := P_OBJECT_ID;
495 v_rel.object_type := 'GROUP';
496 v_rel.object_table_name := 'HZ_PARTIES';
497 v_rel.relationship_code := 'MEMBER_OF';
498 v_rel.relationship_type := 'MEMBERSHIP';
499 v_rel.start_date := sysdate;
500 v_rel.created_by_module := c_module_name;
501
502 hz_relationship_v2pub.create_relationship(
503 p_relationship_rec => v_rel,
504 x_return_status => x1_return_status,
505 x_msg_count => v_msg_count,
506 x_msg_data => x1_msg_data,
507 x_relationship_id => x1_relationship_id,
508 x_party_id => x1_party_id,
509 x_party_number => v_party_number);
510
511 end Create_Relationship;
512
513 -----------------------------------------------------------------------
514 PROCEDURE Update_Relationship( p_relationship_id in number,
515 p_subject_id in number,
516 p_object_id in number,
517 p_party_id in number,
518 p_status in varchar2,
519 p_rel_date in date, -- Unused in V2
520 p_party_date in date, -- Unused in V2
521 x1_return_status out NOCOPY varchar2,
522 x1_msg_data out NOCOPY varchar2) IS
523 -----------------------------------------------------------------------
524 -- R12 ER 2648803/4614568. Rewritten to use TCA V2 APIs.
525 -- bso Fri Sep 16 13:37:14 PDT 2005
526 l_rel_rec hz_relationship_v2pub.relationship_rec_type;
527 return_status varchar2(10);
528 x1_msg_count number;
529 l_object_ver number;
530 l_dummy_party_object_ver number;
531
532 begin
533 l_rel_rec.RELATIONSHIP_ID := p_relationship_id;
534 l_rel_rec.SUBJECT_ID := p_subject_id;
535 l_rel_rec.OBJECT_ID := p_object_id;
536 l_rel_rec.party_rec.PARTY_ID := p_party_id;
537 l_rel_rec.STATUS := p_status;
538
539 -- R12 ER 2648803/4614568. Rewritten to use TCA V2 APIs.
540 -- which don't use these variables any more.
541 --
542 -- l_rel_last_update_date := p_rel_date;
543 -- l_party_last_update_date := p_party_date;
544
545 -- Bug 4646910 Obsoleted hz_party_relationships
546 -- Use qa_hz_party_relationships_v instead.
547 -- bso Tue Oct 4 16:39:51 PDT 2005
548 SELECT object_version_number
549 INTO l_object_ver
550 FROM qa_hz_party_relationships_v
551 WHERE party_relationship_id = p_relationship_id;
552
553 --
554 -- Bug 4057596
555 -- Selecting the object version number
556 -- from HZ_Parties table. It is used by
557 -- the TCA procedure to detect locking issues
558 -- ntungare Wed Aug 2 00:29:49 PDT 2006
559 --
560 SELECT object_version_number
561 INTO l_dummy_party_object_ver
562 FROM hz_parties
563 WHERE party_id = p_party_id;
564
565 hz_relationship_v2pub.update_relationship (
566 p_relationship_rec => l_rel_rec,
567 p_object_version_number => l_object_ver,
568 p_party_object_version_number => l_dummy_party_object_ver,
569 x_return_status => x1_return_status,
570 x_msg_count => x1_msg_count,
571 x_msg_data => x1_msg_data);
572
573 end Update_Relationship;
574
575
576 -- anagarwa Tue Aug 3 12:26:09 PDT 2004
577 -- bug 3695361: Slow performance when security is on
578 -- Following procedure takes in used id and returns user name
579 FUNCTION get_user_name (p_user_id in number)
580 RETURN VARCHAR2 IS
581
582 --
583 -- Bug 4330282. SSO change. All customer_id references
584 -- changed to person_party_id
585 --
586 l_user_name VARCHAR2(30);
587 l_person_party_id NUMBER;
588
589 CURSOR c (l_user_id NUMBER) IS
590 SELECT NVL(person_party_id, -1)
591 FROM fnd_user
592 WHERE user_id = p_user_id;
593
594 BEGIN
595
596 OPEN c(p_user_id);
597 FETCH c INTO l_person_party_id;
598 CLOSE c;
599
600 l_user_name := 'HZ_PARTY:'||l_person_party_id;
601
602 RETURN l_user_name;
603
604 END get_user_name;
605
606
607 -- anagarwa Tue Aug 3 12:26:09 PDT 2004
608 -- bug 3695361: Slow performance when security is on
609 -- Following procedure takes in used id instead of user name and
610 -- finds user name and then calls original security_predicate
611
612 PROCEDURE ssqr_security_predicate(p2_function in varchar2,
613 p2_object_name in varchar2,
614 p2_user_id in number,
615 x2_predicate out NOCOPY varchar2,
616 x2_return_status out NOCOPY varchar2) IS
617 BEGIN
618
619 security_predicate (p1_function => p2_function,
620 p1_object_name => p2_object_name,
621 p1_user_name => get_user_name(p2_user_id),
622 x1_predicate => x2_predicate,
623 x1_return_status => x2_return_status);
624
625 END;
626
627
628
629 END QA_SECURITY_PKG;