[Home] [Help]
PACKAGE BODY: APPS.CN_IMPORT_PVT
Source
1 PACKAGE BODY CN_IMPORT_PVT AS
2 -- $Header: cnvimpb.pls 120.3 2005/08/17 21:46:28 kjayapau noship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_IMPORT_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvimpb.pls';
6
7 --
8 -- API name : get_imp_type_code
9 --
10 FUNCTION get_imp_type_code (p_imp_header_id IN NUMBER)
11 RETURN VARCHAR2
12 IS
13 l_imp_type_code VARCHAR2(30) := NULL ;
14 BEGIN
15
16 SELECT import_type_code INTO l_imp_type_code
17 FROM cn_imp_headers
18 WHERE imp_header_id = p_imp_header_id;
19
20 RETURN l_imp_type_code;
21 EXCEPTION
22 WHEN no_data_found THEN
23 RETURN NULL;
24 END get_imp_type_code;
25
26 -- --------------------------------------------------------+
27 -- upd_impline_recnum
28 --
29 -- This procedure will update cn_imp_lines record_num
30 -- --------------------------------------------------------+
31 PROCEDURE upd_impline_recnum
32 (p_imp_header_id IN NUMBER) IS
33 PRAGMA AUTONOMOUS_TRANSACTION;
34
35 CURSOR c_imp_lines_csr IS
36 SELECT imp_line_id FROM cn_imp_lines
37 WHERE imp_header_id = p_imp_header_id
38 ORDER BY imp_line_id;
39
40 l_count NUMBER := 0;
41
42 BEGIN
43 FOR l_imp_lines_csr IN c_imp_lines_csr LOOP
44 l_count := l_count + 1;
45 UPDATE cn_imp_lines
46 SET record_num = l_count
47 WHERE imp_line_id = l_imp_lines_csr.imp_line_id
48 ;
49 END LOOP;
50 COMMIT;
51
52 END upd_impline_recnum;
53
54
55 -- Start of comments
56 -- API name : Import_Data
57 -- Type : Private.
58 -- Function : Main program to call all the concurrent programs
59 -- to transfer data from datafile to stage table then to
60 -- destination table
61 -- Pre-reqs : None.
62 -- Parameters :
63 -- Version : Current version 1.0
64 -- End of comments
65
66 PROCEDURE Import_Data
67 ( p_api_version IN NUMBER ,
68 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
69 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
70 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
71 x_return_status OUT NOCOPY VARCHAR2 ,
72 x_msg_count OUT NOCOPY NUMBER ,
73 x_msg_data OUT NOCOPY VARCHAR2 ,
74 p_imp_header_id IN NUMBER,
75 p_user_id IN NUMBER, -- setting the session context.
76 p_resp_id IN NUMBER, -- setting the session context.
77 p_app_id IN NUMBER, -- setting the session context.
78 p_control_file IN VARCHAR2,
79 x_request_id OUT NOCOPY NUMBER,
80 p_org_id IN NUMBER
81 ) IS
82 l_api_name CONSTANT VARCHAR2(30) := 'Import_Data';
83 l_api_version CONSTANT NUMBER := 1.0;
84
85 l_request_id NUMBER;
86 l_imp_type_code VARCHAR2(30);
87 l_imp_header cn_imp_headers_pvt.imp_headers_rec_type := cn_imp_headers_pvt.G_MISS_IMP_HEADERS_REC;
88 l_process_audit_id cn_process_audits.process_audit_id%TYPE;
89
90 BEGIN
91 -- Standard Start of API savepoint
92 SAVEPOINT Import_Data;
93 -- Standard call to check for call compatibility.
94 IF NOT FND_API.compatible_api_call
95 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
96 THEN
97 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
98 END IF;
99 -- Initialize message list if p_init_msg_list is set to TRUE.
100 IF FND_API.to_Boolean( p_init_msg_list ) THEN
101 FND_MSG_PUB.initialize;
102 END IF;
103 -- Initialize API return status to success
104 x_return_status := FND_API.G_RET_STS_SUCCESS;
105 -- API body
106 l_imp_type_code := get_imp_type_code(p_imp_header_id);
107 SELECT name, status_code,server_flag
108 INTO l_imp_header.name ,l_imp_header.status_code ,
109 l_imp_header.server_flag
110 FROM cn_imp_headers
111 WHERE imp_header_id = p_imp_header_id;
112
113 -- open process audit batch
114 cn_message_pkg.begin_batch
115 ( x_process_type => l_imp_type_code,
116 x_parent_proc_audit_id => p_imp_header_id ,
117 x_process_audit_id => l_process_audit_id,
118 x_request_id => null,
119 p_org_id => p_org_id);
120
121 cn_message_pkg.write
122 (p_message_text => 'Importing ' || l_imp_header.name,
123 p_message_type => 'MILESTONE'
124 );
125 ----------------------------------------------+
126 -- Set the session context equal to the
127 -- calling procedure.
128 -- NOTE: The session context needs to be
129 -- set at the PL/SQL level because the JSP
130 -- connection object did not include the
131 -- session context, so all calls through the
132 -- connection object are equivalent to calls
133 -- from a regular database login, not an
134 -- Apps session. Once this is fixed, we can
135 -- remove the call to apps_initialize.
136 ----------------------------------------------+
137 FND_GLOBAL.apps_initialize
138 (user_id => p_user_id,
139 resp_id => p_resp_id,
140 resp_appl_id => p_app_id
141 );
142
143 IF l_imp_header.server_flag = 'Y' THEN
144 -- Server Side Import. Need to Stage.
145 -- SQL*LOADER call to populate the data in OIC tables.
146 --
147
148 FND_REQUEST.SET_ORG_ID(p_org_id);
149
150 l_request_id :=
151 FND_REQUEST.SUBMIT_REQUEST
152 (application => 'CN',
153 program => 'CNIMPDS',
154 argument1 => p_imp_header_id,
155 argument2 => p_control_file,
156 argument3 => p_org_id
157 );
158
159 IF l_request_id = 0 THEN
160 update_imp_headers
161 (p_imp_header_id => p_imp_header_id,
162 p_status_code => 'FAIL');
163 cn_message_pkg.write
164 (p_message_text => 'CNIMPDS submission fail.',
165 p_message_type => 'ERROR');
166 RAISE FND_API.g_exc_unexpected_error;
167 END IF;
168
169 cn_message_pkg.write
170 (p_message_text => 'Staging Concurrent Program CNIMPDS Started.REQ = '|| l_request_id,
171 p_message_type => 'MILESTONE'
172 );
173
174 x_request_id := l_request_id;
175
176 ELSE
177 -- Client Side Import. Stage Completed.
178 -- Load_Data call to transfer from stage to target.
179 --
180 cn_message_pkg.debug('Status = '|| l_imp_header.status_code);
181
182 IF l_imp_header.status_code = 'STAGE' THEN
183
184 FND_REQUEST.SET_ORG_ID(p_org_id);
185
186 l_request_id :=
187 FND_REQUEST.SUBMIT_REQUEST
188 (application => 'CN',
189 program => 'CNIMPST',
190 argument1 => p_imp_header_id,
191 argument2 => p_org_id
192 );
193
194 IF l_request_id = 0 THEN
195 update_imp_headers
196 (p_imp_header_id => p_imp_header_id,
197 p_status_code => 'FAIL');
198 cn_message_pkg.write
199 (p_message_text => 'CNIMPST submission fail.',
200 p_message_type => 'ERROR');
201 RAISE FND_API.g_exc_unexpected_error;
202 END IF;
203
204 cn_message_pkg.write
205 (p_message_text => 'Loading Concurrent Program CNIMPST Started.REQ = '|| l_request_id,
206 p_message_type => 'MILESTONE'
207 );
208
209 x_request_id := l_request_id;
210 END IF;
211 END IF;
212
213 -- close process batch
214 cn_message_pkg.end_batch(l_process_audit_id);
215
216 -- End of API body.
217
218 -- Standard check of p_commit.
219 IF FND_API.To_Boolean( p_commit ) THEN
220 COMMIT WORK;
221 END IF;
222 -- Standard call to get message count and if count is 1, get message info.
223 FND_MSG_PUB.count_and_get
224 (p_count => x_msg_count ,
225 p_data => x_msg_data ,
226 p_encoded => FND_API.G_FALSE
227 );
228
229 EXCEPTION
230 WHEN FND_API.G_EXC_ERROR THEN
231 ROLLBACK TO Import_Data ;
232 x_return_status := FND_API.G_RET_STS_ERROR ;
233 cn_message_pkg.end_batch(l_process_audit_id);
234 FND_MSG_PUB.Count_And_Get(
235 p_count => x_msg_count ,
236 p_data => x_msg_data ,
237 p_encoded => FND_API.G_FALSE
238 );
239
240 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
241 ROLLBACK TO Import_Data ;
242 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
243 cn_message_pkg.end_batch(l_process_audit_id);
244 FND_MSG_PUB.Count_And_Get(
245 p_count => x_msg_count ,
246 p_data => x_msg_data ,
247 p_encoded => FND_API.G_FALSE
248 );
249
250 WHEN OTHERS THEN
251 ROLLBACK TO Import_Data ;
252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
253 cn_message_pkg.set_error(l_api_name,'Unexpected error');
254 cn_message_pkg.end_batch(l_process_audit_id);
255 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
256 THEN
257 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
258 END IF;
259 FND_MSG_PUB.Count_And_Get
260 (
261 p_count => x_msg_count ,
262 p_data => x_msg_data ,
263 p_encoded => FND_API.G_FALSE
264 );
265 END import_data;
266
267 -- Start of comments
268 -- API name : Export_Data
269 -- Type : Private.
270 -- Function : Main program to call all the concurrent programs
271 -- to transfer data from destination file to stage table
272 -- Pre-reqs : None.
273 -- Version : Current version 1.0
274 -- End of comments
275 PROCEDURE Export_Data
276 ( p_api_version IN NUMBER ,
277 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
278 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
279 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
280 x_return_status OUT NOCOPY VARCHAR2 ,
281 x_msg_count OUT NOCOPY NUMBER ,
282 x_msg_data OUT NOCOPY VARCHAR2 ,
283 p_imp_header_id IN NUMBER,
284 p_user_id IN NUMBER, -- setting the session context.
285 p_resp_id IN NUMBER, -- setting the session context.
286 p_app_id IN NUMBER, -- setting the session context.
287 x_request_id OUT NOCOPY NUMBER,
288 p_org_id IN NUMBER) IS
289
290 l_api_name CONSTANT VARCHAR2(30) := 'Export_Data';
291 l_api_version CONSTANT NUMBER := 1.0;
292
293 l_request_id NUMBER;
294 l_imp_type_code VARCHAR2(30);
295 l_name VARCHAR2(30);
296 l_status_code VARCHAR2(30);
297 l_conc_pgm VARCHAR2(150);
298 l_process_audit_id cn_process_audits.process_audit_id%TYPE;
299
300 BEGIN
301 -- Standard Start of API savepoint
302 SAVEPOINT Export_Data;
303 -- Standard call to check for call compatibility.
304 IF NOT FND_API.compatible_api_call
305 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
306 THEN
307 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
308 END IF;
309 -- Initialize message list if p_init_msg_list is set to TRUE.
310 IF FND_API.to_Boolean( p_init_msg_list ) THEN
311 FND_MSG_PUB.initialize;
312 END IF;
313 -- Initialize API return status to success
314 x_return_status := FND_API.G_RET_STS_SUCCESS;
315 -- API body
316
317 l_imp_type_code := get_imp_type_code(p_imp_header_id);
318
319 SELECT name, status_code
320 INTO l_name ,l_status_code
321 FROM cn_imp_headers
322 WHERE imp_header_id = p_imp_header_id;
323
324 SELECT export_conc_program
325 INTO l_conc_pgm
326 FROM cn_import_types
327 WHERE import_type_code = l_imp_type_code;
328
329 -- open process audit batch
330 cn_message_pkg.begin_batch
331 ( x_process_type => l_imp_type_code,
332 x_parent_proc_audit_id => p_imp_header_id ,
333 x_process_audit_id => l_process_audit_id,
334 x_request_id => null,
335 p_org_id => p_org_id);
336
337 cn_message_pkg.write
338 (p_message_text => 'Exporting ' || l_name,
339 p_message_type => 'MILESTONE');
340
341 ----------------------------------------------+
342 -- Set the session context equal to the
343 -- calling procedure.
344 -- NOTE: The session context needs to be
345 -- set at the PL/SQL level because the JSP
346 -- connection object did not include the
347 -- session context, so all calls through the
348 -- connection object are equivalent to calls
349 -- from a regular database login, not an
350 -- Apps session. Once this is fixed, we can
351 -- remove the call to apps_initialize.
352 ----------------------------------------------+
353 FND_GLOBAL.apps_initialize
354 (user_id => p_user_id,
355 resp_id => p_resp_id,
356 resp_appl_id => p_app_id);
357
358 -- say request has been submitted
359 update_imp_headers
360 (p_imp_header_id => p_imp_header_id,
361 p_status_code => 'SUBMIT');
362
363 -- Call request to export data to the stage table
364
365 FND_REQUEST.SET_ORG_ID(p_org_id);
366
367 l_request_id :=
368 FND_REQUEST.SUBMIT_REQUEST
369 (application => 'CN',
370 program => l_conc_pgm,
371 argument1 => p_imp_header_id,
372 argument2 => p_org_id);
373
374 IF l_request_id = 0 THEN
375 update_imp_headers
376 (p_imp_header_id => p_imp_header_id,
377 p_status_code => 'FAIL');
378 cn_message_pkg.write
379 (p_message_text => l_conc_pgm || ' submission fail.',
380 p_message_type => 'ERROR');
381 RAISE FND_API.g_exc_unexpected_error;
382 END IF;
383
384 cn_message_pkg.write
385 (p_message_text => 'Loading Concurrent Program ' || l_conc_pgm ||
386 ' Started.REQ = '|| l_request_id,
387 p_message_type => 'MILESTONE');
388
389 x_request_id := l_request_id;
390
391 -- close process batch
392 cn_message_pkg.end_batch(l_process_audit_id);
393
394 -- End of API body.
395
396 -- Standard check of p_commit.
397 IF FND_API.To_Boolean( p_commit ) THEN
398 COMMIT WORK;
399 END IF;
400 -- Standard call to get message count and if count is 1, get message info.
401 FND_MSG_PUB.count_and_get
402 (p_count => x_msg_count ,
403 p_data => x_msg_data ,
404 p_encoded => FND_API.G_FALSE
405 );
406
407 EXCEPTION
408 WHEN FND_API.G_EXC_ERROR THEN
409 ROLLBACK TO Export_Data ;
410 x_return_status := FND_API.G_RET_STS_ERROR ;
411 cn_message_pkg.end_batch(l_process_audit_id);
412 FND_MSG_PUB.Count_And_Get(
413 p_count => x_msg_count ,
414 p_data => x_msg_data ,
415 p_encoded => FND_API.G_FALSE
416 );
417
418 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
419 ROLLBACK TO Export_Data ;
420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
421 cn_message_pkg.end_batch(l_process_audit_id);
422 FND_MSG_PUB.Count_And_Get(
423 p_count => x_msg_count ,
424 p_data => x_msg_data ,
425 p_encoded => FND_API.G_FALSE
426 );
427
428 WHEN OTHERS THEN
429 ROLLBACK TO Export_Data ;
430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
431 cn_message_pkg.set_error(l_api_name,'Unexpected error');
432 cn_message_pkg.end_batch(l_process_audit_id);
433 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
434 THEN
435 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
436 END IF;
437 FND_MSG_PUB.Count_And_Get
438 (
439 p_count => x_msg_count ,
440 p_data => x_msg_data ,
441 p_encoded => FND_API.G_FALSE
442 );
443 END Export_Data;
444
445 -- --------------------------------------------------------+
446 -- This program invokes sql*loader from concurrent program
447 -- to populate the data from the data file to the OIC application.
448 -- --------------------------------------------------------+
449
450 PROCEDURE Server_Stage_data
451 (errbuf OUT NOCOPY VARCHAR2,
452 retcode OUT NOCOPY NUMBER,
453 p_imp_header_id IN NUMBER,
454 p_control_file IN VARCHAR2,
455 p_org_id IN NUMBER
456 ) IS
457
458 L_SQL_LOADER CONSTANT VARCHAR2(30) := 'SQL*Loader';
459 l_short_name VARCHAR2(30);
460 l_request_id NUMBER;
461 l_wait_status BOOLEAN;
462 l_phase VARCHAR2(30);
463 l_status VARCHAR2(30);
464 l_start_time DATE;
465 l_dev_phase VARCHAR2(30);
466 l_dev_status VARCHAR2(30);
467 l_message VARCHAR2(240);
468 l_loaded_rows NUMBER;
469 l_imp_header cn_imp_headers_pvt.imp_headers_rec_type := cn_imp_headers_pvt.G_MISS_IMP_HEADERS_REC;
470 l_imp_type_code VARCHAR2(30);
471 l_msg_count NUMBER := 0;
472 l_process_audit_id cn_process_audits.process_audit_id%TYPE;
473 l_api_name CONSTANT VARCHAR2(30) := 'Server_Stage_data';
474 l_api_version CONSTANT NUMBER := 1.0;
475 err_num NUMBER;
476
477 CURSOR c_loaded_rows IS
478 SELECT COUNT(*)
479 FROM cn_imp_lines
480 WHERE imp_header_id = p_imp_header_id;
481
482 BEGIN
483 -- Standard Start of API savepoint
484 -- Initialize API return status to success
485 -- API body
486
487 update_imp_headers
488 (p_imp_header_id => p_imp_header_id,
489 p_status_code => 'SUBMIT');
490
491 retcode := 0; -- no problems. 1= warning, 2 = fail
492 l_imp_type_code := get_imp_type_code(p_imp_header_id);
493
494 -- For SQL*Loader programs, the executable file name is equivalent to the
495 -- control file. The field "Executable" on the screen is the same as
496 -- "program" in the API. We will use the short name for both program and
497 -- program short name.
498
499 l_short_name := 'CN' || FND_GLOBAL.LOGIN_ID || 'IMP' || p_imp_header_id;
500
501 SELECT name, status_code,server_flag
502 INTO l_imp_header.name ,l_imp_header.status_code ,
503 l_imp_header.server_flag
504 FROM cn_imp_headers
505 WHERE imp_header_id = p_imp_header_id;
506
507 -- open process audit batch
508 cn_message_pkg.begin_batch
509 ( x_process_type => l_imp_type_code,
510 x_parent_proc_audit_id => p_imp_header_id ,
511 x_process_audit_id => l_process_audit_id,
512 x_request_id => null,
513 p_org_id => p_org_id);
514
515 cn_message_pkg.write
516 (p_message_text => 'Start Server Staging Data : Server_Stage_data - ' || l_short_name,
517 p_message_type => 'MILESTONE'
518 );
519
520 -- Start Staging Data
521 IF l_imp_header.status_code = 'SUBMIT' AND l_imp_header.server_flag = 'Y' THEN
522 -- For SQL*Loader Conc Pgm,the control must locate at $CN_TOP/bin/
523 -- the execution_file_name cannot include any . or space
524 -- Create the Executable entry.
525 FND_PROGRAM.EXECUTABLE
526 ( executable => l_short_name,
527 application => 'CN',
528 short_name => l_short_name,
529 description => l_imp_header.name,
530 execution_method => L_SQL_LOADER,
531 execution_file_name => p_control_file,
532 language_code => USERENV ('LANG')
533 );
534
535 cn_message_pkg.debug('Staging Data : Conc Executable Created - ' || l_short_name);
536
537 -- Register the concurrent program.
538 FND_PROGRAM.REGISTER
539 (program => l_short_name,
540 application => 'CN',
541 enabled => 'Y',
542 short_name => l_short_name,
543 executable_short_name => l_short_name,
544 executable_application => 'CN',
545 language_code => USERENV ('LANG')
546 );
547
548 cn_message_pkg.debug('Staging Data : Conc Program Created - ' || l_short_name);
549
550 -- Since this is a SQL*Loader concurrent program,
551 -- we don't need to specify other parameters. The
552 -- only other parameter would be the data file name,
553 -- but we will include that in the control file.
554 --
555 l_request_id := FND_REQUEST.SUBMIT_REQUEST
556 (application => 'CN',
557 program => l_short_name
558 );
559
560 IF l_request_id = 0 THEN
561 FND_MESSAGE.set_name ('CN', 'CN_IMP_DS_SUBMIT_FAIL');
562 cn_message_pkg.write
563 (p_message_text => fnd_message.get_string('CN','CN_IMP_DS_SUBMIT_FAIL'),
564 p_message_type => 'ERROR');
565 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
566
567 ELSE
568 cn_message_pkg.debug('Staging Data : SQL*Loader Submited. Request ID = ' || l_request_id);
569 END IF;
570 -- when submit conc reg from pl/sql, a COMMIT is required
571 -- **** DO NOT REMOVE ****
572 COMMIT;
573
574 -- The sql*loader concurrent program MUST finish
575 -- before invoking the destination application
576 -- concurrent program
577 -- This is also required for the clean up process.
578
579 l_wait_status := FND_CONCURRENT.WAIT_FOR_REQUEST
580 (request_id => l_request_id,
581 phase => l_phase,
582 status => l_status,
583 dev_phase => l_dev_phase,
584 dev_status => l_dev_status,
585 message => l_message
586 );
587
588 ---------------------------------------------+
589 -- WAIT_STATUS should only come back as
590 -- TRUE. It only comes back as FALSE if
591 -- the conc request was not successfully
592 -- submitted.
593 ---------------------------------------------+
594 IF NOT l_wait_status THEN
595 FND_MESSAGE.set_name ('CN', 'CN_IMP_DS_SUBMIT_FAIL');
596 cn_message_pkg.write
597 (p_message_text => fnd_message.get_string('CN','CN_IMP_DS_SUBMIT_FAIL'),
598 p_message_type => 'ERROR');
599 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
600 END IF;
601
602 IF l_dev_phase <> 'COMPLETE' OR l_dev_status <> 'NORMAL' THEN
603 -- Conc req complete with error or not complete
604 FND_MESSAGE.set_name ('CN', 'CN_IMP_DS_FAIL');
605 update_imp_headers
606 (p_imp_header_id => p_imp_header_id,
607 p_status_code => 'STAGE_FAIL');
608 cn_message_pkg.write
609 (p_message_text => fnd_message.get_string('CN','CN_IMP_DS_FAIL'),
610 p_message_type => 'ERROR');
611 -- raise error after clean up creaated conc
612 GOTO delete_conc;
613 -- RAISE FND_API.g_exc_unexpected_error;
614 END IF;
615
616 cn_message_pkg.debug('Staging Data : SQL*Loader Finished.');
617
618 -- UPDATE cn_imp_headers
619 OPEN c_loaded_rows;
620 FETCH c_loaded_rows INTO l_loaded_rows;
621 CLOSE c_loaded_rows;
622
623 update_imp_headers
624 (p_imp_header_id => p_imp_header_id,
625 p_status_code => 'STAGE',
626 p_staged_row => l_loaded_rows);
627
628 << delete_conc >>
629 -- Clean-up the concurrent programs which were created
630 -- during program execution.
631 FND_PROGRAM.DELETE_PROGRAM
632 (program_short_name => l_short_name,
633 application => 'CN'
634 );
635 cn_message_pkg.debug('Staging Data : Delete Conc Program.');
636
637 --
638 -- The "executable" will be the same as the program short name
639 -- for all run-time generated concurrent programs. The registered
640 -- program must be deleted before the executable can be.
641 FND_PROGRAM.DELETE_EXECUTABLE
642 (executable_short_name => l_short_name,
643 application => 'CN'
644 );
645 cn_message_pkg.debug('Staging Data : Delete Conc Executable.');
646
647 IF l_dev_phase <> 'COMPLETE' OR l_dev_status <> 'NORMAL' THEN
648 -- Conc req complete with error or not complete
649 -- raise error after clean up created conc
650 RAISE FND_API.g_exc_unexpected_error;
651 END IF;
652
653 END IF; -- end status_code = 'SUBMIT' AND server_flag = 'Y'
654
655 cn_message_pkg.write
656 (p_message_text => 'End Staging Data : Server_Stage_data - ' || l_short_name,
657 p_message_type => 'MILESTONE');
658
659 -- close process batch
660 cn_message_pkg.end_batch(l_process_audit_id);
661
662 -- Get imp_header status
663 SELECT name, status_code,server_flag
664 INTO l_imp_header.name ,l_imp_header.status_code ,l_imp_header.server_flag
665 FROM cn_imp_headers
666 WHERE imp_header_id = p_imp_header_id;
667
668 -- Start Loading Data
669 IF l_imp_header.status_code = 'STAGE' THEN
670 Load_Data
671 (errbuf => errbuf,
672 retcode => retcode,
673 p_imp_header_id => p_imp_header_id,
674 p_org_id => p_org_id
675 );
676
677 END IF ;
678
679 --errbuf := SUBSTR (FND_MESSAGE.GET, 1, 240);
680 -- End of API body.
681
682 EXCEPTION
683 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
684 retcode := 2 ;
685 update_imp_headers
686 (p_imp_header_id => p_imp_header_id,
687 p_status_code => 'STAGE_FAIL');
688 cn_message_pkg.end_batch(l_process_audit_id);
689 FND_MSG_PUB.Count_And_Get(
690 p_count => l_msg_count ,
691 p_data => errbuf ,
692 p_encoded => FND_API.G_FALSE
693 );
694
695 WHEN OTHERS THEN
696 update_imp_headers
697 (p_imp_header_id => p_imp_header_id,
698 p_status_code => 'STAGE_FAIL');
699 err_num := SQLCODE;
700 IF err_num = -6501 THEN
701 retcode := 2 ;
702 errbuf := fnd_program.message;
703 ELSE
704 retcode := 2 ;
705 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
706 THEN
707 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
708 END IF;
709 FND_MSG_PUB.count_and_get
710 (p_count => l_msg_count ,
711 p_data => errbuf ,
712 p_encoded => FND_API.G_FALSE
713 );
714 END IF;
715 cn_message_pkg.set_error(l_api_name,errbuf);
716 cn_message_pkg.end_batch(l_process_audit_id);
717
718 END Server_Stage_data;
719
720 -- --------------------------------------------------------+
721 -- This program invokes concurrent program base on import type
722 -- to populate the data from the staging table into destination table
723 -- --------------------------------------------------------+
724
725 PROCEDURE Load_Data
726 (errbuf OUT NOCOPY VARCHAR2,
727 retcode OUT NOCOPY NUMBER,
728 p_imp_header_id IN NUMBER,
729 p_org_id IN NUMBER
730 ) IS
731
732 l_short_name VARCHAR2(30);
733 l_request_id NUMBER;
734 l_wait_status BOOLEAN;
735 l_phase VARCHAR2(30);
736 l_status VARCHAR2(30);
737 l_start_time DATE;
738 l_dev_phase VARCHAR2(30);
739 l_dev_status VARCHAR2(30);
740 l_message VARCHAR2(240);
741 l_loaded_rows NUMBER;
742
743 l_imp_header cn_imp_headers_pvt.imp_headers_rec_type := cn_imp_headers_pvt.G_MISS_IMP_HEADERS_REC;
744 l_process_audit_id cn_process_audits.process_audit_id%TYPE;
745 l_imp_type_code VARCHAR2(30);
746 l_conc_program VARCHAR2(30);
747
748 l_msg_count NUMBER := 0;
749
750 l_api_name CONSTANT VARCHAR2(30) := 'Load_Data';
751 l_api_version CONSTANT NUMBER := 1.0;
752 err_num NUMBER;
753
754 CURSOR c_conc_name IS
755 SELECT conc_program FROM cn_import_types
756 WHERE import_type_code = l_imp_type_code;
757
758
759 BEGIN
760 -- Standard Start of API savepoint
761 -- Initialize API return status to success
762 -- API body
763
764 retcode := 0; -- no problems. 1= warning, 2 = fail
765 l_imp_type_code := get_imp_type_code(p_imp_header_id);
766 OPEN c_conc_name;
767 FETCH c_conc_name INTO l_conc_program;
768 CLOSE c_conc_name;
769
770 SELECT name, status_code,server_flag
771 INTO l_imp_header.name ,l_imp_header.status_code,
772 l_imp_header.server_flag
773 FROM cn_imp_headers
774 WHERE imp_header_id = p_imp_header_id;
775
776 -- open process audit batch
777 cn_message_pkg.begin_batch
778 ( x_process_type => l_imp_type_code,
779 x_parent_proc_audit_id => p_imp_header_id ,
780 x_process_audit_id => l_process_audit_id,
781 x_request_id => null,
782 p_org_id => p_org_id);
783
784 cn_message_pkg.write
785 (p_message_text => 'Start Loading Data : Load_Data - ' || l_conc_program,
786 p_message_type => 'MILESTONE');
787
788 -- Start Loading Data
789 IF l_imp_header.status_code = 'STAGE' THEN
790 -- Update record_num in cn_imp_lines
791 upd_impline_recnum (p_imp_header_id => p_imp_header_id);
792
793 -- set status = SCHEDULE
794 update_imp_headers
795 (p_imp_header_id => p_imp_header_id,
796 p_status_code => 'SCHEDULE');
797
798 -- Submit conc req
799 FND_REQUEST.SET_ORG_ID(p_org_id);
800
801 l_request_id := FND_REQUEST.SUBMIT_REQUEST
802 (application => 'CN',
803 program => l_conc_program,
804 argument1 => p_imp_header_id,
805 argument2 => p_org_id
806 );
807
808 IF l_request_id = 0 THEN
809 FND_MESSAGE.set_name ('CN', 'CN_IMP_ST_SUBMIT_FAIL');
810 cn_message_pkg.write
811 (p_message_text => fnd_message.get_string('CN','CN_IMP_ST_SUBMIT_FAIL') || ' ' || l_conc_program,
812 p_message_type => 'ERROR');
813 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
814
815 ELSE
816 cn_message_pkg.debug('Loading Data : Conc Pgm Submited. Request ID = ' || l_request_id);
817 END IF;
818 -- when submit conc reg from pl/sql, a COMMIT is required
819 -- **** DO NOT REMOVE ****
820 COMMIT;
821
822 -- The concurrent program MUST finish
823 -- before update import status
824
825 l_wait_status := FND_CONCURRENT.WAIT_FOR_REQUEST
826 (request_id => l_request_id,
827 phase => l_phase,
828 status => l_status,
829 dev_phase => l_dev_phase,
830 dev_status => l_dev_status,
831 message => l_message
832 );
833
834 ---------------------------------------------+
835 -- WAIT_STATUS should only come back as
836 -- TRUE. It only comes back as FALSE if
837 -- the conc request was not successfully
838 -- submitted.
839 ---------------------------------------------+
840 IF NOT l_wait_status THEN
841 FND_MESSAGE.set_name ('CN', 'CN_IMP_ST_SUBMIT_FAIL');
842 cn_message_pkg.write
843 (p_message_text => fnd_message.get_string('CN','CN_IMP_ST_SUBMIT_FAIL'),
844 p_message_type => 'ERROR');
845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
846 END IF;
847
848 IF l_dev_phase <> 'COMPLETE' OR l_dev_status <> 'NORMAL' THEN
849 -- Conc req complete with error or not complete
850 FND_MESSAGE.set_name ('CN', 'CN_IMP_ST_FAIL');
851 update_imp_headers
852 (p_imp_header_id => p_imp_header_id,
853 p_status_code => 'IMPORT_FAIL');
854 cn_message_pkg.write
855 (p_message_text => fnd_message.get_string('CN','CN_IMP_ST_FAIL'),
856 p_message_type => 'ERROR');
857 RAISE FND_API.g_exc_unexpected_error;
858 END IF;
859
860 cn_message_pkg.debug('Loading Data : Load Data Conc Pgm Finished.');
861 END IF; -- end status_code = 'STAGE'
862 cn_message_pkg.write
863 (p_message_text => 'End Loading Data : Load_Data - ' || l_conc_program,
864 p_message_type => 'MILESTONE'
865 );
866
867 -- Close process audit batch
868 cn_message_pkg.end_batch(l_process_audit_id);
869
870 --errbuf := SUBSTR (FND_MESSAGE.GET, 1, 240);
871 -- End of API body.
872
873 EXCEPTION
874 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
875 retcode := 2 ;
876 update_imp_headers
877 (p_imp_header_id => p_imp_header_id,
878 p_status_code => 'IMPORT_FAIL');
879 cn_message_pkg.end_batch(l_process_audit_id);
880 FND_MSG_PUB.Count_And_Get(
881 p_count => l_msg_count ,
882 p_data => errbuf ,
883 p_encoded => FND_API.G_FALSE
884 );
885
886 WHEN OTHERS THEN
887 update_imp_headers
888 (p_imp_header_id => p_imp_header_id,
889 p_status_code => 'IMPORT_FAIL');
890 err_num := SQLCODE;
891 IF err_num = -6501 THEN
892 retcode := 2 ;
893 errbuf := fnd_program.message;
894 ELSE
895 retcode := 2 ;
896 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
897 THEN
898 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
899 END IF;
900 FND_MSG_PUB.count_and_get
901 (p_count => l_msg_count ,
902 p_data => errbuf ,
903 p_encoded => FND_API.G_FALSE
904 );
905 END IF;
906 cn_message_pkg.set_error(l_api_name,errbuf);
907 cn_message_pkg.end_batch(l_process_audit_id);
908
909 END Load_Data;
910
911 -- Start of comments
912 -- API name : Client_Stage_data
913 -- Type : Private.
914 -- Function : Main program to call CN_IMPORT_CLIENT_PVT
915 -- to transfer data from datafile to stage table
916 -- Pre-reqs : None.
917 -- Parameters :
918 -- Version : Current version 1.0
919 --
920 -- Notes :
921 -- The "p_data" contains all data needed to be inserted, assuming all data
922 -- types are "VARCHAR2". For example, if the data to be inserted are the
923 -- followings:
924 --
925 -- Row Number 1 2 3 4
926 -- Column1 Frank Smith Scott Marry
927 -- Column2 Amos Anderson Baber Beier
928 -- Column3 75039 77002 23060 03062
929 --
930 -- The data is stored in the "p_data" as:"Frank", "Smith", "Scott","Marry",
931 -- "Amos", "Anderson", "Baber", "Beier", "75039", "77002", "23060","03062".
932 --
933 -- End of comments
934
935 PROCEDURE Client_Stage_Data
936 ( p_api_version IN NUMBER ,
937 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
938 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
939 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
940 x_return_status OUT NOCOPY VARCHAR2 ,
941 x_msg_count OUT NOCOPY NUMBER ,
942 x_msg_data OUT NOCOPY VARCHAR2 ,
943 p_imp_header_id IN NUMBER,
944 p_data IN CHAR_DATA_SET_TYPE,
945 p_row_count IN NUMBER,
946 p_map_obj_ver IN NUMBER,
947 p_org_id IN NUMBER
948 )
949 IS
950 l_api_name CONSTANT VARCHAR2(30) := 'Client_Stage_Data';
951 l_api_version CONSTANT NUMBER := 1.0;
952
953 l_imp_type_code VARCHAR2(30);
954 l_imp_header cn_imp_headers_pvt.imp_headers_rec_type := cn_imp_headers_pvt.G_MISS_IMP_HEADERS_REC;
955 l_process_audit_id cn_process_audits.process_audit_id%TYPE;
956
957 CURSOR c_tar_col_cv(c_imp_map_id NUMBER) IS
958 SELECT f.source_column,f.target_column_name,f.target_table_name,
959 m.object_version_number map_obj_ver
960 FROM cn_imp_map_fields f, cn_imp_maps m
961 WHERE m.imp_map_id = c_imp_map_id
962 AND f.imp_map_id = m.imp_map_id
963 ORDER BY f.source_column
964 ;
965
966 CURSOR c_loaded_rows IS
967 SELECT COUNT(*)
968 FROM cn_imp_lines
969 WHERE imp_header_id = p_imp_header_id;
970 l_loaded_rows NUMBER;
971
972 l_data char_data_set_type;
973 l_columns char_data_set_type;
974 l_row_count NUMBER := 0;
975 l_tar_index NUMBER := 1;
976 l_src_index NUMBER := 1;
977 l_index NUMBER := 1;
978 l_target_table VARCHAR2(30);
979 errbuf VARCHAR2(2000);
980 retcode NUMBER;
981
982 BEGIN
983 -- Standard Start of API savepoint
984 SAVEPOINT Client_Stage_Data;
985 -- Standard call to check for call compatibility.
986 IF NOT FND_API.compatible_api_call
987 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
988 THEN
989 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
990 END IF;
991 -- Initialize message list if p_init_msg_list is set to TRUE.
992 IF FND_API.to_Boolean( p_init_msg_list ) THEN
993 FND_MSG_PUB.initialize;
994 END IF;
995 -- Initialize API return status to success
996 x_return_status := FND_API.G_RET_STS_SUCCESS;
997 -- API body
998 l_imp_type_code := get_imp_type_code(p_imp_header_id);
999
1000 -- Get imp_header info
1001 SELECT name, status_code,server_flag,imp_map_id, source_column_num
1002 INTO l_imp_header.name ,l_imp_header.status_code ,
1003 l_imp_header.server_flag, l_imp_header.imp_map_id,
1004 l_imp_header.source_column_num
1005 FROM cn_imp_headers
1006 WHERE imp_header_id = p_imp_header_id;
1007
1008 -- Check if Client_Stage_Data has been process multiple time, means import
1009 -- data has been divide into multiple chunks and each chunk will call
1010 -- Client_Stage_Data. Do not reset status if previous chunk of data fail
1011 IF l_imp_header.status_code <> 'STAGE_FAIL' THEN
1012 -- set/reset status to 'SUBMIT'
1013 update_imp_headers
1014 (p_imp_header_id => p_imp_header_id,
1015 p_status_code => 'SUBMIT');
1016 l_imp_header.status_code := 'SUBMIT';
1017 END IF;
1018
1019 -- open process audit batch
1020 cn_message_pkg.begin_batch
1021 ( x_process_type => l_imp_type_code,
1022 x_parent_proc_audit_id => p_imp_header_id ,
1023 x_process_audit_id => l_process_audit_id,
1024 x_request_id => null,
1025 p_org_id => p_org_id);
1026
1027 cn_message_pkg.write
1028 (p_message_text => 'Start Client Staging Data : Client_Stage_data ' ,
1029 p_message_type => 'MILESTONE');
1030
1031 -- Start staging
1032 IF l_imp_header.status_code = 'SUBMIT' AND l_imp_header.server_flag = 'N'
1033 AND l_imp_header.imp_map_id IS NOT NULL
1034 THEN
1035 -- re-build p_data to remove unmapped columns
1036 l_row_count := p_data.COUNT / l_imp_header.source_column_num;
1037 -- for each mapped column
1038 FOR c_tar_col IN c_tar_col_cv(l_imp_header.imp_map_id) LOOP
1039 -- check if mapping is updated by other user
1040 IF c_tar_col.map_obj_ver <> p_map_obj_ver THEN
1041 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1042 THEN
1043 FND_MESSAGE.SET_NAME ('CN' , 'CN_IMP_MAPPED_CHANGED');
1044 FND_MSG_PUB.Add;
1045 END IF;
1046 cn_message_pkg.write
1047 (p_message_text => fnd_message.get_string('CN','CN_IMP_MAPPED_CHANGED'),
1048 p_message_type => 'ERROR');
1049 RAISE FND_API.G_EXC_ERROR ;
1050 END IF;
1051 -- build column list
1052 l_columns(l_index) := c_tar_col.target_column_name;
1053 l_index := l_index + 1;
1054 l_target_table := c_tar_col.target_table_name;
1055 -- copy data from p_data into l_data
1056 l_src_index := (To_number(c_tar_col.source_column) - 1) * l_row_count + 1;
1057 FOR i IN 1 .. l_row_count LOOP
1058 l_data(l_tar_index) := p_data(l_src_index);
1059 l_tar_index := l_tar_index + 1;
1060 l_src_index := l_src_index + 1;
1061 END LOOP; -- end l_row_count LOOP
1062 END LOOP; -- End FOR c_tar_col IN c_tar_col_cv LOOP
1063
1064 cn_message_pkg.debug('Staging Data : Unmapped data dropped.');
1065
1066 -- Insert data into stage table
1067 cn_import_client_pvt.insert_data
1068 (p_api_version => 1.0,
1069 p_imp_header_id => p_imp_header_id,
1070 p_import_type_code => l_imp_type_code,
1071 p_table_name => l_target_table,
1072 p_col_names => l_columns,
1073 p_data => l_data,
1074 p_row_count => l_row_count,
1075 x_return_status => x_return_status,
1076 x_msg_count => x_msg_count,
1077 x_msg_data => x_msg_data);
1078
1079 IF x_return_status <> FND_API.g_ret_sts_success THEN
1080 RAISE FND_API.G_EXC_ERROR;
1081 END IF;
1082 -- UPDATE cn_imp_headers
1083 OPEN c_loaded_rows;
1084 FETCH c_loaded_rows INTO l_loaded_rows;
1085 CLOSE c_loaded_rows;
1086 -- data may cut into 3600 element chuncks, l_row_count is not necessary
1087 -- staged_row
1088 update_imp_headers
1089 (p_imp_header_id => p_imp_header_id,
1090 p_status_code => 'STAGE',
1091 p_staged_row => l_loaded_rows);
1092
1093 END IF; -- End IF l_imp_header.status_code = 'SUBMIT'
1094
1095 cn_message_pkg.write
1096 (p_message_text => 'staged row = ' || l_row_count ,
1097 p_message_type => 'DEBUG');
1098
1099 cn_message_pkg.write
1100 (p_message_text => 'End Client Staging Data : Client_Stage_data ' ,
1101 p_message_type => 'MILESTONE');
1102
1103 -- close process batch
1104 cn_message_pkg.end_batch(l_process_audit_id);
1105
1106 -- End of API body.
1107
1108 -- Standard check of p_commit.
1109 IF FND_API.To_Boolean( p_commit ) THEN
1110 COMMIT WORK;
1111 END IF;
1112 -- Standard call to get message count and if count is 1, get message info.
1113 FND_MSG_PUB.count_and_get
1114 (p_count => x_msg_count ,
1115 p_data => x_msg_data ,
1116 p_encoded => FND_API.G_FALSE
1117 );
1118
1119 EXCEPTION
1120 WHEN FND_API.G_EXC_ERROR THEN
1121 ROLLBACK TO Client_Stage_Data ;
1122 update_imp_headers
1123 (p_imp_header_id => p_imp_header_id,
1124 p_status_code => 'STAGE_FAIL');
1125 x_return_status := FND_API.G_RET_STS_ERROR ;
1126 cn_message_pkg.end_batch(l_process_audit_id);
1127 FND_MSG_PUB.Count_And_Get(
1128 p_count => x_msg_count ,
1129 p_data => x_msg_data ,
1130 p_encoded => FND_API.G_FALSE
1131 );
1132
1133 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1134 ROLLBACK TO Client_Stage_Data ;
1135 update_imp_headers
1136 (p_imp_header_id => p_imp_header_id,
1137 p_status_code => 'STAGE_FAIL');
1138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1139 cn_message_pkg.end_batch(l_process_audit_id);
1140 FND_MSG_PUB.Count_And_Get(
1141 p_count => x_msg_count ,
1142 p_data => x_msg_data ,
1143 p_encoded => FND_API.G_FALSE
1144 );
1145
1146 WHEN OTHERS THEN
1147 ROLLBACK TO Client_Stage_Data ;
1148 update_imp_headers
1149 (p_imp_header_id => p_imp_header_id,
1150 p_status_code => 'STAGE_FAIL');
1151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1152 cn_message_pkg.set_error(l_api_name,'Unexpected Error');
1153 cn_message_pkg.end_batch(l_process_audit_id);
1154 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1155 THEN
1156 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1157 END IF;
1158 FND_MSG_PUB.Count_And_Get
1159 (
1160 p_count => x_msg_count ,
1161 p_data => x_msg_data ,
1162 p_encoded => FND_API.G_FALSE
1163 );
1164
1165 END Client_Stage_Data ;
1166
1167 -- ========================================================
1168 -- Utility Modules
1169 -- ========================================================
1170 -- --------------------------------------------------------+
1171 -- update_imp_lines
1172 --
1173 -- This procedure will update cn_imp_lines status and error code
1174 -- --------------------------------------------------------+
1175 PROCEDURE update_imp_lines
1176 (p_imp_line_id IN NUMBER,
1177 p_status_code IN VARCHAR2,
1178 p_error_code IN VARCHAR2,
1179 p_error_msg IN VARCHAR2 := NULL) IS
1180 PRAGMA AUTONOMOUS_TRANSACTION;
1181 BEGIN
1182
1183 UPDATE cn_imp_lines
1184 SET status_code = p_status_code, error_code = p_error_code,
1185 error_msg = p_error_msg
1186 WHERE imp_line_id = p_imp_line_id
1187 ;
1188
1189 IF (SQL%ROWCOUNT=0) THEN
1190 RAISE NO_DATA_FOUND;
1191 END IF;
1192 COMMIT;
1193 END update_imp_lines;
1194
1195 -- --------------------------------------------------------+
1196 -- update_imp_headers
1197 --
1198 -- This procedure will update cn_imp_headers status,processed_row
1199 -- and failed_row
1200 -- --------------------------------------------------------+
1201 PROCEDURE update_imp_headers
1202 (p_imp_header_id IN NUMBER,
1203 p_status_code IN VARCHAR2,
1204 p_staged_row IN NUMBER := NULL,
1205 p_processed_row IN NUMBER := NULL,
1206 p_failed_row IN NUMBER := NULL) IS
1207 PRAGMA AUTONOMOUS_TRANSACTION;
1208 BEGIN
1209
1210 UPDATE cn_imp_headers
1211 SET status_code = Decode(p_status_code,NULL,status_code,p_status_code),
1212 staged_row =Decode(p_staged_row,NULL,staged_row,p_staged_row),
1213 processed_row =Decode(p_processed_row,NULL,processed_row,p_processed_row),
1214 failed_row = Decode(p_failed_row,NULL,failed_row,p_failed_row)
1215 WHERE imp_header_id = p_imp_header_id
1216 ;
1217
1218 IF (SQL%ROWCOUNT=0) THEN
1219 RAISE NO_DATA_FOUND;
1220 END IF;
1221 COMMIT;
1222 END update_imp_headers;
1223
1224 -- --------------------------------------------------------+
1225 -- build_error_rec
1226 --
1227 -- This procedure will generate the list of source column headers for error
1228 -- reporting. It will also generate a SQL statement which will be used to
1229 -- retrieve target column values
1230 -- --------------------------------------------------------+
1231 PROCEDURE build_error_rec
1232 (p_imp_header_id IN NUMBER,
1233 x_header_list OUT NOCOPY VARCHAR2,
1234 x_sql_stmt OUT NOCOPY VARCHAR2 )
1235 IS
1236 CURSOR c_src_col_csr IS
1237 SELECT f.source_column,f.source_user_column,
1238 f.target_column_name,f.target_table_name
1239 FROM cn_imp_headers h, cn_imp_map_fields f
1240 WHERE h.imp_header_id = p_imp_header_id
1241 AND f.imp_map_id = h.imp_map_id
1242 ORDER BY f.source_column
1243 ;
1244 l_src_col_csr c_src_col_csr%ROWTYPE;
1245 l_table_name cn_imp_map_fields.target_column_name%TYPE ;
1246 l_errbuf VARCHAR2(2000);
1247
1248 BEGIN
1249 x_header_list := NULL;
1250 x_sql_stmt := 'SELECT ';
1251
1252 OPEN c_src_col_csr;
1253 LOOP
1254 FETCH c_src_col_csr INTO l_src_col_csr;
1255 EXIT WHEN c_src_col_csr%notfound;
1256
1257 IF x_header_list IS NOT NULL THEN
1258 x_header_list := x_header_list || ' , ';
1259 END IF;
1260 x_header_list := x_header_list || l_src_col_csr.source_user_column;
1261
1262 IF x_sql_stmt <> 'SELECT ' THEN
1263 x_sql_stmt := x_sql_stmt || ' || '' , '' || ';
1264 END IF;
1265 x_sql_stmt := x_sql_stmt || l_src_col_csr.target_column_name;
1266 l_table_name := l_src_col_csr.target_table_name;
1267 END LOOP;
1268 IF c_src_col_csr%ROWCOUNT = 0 THEN
1269 x_header_list := NULL;
1270 x_sql_stmt := NULL;
1271 ELSE
1272 x_sql_stmt := x_sql_stmt || ' FROM ' || l_table_name;
1273 x_sql_stmt := x_sql_stmt || ' WHERE imp_line_id = :id' ;
1274 END IF;
1275 CLOSE c_src_col_csr;
1276
1277 -- cn_message_pkg.debug(x_header_list);
1278 -- cn_message_pkg.debug(x_sql_stmt);
1279
1280 END build_error_rec;
1281
1282 -- --------------------------------------------------------+
1283 -- write_error_rec
1284 --
1285 -- This procedure will write the list of source column headers to process log
1286 -- also retrieve the value of corresponding target columns and write to log
1287 -- --------------------------------------------------------+
1288 PROCEDURE write_error_rec
1289 (p_imp_header_id IN NUMBER,
1290 p_imp_line_id IN NUMBER,
1291 p_header_list IN VARCHAR2,
1292 p_sql_stmt IN VARCHAR2 )
1293 IS
1294 l_data_list VARCHAR2(2000);
1295 l_errbuf VARCHAR2(2000);
1296 BEGIN
1297
1298 EXECUTE IMMEDIATE p_sql_stmt INTO l_data_list USING p_imp_line_id ;
1299
1300 cn_message_pkg.write
1301 (p_message_text => '-- ' || p_header_list,
1302 p_message_type => 'ERROR');
1303
1304 cn_message_pkg.write
1305 (p_message_text => '-- ' || l_data_list,
1306 p_message_type => 'ERROR');
1307
1308 END write_error_rec;
1309
1310 END CN_IMPORT_PVT;