[Home] [Help]
PACKAGE BODY: APPS.GR_MAIN_HEADINGS_TL_PKG
Source
1 PACKAGE BODY GR_MAIN_HEADINGS_TL_PKG AS
2 /*$Header: GRHIMHTB.pls 115.7 2002/10/28 16:47:11 methomas ship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_main_heading_code IN VARCHAR2,
7 p_language IN VARCHAR2,
8 p_main_heading_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(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_main_heading_code,
53 p_language,
54 p_main_heading_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_main_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_main_headings_tl
77 (main_heading_code,
78 language,
79 main_heading_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_main_heading_code,
88 p_language,
89 p_main_heading_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_main_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_main_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_main_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_main_heading_code;
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_main_heading_code IN VARCHAR2,
191 p_language IN VARCHAR2,
192 p_main_heading_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(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_main_heading_code;
228
229 /* Now call the check foreign key procedure */
230
231 Check_Foreign_Keys
232 (p_main_heading_code,
233 p_language,
234 p_main_heading_description,
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_main_headings_tl
244 SET main_heading_code = p_main_heading_code,
245 language = p_language,
246 main_heading_description = p_main_heading_description,
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 := SQLCODE;
301 l_msg_data := SUBSTR(SQLERRM, 1, 200);
302 FND_MESSAGE.SET_NAME('GR',
303 'GR_UNEXPECTED_ERROR');
304 FND_MESSAGE.SET_TOKEN('TEXT',
305 l_msg_token,
306 FALSE);
307 IF FND_API.To_Boolean(p_called_by_form) THEN
308 APP_EXCEPTION.Raise_Exception;
309 ELSE
310 x_msg_data := FND_MESSAGE.Get;
311 END IF;
312
313 END Update_Row;
314
315 PROCEDURE Add_Language
316 (p_commit IN VARCHAR2,
317 p_called_by_form IN VARCHAR2,
318 p_main_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_MAIN_HEADINGS_TL.main_heading_description%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_main_heading
349 IS
350 SELECT mht.main_heading_description,
351 mht.created_by,
352 mht.creation_date,
353 mht.last_updated_by,
354 mht.last_update_date,
355 mht.last_update_login
356 FROM gr_main_headings_tl mht
357 WHERE mht.main_heading_code = p_main_heading_code
358 AND mht.language = l_language;
359 MainHdgRecord c_get_main_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_main_heading_code || ' ' || p_language;
378
379 /* Remove translations with no base row */
380
381 delete from GR_MAIN_HEADINGS_TL T
382 where not exists
383 (select NULL
384 from GR_MAIN_HEADINGS_B B
385 where B.MAIN_HEADING_CODE = T.MAIN_HEADING_CODE
386 );
387
388 /* Redefault translations from the source language */
389
390 update gr_main_headings_tl t set (
391 main_heading_description ) =
392 ( select
393 B.MAIN_HEADING_DESCRIPTION
394 from GR_MAIN_HEADINGS_TL B
395 where B.MAIN_HEADING_CODE = T.MAIN_HEADING_CODE
396 and B.LANGUAGE = T.SOURCE_LANG)
397 where (
398 T.MAIN_HEADING_CODE,
399 T.LANGUAGE
400 ) in (select
401 SUBT.MAIN_HEADING_CODE,
402 SUBT.LANGUAGE
403 from GR_MAIN_HEADINGS_TL SUBB, GR_MAIN_HEADINGS_TL SUBT
404 where SUBB.MAIN_HEADING_CODE = SUBT.MAIN_HEADING_CODE
405 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
406 and (SUBB.MAIN_HEADING_DESCRIPTION <> SUBT.MAIN_HEADING_DESCRIPTION
407 or (SUBB.MAIN_HEADING_DESCRIPTION is null and SUBT.MAIN_HEADING_DESCRIPTION is not null)
408 or (SUBB.MAIN_HEADING_DESCRIPTION is not null and SUBT.MAIN_HEADING_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_main_heading;
416 FETCH c_get_main_heading INTO MainHdgRecord;
417 IF c_get_main_heading%NOTFOUND THEN
418 CLOSE c_get_main_heading;
419 RAISE Language_Missing_Error;
420 ELSE
421 l_base_desc := MainHdgRecord.main_heading_description;
422 l_created_by := MainHdgRecord.created_by;
423 l_creation_date := MainHdgRecord.creation_date;
424 l_last_updated_by := MainHdgRecord.last_updated_by;
425 l_last_update_date := MainHdgRecord.last_update_date;
426 l_last_update_login := MainHdgRecord.last_update_login;
427 CLOSE c_get_main_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_main_heading;
442 FETCH c_get_main_heading INTO MainHdgRecord;
443 IF c_get_main_heading%NOTFOUND THEN
444 CLOSE c_get_main_heading;
445 INSERT INTO gr_main_headings_tl
446 (main_heading_code,
447 language,
448 main_heading_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_main_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_main_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 := 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_main_heading_code IN VARCHAR2,
518 p_language IN VARCHAR2,
519 p_main_heading_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(100);
536
537 /* Number Variables */
538
539 L_ORACLE_ERROR NUMBER;
540
541 /* Exceptions */
542
543 NO_DATA_FOUND_ERROR EXCEPTION;
544 ROW_ALREADY_LOCKED_ERROR EXCEPTION;
545 PRAGMA EXCEPTION_INIT(ROW_ALREADY_LOCKED_ERROR,-54);
546
547 /* Define the cursors */
548
549 CURSOR c_lock_main_hdgs_tl
550 IS
551 SELECT *
552 FROM gr_main_headings_tl
553 WHERE rowid = p_rowid
554 FOR UPDATE NOWAIT;
555 LockMainHdgRcd c_lock_main_hdgs_tl%ROWTYPE;
556
557 BEGIN
558
559 /* Initialization Routine */
560
561 SAVEPOINT Lock_Row;
562 x_return_status := 'S';
563 x_oracle_error := 0;
564 x_msg_data := NULL;
565 l_msg_token := p_main_heading_code || ' ' || p_language;
566
567 /* Now lock the record */
568
569 OPEN c_lock_main_hdgs_tl;
570 FETCH c_lock_main_hdgs_tl INTO LockMainHdgRcd;
571 IF c_lock_main_hdgs_tl%NOTFOUND THEN
572 CLOSE c_lock_main_hdgs_tl;
573 RAISE No_Data_Found_Error;
574 END IF;
575 CLOSE c_lock_main_hdgs_tl;
576
577 IF FND_API.To_Boolean(p_commit) THEN
578 COMMIT WORK;
579 END IF;
580
581 EXCEPTION
582
583 WHEN No_Data_Found_Error THEN
584 ROLLBACK TO SAVEPOINT Lock_Row;
585 x_return_status := 'E';
586 FND_MESSAGE.SET_NAME('GR',
587 'GR_RECORD_NOT_FOUND');
588 FND_MESSAGE.SET_TOKEN('CODE',
589 l_msg_token,
590 FALSE);
591 IF FND_API.To_Boolean(p_called_by_form) THEN
592 APP_EXCEPTION.Raise_Exception;
593 ELSE
594 x_msg_data := FND_MESSAGE.Get;
595 END IF;
596
597 WHEN Row_Already_Locked_Error THEN
598 ROLLBACK TO SAVEPOINT Lock_Row;
599 x_return_status := 'E';
600 x_oracle_error := APP_EXCEPTION.Get_Code;
601 FND_MESSAGE.SET_NAME('GR',
602 'GR_ROW_IS_LOCKED');
603 IF FND_API.To_Boolean(p_called_by_form) THEN
604 APP_EXCEPTION.Raise_Exception;
605 ELSE
606 x_msg_data := FND_MESSAGE.Get;
607 END IF;
608
609 WHEN OTHERS THEN
610 ROLLBACK TO SAVEPOINT Lock_Row;
611 x_return_status := 'U';
612 x_oracle_error := SQLCODE;
613 l_msg_data := SUBSTR(SQLERRM, 1, 200);
614 FND_MESSAGE.SET_NAME('GR',
615 'GR_UNEXPECTED_ERROR');
616 FND_MESSAGE.SET_TOKEN('TEXT',
617 l_msg_token,
618 FALSE);
619 IF FND_API.To_Boolean(p_called_by_form) THEN
620 APP_EXCEPTION.Raise_Exception;
621 ELSE
622 x_msg_data := FND_MESSAGE.Get;
623 END IF;
624
625 END Lock_Row;
626
627 PROCEDURE Delete_Row
628 (p_commit IN VARCHAR2,
629 p_called_by_form IN VARCHAR2,
630 p_rowid IN VARCHAR2,
631 p_main_heading_code IN VARCHAR2,
632 p_language IN VARCHAR2,
633 p_main_heading_description IN VARCHAR2,
634 p_source_lang IN VARCHAR2,
635 p_created_by IN NUMBER,
636 p_creation_date IN DATE,
637 p_last_updated_by IN NUMBER,
638 p_last_update_date IN DATE,
639 p_last_update_login IN NUMBER,
640 x_return_status OUT NOCOPY VARCHAR2,
641 x_oracle_error OUT NOCOPY NUMBER,
642 x_msg_data OUT NOCOPY VARCHAR2)
643 IS
644
645 /* Alpha Variables */
646
647 L_RETURN_STATUS VARCHAR2(1) := 'S';
648 L_MSG_DATA VARCHAR2(2000);
649 L_MSG_TOKEN VARCHAR2(100);
650 L_CALLED_BY_FORM VARCHAR2(1);
651
652 /* Number Variables */
653
654 L_ORACLE_ERROR NUMBER;
655
656 /* Exceptions */
657
658 CHECK_INTEGRITY_ERROR EXCEPTION;
659 ROW_MISSING_ERROR EXCEPTION;
660 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
661
662 /* Define the cursor */
663
664 BEGIN
665
666 /* Initialization Routine */
667
668 SAVEPOINT Delete_Row;
669 x_return_status := 'S';
670 l_called_by_form := 'F';
671 x_oracle_error := 0;
672 x_msg_data := NULL;
673 l_msg_token := p_main_heading_code || ' ' || p_language;
674
675 /* Now call the check integrity procedure */
676
677 Check_Integrity
678 (l_called_by_form,
679 p_main_heading_code,
680 p_language,
681 p_main_heading_description,
682 p_source_lang,
683 l_return_status,
684 l_oracle_error,
685 l_msg_data);
686
687 IF l_return_status <> 'S' THEN
688 RAISE Check_Integrity_Error;
689 END IF;
690
691 DELETE FROM gr_main_headings_tl
692 WHERE rowid = p_rowid;
693
694 /* Check the commit flag and if set, then commit the work. */
695
696 IF FND_API.TO_Boolean(p_commit) THEN
697 COMMIT WORK;
698 END IF;
699
700 EXCEPTION
701
702 WHEN Check_Integrity_Error THEN
703 ROLLBACK TO SAVEPOINT Delete_Row;
704 x_return_status := l_return_status;
705 x_oracle_error := l_oracle_error;
706 IF FND_API.To_Boolean(p_called_by_form) THEN
707 APP_EXCEPTION.Raise_Exception;
708 ELSE
709 x_msg_data := FND_MESSAGE.Get;
710 END IF;
711
712 WHEN Row_Missing_Error THEN
713 ROLLBACK TO SAVEPOINT Delete_Row;
714 x_return_status := 'E';
715 x_oracle_error := APP_EXCEPTION.Get_Code;
716 FND_MESSAGE.SET_NAME('GR',
717 'GR_RECORD_NOT_FOUND');
718 FND_MESSAGE.SET_TOKEN('CODE',
719 l_msg_token,
720 FALSE);
721 IF FND_API.To_Boolean(p_called_by_form) THEN
722 APP_EXCEPTION.Raise_Exception;
723 ELSE
724 x_msg_data := FND_MESSAGE.Get;
725 END IF;
726
727 WHEN OTHERS THEN
728 ROLLBACK TO SAVEPOINT Delete_Row;
729 x_return_status := 'U';
730 x_oracle_error := SQLCODE;
731 l_msg_data := SUBSTR(SQLERRM, 1, 200);
732 FND_MESSAGE.SET_NAME('GR',
733 'GR_UNEXPECTED_ERROR');
734 FND_MESSAGE.SET_TOKEN('TEXT',
735 l_msg_data,
736 FALSE);
737 IF FND_API.To_Boolean(p_called_by_form) THEN
738 APP_EXCEPTION.Raise_Exception;
739 ELSE
740 x_msg_data := FND_MESSAGE.Get;
741 END IF;
742
743 END Delete_Row;
744
745 PROCEDURE Delete_Rows
746 (p_commit IN VARCHAR2,
747 p_called_by_form IN VARCHAR2,
748 p_main_heading_code IN VARCHAR2,
749 x_return_status OUT NOCOPY VARCHAR2,
750 x_oracle_error OUT NOCOPY NUMBER,
751 x_msg_data OUT NOCOPY VARCHAR2)
752 IS
753
754 /* Alpha Variables */
755
756 L_RETURN_STATUS VARCHAR2(1) := 'S';
757 L_MSG_DATA VARCHAR2(2000);
758 L_MSG_TOKEN VARCHAR2(100);
759
760 /* Number Variables */
761
762 L_ORACLE_ERROR NUMBER;
763
764 /* Define the cursors */
765
766 BEGIN
767
768 /* Initialization Routine */
769
770 SAVEPOINT Delete_Rows;
771 x_return_status := 'S';
772 x_oracle_error := 0;
773 x_msg_data := NULL;
774 l_msg_token := p_main_heading_code;
775
776 DELETE FROM gr_main_headings_tl
777 WHERE main_heading_code = p_main_heading_code;
778
779 IF FND_API.To_Boolean(p_commit) THEN
780 COMMIT WORK;
781 END IF;
782
783 EXCEPTION
784
785 WHEN OTHERS THEN
786 ROLLBACK TO SAVEPOINT Delete_Rows;
787 x_return_status := 'U';
788 x_oracle_error := SQLCODE;
789 l_msg_data := SUBSTR(SQLERRM, 1, 200);
790 FND_MESSAGE.SET_NAME('GR',
791 'GR_UNEXPECTED_ERROR');
792 FND_MESSAGE.SET_TOKEN('TEXT',
793 l_msg_token,
794 FALSE);
795 IF FND_API.To_Boolean(p_called_by_form) THEN
796 APP_EXCEPTION.Raise_Exception;
797 ELSE
798 x_msg_data := FND_MESSAGE.Get;
799 END IF;
800
801 END Delete_Rows;
802
803 PROCEDURE Check_Foreign_Keys
804 (p_main_heading_code IN VARCHAR2,
805 p_language IN VARCHAR2,
806 p_main_heading_description IN VARCHAR2,
807 p_source_lang IN VARCHAR2,
808 x_return_status OUT NOCOPY VARCHAR2,
809 x_oracle_error OUT NOCOPY NUMBER,
810 x_msg_data OUT NOCOPY VARCHAR2)
811 IS
812
813 /* Alpha Variables */
814
815 L_RETURN_STATUS VARCHAR2(1) := 'S';
816 L_MSG_DATA VARCHAR2(2000);
817 L_MSG_TOKEN VARCHAR2(100);
818 L_LANGUAGE_CODE VARCHAR2(4);
819
820 /* Number Variables */
821
822 L_ORACLE_ERROR NUMBER;
823
824 /* Error Definitions */
825
826 ROW_MISSING_ERROR EXCEPTION;
827
828 /* Define the cursors */
829
830 CURSOR c_get_language
831 IS
832 SELECT lng.language_code
833 FROM fnd_languages lng
834 WHERE lng.language_code = l_language_code;
835 LangRecord c_get_language%ROWTYPE;
836
837 BEGIN
838
839 /* Initialization Routine */
840
841 SAVEPOINT Check_Foreign_Keys;
842 x_return_status := 'S';
843 x_oracle_error := 0;
844 x_msg_data := NULL;
845 l_msg_token := p_main_heading_code || ' ' || p_language;
846
847 /* Check the language codes */
848
849 l_language_code := p_language;
850 OPEN c_get_language;
851 FETCH c_get_language INTO LangRecord;
852 IF c_get_language%NOTFOUND THEN
853 CLOSE c_get_language;
854 l_msg_token := l_language_code;
855 RAISE Row_Missing_Error;
856 END IF;
857 CLOSE c_get_language;
858
859 l_language_code := p_source_lang;
860 OPEN c_get_language;
861 FETCH c_get_language INTO LangRecord;
862 IF c_get_language%NOTFOUND THEN
863 CLOSE c_get_language;
864 l_msg_token := l_language_code;
865 RAISE Row_Missing_Error;
866 END IF;
867 CLOSE c_get_language;
868
869 EXCEPTION
870
871 WHEN Row_Missing_Error THEN
872 ROLLBACK TO SAVEPOINT Delete_Row;
873 x_return_status := 'E';
874 x_oracle_error := APP_EXCEPTION.Get_Code;
875 FND_MESSAGE.SET_NAME('GR',
876 'GR_RECORD_NOT_FOUND');
877 FND_MESSAGE.SET_TOKEN('CODE',
878 l_msg_token,
879 FALSE);
880 x_msg_data := FND_MESSAGE.Get;
881
882 WHEN OTHERS THEN
883 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
884 x_return_status := 'U';
885 x_oracle_error := SQLCODE;
886 l_msg_data := SUBSTR(SQLERRM, 1, 200);
887 FND_MESSAGE.SET_NAME('GR',
888 'GR_UNEXPECTED_ERROR');
889 FND_MESSAGE.SET_TOKEN('TEXT',
890 l_msg_token,
891 FALSE);
892 x_msg_data := FND_MESSAGE.Get;
893
894 END Check_Foreign_Keys;
895
896 PROCEDURE Check_Integrity
897 (p_called_by_form IN VARCHAR2,
898 p_main_heading_code IN VARCHAR2,
899 p_language IN VARCHAR2,
900 p_main_heading_description IN VARCHAR2,
901 p_source_lang IN VARCHAR2,
902 x_return_status OUT NOCOPY VARCHAR2,
903 x_oracle_error OUT NOCOPY NUMBER,
904 x_msg_data OUT NOCOPY VARCHAR2)
905 IS
906
907 /* Alpha Variables */
908
909 L_RETURN_STATUS VARCHAR2(1) := 'S';
910 L_MSG_DATA VARCHAR2(2000);
911 L_CODE_BLOCK VARCHAR2(100);
912
913 /* Number Variables */
914
915 L_ORACLE_ERROR NUMBER;
916 L_RECORD_COUNT NUMBER;
917
918 /* Exceptions */
919
920 INSTALLED_LANGUAGE_ERROR EXCEPTION;
921
922
923 /* Define the Cursors */
924
925 CURSOR c_get_language_code
926 IS
927 SELECT lng.installed_flag
928 FROM fnd_languages lng
929 WHERE lng.language_code = p_language
930 AND lng.installed_flag IN ('B', 'I');
931 LangRecord c_get_language_code%ROWTYPE;
932
933 BEGIN
934
935 /* Initialization Routine */
936
937 SAVEPOINT Check_Integrity;
938 x_return_status := 'S';
939 x_oracle_error := 0;
940 x_msg_data := NULL;
941
942 /* Check the language isn't base or installed */
943
944 OPEN c_get_language_code;
945 FETCH c_get_language_code INTO LangRecord;
946 IF c_get_language_code%FOUND THEN
947 CLOSE c_get_language_code;
948 RAISE Installed_Language_Error;
949 END IF;
950 CLOSE c_get_language_code;
951
952 EXCEPTION
953
954 WHEN Installed_Language_Error THEN
955 ROLLBACK TO SAVEPOINT Check_Integrity;
956 x_return_status := 'E';
957 FND_MESSAGE.SET_NAME('GR',
958 'GR_INSTALLED_LANG');
959 FND_MESSAGE.SET_TOKEN('CODE',
960 p_language,
961 FALSE);
962 IF FND_API.To_Boolean(p_called_by_form) THEN
963 APP_EXCEPTION.Raise_Exception;
964 ELSE
965 x_msg_data := FND_MESSAGE.Get;
966 END IF;
967
968 WHEN OTHERS THEN
969 ROLLBACK TO SAVEPOINT Check_Integrity;
970 x_return_status := 'U';
971 x_oracle_error := SQLCODE;
972 l_msg_data := SUBSTR(SQLERRM, 1, 200);
973 FND_MESSAGE.SET_NAME('GR',
974 'GR_UNEXPECTED_ERROR');
975 FND_MESSAGE.SET_TOKEN('TEXT',
976 l_msg_data,
977 FALSE);
978 IF FND_API.To_Boolean(p_called_by_form) THEN
979 APP_EXCEPTION.Raise_Exception;
980 ELSE
981 x_msg_data := FND_MESSAGE.Get;
982 END IF;
983
984 END Check_Integrity;
985
986 PROCEDURE Check_Primary_Key
987 /* p_main_heading_code is the code to check.
988 ** p_language is the language code part of the key
989 ** p_called_by_form is 'T' if called by a form or 'F' if not.
990 ** x_rowid is the row id of the record if found.
991 ** x_key_exists is 'T' is the record is found, 'F' if not.
992 */
993 (p_main_heading_code IN VARCHAR2,
994 p_language IN VARCHAR2,
995 p_called_by_form IN VARCHAR2,
996 x_rowid OUT NOCOPY VARCHAR2,
997 x_key_exists OUT NOCOPY VARCHAR2)
998 IS
999 /* Alphanumeric variables */
1000
1001 L_MSG_DATA VARCHAR2(80);
1002
1003 /* Declare any variables and the cursor */
1004
1005
1006 CURSOR c_get_main_hdgs_tl_rowid
1007 IS
1008 SELECT mht.rowid
1009 FROM gr_main_headings_tl mht
1010 WHERE mht.main_heading_code = p_main_heading_code
1011 AND mht.language = p_language;
1012 HeadingTLRecord c_get_main_hdgs_tl_rowid%ROWTYPE;
1013
1014 BEGIN
1015
1016 l_msg_data := p_main_heading_code || ' ' || p_language;
1017
1018 x_key_exists := 'F';
1019 OPEN c_get_main_hdgs_tl_rowid;
1020 FETCH c_get_main_hdgs_tl_rowid INTO HeadingTLRecord;
1021 IF c_get_main_hdgs_tl_rowid%FOUND THEN
1022 x_key_exists := 'T';
1023 x_rowid := HeadingTLRecord.rowid;
1024 ELSE
1025 x_key_exists := 'F';
1026 END IF;
1027 CLOSE c_get_main_hdgs_tl_rowid;
1028
1029 EXCEPTION
1030
1031 WHEN Others THEN
1032 l_msg_data := SUBSTR(SQLERRM, 1, 200);
1033 FND_MESSAGE.SET_NAME('GR',
1034 'GR_UNEXPECTED_ERROR');
1035 FND_MESSAGE.SET_TOKEN('TEXT',
1036 l_msg_data,
1037 FALSE);
1038 IF FND_API.To_Boolean(p_called_by_form) THEN
1039 APP_EXCEPTION.Raise_Exception;
1040 END IF;
1041
1042 END Check_Primary_Key;
1043
1044 PROCEDURE translate_row (
1045 X_MAIN_HEADING_CODE IN VARCHAR2
1046 ,X_LANGUAGE IN VARCHAR2
1047 ,X_MAIN_HEADING_DESCRIPTION IN VARCHAR2
1048 ,X_SOURCE_LANG IN VARCHAR2
1049 ) IS
1050 BEGIN
1051 UPDATE GR_MAIN_HEADINGS_TL SET
1052 MAIN_HEADING_DESCRIPTION = X_MAIN_HEADING_DESCRIPTION,
1053 SOURCE_LANG = USERENV('LANG'),
1054 LAST_UPDATE_DATE = sysdate,
1055 LAST_UPDATED_BY = 0,
1056 LAST_UPDATE_LOGIN = 0
1057 WHERE (MAIN_HEADING_CODE = X_MAIN_HEADING_CODE)
1058 AND (USERENV('LANG') IN (LANGUAGE, SOURCE_LANG));
1059 END TRANSLATE_ROW;
1060
1061
1062 PROCEDURE load_row (
1063 X_MAIN_HEADING_CODE IN VARCHAR2
1064 ,X_LANGUAGE IN VARCHAR2
1065 ,X_MAIN_HEADING_DESCRIPTION IN VARCHAR2
1066 ,X_SOURCE_LANG IN VARCHAR2
1067 ) IS
1068 CURSOR Cur_rowid IS
1069 SELECT rowid
1070 FROM GR_MAIN_HEADINGS_TL
1071 WHERE (MAIN_HEADING_CODE = X_MAIN_HEADING_CODE)
1072 AND (LANGUAGE = X_LANGUAGE);
1073 l_user_id NUMBER DEFAULT 1;
1074 l_row_id VARCHAR2(64);
1075 l_return_status VARCHAR2(1);
1076 l_oracle_error NUMBER;
1077 l_msg_data VARCHAR2(2000);
1078 BEGIN
1079 OPEN Cur_rowid;
1080 FETCH Cur_rowid INTO l_row_id;
1081 IF Cur_rowid%FOUND THEN
1082 GR_MAIN_HEADINGS_TL_PKG.UPDATE_ROW(
1083 P_COMMIT => 'T'
1084 ,P_CALLED_BY_FORM => 'F'
1085 ,P_ROWID => l_row_id
1086 ,P_MAIN_HEADING_CODE => X_MAIN_HEADING_CODE
1087 ,P_LANGUAGE => X_LANGUAGE
1088 ,P_MAIN_HEADING_DESCRIPTION => X_MAIN_HEADING_DESCRIPTION
1089 ,P_SOURCE_LANG => X_SOURCE_LANG
1090 ,P_CREATED_BY => l_user_id
1091 ,P_CREATION_DATE => sysdate
1092 ,P_LAST_UPDATED_BY => l_user_id
1093 ,P_LAST_UPDATE_DATE => sysdate
1094 ,P_LAST_UPDATE_LOGIN => 0
1095 ,X_RETURN_STATUS => l_return_status
1096 ,X_ORACLE_ERROR => l_oracle_error
1097 ,X_MSG_DATA => l_msg_data);
1098 ELSE
1099 GR_MAIN_HEADINGS_TL_PKG.INSERT_ROW(
1100 P_COMMIT => 'T'
1101 ,P_CALLED_BY_FORM => 'F'
1102 ,P_MAIN_HEADING_CODE => X_MAIN_HEADING_CODE
1103 ,P_LANGUAGE => X_LANGUAGE
1104 ,P_MAIN_HEADING_DESCRIPTION => X_MAIN_HEADING_DESCRIPTION
1105 ,P_SOURCE_LANG => X_SOURCE_LANG
1106 ,P_CREATED_BY => l_user_id
1107 ,P_CREATION_DATE => sysdate
1108 ,P_LAST_UPDATED_BY => l_user_id
1109 ,P_LAST_UPDATE_DATE => sysdate
1110 ,P_LAST_UPDATE_LOGIN => 0
1111 ,X_ROWID => l_row_id
1112 ,X_RETURN_STATUS => l_return_status
1113 ,X_ORACLE_ERROR => l_oracle_error
1114 ,X_MSG_DATA => l_msg_data);
1115 END IF;
1116 CLOSE Cur_rowid;
1117 END LOAD_ROW;
1118
1119 /* 21-Jan-2002 Mercy Thomas BUG 2190024 - Added procedure NEW_LANGUAGE
1120 to be called from GRNLINS.sql. Generated from tltblgen. */
1121
1122 /* 28-Jan-2002 Melanie Grosser BUG 2190024 - Procedure NEW_LANGUAGE had been
1123 generated incorrectly. I regenerated it.
1124
1125 */
1126
1127 procedure NEW_LANGUAGE
1128 is
1129 begin
1130 delete from GR_MAIN_HEADINGS_TL T
1131 where not exists
1132 (select NULL
1133 from GR_MAIN_HEADINGS_B B
1134 where B.MAIN_HEADING_CODE = T.MAIN_HEADING_CODE
1135 );
1136
1137 update GR_MAIN_HEADINGS_TL T set (
1138 MAIN_HEADING_DESCRIPTION
1139 ) = (select
1140 B.MAIN_HEADING_DESCRIPTION
1141 from GR_MAIN_HEADINGS_TL B
1142 where B.MAIN_HEADING_CODE = T.MAIN_HEADING_CODE
1143 and B.LANGUAGE = T.SOURCE_LANG)
1144 where (
1145 T.MAIN_HEADING_CODE,
1146 T.LANGUAGE
1147 ) in (select
1148 SUBT.MAIN_HEADING_CODE,
1149 SUBT.LANGUAGE
1150 from GR_MAIN_HEADINGS_TL SUBB, GR_MAIN_HEADINGS_TL SUBT
1151 where SUBB.MAIN_HEADING_CODE = SUBT.MAIN_HEADING_CODE
1152 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1153 and (SUBB.MAIN_HEADING_DESCRIPTION <> SUBT.MAIN_HEADING_DESCRIPTION
1154 ));
1155
1156 insert into GR_MAIN_HEADINGS_TL (
1157 CREATION_DATE,
1158 LAST_UPDATED_BY,
1159 LAST_UPDATE_DATE,
1160 LAST_UPDATE_LOGIN,
1161 MAIN_HEADING_CODE,
1162 MAIN_HEADING_DESCRIPTION,
1163 CREATED_BY,
1164 LANGUAGE,
1165 SOURCE_LANG
1166 ) select
1167 B.CREATION_DATE,
1168 B.LAST_UPDATED_BY,
1169 B.LAST_UPDATE_DATE,
1170 B.LAST_UPDATE_LOGIN,
1171 B.MAIN_HEADING_CODE,
1172 B.MAIN_HEADING_DESCRIPTION,
1173 B.CREATED_BY,
1174 L.LANGUAGE_CODE,
1175 B.SOURCE_LANG
1176 from GR_MAIN_HEADINGS_TL B, FND_LANGUAGES L
1177 where L.INSTALLED_FLAG in ('I', 'B')
1178 and B.LANGUAGE = userenv('LANG')
1179 and not exists
1180 (select NULL
1181 from GR_MAIN_HEADINGS_TL T
1182 where T.MAIN_HEADING_CODE = B.MAIN_HEADING_CODE
1183 and T.LANGUAGE = L.LANGUAGE_CODE);
1184
1185 end NEW_LANGUAGE;
1186
1187 END GR_MAIN_HEADINGS_TL_PKG;