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