1 PACKAGE BODY AMW_ASSESSMENTS_PKG as
2 /* $Header: amwvassb.pls 120.0 2005/05/31 18:39:29 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_ASSESSMENT_ID in NUMBER,
6 X_ATTRIBUTE15 in VARCHAR2,
7 X_ATTRIBUTE8 in VARCHAR2,
8 X_ATTRIBUTE9 in VARCHAR2,
9 X_ATTRIBUTE10 in VARCHAR2,
10 X_ATTRIBUTE11 in VARCHAR2,
11 X_ATTRIBUTE12 in VARCHAR2,
12 X_ATTRIBUTE13 in VARCHAR2,
13 X_ATTRIBUTE14 in VARCHAR2,
14 X_SECURITY_GROUP_ID in NUMBER,
15 X_OBJECT_VERSION_NUMBER in NUMBER,
16 X_EFFECTIVE_COMPLETION_DATE in DATE,
17 X_ATTRIBUTE7 in VARCHAR2,
18 X_ATTRIBUTE3 in VARCHAR2,
19 X_ATTRIBUTE4 in VARCHAR2,
20 X_ATTRIBUTE5 in VARCHAR2,
21 X_ATTRIBUTE6 in VARCHAR2,
22 X_ASSESSMENT_TYPE_CODE in VARCHAR2,
23 X_ASSESSMENT_OWNER_ID in NUMBER,
24 X_IES_SURVEY_ID in NUMBER,
25 X_IES_CYCLE_ID in NUMBER,
26 X_IES_DEPLOYMENT_ID in NUMBER,
27 X_ASSESSMENT_STATUS_CODE in VARCHAR2,
28 X_ATTRIBUTE_CATEGORY in VARCHAR2,
29 X_ATTRIBUTE1 in VARCHAR2,
30 X_ATTRIBUTE2 in VARCHAR2,
31 X_NAME 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_START_DATE in DATE,
39 X_PERIOD_NAME in VARCHAR2
40 ) is
41 cursor C is select ROWID from AMW_ASSESSMENTS_B
42 where ASSESSMENT_ID = X_ASSESSMENT_ID;
43
44 l_start_date DATE;
45 begin
46 -- 12.16.2003 tsho: update start_date if status_code becomes 'P' (In Progress)
47 if (X_START_DATE is null AND X_ASSESSMENT_STATUS_CODE='P') then
48 l_start_date := SYSDATE;
49 else
50 l_start_date := X_START_DATE;
51 end if;
52
53 insert into AMW_ASSESSMENTS_B (
54 ATTRIBUTE15,
55 ATTRIBUTE8,
56 ATTRIBUTE9,
57 ATTRIBUTE10,
58 ATTRIBUTE11,
59 ATTRIBUTE12,
60 ATTRIBUTE13,
61 ATTRIBUTE14,
62 SECURITY_GROUP_ID,
63 OBJECT_VERSION_NUMBER,
64 EFFECTIVE_COMPLETION_DATE,
65 ATTRIBUTE7,
66 ATTRIBUTE3,
67 ATTRIBUTE4,
68 ATTRIBUTE5,
69 ATTRIBUTE6,
70 ASSESSMENT_ID,
71 ASSESSMENT_TYPE_CODE,
72 ASSESSMENT_OWNER_ID,
73 IES_SURVEY_ID,
74 IES_CYCLE_ID,
75 IES_DEPLOYMENT_ID,
76 ASSESSMENT_STATUS_CODE,
77 ATTRIBUTE_CATEGORY,
78 ATTRIBUTE1,
79 ATTRIBUTE2,
80 CREATION_DATE,
81 CREATED_BY,
82 LAST_UPDATE_DATE,
83 LAST_UPDATED_BY,
84 LAST_UPDATE_LOGIN,
85 START_DATE,
86 PERIOD_NAME
87 ) values (
88 X_ATTRIBUTE15,
89 X_ATTRIBUTE8,
90 X_ATTRIBUTE9,
91 X_ATTRIBUTE10,
92 X_ATTRIBUTE11,
93 X_ATTRIBUTE12,
94 X_ATTRIBUTE13,
95 X_ATTRIBUTE14,
96 X_SECURITY_GROUP_ID,
97 X_OBJECT_VERSION_NUMBER,
98 X_EFFECTIVE_COMPLETION_DATE,
99 X_ATTRIBUTE7,
100 X_ATTRIBUTE3,
101 X_ATTRIBUTE4,
102 X_ATTRIBUTE5,
103 X_ATTRIBUTE6,
104 X_ASSESSMENT_ID,
105 X_ASSESSMENT_TYPE_CODE,
106 X_ASSESSMENT_OWNER_ID,
107 X_IES_SURVEY_ID,
108 X_IES_CYCLE_ID,
109 X_IES_DEPLOYMENT_ID,
110 X_ASSESSMENT_STATUS_CODE,
111 X_ATTRIBUTE_CATEGORY,
112 X_ATTRIBUTE1,
113 X_ATTRIBUTE2,
114 X_CREATION_DATE,
115 X_CREATED_BY,
116 X_LAST_UPDATE_DATE,
117 X_LAST_UPDATED_BY,
118 X_LAST_UPDATE_LOGIN,
119 l_start_date, -- 12.16.2003 tsho: use l_start_date instead of X_START_DATE
120 X_PERIOD_NAME
121 );
122
123 insert into AMW_ASSESSMENTS_TL (
124 LAST_UPDATE_LOGIN,
125 ASSESSMENT_ID,
126 NAME,
127 DESCRIPTION,
128 LAST_UPDATE_DATE,
129 LAST_UPDATED_BY,
130 CREATION_DATE,
131 CREATED_BY,
132 SECURITY_GROUP_ID,
133 LANGUAGE,
134 SOURCE_LANG
135 ) select
136 X_LAST_UPDATE_LOGIN,
137 X_ASSESSMENT_ID,
138 X_NAME,
139 X_DESCRIPTION,
140 X_LAST_UPDATE_DATE,
141 X_LAST_UPDATED_BY,
142 X_CREATION_DATE,
143 X_CREATED_BY,
144 X_SECURITY_GROUP_ID,
145 L.LANGUAGE_CODE,
146 userenv('LANG')
147 from FND_LANGUAGES L
148 where L.INSTALLED_FLAG in ('I', 'B')
149 and not exists
150 (select NULL
151 from AMW_ASSESSMENTS_TL T
152 where T.ASSESSMENT_ID = X_ASSESSMENT_ID
153 and T.LANGUAGE = L.LANGUAGE_CODE);
154
155 open c;
156 fetch c into X_ROWID;
157 if (c%notfound) then
158 close c;
159 raise no_data_found;
160 end if;
161 close c;
162
163 end INSERT_ROW;
164
165 procedure LOCK_ROW (
166 X_ASSESSMENT_ID in NUMBER,
167 X_ATTRIBUTE15 in VARCHAR2,
168 X_ATTRIBUTE8 in VARCHAR2,
169 X_ATTRIBUTE9 in VARCHAR2,
170 X_ATTRIBUTE10 in VARCHAR2,
171 X_ATTRIBUTE11 in VARCHAR2,
172 X_ATTRIBUTE12 in VARCHAR2,
173 X_ATTRIBUTE13 in VARCHAR2,
174 X_ATTRIBUTE14 in VARCHAR2,
175 X_SECURITY_GROUP_ID in NUMBER,
176 X_OBJECT_VERSION_NUMBER in NUMBER,
177 X_EFFECTIVE_COMPLETION_DATE in DATE,
178 X_ATTRIBUTE7 in VARCHAR2,
179 X_ATTRIBUTE3 in VARCHAR2,
180 X_ATTRIBUTE4 in VARCHAR2,
181 X_ATTRIBUTE5 in VARCHAR2,
182 X_ATTRIBUTE6 in VARCHAR2,
183 X_ASSESSMENT_TYPE_CODE in VARCHAR2,
184 X_ASSESSMENT_OWNER_ID in NUMBER,
185 X_IES_SURVEY_ID in NUMBER,
186 X_IES_CYCLE_ID in NUMBER,
187 X_IES_DEPLOYMENT_ID in NUMBER,
188 X_ASSESSMENT_STATUS_CODE in VARCHAR2,
189 X_ATTRIBUTE_CATEGORY in VARCHAR2,
190 X_ATTRIBUTE1 in VARCHAR2,
191 X_ATTRIBUTE2 in VARCHAR2,
192 X_NAME in VARCHAR2,
193 X_DESCRIPTION in VARCHAR2,
194 X_START_DATE in DATE,
195 X_PERIOD_NAME in VARCHAR2
196 ) is
197 cursor c is select
198 ATTRIBUTE15,
199 ATTRIBUTE8,
200 ATTRIBUTE9,
201 ATTRIBUTE10,
202 ATTRIBUTE11,
203 ATTRIBUTE12,
204 ATTRIBUTE13,
205 ATTRIBUTE14,
206 SECURITY_GROUP_ID,
207 OBJECT_VERSION_NUMBER,
208 EFFECTIVE_COMPLETION_DATE,
209 ATTRIBUTE7,
210 ATTRIBUTE3,
211 ATTRIBUTE4,
212 ATTRIBUTE5,
213 ATTRIBUTE6,
214 ASSESSMENT_TYPE_CODE,
215 ASSESSMENT_OWNER_ID,
216 IES_SURVEY_ID,
217 IES_CYCLE_ID,
218 IES_DEPLOYMENT_ID,
219 ASSESSMENT_STATUS_CODE,
220 ATTRIBUTE_CATEGORY,
221 ATTRIBUTE1,
222 ATTRIBUTE2,
223 START_DATE,
224 PERIOD_NAME
225 from AMW_ASSESSMENTS_B
226 where ASSESSMENT_ID = X_ASSESSMENT_ID
227 for update of ASSESSMENT_ID nowait;
228 recinfo c%rowtype;
229
230 cursor c1 is select
231 NAME,
232 DESCRIPTION,
233 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
234 from AMW_ASSESSMENTS_TL
235 where ASSESSMENT_ID = X_ASSESSMENT_ID
236 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
237 for update of ASSESSMENT_ID nowait;
238 begin
239 open c;
240 fetch c into recinfo;
241 if (c%notfound) then
242 close c;
243 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
244 app_exception.raise_exception;
245 end if;
246 close c;
247 if ( ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
248 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
249 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
250 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
251 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
252 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
253 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
254 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
255 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
256 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
257 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
258 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
259 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
260 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
261 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
262 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
263 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
264 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
265 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
266 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
267 AND ((recinfo.EFFECTIVE_COMPLETION_DATE = X_EFFECTIVE_COMPLETION_DATE)
268 OR ((recinfo.EFFECTIVE_COMPLETION_DATE is null) AND (X_EFFECTIVE_COMPLETION_DATE is null)))
269 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
270 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
271 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
272 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
273 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
274 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
275 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
276 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
277 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
278 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
279 AND (recinfo.ASSESSMENT_TYPE_CODE = X_ASSESSMENT_TYPE_CODE)
280 AND ((recinfo.ASSESSMENT_OWNER_ID = X_ASSESSMENT_OWNER_ID)
281 OR ((recinfo.ASSESSMENT_OWNER_ID is null) AND (X_ASSESSMENT_OWNER_ID is null)))
282 AND ((recinfo.IES_SURVEY_ID = X_IES_SURVEY_ID)
283 OR ((recinfo.IES_SURVEY_ID is null) AND (X_IES_SURVEY_ID is null)))
284 AND ((recinfo.IES_CYCLE_ID = X_IES_CYCLE_ID)
285 OR ((recinfo.IES_CYCLE_ID is null) AND (X_IES_CYCLE_ID is null)))
286 AND ((recinfo.IES_DEPLOYMENT_ID = X_IES_DEPLOYMENT_ID)
287 OR ((recinfo.IES_DEPLOYMENT_ID is null) AND (X_IES_DEPLOYMENT_ID is null)))
288 AND (recinfo.ASSESSMENT_STATUS_CODE = X_ASSESSMENT_STATUS_CODE)
289 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
290 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
291 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
292 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
293 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
294 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
295 AND ((recinfo.PERIOD_NAME = X_PERIOD_NAME)
296 OR ((recinfo.PERIOD_NAME is null) AND (X_PERIOD_NAME is null)))
297 ) then
298 null;
299 else
300 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
301 app_exception.raise_exception;
302 end if;
303
304 for tlinfo in c1 loop
305 if (tlinfo.BASELANG = 'Y') then
306 if ( (tlinfo.NAME = X_NAME)
307 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
308 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
309 ) then
310 null;
311 else
312 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
313 app_exception.raise_exception;
314 end if;
315 end if;
316 end loop;
317 return;
318 end LOCK_ROW;
319
320 procedure UPDATE_ROW (
321 X_ASSESSMENT_ID in NUMBER,
322 X_ATTRIBUTE15 in VARCHAR2,
323 X_ATTRIBUTE8 in VARCHAR2,
324 X_ATTRIBUTE9 in VARCHAR2,
325 X_ATTRIBUTE10 in VARCHAR2,
326 X_ATTRIBUTE11 in VARCHAR2,
327 X_ATTRIBUTE12 in VARCHAR2,
328 X_ATTRIBUTE13 in VARCHAR2,
329 X_ATTRIBUTE14 in VARCHAR2,
330 X_SECURITY_GROUP_ID in NUMBER,
331 X_OBJECT_VERSION_NUMBER in NUMBER,
332 X_EFFECTIVE_COMPLETION_DATE in DATE,
333 X_ATTRIBUTE7 in VARCHAR2,
334 X_ATTRIBUTE3 in VARCHAR2,
335 X_ATTRIBUTE4 in VARCHAR2,
336 X_ATTRIBUTE5 in VARCHAR2,
337 X_ATTRIBUTE6 in VARCHAR2,
338 X_ASSESSMENT_TYPE_CODE in VARCHAR2,
339 X_ASSESSMENT_OWNER_ID in NUMBER,
340 X_IES_SURVEY_ID in NUMBER,
341 X_IES_CYCLE_ID in NUMBER,
342 X_IES_DEPLOYMENT_ID in NUMBER,
343 X_ASSESSMENT_STATUS_CODE in VARCHAR2,
344 X_ATTRIBUTE_CATEGORY in VARCHAR2,
345 X_ATTRIBUTE1 in VARCHAR2,
346 X_ATTRIBUTE2 in VARCHAR2,
347 X_NAME in VARCHAR2,
348 X_DESCRIPTION in VARCHAR2,
349 X_LAST_UPDATE_DATE in DATE,
350 X_LAST_UPDATED_BY in NUMBER,
351 X_LAST_UPDATE_LOGIN in NUMBER,
352 X_START_DATE in DATE,
353 X_PERIOD_NAME in VARCHAR2
354 ) is
355 l_start_date DATE;
356
357 begin
358 -- 12.16.2003 tsho: update start_date if status_code becomes 'P' (In Progress)
359 if (X_START_DATE is null AND X_ASSESSMENT_STATUS_CODE='P') then
360 l_start_date := SYSDATE;
361 else
362 l_start_date := X_START_DATE;
363 end if;
364
365 update AMW_ASSESSMENTS_B set
366 ATTRIBUTE15 = X_ATTRIBUTE15,
367 ATTRIBUTE8 = X_ATTRIBUTE8,
368 ATTRIBUTE9 = X_ATTRIBUTE9,
369 ATTRIBUTE10 = X_ATTRIBUTE10,
370 ATTRIBUTE11 = X_ATTRIBUTE11,
371 ATTRIBUTE12 = X_ATTRIBUTE12,
372 ATTRIBUTE13 = X_ATTRIBUTE13,
373 ATTRIBUTE14 = X_ATTRIBUTE14,
374 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
375 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
376 EFFECTIVE_COMPLETION_DATE = X_EFFECTIVE_COMPLETION_DATE,
377 ATTRIBUTE7 = X_ATTRIBUTE7,
378 ATTRIBUTE3 = X_ATTRIBUTE3,
379 ATTRIBUTE4 = X_ATTRIBUTE4,
380 ATTRIBUTE5 = X_ATTRIBUTE5,
381 ATTRIBUTE6 = X_ATTRIBUTE6,
382 ASSESSMENT_TYPE_CODE = X_ASSESSMENT_TYPE_CODE,
383 ASSESSMENT_OWNER_ID = X_ASSESSMENT_OWNER_ID,
384 IES_SURVEY_ID = X_IES_SURVEY_ID,
385 IES_CYCLE_ID = X_IES_CYCLE_ID,
386 IES_DEPLOYMENT_ID = X_IES_DEPLOYMENT_ID,
387 ASSESSMENT_STATUS_CODE = X_ASSESSMENT_STATUS_CODE,
388 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
389 ATTRIBUTE1 = X_ATTRIBUTE1,
390 ATTRIBUTE2 = X_ATTRIBUTE2,
391 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
392 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
396 where ASSESSMENT_ID = X_ASSESSMENT_ID;
393 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
394 START_DATE = l_start_date, -- 12.16.2003 tsho: use l_start_date instead of X_START_DATE
395 PERIOD_NAME = X_PERIOD_NAME
397
398 if (sql%notfound) then
399 raise no_data_found;
400 end if;
401
402 update AMW_ASSESSMENTS_TL set
403 NAME = X_NAME,
404 DESCRIPTION = X_DESCRIPTION,
405 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
406 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
407 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
408 SOURCE_LANG = userenv('LANG')
409 where ASSESSMENT_ID = X_ASSESSMENT_ID
410 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
411
412 if (sql%notfound) then
413 raise no_data_found;
414 end if;
415 end UPDATE_ROW;
416
417 procedure DELETE_ROW (
418 X_ASSESSMENT_ID in NUMBER
419 ) is
420 begin
421 delete from AMW_ASSESSMENTS_TL
422 where ASSESSMENT_ID = X_ASSESSMENT_ID;
423
424 if (sql%notfound) then
425 raise no_data_found;
426 end if;
427
428 delete from AMW_ASSESSMENTS_B
429 where ASSESSMENT_ID = X_ASSESSMENT_ID;
430
431 if (sql%notfound) then
432 raise no_data_found;
433 end if;
434 end DELETE_ROW;
435
436 procedure ADD_LANGUAGE
437 is
438 begin
439 delete from AMW_ASSESSMENTS_TL T
440 where not exists
441 (select NULL
442 from AMW_ASSESSMENTS_B B
443 where B.ASSESSMENT_ID = T.ASSESSMENT_ID
444 );
445
446 update AMW_ASSESSMENTS_TL T set (
447 NAME,
448 DESCRIPTION
449 ) = (select
450 B.NAME,
451 B.DESCRIPTION
452 from AMW_ASSESSMENTS_TL B
453 where B.ASSESSMENT_ID = T.ASSESSMENT_ID
454 and B.LANGUAGE = T.SOURCE_LANG)
455 where (
456 T.ASSESSMENT_ID,
457 T.LANGUAGE
458 ) in (select
459 SUBT.ASSESSMENT_ID,
460 SUBT.LANGUAGE
461 from AMW_ASSESSMENTS_TL SUBB, AMW_ASSESSMENTS_TL SUBT
462 where SUBB.ASSESSMENT_ID = SUBT.ASSESSMENT_ID
463 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
464 and (SUBB.NAME <> SUBT.NAME
465 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
466 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
467 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
468 ));
469
470 insert into AMW_ASSESSMENTS_TL (
471 LAST_UPDATE_LOGIN,
472 ASSESSMENT_ID,
473 NAME,
474 DESCRIPTION,
475 LAST_UPDATE_DATE,
476 LAST_UPDATED_BY,
477 CREATION_DATE,
478 CREATED_BY,
479 SECURITY_GROUP_ID,
480 LANGUAGE,
481 SOURCE_LANG
482 ) select
483 B.LAST_UPDATE_LOGIN,
484 B.ASSESSMENT_ID,
485 B.NAME,
486 B.DESCRIPTION,
487 B.LAST_UPDATE_DATE,
488 B.LAST_UPDATED_BY,
489 B.CREATION_DATE,
490 B.CREATED_BY,
491 B.SECURITY_GROUP_ID,
492 L.LANGUAGE_CODE,
493 B.SOURCE_LANG
494 from AMW_ASSESSMENTS_TL B, FND_LANGUAGES L
495 where L.INSTALLED_FLAG in ('I', 'B')
496 and B.LANGUAGE = userenv('LANG')
497 and not exists
498 (select NULL
499 from AMW_ASSESSMENTS_TL T
500 where T.ASSESSMENT_ID = B.ASSESSMENT_ID
501 and T.LANGUAGE = L.LANGUAGE_CODE);
502 end ADD_LANGUAGE;
503
504 end AMW_ASSESSMENTS_PKG;
505
[Home] [Help]