[Home] [Help]
PACKAGE BODY: APPS.GR_GENERIC_ML_NAME_TL_PKG
Source
1 PACKAGE BODY GR_GENERIC_ML_NAME_TL_PKG AS
2 /*$Header: GRHIGITB.pls 120.0 2005/05/26 14:24:42 appldev noship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_item_code IN VARCHAR2,
7 p_item_no IN VARCHAR2,
8 p_language IN VARCHAR2,
9 p_label_code IN VARCHAR2,
10 p_source_lang IN VARCHAR2,
11 p_name_description IN VARCHAR2,
12 p_attribute_category IN VARCHAR2,
13 p_attribute1 IN VARCHAR2,
14 p_attribute2 IN VARCHAR2,
15 p_attribute3 IN VARCHAR2,
16 p_attribute4 IN VARCHAR2,
17 p_attribute5 IN VARCHAR2,
18 p_attribute6 IN VARCHAR2,
19 p_attribute7 IN VARCHAR2,
20 p_attribute8 IN VARCHAR2,
21 p_attribute9 IN VARCHAR2,
22 p_attribute10 IN VARCHAR2,
23 p_attribute11 IN VARCHAR2,
24 p_attribute12 IN VARCHAR2,
25 p_attribute13 IN VARCHAR2,
26 p_attribute14 IN VARCHAR2,
27 p_attribute15 IN VARCHAR2,
28 p_attribute16 IN VARCHAR2,
29 p_attribute17 IN VARCHAR2,
30 p_attribute18 IN VARCHAR2,
31 p_attribute19 IN VARCHAR2,
32 p_attribute20 IN VARCHAR2,
33 p_attribute21 IN VARCHAR2,
34 p_attribute22 IN VARCHAR2,
35 p_attribute23 IN VARCHAR2,
36 p_attribute24 IN VARCHAR2,
37 p_attribute25 IN VARCHAR2,
38 p_attribute26 IN VARCHAR2,
39 p_attribute27 IN VARCHAR2,
40 p_attribute28 IN VARCHAR2,
41 p_attribute29 IN VARCHAR2,
42 p_attribute30 IN VARCHAR2,
43 p_created_by IN NUMBER,
44 p_creation_date IN DATE,
45 p_last_updated_by IN NUMBER,
46 p_last_update_date IN DATE,
47 p_last_update_login IN NUMBER,
48 x_rowid OUT NOCOPY VARCHAR2,
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_oracle_error OUT NOCOPY NUMBER,
51 x_msg_data OUT NOCOPY VARCHAR2)
52 IS
53 /* Alpha Variables */
54
55 L_RETURN_STATUS VARCHAR2(1) := 'S';
56 L_KEY_EXISTS VARCHAR2(1);
57 L_MSG_DATA VARCHAR2(2000);
58 L_ROWID VARCHAR2(18);
59 L_MSG_TOKEN VARCHAR2(100);
60
61 /* Number Variables */
62
63 L_ORACLE_ERROR NUMBER;
64 /* Exceptions */
65
66 FOREIGN_KEY_ERROR EXCEPTION;
67 ITEM_EXISTS_ERROR EXCEPTION;
68 ROW_MISSING_ERROR EXCEPTION;
69
70 /* Declare cursors */
71
72
73 BEGIN
74
75 /* Initialization Routine */
76
77 SAVEPOINT Insert_Row;
78 x_return_status := 'S';
79 x_oracle_error := 0;
80 x_msg_data := NULL;
81
82 /* Now call the check foreign key procedure */
83
84 Check_Foreign_Keys
85 (p_item_code,
86 p_item_no,
87 p_language,
88 p_label_code,
89 p_source_lang,
90 p_name_description,
91 p_attribute_category,
92 p_attribute1,
93 p_attribute2,
94 p_attribute3,
95 p_attribute4,
96 p_attribute5,
97 p_attribute6,
98 p_attribute7,
99 p_attribute8,
100 p_attribute9,
101 p_attribute10,
102 p_attribute11,
103 p_attribute12,
104 p_attribute13,
105 p_attribute14,
106 p_attribute15,
107 p_attribute16,
108 p_attribute17,
109 p_attribute18,
110 p_attribute19,
111 p_attribute20,
112 p_attribute21,
113 p_attribute22,
114 p_attribute23,
115 p_attribute24,
116 p_attribute25,
117 p_attribute26,
118 p_attribute27,
119 p_attribute28,
120 p_attribute29,
121 p_attribute30,
122 l_return_status,
123 l_oracle_error,
124 l_msg_data);
125 IF l_return_status <> 'S' THEN
126 RAISE Foreign_Key_Error;
127 END IF;
128
129 /* Now check the primary key doesn't already exist */
130
131 Check_Primary_Key
132 (p_item_code,
133 p_item_no,
134 p_language,
135 'F',
136 l_rowid,
137 l_key_exists);
138
139 IF FND_API.To_Boolean(l_key_exists) THEN
140 RAISE Item_Exists_Error;
141 END IF;
142
143 INSERT INTO gr_generic_ml_name_tl
144 (item_code,
145 item_no,
146 language,
147 label_code,
148 source_lang,
149 name_description,
150 attribute_category,
151 attribute1,
152 attribute2,
153 attribute3,
154 attribute4,
155 attribute5,
156 attribute6,
157 attribute7,
158 attribute8,
159 attribute9,
160 attribute10,
161 attribute11,
162 attribute12,
163 attribute13,
164 attribute14,
165 attribute15,
166 attribute16,
167 attribute17,
168 attribute18,
169 attribute19,
170 attribute20,
171 attribute21,
172 attribute22,
173 attribute23,
174 attribute24,
175 attribute25,
176 attribute26,
177 attribute27,
178 attribute28,
179 attribute29,
180 attribute30,
181 created_by,
182 creation_date,
183 last_updated_by,
184 last_update_date,
185 last_update_login)
186 VALUES
187 (p_item_code,
188 p_item_no,
189 p_language,
190 p_label_code,
191 p_source_lang,
192 p_name_description,
193 p_attribute_category,
194 p_attribute1,
195 p_attribute2,
196 p_attribute3,
197 p_attribute4,
198 p_attribute5,
199 p_attribute6,
200 p_attribute7,
201 p_attribute8,
202 p_attribute9,
203 p_attribute10,
204 p_attribute11,
205 p_attribute12,
206 p_attribute13,
207 p_attribute14,
208 p_attribute15,
209 p_attribute16,
210 p_attribute17,
211 p_attribute18,
212 p_attribute19,
213 p_attribute20,
214 p_attribute21,
215 p_attribute22,
216 p_attribute23,
217 p_attribute24,
218 p_attribute25,
219 p_attribute26,
220 p_attribute27,
221 p_attribute28,
222 p_attribute29,
223 p_attribute30,
224 p_created_by,
225 p_creation_date,
226 p_last_updated_by,
227 p_last_update_date,
228 p_last_update_login);
229
230 /* Now get the row id of the inserted record */
231
232 Check_Primary_Key
233 (p_item_code,
234 p_item_no,
235 p_language,
236 'F',
237 l_rowid,
238 l_key_exists);
239
240 IF FND_API.To_Boolean(l_key_exists) THEN
241 x_rowid := l_rowid;
242 ELSE
243 RAISE Row_Missing_Error;
244 END IF;
245
246 /* Check the commit flag and if set, then commit the work. */
247
248 IF FND_API.To_Boolean(p_commit) THEN
249 COMMIT WORK;
250 END IF;
251
252 EXCEPTION
253
254 WHEN Foreign_Key_Error THEN
255 ROLLBACK TO SAVEPOINT Insert_Row;
256 x_return_status := l_return_status;
257 x_oracle_error := l_oracle_error;
258 FND_MESSAGE.SET_NAME('GR',
259 'GR_FOREIGN_KEY_ERROR');
260 FND_MESSAGE.SET_TOKEN('TEXT',
261 l_msg_data,
262 FALSE);
263 IF FND_API.To_Boolean(p_called_by_form) THEN
264 APP_EXCEPTION.Raise_Exception;
265 ELSE
266 x_msg_data := FND_MESSAGE.Get;
267 END IF;
268
269 WHEN Item_Exists_Error THEN
270 ROLLBACK TO SAVEPOINT Insert_Row;
271 l_msg_token := p_item_code || ' ' || p_item_no || ' ' || p_language;
272 x_return_status := 'E';
273 x_oracle_error := APP_EXCEPTION.Get_Code;
274 FND_MESSAGE.SET_NAME('GR',
275 'GR_RECORD_EXISTS');
276 FND_MESSAGE.SET_TOKEN('CODE',
277 l_msg_token,
278 FALSE);
279 IF FND_API.To_Boolean(p_called_by_form) THEN
280 APP_EXCEPTION.Raise_Exception;
281 ELSE
282 x_msg_data := FND_MESSAGE.Get;
283 END IF;
284
285 WHEN Row_Missing_Error THEN
286 ROLLBACK TO SAVEPOINT Insert_Row;
287 l_msg_token := p_item_code || ' ' || p_item_no || ' ' || p_language;
288 x_return_status := 'E';
289 x_oracle_error := APP_EXCEPTION.Get_Code;
290 FND_MESSAGE.SET_NAME('GR',
291 'GR_NO_RECORD_INSERTED');
292 FND_MESSAGE.SET_TOKEN('CODE',
293 l_msg_token,
294 FALSE);
295 IF FND_API.To_Boolean(p_called_by_form) THEN
296 APP_EXCEPTION.Raise_Exception;
297 ELSE
298 x_msg_data := FND_MESSAGE.Get;
299 END IF;
300
301 WHEN OTHERS THEN
302 ROLLBACK TO SAVEPOINT Insert_Row;
303 l_msg_token := p_item_code || ' ' || p_item_no || ' ' || p_language;
304 x_return_status := 'U';
305 x_oracle_error := APP_EXCEPTION.Get_Code;
306 l_msg_data := APP_EXCEPTION.Get_Text;
307 FND_MESSAGE.SET_NAME('GR',
308 'GR_UNEXPECTED_ERROR');
309 FND_MESSAGE.SET_TOKEN('TEXT',
310 l_msg_token,
311 FALSE);
312 IF FND_API.To_Boolean(p_called_by_form) THEN
313 APP_EXCEPTION.Raise_Exception;
314 ELSE
315 x_msg_data := FND_MESSAGE.Get;
316 END IF;
317
318 END Insert_Row;
319
320 PROCEDURE Update_Row
321 (p_commit IN VARCHAR2,
322 p_called_by_form IN VARCHAR2,
323 p_rowid IN VARCHAR2,
324 p_item_code IN VARCHAR2,
325 p_item_no IN VARCHAR2,
326 p_language IN VARCHAR2,
327 p_label_code IN VARCHAR2,
328 p_source_lang IN VARCHAR2,
329 p_name_description IN VARCHAR2,
330 p_attribute_category IN VARCHAR2,
331 p_attribute1 IN VARCHAR2,
332 p_attribute2 IN VARCHAR2,
333 p_attribute3 IN VARCHAR2,
334 p_attribute4 IN VARCHAR2,
335 p_attribute5 IN VARCHAR2,
336 p_attribute6 IN VARCHAR2,
337 p_attribute7 IN VARCHAR2,
338 p_attribute8 IN VARCHAR2,
339 p_attribute9 IN VARCHAR2,
340 p_attribute10 IN VARCHAR2,
341 p_attribute11 IN VARCHAR2,
342 p_attribute12 IN VARCHAR2,
343 p_attribute13 IN VARCHAR2,
344 p_attribute14 IN VARCHAR2,
345 p_attribute15 IN VARCHAR2,
346 p_attribute16 IN VARCHAR2,
347 p_attribute17 IN VARCHAR2,
348 p_attribute18 IN VARCHAR2,
349 p_attribute19 IN VARCHAR2,
350 p_attribute20 IN VARCHAR2,
351 p_attribute21 IN VARCHAR2,
352 p_attribute22 IN VARCHAR2,
353 p_attribute23 IN VARCHAR2,
354 p_attribute24 IN VARCHAR2,
355 p_attribute25 IN VARCHAR2,
356 p_attribute26 IN VARCHAR2,
357 p_attribute27 IN VARCHAR2,
358 p_attribute28 IN VARCHAR2,
359 p_attribute29 IN VARCHAR2,
360 p_attribute30 IN VARCHAR2,
361 p_created_by IN NUMBER,
362 p_creation_date IN DATE,
363 p_last_updated_by IN NUMBER,
364 p_last_update_date IN DATE,
365 p_last_update_login IN NUMBER,
366 x_return_status OUT NOCOPY VARCHAR2,
367 x_oracle_error OUT NOCOPY NUMBER,
368 x_msg_data OUT NOCOPY VARCHAR2)
369 IS
370
371 /* Alpha Variables */
372
373 L_RETURN_STATUS VARCHAR2(1) := 'S';
374 L_MSG_DATA VARCHAR2(2000);
375 L_MSG_TOKEN VARCHAR2(100);
376
377 /* Number Variables */
378
379 L_ORACLE_ERROR NUMBER;
380
381 /* Exceptions */
382
383 FOREIGN_KEY_ERROR EXCEPTION;
384 ROW_MISSING_ERROR EXCEPTION;
385
386 BEGIN
387
388 /* Initialization Routine */
389
390 SAVEPOINT Update_Row;
391 x_return_status := 'S';
392 x_oracle_error := 0;
393 x_msg_data := NULL;
394 l_msg_token := p_item_code || ' ' || p_item_no || ' ' || p_language;
395
396 /* Now call the check foreign key procedure */
397
398 Check_Foreign_Keys
399 (p_item_code,
400 p_item_no,
401 p_language,
402 p_label_code,
403 p_source_lang,
404 p_name_description,
405 p_attribute_category,
406 p_attribute1,
407 p_attribute2,
408 p_attribute3,
409 p_attribute4,
410 p_attribute5,
411 p_attribute6,
412 p_attribute7,
413 p_attribute8,
414 p_attribute9,
415 p_attribute10,
416 p_attribute11,
417 p_attribute12,
418 p_attribute13,
419 p_attribute14,
420 p_attribute15,
421 p_attribute16,
422 p_attribute17,
423 p_attribute18,
424 p_attribute19,
425 p_attribute20,
426 p_attribute21,
427 p_attribute22,
428 p_attribute23,
429 p_attribute24,
430 p_attribute25,
431 p_attribute26,
432 p_attribute27,
433 p_attribute28,
434 p_attribute29,
435 p_attribute30,
436 l_return_status,
437 l_oracle_error,
438 l_msg_data);
439
440 IF l_return_status <> 'S' THEN
441 RAISE Foreign_Key_Error;
442 ELSE
443 UPDATE gr_generic_ml_name_tl
444 SET item_code = p_item_code,
445 item_no = p_item_no,
446 language = p_language,
447 label_code = p_label_code,
448 source_lang = p_source_lang,
449 name_description = p_name_description,
450 attribute_category = p_attribute_category,
451 attribute1 = p_attribute1,
452 attribute2 = p_attribute2,
453 attribute3 = p_attribute3,
454 attribute4 = p_attribute4,
455 attribute5 = p_attribute5,
456 attribute6 = p_attribute6,
457 attribute7 = p_attribute7,
458 attribute8 = p_attribute8,
459 attribute9 = p_attribute9,
460 attribute10 = p_attribute10,
461 attribute11 = p_attribute11,
462 attribute12 = p_attribute12,
463 attribute13 = p_attribute13,
464 attribute14 = p_attribute14,
465 attribute15 = p_attribute15,
466 attribute16 = p_attribute16,
467 attribute17 = p_attribute17,
468 attribute18 = p_attribute18,
469 attribute19 = p_attribute19,
470 attribute20 = p_attribute20,
471 attribute21 = p_attribute11,
472 attribute22 = p_attribute22,
473 attribute23 = p_attribute23,
474 attribute24 = p_attribute24,
475 attribute25 = p_attribute25,
476 attribute26 = p_attribute26,
477 attribute27 = p_attribute27,
478 attribute28 = p_attribute28,
479 attribute29 = p_attribute29,
480 attribute30 = p_attribute30,
481 created_by = p_created_by,
482 creation_date = p_creation_date,
483 last_updated_by = p_last_updated_by,
484 last_update_date = p_last_update_date,
485 last_update_login = p_last_update_login
486 WHERE rowid = p_rowid;
487 IF SQL%NOTFOUND THEN
488 RAISE Row_Missing_Error;
489 END IF;
490 END IF;
491
495 COMMIT WORK;
492 /* Check the commit flag and if set, then commit the work. */
493
494 IF FND_API.To_Boolean(p_commit) THEN
496 END IF;
497
498 EXCEPTION
499
500 WHEN Foreign_Key_Error THEN
501 ROLLBACK TO SAVEPOINT Update_Row;
502 x_return_status := l_return_status;
503 x_oracle_error := l_oracle_error;
504 FND_MESSAGE.SET_NAME('GR',
505 'GR_FOREIGN_KEY_ERROR');
506 FND_MESSAGE.SET_TOKEN('TEXT',
507 l_msg_data,
508 FALSE);
509 IF FND_API.To_Boolean(p_called_by_form) THEN
510 APP_EXCEPTION.Raise_Exception;
511 ELSE
512 x_msg_data := FND_MESSAGE.Get;
513 END IF;
514
515 WHEN Row_Missing_Error THEN
516 ROLLBACK TO SAVEPOINT Update_Row;
517 x_return_status := 'E';
518 x_oracle_error := APP_EXCEPTION.Get_Code;
519 FND_MESSAGE.SET_NAME('GR',
520 'GR_NO_RECORD_INSERTED');
521 FND_MESSAGE.SET_TOKEN('CODE',
522 l_msg_token,
523 FALSE);
524 IF FND_API.To_Boolean(p_called_by_form) THEN
525 APP_EXCEPTION.Raise_Exception;
526 ELSE
527 x_msg_data := FND_MESSAGE.Get;
528 END IF;
529
530 WHEN OTHERS THEN
531 ROLLBACK TO SAVEPOINT Update_Row;
532 x_return_status := 'U';
533 x_oracle_error := APP_EXCEPTION.Get_Code;
534 l_msg_data := APP_EXCEPTION.Get_Text;
535 FND_MESSAGE.SET_NAME('GR',
536 'GR_UNEXPECTED_ERROR');
537 FND_MESSAGE.SET_TOKEN('TEXT',
538 l_msg_token,
539 FALSE);
540 IF FND_API.To_Boolean(p_called_by_form) THEN
541 APP_EXCEPTION.Raise_Exception;
542 ELSE
543 x_msg_data := FND_MESSAGE.Get;
544 END IF;
545
546 END Update_Row;
547
548 PROCEDURE Add_Language
549 (p_commit IN VARCHAR2,
550 p_called_by_form IN VARCHAR2,
551 p_item_code IN VARCHAR2,
552 p_item_no IN VARCHAR2,
553 p_language IN VARCHAR2,
554 x_return_status OUT NOCOPY VARCHAR2,
555 x_oracle_error OUT NOCOPY NUMBER,
556 x_msg_data OUT NOCOPY VARCHAR2)
557 IS
558
559 /* Alpha Variables */
560
561 L_RETURN_STATUS VARCHAR2(1) := 'S';
562 L_MSG_DATA VARCHAR2(2000);
563 L_MSG_TOKEN VARCHAR2(100);
564 L_BASE_DESC VARCHAR2(240);
565 L_LABEL_CODE VARCHAR2(5);
566 L_LANGUAGE VARCHAR2(4);
567 L_CREATION_DATE DATE;
568 L_LAST_UPDATE_DATE DATE;
569 L_ATTRIBUTE_CATEGORY VARCHAR2(30);
570 L_ATTRIBUTE1 VARCHAR2(240);
571 L_ATTRIBUTE2 VARCHAR2(240);
572 L_ATTRIBUTE3 VARCHAR2(240);
573 L_ATTRIBUTE4 VARCHAR2(240);
574 L_ATTRIBUTE5 VARCHAR2(240);
575 L_ATTRIBUTE6 VARCHAR2(240);
576 L_ATTRIBUTE7 VARCHAR2(240);
577 L_ATTRIBUTE8 VARCHAR2(240);
578 L_ATTRIBUTE9 VARCHAR2(240);
579 L_ATTRIBUTE10 VARCHAR2(240);
580 L_ATTRIBUTE11 VARCHAR2(240);
581 L_ATTRIBUTE12 VARCHAR2(240);
582 L_ATTRIBUTE13 VARCHAR2(240);
583 L_ATTRIBUTE14 VARCHAR2(240);
584 L_ATTRIBUTE15 VARCHAR2(240);
585 L_ATTRIBUTE16 VARCHAR2(240);
586 L_ATTRIBUTE17 VARCHAR2(240);
587 L_ATTRIBUTE18 VARCHAR2(240);
588 L_ATTRIBUTE19 VARCHAR2(240);
589 L_ATTRIBUTE20 VARCHAR2(240);
590 L_ATTRIBUTE21 VARCHAR2(240);
591 L_ATTRIBUTE22 VARCHAR2(240);
592 L_ATTRIBUTE23 VARCHAR2(240);
593 L_ATTRIBUTE24 VARCHAR2(240);
594 L_ATTRIBUTE25 VARCHAR2(240);
595 L_ATTRIBUTE26 VARCHAR2(240);
596 L_ATTRIBUTE27 VARCHAR2(240);
597 L_ATTRIBUTE28 VARCHAR2(240);
598 L_ATTRIBUTE29 VARCHAR2(240);
599 L_ATTRIBUTE30 VARCHAR2(240);
600
601 /* Number Variables */
602
603 L_ORACLE_ERROR NUMBER;
604 L_CREATED_BY NUMBER;
605 L_LAST_UPDATED_BY NUMBER;
606 L_LAST_UPDATE_LOGIN NUMBER;
607 L_PRINT_SIZE NUMBER;
608
609 /* Exceptions */
610
611 LANGUAGE_MISSING_ERROR EXCEPTION;
612
613
614 /* Cursors */
615
616 CURSOR c_get_descs
617 IS
618 SELECT gin.name_description,
619 gin.label_code,
620 gin.attribute_category,
621 gin.attribute1,
622 gin.attribute2,
623 gin.attribute3,
624 gin.attribute4,
625 gin.attribute5,
626 gin.attribute6,
627 gin.attribute7,
628 gin.attribute8,
629 gin.attribute9,
630 gin.attribute10,
631 gin.attribute11,
632 gin.attribute12,
633 gin.attribute13,
634 gin.attribute14,
635 gin.attribute15,
636 gin.attribute16,
637 gin.attribute17,
638 gin.attribute18,
639 gin.attribute19,
640 gin.attribute20,
641 gin.attribute21,
642 gin.attribute22,
643 gin.attribute23,
644 gin.attribute24,
645 gin.attribute25,
646 gin.attribute26,
647 gin.attribute27,
648 gin.attribute28,
649 gin.attribute29,
650 gin.attribute30,
651 gin.created_by,
652 gin.creation_date,
653 gin.last_updated_by,
654 gin.last_update_date,
658 AND gin.item_no = p_item_no
655 gin.last_update_login
656 FROM gr_generic_ml_name_tl gin
657 WHERE gin.item_code = p_item_code
659 AND gin.language = l_language;
660 GenericDesc c_get_descs%ROWTYPE;
661
662 CURSOR c_get_installed_languages
663 IS
664 SELECT lng.language_code
665 FROM fnd_languages lng
666 WHERE lng.installed_flag IN ('I', 'B');
667 InstLang c_get_installed_languages%ROWTYPE;
668
669
670 BEGIN
671
672 /* Initialization Routine */
673
674 SAVEPOINT Add_Language;
675 x_return_status := 'S';
676 x_oracle_error := 0;
677 x_msg_data := NULL;
678 l_msg_token := p_item_code || ' ' || p_item_no || ' ' || p_language;
679
680 /* Remove translations with no base row */
681
682 delete from GR_GENERIC_ML_NAME_TL T
683 where not exists
684 (select NULL
685 from GR_GENERIC_ITEMS_B B
686 where B.ITEM_CODE = T.ITEM_CODE
687 and B.ITEM_NO = T.ITEM_NO
688 );
689
690 /* Redefault translations from the source language */
691
692 update gr_generic_ml_name_tl t set (
693 name_description ) =
694 ( select
695 B.NAME_DESCRIPTION
696 from GR_GENERIC_ML_NAME_TL B
697 where B.ITEM_CODE = T.ITEM_CODE
698 and B.ITEM_NO = T.ITEM_NO
699 and B.LANGUAGE = T.SOURCE_LANG)
700 where (
701 T.ITEM_CODE,
702 T.ITEM_NO,
703 T.LANGUAGE
704 ) in (select
705 SUBT.ITEM_CODE,
706 SUBT.ITEM_NO,
707 SUBT.LANGUAGE
708 from GR_GENERIC_ML_NAME_TL SUBB, GR_GENERIC_ML_NAME_TL SUBT
709 where SUBB.ITEM_CODE = SUBT.ITEM_CODE
710 and SUBB.ITEM_NO = SUBB.ITEM_NO
711 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
712 and (SUBB.NAME_DESCRIPTION <> SUBT.NAME_DESCRIPTION
713 or (SUBB.NAME_DESCRIPTION is null and SUBT.NAME_DESCRIPTION is not null)
714 or (SUBB.NAME_DESCRIPTION is not null and SUBT.NAME_DESCRIPTION is null)
715 ));
716
717 /* Open the language cursor and get the description entered from the
718 ** user environment variable.
719 */
720 l_language := p_language;
721 OPEN c_get_descs;
722 FETCH c_get_descs INTO GenericDesc;
723 IF c_get_descs%NOTFOUND THEN
724 CLOSE c_get_descs;
725 RAISE Language_Missing_Error;
726 ELSE
727 l_base_desc := GenericDesc.name_description;
728 l_label_code := GenericDesc.label_code;
729 l_attribute_category := GenericDesc.attribute_category;
730 l_attribute1 := GenericDesc.attribute1;
731 l_attribute2 := GenericDesc.attribute2;
732 l_attribute3 := GenericDesc.attribute3;
733 l_attribute4 := GenericDesc.attribute4;
734 l_attribute5 := GenericDesc.attribute5;
735 l_attribute6 := GenericDesc.attribute6;
736 l_attribute7 := GenericDesc.attribute7;
737 l_attribute8 := GenericDesc.attribute8;
738 l_attribute9 := GenericDesc.attribute9;
739 l_attribute10 := GenericDesc.attribute10;
740 l_attribute11 := GenericDesc.attribute11;
741 l_attribute12 := GenericDesc.attribute12;
742 l_attribute13 := GenericDesc.attribute13;
743 l_attribute14 := GenericDesc.attribute14;
744 l_attribute15 := GenericDesc.attribute15;
745 l_attribute16 := GenericDesc.attribute16;
746 l_attribute17 := GenericDesc.attribute17;
747 l_attribute18 := GenericDesc.attribute18;
748 l_attribute19 := GenericDesc.attribute19;
749 l_attribute20 := GenericDesc.attribute20;
750 l_attribute21 := GenericDesc.attribute21;
751 l_attribute22 := GenericDesc.attribute22;
752 l_attribute23 := GenericDesc.attribute23;
753 l_attribute24 := GenericDesc.attribute24;
754 l_attribute25 := GenericDesc.attribute25;
755 l_attribute26 := GenericDesc.attribute26;
756 l_attribute27 := GenericDesc.attribute27;
757 l_attribute28 := GenericDesc.attribute28;
758 l_attribute29 := GenericDesc.attribute29;
759 l_attribute30 := GenericDesc.attribute30;
760 l_created_by := GenericDesc.created_by;
761 l_creation_date := GenericDesc.creation_date;
762 l_last_updated_by := GenericDesc.last_updated_by;
763 l_last_update_date := GenericDesc.last_update_date;
764 l_last_update_login := GenericDesc.last_update_login;
765 CLOSE c_get_descs;
766 END IF;
767
768 /* Read fnd_languages for the installed and base languages.
769 ** For those that are found, read the phrases tl table.
770 ** If there isn't a record in the table for that language then
771 ** insert it and go on to the next.
772 */
773 OPEN c_get_installed_languages;
774 FETCH c_get_installed_languages INTO InstLang;
775 IF c_get_installed_languages%FOUND THEN
776 WHILE c_get_installed_languages%FOUND LOOP
777 IF InstLang.language_code <> p_language THEN
778 l_language := InstLang.language_code;
779 OPEN c_get_descs;
780 FETCH c_get_descs INTO GenericDesc;
781 IF c_get_descs%NOTFOUND THEN
782 CLOSE c_get_descs;
783 INSERT INTO gr_generic_ml_name_tl
784 (item_code,
785 item_no,
786 language,
787 label_code,
788 source_lang,
789 name_description,
790 attribute_category,
794 attribute4,
791 attribute1,
792 attribute2,
793 attribute3,
795 attribute5,
796 attribute6,
797 attribute7,
798 attribute8,
799 attribute9,
800 attribute10,
801 attribute11,
802 attribute12,
803 attribute13,
804 attribute14,
805 attribute15,
806 attribute16,
807 attribute17,
808 attribute18,
809 attribute19,
810 attribute20,
811 attribute21,
812 attribute22,
813 attribute23,
814 attribute24,
815 attribute25,
816 attribute26,
817 attribute27,
818 attribute28,
819 attribute29,
820 attribute30,
821 created_by,
822 creation_date,
823 last_updated_by,
824 last_update_date,
825 last_update_login)
826 VALUES
827 (p_item_code,
828 p_item_no,
829 l_language,
830 l_label_code,
831 p_language,
832 l_base_desc,
833 l_attribute_category,
834 l_attribute1,
835 l_attribute2,
836 l_attribute3,
837 l_attribute4,
838 l_attribute5,
839 l_attribute6,
840 l_attribute7,
841 l_attribute8,
842 l_attribute9,
843 l_attribute10,
844 l_attribute11,
845 l_attribute12,
846 l_attribute13,
847 l_attribute14,
848 l_attribute15,
849 l_attribute16,
850 l_attribute17,
851 l_attribute18,
852 l_attribute19,
853 l_attribute20,
854 l_attribute21,
855 l_attribute22,
856 l_attribute23,
857 l_attribute24,
858 l_attribute25,
859 l_attribute26,
860 l_attribute27,
861 l_attribute28,
862 l_attribute29,
863 l_attribute30,
864 l_created_by,
865 l_creation_date,
866 l_last_updated_by,
867 l_last_update_date,
868 l_last_update_login);
869 ELSE
870 CLOSE c_get_descs;
871 END IF;
872 END IF;
873 FETCH c_get_installed_languages INTO InstLang;
874 END LOOP;
875 END IF;
876 CLOSE c_get_installed_languages;
877
878 IF FND_API.To_Boolean(p_commit) THEN
879 COMMIT WORK;
880 END IF;
881
882 EXCEPTION
883
884 WHEN Language_Missing_Error THEN
885 ROLLBACK TO SAVEPOINT Add_Language;
886 x_return_status := 'E';
887 x_oracle_error := APP_EXCEPTION.Get_Code;
888 FND_MESSAGE.SET_NAME('GR',
889 'GR_RECORD_NOT_FOUND');
890 FND_MESSAGE.SET_TOKEN('CODE',
891 l_msg_token,
892 FALSE);
893 IF FND_API.To_Boolean(p_called_by_form) THEN
894 APP_EXCEPTION.Raise_Exception;
895 ELSE
896 x_msg_data := FND_MESSAGE.Get;
897 END IF;
898
899 WHEN OTHERS THEN
900 ROLLBACK TO SAVEPOINT Add_Language;
901 x_return_status := 'U';
902 x_oracle_error := APP_EXCEPTION.Get_Code;
903 FND_MESSAGE.SET_NAME('GR',
904 'GR_UNEXPECTED_ERROR');
905 FND_MESSAGE.SET_TOKEN('TEXT',
906 l_msg_token,
907 FALSE);
908 IF FND_API.To_Boolean(p_called_by_form) THEN
909 APP_EXCEPTION.Raise_Exception;
910 ELSE
911 x_msg_data := FND_MESSAGE.Get;
912 END IF;
913
914 END Add_Language;
915
916 PROCEDURE Lock_Row
917 (p_commit IN VARCHAR2,
918 p_called_by_form IN VARCHAR2,
919 p_rowid IN VARCHAR2,
920 p_item_code IN VARCHAR2,
921 p_item_no IN VARCHAR2,
922 p_language IN VARCHAR2,
923 p_label_code IN VARCHAR2,
924 p_source_lang IN VARCHAR2,
925 p_name_description IN VARCHAR2,
926 p_attribute_category IN VARCHAR2,
927 p_attribute1 IN VARCHAR2,
928 p_attribute2 IN VARCHAR2,
929 p_attribute3 IN VARCHAR2,
930 p_attribute4 IN VARCHAR2,
931 p_attribute5 IN VARCHAR2,
932 p_attribute6 IN VARCHAR2,
933 p_attribute7 IN VARCHAR2,
934 p_attribute8 IN VARCHAR2,
935 p_attribute9 IN VARCHAR2,
936 p_attribute10 IN VARCHAR2,
937 p_attribute11 IN VARCHAR2,
938 p_attribute12 IN VARCHAR2,
939 p_attribute13 IN VARCHAR2,
940 p_attribute14 IN VARCHAR2,
941 p_attribute15 IN VARCHAR2,
942 p_attribute16 IN VARCHAR2,
943 p_attribute17 IN VARCHAR2,
944 p_attribute18 IN VARCHAR2,
945 p_attribute19 IN VARCHAR2,
946 p_attribute20 IN VARCHAR2,
947 p_attribute21 IN VARCHAR2,
948 p_attribute22 IN VARCHAR2,
949 p_attribute23 IN VARCHAR2,
950 p_attribute24 IN VARCHAR2,
951 p_attribute25 IN VARCHAR2,
952 p_attribute26 IN VARCHAR2,
953 p_attribute27 IN VARCHAR2,
954 p_attribute28 IN VARCHAR2,
955 p_attribute29 IN VARCHAR2,
959 p_last_updated_by IN NUMBER,
956 p_attribute30 IN VARCHAR2,
957 p_created_by IN NUMBER,
958 p_creation_date IN DATE,
960 p_last_update_date IN DATE,
961 p_last_update_login IN NUMBER,
962 x_return_status OUT NOCOPY VARCHAR2,
963 x_oracle_error OUT NOCOPY NUMBER,
964 x_msg_data OUT NOCOPY VARCHAR2)
965 IS
966
967 /* Alpha Variables */
968
969 L_RETURN_STATUS VARCHAR2(1) := 'S';
970 L_MSG_DATA VARCHAR2(2000);
971 L_MSG_TOKEN VARCHAR2(100);
972
973 /* Number Variables */
974
975 L_ORACLE_ERROR NUMBER;
976
977 /* Exceptions */
978
979 NO_DATA_FOUND_ERROR EXCEPTION;
980 RECORD_CHANGED_ERROR EXCEPTION;
981
982 /* Define the cursors */
983
984 CURSOR c_lock_generics_tl
985 IS
986 SELECT last_update_date
987 FROM gr_generic_ml_name_tl
988 WHERE rowid = p_rowid
989 FOR UPDATE NOWAIT;
990 LockGenericRcd c_lock_generics_tl%ROWTYPE;
991
992 BEGIN
993
994 /* Initialization Routine */
995
996 SAVEPOINT Lock_Row;
997 x_return_status := 'S';
998 x_oracle_error := 0;
999 x_msg_data := NULL;
1000 l_msg_token := p_item_code || ' ' || p_item_no || ' ' || p_language;
1001
1002 /* Now lock the record */
1003
1004 OPEN c_lock_generics_tl;
1005 FETCH c_lock_generics_tl INTO LockGenericRcd;
1006 IF c_lock_generics_tl%NOTFOUND THEN
1007 CLOSE c_lock_generics_tl;
1008 RAISE No_Data_Found_Error;
1009 END IF;
1010 CLOSE c_lock_generics_tl;
1011
1012 IF LockGenericRcd.last_update_date <> p_last_update_date THEN
1013 RAISE RECORD_CHANGED_ERROR;
1014 END IF;
1015
1016 IF FND_API.To_Boolean(p_commit) THEN
1017 COMMIT WORK;
1018 END IF;
1019
1020 EXCEPTION
1021
1022 WHEN No_Data_Found_Error THEN
1023 ROLLBACK TO SAVEPOINT Lock_Row;
1024 x_return_status := 'E';
1025 FND_MESSAGE.SET_NAME('GR',
1026 'GR_RECORD_NOT_FOUND');
1027 FND_MESSAGE.SET_TOKEN('CODE',
1028 l_msg_token,
1029 FALSE);
1030 IF FND_API.To_Boolean(p_called_by_form) THEN
1031 APP_EXCEPTION.Raise_Exception;
1032 ELSE
1033 x_msg_data := FND_MESSAGE.Get;
1034 END IF;
1035
1036 WHEN RECORD_CHANGED_ERROR THEN
1037 ROLLBACK TO SAVEPOINT Lock_Row;
1038 X_return_status := 'E';
1039 FND_MESSAGE.SET_NAME('FND',
1040 'FORM_RECORD_CHANGED');
1041 IF FND_API.To_Boolean(p_called_by_form) THEN
1042 APP_EXCEPTION.Raise_Exception;
1043 ELSE
1044 x_msg_data := FND_MESSAGE.Get;
1045 END IF;
1046 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
1047 ROLLBACK TO SAVEPOINT Lock_Row;
1048 x_return_status := 'L';
1049 x_oracle_error := APP_EXCEPTION.Get_Code;
1050 IF NOT (FND_API.To_Boolean(p_called_by_form)) THEN
1051 FND_MESSAGE.SET_NAME('GR',
1052 'GR_ROW_IS_LOCKED');
1053 x_msg_data := FND_MESSAGE.Get;
1054 END IF;
1055
1056
1057 WHEN OTHERS THEN
1058 ROLLBACK TO SAVEPOINT Lock_Row;
1059 x_return_status := 'U';
1060 x_oracle_error := APP_EXCEPTION.Get_Code;
1061 l_msg_data := APP_EXCEPTION.Get_Text;
1062 FND_MESSAGE.SET_NAME('GR',
1063 'GR_UNEXPECTED_ERROR');
1064 FND_MESSAGE.SET_TOKEN('TEXT',
1065 l_msg_token,
1066 FALSE);
1067 IF FND_API.To_Boolean(p_called_by_form) THEN
1068 APP_EXCEPTION.Raise_Exception;
1069 ELSE
1070 x_msg_data := FND_MESSAGE.Get;
1071 END IF;
1072
1073 END Lock_Row;
1074
1075 PROCEDURE Delete_Row
1076 (p_commit IN VARCHAR2,
1077 p_called_by_form IN VARCHAR2,
1078 p_rowid IN VARCHAR2,
1079 p_item_code IN VARCHAR2,
1080 p_item_no IN VARCHAR2,
1081 p_language IN VARCHAR2,
1082 p_label_code IN VARCHAR2,
1083 p_source_lang IN VARCHAR2,
1084 p_name_description IN VARCHAR2,
1085 p_attribute_category IN VARCHAR2,
1086 p_attribute1 IN VARCHAR2,
1087 p_attribute2 IN VARCHAR2,
1088 p_attribute3 IN VARCHAR2,
1089 p_attribute4 IN VARCHAR2,
1090 p_attribute5 IN VARCHAR2,
1091 p_attribute6 IN VARCHAR2,
1092 p_attribute7 IN VARCHAR2,
1093 p_attribute8 IN VARCHAR2,
1094 p_attribute9 IN VARCHAR2,
1095 p_attribute10 IN VARCHAR2,
1096 p_attribute11 IN VARCHAR2,
1097 p_attribute12 IN VARCHAR2,
1098 p_attribute13 IN VARCHAR2,
1099 p_attribute14 IN VARCHAR2,
1100 p_attribute15 IN VARCHAR2,
1101 p_attribute16 IN VARCHAR2,
1102 p_attribute17 IN VARCHAR2,
1103 p_attribute18 IN VARCHAR2,
1104 p_attribute19 IN VARCHAR2,
1105 p_attribute20 IN VARCHAR2,
1106 p_attribute21 IN VARCHAR2,
1107 p_attribute22 IN VARCHAR2,
1108 p_attribute23 IN VARCHAR2,
1109 p_attribute24 IN VARCHAR2,
1110 p_attribute25 IN VARCHAR2,
1111 p_attribute26 IN VARCHAR2,
1112 p_attribute27 IN VARCHAR2,
1116 p_created_by IN NUMBER,
1113 p_attribute28 IN VARCHAR2,
1114 p_attribute29 IN VARCHAR2,
1115 p_attribute30 IN VARCHAR2,
1117 p_creation_date IN DATE,
1118 p_last_updated_by IN NUMBER,
1119 p_last_update_date IN DATE,
1120 p_last_update_login IN NUMBER,
1121 x_return_status OUT NOCOPY VARCHAR2,
1122 x_oracle_error OUT NOCOPY NUMBER,
1123 x_msg_data OUT NOCOPY VARCHAR2)
1124 IS
1125
1126 /* Alpha Variables */
1127
1128 L_RETURN_STATUS VARCHAR2(1) := 'S';
1129 L_MSG_DATA VARCHAR2(2000);
1130 L_MSG_TOKEN VARCHAR2(100);
1131 L_CALLED_BY_FORM VARCHAR2(1);
1132
1133 /* Number Variables */
1134
1135 L_ORACLE_ERROR NUMBER;
1136
1137 /* Exceptions */
1138
1139 CHECK_INTEGRITY_ERROR EXCEPTION;
1140 ROW_MISSING_ERROR EXCEPTION;
1141 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
1142
1143 /* Define the cursor */
1144
1145 BEGIN
1146
1147 /* Initialization Routine */
1148
1149 SAVEPOINT Delete_Row;
1150 x_return_status := 'S';
1151 l_called_by_form := 'F';
1152 x_oracle_error := 0;
1153 x_msg_data := NULL;
1154 l_msg_token := p_item_code || ' ' || p_item_no || ' ' || p_language;
1155
1156 /* Now call the check integrity procedure */
1157
1158 Check_Integrity
1159 (l_called_by_form,
1160 p_item_code,
1161 p_item_no,
1162 p_language,
1163 p_label_code,
1164 p_source_lang,
1165 p_name_description,
1166 p_attribute_category,
1167 p_attribute1,
1168 p_attribute2,
1169 p_attribute3,
1170 p_attribute4,
1171 p_attribute5,
1172 p_attribute6,
1173 p_attribute7,
1174 p_attribute8,
1175 p_attribute9,
1176 p_attribute10,
1177 p_attribute11,
1178 p_attribute12,
1179 p_attribute13,
1180 p_attribute14,
1181 p_attribute15,
1182 p_attribute16,
1183 p_attribute17,
1184 p_attribute18,
1185 p_attribute19,
1186 p_attribute20,
1187 p_attribute21,
1188 p_attribute22,
1189 p_attribute23,
1190 p_attribute24,
1191 p_attribute25,
1192 p_attribute26,
1193 p_attribute27,
1194 p_attribute28,
1195 p_attribute29,
1196 p_attribute30,
1197 l_return_status,
1198 l_oracle_error,
1199 l_msg_data);
1200
1201 IF l_return_status <> 'S' THEN
1202 RAISE Check_Integrity_Error;
1203 END IF;
1204
1205 DELETE FROM gr_generic_ml_name_tl
1206 WHERE rowid = p_rowid;
1207
1208 /* Check the commit flag and if set, then commit the work. */
1209
1210 IF FND_API.TO_Boolean(p_commit) THEN
1211 COMMIT WORK;
1212 END IF;
1213
1214 EXCEPTION
1215
1216 WHEN Check_Integrity_Error THEN
1217 ROLLBACK TO SAVEPOINT Delete_Row;
1218 x_return_status := l_return_status;
1219 x_oracle_error := l_oracle_error;
1220 IF FND_API.To_Boolean(p_called_by_form) THEN
1221 APP_EXCEPTION.Raise_Exception;
1222 ELSE
1223 x_msg_data := FND_MESSAGE.Get;
1224 END IF;
1225
1226 WHEN Row_Missing_Error THEN
1227 ROLLBACK TO SAVEPOINT Delete_Row;
1228 x_return_status := 'E';
1229 x_oracle_error := APP_EXCEPTION.Get_Code;
1230 FND_MESSAGE.SET_NAME('GR',
1231 'GR_RECORD_NOT_FOUND');
1232 FND_MESSAGE.SET_TOKEN('CODE',
1233 l_msg_token,
1234 FALSE);
1235 IF FND_API.To_Boolean(p_called_by_form) THEN
1236 APP_EXCEPTION.Raise_Exception;
1237 ELSE
1238 x_msg_data := FND_MESSAGE.Get;
1239 END IF;
1240
1241 WHEN OTHERS THEN
1242 ROLLBACK TO SAVEPOINT Delete_Row;
1243 x_return_status := 'U';
1244 x_oracle_error := APP_EXCEPTION.Get_Code;
1245 l_msg_data := APP_EXCEPTION.Get_Text;
1246 l_msg_data := APP_EXCEPTION.Get_Text;
1247 FND_MESSAGE.SET_NAME('GR',
1248 'GR_UNEXPECTED_ERROR');
1249 FND_MESSAGE.SET_TOKEN('TEXT',
1250 l_msg_token,
1251 FALSE);
1252 IF FND_API.To_Boolean(p_called_by_form) THEN
1253 APP_EXCEPTION.Raise_Exception;
1254 ELSE
1255 x_msg_data := FND_MESSAGE.Get;
1256 END IF;
1257
1258 END Delete_Row;
1259
1260 PROCEDURE Delete_Rows
1261 (p_commit IN VARCHAR2,
1262 p_called_by_form IN VARCHAR2,
1263 p_delete_option IN VARCHAR2,
1264 p_item_code IN VARCHAR2,
1265 p_item_no IN VARCHAR2,
1266 x_return_status OUT NOCOPY VARCHAR2,
1267 x_oracle_error OUT NOCOPY NUMBER,
1268 x_msg_data OUT NOCOPY VARCHAR2)
1269 IS
1270
1271 /* Alpha Variables */
1272
1273 L_RETURN_STATUS VARCHAR2(1) := 'S';
1274 L_MSG_DATA VARCHAR2(2000);
1275 L_MSG_TOKEN VARCHAR2(100);
1276
1277 /* Number Variables */
1278
1279 L_ORACLE_ERROR NUMBER;
1280
1281 /* Excceptions */
1282
1286
1283 NULL_DELETE_OPTION_ERROR EXCEPTION;
1284
1285 /* Define the cursors */
1287 BEGIN
1288
1289 /* Initialization Routine */
1290
1291 SAVEPOINT Delete_Rows;
1292 x_return_status := 'S';
1293 x_oracle_error := 0;
1294 x_msg_data := NULL;
1295 l_msg_token := p_item_code || ' ' || p_item_no;
1296
1297 /*
1298 ** p_delete_option has one of three values.
1299 ** 'G' - Delete all rows for the generic item in p_item_code.
1300 ** 'I' - Delete all rows for the specified item in p_item_no.
1301 ** 'B' - Delete all rows for the generic and specified item.
1302 */
1303 IF p_delete_option = 'G' THEN
1304 IF p_item_code IS NULL THEN
1305 l_msg_token := 'Item Code';
1306 RAISE Null_Delete_Option_Error;
1307 ELSE
1308 l_msg_token := p_item_code;
1309
1310 DELETE FROM gr_generic_ml_name_tl
1311 WHERE item_code = p_item_code;
1312 END IF;
1313 ELSIF p_delete_option = 'I' THEN
1314 IF p_item_no IS NULL THEN
1315 l_msg_token := 'Item No.';
1316 RAISE Null_Delete_Option_Error;
1317 ELSE
1318 l_msg_token := p_item_no;
1319
1320 DELETE FROM gr_generic_ml_name_tl
1321 WHERE item_no = p_item_no;
1322 END IF;
1323 ELSIF p_delete_option = 'B' THEN
1324 IF p_item_code IS NULL OR
1325 p_item_no IS NULL THEN
1326 l_msg_token := 'Item Code / Item No.';
1327 RAISE Null_Delete_Option_Error;
1328 ELSE
1329 l_msg_token := p_item_code || ' ' || p_item_no;
1330
1331 DELETE FROM gr_generic_ml_name_tl
1332 WHERE item_code = p_item_code
1333 AND item_no = p_item_no;
1334 END IF;
1335 END IF;
1336
1337 IF FND_API.To_Boolean(p_commit) THEN
1338 COMMIT WORK;
1339 END IF;
1340
1341 EXCEPTION
1342
1343 WHEN Null_Delete_Option_Error THEN
1344 x_return_status := 'E';
1345 x_oracle_error := APP_EXCEPTION.Get_Code;
1346 FND_MESSAGE.SET_NAME('GR',
1347 'GR_NULL_VALUE');
1348 FND_MESSAGE.SET_TOKEN('CODE',
1349 l_msg_token,
1350 FALSE);
1351 IF FND_API.To_Boolean(p_called_by_form) THEN
1352 APP_EXCEPTION.Raise_Exception;
1353 ELSE
1354 x_msg_data := FND_MESSAGE.Get;
1355 END IF;
1356
1357 WHEN OTHERS THEN
1358 ROLLBACK TO SAVEPOINT Delete_Rows;
1359 x_return_status := 'U';
1360 x_oracle_error := APP_EXCEPTION.Get_Code;
1361 l_msg_data := APP_EXCEPTION.Get_Text;
1362 FND_MESSAGE.SET_NAME('GR',
1363 'GR_UNEXPECTED_ERROR');
1364 FND_MESSAGE.SET_TOKEN('TEXT',
1365 l_msg_token,
1366 FALSE);
1367 IF FND_API.To_Boolean(p_called_by_form) THEN
1368 APP_EXCEPTION.Raise_Exception;
1369 ELSE
1370 x_msg_data := FND_MESSAGE.Get;
1371 END IF;
1372
1373 END Delete_Rows;
1374
1375 PROCEDURE Check_Foreign_Keys
1376 (p_item_code IN VARCHAR2,
1377 p_item_no IN VARCHAR2,
1378 p_language IN VARCHAR2,
1379 p_label_code IN VARCHAR2,
1380 p_source_lang IN VARCHAR2,
1381 p_name_description IN VARCHAR2,
1382 p_attribute_category IN VARCHAR2,
1383 p_attribute1 IN VARCHAR2,
1384 p_attribute2 IN VARCHAR2,
1385 p_attribute3 IN VARCHAR2,
1386 p_attribute4 IN VARCHAR2,
1387 p_attribute5 IN VARCHAR2,
1388 p_attribute6 IN VARCHAR2,
1389 p_attribute7 IN VARCHAR2,
1390 p_attribute8 IN VARCHAR2,
1391 p_attribute9 IN VARCHAR2,
1392 p_attribute10 IN VARCHAR2,
1393 p_attribute11 IN VARCHAR2,
1394 p_attribute12 IN VARCHAR2,
1395 p_attribute13 IN VARCHAR2,
1396 p_attribute14 IN VARCHAR2,
1397 p_attribute15 IN VARCHAR2,
1398 p_attribute16 IN VARCHAR2,
1399 p_attribute17 IN VARCHAR2,
1400 p_attribute18 IN VARCHAR2,
1401 p_attribute19 IN VARCHAR2,
1402 p_attribute20 IN VARCHAR2,
1403 p_attribute21 IN VARCHAR2,
1404 p_attribute22 IN VARCHAR2,
1405 p_attribute23 IN VARCHAR2,
1406 p_attribute24 IN VARCHAR2,
1407 p_attribute25 IN VARCHAR2,
1408 p_attribute26 IN VARCHAR2,
1409 p_attribute27 IN VARCHAR2,
1410 p_attribute28 IN VARCHAR2,
1411 p_attribute29 IN VARCHAR2,
1412 p_attribute30 IN VARCHAR2,
1413 x_return_status OUT NOCOPY VARCHAR2,
1414 x_oracle_error OUT NOCOPY NUMBER,
1415 x_msg_data OUT NOCOPY VARCHAR2)
1416 IS
1417
1418 /* Alpha Variables */
1419
1420 L_RETURN_STATUS VARCHAR2(1) := 'S';
1421 L_MSG_DATA VARCHAR2(2000);
1422 L_MSG_TOKEN VARCHAR2(100);
1423 L_LANGUAGE_CODE VARCHAR2(4);
1424 L_ROWID VARCHAR2(18);
1425 L_KEY_EXISTS VARCHAR2(1);
1426
1427 /* Number Variables */
1428
1429 L_ORACLE_ERROR NUMBER;
1430
1431 /* Error Definitions */
1432
1433 ROW_MISSING_ERROR EXCEPTION;
1434
1435 /* Define the cursors */
1436 /* Inventory Master */
1437
1438 CURSOR c_get_ic_item
1439 IS
1440 SELECT ic.item_id
1441 FROM ic_item_mst ic
1442 WHERE ic.item_no = p_item_no;
1446
1443 ICItemRecord c_get_ic_item%ROWTYPE;
1444
1445 /* Language Codes */
1447 CURSOR c_get_language
1448 IS
1449 SELECT lng.language_code
1450 FROM fnd_languages lng
1451 WHERE lng.language_code = l_language_code;
1452 LangRecord c_get_language%ROWTYPE;
1453
1454 BEGIN
1455
1456 /* Initialization Routine */
1457
1458 SAVEPOINT Check_Foreign_Keys;
1459 x_return_status := 'S';
1460 x_oracle_error := 0;
1461 x_msg_data := NULL;
1462 l_msg_token := p_item_code || ' ' || p_item_no || ' ' || p_language;
1463
1464 /* Check the item code */
1465
1466 GR_ITEM_GENERAL_PKG.Check_Primary_Key
1467 (p_item_code,
1468 'F',
1469 l_rowid,
1470 l_key_exists);
1471 IF NOT FND_API.To_Boolean(l_key_exists) THEN
1472 x_return_status := 'E';
1473 FND_MESSAGE.SET_NAME('GR',
1474 'GR_RECORD_NOT_FOUND');
1475 FND_MESSAGE.SET_TOKEN('CODE',
1476 p_item_code,
1477 FALSE);
1478 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1479 END IF;
1480
1481 /* Check the item no */
1482
1483 OPEN c_get_ic_item;
1484 FETCH c_get_ic_item INTO ICItemRecord;
1485 IF c_get_ic_item%NOTFOUND THEN
1486 x_return_status := 'E';
1487 FND_MESSAGE.SET_NAME('GR',
1488 'GR_RECORD_NOT_FOUND');
1489 FND_MESSAGE.SET_TOKEN('CODE',
1490 p_item_no,
1491 FALSE);
1492 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1493 END IF;
1494
1495 /* Check the label code */
1496
1497 GR_LABELS_B_PKG.Check_Primary_Key
1498 (p_label_code,
1499 'F',
1500 l_rowid,
1501 l_key_exists);
1502 IF NOT FND_API.To_Boolean(l_key_exists) THEN
1503 x_return_status := 'E';
1504 FND_MESSAGE.SET_NAME('GR',
1505 'GR_RECORD_NOT_FOUND');
1506 FND_MESSAGE.SET_TOKEN('CODE',
1507 p_label_code,
1508 FALSE);
1509 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1510 END IF;
1511
1512 /* Check the language codes */
1513
1514 l_language_code := p_language;
1515 OPEN c_get_language;
1516 FETCH c_get_language INTO LangRecord;
1517 IF c_get_language%NOTFOUND THEN
1518 CLOSE c_get_language;
1519 l_msg_token := l_language_code;
1520 RAISE Row_Missing_Error;
1521 END IF;
1522 CLOSE c_get_language;
1523
1524 l_language_code := p_source_lang;
1525 OPEN c_get_language;
1526 FETCH c_get_language INTO LangRecord;
1527 IF c_get_language%NOTFOUND THEN
1528 CLOSE c_get_language;
1529 l_msg_token := l_language_code;
1530 RAISE Row_Missing_Error;
1531 END IF;
1532 CLOSE c_get_language;
1533
1534 EXCEPTION
1535
1536 WHEN Row_Missing_Error THEN
1537 ROLLBACK TO SAVEPOINT Delete_Row;
1538 x_return_status := 'E';
1539 x_oracle_error := APP_EXCEPTION.Get_Code;
1540 FND_MESSAGE.SET_NAME('GR',
1541 'GR_RECORD_NOT_FOUND');
1542 FND_MESSAGE.SET_TOKEN('CODE',
1543 l_msg_token,
1544 FALSE);
1545 x_msg_data := FND_MESSAGE.Get;
1546
1547 WHEN OTHERS THEN
1548 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
1549 x_return_status := 'U';
1550 x_oracle_error := APP_EXCEPTION.Get_Code;
1551 l_msg_data := APP_EXCEPTION.Get_Text;
1552 FND_MESSAGE.SET_NAME('GR',
1553 'GR_UNEXPECTED_ERROR');
1554 FND_MESSAGE.SET_TOKEN('TEXT',
1555 l_msg_token,
1556 FALSE);
1557 x_msg_data := FND_MESSAGE.Get;
1558
1559 END Check_Foreign_Keys;
1560
1561 PROCEDURE Check_Integrity
1562 (p_called_by_form IN VARCHAR2,
1563 p_item_code IN VARCHAR2,
1564 p_item_no IN VARCHAR2,
1565 p_language IN VARCHAR2,
1566 p_label_code IN VARCHAR2,
1567 p_source_lang IN VARCHAR2,
1568 p_name_description IN VARCHAR2,
1569 p_attribute_category IN VARCHAR2,
1570 p_attribute1 IN VARCHAR2,
1571 p_attribute2 IN VARCHAR2,
1572 p_attribute3 IN VARCHAR2,
1573 p_attribute4 IN VARCHAR2,
1574 p_attribute5 IN VARCHAR2,
1575 p_attribute6 IN VARCHAR2,
1576 p_attribute7 IN VARCHAR2,
1577 p_attribute8 IN VARCHAR2,
1578 p_attribute9 IN VARCHAR2,
1579 p_attribute10 IN VARCHAR2,
1580 p_attribute11 IN VARCHAR2,
1581 p_attribute12 IN VARCHAR2,
1582 p_attribute13 IN VARCHAR2,
1583 p_attribute14 IN VARCHAR2,
1584 p_attribute15 IN VARCHAR2,
1585 p_attribute16 IN VARCHAR2,
1586 p_attribute17 IN VARCHAR2,
1587 p_attribute18 IN VARCHAR2,
1588 p_attribute19 IN VARCHAR2,
1589 p_attribute20 IN VARCHAR2,
1590 p_attribute21 IN VARCHAR2,
1591 p_attribute22 IN VARCHAR2,
1592 p_attribute23 IN VARCHAR2,
1593 p_attribute24 IN VARCHAR2,
1594 p_attribute25 IN VARCHAR2,
1595 p_attribute26 IN VARCHAR2,
1596 p_attribute27 IN VARCHAR2,
1600 x_return_status OUT NOCOPY VARCHAR2,
1597 p_attribute28 IN VARCHAR2,
1598 p_attribute29 IN VARCHAR2,
1599 p_attribute30 IN VARCHAR2,
1601 x_oracle_error OUT NOCOPY NUMBER,
1602 x_msg_data OUT NOCOPY VARCHAR2)
1603 IS
1604
1605 /* Alpha Variables */
1606
1607 L_RETURN_STATUS VARCHAR2(1) := 'S';
1608 L_MSG_DATA VARCHAR2(2000);
1609 L_CODE_BLOCK VARCHAR2(100);
1610
1611 /* Number Variables */
1612
1613 L_ORACLE_ERROR NUMBER;
1614 L_RECORD_COUNT NUMBER;
1615
1616 /* Exceptions */
1617
1618 INSTALLED_LANGUAGE_ERROR EXCEPTION;
1619
1620
1621 /* Define the Cursors */
1622
1623 CURSOR c_get_language_code
1624 IS
1625 SELECT lng.installed_flag
1626 FROM fnd_languages lng
1627 WHERE lng.language_code = p_language
1628 AND lng.installed_flag IN ('B', 'I');
1629 LangRecord c_get_language_code%ROWTYPE;
1630
1631 BEGIN
1632
1633 /* Initialization Routine */
1634
1635 SAVEPOINT Check_Integrity;
1636 x_return_status := 'S';
1637 x_oracle_error := 0;
1638 x_msg_data := NULL;
1639
1640 /* Check the language isn't base or installed */
1641
1642 OPEN c_get_language_code;
1643 FETCH c_get_language_code INTO LangRecord;
1644 IF c_get_language_code%FOUND THEN
1645 CLOSE c_get_language_code;
1646 RAISE Installed_Language_Error;
1647 END IF;
1648 CLOSE c_get_language_code;
1649
1650 EXCEPTION
1651
1652 WHEN Installed_Language_Error THEN
1653 ROLLBACK TO SAVEPOINT Check_Integrity;
1654 x_return_status := 'E';
1655 FND_MESSAGE.SET_NAME('GR',
1656 'GR_INSTALLED_LANG');
1657 FND_MESSAGE.SET_TOKEN('CODE',
1658 p_language,
1659 FALSE);
1660 IF FND_API.To_Boolean(p_called_by_form) THEN
1661 APP_EXCEPTION.Raise_Exception;
1662 ELSE
1663 x_msg_data := FND_MESSAGE.Get;
1664 END IF;
1665
1666 WHEN OTHERS THEN
1667 ROLLBACK TO SAVEPOINT Check_Integrity;
1668 x_return_status := 'U';
1669 x_oracle_error := APP_EXCEPTION.Get_Code;
1670 l_msg_data := APP_EXCEPTION.Get_Text;
1671 FND_MESSAGE.SET_NAME('GR',
1672 'GR_UNEXPECTED_ERROR');
1673 FND_MESSAGE.SET_TOKEN('TEXT',
1674 l_msg_data,
1675 FALSE);
1676 IF FND_API.To_Boolean(p_called_by_form) THEN
1677 APP_EXCEPTION.Raise_Exception;
1678 ELSE
1679 x_msg_data := FND_MESSAGE.Get;
1680 END IF;
1681
1682 END Check_Integrity;
1683
1684 PROCEDURE Check_Primary_Key
1685 /* p_item_code is the generic item code to check.
1686 ** p_item_no is the item number to check.
1687 ** p_language is the language code part of the key
1688 ** p_called_by_form is 'T' if called by a form or 'F' if not.
1689 ** x_rowid is the row id of the record if found.
1690 ** x_key_exists is 'T' is the record is found, 'F' if not.
1691 */
1692 (p_item_code IN VARCHAR2,
1693 p_item_no IN VARCHAR2,
1694 p_language IN VARCHAR2,
1695 p_called_by_form IN VARCHAR2,
1696 x_rowid OUT NOCOPY VARCHAR2,
1697 x_key_exists OUT NOCOPY VARCHAR2)
1698 IS
1699 /* Alphanumeric variables */
1700
1701 L_MSG_DATA VARCHAR2(80);
1702
1703 /* Declare any variables and the cursor */
1704
1705
1706 CURSOR c_get_generic_tl_rowid
1707 IS
1708 SELECT gin.rowid
1709 FROM gr_generic_ml_name_tl gin
1710 WHERE gin.item_code = p_item_code
1711 AND gin.item_no = p_item_no
1712 AND gin.language = p_language;
1713 GenericTLRecord c_get_generic_tl_rowid%ROWTYPE;
1714
1715 BEGIN
1716
1717 l_msg_data := p_item_code || ' ' || p_item_no || ' ' || p_language;
1718
1719 x_key_exists := 'F';
1720 OPEN c_get_generic_tl_rowid;
1721 FETCH c_get_generic_tl_rowid INTO GenericTLRecord;
1722 IF c_get_generic_tl_rowid%FOUND THEN
1723 x_key_exists := 'T';
1724 x_rowid := GenericTLRecord.rowid;
1725 ELSE
1726 x_key_exists := 'F';
1727 END IF;
1728 CLOSE c_get_generic_tl_rowid;
1729
1730 EXCEPTION
1731
1732 WHEN Others THEN
1733 l_msg_data := APP_EXCEPTION.Get_Text;
1734 FND_MESSAGE.SET_NAME('GR',
1735 'GR_UNEXPECTED_ERROR');
1736 FND_MESSAGE.SET_TOKEN('TEXT',
1737 l_msg_data,
1738 FALSE);
1739 IF FND_API.To_Boolean(p_called_by_form) THEN
1740 APP_EXCEPTION.Raise_Exception;
1741 END IF;
1742
1743 END Check_Primary_Key;
1744
1745 /* 21-Jan-2002 Mercy Thomas BUG 2190024 - Added procedure NEW_LANGUAGE
1746 to be called from GRNLINS.sql. Generated from tltblgen. */
1747
1748 /* 28-Jan-2002 Melanie Grosser BUG 2190024 - Procedure NEW_LANGUAGE had been
1749 generated incorrectly. I regenerated it.
1750
1751 */
1752 /* Melanie Grosser 23-Dec-2003 BUG 3307401 - Regenerated procedure NEW_LANGUAGE using
1753 GR_GENERIC_ITEMS_B as the base table and fully defining the primary
1757 is
1754 key.
1755 */
1756 procedure NEW_LANGUAGE
1758 begin
1759 delete from GR_GENERIC_ML_NAME_TL T
1760 where not exists
1761 (select NULL
1762 from GR_GENERIC_ITEMS_B B
1763 where B.ITEM_CODE = T.ITEM_CODE
1764 and B.ITEM_NO = T.ITEM_NO
1765 );
1766
1767 update GR_GENERIC_ML_NAME_TL T set (
1768 NAME_DESCRIPTION
1769 ) = (select
1770 B.NAME_DESCRIPTION
1771 from GR_GENERIC_ML_NAME_TL B
1772 where B.ITEM_CODE = T.ITEM_CODE
1773 and B.ITEM_NO = T.ITEM_NO
1774 and B.LANGUAGE = T.SOURCE_LANG)
1775 where (
1776 T.ITEM_CODE,
1777 T.ITEM_NO,
1778 T.LANGUAGE
1779 ) in (select
1780 SUBT.ITEM_CODE,
1781 SUBT.ITEM_NO,
1782 SUBT.LANGUAGE
1783 from GR_GENERIC_ML_NAME_TL SUBB, GR_GENERIC_ML_NAME_TL SUBT
1784 where SUBB.ITEM_CODE = SUBT.ITEM_CODE
1785 and SUBB.ITEM_NO = SUBT.ITEM_NO
1786 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1787 and (SUBB.NAME_DESCRIPTION <> SUBT.NAME_DESCRIPTION
1788 ));
1789
1790 insert into GR_GENERIC_ML_NAME_TL (
1791 ATTRIBUTE10,
1792 ATTRIBUTE11,
1793 ATTRIBUTE12,
1794 ATTRIBUTE13,
1795 ATTRIBUTE14,
1796 ATTRIBUTE15,
1797 ATTRIBUTE16,
1798 ATTRIBUTE17,
1799 ATTRIBUTE18,
1800 ATTRIBUTE19,
1801 ATTRIBUTE20,
1802 ATTRIBUTE21,
1803 ATTRIBUTE22,
1804 ATTRIBUTE23,
1805 ATTRIBUTE24,
1806 ATTRIBUTE25,
1807 ATTRIBUTE26,
1808 ATTRIBUTE27,
1809 ATTRIBUTE28,
1810 ATTRIBUTE29,
1811 ATTRIBUTE30,
1812 CREATED_BY,
1813 CREATION_DATE,
1814 LAST_UPDATED_BY,
1815 LAST_UPDATE_DATE,
1816 LAST_UPDATE_LOGIN,
1817 ITEM_CODE,
1818 ITEM_NO,
1819 LABEL_CODE,
1820 NAME_DESCRIPTION,
1821 ATTRIBUTE_CATEGORY,
1822 ATTRIBUTE1,
1823 ATTRIBUTE2,
1824 ATTRIBUTE3,
1825 ATTRIBUTE4,
1826 ATTRIBUTE5,
1827 ATTRIBUTE6,
1828 ATTRIBUTE7,
1829 ATTRIBUTE8,
1830 ATTRIBUTE9,
1831 LANGUAGE,
1832 SOURCE_LANG
1833 ) select /*+ ORDERED */
1834 B.ATTRIBUTE10,
1835 B.ATTRIBUTE11,
1836 B.ATTRIBUTE12,
1837 B.ATTRIBUTE13,
1838 B.ATTRIBUTE14,
1839 B.ATTRIBUTE15,
1840 B.ATTRIBUTE16,
1841 B.ATTRIBUTE17,
1842 B.ATTRIBUTE18,
1843 B.ATTRIBUTE19,
1844 B.ATTRIBUTE20,
1845 B.ATTRIBUTE21,
1846 B.ATTRIBUTE22,
1847 B.ATTRIBUTE23,
1848 B.ATTRIBUTE24,
1849 B.ATTRIBUTE25,
1850 B.ATTRIBUTE26,
1851 B.ATTRIBUTE27,
1852 B.ATTRIBUTE28,
1853 B.ATTRIBUTE29,
1854 B.ATTRIBUTE30,
1855 B.CREATED_BY,
1856 B.CREATION_DATE,
1857 B.LAST_UPDATED_BY,
1858 B.LAST_UPDATE_DATE,
1859 B.LAST_UPDATE_LOGIN,
1860 B.ITEM_CODE,
1861 B.ITEM_NO,
1862 B.LABEL_CODE,
1863 B.NAME_DESCRIPTION,
1864 B.ATTRIBUTE_CATEGORY,
1865 B.ATTRIBUTE1,
1866 B.ATTRIBUTE2,
1867 B.ATTRIBUTE3,
1868 B.ATTRIBUTE4,
1869 B.ATTRIBUTE5,
1870 B.ATTRIBUTE6,
1871 B.ATTRIBUTE7,
1872 B.ATTRIBUTE8,
1873 B.ATTRIBUTE9,
1874 L.LANGUAGE_CODE,
1875 B.SOURCE_LANG
1876 from GR_GENERIC_ML_NAME_TL B, FND_LANGUAGES L
1877 where L.INSTALLED_FLAG in ('I', 'B')
1878 and B.LANGUAGE = userenv('LANG')
1879 and not exists
1880 (select NULL
1881 from GR_GENERIC_ML_NAME_TL T
1882 where T.ITEM_CODE = B.ITEM_CODE
1883 and T.ITEM_NO = B.ITEM_NO
1884 and T.LANGUAGE = L.LANGUAGE_CODE);
1885
1886 end NEW_LANGUAGE;
1887
1888 END GR_GENERIC_ML_NAME_TL_PKG;