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