1 package body AK_FOREIGN_KEYS_PKG as
2 /* $Header: AKDOBFKB.pls 120.3 2006/01/25 15:57:40 tshort ship $ */
3 --*****************************************************************************
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_FOREIGN_KEY_NAME in VARCHAR2,
7 X_DATABASE_OBJECT_NAME in VARCHAR2,
8 X_UNIQUE_KEY_NAME in VARCHAR2,
9 X_APPLICATION_ID in NUMBER,
10 X_FROM_TO_NAME in VARCHAR2,
11 X_FROM_TO_DESCRIPTION in VARCHAR2,
12 X_TO_FROM_NAME in VARCHAR2,
13 X_TO_FROM_DESCRIPTION in VARCHAR2,
14 X_CREATION_DATE in DATE,
15 X_CREATED_BY in NUMBER,
16 X_LAST_UPDATE_DATE in DATE,
17 X_LAST_UPDATED_BY in NUMBER,
18 X_LAST_UPDATE_LOGIN in NUMBER,
19 X_ATTRIBUTE_CATEGORY in VARCHAR2,
20 X_ATTRIBUTE1 in VARCHAR2,
21 X_ATTRIBUTE2 in VARCHAR2,
22 X_ATTRIBUTE3 in VARCHAR2,
23 X_ATTRIBUTE4 in VARCHAR2,
24 X_ATTRIBUTE5 in VARCHAR2,
25 X_ATTRIBUTE6 in VARCHAR2,
26 X_ATTRIBUTE7 in VARCHAR2,
27 X_ATTRIBUTE8 in VARCHAR2,
28 X_ATTRIBUTE9 in VARCHAR2,
29 X_ATTRIBUTE10 in VARCHAR2,
30 X_ATTRIBUTE11 in VARCHAR2,
31 X_ATTRIBUTE12 in VARCHAR2,
32 X_ATTRIBUTE13 in VARCHAR2,
33 X_ATTRIBUTE14 in VARCHAR2,
34 X_ATTRIBUTE15 in VARCHAR2
35 ) is
36 cursor C is select ROWID from AK_FOREIGN_KEYS
37 where FOREIGN_KEY_NAME = X_FOREIGN_KEY_NAME;
38 begin
39 insert into AK_FOREIGN_KEYS (
40 FOREIGN_KEY_NAME,
41 DATABASE_OBJECT_NAME,
42 UNIQUE_KEY_NAME,
43 APPLICATION_ID,
44 CREATION_DATE,
45 CREATED_BY,
46 LAST_UPDATE_DATE,
47 LAST_UPDATED_BY,
48 LAST_UPDATE_LOGIN,
49 ATTRIBUTE_CATEGORY,
50 ATTRIBUTE1,
51 ATTRIBUTE2,
52 ATTRIBUTE3,
53 ATTRIBUTE4,
54 ATTRIBUTE5,
55 ATTRIBUTE6,
56 ATTRIBUTE7,
57 ATTRIBUTE8,
58 ATTRIBUTE9,
59 ATTRIBUTE10,
60 ATTRIBUTE11,
61 ATTRIBUTE12,
62 ATTRIBUTE13,
63 ATTRIBUTE14,
64 ATTRIBUTE15
65 ) values (
66 X_FOREIGN_KEY_NAME,
67 X_DATABASE_OBJECT_NAME,
68 X_UNIQUE_KEY_NAME,
69 X_APPLICATION_ID,
70 X_CREATION_DATE,
71 X_CREATED_BY,
72 X_LAST_UPDATE_DATE,
73 X_LAST_UPDATED_BY,
74 X_LAST_UPDATE_LOGIN,
75 X_ATTRIBUTE_CATEGORY,
76 X_ATTRIBUTE1,
77 X_ATTRIBUTE2,
78 X_ATTRIBUTE3,
79 X_ATTRIBUTE4,
80 X_ATTRIBUTE5,
81 X_ATTRIBUTE6,
82 X_ATTRIBUTE7,
83 X_ATTRIBUTE8,
84 X_ATTRIBUTE9,
85 X_ATTRIBUTE10,
86 X_ATTRIBUTE11,
87 X_ATTRIBUTE12,
88 X_ATTRIBUTE13,
89 X_ATTRIBUTE14,
90 X_ATTRIBUTE15
91 );
92
93 open c;
94 fetch c into X_ROWID;
95 if (c%notfound) then
96 close c;
97 raise no_data_found;
98 end if;
99 close c;
100
101 insert into AK_FOREIGN_KEYS_TL (
102 FOREIGN_KEY_NAME,
103 LANGUAGE,
104 FROM_TO_NAME,
105 FROM_TO_DESCRIPTION,
106 TO_FROM_NAME,
107 TO_FROM_DESCRIPTION,
108 SOURCE_LANG,
109 CREATED_BY,
110 CREATION_DATE,
111 LAST_UPDATED_BY,
112 LAST_UPDATE_DATE,
113 LAST_UPDATE_LOGIN
114 ) select
115 X_FOREIGN_KEY_NAME,
116 L.LANGUAGE_CODE,
117 X_FROM_TO_NAME,
118 X_FROM_TO_DESCRIPTION,
119 X_TO_FROM_NAME,
120 X_TO_FROM_DESCRIPTION,
121 userenv('LANG'),
122 X_CREATED_BY,
123 X_CREATION_DATE,
124 X_LAST_UPDATED_BY,
125 X_LAST_UPDATE_DATE,
126 X_LAST_UPDATE_LOGIN
127 from FND_LANGUAGES L
128 where L.INSTALLED_FLAG in ('I', 'B')
129 and not exists
130 (select NULL
131 from AK_FOREIGN_KEYS_TL T
132 where T.FOREIGN_KEY_NAME = X_FOREIGN_KEY_NAME
133 and T.LANGUAGE = L.LANGUAGE_CODE);
134 end INSERT_ROW;
135 --*****************************************************************************
136 procedure INSERT_AFKC_ROW (
137 X_ROWID in out NOCOPY VARCHAR2,
138 X_FOREIGN_KEY_NAME in VARCHAR2,
139 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
140 X_ATTRIBUTE_CODE in VARCHAR2,
141 X_FOREIGN_KEY_SEQUENCE in NUMBER,
142 X_CREATION_DATE in DATE,
143 X_CREATED_BY in NUMBER,
144 X_LAST_UPDATE_DATE in DATE,
145 X_LAST_UPDATED_BY in NUMBER,
146 X_LAST_UPDATE_LOGIN in NUMBER,
147 X_ATTRIBUTE_CATEGORY in VARCHAR2,
148 X_ATTRIBUTE1 in VARCHAR2,
149 X_ATTRIBUTE2 in VARCHAR2,
150 X_ATTRIBUTE3 in VARCHAR2,
151 X_ATTRIBUTE4 in VARCHAR2,
152 X_ATTRIBUTE5 in VARCHAR2,
153 X_ATTRIBUTE6 in VARCHAR2,
154 X_ATTRIBUTE7 in VARCHAR2,
155 X_ATTRIBUTE8 in VARCHAR2,
156 X_ATTRIBUTE9 in VARCHAR2,
157 X_ATTRIBUTE10 in VARCHAR2,
158 X_ATTRIBUTE11 in VARCHAR2,
159 X_ATTRIBUTE12 in VARCHAR2,
160 X_ATTRIBUTE13 in VARCHAR2,
161 X_ATTRIBUTE14 in VARCHAR2,
162 X_ATTRIBUTE15 in VARCHAR2
163 ) is
164 cursor C is select ROWID from AK_FOREIGN_KEY_COLUMNS
165 where FOREIGN_KEY_NAME = X_FOREIGN_KEY_NAME;
166 begin
167 insert into AK_FOREIGN_KEY_COLUMNS (
168 FOREIGN_KEY_NAME,
169 ATTRIBUTE_APPLICATION_ID,
170 ATTRIBUTE_CODE,
171 FOREIGN_KEY_SEQUENCE,
172 CREATION_DATE,
173 CREATED_BY,
174 LAST_UPDATE_DATE,
175 LAST_UPDATED_BY,
176 LAST_UPDATE_LOGIN,
177 ATTRIBUTE_CATEGORY,
178 ATTRIBUTE1,
179 ATTRIBUTE2,
180 ATTRIBUTE3,
181 ATTRIBUTE4,
182 ATTRIBUTE5,
183 ATTRIBUTE6,
184 ATTRIBUTE7,
185 ATTRIBUTE8,
186 ATTRIBUTE9,
187 ATTRIBUTE10,
188 ATTRIBUTE11,
189 ATTRIBUTE12,
190 ATTRIBUTE13,
191 ATTRIBUTE14,
192 ATTRIBUTE15
193 ) values (
194 X_FOREIGN_KEY_NAME,
195 X_ATTRIBUTE_APPLICATION_ID,
196 X_ATTRIBUTE_CODE,
197 X_FOREIGN_KEY_SEQUENCE,
198 X_CREATION_DATE,
199 X_CREATED_BY,
200 X_LAST_UPDATE_DATE,
201 X_LAST_UPDATED_BY,
202 X_LAST_UPDATE_LOGIN,
203 X_ATTRIBUTE_CATEGORY,
204 X_ATTRIBUTE1,
205 X_ATTRIBUTE2,
206 X_ATTRIBUTE3,
207 X_ATTRIBUTE4,
208 X_ATTRIBUTE5,
209 X_ATTRIBUTE6,
210 X_ATTRIBUTE7,
211 X_ATTRIBUTE8,
212 X_ATTRIBUTE9,
213 X_ATTRIBUTE10,
214 X_ATTRIBUTE11,
215 X_ATTRIBUTE12,
216 X_ATTRIBUTE13,
217 X_ATTRIBUTE14,
218 X_ATTRIBUTE15
219 );
220
221 open c;
222 fetch c into X_ROWID;
223 if (c%notfound) then
224 close c;
225 raise no_data_found;
226 end if;
227 close c;
228 end INSERT_AFKC_ROW;
229 --*****************************************************************************
230 procedure LOCK_ROW (
231 X_FOREIGN_KEY_NAME in VARCHAR2,
232 X_DATABASE_OBJECT_NAME in VARCHAR2,
233 X_UNIQUE_KEY_NAME in VARCHAR2,
234 X_APPLICATION_ID in NUMBER,
235 X_FROM_TO_NAME in VARCHAR2,
236 X_FROM_TO_DESCRIPTION in VARCHAR2,
237 X_TO_FROM_NAME in VARCHAR2,
238 X_TO_FROM_DESCRIPTION in VARCHAR2,
239 X_ATTRIBUTE_CATEGORY in VARCHAR2,
240 X_ATTRIBUTE1 in VARCHAR2,
241 X_ATTRIBUTE2 in VARCHAR2,
242 X_ATTRIBUTE3 in VARCHAR2,
243 X_ATTRIBUTE4 in VARCHAR2,
244 X_ATTRIBUTE5 in VARCHAR2,
245 X_ATTRIBUTE6 in VARCHAR2,
246 X_ATTRIBUTE7 in VARCHAR2,
247 X_ATTRIBUTE8 in VARCHAR2,
248 X_ATTRIBUTE9 in VARCHAR2,
249 X_ATTRIBUTE10 in VARCHAR2,
250 X_ATTRIBUTE11 in VARCHAR2,
251 X_ATTRIBUTE12 in VARCHAR2,
252 X_ATTRIBUTE13 in VARCHAR2,
253 X_ATTRIBUTE14 in VARCHAR2,
254 X_ATTRIBUTE15 in VARCHAR2
255 ) is
256 cursor c is select
257 DATABASE_OBJECT_NAME,
258 UNIQUE_KEY_NAME,
259 APPLICATION_ID,
260 ATTRIBUTE_CATEGORY,
261 ATTRIBUTE1,
262 ATTRIBUTE2,
263 ATTRIBUTE3,
264 ATTRIBUTE4,
265 ATTRIBUTE5,
266 ATTRIBUTE6,
267 ATTRIBUTE7,
268 ATTRIBUTE8,
269 ATTRIBUTE9,
270 ATTRIBUTE10,
271 ATTRIBUTE11,
272 ATTRIBUTE12,
273 ATTRIBUTE13,
274 ATTRIBUTE14,
275 ATTRIBUTE15
276 from AK_FOREIGN_KEYS
277 where FOREIGN_KEY_NAME = X_FOREIGN_KEY_NAME
278 for update of FOREIGN_KEY_NAME nowait;
279 recinfo c%rowtype;
280
281 cursor c1 is select
282 FROM_TO_NAME,
283 FROM_TO_DESCRIPTION,
284 TO_FROM_NAME,
285 TO_FROM_DESCRIPTION
286 from AK_FOREIGN_KEYS_TL
287 where FOREIGN_KEY_NAME = X_FOREIGN_KEY_NAME
288 and LANGUAGE = userenv('LANG')
289 for update of FOREIGN_KEY_NAME nowait;
290 tlinfo c1%rowtype;
291
292 begin
293 open c;
294 fetch c into recinfo;
295 if (c%notfound) then
296 close c;
297 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
298 app_exception.raise_exception;
299 end if;
300 close c;
301 if ( ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
302 OR ((recinfo.ATTRIBUTE10 is null)
303 AND (X_ATTRIBUTE10 is null)))
304 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
305 OR ((recinfo.ATTRIBUTE11 is null)
306 AND (X_ATTRIBUTE11 is null)))
307 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
308 OR ((recinfo.ATTRIBUTE12 is null)
309 AND (X_ATTRIBUTE12 is null)))
310 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
311 OR ((recinfo.ATTRIBUTE13 is null)
312 AND (X_ATTRIBUTE13 is null)))
313 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
314 OR ((recinfo.ATTRIBUTE14 is null)
315 AND (X_ATTRIBUTE14 is null)))
316 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
317 OR ((recinfo.ATTRIBUTE15 is null)
318 AND (X_ATTRIBUTE15 is null)))
319 AND (recinfo.DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME)
320 AND (recinfo.UNIQUE_KEY_NAME = X_UNIQUE_KEY_NAME)
321 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
322 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
323 OR ((recinfo.ATTRIBUTE_CATEGORY is null)
324 AND (X_ATTRIBUTE_CATEGORY is null)))
325 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
326 OR ((recinfo.ATTRIBUTE1 is null)
327 AND (X_ATTRIBUTE1 is null)))
328 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
329 OR ((recinfo.ATTRIBUTE2 is null)
330 AND (X_ATTRIBUTE2 is null)))
331 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
332 OR ((recinfo.ATTRIBUTE3 is null)
333 AND (X_ATTRIBUTE3 is null)))
334 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
335 OR ((recinfo.ATTRIBUTE4 is null)
336 AND (X_ATTRIBUTE4 is null)))
337 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
338 OR ((recinfo.ATTRIBUTE5 is null)
339 AND (X_ATTRIBUTE5 is null)))
340 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
341 OR ((recinfo.ATTRIBUTE6 is null)
342 AND (X_ATTRIBUTE6 is null)))
343 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
344 OR ((recinfo.ATTRIBUTE7 is null)
345 AND (X_ATTRIBUTE7 is null)))
346 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
347 OR ((recinfo.ATTRIBUTE8 is null)
348 AND (X_ATTRIBUTE8 is null)))
349 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
350 OR ((recinfo.ATTRIBUTE9 is null)
351 AND (X_ATTRIBUTE9 is null)))
352 ) then
353 null;
354 else
355 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
356 app_exception.raise_exception;
357 end if;
358
359 open c1;
363 return;
360 fetch c1 into tlinfo;
361 if (c1%notfound) then
362 close c1;
364 end if;
365 close c1;
366
367 if ( ((tlinfo.FROM_TO_NAME = X_FROM_TO_NAME)
368 OR ((tlinfo.FROM_TO_NAME is null)
369 AND (X_FROM_TO_NAME is null)))
370 AND ((tlinfo.FROM_TO_DESCRIPTION = X_FROM_TO_DESCRIPTION)
371 OR ((tlinfo.FROM_TO_DESCRIPTION is null)
372 AND (X_FROM_TO_DESCRIPTION is null)))
373 AND ((tlinfo.TO_FROM_NAME = X_TO_FROM_NAME)
374 OR ((tlinfo.TO_FROM_NAME is null)
375 AND (X_TO_FROM_NAME is null)))
376 AND ((tlinfo.TO_FROM_DESCRIPTION = X_TO_FROM_DESCRIPTION)
377 OR ((tlinfo.TO_FROM_DESCRIPTION is null)
378 AND (X_TO_FROM_DESCRIPTION is null)))
379 ) then
380 null;
381 else
382 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
383 app_exception.raise_exception;
384 end if;
385 return;
386 end LOCK_ROW;
387 --*****************************************************************************
388 procedure LOCK_AFKC_ROW (
389 X_FOREIGN_KEY_NAME in VARCHAR2,
390 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
391 X_ATTRIBUTE_CODE in VARCHAR2,
392 X_FOREIGN_KEY_SEQUENCE in NUMBER,
393 X_ATTRIBUTE_CATEGORY in VARCHAR2,
394 X_ATTRIBUTE1 in VARCHAR2,
395 X_ATTRIBUTE2 in VARCHAR2,
396 X_ATTRIBUTE3 in VARCHAR2,
397 X_ATTRIBUTE4 in VARCHAR2,
398 X_ATTRIBUTE5 in VARCHAR2,
399 X_ATTRIBUTE6 in VARCHAR2,
400 X_ATTRIBUTE7 in VARCHAR2,
401 X_ATTRIBUTE8 in VARCHAR2,
402 X_ATTRIBUTE9 in VARCHAR2,
403 X_ATTRIBUTE10 in VARCHAR2,
404 X_ATTRIBUTE11 in VARCHAR2,
405 X_ATTRIBUTE12 in VARCHAR2,
406 X_ATTRIBUTE13 in VARCHAR2,
407 X_ATTRIBUTE14 in VARCHAR2,
408 X_ATTRIBUTE15 in VARCHAR2
409 ) is
410 cursor c is select
411 FOREIGN_KEY_NAME,
412 ATTRIBUTE_APPLICATION_ID,
413 ATTRIBUTE_CODE,
414 FOREIGN_KEY_SEQUENCE,
415 ATTRIBUTE_CATEGORY,
416 ATTRIBUTE1,
417 ATTRIBUTE2,
418 ATTRIBUTE3,
419 ATTRIBUTE4,
420 ATTRIBUTE5,
421 ATTRIBUTE6,
422 ATTRIBUTE7,
423 ATTRIBUTE8,
424 ATTRIBUTE9,
425 ATTRIBUTE10,
426 ATTRIBUTE11,
427 ATTRIBUTE12,
428 ATTRIBUTE13,
429 ATTRIBUTE14,
430 ATTRIBUTE15
431 from AK_FOREIGN_KEY_COLUMNS
432 where FOREIGN_KEY_NAME = X_FOREIGN_KEY_NAME
433 and FOREIGN_KEY_SEQUENCE = X_FOREIGN_KEY_SEQUENCE
434 for update of FOREIGN_KEY_NAME nowait;
435 recinfo c%rowtype;
436 begin
437 open c;
438 fetch c into recinfo;
439 if (c%notfound) then
440 close c;
441 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
442 app_exception.raise_exception;
443 end if;
444 close c;
445 if ((recinfo.ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID)
446 AND (recinfo.ATTRIBUTE_CODE = X_ATTRIBUTE_CODE)
447 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
448 OR ((recinfo.ATTRIBUTE_CATEGORY is null)
449 AND (X_ATTRIBUTE_CATEGORY is null)))
450 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
451 OR ((recinfo.ATTRIBUTE1 is null)
452 AND (X_ATTRIBUTE1 is null)))
453 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
454 OR ((recinfo.ATTRIBUTE2 is null)
455 AND (X_ATTRIBUTE2 is null)))
456 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
457 OR ((recinfo.ATTRIBUTE3 is null)
458 AND (X_ATTRIBUTE3 is null)))
459 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
460 OR ((recinfo.ATTRIBUTE4 is null)
461 AND (X_ATTRIBUTE4 is null)))
462 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
463 OR ((recinfo.ATTRIBUTE5 is null)
464 AND (X_ATTRIBUTE5 is null)))
465 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
466 OR ((recinfo.ATTRIBUTE6 is null)
467 AND (X_ATTRIBUTE6 is null)))
468 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
469 OR ((recinfo.ATTRIBUTE7 is null)
470 AND (X_ATTRIBUTE7 is null)))
471 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
472 OR ((recinfo.ATTRIBUTE8 is null)
473 AND (X_ATTRIBUTE8 is null)))
474 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
475 OR ((recinfo.ATTRIBUTE9 is null)
476 AND (X_ATTRIBUTE9 is null)))
477 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
478 OR ((recinfo.ATTRIBUTE10 is null)
479 AND (X_ATTRIBUTE10 is null)))
483 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
480 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
481 OR ((recinfo.ATTRIBUTE11 is null)
482 AND (X_ATTRIBUTE11 is null)))
484 OR ((recinfo.ATTRIBUTE12 is null)
485 AND (X_ATTRIBUTE12 is null)))
486 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
487 OR ((recinfo.ATTRIBUTE13 is null)
488 AND (X_ATTRIBUTE13 is null)))
489 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
490 OR ((recinfo.ATTRIBUTE14 is null)
491 AND (X_ATTRIBUTE14 is null)))
492 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
493 OR ((recinfo.ATTRIBUTE15 is null)
494 AND (X_ATTRIBUTE15 is null)))
495 ) then
496 null;
497 else
498 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
499 app_exception.raise_exception;
500 end if;
501 end LOCK_AFKC_ROW;
502 --*****************************************************************************
503 procedure UPDATE_ROW (
504 X_FOREIGN_KEY_NAME in VARCHAR2,
505 X_DATABASE_OBJECT_NAME in VARCHAR2,
506 X_UNIQUE_KEY_NAME in VARCHAR2,
507 X_APPLICATION_ID in NUMBER,
508 X_FROM_TO_NAME in VARCHAR2,
509 X_FROM_TO_DESCRIPTION in VARCHAR2,
510 X_TO_FROM_NAME in VARCHAR2,
511 X_TO_FROM_DESCRIPTION in VARCHAR2,
512 X_LAST_UPDATE_DATE in DATE,
513 X_LAST_UPDATED_BY in NUMBER,
514 X_LAST_UPDATE_LOGIN in NUMBER,
515 X_ATTRIBUTE_CATEGORY in VARCHAR2,
516 X_ATTRIBUTE1 in VARCHAR2,
517 X_ATTRIBUTE2 in VARCHAR2,
518 X_ATTRIBUTE3 in VARCHAR2,
519 X_ATTRIBUTE4 in VARCHAR2,
520 X_ATTRIBUTE5 in VARCHAR2,
521 X_ATTRIBUTE6 in VARCHAR2,
522 X_ATTRIBUTE7 in VARCHAR2,
523 X_ATTRIBUTE8 in VARCHAR2,
524 X_ATTRIBUTE9 in VARCHAR2,
525 X_ATTRIBUTE10 in VARCHAR2,
526 X_ATTRIBUTE11 in VARCHAR2,
527 X_ATTRIBUTE12 in VARCHAR2,
528 X_ATTRIBUTE13 in VARCHAR2,
529 X_ATTRIBUTE14 in VARCHAR2,
530 X_ATTRIBUTE15 in VARCHAR2
531 ) is
532 begin
533 update AK_FOREIGN_KEYS set
534 FOREIGN_KEY_NAME = X_FOREIGN_KEY_NAME,
535 DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME,
536 UNIQUE_KEY_NAME = X_UNIQUE_KEY_NAME,
537 APPLICATION_ID = X_APPLICATION_ID,
538 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
539 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
540 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
541 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
542 ATTRIBUTE1 = X_ATTRIBUTE1,
543 ATTRIBUTE2 = X_ATTRIBUTE2,
544 ATTRIBUTE3 = X_ATTRIBUTE3,
545 ATTRIBUTE4 = X_ATTRIBUTE4,
546 ATTRIBUTE5 = X_ATTRIBUTE5,
547 ATTRIBUTE6 = X_ATTRIBUTE6,
548 ATTRIBUTE7 = X_ATTRIBUTE7,
549 ATTRIBUTE8 = X_ATTRIBUTE8,
550 ATTRIBUTE9 = X_ATTRIBUTE9,
551 ATTRIBUTE10 = X_ATTRIBUTE10,
552 ATTRIBUTE11 = X_ATTRIBUTE11,
553 ATTRIBUTE12 = X_ATTRIBUTE12,
554 ATTRIBUTE13 = X_ATTRIBUTE13,
555 ATTRIBUTE14 = X_ATTRIBUTE14,
556 ATTRIBUTE15 = X_ATTRIBUTE15
557 where FOREIGN_KEY_NAME = X_FOREIGN_KEY_NAME;
558 if (sql%notfound) then
559 raise no_data_found;
560 end if;
561
562 update AK_FOREIGN_KEYS_TL set
563 FROM_TO_NAME = X_FROM_TO_NAME,
564 FROM_TO_DESCRIPTION = X_FROM_TO_DESCRIPTION,
565 TO_FROM_NAME = X_TO_FROM_NAME,
566 TO_FROM_DESCRIPTION = X_TO_FROM_DESCRIPTION,
567 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
568 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
569 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
570 SOURCE_LANG = userenv('LANG')
571 where FOREIGN_KEY_NAME = X_FOREIGN_KEY_NAME
572 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
573
574 if (sql%notfound) then
575 raise no_data_found;
576 end if;
577 end UPDATE_ROW;
578 --*****************************************************************************
579 procedure UPDATE_AFKC_ROW (
580 X_FOREIGN_KEY_NAME in VARCHAR2,
581 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
582 X_ATTRIBUTE_CODE in VARCHAR2,
583 X_FOREIGN_KEY_SEQUENCE in NUMBER,
584 X_LAST_UPDATE_DATE in DATE,
585 X_LAST_UPDATED_BY in NUMBER,
586 X_LAST_UPDATE_LOGIN in NUMBER,
587 X_ATTRIBUTE_CATEGORY in VARCHAR2,
588 X_ATTRIBUTE1 in VARCHAR2,
589 X_ATTRIBUTE2 in VARCHAR2,
590 X_ATTRIBUTE3 in VARCHAR2,
591 X_ATTRIBUTE4 in VARCHAR2,
592 X_ATTRIBUTE5 in VARCHAR2,
593 X_ATTRIBUTE6 in VARCHAR2,
594 X_ATTRIBUTE7 in VARCHAR2,
595 X_ATTRIBUTE8 in VARCHAR2,
596 X_ATTRIBUTE9 in VARCHAR2,
597 X_ATTRIBUTE10 in VARCHAR2,
598 X_ATTRIBUTE11 in VARCHAR2,
599 X_ATTRIBUTE12 in VARCHAR2,
600 X_ATTRIBUTE13 in VARCHAR2,
601 X_ATTRIBUTE14 in VARCHAR2,
602 X_ATTRIBUTE15 in VARCHAR2
603 ) is
604 begin
605 update AK_FOREIGN_KEY_COLUMNS set
606 ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID,
607 ATTRIBUTE_CODE = X_ATTRIBUTE_CODE,
608 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
609 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
610 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
611 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
612 ATTRIBUTE1 = X_ATTRIBUTE1,
613 ATTRIBUTE2 = X_ATTRIBUTE2,
614 ATTRIBUTE3 = X_ATTRIBUTE3,
615 ATTRIBUTE4 = X_ATTRIBUTE4,
616 ATTRIBUTE5 = X_ATTRIBUTE5,
617 ATTRIBUTE6 = X_ATTRIBUTE6,
618 ATTRIBUTE7 = X_ATTRIBUTE7,
619 ATTRIBUTE8 = X_ATTRIBUTE8,
620 ATTRIBUTE9 = X_ATTRIBUTE9,
621 ATTRIBUTE10 = X_ATTRIBUTE10,
622 ATTRIBUTE11 = X_ATTRIBUTE11,
623 ATTRIBUTE12 = X_ATTRIBUTE12,
627 where FOREIGN_KEY_NAME = X_FOREIGN_KEY_NAME
624 ATTRIBUTE13 = X_ATTRIBUTE13,
625 ATTRIBUTE14 = X_ATTRIBUTE14,
626 ATTRIBUTE15 = X_ATTRIBUTE15
628 and FOREIGN_KEY_SEQUENCE = X_FOREIGN_KEY_SEQUENCE;
629 if (sql%notfound) then
630 raise no_data_found;
631 end if;
632 end UPDATE_AFKC_ROW;
633 --*****************************************************************************
634 procedure DELETE_ROW (
635 X_FOREIGN_KEY_NAME in VARCHAR2
636 ) is
637 begin
638 delete from AK_FOREIGN_KEYS
639 where FOREIGN_KEY_NAME = X_FOREIGN_KEY_NAME;
640
641 if (sql%notfound) then
642 raise no_data_found;
643 end if;
644
645 delete from AK_FOREIGN_KEYS_TL
646 where FOREIGN_KEY_NAME = X_FOREIGN_KEY_NAME;
647 if (sql%notfound) then
648 raise no_data_found;
649 end if;
650 end DELETE_ROW;
651 --*****************************************************************************
652 procedure DELETE_AFKC_ROW (
653 X_FOREIGN_KEY_NAME in VARCHAR2,
654 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
655 X_ATTRIBUTE_CODE in VARCHAR2,
656 X_FOREIGN_KEY_SEQUENCE in NUMBER
657 ) is
658 begin
659 if X_FOREIGN_KEY_SEQUENCE is null then
660 delete from AK_FOREIGN_KEY_COLUMNS
661 where FOREIGN_KEY_NAME = X_FOREIGN_KEY_NAME
662 and ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
663 and ATTRIBUTE_CODE = X_ATTRIBUTE_CODE;
664 else
665 delete from AK_FOREIGN_KEY_COLUMNS
666 where FOREIGN_KEY_NAME = X_FOREIGN_KEY_NAME
667 and FOREIGN_KEY_SEQUENCE = X_FOREIGN_KEY_SEQUENCE;
668 end if;
669
670 if (sql%notfound) then
671 raise no_data_found;
672 end if;
673 end DELETE_AFKC_ROW;
674 --*****************************************************************************
675 procedure ADD_LANGUAGE
676 is
677 begin
678 /* Mar/19/03 requested by Ric Ginsberg */
679 /* The following delete and update statements are commented out */
680 /* as a quick workaround to fix the time-consuming table handler issue */
681 /* Eventually we'll need to turn them into a separate fix_language procedure */
682 /*
683
684 delete from AK_FOREIGN_KEYS_TL T
685 where not exists
686 (select NULL
687 from AK_FOREIGN_KEYS B
688 where B.FOREIGN_KEY_NAME = T.FOREIGN_KEY_NAME
689 );
690
691 update AK_FOREIGN_KEYS_TL T set (
692 FROM_TO_NAME,
693 FROM_TO_DESCRIPTION,
694 TO_FROM_NAME,
695 TO_FROM_DESCRIPTION
696 ) = (select
697 B.FROM_TO_NAME,
698 B.FROM_TO_DESCRIPTION,
699 B.TO_FROM_NAME,
700 B.TO_FROM_DESCRIPTION
701 from AK_FOREIGN_KEYS_TL B
702 where B.FOREIGN_KEY_NAME = T.FOREIGN_KEY_NAME
703 and B.LANGUAGE = T.SOURCE_LANG)
704 where (
705 T.FOREIGN_KEY_NAME,
706 T.LANGUAGE
707 ) in (select
708 SUBT.FOREIGN_KEY_NAME,
709 SUBT.LANGUAGE
710 from AK_FOREIGN_KEYS_TL SUBB, AK_FOREIGN_KEYS_TL SUBT
711 where SUBB.FOREIGN_KEY_NAME = SUBT.FOREIGN_KEY_NAME
712 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
713 and (SUBB.FROM_TO_NAME <> SUBT.FROM_TO_NAME
714 or (SUBB.FROM_TO_NAME is null and SUBT.FROM_TO_NAME is not null)
715 or (SUBB.FROM_TO_NAME is not null and SUBT.FROM_TO_NAME is null)
716 or SUBB.FROM_TO_DESCRIPTION <> SUBT.FROM_TO_DESCRIPTION
717 or (SUBB.FROM_TO_DESCRIPTION is null and SUBT.FROM_TO_DESCRIPTION is not null)
718 or (SUBB.FROM_TO_DESCRIPTION is not null and SUBT.FROM_TO_DESCRIPTION is null)
719 or SUBB.TO_FROM_NAME <> SUBT.TO_FROM_NAME
720 or (SUBB.TO_FROM_NAME is null and SUBT.TO_FROM_NAME is not null)
721 or (SUBB.TO_FROM_NAME is not null and SUBT.TO_FROM_NAME is null)
722 or SUBB.TO_FROM_DESCRIPTION <> SUBT.TO_FROM_DESCRIPTION
723 or (SUBB.TO_FROM_DESCRIPTION is null and SUBT.TO_FROM_DESCRIPTION is not null)
724 or (SUBB.TO_FROM_DESCRIPTION is not null and SUBT.TO_FROM_DESCRIPTION is null)
725 ));
726
727 */
728
729 insert /*+ append parallel(tt) */ into AK_FOREIGN_KEYS_TL tt (
730 FOREIGN_KEY_NAME,
731 FROM_TO_NAME,
732 FROM_TO_DESCRIPTION,
733 TO_FROM_NAME,
734 TO_FROM_DESCRIPTION,
735 CREATED_BY,
736 CREATION_DATE,
737 LAST_UPDATED_BY,
738 LAST_UPDATE_DATE,
739 LAST_UPDATE_LOGIN,
740 LANGUAGE,
741 SOURCE_LANG
742 ) select /*+ parallel(v) parallel(t) use_nl(t) */ v.* from
743 (select /*+ no_merge ordered parallel(b) */
744 B.FOREIGN_KEY_NAME,
745 B.FROM_TO_NAME,
746 B.FROM_TO_DESCRIPTION,
747 B.TO_FROM_NAME,
748 B.TO_FROM_DESCRIPTION,
749 B.CREATED_BY,
750 B.CREATION_DATE,
751 B.LAST_UPDATED_BY,
752 B.LAST_UPDATE_DATE,
753 B.LAST_UPDATE_LOGIN,
754 L.LANGUAGE_CODE,
755 B.SOURCE_LANG
756 from AK_FOREIGN_KEYS_TL B, FND_LANGUAGES L
757 where L.INSTALLED_FLAG in ('I', 'B')
758 and B.LANGUAGE = userenv('LANG')
759 ) v, AK_FOREIGN_KEYS_TL T
760 where T.FOREIGN_KEY_NAME(+) = v.FOREIGN_KEY_NAME
761 and T.LANGUAGE(+) = v.LANGUAGE_CODE
762 and T.FOREIGN_KEY_NAME is NULL;
763
764 end ADD_LANGUAGE;
765 --*****************************************************************************
766 end AK_FOREIGN_KEYS_PKG;