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