[Home] [Help]
1288: BEGIN
1289: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1290: FND_FILE.put_line(FND_FILE.log,'WU: '||from_osr||' to '||to_osr);
1291: FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));
1292: insert into hz_int_dup_results(batch_id, f_osr,t_osr,ord_f_osr,ord_t_osr,score,f_os, t_os)
1293: select p_batch_id, f, t, least(f,t), greatest(f,t), sum(score) score, fos, tos from (
1294: ------------------ PARTY LEVEL DUPLICATE IDENTIFICATION BEGINS --------------------
1295: select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,
1296: -------PARTY ENTITY: SCORING SECTION ---------
1309: )
1310: score, s1.party_os fos, s2.party_os tos
1311: from HZ_SRCH_PARTIES s1, HZ_SRCH_PARTIES s2
1312: where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.party_osr <> s2.party_osr
1313: 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)
1314: and (
1315: -------PARTY ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
1316: -- do an or between all the transformations of an attribute --
1317: (
1349: )
1350: score, s1.party_os fos, s2.party_os tos
1351: from HZ_SRCH_PSITES s1, HZ_SRCH_PSITES s2
1352: where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.party_osr <> s2.party_osr
1353: 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)
1354: and (
1355: -------PARTY_SITES ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
1356: -- do an or between all the transformations of an attribute --
1357: (
1401: 0
1402: )
1403: )
1404: score
1405: from hz_int_dup_results h1, HZ_SRCH_CPTS s1, HZ_SRCH_CPTS s2
1406: where
1407: s1.party_osr = h1.f_osr and s2.party_osr = h1.t_osr and h1.batch_id = p_batch_id
1408: and s1.party_osr between from_osr and to_osr
1409: and s1.contact_point_type = s2.contact_point_type
1419: (s1.TX158 is not null and s2.TX158 like s1.TX158 || decode(sign(lengthb(s1.TX158)-3),1,'%',''))
1420: )
1421: )
1422: ) group by f,t ;
1423: HZ_DQM_DUP_ID_PKG.update_hz_int_dup_results(p_batch_id,x_ent_cur);
1424: close x_ent_cur;
1425: FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
1426: FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
1427: FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of CONTACT_POINTS');
1443: decode(instrb(s2.TX22,s1.TX22),1,10,
1444: 0
1445: )
1446: score
1447: from hz_int_dup_results h1, HZ_SRCH_CONTACTS s1, HZ_SRCH_CONTACTS s2
1448: where
1449: s1.party_osr = h1.f_osr and s2.party_osr = h1.t_osr and h1.batch_id = p_batch_id
1450: and s1.party_osr between from_osr and to_osr
1451: and (
1455: (s1.TX23 is not null and s2.TX23 like s1.TX23 || decode(sign(lengthb(s1.TX23)-3),1,'%',''))
1456: )
1457: )
1458: ) group by f,t ;
1459: HZ_DQM_DUP_ID_PKG.update_hz_int_dup_results(p_batch_id,x_ent_cur);
1460: close x_ent_cur;
1461: FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
1462: FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
1463: FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of CONTACTS');