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