1 PACKAGE BODY AMW_SETUP_RISK_TYPES_PVT as
2 /* $Header: amwvrtpb.pls 120.1 2006/04/17 07:43:39 appldev noship $ */
3
4 -- ===============================================================
5 -- Package name
6 -- AMW_SETUP_RISK_TYPES_PVT
7 -- Purpose
8 -- for handling setup risk type actions
9 --
10 -- History
11 -- 07/14/2004 tsho Creates
12 -- ===============================================================
13
14
15 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMW_SETUP_RISK_TYPES_PVT';
16 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwvrtpb.pls';
17
18 -- ===============================================================
19 -- Procedure name
20 -- Reassign_Risk_Type
21 -- Purpose
22 -- Reassign specified risk type to other parent risk type.
23 -- ===============================================================
24 PROCEDURE Reassign_Risk_Type(
25 p_setup_risk_type_id IN NUMBER,
26 p_parent_setup_risk_type_id IN NUMBER
27 )IS
28 BEGIN
29 null;
30 END Reassign_Risk_Type;
31
32
33 -- ===============================================================
34 -- Procedure name
35 -- Delete_Risk_Types
36 -- Purpose
37 -- Delete specified risk type and its descendant.
38 -- Delete associations records in AMW_COMPLIANCE_ENV_ASSOCS
39 -- for the specified risk type and its descendant.
40 -- ===============================================================
41 PROCEDURE Delete_Risk_Types(
42 p_setup_risk_type_id IN NUMBER,
43 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
44 p_commit IN VARCHAR2 := FND_API.G_FALSE,
45 p_validate_only IN VARCHAR2 := FND_API.G_FALSE,
46 x_return_status OUT NOCOPY VARCHAR2,
47 x_msg_count OUT NOCOPY NUMBER,
48 x_msg_data OUT NOCOPY VARCHAR2
49 )IS
50 l_setup_risk_type_id NUMBER;
51
52 -- store the target setup risk types
53 l_risk_type_list G_NUMBER_TABLE;
54
55 i NUMBER;
56
57 BEGIN
58 -- create savepoint if p_commit is true
59 IF p_commit = FND_API.G_TRUE THEN
60 SAVEPOINT Delete_Risk_Types_Save;
61 END IF;
62
63 -- initialize message list if p_init_msg_list is set to true
64 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
65 fnd_msg_pub.initialize;
66 end if;
67
68 l_setup_risk_type_id := p_setup_risk_type_id;
69 l_risk_type_list := GET_ALL_DESCENDANTS(l_setup_risk_type_id);
70
71 IF (l_risk_type_list.FIRST is not NULL) THEN
72 FOR i in 0 .. l_risk_type_list.LAST LOOP
73 delete from AMW_SETUP_RISK_TYPES_B
74 where SETUP_RISK_TYPE_ID = l_risk_type_list(i);
75 END LOOP; -- end of for
76
77 FOR i in 0 .. l_risk_type_list.LAST LOOP
78 delete from AMW_SETUP_RISK_TYPES_TL
79 where SETUP_RISK_TYPE_ID = l_risk_type_list(i)
80 and SETUP_RISK_TYPE_ID not in (
81 select SETUP_RISK_TYPE_ID from AMW_SETUP_RISK_TYPES_B
82 )
83 and SETUP_RISK_TYPE_ID not in (
84 select PARENT_SETUP_RISK_TYPE_ID from AMW_SETUP_RISK_TYPES_B
85 where parent_setup_risk_type_id is not null
86 );
87 END LOOP; -- end of for
88
89 FOR i in 0 .. l_risk_type_list.LAST LOOP
90 delete from AMW_RISK_TYPE
91 where RISK_TYPE_CODE = (
92 select RISK_TYPE_CODE
93 from AMW_SETUP_RISK_TYPES_B
94 where SETUP_RISK_TYPE_ID = l_risk_type_list(i)
95 );
96 END LOOP; -- end of for
97
98 END IF; -- end of if: l_risk_type_list
99
100 EXCEPTION
101 WHEN FND_API.G_EXC_ERROR THEN
102 IF p_commit = FND_API.G_TRUE THEN
103 ROLLBACK TO Delete_Risk_Types_Save;
104 END IF;
105
106 x_return_status := FND_API.G_RET_STS_ERROR;
107
108 fnd_msg_pub.count_and_get(p_count => x_msg_count,
109 p_data => x_msg_data);
110
111 WHEN OTHERS THEN
112 IF p_commit = FND_API.G_TRUE THEN
113 ROLLBACK TO Delete_Risk_Types_Save;
114 END IF;
115
116 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
117
118 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AMW_SETUP_RISK_TYPES_PVT',
119 p_procedure_name => 'Delete_Risk_Types',
120 p_error_text => SUBSTRB(SQLERRM,1,240));
121
122 fnd_msg_pub.count_and_get(p_count => x_msg_count,
123 p_data => x_msg_data);
124
125 END Delete_Risk_Types;
126
127
128 -- ===============================================================
129 -- Procedure name
130 -- InValidate_Risk_Types
131 -- Purpose
132 -- InValidate(End-Date) specified risk type and its descendants.
133 -- Notes
134 -- Should update those descendant's end_date to be the same as its end_date
135 -- if the descendant's end_date is null or end_date is later than its end_date.
136 -- At any point of time, child's(descendant) end_date cannot be later than
137 -- parent's end_date.(aka, if parent risk type is invalid, so is all its descendants).
138 -- ===============================================================
139 PROCEDURE InValidate_Risk_Types(
140 p_setup_risk_type_id IN NUMBER,
141 p_end_date IN DATE,
142 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
143 p_commit IN VARCHAR2 := FND_API.G_FALSE,
144 p_validate_only IN VARCHAR2 := FND_API.G_FALSE,
145 x_return_status OUT NOCOPY VARCHAR2,
146 x_msg_count OUT NOCOPY NUMBER,
147 x_msg_data OUT NOCOPY VARCHAR2
148 ) IS
149
150 l_setup_risk_type_id NUMBER;
151
152 -- store the target setup risk types
153 l_risk_type_list G_NUMBER_TABLE;
154
155 i NUMBER;
156
157 BEGIN
158 -- create savepoint if p_commit is true
159 IF p_commit = FND_API.G_TRUE THEN
160 SAVEPOINT InValidate_Risk_Types_Save;
161 END IF;
162
163 -- initialize message list if p_init_msg_list is set to true
164 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
165 fnd_msg_pub.initialize;
166 end if;
167
168 -- initialize return status to success
169 x_return_status := fnd_api.g_ret_sts_success;
170
171
172 l_setup_risk_type_id := p_setup_risk_type_id;
173 l_risk_type_list := GET_ALL_DESCENDANTS(l_setup_risk_type_id);
174
175 IF (l_risk_type_list.FIRST is not NULL) THEN
176 -- update its end_date
177 update AMW_SETUP_RISK_TYPES_B
178 set END_DATE = p_end_date
179 where SETUP_RISK_TYPE_ID = l_risk_type_list(0);
180
181 -- should update those descendant's end_date to be the same as its end_date
182 -- if the descendant's end_date is null or end_date is later than its end_date
183 FOR i in 1 .. l_risk_type_list.LAST LOOP
184 --DBMS_OUTPUT.PUT_LINE(' l_risk_type_list('||i||') = '||l_risk_type_list(i));
185 update AMW_SETUP_RISK_TYPES_B
186 set END_DATE = p_end_date
187 where SETUP_RISK_TYPE_ID = l_risk_type_list(i)
188 and (END_DATE IS NULL OR END_DATE > p_end_date);
189 END LOOP; -- end of for
190 END IF; -- end of if: l_risk_type_list
191
192 EXCEPTION
193 WHEN FND_API.G_EXC_ERROR THEN
194 IF p_commit = FND_API.G_TRUE THEN
195 ROLLBACK TO InValidate_Risk_Types_Save;
196 END IF;
197
198 x_return_status := FND_API.G_RET_STS_ERROR;
199
200 fnd_msg_pub.count_and_get(p_count => x_msg_count,
201 p_data => x_msg_data);
202
203 WHEN OTHERS THEN
204 IF p_commit = FND_API.G_TRUE THEN
205 ROLLBACK TO InValidate_Risk_Types_Save;
206 END IF;
207
208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
209
210 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AMW_SETUP_RISK_TYPES_PVT',
211 p_procedure_name => 'InValidate_Risk_Types',
212 p_error_text => SUBSTRB(SQLERRM,1,240));
213
214 fnd_msg_pub.count_and_get(p_count => x_msg_count,
215 p_data => x_msg_data);
216
217 END InValidate_Risk_Types;
218
219
220 -- ===============================================================
221 -- Function name
222 -- RISK_TYPE_PRESENT
223 -- Purpose
224 -- return non translated character (Y/N) to indicate the
225 -- selected(associated) Setup Risk Type to specified RiskRevId
226 -- ===============================================================
227 FUNCTION RISK_TYPE_PRESENT (
228 p_risk_rev_id IN NUMBER,
229 p_risk_type_code IN VARCHAR2
230 ) RETURN VARCHAR2 IS
231
232 n number;
233 BEGIN
234 select count(*)
235 into n
236 from amw_risk_type
237 where risk_rev_id = p_risk_rev_id
238 and risk_type_code = p_risk_type_code;
239
240 if n > 0 then
241 return 'Y';
242 else
243 return 'N';
244 end if;
245 END RISK_TYPE_PRESENT;
246
247
248 -- ===============================================================
249 -- Function name
250 -- RISK_TYPE_PRESENT_MEAN
251 -- Purpose
252 -- return translated meaning (Yes/No) to indicate the
253 -- selected(associated) Setup Risk Type to specified RiskRevId
254 -- ===============================================================
255 FUNCTION RISK_TYPE_PRESENT_MEAN (
256 p_risk_rev_id IN NUMBER,
257 p_risk_type_code IN VARCHAR2
258 ) RETURN VARCHAR2 IS
259
260 n number;
261 yes varchar2(80);
262 no varchar2(80);
263 BEGIN
264 select count(*)
265 into n
266 from amw_risk_type
267 where risk_rev_id = p_risk_rev_id
268 and risk_type_code = p_risk_type_code;
269
270 select meaning
271 into yes
272 from fnd_lookups
273 where lookup_type='YES_NO'
274 and lookup_code='Y';
275
276 select meaning
277 into no
278 from fnd_lookups
279 where lookup_type='YES_NO'
280 and lookup_code='N';
281
282 if n > 0 then
283 return yes;
284 else
285 return no;
286 end if;
287 END RISK_TYPE_PRESENT_MEAN;
288
289
290 -- ===============================================================
291 -- Function name
292 -- IS_SELF_DESCENDANT_ASSOC_TO_RISK
293 -- Purpose
294 -- return non translated character (Y/N) to indicate if the
295 -- Setup Risk Type or at least one of its descendants are
296 -- associated to specified RiskRevId under specfied compliance
297 -- ===============================================================
298 FUNCTION IS_DESCENDANT_ASSOC_TO_RISK (
299 p_risk_rev_id IN NUMBER,
300 p_setup_risk_type_id IN NUMBER,
301 p_compliance_env_id IN NUMBER
302 ) RETURN VARCHAR2 IS
303
304 l_setup_risk_type_id NUMBER;
305 l_risk_rev_id NUMBER;
306 l_compliance_env_id NUMBER;
307
308 -- store the setup risk types
309 l_risk_type_list G_NUMBER_TABLE;
310
311 i NUMBER;
312 l_dummy NUMBER;
313 isDescendantAssociatedToRisk VARCHAR2(1);
314
315 -- find if specified risk type or any of its descendants are associated with the specified risk_rev_id
316 cursor is_assoc_risk_c (l_setup_risk_type_id IN NUMBER, l_risk_rev_id IN NUMBER, l_compliance_env_id IN NUMBER) is
317 SELECT assoc.risk_type_id
318 from amw_risk_type assoc,
319 amw_setup_risk_types_b rt,
320 amw_compliance_env_assocs compEnv
321 where assoc.risk_rev_id = l_risk_rev_id
322 and rt.setup_risk_type_id = l_setup_risk_type_id
323 and assoc.risk_type_code = rt.risk_type_code
324 and compEnv.object_type = 'SETUP_RISK_TYPE'
325 and compEnv.pk1 = l_setup_risk_type_id
326 and compEnv.compliance_env_id = l_compliance_env_id;
327
328 BEGIN
329
330 isDescendantAssociatedToRisk := 'N';
331 l_setup_risk_type_id := p_setup_risk_type_id;
332 l_risk_rev_id := p_risk_rev_id;
333 l_compliance_env_id := p_compliance_env_id;
334 l_risk_type_list := GET_ALL_DESCENDANTS(l_setup_risk_type_id);
335
336 IF (l_risk_type_list.FIRST is not NULL) THEN
337 FOR i in 0 .. l_risk_type_list.LAST LOOP
338 OPEN is_assoc_risk_c (l_risk_type_list(i), l_risk_rev_id, l_compliance_env_id);
339 FETCH is_assoc_risk_c INTO l_dummy;
340 CLOSE is_assoc_risk_c;
341
342 IF (l_dummy is not NULL) THEN
343 isDescendantAssociatedToRisk := 'Y';
344 EXIT;
345 END IF;
346
347 END LOOP;
348 END IF;
349
350 return isDescendantAssociatedToRisk;
351
352 END IS_DESCENDANT_ASSOC_TO_RISK;
353
354
355
356 -- ===============================================================
357 -- Procedure name
358 -- PROCESS_RISK_TYPE_ASSOCS
359 -- Purpose
360 -- Update the risk-riskTypes associations(store in table AMW_RISK_TYPE)
361 -- depending on the specified p_select_flag .
362 -- ===============================================================
366 p_validate_only IN VARCHAR2 := FND_API.G_FALSE,
363 PROCEDURE PROCESS_RISK_TYPE_ASSOCS (
364 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
365 p_commit IN VARCHAR2 := FND_API.G_FALSE,
367 p_select_flag IN VARCHAR2,
368 p_risk_rev_id IN NUMBER,
369 p_risk_type_code IN VARCHAR2,
370 x_return_status OUT NOCOPY VARCHAR2,
371 x_msg_count OUT NOCOPY NUMBER,
372 x_msg_data OUT NOCOPY VARCHAR2
373 )IS
374
375 l_creation_date date;
376 l_created_by number;
377 l_last_update_date date;
378 l_last_updated_by number;
379 l_last_update_login number;
380 l_risk_type_assoc_id number;
381 l_object_version_number number;
382
383 BEGIN
384
385 -- create savepoint if p_commit is true
386 IF p_commit = FND_API.G_TRUE THEN
387 SAVEPOINT setup_risk_type_assoc_save;
388 END IF;
389
390 -- initialize message list if p_init_msg_list is set to true
391 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
392 fnd_msg_pub.initialize;
393 end if;
394
395 -- initialize return status to success
396 x_return_status := fnd_api.g_ret_sts_success;
397
398 delete from amw_risk_type
399 where risk_rev_id = p_risk_rev_id
400 and risk_type_code = p_risk_type_code;
401
402 if (p_select_flag = 'Y') then
403 l_creation_date := SYSDATE;
404 l_created_by := FND_GLOBAL.USER_ID;
405 l_last_update_date := SYSDATE;
406 l_last_updated_by := FND_GLOBAL.USER_ID;
407 l_last_update_login := FND_GLOBAL.USER_ID;
408 l_object_version_number := 1;
409
410 select amw_risk_type_s.nextval into l_risk_type_assoc_id from dual;
411
412 insert into amw_risk_type (risk_type_id,
413 risk_rev_id,
414 risk_type_code,
415 creation_date,
416 created_by,
417 last_update_date,
418 last_updated_by,
419 last_update_login,
420 object_version_number)
421 values (l_risk_type_assoc_id,
422 p_risk_rev_id,
423 p_risk_type_code,
424 l_creation_date,
425 l_created_by,
426 l_last_update_date,
427 l_last_updated_by,
428 l_last_update_login,
429 l_object_version_number);
430
431 end if;
432 EXCEPTION
433 WHEN FND_API.G_EXC_ERROR THEN
434 IF p_commit = FND_API.G_TRUE THEN
435 ROLLBACK TO setup_risk_type_assoc_save;
436 END IF;
437
438 x_return_status := FND_API.G_RET_STS_ERROR;
439
440 fnd_msg_pub.count_and_get(p_count => x_msg_count,
441 p_data => x_msg_data);
442
443 WHEN OTHERS THEN
444 IF p_commit = FND_API.G_TRUE THEN
445 ROLLBACK TO setup_risk_type_assoc_save;
446 END IF;
447
448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
449
450 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AMW_SETUP_RISK_TYPES_PVT',
451 p_procedure_name => 'PROCESS_RISK_TYPE_ASSOCS',
452 p_error_text => SUBSTRB(SQLERRM,1,240));
453
454 fnd_msg_pub.count_and_get(p_count => x_msg_count,
455 p_data => x_msg_data);
456
457 END PROCESS_RISK_TYPE_ASSOCS;
458
459
460 -- ===============================================================
461 -- Function name
462 -- GET_ALL_DESCENDANTS
463 -- Purpose
464 -- to get all the descendants of specified risk type
465 -- ===============================================================
466 FUNCTION GET_ALL_DESCENDANTS (
467 p_setup_risk_type_id IN NUMBER
468 ) RETURN G_NUMBER_TABLE IS
469
470 l_setup_risk_type_id NUMBER;
471
472 -- store the target setup risk types
473 risk_type_list G_NUMBER_TABLE;
474 risk_type_list_cur PLS_INTEGER;
475 risk_type_list_size PLS_INTEGER;
476 tbl_risk_type_id G_NUMBER_TABLE;
477
478 -- find the direct children of specified setup risk type
479 cursor get_child_risk_type_c (l_setup_risk_type_id IN NUMBER) is
480 SELECT SETUP_RISK_TYPE_ID
481 from amw_setup_risk_types_b
482 where PARENT_SETUP_RISK_TYPE_ID = l_setup_risk_type_id;
483
484 -- find how many direct children of specified setup risk type
485 cursor get_child_count_c (l_setup_risk_type_id IN NUMBER) is
486 SELECT count(*)
487 from amw_setup_risk_types_b
488 where PARENT_SETUP_RISK_TYPE_ID = l_setup_risk_type_id;
489
490 last_index PLS_INTEGER;
491 i NUMBER;
492 n NUMBER;
493
494
495 BEGIN
496
497 risk_type_list_cur := 0;
498 risk_type_list_size := 1;
499 risk_type_list(0) := p_setup_risk_type_id;
500
501 WHILE (risk_type_list_cur < risk_type_list_size) LOOP
502 l_setup_risk_type_id := risk_type_list(risk_type_list_cur);
503
504 OPEN get_child_risk_type_c (l_setup_risk_type_id);
505 FETCH get_child_risk_type_c BULK COLLECT INTO tbl_risk_type_id;
506 CLOSE get_child_risk_type_c;
507
511 last_index := 0;
508 BEGIN
509 -- see if we found out any children
510 IF (tbl_risk_type_id.FIRST is NULL) THEN
512 ELSE
513 last_index := tbl_risk_type_id.LAST;
514 END IF;
515 EXCEPTION
516 WHEN others THEN
517 last_index := 0;
518 END;
519
520
521 FOR i in 1 .. last_index LOOP
522 risk_type_list(risk_type_list_size) := tbl_risk_type_id(i);
523 --DBMS_OUTPUT.PUT_LINE(' put into risk_type_list => '||tbl_risk_type_id(i));
524 risk_type_list_size := risk_type_list_size + 1;
525 END LOOP; -- end of for: last_index
526
527 -- advance to next risk type on working list
528 risk_type_list_cur := risk_type_list_cur + 1;
529
530 END LOOP; -- end of while: risk_type_list_cur
531
532 RETURN risk_type_list;
533
534 END GET_ALL_DESCENDANTS;
535
536
537 -- ===============================================================
538 -- Function name
539 -- IS_DESCENDANT
540 -- Purpose
541 -- return 'Y' if the passed-in p_target_setup_risk_type is the descendants
542 -- of specified risk type (p_setup_risk_type_id)
543 -- Notes
544 -- one is not oneself's descendant
545 -- aka, if p_target_setup_risk_type_id == p_setup_risk_type_id,
546 -- the return value is 'N'.
547 -- ===============================================================
548 FUNCTION IS_DESCENDANT (
549 p_target_setup_risk_type_id IN NUMBER,
550 p_setup_risk_type_id IN NUMBER
551 ) RETURN VARCHAR2 IS
552
553 l_setup_risk_type_id NUMBER;
554
555 -- store the setup risk types
556 l_risk_type_list G_NUMBER_TABLE;
557
558 i NUMBER;
559 isDescendant VARCHAR2(1);
560
561 BEGIN
562 isDescendant := 'N';
563 l_setup_risk_type_id := p_setup_risk_type_id;
564 l_risk_type_list := GET_ALL_DESCENDANTS(l_setup_risk_type_id);
565
566 IF (l_risk_type_list.FIRST is not NULL) THEN
567 FOR i in 1 .. l_risk_type_list.LAST LOOP
568 IF (l_risk_type_list(i) = p_target_setup_risk_type_id) THEN
569 isDescendant := 'Y';
570 EXIT;
571 END IF;
572 END LOOP;
573 END IF;
574
575 return isDescendant;
576
577 END IS_DESCENDANT;
578
579
580 -- ===============================================================
581 -- Function name
582 -- IS_PARENT
583 -- Purpose
584 -- return 'Y' if the passed-in p_target_setup_risk_type is the direct parent
585 -- of specified risk type (p_setup_risk_type_id)
586 -- ===============================================================
587 FUNCTION IS_PARENT (
588 p_target_setup_risk_type_id IN NUMBER,
589 p_setup_risk_type_id IN NUMBER
590 ) RETURN VARCHAR2
591 IS
592 l_setup_risk_type_id NUMBER;
593 l_parent_setup_risk_type_id NUMBER;
594 isParent VARCHAR2(1);
595
596 -- find the direct parent of specified setup risk type
597 cursor get_parent_risk_type_c (l_setup_risk_type_id IN NUMBER) is
598 SELECT PARENT_SETUP_RISK_TYPE_ID
599 from amw_setup_risk_types_b
600 where SETUP_RISK_TYPE_ID = l_setup_risk_type_id;
601
602 BEGIN
603 isParent := 'N';
604 l_setup_risk_type_id := p_setup_risk_type_id;
605
606 OPEN get_parent_risk_type_c (l_setup_risk_type_id);
607 FETCH get_parent_risk_type_c INTO l_parent_setup_risk_type_id;
608 CLOSE get_parent_risk_type_c;
609
610 IF (l_parent_setup_risk_type_id = p_target_setup_risk_type_id)THEN
611 isParent := 'Y';
612 ELSE
613 isParent := 'N';
614 END IF;
615
616 return isParent;
617
618 END IS_PARENT;
619
620
621
622
623 -- ===============================================================
624 -- Function name
625 -- CAN_HAVE_CHILD
626 -- Purpose
627 -- return non translated character (Y/N) to indicate the
628 -- specified parent_setup_risk_type can have target setup_risk_type as child.
629 -- History
630 -- 10/06/2004 tsho: bug 3902475,
631 -- if p_parent_setup_risk_type_id is the current direct parent of p_target_setup_risk_type_id
632 -- will return 'N' to prevent current parent shows up in LOV list
633 -- ===============================================================
634 FUNCTION CAN_HAVE_CHILD (
635 p_target_setup_risk_type_id IN NUMBER,
636 p_parent_setup_risk_type_id IN NUMBER
637 ) RETURN VARCHAR2 IS
638
639 l_setup_risk_type_id NUMBER;
640 l_isParent VARCHAR2(1);
641 last_index NUMBER;
642 canHaveChild VARCHAR2(1);
643
644 -- store the associated compliance env
645 compliance_env_list G_NUMBER_TABLE;
646
647 -- find the associated compliance env of specified setup risk type
648 cursor get_assoc_env_c (l_setup_risk_type_id IN NUMBER) is
649 SELECT COMPLIANCE_ENV_ID
650 from amw_compliance_env_assocs
651 where OBJECT_TYPE = 'SETUP_RISK_TYPE'
652 and PK1 = l_setup_risk_type_id;
653
654 BEGIN
655 l_setup_risk_type_id := p_target_setup_risk_type_id;
656 l_isParent := IS_PARENT (p_target_setup_risk_type_id => p_parent_setup_risk_type_id,
657 p_setup_risk_type_id => p_target_setup_risk_type_id);
658 canHaveChild := 'Y';
659
660 -- 10/06/2004 tsho: bug 3902475, should return 'N'
661 -- if p_parent_setup_risk_type_id is the current direct parent of p_target_setup_risk_type_id
662 IF (l_isParent = 'Y' ) THEN
663 return 'N';
664 END IF;
665
669 END IF;
666 -- if parent is the root (can always add child to root)
667 IF (p_parent_setup_risk_type_id = -1) THEN
668 return 'Y';
670
671 OPEN get_assoc_env_c (l_setup_risk_type_id);
672 FETCH get_assoc_env_c BULK COLLECT INTO compliance_env_list;
673 CLOSE get_assoc_env_c;
674
675 BEGIN
676 -- see if we found out any children
677 IF (compliance_env_list.FIRST is NULL) THEN
678 last_index := 0;
679 ELSE
680 last_index := compliance_env_list.LAST;
681 END IF;
682 EXCEPTION
683 WHEN others THEN
684 last_index := 0;
685 END;
686
687
688 FOR i in 1 .. last_index LOOP
689 -- if the passed-in p_parent_setup_risk_type_id is not yet associated with compliance_env_list(i)
690 -- than p_parent_setup_risk_type_id cannot have child p_target_setup_risk_type_id
691 IF ('N' = AMW_COMPLIANCE_ENV_ASSOCS_PVT.COMPLIANCE_ENVS_PRESENT(
692 p_compliance_env_id => compliance_env_list(i)
693 ,p_object_type => 'SETUP_RISK_TYPE'
694 ,p_pk1 => p_parent_setup_risk_type_id)) THEN
695 canHaveChild := 'N';
696 exit;
697 END IF;
698 END LOOP;
699
700 return canHaveChild;
701
702 END CAN_HAVE_CHILD;
703
704
705 -- ===============================================================
706 -- Procedure name
707 -- IS_ASSOC_TO_RISK
708 -- Purpose
709 -- x_is_assoc_to_risk is 'Y' if at least ONE of the passed-in
710 -- p_setup_risk_type and its descendants
711 -- are currently associated with risks in ame_risk_type table.
712 -- ===============================================================
713 PROCEDURE IS_ASSOC_TO_RISK (
714 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
715 p_commit IN VARCHAR2 := FND_API.G_FALSE,
716 p_validate_only IN VARCHAR2 := FND_API.G_FALSE,
717 p_setup_risk_type_id IN NUMBER,
718 x_is_assoc_to_risk OUT NOCOPY VARCHAR2,
719 x_return_status OUT NOCOPY VARCHAR2,
720 x_msg_count OUT NOCOPY NUMBER,
721 x_msg_data OUT NOCOPY VARCHAR2
722 ) IS
723
724 l_setup_risk_type_id NUMBER;
725
726 -- store the setup risk types
727 l_risk_type_list G_NUMBER_TABLE;
728
729 i NUMBER;
730 l_dummy NUMBER;
731 isAssociatedToRisk VARCHAR2(1);
732
733 -- find if there's associated risk of specified setup risk type
734 cursor get_assoc_risk_count_c (l_setup_risk_type_id IN NUMBER) is
735 SELECT count(*)
736 from amw_risk_type assoc,
737 amw_setup_risk_types_b rt
738 where rt.setup_risk_type_id = l_setup_risk_type_id
739 and assoc.risk_type_code = rt.risk_type_code;
740
741 BEGIN
742 -- create savepoint if p_commit is true
743 IF p_commit = FND_API.G_TRUE THEN
744 SAVEPOINT IS_ASSOC_TO_RISK_SAVE;
745 END IF;
746
747 -- initialize message list if p_init_msg_list is set to true
748 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
749 fnd_msg_pub.initialize;
750 end if;
751
752 -- initialize return status to success
753 x_return_status := fnd_api.g_ret_sts_success;
754
755 x_is_assoc_to_risk := 'N';
756 isAssociatedToRisk := 'N';
757 l_setup_risk_type_id := p_setup_risk_type_id;
758 l_risk_type_list := GET_ALL_DESCENDANTS(l_setup_risk_type_id);
759
760 IF (l_risk_type_list.FIRST is not NULL) THEN
761 FOR i in 0 .. l_risk_type_list.LAST LOOP
762 OPEN get_assoc_risk_count_c (l_risk_type_list(i));
763 FETCH get_assoc_risk_count_c INTO l_dummy;
764 CLOSE get_assoc_risk_count_c;
765
766 IF (l_dummy > 0) THEN
767 isAssociatedToRisk := 'Y';
768 EXIT;
769 END IF;
770
771 END LOOP;
772 END IF;
773
774 x_is_assoc_to_risk := isAssociatedToRisk;
775
776 EXCEPTION
777 WHEN FND_API.G_EXC_ERROR THEN
778 IF p_commit = FND_API.G_TRUE THEN
779 ROLLBACK TO IS_ASSOC_TO_RISK_SAVE;
780 END IF;
781
782 x_return_status := FND_API.G_RET_STS_ERROR;
783
784 fnd_msg_pub.count_and_get(p_count => x_msg_count,
785 p_data => x_msg_data);
786
787 WHEN OTHERS THEN
788 IF p_commit = FND_API.G_TRUE THEN
789 ROLLBACK TO IS_ASSOC_TO_RISK_SAVE;
790 END IF;
791
792 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
793
794 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AMW_SETUP_RISK_TYPES_PVT',
795 p_procedure_name => 'Delete_Risk_Types',
796 p_error_text => SUBSTRB(SQLERRM,1,240));
797
798 fnd_msg_pub.count_and_get(p_count => x_msg_count,
799 p_data => x_msg_data);
800
801 END IS_ASSOC_TO_RISK;
802
803
804 -- ----------------------------------------------------------------------
805
806 END AMW_SETUP_RISK_TYPES_PVT;
[Home] [Help]