[Home] [Help]
PACKAGE BODY: APPS.AK_CUSTOM2_PVT
Source
1 package body AK_CUSTOM2_PVT as
2 /* $Header: akdvcr2b.pls 120.3.12020000.2 2012/11/20 19:03:29 tshort ship $ */
3
4 --=======================================================
5 -- Procedure UPLOAD_CUSTOM
6 --
7 -- Usage Private API for loading customizations from a
8 -- loader file to the database.
9 -- This API should only be called by other APIs
10 -- that are owned by the Core Modules Team (AK).
11 --
12 -- Desc This API reads the custom data (including region
13 -- items) stored in the loader file currently being
14 -- processed, parses the data, and loads them to the
15 -- database. The tables are updated with the timestamp
16 -- passed. This API will process the file until the
17 -- EOF is reached, a parse error is encountered, or when
18 -- data for a different business object is read from the file.
19 --
20 -- Results The API returns the standard p_return_status parameter
21 -- indicating one of the standard return statuses :
22 -- * Unexpected error
23 -- * Error
24 -- * Success
25 -- Parameters p_index : IN OUT required
26 -- Index of PL/SQL file to be processed.
27 -- p_loader_timestamp : IN required
28 -- The timestamp to be used when creating or updating
29 -- records
30 -- p_line_num : IN optional
31 -- The first line number in the file to be processed.
32 -- It is used for keeping track of the line number
33 -- read so that this info can be included in the
34 -- error message when a parse error occurred.
35 -- p_buffer : IN required
36 -- The content of the first line to be processed.
37 -- The calling API has already read the first line
38 -- that needs to be parsed by this API, so this
39 -- line won't be read from the file again.
40 -- p_line_num_out : OUT
41 -- The number of the last line in the loader file
42 -- that is read by this API.
43 -- p_buffer_out : OUT
44 -- The content of the last line read by this API.
45 -- If an EOF has not reached, this line would
46 -- contain the beginning of another business object
47 -- that will need to be processed by another API.
48 --
49 -- Version Initial version number = 1.0
50 -- History Current version number = 1.0
51 --=======================================================
52 procedure UPLOAD_CUSTOM (
53 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
54 p_api_version_number IN NUMBER,
55 p_return_status OUT NOCOPY VARCHAR2,
56 p_index IN OUT NOCOPY NUMBER,
57 p_loader_timestamp IN DATE,
58 p_line_num IN NUMBER := FND_API.G_MISS_NUM,
59 p_buffer IN AK_ON_OBJECTS_PUB.Buffer_Type,
60 p_line_num_out OUT NOCOPY NUMBER,
61 p_buffer_out OUT NOCOPY AK_ON_OBJECTS_PUB.Buffer_Type,
62 p_upl_loader_cur IN OUT NOCOPY AK_ON_OBJECTS_PUB.LoaderCurTyp,
63 p_pass IN NUMBER := 1
64 ) is
65 l_api_version_number CONSTANT number := 1.0;
66 l_api_name CONSTANT varchar2(30) := 'Upload_Custom';
67 l_buffer AK_ON_OBJECTS_PUB.Buffer_Type;
68 l_column varchar2(30);
69 l_dummy NUMBER;
70 l_eof_flag VARCHAR2(1);
71 l_custom_index NUMBER := 0;
72 l_index NUMBER;
73 l_line_num NUMBER;
74 l_lines_read NUMBER;
75 l_more_custom BOOLEAN := TRUE;
76 l_msg_count NUMBER;
77 l_msg_data VARCHAR2(2000);
78 l_return_status varchar2(1);
79 l_saved_token AK_ON_OBJECTS_PUB.Buffer_Type;
80 l_state NUMBER;
81 l_token AK_ON_OBJECTS_PUB.Buffer_Type;
82 l_value_count NUMBER;
83 l_copy_redo_flag BOOLEAN := FALSE;
84 l_user_id1 NUMBER;
85 l_user_id2 NUMBER;
86 l_update1 DATE;
87 l_update2 DATE;
88 l_custom_rec AK_CUSTOM_PUB.Custom_Rec_Type;
89 l_custom_tbl AK_CUSTOM_PUB.Custom_Tbl_Type;
90 l_cust_region_index NUMBER := 0;
91 l_cust_region_rec AK_CUSTOM_PUB.Cust_Region_Rec_Type;
92 l_cust_region_tbl AK_CUSTOM_PUB.Cust_Region_Tbl_Type;
93 l_cust_reg_item_index NUMBER := 0;
94 l_cust_reg_item_rec AK_CUSTOM_PUB.Cust_Reg_Item_Rec_Type;
95 l_cust_reg_item_tbl AK_CUSTOM_PUB.Cust_Reg_Item_Tbl_Type;
96 l_criteria_index NUMBER := 0;
97 l_criteria_rec AK_CRITERIA%ROWTYPE;
98 l_empty_criteria_rec AK_CRITERIA%ROWTYPE;
99 l_criteria_tbl AK_CUSTOM_PUB.Criteria_Tbl_Type;
100 l_error_key_info VARCHAR2(500); -- for debug purpose: see EXCEPTION stack
101
102 begin
103 --dbms_output.put_line('Started region upload: ' ||
104 -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
105
106 IF NOT FND_API.Compatible_API_Call (
107 l_api_version_number, p_api_version_number, l_api_name,
108 G_PKG_NAME) then
109 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
110 return;
111 END IF;
112
113 SAVEPOINT Start_Upload;
114
115 -- Retrieve the first non-blank, non-comment line
116 l_state := 0;
117 l_eof_flag := 'N';
118 --
119 -- if calling from ak_on_objects.upload (ie, loader timestamp is given),
120 -- the tokens 'BEGIN CUSTOM' has already been parsed. Set initial
121 -- buffer to 'BEGIN CUSTOM' before reading the next line from the
122 -- file. Otherwise, set initial buffer to null.
123 --
124 if (p_loader_timestamp <> FND_API.G_MISS_DATE) then
125 l_buffer := 'BEGIN CUSTOMIZATION ' || p_buffer;
126 else
127 l_buffer := null;
128 end if;
129
130 if (p_line_num = FND_API.G_MISS_NUM) then
131 l_line_num := 0;
132 else
133 l_line_num := p_line_num;
134 end if;
135
136 while (l_buffer is null and l_eof_flag = 'N' and p_index <= AK_ON_OBJECTS_PVT.G_UPL_TABLE_NUM) loop
137 AK_ON_OBJECTS_PVT.READ_LINE (
138 p_return_status => l_return_status,
139 p_index => p_index,
140 p_buffer => l_buffer,
141 p_lines_read => l_lines_read,
142 p_eof_flag => l_eof_flag,
143 p_upl_loader_cur => p_upl_loader_cur
144 );
145 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
146 (l_return_status = FND_API.G_RET_STS_ERROR) then
147 RAISE FND_API.G_EXC_ERROR;
148 end if;
149 l_line_num := l_line_num + l_lines_read;
150 --
151 -- trim leading spaces and discard comment lines
152 --
153 l_buffer := LTRIM(l_buffer);
154 if (SUBSTR(l_buffer, 1, 1) = '#') then
155 l_buffer := null;
156 end if;
157 end loop;
158
159 --
160 -- Error if there is nothing to be read from the file
161 --
162 if (l_buffer is null and l_eof_flag = 'Y') then
163 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
164 FND_MESSAGE.SET_NAME('AK','AK_EMPTY_BUFFER');
165 FND_MSG_PUB.Add;
166 end if;
167 raise FND_API.G_EXC_ERROR;
168 end if;
169
170 -- Read tokens from file, one at a time
171
172 while (l_eof_flag = 'N') and (l_buffer is not null)
173 and (l_more_custom) loop
174
175 AK_ON_OBJECTS_PVT.GET_TOKEN(
176 p_return_status => l_return_status,
177 p_in_buf => l_buffer,
178 p_token => l_token
179 );
180
181 --dbms_output.put_line(' State:' || l_state || 'Token:' || l_token);
182
183 if (l_return_status = FND_API.G_RET_STS_ERROR) or
184 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
185 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
186 FND_MESSAGE.SET_NAME('AK','AK_GET_TOKEN_ERROR');
187 FND_MSG_PUB.Add;
188 end if;
189 --dbms_output.put_line(l_api_name || ' Error parsing buffer');
190 raise FND_API.G_EXC_ERROR;
191 end if;
192
193
194 --
195 -- CUSTOM (tates 0 - 19)
196 --
197 if (l_state = 0) then
198 if (l_token = 'BEGIN') then
199 l_state := 1;
200 else
201 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
202 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
203 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
204 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
205 FND_MESSAGE.SET_TOKEN('EXPECTED','BEGIN');
206 FND_MSG_PUB.Add;
207 end if;
208 raise FND_API.G_EXC_ERROR;
209 end if;
210 elsif (l_state = 1) then
211 if (l_token = 'CUSTOMIZATION') then
212 --== Clear out previous column data ==--
213 l_custom_rec := AK_CUSTOM_PUB.G_MISS_CUSTOM_REC;
214 l_state := 2;
215 else
216 -- Found the beginning of a non-custom object,
217 -- rebuild last line and pass it back to the caller
218 -- (ak_on_objects_pvt.upload).
219 p_buffer_out := 'BEGIN ' || l_token || ' ' || l_buffer;
220 l_more_custom := FALSE;
221 end if;
222 elsif (l_state = 2) then
223 if (l_token is not null) then
224 l_custom_rec.customization_appl_id := to_number(l_token);
225 l_state := 3;
226 else
227 --dbms_output.put_line('Expecting custom region application ID');
228 raise FND_API.G_EXC_ERROR;
229 end if;
230 elsif (l_state = 3) then
231 if (l_token is not null) then
232 l_custom_rec.customization_code := l_token;
233 l_state := 4;
234 else
235 --dbms_output.put_line('Expecting customization code');
236 raise FND_API.G_EXC_ERROR;
237 end if;
238 elsif (l_state = 4) then
239 if (l_token is not null) then
240 l_custom_rec.region_appl_id := l_token;
241 l_state := 5;
242 else
243 --dbms_output.put_line('Expecting region application ID');
244 raise FND_API.G_EXC_ERROR;
245 end if;
246 elsif (l_state = 5) then
247 if (l_token is not null) then
248 l_custom_rec.region_code := l_token;
249 l_value_count := null;
250 l_state := 10;
251 else
252 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
253 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
254 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
255 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
256 FND_MESSAGE.SET_TOKEN('EXPECTED','REGION_CODE');
257 FND_MSG_PUB.Add;
258 end if;
259 raise FND_API.G_EXC_ERROR;
260 end if;
261 elsif (l_state = 10) then
262 if (l_token = 'BEGIN') then
263 l_state := 13;
264 elsif (l_token = 'END') then
265 l_state := 19;
266 elsif (l_token = 'VERTICALIZATION_ID') or
267 (l_token = 'LOCALIZATION_CODE') or
268 (l_token = 'ORG_ID') or
269 (l_token = 'SITE_ID') or
270 (l_token = 'RESPONSIBILITY_ID') or
271 (l_token = 'WEB_USER_ID') or
272 (l_token = 'CUSTOMIZATION_FLAG') or
273 (l_token = 'CUSTOMIZATION_LEVEL_ID') or
274 (l_token = 'DEVELOPER_MODE') or
275 (l_token = 'REFERENCE_PATH') or
276 (l_token = 'FUNCTION_NAME') or
277 (l_token = 'START_DATE_ACTIVE') or
278 (l_token = 'END_DATE_ACTIVE') or
279 (l_token = 'NAME') or
280 (l_token = 'DESCRIPTION') or
281 (l_token = 'CREATED_BY') or
282 (l_token = 'CREATION_DATE') or
283 (l_token = 'LAST_UPDATED_BY') or
284 (l_token = 'OWNER') or
285 (l_token = 'LAST_UPDATE_DATE') or
286 (l_token = 'LAST_UPDATE_LOGIN') then
287 l_column := l_token;
288 l_state := 11;
289 else
290 --
291 -- error if not expecting attribute values added by the translation team
292 -- or if we have read in more than a certain number of values
293 -- for the same DB column
294 --
295 l_value_count := l_value_count + 1;
296 --
297 -- save second value. It will be the token with error if
298 -- it turns out that there is a parse error on this line.
299 --
300 if (l_value_count = 2) then
301 l_saved_token := l_token;
302 end if;
303 if (l_value_count > AK_ON_OBJECTS_PUB.G_MAX_NUM_LOADER_VALUES) or
304 (l_value_count is null) then
305 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
306 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_EFIELD');
307 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
308 if (l_value_count is null) then
309 FND_MESSAGE.SET_TOKEN('TOKEN', l_token);
310 else
311 FND_MESSAGE.SET_TOKEN('TOKEN',l_saved_token);
312 end if;
313 FND_MESSAGE.SET_TOKEN('EXPECTED','CUSTOM');
314 FND_MSG_PUB.Add;
315 end if;
316 -- dbms_output.put_line('Expecting region field, BEGIN, or END');
317 raise FND_API.G_EXC_ERROR;
318 end if;
319 end if;
320 elsif (l_state = 11) then
324 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
321 if (l_token = '=') then
322 l_state := 12;
323 else
325 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
326 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
327 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
328 FND_MESSAGE.SET_TOKEN('EXPECTED','=');
329 FND_MSG_PUB.Add;
330 end if;
331 raise FND_API.G_EXC_ERROR;
332 end if;
333 elsif (l_state = 12) then
334 l_value_count := 1;
335 if (l_column = 'VERTICALIZATION_ID') then
336 l_custom_rec.verticalization_id := l_token;
337 elsif (l_column = 'LOCALIZATION_CODE') then
338 l_custom_rec.localization_code := l_token;
339 elsif (l_column = 'ORG_ID') then
340 l_custom_rec.org_id := l_token;
341 elsif (l_column = 'SITE_ID') then
342 l_custom_rec.site_id := l_token;
343 elsif (l_column = 'RESPONSIBILITY_ID') then
344 l_custom_rec.responsibility_id := l_token;
345 elsif (l_column = 'WEB_USER_ID') then
346 l_custom_rec.web_user_id := l_token;
347 elsif (l_column = 'CUSTOMIZATION_FLAG') then
348 l_custom_rec.default_customization_flag := l_token;
349 elsif (l_column = 'CUSTOMIZATION_LEVEL_ID') then
350 l_custom_rec.customization_level_id := l_token;
351 elsif (l_column = 'DEVELOPER_MODE') then
352 l_custom_rec.developer_mode := l_token;
353 elsif (l_column = 'REFERENCE_PATH') then
354 l_custom_rec.reference_path := l_token;
355 elsif (l_column = 'FUNCTION_NAME') then
356 l_custom_rec.function_name := l_token;
357 elsif (l_column = 'START_DATE_ACTIVE') then
358 l_custom_rec.start_date_active := to_date(l_token,
359 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
360 elsif (l_column = 'END_DATE_ACTIVE') then
361 l_custom_rec.end_date_active := to_date(l_token,
362 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
363 elsif (l_column = 'NAME') then
364 l_custom_rec.name := l_token;
365 elsif (l_column = 'DESCRIPTION') then
366 l_custom_rec.description := l_token;
367 elsif (l_column = 'CREATED_BY') then
368 l_custom_rec.created_by := to_number(l_token);
369 elsif (l_column = 'CREATION_DATE') then
370 l_custom_rec.creation_date := to_date(l_token,
371 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
372 elsif (l_column = 'LAST_UPDATED_BY') then
373 l_custom_rec.last_updated_by := to_number(l_token);
374 elsif (l_column = 'OWNER') then
375 l_custom_rec.last_updated_by := FND_LOAD_UTIL.OWNER_ID(l_token);
376 elsif (l_column = 'LAST_UPDATE_DATE') then
377 l_custom_rec.last_update_date := to_date(l_token,
378 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
379 elsif (l_column = 'LAST_UPDATE_LOGIN') then
380 l_custom_rec.last_update_login := to_number(l_token);
381 else
382 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
383 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
384 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
385 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
386 FND_MESSAGE.SET_TOKEN('EXPECTED', l_column);
387 FND_MSG_PUB.Add;
388 end if;
389 raise FND_API.G_EXC_ERROR;
390 end if;
391 l_state := 10;
392 elsif (l_state = 13) then
393 if (l_token = 'CUSTOM_REGION') then
394 --== Clear out previous region item column data ==--
395 -- and load region key values into record --
396 l_cust_region_rec := AK_CUSTOM_PUB.G_MISS_CUST_REGION_REC;
397 l_cust_region_rec.customization_appl_id := l_custom_rec.customization_appl_id;
398 l_cust_region_rec.customization_code := l_custom_rec.customization_code;
399 l_cust_region_rec.region_appl_id := l_custom_rec.region_appl_id;
400 l_cust_region_rec.region_code := l_custom_rec.region_code;
401 l_state := 20;
402 elsif ( l_token = 'CUSTOM_REGION_ITEM') then
403 -- clear out previous custom region item column data --
404 -- and load customization key values into record --
405 l_cust_reg_item_rec := AK_CUSTOM_PUB.G_MISS_CUST_REG_ITEM_REC;
406 l_cust_reg_item_rec.customization_appl_id := l_custom_rec.customization_appl_id;
407 l_cust_reg_item_rec.customization_code := l_custom_rec.customization_code;
408 l_cust_reg_item_rec.region_appl_id := l_custom_rec.region_appl_id;
409 l_cust_reg_item_rec.region_code := l_custom_rec.region_code;
410 l_state := 100;
411 elsif ( l_token = 'CRITERIA') then
412 -- clear out previous custom criteria column data --
413 -- and load customization key values into record --
414 l_criteria_rec := l_empty_criteria_rec;
415 l_criteria_rec.customization_application_id := l_custom_rec.customization_appl_id;
416 l_criteria_rec.customization_code := l_custom_rec.customization_code;
417 l_criteria_rec.region_application_id := l_custom_rec.region_appl_id;
418 l_criteria_rec.region_code := l_custom_rec.region_code;
419 l_state := 200;
420 else
421 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
422 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
423 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
424 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
425 FND_MESSAGE.SET_TOKEN('EXPECTED', 'CUSTOM_REGION, CUSTOM_REGION_ITEM, CRITERIA');
426 FND_MSG_PUB.Add;
427 end if;
428 raise FND_API.G_EXC_ERROR;
429 end if;
430 elsif (l_state = 19) then
431 if (l_token = 'CUSTOMIZATION') then
432 l_state := 0;
433 l_custom_index := l_custom_index + 1;
434 l_custom_tbl(l_custom_index) := l_custom_rec;
435 else
439 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
436 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
437 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
438 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
440 FND_MESSAGE.SET_TOKEN('EXPECTED', 'CUSTOMIZATION');
441 FND_MSG_PUB.Add;
442 end if;
443 raise FND_API.G_EXC_ERROR;
444 end if;
445
446 --
447 -- CUSTOM_REGION (States 20 - 39)
448 --
449 elsif (l_state = 20) then
450 if (l_token is not null) then
451 l_cust_region_rec.property_name := l_token;
452 l_value_count := null;
453 l_state := 30;
454 else
455 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
456 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
457 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
458 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
459 FND_MESSAGE.SET_TOKEN('EXPECTED', 'PROPERTY_NAME');
460 FND_MSG_PUB.Add;
461 end if;
462 raise FND_API.G_EXC_ERROR;
463 end if;
464 elsif (l_state = 30) then
465 if (l_token = 'END') then
466 l_state := 39;
467 elsif (l_token = 'PROPERTY_VARCHAR2_VALUE') or
468 (l_token = 'PROPERTY_NUMBER_VALUE') or
469 (l_token = 'CRITERIA_JOIN_CONDITION') or
470 (l_token = 'PROPERTY_VARCHAR2_VALUE_TL') or
471 (l_token = 'CREATED_BY') or
472 (l_token = 'CREATION_DATE') or
473 (l_token = 'LAST_UPDATED_BY') or
474 (l_token = 'OWNER') or
475 (l_token = 'LAST_UPDATE_DATE') or
476 (l_token = 'LAST_UPDATE_LOGIN') then
477 l_column := l_token;
478 l_state := 31;
479 else
480 --
481 -- error if not expecting attribute values added by the translation team
482 -- or if we have read in more than a certain number of values
483 -- for the same DB column
484 --
485 l_value_count := l_value_count + 1;
486 --
487 -- save second value. It will be the token with error if
488 -- it turns out that there is a parse error on this line.
489 --
490 if (l_value_count = 2) then
491 l_saved_token := l_token;
492 end if;
493 if (l_value_count > AK_ON_OBJECTS_PUB.G_MAX_NUM_LOADER_VALUES) or
494 (l_value_count is null) then
495 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
496 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_EFIELD');
497 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
498 if (l_value_count is null) then
499 FND_MESSAGE.SET_TOKEN('TOKEN', l_token);
500 else
501 FND_MESSAGE.SET_TOKEN('TOKEN',l_saved_token);
502 end if;
503 FND_MESSAGE.SET_TOKEN('EXPECTED','CUSTOM_REGION');
504 FND_MSG_PUB.Add;
505 end if;
506 raise FND_API.G_EXC_ERROR;
507 end if;
508 end if;
509 elsif (l_state = 31) then
510 if (l_token = '=') then
511 l_state := 32;
512 else
513 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
514 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
515 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
516 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
517 FND_MESSAGE.SET_TOKEN('EXPECTED', '=');
518 FND_MSG_PUB.Add;
519 end if;
520 raise FND_API.G_EXC_ERROR;
521 end if;
522 elsif (l_state = 32) then
523 l_value_count := 1;
524 if (l_column = 'PROPERTY_VARCHAR2_VALUE') then
525 l_cust_region_rec.PROPERTY_VARCHAR2_VALUE := l_token;
526 l_state := 30;
527 elsif (l_column = 'PROPERTY_NUMBER_VALUE') then
528 l_cust_region_rec.PROPERTY_NUMBER_VALUE := to_number(l_token);
529 l_state := 30;
530 elsif (l_column = 'CRITERIA_JOIN_CONDITION') then
531 l_cust_region_rec.CRITERIA_JOIN_CONDITION := l_token;
532 l_state := 30;
533 elsif (l_column = 'PROPERTY_VARCHAR2_VALUE_TL') then
534 l_cust_region_rec.PROPERTY_VARCHAR2_VALUE_TL := l_token;
535 l_state := 30;
536 elsif (l_column = 'CREATED_BY') then
537 l_cust_region_rec.created_by := to_number(l_token);
538 l_state := 30;
539 elsif (l_column = 'CREATION_DATE') then
540 l_cust_region_rec.creation_date := to_date(l_token,
541 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
542 l_state := 30;
543 elsif (l_column = 'LAST_UPDATED_BY') then
544 l_cust_region_rec.last_updated_by := to_number(l_token);
545 l_state := 30;
546 elsif (l_column = 'OWNER') then
547 l_cust_region_rec.last_updated_by := FND_LOAD_UTIL.OWNER_ID(l_token);
548 l_state := 30;
549 elsif (l_column = 'LAST_UPDATE_DATE') then
550 l_cust_region_rec.last_update_date := to_date(l_token,
551 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
552 l_state := 30;
553 elsif (l_column = 'LAST_UPDATE_LOGIN') then
554 l_cust_region_rec.last_update_login := to_number(l_token);
555 l_state := 30;
556 else
557 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
558 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
559 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
560 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
561 FND_MESSAGE.SET_TOKEN('EXPECTED', l_column || ' value');
562 FND_MSG_PUB.Add;
563 end if;
564 raise FND_API.G_EXC_ERROR;
565 end if;
566 elsif (l_state = 39) then
567 if (l_token = 'CUSTOM_REGION') then
568 l_value_count := null;
572 else
569 l_state := 10;
570 l_cust_region_index := l_cust_region_index + 1;
571 l_cust_region_tbl(l_cust_region_index) := l_cust_region_rec;
573 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
574 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
575 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
576 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
577 FND_MESSAGE.SET_TOKEN('EXPECTED', 'CUSTOM_REGION');
578 FND_MSG_PUB.Add;
579 end if;
580 raise FND_API.G_EXC_ERROR;
581 end if;
582
583 --
584 -- CUSTOM_REGION_ITEM (states 100 - 139)
585 --
586 elsif (l_state = 100) then
587 if (l_token is not null) then
588 l_cust_reg_item_rec.attr_appl_id := to_number(l_token);
589 l_state := 101;
590 else
591 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
592 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
593 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
594 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
595 FND_MESSAGE.SET_TOKEN('EXPECTED', 'ATTRIBUTE_APPL_ID');
596 FND_MSG_PUB.Add;
597 end if;
598 raise FND_API.G_EXC_ERROR;
599 end if;
600 elsif (l_state = 101) then
601 if (l_token is not null) then
602 l_cust_reg_item_rec.attr_code := l_token;
603 l_state := 102;
604 else
605 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
606 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
607 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
608 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
609 FND_MESSAGE.SET_TOKEN('EXPECTED', 'ATTRIBUTE_CODE');
610 FND_MSG_PUB.Add;
611 end if;
612 raise FND_API.G_EXC_ERROR;
613 end if;
614 elsif (l_state = 102) then
615 if (l_token is not null) then
616 l_cust_reg_item_rec.property_name := l_token;
617 l_value_count := null;
618 l_state := 130;
619 else
620 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
621 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
622 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
623 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
624 FND_MESSAGE.SET_TOKEN('EXPECTED', 'PROPERTY_NAME');
625 FND_MSG_PUB.Add;
626 end if;
627 raise FND_API.G_EXC_ERROR;
628 end if;
629 elsif (l_state = 130) then
630 if (l_token = 'END') then
631 l_state := 139;
632 elsif ( l_token = 'PROPERTY_VARCHAR2_VALUE') or
633 ( l_token = 'PROPERTY_NUMBER_VALUE') or
634 ( l_token = 'PROPERTY_DATE_VALUE') or
635 ( l_token = 'PROPERTY_VARCHAR2_VALUE_TL') or
636 (l_token = 'CREATED_BY') or
637 (l_token = 'CREATION_DATE') or
638 (l_token = 'LAST_UPDATED_BY') or
639 (l_token = 'OWNER') or
640 (l_token = 'LAST_UPDATE_DATE') or
641 (l_token = 'LAST_UPDATE_LOGIN') then
642 l_column := l_token;
643 l_state := 131;
644 else
645 --
646 -- error if not expecting attribute values added by the translation team
647 -- or if we have read in more than a certain number of values
648 -- for the same DB column
649 --
650 l_value_count := l_value_count + 1;
651 --
652 -- save second value. It will be the token with error if
653 -- it turns out that there is a parse error on this line.
654 --
655 if (l_value_count = 2) then
656 l_saved_token := l_token;
657 end if;
658 if (l_value_count > AK_ON_OBJECTS_PUB.G_MAX_NUM_LOADER_VALUES) or
659 (l_value_count is null) then
660 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
661 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_EFIELD');
662 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
663 if (l_value_count is null) then
664 FND_MESSAGE.SET_TOKEN('TOKEN', l_token);
665 else
666 FND_MESSAGE.SET_TOKEN('TOKEN',l_saved_token);
667 end if;
668 FND_MESSAGE.SET_TOKEN('EXPECTED','CUSTOM_REGION_ITEM');
669 FND_MSG_PUB.Add;
670 end if;
671 raise FND_API.G_EXC_ERROR;
672 end if;
673 end if;
674 elsif (l_state = 131) then
675 if (l_token = '=') then
676 l_state := 132;
677 else
678 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
679 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
680 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
681 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
682 FND_MESSAGE.SET_TOKEN('EXPECTED', '=');
683 FND_MSG_PUB.Add;
684 end if;
685 raise FND_API.G_EXC_ERROR;
686 end if;
687 elsif (l_state = 132) then
688 l_value_count := 1;
689 if (l_column = 'PROPERTY_VARCHAR2_VALUE') then
690 l_cust_reg_item_rec.PROPERTY_VARCHAR2_VALUE := l_token;
691 l_state := 130;
692 elsif (l_column = 'PROPERTY_NUMBER_VALUE') then
693 l_cust_reg_item_rec.PROPERTY_NUMBER_VALUE := to_number(l_token);
694 l_state := 130;
695 elsif (l_column = 'PROPERTY_DATE_VALUE') then
696 l_cust_reg_item_rec.PROPERTY_DATE_VALUE := to_date(l_token,
697 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
698 l_state := 130;
699 elsif (l_column = 'PROPERTY_VARCHAR2_VALUE_TL') then
700 l_cust_reg_item_rec.PROPERTY_VARCHAR2_VALUE_TL := l_token;
704 l_state := 130;
701 l_state := 130;
702 elsif (l_column = 'CREATED_BY') then
703 l_cust_reg_item_rec.created_by := to_number(l_token);
705 elsif (l_column = 'CREATION_DATE') then
706 l_cust_reg_item_rec.creation_date := to_date(l_token,
707 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
708 l_state := 130;
709 elsif (l_column = 'LAST_UPDATED_BY') then
710 l_cust_reg_item_rec.last_updated_by := to_number(l_token);
711 l_state := 130;
712 elsif (l_column = 'OWNER') then
713 l_cust_reg_item_rec.last_updated_by := FND_LOAD_UTIL.OWNER_ID(l_token);
714 l_state := 130;
715 elsif (l_column = 'LAST_UPDATE_DATE') then
716 l_cust_reg_item_rec.last_update_date := to_date(l_token,
717 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
718 l_state := 130;
719 elsif (l_column = 'LAST_UPDATE_LOGIN') then
720 l_cust_reg_item_rec.last_update_login := to_number(l_token);
721 l_state := 130;
722 else
723 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
724 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
725 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
726 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
727 FND_MESSAGE.SET_TOKEN('EXPECTED', l_column || ' value');
728 FND_MSG_PUB.Add;
729 end if;
730 raise FND_API.G_EXC_ERROR;
731 end if;
732 elsif (l_state = 139) then
733 if (l_token = 'CUSTOM_REGION_ITEM') then
734 l_value_count := null;
735 l_state := 10;
736 l_cust_reg_item_index := l_cust_reg_item_index + 1;
737 l_cust_reg_item_tbl(l_cust_reg_item_index) := l_cust_reg_item_rec;
738 else
739 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
740 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
741 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
742 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
743 FND_MESSAGE.SET_TOKEN('EXPECTED', 'CUSTOM_REGION_ITEM');
744 FND_MSG_PUB.Add;
745 end if;
746 raise FND_API.G_EXC_ERROR;
747 end if;
748
749 --
750 -- CRITERIA (states 200 - 239)
751 --
752 elsif (l_state = 200) then
753 if (l_token is not null) then
754 l_criteria_rec.attribute_application_id := to_number(l_token);
755 l_state := 201;
756 else
757 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
758 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
759 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
760 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
761 FND_MESSAGE.SET_TOKEN('EXPECTED', 'ATTRIBUTE_APPL_ID');
762 FND_MSG_PUB.Add;
763 end if;
764 raise FND_API.G_EXC_ERROR;
765 end if;
766 elsif (l_state = 201) then
767 if (l_token is not null) then
768 l_criteria_rec.attribute_code := l_token;
769 l_state := 202;
770 else
771 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
772 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
773 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
774 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
775 FND_MESSAGE.SET_TOKEN('EXPECTED', 'ATTRIBUTE_CODE');
776 end if;
777 raise FND_API.G_EXC_ERROR;
778 end if;
779 elsif (l_state = 202) then
780 if (l_token is not null) then
781 l_criteria_rec.sequence_number := to_number(l_token);
782 l_value_count := null;
783 l_state := 230;
784 else
785 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
786 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
787 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
788 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
789 FND_MESSAGE.SET_TOKEN('EXPECTED', 'SEQUENCE_NUMBER');
790 FND_MSG_PUB.Add;
791 end if;
792 raise FND_API.G_EXC_ERROR;
793 end if;
794 elsif (l_state = 230) then
795 if (l_token = 'END') then
796 l_state := 239;
797 elsif ( l_token = 'OPERATION' ) or
798 ( l_token = 'VALUE_VARCHAR2' ) or
799 ( l_token = 'VALUE_NUMBER' ) or
800 ( l_token = 'VALUE_DATE' ) or
801 ( l_token = 'START_DATE_ACTIVE') or
802 ( l_token = 'END_DATE_ACTIVE') or
803 (l_token = 'CREATED_BY') or
804 (l_token = 'CREATION_DATE') or
805 (l_token = 'LAST_UPDATED_BY') or
806 (l_token = 'OWNER') or
807 (l_token = 'LAST_UPDATE_DATE') or
808 (l_token = 'LAST_UPDATE_LOGIN') then
809 l_column := l_token;
810 l_state := 231;
811 else
812 --
813 -- error if not expecting attribute values added by the translation team
814 -- or if we have read in more than a certain number of values
815 -- for the same DB column
816 --
817 l_value_count := l_value_count + 1;
818 --
819 -- save second value. It will be the token with error if
820 -- it turns out that there is a parse error on this line.
821 --
822 if (l_value_count = 2) then
823 l_saved_token := l_token;
824 end if;
825 if (l_value_count > AK_ON_OBJECTS_PUB.G_MAX_NUM_LOADER_VALUES) or
826 (l_value_count is null) then
827 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
828 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_EFIELD');
829 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
833 FND_MESSAGE.SET_TOKEN('TOKEN',l_saved_token);
830 if (l_value_count is null) then
831 FND_MESSAGE.SET_TOKEN('TOKEN', l_token);
832 else
834 end if;
835 FND_MESSAGE.SET_TOKEN('EXPECTED','CRITERIA');
836 FND_MSG_PUB.Add;
837 end if;
838 raise FND_API.G_EXC_ERROR;
839 end if;
840 end if;
841 elsif (l_state = 231) then
842 if (l_token = '=') then
843 l_state := 232;
844 else
845 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
846 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
847 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
848 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
849 FND_MESSAGE.SET_TOKEN('EXPECTED', '=');
850 FND_MSG_PUB.Add;
851 end if;
852 raise FND_API.G_EXC_ERROR;
853 end if;
854 elsif (l_state = 232) then
855 l_value_count := 1;
856 if (l_column = 'OPERATION') then
857 l_criteria_rec.OPERATION := l_token;
858 l_state := 230;
859 elsif (l_column = 'VALUE_VARCHAR2') then
860 l_criteria_rec.VALUE_VARCHAR2 := l_token;
861 l_state := 230;
862 elsif (l_column = 'VALUE_NUMBER') then
863 l_criteria_rec.VALUE_NUMBER := to_number(l_token);
864 l_state := 230;
865 elsif (l_column = 'VALUE_DATE') then
866 l_criteria_rec.VALUE_DATE := to_date(l_token,
867 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
868 l_state := 230;
869 elsif (l_column = 'START_DATE_ACTIVE') then
870 l_criteria_rec.START_DATE_ACTIVE := to_date(l_token,
871 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
872 l_state := 230;
873 elsif (l_column = 'END_DATE_ACTIVE') then
874 l_criteria_rec.END_DATE_ACTIVE := to_date(l_token,
875 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
876 l_state := 230;
877 elsif (l_column = 'CREATED_BY') then
878 l_criteria_rec.created_by := to_number(l_token);
879 l_state := 230;
880 elsif (l_column = 'CREATION_DATE') then
881 l_criteria_rec.creation_date := to_date(l_token,
882 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
883 l_state := 230;
884 elsif (l_column = 'LAST_UPDATED_BY') then
885 l_criteria_rec.last_updated_by := to_number(l_token);
886 l_state := 230;
887 elsif (l_column = 'OWNER') then
888 l_criteria_rec.last_updated_by := FND_LOAD_UTIL.OWNER_ID(l_token);
889 l_state := 230;
890 elsif (l_column = 'LAST_UPDATE_DATE') then
891 l_criteria_rec.last_update_date := to_date(l_token,
892 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
893 l_state := 230;
894 elsif (l_column = 'LAST_UPDATE_LOGIN') then
895 l_criteria_rec.last_update_login := to_number(l_token);
896 l_state := 230;
897 else
898 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
899 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
900 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
901 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
902 FND_MESSAGE.SET_TOKEN('EXPECTED', l_column || ' value');
903 FND_MSG_PUB.Add;
904 end if;
905 raise FND_API.G_EXC_ERROR;
906 end if;
907 elsif (l_state = 239) then
908 if (l_token = 'CRITERIA') then
909 l_value_count := null;
910 l_state := 10;
911 l_criteria_index := l_criteria_index + 1;
912 l_criteria_tbl(l_criteria_index) := l_criteria_rec;
913 else
914 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
915 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
916 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
917 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
918 FND_MESSAGE.SET_TOKEN('EXPECTED', 'CRITERIA');
919 end if;
920 raise FND_API.G_EXC_ERROR;
921 end if;
922
923 end if; -- if l_state = ...
924
925 -- Get rid of leading white spaces, so that buffer would become
926 -- null if the only thing in it are white spaces
927 l_buffer := LTRIM(l_buffer);
928
929 -- Get the next non-blank, non-comment line if current line is
930 -- fully parsed
931 while (l_buffer is null and l_eof_flag = 'N' and p_index <= AK_ON_OBJECTS_PVT.G_UPL_TABLE_NUM) loop
932 AK_ON_OBJECTS_PVT.READ_LINE (
933 p_return_status => l_return_status,
934 p_index => p_index,
935 p_buffer => l_buffer,
936 p_lines_read => l_lines_read,
937 p_eof_flag => l_eof_flag,
938 p_upl_loader_cur => p_upl_loader_cur
939 );
940 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
941 (l_return_status = FND_API.G_RET_STS_ERROR) then
942 RAISE FND_API.G_EXC_ERROR;
943 end if;
944 l_line_num := l_line_num + l_lines_read;
945 --
946 -- trim leading spaces and discard comment lines
947 --
948 l_buffer := LTRIM(l_buffer);
949 if (SUBSTR(l_buffer, 1, 1) = '#') then
950 l_buffer := null;
951 end if;
952 end loop;
953
954 end LOOP;
955 -- If the loops end in a state other then at the end of a region
956 -- (state 0) or when the beginning of another business object was
957 -- detected, then the file must have ended prematurely, which is an error
958 if (l_state <> 0) and (l_more_custom) then
959 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
960 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
961 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
965 end if;
962 FND_MESSAGE.SET_TOKEN('TOKEN', 'END OF FILE');
963 FND_MESSAGE.SET_TOKEN('EXPECTED', null);
964 FND_MSG_PUB.Add;
966 --dbms_output.put_line('Unexpected END OF FILE: state is ' ||
967 -- to_char(l_state));
968 raise FND_API.G_EXC_ERROR;
969 end if;
970
971 --
972 -- create or update all regions to the database
973 --
974 if (l_custom_tbl.count > 0) then
975 for l_index in l_custom_tbl.FIRST .. l_custom_tbl.LAST loop
976 if (l_custom_tbl.exists(l_index)) then
977 if AK_CUSTOM_PVT.CUSTOM_EXISTS (
978 p_api_version_number => 1.0,
979 p_return_status => l_return_status,
980 p_custom_appl_id => l_custom_tbl(l_index).customization_appl_id,
981 p_custom_code => l_custom_tbl(l_index).customization_code,
982 p_region_application_id => l_custom_tbl(l_index).region_appl_id,
983 p_region_code => l_custom_tbl(l_index).region_code) then
984 --
985 -- Update Regions only if G_UPDATE_MODE is TRUE
986 --
987 if (AK_UPLOAD_GRP.G_UPDATE_MODE) then
988 AK_CUSTOM_PVT.UPDATE_CUSTOM (
989 p_validation_level => p_validation_level,
990 p_api_version_number => 1.0,
991 p_msg_count => l_msg_count,
992 p_msg_data => l_msg_data,
993 p_return_status => l_return_status,
994 p_custom_appl_id => l_custom_tbl(l_index).customization_appl_id,
995 p_custom_code => l_custom_tbl(l_index).customization_code,
996 p_region_application_id => l_custom_tbl(l_index).region_appl_id,
997 p_region_code => l_custom_tbl(l_index).region_code,
998 p_verticalization_id => l_custom_tbl(l_index).verticalization_id,
999 p_localization_code => l_custom_tbl(l_index).localization_code,
1000 p_org_id => l_custom_tbl(l_index).org_id,
1001 p_site_id => l_custom_tbl(l_index).site_id,
1002 p_responsibility_id => l_custom_tbl(l_index).responsibility_id,
1003 p_web_user_id => l_custom_tbl(l_index).web_user_id,
1004 p_default_customization_flag => l_custom_tbl(l_index).default_customization_flag,
1005 p_customization_level_id => l_custom_tbl(l_index).customization_level_id,
1006 p_developer_mode => l_custom_tbl(l_index).developer_mode,
1007 p_reference_path => l_custom_tbl(l_index).reference_path,
1008 p_function_name => l_custom_tbl(l_index).function_name,
1009 p_start_date_Active => l_custom_tbl(l_index).start_date_Active,
1010 p_end_date_active => l_custom_tbl(l_index).end_date_active,
1011 p_name => l_custom_tbl(l_index).name,
1012 p_description => l_custom_Tbl(l_index).description,
1013 p_created_by => l_custom_tbl(l_index).created_by,
1014 p_creation_date => l_custom_tbl(l_index).creation_date,
1015 p_last_updated_by => l_custom_tbl(l_index).last_updated_by,
1016 p_last_update_date => l_custom_tbl(l_index).last_update_date,
1017 p_last_update_login => l_custom_tbl(l_index).last_update_login,
1018 p_loader_timestamp => p_loader_timestamp,
1019 p_pass => p_pass,
1020 p_copy_redo_flag => l_copy_redo_flag
1021 );
1022 elsif (AK_UPLOAD_GRP.G_NO_CUSTOM_UPDATE) then
1023 -- do not update customized data
1024 l_error_key_info := 'CUSTOMIZATION CAPPLID='||to_char(l_custom_tbl(l_index).customization_appl_id)||
1025 ' CCODE='||l_custom_tbl(l_index).customization_code||' RAPPLID='||
1026 to_char(l_custom_tbl(l_index).region_appl_id)||' RCODE='||
1027 l_custom_tbl(l_index).region_code;
1028 select ac.last_updated_by, act.last_updated_by,
1029 ac.last_update_date, act.last_update_date
1030 into l_user_id1, l_user_id2, l_update1, l_update2
1031 from ak_customizations ac, ak_customizations_tl act
1032 where ac.customization_application_id = l_custom_tbl(l_index).customization_appl_id
1033 and ac.customization_code = l_custom_tbl(l_index).customization_code
1034 and ac.region_application_id = l_custom_tbl(l_index).region_appl_id
1035 and ac.region_code = l_custom_tbl(l_index).region_code
1036 and ac.customization_application_id = act.customization_application_id
1037 and ac.customization_code = act.customization_code
1038 and ac.region_application_id = act.region_application_id
1039 and ac.region_code = act.region_code
1040 and act.language = userenv('LANG');
1041 /*if (( l_user_id1 = 1 or l_user_id1 = 2) and
1042 (l_user_id2 = 1 or l_user_id2 = 2)) then*/
1043 if (AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
1044 p_loader_timestamp => p_loader_timestamp,
1045 p_created_by => l_custom_tbl(l_index).created_by,
1046 p_creation_date => l_custom_tbl(l_index).creation_date,
1047 p_last_updated_by => l_custom_tbl(l_index).last_updated_by,
1048 p_db_last_updated_by => l_user_id1,
1049 p_last_update_date => l_custom_tbl(l_index).last_update_date,
1050 p_db_last_update_date => l_update1,
1051 p_last_update_login => l_custom_tbl(l_index).last_update_login,
1052 p_create_or_update => 'UPDATE') and
1053
1054 AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
1055 p_loader_timestamp => p_loader_timestamp,
1056 p_created_by => l_custom_tbl(l_index).created_by,
1057 p_creation_date => l_custom_tbl(l_index).creation_date,
1058 p_last_updated_by => l_custom_tbl(l_index).last_updated_by,
1059 p_db_last_updated_by => l_user_id2,
1060 p_last_update_date => l_custom_tbl(l_index).last_update_date,
1061 p_db_last_update_date => l_update2,
1062 p_last_update_login => l_custom_tbl(l_index).last_update_login,
1063 p_create_or_update => 'UPDATE')) then
1064
1068 p_msg_count => l_msg_count,
1065 AK_CUSTOM_PVT.UPDATE_CUSTOM (
1066 p_validation_level => p_validation_level,
1067 p_api_version_number => 1.0,
1069 p_msg_data => l_msg_data,
1070 p_return_status => l_return_status,
1071 p_custom_appl_id => l_custom_tbl(l_index).customization_appl_id,
1072 p_custom_code => l_custom_tbl(l_index).customization_code,
1073 p_region_application_id => l_custom_tbl(l_index).region_appl_id,
1074 p_region_code => l_custom_tbl(l_index).region_code,
1075 p_verticalization_id => l_custom_tbl(l_index).verticalization_id,
1076 p_localization_code => l_custom_tbl(l_index).localization_code,
1077 p_org_id => l_custom_tbl(l_index).org_id,
1078 p_site_id => l_custom_tbl(l_index).site_id,
1079 p_responsibility_id => l_custom_tbl(l_index).responsibility_id,
1080 p_web_user_id => l_custom_tbl(l_index).web_user_id,
1081 p_default_customization_flag => l_custom_tbl(l_index).default_customization_flag,
1082 p_customization_level_id => l_custom_tbl(l_index).customization_level_id,
1083 p_developer_mode => l_custom_tbl(l_index).developer_mode,
1084 p_reference_path => l_custom_tbl(l_index).reference_path,
1085 p_function_name => l_custom_tbl(l_index).function_name,
1086 p_start_date_active => l_custom_tbl(l_index).start_date_Active,
1087 p_end_date_active => l_custom_tbl(l_index).end_date_active,
1088 p_name => l_custom_tbl(l_index).name,
1089 p_description => l_custom_tbl(l_index).description,
1090 p_created_by => l_custom_tbl(l_index).created_by,
1091 p_creation_date => l_custom_tbl(l_index).creation_date,
1092 p_last_updated_by => l_custom_tbl(l_index).last_updated_by,
1093 p_last_update_date => l_custom_tbl(l_index).last_update_date,
1094 p_last_update_login => l_custom_tbl(l_index).last_update_login,
1095 p_loader_timestamp => p_loader_timestamp,
1096 p_pass => p_pass,
1097 p_copy_redo_flag => l_copy_redo_flag
1098 );
1099 end if; -- /* if ( l_user_id1 = 1 and l_user_id1 = 1 ) */
1100 end if; -- /* if G_UPDATE_MODE G_NC_UPDATE_MODE*/
1101 else
1102 AK_CUSTOM_PVT.CREATE_CUSTOM (
1103 p_validation_level => p_validation_level,
1104 p_api_version_number => 1.0,
1105 p_msg_count => l_msg_count,
1106 p_msg_data => l_msg_data,
1107 p_return_status => l_return_status,
1108 p_custom_appl_id => l_custom_tbl(l_index).customization_appl_id,
1109 p_custom_code => l_custom_tbl(l_index).customization_code,
1110 p_region_appl_id => l_custom_tbl(l_index).region_appl_id,
1111 p_region_code => l_custom_tbl(l_index).region_code,
1112 p_verticalization_id => l_custom_tbl(l_index).verticalization_id,
1113 p_localization_code => l_custom_tbl(l_index).localization_code,
1114 p_org_id => l_custom_tbl(l_index).org_id,
1115 p_site_id => l_custom_tbl(l_index).site_id,
1116 p_responsibility_id => l_custom_tbl(l_index).responsibility_id,
1117 p_web_user_id => l_custom_tbl(l_index).web_user_id,
1118 p_default_customization_flag => l_custom_tbl(l_index).default_customization_flag,
1119 p_customization_level_id => l_custom_tbl(l_index).customization_level_id,
1120 p_developer_mode => l_custom_tbl(l_index).developer_mode,
1121 p_reference_path => l_custom_tbl(l_index).reference_path,
1122 p_function_name => l_custom_tbl(l_index).function_name,
1123 p_start_date_active => l_custom_tbl(l_index).start_date_active,
1124 p_end_date_active => l_custom_tbl(l_index).end_date_active,
1125 p_name => l_custom_tbl(l_index).name,
1126 p_description => l_custom_tbl(l_index).description,
1127 p_created_by => l_custom_tbl(l_index).created_by,
1128 p_creation_date => l_custom_tbl(l_index).creation_date,
1129 p_last_updated_by => l_custom_tbl(l_index).last_updated_by,
1130 p_last_update_date => l_custom_tbl(l_index).last_update_date,
1131 p_last_update_login => l_custom_tbl(l_index).last_update_login,
1132 p_loader_timestamp => p_loader_timestamp,
1133 p_pass => p_pass,
1134 p_copy_redo_flag => l_copy_redo_flag
1135 );
1136 end if; -- /* if CUSTOM_EXISTS */
1137 --
1138 -- If API call returns with an error status, upload aborts
1139 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1140 (l_return_status = FND_API.G_RET_STS_ERROR) then
1141 RAISE FND_API.G_EXC_ERROR;
1142 end if; -- /* if l_return_status */
1143 --
1144 -- if validation fails, then this record should go to second pass
1145 if (l_copy_redo_flag) then
1146 G_CUSTOM_REDO_INDEX := G_CUSTOM_REDO_INDEX + 1;
1147 G_CUSTOM_REDO_TBL(G_CUSTOM_REDO_INDEX) := l_custom_tbl(l_index);
1148 l_copy_redo_flag := FALSE;
1149 end if; --/* if l_copy_redo_flag */
1150 end if;
1151 end loop;
1152 end if;
1153
1154 --
1155 -- create or update all custom_regions to the database
1156 --
1157 if (l_cust_region_tbl.count > 0) then
1158 for l_index in l_cust_region_tbl.FIRST .. l_cust_region_tbl.LAST loop
1159 if (l_cust_region_tbl.exists(l_index)) then
1160 if AK_CUSTOM_PVT.CUST_REGION_EXISTS (
1161 p_api_version_number => 1.0,
1162 p_return_status => l_return_status,
1163 p_custom_appl_id => l_cust_region_tbl(l_index).customization_appl_id,
1164 p_custom_code => l_cust_region_tbl(l_index).customization_code,
1165 p_region_application_id =>
1166 l_cust_region_tbl(l_index).region_appl_id,
1170 -- Update Regions only if G_UPDATE_MODE is TRUE
1167 p_region_code => l_cust_region_tbl(l_index).region_code,
1168 p_property_name => l_cust_region_tbl(l_index).property_name) then
1169 --
1171 --
1172 if (AK_UPLOAD_GRP.G_UPDATE_MODE) then
1173 AK_CUSTOM_PVT.UPDATE_CUST_REGION (
1174 p_validation_level => p_validation_level,
1175 p_api_version_number => 1.0,
1176 p_msg_count => l_msg_count,
1177 p_msg_data => l_msg_data,
1178 p_return_status => l_return_status,
1179 p_custom_appl_id => l_cust_region_tbl(l_index).customization_appl_id,
1180 p_custom_code => l_cust_region_tbl(l_index).customization_code,
1181 p_region_application_id => l_cust_region_tbl(l_index).region_appl_id,
1182 p_region_code => l_cust_region_tbl(l_index).region_code,
1183 p_property_name => l_cust_region_tbl(l_index).property_name,
1184 p_property_varchar2_value => l_cust_region_tbl(l_index).property_varchar2_value,
1185 p_property_number_value => l_cust_region_tbl(l_index).property_number_value,
1186 p_criteria_join_condition => l_cust_region_tbl(l_index).criteria_join_condition,
1187 p_property_varchar2_value_tl => l_cust_region_tbl(l_index).property_varchar2_value_tl,
1188 p_created_by => l_cust_region_tbl(l_index).created_by,
1189 p_creation_date => l_cust_region_tbl(l_index).creation_date,
1190 p_last_updated_by => l_cust_region_tbl(l_index).last_updated_by,
1191 p_last_update_date => l_cust_region_tbl(l_index).last_update_date,
1192 p_last_update_login => l_cust_region_tbl(l_index).last_update_login,
1193 p_loader_timestamp => p_loader_timestamp,
1194 p_pass => p_pass,
1195 p_copy_redo_flag => l_copy_redo_flag
1196 );
1197 elsif (AK_UPLOAD_GRP.G_NO_CUSTOM_UPDATE) then
1198 -- do not update customized data
1199 l_error_key_info := 'CUST REG CAPPLID='||to_char(l_cust_region_tbl(l_index).customization_appl_id)||
1200 ' CCODE='||l_cust_region_tbl(l_index).customization_code||' RAPPLID='||
1201 to_char(l_cust_region_tbl(l_index).region_appl_id)||' RCODE='||
1202 l_cust_region_tbl(l_index).region_code||' PNAME='||
1203 l_cust_region_tbl(l_index).property_name;
1204
1205 select ac.last_updated_by, act.last_updated_by,
1206 ac.last_update_date, act.last_update_date
1207 into l_user_id1, l_user_id2, l_update1, l_update2
1208 from ak_custom_regions ac, ak_custom_regions_tl act
1209 where ac.customization_application_id = l_cust_region_tbl(l_index).customization_appl_id
1210 and ac.customization_code = l_cust_region_tbl(l_index).customization_code
1211 and ac.region_application_id = l_cust_region_tbl(l_index).region_appl_id
1212 and ac.region_code = l_cust_region_tbl(l_index).region_code
1213 and ac.property_name = l_cust_region_tbl(l_index).property_name
1214 and ac.customization_application_id = act.customization_application_id
1215 and ac.customization_code = act.customization_code
1216 and ac.region_application_id = act.region_application_id
1217 and ac.region_code = act.region_code
1218 and ac.property_name = act.property_name
1219 and act.language = userenv('LANG');
1220 /*if (( l_user_id1 = 1 or l_user_id1 = 2)
1221 and (l_user_id2 = 1 or l_user_id2 = 2)) then*/
1222 if (AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
1223 p_loader_timestamp => p_loader_timestamp,
1224 p_created_by => l_cust_region_tbl(l_index).created_by,
1225 p_creation_date => l_cust_region_tbl(l_index).creation_date,
1226 p_last_updated_by => l_cust_region_tbl(l_index).last_updated_by,
1227 p_db_last_updated_by => l_user_id1,
1228 p_last_update_date => l_cust_region_tbl(l_index).last_update_date,
1229 p_db_last_update_date => l_update1,
1230 p_last_update_login => l_cust_region_tbl(l_index).last_update_login,
1231 p_create_or_update => 'UPDATE') and
1232
1233 AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
1234 p_loader_timestamp => p_loader_timestamp,
1235 p_created_by => l_cust_region_tbl(l_index).created_by,
1236 p_creation_date => l_cust_region_tbl(l_index).creation_date,
1237 p_last_updated_by => l_cust_region_tbl(l_index).last_updated_by,
1238 p_db_last_updated_by => l_user_id2,
1239 p_last_update_date => l_cust_region_tbl(l_index).last_update_date,
1240 p_db_last_update_date => l_update2,
1241 p_last_update_login => l_cust_region_tbl(l_index).last_update_login,
1242 p_create_or_update => 'UPDATE')) then
1243
1244 AK_CUSTOM_PVT.UPDATE_CUST_REGION (
1245 p_validation_level => p_validation_level,
1246 p_api_version_number => 1.0,
1247 p_msg_count => l_msg_count,
1248 p_msg_data => l_msg_data,
1249 p_return_status => l_return_status,
1250 p_custom_appl_id => l_cust_region_tbl(l_index).customization_appl_id,
1251 p_custom_code => l_cust_region_tbl(l_index).customization_code,
1252 p_region_application_id => l_cust_region_tbl(l_index).region_appl_id,
1253 p_region_code => l_cust_region_tbl(l_index).region_code,
1254 p_property_name => l_cust_region_tbl(l_index).property_name,
1255 p_property_varchar2_value => l_cust_region_tbl(l_index).property_varchar2_value,
1256 p_property_number_value => l_cust_region_tbl(l_index).property_number_value,
1257 p_criteria_join_condition => l_cust_region_tbl(l_index).criteria_join_condition,
1261 p_last_updated_by => l_cust_region_tbl(l_index).last_updated_by,
1258 p_property_varchar2_value_tl => l_cust_region_tbl(l_index).property_varchar2_value_tl,
1259 p_created_by => l_cust_region_tbl(l_index).created_by,
1260 p_creation_date => l_cust_region_tbl(l_index).creation_date,
1262 p_last_update_date => l_cust_region_tbl(l_index).last_update_date,
1263 p_last_update_login => l_cust_region_tbl(l_index).last_update_login,
1264 p_loader_timestamp => p_loader_timestamp,
1265 p_pass => p_pass,
1266 p_copy_redo_flag => l_copy_redo_flag
1267 );
1268 end if; -- /* if ( l_user_id1 = 1 and l_user_id2 = 1 ) */
1269 end if; -- /* if G_UPDATE_MODE G_NC_UPDATE_MODE*/
1270 else
1271 AK_CUSTOM_PVT.CREATE_CUST_REGION (
1272 p_validation_level => p_validation_level,
1273 p_api_version_number => 1.0,
1274 p_msg_count => l_msg_count,
1275 p_msg_data => l_msg_data,
1276 p_return_status => l_return_status,
1277 p_custom_appl_id => l_cust_region_tbl(l_index).customization_appl_id,
1278 p_custom_code => l_cust_region_tbl(l_index).customization_code,
1279 p_region_appl_id => l_cust_region_tbl(l_index).region_appl_id,
1280 p_region_code => l_cust_region_tbl(l_index).region_code,
1281 p_property_name => l_cust_region_tbl(l_index).property_name,
1282 p_property_varchar2_value => l_cust_region_tbl(l_index).property_varchar2_value,
1283 p_property_number_value => l_cust_region_tbl(l_index).property_number_value,
1284 p_criteria_join_condition => l_cust_region_tbl(l_index).criteria_join_condition,
1285 p_property_varchar2_value_tl => l_cust_region_tbl(l_index).property_varchar2_value_tl,
1286 p_created_by => l_cust_region_tbl(l_index).created_by,
1287 p_creation_date => l_cust_region_tbl(l_index).creation_date,
1288 p_last_updated_by => l_cust_region_tbl(l_index).last_updated_by,
1289 p_last_update_date => l_cust_region_tbl(l_index).last_update_date,
1290 p_last_update_login => l_cust_region_tbl(l_index).last_update_login,
1291 p_loader_timestamp => p_loader_timestamp,
1292 p_pass => p_pass,
1293 p_copy_redo_flag => l_copy_redo_flag
1294 );
1295 end if; -- /* if CUST_REGION_EXISTS */
1296 --
1297 -- If API call returns with an error status, upload aborts
1298 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1299 (l_return_status = FND_API.G_RET_STS_ERROR) then
1300 RAISE FND_API.G_EXC_ERROR;
1301 end if; -- /* if l_return_status */
1302 --
1303 -- if validation fails, then this record should go to second pas
1304 if (l_copy_redo_flag) then
1305 G_CUST_REGION_REDO_INDEX := G_CUST_REGION_REDO_INDEX + 1;
1306 G_CUST_REGION_REDO_TBL(G_CUST_REGION_REDO_INDEX) := l_cust_region_tbl(l_index);
1307 l_copy_redo_flag := FALSE;
1308 end if; --/* if l_copy_redo_flag */
1309 end if;
1310 end loop;
1311 end if;
1312
1313 --
1314 -- create or update all custom_region_items to the database
1315 --
1316 if (l_cust_reg_item_tbl.count > 0) then
1317 for l_index in l_cust_reg_item_tbl.FIRST .. l_cust_reg_item_tbl.LAST loop
1318 if (l_cust_reg_item_tbl.exists(l_index)) then
1319 if AK_CUSTOM_PVT.CUST_REG_ITEM_EXISTS (
1320 p_api_version_number => 1.0,
1321 p_return_status => l_return_status,
1322 p_custom_appl_id => l_cust_reg_item_tbl(l_index).customization_appl_id,
1323 p_custom_code => l_cust_reg_item_tbl(l_index).customization_code,
1324 p_region_application_id => l_cust_reg_item_tbl(l_index).region_appl_id,
1325 p_region_code => l_cust_reg_item_tbl(l_index).region_code,
1326 p_attribute_appl_id => l_cust_reg_item_tbl(l_index).attr_appl_id,
1327 p_attribute_code => l_cust_reg_item_tbl(l_index).attr_code,
1328 p_property_name => l_cust_reg_item_tbl(l_index).property_name) then
1329 --
1330 -- Update Regions only if G_UPDATE_MODE is TRUE
1331 --
1332 if (AK_UPLOAD_GRP.G_UPDATE_MODE) then
1333 AK_CUSTOM_PVT.UPDATE_CUST_REG_ITEM (
1334 p_validation_level => p_validation_level,
1335 p_api_version_number => 1.0,
1336 p_msg_count => l_msg_count,
1337 p_msg_data => l_msg_data,
1338 p_return_status => l_return_status,
1339 p_custom_appl_id => l_cust_reg_item_tbl(l_index).customization_appl_id,
1340 p_custom_code => l_cust_reg_item_tbl(l_index).customization_code,
1341 p_region_application_id => l_cust_reg_item_tbl(l_index).region_appl_id,
1342 p_region_code => l_cust_reg_item_tbl(l_index).region_code,
1343 p_attribute_appl_id => l_cust_reg_item_tbl(l_index).attr_appl_id,
1344 p_attribute_code => l_cust_reg_item_tbl(l_index).attr_code,
1345 p_property_name => l_cust_reg_item_tbl(l_index).property_name,
1346 p_property_varchar2_value => l_cust_reg_item_tbl(l_index).property_varchar2_value,
1347 p_property_number_value => l_cust_reg_item_tbl(l_index).property_number_value,
1348 p_property_date_value => l_cust_reg_item_tbl(l_index).property_date_value,
1349 p_property_varchar2_value_tl => l_cust_reg_item_tbl(l_index).property_varchar2_value_tl,
1350 p_created_by => l_cust_reg_item_tbl(l_index).created_by,
1351 p_creation_date => l_cust_reg_item_tbl(l_index).creation_date,
1352 p_last_updated_by => l_cust_reg_item_tbl(l_index).last_updated_by,
1353 p_last_update_date => l_cust_reg_item_tbl(l_index).last_update_date,
1354 p_last_update_login => l_cust_reg_item_tbl(l_index).last_update_login,
1355 p_loader_timestamp => p_loader_timestamp,
1356 p_pass => p_pass,
1360 l_error_key_info := 'CUST REG ITEM CAPPLID='||to_char(l_cust_reg_item_tbl(l_index).customization_appl_id)||
1357 p_copy_redo_flag => l_copy_redo_flag
1358 );
1359 elsif (AK_UPLOAD_GRP.G_NO_CUSTOM_UPDATE) then
1361 ' CCODE='||l_cust_reg_item_tbl(l_index).customization_code||' RAPPLID='||
1362 to_char(l_cust_reg_item_tbl(l_index).region_appl_id)||' RCODE='||
1363 l_cust_reg_item_tbl(l_index).region_code||' AAPPLID='||
1364 to_char(l_cust_reg_item_tbl(l_index).attr_appl_id)||' ACODE='||
1365 l_cust_reg_item_tbl(l_index).attr_code||' PNAME='||
1366 l_cust_reg_item_tbl(l_index).property_name;
1367
1368 -- do not update customized data
1369 select ac.last_updated_by, act.last_updated_by,
1370 ac.last_update_date, act.last_update_date
1371 into l_user_id1, l_user_id2, l_update1, l_update2
1372 from ak_custom_region_items ac, ak_custom_region_items_tl act
1373 where ac.customization_application_id = l_cust_reg_item_tbl(l_index).customization_appl_id
1374 and ac.customization_code = l_cust_reg_item_tbl(l_index).customization_code
1375 and ac.region_application_id = l_cust_reg_item_tbl(l_index).region_appl_id
1376 and ac.region_code = l_cust_reg_item_tbl(l_index).region_code
1377 and ac.property_name = l_cust_reg_item_tbl(l_index).property_name
1378 and ac.attribute_application_id = l_cust_reg_item_tbl(l_index).attr_appl_id
1379 and ac.attribute_code = l_cust_reg_item_tbl(l_index).attr_code
1380 and ac.customization_application_id = act.customization_application_id
1381 and ac.customization_code = act.customization_code
1382 and ac.region_application_id = act.region_application_id
1383 and ac.region_code = act.region_code
1384 and ac.property_name = act.property_name
1385 and ac.attribute_application_id = act.attribute_application_id
1386 and ac.attribute_code = act.attribute_code
1387 and act.language = userenv('LANG');
1388 /*if (( l_user_id1 = 1 or l_user_id1 = 2) and
1389 (l_user_id2 = 1 or l_user_id2 = 2 )) then*/
1390 if (AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
1391 p_loader_timestamp => p_loader_timestamp,
1392 p_created_by => l_cust_reg_item_tbl(l_index).created_by,
1393 p_creation_date => l_cust_reg_item_tbl(l_index).creation_date,
1394 p_last_updated_by => l_cust_reg_item_tbl(l_index).last_updated_by,
1395 p_db_last_updated_by => l_user_id1,
1396 p_last_update_date => l_cust_reg_item_tbl(l_index).last_update_date,
1397 p_db_last_update_date => l_update1,
1398 p_last_update_login => l_cust_reg_item_tbl(l_index).last_update_login,
1399 p_create_or_update => 'UPDATE') and
1400
1401 AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
1402 p_loader_timestamp => p_loader_timestamp,
1403 p_created_by => l_cust_reg_item_tbl(l_index).created_by,
1404 p_creation_date => l_cust_reg_item_tbl(l_index).creation_date,
1405 p_last_updated_by => l_cust_reg_item_tbl(l_index).last_updated_by,
1406 p_db_last_updated_by => l_user_id2,
1407 p_last_update_date => l_cust_reg_item_tbl(l_index).last_update_date,
1408 p_db_last_update_date => l_update2,
1409 p_last_update_login => l_cust_reg_item_tbl(l_index).last_update_login,
1410 p_create_or_update => 'UPDATE')) then
1411
1412 AK_CUSTOM_PVT.UPDATE_CUST_REG_ITEM (
1413 p_validation_level => p_validation_level,
1414 p_api_version_number => 1.0,
1415 p_msg_count => l_msg_count,
1416 p_msg_data => l_msg_data,
1417 p_return_status => l_return_status,
1418 p_custom_appl_id => l_cust_reg_item_tbl(l_index).customization_appl_id,
1419 p_custom_code => l_cust_reg_item_tbl(l_index).customization_code,
1420 p_region_application_id => l_cust_reg_item_tbl(l_index).region_appl_id,
1421 p_region_code => l_cust_reg_item_tbl(l_index).region_code,
1422 p_attribute_appl_id => l_cust_reg_item_tbl(l_index).attr_appl_id,
1423 p_attribute_code => l_cust_reg_item_tbl(l_index).attr_code,
1424 p_property_name => l_cust_reg_item_tbl(l_index).property_name,
1425 p_property_varchar2_value => l_cust_reg_item_tbl(l_index).property_varchar2_value,
1426 p_property_number_value => l_cust_reg_item_tbl(l_index).property_number_value,
1427 p_property_date_value => l_cust_reg_item_tbl(l_index).property_date_value,
1428 p_property_varchar2_value_tl => l_cust_reg_item_tbl(l_index).property_varchar2_value_tl,
1429 p_created_by => l_cust_reg_item_tbl(l_index).created_by,
1430 p_creation_date => l_cust_reg_item_tbl(l_index).creation_date,
1431 p_last_updated_by => l_cust_reg_item_tbl(l_index).last_updated_by,
1432 p_last_update_date => l_cust_reg_item_tbl(l_index).last_update_date,
1433 p_last_update_login => l_cust_reg_item_tbl(l_index).last_update_login,
1434 p_loader_timestamp => p_loader_timestamp,
1435 p_pass => p_pass,
1436 p_copy_redo_flag => l_copy_redo_flag
1437 );
1438 end if; -- /* if ( l_user_id1 = 1 and l_user_id2 = 1 ) */
1439 end if; -- /* if G_UPDATE_MODE G_NC_UPDATE_MODE*/
1440 else
1441 AK_CUSTOM_PVT.CREATE_CUST_REG_ITEM (
1442 p_validation_level => p_validation_level,
1443 p_api_version_number => 1.0,
1444 p_msg_count => l_msg_count,
1445 p_msg_data => l_msg_data,
1449 p_region_appl_id => l_cust_reg_item_tbl(l_index).region_appl_id,
1446 p_return_status => l_return_status,
1447 p_custom_appl_id => l_cust_reg_item_tbl(l_index).customization_appl_id,
1448 p_custom_code => l_cust_reg_item_tbl(l_index).customization_code,
1450 p_region_code => l_cust_reg_item_tbl(l_index).region_code,
1451 p_attr_appl_id => l_cust_reg_item_tbl(l_index).attr_appl_id,
1452 p_attr_code => l_cust_reg_item_tbl(l_index).attr_code,
1453 p_property_name => l_cust_reg_item_tbl(l_index).property_name,
1454 p_property_varchar2_value => l_cust_reg_item_tbl(l_index).property_varchar2_value,
1455 p_property_number_value => l_cust_reg_item_tbl(l_index).property_number_value,
1456 p_property_date_value => l_cust_reg_item_tbl(l_index).property_date_value,
1457 p_property_varchar2_value_tl => l_cust_reg_item_tbl(l_index).property_varchar2_value_tl,
1458 p_created_by => l_cust_reg_item_tbl(l_index).created_by,
1459 p_creation_date => l_cust_reg_item_tbl(l_index).creation_date,
1460 p_last_updated_by => l_cust_reg_item_tbl(l_index).last_updated_by,
1461 p_last_update_date => l_cust_reg_item_tbl(l_index).last_update_date,
1462 p_last_update_login => l_cust_reg_item_tbl(l_index).last_update_login,
1463 p_loader_timestamp => p_loader_timestamp,
1464 p_pass => p_pass,
1465 p_copy_redo_flag => l_copy_redo_flag
1466 );
1467 end if; -- /* if CUST_REGION_EXISTS */
1468 --
1469 -- If API call returns with an error status, upload aborts
1470 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1471 (l_return_status = FND_API.G_RET_STS_ERROR) then
1472 RAISE FND_API.G_EXC_ERROR;
1473 end if; -- /* if l_return_status */
1474 --
1475 -- if validation fails, then this record should go to second pas
1476 if (l_copy_redo_flag) then
1477 G_CUST_REG_ITEM_REDO_INDEX := G_CUST_REG_ITEM_REDO_INDEX + 1;
1478 G_CUST_REG_ITEM_REDO_TBL(G_CUST_REG_ITEM_REDO_INDEX) := l_cust_reg_item_tbl(l_index);
1479 l_copy_redo_flag := FALSE;
1480 end if; --/* if l_copy_redo_flag */
1481 end if;
1482 end loop;
1483 end if;
1484
1485 --
1486 -- create or update all custom_criteria to the database
1487 --
1488 if (l_criteria_tbl.count > 0) then
1489 for l_index in l_criteria_tbl.FIRST .. l_criteria_tbl.LAST loop
1490 if (l_criteria_tbl.exists(l_index)) then
1491 if AK_CUSTOM_PVT.CRITERIA_EXISTS (
1492 p_api_version_number => 1.0,
1493 p_return_status => l_return_status,
1494 p_custom_appl_id => l_criteria_tbl(l_index).customization_application_id,
1495 p_custom_code => l_criteria_tbl(l_index).customization_code,
1496 p_region_application_id => l_criteria_tbl(l_index).region_application_id,
1497 p_region_code => l_criteria_tbl(l_index).region_code,
1498 p_attribute_appl_id => l_criteria_tbl(l_index).attribute_application_id,
1499 p_attribute_code => l_criteria_tbl(l_index).attribute_code,
1500 p_sequence_number => l_criteria_tbl(l_index).sequence_number) then
1501 --
1502 -- Update Regions only if G_UPDATE_MODE is TRUE
1503 --
1504 if (AK_UPLOAD_GRP.G_UPDATE_MODE) then
1505 AK_CUSTOM_PVT.UPDATE_CRITERIA (
1506 p_validation_level => p_validation_level,
1507 p_api_version_number => 1.0,
1508 p_msg_count => l_msg_count,
1509 p_msg_data => l_msg_data,
1510 p_return_status => l_return_status,
1511 p_custom_appl_id => l_criteria_tbl(l_index).customization_application_id,
1512 p_custom_code => l_criteria_tbl(l_index).customization_code,
1513 p_region_application_id => l_criteria_tbl(l_index).region_application_id,
1514 p_region_code => l_criteria_tbl(l_index).region_code,
1515 p_attribute_appl_id => l_criteria_tbl(l_index).attribute_application_id,
1516 p_attribute_code => l_criteria_tbl(l_index).attribute_code,
1517 p_sequence_number => l_criteria_tbl(l_index).sequence_number,
1518 p_operation => l_criteria_tbl(l_index).operation,
1519 p_value_varchar2 => l_criteria_tbl(l_index).value_varchar2,
1520 p_value_number => l_criteria_tbl(l_index).value_number,
1521 p_value_date => l_criteria_tbl(l_index).value_date,
1522 p_start_date_active => l_criteria_tbl(l_index).start_date_active,
1523 p_end_date_active => l_criteria_tbl(l_index).end_date_active,
1524 p_created_by => l_criteria_tbl(l_index).created_by,
1525 p_creation_date => l_criteria_tbl(l_index).creation_date,
1526 p_last_updated_by => l_criteria_tbl(l_index).last_updated_by,
1527 p_last_update_date => l_criteria_tbl(l_index).last_update_date,
1528 p_last_update_login => l_criteria_tbl(l_index).last_update_login,
1529 p_loader_timestamp => p_loader_timestamp,
1530 p_pass => p_pass,
1531 p_copy_redo_flag => l_copy_redo_flag
1532 );
1533 elsif (AK_UPLOAD_GRP.G_NO_CUSTOM_UPDATE) then
1534 l_error_key_info := 'CRITERIA CAPPLID='||to_char(l_criteria_tbl(l_index).customization_application_id)||
1535 ' CCODE='||l_criteria_tbl(l_index).customization_code||' RAPPLID='||
1536 to_char(l_criteria_tbl(l_index).region_application_id)||' RCODE='||
1537 l_criteria_tbl(l_index).region_code||' AAPPLID='||
1538 to_char(l_criteria_tbl(l_index).attribute_application_id)||' ACODE='||
1539 l_criteria_tbl(l_index).attribute_code;
1540
1541 -- do not update customized data
1542 select last_updated_by, last_update_date into
1543 l_user_id1, l_update1
1544 from ak_criteria ac
1548 and ac.region_code = l_criteria_tbl(l_index).region_code
1545 where ac.customization_application_id = l_criteria_tbl(l_index).customization_application_id
1546 and ac.customization_code = l_criteria_tbl(l_index).customization_code
1547 and ac.region_application_id = l_criteria_tbl(l_index).region_application_id
1549 and ac.sequence_number = l_criteria_tbl(l_index).sequence_number
1550 and ac.attribute_application_id = l_criteria_tbl(l_index).attribute_application_id
1551 and ac.attribute_code = l_criteria_tbl(l_index).attribute_code;
1552 /*if ( l_user_id1 = 1 or l_user_id1 = 2 ) then*/
1553 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
1554 p_loader_timestamp => p_loader_timestamp,
1555 p_created_by => l_criteria_tbl(l_index).created_by,
1556 p_creation_date => l_criteria_tbl(l_index).creation_date,
1557 p_last_updated_by => l_criteria_tbl(l_index).last_updated_by,
1558 p_db_last_updated_by => l_user_id1,
1559 p_last_update_date => l_criteria_tbl(l_index).last_update_date,
1560 p_db_last_update_date => l_update1,
1561 p_last_update_login => l_criteria_tbl(l_index).last_update_login,
1562 p_create_or_update => 'UPDATE') then
1563
1564 AK_CUSTOM_PVT.UPDATE_CRITERIA (
1565 p_validation_level => p_validation_level,
1566 p_api_version_number => 1.0,
1567 p_msg_count => l_msg_count,
1568 p_msg_data => l_msg_data,
1569 p_return_status => l_return_status,
1570 p_custom_appl_id => l_criteria_tbl(l_index).customization_application_id,
1571 p_custom_code => l_criteria_tbl(l_index).customization_code,
1572 p_region_application_id => l_criteria_tbl(l_index).region_application_id,
1573 p_region_code => l_criteria_tbl(l_index).region_code,
1574 p_attribute_appl_id => l_criteria_tbl(l_index).attribute_application_id,
1575 p_attribute_code => l_criteria_tbl(l_index).attribute_code,
1576 p_sequence_number => l_criteria_tbl(l_index).sequence_number,
1577 p_operation => l_criteria_tbl(l_index).operation,
1578 p_value_varchar2 => l_criteria_tbl(l_index).value_varchar2,
1579 p_value_number => l_criteria_tbl(l_index).value_number,
1580 p_value_date => l_criteria_tbl(l_index).value_date,
1581 p_start_date_active => l_criteria_tbl(l_index).start_date_active,
1582 p_end_date_active => l_criteria_tbl(l_index).end_date_active,
1583 p_created_by => l_criteria_tbl(l_index).created_by,
1584 p_creation_date => l_criteria_tbl(l_index).creation_date,
1585 p_last_updated_by => l_criteria_tbl(l_index).last_updated_by,
1586 p_last_update_date => l_criteria_tbl(l_index).last_update_date,
1587 p_last_update_login => l_criteria_tbl(l_index).last_update_login,
1588 p_loader_timestamp => p_loader_timestamp,
1589 p_pass => p_pass,
1590 p_copy_redo_flag => l_copy_redo_flag
1591 );
1592 end if; -- /* if ( l_user_id1 = 1 and l_user_id2 = 1 ) */
1593 end if; -- /* if G_UPDATE_MODE G_NC_UPDATE_MODE*/
1594 else
1595 AK_CUSTOM_PVT.CREATE_CRITERIA (
1596 p_validation_level => p_validation_level,
1597 p_api_version_number => 1.0,
1598 p_msg_count => l_msg_count,
1599 p_msg_data => l_msg_data,
1600 p_return_status => l_return_status,
1601 p_custom_appl_id => l_criteria_tbl(l_index).customization_application_id,
1602 p_custom_code => l_criteria_tbl(l_index).customization_code,
1603 p_region_appl_id => l_criteria_tbl(l_index).region_application_id,
1604 p_region_code => l_criteria_tbl(l_index).region_code,
1605 p_attr_appl_id => l_criteria_tbl(l_index).attribute_application_id,
1606 p_attr_code => l_criteria_tbl(l_index).attribute_code,
1607 p_sequence_number => l_criteria_tbl(l_index).sequence_number,
1608 p_operation => l_criteria_tbl(l_index).operation,
1609 p_value_varchar2 => l_criteria_tbl(l_index).value_varchar2,
1610 p_value_number => l_criteria_tbl(l_index).value_number,
1611 p_value_date => l_criteria_tbl(l_index).value_date,
1612 p_start_date_active => l_criteria_tbl(l_index).start_date_active,
1613 p_end_date_active => l_criteria_tbl(l_index).end_date_active,
1614 p_created_by => l_criteria_tbl(l_index).created_by,
1615 p_creation_date => l_criteria_tbl(l_index).creation_date,
1616 p_last_updated_by => l_criteria_tbl(l_index).last_updated_by,
1617 p_last_update_date => l_criteria_tbl(l_index).last_update_date,
1618 p_last_update_login => l_criteria_tbl(l_index).last_update_login,
1619 p_loader_timestamp => p_loader_timestamp,
1620 p_pass => p_pass,
1621 p_copy_redo_flag => l_copy_redo_flag
1622 );
1623 end if; -- /* if CUST_REGION_EXISTS */
1624 --
1625 -- If API call returns with an error status, upload aborts
1626 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1627 (l_return_status = FND_API.G_RET_STS_ERROR) then
1628 RAISE FND_API.G_EXC_ERROR;
1629 end if; -- /* if l_return_status */
1630 --
1631 -- if validation fails, then this record should go to second pas
1632 if (l_copy_redo_flag) then
1633 G_CRITERIA_REDO_INDEX := G_CRITERIA_REDO_INDEX + 1;
1634 G_CRITERIA_REDO_TBL(G_CRITERIA_REDO_INDEX) := l_criteria_tbl(l_index);
1635 l_copy_redo_flag := FALSE;
1636 end if; --/* if l_copy_redo_flag */
1640
1637 end if;
1638 end loop;
1639 end if;
1641 --
1642 -- Load line number of the last file line processed
1643 --
1644 p_line_num_out := l_line_num;
1645
1646 p_return_status := FND_API.G_RET_STS_SUCCESS;
1647
1648 --dbms_output.put_line('Leaving region upload: ' ||
1649 -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
1650
1651 EXCEPTION
1652 WHEN FND_API.G_EXC_ERROR THEN
1653 p_return_status := FND_API.G_RET_STS_ERROR;
1654 rollback to Start_Upload;
1655 WHEN VALUE_ERROR THEN
1656 p_return_status := FND_API.G_RET_STS_ERROR;
1657 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_VALUE_ERROR');
1658 FND_MESSAGE.SET_TOKEN('KEY',to_char(l_custom_rec.region_appl_id)||' '||
1659 l_custom_rec.region_code);
1660 FND_MSG_PUB.Add;
1661 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1662 SUBSTR (SQLERRM, 1, 240)||': '||l_column||'='||l_token );
1663 FND_MSG_PUB.Add;
1664 WHEN OTHERS THEN
1665 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1666 rollback to Start_Upload;
1667 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1668 SUBSTR (SQLERRM, 1, 240) );
1669 FND_MSG_PUB.Add;
1670 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_VALUE_ERROR');
1671 FND_MESSAGE.SET_TOKEN('KEY', l_error_key_info);
1672 FND_MSG_PUB.Add;
1673 end UPLOAD_CUSTOM;
1674
1675 --=======================================================
1676 -- Procedure UPLOAD_CUSTOM_SECOND
1677 --
1678 -- Usage Private API for loading customizations that were
1679 -- failed during its first pass
1680 -- This API should only be called by other APIs
1681 -- that are owned by the Core Modules Team (AK).
1682 --
1683 -- Desc This API reads the custom data from PL/SQL table
1684 -- that was prepared during 1st pass, then processes
1685 -- the data, and loads them to the database. The tables
1686 -- are updated with the timestamp passed. This API
1687 -- will process the file until the EOF is reached,
1688 -- a parse error is encountered, or when data for
1689 -- a different business object is read from the file.
1690 --
1691 -- Results The API returns the standard p_return_status parameter
1692 -- indicating one of the standard return statuses :
1693 -- * Unexpected error
1694 -- * Error
1695 -- * Success
1696 -- Parameters p_validation_level : IN required
1697 -- validation level
1698 --
1699 -- Version Initial version number = 1.0
1700 -- History Current version number = 1.0
1701 --=======================================================
1702 procedure UPLOAD_CUSTOM_SECOND (
1703 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1704 p_return_status OUT NOCOPY VARCHAR2,
1705 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
1706 p_pass IN NUMBER := 2
1707 ) is
1708 l_api_name CONSTANT varchar2(30) := 'Upload_Custom_Second';
1709 l_rec_index NUMBER;
1710 l_return_status VARCHAR2(1);
1711 l_msg_count NUMBER;
1712 l_msg_data VARCHAR2(240);
1713 l_copy_redo_flag BOOLEAN;
1714 l_user_id1 NUMBER;
1715 l_user_id2 NUMBER;
1716 l_update1 DATE;
1717 l_update2 DATE;
1718 begin
1719 --
1720 -- create or update all customizations to the database
1721 --
1722 if (G_CUSTOM_REDO_INDEX > 0) then
1723 for l_index in G_CUSTOM_REDO_TBL.FIRST .. G_CUSTOM_REDO_TBL.LAST loop
1724 if (G_CUSTOM_REDO_TBL.exists(l_index)) then
1725 if AK_CUSTOM_PVT.CUSTOM_EXISTS (
1726 p_api_version_number => 1.0,
1727 p_return_status => l_return_status,
1728 p_custom_appl_id => G_CUSTOM_REDO_TBL(l_index).customization_appl_id,
1729 p_custom_code => G_CUSTOM_REDO_TBL(l_index).customization_code,
1730 p_region_application_id =>
1731 G_CUSTOM_REDO_TBL(l_index).region_appl_id,
1732 p_region_code => G_CUSTOM_REDO_TBL(l_index).region_code) then
1733 AK_CUSTOM_PVT.UPDATE_CUSTOM (
1734 p_validation_level => p_validation_level,
1735 p_api_version_number => 1.0,
1736 p_msg_count => l_msg_count,
1737 p_msg_data => l_msg_data,
1738 p_return_status => l_return_status,
1739 p_custom_appl_id => G_CUSTOM_REDO_TBL(l_index).customization_appl_id,
1740 p_custom_code => G_CUSTOM_REDO_TBL(l_index).customization_code,
1741 p_region_application_id => G_CUSTOM_REDO_TBL(l_index).region_appl_id,
1742 p_region_code => G_CUSTOM_REDO_TBL(l_index).region_code,
1743 p_verticalization_id => G_CUSTOM_REDO_TBL(l_index).verticalization_id,
1744 p_localization_code => G_CUSTOM_REDO_TBL(l_index).localization_code,
1745 p_org_id => G_CUSTOM_REDO_TBL(l_index).org_id,
1746 p_site_id => G_CUSTOM_REDO_TBL(l_index).site_id,
1747 p_responsibility_id => G_CUSTOM_REDO_TBL(l_index).responsibility_id,
1748 p_web_user_id => G_CUSTOM_REDO_TBL(l_index).web_user_id,
1749 p_default_customization_flag => G_CUSTOM_REDO_TBL(l_index).default_customization_flag,
1750 p_customization_level_id => G_CUSTOM_REDO_TBL(l_index).customization_level_id,
1751 p_developer_mode => G_CUSTOM_REDO_TBL(l_index).developer_mode,
1752 p_reference_path => G_CUSTOM_REDO_TBL(l_index).reference_path,
1753 p_function_name => G_CUSTOM_REDO_TBL(l_index).function_name,
1754 p_start_date_active => G_CUSTOM_REDO_TBL(l_index).start_date_active,
1758 p_created_by => G_CUSTOM_REDO_TBL(l_index).created_by,
1755 p_end_date_active => G_CUSTOM_REDO_TBL(l_index).end_date_active,
1756 p_name => G_CUSTOM_REDO_TBL(l_index).name,
1757 p_description => G_CUSTOM_REDO_TBL(l_index).description,
1759 p_creation_date => G_CUSTOM_REDO_TBL(l_index).creation_date,
1760 p_last_updated_by => G_CUSTOM_REDO_TBL(l_index).last_updated_by,
1761 p_last_update_date => G_CUSTOM_REDO_TBL(l_index).last_update_date,
1762 p_last_update_login => G_CUSTOM_REDO_TBL(l_index).last_update_login,
1763 p_loader_timestamp => p_loader_timestamp,
1764 p_pass => p_pass,
1765 p_copy_redo_flag => l_copy_redo_flag
1766 );
1767 else
1768 AK_CUSTOM_PVT.CREATE_CUSTOM (
1769 p_validation_level => p_validation_level,
1770 p_api_version_number => 1.0,
1771 p_msg_count => l_msg_count,
1772 p_msg_data => l_msg_data,
1773 p_return_status => l_return_status,
1774 p_custom_appl_id => G_CUSTOM_REDO_TBL(l_index).customization_appl_id,
1775 p_custom_code => G_CUSTOM_REDO_TBL(l_index).customization_code,
1776 p_region_appl_id => G_CUSTOM_REDO_TBL(l_index).region_appl_id,
1777 p_region_code => G_CUSTOM_REDO_TBL(l_index).region_code,
1778 p_verticalization_id => G_CUSTOM_REDO_TBL(l_index).verticalization_id,
1779 p_localization_code => G_CUSTOM_REDO_TBL(l_index).localization_code,
1780 p_org_id => G_CUSTOM_REDO_TBL(l_index).org_id,
1781 p_site_id => G_CUSTOM_REDO_TBL(l_index).site_id,
1782 p_responsibility_id => G_CUSTOM_REDO_TBL(l_index).responsibility_id,
1783 p_web_user_id => G_CUSTOM_REDO_TBL(l_index).web_user_id,
1784 p_default_customization_flag => G_CUSTOM_REDO_TBL(l_index).default_customization_flag,
1785 p_customization_level_id => G_CUSTOM_REDO_TBL(l_index).customization_level_id,
1786 p_developer_mode => G_CUSTOM_REDO_TBL(l_index).developer_mode,
1787 p_reference_path => G_CUSTOM_REDO_TBL(l_index).reference_path,
1788 p_function_name => G_CUSTOM_REDO_TBL(l_index).function_name,
1789 p_start_date_active => G_CUSTOM_REDO_TBL(l_index).start_date_active,
1790 p_end_date_active => G_CUSTOM_REDO_TBL(l_index).end_date_active,
1791 p_name => G_CUSTOM_REDO_TBL(l_index).name,
1792 p_description => G_CUSTOM_REDO_TBL(l_index).description,
1793 p_created_by => G_CUSTOM_REDO_TBL(l_index).created_by,
1794 p_creation_date => G_CUSTOM_REDO_TBL(l_index).creation_date,
1795 p_last_updated_by => G_CUSTOM_REDO_TBL(l_index).lasT_updated_by,
1796 p_last_update_date => G_CUSTOM_REDO_TBL(l_index).last_update_date,
1797 p_last_update_login => G_CUSTOM_REDO_TBL(l_index).last_update_login,
1798 p_loader_timestamp => p_loader_timestamp,
1799 p_pass => p_pass,
1800 p_copy_redo_flag => l_copy_redo_flag
1801 );
1802 end if; -- /* if CUSTOM_EXISTS */
1803 --
1804 -- If API call returns with an error status, upload aborts
1805 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1806 (l_return_status = FND_API.G_RET_STS_ERROR) then
1807 RAISE FND_API.G_EXC_ERROR;
1808 end if; -- /* if l_return_status */
1809 end if; -- /* if G_CUSTOM_REDO_TBL.exists(l_index) */
1810 end loop;
1811 G_CUSTOM_REDO_INDEX := 0;
1812 G_CUSTOM_REDO_TBL.DELETE;
1813 end if; -- /* if G_CUSTOM_REDO_INDEX > 0 */
1814
1815 --
1816 -- create or update all custom_regions to the database
1817 --
1818 if (G_CUST_REGION_REDO_INDEX > 0) then
1819 for l_index in G_CUST_REGION_REDO_TBL.FIRST .. G_CUST_REGION_REDO_TBL.LAST loop
1820 if (G_CUST_REGION_REDO_TBL.exists(l_index)) then
1821 if AK_CUSTOM_PVT.CUST_REGION_EXISTS (
1822 p_api_version_number => 1.0,
1823 p_return_status => l_return_status,
1824 p_custom_appl_id => G_CUST_REGION_REDO_TBL(l_index).customization_appl_id,
1825 p_custom_code => G_CUST_REGION_REDO_TBL(l_index).customization_code,
1826 p_region_application_id =>
1827 G_CUST_REGION_REDO_TBL(l_index).region_appl_id,
1828 p_region_code => G_CUST_REGION_REDO_TBL(l_index).region_code,
1829 p_property_name => G_CUST_REGION_REDO_TBL(l_index).property_name) then
1830 --
1831 -- Update Custom Regions only if G_UPDATE_MODE is TRUE
1832 --
1833 if (AK_UPLOAD_GRP.G_UPDATE_MODE) then
1834 AK_CUSTOM_PVT.UPDATE_CUST_REGION (
1835 p_validation_level => p_validation_level,
1836 p_api_version_number => 1.0,
1837 p_msg_count => l_msg_count,
1838 p_msg_data => l_msg_data,
1839 p_return_status => l_return_status,
1840 p_custom_appl_id => G_CUST_REGION_REDO_TBL(l_index).customization_appl_id,
1841 p_custom_code => G_CUST_REGION_REDO_TBL(l_index).customization_code,
1842 p_region_application_id => G_CUST_REGION_REDO_TBL(l_index).region_appl_id,
1843 p_region_code => G_CUST_REGION_REDO_TBL(l_index).region_code,
1844 p_property_name => G_CUST_REGION_REDO_TBL(l_index).property_name,
1845 p_property_varchar2_value => G_CUST_REGION_REDO_TBL(l_index).property_varchar2_value,
1846 p_property_number_value => G_CUST_REGION_REDO_TBL(l_index).property_number_value,
1847 p_criteria_join_condition => G_CUST_REGION_REDO_TBL(l_index).criteria_join_condition,
1848 p_property_varchar2_value_tl => G_CUST_REGION_REDO_TBL(l_index).property_varchar2_value_tl,
1849 p_created_by => G_CUST_REGION_REDO_TBL(l_index).created_by,
1850 p_creation_date => G_CUST_REGION_REDO_TBL(l_index).creation_date,
1851 p_last_updated_by => G_CUST_REGION_REDO_TBL(l_index).last_updated_by,
1852 p_last_update_date => G_CUST_REGION_REDO_TBL(l_index).last_update_date,
1856 p_copy_redo_flag => l_copy_redo_flag
1853 p_last_update_login => G_CUST_REGION_REDO_TBL(l_index).last_update_login,
1854 p_loader_timestamp => p_loader_timestamp,
1855 p_pass => p_pass,
1857 );
1858 elsif (AK_UPLOAD_GRP.G_NO_CUSTOM_UPDATE) then
1859 -- do not update customized data
1860 select ac.last_updated_by, act.last_updated_by,
1861 ac.last_update_date, act.last_update_date
1862 into l_user_id1, l_user_id2, l_update1, l_update2
1863 from ak_custom_regions ac, ak_custom_regions_tl act
1864 where ac.customization_application_id = G_CUST_REGION_REDO_TBL(l_index).customization_appl_id
1865 and ac.customization_code = G_CUST_REGION_REDO_TBL(l_index).customization_code
1866 and ac.region_application_id = G_CUST_REGION_REDO_TBL(l_index).region_appl_id
1867 and ac.region_code = G_CUST_REGION_REDO_TBL(l_index).region_code
1868 and ac.property_name = G_CUST_REGION_REDO_TBL(l_index).property_name
1869 and ac.customization_application_id = act.customization_application_id
1870 and ac.customization_code = act.customization_code
1871 and ac.region_application_id = act.region_application_id
1872 and ac.region_code = act.region_code
1873 and act.language = userenv('LANG');
1874 /*if (( l_user_id1 = 1 or l_user_id1 = 2 ) and
1875 ( l_user_id2 = 1 or l_user_id2 = 2 )) then*/
1876 if (AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
1877 p_loader_timestamp => p_loader_timestamp,
1878 p_created_by => G_CUST_REGION_REDO_TBL(l_index).created_by,
1879 p_creation_date => G_CUST_REGION_REDO_TBL(l_index).creation_date,
1880 p_last_updated_by => G_CUST_REGION_REDO_TBL(l_index).last_updated_by,
1881 p_db_last_updated_by => l_user_id1,
1882 p_last_update_date => G_CUST_REGION_REDO_TBL(l_index).last_update_date,
1883 p_db_last_update_date => l_update1,
1884 p_last_update_login => G_CUST_REGION_REDO_TBL(l_index).last_update_login,
1885 p_create_or_update => 'UPDATE') and
1886
1887 AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
1888 p_loader_timestamp => p_loader_timestamp,
1889 p_created_by => G_CUST_REGION_REDO_TBL(l_index).created_by,
1890 p_creation_date => G_CUST_REGION_REDO_TBL(l_index).creation_date,
1891 p_last_updated_by => G_CUST_REGION_REDO_TBL(l_index).last_updated_by,
1892 p_db_last_updated_by => l_user_id2,
1893 p_last_update_date => G_CUST_REGION_REDO_TBL(l_index).last_update_date,
1894 p_db_last_update_date => l_update2,
1895 p_last_update_login => G_CUST_REGION_REDO_TBL(l_index).last_update_login,
1896 p_create_or_update => 'UPDATE')) then
1897
1898 AK_CUSTOM_PVT.UPDATE_CUST_REGION (
1899 p_validation_level => p_validation_level,
1900 p_api_version_number => 1.0,
1901 p_msg_count => l_msg_count,
1902 p_msg_data => l_msg_data,
1903 p_return_status => l_return_status,
1904 p_custom_appl_id => G_CUST_REGION_REDO_TBL(l_index).customization_appl_id,
1905 p_custom_code => G_CUST_REGION_REDO_TBL(l_index).customization_code,
1906 p_region_application_id => G_CUST_REGION_REDO_TBL(l_index).region_appl_id,
1907 p_region_code => G_CUST_REGION_REDO_TBL(l_index).region_code,
1908 p_property_name => G_CUST_REGION_REDO_TBL(l_index).property_name,
1909 p_property_varchar2_value => G_CUST_REGION_REDO_TBL(l_index).property_varchar2_value,
1910 p_property_number_value => G_CUST_REGION_REDO_TBL(l_index).property_number_value,
1911 p_criteria_join_condition => G_CUST_REGION_REDO_TBL(l_index).criteria_join_condition,
1912 p_property_varchar2_value_tl => G_CUST_REGION_REDO_TBL(l_index).property_varchar2_value_tl,
1913 p_created_by => G_CUST_REGION_REDO_TBL(l_index).created_by,
1914 p_creation_date => G_CUST_REGION_REDO_TBL(l_index).creation_date,
1915 p_last_updated_by => G_CUST_REGION_REDO_TBL(l_index).last_updated_by,
1916 p_last_update_date => G_CUST_REGION_REDO_TBL(l_index).last_update_date,
1917 p_last_update_login => G_CUST_REGION_REDO_TBL(l_index).last_update_login,
1918 p_loader_timestamp => p_loader_timestamp,
1919 p_pass => p_pass,
1920 p_copy_redo_flag => l_copy_redo_flag
1921 );
1922 end if; -- /* if ( l_user_id1 = 1 and l_user_id2 = 1 ) */
1923 end if; -- /* if G_UPDATE_MODE G_NC_UPDATE_MODE*/
1924 else
1925 AK_CUSTOM_PVT.CREATE_CUST_REGION (
1926 p_validation_level => p_validation_level,
1927 p_api_version_number => 1.0,
1928 p_msg_count => l_msg_count,
1929 p_msg_data => l_msg_data,
1930 p_return_status => l_return_status,
1931 p_custom_appl_id => G_CUST_REGION_REDO_TBL(l_index).customization_appl_id,
1932 p_custom_code => G_CUST_REGION_REDO_TBL(l_index).customization_code,
1933 p_region_appl_id => G_CUST_REGION_REDO_TBL(l_index).region_appl_id,
1934 p_region_code => G_CUST_REGION_REDO_TBL(l_index).region_code,
1935 p_property_name => G_CUST_REGION_REDO_TBL(l_index).property_name,
1936 p_property_varchar2_value => G_CUST_REGION_REDO_TBL(l_index).property_varchar2_value,
1937 p_property_number_value => G_CUST_REGION_REDO_TBL(l_index).property_number_value,
1941 p_creation_date => G_CUST_REGION_REDO_TBL(l_index).creation_date,
1938 p_criteria_join_condition => G_CUST_REGION_REDO_TBL(l_index).criteria_join_condition,
1939 p_property_varchar2_value_tl => G_CUST_REGION_REDO_TBL(l_index).property_varchar2_value_tl,
1940 p_created_by => G_CUST_REGION_REDO_TBL(l_index).created_by,
1942 p_last_updated_by => G_CUST_REGION_REDO_TBL(l_index).last_updated_by,
1943 p_last_update_date => G_CUST_REGION_REDO_TBL(l_index).last_update_date,
1944 p_last_update_login => G_CUST_REGION_REDO_TBL(l_index).last_update_login,
1945 p_loader_timestamp => p_loader_timestamp,
1946 p_pass => p_pass,
1947 p_copy_redo_flag => l_copy_redo_flag
1948 );
1949 end if; -- /* if CUST_REGION_EXISTS */
1950 --
1951 -- If API call returns with an error status, upload aborts
1952 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1953 (l_return_status = FND_API.G_RET_STS_ERROR) then
1954 RAISE FND_API.G_EXC_ERROR;
1955 end if; -- /* if l_return_status */
1956 end if; -- /* if G_CUST_REGION_REDO_TBL.exists */
1957 end loop;
1958 G_CUST_REGION_REDO_INDEX := 0;
1959 G_CUST_REGION_REDO_TBL.DELETE;
1960 end if;
1961 --
1962 -- create or update all custom_region_items to the database
1963 --
1964 if (G_CUST_REG_ITEM_REDO_INDEX > 0) then
1965 for l_index in G_CUST_REG_ITEM_REDO_TBL.FIRST .. G_CUST_REG_ITEM_REDO_TBL.LAST
1966 loop
1967 if (G_CUST_REG_ITEM_REDO_TBL.exists(l_index)) then
1968 if AK_CUSTOM_PVT.CUST_REG_ITEM_EXISTS (
1969 p_api_version_number => 1.0,
1970 p_return_status => l_return_status,
1971 p_custom_appl_id => G_CUST_REG_ITEM_REDO_TBL(l_index).customization_appl_id,
1972 p_custom_code => G_CUST_REG_ITEM_REDO_TBL(l_index).customization_code,
1973 p_region_application_id => G_CUST_REG_ITEM_REDO_TBL(l_index).region_appl_id,
1974 p_region_code => G_CUST_REG_ITEM_REDO_TBL(l_index).region_code,
1975 p_attribute_appl_id => G_CUST_REG_ITEM_REDO_TBL(l_index).attr_appl_id,
1976 p_attribute_code => G_CUST_REG_ITEM_REDO_TBL(l_index).attr_code,
1977 p_property_name => G_CUST_REG_ITEM_REDO_TBL(l_index).property_name)
1978 then
1979 AK_CUSTOM_PVT.UPDATE_CUST_REG_ITEM (
1980 p_validation_level => p_validation_level,
1981 p_api_version_number => 1.0,
1982 p_msg_count => l_msg_count,
1983 p_msg_data => l_msg_data,
1984 p_return_status => l_return_status,
1985 p_custom_appl_id => G_CUST_REG_ITEM_REDO_TBL(l_index).customization_appl_id,
1986 p_custom_code => G_CUST_REG_ITEM_REDO_TBL(l_index).customization_code,
1987 p_region_application_id => G_CUST_REG_ITEM_REDO_TBL(l_index).region_appl_id,
1988 p_region_code => G_CUST_REG_ITEM_REDO_TBL(l_index).region_code,
1989 p_attribute_appl_id => G_CUST_REG_ITEM_REDO_TBL(l_index).attr_appl_id,
1990 p_attribute_code => G_CUST_REG_ITEM_REDO_TBL(l_index).attr_code,
1991 p_property_name => G_CUST_REG_ITEM_REDO_TBL(l_index).property_name,
1992 p_property_varchar2_value => G_CUST_REG_ITEM_REDO_TBL(l_index).property_varchar2_value,
1993 p_property_number_value => G_CUST_REG_ITEM_REDO_TBL(l_index).property_number_value,
1994 p_property_date_value => G_CUST_REG_ITEM_REDO_TBL(l_index).property_date_value,
1995 p_property_varchar2_value_tl => G_CUST_REG_ITEM_REDO_TBL(l_index).property_varchar2_value_tl,
1996 p_created_by => G_CUST_REG_ITEM_REDO_TBL(l_index).created_by,
1997 p_creation_date => G_CUST_REG_ITEM_REDO_TBL(l_index).creation_date,
1998 p_last_updated_by => G_CUST_REG_ITEM_REDO_TBL(l_index).last_updated_by,
1999 p_last_update_date => G_CUST_REG_ITEM_REDO_TBL(l_index).last_update_date,
2000 p_last_update_login => G_CUST_REG_ITEM_REDO_TBL(l_index).last_update_login,
2001 p_loader_timestamp => p_loader_timestamp,
2002 p_pass => p_pass,
2003 p_copy_redo_flag => l_copy_redo_flag
2004 );
2005 else
2006 AK_CUSTOM_PVT.CREATE_CUST_REG_ITEM (
2007 p_validation_level => p_validation_level,
2008 p_api_version_number => 1.0,
2009 p_msg_count => l_msg_count,
2010 p_msg_data => l_msg_data,
2011 p_return_status => l_return_status,
2012 p_custom_appl_id => G_CUST_REG_ITEM_REDO_TBL(l_index).customization_appl_id,
2013 p_custom_code => G_CUST_REG_ITEM_REDO_TBL(l_index).customization_code,
2014 p_region_appl_id => G_CUST_REG_ITEM_REDO_TBL(l_index).region_appl_id,
2015 p_region_code => G_CUST_REG_ITEM_REDO_TBL(l_index).region_code,
2016 p_attr_appl_id => G_CUST_REG_ITEM_REDO_TBL(l_index).attr_appl_id,
2017 p_attr_code => G_CUST_REG_ITEM_REDO_TBL(l_index).attr_code,
2018 p_property_name => G_CUST_REG_ITEM_REDO_TBL(l_index).property_name,
2019 p_property_varchar2_value => G_CUST_REG_ITEM_REDO_TBL(l_index).property_varchar2_value,
2020 p_property_number_value => G_CUST_REG_ITEM_REDO_TBL(l_index).property_number_value,
2021 p_property_date_value => G_CUST_REG_ITEM_REDO_TBL(l_index).property_date_value,
2022 p_property_varchar2_value_tl => G_CUST_REG_ITEM_REDO_TBL(l_index).property_varchar2_value_tl,
2023 p_created_by => G_CUST_REG_ITEM_REDO_TBL(l_index).created_by,
2024 p_creation_date => G_CUST_REG_ITEM_REDO_TBL(l_index).creation_date,
2025 p_last_updated_by => G_CUST_REG_ITEM_REDO_TBL(l_index).last_updateD_by,
2026 p_last_update_date => G_CUST_REG_ITEM_REDO_TBL(l_index).last_update_date,
2027 p_last_update_login => G_CUST_REG_ITEM_REDO_TBL(l_index).last_update_login,
2028 p_loader_timestamp => p_loader_timestamp,
2029 p_pass => p_pass,
2030 p_copy_redo_flag => l_copy_redo_flag
2031 );
2032 end if; -- /* if CUST_REGION_EXISTS */
2033 --
2034 -- If API call returns with an error status, upload aborts
2038 end if; -- /* if l_return_status */
2035 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2036 (l_return_status = FND_API.G_RET_STS_ERROR) then
2037 RAISE FND_API.G_EXC_ERROR;
2039 end if; -- /* if G_CUST_REG_ITEM_REDO_TBL.exists(l_index) */
2040 end loop;
2041 G_CUST_REG_ITEM_REDO_INDEX := 0;
2042 G_CUST_REG_ITEM_REDO_TBL.DELETE;
2043 end if; -- /* if G_CUST_REG_ITEM_REDO_INDEX > 0 */
2044
2045 --
2046 -- create or update all custom_criteria to the database
2047 --
2048 if (G_CRITERIA_REDO_INDEX > 0) then
2049 for l_index in G_CRITERIA_REDO_TBL.FIRST .. G_CRITERIA_REDO_TBL.LAST
2050 loop
2051 if (G_CRITERIA_REDO_TBL.exists(l_index)) then
2052 if AK_CUSTOM_PVT.CRITERIA_EXISTS (
2053 p_api_version_number => 1.0,
2054 p_return_status => l_return_status,
2055 p_custom_appl_id => G_CRITERIA_REDO_TBL(l_index).customization_application_id,
2056 p_custom_code => G_CRITERIA_REDO_TBL(l_index).customization_code,
2057 p_region_application_id => G_CRITERIA_REDO_TBL(l_index).region_application_id,
2058 p_region_code => G_CRITERIA_REDO_TBL(l_index).region_code,
2059 p_attribute_appl_id => G_CRITERIA_REDO_TBL(l_index).attribute_application_id,
2060 p_attribute_code => G_CRITERIA_REDO_TBL(l_index).attribute_code,
2061 p_sequence_number => G_CRITERIA_REDO_TBL(l_index).sequence_number) then
2062 AK_CUSTOM_PVT.UPDATE_CRITERIA (
2063 p_validation_level => p_validation_level,
2064 p_api_version_number => 1.0,
2065 p_msg_count => l_msg_count,
2066 p_msg_data => l_msg_data,
2067 p_return_status => l_return_status,
2068 p_custom_appl_id => G_CRITERIA_REDO_TBL(l_index).customization_application_id,
2069 p_custom_code => G_CRITERIA_REDO_TBL(l_index).customization_code,
2070 p_region_application_id => G_CRITERIA_REDO_TBL(l_index).region_application_id,
2071 p_region_code => G_CRITERIA_REDO_TBL(l_index).region_code,
2072 p_attribute_appl_id => G_CRITERIA_REDO_TBL(l_index).attribute_application_id,
2073 p_attribute_code => G_CRITERIA_REDO_TBL(l_index).attribute_code,
2074 p_sequence_number => G_CRITERIA_REDO_TBL(l_index).sequence_number,
2075 p_operation => G_CRITERIA_REDO_TBL(l_index).operation,
2076 p_value_varchar2 => G_CRITERIA_REDO_TBL(l_index).value_varchar2,
2077 p_value_number => G_CRITERIA_REDO_TBL(l_index).value_number,
2078 p_value_date => G_CRITERIA_REDO_TBL(l_index).value_date,
2079 p_start_date_active => G_CRITERIA_REDO_TBL(l_index).start_date_active,
2080 p_end_date_active => G_CRITERIA_REDO_TBL(l_index).end_date_active,
2081 p_created_by => G_CRITERIA_REDO_TBL(l_index).created_by,
2082 p_creation_date => G_CRITERIA_REDO_TBL(l_index).creation_date,
2083 p_last_updated_by => G_CRITERIA_REDO_TBL(l_index).last_updated_by,
2084 p_last_update_date => G_CRITERIA_REDO_TBL(l_index).last_update_date,
2085 p_last_update_login => G_CRITERIA_REDO_TBL(l_index).last_update_login,
2086 p_loader_timestamp => p_loader_timestamp,
2087 p_pass => p_pass,
2088 p_copy_redo_flag => l_copy_redo_flag
2089 );
2090 else
2091 AK_CUSTOM_PVT.CREATE_CRITERIA (
2092 p_validation_level => p_validation_level,
2093 p_api_version_number => 1.0,
2094 p_msg_count => l_msg_count,
2095 p_msg_data => l_msg_data,
2096 p_return_status => l_return_status,
2097 p_custom_appl_id => G_CRITERIA_REDO_TBL(l_index).customization_application_id,
2098 p_custom_code => G_CRITERIA_REDO_TBL(l_index).customization_code,
2099 p_region_appl_id => G_CRITERIA_REDO_TBL(l_index).region_application_id,
2100 p_region_code => G_CRITERIA_REDO_TBL(l_index).region_code,
2101 p_attr_appl_id => G_CRITERIA_REDO_TBL(l_index).attribute_application_id,
2102 p_attr_code => G_CRITERIA_REDO_TBL(l_index).attribute_code,
2103 p_sequence_number => G_CRITERIA_REDO_TBL(l_index).sequence_number,
2104 p_operation => G_CRITERIA_REDO_TBL(l_index).operation,
2105 p_value_varchar2 => G_CRITERIA_REDO_TBL(l_index).value_varchar2,
2106 p_value_number => G_CRITERIA_REDO_TBL(l_index).value_number,
2107 p_value_date => G_CRITERIA_REDO_TBL(l_index).value_date,
2108 p_start_date_active => G_CRITERIA_REDO_TBL(l_index).start_date_active,
2109 p_end_date_active => G_CRITERIA_REDO_TBL(l_index).end_date_active,
2110 p_created_by => G_CRITERIA_REDO_TBL(l_index).created_by,
2111 p_creation_date => G_CRITERIA_REDO_TBL(l_index).creation_date,
2112 p_last_updated_by => G_CRITERIA_REDO_TBL(l_index).last_updated_by,
2113 p_last_update_date => G_CRITERIA_REDO_TBL(l_index).last_update_date,
2114 p_last_update_login => G_CRITERIA_REDO_TBL(l_index).last_update_login,
2115 p_loader_timestamp => p_loader_timestamp,
2116 p_pass => p_pass,
2117 p_copy_redo_flag => l_copy_redo_flag
2118 );
2119 end if; -- /* if CUST_REGION_EXISTS */
2120 --
2121 -- If API call returns with an error status, upload aborts
2122 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2123 (l_return_status = FND_API.G_RET_STS_ERROR) then
2124 RAISE FND_API.G_EXC_ERROR;
2125 end if; -- /* if l_return_status */
2126 end if; -- /* if G_CRITERIA_REDO_TBL.exists(l_index) */
2127 end loop;
2128 G_CRITERIA_REDO_INDEX := 0;
2129 G_CRITERIA_REDO_TBL.DELETE;
2130 end if; -- /* if G_CRITERIA_REDO_INDEX > 0 */
2131
2132 p_return_status := FND_API.G_RET_STS_SUCCESS;
2133
2134 EXCEPTION
2135 WHEN FND_API.G_EXC_ERROR THEN
2136 p_return_status := FND_API.G_RET_STS_ERROR;
2137 FND_MSG_PUB.Count_And_Get (
2138 p_count => l_msg_count,
2139 p_data => l_msg_data);
2140 G_CUSTOM_REDO_INDEX := 0;
2141 G_CUST_REGION_REDO_INDEX := 0;
2142 G_CUST_REG_ITEM_REDO_INDEX := 0;
2143 G_CRITERIA_REDO_INDEX := 0;
2144 G_CUSTOM_REDO_TBL.DELETE;
2145 G_CUST_REGION_REDO_TBL.DELETE;
2146 G_CUST_REG_ITEM_REDO_TBL.DELETE;
2147 G_CRITERIA_REDO_TBL.DELETE;
2148 WHEN OTHERS THEN
2149 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2150 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2151 SUBSTR (SQLERRM, 1, 240) );
2152 FND_MSG_PUB.Count_And_Get (
2153 p_count => l_msg_count,
2154 p_data => l_msg_data);
2155 G_CUSTOM_REDO_INDEX := 0;
2156 G_CUST_REGION_REDO_INDEX := 0;
2157 G_CUST_REG_ITEM_REDO_INDEX := 0;
2158 G_CRITERIA_REDO_INDEX := 0;
2159 G_CUSTOM_REDO_TBL.DELETE;
2160 G_CUST_REGION_REDO_TBL.DELETE;
2161 G_CUST_REG_ITEM_REDO_TBL.DELETE;
2162 G_CRITERIA_REDO_TBL.DELETE;
2163 end UPLOAD_CUSTOM_SECOND;
2164
2165 end AK_CUSTOM2_PVT;