[Home] [Help]
1094: -------------------------------------------------------------------------------------------------------------
1095: -------------------------------------------------------------------------------------------------------------
1096:
1097: /**
1098: * PROCEDURE update_hz_imp_dup_parties
1099: *
1100: * DESCRIPTION
1101: *
1102: *
1115: *
1116: */
1117:
1118: --------------------------------------------------------------------------------------
1119: -- update_hz_imp_dup_parties ::: This is a generic procedure, that would do a bulk update
1120: -- of hz_dup_results, using a passed in open cursor
1121: --------------------------------------------------------------------------------------
1122:
1123:
1120: -- of hz_dup_results, using a passed in open cursor
1121: --------------------------------------------------------------------------------------
1122:
1123:
1124: PROCEDURE update_hz_imp_dup_parties (
1125: p_batch_id IN number,
1126: p_cur IN EntityCur )
1127: is
1128: l_limit NUMBER := 200;
1151:
1152: BEGIN
1153:
1154: FORALL I in H_PARTY_ID.FIRST..H_DUP_PARTY_ID.LAST
1155: UPDATE HZ_IMP_DUP_PARTIES A
1156: SET A.SCORE = A.SCORE + H_SCORE(I)
1157: WHERE
1158: ( A.PARTY_ID = H_PARTY_ID(I) and
1159: A.DUP_PARTY_ID = H_DUP_PARTY_ID(I) and
1169: ---------- exception block ---------------
1170: EXCEPTION
1171: WHEN OTHERS THEN
1172: FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
1173: FND_MESSAGE.SET_TOKEN('PROC','UPDATE_HZ_IMP_DUP_PARTIES');
1174: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
1175: FND_MSG_PUB.ADD;
1176: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1177: END;
1225: -- DUPS ARE INTERFACE PARTIES
1226:
1227:
1228:
1229: -- INSERT INTO HZ_DUP_SETS, ALL THE DISTINCT DUP_PARTY_IDs WHICH OCCUR IN HZ_IMP_DUP_PARTIES
1230: -- AS WINNERS, FOR THE GIVEN BATCH PROVIDED THE FOLLOWING CONDITION IS MET.
1231: -- 1. THE WINNER HAS ATLEAST A PARTY ID ASSOCIATED WITH IT, WHICH HAS
1232: -- ITS AUTO MERGE FLAG <> 'R' AND IS LOADED
1233:
1231: -- 1. THE WINNER HAS ATLEAST A PARTY ID ASSOCIATED WITH IT, WHICH HAS
1232: -- ITS AUTO MERGE FLAG <> 'R' AND IS LOADED
1233:
1234: select count(1) into p_count
1235: from hz_imp_dup_parties
1236: where batch_id = p_batch_id;
1237:
1238: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
1239: FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties in HZ_IMP_DUP_PARTIES before reporting '|| p_count );
1235: from hz_imp_dup_parties
1236: where batch_id = p_batch_id;
1237:
1238: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
1239: FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties in HZ_IMP_DUP_PARTIES before reporting '|| p_count );
1240: FND_FILE.put_line(FND_FILE.log,'Parties ::: Begin time of insert '||to_char(sysdate,'hh24:mi:ss'));
1241:
1242: insert into hz_dup_sets ( winner_party_id, dup_set_id, dup_batch_id,
1243: status, merge_type, created_by, creation_date, last_update_login,
1247: hz_utility_pub.last_update_login, hz_utility_pub.last_update_date,
1248: hz_utility_pub.user_id
1249: from
1250: (select distinct h.dup_party_id as win_party_id
1251: from hz_imp_dup_parties h
1252: where h.batch_id = p_batch_id
1253: and exists (select a.party_id
1254: from hz_imp_dup_parties a, hz_parties b
1255: where a.batch_id = p_batch_id
1250: (select distinct h.dup_party_id as win_party_id
1251: from hz_imp_dup_parties h
1252: where h.batch_id = p_batch_id
1253: and exists (select a.party_id
1254: from hz_imp_dup_parties a, hz_parties b
1255: where a.batch_id = p_batch_id
1256: and a.dup_party_id = h.dup_party_id
1257: and a.auto_merge_flag <> 'R'
1258: and a.party_id = b.party_id
1290: select b.party_id, a.dup_set_id, 0, p_batch_id, b.score , b.auto_merge_flag,
1291: hz_utility_pub.created_by,hz_utility_pub.creation_date,
1292: hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
1293: hz_utility_pub.user_id,a.dup_batch_id --Bug No: 4244529
1294: from hz_dup_sets a, hz_imp_dup_parties b, hz_parties c
1295: where a.dup_batch_id = l_dup_batch_id
1296: and a.winner_party_id = b.dup_party_id
1297: and b.party_id = c.party_id
1298: and c.request_id = p_request_id
1513: -- get the match rule package
1514: l_pkg_name := 'HZ_IMP_MATCH_RULE_'||p_match_rule_id;
1515:
1516: -- call the corresponding function in this match rule package, to join based on entities
1517: -- dictated by the match rule and report results to the hz_imp_dup_parties table
1518: -- and any other details table
1519: anon_str := 'begin ' || l_pkg_name || '.interface_tca_join_entities(:p_batch_id,' ||
1520: ':p_from_osr,:p_to_osr,:x_threshold,:x_auto_merge_threshold); end;' ;
1521:
1686: -- update dup party counts to hz_imp_batch_summary table
1687: update hz_imp_batch_summary h
1688: set dup_parties =
1689: (select count (distinct party_id)
1690: from hz_imp_dup_parties
1691: where batch_id = p_batch_id
1692: and auto_merge_flag = 'Y'
1693: )
1694: where
1698: -- update potential dup party counts to hz_imp_batch_summary table
1699: update hz_imp_batch_summary h
1700: set potential_dup_parties =
1701: (select count (distinct party_id)
1702: from hz_imp_dup_parties
1703: where batch_id = p_batch_id
1704: and auto_merge_flag = 'N'
1705: )
1706: where