[Home] [Help]
PACKAGE BODY: APPS.HZ_IMP_LOAD_STAGE2
Source
1 PACKAGE BODY HZ_IMP_LOAD_STAGE2 AS
2 /*$Header: ARHLS2WB.pls 120.29 2007/09/25 12:48:46 rarajend ship $*/
3
4 PROCEDURE ERROR_LIMIT_HANDLING(
5 P_BATCH_ID IN NUMBER,
6 P_BATCH_MODE_FLAG IN VARCHAR2
7
8 ) IS
9 BEGIN
10 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR_LIMIT Reached, return to caller wrapper');
11
12 commit;
13
14 -- update batch summary table and detail table
15 -- set statu as complete with error
16
17 update hz_imp_batch_summary
18 set IMPORT_STATUS = 'COMPL_ERROR_LIMIT'
19 where BATCH_ID = P_BATCH_ID;
20
21 UPDATE hz_imp_batch_details
22 SET import_status = 'COMPL_ERROR_LIMIT'
23 WHERE batch_id = P_BATCH_ID
24 AND run_number = (SELECT max(run_number)
25 FROM hz_imp_batch_details
26 WHERE batch_id = P_BATCH_ID);
27
28 /* comment out as this will be done in main wrapper
29
30 HZ_IMP_LOAD_WRAPPER.cleanup_staging(P_BATCH_ID, P_BATCH_MODE_FLAG);
31 */
32
33 commit;
34
35 END ERROR_LIMIT_HANDLING;
36
37 PROCEDURE WHAT_IF_ANALYSIS (
38 P_BATCH_ID IN NUMBER,
39 P_OS IN VARCHAR2
40 ) IS
41 BEGIN
42
43 FND_FILE.PUT_LINE(FND_FILE.LOG,'WHAT_IF_ANALYSIS handling');
44
45 -- update batch summary table and detail table
46 -- set statu as action required
47
48 update hz_imp_batch_summary
49 set IMPORT_STATUS = 'ACTION_REQUIRED'
50 where BATCH_ID = P_BATCH_ID;
51
52 UPDATE hz_imp_batch_details
53 SET import_status = 'ACTION_REQUIRED'
54 WHERE batch_id = P_BATCH_ID
55 AND run_number = (SELECT max(run_number)
56 FROM hz_imp_batch_details
57 WHERE batch_id = P_BATCH_ID);
58
59 -- get summary for each entity, update batch summary table
60 -- columns like 'NEW_UNIQUE_ADDRESSES' and 'EXISTING_ADDRESSES'
61 HZ_IMP_LOAD_BATCH_COUNTS_PKG.what_if_import_counts(P_BATCH_ID, P_OS);
62
63 END WHAT_IF_ANALYSIS;
64
65
66 /**********************************************
67 * public procedure WORKER_PROCESS
68 *
69 * DESCRIPTION
70 * Stage 2 WORKER_PROCESS.
71 *
72 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
73 *
74 * ARGUMENTS
75 * IN:
76 * P_BATCH_ID IN NUMBER(15,0),
77 * P_ACTUAL_CONTENT_SRC IN VARCHAR2(30),
78 * P_RERUN IN VARCHAR2(1),
79 * P_ERROR_LIMIT IN NUMBER,
80 * P_BATCH_MODE_FLAG IN VARCHAR2(1),
81 * P_USER_ID IN NUMBER(15,0),
82 * P_SYSDATE IN DATE,
83 * P_LAST_UPDATE_LOGIN IN NUMBER(15,0),
84 * P_PROGRAM_ID IN NUMBER(15,0),
85 * P_PROGRAM_APPLICATION_ID IN NUMBER(15,0),
86 * P_REQUEST_ID IN NUMBER(15,0),
87 * P_APPLICATION_ID IN NUMBER,
88 * P_GMISS_CHAR IN VARCHAR2(1),
89 * P_GMISS_NUM IN NUMBER,
90 * P_GMISS_DATE IN DATE,
91 * P_FLEX_VALIDATION IN VARCHAR2(1),
92 * P_DSS_SECURITY IN VARCHAR2(1),
93 * P_ALLOW_DISABLED_LOOKUP IN VARCHAR2(1),
94 * P_PROFILE_VERSION IN VARCHAR2(30)
95 * P_WHAT_IF_ANALYSIS IN VARCHAR2,
96 * P_REGISTRY_DEDUP IN VARCHAR2,
97 * P_REGISTRY_DEDUP_MATCH_RULE_ID IN VARCHAR2,
98 * OUT:
99 * X_RETURN_STATUS OUT NOCOPY VARCHAR2,
100 * X_MSG_COUNT OUT NOCOPY NUMBER,
101 * X_MSG_DATA OUT NOCOPY VARCHAR2
102 *
103 * NOTES
104 *
105 * MODIFICATION HISTORY
106 *
107 * 08-10-03 Kate Shan o Created
108 *
109 **********************************************/
110
111 PROCEDURE WORKER_PROCESS(
112 Errbuf OUT NOCOPY VARCHAR2,
113 Retcode OUT NOCOPY VARCHAR2,
114 P_BATCH_ID IN NUMBER,
115 P_ACTUAL_CONTENT_SRC IN VARCHAR2,
116 P_RERUN IN VARCHAR2,
117 P_ERROR_LIMIT IN NUMBER,
118 P_BATCH_MODE_FLAG IN VARCHAR2,
119 P_USER_ID IN NUMBER,
120 --bug 3932987
121 --P_SYSDATE IN DATE,
122 P_SYSDATE IN VARCHAR2,
123 P_LAST_UPDATE_LOGIN IN NUMBER,
124 P_PROGRAM_ID IN NUMBER,
125 P_PROGRAM_APPLICATION_ID IN NUMBER,
126 P_REQUEST_ID IN NUMBER,
127 P_APPLICATION_ID IN NUMBER,
128 P_GMISS_CHAR IN VARCHAR2,
129 P_GMISS_NUM IN NUMBER,
130 P_GMISS_DATE IN DATE,
131 P_FLEX_VALIDATION IN VARCHAR2,
132 P_DSS_SECURITY IN VARCHAR2,
133 P_ALLOW_DISABLED_LOOKUP IN VARCHAR2,
134 P_PROFILE_VERSION IN VARCHAR2,
135 P_WHAT_IF_ANALYSIS IN VARCHAR2,
136 P_REGISTRY_DEDUP IN VARCHAR2,
137 P_REGISTRY_DEDUP_MATCH_RULE_ID IN VARCHAR2
138 ) IS
139
140 l_dml_record HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE;
141 l_hwm_stage NUMBER;
142 -- l_rerun_flag VARCHAR2(1) := 'N';
143 l_start_error_id NUMBER;
144 l_current_error_id NUMBER;
145 l_real_error_count NUMBER;
146 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
147 l_msg_count NUMBER;
148 l_msg_data VARCHAR2(2000);
149 l_what_if_sg_data_exists VARCHAR2(1) ;
150 l_batch_run_before VARCHAR2(1) ;
151 l_os VARCHAR2(30);
152 -- Bug 4594407
153 l_pp_status VARCHAR2(30);
154
155 BEGIN
156 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Stage 2 WORKER_PROCESS (+)');
157
158 -- construct DML_RECORD_TYPE l_dml_record
159 -- value for l_dml_record.RERUN will be decide later.
160
161 l_dml_record.BATCH_ID := P_BATCH_ID;
162 l_dml_record.ACTUAL_CONTENT_SRC := P_ACTUAL_CONTENT_SRC;
163 l_dml_record.ERROR_LIMIT := P_ERROR_LIMIT;
164 l_dml_record.BATCH_MODE_FLAG := P_BATCH_MODE_FLAG;
165 l_dml_record.USER_ID := P_USER_ID;
166 --bug 3932987
167 --l_dml_record.SYSDATE := P_SYSDATE;
168 l_dml_record.SYSDATE := to_date(P_SYSDATE,'DD-MM-YY HH24:MI:SS');
169 l_dml_record.LAST_UPDATE_LOGIN := P_LAST_UPDATE_LOGIN;
170 l_dml_record.PROGRAM_ID := P_PROGRAM_ID;
171 l_dml_record.PROGRAM_APPLICATION_ID := P_PROGRAM_APPLICATION_ID;
172 l_dml_record.REQUEST_ID := P_REQUEST_ID;
173 l_dml_record.APPLICATION_ID := P_APPLICATION_ID;
174 l_dml_record.GMISS_CHAR := P_GMISS_CHAR;
175 l_dml_record.GMISS_NUM := P_GMISS_NUM;
176 l_dml_record.GMISS_DATE := P_GMISS_DATE;
177 l_dml_record.FLEX_VALIDATION := P_FLEX_VALIDATION;
178 l_dml_record.DSS_SECURITY := P_DSS_SECURITY;
179 l_dml_record.ALLOW_DISABLED_LOOKUP := P_ALLOW_DISABLED_LOOKUP;
180 l_dml_record.PROFILE_VERSION := P_PROFILE_VERSION;
181 -- get the start error_id sequence number
182 SELECT hz_imp_errors_s.NEXTVAL INTO l_start_error_id FROM dual;
183
184 -- check if staging table has data
185 l_what_if_sg_data_exists := HZ_IMP_LOAD_WRAPPER.STAGING_DATA_EXISTS(P_BATCH_ID, P_BATCH_MODE_FLAG, 2);
186
187 LOOP
188
189 -- get the start error_id sequence number
190 -- if NO. of errors >= Error Limit, worker should not pick the next WU
191
192 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: check for error limit');
193 SELECT hz_imp_errors_s.CURRVAL INTO l_current_error_id FROM dual;
194
195 -- if estimated error is greater than error limit
196 IF l_current_error_id - l_start_error_id >= l_dml_record.ERROR_LIMIT AND
197 l_dml_record.OS IS NOT NUll THEN
198
199 -- get real number of errors
200 SELECT count(rowid) INTO l_real_error_count
201 FROM HZ_IMP_TMP_ERRORS
202 WHERE BATCH_ID = l_dml_record.BATCH_ID and
203 REQUEST_ID = l_dml_record.REQUEST_ID ;
204
205 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Real error count =' || l_real_error_count);
206
207 IF l_real_error_count >= l_dml_record.ERROR_LIMIT THEN
208
209 /* bug 3401629 fix
210 -- error limit reached , decrease stage in HZ_IMP_WORK_UNITS table
211 UPDATE HZ_IMP_WORK_UNITS
212 SET STATUS = 'C', STAGE = STAGE-1
213 WHERE BATCH_ID = l_dml_record.BATCH_ID
214 AND FROM_ORIG_SYSTEM_REF = l_dml_record.FROM_OSR;
215 */
216
217 Retcode := 2;
218 ERROR_LIMIT_HANDLING(l_dml_record.BATCH_ID, l_dml_record.BATCH_MODE_FLAG);
219 RETURN;
220 END IF;
221 END IF;
222
223
224 -- get the next available worker
225 l_dml_record.OS := NUll;
226 -- Bug 4594407
227 HZ_IMP_LOAD_WRAPPER.RETRIEVE_WORK_UNIT(P_BATCH_ID, '2' , l_dml_record.OS, l_dml_record.FROM_OSR, l_dml_record.TO_OSR,
228 l_hwm_stage, l_pp_status);
229
230 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Retrieved Work unit');
231 FND_FILE.PUT_LINE(FND_FILE.LOG, 'wu_os:' || l_dml_record.OS);
232 FND_FILE.PUT_LINE(FND_FILE.LOG, 'from_osr:' || l_dml_record.FROM_OSR);
233 FND_FILE.PUT_LINE(FND_FILE.LOG, 'to_osr:' || l_dml_record.TO_OSR);
234
235 IF (l_dml_record.OS IS NULL) Then
236 EXIT;
237 ELSE
238 l_os := l_dml_record.OS;
239 END IF;
240
241 -- disable policy function
242 hz_common_pub.disable_cont_source_security;
243
244 -- IF re-run parameter is 'Unexpected Errors'/'Error Limit Reached',
245 -- and current stg < HWM stage, set re-run flag to 'Y'
246 -- IF 'completed with errors' , set re-run flag to 'Y'
247 -- IF re-run parameter is 'new batch'm set re-run flag to 'N'
248 -- IF re-run parameter is 'what-if-resume' THEN
249 -- Look at hz_imp_batch_details table to find out if this batch has been run successfully;
250 -- IF batch run before THEN
251 -- set l_dml_record.RERUN flag to 'Y';
252 -- ELSE
253 -- set l_dml_record.RERUN flag to 'N';
254 -- END IF;
255
256 /* Bug 4594407
257 -- get high worker mark for current worker
258 SELECT HWM_STAGE INTO l_hwm_stage
259 FROM HZ_IMP_WORK_UNITS
260 WHERE BATCH_ID = l_dml_record.BATCH_ID
261 AND FROM_ORIG_SYSTEM_REF = l_dml_record.FROM_OSR;
262 */
263
264 FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: P_RERUN = ' || P_RERUN);
265
266 -- set re-run flag
267 IF P_RERUN = 'E' THEN
268 l_dml_record.RERUN := 'Y';
269 ELSIF (P_RERUN = 'U' OR P_RERUN = 'L') AND l_hwm_stage >= 3 THEN
270 l_dml_record.RERUN := 'Y';
271 ELSIF P_RERUN = 'R' THEN
272 BEGIN
273 select 'Y' into l_batch_run_before
274 from hz_imp_batch_details
275 where batch_id = P_BATCH_ID
276 AND ( import_status = 'COMPL_ERRORS' OR import_status = 'COMPLETED')
277 AND rownum = 1;
278 EXCEPTION
279 WHEN NO_DATA_FOUND THEN
280 l_batch_run_before := 'N';
281 END;
282
283 IF l_batch_run_before = 'Y' THEN
284 l_dml_record.RERUN := 'Y';
285 ELSE
286 l_dml_record.RERUN := 'N';
287 END IF;
288 ELSE
289 l_dml_record.RERUN := 'N';
290 END IF;
291
292 FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: l_dml_record.RERUN = ' || l_dml_record.RERUN);
293
294 -- IF rerun flag is not 'R' or rerun flag is 'R' but no data in staging table
295 -- call matching and dqm
296
297 -- IF P_WHAT_IF_ANALYSIS is null OR (P_WHAT_IF_ANALYSIS is not null AND P_WHAT_IF_ANALYSIS <> 'R') THEN
298 IF upper(P_RERUN) <> 'R' OR
299 ( upper(P_RERUN) = 'R' and l_what_if_sg_data_exists = 'N' ) THEN
300
301
302 -- this event needs to be set for matching (at least)
303 -- it disables index skip scans ..which do not want
304 -- and there is a cbo bug that makes it look cheaper.
305 -- it is session specific, so in the multiple worker case, each worker
306 -- must set this at the begining of matching.
307
308 execute immediate 'alter session set events ''10196 trace name context forever, level 1''';
309
310
311 -- call Matching of Other Entities
312
313 -- matching of relationships
314 HZ_IMP_LOAD_SSM_MATCHING_PKG.MATCH_RELATIONSHIPS(
315 l_dml_record.BATCH_ID,
316 l_dml_record.OS,
317 l_dml_record.FROM_OSR,
318 l_dml_record.TO_OSR,
319 l_dml_record.ACTUAL_CONTENT_SRC,
320 l_dml_record.RERUN,
321 l_dml_record.BATCH_MODE_FLAG
322 );
323 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MATCH_RELATIONSHIPS completed ');
324
325 -- matching of org contacts
326 HZ_IMP_LOAD_SSM_MATCHING_PKG.MATCH_CONTACTS(
327 l_dml_record.BATCH_ID,
328 l_dml_record.OS,
329 l_dml_record.FROM_OSR,
330 l_dml_record.TO_OSR,
331 l_dml_record.ACTUAL_CONTENT_SRC,
332 l_dml_record.RERUN,
333 l_dml_record.BATCH_MODE_FLAG
334 );
335 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MATCH_CONTACTS completed ');
336
337 -- matching of addresses
338 HZ_IMP_LOAD_SSM_MATCHING_PKG.MATCH_ADDRESSES(
339 l_dml_record.BATCH_ID,
340 l_dml_record.OS,
341 l_dml_record.FROM_OSR,
342 l_dml_record.TO_OSR,
343 -- l_dml_record.ACTUAL_CONTENT_SRC,
344 l_dml_record.RERUN,
345 l_dml_record.BATCH_MODE_FLAG
346 );
347 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MATCH_ADDRESSES completed ');
348
349 -- matching of contact points
350 HZ_IMP_LOAD_SSM_MATCHING_PKG.MATCH_CONTACT_POINTS(
351 l_dml_record.BATCH_ID,
352 l_dml_record.OS,
353 l_dml_record.FROM_OSR,
354 l_dml_record.TO_OSR,
355 -- l_dml_record.ACTUAL_CONTENT_SRC,
356 l_dml_record.RERUN,
357 l_dml_record.BATCH_MODE_FLAG
358 );
359 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MATCH_CONTACT_POINTS completed ');
360
361 -- matching of party site use
362 HZ_IMP_LOAD_SSM_MATCHING_PKG.MATCH_ADDRUSES(
363 l_dml_record.BATCH_ID,
364 l_dml_record.OS,
365 l_dml_record.FROM_OSR,
366 l_dml_record.TO_OSR,
367 l_dml_record.ACTUAL_CONTENT_SRC,
368 l_dml_record.RERUN,
369 l_dml_record.BATCH_MODE_FLAG
370 );
371 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MATCH_ADDRUSES completed ');
372
373 -- matching of contact role
374 HZ_IMP_LOAD_SSM_MATCHING_PKG.MATCH_CONTACTROLES(
375 l_dml_record.BATCH_ID,
376 l_dml_record.OS,
377 l_dml_record.FROM_OSR,
378 l_dml_record.TO_OSR,
379 l_dml_record.ACTUAL_CONTENT_SRC,
380 l_dml_record.RERUN,
381 l_dml_record.BATCH_MODE_FLAG
382 );
383 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MATCH_CONTACTROLES completed ');
384
385 -- matching of financial reports
386 HZ_IMP_LOAD_SSM_MATCHING_PKG.MATCH_FINANCIAL_REPORTS(
387 l_dml_record.BATCH_ID,
388 l_dml_record.OS,
389 l_dml_record.FROM_OSR,
390 l_dml_record.TO_OSR,
391 l_dml_record.ACTUAL_CONTENT_SRC,
392 l_dml_record.RERUN,
393 l_dml_record.BATCH_MODE_FLAG
394 );
395 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MATCH_FINANCIAL_REPORTS completed ');
396
397 -- matching of financial numbers
398 HZ_IMP_LOAD_SSM_MATCHING_PKG.MATCH_FINANCIAL_NUMBERS(
399 l_dml_record.BATCH_ID,
400 l_dml_record.OS,
401 l_dml_record.FROM_OSR,
402 l_dml_record.TO_OSR,
403 l_dml_record.ACTUAL_CONTENT_SRC,
404 l_dml_record.RERUN,
405 l_dml_record.BATCH_MODE_FLAG
406 );
407 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MATCH_FINANCIAL_NUMBERS completed ');
408
409 -- matching of credit ratings
410 HZ_IMP_LOAD_SSM_MATCHING_PKG.MATCH_CREDIT_RATINGS(
411 l_dml_record.BATCH_ID,
412 l_dml_record.OS,
413 l_dml_record.FROM_OSR,
414 l_dml_record.TO_OSR,
415 l_dml_record.SYSDATE,
416 l_dml_record.ACTUAL_CONTENT_SRC,
417 l_dml_record.RERUN,
418 l_dml_record.BATCH_MODE_FLAG
419 );
420 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MATCH_CREDIT_RATINGS completed ');
421
422 -- matching of code assignments
423 HZ_IMP_LOAD_SSM_MATCHING_PKG.MATCH_CODE_ASSIGNMENTS(
424 l_dml_record.BATCH_ID,
425 l_dml_record.OS,
426 l_dml_record.FROM_OSR,
427 l_dml_record.TO_OSR,
428 l_dml_record.ACTUAL_CONTENT_SRC,
429 l_dml_record.RERUN,
430 l_dml_record.BATCH_MODE_FLAG
431 );
432 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MATCH_CODE_ASSIGNMENTS completed ');
433
434 IF P_REGISTRY_DEDUP = 'Y' THEN
435
436 -- fetch record for DQM
437
438 -- IF re-run parameter is or 'Error Limit Reached',
439 -- or 'completed with errors' , or 'What-If Resume'
440 -- or 'Unexpected Errors' with current stg < HWM
441 -- set table with new Ids Generated by matching,
442 -- If re-run parameter is 'new batch', call DQM
443
444 IF P_RERUN = 'L' OR
445 P_RERUN = 'E' OR
446 (P_RERUN = 'R' and l_what_if_sg_data_exists = 'Y') OR
447 (P_RERUN = 'U' AND l_hwm_stage >= 3)
448 THEN
449
450 FND_FILE.PUT_LINE(FND_FILE.LOG, 'update dup party id ');
451
452 -- update dup party id
453 UPDATE HZ_IMP_DUP_PARTIES idp
454 SET PARTY_ID =
455 ( SELECT PARTY_ID FROM HZ_IMP_PARTIES_SG ips
456 WHERE ips.PARTY_ORIG_SYSTEM = idp.PARTY_OS
457 and ips.PARTY_ORIG_SYSTEM_REFERENCE = idp.PARTY_OSR
458 and ips.BATCH_ID = P_BATCH_ID)
459 WHERE idp.BATCH_ID = P_BATCH_ID
460 AND idp.PARTY_OS = l_dml_record.OS
461 AND idp.PARTY_OSR BETWEEN l_dml_record.FROM_OSR AND l_dml_record.TO_OSR
462 AND idp.PARTY_OSR IN (SELECT PARTY_ORIG_SYSTEM_REFERENCE
463 FROM HZ_IMP_PARTIES_SG
464 WHERE BATCH_ID = P_BATCH_ID
465 AND PARTY_ORIG_SYSTEM = idp.PARTY_OS
466 AND PARTY_ORIG_SYSTEM_REFERENCE BETWEEN l_dml_record.FROM_OSR AND l_dml_record.TO_OSR);
467
468 -- update dup party site
469 UPDATE HZ_IMP_DUP_DETAILS idd
470 SET (PARTY_ID, RECORD_ID)=
471 ( SELECT PARTY_ID, PARTY_SITE_ID FROM HZ_IMP_ADDRESSES_SG ias
472 WHERE ias.PARTY_ORIG_SYSTEM = idd.PARTY_OS
473 and ias.PARTY_ORIG_SYSTEM_REFERENCE = idd.PARTY_OSR
474 and ias.SITE_ORIG_SYSTEM = idd.RECORD_OS
475 and ias.SITE_ORIG_SYSTEM_REFERENCE = idd.RECORD_OSR
476 and ias.BATCH_ID = P_BATCH_ID)
477 WHERE ENTITY = 'PARTY_SITES'
478 and idd.BATCH_ID = P_BATCH_ID
479 AND idd.PARTY_OS = l_dml_record.OS
480 AND idd.PARTY_OSR BETWEEN l_dml_record.FROM_OSR AND l_dml_record.TO_OSR
481 AND idd.PARTY_OSR IN (SELECT PARTY_ORIG_SYSTEM_REFERENCE
482 FROM HZ_IMP_ADDRESSES_SG
483 WHERE BATCH_ID = P_BATCH_ID
484 AND PARTY_ORIG_SYSTEM = idd.PARTY_OS
485 AND PARTY_ORIG_SYSTEM_REFERENCE BETWEEN l_dml_record.FROM_OSR AND l_dml_record.TO_OSR);
486
487
488 -- update dup contact
489
490 UPDATE HZ_IMP_DUP_DETAILS idd
491 SET (PARTY_ID, RECORD_ID)=
492 ( SELECT PARTY_ID, CONTACT_ID FROM HZ_IMP_CONTACTS_SG ics
493 WHERE ics.CONTACT_ORIG_SYSTEM = idd.RECORD_OS
494 and ics.CONTACT_ORIG_SYSTEM_REFERENCE = idd.RECORD_OSR
495 and ics.BATCH_ID = P_BATCH_ID)
496 WHERE ENTITY = 'CONTACTS'
497 and idd.BATCH_ID = P_BATCH_ID
498 AND idd.PARTY_OS = l_dml_record.OS
499 AND idd.PARTY_OSR BETWEEN l_dml_record.FROM_OSR AND l_dml_record.TO_OSR
500 AND idd.RECORD_OSR IN (SELECT CONTACT_ORIG_SYSTEM_REFERENCE
501 FROM HZ_IMP_CONTACTS_SG
502 WHERE BATCH_ID = P_BATCH_ID
503 AND CONTACT_ORIG_SYSTEM = idd.RECORD_OS
504 AND SUB_ORIG_SYSTEM_REFERENCE BETWEEN l_dml_record.FROM_OSR AND l_dml_record.TO_OSR);
505
506
507 -- update dup contact point
508
509 UPDATE HZ_IMP_DUP_DETAILS idd
510 SET (PARTY_ID, RECORD_ID)=
511 ( SELECT PARTY_ID, CONTACT_POINT_ID FROM HZ_IMP_CONTACTPTS_SG ics
512 WHERE ics.PARTY_ORIG_SYSTEM = idd.PARTY_OS
513 and ics.PARTY_ORIG_SYSTEM_REFERENCE = idd.PARTY_OSR
514 and ics.BATCH_ID = P_BATCH_ID)
515 WHERE ENTITY = 'CONTACT_POINTS'
516 and idd.BATCH_ID = P_BATCH_ID
517 AND idd.PARTY_OS = l_dml_record.OS
518 AND idd.PARTY_OSR BETWEEN l_dml_record.FROM_OSR AND l_dml_record.TO_OSR
519 AND idd.PARTY_OSR IN (SELECT PARTY_ORIG_SYSTEM_REFERENCE
520 FROM HZ_IMP_CONTACTPTS_SG
521 WHERE BATCH_ID = P_BATCH_ID
522 AND PARTY_ORIG_SYSTEM = idd.PARTY_OS
523 AND PARTY_ORIG_SYSTEM_REFERENCE BETWEEN l_dml_record.FROM_OSR AND l_dml_record.TO_OSR);
524
525 ELSE
526
527 FND_FILE.PUT_LINE(FND_FILE.LOG, 'calling DQM ');
528 HZ_DQM_DUP_ID_PKG.interface_tca_dup_id (
529 p_batch_id => l_dml_record.BATCH_ID,
530 p_match_rule_id => P_REGISTRY_DEDUP_MATCH_RULE_ID,
531 p_from_osr => l_dml_record.FROM_OSR,
532 p_to_osr => l_dml_record.TO_OSR,
533 p_batch_mode_flag => l_dml_record.BATCH_MODE_FLAG,
534 -- p_init_msg_list => 'F',
535 x_return_status => l_return_status,
536 x_msg_count => l_msg_count,
537 x_msg_data => l_msg_data
538 );
539
540 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
541 FND_FILE.PUT_LINE(FND_FILE.LOG, 'DQM return status ERROR');
542 FND_FILE.PUT_LINE(FND_FILE.LOG, SubStr('l_msg_data = '||l_msg_data,1,255));
543 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
544 FND_FILE.PUT_LINE(FND_FILE.LOG, 'message[' ||I||']=');
545 FND_FILE.PUT_LINE(FND_FILE.LOG, Substr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1,255));
546 END LOOP;
547 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
548 END IF;
549 FND_FILE.PUT_LINE(FND_FILE.LOG, 'DQM complete successfully ');
550
551 END IF;
552
553 END IF; -- end of IF P_REGISTRY_DEDUP = 'Y'
554
555 END IF; -- IF P_RERUN <> 'R'
556
557 -- Call V+DML Of Parties when
558 -- WHAT-IF ANALYSIS parameter <> 'ANALYSIS'
559 IF P_WHAT_IF_ANALYSIS is null OR (P_WHAT_IF_ANALYSIS is not null AND P_WHAT_IF_ANALYSIS <> 'A') THEN
560
561 FND_FILE.PUT_LINE(FND_FILE.LOG, 'LOAD_PARTIES ... ');
562
563 HZ_IMP_LOAD_PARTIES_PKG.LOAD_PARTIES (
564 P_DML_RECORD => l_dml_record,
565 X_RETURN_STATUS => l_return_status,
566 X_MSG_COUNT => l_msg_count,
567 X_MSG_DATA => l_msg_data
568 );
569
570 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
571 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
572 END IF;
573
574 FND_FILE.PUT_LINE(FND_FILE.LOG, 'LOAD_PARTIES successfully ');
575
576 -- Bug 4594407
577 -- Populate staging table for unprocessed post-processing records from previous run
578 -- matching of parties
579 -- Bug 4925023 : handle cases when records passed stage 3 at previous run.
580 -- Change from l_hwm_stage = 2 to l_hwm_stage >= 2
581 IF l_hwm_stage >= 2 AND l_pp_status = 'U' THEN
582 HZ_IMP_LOAD_SSM_MATCHING_PKG.MATCH_PARTIES(
583 l_dml_record.BATCH_ID,
584 l_dml_record.OS,
585 l_dml_record.FROM_OSR,
586 l_dml_record.TO_OSR,
587 l_dml_record.ACTUAL_CONTENT_SRC,
588 'N',
589 l_dml_record.BATCH_MODE_FLAG
590 );
591 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MATCH_PARTIES completed ');
592 END IF;
593
594 END IF;
595
596 /* Update status to Complete for the work unit that just finished */
597 UPDATE HZ_IMP_WORK_UNITS
598 SET STATUS = 'C'
599 WHERE BATCH_ID = l_dml_record.BATCH_ID
600 AND FROM_ORIG_SYSTEM_REF = l_dml_record.FROM_OSR;
601
602 COMMIT;
603
604 END LOOP;
605
606 -- What-If parameter = 'ANALYSIS'
607 IF P_WHAT_IF_ANALYSIS = 'A' THEN
608 FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: ANALYSIS ');
609 WHAT_IF_ANALYSIS(l_dml_record.BATCH_ID, l_os);
610 END IF;
611
612 /* code is moved to the wrapper
613 IF P_BATCH_MODE_FLAG = 'Y' THEN
614 -- Analyze staging table after matching
615 fnd_stats.gather_table_stats('AR', 'HZ_IMP_ADDRESSES_SG', percent=>5, degree=>4);
616 fnd_stats.gather_table_stats('AR', 'HZ_IMP_ADDRESSUSES_SG', percent=>5, degree=>4);
617 fnd_stats.gather_table_stats('AR', 'HZ_IMP_CLASSIFICS_SG', percent=>5, degree=>4);
618 fnd_stats.gather_table_stats('AR', 'HZ_IMP_CONTACTPTS_SG', percent=>5, degree=>4);
619 fnd_stats.gather_table_stats('AR', 'HZ_IMP_CONTACTROLES_SG', percent=>5, degree=>4);
620 fnd_stats.gather_table_stats('AR', 'HZ_IMP_CONTACTS_SG', percent=>5, degree=>4);
621 fnd_stats.gather_table_stats('AR', 'HZ_IMP_CREDITRTNGS_SG', percent=>5, degree=>4);
622 fnd_stats.gather_table_stats('AR', 'HZ_IMP_FINNUMBERS_SG', percent=>5, degree=>4);
623 fnd_stats.gather_table_stats('AR', 'HZ_IMP_FINREPORTS_SG', percent=>5, degree=>4);
624 fnd_stats.gather_table_stats('AR', 'HZ_IMP_RELSHIPS_SG', percent=>5, degree=>4);
625 END IF;
626 */
627
628 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Stage 2 WORKER_PROCESS (-)');
629
630 RETURN;
631
632 EXCEPTION
633 WHEN OTHERS THEN
634 ROLLBACK;
635 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
636 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
637
638 errbuf := FND_MESSAGE.get;
639 retcode := 2;
640
641 UPDATE hz_imp_batch_summary
642 SET import_status = 'ERROR'
643 WHERE batch_id = P_BATCH_ID;
644
645 UPDATE hz_imp_batch_details
646 SET import_status = 'ERROR'
647 WHERE batch_id = P_BATCH_ID
648 AND run_number = (SELECT max(run_number)
649 FROM hz_imp_batch_details
650 WHERE batch_id = P_BATCH_ID);
651
652 COMMIT;
653
654 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Stage 2 worker: SQLERRM: ' || SQLERRM);
655 FND_FILE.PUT_LINE(FND_FILE.LOG, SubStr('l_msg_data = '||l_msg_data,1,255));
656 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
657 FND_FILE.PUT_LINE(FND_FILE.LOG, 'message[' ||I||']=');
658 FND_FILE.PUT_LINE(FND_FILE.LOG, Substr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1,255));
659 END LOOP;
660
661
662 END WORKER_PROCESS;
663
664
665 END HZ_IMP_LOAD_STAGE2;