[Home] [Help]
PACKAGE BODY: APPS.AK_ATTRIBUTE_PVT
Source
1 package body AK_Attribute_pvt as
2 /* $Header: akdvatrb.pls 120.5.12020000.2 2012/11/20 18:59:45 tshort ship $ */
3
4 --=======================================================
5 -- Function VALIDATE_ATTRIBUTE
6 --
7 -- Usage Private API for validating an attribute. This
8 -- API should only be called by other APIs that are
9 -- owned by the Core Modules Team (AK).
10 --
11 -- Desc Perform validation on an attribute record.
12 --
13 -- Results The API returns the standard p_return_status parameter
14 -- indicating one of the standard return statuses :
15 -- * Unexpected error
16 -- * Error
17 -- * Success
18 -- In addition, this function returns TRUE if all
19 -- validation tests are passed, or FALSE otherwise.
20 -- Parameters Attribute columns
21 -- p_caller : IN required
22 -- Must be one of the following values defined
23 -- in package AK_ON_OBJECTS_PVT:
24 -- - G_CREATE (if calling from the Create API)
25 -- - G_DOWNLOAD (if calling from the Download API)
26 -- - G_UPDATE (if calling from the Update API)
27 --
28 -- Note This API is intended for performing record-level
29 -- validation. It is not designed for item-level
30 -- validation.
31 --
32 -- Version Initial version number = 1.0
33 -- History Current version number = 1.0
34 --=======================================================
35 function VALIDATE_ATTRIBUTE (
36 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
37 p_api_version_number IN NUMBER,
38 p_return_status OUT NOCOPY VARCHAR2,
39 p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
40 p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
41 p_attribute_label_length IN NUMBER := FND_API.G_MISS_NUM,
42 p_attribute_value_length IN NUMBER := FND_API.G_MISS_NUM,
43 p_bold IN VARCHAR2 := FND_API.G_MISS_CHAR,
44 p_italic IN VARCHAR2 := FND_API.G_MISS_CHAR,
45 p_vertical_alignment IN VARCHAR2 := FND_API.G_MISS_CHAR,
46 p_horizontal_alignment IN VARCHAR2 := FND_API.G_MISS_CHAR,
47 p_data_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
48 p_upper_case_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
49 p_default_value_varchar2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
50 p_default_value_number IN NUMBER := FND_API.G_MISS_NUM,
51 p_default_value_date IN DATE := FND_API.G_MISS_DATE,
52 p_lov_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
53 p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
54 p_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
55 p_attribute_label_long IN VARCHAR2 := FND_API.G_MISS_CHAR,
56 p_attribute_label_short IN VARCHAR2 := FND_API.G_MISS_CHAR,
57 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
58 p_caller IN VARCHAR2,
59 p_pass IN NUMBER := 2
60 ) return BOOLEAN is
61 l_api_version_number CONSTANT number := 1.0;
62 l_api_name CONSTANT varchar2(30) := 'Validate_Attribute';
63 l_error BOOLEAN;
64 l_return_status VARCHAR2(1);
65 begin
66 IF NOT FND_API.Compatible_API_Call (
67 l_api_version_number, p_api_version_number, l_api_name,
68 G_PKG_NAME) then
69 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
70 return FALSE;
71 END IF;
72
73 l_error := FALSE;
74 --
75 -- if validation level is none, no validation is necessary
76 --
77 if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
78 p_return_status := FND_API.G_RET_STS_SUCCESS;
79 return TRUE;
80 end if;
81
82 --
83 -- check that key columns are not null and not missing
84 --
85 if ((p_attribute_application_id is null) or
86 (p_attribute_application_id = FND_API.G_MISS_NUM)) then
87 l_error := TRUE;
88 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
89 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
90 FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_APPLICATION_ID');
91 FND_MSG_PUB.Add;
92 end if;
93 end if;
94
95 if ((p_attribute_code is null) or
96 (p_attribute_code = FND_API.G_MISS_CHAR)) then
97 l_error := TRUE;
98 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
99 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
100 FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_CODE');
101 FND_MSG_PUB.Add;
102 end if;
103 end if;
104
105 /*** check that required columns are not null and, unless calling ***/
106 /*** from UPDATE procedure, the columns are not missing ***/
107 if ((p_bold is null) or
108 (p_bold = FND_API.G_MISS_CHAR and
109 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
110 l_error := TRUE;
111 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
112 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
113 FND_MESSAGE.SET_TOKEN('COLUMN', 'BOLD');
114 FND_MSG_PUB.Add;
115 end if;
116 end if;
117
118 if ((p_italic is null) or
119 (p_italic = FND_API.G_MISS_CHAR and
120 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
121 l_error := TRUE;
122 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
123 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
124 FND_MESSAGE.SET_TOKEN('COLUMN', 'ITALIC');
125 FND_MSG_PUB.Add;
126 end if;
127 end if;
128
129 if ((p_vertical_alignment is null) or
130 (p_vertical_alignment = FND_API.G_MISS_CHAR and
131 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE))
132 then
133 l_error := TRUE;
134 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
135 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
136 FND_MESSAGE.SET_TOKEN('COLUMN', 'VERTICAL_ALIGNMENT');
137 FND_MSG_PUB.Add;
138 end if;
139 end if;
140
141 if ((p_horizontal_alignment is null) or
142 (p_horizontal_alignment = FND_API.G_MISS_CHAR and
143 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE))
144 then
145 l_error := TRUE;
146 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
147 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
148 FND_MESSAGE.SET_TOKEN('COLUMN', 'HORIZONTAL_ALIGNMENT');
149 FND_MSG_PUB.Add;
150 end if;
151 end if;
152
153 if ((p_data_type is null) or
154 (p_data_type = FND_API.G_MISS_CHAR and
155 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
156 l_error := TRUE;
157 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
158 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
159 FND_MESSAGE.SET_TOKEN('COLUMN', 'DATA_TYPE');
160 FND_MSG_PUB.Add;
161 end if;
162 end if;
163
164 if ((p_name is null) or
165 (p_name = FND_API.G_MISS_CHAR and
166 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
167 l_error := TRUE;
168 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
169 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
170 FND_MESSAGE.SET_TOKEN('COLUMN', 'NAME');
171 FND_MSG_PUB.Add;
172 end if;
173 end if;
174
175 --*** Validate columns ***
176
177 -- - application ID
178 if (p_attribute_application_id <> FND_API.G_MISS_NUM) then
179 if (NOT AK_ON_OBJECTS_PVT.VALID_APPLICATION_ID (
180 p_api_version_number => 1.0,
181 p_return_status => l_return_status,
182 p_application_id => p_attribute_application_id)
183 ) then
184 l_error := TRUE;
185 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
186 FND_MESSAGE.SET_NAME('AK','AK_INVALID_COLUMN_VALUE');
187 FND_MESSAGE.SET_TOKEN('COLUMN','ATTRIBUTE_APPLICATION_ID');
188 FND_MSG_PUB.Add;
189 end if;
190 end if;
191 end if;
192
193 -- - data type
194 if (p_data_type <> FND_API.G_MISS_CHAR) then
195 if (NOT AK_ON_OBJECTS_PVT.VALID_LOOKUP_CODE (
196 p_api_version_number => 1.0,
197 p_return_status => l_return_status,
198 p_lookup_type => 'DATA_TYPE',
199 p_lookup_code => p_data_type) ) then
200 l_error := TRUE;
201 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
202 FND_MESSAGE.SET_NAME('AK','AK_INVALID_COLUMN_VALUE');
203 FND_MESSAGE.SET_TOKEN('COLUMN','DATA_TYPE');
204 FND_MSG_PUB.Add;
205 end if;
206 end if;
207 end if;
208
209 -- - bold
210 if (p_bold <> FND_API.G_MISS_CHAR) then
211 if (NOT AK_ON_OBJECTS_PVT.VALID_YES_NO(p_bold)) then
212 l_error := TRUE;
213 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
214 FND_MESSAGE.SET_NAME('AK','AK_VALUE_NOT_YES_NO');
215 FND_MESSAGE.SET_TOKEN('COLUMN','BOLD');
216 FND_MSG_PUB.Add;
217 end if;
218 end if;
219 end if;
220
221 -- - upper_case_flag
222 if (p_upper_case_flag <> FND_API.G_MISS_CHAR) then
223 if (NOT AK_ON_OBJECTS_PVT.VALID_YES_NO(p_upper_case_flag)) then
224 l_error := TRUE;
225 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
226 FND_MESSAGE.SET_NAME('AK','AK_VALUE_NOT_YES_NO');
227 FND_MESSAGE.SET_TOKEN('COLUMN','UPPER_CASE_FLAG');
228 FND_MSG_PUB.Add;
229 end if;
230 end if;
231 end if;
232
233 -- - italic
234 if (p_italic <> FND_API.G_MISS_CHAR) then
235 if (NOT AK_ON_OBJECTS_PVT.VALID_YES_NO(p_italic)) then
236 l_error := TRUE;
237 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
238 FND_MESSAGE.SET_NAME('AK','AK_VALUE_NOT_YES_NO');
239 FND_MESSAGE.SET_TOKEN('COLUMN','ITALIC');
240 FND_MSG_PUB.Add;
241 end if;
242 end if;
243 end if;
244
245 -- - vertical alignment
246 if (p_vertical_alignment <> FND_API.G_MISS_CHAR) then
247 if (NOT AK_ON_OBJECTS_PVT.VALID_LOOKUP_CODE (
248 p_api_version_number => 1.0,
249 p_return_status => l_return_status,
250 p_lookup_type => 'VERTICAL_ALIGNMENT',
251 p_lookup_code => p_vertical_alignment)) then
252 l_error := TRUE;
253 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
254 FND_MESSAGE.SET_NAME('AK','AK_INVALID_COLUMN_VALUE');
255 FND_MESSAGE.SET_TOKEN('COLUMN','VERTICAL_ALIGNMENT');
256 FND_MSG_PUB.Add;
257 end if;
258 end if;
259 end if;
260
261 /* - horizontal alignment */
262 if (p_horizontal_alignment <> FND_API.G_MISS_CHAR) then
263 if (NOT AK_ON_OBJECTS_PVT.VALID_LOOKUP_CODE (
264 p_api_version_number => 1.0,
265 p_return_status => l_return_status,
266 p_lookup_type => 'HORIZONTAL_ALIGNMENT',
267 p_lookup_code => p_horizontal_alignment)) then
268 l_error := TRUE;
269 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
270 FND_MESSAGE.SET_NAME('AK','AK_INVALID_COLUMN_VALUE');
271 FND_MESSAGE.SET_TOKEN('COLUMN','HORIZONTAL_ALIGNMENT');
272 FND_MSG_PUB.Add;
273 end if;
274 -- dbms_output.put_line('Invalid Horizontal Alignment value');
275 end if;
276 end if;
277
278 -- - lov_region_application_id and lov_region_code
279 if ( (p_lov_region_application_id <> FND_API.G_MISS_NUM) and
280 (p_lov_region_application_id is not null) ) or
281 ( (p_lov_region_code <> FND_API.G_MISS_CHAR) and
282 (p_lov_region_code is not null) )then
283 if (NOT AK_REGION_PVT.REGION_EXISTS (
284 p_api_version_number => 1.0,
285 p_return_status => l_return_status,
286 p_region_application_id => p_lov_region_application_id,
287 p_region_code => p_lov_region_code)) then
288 l_error := TRUE;
289 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
290 FND_MESSAGE.SET_NAME('AK','AK_LOV_REG_DOES_NOT_EXIST');
291 FND_MESSAGE.SET_TOKEN('KEY',to_char(p_lov_region_application_id)||' '||p_lov_region_code);
292 FND_MSG_PUB.Add;
293 end if;
294 end if;
295 end if;
296
297 /* return true if no error, false otherwise */
298 p_return_status := FND_API.G_RET_STS_SUCCESS;
299 return (not l_error);
300
301 EXCEPTION
302 WHEN FND_API.G_EXC_ERROR THEN
303 p_return_status := FND_API.G_RET_STS_ERROR;
304 return FALSE;
305 WHEN OTHERS THEN
306 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
307 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
308 SUBSTR (SQLERRM, 1, 240) );
309 FND_MSG_PUB.Add;
310 return FALSE;
311
312 end VALIDATE_ATTRIBUTE;
313
314 --=======================================================
315 -- Function ATTRIBUTE_EXISTS
316 --
317 -- Usage Private API for checking for the existence of
318 -- an attribute with the given key values. This
319 -- API should only be called by other APIs that are
320 -- owned by the Core Modules Team (AK).
321 --
322 -- Desc This API check to see if an attribute record
323 -- exists with the given key values.
324 --
325 -- Results The API returns the standard p_return_status parameter
326 -- indicating one of the standard return statuses :
327 -- * Unexpected error
328 -- * Error
329 -- * Success
330 -- This function will return TRUE if such an attribute
331 -- exists, or FALSE otherwise.
332 -- Parameters Attribute key columns
333 --
334 -- Version Initial version number = 1.0
335 -- History Current version number = 1.0
336 --=======================================================
337 function ATTRIBUTE_EXISTS (
338 p_api_version_number IN NUMBER,
339 p_return_status OUT NOCOPY VARCHAR2,
340 p_attribute_application_id IN NUMBER,
341 p_attribute_code IN VARCHAR2
342 ) return BOOLEAN is
343 cursor l_check_csr is
344 select 1
345 from AK_ATTRIBUTES
346 where attribute_application_id = p_attribute_application_id
347 and attribute_code = p_attribute_code;
348 l_api_version_number CONSTANT number := 1.0;
349 l_api_name CONSTANT varchar2(30) := 'Attribute_Exists';
350 l_dummy number;
351 begin
352 IF NOT FND_API.Compatible_API_Call (
353 l_api_version_number, p_api_version_number, l_api_name,
354 G_PKG_NAME) then
355 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
356 return FALSE;
357 END IF;
358
359 open l_check_csr;
360 fetch l_check_csr into l_dummy;
361 if (l_check_csr%notfound) then
362 close l_check_csr;
363 p_return_status := FND_API.G_RET_STS_SUCCESS;
364 return FALSE;
365 else
366 close l_check_csr;
367 p_return_status := FND_API.G_RET_STS_SUCCESS;
368 return TRUE;
369 end if;
370
371 EXCEPTION
372 WHEN FND_API.G_EXC_ERROR THEN
373 p_return_status := FND_API.G_RET_STS_ERROR;
374 return FALSE;
375 WHEN OTHERS THEN
376 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
377 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
378 SUBSTR (SQLERRM, 1, 240) );
379 FND_MSG_PUB.Add;
380 return FALSE;
381
382 end ATTRIBUTE_EXISTS;
383
384 --========================================================
385 -- Procedure CREATE_ATTRIBUTE
386 --
387 -- Usage Private API for creating an attribute. This
388 -- API should only be called by other APIs that are
389 -- owned by the Core Modules Team (AK).
390 --
391 -- Desc Creates an attribute using the given info. This
392 -- API should only be called by other APIs that are
393 -- owned by the Core Modules Team (AK).
394 --
395 -- Results The API returns the standard p_return_status parameter
396 -- indicating one of the standard return statuses :
397 -- * Unexpected error
398 -- * Error
399 -- * Success
400 -- Parameters Attribute columns
401 -- p_loader_timestamp : IN optional
402 -- If a timestamp is passed, the API will create the
403 -- record using this timestamp. Only the upload API
404 -- should call with this parameter loaded.
405 -- p_temp_redo_tbl: IN required
406 -- For saving records temporarily to see if it
407 -- fails in first pass of upload. If it does,
408 -- then the record is saved for second pass
409 -- Version Initial version number = 1.0
410 -- History Current version number = 1.0
411 --=======================================================
412 procedure CREATE_ATTRIBUTE (
413 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
414 p_api_version_number IN NUMBER,
415 p_init_msg_tbl IN BOOLEAN := FALSE,
416 p_msg_count OUT NOCOPY NUMBER,
417 p_msg_data OUT NOCOPY VARCHAR2,
418 p_return_status OUT NOCOPY VARCHAR2,
419 p_attribute_application_id IN NUMBER,
420 p_attribute_code IN VARCHAR2,
421 p_attribute_label_length IN NUMBER := FND_API.G_MISS_NUM,
422 p_attribute_value_length IN NUMBER := FND_API.G_MISS_NUM,
423 p_bold IN VARCHAR2,
424 p_italic IN VARCHAR2,
425 p_vertical_alignment IN VARCHAR2,
426 p_horizontal_alignment IN VARCHAR2,
427 p_data_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
428 p_upper_case_flag IN VARCHAR2,
429 p_default_value_varchar2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
430 p_default_value_number IN NUMBER := FND_API.G_MISS_NUM,
431 p_default_value_date IN DATE := FND_API.G_MISS_DATE,
432 p_lov_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
433 p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
434 p_item_style IN VARCHAR2,
435 p_display_height IN NUMBER := FND_API.G_MISS_NUM,
436 p_css_class_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
437 p_poplist_viewobject IN VARCHAR2 := FND_API.G_MISS_CHAR,
438 p_poplist_display_attr IN VARCHAR2 := FND_API.G_MISS_CHAR,
439 p_poplist_value_attr IN VARCHAR2 := FND_API.G_MISS_CHAR,
440 p_css_label_class_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
441 p_precision IN NUMBER := FND_API.G_MISS_NUM,
442 p_expansion IN NUMBER := FND_API.G_MISS_NUM,
443 p_als_max_length IN NUMBER := FND_API.G_MISS_NUM,
444 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
445 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
446 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
447 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
448 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
449 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
450 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
451 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
452 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
453 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
454 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
455 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
456 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
457 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
458 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
459 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
460 p_name IN VARCHAR2,
461 p_attribute_label_long IN VARCHAR2 := FND_API.G_MISS_CHAR,
462 p_attribute_label_short IN VARCHAR2 := FND_API.G_MISS_CHAR,
463 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
464 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
465 p_creation_date IN DATE := FND_API.G_MISS_DATE,
466 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
467 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
468 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
469 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
470 p_pass IN NUMBER,
471 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
472 ) is
473 l_api_version_number CONSTANT number := 1.0;
474 l_api_name CONSTANT varchar2(30) := 'Create_Attribute';
475 l_attribute_label_length NUMBER := null;
476 l_attribute_label_long VARCHAR2(80);
477 l_attribute_label_short VARCHAR2(40);
478 l_attribute_value_length NUMBER := null;
479 l_created_by number;
480 l_creation_date date;
481 l_description VARCHAR2(2000) := null;
482 l_default_value_varchar2 VARCHAR2(240) := null;
483 l_default_value_number number;
484 l_default_value_date date;
485 l_attribute_category VARCHAR2(30);
486 l_attribute1 VARCHAR2(150);
487 l_attribute2 VARCHAR2(150);
488 l_attribute3 VARCHAR2(150);
489 l_attribute4 VARCHAR2(150);
490 l_attribute5 VARCHAR2(150);
491 l_attribute6 VARCHAR2(150);
492 l_attribute7 VARCHAR2(150);
493 l_attribute8 VARCHAR2(150);
494 l_attribute9 VARCHAR2(150);
495 l_attribute10 VARCHAR2(150);
496 l_attribute11 VARCHAR2(150);
497 l_attribute12 VARCHAR2(150);
498 l_attribute13 VARCHAR2(150);
499 l_attribute14 VARCHAR2(150);
500 l_attribute15 VARCHAR2(150);
501 l_lang varchar2(30);
502 l_last_update_date date;
503 l_last_update_login number;
504 l_last_updated_by number;
505 l_lov_region_application_id number;
506 l_lov_region_code VARCHAR2(15);
507 l_return_status varchar2(1);
508 l_upper_case_flag VARCHAR2(1) := null;
509 l_item_style VARCHAR2(30) := 'TEXT';
510 l_display_height number := null;
511 l_css_class_name VARCHAR2(80) := NULL;
512 l_css_label_class_name VARCHAR2(80) := NULL;
513 l_precision number := null;
514 l_expansion number := null;
515 l_als_max_length number := null;
516 l_poplist_viewobject VARCHAR2(240) := null;
517 l_poplist_display_attr VARCHAR2(80) := NULL;
518 l_poplist_value_attr VARCHAR2(80) := NULL;
519
520 begin
521
522 IF NOT FND_API.Compatible_API_Call (
523 l_api_version_number, p_api_version_number, l_api_name,
524 G_PKG_NAME) then
525 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
526 return;
527 END IF;
528
529 -- Initialize the message table if requested.
530
531 if p_init_msg_tbl then
532 FND_MSG_PUB.initialize;
533 end if;
534
535 savepoint start_create_attribute;
536
537 /* TSHORT - 5665840 - new logic to avoid unique constraint error */
538 /* now if we hit that error the exception handling calls update_attribute */
539 /* --
540 -- check to see if row already exists
541 --
542 if AK_ATTRIBUTE_PVT.ATTRIBUTE_EXISTS (
543 p_api_version_number => 1.0,
544 p_return_status => l_return_status,
545 p_attribute_application_id => p_attribute_application_id,
546 p_attribute_code => p_attribute_code) then
547 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
548 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_EXISTS');
549 FND_MSG_PUB.Add;
550 end if;
551 raise FND_API.G_EXC_ERROR;
552 end if; */
553
554 --** create with blank lov region application id, and lov region code
555 --** if calling from the loader **
556 -- (this is because no region records have been loaded
557 -- at the time when the loader is creating attributes)
558 --
559 if (p_loader_timestamp <> FND_API.G_MISS_DATE) then
560 l_lov_region_application_id := null;
561 l_lov_region_code := null;
562 else
563 if (p_lov_region_application_id <> FND_API.G_MISS_NUM) then
564 l_lov_region_application_id := p_lov_region_application_id;
565 end if;
566 if (p_lov_region_code <> FND_API.G_MISS_CHAR) then
567 l_lov_region_code := p_lov_region_code;
568 end if;
569 end if;
570
571 --
572 -- validate table columns passed in
573 --
574 if (p_validation_level <> FND_API.G_VALID_LEVEL_NONE) then
575 --
576 -- If this API is called from the upload_attribute API,
577 -- we will create an attribute with a null lov region if
578 -- the lov region passed is invalid.
579 --
580 if (p_loader_timestamp <> FND_API.G_MISS_DATE) and
581 (p_lov_region_code is not null) and
582 (p_lov_region_code <> FND_API.G_MISS_CHAR) then
583 if NOT AK_REGION_PVT.REGION_EXISTS (
584 p_api_version_number => 1.0,
585 p_return_status => l_return_status,
586 p_region_application_id => p_lov_region_application_id,
587 p_region_code => p_lov_region_code) then
588 l_lov_region_application_id := null;
589 l_lov_region_code := null;
590 end if;
591 end if;
592 --
593 -- Validate all columns passed in
594 --
595 if NOT VALIDATE_ATTRIBUTE(
596 p_validation_level => p_validation_level,
597 p_api_version_number => 1.0,
598 p_return_status => l_return_status,
599 p_attribute_application_id => p_attribute_application_id,
600 p_attribute_code => p_attribute_code,
601 p_attribute_label_length => p_attribute_label_length,
602 p_attribute_value_length => p_attribute_value_length,
603 p_bold => p_bold,
604 p_italic => p_italic,
605 p_vertical_alignment => p_vertical_alignment,
606 p_horizontal_alignment => p_horizontal_alignment,
607 p_data_type => p_data_type,
608 p_upper_case_flag => p_upper_case_flag,
609 p_default_value_varchar2 => p_default_value_varchar2,
610 p_default_value_number => p_default_value_number,
611 p_default_value_date => p_default_value_date,
612 p_lov_region_application_id => l_lov_region_application_id,
613 p_lov_region_code => l_lov_region_code,
614 p_name => p_name,
615 p_attribute_label_long => p_attribute_label_long,
616 p_attribute_label_short => p_attribute_label_short,
617 p_description => p_description,
618 p_caller => AK_ON_OBJECTS_PVT.G_CREATE,
619 p_pass => p_pass
620 ) then
621 -- Do not raise an error if it's the first pass, continue to
622 -- insert the record
623 if (p_pass = 1) then
624 p_copy_redo_flag := TRUE;
625 else
626 raise FND_API.G_EXC_ERROR;
627 end if; -- /* if p_pass */
628 end if; --/* if VALIDATE_ATTRIBUTE */
629 end if; --/* if p_validation_level */
630
631 -- Do not insert if fails validation ie. p_copy_redo_flag = true
632 if (not p_copy_redo_flag) then
633 --
634 -- Load non-required columns if their values are given
635 -- (do not load lov_region_application_id and lov_region_code
636 -- again since they have already been loaded earlier)
637 --
638 if (p_attribute_label_length <> FND_API.G_MISS_NUM) then
639 l_attribute_label_length := p_attribute_label_length;
640 end if;
641
642 if (p_attribute_value_length <> FND_API.G_MISS_NUM) then
643 l_attribute_value_length := p_attribute_value_length;
644 end if;
645
646 if (p_upper_case_flag <> FND_API.G_MISS_CHAR) then
647 l_upper_case_flag := p_upper_case_flag;
648 end if;
649
650 if (p_default_value_varchar2 <> FND_API.G_MISS_CHAR) then
651 l_default_value_varchar2 := p_default_value_varchar2;
652 end if;
653
654 if (p_default_value_number <> FND_API.G_MISS_NUM) then
655 l_default_value_number := p_default_value_number;
656 end if;
657
658 if (p_default_value_date <> FND_API.G_MISS_DATE) then
659 l_default_value_date := p_default_value_date;
660 end if;
661
662 -- Load non-required JSP columns
663 if (p_item_style <> FND_API.G_MISS_CHAR and p_item_style is not null) then
664 l_item_style := p_item_style;
665 end if;
666
667 if (p_display_height <> FND_API.G_MISS_NUM) then
668 l_display_height := p_display_height;
669 end if;
670
671 if (p_css_class_name <> FND_API.G_MISS_CHAR) then
672 l_css_class_name := p_css_class_name;
673 end if;
674
675 if (p_poplist_viewobject <> FND_API.G_MISS_CHAR) then
676 l_poplist_viewobject := p_poplist_viewobject;
677 end if;
678
679 if (p_poplist_display_attr <> FND_API.G_MISS_CHAR) then
680 l_poplist_display_attr := p_poplist_display_attr;
681 end if;
682
683 if (p_poplist_value_attr <> FND_API.G_MISS_CHAR) then
684 l_poplist_value_attr := p_poplist_value_attr;
685 end if;
686
687 if (p_css_label_class_name <> FND_API.G_MISS_CHAR) then
688 l_css_label_class_name := p_css_label_class_name;
689 end if;
690
691 if (p_precision <> FND_API.G_MISS_NUM) then
692 l_precision := p_precision;
693 end if;
694
695 if (p_expansion <> FND_API.G_MISS_NUM) then
696 l_expansion := p_expansion;
697 end if;
698
699 if (p_als_max_length <> FND_API.G_MISS_NUM) then
700 l_als_max_length := p_als_max_length;
701 end if;
702
703 -- flex columns
704 if (p_attribute_category <> FND_API.G_MISS_CHAR) then
705 l_attribute_category := p_attribute_category;
706 end if;
707
708 if (p_attribute1 <> FND_API.G_MISS_CHAR) then
709 l_attribute1 := p_attribute1;
710 end if;
711
712 if (p_attribute2 <> FND_API.G_MISS_CHAR) then
713 l_attribute2 := p_attribute2;
714 end if;
715
716 if (p_attribute3 <> FND_API.G_MISS_CHAR) then
717 l_attribute3 := p_attribute3;
718 end if;
719
720 if (p_attribute4 <> FND_API.G_MISS_CHAR) then
721 l_attribute4 := p_attribute4;
722 end if;
723
724 if (p_attribute5 <> FND_API.G_MISS_CHAR) then
725 l_attribute5 := p_attribute5;
726 end if;
727
728 if (p_attribute6 <> FND_API.G_MISS_CHAR) then
729 l_attribute6 := p_attribute6;
730 end if;
731
732 if (p_attribute7 <> FND_API.G_MISS_CHAR) then
733 l_attribute7:= p_attribute7;
734 end if;
735
736 if (p_attribute8 <> FND_API.G_MISS_CHAR) then
737 l_attribute8 := p_attribute8;
738 end if;
739
740 if (p_attribute9 <> FND_API.G_MISS_CHAR) then
741 l_attribute9 := p_attribute9;
742 end if;
743
744 if (p_attribute10 <> FND_API.G_MISS_CHAR) then
745 l_attribute10 := p_attribute10;
746 end if;
747
748 if (p_attribute11 <> FND_API.G_MISS_CHAR) then
749 l_attribute11 := p_attribute11;
750 end if;
751
752 if (p_attribute12 <> FND_API.G_MISS_CHAR) then
753 l_attribute12 := p_attribute12;
754 end if;
755
756 if (p_attribute13 <> FND_API.G_MISS_CHAR) then
757 l_attribute13 := p_attribute13;
758 end if;
759
760 if (p_attribute14 <> FND_API.G_MISS_CHAR) then
761 l_attribute14 := p_attribute14;
762 end if;
763
764 if (p_attribute15 <> FND_API.G_MISS_CHAR) then
765 l_attribute15 := p_attribute15;
766 end if;
767
768 if (p_description <> FND_API.G_MISS_CHAR) then
769 l_description := p_description;
770 end if;
771
772 if (p_attribute_label_long <> FND_API.G_MISS_CHAR) then
773 l_attribute_label_long := p_attribute_label_long;
774 end if;
775
776 if (p_attribute_label_short <> FND_API.G_MISS_CHAR) then
777 l_attribute_label_short := p_attribute_label_short;
778 end if;
779
780 if (p_created_by <> FND_API.G_MISS_NUM) then
781 l_created_by := p_created_by;
782 end if;
783
784 if (p_creation_date <> FND_API.G_MISS_DATE) then
785 l_creation_date := p_creation_date;
786 end if;
787
788 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
789 l_last_updated_by := p_last_updated_by;
790 end if;
791
792 if (p_last_update_date <> FND_API.G_MISS_DATE) then
793 l_last_update_date := p_last_update_date;
794 end if;
795
796 if (p_last_update_login <> FND_API.G_MISS_NUM) then
797 l_last_update_login := p_last_update_login;
798 end if;
799
800 --
801 -- Create record if no validation error was found
802 --
803 -- NOTE - Calling IS_UPDATEABLE for backward compatibility
804 -- old jlt files didn't have who columns and IS_UPDATEABLE
805 -- calls SET_WHO which populates those columns, for later
806 -- jlt files IS_UPDATEABLE will always return TRUE for CREATE
807
808 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
809 p_loader_timestamp => p_loader_timestamp,
810 p_created_by => l_created_by,
811 p_creation_date => l_creation_date,
812 p_last_updated_by => l_last_updated_by,
813 p_db_last_updated_by => null,
814 p_last_update_date => l_last_update_date,
815 p_db_last_update_date => null,
816 p_last_update_login => l_last_update_login,
817 p_create_or_update => 'CREATE') then
818 null;
819 end if;
820
821 -- Gets userenv('LANG') which is the system's current language code
822
823 select userenv('LANG') into l_lang
824 from dual;
825
826 insert into AK_ATTRIBUTES (
827 ATTRIBUTE_APPLICATION_ID,
828 ATTRIBUTE_CODE,
829 ATTRIBUTE_LABEL_LENGTH,
830 ATTRIBUTE_VALUE_LENGTH,
831 BOLD,
832 ITALIC,
833 VERTICAL_ALIGNMENT,
834 HORIZONTAL_ALIGNMENT,
835 DATA_TYPE,
836 UPPER_CASE_FLAG,
837 DEFAULT_VALUE_VARCHAR2,
838 DEFAULT_VALUE_NUMBER,
839 DEFAULT_VALUE_DATE,
840 LOV_REGION_APPLICATION_ID,
841 LOV_REGION_CODE,
842 ITEM_STYLE,
843 DISPLAY_HEIGHT,
844 CSS_CLASS_NAME,
845 POPLIST_VIEWOBJECT,
846 POPLIST_DISPLAY_ATTRIBUTE,
847 POPLIST_VALUE_ATTRIBUTE,
848 CSS_LABEL_CLASS_NAME,
849 PRECISION,
850 EXPANSION,
851 ALS_MAX_LENGTH,
852 ATTRIBUTE_CATEGORY,
853 ATTRIBUTE1,
854 ATTRIBUTE2,
855 ATTRIBUTE3,
856 ATTRIBUTE4,
857 ATTRIBUTE5,
858 ATTRIBUTE6,
859 ATTRIBUTE7,
860 ATTRIBUTE8,
861 ATTRIBUTE9,
862 ATTRIBUTE10,
863 ATTRIBUTE11,
864 ATTRIBUTE12,
865 ATTRIBUTE13,
866 ATTRIBUTE14,
867 ATTRIBUTE15,
868 CREATION_DATE,
869 CREATED_BY,
870 LAST_UPDATE_DATE,
871 LAST_UPDATED_BY,
872 LAST_UPDATE_LOGIN
873 ) values (
874 p_attribute_application_id,
875 p_attribute_code,
876 l_attribute_label_length,
877 l_attribute_value_length,
878 p_bold,
879 p_italic,
880 p_vertical_alignment,
881 p_horizontal_alignment,
882 p_data_type,
883 l_upper_case_flag,
884 l_default_value_varchar2,
885 l_default_value_number,
886 l_default_value_date,
887 l_lov_region_application_id,
888 l_lov_region_code,
889 l_item_style,
890 l_display_height,
891 l_css_class_name,
892 l_poplist_viewobject,
893 l_poplist_display_attr,
894 l_poplist_value_attr,
895 l_css_label_class_name,
896 l_precision,
897 l_expansion,
898 l_als_max_length,
899 l_attribute_category,
900 l_attribute1,
901 l_attribute2,
902 l_attribute3,
903 l_attribute4,
904 l_attribute5,
905 l_attribute6,
906 l_attribute7,
907 l_attribute8,
908 l_attribute9,
909 l_attribute10,
910 l_attribute11,
911 l_attribute12,
912 l_attribute13,
913 l_attribute14,
914 l_attribute15,
915 l_creation_date,
916 l_created_by,
917 l_last_update_date,
918 l_last_updated_by,
919 l_last_update_login
920 );
921
922 --
923 -- row should exists before inserting rows for other languages
924 --
925 if NOT AK_ATTRIBUTE_PVT.ATTRIBUTE_EXISTS (
926 p_api_version_number => 1.0,
927 p_return_status => l_return_status,
928 p_attribute_application_id => p_attribute_application_id,
929 p_attribute_code => p_attribute_code) then
930 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
931 FND_MESSAGE.SET_NAME('AK','AK_INSERT_ATTRIBUTE_FAILED');
932 FND_MESSAGE.SET_TOKEN('OBJECT','AK_LC_ATTRIBUTE',TRUE);
933 FND_MSG_PUB.Add;
934 end if;
935 raise FND_API.G_EXC_ERROR;
936 end if;
937
938 insert into AK_ATTRIBUTES_TL (
939 ATTRIBUTE_APPLICATION_ID,
940 ATTRIBUTE_CODE,
941 LANGUAGE,
942 NAME,
943 ATTRIBUTE_LABEL_LONG,
944 ATTRIBUTE_LABEL_SHORT,
945 DESCRIPTION,
946 SOURCE_LANG,
947 CREATED_BY,
948 CREATION_DATE,
949 LAST_UPDATED_BY,
950 LAST_UPDATE_DATE,
951 LAST_UPDATE_LOGIN
952 ) select
953 p_attribute_application_id,
954 p_attribute_code,
955 L.LANGUAGE_CODE,
956 p_name,
957 l_attribute_label_long,
958 l_attribute_label_short,
959 l_description,
960 l_lang,
961 l_created_by,
962 l_creation_date,
963 l_last_updated_by,
964 l_last_update_date,
965 l_last_update_login
966 from FND_LANGUAGES L
967 where L.INSTALLED_FLAG in ('I', 'B')
968 and not exists
969 (select NULL
970 from AK_ATTRIBUTES_TL T
971 where T.ATTRIBUTE_APPLICATION_ID = p_attribute_application_id
972 and T.ATTRIBUTE_CODE = p_attribute_code
973 and T.LANGUAGE = L.LANGUAGE_CODE);
974
975 -- /** commit the insert **/
976 commit;
977
978 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
979 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_CREATED');
980 FND_MESSAGE.SET_TOKEN('KEY',to_char(p_attribute_application_id) ||
981 ' ' || p_attribute_code);
982 FND_MSG_PUB.Add;
983 end if;
984
985 end if; -- /* if not p_copy_redo_flag */
986
987 p_return_status := FND_API.G_RET_STS_SUCCESS;
988
989 FND_MSG_PUB.Count_And_Get (
990 p_count => p_msg_count,
991 p_data => p_msg_data);
992
993
994 EXCEPTION
995 WHEN VALUE_ERROR THEN
996 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
997 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_VALUE_ERROR');
998 FND_MESSAGE.SET_TOKEN('KEY',to_char(p_attribute_application_id) ||
999 ' ' || p_attribute_code);
1000 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_NOT_CREATED');
1001 FND_MESSAGE.SET_TOKEN('KEY',to_char(p_attribute_application_id) ||
1002 ' ' || p_attribute_code);
1003 FND_MSG_PUB.Add;
1004 end if;
1005 p_return_status := FND_API.G_RET_STS_ERROR;
1006 rollback to start_create_attribute;
1007 FND_MSG_PUB.Count_And_Get (
1008 p_count => p_msg_count,
1009 p_data => p_msg_data);
1010 WHEN FND_API.G_EXC_ERROR THEN
1011 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1012 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_NOT_CREATED');
1013 FND_MESSAGE.SET_TOKEN('KEY',to_char(p_attribute_application_id) ||
1014 ' ' || p_attribute_code);
1015 FND_MSG_PUB.Add;
1016 end if;
1017 p_return_status := FND_API.G_RET_STS_ERROR;
1018 rollback to start_create_attribute;
1019 FND_MSG_PUB.Count_And_Get (
1020 p_count => p_msg_count,
1021 p_data => p_msg_data);
1022 WHEN OTHERS THEN
1023 if (SQLCODE = -1) then
1024 rollback to start_create_attribute;
1025 AK_ATTRIBUTE_PVT.UPDATE_ATTRIBUTE (
1026 p_validation_level => p_validation_level,
1027 p_api_version_number => 1.0,
1028 p_msg_count => p_msg_count,
1029 p_msg_data => p_msg_data,
1030 p_return_status => p_return_status,
1031 p_attribute_application_id => p_attribute_application_id,
1032 p_attribute_code => p_attribute_code,
1033 p_attribute_label_length => p_attribute_label_length,
1034 p_attribute_value_length => p_attribute_value_length,
1035 p_bold => p_bold,
1036 p_italic => p_italic,
1037 p_vertical_alignment => p_vertical_alignment,
1038 p_horizontal_alignment => p_horizontal_alignment,
1039 p_data_type => p_data_type,
1040 p_precision => p_precision,
1041 p_upper_case_flag => p_upper_case_flag,
1042 p_default_value_varchar2 => p_default_value_varchar2,
1043 p_default_value_number => p_default_value_number,
1044 p_default_value_date => p_default_value_date,
1045 p_lov_region_application_id => p_lov_region_application_id,
1046 p_lov_region_code => p_lov_region_code,
1047 p_item_style => p_item_style,
1048 p_display_height => p_display_height,
1049 p_css_class_name => p_css_class_name,
1050 p_poplist_viewobject => p_poplist_viewobject,
1051 p_poplist_display_attr => p_poplist_display_attr,
1052 p_poplist_value_attr => p_poplist_value_attr,
1053 p_css_label_class_name => p_css_label_class_name,
1054 p_attribute_category => p_attribute_category,
1055 p_expansion => p_expansion,
1056 p_als_max_length => p_als_max_length,
1057 p_attribute1 => p_attribute1,
1058 p_attribute2 => p_attribute2,
1059 p_attribute3 => p_attribute3,
1060 p_attribute4 => p_attribute4,
1061 p_attribute5 => p_attribute5,
1062 p_attribute6 => p_attribute6,
1063 p_attribute7 => p_attribute7,
1064 p_attribute8 => p_attribute8,
1065 p_attribute9 => p_attribute9,
1066 p_attribute10 => p_attribute10,
1067 p_attribute11 => p_attribute11,
1068 p_attribute12 => p_attribute12,
1069 p_attribute13 => p_attribute13,
1070 p_attribute14 => p_attribute14,
1071 p_attribute15 => p_attribute15,
1072 p_created_by => p_created_by,
1073 p_creation_date => p_creation_date,
1074 p_last_updated_by => p_last_updated_by,
1075 p_last_update_date => p_last_update_date,
1076 p_last_update_login => p_last_update_login,
1077 p_name => p_name,
1078 p_attribute_label_long => p_attribute_label_long,
1079 p_attribute_label_short =>p_attribute_label_short,
1080 p_description => p_description,
1081 p_loader_timestamp => p_loader_timestamp,
1082 p_pass => p_pass,
1083 p_copy_redo_flag => p_copy_redo_flag
1084 );
1085 else
1086 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1087 rollback to start_create_attribute;
1088 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name,
1089 SUBSTR (SQLERRM, 1, 240) );
1090 FND_MSG_PUB.Count_And_Get (
1091 p_count => p_msg_count,
1092 p_data => p_msg_data);
1093 end if;
1094 end CREATE_ATTRIBUTE;
1095
1096 --=======================================================
1097 -- Procedure DELETE_ATTRIBUTE
1098 --
1099 -- Usage Private API for deleting an attribute. This
1100 -- API should only be called by other APIs that are
1101 -- owned by the Core Modules Team (AK).
1102 --
1103 -- Desc Deletes an attribute with the given key value.
1104 --
1105 -- Results The API returns the standard p_return_status parameter
1106 -- indicating one of the standard return statuses :
1107 -- * Unexpected error
1108 -- * Error
1109 -- * Success
1110 -- Parameters p_attribute_application_id : IN required
1111 -- p_attribute_code : IN required
1112 -- Key value of the attribute to be deleted.
1113 -- p_delete_cascade : IN required
1114 -- If p_delete_cascade flag is 'Y', also delete all
1115 -- rows in other tables that references this attribute.
1116 -- Otherwise, this attribute will not be deleted if there
1117 -- are any other rows referencing it.
1118 --
1119 -- Version Initial version number = 1.0
1120 -- History Current version number = 1.0
1121 --=======================================================
1122 procedure DELETE_ATTRIBUTE (
1123 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1124 p_api_version_number IN NUMBER,
1125 p_init_msg_tbl IN BOOLEAN := FALSE,
1126 p_msg_count OUT NOCOPY NUMBER,
1127 p_msg_data OUT NOCOPY VARCHAR2,
1128 p_return_status OUT NOCOPY VARCHAR2,
1129 p_attribute_application_id IN NUMBER,
1130 p_attribute_code IN VARCHAR2,
1131 p_delete_cascade IN VARCHAR2
1132 ) is
1133 cursor l_get_obj_attr_csr is
1134 select DATABASE_OBJECT_NAME
1135 from AK_OBJECT_ATTRIBUTES
1136 where ATTRIBUTE_APPLICATION_ID = p_attribute_application_id
1137 and ATTRIBUTE_CODE = p_attribute_code;
1138 cursor l_get_region_item_csr is
1139 select REGION_APPLICATION_ID, REGION_CODE
1140 from AK_REGION_ITEMS
1141 where ATTRIBUTE_APPLICATION_ID = p_attribute_application_id
1142 and ATTRIBUTE_CODE = p_attribute_code
1143 and OBJECT_ATTRIBUTE_FLAG = 'N';
1144 l_api_version_number CONSTANT number := 1.0;
1145 l_api_name CONSTANT varchar2(30) := 'Delete_Attribute';
1146 l_database_object_name VARCHAR2(30);
1147 l_msg_count NUMBER;
1148 l_msg_data VARCHAR2(2000);
1149 l_region_application_id NUMBER;
1150 l_region_code VARCHAR2(30);
1151 l_return_status varchar2(1);
1152 begin
1153
1154 IF NOT FND_API.Compatible_API_Call (
1155 l_api_version_number, p_api_version_number, l_api_name,
1156 G_PKG_NAME) then
1157 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1158 return;
1159 END IF;
1160
1161 -- Initialize the message table if requested.
1162
1163 if p_init_msg_tbl then
1164 FND_MSG_PUB.initialize;
1165 end if;
1166
1167 savepoint start_delete_attribute;
1168
1169 --
1170 -- error if attribute to be deleted does not exists
1171 --
1172 if NOT AK_ATTRIBUTE_PVT.ATTRIBUTE_EXISTS (
1173 p_api_version_number => 1.0,
1174 p_return_status => l_return_status,
1175 p_attribute_application_id => p_attribute_application_id,
1176 p_attribute_code => p_attribute_code) then
1177 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1178 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_DOES_NOT_EXIST');
1179 FND_MSG_PUB.Add;
1180 end if;
1181 raise FND_API.G_EXC_ERROR;
1182 end if;
1183
1184 if (p_delete_cascade = 'N') then
1185 --
1186 -- If we are not deleting any referencing records, we cannot
1187 -- delete the attribute if it is being referenced in any of
1188 -- following tables.
1189 --
1190 -- AK_OBJECT_ATTRIBUTES
1191 --
1192 open l_get_obj_attr_csr;
1193 fetch l_get_obj_attr_csr into l_database_object_name;
1194 if l_get_obj_attr_csr%found then
1195 close l_get_obj_attr_csr;
1196 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1197 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_DEL_REF_ATTR_OA');
1198 FND_MSG_PUB.Add;
1199 end if;
1200 raise FND_API.G_EXC_ERROR;
1201 end if;
1202 close l_get_obj_attr_csr;
1203 --
1204 -- AK_REGION_ITEMS
1205 --
1206 open l_get_region_item_csr;
1207 fetch l_get_region_item_csr into l_region_application_id, l_region_code;
1208 if l_get_region_item_csr%found then
1209 close l_get_region_item_csr;
1210 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1211 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_DEL_REF_ATTR_RI');
1212 FND_MSG_PUB.Add;
1213 end if;
1214 raise FND_API.G_EXC_ERROR;
1215 end if;
1216 close l_get_region_item_csr;
1217
1218 else
1219 --
1220 -- Otherwise, delete all referencing rows in other tables
1221 --
1222 -- AK_OBJECT_ATTRIBUTES
1223 --
1224 open l_get_obj_attr_csr;
1225 loop
1226 fetch l_get_obj_attr_csr into l_database_object_name;
1227 exit when l_get_obj_attr_csr%notfound;
1228 AK_OBJECT_PVT.DELETE_ATTRIBUTE(
1229 p_validation_level => p_validation_level,
1230 p_api_version_number => 1.0,
1231 p_msg_count => l_msg_count,
1232 p_msg_data => l_msg_data,
1233 p_return_status => l_return_status,
1234 p_database_object_name => l_database_object_name,
1235 p_attribute_application_id => p_attribute_application_id,
1236 p_attribute_code => p_attribute_code,
1237 p_delete_cascade => p_delete_cascade
1238 );
1239 if (l_return_status = FND_API.G_RET_STS_ERROR) or
1240 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1241 close l_get_obj_attr_csr;
1242 raise FND_API.G_EXC_ERROR;
1243 end if;
1244 end loop;
1245 close l_get_obj_attr_csr;
1246 --
1247 -- AK_REGION_ITEMS
1248 --
1249 open l_get_region_item_csr;
1250 loop
1251 fetch l_get_region_item_csr into l_region_application_id, l_region_code;
1252 exit when l_get_region_item_csr%notfound;
1253 AK_REGION_PVT.DELETE_ITEM (
1254 p_validation_level => p_validation_level,
1255 p_api_version_number => 1.0,
1256 p_msg_count => l_msg_count,
1257 p_msg_data => l_msg_data,
1258 p_return_status => l_return_status,
1259 p_region_application_id => l_region_application_id,
1260 p_region_code => l_region_code,
1261 p_attribute_application_id => p_attribute_application_id,
1262 p_attribute_code => p_attribute_code,
1263 p_delete_cascade => p_delete_cascade
1264 );
1265 if (l_return_status = FND_API.G_RET_STS_ERROR) or
1266 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1267 close l_get_region_item_csr;
1268 raise FND_API.G_EXC_ERROR;
1269 end if;
1270 end loop;
1271 close l_get_region_item_csr;
1272
1273 end if;
1274
1275 --
1276 -- delete attribute once we checked that there are no references
1277 -- to this attribute, or all references have been deleted.
1278 --
1279 delete from ak_attributes
1280 where attribute_application_id = p_attribute_application_id
1281 and attribute_code = p_attribute_code;
1282
1283 if (sql%notfound) then
1284 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1285 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_DOES_NOT_EXIST');
1286 FND_MSG_PUB.Add;
1287 end if;
1288 raise FND_API.G_EXC_ERROR;
1289 end if;
1290
1291 delete from ak_attributes_tl
1292 where attribute_application_id = p_attribute_application_id
1293 and attribute_code = p_attribute_code;
1294
1295 if (sql%notfound) then
1296 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1297 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_DOES_NOT_EXIST');
1298 FND_MSG_PUB.Add;
1299 end if;
1300 raise FND_API.G_EXC_ERROR;
1301 end if;
1302
1303 --
1304 -- Load success message
1305 --
1306 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) then
1307 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_DELETED');
1308 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_attribute_application_id) ||
1309 ' ' || p_attribute_code);
1310 FND_MSG_PUB.Add;
1311 end if;
1312
1313 p_return_status := FND_API.G_RET_STS_SUCCESS;
1314
1315 FND_MSG_PUB.Count_And_Get (
1316 p_count => p_msg_count,
1317 p_data => p_msg_data);
1318
1319 EXCEPTION
1320 WHEN FND_API.G_EXC_ERROR THEN
1321 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1322 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_NOT_DELETED');
1323 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_attribute_application_id) ||
1324 ' ' || p_attribute_code);
1325 FND_MSG_PUB.Add;
1326 end if;
1327 p_return_status := FND_API.G_RET_STS_ERROR;
1328 rollback to start_delete_attribute;
1329 FND_MSG_PUB.Count_And_Get (
1330 p_count => p_msg_count,
1331 p_data => p_msg_data);
1332 WHEN OTHERS THEN
1333 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1334 rollback to start_delete_attribute;
1335 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name,
1336 SUBSTR (SQLERRM, 1, 240) );
1337 FND_MSG_PUB.Count_And_Get (
1338 p_count => p_msg_count,
1339 p_data => p_msg_data);
1340 end DELETE_ATTRIBUTE;
1341
1342 --=======================================================
1343 -- Procedure WRITE_TO_BUFFER (local procedure)
1344 --
1345 -- Usage Local procedure for writing one attribute to
1346 -- the output file. Not designed to be called
1347 -- from outside this package.
1348 --
1349 -- Desc Appends the single attribute passed in through the
1350 -- parameters to the specified output file. The
1351 -- output will be in loader file format.
1352 --
1353 -- Results The API returns the standard p_return_status parameter
1354 -- indicating one of the standard return statuses :
1355 -- * Unexpected error
1356 -- * Error
1357 -- * Success
1358 -- Parameters Attribute record and its TL record.
1359 --=======================================================
1360 procedure WRITE_TO_BUFFER (
1361 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1362 p_return_status OUT NOCOPY VARCHAR2,
1363 p_attributes_rec IN ak_attributes%ROWTYPE,
1364 p_attributes_tl_rec IN ak_attributes_tl%ROWTYPE
1365 ) is
1366 l_api_name CONSTANT varchar2(30) := 'Write_to_buffer';
1367 l_databuffer_tbl AK_ON_OBJECTS_PUB.Buffer_Tbl_Type;
1368 l_index NUMBER;
1369 l_lov_object VARCHAR2(30);
1370 l_return_status varchar2(1);
1371 begin
1372 --
1373 -- Attribute must be validated before it is written to the file
1374 --
1375 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
1376 if not VALIDATE_ATTRIBUTE (
1377 p_validation_level => p_validation_level,
1378 p_api_version_number => 1.0,
1379 p_return_status => l_return_status,
1380 p_attribute_application_id =>
1381 p_attributes_rec.attribute_application_id,
1382 p_attribute_code => p_attributes_rec.attribute_code,
1383 p_attribute_label_length => p_attributes_rec.attribute_label_length,
1384 p_attribute_value_length => p_attributes_rec.attribute_value_length,
1385 p_bold => p_attributes_rec.bold,
1386 p_italic => p_attributes_rec.italic,
1387 p_vertical_alignment => p_attributes_rec.vertical_alignment,
1388 p_horizontal_alignment => p_attributes_rec.horizontal_alignment,
1389 p_data_type => p_attributes_rec.data_type,
1390 p_upper_case_flag => p_attributes_rec.upper_case_flag,
1391 p_default_value_varchar2 => p_attributes_rec.default_value_varchar2,
1392 p_default_value_number => p_attributes_rec.default_value_number,
1393 p_default_value_date => p_attributes_rec.default_value_date,
1394 p_lov_region_application_id =>
1395 p_attributes_rec.lov_region_application_id,
1396 p_lov_region_code => p_attributes_rec.lov_region_code,
1397 p_name => p_attributes_tl_rec.name,
1398 p_attribute_label_long => p_attributes_tl_rec.attribute_label_long,
1399 p_attribute_label_short => p_attributes_tl_rec.attribute_label_short,
1400 p_description => p_attributes_tl_rec.description,
1401 p_caller => AK_ON_OBJECTS_PVT.G_DOWNLOAD)
1402 then
1403 -- dbms_output.put_line('Attribute ' || p_attributes_rec.attribute_code
1404 -- || ' not downloaded due to validation error');
1405 raise FND_API.G_EXC_ERROR;
1406 end if;
1407 end if;
1408
1409 --
1410 -- Write attribute and its TL record into buffer
1411 --
1412 l_databuffer_tbl.DELETE;
1413 l_index := 1;
1414
1415 l_databuffer_tbl(l_index) := 'BEGIN ATTRIBUTE "' ||
1416 p_attributes_rec.attribute_application_id || '" "' ||
1417 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(p_attributes_rec.attribute_code) || '"';
1418 --
1419 -- only write out columns that is not null
1420 --
1421 if ((p_attributes_rec.attribute_label_length IS NOT NULL) and
1422 (p_attributes_rec.attribute_label_length <> FND_API.G_MISS_NUM)) then
1423 l_index := l_index + 1;
1424 l_databuffer_tbl(l_index) := ' ATTRIBUTE_LABEL_LENGTH = "' ||
1425 nvl(to_char(p_attributes_rec.attribute_label_length),'') || '"';
1426 end if;
1427 if ((p_attributes_rec.attribute_value_length IS NOT NULL) and
1428 (p_attributes_rec.attribute_value_length <> FND_API.G_MISS_NUM)) then
1429 l_index := l_index + 1;
1430 l_databuffer_tbl(l_index) := ' ATTRIBUTE_VALUE_LENGTH = "' ||
1431 nvl(to_char(p_attributes_rec.attribute_value_length),'') || '"';
1432 end if;
1433 if ((p_attributes_rec.bold IS NOT NULL) and
1434 (p_attributes_rec.bold <> FND_API.G_MISS_CHAR)) then
1435 l_index := l_index + 1;
1436 l_databuffer_tbl(l_index) := ' BOLD = "' ||
1437 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(p_attributes_rec.bold) || '"';
1438 end if;
1439 if ((p_attributes_rec.italic IS NOT NULL) and
1440 (p_attributes_rec.italic <> FND_API.G_MISS_CHAR)) then
1441 l_index := l_index + 1;
1442 l_databuffer_tbl(l_index) := ' ITALIC = "' ||
1443 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(p_attributes_rec.italic) || '"';
1444 end if;
1445 if ((p_attributes_rec.vertical_alignment IS NOT NULL) and
1446 (p_attributes_rec.vertical_alignment <> FND_API.G_MISS_CHAR)) then
1447 l_index := l_index + 1;
1448 l_databuffer_tbl(l_index) := ' VERTICAL_ALIGNMENT = "' ||
1449 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(p_attributes_rec.vertical_alignment)
1450 || '"';
1451 end if;
1452 if ((p_attributes_rec.horizontal_alignment IS NOT NULL) and
1453 (p_attributes_rec.horizontal_alignment <> FND_API.G_MISS_CHAR)) then
1454 l_index := l_index + 1;
1455 l_databuffer_tbl(l_index) := ' HORIZONTAL_ALIGNMENT = "' ||
1456 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(p_attributes_rec.horizontal_alignment)
1457 || '"';
1458 end if;
1459 if ((p_attributes_rec.data_type IS NOT NULL) and
1460 (p_attributes_rec.data_type <> FND_API.G_MISS_CHAR)) then
1461 l_index := l_index + 1;
1462 l_databuffer_tbl(l_index) := ' DATA_TYPE = "' ||
1463 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(p_attributes_rec.data_type) || '"';
1464 end if;
1465 if ((p_attributes_rec.upper_case_flag IS NOT NULL) and
1466 (p_attributes_rec.upper_case_flag <> FND_API.G_MISS_CHAR)) then
1467 l_index := l_index + 1;
1468 l_databuffer_tbl(l_index) := ' UPPER_CASE_FLAG = "' ||
1469 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(p_attributes_rec.upper_case_flag)
1470 || '"';
1471 end if;
1472 if ((p_attributes_rec.default_value_varchar2 IS NOT NULL) and
1473 (p_attributes_rec.default_value_varchar2 <> FND_API.G_MISS_CHAR)) then
1474 l_index := l_index + 1;
1475 l_databuffer_tbl(l_index) := ' DEFAULT_VALUE_VARCHAR2 = "' ||
1476 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1477 p_attributes_rec.default_value_varchar2) || '"';
1478 end if;
1479 if ((p_attributes_rec.default_value_number IS NOT NULL) and
1480 (p_attributes_rec.default_value_number <> FND_API.G_MISS_NUM)) then
1481 l_index := l_index + 1;
1482 l_databuffer_tbl(l_index) := ' DEFAULT_VALUE_NUMBER = "' ||
1483 nvl(to_char(p_attributes_rec.default_value_number),'') || '"';
1484 end if;
1485 if ((p_attributes_rec.default_value_date IS NOT NULL) and
1486 (p_attributes_rec.default_value_date <> FND_API.G_MISS_DATE)) then
1487 l_index := l_index + 1;
1488 l_databuffer_tbl(l_index) := ' DEFAULT_VALUE_DATE = "' ||
1489 to_char(p_attributes_rec.default_value_date,
1490 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1491 end if;
1492 if ((p_attributes_rec.lov_region_application_id IS NOT NULL) and
1493 (p_attributes_rec.lov_region_application_id <> FND_API.G_MISS_NUM) and
1494 (p_attributes_rec.lov_region_code IS NOT NULL) and
1495 (p_attributes_rec.lov_region_code <> FND_API.G_MISS_CHAR)) then
1496 l_index := l_index + 1;
1497 l_databuffer_tbl(l_index) := ' LOV_REGION = "' ||
1498 nvl(to_char(p_attributes_rec.lov_region_application_id),'')||'" "'||
1499 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(p_attributes_rec.lov_region_code) ||
1500 '"';
1501 end if;
1502 -- new columns for JSP renderer
1503 if ((p_attributes_rec.item_style IS NOT NULL) and
1504 (p_attributes_rec.item_style <> FND_API.G_MISS_CHAR)) then
1505 l_index := l_index + 1;
1506 l_databuffer_tbl(l_index) := ' ITEM_STYLE = "' ||
1507 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1508 p_attributes_rec.item_style) || '"';
1509 end if;
1510 if ((p_attributes_rec.display_height IS NOT NULL) and
1511 (p_attributes_rec.display_height <> FND_API.G_MISS_NUM)) then
1512 l_index := l_index + 1;
1513 l_databuffer_tbl(l_index) := ' DISPLAY_HEIGHT = "' ||
1514 nvl(to_char(p_attributes_rec.display_height),'') || '"';
1515 end if;
1516 if ((p_attributes_rec.css_class_name IS NOT NULL) and
1517 (p_attributes_rec.css_class_name <> FND_API.G_MISS_CHAR)) then
1518 l_index := l_index + 1;
1519 l_databuffer_tbl(l_index) := ' CSS_CLASS_NAME = "' ||
1520 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1521 p_attributes_rec.css_class_name) || '"';
1522 end if;
1523 if ((p_attributes_rec.poplist_viewobject IS NOT NULL) and
1524 (p_attributes_rec.poplist_viewobject <> FND_API.G_MISS_CHAR)) then
1525 l_index := l_index + 1;
1526 l_databuffer_tbl(l_index) := ' POPLIST_VIEWOBJECT = "' ||
1527 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1528 p_attributes_rec.poplist_viewobject) || '"';
1529 end if;
1530 if ((p_attributes_rec.poplist_display_attribute IS NOT NULL) and
1531 (p_attributes_rec.poplist_display_attribute <> FND_API.G_MISS_CHAR)) then
1532 l_index := l_index + 1;
1533 l_databuffer_tbl(l_index) := ' POPLIST_DISPLAY_ATTRIBUTE = "' ||
1534 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1535 p_attributes_rec.poplist_display_attribute) || '"';
1536 end if;
1537 if ((p_attributes_rec.poplist_value_attribute IS NOT NULL) and
1538 (p_attributes_rec.poplist_value_attribute <> FND_API.G_MISS_CHAR)) then
1539 l_index := l_index + 1;
1540 l_databuffer_tbl(l_index) := ' POPLIST_VALUE_ATTRIBUTE = "' ||
1541 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1542 p_attributes_rec.poplist_value_attribute) || '"';
1543 end if;
1544 if ((p_attributes_rec.css_label_class_name IS NOT NULL ) and
1545 (p_attributes_rec.css_label_class_name <> FND_API.G_MISS_CHAR)) then
1546 l_index := l_index + 1;
1547 l_databuffer_tbl(l_index) := ' CSS_LABEL_CLASS_NAME = "' ||
1548 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1549 p_attributes_rec.css_label_class_name) || '"';
1550 end if;
1551 if ((p_attributes_rec.precision IS NOT NULL ) and
1552 (p_attributes_rec.precision <> FND_API.G_MISS_NUM)) then
1553 l_index := l_index + 1;
1554 l_databuffer_tbl(l_index) := ' PRECISION = "' ||
1555 nvl(to_char(p_attributes_rec.precision),'') || '"';
1556 end if;
1557 if ((p_attributes_rec.expansion IS NOT NULL ) and
1558 (p_attributes_rec.expansion <> FND_API.G_MISS_NUM)) then
1559 l_index := l_index + 1;
1560 l_databuffer_tbl(l_index) := ' EXPANSION = "' ||
1561 nvl(to_char(p_attributes_rec.expansion),'') || '"';
1562 end if;
1563 if ((p_attributes_rec.als_max_length IS NOT NULL ) and
1564 (p_attributes_rec.als_max_length <> FND_API.G_MISS_NUM)) then
1565 l_index := l_index + 1;
1566 l_databuffer_tbl(l_index) := ' ALS_MAX_LENGTH = "' ||
1567 nvl(to_char(p_attributes_rec.als_max_length),'') || '"';
1568 end if;
1569
1570
1571 -- Flex Fields
1572 --
1573 if ((p_attributes_rec.attribute_category IS NOT NULL) and
1574 (p_attributes_rec.attribute_category <> FND_API.G_MISS_CHAR)) then
1575 l_index := l_index + 1;
1576 l_databuffer_tbl(l_index) := ' ATTRIBUTE_CATEGORY = "' ||
1577 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1578 p_attributes_rec.attribute_category) || '"';
1579 end if;
1580 if ((p_attributes_rec.attribute1 IS NOT NULL) and
1581 (p_attributes_rec.attribute1 <> FND_API.G_MISS_CHAR)) then
1582 l_index := l_index + 1;
1583 l_databuffer_tbl(l_index) := ' ATTRIBUTE1 = "' ||
1584 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1585 p_attributes_rec.attribute1) || '"';
1586 end if;
1587 if ((p_attributes_rec.attribute2 IS NOT NULL) and
1588 (p_attributes_rec.attribute2 <> FND_API.G_MISS_CHAR)) then
1589 l_index := l_index + 1;
1590 l_databuffer_tbl(l_index) := ' ATTRIBUTE2 = "' ||
1591 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1592 p_attributes_rec.attribute2) || '"';
1593 end if;
1594 if ((p_attributes_rec.attribute3 IS NOT NULL) and
1595 (p_attributes_rec.attribute3 <> FND_API.G_MISS_CHAR)) then
1596 l_index := l_index + 1;
1597 l_databuffer_tbl(l_index) := ' ATTRIBUTE3 = "' ||
1598 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1599 p_attributes_rec.attribute3) || '"';
1600 end if;
1601 if ((p_attributes_rec.attribute4 IS NOT NULL) and
1602 (p_attributes_rec.attribute4 <> FND_API.G_MISS_CHAR)) then
1603 l_index := l_index + 1;
1604 l_databuffer_tbl(l_index) := ' ATTRIBUTE4 = "' ||
1605 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1606 p_attributes_rec.attribute4) || '"';
1607 end if;
1608 if ((p_attributes_rec.attribute5 IS NOT NULL) and
1609 (p_attributes_rec.attribute5 <> FND_API.G_MISS_CHAR)) then
1610 l_index := l_index + 1;
1611 l_databuffer_tbl(l_index) := ' ATTRIBUTE5 = "' ||
1612 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1613 p_attributes_rec.attribute5) || '"';
1614 end if;
1615 if ((p_attributes_rec.attribute6 IS NOT NULL) and
1616 (p_attributes_rec.attribute6 <> FND_API.G_MISS_CHAR)) then
1617 l_index := l_index + 1;
1618 l_databuffer_tbl(l_index) := ' ATTRIBUTE6 = "' ||
1619 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1620 p_attributes_rec.attribute6) || '"';
1621 end if;
1622 if ((p_attributes_rec.attribute7 IS NOT NULL) and
1623 (p_attributes_rec.attribute7 <> FND_API.G_MISS_CHAR)) then
1624 l_index := l_index + 1;
1625 l_databuffer_tbl(l_index) := ' ATTRIBUTE7 = "' ||
1626 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1627 p_attributes_rec.attribute7) || '"';
1628 end if;
1629 if ((p_attributes_rec.attribute8 IS NOT NULL) and
1630 (p_attributes_rec.attribute8 <> FND_API.G_MISS_CHAR)) then
1631 l_index := l_index + 1;
1632 l_databuffer_tbl(l_index) := ' ATTRIBUTE8 = "' ||
1633 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1634 p_attributes_rec.attribute8) || '"';
1635 end if;
1636 if ((p_attributes_rec.attribute9 IS NOT NULL) and
1637 (p_attributes_rec.attribute9 <> FND_API.G_MISS_CHAR)) then
1638 l_index := l_index + 1;
1639 l_databuffer_tbl(l_index) := ' ATTRIBUTE9 = "' ||
1640 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1641 p_attributes_rec.attribute9) || '"';
1642 end if;
1643 if ((p_attributes_rec.attribute10 IS NOT NULL) and
1644 (p_attributes_rec.attribute10 <> FND_API.G_MISS_CHAR)) then
1645 l_index := l_index + 1;
1646 l_databuffer_tbl(l_index) := ' ATTRIBUTE10 = "' ||
1647 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1648 p_attributes_rec.attribute10) || '"';
1649 end if;
1650 if ((p_attributes_rec.attribute11 IS NOT NULL) and
1651 (p_attributes_rec.attribute11 <> FND_API.G_MISS_CHAR)) then
1652 l_index := l_index + 1;
1653 l_databuffer_tbl(l_index) := ' ATTRIBUTE11 = "' ||
1654 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1655 p_attributes_rec.attribute11) || '"';
1656 end if;
1657 if ((p_attributes_rec.attribute12 IS NOT NULL) and
1658 (p_attributes_rec.attribute12 <> FND_API.G_MISS_CHAR)) then
1659 l_index := l_index + 1;
1660 l_databuffer_tbl(l_index) := ' ATTRIBUTE12 = "' ||
1661 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1662 p_attributes_rec.attribute12) || '"';
1663 end if;
1664 if ((p_attributes_rec.attribute13 IS NOT NULL) and
1665 (p_attributes_rec.attribute13 <> FND_API.G_MISS_CHAR)) then
1666 l_index := l_index + 1;
1667 l_databuffer_tbl(l_index) := ' ATTRIBUTE13 = "' ||
1668 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1669 p_attributes_rec.attribute13) || '"';
1670 end if;
1671 if ((p_attributes_rec.attribute14 IS NOT NULL) and
1672 (p_attributes_rec.attribute14 <> FND_API.G_MISS_CHAR)) then
1673 l_index := l_index + 1;
1674 l_databuffer_tbl(l_index) := ' ATTRIBUTE14 = "' ||
1675 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1676 p_attributes_rec.attribute14) || '"';
1677 end if;
1678 if ((p_attributes_rec.attribute15 IS NOT NULL) and
1679 (p_attributes_rec.attribute15 <> FND_API.G_MISS_CHAR)) then
1680 l_index := l_index + 1;
1681 l_databuffer_tbl(l_index) := ' ATTRIBUTE15 = "' ||
1682 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1683 p_attributes_rec.attribute15) || '"';
1684 end if;
1685 -- - Write out who columns
1686 l_index := l_index + 1;
1687 l_databuffer_tbl(l_index) := ' CREATED_BY = "' ||
1688 nvl(to_char(p_attributes_rec.created_by),'') || '"';
1689 l_index := l_index + 1;
1690 l_databuffer_tbl(l_index) := ' CREATION_DATE = "' ||
1691 to_char(p_attributes_rec.creation_date,
1692 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1693 l_index := l_index + 1;
1694 -- CHANGED TO OWNER FOR R12
1695 -- l_databuffer_tbl(l_index) := ' LAST_UPDATED_BY = "' ||
1696 -- nvl(to_char(p_attributes_rec.last_updated_by),'') || '"';
1697 l_databuffer_tbl(l_index) := ' OWNER = "' ||
1698 FND_LOAD_UTIL.OWNER_NAME(p_attributes_rec.last_updated_by) || '"';
1699 l_index := l_index + 1;
1700 l_databuffer_tbl(l_index) := ' LAST_UPDATE_DATE = "' ||
1701 to_char(p_attributes_rec.last_update_date,
1702 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1703 l_index := l_index + 1;
1704 l_databuffer_tbl(l_index) := ' LAST_UPDATE_LOGIN = "' ||
1705 nvl(to_char(p_attributes_rec.last_update_login),'') || '"';
1706 -- translation columns
1707 --
1708 if ((p_attributes_tl_rec.name IS NOT NULL) and
1709 (p_attributes_tl_rec.name <> FND_API.G_MISS_CHAR)) then
1710 l_index := l_index + 1;
1711 l_databuffer_tbl(l_index) := ' NAME = "' ||
1712 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(p_attributes_tl_rec.name) || '"';
1713 end if;
1714 if ((p_attributes_tl_rec.attribute_label_long IS NOT NULL) and
1715 (p_attributes_tl_rec.attribute_label_long <> FND_API.G_MISS_CHAR)) then
1716 l_index := l_index + 1;
1717 l_databuffer_tbl(l_index) := ' ATTRIBUTE_LABEL_LONG = "' ||
1718 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(p_attributes_tl_rec.attribute_label_long)
1719 || '"';
1720 end if;
1721 if ((p_attributes_tl_rec.attribute_label_short IS NOT NULL) and
1722 (p_attributes_tl_rec.attribute_label_short <> FND_API.G_MISS_CHAR)) then
1723 l_index := l_index + 1;
1724 l_databuffer_tbl(l_index) := ' ATTRIBUTE_LABEL_SHORT = "' ||
1725 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(p_attributes_tl_rec.attribute_label_short)
1726 || '"';
1727 end if;
1728 if ((p_attributes_tl_rec.description IS NOT NULL) and
1729 (p_attributes_tl_rec.description <> FND_API.G_MISS_CHAR)) then
1730 l_index := l_index + 1;
1731 l_databuffer_tbl(l_index) := ' DESCRIPTION = "' ||
1732 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(p_attributes_tl_rec.description) || '"';
1733 end if;
1734
1735 l_index := l_index + 1;
1736 l_databuffer_tbl(l_index) := 'END ATTRIBUTE';
1737 l_index := l_index + 1;
1738 l_databuffer_tbl(l_index) := ' ';
1739
1740 --
1741 -- - Write attribute data out to the specified file
1742 --
1743 AK_ON_OBJECTS_PVT.WRITE_FILE (
1744 p_return_status => l_return_status,
1745 p_buffer_tbl => l_databuffer_tbl,
1746 p_write_mode => AK_ON_OBJECTS_PUB.G_APPEND
1747 );
1748
1749 --
1750 -- If API call returns with an error status...
1751 --
1752 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1753 (l_return_status = FND_API.G_RET_STS_ERROR) then
1754 RAISE FND_API.G_EXC_ERROR;
1755 end if;
1756
1757 p_return_status := FND_API.G_RET_STS_SUCCESS;
1758
1759 EXCEPTION
1760 WHEN VALUE_ERROR THEN
1761 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1762 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_VALUE_ERROR');
1763 FND_MESSAGE.SET_TOKEN('KEY',
1764 to_char(p_attributes_rec.attribute_application_id) ||
1765 ' ' || p_attributes_rec.attribute_code);
1766 FND_MSG_PUB.Add;
1767 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_NOT_DOWNLOADED');
1768 FND_MESSAGE.SET_TOKEN('KEY',
1769 to_char(p_attributes_rec.attribute_application_id) ||
1770 ' ' || p_attributes_rec.attribute_code);
1771 FND_MSG_PUB.Add;
1772 end if;
1773 p_return_status := FND_API.G_RET_STS_ERROR;
1774 WHEN FND_API.G_EXC_ERROR THEN
1775 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1776 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_NOT_DOWNLOADED');
1777 FND_MESSAGE.SET_TOKEN('KEY',
1778 to_char(p_attributes_rec.attribute_application_id) ||
1779 ' ' || p_attributes_rec.attribute_code);
1780 FND_MSG_PUB.Add;
1781 end if;
1782 p_return_status := FND_API.G_RET_STS_ERROR;
1783 WHEN OTHERS THEN
1784 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1785 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1786 SUBSTR (SQLERRM, 1, 240) );
1787 FND_MSG_PUB.Add;
1788 end WRITE_TO_BUFFER;
1789
1790 --=======================================================
1791 -- Procedure DOWNLOAD_ATTRIBUTE
1792 --
1793 -- Usage Private API for downloading attributes. This
1794 -- API should only be called by other APIs that are
1795 -- owned by the Core Modules Team (AK).
1796 --
1797 -- Desc This API will extract the attributes selected
1798 -- by application ID or by key values from the
1799 -- database to the output file.
1800 --
1801 -- Results The API returns the standard p_return_status parameter
1802 -- indicating one of the standard return statuses :
1803 -- * Unexpected error
1804 -- * Error
1805 -- * Success
1806 -- Parameters
1807 -- p_nls_language : IN optional
1808 -- NLS language for database. If none if given,
1809 -- the current NLS language will be used.
1810 --
1811 -- One of the following parameters must be provided:
1812 --
1813 -- p_application_id : IN optional
1814 -- If given, all attributes for this application ID
1815 -- will be written to the output file.
1816 -- p_application_id will be ignored if a table is
1817 -- given in p_attribute_pk_tbl.
1818 -- p_attribute_pk_tbl : IN optional
1819 -- If given, only attributes whose key values are
1820 -- included in this table will be written to the
1821 -- output file.
1822 --
1823 --
1824 -- Version Initial version number = 1.0
1825 -- History Current version number = 1.0
1826 --=======================================================
1827 procedure DOWNLOAD_ATTRIBUTE (
1828 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1829 p_api_version_number IN NUMBER,
1830 p_return_status OUT NOCOPY VARCHAR2,
1831 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
1832 p_attribute_pk_tbl IN AK_ATTRIBUTE_PUB.Attribute_PK_Tbl_Type
1833 := AK_ATTRIBUTE_PUB.G_MISS_ATTRIBUTE_PK_TBL,
1834 p_nls_language IN VARCHAR2
1835 ) is
1836 cursor l_get_attribute_1_csr (appl_id_parm number) is
1837 select *
1838 from AK_ATTRIBUTES
1839 where ATTRIBUTE_APPLICATION_ID = appl_id_parm;
1840 cursor l_get_attribute_2_csr (appl_id_parm number,
1841 attr_code_parm varchar2) is
1842 select *
1843 from AK_ATTRIBUTES
1844 where ATTRIBUTE_APPLICATION_ID = appl_id_parm
1845 and ATTRIBUTE_CODE = attr_code_parm;
1846 cursor l_get_attribute_2p_csr (appl_id_parm number,
1847 attr_code_parm varchar2) is
1848 select *
1849 from AK_ATTRIBUTES
1850 where ATTRIBUTE_APPLICATION_ID = appl_id_parm
1851 and ATTRIBUTE_CODE like attr_code_parm;
1852 cursor l_get_tl_csr (appl_id_parm number,
1853 attr_code_parm varchar2,
1854 lang_parm varchar2) is
1855 select *
1856 from AK_ATTRIBUTES_TL
1857 where ATTRIBUTE_APPLICATION_ID = appl_id_parm
1858 and ATTRIBUTE_CODE = attr_code_parm
1859 and LANGUAGE = lang_parm;
1860 cursor l_get_tlp_csr (appl_id_parm number,
1861 attr_code_parm varchar2,
1862 lang_parm varchar2) is
1863 select *
1864 from AK_ATTRIBUTES_TL
1865 where ATTRIBUTE_APPLICATION_ID = appl_id_parm
1866 and ATTRIBUTE_CODE like attr_code_parm
1867 and LANGUAGE = lang_parm;
1868 cursor l_percent_check (attr_code_parm varchar2) is
1869 select instr(attr_code_parm,'%')
1870 from dual;
1871
1872 l_api_version_number CONSTANT number := 1.0;
1873 l_api_name CONSTANT varchar2(30) := 'Download';
1874 l_attribute_found BOOLEAN;
1875 i number;
1876 l_attribute_appl_id NUMBER;
1877 l_attributes_rec ak_attributes%ROWTYPE;
1878 l_attributes_tl_rec ak_attributes_tl%ROWTYPE;
1879 l_return_status varchar2(1);
1880 l_select_by_appl_id BOOLEAN;
1881 l_percent NUMBER;
1882 begin
1883 IF NOT FND_API.Compatible_API_Call (
1884 l_api_version_number, p_api_version_number, l_api_name,
1885 G_PKG_NAME) then
1886 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1887 return;
1888 END IF;
1889
1890 --
1891 -- Check that one of the following selection criteria is given:
1892 -- - p_application_id alone, or
1893 -- - attribute_application_id and attribute_code pairs in
1894 -- p_attribute_pk_tbl, or
1895 -- - both p_application_id and p_attribute_pk_tbl if any
1896 -- p_attribute_application_id is missing in p_attribute_pk_tbl
1897 --
1898 if (p_application_id = FND_API.G_MISS_NUM) then
1899 if (p_attribute_pk_tbl.count = 0) then
1900 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1901 FND_MESSAGE.SET_NAME('AK','AK_NO_SELECTION');
1902 FND_MSG_PUB.Add;
1903 end if;
1904 raise FND_API.G_EXC_ERROR;
1905 else
1906 --
1907 -- since no application ID is passed in thru p_application_id,
1908 -- none of the attribute_application_id or attribute_code
1909 -- in table can be null
1910 --
1911
1912 for i in p_attribute_pk_tbl.FIRST .. p_attribute_pk_tbl.LAST LOOP
1913 if (p_attribute_pk_tbl.exists(i)) then
1914 if (p_attribute_pk_tbl(i).attribute_appl_id = FND_API.G_MISS_NUM) or
1915 (p_attribute_pk_tbl(i).attribute_code = FND_API.G_MISS_CHAR)
1916 then
1917 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1918 FND_MESSAGE.SET_NAME('AK','AK_INVALID_LIST');
1919 FND_MESSAGE.SET_TOKEN('ELEMENT_NUM',to_char(i));
1920 FND_MSG_PUB.Add;
1921 end if;
1922 raise FND_API.G_EXC_ERROR;
1923 end if; /* if attribute_appl_id is null */
1924 end if; /* if exists */
1925 end LOOP;
1926
1927 end if;
1928 end if;
1929
1930 --
1931 -- selection is by application ID if the attribute list table is empty
1932 --
1933 if (p_attribute_pk_tbl.count = 0) then
1934 l_select_by_appl_id := TRUE;
1935 else
1936 l_select_by_appl_id := FALSE;
1937 end if;
1938
1939 --
1940 -- Retrieve attributes from AK_ATTRIBUTES that fits the selection
1941 -- criteria, one at a time, and write it the buffer table
1942 --
1943 if (l_select_by_appl_id) then
1944 --
1945 -- download by application ID
1946 --
1947 open l_get_attribute_1_csr(p_application_id);
1948
1949 loop
1950 fetch l_get_attribute_1_csr into l_attributes_rec;
1951 exit when l_get_attribute_1_csr%notfound;
1952 open l_get_tl_csr (l_attributes_rec.attribute_application_id,
1953 l_attributes_rec.attribute_code,
1954 p_nls_language);
1955 fetch l_get_tl_csr into l_attributes_tl_rec;
1956 exit when l_get_tl_csr%notfound;
1957 close l_get_tl_csr;
1958
1959 WRITE_TO_BUFFER(
1960 p_validation_level => p_validation_level,
1961 p_return_status => l_return_status,
1962 p_attributes_rec => l_attributes_rec,
1963 p_attributes_tl_rec => l_attributes_tl_rec
1964 );
1965 --
1966 -- abort Download if validation has been failed
1967 --
1968 if (l_return_status = FND_API.G_RET_STS_ERROR) then
1969 raise FND_API.G_EXC_ERROR;
1970 end if;
1971 end loop;
1972 close l_get_attribute_1_csr;
1973
1974 if l_get_tl_csr%isopen then
1975 close l_get_tl_csr;
1976 end if;
1977 else
1978 --
1979 -- download by list of attributes
1980 --
1981 for i in p_attribute_pk_tbl.FIRST .. p_attribute_pk_tbl.LAST LOOP
1982 if (p_attribute_pk_tbl.exists(i)) then
1983 --
1984 -- default application ID to p_application_id if not given
1985 --
1986 if (p_attribute_pk_tbl(i).attribute_appl_id = FND_API.G_MISS_NUM) then
1987 l_attribute_appl_id := p_application_id;
1988 else
1989 l_attribute_appl_id := p_attribute_pk_tbl(i).attribute_appl_id;
1990 end if;
1991
1992 --
1993 -- Retrieve attribute and its TL entry from the database
1994 --
1995 l_attribute_found := TRUE;
1996 open l_percent_check(p_attribute_pk_tbl(i).attribute_code);
1997 fetch l_percent_check into l_percent;
1998 if l_percent <> 0 then
1999 open l_get_attribute_2p_csr(l_attribute_appl_id,
2000 p_attribute_pk_tbl(i).attribute_code);
2001 loop
2002 fetch l_get_attribute_2p_csr into l_attributes_rec;
2003 exit when l_get_attribute_2p_csr%notfound;
2004 open l_get_tlp_csr (l_attributes_rec.attribute_application_id,
2005 l_attributes_rec.attribute_code,
2006 p_nls_language);
2007 fetch l_get_tlp_csr into l_attributes_tl_rec;
2008 exit when l_get_tlp_csr%notfound;
2009 close l_get_tlp_csr;
2010
2011 WRITE_TO_BUFFER(
2012 p_validation_level => p_validation_level,
2013 p_return_status => l_return_status,
2014 p_attributes_rec => l_attributes_rec,
2015 p_attributes_tl_rec => l_attributes_tl_rec
2016 );
2017 --
2018 -- abort Download if validation has been failed
2019 --
2020 if (l_return_status = FND_API.G_RET_STS_ERROR) then
2021 raise FND_API.G_EXC_ERROR;
2022 end if;
2023 end loop;
2024 close l_get_attribute_2p_csr;
2025 else
2026 open l_get_attribute_2_csr(l_attribute_appl_id,
2027 p_attribute_pk_tbl(i).attribute_code);
2028 fetch l_get_attribute_2_csr into l_attributes_rec;
2029 if (l_get_attribute_2_csr%notfound) then
2030 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2031 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_DOES_NOT_EXIST');
2032 FND_MSG_PUB.Add;
2033 FND_MESSAGE.SET_NAME('AK','AK_ATTR_NOT_DOWNLOADED');
2034 FND_MESSAGE.SET_TOKEN('KEY', to_char(l_attribute_appl_id) ||
2035 ' ' || p_attribute_pk_tbl(i).attribute_code);
2036 FND_MSG_PUB.Add;
2037 end if;
2038 l_attribute_found := FALSE;
2039 else
2040 open l_get_tl_csr (l_attributes_rec.attribute_application_id,
2041 l_attributes_rec.attribute_code,
2042 p_nls_language);
2043 fetch l_get_tl_csr into l_attributes_tl_rec;
2044 if ( l_get_tl_csr%notfound) then
2045 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2046 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_DOES_NOT_EXIST');
2047 FND_MSG_PUB.Add;
2048 end if;
2049 l_attribute_found := FALSE;
2050 end if;
2051 close l_get_tl_csr;
2052 end if;
2053 close l_get_attribute_2_csr;
2054
2055 --
2056 -- write attribute and its TL entry to buffer
2057 --
2058 if l_attribute_found then
2059 WRITE_TO_BUFFER(
2060 p_validation_level => p_validation_level,
2061 p_return_status => l_return_status,
2062 p_attributes_rec => l_attributes_rec,
2063 p_attributes_tl_rec => l_attributes_tl_rec
2064 );
2065 --
2066 -- abort Download if validation has been failed
2067 --
2068 if (l_return_status = FND_API.G_RET_STS_ERROR) then
2069 raise FND_API.G_EXC_ERROR;
2070 end if;
2071 end if; /* if l_attribute_found */
2072 end if; /* if l_percent */
2073 close l_percent_check;
2074 end if; /* if exists(i) */
2075 end loop;
2076 end if;
2077
2078 p_return_status := FND_API.G_RET_STS_SUCCESS;
2079
2080 EXCEPTION
2081 WHEN FND_API.G_EXC_ERROR THEN
2082 p_return_status := FND_API.G_RET_STS_ERROR;
2083 WHEN OTHERS THEN
2084 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2085 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name,
2086 SUBSTR (SQLERRM, 1, 240) );
2087 end DOWNLOAD_ATTRIBUTE;
2088
2089 --=======================================================
2090 -- Procedure UPLOAD_ATTRIBUTE
2091 --
2092 -- Usage Private API for loading attributes from a
2093 -- loader file to the database.
2094 -- This API should only be called by other APIs
2095 -- that are owned by the Core Modules Team (AK).
2096 --
2097 -- Desc This API reads the attribute data stored in
2098 -- the loader file currently being processed, parses
2099 -- the data, and loads them to the database. The tables
2100 -- are updated with the timestamp passed. This API
2101 -- will process the file until the EOF is reached,
2102 -- a parse error is encountered, or when data for
2103 -- a different business object is read from the file.
2104 --
2105 -- Results The API returns the standard p_return_status parameter
2106 -- indicating one of the standard return statuses :
2107 -- * Unexpected error
2108 -- * Error
2109 -- * Success
2110 -- Parameters p_index : IN OUT required
2111 -- Index of PL/SQL file to be processed.
2112 -- p_loader_timestamp : IN required
2113 -- The timestamp to be used when creating or updating
2114 -- records
2115 -- p_line_num : IN optional
2116 -- The first line number in the file to be processed.
2117 -- It is used for keeping track of the line number
2118 -- read so that this info can be included in the
2119 -- error message when a parse error occurred.
2120 -- p_buffer : IN required
2121 -- The content of the first line to be processed.
2122 -- The calling API has already read the first line
2123 -- that needs to be parsed by this API, so this
2124 -- line won't be read from the file again.
2125 -- p_line_num_out : OUT
2126 -- The number of the last line in the loader file
2127 -- that is read by this API.
2128 -- p_buffer_out : OUT
2129 -- The content of the last line read by this API.
2130 -- If an EOF has not reached, this line would
2131 -- contain the beginning of another business object
2132 -- that will need to be processed by another API.
2133 --
2134 -- Version Initial version number = 1.0
2135 -- History Current version number = 1.0
2136 --=======================================================
2137 procedure UPLOAD_ATTRIBUTE (
2138 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2139 p_api_version_number IN NUMBER,
2140 p_return_status OUT NOCOPY VARCHAR2,
2141 p_index IN OUT NOCOPY NUMBER,
2142 p_loader_timestamp IN DATE,
2143 p_line_num IN NUMBER := FND_API.G_MISS_NUM,
2144 p_buffer IN AK_ON_OBJECTS_PUB.Buffer_Type,
2145 p_line_num_out OUT NOCOPY NUMBER,
2146 p_buffer_out OUT NOCOPY AK_ON_OBJECTS_PUB.Buffer_Type,
2147 p_upl_loader_cur IN OUT NOCOPY AK_ON_OBJECTS_PUB.LoaderCurTyp,
2148 p_pass IN NUMBER := 1
2149 ) is
2150 l_api_version_number CONSTANT number := 1.0;
2151 l_api_name CONSTANT varchar2(30) := 'Upload_Attribute';
2152 l_attribute_rec ak_attributes%ROWTYPE;
2153 l_attribute_tl_rec AK_ATTRIBUTE_PUB.Attribute_Tl_Rec_Type;
2154 l_buffer AK_ON_OBJECTS_PUB.Buffer_Type;
2155 l_column varchar2(30);
2156 l_dummy NUMBER;
2157 l_empty_attribute_rec ak_attributes%ROWTYPE;
2158 l_empty_attribute_tl_rec AK_ATTRIBUTE_PUB.Attribute_Tl_Rec_Type;
2159 l_eof_flag VARCHAR2(1);
2160 l_line_num NUMBER;
2161 l_lines_read NUMBER;
2162 l_msg_count NUMBER;
2163 l_msg_data VARCHAR2(2000);
2164 l_more_attr BOOLEAN := TRUE;
2165 l_return_status varchar2(1);
2166 l_saved_token AK_ON_OBJECTS_PUB.Buffer_type;
2167 l_state NUMBER; /* parse state */
2168 l_token AK_ON_OBJECTS_PUB.Buffer_Type;
2169 l_value_count NUMBER; /* # of values read for current column */
2170 l_copy_redo_flag BOOLEAN := FALSE;
2171 l_user_id1 NUMBER;
2172 l_user_id2 NUMBER;
2173 l_update1 DATE;
2174 l_update2 DATE;
2175 begin
2176 IF NOT FND_API.Compatible_API_Call (
2177 l_api_version_number, p_api_version_number, l_api_name,
2178 G_PKG_NAME) then
2179 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2180 return;
2181 END IF;
2182
2183 --dbms_output.put_line('Started attribute upload: ' ||
2184 -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
2185
2186 -- SAVEPOINT Start_Upload;
2187
2188 --
2189 -- Retrieve the first non-blank, non-comment line
2190 --
2191 l_state := 0;
2192 l_eof_flag := 'N';
2193 --
2194 -- if calling from ak_on_objects.upload (ie, loader timestamp is given),
2195 -- the tokens 'BEGIN ATTRIBUTE' has already been parsed. Set initial
2196 -- buffer to 'BEGIN ATTRIBUTE' before reading the next line from the
2197 -- file. Otherwise, set initial buffer to null.
2198 --
2199 if (p_loader_timestamp <> FND_API.G_MISS_DATE) then
2200 l_buffer := 'BEGIN ATTRIBUTE ' || p_buffer;
2201 else
2202 l_buffer := null;
2203 end if;
2204
2205 if (p_line_num = FND_API.G_MISS_NUM) then
2206 l_line_num := 0;
2207 else
2208 l_line_num := p_line_num;
2209 end if;
2210
2211 while (l_buffer is null and l_eof_flag = 'N' and p_index <= AK_ON_OBJECTS_PVT.G_UPL_TABLE_NUM) loop
2212 AK_ON_OBJECTS_PVT.READ_LINE (
2213 p_return_status => l_return_status,
2214 p_index => p_index,
2215 p_buffer => l_buffer,
2216 p_lines_read => l_lines_read,
2217 p_eof_flag => l_eof_flag,
2218 p_upl_loader_cur => p_upl_loader_cur
2219 );
2220 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2221 (l_return_status = FND_API.G_RET_STS_ERROR) then
2222 RAISE FND_API.G_EXC_ERROR;
2223 end if;
2224 --dbms_output.put_line('READ_LINE gets l_buffer = '||l_buffer);
2225
2226 l_line_num := l_line_num + l_lines_read;
2227 --
2228 -- trim leading spaces and discard comment lines
2229 --
2230 l_buffer := LTRIM(l_buffer);
2231 if (SUBSTR(l_buffer, 1, 1) = '#') then
2232 l_buffer := null;
2233 end if;
2234 end loop;
2235
2236 --
2237 -- Error if there is nothing to be read from the file
2238 --
2239 if (l_buffer is null and l_eof_flag = 'Y') then
2240 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2241 FND_MESSAGE.SET_NAME('AK','AK_EMPTY_BUFFER');
2242 FND_MSG_PUB.Add;
2243 end if;
2244 raise FND_API.G_EXC_ERROR;
2245 end if;
2246
2247 --
2248 -- Read tokens from file, one at a time
2249 --
2250 while (l_eof_flag = 'N') and (l_buffer is not null)
2251 and (l_more_attr) loop
2252
2253 AK_ON_OBJECTS_PVT.GET_TOKEN(
2254 p_return_status => l_return_status,
2255 p_in_buf => l_buffer,
2256 p_token => l_token
2257 );
2258
2259 --dbms_output.put_line(' State:' || l_state || 'Token:' || l_token);
2260 -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
2261
2262 if (l_return_status = FND_API.G_RET_STS_ERROR) or
2263 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
2264 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2265 -- dbms_output.put_line('State '||l_state||' Token '||l_token|| 'l_buffer '||l_buffer);
2266 FND_MESSAGE.SET_NAME('AK','AK_GET_TOKEN_ERROR');
2267 FND_MSG_PUB.Add;
2268 end if;
2269 -- dbms_output.put_line('Error parsing buffer');
2270 raise FND_API.G_EXC_ERROR;
2271 end if;
2272
2273 if (l_state = 0) then
2274 if (l_token = 'BEGIN') then
2275 --== Clear out previous column data ==--
2276 l_attribute_rec := l_empty_attribute_rec;
2277 l_attribute_tl_rec := l_empty_attribute_tl_rec;
2278 l_state := 1;
2279 else
2280 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2281 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
2282 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
2283 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
2284 FND_MESSAGE.SET_TOKEN('EXPECTED','BEGIN');
2285 FND_MSG_PUB.Add;
2286 end if;
2287 raise FND_API.G_EXC_ERROR;
2288 end if;
2289 elsif (l_state = 1) then
2290 if (l_token = 'ATTRIBUTE') then
2291 l_state := 2;
2292 else
2293 -- Found the beginning of a non-attribute object,
2294 -- rebuild last line and pass it back to the caller
2295 -- (ak_on_objects_pvt.upload).
2296 p_buffer_out := 'BEGIN ' || l_token || ' ' || l_buffer;
2297 l_more_attr := FALSE;
2298 end if;
2299 elsif (l_state = 2) then
2300 if (l_token is not null) then
2301 l_attribute_rec.attribute_application_id := to_number(l_token);
2302 l_state := 3;
2303 else
2304 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2305 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
2306 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
2307 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
2308 FND_MESSAGE.SET_TOKEN('EXPECTED','ATTRIBUTE_APPLICATION_ID');
2309 FND_MSG_PUB.Add;
2310 end if;
2311 -- dbms_output.put_line('Expecting attribute application ID');
2312 raise FND_API.G_EXC_ERROR;
2313 end if;
2314 elsif (l_state = 3) then
2315 if (l_token is not null) then
2316 l_attribute_rec.attribute_code := l_token;
2317 l_value_count := null;
2318 l_state := 10;
2319 else
2320 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2321 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
2322 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
2323 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
2324 FND_MESSAGE.SET_TOKEN('EXPECTED','ATTRIBUTE_CODE');
2325 FND_MSG_PUB.Add;
2326 end if;
2327 --dbms_output.put_line('Expecting attribute code');
2328 raise FND_API.G_EXC_ERROR;
2329 end if;
2330 elsif (l_state = 10) then
2331 if (l_token = 'END') then
2332 l_state := 19;
2333 elsif (l_token = 'ATTRIBUTE_LABEL_LENGTH') or
2334 (l_token = 'ATTRIBUTE_VALUE_LENGTH') or
2335 (l_token = 'BOLD') or
2336 (l_token = 'ITALIC') or
2337 (l_token = 'VERTICAL_ALIGNMENT') or
2338 (l_token = 'HORIZONTAL_ALIGNMENT') or
2339 (l_token = 'DATA_TYPE') or
2340 (l_token = 'UPPER_CASE_FLAG') or
2341 (l_token = 'DEFAULT_VALUE_VARCHAR2') or
2342 (l_token = 'DEFAULT_VALUE_NUMBER') or
2343 (l_token = 'DEFAULT_VALUE_DATE') or
2344 (l_token = 'LOV_REGION') or
2345 (l_token = 'ITEM_STYLE') or
2346 (l_token = 'DISPLAY_HEIGHT') or
2347 (l_token = 'CSS_CLASS_NAME') or
2348 (l_token = 'POPLIST_VIEWOBJECT') or
2349 (l_token = 'POPLIST_DISPLAY_ATTRIBUTE') or
2350 (l_token = 'POPLIST_VALUE_ATTRIBUTE') or
2351 (l_token = 'CSS_LABEL_CLASS_NAME') or
2352 (l_token = 'PRECISION') or
2353 (l_token = 'EXPANSION') or
2354 (l_token = 'ALS_MAX_LENGTH') or
2355 (l_token = 'ATTRIBUTE_CATEGORY') or
2356 (l_token = 'ATTRIBUTE1') or
2357 (l_token = 'ATTRIBUTE2') or
2358 (l_token = 'ATTRIBUTE3') or
2359 (l_token = 'ATTRIBUTE4') or
2360 (l_token = 'ATTRIBUTE5') or
2361 (l_token = 'ATTRIBUTE6') or
2362 (l_token = 'ATTRIBUTE7') or
2363 (l_token = 'ATTRIBUTE8') or
2364 (l_token = 'ATTRIBUTE9') or
2365 (l_token = 'ATTRIBUTE10') or
2366 (l_token = 'ATTRIBUTE11') or
2367 (l_token = 'ATTRIBUTE12') or
2368 (l_token = 'ATTRIBUTE13') or
2369 (l_token = 'ATTRIBUTE14') or
2370 (l_token = 'ATTRIBUTE15') or
2371 (l_token = 'CREATED_BY') or
2372 (l_token = 'CREATION_DATE') or
2373 (l_token = 'LAST_UPDATED_BY') or
2374 (l_token = 'OWNER') or
2375 (l_token = 'LAST_UPDATE_DATE') or
2376 (l_token = 'LAST_UPDATE_LOGIN') or
2377 (l_token = 'ATTRIBUTE_LABEL_LONG') or
2378 (l_token = 'ATTRIBUTE_LABEL_SHORT') or
2379 (l_token = 'NAME') or
2380 (l_token = 'DESCRIPTION') then
2381 l_column := l_token;
2382 l_state := 11;
2383 else
2384 --
2385 -- error if not expecting attribute values added by the translation team
2386 -- or if we have read in more than a certain number of values
2387 -- for the same DB column
2388 --
2389 l_value_count := l_value_count + 1;
2390 --
2391 -- save second value. It will be the token with error if
2392 -- it turns out that there is a parse error on this line.
2393 --
2394 if (l_value_count = 2) then
2395 l_saved_token := l_token;
2396 end if;
2397 if (l_value_count > AK_ON_OBJECTS_PUB.G_MAX_NUM_LOADER_VALUES) or
2398 (l_value_count is null) then
2399 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2400 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_EFIELD');
2401 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
2402 if (l_value_count is null) then
2403 FND_MESSAGE.SET_TOKEN('TOKEN', l_token);
2404 else
2405 FND_MESSAGE.SET_TOKEN('TOKEN',l_saved_token);
2406 end if;
2407 FND_MESSAGE.SET_TOKEN('EXPECTED','ATTRIBUTE');
2408 FND_MSG_PUB.Add;
2409 end if;
2410 -- dbms_output.put_line('Expecting attribute field or END');
2411 raise FND_API.G_EXC_ERROR;
2412 end if;
2413 end if;
2414 elsif (l_state = 11) then
2415 if (l_token = '=') then
2416 l_state := 12;
2417 else
2418 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2419 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
2420 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
2421 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
2422 FND_MESSAGE.SET_TOKEN('EXPECTED','=');
2423 FND_MSG_PUB.Add;
2424 end if;
2425 raise FND_API.G_EXC_ERROR;
2426 end if;
2427 elsif (l_state = 12) then
2428 l_value_count := 1;
2429 if (l_column = 'ATTRIBUTE_LABEL_LENGTH') then
2430 l_attribute_rec.attribute_label_length := to_number(l_token);
2431 l_state := 10;
2432 elsif (l_column = 'ATTRIBUTE_VALUE_LENGTH') then
2433 l_attribute_rec.attribute_value_length := to_number(l_token);
2434 l_state := 10;
2435 elsif (l_column = 'BOLD') then
2436 l_attribute_rec.bold := l_token;
2437 l_state := 10;
2438 elsif (l_column = 'ITALIC')then
2439 l_attribute_rec.italic := l_token;
2440 l_state := 10;
2441 elsif (l_column = 'VERTICAL_ALIGNMENT') then
2442 l_attribute_rec.vertical_alignment := l_token;
2443 l_state := 10;
2444 elsif (l_column = 'HORIZONTAL_ALIGNMENT') then
2445 l_attribute_rec.horizontal_alignment := l_token;
2446 l_state := 10;
2447 elsif (l_column = 'DATA_TYPE') then
2448 l_attribute_rec.data_type := l_token;
2449 l_state := 10;
2450 elsif (l_column = 'UPPER_CASE_FLAG') then
2451 l_attribute_rec.upper_case_flag := l_token;
2452 l_state := 10;
2453 elsif (l_column = 'DEFAULT_VALUE_VARCHAR2') then
2454 l_attribute_rec.default_value_varchar2 := l_token;
2455 l_state := 10;
2456 elsif (l_column = 'DEFAULT_VALUE_NUMBER') then
2457 l_attribute_rec.default_value_number := to_number(l_token);
2458 l_state := 10;
2459 elsif (l_column = 'DEFAULT_VALUE_DATE') then
2460 l_attribute_rec.default_value_date := to_date(l_token,
2461 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
2462 l_state := 10;
2463 elsif (l_column = 'LOV_REGION') then
2464 l_attribute_rec.lov_region_application_id := to_number(l_token);
2465 l_state := 14;
2466 elsif (l_column = 'ITEM_STYLE') then
2467 l_attribute_rec.item_style := l_token;
2468 l_state := 10;
2469 elsif (l_column = 'DISPLAY_HEIGHT') then
2470 l_attribute_rec.display_height := to_number(l_token);
2471 l_state := 10;
2472 elsif (l_column = 'CSS_CLASS_NAME') then
2473 l_attribute_rec.css_class_name := l_token;
2474 l_state := 10;
2475 elsif (l_column = 'POPLIST_VIEWOBJECT') then
2476 l_attribute_rec.poplist_viewobject := l_token;
2477 l_state := 10;
2478 elsif (l_column = 'POPLIST_DISPLAY_ATTRIBUTE') then
2479 l_attribute_rec.poplist_display_attribute := l_token;
2480 l_state := 10;
2481 elsif (l_column = 'POPLIST_VALUE_ATTRIBUTE') then
2482 l_attribute_rec.poplist_value_attribute := l_token;
2483 l_state := 10;
2484 elsif (l_column = 'CSS_LABEL_CLASS_NAME') then
2485 l_attribute_rec.css_label_class_name := l_token;
2486 l_state := 10;
2487 elsif (l_column = 'PRECISION') then
2488 l_attribute_rec.precision := l_token;
2489 l_state := 10;
2490 elsif (l_column = 'EXPANSION') then
2491 l_attribute_rec.expansion := to_number(l_token);
2492 l_state := 10;
2493 elsif (l_column = 'ALS_MAX_LENGTH') then
2494 l_attribute_rec.als_max_length := to_number(l_token);
2495 l_state := 10;
2496 elsif (l_column = 'ATTRIBUTE_CATEGORY') then
2497 l_attribute_rec.attribute_category := l_token;
2498 l_state := 10;
2499 elsif (l_column = 'ATTRIBUTE1') then
2500 l_attribute_rec.attribute1 := l_token;
2501 l_state := 10;
2502 elsif (l_column = 'ATTRIBUTE2') then
2503 l_attribute_rec.attribute2 := l_token;
2504 l_state := 10;
2505 elsif (l_column = 'ATTRIBUTE3') then
2506 l_attribute_rec.attribute3 := l_token;
2507 l_state := 10;
2508 elsif (l_column = 'ATTRIBUTE4') then
2509 l_attribute_rec.attribute4 := l_token;
2510 l_state := 10;
2511 elsif (l_column = 'ATTRIBUTE5') then
2512 l_attribute_rec.attribute5 := l_token;
2513 l_state := 10;
2514 elsif (l_column = 'ATTRIBUTE6') then
2515 l_attribute_rec.attribute6 := l_token;
2516 l_state := 10;
2517 elsif (l_column = 'ATTRIBUTE7') then
2518 l_attribute_rec.attribute7 := l_token;
2519 l_state := 10;
2520 elsif (l_column = 'ATTRIBUTE8') then
2521 l_attribute_rec.attribute8 := l_token;
2522 l_state := 10;
2523 elsif (l_column = 'ATTRIBUTE9') then
2524 l_attribute_rec.attribute9 := l_token;
2525 l_state := 10;
2526 elsif (l_column = 'ATTRIBUTE10') then
2527 l_attribute_rec.attribute10 := l_token;
2528 l_state := 10;
2529 elsif (l_column = 'ATTRIBUTE11') then
2530 l_attribute_rec.attribute11 := l_token;
2531 l_state := 10;
2532 elsif (l_column = 'ATTRIBUTE12') then
2533 l_attribute_rec.attribute12 := l_token;
2534 l_state := 10;
2535 elsif (l_column = 'ATTRIBUTE13') then
2536 l_attribute_rec.attribute13 := l_token;
2537 l_state := 10;
2538 elsif (l_column = 'ATTRIBUTE14') then
2539 l_attribute_rec.attribute14 := l_token;
2540 l_state := 10;
2541 elsif (l_column = 'ATTRIBUTE15') then
2542 l_attribute_rec.attribute15 := l_token;
2543 l_state := 10;
2544 elsif (l_column = 'CREATED_BY') then
2545 l_attribute_rec.created_by := to_number(l_token);
2546 l_state := 10;
2547 elsif (l_column = 'CREATION_DATE') then
2548 l_attribute_rec.creation_date := to_date(l_token,
2549 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
2550 l_state := 10;
2551 elsif (l_column = 'LAST_UPDATED_BY') then
2552 l_attribute_rec.last_updated_by := to_number(l_token);
2553 l_state := 10;
2554 elsif (l_column = 'OWNER') then
2555 l_attribute_rec.last_updated_by := FND_LOAD_UTIL.OWNER_ID(l_token);
2556 l_state := 10;
2557 elsif (l_column = 'LAST_UPDATE_DATE') then
2558 l_attribute_rec.last_update_date := to_date(l_token,
2559 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
2560 l_state := 10;
2561 elsif (l_column = 'LAST_UPDATE_LOGIN') then
2562 l_attribute_rec.last_update_login := to_number(l_token);
2563 l_state := 10;
2564 elsif (l_column = 'ATTRIBUTE_LABEL_SHORT') then
2565 l_attribute_tl_rec.attribute_label_short := l_token;
2566 l_state := 10;
2567 elsif (l_column = 'ATTRIBUTE_LABEL_LONG') then
2568 l_attribute_tl_rec.attribute_label_long := l_token;
2569 l_state := 10;
2570 elsif (l_column = 'NAME') then
2571 l_attribute_tl_rec.name := l_token;
2572 l_state := 10;
2573 elsif (l_column = 'DESCRIPTION') then
2574 l_attribute_tl_rec.description := l_token;
2575 l_state := 10;
2576 else
2577 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2578 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
2579 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
2580 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
2581 FND_MESSAGE.SET_TOKEN('EXPECTED',l_column);
2582 FND_MSG_PUB.Add;
2583 end if;
2584 --dbms_output.put_line('Expecting ' || l_column || ' value');
2585 raise FND_API.G_EXC_ERROR;
2586 end if;
2587 elsif (l_state = 14) then
2588 if (l_column = 'LOV_REGION') then
2589 l_attribute_rec.lov_region_code := l_token;
2590 l_state := 10;
2591 else
2592 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2593 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
2594 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
2595 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
2596 FND_MESSAGE.SET_TOKEN('EXPECTED',l_column);
2597 FND_MSG_PUB.Add;
2598 end if;
2599 --dbms_output.put_line('Expecting ' || l_column || ' value');
2600 raise FND_API.G_EXC_ERROR;
2601 end if;
2602 elsif (l_state = 19) then
2603 if (l_token = 'ATTRIBUTE') then
2604 if AK_ATTRIBUTE_PVT.ATTRIBUTE_EXISTS (
2605 p_api_version_number => 1.0,
2606 p_return_status => l_return_status,
2607 p_attribute_application_id=>
2608 l_attribute_rec.attribute_application_id,
2609 p_attribute_code => l_attribute_rec.attribute_code) then
2610 --
2611 -- do not update customized data
2612 if (AK_UPLOAD_GRP.G_NO_CUSTOM_UPDATE) then
2613 select aa.last_updated_by, aat.last_updated_by,
2614 aa.last_update_date, aat.last_update_date
2615 into l_user_id1, l_user_id2, l_update1, l_update2
2616 from ak_attributes aa, ak_attributes_tl aat
2617 where aa.attribute_code = l_attribute_rec.attribute_code
2618 and aa.attribute_application_id = l_attribute_rec.attribute_application_id
2619 and aa.attribute_code = aat.attribute_code
2620 and aa.attribute_application_id = aat.attribute_application_id
2621 and aat.language = userenv('LANG');
2622
2623 /*if (( l_user_id1 = 1 or l_user_id1 = 2) and
2624 (l_user_id2 = 1 or l_user_id2 = 2)) then*/
2625 if (AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
2626 p_loader_timestamp => p_loader_timestamp,
2627 p_created_by => l_attribute_rec.created_by,
2628 p_creation_date => l_attribute_rec.creation_date,
2629 p_last_updated_by => l_attribute_rec.last_updated_by,
2630 p_db_last_updated_by => l_user_id1,
2631 p_last_update_date => l_attribute_rec.last_update_date,
2632 p_db_last_update_date => l_update1,
2633 p_last_update_login => l_attribute_rec.last_update_login,
2634 p_create_or_update => 'UPDATE') and
2635
2636 AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
2637 p_loader_timestamp => p_loader_timestamp,
2638 p_created_by => l_attribute_rec.created_by,
2639 p_creation_date => l_attribute_rec.creation_date,
2640 p_last_updated_by => l_attribute_rec.last_updated_by,
2641 p_db_last_updated_by => l_user_id2,
2642 p_last_update_date => l_attribute_rec.last_update_date,
2643 p_db_last_update_date => l_update2,
2644 p_last_update_login => l_attribute_rec.last_update_login,
2645 p_create_or_update => 'UPDATE')) then
2646
2647 AK_ATTRIBUTE_PVT.UPDATE_ATTRIBUTE (
2648 p_validation_level => p_validation_level,
2649 p_api_version_number => 1.0,
2650 p_msg_count => l_msg_count,
2651 p_msg_data => l_msg_data,
2652 p_return_status => l_return_status,
2653 p_attribute_application_id =>
2654 l_attribute_rec.attribute_application_id,
2655 p_attribute_code => l_attribute_rec.attribute_code,
2656 p_attribute_label_length => l_attribute_rec.attribute_label_length,
2657 p_attribute_value_length => l_attribute_rec.attribute_value_length,
2658 p_bold => l_attribute_rec.bold,
2659 p_italic => l_attribute_rec.italic,
2660 p_vertical_alignment => l_attribute_rec.vertical_alignment,
2661 p_horizontal_alignment => l_attribute_rec.horizontal_alignment,
2662 p_data_type => l_attribute_rec.data_type,
2663 p_precision => l_attribute_rec.precision,
2664 p_upper_case_flag => l_attribute_rec.upper_case_flag,
2665 p_default_value_varchar2 => l_attribute_rec.default_value_varchar2,
2666 p_default_value_number => l_attribute_rec.default_value_number,
2667 p_default_value_date => l_attribute_rec.default_value_date,
2668 p_lov_region_application_id =>
2669 l_attribute_rec.lov_region_application_id,
2670 p_lov_region_code => l_attribute_rec.lov_region_code,
2671 p_item_style => l_attribute_rec.item_style,
2672 p_display_height => l_attribute_rec.display_height,
2673 p_css_class_name => l_attribute_rec.css_class_name,
2674 p_poplist_viewobject => l_attribute_rec.poplist_viewobject,
2675 p_poplist_display_attr => l_attribute_rec.poplist_display_attribute,
2676 p_poplist_value_attr => l_attribute_rec.poplist_value_attribute,
2677 p_css_label_class_name => l_attribute_rec.css_label_class_name,
2678 p_attribute_category => l_attribute_rec.attribute_category,
2679 p_expansion => l_attribute_rec.expansion,
2680 p_als_max_length => l_attribute_rec.als_max_length,
2681 p_attribute1 => l_attribute_rec.attribute1,
2682 p_attribute2 => l_attribute_rec.attribute2,
2683 p_attribute3 => l_attribute_rec.attribute3,
2684 p_attribute4 => l_attribute_rec.attribute4,
2685 p_attribute5 => l_attribute_rec.attribute5,
2686 p_attribute6 => l_attribute_rec.attribute6,
2687 p_attribute7 => l_attribute_rec.attribute7,
2688 p_attribute8 => l_attribute_rec.attribute8,
2689 p_attribute9 => l_attribute_rec.attribute9,
2690 p_attribute10 => l_attribute_rec.attribute10,
2691 p_attribute11 => l_attribute_rec.attribute11,
2692 p_attribute12 => l_attribute_rec.attribute12,
2693 p_attribute13 => l_attribute_rec.attribute13,
2694 p_attribute14 => l_attribute_rec.attribute14,
2695 p_attribute15 => l_attribute_rec.attribute15,
2696 p_created_by => l_attribute_rec.created_by,
2697 p_creation_date => l_attribute_rec.creation_date,
2698 p_last_updated_by => l_attribute_rec.last_updated_by,
2699 p_last_update_date => l_attribute_rec.last_update_date,
2700 p_last_update_login => l_attribute_rec.last_update_login,
2701
2702 p_name => l_attribute_tl_rec.name,
2703 p_attribute_label_long => l_attribute_tl_rec.attribute_label_long,
2704 p_attribute_label_short =>l_attribute_tl_rec.attribute_label_short,
2705 p_description => l_attribute_tl_rec.description,
2706 p_loader_timestamp => p_loader_timestamp,
2707 p_pass => p_pass,
2708 p_copy_redo_flag => l_copy_redo_flag
2709 );
2710 end if; /* l_user_id */
2711 -- update all records --
2712 --
2713 -- Update record only if Update mode is set to true
2714 --
2715 elsif (AK_UPLOAD_GRP.G_UPDATE_MODE) then
2716 AK_ATTRIBUTE_PVT.UPDATE_ATTRIBUTE (
2717 p_validation_level => p_validation_level,
2718 p_api_version_number => 1.0,
2719 p_msg_count => l_msg_count,
2720 p_msg_data => l_msg_data,
2721 p_return_status => l_return_status,
2722 p_attribute_application_id =>
2723 l_attribute_rec.attribute_application_id,
2724 p_attribute_code => l_attribute_rec.attribute_code,
2725 p_attribute_label_length => l_attribute_rec.attribute_label_length,
2726 p_attribute_value_length => l_attribute_rec.attribute_value_length,
2727 p_bold => l_attribute_rec.bold,
2728 p_italic => l_attribute_rec.italic,
2729 p_vertical_alignment => l_attribute_rec.vertical_alignment,
2730 p_horizontal_alignment => l_attribute_rec.horizontal_alignment,
2731 p_data_type => l_attribute_rec.data_type,
2732 p_precision => l_attribute_rec.precision,
2733 p_upper_case_flag => l_attribute_rec.upper_case_flag,
2734 p_default_value_varchar2 => l_attribute_rec.default_value_varchar2,
2735 p_default_value_number => l_attribute_rec.default_value_number,
2736 p_default_value_date => l_attribute_rec.default_value_date,
2737 p_lov_region_application_id =>
2738 l_attribute_rec.lov_region_application_id,
2739 p_lov_region_code => l_attribute_rec.lov_region_code,
2740 p_item_style => l_attribute_rec.item_style,
2741 p_display_height => l_attribute_rec.display_height,
2742 p_css_class_name => l_attribute_rec.css_class_name,
2743 p_poplist_viewobject => l_attribute_rec.poplist_viewobject,
2744 p_poplist_display_attr => l_attribute_rec.poplist_display_attribute,
2745 p_poplist_value_attr => l_attribute_rec.poplist_value_attribute,
2746 p_css_label_class_name => l_attribute_rec.css_label_class_name,
2747 p_expansion => l_attribute_rec.expansion,
2748 p_als_max_length => l_attribute_rec.als_max_length,
2749 p_attribute_category => l_attribute_rec.attribute_category,
2750 p_attribute1 => l_attribute_rec.attribute1,
2751 p_attribute2 => l_attribute_rec.attribute2,
2752 p_attribute3 => l_attribute_rec.attribute3,
2753 p_attribute4 => l_attribute_rec.attribute4,
2754 p_attribute5 => l_attribute_rec.attribute5,
2755 p_attribute6 => l_attribute_rec.attribute6,
2756 p_attribute7 => l_attribute_rec.attribute7,
2757 p_attribute8 => l_attribute_rec.attribute8,
2758 p_attribute9 => l_attribute_rec.attribute9,
2759 p_attribute10 => l_attribute_rec.attribute10,
2760 p_attribute11 => l_attribute_rec.attribute11,
2761 p_attribute12 => l_attribute_rec.attribute12,
2762 p_attribute13 => l_attribute_rec.attribute13,
2763 p_attribute14 => l_attribute_rec.attribute14,
2764 p_attribute15 => l_attribute_rec.attribute15,
2765 p_created_by => l_attribute_rec.created_by,
2766 p_creation_date => l_attribute_rec.creation_date,
2767 p_last_updated_by => l_attribute_rec.last_updated_by,
2768 p_last_update_date => l_attribute_rec.last_update_date,
2769 p_last_update_login => l_attribute_rec.last_update_login,
2770 p_name => l_attribute_tl_rec.name,
2771 p_attribute_label_long => l_attribute_tl_rec.attribute_label_long,
2772 p_attribute_label_short =>l_attribute_tl_rec.attribute_label_short,
2773 p_description => l_attribute_tl_rec.description,
2774 p_loader_timestamp => p_loader_timestamp,
2775 p_pass => p_pass,
2776 p_copy_redo_flag => l_copy_redo_flag
2777 );
2778 end if; -- /* if G_UPDATE_MODE G_NC_UPDATE_MODE*/
2779 else
2780 AK_ATTRIBUTE_PVT.CREATE_ATTRIBUTE (
2781 p_validation_level => p_validation_level,
2782 p_api_version_number => 1.0,
2783 p_msg_count => l_msg_count,
2784 p_msg_data => l_msg_data,
2785 p_return_status => l_return_status,
2786 p_attribute_application_id =>
2787 l_attribute_rec.attribute_application_id,
2788 p_attribute_code => l_attribute_rec.attribute_code,
2789 p_attribute_label_length => l_attribute_rec.attribute_label_length,
2790 p_attribute_value_length => l_attribute_rec.attribute_value_length,
2791 p_bold => l_attribute_rec.bold,
2792 p_italic => l_attribute_rec.italic,
2793 p_vertical_alignment => l_attribute_rec.vertical_alignment,
2794 p_horizontal_alignment => l_attribute_rec.horizontal_alignment,
2795 p_data_type => l_attribute_rec.data_type,
2796 p_precision => l_attribute_rec.precision,
2797 p_upper_case_flag => l_attribute_rec.upper_case_flag,
2798 p_default_value_varchar2 => l_attribute_rec.default_value_varchar2,
2799 p_default_value_number => l_attribute_rec.default_value_number,
2800 p_default_value_date => l_attribute_rec.default_value_date,
2801 p_lov_region_application_id =>
2802 l_attribute_rec.lov_region_application_id,
2803 p_lov_region_code => l_attribute_rec.lov_region_code,
2804 p_item_style => l_attribute_rec.item_style,
2805 p_display_height => l_attribute_rec.display_height,
2806 p_css_class_name => l_attribute_rec.css_class_name,
2807 p_poplist_viewobject => l_attribute_rec.poplist_viewobject,
2808 p_poplist_display_attr => l_attribute_rec.poplist_display_attribute,
2809 p_poplist_value_attr => l_attribute_rec.poplist_value_attribute,
2810 p_css_label_class_name => l_attribute_rec.css_label_class_name,
2811 p_expansion => l_attribute_rec.expansion,
2812 p_als_max_length => l_attribute_rec.als_max_length,
2813 p_attribute_category => l_attribute_rec.attribute_category,
2814 p_attribute1 => l_attribute_rec.attribute1,
2815 p_attribute2 => l_attribute_rec.attribute2,
2816 p_attribute3 => l_attribute_rec.attribute3,
2817 p_attribute4 => l_attribute_rec.attribute4,
2818 p_attribute5 => l_attribute_rec.attribute5,
2819 p_attribute6 => l_attribute_rec.attribute6,
2820 p_attribute7 => l_attribute_rec.attribute7,
2821 p_attribute8 => l_attribute_rec.attribute8,
2822 p_attribute9 => l_attribute_rec.attribute9,
2823 p_attribute10 => l_attribute_rec.attribute10,
2824 p_attribute11 => l_attribute_rec.attribute11,
2825 p_attribute12 => l_attribute_rec.attribute12,
2826 p_attribute13 => l_attribute_rec.attribute13,
2827 p_attribute14 => l_attribute_rec.attribute14,
2828 p_attribute15 => l_attribute_rec.attribute15,
2829 p_created_by => l_attribute_rec.created_by,
2830 p_creation_date => l_attribute_rec.creation_date,
2831 p_last_updated_by => l_attribute_rec.last_updated_by,
2832 p_last_update_date => l_attribute_rec.last_update_date,
2833 p_last_update_login => l_attribute_rec.last_update_login,
2834 p_name => l_attribute_tl_rec.name,
2835 p_attribute_label_long => l_attribute_tl_rec.attribute_label_long,
2836 p_attribute_label_short =>l_attribute_tl_rec.attribute_label_short,
2837 p_description => l_attribute_tl_rec.description,
2838 p_loader_timestamp => p_loader_timestamp,
2839 p_pass => p_pass,
2840 p_copy_redo_flag => l_copy_redo_flag
2841 );
2842 end if; -- /* if ATTRIBUTE_EXISTS */
2843 --
2844 -- If API call returns with an error status, upload aborts
2845 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2846 (l_return_status = FND_API.G_RET_STS_ERROR) then
2847 RAISE FND_API.G_EXC_ERROR;
2848 end if; -- /* if l_return_status */
2849 --
2850 -- if validation fails, then this record should go to second pass
2851 if (l_copy_redo_flag) then
2852 G_ATTRIBUTE_REDO_INDEX := G_ATTRIBUTE_REDO_INDEX + 1;
2853 G_ATTRIBUTE_REDO_TBL(G_ATTRIBUTE_REDO_INDEX) := l_attribute_rec;
2854 G_ATTRIBUTE_TL_REDO_TBL(G_ATTRIBUTE_REDO_INDEX) := l_attribute_tl_rec;
2855 l_copy_redo_flag := FALSE;
2856 end if; --/* if l_copy_redo_flag */
2857 l_state := 0;
2858 else
2859 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2860 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
2861 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
2862 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
2863 FND_MESSAGE.SET_TOKEN('EXPECTED','ATTRIBUTE');
2864 FND_MSG_PUB.Add;
2865 end if;
2866 raise FND_API.G_EXC_ERROR;
2867 end if;
2868 end if;
2869
2870 --
2871 -- Get rid of leading white spaces, so that buffer would become
2872 -- null if the only thing in it are white spaces
2873 --
2874 l_buffer := LTRIM(l_buffer);
2875
2876 --
2877 -- Get the next non-blank, non-comment line if current line is
2878 -- fully parsed
2879 --
2880 while (l_buffer is null and l_eof_flag = 'N' and p_index <= AK_ON_OBJECTS_PVT.G_UPL_TABLE_NUM) loop
2881 AK_ON_OBJECTS_PVT.READ_LINE (
2882 p_return_status => l_return_status,
2883 p_index => p_index,
2884 p_buffer => l_buffer,
2885 p_lines_read => l_lines_read,
2886 p_eof_flag => l_eof_flag,
2887 p_upl_loader_cur => p_upl_loader_cur
2888 );
2889
2890 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2891 (l_return_status = FND_API.G_RET_STS_ERROR) then
2892 RAISE FND_API.G_EXC_ERROR;
2893 end if;
2894 l_line_num := l_line_num + l_lines_read;
2895 --
2896 -- trim leading spaces and discard comment lines
2897 --
2898 l_buffer := LTRIM(l_buffer);
2899 if (SUBSTR(l_buffer, 1, 1) = '#') then
2900 l_buffer := null;
2901 end if;
2902 end loop;
2903
2904 end LOOP;
2905
2906 -- If the loops end in a state other then at the end of an attribute
2907 -- (state 0) or when the beginning of another business object was
2908 -- detected, then the file must have ended prematurely, which is an error
2909 --
2910 if (l_state <> 0) and (l_more_attr) then
2911 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2912 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
2913 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
2914 FND_MESSAGE.SET_TOKEN('TOKEN','END OF FILE');
2915 FND_MESSAGE.SET_TOKEN('EXPECTED',null);
2916 FND_MSG_PUB.Add;
2917 end if;
2918 --dbms_output.put_line('Unexpected END OF FILE: state is ' ||
2919 -- to_char(l_state));
2920 raise FND_API.G_EXC_ERROR;
2921 end if;
2922
2923 --
2924 -- Load line number of the last file line processed
2925 --
2926 p_line_num_out := l_line_num;
2927
2928 p_return_status := FND_API.G_RET_STS_SUCCESS;
2929
2930 -- dbms_output.put_line('Leaving attribute upload: ' ||
2931 -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
2932
2933 EXCEPTION
2934 WHEN FND_API.G_EXC_ERROR THEN
2935 p_return_status := FND_API.G_RET_STS_ERROR;
2936 WHEN VALUE_ERROR THEN
2937 p_return_status := FND_API.G_RET_STS_ERROR;
2938 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_VALUE_ERROR');
2939 FND_MESSAGE.SET_TOKEN('KEY',to_char(l_attribute_rec.attribute_application_id) ||
2940 ' ' || l_attribute_rec.attribute_code);
2941 FND_MSG_PUB.Add;
2942 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2943 SUBSTR (SQLERRM, 1, 240)||': '||l_column||'='||l_token );
2944 FND_MSG_PUB.Add;
2945 WHEN OTHERS THEN
2946 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2947 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2948 SUBSTR (SQLERRM, 1, 240) );
2949 FND_MSG_PUB.Add;
2950 end UPLOAD_ATTRIBUTE;
2951
2952 --=======================================================
2953 -- Procedure INSERT_ATTRIBUTE_PK_TABLE
2954 --
2955 -- Usage Private API for inserting the given attribute's
2956 -- primary key value into the given attribute
2957 -- table.
2958 -- This API should only be called by other APIs
2959 -- that are owned by the Core Modules Team (AK).
2960 --
2961 -- Desc This API inserts the given attribute primary
2962 -- key value into a given attribute table
2963 -- (of type Attribute_PK_Tbl_Type) only if the
2964 -- primary key does not already exist in the table.
2965 --
2966 -- Results The API returns the standard p_return_status parameter
2967 -- indicating one of the standard return statuses :
2968 -- * Unexpected error
2969 -- * Error
2970 -- * Success
2971 -- Parameters p_attribute_application_id : IN required
2972 -- Application ID of the attribute to be inserted to the
2973 -- table.
2974 -- p_attribute_code : IN required
2975 -- Application code of the attribute to be inserted to the
2976 -- table.
2977 -- p_attribute_pk_tbl : IN OUT
2978 -- Attribute table to be updated.
2979 --
2980 -- Version Initial version number = 1.0
2981 -- History Current version number = 1.0
2982 --=======================================================
2983 procedure INSERT_ATTRIBUTE_PK_TABLE (
2984 p_return_status OUT NOCOPY VARCHAR2,
2985 p_attribute_application_id IN NUMBER,
2986 p_attribute_code IN VARCHAR2,
2987 p_attribute_pk_tbl IN OUT NOCOPY AK_ATTRIBUTE_PUB.Attribute_PK_Tbl_Type
2988 ) is
2989 l_api_version_number CONSTANT number := 1.0;
2990 l_api_name CONSTANT varchar2(30) := 'Insert_Attribute_PK_Table';
2991 l_index NUMBER;
2992 begin
2993 --
2994 -- if table is empty, just insert the attribute primary key into it
2995 --
2996 if (p_attribute_pk_tbl.count = 0) then
2997 p_attribute_pk_tbl(1).attribute_appl_id := p_attribute_application_id;
2998 p_attribute_pk_tbl(1).attribute_code := p_attribute_code;
2999 return;
3000 end if;
3001
3002 --
3003 -- otherwise, insert the attribute to the end of the table if it is
3004 -- not already in the table. If it is already in the table, return
3005 -- without changing the table.
3006 --
3007 for l_index in p_attribute_pk_tbl.FIRST .. p_attribute_pk_tbl.LAST loop
3008 if (p_attribute_pk_tbl.exists(l_index)) then
3009 if (p_attribute_pk_tbl(l_index).attribute_appl_id = p_attribute_application_id)
3010 and
3011 (p_attribute_pk_tbl(l_index).attribute_code = p_attribute_code) then
3012 return;
3013 end if;
3014 end if;
3015 end loop;
3016
3017 l_index := p_attribute_pk_tbl.LAST + 1;
3018 p_attribute_pk_tbl(l_index).attribute_appl_id := p_attribute_application_id;
3019 p_attribute_pk_tbl(l_index).attribute_code := p_attribute_code;
3020
3021 EXCEPTION
3022 WHEN FND_API.G_EXC_ERROR THEN
3023 p_return_status := FND_API.G_RET_STS_ERROR;
3024 WHEN OTHERS THEN
3025 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3026 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3027 SUBSTR (SQLERRM, 1, 240) );
3028 FND_MSG_PUB.Add;
3029 end INSERT_ATTRIBUTE_PK_TABLE;
3030
3031 --=======================================================
3032 -- Procedure UPDATE_ATTRIBUTE
3033 --
3034 -- Usage Private API for updating an attribute.
3035 -- This API should only be called by other APIs
3036 -- that are owned by the Core Modules Team (AK).
3037 --
3038 -- Desc This API updates an attribute using the given info
3039 --
3040 -- Results The API returns the standard p_return_status parameter
3041 -- indicating one of the standard return statuses :
3042 -- * Unexpected error
3043 -- * Error
3044 -- * Success
3045 -- Parameters Attribute columns
3046 -- p_loader_timestamp : IN optional
3047 -- If a timestamp is passed, the API will update the
3048 -- record using this timestamp. Only the upload API
3049 -- should call with this parameter loaded.
3050 -- p_temp_redo_tbl: IN required
3051 -- For saving records temporarily to see if it
3052 -- fails in first pass of upload. If it does,
3053 -- then the record is saved for second pass
3054 --
3055 -- Version Initial version number = 1.0
3056 -- History Current version number = 1.0
3057 --=======================================================
3058 procedure UPDATE_ATTRIBUTE (
3059 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3060 p_api_version_number IN NUMBER,
3061 p_init_msg_tbl IN BOOLEAN := FALSE,
3062 p_msg_count OUT NOCOPY NUMBER,
3063 p_msg_data OUT NOCOPY VARCHAR2,
3064 p_return_status OUT NOCOPY VARCHAR2,
3065 p_attribute_application_id IN NUMBER,
3066 p_attribute_code IN VARCHAR2,
3067 p_attribute_label_length IN NUMBER := FND_API.G_MISS_NUM,
3068 p_attribute_value_length IN NUMBER := FND_API.G_MISS_NUM,
3069 p_bold IN VARCHAR2 := FND_API.G_MISS_CHAR,
3070 p_italic IN VARCHAR2 := FND_API.G_MISS_CHAR,
3071 p_vertical_alignment IN VARCHAR2 := FND_API.G_MISS_CHAR,
3072 p_horizontal_alignment IN VARCHAR2 := FND_API.G_MISS_CHAR,
3073 p_data_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
3074 p_upper_case_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
3075 p_default_value_varchar2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3076 p_default_value_number IN NUMBER := FND_API.G_MISS_NUM,
3077 p_default_value_date IN DATE := FND_API.G_MISS_DATE,
3078 p_lov_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
3079 p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
3080 p_item_style IN VARCHAR2 := FND_API.G_MISS_CHAR,
3081 p_display_height IN NUMBER := FND_API.G_MISS_NUM,
3082 p_css_class_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
3083 p_poplist_viewobject IN VARCHAR2 := FND_API.G_MISS_CHAR,
3084 p_poplist_display_attr IN VARCHAR2 := FND_API.G_MISS_CHAR,
3085 p_poplist_value_attr IN VARCHAR2 := FND_API.G_MISS_CHAR,
3086 p_css_label_class_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
3087 p_precision IN NUMBER := FND_API.G_MISS_NUM,
3088 p_expansion IN NUMBER := FND_API.G_MISS_NUM,
3089 p_als_max_length IN NUMBER := FND_API.G_MISS_NUM,
3090 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
3091 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3092 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3093 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3094 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3095 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3096 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3097 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3098 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3099 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3100 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3101 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3102 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3103 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3104 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3105 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3106 p_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
3107 p_attribute_label_long IN VARCHAR2 := FND_API.G_MISS_CHAR,
3108 p_attribute_label_short IN VARCHAR2 := FND_API.G_MISS_CHAR,
3109 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
3110 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
3111 p_creation_date IN DATE := FND_API.G_MISS_DATE,
3112 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
3113 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
3114 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
3115 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
3116 p_pass IN NUMBER,
3117 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
3118 ) is
3119 cursor l_get_row_csr is
3120 select *
3121 from AK_ATTRIBUTES
3122 where ATTRIBUTE_APPLICATION_ID = p_attribute_application_id
3123 and ATTRIBUTE_CODE = p_attribute_code
3124 for update of ATTRIBUTE_APPLICATION_ID;
3125 cursor l_get_tl_row_csr (lang_parm varchar2) is
3126 select *
3127 from AK_ATTRIBUTES_TL
3128 where ATTRIBUTE_APPLICATION_ID = p_attribute_application_id
3129 and ATTRIBUTE_CODE = p_attribute_code
3130 and LANGUAGE = lang_parm
3131 for update of ATTRIBUTE_APPLICATION_ID;
3132 cursor l_check_navigation_csr is
3133 select 1
3134 from AK_OBJECT_ATTRIBUTE_NAVIGATION
3135 where ATTRIBUTE_APPLICATION_ID = p_attribute_application_id
3136 and ATTRIBUTE_CODE = p_attribute_code;
3137 cursor l_check_attr_value_csr is
3138 select 1
3139 from AK_INST_ATTRIBUTE_VALUES
3140 where ATTRIBUTE_APPLICATION_ID = p_attribute_application_id
3141 and ATTRIBUTE_CODE = p_attribute_code;
3142 cursor l_check_page_region_item_csr is
3143 select 1
3144 from AK_FLOW_PAGE_REGION_ITEMS ri
3145 where ri.to_url_attribute_appl_id = p_attribute_application_id
3146 and ri.to_url_attribute_code = p_attribute_code;
3147 l_api_version_number CONSTANT number := 1.0;
3148 l_api_name CONSTANT varchar2(30) := 'Update_Attribute';
3149 l_attributes_rec ak_attributes%ROWTYPE;
3150 l_attributes_tl_rec ak_attributes_tl%ROWTYPE;
3151 l_created_by number;
3152 l_creation_date date;
3153 l_dummy number;
3154 l_error boolean;
3155 l_lang varchar2(30);
3156 l_last_update_date date;
3157 l_last_update_login number;
3158 l_last_updated_by number;
3159 l_return_status varchar2(1);
3160 l_item_style varchar2(30) := 'TEXT';
3161 l_file_version number;
3162 begin
3163 IF NOT FND_API.Compatible_API_Call (
3164 l_api_version_number, p_api_version_number, l_api_name,
3165 G_PKG_NAME) then
3166 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3167 return;
3168 END IF;
3169
3170 -- Initialize the message table if requested.
3171
3172 if p_init_msg_tbl then
3173 FND_MSG_PUB.initialize;
3174 end if;
3175
3176 savepoint start_update_attribute;
3177
3178 select userenv('LANG') into l_lang
3179 from dual;
3180
3181 --
3182 -- retrieve ak_attributes row if it exists
3183 --
3184 open l_get_row_csr;
3185 fetch l_get_row_csr into l_attributes_rec;
3186 if (l_get_row_csr%notfound) then
3187 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3188 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_DOES_NOT_EXIST');
3189 FND_MSG_PUB.Add;
3190 end if;
3191 close l_get_row_csr;
3192 raise FND_API.G_EXC_ERROR;
3193 end if;
3194 close l_get_row_csr;
3195
3196 --
3197 -- retrieve ak_attributes_tl row if it exists
3198 --
3199 open l_get_tl_row_csr(l_lang);
3200 fetch l_get_tl_row_csr into l_attributes_tl_rec;
3201 if (l_get_tl_row_csr%notfound) then
3202 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3203 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_DOES_NOT_EXIST');
3204 FND_MSG_PUB.Add;
3205 end if;
3206 -- dbms_output.put_line('Error - TL Row does not exist');
3207 close l_get_tl_row_csr;
3208 raise FND_API.G_EXC_ERROR;
3209 end if;
3210 close l_get_tl_row_csr;
3211
3212 --
3213 -- validate table columns passed in
3214 --
3215 if (p_validation_level <> FND_API.G_VALID_LEVEL_NONE) then
3216 if not VALIDATE_ATTRIBUTE (
3217 p_validation_level => p_validation_level,
3218 p_api_version_number => 1.0,
3219 p_return_status => l_return_status,
3220 p_attribute_application_id => p_attribute_application_id,
3221 p_attribute_code => p_attribute_code,
3222 p_attribute_label_length => p_attribute_label_length,
3223 p_attribute_value_length => p_attribute_value_length,
3224 p_bold => p_bold,
3225 p_italic => p_italic,
3226 p_vertical_alignment => p_vertical_alignment,
3227 p_horizontal_alignment => p_horizontal_alignment,
3228 p_data_type => p_data_type,
3229 p_upper_case_flag => p_upper_case_flag,
3230 p_default_value_varchar2 => p_default_value_varchar2,
3231 p_default_value_number => p_default_value_number,
3232 p_default_value_date => p_default_value_date,
3233 p_lov_region_application_id => p_lov_region_application_id,
3234 p_lov_region_code => p_lov_region_code,
3235 p_name => p_name,
3236 p_attribute_label_long => p_attribute_label_long,
3237 p_attribute_label_short => p_attribute_label_short,
3238 p_description => p_description,
3239 p_caller => AK_ON_OBJECTS_PVT.G_UPDATE,
3240 p_pass => p_pass
3241 ) then
3242 if (p_pass = 1) then
3243 p_copy_redo_flag := TRUE;
3244 else
3245 raise FND_API.G_EXC_ERROR;
3246 end if; --/* if p_pass */
3247 end if;
3248 end if;
3249
3250 -- Do not update if fails validation ie. p_copy_redo_flag = true
3251 if (not p_copy_redo_flag) then
3252
3253 --** Additional validation logic for update **
3254
3255 -- - Cannot change data_type if there are attribute navigation,
3256 -- attribute value, or page region item data for this attribute
3257
3258 if ((p_data_type is not null) and (p_data_type <> FND_API.G_MISS_CHAR) and
3259 (p_data_type <> l_attributes_rec.data_type)) then
3260
3261 if (l_attributes_rec.data_type = 'URL') then
3262 /* see if any 'To URL attribute' in ak_flow_page_region_items */
3263 /* are using this URL-type object attribute */
3264 open l_check_page_region_item_csr;
3265 fetch l_check_page_region_item_csr into l_dummy;
3266 if (l_check_page_region_item_csr%found) then
3267 close l_check_page_region_item_csr;
3268 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3269 FND_MESSAGE.SET_NAME('AK','AK_CHANGE_DATA_TYPE');
3270 FND_MESSAGE.SET_TOKEN('FORM','AK_PAGE_REGION_LINKS', TRUE);
3271 FND_MSG_PUB.Add;
3272 end if;
3273 -- dbms_output.put_line(l_api_name || ' Cannot change data type');
3274 raise FND_API.G_EXC_ERROR;
3275 end if;
3276 close l_check_page_region_item_csr;
3277 else
3278 /* see if there are any attribute navigation for this non-URL type */
3279 /* object attribute */
3280 open l_check_navigation_csr;
3281 fetch l_check_navigation_csr into l_dummy;
3282 if (l_check_navigation_csr%found) then
3283 close l_check_navigation_csr;
3284 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3285 FND_MESSAGE.SET_NAME('AK','AK_CHANGE_DATA_TYPE');
3286 FND_MESSAGE.SET_TOKEN('FORM','AK_ATTRIBUTE_NAVIGATION', TRUE);
3287 FND_MSG_PUB.Add;
3288 end if;
3289 -- dbms_output.put_line(l_api_name || ' Cannot change data type');
3290 raise FND_API.G_EXC_ERROR;
3291 end if;
3292 close l_check_navigation_csr;
3293
3294 /* see if there are any attribute values for this non-URL type */
3295 /* object attribute */
3296 open l_check_attr_value_csr;
3297 fetch l_check_attr_value_csr into l_dummy;
3298 if (l_check_attr_value_csr%found) then
3299 close l_check_attr_value_csr;
3300 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3301 FND_MESSAGE.SET_NAME('AK','AK_CHANGE_DATA_TYPE');
3302 FND_MESSAGE.SET_TOKEN('FORM','AK_ATTRIBUTE_VALUE', TRUE);
3303 FND_MSG_PUB.Add;
3304 end if;
3305 raise FND_API.G_EXC_ERROR;
3306 end if;
3307 close l_check_attr_value_csr;
3308 end if;
3309 end if;
3310
3311 --
3312 -- Load record to be updated to the database
3313 -- - first load nullable columns
3314 --
3315 if (p_upper_case_flag <> FND_API.G_MISS_CHAR) or
3316 (p_upper_case_flag is null) then
3317 l_attributes_rec.upper_case_flag := p_upper_case_flag;
3318 end if;
3319 if (p_default_value_varchar2 <> FND_API.G_MISS_CHAR) or
3320 (p_default_value_varchar2 is null) then
3321 l_attributes_rec.default_value_varchar2 := p_default_value_varchar2;
3322 end if;
3323 if (p_default_value_number <> FND_API.G_MISS_NUM) or
3324 (p_default_value_number is null) then
3325 l_attributes_rec.default_value_number := p_default_value_number;
3326 end if;
3327 if (p_default_value_date <> FND_API.G_MISS_DATE) or
3328 (p_default_value_date is null) then
3329 l_attributes_rec.default_value_date := p_default_value_date;
3330 end if;
3331 if (p_lov_region_application_id <> FND_API.G_MISS_NUM) or
3332 (p_lov_region_application_id is null) then
3333 l_attributes_rec.lov_region_application_id := p_lov_region_application_id;
3334 end if;
3335 if (p_lov_region_code <> FND_API.G_MISS_CHAR) or
3336 (p_lov_region_code is null) then
3337 l_attributes_rec.lov_region_code := p_lov_region_code;
3338 end if;
3339 -- JSP new columns
3340 if (p_item_style <> FND_API.G_MISS_CHAR) and
3341 (p_item_style is not null) then
3342 l_attributes_rec.item_style := p_item_style;
3343 else
3344 l_attributes_rec.item_style := l_item_style;
3345 end if;
3346 if (p_display_height <> FND_API.G_MISS_NUM) or
3347 (p_display_height is null) then
3348 l_attributes_rec.display_height := p_display_height;
3349 end if;
3350 if (p_css_class_name <> FND_API.G_MISS_CHAR) or
3351 (p_css_class_name is null) then
3352 l_attributes_rec.css_class_name := p_css_class_name;
3353 end if;
3354 if (p_poplist_viewobject <> FND_API.G_MISS_CHAR) or
3355 (p_poplist_viewobject is null) then
3356 l_attributes_rec.poplist_viewobject := p_poplist_viewobject;
3357 end if;
3358 if (p_poplist_display_attr <> FND_API.G_MISS_CHAR) or
3359 (p_poplist_display_attr is null) then
3360 l_attributes_rec.poplist_display_attribute := p_poplist_display_attr;
3361 end if;
3362 if (p_poplist_value_attr <> FND_API.G_MISS_CHAR) or
3363 (p_poplist_value_attr is null) then
3364 l_attributes_rec.poplist_value_attribute := p_poplist_value_attr;
3365 end if;
3366 if (p_css_label_class_name <> FND_API.G_MISS_CHAR) or
3367 (p_css_label_class_name is null) then
3368 l_attributes_rec.css_label_class_name := p_css_label_class_name;
3369 end if;
3370 if (p_precision <> FND_API.G_MISS_NUM) or
3371 (p_precision is null) then
3372 l_attributes_rec.precision := p_precision;
3373 end if;
3374 if (p_expansion <> FND_API.G_MISS_NUM) or
3375 (p_expansion is null) then
3376 l_attributes_rec.expansion := p_expansion;
3377 end if;
3378 if (p_als_max_length <> FND_API.G_MISS_NUM) or
3379 (p_als_max_length is null) then
3380 l_attributes_rec.als_max_length := p_als_max_length;
3381 end if;
3382
3383 if (p_attribute_category <> FND_API.G_MISS_CHAR) or
3384 (p_attribute_category is null) then
3385 l_attributes_rec.attribute_category := p_attribute_category;
3386 end if;
3387 if (p_attribute1 <> FND_API.G_MISS_CHAR) or
3388 (p_attribute1 is null) then
3389 l_attributes_rec.attribute1 := p_attribute1;
3390 end if;
3391 if (p_attribute2 <> FND_API.G_MISS_CHAR) or
3392 (p_attribute2 is null) then
3393 l_attributes_rec.attribute2 := p_attribute2;
3394 end if;
3395 if (p_attribute3 <> FND_API.G_MISS_CHAR) or
3396 (p_attribute3 is null) then
3397 l_attributes_rec.attribute3 := p_attribute3;
3398 end if;
3399 if (p_attribute4 <> FND_API.G_MISS_CHAR) or
3400 (p_attribute4 is null) then
3401 l_attributes_rec.attribute4 := p_attribute4;
3402 end if;
3403 if (p_attribute5 <> FND_API.G_MISS_CHAR) or
3404 (p_attribute5 is null) then
3405 l_attributes_rec.attribute5 := p_attribute5;
3406 end if;
3407 if (p_attribute6 <> FND_API.G_MISS_CHAR) or
3408 (p_attribute6 is null) then
3409 l_attributes_rec.attribute6 := p_attribute6;
3410 end if;
3411 if (p_attribute7 <> FND_API.G_MISS_CHAR) or
3412 (p_attribute7 is null) then
3413 l_attributes_rec.attribute7 := p_attribute7;
3414 end if;
3415 if (p_attribute8 <> FND_API.G_MISS_CHAR) or
3416 (p_attribute8 is null) then
3417 l_attributes_rec.attribute8 := p_attribute8;
3418 end if;
3419 if (p_attribute9 <> FND_API.G_MISS_CHAR) or
3420 (p_attribute9 is null) then
3421 l_attributes_rec.attribute9 := p_attribute9;
3422 end if;
3423 if (p_attribute10 <> FND_API.G_MISS_CHAR) or
3424 (p_attribute10 is null) then
3425 l_attributes_rec.attribute10 := p_attribute10;
3426 end if;
3427 if (p_attribute11 <> FND_API.G_MISS_CHAR) or
3428 (p_attribute11 is null) then
3429 l_attributes_rec.attribute11 := p_attribute11;
3430 end if;
3431 if (p_attribute12 <> FND_API.G_MISS_CHAR) or
3432 (p_attribute12 is null) then
3433 l_attributes_rec.attribute12 := p_attribute12;
3434 end if;
3435 if (p_attribute13 <> FND_API.G_MISS_CHAR) or
3436 (p_attribute13 is null) then
3437 l_attributes_rec.attribute13 := p_attribute13;
3438 end if;
3439 if (p_attribute14 <> FND_API.G_MISS_CHAR) or
3440 (p_attribute14 is null) then
3441 l_attributes_rec.attribute14 := p_attribute14;
3442 end if;
3443 if (p_attribute15 <> FND_API.G_MISS_CHAR) or
3444 (p_attribute15 is null) then
3445 l_attributes_rec.attribute15 := p_attribute15;
3446 end if;
3447 if (p_attribute_label_length <> FND_API.G_MISS_NUM) or
3448 (p_attribute_label_length is null) then
3449 l_attributes_rec.attribute_label_length := p_attribute_label_length;
3450 end if;
3451 if (p_attribute_value_length <> FND_API.G_MISS_NUM) or
3452 (p_attribute_value_length is null) then
3453 l_attributes_rec.attribute_value_length := p_attribute_value_length;
3454 end if;
3455 if (p_attribute_label_long <> FND_API.G_MISS_CHAR) or
3456 (p_attribute_label_long is null) then
3457 l_attributes_tl_rec.attribute_label_long := p_attribute_label_long;
3458 end if;
3459 if (p_attribute_label_short <> FND_API.G_MISS_CHAR) or
3460 (p_attribute_label_short is null) then
3461 l_attributes_tl_rec.attribute_label_short := p_attribute_label_short;
3462 end if;
3463 if (p_description <> FND_API.G_MISS_CHAR) or
3464 (p_description is null) then
3465 l_attributes_tl_rec.description := p_description;
3466 end if;
3467
3468 --
3469 -- - next, load non-null columns
3470 --
3471 if (p_bold <> FND_API.G_MISS_CHAR) then
3472 l_attributes_rec.bold := p_bold;
3473 end if;
3474 if (p_italic <> FND_API.G_MISS_CHAR) then
3475 l_attributes_rec.italic := p_italic;
3476 end if;
3477 if (p_vertical_alignment <> FND_API.G_MISS_CHAR) then
3478 l_attributes_rec.vertical_alignment := p_vertical_alignment;
3479 end if;
3480 if (p_horizontal_alignment <> FND_API.G_MISS_CHAR) then
3481 l_attributes_rec.horizontal_alignment := p_horizontal_alignment;
3482 end if;
3483 if (p_data_type <> FND_API.G_MISS_CHAR) then
3484 l_attributes_rec.data_type := p_data_type;
3485 end if;
3486 if (p_name <> FND_API.G_MISS_CHAR) then
3487 l_attributes_tl_rec.name := p_name;
3488 end if;
3489 if (p_created_by <> FND_API.G_MISS_NUM) then
3490 l_created_by := p_created_by;
3491 end if;
3492 if (p_creation_date <> FND_API.G_MISS_DATE) then
3493 l_creation_date := p_creation_date;
3494 end if;
3495 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
3496 l_last_updated_by := p_last_updated_by;
3497 end if;
3498 if (p_last_update_date <> FND_API.G_MISS_DATE) then
3499 l_last_update_date := p_last_update_date;
3500 end if;
3501 if (p_last_update_login <> FND_API.G_MISS_NUM) then
3502 l_last_update_login := p_last_update_login;
3503 end if;
3504
3505 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
3506 p_loader_timestamp => p_loader_timestamp,
3507 p_created_by => l_created_by,
3508 p_creation_date => l_creation_date,
3509 p_last_updated_by => l_last_updated_by,
3510 p_db_last_updated_by => l_attributes_rec.last_updated_by,
3511 p_last_update_date => l_last_update_date,
3512 p_db_last_update_date => l_attributes_rec.last_update_date,
3513 p_last_update_login => l_last_update_login,
3514 p_create_or_update => 'UPDATE') then
3515
3516 update AK_ATTRIBUTES set
3517 ATTRIBUTE_LABEL_LENGTH = l_attributes_rec.attribute_label_length,
3518 ATTRIBUTE_VALUE_LENGTH = l_attributes_rec.attribute_value_length,
3519 BOLD = l_attributes_rec.bold,
3520 ITALIC = l_attributes_rec.italic,
3521 VERTICAL_ALIGNMENT = l_attributes_rec.vertical_alignment,
3522 HORIZONTAL_ALIGNMENT = l_attributes_rec.horizontal_alignment,
3523 DATA_TYPE = l_attributes_rec.data_type,
3524 UPPER_CASE_FLAG = l_attributes_rec.upper_case_flag,
3525 DEFAULT_VALUE_VARCHAR2 = l_attributes_rec.default_value_varchar2,
3526 DEFAULT_VALUE_NUMBER = l_attributes_rec.default_value_number,
3527 DEFAULT_VALUE_DATE = l_attributes_rec.default_value_date,
3528 LOV_REGION_APPLICATION_ID = l_attributes_rec.lov_region_application_id,
3529 LOV_REGION_CODE = l_attributes_rec.lov_region_code,
3530 ITEM_STYLE = l_attributes_rec.item_style,
3531 DISPLAY_HEIGHT = l_attributes_rec.display_height,
3532 CSS_CLASS_NAME = l_attributes_rec.css_class_name,
3533 POPLIST_VIEWOBJECT = l_attributes_rec.poplist_viewobject,
3534 POPLIST_DISPLAY_ATTRIBUTE = l_attributes_rec.poplist_display_attribute,
3535 POPLIST_VALUE_ATTRIBUTE = l_attributes_rec.poplist_value_attribute,
3536 CSS_LABEL_CLASS_NAME = l_attributes_rec.css_label_class_name,
3537 PRECISION = l_attributes_rec.precision,
3538 EXPANSION = l_attributes_rec.expansion,
3539 ALS_MAX_LENGTH = l_attributes_rec.als_max_length,
3540 ATTRIBUTE_CATEGORY = l_attributes_rec.attribute_category,
3541 ATTRIBUTE1 = l_attributes_rec.attribute1,
3542 ATTRIBUTE2 = l_attributes_rec.attribute2,
3543 ATTRIBUTE3 = l_attributes_rec.attribute3,
3544 ATTRIBUTE4 = l_attributes_rec.attribute4,
3545 ATTRIBUTE5 = l_attributes_rec.attribute5,
3546 ATTRIBUTE6 = l_attributes_rec.attribute6,
3547 ATTRIBUTE7 = l_attributes_rec.attribute7,
3548 ATTRIBUTE8 = l_attributes_rec.attribute8,
3549 ATTRIBUTE9 = l_attributes_rec.attribute9,
3550 ATTRIBUTE10 = l_attributes_rec.attribute10,
3551 ATTRIBUTE11 = l_attributes_rec.attribute11,
3552 ATTRIBUTE12 = l_attributes_rec.attribute12,
3553 ATTRIBUTE13 = l_attributes_rec.attribute13,
3554 ATTRIBUTE14 = l_attributes_rec.attribute14,
3555 ATTRIBUTE15 = l_attributes_rec.attribute15,
3556 LAST_UPDATE_DATE = l_last_update_date,
3557 LAST_UPDATED_BY = l_last_updated_by,
3558 LAST_UPDATE_LOGIN = l_last_update_login
3559 where attribute_application_id = p_attribute_application_id
3560 and attribute_code = p_attribute_code;
3561 if (sql%notfound) then
3562 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3563 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_UPDATE_FAILED');
3564 FND_MSG_PUB.Add;
3565 end if;
3566 raise FND_API.G_EXC_ERROR;
3567 end if;
3568
3569 update AK_ATTRIBUTES_TL set
3570 NAME = l_attributes_tl_rec.name,
3571 ATTRIBUTE_LABEL_LONG = l_attributes_tl_rec.attribute_label_long,
3572 ATTRIBUTE_LABEL_SHORT = l_attributes_tl_rec.attribute_label_short,
3573 DESCRIPTION = l_attributes_tl_rec.description,
3574 LAST_UPDATED_BY = l_last_updated_by,
3575 LAST_UPDATE_DATE = l_last_update_date,
3576 LAST_UPDATE_LOGIN = l_last_update_login,
3577 SOURCE_LANG = l_lang
3578 where attribute_application_id = p_attribute_application_id
3579 and attribute_code = p_attribute_code
3580 and l_lang in (LANGUAGE, SOURCE_LANG);
3581 if (sql%notfound) then
3582 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3583 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_UPDATE_FAILED');
3584 FND_MSG_PUB.Add;
3585 end if;
3586 -- dbms_output.put_line('TL Row does not exist during update');
3587 raise FND_API.G_EXC_ERROR;
3588 end if;
3589
3590 -- /** commit the update **/
3591 commit;
3592
3593 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
3594 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_UPDATED');
3595 FND_MESSAGE.SET_TOKEN('KEY',to_char(p_attribute_application_id) ||
3596 ' ' || p_attribute_code);
3597 FND_MSG_PUB.Add;
3598 end if;
3599
3600 end if;
3601
3602 end if; -- /* if not p_copy_redo_flag */
3603
3604 p_return_status := FND_API.G_RET_STS_SUCCESS;
3605
3606 FND_MSG_PUB.Count_And_Get (
3607 p_count => p_msg_count,
3608 p_data => p_msg_data);
3609
3610
3611 EXCEPTION
3612 WHEN VALUE_ERROR THEN
3613 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3614 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_VALUE_ERROR');
3615 FND_MESSAGE.SET_TOKEN('KEY',to_char(p_attribute_application_id) ||
3616 ' ' || p_attribute_code);
3617 FND_MSG_PUB.Add;
3618 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_NOT_UPDATED');
3619 FND_MESSAGE.SET_TOKEN('KEY',to_char(p_attribute_application_id) ||
3620 ' ' || p_attribute_code);
3621 FND_MSG_PUB.Add;
3622 end if;
3623 p_return_status := FND_API.G_RET_STS_ERROR;
3624 rollback to start_update_attribute;
3625 FND_MSG_PUB.Count_And_Get (
3626 p_count => p_msg_count,
3627 p_data => p_msg_data);
3628 WHEN FND_API.G_EXC_ERROR THEN
3629 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3630 FND_MESSAGE.SET_NAME('AK','AK_ATTRIBUTE_NOT_UPDATED');
3631 FND_MESSAGE.SET_TOKEN('KEY',to_char(p_attribute_application_id) ||
3632 ' ' || p_attribute_code);
3633 FND_MSG_PUB.Add;
3634 end if;
3635 p_return_status := FND_API.G_RET_STS_ERROR;
3636 rollback to start_update_attribute;
3637 FND_MSG_PUB.Count_And_Get (
3638 p_count => p_msg_count,
3639 p_data => p_msg_data);
3640 WHEN OTHERS THEN
3641 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3642 rollback to start_update_attribute;
3643 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3644 SUBSTR (SQLERRM, 1, 240) );
3645 FND_MSG_PUB.Count_And_Get (
3646 p_count => p_msg_count,
3647 p_data => p_msg_data);
3648 end UPDATE_ATTRIBUTE;
3649
3650 --=======================================================
3651 -- Procedure UPLOAD_ATTRIBUTE_SECOND
3652 --
3653 -- Usage Private API for loading attributes that were
3654 -- failed during its first pass
3655 -- This API should only be called by other APIs
3656 -- that are owned by the Core Modules Team (AK).
3657 --
3658 -- Desc This API reads the attribute data from PL/SQL table
3659 -- that was prepared during 1st pass, then processes
3660 -- the data, and loads them to the database. The tables
3661 -- are updated with the timestamp passed. This API
3662 -- will process the file until the EOF is reached,
3663 -- a parse error is encountered, or when data for
3664 -- a different business object is read from the file.
3665 --
3666 -- Results The API returns the standard p_return_status parameter
3667 -- indicating one of the standard return statuses :
3668 -- * Unexpected error
3669 -- * Error
3670 -- * Success
3671 -- Parameters p_validation_level : IN required
3672 -- validation level
3673 --
3674 -- Version Initial version number = 1.0
3675 -- History Current version number = 1.0
3676 --=======================================================
3677 procedure UPLOAD_ATTRIBUTE_SECOND (
3678 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3679 p_return_status OUT NOCOPY VARCHAR2,
3680 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
3681 p_pass IN NUMBER := 2
3682 ) is
3683 l_api_name CONSTANT varchar2(30) := 'Upload_Attribute_Second';
3684 l_rec_index NUMBER;
3685 l_return_status VARCHAR2(1);
3686 l_msg_count NUMBER;
3687 l_msg_data VARCHAR2(240);
3688 l_copy_redo_flag BOOLEAN;
3689 begin
3690 if (G_ATTRIBUTE_REDO_TBL.count > 0) then
3691 for l_rec_index in G_ATTRIBUTE_REDO_TBL.FIRST .. G_ATTRIBUTE_REDO_TBL.LAST loop
3692 if (G_ATTRIBUTE_REDO_TBL.exists(l_rec_index)) then
3693 if AK_ATTRIBUTE_PVT.ATTRIBUTE_EXISTS (
3694 p_api_version_number => 1.0,
3695 p_return_status => l_return_status,
3696 p_attribute_application_id=>
3697 G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute_application_id,
3698 p_attribute_code => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute_code) then
3699 AK_ATTRIBUTE_PVT.UPDATE_ATTRIBUTE (
3700 p_validation_level => p_validation_level,
3701 p_api_version_number => 1.0,
3702 p_msg_count => l_msg_count,
3703 p_msg_data => l_msg_data,
3704 p_return_status => l_return_status,
3705 p_attribute_application_id =>
3706 G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute_application_id,
3707 p_attribute_code => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute_code,
3708 p_attribute_label_length => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute_label_length,
3709 p_attribute_value_length => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute_value_length,
3710 p_bold => G_ATTRIBUTE_REDO_TBL(l_rec_index).bold,
3711 p_italic => G_ATTRIBUTE_REDO_TBL(l_rec_index).italic,
3712 p_vertical_alignment => G_ATTRIBUTE_REDO_TBL(l_rec_index).vertical_alignment,
3713 p_horizontal_alignment => G_ATTRIBUTE_REDO_TBL(l_rec_index).horizontal_alignment,
3714 p_data_type => G_ATTRIBUTE_REDO_TBL(l_rec_index).data_type,
3715 p_upper_case_flag => G_ATTRIBUTE_REDO_TBL(l_rec_index).upper_case_flag,
3716 p_default_value_varchar2 => G_ATTRIBUTE_REDO_TBL(l_rec_index).default_value_varchar2,
3717 p_default_value_number => G_ATTRIBUTE_REDO_TBL(l_rec_index).default_value_number,
3718 p_default_value_date => G_ATTRIBUTE_REDO_TBL(l_rec_index).default_value_date,
3719 p_lov_region_application_id =>
3720 G_ATTRIBUTE_REDO_TBL(l_rec_index).lov_region_application_id,
3721 p_lov_region_code => G_ATTRIBUTE_REDO_TBL(l_rec_index).lov_region_code,
3722 p_item_style => G_ATTRIBUTE_REDO_TBL(l_rec_index).item_style,
3723 p_display_height => G_ATTRIBUTE_REDO_TBL(l_rec_index).display_height,
3724 p_css_class_name => G_ATTRIBUTE_REDO_TBL(l_rec_index).css_class_name,
3725 p_poplist_viewobject => G_ATTRIBUTE_REDO_TBL(l_rec_index).poplist_viewobject,
3726 p_poplist_display_attr => G_ATTRIBUTE_REDO_TBL(l_rec_index).poplist_display_attribute,
3727 p_poplist_value_attr => G_ATTRIBUTE_REDO_TBL(l_rec_index).poplist_value_attribute,
3728 p_css_label_class_name => G_ATTRIBUTE_REDO_TBL(l_rec_index).css_label_class_name,
3729 p_precision => G_ATTRIBUTE_REDO_TBL(l_rec_index).precision,
3730 p_expansion => G_ATTRIBUTE_REDO_TBL(l_rec_index).expansion,
3731 p_als_max_length => G_ATTRIBUTE_REDO_TBL(l_rec_index).als_max_length,
3732 p_attribute_category => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute_category,
3733 p_attribute1 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute1,
3734 p_attribute2 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute2,
3735 p_attribute3 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute3,
3736 p_attribute4 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute4,
3737 p_attribute5 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute5,
3738 p_attribute6 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute6,
3739 p_attribute7 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute7,
3740 p_attribute8 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute8,
3741 p_attribute9 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute9,
3742 p_attribute10 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute10,
3743 p_attribute11 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute11,
3744 p_attribute12 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute12,
3745 p_attribute13 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute13,
3746 p_attribute14 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute14,
3747 p_attribute15 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute15,
3748 p_name => G_ATTRIBUTE_TL_REDO_TBL(l_rec_index).name,
3749 p_attribute_label_long => G_ATTRIBUTE_TL_REDO_TBL(l_rec_index).attribute_label_long,
3750 p_attribute_label_short => G_ATTRIBUTE_TL_REDO_TBL(l_rec_index).attribute_label_short,
3751 p_description => G_ATTRIBUTE_TL_REDO_TBL(l_rec_index).description,
3752 p_created_by => G_ATTRIBUTE_REDO_TBL(l_rec_index).created_by,
3753 p_creation_date => G_ATTRIBUTE_REDO_TBL(l_rec_index).creation_date,
3754 p_last_updated_by => G_ATTRIBUTE_REDO_TBL(l_rec_index).last_updated_by,
3755 p_last_update_date => G_ATTRIBUTE_REDO_TBL(l_rec_index).last_update_date,
3756 p_last_update_login => G_ATTRIBUTE_REDO_TBL(l_rec_index).last_update_login,
3757 p_loader_timestamp => p_loader_timestamp,
3758 p_pass => p_pass,
3759 p_copy_redo_flag => l_copy_redo_flag
3760 );
3761 else
3762 AK_ATTRIBUTE_PVT.CREATE_ATTRIBUTE (
3763 p_validation_level => p_validation_level,
3764 p_api_version_number => 1.0,
3765 p_msg_count => l_msg_count,
3766 p_msg_data => l_msg_data,
3767 p_return_status => l_return_status,
3768 p_attribute_application_id =>
3769 G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute_application_id,
3770 p_attribute_code => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute_code,
3771 p_attribute_label_length => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute_label_length,
3772 p_attribute_value_length => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute_value_length,
3773 p_bold => G_ATTRIBUTE_REDO_TBL(l_rec_index).bold,
3774 p_italic => G_ATTRIBUTE_REDO_TBL(l_rec_index).italic,
3775 p_vertical_alignment => G_ATTRIBUTE_REDO_TBL(l_rec_index).vertical_alignment,
3776 p_horizontal_alignment => G_ATTRIBUTE_REDO_TBL(l_rec_index).horizontal_alignment,
3777 p_data_type => G_ATTRIBUTE_REDO_TBL(l_rec_index).data_type,
3778 p_upper_case_flag => G_ATTRIBUTE_REDO_TBL(l_rec_index).upper_case_flag,
3779 p_default_value_varchar2 => G_ATTRIBUTE_REDO_TBL(l_rec_index).default_value_varchar2,
3780 p_default_value_number => G_ATTRIBUTE_REDO_TBL(l_rec_index).default_value_number,
3781 p_default_value_date => G_ATTRIBUTE_REDO_TBL(l_rec_index).default_value_date,
3782 p_lov_region_application_id =>
3783 G_ATTRIBUTE_REDO_TBL(l_rec_index).lov_region_application_id,
3784 p_lov_region_code => G_ATTRIBUTE_REDO_TBL(l_rec_index).lov_region_code,
3785 p_item_style => G_ATTRIBUTE_REDO_TBL(l_rec_index).item_style,
3786 p_display_height => G_ATTRIBUTE_REDO_TBL(l_rec_index).display_height,
3787 p_css_class_name => G_ATTRIBUTE_REDO_TBL(l_rec_index).css_class_name,
3788 p_poplist_viewobject => G_ATTRIBUTE_REDO_TBL(l_rec_index).poplist_viewobject,
3789 p_poplist_display_attr => G_ATTRIBUTE_REDO_TBL(l_rec_index).poplist_display_attribute,
3790 p_poplist_value_attr => G_ATTRIBUTE_REDO_TBL(l_rec_index).poplist_value_attribute,
3791 p_css_label_class_name => G_ATTRIBUTE_REDO_TBL(l_rec_index).css_label_class_name,
3792 p_precision => G_ATTRIBUTE_REDO_TBL(l_rec_index).precision,
3793 p_expansion => G_ATTRIBUTE_REDO_TBL(l_rec_index).expansion,
3794 p_als_max_length => G_ATTRIBUTE_REDO_TBL(l_rec_index).als_max_length,
3795 p_attribute1 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute1,
3796 p_attribute2 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute2,
3797 p_attribute3 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute3,
3798 p_attribute4 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute4,
3799 p_attribute5 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute5,
3800 p_attribute6 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute6,
3801 p_attribute7 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute7,
3802 p_attribute8 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute8,
3803 p_attribute9 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute9,
3804 p_attribute10 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute10,
3805 p_attribute11 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute11,
3806 p_attribute12 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute12,
3807 p_attribute13 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute13,
3808 p_attribute14 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute14,
3809 p_attribute15 => G_ATTRIBUTE_REDO_TBL(l_rec_index).attribute15,
3810 p_name => G_ATTRIBUTE_TL_REDO_TBL(l_rec_index).name,
3811 p_attribute_label_long => G_ATTRIBUTE_TL_REDO_TBL(l_rec_index).attribute_label_long,
3812 p_attribute_label_short =>G_ATTRIBUTE_TL_REDO_TBL(l_rec_index).attribute_label_short,
3813 p_description => G_ATTRIBUTE_TL_REDO_TBL(l_rec_index).description,
3814 p_created_by => G_ATTRIBUTE_REDO_TBL(l_rec_index).created_by,
3815 p_creation_date => G_ATTRIBUTE_REDO_TBL(l_rec_index).creation_date,
3816 p_last_updated_by => G_ATTRIBUTE_REDO_TBL(l_rec_index).last_updated_by,
3817 p_last_update_date => G_ATTRIBUTE_REDO_TBL(l_rec_index).last_update_date,
3818 p_last_update_login => G_ATTRIBUTE_REDO_TBL(l_rec_index).last_update_login,
3819 p_loader_timestamp => p_loader_timestamp,
3820 p_pass => p_pass,
3821 p_copy_redo_flag => l_copy_redo_flag
3822 );
3823 end if; -- /* if ATTRIBUTE_EXISTS */
3824 --
3825 -- If API call returns with an error status, upload aborts
3826 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3827 (l_return_status = FND_API.G_RET_STS_ERROR) then
3828 RAISE FND_API.G_EXC_ERROR;
3829 end if; -- /* if l_return_status */
3830 end if; -- /* if G_ATTRIBUTE_REDO_TBL.exists */
3831 end loop; --/* for loop */
3832 G_ATTRIBUTE_REDO_TBL.DELETE;
3833 end if; -- /* if G_ATTRIBUTE_REDO_TBL.count */
3834
3835 p_return_status := FND_API.G_RET_STS_SUCCESS;
3836
3837 EXCEPTION
3838 WHEN FND_API.G_EXC_ERROR THEN
3839 p_return_status := FND_API.G_RET_STS_ERROR;
3840 FND_MSG_PUB.Count_And_Get (
3841 p_count => l_msg_count,
3842 p_data => l_msg_data);
3843 G_ATTRIBUTE_REDO_TBL.DELETE;
3844 WHEN OTHERS THEN
3845 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3846 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3847 SUBSTR (SQLERRM, 1, 240) );
3848 FND_MSG_PUB.Count_And_Get (
3849 p_count => l_msg_count,
3850 p_data => l_msg_data);
3851 G_ATTRIBUTE_REDO_TBL.DELETE;
3852 end UPLOAD_ATTRIBUTE_SECOND;
3853
3854 end AK_Attribute_pvt;