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