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