[Home] [Help]
PACKAGE BODY: APPS.HZ_BATCH_IMPORT_PKG
Source
1 PACKAGE BODY HZ_BATCH_IMPORT_PKG AS
2 /*$Header: ARHIBASB.pls 120.29 2011/01/31 19:56:10 awu ship $ */
3
4 /******
5 Pseudo code of hz_batch_import_pkg.import_batch
6 ===============================================
7
8 Check database version
9
10 get id of last request from batch summary table
11 get status of the request
12 if (last stage is BATCH_DEDUP or ADDR_VAL) then
13 {
14 if error then
15 { update batch summary table and exit }
16 }
17 if (last stage is DATA_LOAD) then
18 {
19 if (error) then
20 {
21 update batch summary table
22 update batch detail table
23 if(run is COMPLETE or CONTINUE) then
24 {
25 if(ran BATCH_DEDUP and REGISTRY_DEDUP) then
26 {
27 call DQM cleanup routine
28 }
29 if(ran REGISTRY_DEDUP) then
30 {
31 call DQM interface tca sanitize report
32 kick off automerge if necessary
33 }
34 call post processing
35 sleep
36 }
37 else if(run is WHAT_IF) then
38 {
39 if(ran BATCH_DEDUP and REGISTRY_DEDUP) then
40 {
41 call DQM cleanup routine
42 }
43 skip
44 set last_stage to POST_PROCESS (why? not necessary)
45 }
46 return
47 }
48 }
49
50 if(request_data is null, i.e. first stage) then
51 {
52 validate OS and OSR
53 if(run is CONTINUE) then
54 {
55 report error if pre-import has not been run
56 report error if batch is already complete
57 }
58
59 if(current run is not the first run) then
60 {
61 if(status of last run is not PENDING) then
62 {
63 create entry in batch details
64 }
65 else
66 {
67 create entry in batch details
68 }
69 }
70
71 if(run is WHAT_IF or COMPLETE) then
72 {
73 if(request_data is null, i.e. first stage) then
74 {
75 check availability of match rule id if any dedup
76 update status of batch summary table
77 if(what_if_flag='Y' in batch summary table, i.e.last run is what-if)
78 {
79 (need to check if necessary before cleanup?)
80 clean up batch-dedup info
81 clean up address validation info
82 call dqm cleanup
83 }
84 }
85
86 if(request_data is null, i.e. first stage and
87 run batch_dedup) then
88 {
89 generate work units
90 run batch dedup
91 sleep
92 }
93 else
94 {
95 if(request_data is null, i.e. first stage and
96 NOT run batch_dedup) then
97 {
98 set request_data to SKIP_BATCH_DEDUP
99 skip
100 }
101 }
102
103 if(last stage is BATCH_DEDUP or SKIP_BATCH_DEDUP)
104 {
105 if(run is COMPLETE and run batch_dedup) then
106 {
107 apply batch dedup action
108 }
109 if(run addr_val) then
110 {
111 submit address validation request
112 sleep
113 }
114 else
115 {
116 set request_data to SKIP_ADDR_DEDUP
117 skip
118 }
119 }
120
121 if(last stage is ADDR_VAL or SKIP_ADDR_VAL) then
122 {
123 call DQM cleanup for staging reuse if ran registry dedup
124 submit concurrent request for dataload
125 sleep
126 }
127
128 if(last stage is DATALOAD) then
129 {
130 if(run is COMPLETE) then
131 {
132 call the DQM cleanup routine if ran registry dedup or batch dedup
133 if(ran registry dedup) then
134 {
135 call the report dupsets API
136 update batch details
137 submit automerge request
138 call dataload postprocessing request
139 sleep
140 }
141 }
142 elseif(run is WHAT_IF) then
143 { (why is it possible to have this stage for WHAT-IF??)
144 call the DQM cleanup routine if ran batch or registry dedup
145 skip
146 }
147 }
148 }
149
150 if(run is CONTINUE) then
151 {
152 if(request_data is null, i.e. first stage) then
153 {
154 update batch summmary table
155 if(run batch dedup and
156 import_status was ACTION_REQUIRED) then
157 {
158 apply batch dedup action
159 }
160
161 if(run registry dedup) then
162 {
163 apply registry dedup action
164 }
165 submit dataload request
166 sleep
167 }
168
169 if(last_stage is DATALOAD) then
170 {
171 call DQM post import cleanup if ran batch or registry dedup
172 call the report dupsets API if ran registry dedup
173 submit automerge request
174 submit postprocessing request
175 sleep
176 }
177
178 if(last_stage is POST_PROCESS) then
179 {
180 update batch summary table
181 }
182 }
183 *******/
184
185
186 ---------------------
187 -- private procedures
188 ---------------------
189
190 PROCEDURE final_steps_whatif(
191 p_batch_id IN NUMBER,
192 x_return_status OUT NOCOPY VARCHAR2,
193 x_msg_count OUT NOCOPY NUMBER,
194 x_msg_data OUT NOCOPY VARCHAR2
195 )
196 IS
197
198 l_reg_dedup VARCHAR2(1);
199 l_batch_dedup VARCHAR2(1);
200 BEGIN
201
202 SELECT registry_dedup_flag, batch_dedup_flag
203 INTO l_reg_dedup, l_batch_dedup
204 FROM hz_imp_batch_summary
205 WHERE batch_id = p_batch_id;
206
207 -- call the DQM cleanup routine
208 IF l_reg_dedup = 'Y' OR l_batch_dedup = 'Y' THEN
209 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** POST - Calling dqm_post_imp_cleanup');
210 HZ_IMP_DQM_STAGE.dqm_post_imp_cleanup
211 (p_batch_id => p_batch_id,
212 x_return_status => x_return_status,
213 x_msg_count => x_msg_count,
214 x_msg_data => x_msg_data);
215 END IF;
216
217 END;
218
219
220 PROCEDURE get_current_run(
221 p_batch_id IN NUMBER,
222 x_run_number OUT NOCOPY NUMBER)
223 IS
224
225 CURSOR c1 IS
226 SELECT max(run_number)
227 FROM hz_imp_batch_details
228 WHERE batch_id = p_batch_id;
229
230 l_run_number NUMBER;
231
232 BEGIN
233
234 OPEN c1;
235 FETCH c1 INTO l_run_number;
236
237 IF c1%NOTFOUND OR l_run_number IS NULL THEN
238 l_run_number := 1;
239 ELSE
240 l_run_number := l_run_number+1;
241 END IF;
242
243 CLOSE c1;
244
245 x_run_number := l_run_number;
246
247 END;
248
249 FUNCTION STAGING_DATA_EXISTS(
250 P_BATCH_ID IN NUMBER,
251 P_BATCH_MODE_FLAG IN VARCHAR2
252 ) RETURN VARCHAR2 IS
253
254 CURSOR c_what_if_sg_data(p_batch_id number, p_batch_mode_flag varchar2) IS
255 SELECT 'Y'
256 FROM dual
257 WHERE EXISTS (
258 SELECT 'Y'
259 FROM HZ_IMP_PARTIES_SG
260 WHERE batch_id = p_batch_id
261 AND batch_mode_flag = p_batch_mode_flag
262 AND rownum = 1)
263 OR EXISTS (
264 SELECT 'Y'
265 FROM HZ_IMP_ADDRESSES_SG
266 WHERE batch_id = p_batch_id
267 AND batch_mode_flag = p_batch_mode_flag
268 AND rownum = 1)
269 OR EXISTS (
270 SELECT 'Y'
271 FROM HZ_IMP_CONTACTPTS_SG
272 WHERE batch_id = p_batch_id
273 AND batch_mode_flag = p_batch_mode_flag
274 AND rownum = 1)
275 OR EXISTS (
276 SELECT 'Y'
277 FROM HZ_IMP_CREDITRTNGS_SG
278 WHERE batch_id = p_batch_id
279 AND batch_mode_flag = p_batch_mode_flag
280 AND rownum = 1)
281 OR EXISTS (
282 SELECT 'Y'
283 FROM HZ_IMP_FINREPORTS_SG
284 WHERE batch_id = p_batch_id
285 AND batch_mode_flag = p_batch_mode_flag
286 AND rownum = 1)
287 OR EXISTS (
288 SELECT 'Y'
289 FROM HZ_IMP_FINNUMBERS_SG
290 WHERE batch_id = p_batch_id
291 AND batch_mode_flag = p_batch_mode_flag
292 AND rownum = 1)
293 OR EXISTS (
294 SELECT 'Y'
295 FROM HZ_IMP_CLASSIFICS_SG
296 WHERE batch_id = p_batch_id
297 AND batch_mode_flag = p_batch_mode_flag
298 AND rownum = 1)
299 OR EXISTS (
300 SELECT 'Y'
301 FROM HZ_IMP_RELSHIPS_SG
302 WHERE batch_id = p_batch_id
303 AND batch_mode_flag = p_batch_mode_flag
304 AND rownum = 1)
305 OR EXISTS (
306 SELECT 'Y'
307 FROM HZ_IMP_CONTACTROLES_SG
308 WHERE batch_id = p_batch_id
309 AND batch_mode_flag = p_batch_mode_flag
310 AND rownum = 1)
311 OR EXISTS (
312 SELECT 'Y'
313 FROM HZ_IMP_CONTACTS_SG
314 WHERE batch_id = p_batch_id
315 AND batch_mode_flag = p_batch_mode_flag
316 AND rownum = 1)
317 OR EXISTS (
318 SELECT 'Y'
319 FROM HZ_IMP_ADDRESSUSES_SG
320 WHERE batch_id = p_batch_id
321 AND batch_mode_flag = p_batch_mode_flag
322 AND rownum = 1);
323
324 l_what_if_sg_data_exists VARCHAR2(1);
325
326 BEGIN
327
328 OPEN c_what_if_sg_data(P_BATCH_ID, P_BATCH_MODE_FLAG);
329 FETCH c_what_if_sg_data INTO l_what_if_sg_data_exists;
330 CLOSE c_what_if_sg_data;
331
332 fnd_file.put_line(FND_FILE.LOG, 'l_what_if_sg_data_exists = ' || l_what_if_sg_data_exists);
333 RETURN NVL(l_what_if_sg_data_exists, 'N');
334 END STAGING_DATA_EXISTS;
335
336 /* Clean up staging. Delete for online, truncate for batch */
337 /* Also chean up the following tables: */
338 /* hz_imp_osr_change */
339 /* HZ_IMP_INT_DEDUP_RESULTS */
340 /* HZ_IMP_TMP_REL_END_DATE */
341 PROCEDURE CLEANUP_STAGING(
342 P_BATCH_ID IN NUMBER,
343 P_BATCH_MODE_FLAG IN VARCHAR2
344 ) IS
345 l_bool BOOLEAN;
346 l_status VARCHAR2(255);
347 l_schema VARCHAR2(255);
348 l_tmp VARCHAR2(2000);
349 --l_debug_prefix VARCHAR2(30) := '';
350 BEGIN
351
352 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
353 hz_utility_v2pub.debug(p_message=>'WRP:CLEANUP_STAGING()+',
354 p_prefix=>'',
355 p_msg_level=>fnd_log.level_procedure);
356 END IF;
357
358 l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_tmp,l_schema);
359
360 IF P_BATCH_MODE_FLAG = 'Y' THEN
361
362 fnd_file.put_line(FND_FILE.LOG, ' l_schema = ' || l_schema);
363
364 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_PARTIES_SG TRUNCATE PARTITION batchpar DROP STORAGE';
365 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_ADDRESSES_SG TRUNCATE PARTITION batchpar DROP STORAGE';
366 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_CONTACTPTS_SG TRUNCATE PARTITION batchpar DROP STORAGE';
367 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_CREDITRTNGS_SG TRUNCATE PARTITION batchpar DROP STORAGE';
368 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_CLASSIFICS_SG TRUNCATE PARTITION batchpar DROP STORAGE';
369 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_FINREPORTS_SG TRUNCATE PARTITION batchpar DROP STORAGE';
370 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_FINNUMBERS_SG TRUNCATE PARTITION batchpar DROP STORAGE';
371 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_RELSHIPS_SG TRUNCATE PARTITION batchpar DROP STORAGE';
372 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_CONTACTS_SG TRUNCATE PARTITION batchpar DROP STORAGE';
373 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_CONTACTROLES_SG TRUNCATE PARTITION batchpar DROP STORAGE';
374 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_ADDRESSUSES_SG TRUNCATE PARTITION batchpar DROP STORAGE';
375
376 ELSE
377 DELETE HZ_IMP_PARTIES_SG
378 WHERE batch_id = P_BATCH_ID
379 AND batch_mode_flag = P_BATCH_MODE_FLAG;
380 DELETE HZ_IMP_ADDRESSES_SG
381 WHERE batch_id = P_BATCH_ID
382 AND batch_mode_flag = P_BATCH_MODE_FLAG;
383 DELETE HZ_IMP_CONTACTPTS_SG
384 WHERE batch_id = P_BATCH_ID
385 AND batch_mode_flag = P_BATCH_MODE_FLAG;
386 DELETE HZ_IMP_CREDITRTNGS_SG
387 WHERE batch_id = P_BATCH_ID
388 AND batch_mode_flag = P_BATCH_MODE_FLAG;
389 DELETE HZ_IMP_CLASSIFICS_SG
390 WHERE batch_id = P_BATCH_ID
391 AND batch_mode_flag = P_BATCH_MODE_FLAG;
392 DELETE HZ_IMP_FINREPORTS_SG
393 WHERE batch_id = P_BATCH_ID
394 AND batch_mode_flag = P_BATCH_MODE_FLAG;
395 DELETE HZ_IMP_FINNUMBERS_SG
396 WHERE batch_id = P_BATCH_ID
397 AND batch_mode_flag = P_BATCH_MODE_FLAG;
398 DELETE HZ_IMP_RELSHIPS_SG
399 WHERE batch_id = P_BATCH_ID
400 AND batch_mode_flag = P_BATCH_MODE_FLAG;
401 DELETE HZ_IMP_CONTACTS_SG
402 WHERE batch_id = P_BATCH_ID
403 AND batch_mode_flag = P_BATCH_MODE_FLAG;
404 DELETE HZ_IMP_CONTACTROLES_SG
405 WHERE batch_id = P_BATCH_ID
406 AND batch_mode_flag = P_BATCH_MODE_FLAG;
407 DELETE HZ_IMP_ADDRESSUSES_SG
408 WHERE batch_id = P_BATCH_ID
409 AND batch_mode_flag = P_BATCH_MODE_FLAG;
410
411 END IF;
412
413 DELETE hz_imp_osr_change WHERE batch_id = P_BATCH_ID;
414 --DELETE HZ_IMP_INT_DEDUP_RESULTS WHERE batch_id = P_BATCH_ID;
415 DELETE HZ_IMP_TMP_REL_END_DATE WHERE batch_id = P_BATCH_ID;
416
417 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
418 hz_utility_v2pub.debug(p_message=>'WRP:CLEANUP_STAGING()-',
419 p_prefix=>'',
420 p_msg_level=>fnd_log.level_procedure);
421 END IF;
422 COMMIT;
423 END CLEANUP_STAGING;
424
425 --------------------
426 -- public procedures
427 --------------------
428
429 /**
430 * PROCEDURE import_batch
431 *
432 * DESCRIPTION
433 * Concurrent program for importing batch
434 *
435 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
436 *
437 * ARGUMENTS
438 * IN:
439 * IN/OUT:
440 * OUT:
441 *
442 * NOTES
443 *
444 * MODIFICATION HISTORY
445 *
446 * 09-JUL-2003 Indrajit Sen o Created.
447 *
448 */
449
450 PROCEDURE import_batch (
451 errbuf OUT NOCOPY VARCHAR2,
452 retcode OUT NOCOPY VARCHAR2,
453 p_batch_id IN NUMBER,
454 p_import_run_option IN VARCHAR2,
455 p_run_batch_dedup IN VARCHAR2,
456 p_batch_dedup_rule_id IN NUMBER,
457 p_batch_dedup_action IN VARCHAR2,
458 p_run_addr_val IN VARCHAR2,
459 p_run_registry_dedup IN VARCHAR2,
460 p_registry_dedup_rule_id IN NUMBER,
461 p_run_automerge IN VARCHAR2 := 'N',
462 p_generate_fuzzy_key IN VARCHAR2 := 'Y'
463 /*,
464 /*
465 p_bd_action_on_parties IN VARCHAR2 DEFAULT NULL,
466 p_bd_action_on_addresses IN VARCHAR2 DEFAULT NULL,
467 p_bd_action_on_contacts IN VARCHAR2 DEFAULT NULL,
468 p_bd_action_on_contact_points IN VARCHAR2 DEFAULT NULL
469 */
470 )
471 IS
472
473 -- cursor to get the batch information
474 cursor c_batch_info
475 is
476 select * from hz_imp_batch_summary
477 where batch_id = p_batch_id;
478
479 r_batch_info c_batch_info%ROWTYPE;
480
481 l_return_status VARCHAR2(1);
482 l_msg_count NUMBER;
483 l_msg_data VARCHAR2(255);
484 /*
485 l_bd_action_on_parties VARCHAR2(30) := p_bd_action_on_parties;
486 l_bd_action_on_addresses VARCHAR2(30) := p_bd_action_on_addresses;
487 l_bd_action_on_contacts VARCHAR2(30) := p_bd_action_on_contacts;
488 l_bd_action_on_contact_points VARCHAR2(30) := p_bd_action_on_contact_points;
489 */
490 l_dup_batch_id NUMBER;
491 l_bd_sub_request NUMBER;
492 l_av_sub_request NUMBER;
493 l_dl_sub_request NUMBER;
494 l_num_of_workers NUMBER;
495 l_req_data VARCHAR2(30);
496 l_dataload_rerun VARCHAR2(30);
497 l_what_if VARCHAR2(1);
498 l_current_run NUMBER;
499 l_last_req NUMBER;
500 l_temp_rphase VARCHAR2(80);
501 l_temp_rstatus VARCHAR2(80);
502 l_temp_dphase VARCHAR2(30);
503 l_temp_dstatus VARCHAR2(30);
504 l_temp_message VARCHAR2(240);
505 l_call_status BOOLEAN;
506 l_ver NUMBER;
507 l_str VARCHAR2(2000);
508 l_last_run_imp_status VARCHAR2(30);
509 l_work_unit VARCHAR2(1);
510 --l_reg_dedup VARCHAR2(1);
511 l_pp_sub_request NUMBER;
512 l_am_sub_request NUMBER;
513 l_automerge_flag VARCHAR2(30);
514 l_rule_id_missing BOOLEAN;
515
516 l_index_conc_program_req_id NUMBER;
517
518 os_exists_flag VARCHAR2(1) :='N'; /* Bug 4079902 */
519 l_batch_mode_flag VARCHAR2(1);
520 l_post_process_flag VARCHAR2(1) := 'N';
521 l_wng_msg VARCHAR2(1000) := 'WARNING****';
522
523 l_pp_error VARCHAR2(1) := 'N';
524
525 CURSOR c_batch_status(p_batch_id number) IS
526 select bs.import_status
527 from hz_imp_batch_details bs
528 where bs.batch_id = p_batch_id
529 and run_number = (select max(run_number)
530 from hz_imp_batch_details
531 where batch_id = p_batch_id);
532
533 CURSOR c_pp_error(p_batch_id number) IS
534 select 'Y'
535 from hz_imp_work_units
536 where batch_id=p_batch_id
537 and (postprocess_status is null
538 OR postprocess_status='U')
539 and rownum=1;
540 BEGIN
541
542 -- check the database version and exit if not 9i and higher
543 SELECT REPLACE(substr(version, 1, instr(version, '.', 1, 3)),'.')
544 INTO l_ver
545 FROM v$instance;
546
547 IF l_ver < 920 THEN
548 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** FATAL ERROR: The feature is only available for Oracle 9iR2 or higher');
549 RETURN;
550 END IF;
551
552 l_req_data := fnd_conc_global.request_data;
553 l_work_unit := 'N';
554
555 -----------------------------------------------------
556 -- get the batch information from batch summary table
557 -----------------------------------------------------
558 open c_batch_info;
559 fetch c_batch_info into r_batch_info;
560 close c_batch_info;
561
562 BEGIN
563 -- This check shall indicate that atleast some workers have finished stage 2
564 -- and hence post processing needs to be done for them.
565 SELECT 'Y' INTO l_post_process_flag
566 FROM HZ_IMP_WORK_UNITS
567 WHERE batch_id=p_batch_id
568 AND (
569 (stage>=2
570 AND status='C')
571 -- to take care of the case when unexpected error in stage 3 and just 1 work unit
572 OR
573 (stage=3
574 AND status='P')
575 )
576 AND rownum=1;
577 EXCEPTION
578 WHEN OTHERS THEN NULL;
579 END;
580
581 IF r_batch_info.load_type = 'CSV' THEN
582 l_batch_mode_flag := 'N';
583 ELSE
584 l_batch_mode_flag := 'Y';
585 END IF;
586
587 --------------------------------------------------------
588 -- Check what the last stage is, get the last request id
589 --------------------------------------------------------
590 -- check for any error that has happened and discontinue if error
591 IF l_req_data = 'BATCH_DEDUP' THEN
592 l_last_req := r_batch_info.batch_dedup_req_id;
593 ELSIF l_req_data = 'ADDR_VAL' THEN
594 l_last_req := r_batch_info.addr_val_req_id;
595 ELSIF l_req_data = 'DATA_LOAD' THEN
596 l_last_req := r_batch_info.import_req_id;
597 END IF;
598
599 l_call_status := fnd_concurrent.get_request_status(
600 l_last_req,
601 null,
602 null,
603 l_temp_rphase,
604 l_temp_rstatus,
605 l_temp_dphase,
606 l_temp_dstatus,
607 l_temp_message);
608
609 -----------------------------------------
610 -- Report error if problem im batch dedup
611 -----------------------------------------
612 IF l_req_data = 'BATCH_DEDUP'
613 AND
614 (l_temp_dstatus <> 'NORMAL' OR r_batch_info.batch_dedup_status = 'ERROR')
615 THEN
616 UPDATE hz_imp_batch_summary
617 SET batch_dedup_status = 'ERROR',
618 batch_status = 'ACTION_REQUIRED',
619 main_conc_status = 'COMPLETED'
620 WHERE batch_id = p_batch_id;
621
622 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Batch de-duplication program errored...exiting');
623 retcode := 1;
624 errbuf := 'WARNING**** Unexpected error occured Batch de-duplication program.';
625 RETURN;
626 END IF;
627
628 ---------------------------------------------
629 -- Report error if problem in addr validation
630 ---------------------------------------------
631 IF l_req_data = 'ADDR_VAL'
632 AND
633 (l_temp_dstatus <> 'NORMAL' OR r_batch_info.addr_val_status = 'ERROR')
634 THEN
635 UPDATE hz_imp_batch_summary
636 SET addr_val_status = 'ERROR',
637 batch_status = 'ACTION_REQUIRED',
638 main_conc_status = 'COMPLETED'
639 WHERE batch_id = p_batch_id;
640
641 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Address validation program errored...exiting');
642 retcode := 1;
643 errbuf := 'WARNING****** Unexpected error occured Address validation program.';
644 RETURN;
645 END IF;
646
647 ---------------------------------------------
648 -- Report error if problem in data load
649 ---------------------------------------------
650 IF l_req_data = 'DATA_LOAD'
651 AND
652 (l_temp_dstatus <> 'NORMAL' OR r_batch_info.import_status = 'ERROR')
653 THEN
654 UPDATE hz_imp_batch_summary
655 SET import_status = 'ERROR',
656 batch_status = 'ACTION_REQUIRED',
657 main_conc_status = 'COMPLETED'
658 WHERE batch_id = p_batch_id;
659
660
661 --------------------------------------------------
662 -- get last entry in batch detail table and update
663 --------------------------------------------------
664 get_current_run(
665 p_batch_id => p_batch_id,
666 x_run_number => l_current_run);
667 UPDATE hz_imp_batch_details
668 SET import_status = 'ERROR'
669 WHERE batch_id = p_batch_id
670 AND run_number = l_current_run;
671
672 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Data load program errored...exiting');
673 -- since the data load might have run partially, there is a need to perform the
674 -- post-processing here
675 -- final steps on import
676 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Performing the post processes');
677
678 IF p_import_run_option = 'COMPLETE' OR p_import_run_option = 'CONTINUE' THEN
679
680 -- retrieve number of workers
681 IF r_batch_info.load_type = 'CSV' THEN
682 l_num_of_workers := 1;
683 ELSE
684 l_num_of_workers := fnd_profile.value('HZ_IMP_NUM_OF_WORKERS');
685 IF l_num_of_workers IS NULL THEN
686 l_num_of_workers := 1;
687 END IF;
688 END IF;
689
690 -- call the DQM cleanup routine
691 IF r_batch_info.registry_dedup_flag = 'Y' OR r_batch_info.batch_dedup_flag = 'Y' THEN
692 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Calling DQM post import cleanup');
693 HZ_IMP_DQM_STAGE.dqm_post_imp_cleanup
694 (p_batch_id => p_batch_id,
695 x_return_status => l_return_status,
696 x_msg_count => l_msg_count,
697 x_msg_data => l_msg_data);
698 END IF;
699
700 IF l_post_process_flag='Y'
701 THEN
702 IF NVL(r_batch_info.registry_dedup_flag,'N') = 'Y' THEN
703
704 -- call the report dupsets API
705 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Calling DQM interface tca sanitize report');
706 HZ_DQM_DUP_ID_PKG.interface_tca_sanitize_report
707 (p_batch_id => p_batch_id,
708 p_match_rule_id => p_registry_dedup_rule_id,
709 p_request_id => fnd_global.conc_request_id,
710 x_dup_batch_id => l_dup_batch_id,
711 x_return_status => l_return_status,
712 x_msg_count => l_msg_count,
713 x_msg_data => l_msg_data);
714
715 -- l_current_run already set, can comment this out
716 /*
717 get_current_run( --
718 p_batch_id => p_batch_id, --
719 x_run_number => l_current_run); --
720 */
721 UPDATE hz_imp_batch_details
722 SET dup_batch_id = l_dup_batch_id
723 WHERE batch_id = p_batch_id
724 AND run_number = l_current_run;
725
726 ------------------------------------------
727 -- if automerge flag, kick off the process
728 ------------------------------------------
729 IF p_run_automerge = 'Y' THEN
730 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Invoking auto merge post process');
731 l_am_sub_request := FND_REQUEST.SUBMIT_REQUEST(
732 'AR',
733 'ARHAMRGP',
734 '',
735 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
736 true,
737 to_char(l_dup_batch_id),
738 to_char(l_num_of_workers)
739 );
740
741 IF l_am_sub_request = 0 THEN
742 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting auto merge post process');
743 ELSE
744 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for auto merge post process');
745 END IF;
746
747 END IF; --nvl(l_automerge_flag,'N') = 'Y'
748
749 END IF; --NVL(l_reg_dedup,'N') = 'Y'
750
751 IF (Nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'),'Y') <> 'DISABLE') THEN --Bug 10435345
752 -- Bug 4594407 : Call DQM Sync Index
753 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Calling Parallel Sync Index concurrent program');
754 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Request Id of the program to be waited on, that is being passed to this : ' || fnd_global.conc_request_id );
755 l_index_conc_program_req_id := FND_REQUEST.SUBMIT_REQUEST('AR',
756 'ARHDQMPP',
757 'DQM Parallel Sync Index Parent Program',
758 NULL,
759 FALSE,
760 fnd_global.conc_request_id
761 );
762 IF l_index_conc_program_req_id = 0 THEN
763 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error submitting DQM Sync Index Program.');
764 ELSE
765 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Request Id of Parallel Sync concurrent Program is : ' || l_index_conc_program_req_id );
766 END IF;
767 END IF;
768
769 -- call dataload post processing
770 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Invoking data load post process');
771 FOR i IN 1..l_num_of_workers LOOP
772 l_pp_sub_request := FND_REQUEST.SUBMIT_REQUEST(
773 'AR',
774 'ARHLPPLB',
775 '',
776 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
777 true,
778 p_batch_id,
779 r_batch_info.original_system,
780 l_batch_mode_flag,
781 to_char(fnd_global.conc_request_id),
782 p_generate_fuzzy_key
783 );
784
785 IF l_pp_sub_request = 0 THEN
786 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting data load post processing ' || l_num_of_workers);
787 ELSE
788 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for data load post processing ' || l_num_of_workers);
789 END IF;
790 END LOOP;
791
792 -- wait for the conc program to finish
793 fnd_conc_global.set_req_globals(
794 conc_status => 'PAUSED',
795 request_data => 'POST_PROCESS');
796 ELSE
797 fnd_conc_global.set_req_globals(
798 conc_status => 'NORMAL',
799 request_data => 'POST_PROCESS');
800 l_req_data := 'POST_PROCESS'; -- not necessary
801 retcode := 1;
802 errbuf := 'WARNING**** Unexpected error occured in the Data Load program';
803 END IF;
804
805 ELSIF p_import_run_option = 'WHAT_IF' THEN
806
807 final_steps_whatif(
808 p_batch_id => p_batch_id,
809 x_return_status => l_return_status,
810 x_msg_count => l_msg_count,
811 x_msg_data => l_msg_data
812 );
813
814 fnd_conc_global.set_req_globals(
815 conc_status => 'NORMAL',
816 request_data => 'POST_PROCESS');
817 l_req_data := 'POST_PROCESS'; -- not necessary
818 retcode := 1;
819 errbuf := 'WARNING**** Unexpected error occured in the Data Load program';
820
821 END IF; -- p_import_run_option = 'COMPLETE'/'WHAT_IF'
822
823 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
824 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** '||FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE));
825 RETURN;
826 END IF;
827
828 RETURN;
829 END IF;
830
831 -----------------------------
832 -- do the one time processing
833 -----------------------------
834
835 IF l_req_data IS NULL THEN
836 ------------------------------
837 -- print the parameters passed
838 ------------------------------
839 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Parameters passed');
840 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** -----------------');
841 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Batch ID: '||p_batch_id);
842 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Import Run Option: '||p_import_run_option);
843 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Run Batch Dedup?: '||p_run_batch_dedup);
844 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Batch dedup rule id: '||p_batch_dedup_rule_id);
845 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Batch dedup action: '||p_batch_dedup_action);
846 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Run addr val?: '||p_run_addr_val);
847 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Run registry dedup?: '||p_run_registry_dedup);
848 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Registry dedup rule id?: '||p_registry_dedup_rule_id);
849 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Run Automerge?: '||p_run_automerge);
850 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** -----------------');
851 FND_FILE.PUT_LINE (FND_FILE.LOG, '');
852
853 ---------------------
854 -- validate the batch
855 ---------------------
856
857 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Validating the batch');
858
859 -- Bug 4079902. Validated orig_system against HZ_ORIG_SYSTEMS_B instead of
860 -- the lookup.
861
862 -- validate original_system against lookup ORIG_SYSTEM
863 /*
864 hz_utility_v2pub.validate_lookup (
865 p_column => 'original_system',
866 p_lookup_type => 'ORIG_SYSTEM',
867 p_column_value => r_batch_info.original_system,
868 x_return_status => l_return_status);
869 */
870 BEGIN
871 SELECT 'Y' INTO os_exists_flag
872 FROM hz_orig_systems_b
873 WHERE
874 orig_system= r_batch_info.original_system
875 AND orig_system<>'SST'
876 AND status='A';
877 EXCEPTION
878 WHEN NO_DATA_FOUND THEN
879 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_FK');
880 FND_MESSAGE.SET_TOKEN('FK','orig_system');
881 FND_MESSAGE.SET_TOKEN('COLUMN','orig_system');
882 FND_MESSAGE.SET_TOKEN('TABLE','HZ_ORIG_SYSTEMS_B');
883
884 -- Bug 4530477
885 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error***** '||FND_MESSAGE.GET);
886
887 UPDATE hz_imp_batch_summary
888 SET main_conc_status = 'ERROR',
889 batch_status = 'ACTION_REQUIRED'
890 WHERE batch_id = p_batch_id;
891
892 Errbuf := fnd_message.get;
893 Retcode := 2;
894
895 RETURN;
896 END;
897
898 BEGIN
899 IF p_run_registry_dedup = 'Y'
900 and p_registry_dedup_rule_id IS NOT NULL
901 THEN
902 IF p_run_automerge='Y' THEN
903 SELECT automerge_flag
904 INTO l_automerge_flag
905 FROM hz_match_rules_b
906 WHERE match_rule_id = p_registry_dedup_rule_id;
907
908 IF l_automerge_flag is NULL
909 or l_automerge_flag='N'
910 THEN
911
912 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error***** '||'The Match Rule selected for Registry De-duplication does not allow Automerge. Please resubmit the batch for import and select No for request parameter Run Automerge.');
913
914 UPDATE hz_imp_batch_summary
915 SET main_conc_status = 'ERROR',
916 batch_status = 'ACTION_REQUIRED'
917 WHERE batch_id = p_batch_id;
918
919 Errbuf := 'The Match Rule selected for Registry De-duplication does not allow Automerge. Please resubmit the batch for import and select No for request parameter Run Automerge.';
920 Retcode := 1;
921
922 RETURN;
923 END IF;
924 END IF;
925 UPDATE HZ_IMP_BATCH_SUMMARY
926 SET AUTOMERGE_FLAG=p_run_automerge
927 WHERE batch_id=p_batch_id;
928 END IF;
929 END;
930
931 -- validate that if it request for CONTINUE, that the what-if has
932 -- already been performed
933 IF p_import_run_option = 'CONTINUE' THEN
934
935 IF not ( NVL(r_batch_info.import_status,'X') in
936 ( 'ACTION_REQUIRED', 'COMPL_ERROR_LIMIT', 'COMPL_ERRORS' )) THEN
937 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Pre-import has not been performed for this batch.');
938 UPDATE hz_imp_batch_summary
939 SET batch_status = 'ACTION_REQUIRED'
940 WHERE batch_id = p_batch_id;
941 RETURN;
942 END IF;
943 END IF;
944
945 -- validate that the batch is not already completed one
946 IF NVL(r_batch_info.import_status,'X') = 'COMPLETED' THEN
947 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** The batch has already been completed.');
948 RETURN;
949 END IF;
950
951 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Completed validation');
952
953 -- update batch summary table and create row in batch detail table
954 get_current_run(
955 p_batch_id => p_batch_id,
956 x_run_number => l_current_run);
957
958 IF l_current_run > 1 THEN
959
960 -- get import_status of last run
961 select import_status
962 into l_last_run_imp_status
963 from hz_imp_batch_details
964 where batch_id = p_batch_id
965 and run_number = l_current_run - 1;
966
967 IF l_last_run_imp_status <> 'PENDING' THEN
968 -- create an entry in the batch details table
969 INSERT INTO hz_imp_batch_details
970 (batch_id,
971 run_number,
972 import_status,
973 import_req_id,
974 created_by,
975 creation_date,
976 last_updated_by,
977 last_update_date,
978 last_update_login,
979 main_conc_req_id)
980 values
981 (p_batch_id,
982 l_current_run,
983 'PENDING',
984 null,
985 HZ_UTILITY_V2PUB.created_by,
986 HZ_UTILITY_V2PUB.creation_date,
987 HZ_UTILITY_V2PUB.last_updated_by,
988 HZ_UTILITY_V2PUB.last_update_date,
989 HZ_UTILITY_V2PUB.last_update_login,
990 fnd_global.conc_request_id);
991
992 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Created entry in batch details table');
993
994 END IF; -- l_last_run_imp_status <> 'PENDING'
995
996 ELSE
997
998 -- create an entry in the batch details table
999 INSERT INTO hz_imp_batch_details
1000 (batch_id,
1001 run_number,
1002 import_status,
1003 import_req_id,
1004 created_by,
1005 creation_date,
1006 last_updated_by,
1007 last_update_date,
1008 last_update_login,
1009 main_conc_req_id)
1010 values
1011 (p_batch_id,
1012 l_current_run,
1013 'PENDING',
1014 null,
1015 HZ_UTILITY_V2PUB.created_by,
1016 HZ_UTILITY_V2PUB.creation_date,
1017 HZ_UTILITY_V2PUB.last_updated_by,
1018 HZ_UTILITY_V2PUB.last_update_date,
1019 HZ_UTILITY_V2PUB.last_update_login,
1020 fnd_global.conc_request_id);
1021
1022 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Created entry in batch details table');
1023
1024 END IF;
1025
1026 END IF; -- l_req_data IS NULL
1027
1028 /* Clean up staging tables if previous run did not complete successfully */
1029 /* Get latest batch details status */
1030 IF l_req_data is null THEN
1031 IF ((l_last_run_imp_status <> 'ACTION_REQUIRED' OR p_import_run_option <> 'CONTINUE') OR
1032 (l_last_run_imp_status = 'ACTION_REQUIRED' AND p_import_run_option = 'CONTINUE'
1033 AND l_batch_mode_flag = 'Y' AND
1034 STAGING_DATA_EXISTS(p_batch_id, l_batch_mode_flag) <> 'Y')) THEN
1035 CLEANUP_STAGING(p_batch_id, l_batch_mode_flag);
1036 END IF;
1037 END IF;
1038
1039 -----------------------------------
1040 -- count total records in the batch
1041 -----------------------------------
1042
1043
1044 -----------------------
1045 -- start business logic
1046 -----------------------
1047
1048 -- //////////////////////////
1049 -- what if or complete import
1050 -- //////////////////////////
1051
1052 -- if it is what-if or complete import, then do the following.
1053 -- the basic flow is same, except for a few things.
1054 -- the different things are done using the actual option.
1055 IF (p_import_run_option = 'WHAT_IF'
1056 OR
1057 p_import_run_option = 'COMPLETE') THEN
1058
1059 IF l_req_data IS NULL THEN
1060 -- check if the right parameters have been passed,
1061 -- otherwise error out
1062 -- if no match rule has been provided, error out
1063 IF (p_run_batch_dedup = 'Y' and p_batch_dedup_rule_id IS NULL) THEN
1064 l_rule_id_missing := true;
1065 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** FATAL ERROR: No match rule provided for batch dedup.');
1066 END IF;
1067 IF (p_run_registry_dedup = 'Y' and p_registry_dedup_rule_id IS NULL) THEN
1068 l_rule_id_missing := true;
1069 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** FATAL ERROR: No match rule provided for registry dedup.');
1070 END IF;
1071
1072 IF (l_rule_id_missing) then
1073 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Data Import cannot proceed, exit with error');
1074 UPDATE hz_imp_batch_summary
1075 SET main_conc_status = 'ERROR'
1076 WHERE batch_id = p_batch_id;
1077 retcode := 2;
1078 RETURN;
1079 END IF;
1080
1081 -- set the processing status appropriately
1082 UPDATE hz_imp_batch_summary
1083 SET batch_dedup_flag = decode(p_run_batch_dedup, 'Y', 'Y', 'N'),
1084 batch_dedup_status = decode(p_run_batch_dedup, 'Y', 'PENDING', 'DECLINED'),
1085 batch_dedup_match_rule_id = decode(p_run_batch_dedup, 'Y', p_batch_dedup_rule_id, null),
1086 addr_val_flag = decode(p_run_addr_val, 'Y', 'Y', 'N'),
1087 addr_val_status = decode(p_run_addr_val, 'Y', 'PENDING', 'DECLINED'),
1088 registry_dedup_flag = decode(p_run_registry_dedup, 'Y', 'Y', 'N'),
1089 registry_dedup_match_rule_id = decode(p_run_registry_dedup, 'Y', p_registry_dedup_rule_id, null),
1090 import_status = 'PENDING',
1091 what_if_flag = decode(p_import_run_option, 'WHAT_IF', 'Y', 'N'),
1092 main_conc_status = 'PROCESSING',
1093 batch_status = 'PROCESSING',
1094 main_conc_req_id = fnd_global.conc_request_id,
1095 bd_action_on_parties = NVL(p_batch_dedup_action,bd_action_on_parties),
1096 bd_action_on_addresses = NVL(p_batch_dedup_action,bd_action_on_addresses),
1097 bd_action_on_contacts = NVL(p_batch_dedup_action,bd_action_on_contacts),
1098 bd_action_on_contact_points = NVL(p_batch_dedup_action,bd_action_on_contact_points)
1099 WHERE batch_id = p_batch_id;
1100
1101 -- do some cleanup if it is a rerun after what-if
1102 IF r_batch_info.what_if_flag = 'Y' THEN
1103 -- cleanup the previous what-if results
1104 -- 1. call batch deduplication cleanup routine
1105 -- 2. call registry deduplication cleanup routine
1106 -- it is the same cleanup routine that does both the above.
1107 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Cleaning up batch de-duplication actions');
1108 HZ_BATCH_ACTION_PUB.clear_status(
1109 p_batch_id => p_batch_id,
1110 x_return_status => l_return_status);
1111
1112 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1113 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** '||FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE));
1114 RETURN;
1115 END IF;
1116
1117 -- 3. cleanup address validation information
1118 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Cleaning up address validation actions');
1119 UPDATE hz_imp_addresses_int
1120 SET VALIDATION_SUBSET_ID = null,
1121 ACCEPT_STANDARDIZED_FLAG = null,
1122 ADAPTER_CONTENT_SOURCE = null,
1123 ADDR_VALID_STATUS_CODE = null,
1124 DATE_VALIDATED = null,
1125 ADDRESS1_STD = null,
1126 ADDRESS2_STD = null,
1127 ADDRESS3_STD = null,
1128 ADDRESS4_STD = null,
1129 CITY_STD = null,
1130 PROV_STATE_ADMIN_CODE_STD = null,
1131 COUNTY_STD = null,
1132 COUNTRY_STD = null,
1133 POSTAL_CODE_STD = null
1134 WHERE batch_id = p_batch_id;
1135
1136 -- 4. call dqm cleanup routine
1137 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Calling DQM pre-import cleanup');
1138 IF p_run_batch_dedup = 'Y' OR p_run_registry_dedup = 'Y' THEN
1139 HZ_IMP_DQM_STAGE.dqm_pre_imp_cleanup(
1140 p_batch_id => p_batch_id,
1141 x_return_status => l_return_status,
1142 x_msg_count => l_msg_count,
1143 x_msg_data => l_msg_data);
1144
1145 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1146 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** '||FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE));
1147 RETURN;
1148 END IF;
1149
1150 END IF; -- p_run_batch_dedup = 'Y' OR p_run_registry_dedup = 'Y'
1151
1152 END IF; -- r_batch_info.what_if_flag = 'Y'
1153
1154 END IF; -- l_req_data IS NULL
1155
1156 -- calculate number of workers
1157 IF r_batch_info.load_type = 'CSV' THEN
1158 l_num_of_workers := 1;
1159 ELSE
1160 l_num_of_workers := fnd_profile.value('HZ_IMP_NUM_OF_WORKERS');
1161 IF l_num_of_workers IS NULL THEN
1162 l_num_of_workers := 1;
1163 END IF; --l_num_of_workers IS NULL
1164 END IF; --r_batch_info.load_type = 'CSV'
1165
1166 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Number of workers used : '||l_num_of_workers);
1167
1168 -- call the batch deduplication process if needed.
1169 IF NVL(p_run_batch_dedup,'N') = 'Y' AND l_req_data IS NULL THEN
1170 l_str := 'begin HZ_IMP_LOAD_WRAPPER.DATA_LOAD_PREPROCESSING(:1,:2,:3,:4); end;';
1171 execute immediate l_str using p_batch_id, r_batch_info.original_system, l_what_if, OUT l_dataload_rerun;
1172 l_work_unit := 'Y';
1173
1174 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Called data load wrapper to generate work units');
1175
1176 -- submit batch dedup program
1177 l_bd_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1178 'AR',
1179 'ARHDIDIP',
1180 '',
1181 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1182 true,
1183 p_batch_id,
1184 p_batch_dedup_rule_id,
1185 l_num_of_workers
1186 );
1187
1188 IF l_bd_sub_request = 0 THEN
1189 UPDATE hz_imp_batch_summary
1190 SET main_conc_status = 'COMPLETED',
1191 batch_status = 'ACTION_REQUIRED'
1192 WHERE batch_id = p_batch_id;
1193 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting batch de-duplication');
1194 RETURN;
1195 ELSE
1196 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for batch de-dupication');
1197
1198 UPDATE hz_imp_batch_summary
1199 SET batch_dedup_req_id = l_bd_sub_request
1200 WHERE batch_id = p_batch_id;
1201
1202 END IF;
1203
1204 -- wait for the conc program to finish
1205 fnd_conc_global.set_req_globals(
1206 conc_status => 'PAUSED',
1207 request_data => 'BATCH_DEDUP');
1208
1209 ELSE
1210 IF NVL(p_run_batch_dedup,'N') = 'N' AND l_req_data IS NULL THEN
1211 -- just set the l_req_data
1212 fnd_conc_global.set_req_globals(
1213 conc_status => 'NORMAL',
1214 request_data => 'SKIP_BATCH_DEDUP');
1215 l_req_data := fnd_conc_global.request_data;
1216 END IF;
1217
1218 END IF; -- p_run_batch_dedup = 'Y' AND l_req_data IS NULL
1219
1220 -- call the address validation process if needed.
1221
1222 IF (l_req_data = 'BATCH_DEDUP' OR l_req_data = 'SKIP_BATCH_DEDUP')
1223 THEN
1224 -- apply the batch actions if this is a complete import run
1225 -- and batch de-duplication was performed
1226 IF p_import_run_option = 'COMPLETE' AND
1227 p_run_batch_dedup = 'Y'
1228 THEN
1229 HZ_BATCH_ACTION_PUB.batch_dedup_action(
1230 p_batch_id => p_batch_id,
1231 p_action_on_parties => nvl(p_batch_dedup_action, r_batch_info.bd_action_on_parties),
1232 p_action_on_addresses => nvl(p_batch_dedup_action, r_batch_info.bd_action_on_addresses),
1233 p_action_on_contacts => nvl(p_batch_dedup_action, r_batch_info.bd_action_on_contacts),
1234 p_action_on_contact_points => nvl(p_batch_dedup_action, r_batch_info.bd_action_on_contact_points),
1235 x_return_status => l_return_status,
1236 x_msg_data => l_msg_data,
1237 x_msg_count => l_msg_count
1238 );
1239
1240 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1241 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** '||FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE));
1242 RETURN;
1243 END IF;
1244 END IF; /***p_import_run_option = 'COMPLETE' AND p_run_batch_dedup = 'Y' ***/
1245
1246 IF NVL(p_run_addr_val,'N') = 'Y' THEN
1247 -- submit address validation program
1248 l_av_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1249 'AR',
1250 'ARHADDRM',
1251 '',
1252 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1253 true,
1254 p_batch_id
1255 );
1256
1257 IF l_av_sub_request = 0 THEN
1258 UPDATE hz_imp_batch_summary
1259 SET main_conc_status = 'COMPLETED',
1260 batch_status = 'ACTION_REQUIRED'
1261 WHERE batch_id = p_batch_id;
1262 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting address validation');
1263 RETURN;
1264 ELSE
1265 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for addrress validation');
1266
1267 UPDATE hz_imp_batch_summary
1268 SET addr_val_req_id = l_av_sub_request
1269 WHERE batch_id = p_batch_id;
1270 END IF;
1271
1272 -- wait for the con program to finish
1273 fnd_conc_global.set_req_globals(
1274 conc_status => 'PAUSED',
1275 request_data => 'ADDR_VAL');
1276 else
1277 -- skip address validation
1278 fnd_conc_global.set_req_globals(
1279 conc_status => 'NORMAL',
1280 request_data => 'SKIP_ADDR_VAL');
1281 l_req_data := fnd_conc_global.request_data;
1282 end if;
1283
1284 end if;
1285
1286 -- call the data load process
1287 IF l_req_data = 'ADDR_VAL' OR l_req_data = 'SKIP_ADDR_VAL' THEN
1288
1289 IF p_import_run_option = 'COMPLETE' THEN
1290 l_what_if := null;
1291 ELSIF p_import_run_option = 'WHAT_IF' THEN
1292 l_what_if := 'A';
1293 END IF;
1294
1295 -- DQM cleanup for staging reuse
1296 IF r_batch_info.registry_dedup_flag = 'Y' THEN
1297 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Calling DQM intermediate cleanup');
1298 HZ_IMP_DQM_STAGE.dqm_inter_imp_cleanup(
1299 p_batch_id => p_batch_id,
1300 x_return_status => l_return_status,
1301 x_msg_count => l_msg_count,
1302 x_msg_data => l_msg_data);
1303
1304 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1305 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** '||FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE));
1306 RETURN;
1307 END IF;
1308 END IF;
1309
1310 IF l_work_unit <> 'Y' THEN
1311 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Calling data load preprocessing for work unit calculation');
1312 l_str := 'begin HZ_IMP_LOAD_WRAPPER.DATA_LOAD_PREPROCESSING(:1,:2,:3,:4); end;';
1313 execute immediate l_str using p_batch_id, r_batch_info.original_system, l_what_if, OUT l_dataload_rerun;
1314 END IF;
1315
1316 -- submit data load program
1317 IF r_batch_info.load_type = 'CSV' THEN
1318 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Calling ARHLWRPO - Online Data Load');
1319 l_dl_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1320 'AR',
1321 'ARHLWRPO',
1322 '',
1323 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1324 true,
1325 p_batch_id,
1326 r_batch_info.original_system,
1327 l_what_if,
1328 NVL(p_run_registry_dedup,'N'),
1329 nvl(p_registry_dedup_rule_id, r_batch_info.registry_dedup_match_rule_id),
1330 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1331 nvl(r_batch_info.error_limit,FND_PROFILE.value('HZ_IMP_ERROR_LIMIT')),
1332 l_dataload_rerun,
1333 fnd_global.conc_request_id,
1334 fnd_global.prog_appl_id,
1335 fnd_global.conc_program_id
1336 );
1337
1338 ELSE
1339 -- generate number of workers
1340 -- wawong r_batch_info.load_type <> 'CSV' here
1341 /*
1342 IF r_batch_info.load_type = 'CSV' THEN
1343 l_num_of_workers := 1;
1344 ELSE
1345 */
1346 l_num_of_workers := fnd_profile.value('HZ_IMP_NUM_OF_WORKERS');
1347 IF l_num_of_workers IS NULL THEN
1348 l_num_of_workers := 1;
1349 END IF;
1350 --END IF;
1351
1352 FND_FILE.PUT_LINE(FND_FILE.LOG, 'UIC***** Calculated number of workers : '||l_num_of_workers);
1353 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Calling ARHLWRPB - Batch Data Load');
1354
1355 l_dl_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1356 'AR',
1357 'ARHLWRPB',
1358 '',
1359 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1360 true,
1361 p_batch_id,
1362 r_batch_info.original_system,
1363 l_what_if,
1364 NVL(p_run_registry_dedup,'N'),
1365 nvl(p_registry_dedup_rule_id, r_batch_info.registry_dedup_match_rule_id),
1366 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1367 l_num_of_workers,
1368 nvl(r_batch_info.error_limit,FND_PROFILE.value('HZ_IMP_ERROR_LIMIT')),
1369 l_dataload_rerun,
1370 fnd_global.conc_request_id,
1371 fnd_global.prog_appl_id,
1372 fnd_global.conc_program_id
1373 );
1374
1375 END IF;
1376
1377 IF l_dl_sub_request = 0 THEN
1378 UPDATE hz_imp_batch_summary
1379 SET main_conc_status = 'COMPLETED',
1380 batch_status = 'ACTION_REQUIRED'
1381 WHERE batch_id = p_batch_id;
1382 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting data load');
1383 RETURN;
1384 ELSE
1385 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for data load');
1386
1387 -- update batch summary table and create row in batch detail table
1388 get_current_run(
1389 p_batch_id => p_batch_id,
1390 x_run_number => l_current_run);
1391
1392 UPDATE hz_imp_batch_summary
1393 SET import_req_id = l_dl_sub_request
1394 WHERE batch_id = p_batch_id;
1395
1396 END IF;
1397
1398 -- wait for the con program to finish
1399 fnd_conc_global.set_req_globals(
1400 conc_status => 'PAUSED',
1401 request_data => 'DATA_LOAD');
1402
1403 END IF; -- p_run_addr_val = 'Y' AND l_req_data = 'BACTH_DEDUP'
1404
1405 IF l_req_data = 'DATA_LOAD' THEN
1406 -- final steps on import
1407 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Performing the post processes');
1408
1409 IF p_import_run_option = 'COMPLETE' THEN
1410
1411 -- generate number of workers
1412 IF r_batch_info.load_type = 'CSV' THEN
1413 l_num_of_workers := 1;
1414 ELSE
1415 l_num_of_workers := fnd_profile.value('HZ_IMP_NUM_OF_WORKERS');
1416 IF l_num_of_workers IS NULL THEN
1417 l_num_of_workers := 1;
1418 END IF;
1419 END IF;
1420
1421 -- call the DQM cleanup routine
1422 IF r_batch_info.registry_dedup_flag = 'Y' OR r_batch_info.batch_dedup_flag = 'Y' THEN
1423 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Calling DQM post import cleanup');
1424 HZ_IMP_DQM_STAGE.dqm_post_imp_cleanup
1425 (p_batch_id => p_batch_id,
1426 x_return_status => l_return_status,
1427 x_msg_count => l_msg_count,
1428 x_msg_data => l_msg_data);
1429 END IF;
1430
1431 /*
1432 SELECT registry_dedup_flag
1433 INTO l_reg_dedup
1434 FROM hz_imp_batch_summary
1435 WHERE batch_id = p_batch_id;
1436 */
1437 IF NVL(r_batch_info.registry_dedup_flag,'N') = 'Y' THEN
1438
1439 -- call the report dupsets API
1440 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Calling DQM interface tca sanitize report');
1441 HZ_DQM_DUP_ID_PKG.interface_tca_sanitize_report
1442 (p_batch_id => p_batch_id,
1443 p_match_rule_id => p_registry_dedup_rule_id,
1444 p_request_id => fnd_global.conc_request_id,
1445 x_dup_batch_id => l_dup_batch_id,
1446 x_return_status => l_return_status,
1447 x_msg_count => l_msg_count,
1448 x_msg_data => l_msg_data);
1449
1450 get_current_run(
1451 p_batch_id => p_batch_id,
1452 x_run_number => l_current_run);
1453
1454 UPDATE hz_imp_batch_details
1455 SET dup_batch_id = l_dup_batch_id
1456 WHERE batch_id = p_batch_id
1457 AND run_number = l_current_run;
1458
1459 IF p_run_automerge = 'Y' THEN
1460 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Invoking auto merge post process');
1461 l_am_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1462 'AR',
1463 'ARHAMRGP',
1464 '',
1465 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1466 true,
1467 to_char(l_dup_batch_id),
1468 to_char(l_num_of_workers)
1469 );
1470
1471 IF l_am_sub_request = 0 THEN
1472 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting auto merge post process');
1473 ELSE
1474 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for auto merge post process');
1475 END IF;
1476
1477 END IF;
1478 END IF; --NVL(r_batch_info.registry_dedup_flag,'N') = 'Y'
1479
1480 IF (Nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'),'Y') <> 'DISABLE') THEN --Bug 10435345
1481 -- Bug 4594407 : Call DQM Sync Index
1482 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Calling Parallel Sync Index concurrent program');
1483 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Request Id of the program to be waited on, that is being passed to this : ' || fnd_global.conc_request_id );
1484 l_index_conc_program_req_id := FND_REQUEST.SUBMIT_REQUEST('AR',
1485 'ARHDQMPP',
1486 'DQM Parallel Sync Index Parent Program',
1487 NULL,
1488 FALSE,
1489 fnd_global.conc_request_id
1490 );
1491 IF l_index_conc_program_req_id = 0 THEN
1492 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error submitting DQM Sync Index Program.');
1493 ELSE
1494 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Request Id of Parallel Sync concurrent Program is : ' || l_index_conc_program_req_id );
1495 END IF;
1496 END IF;
1497
1498 -- call dataload post processing
1499 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Invoking data load post process');
1500 FOR i IN 1..l_num_of_workers LOOP
1501
1502 l_pp_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1503 'AR',
1504 'ARHLPPLB',
1505 '',
1506 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1507 true,
1508 p_batch_id,
1509 r_batch_info.original_system,
1510 l_batch_mode_flag,
1511 to_char(fnd_global.conc_request_id),
1512 p_generate_fuzzy_key
1513 );
1514
1515 IF l_pp_sub_request = 0 THEN
1516 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting data load post processing ' || l_num_of_workers);
1517 ELSE
1518 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for data load post processing ' || l_num_of_workers);
1519 END IF;
1520 END LOOP;
1521
1522 -- wait for the conc program to finish
1523 fnd_conc_global.set_req_globals(
1524 conc_status => 'PAUSED',
1525 request_data => 'POST_PROCESS');
1526
1527 ELSIF p_import_run_option = 'WHAT_IF' THEN
1528 final_steps_whatif(
1529 p_batch_id => p_batch_id,
1530 x_return_status => l_return_status,
1531 x_msg_count => l_msg_count,
1532 x_msg_data => l_msg_data
1533 );
1534
1535 fnd_conc_global.set_req_globals(
1536 conc_status => 'NORMAL',
1537 request_data => 'POST_PROCESS');
1538 l_req_data := 'POST_PROCESS';
1539
1540 END IF; -- p_import_run_option = 'COMPLETE'/'WHAT_IF'
1541
1542 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1543 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** '||FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE));
1544 RETURN;
1545 END IF;
1546
1547 END IF; --l_req_data = 'DATA_LOAD'
1548
1549 END IF; -- p_import_run_option = 'WHAT_IF'/'COMPLETE'
1550
1551
1552
1553
1554
1555
1556
1557
1558 -- if it is continuation from what-if, then do the following
1559 -- //////////////////////////////////
1560 -- continue to import from what if
1561 -- //////////////////////////////////
1562
1563 -- if it is continuation from what-if, then do the following
1564 IF p_import_run_option = 'CONTINUE' THEN
1565
1566 IF l_req_data IS NULL THEN
1567
1568 -- set the data load status to pending
1569 UPDATE hz_imp_batch_summary
1570 SET import_status = 'PENDING',
1571 main_conc_status = 'PROCESSING',
1572 main_conc_req_id = fnd_global.conc_request_id
1573 WHERE batch_id = p_batch_id;
1574
1575 -- apply the batch de-duplication actions if needed.
1576 -- this is done becuase during what-if run, batch deduplication actions
1577 -- will not be performed, the options will just be stored. the actions will
1578 -- be performed only when users continue to import.
1579
1580 -- IF nvl(p_run_batch_dedup, r_batch_info.batch_dedup_flag) = 'Y' THEN
1581 IF (r_batch_info.batch_dedup_flag = 'Y') THEN
1582
1583 -- call batch dedup actions api with the r_batch_info.bd_action_on* parameters
1584 IF r_batch_info.import_status = 'ACTION_REQUIRED' THEN
1585
1586 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Calling the batch de-duplication actions API');
1587 HZ_BATCH_ACTION_PUB.batch_dedup_action(
1588 p_batch_id => p_batch_id,
1589 p_action_on_parties => nvl(p_batch_dedup_action, r_batch_info.bd_action_on_parties),
1590 p_action_on_addresses => nvl(p_batch_dedup_action, r_batch_info.bd_action_on_addresses),
1591 p_action_on_contacts => nvl(p_batch_dedup_action, r_batch_info.bd_action_on_contacts),
1592 p_action_on_contact_points => nvl(p_batch_dedup_action, r_batch_info.bd_action_on_contact_points),
1593 x_return_status => l_return_status,
1594 x_msg_data => l_msg_data,
1595 x_msg_count => l_msg_count
1596 );
1597
1598 END IF; -- r_batch_info.import_status = 'ACTION_REQUIRED'
1599
1600 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1601 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** '||FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE));
1602 RETURN;
1603 END IF;
1604
1605 END IF; -- r_batch_info.batch_dedup_flag = 'Y'
1606
1607 -- apply the registry deduplication actions if needed.
1608 -- again the options are recorded in the batch summery table previously.
1609 IF r_batch_info.registry_dedup_flag = 'Y' THEN
1610
1611 -- call registry dedup actions api with the r_batch_info.rd_action_on* parameters
1612 -- call to be added later since it is only needed for UI pages
1613 IF r_batch_info.rd_action_new_parties IS NOT NULL
1614 OR
1615 r_batch_info.rd_action_existing_parties IS NOT NULL
1616 OR
1617 r_batch_info.rd_action_dup_parties IS NOT NULL
1618 OR
1619 r_batch_info.rd_action_pot_dup_parties IS NOT NULL
1620 OR
1621 r_batch_info.rd_action_new_addrs IS NOT NULL
1622 OR
1623 r_batch_info.rd_action_existing_addrs IS NOT NULL
1624 OR
1625 r_batch_info.rd_action_pot_dup_addrs IS NOT NULL
1626 OR
1627 r_batch_info.rd_action_new_contacts IS NOT NULL
1628 OR
1629 r_batch_info.rd_action_existing_contacts IS NOT NULL
1630 OR
1631 r_batch_info.rd_action_pot_dup_contacts IS NOT NULL
1632 OR
1633 r_batch_info.rd_action_new_cpts IS NOT NULL
1634 OR
1635 r_batch_info.rd_action_existing_cpts IS NOT NULL
1636 OR
1637 r_batch_info.rd_action_pot_dup_cpts IS NOT NULL
1638 OR
1639 r_batch_info.rd_action_new_supents IS NOT NULL
1640 OR
1641 r_batch_info.rd_action_existing_supents IS NOT NULL
1642 OR
1643 r_batch_info.rd_action_new_finents IS NOT NULL
1644 OR
1645 r_batch_info.rd_action_existing_finents IS NOT NULL
1646 THEN
1647 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Calling the registry de-duplication actions API');
1648 HZ_BATCH_ACTION_PUB.registry_dedup_action(
1649 p_batch_id => p_batch_id,
1650 p_action_new_parties => r_batch_info.rd_action_new_parties,
1651 p_action_existing_parties => r_batch_info.rd_action_existing_parties,
1652 p_action_dup_parties => r_batch_info.rd_action_dup_parties,
1653 p_action_pot_dup_parties => r_batch_info.rd_action_pot_dup_parties,
1654 p_action_new_addrs => r_batch_info.rd_action_new_addrs,
1655 p_action_existing_addrs => r_batch_info.rd_action_existing_addrs,
1656 p_action_pot_dup_addrs => r_batch_info.rd_action_pot_dup_addrs,
1657 p_action_new_contacts => r_batch_info.rd_action_new_contacts,
1658 p_action_existing_contacts => r_batch_info.rd_action_existing_contacts,
1659 p_action_pot_dup_contacts => r_batch_info.rd_action_pot_dup_contacts,
1660 p_action_new_cpts => r_batch_info.rd_action_new_cpts,
1661 p_action_existing_cpts => r_batch_info.rd_action_existing_cpts,
1662 p_action_pot_dup_cpts => r_batch_info.rd_action_pot_dup_cpts,
1663 p_action_new_supents => r_batch_info.rd_action_new_supents,
1664 p_action_existing_supents => r_batch_info.rd_action_existing_supents,
1665 p_action_new_finents => r_batch_info.rd_action_new_finents,
1666 p_action_existing_finents => r_batch_info.rd_action_existing_finents,
1667 x_return_status => l_return_status,
1668 x_msg_count => l_msg_count,
1669 x_msg_data => l_msg_data
1670 );
1671 END IF;
1672
1673 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1674 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** '||FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE));
1675 RETURN;
1676 END IF;
1677
1678 END IF; -- r_batch_info.registry_dedup_flag = 'Y'
1679
1680 -- set l_what_if to 'R' to inform it is a resume / continue option
1681 l_what_if := 'R';
1682
1683 -- call create work unit api (if not already called) and then call data load
1684 l_str := 'begin HZ_IMP_LOAD_WRAPPER.DATA_LOAD_PREPROCESSING(:1,:2,:3,:4); end;';
1685 execute immediate l_str using p_batch_id, r_batch_info.original_system, l_what_if, OUT l_dataload_rerun;
1686
1687 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Called data load wrapper to generate work units');
1688
1689 IF r_batch_info.load_type = 'CSV' THEN
1690
1691 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Calling ARHLWRPO - Online Data Load');
1692
1693 l_dl_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1694 'AR',
1695 'ARHLWRPO',
1696 '',
1697 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1698 true,
1699 p_batch_id,
1700 r_batch_info.original_system,
1701 l_what_if,
1702 NVL(r_batch_info.registry_dedup_flag,'N'),
1703 r_batch_info.registry_dedup_match_rule_id,
1704 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1705 nvl(r_batch_info.error_limit,FND_PROFILE.value('HZ_IMP_ERROR_LIMIT')),
1706 l_dataload_rerun,
1707 fnd_global.conc_request_id,
1708 fnd_global.prog_appl_id,
1709 fnd_global.conc_program_id
1710 );
1711
1712 ELSE
1713
1714 -- generate number of workers
1715 IF r_batch_info.load_type = 'CSV' THEN
1716 l_num_of_workers := 1;
1717 ELSE
1718 l_num_of_workers := fnd_profile.value('HZ_IMP_NUM_OF_WORKERS');
1719 IF l_num_of_workers IS NULL THEN
1720 l_num_of_workers := 1;
1721 END IF;
1722 END IF;
1723
1724 FND_FILE.PUT_LINE(FND_FILE.LOG, 'UIC***** Calculated number of workers : '||l_num_of_workers);
1725 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Calling ARHLWRPB - Batch Data Load');
1726
1727 l_dl_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1728 'AR',
1729 'ARHLWRPB',
1730 '',
1731 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1732 true,
1733 p_batch_id,
1734 r_batch_info.original_system,
1735 l_what_if,
1736 NVL(r_batch_info.registry_dedup_flag,'N'),
1737 r_batch_info.registry_dedup_match_rule_id,
1738 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1739 l_num_of_workers,
1740 nvl(r_batch_info.error_limit,FND_PROFILE.value('HZ_IMP_ERROR_LIMIT')),
1741 l_dataload_rerun,
1742 fnd_global.conc_request_id,
1743 fnd_global.prog_appl_id,
1744 fnd_global.conc_program_id
1745 );
1746
1747 END IF; -- IF r_batch_info.load_type = 'CSV'
1748
1749 IF l_dl_sub_request = 0 THEN
1750 UPDATE hz_imp_batch_summary
1751 SET main_conc_status = 'COMPLETED',
1752 batch_status = 'ACTION_REQUIRED'
1753 WHERE batch_id = p_batch_id;
1754 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting data load');
1755 RETURN;
1756 ELSE
1757 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for data load');
1758
1759 -- update batch summary table and create row in batch detail table
1760 get_current_run(
1761 p_batch_id => p_batch_id,
1762 x_run_number => l_current_run);
1763
1764 UPDATE hz_imp_batch_summary
1765 SET import_req_id = l_dl_sub_request
1766 WHERE batch_id = p_batch_id;
1767
1768 END IF;
1769
1770 -- wait for the con program to finish
1771 fnd_conc_global.set_req_globals(
1772 conc_status => 'PAUSED',
1773 request_data => 'DATA_LOAD');
1774
1775 END IF; -- l_req_data IS NULL
1776
1777 IF l_req_data = 'DATA_LOAD' THEN
1778 -- final steps on import
1779 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Performing the post processes');
1780
1781 -- generate number of workers
1782 IF r_batch_info.load_type = 'CSV' THEN
1783 l_num_of_workers := 1;
1784 ELSE
1785 l_num_of_workers := fnd_profile.value('HZ_IMP_NUM_OF_WORKERS');
1786 IF l_num_of_workers IS NULL THEN
1787 l_num_of_workers := 1;
1788 END IF;
1789 END IF;
1790
1791 IF r_batch_info.registry_dedup_flag = 'Y' OR r_batch_info.batch_dedup_flag = 'Y' THEN
1792 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Calling DQM post import cleanup');
1793 HZ_IMP_DQM_STAGE.dqm_post_imp_cleanup
1794 (p_batch_id => p_batch_id,
1795 x_return_status => l_return_status,
1796 x_msg_count => l_msg_count,
1797 x_msg_data => l_msg_data);
1798 END IF;
1799
1800 /*
1801 SELECT registry_dedup_flag
1802 INTO l_reg_dedup
1803 FROM hz_imp_batch_summary
1804 WHERE batch_id = p_batch_id;
1805 */
1806 IF NVL(r_batch_info.registry_dedup_flag,'N') = 'Y' THEN
1807
1808 -- call the report dupsets API
1809 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Calling DQM interface tca sanitize report');
1810 HZ_DQM_DUP_ID_PKG.interface_tca_sanitize_report
1811 (p_batch_id => p_batch_id,
1812 p_match_rule_id => r_batch_info.registry_dedup_match_rule_id,
1813 p_request_id => fnd_global.conc_request_id,
1814 x_dup_batch_id => l_dup_batch_id,
1815 x_return_status => l_return_status,
1816 x_msg_count => l_msg_count,
1817 x_msg_data => l_msg_data);
1818
1819 get_current_run(
1820 p_batch_id => p_batch_id,
1821 x_run_number => l_current_run);
1822
1823 UPDATE hz_imp_batch_details
1824 SET dup_batch_id = l_dup_batch_id
1825 WHERE batch_id = p_batch_id
1826 AND run_number = l_current_run;
1827
1828 IF p_run_automerge = 'Y' THEN
1829 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Invoking auto merge post process');
1830 l_am_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1831 'AR',
1832 'ARHAMRGP',
1833 '',
1834 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1835 true,
1836 to_char(l_dup_batch_id),
1837 to_char(l_num_of_workers)
1838 );
1839
1840 IF l_am_sub_request = 0 THEN
1841 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting auto merge post process');
1842 ELSE
1843 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for auto merge post process');
1844 END IF;
1845
1846 END IF;
1847 END IF; --NVL(r_batch_info.r_batch_info.registry_dedup_flag,'N') = 'Y'
1848
1849 IF (Nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'),'Y') <> 'DISABLE') THEN --Bug 10435345
1850 -- Bug 4594407 : Call DQM Sync Index
1851 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Calling Parallel Sync Index concurrent program');
1852 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Request Id of the program to be waited on, that is being passed to this : ' || fnd_global.conc_request_id );
1853 l_index_conc_program_req_id := FND_REQUEST.SUBMIT_REQUEST('AR',
1854 'ARHDQMPP',
1855 'DQM Parallel Sync Index Parent Program',
1856 NULL,
1857 FALSE,
1858 fnd_global.conc_request_id
1859 );
1860 IF l_index_conc_program_req_id = 0 THEN
1861 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error submitting DQM Sync Index Program.');
1862 ELSE
1863 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Request Id of Parallel Sync concurrent Program is : ' || l_index_conc_program_req_id );
1864 END IF;
1865 END IF;
1866
1867 -- call dataload post processing
1868 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Invoking data load post process');
1869 FOR i IN 1..l_num_of_workers LOOP
1870 l_pp_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1871 'AR',
1872 'ARHLPPLB',
1873 '',
1874 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1875 true,
1876 p_batch_id,
1877 r_batch_info.original_system,
1878 l_batch_mode_flag,
1879 to_char(fnd_global.conc_request_id),
1880 p_generate_fuzzy_key
1881 );
1882
1883 IF l_pp_sub_request = 0 THEN
1884 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting data load post processing ' || l_num_of_workers);
1885 ELSE
1886 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for data load post processing ' || l_num_of_workers);
1887 END IF;
1888 END LOOP;
1889
1890 -- wait for the conc program to finish
1891 fnd_conc_global.set_req_globals(
1892 conc_status => 'PAUSED',
1893 request_data => 'POST_PROCESS');
1894
1895 END IF; --l_req_data = 'DATA_LOAD'
1896
1897 END IF; -- p_import_run_option = 'CONTINUE'
1898
1899
1900 IF l_req_data = 'POST_PROCESS' THEN
1901
1902 /* Clean up staging if not a what-if */
1903 IF p_import_run_option <> 'WHAT_IF'
1904 THEN
1905 CLEANUP_STAGING(p_batch_id, l_batch_mode_flag);
1906 END IF;
1907
1908 -- program completed successfully
1909 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Program completed successfully');
1910
1911 /* Delete Work Unit if not what-if */
1912 IF p_import_run_option <> 'WHAT_IF'
1913 and (r_batch_info.import_status='COMPLETED'
1914 or r_batch_info.import_status='COMPL_ERRORS')
1915 THEN
1916 delete hz_imp_work_units where batch_id = P_BATCH_ID;
1917 END IF;
1918
1919 -- set the data load status to COMPLETED
1920 UPDATE hz_imp_batch_summary
1921 SET main_conc_status = 'COMPLETED',
1922 batch_status = decode(r_batch_info.import_status,'COMPLETED','COMPLETED','ACTION_REQUIRED')
1923 WHERE batch_id = p_batch_id;
1924
1925
1926 l_last_req := r_batch_info.import_req_id;
1927 l_call_status := fnd_concurrent.get_request_status(
1928 l_last_req,
1929 null,
1930 null,
1931 l_temp_rphase,
1932 l_temp_rstatus,
1933 l_temp_dphase,
1934 l_temp_dstatus,
1935 l_temp_message);
1936
1937 IF l_temp_dstatus <> 'NORMAL' OR r_batch_info.import_status = 'ERROR'
1938 THEN
1939 l_wng_msg := l_wng_msg||' Unexpected error occured in the Data Load program.';
1940 END IF;
1941
1942 IF p_import_run_option <> 'WHAT_IF'
1943 THEN
1944 open c_pp_error(p_batch_id);
1945 fetch c_pp_error into l_pp_error;
1946 close c_pp_error;
1947 END IF;
1948
1949 IF l_pp_error = 'Y'
1950 THEN
1951 l_wng_msg := l_wng_msg||' Unexpected error occured in the Post Processing program.';
1952 END IF;
1953
1954
1955 IF l_wng_msg<>'WARNING****'
1956 THEN
1957 errbuf := l_wng_msg;
1958 retcode := 1;
1959 END IF;
1960
1961
1962
1963 END IF;
1964 EXCEPTION
1965 WHEN OTHERS THEN
1966 Errbuf := fnd_message.get||' '||SQLERRM;
1967 Retcode := 2;
1968
1969 END;
1970
1971 END HZ_BATCH_IMPORT_PKG;