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