[Home] [Help]
PACKAGE BODY: APPS.GL_JE_CATEGORIES_PKG
Source
1 PACKAGE BODY GL_JE_CATEGORIES_PKG AS
2 /* $Header: glijectb.pls 120.15 2011/11/03 06:10:28 skotakar 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 /*Added as part of bug13037565*/
562 v_attribute1 varchar2(150) := null;
563 v_attribute2 varchar2(150) := null;
564 v_attribute3 varchar2(150) := null;
565 v_attribute4 varchar2(150) := null;
566 v_attribute5 varchar2(150) := null;
567 v_context varchar2(150) := null;
568 BEGIN
569
570 -- validate input parameters
571 if ( X_User_Je_Category_Name is NULL ) then
572 fnd_message.set_name('SQLGL', 'GL_LOAD_ROW_NO_DATA');
573 app_exception.raise_exception;
574 end if;
575
576 if (X_OWNER = 'SEED') then
577 user_id := 1;
578 end if;
579
580 begin
581 /* When je_category_name is null, we assume it to be a new row. */
582 if (X_je_category_name is null) then
583 SELECT gl_je_categories_s.NEXTVAL
584 INTO X_je_category_name
585 FROM dual;
586 raise no_data_found;
587 end if;
588
589 /* Check if the row exists in the database. If it does, retrieves
590 the creation date for update_row. */
591 /* select creation_date
592 into v_creation_date
593 from gl_je_categories
594 where je_category_name = X_je_category_name;*/
595
596 /*Modified as part of bug13037565*/
597 select creation_date,
598 attribute1,
599 attribute2,
600 attribute3,
601 attribute4,
602 attribute5,
603 context
604 into v_creation_date,
605 v_attribute1,
606 v_attribute2,
607 v_attribute3,
608 v_attribute4,
609 v_attribute5,
610 v_context
611 from gl_je_categories
612 where je_category_name = X_je_category_name;
613
614 /* Update only if force_edits is 'Y' or if user_id = 1 */
615 if ( user_id = 1 or X_Force_Edits = 'Y' ) then
616 -- update row in GL_JE_CATEGORIES_TL if present
617 GL_JE_CATEGORIES_PKG.Update_Row(
618 X_Rowid => v_rowid,
619 X_je_category_name => X_Je_Category_Name,
620 X_last_update_date => sysdate,
621 X_last_updated_by => user_id,
622 X_user_je_category_name => X_user_je_category_name,
623 X_je_category_key => nvl(X_Je_Category_Key,
624 X_Je_Category_Name),
625 X_creation_date => v_creation_date,
626 X_last_update_login => 0,
627 X_Description => X_Description,
628 /*Modified as part of bug13037565*/
629 X_Attribute1 => V_Attribute1,
630 X_Attribute2 => V_Attribute2,
631 X_Attribute3 => V_Attribute3,
632 X_Attribute4 => V_Attribute4,
633 X_Attribute5 => V_Attribute5,
634 X_context => V_Context,
635 X_Consolidation_Flag => null);
636
637 -- update FND_DOC_SEQUENCE_CATEGORIES if change is made in
638 -- the base language
639 if ( userenv('LANG') = FND_GLOBAL.BASE_LANGUAGE ) then
640 GL_JE_CATEGORIES_PKG.update_fnd_cat( x_je_category_name,
641 x_user_je_category_name,
642 x_description,
643 user_id );
644 end if;
645 end if;
646 exception
647 when NO_DATA_FOUND then
648 GL_JE_CATEGORIES_PKG.Insert_Row(
649 X_Rowid => v_rowid,
650 X_je_category_name => X_Je_Category_Name,
651 X_language => v_language,
652 X_source_lang => v_source_lang,
653 X_last_update_date => sysdate,
654 X_last_updated_by => user_id,
655 X_user_je_category_name => X_user_je_category_name,
656 X_je_category_key => Nvl(X_Je_Category_Key,
657 X_Je_Category_Name),
658 X_creation_date => sysdate,
659 X_created_by => user_id,
660 X_last_update_login => 0,
661 X_Description => X_Description,
662 X_Attribute1 => X_Attribute1,
663 X_Attribute2 => X_Attribute2,
664 X_Attribute3 => X_Attribute3,
665 X_Attribute4 => X_Attribute4,
666 X_Attribute5 => X_Attribute5,
667 X_context => X_Context,
668 X_Consolidation_Flag => null);
669 end;
670 END Load_Row;
671
672 -- ************************************************************************
673 PROCEDURE Translate_Row(
674 X_Je_Category_Name VARCHAR2,
675 X_User_Je_Category_Name VARCHAR2,
676 X_Description VARCHAR2,
677 X_Owner VARCHAR2,
678 X_Force_Edits VARCHAR2 ) IS
679 user_id number := 0;
680 BEGIN
681 if (X_OWNER = 'SEED') then
682 user_id := 1;
683 end if;
684
685 /* Update only if force_edits is 'Y' or if user_id = 1 */
686 if ( user_id = 1 or X_Force_Edits = 'Y' ) then
687 UPDATE GL_JE_CATEGORIES_TL
688 SET
689 user_je_category_name = x_user_je_category_name,
690 description = x_description,
691 last_update_date = sysdate,
692 last_updated_by = user_id,
693 last_update_login = 0,
694 source_lang = userenv('LANG')
695 WHERE userenv('LANG') in (LANGUAGE, SOURCE_LANG)
696 and je_category_name = X_je_category_name;
697
698 -- update FND_DOC_SEQUENCE_CATEGORIES if change is made in
699 -- the base language
700 if ( userenv('LANG') = FND_GLOBAL.BASE_LANGUAGE ) then
701 GL_JE_CATEGORIES_PKG.update_fnd_cat( x_je_category_name,
702 x_user_je_category_name,
703 x_description,
704 user_id );
705 end if;
706 end if;
707 /*If base language is not set to the language being uploaded, then do nothing.*/
708 if (sql%notfound) then
709 null;
710 end if;
711 END Translate_Row;
712
713 -- ************************************************************************
714
715 procedure ADD_LANGUAGE
716 is
717 begin
718
719 update GL_JE_CATEGORIES_TL T
720 set ( user_je_category_name,
721 DESCRIPTION )
722 = ( select
723 B.user_je_category_name,
724 B.DESCRIPTION
725 from gl_je_categories_tl B
726 where B.je_category_name = T.je_category_name
727 and B.LANGUAGE = T.SOURCE_LANG )
728 where ( T.je_category_name,
729 T.LANGUAGE ) in
730 ( select
731 SUBT.je_category_name,
732 SUBT.LANGUAGE
733 from gl_je_categories_tl SUBB,
734 gl_je_categories_tl SUBT
735 where SUBB.je_category_name = SUBT.je_category_name
736 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
737 and (SUBB.USER_JE_CATEGORY_NAME <> SUBT.USER_JE_CATEGORY_NAME
738 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
739 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
740 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null))
741 );
742
743 insert into gl_je_categories_tl (
744 je_category_name,
745 LAST_UPDATE_DATE,
746 LAST_UPDATED_BY,
747 CREATION_DATE,
748 CREATED_BY,
749 LAST_UPDATE_LOGIN,
750 DESCRIPTION,
751 user_je_category_name,
752 je_category_key,
753 LANGUAGE,
754 SOURCE_LANG,
755 attribute1,
756 attribute2,
757 attribute3,
758 attribute4,
759 attribute5,
760 context,
761 consolidation_flag
762 )
763 select
764 B.je_category_name,
765 B.LAST_UPDATE_DATE,
766 B.LAST_UPDATED_BY,
767 B.CREATION_DATE,
768 B.CREATED_BY,
769 B.LAST_UPDATE_LOGIN,
770 B.DESCRIPTION,
771 B.user_je_category_name,
772 B.je_category_key,
773 L.LANGUAGE_CODE,
774 B.source_lang,
775 B.attribute1,
776 B.attribute2,
777 B.attribute3,
778 B.attribute4,
779 B.attribute5,
780 B.context,
781 B.consolidation_flag
782 from gl_je_categories_tl B, FND_LANGUAGES L
783 where L.INSTALLED_FLAG in ('I', 'B')
784 and B.LANGUAGE = userenv('LANG')
785 and not exists
786 (select NULL
787 from gl_je_categories_tl T
788 where T.je_category_name = B.je_category_name
789 and T.LANGUAGE = L.LANGUAGE_CODE);
790
791 end ADD_LANGUAGE;
792
793 -- ************************************************************************
794
795 END GL_JE_CATEGORIES_PKG;