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