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