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