[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.2 2008/09/17 12:15:59 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
436
433 i := i + 1;
434 end loop;
435 close c_get_dss_groups;
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 --
637 -- find all of groups that are applicable to this entity
638 --
639 OPEN c_get_dss_groups(l_db_object_name);
640 LOOP
641
642 << next_fetch>>
643
644 l_falling_into_the_group := 'NA';
645 l_failure_reason := 'INITIAL';
646
647 FETCH c_get_dss_groups INTO
648 l_object_id, l_object_name,
649 l_instance_set_id, l_instance_set_name,
650 l_pk1_column_name, l_pk2_column_name, l_predicate,
651 l_dss_group_code, l_rank;
652 EXIT WHEN c_get_dss_groups%NOTFOUND;
653
654 --
655 -- debug messages
656 --
657 /*
658 print (
659 'object_id = '||l_object_id||' '||
660 'obj_name = '||l_object_name
661 );
662 print (
663 'instance_set_id = '||l_instance_set_id||' '||
664 'instance_set_name = '||l_instance_set_name
665 );
666 print (
667 'pk1_column_name = '||l_pk1_column_name||' '||
668 'pk2_column_name = '||l_pk2_column_name
669 );
670 print (
671 'predicate = '||l_predicate||' '||
672 'dss_group_code = '||l_dss_group_code||' '||
673 'rank = '||l_rank
674 );
675 */
676
677 --
678 -- check if the record can fall into the group if
679 -- the group secure instance entities
680 --
681
682 IF l_predicate IS NOT NULL THEN
683 BEGIN
684 l_sql := 'SELECT 1 FROM '||l_db_object_name||' '||
685 'WHERE '||l_pk1_column_name||' = :1'||' '||
686 'AND '||l_predicate;
687
688 IF l_pk2_column_name IS NULL THEN
689 --
693
690 -- debug messages
691 --
692 -- print(l_sql);
694 EXECUTE IMMEDIATE l_sql into l_dummy USING l_object_pk1;
695 ELSE
696 l_sql := l_sql||' '||
697 'AND '||l_pk2_column_name||' = :2';
698
699 --
700 -- debug messages
701 --
702 -- print(l_sql);
703
704 EXECUTE IMMEDIATE l_sql into l_dummy USING l_object_pk1, l_object_pk2;
705 END IF;
706
707 EXCEPTION
708 WHEN OTHERS THEN
709 -- print('EXCEPTION :'||SQLERRM);
710 l_failure_reason := 'INSTANCE_CHECK';
711 GOTO next_fetch;
712 END;
713 ELSE
714 --print('Predicate NULL');
715 NULL;
716 END IF;
717
718 --
719 -- check cache
720 --
721 IF l_pre_db_object_name = l_db_object_name AND
722 l_failure_reason <> 'INSTANCE_CHECK' AND
723 l_pre_dss_group_code = l_dss_group_code
724 THEN
725 --print ('l_pre_db_object_name='||l_db_object_name||':l_failure_reason='||l_failure_reason
726 -- ||'l_pre_dss_group_code='||l_pre_dss_group_code||':GOTO next_fetch');
727 GOTO next_fetch;
728 END IF;
729
730 --
731 -- check if there is any criteria defined in the dss group
732 -- no criteria means the group securing all of records.
733 -- return the group code directly.
734 --
735 OPEN c_check_criteria(l_dss_group_code);
736 FETCH c_check_criteria INTO l_dummy;
737 IF c_check_criteria%NOTFOUND THEN
738 CLOSE c_check_criteria;
739 --print('c_check_criteria%NOTFOUND, EXIT Loop, return l_returned_dss_group='||l_dss_group_code);
740 l_returned_dss_group := l_dss_group_code;
741 EXIT;
742 ELSE
743 --print('c_check_criteria%FOUND, continue');
744 NULL;
745 END IF;
746 CLOSE c_check_criteria;
747
748 --
749 -- get parent party id and type
750 --
751 get_parent_party_id(
752 p_db_object_name => l_db_object_name,
753 p_object_pk1 => l_object_pk1,
754 p_object_pk2 => l_object_pk2,
755 p_object_pk3 => l_object_pk3,
756 p_object_pk4 => l_object_pk4,
757 p_object_pk5 => l_object_pk5,
758 x_party_id_tbl => l_parent_party_id_tbl,
759 x_party_type_tbl => l_parent_party_type_tbl
760 );
761
762 --
763 -- debug messages
764 --
765 -- print('Number of parent parties: '||l_parent_party_id_tbl.COUNT);
766 /*
767 FOR i IN 1..l_parent_party_id_tbl.COUNT LOOP
768 print('party_id = '||l_parent_party_id_tbl(i)||' '||
769 'party_type = '||l_parent_party_type_tbl(i));
770 END LOOP;
771 */
772
773 --
774 -- check if it is created by module based
775 --
776 OPEN c_check_created_by_module(l_dss_group_code);
777 FETCH c_check_created_by_module INTO l_dummy;
778 IF c_check_created_by_module%NOTFOUND THEN
779 l_module_based_dsg := 'N';
780 ELSE
781 l_module_based_dsg := 'Y';
782 END IF;
783 CLOSE c_check_created_by_module;
784
785 --
786 -- debug messages
787 --
788 -- print('module_based_dsg = '||l_module_based_dsg);
789
790 IF l_module_based_dsg = 'Y' THEN
791 l_falling_into_the_group :=
792 check_created_by_module_cr (
793 p_dss_group_code => l_dss_group_code,
794 p_parent_party_id_tbl => l_parent_party_id_tbl,
795 p_parent_party_type_tbl => l_parent_party_type_tbl
796 );
797
798 IF l_falling_into_the_group = 'N' THEN
799 --print('l_falling_into_the_group = N, l_failure_reason=CREATED_BY_MODULE, GOTO next_fetch');
800 l_failure_reason := 'CREATED_BY_MODULE';
801 GOTO next_fetch;
802 ELSE
803 --print('l_falling_into_the_group = Y, continue');
804 NULL;
805 END IF;
806 END IF;
807
808 --
809 -- check if it is classification based
810 --
811 OPEN c_check_classification(l_dss_group_code);
812 FETCH c_check_classification INTO l_dummy;
813 IF c_check_classification%NOTFOUND THEN
814 l_class_based_dsg := 'N';
815 ELSE
816 l_class_based_dsg := 'Y';
817 END IF;
818 CLOSE c_check_classification;
819
820 --
821 -- debug messages
822 --
823 -- print('class_based_dsg = '||l_class_based_dsg);
824
825 IF l_class_based_dsg = 'Y' THEN
826 l_falling_into_the_group :=
827 check_classification_cr (
828 p_dss_group_code => l_dss_group_code,
829 p_parent_party_id_tbl => l_parent_party_id_tbl,
830 p_parent_party_type_tbl => l_parent_party_type_tbl
834 --print('l_falling_into_the_group = N, l_failure_reason=CLASSIFICATION, GOTO next_fetch');
831 );
832
833 IF l_falling_into_the_group = 'N' THEN
835 l_failure_reason := 'CLASSIFICATION';
836 GOTO next_fetch;
837 ELSE
838 --print('l_falling_into_the_group = Y, continue');
839 NULL;
840 END IF;
841 END IF;
842
843 --
844 -- check if it is relationship type based
845 --
846 OPEN c_check_relationship_type(l_dss_group_code);
847 FETCH c_check_relationship_type INTO l_dummy;
848 IF c_check_relationship_type%NOTFOUND THEN
849 l_rel_based_dsg := 'N';
850 ELSE
851 l_rel_based_dsg := 'Y';
852 END IF;
853 CLOSE c_check_relationship_type;
854
855 --
856 -- debug messages
857 --
858 -- print('relationship_based_dsg = '||l_rel_based_dsg);
859
860 IF l_rel_based_dsg = 'Y' THEN
861 l_falling_into_the_group :=
862 check_relationship_type_cr (
863 p_dss_group_code => l_dss_group_code,
864 p_db_object_name => l_db_object_name,
865 p_object_pk1 => l_object_pk1,
866 p_object_pk2 => l_object_pk2,
867 p_object_pk3 => l_object_pk3,
868 p_object_pk4 => l_object_pk4,
869 p_object_pk5 => l_object_pk5,
870 p_parent_party_id_tbl => l_parent_party_id_tbl,
871 p_parent_party_type_tbl => l_parent_party_type_tbl
872 );
873
874 IF l_falling_into_the_group = 'N' THEN
875 l_failure_reason := 'RELATIONSHIP_TYPE';
876 --print('l_falling_into_the_group = N, l_failure_reason=RELATIONSHIP_TYPE, GOTO next_fetch');
877 GOTO next_fetch;
878 ELSE
879 --print('l_falling_into_the_group = Y, continue');
880 NULL;
881 END IF;
882 END IF;
883
884 IF l_falling_into_the_group = 'Y' THEN
885 l_returned_dss_group := l_dss_group_code;
886 --print('l_falling_into_the_group = Y, Exit Loop l_returned_dss_group='||l_dss_group_code);
887 EXIT;
888 END IF;
889
890 l_pre_db_object_name := l_db_object_name;
891 l_pre_dss_group_code := l_dss_group_code;
892
893 --print('Finally: l_pre_db_object_name='||l_pre_db_object_name);
894 --print('Finally: l_pre_dss_group_code='||l_pre_dss_group_code);
895 END LOOP;
896 CLOSE c_get_dss_groups;
897
898 IF l_returned_dss_group IS NULL THEN
899 --print('l_returned_dss_group is NULL, so get profile value for HZ_DEFAULT_DSS_GROUP');
900 l_returned_dss_group := FND_PROFILE.VALUE('HZ_DEFAULT_DSS_GROUP');
901 END IF;
902
903 -- print('Finally Return Value l_returned_dss_group:'||l_returned_dss_group);
904 RETURN l_returned_dss_group;
905
906 END determine_dss_group;
907
908
909 /*===========================================================================+
910 | PROCEDURE
911 | assign_dss_group
912 |
913 | DESCRIPTION
914 | For a given row in a table ,assign the data sharing group
915 | Based on p_process_subentities_flag, it could be assigned to all
916 | the subentities as well
917 |
918 | SCOPE - PUBLIC
919 |
920 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
921 |
922 | ARGUMENTS : IN:
923 | p_db_object_name VARCHAR2 e.g. HZ_PARTIES
924 | p_object_pk1 VARCHAR2 e.g. 1000
925 | p_object_pk2 VARCHAR2 (if any)
926 | p_object_pk3 VARCHAR2 (if any)
927 | p_object_pk4 VARCHAR2 (if any)
928 | p_object_pk5 VARCHAR2 (if any)
929 | p_root_db_object_name VARCHAR2 name of the root entity(optional)
930 | p_root_object_pk1 VARCHAR2 Primary key value of root(optional)
931 | p_root_object_pk2 VARCHAR2 Primary key value of root(optional)
932 | p_root_object_pk3 VARCHAR2 Primary key value of root(optional)
933 | p_root_object_pk4 VARCHAR2 Primary key value of root(optional)
934 | p_root_object_pk5 VARCHAR2 Primary key value of root(optional)
935 | p_process_subentities_flag VARCHAR2 Y/N If all child entities need
936 | to be processed
937 |
938 | OUT:
939 | IN/ OUT:
940 |
941 | RETURNS : NONE
942 |
943 | NOTES
944 |
945 | MODIFICATION HISTORY -
946 | Jyoti Pandey 08-07-2002 Created.
947 |
948 +===========================================================================*/
949
950 PROCEDURE assign_dss_group(
951 p_db_object_name IN VARCHAR2,
952 p_object_pk1 IN VARCHAR2,
953 p_object_pk2 IN VARCHAR2,
954 p_object_pk3 IN VARCHAR2,
955 p_object_pk4 IN VARCHAR2,
956 p_object_pk5 IN VARCHAR2,
957 p_root_db_object_name IN VARCHAR2,
958 p_root_object_pk1 IN VARCHAR2,
959 p_root_object_pk2 IN VARCHAR2,
960 p_root_object_pk3 IN VARCHAR2,
961 p_root_object_pk4 IN VARCHAR2,
965 l_dss_assignment_rec HZ_DSS_GROUPS_PUB.dss_assignment_type;
962 p_root_object_pk5 IN VARCHAR2,
963 p_process_subentities_flag IN VARCHAR2) IS
964
966
967 ----** get pk name, fk name ,assignment method for the object name passed**----
968 CURSOR get_grp_assign_level(t_object_name IN VARCHAR2) IS
969 SELECT dse.entity_id, fo.object_id,
970 fo.pk1_column_name, fo.pk2_column_name ,
971 dse.parent_fk_column1 , dse.parent_fk_column2,
972 dse.group_assignment_level
973 FROM fnd_objects fo , hz_dss_entities dse
974 WHERE ( ( dse.object_id IN ( select object_id from fnd_objects
975 where database_object_name = t_object_name) )
976 OR
977 (dse.instance_set_id in ( select instance_set_id
978 from fnd_object_instance_sets ois
979 where ois.object_id IN
980 (select object_id from fnd_objects
981 where database_object_name = t_object_name)
982 )
983 )
984 )
985 AND fo.object_id = dse.object_id
986 AND dse.status = 'A';
987
988 l_entity_id hz_dss_entities.entity_id%type;
989 l_object_id hz_dss_entities.object_id%type;
990 l_pk1_column_name fnd_objects.pk1_column_name%type;
991 l_pk2_column_name fnd_objects.pk2_column_name%type;
992 l_parent_fk_column1 hz_dss_entities.parent_fk_column1%type;
993 l_parent_fk_column2 hz_dss_entities.parent_fk_column2%type;
994 l_group_assignment_level hz_dss_entities.group_assignment_level%type;
995 l_dsg_code hz_dss_secured_entities.dss_group_code%type;
996 l_sql varchar2(2000);
997
998
999 x_assignment_id NUMBER;
1000 x_return_status varchar2(1);
1001 x_msg_count number;
1002 x_msg_data varchar2(2000);
1003
1004 BEGIN
1005
1006 --determine if the DSG should INHERIT DIRECT ASSIGN
1007 OPEN get_grp_assign_level(p_db_object_name);
1008 LOOP
1009
1010 FETCH get_grp_assign_level INTO l_entity_id, l_object_id,
1011 l_pk1_column_name, l_pk2_column_name ,
1012 l_parent_fk_column1 , l_parent_fk_column2, l_group_assignment_level;
1013
1014 EXIT WHEN get_grp_assign_level%NOTFOUND;
1015
1016 if l_group_assignment_level = 'INHERIT' then
1017 null; ---don't do anything
1018 exit;
1019 close get_grp_assign_level;
1020 else
1021 ---Determine the DSG
1022 l_dsg_code := hz_dss_util_pub.determine_dss_group(
1023 p_db_object_name,
1024 p_object_pk1,
1025 p_object_pk2,
1026 p_object_pk3,
1027 p_object_pk4,
1028 p_object_pk5,
1029 p_root_db_object_name,
1030 p_root_object_pk1,
1031 p_root_object_pk2,
1032 p_root_object_pk3,
1033 p_root_object_pk4,
1034 p_root_object_pk5 );
1035
1036
1037 end if; ---- l_group_assignment_level = 'INHERIT'
1038
1039 if l_dsg_code is not null then
1040
1041 ---make a callout to HZ_DSS_GROUPS_PUB.create_assignment
1042 l_dss_assignment_rec.dss_group_code := l_dsg_code;
1043 l_dss_assignment_rec.assignment_id := null;
1044 l_dss_assignment_rec.owner_table_name := p_db_object_name;
1045 l_dss_assignment_rec.owner_table_id1 := p_object_pk1;
1046 l_dss_assignment_rec.owner_table_id2 := p_object_pk2;
1047 l_dss_assignment_rec.owner_table_id3 := p_object_pk3;
1048 l_dss_assignment_rec.owner_table_id4 := p_object_pk4;
1049 l_dss_assignment_rec.owner_table_id5 := p_object_pk5;
1050 l_dss_assignment_rec.status := null;
1051
1052 if l_group_assignment_level = 'ASSIGN' then
1053 HZ_DSS_GROUPS_PUB.create_assignment (
1054 'T',
1055 l_dss_assignment_rec,
1056 x_assignment_id ,
1057 x_return_status ,
1058 x_msg_count,
1059 x_msg_data);
1060
1061 elsif l_group_assignment_level = 'DIRECT' then
1062
1063 begin
1064 l_sql := ' UPDATE ' || p_db_object_name ||
1065 ' SET ' || ' dss_group_code ' || ' = :dsg ' ||
1066 ' WHERE ' || l_pk1_column_name|| ' = :pk ' ;
1067 EXECUTE IMMEDIATE l_sql USING l_dsg_code ,p_object_pk1;
1068 exception
1069 when others then
1070 raise;
1071 end ;
1072
1073 end if; ---group assignment level
1074
1075 if p_process_subentities_flag = 'Y' then
1076
1077 stamp_child_entities(
1078 p_entity_id =>l_entity_id,
1079 p_object_pk1 =>p_object_pk1,
1080 p_object_pk2 =>p_object_pk2,
1081 p_object_pk3 =>p_object_pk3,
1082 p_object_pk4 =>p_object_pk4,
1083 p_object_pk5 =>p_object_pk5) ;
1084
1085 end if;
1086
1087 end if; ---l_dsg_code
1091 END assign_dss_group;
1088 end loop;
1089 close get_grp_assign_level;
1090
1092
1093
1094 --Private Procedures
1095 PROCEDURE stamp_child_entities(p_entity_id IN NUMBER,
1096 p_object_pk1 IN VARCHAR2,
1097 p_object_pk2 IN VARCHAR2,
1098 p_object_pk3 IN VARCHAR2,
1099 p_object_pk4 IN VARCHAR2,
1100 p_object_pk5 IN VARCHAR2) IS
1101
1102 CURSOR get_child_entities(t_entity_id IN NUMBER) IS
1103 SELECT entity_id,
1104 fo.database_object_name,
1105 fo.pk1_column_name,
1106 fo.pk2_column_name ,
1107 dse.parent_entity_id , dse.parent_fk_column1 , dse.parent_fk_column2
1108 FROM fnd_objects fo , hz_dss_entities dse
1109 WHERE parent_entity_id is not null
1110 AND ( dse.object_id is not null and
1111 fo.object_id = dse.object_id )
1112 OR (dse.instance_set_id is not null and
1113 fo.object_id = ( select distinct object_id from fnd_object_instance_sets
1114 where instance_set_id = dse.instance_set_id))
1115 AND dse.parent_entity_id = t_entity_id
1116 AND dse.status = 'A'
1117 order by dse.entity_id;
1118
1119 TYPE child_pk_typ IS REF CURSOR;
1120 child_pk child_pk_typ;
1121
1122 l_child_entity_id NUMBER;
1123 l_object_id NUMBER;
1124 l_database_object_name varchar2(55);
1125 l_pk1_column_name VARCHAR2(50);
1126 l_pk2_column_name VARCHAR2(50);
1127 l_parent_entity_id NUMBER;
1128 l_parent_fk_column1 VARCHAR2(50);
1129 l_parent_fk_column2 VARCHAR2(50);
1130 l_new_pk1_value varchar2(30);
1131 l_new_pk2_value varchar2(30);
1132 l_sql varchar2(2000);
1133
1134 begin
1135
1136 OPEN get_child_entities(p_entity_id);
1137 LOOP
1138 FETCH get_child_entities INTO l_child_entity_id,
1139 l_database_object_name,
1140 l_pk1_column_name,
1141 l_pk2_column_name ,
1142 l_parent_entity_id ,
1143 l_parent_fk_column1 ,
1144 l_parent_fk_column2;
1145
1146 EXIT WHEN get_child_entities%notfound;
1147
1148 if l_parent_fk_column2 is not null then
1149 begin
1150 OPEN child_pk FOR
1151 'SELECT ' || l_pk1_column_name ||' , '||
1152 nvl(l_pk2_column_name,-1) ||
1153 ' FROM ' || l_database_object_name||
1154 ' WHERE '|| l_parent_fk_column1 || '= :id1 ' ||
1155 ' AND ' || l_parent_fk_column2 || '= :id2 ' USING p_object_pk1 , p_object_pk2;
1156
1157 LOOP
1158 FETCH child_pk INTO l_new_pk1_value, l_new_pk2_value;
1159
1160 EXIT when child_pk%notfound;
1161
1162 if l_new_pk2_value = -1 then
1163 l_new_pk2_value := null;
1164 end if;
1165
1166
1167 assign_dss_group(
1168 p_db_object_name => l_database_object_name,
1169 p_object_pk1 => l_new_pk1_value,
1170 p_object_pk2 => l_new_pk2_value,
1171 p_object_pk3 => NULL,
1172 p_object_pk4 => NULL,
1173 p_object_pk5 => NULL,
1174 p_root_db_object_name =>NULL,
1175 p_root_object_pk1 =>NULL,
1176 p_root_object_pk2 =>NULL,
1177 p_root_object_pk3 =>NULL,
1178 p_root_object_pk4 =>NULL,
1179 p_root_object_pk5 =>NULL,
1180 p_process_subentities_flag => 'Y' );
1181
1182 END LOOP;
1183 CLOSE child_pk;
1184
1185 exception when no_data_found then
1186 null;
1187 end;
1188 else
1189 begin
1190 OPEN child_pk FOR
1191 'SELECT ' || l_pk1_column_name || ',' ||
1192 nvl(l_pk2_column_name ,-1) ||
1193 ' FROM ' || l_database_object_name||
1194 ' WHERE '|| l_parent_fk_column1 || '= :id1 ' USING p_object_pk1;
1195
1196 LOOP
1197 FETCH child_pk INTO l_new_pk1_value, l_new_pk2_value;
1198
1199 EXIT when child_pk%notfound;
1200
1201 assign_dss_group(
1202 p_db_object_name => l_database_object_name,
1203 p_object_pk1 => l_new_pk1_value,
1204 p_object_pk2 => l_new_pk2_value,
1205 p_object_pk3 => NULL,
1206 p_object_pk4 => NULL,
1207 p_object_pk5 => NULL,
1208 p_root_db_object_name =>NULL,
1209 p_root_object_pk1 =>NULL,
1210 p_root_object_pk2 =>NULL,
1211 p_root_object_pk3 =>NULL,
1212 p_root_object_pk4 =>NULL,
1213 p_root_object_pk5 =>NULL,
1214 p_process_subentities_flag => 'Y' );
1215
1219
1216 if l_new_pk2_value = -1 then
1217 l_new_pk2_value := null;
1218 end if;
1220 END LOOP;
1221 CLOSE child_pk;
1222
1223 exception when no_data_found then
1224 null;
1225 end;
1226
1227 end if;
1228
1229 end loop;
1230 close get_child_entities;
1231
1232 end stamp_child_entities;
1233
1234 /*===========================================================================+
1235 | PROCEDURE
1236 | switch_context
1237 |
1238 | DESCRIPTION
1239 | For a given user , populate the temporary table HZ_DSS_GROUP_CACHE
1240 | with the Data Sharing Groups that the user has SELECT access to
1241 |
1242 | SCOPE - PUBLIC
1243 |
1244 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1245 |
1246 | ARGUMENTS :
1247 |
1248 | OUT:
1249 | IN/ OUT:
1250 |
1251 | RETURNS : NONE
1252 |
1253 | NOTES
1254 |
1255 | MODIFICATION HISTORY -
1256 | Jyoti Pandey 08-07-2002 Created.
1257 |
1258 +===========================================================================*/
1259
1260 procedure switch_context (p_user_name IN VARCHAR2,
1261 x_return_status OUT NOCOPY VARCHAR2,
1262 x_msg_count OUT NOCOPY NUMBER,
1263 x_msg_data OUT NOCOPY VARCHAR2)IS
1264
1265 x_granted_groups HZ_DSS_UTIL_PUB.dss_group_tbl_type;
1266
1267 i number;
1268 l_user_name fnd_user.user_name%type;
1269 l_return_status VARCHAR2(1);
1270 l_msg_count NUMBER;
1271 l_msg_data VARCHAR2(2000);
1272
1273 begin
1274 ---initialize the message
1275 FND_MSG_PUB.initialize;
1276
1277 --- initialize API return status to success.
1278 x_return_status := FND_API.G_RET_STS_SUCCESS;
1279
1280 l_user_name := fnd_global.user_name;
1281
1282 --clear the temporary table
1283 ---delete from HZ_DSS_GROUP_CACHE ;
1284
1285 --determine the DSG's that the user has SELECT access to
1286 HZ_DSS_UTIL_PUB.get_granted_groups (
1287 l_user_name,
1288 'SELECT',
1289 x_granted_groups,
1290 l_return_status,
1291 l_msg_count,
1292 l_msg_data);
1293
1294
1295 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
1296
1297 FOR I IN x_granted_groups.first..x_granted_groups.last
1298 loop
1299 null;
1300 -- insert into HZ_DSS_GROUP_CACHE (entity_id , dss_group_code)
1301 -- values (x_granted_groups(i).entity_id ,
1302 -- x_granted_groups(i).dss_group_code);
1303
1304 end loop;
1305 ELSE
1306 x_return_status := FND_API.G_RET_STS_ERROR;
1307 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1308 p_count => x_msg_count,
1309 p_data => x_msg_data);
1310 END IF;
1311
1312 exception when others then
1313 raise;
1314
1315 end switch_context;
1316
1317 /**
1318 * FUNCTION
1319 * generate_predicate
1320 *
1321 * DESCRIPTION
1322 *
1323 *
1324 * SCOPE - PUBLIC
1325 *
1326 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1327 *
1328 * ARGUMENTS : IN:
1329 * OUT: T/F
1330 * IN/ OUT:
1331 *
1332 * RETURNS : NONE
1333 *
1334 * NOTES
1335 *
1336 * MODIFICATION HISTORY -
1337 * Bug 2630164 changed signature to include msg_count msg data
1338 * also included validation for Data sharing group and entity_id
1339 *
1340 */
1341
1342 PROCEDURE generate_predicate(
1343 p_dss_group_code IN VARCHAR2,
1344 p_entity_id IN NUMBER,
1345 x_predicate OUT NOCOPY VARCHAR2,
1346 x_return_status OUT NOCOPY VARCHAR2,
1347 x_msg_count OUT NOCOPY NUMBER,
1348 x_msg_data OUT NOCOPY VARCHAR2
1349 ) IS
1350
1351 CURSOR c_find_objects IS
1352 SELECT obj.database_object_name ,
1353 decode(pk1_column_name , null,null, pk1_column_name) ||
1354 decode(pk2_column_name , null,null, ','|| pk2_column_name) ||
1355 decode(pk3_column_name , null,null, ','|| pk3_column_name) ||
1356 decode(pk4_column_name , null,null, ','|| pk4_column_name) ||
1357 decode(pk5_column_name , null,null, ','|| pk5_column_name)
1358 FROM fnd_objects obj,
1359 hz_dss_entities dse
1360 WHERE dse.entity_id = p_entity_id
1361 AND dse.status = 'A'
1362 AND dse.object_id IS NOT NULL
1363 AND dse.object_id = obj.object_id
1364 UNION ALL
1365 SELECT obj.database_object_name ,
1366 decode(pk1_column_name , null,null, pk1_column_name) ||
1367 decode(pk2_column_name , null,null, ','|| pk2_column_name) ||
1368 decode(pk3_column_name , null,null, ','|| pk3_column_name) ||
1369 decode(pk4_column_name , null,null, ','|| pk4_column_name) ||
1370 decode(pk5_column_name , null,null, ','|| pk5_column_name)
1374 WHERE dse.entity_id = p_entity_id
1371 FROM fnd_object_instance_sets ins,
1372 fnd_objects obj,
1373 hz_dss_entities dse
1375 AND dse.status = 'A'
1376 AND dse.instance_set_id IS NOT NULL
1377 AND dse.instance_set_id = ins.instance_set_id
1378 AND ins.object_id = obj.object_id;
1379
1380 l_string VARCHAR2(2000);
1381 l_object_name VARCHAR2(30);
1382 l_sql VARCHAR2(2000);
1383
1384 BEGIN
1385
1386 ---initialize the message
1387 FND_MSG_PUB.initialize;
1388
1389 --- initialize API return status to success.
1390 x_return_status := FND_API.G_RET_STS_SUCCESS;
1391
1392 --- validation passed in group code should be valid
1393 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_groups_b(p_dss_group_code)= 'N' THEN
1394 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_CODE_INVALID');
1395 FND_MSG_PUB.ADD;
1396 RAISE FND_API.G_EXC_ERROR;
1397 END IF;
1398
1399 -- entity id validation
1400 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_entities(p_entity_id) = 'N' THEN
1401 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_ENT_ID_INVALID');
1402 FND_MSG_PUB.ADD;
1403 RAISE FND_API.G_EXC_ERROR;
1404 END IF;
1405
1406 OPEN c_find_objects;
1407 FETCH c_find_objects INTO l_object_name, l_string;
1408 CLOSE c_find_objects;
1409
1410 ---Determine the DSG
1411 l_sql := 'hz_dss_util_pub.determine_dss_group(' ||
1412 ''''||l_object_name||'''' || ',' ||
1413 l_string ||
1414 ') = ' || ''''||p_dss_group_code || '''';
1415
1416 ------------------------------------------------------------------------
1417 ---HR's validation: check if the Data Sharing Group is HR_SHARED then it
1418 ---should pass HR's Created by module test also
1419 ---a similar check is performed in party_validate too
1420 ------------------------------------------------------------------------
1421
1422 IF p_dss_group_code = 'HR_SHARED' THEN
1423 ---get the user's module
1424 l_sql := l_sql || ' AND '||
1425 'NVL(fnd_profile.value(''HZ_CREATED_BY_MODULE''), ''-222'')' ||
1426 ' = ''HR API'' ';
1427 END IF;
1428
1429 -- Build and test the sql statement to make sure generated predicate
1430 -- is valid
1431 -- l_sql_to_test := ' select 1 from ' ||l_object_name || ' where '|| l_sql ;
1432 -- c := dbms_sql.open_cursor;
1433 -- dbms_sql.parse(c, l_sql_to_test, dbms_sql.native);
1434
1435 x_return_status := FND_API.G_RET_STS_SUCCESS;
1436 x_predicate := l_sql ;
1437
1438 EXCEPTION
1439 WHEN FND_API.G_EXC_ERROR THEN
1440 x_return_status := FND_API.G_RET_STS_ERROR;
1441
1442 FND_MSG_PUB.COUNT_AND_GET(
1443 p_encoded => fnd_api.g_false,
1444 p_count => x_msg_count,
1445 p_data => x_msg_data
1446 );
1447
1448 WHEN OTHERS THEN
1449 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1450
1451 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1452 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1453 FND_MSG_PUB.ADD;
1454
1455 FND_MSG_PUB.COUNT_AND_GET(
1456 p_encoded => fnd_api.g_false,
1457 p_count => x_msg_count,
1458 p_data => x_msg_data
1459 );
1460
1461 END generate_predicate;
1462
1463 /**
1464 * PROCEDURE
1465 * print
1466 *
1467 * DESCRIPTION
1468 *
1469 *
1470 * SCOPE - PRIVATE
1471 *
1472 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1473 *
1474 * ARGUMENTS : IN:
1475 * OUT: T/F
1476 * IN/ OUT:
1477 *
1478 * RETURNS : NONE
1479 *
1480 * NOTES
1481 *
1482 * MODIFICATION HISTORY -
1483 *
1484 */
1485
1486 PROCEDURE print (
1487 p_str IN VARCHAR2
1488 ) IS
1489
1490 j NUMBER;
1491
1492 BEGIN
1493 j := 1;
1494
1495 FOR i IN 1..CEIL(length(p_str)/255) LOOP
1496 -- dbms_output.put_line( SUBSTR( p_str, j, 255 ) );
1497 j := j + 255;
1498 END LOOP;
1499 END print;
1500
1501
1502 /**
1503 * FUNCTION
1504 * check_created_by_module_cr
1505 *
1506 * DESCRIPTION
1507 *
1508 *
1509 * SCOPE - PRIVATE
1510 *
1511 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1512 *
1513 * ARGUMENTS : IN:
1514 * OUT: T/F
1515 * IN/ OUT:
1516 *
1517 * RETURNS : NONE
1518 *
1519 * NOTES
1520 *
1521 * MODIFICATION HISTORY -
1522 *
1523 */
1524
1525 FUNCTION check_created_by_module_cr (
1526 p_dss_group_code IN VARCHAR2,
1527 p_parent_party_id_tbl IN t_number_tbl,
1528 p_parent_party_type_tbl IN t_varchar_30_tbl
1529 ) RETURN VARCHAR2 IS
1530
1531 CURSOR c_check_created_by_modules (
1532 p_party_id NUMBER,
1533 p_dss_group_code VARCHAR2
1534 ) IS
1535 SELECT 1
1539 SELECT owner_table_id2
1536 FROM hz_parties p, hz_dss_criteria dc
1537 WHERE p.party_id = p_party_id
1538 AND p.created_by_module IN (
1540 FROM hz_dss_criteria
1541 WHERE dss_group_code = p_dss_group_code
1542 AND owner_table_name = 'AR_LOOKUPS'
1543 AND owner_table_id1 = 'HZ_CREATED_BY_MODULES'
1544 AND status = 'A');
1545
1546 l_falling_into_the_group VARCHAR2(2);
1547 l_found_non_rel_party VARCHAR2(1);
1548 l_dummy NUMBER(1);
1549
1550 BEGIN
1551
1552 l_falling_into_the_group := 'N';
1553 l_found_non_rel_party := 'N';
1554
1555 FOR i IN 1..p_parent_party_id_tbl.COUNT LOOP
1556 IF p_parent_party_type_tbl(i) <> 'PARTY_RELATIONSHIP' THEN
1557 l_found_non_rel_party := 'Y';
1558
1559 OPEN c_check_created_by_modules(p_parent_party_id_tbl(i), p_dss_group_code);
1560 FETCH c_check_created_by_modules INTO l_dummy;
1561 IF c_check_created_by_modules%FOUND THEN
1562 CLOSE c_check_created_by_modules;
1563 l_falling_into_the_group := 'Y';
1564 EXIT;
1565 END IF;
1566 CLOSE c_check_created_by_modules;
1567 END IF;
1568 END LOOP;
1569
1570 IF l_found_non_rel_party = 'N' THEN
1571 l_falling_into_the_group := 'NA';
1572 END IF;
1573
1574 -- print('check_created_by_module_cr - '||l_falling_into_the_group);
1575
1576 RETURN l_falling_into_the_group;
1577
1578 END check_created_by_module_cr;
1579
1580
1581 /**
1582 * FUNCTION
1583 * check_classifications
1584 *
1585 * DESCRIPTION
1586 *
1587 *
1588 * SCOPE - PRIVATE
1589 *
1590 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1591 *
1592 * ARGUMENTS : IN:
1593 * OUT: T/F
1594 * IN/ OUT:
1595 *
1596 * RETURNS : NONE
1597 *
1598 * NOTES
1599 *
1600 * MODIFICATION HISTORY -
1601 * 17-SEP-2008 Sudhir Gokavarapu Bug 7290836: Changed l_class_code_is_used check
1602 * from N to Y for EXIT criteria.
1603 * Added 'order by' to c_sub_class_codes cursor for
1604 * performance reason. We do not want to travese
1605 * all subcodes if parnet code is securing criteria
1606 * and is assigned to the party.
1607 *
1608 */
1609
1610 FUNCTION check_classifications (
1611 p_dss_group_code IN VARCHAR2,
1612 p_party_id IN NUMBER
1613 ) RETURN VARCHAR2 IS
1614
1615 CURSOR c_check_classifications (
1616 p_party_id NUMBER,
1617 p_class_category VARCHAR2,
1618 p_class_code VARCHAR2
1619 ) IS
1620 SELECT 1
1621 FROM hz_code_assignments
1622 WHERE owner_table_name = 'HZ_PARTIES'
1623 AND owner_table_id = p_party_id
1624 AND class_category = p_class_category
1625 AND class_code = p_class_code
1626 AND SYSDATE BETWEEN
1627 NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE)
1628 AND status = 'A';
1629
1630 --
1631 -- Get the classification codes for the data sharing group
1632 --
1633 CURSOR c_class_codes_for_dsg (
1634 p_dss_group_code VARCHAR2
1635 ) IS
1636 SELECT dsc.owner_table_id1 , dsc.owner_table_id2
1637 FROM hz_dss_criteria dsc
1638 WHERE dsc.dss_group_code = p_dss_group_code
1639 AND owner_table_name = 'FND_LOOKUP_VALUES'
1640 AND status = 'A';
1641
1642 --
1643 -- get child class codes
1644 --
1645 CURSOR c_sub_class_codes (
1646 p_class_category VARCHAR2,
1647 p_class_code VARCHAR2
1648 ) IS
1649 SELECT class_code
1650 FROM hz_class_code_denorm ccd
1651 WHERE ccd.class_category = p_class_category
1652 AND INSTRB('/'||concat_class_code||'/','/'||p_class_code||'/') > 0
1653 AND LANGUAGE = userenv('LANG')
1654 ORDER BY concat_class_code; -- Bug 7290836(no need to fetch more rec if
1655 -- parent class code was assigned to party)
1656
1657 l_dummy NUMBER(1);
1658 l_class_code_is_used VARCHAR2(1);
1659 l_class_category_tbl t_varchar_30_tbl;
1660 l_class_code_tbl t_varchar_30_tbl;
1661 l_sub_class_code_tbl t_varchar_30_tbl;
1662 l_falling_into_the_group VARCHAR2(2);
1663
1664 BEGIN
1665
1666 l_falling_into_the_group := 'N';
1667
1668 OPEN c_class_codes_for_dsg(p_dss_group_code);
1669 FETCH c_class_codes_for_dsg BULK COLLECT INTO
1670 l_class_category_tbl, l_class_code_tbl;
1671 CLOSE c_class_codes_for_dsg;
1672
1673 --
1674 -- all class codes (or its sub class codes) in a dsg must
1675 -- be assigned to the party
1676 --
1677 FOR i IN 1..l_class_category_tbl.COUNT LOOP
1678 l_class_code_is_used := 'N';
1679
1680 OPEN c_sub_class_codes(l_class_category_tbl(i), l_class_code_tbl(i));
1684 FOR j IN 1..l_sub_class_code_tbl.COUNT LOOP
1681 FETCH c_sub_class_codes BULK COLLECT INTO l_sub_class_code_tbl;
1682 CLOSE c_sub_class_codes;
1683
1685 OPEN c_check_classifications(p_party_id, l_class_category_tbl(i), l_sub_class_code_tbl(j));
1686 FETCH c_check_classifications INTO l_dummy;
1687
1688 IF c_check_classifications%FOUND THEN
1689 CLOSE c_check_classifications;
1690 l_class_code_is_used := 'Y';
1691 EXIT;
1692 END IF;
1693 CLOSE c_check_classifications;
1694 END LOOP;
1695
1696 -- IF l_class_code_is_used = 'N' THEN -- Bug 7290836(Should always exist if found a match)
1697 IF l_class_code_is_used = 'Y' THEN -- Changed from N to Y
1698 EXIT;
1699 END IF;
1700 END LOOP;
1701
1702 IF l_class_code_is_used = 'Y' THEN
1703 l_falling_into_the_group := 'Y';
1704 END IF;
1705
1706 -- print('check_classifications - '||l_falling_into_the_group);
1707
1708 RETURN l_falling_into_the_group;
1709
1710 END check_classifications;
1711
1712
1713 /**
1714 * FUNCTION
1715 * check_classification_cr
1716 *
1717 * DESCRIPTION
1718 *
1719 *
1720 * SCOPE - PRIVATE
1721 *
1722 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1723 *
1724 * ARGUMENTS : IN:
1725 * OUT: T/F
1726 * IN/ OUT:
1727 *
1728 * RETURNS : NONE
1729 *
1730 * NOTES
1731 *
1732 * MODIFICATION HISTORY -
1733 *
1734 */
1735
1736 FUNCTION check_classification_cr (
1737 p_dss_group_code IN VARCHAR2,
1738 p_parent_party_id_tbl IN t_number_tbl,
1739 p_parent_party_type_tbl IN t_varchar_30_tbl
1740 ) RETURN VARCHAR2 IS
1741
1742 l_falling_into_the_group VARCHAR2(2);
1743 l_found_non_rel_party VARCHAR2(1);
1744
1745 BEGIN
1746
1747 l_falling_into_the_group := 'N';
1748 l_found_non_rel_party := 'N';
1749
1750 FOR i IN 1..p_parent_party_id_tbl.COUNT LOOP
1751 IF p_parent_party_type_tbl(i) <> 'PARTY_RELATIONSHIP' THEN
1752 l_found_non_rel_party := 'Y';
1753
1754 l_falling_into_the_group :=
1755 check_classifications(p_dss_group_code, p_parent_party_id_tbl(i));
1756
1757 IF l_falling_into_the_group = 'Y' THEN
1758 EXIT;
1759 END IF;
1760 END IF;
1761 END LOOP;
1762
1763 IF l_found_non_rel_party = 'N' THEN
1764 l_falling_into_the_group := 'NA';
1765 END IF;
1766
1767 -- print('check_classification_cr - '||l_falling_into_the_group);
1768
1769 RETURN l_falling_into_the_group;
1770
1771 END check_classification_cr;
1772
1773
1774 /**
1775 * FUNCTION
1776 * check_relationship_types
1777 *
1778 * DESCRIPTION
1779 *
1780 *
1781 * SCOPE - PRIVATE
1782 *
1783 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1784 *
1785 * ARGUMENTS : IN:
1786 * OUT: T/F
1787 * IN/ OUT:
1788 *
1789 * RETURNS : NONE
1790 *
1791 * NOTES
1792 *
1793 * MODIFICATION HISTORY -
1794 13-FEB-2007 Nishant Singhai Bug 5687869
1795 If same subject id and object id have 2 different relationships,
1796 and only 1 of them is secured, then security rules get applied
1797 either to both or none (based on randomly which record gets
1798 picked up first).
1799 For example, if 1 is updateable while other is not,
1800 without relationship id filter, behaviour is random and
1801 either both becomes updateable or both becomes non-updateable.
1802 *
1803 */
1804
1805
1806 FUNCTION check_relationship_types (
1807 p_dss_group_code IN VARCHAR2,
1808 p_party_id IN NUMBER
1809 ,p_relationship_id IN NUMBER -- Bug 5687869 (Nishant)
1810 ) RETURN VARCHAR2 IS
1811
1812 CURSOR c_check_relationship_types_p (
1813 p_party_id NUMBER,
1814 p_relationship_type_id NUMBER
1815 ,p_relationship_id NUMBER -- Bug 5687869 (Nishant)
1816 ) IS
1817 -- SELECT 1
1818 SELECT rel.relationship_type -- changed so that it is easy to debug
1819 FROM hz_relationships rel
1820 WHERE rel.subject_id = p_party_id AND
1821 rel.subject_table_name = 'HZ_PARTIES'
1822 AND rel.relationship_id = p_relationship_id -- added for Bug 5687869
1823 AND (rel.relationship_type, rel.relationship_code, rel.subject_type, rel.object_type) IN (
1824 SELECT relationship_type, forward_rel_code,
1825 subject_type, object_type
1826 FROM hz_relationship_types rt
1827 WHERE rt.relationship_type_id = p_relationship_type_id)
1828 AND SYSDATE BETWEEN
1829 NVL(start_date, SYSDATE) AND NVL(end_date, SYSDATE)
1830 AND status = 'A'
1831 AND ROWNUM = 1;
1832
1833 CURSOR c_dss_relationship_types (
1834 p_dss_group_code VARCHAR2
1835 ) IS
1836 SELECT owner_table_id1
1837 FROM hz_dss_criteria dsc
1841
1838 WHERE dsc.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
1839 AND dsc.dss_group_code = p_dss_group_code
1840 AND dsc.status = 'A';
1842 l_falling_into_the_group VARCHAR2(2);
1843 l_dummy NUMBER(1);
1844 l_dummy_varchar VARCHAR2(100);
1845 l_relationship_type_id_tbl t_number_tbl;
1846
1847 BEGIN
1848 --print('BEGIN check_relationship_types');
1849 --print('p_dss_group_code='||p_dss_group_code||', p_party_id='||p_party_id);
1850
1851 OPEN c_dss_relationship_types(p_dss_group_code);
1852 FETCH c_dss_relationship_types BULK COLLECT INTO l_relationship_type_id_tbl;
1853 CLOSE c_dss_relationship_types;
1854
1855 l_falling_into_the_group := 'Y';
1856 --print('set l_falling_into_the_group=Y');
1857
1858 FOR i IN 1..l_relationship_type_id_tbl.COUNT LOOP
1859 -- debug message
1860 --
1861 --
1862 -- print ('relationship_type_id = '||l_relationship_type_id_tbl(i));
1863
1864 OPEN c_check_relationship_types_p(
1865 -- p_party_id, l_relationship_type_id_tbl(i)); (Bug 5687869)
1866 p_party_id, l_relationship_type_id_tbl(i),p_relationship_id);
1867 FETCH c_check_relationship_types_p INTO l_dummy_varchar; --l_dummy;
1868 --print('Validated against relationship type :'||l_dummy_varchar);
1869
1870 IF c_check_relationship_types_p%NOTFOUND THEN
1871 l_falling_into_the_group := 'N';
1872 --print('c_check_relationship_types_p%NOTFOUND..l_falling_into_the_group=N.. exit');
1873 CLOSE c_check_relationship_types_p;
1874 EXIT;
1875 END IF;
1876 CLOSE c_check_relationship_types_p;
1877 END LOOP;
1878
1879 -- debug message
1880 --
1881 --print('Finally l_falling_into_the_group='||l_falling_into_the_group);
1882 --print('END check_relationship_types');
1883
1884 RETURN l_falling_into_the_group;
1885
1886 END check_relationship_types;
1887
1888
1889 /**
1890 * FUNCTION
1891 * check_relationship_type_cr
1892 *
1893 * DESCRIPTION
1894 *
1895 *
1896 * SCOPE - PRIVATE
1897 *
1898 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1899 *
1900 * ARGUMENTS : IN:
1901 * OUT: T/F
1902 * IN/ OUT:
1903 *
1904 * RETURNS : NONE
1905 *
1906 * NOTES
1907 *
1908 * MODIFICATION HISTORY -
1909 *
1910 */
1911
1912
1913 FUNCTION check_relationship_type_cr (
1914 p_dss_group_code IN VARCHAR2,
1915 p_db_object_name IN VARCHAR2,
1916 p_object_pk1 IN VARCHAR2 DEFAULT NULL,
1917 p_object_pk2 IN VARCHAR2 DEFAULT NULL,
1918 p_object_pk3 IN VARCHAR2 DEFAULT NULL,
1919 p_object_pk4 IN VARCHAR2 DEFAULT NULL,
1920 p_object_pk5 IN VARCHAR2 DEFAULT NULL,
1921 p_parent_party_id_tbl IN t_number_tbl,
1922 p_parent_party_type_tbl IN t_varchar_30_tbl
1923 ) RETURN VARCHAR2 IS
1924
1925 CURSOR c_check_relationship_types_o (
1926 p_party_id NUMBER,
1927 p_dss_group_code VARCHAR2
1928 ) IS
1929 -- SELECT 1 (Bug 5687869)
1930 SELECT rel.relationship_type
1931 FROM hz_relationships rel
1932 WHERE rel.party_id = p_party_id
1933 AND rel.relationship_id = p_object_pk1 --(Bug 5687869)
1934 AND (rel.relationship_type, rel.relationship_code, rel.subject_type, rel.object_type) IN (
1935 SELECT relationship_type, forward_rel_code,
1936 subject_type, object_type
1937 FROM hz_relationship_types rt, hz_dss_criteria dsc
1938 WHERE dsc.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
1939 AND dsc.dss_group_code = p_dss_group_code
1940 AND dsc.owner_table_id1 = rt.relationship_type_id
1941 AND dsc.status = 'A' )
1942 AND SYSDATE BETWEEN
1943 NVL(start_date, SYSDATE) AND NVL(end_date, SYSDATE)
1944 AND status = 'A';
1945
1946 l_falling_into_the_group VARCHAR2(2);
1947 l_dummy_varchar VARCHAR2(100);
1948 l_dummy NUMBER(1);
1949
1950 BEGIN
1951
1952 --
1953 -- debug message
1954 --
1955 /*
1956 print ('BEGIN check_relationship_type_cr ...');
1957 print ('p_db_object_name = '||p_db_object_name||' '||
1958 'p_object_pk1 = '||p_object_pk1);
1959 */
1960
1961 l_falling_into_the_group := 'N';
1962
1963 FOR i IN 1..p_parent_party_id_tbl.COUNT LOOP
1964 IF p_parent_party_type_tbl(i) = 'PARTY_RELATIONSHIP' THEN
1965 /* -- debug messages
1966 print('PARTY_RELATIONSHIP .. checking cursor c_check_relationship_types_o');
1967 print('CURSOR parameter p_party_id='||p_parent_party_id_tbl(i));
1968 print('CURSOR parameter p_dss_group_code='||p_dss_group_code);
1969 */
1970 OPEN c_check_relationship_types_o(
1971 p_parent_party_id_tbl(i), p_dss_group_code);
1972 FETCH c_check_relationship_types_o INTO l_dummy_varchar; --l_dummy;
1973
1977 --print('c_check_relationship_types_o FOUND..l_falling_into_the_group=Y..exit ');
1974 --print('Validated against relationship type :'||l_dummy_varchar);
1975
1976 IF c_check_relationship_types_o%FOUND THEN
1978 l_falling_into_the_group := 'Y';
1979 CLOSE c_check_relationship_types_o;
1980 EXIT;
1981 ELSE
1982 --print('c_check_relationship_types_o NOTFOUND..l_falling_into_the_group=N ');
1983 NULL;
1984 END IF;
1985 CLOSE c_check_relationship_types_o;
1986 ELSE -- not relationship party
1987 /* -- debug messages
1988 print('RELATIONSHIP_TYPE='||p_parent_party_type_tbl(i)||'..checking function check_relationship_types');
1989 print('CURSOR parameter p_party_id='||p_parent_party_id_tbl(i));
1990 print('CURSOR parameter p_dss_group_code='||p_dss_group_code);
1991 */
1992 l_falling_into_the_group :=
1993 -- check_relationship_types(p_dss_group_code, p_parent_party_id_tbl(i)); (Bug 5687869)
1994 check_relationship_types(p_dss_group_code, p_parent_party_id_tbl(i),p_object_pk1);
1995
1996 IF l_falling_into_the_group = 'Y' THEN
1997 --print('l_falling_into_the_group=Y..exit');
1998 EXIT;
1999 END IF;
2000 END IF;
2001 END LOOP;
2002
2003 --print('Finally check_relationship_type_cr - '||l_falling_into_the_group);
2004 --print('END check_relationship_type_cr');
2005
2006 RETURN l_falling_into_the_group;
2007
2008 END check_relationship_type_cr;
2009
2010
2011 /**
2012 * FUNCTION
2013 * is_relationship_party
2014 *
2015 * DESCRIPTION
2016 *
2017 *
2018 * SCOPE - PRIVATE
2019 *
2020 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2021 *
2022 * ARGUMENTS : IN:
2023 * OUT: T/F
2024 * IN/ OUT:
2025 *
2026 * RETURNS : NONE
2027 *
2028 * NOTES
2029 *
2030 * MODIFICATION HISTORY -
2031 *
2032 */
2033
2034 FUNCTION is_relationship_party (
2035 p_party_id IN NUMBER,
2036 x_relationship_id OUT NOCOPY NUMBER
2037 ) RETURN VARCHAR2 IS
2038
2039 CURSOR c_party (
2040 p_party_id NUMBER
2041 ) IS
2042 SELECT party_type
2043 FROM hz_parties
2044 WHERE party_id = p_party_id;
2045
2046 CURSOR c_relationship_party (
2047 p_party_id NUMBER
2048 ) IS
2049 SELECT relationship_id
2050 FROM hz_relationships
2051 WHERE party_id = p_party_id
2052 AND directional_flag = 'F';
2053
2054 l_party_type VARCHAR2(30);
2055 l_is_relationship_party VARCHAR2(1);
2056
2057 BEGIN
2058
2059 l_is_relationship_party := 'N';
2060
2061 OPEN c_party(p_party_id);
2062 FETCH c_party INTO l_party_type;
2063 CLOSE c_party;
2064
2065 IF l_party_type IS NOT NULL THEN
2066 IF l_party_type <> 'PARTY_RELATIONSHIP' THEN
2067 l_is_relationship_party := 'N';
2068 ELSE
2069 l_is_relationship_party := 'Y';
2070
2071 OPEN c_relationship_party(p_party_id);
2072 FETCH c_relationship_party INTO x_relationship_id;
2073 CLOSE c_relationship_party;
2074 END IF;
2075 END IF;
2076
2077 RETURN l_is_relationship_party;
2078
2079 END is_relationship_party;
2080
2081
2082 /**
2083 * PROCEDURE
2084 * get_parent_party_id
2085 *
2086 * DESCRIPTION
2087 *
2088 *
2089 * SCOPE - PRIVATE
2090 *
2091 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2092 *
2093 * ARGUMENTS : IN:
2094 * OUT: T/F
2095 * IN/ OUT:
2096 *
2097 * RETURNS : NONE
2098 *
2099 * NOTES
2100 *
2101 * MODIFICATION HISTORY -
2102 *
2103 */
2104
2105 PROCEDURE get_parent_party_id (
2106 p_db_object_name IN VARCHAR2,
2107 p_object_pk1 IN VARCHAR2 DEFAULT NULL,
2108 p_object_pk2 IN VARCHAR2 DEFAULT NULL,
2109 p_object_pk3 IN VARCHAR2 DEFAULT NULL,
2110 p_object_pk4 IN VARCHAR2 DEFAULT NULL,
2111 p_object_pk5 IN VARCHAR2 DEFAULT NULL,
2112 x_party_id_tbl OUT NOCOPY t_number_tbl,
2113 x_party_type_tbl OUT NOCOPY t_varchar_30_tbl
2114 ) IS
2115
2116 CURSOR c_party (
2117 p_party_id NUMBER
2118 ) IS
2119 SELECT party_id, party_type
2120 FROM hz_parties
2121 WHERE party_id = p_party_id;
2122
2123 CURSOR c_party_site (
2124 p_party_site_id NUMBER
2125 ) IS
2126 SELECT p.party_id, p.party_type
2127 FROM hz_party_sites ps, hz_parties p
2128 WHERE party_site_id = p_party_site_id
2129 AND ps.party_id = p.party_id;
2130
2131 CURSOR c_location (
2132 p_location_id NUMBER
2133 ) IS
2134 SELECT p.party_id, p.party_type
2135 FROM hz_locations loc, hz_party_sites ps, hz_parties p
2139
2136 WHERE loc.location_id = p_location_id
2137 AND loc.location_id = ps.location_id
2138 AND ps.party_id = p.party_id;
2140 CURSOR c_code_assignment (
2141 p_code_assignment_id NUMBER
2142 ) IS
2143 SELECT p.party_id, p.party_type
2144 FROM hz_code_assignments, hz_parties p
2145 WHERE code_assignment_id = p_code_assignment_id
2146 AND owner_table_name = 'HZ_PARTIES'
2147 AND owner_table_id = p.party_id;
2148
2149 CURSOR c_relationship (
2150 p_relationship_id NUMBER,
2151 p_directional_flag VARCHAR2
2152 ) IS
2153 SELECT subject_id, subject_table_name, subject_type,
2154 object_id, object_table_name, object_type
2155 FROM hz_relationships
2156 WHERE relationship_id = p_relationship_id
2157 AND directional_flag = p_directional_flag
2158 AND (subject_table_name = 'HZ_PARTIES' OR
2159 object_table_name = 'HZ_PARTIES');
2160
2161 CURSOR c_contact_point (
2162 p_contact_point_id NUMBER
2163 ) IS
2164 SELECT p.party_id, p.party_type
2165 FROM hz_contact_points, hz_parties p
2166 WHERE contact_point_id = p_contact_point_id
2167 AND owner_table_name = 'HZ_PARTIES'
2168 AND owner_table_id = p.party_id;
2169
2170 CURSOR c_contact_point_ps (
2171 p_contact_point_id NUMBER
2172 ) IS
2173 SELECT p.party_id, p.party_type
2174 FROM hz_contact_points cp, hz_party_sites ps, hz_parties p
2175 WHERE contact_point_id = p_contact_point_id
2176 AND owner_table_name = 'HZ_PARTY_SITES'
2177 AND owner_table_id = ps.party_site_id
2178 AND ps.party_id = p.party_id;
2179
2180 i NUMBER;
2181 l_subject_id NUMBER;
2182 l_subject_table_name VARCHAR2(30);
2183 l_subject_type VARCHAR2(30);
2184 l_object_id NUMBER;
2185 l_object_table_name VARCHAR2(30);
2186 l_object_type VARCHAR2(30);
2187
2188 BEGIN
2189
2190 IF p_db_object_name = 'HZ_PARTIES' THEN
2191 OPEN c_party(TO_NUMBER(p_object_pk1));
2192 FETCH c_party BULK COLLECT INTO x_party_id_tbl, x_party_type_tbl;
2193 CLOSE c_party;
2194
2195 ELSIF p_db_object_name = 'HZ_PARTY_SITES' THEN
2196 OPEN c_party_site(TO_NUMBER(p_object_pk1));
2197 FETCH c_party_site BULK COLLECT INTO x_party_id_tbl, x_party_type_tbl;
2198 CLOSE c_party_site;
2199
2200 ELSIF p_db_object_name = 'HZ_LOCATIONS' THEN
2201 OPEN c_location(TO_NUMBER(p_object_pk1));
2202 FETCH c_location BULK COLLECT INTO x_party_id_tbl, x_party_type_tbl;
2203 CLOSE c_location;
2204
2205 ELSIF p_db_object_name = 'HZ_CODE_ASSIGNMENTS' THEN
2206 OPEN c_code_assignment(TO_NUMBER(p_object_pk1));
2207 FETCH c_code_assignment BULK COLLECT INTO x_party_id_tbl, x_party_type_tbl;
2208 CLOSE c_code_assignment;
2209
2210 ELSIF p_db_object_name = 'HZ_RELATIONSHIPS' THEN
2211 OPEN c_relationship(TO_NUMBER(p_object_pk1), p_object_pk2);
2212 FETCH c_relationship INTO
2213 l_subject_id, l_subject_table_name, l_subject_type,
2214 l_object_id, l_object_table_name, l_object_type;
2215 CLOSE c_relationship;
2216
2217 i := 1;
2218 IF l_subject_table_name = 'HZ_PARTIES' THEN
2219 x_party_id_tbl(i) := l_subject_id;
2220 x_party_type_tbl(i) := l_subject_type;
2221 i := i+1;
2222 END IF;
2223 IF l_object_table_name = 'HZ_PARTIES' THEN
2224 x_party_id_tbl(i) := l_object_id;
2225 x_party_type_tbl(i) := l_object_type;
2226 i := i+1;
2227 END IF;
2228
2229 IF i = 3 AND l_subject_id = l_object_id THEN
2230 x_party_id_tbl.DELETE(2);
2231 x_party_type_tbl.DELETE(2);
2232 END IF;
2233
2234 ELSIF p_db_object_name = 'HZ_CONTACT_POINTS' THEN
2235 OPEN c_contact_point(TO_NUMBER(p_object_pk1));
2236 FETCH c_contact_point BULK COLLECT INTO x_party_id_tbl, x_party_type_tbl;
2237 CLOSE c_contact_point;
2238
2239 IF x_party_id_tbl.COUNT = 0 THEN
2240 OPEN c_contact_point_ps(TO_NUMBER(p_object_pk1));
2241 FETCH c_contact_point_ps BULK COLLECT INTO x_party_id_tbl, x_party_type_tbl;
2242 CLOSE c_contact_point_ps;
2243 END IF;
2244
2245 END IF;
2246
2247 END get_parent_party_id;
2248
2249
2250 /**
2251 * FUNCTION
2252 * get_display_name
2253 *
2254 * DESCRIPTION
2255 * return the display name of an object or an object instance set.
2256 *
2257 *
2258 * SCOPE - PUBLIC
2259 *
2260 * EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2261 *
2262 * ARGUMENTS : IN:
2263 * p_object_name object name
2264 * p_object_instance_name object instance name
2265 *
2266 * RETURNS : NONE
2267 *
2268 * NOTES
2269 *
2270 * MODIFICATION HISTORY -
2271 *
2272 */
2273
2274 FUNCTION get_display_name (
2275 p_object_name IN VARCHAR2,
2276 p_object_instance_name IN VARCHAR2
2277 ) RETURN VARCHAR2 IS
2278
2279 CURSOR c_objects IS
2280 SELECT display_name
2281 FROM fnd_objects_vl
2282 WHERE obj_name = p_object_name;
2283
2284 CURSOR c_object_instance_sets IS
2285 SELECT display_name
2286 FROM fnd_object_instance_sets_vl
2287 WHERE instance_set_name = p_object_instance_name;
2288
2289 l_return VARCHAR2(300);
2290
2291 BEGIN
2292
2293 IF p_object_instance_name IS NOT NULL THEN
2294 OPEN c_object_instance_sets;
2295 FETCH c_object_instance_sets INTO l_return;
2296 IF c_object_instance_sets%NOTFOUND THEN
2297 l_return := NULL;
2298 END IF;
2299 CLOSE c_object_instance_sets;
2300 ELSIF p_object_name IS NOT NULL THEN
2301 OPEN c_objects;
2302 FETCH c_objects INTO l_return;
2303 IF c_objects%NOTFOUND THEN
2304 l_return := NULL;
2305 END IF;
2306 CLOSE c_objects;
2307 END IF;
2308
2309 RETURN l_return;
2310
2311 END get_display_name;
2312
2313 END HZ_DSS_UTIL_PUB;