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