[Home] [Help]
PACKAGE BODY: APPS.AMS_CLIK_THRU_ACT_PKG
Source
1 package body AMS_CLIK_THRU_ACT_PKG as
2 /* $Header: amslctab.pls 115.5 2003/10/30 20:53:54 rrajesh noship $ */
3 procedure INSERT_ROW (
4 X_ROWID IN OUT NOCOPY VARCHAR2,
5 X_ACTION_ID in NUMBER,
6 X_ACTION_CODE in VARCHAR2,
7 X_EXECUTABLE_NAME in VARCHAR2,
8 X_SERVER_URL in VARCHAR2,
9 X_PROFILE_FOR_SERVER_URL in VARCHAR2,
10 X_DEPENDS_ON_APP in VARCHAR2,
11 X_APPLICATION_ID in NUMBER,
12 X_APPLICABLE_FOR in VARCHAR2,
13 X_TRACK_FLAG in VARCHAR2,
14 X_ENABLED_FLAG in VARCHAR2,
15 X_DYNAMIC_PARAMS_FLAG in VARCHAR2,
16 X_ADHOC_PARAMS_FLAG in VARCHAR2,
17 X_JAVA_CLASS_NAME in VARCHAR2,
18 X_NEW_JAVA_CLASS_NAME in VARCHAR2,
19 X_ACTION_CODE_MEANING in VARCHAR2,
20 X_DESCRIPTION in VARCHAR2,
21 X_LAST_UPDATE_DATE in DATE,
22 X_LAST_UPDATED_BY in NUMBER,
23 X_CREATION_DATE in DATE,
24 X_CREATED_BY in NUMBER,
25 X_LAST_UPDATE_LOGIN in NUMBER,
26 X_OBJECT_VERSION_NUMBER in NUMBER
27 ) is
28 cursor c is select ROWID from AMS_CLIK_THRU_ACTIONS_B
29 where ACTION_ID = X_ACTION_ID
30 ;
31 cursor ctl is select ROWID from AMS_CLIK_THRU_ACTIONS_TL
32 where ACTION_ID = X_ACTION_ID
33 ;
34 begin
35 insert into AMS_CLIK_THRU_ACTIONS_B (
36 ACTION_ID
37 ,ACTION_CODE
38 ,EXECUTABLE_NAME
39 ,SERVER_URL
40 ,PROFILE_FOR_SERVER_URL
41 ,DEPENDS_ON_APP
42 ,APPLICATION_ID
43 ,APPLICABLE_FOR
44 ,TRACK_FLAG
45 ,ENABLED_FLAG
46 ,DYNAMIC_PARAMS_FLAG
47 ,ADHOC_PARAMS_FLAG
48 ,JAVA_CLASS_NAME
49 ,NEW_JAVA_CLASS_NAME
50 ,LAST_UPDATE_DATE
51 ,LAST_UPDATED_BY
52 ,CREATION_DATE
53 ,CREATED_BY
54 ,LAST_UPDATE_LOGIN
55 ,OBJECT_VERSION_NUMBER
56 ) values (
57 X_ACTION_ID
58 ,X_ACTION_CODE
59 ,X_EXECUTABLE_NAME
60 ,X_SERVER_URL
61 ,X_PROFILE_FOR_SERVER_URL
62 ,X_DEPENDS_ON_APP
63 ,X_APPLICATION_ID
64 ,X_APPLICABLE_FOR
65 ,X_TRACK_FLAG
66 ,X_ENABLED_FLAG
67 ,X_DYNAMIC_PARAMS_FLAG
68 ,X_ADHOC_PARAMS_FLAG
69 ,X_JAVA_CLASS_NAME
70 ,X_NEW_JAVA_CLASS_NAME
71 ,DECODE(X_LAST_UPDATE_DATE,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,X_LAST_UPDATE_DATE)
72 ,DECODE(X_LAST_UPDATED_BY,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,X_LAST_UPDATED_BY)
73 ,DECODE(X_CREATION_DATE,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,X_CREATION_DATE)
74 ,DECODE(X_CREATED_BY,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,X_CREATED_BY)
75 ,DECODE(X_LAST_UPDATE_LOGIN,FND_API.G_MISS_NUM,FND_GLOBAL.login_id,NULL,FND_GLOBAL.user_id,X_LAST_UPDATE_LOGIN)
76 ,X_OBJECT_VERSION_NUMBER
77 );
78
79 open c;
80 fetch c into X_ROWID;
81 if (c%notfound) then
82 close c;
83 raise no_data_found;
84 end if;
85 close c;
86
87 insert into AMS_CLIK_THRU_ACTIONS_TL (
88 ACTION_ID
89 ,ACTION_CODE_MEANING
90 ,DESCRIPTION
91 ,LANGUAGE
92 ,SOURCE_LANG
93 ,LAST_UPDATE_DATE
94 ,LAST_UPDATED_BY
95 ,CREATION_DATE
96 ,CREATED_BY
97 ,LAST_UPDATE_LOGIN
98 )
99 SELECT
100 X_ACTION_ID
101 ,X_ACTION_CODE_MEANING
102 ,DECODE(X_DESCRIPTION,FND_API.G_MISS_CHAR,NULL,X_DESCRIPTION)
103 ,l.language_code
104 ,USERENV('lang')
105 ,DECODE(X_LAST_UPDATE_DATE,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,X_LAST_UPDATE_DATE)
106 ,DECODE(X_LAST_UPDATED_BY,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,X_LAST_UPDATED_BY)
107 ,DECODE(X_CREATION_DATE,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,X_CREATION_DATE)
108 ,DECODE(X_CREATED_BY,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,X_CREATED_BY)
109 ,DECODE(X_LAST_UPDATE_LOGIN,FND_API.G_MISS_NUM,FND_GLOBAL.login_id,NULL,FND_GLOBAL.user_id,X_LAST_UPDATE_LOGIN)
110 FROM fnd_languages l
111 WHERE l.installed_flag IN ('I', 'B')
112 AND NOT EXISTS
113 (SELECT NULL
114 FROM AMS_CLIK_THRU_ACTIONS_TL T
115 WHERE T.ACTION_ID = X_ACTION_ID
116 AND T.LANGUAGE = l.language_code);
117
118 open ctl;
119 fetch ctl into X_ROWID;
120 if (ctl%notfound) then
121 close ctl;
122 raise no_data_found;
123 end if;
124 close ctl;
125
126 end INSERT_ROW;
127
128 procedure LOCK_ROW (
129 X_ACTION_ID in NUMBER,
130 X_OBJECT_VERSION_NUMBER in NUMBER,
131 X_ACTION_CODE in VARCHAR2,
132 X_EXECUTABLE_NAME in VARCHAR2,
133 X_SERVER_URL in VARCHAR2,
134 X_PROFILE_FOR_SERVER_URL in VARCHAR2,
135 X_DEPENDS_ON_APP in VARCHAR2,
136 X_APPLICATION_ID in NUMBER,
137 X_APPLICABLE_FOR in VARCHAR2,
138 X_TRACK_FLAG in VARCHAR2,
139 X_ENABLED_FLAG in VARCHAR2,
140 X_DYNAMIC_PARAMS_FLAG in VARCHAR2,
141 X_ADHOC_PARAMS_FLAG in VARCHAR2,
142 X_JAVA_CLASS_NAME in VARCHAR2,
143 X_NEW_JAVA_CLASS_NAME in VARCHAR2,
144 X_ACTION_CODE_MEANING in VARCHAR2,
145 X_DESCRIPTION in VARCHAR2
146 ) is
147 cursor c is select
148 OBJECT_VERSION_NUMBER
149 ,ACTION_CODE
150 ,EXECUTABLE_NAME
151 ,SERVER_URL
152 ,PROFILE_FOR_SERVER_URL
153 ,DEPENDS_ON_APP
154 ,APPLICATION_ID
155 ,APPLICABLE_FOR
156 ,TRACK_FLAG
157 ,ENABLED_FLAG
158 ,DYNAMIC_PARAMS_FLAG
159 ,ADHOC_PARAMS_FLAG
160 ,JAVA_CLASS_NAME
161 ,NEW_JAVA_CLASS_NAME
162 ,LAST_UPDATE_DATE
163 ,LAST_UPDATED_BY
164 ,CREATION_DATE
165 ,CREATED_BY
166 ,LAST_UPDATE_LOGIN
167 from AMS_CLIK_THRU_ACTIONS_B
168 where ACTION_ID = X_ACTION_ID
169 for update of ACTION_ID nowait;
170 recinfo c%rowtype;
171
172 /*
173 cursor ctl is select
174 ACTION_ID
175 ,ACTION_CODE_MEANING
176 ,DESCRIPTION
177 ,LANGUAGE
178 ,SOURCE_LANG
179 ,LAST_UPDATE_DATE
180 ,LAST_UPDATED_BY
181 ,CREATION_DATE
182 ,CREATED_BY
183 ,LAST_UPDATE_LOGIN
184 from AMS_CLIK_THRU_ACTIONS_TL
185 where ACTION_ID = X_ACTION_ID
186 for update of ACTION_ID nowait;
187 tlrecinfo ctl%rowtype;
188 */
189
190 CURSOR ctl IS SELECT
191 ACTION_CODE_MEANING,
192 DESCRIPTION,
193 DECODE(LANGUAGE, USERENV('lang'), 'y', 'n') baselang
194 FROM AMS_CLIK_THRU_ACTIONS_TL
195 WHERE ACTION_ID = X_ACTION_ID
196 AND USERENV('lang') IN (LANGUAGE, source_lang)
197 FOR UPDATE OF ACTION_ID NOWAIT;
198
199 begin
200 open c;
201 fetch c into recinfo;
202 if (c%notfound) then
203 close c;
204 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
205 app_exception.raise_exception;
206 end if;
207 close c;
208 if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
209 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
210 AND ((recinfo.ACTION_CODE = X_ACTION_CODE)
211 OR ((recinfo.ACTION_CODE is null) AND (X_ACTION_CODE is null)))
212 AND ((recinfo.EXECUTABLE_NAME = X_EXECUTABLE_NAME)
213 OR ((recinfo.EXECUTABLE_NAME is null) AND (X_EXECUTABLE_NAME is null)))
214 AND ((recinfo.SERVER_URL = X_SERVER_URL)
215 OR ((recinfo.SERVER_URL is null) AND (X_SERVER_URL is null)))
216 AND ((recinfo.PROFILE_FOR_SERVER_URL = X_PROFILE_FOR_SERVER_URL)
217 OR ((recinfo.PROFILE_FOR_SERVER_URL is null) AND (X_PROFILE_FOR_SERVER_URL is null)))
218 AND ((recinfo.DEPENDS_ON_APP = X_DEPENDS_ON_APP)
219 OR ((recinfo.DEPENDS_ON_APP is null) AND (X_DEPENDS_ON_APP is null)))
220 AND ((recinfo.APPLICATION_ID = X_APPLICATION_ID)
221 OR ((recinfo.APPLICATION_ID is null) AND (X_APPLICATION_ID is null)))
222 AND ((recinfo.APPLICABLE_FOR = X_APPLICABLE_FOR)
223 OR ((recinfo.APPLICABLE_FOR is null) AND (X_APPLICABLE_FOR is null)))
224 AND ((recinfo.TRACK_FLAG = X_TRACK_FLAG)
225 OR ((recinfo.TRACK_FLAG is null) AND (X_TRACK_FLAG is null)))
226 AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
227 OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
228 AND ((recinfo.DYNAMIC_PARAMS_FLAG = X_DYNAMIC_PARAMS_FLAG)
229 OR ((recinfo.DYNAMIC_PARAMS_FLAG is null) AND (X_DYNAMIC_PARAMS_FLAG is null)))
230 AND ((recinfo.ADHOC_PARAMS_FLAG = X_ADHOC_PARAMS_FLAG)
231 OR ((recinfo.ADHOC_PARAMS_FLAG is null) AND (X_ADHOC_PARAMS_FLAG is null)))
232 AND ((recinfo.JAVA_CLASS_NAME = X_JAVA_CLASS_NAME)
233 OR ((recinfo.JAVA_CLASS_NAME is null) AND (X_JAVA_CLASS_NAME is null)))
234 AND ((recinfo.NEW_JAVA_CLASS_NAME = X_NEW_JAVA_CLASS_NAME)
235 OR ((recinfo.NEW_JAVA_CLASS_NAME is null) AND (X_NEW_JAVA_CLASS_NAME is null)))
236 ) then
237 null;
238 else
239 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
240 app_exception.raise_exception;
241 end if;
242
243 FOR tlinfo IN ctl LOOP
244 IF (tlinfo.BASELANG = 'Y') THEN
245 IF ( (tlinfo.ACTION_CODE_MEANING = X_ACTION_CODE_MEANING)
246 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
247 OR ((tlinfo.DESCRIPTION IS NULL) AND (X_DESCRIPTION IS NULL)))
248 ) THEN
249 NULL;
250 ELSE
251 Fnd_Message.set_name('FND', 'FORM_RECORD_CHANGED');
252 App_Exception.raise_exception;
253 END IF;
254 END IF;
255 END LOOP;
256
257 return;
258 end LOCK_ROW;
259
260 procedure UPDATE_ROW (
261 X_ACTION_ID in NUMBER,
262 X_OBJECT_VERSION_NUMBER in NUMBER,
263 X_ACTION_CODE in VARCHAR2,
264 X_EXECUTABLE_NAME in VARCHAR2,
265 X_SERVER_URL in VARCHAR2,
266 X_PROFILE_FOR_SERVER_URL in VARCHAR2,
267 X_DEPENDS_ON_APP in VARCHAR2,
268 X_APPLICATION_ID in NUMBER,
269 X_APPLICABLE_FOR in VARCHAR2,
270 X_TRACK_FLAG in VARCHAR2,
271 X_ENABLED_FLAG in VARCHAR2,
272 X_DYNAMIC_PARAMS_FLAG in VARCHAR2,
273 X_ADHOC_PARAMS_FLAG in VARCHAR2,
274 X_JAVA_CLASS_NAME in VARCHAR2,
275 X_NEW_JAVA_CLASS_NAME in VARCHAR2,
276 X_ACTION_CODE_MEANING in VARCHAR2,
277 X_DESCRIPTION in VARCHAR2,
278 X_LAST_UPDATE_DATE in DATE,
279 X_LAST_UPDATED_BY in NUMBER,
280 X_LAST_UPDATE_LOGIN in NUMBER
281 ) is
282 begin
283 update AMS_CLIK_THRU_ACTIONS_B set
284 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
285 ,ACTION_CODE = X_ACTION_CODE
286 ,EXECUTABLE_NAME = X_EXECUTABLE_NAME
287 ,SERVER_URL = X_SERVER_URL
288 ,PROFILE_FOR_SERVER_URL = X_PROFILE_FOR_SERVER_URL
289 ,DEPENDS_ON_APP = X_DEPENDS_ON_APP
290 ,APPLICATION_ID = X_APPLICATION_ID
291 ,APPLICABLE_FOR = X_APPLICABLE_FOR
292 ,TRACK_FLAG = X_TRACK_FLAG
293 ,ENABLED_FLAG = X_ENABLED_FLAG
294 ,DYNAMIC_PARAMS_FLAG = X_DYNAMIC_PARAMS_FLAG
295 ,ADHOC_PARAMS_FLAG = X_ADHOC_PARAMS_FLAG
296 ,JAVA_CLASS_NAME = X_JAVA_CLASS_NAME
297 ,NEW_JAVA_CLASS_NAME = X_NEW_JAVA_CLASS_NAME
298 ,LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
299 ,LAST_UPDATED_BY = X_LAST_UPDATED_BY
300 ,LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
301 where ACTION_ID = X_ACTION_ID;
302
303 if (sql%notfound) then
304 raise no_data_found;
305 end if;
306
307 update AMS_CLIK_THRU_ACTIONS_TL set
308 ACTION_CODE_MEANING = X_ACTION_CODE_MEANING,
309 DESCRIPTION = X_DESCRIPTION,
310 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
311 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
312 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
313 SOURCE_LANG = USERENV('LANG')
314 where ACTION_ID = X_ACTION_ID
315 and USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
316
317 if (sql%notfound) then
318 raise no_data_found;
319 end if;
320
321 end UPDATE_ROW;
322
323 procedure DELETE_ROW (
324 X_ACTION_ID in NUMBER
325 ) is
326 begin
327 delete from AMS_CLIK_THRU_ACTIONS_TL
328 where ACTION_ID = X_ACTION_ID;
329
330 if (sql%notfound) then
331 raise no_data_found;
332 end if;
333
334 delete from AMS_CLIK_THRU_ACTIONS_B
335 where ACTION_ID = X_ACTION_ID;
336
337 if (sql%notfound) then
338 raise no_data_found;
339 end if;
340
341 end DELETE_ROW;
342
343 procedure LOAD_ROW(
344 X_ACTION_ID in NUMBER,
345 X_ACTION_CODE in VARCHAR2,
346 X_EXECUTABLE_NAME in VARCHAR2,
347 X_SERVER_URL in VARCHAR2,
348 X_PROFILE_FOR_SERVER_URL in VARCHAR2,
349 X_DEPENDS_ON_APP in VARCHAR2,
350 X_APPLICATION_ID in NUMBER,
351 X_APPLICABLE_FOR in VARCHAR2,
352 X_TRACK_FLAG in VARCHAR2,
353 X_ENABLED_FLAG in VARCHAR2,
354 X_DYNAMIC_PARAMS_FLAG in VARCHAR2,
355 X_ADHOC_PARAMS_FLAG in VARCHAR2,
356 X_JAVA_CLASS_NAME in VARCHAR2,
357 X_NEW_JAVA_CLASS_NAME in VARCHAR2,
358 X_ACTION_CODE_MEANING in VARCHAR2,
359 X_DESCRIPTION in VARCHAR2,
360 X_OWNER in VARCHAR2,
361 X_CUSTOM_MODE in VARCHAR2
362 ) is
363
364 l_user_id number := 0;
365 l_last_updated_by number;
366 l_obj_verno number;
367 l_dummy_char varchar2(1);
368 l_row_id varchar2(100);
369
370 cursor c_obj_verno is
371 select OBJECT_VERSION_NUMBER,
372 last_updated_by
373 from AMS_CLIK_THRU_ACTIONS_B
374 where ACTION_ID = X_ACTION_ID;
375
376 cursor c_chk_cta_exists is
377 select 'x'
378 from AMS_CLIK_THRU_ACTIONS_B
379 where ACTION_ID = X_ACTION_ID;
380
381 cursor ctl_chk_cta_exists is
382 select 'x'
383 from AMS_CLIK_THRU_ACTIONS_TL
384 where ACTION_ID = X_ACTION_ID;
385
386 BEGIN
387
388 if X_OWNER = 'SEED' then
389 l_user_id := 1;
390 elsif X_OWNER = 'ORACLE' then
391 l_user_id := 2;
392 elsif X_OWNER = 'SYSADMIN' THEN
393 l_user_id := 0;
394 end if;
395
396 open c_chk_cta_exists;
397 fetch c_chk_cta_exists into l_dummy_char;
398 if c_chk_cta_exists%notfound
399 then
400 close c_chk_cta_exists;
401
402 l_obj_verno := 1;
403
404 AMS_CLIK_THRU_ACT_PKG.INSERT_ROW(
405 X_ROWID => l_row_id
406 ,X_ACTION_ID => X_ACTION_ID
407 ,X_ACTION_CODE => X_ACTION_CODE
408 ,X_EXECUTABLE_NAME => X_EXECUTABLE_NAME
409 ,X_SERVER_URL => X_SERVER_URL
410 ,X_PROFILE_FOR_SERVER_URL => X_PROFILE_FOR_SERVER_URL
411 ,X_DEPENDS_ON_APP => X_DEPENDS_ON_APP
412 ,X_APPLICATION_ID => X_APPLICATION_ID
413 ,X_APPLICABLE_FOR => X_APPLICABLE_FOR
414 ,X_TRACK_FLAG => X_TRACK_FLAG
415 ,X_ENABLED_FLAG => X_ENABLED_FLAG
419 ,X_NEW_JAVA_CLASS_NAME => X_NEW_JAVA_CLASS_NAME
416 ,X_DYNAMIC_PARAMS_FLAG => X_DYNAMIC_PARAMS_FLAG
417 ,X_ADHOC_PARAMS_FLAG => X_ADHOC_PARAMS_FLAG
418 ,X_JAVA_CLASS_NAME => X_JAVA_CLASS_NAME
420 ,X_ACTION_CODE_MEANING => X_ACTION_CODE_MEANING
421 ,X_DESCRIPTION => X_DESCRIPTION
422 ,X_LAST_UPDATE_DATE => SYSDATE
423 ,X_LAST_UPDATED_BY => l_user_id
424 ,X_CREATION_DATE => SYSDATE
425 ,X_CREATED_BY => l_user_id
426 ,X_LAST_UPDATE_LOGIN => 0
427 ,X_OBJECT_VERSION_NUMBER => l_obj_verno
428 );
429 else
430 close c_chk_cta_exists;
431 open c_obj_verno;
432 fetch c_obj_verno into l_obj_verno,l_last_updated_by;
433 close c_obj_verno;
434
435 if (l_last_updated_by in (1,2,0) OR
436 NVL(x_custom_mode,'PRESERVE')='FORCE') THEN
437
438 AMS_CLIK_THRU_ACT_PKG.UPDATE_ROW(
439 X_ACTION_ID => X_ACTION_ID,
440 X_ACTION_CODE => X_ACTION_CODE,
441 X_EXECUTABLE_NAME => X_EXECUTABLE_NAME,
442 X_SERVER_URL => X_SERVER_URL,
443 X_PROFILE_FOR_SERVER_URL => X_PROFILE_FOR_SERVER_URL,
444 X_DEPENDS_ON_APP => X_DEPENDS_ON_APP,
445 X_APPLICATION_ID => X_APPLICATION_ID,
446 X_APPLICABLE_FOR => X_APPLICABLE_FOR,
447 X_TRACK_FLAG => X_TRACK_FLAG,
448 X_ENABLED_FLAG => X_ENABLED_FLAG,
449 X_DYNAMIC_PARAMS_FLAG => X_DYNAMIC_PARAMS_FLAG,
450 X_ADHOC_PARAMS_FLAG => X_ADHOC_PARAMS_FLAG,
451 X_JAVA_CLASS_NAME => X_JAVA_CLASS_NAME,
452 X_NEW_JAVA_CLASS_NAME => X_NEW_JAVA_CLASS_NAME,
453 X_ACTION_CODE_MEANING => X_ACTION_CODE_MEANING,
454 X_DESCRIPTION => X_DESCRIPTION,
455 X_LAST_UPDATE_DATE => SYSDATE,
456 X_LAST_UPDATED_BY => l_user_id,
457 X_LAST_UPDATE_LOGIN => 0,
458 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1
459 );
460 end if;
461 end if;
462
463 END LOAD_ROW;
464
465 PROCEDURE TRANSLATE_ROW (
466 X_ACTION_ID IN NUMBER
467 , X_ACTION_CODE_MEANING IN VARCHAR2
468 , X_DESCRIPTION IN VARCHAR2
469 , X_OWNER IN VARCHAR2
470 , X_CUSTOM_MODE IN VARCHAR2
471 ) IS
472 cursor c_last_updated_by is
473 select last_updated_by
474 from AMS_CLIK_THRU_ACTIONS_TL
475 where ACTION_ID = X_ACTION_ID
476 and USERENV('LANG') = LANGUAGE;
477
478 l_last_updated_by number;
479
480 BEGIN
481
482 open c_last_updated_by;
483 fetch c_last_updated_by into l_last_updated_by;
484 close c_last_updated_by;
485
486
487 if (l_last_updated_by in (1,2,0) OR
488 NVL(x_custom_mode,'PRESERVE')='FORCE') THEN
489 -- Only update rows which have not been altered by user
490
491 UPDATE AMS_CLIK_THRU_ACTIONS_TL
492 SET description = X_DESCRIPTION,
493 action_code_meaning = X_ACTION_CODE_MEANING,
494 source_lang = USERENV('LANG'),
495 last_update_date = SYSDATE,
496 last_updated_by = DECODE(X_OWNER, 'SEED', 1,
497 'ORACLE',2,
498 'SYSADMIN',0 , -1),
499 last_update_login = 0
500 WHERE ACTION_ID = X_ACTION_ID
501 AND USERENV('LANG') IN (LANGUAGE, source_lang);
502
503 end if;
504
505 END TRANSLATE_ROW;
506
507 PROCEDURE add_language
508 IS
509 BEGIN
510 DELETE FROM AMS_CLIK_THRU_ACTIONS_TL T
511 WHERE NOT EXISTS
512 (SELECT NULL
513 FROM AMS_CLIK_THRU_ACTIONS_B B
514 WHERE B.ACTION_ID = T.ACTION_ID
515 );
516
517 UPDATE AMS_CLIK_THRU_ACTIONS_TL T SET (
518 ACTION_CODE_MEANING,
519 DESCRIPTION
520 ) =
521 (SELECT
522 T1.ACTION_CODE_MEANING,
523 T1.DESCRIPTION
524 FROM AMS_CLIK_THRU_ACTIONS_TL T1
525 WHERE T1.ACTION_ID = T.ACTION_ID
526 AND T1.LANGUAGE = T.SOURCE_LANG)
527 WHERE (
528 T.ACTION_ID,
529 T.LANGUAGE
530 ) IN
531 (SELECT
532 subt.ACTION_ID,
533 subt.LANGUAGE
534 FROM AMS_CLIK_THRU_ACTIONS_TL subb, AMS_CLIK_THRU_ACTIONS_TL subt
535 WHERE subb.ACTION_ID = subt.ACTION_ID
536 AND subb.LANGUAGE = subt.SOURCE_LANG
537 AND (subb.ACTION_CODE_MEANING <> subt.ACTION_CODE_MEANING
538 OR subb.DESCRIPTION <> subt.DESCRIPTION
539 OR (subb.DESCRIPTION IS NULL AND subt.DESCRIPTION IS NOT NULL)
540 OR (subb.DESCRIPTION IS NOT NULL AND subt.DESCRIPTION IS NULL)
541 ));
542
543 INSERT INTO AMS_CLIK_THRU_ACTIONS_TL (
544 action_id,
545 action_code_meaning,
546 description,
547 created_by,
548 creation_date,
549 last_updated_by,
550 last_update_date,
551 last_update_login,
552 LANGUAGE,
553 source_lang
554 ) SELECT /*+ ordered */
555 b.action_id,
556 b.action_code_meaning,
557 b.description,
558 b.created_by,
559 b.creation_date,
560 b.last_updated_by,
561 b.last_update_date,
562 b.last_update_login,
563 l.language_code,
564 b.source_lang
565 FROM ams_clik_thru_actions_tl b, fnd_languages l
566 WHERE l.installed_flag IN ('I', 'B')
567 AND b.LANGUAGE = USERENV('lang')
568 AND NOT EXISTS
569 (SELECT NULL
570 FROM ams_clik_thru_actions_tl T
571 WHERE T.action_id = b.action_id
572 AND T.LANGUAGE = l.language_code);
573 END add_language;
574
575
576 end AMS_CLIK_THRU_ACT_PKG;