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