[Home] [Help]
PACKAGE BODY: APPS.GR_RISK_PHRASES_TL_PKG
Source
1 PACKAGE BODY GR_RISK_PHRASES_TL_PKG AS
2 /*$Header: GRHIRPTB.pls 120.1 2006/06/16 21:41:20 pbamb noship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_risk_phrase_code IN VARCHAR2,
7 p_language IN VARCHAR2,
8 p_source_language IN VARCHAR2,
9 p_risk_description IN VARCHAR2,
10 p_created_by IN NUMBER,
11 p_creation_date IN DATE,
12 p_last_updated_by IN NUMBER,
13 p_last_update_date IN DATE,
14 p_last_update_login IN NUMBER,
15 x_rowid OUT NOCOPY VARCHAR2,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_oracle_error OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2)
19 IS
20 /* Alpha Variables */
21
22 L_RETURN_STATUS VARCHAR2(1) := 'S';
23 L_KEY_EXISTS VARCHAR2(1);
24 L_MSG_DATA VARCHAR2(2000);
25 L_ROWID VARCHAR2(18);
26 L_MSG_TOKEN VARCHAR2(100);
27
28 /* Number Variables */
29
30 L_ORACLE_ERROR NUMBER;
31 /* Exceptions */
32
33 FOREIGN_KEY_ERROR EXCEPTION;
34 ITEM_EXISTS_ERROR EXCEPTION;
35 ROW_MISSING_ERROR EXCEPTION;
36
37 /* Declare cursors */
38
39
40 BEGIN
41
42 /* Initialization Routine */
43
44 SAVEPOINT Insert_Row;
45 x_return_status := 'S';
46 x_oracle_error := 0;
47 x_msg_data := NULL;
48
49 /* Now call the check foreign key procedure */
50
51 Check_Foreign_Keys
52 (p_risk_phrase_code,
53 p_language,
54 p_source_language,
55 p_risk_description,
56 l_return_status,
57 l_oracle_error,
58 l_msg_data);
59 IF l_return_status <> 'S' THEN
60 RAISE Foreign_Key_Error;
61 END IF;
62
63 /* Now check the primary key doesn't already exist */
64
65 Check_Primary_Key
66 (p_risk_phrase_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_risk_phrases_tl
77 (risk_phrase_code,
78 language,
79 source_lang,
80 risk_description,
81 created_by,
82 creation_date,
83 last_updated_by,
84 last_update_date,
85 last_update_login)
86 VALUES
87 (p_risk_phrase_code,
88 p_language,
89 p_source_language,
90 p_risk_description,
91 p_created_by,
92 p_creation_date,
93 p_last_updated_by,
94 p_last_update_date,
95 p_last_update_login);
96
97 /* Now get the row id of the inserted record */
98
99 Check_Primary_Key
100 (p_risk_phrase_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_risk_phrase_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_THERE');
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_risk_phrase_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_risk_phrase_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_risk_phrase_code IN VARCHAR2,
191 p_language IN VARCHAR2,
192 p_source_language IN VARCHAR2,
193 p_risk_description IN VARCHAR2,
194 p_created_by IN NUMBER,
195 p_creation_date IN DATE,
196 p_last_updated_by IN NUMBER,
197 p_last_update_date IN DATE,
198 p_last_update_login IN NUMBER,
199 x_return_status OUT NOCOPY VARCHAR2,
200 x_oracle_error OUT NOCOPY NUMBER,
201 x_msg_data OUT NOCOPY VARCHAR2)
202 IS
203
204 /* Alpha Variables */
205
206 L_RETURN_STATUS VARCHAR2(1) := 'S';
207 L_MSG_DATA VARCHAR2(2000);
208 L_MSG_TOKEN VARCHAR2(100);
209
210 /* Number Variables */
211
212 L_ORACLE_ERROR NUMBER;
213
214 /* Exceptions */
215
216 FOREIGN_KEY_ERROR EXCEPTION;
217 ROW_MISSING_ERROR EXCEPTION;
218
219 BEGIN
220
221 /* Initialization Routine */
222
223 SAVEPOINT Update_Row;
224 x_return_status := 'S';
225 x_oracle_error := 0;
226 x_msg_data := NULL;
227 l_msg_token := p_risk_phrase_code || ' ' || p_language;
228
229 /* Now call the check foreign key procedure */
230
231 Check_Foreign_Keys
232 (p_risk_phrase_code,
233 p_language,
234 p_source_language,
235 p_risk_description,
236 l_return_status,
237 l_oracle_error,
238 l_msg_data);
239
240 IF l_return_status <> 'S' THEN
241 RAISE Foreign_Key_Error;
242 ELSE
243 UPDATE gr_risk_phrases_tl
244 SET risk_phrase_code = p_risk_phrase_code,
245 language = p_language,
246 source_lang = p_source_language,
247 risk_description = p_risk_description,
248 created_by = p_created_by,
249 creation_date = p_creation_date,
250 last_updated_by = p_last_updated_by,
251 last_update_date = p_last_update_date,
252 last_update_login = p_last_update_login
253 WHERE rowid = p_rowid;
254 IF SQL%NOTFOUND THEN
255 RAISE Row_Missing_Error;
256 END IF;
257 END IF;
258
259 /* Check the commit flag and if set, then commit the work. */
260
261 IF FND_API.To_Boolean(p_commit) THEN
262 COMMIT WORK;
263 END IF;
264
265 EXCEPTION
266
267 WHEN Foreign_Key_Error THEN
268 ROLLBACK TO SAVEPOINT Update_Row;
269 x_return_status := l_return_status;
270 x_oracle_error := l_oracle_error;
271 FND_MESSAGE.SET_NAME('GR',
272 'GR_FOREIGN_KEY_ERROR');
273 FND_MESSAGE.SET_TOKEN('TEXT',
274 l_msg_data,
275 FALSE);
276 IF FND_API.To_Boolean(p_called_by_form) THEN
277 APP_EXCEPTION.Raise_Exception;
278 ELSE
279 x_msg_data := FND_MESSAGE.Get;
280 END IF;
281
282 WHEN Row_Missing_Error THEN
283 ROLLBACK TO SAVEPOINT Update_Row;
284 x_return_status := 'E';
285 x_oracle_error := APP_EXCEPTION.Get_Code;
286 FND_MESSAGE.SET_NAME('GR',
287 'GR_NO_RECORD_INSERTED');
288 FND_MESSAGE.SET_TOKEN('CODE',
289 l_msg_token,
290 FALSE);
291 IF FND_API.To_Boolean(p_called_by_form) THEN
292 APP_EXCEPTION.Raise_Exception;
293 ELSE
294 x_msg_data := FND_MESSAGE.Get;
295 END IF;
296
297 WHEN OTHERS THEN
298 ROLLBACK TO SAVEPOINT Update_Row;
299 x_return_status := 'U';
300 x_oracle_error := SQLCODE;
301 l_msg_data := SUBSTR(SQLERRM, 1, 200);
302 FND_MESSAGE.SET_NAME('GR',
303 'GR_UNEXPECTED_ERROR');
304 FND_MESSAGE.SET_TOKEN('TEXT',
305 l_msg_token,
306 FALSE);
307 IF FND_API.To_Boolean(p_called_by_form) THEN
308 APP_EXCEPTION.Raise_Exception;
309 ELSE
310 x_msg_data := FND_MESSAGE.Get;
311 END IF;
312
313 END Update_Row;
314
315 PROCEDURE Add_Language
316 (p_commit IN VARCHAR2,
317 p_called_by_form IN VARCHAR2,
318 p_risk_phrase_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 /* Alpha Variables */
326
327 L_RETURN_STATUS VARCHAR2(1) := 'S';
328 L_MSG_DATA VARCHAR2(2000);
329 L_MSG_TOKEN VARCHAR2(100);
330 L_BASE_DESC VARCHAR2(240);
331 L_LANGUAGE VARCHAR2(4);
332 L_CREATION_DATE DATE;
333 L_LAST_UPDATE_DATE DATE;
334
335 /* Number Variables */
336
337 L_ORACLE_ERROR NUMBER;
338 L_CREATED_BY NUMBER;
339 L_LAST_UPDATED_BY NUMBER;
340 L_LAST_UPDATE_LOGIN NUMBER;
341
342 /* Exceptions */
343
344 LANGUAGE_MISSING_ERROR EXCEPTION;
345
346
347 /* Cursors */
348
349 CURSOR c_get_descs
350 IS
351 SELECT rpt.risk_description,
352 rpt.created_by,
353 rpt.creation_date,
354 rpt.last_updated_by,
355 rpt.last_update_date,
356 rpt.last_update_login
357 FROM gr_risk_phrases_tl rpt
358 WHERE rpt.risk_phrase_code = p_risk_phrase_code
359 AND rpt.language = l_language;
360 PhraseDesc c_get_descs%ROWTYPE;
361
362 CURSOR c_get_installed_languages
363 IS
364 SELECT lng.language_code
365 FROM fnd_languages lng
366 WHERE lng.installed_flag IN ('I', 'B');
367 InstLang c_get_installed_languages%ROWTYPE;
368
369
370 BEGIN
371
372 /* Initialization Routine */
373
374 SAVEPOINT Add_Language;
375 x_return_status := 'S';
376 x_oracle_error := 0;
377 x_msg_data := NULL;
378 l_msg_token := p_risk_phrase_code || ' ' || p_language;
379
380 /* Remove translations with no base row */
381
382 delete from GR_RISK_PHRASES_TL T
383 where not exists
384 (select NULL
385 from GR_RISK_PHRASES_B B
386 where B.RISK_PHRASE_CODE = T.RISK_PHRASE_CODE
387 );
388
389 /* Redefault translations from the source language */
390
391 update gr_risk_phrases_tl t set (
392 risk_description ) =
393 ( select
394 B.RISK_DESCRIPTION
395 from GR_RISK_PHRASES_TL B
396 where B.RISK_PHRASE_CODE = T.RISK_PHRASE_CODE
397 and B.LANGUAGE = T.SOURCE_LANG)
398 where (
399 T.RISK_PHRASE_CODE,
400 T.LANGUAGE
401 ) in (select
402 SUBT.RISK_PHRASE_CODE,
403 SUBT.LANGUAGE
404 from GR_RISK_PHRASES_TL SUBB, GR_RISK_PHRASES_TL SUBT
405 where SUBB.RISK_PHRASE_CODE = SUBT.RISK_PHRASE_CODE
406 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
407 and (SUBB.RISK_DESCRIPTION <> SUBT.RISK_DESCRIPTION
408 or (SUBB.RISK_DESCRIPTION is null and SUBT.RISK_DESCRIPTION is not null)
409 or (SUBB.RISK_DESCRIPTION is not null and SUBT.RISK_DESCRIPTION is null)
410 ));
411
412 /* Open the language cursor and get the description entered from the
413 ** user environment variable.
414 */
415 l_language := p_language;
416 OPEN c_get_descs;
417 FETCH c_get_descs INTO PhraseDesc;
418 IF c_get_descs%NOTFOUND THEN
419 CLOSE c_get_descs;
420 RAISE Language_Missing_Error;
421 ELSE
422 l_base_desc := PhraseDesc.risk_description;
423 l_created_by := PhraseDesc.created_by;
424 l_creation_date := PhraseDesc.creation_date;
425 l_last_updated_by := PhraseDesc.last_updated_by;
426 l_last_update_date := PhraseDesc.last_update_date;
427 l_last_update_login := PhraseDesc.last_update_login;
428 CLOSE c_get_descs;
429 END IF;
430
431 /* Read fnd_languages for the installed and base languages.
432 ** For those that are found, read the risk phrase tl table.
433 ** If there isn't a record in the table for that language then
434 ** insert it and go on to the next.
435 */
436 OPEN c_get_installed_languages;
437 FETCH c_get_installed_languages INTO InstLang;
438 IF c_get_installed_languages%FOUND THEN
439 WHILE c_get_installed_languages%FOUND LOOP
440 IF InstLang.language_code <> p_language THEN
441 l_language := InstLang.language_code;
442 OPEN c_get_descs;
443 FETCH c_get_descs INTO PhraseDesc;
444 IF c_get_descs%NOTFOUND THEN
445 CLOSE c_get_descs;
446 INSERT INTO gr_risk_phrases_tl
447 (risk_phrase_code,
448 language,
449 source_lang,
450 risk_description,
451 created_by,
452 creation_date,
453 last_updated_by,
454 last_update_date,
455 last_update_login)
456 VALUES
457 (p_risk_phrase_code,
458 l_language,
459 p_language,
460 l_base_desc,
461 l_created_by,
462 l_creation_date,
463 l_last_updated_by,
464 l_last_update_date,
465 l_last_update_login);
466 ELSE
467 CLOSE c_get_descs;
468 END IF;
469 END IF;
470 FETCH c_get_installed_languages INTO InstLang;
471 END LOOP;
472 END IF;
473 CLOSE c_get_installed_languages;
474
475 IF FND_API.To_Boolean(p_commit) THEN
476 COMMIT WORK;
477 END IF;
478
479 EXCEPTION
480
481 WHEN Language_Missing_Error THEN
482 ROLLBACK TO SAVEPOINT Add_Language;
483 x_return_status := 'E';
484 x_oracle_error := APP_EXCEPTION.Get_Code;
485 FND_MESSAGE.SET_NAME('GR',
486 'GR_RECORD_NOT_FOUND');
487 FND_MESSAGE.SET_TOKEN('CODE',
488 l_msg_token,
489 FALSE);
490 IF FND_API.To_Boolean(p_called_by_form) THEN
491 APP_EXCEPTION.Raise_Exception;
492 ELSE
493 x_msg_data := FND_MESSAGE.Get;
494 END IF;
495
496 WHEN OTHERS THEN
497 ROLLBACK TO SAVEPOINT Add_Language;
498 x_return_status := 'U';
499 x_oracle_error := SQLCODE;
500 l_msg_data := SUBSTR(SQLERRM, 1, 200);
501 FND_MESSAGE.SET_NAME('GR',
502 'GR_UNEXPECTED_ERROR');
503 FND_MESSAGE.SET_TOKEN('TEXT',
504 l_msg_token,
505 FALSE);
506 IF FND_API.To_Boolean(p_called_by_form) THEN
507 APP_EXCEPTION.Raise_Exception;
508 ELSE
509 x_msg_data := FND_MESSAGE.Get;
510 END IF;
511
512 END Add_Language;
513
514 PROCEDURE Lock_Row
515 (p_commit IN VARCHAR2,
516 p_called_by_form IN VARCHAR2,
517 p_rowid IN VARCHAR2,
518 p_risk_phrase_code IN VARCHAR2,
519 p_language IN VARCHAR2,
520 p_source_language IN VARCHAR2,
521 p_risk_description IN VARCHAR2,
522 p_created_by IN NUMBER,
523 p_creation_date IN DATE,
524 p_last_updated_by IN NUMBER,
525 p_last_update_date IN DATE,
526 p_last_update_login IN NUMBER,
527 x_return_status OUT NOCOPY VARCHAR2,
528 x_oracle_error OUT NOCOPY NUMBER,
529 x_msg_data OUT NOCOPY VARCHAR2)
530 IS
531
532 /* Alpha Variables */
533
534 L_RETURN_STATUS VARCHAR2(1) := 'S';
535 L_MSG_DATA VARCHAR2(2000);
536 L_MSG_TOKEN VARCHAR2(100);
537
538 /* Number Variables */
539
540 L_ORACLE_ERROR NUMBER;
541
542 /* Exceptions */
543
544 NO_DATA_FOUND_ERROR EXCEPTION;
545 RECORD_CHANGED_ERROR EXCEPTION;
546
547
548 /* Define the cursors */
549
550 CURSOR c_lock_risk_tl
551 IS
552 SELECT last_update_date
553 FROM gr_risk_phrases_tl
554 WHERE rowid = p_rowid
555 FOR UPDATE NOWAIT;
556 LockRiskRcd c_lock_risk_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_risk_phrase_code || ' ' || p_language;
566
567 /* Now lock the record */
568
569 OPEN c_lock_risk_tl;
570 FETCH c_lock_risk_tl INTO LockRiskRcd;
571 IF c_lock_risk_tl%NOTFOUND THEN
572 CLOSE c_lock_risk_tl;
573 RAISE No_Data_Found_Error;
574 END IF;
575 CLOSE c_lock_risk_tl;
576
577 IF LockRiskRcd.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
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_risk_phrase_code IN VARCHAR2,
645 p_language IN VARCHAR2,
646 p_source_language IN VARCHAR2,
647 p_risk_description IN VARCHAR2,
648 p_created_by IN NUMBER,
649 p_creation_date IN DATE,
650 p_last_updated_by IN NUMBER,
651 p_last_update_date IN DATE,
652 p_last_update_login IN NUMBER,
653 x_return_status OUT NOCOPY VARCHAR2,
654 x_oracle_error OUT NOCOPY NUMBER,
655 x_msg_data OUT NOCOPY VARCHAR2)
656 IS
657
658 /* Alpha Variables */
659
660 L_RETURN_STATUS VARCHAR2(1) := 'S';
661 L_MSG_DATA VARCHAR2(2000);
662 L_MSG_TOKEN VARCHAR2(100);
663 L_CALLED_BY_FORM VARCHAR2(1);
664
665 /* Number Variables */
666
667 L_ORACLE_ERROR NUMBER;
668
669 /* Exceptions */
670
671 CHECK_INTEGRITY_ERROR EXCEPTION;
672 ROW_MISSING_ERROR EXCEPTION;
673 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
674
675 /* Define the cursors */
676
677 BEGIN
678
679 /* Initialization Routine */
680
681 SAVEPOINT Delete_Row;
682 x_return_status := 'S';
683 l_called_by_form := 'F';
684 x_oracle_error := 0;
685 x_msg_data := NULL;
686 l_msg_token := p_risk_phrase_code || ' ' || p_language;
687
688 /* Now call the check integrity procedure */
689
690 Check_Integrity
691 (l_called_by_form,
692 p_risk_phrase_code,
693 p_language,
694 p_source_language,
695 p_risk_description,
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_risk_phrases_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 x_msg_data := l_msg_data;
720 IF FND_API.To_Boolean(p_called_by_form) THEN
721 APP_EXCEPTION.Raise_Exception;
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_risk_phrase_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_risk_phrase_code;
787
788 DELETE FROM gr_risk_phrases_tl
789 WHERE risk_phrase_code = p_risk_phrase_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 END Delete_Rows;
808
809 PROCEDURE Check_Foreign_Keys
810 (p_risk_phrase_code IN VARCHAR2,
811 p_language IN VARCHAR2,
812 p_source_language IN VARCHAR2,
813 p_risk_description IN VARCHAR2,
814 x_return_status OUT NOCOPY VARCHAR2,
815 x_oracle_error OUT NOCOPY NUMBER,
816 x_msg_data OUT NOCOPY VARCHAR2)
817 IS
818
819 /* Alpha Variables */
820
821 L_RETURN_STATUS VARCHAR2(1) := 'S';
822 L_MSG_DATA VARCHAR2(2000);
823 L_MSG_TOKEN VARCHAR2(100);
824 L_LANGUAGE_CODE VARCHAR2(4);
825
826 /* Number Variables */
827
828 L_ORACLE_ERROR NUMBER;
829
830 /* Error Definitions */
831
832 ROW_MISSING_ERROR EXCEPTION;
833
834 /* Define the cursors */
835
836 CURSOR c_get_language
837 IS
838 SELECT lng.language_code
839 FROM fnd_languages lng
840 WHERE lng.language_code = l_language_code;
841 LangRecord c_get_language%ROWTYPE;
842
843 BEGIN
844
845 /* Initialization Routine */
846
847 SAVEPOINT Check_Foreign_Keys;
848 x_return_status := 'S';
849 x_oracle_error := 0;
850 x_msg_data := NULL;
851 l_msg_token := p_risk_phrase_code || ' ' || p_language;
852
853 /* Check the language codes */
854
855 l_language_code := p_language;
856 OPEN c_get_language;
857 FETCH c_get_language INTO LangRecord;
858 IF c_get_language%NOTFOUND THEN
859 CLOSE c_get_language;
860 l_msg_token := l_language_code;
861 RAISE Row_Missing_Error;
862 END IF;
863 CLOSE c_get_language;
864
865 l_language_code := p_source_language;
866 OPEN c_get_language;
867 FETCH c_get_language INTO LangRecord;
868 IF c_get_language%NOTFOUND THEN
869 CLOSE c_get_language;
870 l_msg_token := l_language_code;
871 RAISE Row_Missing_Error;
872 END IF;
873 CLOSE c_get_language;
874
875 EXCEPTION
876
877 WHEN Row_Missing_Error THEN
878 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
879 x_return_status := 'E';
880 x_oracle_error := APP_EXCEPTION.Get_Code;
881 FND_MESSAGE.SET_NAME('GR',
882 'GR_RECORD_NOT_FOUND');
883 FND_MESSAGE.SET_TOKEN('CODE',
884 l_msg_token,
885 FALSE);
886 WHEN OTHERS THEN
887 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
888 x_return_status := 'U';
889 x_oracle_error := SQLCODE;
890 l_msg_data := SUBSTR(SQLERRM, 1, 200);
891 FND_MESSAGE.SET_NAME('GR',
892 'GR_UNEXPECTED_ERROR');
893 FND_MESSAGE.SET_TOKEN('TEXT',
894 l_msg_token,
895 FALSE);
896 END Check_Foreign_Keys;
897
898 PROCEDURE Check_Integrity
899 (p_called_by_form IN VARCHAR2,
900 p_risk_phrase_code IN VARCHAR2,
901 p_language IN VARCHAR2,
902 p_source_language IN VARCHAR2,
903 p_risk_description IN VARCHAR2,
904 x_return_status OUT NOCOPY VARCHAR2,
905 x_oracle_error OUT NOCOPY NUMBER,
906 x_msg_data OUT NOCOPY VARCHAR2)
907 IS
908
909 /* Alpha Variables */
910
911 L_RETURN_STATUS VARCHAR2(1) := 'S';
912 L_MSG_DATA VARCHAR2(2000);
913 L_CODE_BLOCK VARCHAR2(30);
914
915 /* Number Variables */
916
917 L_ORACLE_ERROR NUMBER;
918 L_RECORD_COUNT NUMBER;
919
920 /* Exceptions */
921
922 INSTALLED_LANGUAGE_ERROR EXCEPTION;
923
924 /* Define the Cursors */
925
926 CURSOR c_get_language_code
927 IS
928 SELECT lng.installed_flag
929 FROM fnd_languages lng
930 WHERE lng.language_code = p_language
931 AND lng.installed_flag IN ('B', 'I');
932 LangRecord c_get_language_code%ROWTYPE;
933
934 BEGIN
935
936 /* Initialization Routine */
937
938 SAVEPOINT Check_Integrity;
939 x_return_status := 'S';
940 x_oracle_error := 0;
941 x_msg_data := NULL;
942
943 /* Check the language isn't base or installed */
944
945 OPEN c_get_language_code;
946 FETCH c_get_language_code INTO LangRecord;
947 IF c_get_language_code%FOUND THEN
948 CLOSE c_get_language_code;
949 RAISE Installed_Language_Error;
950 END IF;
951 CLOSE c_get_language_code;
952
953 EXCEPTION
954
955 WHEN Installed_Language_Error THEN
956 ROLLBACK TO SAVEPOINT Check_Integrity;
957 x_return_status := 'E';
958 FND_MESSAGE.SET_NAME('GR',
959 'GR_INSTALLED_LANG');
960 FND_MESSAGE.SET_TOKEN('CODE',
961 p_language,
962 FALSE);
963
964 IF FND_API.To_Boolean(p_called_by_form) THEN
965 APP_EXCEPTION.Raise_Exception;
966 ELSE
967 x_msg_data := FND_MESSAGE.Get;
968 END IF;
969
970 WHEN OTHERS THEN
971 ROLLBACK TO SAVEPOINT Check_Integrity;
972 x_return_status := 'U';
973 x_oracle_error := SQLCODE;
974 l_msg_data := SUBSTR(SQLERRM, 1, 200);
975 FND_MESSAGE.SET_NAME('GR',
976 'GR_UNEXPECTED_ERROR');
977 FND_MESSAGE.SET_TOKEN('TEXT',
978 l_msg_data,
979 FALSE);
980 IF FND_API.To_Boolean(p_called_by_form) THEN
981 APP_EXCEPTION.Raise_Exception;
982 ELSE
983 x_msg_data := FND_MESSAGE.Get;
984 END IF;
985
986 END Check_Integrity;
987
988 PROCEDURE Check_Primary_Key
989 /* p_risk_phrase_code is the risk phrase code to check.
990 ** p_language is the language code part of the key
991 ** p_called_by_form is 'T' if called by a form or 'F' if not.
992 ** x_rowid is the row id of the record if found.
993 ** x_key_exists is 'T' is the record is found, 'F' if not.
994 */
995 (p_risk_phrase_code IN VARCHAR2,
996 p_language IN VARCHAR2,
997 p_called_by_form IN VARCHAR2,
998 x_rowid OUT NOCOPY VARCHAR2,
999 x_key_exists OUT NOCOPY VARCHAR2)
1000 IS
1001 /* Alphanumeric variables */
1002
1003 L_MSG_DATA VARCHAR2(80);
1004
1005 /* Declare any variables and the cursor */
1006
1007
1008 CURSOR c_get_risk_tl_rowid
1009 IS
1010 SELECT rpt.rowid
1011 FROM gr_risk_phrases_tl rpt
1012 WHERE rpt.risk_phrase_code = p_risk_phrase_code
1013 AND rpt.language = p_language;
1014 RiskTLRecord c_get_risk_tl_rowid%ROWTYPE;
1015
1016 BEGIN
1017
1018 l_msg_data := p_risk_phrase_code || ' ' || p_language;
1019
1020 x_key_exists := 'F';
1021 OPEN c_get_risk_tl_rowid;
1022 FETCH c_get_risk_tl_rowid INTO RiskTLRecord;
1023 IF c_get_risk_tl_rowid%FOUND THEN
1024 x_key_exists := 'T';
1025 x_rowid := RiskTLRecord.rowid;
1026 ELSE
1027 x_key_exists := 'F';
1028 END IF;
1029 CLOSE c_get_risk_tl_rowid;
1030
1031 EXCEPTION
1032
1033 WHEN Others THEN
1034 l_msg_data := SUBSTR(SQLERRM, 1, 200);
1035 FND_MESSAGE.SET_NAME('GR',
1036 'GR_UNEXPECTED_ERROR');
1037 FND_MESSAGE.SET_TOKEN('TEXT',
1038 l_msg_data,
1039 FALSE);
1040 IF FND_API.To_Boolean(p_called_by_form) THEN
1041 APP_EXCEPTION.Raise_Exception;
1042 END IF;
1043
1044 END Check_Primary_Key;
1045
1046 PROCEDURE translate_row (
1047 X_RISK_PHRASE_CODE IN VARCHAR2
1048 ,X_LANGUAGE IN VARCHAR2
1049 ,X_SOURCE_LANG IN VARCHAR2
1050 ,X_RISK_DESCRIPTION IN VARCHAR2
1051 ) IS
1052 BEGIN
1053 UPDATE GR_RISK_PHRASES_TL SET
1054 RISK_DESCRIPTION = X_RISK_DESCRIPTION,
1055 SOURCE_LANG = USERENV('LANG'),
1056 LAST_UPDATE_DATE = sysdate,
1057 LAST_UPDATED_BY = 0,
1058 LAST_UPDATE_LOGIN = 0
1059 WHERE (RISK_PHRASE_CODE = X_RISK_PHRASE_CODE)
1060 AND (USERENV('LANG') IN (LANGUAGE, SOURCE_LANG));
1061 END TRANSLATE_ROW;
1062
1063 PROCEDURE translate_row (
1064 X_RISK_PHRASE_CODE IN VARCHAR2
1065 ,X_RISK_DESCRIPTION IN VARCHAR2
1066 ,X_OWNER IN VARCHAR2
1067 ) IS
1068 BEGIN
1069 UPDATE GR_RISK_PHRASES_TL SET
1070 RISK_DESCRIPTION = X_RISK_DESCRIPTION,
1071 SOURCE_LANG = USERENV('LANG'),
1072 LAST_UPDATE_DATE = sysdate,
1073 LAST_UPDATED_BY = Decode(X_OWNER, 'SEED', 1, 0),
1074 LAST_UPDATE_LOGIN = 0
1075 WHERE (RISK_PHRASE_CODE = X_RISK_PHRASE_CODE)
1076 AND (USERENV('LANG') IN (LANGUAGE, SOURCE_LANG));
1077 END TRANSLATE_ROW;
1078
1079
1080 PROCEDURE load_row (
1081 X_RISK_PHRASE_CODE IN VARCHAR2
1082 ,X_LANGUAGE IN VARCHAR2
1083 ,X_SOURCE_LANG IN VARCHAR2
1084 ,X_RISK_DESCRIPTION IN VARCHAR2
1085 ) IS
1086 CURSOR Cur_rowid IS
1087 SELECT rowid
1088 FROM GR_RISK_PHRASES_TL
1089 WHERE (RISK_PHRASE_CODE = X_RISK_PHRASE_CODE)
1090 AND (LANGUAGE = X_LANGUAGE);
1091 l_user_id NUMBER DEFAULT 1;
1092 l_row_id VARCHAR2(64);
1093 l_return_status VARCHAR2(1);
1094 l_oracle_error NUMBER;
1095 l_msg_data VARCHAR2(2000);
1096 BEGIN
1097 OPEN Cur_rowid;
1098 FETCH Cur_rowid INTO l_row_id;
1099 IF Cur_rowid%FOUND THEN
1100 GR_RISK_PHRASES_TL_PKG.UPDATE_ROW(
1101 P_COMMIT => 'T'
1102 ,P_CALLED_BY_FORM => 'F'
1103 ,P_ROWID => l_row_id
1104 ,P_RISK_PHRASE_CODE => X_RISK_PHRASE_CODE
1105 ,P_LANGUAGE => X_LANGUAGE
1106 ,P_SOURCE_LANGUAGE => X_SOURCE_LANG
1107 ,P_RISK_DESCRIPTION => X_RISK_DESCRIPTION
1108 ,P_CREATED_BY => l_user_id
1109 ,P_CREATION_DATE => sysdate
1110 ,P_LAST_UPDATED_BY => l_user_id
1111 ,P_LAST_UPDATE_DATE => sysdate
1112 ,P_LAST_UPDATE_LOGIN => 0
1113 ,X_RETURN_STATUS => l_return_status
1114 ,X_ORACLE_ERROR => l_oracle_error
1115 ,X_MSG_DATA => l_msg_data);
1116 ELSE
1117 GR_RISK_PHRASES_TL_PKG.INSERT_ROW(
1118 P_COMMIT => 'T'
1119 ,P_CALLED_BY_FORM => 'F'
1120 ,P_RISK_PHRASE_CODE => X_RISK_PHRASE_CODE
1121 ,P_LANGUAGE => X_LANGUAGE
1122 ,P_SOURCE_LANGUAGE => X_SOURCE_LANG
1123 ,P_RISK_DESCRIPTION => X_RISK_DESCRIPTION
1124 ,P_CREATED_BY => l_user_id
1125 ,P_CREATION_DATE => sysdate
1126 ,P_LAST_UPDATED_BY => l_user_id
1127 ,P_LAST_UPDATE_DATE => sysdate
1128 ,P_LAST_UPDATE_LOGIN => 0
1129 ,X_ROWID => l_row_id
1130 ,X_RETURN_STATUS => l_return_status
1131 ,X_ORACLE_ERROR => l_oracle_error
1132 ,X_MSG_DATA => l_msg_data);
1133 END IF;
1134 CLOSE Cur_rowid;
1135 END LOAD_ROW;
1136
1137 PROCEDURE load_row (
1138 X_RISK_PHRASE_CODE IN VARCHAR2
1139 ,X_RISK_DESCRIPTION IN VARCHAR2
1140 ,X_OWNER IN VARCHAR2
1141 ) IS
1142 CURSOR Cur_rowid IS
1143 SELECT rowid
1144 FROM GR_RISK_PHRASES_TL
1145 WHERE (RISK_PHRASE_CODE = X_RISK_PHRASE_CODE)
1146 AND USERENV('LANG') in (LANGUAGE,SOURCE_LANG);
1147 l_user_id NUMBER DEFAULT 0;
1148 l_row_id VARCHAR2(64);
1149 l_return_status VARCHAR2(1);
1150 l_oracle_error NUMBER;
1151 l_msg_data VARCHAR2(2000);
1152 l_sysdate DATE;
1153
1154 BEGIN
1155 IF (x_owner = 'SEED') THEN
1156 l_user_id := 1;
1157 END IF;
1158 select sysdate into l_sysdate from dual;
1159
1160 OPEN Cur_rowid;
1161 FETCH Cur_rowid INTO l_row_id;
1162 IF Cur_rowid%FOUND THEN
1163 UPDATE GR_RISK_PHRASES_TL SET
1164 RISK_DESCRIPTION = X_RISK_DESCRIPTION,
1165 SOURCE_LANG = USERENV('LANG'),
1166 LAST_UPDATE_DATE = l_sysdate,
1167 LAST_UPDATED_BY = l_user_id,
1168 LAST_UPDATE_LOGIN = 0
1169 WHERE (RISK_PHRASE_CODE = X_RISK_PHRASE_CODE)
1170 AND (USERENV('LANG') IN (LANGUAGE, SOURCE_LANG));
1171 ELSE
1172 insert into GR_RISK_PHRASES_TL (
1173 RISK_PHRASE_CODE,
1174 RISK_DESCRIPTION,
1175 CREATED_BY,
1176 CREATION_DATE,
1177 LAST_UPDATED_BY,
1178 LAST_UPDATE_DATE,
1179 LAST_UPDATE_LOGIN,
1180 LANGUAGE,
1181 SOURCE_LANG
1182 ) select
1183 X_RISK_PHRASE_CODE,
1184 X_RISK_DESCRIPTION,
1185 l_user_id,
1186 l_sysdate,
1187 l_user_id,
1188 l_sysdate,
1189 0,
1190 L.LANGUAGE_CODE,
1191 userenv('LANG')
1192 from FND_LANGUAGES L
1193 where L.INSTALLED_FLAG in ('I', 'B')
1194 and not exists
1195 (select NULL
1196 from GR_RISK_PHRASES_TL T
1197 where T.RISK_PHRASE_CODE = X_RISK_PHRASE_CODE
1198 and T.LANGUAGE = L.LANGUAGE_CODE);
1199 END IF;
1200 CLOSE Cur_rowid;
1201 END LOAD_ROW;
1202
1203 /* 21-Jan-2002 Melanie Grosser BUG 2190024 - Added procedure NEW_LANGUAGE
1204 to be called from GRNLINS.sql. Generated
1205 from tltblgen.
1206 */
1207 procedure NEW_LANGUAGE
1208 is
1209 begin
1210 delete from GR_RISK_PHRASES_TL T
1211 where not exists
1212 (select NULL
1213 from GR_RISK_PHRASES_B B
1214 where B.RISK_PHRASE_CODE = T.RISK_PHRASE_CODE
1215 );
1216
1217 update GR_RISK_PHRASES_TL T set (
1218 RISK_DESCRIPTION
1219 ) = (select
1220 B.RISK_DESCRIPTION
1221 from GR_RISK_PHRASES_TL B
1222 where B.RISK_PHRASE_CODE = T.RISK_PHRASE_CODE
1223 and B.LANGUAGE = T.SOURCE_LANG)
1224 where (
1225 T.RISK_PHRASE_CODE,
1226 T.LANGUAGE
1227 ) in (select
1228 SUBT.RISK_PHRASE_CODE,
1229 SUBT.LANGUAGE
1230 from GR_RISK_PHRASES_TL SUBB, GR_RISK_PHRASES_TL SUBT
1231 where SUBB.RISK_PHRASE_CODE = SUBT.RISK_PHRASE_CODE
1232 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1233 and (SUBB.RISK_DESCRIPTION <> SUBT.RISK_DESCRIPTION
1234 ));
1235
1236 insert into GR_RISK_PHRASES_TL (
1237 RISK_PHRASE_CODE,
1238 RISK_DESCRIPTION,
1239 CREATED_BY,
1240 CREATION_DATE,
1241 LAST_UPDATED_BY,
1242 LAST_UPDATE_DATE,
1243 LAST_UPDATE_LOGIN,
1244 LANGUAGE,
1245 SOURCE_LANG
1246 ) select
1247 B.RISK_PHRASE_CODE,
1248 B.RISK_DESCRIPTION,
1249 B.CREATED_BY,
1250 B.CREATION_DATE,
1251 B.LAST_UPDATED_BY,
1252 B.LAST_UPDATE_DATE,
1253 B.LAST_UPDATE_LOGIN,
1254 L.LANGUAGE_CODE,
1255 B.SOURCE_LANG
1256 from GR_RISK_PHRASES_TL B, FND_LANGUAGES L
1257 where L.INSTALLED_FLAG in ('I', 'B')
1258 and B.LANGUAGE = userenv('LANG')
1259 and not exists
1260 (select NULL
1261 from GR_RISK_PHRASES_TL T
1262 where T.RISK_PHRASE_CODE = B.RISK_PHRASE_CODE
1263 and T.LANGUAGE = L.LANGUAGE_CODE);
1264
1265 end NEW_LANGUAGE;
1266
1267
1268
1269 END GR_RISK_PHRASES_TL_PKG;