1 package body AS_INTEREST_CODES_PKG as
2 /* $Header: asxvicpb.pls 120.1 2005/11/28 01:39:53 sumahali noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_INTEREST_CODE_ID in NUMBER,
6 X_INTEREST_TYPE_ID in NUMBER,
7 X_ENABLED_FLAG in VARCHAR2,
8 X_PARENT_INTEREST_CODE_ID in NUMBER,
9 X_CATEGORY_ID in NUMBER,
10 X_CATEGORY_SET_ID in NUMBER,
11 X_ATTRIBUTE_CATEGORY in VARCHAR2,
12 X_ATTRIBUTE1 in VARCHAR2,
13 X_ATTRIBUTE2 in VARCHAR2,
14 X_ATTRIBUTE3 in VARCHAR2,
15 X_ATTRIBUTE4 in VARCHAR2,
16 X_ATTRIBUTE5 in VARCHAR2,
17 X_ATTRIBUTE6 in VARCHAR2,
18 X_ATTRIBUTE7 in VARCHAR2,
19 X_ATTRIBUTE8 in VARCHAR2,
20 X_ATTRIBUTE9 in VARCHAR2,
21 X_ATTRIBUTE10 in VARCHAR2,
22 X_ATTRIBUTE11 in VARCHAR2,
23 X_ATTRIBUTE12 in VARCHAR2,
24 X_ATTRIBUTE13 in VARCHAR2,
25 X_ATTRIBUTE14 in VARCHAR2,
26 X_ATTRIBUTE15 in VARCHAR2,
27 X_PF_ITEM_ID in NUMBER,
28 X_PF_ORGANIZATION_ID in NUMBER,
29 X_PRICE in NUMBER,
30 X_CURRENCY_CODE in VARCHAR2,
31 X_CODE in VARCHAR2,
32 X_DESCRIPTION in VARCHAR2,
33 X_CREATION_DATE in DATE,
34 X_CREATED_BY in NUMBER,
35 X_LAST_UPDATE_DATE in DATE,
36 X_LAST_UPDATED_BY in NUMBER,
37 X_LAST_UPDATE_LOGIN in NUMBER,
38 X_PROD_CAT_SET_ID in NUMBER,
39 X_PROD_CAT_ID in NUMBER
40 ) is
41 cursor C is
42 select ROWID from AS_INTEREST_CODES_B
43 where INTEREST_CODE_ID = X_INTEREST_CODE_ID;
44
45 BEGIN
46 insert into AS_INTEREST_CODES_B (
47 INTEREST_CODE_ID,
48 INTEREST_TYPE_ID,
49 ENABLED_FLAG,
50 PARENT_INTEREST_CODE_ID,
51 CATEGORY_ID,
52 CATEGORY_SET_ID,
53 PF_ITEM_ID,
54 PF_ORGANIZATION_ID,
55 PRICE,
56 CURRENCY_CODE,
57 CREATION_DATE,
58 CREATED_BY,
59 LAST_UPDATE_DATE,
60 LAST_UPDATED_BY,
61 LAST_UPDATE_LOGIN,
62 ATTRIBUTE_CATEGORY,
63 ATTRIBUTE1,
64 ATTRIBUTE2,
65 ATTRIBUTE3,
66 ATTRIBUTE4,
67 ATTRIBUTE5,
68 ATTRIBUTE6,
69 ATTRIBUTE7,
70 ATTRIBUTE8,
71 ATTRIBUTE9,
72 ATTRIBUTE10,
73 ATTRIBUTE11,
74 ATTRIBUTE12,
75 ATTRIBUTE13,
76 ATTRIBUTE14,
77 ATTRIBUTE15,
78 PRODUCT_CAT_SET_ID,
79 PRODUCT_CATEGORY_ID
80 ) values (
81 X_INTEREST_CODE_ID,
82 X_INTEREST_TYPE_ID,
83 X_ENABLED_FLAG ,
84 X_PARENT_INTEREST_CODE_ID,
85 X_CATEGORY_ID,
86 X_CATEGORY_SET_ID,
87 X_PF_ITEM_ID,
88 X_PF_ORGANIZATION_ID,
89 X_PRICE,
90 X_CURRENCY_CODE,
91 X_CREATION_DATE,
92 X_CREATED_BY,
93 X_LAST_UPDATE_DATE,
94 X_LAST_UPDATED_BY,
95 X_LAST_UPDATE_LOGIN,
96 X_ATTRIBUTE_CATEGORY ,
97 X_ATTRIBUTE1 ,
98 X_ATTRIBUTE2 ,
99 X_ATTRIBUTE3 ,
100 X_ATTRIBUTE4 ,
101 X_ATTRIBUTE5 ,
102 X_ATTRIBUTE6 ,
103 X_ATTRIBUTE7 ,
104 X_ATTRIBUTE8 ,
105 X_ATTRIBUTE9 ,
106 X_ATTRIBUTE10 ,
107 X_ATTRIBUTE11 ,
108 X_ATTRIBUTE12 ,
109 X_ATTRIBUTE13 ,
110 X_ATTRIBUTE14 ,
111 X_ATTRIBUTE15 ,
112 X_PROD_CAT_SET_ID ,
113 X_PROD_CAT_ID
114 );
115
116 -- insert into AS_INTEREST_CODES_ALL (
117 -- INTEREST_CODE_ID,
118 -- ENABLED_FLAG,
119 -- ORG_ID ,
120 -- CREATION_DATE,
121 -- CREATED_BY,
122 -- LAST_UPDATE_DATE,
123 -- LAST_UPDATED_BY,
124 -- LAST_UPDATE_LOGIN
125 -- ) values (
126 -- X_INTEREST_CODE_ID,
127 -- X_ENABLED_FLAG,
128 -- X_ORG_ID ,
129 -- X_CREATION_DATE,
130 -- X_CREATED_BY,
131 -- X_LAST_UPDATE_DATE,
132 -- X_LAST_UPDATED_BY,
133 -- X_LAST_UPDATE_LOGIN
134 -- );
135
136 insert into AS_INTEREST_CODES_TL (
137 INTEREST_CODE_ID,
138 LAST_UPDATE_DATE,
139 LAST_UPDATED_BY,
140 CREATION_DATE,
141 CREATED_BY,
142 LAST_UPDATE_LOGIN,
143 CODE,
144 DESCRIPTION,
145 LANGUAGE,
146 SOURCE_LANG
147 ) select
148 X_INTEREST_CODE_ID,
149 X_LAST_UPDATE_DATE,
150 X_LAST_UPDATED_BY,
151 X_CREATION_DATE,
152 X_CREATED_BY,
153 X_LAST_UPDATE_LOGIN,
154 ltrim(rtrim(X_CODE)),
155 X_DESCRIPTION,
156 L.LANGUAGE_CODE,
157 userenv('LANG')
158 from FND_LANGUAGES L
159 where L.INSTALLED_FLAG in ('I', 'B')
160 and not exists
161 (select NULL
162 from AS_INTEREST_CODES_TL T
163 where T.INTEREST_CODE_ID = X_INTEREST_CODE_ID
164 and T.LANGUAGE = L.LANGUAGE_CODE);
165
166 open c;
167 fetch c into X_ROWID;
168 if (c%notfound) then
169 close c;
170 raise no_data_found;
171 end if;
172 close c;
173
174 end INSERT_ROW;
175
176 procedure LOCK_ROW (
177 X_INTEREST_CODE_ID in NUMBER,
178 X_INTEREST_TYPE_ID in NUMBER,
179 X_ENABLED_FLAG in VARCHAR2,
180 X_PARENT_INTEREST_CODE_ID in NUMBER,
181 X_CATEGORY_ID in NUMBER,
182 X_CATEGORY_SET_ID in NUMBER,
183 X_ATTRIBUTE_CATEGORY in VARCHAR2,
184 X_ATTRIBUTE1 in VARCHAR2,
185 X_ATTRIBUTE2 in VARCHAR2,
186 X_ATTRIBUTE3 in VARCHAR2,
187 X_ATTRIBUTE4 in VARCHAR2,
188 X_ATTRIBUTE5 in VARCHAR2,
189 X_ATTRIBUTE6 in VARCHAR2,
190 X_ATTRIBUTE7 in VARCHAR2,
191 X_ATTRIBUTE8 in VARCHAR2,
192 X_ATTRIBUTE9 in VARCHAR2,
193 X_ATTRIBUTE10 in VARCHAR2,
194 X_ATTRIBUTE11 in VARCHAR2,
195 X_ATTRIBUTE12 in VARCHAR2,
196 X_ATTRIBUTE13 in VARCHAR2,
197 X_ATTRIBUTE14 in VARCHAR2,
198 X_ATTRIBUTE15 in VARCHAR2,
199 X_PF_ITEM_ID in NUMBER,
200 X_PF_ORGANIZATION_ID in NUMBER,
201 X_PRICE in NUMBER,
202 X_CURRENCY_CODE in VARCHAR2,
203 X_CODE in VARCHAR2,
204 X_DESCRIPTION in VARCHAR2,
205 X_PROD_CAT_SET_ID in NUMBER,
206 X_PROD_CAT_ID in NUMBER
207 ) is
208 cursor c is
209 select
210 INTEREST_TYPE_ID,
211 ENABLED_FLAG,
212 PARENT_INTEREST_CODE_ID,
213 CATEGORY_ID,
214 CATEGORY_SET_ID,
215 PF_ITEM_ID,
216 PF_ORGANIZATION_ID,
217 PRICE,
218 CURRENCY_CODE,
219 ATTRIBUTE_CATEGORY, ATTRIBUTE1,
220 ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
221 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
222 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
223 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
224 ATTRIBUTE14, ATTRIBUTE15,
225 PRODUCT_CAT_SET_ID, PRODUCT_CATEGORY_ID
226 from AS_INTEREST_CODES_B
227 where INTEREST_CODE_ID = X_INTEREST_CODE_ID
228 for update of INTEREST_CODE_ID nowait;
229
230 recinfo c%rowtype;
231
232 -- cursor c_all is
233 --select
234 --ENABLED_FLAG,
235 --ORG_ID
236 --from AS_INTEREST_CODES_ALL
237 --where INTEREST_CODE_ID = X_INTEREST_CODE_ID
238 --for update of INTEREST_CODE_ID nowait;
239 --
240 -- allinfo c_all%rowtype;
241
242 cursor c1 is select
243 CODE,
244 DESCRIPTION,
245 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
246 from AS_INTEREST_CODES_TL
247 where INTEREST_CODE_ID = X_INTEREST_CODE_ID
248 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
249 for update of INTEREST_CODE_ID nowait;
250
251 BEGIN
252 OPEN c;
253 FETCH c INTO recinfo;
254 IF (c%NOTFOUND) THEN
255 CLOSE c;
256 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
257 app_exception.raise_exception;
258 END IF;
259 CLOSE c;
260 IF (
261 (recinfo.INTEREST_TYPE_ID = X_INTEREST_TYPE_ID)
262 AND ((recinfo.PARENT_INTEREST_CODE_ID = X_PARENT_INTEREST_CODE_ID)
263 OR ((recinfo.PARENT_INTEREST_CODE_ID is null) AND
264 (X_PARENT_INTEREST_CODE_ID is null)))
265 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
266 AND ((recinfo.CATEGORY_ID = X_CATEGORY_ID)
267 OR ((recinfo.CATEGORY_ID is null) AND
268 (X_CATEGORY_ID is null)))
269 AND ((recinfo.CATEGORY_SET_ID = X_CATEGORY_SET_ID)
270 OR ((recinfo.CATEGORY_SET_ID is null) AND
271 (X_CATEGORY_SET_ID is null)))
272 AND ((recinfo.PF_ITEM_ID = X_PF_ITEM_ID)
273 OR ((recinfo.PF_ITEM_ID is null) AND (X_PF_ITEM_ID is null)))
274 AND ((recinfo.PF_ORGANIZATION_ID = X_PF_ORGANIZATION_ID)
275 OR ((recinfo.PF_ORGANIZATION_ID is null) AND
276 (X_PF_ORGANIZATION_ID is null)))
277 AND ((recinfo.PRICE = X_PRICE)
278 OR ((recinfo.PRICE is null) AND (X_PRICE is null)))
279 AND ((recinfo.CURRENCY_CODE = X_CURRENCY_CODE)
280 OR ((recinfo.CURRENCY_CODE is null) AND (X_CURRENCY_CODE is null)))
281 AND ((recinfo.ATTRIBUTE_CATEGORY= X_ATTRIBUTE_CATEGORY)
282 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND
283 (X_ATTRIBUTE_CATEGORY is null)))
284 AND ((recinfo.ATTRIBUTE1= X_ATTRIBUTE1)
285 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
286 AND ((recinfo.ATTRIBUTE2= X_ATTRIBUTE2)
287 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
288 AND ((recinfo.ATTRIBUTE3= X_ATTRIBUTE3)
289 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
290 AND ((recinfo.ATTRIBUTE4= X_ATTRIBUTE4)
291 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
292 AND ((recinfo.ATTRIBUTE5= X_ATTRIBUTE5)
293 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
294 AND ((recinfo.ATTRIBUTE6= X_ATTRIBUTE6)
295 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
296 AND ((recinfo.ATTRIBUTE7= X_ATTRIBUTE7)
297 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
298 AND ((recinfo.ATTRIBUTE8= X_ATTRIBUTE8)
299 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
300 AND ((recinfo.ATTRIBUTE9= X_ATTRIBUTE9)
301 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
302 AND ((recinfo.ATTRIBUTE10= X_ATTRIBUTE10)
303 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
304 AND ((recinfo.ATTRIBUTE11= X_ATTRIBUTE11)
305 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
306 AND ((recinfo.ATTRIBUTE12= X_ATTRIBUTE12)
307 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
308 AND ((recinfo.ATTRIBUTE13= X_ATTRIBUTE13)
309 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
310 AND ((recinfo.ATTRIBUTE14= X_ATTRIBUTE14)
311 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
312 AND ((recinfo.ATTRIBUTE15= X_ATTRIBUTE15)
313 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
314 AND ((recinfo.PRODUCT_CATEGORY_ID = X_PROD_CAT_ID)
315 OR ((recinfo.PRODUCT_CATEGORY_ID is null) AND
316 (X_PROD_CAT_ID is null)))
317 AND ((recinfo.PRODUCT_CAT_SET_ID = X_PROD_CAT_SET_ID)
318 OR ((recinfo.PRODUCT_CAT_SET_ID is null) AND
319 (X_PROD_CAT_SET_ID is null)))
320 ) THEN
321 NULL;
322 ELSE
323 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
324 app_exception.raise_exception;
325 END IF;
326
327 -- open c_all;
328 -- fetch c_all into allinfo;
329 -- if (c_all%notfound) then
330 -- close c_all;
331 -- fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
332 -- app_exception.raise_exception;
333 -- end if;
334 -- close c_all;
335 -- if (
336 -- (allinfo.ENABLED_FLAG = X_ENABLED_FLAG)
337 -- AND ((allinfo.ORG_ID = X_ORG_ID)
338 -- OR ((allinfo.ORG_ID is null) AND (X_ORG_ID is null)))
339 -- ) then
340 -- null;
341 -- else
342 -- fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
343 -- app_exception.raise_exception;
344 -- end if;
345
346 for tlinfo in c1 loop
347 if (tlinfo.BASELANG = 'Y') then
348 if ((tlinfo.CODE = X_CODE)
349 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
350 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
351 ) then
355 app_exception.raise_exception;
352 null;
353 else
354 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
356 end if;
357 end if;
358 end loop;
359 return;
360 end LOCK_ROW;
361
362 procedure UPDATE_ROW (
363 X_INTEREST_CODE_ID in NUMBER,
364 X_INTEREST_TYPE_ID in NUMBER,
365 X_ENABLED_FLAG in VARCHAR2,
366 X_PARENT_INTEREST_CODE_ID in NUMBER,
367 X_CATEGORY_ID in NUMBER,
368 X_CATEGORY_SET_ID in NUMBER,
369 X_ATTRIBUTE_CATEGORY in VARCHAR2,
370 X_ATTRIBUTE1 in VARCHAR2,
371 X_ATTRIBUTE2 in VARCHAR2,
372 X_ATTRIBUTE3 in VARCHAR2,
373 X_ATTRIBUTE4 in VARCHAR2,
374 X_ATTRIBUTE5 in VARCHAR2,
375 X_ATTRIBUTE6 in VARCHAR2,
376 X_ATTRIBUTE7 in VARCHAR2,
377 X_ATTRIBUTE8 in VARCHAR2,
378 X_ATTRIBUTE9 in VARCHAR2,
379 X_ATTRIBUTE10 in VARCHAR2,
380 X_ATTRIBUTE11 in VARCHAR2,
381 X_ATTRIBUTE12 in VARCHAR2,
382 X_ATTRIBUTE13 in VARCHAR2,
383 X_ATTRIBUTE14 in VARCHAR2,
384 X_ATTRIBUTE15 in VARCHAR2,
385 X_PF_ITEM_ID in NUMBER,
386 X_PF_ORGANIZATION_ID in NUMBER,
387 X_PRICE in NUMBER,
388 X_CURRENCY_CODE in VARCHAR2,
389 X_CODE in VARCHAR2,
390 X_DESCRIPTION in VARCHAR2,
391 X_LAST_UPDATE_DATE in DATE,
392 X_LAST_UPDATED_BY in NUMBER,
393 X_LAST_UPDATE_LOGIN in NUMBER,
394 X_PROD_CAT_SET_ID in NUMBER,
395 X_PROD_CAT_ID in NUMBER
396 ) is
397 begin
398 update
399 AS_INTEREST_CODES_B set
400 INTEREST_TYPE_ID = X_INTEREST_TYPE_ID,
401 ENABLED_FLAG = X_ENABLED_FLAG,
402 PARENT_INTEREST_CODE_ID = X_PARENT_INTEREST_CODE_ID,
403 CATEGORY_ID = X_CATEGORY_ID,
404 CATEGORY_SET_ID = X_CATEGORY_SET_ID,
405 PF_ITEM_ID = X_PF_ITEM_ID,
406 PF_ORGANIZATION_ID = X_PF_ORGANIZATION_ID,
407 PRICE = X_PRICE,
408 CURRENCY_CODE = X_CURRENCY_CODE,
409 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
410 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
411 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
412 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY ,
413 ATTRIBUTE1 = X_ATTRIBUTE1 ,
414 ATTRIBUTE2 = X_ATTRIBUTE2 ,
415 ATTRIBUTE3 = X_ATTRIBUTE3 ,
416 ATTRIBUTE4 = X_ATTRIBUTE4 ,
417 ATTRIBUTE5 = X_ATTRIBUTE5 ,
418 ATTRIBUTE6 = X_ATTRIBUTE6 ,
419 ATTRIBUTE7 = X_ATTRIBUTE7 ,
420 ATTRIBUTE8 = X_ATTRIBUTE8 ,
421 ATTRIBUTE9 = X_ATTRIBUTE9 ,
422 ATTRIBUTE10 = X_ATTRIBUTE10 ,
423 ATTRIBUTE11 = X_ATTRIBUTE11 ,
424 ATTRIBUTE12 = X_ATTRIBUTE12 ,
425 ATTRIBUTE13 = X_ATTRIBUTE13 ,
426 ATTRIBUTE14 = X_ATTRIBUTE14 ,
427 ATTRIBUTE15 = X_ATTRIBUTE15 ,
428 PRODUCT_CAT_SET_ID = X_PROD_CAT_SET_ID ,
429 PRODUCT_CATEGORY_ID = X_PROD_CAT_ID
430 where INTEREST_CODE_ID = X_INTEREST_CODE_ID;
431
432 if (sql%notfound) then
433 raise no_data_found;
434 end if;
435
436 -- update AS_INTEREST_CODES_ALL set
437 -- ENABLED_FLAG = X_ENABLED_FLAG,
438 -- ORG_ID = X_ORG_ID,
439 -- LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
440 -- LAST_UPDATED_BY = X_LAST_UPDATED_BY,
441 -- LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
442 -- where INTEREST_CODE_ID = X_INTEREST_CODE_ID;
443
444 if (sql%notfound) then
445 raise no_data_found;
446 end if;
447
448 update AS_INTEREST_CODES_TL set
449 CODE = X_CODE,
450 DESCRIPTION = X_DESCRIPTION,
451 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
452 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
453 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
454 SOURCE_LANG = userenv('LANG')
455 where INTEREST_CODE_ID = X_INTEREST_CODE_ID
456 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
457
458 if (sql%notfound) then
459 raise no_data_found;
460 end if;
461 end UPDATE_ROW;
462
463 procedure DELETE_ROW (
464 X_INTEREST_CODE_ID in NUMBER
465 ) is
466 begin
467 delete from AS_INTEREST_CODES_TL
468 where INTEREST_CODE_ID = X_INTEREST_CODE_ID;
469
470 if (sql%notfound) then
471 raise no_data_found;
472 end if;
473
474 --delete from AS_INTEREST_CODES_ALL
475 --where INTEREST_CODE_ID = X_INTEREST_CODE_ID;
476
477 if (sql%notfound) then
478 raise no_data_found;
479 end if;
480
481 delete from AS_INTEREST_CODES_B
482 where INTEREST_CODE_ID = X_INTEREST_CODE_ID;
483
484 if (sql%notfound) then
485 raise no_data_found;
486 end if;
487 end DELETE_ROW;
488
489 procedure TRANSLATE_ROW (
490 X_INTEREST_CODE_ID in NUMBER,
491 X_CODE in VARCHAR2,
492 X_DESCRIPTION in VARCHAR2,
493 X_OWNER in VARCHAR2)
494 IS
495 begin
496 -- only update rows that have not been altered by user
497 update AS_INTEREST_CODES_TL
498 set CODE=X_CODE,
499 DESCRIPTION=X_DESCRIPTION,
500 source_lang = userenv('LANG'),
501 last_update_date = sysdate,
502 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
503 last_update_login = 0
504 where INTEREST_CODE_ID = X_INTEREST_CODE_ID
505 and userenv('LANG') in (language, source_lang);
506 end TRANSLATE_ROW;
507
508 procedure ADD_LANGUAGE
509 is
510 begin
511 delete from AS_INTEREST_CODES_TL T
512 where not exists
513 (select NULL
514 from AS_INTEREST_CODES_B B
515 where B.INTEREST_CODE_ID = T.INTEREST_CODE_ID
516 );
517
518 update AS_INTEREST_CODES_TL T set (
519 CODE,
520 DESCRIPTION
521 ) = (select
522 B.CODE,
523 B.DESCRIPTION
524 from AS_INTEREST_CODES_TL B
528 T.INTEREST_CODE_ID,
525 where B.INTEREST_CODE_ID = T.INTEREST_CODE_ID
526 and B.LANGUAGE = T.SOURCE_LANG)
527 where (
529 T.LANGUAGE
530 ) in (select
531 SUBT.INTEREST_CODE_ID,
532 SUBT.LANGUAGE
533 from AS_INTEREST_CODES_TL SUBB, AS_INTEREST_CODES_TL SUBT
534 where SUBB.INTEREST_CODE_ID = SUBT.INTEREST_CODE_ID
535 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
536 and (SUBB.CODE <> SUBT.CODE
537 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
538 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
539 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
540 ));
541
542 insert into AS_INTEREST_CODES_TL (
543 INTEREST_CODE_ID,
544 LAST_UPDATE_DATE,
545 LAST_UPDATED_BY,
546 CREATION_DATE,
547 CREATED_BY,
548 LAST_UPDATE_LOGIN,
549 CODE,
550 DESCRIPTION,
551 LANGUAGE,
552 SOURCE_LANG
553 ) select
554 B.INTEREST_CODE_ID,
555 B.LAST_UPDATE_DATE,
556 B.LAST_UPDATED_BY,
557 B.CREATION_DATE,
558 B.CREATED_BY,
559 B.LAST_UPDATE_LOGIN,
560 B.CODE,
561 B.DESCRIPTION,
562 L.LANGUAGE_CODE,
563 B.SOURCE_LANG
564 from AS_INTEREST_CODES_TL B, FND_LANGUAGES L
565 where L.INSTALLED_FLAG in ('I', 'B')
566 and B.LANGUAGE = userenv('LANG')
567 and not exists
568 (select NULL
569 from AS_INTEREST_CODES_TL T
570 where T.INTEREST_CODE_ID = B.INTEREST_CODE_ID
571 and T.LANGUAGE = L.LANGUAGE_CODE);
572 end ADD_LANGUAGE;
573
574 end AS_INTEREST_CODES_PKG;
[Home] [Help]