[Home] [Help]
PACKAGE BODY: APPS.HZ_AUTOMERGE_PKG
Source
1 PACKAGE BODY HZ_AUTOMERGE_PKG AS
2 /*$Header: ARHAMRGB.pls 120.18.12000000.3 2007/05/25 10:09:41 vsegu ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'HZ_AUTOMERGE_PKG' ;
6
7 /**
8 * Procedure to write a message to the out file
9 **/
10 PROCEDURE out(
11 message IN VARCHAR2,
12 newline IN BOOLEAN DEFAULT TRUE) IS
13 BEGIN
14 IF message = 'NEWLINE' THEN
15 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
16 ELSIF (newline) THEN
17 FND_FILE.put_line(fnd_file.output,message);
18 ELSE
19 FND_FILE.put(fnd_file.output,message);
20 END IF;
21 END out;
22
23 /**
24 * Procedure to write a message to the log file
25 **/
26 PROCEDURE log(
27 message IN VARCHAR2,
28 newline IN BOOLEAN DEFAULT TRUE
29 ) IS
30 BEGIN
31 IF message = 'NEWLINE' THEN
32 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
33 ELSIF (newline) THEN
34 FND_FILE.put_line(fnd_file.log,message);
35 ELSE
36 FND_FILE.put(fnd_file.log,message);
37 END IF;
38 END log;
39
40
41 -----------------------------------------------------------------------
42 -- Function to fetch messages of the stack and log the error
43 -----------------------------------------------------------------------
44 PROCEDURE logerror(SQLERRM VARCHAR2 DEFAULT NULL)
45 IS
46 l_msg_data VARCHAR2(2000);
47 BEGIN
48 FND_MSG_PUB.Reset;
49 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
50 l_msg_data := substr(l_msg_data || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 2000) ;
51 END LOOP;
52 IF (SQLERRM IS NOT NULL) THEN
53 l_msg_data := substr(l_msg_data || SQLERRM, 1, 2000);
54 END IF;
55 log(l_msg_data);
56 END;
57
58
59 /**
60 * Procedure to write a message to the out and log files
61 **/
62 PROCEDURE outandlog(
63 message IN VARCHAR2,
64 newline IN BOOLEAN DEFAULT TRUE) IS
65 BEGIN
66 out(message, newline);
67 log(message, newline);
68 END outandlog;
69
70 FUNCTION chk_for_rem_batch( P_DUP_SET_ID IN NUMBER
71 ) RETURN NUMBER
72 IS
73 l_count NUMBER;
74 l_procedure_name VARCHAR2(255) := 'CHK_FOR_REM_BATCH';
75 BEGIN
76 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
77 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
78 END IF;
79 select count(*) into l_count
80 from hz_dup_set_parties
81 where dup_set_id = P_DUP_SET_ID
82 and merge_flag = 'N' ;
83 RETURN l_count;
84 END chk_for_rem_batch;
85
86 FUNCTION chk_count( P_DUP_SET_ID IN Number,
87 p_winner_party_id IN Number
88 ) RETURN NUMBER
89 IS
90 l_count NUMBER;
91 l_procedure_name VARCHAR2(255) := 'CHK_COUNT';
92 BEGIN
93 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
94 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
95 END IF;
96 select count(*) into l_count
97 from hz_dup_set_parties
98 where dup_set_id = P_DUP_SET_ID
99 and dup_party_id <> p_winner_party_id;
100 RETURN l_count;
101 END chk_count;
102
103 -- Creates remainder batch for viewing and manual submission by data librarian.
104 -- Remainder batch is created during succsfful invocatoin of create_merge_batch
105 -- api or if expected error occurs. If an expected error occurs create remainder
106 -- batch with all parties irrespective of merge flag else create remainder batch
107 -- for all parties which are not being merged by automerge (merge_flag = 'N')
108 PROCEDURE create_rem_batch( p_dup_batch_id IN NUMBER,
109 P_DUP_SET_ID IN Number,
110 P_WINNER_PARTY_ID IN Number,
111 p_new_dup_batch_id IN NUMBER,
112 p_state IN VARCHAR2
113 ) IS
114 TYPE NumberList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
115 l_dup_party_id NumberList;
116 l_score NumberList;
117 l_match_rule_id NUMBER;
118 l_req_type VARCHAR2(30);
119 l_w_score NUMBER;
120 l_new_dup_set_id NUMBER;
121 l_procedure_name VARCHAR2(255) := 'CREATE_REM_BATCH';
122 l_merge_type VARCHAR2(30);
123
124 CURSOR c1(l_dup_set_id NUMBER, l_winner_party_id NUMBER) is
125 select score
126 from hz_dup_set_parties
127 where dup_set_id = l_dup_set_id
128 and dup_party_id = l_winner_party_id;
129
130 CURSOR c2(l_dup_set_id NUMBER, l_winner_party_id NUMBER, p_state VARCHAR2) is
131 select dup_party_id, score
132 from hz_dup_set_parties
133 where dup_set_id = l_dup_set_id
134 and decode(p_state, 'S', merge_flag, 'E', 'N') = 'N'
135 and dup_party_id <> l_winner_party_id;
136
137 BEGIN
138 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
139 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
140 END IF;
141 SAVEPOINT create_rem_batch;
142 select hz_merge_batch_s.nextval into l_new_dup_set_id from dual;
143 log (' Creating remainder batch....');
144 log (' l_new_dup_set_id = ' || l_new_dup_set_id);
145 -- log(' l_winner_party_id = ' || p_winner_party_id);
146 -- log (' s_dup_batch_id = ' || p_new_dup_batch_id);
147 -- log (' p_state = '|| p_state);
148 -- insert row for the dup set
149 insert into hz_dup_sets(winner_party_id, dup_batch_id, dup_set_id, status, merge_type,
150 object_version_number, created_by, creation_date, last_update_login, last_update_date, last_updated_by)
151 values( p_winner_party_id, p_new_dup_batch_id, l_new_dup_set_id, 'SYSBATCH', 'PARTY_MERGE',
152 1,
153 HZ_UTILITY_V2PUB.CREATED_BY,
154 HZ_UTILITY_V2PUB.CREATION_DATE, HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
155 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
156 HZ_UTILITY_V2PUB.LAST_UPDATED_BY);
157 OPEN c1(p_DUP_SET_ID, p_winner_party_id);
158 FETCH c1 into l_w_score;
159 -- insert row for the dup set and winner party id.
160 insert into hz_dup_set_parties(dup_party_id, dup_set_id, merge_flag, score, created_by,
161 creation_date, last_update_login, last_update_date, last_updated_by,dup_set_batch_id) --Bug No: 4244529
162 values (p_winner_party_id, l_new_dup_set_id, 'N', l_w_score, HZ_UTILITY_V2PUB.CREATED_BY,
163 HZ_UTILITY_V2PUB.CREATION_DATE, HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
164 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE, HZ_UTILITY_V2PUB.LAST_UPDATED_BY,p_new_dup_batch_id) ; --Bug No: 4244529
165 CLOSE c1;
166 OPEN c2(p_DUP_SET_ID, p_winner_party_id, p_state);
167 FETCH c2 bulk collect into l_dup_party_id, l_score;
168 log (' l_dup_party_id.count = ' || l_dup_party_id.count);
169 FOR I in l_dup_party_id.FIRST..l_dup_party_id.LAST
170 LOOP
171 -- log('l_dup_party_id('||I||') = '|| l_dup_party_id(I));
172 -- log (' l_score('||I||') = ' || l_score(I));
173
174 -- insert row for a particualr dup set and all its parties which are not being
175 -- merged by automerge.
176 insert into hz_dup_set_parties(dup_party_id, dup_set_id, merge_flag, score, created_by,
177 creation_date, last_update_login, last_update_date, last_updated_by,dup_set_batch_id) --Bug No: 4244529
178 values (l_dup_party_id(I), l_new_dup_set_id, 'N', l_score(I), HZ_UTILITY_V2PUB.CREATED_BY,
179 HZ_UTILITY_V2PUB.CREATION_DATE, HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
180 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE, HZ_UTILITY_V2PUB.LAST_UPDATED_BY,p_new_dup_batch_id) ; --Bug No: 4244529
181 END LOOP;
182 CLOSE c2;
183 log (' Batch for review created with dup_batch_id = ' ||p_new_dup_batch_id );
184 EXCEPTION WHEN OTHERS THEN
185 ROLLBACK TO create_rem_batch;
186 log(SQLERRM);
187 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
188 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
189 FND_MSG_PUB.ADD;
190 RAISE;
191 END create_rem_batch;
192
193 -- bug 4773387, non automerge dup set will be created in rem batch. Delete it from orig. batch
194 procedure delete_non_am_dup_set(p_dup_set_id number, p_all_cnt number, p_non_am_cnt number) is
195
196 begin
197 if p_all_cnt = p_non_am_cnt -- no automerge candidates in the set
198 then
199 DELETE FROM HZ_DUP_SETS
200 where DUP_SET_ID = p_DUP_SET_ID;
201
202 delete from hz_dup_set_parties
203 where dup_set_id = p_dup_set_id;
204 end if;
205
206 end;
207
208
209 -- Program merges the parties which have higher score than threshold (set during
210 -- match rule definition) and identified as duplicates.
211 PROCEDURE automerge( retcode OUT NOCOPY VARCHAR2,
212 err OUT NOCOPY VARCHAR2,
213 p_dup_batch_id IN VARCHAR2,
214 p_no_of_workers IN VARCHAR2)
215 IS
216 TYPE NumberList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
217 A_DUP_SET_ID NumberList;
218 l_winner_party_id NumberList;
219
220 l_object_version_number NUMBER;
221 x_return_status VARCHAR2(1);
222 x_msg_count NUMBER;
223 x_msg_data VARCHAR2(2000);
224 x_request_id NUMBER;
225 c1 HZ_PARTY_STAGE.StageCurTyp;
226 l_sql_stmt1 VARCHAR2(4000);
227 l_prof_value VARCHAR2(30);
228 l_sub_requests NumberList;
229 A_MERGE_BATCH_ID NumberList;
230 l_no_of_workers NUMBER;
231 l_dup_batch_id NUMBER;
232 J NUMBER;
233 req_data VARCHAR2(30);
234 l_success BOOLEAN := true;
235 l_new_dup_batch_id NUMBER;
236 i NUMBER;
237 l_batch_name VARCHAR2(255);
238 l_match_rule_id NUMBER;
239 l_req_type VARCHAR2(30);
240 l_request_type VARCHAR2(30);
241 l_count NUMBER;
242 l_all_count NUMBER;
243 l_procedure_name VARCHAR2(255) := 'AUTOMERGE';
244 is_first boolean := true;
245 l_temp NUMBER;
246 l_automerge_flag VARCHAR2(1) := 'N';
247 l_party_name varchar2(360);
248 l_exist varchar2(1);
249 l_default_mapping varchar2(1);
250 l_master_candidate_cnt number;
251 cursor sysbatch_exist is
252 select 'Y'
253 from hz_dup_sets
254 where dup_batch_id = p_dup_batch_id
255 and status = 'SYSBATCH'
256 and rownum = 1;
257
258 cursor get_obj_version_csr(cp_dup_set_id number) is
259 SELECT object_version_number
260 FROM hz_dup_sets
264 select count(*)
261 WHERE dup_set_id = cp_dup_set_id;
262
263 cursor get_active_party_count_csr(cp_dup_set_id number) is
265 from hz_parties p, hz_dup_set_parties mp
266 where p.party_id = mp.dup_party_id
267 and mp.dup_set_id = cp_dup_set_id
268 and p.status = 'A'
269 and nvl(mp.merge_flag,'Y') = 'Y';
270
271 BEGIN
272 SAVEPOINT automerge;
273 -- Diagnositics
274 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
275 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
276 END IF;
277 -- log(' p_dup_batch_id = ' || p_dup_batch_id);
278 select a.dup_batch_name, a.match_rule_id,
279 decode(a.request_type, 'SYSTEM_GENERATED', 'SYSTEM_AUTOMERGE', 'IMPORT', 'IMPORT_AUTOMERGE'), a.request_type, b.automerge_flag
280 into l_batch_name, l_match_rule_id, l_req_type, l_request_type, l_automerge_flag
281 from hz_dup_batch a, hz_match_rules_b b
282 where a.dup_batch_id = p_dup_batch_id
283 and a.match_rule_id = b.match_rule_id;
284 -- log(' l_automerge_flag = ' || l_automerge_flag);
285 IF (upper(l_automerge_flag) <> 'Y') THEN
286 fnd_conc_global.set_req_globals(conc_status => 'ERROR',
287 request_data => 'ERROR') ;
288 FND_MESSAGE.SET_NAME('AR', 'HZ_AM_MATCH_RULE_SUPPORT');
289 FND_MSG_PUB.ADD;
290 logerror;
291 err := fnd_message.get;
292 retcode := 2;
293 ELSE
294
295 req_data := fnd_conc_global.request_data;
296 log(' ');
297 -- log(' req_data = ' || req_data);
298 -- l_no_of_workers := nvl(to_number(p_no_of_workers),1);
299 l_no_of_workers := nvl(fnd_profile.value('HZ_DQM_PM_NUM_OF_WORKERS'), 1);
300 l_dup_batch_id := to_number(p_dup_batch_id);
301
302
303 IF (req_data IS NULL OR req_data = 'SYSTEM_PHASE1') THEN
304 log (' l_batch_name = ' || l_batch_name);
305 --log (' l_req_type = ' || l_req_type);
306 --log(' l_no_of_workers ' || l_no_of_workers);
307 log(' l_dup_batch_id ' || l_dup_batch_id);
308 x_return_status := FND_API.G_RET_STS_SUCCESS;
309 fnd_msg_pub.initialize;
310
311 -- get all dup_set_id's for a particular batch. this conditions also helps in re-running
312 -- the program again for same batch.
313
314 open sysbatch_exist;
315 fetch sysbatch_exist into l_exist;
316 close sysbatch_exist;
317 -- log ('sysbatch exist '|| nvl(l_exist,'N'));
318 if nvl(l_exist,'N') = 'Y'
319 then
320 OPEN c1 FOR SELECT dup_set_id, winner_party_id
321 FROM hz_dup_sets
322 WHERE dup_batch_id = p_dup_batch_id
323 and status = 'SYSBATCH';
324 FETCH c1 BULK COLLECT INTO A_DUP_SET_ID, l_winner_party_id ;
325
326 IF (c1%ROWCOUNT > 0) THEN
327 /* Using the new master default. Call default_master
328 -- based on this profile, winner party is updated if the request type for a batch is 'SYSTEM_GENERATED'
332 IF l_prof_value = 'KEEP_EARLIEST_CREATED' THEN
329 l_prof_value := nvl(fnd_profile.value('HZ_AUTOMERGE_WINNER_SELECT_RULE'), 'KEEP_EARLIEST_CREATED'); -- creae new profile and change code accordingly
330 log (' l_prof_value' || l_prof_value);
331 log (' ');
333 l_sql_stmt1 := 'select dup_party_id
334 from hz_dup_set_parties a, hz_parties b
335 where a.dup_set_id = :1
336 and a.dup_party_id = b.party_id
337 and nvl(merge_flag, ''Y'') = ''Y''
338 and rownum = 1
339 order by b.creation_date asc ' ;
340 ELSIF l_prof_value = 'KEEP_LATEST_CREATED' THEN
341 l_sql_stmt1 := 'select dup_party_id
342 from hz_dup_set_parties a, hz_parties b
343 where a.dup_set_id = :1
347 order by b.creation_date desc ' ;
344 and a.dup_party_id = b.party_id
345 and nvl(merge_flag, ''Y'') = ''Y''
346 and rownum = 1
348 END IF; */
349
350 if nvl(fnd_profile.value('HZ_AM_SUGG_GROUPINGS'),'N') = 'Y'
351 then
352 l_default_mapping := 'Y';
353 else
354 l_default_mapping := 'N';
355 end if;
356
357 FOR I in A_DUP_SET_ID.FIRST..A_DUP_SET_ID.LAST
358 LOOP
359 --log(' ');
360 --log(' A_DUP_SET_ID('||I||') = '||A_DUP_SET_ID(I));
361 --log (' l_request_type = ' || l_request_type);
362 l_count := chk_for_rem_batch(A_DUP_SET_ID(I));
363 --log (' l_count = ' || l_count);
364 -- prefix AM
365 l_all_count := chk_count(A_DUP_SET_ID(I), l_winner_party_id(I));
366 --log (' l_all_count = ' || l_all_count);
367 IF substr(l_batch_name,0,3) <> 'AM:' THEN --SDIB scheduling bug 6067226
368 update hz_dup_batch
369 set dup_batch_name = 'AM: ' ||substr(l_batch_name, 1, 250)
370 where dup_batch_id = p_dup_batch_id;
371 END IF;
372
373 IF (( l_count > 0) AND (is_first))THEN
374 is_first := false;
375 select hz_dup_batch_s.nextval into l_new_dup_batch_id from dual;
376 log (' l_new_dup_batch_id = '|| l_new_dup_batch_id);
377 -- substring of dup_batch_name as this gets appended and inserted in same column. This way we avoid insertion errors.
378
379 insert into hz_dup_batch(dup_batch_id, dup_batch_name, match_rule_id, request_type,
380 created_by, creation_date, last_update_login, last_update_date,
381 last_updated_by, application_id)
382 values(l_new_dup_batch_id, l_batch_name, l_match_rule_id, l_req_type,
383 HZ_UTILITY_V2PUB.CREATED_BY, HZ_UTILITY_V2PUB.CREATION_DATE, HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
384 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE, HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
385 HZ_UTILITY_V2PUB.APPLICATION_ID);
386 END IF;
387 -- update winner party id if request_type = 'SYSTEM_GENERATED'
388 IF (l_request_type = 'SYSTEM_GENERATED') THEN
389
390 -- execute immediate l_sql_stmt1 into l_winner_party_id(I) using A_DUP_SET_ID(I);
391
392 open get_active_party_count_csr(A_DUP_SET_ID(I));
393 fetch get_active_party_count_csr into l_master_candidate_cnt;
394 close get_active_party_count_csr;
395
396 if l_master_candidate_cnt <> 0
397 then
398 hz_dup_pvt.default_master(
399 p_dup_set_id => A_DUP_SET_ID(I),
400 x_master_party_id => l_winner_party_id(I),
401 x_master_party_name => l_party_name,
402 x_return_status => x_return_status,
403 x_msg_count => x_msg_count,
404 x_msg_data => x_msg_data );
405 -- log(' l_winner_party_id('||I||') = '|| l_winner_party_id(I));
406 end if;
407 -- The code below is called in default_master
408 /*
409 update hz_dup_sets c
410 set c.winner_party_id = l_winner_party_id(I)
411 where c.dup_set_id = A_DUP_SET_ID(I);
412
413 -- update the winner party id to have merge_flag = 'Y'
414 update hz_dup_set_parties
415 set merge_flag = 'Y'
416 where dup_set_id = A_DUP_SET_ID(I)
417 and dup_party_id = l_winner_party_id(I); */
418 END IF;
419
420 -- create merge batch
421 IF (l_all_count > l_count) THEN
422 open get_obj_version_csr(A_DUP_SET_ID(I));
423 fetch get_obj_version_csr into l_object_version_number;
424 close get_obj_version_csr;
425
426 HZ_MERGE_DUP_PVT.Create_Merge_Batch(
427 A_DUP_SET_ID(I), -- all ids from c1
428 l_default_mapping,
429 l_object_version_number,
430 A_MERGE_BATCH_ID(I),
431 x_return_status,
432 x_msg_count,
433 x_msg_data);
434 --log(' A_MERGE_BATCH_ID('|| I ||') = '||A_MERGE_BATCH_ID(I));
435 --log(SubStr(' x_return_status from api HZ_MERGE_DUP_PVT.Create_Merge_Batch = '||x_return_status,1,255));
436 END IF;
437 -- If no 'N', then do not request party merge
438 IF (l_count = 0) THEN
439 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
440 update hz_dup_sets set status = 'AM_QUEUE' where dup_set_id = A_DUP_SET_ID(I);
441 ELSE
442 l_success := FALSE;
443 retcode := 2;
444 err := SQLERRM;
445 fnd_msg_pub.count_and_get(
446 p_encoded => fnd_api.g_false,
447 p_count => x_msg_count,
448 p_data => x_msg_data);
449 ROLLBACK TO automerge;
450 EXIT;
451 END IF;
452 END IF;
453 IF (l_count > 0) THEN
454 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
455 create_rem_batch( p_dup_batch_id, A_DUP_SET_ID(I), L_WINNER_PARTY_ID(I), l_new_dup_batch_id, 'S');
456 delete_non_am_dup_set(A_DUP_SET_ID(I), l_all_count, l_count);
457
458 IF (l_all_count > l_count) THEN
459 log (' updating hz_dup_sets status to AM Queue for dup_set_id = ' || A_DUP_SET_ID(I));
460 update hz_dup_sets set status = 'AM_QUEUE' where dup_set_id = A_DUP_SET_ID(I);
461 END IF;
462 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
463 -- if unexpected error, then error out the program
464 l_success := FALSE;
470 p_data => x_msg_data);
465 retcode := 2;
466 err := SQLERRM;
467 fnd_msg_pub.count_and_get(
468 p_encoded => fnd_api.g_false,
469 p_count => x_msg_count,
471 ROLLBACK TO automerge;
472 close c1;
473 EXIT;
474 -- if expected error, create remainder batch but do not run party merge
475 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
476 l_success := FALSE;
477 l_count := chk_for_rem_batch(A_DUP_SET_ID(I));
478 create_rem_batch( p_dup_batch_id, A_DUP_SET_ID(I), L_WINNER_PARTY_ID(I), l_new_dup_batch_id, 'E');
479 update hz_dup_sets set status = 'CREATION_ERROR' where dup_set_id = A_DUP_SET_ID(I);
480 retcode := 1;
481 err := SQLERRM;
482 log ('Error :: ' || SQLERRM);
483 END IF;
484 END IF;
485 END LOOP;
486 close c1;
487 COMMIT;
488 log (' Commit done.');
489 log(' Number of merge requests generated: '|| to_char(A_DUP_SET_ID.count));
490 END IF;
491 end if; -- if l_exist
492 END IF; -- if req_data is null
493 i := 1;
494 J := 0;
495 select count(*) into J
496 from hz_dup_sets
497 where status = 'AM_QUEUE'
498 and dup_batch_id = p_dup_batch_id;
499 IF J <= 0 THEN
500 log (' No more records to process -- exiting, J = ' || J);
501 l_success := false;
502 END IF;
503
504 if not (l_success)
505 then log (' l_success is false');
506 end if;
507
508 IF (l_success) THEN
509 FOR TX IN (select dup_set_id
510 from hz_dup_sets
511 where status = 'AM_QUEUE'
512 and rownum <= l_no_of_workers
513 and dup_batch_id = p_dup_batch_id)
514 LOOP
515 -- call party merge
516 -- log(' Calling party merge for following DUP_SET_ID = '|| TX.DUP_SET_ID);
517 l_sub_requests(i) := FND_REQUEST.SUBMIT_REQUEST('AR', 'ARHPMERGE',
518 'Party Merge Concurrent Request',
519 to_char(sysdate,'DD-MON-YY HH24:MI:SS'),
520 true, to_char(TX.DUP_SET_ID), 'N');
521 -- log ( ' l_sub_requests = ' || l_sub_requests(i));
522 IF l_sub_requests(i) = 0 THEN
523 log('Error submitting worker ' || i);
524 log(fnd_message.get);
525 -- ELSE
526 -- log(' Submitted request for Worker ' || TO_CHAR(I) );
527 -- log(' Request ID : ' || l_sub_requests(i));
528 END IF;
529 EXIT when l_sub_requests(i) = 0;
530 i := i + 1;
531 END LOOP;
532 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
533 request_data => 'SUBMITTING_MERGE_REQUEST') ;
534 err := 'Concurrent Workers submitted.';
535 retcode := 0;
536 END IF;
537 END IF; -- IF (upper(l_automerge_flag) <> 'Y') THEN
538 EXCEPTION WHEN OTHERS THEN
539 ROLLBACK to automerge;
540 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
541 retcode := 2;
542 err := SQLERRM;
543 log ('ERROR :: =' || SQLERRM);
544 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
545 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
546 FND_MSG_PUB.ADD;
547 FND_MSG_PUB.Count_And_Get(
548 p_encoded => FND_API.G_FALSE,
549 p_count => x_msg_count,
550 p_data => x_msg_data);
551
552 RAISE;
553 END automerge;
554
555 END HZ_AUTOMERGE_PKG;
556
557