[Home] [Help]
PACKAGE BODY: APPS.HZ_BATCH_IMPORT_PKG
Source
1 PACKAGE BODY HZ_BATCH_IMPORT_PKG AS
2 /*$Header: ARHIBASB.pls 120.28 2006/03/24 06:49:37 vravicha noship $ */
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;
490 l_dup_batch_id NUMBER;
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 */
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;
627
624 errbuf := 'WARNING**** Unexpected error occured Batch de-duplication program.';
625 RETURN;
626 END IF;
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 -- Bug 4594407 : Call DQM Sync Index
755 'ARHDQMPP',
752 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Calling Parallel Sync Index concurrent program');
753 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 );
754 l_index_conc_program_req_id := FND_REQUEST.SUBMIT_REQUEST('AR',
756 'DQM Parallel Sync Index Parent Program',
757 NULL,
758 FALSE,
759 fnd_global.conc_request_id
760 );
761 IF l_index_conc_program_req_id = 0 THEN
762 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error submitting DQM Sync Index Program.');
763 ELSE
764 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Request Id of Parallel Sync concurrent Program is : ' || l_index_conc_program_req_id );
765 END IF;
766
767 -- call dataload post processing
768 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Invoking data load post process');
769 FOR i IN 1..l_num_of_workers LOOP
770 l_pp_sub_request := FND_REQUEST.SUBMIT_REQUEST(
771 'AR',
772 'ARHLPPLB',
773 '',
774 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
775 true,
776 p_batch_id,
777 r_batch_info.original_system,
778 l_batch_mode_flag,
779 to_char(fnd_global.conc_request_id),
780 p_generate_fuzzy_key
781 );
782
783 IF l_pp_sub_request = 0 THEN
784 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting data load post processing ' || l_num_of_workers);
785 ELSE
786 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for data load post processing ' || l_num_of_workers);
787 END IF;
788 END LOOP;
789
790 -- wait for the conc program to finish
791 fnd_conc_global.set_req_globals(
792 conc_status => 'PAUSED',
793 request_data => 'POST_PROCESS');
794 ELSE
795 fnd_conc_global.set_req_globals(
796 conc_status => 'NORMAL',
797 request_data => 'POST_PROCESS');
798 l_req_data := 'POST_PROCESS'; -- not necessary
799 retcode := 1;
800 errbuf := 'WARNING**** Unexpected error occured in the Data Load program';
801 END IF;
802
803 ELSIF p_import_run_option = 'WHAT_IF' THEN
804
805 final_steps_whatif(
806 p_batch_id => p_batch_id,
807 x_return_status => l_return_status,
808 x_msg_count => l_msg_count,
809 x_msg_data => l_msg_data
810 );
811
812 fnd_conc_global.set_req_globals(
813 conc_status => 'NORMAL',
814 request_data => 'POST_PROCESS');
815 l_req_data := 'POST_PROCESS'; -- not necessary
816 retcode := 1;
817 errbuf := 'WARNING**** Unexpected error occured in the Data Load program';
818
819 END IF; -- p_import_run_option = 'COMPLETE'/'WHAT_IF'
820
821 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
822 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** '||FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE));
823 RETURN;
824 END IF;
825
826 RETURN;
827 END IF;
828
829 -----------------------------
830 -- do the one time processing
831 -----------------------------
832
833 IF l_req_data IS NULL THEN
834 ------------------------------
835 -- print the parameters passed
836 ------------------------------
837 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Parameters passed');
838 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** -----------------');
839 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Batch ID: '||p_batch_id);
840 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Import Run Option: '||p_import_run_option);
841 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Run Batch Dedup?: '||p_run_batch_dedup);
842 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Batch dedup rule id: '||p_batch_dedup_rule_id);
843 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Batch dedup action: '||p_batch_dedup_action);
844 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Run addr val?: '||p_run_addr_val);
845 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Run registry dedup?: '||p_run_registry_dedup);
846 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Registry dedup rule id?: '||p_registry_dedup_rule_id);
847 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Run Automerge?: '||p_run_automerge);
848 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** -----------------');
849 FND_FILE.PUT_LINE (FND_FILE.LOG, '');
850
851 ---------------------
852 -- validate the batch
853 ---------------------
854
855 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Validating the batch');
856
857 -- Bug 4079902. Validated orig_system against HZ_ORIG_SYSTEMS_B instead of
858 -- the lookup.
859
860 -- validate original_system against lookup ORIG_SYSTEM
861 /*
862 hz_utility_v2pub.validate_lookup (
863 p_column => 'original_system',
864 p_lookup_type => 'ORIG_SYSTEM',
865 p_column_value => r_batch_info.original_system,
866 x_return_status => l_return_status);
867 */
868 BEGIN
869 SELECT 'Y' INTO os_exists_flag
873 AND orig_system<>'SST'
870 FROM hz_orig_systems_b
871 WHERE
872 orig_system= r_batch_info.original_system
874 AND status='A';
875 EXCEPTION
876 WHEN NO_DATA_FOUND THEN
877 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_FK');
878 FND_MESSAGE.SET_TOKEN('FK','orig_system');
879 FND_MESSAGE.SET_TOKEN('COLUMN','orig_system');
880 FND_MESSAGE.SET_TOKEN('TABLE','HZ_ORIG_SYSTEMS_B');
881
882 -- Bug 4530477
883 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error***** '||FND_MESSAGE.GET);
884
885 UPDATE hz_imp_batch_summary
886 SET main_conc_status = 'ERROR',
887 batch_status = 'ACTION_REQUIRED'
888 WHERE batch_id = p_batch_id;
889
890 Errbuf := fnd_message.get;
891 Retcode := 2;
892
893 RETURN;
894 END;
895
896 BEGIN
897 IF p_run_registry_dedup = 'Y'
898 and p_registry_dedup_rule_id IS NOT NULL
899 THEN
900 IF p_run_automerge='Y' THEN
901 SELECT automerge_flag
902 INTO l_automerge_flag
903 FROM hz_match_rules_b
904 WHERE match_rule_id = p_registry_dedup_rule_id;
905
906 IF l_automerge_flag is NULL
907 or l_automerge_flag='N'
908 THEN
909
910 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.');
911
912 UPDATE hz_imp_batch_summary
913 SET main_conc_status = 'ERROR',
914 batch_status = 'ACTION_REQUIRED'
915 WHERE batch_id = p_batch_id;
916
917 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.';
918 Retcode := 1;
919
920 RETURN;
921 END IF;
922 END IF;
923 UPDATE HZ_IMP_BATCH_SUMMARY
924 SET AUTOMERGE_FLAG=p_run_automerge
925 WHERE batch_id=p_batch_id;
926 END IF;
927 END;
928
929 -- validate that if it request for CONTINUE, that the what-if has
930 -- already been performed
931 IF p_import_run_option = 'CONTINUE' THEN
932
933 IF not ( NVL(r_batch_info.import_status,'X') in
934 ( 'ACTION_REQUIRED', 'COMPL_ERROR_LIMIT', 'COMPL_ERRORS' )) THEN
935 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Pre-import has not been performed for this batch.');
936 UPDATE hz_imp_batch_summary
937 SET batch_status = 'ACTION_REQUIRED'
938 WHERE batch_id = p_batch_id;
939 RETURN;
940 END IF;
941 END IF;
942
943 -- validate that the batch is not already completed one
944 IF NVL(r_batch_info.import_status,'X') = 'COMPLETED' THEN
945 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** The batch has already been completed.');
946 RETURN;
947 END IF;
948
949 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Completed validation');
950
951 -- update batch summary table and create row in batch detail table
952 get_current_run(
953 p_batch_id => p_batch_id,
954 x_run_number => l_current_run);
955
956 IF l_current_run > 1 THEN
957
958 -- get import_status of last run
959 select import_status
960 into l_last_run_imp_status
961 from hz_imp_batch_details
962 where batch_id = p_batch_id
963 and run_number = l_current_run - 1;
964
965 IF l_last_run_imp_status <> 'PENDING' THEN
966 -- create an entry in the batch details table
967 INSERT INTO hz_imp_batch_details
968 (batch_id,
969 run_number,
970 import_status,
971 import_req_id,
972 created_by,
973 creation_date,
974 last_updated_by,
975 last_update_date,
976 last_update_login,
977 main_conc_req_id)
978 values
979 (p_batch_id,
980 l_current_run,
981 'PENDING',
982 null,
983 HZ_UTILITY_V2PUB.created_by,
984 HZ_UTILITY_V2PUB.creation_date,
985 HZ_UTILITY_V2PUB.last_updated_by,
986 HZ_UTILITY_V2PUB.last_update_date,
987 HZ_UTILITY_V2PUB.last_update_login,
988 fnd_global.conc_request_id);
989
990 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Created entry in batch details table');
991
992 END IF; -- l_last_run_imp_status <> 'PENDING'
993
994 ELSE
995
996 -- create an entry in the batch details table
997 INSERT INTO hz_imp_batch_details
998 (batch_id,
999 run_number,
1000 import_status,
1001 import_req_id,
1002 created_by,
1003 creation_date,
1004 last_updated_by,
1005 last_update_date,
1006 last_update_login,
1007 main_conc_req_id)
1008 values
1009 (p_batch_id,
1010 l_current_run,
1011 'PENDING',
1012 null,
1013 HZ_UTILITY_V2PUB.created_by,
1017 HZ_UTILITY_V2PUB.last_update_login,
1014 HZ_UTILITY_V2PUB.creation_date,
1015 HZ_UTILITY_V2PUB.last_updated_by,
1016 HZ_UTILITY_V2PUB.last_update_date,
1018 fnd_global.conc_request_id);
1019
1020 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Created entry in batch details table');
1021
1022 END IF;
1023
1024 END IF; -- l_req_data IS NULL
1025
1026 /* Clean up staging tables if previous run did not complete successfully */
1027 /* Get latest batch details status */
1028 IF l_req_data is null THEN
1029 IF ((l_last_run_imp_status <> 'ACTION_REQUIRED' OR p_import_run_option <> 'CONTINUE') OR
1030 (l_last_run_imp_status = 'ACTION_REQUIRED' AND p_import_run_option = 'CONTINUE'
1031 AND l_batch_mode_flag = 'Y' AND
1032 STAGING_DATA_EXISTS(p_batch_id, l_batch_mode_flag) <> 'Y')) THEN
1033 CLEANUP_STAGING(p_batch_id, l_batch_mode_flag);
1034 END IF;
1035 END IF;
1036
1037 -----------------------------------
1038 -- count total records in the batch
1039 -----------------------------------
1040
1041
1042 -----------------------
1043 -- start business logic
1044 -----------------------
1045
1046 -- //////////////////////////
1047 -- what if or complete import
1048 -- //////////////////////////
1049
1050 -- if it is what-if or complete import, then do the following.
1051 -- the basic flow is same, except for a few things.
1052 -- the different things are done using the actual option.
1053 IF (p_import_run_option = 'WHAT_IF'
1054 OR
1055 p_import_run_option = 'COMPLETE') THEN
1056
1057 IF l_req_data IS NULL THEN
1058 -- check if the right parameters have been passed,
1059 -- otherwise error out
1060 -- if no match rule has been provided, error out
1061 IF (p_run_batch_dedup = 'Y' and p_batch_dedup_rule_id IS NULL) THEN
1062 l_rule_id_missing := true;
1063 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** FATAL ERROR: No match rule provided for batch dedup.');
1064 END IF;
1065 IF (p_run_registry_dedup = 'Y' and p_registry_dedup_rule_id IS NULL) THEN
1066 l_rule_id_missing := true;
1067 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** FATAL ERROR: No match rule provided for registry dedup.');
1068 END IF;
1069
1070 IF (l_rule_id_missing) then
1071 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Data Import cannot proceed, exit with error');
1072 UPDATE hz_imp_batch_summary
1073 SET main_conc_status = 'ERROR'
1074 WHERE batch_id = p_batch_id;
1075 retcode := 2;
1076 RETURN;
1077 END IF;
1078
1079 -- set the processing status appropriately
1080 UPDATE hz_imp_batch_summary
1081 SET batch_dedup_flag = decode(p_run_batch_dedup, 'Y', 'Y', 'N'),
1082 batch_dedup_status = decode(p_run_batch_dedup, 'Y', 'PENDING', 'DECLINED'),
1083 batch_dedup_match_rule_id = decode(p_run_batch_dedup, 'Y', p_batch_dedup_rule_id, null),
1084 addr_val_flag = decode(p_run_addr_val, 'Y', 'Y', 'N'),
1085 addr_val_status = decode(p_run_addr_val, 'Y', 'PENDING', 'DECLINED'),
1086 registry_dedup_flag = decode(p_run_registry_dedup, 'Y', 'Y', 'N'),
1087 registry_dedup_match_rule_id = decode(p_run_registry_dedup, 'Y', p_registry_dedup_rule_id, null),
1088 import_status = 'PENDING',
1089 what_if_flag = decode(p_import_run_option, 'WHAT_IF', 'Y', 'N'),
1090 main_conc_status = 'PROCESSING',
1091 batch_status = 'PROCESSING',
1092 main_conc_req_id = fnd_global.conc_request_id,
1093 bd_action_on_parties = NVL(p_batch_dedup_action,bd_action_on_parties),
1094 bd_action_on_addresses = NVL(p_batch_dedup_action,bd_action_on_addresses),
1095 bd_action_on_contacts = NVL(p_batch_dedup_action,bd_action_on_contacts),
1096 bd_action_on_contact_points = NVL(p_batch_dedup_action,bd_action_on_contact_points)
1097 WHERE batch_id = p_batch_id;
1098
1099 -- do some cleanup if it is a rerun after what-if
1100 IF r_batch_info.what_if_flag = 'Y' THEN
1101 -- cleanup the previous what-if results
1102 -- 1. call batch deduplication cleanup routine
1103 -- 2. call registry deduplication cleanup routine
1104 -- it is the same cleanup routine that does both the above.
1105 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Cleaning up batch de-duplication actions');
1106 HZ_BATCH_ACTION_PUB.clear_status(
1107 p_batch_id => p_batch_id,
1108 x_return_status => l_return_status);
1109
1110 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1111 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** '||FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE));
1112 RETURN;
1113 END IF;
1114
1115 -- 3. cleanup address validation information
1116 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Cleaning up address validation actions');
1117 UPDATE hz_imp_addresses_int
1118 SET VALIDATION_SUBSET_ID = null,
1119 ACCEPT_STANDARDIZED_FLAG = null,
1120 ADAPTER_CONTENT_SOURCE = null,
1121 ADDR_VALID_STATUS_CODE = null,
1122 DATE_VALIDATED = null,
1123 ADDRESS1_STD = null,
1124 ADDRESS2_STD = null,
1125 ADDRESS3_STD = null,
1126 ADDRESS4_STD = null,
1127 CITY_STD = null,
1131 POSTAL_CODE_STD = null
1128 PROV_STATE_ADMIN_CODE_STD = null,
1129 COUNTY_STD = null,
1130 COUNTRY_STD = null,
1132 WHERE batch_id = p_batch_id;
1133
1134 -- 4. call dqm cleanup routine
1135 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Calling DQM pre-import cleanup');
1136 IF p_run_batch_dedup = 'Y' OR p_run_registry_dedup = 'Y' THEN
1137 HZ_IMP_DQM_STAGE.dqm_pre_imp_cleanup(
1138 p_batch_id => p_batch_id,
1139 x_return_status => l_return_status,
1140 x_msg_count => l_msg_count,
1141 x_msg_data => l_msg_data);
1142
1143 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1144 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** '||FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE));
1145 RETURN;
1146 END IF;
1147
1148 END IF; -- p_run_batch_dedup = 'Y' OR p_run_registry_dedup = 'Y'
1149
1150 END IF; -- r_batch_info.what_if_flag = 'Y'
1151
1152 END IF; -- l_req_data IS NULL
1153
1154 -- calculate number of workers
1155 IF r_batch_info.load_type = 'CSV' THEN
1156 l_num_of_workers := 1;
1157 ELSE
1158 l_num_of_workers := fnd_profile.value('HZ_IMP_NUM_OF_WORKERS');
1159 IF l_num_of_workers IS NULL THEN
1160 l_num_of_workers := 1;
1161 END IF; --l_num_of_workers IS NULL
1162 END IF; --r_batch_info.load_type = 'CSV'
1163
1164 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Number of workers used : '||l_num_of_workers);
1165
1166 -- call the batch deduplication process if needed.
1167 IF NVL(p_run_batch_dedup,'N') = 'Y' AND l_req_data IS NULL THEN
1168 l_str := 'begin HZ_IMP_LOAD_WRAPPER.DATA_LOAD_PREPROCESSING(:1,:2,:3,:4); end;';
1169 execute immediate l_str using p_batch_id, r_batch_info.original_system, l_what_if, OUT l_dataload_rerun;
1170 l_work_unit := 'Y';
1171
1172 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Called data load wrapper to generate work units');
1173
1174 -- submit batch dedup program
1175 l_bd_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1176 'AR',
1177 'ARHDIDIP',
1178 '',
1179 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1180 true,
1181 p_batch_id,
1182 p_batch_dedup_rule_id,
1183 l_num_of_workers
1184 );
1185
1186 IF l_bd_sub_request = 0 THEN
1187 UPDATE hz_imp_batch_summary
1188 SET main_conc_status = 'COMPLETED',
1189 batch_status = 'ACTION_REQUIRED'
1190 WHERE batch_id = p_batch_id;
1191 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting batch de-duplication');
1192 RETURN;
1193 ELSE
1194 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for batch de-dupication');
1195
1196 UPDATE hz_imp_batch_summary
1197 SET batch_dedup_req_id = l_bd_sub_request
1198 WHERE batch_id = p_batch_id;
1199
1200 END IF;
1201
1202 -- wait for the conc program to finish
1203 fnd_conc_global.set_req_globals(
1204 conc_status => 'PAUSED',
1205 request_data => 'BATCH_DEDUP');
1206
1207 ELSE
1208 IF NVL(p_run_batch_dedup,'N') = 'N' AND l_req_data IS NULL THEN
1209 -- just set the l_req_data
1210 fnd_conc_global.set_req_globals(
1211 conc_status => 'NORMAL',
1212 request_data => 'SKIP_BATCH_DEDUP');
1213 l_req_data := fnd_conc_global.request_data;
1214 END IF;
1215
1216 END IF; -- p_run_batch_dedup = 'Y' AND l_req_data IS NULL
1217
1218 -- call the address validation process if needed.
1219
1220 IF (l_req_data = 'BATCH_DEDUP' OR l_req_data = 'SKIP_BATCH_DEDUP')
1221 THEN
1222 -- apply the batch actions if this is a complete import run
1223 -- and batch de-duplication was performed
1224 IF p_import_run_option = 'COMPLETE' AND
1225 p_run_batch_dedup = 'Y'
1226 THEN
1227 HZ_BATCH_ACTION_PUB.batch_dedup_action(
1228 p_batch_id => p_batch_id,
1229 p_action_on_parties => nvl(p_batch_dedup_action, r_batch_info.bd_action_on_parties),
1230 p_action_on_addresses => nvl(p_batch_dedup_action, r_batch_info.bd_action_on_addresses),
1231 p_action_on_contacts => nvl(p_batch_dedup_action, r_batch_info.bd_action_on_contacts),
1232 p_action_on_contact_points => nvl(p_batch_dedup_action, r_batch_info.bd_action_on_contact_points),
1233 x_return_status => l_return_status,
1234 x_msg_data => l_msg_data,
1235 x_msg_count => l_msg_count
1236 );
1237
1238 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1239 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** '||FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE));
1240 RETURN;
1241 END IF;
1242 END IF; /***p_import_run_option = 'COMPLETE' AND p_run_batch_dedup = 'Y' ***/
1243
1244 IF NVL(p_run_addr_val,'N') = 'Y' THEN
1245 -- submit address validation program
1246 l_av_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1247 'AR',
1248 'ARHADDRM',
1249 '',
1250 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1251 true,
1252 p_batch_id
1256 UPDATE hz_imp_batch_summary
1253 );
1254
1255 IF l_av_sub_request = 0 THEN
1257 SET main_conc_status = 'COMPLETED',
1258 batch_status = 'ACTION_REQUIRED'
1259 WHERE batch_id = p_batch_id;
1260 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting address validation');
1261 RETURN;
1262 ELSE
1263 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for addrress validation');
1264
1265 UPDATE hz_imp_batch_summary
1266 SET addr_val_req_id = l_av_sub_request
1267 WHERE batch_id = p_batch_id;
1268 END IF;
1269
1270 -- wait for the con program to finish
1271 fnd_conc_global.set_req_globals(
1272 conc_status => 'PAUSED',
1273 request_data => 'ADDR_VAL');
1274 else
1275 -- skip address validation
1276 fnd_conc_global.set_req_globals(
1277 conc_status => 'NORMAL',
1278 request_data => 'SKIP_ADDR_VAL');
1279 l_req_data := fnd_conc_global.request_data;
1280 end if;
1281
1282 end if;
1283
1284 -- call the data load process
1285 IF l_req_data = 'ADDR_VAL' OR l_req_data = 'SKIP_ADDR_VAL' THEN
1286
1287 IF p_import_run_option = 'COMPLETE' THEN
1288 l_what_if := null;
1289 ELSIF p_import_run_option = 'WHAT_IF' THEN
1290 l_what_if := 'A';
1291 END IF;
1292
1293 -- DQM cleanup for staging reuse
1294 IF r_batch_info.registry_dedup_flag = 'Y' THEN
1295 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Calling DQM intermediate cleanup');
1296 HZ_IMP_DQM_STAGE.dqm_inter_imp_cleanup(
1297 p_batch_id => p_batch_id,
1298 x_return_status => l_return_status,
1299 x_msg_count => l_msg_count,
1300 x_msg_data => l_msg_data);
1301
1302 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1303 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** '||FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE));
1304 RETURN;
1305 END IF;
1306 END IF;
1307
1308 IF l_work_unit <> 'Y' THEN
1309 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Calling data load preprocessing for work unit calculation');
1310 l_str := 'begin HZ_IMP_LOAD_WRAPPER.DATA_LOAD_PREPROCESSING(:1,:2,:3,:4); end;';
1311 execute immediate l_str using p_batch_id, r_batch_info.original_system, l_what_if, OUT l_dataload_rerun;
1312 END IF;
1313
1314 -- submit data load program
1315 IF r_batch_info.load_type = 'CSV' THEN
1316 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Calling ARHLWRPO - Online Data Load');
1317 l_dl_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1318 'AR',
1319 'ARHLWRPO',
1320 '',
1321 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1322 true,
1323 p_batch_id,
1324 r_batch_info.original_system,
1325 l_what_if,
1326 NVL(p_run_registry_dedup,'N'),
1327 nvl(p_registry_dedup_rule_id, r_batch_info.registry_dedup_match_rule_id),
1328 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1329 nvl(r_batch_info.error_limit,FND_PROFILE.value('HZ_IMP_ERROR_LIMIT')),
1330 l_dataload_rerun,
1331 fnd_global.conc_request_id,
1332 fnd_global.prog_appl_id,
1333 fnd_global.conc_program_id
1334 );
1335
1336 ELSE
1337 -- generate number of workers
1338 -- wawong r_batch_info.load_type <> 'CSV' here
1339 /*
1340 IF r_batch_info.load_type = 'CSV' THEN
1341 l_num_of_workers := 1;
1342 ELSE
1343 */
1344 l_num_of_workers := fnd_profile.value('HZ_IMP_NUM_OF_WORKERS');
1345 IF l_num_of_workers IS NULL THEN
1346 l_num_of_workers := 1;
1347 END IF;
1348 --END IF;
1349
1350 FND_FILE.PUT_LINE(FND_FILE.LOG, 'UIC***** Calculated number of workers : '||l_num_of_workers);
1351 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Calling ARHLWRPB - Batch Data Load');
1352
1353 l_dl_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1354 'AR',
1355 'ARHLWRPB',
1356 '',
1357 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1358 true,
1359 p_batch_id,
1360 r_batch_info.original_system,
1361 l_what_if,
1362 NVL(p_run_registry_dedup,'N'),
1363 nvl(p_registry_dedup_rule_id, r_batch_info.registry_dedup_match_rule_id),
1364 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1365 l_num_of_workers,
1366 nvl(r_batch_info.error_limit,FND_PROFILE.value('HZ_IMP_ERROR_LIMIT')),
1367 l_dataload_rerun,
1368 fnd_global.conc_request_id,
1369 fnd_global.prog_appl_id,
1370 fnd_global.conc_program_id
1371 );
1372
1373 END IF;
1374
1375 IF l_dl_sub_request = 0 THEN
1376 UPDATE hz_imp_batch_summary
1377 SET main_conc_status = 'COMPLETED',
1378 batch_status = 'ACTION_REQUIRED'
1379 WHERE batch_id = p_batch_id;
1380 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting data load');
1381 RETURN;
1382 ELSE
1383 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for data load');
1384
1388 x_run_number => l_current_run);
1385 -- update batch summary table and create row in batch detail table
1386 get_current_run(
1387 p_batch_id => p_batch_id,
1389
1390 UPDATE hz_imp_batch_summary
1391 SET import_req_id = l_dl_sub_request
1392 WHERE batch_id = p_batch_id;
1393
1394 END IF;
1395
1396 -- wait for the con program to finish
1397 fnd_conc_global.set_req_globals(
1398 conc_status => 'PAUSED',
1399 request_data => 'DATA_LOAD');
1400
1401 END IF; -- p_run_addr_val = 'Y' AND l_req_data = 'BACTH_DEDUP'
1402
1403 IF l_req_data = 'DATA_LOAD' THEN
1404 -- final steps on import
1405 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Performing the post processes');
1406
1407 IF p_import_run_option = 'COMPLETE' THEN
1408
1409 -- generate number of workers
1410 IF r_batch_info.load_type = 'CSV' THEN
1411 l_num_of_workers := 1;
1412 ELSE
1413 l_num_of_workers := fnd_profile.value('HZ_IMP_NUM_OF_WORKERS');
1414 IF l_num_of_workers IS NULL THEN
1415 l_num_of_workers := 1;
1416 END IF;
1417 END IF;
1418
1419 -- call the DQM cleanup routine
1420 IF r_batch_info.registry_dedup_flag = 'Y' OR r_batch_info.batch_dedup_flag = 'Y' THEN
1421 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Calling DQM post import cleanup');
1422 HZ_IMP_DQM_STAGE.dqm_post_imp_cleanup
1423 (p_batch_id => p_batch_id,
1424 x_return_status => l_return_status,
1425 x_msg_count => l_msg_count,
1426 x_msg_data => l_msg_data);
1427 END IF;
1428
1429 /*
1430 SELECT registry_dedup_flag
1431 INTO l_reg_dedup
1432 FROM hz_imp_batch_summary
1433 WHERE batch_id = p_batch_id;
1434 */
1435 IF NVL(r_batch_info.registry_dedup_flag,'N') = 'Y' THEN
1436
1437 -- call the report dupsets API
1438 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Calling DQM interface tca sanitize report');
1439 HZ_DQM_DUP_ID_PKG.interface_tca_sanitize_report
1440 (p_batch_id => p_batch_id,
1441 p_match_rule_id => p_registry_dedup_rule_id,
1442 p_request_id => fnd_global.conc_request_id,
1443 x_dup_batch_id => l_dup_batch_id,
1444 x_return_status => l_return_status,
1445 x_msg_count => l_msg_count,
1446 x_msg_data => l_msg_data);
1447
1448 get_current_run(
1449 p_batch_id => p_batch_id,
1450 x_run_number => l_current_run);
1451
1452 UPDATE hz_imp_batch_details
1453 SET dup_batch_id = l_dup_batch_id
1454 WHERE batch_id = p_batch_id
1455 AND run_number = l_current_run;
1456
1457 IF p_run_automerge = 'Y' THEN
1458 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Invoking auto merge post process');
1459 l_am_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1460 'AR',
1461 'ARHAMRGP',
1462 '',
1463 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1464 true,
1465 to_char(l_dup_batch_id),
1466 to_char(l_num_of_workers)
1467 );
1468
1469 IF l_am_sub_request = 0 THEN
1470 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting auto merge post process');
1471 ELSE
1472 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for auto merge post process');
1473 END IF;
1474
1475 END IF;
1476 END IF; --NVL(r_batch_info.registry_dedup_flag,'N') = 'Y'
1477
1478 -- Bug 4594407 : Call DQM Sync Index
1479 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Calling Parallel Sync Index concurrent program');
1480 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 );
1481 l_index_conc_program_req_id := FND_REQUEST.SUBMIT_REQUEST('AR',
1482 'ARHDQMPP',
1483 'DQM Parallel Sync Index Parent Program',
1484 NULL,
1485 FALSE,
1486 fnd_global.conc_request_id
1487 );
1488 IF l_index_conc_program_req_id = 0 THEN
1489 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error submitting DQM Sync Index Program.');
1490 ELSE
1491 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Request Id of Parallel Sync concurrent Program is : ' || l_index_conc_program_req_id );
1492 END IF;
1493
1494 -- call dataload post processing
1495 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Invoking data load post process');
1496 FOR i IN 1..l_num_of_workers LOOP
1497
1498 l_pp_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1499 'AR',
1500 'ARHLPPLB',
1501 '',
1502 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1503 true,
1504 p_batch_id,
1505 r_batch_info.original_system,
1506 l_batch_mode_flag,
1510
1507 to_char(fnd_global.conc_request_id),
1508 p_generate_fuzzy_key
1509 );
1511 IF l_pp_sub_request = 0 THEN
1512 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting data load post processing ' || l_num_of_workers);
1513 ELSE
1514 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for data load post processing ' || l_num_of_workers);
1515 END IF;
1516 END LOOP;
1517
1518 -- wait for the conc program to finish
1519 fnd_conc_global.set_req_globals(
1520 conc_status => 'PAUSED',
1521 request_data => 'POST_PROCESS');
1522
1523 ELSIF p_import_run_option = 'WHAT_IF' THEN
1524 final_steps_whatif(
1525 p_batch_id => p_batch_id,
1526 x_return_status => l_return_status,
1527 x_msg_count => l_msg_count,
1528 x_msg_data => l_msg_data
1529 );
1530
1531 fnd_conc_global.set_req_globals(
1532 conc_status => 'NORMAL',
1533 request_data => 'POST_PROCESS');
1534 l_req_data := 'POST_PROCESS';
1535
1536 END IF; -- p_import_run_option = 'COMPLETE'/'WHAT_IF'
1537
1538 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1539 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** '||FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE));
1540 RETURN;
1541 END IF;
1542
1543 END IF; --l_req_data = 'DATA_LOAD'
1544
1545 END IF; -- p_import_run_option = 'WHAT_IF'/'COMPLETE'
1546
1547
1548
1549
1550
1551
1552
1553
1554 -- if it is continuation from what-if, then do the following
1555 -- //////////////////////////////////
1556 -- continue to import from what if
1557 -- //////////////////////////////////
1558
1559 -- if it is continuation from what-if, then do the following
1560 IF p_import_run_option = 'CONTINUE' THEN
1561
1562 IF l_req_data IS NULL THEN
1563
1564 -- set the data load status to pending
1565 UPDATE hz_imp_batch_summary
1566 SET import_status = 'PENDING',
1567 main_conc_status = 'PROCESSING',
1568 main_conc_req_id = fnd_global.conc_request_id
1569 WHERE batch_id = p_batch_id;
1570
1571 -- apply the batch de-duplication actions if needed.
1572 -- this is done becuase during what-if run, batch deduplication actions
1573 -- will not be performed, the options will just be stored. the actions will
1574 -- be performed only when users continue to import.
1575
1576 -- IF nvl(p_run_batch_dedup, r_batch_info.batch_dedup_flag) = 'Y' THEN
1577 IF (r_batch_info.batch_dedup_flag = 'Y') THEN
1578
1579 -- call batch dedup actions api with the r_batch_info.bd_action_on* parameters
1580 IF r_batch_info.import_status = 'ACTION_REQUIRED' THEN
1581
1582 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Calling the batch de-duplication actions API');
1583 HZ_BATCH_ACTION_PUB.batch_dedup_action(
1584 p_batch_id => p_batch_id,
1585 p_action_on_parties => nvl(p_batch_dedup_action, r_batch_info.bd_action_on_parties),
1586 p_action_on_addresses => nvl(p_batch_dedup_action, r_batch_info.bd_action_on_addresses),
1587 p_action_on_contacts => nvl(p_batch_dedup_action, r_batch_info.bd_action_on_contacts),
1591 x_msg_count => l_msg_count
1588 p_action_on_contact_points => nvl(p_batch_dedup_action, r_batch_info.bd_action_on_contact_points),
1589 x_return_status => l_return_status,
1590 x_msg_data => l_msg_data,
1592 );
1593
1594 END IF; -- r_batch_info.import_status = 'ACTION_REQUIRED'
1595
1596 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1597 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** '||FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE));
1598 RETURN;
1599 END IF;
1600
1601 END IF; -- r_batch_info.batch_dedup_flag = 'Y'
1602
1603 -- apply the registry deduplication actions if needed.
1604 -- again the options are recorded in the batch summery table previously.
1605 IF r_batch_info.registry_dedup_flag = 'Y' THEN
1606
1607 -- call registry dedup actions api with the r_batch_info.rd_action_on* parameters
1608 -- call to be added later since it is only needed for UI pages
1609 IF r_batch_info.rd_action_new_parties IS NOT NULL
1610 OR
1611 r_batch_info.rd_action_existing_parties IS NOT NULL
1612 OR
1613 r_batch_info.rd_action_dup_parties IS NOT NULL
1614 OR
1615 r_batch_info.rd_action_pot_dup_parties IS NOT NULL
1616 OR
1617 r_batch_info.rd_action_new_addrs IS NOT NULL
1618 OR
1619 r_batch_info.rd_action_existing_addrs IS NOT NULL
1620 OR
1621 r_batch_info.rd_action_pot_dup_addrs IS NOT NULL
1622 OR
1623 r_batch_info.rd_action_new_contacts IS NOT NULL
1624 OR
1625 r_batch_info.rd_action_existing_contacts IS NOT NULL
1626 OR
1627 r_batch_info.rd_action_pot_dup_contacts IS NOT NULL
1628 OR
1629 r_batch_info.rd_action_new_cpts IS NOT NULL
1630 OR
1631 r_batch_info.rd_action_existing_cpts IS NOT NULL
1632 OR
1633 r_batch_info.rd_action_pot_dup_cpts IS NOT NULL
1634 OR
1635 r_batch_info.rd_action_new_supents IS NOT NULL
1636 OR
1637 r_batch_info.rd_action_existing_supents IS NOT NULL
1638 OR
1639 r_batch_info.rd_action_new_finents IS NOT NULL
1640 OR
1641 r_batch_info.rd_action_existing_finents IS NOT NULL
1642 THEN
1643 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Calling the registry de-duplication actions API');
1644 HZ_BATCH_ACTION_PUB.registry_dedup_action(
1645 p_batch_id => p_batch_id,
1646 p_action_new_parties => r_batch_info.rd_action_new_parties,
1647 p_action_existing_parties => r_batch_info.rd_action_existing_parties,
1648 p_action_dup_parties => r_batch_info.rd_action_dup_parties,
1649 p_action_pot_dup_parties => r_batch_info.rd_action_pot_dup_parties,
1650 p_action_new_addrs => r_batch_info.rd_action_new_addrs,
1651 p_action_existing_addrs => r_batch_info.rd_action_existing_addrs,
1652 p_action_pot_dup_addrs => r_batch_info.rd_action_pot_dup_addrs,
1653 p_action_new_contacts => r_batch_info.rd_action_new_contacts,
1654 p_action_existing_contacts => r_batch_info.rd_action_existing_contacts,
1655 p_action_pot_dup_contacts => r_batch_info.rd_action_pot_dup_contacts,
1656 p_action_new_cpts => r_batch_info.rd_action_new_cpts,
1657 p_action_existing_cpts => r_batch_info.rd_action_existing_cpts,
1658 p_action_pot_dup_cpts => r_batch_info.rd_action_pot_dup_cpts,
1659 p_action_new_supents => r_batch_info.rd_action_new_supents,
1660 p_action_existing_supents => r_batch_info.rd_action_existing_supents,
1661 p_action_new_finents => r_batch_info.rd_action_new_finents,
1662 p_action_existing_finents => r_batch_info.rd_action_existing_finents,
1666 );
1663 x_return_status => l_return_status,
1664 x_msg_count => l_msg_count,
1665 x_msg_data => l_msg_data
1667 END IF;
1668
1669 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1670 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** '||FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE));
1671 RETURN;
1672 END IF;
1673
1674 END IF; -- r_batch_info.registry_dedup_flag = 'Y'
1675
1676 -- set l_what_if to 'R' to inform it is a resume / continue option
1677 l_what_if := 'R';
1678
1679 -- call create work unit api (if not already called) and then call data load
1680 l_str := 'begin HZ_IMP_LOAD_WRAPPER.DATA_LOAD_PREPROCESSING(:1,:2,:3,:4); end;';
1681 execute immediate l_str using p_batch_id, r_batch_info.original_system, l_what_if, OUT l_dataload_rerun;
1682
1683 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Called data load wrapper to generate work units');
1684
1685 IF r_batch_info.load_type = 'CSV' THEN
1686
1687 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Calling ARHLWRPO - Online Data Load');
1688
1689 l_dl_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1690 'AR',
1691 'ARHLWRPO',
1692 '',
1693 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1694 true,
1695 p_batch_id,
1696 r_batch_info.original_system,
1697 l_what_if,
1698 NVL(r_batch_info.registry_dedup_flag,'N'),
1699 r_batch_info.registry_dedup_match_rule_id,
1700 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1701 nvl(r_batch_info.error_limit,FND_PROFILE.value('HZ_IMP_ERROR_LIMIT')),
1702 l_dataload_rerun,
1703 fnd_global.conc_request_id,
1704 fnd_global.prog_appl_id,
1705 fnd_global.conc_program_id
1706 );
1707
1708 ELSE
1709
1710 -- generate number of workers
1711 IF r_batch_info.load_type = 'CSV' THEN
1712 l_num_of_workers := 1;
1713 ELSE
1714 l_num_of_workers := fnd_profile.value('HZ_IMP_NUM_OF_WORKERS');
1715 IF l_num_of_workers IS NULL THEN
1716 l_num_of_workers := 1;
1717 END IF;
1718 END IF;
1719
1720 FND_FILE.PUT_LINE(FND_FILE.LOG, 'UIC***** Calculated number of workers : '||l_num_of_workers);
1721 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Calling ARHLWRPB - Batch Data Load');
1722
1723 l_dl_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1724 'AR',
1725 'ARHLWRPB',
1726 '',
1727 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1728 true,
1729 p_batch_id,
1730 r_batch_info.original_system,
1731 l_what_if,
1732 NVL(r_batch_info.registry_dedup_flag,'N'),
1733 r_batch_info.registry_dedup_match_rule_id,
1734 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1735 l_num_of_workers,
1736 nvl(r_batch_info.error_limit,FND_PROFILE.value('HZ_IMP_ERROR_LIMIT')),
1737 l_dataload_rerun,
1738 fnd_global.conc_request_id,
1739 fnd_global.prog_appl_id,
1740 fnd_global.conc_program_id
1741 );
1742
1743 END IF; -- IF r_batch_info.load_type = 'CSV'
1744
1745 IF l_dl_sub_request = 0 THEN
1746 UPDATE hz_imp_batch_summary
1747 SET main_conc_status = 'COMPLETED',
1748 batch_status = 'ACTION_REQUIRED'
1749 WHERE batch_id = p_batch_id;
1750 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting data load');
1751 RETURN;
1752 ELSE
1753 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for data load');
1754
1755 -- update batch summary table and create row in batch detail table
1756 get_current_run(
1757 p_batch_id => p_batch_id,
1758 x_run_number => l_current_run);
1759
1760 UPDATE hz_imp_batch_summary
1761 SET import_req_id = l_dl_sub_request
1762 WHERE batch_id = p_batch_id;
1763
1764 END IF;
1765
1766 -- wait for the con program to finish
1767 fnd_conc_global.set_req_globals(
1768 conc_status => 'PAUSED',
1769 request_data => 'DATA_LOAD');
1770
1771 END IF; -- l_req_data IS NULL
1772
1773 IF l_req_data = 'DATA_LOAD' THEN
1774 -- final steps on import
1775 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Performing the post processes');
1776
1777 -- generate number of workers
1778 IF r_batch_info.load_type = 'CSV' THEN
1779 l_num_of_workers := 1;
1780 ELSE
1781 l_num_of_workers := fnd_profile.value('HZ_IMP_NUM_OF_WORKERS');
1782 IF l_num_of_workers IS NULL THEN
1783 l_num_of_workers := 1;
1784 END IF;
1785 END IF;
1786
1787 IF r_batch_info.registry_dedup_flag = 'Y' OR r_batch_info.batch_dedup_flag = 'Y' THEN
1788 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Calling DQM post import cleanup');
1789 HZ_IMP_DQM_STAGE.dqm_post_imp_cleanup
1790 (p_batch_id => p_batch_id,
1791 x_return_status => l_return_status,
1792 x_msg_count => l_msg_count,
1793 x_msg_data => l_msg_data);
1794 END IF;
1795
1796 /*
1797 SELECT registry_dedup_flag
1798 INTO l_reg_dedup
1799 FROM hz_imp_batch_summary
1800 WHERE batch_id = p_batch_id;
1801 */
1802 IF NVL(r_batch_info.registry_dedup_flag,'N') = 'Y' THEN
1803
1804 -- call the report dupsets API
1808 p_match_rule_id => r_batch_info.registry_dedup_match_rule_id,
1805 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Calling DQM interface tca sanitize report');
1806 HZ_DQM_DUP_ID_PKG.interface_tca_sanitize_report
1807 (p_batch_id => p_batch_id,
1809 p_request_id => fnd_global.conc_request_id,
1810 x_dup_batch_id => l_dup_batch_id,
1811 x_return_status => l_return_status,
1812 x_msg_count => l_msg_count,
1813 x_msg_data => l_msg_data);
1814
1815 get_current_run(
1816 p_batch_id => p_batch_id,
1817 x_run_number => l_current_run);
1818
1819 UPDATE hz_imp_batch_details
1820 SET dup_batch_id = l_dup_batch_id
1821 WHERE batch_id = p_batch_id
1822 AND run_number = l_current_run;
1823
1824 IF p_run_automerge = 'Y' THEN
1825 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Invoking auto merge post process');
1826 l_am_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1827 'AR',
1828 'ARHAMRGP',
1829 '',
1830 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1831 true,
1832 to_char(l_dup_batch_id),
1833 to_char(l_num_of_workers)
1834 );
1835
1836 IF l_am_sub_request = 0 THEN
1837 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting auto merge post process');
1838 ELSE
1839 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for auto merge post process');
1840 END IF;
1841
1842 END IF;
1843 END IF; --NVL(r_batch_info.r_batch_info.registry_dedup_flag,'N') = 'Y'
1844
1845 -- Bug 4594407 : Call DQM Sync Index
1846 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Calling Parallel Sync Index concurrent program');
1847 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 );
1848 l_index_conc_program_req_id := FND_REQUEST.SUBMIT_REQUEST('AR',
1849 'ARHDQMPP',
1850 'DQM Parallel Sync Index Parent Program',
1851 NULL,
1852 FALSE,
1853 fnd_global.conc_request_id
1854 );
1855 IF l_index_conc_program_req_id = 0 THEN
1856 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error submitting DQM Sync Index Program.');
1857 ELSE
1858 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Request Id of Parallel Sync concurrent Program is : ' || l_index_conc_program_req_id );
1859 END IF;
1860
1861 -- call dataload post processing
1862 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Invoking data load post process');
1863 FOR i IN 1..l_num_of_workers LOOP
1864 l_pp_sub_request := FND_REQUEST.SUBMIT_REQUEST(
1865 'AR',
1866 'ARHLPPLB',
1867 '',
1868 to_char(sysdate,'DD-MM-YY HH24:MI:SS'),
1869 true,
1870 p_batch_id,
1871 r_batch_info.original_system,
1872 l_batch_mode_flag,
1873 to_char(fnd_global.conc_request_id),
1874 p_generate_fuzzy_key
1875 );
1876
1877 IF l_pp_sub_request = 0 THEN
1878 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Error submitting data load post processing ' || l_num_of_workers);
1879 ELSE
1880 fnd_file.put_line(FND_FILE.LOG, 'UIC***** Submitted request for data load post processing ' || l_num_of_workers);
1881 END IF;
1882 END LOOP;
1883
1884 -- wait for the conc program to finish
1885 fnd_conc_global.set_req_globals(
1886 conc_status => 'PAUSED',
1887 request_data => 'POST_PROCESS');
1888
1889 END IF; --l_req_data = 'DATA_LOAD'
1890
1891 END IF; -- p_import_run_option = 'CONTINUE'
1892
1893
1894 IF l_req_data = 'POST_PROCESS' THEN
1895
1896 /* Clean up staging if not a what-if */
1897 IF p_import_run_option <> 'WHAT_IF'
1898 THEN
1899 CLEANUP_STAGING(p_batch_id, l_batch_mode_flag);
1900 END IF;
1901
1902 -- program completed successfully
1903 FND_FILE.PUT_LINE (FND_FILE.LOG, 'UIC***** Program completed successfully');
1904
1905 /* Delete Work Unit if not what-if */
1906 IF p_import_run_option <> 'WHAT_IF'
1907 and (r_batch_info.import_status='COMPLETED'
1908 or r_batch_info.import_status='COMPL_ERRORS')
1909 THEN
1910 delete hz_imp_work_units where batch_id = P_BATCH_ID;
1911 END IF;
1912
1913 -- set the data load status to COMPLETED
1914 UPDATE hz_imp_batch_summary
1915 SET main_conc_status = 'COMPLETED',
1916 batch_status = decode(r_batch_info.import_status,'COMPLETED','COMPLETED','ACTION_REQUIRED')
1917 WHERE batch_id = p_batch_id;
1918
1919
1920 l_last_req := r_batch_info.import_req_id;
1921 l_call_status := fnd_concurrent.get_request_status(
1922 l_last_req,
1923 null,
1924 null,
1925 l_temp_rphase,
1926 l_temp_rstatus,
1927 l_temp_dphase,
1928 l_temp_dstatus,
1929 l_temp_message);
1930
1931 IF l_temp_dstatus <> 'NORMAL' OR r_batch_info.import_status = 'ERROR'
1932 THEN
1933 l_wng_msg := l_wng_msg||' Unexpected error occured in the Data Load program.';
1934 END IF;
1935
1936 IF p_import_run_option <> 'WHAT_IF'
1937 THEN
1941 END IF;
1938 open c_pp_error(p_batch_id);
1939 fetch c_pp_error into l_pp_error;
1940 close c_pp_error;
1942
1943 IF l_pp_error = 'Y'
1944 THEN
1945 l_wng_msg := l_wng_msg||' Unexpected error occured in the Post Processing program.';
1946 END IF;
1947
1948
1949 IF l_wng_msg<>'WARNING****'
1950 THEN
1951 errbuf := l_wng_msg;
1952 retcode := 1;
1953 END IF;
1954
1955
1956
1957 END IF;
1958 EXCEPTION
1959 WHEN OTHERS THEN
1960 Errbuf := fnd_message.get||' '||SQLERRM;
1961 Retcode := 2;
1962
1963 END;
1964
1965 END HZ_BATCH_IMPORT_PKG;