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