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