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