1 package body EGO_CATG_MAP_PKG as
2 /* $Header: EGOCTMPB.pls 120.1 2005/12/08 01:55:50 lparihar noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_CATG_MAP_ID in NUMBER,
7 X_SOURCE_CATG_SET_ID in NUMBER,
8 X_TARGET_CATG_SET_ID in NUMBER,
9 X_ENABLED_FLAG in VARCHAR2,
10 X_CATG_MAP_NAME in VARCHAR2,
11 X_CATG_MAP_DESC in VARCHAR2,
12 X_CREATION_DATE in DATE,
13 X_CREATED_BY in NUMBER,
14 X_LAST_UPDATE_DATE in DATE,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER
17 ) is
18 cursor C is select ROWID from EGO_CATG_MAP_HDRS_B
19 where CATG_MAP_ID = X_CATG_MAP_ID
20 ;
21 begin
22 insert into EGO_CATG_MAP_HDRS_B (
23 CATG_MAP_ID,
24 SOURCE_CATG_SET_ID,
25 TARGET_CATG_SET_ID,
26 ENABLED_FLAG,
27 CREATION_DATE,
28 CREATED_BY,
29 LAST_UPDATE_DATE,
30 LAST_UPDATED_BY,
31 LAST_UPDATE_LOGIN
32 ) values (
33 X_CATG_MAP_ID,
34 X_SOURCE_CATG_SET_ID,
35 X_TARGET_CATG_SET_ID,
36 X_ENABLED_FLAG,
37 X_CREATION_DATE,
38 X_CREATED_BY,
39 X_LAST_UPDATE_DATE,
40 X_LAST_UPDATED_BY,
41 X_LAST_UPDATE_LOGIN
42 );
43
44 insert into EGO_CATG_MAP_HDRS_TL (
45 CATG_MAP_ID,
46 CATG_MAP_NAME,
47 CATG_MAP_DESC,
48 CREATION_DATE,
49 CREATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY,
52 LAST_UPDATE_LOGIN,
53 LANGUAGE,
54 SOURCE_LANG
55 ) select
56 X_CATG_MAP_ID,
57 X_CATG_MAP_NAME,
58 X_CATG_MAP_DESC,
59 X_CREATION_DATE,
60 X_CREATED_BY,
61 X_LAST_UPDATE_DATE,
62 X_LAST_UPDATED_BY,
63 X_LAST_UPDATE_LOGIN,
64 L.LANGUAGE_CODE,
65 userenv('LANG')
66 from FND_LANGUAGES L
67 where L.INSTALLED_FLAG in ('I', 'B')
68 and not exists
69 (select NULL
70 from EGO_CATG_MAP_HDRS_TL T
71 where T.CATG_MAP_ID = X_CATG_MAP_ID
72 and T.LANGUAGE = L.LANGUAGE_CODE);
73 open c;
74 fetch c into X_ROWID;
75 if (c%notfound) then
76 close c;
77 raise no_data_found;
78 end if;
79 close c;
80
81 end INSERT_ROW;
82
83
84 procedure LOCK_ROW (
85 X_CATG_MAP_ID in NUMBER,
86 X_SOURCE_CATG_SET_ID in NUMBER,
87 X_TARGET_CATG_SET_ID in NUMBER,
88 X_ENABLED_FLAG in VARCHAR2,
89 X_CATG_MAP_NAME in VARCHAR2,
90 X_CATG_MAP_DESC in VARCHAR2
91 ) is
92 cursor c is select
93 SOURCE_CATG_SET_ID,
94 TARGET_CATG_SET_ID,
95 ENABLED_FLAG
96 from EGO_CATG_MAP_HDRS_B
97 where CATG_MAP_ID = X_CATG_MAP_ID
98 for update of CATG_MAP_ID nowait;
99 recinfo c%rowtype;
100
101 cursor c1 is select
102 CATG_MAP_NAME,
103 CATG_MAP_DESC,
104 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
105 from EGO_CATG_MAP_HDRS_TL
106 where CATG_MAP_ID = X_CATG_MAP_ID
107 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
108 for update of CATG_MAP_ID nowait;
109 begin
110 open c;
111 fetch c into recinfo;
112 if (c%notfound) then
113 close c;
114 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
115 app_exception.raise_exception;
116 end if;
117 close c;
118 if ( ((recinfo.SOURCE_CATG_SET_ID = X_SOURCE_CATG_SET_ID)
119 OR ((recinfo.SOURCE_CATG_SET_ID is null) AND (X_SOURCE_CATG_SET_ID is null)))
120 AND ((recinfo.TARGET_CATG_SET_ID = X_TARGET_CATG_SET_ID)
121 OR ((recinfo.TARGET_CATG_SET_ID is null) AND (X_TARGET_CATG_SET_ID is null)))
122 AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
123 OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
124 ) then
125 null;
126 else
127 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
128 app_exception.raise_exception;
129 end if;
130
131 for tlinfo in c1 loop
132 if (tlinfo.BASELANG = 'Y') then
133 if ( ((tlinfo.CATG_MAP_NAME = X_CATG_MAP_NAME)
134 OR ((tlinfo.CATG_MAP_NAME is null) AND (X_CATG_MAP_NAME is null)))
135 AND ((tlinfo.CATG_MAP_DESC = X_CATG_MAP_DESC)
136 OR ((tlinfo.CATG_MAP_DESC is null) AND (X_CATG_MAP_DESC is null)))
137 ) then
138 null;
139 else
140 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
141 app_exception.raise_exception;
142 end if;
143 end if;
144 end loop;
145 return;
146 end LOCK_ROW;
147
148
149 procedure UPDATE_ROW (
150 X_CATG_MAP_ID in NUMBER,
151 X_SOURCE_CATG_SET_ID in NUMBER,
152 X_TARGET_CATG_SET_ID in NUMBER,
153 X_ENABLED_FLAG in VARCHAR2,
154 X_CATG_MAP_NAME in VARCHAR2,
155 X_CATG_MAP_DESC in VARCHAR2,
156 X_LAST_UPDATE_DATE in DATE,
157 X_LAST_UPDATED_BY in NUMBER,
158 X_LAST_UPDATE_LOGIN in NUMBER
159 ) is
160 BEGIN
161 update EGO_CATG_MAP_HDRS_B set
162 SOURCE_CATG_SET_ID = X_SOURCE_CATG_SET_ID,
163 TARGET_CATG_SET_ID = X_TARGET_CATG_SET_ID,
164 ENABLED_FLAG = X_ENABLED_FLAG,
165 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
166 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
167 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
168 where CATG_MAP_ID = X_CATG_MAP_ID;
169
170 if (sql%notfound) then
171 raise no_data_found;
172 end if;
173
174 update EGO_CATG_MAP_HDRS_TL set
175 CATG_MAP_NAME = X_CATG_MAP_NAME,
176 CATG_MAP_DESC = X_CATG_MAP_DESC,
177 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
178 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
179 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
180 SOURCE_LANG = userenv('LANG')
181 where CATG_MAP_ID = X_CATG_MAP_ID
182 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
183
184 if (sql%notfound) then
185 raise no_data_found;
186 end if;
187 end UPDATE_ROW;
188
189
190 procedure DELETE_ROW (
191 X_CATG_MAP_ID in NUMBER
192 ) is
193 begin
194 delete from EGO_CATG_MAP_HDRS_TL
195 where CATG_MAP_ID = X_CATG_MAP_ID;
196
197 if (sql%notfound) then
198 raise no_data_found;
199 end if;
200
201 delete from EGO_CATG_MAP_HDRS_B
202 where CATG_MAP_ID = X_CATG_MAP_ID;
203
204 if (sql%notfound) then
205 raise no_data_found;
206 end if;
207 end DELETE_ROW;
208
209 PROCEDURE Load_Row
210 (
211 X_CATG_MAP_ID in NUMBER,
212 X_SOURCE_CATG_SET_NAME in VARCHAR2,
213 X_TARGET_CATG_SET_NAME in VARCHAR2,
214 X_ENABLED_FLAG in VARCHAR2,
215 X_OWNER in VARCHAR2,
216 X_LAST_UPDATE_DATE in VARCHAR2,
217 X_CATG_MAP_NAME in VARCHAR2,
218 X_CATG_MAP_DESC in VARCHAR2
219 ) IS
220 l_catg_map_id NUMBER;
221 l_source_catg_set_id NUMBER;
222 l_target_catg_set_id NUMBER;
223 l_current_user_id NUMBER := EGO_SCTX.Get_User_Id();
224 l_current_login_id NUMBER := FND_GLOBAL.Login_Id;
225
226
227 CURSOR get_catg_map_id(l_source_catg_set_id NUMBER, l_target_catg_set_id NUMBER)
228 IS
229 SELECT CATG_MAP_ID
230 FROM EGO_CATG_MAP_HDRS_B
231 WHERE SOURCE_CATG_SET_ID = l_source_catg_set_id
232 AND TARGET_CATG_SET_ID = l_target_catg_set_id;
233
234 BEGIN
235
236 BEGIN
237 -- getting the source catg set id by passing the source category_set_name
238 SELECT CATEGORY_SET_ID SOURCE_CATG_SET_ID
239 INTO l_source_catg_set_id
240 FROM MTL_CATEGORY_SETS
241 WHERE CATEGORY_SET_NAME = X_SOURCE_CATG_SET_NAME;
242
243 EXCEPTION
244 WHEN no_data_found THEN
245 fnd_message.set_name('FND','GENERIC-INTERNAL ERROR');
246 fnd_message.set_token('ROUTINE','Category Mapping');
247 fnd_message.set_token('REASON','Source Category Set ' || X_SOURCE_CATG_SET_NAME || ' does not exist ' );
248 app_exception.raise_exception;
249 END;
250
251 BEGIN
252 -- getting the target catg set id by passing the target category_set_name
253 SELECT CATEGORY_SET_ID TARGET_CATG_SET_ID
254 INTO l_target_catg_set_id
255 FROM MTL_CATEGORY_SETS
256 WHERE CATEGORY_SET_NAME = X_TARGET_CATG_SET_NAME;
257
258 EXCEPTION
259 WHEN no_data_found THEN
260 fnd_message.set_name('FND','GENERIC-INTERNAL ERROR');
261 fnd_message.set_token('ROUTINE','Category Mapping');
262 fnd_message.set_token('REASON','Target Category Set ' || X_TARGET_CATG_SET_NAME || ' does not exist ' );
263 app_exception.raise_exception;
264 END;
265
266 -- trying to findout whether the catg_map id exists for the source and target category sets
267 OPEN get_catg_map_id(l_source_catg_set_id, l_target_catg_set_id);
268 -- if it doesn't exist, inserting in to the ego_catg_map_header table
269 FETCH get_catg_map_id INTO l_catg_map_id;
270 if (get_catg_map_id%NOTFOUND) THEN
271 select EGO_CATG_MAPS_S.nextval into l_catg_map_id from dual;
272
273 INSERT INTO EGO_CATG_MAP_HDRS_B
274 (
275 CATG_MAP_ID,
276 SOURCE_CATG_SET_ID,
277 TARGET_CATG_SET_ID,
278 ENABLED_FLAG,
279 CREATED_BY,
280 CREATION_DATE,
281 LAST_UPDATED_BY,
282 LAST_UPDATE_DATE,
283 LAST_UPDATE_LOGIN
284 ) VALUES
285 (
286 l_catg_map_id,
287 l_source_catg_set_id,
288 l_target_catg_set_id,
289 X_ENABLED_FLAG,
290 l_current_user_id,
291 sysdate,
292 l_current_user_id,
293 sysdate,
294 l_current_login_id
295 );
296
297 INSERT INTO EGO_CATG_MAP_HDRS_TL
298 (
299 CATG_MAP_ID,
300 LANGUAGE,
301 SOURCE_LANG,
302 CATG_MAP_NAME,
303 CATG_MAP_DESC,
304 CREATED_BY,
305 CREATION_DATE,
306 LAST_UPDATED_BY,
307 LAST_UPDATE_DATE,
308 LAST_UPDATE_LOGIN)
309 SELECT
310 l_catg_map_id,
311 L.LANGUAGE_CODE,
312 USERENV('LANG'),
313 X_CATG_MAP_NAME,
314 X_CATG_MAP_DESC,
315 l_current_user_id,
316 sysdate,
317 l_current_user_id,
318 sysdate,
319 l_current_login_id
320 FROM FND_LANGUAGES L
321 WHERE L.INSTALLED_FLAG IN ('I','B')
322 AND NOT EXISTS(SELECT 'X' FROM EGO_CATG_MAP_HDRS_TL A
323 WHERE A.CATG_MAP_ID = l_catg_map_id
324 AND A.LANGUAGE = L.LANGUAGE_CODE);
325 ELSE
326
327 -- if it does exist updating the enabled flag and description
328 UPDATE EGO_CATG_MAP_HDRS_B
329 SET ENABLED_FLAG = X_ENABLED_FLAG,
330 LAST_UPDATED_BY = l_current_user_id,
331 LAST_UPDATE_DATE = sysdate,
332 LAST_UPDATE_LOGIN = l_current_login_id
333 WHERE SOURCE_CATG_SET_ID = l_source_catg_set_id
334 AND TARGET_CATG_SET_ID = l_target_catg_set_id
335 AND CATG_MAP_ID = l_catg_map_id;
336
337 UPDATE EGO_CATG_MAP_HDRS_TL
338 SET CATG_MAP_DESC = X_CATG_MAP_DESC,
339 LAST_UPDATED_BY = l_current_user_id,
340 LAST_UPDATE_DATE = sysdate,
341 LAST_UPDATE_LOGIN = l_current_login_id
342 WHERE CATG_MAP_ID = l_catg_map_id
343 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
344 END IF;
345 CLOSE get_catg_map_id;
346
347 END Load_Row;
348
349
350 PROCEDURE Load_Row
351 (
352 X_CATG_MAP_ID in NUMBER,
353 X_SOURCE_CATG_SET_NAME in VARCHAR2,
354 X_TARGET_CATG_SET_NAME in VARCHAR2,
355 X_OWNER in VARCHAR2,
356 X_LAST_UPDATE_DATE in VARCHAR2,
357 X_SOURCE_CATG_NAME in VARCHAR2,
358 X_TARGET_CATG_NAME in VARCHAR2
359 ) IS
360
361 l_source_catg_id NUMBER;
362 l_target_catg_id NUMBER;
363 l_current_user_id NUMBER := EGO_SCTX.Get_User_Id();
364 l_current_login_id NUMBER := FND_GLOBAL.Login_Id;
365 l_source_catg_set_id NUMBER;
366 l_target_catg_set_id NUMBER;
367 l_catg_map_id NUMBER;
368 l_count NUMBER;
369
370 CURSOR get_catg_map_id(l_source_catg_set_id NUMBER, l_target_catg_set_id NUMBER)
371 IS
372 SELECT CATG_MAP_ID
373 FROM EGO_CATG_MAP_HDRS_B
374 WHERE SOURCE_CATG_SET_ID = l_source_catg_set_id
375 AND TARGET_CATG_SET_ID = l_target_catg_set_id;
376
377 CURSOR is_catg_map_dtls_exists(l_catg_map_id NUMBER,
378 l_source_catg_id NUMBER,
379 l_target_catg_id NUMBER)
380 IS
381 SELECT 1
382 FROM EGO_CATG_MAP_DTLS
383 WHERE CATG_MAP_ID = l_catg_map_id
384 AND SOURCE_CATG_ID = l_source_catg_id
385 AND TARGET_CATG_ID = l_target_catg_id
386 AND ROWNUM = 1;
387
388 BEGIN
389
390 -- getting the source catg set id by passing the source category_set_name
391 SELECT B.CATEGORY_SET_ID SOURCE_CATG_SET_ID
392 INTO l_source_catg_set_id
393 FROM MTL_CATEGORY_SETS B
394 WHERE B.CATEGORY_SET_NAME = X_SOURCE_CATG_SET_NAME;
395
396 -- getting the target catg set id by passing the target category_set_name
397 SELECT CATEGORY_SET_ID TARGET_CATG_SET_ID
398 INTO l_target_catg_set_id
399 FROM MTL_CATEGORY_SETS
400 WHERE CATEGORY_SET_NAME = X_TARGET_CATG_SET_NAME;
401
402 -- getting the source category id by passing the source category_name
403 BEGIN
404 SELECT A.CATEGORY_ID SOURCE_CATG_ID
405 INTO l_source_catg_id
406 FROM MTL_CATEGORIES_B_KFV A,
407 MTL_CATEGORY_SETS B
408 WHERE B.CATEGORY_SET_ID = l_source_catg_set_id
409 AND A.STRUCTURE_ID = B.STRUCTURE_ID
410 AND A.CONCATENATED_SEGMENTS = X_SOURCE_CATG_NAME;
411 EXCEPTION
412 WHEN no_data_found THEN
413 fnd_message.set_name('FND','GENERIC-INTERNAL ERROR');
414 fnd_message.set_token('ROUTINE','Category Mapping');
415 fnd_message.set_token('REASON','Source Category ' || X_SOURCE_CATG_NAME || ' does not exist under catalog ' || X_SOURCE_CATG_SET_NAME );
416 -- app_exception.raise_exception;
417 END;
418
419 -- getting the target category id by passing the target category_name
420 BEGIN
421
422 SELECT A.CATEGORY_ID TARGET_CATG_ID
423 INTO l_target_catg_id
424 FROM MTL_CATEGORIES_B_KFV A,
425 MTL_CATEGORY_SETS B
426 WHERE B.CATEGORY_SET_ID = l_target_catg_set_id
427 AND A.STRUCTURE_ID = B.STRUCTURE_ID
428 AND A.CONCATENATED_SEGMENTS = X_TARGET_CATG_NAME;
429 EXCEPTION
430 WHEN no_data_found THEN
431 fnd_message.set_name('FND','GENERIC-INTERNAL ERROR');
432 fnd_message.set_token('ROUTINE','Category Mapping');
433 fnd_message.set_token('REASON','Target Category ' || X_TARGET_CATG_NAME || ' does not exist under catalog ' || X_TARGET_CATG_SET_NAME);
434 -- app_exception.raise_exception;
435 END;
436
437
438 -- Getting the category map id based on the source and the target catg sets
439 OPEN get_catg_map_id(l_source_catg_set_id,
440 l_target_catg_set_id);
441 FETCH get_catg_map_id INTO l_catg_map_id;
442 CLOSE get_catg_map_id;
443
444 OPEN is_catg_map_dtls_exists(l_catg_map_id,
445 l_source_catg_id,
446 l_target_catg_id);
447
448 -- if it doesn't exists, inserting the data in to the ego_catg_map_dtls table
449 FETCH is_catg_map_dtls_exists INTO l_count;
450 if (is_catg_map_dtls_exists%NOTFOUND) THEN
451
452
453 BEGIN
454 INSERT INTO EGO_CATG_MAP_DTLS
455 (
456 CATG_MAP_ID,
457 SOURCE_CATG_ID,
458 TARGET_CATG_ID,
459 CREATED_BY,
460 CREATION_DATE,
461 LAST_UPDATED_BY,
462 LAST_UPDATE_DATE,
463 LAST_UPDATE_LOGIN
464 ) VALUES
465 (
466 l_catg_map_id,
467 l_source_catg_id,
468 l_target_catg_id,
469 l_current_user_id,
470 sysdate,
471 l_current_user_id,
472 sysdate,
473 l_current_login_id
474 );
475
476 EXCEPTION
477 WHEN OTHERS THEN
478 null;
479 END;
480
481 END IF;
482 CLOSE is_catg_map_dtls_exists;
483
484 END Load_Row;
485
486 PROCEDURE Translate_Row
487 (
488 X_CATG_MAP_ID in NUMBER,
489 X_SOURCE_CATG_SET_NAME in VARCHAR2,
490 X_TARGET_CATG_SET_NAME in VARCHAR2,
491 X_ENABLED_FLAG in VARCHAR2,
492 X_OWNER in VARCHAR2,
493 X_LAST_UPDATE_DATE in VARCHAR2,
494 X_CATG_MAP_NAME in VARCHAR2,
495 X_CATG_MAP_DESC in VARCHAR2
496 ) IS
497
498 f_luby NUMBER; -- entity owner in file
499 f_ludate DATE; -- entity update date in file
500 db_luby NUMBER; -- entity owner in db
501 db_ludate DATE; -- entity update date in db
502
503 BEGIN
504
505 -- Translate owner to file_last_updated_by
506 f_luby := fnd_load_util.owner_id(x_owner);
507
508 -- Translate char last_update_date to date
509 f_ludate := SYSDATE;
510
511 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE
512 INTO db_luby, db_ludate
513 FROM EGO_CATG_MAP_HDRS_TL TL
514 WHERE TL.catg_map_id = X_CATG_MAP_ID
515 AND userenv('LANG') IN (language, source_lang);
516
517 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
518 db_ludate, NULL)) THEN
519
520 UPDATE EGO_CATG_MAP_HDRS_TL TL
521 SET TL.CATG_MAP_NAME = NVL(x_CATG_MAP_NAME, CATG_MAP_NAME)
522 , TL.CATG_MAP_DESC = NVL(x_CATG_MAP_DESC, CATG_MAP_DESC)
523 , last_update_date = db_ludate
524 , last_updated_by = db_luby
525 , last_update_login = 0
526 , source_lang = userenv('LANG')
527 WHERE TL.catg_map_id = X_CATG_MAP_ID
528 AND userenv('LANG') IN (language, source_lang);
529
530 END IF;
531 END Translate_Row;
532
533
534
535 procedure ADD_LANGUAGE
536 is
537 begin
538 delete from EGO_CATG_MAP_HDRS_TL T
539 where not exists
540 (select NULL
541 from EGO_CATG_MAP_HDRS_B B
542 where B.CATG_MAP_ID = T.CATG_MAP_ID
543 );
544
545 update EGO_CATG_MAP_HDRS_TL T set (
546 CATG_MAP_NAME,
547 CATG_MAP_DESC
548 ) = (select
549 B.CATG_MAP_NAME,
550 B.CATG_MAP_DESC
551 from EGO_CATG_MAP_HDRS_TL B
552 where B.CATG_MAP_ID = T.CATG_MAP_ID
553 and B.LANGUAGE = T.SOURCE_LANG)
554 where (
555 T.CATG_MAP_ID,
556 T.LANGUAGE
557 ) in (select
558 SUBT.CATG_MAP_ID,
559 SUBT.LANGUAGE
560 from EGO_CATG_MAP_HDRS_TL SUBB, EGO_CATG_MAP_HDRS_TL SUBT
561 where SUBB.CATG_MAP_ID = SUBT.CATG_MAP_ID
562 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
563 and (SUBB.CATG_MAP_NAME <> SUBT.CATG_MAP_NAME
564 or (SUBB.CATG_MAP_NAME is null and SUBT.CATG_MAP_NAME is not null)
565 or (SUBB.CATG_MAP_NAME is not null and SUBT.CATG_MAP_NAME is null)
566 or SUBB.CATG_MAP_DESC <> SUBT.CATG_MAP_DESC
567 or (SUBB.CATG_MAP_DESC is null and SUBT.CATG_MAP_DESC is not null)
568 or (SUBB.CATG_MAP_DESC is not null and SUBT.CATG_MAP_DESC is null)
569 ));
570
571 insert into EGO_CATG_MAP_HDRS_TL (
572 CATG_MAP_ID,
573 CATG_MAP_NAME,
574 CATG_MAP_DESC,
575 CREATION_DATE,
576 CREATED_BY,
577 LAST_UPDATE_DATE,
578 LAST_UPDATED_BY,
579 LAST_UPDATE_LOGIN,
580 LANGUAGE,
581 SOURCE_LANG
582 ) select
583 B.CATG_MAP_ID,
584 B.CATG_MAP_NAME,
585 B.CATG_MAP_DESC,
586 B.CREATION_DATE,
587 B.CREATED_BY,
588 B.LAST_UPDATE_DATE,
589 B.LAST_UPDATED_BY,
590 B.LAST_UPDATE_LOGIN,
591 L.LANGUAGE_CODE,
592 B.SOURCE_LANG
593 from EGO_CATG_MAP_HDRS_TL B, FND_LANGUAGES L
594 where L.INSTALLED_FLAG in ('I', 'B')
595 and B.LANGUAGE = userenv('LANG')
596 and not exists
597 (select NULL
598 from EGO_CATG_MAP_HDRS_TL T
599 where T.CATG_MAP_ID = B.CATG_MAP_ID
600 and T.LANGUAGE = L.LANGUAGE_CODE);
601 end ADD_LANGUAGE;
602
603 end EGO_CATG_MAP_PKG;
[Home] [Help]