[Home] [Help]
990: BEGIN
991: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
992: FND_FILE.put_line(FND_FILE.log,'WU: '||from_osr||' to '||to_osr);
993: FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));
994: insert into hz_int_dup_results(batch_id, f_osr,t_osr,ord_f_osr,ord_t_osr,score,f_os, t_os)
995: select p_batch_id, f, t, least(f,t), greatest(f,t), sum(score) score, fos, tos from (
996: ------------------ PARTY LEVEL DUPLICATE IDENTIFICATION BEGINS --------------------
997: select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,
998: -------PARTY ENTITY: SCORING SECTION ---------
1007: )
1008: score, s1.party_os fos, s2.party_os tos
1009: from HZ_SRCH_PARTIES s1, HZ_SRCH_PARTIES s2
1010: where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.party_osr <> s2.party_osr
1011: and s2.batch_id = p_batch_id and not exists (select 1 from HZ_INT_DUP_RESULTS WHERE t_osr = s1.party_osr and batch_id = p_batch_id)
1012: and (
1013: -------PARTY ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
1014: -- do an or between all the transformations of an attribute --
1015: (
1036: )
1037: score, s1.party_os fos, s2.party_os tos
1038: from HZ_SRCH_CPTS s1, HZ_SRCH_CPTS s2
1039: where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.party_osr <> s2.party_osr
1040: and s2.batch_id = p_batch_id and not exists (select 1 from HZ_INT_DUP_RESULTS WHERE t_osr = s1.party_osr and batch_id = p_batch_id)
1041: and s1.contact_point_type = s2.contact_point_type
1042: and (
1043: -------CONTACT_POINTS ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
1044: -- do an or between all the transformations of an attribute --
1094: decode(instrb(s2.TX22,s1.TX22),1,5,
1095: 0
1096: )
1097: score
1098: from hz_int_dup_results h1, HZ_SRCH_PSITES s1, HZ_SRCH_PSITES s2
1099: where
1100: s1.party_osr = h1.f_osr and s2.party_osr = h1.t_osr and h1.batch_id = p_batch_id
1101: and s1.party_osr between from_osr and to_osr
1102: and (
1112: (
1113: ((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11)
1114: )
1115: ) group by f,t ;
1116: HZ_DQM_DUP_ID_PKG.update_hz_int_dup_results(p_batch_id,x_ent_cur);
1117: close x_ent_cur;
1118: FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
1119: FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
1120: FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');