[Home] [Help]
PACKAGE BODY: APPS.GR_EIN_NUMBERS_TL_PKG
Source
1 PACKAGE BODY GR_EIN_NUMBERS_TL_PKG AS
2 /*$Header: GRHIEITB.pls 115.4 2003/08/05 18:04:35 gkelly noship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_european_index_number IN VARCHAR2,
7 p_language IN VARCHAR2,
8 p_description IN VARCHAR2,
9 p_source_lang IN VARCHAR2,
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
21 /* Alpha Variables */
22
23 L_RETURN_STATUS VARCHAR2(1) := 'S';
24 L_KEY_EXISTS VARCHAR2(1);
25 L_MSG_DATA VARCHAR2(2000);
26 L_ROWID VARCHAR2(18);
27
28 /* Number Variables */
29
30 L_ORACLE_ERROR NUMBER;
31
32 /* Exceptions */
33
34 FOREIGN_KEY_ERROR EXCEPTION;
35 ITEM_EXISTS_ERROR EXCEPTION;
36 ROW_MISSING_ERROR EXCEPTION;
37
38 /* Declare cursors */
39
40
41 BEGIN
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_european_index_number,
53 p_language,
54 p_source_lang,
55 l_return_status,
56 l_oracle_error,
57 l_msg_data);
58
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_european_index_number,
67 p_language,
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_ein_numbers_tl
77 (european_index_number,
78 language,
79 description,
80 source_lang,
81 created_by,
82 creation_date,
83 last_updated_by,
84 last_update_date,
85 last_update_login)
86 VALUES
87 (p_european_index_number,
88 p_language,
89 p_description,
90 p_source_lang,
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_european_index_number,
101 p_language,
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_european_index_number,
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_european_index_number,
158 FALSE);
159 x_msg_data := FND_MESSAGE.Get;
160 IF FND_API.To_Boolean(p_called_by_form) THEN
161 APP_EXCEPTION.Raise_Exception;
162 ELSE
163 x_msg_data := FND_MESSAGE.Get;
164 END IF;
165
166 WHEN OTHERS THEN
167 ROLLBACK TO SAVEPOINT Insert_Row;
168 x_return_status := 'U';
169 x_oracle_error := SQLCODE;
170 l_msg_data := SUBSTR(sqlerrm, 1, 200);
171 FND_MESSAGE.SET_NAME('GR',
172 'GR_UNEXPECTED_ERROR');
173 FND_MESSAGE.SET_TOKEN('TEXT',
174 l_msg_data,
175 FALSE);
176 IF FND_API.To_Boolean(p_called_by_form) THEN
177 APP_EXCEPTION.Raise_Exception;
178 ELSE
179 x_msg_data := FND_MESSAGE.Get;
180 END IF;
181
182 END Insert_Row;
183
184 PROCEDURE Update_Row
185 (p_commit IN VARCHAR2,
186 p_called_by_form IN VARCHAR2,
187 p_rowid IN VARCHAR2,
188 p_european_index_number IN VARCHAR2,
189 p_language IN VARCHAR2,
190 p_description IN VARCHAR2,
191 p_source_lang IN VARCHAR2,
192 p_created_by IN NUMBER,
193 p_creation_date IN DATE,
194 p_last_updated_by IN NUMBER,
195 p_last_update_date IN DATE,
196 p_last_update_login IN NUMBER,
197 x_return_status OUT NOCOPY VARCHAR2,
198 x_oracle_error OUT NOCOPY NUMBER,
199 x_msg_data OUT NOCOPY VARCHAR2)
200 IS
201
202 /* Alpha Variables */
203
204 L_RETURN_STATUS VARCHAR2(1) := 'S';
205 L_MSG_DATA VARCHAR2(2000);
206
207 /* Number Variables */
208
209 L_ORACLE_ERROR NUMBER;
210
211 /* Exceptions */
212
213 FOREIGN_KEY_ERROR EXCEPTION;
214 ROW_MISSING_ERROR EXCEPTION;
215
216 BEGIN
217
218 /* Initialization Routine */
219
220 SAVEPOINT Update_Row;
221 x_return_status := 'S';
222 x_oracle_error := 0;
223 x_msg_data := NULL;
224
225 /* Now call the check foreign key procedure */
226
227 Check_Foreign_Keys
228 (p_european_index_number,
229 p_language,
230 p_source_lang,
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_ein_numbers_tl
239 SET european_index_number = p_european_index_number,
240 language = p_language,
241 description = p_description,
242 source_lang = p_source_lang,
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
250 IF SQL%NOTFOUND THEN
251 RAISE Row_Missing_Error;
252 END IF;
253 END IF;
254
255 /* Check if do the commit */
256
257 IF FND_API.To_Boolean(p_commit) THEN
258 COMMIT WORK;
259 END IF;
260
261 EXCEPTION
262
263 WHEN Foreign_Key_Error THEN
264 ROLLBACK TO SAVEPOINT Update_Row;
265 x_return_status := l_return_status;
266 x_oracle_error := l_oracle_error;
267 FND_MESSAGE.SET_NAME('GR',
268 'GR_FOREIGN_KEY_ERROR');
269 FND_MESSAGE.SET_TOKEN('TEXT',
270 l_msg_data,
271 FALSE);
272 IF FND_API.To_Boolean(p_called_by_form) THEN
273 APP_EXCEPTION.Raise_Exception;
274 ELSE
275 x_msg_data := FND_MESSAGE.Get;
276 END IF;
277
278 WHEN Row_Missing_Error THEN
279 ROLLBACK TO SAVEPOINT Update_Row;
280 x_return_status := 'E';
281 x_oracle_error := APP_EXCEPTION.Get_Code;
282 FND_MESSAGE.SET_NAME('GR',
283 'GR_NO_RECORD_INSERTED');
284 FND_MESSAGE.SET_TOKEN('CODE',
285 p_european_index_number,
286 FALSE);
287 IF FND_API.To_Boolean(p_called_by_form) THEN
288 APP_EXCEPTION.Raise_Exception;
289 ELSE
290 x_msg_data := FND_MESSAGE.Get;
291 END IF;
292
293 WHEN OTHERS THEN
294 ROLLBACK TO SAVEPOINT Update_Row;
295 x_return_status := 'U';
296 x_oracle_error := SQLCODE;
297 l_msg_data := SUBSTR(sqlerrm, 1, 200);
298 FND_MESSAGE.SET_NAME('GR',
299 'GR_UNEXPECTED_ERROR');
300 FND_MESSAGE.SET_TOKEN('TEXT',
301 l_msg_data,
302 FALSE);
303 IF FND_API.To_Boolean(p_called_by_form) THEN
304 APP_EXCEPTION.Raise_Exception;
305 ELSE
306 x_msg_data := FND_MESSAGE.Get;
307 END IF;
308
309 END Update_Row;
310
311 PROCEDURE Add_Language
312 (p_commit IN VARCHAR2,
313 p_called_by_form IN VARCHAR2,
314 p_european_index_number IN VARCHAR2,
315 p_language IN VARCHAR2,
316 x_return_status OUT NOCOPY VARCHAR2,
317 x_oracle_error OUT NOCOPY NUMBER,
318 x_msg_data OUT NOCOPY VARCHAR2)
319 IS
320
321 /* Alpha Variables */
322
323 L_RETURN_STATUS VARCHAR2(1) := 'S';
324 L_MSG_DATA VARCHAR2(2000);
325 L_MSG_TOKEN VARCHAR2(30);
326 L_BASE_DESC VARCHAR2(240);
327 L_LANGUAGE VARCHAR2(4);
328 L_CREATION_DATE DATE;
329 L_LAST_UPDATE_DATE DATE;
330
331 /* Number Variables */
332
333 L_ORACLE_ERROR NUMBER;
334 L_CREATED_BY NUMBER;
335 L_LAST_UPDATED_BY NUMBER;
336 L_LAST_UPDATE_LOGIN NUMBER;
337
338 /* Exceptions */
339
340 LANGUAGE_MISSING_ERROR EXCEPTION;
341
342 /* Cursors */
343
344 CURSOR c_get_descs
345 IS
346 SELECT eit.description,
347 eit.created_by,
348 eit.creation_date,
349 eit.last_updated_by,
350 eit.last_update_date,
351 eit.last_update_login
352 FROM gr_ein_numbers_tl eit
353 WHERE eit.european_index_number = p_european_index_number
354 AND eit.language = l_language;
355 TypeDesc c_get_descs%ROWTYPE;
356
357 CURSOR c_get_installed_languages
358 IS
359 SELECT lng.language_code
360 FROM fnd_languages lng
361 WHERE lng.installed_flag IN ('I', 'B');
362 InstLang c_get_installed_languages%ROWTYPE;
363
364 BEGIN
365
366 /* Initialization Routine */
367
368 SAVEPOINT Add_Language;
369 x_return_status := 'S';
370 x_oracle_error := 0;
371 x_msg_data := NULL;
372 l_msg_token := p_european_index_number || ' ' || p_language;
373
374 /* Remove translations with no base row */
375
376 delete from GR_EIN_NUMBERS_TL T
377 where not exists
378 (select NULL
379 from GR_EIN_NUMBERS_B B
380 where B.EUROPEAN_INDEX_NUMBER = T.EUROPEAN_INDEX_NUMBER
381 );
382
383 /* Redefault translations from the source language */
384
385 update gr_ein_numbers_tl t set (
386 description ) =
387 ( select
388 B.DESCRIPTION
389 from GR_EIN_NUMBERS_TL B
390 where B.EUROPEAN_INDEX_NUMBER = T.EUROPEAN_INDEX_NUMBER
391 and B.LANGUAGE = T.SOURCE_LANG)
392 where (
393 T.EUROPEAN_INDEX_NUMBER,
394 T.LANGUAGE
395 ) in (select
396 SUBT.EUROPEAN_INDEX_NUMBER,
397 SUBT.LANGUAGE
398 from GR_EIN_NUMBERS_TL SUBB, GR_EIN_NUMBERS_TL SUBT
399 where SUBB.EUROPEAN_INDEX_NUMBER = SUBT.EUROPEAN_INDEX_NUMBER
400 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
401 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
402 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
403 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
404 ));
405
406 /* Open the language cursor and get the description entered from the
407 ** user environment variable.
408 */
409 l_language := p_language;
410 OPEN c_get_descs;
411 FETCH c_get_descs INTO TypeDesc;
412 IF c_get_descs%NOTFOUND THEN
413 CLOSE c_get_descs;
414 RAISE Language_Missing_Error;
415 ELSE
416 l_base_desc := TypeDesc.description;
417 l_created_by := TypeDesc.created_by;
418 l_creation_date := TypeDesc.creation_date;
419 l_last_updated_by := TypeDesc.last_updated_by;
420 l_last_update_date := TypeDesc.last_update_date;
421 l_last_update_login := TypeDesc.last_update_login;
422 CLOSE c_get_descs;
423 END IF;
424
425 /* Read fnd_languages for the installed and base languages.
426 ** For those that are found, read the types tl table.
427 ** If there isn't a record in the table for that language then
428 ** insert it and go on to the next.
429 */
430 OPEN c_get_installed_languages;
431 FETCH c_get_installed_languages INTO InstLang;
432 IF c_get_installed_languages%FOUND THEN
433 WHILE c_get_installed_languages%FOUND LOOP
434 IF InstLang.language_code <> p_language THEN
435 l_language := InstLang.language_code;
436 OPEN c_get_descs;
437 FETCH c_get_descs INTO TypeDesc;
438 IF c_get_descs%NOTFOUND THEN
439 CLOSE c_get_descs;
440 INSERT INTO gr_ein_numbers_tl
441 (european_index_number,
442 language,
443 description,
444 source_lang,
445 created_by,
446 creation_date,
447 last_updated_by,
448 last_update_date,
449 last_update_login)
450 VALUES
451 (p_european_index_number,
452 l_language,
453 l_base_desc,
454 p_language,
455 l_created_by,
456 l_creation_date,
457 l_last_updated_by,
458 l_last_update_date,
459 l_last_update_login);
460 ELSE
461 CLOSE c_get_descs;
462 END IF;
463 END IF;
464 FETCH c_get_installed_languages INTO InstLang;
465 END LOOP;
466 END IF;
467 CLOSE c_get_installed_languages;
468
469 IF FND_API.To_Boolean(p_commit) THEN
470 COMMIT WORK;
471 END IF;
472
473 EXCEPTION
474
475 WHEN Language_Missing_Error THEN
476 ROLLBACK TO SAVEPOINT Add_Language;
477 x_return_status := 'E';
478 x_oracle_error := APP_EXCEPTION.Get_Code;
479 FND_MESSAGE.SET_NAME('GR',
480 'GR_RECORD_NOT_FOUND');
481 FND_MESSAGE.SET_TOKEN('CODE',
482 l_msg_token,
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 OTHERS THEN
491 ROLLBACK TO SAVEPOINT Add_Language;
492 x_return_status := 'U';
493 x_oracle_error := SQLCODE;
494 l_msg_data := SUBSTR(SQLERRM, 1, 200);
498 l_msg_token,
495 FND_MESSAGE.SET_NAME('GR',
496 'GR_UNEXPECTED_ERROR');
497 FND_MESSAGE.SET_TOKEN('TEXT',
499 FALSE);
500 IF FND_API.To_Boolean(p_called_by_form) THEN
501 APP_EXCEPTION.Raise_Exception;
502 ELSE
503 x_msg_data := FND_MESSAGE.Get;
504 END IF;
505
506 END Add_Language;
507
508
509 PROCEDURE Lock_Row
510 (p_commit IN VARCHAR2,
511 p_called_by_form IN VARCHAR2,
512 p_rowid IN VARCHAR2,
513 p_european_index_number IN VARCHAR2,
514 p_language IN VARCHAR2,
515 p_description IN VARCHAR2,
516 p_source_lang IN VARCHAR2,
517 p_created_by IN NUMBER,
518 p_creation_date IN DATE,
519 p_last_updated_by IN NUMBER,
520 p_last_update_date IN DATE,
521 p_last_update_login IN NUMBER,
522 x_return_status OUT NOCOPY VARCHAR2,
523 x_oracle_error OUT NOCOPY NUMBER,
524 x_msg_data OUT NOCOPY VARCHAR2)
525 IS
526
527 /* Alpha Variables */
528
529 L_RETURN_STATUS VARCHAR2(1) := 'S';
530 L_MSG_DATA VARCHAR2(2000);
531
532 /* Number Variables */
533
534 L_ORACLE_ERROR NUMBER;
535
536 /* Exceptions */
537
538 NO_DATA_FOUND_ERROR EXCEPTION;
539 RECORD_CHANGED_ERROR EXCEPTION;
540
541
542 /* Define the cursors */
543
544 CURSOR c_lock_ein_number
545 IS
546 SELECT last_update_date
547 FROM gr_ein_numbers_tl
548 WHERE rowid = p_rowid
549 FOR UPDATE NOWAIT;
550 LockEinRcd c_lock_ein_number%ROWTYPE;
551 BEGIN
552
553 /* Initialization Routine */
554
555 SAVEPOINT Lock_Row;
556 x_return_status := 'S';
557 x_oracle_error := 0;
558 x_msg_data := NULL;
559
560 /* Now lock the record */
561
562 OPEN c_lock_ein_number;
563 FETCH c_lock_ein_number INTO LockEinRcd;
564 IF c_lock_ein_number%NOTFOUND THEN
565 CLOSE c_lock_ein_number;
566 RAISE No_Data_Found_Error;
567 END IF;
568 CLOSE c_lock_ein_number;
569
570 IF LockEinRcd.last_update_date <> p_last_update_date THEN
571 RAISE RECORD_CHANGED_ERROR;
572 END IF;
573
574 IF FND_API.To_Boolean(p_commit) THEN
575 COMMIT WORK;
576 END IF;
577
578 EXCEPTION
579
580 WHEN No_Data_Found_Error THEN
581 ROLLBACK TO SAVEPOINT Lock_Row;
582 x_return_status := 'E';
583 FND_MESSAGE.SET_NAME('GR',
584 'GR_RECORD_NOT_FOUND');
585 FND_MESSAGE.SET_TOKEN('CODE',
586 p_european_index_number,
587 FALSE);
588 IF FND_API.To_Boolean(p_called_by_form) THEN
589 APP_EXCEPTION.Raise_Exception;
590 ELSE
591 x_msg_data := FND_MESSAGE.Get;
592 END IF;
593 WHEN RECORD_CHANGED_ERROR THEN
594 ROLLBACK TO SAVEPOINT Lock_Row;
595 X_return_status := 'E';
596 FND_MESSAGE.SET_NAME('FND',
597 'FORM_RECORD_CHANGED');
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 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
604 ROLLBACK TO SAVEPOINT Lock_Row;
605 x_return_status := 'L';
606 x_oracle_error := APP_EXCEPTION.Get_Code;
607 IF NOT (FND_API.To_Boolean(p_called_by_form)) THEN
608 FND_MESSAGE.SET_NAME('GR',
609 'GR_ROW_IS_LOCKED');
610 x_msg_data := FND_MESSAGE.Get;
611 END IF;
612 WHEN OTHERS THEN
613 ROLLBACK TO SAVEPOINT Lock_Row;
614 x_return_status := 'U';
615 x_oracle_error := SQLCODE;
616 l_msg_data := SUBSTR(sqlerrm, 1, 200);
617 FND_MESSAGE.SET_NAME('GR',
618 'GR_UNEXPECTED_ERROR');
619 FND_MESSAGE.SET_TOKEN('TEXT',
620 l_msg_data,
621 FALSE);
622 IF FND_API.To_Boolean(p_called_by_form) THEN
623 APP_EXCEPTION.Raise_Exception;
624 ELSE
625 x_msg_data := FND_MESSAGE.Get;
626 END IF;
627
628 END Lock_Row;
629
630 PROCEDURE Delete_Row
631 (p_commit IN VARCHAR2,
632 p_called_by_form IN VARCHAR2,
633 p_rowid IN VARCHAR2,
634 p_european_index_number IN VARCHAR2,
635 p_language IN VARCHAR2,
636 x_return_status OUT NOCOPY VARCHAR2,
637 x_oracle_error OUT NOCOPY NUMBER,
638 x_msg_data OUT NOCOPY VARCHAR2)
639 IS
640 /* Alpha Variables */
641
642 L_RETURN_STATUS VARCHAR2(1) := 'S';
643 L_MSG_DATA VARCHAR2(2000);
644
645 /* Number Variables */
646
647 L_ORACLE_ERROR NUMBER;
648
649 /* Exceptions */
650
651 CHECK_INTEGRITY_ERROR EXCEPTION;
652 ROW_MISSING_ERROR EXCEPTION;
653 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
654
655 BEGIN
656
657 /* Initialization Routine */
658
659 SAVEPOINT Delete_Row;
660 x_return_status := 'S';
661 x_oracle_error := 0;
662 x_msg_data := NULL;
663
664 /* Now call the check integrity procedure */
665
666 Check_Integrity
667 (p_called_by_form,
671 l_oracle_error,
668 p_european_index_number,
669 p_language,
670 l_return_status,
672 l_msg_data);
673
674 IF l_return_status <> 'S' THEN
675 RAISE Check_Integrity_Error;
676 END IF;
677
678 DELETE FROM gr_ein_numbers_tl
679 WHERE rowid = p_rowid;
680
681 /* Check the commit flag and if set, then commit the work. */
682
683 IF FND_API.TO_Boolean(p_commit) THEN
684 COMMIT WORK;
685 END IF;
686
687 EXCEPTION
688
689 WHEN Check_Integrity_Error THEN
690 ROLLBACK TO SAVEPOINT Delete_Row;
691 x_return_status := l_return_status;
692 x_oracle_error := l_oracle_error;
693 IF FND_API.To_Boolean(p_called_by_form) THEN
694 APP_EXCEPTION.Raise_Exception;
695 ELSE
696 x_msg_data := FND_MESSAGE.Get;
697 END IF;
698
699 WHEN Row_Missing_Error THEN
700 ROLLBACK TO SAVEPOINT Delete_Row;
701 x_return_status := 'E';
702 x_oracle_error := APP_EXCEPTION.Get_Code;
703 FND_MESSAGE.SET_NAME('GR',
704 'GR_RECORD_NOT_FOUND');
705 FND_MESSAGE.SET_TOKEN('CODE',
706 p_european_index_number,
707 FALSE);
708 IF FND_API.To_Boolean(p_called_by_form) THEN
709 APP_EXCEPTION.Raise_Exception;
710 ELSE
711 x_msg_data := FND_MESSAGE.Get;
712 END IF;
713
714 WHEN OTHERS THEN
715 ROLLBACK TO SAVEPOINT Delete_Row;
716 x_return_status := 'U';
717 x_oracle_error := SQLCODE;
718 l_msg_data := SUBSTR(sqlerrm, 1, 200);
719 FND_MESSAGE.SET_NAME('GR',
720 'GR_UNEXPECTED_ERROR');
721 FND_MESSAGE.SET_TOKEN('TEXT',
722 l_msg_data,
723 FALSE);
724 IF FND_API.To_Boolean(p_called_by_form) THEN
725 APP_EXCEPTION.Raise_Exception;
726 ELSE
727 x_msg_data := FND_MESSAGE.Get;
728 END IF;
729
730 END Delete_Row;
731
732 PROCEDURE Delete_Rows
733 (p_commit IN VARCHAR2,
734 p_called_by_form IN VARCHAR2,
735 p_european_index_number IN VARCHAR2,
736 x_return_status OUT NOCOPY VARCHAR2,
737 x_oracle_error OUT NOCOPY NUMBER,
738 x_msg_data OUT NOCOPY VARCHAR2)
739 IS
740
741 /* Alpha Variables */
742
743 L_RETURN_STATUS VARCHAR2(1) := 'S';
744 L_MSG_DATA VARCHAR2(2000);
745 L_MSG_TOKEN VARCHAR2(30);
746
747 /* Number Variables */
748
749 L_ORACLE_ERROR NUMBER;
750
751 /* Define the cursors */
752
753 BEGIN
754
755 /* Initialization Routine */
756
757 SAVEPOINT Delete_Rows;
758 x_return_status := 'S';
759 x_oracle_error := 0;
760 x_msg_data := NULL;
761 l_msg_token := p_european_index_number;
762
763 DELETE FROM gr_ein_numbers_tl
764 WHERE european_index_number = p_european_index_number;
765
766 IF FND_API.To_Boolean(p_commit) THEN
767 COMMIT WORK;
768 END IF;
769
770 EXCEPTION
771
772 WHEN OTHERS THEN
773 ROLLBACK TO SAVEPOINT Delete_Rows;
774 x_return_status := 'U';
775 x_oracle_error := SQLCODE;
776 l_msg_data := SUBSTR(sqlerrm, 1, 200);
777 FND_MESSAGE.SET_NAME('GR',
778 'GR_UNEXPECTED_ERROR');
779 FND_MESSAGE.SET_TOKEN('TEXT',
780 l_msg_token,
781 FALSE);
782 IF FND_API.To_Boolean(p_called_by_form) THEN
783 APP_EXCEPTION.Raise_Exception;
784 ELSE
785 x_msg_data := FND_MESSAGE.Get;
786 END IF;
787
788 END Delete_Rows;
789
790
791 PROCEDURE Check_Foreign_Keys
792 (p_european_index_number IN VARCHAR2,
793 p_language IN VARCHAR2,
794 p_source_lang IN VARCHAR2,
795 x_return_status OUT NOCOPY VARCHAR2,
796 x_oracle_error OUT NOCOPY NUMBER,
797 x_msg_data OUT NOCOPY VARCHAR2)
798 IS
799
800 /* Alpha Variables */
801
802 L_RETURN_STATUS VARCHAR2(1) := 'S';
803 L_MSG_DATA VARCHAR2(2000);
804 L_ROWID VARCHAR2(18);
805 L_KEY_EXISTS VARCHAR2(1);
806 L_MSG_TOKEN VARCHAR2(30);
807 L_LANGUAGE_CODE VARCHAR2(4);
808
809 /* Number Variables */
810
811 L_ORACLE_ERROR NUMBER;
812
813 /* Cursors */
814 CURSOR c_get_language
815 IS
816 SELECT lng.language_code
817 FROM fnd_languages lng
818 WHERE lng.language_code = l_language_code;
819 LangRecord c_get_language%ROWTYPE;
820
821 BEGIN
822
823 /* Initialization Routine */
824
825 SAVEPOINT Check_Foreign_Keys;
826 x_return_status := 'S';
827 x_oracle_error := 0;
828 x_msg_data := NULL;
829
830 l_msg_token := p_european_index_number || ' ' || p_language;
831
832 /* Check the language codes */
833
834 l_language_code := p_language;
835 OPEN c_get_language;
836 FETCH c_get_language INTO LangRecord;
837 IF c_get_language%NOTFOUND THEN
838 l_msg_token := l_language_code;
839 x_return_status := 'E';
840 x_oracle_error := APP_EXCEPTION.Get_Code;
841 FND_MESSAGE.SET_NAME('GR',
842 'GR_RECORD_NOT_FOUND');
846 l_msg_data := FND_MESSAGE.Get;
843 FND_MESSAGE.SET_TOKEN('CODE',
844 l_msg_token,
845 FALSE);
847 END IF;
848 CLOSE c_get_language;
849
850 l_language_code := p_source_lang;
851 OPEN c_get_language;
852 FETCH c_get_language INTO LangRecord;
853 IF c_get_language%NOTFOUND THEN
854 l_msg_token := l_language_code;
855 x_return_status := 'E';
856 x_oracle_error := APP_EXCEPTION.Get_Code;
857 FND_MESSAGE.SET_NAME('GR',
858 'GR_RECORD_NOT_FOUND');
859 FND_MESSAGE.SET_TOKEN('CODE',
860 l_msg_token,
861 FALSE);
862 l_msg_data := FND_MESSAGE.Get;
863 END IF;
864 CLOSE c_get_language;
865
866 IF x_return_status <> 'S' THEN
867 x_msg_data := l_msg_data;
868 END IF;
869
870
871 EXCEPTION
872
873 WHEN OTHERS THEN
874 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
875 x_return_status := 'U';
876 x_oracle_error := SQLCODE;
877 l_msg_data := SUBSTR(sqlerrm, 1, 200);
878 FND_MESSAGE.SET_NAME('GR',
879 'GR_UNEXPECTED_ERROR');
880 FND_MESSAGE.SET_TOKEN('TEXT',
881 l_msg_data,
882 FALSE);
883 x_msg_data := FND_MESSAGE.Get;
884
885 END Check_Foreign_Keys;
886
887 PROCEDURE Check_Integrity
888 (p_called_by_form IN VARCHAR2,
889 p_european_index_number IN VARCHAR2,
890 p_language IN VARCHAR2,
891 x_return_status OUT NOCOPY VARCHAR2,
892 x_oracle_error OUT NOCOPY NUMBER,
893 x_msg_data OUT NOCOPY VARCHAR2)
894 IS
895
896 /* Alpha Variables */
897
898 L_RETURN_STATUS VARCHAR2(1) := 'S';
899 L_MSG_DATA VARCHAR2(2000);
900
901 /* Number Variables */
902
903 L_ORACLE_ERROR NUMBER;
904 L_RECORD_COUNT NUMBER;
905
906 /* Exceptions */
907 INSTALLED_LANGUAGE_ERROR EXCEPTION;
908 /* Define Cursors */
909
910 CURSOR c_get_language_code
911 IS
912 SELECT lng.installed_flag
913 FROM fnd_languages lng
914 WHERE lng.language_code = p_language
915 AND lng.installed_flag IN ('B', 'I');
916 LangRecord c_get_language_code%ROWTYPE;
917
918 BEGIN
919 /*
920 ** Initialization Routine
921 */
922 SAVEPOINT Check_Integrity;
923 x_return_status := 'S';
924 x_oracle_error := 0;
925 x_msg_data := NULL;
926 l_record_count := 0;
927
928 OPEN c_get_language_code;
929 FETCH c_get_language_code INTO LangRecord;
930 IF c_get_language_code%FOUND THEN
931 CLOSE c_get_language_code;
932 RAISE Installed_Language_Error;
933 END IF;
934 CLOSE c_get_language_code;
935
936 EXCEPTION
937
938 WHEN Installed_Language_Error THEN
939 ROLLBACK TO SAVEPOINT Check_Integrity;
940 x_return_status := 'E';
941 FND_MESSAGE.SET_NAME('GR',
942 'GR_INSTALLED_LANG');
943 FND_MESSAGE.SET_TOKEN('CODE',
944 p_language,
945 FALSE);
946 IF FND_API.To_Boolean(p_called_by_form) THEN
947 APP_EXCEPTION.Raise_Exception;
948 ELSE
949 x_msg_data := FND_MESSAGE.Get;
950 END IF;
951
952 WHEN OTHERS THEN
953 ROLLBACK TO SAVEPOINT Check_Integrity;
954 x_return_status := 'U';
955 x_oracle_error := SQLCODE;
956 l_msg_data := SUBSTR(SQLERRM, 1, 200);
957 FND_MESSAGE.SET_NAME('GR',
958 'GR_UNEXPECTED_ERROR');
959 FND_MESSAGE.SET_TOKEN('TEXT',
960 l_msg_data,
961 FALSE);
962 IF FND_API.To_Boolean(p_called_by_form) THEN
963 APP_EXCEPTION.Raise_Exception;
964 ELSE
965 x_msg_data := FND_MESSAGE.Get;
966 END IF;
967
968 END Check_Integrity;
969
970 PROCEDURE Check_Primary_Key
971 (p_european_index_number IN VARCHAR2,
972 p_language IN VARCHAR2,
973 p_called_by_form IN VARCHAR2,
974 x_rowid OUT NOCOPY VARCHAR2,
975 x_key_exists OUT NOCOPY VARCHAR2)
976 IS
977 /* Declare any variables and the cursor */
978
979 L_MSG_DATA VARCHAR2(2000);
980
981 CURSOR c_get_ein_rowid
982 IS
983 SELECT ein.rowid
984 FROM gr_ein_numbers_tl ein
985 WHERE ein.european_index_number = p_european_index_number
986 AND ein.language = p_language;
987 EinRecord c_get_ein_rowid%ROWTYPE;
988
989 BEGIN
990
991 x_key_exists := 'F';
992 OPEN c_get_ein_rowid;
993 FETCH c_get_ein_rowid INTO EinRecord;
994 IF c_get_ein_rowid%FOUND THEN
995 x_key_exists := 'T';
996 x_rowid := EinRecord.rowid;
997 ELSE
998 x_key_exists := 'F';
999 END IF;
1000 CLOSE c_get_ein_rowid;
1001
1002 EXCEPTION
1003
1004 WHEN Others THEN
1005 l_msg_data := sqlerrm;
1006 FND_MESSAGE.SET_NAME('GR',
1007 'GR_UNEXPECTED_ERROR');
1008 FND_MESSAGE.SET_TOKEN('TEXT',
1009 l_msg_data,
1010 FALSE);
1011 IF FND_API.To_Boolean(p_called_by_form) THEN
1012 APP_EXCEPTION.Raise_Exception;
1013 END IF;
1014
1015 END Check_Primary_Key;
1016
1017
1018 /* =====================================================================
1019 PROOCEDURE:
1020 New_Language
1021
1022 DESCRIPTION:
1023 This PL/SQL procedure is used to add a new language
1024 GR_FIELD_NAME_MASKS_TL
1025
1026
1027 HISTORY
1028 04-Aug-2003 GK Bug 2961127 - Added in procedure new language for populating the tables.
1029 ===================================================================== */
1030 PROCEDURE NEW_LANGUAGE
1031 is
1032 begin
1033 delete from GR_EIN_NUMBERS_TL T
1034 where not exists
1035 (select NULL
1036 from GR_EIN_NUMBERS_B B
1037 where B.EUROPEAN_INDEX_NUMBER = T.EUROPEAN_INDEX_NUMBER
1038 );
1039
1040 update GR_EIN_NUMBERS_TL T set (
1041 DESCRIPTION
1042 ) = (select
1043 B.DESCRIPTION
1044 from GR_EIN_NUMBERS_TL B
1045 where B.EUROPEAN_INDEX_NUMBER = T.EUROPEAN_INDEX_NUMBER
1046 and B.LANGUAGE = T.SOURCE_LANG)
1047 where (
1048 T.EUROPEAN_INDEX_NUMBER,
1049 T.LANGUAGE
1050 ) in (select
1051 SUBT.EUROPEAN_INDEX_NUMBER,
1052 SUBT.LANGUAGE
1053 from GR_EIN_NUMBERS_TL SUBB, GR_EIN_NUMBERS_TL SUBT
1054 where SUBB.EUROPEAN_INDEX_NUMBER = SUBT.EUROPEAN_INDEX_NUMBER
1055 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1056 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
1057 ));
1058
1059 insert into GR_EIN_NUMBERS_TL (
1060 DESCRIPTION,
1061 CREATED_BY,
1062 CREATION_DATE,
1063 LAST_UPDATED_BY,
1064 LAST_UPDATE_DATE,
1065 LAST_UPDATE_LOGIN,
1066 EUROPEAN_INDEX_NUMBER,
1067 LANGUAGE,
1068 SOURCE_LANG
1069 ) select
1070 B.DESCRIPTION,
1071 B.CREATED_BY,
1072 B.CREATION_DATE,
1073 B.LAST_UPDATED_BY,
1074 B.LAST_UPDATE_DATE,
1075 B.LAST_UPDATE_LOGIN,
1076 B.EUROPEAN_INDEX_NUMBER,
1077 L.LANGUAGE_CODE,
1078 B.SOURCE_LANG
1079 from GR_EIN_NUMBERS_TL B, FND_LANGUAGES L
1080 where L.INSTALLED_FLAG in ('I', 'B')
1081 and B.LANGUAGE = userenv('LANG')
1082 and not exists
1083 (select NULL
1084 from GR_EIN_NUMBERS_TL T
1085 where T.EUROPEAN_INDEX_NUMBER = B.EUROPEAN_INDEX_NUMBER
1086 and T.LANGUAGE = L.LANGUAGE_CODE);
1087
1088 END NEW_LANGUAGE;
1089
1090 END GR_EIN_NUMBERS_TL_PKG;