[Home] [Help]
PACKAGE BODY: APPS.GR_EXPOSURE_AUTHS_TL_PKG
Source
1 PACKAGE BODY GR_EXPOSURE_AUTHS_TL_PKG AS
2 /*$Header: GRHIEATB.pls 115.12 2002/10/29 20:45:25 mgrosser ship $*/
3
4 PROCEDURE Insert_Row
5 (p_commit IN VARCHAR2,
6 p_called_by_form IN VARCHAR2,
7 p_exposure_authority_code IN VARCHAR2,
8 p_language IN VARCHAR2,
9 p_source_lang IN VARCHAR2,
10 p_exposure_authority_desc IN VARCHAR2,
11 p_created_by IN NUMBER,
12 p_creation_date IN DATE,
13 p_last_updated_by IN NUMBER,
14 p_last_update_date IN DATE,
15 p_last_update_login IN NUMBER,
16 x_rowid OUT NOCOPY VARCHAR2,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_oracle_error OUT NOCOPY NUMBER,
19 x_msg_data OUT NOCOPY VARCHAR2)
20 IS
21 /* Alpha Variables */
22
23 L_RETURN_STATUS VARCHAR2(1) := 'S';
24 L_KEY_EXISTS VARCHAR2(1);
25 L_MSG_DATA VARCHAR2(2000);
26 L_ROWID VARCHAR2(18);
27 L_MSG_TOKEN VARCHAR2(30);
28
29 /* Number Variables */
30
31 L_ORACLE_ERROR NUMBER;
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_exposure_authority_code,
54 p_language,
55 p_source_lang,
56 p_exposure_authority_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_exposure_authority_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_exposure_auths_tl
78 (exposure_authority_code,
79 language,
80 source_lang,
81 exposure_authority_desc,
82 created_by,
83 creation_date,
84 last_updated_by,
85 last_update_date,
86 last_update_login)
87 VALUES
88 (p_exposure_authority_code,
89 p_language,
90 p_source_lang,
91 p_exposure_authority_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_exposure_authority_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_exposure_authority_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_exposure_authority_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_exposure_authority_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_exposure_authority_code IN VARCHAR2,
192 p_language IN VARCHAR2,
193 p_source_lang IN VARCHAR2,
194 p_exposure_authority_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 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_exposure_authority_code || ' ' || p_language;
228
229 /* Now call the check foreign key procedure */
230
231 Check_Foreign_Keys
232 (p_exposure_authority_code,
233 p_language,
234 p_source_lang,
235 p_exposure_authority_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_exposure_auths_tl
244 SET exposure_authority_code = p_exposure_authority_code,
245 language = p_language,
246 source_lang = p_source_lang,
247 exposure_authority_desc = p_exposure_authority_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_exposure_authority_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(30);
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 eat.exposure_authority_desc,
353 eat.created_by,
354 eat.creation_date,
355 eat.last_updated_by,
356 eat.last_update_date,
357 eat.last_update_login
358 FROM gr_exposure_auths_tl eat
359 WHERE eat.exposure_authority_code = p_exposure_authority_code
360 AND eat.language = l_language;
361 AuthDesc 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_exposure_authority_code || ' ' || p_language;
380
381 /* Remove translations with no base row */
382
383 delete from GR_EXPOSURE_AUTHS_TL T
384 where not exists
385 (select NULL
386 from GR_EXPOSURE_AUTHS_B B
387 where B.EXPOSURE_AUTHORITY_CODE = T.EXPOSURE_AUTHORITY_CODE
388 );
389
390 /* Redefault translations from the source language */
391
392 update gr_exposure_auths_tl t set (
393 exposure_authority_desc ) =
394 ( select
395 B.EXPOSURE_AUTHORITY_DESC
396 from GR_EXPOSURE_AUTHS_TL B
397 where B.EXPOSURE_AUTHORITY_CODE = T.EXPOSURE_AUTHORITY_CODE
398 and B.LANGUAGE = T.SOURCE_LANG)
399 where (
400 T.EXPOSURE_AUTHORITY_CODE,
401 T.LANGUAGE
402 ) in (select
403 SUBT.EXPOSURE_AUTHORITY_CODE,
404 SUBT.LANGUAGE
405 from GR_EXPOSURE_AUTHS_TL SUBB, GR_EXPOSURE_AUTHS_TL SUBT
406 where SUBB.EXPOSURE_AUTHORITY_CODE = SUBT.EXPOSURE_AUTHORITY_CODE
407 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
408 and (SUBB.EXPOSURE_AUTHORITY_DESC <> SUBT.EXPOSURE_AUTHORITY_DESC
409 or (SUBB.EXPOSURE_AUTHORITY_DESC is null and SUBT.EXPOSURE_AUTHORITY_DESC is not null)
410 or (SUBB.EXPOSURE_AUTHORITY_DESC is not null and SUBT.EXPOSURE_AUTHORITY_DESC is null)
411 ));
412
413
414 /* Open the language cursor and get the description entered from the
415 ** user environment variable.
416 */
417 l_language := p_language;
418 OPEN c_get_descs;
419 FETCH c_get_descs INTO AuthDesc;
420 IF c_get_descs%NOTFOUND THEN
421 CLOSE c_get_descs;
422 RAISE Language_Missing_Error;
423 ELSE
424 l_base_desc := AuthDesc.exposure_authority_desc;
425 l_created_by := AuthDesc.created_by;
426 l_creation_date := AuthDesc.creation_date;
427 l_last_updated_by := AuthDesc.last_updated_by;
428 l_last_update_date := AuthDesc.last_update_date;
429 l_last_update_login := AuthDesc.last_update_login;
430 CLOSE c_get_descs;
431 END IF;
432
433 /* Read fnd_languages for the installed and base languages.
434 ** For those that are found, read the auths tl table.
435 ** If there isn't a record in the table for that language then
436 ** insert it and go on to the next.
437 */
438 OPEN c_get_installed_languages;
439 FETCH c_get_installed_languages INTO InstLang;
440 IF c_get_installed_languages%FOUND THEN
441 WHILE c_get_installed_languages%FOUND LOOP
442 IF InstLang.language_code <> p_language THEN
443 l_language := InstLang.language_code;
444 OPEN c_get_descs;
445 FETCH c_get_descs INTO AuthDesc;
446 IF c_get_descs%NOTFOUND THEN
447 CLOSE c_get_descs;
448 INSERT INTO gr_exposure_auths_tl
449 (exposure_authority_code,
450 language,
451 exposure_authority_desc,
452 source_lang,
453 created_by,
454 creation_date,
455 last_updated_by,
456 last_update_date,
457 last_update_login)
458 VALUES
459 (p_exposure_authority_code,
460 l_language,
461 l_base_desc,
462 p_language,
463 l_created_by,
464 l_creation_date,
465 l_last_updated_by,
466 l_last_update_date,
467 l_last_update_login);
468 ELSE
469 CLOSE c_get_descs;
470 END IF;
471 END IF;
472 FETCH c_get_installed_languages INTO InstLang;
473 END LOOP;
474 END IF;
475 CLOSE c_get_installed_languages;
476
477 IF FND_API.To_Boolean(p_commit) THEN
478 COMMIT WORK;
479 END IF;
480
481 EXCEPTION
482
483 WHEN Language_Missing_Error THEN
484 ROLLBACK TO SAVEPOINT Add_Language;
485 x_return_status := 'E';
486 x_oracle_error := APP_EXCEPTION.Get_Code;
487 FND_MESSAGE.SET_NAME('GR',
488 'GR_RECORD_NOT_FOUND');
489 FND_MESSAGE.SET_TOKEN('CODE',
490 l_msg_token,
491 FALSE);
492 IF FND_API.To_Boolean(p_called_by_form) THEN
493 APP_EXCEPTION.Raise_Exception;
494 ELSE
495 x_msg_data := FND_MESSAGE.Get;
496 END IF;
497
498 WHEN OTHERS THEN
499 ROLLBACK TO SAVEPOINT Add_Language;
500 x_return_status := 'U';
501 x_oracle_error := SQLCODE;
502 l_msg_data := SUBSTR(SQLERRM, 1, 200);
503 FND_MESSAGE.SET_NAME('GR',
504 'GR_UNEXPECTED_ERROR');
505 FND_MESSAGE.SET_TOKEN('TEXT',
506 l_msg_token,
507 FALSE);
508 IF FND_API.To_Boolean(p_called_by_form) THEN
509 APP_EXCEPTION.Raise_Exception;
510 ELSE
511 x_msg_data := FND_MESSAGE.Get;
512 END IF;
513
514 END Add_Language;
515
516 PROCEDURE Lock_Row
517 (p_commit IN VARCHAR2,
518 p_called_by_form IN VARCHAR2,
519 p_rowid IN VARCHAR2,
520 p_exposure_authority_code IN VARCHAR2,
521 p_language IN VARCHAR2,
522 p_source_lang IN VARCHAR2,
523 p_exposure_authority_desc IN VARCHAR2,
524 p_created_by IN NUMBER,
525 p_creation_date IN DATE,
526 p_last_updated_by IN NUMBER,
527 p_last_update_date IN DATE,
528 p_last_update_login IN NUMBER,
529 x_return_status OUT NOCOPY VARCHAR2,
530 x_oracle_error OUT NOCOPY NUMBER,
531 x_msg_data OUT NOCOPY VARCHAR2)
532 IS
533
534 /* Alpha Variables */
535
536 L_RETURN_STATUS VARCHAR2(1) := 'S';
537 L_MSG_DATA VARCHAR2(2000);
538 L_MSG_TOKEN VARCHAR2(30);
539
540 /* Number Variables */
541
542 L_ORACLE_ERROR NUMBER;
543
544 /* Exceptions */
545
546 NO_DATA_FOUND_ERROR EXCEPTION;
547 RECORD_CHANGED_ERROR EXCEPTION;
548
549 /* Define the cursors */
550
551 CURSOR c_lock_auths_tl
552 IS
553 SELECT last_update_date
554 FROM gr_exposure_auths_tl
555 WHERE rowid = p_rowid
556 FOR UPDATE NOWAIT;
557 LockAuthRcd c_lock_auths_tl%ROWTYPE;
558 BEGIN
559
560 /* Initialization Routine */
561
562 SAVEPOINT Lock_Row;
563 x_return_status := 'S';
564 x_oracle_error := 0;
565 x_msg_data := NULL;
566 l_msg_token := p_exposure_authority_code || ' ' || p_language;
567
568 /* Now lock the record */
569
570 OPEN c_lock_auths_tl;
571 FETCH c_lock_auths_tl INTO LockAuthRcd;
572 IF c_lock_auths_tl%NOTFOUND THEN
573 CLOSE c_lock_auths_tl;
574 RAISE No_Data_Found_Error;
575 END IF;
576 CLOSE c_lock_auths_tl;
577
578 IF LockAuthRcd.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';
625 x_oracle_error := SQLCODE;
626 l_msg_data := SUBSTR(SQLERRM, 1, 200);
627 FND_MESSAGE.SET_NAME('GR',
628 'GR_UNEXPECTED_ERROR');
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_exposure_authority_code IN VARCHAR2,
645 p_language IN VARCHAR2,
646 p_source_lang IN VARCHAR2,
647 p_exposure_authority_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(30);
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 Cursors */
676
677 BEGIN
678
679 /* Initialization Routine */
680
681 SAVEPOINT Delete_Row;
682 x_return_status := 'S';
683 x_oracle_error := 0;
684 x_msg_data := NULL;
685 l_called_by_form := 'F';
686 l_msg_token := p_exposure_authority_code || ' ' || p_language;
687
688 /* Now call the check integrity procedure */
689
690 Check_Integrity
691 (l_called_by_form,
692 p_exposure_authority_code,
693 p_language,
694 p_source_lang,
695 p_exposure_authority_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_exposure_auths_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 x_msg_data := FND_MESSAGE.Get;
735 IF FND_API.To_Boolean(p_called_by_form) THEN
736 APP_EXCEPTION.Raise_Exception;
737 ELSE
738 x_msg_data := FND_MESSAGE.Get;
739 END IF;
740
741 WHEN OTHERS THEN
742 ROLLBACK TO SAVEPOINT Delete_Row;
743 x_return_status := 'U';
744 x_oracle_error := SQLCODE;
745 l_msg_data := SUBSTR(SQLERRM, 1, 200);
746 FND_MESSAGE.SET_NAME('GR',
747 'GR_UNEXPECTED_ERROR');
748 FND_MESSAGE.SET_TOKEN('TEXT',
749 l_msg_token,
750 FALSE);
751 IF FND_API.To_Boolean(p_called_by_form) THEN
752 APP_EXCEPTION.Raise_Exception;
753 ELSE
754 x_msg_data := FND_MESSAGE.Get;
755 END IF;
756
757 END Delete_Row;
758
759 PROCEDURE Delete_Rows
760 (p_commit IN VARCHAR2,
761 p_called_by_form IN VARCHAR2,
762 p_exposure_authority_code IN VARCHAR2,
763 x_return_status OUT NOCOPY VARCHAR2,
764 x_oracle_error OUT NOCOPY NUMBER,
765 x_msg_data OUT NOCOPY VARCHAR2)
766 IS
767
768 /* Alpha Variables */
769
770 L_RETURN_STATUS VARCHAR2(1) := 'S';
771 L_MSG_DATA VARCHAR2(2000);
772 L_MSG_TOKEN VARCHAR2(30);
773
774 /* Number Variables */
775
776 L_ORACLE_ERROR NUMBER;
777
778 /* Define the cursors */
779
780 BEGIN
781
782 /* Initialization Routine */
783
784 SAVEPOINT Delete_Rows;
785 x_return_status := 'S';
786 x_oracle_error := 0;
787 x_msg_data := NULL;
788 l_msg_token := p_exposure_authority_code;
789
790 DELETE FROM gr_exposure_auths_tl
791 WHERE exposure_authority_code = p_exposure_authority_code;
792
793 IF FND_API.To_Boolean(p_commit) THEN
794 COMMIT WORK;
795 END IF;
796
797 EXCEPTION
798
799 WHEN OTHERS THEN
800 ROLLBACK TO SAVEPOINT Delete_Rows;
801 x_return_status := 'U';
802 x_oracle_error := SQLCODE;
803 l_msg_data := SUBSTR(SQLERRM, 1, 200);
804 FND_MESSAGE.SET_NAME('GR',
805 'GR_UNEXPECTED_ERROR');
806 FND_MESSAGE.SET_TOKEN('TEXT',
807 l_msg_token,
808 FALSE);
809 IF FND_API.To_Boolean(p_called_by_form) THEN
810 APP_EXCEPTION.Raise_Exception;
811 ELSE
812 x_msg_data := FND_MESSAGE.Get;
813 END IF;
814
815 END Delete_Rows;
816
817 PROCEDURE Check_Foreign_Keys
818 (p_exposure_authority_code IN VARCHAR2,
819 p_language IN VARCHAR2,
820 p_source_lang IN VARCHAR2,
821 p_exposure_authority_desc IN VARCHAR2,
822 x_return_status OUT NOCOPY VARCHAR2,
823 x_oracle_error OUT NOCOPY NUMBER,
824 x_msg_data OUT NOCOPY VARCHAR2)
825 IS
826
827 /* Alpha Variables */
828
829 L_RETURN_STATUS VARCHAR2(1) := 'S';
830 L_MSG_DATA VARCHAR2(2000);
831 L_MSG_TOKEN VARCHAR2(30);
832 L_LANGUAGE_CODE VARCHAR2(4);
833
834 /* Number Variables */
835
836 L_ORACLE_ERROR NUMBER;
837
838 /* Define the cursors */
839
840 CURSOR c_get_language
841 IS
842 SELECT lng.language_code
843 FROM fnd_languages lng
844 WHERE lng.language_code = l_language_code;
845 LangRecord c_get_language%ROWTYPE;
846
847 BEGIN
848
849 /* Initialization Routine */
850
851 SAVEPOINT Check_Foreign_Keys;
852 x_return_status := 'S';
853 x_oracle_error := 0;
854 x_msg_data := NULL;
855 l_msg_token := p_exposure_authority_code || ' ' || p_language;
856
857 /* Check the language codes */
858
859 l_language_code := p_language;
860 OPEN c_get_language;
861 FETCH c_get_language INTO LangRecord;
862 IF c_get_language%NOTFOUND THEN
863 l_msg_token := l_language_code;
864 x_return_status := 'E';
865 x_oracle_error := APP_EXCEPTION.Get_Code;
866 FND_MESSAGE.SET_NAME('GR',
867 'GR_RECORD_NOT_FOUND');
868 FND_MESSAGE.SET_TOKEN('CODE',
869 l_msg_token,
870 FALSE);
871 l_msg_data := FND_MESSAGE.Get;
872 END IF;
873 CLOSE c_get_language;
874
875 l_language_code := p_source_lang;
876 OPEN c_get_language;
877 FETCH c_get_language INTO LangRecord;
878 IF c_get_language%NOTFOUND THEN
879 l_msg_token := l_language_code;
880 x_return_status := 'E';
881 x_oracle_error := APP_EXCEPTION.Get_Code;
882 FND_MESSAGE.SET_NAME('GR',
883 'GR_RECORD_NOT_FOUND');
884 FND_MESSAGE.SET_TOKEN('CODE',
885 l_msg_token,
886 FALSE);
887 l_msg_data := FND_MESSAGE.Get;
888 END IF;
889 CLOSE c_get_language;
890
891 IF x_return_status <> 'S' THEN
892 x_msg_data := l_msg_data;
893 END IF;
894
895 EXCEPTION
896
897 WHEN OTHERS THEN
898 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
899 x_return_status := 'U';
900 x_oracle_error := SQLCODE;
901 l_msg_data := SUBSTR(SQLERRM, 1, 200);
902 FND_MESSAGE.SET_NAME('GR',
903 'GR_UNEXPECTED_ERROR');
904 FND_MESSAGE.SET_TOKEN('TEXT',
905 l_msg_token,
906 FALSE);
907 x_msg_data := FND_MESSAGE.Get;
908
909 END Check_Foreign_Keys;
910
911 PROCEDURE Check_Integrity
912 (p_called_by_form IN VARCHAR2,
913 p_exposure_authority_code IN VARCHAR2,
914 p_language IN VARCHAR2,
915 p_source_lang IN VARCHAR2,
916 p_exposure_authority_desc IN VARCHAR2,
917 x_return_status OUT NOCOPY VARCHAR2,
918 x_oracle_error OUT NOCOPY NUMBER,
919 x_msg_data OUT NOCOPY VARCHAR2)
920 IS
921
922 /* Alpha Variables */
923
924 L_RETURN_STATUS VARCHAR2(1) := 'S';
925 L_MSG_DATA VARCHAR2(2000);
926 L_CODE_BLOCK VARCHAR2(30);
927
928 /* Number Variables */
929
930 L_ORACLE_ERROR NUMBER;
931 L_RECORD_COUNT NUMBER;
932
933 /* Exceptions */
934
935 INSTALLED_LANGUAGE_ERROR EXCEPTION;
936
937 /* Define the Cursors */
938
939 CURSOR c_get_language_code
940 IS
941 SELECT lng.installed_flag
942 FROM fnd_languages lng
943 WHERE lng.language_code = p_language
944 AND lng.installed_flag IN ('I', 'B');
945 LangRecord c_get_language_code%ROWTYPE;
946
947 BEGIN
948
949 /* Initialization Routine */
950
951 SAVEPOINT Check_Integrity;
952 x_return_status := 'S';
953 x_oracle_error := 0;
954 x_msg_data := NULL;
955
956 /* Check the language isn't base or installed */
957
958 OPEN c_get_language_code;
959 FETCH c_get_language_code INTO LangRecord;
960 IF c_get_language_code%FOUND THEN
961 CLOSE c_get_language_code;
962 RAISE Installed_Language_Error;
963 END IF;
964 CLOSE c_get_language_code;
965
966 EXCEPTION
967
968 WHEN Installed_Language_Error THEN
969 ROLLBACK TO SAVEPOINT Check_Integrity;
970 x_return_status := 'E';
971 FND_MESSAGE.SET_NAME('GR',
972 'GR_INSTALLED_LANG');
973 FND_MESSAGE.SET_TOKEN('CODE',
974 p_language,
975 FALSE);
976 IF FND_API.To_Boolean(p_called_by_form) THEN
977 APP_EXCEPTION.Raise_Exception;
978 ELSE
979 x_msg_data := FND_MESSAGE.Get;
980 END IF;
981
982 WHEN OTHERS THEN
983 ROLLBACK TO SAVEPOINT Check_Integrity;
984 x_return_status := 'U';
985 x_oracle_error := SQLCODE;
986 l_msg_data := SUBSTR(SQLERRM, 1, 200);
987 FND_MESSAGE.SET_NAME('GR',
988 'GR_UNEXPECTED_ERROR');
989 FND_MESSAGE.SET_TOKEN('TEXT',
990 l_msg_data,
991 FALSE);
992 IF FND_API.To_Boolean(p_called_by_form) THEN
993 APP_EXCEPTION.Raise_Exception;
994 ELSE
995 x_msg_data := FND_MESSAGE.Get;
996 END IF;
997
998 END Check_Integrity;
999
1000 PROCEDURE Check_Primary_Key
1001 /* p_exposure_authority_code is the authority code to check.
1002 ** p_language is the language code part of the key
1003 ** p_called_by_form is 'T' if called by a form or 'F' if not.
1004 ** x_rowid is the row id of the record if found.
1005 ** x_key_exists is 'T' is the record is found, 'F' if not.
1006 */
1007 (p_exposure_authority_code IN VARCHAR2,
1008 p_language IN VARCHAR2,
1009 p_called_by_form IN VARCHAR2,
1010 x_rowid OUT NOCOPY VARCHAR2,
1011 x_key_exists OUT NOCOPY VARCHAR2)
1012 IS
1013 /* Alphanumeric variables */
1014
1015 L_MSG_DATA VARCHAR2(80);
1016
1017 /* Declare any variables and the cursor */
1018
1019
1020 CURSOR c_get_auths_tl_rowid
1021 IS
1022 SELECT eat.rowid
1023 FROM gr_exposure_auths_tl eat
1024 WHERE eat.exposure_authority_code = p_exposure_authority_code
1025 AND eat.language = p_language;
1026 AuthTLRecord c_get_auths_tl_rowid%ROWTYPE;
1027
1028 BEGIN
1029
1030 l_msg_data := p_exposure_authority_code || ' ' || p_language;
1031
1032 x_key_exists := 'F';
1033 OPEN c_get_auths_tl_rowid;
1034 FETCH c_get_auths_tl_rowid INTO AuthTLRecord;
1035 IF c_get_auths_tl_rowid%FOUND THEN
1036 x_key_exists := 'T';
1037 x_rowid := AuthTLRecord.rowid;
1038 ELSE
1039 x_key_exists := 'F';
1040 END IF;
1041 CLOSE c_get_auths_tl_rowid;
1042
1043 EXCEPTION
1044
1045 WHEN Others THEN
1046 l_msg_data := SUBSTR(SQLERRM, 1, 200);
1047 FND_MESSAGE.SET_NAME('GR',
1048 'GR_UNEXPECTED_ERROR');
1049 FND_MESSAGE.SET_TOKEN('TEXT',
1050 l_msg_data,
1051 FALSE);
1052 IF FND_API.To_Boolean(p_called_by_form) THEN
1053 APP_EXCEPTION.Raise_Exception;
1054 END IF;
1055
1056 END Check_Primary_Key;
1057
1058 PROCEDURE translate_row (
1059 X_EXPOSURE_AUTHORITY_CODE IN VARCHAR2
1060 ,X_LANGUAGE IN VARCHAR2
1061 ,X_EXPOSURE_AUTHORITY_DESC IN VARCHAR2
1062 ,X_SOURCE_LANG IN VARCHAR2
1063 ) IS
1064 BEGIN
1065 UPDATE GR_EXPOSURE_AUTHS_TL SET
1066 EXPOSURE_AUTHORITY_DESC = X_EXPOSURE_AUTHORITY_DESC,
1067 SOURCE_LANG = USERENV('LANG'),
1068 LAST_UPDATE_DATE = sysdate,
1069 LAST_UPDATED_BY = 0,
1070 LAST_UPDATE_LOGIN = 0
1071 WHERE (EXPOSURE_AUTHORITY_CODE = X_EXPOSURE_AUTHORITY_CODE)
1072 AND (USERENV('LANG') IN (LANGUAGE, SOURCE_LANG));
1073 END TRANSLATE_ROW;
1074
1075
1076 PROCEDURE load_row (
1077 X_EXPOSURE_AUTHORITY_CODE IN VARCHAR2
1078 ,X_LANGUAGE IN VARCHAR2
1079 ,X_EXPOSURE_AUTHORITY_DESC IN VARCHAR2
1080 ,X_SOURCE_LANG IN VARCHAR2
1081 ) IS
1082 CURSOR Cur_rowid IS
1083 SELECT rowid
1084 FROM GR_EXPOSURE_AUTHS_TL
1085 WHERE (EXPOSURE_AUTHORITY_CODE = X_EXPOSURE_AUTHORITY_CODE)
1086 AND (LANGUAGE = X_LANGUAGE);
1087 l_user_id NUMBER DEFAULT 1;
1088 l_row_id VARCHAR2(64);
1089 l_return_status VARCHAR2(1);
1090 l_oracle_error NUMBER;
1091 l_msg_data VARCHAR2(2000);
1092 BEGIN
1093 OPEN Cur_rowid;
1094 FETCH Cur_rowid INTO l_row_id;
1095 IF Cur_rowid%FOUND THEN
1096 GR_EXPOSURE_AUTHS_TL_PKG.UPDATE_ROW(
1097 P_COMMIT => 'T'
1098 ,P_CALLED_BY_FORM => 'F'
1099 ,P_ROWID => l_row_id
1100 ,P_EXPOSURE_AUTHORITY_CODE => X_EXPOSURE_AUTHORITY_CODE
1101 ,P_LANGUAGE => X_LANGUAGE
1102 ,P_EXPOSURE_AUTHORITY_DESC => X_EXPOSURE_AUTHORITY_DESC
1103 ,P_SOURCE_LANG => X_SOURCE_LANG
1104 ,P_CREATED_BY => l_user_id
1105 ,P_CREATION_DATE => sysdate
1106 ,P_LAST_UPDATED_BY => l_user_id
1107 ,P_LAST_UPDATE_DATE => sysdate
1108 ,P_LAST_UPDATE_LOGIN => 0
1109 ,X_RETURN_STATUS => l_return_status
1110 ,X_ORACLE_ERROR => l_oracle_error
1111 ,X_MSG_DATA => l_msg_data);
1112 ELSE
1113 GR_EXPOSURE_AUTHS_TL_PKG.INSERT_ROW(
1114 P_COMMIT => 'T'
1115 ,P_CALLED_BY_FORM => 'F'
1116 ,P_EXPOSURE_AUTHORITY_CODE => X_EXPOSURE_AUTHORITY_CODE
1117 ,P_LANGUAGE => X_LANGUAGE
1118 ,P_EXPOSURE_AUTHORITY_DESC => X_EXPOSURE_AUTHORITY_DESC
1119 ,P_SOURCE_LANG => X_SOURCE_LANG
1120 ,P_CREATED_BY => l_user_id
1121 ,P_CREATION_DATE => sysdate
1122 ,P_LAST_UPDATED_BY => l_user_id
1123 ,P_LAST_UPDATE_DATE => sysdate
1124 ,P_LAST_UPDATE_LOGIN => 0
1125 ,X_ROWID => l_row_id
1126 ,X_RETURN_STATUS => l_return_status
1127 ,X_ORACLE_ERROR => l_oracle_error
1128 ,X_MSG_DATA => l_msg_data);
1129 END IF;
1130 CLOSE Cur_rowid;
1131 END LOAD_ROW;
1132
1133
1134 /* 21-Jan-2002 Melanie Grosser BUG 2190024 - Added procedure NEW_LANGUAGE
1135 to be called from GRNLINS.sql. Generated
1136 from tltblgen.
1137 */
1138 procedure NEW_LANGUAGE
1139 is
1140 begin
1141 delete from GR_EXPOSURE_AUTHS_TL T
1142 where not exists
1143 (select NULL
1144 from GR_EXPOSURE_AUTHS_B B
1145 where B.EXPOSURE_AUTHORITY_CODE = T.EXPOSURE_AUTHORITY_CODE
1146 );
1147
1148 update GR_EXPOSURE_AUTHS_TL T set (
1149 EXPOSURE_AUTHORITY_DESC
1150 ) = (select
1151 B.EXPOSURE_AUTHORITY_DESC
1152 from GR_EXPOSURE_AUTHS_TL B
1153 where B.EXPOSURE_AUTHORITY_CODE = T.EXPOSURE_AUTHORITY_CODE
1154 and B.LANGUAGE = T.SOURCE_LANG)
1155 where (
1156 T.EXPOSURE_AUTHORITY_CODE,
1157 T.LANGUAGE
1158 ) in (select
1159 SUBT.EXPOSURE_AUTHORITY_CODE,
1160 SUBT.LANGUAGE
1161 from GR_EXPOSURE_AUTHS_TL SUBB, GR_EXPOSURE_AUTHS_TL SUBT
1162 where SUBB.EXPOSURE_AUTHORITY_CODE = SUBT.EXPOSURE_AUTHORITY_CODE
1163 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1164 and (SUBB.EXPOSURE_AUTHORITY_DESC <> SUBT.EXPOSURE_AUTHORITY_DESC
1165 ));
1166
1167 insert into GR_EXPOSURE_AUTHS_TL (
1168 EXPOSURE_AUTHORITY_CODE,
1169 EXPOSURE_AUTHORITY_DESC,
1170 CREATED_BY,
1171 CREATION_DATE,
1172 LAST_UPDATED_BY,
1173 LAST_UPDATE_DATE,
1174 LAST_UPDATE_LOGIN,
1175 LANGUAGE,
1176 SOURCE_LANG
1177 ) select
1178 B.EXPOSURE_AUTHORITY_CODE,
1179 B.EXPOSURE_AUTHORITY_DESC,
1180 B.CREATED_BY,
1181 B.CREATION_DATE,
1182 B.LAST_UPDATED_BY,
1183 B.LAST_UPDATE_DATE,
1184 B.LAST_UPDATE_LOGIN,
1185 L.LANGUAGE_CODE,
1186 B.SOURCE_LANG
1187 from GR_EXPOSURE_AUTHS_TL B, FND_LANGUAGES L
1188 where L.INSTALLED_FLAG in ('I', 'B')
1189 and B.LANGUAGE = userenv('LANG')
1190 and not exists
1191 (select NULL
1192 from GR_EXPOSURE_AUTHS_TL T
1193 where T.EXPOSURE_AUTHORITY_CODE = B.EXPOSURE_AUTHORITY_CODE
1194 and T.LANGUAGE = L.LANGUAGE_CODE);
1195 end NEW_LANGUAGE;
1196
1197
1198 END GR_EXPOSURE_AUTHS_TL_PKG;