1 package body AK_KEY_PVT as
2 /* $Header: akdvkeyb.pls 120.3 2005/09/15 22:18:28 tshort ship $: AKDVKEYB.pls */
3
4 --=======================================================
5 -- Procedure CREATE_FOREIGN_KEY
6 --
7 -- Usage Private API for creating a foreign key. This
8 -- API should only be called by other APIs that are
9 -- owned by the Core Modules Team (AK).
10 --
11 -- Desc Creates a foreign key using the given info. This
12 -- API should only be called by other APIs that are
13 -- owned by the Core Modules Team (AK).
14 --
15 -- Results The API returns the standard p_return_status parameter
16 -- indicating one of the standard return statuses :
17 -- * Unexpected error
18 -- * Error
19 -- * Success
20 -- Parameters Foreign Key columns
21 -- p_loader_timestamp : IN optional
22 -- If a timestamp is passed, the API will create the
23 -- record using this timestamp. Only the upload API
24 -- should call with this parameter loaded.
25 --
26 -- Version Initial version number = 1.0
27 -- History Current version number = 1.0
28 --=======================================================
29 procedure CREATE_FOREIGN_KEY (
30 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
31 p_api_version_number IN NUMBER,
32 p_init_msg_tbl IN BOOLEAN := FALSE,
33 p_msg_count OUT NOCOPY NUMBER,
34 p_msg_data OUT NOCOPY VARCHAR2,
35 p_return_status OUT NOCOPY VARCHAR2,
36 p_foreign_key_name IN VARCHAR2,
37 p_database_object_name IN VARCHAR2,
38 p_unique_key_name IN VARCHAR2,
39 p_application_id IN NUMBER,
40 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
41 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
42 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
43 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
44 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
45 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
46 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
47 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
48 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
49 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
50 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
51 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
52 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
53 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
54 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
55 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
56 p_from_to_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
57 p_from_to_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
58 p_to_from_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
59 p_to_from_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
60 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
61 p_creation_date IN DATE := FND_API.G_MISS_DATE,
62 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
63 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
64 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
65 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
66 p_pass IN NUMBER,
67 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
68 ) is
69 l_api_version_number CONSTANT number := 1.0;
70 l_api_name CONSTANT varchar2(30) := 'Create_Foreign_Key';
71 l_attribute_category VARCHAR2(30);
72 l_attribute1 VARCHAR2(150);
73 l_attribute2 VARCHAR2(150);
74 l_attribute3 VARCHAR2(150);
75 l_attribute4 VARCHAR2(150);
76 l_attribute5 VARCHAR2(150);
77 l_attribute6 VARCHAR2(150);
78 l_attribute7 VARCHAR2(150);
79 l_attribute8 VARCHAR2(150);
80 l_attribute9 VARCHAR2(150);
81 l_attribute10 VARCHAR2(150);
82 l_attribute11 VARCHAR2(150);
83 l_attribute12 VARCHAR2(150);
84 l_attribute13 VARCHAR2(150);
85 l_attribute14 VARCHAR2(150);
86 l_attribute15 VARCHAR2(150);
87 l_created_by number;
88 l_creation_date date;
89 l_error boolean;
90 l_from_to_description VARCHAR2(1500);
91 l_from_to_name VARCHAR2(45);
92 l_lang varchar2(30);
93 l_last_update_date date;
94 l_last_update_login number;
95 l_last_updated_by number;
96 l_return_status varchar2(1);
97 l_to_from_description VARCHAR2(1500);
98 l_to_from_name VARCHAR2(45);
99 begin
100
101 IF NOT FND_API.Compatible_API_Call (
102 l_api_version_number, p_api_version_number, l_api_name,
103 G_PKG_NAME) then
104 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
105 return;
106 END IF;
107
108 -- Initialize the message table if requested.
109
110 if p_init_msg_tbl then
111 FND_MSG_PUB.initialize;
112 end if;
113
114 savepoint start_create_foreign_key;
115
116 --** check to see if row already exists **
117 if AK_KEY_PVT.FOREIGN_KEY_EXISTS (
118 p_api_version_number => 1.0,
119 p_return_status => l_return_status,
120 p_foreign_key_name => p_foreign_key_name) then
121 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
122 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_EXISTS');
123 FND_MSG_PUB.Add;
124 end if;
125 --dbms_output.put_line(l_api_name || 'Error - Row already exists');
126 raise FND_API.G_EXC_ERROR;
127 end if;
128
129 --** validate table columns passed in **
130 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
131 if not VALIDATE_FOREIGN_KEY (
132 p_validation_level => p_validation_level,
133 p_api_version_number => 1.0,
134 p_return_status => l_return_status,
135 p_foreign_key_name => p_foreign_key_name,
136 p_database_object_name => p_database_object_name,
137 p_unique_key_name => p_unique_key_name,
138 p_application_id => p_application_id,
139 p_caller => AK_ON_OBJECTS_PVT.G_CREATE,
140 p_pass => p_pass
141 ) then
142 --dbms_output.put_line(l_api_name || 'validation failed');
143 raise FND_API.G_EXC_ERROR;
144 end if;
145 end if;
146
147 --** Load non-required columns if their values are given **
148 if (p_attribute_category <> FND_API.G_MISS_CHAR) then
149 l_attribute_category := p_attribute_category;
150 end if;
151
152 if (p_attribute1 <> FND_API.G_MISS_CHAR) then
153 l_attribute1 := p_attribute1;
154 end if;
155
156 if (p_attribute2 <> FND_API.G_MISS_CHAR) then
157 l_attribute2 := p_attribute2;
158 end if;
159
160 if (p_attribute3 <> FND_API.G_MISS_CHAR) then
161 l_attribute3 := p_attribute3;
162 end if;
163
164 if (p_attribute4 <> FND_API.G_MISS_CHAR) then
165 l_attribute4 := p_attribute4;
166 end if;
167
168 if (p_attribute5 <> FND_API.G_MISS_CHAR) then
169 l_attribute5 := p_attribute5;
170 end if;
171
172 if (p_attribute6 <> FND_API.G_MISS_CHAR) then
173 l_attribute6 := p_attribute6;
174 end if;
175
176 if (p_attribute7 <> FND_API.G_MISS_CHAR) then
177 l_attribute7:= p_attribute7;
178 end if;
179
180 if (p_attribute8 <> FND_API.G_MISS_CHAR) then
181 l_attribute8 := p_attribute8;
182 end if;
183
184 if (p_attribute9 <> FND_API.G_MISS_CHAR) then
185 l_attribute9 := p_attribute9;
186 end if;
187
188 if (p_attribute10 <> FND_API.G_MISS_CHAR) then
189 l_attribute10 := p_attribute10;
190 end if;
191
192 if (p_attribute11 <> FND_API.G_MISS_CHAR) then
193 l_attribute11 := p_attribute11;
194 end if;
195
196 if (p_attribute12 <> FND_API.G_MISS_CHAR) then
197 l_attribute12 := p_attribute12;
198 end if;
199
200 if (p_attribute13 <> FND_API.G_MISS_CHAR) then
201 l_attribute13 := p_attribute13;
202 end if;
203
204 if (p_attribute14 <> FND_API.G_MISS_CHAR) then
205 l_attribute14 := p_attribute14;
206 end if;
207
208 if (p_attribute15 <> FND_API.G_MISS_CHAR) then
209 l_attribute15 := p_attribute15;
210 end if;
211
212 if (p_from_to_name <> FND_API.G_MISS_CHAR) then
213 l_from_to_name := p_from_to_name;
214 end if;
215
216 if (p_from_to_description <> FND_API.G_MISS_CHAR) then
217 l_from_to_description := p_from_to_description;
218 end if;
219
220 if (p_to_from_name <> FND_API.G_MISS_CHAR) then
221 l_to_from_name := p_to_from_name;
222 end if;
223
224 if (p_to_from_description <> FND_API.G_MISS_CHAR) then
225 l_to_from_description := p_to_from_description;
226 end if;
227
228 if (p_created_by <> FND_API.G_MISS_NUM) then
229 l_created_by := p_created_by;
230 end if;
231
232 if (p_creation_date <> FND_API.G_MISS_DATE) then
233 l_creation_date := p_creation_date;
234 end if;
235
236 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
237 l_last_updated_by := p_last_updated_by;
238 end if;
239
240 if (p_last_update_date <> FND_API.G_MISS_DATE) then
241 l_last_update_date := p_last_update_date;
242 end if;
243
244 if (p_last_update_login <> FND_API.G_MISS_NUM) then
245 l_last_update_login := p_last_update_login;
246 end if;
247
248 -- Create record if no validation error was found
249 -- NOTE - Calling IS_UPDATEABLE for backward compatibility
250 -- old jlt files didn't have who columns and IS_UPDATEABLE
251 -- calls SET_WHO which populates those columns, for later
252 -- jlt files IS_UPDATEABLE will always return TRUE for CREATE
253
254 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
255 p_loader_timestamp => p_loader_timestamp,
256 p_created_by => l_created_by,
257 p_creation_date => l_creation_date,
258 p_last_updated_by => l_last_updated_by,
259 p_db_last_updated_by => null,
260 p_last_update_date => l_last_update_date,
261 p_db_last_update_date => null,
262 p_last_update_login => l_last_update_login,
263 p_create_or_update => 'CREATE') then
264 null;
265 end if;
266
267 select userenv('LANG') into l_lang
268 from dual;
269
270 insert into AK_FOREIGN_KEYS (
271 FOREIGN_KEY_NAME,
272 DATABASE_OBJECT_NAME,
273 UNIQUE_KEY_NAME,
274 APPLICATION_ID,
275 ATTRIBUTE_CATEGORY,
276 ATTRIBUTE1,
277 ATTRIBUTE2,
278 ATTRIBUTE3,
279 ATTRIBUTE4,
280 ATTRIBUTE5,
281 ATTRIBUTE6,
282 ATTRIBUTE7,
283 ATTRIBUTE8,
284 ATTRIBUTE9,
285 ATTRIBUTE10,
286 ATTRIBUTE11,
287 ATTRIBUTE12,
288 ATTRIBUTE13,
289 ATTRIBUTE14,
290 ATTRIBUTE15,
291 CREATION_DATE,
292 CREATED_BY,
293 LAST_UPDATE_DATE,
294 LAST_UPDATED_BY,
295 LAST_UPDATE_LOGIN
296 ) values (
297 p_foreign_key_name,
298 p_database_object_name,
299 p_unique_key_name,
300 p_application_id,
301 l_attribute_category,
302 l_attribute1,
303 l_attribute2,
304 l_attribute3,
305 l_attribute4,
306 l_attribute5,
307 l_attribute6,
308 l_attribute7,
309 l_attribute8,
310 l_attribute9,
311 l_attribute10,
312 l_attribute11,
313 l_attribute12,
314 l_attribute13,
315 l_attribute14,
316 l_attribute15,
317 l_creation_date,
318 l_created_by,
319 l_last_update_date,
320 l_last_updated_by,
321 l_last_update_login
322 );
323
324 --** row should exists before inserting rows for other languages **
325 if NOT AK_KEY_PVT.FOREIGN_KEY_EXISTS (
326 p_api_version_number => 1.0,
327 p_return_status => l_return_status,
328 p_foreign_key_name => p_foreign_key_name) then
329 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) THEN
330 FND_MESSAGE.SET_NAME('AK','AK_INSERT_FK_FAILED');
331 FND_MSG_PUB.Add;
332 end if;
333 --dbms_output.put_line(G_PKG_NAME || 'Error - First insert failed');
334 raise FND_API.G_EXC_ERROR;
335 end if;
336
337 insert into AK_FOREIGN_KEYS_TL (
338 FOREIGN_KEY_NAME,
339 LANGUAGE,
340 FROM_TO_NAME,
341 FROM_TO_DESCRIPTION,
342 TO_FROM_NAME,
343 TO_FROM_DESCRIPTION,
344 SOURCE_LANG,
345 CREATION_DATE,
346 CREATED_BY,
347 LAST_UPDATE_DATE,
348 LAST_UPDATED_BY,
349 LAST_UPDATE_LOGIN
350 ) select
351 p_foreign_key_name,
352 L.LANGUAGE_CODE,
353 l_from_to_name,
354 l_from_to_description,
355 l_to_from_name,
356 l_to_from_description,
357 decode(L.LANGUAGE_CODE, l_lang, L.LANGUAGE_CODE, l_lang),
358 l_creation_date,
359 l_created_by,
360 l_last_update_date,
361 l_last_updated_by,
362 l_last_update_login
363 from FND_LANGUAGES L
364 where L.INSTALLED_FLAG in ('I', 'B')
365 and not exists
366 (select NULL
367 from AK_FOREIGN_KEYS_TL T
368 where T.FOREIGN_KEY_NAME = p_foreign_key_name
369 and T.LANGUAGE = L.LANGUAGE_CODE);
370
371 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
372 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_CREATED');
373 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name);
374 FND_MSG_PUB.Add;
375 end if;
376
377 p_return_status := FND_API.G_RET_STS_SUCCESS;
378
379 FND_MSG_PUB.Count_And_Get (
380 p_count => p_msg_count,
381 p_data => p_msg_data);
382
383 EXCEPTION
384 WHEN VALUE_ERROR THEN
385 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
386 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_VALUE_ERROR');
387 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name);
388 FND_MSG_PUB.Add;
389 end if;
390 p_return_status := FND_API.G_RET_STS_ERROR;
391 rollback to start_create_foreign_key;
392 FND_MSG_PUB.Count_And_Get (
393 p_count => p_msg_count,
394 p_data => p_msg_data);
395 WHEN FND_API.G_EXC_ERROR THEN
399 FND_MSG_PUB.Add;
396 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
397 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_NOT_CREATED');
398 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name);
400 end if;
401 p_return_status := FND_API.G_RET_STS_ERROR;
402 rollback to start_create_foreign_key;
403 FND_MSG_PUB.Count_And_Get (
404 p_count => p_msg_count,
405 p_data => p_msg_data);
406 WHEN OTHERS THEN
407 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
408 rollback to start_create_foreign_key;
409 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_NOT_CREATED');
410 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name);
411 FND_MSG_PUB.Add;
412 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name,
413 SUBSTR (SQLERRM, 1, 240) );
414 FND_MSG_PUB.Count_And_Get (
415 p_count => p_msg_count,
416 p_data => p_msg_data);
417 end CREATE_FOREIGN_KEY;
418
419 --=======================================================
420 -- Procedure CREATE_FOREIGN_KEY_COLUMN
421 --
422 -- Usage Private API for creating a foreign key column record.
423 -- This API should only be called by other APIs that are
424 -- owned by the Core Modules Team (AK).
425 --
426 -- Desc Creates a foreign key column record using the given info.
427 -- This API should only be called by other APIs that are
428 -- owned by the Core Modules Team (AK).
429 --
430 -- Results The API returns the standard p_return_status parameter
431 -- indicating one of the standard return statuses :
432 -- * Unexpected error
433 -- * Error
434 -- * Success
435 -- Parameters Foreign Key Column columns
436 -- p_loader_timestamp : IN optional
437 -- If a timestamp is passed, the API will create the
438 -- record using this timestamp. Only the upload API
439 -- should call with this parameter loaded.
440 --
441 -- Version Initial version number = 1.0
442 -- History Current version number = 1.0
443 --=======================================================
444 procedure CREATE_FOREIGN_KEY_COLUMN (
445 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
446 p_api_version_number IN NUMBER,
447 p_init_msg_tbl IN BOOLEAN := FALSE,
448 p_msg_count OUT NOCOPY NUMBER,
449 p_msg_data OUT NOCOPY VARCHAR2,
450 p_return_status OUT NOCOPY VARCHAR2,
451 p_foreign_key_name IN VARCHAR2,
452 p_attribute_application_id IN NUMBER,
453 p_attribute_code IN VARCHAR2,
454 p_foreign_key_sequence IN NUMBER,
455 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
456 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
457 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
458 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
459 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
460 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
461 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
462 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
463 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
464 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
465 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
466 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
467 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
468 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
469 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
470 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
471 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
472 p_creation_date IN DATE := FND_API.G_MISS_DATE,
473 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
474 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
475 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
476 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
477 p_pass IN NUMBER,
478 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
479 ) is
480 l_api_version_number CONSTANT number := 1.0;
481 l_api_name CONSTANT varchar2(30) := 'Create_Foreign_Key_Column';
482 l_attribute_category VARCHAR2(30);
483 l_attribute1 VARCHAR2(150);
484 l_attribute2 VARCHAR2(150);
485 l_attribute3 VARCHAR2(150);
486 l_attribute4 VARCHAR2(150);
487 l_attribute5 VARCHAR2(150);
488 l_attribute6 VARCHAR2(150);
489 l_attribute7 VARCHAR2(150);
490 l_attribute8 VARCHAR2(150);
491 l_attribute9 VARCHAR2(150);
492 l_attribute10 VARCHAR2(150);
493 l_attribute11 VARCHAR2(150);
494 l_attribute12 VARCHAR2(150);
495 l_attribute13 VARCHAR2(150);
496 l_attribute14 VARCHAR2(150);
497 l_attribute15 VARCHAR2(150);
498 l_created_by number;
499 l_creation_date date;
500 l_error boolean;
501 l_last_update_date date;
502 l_last_update_login number;
503 l_last_updated_by number;
504 l_return_status varchar2(1);
505 begin
506
507 IF NOT FND_API.Compatible_API_Call (
511 return;
508 l_api_version_number, p_api_version_number, l_api_name,
509 G_PKG_NAME) then
510 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
512 END IF;
513
514 -- Initialize the message table if requested.
515
516 if p_init_msg_tbl then
517 FND_MSG_PUB.initialize;
518 end if;
519
520 savepoint start_create_key_column;
521
522 --** check to see if row already exists **
523 if AK_KEY_PVT.FOREIGN_KEY_COLUMN_EXISTS (
524 p_api_version_number => 1.0,
525 p_return_status => l_return_status,
526 p_foreign_key_name => p_foreign_key_name,
527 p_attribute_application_id => p_attribute_application_id,
528 p_attribute_code => p_attribute_code) then
529 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
530 FND_MESSAGE.SET_NAME('AK','AK_FK_COLUMN_EXISTS');
531 FND_MSG_PUB.Add;
532 end if;
533 raise FND_API.G_EXC_ERROR;
534 end if;
535
536 --** validate table columns passed in **
537 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
538 if not VALIDATE_FOREIGN_KEY_COLUMN (
539 p_validation_level => p_validation_level,
540 p_api_version_number => 1.0,
541 p_return_status => l_return_status,
542 p_foreign_key_name => p_foreign_key_name,
543 p_attribute_application_id => p_attribute_application_id,
544 p_attribute_code => p_attribute_code,
545 p_foreign_key_sequence => p_foreign_key_sequence,
546 p_caller => AK_ON_OBJECTS_PVT.G_CREATE,
547 p_pass => p_pass
548 ) then
549 raise FND_API.G_EXC_ERROR;
550 end if;
551 end if;
552
553 --** Load non-required columns if their values are given **
554 --
555 if (p_attribute_category <> FND_API.G_MISS_CHAR) then
556 l_attribute_category := p_attribute_category;
557 end if;
558
559 if (p_attribute1 <> FND_API.G_MISS_CHAR) then
560 l_attribute1 := p_attribute1;
561 end if;
562
563 if (p_attribute2 <> FND_API.G_MISS_CHAR) then
564 l_attribute2 := p_attribute2;
565 end if;
566
567 if (p_attribute3 <> FND_API.G_MISS_CHAR) then
568 l_attribute3 := p_attribute3;
569 end if;
570
571 if (p_attribute4 <> FND_API.G_MISS_CHAR) then
572 l_attribute4 := p_attribute4;
573 end if;
574
575 if (p_attribute5 <> FND_API.G_MISS_CHAR) then
576 l_attribute5 := p_attribute5;
577 end if;
578
579 if (p_attribute6 <> FND_API.G_MISS_CHAR) then
580 l_attribute6 := p_attribute6;
581 end if;
582
583 if (p_attribute7 <> FND_API.G_MISS_CHAR) then
584 l_attribute7:= p_attribute7;
585 end if;
586
587 if (p_attribute8 <> FND_API.G_MISS_CHAR) then
588 l_attribute8 := p_attribute8;
589 end if;
590
591 if (p_attribute9 <> FND_API.G_MISS_CHAR) then
592 l_attribute9 := p_attribute9;
593 end if;
594
595 if (p_attribute10 <> FND_API.G_MISS_CHAR) then
596 l_attribute10 := p_attribute10;
597 end if;
598
599 if (p_attribute11 <> FND_API.G_MISS_CHAR) then
600 l_attribute11 := p_attribute11;
601 end if;
602
603 if (p_attribute12 <> FND_API.G_MISS_CHAR) then
604 l_attribute12 := p_attribute12;
605 end if;
606
607 if (p_attribute13 <> FND_API.G_MISS_CHAR) then
608 l_attribute13 := p_attribute13;
609 end if;
610
611 if (p_attribute14 <> FND_API.G_MISS_CHAR) then
612 l_attribute14 := p_attribute14;
613 end if;
614
615 if (p_attribute15 <> FND_API.G_MISS_CHAR) then
616 l_attribute15 := p_attribute15;
617 end if;
618
619 if (p_created_by <> FND_API.G_MISS_NUM) then
620 l_created_by := p_created_by;
621 end if;
622
623 if (p_creation_date <> FND_API.G_MISS_DATE) then
624 l_creation_date := p_creation_date;
625 end if;
626
627 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
628 l_last_updated_by := p_last_updated_by;
629 end if;
630
631 if (p_last_update_date <> FND_API.G_MISS_DATE) then
632 l_last_update_date := p_last_update_date;
633 end if;
634
635 if (p_last_update_login <> FND_API.G_MISS_NUM) then
636 l_last_update_login := p_last_update_login;
637 end if;
638
639 -- Create record if no validation error was found
640 -- NOTE - Calling IS_UPDATEABLE for backward compatibility
641 -- old jlt files didn't have who columns and IS_UPDATEABLE
642 -- calls SET_WHO which populates those columns, for later
643 -- jlt files IS_UPDATEABLE will always return TRUE for CREATE
644
645 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
646 p_loader_timestamp => p_loader_timestamp,
647 p_created_by => l_created_by,
648 p_creation_date => l_creation_date,
649 p_last_updated_by => l_last_updated_by,
650 p_db_last_updated_by => null,
651 p_last_update_date => l_last_update_date,
652 p_db_last_update_date => null,
653 p_last_update_login => l_last_update_login,
654 p_create_or_update => 'CREATE') then
655 null;
656 end if;
657
658 insert into AK_FOREIGN_KEY_COLUMNS (
659 FOREIGN_KEY_NAME,
660 ATTRIBUTE_APPLICATION_ID,
661 ATTRIBUTE_CODE,
662 FOREIGN_KEY_SEQUENCE,
663 ATTRIBUTE_CATEGORY,
664 ATTRIBUTE1,
665 ATTRIBUTE2,
666 ATTRIBUTE3,
667 ATTRIBUTE4,
668 ATTRIBUTE5,
669 ATTRIBUTE6,
670 ATTRIBUTE7,
671 ATTRIBUTE8,
675 ATTRIBUTE12,
672 ATTRIBUTE9,
673 ATTRIBUTE10,
674 ATTRIBUTE11,
676 ATTRIBUTE13,
677 ATTRIBUTE14,
678 ATTRIBUTE15,
679 CREATION_DATE,
680 CREATED_BY,
681 LAST_UPDATE_DATE,
682 LAST_UPDATED_BY,
683 LAST_UPDATE_LOGIN
684 ) values (
685 p_foreign_key_name,
686 p_attribute_application_id,
687 p_attribute_code,
688 p_foreign_key_sequence,
689 l_attribute_category,
690 l_attribute1,
691 l_attribute2,
692 l_attribute3,
693 l_attribute4,
694 l_attribute5,
695 l_attribute6,
696 l_attribute7,
697 l_attribute8,
698 l_attribute9,
699 l_attribute10,
700 l_attribute11,
701 l_attribute12,
702 l_attribute13,
703 l_attribute14,
704 l_attribute15,
705 l_creation_date,
706 l_created_by,
707 l_last_update_date,
708 l_last_updated_by,
709 l_last_update_login
710 );
711
712 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
713 FND_MESSAGE.SET_NAME('AK','AK_FK_COLUMN_CREATED');
714 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name ||
715 ' ' || to_char(p_attribute_application_id) ||
716 ' ' || p_attribute_code);
717 FND_MSG_PUB.Add;
718 end if;
719
720 p_return_status := FND_API.G_RET_STS_SUCCESS;
721
722 FND_MSG_PUB.Count_And_Get (
723 p_count => p_msg_count,
724 p_data => p_msg_data);
725
726 EXCEPTION
727 WHEN VALUE_ERROR THEN
728 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
729 FND_MESSAGE.SET_NAME('AK','AK_FK_COLUMN_VALUE_ERROR');
730 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name ||
731 ' ' || to_char(p_attribute_application_id) ||
732 ' ' || p_attribute_code);
733 FND_MSG_PUB.Add;
734 end if;
735 p_return_status := FND_API.G_RET_STS_ERROR;
736 rollback to start_create_key_column;
737 FND_MSG_PUB.Count_And_Get (
738 p_count => p_msg_count,
739 p_data => p_msg_data);
740 WHEN FND_API.G_EXC_ERROR THEN
741 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
742 FND_MESSAGE.SET_NAME('AK','AK_FK_COLUMN_NOT_CREATED');
743 FND_MESSAGE.SET_TOKEN('OBJECT','AK_FOREIGN_KEY_COLUMN',TRUE);
744 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name ||
745 ' ' || to_char(p_attribute_application_id) ||
746 ' ' || p_attribute_code);
747 FND_MSG_PUB.Add;
748 end if;
749 p_return_status := FND_API.G_RET_STS_ERROR;
750 rollback to start_create_key_column;
751 FND_MSG_PUB.Count_And_Get (
752 p_count => p_msg_count,
753 p_data => p_msg_data);
754 WHEN OTHERS THEN
755 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
756 rollback to start_create_key_column;
757 FND_MESSAGE.SET_NAME('AK','AK_FK_COLUMN_NOT_CREATED');
758 FND_MESSAGE.SET_TOKEN('OBJECT','AK_FOREIGN_KEY_COLUMN',TRUE);
759 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name ||
760 ' ' || to_char(p_attribute_application_id) ||
761 ' ' || p_attribute_code);
762 FND_MSG_PUB.Add;
763 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
764 SUBSTR (SQLERRM, 1, 240) );
765 FND_MSG_PUB.Add;
766 FND_MSG_PUB.Count_And_Get (
767 p_count => p_msg_count,
768 p_data => p_msg_data);
769 end CREATE_FOREIGN_KEY_COLUMN;
770
771 --=======================================================
772 -- Procedure CREATE_UNIQUE_KEY
773 --
774 -- Usage Private API for creating a unique key. This
775 -- API should only be called by other APIs that are
776 -- owned by the Core Modules Team (AK).
777 --
778 -- Desc Creates a unique key using the given info. This
779 -- API should only be called by other APIs that are
780 -- owned by the Core Modules Team (AK).
781 --
782 -- Results The API returns the standard p_return_status parameter
783 -- indicating one of the standard return statuses :
784 -- * Unexpected error
785 -- * Error
786 -- * Success
787 -- Parameters Unique Key columns
788 -- p_loader_timestamp : IN optional
789 -- If a timestamp is passed, the API will create the
790 -- record using this timestamp. Only the upload API
791 -- should call with this parameter loaded.
792 --
793 -- Version Initial version number = 1.0
794 -- History Current version number = 1.0
795 --=======================================================
796 procedure CREATE_UNIQUE_KEY (
797 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
798 p_api_version_number IN NUMBER,
799 p_init_msg_tbl IN BOOLEAN := FALSE,
800 p_msg_count OUT NOCOPY NUMBER,
801 p_msg_data OUT NOCOPY VARCHAR2,
802 p_return_status OUT NOCOPY VARCHAR2,
803 p_unique_key_name IN VARCHAR2,
804 p_database_object_name IN VARCHAR2,
805 p_application_id IN NUMBER,
806 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
807 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
808 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
809 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
810 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
811 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
812 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
816 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
813 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
814 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
815 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
817 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
818 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
819 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
820 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
821 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
822 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
823 p_creation_date IN DATE := FND_API.G_MISS_DATE,
824 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
825 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
826 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
827 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
828 p_pass IN NUMBER,
829 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
830 ) is
831 l_attribute_category VARCHAR2(30);
832 l_attribute1 VARCHAR2(150);
833 l_attribute2 VARCHAR2(150);
834 l_attribute3 VARCHAR2(150);
835 l_attribute4 VARCHAR2(150);
836 l_attribute5 VARCHAR2(150);
837 l_attribute6 VARCHAR2(150);
838 l_attribute7 VARCHAR2(150);
839 l_attribute8 VARCHAR2(150);
840 l_attribute9 VARCHAR2(150);
841 l_attribute10 VARCHAR2(150);
842 l_attribute11 VARCHAR2(150);
843 l_attribute12 VARCHAR2(150);
844 l_attribute13 VARCHAR2(150);
845 l_attribute14 VARCHAR2(150);
846 l_attribute15 VARCHAR2(150);
847 l_api_version_number CONSTANT number := 1.0;
848 l_api_name CONSTANT varchar2(30) := 'Create_Unique_Key';
849 l_created_by number;
850 l_creation_date date;
851 l_dummy number;
852 l_error boolean;
853 l_last_update_date date;
854 l_last_update_login number;
855 l_last_updated_by number;
856 l_return_status varchar2(1);
857 begin
858
859 IF NOT FND_API.Compatible_API_Call (
860 l_api_version_number, p_api_version_number, l_api_name,
861 G_PKG_NAME) then
862 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
863 return;
864 END IF;
865
866 -- Initialize the message table if requested.
867
868 if p_init_msg_tbl then
869 FND_MSG_PUB.initialize;
870 end if;
871
872 savepoint start_create_unique_key;
873
874 --** check to see if row already exists **
875 if AK_KEY_PVT.UNIQUE_KEY_EXISTS (
876 p_api_version_number => 1.0,
877 p_return_status => l_return_status,
878 p_unique_key_name => p_unique_key_name) then
879 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
880 FND_MESSAGE.SET_NAME('AK','AK_UNIQUE_KEY_EXISTS');
881 FND_MSG_PUB.Add;
882 end if;
883 raise FND_API.G_EXC_ERROR;
884 end if;
885
886 --** validate table columns passed in **
887 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
888 if not VALIDATE_UNIQUE_KEY (
889 p_validation_level => p_validation_level,
890 p_api_version_number => 1.0,
891 p_return_status => l_return_status,
892 p_unique_key_name => p_unique_key_name,
893 p_database_object_name => p_database_object_name,
894 p_application_id => p_application_id,
895 p_caller => AK_ON_OBJECTS_PVT.G_CREATE,
896 p_pass => p_pass
897 ) then
898 raise FND_API.G_EXC_ERROR;
899 end if;
900 end if;
901
902 --
903 -- Load non-required columns if their values are given
904 if (p_attribute_category <> FND_API.G_MISS_CHAR) then
905 l_attribute_category := p_attribute_category;
906 end if;
907
908 if (p_attribute1 <> FND_API.G_MISS_CHAR) then
909 l_attribute1 := p_attribute1;
910 end if;
911
912 if (p_attribute2 <> FND_API.G_MISS_CHAR) then
913 l_attribute2 := p_attribute2;
914 end if;
915
916 if (p_attribute3 <> FND_API.G_MISS_CHAR) then
917 l_attribute3 := p_attribute3;
918 end if;
919
920 if (p_attribute4 <> FND_API.G_MISS_CHAR) then
921 l_attribute4 := p_attribute4;
922 end if;
923
924 if (p_attribute5 <> FND_API.G_MISS_CHAR) then
925 l_attribute5 := p_attribute5;
926 end if;
927
928 if (p_attribute6 <> FND_API.G_MISS_CHAR) then
929 l_attribute6 := p_attribute6;
930 end if;
931
932 if (p_attribute7 <> FND_API.G_MISS_CHAR) then
933 l_attribute7:= p_attribute7;
934 end if;
935
936 if (p_attribute8 <> FND_API.G_MISS_CHAR) then
937 l_attribute8 := p_attribute8;
938 end if;
939
940 if (p_attribute9 <> FND_API.G_MISS_CHAR) then
941 l_attribute9 := p_attribute9;
942 end if;
943
944 if (p_attribute10 <> FND_API.G_MISS_CHAR) then
945 l_attribute10 := p_attribute10;
946 end if;
947
948 if (p_attribute11 <> FND_API.G_MISS_CHAR) then
949 l_attribute11 := p_attribute11;
950 end if;
951
952 if (p_attribute12 <> FND_API.G_MISS_CHAR) then
953 l_attribute12 := p_attribute12;
954 end if;
955
956 if (p_attribute13 <> FND_API.G_MISS_CHAR) then
957 l_attribute13 := p_attribute13;
958 end if;
959
960 if (p_attribute14 <> FND_API.G_MISS_CHAR) then
964 if (p_attribute15 <> FND_API.G_MISS_CHAR) then
961 l_attribute14 := p_attribute14;
962 end if;
963
965 l_attribute15 := p_attribute15;
966 end if;
967
968 if (p_created_by <> FND_API.G_MISS_NUM) then
969 l_created_by := p_created_by;
970 end if;
971
972 if (p_creation_date <> FND_API.G_MISS_DATE) then
973 l_creation_date := p_creation_date;
974 end if;
975
976 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
977 l_last_updated_by := p_last_updated_by;
978 end if;
979
980 if (p_last_update_date <> FND_API.G_MISS_DATE) then
981 l_last_update_date := p_last_update_date;
982 end if;
983
984 if (p_last_update_login <> FND_API.G_MISS_NUM) then
985 l_last_update_login := p_last_update_login;
986 end if;
987
988 -- Create record if no validation error was found
989 -- NOTE - Calling IS_UPDATEABLE for backward compatibility
990 -- old jlt files didn't have who columns and IS_UPDATEABLE
991 -- calls SET_WHO which populates those columns, for later
992 -- jlt files IS_UPDATEABLE will always return TRUE for CREATE
993
994 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
995 p_loader_timestamp => p_loader_timestamp,
996 p_created_by => l_created_by,
997 p_creation_date => l_creation_date,
998 p_last_updated_by => l_last_updated_by,
999 p_db_last_updated_by => null,
1000 p_last_update_date => l_last_update_date,
1001 p_db_last_update_date => null,
1002 p_last_update_login => l_last_update_login,
1003 p_create_or_update => 'CREATE') then
1004 null;
1005 end if;
1006
1007 insert into AK_UNIQUE_KEYS (
1008 UNIQUE_KEY_NAME,
1009 DATABASE_OBJECT_NAME,
1010 APPLICATION_ID,
1011 ATTRIBUTE_CATEGORY,
1012 ATTRIBUTE1,
1013 ATTRIBUTE2,
1014 ATTRIBUTE3,
1015 ATTRIBUTE4,
1016 ATTRIBUTE5,
1017 ATTRIBUTE6,
1018 ATTRIBUTE7,
1019 ATTRIBUTE8,
1020 ATTRIBUTE9,
1021 ATTRIBUTE10,
1022 ATTRIBUTE11,
1023 ATTRIBUTE12,
1024 ATTRIBUTE13,
1025 ATTRIBUTE14,
1026 ATTRIBUTE15,
1027 CREATION_DATE,
1028 CREATED_BY,
1029 LAST_UPDATE_DATE,
1030 LAST_UPDATED_BY,
1031 LAST_UPDATE_LOGIN
1032 ) values (
1033 p_unique_key_name,
1034 p_database_object_name,
1035 p_application_id,
1036 l_attribute_category,
1037 l_attribute1,
1038 l_attribute2,
1039 l_attribute3,
1040 l_attribute4,
1041 l_attribute5,
1042 l_attribute6,
1043 l_attribute7,
1044 l_attribute8,
1045 l_attribute9,
1046 l_attribute10,
1047 l_attribute11,
1048 l_attribute12,
1049 l_attribute13,
1050 l_attribute14,
1051 l_attribute15,
1052 l_creation_date,
1053 l_created_by,
1054 l_last_update_date,
1055 l_last_updated_by,
1056 l_last_update_login
1057 );
1058
1059 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
1060 FND_MESSAGE.SET_NAME('AK','AK_UNIQUE_KEY_CREATED');
1061 FND_MESSAGE.SET_TOKEN('KEY',p_unique_key_name);
1062 FND_MSG_PUB.Add;
1063 end if;
1064
1065 p_return_status := FND_API.G_RET_STS_SUCCESS;
1066
1067 FND_MSG_PUB.Count_And_Get (
1068 p_count => p_msg_count,
1069 p_data => p_msg_data);
1070
1071 EXCEPTION
1072 WHEN VALUE_ERROR THEN
1073 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1074 FND_MESSAGE.SET_NAME('AK','AK_UNIQUE_KEY_VALUE_ERROR');
1075 FND_MESSAGE.SET_TOKEN('KEY',p_unique_key_name);
1076 FND_MSG_PUB.Add;
1077 end if;
1078 p_return_status := FND_API.G_RET_STS_ERROR;
1079 rollback to start_create_unique_key;
1080 FND_MSG_PUB.Count_And_Get (
1081 p_count => p_msg_count,
1082 p_data => p_msg_data);
1083 WHEN FND_API.G_EXC_ERROR THEN
1084 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1085 FND_MESSAGE.SET_NAME('AK','AK_UNIQUE_KEY_NOT_CREATED');
1086 FND_MESSAGE.SET_TOKEN('KEY',p_unique_key_name);
1087 FND_MSG_PUB.Add;
1088 end if;
1089 p_return_status := FND_API.G_RET_STS_ERROR;
1090 rollback to start_create_unique_key;
1091 FND_MSG_PUB.Count_And_Get (
1092 p_count => p_msg_count,
1093 p_data => p_msg_data);
1094 WHEN OTHERS THEN
1095 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1096 rollback to start_create_unique_key;
1097 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1098 SUBSTR (SQLERRM, 1, 240) );
1099 FND_MSG_PUB.Add;
1100 FND_MSG_PUB.Count_And_Get (
1101 p_count => p_msg_count,
1102 p_data => p_msg_data);
1103 end CREATE_UNIQUE_KEY;
1104
1105 --=======================================================
1106 -- Procedure CREATE_UNIQUE_KEY_COLUMN
1107 --
1108 -- Usage Private API for creating a unique key column record.
1109 -- This API should only be called by other APIs that are
1110 -- owned by the Core Modules Team (AK).
1111 --
1112 -- Desc Creates a unique key column record using the given info.
1113 -- This API should only be called by other APIs that are
1114 -- owned by the Core Modules Team (AK).
1115 --
1116 -- Results The API returns the standard p_return_status parameter
1117 -- indicating one of the standard return statuses :
1118 -- * Unexpected error
1119 -- * Error
1120 -- * Success
1121 -- Parameters Unique Key Column columns
1122 -- p_loader_timestamp : IN optional
1126 --
1123 -- If a timestamp is passed, the API will create the
1124 -- record using this timestamp. Only the upload API
1125 -- should call with this parameter loaded.
1127 -- Version Initial version number = 1.0
1128 -- History Current version number = 1.0
1129 --=======================================================
1130 procedure CREATE_UNIQUE_KEY_COLUMN (
1131 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1132 p_api_version_number IN NUMBER,
1133 p_init_msg_tbl IN BOOLEAN := FALSE,
1134 p_msg_count OUT NOCOPY NUMBER,
1135 p_msg_data OUT NOCOPY VARCHAR2,
1136 p_return_status OUT NOCOPY VARCHAR2,
1137 p_unique_key_name IN VARCHAR2,
1138 p_attribute_application_id IN NUMBER,
1139 p_attribute_code IN VARCHAR2,
1140 p_unique_key_sequence IN NUMBER,
1141 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
1142 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1143 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1144 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1145 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1146 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1147 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1148 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1149 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1150 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1151 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1152 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1153 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1154 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1155 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1156 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1157 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
1158 p_creation_date IN DATE := FND_API.G_MISS_DATE,
1159 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
1160 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
1161 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
1162 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
1163 p_pass IN NUMBER,
1164 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
1165 ) is
1166 l_attribute_category VARCHAR2(30);
1167 l_attribute1 VARCHAR2(150);
1168 l_attribute2 VARCHAR2(150);
1169 l_attribute3 VARCHAR2(150);
1170 l_attribute4 VARCHAR2(150);
1171 l_attribute5 VARCHAR2(150);
1172 l_attribute6 VARCHAR2(150);
1173 l_attribute7 VARCHAR2(150);
1174 l_attribute8 VARCHAR2(150);
1175 l_attribute9 VARCHAR2(150);
1176 l_attribute10 VARCHAR2(150);
1177 l_attribute11 VARCHAR2(150);
1178 l_attribute12 VARCHAR2(150);
1179 l_attribute13 VARCHAR2(150);
1180 l_attribute14 VARCHAR2(150);
1181 l_attribute15 VARCHAR2(150);
1182 l_api_version_number CONSTANT number := 1.0;
1183 l_api_name CONSTANT varchar2(30) := 'Create_Unique_Key_Column';
1184 l_created_by number;
1185 l_creation_date date;
1186 l_error boolean;
1187 l_last_update_date date;
1188 l_last_update_login number;
1189 l_last_updated_by number;
1190 l_return_status varchar2(1);
1191 begin
1192
1193 IF NOT FND_API.Compatible_API_Call (
1194 l_api_version_number, p_api_version_number, l_api_name,
1195 G_PKG_NAME) then
1196 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1197 return;
1198 END IF;
1199
1200 -- Initialize the message table if requested.
1201
1202 if p_init_msg_tbl then
1203 FND_MSG_PUB.initialize;
1204 end if;
1205
1206 savepoint start_create_key_column;
1207
1208 --** check to see if row already exists **
1209 if AK_KEY_PVT.UNIQUE_KEY_COLUMN_EXISTS (
1210 p_api_version_number => 1.0,
1211 p_return_status => l_return_status,
1212 p_unique_key_name => p_unique_key_name,
1213 p_attribute_application_id => p_attribute_application_id,
1214 p_attribute_code => p_attribute_code) then
1215 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1216 FND_MESSAGE.SET_NAME('AK','AK_UK_COLUMN_EXISTS');
1217 FND_MSG_PUB.Add;
1218 end if;
1219 raise FND_API.G_EXC_ERROR;
1220 end if;
1221
1222 --** validate table columns passed in **
1223 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
1224 if not VALIDATE_UNIQUE_KEY_COLUMN (
1225 p_validation_level => p_validation_level,
1226 p_api_version_number => 1.0,
1227 p_return_status => l_return_status,
1228 p_unique_key_name => p_unique_key_name,
1229 p_attribute_application_id => p_attribute_application_id,
1230 p_attribute_code => p_attribute_code,
1231 p_unique_key_sequence => p_unique_key_sequence,
1232 p_caller => AK_ON_OBJECTS_PVT.G_CREATE,
1233 p_pass => p_pass
1234 ) then
1235 raise FND_API.G_EXC_ERROR;
1236 end if;
1237 end if;
1238
1239 --
1240 -- Load non-required columns if their values are given
1241 --
1242 if (p_attribute_category <> FND_API.G_MISS_CHAR) then
1243 l_attribute_category := p_attribute_category;
1244 end if;
1245
1249
1246 if (p_attribute1 <> FND_API.G_MISS_CHAR) then
1247 l_attribute1 := p_attribute1;
1248 end if;
1250 if (p_attribute2 <> FND_API.G_MISS_CHAR) then
1251 l_attribute2 := p_attribute2;
1252 end if;
1253
1254 if (p_attribute3 <> FND_API.G_MISS_CHAR) then
1255 l_attribute3 := p_attribute3;
1256 end if;
1257
1258 if (p_attribute4 <> FND_API.G_MISS_CHAR) then
1259 l_attribute4 := p_attribute4;
1260 end if;
1261
1262 if (p_attribute5 <> FND_API.G_MISS_CHAR) then
1263 l_attribute5 := p_attribute5;
1264 end if;
1265
1266 if (p_attribute6 <> FND_API.G_MISS_CHAR) then
1267 l_attribute6 := p_attribute6;
1268 end if;
1269
1270 if (p_attribute7 <> FND_API.G_MISS_CHAR) then
1271 l_attribute7:= p_attribute7;
1272 end if;
1273
1274 if (p_attribute8 <> FND_API.G_MISS_CHAR) then
1275 l_attribute8 := p_attribute8;
1276 end if;
1277
1278 if (p_attribute9 <> FND_API.G_MISS_CHAR) then
1279 l_attribute9 := p_attribute9;
1280 end if;
1281
1282 if (p_attribute10 <> FND_API.G_MISS_CHAR) then
1283 l_attribute10 := p_attribute10;
1284 end if;
1285
1286 if (p_attribute11 <> FND_API.G_MISS_CHAR) then
1287 l_attribute11 := p_attribute11;
1288 end if;
1289
1290 if (p_attribute12 <> FND_API.G_MISS_CHAR) then
1291 l_attribute12 := p_attribute12;
1292 end if;
1293
1294 if (p_attribute13 <> FND_API.G_MISS_CHAR) then
1295 l_attribute13 := p_attribute13;
1296 end if;
1297
1298 if (p_attribute14 <> FND_API.G_MISS_CHAR) then
1299 l_attribute14 := p_attribute14;
1300 end if;
1301
1302 if (p_attribute15 <> FND_API.G_MISS_CHAR) then
1303 l_attribute15 := p_attribute15;
1304 end if;
1305
1306 if (p_created_by <> FND_API.G_MISS_NUM) then
1307 l_created_by := p_created_by;
1308 end if;
1309
1310 if (p_creation_date <> FND_API.G_MISS_DATE) then
1311 l_creation_date := p_creation_date;
1312 end if;
1313
1314 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
1315 l_last_updated_by := p_last_updated_by;
1316 end if;
1317
1318 if (p_last_update_date <> FND_API.G_MISS_DATE) then
1319 l_last_update_date := p_last_update_date;
1320 end if;
1321
1322 if (p_last_update_login <> FND_API.G_MISS_NUM) then
1323 l_last_update_login := p_last_update_login;
1324 end if;
1325
1326 -- Create record if no validation error was found
1327 -- NOTE - Calling IS_UPDATEABLE for backward compatibility
1328 -- old jlt files didn't have who columns and IS_UPDATEABLE
1329 -- calls SET_WHO which populates those columns, for later
1330 -- jlt files IS_UPDATEABLE will always return TRUE for CREATE
1331
1332 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
1333 p_loader_timestamp => p_loader_timestamp,
1334 p_created_by => l_created_by,
1335 p_creation_date => l_creation_date,
1336 p_last_updated_by => l_last_updated_by,
1337 p_db_last_updated_by => null,
1338 p_last_update_date => l_last_update_date,
1339 p_db_last_update_date => null,
1340 p_last_update_login => l_last_update_login,
1341 p_create_or_update => 'CREATE') then
1342 null;
1343 end if;
1344
1345 insert into AK_UNIQUE_KEY_COLUMNS (
1346 UNIQUE_KEY_NAME,
1347 ATTRIBUTE_APPLICATION_ID,
1348 ATTRIBUTE_CODE,
1349 UNIQUE_KEY_SEQUENCE,
1350 ATTRIBUTE_CATEGORY,
1351 ATTRIBUTE1,
1352 ATTRIBUTE2,
1353 ATTRIBUTE3,
1354 ATTRIBUTE4,
1355 ATTRIBUTE5,
1356 ATTRIBUTE6,
1357 ATTRIBUTE7,
1358 ATTRIBUTE8,
1359 ATTRIBUTE9,
1360 ATTRIBUTE10,
1361 ATTRIBUTE11,
1362 ATTRIBUTE12,
1363 ATTRIBUTE13,
1364 ATTRIBUTE14,
1365 ATTRIBUTE15,
1366 CREATION_DATE,
1367 CREATED_BY,
1368 LAST_UPDATE_DATE,
1369 LAST_UPDATED_BY,
1370 LAST_UPDATE_LOGIN
1371 ) values (
1372 p_unique_key_name,
1373 p_attribute_application_id,
1374 p_attribute_code,
1375 p_unique_key_sequence,
1376 l_attribute_category,
1377 l_attribute1,
1378 l_attribute2,
1379 l_attribute3,
1380 l_attribute4,
1381 l_attribute5,
1382 l_attribute6,
1383 l_attribute7,
1384 l_attribute8,
1385 l_attribute9,
1386 l_attribute10,
1387 l_attribute11,
1388 l_attribute12,
1389 l_attribute13,
1390 l_attribute14,
1391 l_attribute15,
1392 l_creation_date,
1393 l_created_by,
1394 l_last_update_date,
1395 l_last_updated_by,
1396 l_last_update_login
1397 );
1398
1399 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
1400 FND_MESSAGE.SET_NAME('AK','AK_UK_COLUMN_CREATED');
1401 FND_MESSAGE.SET_TOKEN('KEY',p_unique_key_name || ' ' ||
1402 to_char(p_attribute_application_id) || ' "' ||
1403 p_attribute_code || '"');
1404 FND_MSG_PUB.Add;
1405 end if;
1406
1407 p_return_status := FND_API.G_RET_STS_SUCCESS;
1408
1409 FND_MSG_PUB.Count_And_Get (
1410 p_count => p_msg_count,
1411 p_data => p_msg_data);
1412
1413 EXCEPTION
1414 WHEN VALUE_ERROR THEN
1415 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1416 FND_MESSAGE.SET_NAME('AK','AK_UK_COLUMN_VALUE_ERROR');
1417 FND_MESSAGE.SET_TOKEN('KEY',p_unique_key_name || ' ' ||
1418 to_char(p_attribute_application_id) || ' "' ||
1419 p_attribute_code || '"');
1420 FND_MSG_PUB.Add;
1421 end if;
1425 p_count => p_msg_count,
1422 p_return_status := FND_API.G_RET_STS_ERROR;
1423 rollback to start_create_key_column;
1424 FND_MSG_PUB.Count_And_Get (
1426 p_data => p_msg_data);
1427 WHEN FND_API.G_EXC_ERROR THEN
1428 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1429 FND_MESSAGE.SET_NAME('AK','AK_UK_COLUMN_NOT_CREATED');
1430 FND_MESSAGE.SET_TOKEN('KEY',p_unique_key_name || ' ' ||
1431 to_char(p_attribute_application_id) || ' "' ||
1432 p_attribute_code || '"');
1433 FND_MSG_PUB.Add;
1434 end if;
1435 p_return_status := FND_API.G_RET_STS_ERROR;
1436 rollback to start_create_key_column;
1437 FND_MSG_PUB.Count_And_Get (
1438 p_count => p_msg_count,
1439 p_data => p_msg_data);
1440 WHEN OTHERS THEN
1441 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1442 rollback to start_create_key_column;
1443 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1444 SUBSTR (SQLERRM, 1, 240) );
1445 FND_MSG_PUB.Add;
1446 FND_MSG_PUB.Count_And_Get (
1447 p_count => p_msg_count,
1448 p_data => p_msg_data);
1449 end CREATE_UNIQUE_KEY_COLUMN;
1450
1451 --=======================================================
1452 -- Procedure DELETE_FOREIGN_KEY
1453 --
1454 -- Usage Private API for deleting a foreign key. This
1455 -- API should only be called by other APIs that are
1456 -- owned by the Core Modules Team (AK).
1457 --
1458 -- Desc Deletes a foreign key with the given key value.
1459 --
1460 -- Results The API returns the standard p_return_status parameter
1461 -- indicating one of the standard return statuses :
1462 -- * Unexpected error
1463 -- * Error
1464 -- * Success
1465 -- Parameters p_foreign_key_name : IN required
1466 -- The name of the foreign key to be deleted.
1467 -- p_delete_cascade : IN required
1468 -- If p_delete_cascade flag is 'Y', also delete all
1469 -- rows in other tables that references this record.
1470 -- Otherwise, this record will not be deleted if there
1471 -- are any other rows referencing it.
1472 --
1473 -- Version Initial version number = 1.0
1474 -- History Current version number = 1.0
1475 --=======================================================
1476 procedure DELETE_FOREIGN_KEY (
1477 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1478 p_api_version_number IN NUMBER,
1479 p_init_msg_tbl IN BOOLEAN := FALSE,
1480 p_msg_count OUT NOCOPY NUMBER,
1481 p_msg_data OUT NOCOPY VARCHAR2,
1482 p_return_status OUT NOCOPY VARCHAR2,
1483 p_foreign_key_name IN VARCHAR2,
1484 p_delete_cascade IN VARCHAR2
1485 ) is
1486 cursor l_get_columns_csr is
1487 select ATTRIBUTE_APPLICATION_ID, ATTRIBUTE_CODE
1488 from AK_FOREIGN_KEY_COLUMNS
1489 where FOREIGN_KEY_NAME = p_foreign_key_name;
1490 cursor l_get_relations_csr is
1491 select FLOW_APPLICATION_ID, FLOW_CODE, FROM_PAGE_APPL_ID, FROM_PAGE_CODE,
1492 FROM_REGION_APPL_ID, FROM_REGION_CODE, TO_PAGE_APPL_ID,
1493 TO_PAGE_CODE, TO_REGION_APPL_ID, TO_REGION_CODE
1494 from AK_FLOW_REGION_RELATIONS
1495 where FOREIGN_KEY_NAME = p_foreign_key_name;
1496 l_api_version_number CONSTANT number := 1.0;
1497 l_api_name CONSTANT varchar2(30):= 'Delete_Foreign_Key';
1498 l_attribute_appl_id NUMBER;
1499 l_attribute_code VARCHAR2(30);
1500 l_msg_count NUMBER;
1501 l_msg_data VARCHAR2(2000);
1502 l_flow_application_id NUMBER;
1503 l_flow_code VARCHAR2(30);
1504 l_from_page_appl_id NUMBER;
1505 l_from_page_code VARCHAR2(30);
1506 l_from_region_appl_id NUMBER;
1507 l_from_region_code VARCHAR2(30);
1508 l_return_status varchar2(1);
1509 l_to_page_appl_id NUMBER;
1510 l_to_page_code VARCHAR2(30);
1511 l_to_region_appl_id NUMBER;
1512 l_to_region_code VARCHAR2(30);
1513 begin
1514 IF NOT FND_API.Compatible_API_Call (
1515 l_api_version_number, p_api_version_number, l_api_name,
1516 G_PKG_NAME) then
1517 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1518 return;
1519 END IF;
1520
1521 -- Initialize the message table if requested.
1522
1523 if p_init_msg_tbl then
1524 FND_MSG_PUB.initialize;
1525 end if;
1526
1527 savepoint start_delete_foreign_key;
1528
1529 --
1530 -- error if foreign key to be deleted does not exists
1531 --
1532 if NOT AK_KEY_PVT.FOREIGN_KEY_EXISTS (
1533 p_api_version_number => 1.0,
1534 p_return_status => l_return_status,
1535 p_foreign_key_name => p_foreign_key_name) then
1536 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1537 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_DOES_NOT_EXIST');
1538 FND_MSG_PUB.Add;
1539 end if;
1540 raise FND_API.G_EXC_ERROR;
1541 end if;
1542
1543 if (p_delete_cascade = 'N') then
1544 --
1545 -- If we are not deleting any referencing records, we cannot
1546 -- delete the foreign key if it is being referenced in any of
1547 -- following tables.
1548 --
1549 -- AK_FOREIGN_KEY_COLUMNS
1550 --
1551 open l_get_columns_csr;
1552 fetch l_get_columns_csr into l_attribute_appl_id, l_attribute_code;
1553 if l_get_columns_csr%found then
1554 close l_get_columns_csr;
1555 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1559 raise FND_API.G_EXC_ERROR;
1556 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_DEL_REF_FK_FKC');
1557 FND_MSG_PUB.Add;
1558 end if;
1560 end if;
1561 close l_get_columns_csr;
1562 --
1563 -- AK_FLOW_REGION_RELATIONS
1564 --
1565 open l_get_relations_csr;
1566 fetch l_get_relations_csr into l_flow_application_id, l_flow_code,
1567 l_from_page_appl_id, l_from_page_code,
1568 l_from_region_appl_id, l_from_region_code,
1569 l_to_page_appl_id, l_to_page_code,
1570 l_to_region_appl_id, l_to_region_code;
1571 if l_get_relations_csr%found then
1572 close l_get_relations_csr;
1573 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1574 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_DEL_REF_FK_REL');
1575 FND_MSG_PUB.Add;
1576 end if;
1577 raise FND_API.G_EXC_ERROR;
1578 end if;
1579 close l_get_relations_csr;
1580
1581 else
1582 --
1583 -- Otherwise, delete all referencing rows in other tables
1584 --
1585 -- AK_FOREIGN_KEY_COLUMNS
1586 --
1587 open l_get_columns_csr;
1588 loop
1589 fetch l_get_columns_csr into l_attribute_appl_id, l_attribute_code;
1590 exit when l_get_columns_csr%notfound;
1591 AK_KEY_PVT.DELETE_FOREIGN_KEY_COLUMN (
1592 p_validation_level => p_validation_level,
1593 p_api_version_number => 1.0,
1594 p_msg_count => l_msg_count,
1595 p_msg_data => l_msg_data,
1596 p_return_status => l_return_status,
1597 p_foreign_key_name => p_foreign_key_name,
1598 p_attribute_application_id => l_attribute_appl_id,
1599 p_attribute_code => l_attribute_code,
1600 p_delete_cascade => p_delete_cascade
1601 );
1602 if (l_return_status = FND_API.G_RET_STS_ERROR) or
1603 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1604 close l_get_columns_csr;
1605 raise FND_API.G_EXC_ERROR;
1606 end if;
1607 end loop;
1608 close l_get_columns_csr;
1609 --
1610 -- AK_FLOW_REGION_RELATIONS
1611 --
1612 open l_get_relations_csr;
1613 loop
1614 fetch l_get_relations_csr into l_flow_application_id, l_flow_code,
1615 l_from_page_appl_id, l_from_page_code,
1616 l_from_region_appl_id, l_from_region_code,
1617 l_to_page_appl_id, l_to_page_code,
1618 l_to_region_appl_id, l_to_region_code;
1619 exit when l_get_relations_csr%notfound;
1620 AK_FLOW_PVT.DELETE_REGION_RELATION (
1621 p_validation_level => p_validation_level,
1622 p_api_version_number => 1.0,
1623 p_return_status => l_return_status,
1624 p_msg_count => l_msg_count,
1625 p_msg_data => l_msg_data,
1626 p_flow_application_id => l_flow_application_id,
1627 p_flow_code => l_flow_code,
1628 p_foreign_key_name => p_foreign_key_name,
1629 p_from_page_appl_id => l_from_page_appl_id,
1630 p_from_page_code => l_from_page_code,
1631 p_from_region_appl_id => l_from_region_appl_id,
1632 p_from_region_code => l_from_region_code,
1633 p_to_page_appl_id => l_to_page_appl_id,
1634 p_to_page_code => l_to_page_code,
1635 p_to_region_appl_id => l_to_region_appl_id,
1636 p_to_region_code => l_to_region_code,
1637 p_delete_cascade => p_delete_cascade
1638 );
1639 if (l_return_status = FND_API.G_RET_STS_ERROR) or
1640 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1641 close l_get_relations_csr;
1642 raise FND_API.G_EXC_ERROR;
1643 end if;
1644 end loop;
1645 close l_get_relations_csr;
1646
1647 end if;
1648
1649 --
1650 -- delete foreign key once we checked that there are no references
1651 -- to it, or all references have been deleted.
1652 --
1653 delete from ak_foreign_keys
1654 where foreign_key_name = p_foreign_key_name;
1655
1656 if (sql%notfound) then
1657 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1658 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_DOES_NOT_EXIST');
1659 FND_MSG_PUB.Add;
1660 end if;
1661 raise FND_API.G_EXC_ERROR;
1662 end if;
1663
1664 delete from ak_foreign_keys_tl
1665 where foreign_key_name = p_foreign_key_name;
1666
1667 if (sql%notfound) then
1668 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1669 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_DOES_NOT_EXIST');
1670 FND_MSG_PUB.Add;
1671 end if;
1672 raise FND_API.G_EXC_ERROR;
1673 end if;
1674
1675 --
1676 -- Load success message
1677 --
1678 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) then
1679 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_DELETED');
1680 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name);
1681 FND_MSG_PUB.Add;
1682 end if;
1683
1684 p_return_status := FND_API.G_RET_STS_SUCCESS;
1685
1686 FND_MSG_PUB.Count_And_Get (
1687 p_count => p_msg_count,
1688 p_data => p_msg_data);
1689
1690 EXCEPTION
1691 WHEN FND_API.G_EXC_ERROR THEN
1692 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1693 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_NOT_DELETED');
1694 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name);
1695 FND_MSG_PUB.Add;
1696 end if;
1697 p_return_status := FND_API.G_RET_STS_ERROR;
1698 rollback to start_delete_foreign_key;
1699 FND_MSG_PUB.Count_And_Get (
1700 p_count => p_msg_count,
1701 p_data => p_msg_data);
1702 WHEN OTHERS THEN
1703 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1704 rollback to start_delete_foreign_key;
1705 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1706 SUBSTR (SQLERRM, 1, 240) );
1707 FND_MSG_PUB.Add;
1708 FND_MSG_PUB.Count_And_Get (
1709 p_count => p_msg_count,
1713 --=======================================================
1710 p_data => p_msg_data);
1711 end DELETE_FOREIGN_KEY;
1712
1714 -- Procedure DELETE_FOREIGN_KEY_COLUMN
1715 --
1716 -- Usage Private API for deleting a foreign key column record.
1717 -- This API should only be called by other APIs that are
1718 -- owned by the Core Modules Team (AK).
1719 --
1720 -- Desc Deletes a foreign key column record with the given key value.
1721 --
1722 -- Results The API returns the standard p_return_status parameter
1723 -- indicating one of the standard return statuses :
1724 -- * Unexpected error
1725 -- * Error
1726 -- * Success
1727 -- Parameters p_foreign_key_name : IN required
1728 -- p_attribute_application_id : IN required
1729 -- p_attribute_code : IN required
1730 -- The key of the foreign key column record to be deleted.
1731 -- p_delete_cascade : IN required
1732 -- If p_delete_cascade flag is 'Y', also delete all
1733 -- rows in other tables that references this record.
1734 -- Otherwise, this record will not be deleted if there
1735 -- are any other rows referencing it.
1736 --
1737 -- Version Initial version number = 1.0
1738 -- History Current version number = 1.0
1739 --=======================================================
1740 procedure DELETE_FOREIGN_KEY_COLUMN (
1741 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1742 p_api_version_number IN NUMBER,
1743 p_init_msg_tbl IN BOOLEAN := FALSE,
1744 p_msg_count OUT NOCOPY NUMBER,
1745 p_msg_data OUT NOCOPY VARCHAR2,
1746 p_return_status OUT NOCOPY VARCHAR2,
1747 p_foreign_key_name IN VARCHAR2,
1748 p_attribute_application_id IN NUMBER,
1749 p_attribute_code IN VARCHAR2,
1750 p_delete_cascade IN VARCHAR2
1751 ) is
1752 l_api_version_number CONSTANT number := 1.0;
1753 l_api_name CONSTANT varchar2(30):= 'Delete_Foreign_Key_Column';
1754 l_return_status varchar2(1);
1755 begin
1756 IF NOT FND_API.Compatible_API_Call (
1757 l_api_version_number, p_api_version_number, l_api_name,
1758 G_PKG_NAME) then
1759 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1760 return;
1761 END IF;
1762
1763 -- Initialize the message table if requested.
1764
1765 if p_init_msg_tbl then
1766 FND_MSG_PUB.initialize;
1767 end if;
1768
1769 savepoint start_delete_key_column;
1770
1771 --
1772 -- error if foreign key to be deleted does not exists
1773 --
1774 if NOT AK_KEY_PVT.FOREIGN_KEY_COLUMN_EXISTS (
1775 p_api_version_number => 1.0,
1776 p_return_status => l_return_status,
1777 p_foreign_key_name => p_foreign_key_name,
1778 p_attribute_application_id => p_attribute_application_id,
1779 p_attribute_code => p_attribute_code) then
1780 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1781 FND_MESSAGE.SET_NAME('AK','AK_FK_COLUMN_DOES_NOT_EXIST');
1782 FND_MSG_PUB.Add;
1783 end if;
1784 raise FND_API.G_EXC_ERROR;
1785 end if;
1786
1787 if (p_delete_cascade = 'N') then
1788 --
1789 -- If we are not deleting any referencing records, we cannot
1790 -- delete the foreign key column if it is being referenced in any of
1791 -- following tables.
1792 --
1793 -- (currently none - add logic here in the future)
1794 --
1795 null;
1796 else
1797 --
1798 -- Otherwise, delete all referencing rows in other tables
1799 --
1800 -- (currently none - add logic here in the future)
1801 --
1802 null;
1803 end if;
1804
1805 --
1806 -- delete foreign key column once we checked that there are no references
1807 -- to it, or all references have been deleted.
1808 --
1809 delete from ak_foreign_key_columns
1810 where foreign_key_name = p_foreign_key_name
1811 and attribute_application_id = p_attribute_application_id
1812 and attribute_code = p_attribute_code;
1813
1814 if (sql%notfound) then
1815 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1816 FND_MESSAGE.SET_NAME('AK','AK_FK_COLUMN_DOES_NOT_EXIST');
1817 FND_MESSAGE.SET_TOKEN('OBJECT', 'AK_FOREIGN_KEY_COLUMN',TRUE);
1818 FND_MSG_PUB.Add;
1819 end if;
1820 raise FND_API.G_EXC_ERROR;
1821 end if;
1822
1823 --
1824 -- Load success message
1825 --
1826 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) then
1827 FND_MESSAGE.SET_NAME('AK','AK_FK_COLUMN_DELETED');
1828 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name ||
1829 ' ' || to_char(p_attribute_application_id) ||
1830 ' ' || p_attribute_code);
1831 FND_MSG_PUB.Add;
1832 end if;
1833
1834 p_return_status := FND_API.G_RET_STS_SUCCESS;
1835
1836 FND_MSG_PUB.Count_And_Get (
1837 p_count => p_msg_count,
1838 p_data => p_msg_data);
1839
1840 EXCEPTION
1841 WHEN FND_API.G_EXC_ERROR THEN
1842 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1843 FND_MESSAGE.SET_NAME('AK','AK_FK_COLUMN_NOT_DELETED');
1844 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name ||
1845 ' ' || to_char(p_attribute_application_id) ||
1846 ' ' || p_attribute_code);
1847 FND_MSG_PUB.Add;
1848 end if;
1849 p_return_status := FND_API.G_RET_STS_ERROR;
1850 rollback to start_delete_key_column;
1854 WHEN OTHERS THEN
1851 FND_MSG_PUB.Count_And_Get (
1852 p_count => p_msg_count,
1853 p_data => p_msg_data);
1855 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1856 rollback to start_delete_key_column;
1857 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1858 SUBSTR (SQLERRM, 1, 240) );
1859 FND_MSG_PUB.Add;
1860 FND_MSG_PUB.Count_And_Get (
1861 p_count => p_msg_count,
1862 p_data => p_msg_data);
1863 end DELETE_FOREIGN_KEY_COLUMN;
1864
1865 --=======================================================
1866 -- Procedure DELETE_UNIQUE_KEY
1867 --
1868 -- Usage Private API for deleting a unique key. This
1869 -- API should only be called by other APIs that are
1870 -- owned by the Core Modules Team (AK).
1871 --
1872 -- Desc Deletes a unique key with the given key value.
1873 --
1874 -- Results The API returns the standard p_return_status parameter
1875 -- indicating one of the standard return statuses :
1876 -- * Unexpected error
1877 -- * Error
1878 -- * Success
1879 -- Parameters p_unique_key_name : IN required
1880 -- The name of the unique key to be deleted.
1881 -- p_delete_cascade : IN required
1882 -- If p_delete_cascade flag is 'Y', also delete all
1883 -- rows in other tables that references this record.
1884 -- Otherwise, this record will not be deleted if there
1885 -- are any other rows referencing it.
1886 --
1887 -- Version Initial version number = 1.0
1888 -- History Current version number = 1.0
1889 --=======================================================
1890 procedure DELETE_UNIQUE_KEY (
1891 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1892 p_api_version_number IN NUMBER,
1893 p_init_msg_tbl IN BOOLEAN := FALSE,
1894 p_msg_count OUT NOCOPY NUMBER,
1895 p_msg_data OUT NOCOPY VARCHAR2,
1896 p_return_status OUT NOCOPY VARCHAR2,
1897 p_unique_key_name IN VARCHAR2,
1898 p_delete_cascade IN VARCHAR2
1899 ) is
1900 cursor l_get_columns_csr is
1901 select ATTRIBUTE_APPLICATION_ID, ATTRIBUTE_CODE
1902 from AK_UNIQUE_KEY_COLUMNS
1903 where UNIQUE_KEY_NAME = p_unique_key_name;
1904 cursor l_get_fk_csr is
1905 select FOREIGN_KEY_NAME
1906 from AK_FOREIGN_KEYS
1907 where UNIQUE_KEY_NAME = p_unique_key_name;
1908
1909 l_api_version_number CONSTANT number := 1.0;
1910 l_api_name CONSTANT varchar2(30):= 'Delete_Unique_Key';
1911 l_attribute_appl_id NUMBER;
1912 l_attribute_code VARCHAR2(30);
1913 l_msg_count NUMBER;
1914 l_msg_data VARCHAR2(2000);
1915 l_foreign_key_name VARCHAR2(30);
1916 l_return_status varchar2(1);
1917 begin
1918 IF NOT FND_API.Compatible_API_Call (
1919 l_api_version_number, p_api_version_number, l_api_name,
1920 G_PKG_NAME) then
1921 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1922 return;
1923 END IF;
1924
1925 -- Initialize the message table if requested.
1926
1927 if p_init_msg_tbl then
1928 FND_MSG_PUB.initialize;
1929 end if;
1930
1931 savepoint start_delete_unique_key;
1932
1933 --
1934 -- error if foreign key to be deleted does not exists
1935 --
1936 if NOT AK_KEY_PVT.UNIQUE_KEY_EXISTS (
1937 p_api_version_number => 1.0,
1938 p_return_status => l_return_status,
1939 p_unique_key_name => p_unique_key_name) then
1940 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1941 FND_MESSAGE.SET_NAME('AK','AK_UNIQUE_KEY_DOES_NOT_EXIST');
1942 FND_MSG_PUB.Add;
1943 end if;
1944 raise FND_API.G_EXC_ERROR;
1945 end if;
1946
1947 if (p_delete_cascade = 'N') then
1948 --
1949 -- If we are not deleting any referencing records, we cannot
1950 -- delete the primary key if it is being referenced in any of
1951 -- following tables.
1952 --
1953 -- AK_UNIQUE_KEY_COLUMNS
1954 --
1955 open l_get_columns_csr;
1956 fetch l_get_columns_csr into l_attribute_appl_id, l_attribute_code;
1957 if l_get_columns_csr%found then
1958 close l_get_columns_csr;
1959 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1960 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_DEL_REF_UK_UKC');
1961 FND_MSG_PUB.Add;
1962 end if;
1963 raise FND_API.G_EXC_ERROR;
1964 end if;
1965 close l_get_columns_csr;
1966 --
1967 -- AK_FOREIGN_KEYS
1968 --
1969 open l_get_fk_csr;
1970 fetch l_get_fk_csr into l_foreign_key_name;
1971 if l_get_fk_csr%found then
1972 close l_get_fk_csr;
1973 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1974 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_DEL_REF_UK_FK');
1975 FND_MSG_PUB.Add;
1976 end if;
1977 raise FND_API.G_EXC_ERROR;
1978 end if;
1979 close l_get_fk_csr;
1980
1981 else
1982 --
1983 -- Otherwise, delete all referencing rows in other tables
1984 --
1985 -- AK_UNIQUE_KEYS
1986 --
1987 open l_get_columns_csr;
1988 loop
1989 fetch l_get_columns_csr into l_attribute_appl_id, l_attribute_code;
1990 exit when l_get_columns_csr%notfound;
1991 AK_KEY_PVT.DELETE_UNIQUE_KEY_COLUMN (
1992 p_validation_level => p_validation_level,
1993 p_api_version_number => 1.0,
1994 p_msg_count => l_msg_count,
1995 p_msg_data => l_msg_data,
1999 p_attribute_code => l_attribute_code,
1996 p_return_status => l_return_status,
1997 p_unique_key_name => p_unique_key_name,
1998 p_attribute_application_id => l_attribute_appl_id,
2000 p_delete_cascade => p_delete_cascade
2001 );
2002 if (l_return_status = FND_API.G_RET_STS_ERROR) or
2003 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
2004 close l_get_columns_csr;
2005 raise FND_API.G_EXC_ERROR;
2006 end if;
2007 end loop;
2008 close l_get_columns_csr;
2009 --
2010 -- AK_FLOW_REGION_RELATIONS
2011 --
2012 open l_get_fk_csr;
2013 loop
2014 fetch l_get_fk_csr into l_foreign_key_name;
2015 exit when l_get_fk_csr%notfound;
2016 AK_KEY_PVT.DELETE_FOREIGN_KEY (
2017 p_validation_level => p_validation_level,
2018 p_api_version_number => 1.0,
2019 p_msg_count => l_msg_count,
2020 p_msg_data => l_msg_data,
2021 p_return_status => l_return_status,
2022 p_foreign_key_name => l_foreign_key_name,
2023 p_delete_cascade => p_delete_cascade
2024 );
2025 if (l_return_status = FND_API.G_RET_STS_ERROR) or
2026 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
2027 close l_get_fk_csr;
2028 raise FND_API.G_EXC_ERROR;
2029 end if;
2030 end loop;
2031 close l_get_fk_csr;
2032
2033 end if;
2034
2035 --
2036 -- delete unique key once we checked that there are no references
2037 -- to it, or all references have been deleted.
2038 --
2039 delete from ak_unique_keys
2040 where unique_key_name = p_unique_key_name;
2041
2042 if (sql%notfound) then
2043 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2044 FND_MESSAGE.SET_NAME('AK','AK_UNIQUE_KEY_DOES_NOT_EXIST');
2045 FND_MSG_PUB.Add;
2046 end if;
2047 raise FND_API.G_EXC_ERROR;
2048 end if;
2049
2050 --
2051 -- Load success message
2052 --
2053 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) then
2054 FND_MESSAGE.SET_NAME('AK','AK_UNIQUE_KEY_DELETED');
2055 FND_MESSAGE.SET_TOKEN('KEY',p_unique_key_name);
2056 FND_MSG_PUB.Add;
2057 end if;
2058
2059 p_return_status := FND_API.G_RET_STS_SUCCESS;
2060
2061 FND_MSG_PUB.Count_And_Get (
2062 p_count => p_msg_count,
2063 p_data => p_msg_data);
2064
2065 EXCEPTION
2066 WHEN FND_API.G_EXC_ERROR THEN
2067 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2068 FND_MESSAGE.SET_NAME('AK','AK_UNIQUE_KEY_NOT_DELETED');
2069 FND_MESSAGE.SET_TOKEN('KEY',p_unique_key_name);
2070 FND_MSG_PUB.Add;
2071 end if;
2072 p_return_status := FND_API.G_RET_STS_ERROR;
2073 rollback to start_delete_unique_key;
2074 FND_MSG_PUB.Count_And_Get (
2075 p_count => p_msg_count,
2076 p_data => p_msg_data);
2077 WHEN OTHERS THEN
2078 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2079 rollback to start_delete_unique_key;
2080 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2081 SUBSTR (SQLERRM, 1, 240) );
2082 FND_MSG_PUB.Add;
2083 FND_MSG_PUB.Count_And_Get (
2084 p_count => p_msg_count,
2085 p_data => p_msg_data);
2086 end DELETE_UNIQUE_KEY;
2087
2088 --=======================================================
2089 -- Procedure DELETE_UNIQUE_KEY_COLUMN
2090 --
2091 -- Usage Private API for deleting a unique key column record.
2092 -- This API should only be called by other APIs that are
2093 -- owned by the Core Modules Team (AK).
2094 --
2095 -- Desc Deletes a unique key column record with the given key value.
2096 --
2097 -- Results The API returns the standard p_return_status parameter
2098 -- indicating one of the standard return statuses :
2099 -- * Unexpected error
2100 -- * Error
2101 -- * Success
2102 -- Parameters p_unique_key_name : IN required
2103 -- p_attribute_application_id : IN required
2104 -- p_attribute_code : IN required
2105 -- The key of the unique key column record to be deleted.
2106 -- p_delete_cascade : IN required
2107 -- If p_delete_cascade flag is 'Y', also delete all
2108 -- rows in other tables that references this record.
2109 -- Otherwise, this record will not be deleted if there
2110 -- are any other rows referencing it.
2111 --
2112 -- Version Initial version number = 1.0
2113 -- History Current version number = 1.0
2114 --=======================================================
2115 procedure DELETE_UNIQUE_KEY_COLUMN (
2116 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2117 p_api_version_number IN NUMBER,
2118 p_init_msg_tbl IN BOOLEAN := FALSE,
2119 p_msg_count OUT NOCOPY NUMBER,
2120 p_msg_data OUT NOCOPY VARCHAR2,
2121 p_return_status OUT NOCOPY VARCHAR2,
2122 p_unique_key_name IN VARCHAR2,
2123 p_attribute_application_id IN NUMBER,
2124 p_attribute_code IN VARCHAR2,
2125 p_delete_cascade IN VARCHAR2,
2126 p_override IN VARCHAR2 := 'N'
2127 ) is
2128 cursor l_get_fkc_csr is
2129 select fkc.FOREIGN_KEY_NAME, fkc.ATTRIBUTE_APPLICATION_ID,
2130 fkc.ATTRIBUTE_CODE
2131 from AK_FOREIGN_KEY_COLUMNS fkc,
2132 AK_FOREIGN_KEYS fk,
2133 AK_UNIQUE_KEY_COLUMNS pkc
2134 where fk.unique_key_name = pkc.unique_key_name
2135 and fk.foreign_key_name = fkc.foreign_key_name
2139 and pkc.attribute_code = p_attribute_code;
2136 and fkc.foreign_key_sequence = pkc.unique_key_sequence
2137 and pkc.unique_key_name = p_unique_key_name
2138 and pkc.attribute_application_id = p_attribute_application_id
2140 l_api_version_number CONSTANT number := 1.0;
2141 l_api_name CONSTANT varchar2(30):= 'Delete_Unique_Key_Column';
2142 l_attribute_appl_id NUMBER;
2143 l_attribute_code VARCHAR2(30);
2144 l_msg_count NUMBER;
2145 l_msg_data VARCHAR2(2000);
2146 l_foreign_key_name VARCHAR2(30);
2147 l_return_status varchar2(1);
2148 begin
2149 IF NOT FND_API.Compatible_API_Call (
2150 l_api_version_number, p_api_version_number, l_api_name,
2151 G_PKG_NAME) then
2152 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2153 return;
2154 END IF;
2155
2156 -- Initialize the message table if requested.
2157
2158 if p_init_msg_tbl then
2159 FND_MSG_PUB.initialize;
2160 end if;
2161
2162 savepoint start_delete_key_column;
2163
2164 --
2165 -- error if unique key to be deleted does not exists
2166 --
2167 if NOT AK_KEY_PVT.UNIQUE_KEY_COLUMN_EXISTS (
2168 p_api_version_number => 1.0,
2169 p_return_status => l_return_status,
2170 p_unique_key_name => p_unique_key_name,
2171 p_attribute_application_id => p_attribute_application_id,
2172 p_attribute_code => p_attribute_code) then
2173 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2174 FND_MESSAGE.SET_NAME('AK','AK_UK_COLUMN_DOES_NOT_EXIST');
2175 FND_MSG_PUB.Add;
2176 end if;
2177 raise FND_API.G_EXC_ERROR;
2178 end if;
2179
2180 -- p_override is set to 'Y' during upload
2181 --
2182 if (p_override = 'N') then
2183 if (p_delete_cascade = 'N') then
2184 --
2185 -- If we are not deleting any referencing records, we cannot
2186 -- delete the unique key column if it is being referenced in any of
2187 -- following tables.
2188 --
2189 -- AK_FOREIGN_KEY_COLUMNS
2190 --
2191 open l_get_fkc_csr;
2192 fetch l_get_fkc_csr into l_foreign_key_name, l_attribute_appl_id,
2193 l_attribute_code;
2194 if l_get_fkc_csr%found then
2195 close l_get_fkc_csr;
2196 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2197 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_DEL_REF_UKC_FKC');
2198 FND_MSG_PUB.Add;
2199 end if;
2200 raise FND_API.G_EXC_ERROR;
2201 end if;
2202 close l_get_fkc_csr;
2203 else
2204 --
2205 -- Otherwise, delete all referencing rows in other tables
2206 --
2207 -- AK_FOREIGN_KEY_COLUMNS
2208 --
2209 open l_get_fkc_csr;
2210 loop
2211 fetch l_get_fkc_csr into l_foreign_key_name, l_attribute_appl_id,
2212 l_attribute_code;
2213 exit when l_get_fkc_csr%notfound;
2214 AK_KEY_PVT.DELETE_FOREIGN_KEY_COLUMN(
2215 p_validation_level => p_validation_level,
2216 p_api_version_number => 1.0,
2217 p_msg_count => l_msg_count,
2218 p_msg_data => l_msg_data,
2219 p_return_status => l_return_status,
2220 p_foreign_key_name => l_foreign_key_name,
2221 p_attribute_application_id => l_attribute_appl_id,
2222 p_attribute_code => l_attribute_code,
2223 p_delete_cascade => p_delete_cascade
2224 );
2225 if (l_return_status = FND_API.G_RET_STS_ERROR) or
2226 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
2227 close l_get_fkc_csr;
2228 raise FND_API.G_EXC_ERROR;
2229 end if;
2230 end loop;
2231 close l_get_fkc_csr;
2232 end if; -- /* if p_delete_cascade */
2233 end if; -- /* if p_override */
2234 --
2235 -- delete unique key column once we checked that there are no references
2236 -- to it, or all references have been deleted.
2237 --
2238 delete from ak_unique_key_columns
2239 where unique_key_name = p_unique_key_name
2240 and attribute_application_id = p_attribute_application_id
2241 and attribute_code = p_attribute_code;
2242
2243 if (sql%notfound) then
2244 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2245 FND_MESSAGE.SET_NAME('AK','AK_UK_COLUMN_DOES_NOT_EXIST');
2246 FND_MSG_PUB.Add;
2247 end if;
2248 raise FND_API.G_EXC_ERROR;
2249 end if;
2250
2251 --
2252 -- Load success message
2253 --
2254 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) then
2255 FND_MESSAGE.SET_NAME('AK','AK_UK_COLUMN_DELETED');
2256 FND_MESSAGE.SET_TOKEN('KEY',p_unique_key_name ||
2257 ' ' || to_char(p_attribute_application_id) ||
2258 ' ' || p_attribute_code);
2259 FND_MSG_PUB.Add;
2260 end if;
2261
2262 p_return_status := FND_API.G_RET_STS_SUCCESS;
2263
2264 FND_MSG_PUB.Count_And_Get (
2265 p_count => p_msg_count,
2266 p_data => p_msg_data);
2267
2268 EXCEPTION
2269 WHEN FND_API.G_EXC_ERROR THEN
2270 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2271 FND_MESSAGE.SET_NAME('AK','AK_UK_COLUMN_NOT_DELETED');
2272 FND_MESSAGE.SET_TOKEN('KEY',p_unique_key_name ||
2273 ' ' || to_char(p_attribute_application_id) ||
2274 ' ' || p_attribute_code);
2275 FND_MSG_PUB.Add;
2276 end if;
2277 p_return_status := FND_API.G_RET_STS_ERROR;
2278 rollback to start_delete_key_column;
2279 FND_MSG_PUB.Count_And_Get (
2280 p_count => p_msg_count,
2281 p_data => p_msg_data);
2282 WHEN OTHERS THEN
2283 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2284 rollback to start_delete_key_column;
2285 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2286 SUBSTR (SQLERRM, 1, 240) );
2287 FND_MSG_PUB.Add;
2291 end DELETE_UNIQUE_KEY_COLUMN;
2288 FND_MSG_PUB.Count_And_Get (
2289 p_count => p_msg_count,
2290 p_data => p_msg_data);
2292
2293 --=======================================================
2294 -- Function FOREIGN_KEY_EXISTS
2295 --
2296 -- Usage Private API for checking for the existence of
2297 -- a foreign key with the given key values. This
2298 -- API should only be called by other APIs that are
2299 -- owned by the Core Modules Team (AK).
2300 --
2301 -- Desc This API check to see if a foreign key record
2302 -- exists with the given key values.
2303 --
2304 -- Results The API returns the standard p_return_status parameter
2305 -- indicating one of the standard return statuses :
2306 -- * Unexpected error
2307 -- * Error
2308 -- * Success
2309 -- This function will return TRUE if such a foreign key
2310 -- exists, or FALSE otherwise.
2311 -- Parameters Foreign Key key columns
2312 --
2313 -- Version Initial version number = 1.0
2314 -- History Current version number = 1.0
2315 --=======================================================
2316 function FOREIGN_KEY_EXISTS (
2317 p_api_version_number IN NUMBER,
2318 p_return_status OUT NOCOPY VARCHAR2,
2319 p_foreign_key_name IN VARCHAR2
2320 ) return BOOLEAN is
2321 cursor l_check_csr is
2322 select 1
2323 from AK_FOREIGN_KEYS
2324 where FOREIGN_KEY_NAME = p_foreign_key_name;
2325 l_api_version_number CONSTANT number := 1.0;
2326 l_api_name CONSTANT varchar2(30) := 'Foreign_Key_Exists';
2327 l_dummy number;
2328 begin
2329 IF NOT FND_API.Compatible_API_Call (
2330 l_api_version_number, p_api_version_number, l_api_name,
2331 G_PKG_NAME) then
2332 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2333 return FALSE;
2334 END IF;
2335
2336 open l_check_csr;
2337 fetch l_check_csr into l_dummy;
2338 if (l_check_csr%notfound) then
2339 close l_check_csr;
2340 p_return_status := FND_API.G_RET_STS_SUCCESS;
2341 return FALSE;
2342 else
2343 close l_check_csr;
2344 p_return_status := FND_API.G_RET_STS_SUCCESS;
2345 return TRUE;
2346 end if;
2347
2348 EXCEPTION
2349 WHEN FND_API.G_EXC_ERROR THEN
2350 p_return_status := FND_API.G_RET_STS_ERROR;
2351 return FALSE;
2352 WHEN OTHERS THEN
2353 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2354 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2355 SUBSTR (SQLERRM, 1, 240) );
2356 FND_MSG_PUB.Add;
2357 return FALSE;
2358
2359 end FOREIGN_KEY_EXISTS;
2360
2361 --=======================================================
2362 -- Function FOREIGN_KEY_COLUMN_EXISTS
2363 --
2364 -- Usage Private API for checking for the existence of
2365 -- a foreign key column record with the given key values.
2366 -- This API should only be called by other APIs that are
2367 -- owned by the Core Modules Team (AK).
2368 --
2369 -- Desc This API check to see if a foreign key column record
2370 -- exists with the given key values.
2371 --
2372 -- Results The API returns the standard p_return_status parameter
2373 -- indicating one of the standard return statuses :
2374 -- * Unexpected error
2375 -- * Error
2376 -- * Success
2377 -- This function will return TRUE if such a foreign key
2378 -- exists, or FALSE otherwise.
2379 -- Parameters Foreign Key Column key columns
2380 --
2381 -- Version Initial version number = 1.0
2382 -- History Current version number = 1.0
2383 --=======================================================
2384 function FOREIGN_KEY_COLUMN_EXISTS (
2385 p_api_version_number IN NUMBER,
2386 p_return_status OUT NOCOPY VARCHAR2,
2387 p_foreign_key_name IN VARCHAR2,
2388 p_attribute_application_id IN NUMBER,
2389 p_attribute_code IN VARCHAR2
2390 ) return BOOLEAN is
2391 cursor l_check_csr is
2392 select 1
2393 from AK_FOREIGN_KEY_COLUMNS
2394 where FOREIGN_KEY_NAME = p_foreign_key_name
2395 and ATTRIBUTE_APPLICATION_ID = p_attribute_application_id
2396 and ATTRIBUTE_CODE = p_attribute_code;
2397 l_api_version_number CONSTANT number := 1.0;
2398 l_api_name CONSTANT varchar2(30) := 'Foreign_Key_Column_Exists'
2399 ;
2400 l_dummy number;
2401 begin
2402 open l_check_csr;
2403 fetch l_check_csr into l_dummy;
2404 if (l_check_csr%notfound) then
2405 close l_check_csr;
2406 p_return_status := FND_API.G_RET_STS_SUCCESS;
2407 return FALSE;
2408 else
2409 close l_check_csr;
2410 p_return_status := FND_API.G_RET_STS_SUCCESS;
2411 return TRUE;
2412 end if;
2413
2414 EXCEPTION
2415 WHEN FND_API.G_EXC_ERROR THEN
2416 p_return_status := FND_API.G_RET_STS_ERROR;
2417 return FALSE;
2418 WHEN OTHERS THEN
2419 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2420 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2421 SUBSTR (SQLERRM, 1, 240) );
2422 FND_MSG_PUB.Add;
2423 return FALSE;
2424
2425 end FOREIGN_KEY_COLUMN_EXISTS;
2426
2427 --=======================================================
2428 -- Function UNIQUE_KEY_EXISTS
2432 -- API should only be called by other APIs that are
2429 --
2430 -- Usage Private API for checking for the existence of
2431 -- a unique key with the given key values. This
2433 -- owned by the Core Modules Team (AK).
2434 --
2435 -- Desc This API check to see if a unique key record
2436 -- exists with the given key values.
2437 --
2438 -- Results The API returns the standard p_return_status parameter
2439 -- indicating one of the standard return statuses :
2440 -- * Unexpected error
2441 -- * Error
2442 -- * Success
2443 -- This function will return TRUE if such a foreign key
2444 -- exists, or FALSE otherwise.
2445 -- Parameters Unique Key key columns
2446 --
2447 -- Version Initial version number = 1.0
2448 -- History Current version number = 1.0
2449 --=======================================================
2450 function UNIQUE_KEY_EXISTS (
2451 p_api_version_number IN NUMBER,
2452 p_return_status OUT NOCOPY VARCHAR2,
2453 p_unique_key_name IN VARCHAR2
2454 ) return BOOLEAN is
2455 cursor l_check_csr is
2456 select 1
2457 from AK_UNIQUE_KEYS
2458 where UNIQUE_KEY_NAME = p_unique_key_name;
2459 l_api_version_number CONSTANT number := 1.0;
2460 l_api_name CONSTANT varchar2(30) := 'Unique_Key_Exists';
2461 l_dummy number;
2462 begin
2463 open l_check_csr;
2464 fetch l_check_csr into l_dummy;
2465 if (l_check_csr%notfound) then
2466 close l_check_csr;
2467 p_return_status := FND_API.G_RET_STS_SUCCESS;
2468 return FALSE;
2469 else
2470 close l_check_csr;
2471 p_return_status := FND_API.G_RET_STS_SUCCESS;
2472 return TRUE;
2473 end if;
2474
2475 EXCEPTION
2476 WHEN FND_API.G_EXC_ERROR THEN
2477 p_return_status := FND_API.G_RET_STS_ERROR;
2478 return FALSE;
2479 WHEN OTHERS THEN
2480 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2481 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2482 SUBSTR (SQLERRM, 1, 240) );
2483 FND_MSG_PUB.Add;
2484 return FALSE;
2485
2486 end UNIQUE_KEY_EXISTS;
2487
2488 --=======================================================
2489 -- Function UNIQUE_KEY_COLUMN_EXISTS
2490 --
2491 -- Usage Private API for checking for the existence of
2492 -- a unique key column record with the given key values.
2493 -- This API should only be called by other APIs that are
2494 -- owned by the Core Modules Team (AK).
2495 --
2496 -- Desc This API check to see if a unique key column record
2497 -- exists with the given key values.
2498 --
2499 -- Results The API returns the standard p_return_status parameter
2500 -- indicating one of the standard return statuses :
2501 -- * Unexpected error
2502 -- * Error
2503 -- * Success
2504 -- This function will return TRUE if such a unique key
2505 -- exists, or FALSE otherwise.
2506 -- Parameters Unique Key Column key columns
2507 --
2508 -- Version Initial version number = 1.0
2509 -- History Current version number = 1.0
2510 --=======================================================
2511 function UNIQUE_KEY_COLUMN_EXISTS (
2512 p_api_version_number IN NUMBER,
2513 p_return_status OUT NOCOPY VARCHAR2,
2514 p_unique_key_name IN VARCHAR2,
2515 p_attribute_application_id IN NUMBER,
2516 p_attribute_code IN VARCHAR2
2517 ) return BOOLEAN is
2518 cursor l_check_csr is
2519 select 1
2520 from AK_UNIQUE_KEY_COLUMNS
2521 where UNIQUE_KEY_NAME = p_unique_key_name
2522 and ATTRIBUTE_APPLICATION_ID = p_attribute_application_id
2523 and ATTRIBUTE_CODE = p_attribute_code;
2524 l_api_version_number CONSTANT number := 1.0;
2525 l_api_name CONSTANT varchar2(30) := 'Unique_Key_Column_Exists';
2526 l_dummy number;
2527 begin
2528 open l_check_csr;
2529 fetch l_check_csr into l_dummy;
2530 if (l_check_csr%notfound) then
2531 close l_check_csr;
2532 p_return_status := FND_API.G_RET_STS_SUCCESS;
2533 return FALSE;
2534 else
2535 close l_check_csr;
2536 p_return_status := FND_API.G_RET_STS_SUCCESS;
2537 return TRUE;
2538 end if;
2539
2540 EXCEPTION
2541 WHEN FND_API.G_EXC_ERROR THEN
2542 p_return_status := FND_API.G_RET_STS_ERROR;
2543 return FALSE;
2544 WHEN OTHERS THEN
2545 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2546 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2547 SUBSTR (SQLERRM, 1, 240) );
2548 FND_MSG_PUB.Add;
2549 return FALSE;
2550
2551 end UNIQUE_KEY_COLUMN_EXISTS;
2552
2553 --=======================================================
2554 -- Procedure UPDATE_FOREIGN_KEY
2555 --
2556 -- Usage Private API for updating a foreign key.
2557 -- This API should only be called by other APIs
2558 -- that are owned by the Core Modules Team (AK).
2559 --
2560 -- Desc This API updates a foreign key using the given info
2561 --
2562 -- Results The API returns the standard p_return_status parameter
2563 -- indicating one of the standard return statuses :
2564 -- * Unexpected error
2565 -- * Error
2566 -- * Success
2570 -- record using this timestamp. Only the upload API
2567 -- Parameters Foreign Key columns
2568 -- p_loader_timestamp : IN optional
2569 -- If a timestamp is passed, the API will update the
2571 -- should call with this parameter loaded.
2572 --
2573 -- Version Initial version number = 1.0
2574 -- History Current version number = 1.0
2575 --=======================================================
2576 procedure UPDATE_FOREIGN_KEY (
2577 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2578 p_api_version_number IN NUMBER,
2579 p_init_msg_tbl IN BOOLEAN := FALSE,
2580 p_msg_count OUT NOCOPY NUMBER,
2581 p_msg_data OUT NOCOPY VARCHAR2,
2582 p_return_status OUT NOCOPY VARCHAR2,
2583 p_foreign_key_name IN VARCHAR2,
2584 p_database_object_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
2585 p_unique_key_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
2586 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
2587 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
2588 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2589 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2590 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2591 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2592 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2593 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2594 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2595 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2596 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2597 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2598 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2599 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2600 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2601 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2602 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2603 p_from_to_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
2604 p_from_to_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
2605 p_to_from_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
2606 p_to_from_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
2607 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
2608 p_creation_date IN DATE := FND_API.G_MISS_DATE,
2609 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
2610 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
2611 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
2612 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
2613 p_pass IN NUMBER,
2614 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
2615 ) is
2616 cursor l_get_row_csr is
2617 select *
2618 from AK_FOREIGN_KEYS
2619 where FOREIGN_KEY_NAME = p_foreign_key_name
2620 for update of DATABASE_OBJECT_NAME;
2621 cursor l_get_tl_row_csr (lang_parm varchar2) is
2622 select *
2623 from AK_FOREIGN_KEYS_TL
2624 where FOREIGN_KEY_NAME = p_foreign_key_name
2625 and LANGUAGE = lang_parm
2626 for update of FROM_TO_NAME;
2627 l_api_version_number CONSTANT number := 1.0;
2628 l_api_name CONSTANT varchar2(30):= 'Update_Foreign_Key';
2629 l_created_by number;
2630 l_creation_date date;
2631 l_foreign_key_rec ak_foreign_keys%ROWTYPE;
2632 l_foreign_key_tl_rec ak_foreign_keys_tl%ROWTYPE;
2633 l_error boolean;
2634 l_lang varchar2(30);
2635 l_last_update_date date;
2636 l_last_update_login number;
2637 l_last_updated_by number;
2638 l_return_status varchar2(1);
2639 l_file_version number;
2640 begin
2641
2642 IF NOT FND_API.Compatible_API_Call (
2643 l_api_version_number, p_api_version_number, l_api_name,
2644 G_PKG_NAME) then
2645 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2646 return;
2647 END IF;
2648
2649 -- Initialize the message table if requested.
2650
2651 if p_init_msg_tbl then
2652 FND_MSG_PUB.initialize;
2653 end if;
2654
2655 savepoint start_update_foreign_key;
2656
2657 select userenv('LANG') into l_lang
2658 from dual;
2659
2660 --** retrieve ak_foreign_keys row if it exists **
2661 open l_get_row_csr;
2662 fetch l_get_row_csr into l_foreign_key_rec;
2663 if (l_get_row_csr%notfound) then
2664 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2665 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_DOES_NOT_EXIST');
2666 FND_MSG_PUB.Add;
2667 end if;
2668 --dbms_output.put_line('Error - Row does not exist');
2669 close l_get_row_csr;
2670 raise FND_API.G_EXC_ERROR;
2671 end if;
2672 close l_get_row_csr;
2673
2674 --** retrieve ak_foreign_keys_tl row if it exists **
2675 open l_get_tl_row_csr(l_lang);
2676 fetch l_get_tl_row_csr into l_foreign_key_tl_rec;
2677 if (l_get_tl_row_csr%notfound) then
2678 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2679 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_DOES_NOT_EXIST');
2680 FND_MSG_PUB.Add;
2681 end if;
2682 --dbms_output.put_line('Error - TL Row does not exist');
2683 close l_get_tl_row_csr;
2684 raise FND_API.G_EXC_ERROR;
2685 end if;
2689 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
2686 close l_get_tl_row_csr;
2687
2688 --** validate table columns passed in **
2690 if not VALIDATE_FOREIGN_KEY (
2691 p_validation_level => p_validation_level,
2692 p_api_version_number => 1.0,
2693 p_return_status => l_return_status,
2694 p_foreign_key_name => p_foreign_key_name,
2695 p_database_object_name => p_database_object_name,
2696 p_unique_key_name => p_unique_key_name,
2697 p_application_id => p_application_id,
2698 p_from_to_name => p_from_to_name,
2699 p_from_to_description => p_from_to_description,
2700 p_to_from_name => p_to_from_name,
2701 p_to_from_description => p_to_from_description,
2702 p_caller => AK_ON_OBJECTS_PVT.G_UPDATE,
2703 p_pass => p_pass
2704 ) then
2705 --dbms_output.put_line(l_api_name || ' validation failed');
2706 raise FND_API.G_EXC_ERROR;
2707 end if;
2708 end if;
2709
2710 --** Load record to be updated to the database **
2711 --** - first load nullable columns **
2712
2713 if (p_attribute_category <> FND_API.G_MISS_CHAR) or
2714 (p_attribute_category is null) then
2715 l_foreign_key_rec.attribute_category := p_attribute_category;
2716 end if;
2717 if (p_attribute1 <> FND_API.G_MISS_CHAR) or
2718 (p_attribute1 is null) then
2719 l_foreign_key_rec.attribute1 := p_attribute1;
2720 end if;
2721 if (p_attribute2 <> FND_API.G_MISS_CHAR) or
2722 (p_attribute2 is null) then
2723 l_foreign_key_rec.attribute2 := p_attribute2;
2724 end if;
2725 if (p_attribute3 <> FND_API.G_MISS_CHAR) or
2726 (p_attribute3 is null) then
2727 l_foreign_key_rec.attribute3 := p_attribute3;
2728 end if;
2729 if (p_attribute4 <> FND_API.G_MISS_CHAR) or
2730 (p_attribute4 is null) then
2731 l_foreign_key_rec.attribute4 := p_attribute4;
2732 end if;
2733 if (p_attribute5 <> FND_API.G_MISS_CHAR) or
2734 (p_attribute5 is null) then
2735 l_foreign_key_rec.attribute5 := p_attribute5;
2736 end if;
2737 if (p_attribute6 <> FND_API.G_MISS_CHAR) or
2738 (p_attribute6 is null) then
2739 l_foreign_key_rec.attribute6 := p_attribute6;
2740 end if;
2741 if (p_attribute7 <> FND_API.G_MISS_CHAR) or
2742 (p_attribute7 is null) then
2743 l_foreign_key_rec.attribute7 := p_attribute7;
2744 end if;
2745 if (p_attribute8 <> FND_API.G_MISS_CHAR) or
2746 (p_attribute8 is null) then
2747 l_foreign_key_rec.attribute8 := p_attribute8;
2748 end if;
2749 if (p_attribute9 <> FND_API.G_MISS_CHAR) or
2750 (p_attribute9 is null) then
2751 l_foreign_key_rec.attribute9 := p_attribute9;
2752 end if;
2753 if (p_attribute10 <> FND_API.G_MISS_CHAR) or
2754 (p_attribute10 is null) then
2755 l_foreign_key_rec.attribute10 := p_attribute10;
2756 end if;
2757 if (p_attribute11 <> FND_API.G_MISS_CHAR) or
2758 (p_attribute11 is null) then
2759 l_foreign_key_rec.attribute11 := p_attribute11;
2760 end if;
2761 if (p_attribute12 <> FND_API.G_MISS_CHAR) or
2762 (p_attribute12 is null) then
2763 l_foreign_key_rec.attribute12 := p_attribute12;
2764 end if;
2765 if (p_attribute13 <> FND_API.G_MISS_CHAR) or
2766 (p_attribute13 is null) then
2767 l_foreign_key_rec.attribute13 := p_attribute13;
2768 end if;
2769 if (p_attribute14 <> FND_API.G_MISS_CHAR) or
2770 (p_attribute14 is null) then
2771 l_foreign_key_rec.attribute14 := p_attribute14;
2772 end if;
2773 if (p_attribute15 <> FND_API.G_MISS_CHAR) or
2774 (p_attribute15 is null) then
2775 l_foreign_key_rec.attribute15 := p_attribute15;
2776 end if;
2777 if (p_from_to_name <> FND_API.G_MISS_CHAR) or
2778 (p_from_to_name is null) then
2779 l_foreign_key_tl_rec.from_to_name := p_from_to_name;
2780 end if;
2781
2782 if (p_from_to_description <> FND_API.G_MISS_CHAR) or
2783 (p_from_to_description is null) then
2784 l_foreign_key_tl_rec.from_to_description := p_from_to_description;
2785 end if;
2786
2787 if (p_to_from_name <> FND_API.G_MISS_CHAR) or
2788 (p_to_from_name is null) then
2789 l_foreign_key_tl_rec.to_from_name := p_to_from_name;
2790 end if;
2791
2792 if (p_to_from_description <> FND_API.G_MISS_CHAR) or
2793 (p_to_from_description is null) then
2794 l_foreign_key_tl_rec.to_from_description := p_to_from_description;
2795 end if;
2796
2797 --** - load non-null columns **
2798 if (p_database_object_name <> FND_API.G_MISS_CHAR) then
2799 l_foreign_key_rec.database_object_name := p_database_object_name;
2800 end if;
2801 if (p_unique_key_name <> FND_API.G_MISS_CHAR) then
2802 l_foreign_key_rec.unique_key_name := p_unique_key_name;
2803 end if;
2804 if (p_application_id <> FND_API.G_MISS_NUM) then
2805 l_foreign_key_rec.application_id := p_application_id;
2806 end if;
2807
2808 if (p_created_by <> FND_API.G_MISS_NUM) then
2809 l_created_by := p_created_by;
2810 end if;
2811 if (p_creation_date <> FND_API.G_MISS_DATE) then
2812 l_creation_date := p_creation_date;
2813 end if;
2814 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
2815 l_last_updated_by := p_last_updated_by;
2816 end if;
2817 if (p_last_update_date <> FND_API.G_MISS_DATE) then
2818 l_last_update_date := p_last_update_date;
2819 end if;
2820 if (p_last_update_login <> FND_API.G_MISS_NUM) then
2821 l_last_update_login := p_last_update_login;
2822 end if;
2823
2824 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
2825 p_loader_timestamp => p_loader_timestamp,
2826 p_created_by => l_created_by,
2827 p_creation_date => l_creation_date,
2831 p_db_last_update_date => l_foreign_key_rec.last_update_date,
2828 p_last_updated_by => l_last_updated_by,
2829 p_db_last_updated_by => l_foreign_key_rec.last_updated_by,
2830 p_last_update_date => l_last_update_date,
2832 p_last_update_login => l_last_update_login,
2833 p_create_or_update => 'UPDATE') then
2834
2835 update AK_FOREIGN_KEYS set
2836 DATABASE_OBJECT_NAME = l_foreign_key_rec.database_object_name,
2837 UNIQUE_KEY_NAME = l_foreign_key_rec.unique_key_name,
2838 APPLICATION_ID = l_foreign_key_rec.application_id,
2839 ATTRIBUTE_CATEGORY = l_foreign_key_rec.attribute_category,
2840 ATTRIBUTE1 = l_foreign_key_rec.attribute1,
2841 ATTRIBUTE2 = l_foreign_key_rec.attribute2,
2842 ATTRIBUTE3 = l_foreign_key_rec.attribute3,
2843 ATTRIBUTE4 = l_foreign_key_rec.attribute4,
2844 ATTRIBUTE5 = l_foreign_key_rec.attribute5,
2845 ATTRIBUTE6 = l_foreign_key_rec.attribute6,
2846 ATTRIBUTE7 = l_foreign_key_rec.attribute7,
2847 ATTRIBUTE8 = l_foreign_key_rec.attribute8,
2848 ATTRIBUTE9 = l_foreign_key_rec.attribute9,
2849 ATTRIBUTE10 = l_foreign_key_rec.attribute10,
2850 ATTRIBUTE11 = l_foreign_key_rec.attribute11,
2851 ATTRIBUTE12 = l_foreign_key_rec.attribute12,
2852 ATTRIBUTE13 = l_foreign_key_rec.attribute13,
2853 ATTRIBUTE14 = l_foreign_key_rec.attribute14,
2854 ATTRIBUTE15 = l_foreign_key_rec.attribute15,
2855 LAST_UPDATE_DATE = l_last_update_date,
2856 LAST_UPDATED_BY = l_last_updated_by,
2857 LAST_UPDATE_LOGIN = l_last_update_login
2858 where foreign_key_name = p_foreign_key_name;
2859 if (sql%notfound) then
2860 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2861 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_UPDATE_FAILED');
2862 FND_MSG_PUB.Add;
2863 end if;
2864 -- dbms_output.put_line('Row does not exist during update');
2865 raise FND_API.G_EXC_ERROR;
2866 end if;
2867
2868 update AK_FOREIGN_KEYS_TL set
2869 FROM_TO_NAME = l_foreign_key_tl_rec.from_to_name,
2870 FROM_TO_DESCRIPTION = l_foreign_key_tl_rec.from_to_description,
2871 TO_FROM_NAME = l_foreign_key_tl_rec.to_from_name,
2872 TO_FROM_DESCRIPTION = l_foreign_key_tl_rec.to_from_description,
2873 LAST_UPDATE_DATE = l_last_update_date,
2874 LAST_UPDATED_BY = l_last_updated_by,
2875 LAST_UPDATE_LOGIN = l_last_update_login,
2876 SOURCE_LANG = l_lang
2877 where foreign_key_name = p_foreign_key_name
2878 and l_lang in (LANGUAGE, SOURCE_LANG);
2879 if (sql%notfound) then
2880 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2881 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_UPDATE_FAILED');
2882 FND_MSG_PUB.Add;
2883 end if;
2884 --dbms_output.put_line('TL Row does not exist during update');
2885 raise FND_API.G_EXC_ERROR;
2886 end if;
2887
2888 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
2889 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_UPDATED');
2890 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name);
2891 FND_MSG_PUB.Add;
2892 end if;
2893
2894 end if;
2895 p_return_status := FND_API.G_RET_STS_SUCCESS;
2896
2897 FND_MSG_PUB.Count_And_Get (
2898 p_count => p_msg_count,
2899 p_data => p_msg_data);
2900
2901 EXCEPTION
2902 WHEN VALUE_ERROR THEN
2903 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2904 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_VALUE_ERROR');
2905 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name);
2906 FND_MSG_PUB.Add;
2907 end if;
2908 rollback to start_update_foreign_key;
2909 p_return_status := FND_API.G_RET_STS_ERROR;
2910 FND_MSG_PUB.Count_And_Get (
2911 p_count => p_msg_count,
2912 p_data => p_msg_data);
2913 WHEN FND_API.G_EXC_ERROR THEN
2914 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2915 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_NOT_UPDATED');
2916 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name);
2917 FND_MSG_PUB.Add;
2918 end if;
2919 p_return_status := FND_API.G_RET_STS_ERROR;
2920 rollback to start_update_foreign_key;
2921 FND_MSG_PUB.Count_And_Get (
2922 p_count => p_msg_count,
2923 p_data => p_msg_data);
2924 WHEN OTHERS THEN
2925 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2926 rollback to start_update_foreign_key;
2927 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2928 SUBSTR (SQLERRM, 1, 240) );
2929 FND_MSG_PUB.Add;
2930 FND_MSG_PUB.Count_And_Get (
2931 p_count => p_msg_count,
2932 p_data => p_msg_data);
2933 end UPDATE_FOREIGN_KEY;
2934
2935 --=======================================================
2936 -- Procedure UPDATE_FOREIGN_KEY_COLUMN
2937 --
2938 -- Usage Private API for updating a foreign key column.
2939 -- This API should only be called by other APIs
2940 -- that are owned by the Core Modules Team (AK).
2941 --
2942 -- Desc This API updates a foreign key column using the given info
2943 --
2944 -- Results The API returns the standard p_return_status parameter
2945 -- indicating one of the standard return statuses :
2946 -- * Unexpected error
2947 -- * Error
2948 -- * Success
2949 -- Parameters Foreign Key Column columns
2950 -- p_loader_timestamp : IN optional
2951 -- If a timestamp is passed, the API will update the
2952 -- record using this timestamp. Only the upload API
2953 -- should call with this parameter loaded.
2954 --
2958 procedure UPDATE_FOREIGN_KEY_COLUMN (
2955 -- Version Initial version number = 1.0
2956 -- History Current version number = 1.0
2957 --=======================================================
2959 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2960 p_api_version_number IN NUMBER,
2961 p_init_msg_tbl IN BOOLEAN := FALSE,
2962 p_msg_count OUT NOCOPY NUMBER,
2963 p_msg_data OUT NOCOPY VARCHAR2,
2964 p_return_status OUT NOCOPY VARCHAR2,
2965 p_foreign_key_name IN VARCHAR2,
2966 p_attribute_application_id IN NUMBER,
2967 p_attribute_code IN VARCHAR2,
2968 p_foreign_key_sequence IN NUMBER := FND_API.G_MISS_NUM,
2969 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
2970 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2971 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2972 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2973 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2974 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2975 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2976 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2977 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2978 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2979 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2980 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2981 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2982 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2983 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2984 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2985 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
2986 p_creation_date IN DATE := FND_API.G_MISS_DATE,
2987 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
2988 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
2989 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
2990 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
2991 p_pass IN NUMBER,
2992 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
2993 ) is
2994 cursor l_get_row_csr is
2995 select *
2996 from AK_FOREIGN_KEY_COLUMNS
2997 where FOREIGN_KEY_NAME = p_foreign_key_name
2998 and ATTRIBUTE_APPLICATION_ID = p_attribute_application_id
2999 and ATTRIBUTE_CODE = p_attribute_code
3000 for update of FOREIGN_KEY_SEQUENCE;
3001 l_api_version_number CONSTANT number := 1.0;
3002 l_api_name CONSTANT varchar2(30):= 'Update_Foreign_Key_Column';
3003 l_created_by number;
3004 l_creation_date date;
3005 l_key_column_rec ak_foreign_key_columns%ROWTYPE;
3006 l_error boolean;
3007 l_last_update_date date;
3008 l_last_update_login number;
3009 l_last_updated_by number;
3010 l_return_status varchar2(1);
3011 l_file_version number;
3012 begin
3013 IF NOT FND_API.Compatible_API_Call (
3014 l_api_version_number, p_api_version_number, l_api_name,
3015 G_PKG_NAME) then
3016 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3017 return;
3018 END IF;
3019
3020 -- Initialize the message table if requested.
3021
3022 if p_init_msg_tbl then
3023 FND_MSG_PUB.initialize;
3024 end if;
3025
3026 savepoint start_update_key_column;
3027
3028 --** retrieve ak_foreign_key_columns row if it exists **
3029 open l_get_row_csr;
3030 fetch l_get_row_csr into l_key_column_rec;
3031 if (l_get_row_csr%notfound) then
3032 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3033 FND_MESSAGE.SET_NAME('AK','AK_FK_COLUMN_DOES_NOT_EXIST');
3034 FND_MSG_PUB.Add;
3035 end if;
3036 close l_get_row_csr;
3037 raise FND_API.G_EXC_ERROR;
3038 end if;
3039 close l_get_row_csr;
3040
3041 --** validate table columns passed in **
3042 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
3043 if not VALIDATE_FOREIGN_KEY_COLUMN (
3044 p_validation_level => p_validation_level,
3045 p_api_version_number => 1.0,
3046 p_return_status => l_return_status,
3047 p_foreign_key_name => p_foreign_key_name,
3048 p_attribute_application_id => p_attribute_application_id,
3049 p_attribute_code => p_attribute_code,
3050 p_foreign_key_sequence => p_foreign_key_sequence,
3051 p_caller => AK_ON_OBJECTS_PVT.G_UPDATE,
3052 p_pass => p_pass
3053 ) then
3054 raise FND_API.G_EXC_ERROR;
3055 end if;
3056 end if;
3057
3058 --** Load record to be updated to the database **
3059 --** - first load nullable columns **
3060 -- (none)
3061
3062 --** - load non-null columns **
3063
3064 if (p_foreign_key_sequence <> FND_API.G_MISS_NUM) then
3065 l_key_column_rec.foreign_key_sequence := p_foreign_key_sequence;
3066 end if;
3067 if (p_attribute_category <> FND_API.G_MISS_CHAR) or
3068 (p_attribute_category is null) then
3069 l_key_column_rec.attribute_category := p_attribute_category;
3070 end if;
3071 if (p_attribute1 <> FND_API.G_MISS_CHAR) or
3072 (p_attribute1 is null) then
3073 l_key_column_rec.attribute1 := p_attribute1;
3074 end if;
3075 if (p_attribute2 <> FND_API.G_MISS_CHAR) or
3076 (p_attribute2 is null) then
3077 l_key_column_rec.attribute2 := p_attribute2;
3078 end if;
3082 end if;
3079 if (p_attribute3 <> FND_API.G_MISS_CHAR) or
3080 (p_attribute3 is null) then
3081 l_key_column_rec.attribute3 := p_attribute3;
3083 if (p_attribute4 <> FND_API.G_MISS_CHAR) or
3084 (p_attribute4 is null) then
3085 l_key_column_rec.attribute4 := p_attribute4;
3086 end if;
3087 if (p_attribute5 <> FND_API.G_MISS_CHAR) or
3088 (p_attribute5 is null) then
3089 l_key_column_rec.attribute5 := p_attribute5;
3090 end if;
3091 if (p_attribute6 <> FND_API.G_MISS_CHAR) or
3092 (p_attribute6 is null) then
3093 l_key_column_rec.attribute6 := p_attribute6;
3094 end if;
3095 if (p_attribute7 <> FND_API.G_MISS_CHAR) or
3096 (p_attribute7 is null) then
3097 l_key_column_rec.attribute7 := p_attribute7;
3098 end if;
3099 if (p_attribute8 <> FND_API.G_MISS_CHAR) or
3100 (p_attribute8 is null) then
3101 l_key_column_rec.attribute8 := p_attribute8;
3102 end if;
3103 if (p_attribute9 <> FND_API.G_MISS_CHAR) or
3104 (p_attribute9 is null) then
3105 l_key_column_rec.attribute9 := p_attribute9;
3106 end if;
3107 if (p_attribute10 <> FND_API.G_MISS_CHAR) or
3108 (p_attribute10 is null) then
3109 l_key_column_rec.attribute10 := p_attribute10;
3110 end if;
3111 if (p_attribute11 <> FND_API.G_MISS_CHAR) or
3112 (p_attribute11 is null) then
3113 l_key_column_rec.attribute11 := p_attribute11;
3114 end if;
3115 if (p_attribute12 <> FND_API.G_MISS_CHAR) or
3116 (p_attribute12 is null) then
3117 l_key_column_rec.attribute12 := p_attribute12;
3118 end if;
3119 if (p_attribute13 <> FND_API.G_MISS_CHAR) or
3120 (p_attribute13 is null) then
3121 l_key_column_rec.attribute13 := p_attribute13;
3122 end if;
3123 if (p_attribute14 <> FND_API.G_MISS_CHAR) or
3124 (p_attribute14 is null) then
3125 l_key_column_rec.attribute14 := p_attribute14;
3126 end if;
3127 if (p_attribute15 <> FND_API.G_MISS_CHAR) or
3128 (p_attribute15 is null) then
3129 l_key_column_rec.attribute15 := p_attribute15;
3130 end if;
3131
3132 if (p_created_by <> FND_API.G_MISS_NUM) then
3133 l_created_by := p_created_by;
3134 end if;
3135 if (p_creation_date <> FND_API.G_MISS_DATE) then
3136 l_creation_date := p_creation_date;
3137 end if;
3138 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
3139 l_last_updated_by := p_last_updated_by;
3140 end if;
3141 if (p_last_update_date <> FND_API.G_MISS_DATE) then
3142 l_last_update_date := p_last_update_date;
3143 end if;
3144 if (p_last_update_login <> FND_API.G_MISS_NUM) then
3145 l_last_update_login := p_last_update_login;
3146 end if;
3147
3148 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
3149 p_loader_timestamp => p_loader_timestamp,
3150 p_created_by => l_created_by,
3151 p_creation_date => l_creation_date,
3152 p_last_updated_by => l_last_updated_by,
3153 p_db_last_updated_by => l_key_column_rec.last_updated_by,
3154 p_last_update_date => l_last_update_date,
3155 p_db_last_update_date => l_key_column_rec.last_update_date,
3156 p_last_update_login => l_last_update_login,
3157 p_create_or_update => 'UPDATE') then
3158
3159 update AK_FOREIGN_KEY_COLUMNS set
3160 FOREIGN_KEY_SEQUENCE = l_key_column_rec.foreign_key_sequence,
3161 ATTRIBUTE_CATEGORY = l_key_column_rec.attribute_category,
3162 ATTRIBUTE1 = l_key_column_rec.attribute1,
3163 ATTRIBUTE2 = l_key_column_rec.attribute2,
3164 ATTRIBUTE3 = l_key_column_rec.attribute3,
3165 ATTRIBUTE4 = l_key_column_rec.attribute4,
3166 ATTRIBUTE5 = l_key_column_rec.attribute5,
3167 ATTRIBUTE6 = l_key_column_rec.attribute6,
3168 ATTRIBUTE7 = l_key_column_rec.attribute7,
3169 ATTRIBUTE8 = l_key_column_rec.attribute8,
3170 ATTRIBUTE9 = l_key_column_rec.attribute9,
3171 ATTRIBUTE10 = l_key_column_rec.attribute10,
3172 ATTRIBUTE11 = l_key_column_rec.attribute11,
3173 ATTRIBUTE12 = l_key_column_rec.attribute12,
3174 ATTRIBUTE13 = l_key_column_rec.attribute13,
3175 ATTRIBUTE14 = l_key_column_rec.attribute14,
3176 ATTRIBUTE15 = l_key_column_rec.attribute15,
3177 LAST_UPDATE_DATE = l_last_update_date,
3178 LAST_UPDATED_BY = l_last_updated_by,
3179 LAST_UPDATE_LOGIN = l_last_update_login
3180 where foreign_key_name = p_foreign_key_name
3181 and attribute_application_id = p_attribute_application_id
3182 and attribute_code = p_attribute_code;
3183 if (sql%notfound) then
3184 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3185 FND_MESSAGE.SET_NAME('AK','AK_FK_COLUMN_UPDATE_FAILED');
3186 FND_MSG_PUB.Add;
3187 end if;
3188 --dbms_output.put_line('Row does not exist during update');
3189 raise FND_API.G_EXC_ERROR;
3190 end if;
3191
3192 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
3193 FND_MESSAGE.SET_NAME('AK','AK_FK_COLUMN_UPDATED');
3194 FND_MESSAGE.SET_TOKEN('OBJECT','AK_FOREIGN_KEY_COLUMN',TRUE);
3195 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name ||
3196 ' ' || to_char(p_attribute_application_id) ||
3197 ' ' || p_attribute_code);
3198 FND_MSG_PUB.Add;
3199 end if;
3200
3201 end if;
3202 p_return_status := FND_API.G_RET_STS_SUCCESS;
3203
3204 FND_MSG_PUB.Count_And_Get (
3205 p_count => p_msg_count,
3206 p_data => p_msg_data);
3207
3208 EXCEPTION
3209 WHEN VALUE_ERROR THEN
3210 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3211 FND_MESSAGE.SET_NAME('AK','AK_FK_COLUMN_VALUE_ERROR');
3212 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name ||
3216 end if;
3213 ' ' || to_char(p_attribute_application_id) ||
3214 ' ' || p_attribute_code);
3215 FND_MSG_PUB.Add;
3217 rollback to start_update_key_column;
3218 p_return_status := FND_API.G_RET_STS_ERROR;
3219 FND_MSG_PUB.Count_And_Get (
3220 p_count => p_msg_count,
3221 p_data => p_msg_data);
3222 WHEN FND_API.G_EXC_ERROR THEN
3223 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3224 FND_MESSAGE.SET_NAME('AK','AK_FK_COLUMN_NOT_UPDATED');
3225 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name ||
3226 ' ' || to_char(p_attribute_application_id) ||
3227 ' ' || p_attribute_code);
3228 FND_MSG_PUB.Add;
3229 end if;
3230 p_return_status := FND_API.G_RET_STS_ERROR;
3231 rollback to start_update_key_column;
3232 FND_MSG_PUB.Count_And_Get (
3233 p_count => p_msg_count,
3234 p_data => p_msg_data);
3235 WHEN OTHERS THEN
3236 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3237 rollback to start_update_key_column;
3238 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3239 SUBSTR (SQLERRM, 1, 240) );
3240 FND_MSG_PUB.Add;
3241 FND_MSG_PUB.Count_And_Get (
3242 p_count => p_msg_count,
3243 p_data => p_msg_data);
3244 end UPDATE_FOREIGN_KEY_COLUMN;
3245
3246 --=======================================================
3247 -- Procedure UPDATE_UNIQUE_KEY
3248 --
3249 -- Usage Private API for updating a unique key.
3250 -- This API should only be called by other APIs
3251 -- that are owned by the Core Modules Team (AK).
3252 --
3253 -- Desc This API updates a unique key using the given info
3254 --
3255 -- Results The API returns the standard p_return_status parameter
3256 -- indicating one of the standard return statuses :
3257 -- * Unexpected error
3258 -- * Error
3259 -- * Success
3260 -- Parameters Unique Key columns
3261 -- p_loader_timestamp : IN optional
3262 -- If a timestamp is passed, the API will update the
3263 -- record using this timestamp. Only the upload API
3264 -- should call with this parameter loaded.
3265 --
3266 -- Version Initial version number = 1.0
3267 -- History Current version number = 1.0
3268 --=======================================================
3269 procedure UPDATE_UNIQUE_KEY (
3270 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3271 p_api_version_number IN NUMBER,
3272 p_init_msg_tbl IN BOOLEAN := FALSE,
3273 p_msg_count OUT NOCOPY NUMBER,
3274 p_msg_data OUT NOCOPY VARCHAR2,
3275 p_return_status OUT NOCOPY VARCHAR2,
3276 p_unique_key_name IN VARCHAR2,
3277 p_database_object_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
3278 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
3279 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
3280 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3281 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3282 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3283 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3284 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3285 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3286 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3287 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3288 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3289 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3290 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3291 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3292 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3293 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3294 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3295 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
3296 p_creation_date IN DATE := FND_API.G_MISS_DATE,
3297 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
3298 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
3299 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
3300 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
3301 p_pass IN NUMBER,
3302 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
3303 ) is
3304 cursor l_get_row_csr is
3305 select *
3306 from AK_UNIQUE_KEYS
3307 where UNIQUE_KEY_NAME = p_unique_key_name
3308 for update of DATABASE_OBJECT_NAME;
3309 l_api_version_number CONSTANT number := 1.0;
3310 l_api_name CONSTANT varchar2(30):= 'Update_Unique_Key';
3311 l_created_by number;
3312 l_creation_date date;
3313 l_unique_key_rec ak_unique_keys%ROWTYPE;
3314 l_error boolean;
3315 l_last_update_date date;
3316 l_last_update_login number;
3317 l_last_updated_by number;
3318 l_return_status varchar2(1);
3319 l_file_version number;
3320 begin
3321 IF NOT FND_API.Compatible_API_Call (
3322 l_api_version_number, p_api_version_number, l_api_name,
3323 G_PKG_NAME) then
3324 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3325 return;
3326 END IF;
3327
3328 -- Initialize the message table if requested.
3329
3330 if p_init_msg_tbl then
3334 savepoint start_update_unique_key;
3331 FND_MSG_PUB.initialize;
3332 end if;
3333
3335
3336 --** retrieve ak_unique_keys row if it exists **
3337 open l_get_row_csr;
3338 fetch l_get_row_csr into l_unique_key_rec;
3339 if (l_get_row_csr%notfound) then
3340 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3341 FND_MESSAGE.SET_NAME('AK','AK_UNIQUE_KEY_DOES_NOT_EXIST');
3342 FND_MSG_PUB.Add;
3343 end if;
3344 -- dbms_output.put_line('Error - Row does not exist');
3345 close l_get_row_csr;
3346 raise FND_API.G_EXC_ERROR;
3347 end if;
3348 close l_get_row_csr;
3349
3350 --** validate table columns passed in **
3351 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
3352 if not VALIDATE_UNIQUE_KEY (
3353 p_validation_level => p_validation_level,
3354 p_api_version_number => 1.0,
3355 p_return_status => l_return_status,
3356 p_unique_key_name => p_unique_key_name,
3357 p_database_object_name => p_database_object_name,
3358 p_application_id => p_application_id,
3359 p_caller => AK_ON_OBJECTS_PVT.G_UPDATE,
3360 p_pass => p_pass
3361 ) then
3362 --dbms_output.put_line(l_api_name || ' validation failed');
3363 raise FND_API.G_EXC_ERROR;
3364 end if;
3365 end if;
3366
3367 --** Load record to be updated to the database **
3368 --** - load non-null columns **
3369
3370 if (p_database_object_name <> FND_API.G_MISS_CHAR) then
3371 l_unique_key_rec.database_object_name := p_database_object_name;
3372 end if;
3373 if (p_application_id <> FND_API.G_MISS_NUM) then
3374 l_unique_key_rec.application_id := p_application_id;
3375 end if;
3376 if (p_attribute_category <> FND_API.G_MISS_CHAR) or
3377 (p_attribute_category is null) then
3378 l_unique_key_rec.attribute_category := p_attribute_category;
3379 end if;
3380 if (p_attribute1 <> FND_API.G_MISS_CHAR) or
3381 (p_attribute1 is null) then
3382 l_unique_key_rec.attribute1 := p_attribute1;
3383 end if;
3384 if (p_attribute2 <> FND_API.G_MISS_CHAR) or
3385 (p_attribute2 is null) then
3386 l_unique_key_rec.attribute2 := p_attribute2;
3387 end if;
3388 if (p_attribute3 <> FND_API.G_MISS_CHAR) or
3389 (p_attribute3 is null) then
3390 l_unique_key_rec.attribute3 := p_attribute3;
3391 end if;
3392 if (p_attribute4 <> FND_API.G_MISS_CHAR) or
3393 (p_attribute4 is null) then
3394 l_unique_key_rec.attribute4 := p_attribute4;
3395 end if;
3396 if (p_attribute5 <> FND_API.G_MISS_CHAR) or
3397 (p_attribute5 is null) then
3398 l_unique_key_rec.attribute5 := p_attribute5;
3399 end if;
3400 if (p_attribute6 <> FND_API.G_MISS_CHAR) or
3401 (p_attribute6 is null) then
3402 l_unique_key_rec.attribute6 := p_attribute6;
3403 end if;
3404 if (p_attribute7 <> FND_API.G_MISS_CHAR) or
3405 (p_attribute7 is null) then
3406 l_unique_key_rec.attribute7 := p_attribute7;
3407 end if;
3408 if (p_attribute8 <> FND_API.G_MISS_CHAR) or
3409 (p_attribute8 is null) then
3410 l_unique_key_rec.attribute8 := p_attribute8;
3411 end if;
3412 if (p_attribute9 <> FND_API.G_MISS_CHAR) or
3413 (p_attribute9 is null) then
3414 l_unique_key_rec.attribute9 := p_attribute9;
3415 end if;
3416 if (p_attribute10 <> FND_API.G_MISS_CHAR) or
3417 (p_attribute10 is null) then
3418 l_unique_key_rec.attribute10 := p_attribute10;
3419 end if;
3420 if (p_attribute11 <> FND_API.G_MISS_CHAR) or
3421 (p_attribute11 is null) then
3422 l_unique_key_rec.attribute11 := p_attribute11;
3423 end if;
3424 if (p_attribute12 <> FND_API.G_MISS_CHAR) or
3425 (p_attribute12 is null) then
3426 l_unique_key_rec.attribute12 := p_attribute12;
3427 end if;
3428 if (p_attribute13 <> FND_API.G_MISS_CHAR) or
3429 (p_attribute13 is null) then
3430 l_unique_key_rec.attribute13 := p_attribute13;
3431 end if;
3432 if (p_attribute14 <> FND_API.G_MISS_CHAR) or
3433 (p_attribute14 is null) then
3434 l_unique_key_rec.attribute14 := p_attribute14;
3435 end if;
3436 if (p_attribute15 <> FND_API.G_MISS_CHAR) or
3437 (p_attribute15 is null) then
3438 l_unique_key_rec.attribute15 := p_attribute15;
3439 end if;
3440
3441 if (p_created_by <> FND_API.G_MISS_NUM) then
3442 l_created_by := p_created_by;
3443 end if;
3444 if (p_creation_date <> FND_API.G_MISS_DATE) then
3445 l_creation_date := p_creation_date;
3446 end if;
3447 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
3448 l_last_updated_by := p_last_updated_by;
3449 end if;
3450 if (p_last_update_date <> FND_API.G_MISS_DATE) then
3451 l_last_update_date := p_last_update_date;
3452 end if;
3453 if (p_last_update_login <> FND_API.G_MISS_NUM) then
3454 l_last_update_login := p_last_update_login;
3455 end if;
3456
3457 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
3458 p_loader_timestamp => p_loader_timestamp,
3459 p_created_by => l_created_by,
3460 p_creation_date => l_creation_date,
3461 p_last_updated_by => l_last_updated_by,
3462 p_db_last_updated_by => l_unique_key_rec.last_updated_by,
3463 p_last_update_date => l_last_update_date,
3464 p_db_last_update_date => l_unique_key_rec.last_update_date,
3465 p_last_update_login => l_last_update_login,
3466 p_create_or_update => 'UPDATE') then
3467
3468 update AK_UNIQUE_KEYS set
3469 DATABASE_OBJECT_NAME = l_unique_key_rec.database_object_name,
3470 APPLICATION_ID = l_unique_key_rec.application_id,
3474 ATTRIBUTE3 = l_unique_key_rec.attribute3,
3471 ATTRIBUTE_CATEGORY = l_unique_key_rec.attribute_category,
3472 ATTRIBUTE1 = l_unique_key_rec.attribute1,
3473 ATTRIBUTE2 = l_unique_key_rec.attribute2,
3475 ATTRIBUTE4 = l_unique_key_rec.attribute4,
3476 ATTRIBUTE5 = l_unique_key_rec.attribute5,
3477 ATTRIBUTE6 = l_unique_key_rec.attribute6,
3478 ATTRIBUTE7 = l_unique_key_rec.attribute7,
3479 ATTRIBUTE8 = l_unique_key_rec.attribute8,
3480 ATTRIBUTE9 = l_unique_key_rec.attribute9,
3481 ATTRIBUTE10 = l_unique_key_rec.attribute10,
3482 ATTRIBUTE11 = l_unique_key_rec.attribute11,
3483 ATTRIBUTE12 = l_unique_key_rec.attribute12,
3484 ATTRIBUTE13 = l_unique_key_rec.attribute13,
3485 ATTRIBUTE14 = l_unique_key_rec.attribute14,
3486 ATTRIBUTE15 = l_unique_key_rec.attribute15,
3487 LAST_UPDATE_DATE = l_last_update_date,
3488 LAST_UPDATED_BY = l_last_updated_by,
3489 LAST_UPDATE_LOGIN = l_last_update_login
3490 where unique_key_name = p_unique_key_name;
3491 if (sql%notfound) then
3492 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3493 FND_MESSAGE.SET_NAME('AK','AK_UNIQUE_KEY_UPDATE_FAILED');
3494 FND_MSG_PUB.Add;
3495 end if;
3496 --dbms_output.put_line('Row does not exist during update');
3497 raise FND_API.G_EXC_ERROR;
3498 end if;
3499
3500 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
3501 FND_MESSAGE.SET_NAME('AK','AK_UNIQUE_KEY_UPDATED');
3502 FND_MESSAGE.SET_TOKEN('KEY',p_unique_key_name);
3503 FND_MSG_PUB.Add;
3504 end if;
3505
3506 end if;
3507 p_return_status := FND_API.G_RET_STS_SUCCESS;
3508
3509 FND_MSG_PUB.Count_And_Get (
3510 p_count => p_msg_count,
3511 p_data => p_msg_data);
3512
3513 EXCEPTION
3514 WHEN VALUE_ERROR THEN
3515 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3516 FND_MESSAGE.SET_NAME('AK','AK_UNIQUE_KEY_VALUE_ERROR');
3517 FND_MESSAGE.SET_TOKEN('KEY',p_unique_key_name);
3518 FND_MSG_PUB.Add;
3519 end if;
3520 rollback to start_update_unique_key;
3521 p_return_status := FND_API.G_RET_STS_ERROR;
3522 FND_MSG_PUB.Count_And_Get (
3523 p_count => p_msg_count,
3524 p_data => p_msg_data);
3525 WHEN FND_API.G_EXC_ERROR THEN
3526 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3527 FND_MESSAGE.SET_NAME('AK','AK_UNIQUE_KEY_NOT_UPDATED');
3528 FND_MESSAGE.SET_TOKEN('KEY',p_unique_key_name);
3529 FND_MSG_PUB.Add;
3530 end if;
3531 p_return_status := FND_API.G_RET_STS_ERROR;
3532 rollback to start_update_unique_key;
3533 FND_MSG_PUB.Count_And_Get (
3534 p_count => p_msg_count,
3535 p_data => p_msg_data);
3536 WHEN OTHERS THEN
3537 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3538 rollback to start_update_unique_key;
3539 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3540 SUBSTR (SQLERRM, 1, 240) );
3541 FND_MSG_PUB.Add;
3542 FND_MSG_PUB.Count_And_Get (
3543 p_count => p_msg_count,
3544 p_data => p_msg_data);
3545 end UPDATE_UNIQUE_KEY;
3546
3547 --=======================================================
3548 -- Procedure UPDATE_UNIQUE_KEY_COLUMN
3549 --
3550 -- Usage Private API for updating a unique key column.
3551 -- This API should only be called by other APIs
3552 -- that are owned by the Core Modules Team (AK).
3553 --
3554 -- Desc This API updates a unique key column using the given info
3555 --
3556 -- Results The API returns the standard p_return_status parameter
3557 -- indicating one of the standard return statuses :
3558 -- * Unexpected error
3559 -- * Error
3560 -- * Success
3561 -- Parameters Unique Key Column columns
3562 -- p_loader_timestamp : IN optional
3563 -- If a timestamp is passed, the API will update the
3564 -- record using this timestamp. Only the upload API
3565 -- should call with this parameter loaded.
3566 --
3567 -- Version Initial version number = 1.0
3568 -- History Current version number = 1.0
3569 --=======================================================
3570 procedure UPDATE_UNIQUE_KEY_COLUMN (
3571 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3572 p_api_version_number IN NUMBER,
3573 p_init_msg_tbl IN BOOLEAN := FALSE,
3574 p_msg_count OUT NOCOPY NUMBER,
3575 p_msg_data OUT NOCOPY VARCHAR2,
3576 p_return_status OUT NOCOPY VARCHAR2,
3577 p_unique_key_name IN VARCHAR2,
3578 p_attribute_application_id IN NUMBER,
3579 p_attribute_code IN VARCHAR2,
3580 p_unique_key_sequence IN NUMBER := FND_API.G_MISS_NUM,
3581 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
3582 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3583 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3584 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3585 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3586 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3587 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3588 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3589 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3590 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3591 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3595 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3592 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3593 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3594 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3596 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3597 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
3598 p_creation_date IN DATE := FND_API.G_MISS_DATE,
3599 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
3600 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
3601 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
3602 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
3603 p_pass IN NUMBER,
3604 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
3605 ) is
3606 cursor l_get_row_csr is
3607 select *
3608 from AK_UNIQUE_KEY_COLUMNS
3609 where UNIQUE_KEY_NAME = p_unique_key_name
3610 and ATTRIBUTE_APPLICATION_ID = p_attribute_application_id
3611 and ATTRIBUTE_CODE = p_attribute_code
3612 for update of UNIQUE_KEY_SEQUENCE;
3613 l_api_version_number CONSTANT number := 1.0;
3614 l_api_name CONSTANT varchar2(30):= 'Update_Unique_Key_Column';
3615 l_created_by number;
3616 l_creation_date date;
3617 l_key_column_rec ak_unique_key_columns%ROWTYPE;
3618 l_error boolean;
3619 l_last_update_date date;
3620 l_last_update_login number;
3621 l_last_updated_by number;
3622 l_return_status varchar2(1);
3623 l_file_version number;
3624 begin
3625 IF NOT FND_API.Compatible_API_Call (
3626 l_api_version_number, p_api_version_number, l_api_name,
3627 G_PKG_NAME) then
3628 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3629 return;
3630 END IF;
3631
3632 -- Initialize the message table if requested.
3633
3634 if p_init_msg_tbl then
3635 FND_MSG_PUB.initialize;
3636 end if;
3637
3638 savepoint start_update_key_column;
3639
3640 --** retrieve ak_unique_key_columns row if it exists **
3641 open l_get_row_csr;
3642 fetch l_get_row_csr into l_key_column_rec;
3643 if (l_get_row_csr%notfound) then
3644 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3645 FND_MESSAGE.SET_NAME('AK','AK_UK_COLUMN_DOES_NOT_EXIST');
3646 FND_MSG_PUB.Add;
3647 end if;
3648 --dbms_output.put_line('Error - Row does not exist');
3649 close l_get_row_csr;
3650 raise FND_API.G_EXC_ERROR;
3651 end if;
3652 close l_get_row_csr;
3653
3654 --** validate table columns passed in **
3655 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
3656 if not VALIDATE_UNIQUE_KEY_COLUMN (
3657 p_validation_level => p_validation_level,
3658 p_api_version_number => 1.0,
3659 p_return_status => l_return_status,
3660 p_unique_key_name => p_unique_key_name,
3661 p_attribute_application_id => p_attribute_application_id,
3662 p_attribute_code => p_attribute_code,
3663 p_unique_key_sequence => p_unique_key_sequence,
3664 p_caller => AK_ON_OBJECTS_PVT.G_UPDATE,
3665 p_pass => p_pass
3666 ) then
3667 --dbms_output.put_line(l_api_name || ' validation failed');
3668 raise FND_API.G_EXC_ERROR;
3669 end if;
3670 end if;
3671
3672 --** Load record to be updated to the database **
3673 --** - load non-null columns **
3674
3675 if (p_unique_key_sequence <> FND_API.G_MISS_NUM) then
3676 l_key_column_rec.unique_key_sequence := p_unique_key_sequence;
3677 end if;
3678 if (p_attribute_category <> FND_API.G_MISS_CHAR) or
3679 (p_attribute_category is null) then
3680 l_key_column_rec.attribute_category := p_attribute_category;
3681 end if;
3682 if (p_attribute1 <> FND_API.G_MISS_CHAR) or
3683 (p_attribute1 is null) then
3684 l_key_column_rec.attribute1 := p_attribute1;
3685 end if;
3686 if (p_attribute2 <> FND_API.G_MISS_CHAR) or
3687 (p_attribute2 is null) then
3688 l_key_column_rec.attribute2 := p_attribute2;
3689 end if;
3690 if (p_attribute3 <> FND_API.G_MISS_CHAR) or
3691 (p_attribute3 is null) then
3692 l_key_column_rec.attribute3 := p_attribute3;
3693 end if;
3694 if (p_attribute4 <> FND_API.G_MISS_CHAR) or
3695 (p_attribute4 is null) then
3696 l_key_column_rec.attribute4 := p_attribute4;
3697 end if;
3698 if (p_attribute5 <> FND_API.G_MISS_CHAR) or
3699 (p_attribute5 is null) then
3700 l_key_column_rec.attribute5 := p_attribute5;
3701 end if;
3702 if (p_attribute6 <> FND_API.G_MISS_CHAR) or
3703 (p_attribute6 is null) then
3704 l_key_column_rec.attribute6 := p_attribute6;
3705 end if;
3706 if (p_attribute7 <> FND_API.G_MISS_CHAR) or
3707 (p_attribute7 is null) then
3708 l_key_column_rec.attribute7 := p_attribute7;
3709 end if;
3710 if (p_attribute8 <> FND_API.G_MISS_CHAR) or
3711 (p_attribute8 is null) then
3712 l_key_column_rec.attribute8 := p_attribute8;
3713 end if;
3714 if (p_attribute9 <> FND_API.G_MISS_CHAR) or
3715 (p_attribute9 is null) then
3716 l_key_column_rec.attribute9 := p_attribute9;
3717 end if;
3718 if (p_attribute10 <> FND_API.G_MISS_CHAR) or
3719 (p_attribute10 is null) then
3720 l_key_column_rec.attribute10 := p_attribute10;
3721 end if;
3722 if (p_attribute11 <> FND_API.G_MISS_CHAR) or
3723 (p_attribute11 is null) then
3724 l_key_column_rec.attribute11 := p_attribute11;
3728 l_key_column_rec.attribute12 := p_attribute12;
3725 end if;
3726 if (p_attribute12 <> FND_API.G_MISS_CHAR) or
3727 (p_attribute12 is null) then
3729 end if;
3730 if (p_attribute13 <> FND_API.G_MISS_CHAR) or
3731 (p_attribute13 is null) then
3732 l_key_column_rec.attribute13 := p_attribute13;
3733 end if;
3734 if (p_attribute14 <> FND_API.G_MISS_CHAR) or
3735 (p_attribute14 is null) then
3736 l_key_column_rec.attribute14 := p_attribute14;
3737 end if;
3738 if (p_attribute15 <> FND_API.G_MISS_CHAR) or
3739 (p_attribute15 is null) then
3740 l_key_column_rec.attribute15 := p_attribute15;
3741 end if;
3742
3743 if (p_created_by <> FND_API.G_MISS_NUM) then
3744 l_created_by := p_created_by;
3745 end if;
3746 if (p_creation_date <> FND_API.G_MISS_DATE) then
3747 l_creation_date := p_creation_date;
3748 end if;
3749 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
3750 l_last_updated_by := p_last_updated_by;
3751 end if;
3752 if (p_last_update_date <> FND_API.G_MISS_DATE) then
3753 l_last_update_date := p_last_update_date;
3754 end if;
3755 if (p_last_update_login <> FND_API.G_MISS_NUM) then
3756 l_last_update_login := p_last_update_login;
3757 end if;
3758
3759 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
3760 p_loader_timestamp => p_loader_timestamp,
3761 p_created_by => l_created_by,
3762 p_creation_date => l_creation_date,
3763 p_last_updated_by => l_last_updated_by,
3764 p_db_last_updated_by => l_key_column_rec.last_updated_by,
3765 p_last_update_date => l_last_update_date,
3766 p_db_last_update_date => l_key_column_rec.last_update_date,
3767 p_last_update_login => l_last_update_login,
3768 p_create_or_update => 'UPDATE') then
3769
3770 update AK_UNIQUE_KEY_COLUMNS set
3771 UNIQUE_KEY_SEQUENCE = l_key_column_rec.unique_key_sequence,
3772 ATTRIBUTE_CATEGORY = l_key_column_rec.attribute_category,
3773 ATTRIBUTE1 = l_key_column_rec.attribute1,
3774 ATTRIBUTE2 = l_key_column_rec.attribute2,
3775 ATTRIBUTE3 = l_key_column_rec.attribute3,
3776 ATTRIBUTE4 = l_key_column_rec.attribute4,
3777 ATTRIBUTE5 = l_key_column_rec.attribute5,
3778 ATTRIBUTE6 = l_key_column_rec.attribute6,
3779 ATTRIBUTE7 = l_key_column_rec.attribute7,
3780 ATTRIBUTE8 = l_key_column_rec.attribute8,
3781 ATTRIBUTE9 = l_key_column_rec.attribute9,
3782 ATTRIBUTE10 = l_key_column_rec.attribute10,
3783 ATTRIBUTE11 = l_key_column_rec.attribute11,
3784 ATTRIBUTE12 = l_key_column_rec.attribute12,
3785 ATTRIBUTE13 = l_key_column_rec.attribute13,
3786 ATTRIBUTE14 = l_key_column_rec.attribute14,
3787 ATTRIBUTE15 = l_key_column_rec.attribute15,
3788 LAST_UPDATE_DATE = l_last_update_date,
3789 LAST_UPDATED_BY = l_last_updated_by,
3790 LAST_UPDATE_LOGIN = l_last_update_login
3791 where unique_key_name = p_unique_key_name
3792 and attribute_application_id = p_attribute_application_id
3793 and attribute_code = p_attribute_code;
3794 if (sql%notfound) then
3795 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3796 FND_MESSAGE.SET_NAME('AK','AK_UK_COLUMN_UPDATE_FAILED');
3797 FND_MSG_PUB.Add;
3798 end if;
3799 --dbms_output.put_line('Row does not exist during update');
3800 raise FND_API.G_EXC_ERROR;
3801 end if;
3802
3803 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
3804 FND_MESSAGE.SET_NAME('AK','AK_UK_COLUMN_UPDATED');
3805 FND_MESSAGE.SET_TOKEN('KEY',p_unique_key_name || ' ' ||
3806 ' ' || to_char(p_attribute_application_id) ||
3807 ' ' || p_attribute_code);
3808 FND_MSG_PUB.Add;
3809 end if;
3810
3811 end if;
3812 p_return_status := FND_API.G_RET_STS_SUCCESS;
3813
3814 FND_MSG_PUB.Count_And_Get (
3815 p_count => p_msg_count,
3816 p_data => p_msg_data);
3817
3818 EXCEPTION
3819 WHEN VALUE_ERROR THEN
3820 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3821 FND_MESSAGE.SET_NAME('AK','AK_UK_COLUMN_VALUE_ERROR');
3822 FND_MESSAGE.SET_TOKEN('KEY',p_unique_key_name || ' ' ||
3823 ' ' || to_char(p_attribute_application_id) ||
3824 ' ' || p_attribute_code);
3825 FND_MSG_PUB.Add;
3826 end if;
3827 rollback to start_update_key_column;
3828 p_return_status := FND_API.G_RET_STS_ERROR;
3829 FND_MSG_PUB.Count_And_Get (
3830 p_count => p_msg_count,
3831 p_data => p_msg_data);
3832 WHEN FND_API.G_EXC_ERROR THEN
3833 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3834 FND_MESSAGE.SET_NAME('AK','AK_UK_COLUMN_NOT_UPDATED');
3835 FND_MESSAGE.SET_TOKEN('KEY',p_unique_key_name || ' ' ||
3836 ' ' || to_char(p_attribute_application_id) ||
3837 ' ' || p_attribute_code);
3838 FND_MSG_PUB.Add;
3839 end if;
3840 p_return_status := FND_API.G_RET_STS_ERROR;
3841 rollback to start_update_key_column;
3842 FND_MSG_PUB.Count_And_Get (
3843 p_count => p_msg_count,
3844 p_data => p_msg_data);
3845 WHEN OTHERS THEN
3846 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3847 rollback to start_update_key_column;
3848 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3849 SUBSTR (SQLERRM, 1, 240) );
3850 FND_MSG_PUB.Add;
3851 FND_MSG_PUB.Count_And_Get (
3852 p_count => p_msg_count,
3853 p_data => p_msg_data);
3854 end UPDATE_UNIQUE_KEY_COLUMN;
3855
3856 --========================================================
3857 -- Function VALIDATE_FOREIGN_KEY
3858 --
3859 -- Usage Private API for validating a foreign key. This
3860 -- API should only be called by other APIs that are
3861 -- owned by the Core Modules Team (AK).
3865 -- Results The API returns the standard p_return_status parameter
3862 --
3863 -- Desc Perform validation on a foreign key record.
3864 --
3866 -- indicating one of the standard return statuses :
3867 -- * Unexpected error
3868 -- * Error
3869 -- * Success
3870 -- In addition, this function returns TRUE if all
3871 -- validation tests are passed, or FALSE otherwise.
3872 -- Parameters Foreign Key columns
3873 -- p_caller : IN required
3874 -- Must be one of the following values defined
3875 -- in package AK_ON_OBJECTS_PVT:
3876 -- - G_CREATE (if calling from the Create API)
3877 -- - G_DOWNLOAD (if calling from the Download API)
3878 -- - G_UPDATE (if calling from the Update API)
3879 --
3880 -- Note This API is intended for performing record-level
3881 -- validation. It is not designed for item-level
3882 -- validation.
3883 --
3884 -- Version Initial version number = 1.0
3885 -- History Current version number = 1.0
3886 --========================================================
3887 function VALIDATE_FOREIGN_KEY (
3888 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3889 p_api_version_number IN NUMBER,
3890 p_return_status OUT NOCOPY VARCHAR2,
3891 p_foreign_key_name IN VARCHAR2,
3892 p_database_object_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
3893 p_unique_key_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
3894 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
3895 p_from_to_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
3896 p_from_to_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
3897 p_to_from_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
3898 p_to_from_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
3899 p_caller IN VARCHAR2,
3900 p_pass IN NUMBER := 2
3901 ) return BOOLEAN is
3902 l_api_version_number CONSTANT number := 1.0;
3903 l_api_name CONSTANT varchar2(30) := 'Validate_Foreign_Key';
3904 l_error BOOLEAN;
3905 l_return_status VARCHAR2(1);
3906 begin
3907 IF NOT FND_API.Compatible_API_Call (
3908 l_api_version_number, p_api_version_number, l_api_name,
3909 G_PKG_NAME) then
3910 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3911 return FALSE;
3912 END IF;
3913
3914 l_error := FALSE;
3915
3916 --** if validation level is none, no validation is necessary
3917 if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
3918 p_return_status := FND_API.G_RET_STS_SUCCESS;
3919 return TRUE;
3920 end if;
3921
3922 --** check that key columns are not null and not missing **
3923 if ((p_foreign_key_name is null) or
3924 (p_foreign_key_name = FND_API.G_MISS_CHAR)) then
3925 l_error := TRUE;
3926 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
3927 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
3928 FND_MESSAGE.SET_TOKEN('COLUMN', 'FOREIGN_KEY_NAME');
3929 FND_MSG_PUB.Add;
3930 end if;
3931 end if;
3932
3933 -- - Check that the parent object exists
3934 --* (This check can be skipped if called from the download procedure
3935 --* which have already read the parent object.)
3936 --* (This check is only done if a view name is given, which may not
3937 --* be the case if called from the Update_Foreign_Key API.)
3938 if (p_caller <> AK_ON_OBJECTS_PVT.G_DOWNLOAD) then
3939 if (p_database_object_name <> FND_API.G_MISS_CHAR) then
3940 if (NOT AK_OBJECT_PVT.OBJECT_EXISTS (
3941 p_api_version_number => 1.0,
3942 p_return_status => l_return_status,
3943 p_database_object_name => p_database_object_name) ) then
3944 l_error := TRUE;
3945 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
3946 FND_MESSAGE.SET_NAME('AK','AK_INVALID_OBJECT_REFERENCE');
3947 FND_MESSAGE.SET_TOKEN('KEY', p_database_object_name);
3948 FND_MSG_PUB.Add;
3949 end if;
3950 end if;
3951 end if;
3952 end if;
3953
3954 --** check that required columns are not null and, unless calling **
3955 --** from UPDATE procedure, the columns are not missing **
3956 if ((p_database_object_name is null) or
3957 (p_database_object_name = FND_API.G_MISS_CHAR and
3958 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE))
3959 then
3960 l_error := TRUE;
3961 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
3962 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
3963 FND_MESSAGE.SET_TOKEN('COLUMN', 'DATABASE_OBJECT_NAME');
3964 FND_MSG_PUB.Add;
3965 end if;
3966 end if;
3967
3968 if ((p_unique_key_name is null) or
3969 (p_unique_key_name = FND_API.G_MISS_CHAR and
3970 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
3971 l_error := TRUE;
3972 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
3973 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
3974 FND_MESSAGE.SET_TOKEN('COLUMN', 'UNIQUE_KEY_NAME');
3975 FND_MSG_PUB.Add;
3976 end if;
3977 end if;
3978
3979 if ((p_application_id is null) or
3980 (p_application_id = FND_API.G_MISS_NUM and
3981 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
3982 l_error := TRUE;
3986 FND_MSG_PUB.Add;
3983 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
3984 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
3985 FND_MESSAGE.SET_TOKEN('COLUMN', 'APPLICATION_ID');
3987 end if;
3988 end if;
3989
3990 --** Validate columns **
3991 -- - application ID
3992 if (p_application_id <> FND_API.G_MISS_NUM) then
3993 if (NOT AK_ON_OBJECTS_PVT.VALID_APPLICATION_ID (
3994 p_api_version_number => 1.0,
3995 p_return_status => l_return_status,
3996 p_application_id => p_application_id) ) then
3997 l_error := TRUE;
3998 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
3999 FND_MESSAGE.SET_NAME('AK','AK_INVALID_COLUMN_VALUE');
4000 FND_MESSAGE.SET_TOKEN('COLUMN','APPLICATION_ID');
4001 FND_MSG_PUB.Add;
4002 end if;
4003 end if;
4004 end if;
4005
4006 -- - unique_key_name
4007 if (p_unique_key_name <> FND_API.G_MISS_CHAR) then
4008 if (NOT AK_KEY_PVT.UNIQUE_KEY_EXISTS (
4009 p_api_version_number => 1.0,
4010 p_return_status => l_return_status,
4011 p_unique_key_name => p_unique_key_name) ) then
4012 l_error := TRUE;
4013 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4014 FND_MESSAGE.SET_NAME('AK','AK_INVALID_UK_REFERENCE');
4015 FND_MESSAGE.SET_TOKEN('KEY', p_unique_key_name);
4016 FND_MSG_PUB.Add;
4017 end if;
4018 --dbms_output.put_line('Invalid unique key name');
4019 end if;
4020 end if;
4021
4022 -- return true if no error, false otherwise
4023 p_return_status := FND_API.G_RET_STS_SUCCESS;
4024 return (not l_error);
4025
4026 EXCEPTION
4027 WHEN FND_API.G_EXC_ERROR THEN
4028 p_return_status := FND_API.G_RET_STS_ERROR;
4029 return FALSE;
4030 WHEN OTHERS THEN
4031 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4032 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4033 SUBSTR (SQLERRM, 1, 240) );
4034 FND_MSG_PUB.Add;
4035 return FALSE;
4036
4037 end VALIDATE_FOREIGN_KEY;
4038
4039 --========================================================
4040 -- Function VALIDATE_FOREIGN_KEY_COLUMN
4041 --
4042 -- Usage Private API for validating a foreign key column record.
4043 -- This API should only be called by other APIs that are
4044 -- owned by the Core Modules Team (AK).
4045 --
4046 -- Desc Perform validation on a foreign key column record.
4047 --
4048 -- Results The API returns the standard p_return_status parameter
4049 -- indicating one of the standard return statuses :
4050 -- * Unexpected error
4051 -- * Error
4052 -- * Success
4053 -- In addition, this function returns TRUE if all
4054 -- validation tests are passed, or FALSE otherwise.
4055 -- Parameters Foreign Key Column columns
4056 -- p_caller : IN required
4057 -- Must be one of the following values defined
4058 -- in package AK_ON_OBJECTS_PVT:
4059 -- - G_CREATE (if calling from the Create API)
4060 -- - G_DOWNLOAD (if calling from the Download API)
4061 -- - G_UPDATE (if calling from the Update API)
4062 --
4063 -- Note This API is intended for performing record-level
4064 -- validation. It is not designed for item-level
4065 -- validation.
4066 --
4067 -- Version Initial version number = 1.0
4068 -- History Current version number = 1.0
4069 --========================================================
4070 function VALIDATE_FOREIGN_KEY_COLUMN (
4071 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4072 p_api_version_number IN NUMBER,
4073 p_return_status OUT NOCOPY VARCHAR2,
4074 p_foreign_key_name IN VARCHAR2,
4075 p_attribute_application_id IN NUMBER,
4076 p_attribute_code IN VARCHAR2,
4077 p_foreign_key_sequence IN NUMBER := FND_API.G_MISS_NUM,
4078 p_caller IN VARCHAR2,
4079 p_pass IN NUMBER := 2
4080 ) return BOOLEAN is
4081 cursor l_check_fk_csr is
4082 select database_object_name
4083 from AK_FOREIGN_KEYS
4084 where FOREIGN_KEY_NAME = p_foreign_key_name;
4085 cursor l_check_seq_csr is
4086 select 1
4087 from AK_UNIQUE_KEY_COLUMNS pkc, AK_FOREIGN_KEYS fk
4088 where fk.FOREIGN_KEY_NAME = p_foreign_key_name
4089 and fk.UNIQUE_KEY_NAME = pkc.UNIQUE_KEY_NAME
4090 and pkc.UNIQUE_KEY_SEQUENCE = p_foreign_key_sequence;
4091 l_api_version_number CONSTANT number := 1.0;
4092 l_api_name CONSTANT varchar2(30) := 'Validate_Foreign_Key_Column';
4093 l_dummy NUMBER;
4094 l_error BOOLEAN;
4095 l_return_status VARCHAR2(1);
4096 l_database_object_name VARCHAR2(30);
4097 begin
4098
4099 IF NOT FND_API.Compatible_API_Call (
4100 l_api_version_number, p_api_version_number, l_api_name,
4101 G_PKG_NAME) then
4102 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4103 return FALSE;
4104 END IF;
4105
4106 l_error := FALSE;
4107
4108 --** if validation level is none, no validation is necessary
4109 if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
4110 p_return_status := FND_API.G_RET_STS_SUCCESS;
4111 return TRUE;
4112 end if;
4113
4114 --** check that key columns are not null and not missing **
4115 if ((p_foreign_key_name is null) or
4119 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4116 (p_foreign_key_name = FND_API.G_MISS_CHAR)) then
4117 l_error := TRUE;
4118 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4120 FND_MESSAGE.SET_TOKEN('COLUMN', 'FOREIGN_KEY_NAME');
4121 FND_MSG_PUB.Add;
4122 end if;
4123 end if;
4124
4125 if ((p_attribute_application_id is null) or
4126 (p_attribute_application_id = FND_API.G_MISS_NUM)) then
4127 l_error := TRUE;
4128 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4129 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4130 FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_APPLICATION_ID');
4131 FND_MSG_PUB.Add;
4132 end if;
4133 end if;
4134
4135 if ((p_attribute_code is null) or
4136 (p_attribute_code = FND_API.G_MISS_CHAR)) then
4137 l_error := TRUE;
4138 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4139 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4140 FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_CODE');
4141 FND_MSG_PUB.Add;
4142 end if;
4143 end if;
4144
4145 -- - Check that the parent foreign key exists, and retrieve
4146 -- the view name for checking of valid column name below
4147 open l_check_fk_csr;
4148 fetch l_check_fk_csr into l_database_object_name;
4149 if (l_check_fk_csr%notfound) then
4150 close l_check_fk_csr;
4151 l_error := TRUE;
4152 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4153 FND_MESSAGE.SET_NAME('AK','AK_INVALID_FK_REFERENCE');
4154 FND_MESSAGE.SET_TOKEN('KEY', p_foreign_key_name);
4155 FND_MSG_PUB.Add;
4156 end if;
4157 --dbms_output.put_line('Parent foreign key does not exist!');
4158 else
4159 close l_check_fk_csr;
4160 -- - verify that the column attribute is a valid object attribute
4161 if (NOT AK_OBJECT_PVT.ATTRIBUTE_EXISTS (
4162 p_api_version_number => 1.0,
4163 p_return_status => l_return_status,
4164 p_database_object_name => l_database_object_name,
4165 p_attribute_application_id => p_attribute_application_id,
4166 p_attribute_code => p_attribute_code) ) then
4167 l_error := TRUE;
4168 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4169 FND_MESSAGE.SET_NAME('AK','AK_INVALID_OA_REFERENCE');
4170 FND_MESSAGE.SET_TOKEN('KEY', l_database_object_name ||
4171 ' ' || to_char(p_attribute_application_id) ||
4172 ' ' || p_attribute_code);
4173 FND_MSG_PUB.Add;
4174 end if;
4175 end if;
4176 end if;
4177
4178 --** check that required columns are not null and, unless calling **
4179 --** from UPDATE procedure, the columns are not missing **
4180 if ((p_foreign_key_sequence is null) or
4181 (p_foreign_key_sequence = FND_API.G_MISS_NUM and
4182 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE))
4183 then
4184 l_error := TRUE;
4185 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4186 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4187 FND_MESSAGE.SET_TOKEN('COLUMN', 'FOREIGN_KEY_SEQUENCE');
4188 FND_MSG_PUB.Add;
4189 end if;
4190 end if;
4191
4192 --** check that the foreign_key_sequence should be referencing *
4193 --** some valid unique key columns *
4194 --** (Check this only if a foreign_key_sequence value is passed) *
4195 if (p_foreign_key_sequence <> FND_API.G_MISS_NUM) then
4196 open l_check_seq_csr;
4197 fetch l_check_seq_csr into l_dummy;
4198 if (l_check_seq_csr%notfound) then
4199 close l_check_seq_csr;
4200 l_error := TRUE;
4201 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4202 FND_MESSAGE.SET_NAME('AK','AK_INVALID_FOREIGN_KEY_SEQ');
4203 FND_MESSAGE.SET_TOKEN('SEQUENCE', to_char(p_foreign_key_sequence));
4204 FND_MSG_PUB.Add;
4205 end if;
4206 else
4207 close l_check_seq_csr;
4208 end if;
4209 end if;
4210
4211 -- return true if no error, false otherwise
4212 p_return_status := FND_API.G_RET_STS_SUCCESS;
4213 return (not l_error);
4214
4215 EXCEPTION
4216 WHEN FND_API.G_EXC_ERROR THEN
4217 p_return_status := FND_API.G_RET_STS_ERROR;
4218 return FALSE;
4219 WHEN OTHERS THEN
4220 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4221 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4222 SUBSTR (SQLERRM, 1, 240) );
4223 FND_MSG_PUB.Add;
4224 return FALSE;
4225
4226 end VALIDATE_FOREIGN_KEY_COLUMN;
4227
4228 --========================================================
4229 -- Function VALIDATE_UNIQUE_KEY
4230 --
4231 -- Usage Private API for validating a unique key. This
4232 -- API should only be called by other APIs that are
4233 -- owned by the Core Modules Team (AK).
4234 --
4235 -- Desc Perform validation on a unique key record.
4236 --
4237 -- Results The API returns the standard p_return_status parameter
4238 -- indicating one of the standard return statuses :
4239 -- * Unexpected error
4240 -- * Error
4241 -- * Success
4242 -- In addition, this function returns TRUE if all
4243 -- validation tests are passed, or FALSE otherwise.
4244 -- Parameters Unique Key columns
4245 -- p_caller : IN required
4246 -- Must be one of the following values defined
4247 -- in package AK_ON_OBJECTS_PVT:
4251 --
4248 -- - G_CREATE (if calling from the Create API)
4249 -- - G_DOWNLOAD (if calling from the Download API)
4250 -- - G_UPDATE (if calling from the Update API)
4252 -- Note This API is intended for performing record-level
4253 -- validation. It is not designed for item-level
4254 -- validation.
4255 --
4256 -- Version Initial version number = 1.0
4257 -- History Current version number = 1.0
4258 --========================================================
4259 function VALIDATE_UNIQUE_KEY (
4260 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4261 p_api_version_number IN NUMBER,
4262 p_return_status OUT NOCOPY VARCHAR2,
4263 p_unique_key_name IN VARCHAR2,
4264 p_database_object_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
4265 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
4266 p_caller IN VARCHAR2,
4267 p_pass IN NUMBER := 2
4268 ) return BOOLEAN is
4269 l_api_version_number CONSTANT number := 1.0;
4270 l_api_name CONSTANT varchar2(30) := 'Validate_Unique_Key';
4271 l_error BOOLEAN;
4272 l_return_status VARCHAR2(1);
4273 begin
4274
4275 IF NOT FND_API.Compatible_API_Call (
4276 l_api_version_number, p_api_version_number, l_api_name,
4277 G_PKG_NAME) then
4278 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4279 return FALSE;
4280 END IF;
4281
4282 l_error := FALSE;
4283
4284 --** if validation level is none, no validation is necessary
4285 if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
4286 p_return_status := FND_API.G_RET_STS_SUCCESS;
4287 return TRUE;
4288 end if;
4289
4290 --** check that key columns are not null and not missing **
4291 if ((p_unique_key_name is null) or
4292 (p_unique_key_name = FND_API.G_MISS_CHAR)) then
4293 l_error := TRUE;
4294 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4295 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4296 FND_MESSAGE.SET_TOKEN('COLUMN', 'UNIQUE_KEY_NAME');
4297 FND_MSG_PUB.Add;
4298 end if;
4299 end if;
4300
4301 -- - Check that the parent object exists
4302 --* (This check can be skipped if called from the download procedure
4303 --* which have already read the parent object.)
4304 --* (This check will only be done if a view name is passed.)
4305 if (p_caller <> AK_ON_OBJECTS_PVT.G_DOWNLOAD) then
4306 if (p_database_object_name <> FND_API.G_MISS_CHAR) then
4307 if (NOT AK_OBJECT_PVT.OBJECT_EXISTS (
4308 p_api_version_number => 1.0,
4309 p_return_status => l_return_status,
4310 p_database_object_name => p_database_object_name) ) then
4311 l_error := TRUE;
4312 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4313 FND_MESSAGE.SET_NAME('AK','AK_INVALID_OBJECT_REFERENCE');
4314 FND_MESSAGE.SET_TOKEN('KEY', p_database_object_name);
4315 FND_MSG_PUB.Add;
4316 end if;
4317 end if;
4318 end if;
4319 end if;
4320
4321 --** check that required columns are not null and, unless calling **
4322 --** from UPDATE procedure, the columns are not missing **
4323 if ((p_database_object_name is null) or
4324 (p_database_object_name = FND_API.G_MISS_CHAR and
4325 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
4326 l_error := TRUE;
4327 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4328 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4329 FND_MESSAGE.SET_TOKEN('COLUMN', 'DATABASE_OBJECT_NAME');
4330 FND_MSG_PUB.Add;
4331 end if;
4332 end if;
4333
4334 if ((p_application_id is null) or
4335 (p_application_id = FND_API.G_MISS_NUM and
4336 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
4337 l_error := TRUE;
4338 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4339 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4340 FND_MESSAGE.SET_TOKEN('COLUMN', 'APPLICATION_ID');
4341 FND_MSG_PUB.Add;
4342 end if;
4343 end if;
4344
4345
4346 --** Validate columns **
4347 -- - application ID
4348 if (p_application_id <> FND_API.G_MISS_NUM) then
4349 if (NOT AK_ON_OBJECTS_PVT.VALID_APPLICATION_ID (
4350 p_api_version_number => 1.0,
4351 p_return_status => l_return_status,
4352 p_application_id => p_application_id) ) then
4353 l_error := TRUE;
4354 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4355 FND_MESSAGE.SET_NAME('AK','AK_INVALID_COLUMN_VALUE');
4356 FND_MESSAGE.SET_TOKEN('COLUMN','APPLICATION_ID');
4357 FND_MSG_PUB.Add;
4358 end if;
4359 --dbms_output.put_line('Invalid application ID');
4360 end if;
4361 end if;
4362
4363 -- return true if no error, false otherwise
4364 p_return_status := FND_API.G_RET_STS_SUCCESS;
4365 return (not l_error);
4366
4367 EXCEPTION
4368 WHEN FND_API.G_EXC_ERROR THEN
4369 p_return_status := FND_API.G_RET_STS_ERROR;
4370 return FALSE;
4371 WHEN OTHERS THEN
4372 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4373 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4374 SUBSTR (SQLERRM, 1, 240) );
4375 FND_MSG_PUB.Add;
4376 return FALSE;
4377
4378 end VALIDATE_UNIQUE_KEY;
4379
4380 --========================================================
4381 -- Function VALIDATE_UNIQUE_KEY_COLUMN
4382 --
4386 --
4383 -- Usage Private API for validating a unique key column record.
4384 -- This API should only be called by other APIs that are
4385 -- owned by the Core Modules Team (AK).
4387 -- Desc Perform validation on a unique key column record.
4388 --
4389 -- Results The API returns the standard p_return_status parameter
4390 -- indicating one of the standard return statuses :
4391 -- * Unexpected error
4392 -- * Error
4393 -- * Success
4394 -- In addition, this function returns TRUE if all
4395 -- validation tests are passed, or FALSE otherwise.
4396 -- Parameters Unique Key Column columns
4397 -- p_caller : IN required
4398 -- Must be one of the following values defined
4399 -- in package AK_ON_OBJECTS_PVT:
4400 -- - G_CREATE (if calling from the Create API)
4401 -- - G_DOWNLOAD (if calling from the Download API)
4402 -- - G_UPDATE (if calling from the Update API)
4403 --
4404 -- Note This API is intended for performing record-level
4405 -- validation. It is not designed for item-level
4406 -- validation.
4407 --
4408 -- Version Initial version number = 1.0
4409 -- History Current version number = 1.0
4410 --========================================================
4411 function VALIDATE_UNIQUE_KEY_COLUMN (
4412 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4413 p_api_version_number IN NUMBER,
4414 p_return_status OUT NOCOPY VARCHAR2,
4415 p_unique_key_name IN VARCHAR2,
4416 p_attribute_application_id IN NUMBER,
4417 p_attribute_code IN VARCHAR2,
4418 p_unique_key_sequence IN NUMBER := FND_API.G_MISS_NUM,
4419 p_caller IN VARCHAR2,
4420 p_pass IN NUMBER := 2
4421 ) return BOOLEAN is
4422 cursor l_check_pk_csr is
4423 select database_object_name
4424 from AK_UNIQUE_KEYS
4425 where UNIQUE_KEY_NAME = p_unique_key_name;
4426 cursor l_check_seq_csr is
4427 select 1
4428 from AK_UNIQUE_KEY_COLUMNS
4429 where UNIQUE_KEY_NAME = p_unique_key_name
4430 and UNIQUE_KEY_SEQUENCE = p_unique_key_sequence
4431 and ( (ATTRIBUTE_APPLICATION_ID <> p_attribute_application_id)
4432 or (ATTRIBUTE_CODE <> p_attribute_code) );
4433 l_api_version_number CONSTANT number := 1.0;
4434 l_api_name CONSTANT varchar2(30) := 'Validate_Unique_Key_Column';
4435 l_dummy NUMBER;
4436 l_error BOOLEAN;
4437 l_return_status VARCHAR2(1);
4438 l_database_object_name VARCHAR2(30);
4439 begin
4440
4441 IF NOT FND_API.Compatible_API_Call (
4442 l_api_version_number, p_api_version_number, l_api_name,
4443 G_PKG_NAME) then
4444 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4445 return FALSE;
4446 END IF;
4447
4448 l_error := FALSE;
4449
4450 --** if validation level is none, no validation is necessary
4451 if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
4452 p_return_status := FND_API.G_RET_STS_SUCCESS;
4453 return TRUE;
4454 end if;
4455
4456 --** check that key columns are not null and not missing **
4457 if ((p_unique_key_name is null) or
4458 (p_unique_key_name = FND_API.G_MISS_CHAR)) then
4459 l_error := TRUE;
4460 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4461 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4462 FND_MESSAGE.SET_TOKEN('COLUMN', 'UNIQUE_KEY_NAME');
4463 FND_MSG_PUB.Add;
4464 end if;
4465 end if;
4466
4467 if ((p_attribute_application_id is null) or
4468 (p_attribute_application_id = FND_API.G_MISS_NUM)) then
4469 l_error := TRUE;
4470 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4471 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4472 FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_APPLICATION_ID');
4473 FND_MSG_PUB.Add;
4474 end if;
4475 end if;
4476
4477 if ((p_attribute_code is null) or
4478 (p_attribute_code = FND_API.G_MISS_CHAR)) then
4479 l_error := TRUE;
4480 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4481 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4482 FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_CODE');
4483 FND_MSG_PUB.Add;
4484 end if;
4485 end if;
4486
4487 -- - Check that the parent unique key exists, and retrieve
4488 -- the database object name for checking of valid column name below
4489 open l_check_pk_csr;
4490 fetch l_check_pk_csr into l_database_object_name;
4491 if (l_check_pk_csr%notfound) then
4492 close l_check_pk_csr;
4493 l_error := TRUE;
4494 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4495 FND_MESSAGE.SET_NAME('AK','AK_INVALID_UK_REFERENCE');
4496 FND_MESSAGE.SET_TOKEN('KEY', p_unique_key_name);
4497 FND_MSG_PUB.Add;
4498 end if;
4499 else
4500 close l_check_pk_csr;
4501 -- - verify that the column attribute is a valid object attribute
4502 if (NOT AK_OBJECT_PVT.ATTRIBUTE_EXISTS (
4503 p_api_version_number => 1.0,
4504 p_return_status => l_return_status,
4505 p_database_object_name => l_database_object_name,
4506 p_attribute_application_id => p_attribute_application_id,
4507 p_attribute_code => p_attribute_code) ) then
4508 l_error := TRUE;
4512 ' ' || to_char(p_attribute_application_id) ||
4509 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4510 FND_MESSAGE.SET_NAME('AK','AK_INVALID_OA_REFERENCE');
4511 FND_MESSAGE.SET_TOKEN('KEY', l_database_object_name ||
4513 ' ' || p_attribute_code);
4514 FND_MSG_PUB.Add;
4515 end if;
4516 end if;
4517 end if;
4518
4519 --** check that required columns are not null and, unless calling **
4520 --** from UPDATE procedure, the columns are not missing **
4521 if ((p_unique_key_sequence is null) or
4522 (p_unique_key_sequence = FND_API.G_MISS_NUM and
4523 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE))
4524 then
4525 l_error := TRUE;
4526 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4527 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4528 FND_MESSAGE.SET_TOKEN('COLUMN', 'UNIQUE_KEY_SEQUENCE');
4529 FND_MSG_PUB.Add;
4530 end if;
4531 end if;
4532
4533 --** check that the unique_key_sequence should be unique within *
4534 --** the same unique key *
4535 open l_check_seq_csr;
4536 fetch l_check_seq_csr into l_dummy;
4537 if (l_check_seq_csr%found) then
4538 close l_check_seq_csr;
4539 l_error := TRUE;
4540 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4541 FND_MESSAGE.SET_NAME('AK','AK_UNIQUE_UNIQUE_SEQ');
4542 FND_MESSAGE.SET_TOKEN('SEQUENCE', to_char(p_unique_key_sequence) );
4543 FND_MESSAGE.SET_TOKEN('KEY', p_unique_key_name);
4544 FND_MSG_PUB.Add;
4545 end if;
4546 else
4547 close l_check_seq_csr;
4548 end if;
4549
4550 -- return true if no error, false otherwise
4551 p_return_status := FND_API.G_RET_STS_SUCCESS;
4552 return (not l_error);
4553
4554 EXCEPTION
4555 WHEN FND_API.G_EXC_ERROR THEN
4556 p_return_status := FND_API.G_RET_STS_ERROR;
4557 return FALSE;
4558 WHEN OTHERS THEN
4559 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4560 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4561 SUBSTR (SQLERRM, 1, 240) );
4562 FND_MSG_PUB.Add;
4563 return FALSE;
4564
4565 end VALIDATE_UNIQUE_KEY_COLUMN;
4566
4567
4568 --=======================================================
4569 -- Procedure CHECK_FOREIGN_KEY_SEQUENCE
4570 --
4571 -- Usage Private API for checking for the existence of
4572 -- a foreign key column record with the given foreign_
4573 -- key_name and foreign_key_sequence. If such a record
4574 -- exists but has attribute_codes or attribute_application
4575 -- id values different from the parameters, the record
4576 -- will be deleted
4577 -- owned by the Core Modules Team (AK).
4578 --
4579 -- Desc This API check to see if a foreign key column record
4580 -- exists with the given key values.
4581 --
4582 -- Results The API returns the standard p_return_status parameter
4583 -- indicating one of the standard return statuses :
4584 -- * Unexpected error
4585 -- * Error
4586 -- * Success
4587 -- Parameters Foreign Key Column key columns
4588 --
4589 -- Version Initial version number = 1.0
4590 -- History Current version number = 1.0
4591 --=======================================================
4592 procedure CHECK_FOREIGN_KEY_SEQUENCE (
4593 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4594 p_api_version_number IN NUMBER,
4595 p_return_status OUT NOCOPY VARCHAR2,
4596 p_foreign_key_name IN VARCHAR2,
4597 p_attribute_application_id IN NUMBER,
4598 p_attribute_code IN VARCHAR2,
4599 p_foreign_key_sequence IN NUMBER
4600 ) is
4601 cursor l_check_csr is
4602 select ATTRIBUTE_CODE, ATTRIBUTE_APPLICATION_ID
4603 from AK_FOREIGN_KEY_COLUMNS
4604 where FOREIGN_KEY_NAME = p_foreign_key_name
4605 and FOREIGN_KEY_SEQUENCE = p_foreign_key_sequence;
4606 l_api_version_number CONSTANT number := 1.0;
4607 l_api_name CONSTANT varchar2(30) := 'Cbeck_Foreign_Key_Sequence';
4608 l_attribute_code varchar2(30);
4609 l_attribute_application_id number;
4610 l_done boolean := FALSE;
4611 l_return_status varchar(1);
4612 l_msg_data VARCHAR2(2000);
4613 l_msg_count number;
4614 begin
4615 open l_check_csr;
4616 loop
4617 fetch l_check_csr into l_attribute_code, l_attribute_application_id;
4618 if (l_check_csr%notfound) then
4619 close l_check_csr;
4620 p_return_status := FND_API.G_RET_STS_SUCCESS;
4621 exit;
4622 else
4623 if (l_attribute_code <> p_attribute_code) or
4624 (l_attribute_application_id <> p_attribute_application_id) then
4625 --
4626 -- Delete the record in foreign_key_column that has the same
4627 -- foreign_key_name and foreign_key_sequence, but has different
4628 -- attribute_code or attribute_application_id
4629 --
4630 AK_KEY_PVT.DELETE_FOREIGN_KEY_COLUMN (
4631 p_validation_level => p_validation_level,
4632 p_api_version_number => 1.0,
4633 p_msg_count => l_msg_count,
4634 p_msg_data => l_msg_data,
4635 p_return_status => l_return_status,
4636 p_foreign_key_name => p_foreign_key_name,
4640 );
4637 p_attribute_application_id => l_attribute_application_id,
4638 p_attribute_code => l_attribute_code,
4639 p_delete_cascade => 'Y'
4641 if (l_return_status = FND_API.G_RET_STS_ERROR) or
4642 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
4643 close l_check_csr;
4644 raise FND_API.G_EXC_ERROR;
4645 end if;
4646 end if; -- /* if l_attribute_code */
4647 end if; -- /* if l_check_csr%notfound */
4648 end loop;
4649 if l_check_csr%isopen then
4650 close l_check_csr;
4651 end if;
4652
4653 EXCEPTION
4654 WHEN FND_API.G_EXC_ERROR THEN
4655 p_return_status := FND_API.G_RET_STS_ERROR;
4656 WHEN OTHERS THEN
4657 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4658 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4659 SUBSTR (SQLERRM, 1, 240) );
4660 FND_MSG_PUB.Add;
4661
4662 END CHECK_FOREIGN_KEY_SEQUENCE;
4663
4664
4665 --=======================================================
4666 -- Procedure DELETE_RELATED_FOREIGN_KEY_COL
4667 --
4668 -- Usage Private API for deleting foreign key columns.
4669 -- This API should only be called by other APIs
4670 -- that are owned by the Core Modules Team (AK).
4671 --
4672 -- Desc This API deletes foreign key columns from a
4673 -- given foreign_key_name.
4674 --
4675 -- Results The API returns the standard p_return_status parameter
4676 -- indicating one of the standard return statuses :
4677 -- * Unexpected error
4678 -- * Error
4679 -- * Success
4680 -- Parameters Foreign Key columns
4681 --
4682 -- Version Initial version number = 1.0
4683 -- History Current version number = 1.0
4684 --=======================================================
4685 procedure DELETE_RELATED_FOREIGN_KEY_COL (
4686 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4687 p_api_version_number IN NUMBER,
4688 p_msg_count OUT NOCOPY NUMBER,
4689 p_msg_data OUT NOCOPY VARCHAR2,
4690 p_return_status OUT NOCOPY VARCHAR2,
4691 p_foreign_key_name IN VARCHAR2
4692 ) is
4693
4694 cursor l_get_columns_csr is
4695 select ATTRIBUTE_APPLICATION_ID, ATTRIBUTE_CODE
4696 from AK_FOREIGN_KEY_COLUMNS
4697 where FOREIGN_KEY_NAME = p_foreign_key_name;
4698
4699 l_api_name CONSTANT varchar2(30):= 'Delete_Related_Foreign_Key_Col';
4700 l_msg_count NUMBER;
4701 l_msg_data VARCHAR2(2000);
4702 l_return_status VARCHAR2(1);
4703 l_attribute_code VARCHAR2(30);
4704 l_attribute_appl_id NUMBER;
4705 i NUMBER := 0;
4706 begin
4707
4708 savepoint start_delete_rel_foreign_col;
4709
4710 for csr_rec in l_get_columns_csr loop
4711 AK_KEY_PVT.DELETE_FOREIGN_KEY_COLUMN (
4712 p_validation_level => p_validation_level,
4713 p_api_version_number => 1.0,
4714 p_msg_count => l_msg_count,
4715 p_msg_data => l_msg_data,
4716 p_return_status => l_return_status,
4717 p_foreign_key_name => p_foreign_key_name,
4718 p_attribute_application_id => csr_rec.attribute_application_id,
4719 p_attribute_code => csr_rec.attribute_code,
4720 p_delete_cascade => 'Y'
4721 );
4722 if (l_return_status = FND_API.G_RET_STS_ERROR) or
4723 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
4724 l_attribute_code := csr_rec.attribute_code;
4725 l_attribute_appl_id := csr_rec.attribute_application_id;
4726 raise FND_API.G_EXC_ERROR;
4727 end if;
4728 end loop;
4729
4730 p_return_status := FND_API.G_RET_STS_SUCCESS;
4731
4732 EXCEPTION
4733 WHEN FND_API.G_EXC_ERROR THEN
4734 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4735 FND_MESSAGE.SET_NAME('AK','AK_FK_COLUMN_NOT_DELETED');
4736 FND_MESSAGE.SET_TOKEN('KEY',p_foreign_key_name ||
4737 ' ' || to_char(l_attribute_appl_id) ||
4738 ' ' || l_attribute_code);
4739 FND_MSG_PUB.Add;
4740 end if;
4741 p_return_status := FND_API.G_RET_STS_ERROR;
4742 rollback to start_delete_rel_foreign_col;
4743 FND_MSG_PUB.Count_And_Get (
4744 p_count => p_msg_count,
4745 p_data => p_msg_data);
4746 WHEN OTHERS THEN
4747 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4748 -- dbms_output.put_line('Unexpected error:'||substr(SQLERRM,1,240));
4749 rollback to start_delete_rel_foreign_col;
4750 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4751 SUBSTR (SQLERRM, 1, 240) );
4752 FND_MSG_PUB.Add;
4753 FND_MSG_PUB.Count_And_Get (
4754 p_count => p_msg_count,
4755 p_data => p_msg_data);
4756 end DELETE_RELATED_FOREIGN_KEY_COL;
4757
4758 --=======================================================
4759 -- Procedure DELETE_RELATED_UNIQUE_KEY_COL
4760 --
4761 -- Usage Private API for deleting foreign key columns.
4762 -- This API should only be called by other APIs
4763 -- that are owned by the Core Modules Team (AK).
4764 --
4765 -- Desc This API deletes foreign key columns from a
4766 -- given foreign_key_name.
4767 --
4768 -- Results The API returns the standard p_return_status parameter
4769 -- indicating one of the standard return statuses :
4770 -- * Unexpected error
4771 -- * Error
4772 -- * Success
4773 -- Parameters Foreign Key columns
4774 --
4775 -- Version Initial version number = 1.0
4776 -- History Current version number = 1.0
4777 --=======================================================
4778 procedure DELETE_RELATED_UNIQUE_KEY_COL (
4779 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4780 p_api_version_number IN NUMBER,
4781 p_msg_count OUT NOCOPY NUMBER,
4782 p_msg_data OUT NOCOPY VARCHAR2,
4783 p_return_status OUT NOCOPY VARCHAR2,
4784 p_unique_key_name IN VARCHAR2
4785 ) is
4786
4787 cursor l_get_columns_csr is
4788 select ATTRIBUTE_APPLICATION_ID, ATTRIBUTE_CODE
4789 from AK_UNIQUE_KEY_COLUMNS
4790 where UNIQUE_KEY_NAME = p_unique_key_name;
4791
4792 l_api_name CONSTANT varchar2(30):= 'Delete_Related_Unique_Key_Col';
4793 l_msg_count NUMBER;
4794 l_msg_data VARCHAR2(2000);
4795 l_return_status VARCHAR2(1);
4796 l_attribute_code VARCHAR2(30);
4797 l_attribute_appl_id NUMBER;
4798 begin
4799
4800 savepoint start_delete_rel_unique_col;
4801
4802 for csr_rec in l_get_columns_csr loop
4803 --
4804 -- p_override flag is set to 'Y' here so that only AK_UNIQUE_KEY_COLUMNS
4805 -- get deleted, other references would not be deleted and would not be
4806 -- checked.
4807 AK_KEY_PVT.DELETE_UNIQUE_KEY_COLUMN (
4808 p_validation_level => p_validation_level,
4809 p_api_version_number => 1.0,
4810 p_msg_count => l_msg_count,
4811 p_msg_data => l_msg_data,
4812 p_return_status => l_return_status,
4813 p_unique_key_name => p_unique_key_name,
4814 p_attribute_application_id => csr_rec.attribute_application_id,
4815 p_attribute_code => csr_rec.attribute_code,
4816 p_delete_cascade => 'N',
4817 p_override => 'Y'
4818 );
4819
4820 if (l_return_status = FND_API.G_RET_STS_ERROR) or
4821 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
4822 l_attribute_code := csr_rec.attribute_code;
4823 l_attribute_appl_id := csr_rec.attribute_application_id;
4824 raise FND_API.G_EXC_ERROR;
4825 end if;
4826 end loop;
4827
4828 p_return_status := FND_API.G_RET_STS_SUCCESS;
4829
4830 EXCEPTION
4831 WHEN FND_API.G_EXC_ERROR THEN
4832 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4833 FND_MESSAGE.SET_NAME('AK','AK_UK_COLUMN_NOT_DELETED');
4834 FND_MESSAGE.SET_TOKEN('OBJECT', 'AK_UNIQUE_KEY_COLUMN',TRUE);
4835 FND_MESSAGE.SET_TOKEN('KEY',p_unique_key_name ||
4836 ' ' || to_char(l_attribute_appl_id) ||
4837 ' ' || l_attribute_code);
4838 FND_MSG_PUB.Add;
4839 end if;
4840 p_return_status := FND_API.G_RET_STS_ERROR;
4841 rollback to start_delete_rel_unique_col;
4842 FND_MSG_PUB.Count_And_Get (
4843 p_count => p_msg_count,
4844 p_data => p_msg_data);
4845 WHEN OTHERS THEN
4846 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4847 rollback to start_delete_rel_unique_col;
4848 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4849 SUBSTR (SQLERRM, 1, 240) );
4850 FND_MSG_PUB.Add;
4851 FND_MSG_PUB.Count_And_Get (
4852 p_count => p_msg_count,
4853 p_data => p_msg_data);
4854 end DELETE_RELATED_UNIQUE_KEY_COL;
4855
4856 end AK_KEY_PVT;
[Home] [Help]