[Home] [Help]
PACKAGE BODY: APPS.GR_OTHER_NAMES_TL_PKG
Source
1 PACKAGE BODY GR_OTHER_NAMES_TL_PKG AS
2 /*$Header: GRHIONTB.pls 115.13 2002/10/28 17:03:11 methomas ship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_item_code IN VARCHAR2,
7 p_language IN VARCHAR2,
8 p_orgn_code IN VARCHAR2,
9 p_synonym_sequence_number IN NUMBER,
10 p_source_lang IN VARCHAR2,
11 p_item_other_name IN VARCHAR2,
12 p_created_by IN NUMBER,
13 p_creation_date IN DATE,
14 p_last_updated_by IN NUMBER,
15 p_last_update_date IN DATE,
16 p_last_update_login IN NUMBER,
17 x_rowid OUT NOCOPY VARCHAR2,
18 x_return_status OUT NOCOPY VARCHAR2,
19 x_oracle_error OUT NOCOPY NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2)
21 IS
22 /* Alpha Variables */
23
24 L_RETURN_STATUS VARCHAR2(1) := 'S';
25 L_KEY_EXISTS VARCHAR2(1);
26 L_MSG_DATA VARCHAR2(2000);
27 L_ROWID VARCHAR2(18);
28 L_MSG_TOKEN VARCHAR2(100);
29
30 /* Number Variables */
31
32 L_ORACLE_ERROR NUMBER;
33 /* Exceptions */
34
35 FOREIGN_KEY_ERROR EXCEPTION;
36 ITEM_EXISTS_ERROR EXCEPTION;
37 ROW_MISSING_ERROR EXCEPTION;
38
39 /* Declare cursors */
40
41
42 BEGIN
43
44 /* Initialization Routine */
45
46 SAVEPOINT Insert_Row;
47 x_return_status := 'S';
48 x_oracle_error := 0;
49 x_msg_data := NULL;
50
51 /* Now call the check foreign key procedure */
52
53 Check_Foreign_Keys
54 (p_item_code,
55 p_language,
56 p_orgn_code,
57 p_synonym_sequence_number,
58 p_source_lang,
59 p_item_other_name,
60 l_return_status,
61 l_oracle_error,
62 l_msg_data);
63 IF l_return_status <> 'S' THEN
64 RAISE Foreign_Key_Error;
65 END IF;
66
67 /* Now check the primary key doesn't already exist */
68
69 Check_Primary_Key
70 (p_item_code,
71 p_orgn_code,
72 p_language,
73 p_synonym_sequence_number,
74 'F',
75 l_rowid,
76 l_key_exists);
77
78 IF FND_API.To_Boolean(l_key_exists) THEN
79 RAISE Item_Exists_Error;
80 END IF;
81
82 INSERT INTO gr_other_names_tl
83 (item_code,
84 language,
85 orgn_code,
86 synonym_sequence_number,
87 source_lang,
88 item_other_name,
89 created_by,
90 creation_date,
91 last_updated_by,
92 last_update_date,
93 last_update_login)
94 VALUES
95 (p_item_code,
96 p_language,
97 p_orgn_code,
98 p_synonym_sequence_number,
99 p_source_lang,
100 p_item_other_name,
101 p_created_by,
102 p_creation_date,
103 p_last_updated_by,
104 p_last_update_date,
105 p_last_update_login);
106
107 /* Now get the row id of the inserted record */
108
109 Check_Primary_Key
110 (p_item_code,
111 p_orgn_code,
112 p_language,
113 p_synonym_sequence_number,
114 'F',
115 l_rowid,
116 l_key_exists);
117
118 IF FND_API.To_Boolean(l_key_exists) THEN
119 x_rowid := l_rowid;
120 ELSE
121 RAISE Row_Missing_Error;
122 END IF;
123
124 /* Check the commit flag and if set, then commit the work. */
125
126 IF FND_API.To_Boolean(p_commit) THEN
127 COMMIT WORK;
128 END IF;
129
130 EXCEPTION
131
132 WHEN Foreign_Key_Error THEN
133 ROLLBACK TO SAVEPOINT Insert_Row;
134 x_return_status := l_return_status;
135 x_oracle_error := l_oracle_error;
136 FND_MESSAGE.SET_NAME('GR',
137 'GR_FOREIGN_KEY_ERROR');
138 FND_MESSAGE.SET_TOKEN('TEXT',
139 l_msg_data,
140 FALSE);
141 IF FND_API.To_Boolean(p_called_by_form) THEN
142 APP_EXCEPTION.Raise_Exception;
143 ELSE
144 x_msg_data := FND_MESSAGE.Get;
145 END IF;
146
147 WHEN Item_Exists_Error THEN
148 ROLLBACK TO SAVEPOINT Insert_Row;
149 l_msg_token := p_item_code || ' ' || p_orgn_code || ' ' || p_language;
150 x_return_status := 'E';
151 x_oracle_error := APP_EXCEPTION.Get_Code;
152 FND_MESSAGE.SET_NAME('GR',
153 'GR_RECORD_EXISTS');
154 FND_MESSAGE.SET_TOKEN('CODE',
155 l_msg_token,
156 FALSE);
157 IF FND_API.To_Boolean(p_called_by_form) THEN
158 APP_EXCEPTION.Raise_Exception;
159 ELSE
160 x_msg_data := FND_MESSAGE.Get;
161 END IF;
162
163 WHEN Row_Missing_Error THEN
164 ROLLBACK TO SAVEPOINT Insert_Row;
165 l_msg_token := p_item_code || ' ' || p_orgn_code || ' ' || p_language;
166 x_return_status := 'E';
167 x_oracle_error := APP_EXCEPTION.Get_Code;
168 FND_MESSAGE.SET_NAME('GR',
169 'GR_NO_RECORD_INSERTED');
170 FND_MESSAGE.SET_TOKEN('CODE',
171 l_msg_token,
172 FALSE);
173 IF FND_API.To_Boolean(p_called_by_form) THEN
174 APP_EXCEPTION.Raise_Exception;
175 ELSE
176 x_msg_data := FND_MESSAGE.Get;
177 END IF;
178
179 WHEN OTHERS THEN
180 ROLLBACK TO SAVEPOINT Insert_Row;
181 l_msg_token := p_item_code || ' ' || p_orgn_code || ' ' || p_language;
182 x_return_status := 'U';
183 x_oracle_error := APP_EXCEPTION.Get_Code;
184 l_msg_data := APP_EXCEPTION.Get_Text;
185 FND_MESSAGE.SET_NAME('GR',
186 'GR_UNEXPECTED_ERROR');
187 FND_MESSAGE.SET_TOKEN('TEXT',
188 l_msg_token,
189 FALSE);
190 IF FND_API.To_Boolean(p_called_by_form) THEN
191 APP_EXCEPTION.Raise_Exception;
192 ELSE
193 x_msg_data := FND_MESSAGE.Get;
194 END IF;
195
196 END Insert_Row;
197
198 PROCEDURE Update_Row
199 (p_commit IN VARCHAR2,
200 p_called_by_form IN VARCHAR2,
201 p_rowid IN VARCHAR2,
202 p_item_code IN VARCHAR2,
203 p_language IN VARCHAR2,
204 p_orgn_code IN VARCHAR2,
205 p_synonym_sequence_number IN NUMBER,
206 p_source_lang IN VARCHAR2,
207 p_item_other_name IN VARCHAR2,
208 p_created_by IN NUMBER,
209 p_creation_date IN DATE,
210 p_last_updated_by IN NUMBER,
211 p_last_update_date IN DATE,
212 p_last_update_login IN NUMBER,
213 x_return_status OUT NOCOPY VARCHAR2,
214 x_oracle_error OUT NOCOPY NUMBER,
215 x_msg_data OUT NOCOPY VARCHAR2)
216 IS
217
218 /* Alpha Variables */
219
220 L_RETURN_STATUS VARCHAR2(1) := 'S';
221 L_MSG_DATA VARCHAR2(2000);
222 L_MSG_TOKEN VARCHAR2(100);
223
224 /* Number Variables */
225
226 L_ORACLE_ERROR NUMBER;
227
228 /* Exceptions */
229
230 FOREIGN_KEY_ERROR EXCEPTION;
231 ROW_MISSING_ERROR EXCEPTION;
232
233 BEGIN
234
235 /* Initialization Routine */
236
237 SAVEPOINT Update_Row;
238 x_return_status := 'S';
239 x_oracle_error := 0;
240 x_msg_data := NULL;
241 l_msg_token := p_item_code || ' ' || p_orgn_code || ' ' || p_language;
242
243 /* Now call the check foreign key procedure */
244
245 Check_Foreign_Keys
246 (p_item_code,
247 p_language,
248 p_orgn_code,
249 p_synonym_sequence_number,
250 p_source_lang,
251 p_item_other_name,
252 l_return_status,
253 l_oracle_error,
254 l_msg_data);
255
256 IF l_return_status <> 'S' THEN
257 RAISE Foreign_Key_Error;
258 ELSE
259 UPDATE gr_other_names_tl
260 SET item_code = p_item_code,
261 language = p_language,
262 orgn_code = p_orgn_code,
263 synonym_sequence_number = p_synonym_sequence_number,
264 source_lang = p_source_lang,
265 item_other_name = p_item_other_name,
266 created_by = p_created_by,
267 creation_date = p_creation_date,
268 last_updated_by = p_last_updated_by,
269 last_update_date = p_last_update_date,
270 last_update_login = p_last_update_login
271 WHERE rowid = p_rowid;
272 IF SQL%NOTFOUND THEN
273 RAISE Row_Missing_Error;
274 END IF;
275 END IF;
276
277 /* Check the commit flag and if set, then commit the work. */
278
279 IF FND_API.To_Boolean(p_commit) THEN
280 COMMIT WORK;
281 END IF;
282
283 EXCEPTION
284
285 WHEN Foreign_Key_Error THEN
286 ROLLBACK TO SAVEPOINT Update_Row;
287 x_return_status := l_return_status;
288 x_oracle_error := l_oracle_error;
289 FND_MESSAGE.SET_NAME('GR',
290 'GR_FOREIGN_KEY_ERROR');
291 FND_MESSAGE.SET_TOKEN('TEXT',
292 l_msg_data,
293 FALSE);
294 IF FND_API.To_Boolean(p_called_by_form) THEN
295 APP_EXCEPTION.Raise_Exception;
296 ELSE
297 x_msg_data := FND_MESSAGE.Get;
298 END IF;
299
300 WHEN Row_Missing_Error THEN
301 ROLLBACK TO SAVEPOINT Update_Row;
302 x_return_status := 'E';
303 x_oracle_error := APP_EXCEPTION.Get_Code;
304 FND_MESSAGE.SET_NAME('GR',
305 'GR_NO_RECORD_INSERTED');
306 FND_MESSAGE.SET_TOKEN('CODE',
307 l_msg_token,
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 WHEN OTHERS THEN
316 ROLLBACK TO SAVEPOINT Update_Row;
317 x_return_status := 'U';
318 x_oracle_error := APP_EXCEPTION.Get_Code;
319 l_msg_data := APP_EXCEPTION.Get_Text;
320 FND_MESSAGE.SET_NAME('GR',
321 'GR_UNEXPECTED_ERROR');
322 FND_MESSAGE.SET_TOKEN('TEXT',
323 l_msg_token,
324 FALSE);
325 IF FND_API.To_Boolean(p_called_by_form) THEN
326 APP_EXCEPTION.Raise_Exception;
327 ELSE
328 x_msg_data := FND_MESSAGE.Get;
329 END IF;
330
331 END Update_Row;
332
333 PROCEDURE Add_Language
334 (p_commit IN VARCHAR2,
335 p_called_by_form IN VARCHAR2,
336 p_item_code IN VARCHAR2,
337 p_orgn_code IN VARCHAR2,
338 p_language IN VARCHAR2,
339 x_return_status OUT NOCOPY VARCHAR2,
340 x_oracle_error OUT NOCOPY NUMBER,
341 x_msg_data OUT NOCOPY VARCHAR2)
342 IS
343
344 /* Alpha Variables */
345
346 L_RETURN_STATUS VARCHAR2(1) := 'S';
347 L_MSG_DATA VARCHAR2(2000);
348 L_MSG_TOKEN VARCHAR2(100);
349 L_BASE_DESC VARCHAR2(240);
350 L_ORGN_CODE VARCHAR2(4);
351 L_LANGUAGE VARCHAR2(4);
352 L_BASE_LANG VARCHAR2(4);
353 L_CREATION_DATE DATE;
354 L_LAST_UPDATE_DATE DATE;
355
356 /* Number Variables */
357
358 L_ORACLE_ERROR NUMBER;
359 L_CREATED_BY NUMBER;
360 L_LAST_UPDATED_BY NUMBER;
361 L_LAST_UPDATE_LOGIN NUMBER;
362 L_SYNONYM_SEQUENCE NUMBER;
363 L_EXISTS NUMBER;
364 /* Exceptions */
365
366 LANGUAGE_MISSING_ERROR EXCEPTION;
367
368
369 /* Cursors */
370
371 CURSOR c_get_descs
372 IS
373 SELECT ion.item_other_name,
374 ion.orgn_code,
375 ion.synonym_sequence_number,
376 ion.created_by,
377 ion.creation_date,
378 ion.last_updated_by,
379 ion.last_update_date,
380 ion.last_update_login,
381 ion.language
382 FROM gr_other_names_tl ion
383 WHERE ion.item_code = p_item_code
384 AND ion.orgn_code = p_orgn_code
385 AND (ion.language = l_language OR
386 synonym_sequence_number NOT IN (SELECT synonym_sequence_number
387 FROM gr_other_names_tl
388 WHERE item_code = p_item_code
389 AND orgn_code = p_orgn_code
390 AND language = l_language));
391 OtherNameDesc c_get_descs%ROWTYPE;
392
393 CURSOR c_get_descs_sequence
394 IS
395 SELECT 1
396 FROM gr_other_names_tl ion
397 WHERE ion.item_code = p_item_code
398 AND ion.orgn_code = p_orgn_code
399 AND ion.language = l_language
400 AND ion.synonym_sequence_number = l_synonym_sequence;
401
402 CURSOR c_get_installed_languages
403 IS
404 SELECT lng.language_code
405 FROM fnd_languages lng
406 WHERE lng.installed_flag IN ('I', 'B');
407 InstLang c_get_installed_languages%ROWTYPE;
408
409
410 BEGIN
411
412 /* Initialization Routine */
413
414 SAVEPOINT Add_Language;
415 x_return_status := 'S';
416 x_oracle_error := 0;
417 x_msg_data := NULL;
418 l_msg_token := p_item_code || ' ' || p_orgn_code || ' ' || p_language;
419
420 /* Remove translations with no base row */
421
422 delete from GR_OTHER_NAMES_TL T
423 where not exists
424 (select NULL
425 from GR_ITEM_GENERAL B,
426 SY_ORGN_MST S
427 where B.ITEM_CODE = T.ITEM_CODE
428 and S.ORGN_CODE = T.ORGN_CODE
429 );
430
431 /* Redefault translations from the source language */
432
433 update gr_other_names_tl t set (
434 item_other_name ) =
435 ( select
436 B.ITEM_OTHER_NAME
437 from GR_OTHER_NAMES_TL B
438 where B.ITEM_CODE = T.ITEM_CODE
439 and B.ORGN_CODE = T.ORGN_CODE
440 and B.LANGUAGE = T.SOURCE_LANG
441 and B.SYNONYM_SEQUENCE_NUMBER = T.SYNONYM_SEQUENCE_NUMBER)
442 where (
443 T.ITEM_CODE,
444 T.ORGN_CODE,
445 T.LANGUAGE
446 ) in (select
447 SUBT.ITEM_CODE,
448 SUBT.ORGN_CODE,
449 SUBT.LANGUAGE
450 from GR_OTHER_NAMES_TL SUBB, GR_OTHER_NAMES_TL SUBT
451 where SUBB.ITEM_CODE = SUBT.ITEM_CODE
452 and SUBB.ORGN_CODE = SUBT.ORGN_CODE
453 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
454 and (SUBB.ITEM_OTHER_NAME <> SUBT.ITEM_OTHER_NAME
455 or (SUBB.ITEM_OTHER_NAME is null and SUBT.ITEM_OTHER_NAME is not null)
456 or (SUBB.ITEM_OTHER_NAME is not null and SUBT.ITEM_OTHER_NAME is null)
457 ));
461 ** user environment variable.
458
459
460 /* Open the language cursor and get the description entered from the
462 */
463 l_language := p_language;
464 OPEN c_get_descs;
465 FETCH c_get_descs INTO OtherNameDesc;
466 IF c_get_descs%NOTFOUND THEN
467 CLOSE c_get_descs;
468 RAISE Language_Missing_Error;
469 ELSE
470 LOOP
471 l_base_lang := OtherNameDesc.language;
472 l_base_desc := OtherNameDesc.item_other_name;
473 l_orgn_code := OtherNameDesc.orgn_code;
474 l_synonym_sequence := OtherNameDesc.synonym_sequence_number;
475 l_created_by := OtherNameDesc.created_by;
476 l_creation_date := OtherNameDesc.creation_date;
477 l_last_updated_by := OtherNameDesc.last_updated_by;
478 l_last_update_date := OtherNameDesc.last_update_date;
479 l_last_update_login := OtherNameDesc.last_update_login;
480
481 /* Read fnd_languages for the installed and base languages.
482 ** For those that are found, read the phrases tl table.
483 ** If there isn't a record in the table for that language then
484 ** insert it and go on to the next.
485 */
486 OPEN c_get_installed_languages;
487 FETCH c_get_installed_languages INTO InstLang;
488 IF c_get_installed_languages%FOUND THEN
489 WHILE c_get_installed_languages%FOUND LOOP
490 IF InstLang.language_code <> l_base_lang THEN
491 l_language := InstLang.language_code;
492 OPEN c_get_descs_sequence;
493 FETCH c_get_descs_sequence INTO l_exists;
494 IF c_get_descs_sequence%NOTFOUND THEN
495 CLOSE c_get_descs_sequence;
496 INSERT INTO gr_other_names_tl
497 (item_code,
498 language,
499 orgn_code,
500 synonym_sequence_number,
501 source_lang,
502 item_other_name,
503 created_by,
504 creation_date,
505 last_updated_by,
506 last_update_date,
507 last_update_login)
508 VALUES
509 (p_item_code,
510 l_language,
511 l_orgn_code,
512 l_synonym_sequence,
513 p_language,
514 l_base_desc,
515 l_created_by,
516 l_creation_date,
517 l_last_updated_by,
518 l_last_update_date,
519 l_last_update_login);
520 ELSE
521 CLOSE c_get_descs_sequence;
522 END IF;
523 END IF;
524 FETCH c_get_installed_languages INTO InstLang;
525 END LOOP;
526 END IF;
527 CLOSE c_get_installed_languages;
528 FETCH c_get_descs INTO OtherNameDesc;
529 EXIT WHEN c_get_descs%NOTFOUND;
530 END LOOP;
531 CLOSE c_get_descs;
532 END IF;
533
534 IF FND_API.To_Boolean(p_commit) THEN
535 COMMIT WORK;
536 END IF;
537
538 EXCEPTION
539
540 WHEN Language_Missing_Error THEN
541 ROLLBACK TO SAVEPOINT Add_Language;
542 x_return_status := 'E';
543 x_oracle_error := APP_EXCEPTION.Get_Code;
544 FND_MESSAGE.SET_NAME('GR',
545 'GR_RECORD_NOT_FOUND');
546 FND_MESSAGE.SET_TOKEN('CODE',
547 l_msg_token,
548 FALSE);
549 IF FND_API.To_Boolean(p_called_by_form) THEN
550 APP_EXCEPTION.Raise_Exception;
551 ELSE
552 x_msg_data := FND_MESSAGE.Get;
553 END IF;
554
555 WHEN OTHERS THEN
556 ROLLBACK TO SAVEPOINT Add_Language;
557 x_return_status := 'U';
558 x_oracle_error := APP_EXCEPTION.Get_Code;
559 FND_MESSAGE.SET_NAME('GR',
560 'GR_UNEXPECTED_ERROR');
561 FND_MESSAGE.SET_TOKEN('TEXT',
562 l_msg_token,
563 FALSE);
564 IF FND_API.To_Boolean(p_called_by_form) THEN
565 APP_EXCEPTION.Raise_Exception;
566 ELSE
567 x_msg_data := FND_MESSAGE.Get;
568 END IF;
569
570 END Add_Language;
571
572 PROCEDURE Lock_Row
573 (p_commit IN VARCHAR2,
574 p_called_by_form IN VARCHAR2,
575 p_rowid IN VARCHAR2,
576 p_item_code IN VARCHAR2,
577 p_language IN VARCHAR2,
578 p_orgn_code IN VARCHAR2,
579 p_synonym_sequence_number IN NUMBER,
580 p_source_lang IN VARCHAR2,
581 p_item_other_name IN VARCHAR2,
582 p_created_by IN NUMBER,
583 p_creation_date IN DATE,
584 p_last_updated_by IN NUMBER,
585 p_last_update_date IN DATE,
586 p_last_update_login IN NUMBER,
587 x_return_status OUT NOCOPY VARCHAR2,
588 x_oracle_error OUT NOCOPY NUMBER,
589 x_msg_data OUT NOCOPY VARCHAR2)
590 IS
591
592 /* Alpha Variables */
593
594 L_RETURN_STATUS VARCHAR2(1) := 'S';
595 L_MSG_DATA VARCHAR2(2000);
596 L_MSG_TOKEN VARCHAR2(100);
597
598 /* Number Variables */
599
600 L_ORACLE_ERROR NUMBER;
601
602 /* Exceptions */
603
604 NO_DATA_FOUND_ERROR EXCEPTION;
605 ROW_ALREADY_LOCKED_ERROR EXCEPTION;
606 PRAGMA EXCEPTION_INIT(ROW_ALREADY_LOCKED_ERROR,-54);
607
608 /* Define the cursors */
609
613 FROM gr_other_names_tl
610 CURSOR c_lock_other_name_tl
611 IS
612 SELECT *
614 WHERE rowid = p_rowid
615 FOR UPDATE NOWAIT;
616 LockOtherNameRcd c_lock_other_name_tl%ROWTYPE;
617
618 BEGIN
619
620 /* Initialization Routine */
621
622 SAVEPOINT Lock_Row;
623 x_return_status := 'S';
624 x_oracle_error := 0;
625 x_msg_data := NULL;
626 l_msg_token := p_item_code || ' ' || p_orgn_code || ' ' || p_language;
627
628 /* Now lock the record */
629
630 OPEN c_lock_other_name_tl;
631 FETCH c_lock_other_name_tl INTO LockOtherNameRcd;
632 IF c_lock_other_name_tl%NOTFOUND THEN
633 CLOSE c_lock_other_name_tl;
634 RAISE No_Data_Found_Error;
635 END IF;
636 CLOSE c_lock_other_name_tl;
637
638 IF FND_API.To_Boolean(p_commit) THEN
639 COMMIT WORK;
640 END IF;
641
642 EXCEPTION
643
644 WHEN No_Data_Found_Error THEN
645 ROLLBACK TO SAVEPOINT Lock_Row;
646 x_return_status := 'E';
647 FND_MESSAGE.SET_NAME('GR',
648 'GR_RECORD_NOT_FOUND');
649 FND_MESSAGE.SET_TOKEN('CODE',
650 l_msg_token,
651 FALSE);
652 IF FND_API.To_Boolean(p_called_by_form) THEN
653 APP_EXCEPTION.Raise_Exception;
654 ELSE
655 x_msg_data := FND_MESSAGE.Get;
656 END IF;
657
658 WHEN Row_Already_Locked_Error THEN
659 ROLLBACK TO SAVEPOINT Lock_Row;
660 x_return_status := 'E';
661 x_oracle_error := APP_EXCEPTION.Get_Code;
662 FND_MESSAGE.SET_NAME('GR',
663 'GR_ROW_IS_LOCKED');
664 IF FND_API.To_Boolean(p_called_by_form) THEN
665 APP_EXCEPTION.Raise_Exception;
666 ELSE
667 x_msg_data := FND_MESSAGE.Get;
668 END IF;
669
670 WHEN OTHERS THEN
671 ROLLBACK TO SAVEPOINT Lock_Row;
672 x_return_status := 'U';
673 x_oracle_error := APP_EXCEPTION.Get_Code;
674 l_msg_data := APP_EXCEPTION.Get_Text;
675 FND_MESSAGE.SET_NAME('GR',
676 'GR_UNEXPECTED_ERROR');
677 FND_MESSAGE.SET_TOKEN('TEXT',
678 l_msg_token,
679 FALSE);
680 IF FND_API.To_Boolean(p_called_by_form) THEN
681 APP_EXCEPTION.Raise_Exception;
682 ELSE
683 x_msg_data := FND_MESSAGE.Get;
684 END IF;
685
686 END Lock_Row;
687
688 PROCEDURE Delete_Row
689 (p_commit IN VARCHAR2,
690 p_called_by_form IN VARCHAR2,
691 p_rowid IN VARCHAR2,
692 p_item_code IN VARCHAR2,
693 p_language IN VARCHAR2,
694 p_orgn_code IN VARCHAR2,
695 p_synonym_sequence_number IN NUMBER,
696 p_source_lang IN VARCHAR2,
697 p_item_other_name IN VARCHAR2,
698 p_created_by IN NUMBER,
699 p_creation_date IN DATE,
700 p_last_updated_by IN NUMBER,
701 p_last_update_date IN DATE,
702 p_last_update_login IN NUMBER,
703 x_return_status OUT NOCOPY VARCHAR2,
704 x_oracle_error OUT NOCOPY NUMBER,
705 x_msg_data OUT NOCOPY VARCHAR2)
706 IS
707
708 /* Alpha Variables */
709
710 L_RETURN_STATUS VARCHAR2(1) := 'S';
711 L_MSG_DATA VARCHAR2(2000);
712 L_MSG_TOKEN VARCHAR2(100);
713 L_CALLED_BY_FORM VARCHAR2(1);
714
715 /* Number Variables */
716
717 L_ORACLE_ERROR NUMBER;
718
719 /* Exceptions */
720
721 CHECK_INTEGRITY_ERROR EXCEPTION;
722 ROW_MISSING_ERROR EXCEPTION;
723 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
724
725 /* Define the cursor */
726
727 BEGIN
728
729 /* Initialization Routine */
730
731 SAVEPOINT Delete_Row;
732 x_return_status := 'S';
733 l_called_by_form := 'F';
734 x_oracle_error := 0;
735 x_msg_data := NULL;
736 l_msg_token := p_item_code || ' ' || p_orgn_code || ' ' || p_language;
737
738 /* Now call the check integrity procedure */
739
740 Check_Integrity
741 (l_called_by_form,
742 p_item_code,
743 p_language,
744 p_orgn_code,
745 p_synonym_sequence_number,
746 p_source_lang,
747 p_item_other_name,
748 l_return_status,
749 l_oracle_error,
750 l_msg_data);
751
752 IF l_return_status <> 'S' THEN
753 RAISE Check_Integrity_Error;
754 END IF;
755
756 DELETE FROM gr_other_names_tl
757 WHERE rowid = p_rowid;
758
759 /* Check the commit flag and if set, then commit the work. */
760
761 IF FND_API.TO_Boolean(p_commit) THEN
762 COMMIT WORK;
763 END IF;
764
765 EXCEPTION
766
767 WHEN Check_Integrity_Error THEN
768 ROLLBACK TO SAVEPOINT Delete_Row;
769 x_return_status := l_return_status;
770 x_oracle_error := l_oracle_error;
771 IF FND_API.To_Boolean(p_called_by_form) THEN
772 APP_EXCEPTION.Raise_Exception;
773 ELSE
774 x_msg_data := FND_MESSAGE.Get;
778 ROLLBACK TO SAVEPOINT Delete_Row;
775 END IF;
776
777 WHEN Row_Missing_Error THEN
779 x_return_status := 'E';
780 x_oracle_error := APP_EXCEPTION.Get_Code;
781 FND_MESSAGE.SET_NAME('GR',
782 'GR_RECORD_NOT_FOUND');
783 FND_MESSAGE.SET_TOKEN('CODE',
784 l_msg_token,
785 FALSE);
786 IF FND_API.To_Boolean(p_called_by_form) THEN
787 APP_EXCEPTION.Raise_Exception;
788 ELSE
789 x_msg_data := FND_MESSAGE.Get;
790 END IF;
791
792 WHEN OTHERS THEN
793 ROLLBACK TO SAVEPOINT Delete_Row;
794 x_return_status := 'U';
795 x_oracle_error := APP_EXCEPTION.Get_Code;
796 l_msg_data := APP_EXCEPTION.Get_Text;
797 l_msg_data := APP_EXCEPTION.Get_Text;
798 FND_MESSAGE.SET_NAME('GR',
799 'GR_UNEXPECTED_ERROR');
800 FND_MESSAGE.SET_TOKEN('TEXT',
801 l_msg_token,
802 FALSE);
803 IF FND_API.To_Boolean(p_called_by_form) THEN
804 APP_EXCEPTION.Raise_Exception;
805 ELSE
806 x_msg_data := FND_MESSAGE.Get;
807 END IF;
808
809 END Delete_Row;
810
811 PROCEDURE Delete_Rows
812 (p_commit IN VARCHAR2,
813 p_called_by_form IN VARCHAR2,
814 p_delete_option IN VARCHAR2,
815 p_item_code IN VARCHAR2,
816 p_orgn_code IN VARCHAR2,
817 x_return_status OUT NOCOPY VARCHAR2,
818 x_oracle_error OUT NOCOPY NUMBER,
819 x_msg_data OUT NOCOPY VARCHAR2)
820 IS
821
822 /* Alpha Variables */
823
824 L_RETURN_STATUS VARCHAR2(1) := 'S';
825 L_MSG_DATA VARCHAR2(2000);
826 L_MSG_TOKEN VARCHAR2(100);
827
828 /* Number Variables */
829
830 L_ORACLE_ERROR NUMBER;
831
832 /* Excceptions */
833
834 NULL_DELETE_OPTION_ERROR EXCEPTION;
835
836 /* Define the cursors */
837
838 BEGIN
839
840 /* Initialization Routine */
841
842 SAVEPOINT Delete_Rows;
843 x_return_status := 'S';
844 x_oracle_error := 0;
845 x_msg_data := NULL;
846 l_msg_token := p_item_code || ' ' || p_orgn_code;
847
848 /*
849 ** p_delete_option has one of three values.
850 ** 'I' - Delete all rows for the item in p_item_code.
851 ** 'O' - Delete all rows for the label in p_orgn_code.
852 ** 'B' - Delete all rows for the item and label codes.
853 */
854 IF p_delete_option = 'I' THEN
855 IF p_item_code IS NULL THEN
856 l_msg_token := 'Item Code';
857 RAISE Null_Delete_Option_Error;
858 ELSE
859 l_msg_token := p_item_code;
860
861 DELETE FROM gr_other_names_tl
862 WHERE item_code = p_item_code;
863 END IF;
864 ELSIF p_delete_option = 'O' THEN
865 IF p_orgn_code IS NULL THEN
866 l_msg_token := 'Organisation Code';
867 RAISE Null_Delete_Option_Error;
868 ELSE
869 l_msg_token := p_orgn_code;
870
871 DELETE FROM gr_other_names_tl
872 WHERE orgn_code = p_orgn_code;
873 END IF;
874 ELSIF p_delete_option = 'B' THEN
875 IF p_item_code IS NULL OR
876 p_orgn_code IS NULL THEN
877 l_msg_token := 'Item Code / Organisation Code';
878 RAISE Null_Delete_Option_Error;
879 ELSE
880 l_msg_token := p_item_code || ' ' || p_orgn_code;
881
882 DELETE FROM gr_other_names_tl
883 WHERE item_code = p_item_code
884 AND orgn_code = p_orgn_code;
885 END IF;
886 END IF;
887
888 IF FND_API.To_Boolean(p_commit) THEN
889 COMMIT WORK;
890 END IF;
891
892 EXCEPTION
893
894 WHEN Null_Delete_Option_Error THEN
895 x_return_status := 'E';
896 x_oracle_error := APP_EXCEPTION.Get_Code;
897 FND_MESSAGE.SET_NAME('GR',
898 'GR_NULL_VALUE');
899 FND_MESSAGE.SET_TOKEN('CODE',
900 l_msg_token,
901 FALSE);
902 IF FND_API.To_Boolean(p_called_by_form) THEN
903 APP_EXCEPTION.Raise_Exception;
904 ELSE
905 x_msg_data := FND_MESSAGE.Get;
906 END IF;
907
908 WHEN OTHERS THEN
909 ROLLBACK TO SAVEPOINT Delete_Rows;
910 x_return_status := 'U';
911 x_oracle_error := APP_EXCEPTION.Get_Code;
912 l_msg_data := APP_EXCEPTION.Get_Text;
913 FND_MESSAGE.SET_NAME('GR',
914 'GR_UNEXPECTED_ERROR');
915 FND_MESSAGE.SET_TOKEN('TEXT',
916 l_msg_token,
917 FALSE);
918 IF FND_API.To_Boolean(p_called_by_form) THEN
919 APP_EXCEPTION.Raise_Exception;
920 ELSE
921 x_msg_data := FND_MESSAGE.Get;
922 END IF;
923
924 END Delete_Rows;
925
926 PROCEDURE Check_Foreign_Keys
927 (p_item_code IN VARCHAR2,
928 p_language IN VARCHAR2,
929 p_orgn_code IN VARCHAR2,
930 p_synonym_sequence_number IN NUMBER,
931 p_source_lang IN VARCHAR2,
932 p_item_other_name IN VARCHAR2,
936 IS
933 x_return_status OUT NOCOPY VARCHAR2,
934 x_oracle_error OUT NOCOPY NUMBER,
935 x_msg_data OUT NOCOPY VARCHAR2)
937
938 /* Alpha Variables */
939
940 L_RETURN_STATUS VARCHAR2(1) := 'S';
941 L_MSG_DATA VARCHAR2(2000);
942 L_MSG_TOKEN VARCHAR2(100);
943 L_LANGUAGE_CODE VARCHAR2(4);
944 L_ROWID VARCHAR2(18);
945 L_KEY_EXISTS VARCHAR2(1);
946
947 /* Number Variables */
948
949 L_ORACLE_ERROR NUMBER;
950
951 /* Error Definitions */
952
953 ROW_MISSING_ERROR EXCEPTION;
954
955 /* Define the cursors */
956
957 /* Language Codes */
958
959 CURSOR c_get_language
960 IS
961 SELECT lng.language_code
962 FROM fnd_languages lng
963 WHERE lng.language_code = l_language_code;
964 LangRecord c_get_language%ROWTYPE;
965
966 /* Organisation Codes */
967
968 CURSOR c_get_orgn
969 IS
970 SELECT om.orgn_code
971 FROM sy_orgn_mst om
972 WHERE orgn_code = p_orgn_code;
973 OrgnRecord c_get_orgn%ROWTYPE;
974
975 BEGIN
976
977 /* Initialization Routine */
978
979 SAVEPOINT Check_Foreign_Keys;
980 x_return_status := 'S';
981 x_oracle_error := 0;
982 x_msg_data := NULL;
983 l_msg_token := p_item_code || ' ' || p_orgn_code || ' ' || p_language;
984
985 /* Check the item code */
986
987 GR_ITEM_GENERAL_PKG.Check_Primary_Key
988 (p_item_code,
989 'F',
990 l_rowid,
991 l_key_exists);
992 IF NOT FND_API.To_Boolean(l_key_exists) THEN
993 x_return_status := 'E';
994 FND_MESSAGE.SET_NAME('GR',
995 'GR_RECORD_NOT_FOUND');
996 FND_MESSAGE.SET_TOKEN('CODE',
997 p_item_code,
998 FALSE);
999 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1000 END IF;
1001
1002 /* Check the organization code */
1003
1004 OPEN c_get_orgn;
1005 FETCH c_get_orgn INTO OrgnRecord;
1006 IF c_get_orgn%NOTFOUND THEN
1007 CLOSE c_get_orgn;
1008 l_msg_token := p_orgn_code;
1009 RAISE Row_Missing_Error;
1010 END IF;
1011 CLOSE c_get_orgn;
1012
1013 /* Check the language codes */
1014
1015 l_language_code := p_language;
1016 OPEN c_get_language;
1017 FETCH c_get_language INTO LangRecord;
1018 IF c_get_language%NOTFOUND THEN
1019 CLOSE c_get_language;
1020 l_msg_token := l_language_code;
1021 RAISE Row_Missing_Error;
1022 END IF;
1023 CLOSE c_get_language;
1024
1025 l_language_code := p_source_lang;
1026 OPEN c_get_language;
1027 FETCH c_get_language INTO LangRecord;
1028 IF c_get_language%NOTFOUND THEN
1029 CLOSE c_get_language;
1030 l_msg_token := l_language_code;
1031 RAISE Row_Missing_Error;
1032 END IF;
1033 CLOSE c_get_language;
1034
1035 EXCEPTION
1036
1037 WHEN Row_Missing_Error THEN
1038 ROLLBACK TO SAVEPOINT Delete_Row;
1039 x_return_status := 'E';
1040 x_oracle_error := APP_EXCEPTION.Get_Code;
1041 FND_MESSAGE.SET_NAME('GR',
1042 'GR_RECORD_NOT_FOUND');
1043 FND_MESSAGE.SET_TOKEN('CODE',
1044 l_msg_token,
1045 FALSE);
1046 x_msg_data := FND_MESSAGE.Get;
1047
1048 WHEN OTHERS THEN
1049 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
1050 x_return_status := 'U';
1051 x_oracle_error := APP_EXCEPTION.Get_Code;
1052 l_msg_data := APP_EXCEPTION.Get_Text;
1053 FND_MESSAGE.SET_NAME('GR',
1054 'GR_UNEXPECTED_ERROR');
1055 FND_MESSAGE.SET_TOKEN('TEXT',
1056 l_msg_token,
1057 FALSE);
1058 x_msg_data := FND_MESSAGE.Get;
1059
1060 END Check_Foreign_Keys;
1061
1062 PROCEDURE Check_Integrity
1063 (p_called_by_form IN VARCHAR2,
1064 p_item_code IN VARCHAR2,
1065 p_language IN VARCHAR2,
1066 p_orgn_code IN VARCHAR2,
1067 p_synonym_sequence_number IN NUMBER,
1068 p_source_lang IN VARCHAR2,
1069 p_item_other_name IN VARCHAR2,
1070 x_return_status OUT NOCOPY VARCHAR2,
1071 x_oracle_error OUT NOCOPY NUMBER,
1072 x_msg_data OUT NOCOPY VARCHAR2)
1073 IS
1074
1075 /* Alpha Variables */
1076
1077 L_RETURN_STATUS VARCHAR2(1) := 'S';
1078 L_MSG_DATA VARCHAR2(2000);
1079 L_CODE_BLOCK VARCHAR2(100);
1080
1081 /* Number Variables */
1082
1083 L_ORACLE_ERROR NUMBER;
1084 L_RECORD_COUNT NUMBER;
1085
1086 /* Exceptions */
1087
1088 INSTALLED_LANGUAGE_ERROR EXCEPTION;
1089
1090
1091 /* Define the Cursors */
1092
1093 CURSOR c_get_language_code
1094 IS
1095 SELECT lng.installed_flag
1096 FROM fnd_languages lng
1097 WHERE lng.language_code = p_language
1098 AND lng.installed_flag IN ('B', 'I');
1099 LangRecord c_get_language_code%ROWTYPE;
1100
1101 BEGIN
1102
1103 /* Initialization Routine */
1104
1108 x_msg_data := NULL;
1105 SAVEPOINT Check_Integrity;
1106 x_return_status := 'S';
1107 x_oracle_error := 0;
1109
1110 /* Check the language isn't base or installed */
1111
1112 OPEN c_get_language_code;
1113 FETCH c_get_language_code INTO LangRecord;
1114 IF c_get_language_code%FOUND THEN
1115 CLOSE c_get_language_code;
1116 RAISE Installed_Language_Error;
1117 END IF;
1118 CLOSE c_get_language_code;
1119
1120 EXCEPTION
1121
1122 WHEN Installed_Language_Error THEN
1123 ROLLBACK TO SAVEPOINT Check_Integrity;
1124 x_return_status := 'E';
1125 FND_MESSAGE.SET_NAME('GR',
1126 'GR_INSTALLED_LANG');
1127 FND_MESSAGE.SET_TOKEN('CODE',
1128 p_language,
1129 FALSE);
1130 IF FND_API.To_Boolean(p_called_by_form) THEN
1131 APP_EXCEPTION.Raise_Exception;
1132 ELSE
1133 x_msg_data := FND_MESSAGE.Get;
1134 END IF;
1135
1136 WHEN OTHERS THEN
1137 ROLLBACK TO SAVEPOINT Check_Integrity;
1138 x_return_status := 'U';
1139 x_oracle_error := APP_EXCEPTION.Get_Code;
1140 l_msg_data := APP_EXCEPTION.Get_Text;
1141 FND_MESSAGE.SET_NAME('GR',
1142 'GR_UNEXPECTED_ERROR');
1143 FND_MESSAGE.SET_TOKEN('TEXT',
1144 l_msg_data,
1145 FALSE);
1146 IF FND_API.To_Boolean(p_called_by_form) THEN
1147 APP_EXCEPTION.Raise_Exception;
1148 ELSE
1149 x_msg_data := FND_MESSAGE.Get;
1150 END IF;
1151
1152 END Check_Integrity;
1153
1154 PROCEDURE Check_Primary_Key
1155 /* p_item_code is the item code to check.
1156 ** p_orgn_code is the label code to check.
1157 ** p_language is the language code part of the key.
1158 ** p_synonym_sequence_number is the sequence number.
1159 ** p_called_by_form is 'T' if called by a form or 'F' if not.
1160 ** x_rowid is the row id of the record if found.
1161 ** x_key_exists is 'T' is the record is found, 'F' if not.
1162 */
1163 (p_item_code IN VARCHAR2,
1164 p_orgn_code IN VARCHAR2,
1165 p_language IN VARCHAR2,
1166 p_synonym_sequence_number IN NUMBER,
1167 p_called_by_form IN VARCHAR2,
1168 x_rowid OUT NOCOPY VARCHAR2,
1169 x_key_exists OUT NOCOPY VARCHAR2)
1170 IS
1171 /* Alphanumeric variables */
1172
1173 L_MSG_DATA VARCHAR2(80);
1174
1175 /* Declare any variables and the cursor */
1176
1177
1178 CURSOR c_get_other_name_tl_rowid
1179 IS
1180 SELECT ion.rowid
1181 FROM gr_other_names_tl ion
1182 WHERE ion.item_code = p_item_code
1183 AND ion.orgn_code = p_orgn_code
1184 AND ion.language = p_language
1185 AND ion.synonym_sequence_number = p_synonym_sequence_number;
1186 OtherNameTLRecord c_get_other_name_tl_rowid%ROWTYPE;
1187
1188 BEGIN
1189
1190 l_msg_data := p_item_code || ' ' || p_orgn_code || ' ' || p_language;
1191
1192 x_key_exists := 'F';
1193 OPEN c_get_other_name_tl_rowid;
1194 FETCH c_get_other_name_tl_rowid INTO OtherNameTLRecord;
1195 IF c_get_other_name_tl_rowid%FOUND THEN
1196 x_key_exists := 'T';
1197 x_rowid := OtherNameTLRecord.rowid;
1198 ELSE
1199 x_key_exists := 'F';
1200 END IF;
1201 CLOSE c_get_other_name_tl_rowid;
1202
1203 EXCEPTION
1204
1205 WHEN Others THEN
1206 l_msg_data := APP_EXCEPTION.Get_Text;
1207 FND_MESSAGE.SET_NAME('GR',
1208 'GR_UNEXPECTED_ERROR');
1209 FND_MESSAGE.SET_TOKEN('TEXT',
1210 l_msg_data,
1211 FALSE);
1212 IF FND_API.To_Boolean(p_called_by_form) THEN
1213 APP_EXCEPTION.Raise_Exception;
1214 END IF;
1215
1216 END Check_Primary_Key;
1217
1218 /* 21-Jan-2002 Mercy Thomas BUG 2190024 - Added procedure NEW_LANGUAGE
1219 to be called from GRNLINS.sql. Generated from tltblgen. */
1220
1221 PROCEDURE NEW_LANGUAGE IS
1222 begin
1223 delete from GR_OTHER_NAMES_TL T
1224 where not exists
1225 (select NULL
1226 from GR_ITEM_GENERAL B,
1227 SY_ORGN_MST S
1228 where B.ITEM_CODE = T.ITEM_CODE
1229 and S.ORGN_CODE = T.ORGN_CODE
1230 );
1231
1232 /* Redefault translations from the source language */
1233
1234 update gr_other_names_tl t set (
1235 item_other_name ) =
1236 ( select
1237 B.ITEM_OTHER_NAME
1238 from GR_OTHER_NAMES_TL B
1239 where B.ITEM_CODE = T.ITEM_CODE
1240 and B.ORGN_CODE = T.ORGN_CODE
1241 and B.LANGUAGE = T.SOURCE_LANG
1242 and B.SYNONYM_SEQUENCE_NUMBER = T.SYNONYM_SEQUENCE_NUMBER)
1243 where (
1244 T.ITEM_CODE,
1245 T.ORGN_CODE,
1246 T.LANGUAGE
1247 ) in (select
1248 SUBT.ITEM_CODE,
1249 SUBT.ORGN_CODE,
1250 SUBT.LANGUAGE
1251 from GR_OTHER_NAMES_TL SUBB, GR_OTHER_NAMES_TL SUBT
1252 where SUBB.ITEM_CODE = SUBT.ITEM_CODE
1253 and SUBB.ORGN_CODE = SUBT.ORGN_CODE
1254 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1255 and (SUBB.ITEM_OTHER_NAME <> SUBT.ITEM_OTHER_NAME
1256 or (SUBB.ITEM_OTHER_NAME is null and SUBT.ITEM_OTHER_NAME is not null)
1257 or (SUBB.ITEM_OTHER_NAME is not null and SUBT.ITEM_OTHER_NAME is null)
1258 ));
1262 ORGN_CODE,
1259
1260 insert into GR_OTHER_NAMES_TL (
1261 ITEM_CODE,
1263 SYNONYM_SEQUENCE_NUMBER,
1264 ITEM_OTHER_NAME,
1265 CREATED_BY,
1266 CREATION_DATE,
1267 LAST_UPDATED_BY,
1268 LAST_UPDATE_DATE,
1269 LAST_UPDATE_LOGIN,
1270 LANGUAGE,
1271 SOURCE_LANG
1272 ) select
1273 B.ITEM_CODE,
1274 B.ORGN_CODE,
1275 B.SYNONYM_SEQUENCE_NUMBER,
1276 B.ITEM_OTHER_NAME,
1277 B.CREATED_BY,
1278 B.CREATION_DATE,
1279 B.LAST_UPDATED_BY,
1280 B.LAST_UPDATE_DATE,
1281 B.LAST_UPDATE_LOGIN,
1282 L.LANGUAGE_CODE,
1283 B.SOURCE_LANG
1284 from GR_OTHER_NAMES_TL B, FND_LANGUAGES L
1285 where L.INSTALLED_FLAG in ('I', 'B')
1286 and B.LANGUAGE = userenv('LANG')
1287 and not exists
1288 (select NULL
1289 from GR_OTHER_NAMES_TL T
1290 where T.ITEM_CODE = B.ITEM_CODE
1291 and T.ORGN_CODE = B.ORGN_CODE
1292 and T.LANGUAGE = L.LANGUAGE_CODE);
1293 end NEW_LANGUAGE;
1294
1295 END GR_OTHER_NAMES_TL_PKG;