[Home] [Help]
PACKAGE BODY: APPS.GR_LABELS_TL_PKG
Source
1 PACKAGE BODY GR_LABELS_TL_PKG AS
2 /*$Header: GRHILATB.pls 120.1 2006/06/16 21:39:22 pbamb noship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_label_code IN VARCHAR2,
7 p_language IN VARCHAR2,
8 p_label_description 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 /* 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 L_MSG_TOKEN VARCHAR2(100);
27
28 /* Number Variables */
29
30 L_ORACLE_ERROR NUMBER;
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_label_code,
53 p_language,
54 p_label_description,
55 p_source_lang,
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_label_code,
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_labels_tl
77 (label_code,
78 language,
79 label_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_label_code,
88 p_language,
89 p_label_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_label_code,
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 l_msg_token := p_label_code || ' ' || p_language;
138 x_return_status := 'E';
139 x_oracle_error := APP_EXCEPTION.Get_Code;
140 FND_MESSAGE.SET_NAME('GR',
141 'GR_RECORD_EXISTS');
142 FND_MESSAGE.SET_TOKEN('CODE',
143 l_msg_token,
144 FALSE);
145 IF FND_API.To_Boolean(p_called_by_form) THEN
146 APP_EXCEPTION.Raise_Exception;
147 ELSE
148 x_msg_data := FND_MESSAGE.Get;
149 END IF;
150
151 WHEN Row_Missing_Error THEN
152 ROLLBACK TO SAVEPOINT Insert_Row;
153 l_msg_token := p_label_code || ' ' || p_language;
154 x_return_status := 'E';
155 x_oracle_error := APP_EXCEPTION.Get_Code;
156 FND_MESSAGE.SET_NAME('GR',
157 'GR_NO_RECORD_INSERTED');
158 FND_MESSAGE.SET_TOKEN('CODE',
159 l_msg_token,
160 FALSE);
161 IF FND_API.To_Boolean(p_called_by_form) THEN
162 APP_EXCEPTION.Raise_Exception;
163 ELSE
164 x_msg_data := FND_MESSAGE.Get;
165 END IF;
166
167 WHEN OTHERS THEN
168 ROLLBACK TO SAVEPOINT Insert_Row;
169 l_msg_token := p_label_code || ' ' || p_language;
170 x_return_status := 'U';
171 x_oracle_error := SQLCODE;
172 l_msg_data := SUBSTR(SQLERRM, 1, 200);
173 FND_MESSAGE.SET_NAME('GR',
174 'GR_UNEXPECTED_ERROR');
175 FND_MESSAGE.SET_TOKEN('TEXT',
176 l_msg_token||sqlerrm,
177 FALSE);
178 IF FND_API.To_Boolean(p_called_by_form) THEN
179 APP_EXCEPTION.Raise_Exception;
180 ELSE
181 x_msg_data := FND_MESSAGE.Get;
182 END IF;
183
184 END Insert_Row;
185
186 PROCEDURE Update_Row
187 (p_commit IN VARCHAR2,
188 p_called_by_form IN VARCHAR2,
189 p_rowid IN VARCHAR2,
190 p_label_code IN VARCHAR2,
191 p_language IN VARCHAR2,
192 p_label_description IN VARCHAR2,
193 p_source_lang IN VARCHAR2,
194 p_created_by IN NUMBER,
195 p_creation_date IN DATE,
196 p_last_updated_by IN NUMBER,
197 p_last_update_date IN DATE,
198 p_last_update_login IN NUMBER,
199 x_return_status OUT NOCOPY VARCHAR2,
200 x_oracle_error OUT NOCOPY NUMBER,
201 x_msg_data OUT NOCOPY VARCHAR2)
202 IS
203
204 /* Alpha Variables */
205
206 L_RETURN_STATUS VARCHAR2(1) := 'S';
207 L_MSG_DATA VARCHAR2(2000);
208 L_MSG_TOKEN VARCHAR2(100);
209
210 /* Number Variables */
211
212 L_ORACLE_ERROR NUMBER;
213
214 /* Exceptions */
215
216 FOREIGN_KEY_ERROR EXCEPTION;
217 ROW_MISSING_ERROR EXCEPTION;
218
219 BEGIN
220
221 /* Initialization Routine */
222
223 SAVEPOINT Update_Row;
224 x_return_status := 'S';
225 x_oracle_error := 0;
226 x_msg_data := NULL;
227 l_msg_token := p_label_code || ' ' || p_language;
228
229 /* Now call the check foreign key procedure */
230
231 Check_Foreign_Keys
232 (p_label_code,
233 p_language,
234 p_label_description,
235 p_source_lang,
236 l_return_status,
237 l_oracle_error,
238 l_msg_data);
239
240 IF l_return_status <> 'S' THEN
241 RAISE Foreign_Key_Error;
242 ELSE
243 UPDATE gr_labels_tl
244 SET label_code = p_label_code,
245 language = p_language,
246 label_description = p_label_description,
247 source_lang = p_source_lang,
248 created_by = p_created_by,
249 creation_date = p_creation_date,
250 last_updated_by = p_last_updated_by,
251 last_update_date = p_last_update_date,
252 last_update_login = p_last_update_login
253 WHERE rowid = p_rowid;
254 IF SQL%NOTFOUND THEN
255 RAISE Row_Missing_Error;
256 END IF;
257 END IF;
258
259 /* Check the commit flag and if set, then commit the work. */
260
261 IF FND_API.To_Boolean(p_commit) THEN
262 COMMIT WORK;
263 END IF;
264
265 EXCEPTION
266
267 WHEN Foreign_Key_Error THEN
268 ROLLBACK TO SAVEPOINT Update_Row;
269 x_return_status := l_return_status;
270 x_oracle_error := l_oracle_error;
271 FND_MESSAGE.SET_NAME('GR',
272 'GR_FOREIGN_KEY_ERROR');
273 FND_MESSAGE.SET_TOKEN('TEXT',
274 l_msg_data,
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 Row_Missing_Error THEN
283 ROLLBACK TO SAVEPOINT Update_Row;
284 x_return_status := 'E';
285 x_oracle_error := APP_EXCEPTION.Get_Code;
286 FND_MESSAGE.SET_NAME('GR',
287 'GR_NO_RECORD_INSERTED');
288 FND_MESSAGE.SET_TOKEN('CODE',
289 l_msg_token,
290 FALSE);
291 IF FND_API.To_Boolean(p_called_by_form) THEN
292 APP_EXCEPTION.Raise_Exception;
293 ELSE
294 x_msg_data := FND_MESSAGE.Get;
295 END IF;
296
297 WHEN OTHERS THEN
298 ROLLBACK TO SAVEPOINT Update_Row;
299 x_return_status := 'U';
300 x_oracle_error := SQLCODE;
301 l_msg_data := SUBSTR(SQLERRM, 1, 200);
302 FND_MESSAGE.SET_NAME('GR',
303 'GR_UNEXPECTED_ERROR');
304 FND_MESSAGE.SET_TOKEN('TEXT',
305 l_msg_token||sqlerrm,
306 FALSE);
307 IF FND_API.To_Boolean(p_called_by_form) THEN
308 APP_EXCEPTION.Raise_Exception;
309 ELSE
310 x_msg_data := FND_MESSAGE.Get;
311 END IF;
312
313 END Update_Row;
314
315 PROCEDURE Add_Language
316 (p_commit IN VARCHAR2,
317 p_called_by_form IN VARCHAR2,
318 p_label_code IN VARCHAR2,
319 p_language IN VARCHAR2,
320 x_return_status OUT NOCOPY VARCHAR2,
321 x_oracle_error OUT NOCOPY NUMBER,
322 x_msg_data OUT NOCOPY VARCHAR2)
323 IS
324
325 /* Alpha Variables */
326
327 L_RETURN_STATUS VARCHAR2(1) := 'S';
328 L_MSG_DATA VARCHAR2(2000);
329 L_MSG_TOKEN VARCHAR2(100);
330 L_BASE_DESC VARCHAR2(240);
331 L_LANGUAGE VARCHAR2(4);
332 L_CREATION_DATE DATE;
333 L_LAST_UPDATE_DATE DATE;
334
335 /* Number Variables */
336
337 L_ORACLE_ERROR NUMBER;
338 L_CREATED_BY NUMBER;
339 L_LAST_UPDATED_BY NUMBER;
340 L_LAST_UPDATE_LOGIN NUMBER;
341
342 /* Exceptions */
343
344 LANGUAGE_MISSING_ERROR EXCEPTION;
345
346
347 /* Cursors */
348
349 CURSOR c_get_descs
350 IS
351 SELECT lat.label_description,
352 lat.created_by,
353 lat.creation_date,
354 lat.last_updated_by,
355 lat.last_update_date,
356 lat.last_update_login
357 FROM gr_labels_tl lat
358 WHERE lat.label_code = p_label_code
359 AND lat.language = l_language;
360 LabelDesc c_get_descs%ROWTYPE;
361
362 CURSOR c_get_installed_languages
363 IS
364 SELECT lng.language_code
365 FROM fnd_languages lng
366 WHERE lng.installed_flag IN ('I', 'B');
367 InstLang c_get_installed_languages%ROWTYPE;
368
369
370 BEGIN
371
372 /* Initialization Routine */
373
374 SAVEPOINT Add_Language;
375 x_return_status := 'S';
376 x_oracle_error := 0;
377 x_msg_data := NULL;
378 l_msg_token := p_label_code || ' ' || p_language;
379
380 /* Remove translations with no base row */
381
382 delete from GR_LABELS_TL T
383 where not exists
384 (select NULL
385 from GR_LABELS_B B
386 where B.LABEL_CODE = T.LABEL_CODE
387 );
388
389 /* Redefault translations from the source language */
390
391 update gr_labels_tl t set (
392 label_description ) =
393 ( select
394 B.LABEL_DESCRIPTION
395 from GR_LABELS_TL B
396 where B.LABEL_CODE = T.LABEL_CODE
397 and B.LANGUAGE = T.SOURCE_LANG)
398 where (
399 T.LABEL_CODE,
400 T.LANGUAGE
401 ) in (select
402 SUBT.LABEL_CODE,
403 SUBT.LANGUAGE
404 from GR_LABELS_TL SUBB, GR_LABELS_TL SUBT
405 where SUBB.LABEL_CODE = SUBT.LABEL_CODE
406 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
407 and (SUBB.LABEL_DESCRIPTION <> SUBT.LABEL_DESCRIPTION
408 or (SUBB.LABEL_DESCRIPTION is null and SUBT.LABEL_DESCRIPTION is not null)
409 or (SUBB.LABEL_DESCRIPTION is not null and SUBT.LABEL_DESCRIPTION is null)
410 ));
411
412
413 /* Open the language cursor and get the description entered from the
414 ** user environment variable.
415 */
416 l_language := p_language;
417 OPEN c_get_descs;
418 FETCH c_get_descs INTO LabelDesc;
419 IF c_get_descs%NOTFOUND THEN
420 CLOSE c_get_descs;
421 RAISE Language_Missing_Error;
422 ELSE
423 l_base_desc := LabelDesc.label_description;
424 l_created_by := LabelDesc.created_by;
425 l_creation_date := LabelDesc.creation_date;
426 l_last_updated_by := LabelDesc.last_updated_by;
427 l_last_update_date := LabelDesc.last_update_date;
428 l_last_update_login := LabelDesc.last_update_login;
429 CLOSE c_get_descs;
430 END IF;
431
432 /* Read fnd_languages for the installed and base languages.
433 ** For those that are found, read the labels tl table.
434 ** If there isn't a record in the table for that language then
435 ** insert it and go on to the next.
436 */
437 OPEN c_get_installed_languages;
438 FETCH c_get_installed_languages INTO InstLang;
439 IF c_get_installed_languages%FOUND THEN
440 WHILE c_get_installed_languages%FOUND LOOP
441 IF InstLang.language_code <> p_language THEN
442 l_language := InstLang.language_code;
443 OPEN c_get_descs;
444 FETCH c_get_descs INTO LabelDesc;
445 IF c_get_descs%NOTFOUND THEN
446 CLOSE c_get_descs;
447 INSERT INTO gr_labels_tl
448 (label_code,
449 language,
450 label_description,
451 source_lang,
452 created_by,
453 creation_date,
454 last_updated_by,
455 last_update_date,
456 last_update_login)
457 VALUES
458 (p_label_code,
459 l_language,
460 l_base_desc,
461 p_language,
462 l_created_by,
463 l_creation_date,
464 l_last_updated_by,
465 l_last_update_date,
466 l_last_update_login);
467 ELSE
468 CLOSE c_get_descs;
469 END IF;
470 END IF;
471 FETCH c_get_installed_languages INTO InstLang;
472 END LOOP;
473 END IF;
474 CLOSE c_get_installed_languages;
475
476 IF FND_API.To_Boolean(p_commit) THEN
477 COMMIT WORK;
478 END IF;
479
480 EXCEPTION
481
482 WHEN Language_Missing_Error THEN
483 ROLLBACK TO SAVEPOINT Add_Language;
484 x_return_status := 'E';
485 x_oracle_error := APP_EXCEPTION.Get_Code;
486 FND_MESSAGE.SET_NAME('GR',
487 'GR_RECORD_NOT_FOUND');
488 FND_MESSAGE.SET_TOKEN('CODE',
489 l_msg_token,
490 FALSE);
491 IF FND_API.To_Boolean(p_called_by_form) THEN
492 APP_EXCEPTION.Raise_Exception;
493 ELSE
494 x_msg_data := FND_MESSAGE.Get;
495 END IF;
496
497 WHEN OTHERS THEN
498 ROLLBACK TO SAVEPOINT Add_Language;
499 x_return_status := 'U';
500 x_oracle_error := SQLCODE;
501 l_msg_data := SUBSTR(SQLERRM, 1, 200);
502 FND_MESSAGE.SET_NAME('GR',
503 'GR_UNEXPECTED_ERROR');
504 FND_MESSAGE.SET_TOKEN('TEXT',
505 l_msg_token||sqlerrm,
506 FALSE);
507 IF FND_API.To_Boolean(p_called_by_form) THEN
508 APP_EXCEPTION.Raise_Exception;
509 ELSE
510 x_msg_data := FND_MESSAGE.Get;
511 END IF;
512
513 END Add_Language;
514
515 PROCEDURE Lock_Row
516 (p_commit IN VARCHAR2,
517 p_called_by_form IN VARCHAR2,
518 p_rowid IN VARCHAR2,
519 p_label_code IN VARCHAR2,
520 p_language IN VARCHAR2,
521 p_label_description IN VARCHAR2,
522 p_source_lang IN VARCHAR2,
523 p_created_by IN NUMBER,
524 p_creation_date IN DATE,
525 p_last_updated_by IN NUMBER,
526 p_last_update_date IN DATE,
527 p_last_update_login IN NUMBER,
528 x_return_status OUT NOCOPY VARCHAR2,
529 x_oracle_error OUT NOCOPY NUMBER,
530 x_msg_data OUT NOCOPY VARCHAR2)
531 IS
532
533 /* Alpha Variables */
534
535 L_RETURN_STATUS VARCHAR2(1) := 'S';
536 L_MSG_DATA VARCHAR2(2000);
537 L_MSG_TOKEN VARCHAR2(100);
538
539 /* Number Variables */
540
541 L_ORACLE_ERROR NUMBER;
542
543 /* Exceptions */
544
545 NO_DATA_FOUND_ERROR EXCEPTION;
546 ROW_ALREADY_LOCKED_ERROR EXCEPTION;
547 PRAGMA EXCEPTION_INIT(ROW_ALREADY_LOCKED_ERROR,-54);
548
549 /* Define the cursors */
550
551 CURSOR c_lock_labels_tl
552 IS
553 SELECT *
554 FROM gr_labels_tl
555 WHERE rowid = p_rowid
556 FOR UPDATE NOWAIT;
557 LockLabelRcd c_lock_labels_tl%ROWTYPE;
558
559 BEGIN
560
561 /* Initialization Routine */
562
563 SAVEPOINT Lock_Row;
564 x_return_status := 'S';
565 x_oracle_error := 0;
566 x_msg_data := NULL;
567 l_msg_token := p_label_code || ' ' || p_language;
568
569 /* Now lock the record */
570
571 OPEN c_lock_labels_tl;
572 FETCH c_lock_labels_tl INTO LockLabelRcd;
573 IF c_lock_labels_tl%NOTFOUND THEN
574 CLOSE c_lock_labels_tl;
575 RAISE No_Data_Found_Error;
576 END IF;
577 CLOSE c_lock_labels_tl;
578
579 IF FND_API.To_Boolean(p_commit) THEN
580 COMMIT WORK;
581 END IF;
582
583 EXCEPTION
584
585 WHEN No_Data_Found_Error THEN
586 ROLLBACK TO SAVEPOINT Lock_Row;
587 x_return_status := 'E';
588 FND_MESSAGE.SET_NAME('GR',
589 'GR_RECORD_NOT_FOUND');
590 FND_MESSAGE.SET_TOKEN('CODE',
591 l_msg_token,
592 FALSE);
593 IF FND_API.To_Boolean(p_called_by_form) THEN
594 APP_EXCEPTION.Raise_Exception;
595 ELSE
596 x_msg_data := FND_MESSAGE.Get;
597 END IF;
598
599 WHEN Row_Already_Locked_Error THEN
600 ROLLBACK TO SAVEPOINT Lock_Row;
601 x_return_status := 'E';
602 x_oracle_error := APP_EXCEPTION.Get_Code;
603 FND_MESSAGE.SET_NAME('GR',
604 'GR_ROW_IS_LOCKED');
605 IF FND_API.To_Boolean(p_called_by_form) THEN
606 APP_EXCEPTION.Raise_Exception;
607 ELSE
608 x_msg_data := FND_MESSAGE.Get;
609 END IF;
610
611 WHEN OTHERS THEN
612 ROLLBACK TO SAVEPOINT Lock_Row;
613 x_return_status := 'U';
614 x_oracle_error := SQLCODE;
615 l_msg_data := SUBSTR(SQLERRM, 1, 200);
616 FND_MESSAGE.SET_NAME('GR',
617 'GR_UNEXPECTED_ERROR');
618 FND_MESSAGE.SET_TOKEN('TEXT',
619 l_msg_token||sqlerrm,
620 FALSE);
621 IF FND_API.To_Boolean(p_called_by_form) THEN
622 APP_EXCEPTION.Raise_Exception;
623 ELSE
624 x_msg_data := FND_MESSAGE.Get;
625 END IF;
626
627 END Lock_Row;
628
629 PROCEDURE Delete_Row
630 (p_commit IN VARCHAR2,
631 p_called_by_form IN VARCHAR2,
632 p_rowid IN VARCHAR2,
633 p_label_code IN VARCHAR2,
634 p_language IN VARCHAR2,
635 p_label_description IN VARCHAR2,
636 p_source_lang IN VARCHAR2,
637 p_created_by IN NUMBER,
638 p_creation_date IN DATE,
639 p_last_updated_by IN NUMBER,
640 p_last_update_date IN DATE,
641 p_last_update_login IN NUMBER,
642 x_return_status OUT NOCOPY VARCHAR2,
643 x_oracle_error OUT NOCOPY NUMBER,
644 x_msg_data OUT NOCOPY VARCHAR2)
645 IS
646
647 /* Alpha Variables */
648
649 L_RETURN_STATUS VARCHAR2(1) := 'S';
650 L_MSG_DATA VARCHAR2(2000);
651 L_MSG_TOKEN VARCHAR2(100);
652 L_CALLED_BY_FORM VARCHAR2(1);
653
654 /* Number Variables */
655
656 L_ORACLE_ERROR NUMBER;
657
658 /* Exceptions */
659
660 CHECK_INTEGRITY_ERROR EXCEPTION;
661 ROW_MISSING_ERROR EXCEPTION;
662 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
663
664 /* Define the cursor */
665
666 BEGIN
667
668 /* Initialization Routine */
669
670 SAVEPOINT Delete_Row;
671 x_return_status := 'S';
672 l_called_by_form := 'F';
673 x_oracle_error := 0;
674 x_msg_data := NULL;
675 l_msg_token := p_label_code || ' ' || p_language;
676
677 /* Now call the check integrity procedure */
678
679 Check_Integrity
680 (l_called_by_form,
681 p_label_code,
682 p_language,
683 p_label_description,
684 p_source_lang,
685 l_return_status,
686 l_oracle_error,
687 l_msg_data);
688
689 IF l_return_status <> 'S' THEN
690 RAISE Check_Integrity_Error;
691 END IF;
692
693 DELETE FROM gr_labels_tl
694 WHERE rowid = p_rowid;
695
696 /* Check the commit flag and if set, then commit the work. */
697
698 IF FND_API.TO_Boolean(p_commit) THEN
699 COMMIT WORK;
700 END IF;
701
702 EXCEPTION
703
704 WHEN Check_Integrity_Error THEN
705 ROLLBACK TO SAVEPOINT Delete_Row;
706 x_return_status := l_return_status;
707 x_oracle_error := l_oracle_error;
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 Row_Missing_Error THEN
715 ROLLBACK TO SAVEPOINT Delete_Row;
716 x_return_status := 'E';
717 x_oracle_error := APP_EXCEPTION.Get_Code;
718 FND_MESSAGE.SET_NAME('GR',
719 'GR_RECORD_NOT_FOUND');
720 FND_MESSAGE.SET_TOKEN('CODE',
721 l_msg_token,
722 FALSE);
723 IF FND_API.To_Boolean(p_called_by_form) THEN
724 APP_EXCEPTION.Raise_Exception;
725 ELSE
726 x_msg_data := FND_MESSAGE.Get;
727 END IF;
728
729 WHEN OTHERS THEN
730 ROLLBACK TO SAVEPOINT Delete_Row;
731 x_return_status := 'U';
732 x_oracle_error :=SQLCODE;
733 l_msg_data := SUBSTR(SQLERRM, 1, 200);
734 FND_MESSAGE.SET_NAME('GR',
735 'GR_UNEXPECTED_ERROR');
736 FND_MESSAGE.SET_TOKEN('TEXT',
737 l_msg_token||sqlerrm,
738 FALSE);
739 IF FND_API.To_Boolean(p_called_by_form) THEN
740 APP_EXCEPTION.Raise_Exception;
741 ELSE
742 x_msg_data := FND_MESSAGE.Get;
743 END IF;
744
745 END Delete_Row;
746
747 PROCEDURE Delete_Rows
748 (p_commit IN VARCHAR2,
749 p_called_by_form IN VARCHAR2,
750 p_label_code IN VARCHAR2,
751 x_return_status OUT NOCOPY VARCHAR2,
752 x_oracle_error OUT NOCOPY NUMBER,
753 x_msg_data OUT NOCOPY VARCHAR2)
754 IS
755
756 /* Alpha Variables */
757
758 L_RETURN_STATUS VARCHAR2(1) := 'S';
759 L_MSG_DATA VARCHAR2(2000);
760 L_MSG_TOKEN VARCHAR2(100);
761
762 /* Number Variables */
763
764 L_ORACLE_ERROR NUMBER;
765
766 /* Define the cursors */
767
768 BEGIN
769
770 /* Initialization Routine */
771
772 SAVEPOINT Delete_Rows;
773 x_return_status := 'S';
774 x_oracle_error := 0;
775 x_msg_data := NULL;
776 l_msg_token := p_label_code;
777
778 DELETE FROM gr_labels_tl
779 WHERE label_code = p_label_code;
780
781 IF FND_API.To_Boolean(p_commit) THEN
782 COMMIT WORK;
783 END IF;
784
785 EXCEPTION
786
787 WHEN OTHERS THEN
788 ROLLBACK TO SAVEPOINT Delete_Rows;
789 x_return_status := 'U';
790 x_oracle_error := SQLCODE;
791 l_msg_data := SUBSTR(SQLERRM, 1, 200);
792 FND_MESSAGE.SET_NAME('GR',
793 'GR_UNEXPECTED_ERROR');
794 FND_MESSAGE.SET_TOKEN('TEXT',
795 l_msg_token||sqlerrm,
796 FALSE);
797 IF FND_API.To_Boolean(p_called_by_form) THEN
798 APP_EXCEPTION.Raise_Exception;
799 ELSE
800 x_msg_data := FND_MESSAGE.Get;
801 END IF;
802
803 END Delete_Rows;
804
805 PROCEDURE Check_Foreign_Keys
806 (p_label_code IN VARCHAR2,
807 p_language IN VARCHAR2,
808 p_label_description IN VARCHAR2,
809 p_source_lang IN VARCHAR2,
810 x_return_status OUT NOCOPY VARCHAR2,
811 x_oracle_error OUT NOCOPY NUMBER,
812 x_msg_data OUT NOCOPY VARCHAR2)
813 IS
814
815 /* Alpha Variables */
816
817 L_RETURN_STATUS VARCHAR2(1) := 'S';
818 L_MSG_DATA VARCHAR2(2000);
819 L_MSG_TOKEN VARCHAR2(100);
820 L_LANGUAGE_CODE VARCHAR2(4);
821
822 /* Number Variables */
823
824 L_ORACLE_ERROR NUMBER;
825
826 /* Error Definitions */
827
828 ROW_MISSING_ERROR EXCEPTION;
829
830 /* Define the cursors */
831
832 CURSOR c_get_language
833 IS
834 SELECT lng.language_code
835 FROM fnd_languages lng
836 WHERE lng.language_code = l_language_code;
837 LangRecord c_get_language%ROWTYPE;
838
839 BEGIN
840
841 /* Initialization Routine */
842
843 SAVEPOINT Check_Foreign_Keys;
844 x_return_status := 'S';
845 x_oracle_error := 0;
846 x_msg_data := NULL;
847 l_msg_token := p_label_code || ' ' || p_language;
848
849 /* Check the language codes */
850
851 l_language_code := p_language;
852 OPEN c_get_language;
853 FETCH c_get_language INTO LangRecord;
854 IF c_get_language%NOTFOUND THEN
855 CLOSE c_get_language;
856 l_msg_token := l_language_code;
857 RAISE Row_Missing_Error;
858 END IF;
859 CLOSE c_get_language;
860
861 l_language_code := p_source_lang;
862 OPEN c_get_language;
863 FETCH c_get_language INTO LangRecord;
864 IF c_get_language%NOTFOUND THEN
865 CLOSE c_get_language;
866 l_msg_token := l_language_code;
867 RAISE Row_Missing_Error;
868 END IF;
869 CLOSE c_get_language;
870
871 EXCEPTION
872
873 WHEN Row_Missing_Error THEN
874 ROLLBACK TO SAVEPOINT Delete_Row;
875 x_return_status := 'E';
876 x_oracle_error := APP_EXCEPTION.Get_Code;
877 FND_MESSAGE.SET_NAME('GR',
878 'GR_RECORD_NOT_FOUND');
879 FND_MESSAGE.SET_TOKEN('CODE',
880 l_msg_token,
881 FALSE);
882 x_msg_data := FND_MESSAGE.Get;
883
884 WHEN OTHERS THEN
885 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
886 x_return_status := 'U';
887 x_oracle_error := SQLCODE;
888 l_msg_data := SUBSTR(SQLERRM, 1, 200);
889 FND_MESSAGE.SET_NAME('GR',
890 'GR_UNEXPECTED_ERROR');
891 FND_MESSAGE.SET_TOKEN('TEXT',
892 l_msg_token||sqlerrm,
893 FALSE);
894 x_msg_data := FND_MESSAGE.Get;
895
896 END Check_Foreign_Keys;
897
898 PROCEDURE Check_Integrity
899 (p_called_by_form IN VARCHAR2,
900 p_label_code IN VARCHAR2,
901 p_language IN VARCHAR2,
902 p_label_description IN VARCHAR2,
903 p_source_lang IN VARCHAR2,
904 x_return_status OUT NOCOPY VARCHAR2,
905 x_oracle_error OUT NOCOPY NUMBER,
906 x_msg_data OUT NOCOPY VARCHAR2)
907 IS
908
909 /* Alpha Variables */
910
911 L_RETURN_STATUS VARCHAR2(1) := 'S';
912 L_MSG_DATA VARCHAR2(2000);
913 L_CODE_BLOCK VARCHAR2(100);
914
915 /* Number Variables */
916
917 L_ORACLE_ERROR NUMBER;
918 L_RECORD_COUNT NUMBER;
919
920 /* Exceptions */
921
922 INSTALLED_LANGUAGE_ERROR EXCEPTION;
923
924
925 /* Define the Cursors */
926
927 CURSOR c_get_language_code
928 IS
929 SELECT lng.installed_flag
930 FROM fnd_languages lng
931 WHERE lng.language_code = p_language
932 AND lng.installed_flag IN ('B', 'I');
933 LangRecord c_get_language_code%ROWTYPE;
934
935 BEGIN
936
937 /* Initialization Routine */
938
939 SAVEPOINT Check_Integrity;
940 x_return_status := 'S';
941 x_oracle_error := 0;
942 x_msg_data := NULL;
943
944 /* Check the language isn't base or installed */
945
946 OPEN c_get_language_code;
947 FETCH c_get_language_code INTO LangRecord;
948 IF c_get_language_code%FOUND THEN
949 CLOSE c_get_language_code;
950 RAISE Installed_Language_Error;
951 END IF;
952 CLOSE c_get_language_code;
953
954 EXCEPTION
955
956 WHEN Installed_Language_Error THEN
957 ROLLBACK TO SAVEPOINT Check_Integrity;
958 x_return_status := 'E';
959 FND_MESSAGE.SET_NAME('GR',
960 'GR_INSTALLED_LANG');
961 FND_MESSAGE.SET_TOKEN('CODE',
962 p_language,
963 FALSE);
964 IF FND_API.To_Boolean(p_called_by_form) THEN
965 APP_EXCEPTION.Raise_Exception;
966 ELSE
967 x_msg_data := FND_MESSAGE.Get;
968 END IF;
969
970 WHEN OTHERS THEN
971 ROLLBACK TO SAVEPOINT Check_Integrity;
972 x_return_status := 'U';
973 x_oracle_error := SQLCODE;
974 l_msg_data := SUBSTR(SQLERRM, 1, 200);
975 FND_MESSAGE.SET_NAME('GR',
976 'GR_UNEXPECTED_ERROR');
977 FND_MESSAGE.SET_TOKEN('TEXT',
978 l_msg_data,
979 FALSE);
980 IF FND_API.To_Boolean(p_called_by_form) THEN
981 APP_EXCEPTION.Raise_Exception;
982 ELSE
983 x_msg_data := FND_MESSAGE.Get;
984 END IF;
985
986 END Check_Integrity;
987
988 PROCEDURE Check_Primary_Key
989 /* p_label_code is the label code to check.
990 ** p_language is the language code part of the key
991 ** p_called_by_form is 'T' if called by a form or 'F' if not.
992 ** x_rowid is the row id of the record if found.
993 ** x_key_exists is 'T' is the record is found, 'F' if not.
994 */
995 (p_label_code IN VARCHAR2,
996 p_language IN VARCHAR2,
997 p_called_by_form IN VARCHAR2,
998 x_rowid OUT NOCOPY VARCHAR2,
999 x_key_exists OUT NOCOPY VARCHAR2)
1000 IS
1001 /* Alphanumeric variables */
1002
1003 L_MSG_DATA VARCHAR2(80);
1004
1005 /* Declare any variables and the cursor */
1006
1007
1008 CURSOR c_get_labels_tl_rowid
1009 IS
1010 SELECT lat.rowid
1011 FROM gr_labels_tl lat
1012 WHERE lat.label_code = p_label_code
1013 AND lat.language = p_language;
1014 LabelTLRecord c_get_labels_tl_rowid%ROWTYPE;
1015
1016 BEGIN
1017
1018 l_msg_data := p_label_code || ' ' || p_language;
1019
1020 x_key_exists := 'F';
1021 OPEN c_get_labels_tl_rowid;
1022 FETCH c_get_labels_tl_rowid INTO LabelTLRecord;
1023 IF c_get_labels_tl_rowid%FOUND THEN
1024 x_key_exists := 'T';
1025 x_rowid := LabelTLRecord.rowid;
1026 ELSE
1027 x_key_exists := 'F';
1028 END IF;
1029 CLOSE c_get_labels_tl_rowid;
1030
1031 EXCEPTION
1032
1033 WHEN Others THEN
1034 l_msg_data := SUBSTR(SQLERRM, 1, 200);
1035 FND_MESSAGE.SET_NAME('GR',
1036 'GR_UNEXPECTED_ERROR');
1037 FND_MESSAGE.SET_TOKEN('TEXT',
1038 l_msg_data,
1039 FALSE);
1040 IF FND_API.To_Boolean(p_called_by_form) THEN
1041 APP_EXCEPTION.Raise_Exception;
1042 END IF;
1043
1044 END Check_Primary_Key;
1045
1046 PROCEDURE translate_row (
1047 X_LABEL_CODE IN VARCHAR2
1048 ,X_LANGUAGE IN VARCHAR2
1049 ,X_LABEL_DESCRIPTION IN VARCHAR2
1050 ,X_SOURCE_LANG IN VARCHAR2
1051 ) IS
1052 BEGIN
1053 UPDATE GR_LABELS_TL SET
1054 LABEL_DESCRIPTION = X_LABEL_DESCRIPTION,
1055 SOURCE_LANG = USERENV('LANG'),
1056 LAST_UPDATE_DATE = sysdate,
1057 LAST_UPDATED_BY = 0,
1058 LAST_UPDATE_LOGIN = 0
1059 WHERE (LABEL_CODE = X_LABEL_CODE)
1060 AND (USERENV('LANG') IN (LANGUAGE, SOURCE_LANG));
1061 END TRANSLATE_ROW;
1062
1063 /*Bug# 5237433 */
1064 PROCEDURE translate_row (
1065 X_LABEL_CODE IN VARCHAR2
1066 ,X_LABEL_DESCRIPTION IN VARCHAR2
1067 ,X_OWNER IN VARCHAR2
1068 ) IS
1069 BEGIN
1070 UPDATE GR_LABELS_TL SET
1071 LABEL_DESCRIPTION = X_LABEL_DESCRIPTION,
1072 SOURCE_LANG = USERENV('LANG'),
1073 LAST_UPDATE_DATE = sysdate,
1074 LAST_UPDATED_BY = Decode(X_OWNER, 'SEED', 1, 0),
1075 LAST_UPDATE_LOGIN = 0
1076 WHERE (LABEL_CODE = X_LABEL_CODE)
1077 AND (USERENV('LANG') IN (LANGUAGE, SOURCE_LANG));
1078
1079 END TRANSLATE_ROW;
1080
1081 PROCEDURE load_row (
1082 X_LABEL_CODE IN VARCHAR2
1083 ,X_LANGUAGE IN VARCHAR2
1084 ,X_LABEL_DESCRIPTION IN VARCHAR2
1085 ,X_SOURCE_LANG IN VARCHAR2
1086 ) IS
1087 CURSOR Cur_rowid IS
1088 SELECT rowid
1089 FROM GR_LABELS_TL
1090 WHERE (LABEL_CODE = X_LABEL_CODE)
1091 AND (LANGUAGE = X_LANGUAGE);
1092 l_user_id NUMBER DEFAULT 1;
1093 l_row_id VARCHAR2(64);
1094 l_return_status VARCHAR2(1);
1095 l_oracle_error NUMBER;
1096 l_msg_data VARCHAR2(2000);
1097 BEGIN
1098 OPEN Cur_rowid;
1099 FETCH Cur_rowid INTO l_row_id;
1100 IF Cur_rowid%FOUND THEN
1101 GR_LABELS_TL_PKG.UPDATE_ROW(
1102 P_COMMIT => 'T'
1103 ,P_CALLED_BY_FORM => 'F'
1104 ,P_ROWID => l_row_id
1105 ,P_LABEL_CODE => X_LABEL_CODE
1106 ,P_LANGUAGE => X_LANGUAGE
1107 ,P_LABEL_DESCRIPTION => X_LABEL_DESCRIPTION
1108 ,P_SOURCE_LANG => X_SOURCE_LANG
1109 ,P_CREATED_BY => l_user_id
1110 ,P_CREATION_DATE => sysdate
1111 ,P_LAST_UPDATED_BY => l_user_id
1112 ,P_LAST_UPDATE_DATE => sysdate
1113 ,P_LAST_UPDATE_LOGIN => 0
1114 ,X_RETURN_STATUS => l_return_status
1115 ,X_ORACLE_ERROR => l_oracle_error
1116 ,X_MSG_DATA => l_msg_data);
1117 ELSE
1118 GR_LABELS_TL_PKG.INSERT_ROW(
1119 P_COMMIT => 'T'
1120 ,P_CALLED_BY_FORM => 'F'
1121 ,P_LABEL_CODE => X_LABEL_CODE
1122 ,P_LANGUAGE => X_LANGUAGE
1123 ,P_LABEL_DESCRIPTION => X_LABEL_DESCRIPTION
1124 ,P_SOURCE_LANG => X_SOURCE_LANG
1125 ,P_CREATED_BY => l_user_id
1126 ,P_CREATION_DATE => sysdate
1127 ,P_LAST_UPDATED_BY => l_user_id
1128 ,P_LAST_UPDATE_DATE => sysdate
1129 ,P_LAST_UPDATE_LOGIN => 0
1130 ,X_ROWID => l_row_id
1131 ,X_RETURN_STATUS => l_return_status
1132 ,X_ORACLE_ERROR => l_oracle_error
1133 ,X_MSG_DATA => l_msg_data);
1134 END IF;
1135 CLOSE Cur_rowid;
1136 END LOAD_ROW;
1137
1138
1139 PROCEDURE load_row (
1140 X_LABEL_CODE IN VARCHAR2
1141 ,X_LABEL_DESCRIPTION IN VARCHAR2
1142 ,X_OWNER IN VARCHAR2
1143 ) IS
1144 CURSOR Cur_rowid IS
1145 SELECT rowid
1146 FROM GR_LABELS_TL
1147 WHERE (LABEL_CODE = X_LABEL_CODE)
1148 AND USERENV('LANG') in (LANGUAGE,SOURCE_LANG);
1149
1150 l_user_id NUMBER DEFAULT 0;
1151 l_row_id VARCHAR2(64);
1152 l_return_status VARCHAR2(1);
1153 l_oracle_error NUMBER;
1154 l_msg_data VARCHAR2(2000);
1155 l_sysdate DATE;
1156
1157 BEGIN
1158 IF (x_owner = 'SEED') THEN
1159 l_user_id := 1;
1160 END IF;
1161 select sysdate into l_sysdate from dual;
1162
1163 OPEN Cur_rowid;
1164 FETCH Cur_rowid INTO l_row_id;
1165 IF Cur_rowid%FOUND THEN
1166 UPDATE GR_LABELS_TL SET
1167 LABEL_DESCRIPTION = X_LABEL_DESCRIPTION,
1168 LAST_UPDATE_DATE = l_sysdate,
1169 LAST_UPDATED_BY = l_user_id,
1170 LAST_UPDATE_LOGIN = 0,
1171 SOURCE_LANG = userenv('LANG')
1172 WHERE (LABEL_CODE = X_LABEL_CODE)
1173 AND userenv('LANG') in (LANGUAGE,SOURCE_LANG);
1174
1175 ELSE
1176 insert into GR_LABELS_TL (
1177 LABEL_CODE,
1178 LABEL_DESCRIPTION,
1179 CREATED_BY,
1180 CREATION_DATE,
1181 LAST_UPDATED_BY,
1182 LAST_UPDATE_DATE,
1183 LAST_UPDATE_LOGIN,
1184 LANGUAGE,
1185 SOURCE_LANG
1186 ) select
1187 X_LABEL_CODE,
1188 X_LABEL_DESCRIPTION,
1189 l_user_id,
1190 l_sysdate,
1191 l_user_id,
1192 l_sysdate,
1193 0,
1194 L.LANGUAGE_CODE,
1195 userenv('LANG')
1196 from FND_LANGUAGES L
1197 where L.INSTALLED_FLAG in ('I', 'B')
1198 and not exists
1199 (select NULL
1200 from GR_LABELS_TL T
1201 where T.LABEL_CODE = X_LABEL_CODE
1202 and T.LANGUAGE = L.LANGUAGE_CODE);
1203 END IF;
1204 CLOSE Cur_rowid;
1205 END LOAD_ROW;
1206
1207
1208
1209 /* 21-Jan-2002 Melanie Grosser BUG 2190024 - Added procedure NEW_LANGUAGE
1210 to be called from GRNLINS.sql. Generated
1211 from tltblgen.
1212 */
1213 procedure NEW_LANGUAGE
1214 is
1215 begin
1216 delete from GR_LABELS_TL T
1217 where not exists
1218 (select NULL
1219 from GR_LABELS_B B
1220 where B.LABEL_CODE = T.LABEL_CODE
1221 );
1222
1223 update GR_LABELS_TL T set (
1224 LABEL_DESCRIPTION
1225 ) = (select
1226 B.LABEL_DESCRIPTION
1227 from GR_LABELS_TL B
1228 where B.LABEL_CODE = T.LABEL_CODE
1229 and B.LANGUAGE = T.SOURCE_LANG)
1230 where (
1231 T.LABEL_CODE,
1232 T.LANGUAGE
1233 ) in (select
1234 SUBT.LABEL_CODE,
1235 SUBT.LANGUAGE
1236 from GR_LABELS_TL SUBB, GR_LABELS_TL SUBT
1237 where SUBB.LABEL_CODE = SUBT.LABEL_CODE
1238 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1239 and (SUBB.LABEL_DESCRIPTION <> SUBT.LABEL_DESCRIPTION
1240 ));
1241
1242 insert into GR_LABELS_TL (
1243 LABEL_CODE,
1244 LABEL_DESCRIPTION,
1245 CREATED_BY,
1246 CREATION_DATE,
1247 LAST_UPDATED_BY,
1248 LAST_UPDATE_DATE,
1249 LAST_UPDATE_LOGIN,
1250 LANGUAGE,
1251 SOURCE_LANG
1252 ) select
1253 B.LABEL_CODE,
1254 B.LABEL_DESCRIPTION,
1255 B.CREATED_BY,
1256 B.CREATION_DATE,
1257 B.LAST_UPDATED_BY,
1258 B.LAST_UPDATE_DATE,
1259 B.LAST_UPDATE_LOGIN,
1260 L.LANGUAGE_CODE,
1261 B.SOURCE_LANG
1262 from GR_LABELS_TL B, FND_LANGUAGES L
1263 where L.INSTALLED_FLAG in ('I', 'B')
1264 and B.LANGUAGE = userenv('LANG')
1265 and not exists
1266 (select NULL
1267 from GR_LABELS_TL T
1268 where T.LABEL_CODE = B.LABEL_CODE
1269 and T.LANGUAGE = L.LANGUAGE_CODE);
1270
1271 end NEW_LANGUAGE;
1272
1273
1274
1275 END GR_LABELS_TL_PKG;