[Home] [Help]
PACKAGE BODY: APPS.AMS_IMPORT_LIST_PVT
Source
1 PACKAGE BODY AMS_Import_List_PVT as
2 /* $Header: amsvimpb.pls 120.2 2005/12/29 20:12:59 ryedator noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Import_List_PVT
7 -- Purpose
8 --
9 -- History
10 -- 09-May-2002 HUILI added code into the "Create_Import_List" not to pick up
11 -- the list import id if the value is passed.
12 -- 12-June-2002 HUILI pick up new batch id for the "Duplicate_Import_List" module.
13 -- 18-JUNE-2002 huili added the "RECORD_UPDATE_FLAG" and "ERROR_THRESHOLD" to
14 -- the create and update module.
15 -- 08-JULY-2002 huili added the export concurrent program submission.
16 --
17 -- NOTE
18 --
19 -- End of Comments
20 -- ===============================================================
21
22
23 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Import_List_PVT';
24 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvimpb.pls';
25
26 G_ARC_IMPORT_HEADER CONSTANT VARCHAR2(30) := 'IMPH';
27 G_ARC_EXPORT_HEADER CONSTANT VARCHAR2(30) := 'EXPH';
28 G_APP_NAME CONSTANT VARCHAR2(10) := 'AMS';
29 G_IMPORT_CONCURRENT_PROGRAM CONSTANT VARCHAR2(100) := 'AMSIMPREC';
30 G_EXPORT_CONCURRENT_PROGRAM CONSTANT VARCHAR2(100) := 'AMSEXPREC';
31 G_JAVA_CONCURRENT_PROGRAM CONSTANT VARCHAR2(100) := 'Java Concurrent Program';
32 G_PLSQL_CONCURRENT_PROGRAM CONSTANT VARCHAR2(100) := 'PL/SQL Stored Procedure';
33 G_JAVA_CON_PROG_PATH CONSTANT VARCHAR2(100) := 'oracle.apps.ams.list';
34 G_PLSQL_CON_PROG_PACKNAME CONSTANT VARCHAR2(100) := 'AMS_EXPORT_PVT';
35 G_IMPORT_JAVA_CON_PROG_NAME CONSTANT VARCHAR2(100) := 'ImportRepConCurrent';
36 G_EXPORT_PLSQL_CON_PROG_NAME CONSTANT VARCHAR2(100) := 'generate_xml_util';
37 G_IMP_REC_CON_PARA_NAME CONSTANT VARCHAR2(30) := 'P_STRIMPORTLISTHEADERID';
38 G_EXP_REC_CON_PARA_NAME CONSTANT VARCHAR2(30) := 'p_export_header_id';
39 G_IMP_REC_CON_SEQ CONSTANT NUMBER := 10;
40 G_DEFAULT_VALUE_SET CONSTANT VARCHAR2(60) := '30 Characters Optional';
41 G_DEFAULT_DISPLAY_SIZE CONSTANT NUMBER := 30;
42 G_REPEAT_NONE CONSTANT VARCHAR2(30) := 'NONE';
43 G_REPEAT_ASAP CONSTANT VARCHAR2(30) := 'ASAP';
44 G_REPEAT_ONCE CONSTANT VARCHAR2(30) := 'ONCE';
45 G_REPEAT_PERIODICALLY CONSTANT VARCHAR2(30) := 'PERIODICALLY';
46 G_MSG_COUNT NUMBER := 10000;
47 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
48 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
49 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
50
51 PROCEDURE write_msg(p_message IN VARCHAR2)
52 IS
53
54 BEGIN
55 NULL;
56 --INSERT INTO ams_concurrent_test
57 --VALUES
58 --(G_MSG_COUNT||':'||DBMS_UTILITY.get_time ||':'||p_message);
59 -- G_MSG_COUNT := G_MSG_COUNT + 1;
60 --COMMIT;
61 END;
62
63 /*
64 PROCEDURE Do_Recurring (
65 p_api_version_number IN NUMBER,
66 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
67 p_commit IN VARCHAR2 := FND_API.G_FALSE,
68 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
69
70 x_return_status OUT NOCOPY VARCHAR2,
71 x_msg_count OUT NOCOPY NUMBER,
72 x_msg_data OUT NOCOPY VARCHAR2,
73
74 p_obj_id IN NUMBER,
75 p_repeat_mode IN VARCHAR2,
76 p_repeate_time IN VARCHAR2,
77 p_repeate_end_time IN VARCHAR2,
78 p_repeate_unit IN VARCHAR2,
79 p_repeate_interval IN NUMBER,
80 p_recur_type IN VARCHAR2)
81
82 IS
83 L_API_NAME CONSTANT VARCHAR2(30) := 'Do_Recurring';
84 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
85
86 l_con_program_name VARCHAR2(2000) := G_IMPORT_CONCURRENT_PROGRAM;
87 l_obj_type VARCHAR2(2000) := G_ARC_IMPORT_HEADER;
88 l_java_executable_name VARCHAR2(2000) := G_IMPORT_JAVA_CON_PROG_NAME;
89 l_parameter_name VARCHAR(2000) := G_IMP_REC_CON_PARA_NAME;
90 l_request_id NUMBER;
91 l_repeat_option_set BOOLEAN := TRUE;
92 l_repeat_mode VARCHAR2(2000) := UPPER (p_repeat_mode);
93
94 l_cancel_date DATE := NULL;
95 l_cancel_flag VARCHAR2(1) := 'N';
96 l_repeat_time VARCHAR2(2000) := NULL;
97 l_repeat_interval NUMBER := NULL;
98 l_repeat_unit VARCHAR2(2000) := NULL;
99 l_repeate_end_time VARCHAR2(2000) := NULL;
100 l_repeate_start_time VARCHAR2(2000) := NULL;
101
102 l_error VARCHAR2(2000) := '';
103
104 BEGIN
105
106 -- Standard Start of API savepoint
107 --SAVEPOINT Do_Recurring;
108
109 -- Standard call to check for call compatibility.
110 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
111 p_api_version_number,
112 l_api_name,
113 G_PKG_NAME) THEN
114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
115 END IF;
116
117 -- Initialize API return status to SUCCESS
118 x_return_status := FND_API.G_RET_STS_SUCCESS;
119
120 IF (AMS_DEBUG_HIGH_ON) THEN
121
122
123 NULL;
124 --AMS_UTILITY_PVT.debug_message('Do_Recurring::p_recur_type::::' || p_recur_type);
125
126 END IF;
127 write_msg('Do_Recurring::p_recur_type::::' || p_recur_type);
128
129 IF p_recur_type IS NOT NULL AND UPPER(p_recur_type) <> G_ARC_IMPORT_HEADER THEN
130 l_con_program_name := G_EXPORT_CONCURRENT_PROGRAM;
131 l_obj_type := G_ARC_EXPORT_HEADER;
132 l_java_executable_name := G_EXPORT_PLSQL_CON_PROG_NAME;
133 l_parameter_name := G_EXP_REC_CON_PARA_NAME;
134 END IF;
135 IF (AMS_DEBUG_HIGH_ON) THEN
136 NULL;
137 --AMS_UTILITY_PVT.debug_message('Do_Recurring::l_con_program_name::' || l_con_program_name
138 -- || ' l_obj_type::' || l_obj_type || ' l_java_executable_name::' || l_java_executable_name
139 -- || ' l_parameter_name::' || l_parameter_name);
140 END IF;
141
142 IF (AMS_DEBUG_HIGH_ON) THEN
143 NULL;
144
145
146 --AMS_UTILITY_PVT.debug_message('Do_Recurring::l_con_program_name::' || l_con_program_name
147 -- || ' l_obj_type::' || l_obj_type || ' l_java_executable_name::' || l_java_executable_name
148 -- || ' l_parameter_name::' || l_parameter_name);
149
150 END IF;
151 l_con_program_name := l_con_program_name || p_obj_id;
152
153 IF (AMS_DEBUG_HIGH_ON) THEN
154
155
156
157 --AMS_UTILITY_PVT.debug_message('Do_Recurring::l_con_program_name::' || l_con_program_name);
158 NULL;
159 END IF;
160 write_msg('Do_Recurring::l_con_program_name::' || l_con_program_name);
161 --
162 --clean up the program and executable if any
163 --
164 FND_PROGRAM.DELETE_PROGRAM (
165 program_short_name => l_con_program_name,
166 application => G_APP_NAME
167 );
168
169 IF (AMS_DEBUG_HIGH_ON) THEN
170
171
172 NULL;
173 --AMS_UTILITY_PVT.debug_message('Do_Recurring::test 1');
174
175 END IF;
176 write_msg('Do_Recurring::test 1');
177 FND_PROGRAM.DELETE_EXECUTABLE (
178 executable_short_name => l_con_program_name,
179 application => G_APP_NAME
180 );
181 IF (AMS_DEBUG_HIGH_ON) THEN
182 NULL;
183 --AMS_UTILITY_PVT.debug_message('Do_Recurring::test 2');
184 END IF;
185 write_msg('Do_Recurring::test 2');
186
187 --
188 --update the headers table, need to add the export stuff
189 --
190 IF l_repeat_mode = G_REPEAT_NONE THEN --cancel
191 l_cancel_date := SYSDATE;
192 l_cancel_flag := 'Y';
193 ELSIF l_repeat_mode = G_REPEAT_ASAP THEN --'ASAP'
194 l_repeat_time := SYSDATE;
195 ELSIF l_repeat_mode = G_REPEAT_ONCE THEN --'ONCE'
196 l_repeat_time := p_repeate_time;
197 ELSIF l_repeat_mode = G_REPEAT_PERIODICALLY THEN --'PERIODICALLY'
198 l_repeat_time := p_repeate_time;
199 l_repeat_interval := p_repeate_interval;
200 l_repeat_unit := p_repeate_unit;
201 l_repeate_end_time := p_repeate_end_time;
202 l_repeate_start_time := p_repeate_time;
203 END IF;
204
205 IF p_recur_type IS NOT NULL AND UPPER(p_recur_type) = G_ARC_IMPORT_HEADER THEN --import
206 IF (AMS_DEBUG_HIGH_ON) THEN
207
208 --AMS_UTILITY_PVT.debug_message('Do_Recurring::test 2 ::l_cancel_date::' || l_cancel_date
209 -- || ' l_cancel_flag::' || l_cancel_flag || ' l_repeat_time::' || l_repeat_time
210 -- || ' l_repeat_interval::' || l_repeat_interval || ' l_repeat_unit::' || l_repeat_unit
211 -- || ' l_repeate_end_time::' || l_repeate_end_time || ' l_repeate_start_time::' || l_repeate_start_time);
212 NULL;
213 END IF;
214 UPDATE AMS_IMP_LIST_HEADERS_ALL
215 SET REPEAT_MODE = l_repeat_mode, CANCEL_DATE = l_cancel_date,
216 CANCEL_FLAG = l_cancel_flag, REPEAT_TIME = l_repeat_time,
217 REPEAT_INTERVAL = l_repeat_interval, REPEAT_UNIT = l_repeat_unit,
218 REPEAT_END_TIME = l_repeate_end_time, REPEAT_START_TIME = l_repeate_start_time
219 WHERE IMPORT_LIST_HEADER_ID = p_obj_id;
220 ELSE
221 UPDATE AMS_EXP_LIST_HEADERS_ALL
222 SET REPEAT_MODE = l_repeat_mode, CANCEL_DATE = l_cancel_date,
223 CANCEL_FLAG = l_cancel_flag, REPEAT_TIME = l_repeat_time,
224 REPEAT_INTERVAL = l_repeat_interval, REPEAT_UNIT = l_repeat_unit,
225 REPEAT_END_TIME = l_repeate_end_time, REPEAT_START_TIME = l_repeate_start_time
226 WHERE EXPORT_LIST_HEADER_ID = p_obj_id;
227 END IF;
228
229 AMS_Utility_PVT.Create_Log (
230 x_return_status => x_return_status,
231 p_arc_log_used_by => l_obj_type,
232 p_log_used_by_id => p_obj_id,
233 p_msg_data => 'After deleting executable:' || l_con_program_name,
234 p_msg_type => 'DEBUG'
235 );
236
237 IF l_repeat_mode IS NOT NULL AND l_repeat_mode <> G_REPEAT_NONE THEN
238 IF (AMS_DEBUG_HIGH_ON) THEN
239 NULL;
240 --AMS_UTILITY_PVT.debug_message('Do_Recurring::test 4');
241 END IF;
242 write_msg('Do_Recurring::test 4');
243 -- Create the Executable entry.
244 IF p_recur_type IS NOT NULL AND UPPER(p_recur_type) = G_ARC_IMPORT_HEADER THEN
245 FND_PROGRAM.EXECUTABLE (
246 executable => l_con_program_name,
247 application => G_APP_NAME,
248 short_name => l_con_program_name,
249 description => p_obj_id,
250 execution_method => G_JAVA_CONCURRENT_PROGRAM,
251 execution_file_name => l_java_executable_name,
252 language_code => USERENV ('LANG'),
253 execution_file_path => G_JAVA_CON_PROG_PATH
254 );
255 ELSE
256 FND_PROGRAM.EXECUTABLE (
257 executable => l_con_program_name,
258 application => G_APP_NAME,
259 short_name => l_con_program_name,
260 description => p_obj_id,
261 execution_method => G_PLSQL_CONCURRENT_PROGRAM,
262 execution_file_name => G_PLSQL_CON_PROG_PACKNAME || '.' || G_EXPORT_PLSQL_CON_PROG_NAME,
263 language_code => USERENV ('LANG'));
264 END IF;
265 IF (AMS_DEBUG_HIGH_ON) THEN
266 NULL;
267 --AMS_UTILITY_PVT.debug_message('Do_Recurring::test 5');
268 END IF;
269 write_msg('Do_Recurring::test 5');
270 AMS_Utility_PVT.Create_Log (
271 x_return_status => x_return_status,
272 p_arc_log_used_by => l_obj_type,
273 p_log_used_by_id => p_obj_id,
274 p_msg_data => 'Executable:' || l_con_program_name || ' is created successfully.',
275 p_msg_type => 'DEBUG'
276 );
277
278 IF (AMS_DEBUG_HIGH_ON) THEN
279
280
281
282 --AMS_UTILITY_PVT.debug_message('Do_Recurring::test 6');
283 NULL;
284 END IF;
285 write_msg('Do_Recurring::test 6');
286 --
287 -- Register the concurrent program.
288 FND_PROGRAM.REGISTER (
289 program => l_con_program_name,
290 application => G_APP_NAME,
291 enabled => 'Y',
292 short_name => l_con_program_name,
293 executable_short_name => l_con_program_name,
294 executable_application => G_APP_NAME,
295 language_code => USERENV ('LANG'),
296 use_in_srs => 'Y'
297 );
298
299 IF (AMS_DEBUG_HIGH_ON) THEN
300
301
302
303 --AMS_UTILITY_PVT.debug_message('Do_Recurring::test 7::l_con_program_name::' || l_con_program_name
304 -- || ' G_APP_NAME::' || G_APP_NAME || ' G_IMP_REC_CON_SEQ::' || G_IMP_REC_CON_SEQ
305 -- || ' l_parameter_name::' || l_parameter_name || ' G_DEFAULT_VALUE_SET::' || G_DEFAULT_VALUE_SET
306 -- || ' G_DEFAULT_DISPLAY_SIZE::' || G_DEFAULT_DISPLAY_SIZE );
307 NULL;
308 END IF;
309
310 write_msg('Do_Recurring::test 7::l_con_program_name::' || l_con_program_name
311 || ' G_APP_NAME::' || G_APP_NAME || ' G_IMP_REC_CON_SEQ::' || G_IMP_REC_CON_SEQ
312 || ' l_parameter_name::' || l_parameter_name || ' G_DEFAULT_VALUE_SET::' || G_DEFAULT_VALUE_SET
313 || ' G_DEFAULT_DISPLAY_SIZE::' || G_DEFAULT_DISPLAY_SIZE );
314
315 AMS_Utility_PVT.Create_Log (
316 x_return_status => x_return_status,
317 p_arc_log_used_by => l_obj_type,
318 p_log_used_by_id => p_obj_id,
319 p_msg_data => l_con_program_name || ' is registered successfully.',
320 p_msg_type => 'DEBUG'
321 );
322
323 IF p_recur_type IS NOT NULL AND UPPER(p_recur_type) = G_ARC_IMPORT_HEADER THEN
324 FND_PROGRAM.parameter(
325 program_short_name => l_con_program_name,
326 application => G_APP_NAME,
327 sequence => G_IMP_REC_CON_SEQ,
328 parameter => l_parameter_name,
329 value_set => G_DEFAULT_VALUE_SET,
330 display_size => G_DEFAULT_DISPLAY_SIZE,
331 description_size => G_DEFAULT_DISPLAY_SIZE,
332 concatenated_description_size => G_DEFAULT_DISPLAY_SIZE,
333 token => l_parameter_name,
334 prompt => l_parameter_name);
335 ELSE
336 FND_PROGRAM.parameter(
337 program_short_name => l_con_program_name,
338 application => G_APP_NAME,
339 sequence => G_IMP_REC_CON_SEQ,
340 parameter => l_parameter_name,
341 value_set => G_DEFAULT_VALUE_SET,
342 display_size => G_DEFAULT_DISPLAY_SIZE,
343 description_size => G_DEFAULT_DISPLAY_SIZE,
344 concatenated_description_size => G_DEFAULT_DISPLAY_SIZE,
345 prompt => l_parameter_name);
346 END IF;
347
348 IF (AMS_DEBUG_HIGH_ON) THEN
349
350
351
352 --AMS_UTILITY_PVT.debug_message('Do_Recurring::test 8:l_repeat_mode::' || l_repeat_mode);
353 NULL;
354 END IF;
355 write_msg('Do_Recurring::test 8:l_repeat_mode::' || l_repeat_mode);
356
357 AMS_Utility_PVT.Create_Log (
358 x_return_status => x_return_status,
359 p_arc_log_used_by => l_obj_type,
360 p_log_used_by_id => p_obj_id,
364
361 p_msg_data => l_con_program_name || ' parameters are set successfully.',
362 p_msg_type => 'DEBUG'
363 );
365 IF l_repeat_mode = G_REPEAT_ASAP THEN
366 write_msg('Do_Recurring::test 801 -1::' || TO_CHAR(SYSDATE, 'HH24:MI:SS') );
367 --l_repeat_option_set := FND_REQUEST.set_repeat_options (repeat_time => TO_CHAR(SYSDATE, 'HH24:MI:SS'));
368 ELSIF l_repeat_mode = G_REPEAT_ONCE THEN
369 write_msg('Do_Recurring::test 802 -1:p_repeate_time' || p_repeate_time);
370 l_repeat_option_set := FND_REQUEST.set_repeat_options (repeat_time => p_repeate_time);
371 ELSIF l_repeat_mode = G_REPEAT_PERIODICALLY THEN
372 write_msg('Do_Recurring::test 803 - 02:p_repeate_time' || p_repeate_time
373 || ' p_repeate_interval::' || p_repeate_interval || ' p_repeate_unit::' || p_repeate_unit
374 || ' p_repeate_end_time::' || p_repeate_end_time);
375 l_repeat_option_set := FND_REQUEST.set_repeat_options (
376 repeat_end_time => p_repeate_end_time,
377 repeat_interval => p_repeate_interval,
378 repeat_unit => p_repeate_unit);
379 END IF;
380
381 IF (AMS_DEBUG_HIGH_ON) THEN
382
383
384
385 --AMS_UTILITY_PVT.debug_message('Do_Recurring::test 9');
386 NULL;
387 END IF;
388 write_msg('Do_Recurring::test 9');
389
390 IF l_repeat_option_set THEN
391 IF (AMS_DEBUG_HIGH_ON) THEN
392 NULL;
393 --AMS_UTILITY_PVT.debug_message('Do_Recurring::success in set_repeat_options');
394 END IF;
395 write_msg('Do_Recurring::success in set_repeat_options');
396 AMS_Utility_PVT.Create_Log (
397 x_return_status => x_return_status,
398 p_arc_log_used_by => l_obj_type,
399 p_log_used_by_id => p_obj_id,
400 p_msg_data => l_con_program_name || ' schedules are set successfully.',
401 p_msg_type => 'DEBUG'
402 );
403 ELSE
404 IF (AMS_DEBUG_HIGH_ON) THEN
405
406 AMS_UTILITY_PVT.debug_message('Do_Recurring::fails in set_repeat_options');
407 --AMS_UTILITY_PVT.debug_message('AMS_IMP_REC_SCH_ERROR');
408 END IF;
409 --write_msg('Do_Recurring::fails in set_repeat_options');
410 AMS_Utility_PVT.Create_Log (
411 x_return_status => x_return_status,
412 p_arc_log_used_by => l_obj_type,
413 p_log_used_by_id => p_obj_id,
414 p_msg_data => l_con_program_name || ' schedules fail.',
415 p_msg_type => 'DEBUG'
416 );
417 -- Initialize message list if p_init_msg_list is set to TRUE.
418 IF FND_API.to_Boolean( p_init_msg_list ) THEN
419 FND_MSG_PUB.initialize;
420 END IF;
421 AMS_UTILITY_PVT.debug_message('AMS_IMP_REC_SCH_ERROR');
422 RAISE FND_API.g_exc_unexpected_error;
423 END IF;
424
425 --
426 -- submit request
427 --
428 l_request_id := FND_REQUEST.SUBMIT_REQUEST (
429 application => G_APP_NAME,
430 program => l_con_program_name,
431 argument1 => p_obj_id);
432 IF (AMS_DEBUG_HIGH_ON) THEN
433 NULL;
434 --AMS_UTILITY_PVT.debug_message('Do_Recurring::test 10::l_request_id:: '
435 -- || l_request_id);
436 END IF;
437 write_msg('Do_Recurring::test 10::l_request_id:: '
438 || l_request_id);
439 IF l_request_id = 0 THEN
440 IF (AMS_DEBUG_HIGH_ON) THEN
441 NULL;
442 --AMS_UTILITY_PVT.debug_message('Do_Recurring::test 11::l_request_id is 0:: ');
443 END IF;
444 RAISE FND_API.g_exc_unexpected_error;
445 ELSE
446 IF (AMS_DEBUG_HIGH_ON) THEN
447 NULL;
448 --AMS_UTILITY_PVT.debug_message('Do_Recurring::test 11::l_request_id is not 0:: ');
449 END IF;
450 Ams_Utility_PVT.Create_Log (
451 x_return_status => x_return_status,
452 p_arc_log_used_by => l_obj_type,
453 p_log_used_by_id => p_obj_id,
454 p_msg_data => 'Can not submit:' || l_con_program_name,
455 p_msg_type => 'MILESTONE'
456 );
457 END IF;
458 END IF;
459 EXCEPTION
460
461 WHEN FND_API.G_EXC_ERROR THEN
462 x_return_status := FND_API.G_RET_STS_ERROR;
463 FND_MSG_PUB.Count_And_Get (
464 p_encoded => FND_API.G_FALSE,
465 p_count => x_msg_count,
466 p_data => x_msg_data
467 );
468
469 WHEN FND_API.g_exc_unexpected_error THEN
470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
471
472 -- Standard call to get message count and if count=1, get the message
473 FND_MSG_PUB.Count_And_Get (
474 p_encoded => FND_API.G_FALSE,
475 p_count => x_msg_count,
476 p_data => x_msg_data
477 );
478
479 WHEN OTHERS THEN
480 l_error := SQLERRM;
481 IF (AMS_DEBUG_HIGH_ON) THEN
482
483 AMS_UTILITY_PVT.debug_message('Do_Recurring::The error is:: ' || l_error);
484 END IF;
485 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
486 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
487 THEN
488 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
489 END IF;
490 -- Standard call to get message count and if count=1, get the message
491 FND_MSG_PUB.Count_And_Get (
492 p_encoded => FND_API.G_FALSE,
493 p_count => x_msg_count,
494 p_data => x_msg_data
495 );
496 END Do_Recurring;
497
498 */
499
503 p_commit IN VARCHAR2 := FND_API.G_FALSE,
500 PROCEDURE Duplicate_Import_List (
501 p_api_version_number IN NUMBER,
502 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
504 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
505
506 x_return_status OUT NOCOPY VARCHAR2,
507 x_msg_count OUT NOCOPY NUMBER,
508 x_msg_data OUT NOCOPY VARCHAR2,
509
510 p_import_list_header_id IN NUMBER,
511 x_ams_import_rec OUT NOCOPY ams_import_rec_type,
512 x_file_type OUT NOCOPY VARCHAR2)
513 IS
514 L_API_NAME CONSTANT VARCHAR2(30) := 'Duplicate_Import_List';
515 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
516
517 CURSOR c_list_import_rec (p_import_list_header_id NUMBER) IS
518 SELECT import_list_header_id,
519 last_update_date,
520 last_updated_by,
521 creation_date,
522 created_by,
523 last_update_login,
524 object_version_number,
525 view_application_id,
526 name,
527 version,
528 import_type,
529 owner_user_id,
530 list_source_type_id,
531 status_code,
532 status_date,
533 user_status_id,
534 source_system,
535 vendor_id,
536 pin_id,
537 org_id,
538 scheduled_time,
539 null,--loaded_no_of_rows,
540 loaded_date,
541 rows_to_skip,
542 processed_rows,
543 headings_flag,
544 expiry_date,
545 purge_date,
546 description,
547 keywords,
548 transactional_cost,
549 transactional_currency_code,
550 functional_cost,
551 functional_currency_code,
552 terminated_by,
553 enclosed_by,
554 data_filename,
555 process_immed_flag,
556 dedupe_flag,
557 attribute_category,
558 attribute1,
559 attribute2,
560 attribute3,
561 attribute4,
562 attribute5,
563 attribute6,
564 attribute7,
565 attribute8,
566 attribute9,
567 attribute10,
568 attribute11,
569 attribute12,
570 attribute13,
571 attribute14,
572 attribute15,
573 custom_setup_id,
574 country,
575 usage,
576 number_of_records,
577 data_file_name,
578 b2b_flag,
579 rented_list_flag,
580 server_flag,
581 log_file_name,
582 null,--number_of_failed_records,
583 null,--number_of_duplicate_records,
584 enable_word_replacement_flag,
585 validate_file,
586 server_name,
587 user_name,
588 password,
589 upload_flag,
590 parent_imp_header_id,
591 record_update_flag,
592 error_threshold,
593 charset
594 FROM AMS_IMP_LIST_HEADERS_ALL
595 WHERE IMPORT_LIST_HEADER_ID = p_import_list_header_id;
596
597 l_current_date DATE := SYSDATE;
598
599 -- SOLIN, SQL repository
600 CURSOR c_get_status_id(c_status_type VARCHAR2, c_status_code VARCHAR2,
601 c_flag VARCHAR2) IS
602 SELECT user_status_id
603 FROM ams_user_statuses_b
604 WHERE system_status_type = c_status_type -- 'AMS_IMPORT_STATUS'
605 AND system_status_code = c_status_code -- 'NEW'
606 AND default_flag = c_flag; -- 'Y';
607
608
609 CURSOR c_get_file_type (p_import_header_id NUMBER) IS
610 SELECT FILE_TYPE
611 FROM AMS_IMP_DOCUMENTS
612 WHERE IMPORT_LIST_HEADER_ID = p_import_header_id;
613
614 l_status_id c_get_status_id%ROWTYPE;
615
616 BEGIN
617 -- Standard Start of API savepoint
618 SAVEPOINT Duplicate_Import_List;
619
620 -- Standard call to check for call compatibility.
621 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
622 p_api_version_number,
623 l_api_name,
624 G_PKG_NAME) THEN
625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
626 END IF;
627
628 -- Initialize API return status to SUCCESS
629 x_return_status := FND_API.G_RET_STS_SUCCESS;
630
631 OPEN c_list_import_rec (p_import_list_header_id);
632 FETCH c_list_import_rec INTO x_ams_import_rec;
633 CLOSE c_list_import_rec;
634
635 OPEN c_get_status_id('AMS_IMPORT_STATUS', 'NEW', 'Y');
636 FETCH c_get_status_id INTO l_status_id;
637 CLOSE c_get_status_id;
638
639 SELECT AMS_IMP_LIST_HEADERS_ALL_S.NEXTVAL INTO x_ams_import_rec.import_list_header_id
640 FROM DUAL;
641
642 AMS_Utility_PVT.Write_Conc_Log (' Start Duplicate_Import_List');
643
644 x_ams_import_rec.last_update_date := l_current_date;
645 x_ams_import_rec.creation_date := l_current_date;
646 x_ams_import_rec.name := x_ams_import_rec.name || x_ams_import_rec.import_list_header_id;
647 x_ams_import_rec.object_version_number := 1.0;
648 x_ams_import_rec.status_code := 'NEW';
649 x_ams_import_rec.status_date := l_current_date;
650 x_ams_import_rec.user_status_id := l_status_id.user_status_id;
651 x_ams_import_rec.scheduled_time := l_current_date;
652 x_ams_import_rec.loaded_date := l_current_date;
656 AMS_Utility_PVT.Write_Conc_Log (' Start Duplicate_Import_List before Get_DeEncrypt_String ');
653 x_ams_import_rec.expiry_date := l_current_date;
654 x_ams_import_rec.purge_date := l_current_date;
655 x_ams_import_rec.parent_imp_header_id := p_import_list_header_id;
657 IF (x_ams_import_rec.password IS NOT NULL AND LENGTH (x_ams_import_rec.password) > 0) THEN
658 x_ams_import_rec.password := AMS_Import_Security_PVT.Get_DeEncrypt_String (
659 p_input_string => x_ams_import_rec.password,
660 p_header_id => p_import_list_header_id,
661 p_encrypt_flag => FALSE);
662 END IF;
663 AMS_Utility_PVT.Write_Conc_Log (' Start Duplicate_Import_List after Get_DeEncrypt_String ');
664
665 Create_Import_List(
666 p_api_version_number => 1.0,
667 p_commit => FND_API.G_TRUE,
668 x_return_status => x_return_status,
669 x_msg_count => x_msg_count,
670 x_msg_data => x_msg_data,
671 p_ams_import_rec => x_ams_import_rec,
672 x_import_list_header_id => x_ams_import_rec.import_list_header_id);
673
674 AMS_Utility_PVT.Write_Conc_Log (' Start Duplicate_Import_List after Create_Import_List '
675 || ' the p_import_list_header_id::' || p_import_list_header_id
676 || ' and the x_ams_import_rec.import_list_header_id::'
677 || x_ams_import_rec.import_list_header_id);
678
679 update AMS_IMP_LIST_HEADERS_ALL
680 set ( generate_list, number_of_instances)=
681 (select generate_list, number_of_instances
682 from AMS_IMP_LIST_HEADERS_ALL
683 where import_list_header_id = p_import_list_header_id),
684 generated_list_name = name
685 where import_list_header_id = x_ams_import_rec.import_list_header_id;
686
687 AMS_Utility_PVT.Write_Conc_Log (' Start Duplicate_Import_List after update AMS_IMP_LIST_HEADERS_ALL ');
688
689 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
690 RAISE FND_API.G_EXC_ERROR;
691 END IF;
692
693 AMS_Utility_PVT.Write_Conc_Log (' Start Duplicate_Import_List 4444444 ');
694 OPEN c_get_file_type (p_import_list_header_id);
695 FETCH c_get_file_type INTO x_file_type;
696 CLOSE c_get_file_type;
697
698 AMS_Utility_PVT.Write_Conc_Log (' Start Duplicate_Import_List 4444444 after c_get_file_type ');
699
700 -- Standard check for p_commit
701 IF FND_API.to_Boolean( p_commit) THEN
702 COMMIT WORK;
703 END IF;
704
705 -- Standard call to get message count and if count is 1, get message info.
706 FND_MSG_PUB.Count_And_Get
707 (p_count => x_msg_count,
708 p_data => x_msg_data);
709
710 EXCEPTION
711 WHEN AMS_Utility_PVT.resource_locked THEN
712 x_return_status := FND_API.g_ret_sts_error;
713 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
714 WHEN FND_API.G_EXC_ERROR THEN
715 ROLLBACK TO Duplicate_Import_List;
716 x_return_status := FND_API.G_RET_STS_ERROR;
717 -- Standard call to get message count and if count=1, get the message
718 FND_MSG_PUB.Count_And_Get (
719 p_encoded => FND_API.G_FALSE,
720 p_count => x_msg_count,
721 p_data => x_msg_data
722 );
723
724 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
725 ROLLBACK TO Duplicate_Import_List;
726 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
727 -- Standard call to get message count and if count=1, get the message
728 FND_MSG_PUB.Count_And_Get (
729 p_encoded => FND_API.G_FALSE,
730 p_count => x_msg_count,
731 p_data => x_msg_data
732 );
733
734 WHEN OTHERS THEN
735 ROLLBACK TO Duplicate_Import_List;
736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
737 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
738 THEN
739 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
740 END IF;
741 -- Standard call to get message count and if count=1, get the message
742 FND_MSG_PUB.Count_And_Get (
743 p_encoded => FND_API.G_FALSE,
744 p_count => x_msg_count,
745 p_data => x_msg_data
746 );
747 END Duplicate_Import_List;
748
749
750 -- Hint: Primary key needs to be returned.
751 PROCEDURE Create_Import_List(
752 p_api_version_number IN NUMBER,
753 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
754 p_commit IN VARCHAR2 := FND_API.G_FALSE,
755 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
756
757 x_return_status OUT NOCOPY VARCHAR2,
758 x_msg_count OUT NOCOPY NUMBER,
759 x_msg_data OUT NOCOPY VARCHAR2,
760
761 p_ams_import_rec IN ams_import_rec_type := g_miss_ams_import_rec,
762 x_import_list_header_id OUT NOCOPY NUMBER
763 )
764
765 IS
766 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Import_List';
767 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
768 l_return_status_full VARCHAR2(1);
769 l_object_version_number NUMBER := 1;
770 l_org_id NUMBER := FND_API.G_MISS_NUM;
771 l_IMPORT_LIST_HEADER_ID NUMBER;
772 l_dummy NUMBER;
773 l_country NUMBER;
777
774 l_terminated_by VARCHAR2(30) := p_ams_import_rec.terminated_by;
775 l_enclosed_by VARCHAR2(30) := p_ams_import_rec.enclosed_by;
776 l_batch_id NUMBER := FND_API.G_MISS_NUM;
778 l_encrpted_password VARCHAR2 (2000);
779
780 CURSOR c_id IS
781 SELECT AMS_IMP_LIST_HEADERS_ALL_s.NEXTVAL
782 FROM dual;
783
784 CURSOR c_id_exists (l_id IN NUMBER) IS
785 SELECT 1
786 FROM AMS_IMP_LIST_HEADERS_ALL
787 WHERE IMPORT_LIST_HEADER_ID = l_id;
788
789 CURSOR c_get_record (l_id IN NUMBER) IS
790 SELECT version, object_version_number, status_code, status_date,
791 user_status_id, country, validate_file
792 FROM AMS_IMP_LIST_HEADERS_ALL
793 WHERE IMPORT_LIST_HEADER_ID = l_id;
794
795 -- SOLIN, SQL repository
796 CURSOR c_get_status_id(c_status_type VARCHAR2, c_status_code VARCHAR2,
797 c_flag VARCHAR2) IS
798 SELECT user_status_id
799 FROM ams_user_statuses_b
800 WHERE system_status_type = c_status_type -- 'AMS_IMPORT_STATUS'
801 AND system_status_code = c_status_code -- 'NEW'
802 AND default_flag = c_flag; -- 'Y';
803
804 l_user_status_id NUMBER;
805
806
807 -- SOLIN
808 l_return_status VARCHAR2(30);
809 l_ams_import_rec ams_import_rec_type := g_miss_ams_import_rec;
810 BEGIN
811 -- Standard Start of API savepoint
812 SAVEPOINT CREATE_Import_List_PVT;
813
814
815 -- Standard call to check for call compatibility.
816
817 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
818 p_api_version_number,
819 l_api_name,
820 G_PKG_NAME)
821 THEN
822 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
823 END IF;
824
825
826 -- Initialize message list if p_init_msg_list is set to TRUE.
827 IF FND_API.to_Boolean( p_init_msg_list )
828 THEN
829 FND_MSG_PUB.initialize;
830 END IF;
831
832 -- Debug Message
833 IF (AMS_DEBUG_HIGH_ON) THEN
834
835 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
836 END IF;
837
838
839 -- Initialize API return status to SUCCESS
840 x_return_status := FND_API.G_RET_STS_SUCCESS;
841
842 -- Local variable initialization
843 OPEN c_get_status_id('AMS_IMPORT_STATUS', 'NEW', 'Y');
844 FETCH c_get_status_id INTO l_user_status_id;
845 CLOSE c_get_status_id;
846
847 IF p_ams_import_rec.IMPORT_LIST_HEADER_ID IS NULL OR p_ams_import_rec.IMPORT_LIST_HEADER_ID = FND_API.g_miss_num THEN
848 LOOP
849 l_dummy := NULL;
850 OPEN c_id;
851 FETCH c_id INTO l_IMPORT_LIST_HEADER_ID;
852 CLOSE c_id;
853 ams_utility_pvt.create_log(l_return_status, 'IMPH', l_IMPORT_LIST_HEADER_ID, 'Get ID from sequence ' || l_IMPORT_LIST_HEADER_ID);
854
855 OPEN c_id_exists(l_IMPORT_LIST_HEADER_ID);
856 FETCH c_id_exists INTO l_dummy;
857 CLOSE c_id_exists;
858 EXIT WHEN l_dummy IS NULL;
859 END LOOP;
860 ELSE
861 l_IMPORT_LIST_HEADER_ID := p_ams_import_rec.IMPORT_LIST_HEADER_ID;
862 -- SOLIN, bug 4377876
863 -- If the record exists, call update API.
864 ams_utility_pvt.create_log(l_return_status, 'IMPH',
865 l_IMPORT_LIST_HEADER_ID, 'Get ID from passed in value ' || l_IMPORT_LIST_HEADER_ID);
866 l_dummy := null;
867 OPEN c_id_exists(l_IMPORT_LIST_HEADER_ID);
868 FETCH c_id_exists INTO l_dummy;
869 CLOSE c_id_exists;
870 IF l_dummy IS NOT NULL
871 THEN
872 l_ams_import_rec := p_ams_import_rec;
873 OPEN c_get_record(l_IMPORT_LIST_HEADER_ID);
874 FETCH c_get_record INTO l_ams_import_rec.version,
875 l_ams_import_rec.object_version_number,
876 l_ams_import_rec.status_code,
877 l_ams_import_rec.status_date,
878 l_ams_import_rec.user_status_id,
879 l_ams_import_rec.country,
880 l_ams_import_rec.validate_file;
881 CLOSE c_get_record;
882 l_encrpted_password := p_ams_import_rec.password;
883
884 IF UPPER(LTRIM(RTRIM(l_encrpted_password))) = 'NULL' THEN
885 l_encrpted_password := NULL;
886 END IF;
887 IF l_encrpted_password IS NOT NULL
888 AND LENGTH(l_encrpted_password) > 0
889 THEN
890 l_encrpted_password := AMS_Import_Security_PVT.Get_DeEncrypt_String (
891 p_input_string => l_encrpted_password,
892 p_header_id => l_import_list_header_id,
893 p_encrypt_flag => TRUE);
894
895 l_ams_import_rec.password := l_encrpted_password;
896 END IF;
897 -- The record is already there, call update API.
898 Update_Import_List(
899 p_api_version_number => 1.0,
900 p_init_msg_list => FND_API.G_FALSE,
904 x_msg_count => x_msg_count,
901 p_commit => FND_API.G_FALSE,
902 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
903 x_return_status => x_return_status,
905 x_msg_data => x_msg_data,
906 p_ams_import_rec => l_ams_import_rec,
907 x_object_version_number => l_object_version_number);
908 x_import_list_header_id := l_IMPORT_LIST_HEADER_ID;
909 RETURN;
910 END IF;
911 -- SOLIN, end
912 END IF;
913
914 -- initialize any default values
915
916 IF p_ams_import_rec.country IS NULL OR p_ams_import_rec.country = FND_API.g_miss_num THEN
917 l_country := FND_PROFILE.value ('AMS_SRCGEN_USER_CITY');
918 END IF;
919
920
921
922 IF (AMS_DEBUG_HIGH_ON) THEN
923
924
925
926
927
928
929
930 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name);
931
932
933
934 END IF;
935
936 -- =========================================================================
937 -- Validate Environment
938 -- =========================================================================
939
940 IF FND_GLOBAL.User_Id IS NULL
941 THEN
942 AMS_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
943 RAISE FND_API.G_EXC_ERROR;
944 END IF;
945 IF (AMS_DEBUG_HIGH_ON) THEN
946
947 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name);
948 END IF;
949
950 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
951 THEN
952 -- Debug message
953 IF (AMS_DEBUG_HIGH_ON) THEN
954
955 AMS_UTILITY_PVT.debug_message('Private API: Validate_Import_List');
956 END IF;
957
958 -- Invoke validation procedures
959 Validate_import_list(
960 p_api_version_number => 1.0,
961 p_init_msg_list => FND_API.G_FALSE,
962 p_validation_level => p_validation_level,
963 p_ams_import_rec => p_ams_import_rec,
964 x_return_status => x_return_status,
965 x_msg_count => x_msg_count,
966 x_msg_data => x_msg_data);
967
968 END IF;
969
970 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
971 RAISE FND_API.G_EXC_ERROR;
972 END IF;
973 /*
974 -- translate single quote to the real one for terminated by
975 IF (p_ams_import_rec.terminated_by = 'SINGLEQUOTE') AND
976 (p_ams_import_rec.server_flag = 'N')
977 THEN
978 l_terminated_by := '''';
979 END IF;
980
981 IF (p_ams_import_rec.terminated_by = 'SINGLEQUOTE') AND
982 (p_ams_import_rec.server_flag = 'Y')
983 THEN
984 l_terminated_by := '\''';
985 END IF;
986
987 -- translate doulbe quote to the real one for terminated by
988 IF p_ams_import_rec.terminated_by = 'DOUBLEQUOTE'
989 THEN
990 l_terminated_by := '"';
991 END IF;
992
993 -- translate single quote to the real one for enclosed by
994 IF (p_ams_import_rec.enclosed_by = 'SINGLEQUOTE') AND
995 (p_ams_import_rec.server_flag = 'N')
996 THEN
997 l_enclosed_by := '''';
998 END IF;
999
1000 IF (p_ams_import_rec.enclosed_by = 'SINGLEQUOTE') AND
1001 (p_ams_import_rec.server_flag = 'Y')
1002 THEN
1003 l_enclosed_by := '\''';
1004 END IF;
1005
1006 -- translate doulbe quote to the real one for enclosed by
1007 IF p_ams_import_rec.enclosed_by = 'DOUBLEQUOTE'
1008 THEN
1009 l_enclosed_by := '"';
1010 END IF;
1011 */
1012
1013 -- insert batch id when import type is lead
1014 /*
1015 IF p_ams_import_rec.import_type = 'LEAD'
1016 THEN
1017 select as_import_interface_s.nextval into l_batch_id from dual;
1018 END IF;
1019 */
1020 -- insert batch id in any cases
1021 select as_import_interface_s.nextval into l_batch_id from dual;
1022
1023 -- Debug Message
1024 IF (AMS_DEBUG_HIGH_ON) THEN
1025
1026 AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
1027 END IF;
1028
1029 l_encrpted_password := p_ams_import_rec.password;
1030
1031 IF UPPER(LTRIM(RTRIM(l_encrpted_password))) = 'NULL' THEN
1032 l_encrpted_password := NULL;
1033 END IF;
1034
1035 -- Invoke table handler(AMS_IMP_LIST_HEADERS_PKG.Insert_Row)
1036 AMS_IMP_LIST_HEADERS_PKG.Insert_Row(
1037 px_import_list_header_id => l_import_list_header_id,
1038 p_last_update_date => SYSDATE,
1039 p_last_updated_by => FND_GLOBAL.USER_ID,
1040 p_creation_date => SYSDATE,
1041 p_created_by => FND_GLOBAL.USER_ID,
1042 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
1043 px_object_version_number => l_object_version_number,
1047 p_import_type => p_ams_import_rec.import_type,
1044 p_view_application_id => p_ams_import_rec.view_application_id,
1045 p_name => p_ams_import_rec.name,
1046 p_version => '1.0',--p_ams_import_rec.version,
1048 p_owner_user_id => p_ams_import_rec.owner_user_id,
1049 p_list_source_type_id => p_ams_import_rec.list_source_type_id,
1050 p_status_code => 'NEW',--p_ams_import_rec.status_code,
1051 p_status_date => sysdate,--p_ams_import_rec.status_date,
1052 p_user_status_id => l_user_status_id, --3001,--p_ams_import_rec.user_status_id,
1053 p_source_system => p_ams_import_rec.source_system,
1054 p_vendor_id => p_ams_import_rec.vendor_id,
1055 p_pin_id => p_ams_import_rec.pin_id,
1056 px_org_id => l_org_id,
1057 p_scheduled_time => p_ams_import_rec.scheduled_time,
1058 p_loaded_no_of_rows => p_ams_import_rec.loaded_no_of_rows,
1059 p_loaded_date => p_ams_import_rec.loaded_date,
1060 p_rows_to_skip => p_ams_import_rec.rows_to_skip,
1061 p_processed_rows => p_ams_import_rec.processed_rows,
1062 p_headings_flag => p_ams_import_rec.headings_flag,
1063 p_expiry_date => p_ams_import_rec.expiry_date,
1064 p_purge_date => p_ams_import_rec.purge_date,
1065 p_description => p_ams_import_rec.description,
1066 p_keywords => p_ams_import_rec.keywords,
1067 p_transactional_cost => p_ams_import_rec.transactional_cost,
1068 p_transactional_currency_code => p_ams_import_rec.transactional_currency_code,
1069 p_functional_cost => p_ams_import_rec.functional_cost,
1070 p_functional_currency_code => p_ams_import_rec.functional_currency_code,
1071 p_terminated_by => l_terminated_by,
1072 p_enclosed_by => l_enclosed_by,
1073 p_data_filename => p_ams_import_rec.data_filename,
1074 p_process_immed_flag => p_ams_import_rec.process_immed_flag,
1075 p_dedupe_flag => p_ams_import_rec.dedupe_flag,
1076 p_attribute_category => p_ams_import_rec.attribute_category,
1077 p_attribute1 => p_ams_import_rec.attribute1,
1078 p_attribute2 => p_ams_import_rec.attribute2,
1079 p_attribute3 => p_ams_import_rec.attribute3,
1080 p_attribute4 => p_ams_import_rec.attribute4,
1081 p_attribute5 => p_ams_import_rec.attribute5,
1082 p_attribute6 => p_ams_import_rec.attribute6,
1083 p_attribute7 => p_ams_import_rec.attribute7,
1084 p_attribute8 => p_ams_import_rec.attribute8,
1085 p_attribute9 => p_ams_import_rec.attribute9,
1086 p_attribute10 => p_ams_import_rec.attribute10,
1087 p_attribute11 => p_ams_import_rec.attribute11,
1088 p_attribute12 => p_ams_import_rec.attribute12,
1089 p_attribute13 => p_ams_import_rec.attribute13,
1090 p_attribute14 => p_ams_import_rec.attribute14,
1091 p_attribute15 => p_ams_import_rec.attribute15,
1092 p_custom_setup_id => p_ams_import_rec.custom_setup_id,
1093 p_country => l_country,
1094 p_usage => p_ams_import_rec.usage,
1095 p_number_of_records => p_ams_import_rec.number_of_records,
1096 p_data_file_name => p_ams_import_rec.data_file_name,
1097 p_b2b_flag => p_ams_import_rec.b2b_flag,
1098 p_rented_list_flag => p_ams_import_rec.rented_list_flag,
1099 p_server_flag => p_ams_import_rec.server_flag,
1100 p_log_file_name => p_ams_import_rec.log_file_name,
1101 p_number_of_failed_records => p_ams_import_rec.number_of_failed_records,
1102 p_number_of_duplicate_records => p_ams_import_rec.number_of_duplicate_records,
1103 p_enable_word_replacement_flag => p_ams_import_rec.enable_word_replacement_flag,
1104 p_batch_id => l_batch_id,
1105 p_server_name => p_ams_import_rec.server_name,
1106 p_user_name => p_ams_import_rec.user_name,
1107 p_password => l_encrpted_password, --p_ams_import_rec.password,
1108 p_upload_flag => p_ams_import_rec.upload_flag,
1109 p_parent_imp_header_id => p_ams_import_rec.parent_imp_header_id,
1110 p_record_update_flag => p_ams_import_rec.record_update_flag,
1111 p_error_threshold => p_ams_import_rec.error_threshold,
1112 p_charset => p_ams_import_rec.charset);
1113 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1114 RAISE FND_API.G_EXC_ERROR;
1115 END IF;
1116 x_import_list_header_id:=l_import_list_header_id;
1117
1118 IF l_encrpted_password IS NOT NULL AND LENGTH(l_encrpted_password) > 0 THEN
1119 l_encrpted_password := AMS_Import_Security_PVT.Get_DeEncrypt_String (
1120 p_input_string => l_encrpted_password,
1121 p_header_id => l_import_list_header_id,
1122 p_encrypt_flag => TRUE);
1123 UPDATE AMS_IMP_LIST_HEADERS_ALL
1124 SET PASSWORD = l_encrpted_password
1125 WHERE IMPORT_LIST_HEADER_ID = l_import_list_header_id;
1126 END IF;
1127
1128 -- End of API body
1129 --
1130
1131 -- Standard check for p_commit
1132 IF FND_API.to_Boolean( p_commit )
1133 THEN
1134 COMMIT WORK;
1135 END IF;
1136
1137
1138 -- Debug Message
1139 IF (AMS_DEBUG_HIGH_ON) THEN
1140
1141 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1142 END IF;
1143
1147 p_data => x_msg_data
1144 -- Standard call to get message count and if count is 1, get message info.
1145 FND_MSG_PUB.Count_And_Get
1146 (p_count => x_msg_count,
1148 );
1149 EXCEPTION
1150
1151 WHEN AMS_Utility_PVT.resource_locked THEN
1152 x_return_status := FND_API.g_ret_sts_error;
1153 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1154
1155 WHEN FND_API.G_EXC_ERROR THEN
1156 ROLLBACK TO CREATE_Import_List_PVT;
1157 x_return_status := FND_API.G_RET_STS_ERROR;
1158 -- Standard call to get message count and if count=1, get the message
1159 FND_MSG_PUB.Count_And_Get (
1160 p_encoded => FND_API.G_FALSE,
1161 p_count => x_msg_count,
1162 p_data => x_msg_data
1163 );
1164
1165 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1166 ROLLBACK TO CREATE_Import_List_PVT;
1167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1168 -- Standard call to get message count and if count=1, get the message
1169 FND_MSG_PUB.Count_And_Get (
1170 p_encoded => FND_API.G_FALSE,
1171 p_count => x_msg_count,
1172 p_data => x_msg_data
1173 );
1174
1175 WHEN OTHERS THEN
1176 ROLLBACK TO CREATE_Import_List_PVT;
1177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1178 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1179 THEN
1180 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1181 END IF;
1182 -- Standard call to get message count and if count=1, get the message
1183 FND_MSG_PUB.Count_And_Get (
1184 p_encoded => FND_API.G_FALSE,
1185 p_count => x_msg_count,
1186 p_data => x_msg_data
1187 );
1188 End Create_Import_List;
1189
1190
1191 PROCEDURE Update_Import_List(
1192 p_api_version_number IN NUMBER,
1193 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1194 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1195 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1196
1197 x_return_status OUT NOCOPY VARCHAR2,
1198 x_msg_count OUT NOCOPY NUMBER,
1199 x_msg_data OUT NOCOPY VARCHAR2,
1200
1201 p_ams_import_rec IN ams_import_rec_type,
1202 x_object_version_number OUT NOCOPY NUMBER
1203 )
1204
1205 IS
1206
1207 CURSOR c_get_import_list(import_list_header_id NUMBER) IS
1208 SELECT *
1209 FROM AMS_IMP_LIST_HEADERS_ALL
1210 WHERE import_list_header_id=p_ams_import_rec.import_list_header_id;
1211
1212 -- Hint: Developer need to provide Where clause
1213 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Import_List';
1214 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1215 -- Local Variables
1216 l_object_version_number NUMBER;
1217 l_IMPORT_LIST_HEADER_ID NUMBER;
1218 l_ref_ams_import_rec c_get_Import_List%ROWTYPE ;
1219 l_tar_ams_import_rec AMS_Import_List_PVT.ams_import_rec_type := P_ams_import_rec;
1220 l_rowid ROWID;
1221
1222 BEGIN
1223 -- Standard Start of API savepoint
1224 SAVEPOINT UPDATE_Import_List_PVT;
1225
1226 -- Standard call to check for call compatibility.
1227 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1228 p_api_version_number,
1229 l_api_name,
1230 G_PKG_NAME)
1231 THEN
1232 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1233 END IF;
1234
1235 -- Initialize message list if p_init_msg_list is set to TRUE.
1236 IF FND_API.to_Boolean( p_init_msg_list )
1237 THEN
1238 FND_MSG_PUB.initialize;
1239 END IF;
1240
1241 -- Debug Message
1242 IF (AMS_DEBUG_HIGH_ON) THEN
1243
1244 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1245 END IF;
1246
1247
1248 -- Initialize API return status to SUCCESS
1249 x_return_status := FND_API.G_RET_STS_SUCCESS;
1250
1251 -- Debug Message
1252 IF (AMS_DEBUG_HIGH_ON) THEN
1253
1254 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
1255 END IF;
1256
1257 /*
1258 OPEN c_get_Import_List( l_tar_ams_import_rec.import_list_header_id);
1259
1260 FETCH c_get_Import_List INTO l_ref_ams_import_rec ;
1261
1262 If ( c_get_Import_List%NOTFOUND) THEN
1263 AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
1264 p_token_name => 'INFO',
1265 p_token_value => 'Import_List') ;
1266 RAISE FND_API.G_EXC_ERROR;
1267 END IF;
1268 -- Debug Message
1269 IF (AMS_DEBUG_HIGH_ON) THEN
1270
1271 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
1272 END IF;
1273 CLOSE c_get_Import_List;
1274 */
1275
1276
1277 If (l_tar_ams_import_rec.object_version_number is NULL or
1278 l_tar_ams_import_rec.object_version_number = FND_API.G_MISS_NUM ) Then
1279 AMS_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
1280 p_token_name => 'COLUMN',
1281 p_token_value => 'Last_Update_Date') ;
1285 If (l_tar_ams_import_rec.object_version_number <> l_ref_ams_import_rec.object_version_number) Then
1282 raise FND_API.G_EXC_ERROR;
1283 End if;
1284 -- Check Whether record has been changed by someone else
1286 AMS_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
1287 p_token_name => 'INFO',
1288 p_token_value => 'Import_List') ;
1289 raise FND_API.G_EXC_ERROR;
1290 End if;
1291 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
1292 THEN
1293 -- Debug message
1294 IF (AMS_DEBUG_HIGH_ON) THEN
1295
1296 AMS_UTILITY_PVT.debug_message('Private API: Validate_Import_List');
1297 END IF;
1298
1299 -- Invoke validation procedures
1300 Validate_import_list(
1301 p_api_version_number => 1.0,
1302 p_init_msg_list => FND_API.G_FALSE,
1303 p_validation_level => p_validation_level,
1304 p_ams_import_rec => p_ams_import_rec,
1305 x_return_status => x_return_status,
1306 x_msg_count => x_msg_count,
1307 x_msg_data => x_msg_data);
1308 END IF;
1309 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1310 RAISE FND_API.G_EXC_ERROR;
1311 END IF;
1312
1313
1314 -- Debug Message
1315 IF (AMS_DEBUG_HIGH_ON) THEN
1316
1317 AMS_UTILITY_PVT.debug_message('Private API: Calling update table handler');
1318 END IF;
1319
1320 -- Invoke table handler(AMS_IMP_LIST_HEADERS_PKG.Update_Row)
1321 AMS_IMP_LIST_HEADERS_PKG.Update_Row(
1322 p_import_list_header_id => p_ams_import_rec.import_list_header_id,
1323 p_last_update_date => SYSDATE,
1324 p_last_updated_by => FND_GLOBAL.USER_ID,
1325 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
1326 p_object_version_number => p_ams_import_rec.object_version_number,
1327 p_view_application_id => p_ams_import_rec.view_application_id,
1328 p_name => p_ams_import_rec.name,
1329 p_version => p_ams_import_rec.version,
1330 p_import_type => p_ams_import_rec.import_type,
1331 p_owner_user_id => p_ams_import_rec.owner_user_id,
1332 p_list_source_type_id => p_ams_import_rec.list_source_type_id,
1333 p_status_code => p_ams_import_rec.status_code,
1334 p_status_date => p_ams_import_rec.status_date,
1335 p_user_status_id => p_ams_import_rec.user_status_id,
1336 p_source_system => p_ams_import_rec.source_system,
1337 p_vendor_id => p_ams_import_rec.vendor_id,
1338 p_pin_id => p_ams_import_rec.pin_id,
1339 p_org_id => p_ams_import_rec.org_id,
1340 p_scheduled_time => p_ams_import_rec.scheduled_time,
1341 p_loaded_no_of_rows => p_ams_import_rec.loaded_no_of_rows,
1342 p_loaded_date => p_ams_import_rec.loaded_date,
1343 p_rows_to_skip => p_ams_import_rec.rows_to_skip,
1344 p_processed_rows => p_ams_import_rec.processed_rows,
1345 p_headings_flag => p_ams_import_rec.headings_flag,
1346 p_expiry_date => p_ams_import_rec.expiry_date,
1347 p_purge_date => p_ams_import_rec.purge_date,
1348 p_description => p_ams_import_rec.description,
1349 p_keywords => p_ams_import_rec.keywords,
1350 p_transactional_cost => p_ams_import_rec.transactional_cost,
1351 p_transactional_currency_code => p_ams_import_rec.transactional_currency_code,
1352 p_functional_cost => p_ams_import_rec.functional_cost,
1353 p_functional_currency_code => p_ams_import_rec.functional_currency_code,
1354 p_terminated_by => p_ams_import_rec.terminated_by,
1355 p_enclosed_by => p_ams_import_rec.enclosed_by,
1356 p_data_filename => p_ams_import_rec.data_filename,
1357 p_process_immed_flag => p_ams_import_rec.process_immed_flag,
1358 p_dedupe_flag => p_ams_import_rec.dedupe_flag,
1359 p_attribute_category => p_ams_import_rec.attribute_category,
1360 p_attribute1 => p_ams_import_rec.attribute1,
1361 p_attribute2 => p_ams_import_rec.attribute2,
1362 p_attribute3 => p_ams_import_rec.attribute3,
1363 p_attribute4 => p_ams_import_rec.attribute4,
1364 p_attribute5 => p_ams_import_rec.attribute5,
1365 p_attribute6 => p_ams_import_rec.attribute6,
1366 p_attribute7 => p_ams_import_rec.attribute7,
1367 p_attribute8 => p_ams_import_rec.attribute8,
1368 p_attribute9 => p_ams_import_rec.attribute9,
1369 p_attribute10 => p_ams_import_rec.attribute10,
1370 p_attribute11 => p_ams_import_rec.attribute11,
1371 p_attribute12 => p_ams_import_rec.attribute12,
1372 p_attribute13 => p_ams_import_rec.attribute13,
1373 p_attribute14 => p_ams_import_rec.attribute14,
1374 p_attribute15 => p_ams_import_rec.attribute15,
1375 p_custom_setup_id => p_ams_import_rec.custom_setup_id,
1376 p_country => p_ams_import_rec.country,
1377 p_usage => p_ams_import_rec.usage,
1378 p_number_of_records => p_ams_import_rec.number_of_records,
1379 p_data_file_name => p_ams_import_rec.data_file_name,
1380 p_b2b_flag => p_ams_import_rec.b2b_flag,
1381 p_rented_list_flag => p_ams_import_rec.rented_list_flag,
1382 p_server_flag => p_ams_import_rec.server_flag,
1383 p_log_file_name => p_ams_import_rec.log_file_name,
1387 p_validate_file => p_ams_import_rec.validate_file,
1384 p_number_of_failed_records => p_ams_import_rec.number_of_failed_records,
1385 p_number_of_duplicate_records => p_ams_import_rec.number_of_duplicate_records,
1386 p_enable_word_replacement_flag => p_ams_import_rec.enable_word_replacement_flag,
1388 p_record_update_flag => p_ams_import_rec.record_update_flag,
1389 p_error_threshold => p_ams_import_rec.error_threshold);
1390 --
1391 -- End of API body.
1392 --
1393
1394 -- Standard check for p_commit
1395 IF FND_API.to_Boolean( p_commit )
1396 THEN
1397 COMMIT WORK;
1398 END IF;
1399
1400
1401 -- Debug Message
1402 IF (AMS_DEBUG_HIGH_ON) THEN
1403
1404 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1405 END IF;
1406
1407 -- Standard call to get message count and if count is 1, get message info.
1408 FND_MSG_PUB.Count_And_Get
1409 (p_count => x_msg_count,
1410 p_data => x_msg_data
1411 );
1412 EXCEPTION
1413
1414 WHEN AMS_Utility_PVT.resource_locked THEN
1415 x_return_status := FND_API.g_ret_sts_error;
1416 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1417
1418 WHEN FND_API.G_EXC_ERROR THEN
1419 ROLLBACK TO UPDATE_Import_List_PVT;
1420 x_return_status := FND_API.G_RET_STS_ERROR;
1421 -- Standard call to get message count and if count=1, get the message
1422 FND_MSG_PUB.Count_And_Get (
1423 p_encoded => FND_API.G_FALSE,
1424 p_count => x_msg_count,
1425 p_data => x_msg_data
1426 );
1427
1428 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1429 ROLLBACK TO UPDATE_Import_List_PVT;
1430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1431 -- Standard call to get message count and if count=1, get the message
1432 FND_MSG_PUB.Count_And_Get (
1433 p_encoded => FND_API.G_FALSE,
1434 p_count => x_msg_count,
1435 p_data => x_msg_data
1436 );
1437
1438 WHEN OTHERS THEN
1439 ROLLBACK TO UPDATE_Import_List_PVT;
1440 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1441 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1442 THEN
1443 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1444 END IF;
1445 -- Standard call to get message count and if count=1, get the message
1446 FND_MSG_PUB.Count_And_Get (
1447 p_encoded => FND_API.G_FALSE,
1448 p_count => x_msg_count,
1449 p_data => x_msg_data
1450 );
1451 End Update_Import_List;
1452
1453
1454 PROCEDURE Delete_Import_List(
1455 p_api_version_number IN NUMBER,
1456 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1457 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1458 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1459 x_return_status OUT NOCOPY VARCHAR2,
1460 x_msg_count OUT NOCOPY NUMBER,
1461 x_msg_data OUT NOCOPY VARCHAR2,
1462 p_import_list_header_id IN NUMBER,
1463 p_object_version_number IN NUMBER
1464 )
1465
1466 IS
1467 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Import_List';
1468 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1469 l_object_version_number NUMBER;
1470
1471 BEGIN
1472 -- Standard Start of API savepoint
1473 SAVEPOINT DELETE_Import_List_PVT;
1474
1475 -- Standard call to check for call compatibility.
1476 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1477 p_api_version_number,
1478 l_api_name,
1479 G_PKG_NAME)
1480 THEN
1481 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1482 END IF;
1483
1484 -- Initialize message list if p_init_msg_list is set to TRUE.
1485 IF FND_API.to_Boolean( p_init_msg_list )
1486 THEN
1487 FND_MSG_PUB.initialize;
1488 END IF;
1489
1490 -- Debug Message
1491 IF (AMS_DEBUG_HIGH_ON) THEN
1492
1493 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1494 END IF;
1495
1496
1497 -- Initialize API return status to SUCCESS
1498 x_return_status := FND_API.G_RET_STS_SUCCESS;
1499
1500 --
1501 -- Api body
1502 --
1503 -- Debug Message
1504 IF (AMS_DEBUG_HIGH_ON) THEN
1505
1506 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
1507 END IF;
1508
1509 -- Invoke table handler(AMS_IMP_LIST_HEADERS_PKG.Delete_Row)
1510 AMS_IMP_LIST_HEADERS_PKG.Delete_Row(
1511 p_IMPORT_LIST_HEADER_ID => p_IMPORT_LIST_HEADER_ID);
1512 --
1513 -- End of API body
1514 --
1515
1516 -- Standard check for p_commit
1517 IF FND_API.to_Boolean( p_commit )
1518 THEN
1519 COMMIT WORK;
1520 END IF;
1521
1522
1523 -- Debug Message
1524 IF (AMS_DEBUG_HIGH_ON) THEN
1525
1529 -- Standard call to get message count and if count is 1, get message info.
1526 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1527 END IF;
1528
1530 FND_MSG_PUB.Count_And_Get
1531 (p_count => x_msg_count,
1532 p_data => x_msg_data
1533 );
1534 EXCEPTION
1535
1536 WHEN AMS_Utility_PVT.resource_locked THEN
1537 x_return_status := FND_API.g_ret_sts_error;
1538 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1539
1540 WHEN FND_API.G_EXC_ERROR THEN
1541 ROLLBACK TO DELETE_Import_List_PVT;
1542 x_return_status := FND_API.G_RET_STS_ERROR;
1543 -- Standard call to get message count and if count=1, get the message
1544 FND_MSG_PUB.Count_And_Get (
1545 p_encoded => FND_API.G_FALSE,
1546 p_count => x_msg_count,
1547 p_data => x_msg_data
1548 );
1549
1550 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1551 ROLLBACK TO DELETE_Import_List_PVT;
1552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1553 -- Standard call to get message count and if count=1, get the message
1554 FND_MSG_PUB.Count_And_Get (
1555 p_encoded => FND_API.G_FALSE,
1556 p_count => x_msg_count,
1557 p_data => x_msg_data
1558 );
1559
1560 WHEN OTHERS THEN
1561 ROLLBACK TO DELETE_Import_List_PVT;
1562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1563 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1564 THEN
1565 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1566 END IF;
1567 -- Standard call to get message count and if count=1, get the message
1568 FND_MSG_PUB.Count_And_Get (
1569 p_encoded => FND_API.G_FALSE,
1570 p_count => x_msg_count,
1571 p_data => x_msg_data
1572 );
1573 End Delete_Import_List;
1574
1575
1576
1577 -- Hint: Primary key needs to be returned.
1578 PROCEDURE Lock_Import_List(
1579 p_api_version_number IN NUMBER,
1580 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1581
1582 x_return_status OUT NOCOPY VARCHAR2,
1583 x_msg_count OUT NOCOPY NUMBER,
1584 x_msg_data OUT NOCOPY VARCHAR2,
1585
1586 p_import_list_header_id IN NUMBER,
1587 p_object_version IN NUMBER
1588 )
1589
1590 IS
1591 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Import_List';
1592 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1593 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1594 l_IMPORT_LIST_HEADER_ID NUMBER;
1595
1596 CURSOR c_Import_List IS
1597 SELECT IMPORT_LIST_HEADER_ID
1598 FROM AMS_IMP_LIST_HEADERS_ALL
1599 WHERE IMPORT_LIST_HEADER_ID = p_IMPORT_LIST_HEADER_ID
1600 AND object_version_number = p_object_version
1601 FOR UPDATE NOWAIT;
1602
1603 BEGIN
1604
1605 -- Debug Message
1606 IF (AMS_DEBUG_HIGH_ON) THEN
1607
1608 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1609 END IF;
1610
1611 -- Initialize message list if p_init_msg_list is set to TRUE.
1612 IF FND_API.to_Boolean( p_init_msg_list )
1613 THEN
1614 FND_MSG_PUB.initialize;
1615 END IF;
1616
1617 -- Standard call to check for call compatibility.
1618 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1619 p_api_version_number,
1620 l_api_name,
1621 G_PKG_NAME)
1622 THEN
1623 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1624 END IF;
1625
1626
1627 -- Initialize API return status to SUCCESS
1628 x_return_status := FND_API.G_RET_STS_SUCCESS;
1629
1630
1631 ------------------------ lock -------------------------
1632
1633 IF (AMS_DEBUG_HIGH_ON) THEN
1634
1635
1636
1637 AMS_Utility_PVT.debug_message(l_full_name||': start');
1638
1639 END IF;
1640 OPEN c_Import_List;
1641
1642 FETCH c_Import_List INTO l_IMPORT_LIST_HEADER_ID;
1643
1644 IF (c_Import_List%NOTFOUND) THEN
1645 CLOSE c_Import_List;
1646 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1647 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1648 FND_MSG_PUB.add;
1649 END IF;
1650 RAISE FND_API.g_exc_error;
1651 END IF;
1652
1653 CLOSE c_Import_List;
1654
1655 -------------------- finish --------------------------
1656 FND_MSG_PUB.count_and_get(
1657 p_encoded => FND_API.g_false,
1658 p_count => x_msg_count,
1659 p_data => x_msg_data);
1660 IF (AMS_DEBUG_HIGH_ON) THEN
1661
1662 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1663 END IF;
1664 EXCEPTION
1665
1666 WHEN AMS_Utility_PVT.resource_locked THEN
1667 x_return_status := FND_API.g_ret_sts_error;
1668 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1669
1670 WHEN FND_API.G_EXC_ERROR THEN
1671 ROLLBACK TO LOCK_Import_List_PVT;
1672 x_return_status := FND_API.G_RET_STS_ERROR;
1676 p_count => x_msg_count,
1673 -- Standard call to get message count and if count=1, get the message
1674 FND_MSG_PUB.Count_And_Get (
1675 p_encoded => FND_API.G_FALSE,
1677 p_data => x_msg_data
1678 );
1679
1680 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1681 ROLLBACK TO LOCK_Import_List_PVT;
1682 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1683 -- Standard call to get message count and if count=1, get the message
1684 FND_MSG_PUB.Count_And_Get (
1685 p_encoded => FND_API.G_FALSE,
1686 p_count => x_msg_count,
1687 p_data => x_msg_data
1688 );
1689
1690 WHEN OTHERS THEN
1691 ROLLBACK TO LOCK_Import_List_PVT;
1692 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1693 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1694 THEN
1695 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1696 END IF;
1697 -- Standard call to get message count and if count=1, get the message
1698 FND_MSG_PUB.Count_And_Get (
1699 p_encoded => FND_API.G_FALSE,
1700 p_count => x_msg_count,
1701 p_data => x_msg_data
1702 );
1703 End Lock_Import_List;
1704
1705
1706 PROCEDURE check_ams_import_uk_items(
1707 p_ams_import_rec IN ams_import_rec_type,
1708 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1709 x_return_status OUT NOCOPY VARCHAR2)
1710 IS
1711 l_valid_flag VARCHAR2(1);
1712
1713
1714 BEGIN
1715 x_return_status := FND_API.g_ret_sts_success;
1716 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1717 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1718 'AMS_IMP_LIST_HEADERS_ALL',
1719 'IMPORT_LIST_HEADER_ID = ''' || p_ams_import_rec.IMPORT_LIST_HEADER_ID ||''''
1720 );
1721 ELSE
1722 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1723 'AMS_IMP_LIST_HEADERS_ALL',
1724 'IMPORT_LIST_HEADER_ID = ''' || p_ams_import_rec.IMPORT_LIST_HEADER_ID ||
1725 ''' AND IMPORT_LIST_HEADER_ID <> ' || p_ams_import_rec.IMPORT_LIST_HEADER_ID
1726 );
1727 END IF;
1728
1729 IF l_valid_flag = FND_API.g_false THEN
1730 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_IMPORT_LIST_HEADER_ID_DUPLICATE');
1731 x_return_status := FND_API.g_ret_sts_error;
1732 RETURN;
1733 END IF;
1734
1735
1736
1737
1738 END check_ams_import_uk_items;
1739
1740 PROCEDURE check_ams_import_req_items(
1741 p_ams_import_rec IN ams_import_rec_type,
1742 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1743 x_return_status OUT NOCOPY VARCHAR2
1744 )
1745 IS
1746 BEGIN
1747 x_return_status := FND_API.g_ret_sts_success;
1748
1749 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1750
1751
1752 IF p_ams_import_rec.import_list_header_id = FND_API.g_miss_num OR p_ams_import_rec.import_list_header_id IS NULL THEN
1753 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_import_list_header_id');
1754 x_return_status := FND_API.g_ret_sts_error;
1755 RETURN;
1756 END IF;
1757
1758
1759 IF p_ams_import_rec.last_update_date = FND_API.g_miss_date OR p_ams_import_rec.last_update_date IS NULL THEN
1760 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_last_update_date');
1761 x_return_status := FND_API.g_ret_sts_error;
1762 RETURN;
1763 END IF;
1764
1765
1766 IF p_ams_import_rec.last_updated_by = FND_API.g_miss_num OR p_ams_import_rec.last_updated_by IS NULL THEN
1767 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_last_updated_by');
1768 x_return_status := FND_API.g_ret_sts_error;
1769 RETURN;
1770 END IF;
1771
1772
1773 IF p_ams_import_rec.creation_date = FND_API.g_miss_date OR p_ams_import_rec.creation_date IS NULL THEN
1774 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_creation_date');
1775 x_return_status := FND_API.g_ret_sts_error;
1776 RETURN;
1777 END IF;
1778
1779
1780 IF p_ams_import_rec.created_by = FND_API.g_miss_num OR p_ams_import_rec.created_by IS NULL THEN
1781 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_created_by');
1782 x_return_status := FND_API.g_ret_sts_error;
1783 RETURN;
1784 END IF;
1785
1786
1787 IF p_ams_import_rec.view_application_id = FND_API.g_miss_num OR p_ams_import_rec.view_application_id IS NULL THEN
1788 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_view_application_id');
1789 x_return_status := FND_API.g_ret_sts_error;
1790 RETURN;
1791 END IF;
1792
1793
1794 IF p_ams_import_rec.name = FND_API.g_miss_char OR p_ams_import_rec.name IS NULL THEN
1795 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_name');
1796 x_return_status := FND_API.g_ret_sts_error;
1797 RETURN;
1798 END IF;
1799
1800
1801 IF p_ams_import_rec.version = FND_API.g_miss_char OR p_ams_import_rec.version IS NULL THEN
1805 END IF;
1802 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_version');
1803 x_return_status := FND_API.g_ret_sts_error;
1804 RETURN;
1806
1807
1808 IF p_ams_import_rec.import_type = FND_API.g_miss_char OR p_ams_import_rec.import_type IS NULL THEN
1809 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_import_type');
1810 x_return_status := FND_API.g_ret_sts_error;
1811 RETURN;
1812 END IF;
1813
1814
1815 IF p_ams_import_rec.owner_user_id = FND_API.g_miss_num OR p_ams_import_rec.owner_user_id IS NULL THEN
1816 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_owner_user_id');
1817 x_return_status := FND_API.g_ret_sts_error;
1818 RETURN;
1819 END IF;
1820
1821
1822 IF p_ams_import_rec.status_code = FND_API.g_miss_char OR p_ams_import_rec.status_code IS NULL THEN
1823 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_status_code');
1824 x_return_status := FND_API.g_ret_sts_error;
1825 RETURN;
1826 END IF;
1827
1828
1829 IF p_ams_import_rec.status_date = FND_API.g_miss_date OR p_ams_import_rec.status_date IS NULL THEN
1830 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_status_date');
1831 x_return_status := FND_API.g_ret_sts_error;
1832 RETURN;
1833 END IF;
1834
1835
1836 IF p_ams_import_rec.user_status_id = FND_API.g_miss_num OR p_ams_import_rec.user_status_id IS NULL THEN
1837 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_user_status_id');
1838 x_return_status := FND_API.g_ret_sts_error;
1839 RETURN;
1840 END IF;
1841
1842
1843 IF p_ams_import_rec.import_list_header_id IS NULL THEN
1844 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_import_list_header_id');
1845 x_return_status := FND_API.g_ret_sts_error;
1846 RETURN;
1847 END IF;
1848
1849
1850 IF p_ams_import_rec.last_update_date IS NULL THEN
1851 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_last_update_date');
1852 x_return_status := FND_API.g_ret_sts_error;
1853 RETURN;
1854 END IF;
1855
1856
1857 IF p_ams_import_rec.last_updated_by IS NULL THEN
1858 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_last_updated_by');
1859 x_return_status := FND_API.g_ret_sts_error;
1860 RETURN;
1861 END IF;
1862
1863
1864 IF p_ams_import_rec.creation_date IS NULL THEN
1865 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_creation_date');
1866 x_return_status := FND_API.g_ret_sts_error;
1867 RETURN;
1868 END IF;
1869
1870
1871 IF p_ams_import_rec.created_by IS NULL THEN
1872 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_created_by');
1873 x_return_status := FND_API.g_ret_sts_error;
1874 RETURN;
1875 END IF;
1876
1877
1878 IF p_ams_import_rec.view_application_id IS NULL THEN
1879 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_view_application_id');
1880 x_return_status := FND_API.g_ret_sts_error;
1881 RETURN;
1882 END IF;
1883
1884
1885 IF p_ams_import_rec.name IS NULL THEN
1886 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_name');
1887 x_return_status := FND_API.g_ret_sts_error;
1888 RETURN;
1889 END IF;
1890
1891
1892 IF p_ams_import_rec.version IS NULL THEN
1893 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_version');
1894 x_return_status := FND_API.g_ret_sts_error;
1895 RETURN;
1896 END IF;
1897
1898
1899 IF p_ams_import_rec.import_type IS NULL THEN
1900 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_import_type');
1901 x_return_status := FND_API.g_ret_sts_error;
1902 RETURN;
1903 END IF;
1904
1905
1906 IF p_ams_import_rec.owner_user_id IS NULL THEN
1907 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_owner_user_id');
1908 x_return_status := FND_API.g_ret_sts_error;
1909 RETURN;
1910 END IF;
1911
1912
1913 IF p_ams_import_rec.status_code IS NULL THEN
1914 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_status_code');
1915 x_return_status := FND_API.g_ret_sts_error;
1916 RETURN;
1917 END IF;
1918
1919
1920 IF p_ams_import_rec.status_date IS NULL THEN
1921 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_status_date');
1922 x_return_status := FND_API.g_ret_sts_error;
1923 RETURN;
1924 END IF;
1925
1926
1927 IF p_ams_import_rec.user_status_id IS NULL THEN
1928 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_user_status_id');
1929 x_return_status := FND_API.g_ret_sts_error;
1930 RETURN;
1931 END IF;
1932 END IF;
1933
1934 END check_ams_import_req_items;
1935
1936 PROCEDURE check_ams_import_FK_items(
1937 p_ams_import_rec IN ams_import_rec_type,
1938 x_return_status OUT NOCOPY VARCHAR2
1939 )
1940 IS
1941 BEGIN
1945
1942 x_return_status := FND_API.g_ret_sts_success;
1943
1944 -- Enter custom code here
1946 END check_ams_import_FK_items;
1947
1948 PROCEDURE check_ams_import_Lookup_items(
1949 p_ams_import_rec IN ams_import_rec_type,
1950 x_return_status OUT NOCOPY VARCHAR2
1951 )
1952 IS
1953 BEGIN
1954 x_return_status := FND_API.g_ret_sts_success;
1955
1956 -- Enter custom code here
1957
1958 END check_ams_import_Lookup_items;
1959
1960 PROCEDURE check_ams_import_Business(
1961 p_ams_import_rec IN ams_import_rec_type,
1962 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1963 x_return_status OUT NOCOPY VARCHAR2
1964 )
1965 IS
1966 CURSOR c_get_import_list(import_list_header_id NUMBER) IS
1967 SELECT *
1968 FROM AMS_IMP_LIST_HEADERS_ALL
1969 WHERE import_list_header_id=p_ams_import_rec.import_list_header_id;
1970 l_import_rec c_get_import_list%ROWTYPE;
1971
1972 CURSOR c_get_import_list_name(name VARCHAR2) IS
1973 SELECT *
1974 FROM AMS_IMP_LIST_HEADERS_VL
1975 WHERE name=p_ams_import_rec.name;
1976 l_import_name_rec c_get_import_list_name%ROWTYPE;
1977
1978 BEGIN
1979
1980 x_return_status := FND_API.g_ret_sts_success;
1981 IF p_ams_import_rec.server_flag = 'Y'
1982 THEN
1983 if fnd_profile.value('AMS_IMP_CTL_PATH') is NULL then
1984 FND_MESSAGE.set_name('AMS', 'AMS_IMP_CTL_PATH');
1985 FND_MSG_PUB.Add;
1986 x_return_status := FND_API.g_ret_sts_error;
1987 RETURN;
1988 end if;
1989
1990 if fnd_profile.value('AMS_IMP_DATA_PATH') is NULL then
1991 FND_MESSAGE.set_name('AMS', 'AMS_IMP_DATA_PATH');
1992 FND_MSG_PUB.Add;
1993 x_return_status := FND_API.g_ret_sts_error;
1994 RETURN;
1995 end if;
1996 END IF;
1997
1998 IF p_validation_mode = JTF_PLSQL_API.g_update
1999 THEN
2000 --Exp date and Usage validation only applied to Rented lists
2001 IF (p_ams_import_rec.rented_list_flag = 'R') and
2002 (p_ams_import_rec.expiry_date <> FND_API.g_miss_date) and
2003 (p_ams_import_rec.expiry_date < SYSDATE)
2004 THEN
2005 --AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_name');
2006 FND_MESSAGE.set_name('AMS', 'AMS_IMP_EXP_DATE');
2007 FND_MSG_PUB.Add;
2008 x_return_status := FND_API.g_ret_sts_error;
2009 RETURN;
2010 END IF;
2011
2012 -- Rented list must enter exp date and/or usage
2013 IF (p_ams_import_rec.rented_list_flag = 'R') AND
2014 ((p_ams_import_rec.usage is NULL AND p_ams_import_rec.expiry_date is NULL) OR
2015 (p_ams_import_rec.usage = FND_API.g_miss_num AND p_ams_import_rec.expiry_date = FND_API.g_miss_date))
2016 THEN
2017 FND_MESSAGE.set_name('AMS', 'AMS_IMP_RENTED_LIST');
2018 FND_MSG_PUB.Add;
2019 x_return_status := FND_API.g_ret_sts_error;
2020 RETURN;
2021 END IF;
2022 END IF;
2023
2024 OPEN c_get_Import_List( p_ams_import_rec.import_list_header_id);
2025 FETCH c_get_Import_List INTO l_import_rec ;
2026 -- NOFOUND means create mode
2027 IF (c_get_Import_List%NOTFOUND)
2028 THEN
2029
2030 IF (p_ams_import_rec.data_filename = FND_API.g_miss_char) OR
2031 (p_ams_import_rec.data_filename is NULL)
2032 THEN
2033 FND_MESSAGE.set_name('AMS', 'API_MISS_DATA_FILENAME');
2034 FND_MSG_PUB.Add;
2035 x_return_status := FND_API.g_ret_sts_error;
2036 close c_get_Import_list;
2037 RETURN;
2038 END IF;
2039
2040 IF (p_ams_import_rec.terminated_by = p_ams_import_rec.enclosed_by)
2041 THEN
2042 FND_MESSAGE.set_name('AMS', 'API_SAME_TERMIN_ENCLOSED');
2043 FND_MSG_PUB.Add;
2044 x_return_status := FND_API.g_ret_sts_error;
2045 close c_get_Import_list;
2046 RETURN;
2047 END IF;
2048
2049 -- check import name uniqueness
2050 OPEN c_get_Import_List_name( p_ams_import_rec.name);
2051 FETCH c_get_Import_List_name INTO l_import_name_rec ;
2052 IF (c_get_Import_List_name%FOUND)
2053 THEN
2054 --AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_IMPORT_NAME_DUPLICATE');
2055 FND_MESSAGE.set_name('AMS', 'AMS_IMPORT_NAME_DUPLICATE');
2056 FND_MSG_PUB.Add;
2057 x_return_status := FND_API.g_ret_sts_error;
2058 close c_get_Import_list_name;
2059 RETURN;
2060 END IF;
2061
2062 END IF;
2063
2064 END check_ams_import_Business;
2065
2066 PROCEDURE Check_ams_import_Items (
2067 P_ams_import_rec IN ams_import_rec_type,
2068 p_validation_mode IN VARCHAR2,
2069 x_return_status OUT NOCOPY VARCHAR2
2070 )
2071 IS
2072 BEGIN
2073
2074 -- Check Items Uniqueness API calls
2075 IF (AMS_DEBUG_HIGH_ON) THEN
2076
2077 AMS_UTILITY_PVT.debug_message('Private API: ' || 'before check_ams_import_uk_items');
2078 END IF;
2079
2080 check_ams_import_uk_items(
2081 p_ams_import_rec => p_ams_import_rec,
2082 p_validation_mode => p_validation_mode,
2086 END IF;
2083 x_return_status => x_return_status);
2084 IF x_return_status <> FND_API.g_ret_sts_success THEN
2085 RETURN;
2087
2088 -- Check Items Required/NOT NULL API calls
2089 IF (AMS_DEBUG_HIGH_ON) THEN
2090
2091 AMS_UTILITY_PVT.debug_message('Private API: ' || 'before check_ams_import_req_items');
2092 END IF;
2093
2094 check_ams_import_req_items(
2095 p_ams_import_rec => p_ams_import_rec,
2096 p_validation_mode => p_validation_mode,
2097 x_return_status => x_return_status);
2098 IF x_return_status <> FND_API.g_ret_sts_success THEN
2099 RETURN;
2100 END IF;
2101 -- Check Items Foreign Keys API calls
2102 IF (AMS_DEBUG_HIGH_ON) THEN
2103
2104 AMS_UTILITY_PVT.debug_message('Private API: ' || 'before check_ams_import_FK_items');
2105 END IF;
2106
2107 check_ams_import_FK_items(
2108 p_ams_import_rec => p_ams_import_rec,
2109 x_return_status => x_return_status);
2110 IF x_return_status <> FND_API.g_ret_sts_success THEN
2111 RETURN;
2112 END IF;
2113 -- Check Items Lookups
2114 IF (AMS_DEBUG_HIGH_ON) THEN
2115
2116 AMS_UTILITY_PVT.debug_message('Private API: ' || 'before check_ams_import_Lookup_items');
2117 END IF;
2118
2119 check_ams_import_Lookup_items(
2120 p_ams_import_rec => p_ams_import_rec,
2121 x_return_status => x_return_status);
2122 IF x_return_status <> FND_API.g_ret_sts_success THEN
2123 RETURN;
2124 END IF;
2125 IF (AMS_DEBUG_HIGH_ON) THEN
2126
2127 AMS_UTILITY_PVT.debug_message('Private API: ' || 'after check_ams_import_Lookup_items');
2128 END IF;
2129
2130 IF (AMS_DEBUG_HIGH_ON) THEN
2131
2132
2133
2134 AMS_UTILITY_PVT.debug_message('Private API: ' || 'before check_ams_import_Business');
2135
2136 END IF;
2137 check_ams_import_Business(
2138 p_ams_import_rec => p_ams_import_rec,
2139 p_validation_mode => p_validation_mode,
2140 x_return_status => x_return_status);
2141 IF x_return_status <> FND_API.g_ret_sts_success THEN
2142 RETURN;
2143 END IF;
2144 IF (AMS_DEBUG_HIGH_ON) THEN
2145
2146 AMS_UTILITY_PVT.debug_message('Private API: ' || 'after check_ams_import_Business');
2147 END IF;
2148 END Check_ams_import_Items;
2149
2150
2151 PROCEDURE Complete_ams_import_Rec (
2152 p_ams_import_rec IN ams_import_rec_type,
2153 x_complete_rec OUT NOCOPY ams_import_rec_type)
2154 IS
2155 l_return_status VARCHAR2(1);
2156
2157 CURSOR c_complete IS
2158 SELECT *
2159 FROM ams_imp_list_headers_all
2160 WHERE import_list_header_id = p_ams_import_rec.import_list_header_id;
2161 l_ams_import_rec c_complete%ROWTYPE;
2162
2163 CURSOR c_status_id IS
2164 SELECT user_status_id
2165 FROM ams_user_statuses_vl
2166 WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
2167 system_status_code = 'NEW' and default_flag = 'Y';
2168 l_user_status_id NUMBER;
2169 BEGIN
2170 x_complete_rec := p_ams_import_rec;
2171
2172 OPEN c_status_id;
2173 FETCH c_status_id INTO l_user_status_id;
2174 CLOSE c_status_id;
2175
2176
2177 OPEN c_complete;
2178 FETCH c_complete INTO l_ams_import_rec;
2179 CLOSE c_complete;
2180
2181 -- import_list_header_id
2182 IF p_ams_import_rec.import_list_header_id = FND_API.g_miss_num THEN
2183 x_complete_rec.import_list_header_id := l_ams_import_rec.import_list_header_id;
2184 END IF;
2185
2186 -- last_update_date
2187 IF p_ams_import_rec.last_update_date = FND_API.g_miss_date THEN
2188 x_complete_rec.last_update_date := l_ams_import_rec.last_update_date;
2189 END IF;
2190
2191 -- last_updated_by
2192 IF p_ams_import_rec.last_updated_by = FND_API.g_miss_num THEN
2193 x_complete_rec.last_updated_by := l_ams_import_rec.last_updated_by;
2194 END IF;
2195
2196 -- creation_date
2197 IF p_ams_import_rec.creation_date = FND_API.g_miss_date THEN
2198 x_complete_rec.creation_date := l_ams_import_rec.creation_date;
2199 END IF;
2200
2201 -- created_by
2202 IF p_ams_import_rec.created_by = FND_API.g_miss_num THEN
2203 x_complete_rec.created_by := l_ams_import_rec.created_by;
2204 END IF;
2205
2206 -- last_update_login
2207 IF p_ams_import_rec.last_update_login = FND_API.g_miss_num THEN
2208 x_complete_rec.last_update_login := l_ams_import_rec.last_update_login;
2209 END IF;
2210
2211 -- object_version_number
2212 IF p_ams_import_rec.object_version_number = FND_API.g_miss_num THEN
2213 x_complete_rec.object_version_number := l_ams_import_rec.object_version_number;
2214 END IF;
2215
2216 -- view_application_id
2217 IF p_ams_import_rec.view_application_id = FND_API.g_miss_num THEN
2218 x_complete_rec.view_application_id := l_ams_import_rec.view_application_id;
2219 END IF;
2220
2221 -- name
2222 IF p_ams_import_rec.name = FND_API.g_miss_char THEN
2223 x_complete_rec.name := l_ams_import_rec.name;
2224 END IF;
2225
2226 -- version
2227 IF p_ams_import_rec.version = FND_API.g_miss_char THEN
2228 x_complete_rec.version := '1.0';--l_ams_import_rec.version;
2232 IF p_ams_import_rec.import_type = FND_API.g_miss_char THEN
2229 END IF;
2230
2231 -- import_type
2233 x_complete_rec.import_type := l_ams_import_rec.import_type;
2234 END IF;
2235
2236 -- owner_user_id
2237 IF p_ams_import_rec.owner_user_id = FND_API.g_miss_num THEN
2238 x_complete_rec.owner_user_id := l_ams_import_rec.owner_user_id;
2239 END IF;
2240
2241 -- list_source_type_id
2242 IF p_ams_import_rec.list_source_type_id = FND_API.g_miss_num THEN
2243 x_complete_rec.list_source_type_id := l_ams_import_rec.list_source_type_id;
2244 END IF;
2245
2246 -- status_code
2247 IF p_ams_import_rec.status_code = FND_API.g_miss_char THEN
2248 x_complete_rec.status_code := 'NEW';--l_ams_import_rec.status_code;
2249 END IF;
2250
2251 -- status_date
2252 IF p_ams_import_rec.status_date = FND_API.g_miss_date THEN
2253 x_complete_rec.status_date := sysdate;--l_ams_import_rec.status_date;
2254 END IF;
2255
2256 -- user_status_id
2257 IF p_ams_import_rec.user_status_id = FND_API.g_miss_num THEN
2258 x_complete_rec.user_status_id := l_user_status_id;--3001;--l_ams_import_rec.user_status_id;
2259 END IF;
2260
2261 -- source_system
2262 IF p_ams_import_rec.source_system = FND_API.g_miss_char THEN
2263 x_complete_rec.source_system := l_ams_import_rec.source_system;
2264 END IF;
2265
2266 -- vendor_id
2267 IF p_ams_import_rec.vendor_id = FND_API.g_miss_num THEN
2268 x_complete_rec.vendor_id := l_ams_import_rec.vendor_id;
2269 END IF;
2270
2271 -- pin_id
2272 IF p_ams_import_rec.pin_id = FND_API.g_miss_num THEN
2273 x_complete_rec.pin_id := l_ams_import_rec.pin_id;
2274 END IF;
2275
2276 -- org_id
2277 IF p_ams_import_rec.org_id = FND_API.g_miss_num THEN
2278 x_complete_rec.org_id := l_ams_import_rec.org_id;
2279 END IF;
2280
2281 -- scheduled_time
2282 IF p_ams_import_rec.scheduled_time = FND_API.g_miss_date THEN
2283 x_complete_rec.scheduled_time := l_ams_import_rec.scheduled_time;
2284 END IF;
2285
2286 -- loaded_no_of_rows
2287 IF p_ams_import_rec.loaded_no_of_rows = FND_API.g_miss_num THEN
2288 x_complete_rec.loaded_no_of_rows := l_ams_import_rec.loaded_no_of_rows;
2289 END IF;
2290
2291 -- loaded_date
2292 IF p_ams_import_rec.loaded_date = FND_API.g_miss_date THEN
2293 x_complete_rec.loaded_date := l_ams_import_rec.loaded_date;
2294 END IF;
2295
2296 -- rows_to_skip
2297 IF p_ams_import_rec.rows_to_skip = FND_API.g_miss_num THEN
2298 x_complete_rec.rows_to_skip := l_ams_import_rec.rows_to_skip;
2299 END IF;
2300
2301 -- processed_rows
2302 IF p_ams_import_rec.processed_rows = FND_API.g_miss_num THEN
2303 x_complete_rec.processed_rows := l_ams_import_rec.processed_rows;
2304 END IF;
2305
2306 -- headings_flag
2307 IF p_ams_import_rec.headings_flag = FND_API.g_miss_char THEN
2308 x_complete_rec.headings_flag := l_ams_import_rec.headings_flag;
2309 END IF;
2310
2311 -- expiry_date
2312 IF p_ams_import_rec.expiry_date = FND_API.g_miss_date THEN
2313 x_complete_rec.expiry_date := l_ams_import_rec.expiry_date;
2314 END IF;
2315
2316
2317 -- purge_date
2318 IF p_ams_import_rec.purge_date = FND_API.g_miss_date THEN
2319 x_complete_rec.purge_date := l_ams_import_rec.purge_date;
2320 END IF;
2321
2322 -- description
2323 IF p_ams_import_rec.description = FND_API.g_miss_char THEN
2324 x_complete_rec.description := l_ams_import_rec.description;
2325 END IF;
2326
2327 -- keywords
2328 IF p_ams_import_rec.keywords = FND_API.g_miss_char THEN
2329 x_complete_rec.keywords := l_ams_import_rec.keywords;
2330 END IF;
2331
2332 -- transactional_cost
2333 IF p_ams_import_rec.transactional_cost = FND_API.g_miss_num THEN
2334 x_complete_rec.transactional_cost := l_ams_import_rec.transactional_cost;
2335 END IF;
2336
2337 -- transactional_currency_code
2338 IF p_ams_import_rec.transactional_currency_code = FND_API.g_miss_char THEN
2339 x_complete_rec.transactional_currency_code := l_ams_import_rec.transactional_currency_code;
2340 END IF;
2341
2342 -- functional_cost
2343 IF p_ams_import_rec.functional_cost = FND_API.g_miss_num THEN
2344 x_complete_rec.functional_cost := l_ams_import_rec.functional_cost;
2345 END IF;
2346
2347 -- functional_currency_code
2348 IF p_ams_import_rec.functional_currency_code = FND_API.g_miss_char THEN
2349 x_complete_rec.functional_currency_code := l_ams_import_rec.functional_currency_code;
2350 END IF;
2351
2352 -- terminated_by
2353 IF p_ams_import_rec.terminated_by = FND_API.g_miss_char THEN
2354 x_complete_rec.terminated_by := l_ams_import_rec.terminated_by;
2355 END IF;
2356
2357 -- enclosed_by
2358 IF p_ams_import_rec.enclosed_by = FND_API.g_miss_char THEN
2359 x_complete_rec.enclosed_by := l_ams_import_rec.enclosed_by;
2360 END IF;
2361
2362 -- data_filename
2363 IF p_ams_import_rec.data_filename = FND_API.g_miss_char THEN
2364 x_complete_rec.data_filename := l_ams_import_rec.data_filename;
2365 END IF;
2366
2367 -- process_immed_flag
2368 IF p_ams_import_rec.process_immed_flag = FND_API.g_miss_char THEN
2372 -- dedupe_flag
2369 x_complete_rec.process_immed_flag := l_ams_import_rec.process_immed_flag;
2370 END IF;
2371
2373 IF p_ams_import_rec.dedupe_flag = FND_API.g_miss_char THEN
2374 x_complete_rec.dedupe_flag := l_ams_import_rec.dedupe_flag;
2375 END IF;
2376
2377 -- attribute_category
2378 IF p_ams_import_rec.attribute_category = FND_API.g_miss_char THEN
2379 x_complete_rec.attribute_category := l_ams_import_rec.attribute_category;
2380 END IF;
2381
2382 -- attribute1
2383 IF p_ams_import_rec.attribute1 = FND_API.g_miss_char THEN
2384 x_complete_rec.attribute1 := l_ams_import_rec.attribute1;
2385 END IF;
2386
2387 -- attribute2
2388 IF p_ams_import_rec.attribute2 = FND_API.g_miss_char THEN
2389 x_complete_rec.attribute2 := l_ams_import_rec.attribute2;
2390 END IF;
2391
2392 -- attribute3
2393 IF p_ams_import_rec.attribute3 = FND_API.g_miss_char THEN
2394 x_complete_rec.attribute3 := l_ams_import_rec.attribute3;
2395 END IF;
2396
2397 -- attribute4
2398 IF p_ams_import_rec.attribute4 = FND_API.g_miss_char THEN
2399 x_complete_rec.attribute4 := l_ams_import_rec.attribute4;
2400 END IF;
2401
2402 -- attribute5
2403 IF p_ams_import_rec.attribute5 = FND_API.g_miss_char THEN
2404 x_complete_rec.attribute5 := l_ams_import_rec.attribute5;
2405 END IF;
2406
2407 -- attribute6
2408 IF p_ams_import_rec.attribute6 = FND_API.g_miss_char THEN
2409 x_complete_rec.attribute6 := l_ams_import_rec.attribute6;
2410 END IF;
2411
2412 -- attribute7
2413 IF p_ams_import_rec.attribute7 = FND_API.g_miss_char THEN
2414 x_complete_rec.attribute7 := l_ams_import_rec.attribute7;
2415 END IF;
2416
2417 -- attribute8
2418 IF p_ams_import_rec.attribute8 = FND_API.g_miss_char THEN
2419 x_complete_rec.attribute8 := l_ams_import_rec.attribute8;
2420 END IF;
2421
2422 -- attribute9
2423 IF p_ams_import_rec.attribute9 = FND_API.g_miss_char THEN
2424 x_complete_rec.attribute9 := l_ams_import_rec.attribute9;
2425 END IF;
2426
2427 -- attribute10
2428 IF p_ams_import_rec.attribute10 = FND_API.g_miss_char THEN
2429 x_complete_rec.attribute10 := l_ams_import_rec.attribute10;
2430 END IF;
2431
2432 -- attribute11
2433 IF p_ams_import_rec.attribute11 = FND_API.g_miss_char THEN
2434 x_complete_rec.attribute11 := l_ams_import_rec.attribute11;
2435 END IF;
2436
2437 -- attribute12
2438 IF p_ams_import_rec.attribute12 = FND_API.g_miss_char THEN
2439 x_complete_rec.attribute12 := l_ams_import_rec.attribute12;
2440 END IF;
2441
2442 -- attribute13
2443 IF p_ams_import_rec.attribute13 = FND_API.g_miss_char THEN
2444 x_complete_rec.attribute13 := l_ams_import_rec.attribute13;
2445 END IF;
2446
2447 -- attribute14
2448 IF p_ams_import_rec.attribute14 = FND_API.g_miss_char THEN
2449 x_complete_rec.attribute14 := l_ams_import_rec.attribute14;
2450 END IF;
2451
2452 -- attribute15
2453 IF p_ams_import_rec.attribute15 = FND_API.g_miss_char THEN
2454 x_complete_rec.attribute15 := l_ams_import_rec.attribute15;
2455 END IF;
2456
2457
2458 -- custom_setup_id
2459 IF p_ams_import_rec.custom_setup_id = FND_API.g_miss_num THEN
2460 x_complete_rec.custom_setup_id := l_ams_import_rec.custom_setup_id;
2461 END IF;
2462
2463 -- country
2464 IF p_ams_import_rec.country = FND_API.g_miss_num THEN
2465 x_complete_rec.country := l_ams_import_rec.country;
2466 END IF;
2467
2468 -- usage
2469 IF p_ams_import_rec.usage = FND_API.g_miss_num THEN
2470 x_complete_rec.usage := l_ams_import_rec.usage;
2471 END IF;
2472
2473 -- number_of_records
2474 IF p_ams_import_rec.number_of_records = FND_API.g_miss_num THEN
2475 x_complete_rec.number_of_records := l_ams_import_rec.number_of_records;
2476 END IF;
2477
2478 -- data_file_name
2479 IF p_ams_import_rec.data_file_name = FND_API.g_miss_char THEN
2480 x_complete_rec.data_file_name := l_ams_import_rec.data_file_name;
2481 END IF;
2482
2483 -- b2b_flag
2484 IF p_ams_import_rec.b2b_flag = FND_API.g_miss_char THEN
2485 x_complete_rec.b2b_flag := l_ams_import_rec.b2b_flag;
2486 END IF;
2487
2488 -- rented_list_flag
2489 IF p_ams_import_rec.rented_list_flag = FND_API.g_miss_char THEN
2490 x_complete_rec.rented_list_flag := l_ams_import_rec.rented_list_flag;
2491 END IF;
2492
2493 -- server_flag
2494 IF p_ams_import_rec.server_flag = FND_API.g_miss_char THEN
2495 x_complete_rec.server_flag := l_ams_import_rec.server_flag;
2496 END IF;
2497
2498 -- log_file_name
2499 IF p_ams_import_rec.log_file_name = FND_API.g_miss_num THEN
2500 x_complete_rec.log_file_name := l_ams_import_rec.log_file_name;
2501 END IF;
2502
2503 -- number_of_failed_records
2504 IF p_ams_import_rec.number_of_failed_records = FND_API.g_miss_num THEN
2505 x_complete_rec.number_of_failed_records := l_ams_import_rec.number_of_failed_records;
2506 END IF;
2507
2508 -- number_of_duplicate_records
2509 IF p_ams_import_rec.number_of_duplicate_records = FND_API.g_miss_num THEN
2513 -- enable_word_replacement_flag
2510 x_complete_rec.number_of_duplicate_records := l_ams_import_rec.number_of_duplicate_records;
2511 END IF;
2512
2514 IF p_ams_import_rec.enable_word_replacement_flag = FND_API.g_miss_char THEN
2515 x_complete_rec.enable_word_replacement_flag := l_ams_import_rec.enable_word_replacement_flag;
2516 END IF;
2517
2518 -- Note: Developers need to modify the procedure
2519 -- to handle any business specific requirements.
2520 END Complete_ams_import_Rec;
2521 PROCEDURE Validate_import_list(
2522 p_api_version_number IN NUMBER,
2523 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2524 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2525 p_ams_import_rec IN ams_import_rec_type,
2526 x_return_status OUT NOCOPY VARCHAR2,
2527 x_msg_count OUT NOCOPY NUMBER,
2528 x_msg_data OUT NOCOPY VARCHAR2
2529 )
2530 IS
2531 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Import_List';
2532 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2533 l_object_version_number NUMBER;
2534 l_ams_import_rec AMS_Import_List_PVT.ams_import_rec_type;
2535
2536 BEGIN
2537 -- Standard Start of API savepoint
2538 SAVEPOINT VALIDATE_Import_List_;
2539 IF (AMS_DEBUG_HIGH_ON) THEN
2540
2541 AMS_UTILITY_PVT.debug_message('Private API: ' || 'inside validate_import_list');
2542 END IF;
2543
2544 -- Standard call to check for call compatibility.
2545 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2546 p_api_version_number,
2547 l_api_name,
2548 G_PKG_NAME)
2549 THEN
2550 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2551 END IF;
2552 IF (AMS_DEBUG_HIGH_ON) THEN
2553
2554 AMS_UTILITY_PVT.debug_message('Private API: ' || 'after Compatible_API_Call');
2555 END IF;
2556
2557 -- Initialize message list if p_init_msg_list is set to TRUE.
2558 IF FND_API.to_Boolean( p_init_msg_list )
2559 THEN
2560 FND_MSG_PUB.initialize;
2561 END IF;
2562 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
2563 Check_ams_import_Items(
2564 p_ams_import_rec => p_ams_import_rec,
2565 p_validation_mode => JTF_PLSQL_API.g_update,
2566 x_return_status => x_return_status
2567 );
2568
2569 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2570 RAISE FND_API.G_EXC_ERROR;
2571 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2572 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2573 END IF;
2574 END IF;
2575 IF (AMS_DEBUG_HIGH_ON) THEN
2576
2577 AMS_UTILITY_PVT.debug_message('Private API: ' || 'after Check_ams_import_Items');
2578 END IF;
2579
2580 Complete_ams_import_Rec(
2581 p_ams_import_rec => p_ams_import_rec,
2582 x_complete_rec => l_ams_import_rec
2583 );
2584 IF (AMS_DEBUG_HIGH_ON) THEN
2585
2586 AMS_UTILITY_PVT.debug_message('Private API: ' || 'after Complete_ams_import_Rec');
2587 END IF;
2588
2589 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
2590 Validate_ams_import_Rec(
2591 p_api_version_number => 1.0,
2592 p_init_msg_list => FND_API.G_FALSE,
2593 x_return_status => x_return_status,
2594 x_msg_count => x_msg_count,
2595 x_msg_data => x_msg_data,
2596 p_ams_import_rec => l_ams_import_rec);
2597
2598 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2599 RAISE FND_API.G_EXC_ERROR;
2600 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2602 END IF;
2603 END IF;
2604
2605
2606 -- Initialize API return status to SUCCESS
2607 x_return_status := FND_API.G_RET_STS_SUCCESS;
2608
2609
2610 -- Debug Message
2611 IF (AMS_DEBUG_HIGH_ON) THEN
2612
2613 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
2614 END IF;
2615
2616 -- Standard call to get message count and if count is 1, get message info.
2617 FND_MSG_PUB.Count_And_Get
2618 (p_count => x_msg_count,
2619 p_data => x_msg_data
2620 );
2621 IF (AMS_DEBUG_HIGH_ON) THEN
2622
2623 AMS_UTILITY_PVT.debug_message('after Count_And_Get');
2624 END IF;
2625
2626 EXCEPTION
2627
2628 WHEN AMS_Utility_PVT.resource_locked THEN
2629 x_return_status := FND_API.g_ret_sts_error;
2630 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
2631
2632 WHEN FND_API.G_EXC_ERROR THEN
2633 ROLLBACK TO VALIDATE_Import_List_;
2634 x_return_status := FND_API.G_RET_STS_ERROR;
2635 -- Standard call to get message count and if count=1, get the message
2636 FND_MSG_PUB.Count_And_Get (
2637 p_encoded => FND_API.G_FALSE,
2638 p_count => x_msg_count,
2639 p_data => x_msg_data
2640 );
2641
2642 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2643 ROLLBACK TO VALIDATE_Import_List_;
2644 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2645 -- Standard call to get message count and if count=1, get the message
2646 FND_MSG_PUB.Count_And_Get (
2647 p_encoded => FND_API.G_FALSE,
2648 p_count => x_msg_count,
2649 p_data => x_msg_data
2650 );
2651
2652 WHEN OTHERS THEN
2653 ROLLBACK TO VALIDATE_Import_List_;
2654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2655 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2656 THEN
2657 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2658 END IF;
2659 -- Standard call to get message count and if count=1, get the message
2660 FND_MSG_PUB.Count_And_Get (
2661 p_encoded => FND_API.G_FALSE,
2662 p_count => x_msg_count,
2663 p_data => x_msg_data
2664 );
2665 End Validate_Import_List;
2666
2667
2668 PROCEDURE Validate_ams_import_rec(
2669 p_api_version_number IN NUMBER,
2670 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2671 x_return_status OUT NOCOPY VARCHAR2,
2672 x_msg_count OUT NOCOPY NUMBER,
2673 x_msg_data OUT NOCOPY VARCHAR2,
2674 p_ams_import_rec IN ams_import_rec_type
2675 )
2676 IS
2677 BEGIN
2678 -- Initialize message list if p_init_msg_list is set to TRUE.
2679 IF FND_API.to_Boolean( p_init_msg_list )
2680 THEN
2681 FND_MSG_PUB.initialize;
2682 END IF;
2683
2684 -- Initialize API return status to SUCCESS
2685 x_return_status := FND_API.G_RET_STS_SUCCESS;
2686
2687 -- Hint: Validate data
2688 -- If data not valid
2689 -- THEN
2690 -- x_return_status := FND_API.G_RET_STS_ERROR;
2691
2692 -- Debug Message
2693 IF (AMS_DEBUG_HIGH_ON) THEN
2694
2695 AMS_UTILITY_PVT.debug_message('Private API: Validate_ams_import_rec');
2696 END IF;
2697 -- Standard call to get message count and if count is 1, get message info.
2698 FND_MSG_PUB.Count_And_Get
2699 (p_count => x_msg_count,
2700 p_data => x_msg_data
2701 );
2702 END Validate_ams_import_Rec;
2703
2704 END AMS_Import_List_PVT;