[Home] [Help]
247:
248: END;
249:
250:
251: PROCEDURE update_hz_int_dup_results (
252: p_batch_id IN number,
253: p_cur IN EntityCur )
254: is
255: l_limit NUMBER := 200;
278:
279: BEGIN
280:
281: FORALL I in H_F_OSR.FIRST..H_F_OSR.LAST
282: UPDATE HZ_INT_DUP_RESULTS A
283: SET A.SCORE = A.SCORE + H_SCORE(I)
284: WHERE
285: ( A.F_OSR = H_F_OSR(I) and
286: A.T_OSR = H_T_OSR(I) and
297: ---------- exception block ---------------
298: EXCEPTION
299: WHEN OTHERS THEN
300: FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
301: FND_MESSAGE.SET_TOKEN('PROC','UPDATE_HZ_INT_DUP_RESULTS');
302: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
303: FND_MSG_PUB.ADD;
304: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
305: END;
328: -- The pair with the higher score, if in case, the pairs of different scores.
329:
330: /* -- WE WILL BE REPLACING THIS BY AN INSERT TO A TEMP TABLE
331: -- SINCE DELETE IS PERFORMANCE PROHIBITIVE.
332: delete from hz_int_dup_results a
333: where
334: -- delete anything less than the threshold
335: (a.score < p_threshold and a.batch_id = p_batch_id);
336:
333: where
334: -- delete anything less than the threshold
335: (a.score < p_threshold and a.batch_id = p_batch_id);
336:
337: delete from hz_int_dup_results a
338: where
339: a.batch_id = p_batch_id
340: and
341: -- if scores are same, delete the one with highest source
340: and
341: -- if scores are same, delete the one with highest source
342: -- or if scores are different, delete the one with lower score
343: (exists
344: (Select 1 from hz_int_dup_results b
345: where
346: (
347: -- APPLY THE ABOVE PRINCIPLE TO REVERSED PAIRS
348: a.f_osr=b.t_osr and b.f_osr=a.t_osr and b.batch_id = p_batch_id and
371: -- is transitive and has the (same score and whose f_osr is small) or (whose score exceeds a)
372:
373:
374: select count(1) into p_count
375: from hz_int_dup_results
376: where batch_id = p_batch_id;
377:
378: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
379: FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties in HZ_INT_DUP_RESULTS before sanitization '|| p_count );
375: from hz_int_dup_results
376: where batch_id = p_batch_id;
377:
378: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
379: FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties in HZ_INT_DUP_RESULTS before sanitization '|| p_count );
380: FND_FILE.put_line(FND_FILE.log,'Parties ::: Begin time of insert '||to_char(sysdate,'hh24:mi:ss'));
381:
382:
383: -- Bug fix for 3639346 :::: Need to take scores that are >= threshold
380: FND_FILE.put_line(FND_FILE.log,'Parties ::: Begin time of insert '||to_char(sysdate,'hh24:mi:ss'));
381:
382:
383: -- Bug fix for 3639346 :::: Need to take scores that are >= threshold
384: insert into hz_int_dup_results_gt (batch_id, f_osr, f_os, t_osr, t_os, ord_f_osr, ord_t_osr, score)
385: select a.batch_id, a.f_osr, a.f_os, a.t_osr, a.t_os, a.ord_f_osr, a.ord_t_osr, a.score
386: from hz_int_dup_results a
387: where
388: (a.score >= p_threshold and a.batch_id = p_batch_id)
382:
383: -- Bug fix for 3639346 :::: Need to take scores that are >= threshold
384: insert into hz_int_dup_results_gt (batch_id, f_osr, f_os, t_osr, t_os, ord_f_osr, ord_t_osr, score)
385: select a.batch_id, a.f_osr, a.f_os, a.t_osr, a.t_os, a.ord_f_osr, a.ord_t_osr, a.score
386: from hz_int_dup_results a
387: where
388: (a.score >= p_threshold and a.batch_id = p_batch_id)
389: and
390: not exists
387: where
388: (a.score >= p_threshold and a.batch_id = p_batch_id)
389: and
390: not exists
391: (select 1 from hz_int_dup_results b
392: where
393: b.batch_id = p_batch_id
394: and
395: (
407: )
408: );
409:
410: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
411: FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties inserted to HZ_INT_DUP_RESULTS_GT '||SQL%ROWCOUNT);
412: FND_FILE.put_line(FND_FILE.log,'Parties ::: End time of insert '||to_char(sysdate,'hh24:mi:ss'));
413:
414:
415: EXCEPTION
1804: )
1805: IS
1806: BEGIN
1807: -- FIRST, REPORT WINNER OSRS TO INTERFACE DEDUP RESULTS
1808: -- THESE ARE ALL PRECISELY THE ORD_F_OSRs IN HZ_INT_DUP_RESULTS_GT WHICH ARE NOT
1809: -- ORD_T_OSRs OF ANY ROW AND OCCUR AT ODD LEVELS IN THE CONNECT BY.
1810:
1811: ----------------------------------------------------
1812: --- EXAMPLE :
1837: ,hz_utility_v2pub.last_updated_by
1838: from
1839: (
1840: select distinct d.ord_f_osr as win_party_osr, d.f_os as win_party_os, level as levelu
1841: from hz_int_dup_results d
1842: start with d.ord_f_osr not in
1843: (
1844: select c.ord_t_osr
1845: from hz_int_dup_results c
1841: from hz_int_dup_results d
1842: start with d.ord_f_osr not in
1843: (
1844: select c.ord_t_osr
1845: from hz_int_dup_results c
1846: )
1847: connect by prior ord_t_osr = ord_f_osr
1848: )
1849: where mod(levelu, 2) = 1 ;
1854: FND_FILE.put_line(FND_FILE.log,'Parties ::: Begin time of insert '||to_char(sysdate,'hh24:mi:ss'));
1855:
1856:
1857:
1858: -- we use the temporary table hz_int_dup_results_gt, instead of
1859: -- hz_int_dup_results
1860: insert into hz_imp_int_dedup_results ( batch_id, winner_record_osr, winner_record_os,
1861: dup_record_osr, dup_record_os, entity,
1862: score, dup_creation_date, dup_last_update_date
1855:
1856:
1857:
1858: -- we use the temporary table hz_int_dup_results_gt, instead of
1859: -- hz_int_dup_results
1860: insert into hz_imp_int_dedup_results ( batch_id, winner_record_osr, winner_record_os,
1861: dup_record_osr, dup_record_os, entity,
1862: score, dup_creation_date, dup_last_update_date
1863: ,created_by,creation_date,last_update_login
1869: ,hz_utility_v2pub.last_updated_by
1870: from
1871: (
1872: select distinct d.ord_f_osr as win_party_osr, d.f_os as win_party_os, level as levelu
1873: from hz_int_dup_results_gt d
1874: start with d.ord_f_osr not in
1875: (
1876: select c.ord_t_osr
1877: from hz_int_dup_results_gt c
1873: from hz_int_dup_results_gt d
1874: start with d.ord_f_osr not in
1875: (
1876: select c.ord_t_osr
1877: from hz_int_dup_results_gt c
1878: where c.batch_id = p_batch_id
1879: )
1880: and d.batch_id = p_batch_id
1881: connect by prior ord_t_osr = ord_f_osr and prior batch_id = batch_id
1890: FND_FILE.put_line(FND_FILE.log,'Parties ::: Begin time of insert '||to_char(sysdate,'hh24:mi:ss'));
1891:
1892:
1893: -- Take inserted winner party osrs from hz_imp_int_dedup_results , join with
1894: -- hz_int_dup_results_gt and insert the winner, dup pair to hz_imp_int_dedup_results
1895: insert into hz_imp_int_dedup_results ( batch_id, winner_record_osr, winner_record_os,
1896: dup_record_osr, dup_record_os, entity,
1897: score, dup_creation_date, dup_last_update_date
1898: ,created_by,creation_date,last_update_login
1901: 'PARTY', b.score, hz_utility_pub.creation_date, hz_utility_pub.last_update_date
1902: ,hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date
1903: ,hz_utility_v2pub.last_update_login,hz_utility_v2pub.last_update_date
1904: ,hz_utility_v2pub.last_updated_by
1905: from hz_imp_int_dedup_results a, hz_int_dup_results_gt b
1906: where a.batch_id = p_batch_id and b.batch_id = p_batch_id and a.entity = 'PARTY'
1907: and b.ord_f_osr = a.winner_record_osr ;
1908:
1909: FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of duplicate parties inserted to HZ_IMP_INT_DEDUP_RESULTS '||SQL%ROWCOUNT);
1985: l_pkg_name := 'HZ_IMP_MATCH_RULE_'||p_match_rule_id;
1986:
1987: -- call the corresponding function in this match rule package, to join based on entities
1988: -- dictated by the match rule and do the following
1989: -- 1. report party dup results to hz_int_dup_results
1990: -- 2. report detail dup results directly to hz_imp_int_dedup_results
1991:
1992: anon_str := 'begin ' || l_pkg_name || '.interface_join_entities(:p_batch_id,' ||
1993: ':p_from_osr,:p_to_osr,:x_threshold); end;' ;