[Home] [Help]
PACKAGE BODY: APPS.AK_OBJECT2_PVT
Source
1 package body AK_OBJECT2_PVT as
2 /* $Header: akdvob2b.pls 120.4 2005/09/26 20:14:33 tshort ship $ */
3
4 --=======================================================
5 -- Procedure WRITE_NAVIGATION_TO_BUFFER (local procedure)
6 --
7 -- Usage Local procedure for writing all attribute navigation
8 -- records for the given object attribute to
9 -- the output file. Not designed to be called
10 -- from outside this package.
11 --
12 -- Desc This procedure retrieves all Attribute Navigation
13 -- record for the given object attribute from the
14 -- database, and writes them to the output file
15 -- in loader file format.
16 --
17 -- Results The API returns the standard p_return_status parameter
18 -- indicating one of the standard return statuses :
19 -- * Unexpected error
20 -- * Error
21 -- * Success
22 -- Parameters
23 -- p_database_object_name : IN required
24 -- p_attribute_application_id : IN required
25 -- p_attribute_code : IN required
26 -- Key value of the Object Attribute record
27 -- whose Attribute Values records are to be
28 -- extracted to the loader file.
29 --=======================================================
30 procedure WRITE_NAVIGATION_TO_BUFFER (
31 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
32 p_return_status OUT NOCOPY VARCHAR2,
33 p_database_object_name IN VARCHAR2,
34 p_attribute_application_id IN NUMBER,
35 p_attribute_code IN VARCHAR2
36 ) is
37 cursor l_get_navigation_csr is
38 select *
39 from AK_OBJECT_ATTRIBUTE_NAVIGATION
40 where DATABASE_OBJECT_NAME = p_database_object_name
41 and ATTRIBUTE_APPLICATION_ID = p_attribute_application_id
42 and ATTRIBUTE_CODE = p_attribute_code;
43 l_api_name CONSTANT varchar2(30) := 'Write_Navigation_to_buffer';
44 l_databuffer_tbl AK_ON_OBJECTS_PUB.Buffer_Tbl_Type;
45 l_index NUMBER;
46 l_navigation_rec AK_OBJECT_ATTRIBUTE_NAVIGATION%ROWTYPE;
47 l_return_status varchar2(1);
48 begin
49 -- Find out where the next buffer entry to be written to
50 l_index := 1;
51
52 -- Retrieve all attribute values for this object attributes from the
53 -- database
54
55 open l_get_navigation_csr;
56 loop
57 fetch l_get_navigation_csr into l_navigation_rec;
58 exit when l_get_navigation_csr%notfound;
59 -- write this object attribute navigation record if it is valid
60 if (p_validation_level <> FND_API.G_VALID_LEVEL_NONE) and
61 not AK_OBJECT_PVT.VALIDATE_ATTRIBUTE_NAVIGATION (
62 p_validation_level => p_validation_level,
63 p_api_version_number => 1.0,
64 p_return_status => l_return_status,
65 p_database_object_name => p_database_object_name,
66 p_attribute_application_id => p_attribute_application_id,
67 p_attribute_code => p_attribute_code,
68 p_value_varchar2 => l_navigation_rec.value_varchar2,
69 p_value_date => l_navigation_rec.value_date,
70 p_value_number => l_navigation_rec.value_number,
71 p_to_region_appl_id => l_navigation_rec.to_region_appl_id,
72 p_to_region_code => l_navigation_rec.to_region_code,
73 p_caller => AK_ON_OBJECTS_PVT.G_DOWNLOAD )
74 then
75 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
76 FND_MESSAGE.SET_NAME('AK','AK_NAV_NOT_DOWNLOADED');
77 FND_MESSAGE.SET_TOKEN('KEY', p_database_object_name ||
78 ' ' || to_char(p_attribute_application_id) ||
79 l_navigation_rec.value_varchar2 ||
80 to_char(l_navigation_rec.value_date) ||
81 to_char(l_navigation_rec.value_number) );
82 FND_MSG_PUB.Add;
83 close l_get_navigation_csr;
84 RAISE FND_API.G_EXC_ERROR;
85 end if;
86
87 else
88 l_databuffer_tbl(l_index) := ' ';
89 l_index := l_index + 1;
90 l_databuffer_tbl(l_index) := ' BEGIN ATTRIBUTE_NAVIGATION "' ||
91 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_navigation_rec.value_varchar2)
92 || '" "' ||
93 to_char(l_navigation_rec.value_date, AK_ON_OBJECTS_PUB.G_DATE_FORMAT)
94 || '" "' ||
95 nvl(to_char(l_navigation_rec.value_number),'') || '"';
96 if ((l_navigation_rec.to_region_appl_id IS NOT NULL) and
97 (l_navigation_rec.to_region_appl_id <> FND_API.G_MISS_NUM) and
98 (l_navigation_rec.to_region_code IS NOT NULL) and
99 (l_navigation_rec.to_region_code <> FND_API.G_MISS_CHAR)) then
100 l_index := l_index + 1;
101 l_databuffer_tbl(l_index) := ' TO_REGION = "' ||
102 nvl(to_char(l_navigation_rec.to_region_appl_id),'')||'" "'||
103 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_navigation_rec.to_region_code)
104 || '"';
105 end if;
106 -- Flex Fields
107 --
108 if ((l_navigation_rec.attribute_category IS NOT NULL) and
109 (l_navigation_rec.attribute_category <> FND_API.G_MISS_CHAR)) then
110 l_index := l_index + 1;
111 l_databuffer_tbl(l_index) := ' ATTRIBUTE_CATEGORY = "' ||
112 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
113 l_navigation_rec.attribute_category) || '"';
114 end if;
115 if ((l_navigation_rec.attribute1 IS NOT NULL) and
116 (l_navigation_rec.attribute1 <> FND_API.G_MISS_CHAR)) then
117 l_index := l_index + 1;
118 l_databuffer_tbl(l_index) := ' ATTRIBUTE1 = "' ||
119 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
120 l_navigation_rec.attribute1) || '"';
121 end if;
122 if ((l_navigation_rec.attribute2 IS NOT NULL) and
123 (l_navigation_rec.attribute2 <> FND_API.G_MISS_CHAR)) then
124 l_index := l_index + 1;
125 l_databuffer_tbl(l_index) := ' ATTRIBUTE2 = "' ||
126 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
127 l_navigation_rec.attribute2) || '"';
128 end if;
129 if ((l_navigation_rec.attribute3 IS NOT NULL) and
130 (l_navigation_rec.attribute3 <> FND_API.G_MISS_CHAR)) then
131 l_index := l_index + 1;
132 l_databuffer_tbl(l_index) := ' ATTRIBUTE3 = "' ||
133 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
134 l_navigation_rec.attribute3) || '"';
135 end if;
136 if ((l_navigation_rec.attribute4 IS NOT NULL) and
137 (l_navigation_rec.attribute4 <> FND_API.G_MISS_CHAR)) then
138 l_index := l_index + 1;
139 l_databuffer_tbl(l_index) := ' ATTRIBUTE4 = "' ||
140 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
141 l_navigation_rec.attribute4) || '"';
142 end if;
143 if ((l_navigation_rec.attribute5 IS NOT NULL) and
144 (l_navigation_rec.attribute5 <> FND_API.G_MISS_CHAR)) then
145 l_index := l_index + 1;
146 l_databuffer_tbl(l_index) := ' ATTRIBUTE5 = "' ||
147 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
148 l_navigation_rec.attribute5) || '"';
149 end if;
150 if ((l_navigation_rec.attribute6 IS NOT NULL) and
151 (l_navigation_rec.attribute6 <> FND_API.G_MISS_CHAR)) then
152 l_index := l_index + 1;
153 l_databuffer_tbl(l_index) := ' ATTRIBUTE6 = "' ||
154 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
155 l_navigation_rec.attribute6) || '"';
156 end if;
157 if ((l_navigation_rec.attribute7 IS NOT NULL) and
158 (l_navigation_rec.attribute7 <> FND_API.G_MISS_CHAR)) then
159 l_index := l_index + 1;
160 l_databuffer_tbl(l_index) := ' ATTRIBUTE7 = "' ||
161 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
162 l_navigation_rec.attribute7) || '"';
163 end if;
164 if ((l_navigation_rec.attribute8 IS NOT NULL) and
165 (l_navigation_rec.attribute8 <> FND_API.G_MISS_CHAR)) then
166 l_index := l_index + 1;
167 l_databuffer_tbl(l_index) := ' ATTRIBUTE8 = "' ||
168 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
169 l_navigation_rec.attribute8) || '"';
170 end if;
171 if ((l_navigation_rec.attribute9 IS NOT NULL) and
172 (l_navigation_rec.attribute9 <> FND_API.G_MISS_CHAR)) then
173 l_index := l_index + 1;
174 l_databuffer_tbl(l_index) := ' ATTRIBUTE9 = "' ||
175 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
176 l_navigation_rec.attribute9) || '"';
177 end if;
178 if ((l_navigation_rec.attribute10 IS NOT NULL) and
179 (l_navigation_rec.attribute10 <> FND_API.G_MISS_CHAR)) then
180 l_index := l_index + 1;
181 l_databuffer_tbl(l_index) := ' ATTRIBUTE10 = "' ||
182 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
183 l_navigation_rec.attribute10) || '"';
184 end if;
185 if ((l_navigation_rec.attribute11 IS NOT NULL) and
186 (l_navigation_rec.attribute11 <> FND_API.G_MISS_CHAR)) then
187 l_index := l_index + 1;
188 l_databuffer_tbl(l_index) := ' ATTRIBUTE11 = "' ||
189 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
190 l_navigation_rec.attribute11) || '"';
191 end if;
192 if ((l_navigation_rec.attribute12 IS NOT NULL) and
193 (l_navigation_rec.attribute12 <> FND_API.G_MISS_CHAR)) then
194 l_index := l_index + 1;
195 l_databuffer_tbl(l_index) := ' ATTRIBUTE12 = "' ||
196 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
197 l_navigation_rec.attribute12) || '"';
198 end if;
199 if ((l_navigation_rec.attribute13 IS NOT NULL) and
200 (l_navigation_rec.attribute13 <> FND_API.G_MISS_CHAR)) then
201 l_index := l_index + 1;
202 l_databuffer_tbl(l_index) := ' ATTRIBUTE13 = "' ||
203 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
204 l_navigation_rec.attribute13) || '"';
205 end if;
206 if ((l_navigation_rec.attribute14 IS NOT NULL) and
207 (l_navigation_rec.attribute14 <> FND_API.G_MISS_CHAR)) then
208 l_index := l_index + 1;
209 l_databuffer_tbl(l_index) := ' ATTRIBUTE14 = "' ||
210 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
211 l_navigation_rec.attribute14) || '"';
212 end if;
213 if ((l_navigation_rec.attribute15 IS NOT NULL) and
214 (l_navigation_rec.attribute15 <> FND_API.G_MISS_CHAR)) then
215 l_index := l_index + 1;
216 l_databuffer_tbl(l_index) := ' ATTRIBUTE15 = "' ||
217 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
218 l_navigation_rec.attribute15) || '"';
219 end if;
220 -- - Write out who columns
221 l_index := l_index + 1;
222 l_databuffer_tbl(l_index) := ' CREATED_BY = "' ||
223 nvl(to_char(l_navigation_rec.created_by),'') || '"';
224 l_index := l_index + 1;
225 l_databuffer_tbl(l_index) := ' CREATION_DATE = "' ||
226 to_char(l_navigation_rec.creation_date,
227 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
228 l_index := l_index + 1;
229 -- CHANGED TO OWNER FOR R12
230 --l_databuffer_tbl(l_index) := ' LAST_UPDATED_BY = "' ||
231 --nvl(to_char(l_navigation_rec.last_updated_by),'') || '"';
232 l_databuffer_tbl(l_index) := ' OWNER = "' ||
233 FND_LOAD_UTIL.OWNER_NAME(l_navigation_rec.last_updated_by) || '"';
234 l_index := l_index + 1;
235 l_databuffer_tbl(l_index) := ' LAST_UPDATE_DATE = "' ||
236 to_char(l_navigation_rec.last_update_date,
237 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
238 l_index := l_index + 1;
239 l_databuffer_tbl(l_index) := ' LAST_UPDATE_LOGIN = "' ||
240 nvl(to_char(l_navigation_rec.last_update_login),'') || '"';
241
242 -- finish up object attribute navigation
243 l_index := l_index + 1;
244 l_databuffer_tbl(l_index) := ' END ATTRIBUTE_NAVIGATION';
245 l_index := l_index + 1;
246 l_databuffer_tbl(l_index) := ' ';
247 end if; -- validation OK
248
249 end loop;
250 close l_get_navigation_csr;
251
252 -- write attribute navigation data to file
253 -- don't call write_file if there is no data to be written
254 if (l_databuffer_tbl.count > 0) then
255 AK_ON_OBJECTS_PVT.WRITE_FILE (
256 p_return_status => l_return_status,
257 p_buffer_tbl => l_databuffer_tbl,
258 p_write_mode => AK_ON_OBJECTS_PUB.G_APPEND
259 );
260 -- If API call returns with an error status...
261 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
262 (l_return_status = FND_API.G_RET_STS_ERROR) then
263 RAISE FND_API.G_EXC_ERROR;
264 end if;
265 end if;
266
267 p_return_status := FND_API.G_RET_STS_SUCCESS;
268
269 EXCEPTION
270 WHEN VALUE_ERROR THEN
271 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
272 FND_MESSAGE.SET_NAME('AK','AK_NAV_VALUE_ERROR');
273 FND_MESSAGE.SET_TOKEN('KEY', p_database_object_name ||
274 ' ' || to_char(p_attribute_application_id) ||
275 l_navigation_rec.value_varchar2 ||
276 to_char(l_navigation_rec.value_date) ||
277 to_char(l_navigation_rec.value_number) );
278 FND_MSG_PUB.Add;
279 end if;
280 p_return_status := FND_API.G_RET_STS_ERROR;
281 WHEN FND_API.G_EXC_ERROR THEN
282 p_return_status := FND_API.G_RET_STS_ERROR;
283 WHEN OTHERS THEN
284 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
285 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
286 SUBSTR (SQLERRM, 1, 240) );
287 FND_MSG_PUB.Add;
288 end WRITE_NAVIGATION_TO_BUFFER;
289
290 --=======================================================
291 -- Procedure WRITE_ATTRIBUTE_TO_BUFFER (local procedure)
292 --
293 -- Usage Local procedure for writing all object attributes
294 -- and their children records for the given object
295 -- to the output file.
296 -- Not designed to be called from outside this package.
297 --
298 -- Desc This procedure retrieves all Object Attributes
299 -- that belongs to the given object from the database,
300 -- as well as all Attribute Values and Attribute
301 -- Navigation records for these object attributes,
302 -- and writes them to the output file
303 -- in loader file format.
304 --
305 -- Results The API returns the standard p_return_status parameter
306 -- indicating one of the standard return statuses :
307 -- * Unexpected error
308 -- * Error
309 -- * Success
310 -- Parameters
311 -- p_database_object_name : IN required
312 -- Key value of the Object record whose Object
313 -- Attributes are to be extracted to the loader file.
314 -- p_nls_language : IN required
315 -- The NLS langauge that should be used when
316 -- extracting data from the TL table
317 --=======================================================
318 procedure WRITE_ATTRIBUTE_TO_BUFFER (
319 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
320 p_return_status OUT NOCOPY VARCHAR2,
321 p_database_object_name IN VARCHAR2,
322 p_nls_language IN VARCHAR2
323 ) is
324 cursor l_get_attributes_csr is
325 select *
326 from AK_OBJECT_ATTRIBUTES
327 where DATABASE_OBJECT_NAME = p_database_object_name;
328 cursor l_get_attribute_tl_csr (attribute_appl_id_param number,
329 attribute_code_param varchar2) is
330 select *
331 from AK_OBJECT_ATTRIBUTES_TL
332 where DATABASE_OBJECT_NAME = p_database_object_name
333 and ATTRIBUTE_APPLICATION_ID = attribute_appl_id_param
334 and ATTRIBUTE_CODE = attribute_code_param
335 and LANGUAGE = p_nls_language;
336 l_api_name CONSTANT varchar2(30) := 'Write_Attribute_to_buffer';
337 l_databuffer_tbl AK_ON_OBJECTS_PUB.Buffer_Tbl_Type;
338 l_index NUMBER;
339 l_attributes_rec AK_OBJECT_ATTRIBUTES%ROWTYPE;
340 l_attributes_tl_rec AK_OBJECT_ATTRIBUTES_TL%ROWTYPE;
341 l_return_status varchar2(1);
342 begin
343 -- Find out where the next buffer entry to be written to
344 l_index := 1;
345
346 -- Retrieve object attribute and its TL information from the database
347
348 open l_get_attributes_csr;
349 loop
350 fetch l_get_attributes_csr into l_attributes_rec;
351 exit when l_get_attributes_csr%notfound;
352 open l_get_attribute_tl_csr(l_attributes_rec.attribute_application_id,
353 l_attributes_rec.attribute_code);
354 fetch l_get_attribute_tl_csr into l_attributes_tl_rec;
355 if l_get_attribute_tl_csr%found then
356 -- write this object attribute if it is validated
357 if (p_validation_level <> FND_API.G_VALID_LEVEL_NONE) and
358 not AK_OBJECT_PVT.VALIDATE_ATTRIBUTE (
359 p_validation_level => p_validation_level,
360 p_api_version_number => 1.0,
361 p_return_status => l_return_status,
362 p_database_object_name => p_database_object_name,
363 p_attribute_application_id => l_attributes_rec.attribute_application_id,
364 p_attribute_code => l_attributes_rec.attribute_code,
365 p_column_name => l_attributes_rec.column_name,
366 p_attribute_label_length => l_attributes_rec.attribute_label_length,
367 p_display_value_length => l_attributes_rec.display_value_length,
368 p_bold => l_attributes_rec.bold,
369 p_italic => l_attributes_rec.italic,
370 p_vertical_alignment => l_attributes_rec.vertical_alignment,
371 p_horizontal_alignment => l_attributes_rec.horizontal_alignment,
372 p_data_source_type => l_attributes_rec.data_source_type,
373 p_data_storage_type => l_attributes_rec.data_storage_type,
374 p_table_name => l_attributes_rec.table_name,
375 p_base_table_column_name =>
376 l_attributes_rec.base_table_column_name,
377 p_required_flag => l_attributes_rec.required_flag,
378 p_default_value_varchar2 => l_attributes_rec.default_value_varchar2,
379 p_default_value_number => l_attributes_rec.default_value_number,
380 p_default_value_date => l_attributes_rec.default_value_date,
381 p_lov_region_application_id =>
382 l_attributes_rec.lov_region_application_id,
383 p_lov_region_code => l_attributes_rec.lov_region_code,
384 p_lov_foreign_key_name => l_attributes_rec.lov_foreign_key_name,
385 p_lov_attribute_application_id =>
386 l_attributes_rec.lov_attribute_application_id,
387 p_lov_attribute_code => l_attributes_rec.lov_attribute_code,
388 p_defaulting_api_pkg => l_attributes_rec.defaulting_api_pkg,
389 p_defaulting_api_proc => l_attributes_rec.defaulting_api_proc,
390 p_validation_api_pkg => l_attributes_rec.validation_api_pkg,
391 p_validation_api_proc => l_attributes_rec.validation_api_proc,
392 p_attribute_label_long => l_attributes_tl_rec.attribute_label_long,
393 p_attribute_label_short=>l_attributes_tl_rec.attribute_label_short,
394 p_caller => AK_ON_OBJECTS_PVT.G_DOWNLOAD)
395 then
396 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
397 FND_MESSAGE.SET_NAME('AK','AK_OBJECT_ATTR_NOT_DOWNLOADED');
398 FND_MESSAGE.SET_TOKEN('KEY', p_database_object_name ||
399 ' ' || to_char(l_attributes_rec.attribute_application_id) ||
400 ' ' || l_attributes_rec.attribute_code );
401 FND_MSG_PUB.Add;
402 end if;
403 close l_get_attribute_tl_csr;
404 close l_get_attributes_csr;
405 RAISE FND_API.G_EXC_ERROR;
406 else
407
408 l_databuffer_tbl(l_index) := ' ';
409 l_index := l_index + 1;
410 l_databuffer_tbl(l_index) := ' BEGIN OBJECT_ATTRIBUTE "' ||
411 l_attributes_rec.attribute_application_id || '" "' ||
412 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_attributes_rec.attribute_code)
413 || '"';
414 --
415 -- check if all non-required columns are null or not, do not write out
416 -- those that are null
417 --
418 if ((l_attributes_rec.column_name IS NOT NULL) and
419 (l_attributes_rec.column_name <> FND_API.G_MISS_CHAR)) then
420 l_index := l_index + 1;
421 l_databuffer_tbl(l_index) := ' COLUMN_NAME = "' ||
422 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_attributes_rec.column_name)
423 || '"';
424 end if;
425 l_index := l_index + 1;
426 l_databuffer_tbl(l_index) := ' ATTRIBUTE_LABEL_LENGTH = "' ||
427 nvl(to_char(l_attributes_rec.attribute_label_length),'') || '"';
428 l_index := l_index + 1;
429 l_databuffer_tbl(l_index) := ' DISPLAY_VALUE_LENGTH = "' ||
430 nvl(to_char(l_attributes_rec.display_value_length),'') || '"';
431 l_index := l_index + 1;
432 l_databuffer_tbl(l_index) := ' BOLD = "' ||
433 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_attributes_rec.bold) || '"';
434 l_index := l_index + 1;
435 l_databuffer_tbl(l_index) := ' ITALIC = "' ||
436 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_attributes_rec.italic) || '"';
437 l_index := l_index + 1;
438 l_databuffer_tbl(l_index) := ' VERTICAL_ALIGNMENT = "' ||
439 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
440 l_attributes_rec.vertical_alignment)|| '"';
441 l_index := l_index + 1;
442 l_databuffer_tbl(l_index) := ' HORIZONTAL_ALIGNMENT = "' ||
443 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
444 l_attributes_rec.horizontal_alignment)|| '"';
445 l_index := l_index + 1;
446 l_databuffer_tbl(l_index) := ' DATA_SOURCE_TYPE = "' ||
447 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
448 l_attributes_rec.data_source_type)|| '"';
449 l_index := l_index + 1;
450 if ((l_attributes_rec.data_storage_type IS NOT NULL) and
451 (l_attributes_rec.data_storage_type <> FND_API.G_MISS_CHAR)) then
452 l_databuffer_tbl(l_index) := ' DATA_STORAGE_TYPE = "' ||
453 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
454 l_attributes_rec.data_storage_type)|| '"';
455 end if;
456 if ((l_attributes_rec.table_name IS NOT NULL) and
457 (l_attributes_rec.table_name <> FND_API.G_MISS_CHAR)) then
458 l_index := l_index + 1;
459 l_databuffer_tbl(l_index) := ' TABLE_NAME = "' ||
460 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
461 l_attributes_rec.table_name)|| '"';
462 end if;
463 if ((l_attributes_rec.base_table_column_name IS NOT NULL) and
464 (l_attributes_rec.base_table_column_name <> FND_API.G_MISS_CHAR)) then
465 l_index := l_index + 1;
466 l_databuffer_tbl(l_index) := ' BASE_TABLE_COLUMN_NAME = "' ||
467 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
468 l_attributes_rec.base_table_column_name)|| '"';
469 end if;
470 l_index := l_index + 1;
471 l_databuffer_tbl(l_index) := ' REQUIRED_FLAG = "' ||
472 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
473 l_attributes_rec.required_flag)|| '"';
474 if ((l_attributes_rec.default_value_varchar2 IS NOT NULL) and
475 (l_attributes_rec.default_value_varchar2 <> FND_API.G_MISS_CHAR)) then
476 l_index := l_index + 1;
477 l_databuffer_tbl(l_index) := ' DEFAULT_VALUE_VARCHAR2 = "' ||
478 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
479 l_attributes_rec.default_value_varchar2) || '"';
480 end if;
481 if ((l_attributes_rec.default_value_number IS NOT NULL) and
482 (l_attributes_rec.default_value_number <> FND_API.G_MISS_NUM)) then
483 l_index := l_index + 1;
484 l_databuffer_tbl(l_index) := ' DEFAULT_VALUE_NUMBER = "' ||
485 nvl(to_char(l_attributes_rec.default_value_number),'') || '"';
486 end if;
487 if ((l_attributes_rec.default_value_date IS NOT NULL) and
488 (l_attributes_rec.default_value_date <> FND_API.G_MISS_CHAR)) then
489 l_index := l_index + 1;
490 l_databuffer_tbl(l_index) := ' DEFAULT_VALUE_DATE = "' ||
491 to_char(l_attributes_rec.default_value_date,
492 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
493 end if;
494
495 -- Flex Fields
496 --
497 if ((l_attributes_rec.attribute_category IS NOT NULL) and
498 (l_attributes_rec.attribute_category <> FND_API.G_MISS_CHAR)) then
499 l_index := l_index + 1;
500 l_databuffer_tbl(l_index) := ' ATTRIBUTE_CATEGORY = "' ||
501 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
502 l_attributes_rec.attribute_category) || '"';
503 end if;
504 if ((l_attributes_rec.attribute1 IS NOT NULL) and
505 (l_attributes_rec.attribute1 <> FND_API.G_MISS_CHAR)) then
506 l_index := l_index + 1;
507 l_databuffer_tbl(l_index) := ' ATTRIBUTE1 = "' ||
508 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
509 l_attributes_rec.attribute1) || '"';
510 end if;
511 if ((l_attributes_rec.attribute2 IS NOT NULL) and
512 (l_attributes_rec.attribute2 <> FND_API.G_MISS_CHAR)) then
513 l_index := l_index + 1;
514 l_databuffer_tbl(l_index) := ' ATTRIBUTE2 = "' ||
515 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
516 l_attributes_rec.attribute2) || '"';
517 end if;
518 if ((l_attributes_rec.attribute3 IS NOT NULL) and
519 (l_attributes_rec.attribute3 <> FND_API.G_MISS_CHAR)) then
520 l_index := l_index + 1;
521 l_databuffer_tbl(l_index) := ' ATTRIBUTE3 = "' ||
522 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
523 l_attributes_rec.attribute3) || '"';
524 end if;
525 if ((l_attributes_rec.attribute4 IS NOT NULL) and
526 (l_attributes_rec.attribute4 <> FND_API.G_MISS_CHAR)) then
527 l_index := l_index + 1;
528 l_databuffer_tbl(l_index) := ' ATTRIBUTE4 = "' ||
529 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
530 l_attributes_rec.attribute4) || '"';
531 end if;
532 if ((l_attributes_rec.attribute5 IS NOT NULL) and
533 (l_attributes_rec.attribute5 <> FND_API.G_MISS_CHAR)) then
534 l_index := l_index + 1;
535 l_databuffer_tbl(l_index) := ' ATTRIBUTE5 = "' ||
536 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
537 l_attributes_rec.attribute5) || '"';
538 end if;
539 if ((l_attributes_rec.attribute6 IS NOT NULL) and
540 (l_attributes_rec.attribute6 <> FND_API.G_MISS_CHAR)) then
541 l_index := l_index + 1;
542 l_databuffer_tbl(l_index) := ' ATTRIBUTE6 = "' ||
543 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
544 l_attributes_rec.attribute6) || '"';
545 end if;
546 if ((l_attributes_rec.attribute7 IS NOT NULL) and
547 (l_attributes_rec.attribute7 <> FND_API.G_MISS_CHAR)) then
548 l_index := l_index + 1;
549 l_databuffer_tbl(l_index) := ' ATTRIBUTE7 = "' ||
550 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
551 l_attributes_rec.attribute7) || '"';
552 end if;
553 if ((l_attributes_rec.attribute8 IS NOT NULL) and
554 (l_attributes_rec.attribute8 <> FND_API.G_MISS_CHAR)) then
555 l_index := l_index + 1;
556 l_databuffer_tbl(l_index) := ' ATTRIBUTE8 = "' ||
557 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
558 l_attributes_rec.attribute8) || '"';
559 end if;
560 if ((l_attributes_rec.attribute9 IS NOT NULL) and
561 (l_attributes_rec.attribute9 <> FND_API.G_MISS_CHAR)) then
562 l_index := l_index + 1;
563 l_databuffer_tbl(l_index) := ' ATTRIBUTE9 = "' ||
564 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
565 l_attributes_rec.attribute9) || '"';
566 end if;
567 if ((l_attributes_rec.attribute10 IS NOT NULL) and
568 (l_attributes_rec.attribute10 <> FND_API.G_MISS_CHAR)) then
569 l_index := l_index + 1;
570 l_databuffer_tbl(l_index) := ' ATTRIBUTE10 = "' ||
571 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
572 l_attributes_rec.attribute10) || '"';
573 end if;
574 if ((l_attributes_rec.attribute11 IS NOT NULL) and
575 (l_attributes_rec.attribute11 <> FND_API.G_MISS_CHAR)) then
576 l_index := l_index + 1;
577 l_databuffer_tbl(l_index) := ' ATTRIBUTE11 = "' ||
578 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
579 l_attributes_rec.attribute11) || '"';
580 end if;
581 if ((l_attributes_rec.attribute12 IS NOT NULL) and
582 (l_attributes_rec.attribute12 <> FND_API.G_MISS_CHAR)) then
583 l_index := l_index + 1;
584 l_databuffer_tbl(l_index) := ' ATTRIBUTE12 = "' ||
585 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
586 l_attributes_rec.attribute12) || '"';
587 end if;
588 if ((l_attributes_rec.attribute13 IS NOT NULL) and
589 (l_attributes_rec.attribute13 <> FND_API.G_MISS_CHAR)) then
590 l_index := l_index + 1;
591 l_databuffer_tbl(l_index) := ' ATTRIBUTE13 = "' ||
592 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
593 l_attributes_rec.attribute13) || '"';
594 end if;
595 if ((l_attributes_rec.attribute14 IS NOT NULL) and
596 (l_attributes_rec.attribute14 <> FND_API.G_MISS_CHAR)) then
597 l_index := l_index + 1;
598 l_databuffer_tbl(l_index) := ' ATTRIBUTE14 = "' ||
599 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
600 l_attributes_rec.attribute14) || '"';
601 end if;
602 if ((l_attributes_rec.attribute15 IS NOT NULL) and
603 (l_attributes_rec.attribute15 <> FND_API.G_MISS_CHAR)) then
604 l_index := l_index + 1;
605 l_databuffer_tbl(l_index) := ' ATTRIBUTE15 = "' ||
606 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
607 l_attributes_rec.attribute15) || '"';
608 end if;
609 -- - Write out who columns
610 l_index := l_index + 1;
611 l_databuffer_tbl(l_index) := ' CREATED_BY = "' ||
612 nvl(to_char(l_attributes_rec.created_by),'') || '"';
613 l_index := l_index + 1;
614 l_databuffer_tbl(l_index) := ' CREATION_DATE = "' ||
615 to_char(l_attributes_rec.creation_date,
616 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
617 l_index := l_index + 1;
618 -- CHANGED TO OWNER FOR R12
619 --l_databuffer_tbl(l_index) := ' LAST_UPDATED_BY = "' ||
620 --nvl(to_char(l_attributes_rec.last_updated_by),'') || '"';
621 l_databuffer_tbl(l_index) := ' OWNER = "' ||
622 FND_LOAD_UTIL.OWNER_NAME(l_attributes_rec.last_updated_by) || '"';
623 l_index := l_index + 1;
624 l_databuffer_tbl(l_index) := ' LAST_UPDATE_DATE = "' ||
625 to_char(l_attributes_rec.last_update_date,
626 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
627 l_index := l_index + 1;
628 l_databuffer_tbl(l_index) := ' LAST_UPDATE_LOGIN = "' ||
629 nvl(to_char(l_attributes_rec.last_update_login),'') || '"';
630
631 -- translation columns
632 --
633 if ((l_attributes_rec.lov_region_application_id IS NOT NULL) and
634 (l_attributes_rec.lov_region_application_id <> FND_API.G_MISS_NUM) and
635 (l_attributes_rec.lov_region_code IS NOT NULL) and
636 (l_attributes_rec.lov_region_code <> FND_API.G_MISS_CHAR)) then
637 l_index := l_index + 1;
638 l_databuffer_tbl(l_index) := ' LOV_REGION = "' ||
639 nvl(to_char(l_attributes_rec.lov_region_application_id),'')||'" "'||
640 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
641 l_attributes_rec.lov_region_code)|| '"';
642 end if;
643 if ((l_attributes_rec.lov_foreign_key_name IS NOT NULL) and
644 (l_attributes_rec.lov_foreign_key_name <> FND_API.G_MISS_CHAR)) then
645 l_index := l_index + 1;
646 l_databuffer_tbl(l_index) := ' LOV_FOREIGN_KEY_NAME = "' ||
647 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
648 l_attributes_rec.lov_foreign_key_name)|| '"';
649 end if;
650 if ((l_attributes_rec.lov_attribute_application_id IS NOT NULL) and
651 (l_attributes_rec.lov_attribute_application_id <> FND_API.G_MISS_NUM) and
652 (l_attributes_rec.lov_attribute_code IS NOT NULL) and
653 (l_attributes_rec.lov_attribute_code <> FND_API.G_MISS_CHAR)) then
654 l_index := l_index + 1;
655 l_databuffer_tbl(l_index) := ' LOV_ATTRIBUTE = "' ||
656 nvl(to_char(l_attributes_rec.lov_attribute_application_id),'')||'" "'||
657 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
658 l_attributes_rec.lov_attribute_code)|| '"';
659 end if;
660 if ((l_attributes_rec.base_table_column_name IS NOT NULL) and
661 (l_attributes_rec.base_table_column_name <> FND_API.G_MISS_CHAR)) then
662 l_index := l_index + 1;
663 l_databuffer_tbl(l_index) := ' DEFAULTING_API_PKG = "' ||
664 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
665 l_attributes_rec.defaulting_api_pkg)|| '"';
666 end if;
667 if ((l_attributes_rec.defaulting_api_proc IS NOT NULL) and
668 (l_attributes_rec.defaulting_api_proc <> FND_API.G_MISS_CHAR)) then
669 l_index := l_index + 1;
670 l_databuffer_tbl(l_index) := ' DEFAULTING_API_PROC = "' ||
671 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
672 l_attributes_rec.defaulting_api_proc)|| '"';
673 end if;
674 if ((l_attributes_rec.validation_api_pkg IS NOT NULL) and
675 (l_attributes_rec.validation_api_pkg <> FND_API.G_MISS_CHAR)) then
676 l_index := l_index + 1;
677 l_databuffer_tbl(l_index) := ' VALIDATION_API_PKG = "' ||
678 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
679 l_attributes_rec.validation_api_pkg)|| '"';
680 end if;
681 if ((l_attributes_rec.validation_api_proc IS NOT NULL) and
682 (l_attributes_rec.validation_api_proc <> FND_API.G_MISS_CHAR)) then
683 l_index := l_index + 1;
684 l_databuffer_tbl(l_index) := ' VALIDATION_API_PROC = "' ||
685 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
686 l_attributes_rec.validation_api_proc)|| '"';
687 end if;
688
689 -- TL table entries
690 if ((l_attributes_tl_rec.attribute_label_long IS NOT NULL) and
691 (l_attributes_tl_rec.attribute_label_long <> FND_API.G_MISS_CHAR)) then
692 l_index := l_index + 1;
693 l_databuffer_tbl(l_index) := ' ATTRIBUTE_LABEL_LONG = "' ||
694 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
695 l_attributes_tl_rec.attribute_label_long)||'"';
696 end if;
697 if ((l_attributes_tl_rec.attribute_label_short IS NOT NULL) and
698 (l_attributes_tl_rec.attribute_label_short <> FND_API.G_MISS_CHAR)) then
699 l_index := l_index + 1;
700 l_databuffer_tbl(l_index) := ' ATTRIBUTE_LABEL_SHORT = "' ||
701 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
702 l_attributes_tl_rec.attribute_label_short)||'"';
703 end if;
704
705 -- write object attribute data to file
706 AK_ON_OBJECTS_PVT.WRITE_FILE (
707 p_return_status => l_return_status,
708 p_buffer_tbl => l_databuffer_tbl,
709 p_write_mode => AK_ON_OBJECTS_PUB.G_APPEND
710 );
711 -- If API call returns with an error status...
712 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
713 (l_return_status = FND_API.G_RET_STS_ERROR) then
714 close l_get_attribute_tl_csr;
715 close l_get_attributes_csr;
716 RAISE FND_API.G_EXC_ERROR;
717 end if;
718
719 l_databuffer_tbl.delete;
720
721 WRITE_NAVIGATION_TO_BUFFER (
722 p_validation_level => p_validation_level,
723 p_return_status => l_return_status,
724 p_database_object_name => p_database_object_name,
725 p_attribute_application_id => l_attributes_rec.attribute_application_id,
726 p_attribute_code => l_attributes_rec.attribute_code
727 );
728
729 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
730 (l_return_status = FND_API.G_RET_STS_ERROR) then
731 close l_get_attribute_tl_csr;
732 close l_get_attributes_csr;
733 RAISE FND_API.G_EXC_ERROR;
734 end if;
735
736 -- finish up object attributes
737 l_index := 1;
738 l_databuffer_tbl(l_index) := ' END OBJECT_ATTRIBUTE';
739 l_index := l_index + 1;
740 l_databuffer_tbl(l_index) := ' ';
741 end if; -- validation OK
742
743 -- write object attribute ending lines to file
744 AK_ON_OBJECTS_PVT.WRITE_FILE (
745 p_return_status => l_return_status,
746 p_buffer_tbl => l_databuffer_tbl,
747 p_write_mode => AK_ON_OBJECTS_PUB.G_APPEND
748 );
749 -- If API call returns with an error status...
750 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
751 (l_return_status = FND_API.G_RET_STS_ERROR) then
752 close l_get_attribute_tl_csr;
753 close l_get_attributes_csr;
754 RAISE FND_API.G_EXC_ERROR;
755 end if;
756
757 l_databuffer_tbl.delete;
758 end if; -- if TL record found
759 close l_get_attribute_tl_csr;
760
761 end loop;
762 close l_get_attributes_csr;
763
764 p_return_status := FND_API.G_RET_STS_SUCCESS;
765
766 EXCEPTION
767 WHEN VALUE_ERROR THEN
768 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
769 FND_MESSAGE.SET_NAME('AK','AK_OBJECT_ATTR_VALUE_ERROR');
770 FND_MESSAGE.SET_TOKEN('KEY', p_database_object_name ||
771 ' ' || to_char(l_attributes_rec.attribute_application_id) ||
772 ' ' || l_attributes_rec.attribute_code );
773 FND_MSG_PUB.Add;
774 end if;
775 p_return_status := FND_API.G_RET_STS_ERROR;
776 WHEN FND_API.G_EXC_ERROR THEN
777 p_return_status := FND_API.G_RET_STS_ERROR;
778 WHEN OTHERS THEN
779 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
780 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
781 SUBSTR (SQLERRM, 1, 240) );
782 FND_MSG_PUB.Add;
783 end WRITE_ATTRIBUTE_TO_BUFFER;
784
785 --=======================================================
786 -- Procedure WRITE_FOREIGN_KEY_TO_BUFFER (local procedure)
787 --
788 -- Usage Local procedure for writing all foreign key definitions
789 -- and their children records for the given object
790 -- to the output file.
791 -- Not designed to be called from outside this package.
792 --
793 -- Desc This procedure retrieves all Foriegn Key definitions
794 -- that belongs to the given object from the database,
795 -- as well as all foreign key column definitions
796 -- for these foreign keys, and writes them to the output file
797 -- in loader file format.
798 --
799 -- Results The API returns the standard p_return_status parameter
800 -- indicating one of the standard return statuses :
801 -- * Unexpected error
802 -- * Error
803 -- * Success
804 -- Parameters
805 -- p_database_object_name : IN required
806 -- Key value of the Object record whose foreign key
807 -- definitions are to be extracted to the loader file.
808 -- p_nls_language : IN required
809 -- The NLS langauge that should be used when
810 -- extracting data from the TL table
811 --=======================================================
812 procedure WRITE_FOREIGN_KEY_TO_BUFFER (
813 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
814 p_return_status OUT NOCOPY VARCHAR2,
815 p_database_object_name IN VARCHAR2,
816 p_nls_language IN VARCHAR2
817 ) is
818 cursor l_get_foreign_keys_csr is
819 select *
820 from AK_FOREIGN_KEYS
821 where DATABASE_OBJECT_NAME = p_database_object_name;
822 cursor l_get_foreign_key_tl_csr (foreign_key_name_param varchar2) is
823 select *
824 from AK_FOREIGN_KEYS_TL
825 where FOREIGN_KEY_NAME = foreign_key_name_param
826 and LANGUAGE = p_nls_language;
827 cursor l_get_key_columns_csr (foreign_key_name_param varchar2) is
828 select *
829 from AK_FOREIGN_KEY_COLUMNS
830 where FOREIGN_KEY_NAME = foreign_key_name_param;
831 l_api_name CONSTANT varchar2(30) := 'Write_Foreign_key_to_buffer';
832 l_databuffer_tbl AK_ON_OBJECTS_PUB.Buffer_Tbl_Type;
833 l_index NUMBER;
834 l_foreign_keys_rec AK_FOREIGN_KEYS%ROWTYPE;
835 l_foreign_keys_tl_rec AK_FOREIGN_KEYS_TL%ROWTYPE;
836 l_key_columns_rec AK_FOREIGN_KEY_COLUMNS%ROWTYPE;
837 l_return_status varchar2(1);
838 begin
839 -- Find out where the next buffer entry to be written to
840 l_index := 1;
841
842 -- Retrieve foreign key and its column and TL definitions from the
843 -- database
844
845 open l_get_foreign_keys_csr;
846 loop
847 fetch l_get_foreign_keys_csr into l_foreign_keys_rec;
848 exit when l_get_foreign_keys_csr%notfound;
849 open l_get_foreign_key_tl_csr (l_foreign_keys_rec.foreign_key_name);
850 fetch l_get_foreign_key_tl_csr into l_foreign_keys_tl_rec;
851 if (l_get_foreign_key_tl_csr%found) then
852 -- write this foreign key to buffer if it is valid
853 if (p_validation_level <> FND_API.G_VALID_LEVEL_NONE) and
854 not AK_KEY_PVT.VALIDATE_FOREIGN_KEY (
855 p_validation_level => p_validation_level,
856 p_api_version_number => 1.0,
857 p_return_status => l_return_status,
858 p_foreign_key_name => l_foreign_keys_rec.foreign_key_name,
859 p_database_object_name => l_foreign_keys_rec.database_object_name,
860 p_unique_key_name => l_foreign_keys_rec.unique_key_name,
861 p_application_id => l_foreign_keys_rec.application_id,
862 p_from_to_name => l_foreign_keys_tl_rec.from_to_name,
863 p_from_to_description => l_foreign_keys_tl_rec.from_to_description,
864 p_to_from_name => l_foreign_keys_tl_rec.to_from_name,
865 p_to_from_description => l_foreign_keys_tl_rec.to_from_description,
866 p_caller => AK_ON_OBJECTS_PVT.G_DOWNLOAD )
867 then
868 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
869 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_NOT_DOWNLOADED');
870 FND_MESSAGE.SET_TOKEN('KEY', l_foreign_keys_rec.foreign_key_name);
871 FND_MSG_PUB.Add;
872 end if;
873 else
874 l_databuffer_tbl(l_index) := ' ';
875 l_index := l_index + 1;
876 l_databuffer_tbl(l_index) := ' BEGIN FOREIGN_KEY "' ||
877 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_foreign_keys_rec.foreign_key_name)|| '"';
878 l_index := l_index + 1;
879 l_databuffer_tbl(l_index) := ' UNIQUE_KEY_NAME = "' ||
880 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_foreign_keys_rec.unique_key_name)|| '"';
881 l_index := l_index + 1;
882 l_databuffer_tbl(l_index) := ' APPLICATION_ID = "' ||
883 nvl(to_char(l_foreign_keys_rec.application_id),'') || '"';
884 -- Flex Fields
885 --
886 if ((l_foreign_keys_rec.attribute_category IS NOT NULL) and
887 (l_foreign_keys_rec.attribute_category <> FND_API.G_MISS_CHAR)) then
888 l_index := l_index + 1;
889 l_databuffer_tbl(l_index) := ' ATTRIBUTE_CATEGORY = "' ||
890 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
891 l_foreign_keys_rec.attribute_category) || '"';
892 end if;
893 if ((l_foreign_keys_rec.attribute1 IS NOT NULL) and
894 (l_foreign_keys_rec.attribute1 <> FND_API.G_MISS_CHAR)) then
895 l_index := l_index + 1;
896 l_databuffer_tbl(l_index) := ' ATTRIBUTE1 = "' ||
897 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
898 l_foreign_keys_rec.attribute1) || '"';
899 end if;
900 if ((l_foreign_keys_rec.attribute2 IS NOT NULL) and
901 (l_foreign_keys_rec.attribute2 <> FND_API.G_MISS_CHAR)) then
902 l_index := l_index + 1;
903 l_databuffer_tbl(l_index) := ' ATTRIBUTE2 = "' ||
904 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
905 l_foreign_keys_rec.attribute2) || '"';
906 end if;
907 if ((l_foreign_keys_rec.attribute3 IS NOT NULL) and
908 (l_foreign_keys_rec.attribute3 <> FND_API.G_MISS_CHAR)) then
909 l_index := l_index + 1;
910 l_databuffer_tbl(l_index) := ' ATTRIBUTE3 = "' ||
911 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
912 l_foreign_keys_rec.attribute3) || '"';
913 end if;
914 if ((l_foreign_keys_rec.attribute4 IS NOT NULL) and
915 (l_foreign_keys_rec.attribute4 <> FND_API.G_MISS_CHAR)) then
916 l_index := l_index + 1;
917 l_databuffer_tbl(l_index) := ' ATTRIBUTE4 = "' ||
918 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
919 l_foreign_keys_rec.attribute4) || '"';
920 end if;
921 if ((l_foreign_keys_rec.attribute5 IS NOT NULL) and
922 (l_foreign_keys_rec.attribute5 <> FND_API.G_MISS_CHAR)) then
923 l_index := l_index + 1;
924 l_databuffer_tbl(l_index) := ' ATTRIBUTE5 = "' ||
925 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
926 l_foreign_keys_rec.attribute5) || '"';
927 end if;
928 if ((l_foreign_keys_rec.attribute6 IS NOT NULL) and
929 (l_foreign_keys_rec.attribute6 <> FND_API.G_MISS_CHAR)) then
930 l_index := l_index + 1;
931 l_databuffer_tbl(l_index) := ' ATTRIBUTE6 = "' ||
932 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
933 l_foreign_keys_rec.attribute6) || '"';
934 end if;
935 if ((l_foreign_keys_rec.attribute7 IS NOT NULL) and
936 (l_foreign_keys_rec.attribute7 <> FND_API.G_MISS_CHAR)) then
937 l_index := l_index + 1;
938 l_databuffer_tbl(l_index) := ' ATTRIBUTE7 = "' ||
939 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
940 l_foreign_keys_rec.attribute7) || '"';
941 end if;
942 if ((l_foreign_keys_rec.attribute8 IS NOT NULL) and
943 (l_foreign_keys_rec.attribute8 <> FND_API.G_MISS_CHAR)) then
944 l_index := l_index + 1;
945 l_databuffer_tbl(l_index) := ' ATTRIBUTE8 = "' ||
946 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
947 l_foreign_keys_rec.attribute8) || '"';
948 end if;
949 if ((l_foreign_keys_rec.attribute9 IS NOT NULL) and
950 (l_foreign_keys_rec.attribute9 <> FND_API.G_MISS_CHAR)) then
951 l_index := l_index + 1;
952 l_databuffer_tbl(l_index) := ' ATTRIBUTE9 = "' ||
953 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
954 l_foreign_keys_rec.attribute9) || '"';
955 end if;
956 if ((l_foreign_keys_rec.attribute10 IS NOT NULL) and
957 (l_foreign_keys_rec.attribute10 <> FND_API.G_MISS_CHAR)) then
958 l_index := l_index + 1;
959 l_databuffer_tbl(l_index) := ' ATTRIBUTE10 = "' ||
960 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
961 l_foreign_keys_rec.attribute10) || '"';
962 end if;
963 if ((l_foreign_keys_rec.attribute11 IS NOT NULL) and
964 (l_foreign_keys_rec.attribute11 <> FND_API.G_MISS_CHAR)) then
965 l_index := l_index + 1;
966 l_databuffer_tbl(l_index) := ' ATTRIBUTE11 = "' ||
967 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
968 l_foreign_keys_rec.attribute11) || '"';
969 end if;
970 if ((l_foreign_keys_rec.attribute12 IS NOT NULL) and
971 (l_foreign_keys_rec.attribute12 <> FND_API.G_MISS_CHAR)) then
972 l_index := l_index + 1;
973 l_databuffer_tbl(l_index) := ' ATTRIBUTE12 = "' ||
974 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
975 l_foreign_keys_rec.attribute12) || '"';
976 end if;
977 if ((l_foreign_keys_rec.attribute13 IS NOT NULL) and
978 (l_foreign_keys_rec.attribute13 <> FND_API.G_MISS_CHAR)) then
979 l_index := l_index + 1;
980 l_databuffer_tbl(l_index) := ' ATTRIBUTE13 = "' ||
981 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
982 l_foreign_keys_rec.attribute13) || '"';
983 end if;
984 if ((l_foreign_keys_rec.attribute14 IS NOT NULL) and
985 (l_foreign_keys_rec.attribute14 <> FND_API.G_MISS_CHAR)) then
986 l_index := l_index + 1;
987 l_databuffer_tbl(l_index) := ' ATTRIBUTE14 = "' ||
988 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
989 l_foreign_keys_rec.attribute14) || '"';
990 end if;
991 if ((l_foreign_keys_rec.attribute15 IS NOT NULL) and
992 (l_foreign_keys_rec.attribute15 <> FND_API.G_MISS_CHAR)) then
993 l_index := l_index + 1;
994 l_databuffer_tbl(l_index) := ' ATTRIBUTE15 = "' ||
995 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
996 l_foreign_keys_rec.attribute15) || '"';
997 end if;
998 -- - Write out who columns
999 l_index := l_index + 1;
1000 l_databuffer_tbl(l_index) := ' CREATED_BY = "' ||
1001 nvl(to_char(l_foreign_keys_rec.created_by),'') || '"';
1002 l_index := l_index + 1;
1003 l_databuffer_tbl(l_index) := ' CREATION_DATE = "' ||
1004 to_char(l_foreign_keys_rec.creation_date,
1005 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1006 l_index := l_index + 1;
1007 -- CHANGED TO OWNER FOR R12
1008 --l_databuffer_tbl(l_index) := ' LAST_UPDATED_BY = "' ||
1009 --nvl(to_char(l_foreign_keys_rec.last_updated_by),'') || '"';
1010 l_databuffer_tbl(l_index) := ' OWNER = "' ||
1011 FND_LOAD_UTIL.OWNER_NAME(l_foreign_keys_rec.last_updated_by) || '"';
1012 l_index := l_index + 1;
1013 l_databuffer_tbl(l_index) := ' LAST_UPDATE_DATE = "' ||
1014 to_char(l_foreign_keys_rec.last_update_date,
1015 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1016 l_index := l_index + 1;
1017 l_databuffer_tbl(l_index) := ' LAST_UPDATE_LOGIN = "' ||
1018 nvl(to_char(l_foreign_keys_rec.last_update_login),'') || '"';
1019
1020 -- Foreign key TL info
1021 if ((l_foreign_keys_tl_rec.from_to_name IS NOT NULL) and
1022 (l_foreign_keys_tl_rec.from_to_name <> FND_API.G_MISS_CHAR)) then
1023 l_index := l_index + 1;
1024 l_databuffer_tbl(l_index) := ' FROM_TO_NAME = "' ||
1025 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_foreign_keys_tl_rec.from_to_name)|| '"';
1026 end if;
1027 if ((l_foreign_keys_tl_rec.from_to_description IS NOT NULL) and
1028 (l_foreign_keys_tl_rec.from_to_description <> FND_API.G_MISS_CHAR)) then
1029 l_index := l_index + 1;
1030 l_databuffer_tbl(l_index) := ' FROM_TO_DESCRIPTION = "' ||
1031 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_foreign_keys_tl_rec.from_to_description)|| '"';
1032 end if;
1033 if ((l_foreign_keys_tl_rec.to_from_name IS NOT NULL) and
1034 (l_foreign_keys_tl_rec.to_from_name <> FND_API.G_MISS_CHAR)) then
1035 l_index := l_index + 1;
1036 l_databuffer_tbl(l_index) := ' TO_FROM_NAME = "' ||
1037 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_foreign_keys_tl_rec.to_from_name)|| '"';
1038 end if;
1039 if ((l_foreign_keys_tl_rec.to_from_description IS NOT NULL) and
1040 (l_foreign_keys_tl_rec.to_from_description <> FND_API.G_MISS_CHAR)) then
1041 l_index := l_index + 1;
1042 l_databuffer_tbl(l_index) := ' TO_FROM_DESCRIPTION = "' ||
1043 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_foreign_keys_tl_rec.to_from_description)|| '"';
1044 end if;
1045
1046 -- Foreign Key columns
1047 open l_get_key_columns_csr(l_foreign_keys_rec.foreign_key_name);
1048 loop
1049 fetch l_get_key_columns_csr into l_key_columns_rec;
1050 exit when l_get_key_columns_csr%notfound;
1051 -- Write foreign key column if it is valid
1052 if (p_validation_level <> FND_API.G_VALID_LEVEL_NONE) and
1053 not AK_KEY_PVT.VALIDATE_FOREIGN_KEY_COLUMN (
1054 p_validation_level => p_validation_level,
1055 p_api_version_number => 1.0,
1056 p_return_status => l_return_status,
1057 p_foreign_key_name => l_key_columns_rec.foreign_key_name,
1058 p_attribute_application_id =>
1059 l_key_columns_rec.attribute_application_id,
1060 p_attribute_code => l_key_columns_rec.attribute_code,
1061 p_foreign_key_sequence => l_key_columns_rec.foreign_key_sequence,
1062 p_caller => AK_ON_OBJECTS_PVT.G_DOWNLOAD )
1063 then
1064 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1065 FND_MESSAGE.SET_NAME('AK','AK_FK_COLUMN_NOT_DOWNLOADED');
1066 FND_MESSAGE.SET_TOKEN('KEY', l_key_columns_rec.foreign_key_name ||
1067 ' ' ||
1068 to_char(l_key_columns_rec.attribute_application_id) ||
1069 ' ' || l_key_columns_rec.attribute_code);
1070 FND_MSG_PUB.Add;
1071 end if;
1072 close l_get_key_columns_csr;
1073 close l_get_foreign_key_tl_csr;
1074 close l_get_foreign_keys_csr;
1075 RAISE FND_API.G_EXC_ERROR;
1076 else
1077 l_index := l_index + 1;
1078 l_databuffer_tbl(l_index) := ' BEGIN FOREIGN_KEY_COLUMN "' ||
1079 nvl(to_char(l_key_columns_rec.attribute_application_id),'') ||
1080 '" "' ||
1081 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1082 l_key_columns_rec.attribute_code) || '"';
1083 l_index := l_index + 1;
1084 l_databuffer_tbl(l_index) := ' FOREIGN_KEY_SEQUENCE = "' ||
1085 nvl(to_char(l_key_columns_rec.foreign_key_sequence),'') || '"';
1086 -- Flex Fields
1087 --
1088 if ((l_key_columns_rec.attribute_category IS NOT NULL) and
1089 (l_key_columns_rec.attribute_category <> FND_API.G_MISS_CHAR)) then
1090 l_index := l_index + 1;
1091 l_databuffer_tbl(l_index) := ' ATTRIBUTE_CATEGORY = "' ||
1092 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1093 l_key_columns_rec.attribute_category) || '"';
1094 end if;
1095 if ((l_key_columns_rec.attribute1 IS NOT NULL) and
1096 (l_key_columns_rec.attribute1 <> FND_API.G_MISS_CHAR)) then
1097 l_index := l_index + 1;
1098 l_databuffer_tbl(l_index) := ' ATTRIBUTE1 = "' ||
1099 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1100 l_key_columns_rec.attribute1) || '"';
1101 end if;
1102 if ((l_key_columns_rec.attribute2 IS NOT NULL) and
1103 (l_key_columns_rec.attribute2 <> FND_API.G_MISS_CHAR)) then
1104 l_index := l_index + 1;
1105 l_databuffer_tbl(l_index) := ' ATTRIBUTE2 = "' ||
1106 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1107 l_key_columns_rec.attribute2) || '"';
1108 end if;
1109 if ((l_key_columns_rec.attribute3 IS NOT NULL) and
1110 (l_key_columns_rec.attribute3 <> FND_API.G_MISS_CHAR)) then
1111 l_index := l_index + 1;
1112 l_databuffer_tbl(l_index) := ' ATTRIBUTE3 = "' ||
1113 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1114 l_key_columns_rec.attribute3) || '"';
1115 end if;
1116 if ((l_key_columns_rec.attribute4 IS NOT NULL) and
1117 (l_key_columns_rec.attribute4 <> FND_API.G_MISS_CHAR)) then
1118 l_index := l_index + 1;
1119 l_databuffer_tbl(l_index) := ' ATTRIBUTE4 = "' ||
1120 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1121 l_key_columns_rec.attribute4) || '"';
1122 end if;
1123 if ((l_key_columns_rec.attribute5 IS NOT NULL) and
1124 (l_key_columns_rec.attribute5 <> FND_API.G_MISS_CHAR)) then
1125 l_index := l_index + 1;
1126 l_databuffer_tbl(l_index) := ' ATTRIBUTE5 = "' ||
1127 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1128 l_key_columns_rec.attribute5) || '"';
1129 end if;
1130 if ((l_key_columns_rec.attribute6 IS NOT NULL) and
1131 (l_key_columns_rec.attribute6 <> FND_API.G_MISS_CHAR)) then
1132 l_index := l_index + 1;
1133 l_databuffer_tbl(l_index) := ' ATTRIBUTE6 = "' ||
1134 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1135 l_key_columns_rec.attribute6) || '"';
1136 end if;
1137 if ((l_key_columns_rec.attribute7 IS NOT NULL) and
1138 (l_key_columns_rec.attribute7 <> FND_API.G_MISS_CHAR)) then
1139 l_index := l_index + 1;
1140 l_databuffer_tbl(l_index) := ' ATTRIBUTE7 = "' ||
1141 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1142 l_key_columns_rec.attribute7) || '"';
1143 end if;
1144 if ((l_key_columns_rec.attribute8 IS NOT NULL) and
1145 (l_key_columns_rec.attribute8 <> FND_API.G_MISS_CHAR)) then
1146 l_index := l_index + 1;
1147 l_databuffer_tbl(l_index) := ' ATTRIBUTE8 = "' ||
1148 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1149 l_key_columns_rec.attribute8) || '"';
1150 end if;
1151 if ((l_key_columns_rec.attribute9 IS NOT NULL) and
1152 (l_key_columns_rec.attribute9 <> FND_API.G_MISS_CHAR)) then
1153 l_index := l_index + 1;
1154 l_databuffer_tbl(l_index) := ' ATTRIBUTE9 = "' ||
1155 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1156 l_key_columns_rec.attribute9) || '"';
1157 end if;
1158 if ((l_key_columns_rec.attribute10 IS NOT NULL) and
1159 (l_key_columns_rec.attribute10 <> FND_API.G_MISS_CHAR)) then
1160 l_index := l_index + 1;
1161 l_databuffer_tbl(l_index) := ' ATTRIBUTE10 = "' ||
1162 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1163 l_key_columns_rec.attribute10) || '"';
1164 end if;
1165 if ((l_key_columns_rec.attribute11 IS NOT NULL) and
1166 (l_key_columns_rec.attribute11 <> FND_API.G_MISS_CHAR)) then
1167 l_index := l_index + 1;
1168 l_databuffer_tbl(l_index) := ' ATTRIBUTE11 = "' ||
1169 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1170 l_key_columns_rec.attribute11) || '"';
1171 end if;
1172 if ((l_key_columns_rec.attribute12 IS NOT NULL) and
1173 (l_key_columns_rec.attribute12 <> FND_API.G_MISS_CHAR)) then
1174 l_index := l_index + 1;
1175 l_databuffer_tbl(l_index) := ' ATTRIBUTE12 = "' ||
1176 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1177 l_key_columns_rec.attribute12) || '"';
1178 end if;
1179 if ((l_key_columns_rec.attribute13 IS NOT NULL) and
1180 (l_key_columns_rec.attribute13 <> FND_API.G_MISS_CHAR)) then
1181 l_index := l_index + 1;
1182 l_databuffer_tbl(l_index) := ' ATTRIBUTE13 = "' ||
1183 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1184 l_key_columns_rec.attribute13) || '"';
1185 end if;
1186 if ((l_key_columns_rec.attribute14 IS NOT NULL) and
1187 (l_key_columns_rec.attribute14 <> FND_API.G_MISS_CHAR)) then
1188 l_index := l_index + 1;
1189 l_databuffer_tbl(l_index) := ' ATTRIBUTE14 = "' ||
1190 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1191 l_key_columns_rec.attribute14) || '"';
1192 end if;
1193 if ((l_key_columns_rec.attribute15 IS NOT NULL) and
1194 (l_key_columns_rec.attribute15 <> FND_API.G_MISS_CHAR)) then
1195 l_index := l_index + 1;
1196 l_databuffer_tbl(l_index) := ' ATTRIBUTE15 = "' ||
1197 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1198 l_key_columns_rec.attribute15) || '"';
1199 end if;
1200 -- - Write out who columns
1201 l_index := l_index + 1;
1202 l_databuffer_tbl(l_index) := ' CREATED_BY = "' ||
1203 nvl(to_char(l_key_columns_rec.created_by),'') || '"';
1204 l_index := l_index + 1;
1205 l_databuffer_tbl(l_index) := ' CREATION_DATE = "' ||
1206 to_char(l_key_columns_rec.creation_date,
1207 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1208 l_index := l_index + 1;
1209 -- CHANGED TO OWNER FOR R12
1210 --l_databuffer_tbl(l_index) := ' LAST_UPDATED_BY = "' ||
1211 --nvl(to_char(l_key_columns_rec.last_updated_by),'') || '"';
1212 l_databuffer_tbl(l_index) := ' OWNER = "' ||
1213 FND_LOAD_UTIL.OWNER_NAME(l_key_columns_rec.last_updated_by) || '"';
1214 l_index := l_index + 1;
1215 l_databuffer_tbl(l_index) := ' LAST_UPDATE_DATE = "' ||
1216 to_char(l_key_columns_rec.last_update_date,
1217 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1218 l_index := l_index + 1;
1219 l_databuffer_tbl(l_index) := ' LAST_UPDATE_LOGIN = "' ||
1220 nvl(to_char(l_key_columns_rec.last_update_login),'') || '"';
1221
1222 l_index := l_index + 1;
1223 l_databuffer_tbl(l_index) := ' END FOREIGN_KEY_COLUMN';
1224 end if; -- foreign key column validation OK
1225 end loop;
1226 close l_get_key_columns_csr;
1227
1228 -- finish up foreign key
1229 l_index := l_index + 1;
1230 l_databuffer_tbl(l_index) := ' END FOREIGN_KEY';
1231 l_index := l_index + 1;
1232 l_databuffer_tbl(l_index) := ' ';
1233 end if;
1234
1235 end if; -- foreign key validation OK
1236
1237 close l_get_foreign_key_tl_csr;
1238
1239 end loop;
1240 close l_get_foreign_keys_csr;
1241
1242 -- - Write foreign key data out to the specified file
1243 -- don't call write_file if there is no data to be written
1244 if (l_databuffer_tbl.count > 0) then
1245 AK_ON_OBJECTS_PVT.WRITE_FILE (
1246 p_return_status => l_return_status,
1247 p_buffer_tbl => l_databuffer_tbl,
1248 p_write_mode => AK_ON_OBJECTS_PUB.G_APPEND
1249 );
1250 -- If API call returns with an error status...
1251 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1252 (l_return_status = FND_API.G_RET_STS_ERROR) then
1253 RAISE FND_API.G_EXC_ERROR;
1254 end if;
1255 end if;
1256
1257 p_return_status := FND_API.G_RET_STS_SUCCESS;
1258
1259 EXCEPTION
1260 WHEN VALUE_ERROR THEN
1261 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1262 FND_MESSAGE.SET_NAME('AK','AK_FOREIGN_KEY_VALUE_ERROR');
1263 FND_MESSAGE.SET_TOKEN('KEY', l_foreign_keys_rec.foreign_key_name);
1264 FND_MSG_PUB.Add;
1265 end if;
1266 p_return_status := FND_API.G_RET_STS_ERROR;
1267 if l_get_key_columns_csr%ISOPEN then
1268 close l_get_key_columns_csr;
1269 end if;
1270 if l_get_foreign_key_tl_csr%ISOPEN then
1271 close l_get_foreign_key_tl_csr;
1272 end if;
1273 if l_get_foreign_keys_csr%ISOPEN then
1274 close l_get_foreign_keys_csr;
1275 end if;
1276 WHEN FND_API.G_EXC_ERROR THEN
1277 p_return_status := FND_API.G_RET_STS_ERROR;
1278 if l_get_key_columns_csr%ISOPEN then
1279 close l_get_key_columns_csr;
1280 end if;
1281 if l_get_foreign_key_tl_csr%ISOPEN then
1282 close l_get_foreign_key_tl_csr;
1283 end if;
1284 if l_get_foreign_keys_csr%ISOPEN then
1285 close l_get_foreign_keys_csr;
1286 end if;
1287 WHEN OTHERS THEN
1288 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1289 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1290 SUBSTR (SQLERRM, 1, 240) );
1291 FND_MSG_PUB.Add;
1292 if l_get_key_columns_csr%ISOPEN then
1293 close l_get_key_columns_csr;
1294 end if;
1295 if l_get_foreign_key_tl_csr%ISOPEN then
1296 close l_get_foreign_key_tl_csr;
1297 end if;
1298 if l_get_foreign_keys_csr%ISOPEN then
1299 close l_get_foreign_keys_csr;
1300 end if;
1301
1302 end WRITE_FOREIGN_KEY_TO_BUFFER;
1303
1304 --=======================================================
1305 -- Procedure WRITE_UNIQUE_KEY_TO_BUFFER (local procedure)
1306 --
1307 -- Usage Local procedure for writing all unique key definitions
1308 -- and their children records for the given object
1309 -- to the output file.
1310 -- Not designed to be called from outside this package.
1311 --
1312 -- Desc This procedure retrieves all Unique Key definitions
1313 -- that belongs to the given object from the database,
1314 -- as well as all unique key column definitions
1315 -- for these unique keys, and writes them to the output file
1316 -- in loader file format.
1317 --
1318 -- Results The API returns the standard p_return_status parameter
1319 -- indicating one of the standard return statuses :
1320 -- * Unexpected error
1321 -- * Error
1322 -- * Success
1323 -- Parameters
1324 -- p_database_object_name : IN required
1325 -- Key value of the Object record whose unique key
1326 -- definitions are to be extracted to the loader file.
1327 -- p_nls_language : IN required
1328 -- The NLS langauge that should be used when
1329 -- extracting data from the TL table
1330 --=======================================================
1331 procedure WRITE_UNIQUE_KEY_TO_BUFFER (
1332 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1333 p_return_status OUT NOCOPY VARCHAR2,
1334 p_database_object_name IN VARCHAR2
1335 ) is
1336 cursor l_get_unique_keys_csr is
1337 select *
1338 from AK_UNIQUE_KEYS
1339 where DATABASE_OBJECT_NAME = p_database_object_name;
1340 cursor l_get_key_columns_csr (unique_key_name_param varchar2) is
1341 select *
1342 from AK_UNIQUE_KEY_COLUMNS
1343 where UNIQUE_KEY_NAME = unique_key_name_param;
1344 l_api_name CONSTANT varchar2(30) := 'write_unique_key_to_buffer';
1345 l_index NUMBER;
1346 l_databuffer_tbl AK_ON_OBJECTS_PUB.Buffer_Tbl_Type;
1347 l_unique_keys_rec AK_UNIQUE_KEYS%ROWTYPE;
1348 l_key_columns_rec AK_UNIQUE_KEY_COLUMNS%ROWTYPE;
1349 l_return_status varchar2(1);
1350 begin
1351 -- Find out where the next buffer entry to be written to
1352 l_index := 1;
1353
1354 -- Retrieve unique key and key columns information from the database
1355
1356 open l_get_unique_keys_csr;
1357 loop
1358 fetch l_get_unique_keys_csr into l_unique_keys_rec;
1359 exit when l_get_unique_keys_csr%notfound;
1360 -- write this unique key to buffer if it is valid
1361 if (p_validation_level <> FND_API.G_VALID_LEVEL_NONE) and
1362 not AK_KEY_PVT.VALIDATE_UNIQUE_KEY (
1363 p_validation_level => p_validation_level,
1364 p_api_version_number => 1.0,
1365 p_return_status => l_return_status,
1366 p_unique_key_name => l_unique_keys_rec.unique_key_name,
1367 p_database_object_name => l_unique_keys_rec.database_object_name,
1368 p_application_id => l_unique_keys_rec.application_id,
1369 p_caller => AK_ON_OBJECTS_PVT.G_DOWNLOAD )
1370 then
1371 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1372 FND_MESSAGE.SET_NAME('AK','AK_UNIQUE_KEY_NOT_DOWNLOADED');
1373 FND_MESSAGE.SET_TOKEN('KEY', l_unique_keys_rec.unique_key_name);
1374 FND_MSG_PUB.Add;
1375 end if;
1376 else
1377 l_databuffer_tbl(l_index) := ' ';
1378 l_index := l_index + 1;
1379 l_databuffer_tbl(l_index) := ' BEGIN UNIQUE_KEY "' ||
1380 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_unique_keys_rec.unique_key_name)|| '"';
1381 l_index := l_index + 1;
1382 l_databuffer_tbl(l_index) := ' APPLICATION_ID = "' ||
1383 nvl(to_char(l_unique_keys_rec.application_id),'') || '"';
1384 -- Flex Fields
1385 --
1386 if ((l_unique_keys_rec.attribute_category IS NOT NULL) and
1387 (l_unique_keys_rec.attribute_category <> FND_API.G_MISS_CHAR)) then
1388 l_index := l_index + 1;
1389 l_databuffer_tbl(l_index) := ' ATTRIBUTE_CATEGORY = "' ||
1390 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1391 l_unique_keys_rec.attribute_category) || '"';
1392 end if;
1393 if ((l_unique_keys_rec.attribute1 IS NOT NULL) and
1394 (l_unique_keys_rec.attribute1 <> FND_API.G_MISS_CHAR)) then
1395 l_index := l_index + 1;
1396 l_databuffer_tbl(l_index) := ' ATTRIBUTE1 = "' ||
1397 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1398 l_unique_keys_rec.attribute1) || '"';
1399 end if;
1400 if ((l_unique_keys_rec.attribute2 IS NOT NULL) and
1401 (l_unique_keys_rec.attribute2 <> FND_API.G_MISS_CHAR)) then
1402 l_index := l_index + 1;
1403 l_databuffer_tbl(l_index) := ' ATTRIBUTE2 = "' ||
1404 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1405 l_unique_keys_rec.attribute2) || '"';
1406 end if;
1407 if ((l_unique_keys_rec.attribute3 IS NOT NULL) and
1408 (l_unique_keys_rec.attribute3 <> FND_API.G_MISS_CHAR)) then
1409 l_index := l_index + 1;
1410 l_databuffer_tbl(l_index) := ' ATTRIBUTE3 = "' ||
1411 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1412 l_unique_keys_rec.attribute3) || '"';
1413 end if;
1414 if ((l_unique_keys_rec.attribute4 IS NOT NULL) and
1415 (l_unique_keys_rec.attribute4 <> FND_API.G_MISS_CHAR)) then
1416 l_index := l_index + 1;
1417 l_databuffer_tbl(l_index) := ' ATTRIBUTE4 = "' ||
1418 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1419 l_unique_keys_rec.attribute4) || '"';
1420 end if;
1421 if ((l_unique_keys_rec.attribute5 IS NOT NULL) and
1422 (l_unique_keys_rec.attribute5 <> FND_API.G_MISS_CHAR)) then
1423 l_index := l_index + 1;
1424 l_databuffer_tbl(l_index) := ' ATTRIBUTE5 = "' ||
1425 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1426 l_unique_keys_rec.attribute5) || '"';
1427 end if;
1428 if ((l_unique_keys_rec.attribute6 IS NOT NULL) and
1429 (l_unique_keys_rec.attribute6 <> FND_API.G_MISS_CHAR)) then
1430 l_index := l_index + 1;
1431 l_databuffer_tbl(l_index) := ' ATTRIBUTE6 = "' ||
1432 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1433 l_unique_keys_rec.attribute6) || '"';
1434 end if;
1435 if ((l_unique_keys_rec.attribute7 IS NOT NULL) and
1436 (l_unique_keys_rec.attribute7 <> FND_API.G_MISS_CHAR)) then
1437 l_index := l_index + 1;
1438 l_databuffer_tbl(l_index) := ' ATTRIBUTE7 = "' ||
1439 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1440 l_unique_keys_rec.attribute7) || '"';
1441 end if;
1442 if ((l_unique_keys_rec.attribute8 IS NOT NULL) and
1443 (l_unique_keys_rec.attribute8 <> FND_API.G_MISS_CHAR)) then
1444 l_index := l_index + 1;
1445 l_databuffer_tbl(l_index) := ' ATTRIBUTE8 = "' ||
1446 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1447 l_unique_keys_rec.attribute8) || '"';
1448 end if;
1449 if ((l_unique_keys_rec.attribute9 IS NOT NULL) and
1450 (l_unique_keys_rec.attribute9 <> FND_API.G_MISS_CHAR)) then
1451 l_index := l_index + 1;
1452 l_databuffer_tbl(l_index) := ' ATTRIBUTE9 = "' ||
1453 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1454 l_unique_keys_rec.attribute9) || '"';
1455 end if;
1456 if ((l_unique_keys_rec.attribute10 IS NOT NULL) and
1457 (l_unique_keys_rec.attribute10 <> FND_API.G_MISS_CHAR)) then
1458 l_index := l_index + 1;
1459 l_databuffer_tbl(l_index) := ' ATTRIBUTE10 = "' ||
1460 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1461 l_unique_keys_rec.attribute10) || '"';
1462 end if;
1463 if ((l_unique_keys_rec.attribute11 IS NOT NULL) and
1464 (l_unique_keys_rec.attribute11 <> FND_API.G_MISS_CHAR)) then
1465 l_index := l_index + 1;
1466 l_databuffer_tbl(l_index) := ' ATTRIBUTE11 = "' ||
1467 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1468 l_unique_keys_rec.attribute11) || '"';
1469 end if;
1470 if ((l_unique_keys_rec.attribute12 IS NOT NULL) and
1471 (l_unique_keys_rec.attribute12 <> FND_API.G_MISS_CHAR)) then
1472 l_index := l_index + 1;
1473 l_databuffer_tbl(l_index) := ' ATTRIBUTE12 = "' ||
1474 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1475 l_unique_keys_rec.attribute12) || '"';
1476 end if;
1477 if ((l_unique_keys_rec.attribute13 IS NOT NULL) and
1478 (l_unique_keys_rec.attribute13 <> FND_API.G_MISS_CHAR)) then
1479 l_index := l_index + 1;
1480 l_databuffer_tbl(l_index) := ' ATTRIBUTE13 = "' ||
1481 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1482 l_unique_keys_rec.attribute13) || '"';
1483 end if;
1484 if ((l_unique_keys_rec.attribute14 IS NOT NULL) and
1485 (l_unique_keys_rec.attribute14 <> FND_API.G_MISS_CHAR)) then
1486 l_index := l_index + 1;
1487 l_databuffer_tbl(l_index) := ' ATTRIBUTE14 = "' ||
1488 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1489 l_unique_keys_rec.attribute14) || '"';
1490 end if;
1491 if ((l_unique_keys_rec.attribute15 IS NOT NULL) and
1492 (l_unique_keys_rec.attribute15 <> FND_API.G_MISS_CHAR)) then
1493 l_index := l_index + 1;
1494 l_databuffer_tbl(l_index) := ' ATTRIBUTE15 = "' ||
1495 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1496 l_unique_keys_rec.attribute15) || '"';
1497 end if;
1498 -- - Write out who columns
1499 l_index := l_index + 1;
1500 l_databuffer_tbl(l_index) := ' CREATED_BY = "' ||
1501 nvl(to_char(l_unique_keys_rec.created_by),'') || '"';
1502 l_index := l_index + 1;
1503 l_databuffer_tbl(l_index) := ' CREATION_DATE = "' ||
1504 to_char(l_unique_keys_rec.creation_date,
1505 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1506 l_index := l_index + 1;
1507 -- CHANGED TO OWNER FOR R12
1508 --l_databuffer_tbl(l_index) := ' LAST_UPDATED_BY = "' ||
1509 --nvl(to_char(l_unique_keys_rec.last_updated_by),'') || '"';
1510 l_databuffer_tbl(l_index) := ' OWNER = "' ||
1511 FND_LOAD_UTIL.OWNER_NAME(l_unique_keys_rec.last_updated_by) || '"';
1512 l_index := l_index + 1;
1513 l_databuffer_tbl(l_index) := ' LAST_UPDATE_DATE = "' ||
1514 to_char(l_unique_keys_rec.last_update_date,
1515 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1516 l_index := l_index + 1;
1517 l_databuffer_tbl(l_index) := ' LAST_UPDATE_LOGIN = "' ||
1518 nvl(to_char(l_unique_keys_rec.last_update_login),'') || '"';
1519
1520
1521 -- Unique Key columns
1522 open l_get_key_columns_csr(l_unique_keys_rec.unique_key_name);
1523 loop
1524 fetch l_get_key_columns_csr into l_key_columns_rec;
1525 exit when l_get_key_columns_csr%notfound;
1526 if (p_validation_level <> FND_API.G_VALID_LEVEL_NONE) and
1527 not AK_KEY_PVT.VALIDATE_UNIQUE_KEY_COLUMN (
1528 p_validation_level => p_validation_level,
1529 p_api_version_number => 1.0,
1530 p_return_status => l_return_status,
1531 p_unique_key_name => l_key_columns_rec.unique_key_name,
1532 p_attribute_application_id =>
1533 l_key_columns_rec.attribute_application_id,
1534 p_attribute_code => l_key_columns_rec.attribute_code,
1535 p_unique_key_sequence => l_key_columns_rec.unique_key_sequence,
1536 p_caller => AK_ON_OBJECTS_PVT.G_DOWNLOAD )
1537 then
1538 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1539 FND_MESSAGE.SET_NAME('AK','AK_UK_COLUMN_NOT_DOWNLOADED');
1540 FND_MESSAGE.SET_TOKEN('KEY', l_key_columns_rec.unique_key_name ||
1541 ' ' ||
1542 to_char(l_key_columns_rec.attribute_application_id) ||
1543 ' ' || l_key_columns_rec.attribute_code);
1544 FND_MSG_PUB.Add;
1545 end if;
1546 close l_get_key_columns_csr;
1547 close l_get_unique_keys_csr;
1548 RAISE FND_API.G_EXC_ERROR;
1549 else
1550 l_index := l_index + 1;
1551 l_databuffer_tbl(l_index) := ' BEGIN UNIQUE_KEY_COLUMN "' ||
1552 nvl(to_char(l_key_columns_rec.attribute_application_id),'') ||
1553 '" "' ||
1554 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1555 l_key_columns_rec.attribute_code) || '"';
1556 l_index := l_index + 1;
1557 l_databuffer_tbl(l_index) := ' UNIQUE_KEY_SEQUENCE = "' ||
1558 nvl(to_char(l_key_columns_rec.unique_key_sequence),'') || '"';
1559 -- Flex Fields
1560 --
1561 if ((l_key_columns_rec.attribute_category IS NOT NULL) and
1562 (l_key_columns_rec.attribute_category <> FND_API.G_MISS_CHAR)) then
1563 l_index := l_index + 1;
1564 l_databuffer_tbl(l_index) := ' ATTRIBUTE_CATEGORY = "' ||
1565 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1566 l_key_columns_rec.attribute_category) || '"';
1567 end if;
1568 if ((l_key_columns_rec.attribute1 IS NOT NULL) and
1569 (l_key_columns_rec.attribute1 <> FND_API.G_MISS_CHAR)) then
1570 l_index := l_index + 1;
1571 l_databuffer_tbl(l_index) := ' ATTRIBUTE1 = "' ||
1572 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1573 l_key_columns_rec.attribute1) || '"';
1574 end if;
1575 if ((l_key_columns_rec.attribute2 IS NOT NULL) and
1576 (l_key_columns_rec.attribute2 <> FND_API.G_MISS_CHAR)) then
1577 l_index := l_index + 1;
1578 l_databuffer_tbl(l_index) := ' ATTRIBUTE2 = "' ||
1579 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1580 l_key_columns_rec.attribute2) || '"';
1581 end if;
1582 if ((l_key_columns_rec.attribute3 IS NOT NULL) and
1583 (l_key_columns_rec.attribute3 <> FND_API.G_MISS_CHAR)) then
1584 l_index := l_index + 1;
1585 l_databuffer_tbl(l_index) := ' ATTRIBUTE3 = "' ||
1586 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1587 l_key_columns_rec.attribute3) || '"';
1588 end if;
1589 if ((l_key_columns_rec.attribute4 IS NOT NULL) and
1590 (l_key_columns_rec.attribute4 <> FND_API.G_MISS_CHAR)) then
1591 l_index := l_index + 1;
1592 l_databuffer_tbl(l_index) := ' ATTRIBUTE4 = "' ||
1593 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1594 l_key_columns_rec.attribute4) || '"';
1595 end if;
1596 if ((l_key_columns_rec.attribute5 IS NOT NULL) and
1597 (l_key_columns_rec.attribute5 <> FND_API.G_MISS_CHAR)) then
1598 l_index := l_index + 1;
1599 l_databuffer_tbl(l_index) := ' ATTRIBUTE5 = "' ||
1600 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1601 l_key_columns_rec.attribute5) || '"';
1602 end if;
1603 if ((l_key_columns_rec.attribute6 IS NOT NULL) and
1604 (l_key_columns_rec.attribute6 <> FND_API.G_MISS_CHAR)) then
1605 l_index := l_index + 1;
1606 l_databuffer_tbl(l_index) := ' ATTRIBUTE6 = "' ||
1607 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1608 l_key_columns_rec.attribute6) || '"';
1609 end if;
1610 if ((l_key_columns_rec.attribute7 IS NOT NULL) and
1611 (l_key_columns_rec.attribute7 <> FND_API.G_MISS_CHAR)) then
1612 l_index := l_index + 1;
1613 l_databuffer_tbl(l_index) := ' ATTRIBUTE7 = "' ||
1614 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1615 l_key_columns_rec.attribute7) || '"';
1616 end if;
1617 if ((l_key_columns_rec.attribute8 IS NOT NULL) and
1618 (l_key_columns_rec.attribute8 <> FND_API.G_MISS_CHAR)) then
1619 l_index := l_index + 1;
1620 l_databuffer_tbl(l_index) := ' ATTRIBUTE8 = "' ||
1621 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1622 l_key_columns_rec.attribute8) || '"';
1623 end if;
1624 if ((l_key_columns_rec.attribute9 IS NOT NULL) and
1625 (l_key_columns_rec.attribute9 <> FND_API.G_MISS_CHAR)) then
1626 l_index := l_index + 1;
1627 l_databuffer_tbl(l_index) := ' ATTRIBUTE9 = "' ||
1628 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1629 l_key_columns_rec.attribute9) || '"';
1630 end if;
1631 if ((l_key_columns_rec.attribute10 IS NOT NULL) and
1632 (l_key_columns_rec.attribute10 <> FND_API.G_MISS_CHAR)) then
1633 l_index := l_index + 1;
1634 l_databuffer_tbl(l_index) := ' ATTRIBUTE10 = "' ||
1635 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1636 l_key_columns_rec.attribute10) || '"';
1637 end if;
1638 if ((l_key_columns_rec.attribute11 IS NOT NULL) and
1639 (l_key_columns_rec.attribute11 <> FND_API.G_MISS_CHAR)) then
1640 l_index := l_index + 1;
1641 l_databuffer_tbl(l_index) := ' ATTRIBUTE11 = "' ||
1642 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1643 l_key_columns_rec.attribute11) || '"';
1644 end if;
1645 if ((l_key_columns_rec.attribute12 IS NOT NULL) and
1646 (l_key_columns_rec.attribute12 <> FND_API.G_MISS_CHAR)) then
1647 l_index := l_index + 1;
1648 l_databuffer_tbl(l_index) := ' ATTRIBUTE12 = "' ||
1649 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1650 l_key_columns_rec.attribute12) || '"';
1651 end if;
1652 if ((l_key_columns_rec.attribute13 IS NOT NULL) and
1653 (l_key_columns_rec.attribute13 <> FND_API.G_MISS_CHAR)) then
1654 l_index := l_index + 1;
1655 l_databuffer_tbl(l_index) := ' ATTRIBUTE13 = "' ||
1656 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1657 l_key_columns_rec.attribute13) || '"';
1658 end if;
1659 if ((l_key_columns_rec.attribute14 IS NOT NULL) and
1660 (l_key_columns_rec.attribute14 <> FND_API.G_MISS_CHAR)) then
1661 l_index := l_index + 1;
1662 l_databuffer_tbl(l_index) := ' ATTRIBUTE14 = "' ||
1663 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1664 l_key_columns_rec.attribute14) || '"';
1665 end if;
1666 if ((l_key_columns_rec.attribute15 IS NOT NULL) and
1667 (l_key_columns_rec.attribute15 <> FND_API.G_MISS_CHAR)) then
1668 l_index := l_index + 1;
1669 l_databuffer_tbl(l_index) := ' ATTRIBUTE15 = "' ||
1670 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1671 l_key_columns_rec.attribute15) || '"';
1672 end if;
1673 -- - Write out who columns
1674 l_index := l_index + 1;
1675 l_databuffer_tbl(l_index) := ' CREATED_BY = "' ||
1676 nvl(to_char(l_key_columns_rec.created_by),'') || '"';
1677 l_index := l_index + 1;
1678 l_databuffer_tbl(l_index) := ' CREATION_DATE = "' ||
1679 to_char(l_key_columns_rec.creation_date,
1680 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1681 l_index := l_index + 1;
1682 -- CHANGED TO OWNER FOR R12
1683 --l_databuffer_tbl(l_index) := ' LAST_UPDATED_BY = "' ||
1684 --nvl(to_char(l_key_columns_rec.last_updated_by),'') || '"';
1685 l_databuffer_tbl(l_index) := ' OWNER = "' ||
1686 FND_LOAD_UTIL.OWNER_NAME(l_key_columns_rec.last_updated_by) || '"';
1687 l_index := l_index + 1;
1688 l_databuffer_tbl(l_index) := ' LAST_UPDATE_DATE = "' ||
1689 to_char(l_key_columns_rec.last_update_date,
1690 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1691 l_index := l_index + 1;
1692 l_databuffer_tbl(l_index) := ' LAST_UPDATE_LOGIN = "' ||
1693 nvl(to_char(l_key_columns_rec.last_update_login),'') || '"';
1694
1695 l_index := l_index + 1;
1696 l_databuffer_tbl(l_index) := ' END UNIQUE_KEY_COLUMN';
1697 end if; -- unique key column validation OK
1698 end loop;
1699 close l_get_key_columns_csr;
1700
1701 -- finish up unique key
1702 l_index := l_index + 1;
1703 l_databuffer_tbl(l_index) := ' END UNIQUE_KEY';
1704 l_index := l_index + 1;
1705 l_databuffer_tbl(l_index) := ' ';
1706 end if; -- unique key validation OK
1707
1708 end loop;
1709 close l_get_unique_keys_csr;
1710
1711 -- - Write unique key data out to the specified file
1712 -- don't call write_file if there is no data to be written
1713 if (l_databuffer_tbl.count > 0) then
1714 AK_ON_OBJECTS_PVT.WRITE_FILE (
1715 p_return_status => l_return_status,
1716 p_buffer_tbl => l_databuffer_tbl,
1717 p_write_mode => AK_ON_OBJECTS_PUB.G_APPEND
1718 );
1719 -- If API call returns with an error status...
1720 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1721 (l_return_status = FND_API.G_RET_STS_ERROR) then
1722 RAISE FND_API.G_EXC_ERROR;
1723 end if;
1724 end if;
1725
1726 p_return_status := FND_API.G_RET_STS_SUCCESS;
1727
1728 EXCEPTION
1729 WHEN VALUE_ERROR THEN
1730 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1731 FND_MESSAGE.SET_NAME('AK','AK_UNIQUE_KEY_VALUE_ERROR');
1732 FND_MESSAGE.SET_TOKEN('KEY', l_unique_keys_rec.unique_key_name);
1733 FND_MSG_PUB.Add;
1734 end if;
1735 p_return_status := FND_API.G_RET_STS_ERROR;
1736 WHEN FND_API.G_EXC_ERROR THEN
1737 p_return_status := FND_API.G_RET_STS_ERROR;
1738 WHEN OTHERS THEN
1739 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1740 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1741 SUBSTR (SQLERRM, 1, 240) );
1742 FND_MSG_PUB.Add;
1743 end WRITE_UNIQUE_KEY_TO_BUFFER;
1744
1745 --=======================================================
1746 -- Procedure WRITE_TO_BUFFER (local procedure)
1747 --
1748 -- Usage Local procedure for writing the given object
1749 -- and all its children records to the output file.
1750 -- Not designed to be called from outside this package.
1751 --
1752 -- Desc This procedure first retrieves and writes the given
1753 -- object to the loader file. Then it calls other local
1754 -- procedures to write all its object attributes and
1755 -- foriegn and unique key definitions to the same output
1756 -- file.
1757 --
1758 -- Results The API returns the standard p_return_status parameter
1759 -- indicating one of the standard return statuses :
1760 -- * Unexpected error
1761 -- * Error
1762 -- * Success
1763 -- Parameters
1764 -- p_database_object_name : IN required
1765 -- Key value of the Object to be extracted to the loader
1766 -- file.
1767 -- p_nls_language : IN required
1768 -- The NLS langauge that should be used when
1769 -- extracting data from the TL table
1770 --=======================================================
1771 procedure WRITE_TO_BUFFER (
1772 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1773 p_return_status OUT NOCOPY VARCHAR2,
1774 p_database_object_name IN VARCHAR2,
1775 p_nls_language IN VARCHAR2
1776 ) is
1777 cursor l_get_object_csr is
1778 select *
1779 from AK_OBJECTS
1780 where DATABASE_OBJECT_NAME = p_database_object_name;
1781 cursor l_get_object_tl_csr is
1782 select *
1783 from AK_OBJECTS_TL
1784 where database_object_name = p_database_object_name
1785 and language = p_nls_language;
1786 l_api_name CONSTANT varchar2(30) := 'Write_to_buffer';
1787 l_databuffer_tbl AK_ON_OBJECTS_PUB.Buffer_Tbl_Type;
1788 l_index NUMBER;
1789 l_objects_rec AK_OBJECTS%ROWTYPE;
1790 l_objects_tl_rec AK_OBJECTS_TL%ROWTYPE;
1791 l_return_status varchar2(1);
1792 begin
1793 -- Retrieve object information from the database
1794
1795 open l_get_object_csr;
1796 fetch l_get_object_csr into l_objects_rec;
1797 if (l_get_object_csr%notfound) then
1798 close l_get_object_csr;
1799 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1800 FND_MESSAGE.SET_NAME('AK','AK_OBJECT_DOES_NOT_EXIST');
1801 FND_MSG_PUB.Add;
1802 end if;
1803 -- dbms_output.put_line('Cannot find object '||p_database_object_name);
1804 RAISE FND_API.G_EXC_ERROR;
1805 end if;
1806 close l_get_object_csr;
1807
1808 open l_get_object_tl_csr;
1809 fetch l_get_object_tl_csr into l_objects_tl_rec;
1810 if (l_get_object_tl_csr%notfound) then
1811 close l_get_object_tl_csr;
1812 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1813 FND_MESSAGE.SET_NAME('AK','AK_OBJECT_DOES_NOT_EXIST');
1814 FND_MSG_PUB.Add;
1815 end if;
1816 -- dbms_output.put_line('Cannot find object in ak_objects_tl '||p_database_object_name);
1817 RAISE FND_API.G_EXC_ERROR;
1818 end if;
1819 close l_get_object_tl_csr;
1820
1821 -- Object must be validated before it is written to the file
1822 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
1823 if not AK_OBJECT_PVT.VALIDATE_OBJECT (
1824 p_validation_level => p_validation_level,
1825 p_api_version_number => 1.0,
1826 p_return_status => l_return_status,
1827 p_database_object_name => p_database_object_name,
1828 p_name => l_objects_tl_rec.name,
1829 p_description => l_objects_tl_rec.description,
1830 p_application_id => l_objects_rec.application_id,
1831 p_primary_key_name => l_objects_rec.primary_key_name,
1832 p_defaulting_api_pkg => l_objects_rec.defaulting_api_pkg,
1833 p_defaulting_api_proc => l_objects_rec.defaulting_api_proc,
1834 p_validation_api_pkg => l_objects_rec.validation_api_pkg,
1835 p_validation_api_proc => l_objects_rec.validation_api_proc,
1836 p_caller => AK_ON_OBJECTS_PVT.G_DOWNLOAD)
1837 then
1838 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1839 FND_MESSAGE.SET_NAME('AK','AK_OBJECT_NOT_DOWNLOADED');
1840 FND_MESSAGE.SET_TOKEN('KEY', p_database_object_name);
1841 FND_MSG_PUB.Add;
1842 end if;
1843 -- dbms_output.put_line('Object ' || p_database_object_name
1844 -- || ' not downloaded due to validation error');
1845 --raise FND_API.G_EXC_ERROR;
1846 end if;
1847 end if;
1848
1849 -- Write object into buffer
1850 l_index := 1;
1851
1852 l_databuffer_tbl(l_index) := 'BEGIN OBJECT "' ||
1853 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_objects_rec.database_object_name) || '"';
1854 l_index := l_index + 1;
1855 l_databuffer_tbl(l_index) := ' APPLICATION_ID = "' ||
1856 nvl(to_char(l_objects_rec.application_id),'') || '"';
1857 if ((l_objects_rec.primary_key_name IS NOT NULL) and
1858 (l_objects_rec.primary_key_name <> FND_API.G_MISS_CHAR)) then
1859 l_index := l_index + 1;
1860 l_databuffer_tbl(l_index) := ' PRIMARY_KEY_NAME = "' ||
1861 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_objects_rec.primary_key_name) ||
1862 '"';
1863 end if;
1864 if ((l_objects_rec.defaulting_api_pkg IS NOT NULL) and
1865 (l_objects_rec.defaulting_api_pkg <> FND_API.G_MISS_CHAR)) then
1866 l_index := l_index + 1;
1867 l_databuffer_tbl(l_index) := ' DEFAULTING_API_PKG = "' ||
1868 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_objects_rec.defaulting_api_pkg)
1869 || '"';
1870 end if;
1871 if ((l_objects_rec.defaulting_api_proc IS NOT NULL) and
1872 (l_objects_rec.defaulting_api_proc <> FND_API.G_MISS_CHAR)) then
1873 l_index := l_index + 1;
1874 l_databuffer_tbl(l_index) := ' DEFAULTING_API_PROC = "' ||
1875 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_objects_rec.defaulting_api_proc)
1876 || '"';
1877 end if;
1878 if ((l_objects_rec.validation_api_pkg IS NOT NULL) and
1879 (l_objects_rec.validation_api_pkg <> FND_API.G_MISS_CHAR)) then
1880 l_index := l_index + 1;
1881 l_databuffer_tbl(l_index) := ' VALIDATION_API_PKG = "' ||
1882 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_objects_rec.validation_api_pkg)
1883 || '"';
1884 end if;
1885 if ((l_objects_rec.validation_api_proc IS NOT NULL) and
1886 (l_objects_rec.validation_api_proc <> FND_API.G_MISS_CHAR)) then
1887 l_index := l_index + 1;
1888 l_databuffer_tbl(l_index) := ' VALIDATION_API_PROC = "' ||
1889 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_objects_rec.validation_api_proc)
1890 || '"';
1891 end if;
1892 -- Flex Fields
1893 --
1894 if ((l_objects_rec.attribute_category IS NOT NULL) and
1895 (l_objects_rec.attribute_category <> FND_API.G_MISS_CHAR)) then
1896 l_index := l_index + 1;
1897 l_databuffer_tbl(l_index) := ' ATTRIBUTE_CATEGORY = "' ||
1898 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1899 l_objects_rec.attribute_category) || '"';
1900 end if;
1901 if ((l_objects_rec.attribute1 IS NOT NULL) and
1902 (l_objects_rec.attribute1 <> FND_API.G_MISS_CHAR)) then
1903 l_index := l_index + 1;
1904 l_databuffer_tbl(l_index) := ' ATTRIBUTE1 = "' ||
1905 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1906 l_objects_rec.attribute1) || '"';
1907 end if;
1908 if ((l_objects_rec.attribute2 IS NOT NULL) and
1909 (l_objects_rec.attribute2 <> FND_API.G_MISS_CHAR)) then
1910 l_index := l_index + 1;
1911 l_databuffer_tbl(l_index) := ' ATTRIBUTE2 = "' ||
1912 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1913 l_objects_rec.attribute2) || '"';
1914 end if;
1915 if ((l_objects_rec.attribute3 IS NOT NULL) and
1916 (l_objects_rec.attribute3 <> FND_API.G_MISS_CHAR)) then
1917 l_index := l_index + 1;
1918 l_databuffer_tbl(l_index) := ' ATTRIBUTE3 = "' ||
1919 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1920 l_objects_rec.attribute3) || '"';
1921 end if;
1922 if ((l_objects_rec.attribute4 IS NOT NULL) and
1923 (l_objects_rec.attribute4 <> FND_API.G_MISS_CHAR)) then
1924 l_index := l_index + 1;
1925 l_databuffer_tbl(l_index) := ' ATTRIBUTE4 = "' ||
1926 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1927 l_objects_rec.attribute4) || '"';
1928 end if;
1929 if ((l_objects_rec.attribute5 IS NOT NULL) and
1930 (l_objects_rec.attribute5 <> FND_API.G_MISS_CHAR)) then
1931 l_index := l_index + 1;
1932 l_databuffer_tbl(l_index) := ' ATTRIBUTE5 = "' ||
1933 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1934 l_objects_rec.attribute5) || '"';
1935 end if;
1936 if ((l_objects_rec.attribute6 IS NOT NULL) and
1937 (l_objects_rec.attribute6 <> FND_API.G_MISS_CHAR)) then
1938 l_index := l_index + 1;
1939 l_databuffer_tbl(l_index) := ' ATTRIBUTE6 = "' ||
1940 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1941 l_objects_rec.attribute6) || '"';
1942 end if;
1943 if ((l_objects_rec.attribute7 IS NOT NULL) and
1944 (l_objects_rec.attribute7 <> FND_API.G_MISS_CHAR)) then
1945 l_index := l_index + 1;
1946 l_databuffer_tbl(l_index) := ' ATTRIBUTE7 = "' ||
1947 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1948 l_objects_rec.attribute7) || '"';
1949 end if;
1950 if ((l_objects_rec.attribute8 IS NOT NULL) and
1951 (l_objects_rec.attribute8 <> FND_API.G_MISS_CHAR)) then
1952 l_index := l_index + 1;
1953 l_databuffer_tbl(l_index) := ' ATTRIBUTE8 = "' ||
1954 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1955 l_objects_rec.attribute8) || '"';
1956 end if;
1957 if ((l_objects_rec.attribute9 IS NOT NULL) and
1958 (l_objects_rec.attribute9 <> FND_API.G_MISS_CHAR)) then
1959 l_index := l_index + 1;
1960 l_databuffer_tbl(l_index) := ' ATTRIBUTE9 = "' ||
1961 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1962 l_objects_rec.attribute9) || '"';
1963 end if;
1964 if ((l_objects_rec.attribute10 IS NOT NULL) and
1965 (l_objects_rec.attribute10 <> FND_API.G_MISS_CHAR)) then
1966 l_index := l_index + 1;
1967 l_databuffer_tbl(l_index) := ' ATTRIBUTE10 = "' ||
1968 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1969 l_objects_rec.attribute10) || '"';
1970 end if;
1971 if ((l_objects_rec.attribute11 IS NOT NULL) and
1972 (l_objects_rec.attribute11 <> FND_API.G_MISS_CHAR)) then
1973 l_index := l_index + 1;
1974 l_databuffer_tbl(l_index) := ' ATTRIBUTE11 = "' ||
1975 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1976 l_objects_rec.attribute11) || '"';
1977 end if;
1978 if ((l_objects_rec.attribute12 IS NOT NULL) and
1979 (l_objects_rec.attribute12 <> FND_API.G_MISS_CHAR)) then
1980 l_index := l_index + 1;
1981 l_databuffer_tbl(l_index) := ' ATTRIBUTE12 = "' ||
1982 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1983 l_objects_rec.attribute12) || '"';
1984 end if;
1985 if ((l_objects_rec.attribute13 IS NOT NULL) and
1986 (l_objects_rec.attribute13 <> FND_API.G_MISS_CHAR)) then
1987 l_index := l_index + 1;
1988 l_databuffer_tbl(l_index) := ' ATTRIBUTE13 = "' ||
1989 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1990 l_objects_rec.attribute13) || '"';
1991 end if;
1992 if ((l_objects_rec.attribute14 IS NOT NULL) and
1993 (l_objects_rec.attribute14 <> FND_API.G_MISS_CHAR)) then
1994 l_index := l_index + 1;
1995 l_databuffer_tbl(l_index) := ' ATTRIBUTE14 = "' ||
1996 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
1997 l_objects_rec.attribute14) || '"';
1998 end if;
1999 if ((l_objects_rec.attribute15 IS NOT NULL) and
2000 (l_objects_rec.attribute15 <> FND_API.G_MISS_CHAR)) then
2001 l_index := l_index + 1;
2002 l_databuffer_tbl(l_index) := ' ATTRIBUTE15 = "' ||
2003 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
2004 l_objects_rec.attribute15) || '"';
2005 end if;
2006 -- - Write out who columns
2007 l_index := l_index + 1;
2008 l_databuffer_tbl(l_index) := ' CREATED_BY = "' ||
2009 nvl(to_char(l_objects_rec.created_by),'') || '"';
2010 l_index := l_index + 1;
2011 l_databuffer_tbl(l_index) := ' CREATION_DATE = "' ||
2012 to_char(l_objects_rec.creation_date,
2013 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
2014 l_index := l_index + 1;
2015 -- CHANGED TO OWNER FOR R12
2016 --l_databuffer_tbl(l_index) := ' LAST_UPDATED_BY = "' ||
2017 --nvl(to_char(l_objects_rec.last_updated_by),'') || '"';
2018 l_databuffer_tbl(l_index) := ' OWNER = "' ||
2019 FND_LOAD_UTIL.OWNER_NAME(l_objects_rec.last_updated_by) || '"';
2020 l_index := l_index + 1;
2021 l_databuffer_tbl(l_index) := ' LAST_UPDATE_DATE = "' ||
2022 to_char(l_objects_rec.last_update_date,
2023 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
2024 l_index := l_index + 1;
2025 l_databuffer_tbl(l_index) := ' LAST_UPDATE_LOGIN = "' ||
2026 nvl(to_char(l_objects_rec.last_update_login),'') || '"';
2027
2028 -- translation columns
2029 --
2030 if ((l_objects_tl_rec.name IS NOT NULL) and
2031 (l_objects_tl_rec.name <> FND_API.G_MISS_CHAR)) then
2032 l_index := l_index + 1;
2033 l_databuffer_tbl(l_index) := ' NAME = "' ||
2034 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_objects_tl_rec.name) || '"';
2035 end if;
2036 if ((l_objects_tl_rec.description IS NOT NULL) and
2037 (l_objects_tl_rec.description <> FND_API.G_MISS_CHAR)) then
2038 l_index := l_index + 1;
2039 l_databuffer_tbl(l_index) := ' DESCRIPTION = "' ||
2040 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_objects_tl_rec.description) ||
2041 '"';
2042 end if;
2043
2044 -- - Write object data out to the specified file
2045 AK_ON_OBJECTS_PVT.WRITE_FILE (
2046 p_return_status => l_return_status,
2047 p_buffer_tbl => l_databuffer_tbl,
2048 p_write_mode => AK_ON_OBJECTS_PUB.G_APPEND
2049 );
2050 -- If API call returns with an error status...
2051 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2052 (l_return_status = FND_API.G_RET_STS_ERROR) then
2053 RAISE FND_API.G_EXC_ERROR;
2054 end if;
2055
2056 l_databuffer_tbl.delete;
2057
2058 WRITE_ATTRIBUTE_TO_BUFFER (
2059 p_validation_level => p_validation_level,
2060 p_return_status => l_return_status,
2061 p_database_object_name => p_database_object_name,
2062 p_nls_language => p_nls_language
2063 );
2064 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2065 (l_return_status = FND_API.G_RET_STS_ERROR) then
2066 RAISE FND_API.G_EXC_ERROR;
2067 end if;
2068
2069 WRITE_UNIQUE_KEY_TO_BUFFER (
2070 p_validation_level => p_validation_level,
2071 p_return_status => l_return_status,
2072 p_database_object_name => p_database_object_name
2073 );
2074 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2075 (l_return_status = FND_API.G_RET_STS_ERROR) then
2076 RAISE FND_API.G_EXC_ERROR;
2077 end if;
2078
2079 WRITE_FOREIGN_KEY_TO_BUFFER (
2080 p_validation_level => p_validation_level,
2081 p_return_status => l_return_status,
2082 p_database_object_name => p_database_object_name,
2083 p_nls_language => p_nls_language
2084 );
2085 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2086 (l_return_status = FND_API.G_RET_STS_ERROR) then
2087 RAISE FND_API.G_EXC_ERROR;
2088 end if;
2089
2090 l_index := 1;
2091 l_databuffer_tbl(l_index) := 'END OBJECT';
2092 l_index := l_index + 1;
2093 l_databuffer_tbl(l_index) := ' ';
2094
2095 -- - Finish up writing object data out to the specified file
2096 AK_ON_OBJECTS_PVT.WRITE_FILE (
2097 p_return_status => l_return_status,
2098 p_buffer_tbl => l_databuffer_tbl,
2099 p_write_mode => AK_ON_OBJECTS_PUB.G_APPEND
2100 );
2101
2102 -- If API call returns with an error status...
2103 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2104 (l_return_status = FND_API.G_RET_STS_ERROR) then
2105 RAISE FND_API.G_EXC_ERROR;
2106 end if;
2107
2108 p_return_status := FND_API.G_RET_STS_SUCCESS;
2109
2110 EXCEPTION
2111 WHEN VALUE_ERROR THEN
2112 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2113 FND_MESSAGE.SET_NAME('AK','AK_OBJECT_VALUE_ERROR');
2114 FND_MESSAGE.SET_TOKEN('KEY', p_database_object_name);
2115 FND_MSG_PUB.Add;
2116 end if;
2117 p_return_status := FND_API.G_RET_STS_ERROR;
2118 WHEN FND_API.G_EXC_ERROR THEN
2119 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2120 FND_MESSAGE.SET_NAME('AK','AK_OBJECT_NOT_DOWNLOADED');
2121 FND_MESSAGE.SET_TOKEN('KEY', p_database_object_name);
2122 FND_MSG_PUB.Add;
2123 end if;
2124 p_return_status := FND_API.G_RET_STS_ERROR;
2125 WHEN OTHERS THEN
2126 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2127 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2128 SUBSTR (SQLERRM, 1, 240) );
2129 FND_MSG_PUB.Add;
2130 end WRITE_TO_BUFFER;
2131
2132 --=======================================================
2133 -- Procedure DOWNLOAD_OBJECT
2134 --
2135 -- Usage Private API for downloading objects. This
2136 -- API should only be called by other APIs that are
2137 -- owned by the Core Modules Team (AK).
2138 --
2139 -- Desc This API will extract the objects selected
2140 -- by application ID or by key values from the
2141 -- database to the output file.
2142 -- If an object is selected for writing to the loader
2143 -- file, all its children records (including object
2144 -- attributes, foreign and unique key definitions,
2145 -- attribute values, attribute navigation, and regions
2146 -- that references this object, depending on the
2147 -- value of p_get_region_flag) will also be written.
2148 --
2149 -- Results The API returns the standard p_return_status parameter
2150 -- indicating one of the standard return statuses :
2151 -- * Unexpected error
2152 -- * Error
2153 -- * Success
2154 -- Parameters
2155 -- p_attribute_pk_tbl : IN optional
2156 -- If given, attributes whose key values are
2157 -- included in this table will be extracted and
2158 -- written to the output file. This is used for
2159 -- extracting additional attributes, for instance,
2160 -- attributes that are referenced by the region items
2161 -- whose regions are referencing this object when
2162 -- this API is called by the DOWNLOAD_REGION API.
2163 -- p_nls_language : IN optional
2164 -- NLS language for database. If none if given,
2165 -- the current NLS language will be used.
2166 -- p_get_region_flag : IN required
2167 -- Call DOWNLOAD_REGION API to extract regions that
2168 -- are referencing the objects that will be extracted
2169 -- by this API if this parameter is 'Y'.
2170 --
2171 -- One of the following parameters must be provided:
2172 --
2173 -- p_application_id : IN optional
2174 -- If given, all attributes for this application ID
2175 -- will be written to the output file.
2176 -- p_application_id will be ignored if a table is
2177 -- given in p_object_pk_tbl.
2178 -- p_object_pk_tbl : IN optional
2179 -- If given, only objects whose key values are
2180 -- included in this table will be written to the
2181 -- output file.
2182 --
2183 --
2184 -- Version Initial version number = 1.0
2185 -- History Current version number = 1.0
2186 --=======================================================
2187 procedure DOWNLOAD_OBJECT (
2188 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2189 p_api_version_number IN NUMBER,
2190 p_return_status OUT NOCOPY VARCHAR2,
2191 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
2192 p_object_pk_tbl IN AK_OBJECT_PUB.Object_PK_Tbl_Type
2193 := AK_OBJECT_PUB.G_MISS_OBJECT_PK_TBL,
2194 p_attribute_pk_tbl IN AK_ATTRIBUTE_PUB.Attribute_PK_Tbl_Type
2195 := AK_ATTRIBUTE_PUB.G_MISS_ATTRIBUTE_PK_TBL,
2196 p_nls_language IN VARCHAR2,
2197 p_get_region_flag IN VARCHAR2
2198 ) is
2199 cursor l_get_object_list_csr (appl_id_parm number) is
2200 select database_object_name
2201 from AK_OBJECTS
2202 where APPLICATION_ID = appl_id_parm;
2203 cursor l_get_regions_csr (database_object_name_param varchar2) is
2204 select REGION_APPLICATION_ID, REGION_CODE
2205 from AK_REGIONS
2206 where DATABASE_OBJECT_NAME = database_object_name_param;
2207 cursor l_get_attributes_csr (database_object_name_param varchar2) is
2208 select ATTRIBUTE_APPLICATION_ID, ATTRIBUTE_CODE
2209 from AK_OBJECT_ATTRIBUTES
2210 where DATABASE_OBJECT_NAME = database_object_name_param;
2211 cursor l_get_region_items_csr (region_appl_id_param number,
2212 region_code_param varchar2) is
2213 select ATTRIBUTE_APPLICATION_ID, ATTRIBUTE_CODE
2214 from AK_REGION_ITEMS
2215 where REGION_APPLICATION_ID = region_appl_id_param
2216 and REGION_CODE = region_code_param;
2217 cursor l_get_fk_objects_csr (database_object_name_param varchar2) is
2218 select uk.DATABASE_OBJECT_NAME
2219 from AK_UNIQUE_KEYS uk, AK_FOREIGN_KEYS fk
2220 where uk.UNIQUE_KEY_NAME = fk.UNIQUE_KEY_NAME
2221 and fk.DATABASE_OBJECT_NAME = database_object_name_param;
2222 cursor l_get_attr_lov_regions_csr (database_object_name_param varchar2) is
2223 select aa.lov_region_application_id, aa.lov_region_code
2224 from ak_attributes aa, ak_object_attributes aoa
2225 where aa.attribute_application_id = aoa.attribute_application_id
2226 and aa.attribute_code = aoa.attribute_code
2227 and aoa.database_object_name = database_object_name_param
2228 and aa.lov_region_code is not null;
2229 cursor l_get_objattr_lov_regions_csr (database_object_name_param varchar2) is
2230 select lov_region_application_id, lov_region_code
2231 from ak_object_attributes
2232 where database_object_name = database_object_name_param
2233 and lov_region_code is not null;
2234 cursor l_get_region_lov_regions_csr (region_appl_id_param number,
2235 region_code_param varchar2) is
2236 select lov_region_application_id, lov_region_code
2237 from AK_REGION_ITEMS
2238 where REGION_APPLICATION_ID = region_appl_id_param
2239 and REGION_CODE = region_code_param
2240 and lov_region_code is not null;
2241 cursor l_get_region_object_csr (region_appl_id_param number,
2242 region_code_param varchar2) is
2243 select database_object_name
2244 from AK_REGIONS
2245 where REGION_APPLICATION_ID = region_appl_id_param
2246 and REGION_CODE = region_code_param;
2247 l_api_version_number CONSTANT number := 1.0;
2248 l_api_name CONSTANT varchar2(30) := 'Download_Object';
2249 l_attribute_appl_id NUMBER;
2250 l_attribute_code VARCHAR2(30);
2251 l_attribute_pk_tbl AK_ATTRIBUTE_PUB.Attribute_PK_Tbl_Type;
2252 l_database_object_name VARCHAR2(30);
2253 l_index NUMBER;
2254 l_last_orig_index NUMBER;
2255 l_msg_count NUMBER;
2256 l_msg_data VARCHAR2(2000);
2257 l_object_pk_tbl AK_OBJECT_PUB.Object_PK_Tbl_Type;
2258 l_region_appl_id NUMBER;
2259 l_region_code VARCHAR2(30);
2260 l_region_index NUMBER;
2261 l_region_tbl_last NUMBER;
2262 l_region_pk_tbl AK_REGION_PUB.Region_PK_Tbl_Type;
2263 l_return_status varchar2(1);
2264 begin
2265 IF NOT FND_API.Compatible_API_Call (
2266 l_api_version_number, p_api_version_number, l_api_name,
2267 G_PKG_NAME) then
2268 -- dbms_output.put_line('API error in AK_OBJECTS2_PVT');
2269 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2270 return;
2271 END IF;
2272
2273 -- Check that one of the following selection criteria is given:
2274 -- - p_application_id alone, or
2275 -- - object names in p_object_PK_tbl
2276
2277 if (p_application_id = FND_API.G_MISS_NUM) or (p_application_id is null) then
2278 if (p_object_PK_tbl.count = 0) then
2279 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2280 FND_MESSAGE.SET_NAME('AK','AK_NO_SELECTION');
2281 FND_MSG_PUB.Add;
2282 end if;
2283 raise FND_API.G_EXC_ERROR;
2284 end if;
2285 else
2286 if (p_object_PK_tbl.count > 0) then
2287 -- both application ID and a list of objects to be extracted are
2288 -- given, issue a warning that we will ignore the application ID
2289 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2290 FND_MESSAGE.SET_NAME('AK','AK_APPL_ID_IGNORED');
2291 FND_MSG_PUB.Add;
2292 end if;
2293 end if;
2294 end if;
2295
2296 -- If selecting by application ID, first load a object unique key table
2297 -- with the unique key of all objects for the given application ID.
2298 -- If selecting by a list of objects, simply copy the object unique key
2299 -- table with the parameter
2300 if (p_object_PK_tbl.count > 0) then
2301 l_object_pk_tbl := p_object_pk_tbl;
2302 else
2303 l_index := 1;
2304 open l_get_object_list_csr(p_application_id);
2305 loop
2306 fetch l_get_object_list_csr into l_object_pk_tbl(l_index);
2307 exit when l_get_object_list_csr%notfound;
2308 l_index := l_index + 1;
2309 end loop;
2310 close l_get_object_list_csr;
2311 end if;
2312
2313 -- Save the index of the last entry in the table. This marks the
2314 -- last object in the selection criteria.
2315 l_last_orig_index := l_object_pk_tbl.LAST;
2316 --dbms_output.put_line('l_last_orig_index is :' || to_char(l_last_orig_index));
2317
2318 -- Initialize region table index
2319 l_region_index := 1;
2320
2321 -- Initialize attribute table with parameter
2322 l_attribute_pk_tbl := p_attribute_pk_tbl;
2323
2324 -- Build list of regions and attributes that are needed by the
2325 -- list of objects that we are about to extract from the database.
2326 -- Also add additional objects that are referenced by any foreign
2327 -- keys to the object list to be extracted.
2328 --
2329 l_index := l_object_pk_tbl.FIRST;
2330
2331 while (l_index is not null) loop
2332 --
2333 -- if the download region flag is 'Y':
2334 --
2335 if (p_get_region_flag = 'Y') then
2336 --
2337 -- Remember the last element in the region table. This will
2338 -- be used to determine which regions are added to the table
2339 -- in this pass of the loop.
2340 --
2341 l_region_tbl_last := l_region_pk_tbl.last;
2342 --
2343 -- Add regions that refrences this object to the region list
2344 --
2345 open l_get_regions_csr(l_object_pk_tbl(l_index));
2346 loop
2347 fetch l_get_regions_csr into l_region_appl_id, l_region_code;
2348 exit when (l_get_regions_csr%notfound);
2349 AK_REGION_PVT.INSERT_REGION_PK_TABLE (
2350 p_return_status => l_return_status,
2351 p_region_application_id => l_region_appl_id,
2352 p_region_code => l_region_code,
2353 p_region_pk_tbl => l_region_pk_tbl);
2354 end loop;
2355 close l_get_regions_csr;
2356 --
2357 -- Add LOV Regions that are referenced by any object attribute
2358 -- or attribute that belongs to the current object.
2359 --
2360 open l_get_attr_lov_regions_csr(l_object_pk_tbl(l_index));
2361 loop
2362 fetch l_get_attr_lov_regions_csr into l_region_appl_id, l_region_code;
2363 exit when (l_get_attr_lov_regions_csr%notfound);
2364 AK_REGION_PVT.INSERT_REGION_PK_TABLE (
2365 p_return_status => l_return_status,
2366 p_region_application_id => l_region_appl_id,
2367 p_region_code => l_region_code,
2368 p_region_pk_tbl => l_region_pk_tbl);
2369 end loop;
2370 close l_get_attr_lov_regions_csr;
2371
2372 open l_get_objattr_lov_regions_csr(l_object_pk_tbl(l_index));
2373 loop
2374 fetch l_get_objattr_lov_regions_csr into l_region_appl_id,l_region_code;
2375 exit when (l_get_objattr_lov_regions_csr%notfound);
2376 AK_REGION_PVT.INSERT_REGION_PK_TABLE (
2377 p_return_status => l_return_status,
2378 p_region_application_id => l_region_appl_id,
2379 p_region_code => l_region_code,
2380 p_region_pk_tbl => l_region_pk_tbl);
2381 end loop;
2382 close l_get_objattr_lov_regions_csr;
2383
2384 --
2385 -- For each new region or LOV region added:
2386 --
2387 l_region_index := l_region_pk_tbl.next(l_region_tbl_last);
2388 while (l_region_index is not null) loop
2389 --
2390 -- 1. If there is any LOV region referenced by some of its
2391 -- region item, add these LOV regions to the region list
2392 --
2393 open l_get_region_lov_regions_csr(
2394 l_region_pk_tbl(l_region_index).region_appl_id,
2395 l_region_pk_tbl(l_region_index).region_code);
2396 loop
2397 fetch l_get_region_lov_regions_csr into l_region_appl_id,
2398 l_region_code;
2399 exit when (l_get_region_lov_regions_csr%notfound);
2400 AK_REGION_PVT.INSERT_REGION_PK_TABLE (
2401 p_return_status => l_return_status,
2402 p_region_application_id => l_region_appl_id,
2403 p_region_code => l_region_code,
2404 p_region_pk_tbl => l_region_pk_tbl);
2405 end loop;
2406 close l_get_region_lov_regions_csr;
2407 --
2408 -- 2. Build a list of attributes that we need to extract
2409 -- because they are referenced by some region items in this
2410 -- region.
2411 --
2412 if (AK_DOWNLOAD_GRP.G_DOWNLOAD_ATTR = 'Y') then
2413 open l_get_region_items_csr (
2414 l_region_pk_tbl(l_region_index).region_appl_id,
2415 l_region_pk_tbl(l_region_index).region_code);
2416 loop
2417 fetch l_get_region_items_csr into l_attribute_appl_id,
2418 l_attribute_code;
2419 exit when (l_get_region_items_csr%notfound);
2420 AK_ATTRIBUTE_PVT.INSERT_ATTRIBUTE_PK_TABLE (
2421 p_return_status => l_return_status,
2422 p_attribute_application_id => l_attribute_appl_id,
2423 p_attribute_code => l_attribute_code,
2424 p_attribute_pk_tbl => l_attribute_pk_tbl);
2425 end loop;
2426 close l_get_region_items_csr;
2427 end if;
2428 --
2429 -- 3. Finally, add the object referenced by this region to
2430 -- the object list so that the object will also be downloded.
2431 --
2432 open l_get_region_object_csr (
2433 l_region_pk_tbl(l_region_index).region_appl_id,
2434 l_region_pk_tbl(l_region_index).region_code);
2435 loop
2436 fetch l_get_region_object_csr into l_database_object_name;
2437 exit when (l_get_region_object_csr%notfound);
2438 AK_OBJECT_PVT.INSERT_OBJECT_PK_TABLE (
2439 p_return_status => l_return_status,
2440 p_database_object_name => l_database_object_name,
2441 p_object_pk_tbl => l_object_pk_tbl);
2442 end loop;
2443 close l_get_region_object_csr;
2444 --
2445 -- 4. Increment index counter for processing of the next new
2446 -- region.
2447 l_region_index := l_region_pk_tbl.next(l_region_index);
2448
2449 end loop; /* while l_region_index is not null */
2450 end if; /* p_get_region_flag = 'Y' */
2451
2452 -- Build list of attributes that are referenced in object attributes
2453 -- for this object.
2454 if (AK_DOWNLOAD_GRP.G_DOWNLOAD_ATTR = 'Y') then
2455 open l_get_attributes_csr (l_object_pk_tbl(l_index));
2456 loop
2457 fetch l_get_attributes_csr into l_attribute_appl_id, l_attribute_code;
2458 exit when (l_get_attributes_csr%notfound);
2459 AK_ATTRIBUTE_PVT.INSERT_ATTRIBUTE_PK_TABLE (
2460 p_return_status => l_return_status,
2461 p_attribute_application_id => l_attribute_appl_id,
2462 p_attribute_code => l_attribute_code,
2463 p_attribute_pk_tbl => l_attribute_pk_tbl);
2464 end loop;
2465 close l_get_attributes_csr;
2466 end if;
2467
2468 -- Add objects that contain unique keys which were referenced by
2469 -- any of the foreign keys of the object currently being downloaded
2470 open l_get_fk_objects_csr (l_object_pk_tbl(l_index));
2471 loop
2472 fetch l_get_fk_objects_csr into l_database_object_name;
2473 exit when (l_get_fk_objects_csr%notfound);
2474 AK_OBJECT_PVT.INSERT_OBJECT_PK_TABLE (
2475 p_return_status => l_return_status,
2476 p_database_object_name => l_database_object_name,
2477 p_object_pk_tbl => l_object_pk_tbl);
2478 end loop;
2479 close l_get_fk_objects_csr;
2480
2481 -- Ready to download the next object in the list
2482 l_index := l_object_pk_tbl.NEXT(l_index);
2483
2484 end loop; /* while l_index is not null */
2485
2486 -- set l_index to the last index number in the object table
2487 -- l_index := l_object_pk_tbl.LAST;
2488
2489 -- Download attributes that are in the attribute list.
2490 -- These are attributes that needs to be extracted since they were
2491 -- referenced by the object attributes or region items that were
2492 -- being extracted from the database.
2493
2494 if (AK_DOWNLOAD_GRP.G_DOWNLOAD_ATTR = 'Y') then
2495 if (l_attribute_pk_tbl.count > 0) then
2496 AK_ATTRIBUTE_PVT.DOWNLOAD_ATTRIBUTE (
2497 p_validation_level => p_validation_level,
2498 p_api_version_number => 1.0,
2499 p_return_status => l_return_status,
2500 p_attribute_pk_tbl => l_attribute_pk_tbl,
2501 p_nls_language => p_nls_language
2502 );
2503
2504
2505 if (l_return_status = FND_API.G_RET_STS_ERROR) or
2506 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
2507 -- dbms_output.put_line(l_api_name || ' Error downloading attributes');
2508 raise FND_API.G_EXC_ERROR;
2509 end if;
2510
2511 end if;
2512 end if;
2513
2514 if (AK_DOWNLOAD_GRP.G_DOWNLOAD_REG = 'Y') then
2515 -- Write details for each selected object, including its object
2516 -- attributes, unique and foreign key definitions, etc. to a
2517 -- buffer to be passed back to the calling procedure.
2518 --
2519 l_index := l_object_pk_tbl.FIRST;
2520
2521 while (l_index is not null) loop
2522 -- Write object information from the database
2523
2524 --dbms_output.put_line('writing object #'||to_char(l_index) || ':' ||
2525 -- l_object_pk_tbl(l_index));
2526
2527 WRITE_TO_BUFFER(
2528 p_validation_level => p_validation_level,
2529 p_return_status => l_return_status,
2530 p_database_object_name => l_object_pk_tbl(l_index),
2531 p_nls_language => p_nls_language
2532 );
2533 -- Download aborts if any of the validation fails
2534 --
2535 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2536 (l_return_status = FND_API.G_RET_STS_ERROR) then
2537 RAISE FND_API.G_EXC_ERROR;
2538 end if;
2539
2540 -- Ready to download the next object in the list
2541 l_index := l_object_pk_tbl.NEXT(l_index);
2542
2543 end loop;
2544 end if; /*G_DOWNLOAD_REG*/
2545
2546 -- Download region information for regions that were based on any
2547 -- of the extracted objects.
2548
2549 /*
2550 for l_region_index in l_region_pk_tbl.FIRST .. l_region_pk_tbl.LAST LOOP
2551 if l_region_pk_tbl.exists(l_region_index) then
2552 -- dbms_output.put_line('Region list #' || to_char(l_region_index) || ' ' ||
2553 -- l_region_pk_tbl(l_region_index).region_code);
2554 end if;
2555 end loop;
2556 */
2557
2558 if (l_region_pk_tbl.count > 0) then
2559 AK_REGION_PVT.DOWNLOAD_REGION (
2560 p_validation_level => p_validation_level,
2561 p_api_version_number => 1.0,
2562 p_return_status => l_return_status,
2563 p_region_pk_tbl => l_region_pk_tbl,
2564 p_nls_language => p_nls_language,
2565 p_get_object_flag => 'N' -- No need to get objects for regions
2566 );
2567 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2568 (l_return_status = FND_API.G_RET_STS_ERROR) then
2569 RAISE FND_API.G_EXC_ERROR;
2570 end if;
2571 end if;
2572
2573 p_return_status := FND_API.G_RET_STS_SUCCESS;
2574
2575 -- dbms_output.put_line('returning from ak_object_pvt.download_object: ' ||
2576 -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
2577
2578 EXCEPTION
2579 WHEN VALUE_ERROR THEN
2580 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2581 FND_MESSAGE.SET_NAME('AK','AK_OBJECT_PK_VALUE_ERROR');
2582 FND_MSG_PUB.Add;
2583 end if;
2584 -- dbms_output.put_line('Value error occurred in download- check your object list.');
2585 p_return_status := FND_API.G_RET_STS_ERROR;
2586 WHEN FND_API.G_EXC_ERROR THEN
2587 p_return_status := FND_API.G_RET_STS_ERROR;
2588 WHEN OTHERS THEN
2589 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2590 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name,
2591 SUBSTR (SQLERRM, 1, 240) );
2592 end DOWNLOAD_OBJECT;
2593
2594 --=======================================================
2595 -- Procedure UPLOAD_OBJECT_SECOND
2596 --
2597 -- Usage Private API for loading objects that were
2598 -- failed during its first pass
2599 -- This API should only be called by other APIs
2600 -- that are owned by the Core Modules Team (AK).
2601 --
2602 -- Desc This API reads the object data from PL/SQL table
2603 -- that was prepared during 1st pass, then processes
2604 -- the data, and loads them to the database. The tables
2605 -- are updated with the timestamp passed. This API
2606 -- will process the file until the EOF is reached,
2607 -- a parse error is encountered, or when data for
2608 -- a different business object is read from the file.
2609 --
2610 -- Results The API returns the standard p_return_status parameter
2611 -- indicating one of the standard return statuses :
2612 -- * Unexpected error
2613 -- * Error
2614 -- * Success
2615 -- Parameters p_validation_level : IN required
2616 -- validation level
2617 --
2618 -- Version Initial version number = 1.0
2619 -- History Current version number = 1.0
2620 --=======================================================
2621 procedure UPLOAD_OBJECT_SECOND (
2622 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2623 p_return_status OUT NOCOPY VARCHAR2,
2624 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
2625 p_pass IN NUMBER := 2
2626 ) is
2627 l_api_name CONSTANT varchar2(30) := 'Upload_Object_Second';
2628 l_rec_index NUMBER;
2629 l_return_status VARCHAR2(1);
2630 l_msg_count NUMBER;
2631 l_msg_data VARCHAR2(240);
2632 l_copy_redo_flag BOOLEAN := FALSE;
2633 begin
2634 --
2635 -- Insert or update all objects to the database
2636 --
2637 if (G_OBJECT_REDO_INDEX > 0) then
2638 for l_index in G_OBJECT_REDO_TBL.FIRST .. G_OBJECT_REDO_TBL.LAST loop
2639 if (G_OBJECT_REDO_TBL.exists(l_index)) then
2640 if AK_OBJECT_PVT.OBJECT_EXISTS (
2641 p_api_version_number => 1.0,
2642 p_return_status => l_return_status,
2643 p_database_object_name =>
2644 G_OBJECT_REDO_TBL(l_index).database_object_name) then
2645 AK_OBJECT3_PVT.UPDATE_OBJECT (
2646 p_validation_level => p_validation_level,
2647 p_api_version_number => 1.0,
2648 p_msg_count => l_msg_count,
2649 p_msg_data => l_msg_data,
2650 p_return_status => l_return_status,
2651 p_database_object_name =>
2652 G_OBJECT_REDO_TBL(l_index).database_object_name,
2653 p_name => G_OBJECT_REDO_TBL(l_index).name,
2654 p_description => G_OBJECT_REDO_TBL(l_index).description,
2655 p_application_id => G_OBJECT_REDO_TBL(l_index).application_id,
2656 p_primary_key_name => G_OBJECT_REDO_TBL(l_index).primary_key_name,
2657 p_defaulting_api_pkg => G_OBJECT_REDO_TBL(l_index).defaulting_api_pkg,
2658 p_defaulting_api_proc => G_OBJECT_REDO_TBL(l_index).defaulting_api_proc,
2659 p_validation_api_pkg => G_OBJECT_REDO_TBL(l_index).validation_api_pkg,
2660 p_validation_api_proc => G_OBJECT_REDO_TBL(l_index).validation_api_proc,
2661 p_attribute_category => G_OBJECT_REDO_TBL(l_index).attribute_category,
2662 p_attribute1 => G_OBJECT_REDO_TBL(l_index).attribute1,
2663 p_attribute2 => G_OBJECT_REDO_TBL(l_index).attribute2,
2664 p_attribute3 => G_OBJECT_REDO_TBL(l_index).attribute3,
2665 p_attribute4 => G_OBJECT_REDO_TBL(l_index).attribute4,
2666 p_attribute5 => G_OBJECT_REDO_TBL(l_index).attribute5,
2667 p_attribute6 => G_OBJECT_REDO_TBL(l_index).attribute6,
2668 p_attribute7 => G_OBJECT_REDO_TBL(l_index).attribute7,
2669 p_attribute8 => G_OBJECT_REDO_TBL(l_index).attribute8,
2670 p_attribute9 => G_OBJECT_REDO_TBL(l_index).attribute9,
2671 p_attribute10 => G_OBJECT_REDO_TBL(l_index).attribute10,
2672 p_attribute11 => G_OBJECT_REDO_TBL(l_index).attribute11,
2673 p_attribute12 => G_OBJECT_REDO_TBL(l_index).attribute12,
2674 p_attribute13 => G_OBJECT_REDO_TBL(l_index).attribute13,
2675 p_attribute14 => G_OBJECT_REDO_TBL(l_index).attribute14,
2676 p_attribute15 => G_OBJECT_REDO_TBL(l_index).attribute15,
2677 p_created_by => G_OBJECT_REDO_TBL(l_index).created_by,
2678 p_creation_date => G_OBJECT_REDO_TBL(l_index).creation_date,
2679 p_last_updated_by => G_OBJECT_REDO_TBL(l_index).last_updated_by,
2680 p_last_update_date => G_OBJECT_REDO_TBL(l_index).last_update_date,
2681 p_last_update_login => G_OBJECT_REDO_TBL(l_index).last_update_login,
2682 p_loader_timestamp => p_loader_timestamp,
2683 p_pass => p_pass,
2684 p_copy_redo_flag => l_copy_redo_flag
2685 );
2686 else
2687 AK_OBJECT_PVT.CREATE_OBJECT (
2688 p_validation_level => p_validation_level,
2689 p_api_version_number => 1.0,
2690 p_msg_count => l_msg_count,
2691 p_msg_data => l_msg_data,
2692 p_return_status => l_return_status,
2693 p_database_object_name =>
2694 G_OBJECT_REDO_TBL(l_index).database_object_name,
2695 p_name => G_OBJECT_REDO_TBL(l_index).name,
2696 p_description => G_OBJECT_REDO_TBL(l_index).description,
2697 p_application_id => G_OBJECT_REDO_TBL(l_index).application_id,
2698 p_primary_key_name => G_OBJECT_REDO_TBL(l_index).primary_key_name,
2699 p_defaulting_api_pkg => G_OBJECT_REDO_TBL(l_index).defaulting_api_pkg,
2700 p_defaulting_api_proc => G_OBJECT_REDO_TBL(l_index).defaulting_api_proc,
2701 p_validation_api_pkg => G_OBJECT_REDO_TBL(l_index).validation_api_pkg,
2702 p_validation_api_proc => G_OBJECT_REDO_TBL(l_index).validation_api_proc,
2703 p_attribute_category => G_OBJECT_REDO_TBL(l_index).attribute_category,
2704 p_attribute1 => G_OBJECT_REDO_TBL(l_index).attribute1,
2705 p_attribute2 => G_OBJECT_REDO_TBL(l_index).attribute2,
2706 p_attribute3 => G_OBJECT_REDO_TBL(l_index).attribute3,
2707 p_attribute4 => G_OBJECT_REDO_TBL(l_index).attribute4,
2708 p_attribute5 => G_OBJECT_REDO_TBL(l_index).attribute5,
2709 p_attribute6 => G_OBJECT_REDO_TBL(l_index).attribute6,
2710 p_attribute7 => G_OBJECT_REDO_TBL(l_index).attribute7,
2711 p_attribute8 => G_OBJECT_REDO_TBL(l_index).attribute8,
2712 p_attribute9 => G_OBJECT_REDO_TBL(l_index).attribute9,
2713 p_attribute10 => G_OBJECT_REDO_TBL(l_index).attribute10,
2714 p_attribute11 => G_OBJECT_REDO_TBL(l_index).attribute11,
2715 p_attribute12 => G_OBJECT_REDO_TBL(l_index).attribute12,
2716 p_attribute13 => G_OBJECT_REDO_TBL(l_index).attribute13,
2717 p_attribute14 => G_OBJECT_REDO_TBL(l_index).attribute14,
2718 p_attribute15 => G_OBJECT_REDO_TBL(l_index).attribute15,
2719 p_created_by => G_OBJECT_REDO_TBL(l_index).created_by,
2720 p_creation_date => G_OBJECT_REDO_TBL(l_index).creation_date,
2721 p_last_updated_by => G_OBJECT_REDO_TBL(l_index).last_updated_by,
2722 p_last_update_date => G_OBJECT_REDO_TBL(l_index).last_update_date,
2723 p_last_update_login => G_OBJECT_REDO_TBL(l_index).last_update_login,
2724 p_loader_timestamp => p_loader_timestamp,
2725 p_pass => p_pass,
2726 p_copy_redo_flag => l_copy_redo_flag
2727 );
2728 end if; -- /* if OBJECT_EXISTS */
2729 --
2730 -- If API call returns with an error status, upload aborts
2731 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2732 (l_return_status = FND_API.G_RET_STS_ERROR) then
2733 -- dbms_output.put_line('Object key = '||G_OBJECT_REDO_TBL(l_index).database_object_name);
2734 RAISE FND_API.G_EXC_ERROR;
2735 end if; -- /* if l_return_status */
2736 end if;
2737 end loop; -- /* for loop */
2738 end if;
2739
2740 --
2741 -- Insert or update all object attributes to the database
2742 --
2743 if (G_OBJECT_ATTR_REDO_INDEX > 0) then
2744 for l_index in G_OBJECT_ATTR_REDO_TBL.FIRST .. G_OBJECT_ATTR_REDO_TBL.LAST loop
2745 if (G_OBJECT_ATTR_REDO_TBL.exists(l_index)) then
2746 if AK_OBJECT_PVT.ATTRIBUTE_EXISTS (
2747 p_api_version_number => 1.0,
2748 p_return_status => l_return_status,
2749 p_database_object_name =>
2750 G_OBJECT_ATTR_REDO_TBL(l_index).database_object_name,
2751 p_attribute_application_id =>
2752 G_OBJECT_ATTR_REDO_TBL(l_index).attribute_appl_id,
2753 p_attribute_code =>
2754 G_OBJECT_ATTR_REDO_TBL(l_index).attribute_code) then
2755 AK_OBJECT3_PVT.UPDATE_ATTRIBUTE (
2756 p_validation_level => p_validation_level,
2757 p_api_version_number => 1.0,
2758 p_msg_count => l_msg_count,
2759 p_msg_data => l_msg_data,
2760 p_return_status => l_return_status,
2761 p_database_object_name =>
2762 G_OBJECT_ATTR_REDO_TBL(l_index).database_object_name,
2763 p_attribute_application_id =>
2764 G_OBJECT_ATTR_REDO_TBL(l_index).attribute_appl_id,
2765 p_attribute_code => G_OBJECT_ATTR_REDO_TBL(l_index).attribute_code,
2766 p_column_name => G_OBJECT_ATTR_REDO_TBL(l_index).column_name,
2767 p_attribute_label_length =>
2768 G_OBJECT_ATTR_REDO_TBL(l_index).attribute_label_length,
2769 p_display_value_length =>
2770 G_OBJECT_ATTR_REDO_TBL(l_index).display_value_length,
2771 p_bold => G_OBJECT_ATTR_REDO_TBL(l_index).bold,
2772 p_italic => G_OBJECT_ATTR_REDO_TBL(l_index).italic,
2773 p_vertical_alignment =>
2774 G_OBJECT_ATTR_REDO_TBL(l_index).vertical_alignment,
2775 p_horizontal_alignment =>
2776 G_OBJECT_ATTR_REDO_TBL(l_index).horizontal_alignment,
2777 p_data_source_type => G_OBJECT_ATTR_REDO_TBL(l_index).data_source_type,
2778 p_data_storage_type => G_OBJECT_ATTR_REDO_TBL(l_index).data_storage_type,
2779 p_table_name => G_OBJECT_ATTR_REDO_TBL(l_index).table_name,
2780 p_base_table_column_name =>
2781 G_OBJECT_ATTR_REDO_TBL(l_index).base_table_column_name,
2782 p_required_flag => G_OBJECT_ATTR_REDO_TBL(l_index).required_flag,
2783 p_default_value_varchar2 =>
2784 G_OBJECT_ATTR_REDO_TBL(l_index).default_value_varchar2,
2785 p_default_value_number =>
2786 G_OBJECT_ATTR_REDO_TBL(l_index).default_value_number,
2787 p_default_value_date =>
2788 G_OBJECT_ATTR_REDO_TBL(l_index).default_value_date,
2789 p_lov_region_application_id =>
2790 G_OBJECT_ATTR_REDO_TBL(l_index).lov_region_application_id,
2791 p_lov_region_code => G_OBJECT_ATTR_REDO_TBL(l_index).lov_region_code,
2792 p_lov_foreign_key_name =>
2793 G_OBJECT_ATTR_REDO_TBL(l_index).lov_foreign_key_name,
2794 p_lov_attribute_application_id =>
2795 G_OBJECT_ATTR_REDO_TBL(l_index).lov_attribute_application_id,
2796 p_lov_attribute_code =>
2797 G_OBJECT_ATTR_REDO_TBL(l_index).lov_attribute_code,
2798 p_defaulting_api_pkg =>
2799 G_OBJECT_ATTR_REDO_TBL(l_index).defaulting_api_pkg,
2800 p_defaulting_api_proc =>
2801 G_OBJECT_ATTR_REDO_TBL(l_index).defaulting_api_proc,
2802 p_validation_api_pkg =>G_OBJECT_ATTR_REDO_TBL(l_index).validation_api_pkg,
2803 p_validation_api_proc =>
2804 G_OBJECT_ATTR_REDO_TBL(l_index).validation_api_proc,
2805 p_attribute_category => G_OBJECT_ATTR_REDO_TBL(l_index).attribute_category,
2806 p_attribute1 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute1,
2807 p_attribute2 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute2,
2808 p_attribute3 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute3,
2809 p_attribute4 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute4,
2810 p_attribute5 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute5,
2811 p_attribute6 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute6,
2812 p_attribute7 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute7,
2813 p_attribute8 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute8,
2814 p_attribute9 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute9,
2815 p_attribute10 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute10,
2816 p_attribute11 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute11,
2817 p_attribute12 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute12,
2818 p_attribute13 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute13,
2819 p_attribute14 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute14,
2820 p_attribute15 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute15,
2821 p_attribute_label_long =>
2822 G_OBJECT_ATTR_REDO_TBL(l_index).attribute_label_long,
2823 p_attribute_label_short =>
2824 G_OBJECT_ATTR_REDO_TBL(l_index).attribute_label_short,
2825 p_created_by => G_OBJECT_ATTR_REDO_TBL(l_index).created_by,
2826 p_creation_date => G_OBJECT_ATTR_REDO_TBL(l_index).creation_date,
2827 p_last_updated_by => G_OBJECT_ATTR_REDO_TBL(l_index).last_updated_by,
2828 p_last_update_date => G_OBJECT_ATTR_REDO_TBL(l_index).last_update_date,
2829 p_last_update_login => G_OBJECT_ATTR_REDO_TBL(l_index).last_update_login,
2830 p_loader_timestamp => p_loader_timestamp,
2831 p_pass => p_pass,
2832 p_copy_redo_flag => l_copy_redo_flag
2833 );
2834 else
2835 AK_OBJECT_PVT.CREATE_ATTRIBUTE (
2836 p_validation_level => p_validation_level,
2837 p_api_version_number => 1.0,
2838 p_msg_count => l_msg_count,
2839 p_msg_data => l_msg_data,
2840 p_return_status => l_return_status,
2841 p_database_object_name =>
2842 G_OBJECT_ATTR_REDO_TBL(l_index).database_object_name,
2843 p_attribute_application_id =>
2844 G_OBJECT_ATTR_REDO_TBL(l_index).attribute_appl_id,
2845 p_attribute_code => G_OBJECT_ATTR_REDO_TBL(l_index).attribute_code,
2846 p_column_name => G_OBJECT_ATTR_REDO_TBL(l_index).column_name,
2847 p_attribute_label_length =>
2848 G_OBJECT_ATTR_REDO_TBL(l_index).attribute_label_length,
2849 p_display_value_length =>
2850 G_OBJECT_ATTR_REDO_TBL(l_index).display_value_length,
2851 p_bold => G_OBJECT_ATTR_REDO_TBL(l_index).bold,
2852 p_italic => G_OBJECT_ATTR_REDO_TBL(l_index).italic,
2853 p_vertical_alignment =>
2854 G_OBJECT_ATTR_REDO_TBL(l_index).vertical_alignment,
2855 p_horizontal_alignment =>
2856 G_OBJECT_ATTR_REDO_TBL(l_index).horizontal_alignment,
2857 p_data_source_type => G_OBJECT_ATTR_REDO_TBL(l_index).data_source_type,
2858 p_data_storage_type => G_OBJECT_ATTR_REDO_TBL(l_index).data_storage_type,
2859 p_table_name => G_OBJECT_ATTR_REDO_TBL(l_index).table_name,
2860 p_base_table_column_name =>
2861 G_OBJECT_ATTR_REDO_TBL(l_index).base_table_column_name,
2862 p_required_flag => G_OBJECT_ATTR_REDO_TBL(l_index).required_flag,
2863 p_default_value_varchar2 =>
2864 G_OBJECT_ATTR_REDO_TBL(l_index).default_value_varchar2,
2865 p_default_value_number =>
2866 G_OBJECT_ATTR_REDO_TBL(l_index).default_value_number,
2867 p_default_value_date =>
2868 G_OBJECT_ATTR_REDO_TBL(l_index).default_value_date,
2869 p_lov_region_application_id =>
2870 G_OBJECT_ATTR_REDO_TBL(l_index).lov_region_application_id,
2871 p_lov_region_code => G_OBJECT_ATTR_REDO_TBL(l_index).lov_region_code,
2872 p_lov_foreign_key_name =>
2873 G_OBJECT_ATTR_REDO_TBL(l_index).lov_foreign_key_name,
2874 p_lov_attribute_application_id =>
2875 G_OBJECT_ATTR_REDO_TBL(l_index).lov_attribute_application_id,
2876 p_lov_attribute_code =>
2877 G_OBJECT_ATTR_REDO_TBL(l_index).lov_attribute_code,
2878 p_defaulting_api_pkg =>
2879 G_OBJECT_ATTR_REDO_TBL(l_index).defaulting_api_pkg,
2880 p_defaulting_api_proc =>
2881 G_OBJECT_ATTR_REDO_TBL(l_index).defaulting_api_proc,
2882 p_validation_api_pkg =>G_OBJECT_ATTR_REDO_TBL(l_index).validation_api_pkg,
2883 p_validation_api_proc =>
2884 G_OBJECT_ATTR_REDO_TBL(l_index).validation_api_proc,
2885 p_attribute_category => G_OBJECT_ATTR_REDO_TBL(l_index).attribute_category,
2886 p_attribute1 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute1,
2887 p_attribute2 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute2,
2888 p_attribute3 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute3,
2889 p_attribute4 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute4,
2890 p_attribute5 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute5,
2891 p_attribute6 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute6,
2892 p_attribute7 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute7,
2893 p_attribute8 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute8,
2894 p_attribute9 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute9,
2895 p_attribute10 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute10,
2896 p_attribute11 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute11,
2897 p_attribute12 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute12,
2898 p_attribute13 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute13,
2899 p_attribute14 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute14,
2900 p_attribute15 => G_OBJECT_ATTR_REDO_TBL(l_index).attribute15,
2901 p_attribute_label_long =>
2902 G_OBJECT_ATTR_REDO_TBL(l_index).attribute_label_long,
2903 p_attribute_label_short =>
2904 G_OBJECT_ATTR_REDO_TBL(l_index).attribute_label_short,
2905 p_loader_timestamp => p_loader_timestamp,
2906 p_pass => p_pass,
2907 p_copy_redo_flag => l_copy_redo_flag
2908 );
2909 end if; -- /* if ATTRIBUTE_EXISTS */
2910 --
2911 -- If API call returns with an error status, upload aborts
2912 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2913 (l_return_status = FND_API.G_RET_STS_ERROR) then
2914 RAISE FND_API.G_EXC_ERROR;
2915 end if; -- /* if l_return_status */
2916 end if;
2917 end loop;
2918 end if;
2919
2920 --
2921 -- Insert or update all object attribute navigation to the database
2922 --
2923 if (G_ATTR_NAV_REDO_INDEX > 0) then
2924 for l_index in G_ATTR_NAV_REDO_TBL.FIRST .. G_ATTR_NAV_REDO_TBL.LAST loop
2925 if (G_ATTR_NAV_REDO_TBL.exists(l_index)) then
2926 if AK_OBJECT_PVT.ATTRIBUTE_NAVIGATION_EXISTS (
2927 p_api_version_number => 1.0,
2928 p_return_status => l_return_status,
2929 p_database_object_name =>
2930 G_ATTR_NAV_REDO_TBL(l_index).database_object_name,
2931 p_attribute_application_id =>
2932 G_ATTR_NAV_REDO_TBL(l_index).attribute_appl_id,
2933 p_attribute_code => G_ATTR_NAV_REDO_TBL(l_index).attribute_code,
2934 p_value_varchar2 => G_ATTR_NAV_REDO_TBL(l_index).value_varchar2,
2935 p_value_date => G_ATTR_NAV_REDO_TBL(l_index).value_date,
2936 p_value_number => G_ATTR_NAV_REDO_TBL(l_index).value_number) then
2937 AK_OBJECT3_PVT.UPDATE_ATTRIBUTE_NAVIGATION (
2938 p_validation_level => p_validation_level,
2939 p_api_version_number => 1.0,
2940 p_msg_count => l_msg_count,
2941 p_msg_data => l_msg_data,
2942 p_return_status => l_return_status,
2943 p_database_object_name =>
2944 G_ATTR_NAV_REDO_TBL(l_index).database_object_name,
2945 p_attribute_application_id =>
2946 G_ATTR_NAV_REDO_TBL(l_index).attribute_appl_id,
2947 p_attribute_code => G_ATTR_NAV_REDO_TBL(l_index).attribute_code,
2948 p_value_varchar2 => G_ATTR_NAV_REDO_TBL(l_index).value_varchar2,
2949 p_value_date => G_ATTR_NAV_REDO_TBL(l_index).value_date,
2950 p_value_number => G_ATTR_NAV_REDO_TBL(l_index).value_number,
2951 p_to_region_appl_id => G_ATTR_NAV_REDO_TBL(l_index).to_region_appl_id,
2952 p_to_region_code => G_ATTR_NAV_REDO_TBL(l_index).to_region_code,
2953 p_attribute_category => G_ATTR_NAV_REDO_TBL(l_index).attribute_category,
2954 p_attribute1 => G_ATTR_NAV_REDO_TBL(l_index).attribute1,
2955 p_attribute2 => G_ATTR_NAV_REDO_TBL(l_index).attribute2,
2956 p_attribute3 => G_ATTR_NAV_REDO_TBL(l_index).attribute3,
2957 p_attribute4 => G_ATTR_NAV_REDO_TBL(l_index).attribute4,
2958 p_attribute5 => G_ATTR_NAV_REDO_TBL(l_index).attribute5,
2959 p_attribute6 => G_ATTR_NAV_REDO_TBL(l_index).attribute6,
2960 p_attribute7 => G_ATTR_NAV_REDO_TBL(l_index).attribute7,
2961 p_attribute8 => G_ATTR_NAV_REDO_TBL(l_index).attribute8,
2962 p_attribute9 => G_ATTR_NAV_REDO_TBL(l_index).attribute9,
2963 p_attribute10 => G_ATTR_NAV_REDO_TBL(l_index).attribute10,
2964 p_attribute11 => G_ATTR_NAV_REDO_TBL(l_index).attribute11,
2965 p_attribute12 => G_ATTR_NAV_REDO_TBL(l_index).attribute12,
2966 p_attribute13 => G_ATTR_NAV_REDO_TBL(l_index).attribute13,
2967 p_attribute14 => G_ATTR_NAV_REDO_TBL(l_index).attribute14,
2968 p_attribute15 => G_ATTR_NAV_REDO_TBL(l_index).attribute15,
2969 p_loader_timestamp => p_loader_timestamp,
2970 p_pass => p_pass,
2971 p_copy_redo_flag => l_copy_redo_flag
2972 );
2973 else
2974 AK_OBJECT_PVT.CREATE_ATTRIBUTE_NAVIGATION (
2975 p_validation_level => p_validation_level,
2976 p_api_version_number => 1.0,
2977 p_msg_count => l_msg_count,
2978 p_msg_data => l_msg_data,
2979 p_return_status => l_return_status,
2980 p_database_object_name =>
2981 G_ATTR_NAV_REDO_TBL(l_index).database_object_name,
2982 p_attribute_application_id =>
2983 G_ATTR_NAV_REDO_TBL(l_index).attribute_appl_id,
2984 p_attribute_code => G_ATTR_NAV_REDO_TBL(l_index).attribute_code,
2985 p_value_varchar2 => G_ATTR_NAV_REDO_TBL(l_index).value_varchar2,
2986 p_value_date => G_ATTR_NAV_REDO_TBL(l_index).value_date,
2987 p_value_number => G_ATTR_NAV_REDO_TBL(l_index).value_number,
2988 p_to_region_appl_id =>
2989 G_ATTR_NAV_REDO_TBL(l_index).to_region_appl_id,
2990 p_to_region_code => G_ATTR_NAV_REDO_TBL(l_index).to_region_code,
2991 p_attribute_category => G_ATTR_NAV_REDO_TBL(l_index).attribute_category,
2992 p_attribute1 => G_ATTR_NAV_REDO_TBL(l_index).attribute1,
2993 p_attribute2 => G_ATTR_NAV_REDO_TBL(l_index).attribute2,
2994 p_attribute3 => G_ATTR_NAV_REDO_TBL(l_index).attribute3,
2995 p_attribute4 => G_ATTR_NAV_REDO_TBL(l_index).attribute4,
2996 p_attribute5 => G_ATTR_NAV_REDO_TBL(l_index).attribute5,
2997 p_attribute6 => G_ATTR_NAV_REDO_TBL(l_index).attribute6,
2998 p_attribute7 => G_ATTR_NAV_REDO_TBL(l_index).attribute7,
2999 p_attribute8 => G_ATTR_NAV_REDO_TBL(l_index).attribute8,
3000 p_attribute9 => G_ATTR_NAV_REDO_TBL(l_index).attribute9,
3001 p_attribute10 => G_ATTR_NAV_REDO_TBL(l_index).attribute10,
3002 p_attribute11 => G_ATTR_NAV_REDO_TBL(l_index).attribute11,
3003 p_attribute12 => G_ATTR_NAV_REDO_TBL(l_index).attribute12,
3004 p_attribute13 => G_ATTR_NAV_REDO_TBL(l_index).attribute13,
3005 p_attribute14 => G_ATTR_NAV_REDO_TBL(l_index).attribute14,
3006 p_attribute15 => G_ATTR_NAV_REDO_TBL(l_index).attribute15,
3007 p_loader_timestamp => p_loader_timestamp,
3008 p_pass => p_pass,
3009 p_copy_redo_flag => l_copy_redo_flag
3010 );
3011 end if; -- /* if ATTRIBUTE_NAVIGATION_EXISTS */
3012 --
3013 -- If API call returns with an error status, upload aborts
3014 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3015 (l_return_status = FND_API.G_RET_STS_ERROR) then
3016 -- dbms_output.put_line('Attr Nav key: '||G_ATTR_NAV_REDO_TBL(l_index).database_object_name||
3017 -- ' '||G_ATTR_NAV_REDO_TBL(l_index).attribute_code||' '||
3018 -- to_char(G_ATTR_NAV_REDO_TBL(l_index).attribute_appl_id));
3019 RAISE FND_API.G_EXC_ERROR;
3020 end if; -- /* if l_return_status */
3021 end if;
3022 end loop;
3023 end if;
3024
3025 --
3026 -- Insert or update all unique keys to the database
3027 --
3028 if (G_UNIQUE_KEY_REDO_INDEX > 0) then
3029 for l_index in G_UNIQUE_KEY_REDO_TBL.FIRST .. G_UNIQUE_KEY_REDO_TBL.LAST loop
3030 if (G_UNIQUE_KEY_REDO_TBL.exists(l_index)) then
3031 if AK_KEY_PVT.UNIQUE_KEY_EXISTS (
3032 p_api_version_number => 1.0,
3033 p_return_status => l_return_status,
3034 p_unique_key_name =>
3035 G_UNIQUE_KEY_REDO_TBL(l_index).unique_key_name) then
3036 AK_KEY_PVT.UPDATE_UNIQUE_KEY (
3037 p_validation_level => p_validation_level,
3038 p_api_version_number => 1.0,
3039 p_msg_count => l_msg_count,
3040 p_msg_data => l_msg_data,
3041 p_return_status => l_return_status,
3042 p_unique_key_name =>
3043 G_UNIQUE_KEY_REDO_TBL(l_index).unique_key_name,
3044 p_database_object_name =>
3045 G_UNIQUE_KEY_REDO_TBL(l_index).database_object_name,
3046 p_application_id => G_UNIQUE_KEY_REDO_TBL(l_index).application_id,
3047 p_attribute_category => G_UNIQUE_KEY_REDO_TBL(l_index).attribute_category,
3048 p_attribute1 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute1,
3049 p_attribute2 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute2,
3050 p_attribute3 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute3,
3051 p_attribute4 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute4,
3052 p_attribute5 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute5,
3053 p_attribute6 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute6,
3054 p_attribute7 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute7,
3055 p_attribute8 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute8,
3056 p_attribute9 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute9,
3057 p_attribute10 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute10,
3058 p_attribute11 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute11,
3059 p_attribute12 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute12,
3060 p_attribute13 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute13,
3061 p_attribute14 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute14,
3062 p_attribute15 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute15,
3063 p_loader_timestamp => p_loader_timestamp,
3064 p_pass => p_pass,
3065 p_copy_redo_flag => l_copy_redo_flag
3066 );
3067 else
3068 AK_KEY_PVT.CREATE_UNIQUE_KEY (
3069 p_validation_level => p_validation_level,
3070 p_api_version_number => 1.0,
3071 p_msg_count => l_msg_count,
3072 p_msg_data => l_msg_data,
3073 p_return_status => l_return_status,
3074 p_unique_key_name =>
3075 G_UNIQUE_KEY_REDO_TBL(l_index).unique_key_name,
3076 p_database_object_name =>
3077 G_UNIQUE_KEY_REDO_TBL(l_index).database_object_name,
3078 p_application_id => G_UNIQUE_KEY_REDO_TBL(l_index).application_id,
3079 p_attribute_category => G_UNIQUE_KEY_REDO_TBL(l_index).attribute_category,
3080 p_attribute1 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute1,
3081 p_attribute2 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute2,
3082 p_attribute3 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute3,
3083 p_attribute4 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute4,
3084 p_attribute5 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute5,
3085 p_attribute6 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute6,
3086 p_attribute7 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute7,
3087 p_attribute8 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute8,
3088 p_attribute9 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute9,
3089 p_attribute10 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute10,
3090 p_attribute11 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute11,
3091 p_attribute12 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute12,
3092 p_attribute13 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute13,
3093 p_attribute14 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute14,
3094 p_attribute15 => G_UNIQUE_KEY_REDO_TBL(l_index).attribute15,
3095 p_loader_timestamp => p_loader_timestamp,
3096 p_pass => p_pass,
3097 p_copy_redo_flag => l_copy_redo_flag
3098 );
3099 end if; -- /* if UNIQUE_KEY_EXISTS */
3100 --
3101 -- If API call returns with an error status, upload aborts
3102 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3103 (l_return_status = FND_API.G_RET_STS_ERROR) then
3104 -- dbms_output.put_line('Unique_key: '||G_UNIQUE_KEY_REDO_TBL(l_index).unique_key_name||
3105 -- ' '||G_UNIQUE_KEY_REDO_TBL(l_index).database_object_name);
3106 RAISE FND_API.G_EXC_ERROR;
3107 end if; -- /* if l_return_status */
3108 end if;
3109 end loop;
3110 end if;
3111
3112 --
3113 -- Insert or update all unique key columns to the database
3114 --
3115 if (G_UNIQUE_KEY_COL_REDO_INDEX > 0) then
3116 for l_index in G_UNIQUE_KEY_COL_REDO_TBL.FIRST .. G_UNIQUE_KEY_COL_REDO_TBL.LAST loop
3117 if (G_UNIQUE_KEY_COL_REDO_TBL.exists(l_index)) then
3118 if AK_KEY_PVT.UNIQUE_KEY_COLUMN_EXISTS (
3119 p_api_version_number => 1.0,
3120 p_return_status => l_return_status,
3121 p_unique_key_name =>
3122 G_UNIQUE_KEY_COL_REDO_TBL(l_index).unique_key_name,
3123 p_attribute_application_id =>
3124 G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute_application_id,
3125 p_attribute_code =>
3126 G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute_code) then
3127 AK_KEY_PVT.UPDATE_UNIQUE_KEY_COLUMN (
3128 p_validation_level => p_validation_level,
3129 p_api_version_number => 1.0,
3130 p_msg_count => l_msg_count,
3131 p_msg_data => l_msg_data,
3132 p_return_status => l_return_status,
3133 p_unique_key_name =>
3134 G_UNIQUE_KEY_COL_REDO_TBL(l_index).unique_key_name,
3135 p_attribute_application_id =>
3136 G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute_application_id,
3137 p_attribute_code =>
3138 G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute_code,
3139 p_unique_key_sequence =>
3140 G_UNIQUE_KEY_COL_REDO_TBL(l_index).unique_key_sequence,
3141 p_attribute_category => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute_category,
3142 p_attribute1 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute1,
3143 p_attribute2 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute2,
3144 p_attribute3 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute3,
3145 p_attribute4 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute4,
3146 p_attribute5 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute5,
3147 p_attribute6 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute6,
3148 p_attribute7 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute7,
3149 p_attribute8 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute8,
3150 p_attribute9 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute9,
3151 p_attribute10 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute10,
3152 p_attribute11 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute11,
3153 p_attribute12 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute12,
3154 p_attribute13 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute13,
3155 p_attribute14 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute14,
3156 p_attribute15 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute15,
3157 p_loader_timestamp => p_loader_timestamp,
3158 p_pass => p_pass,
3159 p_copy_redo_flag => l_copy_redo_flag
3160 );
3161 else
3162 AK_KEY_PVT.CREATE_UNIQUE_KEY_COLUMN (
3163 p_validation_level => p_validation_level,
3164 p_api_version_number => 1.0,
3165 p_msg_count => l_msg_count,
3166 p_msg_data => l_msg_data,
3167 p_return_status => l_return_status,
3168 p_unique_key_name =>
3169 G_UNIQUE_KEY_COL_REDO_TBL(l_index).unique_key_name,
3170 p_attribute_application_id =>
3171 G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute_application_id,
3172 p_attribute_code =>
3173 G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute_code,
3174 p_unique_key_sequence =>
3175 G_UNIQUE_KEY_COL_REDO_TBL(l_index).unique_key_sequence,
3176 p_attribute_category => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute_category,
3177 p_attribute1 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute1,
3178 p_attribute2 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute2,
3179 p_attribute3 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute3,
3180 p_attribute4 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute4,
3181 p_attribute5 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute5,
3182 p_attribute6 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute6,
3183 p_attribute7 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute7,
3184 p_attribute8 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute8,
3185 p_attribute9 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute9,
3186 p_attribute10 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute10,
3187 p_attribute11 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute11,
3188 p_attribute12 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute12,
3189 p_attribute13 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute13,
3190 p_attribute14 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute14,
3191 p_attribute15 => G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute15,
3192 p_loader_timestamp => p_loader_timestamp,
3193 p_pass => p_pass,
3194 p_copy_redo_flag => l_copy_redo_flag
3195 );
3196 end if; -- /* if UNIQUE_KEY_COLUMN_EXISTS */
3197 --
3198 -- If API call returns with an error status, upload aborts
3199 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3200 (l_return_status = FND_API.G_RET_STS_ERROR) then
3201 -- dbms_output.put_line('Unique key col: '||G_UNIQUE_KEY_COL_REDO_TBL(l_index).unique_key_name||
3202 -- ' '||G_UNIQUE_KEY_COL_REDO_TBL(l_index).attribute_code);
3203 RAISE FND_API.G_EXC_ERROR;
3204 end if; -- /* if l_return_status */
3205 end if;
3206 end loop;
3207 end if;
3208
3209 --
3210 -- Insert or update all foreign keys to the database
3211 --
3212 if (G_FOREIGN_KEY_REDO_INDEX > 0) then
3213 for l_index in G_FOREIGN_KEY_REDO_TBL.FIRST .. G_FOREIGN_KEY_REDO_TBL.LAST loop
3214 if (G_FOREIGN_KEY_REDO_TBL.exists(l_index)) then
3215 if AK_KEY_PVT.FOREIGN_KEY_EXISTS (
3216 p_api_version_number => 1.0,
3217 p_return_status => l_return_status,
3218 p_foreign_key_name =>
3219 G_FOREIGN_KEY_REDO_TBL(l_index).foreign_key_name) then
3220 AK_KEY_PVT.UPDATE_FOREIGN_KEY (
3221 p_validation_level => p_validation_level,
3222 p_api_version_number => 1.0,
3223 p_msg_count => l_msg_count,
3224 p_msg_data => l_msg_data,
3225 p_return_status => l_return_status,
3226 p_foreign_key_name =>
3227 G_FOREIGN_KEY_REDO_TBL(l_index).foreign_key_name,
3228 p_database_object_name =>
3229 G_FOREIGN_KEY_REDO_TBL(l_index).database_object_name,
3230 p_unique_key_name =>
3231 G_FOREIGN_KEY_REDO_TBL(l_index).unique_key_name,
3232 p_application_id => G_FOREIGN_KEY_REDO_TBL(l_index).application_id,
3233 p_attribute_category => G_FOREIGN_KEY_REDO_TBL(l_index).attribute_category,
3234 p_attribute1 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute1,
3235 p_attribute2 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute2,
3236 p_attribute3 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute3,
3237 p_attribute4 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute4,
3238 p_attribute5 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute5,
3239 p_attribute6 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute6,
3240 p_attribute7 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute7,
3241 p_attribute8 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute8,
3242 p_attribute9 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute9,
3243 p_attribute10 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute10,
3244 p_attribute11 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute11,
3245 p_attribute12 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute12,
3246 p_attribute13 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute13,
3247 p_attribute14 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute14,
3248 p_attribute15 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute15,
3249 p_from_to_name => G_FOREIGN_KEY_REDO_TBL(l_index).from_to_name,
3250 p_from_to_description =>
3251 G_FOREIGN_KEY_REDO_TBL(l_index).from_to_description,
3252 p_to_from_name => G_FOREIGN_KEY_REDO_TBL(l_index).to_from_name,
3253 p_to_from_description =>
3254 G_FOREIGN_KEY_REDO_TBL(l_index).to_from_description,
3255 p_loader_timestamp => p_loader_timestamp,
3256 p_pass => p_pass,
3257 p_copy_redo_flag => l_copy_redo_flag
3258 );
3259 else
3260 AK_KEY_PVT.CREATE_FOREIGN_KEY (
3261 p_validation_level => p_validation_level,
3262 p_api_version_number => 1.0,
3263 p_msg_count => l_msg_count,
3264 p_msg_data => l_msg_data,
3265 p_return_status => l_return_status,
3266 p_foreign_key_name =>
3267 G_FOREIGN_KEY_REDO_TBL(l_index).foreign_key_name,
3268 p_database_object_name =>
3269 G_FOREIGN_KEY_REDO_TBL(l_index).database_object_name,
3270 p_unique_key_name =>
3271 G_FOREIGN_KEY_REDO_TBL(l_index).unique_key_name,
3272 p_application_id => G_FOREIGN_KEY_REDO_TBL(l_index).application_id,
3273 p_attribute_category => G_FOREIGN_KEY_REDO_TBL(l_index).attribute_category,
3274 p_attribute1 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute1,
3275 p_attribute2 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute2,
3276 p_attribute3 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute3,
3277 p_attribute4 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute4,
3278 p_attribute5 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute5,
3279 p_attribute6 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute6,
3280 p_attribute7 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute7,
3281 p_attribute8 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute8,
3282 p_attribute9 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute9,
3283 p_attribute10 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute10,
3284 p_attribute11 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute11,
3285 p_attribute12 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute12,
3286 p_attribute13 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute13,
3287 p_attribute14 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute14,
3288 p_attribute15 => G_FOREIGN_KEY_REDO_TBL(l_index).attribute15,
3289 p_from_to_name => G_FOREIGN_KEY_REDO_TBL(l_index).from_to_name,
3290 p_from_to_description =>
3291 G_FOREIGN_KEY_REDO_TBL(l_index).from_to_description,
3292 p_to_from_name => G_FOREIGN_KEY_REDO_TBL(l_index).to_from_name,
3293 p_to_from_description =>
3294 G_FOREIGN_KEY_REDO_TBL(l_index).to_from_description,
3295 p_loader_timestamp => p_loader_timestamp,
3296 p_pass => p_pass,
3297 p_copy_redo_flag => l_copy_redo_flag
3298 );
3299 end if; -- /* if FOREIGN_KEY_EXISTS */
3300 --
3301 -- If API call returns with an error status, upload aborts
3302 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3303 (l_return_status = FND_API.G_RET_STS_ERROR) then
3304 -- dbms_output.put_line('Foreign key: '||G_FOREIGN_KEY_REDO_TBL(l_index).foreign_key_name||
3305 -- ' '||G_FOREIGN_KEY_REDO_TBL(l_index).database_object_name);
3306 RAISE FND_API.G_EXC_ERROR;
3307 end if; -- /* if l_return_status */
3308 end if;
3309 end loop;
3310 end if;
3311
3312 --
3313 -- Insert or update all foreign key columns to the database
3314 --
3315 if (G_FOREIGN_KEY_COL_REDO_INDEX > 0) then
3316 for l_index in G_FOREIGN_KEY_COL_REDO_TBL.FIRST .. G_FOREIGN_KEY_COL_REDO_TBL.LAST loop
3317 if (G_FOREIGN_KEY_COL_REDO_TBL.exists(l_index)) then
3318 if AK_KEY_PVT.FOREIGN_KEY_COLUMN_EXISTS (
3319 p_api_version_number => 1.0,
3320 p_return_status => l_return_status,
3321 p_foreign_key_name =>
3322 G_FOREIGN_KEY_COL_REDO_TBL(l_index).foreign_key_name,
3323 p_attribute_application_id =>
3324 G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute_application_id,
3325 p_attribute_code =>
3326 G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute_code) then
3327 AK_KEY_PVT.UPDATE_FOREIGN_KEY_COLUMN (
3328 p_validation_level => p_validation_level,
3329 p_api_version_number => 1.0,
3330 p_msg_count => l_msg_count,
3331 p_msg_data => l_msg_data,
3332 p_return_status => l_return_status,
3333 p_foreign_key_name =>
3334 G_FOREIGN_KEY_COL_REDO_TBL(l_index).foreign_key_name,
3335 p_attribute_application_id =>
3336 G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute_application_id,
3337 p_attribute_code =>
3338 G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute_code,
3339 p_foreign_key_sequence =>
3340 G_FOREIGN_KEY_COL_REDO_TBL(l_index).foreign_key_sequence,
3341 p_attribute_category => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute_category,
3342 p_attribute1 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute1,
3343 p_attribute2 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute2,
3344 p_attribute3 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute3,
3345 p_attribute4 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute4,
3346 p_attribute5 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute5,
3347 p_attribute6 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute6,
3348 p_attribute7 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute7,
3349 p_attribute8 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute8,
3350 p_attribute9 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute9,
3351 p_attribute10 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute10,
3352 p_attribute11 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute11,
3353 p_attribute12 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute12,
3354 p_attribute13 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute13,
3355 p_attribute14 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute14,
3356 p_attribute15 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute15,
3357 p_loader_timestamp => p_loader_timestamp,
3358 p_pass => p_pass,
3359 p_copy_redo_flag => l_copy_redo_flag
3360 );
3361 else
3362 AK_KEY_PVT.CREATE_FOREIGN_KEY_COLUMN (
3363 p_validation_level => p_validation_level,
3364 p_api_version_number => 1.0,
3365 p_msg_count => l_msg_count,
3366 p_msg_data => l_msg_data,
3367 p_return_status => l_return_status,
3368 p_foreign_key_name =>
3369 G_FOREIGN_KEY_COL_REDO_TBL(l_index).foreign_key_name,
3370 p_attribute_application_id =>
3371 G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute_application_id,
3372 p_attribute_code =>
3373 G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute_code,
3374 p_foreign_key_sequence =>
3375 G_FOREIGN_KEY_COL_REDO_TBL(l_index).foreign_key_sequence,
3376 p_attribute_category => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute_category,
3377 p_attribute1 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute1,
3378 p_attribute2 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute2,
3379 p_attribute3 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute3,
3380 p_attribute4 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute4,
3381 p_attribute5 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute5,
3382 p_attribute6 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute6,
3383 p_attribute7 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute7,
3384 p_attribute8 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute8,
3385 p_attribute9 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute9,
3386 p_attribute10 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute10,
3387 p_attribute11 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute11,
3388 p_attribute12 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute12,
3389 p_attribute13 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute13,
3390 p_attribute14 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute14,
3391 p_attribute15 => G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute15,
3392 p_loader_timestamp => p_loader_timestamp,
3393 p_pass => p_pass,
3394 p_copy_redo_flag => l_copy_redo_flag
3395 );
3396 end if; -- /* if FOREIGN_KEY_COLUMN_EXISTS */
3397 --
3398 -- If API call returns with an error status, upload aborts
3399 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3400 (l_return_status = FND_API.G_RET_STS_ERROR) then
3401 -- dbms_output.put_line('Foreign Key col: '||G_FOREIGN_KEY_COL_REDO_TBL(l_index).foreign_key_name||
3402 -- ' '||G_FOREIGN_KEY_COL_REDO_TBL(l_index).attribute_code);
3403 RAISE FND_API.G_EXC_ERROR;
3404 end if; -- /* if l_return_status */
3405 end if;
3406 end loop;
3407 end if;
3408
3409 p_return_status := FND_API.G_RET_STS_SUCCESS;
3410
3411 EXCEPTION
3412 WHEN FND_API.G_EXC_ERROR THEN
3413 p_return_status := FND_API.G_RET_STS_ERROR;
3414 FND_MSG_PUB.Count_And_Get (
3415 p_count => l_msg_count,
3416 p_data => l_msg_data);
3417 WHEN OTHERS THEN
3418 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3419 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3420 SUBSTR (SQLERRM, 1, 240) );
3421 FND_MSG_PUB.Count_And_Get (
3422 p_count => l_msg_count,
3423 p_data => l_msg_data);
3424
3425 end UPLOAD_OBJECT_SECOND;
3426
3427 end AK_OBJECT2_PVT;