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