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