[Home] [Help]
PACKAGE BODY: APPS.GR_SAFETY_CATEGORIES_TL_PKG
Source
1 PACKAGE BODY GR_SAFETY_CATEGORIES_TL_PKG AS
2 /*$Header: GRHICATB.pls 115.9 2002/10/28 16:51:49 gkelly ship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_safety_category_code IN VARCHAR2,
7 p_safety_category_description IN VARCHAR2,
8 p_language IN VARCHAR2,
9 p_source_lang IN VARCHAR2,
10 p_last_update_login IN NUMBER,
11 p_last_update_date IN DATE,
12 p_last_updated_by IN NUMBER,
13 p_creation_date IN DATE,
14 p_created_by 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_safety_category_code,
53 p_safety_category_description,
54 p_language,
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_safety_category_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_safety_categories_tl
77 (safety_category_code,
78 safety_category_description,
79 language,
80 source_lang,
81 last_update_login,
82 last_update_date,
83 last_updated_by,
84 creation_date,
85 created_by)
86 VALUES
87 (p_safety_category_code,
88 p_safety_category_description,
89 p_language,
90 p_source_lang,
91 p_last_update_login,
92 p_last_update_date,
93 p_last_updated_by,
94 p_creation_date,
95 p_created_by);
96
97 /* Now get the row id of the inserted record */
98
99 Check_Primary_Key
100 (p_safety_category_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_safety_category_code;
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_safety_category_code;
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_safety_category_code ||' ' ||TO_CHAR(x_oracle_error);
170 x_return_status := 'U';
171 --B1052134 - changed APP_EXCEPTION.Get_Code to SQLCODE, added SQLERRM
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||sqlerrm,
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_safety_category_code IN VARCHAR2,
192 p_safety_category_description IN VARCHAR2,
193 p_language IN VARCHAR2,
194 p_source_lang IN VARCHAR2,
195 p_last_update_login IN NUMBER,
196 p_last_update_date IN DATE,
197 p_last_updated_by IN NUMBER,
198 p_creation_date IN DATE,
199 p_created_by 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(100);
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
220 BEGIN
221
222 /* Initialization Routine */
223
224 SAVEPOINT Update_Row;
225 x_return_status := 'S';
226 x_oracle_error := 0;
227 x_msg_data := NULL;
228 l_msg_token := p_safety_category_code;
229
230 /* Now call the check foreign key procedure */
231
232 Check_Foreign_Keys
233 (p_safety_category_code,
234 p_safety_category_description,
235 p_language,
236 p_source_lang,
237 l_return_status,
238 l_oracle_error,
239 l_msg_data);
240
241 IF l_return_status <> 'S' THEN
242 RAISE Foreign_Key_Error;
243 ELSE
244 UPDATE gr_safety_categories_tl
245 SET safety_category_code = p_safety_category_code,
246 safety_category_description = p_safety_category_description,
247 language = p_language,
248 source_lang = p_source_lang,
249 last_update_login = p_last_update_login,
250 last_update_date = p_last_update_date,
251 last_updated_by = p_last_updated_by,
252 creation_date = p_creation_date,
253 created_by = p_created_by
254 WHERE rowid = p_rowid;
255 IF SQL%NOTFOUND THEN
256 RAISE Row_Missing_Error;
257 END IF;
258 END IF;
259
260 /* Check the commit flag and if set, then commit the work. */
261
262 IF FND_API.To_Boolean(p_commit) THEN
263 COMMIT WORK;
264 END IF;
265
266 EXCEPTION
267
268 WHEN Foreign_Key_Error THEN
269 ROLLBACK TO SAVEPOINT Update_Row;
270 x_return_status := l_return_status;
271 x_oracle_error := l_oracle_error;
272 FND_MESSAGE.SET_NAME('GR',
273 'GR_FOREIGN_KEY_ERROR');
274 FND_MESSAGE.SET_TOKEN('TEXT',
275 l_msg_data,
276 FALSE);
277 IF FND_API.To_Boolean(p_called_by_form) THEN
278 APP_EXCEPTION.Raise_Exception;
279 ELSE
280 x_msg_data := FND_MESSAGE.Get;
281 END IF;
282
283 WHEN Row_Missing_Error THEN
284 ROLLBACK TO SAVEPOINT Update_Row;
285 x_return_status := 'E';
286 x_oracle_error := APP_EXCEPTION.Get_Code;
287 FND_MESSAGE.SET_NAME('GR',
288 'GR_NO_RECORD_INSERTED');
289 FND_MESSAGE.SET_TOKEN('CODE',
290 l_msg_token,
291 FALSE);
292 IF FND_API.To_Boolean(p_called_by_form) THEN
293 APP_EXCEPTION.Raise_Exception;
294 ELSE
295 x_msg_data := FND_MESSAGE.Get;
296 END IF;
297
298 WHEN OTHERS THEN
299 ROLLBACK TO SAVEPOINT Update_Row;
300 x_return_status := 'U';
301 --B1052134 - changed APP_EXCEPTION to SQLCODE, added SQLERRM
302 x_oracle_error := SQLCODE;
303 l_msg_data := SUBSTR(SQLERRM, 1, 200);
304 FND_MESSAGE.SET_NAME('GR',
305 'GR_UNEXPECTED_ERROR');
306 FND_MESSAGE.SET_TOKEN('TEXT',
307 l_msg_token ||sqlerrm,
308 FALSE);
309 IF FND_API.To_Boolean(p_called_by_form) THEN
310 APP_EXCEPTION.Raise_Exception;
311 ELSE
312 x_msg_data := FND_MESSAGE.Get;
313 END IF;
314
315 END Update_Row;
316
317 PROCEDURE Add_Language
318 (p_commit IN VARCHAR2,
319 p_called_by_form IN VARCHAR2,
320 p_safety_category_code IN VARCHAR2,
321 p_language IN VARCHAR2,
322 x_return_status OUT NOCOPY VARCHAR2,
323 x_oracle_error OUT NOCOPY NUMBER,
324 x_msg_data OUT NOCOPY VARCHAR2)
325 IS
326
327 /* Alpha Variables */
328
329 L_RETURN_STATUS VARCHAR2(1) := 'S';
330 L_MSG_DATA VARCHAR2(2000);
331 L_MSG_TOKEN VARCHAR2(100);
332 L_BASE_DESC GR_SAFETY_CATEGORIES_TL.safety_category_description%TYPE;
333 L_LANGUAGE VARCHAR2(4);
334 L_CREATION_DATE DATE;
335 L_LAST_UPDATE_DATE DATE;
336 /*
337 ** Number Variables
338 */
339 L_ORACLE_ERROR NUMBER;
340 L_CREATED_BY NUMBER;
341 L_LAST_UPDATED_BY NUMBER;
342 L_LAST_UPDATE_LOGIN NUMBER;
343 /*
344 ** Exceptions
345 */
346 LANGUAGE_MISSING_ERROR EXCEPTION;
347 /*
348 ** Cursors
349 */
350 CURSOR c_get_safety_category
351 IS
352 SELECT cat.safety_category_description,
353 cat.created_by,
354 cat.creation_date,
355 cat.last_updated_by,
356 cat.last_update_date,
357 cat.last_update_login
358 FROM gr_safety_categories_tl cat
359 WHERE cat.safety_category_code = p_safety_category_code
360 AND cat.language = l_language;
361 CategoryRecord c_get_safety_category%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_safety_category_code || ' ' || p_language;
380
381 /* Remove translations with no base row */
382
383 delete from GR_SAFETY_CATEGORIES_TL T
384 where not exists
385 (select NULL
386 from GR_SAFETY_CATEGORIES_B B
387 where B.SAFETY_CATEGORY_CODE = T.SAFETY_CATEGORY_CODE
388 );
389
390 /* Redefault translations from the source language */
391
392 update gr_safety_categories_tl t set (
393 safety_category_description ) =
394 ( select
395 B.SAFETY_CATEGORY_DESCRIPTION
396 from GR_SAFETY_CATEGORIES_TL B
397 where B.SAFETY_CATEGORY_CODE = T.SAFETY_CATEGORY_CODE
398 and B.LANGUAGE = T.SOURCE_LANG)
399 where (
400 T.SAFETY_CATEGORY_CODE,
401 T.LANGUAGE
402 ) in (select
403 SUBT.SAFETY_CATEGORY_CODE,
404 SUBT.LANGUAGE
405 from GR_SAFETY_CATEGORIES_TL SUBB, GR_SAFETY_CATEGORIES_TL SUBT
406 where SUBB.SAFETY_CATEGORY_CODE = SUBT.SAFETY_CATEGORY_CODE
407 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
408 and (SUBB.SAFETY_CATEGORY_DESCRIPTION <> SUBT.SAFETY_CATEGORY_DESCRIPTION
409 or (SUBB.SAFETY_CATEGORY_DESCRIPTION is null and SUBT.SAFETY_CATEGORY_DESCRIPTION is not null)
410 or (SUBB.SAFETY_CATEGORY_DESCRIPTION is not null and SUBT.SAFETY_CATEGORY_DESCRIPTION is null)
411 ));
412
413 /* Open the language cursor and get the description entered from the
414 ** user environment variable.
415 */
416 l_language := p_language;
417 OPEN c_get_safety_category;
418 FETCH c_get_safety_category INTO CategoryRecord;
419 IF c_get_safety_category%NOTFOUND THEN
420 CLOSE c_get_safety_category;
421 RAISE Language_Missing_Error;
422 ELSE
423 l_base_desc := CategoryRecord.safety_category_description;
424 l_created_by := CategoryRecord.created_by;
425 l_creation_date := CategoryRecord.creation_date;
426 l_last_updated_by := CategoryRecord.last_updated_by;
427 l_last_update_date := CategoryRecord.last_update_date;
428 l_last_update_login := CategoryRecord.last_update_login;
429 CLOSE c_get_safety_category;
430 END IF;
431
432 /* Read fnd_languages for the installed and base languages.
433 ** For those that are found, read the phrases tl table.
434 ** If there isn't a record in the table for that language then
435 ** insert it and go on to the next.
436 */
437 OPEN c_get_installed_languages;
438 FETCH c_get_installed_languages INTO InstLang;
439 IF c_get_installed_languages%FOUND THEN
440 WHILE c_get_installed_languages%FOUND LOOP
441 IF InstLang.language_code <> p_language THEN
442 l_language := InstLang.language_code;
443 OPEN c_get_safety_category;
444 FETCH c_get_safety_category INTO CategoryRecord;
445 IF c_get_safety_category%NOTFOUND THEN
446 CLOSE c_get_safety_category;
447 INSERT INTO gr_safety_categories_tl
448 (safety_category_code,
449 safety_category_description,
450 language,
451 source_lang,
452 last_update_login,
453 last_update_date,
454 last_updated_by,
455 creation_date,
456 created_by)
457 VALUES
458 (p_safety_category_code,
459 l_base_desc,
460 l_language,
461 p_language,
462 l_last_update_login,
463 l_last_update_date,
464 l_last_updated_by,
465 l_creation_date,
466 l_created_by);
467 ELSE
468 CLOSE c_get_safety_category;
469 END IF;
470 END IF;
471 FETCH c_get_installed_languages INTO InstLang;
472 END LOOP;
473 END IF;
474 CLOSE c_get_installed_languages;
475
476 IF FND_API.To_Boolean(p_commit) THEN
477 COMMIT WORK;
478 END IF;
479
480 EXCEPTION
481
482 WHEN Language_Missing_Error THEN
483 ROLLBACK TO SAVEPOINT Add_Language;
484 x_return_status := 'E';
485 x_oracle_error := APP_EXCEPTION.Get_Code;
486 FND_MESSAGE.SET_NAME('GR',
487 'GR_RECORD_NOT_FOUND');
488 FND_MESSAGE.SET_TOKEN('CODE',
489 l_msg_token,
490 FALSE);
491 IF FND_API.To_Boolean(p_called_by_form) THEN
492 APP_EXCEPTION.Raise_Exception;
493 ELSE
494 x_msg_data := FND_MESSAGE.Get;
495 END IF;
496
497 WHEN OTHERS THEN
498 ROLLBACK TO SAVEPOINT Add_Language;
499 x_return_status := 'U';
500 x_oracle_error := SQLCODE;
501 l_msg_data := SUBSTR(SQLERRM, 1, 200);
502 FND_MESSAGE.SET_NAME('GR',
503 'GR_UNEXPECTED_ERROR');
504 FND_MESSAGE.SET_TOKEN('TEXT',
505 l_msg_token||sqlerrm,
506 FALSE);
507 IF FND_API.To_Boolean(p_called_by_form) THEN
508 APP_EXCEPTION.Raise_Exception;
509 ELSE
510 x_msg_data := FND_MESSAGE.Get;
511 END IF;
512
513 END Add_Language;
514
515 PROCEDURE Lock_Row
516 (p_commit IN VARCHAR2,
517 p_called_by_form IN VARCHAR2,
518 p_rowid IN VARCHAR2,
519 p_safety_category_code IN VARCHAR2,
520 p_safety_category_description IN VARCHAR2,
521 p_language IN VARCHAR2,
522 p_source_lang IN VARCHAR2,
523 p_last_update_login IN NUMBER,
524 p_last_update_date IN DATE,
525 p_last_updated_by IN NUMBER,
526 p_creation_date IN DATE,
527 p_created_by IN NUMBER,
528 x_return_status OUT NOCOPY VARCHAR2,
529 x_oracle_error OUT NOCOPY NUMBER,
530 x_msg_data OUT NOCOPY VARCHAR2)
531 IS
532
533 /* Alpha Variables */
534
535 L_RETURN_STATUS VARCHAR2(1) := 'S';
536 L_MSG_DATA VARCHAR2(2000);
537 L_MSG_TOKEN VARCHAR2(100);
538
539 /* Number Variables */
540
541 L_ORACLE_ERROR NUMBER;
542
543 /* Exceptions */
544
545 NO_DATA_FOUND_ERROR EXCEPTION;
546 ROW_ALREADY_LOCKED_ERROR EXCEPTION;
547 PRAGMA EXCEPTION_INIT(ROW_ALREADY_LOCKED_ERROR,-54);
548
549 /* Define the cursors */
550
551 CURSOR c_lock_categories_tl
552 IS
553 SELECT *
554 FROM gr_safety_categories_tl
555 WHERE rowid = p_rowid
556 FOR UPDATE NOWAIT;
557 LockCategoryRcd c_lock_categories_tl%ROWTYPE;
558
559 BEGIN
560
561 /* Initialization Routine */
562
563 SAVEPOINT Lock_Row;
564 x_return_status := 'S';
565 x_oracle_error := 0;
566 x_msg_data := NULL;
567 l_msg_token := p_safety_category_code || ' ' || p_language;
568
569 /* Now lock the record */
570
571 OPEN c_lock_categories_tl;
572 FETCH c_lock_categories_tl INTO LockCategoryRcd;
573 IF c_lock_categories_tl%NOTFOUND THEN
574 CLOSE c_lock_categories_tl;
575 RAISE No_Data_Found_Error;
576 END IF;
577 CLOSE c_lock_categories_tl;
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 Row_Already_Locked_Error THEN
600 ROLLBACK TO SAVEPOINT Lock_Row;
601 x_return_status := 'E';
602 x_oracle_error := APP_EXCEPTION.Get_Code;
603 FND_MESSAGE.SET_NAME('GR',
604 'GR_ROW_IS_LOCKED');
605 IF FND_API.To_Boolean(p_called_by_form) THEN
606 APP_EXCEPTION.Raise_Exception;
607 ELSE
608 x_msg_data := FND_MESSAGE.Get;
609 END IF;
610
611 WHEN OTHERS THEN
612 ROLLBACK TO SAVEPOINT Lock_Row;
613 x_return_status := 'U';
614 x_oracle_error := SQLCODE;
615 l_msg_data := SUBSTR(SQLERRM, 1, 200);
616 FND_MESSAGE.SET_NAME('GR',
617 'GR_UNEXPECTED_ERROR');
618 FND_MESSAGE.SET_TOKEN('TEXT',
619 l_msg_token ||sqlerrm,
620 FALSE);
621 IF FND_API.To_Boolean(p_called_by_form) THEN
622 APP_EXCEPTION.Raise_Exception;
623 ELSE
624 x_msg_data := FND_MESSAGE.Get;
625 END IF;
626
627 END Lock_Row;
628
629 PROCEDURE Delete_Row
630 (p_commit IN VARCHAR2,
631 p_called_by_form IN VARCHAR2,
632 p_rowid IN VARCHAR2,
633 p_safety_category_code IN VARCHAR2,
634 p_safety_category_description IN VARCHAR2,
635 p_language IN VARCHAR2,
636 p_source_lang IN VARCHAR2,
637 p_last_update_login IN NUMBER,
638 p_last_update_date IN DATE,
639 p_last_updated_by IN NUMBER,
640 p_creation_date IN DATE,
641 p_created_by IN NUMBER,
642 x_return_status OUT NOCOPY VARCHAR2,
643 x_oracle_error OUT NOCOPY NUMBER,
644 x_msg_data OUT NOCOPY VARCHAR2)
645 IS
646
647 /* Alpha Variables */
648
649 L_RETURN_STATUS VARCHAR2(1) := 'S';
650 L_MSG_DATA VARCHAR2(2000);
651 L_MSG_TOKEN VARCHAR2(100);
652 L_CALLED_BY_FORM VARCHAR2(1);
653
654 /* Number Variables */
655
656 L_ORACLE_ERROR NUMBER;
657
658 /* Exceptions */
659
660 CHECK_INTEGRITY_ERROR EXCEPTION;
661 ROW_MISSING_ERROR EXCEPTION;
662 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
663
664 /* Define the cursor */
665
666 BEGIN
667
668 /* Initialization Routine */
669
670 SAVEPOINT Delete_Row;
671 x_return_status := 'S';
672 l_called_by_form := 'F';
673 x_oracle_error := 0;
674 x_msg_data := NULL;
675 l_msg_token := p_safety_category_code || ' ' || p_language;
676
677 /* Now call the check integrity procedure */
678
679 Check_Integrity
680 (l_called_by_form,
681 p_safety_category_code,
682 p_safety_category_description,
683 p_language,
684 p_source_lang,
685 l_return_status,
686 l_oracle_error,
687 l_msg_data);
688
689 IF l_return_status <> 'S' THEN
690 RAISE Check_Integrity_Error;
691 END IF;
692
693 DELETE FROM gr_safety_categories_tl
694 WHERE rowid = p_rowid;
695
696 /* Check the commit flag and if set, then commit the work. */
697
698 IF FND_API.TO_Boolean(p_commit) THEN
699 COMMIT WORK;
700 END IF;
701
702 EXCEPTION
703
704 WHEN Check_Integrity_Error THEN
705 ROLLBACK TO SAVEPOINT Delete_Row;
706 x_return_status := l_return_status;
707 x_oracle_error := l_oracle_error;
708 IF FND_API.To_Boolean(p_called_by_form) THEN
709 APP_EXCEPTION.Raise_Exception;
710 ELSE
711 x_msg_data := FND_MESSAGE.Get;
712 END IF;
713
714 WHEN Row_Missing_Error THEN
715 ROLLBACK TO SAVEPOINT Delete_Row;
716 x_return_status := 'E';
717 x_oracle_error := APP_EXCEPTION.Get_Code;
718 FND_MESSAGE.SET_NAME('GR',
719 'GR_RECORD_NOT_FOUND');
720 FND_MESSAGE.SET_TOKEN('CODE',
721 l_msg_token,
722 FALSE);
723 IF FND_API.To_Boolean(p_called_by_form) THEN
724 APP_EXCEPTION.Raise_Exception;
725 ELSE
726 x_msg_data := FND_MESSAGE.Get;
727 END IF;
728
729 WHEN OTHERS THEN
730 ROLLBACK TO SAVEPOINT Delete_Row;
731 x_return_status := 'U';
732 x_oracle_error := SQLCODE;
733 l_msg_data := SUBSTR(SQLERRM, 1, 200);
734 FND_MESSAGE.SET_NAME('GR',
735 'GR_UNEXPECTED_ERROR');
736 FND_MESSAGE.SET_TOKEN('TEXT',
737 l_msg_data,
738 FALSE);
739 IF FND_API.To_Boolean(p_called_by_form) THEN
740 APP_EXCEPTION.Raise_Exception;
741 ELSE
742 x_msg_data := FND_MESSAGE.Get;
743 END IF;
744
745 END Delete_Row;
746
747 PROCEDURE Delete_Rows
748 (p_commit IN VARCHAR2,
749 p_called_by_form IN VARCHAR2,
750 p_safety_category_code IN VARCHAR2,
751 x_return_status OUT NOCOPY VARCHAR2,
752 x_oracle_error OUT NOCOPY NUMBER,
753 x_msg_data OUT NOCOPY VARCHAR2)
754 IS
755
756 /* Alpha Variables */
757
758 L_RETURN_STATUS VARCHAR2(1) := 'S';
759 L_MSG_DATA VARCHAR2(2000);
760 L_MSG_TOKEN VARCHAR2(100);
761
762 /* Number Variables */
763
764 L_ORACLE_ERROR NUMBER;
765
766 /* Define the cursors */
767
768 BEGIN
769
770 /* Initialization Routine */
771
772 SAVEPOINT Delete_Rows;
773 x_return_status := 'S';
774 x_oracle_error := 0;
775 x_msg_data := NULL;
776 l_msg_token := p_safety_category_code;
777
778 DELETE FROM gr_safety_categories_tl
779 WHERE safety_category_code = p_safety_category_code;
780
781 IF FND_API.To_Boolean(p_commit) THEN
782 COMMIT WORK;
783 END IF;
784
785 EXCEPTION
786
787 WHEN OTHERS THEN
788 ROLLBACK TO SAVEPOINT Delete_Rows;
789 x_return_status := 'U';
790 x_oracle_error := SQLCODE;
791 l_msg_data := SUBSTR(SQLERRM, 1, 200);
792 FND_MESSAGE.SET_NAME('GR',
793 'GR_UNEXPECTED_ERROR');
794 FND_MESSAGE.SET_TOKEN('TEXT',
795 l_msg_token||sqlerrm,
796 FALSE);
797 IF FND_API.To_Boolean(p_called_by_form) THEN
798 APP_EXCEPTION.Raise_Exception;
799 ELSE
800 x_msg_data := FND_MESSAGE.Get;
801 END IF;
802
803 END Delete_Rows;
804
805 PROCEDURE Check_Foreign_Keys
806 (p_safety_category_code IN VARCHAR2,
807 p_safety_category_description IN VARCHAR2,
808 p_language IN VARCHAR2,
809 p_source_lang IN VARCHAR2,
810 x_return_status OUT NOCOPY VARCHAR2,
811 x_oracle_error OUT NOCOPY NUMBER,
812 x_msg_data OUT NOCOPY VARCHAR2)
813 IS
814
815 /* Alpha Variables */
816
817 L_RETURN_STATUS VARCHAR2(1) := 'S';
818 L_MSG_DATA VARCHAR2(2000);
819 L_MSG_TOKEN VARCHAR2(100);
820 L_LANGUAGE_CODE VARCHAR2(4);
821
822 /* Number Variables */
823
824 L_ORACLE_ERROR NUMBER;
825
826 /* Error Definitions */
827
828 ROW_MISSING_ERROR EXCEPTION;
829
830 /* Define the cursors */
831
832 CURSOR c_get_language
833 IS
834 SELECT lng.language_code
835 FROM fnd_languages lng
836 WHERE lng.language_code = l_language_code;
837 LangRecord c_get_language%ROWTYPE;
838
839 BEGIN
840
841 /* Initialization Routine */
842
843 SAVEPOINT Check_Foreign_Keys;
844 x_return_status := 'S';
845 x_oracle_error := 0;
846 x_msg_data := NULL;
847 l_msg_token := p_safety_category_code || ' ' || p_language;
848
849 /* Check the language codes */
850
851 l_language_code := p_language;
852 OPEN c_get_language;
853 FETCH c_get_language INTO LangRecord;
854 IF c_get_language%NOTFOUND THEN
855 CLOSE c_get_language;
856 l_msg_token := l_language_code;
857 RAISE Row_Missing_Error;
858 END IF;
859 CLOSE c_get_language;
860
861 l_language_code := p_source_lang;
862 OPEN c_get_language;
863 FETCH c_get_language INTO LangRecord;
864 IF c_get_language%NOTFOUND THEN
865 CLOSE c_get_language;
866 l_msg_token := l_language_code;
867 RAISE Row_Missing_Error;
868 END IF;
869 CLOSE c_get_language;
870
871 EXCEPTION
872
873 WHEN Row_Missing_Error THEN
874 ROLLBACK TO SAVEPOINT Delete_Row;
875 x_return_status := 'E';
876 x_oracle_error := APP_EXCEPTION.Get_Code;
877 FND_MESSAGE.SET_NAME('GR',
878 'GR_RECORD_NOT_FOUND');
879 FND_MESSAGE.SET_TOKEN('CODE',
880 l_msg_token,
881 FALSE);
882 x_msg_data := FND_MESSAGE.Get;
883
884 WHEN OTHERS THEN
885 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
886 x_return_status := 'U';
887 x_oracle_error := SQLCODE;
888 l_msg_data := SUBSTR(SQLERRM, 1, 200);
889 FND_MESSAGE.SET_NAME('GR',
890 'GR_UNEXPECTED_ERROR');
891 FND_MESSAGE.SET_TOKEN('TEXT',
892 l_msg_token||sqlerrm,
893 FALSE);
894 x_msg_data := FND_MESSAGE.Get;
895
896 END Check_Foreign_Keys;
897
898 PROCEDURE Check_Integrity
899 (p_called_by_form IN VARCHAR2,
900 p_safety_category_code IN VARCHAR2,
901 p_safety_category_description IN VARCHAR2,
902 p_language IN VARCHAR2,
903 p_source_lang 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(100);
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
925 /* Define the Cursors */
926
927 CURSOR c_get_language_code
928 IS
929 SELECT lng.installed_flag
930 FROM fnd_languages lng
931 WHERE lng.language_code = p_language
932 AND lng.installed_flag IN ('B', 'I');
933 LangRecord c_get_language_code%ROWTYPE;
934
935 BEGIN
936
937 /* Initialization Routine */
938
939 SAVEPOINT Check_Integrity;
940 x_return_status := 'S';
941 x_oracle_error := 0;
942 x_msg_data := NULL;
943
944 /* Check the language isn't base or installed */
945
946 OPEN c_get_language_code;
947 FETCH c_get_language_code INTO LangRecord;
948 IF c_get_language_code%FOUND THEN
949 CLOSE c_get_language_code;
950 RAISE Installed_Language_Error;
951 END IF;
952 CLOSE c_get_language_code;
953
954 EXCEPTION
955
956 WHEN Installed_Language_Error THEN
957 ROLLBACK TO SAVEPOINT Check_Integrity;
958 x_return_status := 'E';
959 FND_MESSAGE.SET_NAME('GR',
960 'GR_INSTALLED_LANG');
961 FND_MESSAGE.SET_TOKEN('CODE',
962 p_language,
963 FALSE);
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_safety_category_code is the 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_safety_category_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_categories_tl_rowid
1009 IS
1010 SELECT cat.rowid
1011 FROM gr_safety_categories_tl cat
1012 WHERE cat.safety_category_code = p_safety_category_code
1013 AND cat.language = p_language;
1014 CategoryTLRecord c_get_categories_tl_rowid%ROWTYPE;
1015
1016 BEGIN
1017
1018 l_msg_data := p_safety_category_code || ' ' || p_language;
1019
1020 x_key_exists := 'F';
1021 OPEN c_get_categories_tl_rowid;
1022 FETCH c_get_categories_tl_rowid INTO CategoryTLRecord;
1023 IF c_get_categories_tl_rowid%FOUND THEN
1024 x_key_exists := 'T';
1025 x_rowid := CategoryTLRecord.rowid;
1026 ELSE
1027 x_key_exists := 'F';
1028 END IF;
1029 CLOSE c_get_categories_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_SAFETY_CATEGORY_CODE IN VARCHAR2
1048 ,X_SAFETY_CATEGORY_DESCRIPTION IN VARCHAR2
1049 ,X_LANGUAGE IN VARCHAR2
1050 ,X_SOURCE_LANG IN VARCHAR2
1051 ) IS
1052 BEGIN
1053 UPDATE GR_SAFETY_CATEGORIES_TL SET
1054 SAFETY_CATEGORY_DESCRIPTION = X_SAFETY_CATEGORY_DESCRIPTION,
1055 SOURCE_LANG = USERENV('LANG'),
1056 LAST_UPDATE_DATE = sysdate,
1057 LAST_UPDATED_BY = 0,
1058 LAST_UPDATE_LOGIN = 0
1059 WHERE (SAFETY_CATEGORY_CODE = X_SAFETY_CATEGORY_CODE)
1060 AND (USERENV('LANG') IN (LANGUAGE, SOURCE_LANG));
1061 END TRANSLATE_ROW;
1062
1063
1064 PROCEDURE load_row (
1065 X_SAFETY_CATEGORY_CODE IN VARCHAR2
1066 ,X_SAFETY_CATEGORY_DESCRIPTION IN VARCHAR2
1067 ,X_LANGUAGE IN VARCHAR2
1068 ,X_SOURCE_LANG IN VARCHAR2
1069 ) IS
1070 CURSOR Cur_rowid IS
1071 SELECT rowid
1072 FROM GR_SAFETY_CATEGORIES_TL
1073 WHERE (SAFETY_CATEGORY_CODE = X_SAFETY_CATEGORY_CODE)
1074 AND (LANGUAGE = X_LANGUAGE);
1075 l_user_id NUMBER DEFAULT 1;
1076 l_row_id VARCHAR2(64);
1077 l_return_status VARCHAR2(1);
1078 l_oracle_error NUMBER;
1079 l_msg_data VARCHAR2(2000);
1080 BEGIN
1081 OPEN Cur_rowid;
1082 FETCH Cur_rowid INTO l_row_id;
1083 IF Cur_rowid%FOUND THEN
1084 GR_SAFETY_CATEGORIES_TL_PKG.UPDATE_ROW(
1085 P_COMMIT => 'T'
1086 ,P_CALLED_BY_FORM => 'F'
1087 ,P_ROWID => l_row_id
1088 ,P_SAFETY_CATEGORY_CODE => X_SAFETY_CATEGORY_CODE
1089 ,P_SAFETY_CATEGORY_DESCRIPTION => X_SAFETY_CATEGORY_DESCRIPTION
1090 ,P_LANGUAGE => X_LANGUAGE
1091 ,P_SOURCE_LANG => X_SOURCE_LANG
1092 ,P_CREATED_BY => l_user_id
1093 ,P_CREATION_DATE => sysdate
1094 ,P_LAST_UPDATED_BY => l_user_id
1095 ,P_LAST_UPDATE_DATE => sysdate
1096 ,P_LAST_UPDATE_LOGIN => 0
1097 ,X_RETURN_STATUS => l_return_status
1098 ,X_ORACLE_ERROR => l_oracle_error
1099 ,X_MSG_DATA => l_msg_data);
1100 ELSE
1101 GR_SAFETY_CATEGORIES_TL_PKG.INSERT_ROW(
1102 P_COMMIT => 'T'
1103 ,P_CALLED_BY_FORM => 'F'
1104 ,P_SAFETY_CATEGORY_CODE => X_SAFETY_CATEGORY_CODE
1105 ,P_SAFETY_CATEGORY_DESCRIPTION => X_SAFETY_CATEGORY_DESCRIPTION
1106 ,P_LANGUAGE => X_LANGUAGE
1107 ,P_SOURCE_LANG => X_SOURCE_LANG
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_ROWID => l_row_id
1114 ,X_RETURN_STATUS => l_return_status
1115 ,X_ORACLE_ERROR => l_oracle_error
1116 ,X_MSG_DATA => l_msg_data);
1117 END IF;
1118 CLOSE Cur_rowid;
1119 END LOAD_ROW;
1120
1121 /* 21-Jan-2002 Mercy Thomas BUG 2190024 - Added procedure NEW_LANGUAGE
1122 to be called from GRNLINS.sql. Generated from tltblgen. */
1123
1124 /* 28-Jan-2002 Melanie Grosser BUG 2190024 - Procedure NEW_LANGUAGE had been
1125 generated incorrectly. I regenerated it.
1126
1127 */
1128 procedure NEW_LANGUAGE
1129 is
1130 begin
1131 delete from GR_SAFETY_CATEGORIES_TL T
1132 where not exists
1133 (select NULL
1134 from GR_SAFETY_CATEGORIES_B B
1135 where B.SAFETY_CATEGORY_CODE = T.SAFETY_CATEGORY_CODE
1136 );
1137
1138 update GR_SAFETY_CATEGORIES_TL T set (
1139 SAFETY_CATEGORY_DESCRIPTION
1140 ) = (select
1141 B.SAFETY_CATEGORY_DESCRIPTION
1142 from GR_SAFETY_CATEGORIES_TL B
1143 where B.SAFETY_CATEGORY_CODE = T.SAFETY_CATEGORY_CODE
1144 and B.LANGUAGE = T.SOURCE_LANG)
1145 where (
1146 T.SAFETY_CATEGORY_CODE,
1147 T.LANGUAGE
1148 ) in (select
1149 SUBT.SAFETY_CATEGORY_CODE,
1150 SUBT.LANGUAGE
1151 from GR_SAFETY_CATEGORIES_TL SUBB, GR_SAFETY_CATEGORIES_TL SUBT
1152 where SUBB.SAFETY_CATEGORY_CODE = SUBT.SAFETY_CATEGORY_CODE
1153 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1154 and (SUBB.SAFETY_CATEGORY_DESCRIPTION <> SUBT.SAFETY_CATEGORY_DESCRIPTION
1155 ));
1156
1157 insert into GR_SAFETY_CATEGORIES_TL (
1158 SAFETY_CATEGORY_CODE,
1159 SAFETY_CATEGORY_DESCRIPTION,
1160 LAST_UPDATE_LOGIN,
1161 LAST_UPDATE_DATE,
1162 LAST_UPDATED_BY,
1163 CREATION_DATE,
1164 CREATED_BY,
1165 LANGUAGE,
1166 SOURCE_LANG
1167 ) select
1168 B.SAFETY_CATEGORY_CODE,
1169 B.SAFETY_CATEGORY_DESCRIPTION,
1170 B.LAST_UPDATE_LOGIN,
1171 B.LAST_UPDATE_DATE,
1172 B.LAST_UPDATED_BY,
1173 B.CREATION_DATE,
1174 B.CREATED_BY,
1175 L.LANGUAGE_CODE,
1176 B.SOURCE_LANG
1177 from GR_SAFETY_CATEGORIES_TL B, FND_LANGUAGES L
1178 where L.INSTALLED_FLAG in ('I', 'B')
1179 and B.LANGUAGE = userenv('LANG')
1180 and not exists
1181 (select NULL
1182 from GR_SAFETY_CATEGORIES_TL T
1183 where T.SAFETY_CATEGORY_CODE = B.SAFETY_CATEGORY_CODE
1184 and T.LANGUAGE = L.LANGUAGE_CODE);
1185
1186 end NEW_LANGUAGE;
1187
1188 END GR_SAFETY_CATEGORIES_TL_PKG;