[Home] [Help]
PACKAGE BODY: APPS.GR_EUROHAZARDS_TL_PKG
Source
1 PACKAGE BODY GR_EUROHAZARDS_TL_PKG AS
2 /*$Header: GRHIEHTB.pls 120.1 2006/06/16 21:38:53 pbamb noship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_hazard_classification_code IN VARCHAR2,
7 p_language IN VARCHAR2,
8 p_hazard_description IN VARCHAR2,
9 p_source_lang 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
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_hazard_classification_code,
53 p_language,
54 p_hazard_description,
55 p_source_lang,
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_hazard_classification_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_eurohazards_tl
77 (hazard_classification_code,
78 language,
79 hazard_description,
80 source_lang,
81 created_by,
82 creation_date,
83 last_updated_by,
84 last_update_date,
85 last_update_login)
86 VALUES
87 (p_hazard_classification_code,
88 p_language,
89 p_hazard_description,
90 p_source_lang,
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_hazard_classification_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_hazard_classification_code || ' ' || p_language;
138 x_return_status := 'E';
139 x_oracle_error := APP_EXCEPTION.Get_Code;
140 FND_MESSAGE.SET_NAME('GR',
141 'GR_RECORD_EXISTS');
142 FND_MESSAGE.SET_TOKEN('CODE',
143 l_msg_token,
144 FALSE);
145 IF FND_API.To_Boolean(p_called_by_form) THEN
146 APP_EXCEPTION.Raise_Exception;
147 ELSE
148 x_msg_data := FND_MESSAGE.Get;
149 END IF;
150
151 WHEN Row_Missing_Error THEN
152 ROLLBACK TO SAVEPOINT Insert_Row;
153 l_msg_token := p_hazard_classification_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_hazard_classification_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_hazard_classification_code IN VARCHAR2,
191 p_language IN VARCHAR2,
192 p_hazard_description IN VARCHAR2,
193 p_source_lang 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(30);
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 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_hazard_classification_code || ' ' || p_language;
227
228 /* Now call the check foreign key procedure */
229
230 Check_Foreign_Keys
231 (p_hazard_classification_code,
232 p_language,
233 p_hazard_description,
234 p_source_lang,
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_eurohazards_tl
243 SET hazard_classification_code = p_hazard_classification_code,
244 language = p_language,
245 hazard_description = p_hazard_description,
246 source_lang = p_source_lang,
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_hazard_classification_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 eht.hazard_description,
350 eht.created_by,
351 eht.creation_date,
352 eht.last_updated_by,
353 eht.last_update_date,
354 eht.last_update_login
355 FROM gr_eurohazards_tl eht
356 WHERE eht.hazard_classification_code = p_hazard_classification_code
357 AND eht.language = l_language;
358 HazardDesc 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
368 BEGIN
369
370 /* Initialization Routine */
371
372 SAVEPOINT Add_Language;
373 x_return_status := 'S';
374 x_oracle_error := 0;
375 x_msg_data := NULL;
376 l_msg_token := p_hazard_classification_code || ' ' || p_language;
377
378 /* Remove translations with no base row */
379
380 delete from GR_EUROHAZARDS_TL T
381 where not exists
382 (select NULL
383 from GR_EUROHAZARDS_B B
384 where B.HAZARD_CLASSIFICATION_CODE = T.HAZARD_CLASSIFICATION_CODE
385 );
386
387 /* Redefault translations from the source language */
388
389
390 update gr_eurohazards_tl t set (
391 hazard_description ) =
392 ( select
393 B.HAZARD_DESCRIPTION
394 from GR_EUROHAZARDS_TL B
395 where B.HAZARD_CLASSIFICATION_CODE = T.HAZARD_CLASSIFICATION_CODE
396 and B.LANGUAGE = T.SOURCE_LANG)
397 where (
398 T.HAZARD_CLASSIFICATION_CODE,
399 T.LANGUAGE
400 ) in (select
401 SUBT.HAZARD_CLASSIFICATION_CODE,
402 SUBT.LANGUAGE
403 from GR_EUROHAZARDS_TL SUBB, GR_EUROHAZARDS_TL SUBT
404 where SUBB.HAZARD_CLASSIFICATION_CODE = SUBT.HAZARD_CLASSIFICATION_CODE
405 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
406 and (SUBB.HAZARD_DESCRIPTION <> SUBT.HAZARD_DESCRIPTION
407 or (SUBB.HAZARD_DESCRIPTION is null and SUBT.HAZARD_DESCRIPTION is not null)
408 or (SUBB.HAZARD_DESCRIPTION is not null and SUBT.HAZARD_DESCRIPTION is null)
409 ));
410
411 /* Open the language cursor and get the description entered from the
412 ** user environment variable.
413 */
414 l_language := p_language;
415 OPEN c_get_descs;
416 FETCH c_get_descs INTO HazardDesc;
417 IF c_get_descs%NOTFOUND THEN
418 CLOSE c_get_descs;
419 RAISE Language_Missing_Error;
420 ELSE
421 l_base_desc := HazardDesc.hazard_description;
422 l_created_by := HazardDesc.created_by;
423 l_creation_date := HazardDesc.creation_date;
424 l_last_updated_by := HazardDesc.last_updated_by;
425 l_last_update_date := HazardDesc.last_update_date;
426 l_last_update_login := HazardDesc.last_update_login;
427 CLOSE c_get_descs;
428 END IF;
429
430 /* Read fnd_languages for the installed and base languages.
431 ** For those that are found, read the types tl table.
432 ** If there isn't a record in the table for that language then
433 ** insert it and go on to the next.
434 */
435 OPEN c_get_installed_languages;
436 FETCH c_get_installed_languages INTO InstLang;
437 IF c_get_installed_languages%FOUND THEN
438 WHILE c_get_installed_languages%FOUND LOOP
439 IF InstLang.language_code <> p_language THEN
443 IF c_get_descs%NOTFOUND THEN
440 l_language := InstLang.language_code;
441 OPEN c_get_descs;
442 FETCH c_get_descs INTO HazardDesc;
444 CLOSE c_get_descs;
445 INSERT INTO gr_eurohazards_tl
446 (hazard_classification_code,
447 language,
448 hazard_description,
449 source_lang,
450 created_by,
451 creation_date,
452 last_updated_by,
453 last_update_date,
454 last_update_login)
455 VALUES
456 (p_hazard_classification_code,
457 l_language,
458 l_base_desc,
459 p_language,
460 l_created_by,
461 l_creation_date,
462 l_last_updated_by,
463 l_last_update_date,
464 l_last_update_login);
465 ELSE
466 CLOSE c_get_descs;
467 END IF;
468 END IF;
469 FETCH c_get_installed_languages INTO InstLang;
470 END LOOP;
471 END IF;
472 CLOSE c_get_installed_languages;
473
474 IF FND_API.To_Boolean(p_commit) THEN
475 COMMIT WORK;
476 END IF;
477
478 EXCEPTION
479
480 WHEN Language_Missing_Error THEN
481 ROLLBACK TO SAVEPOINT Add_Language;
482 x_return_status := 'E';
483 x_oracle_error := APP_EXCEPTION.Get_Code;
484 FND_MESSAGE.SET_NAME('GR',
485 'GR_RECORD_NOT_FOUND');
486 FND_MESSAGE.SET_TOKEN('CODE',
487 l_msg_token,
488 FALSE);
489 IF FND_API.To_Boolean(p_called_by_form) THEN
490 APP_EXCEPTION.Raise_Exception;
491 ELSE
492 x_msg_data := FND_MESSAGE.Get;
493 END IF;
494
495 WHEN OTHERS THEN
496 ROLLBACK TO SAVEPOINT Add_Language;
497 x_return_status := 'U';
498 x_oracle_error := SQLCODE;
499 l_msg_data := SUBSTR(SQLERRM, 1, 200);
500 FND_MESSAGE.SET_NAME('GR',
501 'GR_UNEXPECTED_ERROR');
502 FND_MESSAGE.SET_TOKEN('TEXT',
503 l_msg_token,
504 FALSE);
505 IF FND_API.To_Boolean(p_called_by_form) THEN
506 APP_EXCEPTION.Raise_Exception;
507 ELSE
508 x_msg_data := FND_MESSAGE.Get;
509 END IF;
510
511 END Add_Language;
512
513 PROCEDURE Lock_Row
514 (p_commit IN VARCHAR2,
515 p_called_by_form IN VARCHAR2,
516 p_rowid IN VARCHAR2,
517 p_hazard_classification_code IN VARCHAR2,
518 p_language IN VARCHAR2,
519 p_hazard_description IN VARCHAR2,
520 p_source_lang IN VARCHAR2,
521 p_created_by IN NUMBER,
522 p_creation_date IN DATE,
523 p_last_updated_by IN NUMBER,
524 p_last_update_date IN DATE,
525 p_last_update_login IN NUMBER,
526 x_return_status OUT NOCOPY VARCHAR2,
527 x_oracle_error OUT NOCOPY NUMBER,
528 x_msg_data OUT NOCOPY VARCHAR2)
529 IS
530
531 /* Alpha Variables */
532
533 L_RETURN_STATUS VARCHAR2(1) := 'S';
534 L_MSG_DATA VARCHAR2(2000);
535 L_MSG_TOKEN VARCHAR2(60);
536
537 /* Number Variables */
538
539 L_ORACLE_ERROR NUMBER;
540
541 /* Exceptions */
542
543 NO_DATA_FOUND_ERROR EXCEPTION;
544 RECORD_CHANGED_ERROR EXCEPTION;
545
546 /* Define the cursors */
547
548 CURSOR c_lock_hazards_tl
549 IS
550 SELECT last_update_date
551 FROM gr_eurohazards_tl
552 WHERE rowid = p_rowid
553 FOR UPDATE NOWAIT;
554 LockHazardRcd c_lock_hazards_tl%ROWTYPE;
555 BEGIN
556
557 /* Initialization Routine */
558
559 SAVEPOINT Lock_Row;
560 x_return_status := 'S';
561 x_oracle_error := 0;
562 x_msg_data := NULL;
563 l_msg_token := p_hazard_classification_code || ' ' || p_language;
564
565 /* Now lock the record */
566
567 OPEN c_lock_hazards_tl;
568 FETCH c_lock_hazards_tl INTO LockHazardRcd;
569 IF c_lock_hazards_tl%NOTFOUND THEN
570 CLOSE c_lock_hazards_tl;
571 RAISE No_Data_Found_Error;
572 END IF;
573 CLOSE c_lock_hazards_tl;
574
575 IF LockHazardRcd.last_update_date <> p_last_update_date THEN
576 RAISE RECORD_CHANGED_ERROR;
577 END IF;
578
579 IF FND_API.To_Boolean(p_commit) THEN
580 COMMIT WORK;
581 END IF;
582
583 EXCEPTION
584
585 WHEN No_Data_Found_Error THEN
586 ROLLBACK TO SAVEPOINT Lock_Row;
587 x_return_status := 'E';
588 FND_MESSAGE.SET_NAME('GR',
589 'GR_RECORD_NOT_FOUND');
590 FND_MESSAGE.SET_TOKEN('CODE',
591 l_msg_token,
592 FALSE);
593 IF FND_API.To_Boolean(p_called_by_form) THEN
594 APP_EXCEPTION.Raise_Exception;
595 ELSE
596 x_msg_data := FND_MESSAGE.Get;
597 END IF;
598
599 WHEN RECORD_CHANGED_ERROR THEN
600 ROLLBACK TO SAVEPOINT Lock_Row;
601 X_return_status := 'E';
602 FND_MESSAGE.SET_NAME('FND',
603 'FORM_RECORD_CHANGED');
607 x_msg_data := FND_MESSAGE.Get;
604 IF FND_API.To_Boolean(p_called_by_form) THEN
605 APP_EXCEPTION.Raise_Exception;
606 ELSE
608 END IF;
609 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
610 ROLLBACK TO SAVEPOINT Lock_Row;
611 x_return_status := 'L';
612 x_oracle_error := APP_EXCEPTION.Get_Code;
613 IF NOT (FND_API.To_Boolean(p_called_by_form)) THEN
614 FND_MESSAGE.SET_NAME('GR',
615 'GR_ROW_IS_LOCKED');
616 x_msg_data := FND_MESSAGE.Get;
617 END IF;
618
619
620 WHEN OTHERS THEN
621 ROLLBACK TO SAVEPOINT Lock_Row;
622 x_return_status := 'U';
623 x_oracle_error := SQLCODE;
624 l_msg_data := SUBSTR(SQLERRM, 1, 200);
625 FND_MESSAGE.SET_NAME('GR',
626 'GR_UNEXPECTED_ERROR');
627 FND_MESSAGE.SET_TOKEN('TEXT',
628 l_msg_token,
629 FALSE);
630 IF FND_API.To_Boolean(p_called_by_form) THEN
631 APP_EXCEPTION.Raise_Exception;
632 ELSE
633 x_msg_data := FND_MESSAGE.Get;
634 END IF;
635
636 END Lock_Row;
637
638 PROCEDURE Delete_Row
639 (p_commit IN VARCHAR2,
640 p_called_by_form IN VARCHAR2,
641 p_rowid IN VARCHAR2,
642 p_hazard_classification_code IN VARCHAR2,
643 p_language IN VARCHAR2,
644 p_hazard_description IN VARCHAR2,
645 p_source_lang IN VARCHAR2,
646 p_created_by IN NUMBER,
647 p_creation_date IN DATE,
648 p_last_updated_by IN NUMBER,
649 p_last_update_date IN DATE,
650 p_last_update_login IN NUMBER,
651 x_return_status OUT NOCOPY VARCHAR2,
652 x_oracle_error OUT NOCOPY NUMBER,
653 x_msg_data OUT NOCOPY VARCHAR2)
654 IS
655
656 /* Alpha Variables */
657
658 L_RETURN_STATUS VARCHAR2(1) := 'S';
659 L_CALLED_BY_FORM VARCHAR2(1);
660 L_MSG_DATA VARCHAR2(2000);
661 L_MSG_TOKEN VARCHAR2(30);
662
663 /* Number Variables */
664
665 L_ORACLE_ERROR NUMBER;
666
667 /* Exceptions */
668
669 CHECK_INTEGRITY_ERROR EXCEPTION;
670 ROW_MISSING_ERROR EXCEPTION;
671 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
672
673 /* Define the cursors */
674
675
676 BEGIN
677
678 /* Initialization Routine */
679
680 SAVEPOINT Delete_Row;
681 x_return_status := 'S';
682 l_called_by_form := 'F';
683 x_oracle_error := 0;
684 x_msg_data := NULL;
685 l_msg_token := p_hazard_classification_code || ' ' || p_language;
686
687 /* Now call the check integrity procedure */
688
689 Check_Integrity
690 (l_called_by_form,
691 p_hazard_classification_code,
692 p_language,
693 p_hazard_description,
694 p_source_lang,
695 l_return_status,
696 l_oracle_error,
697 l_msg_data);
698
699 IF l_return_status <> 'S' THEN
700 RAISE Check_Integrity_Error;
701 END IF;
702
703 DELETE FROM gr_eurohazards_tl
704 WHERE rowid = p_rowid;
705
706 /* Check the commit flag and if set, then commit the work. */
707
708 IF FND_API.TO_Boolean(p_commit) THEN
709 COMMIT WORK;
710 END IF;
711
712 EXCEPTION
713
714 WHEN Check_Integrity_Error THEN
715 ROLLBACK TO SAVEPOINT Delete_Row;
716 x_return_status := l_return_status;
717 x_oracle_error := l_oracle_error;
718 IF FND_API.To_Boolean(p_called_by_form) THEN
719 APP_EXCEPTION.Raise_Exception;
720 ELSE
721 x_msg_data := FND_MESSAGE.Get;
722 END IF;
723
724 WHEN Row_Missing_Error THEN
725 ROLLBACK TO SAVEPOINT Delete_Row;
726 x_return_status := 'E';
727 x_oracle_error := APP_EXCEPTION.Get_Code;
728 FND_MESSAGE.SET_NAME('GR',
729 'GR_RECORD_NOT_FOUND');
730 FND_MESSAGE.SET_TOKEN('CODE',
731 l_msg_token,
732 FALSE);
733 IF FND_API.To_Boolean(p_called_by_form) THEN
734 APP_EXCEPTION.Raise_Exception;
735 ELSE
736 x_msg_data := FND_MESSAGE.Get;
737 END IF;
738
739 WHEN OTHERS THEN
740 ROLLBACK TO SAVEPOINT Delete_Row;
741 x_return_status := 'U';
742 x_oracle_error := SQLCODE;
743 l_msg_data := SUBSTR(SQLERRM, 1, 200);
744 FND_MESSAGE.SET_NAME('GR',
745 'GR_UNEXPECTED_ERROR');
746 FND_MESSAGE.SET_TOKEN('TEXT',
747 l_msg_token,
748 FALSE);
749 IF FND_API.To_Boolean(p_called_by_form) THEN
750 APP_EXCEPTION.Raise_Exception;
751 ELSE
752 x_msg_data := FND_MESSAGE.Get;
753 END IF;
754
755 END Delete_Row;
756
757 PROCEDURE Delete_Rows
758 (p_commit IN VARCHAR2,
759 p_called_by_form IN VARCHAR2,
760 p_hazard_classification_code IN VARCHAR2,
761 x_return_status OUT NOCOPY VARCHAR2,
762 x_oracle_error OUT NOCOPY NUMBER,
766 /* Alpha Variables */
763 x_msg_data OUT NOCOPY VARCHAR2)
764 IS
765
767
768 L_RETURN_STATUS VARCHAR2(1) := 'S';
769 L_MSG_DATA VARCHAR2(2000);
770 L_MSG_TOKEN VARCHAR2(30);
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_hazard_classification_code;
787
788 DELETE FROM gr_eurohazards_tl
789 WHERE hazard_classification_code = p_hazard_classification_code;
790
791 IF FND_API.To_Boolean(p_commit) THEN
792 COMMIT WORK;
793 END IF;
794
795 EXCEPTION
796
797 WHEN OTHERS THEN
798 ROLLBACK TO SAVEPOINT Delete_Rows;
799 x_return_status := 'U';
800 x_oracle_error := SQLCODE;
801 l_msg_data := SUBSTR(SQLERRM, 1, 200);
802 FND_MESSAGE.SET_NAME('GR',
803 'GR_UNEXPECTED_ERROR');
804 FND_MESSAGE.SET_TOKEN('TEXT',
805 l_msg_token,
806 FALSE);
807 IF FND_API.To_Boolean(p_called_by_form) THEN
808 APP_EXCEPTION.Raise_Exception;
809 ELSE
810 x_msg_data := FND_MESSAGE.Get;
811 END IF;
812
813 END Delete_Rows;
814
815 PROCEDURE Check_Foreign_Keys
816 (p_hazard_classification_code IN VARCHAR2,
817 p_language IN VARCHAR2,
818 p_hazard_description IN VARCHAR2,
819 p_source_lang IN VARCHAR2,
820 x_return_status OUT NOCOPY VARCHAR2,
821 x_oracle_error OUT NOCOPY NUMBER,
822 x_msg_data OUT NOCOPY VARCHAR2)
823 IS
824
825 /* Alpha Variables */
826
827 L_RETURN_STATUS VARCHAR2(1) := 'S';
828 L_MSG_DATA VARCHAR2(2000);
829 L_MSG_TOKEN VARCHAR2(30);
830 L_LANGUAGE_CODE VARCHAR2(4);
831
832 /* Number Variables */
833
834 L_ORACLE_ERROR NUMBER;
835
836 /* Define the cursors */
837
838 CURSOR c_get_language
839 IS
840 SELECT lng.language_code
841 FROM fnd_languages lng
842 WHERE lng.language_code = l_language_code;
843 LangRecord c_get_language%ROWTYPE;
844
845 BEGIN
846
847 /* Initialization Routine */
848
849 SAVEPOINT Check_Foreign_Keys;
850 x_return_status := 'S';
851 x_oracle_error := 0;
852 x_msg_data := NULL;
853 l_msg_token := p_hazard_classification_code || ' ' || p_language;
854
855 /* Check the language codes */
856
857 l_language_code := p_language;
858 OPEN c_get_language;
859 FETCH c_get_language INTO LangRecord;
860 IF c_get_language%NOTFOUND THEN
861 l_msg_token := l_language_code;
862 x_return_status := 'E';
863 x_oracle_error := APP_EXCEPTION.Get_Code;
864 FND_MESSAGE.SET_NAME('GR',
865 'GR_RECORD_NOT_FOUND');
866 FND_MESSAGE.SET_TOKEN('CODE',
867 l_msg_token,
868 FALSE);
869 l_msg_data := FND_MESSAGE.Get;
870 END IF;
871 CLOSE c_get_language;
872
873 l_language_code := p_source_lang;
874 OPEN c_get_language;
875 FETCH c_get_language INTO LangRecord;
876 IF c_get_language%NOTFOUND THEN
877 l_msg_token := l_language_code;
878 x_return_status := 'E';
879 x_oracle_error := APP_EXCEPTION.Get_Code;
880 FND_MESSAGE.SET_NAME('GR',
881 'GR_RECORD_NOT_FOUND');
882 FND_MESSAGE.SET_TOKEN('CODE',
883 l_msg_token,
884 FALSE);
885 l_msg_data := FND_MESSAGE.Get;
886 END IF;
887 CLOSE c_get_language;
888
889 IF x_return_status <> 'S' THEN
890 x_msg_data := l_msg_data;
891 END IF;
892
893 EXCEPTION
894
895 WHEN OTHERS THEN
896 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
897 x_return_status := 'U';
898 x_oracle_error := SQLCODE;
899 l_msg_data := SUBSTR(SQLERRM, 1, 200);
900 FND_MESSAGE.SET_NAME('GR',
901 'GR_UNEXPECTED_ERROR');
902 FND_MESSAGE.SET_TOKEN('TEXT',
903 l_msg_token,
904 FALSE);
905 x_msg_data := FND_MESSAGE.Get;
906
907 END Check_Foreign_Keys;
908
909 PROCEDURE Check_Integrity
910 (p_called_by_form IN VARCHAR2,
911 p_hazard_classification_code IN VARCHAR2,
912 p_language IN VARCHAR2,
913 p_hazard_description IN VARCHAR2,
914 p_source_lang IN VARCHAR2,
915 x_return_status OUT NOCOPY VARCHAR2,
916 x_oracle_error OUT NOCOPY NUMBER,
917 x_msg_data OUT NOCOPY VARCHAR2)
918 IS
919
920 /* Alpha Variables */
921
922 L_RETURN_STATUS VARCHAR2(1) := 'S';
923 L_MSG_DATA VARCHAR2(2000);
924 L_CODE_BLOCK VARCHAR2(30);
925
926 /* Number Variables */
927
928 L_ORACLE_ERROR NUMBER;
929 L_RECORD_COUNT NUMBER;
930
934
931 /* Exceptions */
932
933 INSTALLED_LANGUAGE_ERROR EXCEPTION;
935 /* Define the Cursors */
936
937 CURSOR c_get_language_code
938 IS
939 SELECT lng.installed_flag
940 FROM fnd_languages lng
941 WHERE lng.language_code = p_language
942 AND lng.installed_flag IN ('B','I');
943 LangRecord c_get_language_code%ROWTYPE;
944
945 BEGIN
946
947 /* Initialization Routine */
948
949 SAVEPOINT Check_Integrity;
950 x_return_status := 'S';
951 x_oracle_error := 0;
952 x_msg_data := NULL;
953
954 /* Check the language isn't base or installed */
955
956 OPEN c_get_language_code;
957 FETCH c_get_language_code INTO LangRecord;
958 IF c_get_language_code%FOUND THEN
959 CLOSE c_get_language_code;
960 RAISE Installed_Language_Error;
961 END IF;
962 CLOSE c_get_language_code;
963
964 EXCEPTION
965
966 WHEN Installed_Language_Error THEN
967 ROLLBACK TO SAVEPOINT Check_Integrity;
968 x_return_status := 'E';
969 FND_MESSAGE.SET_NAME('GR',
970 'GR_INSTALLED_LANG');
971 FND_MESSAGE.SET_TOKEN('CODE',
972 p_language,
973 FALSE);
974 IF FND_API.To_Boolean(p_called_by_form) THEN
975 APP_EXCEPTION.Raise_Exception;
976 ELSE
977 x_msg_data := FND_MESSAGE.Get;
978 END IF;
979
980 WHEN OTHERS THEN
981 ROLLBACK TO SAVEPOINT Check_Integrity;
982 x_return_status := 'U';
983 x_oracle_error := SQLCODE;
984 l_msg_data := SUBSTR(SQLERRM, 1, 200);
985 FND_MESSAGE.SET_NAME('GR',
986 'GR_UNEXPECTED_ERROR');
987 FND_MESSAGE.SET_TOKEN('TEXT',
988 l_msg_data,
989 FALSE);
990 IF FND_API.To_Boolean(p_called_by_form) THEN
991 APP_EXCEPTION.Raise_Exception;
992 ELSE
993 x_msg_data := FND_MESSAGE.Get;
994 END IF;
995
996 END Check_Integrity;
997
998 PROCEDURE Check_Primary_Key
999 /* p_hazard_classification_code is the hazard code to check.
1000 ** p_language is the language code part of the key
1001 ** p_called_by_form is 'T' if called by a form or 'F' if not.
1002 ** x_rowid is the row id of the record if found.
1003 ** x_key_exists is 'T' is the record is found, 'F' if not.
1004 */
1005 (p_hazard_classification_code IN VARCHAR2,
1006 p_language IN VARCHAR2,
1007 p_called_by_form IN VARCHAR2,
1008 x_rowid OUT NOCOPY VARCHAR2,
1009 x_key_exists OUT NOCOPY VARCHAR2)
1010 IS
1011 /* Alphanumeric variables */
1012
1013 L_MSG_DATA VARCHAR2(80);
1014
1015 /* Declare any variables and the cursor */
1016
1017 CURSOR c_get_hazards_tl_rowid
1018 IS
1019 SELECT eht.rowid
1020 FROM gr_eurohazards_tl eht
1021 WHERE eht.hazard_classification_code = p_hazard_classification_code
1022 AND eht.language = p_language;
1023 HazardTLRecord c_get_hazards_tl_rowid%ROWTYPE;
1024
1025 BEGIN
1026
1027 l_msg_data := p_hazard_classification_code || ' ' || p_language;
1028
1029 x_key_exists := 'F';
1030 OPEN c_get_hazards_tl_rowid;
1031 FETCH c_get_hazards_tl_rowid INTO HazardTLRecord;
1032 IF c_get_hazards_tl_rowid%FOUND THEN
1033 x_key_exists := 'T';
1034 x_rowid := HazardTLRecord.rowid;
1035 ELSE
1036 x_key_exists := 'F';
1037 END IF;
1038 CLOSE c_get_hazards_tl_rowid;
1039
1040 EXCEPTION
1041
1042 WHEN Others THEN
1043 l_msg_data := SUBSTR(SQLERRM, 1, 200);
1044 FND_MESSAGE.SET_NAME('GR',
1045 'GR_UNEXPECTED_ERROR');
1046 FND_MESSAGE.SET_TOKEN('TEXT',
1047 l_msg_data,
1048 FALSE);
1049 IF FND_API.To_Boolean(p_called_by_form) THEN
1050 APP_EXCEPTION.Raise_Exception;
1051 END IF;
1052
1053 END Check_Primary_Key;
1054
1055 PROCEDURE translate_row (
1056 X_LANGUAGE IN VARCHAR2
1057 ,X_HAZARD_DESCRIPTION IN VARCHAR2
1058 ,X_SOURCE_LANG IN VARCHAR2
1059 ,X_HAZARD_CLASSIFICATION_CODE IN VARCHAR2
1060 ) IS
1061 BEGIN
1062 UPDATE GR_EUROHAZARDS_TL SET
1063 HAZARD_DESCRIPTION = X_HAZARD_DESCRIPTION,
1064 SOURCE_LANG = USERENV('LANG'),
1065 LAST_UPDATE_DATE = sysdate,
1066 LAST_UPDATED_BY = 0,
1067 LAST_UPDATE_LOGIN = 0
1068 WHERE (HAZARD_CLASSIFICATION_CODE = X_HAZARD_CLASSIFICATION_CODE)
1069 AND (USERENV('LANG') IN (LANGUAGE, SOURCE_LANG));
1070 END TRANSLATE_ROW;
1071
1072 /*Bug# 5237433 */
1073 PROCEDURE translate_row (
1074 X_HAZARD_CLASSIFICATION_CODE IN VARCHAR2
1075 ,X_HAZARD_DESCRIPTION IN VARCHAR2
1076 ,X_OWNER IN VARCHAR2
1077 ) IS
1078 BEGIN
1079 UPDATE GR_EUROHAZARDS_TL SET
1080 HAZARD_DESCRIPTION = X_HAZARD_DESCRIPTION,
1081 SOURCE_LANG = USERENV('LANG'),
1082 LAST_UPDATE_DATE = sysdate,
1083 LAST_UPDATED_BY = Decode(X_OWNER, 'SEED', 1, 0),
1084 LAST_UPDATE_LOGIN = 0
1085 WHERE (HAZARD_CLASSIFICATION_CODE = X_HAZARD_CLASSIFICATION_CODE)
1086 AND (USERENV('LANG') IN (LANGUAGE, SOURCE_LANG));
1087 END TRANSLATE_ROW;
1091 X_LANGUAGE IN VARCHAR2
1088
1089
1090 PROCEDURE load_row (
1092 ,X_HAZARD_DESCRIPTION IN VARCHAR2
1093 ,X_SOURCE_LANG IN VARCHAR2
1094 ,X_HAZARD_CLASSIFICATION_CODE IN VARCHAR2
1095 ) IS
1096 CURSOR Cur_rowid IS
1097 SELECT rowid
1098 FROM GR_EUROHAZARDS_TL
1099 WHERE (HAZARD_CLASSIFICATION_CODE = X_HAZARD_CLASSIFICATION_CODE)
1100
1101 AND (LANGUAGE = X_LANGUAGE);
1102 l_user_id NUMBER DEFAULT 1;
1103 l_row_id VARCHAR2(64);
1104 l_return_status VARCHAR2(1);
1105 l_oracle_error NUMBER;
1106 l_msg_data VARCHAR2(2000);
1107 BEGIN
1108 OPEN Cur_rowid;
1109 FETCH Cur_rowid INTO l_row_id;
1110 IF Cur_rowid%FOUND THEN
1111 GR_EUROHAZARDS_TL_PKG.UPDATE_ROW(
1112 P_COMMIT => 'T'
1113 ,P_CALLED_BY_FORM => 'F'
1114 ,P_ROWID => l_row_id
1115 ,P_LANGUAGE => X_LANGUAGE
1116 ,P_HAZARD_DESCRIPTION => X_HAZARD_DESCRIPTION
1117 ,P_SOURCE_LANG => X_SOURCE_LANG
1118 ,P_HAZARD_CLASSIFICATION_CODE => X_HAZARD_CLASSIFICATION_CODE
1119 ,P_CREATED_BY => l_user_id
1120 ,P_CREATION_DATE => sysdate
1121 ,P_LAST_UPDATED_BY => l_user_id
1122 ,P_LAST_UPDATE_DATE => sysdate
1123 ,P_LAST_UPDATE_LOGIN => 0
1124 ,X_RETURN_STATUS => l_return_status
1125 ,X_ORACLE_ERROR => l_oracle_error
1126 ,X_MSG_DATA => l_msg_data);
1127 ELSE
1128 GR_EUROHAZARDS_TL_PKG.INSERT_ROW(
1129 P_COMMIT => 'T'
1130 ,P_CALLED_BY_FORM => 'F'
1131 ,P_LANGUAGE => X_LANGUAGE
1132 ,P_HAZARD_DESCRIPTION => X_HAZARD_DESCRIPTION
1133 ,P_SOURCE_LANG => X_SOURCE_LANG
1134 ,P_HAZARD_CLASSIFICATION_CODE => X_HAZARD_CLASSIFICATION_CODE
1135 ,P_CREATED_BY => l_user_id
1136 ,P_CREATION_DATE => sysdate
1137 ,P_LAST_UPDATED_BY => l_user_id
1138 ,P_LAST_UPDATE_DATE => sysdate
1139 ,P_LAST_UPDATE_LOGIN => 0
1140 ,X_ROWID => l_row_id
1141 ,X_RETURN_STATUS => l_return_status
1142 ,X_ORACLE_ERROR => l_oracle_error
1143 ,X_MSG_DATA => l_msg_data);
1144 END IF;
1145 CLOSE Cur_rowid;
1146 END LOAD_ROW;
1147
1148 PROCEDURE load_row (
1149 X_HAZARD_CLASSIFICATION_CODE IN VARCHAR2
1150 ,X_HAZARD_DESCRIPTION IN VARCHAR2
1151 ,X_OWNER IN VARCHAR2
1152 ) IS
1153 CURSOR Cur_rowid IS
1154 SELECT rowid
1155 FROM GR_EUROHAZARDS_TL
1156 WHERE (HAZARD_CLASSIFICATION_CODE = X_HAZARD_CLASSIFICATION_CODE)
1157 AND USERENV('LANG') in (LANGUAGE,SOURCE_LANG);
1158
1159 l_user_id NUMBER DEFAULT 0;
1160 l_row_id VARCHAR2(64);
1161 l_return_status VARCHAR2(1);
1162 l_oracle_error NUMBER;
1163 l_msg_data VARCHAR2(2000);
1164 l_sysdate DATE;
1165
1166 BEGIN
1167 IF (x_owner = 'SEED') THEN
1168 l_user_id := 1;
1169 END IF;
1170 select sysdate into l_sysdate from dual;
1171 OPEN Cur_rowid;
1172 FETCH Cur_rowid INTO l_row_id;
1173 IF Cur_rowid%FOUND THEN
1174 UPDATE GR_EUROHAZARDS_TL SET
1175 HAZARD_DESCRIPTION = X_HAZARD_DESCRIPTION,
1176 LAST_UPDATE_DATE = l_sysdate,
1177 LAST_UPDATED_BY = l_user_id,
1178 LAST_UPDATE_LOGIN = 0,
1179 SOURCE_LANG = userenv('LANG')
1180 WHERE HAZARD_CLASSIFICATION_CODE = X_HAZARD_CLASSIFICATION_CODE
1181 AND userenv('LANG') in (LANGUAGE,SOURCE_LANG);
1182
1183 ELSE
1184 insert into GR_EUROHAZARDS_TL (
1185 HAZARD_CLASSIFICATION_CODE,
1186 HAZARD_DESCRIPTION,
1187 CREATED_BY,
1188 CREATION_DATE,
1189 LAST_UPDATED_BY,
1190 LAST_UPDATE_DATE,
1191 LAST_UPDATE_LOGIN,
1192 LANGUAGE,
1193 SOURCE_LANG
1194 ) select
1195 X_HAZARD_CLASSIFICATION_CODE,
1196 X_HAZARD_DESCRIPTION,
1197 l_user_id,
1198 l_sysdate,
1199 l_user_id,
1200 l_sysdate,
1201 0,
1202 L.LANGUAGE_CODE,
1203 userenv('LANG')
1204 from FND_LANGUAGES L
1205 where L.INSTALLED_FLAG in ('I', 'B')
1206 and not exists
1207 (select NULL
1208 from GR_EUROHAZARDS_TL T
1209 where T.HAZARD_CLASSIFICATION_CODE = X_HAZARD_CLASSIFICATION_CODE
1210 and T.LANGUAGE = L.LANGUAGE_CODE);
1211 END IF;
1212 CLOSE Cur_rowid;
1213 END LOAD_ROW;
1214
1215
1216 procedure NEW_LANGUAGE
1217 is
1218 begin
1219 delete from GR_EUROHAZARDS_TL T
1220 where not exists
1221 (select NULL
1222 from GR_EUROHAZARDS_B B
1223 where B.HAZARD_CLASSIFICATION_CODE = T.HAZARD_CLASSIFICATION_CODE
1224 );
1225
1226 update GR_EUROHAZARDS_TL T set (
1227 HAZARD_DESCRIPTION
1228 ) = (select
1229 B.HAZARD_DESCRIPTION
1230 from GR_EUROHAZARDS_TL B
1231 where B.HAZARD_CLASSIFICATION_CODE = T.HAZARD_CLASSIFICATION_CODE
1232 and B.LANGUAGE = T.SOURCE_LANG)
1233 where (
1234 T.HAZARD_CLASSIFICATION_CODE,
1235 T.LANGUAGE
1236 ) in (select
1237 SUBT.HAZARD_CLASSIFICATION_CODE,
1241 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1238 SUBT.LANGUAGE
1239 from GR_EUROHAZARDS_TL SUBB, GR_EUROHAZARDS_TL SUBT
1240 where SUBB.HAZARD_CLASSIFICATION_CODE = SUBT.HAZARD_CLASSIFICATION_CODE
1242 and (SUBB.HAZARD_DESCRIPTION <> SUBT.HAZARD_DESCRIPTION
1243 ));
1244
1245 insert into GR_EUROHAZARDS_TL (
1246 HAZARD_DESCRIPTION,
1247 CREATED_BY,
1248 CREATION_DATE,
1249 LAST_UPDATED_BY,
1250 LAST_UPDATE_DATE,
1251 LAST_UPDATE_LOGIN,
1252 HAZARD_CLASSIFICATION_CODE,
1253 LANGUAGE,
1254 SOURCE_LANG
1255 ) select
1256 B.HAZARD_DESCRIPTION,
1257 B.CREATED_BY,
1258 B.CREATION_DATE,
1259 B.LAST_UPDATED_BY,
1260 B.LAST_UPDATE_DATE,
1261 B.LAST_UPDATE_LOGIN,
1262 B.HAZARD_CLASSIFICATION_CODE,
1263 L.LANGUAGE_CODE,
1264 B.SOURCE_LANG
1265 from GR_EUROHAZARDS_TL B, FND_LANGUAGES L
1266 where L.INSTALLED_FLAG in ('I', 'B')
1267 and B.LANGUAGE = userenv('LANG')
1268 and not exists
1269 (select NULL
1270 from GR_EUROHAZARDS_TL T
1271 where T.HAZARD_CLASSIFICATION_CODE = B.HAZARD_CLASSIFICATION_CODE
1272 and T.LANGUAGE = L.LANGUAGE_CODE);
1273
1274 end NEW_LANGUAGE;
1275
1276 END GR_EUROHAZARDS_TL_PKG;