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