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