[Home] [Help]
PACKAGE BODY: APPS.GR_TOXIC_ROUTES_TL_PKG
Source
1 PACKAGE BODY GR_TOXIC_ROUTES_TL_PKG AS
2 /*$Header: GRHITRTB.pls 115.11 2002/11/15 16:57:22 gkelly ship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_toxic_route_code IN VARCHAR2,
7 p_language IN VARCHAR2,
8 p_source_lang IN VARCHAR2,
9 p_toxic_route_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 /* 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_route_code,
53 p_language,
54 p_source_lang,
55 p_toxic_route_desc,
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_route_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_routes_tl
77 (toxic_route_code,
78 language,
79 source_lang,
80 toxic_route_desc,
81 created_by,
82 creation_date,
83 last_updated_by,
84 last_update_date,
85 last_update_login)
86 VALUES
87 (p_toxic_route_code,
88 p_language,
89 p_source_lang,
90 p_toxic_route_desc,
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_route_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_route_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_route_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_route_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_route_code IN VARCHAR2,
191 p_language IN VARCHAR2,
192 p_source_lang IN VARCHAR2,
193 p_toxic_route_desc 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_route_code || ' ' || p_language;
228
229 /* Now call the check foreign key procedure */
230
231 Check_Foreign_Keys
232 (p_toxic_route_code,
233 p_language,
234 p_source_lang,
235 p_toxic_route_desc,
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_routes_tl
244 SET toxic_route_code = p_toxic_route_code,
245 language = p_language,
246 source_lang = p_source_lang,
247 toxic_route_desc = p_toxic_route_desc,
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_route_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_descs
351 IS
352 SELECT trt.toxic_route_desc,
353 trt.created_by,
354 trt.creation_date,
355 trt.last_updated_by,
356 trt.last_update_date,
357 trt.last_update_login
358 FROM gr_toxic_routes_tl trt
359 WHERE trt.toxic_route_code = p_toxic_route_code
360 AND trt.language = l_language;
361 RouteDesc 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
371 BEGIN
372
373 /* Initialization Routine */
374
375 SAVEPOINT Add_Language;
376 x_return_status := 'S';
377 x_oracle_error := 0;
378 x_msg_data := NULL;
379 l_msg_token := p_toxic_route_code || ' ' || p_language;
380
381 /* Remove translations with no base row */
382
383 delete from GR_TOXIC_ROUTES_TL T
384 where not exists
385 (select NULL
386 from GR_TOXIC_ROUTES_B B
387 where B.TOXIC_ROUTE_CODE = T.TOXIC_ROUTE_CODE
388 );
389
390 /* Redefault translations from the source language */
391
392 update gr_toxic_routes_tl t set (
393 toxic_route_desc ) =
394 ( select
395 B.TOXIC_ROUTE_DESC
396 from GR_TOXIC_ROUTES_TL B
397 where B.TOXIC_ROUTE_CODE = T.TOXIC_ROUTE_CODE
398 and B.LANGUAGE = T.SOURCE_LANG)
399 where (
400 T.TOXIC_ROUTE_CODE,
401 T.LANGUAGE
402 ) in (select
403 SUBT.TOXIC_ROUTE_CODE,
404 SUBT.LANGUAGE
405 from GR_TOXIC_ROUTES_TL SUBB, GR_TOXIC_ROUTES_TL SUBT
406 where SUBB.TOXIC_ROUTE_CODE = SUBT.TOXIC_ROUTE_CODE
407 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
408 and (SUBB.TOXIC_ROUTE_DESC <> SUBT.TOXIC_ROUTE_DESC
409 or (SUBB.TOXIC_ROUTE_DESC is null and SUBT.TOXIC_ROUTE_DESC is not null)
410 or (SUBB.TOXIC_ROUTE_DESC is not null and SUBT.TOXIC_ROUTE_DESC is null)
411 ));
412
413 /* Open the language cursor and get the description entered from the
414 ** user environment variable.
415 */
416 l_language := p_language;
417 OPEN c_get_descs;
418 FETCH c_get_descs INTO RouteDesc;
419 IF c_get_descs%NOTFOUND THEN
420 CLOSE c_get_descs;
421 RAISE Language_Missing_Error;
422 ELSE
423 l_base_desc := RouteDesc.toxic_route_desc;
424 l_created_by := RouteDesc.created_by;
425 l_creation_date := RouteDesc.creation_date;
426 l_last_updated_by := RouteDesc.last_updated_by;
427 l_last_update_date := RouteDesc.last_update_date;
428 l_last_update_login := RouteDesc.last_update_login;
429 CLOSE c_get_descs;
430 END IF;
431
432 /* Read fnd_languages for the installed and base languages.
433 ** For those that are found, read the labels tl table.
434 ** If there isn't a record in the table for that language then
435 ** insert it and go on to the next.
436 */
437 OPEN c_get_installed_languages;
438 FETCH c_get_installed_languages INTO InstLang;
439 IF c_get_installed_languages%FOUND THEN
440 WHILE c_get_installed_languages%FOUND LOOP
441 IF InstLang.language_code <> p_language THEN
442 l_language := InstLang.language_code;
443 OPEN c_get_descs;
444 FETCH c_get_descs INTO RouteDesc;
445 IF c_get_descs%NOTFOUND THEN
446 CLOSE c_get_descs;
447 INSERT INTO gr_toxic_routes_tl
448 (toxic_route_code,
449 language,
450 toxic_route_desc,
451 source_lang,
452 created_by,
453 creation_date,
454 last_updated_by,
455 last_update_date,
456 last_update_login)
457 VALUES
458 (p_toxic_route_code,
459 l_language,
460 l_base_desc,
461 p_language,
462 l_created_by,
463 l_creation_date,
464 l_last_updated_by,
465 l_last_update_date,
466 l_last_update_login);
467 ELSE
468 CLOSE c_get_descs;
469 END IF;
470 END IF;
471 FETCH c_get_installed_languages INTO InstLang;
472 END LOOP;
473 END IF;
474 CLOSE c_get_installed_languages;
475
476 IF FND_API.To_Boolean(p_commit) THEN
477 COMMIT WORK;
478 END IF;
479
480 EXCEPTION
481
482 WHEN Language_Missing_Error THEN
483 ROLLBACK TO SAVEPOINT Add_Language;
484 x_return_status := 'E';
485 x_oracle_error := APP_EXCEPTION.Get_Code;
486 FND_MESSAGE.SET_NAME('GR',
487 'GR_RECORD_NOT_FOUND');
488 FND_MESSAGE.SET_TOKEN('CODE',
489 l_msg_token,
490 FALSE);
491 IF FND_API.To_Boolean(p_called_by_form) THEN
492 APP_EXCEPTION.Raise_Exception;
493 ELSE
494 x_msg_data := FND_MESSAGE.Get;
495 END IF;
496
497 WHEN OTHERS THEN
498 ROLLBACK TO SAVEPOINT Add_Language;
499 x_return_status := 'U';
500 x_oracle_error := SQLCODE;
501 l_msg_data := SUBSTR(SQLERRM, 1, 200);
502 FND_MESSAGE.SET_NAME('GR',
503 'GR_UNEXPECTED_ERROR');
504 FND_MESSAGE.SET_TOKEN('TEXT',
505 l_msg_token,
506 FALSE);
507 IF FND_API.To_Boolean(p_called_by_form) THEN
508 APP_EXCEPTION.Raise_Exception;
509 ELSE
510 x_msg_data := FND_MESSAGE.Get;
511 END IF;
512
513 END Add_Language;
514
515 PROCEDURE Lock_Row
516 (p_commit IN VARCHAR2,
517 p_called_by_form IN VARCHAR2,
518 p_rowid IN VARCHAR2,
519 p_toxic_route_code IN VARCHAR2,
520 p_language IN VARCHAR2,
521 p_source_lang IN VARCHAR2,
522 p_toxic_route_desc IN VARCHAR2,
523 p_created_by IN NUMBER,
524 p_creation_date IN DATE,
525 p_last_updated_by IN NUMBER,
526 p_last_update_date IN DATE,
530 x_msg_data OUT NOCOPY VARCHAR2)
527 p_last_update_login IN NUMBER,
528 x_return_status OUT NOCOPY VARCHAR2,
529 x_oracle_error OUT NOCOPY NUMBER,
531 IS
532
533 /* Alpha Variables */
534
535 L_RETURN_STATUS VARCHAR2(1) := 'S';
536 L_MSG_DATA VARCHAR2(2000);
537 L_MSG_TOKEN VARCHAR2(100);
538
539 /* Number Variables */
540
541 L_ORACLE_ERROR NUMBER;
542
543 /* Exceptions */
544
545 NO_DATA_FOUND_ERROR EXCEPTION;
546 RECORD_CHANGED_ERROR EXCEPTION;
547
548 /* Define the cursors */
549
550 CURSOR c_lock_routes_tl
551 IS
552 SELECT last_update_date
553 FROM gr_toxic_routes_tl
554 WHERE rowid = p_rowid
555 FOR UPDATE NOWAIT;
556 LockRouteRcd c_lock_routes_tl%ROWTYPE;
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_route_code || ' ' || p_language;
566
567 /* Now lock the record */
568
569 OPEN c_lock_routes_tl;
570 FETCH c_lock_routes_tl INTO LockRouteRcd;
571 IF c_lock_routes_tl%NOTFOUND THEN
572 CLOSE c_lock_routes_tl;
573 RAISE No_Data_Found_Error;
574 END IF;
575 CLOSE c_lock_routes_tl;
576
577 IF LockRouteRcd.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');
592 FND_MESSAGE.SET_TOKEN('CODE',
593 l_msg_token,
594 FALSE);
595 IF FND_API.To_Boolean(p_called_by_form) THEN
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_route_code IN VARCHAR2,
644 p_language IN VARCHAR2,
645 p_source_lang IN VARCHAR2,
646 p_toxic_route_desc 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 cursor */
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_route_code || ' ' || p_language;
686
687 /* Now call the check integrity procedure */
688
689 Check_Integrity
690 (l_called_by_form,
691 p_toxic_route_code,
692 p_language,
693 p_source_lang,
694 p_toxic_route_desc,
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_routes_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;
714 WHEN Check_Integrity_Error THEN
711
712 EXCEPTION
713
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);
749 IF FND_API.To_Boolean(p_called_by_form) THEN
750 APP_EXCEPTION.Raise_Exception;
751 ELSE
752 x_msg_data := FND_MESSAGE.Get;
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_route_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_route_code;
787
788 DELETE FROM gr_toxic_routes_tl
789 WHERE toxic_route_code = p_toxic_route_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_route_code IN VARCHAR2,
817 p_language IN VARCHAR2,
818 p_source_lang IN VARCHAR2,
819 p_toxic_route_desc 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_route_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
893 EXCEPTION
890 x_msg_data := l_msg_data;
891 END IF;
892
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_route_code IN VARCHAR2,
912 p_language IN VARCHAR2,
913 p_source_lang IN VARCHAR2,
914 p_toxic_route_desc IN VARCHAR2,
915 x_return_status OUT NOCOPY VARCHAR2,
916 x_oracle_error OUT NOCOPY NUMBER,
917 x_msg_data OUT NOCOPY VARCHAR2)
918 IS
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 /* Check the language isn't base or installed */
955
956 OPEN c_get_language_code;
957 FETCH c_get_language_code INTO LangRecord;
958 IF c_get_language_code%FOUND THEN
959 CLOSE c_get_language_code;
960 RAISE Installed_Language_Error;
961 END IF;
962 CLOSE c_get_language_code;
963
964 EXCEPTION
965
966 WHEN Installed_Language_Error THEN
967 ROLLBACK TO SAVEPOINT Check_Integrity;
968 x_return_status := 'E';
969 FND_MESSAGE.SET_NAME('GR',
970 'GR_INSTALLED_LANG');
971 FND_MESSAGE.SET_TOKEN('CODE',
972 p_language,
973 FALSE);
974 IF FND_API.To_Boolean(p_called_by_form) THEN
975 APP_EXCEPTION.Raise_Exception;
976 ELSE
977 x_msg_data := FND_MESSAGE.Get;
978 END IF;
979
980 WHEN OTHERS THEN
981 ROLLBACK TO SAVEPOINT Check_Integrity;
982 x_return_status := 'U';
983 x_oracle_error := SQLCODE;
984 l_msg_data := SUBSTR(SQLERRM, 1, 200);
985 FND_MESSAGE.SET_NAME('GR',
986 'GR_UNEXPECTED_ERROR');
987 FND_MESSAGE.SET_TOKEN('TEXT',
988 l_msg_data,
989 FALSE);
990 IF FND_API.To_Boolean(p_called_by_form) THEN
991 APP_EXCEPTION.Raise_Exception;
992 ELSE
993 x_msg_data := FND_MESSAGE.Get;
994 END IF;
995
996 END Check_Integrity;
997
998 PROCEDURE Check_Primary_Key
999 /* p_toxic_route_code is the route code to check.
1000 ** p_language is the language code part of the key
1001 ** p_called_by_form is 'T' if called by a form or 'F' if not.
1002 ** x_rowid is the row id of the record if found.
1003 ** x_key_exists is 'T' is the record is found, 'F' if not.
1004 */
1005 (p_toxic_route_code IN VARCHAR2,
1006 p_language IN VARCHAR2,
1007 p_called_by_form IN VARCHAR2,
1008 x_rowid OUT NOCOPY VARCHAR2,
1009 x_key_exists OUT NOCOPY VARCHAR2)
1010 IS
1011 /* Alphanumeric variables */
1012
1013 L_MSG_DATA VARCHAR2(80);
1014
1015 /* Declare any variables and the cursor */
1016
1017
1018 CURSOR c_get_routes_tl_rowid
1019 IS
1020 SELECT trt.rowid
1021 FROM gr_toxic_routes_tl trt
1022 WHERE trt.toxic_route_code = p_toxic_route_code
1023 AND trt.language = p_language;
1024 RouteTLRecord c_get_routes_tl_rowid%ROWTYPE;
1025
1026 BEGIN
1027
1028 l_msg_data := p_toxic_route_code || ' ' || p_language;
1029
1030 x_key_exists := 'F';
1031 OPEN c_get_routes_tl_rowid;
1032 FETCH c_get_routes_tl_rowid INTO RouteTLRecord;
1033 IF c_get_routes_tl_rowid%FOUND THEN
1034 x_key_exists := 'T';
1035 x_rowid := RouteTLRecord.rowid;
1036 ELSE
1037 x_key_exists := 'F';
1038 END IF;
1039 CLOSE c_get_routes_tl_rowid;
1040
1041 EXCEPTION
1042
1043 WHEN Others THEN
1044 l_msg_data := SUBSTR(SQLERRM, 1, 200);
1045 FND_MESSAGE.SET_NAME('GR',
1046 'GR_UNEXPECTED_ERROR');
1047 FND_MESSAGE.SET_TOKEN('TEXT',
1048 l_msg_data,
1049 FALSE);
1050 IF FND_API.To_Boolean(p_called_by_form) THEN
1051 APP_EXCEPTION.Raise_Exception;
1052 END IF;
1053
1054 END Check_Primary_Key;
1055
1056 PROCEDURE translate_row (
1057 X_TOXIC_ROUTE_CODE IN VARCHAR2
1058 ,X_LANGUAGE IN VARCHAR2
1059 ,X_SOURCE_LANG IN VARCHAR2
1060 ,X_TOXIC_ROUTE_DESC IN VARCHAR2
1061 ) IS
1062 BEGIN
1063 UPDATE GR_TOXIC_ROUTES_TL SET
1064 TOXIC_ROUTE_DESC = X_TOXIC_ROUTE_DESC,
1065 SOURCE_LANG = USERENV('LANG'),
1066 LAST_UPDATE_DATE = sysdate,
1067 LAST_UPDATED_BY = 0,
1068 LAST_UPDATE_LOGIN = 0
1072
1069 WHERE (TOXIC_ROUTE_CODE = X_TOXIC_ROUTE_CODE)
1070 AND (USERENV('LANG') IN (LANGUAGE, SOURCE_LANG));
1071 END TRANSLATE_ROW;
1073
1074 PROCEDURE load_row (
1075 X_TOXIC_ROUTE_CODE IN VARCHAR2
1076 ,X_LANGUAGE IN VARCHAR2
1077 ,X_SOURCE_LANG IN VARCHAR2
1078 ,X_TOXIC_ROUTE_DESC IN VARCHAR2
1079 ) IS
1080 CURSOR Cur_rowid IS
1081 SELECT rowid
1082 FROM GR_TOXIC_ROUTES_TL
1083 WHERE (TOXIC_ROUTE_CODE = X_TOXIC_ROUTE_CODE)
1084 AND (LANGUAGE = X_LANGUAGE);
1085 l_user_id NUMBER DEFAULT 1;
1086 l_row_id VARCHAR2(64);
1087 l_return_status VARCHAR2(1);
1088 l_oracle_error NUMBER;
1089 l_msg_data VARCHAR2(2000);
1090 BEGIN
1091 OPEN Cur_rowid;
1092 FETCH Cur_rowid INTO l_row_id;
1093 IF Cur_rowid%FOUND THEN
1094 GR_TOXIC_ROUTES_TL_PKG.UPDATE_ROW(
1095 P_COMMIT => 'T'
1096 ,P_CALLED_BY_FORM => 'F'
1097 ,P_ROWID => l_row_id
1098 ,P_TOXIC_ROUTE_CODE => X_TOXIC_ROUTE_CODE
1099 ,P_LANGUAGE => X_LANGUAGE
1100 ,P_SOURCE_LANG => X_SOURCE_LANG
1101 ,P_TOXIC_ROUTE_DESC => X_TOXIC_ROUTE_DESC
1102 ,P_CREATED_BY => l_user_id
1103 ,P_CREATION_DATE => sysdate
1104 ,P_LAST_UPDATED_BY => l_user_id
1105 ,P_LAST_UPDATE_DATE => sysdate
1106 ,P_LAST_UPDATE_LOGIN => 0
1107 ,X_RETURN_STATUS => l_return_status
1108 ,X_ORACLE_ERROR => l_oracle_error
1109 ,X_MSG_DATA => l_msg_data);
1110 ELSE
1111 GR_TOXIC_ROUTES_TL_PKG.INSERT_ROW(
1112 P_COMMIT => 'T'
1113 ,P_CALLED_BY_FORM => 'F'
1114 ,P_TOXIC_ROUTE_CODE => X_TOXIC_ROUTE_CODE
1115 ,P_LANGUAGE => X_LANGUAGE
1116 ,P_SOURCE_LANG => X_SOURCE_LANG
1117 ,P_TOXIC_ROUTE_DESC => X_TOXIC_ROUTE_DESC
1118 ,P_CREATED_BY => l_user_id
1119 ,P_CREATION_DATE => sysdate
1120 ,P_LAST_UPDATED_BY => l_user_id
1121 ,P_LAST_UPDATE_DATE => sysdate
1122 ,P_LAST_UPDATE_LOGIN => 0
1123 ,X_ROWID => l_row_id
1124 ,X_RETURN_STATUS => l_return_status
1125 ,X_ORACLE_ERROR => l_oracle_error
1126 ,X_MSG_DATA => l_msg_data);
1127 END IF;
1128 CLOSE Cur_rowid;
1129 END LOAD_ROW;
1130
1131 /* 21-Jan-2002 Melanie Grosser BUG 2190024 - Added procedure NEW_LANGUAGE
1132 to be called from GRNLINS.sql. Generated
1133 from tltblgen.
1134 */
1135 procedure NEW_LANGUAGE
1136 is
1137 begin
1138 delete from GR_TOXIC_ROUTES_TL T
1139 where not exists
1140 (select NULL
1141 from GR_TOXIC_ROUTES_B B
1142 where B.TOXIC_ROUTE_CODE = T.TOXIC_ROUTE_CODE
1143 );
1144
1145 update GR_TOXIC_ROUTES_TL T set (
1146 TOXIC_ROUTE_DESC
1147 ) = (select
1148 B.TOXIC_ROUTE_DESC
1149 from GR_TOXIC_ROUTES_TL B
1150 where B.TOXIC_ROUTE_CODE = T.TOXIC_ROUTE_CODE
1151 and B.LANGUAGE = T.SOURCE_LANG)
1152 where (
1153 T.TOXIC_ROUTE_CODE,
1154 T.LANGUAGE
1155 ) in (select
1156 SUBT.TOXIC_ROUTE_CODE,
1157 SUBT.LANGUAGE
1158 from GR_TOXIC_ROUTES_TL SUBB, GR_TOXIC_ROUTES_TL SUBT
1159 where SUBB.TOXIC_ROUTE_CODE = SUBT.TOXIC_ROUTE_CODE
1160 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1161 and (SUBB.TOXIC_ROUTE_DESC <> SUBT.TOXIC_ROUTE_DESC
1162 ));
1163
1164 insert into GR_TOXIC_ROUTES_TL (
1165 TOXIC_ROUTE_CODE,
1166 TOXIC_ROUTE_DESC,
1167 CREATED_BY,
1168 CREATION_DATE,
1169 LAST_UPDATED_BY,
1170 LAST_UPDATE_DATE,
1171 LAST_UPDATE_LOGIN,
1172 LANGUAGE,
1173 SOURCE_LANG
1174 ) select
1175 B.TOXIC_ROUTE_CODE,
1176 B.TOXIC_ROUTE_DESC,
1177 B.CREATED_BY,
1178 B.CREATION_DATE,
1179 B.LAST_UPDATED_BY,
1180 B.LAST_UPDATE_DATE,
1181 B.LAST_UPDATE_LOGIN,
1182 L.LANGUAGE_CODE,
1183 B.SOURCE_LANG
1184 from GR_TOXIC_ROUTES_TL B, FND_LANGUAGES L
1185 where L.INSTALLED_FLAG in ('I', 'B')
1186 and B.LANGUAGE = userenv('LANG')
1187 and not exists
1188 (select NULL
1189 from GR_TOXIC_ROUTES_TL T
1190 where T.TOXIC_ROUTE_CODE = B.TOXIC_ROUTE_CODE
1191 and T.LANGUAGE = L.LANGUAGE_CODE);
1192 end NEW_LANGUAGE;
1193
1194
1195
1196 END GR_TOXIC_ROUTES_TL_PKG;