[Home] [Help]
PACKAGE BODY: APPS.GR_ITEM_GROUPS_TL_PKG
Source
1 PACKAGE BODY GR_ITEM_GROUPS_TL_PKG AS
2 /*$Header: GRHIIGTB.pls 115.10 2002/10/25 20:56:35 methomas ship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_item_group_code IN VARCHAR2,
7 p_language IN VARCHAR2,
8 p_source_lang IN VARCHAR2,
9 p_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(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_item_group_code,
53 p_language,
54 p_source_lang,
55 p_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_item_group_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_item_groups_tl
77 (item_group_code,
78 language,
79 source_lang,
80 description,
81 created_by,
82 creation_date,
83 last_updated_by,
84 last_update_date,
85 last_update_login)
86 VALUES
87 (p_item_group_code,
88 p_language,
89 p_source_lang,
90 p_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_item_group_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_item_group_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_item_group_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_item_group_code || ' ' || p_language;
170 x_return_status := 'U';
171 x_oracle_error := APP_EXCEPTION.Get_Code;
172 l_msg_data := APP_EXCEPTION.Get_Text;
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_item_group_code IN VARCHAR2,
191 p_language IN VARCHAR2,
192 p_source_lang IN VARCHAR2,
193 p_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(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
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_item_group_code || ' ' || p_language;
228
229 /* Now call the check foreign key procedure */
230
231 Check_Foreign_Keys
232 (p_item_group_code,
233 p_language,
234 p_source_lang,
235 p_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_item_groups_tl
244 SET item_group_code = p_item_group_code,
245 language = p_language,
246 source_lang = p_source_lang,
247 description = p_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 := APP_EXCEPTION.Get_Code;
301 l_msg_data := APP_EXCEPTION.Get_Text;
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_item_group_code IN VARCHAR2,
319 p_language IN VARCHAR2,
320 x_return_status OUT NOCOPY VARCHAR2,
321 x_oracle_error OUT NOCOPY NUMBER,
322 x_msg_data OUT NOCOPY VARCHAR2)
323 IS
324
325
326 /* Alpha Variables */
327
328 L_RETURN_STATUS VARCHAR2(1) := 'S';
329 L_MSG_DATA VARCHAR2(2000);
330 L_MSG_TOKEN VARCHAR2(30);
331 L_BASE_DESC VARCHAR2(240);
332 L_LANGUAGE VARCHAR2(4);
333 L_CREATION_DATE DATE;
334 L_LAST_UPDATE_DATE DATE;
335
336 /* Number Variables */
337
338 L_ORACLE_ERROR NUMBER;
339 L_CREATED_BY NUMBER;
340 L_LAST_UPDATED_BY NUMBER;
341 L_LAST_UPDATE_LOGIN NUMBER;
342
343 /* Exceptions */
344
345 LANGUAGE_MISSING_ERROR EXCEPTION;
346
347
348 /* Cursors */
349
350 CURSOR c_get_descs
351 IS
352 SELECT igt.description,
353 igt.created_by,
354 igt.creation_date,
355 igt.last_updated_by,
356 igt.last_update_date,
357 igt.last_update_login
358 FROM gr_item_groups_tl igt
359 WHERE igt.item_group_code = p_item_group_code
360 AND igt.language = l_language;
361 GroupDesc c_get_descs%ROWTYPE;
362
363 CURSOR c_get_installed_languages
364 IS
365 SELECT lng.language_code
366 FROM fnd_languages lng
367 WHERE lng.installed_flag IN ('I', 'B');
368 InstLang c_get_installed_languages%ROWTYPE;
369
370
371 BEGIN
372
373 /* Initialization Routine */
374
375 SAVEPOINT Add_Language;
376 x_return_status := 'S';
377 x_oracle_error := 0;
378 x_msg_data := NULL;
379 l_msg_token := p_item_group_code || ' ' || p_language;
380
381 /* Remove translations with no base row */
382
383 delete from GR_ITEM_GROUPS_TL T
384 where not exists
385 (select NULL
386 from GR_ITEM_GROUPS_B B
387 where B.ITEM_GROUP_CODE = T.ITEM_GROUP_CODE
388 );
389
390 /* Redefault translations from the source language */
391
392 update gr_item_groups_tl t set (
393 description ) =
394 ( select
395 B.DESCRIPTION
396 from GR_ITEM_GROUPS_TL B
397 where B.ITEM_GROUP_CODE = T.ITEM_GROUP_CODE
398 and B.LANGUAGE = T.SOURCE_LANG)
399 where (
400 T.ITEM_GROUP_CODE,
401 T.LANGUAGE
402 ) in (select
403 SUBT.ITEM_GROUP_CODE,
404 SUBT.LANGUAGE
405 from GR_ITEM_GROUPS_TL SUBB, GR_ITEM_GROUPS_TL SUBT
406 where SUBB.ITEM_GROUP_CODE = SUBT.ITEM_GROUP_CODE
407 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
408 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
409 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
410 or (SUBB.DESCRIPTION is not null and SUBT.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_descs;
418 FETCH c_get_descs INTO GroupDesc;
419 IF c_get_descs%NOTFOUND THEN
420 CLOSE c_get_descs;
421 RAISE Language_Missing_Error;
422 ELSE
423 l_base_desc := GroupDesc.description;
424 l_created_by := GroupDesc.created_by;
425 l_creation_date := GroupDesc.creation_date;
426 l_last_updated_by := GroupDesc.last_updated_by;
427 l_last_update_date := GroupDesc.last_update_date;
428 l_last_update_login := GroupDesc.last_update_login;
429 CLOSE c_get_descs;
430 END IF;
431
432 /* Read fnd_languages for the installed and base languages.
433 ** For those that are found, read the item groups 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_descs;
444 FETCH c_get_descs INTO GroupDesc;
445 IF c_get_descs%NOTFOUND THEN
446 CLOSE c_get_descs;
447 INSERT INTO gr_item_groups_tl
448 (item_group_code,
449 language,
450 source_lang,
451 description,
452 created_by,
453 creation_date,
454 last_updated_by,
455 last_update_date,
456 last_update_login)
457 VALUES
458 (p_item_group_code,
459 l_language,
460 p_language,
461 l_base_desc,
462 l_created_by,
463 l_creation_date,
464 l_last_updated_by,
465 l_last_update_date,
466 l_last_update_login);
467 ELSE
468 CLOSE c_get_descs;
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 := APP_EXCEPTION.Get_Code;
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_item_group_code IN VARCHAR2,
519 p_language IN VARCHAR2,
520 p_source_lang IN VARCHAR2,
521 p_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(30);
537
538 /* Number Variables */
539
540 L_ORACLE_ERROR NUMBER;
541
542 /* Exceptions */
543
544 NO_DATA_FOUND_ERROR EXCEPTION;
545 ROW_ALREADY_LOCKED_ERROR EXCEPTION;
546 PRAGMA EXCEPTION_INIT(ROW_ALREADY_LOCKED_ERROR,-54);
547
548 /* Define the cursors */
549
550 CURSOR c_lock_groups_tl
551 IS
552 SELECT *
553 FROM gr_item_groups_tl
554 WHERE rowid = p_rowid
555 FOR UPDATE NOWAIT;
556 LockGroupRcd c_lock_groups_tl%ROWTYPE;
557
558 BEGIN
559
560 /* Initialization Routine */
561
562 SAVEPOINT Lock_Row;
563 x_return_status := 'S';
564 x_oracle_error := 0;
565 x_msg_data := NULL;
566 l_msg_token := p_item_group_code || ' ' || p_language;
567
568 /* Now lock the record */
569
570 OPEN c_lock_groups_tl;
571 FETCH c_lock_groups_tl INTO LockGroupRcd;
572 IF c_lock_groups_tl%NOTFOUND THEN
573 CLOSE c_lock_groups_tl;
574 RAISE No_Data_Found_Error;
575 END IF;
576 CLOSE c_lock_groups_tl;
577
578 IF FND_API.To_Boolean(p_commit) THEN
579 COMMIT WORK;
580 END IF;
581
582 EXCEPTION
583
584 WHEN No_Data_Found_Error THEN
585 ROLLBACK TO SAVEPOINT Lock_Row;
586 x_return_status := 'E';
587 FND_MESSAGE.SET_NAME('GR',
588 'GR_RECORD_NOT_FOUND');
589 FND_MESSAGE.SET_TOKEN('CODE',
590 l_msg_token,
591 FALSE);
592 IF FND_API.To_Boolean(p_called_by_form) THEN
593 APP_EXCEPTION.Raise_Exception;
594 ELSE
595 x_msg_data := FND_MESSAGE.Get;
596 END IF;
597
598 WHEN Row_Already_Locked_Error THEN
599 ROLLBACK TO SAVEPOINT Lock_Row;
600 x_return_status := 'E';
601 x_oracle_error := APP_EXCEPTION.Get_Code;
602 FND_MESSAGE.SET_NAME('GR',
603 'GR_ROW_IS_LOCKED');
604 IF FND_API.To_Boolean(p_called_by_form) THEN
605 APP_EXCEPTION.Raise_Exception;
606 ELSE
607 x_msg_data := FND_MESSAGE.Get;
608 END IF;
609
610 WHEN OTHERS THEN
611 ROLLBACK TO SAVEPOINT Lock_Row;
612 x_return_status := 'U';
613 x_oracle_error := APP_EXCEPTION.Get_Code;
614 l_msg_data := APP_EXCEPTION.Get_Text;
615 FND_MESSAGE.SET_NAME('GR',
616 'GR_UNEXPECTED_ERROR');
617 FND_MESSAGE.SET_TOKEN('TEXT',
618 l_msg_token,
619 FALSE);
620 IF FND_API.To_Boolean(p_called_by_form) THEN
621 APP_EXCEPTION.Raise_Exception;
622 ELSE
623 x_msg_data := FND_MESSAGE.Get;
624 END IF;
625
626 END Lock_Row;
627
628 PROCEDURE Delete_Row
629 (p_commit IN VARCHAR2,
630 p_called_by_form IN VARCHAR2,
631 p_rowid IN VARCHAR2,
632 p_item_group_code IN VARCHAR2,
633 p_language IN VARCHAR2,
634 p_source_lang IN VARCHAR2,
635 p_description IN VARCHAR2,
636 p_created_by IN NUMBER,
637 p_creation_date IN DATE,
638 p_last_updated_by IN NUMBER,
639 p_last_update_date IN DATE,
640 p_last_update_login IN NUMBER,
641 x_return_status OUT NOCOPY VARCHAR2,
642 x_oracle_error OUT NOCOPY NUMBER,
643 x_msg_data OUT NOCOPY VARCHAR2)
644 IS
645
646 /* Alpha Variables */
647
648 L_RETURN_STATUS VARCHAR2(1) := 'S';
649 L_MSG_DATA VARCHAR2(2000);
650 L_MSG_TOKEN VARCHAR2(30);
651 L_CALLED_BY_FORM VARCHAR2(1);
652
653 /* Number Variables */
654
655 L_ORACLE_ERROR NUMBER;
656
657 /* Exceptions */
658
659 CHECK_INTEGRITY_ERROR EXCEPTION;
660 ROW_MISSING_ERROR EXCEPTION;
661 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
662
663 /* Define Cursors */
664
665 CURSOR c_get_language_code
666 IS
667 SELECT lng.installed_flag
668 FROM fnd_languages lng
669 WHERE lng.language_code = p_language
670 AND lng.installed_flag IN ('I', 'B');
671 LangRecord c_get_language_code%ROWTYPE;
672
673 BEGIN
674
675 /* Initialization Routine */
676
677 SAVEPOINT Delete_Row;
678 x_return_status := 'S';
679 l_called_by_form := 'F';
680 x_oracle_error := 0;
681 x_msg_data := NULL;
682 l_msg_token := p_item_group_code || ' ' || p_language;
683
684 /* Now call the check integrity procedure */
685
686 Check_Integrity
687 (l_called_by_form,
688 p_item_group_code,
689 p_language,
690 p_source_lang,
691 p_description,
692 l_return_status,
693 l_oracle_error,
694 l_msg_data);
695
696 IF l_return_status <> 'S' THEN
697 RAISE Check_Integrity_Error;
698 END IF;
699
700 DELETE FROM gr_item_groups_tl
701 WHERE rowid = p_rowid;
702
703 /* Check the commit flag and if set, then commit the work. */
704
705 IF FND_API.TO_Boolean(p_commit) THEN
706 COMMIT WORK;
707 END IF;
708
709 EXCEPTION
710
711 WHEN Check_Integrity_Error THEN
712 ROLLBACK TO SAVEPOINT Delete_Row;
713 x_return_status := l_return_status;
714 x_oracle_error := l_oracle_error;
715 IF FND_API.To_Boolean(p_called_by_form) THEN
716 APP_EXCEPTION.Raise_Exception;
717 ELSE
718 x_msg_data := FND_MESSAGE.Get;
719 END IF;
720
721 WHEN Row_Missing_Error THEN
722 ROLLBACK TO SAVEPOINT Delete_Row;
723 x_return_status := 'E';
724 x_oracle_error := APP_EXCEPTION.Get_Code;
725 FND_MESSAGE.SET_NAME('GR',
726 'GR_RECORD_NOT_FOUND');
727 FND_MESSAGE.SET_TOKEN('CODE',
728 l_msg_token,
729 FALSE);
730 IF FND_API.To_Boolean(p_called_by_form) THEN
731 APP_EXCEPTION.Raise_Exception;
732 ELSE
733 x_msg_data := FND_MESSAGE.Get;
734 END IF;
735
736 WHEN OTHERS THEN
737 ROLLBACK TO SAVEPOINT Delete_Row;
738 x_return_status := 'U';
739 x_oracle_error := APP_EXCEPTION.Get_Code;
740 l_msg_data := APP_EXCEPTION.Get_Text;
741 l_msg_data := APP_EXCEPTION.Get_Text;
742 FND_MESSAGE.SET_NAME('GR',
743 'GR_UNEXPECTED_ERROR');
744 FND_MESSAGE.SET_TOKEN('TEXT',
745 l_msg_token,
746 FALSE);
747 IF FND_API.To_Boolean(p_called_by_form) THEN
748 APP_EXCEPTION.Raise_Exception;
749 ELSE
750 x_msg_data := FND_MESSAGE.Get;
751 END IF;
752
753 END Delete_Row;
754
755 PROCEDURE Delete_Rows
756 (p_commit IN VARCHAR2,
757 p_called_by_form IN VARCHAR2,
758 p_item_group_code IN VARCHAR2,
759 x_return_status OUT NOCOPY VARCHAR2,
760 x_oracle_error OUT NOCOPY NUMBER,
761 x_msg_data OUT NOCOPY VARCHAR2)
762 IS
763
764 /* Alpha Variables */
765
766 L_RETURN_STATUS VARCHAR2(1) := 'S';
767 L_MSG_DATA VARCHAR2(2000);
768 L_MSG_TOKEN VARCHAR2(30);
769
770 /* Number Variables */
771
772 L_ORACLE_ERROR NUMBER;
773
774 /* Define the cursors */
775
776 BEGIN
777
778 /* Initialization Routine */
779
780 SAVEPOINT Delete_Rows;
781 x_return_status := 'S';
782 x_oracle_error := 0;
783 x_msg_data := NULL;
784 l_msg_token := p_item_group_code;
785
786 DELETE FROM gr_item_groups_tl
787 WHERE item_group_code = p_item_group_code;
788
789 IF FND_API.To_Boolean(p_commit) THEN
790 COMMIT WORK;
791 END IF;
792
793 EXCEPTION
794
795 WHEN OTHERS THEN
796 ROLLBACK TO SAVEPOINT Delete_Rows;
797 x_return_status := 'U';
798 x_oracle_error := APP_EXCEPTION.Get_Code;
799 l_msg_data := APP_EXCEPTION.Get_Text;
800 FND_MESSAGE.SET_NAME('GR',
801 'GR_UNEXPECTED_ERROR');
802 FND_MESSAGE.SET_TOKEN('TEXT',
803 l_msg_token,
804 FALSE);
805 IF FND_API.To_Boolean(p_called_by_form) THEN
806 APP_EXCEPTION.Raise_Exception;
807 ELSE
808 x_msg_data := FND_MESSAGE.Get;
809 END IF;
810
811 END Delete_Rows;
812
813 PROCEDURE Check_Foreign_Keys
814 (p_item_group_code IN VARCHAR2,
815 p_language IN VARCHAR2,
816 p_source_lang IN VARCHAR2,
817 p_description IN VARCHAR2,
818 x_return_status OUT NOCOPY VARCHAR2,
819 x_oracle_error OUT NOCOPY NUMBER,
820 x_msg_data OUT NOCOPY VARCHAR2)
821 IS
822
823 /* Alpha Variables */
824
825 L_RETURN_STATUS VARCHAR2(1) := 'S';
826 L_MSG_DATA VARCHAR2(2000);
827 L_MSG_TOKEN VARCHAR2(30);
828 L_LANGUAGE_CODE VARCHAR2(4);
829
830 /* Number Variables */
831
832 L_ORACLE_ERROR NUMBER;
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_item_group_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 l_msg_token := l_language_code;
860 x_return_status := 'E';
861 x_oracle_error := APP_EXCEPTION.Get_Code;
862 FND_MESSAGE.SET_NAME('GR',
863 'GR_RECORD_NOT_FOUND');
864 FND_MESSAGE.SET_TOKEN('CODE',
865 l_msg_token,
866 FALSE);
867 l_msg_data := FND_MESSAGE.Get;
868 END IF;
869 CLOSE c_get_language;
870
871 l_language_code := p_source_lang;
872 OPEN c_get_language;
873 FETCH c_get_language INTO LangRecord;
874 IF c_get_language%NOTFOUND THEN
875 l_msg_token := l_language_code;
876 x_return_status := 'E';
877 x_oracle_error := APP_EXCEPTION.Get_Code;
878 FND_MESSAGE.SET_NAME('GR',
879 'GR_RECORD_NOT_FOUND');
880 FND_MESSAGE.SET_TOKEN('CODE',
881 l_msg_token,
882 FALSE);
883 l_msg_data := FND_MESSAGE.Get;
884 END IF;
885 CLOSE c_get_language;
886
887 IF x_return_status <> 'S' THEN
888 x_msg_data := l_msg_data;
889 END IF;
890
891 EXCEPTION
892
893 WHEN OTHERS THEN
894 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
895 x_return_status := 'U';
896 x_oracle_error := APP_EXCEPTION.Get_Code;
897 l_msg_data := APP_EXCEPTION.Get_Text;
898 FND_MESSAGE.SET_NAME('GR',
899 'GR_UNEXPECTED_ERROR');
900 FND_MESSAGE.SET_TOKEN('TEXT',
901 l_msg_token,
902 FALSE);
903 x_msg_data := FND_MESSAGE.Get;
904
905 END Check_Foreign_Keys;
906
907 PROCEDURE Check_Integrity
908 (p_called_by_form IN VARCHAR2,
909 p_item_group_code IN VARCHAR2,
910 p_language IN VARCHAR2,
911 p_source_lang IN VARCHAR2,
912 p_description IN VARCHAR2,
913 x_return_status OUT NOCOPY VARCHAR2,
914 x_oracle_error OUT NOCOPY NUMBER,
915 x_msg_data OUT NOCOPY VARCHAR2)
916 IS
917
918 /* Alpha Variables */
919
920 L_RETURN_STATUS VARCHAR2(1) := 'S';
921 L_MSG_DATA VARCHAR2(2000);
922 L_CODE_BLOCK VARCHAR2(30);
923
924 /* Number Variables */
925
926 L_ORACLE_ERROR NUMBER;
927 L_RECORD_COUNT NUMBER;
928
929 /* Exceptions */
930
931 INSTALLED_LANGUAGE_ERROR EXCEPTION;
932
933 /* Define the Cursors */
934
935 CURSOR c_get_language_code
936 IS
937 SELECT lng.installed_flag
938 FROM fnd_languages lng
939 WHERE lng.language_code = p_language
940 AND lng.installed_flag IN ('I', 'B');
941 LangRecord c_get_language_code%ROWTYPE;
942
943 BEGIN
944
945 /* Initialization Routine */
946
947 SAVEPOINT Check_Integrity;
948 x_return_status := 'S';
949 x_oracle_error := 0;
950 x_msg_data := NULL;
951
952 /* Check the language isn't base or installed */
953
954 OPEN c_get_language_code;
955 FETCH c_get_language_code INTO LangRecord;
956 IF c_get_language_code%FOUND THEN
957 CLOSE c_get_language_code;
958 RAISE Installed_Language_Error;
959 END IF;
960 CLOSE c_get_language_code;
961
962 EXCEPTION
963
964 WHEN Installed_Language_Error THEN
965 ROLLBACK TO SAVEPOINT Check_Integrity;
966 x_return_status := 'E';
967 FND_MESSAGE.SET_NAME('GR',
968 'GR_INSTALLED_LANG');
969 FND_MESSAGE.SET_TOKEN('CODE',
970 p_language,
971 FALSE);
972 IF FND_API.To_Boolean(p_called_by_form) THEN
973 APP_EXCEPTION.Raise_Exception;
974 ELSE
975 x_msg_data := FND_MESSAGE.Get;
976 END IF;
977
978 WHEN OTHERS THEN
979 ROLLBACK TO SAVEPOINT Check_Integrity;
980 x_return_status := 'U';
981 x_oracle_error := APP_EXCEPTION.Get_Code;
982 l_msg_data := APP_EXCEPTION.Get_Text;
983 FND_MESSAGE.SET_NAME('GR',
984 'GR_UNEXPECTED_ERROR');
985 FND_MESSAGE.SET_TOKEN('TEXT',
986 l_msg_data,
987 FALSE);
988 IF FND_API.To_Boolean(p_called_by_form) THEN
989 APP_EXCEPTION.Raise_Exception;
990 ELSE
991 x_msg_data := FND_MESSAGE.Get;
992 END IF;
993
994 END Check_Integrity;
995
996 PROCEDURE Check_Primary_Key
997 /* p_item_group_code is the group code to check.
998 ** p_language is the language code part of the key
999 ** p_called_by_form is 'T' if called by a form or 'F' if not.
1000 ** x_rowid is the row id of the record if found.
1001 ** x_key_exists is 'T' is the record is found, 'F' if not.
1002 */
1003 (p_item_group_code IN VARCHAR2,
1004 p_language IN VARCHAR2,
1005 p_called_by_form IN VARCHAR2,
1006 x_rowid OUT NOCOPY VARCHAR2,
1007 x_key_exists OUT NOCOPY VARCHAR2)
1008 IS
1009 /* Alphanumeric variables */
1010
1011 L_MSG_DATA VARCHAR2(80);
1012
1013 /* Declare any variables and the cursor */
1014
1015
1016 CURSOR c_get_groups_tl_rowid
1017 IS
1018 SELECT igt.rowid
1019 FROM gr_item_groups_tl igt
1020 WHERE igt.item_group_code = p_item_group_code
1021 AND igt.language = p_language;
1022 GroupTLRecord c_get_groups_tl_rowid%ROWTYPE;
1023
1024 BEGIN
1025
1026 l_msg_data := p_item_group_code || ' ' || p_language;
1027
1028 x_key_exists := 'F';
1029 OPEN c_get_groups_tl_rowid;
1030 FETCH c_get_groups_tl_rowid INTO GroupTLRecord;
1031 IF c_get_groups_tl_rowid%FOUND THEN
1032 x_key_exists := 'T';
1033 x_rowid := GroupTLRecord.rowid;
1034 ELSE
1035 x_key_exists := 'F';
1036 END IF;
1037 CLOSE c_get_groups_tl_rowid;
1038
1039 EXCEPTION
1040
1041 WHEN Others THEN
1042 l_msg_data := APP_EXCEPTION.Get_Text;
1043 FND_MESSAGE.SET_NAME('GR',
1044 'GR_UNEXPECTED_ERROR');
1045 FND_MESSAGE.SET_TOKEN('TEXT',
1046 l_msg_data,
1047 FALSE);
1048 IF FND_API.To_Boolean(p_called_by_form) THEN
1049 APP_EXCEPTION.Raise_Exception;
1050 END IF;
1051
1052 END Check_Primary_Key;
1053
1054 /* 21-Jan-2002 Mercy Thomas BUG 2190024 - Added procedure NEW_LANGUAGE
1055 to be called from GRNLINS.sql. Generated from tltblgen. */
1056
1057 /* 28-Jan-2002 Melanie Grosser BUG 2190024 - Procedure NEW_LANGUAGE had been
1058 generated incorrectly. I regenerated it.
1059
1060 */
1061
1062 procedure NEW_LANGUAGE
1063 is
1064 begin
1065 delete from GR_ITEM_GROUPS_TL T
1066 where not exists
1067 (select NULL
1068 from GR_ITEM_GROUPS_B B
1069 where B.ITEM_GROUP_CODE = T.ITEM_GROUP_CODE
1070 );
1071
1072 update GR_ITEM_GROUPS_TL T set (
1073 DESCRIPTION
1074 ) = (select
1075 B.DESCRIPTION
1076 from GR_ITEM_GROUPS_TL B
1077 where B.ITEM_GROUP_CODE = T.ITEM_GROUP_CODE
1078 and B.LANGUAGE = T.SOURCE_LANG)
1079 where (
1080 T.ITEM_GROUP_CODE,
1081 T.LANGUAGE
1082 ) in (select
1083 SUBT.ITEM_GROUP_CODE,
1084 SUBT.LANGUAGE
1085 from GR_ITEM_GROUPS_TL SUBB, GR_ITEM_GROUPS_TL SUBT
1086 where SUBB.ITEM_GROUP_CODE = SUBT.ITEM_GROUP_CODE
1087 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1088 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
1089 ));
1090
1091 insert into GR_ITEM_GROUPS_TL (
1092 ITEM_GROUP_CODE,
1093 DESCRIPTION,
1094 CREATED_BY,
1095 CREATION_DATE,
1096 LAST_UPDATED_BY,
1097 LAST_UPDATE_DATE,
1098 LAST_UPDATE_LOGIN,
1099 LANGUAGE,
1100 SOURCE_LANG
1101 ) select
1102 B.ITEM_GROUP_CODE,
1103 B.DESCRIPTION,
1104 B.CREATED_BY,
1105 B.CREATION_DATE,
1106 B.LAST_UPDATED_BY,
1107 B.LAST_UPDATE_DATE,
1108 B.LAST_UPDATE_LOGIN,
1109 L.LANGUAGE_CODE,
1110 B.SOURCE_LANG
1111 from GR_ITEM_GROUPS_TL B, FND_LANGUAGES L
1112 where L.INSTALLED_FLAG in ('I', 'B')
1113 and B.LANGUAGE = userenv('LANG')
1114 and not exists
1115 (select NULL
1116 from GR_ITEM_GROUPS_TL T
1117 where T.ITEM_GROUP_CODE = B.ITEM_GROUP_CODE
1118 and T.LANGUAGE = L.LANGUAGE_CODE);
1119
1120 end NEW_LANGUAGE;
1121
1122 END GR_ITEM_GROUPS_TL_PKG;