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