[Home] [Help]
PACKAGE BODY: APPS.GR_RISK_PHRASES_B_PKG
Source
1 PACKAGE BODY GR_RISK_PHRASES_B_PKG AS
2 /*$Header: GRHIRPB.pls 115.5 2002/10/28 16:16:46 gkelly ship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_risk_phrase_code IN VARCHAR2,
7 p_additional_text_indicator IN VARCHAR2,
8 p_lookup_type IN VARCHAR2,
9 p_lookup_code IN VARCHAR2,
10 p_attribute_category IN VARCHAR2,
11 p_attribute1 IN VARCHAR2,
12 p_attribute2 IN VARCHAR2,
13 p_attribute3 IN VARCHAR2,
14 p_attribute4 IN VARCHAR2,
15 p_attribute5 IN VARCHAR2,
16 p_attribute6 IN VARCHAR2,
17 p_attribute7 IN VARCHAR2,
18 p_attribute8 IN VARCHAR2,
19 p_attribute9 IN VARCHAR2,
20 p_attribute10 IN VARCHAR2,
21 p_attribute11 IN VARCHAR2,
22 p_attribute12 IN VARCHAR2,
23 p_attribute13 IN VARCHAR2,
24 p_attribute14 IN VARCHAR2,
25 p_attribute15 IN VARCHAR2,
26 p_attribute16 IN VARCHAR2,
27 p_attribute17 IN VARCHAR2,
28 p_attribute18 IN VARCHAR2,
29 p_attribute19 IN VARCHAR2,
30 p_attribute20 IN VARCHAR2,
31 p_attribute21 IN VARCHAR2,
32 p_attribute22 IN VARCHAR2,
33 p_attribute23 IN VARCHAR2,
34 p_attribute24 IN VARCHAR2,
35 p_attribute25 IN VARCHAR2,
36 p_attribute26 IN VARCHAR2,
37 p_attribute27 IN VARCHAR2,
38 p_attribute28 IN VARCHAR2,
39 p_attribute29 IN VARCHAR2,
40 p_attribute30 IN VARCHAR2,
41 p_created_by IN NUMBER,
42 p_creation_date IN DATE,
43 p_last_updated_by IN NUMBER,
44 p_last_update_date IN DATE,
45 p_last_update_login IN NUMBER,
46 x_rowid OUT NOCOPY VARCHAR2,
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_oracle_error OUT NOCOPY NUMBER,
49 x_msg_data OUT NOCOPY VARCHAR2)
50 IS
51 /* Alpha Variables */
52
53 L_RETURN_STATUS VARCHAR2(1) := 'S';
54 L_KEY_EXISTS VARCHAR2(1);
55 L_MSG_DATA VARCHAR2(2000);
56 L_ROWID VARCHAR2(18);
57
58 /* Number Variables */
59
60 L_ORACLE_ERROR NUMBER;
61 /* Exceptions */
62
63 FOREIGN_KEY_ERROR EXCEPTION;
64 ITEM_EXISTS_ERROR EXCEPTION;
65 ROW_MISSING_ERROR EXCEPTION;
66
67 /* Declare cursors */
68
69
70 BEGIN
71
72 /* Initialization Routine */
73
74 SAVEPOINT Insert_Row;
75 x_return_status := 'S';
76 x_oracle_error := 0;
77 x_msg_data := NULL;
78
79 /* Now call the check foreign key procedure */
80
81 Check_Foreign_Keys
82 (p_risk_phrase_code,
83 p_additional_text_indicator,
84 p_lookup_type,
85 p_lookup_code,
86 p_attribute_category,
87 p_attribute1,
88 p_attribute2,
89 p_attribute3,
90 p_attribute4,
91 p_attribute5,
92 p_attribute6,
93 p_attribute7,
94 p_attribute8,
95 p_attribute9,
96 p_attribute10,
97 p_attribute11,
98 p_attribute12,
99 p_attribute13,
100 p_attribute14,
101 p_attribute15,
102 p_attribute16,
103 p_attribute17,
104 p_attribute18,
105 p_attribute19,
106 p_attribute20,
107 p_attribute21,
108 p_attribute22,
109 p_attribute23,
110 p_attribute24,
111 p_attribute25,
112 p_attribute26,
113 p_attribute27,
114 p_attribute28,
115 p_attribute29,
116 p_attribute30,
117 l_return_status,
118 l_oracle_error,
119 l_msg_data);
120 IF l_return_status <> 'S' THEN
121 RAISE Foreign_Key_Error;
122 END IF;
123
124 /* Now check the primary key doesn't already exist */
125
126 Check_Primary_Key
127 (p_risk_phrase_code,
128 'F',
129 l_rowid,
130 l_key_exists);
131
132 IF FND_API.To_Boolean(l_key_exists) THEN
133 RAISE Item_Exists_Error;
134 END IF;
135
136 INSERT INTO gr_risk_phrases_b
137 (risk_phrase_code,
138 additional_text_indicator,
139 lookup_type,
140 lookup_code,
141 attribute_category,
142 attribute1,
143 attribute2,
144 attribute3,
145 attribute4,
146 attribute5,
147 attribute6,
148 attribute7,
149 attribute8,
150 attribute9,
151 attribute10,
152 attribute11,
153 attribute12,
154 attribute13,
155 attribute14,
156 attribute15,
157 attribute16,
158 attribute17,
159 attribute18,
160 attribute19,
161 attribute20,
162 attribute21,
163 attribute22,
164 attribute23,
165 attribute24,
166 attribute25,
167 attribute26,
168 attribute27,
169 attribute28,
170 attribute29,
171 attribute30,
172 created_by,
173 creation_date,
174 last_updated_by,
175 last_update_date,
176 last_update_login)
177 VALUES
178 (p_risk_phrase_code,
179 p_additional_text_indicator,
180 p_lookup_type,
181 p_lookup_code,
182 p_attribute_category,
183 p_attribute1,
184 p_attribute2,
185 p_attribute3,
186 p_attribute4,
187 p_attribute5,
188 p_attribute6,
189 p_attribute7,
190 p_attribute8,
191 p_attribute9,
192 p_attribute10,
193 p_attribute11,
194 p_attribute12,
195 p_attribute13,
196 p_attribute14,
197 p_attribute15,
198 p_attribute16,
199 p_attribute17,
200 p_attribute18,
201 p_attribute19,
202 p_attribute20,
203 p_attribute21,
204 p_attribute22,
205 p_attribute23,
206 p_attribute24,
207 p_attribute25,
208 p_attribute26,
209 p_attribute27,
210 p_attribute28,
211 p_attribute29,
212 p_attribute30,
213 p_created_by,
214 p_creation_date,
215 p_last_updated_by,
216 p_last_update_date,
217 p_last_update_login);
218
219 /* Now get the row id of the inserted record */
220
221 Check_Primary_Key
222 (p_risk_phrase_code,
223 'F',
224 l_rowid,
225 l_key_exists);
226
227 IF FND_API.To_Boolean(l_key_exists) THEN
228 x_rowid := l_rowid;
229 ELSE
230 RAISE Row_Missing_Error;
231 END IF;
232
233 /* Check the commit flag and if set, then commit the work. */
234
235 IF FND_API.To_Boolean(p_commit) THEN
236 COMMIT WORK;
237 END IF;
238
239 EXCEPTION
240
241 WHEN Foreign_Key_Error THEN
242 ROLLBACK TO SAVEPOINT Insert_Row;
243 x_return_status := l_return_status;
244 x_oracle_error := l_oracle_error;
245 FND_MESSAGE.SET_NAME('GR',
246 'GR_FOREIGN_KEY_ERROR');
247 FND_MESSAGE.SET_TOKEN('TEXT',
248 l_msg_data,
249 FALSE);
250 IF FND_API.To_Boolean(p_called_by_form) THEN
251 APP_EXCEPTION.Raise_Exception;
252 ELSE
253 x_msg_data := FND_MESSAGE.Get;
254 END IF;
255
256 WHEN Item_Exists_Error THEN
257 ROLLBACK TO SAVEPOINT Insert_Row;
258 x_return_status := 'E';
259 x_oracle_error := APP_EXCEPTION.Get_Code;
260 FND_MESSAGE.SET_NAME('GR',
261 'GR_RECORD_THERE');
262 FND_MESSAGE.SET_TOKEN('CODE',
263 p_risk_phrase_code,
264 FALSE);
265 IF FND_API.To_Boolean(p_called_by_form) THEN
266 APP_EXCEPTION.Raise_Exception;
267 ELSE
268 x_msg_data := FND_MESSAGE.Get;
269 END IF;
270
271 WHEN Row_Missing_Error THEN
272 ROLLBACK TO SAVEPOINT Insert_Row;
273 x_return_status := 'E';
274 x_oracle_error := APP_EXCEPTION.Get_Code;
275 FND_MESSAGE.SET_NAME('GR',
276 'GR_NO_RECORD_INSERTED');
277 FND_MESSAGE.SET_TOKEN('CODE',
278 p_risk_phrase_code,
279 FALSE);
280 IF FND_API.To_Boolean(p_called_by_form) THEN
281 APP_EXCEPTION.Raise_Exception;
282 ELSE
283 x_msg_data := FND_MESSAGE.Get;
284 END IF;
285
286 WHEN OTHERS THEN
287 ROLLBACK TO SAVEPOINT Insert_Row;
288 x_return_status := 'U';
289 x_oracle_error := APP_EXCEPTION.Get_Code;
290 l_msg_data := APP_EXCEPTION.Get_Text;
291 FND_MESSAGE.SET_NAME('GR',
292 'GR_UNEXPECTED_ERROR');
293 FND_MESSAGE.SET_TOKEN('TEXT',
294 l_msg_data,
295 FALSE);
296 IF FND_API.To_Boolean(p_called_by_form) THEN
297 APP_EXCEPTION.Raise_Exception;
298 ELSE
299 x_msg_data := FND_MESSAGE.Get;
300 END IF;
301
302 END Insert_Row;
303
304 PROCEDURE Update_Row
305 (p_commit IN VARCHAR2,
306 p_called_by_form IN VARCHAR2,
307 p_rowid IN VARCHAR2,
308 p_risk_phrase_code IN VARCHAR2,
309 p_additional_text_indicator IN VARCHAR2,
310 p_lookup_type IN VARCHAR2,
311 p_lookup_code IN VARCHAR2,
312 p_attribute_category IN VARCHAR2,
313 p_attribute1 IN VARCHAR2,
314 p_attribute2 IN VARCHAR2,
315 p_attribute3 IN VARCHAR2,
316 p_attribute4 IN VARCHAR2,
317 p_attribute5 IN VARCHAR2,
318 p_attribute6 IN VARCHAR2,
319 p_attribute7 IN VARCHAR2,
320 p_attribute8 IN VARCHAR2,
321 p_attribute9 IN VARCHAR2,
322 p_attribute10 IN VARCHAR2,
323 p_attribute11 IN VARCHAR2,
324 p_attribute12 IN VARCHAR2,
325 p_attribute13 IN VARCHAR2,
326 p_attribute14 IN VARCHAR2,
327 p_attribute15 IN VARCHAR2,
328 p_attribute16 IN VARCHAR2,
329 p_attribute17 IN VARCHAR2,
330 p_attribute18 IN VARCHAR2,
331 p_attribute19 IN VARCHAR2,
332 p_attribute20 IN VARCHAR2,
333 p_attribute21 IN VARCHAR2,
334 p_attribute22 IN VARCHAR2,
335 p_attribute23 IN VARCHAR2,
336 p_attribute24 IN VARCHAR2,
337 p_attribute25 IN VARCHAR2,
338 p_attribute26 IN VARCHAR2,
339 p_attribute27 IN VARCHAR2,
340 p_attribute28 IN VARCHAR2,
341 p_attribute29 IN VARCHAR2,
342 p_attribute30 IN VARCHAR2,
343 p_created_by IN NUMBER,
344 p_creation_date IN DATE,
345 p_last_updated_by IN NUMBER,
346 p_last_update_date IN DATE,
347 p_last_update_login IN NUMBER,
348 x_return_status OUT NOCOPY VARCHAR2,
349 x_oracle_error OUT NOCOPY NUMBER,
350 x_msg_data OUT NOCOPY VARCHAR2)
351 IS
352
353 /* Alpha Variables */
354
355 L_RETURN_STATUS VARCHAR2(1) := 'S';
356 L_MSG_DATA VARCHAR2(2000);
357
358 /* Number Variables */
359
360 L_ORACLE_ERROR NUMBER;
361
362 /* Exceptions */
363
364 FOREIGN_KEY_ERROR EXCEPTION;
365 ROW_MISSING_ERROR EXCEPTION;
366 BEGIN
367
368 /* Initialization Routine */
369
370 SAVEPOINT Update_Row;
371 x_return_status := 'S';
372 x_oracle_error := 0;
373 x_msg_data := NULL;
374
375 /* Now call the check foreign key procedure */
376
377 Check_Foreign_Keys
378 (p_risk_phrase_code,
379 p_additional_text_indicator,
380 p_lookup_type,
381 p_lookup_code,
382 p_attribute_category,
383 p_attribute1,
384 p_attribute2,
385 p_attribute3,
386 p_attribute4,
387 p_attribute5,
388 p_attribute6,
389 p_attribute7,
390 p_attribute8,
391 p_attribute9,
392 p_attribute10,
393 p_attribute11,
394 p_attribute12,
395 p_attribute13,
396 p_attribute14,
397 p_attribute15,
398 p_attribute16,
399 p_attribute17,
400 p_attribute18,
404 p_attribute22,
401 p_attribute19,
402 p_attribute20,
403 p_attribute21,
405 p_attribute23,
406 p_attribute24,
407 p_attribute25,
408 p_attribute26,
409 p_attribute27,
410 p_attribute28,
411 p_attribute29,
412 p_attribute30,
413 l_return_status,
414 l_oracle_error,
415 l_msg_data);
416
417 IF l_return_status <> 'S' THEN
418 RAISE Foreign_Key_Error;
419 ELSE
420 UPDATE gr_risk_phrases_b
421 SET risk_phrase_code = p_risk_phrase_code,
422 additional_text_indicator = p_additional_text_indicator,
423 lookup_type = p_lookup_type,
424 lookup_code = p_lookup_code,
425 attribute_category = p_attribute_category,
426 attribute1 = p_attribute1,
427 attribute2 = p_attribute2,
428 attribute3 = p_attribute3,
429 attribute4 = p_attribute4,
430 attribute5 = p_attribute5,
431 attribute6 = p_attribute6,
432 attribute7 = p_attribute7,
433 attribute8 = p_attribute8,
434 attribute9 = p_attribute9,
435 attribute10 = p_attribute10,
436 attribute11 = p_attribute11,
437 attribute12 = p_attribute12,
438 attribute13 = p_attribute13,
439 attribute14 = p_attribute14,
440 attribute15 = p_attribute15,
441 attribute16 = p_attribute16,
442 attribute17 = p_attribute17,
443 attribute18 = p_attribute18,
444 attribute19 = p_attribute19,
445 attribute20 = p_attribute20,
446 attribute21 = p_attribute11,
447 attribute22 = p_attribute22,
448 attribute23 = p_attribute23,
449 attribute24 = p_attribute24,
450 attribute25 = p_attribute25,
451 attribute26 = p_attribute26,
452 attribute27 = p_attribute27,
453 attribute28 = p_attribute28,
454 attribute29 = p_attribute29,
455 attribute30 = p_attribute30,
456 created_by = p_created_by,
457 creation_date = p_creation_date,
458 last_updated_by = p_last_updated_by,
459 last_update_date = p_last_update_date,
460 last_update_login = p_last_update_login
461 WHERE rowid = p_rowid;
462 IF SQL%NOTFOUND THEN
463 RAISE Row_Missing_Error;
464 END IF;
465 END IF;
466
467 /* Check the commit flag and if set, then commit the work. */
468
469 IF FND_API.To_Boolean(p_commit) THEN
470 COMMIT WORK;
471 END IF;
472
473 EXCEPTION
474
475 WHEN Foreign_Key_Error THEN
476 ROLLBACK TO SAVEPOINT Update_Row;
477 x_return_status := l_return_status;
478 x_oracle_error := l_oracle_error;
479 FND_MESSAGE.SET_NAME('GR',
480 'GR_FOREIGN_KEY_ERROR');
481 FND_MESSAGE.SET_TOKEN('TEXT',
482 l_msg_data,
483 FALSE);
484 IF FND_API.To_Boolean(p_called_by_form) THEN
485 APP_EXCEPTION.Raise_Exception;
486 ELSE
487 x_msg_data := FND_MESSAGE.Get;
488 END IF;
489
490 WHEN Row_Missing_Error THEN
491 ROLLBACK TO SAVEPOINT Update_Row;
492 x_return_status := 'E';
493 x_oracle_error := APP_EXCEPTION.Get_Code;
494 FND_MESSAGE.SET_NAME('GR',
495 'GR_NO_RECORD_INSERTED');
496 FND_MESSAGE.SET_TOKEN('CODE',
497 p_risk_phrase_code,
498 FALSE);
499 IF FND_API.To_Boolean(p_called_by_form) THEN
500 APP_EXCEPTION.Raise_Exception;
501 ELSE
502 x_msg_data := FND_MESSAGE.Get;
503 END IF;
504
505 WHEN OTHERS THEN
506 ROLLBACK TO SAVEPOINT Update_Row;
507 x_return_status := 'U';
508 x_oracle_error := APP_EXCEPTION.Get_Code;
509 l_msg_data := APP_EXCEPTION.Get_Text;
510 FND_MESSAGE.SET_NAME('GR',
511 'GR_UNEXPECTED_ERROR');
512 FND_MESSAGE.SET_TOKEN('TEXT',
513 l_msg_data,
514 FALSE);
515 IF FND_API.To_Boolean(p_called_by_form) THEN
516 APP_EXCEPTION.Raise_Exception;
517 ELSE
518 x_msg_data := FND_MESSAGE.Get;
519 END IF;
520
521 END Update_Row;
522
523 PROCEDURE Lock_Row
524 (p_commit IN VARCHAR2,
525 p_called_by_form IN VARCHAR2,
526 p_rowid IN VARCHAR2,
527 p_risk_phrase_code IN VARCHAR2,
528 p_additional_text_indicator IN VARCHAR2,
529 p_lookup_type IN VARCHAR2,
530 p_lookup_code IN VARCHAR2,
531 p_attribute_category IN VARCHAR2,
532 p_attribute1 IN VARCHAR2,
533 p_attribute2 IN VARCHAR2,
534 p_attribute3 IN VARCHAR2,
535 p_attribute4 IN VARCHAR2,
536 p_attribute5 IN VARCHAR2,
537 p_attribute6 IN VARCHAR2,
538 p_attribute7 IN VARCHAR2,
539 p_attribute8 IN VARCHAR2,
540 p_attribute9 IN VARCHAR2,
541 p_attribute10 IN VARCHAR2,
542 p_attribute11 IN VARCHAR2,
543 p_attribute12 IN VARCHAR2,
544 p_attribute13 IN VARCHAR2,
545 p_attribute14 IN VARCHAR2,
546 p_attribute15 IN VARCHAR2,
547 p_attribute16 IN VARCHAR2,
548 p_attribute17 IN VARCHAR2,
549 p_attribute18 IN VARCHAR2,
550 p_attribute19 IN VARCHAR2,
551 p_attribute20 IN VARCHAR2,
552 p_attribute21 IN VARCHAR2,
553 p_attribute22 IN VARCHAR2,
554 p_attribute23 IN VARCHAR2,
555 p_attribute24 IN VARCHAR2,
556 p_attribute25 IN VARCHAR2,
557 p_attribute26 IN VARCHAR2,
558 p_attribute27 IN VARCHAR2,
559 p_attribute28 IN VARCHAR2,
560 p_attribute29 IN VARCHAR2,
561 p_attribute30 IN VARCHAR2,
562 p_created_by IN NUMBER,
563 p_creation_date IN DATE,
564 p_last_updated_by IN NUMBER,
565 p_last_update_date IN DATE,
566 p_last_update_login IN NUMBER,
567 x_return_status OUT NOCOPY VARCHAR2,
568 x_oracle_error OUT NOCOPY NUMBER,
569 x_msg_data OUT NOCOPY VARCHAR2)
570 IS
571
572 /* Alpha Variables */
573
574 L_RETURN_STATUS VARCHAR2(1) := 'S';
575 L_MSG_DATA VARCHAR2(2000);
576
577 /* Number Variables */
578
579 L_ORACLE_ERROR NUMBER;
580
581 /* Exceptions */
582
583 NO_DATA_FOUND_ERROR EXCEPTION;
584 RECORD_CHANGED_ERROR EXCEPTION;
585
586 /* Define the cursors */
587
588 CURSOR c_lock_risk
589 IS
590 SELECT last_update_date
591 FROM gr_risk_phrases_b
592 WHERE rowid = p_rowid
593 FOR UPDATE NOWAIT;
594 LockSafetyRcd c_lock_risk%ROWTYPE;
595 BEGIN
596
597 /* Initialization Routine */
598
599 SAVEPOINT Lock_Row;
600 x_return_status := 'S';
601 x_oracle_error := 0;
602 x_msg_data := NULL;
603
604 /* Now lock the record */
605
606 OPEN c_lock_risk;
607 FETCH c_lock_risk INTO LockSafetyRcd;
608 IF c_lock_risk%NOTFOUND THEN
609 CLOSE c_lock_risk;
610 RAISE No_Data_Found_Error;
611 END IF;
612 CLOSE c_lock_risk;
613
614 IF LockSafetyRcd.last_update_date <> p_last_update_date THEN
615 RAISE RECORD_CHANGED_ERROR;
616 END IF;
617
618 IF FND_API.To_Boolean(p_commit) THEN
619 COMMIT WORK;
620 END IF;
621
622 EXCEPTION
623
624 WHEN No_Data_Found_Error THEN
625 ROLLBACK TO SAVEPOINT Lock_Row;
626 x_return_status := 'E';
627 FND_MESSAGE.SET_NAME('GR',
628 'GR_RECORD_NOT_FOUND');
629 FND_MESSAGE.SET_TOKEN('CODE',
630 p_risk_phrase_code,
631 FALSE);
632 IF FND_API.To_Boolean(p_called_by_form) THEN
633 APP_EXCEPTION.Raise_Exception;
634 ELSE
635 x_msg_data := FND_MESSAGE.Get;
636 END IF;
637
638 WHEN RECORD_CHANGED_ERROR THEN
639 ROLLBACK TO SAVEPOINT Lock_Row;
640 X_return_status := 'E';
641 FND_MESSAGE.SET_NAME('FND',
642 'FORM_RECORD_CHANGED');
643 IF FND_API.To_Boolean(p_called_by_form) THEN
644 APP_EXCEPTION.Raise_Exception;
645 ELSE
646 x_msg_data := FND_MESSAGE.Get;
647 END IF;
648 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
649 ROLLBACK TO SAVEPOINT Lock_Row;
650 x_return_status := 'L';
651 x_oracle_error := APP_EXCEPTION.Get_Code;
652 IF NOT (FND_API.To_Boolean(p_called_by_form)) THEN
653 FND_MESSAGE.SET_NAME('GR',
654 'GR_ROW_IS_LOCKED');
655 x_msg_data := FND_MESSAGE.Get;
656 END IF;
657
658
659 WHEN OTHERS THEN
660 ROLLBACK TO SAVEPOINT Lock_Row;
661 x_return_status := 'U';
662 x_oracle_error := APP_EXCEPTION.Get_Code;
663 l_msg_data := APP_EXCEPTION.Get_Text;
664 FND_MESSAGE.SET_NAME('GR',
665 'GR_UNEXPECTED_ERROR');
666 FND_MESSAGE.SET_TOKEN('TEXT',
667 l_msg_data,
668 FALSE);
669 IF FND_API.To_Boolean(p_called_by_form) THEN
670 APP_EXCEPTION.Raise_Exception;
671 ELSE
672 x_msg_data := FND_MESSAGE.Get;
673 END IF;
674
675 END Lock_Row;
676
677 PROCEDURE Delete_Row
678 (p_commit IN VARCHAR2,
679 p_called_by_form IN VARCHAR2,
680 p_rowid IN VARCHAR2,
681 p_risk_phrase_code IN VARCHAR2,
682 p_additional_text_indicator IN VARCHAR2,
683 p_lookup_type IN VARCHAR2,
684 p_lookup_code IN VARCHAR2,
685 p_attribute_category IN VARCHAR2,
686 p_attribute1 IN VARCHAR2,
687 p_attribute2 IN VARCHAR2,
688 p_attribute3 IN VARCHAR2,
689 p_attribute4 IN VARCHAR2,
690 p_attribute5 IN VARCHAR2,
691 p_attribute6 IN VARCHAR2,
692 p_attribute7 IN VARCHAR2,
693 p_attribute8 IN VARCHAR2,
694 p_attribute9 IN VARCHAR2,
695 p_attribute10 IN VARCHAR2,
696 p_attribute11 IN VARCHAR2,
697 p_attribute12 IN VARCHAR2,
698 p_attribute13 IN VARCHAR2,
699 p_attribute14 IN VARCHAR2,
700 p_attribute15 IN VARCHAR2,
701 p_attribute16 IN VARCHAR2,
702 p_attribute17 IN VARCHAR2,
703 p_attribute18 IN VARCHAR2,
704 p_attribute19 IN VARCHAR2,
705 p_attribute20 IN VARCHAR2,
706 p_attribute21 IN VARCHAR2,
707 p_attribute22 IN VARCHAR2,
708 p_attribute23 IN VARCHAR2,
709 p_attribute24 IN VARCHAR2,
710 p_attribute25 IN VARCHAR2,
711 p_attribute26 IN VARCHAR2,
712 p_attribute27 IN VARCHAR2,
713 p_attribute28 IN VARCHAR2,
714 p_attribute29 IN VARCHAR2,
715 p_attribute30 IN VARCHAR2,
716 p_created_by IN NUMBER,
717 p_creation_date IN DATE,
718 p_last_updated_by IN NUMBER,
719 p_last_update_date IN DATE,
720 p_last_update_login IN NUMBER,
724 IS
721 x_return_status OUT NOCOPY VARCHAR2,
722 x_oracle_error OUT NOCOPY NUMBER,
723 x_msg_data OUT NOCOPY VARCHAR2)
725
726 /* Alpha Variables */
727
728 L_RETURN_STATUS VARCHAR2(1) := 'S';
729 L_MSG_DATA VARCHAR2(2000);
730 L_CALLED_BY_FORM VARCHAR2(1);
731
732 /* Number Variables */
733
734 L_ORACLE_ERROR NUMBER;
735
736 /* Exceptions */
737
738 CHECK_INTEGRITY_ERROR EXCEPTION;
739 ROW_MISSING_ERROR EXCEPTION;
740 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
741 BEGIN
742
743 /* Initialization Routine */
744
745 SAVEPOINT Delete_Row;
746 x_return_status := 'S';
747 l_called_by_form := 'F';
748 x_oracle_error := 0;
749 x_msg_data := NULL;
750
751 /* Now call the check integrity procedure */
752
753 Check_Integrity
754 (l_called_by_form,
755 p_risk_phrase_code,
756 p_additional_text_indicator,
757 p_lookup_type,
758 p_lookup_code,
759 p_attribute_category,
760 p_attribute1,
761 p_attribute2,
762 p_attribute3,
763 p_attribute4,
764 p_attribute5,
765 p_attribute6,
766 p_attribute7,
767 p_attribute8,
768 p_attribute9,
769 p_attribute10,
770 p_attribute11,
771 p_attribute12,
772 p_attribute13,
773 p_attribute14,
774 p_attribute15,
775 p_attribute16,
776 p_attribute17,
777 p_attribute18,
778 p_attribute19,
779 p_attribute20,
780 p_attribute21,
781 p_attribute22,
782 p_attribute23,
783 p_attribute24,
784 p_attribute25,
785 p_attribute26,
786 p_attribute27,
787 p_attribute28,
788 p_attribute29,
789 p_attribute30,
790 l_return_status,
791 l_oracle_error,
792 l_msg_data);
793
794 IF l_return_status <> 'S' THEN
795 RAISE Check_Integrity_Error;
796 END IF;
797
798 DELETE FROM gr_risk_phrases_b
799 WHERE rowid = p_rowid;
800
801 /* Check the commit flag and if set, then commit the work. */
802
803 IF FND_API.TO_Boolean(p_commit) THEN
804 COMMIT WORK;
805 END IF;
806
807 EXCEPTION
808
809 WHEN Check_Integrity_Error THEN
810 ROLLBACK TO SAVEPOINT Delete_Row;
811 x_return_status := l_return_status;
812 x_oracle_error := l_oracle_error;
813 x_msg_data := l_msg_data;
814 IF FND_API.To_Boolean(p_called_by_form) THEN
815 APP_EXCEPTION.Raise_Exception;
816 END IF;
817
818 WHEN Row_Missing_Error THEN
819 ROLLBACK TO SAVEPOINT Delete_Row;
820 x_return_status := 'E';
821 x_oracle_error := APP_EXCEPTION.Get_Code;
822 FND_MESSAGE.SET_NAME('GR',
823 'GR_RECORD_NOT_FOUND');
824 FND_MESSAGE.SET_TOKEN('CODE',
825 p_risk_phrase_code,
826 FALSE);
827 IF FND_API.To_Boolean(p_called_by_form) THEN
828 APP_EXCEPTION.Raise_Exception;
829 ELSE
830 x_msg_data := FND_MESSAGE.Get;
831 END IF;
832
833 WHEN OTHERS THEN
834 ROLLBACK TO SAVEPOINT Delete_Row;
835 x_return_status := 'U';
836 x_oracle_error := APP_EXCEPTION.Get_Code;
837 l_msg_data := APP_EXCEPTION.Get_Text;
838 l_msg_data := APP_EXCEPTION.Get_Text;
839 FND_MESSAGE.SET_NAME('GR',
840 'GR_UNEXPECTED_ERROR');
841 FND_MESSAGE.SET_TOKEN('TEXT',
842 l_msg_data,
843 FALSE);
844 IF FND_API.To_Boolean(p_called_by_form) THEN
845 APP_EXCEPTION.Raise_Exception;
846 ELSE
847 x_msg_data := FND_MESSAGE.Get;
848 END IF;
849
850 END Delete_Row;
851
852 PROCEDURE Check_Foreign_Keys
853 (p_risk_phrase_code IN VARCHAR2,
854 p_additional_text_indicator IN VARCHAR2,
855 p_lookup_type IN VARCHAR2,
856 p_lookup_code IN VARCHAR2,
857 p_attribute_category IN VARCHAR2,
858 p_attribute1 IN VARCHAR2,
859 p_attribute2 IN VARCHAR2,
860 p_attribute3 IN VARCHAR2,
861 p_attribute4 IN VARCHAR2,
862 p_attribute5 IN VARCHAR2,
863 p_attribute6 IN VARCHAR2,
864 p_attribute7 IN VARCHAR2,
865 p_attribute8 IN VARCHAR2,
866 p_attribute9 IN VARCHAR2,
867 p_attribute10 IN VARCHAR2,
868 p_attribute11 IN VARCHAR2,
869 p_attribute12 IN VARCHAR2,
870 p_attribute13 IN VARCHAR2,
871 p_attribute14 IN VARCHAR2,
872 p_attribute15 IN VARCHAR2,
873 p_attribute16 IN VARCHAR2,
874 p_attribute17 IN VARCHAR2,
875 p_attribute18 IN VARCHAR2,
876 p_attribute19 IN VARCHAR2,
877 p_attribute20 IN VARCHAR2,
878 p_attribute21 IN VARCHAR2,
879 p_attribute22 IN VARCHAR2,
880 p_attribute23 IN VARCHAR2,
881 p_attribute24 IN VARCHAR2,
882 p_attribute25 IN VARCHAR2,
883 p_attribute26 IN VARCHAR2,
884 p_attribute27 IN VARCHAR2,
885 p_attribute28 IN VARCHAR2,
886 p_attribute29 IN VARCHAR2,
887 p_attribute30 IN VARCHAR2,
888 x_return_status OUT NOCOPY VARCHAR2,
889 x_oracle_error OUT NOCOPY NUMBER,
890 x_msg_data OUT NOCOPY VARCHAR2)
891 IS
892
893 /* Alpha Variables */
894
895 L_RETURN_STATUS VARCHAR2(1) := 'S';
896 L_MSG_DATA VARCHAR2(2000);
897
898 /* Number Variables */
899
903
900 L_ORACLE_ERROR NUMBER;
901
902 /* Define the cursors */
904 BEGIN
905
906 /* Initialization Routine */
907
908 SAVEPOINT Check_Foreign_Keys;
909 x_return_status := 'S';
910 x_oracle_error := 0;
911 x_msg_data := NULL;
912
913 /* No foreign key references to check */
914
915
916 /* Check the not null columns */
917
918 IF p_additional_text_indicator IS NULL THEN
919 x_return_status := 'E';
920 l_msg_data := l_msg_data || ' Additional Text Indicator cannot be null.';
921 END IF;
922
923 IF x_return_status <> 'S' THEN
924 x_msg_data := l_msg_data;
925 END IF;
926
927 EXCEPTION
928
929 WHEN OTHERS THEN
930 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
931 x_return_status := 'U';
932 x_oracle_error := APP_EXCEPTION.Get_Code;
933 l_msg_data := APP_EXCEPTION.Get_Text;
934 FND_MESSAGE.SET_NAME('GR',
935 'GR_UNEXPECTED_ERROR');
936 FND_MESSAGE.SET_TOKEN('TEXT',
937 l_msg_data,
938 FALSE);
939
940 END Check_Foreign_Keys;
941
942 PROCEDURE Check_Integrity
943 (p_called_by_form IN VARCHAR2,
944 p_risk_phrase_code IN VARCHAR2,
945 p_additional_text_indicator IN VARCHAR2,
946 p_lookup_type IN VARCHAR2,
947 p_lookup_code IN VARCHAR2,
948 p_attribute_category IN VARCHAR2,
949 p_attribute1 IN VARCHAR2,
950 p_attribute2 IN VARCHAR2,
951 p_attribute3 IN VARCHAR2,
952 p_attribute4 IN VARCHAR2,
953 p_attribute5 IN VARCHAR2,
954 p_attribute6 IN VARCHAR2,
955 p_attribute7 IN VARCHAR2,
956 p_attribute8 IN VARCHAR2,
957 p_attribute9 IN VARCHAR2,
958 p_attribute10 IN VARCHAR2,
959 p_attribute11 IN VARCHAR2,
960 p_attribute12 IN VARCHAR2,
961 p_attribute13 IN VARCHAR2,
962 p_attribute14 IN VARCHAR2,
963 p_attribute15 IN VARCHAR2,
964 p_attribute16 IN VARCHAR2,
965 p_attribute17 IN VARCHAR2,
966 p_attribute18 IN VARCHAR2,
967 p_attribute19 IN VARCHAR2,
968 p_attribute20 IN VARCHAR2,
969 p_attribute21 IN VARCHAR2,
970 p_attribute22 IN VARCHAR2,
971 p_attribute23 IN VARCHAR2,
972 p_attribute24 IN VARCHAR2,
973 p_attribute25 IN VARCHAR2,
974 p_attribute26 IN VARCHAR2,
975 p_attribute27 IN VARCHAR2,
976 p_attribute28 IN VARCHAR2,
977 p_attribute29 IN VARCHAR2,
978 p_attribute30 IN VARCHAR2,
979 x_return_status OUT NOCOPY VARCHAR2,
980 x_oracle_error OUT NOCOPY NUMBER,
981 x_msg_data OUT NOCOPY VARCHAR2)
982 IS
983
984 /* Alpha Variables */
985
986 L_RETURN_STATUS VARCHAR2(1) := 'S';
987 L_MSG_DATA VARCHAR2(2000);
988 L_CODE_BLOCK VARCHAR2(100);
989
990 /* Number Variables */
991
992 L_ORACLE_ERROR NUMBER;
993 L_RECORD_COUNT NUMBER;
994
995 /* Exceptions */
996 INTEGRITY_ERROR EXCEPTION;
997
998 /* Define the Cursors */
999
1000 /* Item Risk Phrases table */
1001
1002 CURSOR c_get_item_risk_phrases
1003 IS
1004 SELECT COUNT(*)
1005 FROM gr_item_risk_phrases irp
1006 WHERE irp.risk_phrase_code = p_risk_phrase_code;
1007
1008 /* Work Classification Risks */
1009
1010 CURSOR c_get_work_classn_risks
1011 IS
1012 SELECT COUNT(*)
1013 FROM GR_WORK_CLASSN_RISKS
1014 WHERE RISK_PHRASE_CODE = P_RISK_PHRASE_CODE;
1015
1016 /* Work Additive Risks */
1017
1018 CURSOR c_get_work_additive_risks
1019 IS
1020 SELECT COUNT(*)
1021 FROM GR_WORK_ADDITIVE_RISKS
1022 WHERE RISK_PHRASE_CODE = P_RISK_PHRASE_CODE;
1023
1024 /* Add Mandatory Risks */
1025
1026 CURSOR c_get_add_mandatory_risks
1027 IS
1028 SELECT COUNT(*)
1029 FROM GR_ADD_MANDATORY_RISKS
1030 WHERE RISK_PHRASE_CODE = P_RISK_PHRASE_CODE;
1031
1032 /* Ein Asl Risks */
1033
1034 CURSOR c_get_ein_asl_risks
1035 IS
1036 SELECT COUNT(*)
1037 FROM GR_EIN_ASL_RISKS
1038 WHERE RISK_PHRASE_CODE = P_RISK_PHRASE_CODE;
1039
1040 /* Euro Mandatory Risks */
1041
1042 CURSOR c_get_euro_mandatory_risks
1043 IS
1044 SELECT COUNT(*)
1045 FROM GR_EURO_MANDATORY_RISKS
1046 WHERE RISK_PHRASE_CODE = P_RISK_PHRASE_CODE;
1047
1048 /* Linked Risks */
1049
1050 CURSOR c_get_linked_risks
1051 IS
1052 SELECT COUNT(*)
1053 FROM GR_LINKED_RISKS
1054 WHERE RISK_PHRASE_CODE = P_RISK_PHRASE_CODE;
1055
1056 /* Risk Calculations */
1057
1058 CURSOR c_get_risk_calculations
1059 IS
1060 SELECT COUNT(*)
1061 FROM GR_RISK_CALCULATIONS
1062 WHERE RISK_PHRASE_CODE = P_RISK_PHRASE_CODE;
1063
1064 /* Source Risks */
1065
1066 CURSOR c_get_source_risks
1067 IS
1068 SELECT COUNT(*)
1069 FROM GR_SOURCE_RISKS
1070 WHERE RISK_PHRASE_CODE = P_RISK_PHRASE_CODE;
1071
1072
1073 BEGIN
1074
1075 /* Initialization Routine */
1076
1077 SAVEPOINT Check_Integrity;
1078 x_return_status := 'S';
1079 x_oracle_error := 0;
1080 x_msg_data := NULL;
1081
1082 /* Now read the cursors to make sure the risk phrase code isn't used. */
1083
1084 /* Work Classn Risks */
1085
1086 l_record_count := 0;
1087 l_code_block := 'c_get_work_classn_risks';
1088 OPEN c_get_work_classn_risks;
1089 FETCH c_get_work_classn_risks INTO l_record_count;
1093 END IF;
1090 IF l_record_count <> 0 THEN
1091 l_return_status := 'E';
1092 l_msg_data := l_msg_data || 'gr_work_classn_risks, ';
1094 CLOSE c_get_work_classn_risks;
1095
1096
1097 /* Work Additive Risks */
1098
1099 l_record_count := 0;
1100 l_code_block := 'c_get_work_additive_risks';
1101 OPEN c_get_work_additive_risks;
1102 FETCH c_get_work_additive_risks INTO l_record_count;
1103 IF l_record_count <> 0 THEN
1104 l_return_status := 'E';
1105 l_msg_data := l_msg_data || 'gr_work_additive_risks, ';
1106 END IF;
1107 CLOSE c_get_work_additive_risks;
1108
1109
1110 /* Add Mandatory Risks */
1111
1112 l_record_count := 0;
1113 l_code_block := 'c_get_add_mandatory_risks';
1114 OPEN c_get_add_mandatory_risks;
1115 FETCH c_get_add_mandatory_risks INTO l_record_count;
1116 IF l_record_count <> 0 THEN
1117 l_return_status := 'E';
1118 l_msg_data := l_msg_data || 'gr_add_mandatory_risks, ';
1119 END IF;
1120 CLOSE c_get_add_mandatory_risks;
1121
1122
1123 /* Ein Asl Risks */
1124
1125 l_record_count := 0;
1126 l_code_block := 'c_get_ein_asl_risks';
1127 OPEN c_get_ein_asl_risks;
1128 FETCH c_get_ein_asl_risks INTO l_record_count;
1129 IF l_record_count <> 0 THEN
1130 l_return_status := 'E';
1131 l_msg_data := l_msg_data || 'gr_ein_asl_risks, ';
1132 END IF;
1133 CLOSE c_get_ein_asl_risks;
1134
1135
1136 /* Euro Mandatory Risks */
1137
1138 l_record_count := 0;
1139 l_code_block := 'c_get_euro_mandatory_risks';
1140 OPEN c_get_euro_mandatory_risks;
1141 FETCH c_get_euro_mandatory_risks INTO l_record_count;
1142 IF l_record_count <> 0 THEN
1143 l_return_status := 'E';
1144 l_msg_data := l_msg_data || 'gr_euro_mandatory_risks, ';
1145 END IF;
1146 CLOSE c_get_euro_mandatory_risks;
1147
1148
1149 /* Linked Risks */
1150
1151 l_record_count := 0;
1152 l_code_block := 'c_get_linked_risks';
1153 OPEN c_get_linked_risks;
1154 FETCH c_get_linked_risks INTO l_record_count;
1155 IF l_record_count <> 0 THEN
1156 l_return_status := 'E';
1157 l_msg_data := l_msg_data || 'gr_linked_risks, ';
1158 END IF;
1159 CLOSE c_get_linked_risks;
1160
1161
1162 /* Risk Calculations */
1163
1164 l_record_count := 0;
1165 l_code_block := 'c_get_risk_calculations';
1166 OPEN c_get_risk_calculations;
1167 FETCH c_get_risk_calculations INTO l_record_count;
1168 IF l_record_count <> 0 THEN
1169 l_return_status := 'E';
1170 l_msg_data := l_msg_data || 'gr_risk_calculations, ';
1171 END IF;
1172 CLOSE c_get_risk_calculations;
1173
1174
1175 /* Source Risks */
1176
1177 l_record_count := 0;
1178 l_code_block := 'c_get_source_risks';
1179 OPEN c_get_source_risks;
1180 FETCH c_get_source_risks INTO l_record_count;
1181 IF l_record_count <> 0 THEN
1182 l_return_status := 'E';
1183 l_msg_data := l_msg_data || 'gr_source_risks, ';
1184 END IF;
1185 CLOSE c_get_source_risks;
1186
1187 /* Item Risk Phrases */
1188
1189 l_record_count := 0;
1190 l_code_block := 'c_get_item_risk_phrases';
1191 OPEN c_get_item_risk_phrases;
1192 FETCH c_get_item_risk_phrases INTO l_record_count;
1193 IF l_record_count <> 0 THEN
1194 l_return_status := 'E';
1195 l_msg_data := l_msg_data || 'gr_item_risk_phrases, ';
1196 END IF;
1197 CLOSE c_get_item_risk_phrases;
1198
1199
1200 /* Now sort out the error messaging */
1201
1202 IF l_return_status <> 'S' THEN
1203 RAISE INTEGRITY_ERROR;
1204 END IF;
1205
1206 EXCEPTION
1207 WHEN INTEGRITY_ERROR THEN
1208 x_return_status := 'E';
1209 FND_MESSAGE.SET_NAME('GR',
1210 'GR_INTEGRITY_HEADER');
1211 FND_MESSAGE.SET_TOKEN('CODE',
1212 p_risk_phrase_code,
1213 FALSE);
1214 FND_MESSAGE.SET_TOKEN('TABLES',
1215 SUBSTR(l_msg_data,1,LENGTH(l_msg_data)-1),
1216 FALSE);
1217 IF FND_API.To_Boolean(p_called_by_form) THEN
1218 APP_EXCEPTION.Raise_Exception;
1219 ELSE
1220 x_msg_data := FND_MESSAGE.Get;
1221 END IF;
1222
1223 WHEN OTHERS THEN
1224 ROLLBACK TO SAVEPOINT Check_Integrity;
1225 x_return_status := 'U';
1226 x_oracle_error := APP_EXCEPTION.Get_Code;
1227 l_msg_data := APP_EXCEPTION.Get_Text;
1228 FND_MESSAGE.SET_NAME('GR',
1229 'GR_UNEXPECTED_ERROR');
1230 FND_MESSAGE.SET_TOKEN('TEXT',
1231 l_msg_data,
1232 FALSE);
1233 IF FND_API.To_Boolean(p_called_by_form) THEN
1234 APP_EXCEPTION.Raise_Exception;
1235 ELSE
1236 x_msg_data := FND_MESSAGE.Get;
1237 END IF;
1238
1239 END Check_Integrity;
1240
1241 PROCEDURE Check_Primary_Key
1242 /* p_risk_phrase_code is the risk phrase code to check.
1243 ** p_called_by_form is 'T' if called by a form or 'F' if not.
1244 ** x_rowid is the row id of the record if found.
1245 ** x_key_exists is 'T' is the record is found, 'F' if not.
1246 */
1247 (p_risk_phrase_code IN VARCHAR2,
1248 p_called_by_form IN VARCHAR2,
1249 x_rowid OUT NOCOPY VARCHAR2,
1250 x_key_exists OUT NOCOPY VARCHAR2)
1251 IS
1252 /* Alphanumeric variables */
1253
1254 L_MSG_DATA VARCHAR2(80);
1255
1256 /* Declare any variables and the cursor */
1257
1258
1259 CURSOR c_get_risk_rowid
1260 IS
1261 SELECT sp.rowid
1262 FROM gr_risk_phrases_b sp
1266 BEGIN
1263 WHERE sp.risk_phrase_code = p_risk_phrase_code;
1264 RiskRecord c_get_risk_rowid%ROWTYPE;
1265
1267
1268 x_key_exists := 'F';
1269 OPEN c_get_risk_rowid;
1270 FETCH c_get_risk_rowid INTO RiskRecord;
1271 IF c_get_risk_rowid%FOUND THEN
1272 x_key_exists := 'T';
1273 x_rowid := RiskRecord.rowid;
1274 ELSE
1275 x_key_exists := 'F';
1276 END IF;
1277 CLOSE c_get_risk_rowid;
1278
1279 EXCEPTION
1280
1281 WHEN Others THEN
1282 l_msg_data := APP_EXCEPTION.Get_Text;
1283 FND_MESSAGE.SET_NAME('GR',
1284 'GR_UNEXPECTED_ERROR');
1285 FND_MESSAGE.SET_TOKEN('TEXT',
1286 l_msg_data,
1287 FALSE);
1288 IF FND_API.To_Boolean(p_called_by_form) THEN
1289 APP_EXCEPTION.Raise_Exception;
1290 END IF;
1291
1292 END Check_Primary_Key;
1293
1294 END GR_RISK_PHRASES_B_PKG;