[Home] [Help]
1316: BEGIN
1317: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1318: FND_FILE.put_line(FND_FILE.log,'WU: '||from_osr||' to '||to_osr);
1319: FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));
1320: insert into hz_int_dup_results(batch_id, f_osr,t_osr,ord_f_osr,ord_t_osr,score,f_os, t_os)
1321: select p_batch_id, f, t, least(f,t), greatest(f,t), sum(score) score, fos, tos from (
1322: ------------------ PARTY LEVEL DUPLICATE IDENTIFICATION BEGINS --------------------
1323: select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,
1324: -------PARTY ENTITY: SCORING SECTION ---------
1337: )
1338: score, s1.party_os fos, s2.party_os tos
1339: from HZ_SRCH_PARTIES s1, HZ_SRCH_PARTIES s2
1340: where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.party_osr <> s2.party_osr
1341: 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)
1342: and (
1343: -------PARTY ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
1344: -- do an or between all the transformations of an attribute --
1345: (
1375: )
1376: score, s1.party_os fos, s2.party_os tos
1377: from HZ_SRCH_CPTS s1, HZ_SRCH_CPTS s2
1378: where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.party_osr <> s2.party_osr
1379: 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)
1380: and s1.contact_point_type = s2.contact_point_type
1381: and (
1382: -------CONTACT_POINTS ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
1383: -- do an or between all the transformations of an attribute --
1438: decode(instrb(s2.TX22,s1.TX22),1,5,
1439: 0
1440: )
1441: score
1442: from hz_int_dup_results h1, HZ_SRCH_PSITES s1, HZ_SRCH_PSITES s2
1443: where
1444: s1.party_osr = h1.f_osr and s2.party_osr = h1.t_osr and h1.batch_id = p_batch_id
1445: and s1.party_osr between from_osr and to_osr
1446: and (
1456: (
1457: ((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11)
1458: )
1459: ) group by f,t ;
1460: HZ_DQM_DUP_ID_PKG.update_hz_int_dup_results(p_batch_id,x_ent_cur);
1461: close x_ent_cur;
1462: FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
1463: FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
1464: FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
1480: decode(instrb(s2.TX22,s1.TX22),1,10,
1481: 0
1482: )
1483: score
1484: from hz_int_dup_results h1, HZ_SRCH_CONTACTS s1, HZ_SRCH_CONTACTS s2
1485: where
1486: s1.party_osr = h1.f_osr and s2.party_osr = h1.t_osr and h1.batch_id = p_batch_id
1487: and s1.party_osr between from_osr and to_osr
1488: and (
1492: (s1.TX23 is not null and s2.TX23 like s1.TX23 || decode(sign(lengthb(s1.TX23)-3),1,'%',''))
1493: )
1494: )
1495: ) group by f,t ;
1496: HZ_DQM_DUP_ID_PKG.update_hz_int_dup_results(p_batch_id,x_ent_cur);
1497: close x_ent_cur;
1498: FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
1499: FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
1500: FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of CONTACTS');