[Home] [Help]
PACKAGE BODY: APPS.GL_JE_CATEGORIES_PKG
Source
1 PACKAGE BODY GL_JE_CATEGORIES_PKG AS
2 /* $Header: glijectb.pls 120.14 2005/05/05 01:09:27 kvora ship $ */
3
4
5 --
6 -- PRIVATE FUNCTIONS
7 --
8
9 --
10 -- Procedure
11 -- select_row
12 -- Purpose
13 -- Used to select a particular category row
14 -- History
15 -- 28-MAR-94 D. J. Ogg Created
16 -- Arguments
17 -- recinfo Various information about the row
18 -- Example
19 -- gl_je_categories_pkg.select_row(recinfo)
20 -- Notes
21 --
22 PROCEDURE select_row( recinfo IN OUT NOCOPY gl_je_categories%ROWTYPE) IS
23 BEGIN
24 SELECT *
25 INTO recinfo
26 FROM gl_je_categories
27 WHERE je_category_name = recinfo.je_category_name;
28 END SELECT_ROW;
29
30
31 --
32 -- PUBLIC FUNCTIONS
33 --
34
35 PROCEDURE check_unique( x_rowid VARCHAR2,
36 x_name VARCHAR2 ) IS
37 CURSOR c_dup IS
38 SELECT 'Duplicate'
39 FROM gl_je_categories cat
40 WHERE cat.user_je_category_name = x_name
41 AND ( x_rowid is NULL
42 OR
43 cat.rowid <> x_rowid );
44 dummy VARCHAR2(100);
45
46 BEGIN
47 OPEN c_dup;
48 FETCH c_dup INTO dummy;
49
50 IF c_dup%FOUND THEN
51 CLOSE c_dup;
52 fnd_message.set_name( 'SQLGL', 'GL_DUPLICATE_JE_CATEGORY' );
53 app_exception.raise_exception;
54 END IF;
55
56 CLOSE c_dup;
57
58 EXCEPTION
59 WHEN app_exceptions.application_exception THEN
60 RAISE;
61 WHEN OTHERS THEN
62 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
63 fnd_message.set_token('PROCEDURE',
64 'GL_JE_CATEGORIES_PKG.check_unique');
65 RAISE;
66
67 END check_unique;
68
69
70 PROCEDURE check_unique_key( x_rowid VARCHAR2,
71 x_key VARCHAR2 ) IS
72 CURSOR c_dup IS
73 SELECT 'Duplicate'
74 FROM gl_je_categories cat
75 WHERE cat.je_category_key = x_key
76 AND ( x_rowid is NULL
77 OR
78 cat.rowid <> x_rowid );
79 dummy VARCHAR2(100);
80
81 BEGIN
82 OPEN c_dup;
83 FETCH c_dup INTO dummy;
84
85 IF c_dup%FOUND THEN
86 CLOSE c_dup;
87 fnd_message.set_name( 'SQLGL', 'GL_DUPLICATE_JE_CATEGORY_KEY' );
88 app_exception.raise_exception;
89 END IF;
90
91 CLOSE c_dup;
92
93 EXCEPTION
94 WHEN app_exceptions.application_exception THEN
95 RAISE;
96 WHEN OTHERS THEN
97 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
98 fnd_message.set_token('PROCEDURE',
99 'GL_JE_CATEGORIES_PKG.check_unique_key');
100 RAISE;
101
102 END check_unique_key;
103
104 -- ************************************************************************
105
106 FUNCTION get_unique_id RETURN NUMBER IS
107 CURSOR c_getid IS
108 SELECT gl_je_categories_s.NEXTVAL
109 FROM dual;
110 id number;
111
112 BEGIN
113 OPEN c_getid;
114 FETCH c_getid INTO id;
115
116 IF c_getid%FOUND THEN
117 CLOSE c_getid;
118 RETURN( id );
119 ELSE
120 CLOSE c_getid;
121 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
122 fnd_message.set_token('SEQUENCE', 'gl_je_categories_s');
123 app_exception.raise_exception;
124 END IF;
125
126 EXCEPTION
127 WHEN app_exceptions.application_exception THEN
128 RAISE;
129 WHEN OTHERS THEN
130 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
131 fnd_message.set_token('PROCEDURE',
132 'GL_JE_CATEGORIES_PKG.get_unique_id');
133 RAISE;
134
135 END get_unique_id;
136
137 -- ************************************************************************
138
139 PROCEDURE insert_fnd_cat( x_je_category_name VARCHAR2,
140 x_user_je_category_name VARCHAR2,
141 x_description VARCHAR2,
142 x_last_updated_by NUMBER,
143 x_created_by NUMBER,
144 x_last_update_login NUMBER ) IS
145 CURSOR c_dup IS
146 SELECT 'Duplicate'
147 FROM fnd_doc_sequence_categories fcat
148 WHERE fcat.application_id = 101
149 AND fcat.code = x_je_category_name ;
150 dummy VARCHAR2(100);
151
152 BEGIN
153 OPEN c_dup;
154 FETCH c_dup INTO dummy;
155
156 IF c_dup%NOTFOUND THEN
157 INSERT INTO fnd_doc_sequence_categories (
158 application_id, last_update_date, last_updated_by,
159 code, name, description,
160 table_name, created_by, creation_date,
161 last_update_login )
162 SELECT 101, sysdate, x_last_updated_by,
163 x_je_category_name, x_user_je_category_name, x_description,
164 'GL_JE_HEADERS', x_created_by, sysdate,
165 x_last_update_login
166 FROM dual ;
167 END IF;
168
169 CLOSE c_dup;
170
171 EXCEPTION
172 WHEN app_exceptions.application_exception THEN
173 RAISE;
174 WHEN OTHERS THEN
175 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
176 fnd_message.set_token('PROCEDURE',
177 'GL_JE_CATEGORIES_PKG.insert_fnd_cat');
178 RAISE;
179
180 END insert_fnd_cat;
181
182 -- ************************************************************************
183
184 PROCEDURE update_fnd_cat( x_je_category_name VARCHAR2,
185 x_user_je_category_name VARCHAR2,
186 x_description VARCHAR2,
187 x_last_updated_by NUMBER ) IS
188 CURSOR c_dup IS
189 SELECT 'Duplicate'
190 FROM fnd_doc_sequence_categories fcat
191 WHERE fcat.application_id = 101
192 AND fcat.code = x_je_category_name ;
193 dummy VARCHAR2(100);
194
195 BEGIN
196 OPEN c_dup;
197 FETCH c_dup INTO dummy;
198
199 IF c_dup%FOUND THEN
200 UPDATE fnd_doc_sequence_categories fcat
201 SET fcat.description = x_description,
202 fcat.last_update_date = sysdate,
203 fcat.last_updated_by = x_last_updated_by,
204 fcat.name = x_user_je_category_name
205 WHERE fcat.application_id = 101
206 AND fcat.code = x_je_category_name;
207 END IF;
208
209 CLOSE c_dup;
210
211 EXCEPTION
212 WHEN app_exceptions.application_exception THEN
213 RAISE;
214 WHEN OTHERS THEN
215 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
216 fnd_message.set_token('PROCEDURE',
217 'GL_JE_CATEGORIES_PKG.update_fnd_cat');
218 RAISE;
219
220 END update_fnd_cat;
221
222 -- ************************************************************************
223
224 PROCEDURE update_fnd_cat_all ( x_last_updated_by NUMBER ) IS
225 dummy VARCHAR2(100);
226
227 BEGIN
228
229 UPDATE fnd_doc_sequence_categories fcat
230 SET ( fcat.description,
231 fcat.last_update_date,
232 fcat.last_updated_by,
233 fcat.name ) =
234 ( select gcat.description,
235 sysdate,
236 x_last_updated_by,
237 gcat.user_je_category_name
238 from gl_je_categories_tl gcat
239 where gcat.language = FND_GLOBAL.BASE_LANGUAGE
240 AND gcat.je_category_name = fcat.code )
241 WHERE fcat.application_id = 101
242 and fcat.code in ( select fcat2.code
243 from gl_je_categories_tl gcat2,
244 fnd_doc_sequence_categories fcat2
245 where gcat2.language = FND_GLOBAL.BASE_LANGUAGE
246 AND fcat2.application_id = 101
247 AND gcat2.je_category_name = fcat2.code
248 AND ( gcat2.user_je_category_name <> fcat2.name
249 or gcat2.description <> fcat2.description ) );
250
251 EXCEPTION
252 WHEN app_exceptions.application_exception THEN
253 RAISE;
254 WHEN OTHERS THEN
255 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
256 fnd_message.set_token('PROCEDURE',
257 'GL_JE_CATEGORIES_PKG.update_fnd_cat_all');
258 RAISE;
259
260 END update_fnd_cat_all;
261
262 -- ************************************************************************
263
264 PROCEDURE insert_other_cat( x_je_category_name VARCHAR2,
265 x_user_je_category_name VARCHAR2,
266 x_description VARCHAR2,
267 x_last_updated_by NUMBER,
268 x_created_by NUMBER,
269 x_last_update_login NUMBER ) IS
270 BEGIN
271 insert_fnd_cat( x_je_category_name,
272 x_user_je_category_name,
273 x_description,
274 x_last_updated_by,
275 x_created_by,
276 x_last_update_login);
277
278 GL_AUTOREVERSE_OPTIONS_PKG.insert_reversal_cat(
279 x_je_category_name,
280 x_created_by,
281 x_last_updated_by,
282 x_last_update_login);
283 END;
284
285
286 -- ************************************************************************
287
288
289 PROCEDURE select_columns(
290 x_je_category_name VARCHAR2,
291 x_user_je_category_name IN OUT NOCOPY VARCHAR2 ) IS
292
293 recinfo gl_je_categories%ROWTYPE;
294
295 BEGIN
296 recinfo.je_category_name := x_je_category_name;
297
298 select_row(recinfo);
299
300 x_user_je_category_name := recinfo.user_je_category_name;
301 -- x_reversal_option_code := recinfo.reversal_option_code;
302 END select_columns;
303
304 -- ************************************************************************
305
306 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
307 X_Je_Category_Name IN OUT NOCOPY VARCHAR2,
308 X_Language IN OUT NOCOPY VARCHAR2,
309 X_Source_Lang IN OUT NOCOPY VARCHAR2,
310 X_Last_Update_Date DATE,
311 X_Last_Updated_By NUMBER,
312 X_User_Je_Category_Name VARCHAR2,
313 X_Je_Category_Key VARCHAR2,
314 X_Creation_Date DATE,
315 X_Created_By NUMBER,
316 X_Last_Update_Login NUMBER,
317 X_Description VARCHAR2,
318 X_Attribute1 VARCHAR2,
319 X_Attribute2 VARCHAR2,
320 X_Attribute3 VARCHAR2,
321 X_Attribute4 VARCHAR2,
322 X_Attribute5 VARCHAR2,
323 X_Context VARCHAR2,
324 X_Consolidation_Flag VARCHAR2) IS
325 CURSOR C IS SELECT rowid, language, source_lang
326 FROM gl_je_categories_tl
327 WHERE je_category_name = X_Je_Category_Name
328 and Language = userenv('LANG');
329 BEGIN
330
331 if (X_Je_Category_Name is NULL) then
332 -- app_exception.raise_exception;
333 RAISE NO_DATA_FOUND;
334 end if;
335
336 -- update previously existing columns
337 UPDATE GL_JE_CATEGORIES_TL
338 SET
339 consolidation_flag = UPPER(x_consolidation_flag)
340 WHERE
341 je_category_name = X_je_category_name;
342
343 -- insert new columns
344 INSERT INTO GL_JE_CATEGORIES_TL(
345 je_category_name,
346 language,
347 source_lang,
348 last_update_date,
349 last_updated_by,
350 user_je_category_name,
351 je_category_key,
352 creation_date,
353 created_by,
354 last_update_login,
355 description,
356 attribute1,
357 attribute2,
358 attribute3,
359 attribute4,
360 attribute5,
361 context,
362 consolidation_flag)
363 SELECT
364 X_Je_Category_Name,
365 L.Language_Code,
366 userenv('LANG'),
367 X_Last_Update_Date,
368 X_Last_Updated_By,
369 X_User_Je_Category_Name,
370 X_Je_Category_Key,
371 X_Creation_Date,
372 X_Created_By,
373 X_Last_Update_Login,
374 X_Description,
375 X_Attribute1,
376 X_Attribute2,
377 X_Attribute3,
378 X_Attribute4,
379 X_Attribute5,
380 X_Context,
381 UPPER(X_Consolidation_Flag)
382 FROM FND_LANGUAGES L
383 WHERE L.Installed_Flag in ('I', 'B')
384 AND not exists
385 ( select NULL
386 from GL_JE_CATEGORIES_TL B
387 where B.Je_Category_Name = X_Je_Category_Name
388 and B.Language = L.Language_Code);
389 OPEN C;
390 FETCH C INTO X_Rowid, X_Language, X_Source_lang;
391 if (C%NOTFOUND) then
392 CLOSE C;
393 RAISE NO_DATA_FOUND;
394 end if;
395 CLOSE C;
396
397 GL_JE_CATEGORIES_PKG.insert_other_cat( x_je_category_name,
398 x_user_je_category_name,
399 x_description,
400 x_last_updated_by,
401 x_created_by,
402 x_last_update_login);
403
404 END Insert_Row;
405
406 -- ************************************************************************
407
408 PROCEDURE Lock_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
409 X_Je_Category_Name VARCHAR2,
410 X_User_Je_Category_Name VARCHAR2,
411 X_Je_Category_Key VARCHAR2,
412 X_Description VARCHAR2,
413 X_Attribute1 VARCHAR2,
414 X_Attribute2 VARCHAR2,
415 X_Attribute3 VARCHAR2,
416 X_Attribute4 VARCHAR2,
417 X_Attribute5 VARCHAR2,
418 X_Context VARCHAR2,
419 X_Consolidation_Flag VARCHAR2) IS
420 CURSOR C IS
421 SELECT * FROM GL_JE_CATEGORIES_TL
422 WHERE je_category_name = X_je_category_name
423 and Language = userenv('LANG')
424 FOR UPDATE OF JE_CATEGORY_NAME NOWAIT;
425 Recinfo C%ROWTYPE;
426 BEGIN
427 OPEN C;
428 FETCH C INTO Recinfo;
429 if (C%NOTFOUND) then
430 CLOSE C;
431 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
432 APP_EXCEPTION.RAISE_EXCEPTION;
433 end if;
434 CLOSE C;
435 if (
436 ( ( (Recinfo.je_category_name = X_je_category_name)
437 OR ( (Recinfo.je_category_name IS NULL)
438 AND (X_je_category_name IS NULL))))
439 AND
440 ( ( (Recinfo.user_je_category_name = X_user_je_category_name )
441 OR ( (Recinfo.user_je_category_name IS NULL)
442 AND (X_user_je_category_name IS NULL))))
443 AND
444 ( ( (Recinfo.je_category_key = X_je_category_key )
445 OR ( (Recinfo.je_category_key IS NULL)
446 AND (X_je_category_key IS NULL))))
447 AND
448 ( ( (Recinfo.description = X_description )
449 OR ( (Recinfo.description IS NULL)
450 AND (X_description IS NULL))))
451 AND
452 ( ( (Recinfo.attribute1 = X_attribute1 )
453 OR ( (Recinfo.attribute1 IS NULL)
454 AND (X_attribute1 IS NULL))))
455 AND
456 ( ( (Recinfo.attribute2 = X_attribute2)
457 OR ( (Recinfo.attribute2 IS NULL)
458 AND (X_attribute2 IS NULL))))
459 AND
460 ( ( (Recinfo.attribute3 = X_attribute3 )
461 OR ( (Recinfo.attribute3 IS NULL)
462 AND (X_attribute3 IS NULL))))
463 AND
464 ( ( (Recinfo.attribute4 = X_attribute4 )
465 OR ( (Recinfo.attribute4 IS NULL)
466 AND (X_attribute4 IS NULL))))
467 AND
468 ( ( (Recinfo.attribute5 = X_attribute5 )
469 OR ( (Recinfo.attribute5 IS NULL)
470 AND (X_attribute5 IS NULL))))
471 AND
472 ( ( (Recinfo.context = X_context )
473 OR ( (Recinfo.context IS NULL)
474 AND (X_context IS NULL))))
475 AND
476 ( ( (Recinfo.consolidation_flag = UPPER(X_consolidation_flag) )
477 OR ( (Recinfo.consolidation_flag IS NULL)
478 AND (UPPER(X_consolidation_flag) IS NULL))))) THEN
479 return;
480 ELSE
481 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
482 APP_EXCEPTION.RAISE_EXCEPTION;
483 end if;
484 END Lock_Row;
485
486 -- ************************************************************************
487
488 PROCEDURE Update_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
489 X_Je_Category_Name VARCHAR2,
490 X_Last_Update_Date DATE,
491 X_Last_Updated_By NUMBER,
492 X_User_Je_Category_Name VARCHAR2,
493 X_Je_Category_Key VARCHAR2,
494 X_Creation_Date DATE,
495 X_Last_Update_Login NUMBER,
496 X_Description VARCHAR2,
497 X_Attribute1 VARCHAR2,
498 X_Attribute2 VARCHAR2,
499 X_Attribute3 VARCHAR2,
500 X_Attribute4 VARCHAR2,
501 X_Attribute5 VARCHAR2,
502 X_Context VARCHAR2,
503 X_Consolidation_Flag VARCHAR2) IS
504 BEGIN
505 -- update non-translatable columns
506 UPDATE GL_JE_CATEGORIES_TL
507 SET
508 je_category_name = x_je_category_name,
509 je_category_key = x_je_category_key,
510 last_update_date = x_last_update_date,
511 last_updated_by = x_last_updated_by,
512 creation_date = x_creation_date,
513 last_update_login = x_last_update_login,
514 attribute1 = x_attribute1,
515 attribute2 = x_attribute2,
516 attribute3 = x_attribute3,
517 attribute4 = x_attribute4,
518 attribute5 = x_attribute5,
519 context = x_context,
520 consolidation_flag = UPPER(x_consolidation_flag)
521 WHERE
522 je_category_name = X_je_category_name;
523
524 if (SQL%NOTFOUND) then
525 RAISE NO_DATA_FOUND;
526 end if;
527
528 -- update the translatable columns
529 UPDATE GL_JE_CATEGORIES_TL
530 SET
531 user_je_category_name = x_user_je_category_name,
532 description = x_description,
533 source_lang = userenv('LANG')
534 WHERE je_category_name = x_je_category_name
535 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
536
537 if (sql%notfound) then
538 raise no_data_found;
539 end if;
540 END Update_Row;
541
542 -- ************************************************************************
543
544 PROCEDURE Load_Row(X_Je_Category_Name IN OUT NOCOPY VARCHAR2,
545 X_Je_Category_Key VARCHAR2,
546 X_User_Je_Category_Name VARCHAR2,
547 X_Description VARCHAR2,
548 X_Attribute1 VARCHAR2,
549 X_Attribute2 VARCHAR2,
550 X_Attribute3 VARCHAR2,
551 X_Attribute4 VARCHAR2,
552 X_Attribute5 VARCHAR2,
553 X_Context VARCHAR2,
554 X_Owner VARCHAR2,
555 X_Force_Edits VARCHAR2) IS
556 user_id number := 0;
557 v_creation_date date;
558 v_rowid rowid := null;
559 v_language VARCHAR2(4) := null;
560 v_source_lang VARCHAR2(4) := null;
561 BEGIN
562
563 -- validate input parameters
564 if ( X_User_Je_Category_Name is NULL ) then
565 fnd_message.set_name('SQLGL', 'GL_LOAD_ROW_NO_DATA');
566 app_exception.raise_exception;
567 end if;
568
569 if (X_OWNER = 'SEED') then
570 user_id := 1;
571 end if;
572
573 begin
574 /* When je_category_name is null, we assume it to be a new row. */
575 if (X_je_category_name is null) then
576 SELECT gl_je_categories_s.NEXTVAL
577 INTO X_je_category_name
578 FROM dual;
579 raise no_data_found;
580 end if;
581
582 /* Check if the row exists in the database. If it does, retrieves
583 the creation date for update_row. */
584 select creation_date
585 into v_creation_date
586 from gl_je_categories
587 where je_category_name = X_je_category_name;
588
589 /* Update only if force_edits is 'Y' or if user_id = 1 */
590 if ( user_id = 1 or X_Force_Edits = 'Y' ) then
591 -- update row in GL_JE_CATEGORIES_TL if present
592 GL_JE_CATEGORIES_PKG.Update_Row(
593 X_Rowid => v_rowid,
594 X_je_category_name => X_Je_Category_Name,
595 X_last_update_date => sysdate,
596 X_last_updated_by => user_id,
597 X_user_je_category_name => X_user_je_category_name,
598 X_je_category_key => nvl(X_Je_Category_Key,
599 X_Je_Category_Name),
600 X_creation_date => v_creation_date,
601 X_last_update_login => 0,
602 X_Description => X_Description,
603 X_Attribute1 => X_Attribute1,
604 X_Attribute2 => X_Attribute2,
605 X_Attribute3 => X_Attribute3,
606 X_Attribute4 => X_Attribute4,
607 X_Attribute5 => X_Attribute5,
608 X_context => X_Context,
609 X_Consolidation_Flag => null);
610
611 -- update FND_DOC_SEQUENCE_CATEGORIES if change is made in
612 -- the base language
613 if ( userenv('LANG') = FND_GLOBAL.BASE_LANGUAGE ) then
614 GL_JE_CATEGORIES_PKG.update_fnd_cat( x_je_category_name,
615 x_user_je_category_name,
616 x_description,
617 user_id );
618 end if;
619 end if;
620 exception
621 when NO_DATA_FOUND then
622 GL_JE_CATEGORIES_PKG.Insert_Row(
623 X_Rowid => v_rowid,
624 X_je_category_name => X_Je_Category_Name,
625 X_language => v_language,
626 X_source_lang => v_source_lang,
627 X_last_update_date => sysdate,
628 X_last_updated_by => user_id,
629 X_user_je_category_name => X_user_je_category_name,
630 X_je_category_key => Nvl(X_Je_Category_Key,
631 X_Je_Category_Name),
632 X_creation_date => sysdate,
633 X_created_by => user_id,
634 X_last_update_login => 0,
635 X_Description => X_Description,
636 X_Attribute1 => X_Attribute1,
637 X_Attribute2 => X_Attribute2,
638 X_Attribute3 => X_Attribute3,
639 X_Attribute4 => X_Attribute4,
640 X_Attribute5 => X_Attribute5,
641 X_context => X_Context,
642 X_Consolidation_Flag => null);
643 end;
644 END Load_Row;
645
646 -- ************************************************************************
647 PROCEDURE Translate_Row(
648 X_Je_Category_Name VARCHAR2,
649 X_User_Je_Category_Name VARCHAR2,
650 X_Description VARCHAR2,
651 X_Owner VARCHAR2,
652 X_Force_Edits VARCHAR2 ) IS
653 user_id number := 0;
654 BEGIN
655 if (X_OWNER = 'SEED') then
656 user_id := 1;
657 end if;
658
659 /* Update only if force_edits is 'Y' or if user_id = 1 */
660 if ( user_id = 1 or X_Force_Edits = 'Y' ) then
661 UPDATE GL_JE_CATEGORIES_TL
662 SET
663 user_je_category_name = x_user_je_category_name,
664 description = x_description,
665 last_update_date = sysdate,
666 last_updated_by = user_id,
667 last_update_login = 0,
668 source_lang = userenv('LANG')
669 WHERE userenv('LANG') in (LANGUAGE, SOURCE_LANG)
670 and je_category_name = X_je_category_name;
671
672 -- update FND_DOC_SEQUENCE_CATEGORIES if change is made in
673 -- the base language
674 if ( userenv('LANG') = FND_GLOBAL.BASE_LANGUAGE ) then
675 GL_JE_CATEGORIES_PKG.update_fnd_cat( x_je_category_name,
676 x_user_je_category_name,
677 x_description,
678 user_id );
679 end if;
680 end if;
681 /*If base language is not set to the language being uploaded, then do nothing.*/
682 if (sql%notfound) then
683 null;
684 end if;
685 END Translate_Row;
686
687 -- ************************************************************************
688
689 procedure ADD_LANGUAGE
690 is
691 begin
692
693 update GL_JE_CATEGORIES_TL T
694 set ( user_je_category_name,
695 DESCRIPTION )
696 = ( select
697 B.user_je_category_name,
698 B.DESCRIPTION
699 from gl_je_categories_tl B
700 where B.je_category_name = T.je_category_name
701 and B.LANGUAGE = T.SOURCE_LANG )
702 where ( T.je_category_name,
703 T.LANGUAGE ) in
704 ( select
705 SUBT.je_category_name,
706 SUBT.LANGUAGE
707 from gl_je_categories_tl SUBB,
708 gl_je_categories_tl SUBT
709 where SUBB.je_category_name = SUBT.je_category_name
710 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
711 and (SUBB.USER_JE_CATEGORY_NAME <> SUBT.USER_JE_CATEGORY_NAME
712 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
713 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
714 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null))
715 );
716
717 insert into gl_je_categories_tl (
718 je_category_name,
719 LAST_UPDATE_DATE,
720 LAST_UPDATED_BY,
721 CREATION_DATE,
722 CREATED_BY,
723 LAST_UPDATE_LOGIN,
724 DESCRIPTION,
725 user_je_category_name,
726 je_category_key,
727 LANGUAGE,
728 SOURCE_LANG,
729 attribute1,
730 attribute2,
731 attribute3,
732 attribute4,
733 attribute5,
734 context,
735 consolidation_flag
736 )
737 select
738 B.je_category_name,
739 B.LAST_UPDATE_DATE,
740 B.LAST_UPDATED_BY,
741 B.CREATION_DATE,
742 B.CREATED_BY,
743 B.LAST_UPDATE_LOGIN,
744 B.DESCRIPTION,
745 B.user_je_category_name,
746 B.je_category_key,
747 L.LANGUAGE_CODE,
748 B.source_lang,
749 B.attribute1,
750 B.attribute2,
751 B.attribute3,
752 B.attribute4,
753 B.attribute5,
754 B.context,
755 B.consolidation_flag
756 from gl_je_categories_tl B, FND_LANGUAGES L
757 where L.INSTALLED_FLAG in ('I', 'B')
758 and B.LANGUAGE = userenv('LANG')
759 and not exists
760 (select NULL
761 from gl_je_categories_tl T
762 where T.je_category_name = B.je_category_name
763 and T.LANGUAGE = L.LANGUAGE_CODE);
764
765 end ADD_LANGUAGE;
766
767 -- ************************************************************************
768
769 END GL_JE_CATEGORIES_PKG;