[Home] [Help]
PACKAGE BODY: APPS.GR_MULTILINGUAL_NAME_TL_PKG
Source
1 PACKAGE BODY GR_MULTILINGUAL_NAME_TL_PKG AS
2 /*$Header: GRHIMLTB.pls 115.10 2002/10/28 16:53:36 methomas ship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_item_code IN VARCHAR2,
7 p_language IN VARCHAR2,
8 p_label_code IN VARCHAR2,
9 p_source_lang IN VARCHAR2,
10 p_name_description IN VARCHAR2,
11 p_attribute_category IN VARCHAR2,
12 p_attribute1 IN VARCHAR2,
13 p_attribute2 IN VARCHAR2,
14 p_attribute3 IN VARCHAR2,
15 p_attribute4 IN VARCHAR2,
16 p_attribute5 IN VARCHAR2,
17 p_attribute6 IN VARCHAR2,
18 p_attribute7 IN VARCHAR2,
19 p_attribute8 IN VARCHAR2,
20 p_attribute9 IN VARCHAR2,
21 p_attribute10 IN VARCHAR2,
22 p_attribute11 IN VARCHAR2,
23 p_attribute12 IN VARCHAR2,
24 p_attribute13 IN VARCHAR2,
25 p_attribute14 IN VARCHAR2,
26 p_attribute15 IN VARCHAR2,
27 p_attribute16 IN VARCHAR2,
28 p_attribute17 IN VARCHAR2,
29 p_attribute18 IN VARCHAR2,
30 p_attribute19 IN VARCHAR2,
31 p_attribute20 IN VARCHAR2,
32 p_attribute21 IN VARCHAR2,
33 p_attribute22 IN VARCHAR2,
34 p_attribute23 IN VARCHAR2,
35 p_attribute24 IN VARCHAR2,
36 p_attribute25 IN VARCHAR2,
37 p_attribute26 IN VARCHAR2,
38 p_attribute27 IN VARCHAR2,
39 p_attribute28 IN VARCHAR2,
40 p_attribute29 IN VARCHAR2,
41 p_attribute30 IN VARCHAR2,
42 p_created_by IN NUMBER,
43 p_creation_date IN DATE,
44 p_last_updated_by IN NUMBER,
45 p_last_update_date IN DATE,
46 p_last_update_login IN NUMBER,
47 x_rowid OUT NOCOPY VARCHAR2,
48 x_return_status OUT NOCOPY VARCHAR2,
49 x_oracle_error OUT NOCOPY NUMBER,
50 x_msg_data OUT NOCOPY VARCHAR2)
51 IS
52 /* Alpha Variables */
53
54 L_RETURN_STATUS VARCHAR2(1) := 'S';
55 L_KEY_EXISTS VARCHAR2(1);
56 L_MSG_DATA VARCHAR2(2000);
57 L_ROWID VARCHAR2(18);
58 L_MSG_TOKEN VARCHAR2(100);
59
60 /* Number Variables */
61
62 L_ORACLE_ERROR NUMBER;
63 /* Exceptions */
64
65 FOREIGN_KEY_ERROR EXCEPTION;
66 ITEM_EXISTS_ERROR EXCEPTION;
67 ROW_MISSING_ERROR EXCEPTION;
68
69 /* Declare cursors */
70
71
72 BEGIN
73
74 /* Initialization Routine */
75
76 SAVEPOINT Insert_Row;
77 x_return_status := 'S';
78 x_oracle_error := 0;
79 x_msg_data := NULL;
80
81 /* Now call the check foreign key procedure */
82
83 Check_Foreign_Keys
84 (p_item_code,
85 p_language,
86 p_label_code,
87 p_source_lang,
88 p_name_description,
89 p_attribute_category,
90 p_attribute1,
91 p_attribute2,
92 p_attribute3,
93 p_attribute4,
94 p_attribute5,
95 p_attribute6,
96 p_attribute7,
97 p_attribute8,
98 p_attribute9,
99 p_attribute10,
100 p_attribute11,
101 p_attribute12,
102 p_attribute13,
103 p_attribute14,
104 p_attribute15,
105 p_attribute16,
106 p_attribute17,
107 p_attribute18,
108 p_attribute19,
109 p_attribute20,
110 p_attribute21,
111 p_attribute22,
112 p_attribute23,
113 p_attribute24,
114 p_attribute25,
115 p_attribute26,
116 p_attribute27,
117 p_attribute28,
118 p_attribute29,
119 p_attribute30,
120 l_return_status,
121 l_oracle_error,
122 l_msg_data);
123 IF l_return_status <> 'S' THEN
124 RAISE Foreign_Key_Error;
125 END IF;
126
127 /* Now check the primary key doesn't already exist */
128
129 Check_Primary_Key
130 (p_item_code,
131 p_label_code,
132 p_language,
133 'F',
134 l_rowid,
135 l_key_exists);
136
137 IF FND_API.To_Boolean(l_key_exists) THEN
138 RAISE Item_Exists_Error;
139 END IF;
140
141 INSERT INTO gr_multilingual_name_tl
142 (item_code,
143 language,
144 label_code,
145 source_lang,
146 name_description,
147 attribute_category,
148 attribute1,
149 attribute2,
150 attribute3,
151 attribute4,
152 attribute5,
153 attribute6,
154 attribute7,
155 attribute8,
156 attribute9,
157 attribute10,
158 attribute11,
159 attribute12,
160 attribute13,
161 attribute14,
162 attribute15,
163 attribute16,
164 attribute17,
165 attribute18,
166 attribute19,
167 attribute20,
168 attribute21,
169 attribute22,
170 attribute23,
171 attribute24,
172 attribute25,
173 attribute26,
174 attribute27,
175 attribute28,
176 attribute29,
177 attribute30,
178 created_by,
179 creation_date,
180 last_updated_by,
181 last_update_date,
182 last_update_login)
183 VALUES
184 (p_item_code,
185 p_language,
186 p_label_code,
187 p_source_lang,
188 p_name_description,
189 p_attribute_category,
190 p_attribute1,
191 p_attribute2,
192 p_attribute3,
193 p_attribute4,
194 p_attribute5,
195 p_attribute6,
196 p_attribute7,
197 p_attribute8,
198 p_attribute9,
199 p_attribute10,
200 p_attribute11,
201 p_attribute12,
202 p_attribute13,
203 p_attribute14,
204 p_attribute15,
205 p_attribute16,
206 p_attribute17,
207 p_attribute18,
208 p_attribute19,
209 p_attribute20,
210 p_attribute21,
211 p_attribute22,
212 p_attribute23,
213 p_attribute24,
214 p_attribute25,
215 p_attribute26,
216 p_attribute27,
217 p_attribute28,
218 p_attribute29,
219 p_attribute30,
220 p_created_by,
221 p_creation_date,
222 p_last_updated_by,
223 p_last_update_date,
224 p_last_update_login);
225
226 /* Now get the row id of the inserted record */
227
228 Check_Primary_Key
229 (p_item_code,
230 p_label_code,
231 p_language,
232 'F',
233 l_rowid,
234 l_key_exists);
235
236 IF FND_API.To_Boolean(l_key_exists) THEN
237 x_rowid := l_rowid;
238 ELSE
239 RAISE Row_Missing_Error;
240 END IF;
241
242 /* Check the commit flag and if set, then commit the work. */
243
244 IF FND_API.To_Boolean(p_commit) THEN
245 COMMIT WORK;
246 END IF;
247
248 EXCEPTION
249
250 WHEN Foreign_Key_Error THEN
251 ROLLBACK TO SAVEPOINT Insert_Row;
252 x_return_status := l_return_status;
253 x_oracle_error := l_oracle_error;
254 FND_MESSAGE.SET_NAME('GR',
255 'GR_FOREIGN_KEY_ERROR');
256 FND_MESSAGE.SET_TOKEN('TEXT',
257 l_msg_data,
258 FALSE);
259 IF FND_API.To_Boolean(p_called_by_form) THEN
260 APP_EXCEPTION.Raise_Exception;
261 ELSE
262 x_msg_data := FND_MESSAGE.Get;
263 END IF;
264
265 WHEN Item_Exists_Error THEN
266 ROLLBACK TO SAVEPOINT Insert_Row;
267 l_msg_token := p_item_code || ' ' || p_label_code || ' ' || p_language;
268 x_return_status := 'E';
269 x_oracle_error := APP_EXCEPTION.Get_Code;
270 FND_MESSAGE.SET_NAME('GR',
271 'GR_RECORD_EXISTS');
272 FND_MESSAGE.SET_TOKEN('CODE',
273 l_msg_token,
274 FALSE);
275 IF FND_API.To_Boolean(p_called_by_form) THEN
276 APP_EXCEPTION.Raise_Exception;
277 ELSE
278 x_msg_data := FND_MESSAGE.Get;
279 END IF;
280
281 WHEN Row_Missing_Error THEN
282 ROLLBACK TO SAVEPOINT Insert_Row;
283 l_msg_token := p_item_code || ' ' || p_label_code || ' ' || p_language;
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 Insert_Row;
299 l_msg_token := p_item_code || ' ' || p_label_code || ' ' || p_language;
300 x_return_status := 'U';
301 x_oracle_error := APP_EXCEPTION.Get_Code;
302 l_msg_data := APP_EXCEPTION.Get_Text;
303 FND_MESSAGE.SET_NAME('GR',
304 'GR_UNEXPECTED_ERROR');
305 FND_MESSAGE.SET_TOKEN('TEXT',
306 l_msg_token,
307 FALSE);
308 IF FND_API.To_Boolean(p_called_by_form) THEN
309 APP_EXCEPTION.Raise_Exception;
310 ELSE
311 x_msg_data := FND_MESSAGE.Get;
312 END IF;
313
314 END Insert_Row;
315
316 PROCEDURE Update_Row
317 (p_commit IN VARCHAR2,
318 p_called_by_form IN VARCHAR2,
319 p_rowid IN VARCHAR2,
320 p_item_code IN VARCHAR2,
321 p_language IN VARCHAR2,
322 p_label_code IN VARCHAR2,
323 p_source_lang IN VARCHAR2,
324 p_name_description IN VARCHAR2,
325 p_attribute_category IN VARCHAR2,
326 p_attribute1 IN VARCHAR2,
327 p_attribute2 IN VARCHAR2,
328 p_attribute3 IN VARCHAR2,
329 p_attribute4 IN VARCHAR2,
330 p_attribute5 IN VARCHAR2,
331 p_attribute6 IN VARCHAR2,
332 p_attribute7 IN VARCHAR2,
333 p_attribute8 IN VARCHAR2,
334 p_attribute9 IN VARCHAR2,
335 p_attribute10 IN VARCHAR2,
336 p_attribute11 IN VARCHAR2,
337 p_attribute12 IN VARCHAR2,
338 p_attribute13 IN VARCHAR2,
339 p_attribute14 IN VARCHAR2,
340 p_attribute15 IN VARCHAR2,
341 p_attribute16 IN VARCHAR2,
342 p_attribute17 IN VARCHAR2,
343 p_attribute18 IN VARCHAR2,
344 p_attribute19 IN VARCHAR2,
345 p_attribute20 IN VARCHAR2,
346 p_attribute21 IN VARCHAR2,
347 p_attribute22 IN VARCHAR2,
348 p_attribute23 IN VARCHAR2,
349 p_attribute24 IN VARCHAR2,
350 p_attribute25 IN VARCHAR2,
351 p_attribute26 IN VARCHAR2,
352 p_attribute27 IN VARCHAR2,
353 p_attribute28 IN VARCHAR2,
354 p_attribute29 IN VARCHAR2,
355 p_attribute30 IN VARCHAR2,
356 p_created_by IN NUMBER,
357 p_creation_date IN DATE,
358 p_last_updated_by IN NUMBER,
359 p_last_update_date IN DATE,
360 p_last_update_login IN NUMBER,
361 x_return_status OUT NOCOPY VARCHAR2,
362 x_oracle_error OUT NOCOPY NUMBER,
363 x_msg_data OUT NOCOPY VARCHAR2)
364 IS
365
366 /* Alpha Variables */
367
368 L_RETURN_STATUS VARCHAR2(1) := 'S';
369 L_MSG_DATA VARCHAR2(2000);
370 L_MSG_TOKEN VARCHAR2(100);
371
372 /* Number Variables */
373
374 L_ORACLE_ERROR NUMBER;
375
376 /* Exceptions */
377
378 FOREIGN_KEY_ERROR EXCEPTION;
379 ROW_MISSING_ERROR EXCEPTION;
380
381 BEGIN
382
383 /* Initialization Routine */
384
385 SAVEPOINT Update_Row;
386 x_return_status := 'S';
387 x_oracle_error := 0;
388 x_msg_data := NULL;
389 l_msg_token := p_item_code || ' ' || p_label_code || ' ' || p_language;
390
391 /* Now call the check foreign key procedure */
392
393 Check_Foreign_Keys
394 (p_item_code,
395 p_language,
396 p_label_code,
397 p_source_lang,
398 p_name_description,
399 p_attribute_category,
400 p_attribute1,
401 p_attribute2,
402 p_attribute3,
403 p_attribute4,
404 p_attribute5,
405 p_attribute6,
406 p_attribute7,
407 p_attribute8,
408 p_attribute9,
409 p_attribute10,
410 p_attribute11,
411 p_attribute12,
412 p_attribute13,
413 p_attribute14,
414 p_attribute15,
415 p_attribute16,
416 p_attribute17,
417 p_attribute18,
418 p_attribute19,
419 p_attribute20,
420 p_attribute21,
421 p_attribute22,
422 p_attribute23,
423 p_attribute24,
424 p_attribute25,
425 p_attribute26,
426 p_attribute27,
427 p_attribute28,
428 p_attribute29,
429 p_attribute30,
430 l_return_status,
431 l_oracle_error,
432 l_msg_data);
433
434 IF l_return_status <> 'S' THEN
435 RAISE Foreign_Key_Error;
436 ELSE
437 UPDATE gr_multilingual_name_tl
438 SET item_code = p_item_code,
439 language = p_language,
440 label_code = p_label_code,
441 source_lang = p_source_lang,
442 name_description = p_name_description,
443 attribute_category = p_attribute_category,
444 attribute1 = p_attribute1,
445 attribute2 = p_attribute2,
446 attribute3 = p_attribute3,
447 attribute4 = p_attribute4,
448 attribute5 = p_attribute5,
449 attribute6 = p_attribute6,
450 attribute7 = p_attribute7,
451 attribute8 = p_attribute8,
452 attribute9 = p_attribute9,
453 attribute10 = p_attribute10,
454 attribute11 = p_attribute11,
455 attribute12 = p_attribute12,
456 attribute13 = p_attribute13,
457 attribute14 = p_attribute14,
458 attribute15 = p_attribute15,
459 attribute16 = p_attribute16,
460 attribute17 = p_attribute17,
461 attribute18 = p_attribute18,
462 attribute19 = p_attribute19,
463 attribute20 = p_attribute20,
464 attribute21 = p_attribute11,
465 attribute22 = p_attribute22,
466 attribute23 = p_attribute23,
467 attribute24 = p_attribute24,
468 attribute25 = p_attribute25,
469 attribute26 = p_attribute26,
470 attribute27 = p_attribute27,
471 attribute28 = p_attribute28,
472 attribute29 = p_attribute29,
473 attribute30 = p_attribute30,
474 created_by = p_created_by,
475 creation_date = p_creation_date,
476 last_updated_by = p_last_updated_by,
477 last_update_date = p_last_update_date,
478 last_update_login = p_last_update_login
479 WHERE rowid = p_rowid;
480 IF SQL%NOTFOUND THEN
481 RAISE Row_Missing_Error;
482 END IF;
483 END IF;
484
485 /* Check the commit flag and if set, then commit the work. */
486
487 IF FND_API.To_Boolean(p_commit) THEN
488 COMMIT WORK;
489 END IF;
490
491 EXCEPTION
492
493 WHEN Foreign_Key_Error THEN
494 ROLLBACK TO SAVEPOINT Update_Row;
495 x_return_status := l_return_status;
496 x_oracle_error := l_oracle_error;
497 FND_MESSAGE.SET_NAME('GR',
498 'GR_FOREIGN_KEY_ERROR');
499 FND_MESSAGE.SET_TOKEN('TEXT',
500 l_msg_data,
501 FALSE);
502 IF FND_API.To_Boolean(p_called_by_form) THEN
503 APP_EXCEPTION.Raise_Exception;
504 ELSE
505 x_msg_data := FND_MESSAGE.Get;
506 END IF;
507
508 WHEN Row_Missing_Error THEN
509 ROLLBACK TO SAVEPOINT Update_Row;
510 x_return_status := 'E';
511 x_oracle_error := APP_EXCEPTION.Get_Code;
512 FND_MESSAGE.SET_NAME('GR',
513 'GR_NO_RECORD_INSERTED');
514 FND_MESSAGE.SET_TOKEN('CODE',
515 l_msg_token,
516 FALSE);
517 IF FND_API.To_Boolean(p_called_by_form) THEN
518 APP_EXCEPTION.Raise_Exception;
519 ELSE
520 x_msg_data := FND_MESSAGE.Get;
521 END IF;
522
523 WHEN OTHERS THEN
524 ROLLBACK TO SAVEPOINT Update_Row;
525 x_return_status := 'U';
526 x_oracle_error := APP_EXCEPTION.Get_Code;
527 l_msg_data := APP_EXCEPTION.Get_Text;
528 FND_MESSAGE.SET_NAME('GR',
529 'GR_UNEXPECTED_ERROR');
530 FND_MESSAGE.SET_TOKEN('TEXT',
531 l_msg_token,
532 FALSE);
533 IF FND_API.To_Boolean(p_called_by_form) THEN
534 APP_EXCEPTION.Raise_Exception;
535 ELSE
536 x_msg_data := FND_MESSAGE.Get;
537 END IF;
538
539 END Update_Row;
540
541 PROCEDURE Add_Language
542 (p_commit IN VARCHAR2,
543 p_called_by_form IN VARCHAR2,
544 p_item_code IN VARCHAR2,
545 p_label_code IN VARCHAR2,
546 p_language IN VARCHAR2,
547 x_return_status OUT NOCOPY VARCHAR2,
548 x_oracle_error OUT NOCOPY NUMBER,
549 x_msg_data OUT NOCOPY VARCHAR2)
550 IS
551
552 /* Alpha Variables */
553
554 L_RETURN_STATUS VARCHAR2(1) := 'S';
555 L_MSG_DATA VARCHAR2(2000);
556 L_MSG_TOKEN VARCHAR2(100);
557 L_BASE_DESC VARCHAR2(240);
558 L_LABEL_CODE VARCHAR2(5);
559 L_LANGUAGE VARCHAR2(4);
560 L_CREATION_DATE DATE;
561 L_LAST_UPDATE_DATE DATE;
562 L_ATTRIBUTE_CATEGORY VARCHAR2(30);
563 L_ATTRIBUTE1 VARCHAR2(240);
564 L_ATTRIBUTE2 VARCHAR2(240);
565 L_ATTRIBUTE3 VARCHAR2(240);
566 L_ATTRIBUTE4 VARCHAR2(240);
567 L_ATTRIBUTE5 VARCHAR2(240);
568 L_ATTRIBUTE6 VARCHAR2(240);
569 L_ATTRIBUTE7 VARCHAR2(240);
570 L_ATTRIBUTE8 VARCHAR2(240);
571 L_ATTRIBUTE9 VARCHAR2(240);
572 L_ATTRIBUTE10 VARCHAR2(240);
573 L_ATTRIBUTE11 VARCHAR2(240);
574 L_ATTRIBUTE12 VARCHAR2(240);
575 L_ATTRIBUTE13 VARCHAR2(240);
576 L_ATTRIBUTE14 VARCHAR2(240);
577 L_ATTRIBUTE15 VARCHAR2(240);
578 L_ATTRIBUTE16 VARCHAR2(240);
579 L_ATTRIBUTE17 VARCHAR2(240);
580 L_ATTRIBUTE18 VARCHAR2(240);
581 L_ATTRIBUTE19 VARCHAR2(240);
582 L_ATTRIBUTE20 VARCHAR2(240);
583 L_ATTRIBUTE21 VARCHAR2(240);
584 L_ATTRIBUTE22 VARCHAR2(240);
585 L_ATTRIBUTE23 VARCHAR2(240);
586 L_ATTRIBUTE24 VARCHAR2(240);
587 L_ATTRIBUTE25 VARCHAR2(240);
588 L_ATTRIBUTE26 VARCHAR2(240);
589 L_ATTRIBUTE27 VARCHAR2(240);
590 L_ATTRIBUTE28 VARCHAR2(240);
591 L_ATTRIBUTE29 VARCHAR2(240);
592 L_ATTRIBUTE30 VARCHAR2(240);
593
594 /* Number Variables */
595
596 L_ORACLE_ERROR NUMBER;
597 L_CREATED_BY NUMBER;
598 L_LAST_UPDATED_BY NUMBER;
599 L_LAST_UPDATE_LOGIN NUMBER;
600 L_PRINT_SIZE NUMBER;
601
602 /* Exceptions */
603
604 LANGUAGE_MISSING_ERROR EXCEPTION;
605
606
607 /* Cursors */
608
609 CURSOR c_get_descs
610 IS
611 SELECT mln.name_description,
612 mln.label_code,
613 mln.attribute_category,
614 mln.attribute1,
615 mln.attribute2,
616 mln.attribute3,
617 mln.attribute4,
618 mln.attribute5,
619 mln.attribute6,
620 mln.attribute7,
621 mln.attribute8,
622 mln.attribute9,
623 mln.attribute10,
624 mln.attribute11,
625 mln.attribute12,
626 mln.attribute13,
627 mln.attribute14,
628 mln.attribute15,
629 mln.attribute16,
630 mln.attribute17,
631 mln.attribute18,
632 mln.attribute19,
633 mln.attribute20,
634 mln.attribute21,
635 mln.attribute22,
636 mln.attribute23,
637 mln.attribute24,
638 mln.attribute25,
639 mln.attribute26,
640 mln.attribute27,
641 mln.attribute28,
642 mln.attribute29,
643 mln.attribute30,
644 mln.created_by,
645 mln.creation_date,
646 mln.last_updated_by,
647 mln.last_update_date,
648 mln.last_update_login
649 FROM gr_multilingual_name_tl mln
650 WHERE mln.item_code = p_item_code
651 AND mln.label_code = p_label_code
652 AND mln.language = l_language;
653 MLNameDesc c_get_descs%ROWTYPE;
654
655 CURSOR c_get_installed_languages
656 IS
657 SELECT lng.language_code
658 FROM fnd_languages lng
659 WHERE lng.installed_flag IN ('I', 'B');
660 InstLang c_get_installed_languages%ROWTYPE;
661
662
663 BEGIN
664
665 /* Initialization Routine */
666
667 SAVEPOINT Add_Language;
668 x_return_status := 'S';
669 x_oracle_error := 0;
670 x_msg_data := NULL;
671 l_msg_token := p_item_code || ' ' || p_label_code || ' ' || p_language;
672
673 /* Remove translations with no base row */
674
675 delete from GR_MULTILINGUAL_NAME_TL T
676 where not exists
677 (select NULL
678 from GR_ITEM_GENERAL B,
679 GR_LABELS_B L
680 where B.ITEM_CODE = T.ITEM_CODE
681 and L.LABEL_CODE = T.LABEL_CODE
682 );
683
684 /* Redefault translations from the source language */
685
686 update gr_multilingual_name_tl t set (
687 name_description ) =
688 ( select
689 B.NAME_DESCRIPTION
690 from GR_MULTILINGUAL_NAME_TL B
691 where B.ITEM_CODE = T.ITEM_CODE
692 and B.LABEL_CODE = T.LABEL_CODE
693 and B.LANGUAGE = T.SOURCE_LANG)
694 where (
695 T.ITEM_CODE,
696 T.LABEL_CODE,
697 T.LANGUAGE
698 ) in (select
699 SUBT.ITEM_CODE,
700 SUBT.LABEL_CODE,
701 SUBT.LANGUAGE
702 from GR_MULTILINGUAL_NAME_TL SUBB, GR_MULTILINGUAL_NAME_TL SUBT
703 where SUBB.ITEM_CODE = SUBT.ITEM_CODE
704 AND SUBB.LABEL_CODE = SUBT.LABEL_CODE
705 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
706 and (SUBB.NAME_DESCRIPTION <> SUBT.NAME_DESCRIPTION
707 or (SUBB.NAME_DESCRIPTION is null and SUBT.NAME_DESCRIPTION is not null)
708 or (SUBB.NAME_DESCRIPTION is not null and SUBT.NAME_DESCRIPTION is null)
709 ));
710
711 /* Open the language cursor and get the description entered from the
712 ** user environment variable.
713 */
714 l_language := p_language;
715 OPEN c_get_descs;
716 FETCH c_get_descs INTO MLNameDesc;
717 IF c_get_descs%NOTFOUND THEN
718 CLOSE c_get_descs;
719 RAISE Language_Missing_Error;
720 ELSE
721 l_base_desc := MLNameDesc.name_description;
722 l_label_code := MLNameDesc.label_code;
723 l_attribute_category := MLNameDesc.attribute_category;
724 l_attribute1 := MLNameDesc.attribute1;
725 l_attribute2 := MLNameDesc.attribute2;
726 l_attribute3 := MLNameDesc.attribute3;
727 l_attribute4 := MLNameDesc.attribute4;
728 l_attribute5 := MLNameDesc.attribute5;
729 l_attribute6 := MLNameDesc.attribute6;
730 l_attribute7 := MLNameDesc.attribute7;
731 l_attribute8 := MLNameDesc.attribute8;
732 l_attribute9 := MLNameDesc.attribute9;
733 l_attribute10 := MLNameDesc.attribute10;
734 l_attribute11 := MLNameDesc.attribute11;
735 l_attribute12 := MLNameDesc.attribute12;
736 l_attribute13 := MLNameDesc.attribute13;
737 l_attribute14 := MLNameDesc.attribute14;
738 l_attribute15 := MLNameDesc.attribute15;
739 l_attribute16 := MLNameDesc.attribute16;
740 l_attribute17 := MLNameDesc.attribute17;
741 l_attribute18 := MLNameDesc.attribute18;
742 l_attribute19 := MLNameDesc.attribute19;
743 l_attribute20 := MLNameDesc.attribute20;
744 l_attribute21 := MLNameDesc.attribute21;
745 l_attribute22 := MLNameDesc.attribute22;
746 l_attribute23 := MLNameDesc.attribute23;
747 l_attribute24 := MLNameDesc.attribute24;
748 l_attribute25 := MLNameDesc.attribute25;
749 l_attribute26 := MLNameDesc.attribute26;
750 l_attribute27 := MLNameDesc.attribute27;
751 l_attribute28 := MLNameDesc.attribute28;
752 l_attribute29 := MLNameDesc.attribute29;
753 l_attribute30 := MLNameDesc.attribute30;
754 l_created_by := MLNameDesc.created_by;
755 l_creation_date := MLNameDesc.creation_date;
756 l_last_updated_by := MLNameDesc.last_updated_by;
757 l_last_update_date := MLNameDesc.last_update_date;
758 l_last_update_login := MLNameDesc.last_update_login;
759 CLOSE c_get_descs;
760 END IF;
761
762 /* Read fnd_languages for the installed and base languages.
763 ** For those that are found, read the phrases tl table.
764 ** If there isn't a record in the table for that language then
765 ** insert it and go on to the next.
766 */
767 OPEN c_get_installed_languages;
768 FETCH c_get_installed_languages INTO InstLang;
769 IF c_get_installed_languages%FOUND THEN
770 WHILE c_get_installed_languages%FOUND LOOP
771 IF InstLang.language_code <> p_language THEN
772 l_language := InstLang.language_code;
773 OPEN c_get_descs;
774 FETCH c_get_descs INTO MLNameDesc;
775 IF c_get_descs%NOTFOUND THEN
776 CLOSE c_get_descs;
777 INSERT INTO gr_multilingual_name_tl
778 (item_code,
779 language,
780 label_code,
781 source_lang,
782 name_description,
783 attribute_category,
784 attribute1,
785 attribute2,
786 attribute3,
787 attribute4,
788 attribute5,
789 attribute6,
790 attribute7,
791 attribute8,
792 attribute9,
793 attribute10,
794 attribute11,
795 attribute12,
796 attribute13,
797 attribute14,
798 attribute15,
799 attribute16,
800 attribute17,
801 attribute18,
802 attribute19,
803 attribute20,
804 attribute21,
805 attribute22,
806 attribute23,
807 attribute24,
808 attribute25,
809 attribute26,
810 attribute27,
811 attribute28,
812 attribute29,
813 attribute30,
814 created_by,
815 creation_date,
816 last_updated_by,
817 last_update_date,
818 last_update_login)
819 VALUES
820 (p_item_code,
821 l_language,
822 l_label_code,
823 p_language,
824 l_base_desc,
825 l_attribute_category,
826 l_attribute1,
827 l_attribute2,
828 l_attribute3,
829 l_attribute4,
830 l_attribute5,
831 l_attribute6,
832 l_attribute7,
833 l_attribute8,
834 l_attribute9,
835 l_attribute10,
836 l_attribute11,
837 l_attribute12,
838 l_attribute13,
839 l_attribute14,
840 l_attribute15,
841 l_attribute16,
842 l_attribute17,
843 l_attribute18,
844 l_attribute19,
845 l_attribute20,
846 l_attribute21,
847 l_attribute22,
848 l_attribute23,
849 l_attribute24,
850 l_attribute25,
851 l_attribute26,
852 l_attribute27,
853 l_attribute28,
854 l_attribute29,
855 l_attribute30,
856 l_created_by,
857 l_creation_date,
858 l_last_updated_by,
859 l_last_update_date,
860 l_last_update_login);
861 ELSE
862 CLOSE c_get_descs;
863 END IF;
864 END IF;
865 FETCH c_get_installed_languages INTO InstLang;
866 END LOOP;
867 END IF;
868 CLOSE c_get_installed_languages;
869
870 IF FND_API.To_Boolean(p_commit) THEN
871 COMMIT WORK;
872 END IF;
873
874 EXCEPTION
875
876 WHEN Language_Missing_Error THEN
877 ROLLBACK TO SAVEPOINT Add_Language;
878 x_return_status := 'E';
879 x_oracle_error := APP_EXCEPTION.Get_Code;
880 FND_MESSAGE.SET_NAME('GR',
881 'GR_RECORD_NOT_FOUND');
882 FND_MESSAGE.SET_TOKEN('CODE',
883 l_msg_token,
884 FALSE);
885 IF FND_API.To_Boolean(p_called_by_form) THEN
886 APP_EXCEPTION.Raise_Exception;
887 ELSE
888 x_msg_data := FND_MESSAGE.Get;
889 END IF;
890
891 WHEN OTHERS THEN
892 ROLLBACK TO SAVEPOINT Add_Language;
893 x_return_status := 'U';
894 x_oracle_error := APP_EXCEPTION.Get_Code;
895 FND_MESSAGE.SET_NAME('GR',
896 'GR_UNEXPECTED_ERROR');
897 FND_MESSAGE.SET_TOKEN('TEXT',
898 l_msg_token,
899 FALSE);
900 IF FND_API.To_Boolean(p_called_by_form) THEN
901 APP_EXCEPTION.Raise_Exception;
902 ELSE
903 x_msg_data := FND_MESSAGE.Get;
904 END IF;
905
906 END Add_Language;
907
908 PROCEDURE Lock_Row
909 (p_commit IN VARCHAR2,
910 p_called_by_form IN VARCHAR2,
911 p_rowid IN VARCHAR2,
912 p_item_code IN VARCHAR2,
913 p_language IN VARCHAR2,
914 p_label_code IN VARCHAR2,
915 p_source_lang IN VARCHAR2,
916 p_name_description IN VARCHAR2,
917 p_attribute_category IN VARCHAR2,
918 p_attribute1 IN VARCHAR2,
919 p_attribute2 IN VARCHAR2,
920 p_attribute3 IN VARCHAR2,
921 p_attribute4 IN VARCHAR2,
922 p_attribute5 IN VARCHAR2,
923 p_attribute6 IN VARCHAR2,
924 p_attribute7 IN VARCHAR2,
925 p_attribute8 IN VARCHAR2,
926 p_attribute9 IN VARCHAR2,
927 p_attribute10 IN VARCHAR2,
928 p_attribute11 IN VARCHAR2,
929 p_attribute12 IN VARCHAR2,
930 p_attribute13 IN VARCHAR2,
931 p_attribute14 IN VARCHAR2,
932 p_attribute15 IN VARCHAR2,
933 p_attribute16 IN VARCHAR2,
934 p_attribute17 IN VARCHAR2,
935 p_attribute18 IN VARCHAR2,
936 p_attribute19 IN VARCHAR2,
937 p_attribute20 IN VARCHAR2,
938 p_attribute21 IN VARCHAR2,
939 p_attribute22 IN VARCHAR2,
940 p_attribute23 IN VARCHAR2,
941 p_attribute24 IN VARCHAR2,
942 p_attribute25 IN VARCHAR2,
943 p_attribute26 IN VARCHAR2,
944 p_attribute27 IN VARCHAR2,
945 p_attribute28 IN VARCHAR2,
946 p_attribute29 IN VARCHAR2,
947 p_attribute30 IN VARCHAR2,
948 p_created_by IN NUMBER,
949 p_creation_date IN DATE,
950 p_last_updated_by IN NUMBER,
951 p_last_update_date IN DATE,
952 p_last_update_login IN NUMBER,
953 x_return_status OUT NOCOPY VARCHAR2,
954 x_oracle_error OUT NOCOPY NUMBER,
955 x_msg_data OUT NOCOPY VARCHAR2)
956 IS
957
958 /* Alpha Variables */
959
960 L_RETURN_STATUS VARCHAR2(1) := 'S';
961 L_MSG_DATA VARCHAR2(2000);
962 L_MSG_TOKEN VARCHAR2(100);
963
964 /* Number Variables */
965
966 L_ORACLE_ERROR NUMBER;
967
968 /* Exceptions */
969
970 NO_DATA_FOUND_ERROR EXCEPTION;
971 ROW_ALREADY_LOCKED_ERROR EXCEPTION;
972 PRAGMA EXCEPTION_INIT(ROW_ALREADY_LOCKED_ERROR,-54);
973
974 /* Define the cursors */
975
976 CURSOR c_lock_mlname_tl
977 IS
978 SELECT *
979 FROM gr_multilingual_name_tl
980 WHERE rowid = p_rowid
981 FOR UPDATE NOWAIT;
982 LockMLNameRcd c_lock_mlname_tl%ROWTYPE;
983
984 BEGIN
985
986 /* Initialization Routine */
987
988 SAVEPOINT Lock_Row;
989 x_return_status := 'S';
990 x_oracle_error := 0;
991 x_msg_data := NULL;
992 l_msg_token := p_item_code || ' ' || p_label_code || ' ' || p_language;
993
994 /* Now lock the record */
995
996 OPEN c_lock_mlname_tl;
997 FETCH c_lock_mlname_tl INTO LockMLNameRcd;
998 IF c_lock_mlname_tl%NOTFOUND THEN
999 CLOSE c_lock_mlname_tl;
1000 RAISE No_Data_Found_Error;
1001 END IF;
1002 CLOSE c_lock_mlname_tl;
1003
1004 IF FND_API.To_Boolean(p_commit) THEN
1005 COMMIT WORK;
1006 END IF;
1007
1008 EXCEPTION
1009
1010 WHEN No_Data_Found_Error THEN
1011 ROLLBACK TO SAVEPOINT Lock_Row;
1012 x_return_status := 'E';
1013 FND_MESSAGE.SET_NAME('GR',
1014 'GR_RECORD_NOT_FOUND');
1015 FND_MESSAGE.SET_TOKEN('CODE',
1016 l_msg_token,
1017 FALSE);
1018 IF FND_API.To_Boolean(p_called_by_form) THEN
1019 APP_EXCEPTION.Raise_Exception;
1020 ELSE
1021 x_msg_data := FND_MESSAGE.Get;
1022 END IF;
1023
1024 WHEN Row_Already_Locked_Error THEN
1025 ROLLBACK TO SAVEPOINT Lock_Row;
1026 x_return_status := 'E';
1027 x_oracle_error := APP_EXCEPTION.Get_Code;
1028 FND_MESSAGE.SET_NAME('GR',
1029 'GR_ROW_IS_LOCKED');
1030 IF FND_API.To_Boolean(p_called_by_form) THEN
1031 APP_EXCEPTION.Raise_Exception;
1032 ELSE
1033 x_msg_data := FND_MESSAGE.Get;
1034 END IF;
1035
1036 WHEN OTHERS THEN
1037 ROLLBACK TO SAVEPOINT Lock_Row;
1038 x_return_status := 'U';
1039 x_oracle_error := APP_EXCEPTION.Get_Code;
1040 l_msg_data := APP_EXCEPTION.Get_Text;
1041 FND_MESSAGE.SET_NAME('GR',
1042 'GR_UNEXPECTED_ERROR');
1043 FND_MESSAGE.SET_TOKEN('TEXT',
1044 l_msg_token,
1045 FALSE);
1046 IF FND_API.To_Boolean(p_called_by_form) THEN
1047 APP_EXCEPTION.Raise_Exception;
1048 ELSE
1049 x_msg_data := FND_MESSAGE.Get;
1050 END IF;
1051
1052 END Lock_Row;
1053
1054 PROCEDURE Delete_Row
1055 (p_commit IN VARCHAR2,
1056 p_called_by_form IN VARCHAR2,
1057 p_rowid IN VARCHAR2,
1058 p_item_code IN VARCHAR2,
1059 p_language IN VARCHAR2,
1060 p_label_code IN VARCHAR2,
1061 p_source_lang IN VARCHAR2,
1062 p_name_description IN VARCHAR2,
1063 p_attribute_category IN VARCHAR2,
1064 p_attribute1 IN VARCHAR2,
1065 p_attribute2 IN VARCHAR2,
1066 p_attribute3 IN VARCHAR2,
1067 p_attribute4 IN VARCHAR2,
1068 p_attribute5 IN VARCHAR2,
1069 p_attribute6 IN VARCHAR2,
1070 p_attribute7 IN VARCHAR2,
1071 p_attribute8 IN VARCHAR2,
1072 p_attribute9 IN VARCHAR2,
1073 p_attribute10 IN VARCHAR2,
1074 p_attribute11 IN VARCHAR2,
1075 p_attribute12 IN VARCHAR2,
1076 p_attribute13 IN VARCHAR2,
1077 p_attribute14 IN VARCHAR2,
1078 p_attribute15 IN VARCHAR2,
1079 p_attribute16 IN VARCHAR2,
1080 p_attribute17 IN VARCHAR2,
1081 p_attribute18 IN VARCHAR2,
1082 p_attribute19 IN VARCHAR2,
1083 p_attribute20 IN VARCHAR2,
1084 p_attribute21 IN VARCHAR2,
1085 p_attribute22 IN VARCHAR2,
1086 p_attribute23 IN VARCHAR2,
1087 p_attribute24 IN VARCHAR2,
1088 p_attribute25 IN VARCHAR2,
1089 p_attribute26 IN VARCHAR2,
1090 p_attribute27 IN VARCHAR2,
1091 p_attribute28 IN VARCHAR2,
1092 p_attribute29 IN VARCHAR2,
1093 p_attribute30 IN VARCHAR2,
1094 p_created_by IN NUMBER,
1095 p_creation_date IN DATE,
1096 p_last_updated_by IN NUMBER,
1097 p_last_update_date IN DATE,
1098 p_last_update_login IN NUMBER,
1099 x_return_status OUT NOCOPY VARCHAR2,
1100 x_oracle_error OUT NOCOPY NUMBER,
1101 x_msg_data OUT NOCOPY VARCHAR2)
1102 IS
1103
1104 /* Alpha Variables */
1105
1106 L_RETURN_STATUS VARCHAR2(1) := 'S';
1107 L_MSG_DATA VARCHAR2(2000);
1108 L_MSG_TOKEN VARCHAR2(100);
1109 L_CALLED_BY_FORM VARCHAR2(1);
1110
1111 /* Number Variables */
1112
1113 L_ORACLE_ERROR NUMBER;
1114
1115 /* Exceptions */
1116
1117 CHECK_INTEGRITY_ERROR EXCEPTION;
1118 ROW_MISSING_ERROR EXCEPTION;
1119 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
1120
1121 /* Define the cursor */
1122
1123 BEGIN
1124
1125 /* Initialization Routine */
1126
1127 SAVEPOINT Delete_Row;
1128 x_return_status := 'S';
1129 l_called_by_form := 'F';
1130 x_oracle_error := 0;
1131 x_msg_data := NULL;
1132 l_msg_token := p_item_code || ' ' || p_label_code || ' ' || p_language;
1133
1134 /* Now call the check integrity procedure */
1135
1136 Check_Integrity
1137 (l_called_by_form,
1138 p_item_code,
1139 p_language,
1140 p_label_code,
1141 p_source_lang,
1142 p_name_description,
1143 p_attribute_category,
1144 p_attribute1,
1145 p_attribute2,
1146 p_attribute3,
1147 p_attribute4,
1148 p_attribute5,
1149 p_attribute6,
1150 p_attribute7,
1151 p_attribute8,
1152 p_attribute9,
1153 p_attribute10,
1154 p_attribute11,
1155 p_attribute12,
1156 p_attribute13,
1157 p_attribute14,
1158 p_attribute15,
1159 p_attribute16,
1160 p_attribute17,
1161 p_attribute18,
1162 p_attribute19,
1163 p_attribute20,
1164 p_attribute21,
1165 p_attribute22,
1166 p_attribute23,
1167 p_attribute24,
1168 p_attribute25,
1169 p_attribute26,
1170 p_attribute27,
1171 p_attribute28,
1172 p_attribute29,
1173 p_attribute30,
1174 l_return_status,
1175 l_oracle_error,
1176 l_msg_data);
1177
1178 IF l_return_status <> 'S' THEN
1179 RAISE Check_Integrity_Error;
1180 END IF;
1181
1182 DELETE FROM gr_multilingual_name_tl
1183 WHERE rowid = p_rowid;
1184
1185 /* Check the commit flag and if set, then commit the work. */
1186
1187 IF FND_API.TO_Boolean(p_commit) THEN
1188 COMMIT WORK;
1189 END IF;
1190
1191 EXCEPTION
1192
1193 WHEN Check_Integrity_Error THEN
1194 ROLLBACK TO SAVEPOINT Delete_Row;
1195 x_return_status := l_return_status;
1196 x_oracle_error := l_oracle_error;
1197 IF FND_API.To_Boolean(p_called_by_form) THEN
1198 APP_EXCEPTION.Raise_Exception;
1199 ELSE
1200 x_msg_data := FND_MESSAGE.Get;
1201 END IF;
1202
1203 WHEN Row_Missing_Error THEN
1204 ROLLBACK TO SAVEPOINT Delete_Row;
1205 x_return_status := 'E';
1206 x_oracle_error := APP_EXCEPTION.Get_Code;
1207 FND_MESSAGE.SET_NAME('GR',
1208 'GR_RECORD_NOT_FOUND');
1209 FND_MESSAGE.SET_TOKEN('CODE',
1210 l_msg_token,
1211 FALSE);
1212 IF FND_API.To_Boolean(p_called_by_form) THEN
1213 APP_EXCEPTION.Raise_Exception;
1214 ELSE
1215 x_msg_data := FND_MESSAGE.Get;
1216 END IF;
1217
1218 WHEN OTHERS THEN
1219 ROLLBACK TO SAVEPOINT Delete_Row;
1220 x_return_status := 'U';
1221 x_oracle_error := APP_EXCEPTION.Get_Code;
1222 l_msg_data := APP_EXCEPTION.Get_Text;
1223 l_msg_data := APP_EXCEPTION.Get_Text;
1224 FND_MESSAGE.SET_NAME('GR',
1225 'GR_UNEXPECTED_ERROR');
1226 FND_MESSAGE.SET_TOKEN('TEXT',
1227 l_msg_token,
1228 FALSE);
1229 IF FND_API.To_Boolean(p_called_by_form) THEN
1230 APP_EXCEPTION.Raise_Exception;
1231 ELSE
1232 x_msg_data := FND_MESSAGE.Get;
1233 END IF;
1234
1235 END Delete_Row;
1236
1237 PROCEDURE Delete_Rows
1238 (p_commit IN VARCHAR2,
1239 p_called_by_form IN VARCHAR2,
1240 p_delete_option IN VARCHAR2,
1241 p_item_code IN VARCHAR2,
1242 p_label_code IN VARCHAR2,
1243 x_return_status OUT NOCOPY VARCHAR2,
1244 x_oracle_error OUT NOCOPY NUMBER,
1245 x_msg_data OUT NOCOPY VARCHAR2)
1246 IS
1247
1248 /* Alpha Variables */
1249
1250 L_RETURN_STATUS VARCHAR2(1) := 'S';
1251 L_MSG_DATA VARCHAR2(2000);
1252 L_MSG_TOKEN VARCHAR2(100);
1253
1254 /* Number Variables */
1255
1256 L_ORACLE_ERROR NUMBER;
1257
1258 /* Excceptions */
1259
1260 NULL_DELETE_OPTION_ERROR EXCEPTION;
1261
1262 /* Define the cursors */
1263
1264 BEGIN
1265
1266 /* Initialization Routine */
1267
1268 SAVEPOINT Delete_Rows;
1269 x_return_status := 'S';
1270 x_oracle_error := 0;
1271 x_msg_data := NULL;
1272 l_msg_token := p_item_code || ' ' || p_label_code;
1273
1274 /*
1275 ** p_delete_option has one of three values.
1276 ** 'I' - Delete all rows for the item in p_item_code.
1277 ** 'L' - Delete all rows for the label in p_label_code.
1278 ** 'B' - Delete all rows for the item and label codes.
1279 */
1280 IF p_delete_option = 'I' THEN
1281 IF p_item_code IS NULL THEN
1282 l_msg_token := 'Item Code';
1283 RAISE Null_Delete_Option_Error;
1284 ELSE
1285 l_msg_token := p_item_code;
1286
1287 DELETE FROM gr_multilingual_name_tl
1288 WHERE item_code = p_item_code;
1289 END IF;
1290 ELSIF p_delete_option = 'L' THEN
1291 IF p_label_code IS NULL THEN
1292 l_msg_token := 'Label Code';
1293 RAISE Null_Delete_Option_Error;
1294 ELSE
1295 l_msg_token := p_label_code;
1296
1297 DELETE FROM gr_multilingual_name_tl
1298 WHERE label_code = p_label_code;
1299 END IF;
1300 ELSIF p_delete_option = 'B' THEN
1301 IF p_item_code IS NULL OR
1302 p_label_code IS NULL THEN
1303 l_msg_token := 'Item Code / Label Code';
1304 RAISE Null_Delete_Option_Error;
1305 ELSE
1306 l_msg_token := p_item_code || ' ' || p_label_code;
1307
1308 DELETE FROM gr_multilingual_name_tl
1309 WHERE item_code = p_item_code
1310 AND label_code = p_label_code;
1311 END IF;
1312 END IF;
1313
1314 IF FND_API.To_Boolean(p_commit) THEN
1315 COMMIT WORK;
1316 END IF;
1317
1318 EXCEPTION
1319
1320 WHEN Null_Delete_Option_Error THEN
1321 x_return_status := 'E';
1322 x_oracle_error := APP_EXCEPTION.Get_Code;
1323 FND_MESSAGE.SET_NAME('GR',
1324 'GR_NULL_VALUE');
1325 FND_MESSAGE.SET_TOKEN('CODE',
1326 l_msg_token,
1327 FALSE);
1328 IF FND_API.To_Boolean(p_called_by_form) THEN
1329 APP_EXCEPTION.Raise_Exception;
1330 ELSE
1331 x_msg_data := FND_MESSAGE.Get;
1332 END IF;
1333
1334 WHEN OTHERS THEN
1335 ROLLBACK TO SAVEPOINT Delete_Rows;
1336 x_return_status := 'U';
1337 x_oracle_error := APP_EXCEPTION.Get_Code;
1338 l_msg_data := APP_EXCEPTION.Get_Text;
1339 FND_MESSAGE.SET_NAME('GR',
1340 'GR_UNEXPECTED_ERROR');
1341 FND_MESSAGE.SET_TOKEN('TEXT',
1342 l_msg_token,
1343 FALSE);
1344 IF FND_API.To_Boolean(p_called_by_form) THEN
1345 APP_EXCEPTION.Raise_Exception;
1346 ELSE
1347 x_msg_data := FND_MESSAGE.Get;
1348 END IF;
1349
1350 END Delete_Rows;
1351
1352 PROCEDURE Check_Foreign_Keys
1353 (p_item_code IN VARCHAR2,
1354 p_language IN VARCHAR2,
1355 p_label_code IN VARCHAR2,
1356 p_source_lang IN VARCHAR2,
1357 p_name_description IN VARCHAR2,
1358 p_attribute_category IN VARCHAR2,
1359 p_attribute1 IN VARCHAR2,
1360 p_attribute2 IN VARCHAR2,
1361 p_attribute3 IN VARCHAR2,
1362 p_attribute4 IN VARCHAR2,
1363 p_attribute5 IN VARCHAR2,
1364 p_attribute6 IN VARCHAR2,
1365 p_attribute7 IN VARCHAR2,
1366 p_attribute8 IN VARCHAR2,
1367 p_attribute9 IN VARCHAR2,
1368 p_attribute10 IN VARCHAR2,
1369 p_attribute11 IN VARCHAR2,
1370 p_attribute12 IN VARCHAR2,
1371 p_attribute13 IN VARCHAR2,
1372 p_attribute14 IN VARCHAR2,
1373 p_attribute15 IN VARCHAR2,
1374 p_attribute16 IN VARCHAR2,
1375 p_attribute17 IN VARCHAR2,
1376 p_attribute18 IN VARCHAR2,
1377 p_attribute19 IN VARCHAR2,
1378 p_attribute20 IN VARCHAR2,
1379 p_attribute21 IN VARCHAR2,
1380 p_attribute22 IN VARCHAR2,
1381 p_attribute23 IN VARCHAR2,
1382 p_attribute24 IN VARCHAR2,
1383 p_attribute25 IN VARCHAR2,
1384 p_attribute26 IN VARCHAR2,
1385 p_attribute27 IN VARCHAR2,
1386 p_attribute28 IN VARCHAR2,
1387 p_attribute29 IN VARCHAR2,
1388 p_attribute30 IN VARCHAR2,
1389 x_return_status OUT NOCOPY VARCHAR2,
1390 x_oracle_error OUT NOCOPY NUMBER,
1391 x_msg_data OUT NOCOPY VARCHAR2)
1392 IS
1393
1394 /* Alpha Variables */
1395
1396 L_RETURN_STATUS VARCHAR2(1) := 'S';
1397 L_MSG_DATA VARCHAR2(2000);
1398 L_MSG_TOKEN VARCHAR2(100);
1399 L_LANGUAGE_CODE VARCHAR2(4);
1400 L_ROWID VARCHAR2(18);
1401 L_KEY_EXISTS VARCHAR2(1);
1402
1403 /* Number Variables */
1404
1405 L_ORACLE_ERROR NUMBER;
1406
1407 /* Error Definitions */
1408
1409 ROW_MISSING_ERROR EXCEPTION;
1410
1411 /* Define the cursors */
1412
1413 /* Language Codes */
1414
1415 CURSOR c_get_language
1416 IS
1417 SELECT lng.language_code
1418 FROM fnd_languages lng
1419 WHERE lng.language_code = l_language_code;
1420 LangRecord c_get_language%ROWTYPE;
1421
1422 BEGIN
1423
1424 /* Initialization Routine */
1425
1426 SAVEPOINT Check_Foreign_Keys;
1427 x_return_status := 'S';
1428 x_oracle_error := 0;
1429 x_msg_data := NULL;
1430 l_msg_token := p_item_code || ' ' || p_label_code || ' ' || p_language;
1431
1432 /* Check the item code */
1433
1434 GR_ITEM_GENERAL_PKG.Check_Primary_Key
1435 (p_item_code,
1436 'F',
1437 l_rowid,
1438 l_key_exists);
1439 IF NOT FND_API.To_Boolean(l_key_exists) THEN
1440 x_return_status := 'E';
1441 FND_MESSAGE.SET_NAME('GR',
1442 'GR_RECORD_NOT_FOUND');
1443 FND_MESSAGE.SET_TOKEN('CODE',
1444 p_item_code,
1445 FALSE);
1446 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1447 END IF;
1448
1449 /* Check the label code */
1450
1451 GR_LABELS_B_PKG.Check_Primary_Key
1452 (p_label_code,
1453 'F',
1454 l_rowid,
1455 l_key_exists);
1456 IF NOT FND_API.To_Boolean(l_key_exists) THEN
1457 x_return_status := 'E';
1458 FND_MESSAGE.SET_NAME('GR',
1459 'GR_RECORD_NOT_FOUND');
1460 FND_MESSAGE.SET_TOKEN('CODE',
1461 p_label_code,
1462 FALSE);
1463 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1464 END IF;
1465
1466 /* Check the language codes */
1467
1468 l_language_code := p_language;
1469 OPEN c_get_language;
1470 FETCH c_get_language INTO LangRecord;
1471 IF c_get_language%NOTFOUND THEN
1472 CLOSE c_get_language;
1473 l_msg_token := l_language_code;
1474 RAISE Row_Missing_Error;
1475 END IF;
1476 CLOSE c_get_language;
1477
1478 l_language_code := p_source_lang;
1479 OPEN c_get_language;
1480 FETCH c_get_language INTO LangRecord;
1481 IF c_get_language%NOTFOUND THEN
1482 CLOSE c_get_language;
1483 l_msg_token := l_language_code;
1484 RAISE Row_Missing_Error;
1485 END IF;
1486 CLOSE c_get_language;
1487
1488 EXCEPTION
1489
1490 WHEN Row_Missing_Error THEN
1491 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
1492 x_return_status := 'E';
1493 x_oracle_error := APP_EXCEPTION.Get_Code;
1494 FND_MESSAGE.SET_NAME('GR',
1495 'GR_RECORD_NOT_FOUND');
1496 FND_MESSAGE.SET_TOKEN('CODE',
1497 l_msg_token,
1498 FALSE);
1499 x_msg_data := FND_MESSAGE.Get;
1500
1501 WHEN OTHERS THEN
1502 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
1503 x_return_status := 'U';
1504 x_oracle_error := APP_EXCEPTION.Get_Code;
1505 l_msg_data := APP_EXCEPTION.Get_Text;
1506 FND_MESSAGE.SET_NAME('GR',
1507 'GR_UNEXPECTED_ERROR');
1508 FND_MESSAGE.SET_TOKEN('TEXT',
1509 l_msg_token,
1510 FALSE);
1511 x_msg_data := FND_MESSAGE.Get;
1512
1513 END Check_Foreign_Keys;
1514
1515 PROCEDURE Check_Integrity
1516 (p_called_by_form IN VARCHAR2,
1517 p_item_code IN VARCHAR2,
1518 p_language IN VARCHAR2,
1519 p_label_code IN VARCHAR2,
1520 p_source_lang IN VARCHAR2,
1521 p_name_description IN VARCHAR2,
1522 p_attribute_category IN VARCHAR2,
1523 p_attribute1 IN VARCHAR2,
1524 p_attribute2 IN VARCHAR2,
1525 p_attribute3 IN VARCHAR2,
1526 p_attribute4 IN VARCHAR2,
1527 p_attribute5 IN VARCHAR2,
1528 p_attribute6 IN VARCHAR2,
1529 p_attribute7 IN VARCHAR2,
1530 p_attribute8 IN VARCHAR2,
1531 p_attribute9 IN VARCHAR2,
1532 p_attribute10 IN VARCHAR2,
1533 p_attribute11 IN VARCHAR2,
1534 p_attribute12 IN VARCHAR2,
1535 p_attribute13 IN VARCHAR2,
1536 p_attribute14 IN VARCHAR2,
1537 p_attribute15 IN VARCHAR2,
1538 p_attribute16 IN VARCHAR2,
1539 p_attribute17 IN VARCHAR2,
1540 p_attribute18 IN VARCHAR2,
1541 p_attribute19 IN VARCHAR2,
1542 p_attribute20 IN VARCHAR2,
1543 p_attribute21 IN VARCHAR2,
1544 p_attribute22 IN VARCHAR2,
1545 p_attribute23 IN VARCHAR2,
1546 p_attribute24 IN VARCHAR2,
1547 p_attribute25 IN VARCHAR2,
1548 p_attribute26 IN VARCHAR2,
1549 p_attribute27 IN VARCHAR2,
1550 p_attribute28 IN VARCHAR2,
1551 p_attribute29 IN VARCHAR2,
1552 p_attribute30 IN VARCHAR2,
1553 x_return_status OUT NOCOPY VARCHAR2,
1554 x_oracle_error OUT NOCOPY NUMBER,
1555 x_msg_data OUT NOCOPY VARCHAR2)
1556 IS
1557
1558 /* Alpha Variables */
1559
1560 L_RETURN_STATUS VARCHAR2(1) := 'S';
1561 L_MSG_DATA VARCHAR2(2000);
1562 L_CODE_BLOCK VARCHAR2(100);
1563
1564 /* Number Variables */
1565
1566 L_ORACLE_ERROR NUMBER;
1567 L_RECORD_COUNT NUMBER;
1568
1569 /* Exceptions */
1570
1571 INSTALLED_LANGUAGE_ERROR EXCEPTION;
1572
1573
1574 /* Define the Cursors */
1575
1576 CURSOR c_get_language_code
1577 IS
1578 SELECT lng.installed_flag
1579 FROM fnd_languages lng
1580 WHERE lng.language_code = p_language
1581 AND lng.installed_flag IN ('B', 'I');
1582 LangRecord c_get_language_code%ROWTYPE;
1583
1584 BEGIN
1585
1586 /* Initialization Routine */
1587
1588 SAVEPOINT Check_Integrity;
1589 x_return_status := 'S';
1590 x_oracle_error := 0;
1591 x_msg_data := NULL;
1592
1593 /* Check the language isn't base or installed */
1594
1595 OPEN c_get_language_code;
1596 FETCH c_get_language_code INTO LangRecord;
1597 IF c_get_language_code%FOUND THEN
1598 CLOSE c_get_language_code;
1599 RAISE Installed_Language_Error;
1600 END IF;
1601 CLOSE c_get_language_code;
1602
1603 EXCEPTION
1604
1605 WHEN Installed_Language_Error THEN
1606 ROLLBACK TO SAVEPOINT Check_Integrity;
1607 x_return_status := 'E';
1608 FND_MESSAGE.SET_NAME('GR',
1609 'GR_INSTALLED_LANG');
1610 FND_MESSAGE.SET_TOKEN('CODE',
1611 p_language,
1612 FALSE);
1613 IF FND_API.To_Boolean(p_called_by_form) THEN
1614 APP_EXCEPTION.Raise_Exception;
1615 ELSE
1616 x_msg_data := FND_MESSAGE.Get;
1617 END IF;
1618
1619 WHEN OTHERS THEN
1620 ROLLBACK TO SAVEPOINT Check_Integrity;
1621 x_return_status := 'U';
1622 x_oracle_error := APP_EXCEPTION.Get_Code;
1623 l_msg_data := APP_EXCEPTION.Get_Text;
1624 FND_MESSAGE.SET_NAME('GR',
1625 'GR_UNEXPECTED_ERROR');
1626 FND_MESSAGE.SET_TOKEN('TEXT',
1627 l_msg_data,
1628 FALSE);
1629 IF FND_API.To_Boolean(p_called_by_form) THEN
1630 APP_EXCEPTION.Raise_Exception;
1631 ELSE
1632 x_msg_data := FND_MESSAGE.Get;
1633 END IF;
1634
1635 END Check_Integrity;
1636
1637 PROCEDURE Check_Primary_Key
1638 /* p_item_code is the item code to check.
1639 ** p_label_code is the label code to check.
1640 ** p_language is the language code part of the key
1641 ** p_called_by_form is 'T' if called by a form or 'F' if not.
1642 ** x_rowid is the row id of the record if found.
1643 ** x_key_exists is 'T' is the record is found, 'F' if not.
1644 */
1645 (p_item_code IN VARCHAR2,
1646 p_label_code IN VARCHAR2,
1647 p_language IN VARCHAR2,
1648 p_called_by_form IN VARCHAR2,
1649 x_rowid OUT NOCOPY VARCHAR2,
1650 x_key_exists OUT NOCOPY VARCHAR2)
1651 IS
1652 /* Alphanumeric variables */
1653
1654 L_MSG_DATA VARCHAR2(80);
1655
1656 /* Declare any variables and the cursor */
1657
1658
1659 CURSOR c_get_mlname_tl_rowid
1660 IS
1661 SELECT mln.rowid
1662 FROM gr_multilingual_name_tl mln
1663 WHERE mln.item_code = p_item_code
1664 AND mln.label_code = p_label_code
1665 AND mln.language = p_language;
1666 MLNameTLRecord c_get_mlname_tl_rowid%ROWTYPE;
1667
1668 BEGIN
1669
1670 l_msg_data := p_item_code || ' ' || p_label_code || ' ' || p_language;
1671
1672 x_key_exists := 'F';
1673 OPEN c_get_mlname_tl_rowid;
1674 FETCH c_get_mlname_tl_rowid INTO MLNameTLRecord;
1675 IF c_get_mlname_tl_rowid%FOUND THEN
1676 x_key_exists := 'T';
1677 x_rowid := MLNameTLRecord.rowid;
1678 ELSE
1679 x_key_exists := 'F';
1680 END IF;
1681 CLOSE c_get_mlname_tl_rowid;
1682
1683 EXCEPTION
1684
1685 WHEN Others THEN
1686 l_msg_data := APP_EXCEPTION.Get_Text;
1687 FND_MESSAGE.SET_NAME('GR',
1688 'GR_UNEXPECTED_ERROR');
1689 FND_MESSAGE.SET_TOKEN('TEXT',
1690 l_msg_data,
1691 FALSE);
1692 IF FND_API.To_Boolean(p_called_by_form) THEN
1693 APP_EXCEPTION.Raise_Exception;
1694 END IF;
1695
1696 END Check_Primary_Key;
1697
1698 /* 21-Jan-2002 Mercy Thomas BUG 2190024 - Added procedure NEW_LANGUAGE
1699 to be called from GRNLINS.sql. Generated from tltblgen. */
1700
1701 /* 28-Jan-2002 Melanie Grosser BUG 2190024 - Procedure NEW_LANGUAGE had been
1702 generated incorrectly. I regenerated it.
1703
1704 */
1705
1706 procedure NEW_LANGUAGE
1707 is
1708 begin
1709 delete from GR_MULTILINGUAL_NAME_TL T
1710 where not exists
1711 (select NULL
1712 from GR_ITEM_GENERAL B
1713 where B.ITEM_CODE = T.ITEM_CODE
1714 );
1715
1716 update GR_MULTILINGUAL_NAME_TL T set (
1717 NAME_DESCRIPTION
1718 ) = (select
1719 B.NAME_DESCRIPTION
1720 from GR_MULTILINGUAL_NAME_TL B
1721 where B.ITEM_CODE = T.ITEM_CODE
1722 and B.LABEL_CODE = T.LABEL_CODE
1723 and B.LANGUAGE = T.SOURCE_LANG)
1724 where (
1725 T.ITEM_CODE,
1726 T.LANGUAGE
1727 ) in (select
1728 SUBT.ITEM_CODE,
1729 SUBT.LANGUAGE
1730 from GR_MULTILINGUAL_NAME_TL SUBB, GR_MULTILINGUAL_NAME_TL SUBT
1731 where SUBB.ITEM_CODE = SUBT.ITEM_CODE
1732 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1733 and (SUBB.NAME_DESCRIPTION <> SUBT.NAME_DESCRIPTION
1734 ));
1735
1736 insert into GR_MULTILINGUAL_NAME_TL (
1737 ITEM_CODE,
1738 LABEL_CODE,
1739 NAME_DESCRIPTION,
1740 ATTRIBUTE_CATEGORY,
1741 ATTRIBUTE1,
1742 ATTRIBUTE2,
1743 ATTRIBUTE3,
1744 ATTRIBUTE4,
1745 ATTRIBUTE5,
1746 ATTRIBUTE6,
1747 ATTRIBUTE7,
1748 ATTRIBUTE8,
1749 ATTRIBUTE9,
1750 ATTRIBUTE10,
1751 ATTRIBUTE11,
1752 ATTRIBUTE12,
1753 ATTRIBUTE13,
1754 ATTRIBUTE14,
1755 ATTRIBUTE15,
1756 ATTRIBUTE16,
1757 ATTRIBUTE17,
1758 ATTRIBUTE18,
1759 ATTRIBUTE19,
1760 ATTRIBUTE20,
1761 ATTRIBUTE21,
1762 ATTRIBUTE22,
1763 ATTRIBUTE23,
1764 ATTRIBUTE24,
1765 ATTRIBUTE25,
1766 ATTRIBUTE26,
1767 ATTRIBUTE27,
1768 ATTRIBUTE28,
1769 ATTRIBUTE29,
1770 ATTRIBUTE30,
1771 CREATED_BY,
1772 CREATION_DATE,
1773 LAST_UPDATED_BY,
1774 LAST_UPDATE_DATE,
1775 LAST_UPDATE_LOGIN,
1776 LANGUAGE,
1777 SOURCE_LANG
1778 ) select
1779 B.ITEM_CODE,
1780 B.LABEL_CODE,
1781 B.NAME_DESCRIPTION,
1782 B.ATTRIBUTE_CATEGORY,
1783 B.ATTRIBUTE1,
1784 B.ATTRIBUTE2,
1785 B.ATTRIBUTE3,
1786 B.ATTRIBUTE4,
1787 B.ATTRIBUTE5,
1788 B.ATTRIBUTE6,
1789 B.ATTRIBUTE7,
1790 B.ATTRIBUTE8,
1791 B.ATTRIBUTE9,
1792 B.ATTRIBUTE10,
1793 B.ATTRIBUTE11,
1794 B.ATTRIBUTE12,
1795 B.ATTRIBUTE13,
1796 B.ATTRIBUTE14,
1797 B.ATTRIBUTE15,
1798 B.ATTRIBUTE16,
1799 B.ATTRIBUTE17,
1800 B.ATTRIBUTE18,
1801 B.ATTRIBUTE19,
1802 B.ATTRIBUTE20,
1803 B.ATTRIBUTE21,
1804 B.ATTRIBUTE22,
1805 B.ATTRIBUTE23,
1806 B.ATTRIBUTE24,
1807 B.ATTRIBUTE25,
1808 B.ATTRIBUTE26,
1809 B.ATTRIBUTE27,
1810 B.ATTRIBUTE28,
1811 B.ATTRIBUTE29,
1812 B.ATTRIBUTE30,
1813 B.CREATED_BY,
1814 B.CREATION_DATE,
1815 B.LAST_UPDATED_BY,
1816 B.LAST_UPDATE_DATE,
1817 B.LAST_UPDATE_LOGIN,
1818 L.LANGUAGE_CODE,
1819 B.SOURCE_LANG
1820 from GR_MULTILINGUAL_NAME_TL B, FND_LANGUAGES L
1821 where L.INSTALLED_FLAG in ('I', 'B')
1822 and B.LANGUAGE = userenv('LANG')
1823 and not exists
1824 (select NULL
1825 from GR_MULTILINGUAL_NAME_TL T
1826 where T.ITEM_CODE = B.ITEM_CODE
1827 and T.LABEL_CODE = B.LABEL_CODE
1828 and T.LANGUAGE = L.LANGUAGE_CODE);
1829 end NEW_LANGUAGE;
1830
1831 END GR_MULTILINGUAL_NAME_TL_PKG;