[Home] [Help]
PACKAGE BODY: APPS.GR_RISK_COMBINATIONS_PKG
Source
1 PACKAGE BODY GR_RISK_COMBINATIONS_PKG AS
2 /*$Header: GRHIRCB.pls 115.4 2002/10/28 16:15:39 gkelly ship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_combination_sequence IN NUMBER,
7 p_risk_phrase_code IN VARCHAR2,
8 p_combination_group_number IN NUMBER,
9 p_risk_phrase_code_combo IN VARCHAR2,
10 p_display_order IN NUMBER,
11 p_created_by IN NUMBER,
12 p_creation_date IN DATE,
13 p_last_updated_by IN NUMBER,
14 p_last_update_date IN DATE,
15 p_last_update_login IN NUMBER,
16 x_rowid OUT NOCOPY VARCHAR2,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_oracle_error OUT NOCOPY NUMBER,
19 x_msg_data OUT NOCOPY VARCHAR2)
20 IS
21 /* Alpha Variables */
22
23 L_RETURN_STATUS VARCHAR2(1) := 'S';
24 L_KEY_EXISTS VARCHAR2(1);
25 L_MSG_DATA VARCHAR2(2000);
26 L_ROWID VARCHAR2(18);
27
28 /* Number Variables */
29
30 L_ORACLE_ERROR NUMBER;
31 L_CURRENT_SEQ NUMBER;
32
33 /* Exceptions */
34
35 FOREIGN_KEY_ERROR EXCEPTION;
36 ITEM_EXISTS_ERROR EXCEPTION;
37 ROW_MISSING_ERROR EXCEPTION;
38
39 /* Declare cursors */
40
41
42 BEGIN
43
44 /* Initialization Routine */
45
46 SAVEPOINT Insert_Row;
47 x_return_status := 'S';
48 x_oracle_error := 0;
49 x_msg_data := NULL;
50
51 /* Now call the check foreign key procedure */
52
53 Check_Foreign_Keys
54 (p_combination_sequence,
55 p_risk_phrase_code,
56 p_combination_group_number,
57 p_risk_phrase_code_combo,
58 p_display_order,
59 l_return_status,
60 l_oracle_error,
61 l_msg_data);
62 IF l_return_status <> 'S' THEN
63 RAISE Foreign_Key_Error;
64 END IF;
65
66 /* Now check the primary key doesn't already exist */
67
68 Check_Primary_Key
69 (p_combination_sequence,
70 'F',
71 l_rowid,
72 l_key_exists);
73
74 IF FND_API.To_Boolean(l_key_exists) THEN
75 RAISE Item_Exists_Error;
76 END IF;
77
78 INSERT INTO gr_risk_combinations
79 (combination_sequence_number,
80 risk_phrase_code,
81 combination_group_number,
82 risk_phrase_code_combo,
83 display_order,
84 created_by,
85 creation_date,
86 last_updated_by,
87 last_update_date,
88 last_update_login)
89 VALUES
90 (gr_risk_combinations_s.nextval,
91 p_risk_phrase_code,
92 p_combination_group_number,
93 p_risk_phrase_code_combo,
94 p_display_order,
95 p_created_by,
96 p_creation_date,
97 p_last_updated_by,
98 p_last_update_date,
99 p_last_update_login);
100
101 /* Get the assigned sequence number */
102
103 SELECT gr_risk_combinations_s.currval
104 INTO l_current_seq
105 FROM dual;
106
107 /* Now get the row id of the inserted record */
108
109 Check_Primary_Key
110 (l_current_seq,
111 'F',
112 l_rowid,
113 l_key_exists);
114
115 IF FND_API.To_Boolean(l_key_exists) THEN
116 x_rowid := l_rowid;
117 ELSE
118 RAISE Row_Missing_Error;
119 END IF;
120
121 /* Check the commit flag and if set, then commit the work. */
122
123 IF FND_API.To_Boolean(p_commit) THEN
124 COMMIT WORK;
125 END IF;
126
127 EXCEPTION
128
129 WHEN Foreign_Key_Error THEN
130 ROLLBACK TO SAVEPOINT Insert_Row;
131 x_return_status := l_return_status;
132 x_oracle_error := l_oracle_error;
133 FND_MESSAGE.SET_NAME('GR',
134 'GR_FOREIGN_KEY_ERROR');
135 FND_MESSAGE.SET_TOKEN('TEXT',
136 l_msg_data,
137 FALSE);
138 IF FND_API.To_Boolean(p_called_by_form) THEN
139 APP_EXCEPTION.Raise_Exception;
140 ELSE
141 x_msg_data := FND_MESSAGE.Get;
142 END IF;
143
144 WHEN Item_Exists_Error THEN
145 ROLLBACK TO SAVEPOINT Insert_Row;
146 x_return_status := 'E';
147 x_oracle_error := APP_EXCEPTION.Get_Code;
148 FND_MESSAGE.SET_NAME('GR',
149 'GR_RECORD_EXISTS');
150 FND_MESSAGE.SET_TOKEN('CODE',
151 p_risk_phrase_code,
152 FALSE);
153 IF FND_API.To_Boolean(p_called_by_form) THEN
154 APP_EXCEPTION.Raise_Exception;
155 ELSE
156 x_msg_data := FND_MESSAGE.Get;
157 END IF;
158
159 WHEN Row_Missing_Error THEN
160 ROLLBACK TO SAVEPOINT Insert_Row;
161 x_return_status := 'E';
162 x_oracle_error := APP_EXCEPTION.Get_Code;
163 FND_MESSAGE.SET_NAME('GR',
164 'GR_NO_RECORD_INSERTED');
165 FND_MESSAGE.SET_TOKEN('CODE',
166 p_risk_phrase_code,
167 FALSE);
168 IF FND_API.To_Boolean(p_called_by_form) THEN
169 APP_EXCEPTION.Raise_Exception;
170 ELSE
171 x_msg_data := FND_MESSAGE.Get;
172 END IF;
173
174 WHEN OTHERS THEN
175 ROLLBACK TO SAVEPOINT Insert_Row;
176 x_return_status := 'U';
177 x_oracle_error := APP_EXCEPTION.Get_Code;
178 l_msg_data := APP_EXCEPTION.Get_Text;
179 FND_MESSAGE.SET_NAME('GR',
180 'GR_UNEXPECTED_ERROR');
181 FND_MESSAGE.SET_TOKEN('TEXT',
182 l_msg_data,
183 FALSE);
184 IF FND_API.To_Boolean(p_called_by_form) THEN
185 APP_EXCEPTION.Raise_Exception;
186 ELSE
187 x_msg_data := FND_MESSAGE.Get;
188 END IF;
189
190 END Insert_Row;
191
192 PROCEDURE Update_Row
193 (p_commit IN VARCHAR2,
194 p_called_by_form IN VARCHAR2,
195 p_rowid IN VARCHAR2,
196 p_combination_sequence IN NUMBER,
197 p_risk_phrase_code IN VARCHAR2,
198 p_combination_group_number IN NUMBER,
199 p_risk_phrase_code_combo IN VARCHAR2,
200 p_display_order IN NUMBER,
201 p_created_by IN NUMBER,
202 p_creation_date IN DATE,
203 p_last_updated_by IN NUMBER,
204 p_last_update_date IN DATE,
205 p_last_update_login IN NUMBER,
209 IS
206 x_return_status OUT NOCOPY VARCHAR2,
207 x_oracle_error OUT NOCOPY NUMBER,
208 x_msg_data OUT NOCOPY VARCHAR2)
210
211 /* Alpha Variables */
212
213 L_RETURN_STATUS VARCHAR2(1) := 'S';
214 L_MSG_DATA VARCHAR2(2000);
215
216 /* Number Variables */
217
218 L_ORACLE_ERROR NUMBER;
219
220 /* Exceptions */
221
222 FOREIGN_KEY_ERROR EXCEPTION;
223 ROW_MISSING_ERROR EXCEPTION;
224 BEGIN
225
226 /* Initialization Routine */
227
228 SAVEPOINT Update_Row;
229 x_return_status := 'S';
230 x_oracle_error := 0;
231 x_msg_data := NULL;
232
233 /* Now call the check foreign key procedure */
234
235 Check_Foreign_Keys
236 (p_combination_sequence,
237 p_risk_phrase_code,
238 p_combination_group_number,
239 p_risk_phrase_code_combo,
240 p_display_order,
241 l_return_status,
242 l_oracle_error,
243 l_msg_data);
244
245 IF l_return_status <> 'S' THEN
246 RAISE Foreign_Key_Error;
247 ELSE
248 UPDATE gr_risk_combinations
249 SET combination_sequence_number = p_combination_sequence,
250 risk_phrase_code = p_risk_phrase_code,
251 combination_group_number = p_combination_group_number,
252 risk_phrase_code_combo = p_risk_phrase_code_combo,
253 display_order = p_display_order,
254 created_by = p_created_by,
255 creation_date = p_creation_date,
256 last_updated_by = p_last_updated_by,
257 last_update_date = p_last_update_date,
258 last_update_login = p_last_update_login
259 WHERE rowid = p_rowid;
260 IF SQL%NOTFOUND THEN
261 RAISE Row_Missing_Error;
265 /* Check the commit flag and if set, then commit the work. */
262 END IF;
263 END IF;
264
266
267 IF FND_API.To_Boolean(p_commit) THEN
268 COMMIT WORK;
269 END IF;
270
271 EXCEPTION
272
273 WHEN Foreign_Key_Error THEN
274 ROLLBACK TO SAVEPOINT Update_Row;
275 x_return_status := l_return_status;
276 x_oracle_error := l_oracle_error;
277 FND_MESSAGE.SET_NAME('GR',
278 'GR_FOREIGN_KEY_ERROR');
279 FND_MESSAGE.SET_TOKEN('TEXT',
280 l_msg_data,
281 FALSE);
282 IF FND_API.To_Boolean(p_called_by_form) THEN
283 APP_EXCEPTION.Raise_Exception;
284 ELSE
285 x_msg_data := FND_MESSAGE.Get;
286 END IF;
287
288 WHEN Row_Missing_Error THEN
289 ROLLBACK TO SAVEPOINT Update_Row;
290 x_return_status := 'E';
291 x_oracle_error := APP_EXCEPTION.Get_Code;
292 FND_MESSAGE.SET_NAME('GR',
293 'GR_NO_RECORD_INSERTED');
294 FND_MESSAGE.SET_TOKEN('CODE',
295 p_risk_phrase_code,
296 FALSE);
297 IF FND_API.To_Boolean(p_called_by_form) THEN
298 APP_EXCEPTION.Raise_Exception;
299 ELSE
300 x_msg_data := FND_MESSAGE.Get;
301 END IF;
302
303 WHEN OTHERS THEN
304 ROLLBACK TO SAVEPOINT Update_Row;
305 x_return_status := 'U';
306 x_oracle_error := APP_EXCEPTION.Get_Code;
307 l_msg_data := APP_EXCEPTION.Get_Text;
308 FND_MESSAGE.SET_NAME('GR',
309 'GR_UNEXPECTED_ERROR');
310 FND_MESSAGE.SET_TOKEN('TEXT',
311 l_msg_data,
312 FALSE);
313 IF FND_API.To_Boolean(p_called_by_form) THEN
314 APP_EXCEPTION.Raise_Exception;
315 ELSE
316 x_msg_data := FND_MESSAGE.Get;
317 END IF;
318
319 END Update_Row;
320
321 PROCEDURE Lock_Row
322 (p_commit IN VARCHAR2,
323 p_called_by_form IN VARCHAR2,
324 p_rowid IN VARCHAR2,
325 p_combination_sequence IN NUMBER,
326 p_risk_phrase_code IN VARCHAR2,
327 p_combination_group_number IN NUMBER,
328 p_risk_phrase_code_combo IN VARCHAR2,
329 p_display_order IN NUMBER,
330 p_created_by IN NUMBER,
331 p_creation_date IN DATE,
332 p_last_updated_by IN NUMBER,
336 x_oracle_error OUT NOCOPY NUMBER,
333 p_last_update_date IN DATE,
334 p_last_update_login IN NUMBER,
335 x_return_status OUT NOCOPY VARCHAR2,
337 x_msg_data OUT NOCOPY VARCHAR2)
338 IS
339
340 /* Alpha Variables */
341
342 L_RETURN_STATUS VARCHAR2(1) := 'S';
343 L_MSG_DATA VARCHAR2(2000);
344
345 /* Number Variables */
346
347 L_ORACLE_ERROR NUMBER;
348
349 /* Exceptions */
350
351 NO_DATA_FOUND_ERROR EXCEPTION;
352 ROW_ALREADY_LOCKED_ERROR EXCEPTION;
353 PRAGMA EXCEPTION_INIT(ROW_ALREADY_LOCKED_ERROR,-54);
354
355 /* Define the cursors */
356
357 CURSOR c_lock_combinations
358 IS
359 SELECT *
360 FROM gr_risk_combinations
361 WHERE rowid = p_rowid
362 FOR UPDATE NOWAIT;
363 LockComboRcd c_lock_combinations%ROWTYPE;
364 BEGIN
365
366 /* Initialization Routine */
367
368 SAVEPOINT Lock_Row;
369 x_return_status := 'S';
370 x_oracle_error := 0;
371 x_msg_data := NULL;
372
373 /* Now lock the record */
374
375 OPEN c_lock_combinations;
376 FETCH c_lock_combinations INTO LockComboRcd;
377 IF c_lock_combinations%NOTFOUND THEN
378 CLOSE c_lock_combinations;
379 RAISE No_Data_Found_Error;
380 END IF;
381 CLOSE c_lock_combinations;
382
383 IF FND_API.To_Boolean(p_commit) THEN
384 COMMIT WORK;
385 END IF;
386
387 EXCEPTION
388
389 WHEN No_Data_Found_Error THEN
390 ROLLBACK TO SAVEPOINT Lock_Row;
391 x_return_status := 'E';
392 FND_MESSAGE.SET_NAME('GR',
393 'GR_RECORD_NOT_FOUND');
394 FND_MESSAGE.SET_TOKEN('CODE',
395 p_risk_phrase_code,
396 FALSE);
397 IF FND_API.To_Boolean(p_called_by_form) THEN
398 APP_EXCEPTION.Raise_Exception;
399 ELSE
400 x_msg_data := FND_MESSAGE.Get;
401 END IF;
402
403 WHEN Row_Already_Locked_Error THEN
404 ROLLBACK TO SAVEPOINT Lock_Row;
405 x_return_status := 'E';
406 x_oracle_error := APP_EXCEPTION.Get_Code;
407 FND_MESSAGE.SET_NAME('GR',
408 'GR_ROW_IS_LOCKED');
409 IF FND_API.To_Boolean(p_called_by_form) THEN
410 APP_EXCEPTION.Raise_Exception;
411 ELSE
412 x_msg_data := FND_MESSAGE.Get;
413 END IF;
414
415 WHEN OTHERS THEN
416 ROLLBACK TO SAVEPOINT Lock_Row;
417 x_return_status := 'U';
418 x_oracle_error := APP_EXCEPTION.Get_Code;
419 l_msg_data := APP_EXCEPTION.Get_Text;
420 FND_MESSAGE.SET_NAME('GR',
421 'GR_UNEXPECTED_ERROR');
422 FND_MESSAGE.SET_TOKEN('TEXT',
423 l_msg_data,
427 ELSE
424 FALSE);
425 IF FND_API.To_Boolean(p_called_by_form) THEN
426 APP_EXCEPTION.Raise_Exception;
428 x_msg_data := FND_MESSAGE.Get;
429 END IF;
430
431 END Lock_Row;
432
433 PROCEDURE Delete_Row
434 (p_commit IN VARCHAR2,
435 p_called_by_form IN VARCHAR2,
436 p_rowid IN VARCHAR2,
437 p_combination_sequence IN NUMBER,
438 p_risk_phrase_code IN VARCHAR2,
439 p_combination_group_number IN NUMBER,
440 p_risk_phrase_code_combo IN VARCHAR2,
441 p_display_order IN NUMBER,
442 p_created_by IN NUMBER,
443 p_creation_date IN DATE,
444 p_last_updated_by IN NUMBER,
445 p_last_update_date IN DATE,
446 p_last_update_login IN NUMBER,
447 x_return_status OUT NOCOPY VARCHAR2,
448 x_oracle_error OUT NOCOPY NUMBER,
449 x_msg_data OUT NOCOPY VARCHAR2)
450 IS
451
452 /* Alpha Variables */
453
454 L_RETURN_STATUS VARCHAR2(1) := 'S';
455 L_MSG_DATA VARCHAR2(2000);
456 L_CALLED_BY_FORM VARCHAR2(1);
457
458 /* Number Variables */
459
460 L_ORACLE_ERROR NUMBER;
461
462 /* Exceptions */
463
464 CHECK_INTEGRITY_ERROR EXCEPTION;
465 ROW_MISSING_ERROR EXCEPTION;
466 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
467
468 BEGIN
469
470 /* Initialization Routine */
471
472 SAVEPOINT Delete_Row;
473 x_return_status := 'S';
474 l_called_by_form := 'F';
475 x_oracle_error := 0;
476 x_msg_data := NULL;
477
478 /* Now call the check integrity procedure */
479
480 Check_Integrity
481 (l_called_by_form,
482 p_combination_sequence,
483 p_risk_phrase_code,
484 p_combination_group_number,
485 p_risk_phrase_code_combo,
486 p_display_order,
487 l_return_status,
488 l_oracle_error,
489 l_msg_data);
490
491 IF l_return_status <> 'S' THEN
492 RAISE Check_Integrity_Error;
493 END IF;
494
495 DELETE FROM gr_risk_combinations
496 WHERE rowid = p_rowid;
497
498 /* Check the commit flag and if set, then commit the work. */
499
500 IF FND_API.TO_Boolean(p_commit) THEN
501 COMMIT WORK;
502 END IF;
503
504 EXCEPTION
505
506 WHEN Check_Integrity_Error THEN
507 ROLLBACK TO SAVEPOINT Delete_Row;
511 IF FND_API.To_Boolean(p_called_by_form) THEN
508 x_return_status := l_return_status;
509 x_oracle_error := l_oracle_error;
510 x_msg_data := l_msg_data;
512 APP_EXCEPTION.Raise_Exception;
513 END IF;
514
515 WHEN Row_Missing_Error THEN
516 ROLLBACK TO SAVEPOINT Delete_Row;
517 x_return_status := 'E';
518 x_oracle_error := APP_EXCEPTION.Get_Code;
519 FND_MESSAGE.SET_NAME('GR',
520 'GR_RECORD_NOT_FOUND');
521 FND_MESSAGE.SET_TOKEN('CODE',
522 p_risk_phrase_code,
523 FALSE);
524 IF FND_API.To_Boolean(p_called_by_form) THEN
525 APP_EXCEPTION.Raise_Exception;
526 ELSE
527 x_msg_data := FND_MESSAGE.Get;
528 END IF;
529
530 WHEN OTHERS THEN
531 ROLLBACK TO SAVEPOINT Delete_Row;
532 x_return_status := 'U';
533 x_oracle_error := APP_EXCEPTION.Get_Code;
534 l_msg_data := APP_EXCEPTION.Get_Text;
535 l_msg_data := APP_EXCEPTION.Get_Text;
536 FND_MESSAGE.SET_NAME('GR',
537 'GR_UNEXPECTED_ERROR');
538 FND_MESSAGE.SET_TOKEN('TEXT',
539 l_msg_data,
540 FALSE);
541 IF FND_API.To_Boolean(p_called_by_form) THEN
542 APP_EXCEPTION.Raise_Exception;
543 ELSE
544 x_msg_data := FND_MESSAGE.Get;
545 END IF;
546
547 END Delete_Row;
548
549 PROCEDURE Delete_Rows
550 (p_commit IN VARCHAR2,
551 p_called_by_form IN VARCHAR2,
552 p_risk_phrase_code IN VARCHAR2,
553 x_return_status OUT NOCOPY VARCHAR2,
554 x_oracle_error OUT NOCOPY NUMBER,
555 x_msg_data OUT NOCOPY VARCHAR2)
556 IS
557
558 /* Alpha Variables */
559
560 L_RETURN_STATUS VARCHAR2(1) := 'S';
561 L_MSG_DATA VARCHAR2(2000);
562 L_MSG_TOKEN VARCHAR2(100);
563
564 /* Number Variables */
565
566 L_ORACLE_ERROR NUMBER;
567
568 /* Define the cursors */
569
570 BEGIN
571
572 /* Initialization Routine */
573
574 SAVEPOINT Delete_Rows;
575 x_return_status := 'S';
576 x_oracle_error := 0;
577 x_msg_data := NULL;
578 l_msg_token := p_risk_phrase_code;
579
580 DELETE FROM gr_risk_combinations
581 WHERE risk_phrase_code = p_risk_phrase_code;
582
583 IF FND_API.To_Boolean(p_commit) THEN
584 COMMIT WORK;
585 END IF;
586
587 EXCEPTION
588
589 WHEN OTHERS THEN
590 ROLLBACK TO SAVEPOINT Delete_Rows;
591 x_return_status := 'U';
592 x_oracle_error := APP_EXCEPTION.Get_Code;
593 l_msg_data := APP_EXCEPTION.Get_Text;
594 FND_MESSAGE.SET_NAME('GR',
595 'GR_UNEXPECTED_ERROR');
596 FND_MESSAGE.SET_TOKEN('TEXT',
597 l_msg_token,
598 FALSE);
599 END Delete_Rows;
600
601
602 PROCEDURE Check_Foreign_Keys
603 (p_combination_sequence IN NUMBER,
604 p_risk_phrase_code IN VARCHAR2,
605 p_combination_group_number IN NUMBER,
606 p_risk_phrase_code_combo IN VARCHAR2,
607 p_display_order IN NUMBER,
608 x_return_status OUT NOCOPY VARCHAR2,
609 x_oracle_error OUT NOCOPY NUMBER,
610 x_msg_data OUT NOCOPY VARCHAR2)
611 IS
612
613 /* Alpha Variables */
614
615 L_RETURN_STATUS VARCHAR2(1) := 'S';
616 L_MSG_DATA VARCHAR2(2000);
617 L_risk_PHRASE VARCHAR2(15);
618 L_KEY_EXISTS VARCHAR2(1);
619 L_ROWID VARCHAR2(18);
620
621 /* Number Variables */
622
623 L_ORACLE_ERROR NUMBER;
624
625 /* Define the cursors */
626
627
628 BEGIN
629
630 /* Initialization Routine */
631
632 SAVEPOINT Check_Foreign_Keys;
633 x_return_status := 'S';
634 x_oracle_error := 0;
635 l_msg_data := NULL;
636 x_msg_data := NULL;
637
638 /* Check the main risk phrase code first. */
639
640 l_risk_phrase := p_risk_phrase_code;
641 GR_risk_PHRASES_B_PKG.Check_Primary_Key
642 (l_risk_phrase,
643 'F',
644 l_rowid,
645 l_key_exists);
646
647 IF NOT FND_API.To_Boolean(l_key_exists) THEN
648 FND_MESSAGE.SET_NAME('GR',
649 'GR_RECORD_NOT_FOUND');
650 FND_MESSAGE.SET_TOKEN('CODE',
651 l_risk_phrase,
652 FALSE);
653 l_msg_data := l_msg_data || FND_MESSAGE.Get;
654 x_return_status := 'E';
655 END IF;
656
657 /* Now check the combination phrase */
658
659 l_risk_phrase := p_risk_phrase_code_combo;
660 GR_risk_PHRASES_B_PKG.Check_Primary_Key
661 (l_risk_phrase,
662 'F',
663 l_rowid,
664 l_key_exists);
665
666 IF NOT FND_API.To_Boolean(l_key_exists) THEN
667 FND_MESSAGE.SET_NAME('GR',
668 'GR_RECORD_NOT_FOUND');
669 FND_MESSAGE.SET_TOKEN('CODE',
670 l_risk_phrase,
671 FALSE);
672 l_msg_data := l_msg_data || FND_MESSAGE.Get;
673 x_return_status := 'E';
674 END IF;
675
676 IF x_return_status <> 'S' THEN
677 x_msg_data := l_msg_data;
678 END IF;
679
680 EXCEPTION
681
682 WHEN OTHERS THEN
683 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
684
685 x_return_status := 'U';
686 x_oracle_error := APP_EXCEPTION.Get_Code;
687 l_msg_data := APP_EXCEPTION.Get_Text;
688 FND_MESSAGE.SET_NAME('GR',
689 'GR_UNEXPECTED_ERROR');
690 FND_MESSAGE.SET_TOKEN('TEXT',
691 l_msg_data,
692 FALSE);
693 END Check_Foreign_Keys;
694
695 PROCEDURE Check_Integrity
696 (p_called_by_form IN VARCHAR2,
697 p_combination_sequence IN NUMBER,
698 p_risk_phrase_code IN VARCHAR2,
699 p_combination_group_number IN NUMBER,
700 p_risk_phrase_code_combo IN VARCHAR2,
701 p_display_order IN NUMBER,
702 x_return_status OUT NOCOPY VARCHAR2,
703 x_oracle_error OUT NOCOPY NUMBER,
704 x_msg_data OUT NOCOPY VARCHAR2)
705 IS
706
707 /* Alpha Variables */
708
709 L_RETURN_STATUS VARCHAR2(1) := 'S';
710 L_MSG_DATA VARCHAR2(2000);
711 L_CODE_BLOCK VARCHAR2(100);
712
713 /* Number Variables */
714
715 L_ORACLE_ERROR NUMBER;
716 L_RECORD_COUNT NUMBER;
717
718 /* Define the Cursors */
719
720
721 BEGIN
722
723 /* Initialization Routine */
724
725 SAVEPOINT Check_Integrity;
726 x_return_status := 'S';
727 x_oracle_error := 0;
728 x_msg_data := NULL;
729
730 /* No integrity checking is required */
731
732 /* Now sort out the error messaging */
733
734 IF l_return_status <> 'S' THEN
735 x_return_status := l_return_status;
736 x_msg_data := l_msg_data;
737 END IF;
738
739 EXCEPTION
740
741 WHEN OTHERS THEN
742 ROLLBACK TO SAVEPOINT Check_Integrity;
743 x_return_status := 'U';
744 x_oracle_error := APP_EXCEPTION.Get_Code;
745 l_msg_data := APP_EXCEPTION.Get_Text;
746 FND_MESSAGE.SET_NAME('GR',
747 'GR_UNEXPECTED_ERROR');
748 FND_MESSAGE.SET_TOKEN('TEXT',
749 l_msg_data,
750 FALSE);
751 IF FND_API.To_Boolean(p_called_by_form) THEN
752 APP_EXCEPTION.Raise_Exception;
753 ELSE
754 x_msg_data := FND_MESSAGE.Get;
755 END IF;
756
757 END Check_Integrity;
758
759 PROCEDURE Check_Primary_Key
760 /* p_combination_sequence is the risk phrase combination to check.
761 ** p_called_by_form is 'T' if called by a form or 'F' if not.
762 ** x_rowid is the row id of the record if found.
763 ** x_key_exists is 'T' is the record is found, 'F' if not.
764 */
765 (p_combination_sequence IN NUMBER,
766 p_called_by_form IN VARCHAR2,
767 x_rowid OUT NOCOPY VARCHAR2,
768 x_key_exists OUT NOCOPY VARCHAR2)
769 IS
770 /* Alphanumeric variables */
771
772 L_MSG_DATA VARCHAR2(80);
773
774 /* Declare any variables and the cursor */
775
776
777 CURSOR c_get_combo_rowid
778 IS
779 SELECT rc.rowid
780 FROM gr_risk_combinations rc
781 WHERE rc.combination_sequence_number = p_combination_sequence;
782 ComboRecord c_get_combo_rowid%ROWTYPE;
783
784 BEGIN
785
786 x_key_exists := 'F';
787 OPEN c_get_combo_rowid;
788 FETCH c_get_combo_rowid INTO ComboRecord;
789 IF c_get_combo_rowid%FOUND THEN
790 x_key_exists := 'T';
791 x_rowid := ComboRecord.rowid;
792 ELSE
793 x_key_exists := 'F';
794 END IF;
795 CLOSE c_get_combo_rowid;
796
797 EXCEPTION
798
799 WHEN Others THEN
800 l_msg_data := APP_EXCEPTION.Get_Text;
801 FND_MESSAGE.SET_NAME('GR',
802 'GR_UNEXPECTED_ERROR');
803 FND_MESSAGE.SET_TOKEN('TEXT',
804 l_msg_data,
805 FALSE);
806 IF FND_API.To_Boolean(p_called_by_form) THEN
807 APP_EXCEPTION.Raise_Exception;
808 END IF;
809
810 END Check_Primary_Key;
811
812 END GR_RISK_COMBINATIONS_PKG;