[Home] [Help]
PACKAGE BODY: APPS.HZ_DSS_UTIL_PUB
Source
1 PACKAGE BODY HZ_DSS_UTIL_PUB AS
2 /* $Header: ARHPDSUB.pls 120.13.12010000.4 2010/03/25 10:57:39 rgokavar ship $ */
3
4 --------------------------------------
5 -- declaration of private data types
6 --------------------------------------
7
8 TYPE t_number_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
9 TYPE t_varchar_30_tbl IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
10
11 --------------------------------------
12 -- declaration of private functions and procedures
13 --------------------------------------
14
15 PROCEDURE stamp_child_entities (
16 p_entity_id IN NUMBER,
17 p_object_pk1 IN VARCHAR2,
18 p_object_pk2 IN VARCHAR2,
19 p_object_pk3 IN VARCHAR2,
20 p_object_pk4 IN VARCHAR2,
21 p_object_pk5 IN VARCHAR2
22 );
23
24 FUNCTION check_created_by_module_cr (
25 p_dss_group_code IN VARCHAR2,
26 p_parent_party_id_tbl IN t_number_tbl,
27 p_parent_party_type_tbl IN t_varchar_30_tbl
28 ) RETURN VARCHAR2;
29
30 FUNCTION check_classifications (
31 p_dss_group_code IN VARCHAR2,
32 p_party_id IN NUMBER
33 ) RETURN VARCHAR2;
34
35 FUNCTION check_classification_cr (
36 p_dss_group_code IN VARCHAR2,
37 p_parent_party_id_tbl IN t_number_tbl,
38 p_parent_party_type_tbl IN t_varchar_30_tbl
39 ) RETURN VARCHAR2;
40
41 FUNCTION check_relationship_types (
42 p_dss_group_code IN VARCHAR2,
43 p_party_id IN NUMBER
44 -- ,p_relationship_id IN NUMBER -- Bug 5687869 (Nishant)
45 ) RETURN VARCHAR2;
46
47 FUNCTION check_relationship_type_cr (
48 p_dss_group_code IN VARCHAR2,
49 p_db_object_name IN VARCHAR2,
50 p_object_pk1 IN VARCHAR2 DEFAULT NULL,
51 p_object_pk2 IN VARCHAR2 DEFAULT NULL,
52 p_object_pk3 IN VARCHAR2 DEFAULT NULL,
53 p_object_pk4 IN VARCHAR2 DEFAULT NULL,
54 p_object_pk5 IN VARCHAR2 DEFAULT NULL,
55 p_parent_party_id_tbl IN t_number_tbl,
56 p_parent_party_type_tbl IN t_varchar_30_tbl
57 ) RETURN VARCHAR2;
58
59 FUNCTION is_relationship_party (
60 p_party_id IN NUMBER,
61 x_relationship_id OUT NOCOPY NUMBER
62 ) RETURN VARCHAR2;
63
64 PROCEDURE get_parent_party_id (
65 p_db_object_name IN VARCHAR2,
66 p_object_pk1 IN VARCHAR2 DEFAULT NULL,
67 p_object_pk2 IN VARCHAR2 DEFAULT NULL,
68 p_object_pk3 IN VARCHAR2 DEFAULT NULL,
69 p_object_pk4 IN VARCHAR2 DEFAULT NULL,
70 p_object_pk5 IN VARCHAR2 DEFAULT NULL,
71 x_party_id_tbl OUT NOCOPY t_number_tbl,
72 x_party_type_tbl OUT NOCOPY t_varchar_30_tbl
73 );
74
75 PROCEDURE print (
76 p_str IN VARCHAR2
77 );
78
79 --------------------------------------
80 -- public functions and procedures
81 --------------------------------------
82
83 /**
84 * FUNCTION
85 * test_instance
86 *
87 * DESCRIPTION
88 * Given a user, an operation , object name and primary key
89 * for the object, it returns Trues or False for the access
90 *
91 *
92 * SCOPE - PUBLIC
93 *
94 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
95 *
96 * ARGUMENTS : IN:
97 * p_operation_code VARCHAR2 e.g SELECT,INSERT etc.
98 * p_db_object_name VARCHAR2 Object_name in fnd_objects
99 * e.g.HZ_PARTIES
100 * p_instance_pk1_value VARCHAR2 e.g Party_id = 1000
101 * p_user VARCHAR2 e.g JDOE
102 *
103 * OUT: T/F
104 * IN/ OUT:
105 *
106 * RETURNS : NONE
107 *
108 * NOTES
109 *
110 * MODIFICATION HISTORY -
111 * Jyoti Pandey 08-07-2002 Created.
112 *
113 */
114
115 FUNCTION test_instance (
116 p_operation_code IN VARCHAR2,
117 p_db_object_name IN VARCHAR2,
118 p_instance_pk1_value IN VARCHAR2,
119 p_instance_pk2_value IN VARCHAR2 ,
120 p_instance_pk3_value IN VARCHAR2 ,
121 p_instance_pk4_value IN VARCHAR2 ,
122 p_instance_pk5_value IN VARCHAR2 ,
123 p_user_name IN VARCHAR2 ,
124 x_return_status OUT NOCOPY VARCHAR2,
125 x_msg_count OUT NOCOPY NUMBER,
126 x_msg_data OUT NOCOPY VARCHAR2,
127 p_init_msg_list IN VARCHAR2
128 ) RETURN VARCHAR2 IS
129
130 CURSOR c_check_user IS
131 SELECT '1'
132 FROM fnd_user
133 WHERE user_name = p_user_name
134 AND (start_date IS NULL OR start_date <= SYSDATE)
135 AND (end_date IS NULL OR end_date >= SYSDATE);
136
137 CURSOR c_check_table IS
138 SELECT '1'
139 FROM fnd_objects
140 WHERE database_object_name = p_db_object_name
141 AND ROWNUM = 1;
142
143 CURSOR c_check_operation_code IS
144 SELECT '1'
145 FROM ar_lookups lu
146 WHERE lu.lookup_type = 'HZ_DATA_OPERATIONS'
147 AND lu.lookup_code = p_operation_code;
148
149 CURSOR get_functions_for_op IS
150 SELECT dss.security_scheme_code , func.function_name
151 FROM hz_dss_scheme_functions dss,
152 fnd_form_functions func
153 WHERE dss.data_operation_code = p_operation_code
154 AND dss.status = 'A'
155 AND dss.function_id = func.function_id;
156
157 l_security_scheme_code VARCHAR2(30);
158 l_function_name VARCHAR2(30);
159 l_result VARCHAR2(1);
160 l_exists VARCHAR2(1);
161
162 BEGIN
163
164 ---initialize the message
165 --
166 -- Bug 3667238: initialize message stack based on the value
167 -- of the parameter
168 --
169 IF p_init_msg_list IS NOT NULL AND
170 FND_API.to_Boolean(p_init_msg_list)
171 THEN
172 FND_MSG_PUB.initialize;
173 END IF;
174
175 --- initialize API return status to success.
176 x_return_status := FND_API.G_RET_STS_SUCCESS;
177
178 --Check if security is on
179 IF NVL(FND_PROFILE.VALUE('HZ_DSS_ENABLED'), 'N') = 'Y' THEN
180
181 /* in R12, the fnd api won't accept user name.
182 it will raise a runtime error user_name contains
183 anything other than null or fnd_global.user_name
184
185 ---check if the passed user is valid
186 OPEN c_check_user;
187 FETCH c_check_user INTO l_exists;
188 IF c_check_user%NOTFOUND THEN
189 CLOSE c_check_user;
190 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_INVALID_USER');
191 FND_MESSAGE.SET_TOKEN('USER_NAME', p_user_name);
192 FND_MSG_PUB.ADD;
193 RAISE FND_API.G_EXC_ERROR;
194 END IF;
195 CLOSE c_check_user;
196 */
197
198 ---Check if the table name is valid
199 OPEN c_check_table;
200 FETCH c_check_table INTO l_exists;
201 IF c_check_table%NOTFOUND THEN
202 CLOSE c_check_table;
203 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_INVALID_OBJECT');
204 FND_MESSAGE.SET_TOKEN('OBJECT_NAME',p_db_object_name);
205 FND_MSG_PUB.ADD;
206 RAISE FND_API.G_EXC_ERROR;
207 END IF;
208 CLOSE c_check_table;
209
210 ---- Check if the passed operation code is valid ----
211 OPEN c_check_operation_code;
212 FETCH c_check_operation_code INTO l_exists;
213 IF c_check_operation_code%NOTFOUND THEN
214 CLOSE c_check_operation_code;
215 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_INVALID_OPER');
216 FND_MESSAGE.SET_TOKEN('OPER_NAME',p_operation_code);
217 FND_MSG_PUB.ADD;
218 RAISE FND_API.G_EXC_ERROR;
219 END IF;
220 CLOSE c_check_operation_code;
221
222 ---- Get all the security functions for the operation passed ----
223 OPEN get_functions_for_op;
224 LOOP
225 FETCH get_functions_for_op INTO
226 l_security_scheme_code, l_function_name;
227 EXIT WHEN get_functions_for_op%NOTFOUND;
228
229 ---- Call the AOL check function ----
230 l_result := fnd_data_security.check_function (
231 1,
232 l_function_name,
233 p_db_object_name,
234 p_instance_pk1_value,
235 p_instance_pk2_value,
236 p_instance_pk3_value,
237 p_instance_pk4_value,
238 p_instance_pk5_value,
239 fnd_global.user_name -- p_user_name
240 );
241
242 IF l_result = 'F' THEN
243 EXIT;
244 ELSIF l_result = 'U' THEN
245 FND_MESSAGE.SET_NAME('AR', 'HZ_API_CALLING_ERROR');
246 FND_MESSAGE.SET_TOKEN('PROC_NAME', 'FND_DATA_SECURITY.check_function');
247 FND_MSG_PUB.ADD;
248 RAISE FND_API.G_EXC_ERROR;
249 END IF;
250 END LOOP;
251 CLOSE get_functions_for_op;
252
253 RETURN l_result;
254
255 ELSE
256 --no security so return true
257 RETURN FND_API.G_TRUE;
258 END IF;
259
260 EXCEPTION
261 WHEN FND_API.G_EXC_ERROR THEN
262 x_return_status := FND_API.G_RET_STS_ERROR;
263
264 FND_MSG_PUB.COUNT_AND_GET(
265 p_encoded => fnd_api.g_false,
266 p_count => x_msg_count,
267 p_data => x_msg_data
268 );
269
270 RETURN FND_API.G_FALSE;
271
272 WHEN OTHERS THEN
273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
274
275 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
276 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
277 FND_MSG_PUB.ADD;
278
279 FND_MSG_PUB.COUNT_AND_GET(
280 p_encoded => fnd_api.g_false,
281 p_count => x_msg_count,
282 p_data => x_msg_data
283 );
284
285 RETURN FND_API.G_FALSE;
286
287 END test_instance;
288
289
290 /**
291 * PROCEDURE
292 * get_granted_groups
293 *
294 * DESCRIPTION
295 * For a given user ,operation code, gets all the data sharing groups
296 *
297 * SCOPE - PUBLIC
298 *
299 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
300 *
301 * ARGUMENTS : IN:
302 * p_user_name VARCHAR2 e.g. JDOE
303 * p_operation_code VARCHAR2 e.g. SELECT
304 *
305 * OUT: x_granted_groups table of data dharing group , entity_id
306 * IN/ OUT:
307 *
308 * RETURNS : NONE
309 *
310 * NOTES
311 *
312 * MODIFICATION HISTORY -
313 * Jyoti Pandey 08-07-2002 Created.
314 *
315 */
316
317 PROCEDURE get_granted_groups (
318 p_user_name IN VARCHAR2,
319 p_operation_code IN VARCHAR2,
320 x_granted_groups OUT NOCOPY dss_group_tbl_type,
321 x_return_status OUT NOCOPY VARCHAR2,
322 x_msg_count OUT NOCOPY NUMBER,
323 x_msg_data OUT NOCOPY VARCHAR2
324 ) IS
325
326
327 l_menu_id NUMBER;
328 l_function_id NUMBER;
329 l_exists VARCHAR2(1);
330 l_security_scheme_code hz_dss_secured_entities.dss_group_code%type;
331 l_object_instance_set_id hz_dss_secured_entities.dss_instance_set_id%type;
332
333 i BINARY_INTEGER;
334
335 cursor c_fn_menu_op(t_operation_code IN VARCHAR2) IS
336 SELECT distinct menu_id ,dsf.security_scheme_code ,dsf.function_id
337 FROM hz_dss_scheme_functions dsf, fnd_compiled_menu_functions cmf
338 WHERE dsf.data_operation_code = t_operation_code
339 AND dsf.function_id = cmf.function_id
340 AND dsf.status = 'A';
341
342
343 cursor c_inst_set_from_menu(t_menu_id IN NUMBER,
344 t_function_id IN NUMBER, t_user_name IN VARCHAR2) IS
345 SELECT instance_set_id
346 FROM fnd_grants grants
347 WHERE grants.menu_id= t_menu_id --grant for a menu
348 AND grants.start_date <= sysdate
349 AND ( grants.end_date IS NULL
350 OR grants.end_date >= sysdate )
351 AND ( ( grants.grantee_type = 'USER' --grantee a user
352 AND grants.grantee_key = t_user_name)
353 OR ( grants.grantee_type = 'GROUP' --grantee a group
354 AND grants.grantee_key in
355 (select role_name
356 from wf_user_roles
357 where user_name = t_user_name))
358 OR (grants.grantee_type = 'GLOBAL'));
359
360
361 cursor c_get_dss_groups(t_object_instance_set_id IN NUMBER) IS
362 select dss_group_code, entity_id
363 from hz_dss_secured_entities
364 where dss_instance_set_id = t_object_instance_set_id
365 and status = 'A';
366
367
368 BEGIN
369
370 ---initialize the message
371 FND_MSG_PUB.initialize;
372
373 --- initialize API return status to success.
374 x_return_status := FND_API.G_RET_STS_SUCCESS;
375
376 ---Validate the input
377 ---check if the passed user is valid
378 ---Check if the passed operation code is valid
379 begin
380
381 select '1'
382 into l_exists
383 from fnd_user
384 where user_name = p_user_name
385 and ( start_date IS NULL OR start_date <= SYSDATE)
386 and ( end_date is null or end_date >= sysdate );
387
388 exception when no_data_found then
389 FND_MESSAGE.SET_NAME('AR','HZ_DSS_INVALID_USER');
390 FND_MSG_PUB.ADD;
391 RAISE FND_API.G_EXC_ERROR;
392 end;
393
394 begin
395
396 select '1'
397 into l_exists
398 from ar_lookups lu
399 where lu.lookup_type = 'HZ_DATA_OPERATIONS'
400 and lu.lookup_code = p_operation_code;
401
402 exception when no_data_found then
403 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_INVALID_OPER');
404 FND_MESSAGE.SET_TOKEN('OPER_NAME',p_operation_code);
405 FND_MSG_PUB.ADD;
406 RAISE FND_API.G_EXC_ERROR;
407 end;
408
409 i := 1;
410
411 ---Get all the functions and menus for that operation
412 open c_fn_menu_op(p_operation_code);
413 loop
414 fetch c_fn_menu_op into
415 l_menu_id ,l_security_scheme_code, l_function_id;
416
417 exit when c_fn_menu_op%NOTFOUND;
418
419 ---get instance sets from the user and and menu
420 ---from the grants , form functions table
421 open c_inst_set_from_menu(l_menu_id, l_function_id ,p_user_name);
422 loop
423 fetch c_inst_set_from_menu into l_object_instance_set_id;
424 exit when c_inst_set_from_menu%NOTFOUND;
425
426 open c_get_dss_groups(l_object_instance_set_id);
427 loop
428 fetch c_get_dss_groups into x_granted_groups(i).dss_group_code,
429 x_granted_groups(i).entity_id;
430
431 exit when c_get_dss_groups%NOTFOUND;
432
433 i := i + 1;
434 end loop;
435 close c_get_dss_groups;
436
437 end loop;
438 close c_inst_set_from_menu;
439
440
441 end loop;
442 close c_fn_menu_op;
443
444 EXCEPTION
445 WHEN fnd_api.g_exc_error THEN
446 x_return_status := FND_API.G_RET_STS_ERROR;
447 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
448 p_count => x_msg_count,
449 p_data => x_msg_data);
450
451
452 END get_granted_groups;
453
454 /**
455 * FUNCTION
456 * determine_dss_group
457 *
458 * DESCRIPTION
459 * For a given object for a particular row, determine the data sharing
460 * group
461 *
462 * SCOPE - PUBLIC
463 *
464 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
465 *
466 * ARGUMENTS : IN:
467 * p_db_object_name VARCHAR2 e.g HZ_PARTIES
468 * p_object_pk1 VARCHAR2 e.g Party_id 1000
469 *
470 * OUT: Data Sharing group VARCHAR2
471 * IN/ OUT:
472 *
473 * RETURNS : NONE
474 *
475 * NOTES
476 *
477 * MODIFICATION HISTORY -
478 * Jyoti Pandey 08-07-2002 Created.
479 */
480
481 FUNCTION determine_dss_group (
482 p_db_object_name IN VARCHAR2,
483 p_object_pk1 IN VARCHAR2,
484 p_object_pk2 IN VARCHAR2,
485 p_object_pk3 IN VARCHAR2,
486 p_object_pk4 IN VARCHAR2,
487 p_object_pk5 IN VARCHAR2,
488 p_root_db_object_name IN VARCHAR2,
489 p_root_object_pk1 IN VARCHAR2,
490 p_root_object_pk2 IN VARCHAR2,
491 p_root_object_pk3 IN VARCHAR2,
492 p_root_object_pk4 IN VARCHAR2,
493 p_root_object_pk5 IN VARCHAR2
494 ) RETURN VARCHAR2 IS
495
496 CURSOR c_check_criteria (
497 p_dss_group_code VARCHAR2
498 ) IS
499 SELECT 1
500 FROM hz_dss_criteria
501 WHERE dss_group_code = p_dss_group_code
502 AND status = 'A'
503 AND ROWNUM = 1;
504
505 CURSOR c_check_created_by_module (
506 p_dss_group_code VARCHAR2
507 ) IS
508 SELECT 1
509 FROM hz_dss_criteria
510 WHERE dss_group_code = p_dss_group_code
511 AND owner_table_name = 'AR_LOOKUPS'
512 AND owner_table_id1 = 'HZ_CREATED_BY_MODULES'
513 AND status = 'A'
514 AND ROWNUM = 1;
515
516 CURSOR c_check_classification (
517 p_dss_group_code VARCHAR2
518 ) IS
519 SELECT 1
520 FROM hz_dss_criteria
521 WHERE dss_group_code = p_dss_group_code
522 AND owner_table_name = 'FND_LOOKUP_VALUES'
523 AND status = 'A'
524 AND ROWNUM = 1;
525
526 CURSOR c_check_relationship_type (
527 p_dss_group_code VARCHAR2
528 ) IS
529 SELECT 1
530 FROM hz_dss_criteria
531 WHERE dss_group_code = p_dss_group_code
532 AND owner_table_name = 'HZ_RELATIONSHIP_TYPES'
533 AND status = 'A'
534 AND ROWNUM = 1;
535
536 CURSOR c_get_dss_groups (
537 p_db_object_name VARCHAR2
538 ) IS
539 SELECT obj.object_id, obj.obj_name, null instance_set_id, null instance_set_name,
540 obj.pk1_column_name, obj.pk2_column_name, null predicate,
541 dsg.dss_group_code, dsg.rank
542 FROM hz_dss_entities dse,
543 hz_dss_secured_entities se,
544 hz_dss_groups_b dsg,
545 fnd_objects obj
546 WHERE obj.database_object_name = p_db_object_name
547 AND dse.object_id IS NOT NULL
548 AND dse.object_id = obj.object_id
549 AND dse.entity_id = se.entity_id
550 AND se.dss_group_code = dsg.dss_group_code
551 AND se.status = 'A'
552 AND dsg.status = 'A'
553 AND dse.status = 'A'
554 UNION ALL
555 SELECT obj.object_id, obj.obj_name, ins.instance_set_id, ins.instance_set_name,
556 obj.pk1_column_name, obj.pk2_column_name, ins.predicate,
557 dsg.dss_group_code, dsg.rank
558 FROM hz_dss_entities dse,
559 hz_dss_secured_entities se,
560 hz_dss_groups_b dsg,
561 fnd_objects obj,
562 fnd_object_instance_sets ins
563 WHERE obj.database_object_name = p_db_object_name
564 AND dse.object_id IS NULL
565 AND dse.instance_set_id = ins.instance_set_id
566 AND ins.object_id = obj.object_id
567 AND dse.entity_id = se.entity_id
568 AND se.dss_group_code = dsg.dss_group_code
569 AND se.status = 'A'
570 AND dsg.status = 'A'
571 AND dse.status = 'A'
572 ORDER BY rank;
573
574 l_dummy NUMBER(1);
575 l_db_object_name VARCHAR2(30);
576 l_object_pk1 VARCHAR2(30);
577 l_object_pk2 VARCHAR2(30);
578 l_object_pk3 VARCHAR2(30);
579 l_object_pk4 VARCHAR2(30);
580 l_object_pk5 VARCHAR2(30);
581 l_relationship_id NUMBER;
582 l_parent_party_id_tbl t_number_tbl;
583 l_parent_party_type_tbl t_varchar_30_tbl;
584 l_falling_into_the_group VARCHAR2(2);
585 l_failure_reason VARCHAR2(100);
586 l_object_id NUMBER;
587 l_object_name VARCHAR2(30);
588 l_instance_set_id NUMBER;
589 l_instance_set_name VARCHAR2(30);
590 l_pk1_column_name VARCHAR2(30);
591 l_pk2_column_name VARCHAR2(30);
592 l_predicate VARCHAR2(1000);
593 l_dss_group_code VARCHAR2(30);
594 l_rank NUMBER;
595 l_sql VARCHAR2(1000);
596 l_pre_db_object_name VARCHAR2(30);
597 l_pre_dss_group_code VARCHAR2(30);
598 l_module_based_dsg VARCHAR2(1);
599 l_class_based_dsg VARCHAR2(1);
600 l_rel_based_dsg VARCHAR2(1);
601 l_returned_dss_group VARCHAR2(30);
602
603 BEGIN
604 print ('BEGIN determine_dss_group for :'||p_object_pk1);
605
606 --
607 -- set local variables
608 --
609 l_db_object_name := p_db_object_name;
610 l_object_pk1 := p_object_pk1;
611 l_object_pk2 := p_object_pk2;
612 l_object_pk3 := p_object_pk3;
613 l_object_pk4 := p_object_pk4;
614 l_object_pk5 := p_object_pk5;
615
616 --
617 -- checking relationship party will be redirected to
618 -- check relationship.
619 --
620 IF p_db_object_name = 'HZ_PARTIES' THEN
621 IF is_relationship_party(TO_NUMBER(p_object_pk1), l_relationship_id) = 'Y' THEN
622 l_db_object_name := 'HZ_RELATIONSHIPS';
623 l_object_pk1 := l_relationship_id;
624 l_object_pk2 := 'F';
625 END IF;
626 END IF;
627
628
629 print (
630 'l_db_object_name = '||l_db_object_name||' '||
631 'l_object_pk1 = '||l_object_pk1||' '||
632 'l_object_pk2 = '||l_object_pk2
633 );
634
635 --
636 -- find all of groups that are applicable to this entity
637 --
638 OPEN c_get_dss_groups(l_db_object_name);
639 LOOP
640
641 << next_fetch>>
642
643 l_falling_into_the_group := 'NA';
644 l_failure_reason := 'INITIAL';
645
646 FETCH c_get_dss_groups INTO
647 l_object_id, l_object_name,
648 l_instance_set_id, l_instance_set_name,
649 l_pk1_column_name, l_pk2_column_name, l_predicate,
650 l_dss_group_code, l_rank;
651 EXIT WHEN c_get_dss_groups%NOTFOUND;
652
653 --
654 -- debug messages
655 --
656
657 print (
658 'object_id = '||l_object_id||' '||
659 'obj_name = '||l_object_name
660 );
661 print (
662 'instance_set_id = '||l_instance_set_id||' '||
663 'instance_set_name = '||l_instance_set_name
664 );
665 print (
666 'pk1_column_name = '||l_pk1_column_name||' '||
667 'pk2_column_name = '||l_pk2_column_name
668 );
669 print (
670 'predicate = '||l_predicate||' '||
671 'dss_group_code = '||l_dss_group_code||' '||
672 'rank = '||l_rank
673 );
674
675
676 --
677 -- check if the record can fall into the group if
678 -- the group secure instance entities
679 --
680
681 IF l_predicate IS NOT NULL THEN
682 BEGIN
683 l_sql := 'SELECT 1 FROM '||l_db_object_name||' '||
684 'WHERE '||l_pk1_column_name||' = :1'||' '||
685 'AND '||l_predicate;
686
687 IF l_pk2_column_name IS NULL THEN
688 --
689 -- debug messages
690 --
691 print(l_sql);
692
693 EXECUTE IMMEDIATE l_sql into l_dummy USING l_object_pk1;
694 ELSE
695 l_sql := l_sql||' '||
696 'AND '||l_pk2_column_name||' = :2';
697
698 --
699 -- debug messages
700 --
701 print(l_sql);
702
703 EXECUTE IMMEDIATE l_sql into l_dummy USING l_object_pk1, l_object_pk2;
704 END IF;
705
706 EXCEPTION
707 WHEN OTHERS THEN
708 print('EXCEPTION :'||SQLERRM);
709 l_failure_reason := 'INSTANCE_CHECK';
710 GOTO next_fetch;
711 END;
712 ELSE
713 print('Predicate NULL');
714 NULL;
715 END IF;
716
717 --
718 -- check cache
719 --
720 IF l_pre_db_object_name = l_db_object_name AND
721 l_failure_reason <> 'INSTANCE_CHECK' AND
722 l_pre_dss_group_code = l_dss_group_code
723 THEN
724 print ('l_pre_db_object_name='||l_db_object_name||':l_failure_reason='||l_failure_reason
725 ||'l_pre_dss_group_code='||l_pre_dss_group_code||':GOTO next_fetch');
726 GOTO next_fetch;
727 END IF;
728
729 --
730 -- check if there is any criteria defined in the dss group
731 -- no criteria means the group securing all of records.
732 -- return the group code directly.
733 --
734 OPEN c_check_criteria(l_dss_group_code);
735 FETCH c_check_criteria INTO l_dummy;
736 IF c_check_criteria%NOTFOUND THEN
737 CLOSE c_check_criteria;
738 print('c_check_criteria%NOTFOUND, EXIT Loop, return l_returned_dss_group='||l_dss_group_code);
739 l_returned_dss_group := l_dss_group_code;
740 EXIT;
741 ELSE
742 print('c_check_criteria%FOUND, continue');
743 NULL;
744 END IF;
745 CLOSE c_check_criteria;
746
747 --
748 -- get parent party id and type
749 --
750 get_parent_party_id(
751 p_db_object_name => l_db_object_name,
752 p_object_pk1 => l_object_pk1,
753 p_object_pk2 => l_object_pk2,
754 p_object_pk3 => l_object_pk3,
755 p_object_pk4 => l_object_pk4,
756 p_object_pk5 => l_object_pk5,
757 x_party_id_tbl => l_parent_party_id_tbl,
758 x_party_type_tbl => l_parent_party_type_tbl
759 );
760
761 --
762 -- debug messages
763 --
764 print('Number of parent parties: '||l_parent_party_id_tbl.COUNT);
765
766 FOR i IN 1..l_parent_party_id_tbl.COUNT LOOP
767 print('party_id = '||l_parent_party_id_tbl(i)||' '||
768 'party_type = '||l_parent_party_type_tbl(i));
769 END LOOP;
770
771
772 --
773 -- check if it is created by module based
774 --
775 OPEN c_check_created_by_module(l_dss_group_code);
776 FETCH c_check_created_by_module INTO l_dummy;
777 IF c_check_created_by_module%NOTFOUND THEN
778 l_module_based_dsg := 'N';
779 ELSE
780 l_module_based_dsg := 'Y';
781 END IF;
782 CLOSE c_check_created_by_module;
783
784 --
785 -- debug messages
786 --
787 print('module_based_dsg = '||l_module_based_dsg);
788
789 IF l_module_based_dsg = 'Y' THEN
790 l_falling_into_the_group :=
791 check_created_by_module_cr (
792 p_dss_group_code => l_dss_group_code,
793 p_parent_party_id_tbl => l_parent_party_id_tbl,
794 p_parent_party_type_tbl => l_parent_party_type_tbl
795 );
796
797 IF l_falling_into_the_group = 'N' THEN
798 print('l_falling_into_the_group = N, l_failure_reason=CREATED_BY_MODULE, GOTO next_fetch');
799 l_failure_reason := 'CREATED_BY_MODULE';
800 GOTO next_fetch;
801 ELSE
802 print('l_falling_into_the_group = Y, continue');
803 NULL;
804 END IF;
805 END IF;
806
807 --
808 -- check if it is classification based
809 --
810 OPEN c_check_classification(l_dss_group_code);
811 FETCH c_check_classification INTO l_dummy;
812 IF c_check_classification%NOTFOUND THEN
813 l_class_based_dsg := 'N';
814 ELSE
815 l_class_based_dsg := 'Y';
816 END IF;
817 CLOSE c_check_classification;
818
819 --
820 -- debug messages
821 --
822 print('class_based_dsg = '||l_class_based_dsg);
823
824 IF l_class_based_dsg = 'Y' THEN
825 l_falling_into_the_group :=
826 check_classification_cr (
827 p_dss_group_code => l_dss_group_code,
828 p_parent_party_id_tbl => l_parent_party_id_tbl,
829 p_parent_party_type_tbl => l_parent_party_type_tbl
830 );
831
832 IF l_falling_into_the_group = 'N' THEN
833 print('l_falling_into_the_group = N, l_failure_reason=CLASSIFICATION, GOTO next_fetch');
834 l_failure_reason := 'CLASSIFICATION';
835 GOTO next_fetch;
836 ELSE
837 print('l_falling_into_the_group = Y, continue');
838 NULL;
839 END IF;
840 END IF;
841
842 --
843 -- check if it is relationship type based
844 --
845 OPEN c_check_relationship_type(l_dss_group_code);
846 FETCH c_check_relationship_type INTO l_dummy;
847 IF c_check_relationship_type%NOTFOUND THEN
848 l_rel_based_dsg := 'N';
849 ELSE
850 l_rel_based_dsg := 'Y';
851 END IF;
852 CLOSE c_check_relationship_type;
853
854 --
855 -- debug messages
856 --
857 print('relationship_based_dsg = '||l_rel_based_dsg);
858
859 IF l_rel_based_dsg = 'Y' THEN
860 l_falling_into_the_group :=
861 check_relationship_type_cr (
862 p_dss_group_code => l_dss_group_code,
863 p_db_object_name => l_db_object_name,
864 p_object_pk1 => l_object_pk1,
865 p_object_pk2 => l_object_pk2,
866 p_object_pk3 => l_object_pk3,
867 p_object_pk4 => l_object_pk4,
868 p_object_pk5 => l_object_pk5,
869 p_parent_party_id_tbl => l_parent_party_id_tbl,
870 p_parent_party_type_tbl => l_parent_party_type_tbl
871 );
872
873 IF l_falling_into_the_group = 'N' THEN
874 l_failure_reason := 'RELATIONSHIP_TYPE';
875 print('l_falling_into_the_group = N, l_failure_reason=RELATIONSHIP_TYPE, GOTO next_fetch');
876 GOTO next_fetch;
877 ELSE
878 print('l_falling_into_the_group = Y, continue');
879 NULL;
880 END IF;
881 END IF;
882
883 IF l_falling_into_the_group = 'Y' THEN
884 l_returned_dss_group := l_dss_group_code;
885 print('l_falling_into_the_group = Y, Exit Loop l_returned_dss_group='||l_dss_group_code);
886 EXIT;
887 END IF;
888
889 l_pre_db_object_name := l_db_object_name;
890 l_pre_dss_group_code := l_dss_group_code;
891
892 print('Finally: l_pre_db_object_name='||l_pre_db_object_name);
893 print('Finally: l_pre_dss_group_code='||l_pre_dss_group_code);
894 END LOOP;
895 CLOSE c_get_dss_groups;
896
897 IF l_returned_dss_group IS NULL THEN
898 print('l_returned_dss_group is NULL, so get profile value for HZ_DEFAULT_DSS_GROUP');
899 l_returned_dss_group := FND_PROFILE.VALUE('HZ_DEFAULT_DSS_GROUP');
900 END IF;
901
902 print('Finally Return Value l_returned_dss_group:'||l_returned_dss_group);
903 RETURN l_returned_dss_group;
904
905 END determine_dss_group;
906
907
908 /*===========================================================================+
909 | PROCEDURE
910 | assign_dss_group
911 |
912 | DESCRIPTION
913 | For a given row in a table ,assign the data sharing group
914 | Based on p_process_subentities_flag, it could be assigned to all
915 | the subentities as well
916 |
917 | SCOPE - PUBLIC
918 |
919 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
920 |
921 | ARGUMENTS : IN:
922 | p_db_object_name VARCHAR2 e.g. HZ_PARTIES
923 | p_object_pk1 VARCHAR2 e.g. 1000
924 | p_object_pk2 VARCHAR2 (if any)
925 | p_object_pk3 VARCHAR2 (if any)
926 | p_object_pk4 VARCHAR2 (if any)
927 | p_object_pk5 VARCHAR2 (if any)
928 | p_root_db_object_name VARCHAR2 name of the root entity(optional)
929 | p_root_object_pk1 VARCHAR2 Primary key value of root(optional)
930 | p_root_object_pk2 VARCHAR2 Primary key value of root(optional)
931 | p_root_object_pk3 VARCHAR2 Primary key value of root(optional)
932 | p_root_object_pk4 VARCHAR2 Primary key value of root(optional)
933 | p_root_object_pk5 VARCHAR2 Primary key value of root(optional)
934 | p_process_subentities_flag VARCHAR2 Y/N If all child entities need
935 | to be processed
936 |
937 | OUT:
938 | IN/ OUT:
939 |
940 | RETURNS : NONE
941 |
942 | NOTES
943 |
944 | MODIFICATION HISTORY -
945 | Jyoti Pandey 08-07-2002 Created.
946 |
947 +===========================================================================*/
948
949 PROCEDURE assign_dss_group(
950 p_db_object_name IN VARCHAR2,
951 p_object_pk1 IN VARCHAR2,
952 p_object_pk2 IN VARCHAR2,
953 p_object_pk3 IN VARCHAR2,
954 p_object_pk4 IN VARCHAR2,
955 p_object_pk5 IN VARCHAR2,
956 p_root_db_object_name IN VARCHAR2,
957 p_root_object_pk1 IN VARCHAR2,
958 p_root_object_pk2 IN VARCHAR2,
959 p_root_object_pk3 IN VARCHAR2,
960 p_root_object_pk4 IN VARCHAR2,
961 p_root_object_pk5 IN VARCHAR2,
962 p_process_subentities_flag IN VARCHAR2) IS
963
964 l_dss_assignment_rec HZ_DSS_GROUPS_PUB.dss_assignment_type;
965
966 ----** get pk name, fk name ,assignment method for the object name passed**----
967 CURSOR get_grp_assign_level(t_object_name IN VARCHAR2) IS
968 SELECT dse.entity_id, fo.object_id,
969 fo.pk1_column_name, fo.pk2_column_name ,
970 dse.parent_fk_column1 , dse.parent_fk_column2,
971 dse.group_assignment_level
972 FROM fnd_objects fo , hz_dss_entities dse
973 WHERE ( ( dse.object_id IN ( select object_id from fnd_objects
974 where database_object_name = t_object_name) )
975 OR
976 (dse.instance_set_id in ( select instance_set_id
977 from fnd_object_instance_sets ois
978 where ois.object_id IN
979 (select object_id from fnd_objects
980 where database_object_name = t_object_name)
981 )
982 )
983 )
984 AND fo.object_id = dse.object_id
985 AND dse.status = 'A';
986
987 l_entity_id hz_dss_entities.entity_id%type;
988 l_object_id hz_dss_entities.object_id%type;
989 l_pk1_column_name fnd_objects.pk1_column_name%type;
990 l_pk2_column_name fnd_objects.pk2_column_name%type;
991 l_parent_fk_column1 hz_dss_entities.parent_fk_column1%type;
992 l_parent_fk_column2 hz_dss_entities.parent_fk_column2%type;
993 l_group_assignment_level hz_dss_entities.group_assignment_level%type;
994 l_dsg_code hz_dss_secured_entities.dss_group_code%type;
995 l_sql varchar2(2000);
996
997
998 x_assignment_id NUMBER;
999 x_return_status varchar2(1);
1000 x_msg_count number;
1001 x_msg_data varchar2(2000);
1002
1003 BEGIN
1004
1005 --determine if the DSG should INHERIT DIRECT ASSIGN
1006 OPEN get_grp_assign_level(p_db_object_name);
1007 LOOP
1008
1009 FETCH get_grp_assign_level INTO l_entity_id, l_object_id,
1010 l_pk1_column_name, l_pk2_column_name ,
1011 l_parent_fk_column1 , l_parent_fk_column2, l_group_assignment_level;
1012
1013 EXIT WHEN get_grp_assign_level%NOTFOUND;
1014
1015 if l_group_assignment_level = 'INHERIT' then
1016 null; ---don't do anything
1017 exit;
1018 close get_grp_assign_level;
1019 else
1020 ---Determine the DSG
1021 l_dsg_code := hz_dss_util_pub.determine_dss_group(
1022 p_db_object_name,
1023 p_object_pk1,
1024 p_object_pk2,
1025 p_object_pk3,
1026 p_object_pk4,
1027 p_object_pk5,
1028 p_root_db_object_name,
1029 p_root_object_pk1,
1030 p_root_object_pk2,
1031 p_root_object_pk3,
1032 p_root_object_pk4,
1033 p_root_object_pk5 );
1034
1035
1036 end if; ---- l_group_assignment_level = 'INHERIT'
1037
1038 if l_dsg_code is not null then
1039
1040 ---make a callout to HZ_DSS_GROUPS_PUB.create_assignment
1041 l_dss_assignment_rec.dss_group_code := l_dsg_code;
1042 l_dss_assignment_rec.assignment_id := null;
1043 l_dss_assignment_rec.owner_table_name := p_db_object_name;
1044 l_dss_assignment_rec.owner_table_id1 := p_object_pk1;
1045 l_dss_assignment_rec.owner_table_id2 := p_object_pk2;
1046 l_dss_assignment_rec.owner_table_id3 := p_object_pk3;
1047 l_dss_assignment_rec.owner_table_id4 := p_object_pk4;
1048 l_dss_assignment_rec.owner_table_id5 := p_object_pk5;
1049 l_dss_assignment_rec.status := null;
1050
1051 if l_group_assignment_level = 'ASSIGN' then
1052 HZ_DSS_GROUPS_PUB.create_assignment (
1053 'T',
1054 l_dss_assignment_rec,
1055 x_assignment_id ,
1056 x_return_status ,
1057 x_msg_count,
1058 x_msg_data);
1059
1060 elsif l_group_assignment_level = 'DIRECT' then
1061
1062 begin
1063 l_sql := ' UPDATE ' || p_db_object_name ||
1064 ' SET ' || ' dss_group_code ' || ' = :dsg ' ||
1065 ' WHERE ' || l_pk1_column_name|| ' = :pk ' ;
1066 EXECUTE IMMEDIATE l_sql USING l_dsg_code ,p_object_pk1;
1067 exception
1068 when others then
1069 raise;
1070 end ;
1071
1072 end if; ---group assignment level
1073
1074 if p_process_subentities_flag = 'Y' then
1075
1076 stamp_child_entities(
1077 p_entity_id =>l_entity_id,
1078 p_object_pk1 =>p_object_pk1,
1079 p_object_pk2 =>p_object_pk2,
1080 p_object_pk3 =>p_object_pk3,
1081 p_object_pk4 =>p_object_pk4,
1082 p_object_pk5 =>p_object_pk5) ;
1083
1084 end if;
1085
1086 end if; ---l_dsg_code
1087 end loop;
1088 close get_grp_assign_level;
1089
1090 END assign_dss_group;
1091
1092
1093 --Private Procedures
1094 PROCEDURE stamp_child_entities(p_entity_id IN NUMBER,
1095 p_object_pk1 IN VARCHAR2,
1096 p_object_pk2 IN VARCHAR2,
1097 p_object_pk3 IN VARCHAR2,
1098 p_object_pk4 IN VARCHAR2,
1099 p_object_pk5 IN VARCHAR2) IS
1100
1101 CURSOR get_child_entities(t_entity_id IN NUMBER) IS
1102 SELECT entity_id,
1103 fo.database_object_name,
1104 fo.pk1_column_name,
1105 fo.pk2_column_name ,
1106 dse.parent_entity_id , dse.parent_fk_column1 , dse.parent_fk_column2
1107 FROM fnd_objects fo , hz_dss_entities dse
1108 WHERE parent_entity_id is not null
1109 AND ( dse.object_id is not null and
1110 fo.object_id = dse.object_id )
1111 OR (dse.instance_set_id is not null and
1112 fo.object_id = ( select distinct object_id from fnd_object_instance_sets
1113 where instance_set_id = dse.instance_set_id))
1114 AND dse.parent_entity_id = t_entity_id
1115 AND dse.status = 'A'
1116 order by dse.entity_id;
1117
1118 TYPE child_pk_typ IS REF CURSOR;
1119 child_pk child_pk_typ;
1120
1121 l_child_entity_id NUMBER;
1122 l_object_id NUMBER;
1123 l_database_object_name varchar2(55);
1124 l_pk1_column_name VARCHAR2(50);
1125 l_pk2_column_name VARCHAR2(50);
1126 l_parent_entity_id NUMBER;
1127 l_parent_fk_column1 VARCHAR2(50);
1128 l_parent_fk_column2 VARCHAR2(50);
1129 l_new_pk1_value varchar2(30);
1130 l_new_pk2_value varchar2(30);
1131 l_sql varchar2(2000);
1132
1133 begin
1134
1135 OPEN get_child_entities(p_entity_id);
1136 LOOP
1137 FETCH get_child_entities INTO l_child_entity_id,
1138 l_database_object_name,
1139 l_pk1_column_name,
1140 l_pk2_column_name ,
1141 l_parent_entity_id ,
1142 l_parent_fk_column1 ,
1143 l_parent_fk_column2;
1144
1145 EXIT WHEN get_child_entities%notfound;
1146
1147 if l_parent_fk_column2 is not null then
1148 begin
1149 OPEN child_pk FOR
1150 'SELECT ' || l_pk1_column_name ||' , '||
1151 nvl(l_pk2_column_name,-1) ||
1152 ' FROM ' || l_database_object_name||
1153 ' WHERE '|| l_parent_fk_column1 || '= :id1 ' ||
1154 ' AND ' || l_parent_fk_column2 || '= :id2 ' USING p_object_pk1 , p_object_pk2;
1155
1156 LOOP
1157 FETCH child_pk INTO l_new_pk1_value, l_new_pk2_value;
1158
1159 EXIT when child_pk%notfound;
1160
1161 if l_new_pk2_value = -1 then
1162 l_new_pk2_value := null;
1163 end if;
1164
1165
1166 assign_dss_group(
1167 p_db_object_name => l_database_object_name,
1168 p_object_pk1 => l_new_pk1_value,
1169 p_object_pk2 => l_new_pk2_value,
1170 p_object_pk3 => NULL,
1171 p_object_pk4 => NULL,
1172 p_object_pk5 => NULL,
1173 p_root_db_object_name =>NULL,
1174 p_root_object_pk1 =>NULL,
1175 p_root_object_pk2 =>NULL,
1176 p_root_object_pk3 =>NULL,
1177 p_root_object_pk4 =>NULL,
1178 p_root_object_pk5 =>NULL,
1179 p_process_subentities_flag => 'Y' );
1180
1181 END LOOP;
1182 CLOSE child_pk;
1183
1184 exception when no_data_found then
1185 null;
1186 end;
1187 else
1188 begin
1189 OPEN child_pk FOR
1190 'SELECT ' || l_pk1_column_name || ',' ||
1191 nvl(l_pk2_column_name ,-1) ||
1192 ' FROM ' || l_database_object_name||
1193 ' WHERE '|| l_parent_fk_column1 || '= :id1 ' USING p_object_pk1;
1194
1195 LOOP
1196 FETCH child_pk INTO l_new_pk1_value, l_new_pk2_value;
1197
1198 EXIT when child_pk%notfound;
1199
1200 assign_dss_group(
1201 p_db_object_name => l_database_object_name,
1202 p_object_pk1 => l_new_pk1_value,
1203 p_object_pk2 => l_new_pk2_value,
1204 p_object_pk3 => NULL,
1205 p_object_pk4 => NULL,
1206 p_object_pk5 => NULL,
1207 p_root_db_object_name =>NULL,
1208 p_root_object_pk1 =>NULL,
1209 p_root_object_pk2 =>NULL,
1210 p_root_object_pk3 =>NULL,
1211 p_root_object_pk4 =>NULL,
1212 p_root_object_pk5 =>NULL,
1213 p_process_subentities_flag => 'Y' );
1214
1215 if l_new_pk2_value = -1 then
1216 l_new_pk2_value := null;
1217 end if;
1218
1219 END LOOP;
1220 CLOSE child_pk;
1221
1222 exception when no_data_found then
1223 null;
1224 end;
1225
1226 end if;
1227
1228 end loop;
1229 close get_child_entities;
1230
1231 end stamp_child_entities;
1232
1233 /*===========================================================================+
1234 | PROCEDURE
1235 | switch_context
1236 |
1237 | DESCRIPTION
1238 | For a given user , populate the temporary table HZ_DSS_GROUP_CACHE
1239 | with the Data Sharing Groups that the user has SELECT access to
1240 |
1241 | SCOPE - PUBLIC
1242 |
1243 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1244 |
1245 | ARGUMENTS :
1246 |
1247 | OUT:
1248 | IN/ OUT:
1249 |
1250 | RETURNS : NONE
1251 |
1252 | NOTES
1253 |
1254 | MODIFICATION HISTORY -
1255 | Jyoti Pandey 08-07-2002 Created.
1256 |
1257 +===========================================================================*/
1258
1259 procedure switch_context (p_user_name IN VARCHAR2,
1260 x_return_status OUT NOCOPY VARCHAR2,
1261 x_msg_count OUT NOCOPY NUMBER,
1262 x_msg_data OUT NOCOPY VARCHAR2)IS
1263
1264 x_granted_groups HZ_DSS_UTIL_PUB.dss_group_tbl_type;
1265
1266 i number;
1267 l_user_name fnd_user.user_name%type;
1268 l_return_status VARCHAR2(1);
1269 l_msg_count NUMBER;
1270 l_msg_data VARCHAR2(2000);
1271
1272 begin
1273 ---initialize the message
1274 FND_MSG_PUB.initialize;
1275
1276 --- initialize API return status to success.
1277 x_return_status := FND_API.G_RET_STS_SUCCESS;
1278
1279 l_user_name := fnd_global.user_name;
1280
1281 --clear the temporary table
1282 ---delete from HZ_DSS_GROUP_CACHE ;
1283
1284 --determine the DSG's that the user has SELECT access to
1285 HZ_DSS_UTIL_PUB.get_granted_groups (
1286 l_user_name,
1287 'SELECT',
1288 x_granted_groups,
1289 l_return_status,
1290 l_msg_count,
1291 l_msg_data);
1292
1293
1294 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
1295
1296 FOR I IN x_granted_groups.first..x_granted_groups.last
1297 loop
1298 null;
1299 -- insert into HZ_DSS_GROUP_CACHE (entity_id , dss_group_code)
1300 -- values (x_granted_groups(i).entity_id ,
1301 -- x_granted_groups(i).dss_group_code);
1302
1303 end loop;
1304 ELSE
1305 x_return_status := FND_API.G_RET_STS_ERROR;
1306 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1307 p_count => x_msg_count,
1308 p_data => x_msg_data);
1309 END IF;
1310
1311 exception when others then
1312 raise;
1313
1314 end switch_context;
1315
1316 /**
1317 * FUNCTION
1318 * generate_predicate
1319 *
1320 * DESCRIPTION
1321 *
1322 *
1323 * SCOPE - PUBLIC
1324 *
1325 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1326 *
1327 * ARGUMENTS : IN:
1328 * OUT: T/F
1329 * IN/ OUT:
1330 *
1331 * RETURNS : NONE
1332 *
1333 * NOTES
1334 *
1335 * MODIFICATION HISTORY -
1336 * Bug 2630164 changed signature to include msg_count msg data
1337 * also included validation for Data sharing group and entity_id
1338 *
1339 */
1340
1341 PROCEDURE generate_predicate(
1342 p_dss_group_code IN VARCHAR2,
1343 p_entity_id IN NUMBER,
1344 x_predicate OUT NOCOPY VARCHAR2,
1345 x_return_status OUT NOCOPY VARCHAR2,
1346 x_msg_count OUT NOCOPY NUMBER,
1347 x_msg_data OUT NOCOPY VARCHAR2
1348 ) IS
1349
1350 CURSOR c_find_objects IS
1351 SELECT obj.database_object_name ,
1352 decode(pk1_column_name , null,null, pk1_column_name) ||
1353 decode(pk2_column_name , null,null, ','|| pk2_column_name) ||
1354 decode(pk3_column_name , null,null, ','|| pk3_column_name) ||
1355 decode(pk4_column_name , null,null, ','|| pk4_column_name) ||
1356 decode(pk5_column_name , null,null, ','|| pk5_column_name)
1357 FROM fnd_objects obj,
1358 hz_dss_entities dse
1359 WHERE dse.entity_id = p_entity_id
1360 AND dse.status = 'A'
1361 AND dse.object_id IS NOT NULL
1362 AND dse.object_id = obj.object_id
1363 UNION ALL
1364 SELECT obj.database_object_name ,
1365 decode(pk1_column_name , null,null, pk1_column_name) ||
1366 decode(pk2_column_name , null,null, ','|| pk2_column_name) ||
1367 decode(pk3_column_name , null,null, ','|| pk3_column_name) ||
1368 decode(pk4_column_name , null,null, ','|| pk4_column_name) ||
1369 decode(pk5_column_name , null,null, ','|| pk5_column_name)
1370 FROM fnd_object_instance_sets ins,
1371 fnd_objects obj,
1372 hz_dss_entities dse
1373 WHERE dse.entity_id = p_entity_id
1374 AND dse.status = 'A'
1375 AND dse.instance_set_id IS NOT NULL
1376 AND dse.instance_set_id = ins.instance_set_id
1377 AND ins.object_id = obj.object_id;
1378
1379 l_string VARCHAR2(2000);
1380 l_object_name VARCHAR2(30);
1381 l_sql VARCHAR2(2000);
1382
1383 BEGIN
1384
1385 ---initialize the message
1386 FND_MSG_PUB.initialize;
1387
1388 --- initialize API return status to success.
1389 x_return_status := FND_API.G_RET_STS_SUCCESS;
1390
1391 --- validation passed in group code should be valid
1392 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_groups_b(p_dss_group_code)= 'N' THEN
1393 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_CODE_INVALID');
1394 FND_MSG_PUB.ADD;
1395 RAISE FND_API.G_EXC_ERROR;
1396 END IF;
1397
1398 -- entity id validation
1399 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_entities(p_entity_id) = 'N' THEN
1400 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_ENT_ID_INVALID');
1401 FND_MSG_PUB.ADD;
1402 RAISE FND_API.G_EXC_ERROR;
1403 END IF;
1404
1405 OPEN c_find_objects;
1406 FETCH c_find_objects INTO l_object_name, l_string;
1407 CLOSE c_find_objects;
1408
1409 ---Determine the DSG
1410 l_sql := 'hz_dss_util_pub.determine_dss_group(' ||
1411 ''''||l_object_name||'''' || ',' ||
1412 l_string ||
1413 ') = ' || ''''||p_dss_group_code || '''';
1414
1415 ------------------------------------------------------------------------
1416 ---HR's validation: check if the Data Sharing Group is HR_SHARED then it
1417 ---should pass HR's Created by module test also
1418 ---a similar check is performed in party_validate too
1419 ------------------------------------------------------------------------
1420
1421 IF p_dss_group_code = 'HR_SHARED' THEN
1422 ---get the user's module
1423 l_sql := l_sql || ' AND '||
1424 'NVL(fnd_profile.value(''HZ_CREATED_BY_MODULE''), ''-222'')' ||
1425 ' = ''HR API'' ';
1426 END IF;
1427
1428 -- Build and test the sql statement to make sure generated predicate
1429 -- is valid
1430 -- l_sql_to_test := ' select 1 from ' ||l_object_name || ' where '|| l_sql ;
1431 -- c := dbms_sql.open_cursor;
1432 -- dbms_sql.parse(c, l_sql_to_test, dbms_sql.native);
1433
1434 x_return_status := FND_API.G_RET_STS_SUCCESS;
1435 x_predicate := l_sql ;
1436
1437 EXCEPTION
1438 WHEN FND_API.G_EXC_ERROR THEN
1439 x_return_status := FND_API.G_RET_STS_ERROR;
1440
1441 FND_MSG_PUB.COUNT_AND_GET(
1442 p_encoded => fnd_api.g_false,
1443 p_count => x_msg_count,
1444 p_data => x_msg_data
1445 );
1446
1447 WHEN OTHERS THEN
1448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1449
1450 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1451 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1452 FND_MSG_PUB.ADD;
1453
1454 FND_MSG_PUB.COUNT_AND_GET(
1455 p_encoded => fnd_api.g_false,
1456 p_count => x_msg_count,
1457 p_data => x_msg_data
1458 );
1459
1460 END generate_predicate;
1461
1462 /**
1463 * PROCEDURE
1464 * print
1465 *
1466 * DESCRIPTION
1467 *
1468 *
1469 * SCOPE - PRIVATE
1470 *
1471 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1472 *
1473 * ARGUMENTS : IN:
1474 * OUT: T/F
1475 * IN/ OUT:
1476 *
1477 * RETURNS : NONE
1478 *
1479 * NOTES
1480 *
1481 * MODIFICATION HISTORY -
1482 *
1483 */
1484
1485 PROCEDURE print (
1486 p_str IN VARCHAR2
1487 ) IS
1488
1489 j NUMBER;
1490
1491 BEGIN
1492 j := 1;
1493
1494 FOR i IN 1..CEIL(length(p_str)/255) LOOP
1495 -- dbms_output.put_line( SUBSTR( p_str, j, 255 ) );
1496 j := j + 255;
1497 END LOOP;
1498
1499 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1500 hz_utility_v2pub.debug(p_prefix=>'HZDSS',p_message=>SUBSTR( p_str, j, 255 ) ,
1501 p_msg_level=>fnd_log.level_statement);
1502 END IF;
1503
1504 END print;
1505
1506
1507 /**
1508 * FUNCTION
1509 * check_created_by_module_cr
1510 *
1511 * DESCRIPTION
1512 *
1513 *
1514 * SCOPE - PRIVATE
1515 *
1516 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1517 *
1518 * ARGUMENTS : IN:
1519 * OUT: T/F
1520 * IN/ OUT:
1521 *
1522 * RETURNS : NONE
1523 *
1524 * NOTES
1525 *
1526 * MODIFICATION HISTORY -
1527 *
1528 */
1529
1530 FUNCTION check_created_by_module_cr (
1531 p_dss_group_code IN VARCHAR2,
1532 p_parent_party_id_tbl IN t_number_tbl,
1533 p_parent_party_type_tbl IN t_varchar_30_tbl
1534 ) RETURN VARCHAR2 IS
1535
1536 CURSOR c_check_created_by_modules (
1537 p_party_id NUMBER,
1538 p_dss_group_code VARCHAR2
1539 ) IS
1540 SELECT 1
1541 FROM hz_parties p, hz_dss_criteria dc
1542 WHERE p.party_id = p_party_id
1543 AND p.created_by_module IN (
1544 SELECT owner_table_id2
1545 FROM hz_dss_criteria
1546 WHERE dss_group_code = p_dss_group_code
1547 AND owner_table_name = 'AR_LOOKUPS'
1548 AND owner_table_id1 = 'HZ_CREATED_BY_MODULES'
1549 AND status = 'A');
1550
1551 l_falling_into_the_group VARCHAR2(2);
1552 l_found_non_rel_party VARCHAR2(1);
1553 l_dummy NUMBER(1);
1554
1555 BEGIN
1556
1557 l_falling_into_the_group := 'N';
1558 l_found_non_rel_party := 'N';
1559
1560 FOR i IN 1..p_parent_party_id_tbl.COUNT LOOP
1561 IF p_parent_party_type_tbl(i) <> 'PARTY_RELATIONSHIP' THEN
1562 l_found_non_rel_party := 'Y';
1563
1564 OPEN c_check_created_by_modules(p_parent_party_id_tbl(i), p_dss_group_code);
1565 FETCH c_check_created_by_modules INTO l_dummy;
1566 IF c_check_created_by_modules%FOUND THEN
1567 CLOSE c_check_created_by_modules;
1568 l_falling_into_the_group := 'Y';
1569 EXIT;
1570 END IF;
1571 CLOSE c_check_created_by_modules;
1572 ELSE -- added for better debugging
1573 print('check_created_by_module_cr:'||p_parent_party_id_tbl(i)||'-PARTY_RELATIONSHIP..skipped');
1574 NULL;
1575 END IF;
1576 END LOOP;
1577
1578 IF l_found_non_rel_party = 'N' THEN
1579 l_falling_into_the_group := 'NA';
1580 END IF;
1581
1582 print('check_created_by_module_cr - '||l_falling_into_the_group);
1583
1584 RETURN l_falling_into_the_group;
1585
1586 END check_created_by_module_cr;
1587
1588
1589 /**
1590 * FUNCTION
1591 * check_classifications
1592 *
1593 * DESCRIPTION
1594 *
1595 *
1596 * SCOPE - PRIVATE
1597 *
1598 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1599 *
1600 * ARGUMENTS : IN:
1601 * OUT: T/F
1602 * IN/ OUT:
1603 *
1604 * RETURNS : NONE
1605 *
1606 * NOTES
1607 *
1608 * MODIFICATION HISTORY -
1609 * 17-SEP-2008 Sudhir Gokavarapu Bug 7290836: Changed l_class_code_is_used check
1610 * from N to Y for EXIT criteria.
1611 * Added 'order by' to c_sub_class_codes cursor for
1612 * performance reason. We do not want to travese
1613 * all subcodes if parnet code is securing criteria
1614 * and is assigned to the party.
1615 *
1616 */
1617
1618 FUNCTION check_classifications (
1619 p_dss_group_code IN VARCHAR2,
1620 p_party_id IN NUMBER
1621 ) RETURN VARCHAR2 IS
1622
1623 CURSOR c_check_classifications (
1624 p_party_id NUMBER,
1625 p_class_category VARCHAR2,
1626 p_class_code VARCHAR2
1627 ) IS
1628 SELECT 1
1629 FROM hz_code_assignments
1630 WHERE owner_table_name = 'HZ_PARTIES'
1631 AND owner_table_id = p_party_id
1632 AND class_category = p_class_category
1633 AND class_code = p_class_code
1634 AND SYSDATE BETWEEN
1635 NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE)
1636 AND status = 'A';
1637
1638 --
1639 -- Get the classification codes for the data sharing group
1640 --
1641 CURSOR c_class_codes_for_dsg (
1642 p_dss_group_code VARCHAR2
1643 ) IS
1644 SELECT dsc.owner_table_id1 , dsc.owner_table_id2
1645 FROM hz_dss_criteria dsc
1646 WHERE dsc.dss_group_code = p_dss_group_code
1647 AND owner_table_name = 'FND_LOOKUP_VALUES'
1648 AND status = 'A';
1649
1650 --
1651 -- get child class codes
1652 --
1653 CURSOR c_sub_class_codes (
1654 p_class_category VARCHAR2,
1655 p_class_code VARCHAR2
1656 ) IS
1657 SELECT class_code
1658 FROM hz_class_code_denorm ccd
1659 WHERE ccd.class_category = p_class_category
1660 AND INSTRB('/'||concat_class_code||'/','/'||p_class_code||'/') > 0
1661 AND LANGUAGE = userenv('LANG')
1662 ORDER BY concat_class_code; -- Bug 7290836(no need to fetch more rec if
1663 -- parent class code was assigned to party)
1664
1665 l_dummy NUMBER(1);
1666 l_class_code_is_used VARCHAR2(1);
1667 l_class_category_tbl t_varchar_30_tbl;
1668 l_class_code_tbl t_varchar_30_tbl;
1669 l_sub_class_code_tbl t_varchar_30_tbl;
1670 l_falling_into_the_group VARCHAR2(2);
1671
1672 BEGIN
1673
1674 l_falling_into_the_group := 'N';
1675
1676 OPEN c_class_codes_for_dsg(p_dss_group_code);
1677 FETCH c_class_codes_for_dsg BULK COLLECT INTO
1678 l_class_category_tbl, l_class_code_tbl;
1679 CLOSE c_class_codes_for_dsg;
1680
1681 --
1682 -- all class codes (or its sub class codes) in a dsg must
1683 -- be assigned to the party
1684 --
1685 FOR i IN 1..l_class_category_tbl.COUNT LOOP
1686 l_class_code_is_used := 'N';
1687
1688 OPEN c_sub_class_codes(l_class_category_tbl(i), l_class_code_tbl(i));
1689 FETCH c_sub_class_codes BULK COLLECT INTO l_sub_class_code_tbl;
1690 CLOSE c_sub_class_codes;
1691
1692 FOR j IN 1..l_sub_class_code_tbl.COUNT LOOP
1693 OPEN c_check_classifications(p_party_id, l_class_category_tbl(i), l_sub_class_code_tbl(j));
1694 FETCH c_check_classifications INTO l_dummy;
1695
1696 IF c_check_classifications%FOUND THEN
1697 CLOSE c_check_classifications;
1698 l_class_code_is_used := 'Y';
1699 EXIT;
1700 END IF;
1701 CLOSE c_check_classifications;
1702 END LOOP;
1703
1704 -- IF l_class_code_is_used = 'N' THEN -- Bug 7290836(Should always exist if found a match)
1705 IF l_class_code_is_used = 'Y' THEN -- Changed from N to Y
1706 EXIT;
1707 END IF;
1708 END LOOP;
1709
1710 IF l_class_code_is_used = 'Y' THEN
1711 l_falling_into_the_group := 'Y';
1712 END IF;
1713
1714 print('check_classifications - '||l_falling_into_the_group);
1715
1716 RETURN l_falling_into_the_group;
1717
1718 END check_classifications;
1719
1720
1721 /**
1722 * FUNCTION
1723 * check_classification_cr
1724 *
1725 * DESCRIPTION
1726 *
1727 *
1728 * SCOPE - PRIVATE
1729 *
1730 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1731 *
1732 * ARGUMENTS : IN:
1733 * OUT: T/F
1734 * IN/ OUT:
1735 *
1736 * RETURNS : NONE
1737 *
1738 * NOTES
1739 *
1740 * MODIFICATION HISTORY -
1741 *
1742 */
1743
1744 FUNCTION check_classification_cr (
1745 p_dss_group_code IN VARCHAR2,
1746 p_parent_party_id_tbl IN t_number_tbl,
1747 p_parent_party_type_tbl IN t_varchar_30_tbl
1748 ) RETURN VARCHAR2 IS
1749
1750 l_falling_into_the_group VARCHAR2(2);
1751 l_found_non_rel_party VARCHAR2(1);
1752
1753 BEGIN
1754
1755 l_falling_into_the_group := 'N';
1756 l_found_non_rel_party := 'N';
1757
1758 FOR i IN 1..p_parent_party_id_tbl.COUNT LOOP
1759 IF p_parent_party_type_tbl(i) <> 'PARTY_RELATIONSHIP' THEN
1760 l_found_non_rel_party := 'Y';
1761
1762 l_falling_into_the_group :=
1763 check_classifications(p_dss_group_code, p_parent_party_id_tbl(i));
1764
1765 IF l_falling_into_the_group = 'Y' THEN
1766 EXIT;
1767 END IF;
1768 ELSE -- added for better debugging
1769 print('check_classification_cr:'||p_parent_party_id_tbl(i)||'-PARTY_RELATIONSHIP..skipped');
1770 NULL;
1771 END IF;
1772 END LOOP;
1773
1774 IF l_found_non_rel_party = 'N' THEN
1775 l_falling_into_the_group := 'NA';
1776 END IF;
1777
1778 print('check_classification_cr - '||l_falling_into_the_group);
1779
1780 RETURN l_falling_into_the_group;
1781
1782 END check_classification_cr;
1783
1784
1785 /**
1786 * FUNCTION
1787 * check_relationship_types
1788 *
1789 * DESCRIPTION
1790 *
1791 *
1792 * SCOPE - PRIVATE
1793 *
1794 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1795 *
1796 * ARGUMENTS : IN:
1797 * OUT: T/F
1798 * IN/ OUT:
1799 *
1800 * RETURNS : NONE
1801 *
1802 * NOTES
1803 *
1804 * MODIFICATION HISTORY -
1805 13-FEB-2007 Nishant Singhai Bug 5687869
1806 If same subject id and object id have 2 different relationships,
1807 and only 1 of them is secured, then security rules get applied
1808 either to both or none (based on randomly which record gets
1809 picked up first).
1810 For example, if 1 is updateable while other is not,
1811 without relationship id filter, behaviour is random and
1812 either both becomes updateable or both becomes non-updateable.
1813 25-Mar-2010 Sudhir Gokavarapu Bug#8837776 FP for Bug 8797990
1814 Changes made for bug 5687869 is causing regression in other
1815 entity checks (other than Relationship). If any 'Relationship
1816 Role' criteria is met, all the entities which are marked to be
1817 secured should be secured. Securing only 1 relationship and
1818 leaving rest open is not the design of DSS.
1819 Additionally, enhanced the support for securing entities
1820 hanging from Relationship Party (like Contact Point for Org Contacts)
1821 Since relationship party cannot have 'Relationship Criteria'
1822 attached to it. So, there is no way to secure them. In that case,
1823 check if parties forming the relationship meets the "Relationship
1824 Security Criteria".
1825 *
1826 */
1827
1828
1829 FUNCTION check_relationship_types (
1830 p_dss_group_code IN VARCHAR2,
1831 p_party_id IN NUMBER
1832 --,p_relationship_id IN NUMBER -- Bug 5687869 (Nishant)
1833 ) RETURN VARCHAR2 IS
1834
1835 CURSOR c_check_relationship_types_p (
1836 p_party_id NUMBER,
1837 p_relationship_type_id NUMBER
1838 --,p_relationship_id NUMBER -- Bug 5687869 (Nishant)
1839 ) IS
1840 -- SELECT 1
1841 /*SELECT rel.relationship_type -- changed so that it is easy to debug
1842 FROM hz_relationships rel
1843 WHERE rel.subject_id = p_party_id AND
1844 rel.subject_table_name = 'HZ_PARTIES'
1845 AND rel.relationship_id = p_relationship_id -- added for Bug 5687869
1846 AND (rel.relationship_type, rel.relationship_code, rel.subject_type, rel.object_type) IN (
1847 SELECT relationship_type, forward_rel_code,
1848 subject_type, object_type
1849 FROM hz_relationship_types rt
1850 WHERE rt.relationship_type_id = p_relationship_type_id)
1851 AND SYSDATE BETWEEN
1852 NVL(start_date, SYSDATE) AND NVL(end_date, SYSDATE)
1853 AND status = 'A'
1854 AND ROWNUM = 1;
1855 */
1856 --Changes for bug 8837776/8797990
1857 SELECT rel.relationship_code -- changed so that it is easy to debug
1858 FROM hz_relationships rel
1859 WHERE p_party_id IN (rel.subject_id, rel.object_id)
1860 AND rel.subject_table_name = 'HZ_PARTIES'
1861 AND rel.object_table_name = 'HZ_PARTIES'
1862 AND rel.directional_flag = 'F'
1863 AND (rel.relationship_type, rel.relationship_code, rel.subject_type, rel.object_type) IN (
1864 SELECT relationship_type, forward_rel_code,
1865 subject_type, object_type
1866 FROM hz_relationship_types rt
1867 WHERE rt.relationship_type_id = p_relationship_type_id)
1868 AND SYSDATE BETWEEN
1869 NVL(rel.start_date, SYSDATE) AND NVL(rel.end_date, SYSDATE)
1870 AND rel.status = 'A'
1871 AND ROWNUM = 1;
1872
1873 CURSOR c_dss_relationship_types (
1874 p_dss_group_code VARCHAR2
1875 ) IS
1876 SELECT owner_table_id1
1877 FROM hz_dss_criteria dsc
1878 WHERE dsc.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
1879 AND dsc.dss_group_code = p_dss_group_code
1880 AND dsc.status = 'A';
1881
1882 l_falling_into_the_group VARCHAR2(2);
1883 l_dummy NUMBER(1);
1884 l_dummy_varchar VARCHAR2(100);
1885 l_relationship_type_id_tbl t_number_tbl;
1886
1887 BEGIN
1888 print('BEGIN check_relationship_types');
1889 print('p_dss_group_code='||p_dss_group_code||', p_party_id='||p_party_id);
1890
1891 OPEN c_dss_relationship_types(p_dss_group_code);
1892 FETCH c_dss_relationship_types BULK COLLECT INTO l_relationship_type_id_tbl;
1893 CLOSE c_dss_relationship_types;
1894
1895 l_falling_into_the_group := 'N'; --'Y';
1896 print('Set Initial l_falling_into_the_group=N');
1897
1898 IF l_relationship_type_id_tbl.Count > 0 THEN
1899 print('Relationship role criteria is defined. Check further if party falls in this');
1900 FOR i IN 1..l_relationship_type_id_tbl.COUNT LOOP
1901 -- debug message
1902 --
1903 --
1904 print ('relationship_type_id = '||l_relationship_type_id_tbl(i));
1905
1906 -- re-initialize the dummy variable
1907 l_dummy_varchar := NULL;
1908
1909 OPEN c_check_relationship_types_p(
1910 p_party_id, l_relationship_type_id_tbl(i)); -- Bug 8797990
1911 -- p_party_id, l_relationship_type_id_tbl(i),p_relationship_id); --(Bug 5687869)
1912 FETCH c_check_relationship_types_p INTO l_dummy_varchar; --l_dummy;
1913 print('Validated against relationship type :'||l_dummy_varchar);
1914 -- Continue the loop to check if any of the relationship roles are secured
1915 IF c_check_relationship_types_p%FOUND THEN
1916 l_falling_into_the_group := 'Y';
1917 print('c_check_relationship_types_p%FOUND..l_falling_into_the_group=Y.. exit');
1918 CLOSE c_check_relationship_types_p;
1919 EXIT;
1920 ELSE -- NOTFOUND
1921 l_falling_into_the_group := 'N';
1922 print('c_check_relationship_types_p%NOTFOUND..l_falling_into_the_group=N..continue loop');
1923 END IF;
1924 CLOSE c_check_relationship_types_p;
1925 END LOOP;
1926 ELSE -- no relationship role criteria defined
1927 NULL;
1928 print('no relationship role criteria defined..');
1929 END IF;
1930
1931 -- debug message
1932 --
1933 print('Finally l_falling_into_the_group='||l_falling_into_the_group);
1934 print('END check_relationship_types');
1935
1936 RETURN l_falling_into_the_group;
1937
1938 END check_relationship_types;
1939
1940
1941 /**
1942 * FUNCTION
1943 * check_relationship_type_cr
1944 *
1945 * DESCRIPTION
1946 *
1947 *
1948 * SCOPE - PRIVATE
1949 *
1950 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1951 *
1952 * ARGUMENTS : IN:
1953 * OUT: T/F
1954 * IN/ OUT:
1955 *
1956 * RETURNS : NONE
1957 *
1958 * NOTES
1959 *
1960 * MODIFICATION HISTORY -
1961 * 25-Mar-2010 Sudhir Gokavarapu 8837776 FP for Bug 8797990
1962 * details in check_relationship_types MOD History
1963 *
1964 */
1965
1966
1967 FUNCTION check_relationship_type_cr (
1968 p_dss_group_code IN VARCHAR2,
1969 p_db_object_name IN VARCHAR2,
1970 p_object_pk1 IN VARCHAR2 DEFAULT NULL,
1971 p_object_pk2 IN VARCHAR2 DEFAULT NULL,
1972 p_object_pk3 IN VARCHAR2 DEFAULT NULL,
1973 p_object_pk4 IN VARCHAR2 DEFAULT NULL,
1974 p_object_pk5 IN VARCHAR2 DEFAULT NULL,
1975 p_parent_party_id_tbl IN t_number_tbl,
1976 p_parent_party_type_tbl IN t_varchar_30_tbl
1977 ) RETURN VARCHAR2 IS
1978
1979 CURSOR c_get_subj_obj_id (
1980 p_party_id NUMBER
1981 ) IS
1982 -- since relationship_party cannot have additional relationships
1983 -- we do not anticipate multiple records to be present for relationship
1984 -- party_id
1985 SELECT subject_id, object_id
1986 FROM hz_relationships rel
1987 WHERE rel.party_id = p_party_id
1988 AND rel.directional_flag = 'F'
1989 AND SYSDATE BETWEEN
1990 NVL(rel.start_date, SYSDATE) AND NVL(rel.end_date, SYSDATE)
1991 AND rel.status = 'A'
1992 AND ROWNUM = 1;
1993 /*
1994 CURSOR c_check_relationship_types_o (
1995 p_party_id NUMBER,
1996 p_dss_group_code VARCHAR2
1997 ) IS
1998 -- SELECT 1 (Bug 5687869)
1999 SELECT rel.relationship_type
2000 FROM hz_relationships rel
2001 WHERE rel.party_id = p_party_id
2002 AND rel.relationship_id = p_object_pk1 --(Bug 5687869)
2003 AND (rel.relationship_type, rel.relationship_code, rel.subject_type, rel.object_type) IN (
2004 SELECT relationship_type, forward_rel_code,
2005 subject_type, object_type
2006 FROM hz_relationship_types rt, hz_dss_criteria dsc
2007 WHERE dsc.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
2008 AND dsc.dss_group_code = p_dss_group_code
2009 AND dsc.owner_table_id1 = rt.relationship_type_id
2010 AND dsc.status = 'A' )
2011 AND SYSDATE BETWEEN
2012 NVL(start_date, SYSDATE) AND NVL(end_date, SYSDATE)
2013 AND status = 'A';
2014
2015 */
2016 -- Get the subject_id and object_id of the relationship party
2017 -- Then check relationship security criteria on both the parties
2018 CURSOR c_check_relationship_types_o (
2019 p_subj_id NUMBER,
2020 p_obj_id NUMBER,
2021 p_dss_group_code VARCHAR2
2022 ) IS
2023 SELECT rel.relationship_code -- changed so that it is easy to debug
2024 FROM hz_relationships rel
2025 WHERE p_subj_id IN (rel.subject_id, rel.object_id)
2026 AND rel.subject_table_name = 'HZ_PARTIES'
2027 AND rel.object_table_name = 'HZ_PARTIES'
2028 AND rel.directional_flag = 'F'
2029 AND (rel.relationship_type, rel.relationship_code, rel.subject_type, rel.object_type) IN (
2030 SELECT relationship_type, forward_rel_code,
2031 subject_type, object_type
2032 FROM hz_relationship_types rt, hz_dss_criteria dsc
2033 WHERE dsc.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
2034 AND dsc.dss_group_code = p_dss_group_code
2035 AND dsc.owner_table_id1 = rt.relationship_type_id
2036 AND dsc.status = 'A')
2037 AND SYSDATE BETWEEN
2038 NVL(rel.start_date, SYSDATE) AND NVL(rel.end_date, SYSDATE)
2039 AND rel.status = 'A'
2040 AND ROWNUM = 1
2041 UNION
2042 SELECT rel.relationship_code -- changed so that it is easy to debug
2043 FROM hz_relationships rel
2044 WHERE p_obj_id IN (rel.subject_id, rel.object_id)
2045 AND rel.subject_table_name = 'HZ_PARTIES'
2046 AND rel.object_table_name = 'HZ_PARTIES'
2047 AND rel.directional_flag = 'F'
2048 AND (rel.relationship_type, rel.relationship_code, rel.subject_type, rel.object_type) IN (
2049 SELECT relationship_type, forward_rel_code,
2050 subject_type, object_type
2051 FROM hz_relationship_types rt, hz_dss_criteria dsc
2052 WHERE dsc.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
2053 AND dsc.dss_group_code = p_dss_group_code
2054 AND dsc.owner_table_id1 = rt.relationship_type_id
2055 AND dsc.status = 'A')
2056 AND SYSDATE BETWEEN
2057 NVL(rel.start_date, SYSDATE) AND NVL(rel.end_date, SYSDATE)
2058 AND rel.status = 'A'
2059 AND ROWNUM = 1;
2060
2061 l_falling_into_the_group VARCHAR2(2);
2062 l_dummy_varchar VARCHAR2(100);
2063 l_dummy NUMBER(1);
2064 l_subj_id NUMBER;
2065 l_obj_id NUMBER;
2066 BEGIN
2067
2068 --
2069 -- debug message
2070 --
2071
2072 print ('BEGIN check_relationship_type_cr ...');
2073 print ('p_db_object_name = '||p_db_object_name||' '||
2074 'p_object_pk1 = '||p_object_pk1);
2075
2076
2077 l_falling_into_the_group := 'N';
2078
2079 FOR i IN 1..p_parent_party_id_tbl.COUNT LOOP
2080 IF p_parent_party_type_tbl(i) = 'PARTY_RELATIONSHIP' THEN
2081 -- debug messages
2082 print('PARTY_TYPE:PARTY_RELATIONSHIP..checking cursor c_check_relationship_types_o');
2083 print('CURSOR parameter p_party_id='||p_parent_party_id_tbl(i));
2084 print('CURSOR parameter p_dss_group_code='||p_dss_group_code);
2085
2086 -- initialize
2087 l_subj_id := NULL;
2088 l_obj_id := NULL;
2089 OPEN c_get_subj_obj_id(p_parent_party_id_tbl(i));
2090 FETCH c_get_subj_obj_id INTO l_subj_id, l_obj_id;
2091
2092 print ('Subject_id ='||l_subj_id||' ,Object_id ='||l_obj_id||
2093 ' FOR party_id='||p_parent_party_id_tbl(i));
2094
2095 IF c_get_subj_obj_id%FOUND THEN
2096
2097 print('c_get_subj_obj_id FOUND..checking rel criteria for subject_id as well as object_id');
2098 l_dummy_varchar := NULL;
2099
2100
2101 OPEN c_check_relationship_types_o(
2102 --p_parent_party_id_tbl(i), p_dss_group_code);
2103 l_subj_id, l_obj_id, p_dss_group_code);
2104 FETCH c_check_relationship_types_o INTO l_dummy_varchar; --l_dummy;
2105
2106 print('Validated against relationship code :'||l_dummy_varchar);
2107
2108 IF c_check_relationship_types_o%FOUND THEN
2109 print('c_check_relationship_types_o FOUND..l_falling_into_the_group=Y..exit ');
2110 l_falling_into_the_group := 'Y';
2111 CLOSE c_check_relationship_types_o;
2112 EXIT;
2113 ELSE -- NOTFOUND.. Continue
2114 print('c_check_relationship_types_o NOTFOUND..l_falling_into_the_group=N ');
2115 NULL;
2116 END IF;
2117 CLOSE c_check_relationship_types_o;
2118 ELSE
2119 NULL;
2120 print('c_get_subj_obj_id: Rel Rec NOTFOUND..skipped relationship criteria check for party_id='||
2121 p_parent_party_id_tbl(i));
2122 END IF;
2123
2124 CLOSE c_get_subj_obj_id;
2125
2126 ELSE -- not relationship party
2127 -- debug messages
2128 print('PARTY_TYPE='||p_parent_party_type_tbl(i)||'..checking function check_relationship_types');
2129 print('CURSOR parameter p_party_id='||p_parent_party_id_tbl(i));
2130 print('CURSOR parameter p_dss_group_code='||p_dss_group_code);
2131
2132 l_falling_into_the_group :=
2133 check_relationship_types(p_dss_group_code, p_parent_party_id_tbl(i)); -- Bug 8797990
2134 --check_relationship_types(p_dss_group_code, p_parent_party_id_tbl(i),p_object_pk1); --(Bug 5687869)
2135
2136 IF l_falling_into_the_group = 'Y' THEN
2137 print('l_falling_into_the_group=Y..exit');
2138 EXIT;
2139 END IF;
2140 END IF;
2141 END LOOP;
2142
2143 print('Finally check_relationship_type_cr - '||l_falling_into_the_group);
2144 print('END check_relationship_type_cr');
2145
2146 RETURN l_falling_into_the_group;
2147
2148 END check_relationship_type_cr;
2149
2150
2151 /**
2152 * FUNCTION
2153 * is_relationship_party
2154 *
2155 * DESCRIPTION
2156 *
2157 *
2158 * SCOPE - PRIVATE
2159 *
2160 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2161 *
2162 * ARGUMENTS : IN:
2163 * OUT: T/F
2164 * IN/ OUT:
2165 *
2166 * RETURNS : NONE
2167 *
2168 * NOTES
2169 *
2170 * MODIFICATION HISTORY -
2171 *
2172 */
2173
2174 FUNCTION is_relationship_party (
2175 p_party_id IN NUMBER,
2176 x_relationship_id OUT NOCOPY NUMBER
2177 ) RETURN VARCHAR2 IS
2178
2179 CURSOR c_party (
2180 p_party_id NUMBER
2181 ) IS
2182 SELECT party_type
2183 FROM hz_parties
2184 WHERE party_id = p_party_id;
2185
2186 CURSOR c_relationship_party (
2187 p_party_id NUMBER
2188 ) IS
2189 SELECT relationship_id
2190 FROM hz_relationships
2191 WHERE party_id = p_party_id
2192 AND directional_flag = 'F';
2193
2194 l_party_type VARCHAR2(30);
2195 l_is_relationship_party VARCHAR2(1);
2196
2197 BEGIN
2198
2199 l_is_relationship_party := 'N';
2200
2201 OPEN c_party(p_party_id);
2202 FETCH c_party INTO l_party_type;
2203 CLOSE c_party;
2204
2205 IF l_party_type IS NOT NULL THEN
2206 IF l_party_type <> 'PARTY_RELATIONSHIP' THEN
2207 l_is_relationship_party := 'N';
2208 ELSE
2209 l_is_relationship_party := 'Y';
2210
2211 OPEN c_relationship_party(p_party_id);
2212 FETCH c_relationship_party INTO x_relationship_id;
2213 CLOSE c_relationship_party;
2214 END IF;
2215 END IF;
2216
2217 RETURN l_is_relationship_party;
2218
2219 END is_relationship_party;
2220
2221
2222 /**
2223 * PROCEDURE
2224 * get_parent_party_id
2225 *
2226 * DESCRIPTION
2227 *
2228 *
2229 * SCOPE - PRIVATE
2230 *
2231 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2232 *
2233 * ARGUMENTS : IN:
2234 * OUT: T/F
2235 * IN/ OUT:
2236 *
2237 * RETURNS : NONE
2238 *
2239 * NOTES
2240 *
2241 * MODIFICATION HISTORY -
2242 *
2243 */
2244
2245 PROCEDURE get_parent_party_id (
2246 p_db_object_name IN VARCHAR2,
2247 p_object_pk1 IN VARCHAR2 DEFAULT NULL,
2248 p_object_pk2 IN VARCHAR2 DEFAULT NULL,
2249 p_object_pk3 IN VARCHAR2 DEFAULT NULL,
2250 p_object_pk4 IN VARCHAR2 DEFAULT NULL,
2251 p_object_pk5 IN VARCHAR2 DEFAULT NULL,
2252 x_party_id_tbl OUT NOCOPY t_number_tbl,
2253 x_party_type_tbl OUT NOCOPY t_varchar_30_tbl
2254 ) IS
2255
2256 CURSOR c_party (
2257 p_party_id NUMBER
2258 ) IS
2259 SELECT party_id, party_type
2260 FROM hz_parties
2261 WHERE party_id = p_party_id;
2262
2263 CURSOR c_party_site (
2264 p_party_site_id NUMBER
2265 ) IS
2266 SELECT p.party_id, p.party_type
2267 FROM hz_party_sites ps, hz_parties p
2268 WHERE party_site_id = p_party_site_id
2269 AND ps.party_id = p.party_id;
2270
2271 CURSOR c_location (
2272 p_location_id NUMBER
2273 ) IS
2274 SELECT p.party_id, p.party_type
2275 FROM hz_locations loc, hz_party_sites ps, hz_parties p
2276 WHERE loc.location_id = p_location_id
2277 AND loc.location_id = ps.location_id
2278 AND ps.party_id = p.party_id;
2279
2280 CURSOR c_code_assignment (
2281 p_code_assignment_id NUMBER
2282 ) IS
2283 SELECT p.party_id, p.party_type
2284 FROM hz_code_assignments, hz_parties p
2285 WHERE code_assignment_id = p_code_assignment_id
2286 AND owner_table_name = 'HZ_PARTIES'
2287 AND owner_table_id = p.party_id;
2288
2289 CURSOR c_relationship (
2290 p_relationship_id NUMBER,
2291 p_directional_flag VARCHAR2
2292 ) IS
2293 SELECT subject_id, subject_table_name, subject_type,
2294 object_id, object_table_name, object_type
2295 FROM hz_relationships
2296 WHERE relationship_id = p_relationship_id
2297 AND directional_flag = p_directional_flag
2298 AND (subject_table_name = 'HZ_PARTIES' OR
2299 object_table_name = 'HZ_PARTIES');
2300
2301 CURSOR c_contact_point (
2302 p_contact_point_id NUMBER
2303 ) IS
2304 SELECT p.party_id, p.party_type
2305 FROM hz_contact_points, hz_parties p
2306 WHERE contact_point_id = p_contact_point_id
2307 AND owner_table_name = 'HZ_PARTIES'
2308 AND owner_table_id = p.party_id;
2309
2310 CURSOR c_contact_point_ps (
2311 p_contact_point_id NUMBER
2312 ) IS
2313 SELECT p.party_id, p.party_type
2314 FROM hz_contact_points cp, hz_party_sites ps, hz_parties p
2315 WHERE contact_point_id = p_contact_point_id
2316 AND owner_table_name = 'HZ_PARTY_SITES'
2317 AND owner_table_id = ps.party_site_id
2318 AND ps.party_id = p.party_id;
2319
2320 i NUMBER;
2321 l_subject_id NUMBER;
2322 l_subject_table_name VARCHAR2(30);
2323 l_subject_type VARCHAR2(30);
2324 l_object_id NUMBER;
2325 l_object_table_name VARCHAR2(30);
2326 l_object_type VARCHAR2(30);
2327
2328 BEGIN
2329
2330 IF p_db_object_name = 'HZ_PARTIES' THEN
2331 OPEN c_party(TO_NUMBER(p_object_pk1));
2332 FETCH c_party BULK COLLECT INTO x_party_id_tbl, x_party_type_tbl;
2333 CLOSE c_party;
2334
2335 ELSIF p_db_object_name = 'HZ_PARTY_SITES' THEN
2336 OPEN c_party_site(TO_NUMBER(p_object_pk1));
2337 FETCH c_party_site BULK COLLECT INTO x_party_id_tbl, x_party_type_tbl;
2338 CLOSE c_party_site;
2339
2340 ELSIF p_db_object_name = 'HZ_LOCATIONS' THEN
2341 OPEN c_location(TO_NUMBER(p_object_pk1));
2342 FETCH c_location BULK COLLECT INTO x_party_id_tbl, x_party_type_tbl;
2343 CLOSE c_location;
2344
2345 ELSIF p_db_object_name = 'HZ_CODE_ASSIGNMENTS' THEN
2346 OPEN c_code_assignment(TO_NUMBER(p_object_pk1));
2347 FETCH c_code_assignment BULK COLLECT INTO x_party_id_tbl, x_party_type_tbl;
2348 CLOSE c_code_assignment;
2349
2350 ELSIF p_db_object_name = 'HZ_RELATIONSHIPS' THEN
2351 OPEN c_relationship(TO_NUMBER(p_object_pk1), p_object_pk2);
2352 FETCH c_relationship INTO
2353 l_subject_id, l_subject_table_name, l_subject_type,
2354 l_object_id, l_object_table_name, l_object_type;
2355 CLOSE c_relationship;
2356
2357 i := 1;
2358 IF l_subject_table_name = 'HZ_PARTIES' THEN
2359 x_party_id_tbl(i) := l_subject_id;
2360 x_party_type_tbl(i) := l_subject_type;
2361 i := i+1;
2362 END IF;
2363 IF l_object_table_name = 'HZ_PARTIES' THEN
2364 x_party_id_tbl(i) := l_object_id;
2365 x_party_type_tbl(i) := l_object_type;
2366 i := i+1;
2367 END IF;
2368
2369 IF i = 3 AND l_subject_id = l_object_id THEN
2370 x_party_id_tbl.DELETE(2);
2371 x_party_type_tbl.DELETE(2);
2372 END IF;
2373
2374 ELSIF p_db_object_name = 'HZ_CONTACT_POINTS' THEN
2375 OPEN c_contact_point(TO_NUMBER(p_object_pk1));
2376 FETCH c_contact_point BULK COLLECT INTO x_party_id_tbl, x_party_type_tbl;
2377 CLOSE c_contact_point;
2378
2379 IF x_party_id_tbl.COUNT = 0 THEN
2380 OPEN c_contact_point_ps(TO_NUMBER(p_object_pk1));
2381 FETCH c_contact_point_ps BULK COLLECT INTO x_party_id_tbl, x_party_type_tbl;
2382 CLOSE c_contact_point_ps;
2383 END IF;
2384
2385 END IF;
2386
2387 END get_parent_party_id;
2388
2389
2390 /**
2391 * FUNCTION
2392 * get_display_name
2393 *
2394 * DESCRIPTION
2395 * return the display name of an object or an object instance set.
2396 *
2397 *
2398 * SCOPE - PUBLIC
2399 *
2400 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2401 *
2402 * ARGUMENTS : IN:
2403 * p_object_name object name
2404 * p_object_instance_name object instance name
2405 *
2406 * RETURNS : NONE
2407 *
2408 * NOTES
2409 *
2410 * MODIFICATION HISTORY -
2411 *
2412 */
2413
2414 FUNCTION get_display_name (
2415 p_object_name IN VARCHAR2,
2416 p_object_instance_name IN VARCHAR2
2417 ) RETURN VARCHAR2 IS
2418
2419 CURSOR c_objects IS
2420 SELECT display_name
2421 FROM fnd_objects_vl
2422 WHERE obj_name = p_object_name;
2423
2424 CURSOR c_object_instance_sets IS
2425 SELECT display_name
2426 FROM fnd_object_instance_sets_vl
2427 WHERE instance_set_name = p_object_instance_name;
2428
2429 l_return VARCHAR2(300);
2430
2431 BEGIN
2432
2433 IF p_object_instance_name IS NOT NULL THEN
2434 OPEN c_object_instance_sets;
2435 FETCH c_object_instance_sets INTO l_return;
2436 IF c_object_instance_sets%NOTFOUND THEN
2437 l_return := NULL;
2438 END IF;
2439 CLOSE c_object_instance_sets;
2440 ELSIF p_object_name IS NOT NULL THEN
2441 OPEN c_objects;
2442 FETCH c_objects INTO l_return;
2443 IF c_objects%NOTFOUND THEN
2444 l_return := NULL;
2445 END IF;
2446 CLOSE c_objects;
2447 END IF;
2448
2449 RETURN l_return;
2450
2451 END get_display_name;
2452
2453 END HZ_DSS_UTIL_PUB;