[Home] [Help]
66: END IF;
67: END ;
68: FUNCTION check_estimate_hits (
69: p_entity VARCHAR2,
70: p_contains_str VARCHAR2) RETURN NUMBER IS
71:
72: ustatus VARCHAR2(255);
73: dstatus VARCHAR2(255);
74: l_bool BOOLEAN;
79: END IF;
80: IF p_entity='PARTY' THEN
81:
82: l_hits := CTX_QUERY.count_hits(
83: g_index_owner||'.'||'HZ_STAGE_PARTIES_T1',p_contains_str, false);
84: ELSIF p_entity='PARTY_SITES' THEN
85: l_hits := CTX_QUERY.count_hits(
86: g_index_owner||'.'||'HZ_STAGE_PARTY_SITES_T1',p_contains_str, false);
87: ELSIF p_entity='CONTACTS' THEN
82: l_hits := CTX_QUERY.count_hits(
83: g_index_owner||'.'||'HZ_STAGE_PARTIES_T1',p_contains_str, false);
84: ELSIF p_entity='PARTY_SITES' THEN
85: l_hits := CTX_QUERY.count_hits(
86: g_index_owner||'.'||'HZ_STAGE_PARTY_SITES_T1',p_contains_str, false);
87: ELSIF p_entity='CONTACTS' THEN
88: l_hits := CTX_QUERY.count_hits(
89: g_index_owner||'.'||'HZ_STAGE_CONTACT_T1',p_contains_str, false);
90: ELSIF p_entity='CONTACT_POINTS' THEN
85: l_hits := CTX_QUERY.count_hits(
86: g_index_owner||'.'||'HZ_STAGE_PARTY_SITES_T1',p_contains_str, false);
87: ELSIF p_entity='CONTACTS' THEN
88: l_hits := CTX_QUERY.count_hits(
89: g_index_owner||'.'||'HZ_STAGE_CONTACT_T1',p_contains_str, false);
90: ELSIF p_entity='CONTACT_POINTS' THEN
91: l_hits := CTX_QUERY.count_hits(
92: g_index_owner||'.'||'HZ_STAGE_CPT_T1',p_contains_str, false);
93: END IF;
88: l_hits := CTX_QUERY.count_hits(
89: g_index_owner||'.'||'HZ_STAGE_CONTACT_T1',p_contains_str, false);
90: ELSIF p_entity='CONTACT_POINTS' THEN
91: l_hits := CTX_QUERY.count_hits(
92: g_index_owner||'.'||'HZ_STAGE_CPT_T1',p_contains_str, false);
93: END IF;
94: RETURN floor(l_hits/2) ;
95:
96:
1173: p_party_max_score NUMBER,
1174: p_denorm_max_score NUMBER,
1175: p_non_denorm_max_score NUMBER,
1176: p_threshold NUMBER) RETURN VARCHAR2 IS
1177: l_party_contains_str VARCHAR2(32000);
1178: l_party_filter VARCHAR2(1) := null;
1179: l_prim_temp VARCHAR2(4000);
1180: l_denorm_str VARCHAR2(4000);
1181: BEGIN
1217:
1218: IF l_denorm_str IS NOT NULL THEN
1219: IF (p_non_denorm_max_score+40)>=p_threshold THEN
1220: l_denorm_str := NULL;
1221: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1222: ELSIF (p_non_denorm_max_score+p_denorm_max_score+40)>=p_threshold THEN
1223: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1224: l_denorm_str := NULL;
1225: END IF;
1219: IF (p_non_denorm_max_score+40)>=p_threshold THEN
1220: l_denorm_str := NULL;
1221: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1222: ELSIF (p_non_denorm_max_score+p_denorm_max_score+40)>=p_threshold THEN
1223: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1224: l_denorm_str := NULL;
1225: END IF;
1226: ELSE
1227: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1223: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1224: l_denorm_str := NULL;
1225: END IF;
1226: ELSE
1227: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1228: END IF;
1229: IF lengthb(l_party_contains_str) > 4000 THEN
1230: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1231: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1225: END IF;
1226: ELSE
1227: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1228: END IF;
1229: IF lengthb(l_party_contains_str) > 4000 THEN
1230: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1231: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1232: FND_MSG_PUB.ADD;
1233: RAISE FND_API.G_EXC_ERROR;
1231: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1232: FND_MSG_PUB.ADD;
1233: RAISE FND_API.G_EXC_ERROR;
1234: END IF;
1235: IF (p_match_str = ' AND ' OR p_party_max_score
1237: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is an AND between these strings ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1238: END IF;
1239: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1233: RAISE FND_API.G_EXC_ERROR;
1234: END IF;
1235: IF (p_match_str = ' AND ' OR p_party_max_score
1237: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is an AND between these strings ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1238: END IF;
1239: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1240: hz_utility_v2pub.debug(p_message=>'l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1241: END IF;
1236: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1237: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is an AND between these strings ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1238: END IF;
1239: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1240: hz_utility_v2pub.debug(p_message=>'l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1241: END IF;
1242: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1243: hz_utility_v2pub.debug(p_message=>'p_denorm_str = '||p_denorm_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1244: END IF;
1241: END IF;
1242: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1243: hz_utility_v2pub.debug(p_message=>'p_denorm_str = '||p_denorm_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1244: END IF;
1245: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1246: ELSE
1247: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1248: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1249: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1244: END IF;
1245: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1246: ELSE
1247: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1248: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1249: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1250: END IF;
1251: RETURN l_party_contains_str;
1252: END IF;
1245: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1246: ELSE
1247: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1248: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1249: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1250: END IF;
1251: RETURN l_party_contains_str;
1252: END IF;
1253: END;
1247: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1248: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1249: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1250: END IF;
1251: RETURN l_party_contains_str;
1252: END IF;
1253: END;
1254: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1255: l_contains_str VARCHAR2(32000);
1251: RETURN l_party_contains_str;
1252: END IF;
1253: END;
1254: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1255: l_contains_str VARCHAR2(32000);
1256: l_contains_str_temp VARCHAR2(32000);
1257: l_den_contains_str VARCHAR2(32000);
1258: l_den_contains_str_temp VARCHAR2(32000);
1259: l_filter_str VARCHAR2(4000) := null;
1252: END IF;
1253: END;
1254: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1255: l_contains_str VARCHAR2(32000);
1256: l_contains_str_temp VARCHAR2(32000);
1257: l_den_contains_str VARCHAR2(32000);
1258: l_den_contains_str_temp VARCHAR2(32000);
1259: l_filter_str VARCHAR2(4000) := null;
1260: l_prim_temp VARCHAR2(4000) := null;
1253: END;
1254: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1255: l_contains_str VARCHAR2(32000);
1256: l_contains_str_temp VARCHAR2(32000);
1257: l_den_contains_str VARCHAR2(32000);
1258: l_den_contains_str_temp VARCHAR2(32000);
1259: l_filter_str VARCHAR2(4000) := null;
1260: l_prim_temp VARCHAR2(4000) := null;
1261: l_prim_temp_den VARCHAR2(4000) := null;
1254: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1255: l_contains_str VARCHAR2(32000);
1256: l_contains_str_temp VARCHAR2(32000);
1257: l_den_contains_str VARCHAR2(32000);
1258: l_den_contains_str_temp VARCHAR2(32000);
1259: l_filter_str VARCHAR2(4000) := null;
1260: l_prim_temp VARCHAR2(4000) := null;
1261: l_prim_temp_den VARCHAR2(4000) := null;
1262: BEGIN
1264: hz_utility_v2pub.debug(p_message=>'INIT_PARTY_SITES_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
1265: END IF;
1266: x_denorm_str := NULL;
1267:
1268: -- Dynamic setup of party site contains str
1269: --
1270: -- For each primary transformation add to intermedia query if it
1271: -- is not null
1272: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1269: --
1270: -- For each primary transformation add to intermedia query if it
1271: -- is not null
1272: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1273: l_contains_str_temp := null;
1274: l_den_contains_str_temp := null;
1275: l_filter_str := null;
1276:
1277: -- Setup query string for ADDRESS
1270: -- For each primary transformation add to intermedia query if it
1271: -- is not null
1272: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1273: l_contains_str_temp := null;
1274: l_den_contains_str_temp := null;
1275: l_filter_str := null;
1276:
1277: -- Setup query string for ADDRESS
1278: l_prim_temp := null;
1278: l_prim_temp := null;
1279: l_prim_temp_den := null;
1280: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRADDRESS
1281: g_party_site_stage_list(I).TX4,'A27',l_prim_temp);
1282: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1283:
1284: -- Setup query string for CITY
1285: l_prim_temp := null;
1286: l_prim_temp_den := null;
1287: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1288: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1289: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1290: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1291: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1292: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1293:
1294: -- Setup query string for POSTAL_CODE
1295: l_prim_temp := null;
1288: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1289: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1290: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1291: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1292: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1293:
1294: -- Setup query string for POSTAL_CODE
1295: l_prim_temp := null;
1296: l_prim_temp_den := null;
1297: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1298: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1299: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1300: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1301: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1302: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1303:
1304: -- Setup query string for PROVINCE
1305: l_prim_temp := null;
1298: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1299: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1300: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1301: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1302: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1303:
1304: -- Setup query string for PROVINCE
1305: l_prim_temp := null;
1306: l_prim_temp_den := null;
1307: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1308: g_party_site_stage_list(I).TX13,'A31',l_prim_temp);
1309: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1310: g_party_site_stage_list(I).TX13,'D_PS',l_prim_temp_den);
1311: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1312: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1313:
1314: -- Setup query string for STATE
1315: l_prim_temp := null;
1308: g_party_site_stage_list(I).TX13,'A31',l_prim_temp);
1309: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1310: g_party_site_stage_list(I).TX13,'D_PS',l_prim_temp_den);
1311: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1312: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1313:
1314: -- Setup query string for STATE
1315: l_prim_temp := null;
1316: l_prim_temp_den := null;
1317: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1318: g_party_site_stage_list(I).TX15,'A32',l_prim_temp);
1319: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1320: g_party_site_stage_list(I).TX15,'D_PS',l_prim_temp_den);
1321: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1322: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1323:
1324: -- Setup query string for COUNTRY
1325: l_prim_temp := null;
1318: g_party_site_stage_list(I).TX15,'A32',l_prim_temp);
1319: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1320: g_party_site_stage_list(I).TX15,'D_PS',l_prim_temp_den);
1321: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1322: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1323:
1324: -- Setup query string for COUNTRY
1325: l_prim_temp := null;
1326: l_prim_temp_den := null;
1327: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1328: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1329: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1330: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1331: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1332: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1333:
1334: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1335: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1328: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1329: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1330: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1331: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1332: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1333:
1334: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1335: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1336: END LOOP;
1330: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1331: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1332: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1333:
1334: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1335: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1336: END LOOP;
1337: -- Add the search criteria to query string
1338: IF lengthb(l_contains_str) > 4000 THEN
1331: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1332: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1333:
1334: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1335: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1336: END LOOP;
1337: -- Add the search criteria to query string
1338: IF lengthb(l_contains_str) > 4000 THEN
1339: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1334: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1335: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1336: END LOOP;
1337: -- Add the search criteria to query string
1338: IF lengthb(l_contains_str) > 4000 THEN
1339: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1340: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1341: FND_MSG_PUB.ADD;
1342: RAISE FND_API.G_EXC_ERROR;
1340: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1341: FND_MSG_PUB.ADD;
1342: RAISE FND_API.G_EXC_ERROR;
1343: END IF;
1344: x_denorm_str := l_den_contains_str;
1345: RETURN l_contains_str;
1346: END;
1347:
1348: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1341: FND_MSG_PUB.ADD;
1342: RAISE FND_API.G_EXC_ERROR;
1343: END IF;
1344: x_denorm_str := l_den_contains_str;
1345: RETURN l_contains_str;
1346: END;
1347:
1348: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1349: l_contains_str VARCHAR2(32000);
1345: RETURN l_contains_str;
1346: END;
1347:
1348: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1349: l_contains_str VARCHAR2(32000);
1350: l_contains_str_temp VARCHAR2(32000);
1351: l_den_contains_str VARCHAR2(32000);
1352: l_den_contains_str_temp VARCHAR2(32000);
1353: l_filter_str VARCHAR2(4000) := null;
1346: END;
1347:
1348: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1349: l_contains_str VARCHAR2(32000);
1350: l_contains_str_temp VARCHAR2(32000);
1351: l_den_contains_str VARCHAR2(32000);
1352: l_den_contains_str_temp VARCHAR2(32000);
1353: l_filter_str VARCHAR2(4000) := null;
1354: l_prim_temp VARCHAR2(4000) := null;
1347:
1348: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1349: l_contains_str VARCHAR2(32000);
1350: l_contains_str_temp VARCHAR2(32000);
1351: l_den_contains_str VARCHAR2(32000);
1352: l_den_contains_str_temp VARCHAR2(32000);
1353: l_filter_str VARCHAR2(4000) := null;
1354: l_prim_temp VARCHAR2(4000) := null;
1355: l_prim_temp_den VARCHAR2(4000) := null;
1348: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1349: l_contains_str VARCHAR2(32000);
1350: l_contains_str_temp VARCHAR2(32000);
1351: l_den_contains_str VARCHAR2(32000);
1352: l_den_contains_str_temp VARCHAR2(32000);
1353: l_filter_str VARCHAR2(4000) := null;
1354: l_prim_temp VARCHAR2(4000) := null;
1355: l_prim_temp_den VARCHAR2(4000) := null;
1356: BEGIN
1358: hz_utility_v2pub.debug(p_message=>'INIT_CONTACTS_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
1359: END IF;
1360: x_denorm_str := NULL;
1361:
1362: -- Dynamic setup of party site contains str
1363: --
1364: -- For each primary transformation add to intermedia query if it
1365: -- is not null
1366: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1363: --
1364: -- For each primary transformation add to intermedia query if it
1365: -- is not null
1366: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1367: l_contains_str_temp := null;
1368: l_den_contains_str_temp := null;
1369: l_filter_str := null;
1370:
1371: -- Setup query string for JOB_TITLE
1364: -- For each primary transformation add to intermedia query if it
1365: -- is not null
1366: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1367: l_contains_str_temp := null;
1368: l_den_contains_str_temp := null;
1369: l_filter_str := null;
1370:
1371: -- Setup query string for JOB_TITLE
1372: l_prim_temp := null;
1374: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1375: g_contact_stage_list(I).TX22,'A43',l_prim_temp);
1376: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1377: g_contact_stage_list(I).TX22,'D_CT',l_prim_temp_den);
1378: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1379: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1380:
1381: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1382: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1375: g_contact_stage_list(I).TX22,'A43',l_prim_temp);
1376: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1377: g_contact_stage_list(I).TX22,'D_CT',l_prim_temp_den);
1378: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1379: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1380:
1381: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1382: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1383: END LOOP;
1377: g_contact_stage_list(I).TX22,'D_CT',l_prim_temp_den);
1378: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1379: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1380:
1381: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1382: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1383: END LOOP;
1384: -- Add the search criteria to query string
1385: IF lengthb(l_contains_str) > 4000 THEN
1378: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1379: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1380:
1381: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1382: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1383: END LOOP;
1384: -- Add the search criteria to query string
1385: IF lengthb(l_contains_str) > 4000 THEN
1386: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1381: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1382: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1383: END LOOP;
1384: -- Add the search criteria to query string
1385: IF lengthb(l_contains_str) > 4000 THEN
1386: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1387: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
1388: FND_MSG_PUB.ADD;
1389: RAISE FND_API.G_EXC_ERROR;
1387: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
1388: FND_MSG_PUB.ADD;
1389: RAISE FND_API.G_EXC_ERROR;
1390: END IF;
1391: x_denorm_str := l_den_contains_str;
1392: RETURN l_contains_str;
1393: END;
1394:
1395: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1388: FND_MSG_PUB.ADD;
1389: RAISE FND_API.G_EXC_ERROR;
1390: END IF;
1391: x_denorm_str := l_den_contains_str;
1392: RETURN l_contains_str;
1393: END;
1394:
1395: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1396: l_contains_str VARCHAR2(32000);
1392: RETURN l_contains_str;
1393: END;
1394:
1395: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1396: l_contains_str VARCHAR2(32000);
1397: l_contains_str_temp VARCHAR2(32000);
1398: l_den_contains_str VARCHAR2(32000);
1399: l_den_contains_str_temp VARCHAR2(32000);
1400: l_filter_str VARCHAR2(4000) := null;
1393: END;
1394:
1395: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1396: l_contains_str VARCHAR2(32000);
1397: l_contains_str_temp VARCHAR2(32000);
1398: l_den_contains_str VARCHAR2(32000);
1399: l_den_contains_str_temp VARCHAR2(32000);
1400: l_filter_str VARCHAR2(4000) := null;
1401: l_prim_temp VARCHAR2(4000) := null;
1394:
1395: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1396: l_contains_str VARCHAR2(32000);
1397: l_contains_str_temp VARCHAR2(32000);
1398: l_den_contains_str VARCHAR2(32000);
1399: l_den_contains_str_temp VARCHAR2(32000);
1400: l_filter_str VARCHAR2(4000) := null;
1401: l_prim_temp VARCHAR2(4000) := null;
1402: l_prim_temp_den VARCHAR2(4000) := null;
1395: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1396: l_contains_str VARCHAR2(32000);
1397: l_contains_str_temp VARCHAR2(32000);
1398: l_den_contains_str VARCHAR2(32000);
1399: l_den_contains_str_temp VARCHAR2(32000);
1400: l_filter_str VARCHAR2(4000) := null;
1401: l_prim_temp VARCHAR2(4000) := null;
1402: l_prim_temp_den VARCHAR2(4000) := null;
1403: TYPE CONTACT_PT_REC_TYPE IS RECORD (
1411: hz_utility_v2pub.debug(p_message=>'INIT_CONTACT_POINTS_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
1412: END IF;
1413: x_denorm_str := NULL;
1414:
1415: -- Dynamic setup of party site contains str
1416: --
1417: -- For each primary transformation add to intermedia query if it
1418: -- is not null
1419: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1416: --
1417: -- For each primary transformation add to intermedia query if it
1418: -- is not null
1419: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1420: l_contains_str_temp := null;
1421: l_den_contains_str_temp := null;
1422: l_filter_str := null;
1423: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1424: if(l_cnt_pt_type.count>0) then
1417: -- For each primary transformation add to intermedia query if it
1418: -- is not null
1419: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1420: l_contains_str_temp := null;
1421: l_den_contains_str_temp := null;
1422: l_filter_str := null;
1423: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1424: if(l_cnt_pt_type.count>0) then
1425: x_modify := 'Y';
1441: l_prim_temp := null;
1442: l_prim_temp_den := null;
1443: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL CTX
1444: g_contact_pt_stage_list(I).TX1,'A44',l_prim_temp);
1445: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1446:
1447: -- Setup query string for EMAIL_ADDRESS
1448: l_prim_temp := null;
1449: l_prim_temp_den := null;
1448: l_prim_temp := null;
1449: l_prim_temp_den := null;
1450: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1451: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1452: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1453:
1454: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1455: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1456: IF N>1 THEN
1450: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1451: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1452: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1453:
1454: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1455: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1456: IF N>1 THEN
1457: distinct_search_cpt_types := N-1;
1458: ELSE
1451: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1452: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1453:
1454: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1455: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1456: IF N>1 THEN
1457: distinct_search_cpt_types := N-1;
1458: ELSE
1459: distinct_search_cpt_types := N;
1462: hz_utility_v2pub.debug(p_message=>'distinct_search_cpt_types is - '||distinct_search_cpt_types,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1463: END IF;
1464: END LOOP;
1465: -- Add the search criteria to query string
1466: IF lengthb(l_contains_str) > 4000 THEN
1467: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1468: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1469: FND_MSG_PUB.ADD;
1470: RAISE FND_API.G_EXC_ERROR;
1468: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1469: FND_MSG_PUB.ADD;
1470: RAISE FND_API.G_EXC_ERROR;
1471: END IF;
1472: x_denorm_str := l_den_contains_str;
1473: RETURN l_contains_str;
1474: END;
1475:
1476: PROCEDURE open_party_cursor_direct (
1469: FND_MSG_PUB.ADD;
1470: RAISE FND_API.G_EXC_ERROR;
1471: END IF;
1472: x_denorm_str := l_den_contains_str;
1473: RETURN l_contains_str;
1474: END;
1475:
1476: PROCEDURE open_party_cursor_direct (
1477: p_dup_party_id NUMBER,
1477: p_dup_party_id NUMBER,
1478: p_restrict_sql VARCHAR2,
1479: p_match_str VARCHAR2,
1480: p_search_merged VARCHAR2,
1481: p_party_contains_str VARCHAR2,
1482: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1483: l_sqlstr VARCHAR2(4000);
1484: l_search_merged VARCHAR2(1);
1485: BEGIN
1494: END IF;
1495: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1496: hz_utility_v2pub.debug(p_message=>'Search Merged Flag - '||l_search_merged,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1497: END IF;
1498: IF p_restrict_sql IS NULL AND NOT g_other_party_level_attribs AND NOT (p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1) THEN
1499: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1500: hz_utility_v2pub.debug(p_message=>'Restrict SQL is NULL and other conditions met to OPEN x_cursor',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1501: END IF;
1502: OPEN x_cursor FOR
1526: l_sqlstr := l_sqlstr || ' (TX40 like :TX40||'' %'' ';
1527: l_sqlstr := l_sqlstr || ' )) ';
1528: l_sqlstr := l_sqlstr || ' ) ';
1529: END IF;
1530: IF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1531: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1532: hz_utility_v2pub.debug(p_message=>'p_party_contains_str string is NOT NULL and other conditions met',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1533: END IF;
1534: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1528: l_sqlstr := l_sqlstr || ' ) ';
1529: END IF;
1530: IF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1531: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1532: hz_utility_v2pub.debug(p_message=>'p_party_contains_str string is NOT NULL and other conditions met',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1533: END IF;
1534: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1535: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX10||'' %'' ';
1536: l_sqlstr := l_sqlstr || ' )) ';
1561: END IF;
1562: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1563: hz_utility_v2pub.debug(p_message=>'l_sqlstr is - '||l_sqlstr,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1564: END IF;
1565: IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1566: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1567: hz_utility_v2pub.debug(p_message=>'IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1568: END IF;
1569: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id
1563: hz_utility_v2pub.debug(p_message=>'l_sqlstr is - '||l_sqlstr,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1564: END IF;
1565: IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1566: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1567: hz_utility_v2pub.debug(p_message=>'IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1568: END IF;
1569: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id
1570: ,g_party_stage_rec.TX40
1571: ,g_party_stage_rec.TX40
1587: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id
1588: ,g_party_stage_rec.TX40
1589: ,g_party_stage_rec.TX40
1590: ;
1591: ELSIF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1592: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1593: hz_utility_v2pub.debug(p_message=>'ELSIF p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1594: END IF;
1595: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id
1589: ,g_party_stage_rec.TX40
1590: ;
1591: ELSIF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1592: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1593: hz_utility_v2pub.debug(p_message=>'ELSIF p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1594: END IF;
1595: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id
1596: ,g_party_site_stage_list(1).TX10
1597: ,g_party_site_stage_list(1).TX10
1614: END;
1615: PROCEDURE open_party_cursor(
1616: p_dup_party_id NUMBER,
1617: p_restrict_sql VARCHAR2,
1618: p_contains_str VARCHAR2,
1619: p_search_ctx_id NUMBER,
1620: p_match_str VARCHAR2,
1621: p_search_merged VARCHAR2,
1622: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1633: l_search_merged := 'N';
1634: ELSE
1635: l_search_merged := p_search_merged;
1636: END IF;
1637: IF p_contains_str IS NULL THEN
1638: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1639: hz_utility_v2pub.debug(p_message=>'part contains string is null',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1640: END IF;
1641: OPEN x_cursor FOR
1635: l_search_merged := p_search_merged;
1636: END IF;
1637: IF p_contains_str IS NULL THEN
1638: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1639: hz_utility_v2pub.debug(p_message=>'part contains string is null',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1640: END IF;
1641: OPEN x_cursor FOR
1642: SELECT PARTY_ID , TX40, TX39
1643: FROM HZ_STAGED_PARTIES stage
1652: END IF;
1653: OPEN x_cursor FOR
1654: SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID , TX40, TX39
1655: FROM HZ_STAGED_PARTIES stage
1656: WHERE contains( concat_col, p_contains_str)>0
1657: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1658: AND( (l_search_merged ='Y' )
1659: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1660: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )
1665: END IF;
1666: OPEN x_cursor FOR
1667: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX40, TX39
1668: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1669: WHERE contains( concat_col, p_contains_str)>0
1670: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1671: AND d.party_id = stage.party_id
1672: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1673: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1690: END IF;
1691: IF p_search_ctx_id IS NULL THEN
1692: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX40, TX39'||
1693: ' FROM HZ_STAGED_PARTIES stage'||
1694: ' WHERE contains( concat_col, :cont)>0'||
1695: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1696: ' AND ('||p_restrict_sql||')' ||
1697: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1698: IF l_search_merged = 'Y' THEN
1702: ELSE
1703: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1704: END IF;
1705: output_long_strings('----------------------------------------------------------');
1706: output_long_strings('Party Contains String = '||p_contains_str);
1707: output_long_strings('Restrict Sql = '||p_restrict_sql);
1708: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1709: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1710: ,p_dup_party_id, p_dup_party_id;
1704: END IF;
1705: output_long_strings('----------------------------------------------------------');
1706: output_long_strings('Party Contains String = '||p_contains_str);
1707: output_long_strings('Restrict Sql = '||p_restrict_sql);
1708: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1709: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1710: ,p_dup_party_id, p_dup_party_id;
1711: END IF;
1712: END IF;
1735: PROCEDURE open_party_site_cursor(
1736: p_dup_party_id NUMBER,
1737: p_party_id NUMBER,
1738: p_restrict_sql VARCHAR2,
1739: p_contains_str VARCHAR2,
1740: p_search_ctx_id NUMBER,
1741: p_search_merged VARCHAR2,
1742: p_search_rel_sites VARCHAR2,
1743: p_person_api VARCHAR2,
1767: IF p_search_rel_sites = 'N' THEN
1768: OPEN x_cursor FOR
1769: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
1770: FROM HZ_STAGED_PARTY_SITES stage
1771: WHERE contains( concat_col, p_contains_str)>0
1772: AND( (l_search_merged ='Y' )
1773: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1774: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1775: AND stage.party_id = p_party_id;
1776: ELSE
1777: OPEN x_cursor FOR
1778: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
1779: FROM HZ_STAGED_PARTY_SITES stage
1780: WHERE contains( concat_col, p_contains_str)>0
1781: AND( (l_search_merged ='Y' )
1782: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1783: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1784: AND stage.party_id = p_party_id
1784: AND stage.party_id = p_party_id
1785: UNION
1786: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
1787: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1788: WHERE contains( concat_col, p_contains_str)>0
1789: AND( (l_search_merged ='Y' )
1790: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1791: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1792: AND r.object_id = p_party_id
1806: END IF;
1807: OPEN x_cursor FOR
1808: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
1809: FROM HZ_STAGED_PARTY_SITES stage
1810: WHERE contains( concat_col, p_contains_str)>0
1811: AND EXISTS (
1812: SELECT 1 FROM HZ_STAGED_PARTIES p
1813: WHERE p.PARTY_ID = stage.PARTY_ID
1814: AND( (l_search_merged ='Y' )
1826: IF p_person_api = 'Y' THEN
1827: OPEN x_cursor FOR
1828: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
1829: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1830: WHERE contains( concat_col, p_contains_str)>0
1831: AND( (l_search_merged ='Y' )
1832: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1833: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1834: AND d.search_context_id = p_search_ctx_id
1836: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1837: UNION
1838: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
1839: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1840: WHERE contains( concat_col, p_contains_str)>0
1841: AND( (l_search_merged ='Y' )
1842: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1843: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1844: AND d.search_context_id = p_search_ctx_id
1849: ELSE
1850: OPEN x_cursor FOR
1851: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
1852: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1853: WHERE contains( concat_col, p_contains_str)>0
1854: AND( (l_search_merged ='Y' )
1855: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1856: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1857: AND d.search_context_id = p_search_ctx_id
1894: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1895: END IF;
1896: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
1897: ' FROM HZ_STAGED_PARTY_SITES stage'||
1898: ' WHERE contains( concat_col, :cont)>0'||
1899: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1900: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1901: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1902: ' AND (ORG_CONTACT_ID IS NULL ' ||
1904: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1905: ' UNION ' ||
1906: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
1907: ' FROM HZ_STAGED_PARTY_SITES stage'||
1908: ' WHERE contains( concat_col, :cont)>0'||
1909: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1910: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1911: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1912: ' AND ORG_CONTACT_ID IN ' ||
1914: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1915: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1916: ' and ('||p_restrict_sql1|| ') )' ||
1917: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1918: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1919: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1920: ELSE
1921: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
1922: ' FROM HZ_STAGED_PARTY_SITES stage'||
1915: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1916: ' and ('||p_restrict_sql1|| ') )' ||
1917: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1918: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1919: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1920: ELSE
1921: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
1922: ' FROM HZ_STAGED_PARTY_SITES stage'||
1923: ' WHERE contains( concat_col, :cont)>0'||
1919: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1920: ELSE
1921: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
1922: ' FROM HZ_STAGED_PARTY_SITES stage'||
1923: ' WHERE contains( concat_col, :cont)>0'||
1924: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1925: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1926: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1927: ' AND EXISTS ('||
1929: ' WHERE p.party_id = stage.party_id ' ||
1930: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1931: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1932: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1933: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1934: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1935: ,p_dup_party_id, p_dup_party_id;
1936: END IF;
1937: ELSE
1936: END IF;
1937: ELSE
1938: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
1939: ' FROM HZ_STAGED_PARTY_SITES stage'||
1940: ' WHERE contains( concat_col, :cont)>0'||
1941: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1942: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1943: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1944: ' AND EXISTS ('||
1946: ' WHERE p.party_id = stage.party_id ' ||
1947: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1948: ' AND ('||p_restrict_sql||')' ||
1949: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1950: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1951: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1952: ,p_dup_party_id, p_dup_party_id;
1953: END IF;
1954: END IF;
1952: ,p_dup_party_id, p_dup_party_id;
1953: END IF;
1954: END IF;
1955: output_long_strings('----------------------------------------------------------');
1956: output_long_strings('Party Site Contains String = '||p_contains_str);
1957: output_long_strings('Restrict Sql = '||p_restrict_sql);
1958: exception
1959: when others then
1960: if (instrb(SQLERRM,'DRG-51030')>0) then
1980: PROCEDURE open_contact_cursor(
1981: p_dup_party_id NUMBER,
1982: p_party_id NUMBER,
1983: p_restrict_sql VARCHAR2,
1984: p_contains_str VARCHAR2,
1985: p_search_ctx_id NUMBER,
1986: p_search_merged VARCHAR2,
1987: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1988: l_sqlstr VARCHAR2(4000);
2008: END IF;
2009: OPEN x_cursor FOR
2010: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID, TX22
2011: FROM HZ_STAGED_CONTACTS stage
2012: WHERE contains( concat_col, p_contains_str)>0
2013: AND EXISTS (
2014: SELECT 1 FROM HZ_STAGED_PARTIES p
2015: WHERE p.PARTY_ID = stage.PARTY_ID
2016: AND( (l_search_merged ='Y' )
2031: END IF;
2032: OPEN x_cursor FOR
2033: SELECT ORG_CONTACT_ID, PARTY_ID, TX22
2034: FROM HZ_STAGED_CONTACTS stage
2035: WHERE contains( concat_col, p_contains_str)>0
2036: AND EXISTS (
2037: SELECT 1 FROM HZ_STAGED_PARTIES p
2038: WHERE p.PARTY_ID = stage.PARTY_ID
2039: AND( (l_search_merged ='Y' )
2050: END IF;
2051: OPEN x_cursor FOR
2052: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID, TX22
2053: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
2054: WHERE contains( concat_col, p_contains_str)>0
2055: AND d.search_context_id = p_search_ctx_id
2056: AND d.party_id = stage.party_id
2057: AND( (l_search_merged ='Y' )
2058: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2084: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
2085: END IF;
2086: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID , TX22'||
2087: ' FROM HZ_STAGED_CONTACTS stage'||
2088: ' WHERE contains( concat_col, :cont)>0'||
2089: ' AND EXISTS ('||
2090: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2091: ' WHERE p.party_id = stage.party_id ' ||
2092: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2094: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2095: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2096: ' AND ('||p_restrict_sql||')' ||
2097: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2098: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2099: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2100: ,p_dup_party_id, p_dup_party_id;
2101: END IF;
2102: output_long_strings('----------------------------------------------------------');
2099: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2100: ,p_dup_party_id, p_dup_party_id;
2101: END IF;
2102: output_long_strings('----------------------------------------------------------');
2103: output_long_strings('Contacts Contains String = '||p_contains_str);
2104: output_long_strings('Restrict Sql = '||p_restrict_sql);
2105: exception
2106: when others then
2107: if (instrb(SQLERRM,'DRG-51030')>0) then
2127: PROCEDURE open_contact_pt_cursor(
2128: p_dup_party_id NUMBER,
2129: p_party_id NUMBER,
2130: p_restrict_sql VARCHAR2,
2131: p_contains_str VARCHAR2,
2132: p_search_ctx_id NUMBER,
2133: p_search_merged VARCHAR2,
2134: p_search_rel_cpts VARCHAR2,
2135: p_person_api VARCHAR2,
2157: THEN
2158: OPEN x_cursor FOR
2159: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2160: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2161: WHERE contains( concat_col, p_contains_str)>0
2162: AND d.search_context_id = p_search_ctx_id
2163: AND( (l_search_merged ='Y' )
2164: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2165: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2169: THEN
2170: OPEN x_cursor FOR
2171: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N3) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2172: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2173: WHERE contains( concat_col, p_contains_str)>0
2174: AND d.search_context_id = p_search_ctx_id
2175: AND( (l_search_merged ='Y' )
2176: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2177: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2186: IF p_search_rel_cpts = 'N' THEN
2187: OPEN x_cursor FOR
2188: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2189: FROM HZ_STAGED_CONTACT_POINTS stage
2190: WHERE contains( concat_col, p_contains_str)>0
2191: AND( (l_search_merged ='Y' )
2192: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2193: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2194: AND stage.party_id = p_party_id;
2195: ELSE
2196: OPEN x_cursor FOR
2197: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2198: FROM HZ_STAGED_CONTACT_POINTS stage
2199: WHERE contains( concat_col, p_contains_str)>0
2200: AND( (l_search_merged ='Y' )
2201: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2202: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2203: AND stage.party_id = p_party_id
2203: AND stage.party_id = p_party_id
2204: UNION
2205: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ stage.CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , TX1, TX6, TX5
2206: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2207: WHERE contains( concat_col, p_contains_str)>0
2208: AND r.object_id = p_party_id
2209: AND( (l_search_merged ='Y' )
2210: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2211: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2225: END IF;
2226: OPEN x_cursor FOR
2227: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2228: FROM HZ_STAGED_CONTACT_POINTS stage
2229: WHERE contains( concat_col, p_contains_str)>0
2230: AND EXISTS (
2231: SELECT 1 FROM HZ_STAGED_PARTIES p
2232: WHERE p.PARTY_ID = stage.PARTY_ID
2233: AND( (l_search_merged ='Y' )
2245: IF p_person_api = 'Y' THEN
2246: OPEN x_cursor FOR
2247: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2248: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2249: WHERE contains( concat_col, p_contains_str)>0
2250: AND d.search_context_id = p_search_ctx_id
2251: AND d.party_id = stage.party_id
2252: AND( (l_search_merged ='Y' )
2253: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2255: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2256: UNION
2257: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, r.subject_id, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , TX1, TX6, TX5
2258: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2259: WHERE contains( concat_col, p_contains_str)>0
2260: AND d.search_context_id = p_search_ctx_id
2261: AND d.party_id = r.subject_id
2262: AND r.relationship_id = oc.party_relationship_id
2263: AND oc.org_contact_id = stage.org_contact_id
2268: ELSE
2269: OPEN x_cursor FOR
2270: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2271: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2272: WHERE contains( concat_col, p_contains_str)>0
2273: AND d.search_context_id = p_search_ctx_id
2274: AND d.party_id = stage.party_id
2275: AND( (l_search_merged ='Y' )
2276: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2313: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2314: END IF;
2315: l_sqlstr := ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2316: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2317: ' WHERE contains( concat_col, :cont)>0 '||
2318: ' AND (stage.org_contact_id is null '||
2319: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2320: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2321: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2323: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2324: ' UNION ' ||
2325: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2326: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2327: ' WHERE contains( concat_col, :cont)>0 '||
2328: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2329: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2330: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2331: ' AND (stage.org_contact_id in ' ||
2333: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2334: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2335: ' and ('||p_restrict_sql1 || ') ) )' ||
2336: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2337: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2338: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2339: ELSE
2340: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2341: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2334: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2335: ' and ('||p_restrict_sql1 || ') ) )' ||
2336: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2337: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2338: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2339: ELSE
2340: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2341: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2342: ' WHERE contains( concat_col, :cont)>0'||
2338: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2339: ELSE
2340: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2341: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2342: ' WHERE contains( concat_col, :cont)>0'||
2343: ' AND EXISTS ('||
2344: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2345: ' WHERE p.party_id = stage.party_id ' ||
2346: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2348: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2349: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2350: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2351: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2352: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2353: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2354: ,p_dup_party_id, p_dup_party_id;
2355: END IF;
2356: ELSE
2355: END IF;
2356: ELSE
2357: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2358: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2359: ' WHERE contains( concat_col, :cont)>0'||
2360: ' AND EXISTS ('||
2361: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2362: ' WHERE p.party_id = stage.party_id ' ||
2363: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2365: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2366: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2367: ' AND ('||p_restrict_sql||')' ||
2368: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2369: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2370: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2371: ,p_dup_party_id, p_dup_party_id;
2372: END IF;
2373: END IF;
2372: END IF;
2373: END IF;
2374: END IF;
2375: output_long_strings('----------------------------------------------------------');
2376: output_long_strings('Contact Points Contains String = '||p_contains_str);
2377: output_long_strings('Restrict Sql = '||p_restrict_sql);
2378: exception
2379: when others then
2380: if (instrb(SQLERRM,'DRG-51030')>0) then
2493: x_num_matches OUT NUMBER
2494: ) IS
2495:
2496: -- Strings to hold the generated Intermedia query strings
2497: l_party_contains_str VARCHAR2(32000);
2498: l_party_site_contains_str VARCHAR2(32000);
2499: l_contact_contains_str VARCHAR2(32000);
2500: l_contact_pt_contains_str VARCHAR2(32000);
2501: l_denorm_str VARCHAR2(32000);
2494: ) IS
2495:
2496: -- Strings to hold the generated Intermedia query strings
2497: l_party_contains_str VARCHAR2(32000);
2498: l_party_site_contains_str VARCHAR2(32000);
2499: l_contact_contains_str VARCHAR2(32000);
2500: l_contact_pt_contains_str VARCHAR2(32000);
2501: l_denorm_str VARCHAR2(32000);
2502: l_ps_denorm_str VARCHAR2(32000);
2495:
2496: -- Strings to hold the generated Intermedia query strings
2497: l_party_contains_str VARCHAR2(32000);
2498: l_party_site_contains_str VARCHAR2(32000);
2499: l_contact_contains_str VARCHAR2(32000);
2500: l_contact_pt_contains_str VARCHAR2(32000);
2501: l_denorm_str VARCHAR2(32000);
2502: l_ps_denorm_str VARCHAR2(32000);
2503: l_ct_denorm_str VARCHAR2(32000);
2496: -- Strings to hold the generated Intermedia query strings
2497: l_party_contains_str VARCHAR2(32000);
2498: l_party_site_contains_str VARCHAR2(32000);
2499: l_contact_contains_str VARCHAR2(32000);
2500: l_contact_pt_contains_str VARCHAR2(32000);
2501: l_denorm_str VARCHAR2(32000);
2502: l_ps_denorm_str VARCHAR2(32000);
2503: l_ct_denorm_str VARCHAR2(32000);
2504: l_cpt_denorm_str VARCHAR2(32000);
2596: call_type(2) := 'OR';
2597: END;
2598:
2599: /** Private procedure to acquire and score at party level ***/
2600: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2601: l_party_id_idx NUMBER:=1;
2602: l_ctx_id NUMBER;
2603: l_precalc_score BOOLEAN := FALSE;
2604: l_TX35_new varchar2(4000);
2628: l_search_ctx_id,H_PARTY_ID(I));
2629: l_party_id_idx := H_PARTY_ID.COUNT+1;
2630: ELSE
2631: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2632: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2633: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2634: hz_utility_v2pub.debug(p_message=>'In eval party level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2635: END IF;
2636: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' AND p_dup_batch_id IS NULL THEN
2636: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' AND p_dup_batch_id IS NULL THEN
2637: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2638: hz_utility_v2pub.debug(p_message=>'In eval party level resolution options is set to SQL search.',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2639: END IF;
2640: IF (l_party_site_contains_str IS NULL AND
2641: l_contact_contains_str IS NULL AND
2642: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2643: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,p_search_merged,p_dup_party_id, x_num_matches);
2644: RAISE L_RETURN_IMM_EXC;
2637: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2638: hz_utility_v2pub.debug(p_message=>'In eval party level resolution options is set to SQL search.',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2639: END IF;
2640: IF (l_party_site_contains_str IS NULL AND
2641: l_contact_contains_str IS NULL AND
2642: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2643: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,p_search_merged,p_dup_party_id, x_num_matches);
2644: RAISE L_RETURN_IMM_EXC;
2645: ELSE
2638: hz_utility_v2pub.debug(p_message=>'In eval party level resolution options is set to SQL search.',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2639: END IF;
2640: IF (l_party_site_contains_str IS NULL AND
2641: l_contact_contains_str IS NULL AND
2642: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2643: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,p_search_merged,p_dup_party_id, x_num_matches);
2644: RAISE L_RETURN_IMM_EXC;
2645: ELSE
2646: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2642: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2643: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,p_search_merged,p_dup_party_id, x_num_matches);
2644: RAISE L_RETURN_IMM_EXC;
2645: ELSE
2646: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2647: END IF;
2648: ELSE
2649: IF p_index>1 THEN
2650: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2665: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2666: hz_utility_v2pub.debug(p_message=>'========== START LOOPING THROUGH WHAT IS RETURNED BY OPEN_PARTY_CURSOR ================',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2667: END IF;
2668: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2669: open_party_cursor(p_dup_party_id, p_restrict_sql, p_party_contains_str,l_ctx_id, l_match_str,p_search_merged,l_party_cur);
2670: END IF;
2671: LOOP
2672: FETCH l_party_cur INTO
2673: l_party_id , l_TX40, l_TX39;
2687: l_party_id_idx:= l_party_id_idx+1;
2688: END IF;
2689: IF (l_party_id_idx-1)>l_max_thresh THEN
2690: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2691: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2692: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2693: AND l_contact_pt_contains_str IS NULL THEN
2694: EXIT;
2695: END IF;
2689: IF (l_party_id_idx-1)>l_max_thresh THEN
2690: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2691: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2692: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2693: AND l_contact_pt_contains_str IS NULL THEN
2694: EXIT;
2695: END IF;
2696: CLOSE l_party_cur;
2697: IF p_index>1 THEN
2718: ROLLBACK to eval_start;
2719: END;
2720:
2721: /** Private procedure to acquire and score at party site level ***/
2722: PROCEDURE eval_party_site_level(p_party_site_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
2723: l_party_id_idx NUMBER:=1;
2724: l_ctx_id NUMBER;
2725: h_ps_id HZ_PARTY_SEARCH.IDList;
2726: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2730: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2731: hz_utility_v2pub.debug(p_message=>'-----------------',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2732: hz_utility_v2pub.debug(p_message=>'calling the procedure eval_party_site_level',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2733: END IF;
2734: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2735: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2736: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2737: l_party_id := H_SCORES.FIRST;
2738: WHILE l_party_id IS NOT NULL LOOP
2731: hz_utility_v2pub.debug(p_message=>'-----------------',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2732: hz_utility_v2pub.debug(p_message=>'calling the procedure eval_party_site_level',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2733: END IF;
2734: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2735: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2736: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2737: l_party_id := H_SCORES.FIRST;
2738: WHILE l_party_id IS NOT NULL LOOP
2739: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2732: hz_utility_v2pub.debug(p_message=>'calling the procedure eval_party_site_level',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2733: END IF;
2734: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2735: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2736: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2737: l_party_id := H_SCORES.FIRST;
2738: WHILE l_party_id IS NOT NULL LOOP
2739: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2740: l_party_id:=H_SCORES.NEXT(l_party_id);
2762: l_search_ctx_id,H_PARTY_ID(I));
2763: l_party_id_idx := H_PARTY_ID.COUNT+1;
2764: ELSE
2765: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2766: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2767: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2768: hz_utility_v2pub.debug(p_message=>'In eval party site level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2769: END IF;
2770: IF p_index>1 THEN
2784: END IF;
2785: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2786: hz_utility_v2pub.debug(p_message=>'========== START LOOPING THROUGH WHAT IS RETURNED BY OPEN_PARTY_SITE_CURSOR ================',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2787: END IF;
2788: open_party_site_cursor(p_dup_party_id,NULL, p_restrict_sql, p_party_site_contains_str,l_ctx_id, p_search_merged, 'N','N',l_party_site_cur);
2789: LOOP
2790: FETCH l_party_site_cur INTO
2791: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX22;
2792: EXIT WHEN l_party_site_cur%NOTFOUND;
2840: END IF;
2841: END;
2842:
2843: /** Private procedure to acquire and score at contact point level ***/
2844: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
2845: l_party_id_idx NUMBER:=1;
2846: l_ctx_id NUMBER;
2847: h_ct_id HZ_PARTY_SEARCH.IDList;
2848: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2874: l_search_ctx_id,H_PARTY_ID(I));
2875: l_party_id_idx := H_PARTY_ID.COUNT+1;
2876: ELSE
2877: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2878: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2879: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2880: hz_utility_v2pub.debug(p_message=>'In eval contact level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2881: END IF;
2882: IF p_index>1 THEN
2896: END IF;
2897: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2898: hz_utility_v2pub.debug(p_message=>'========== START LOOPING THROUGH WHAT IS RETURNED BY OPEN_CONTACT_CURSOR ================',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2899: END IF;
2900: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2901: LOOP
2902: FETCH l_contact_cur INTO
2903: l_org_contact_id, l_ct_party_id , l_TX22;
2904: EXIT WHEN l_contact_cur%NOTFOUND;
2949: END IF;
2950: END;
2951:
2952: /** Private procedure to acquire and score at contact point level ***/
2953: PROCEDURE eval_cpt_level(p_contact_pt_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER, p_ins_details VARCHAR2,p_emax_score NUMBER) IS
2954: l_party_id_idx NUMBER:=1;
2955: l_ctx_id NUMBER;
2956: h_cpt_id HZ_PARTY_SEARCH.IDList;
2957: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2985: l_search_ctx_id,H_PARTY_ID(I));
2986: l_party_id_idx := H_PARTY_ID.COUNT+1;
2987: ELSE
2988: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2989: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2990: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2991: hz_utility_v2pub.debug(p_message=>'In eval contact point level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2992: END IF;
2993: IF p_index>1 THEN
3007: END IF;
3008: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3009: hz_utility_v2pub.debug(p_message=>'========== START LOOPING THROUGH WHAT IS RETURNED BY OPEN_CONTACT_PT_CURSOR ================',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3010: END IF;
3011: open_contact_pt_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_pt_contains_str,l_ctx_id, p_search_merged, 'N', 'N',l_contact_pt_cur);
3012: LOOP
3013: FETCH l_contact_pt_cur INTO
3014: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
3015: EXIT WHEN l_contact_pt_cur%NOTFOUND;
3188: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3189: l_threshold := round(( l_entered_max_score / 340) * 269);
3190: other_acq_criteria_exists := TRUE ;
3191: --check if acquisition criteria exists for any other entity
3192: IF l_party_contains_str IS NOT NULL THEN
3193: acq_cnt := 1;
3194: END IF;
3195: IF l_party_site_contains_str IS NOT NULL THEN
3196: acq_cnt := acq_cnt+1;
3191: --check if acquisition criteria exists for any other entity
3192: IF l_party_contains_str IS NOT NULL THEN
3193: acq_cnt := 1;
3194: END IF;
3195: IF l_party_site_contains_str IS NOT NULL THEN
3196: acq_cnt := acq_cnt+1;
3197: END IF;
3198: IF l_contact_contains_str IS NOT NULL THEN
3199: acq_cnt := acq_cnt+1;
3194: END IF;
3195: IF l_party_site_contains_str IS NOT NULL THEN
3196: acq_cnt := acq_cnt+1;
3197: END IF;
3198: IF l_contact_contains_str IS NOT NULL THEN
3199: acq_cnt := acq_cnt+1;
3200: END IF;
3201: IF l_contact_pt_contains_str IS NOT NULL THEN
3202: acq_cnt := acq_cnt+1;
3197: END IF;
3198: IF l_contact_contains_str IS NOT NULL THEN
3199: acq_cnt := acq_cnt+1;
3200: END IF;
3201: IF l_contact_pt_contains_str IS NOT NULL THEN
3202: acq_cnt := acq_cnt+1;
3203: END IF;
3204: IF acq_cnt>1 THEN
3205: other_acq_criteria_exists := TRUE;
3231: FND_MSG_PUB.ADD;
3232: RAISE FND_API.G_EXC_ERROR;
3233: END IF;
3234: END IF;
3235: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3236: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3237: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3238: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3239: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3232: RAISE FND_API.G_EXC_ERROR;
3233: END IF;
3234: END IF;
3235: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3236: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3237: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3238: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3239: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3240: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3233: END IF;
3234: END IF;
3235: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3236: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3237: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3238: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3239: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3240: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3241: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3234: END IF;
3235: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3236: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3237: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3238: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3239: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3240: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3241: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3242: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3235: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3236: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3237: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3238: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3239: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3240: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3241: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3242: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3243: END IF;
3236: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3237: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3238: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3239: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3240: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3241: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3242: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3243: END IF;
3244: END;
3237: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3238: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3239: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3240: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3241: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3242: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3243: END IF;
3244: END;
3245: /************ End of find_parties private procedures **********/
3238: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3239: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3240: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3241: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3242: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3243: END IF;
3244: END;
3245: /************ End of find_parties private procedures **********/
3246:
3310: hz_utility_v2pub.debug(p_message=>'Maximum records that will be evaluated is '||l_max_thresh,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3311: END IF;
3312: -- ************************************************************
3313: -- STEP 2. Setup of intermedia query strings for Acquisition query
3314: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3315: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3316: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3317: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3318: hz_utility_v2pub.debug(p_message=>'Commencing the DENORM LOGIC in find_parties',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3311: END IF;
3312: -- ************************************************************
3313: -- STEP 2. Setup of intermedia query strings for Acquisition query
3314: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3315: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3316: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3317: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3318: hz_utility_v2pub.debug(p_message=>'Commencing the DENORM LOGIC in find_parties',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3319: END IF;
3312: -- ************************************************************
3313: -- STEP 2. Setup of intermedia query strings for Acquisition query
3314: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3315: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3316: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3317: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3318: hz_utility_v2pub.debug(p_message=>'Commencing the DENORM LOGIC in find_parties',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3319: END IF;
3320: l_denorm_max_score:=0;
3344: END IF;
3345: ELSE
3346: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3347: END IF;
3348: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, l_denorm_str, l_party_max_score, l_denorm_max_score, l_non_denorm_max_score, round((269/340)*l_entered_max_score));
3349: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3350:
3351: -- Setup Search Context ID
3352: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3354:
3355: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3356: hz_utility_v2pub.debug(p_message=>'Search context id in find_parties is '||x_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3357: END IF;
3358: IF l_party_contains_str IS NULL THEN
3359: defpt := 1;
3360: END IF;
3361: IF l_party_site_contains_str IS NULL THEN
3362: defps := 1;
3357: END IF;
3358: IF l_party_contains_str IS NULL THEN
3359: defpt := 1;
3360: END IF;
3361: IF l_party_site_contains_str IS NULL THEN
3362: defps := 1;
3363: END IF;
3364: IF l_contact_contains_str IS NULL THEN
3365: defct := 1;
3360: END IF;
3361: IF l_party_site_contains_str IS NULL THEN
3362: defps := 1;
3363: END IF;
3364: IF l_contact_contains_str IS NULL THEN
3365: defct := 1;
3366: END IF;
3367: IF l_contact_pt_contains_str IS NULL THEN
3368: defcpt := 1;
3363: END IF;
3364: IF l_contact_contains_str IS NULL THEN
3365: defct := 1;
3366: END IF;
3367: IF l_contact_pt_contains_str IS NULL THEN
3368: defcpt := 1;
3369: END IF;
3370:
3371: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3372: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3373: hz_utility_v2pub.debug(p_message=>'------------------------',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3374: END IF;
3375: hz_utility_v2pub.debug(p_message=>'In find_parties l_match_str = '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3376: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3377: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_site_contains_str = '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3378: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_contains_str = '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3379: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_pt_contains_str = '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3380: hz_utility_v2pub.debug(p_message=>'In find_parties l_search_ctx_id = '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3373: hz_utility_v2pub.debug(p_message=>'------------------------',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3374: END IF;
3375: hz_utility_v2pub.debug(p_message=>'In find_parties l_match_str = '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3376: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3377: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_site_contains_str = '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3378: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_contains_str = '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3379: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_pt_contains_str = '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3380: hz_utility_v2pub.debug(p_message=>'In find_parties l_search_ctx_id = '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3381: END IF;
3374: END IF;
3375: hz_utility_v2pub.debug(p_message=>'In find_parties l_match_str = '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3376: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3377: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_site_contains_str = '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3378: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_contains_str = '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3379: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_pt_contains_str = '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3380: hz_utility_v2pub.debug(p_message=>'In find_parties l_search_ctx_id = '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3381: END IF;
3382: FOR I in 1..5 LOOP
3375: hz_utility_v2pub.debug(p_message=>'In find_parties l_match_str = '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3376: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3377: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_site_contains_str = '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3378: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_contains_str = '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3379: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_pt_contains_str = '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3380: hz_utility_v2pub.debug(p_message=>'In find_parties l_search_ctx_id = '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3381: END IF;
3382: FOR I in 1..5 LOOP
3383: do_eval(I);
3457: x_num_matches OUT NUMBER
3458: ) IS
3459:
3460: -- Strings to hold the generated Intermedia query strings
3461: l_party_contains_str VARCHAR2(32000);
3462: l_party_site_contains_str VARCHAR2(32000);
3463: l_contact_contains_str VARCHAR2(32000);
3464: l_contact_pt_contains_str VARCHAR2(32000);
3465: l_denorm_str VARCHAR2(32000);
3458: ) IS
3459:
3460: -- Strings to hold the generated Intermedia query strings
3461: l_party_contains_str VARCHAR2(32000);
3462: l_party_site_contains_str VARCHAR2(32000);
3463: l_contact_contains_str VARCHAR2(32000);
3464: l_contact_pt_contains_str VARCHAR2(32000);
3465: l_denorm_str VARCHAR2(32000);
3466: l_ps_denorm_str VARCHAR2(32000);
3459:
3460: -- Strings to hold the generated Intermedia query strings
3461: l_party_contains_str VARCHAR2(32000);
3462: l_party_site_contains_str VARCHAR2(32000);
3463: l_contact_contains_str VARCHAR2(32000);
3464: l_contact_pt_contains_str VARCHAR2(32000);
3465: l_denorm_str VARCHAR2(32000);
3466: l_ps_denorm_str VARCHAR2(32000);
3467: l_ct_denorm_str VARCHAR2(32000);
3460: -- Strings to hold the generated Intermedia query strings
3461: l_party_contains_str VARCHAR2(32000);
3462: l_party_site_contains_str VARCHAR2(32000);
3463: l_contact_contains_str VARCHAR2(32000);
3464: l_contact_pt_contains_str VARCHAR2(32000);
3465: l_denorm_str VARCHAR2(32000);
3466: l_ps_denorm_str VARCHAR2(32000);
3467: l_ct_denorm_str VARCHAR2(32000);
3468: l_cpt_denorm_str VARCHAR2(32000);
3590: call_type(2) := 'OR';
3591: END;
3592:
3593: /** Private procedure to acquire and score at party level ***/
3594: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3595: l_party_id_idx NUMBER:=1;
3596: l_ctx_id NUMBER;
3597: l_TX35_new varchar2(4000);
3598: BEGIN
3612: l_search_ctx_id,H_PARTY_ID(I));
3613: l_party_id_idx := H_PARTY_ID.COUNT+1;
3614: ELSE
3615: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3616: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3617: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3618: hz_utility_v2pub.debug(p_message=>'In eval party level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3619: END IF;
3620: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3620: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3621: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3622: hz_utility_v2pub.debug(p_message=>'In eval party level resolution option is set to SQL search.',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3623: END IF;
3624: IF (l_party_site_contains_str IS NULL AND
3625: l_contact_contains_str IS NULL AND
3626: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3627: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3628: RAISE L_RETURN_IMM_EXC;
3621: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3622: hz_utility_v2pub.debug(p_message=>'In eval party level resolution option is set to SQL search.',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3623: END IF;
3624: IF (l_party_site_contains_str IS NULL AND
3625: l_contact_contains_str IS NULL AND
3626: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3627: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3628: RAISE L_RETURN_IMM_EXC;
3629: ELSE
3622: hz_utility_v2pub.debug(p_message=>'In eval party level resolution option is set to SQL search.',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3623: END IF;
3624: IF (l_party_site_contains_str IS NULL AND
3625: l_contact_contains_str IS NULL AND
3626: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3627: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3628: RAISE L_RETURN_IMM_EXC;
3629: ELSE
3630: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3626: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3627: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3628: RAISE L_RETURN_IMM_EXC;
3629: ELSE
3630: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3631: END IF;
3632: ELSE
3633: IF p_index>1 THEN
3634: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3646: l_ctx_id := NULL;
3647: l_party_id_idx := H_PARTY_ID.COUNT+1;
3648: END IF;
3649: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3650: open_party_cursor(p_dup_party_id, p_restrict_sql, p_party_contains_str,l_ctx_id, l_match_str,p_search_merged,l_party_cur);
3651: END IF;
3652: LOOP
3653: FETCH l_party_cur INTO
3654: l_party_id , l_TX40, l_TX39;
3668: l_party_id_idx:= l_party_id_idx+1;
3669: END IF;
3670: IF (l_party_id_idx-1)>l_max_thresh THEN
3671: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3672: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3673: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3674: AND l_contact_pt_contains_str IS NULL THEN
3675: H_PARTY_ID.DELETE(l_party_id_idx-1);
3676: H_PARTY_ID_LIST.DELETE(l_index);
3670: IF (l_party_id_idx-1)>l_max_thresh THEN
3671: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3672: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3673: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3674: AND l_contact_pt_contains_str IS NULL THEN
3675: H_PARTY_ID.DELETE(l_party_id_idx-1);
3676: H_PARTY_ID_LIST.DELETE(l_index);
3677: H_SCORES.DELETE(l_index);
3678: EXIT;
3699: CLOSE l_party_cur;
3700: ROLLBACK to eval_start;
3701: END;
3702: PROCEDURE open_person_contact_cursor(
3703: p_contains_str VARCHAR2,
3704: p_search_ctx_id NUMBER,
3705: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3706: BEGIN
3707: OPEN x_cursor FOR
3706: BEGIN
3707: OPEN x_cursor FOR
3708: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID, TX22
3709: FROM HZ_STAGED_CONTACTS stage
3710: WHERE contains( concat_col, p_contains_str)>0
3711: AND ORG_CONTACT_ID in (
3712: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3713: org_contact_id
3714: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc
3730: end if;
3731: END;
3732:
3733: /** Private procedure to acquire and score at party site level ***/
3734: PROCEDURE eval_party_site_level(p_party_site_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3735: l_party_id_idx NUMBER:=1;
3736: l_ctx_id NUMBER;
3737: h_ps_id HZ_PARTY_SEARCH.IDList;
3738: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3758: ELSE
3759: l_party_id_idx := H_PARTY_ID.COUNT+1;
3760: l_ctx_id := NULL;
3761: END IF;
3762: open_party_site_cursor(p_dup_party_id,NULL, p_restrict_sql, p_party_site_contains_str,l_ctx_id, p_search_merged,'N', 'Y',l_party_site_cur);
3763: LOOP
3764: FETCH l_party_site_cur INTO
3765: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX22;
3766: EXIT WHEN l_party_site_cur%NOTFOUND;
3829: END IF;
3830: END;
3831:
3832: /** Private procedure to acquire and score at party site level ***/
3833: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3834: l_party_id_idx NUMBER:=1;
3835: l_ctx_id NUMBER;
3836: h_ct_id HZ_PARTY_SEARCH.IDList;
3837: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3845: FORALL I in 1..H_PARTY_ID.COUNT
3846: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3847: l_search_ctx_id,H_PARTY_ID(I));
3848: l_party_id_idx := H_PARTY_ID.COUNT+1;
3849: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3850: LOOP
3851: FETCH l_contact_cur INTO
3852: l_org_contact_id, l_ct_party_id , l_TX22;
3853: EXIT WHEN l_contact_cur%NOTFOUND;
3887: END IF;
3888: END;
3889:
3890: /** Private procedure to acquire and score at contact point level ***/
3891: PROCEDURE eval_cpt_level(p_contact_pt_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER, p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3892: l_party_id_idx NUMBER:=1;
3893: l_ctx_id NUMBER;
3894: h_cpt_id HZ_PARTY_SEARCH.IDList;
3895: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3932: END IF;
3933: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3934: hz_utility_v2pub.debug(p_message=>'====== START LOOPING THROUGH WHAT IS RETURNED BY OPEN_CONTACT_PT_CURSOR =======',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3935: END IF;
3936: open_contact_pt_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_pt_contains_str,l_ctx_id, p_search_merged,'N', 'Y',l_contact_pt_cur);
3937: LOOP
3938: FETCH l_contact_pt_cur INTO
3939: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
3940: EXIT WHEN l_contact_pt_cur%NOTFOUND;
4135: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
4136: l_threshold := round(( l_entered_max_score / 340) * 269);
4137: other_acq_criteria_exists := TRUE ;
4138: --check if acquisition criteria exists for any other entity
4139: IF l_party_contains_str IS NOT NULL THEN
4140: acq_cnt := 1;
4141: END IF;
4142: IF l_party_site_contains_str IS NOT NULL THEN
4143: acq_cnt := acq_cnt+1;
4138: --check if acquisition criteria exists for any other entity
4139: IF l_party_contains_str IS NOT NULL THEN
4140: acq_cnt := 1;
4141: END IF;
4142: IF l_party_site_contains_str IS NOT NULL THEN
4143: acq_cnt := acq_cnt+1;
4144: END IF;
4145: IF l_contact_contains_str IS NOT NULL THEN
4146: acq_cnt := acq_cnt+1;
4141: END IF;
4142: IF l_party_site_contains_str IS NOT NULL THEN
4143: acq_cnt := acq_cnt+1;
4144: END IF;
4145: IF l_contact_contains_str IS NOT NULL THEN
4146: acq_cnt := acq_cnt+1;
4147: END IF;
4148: IF l_contact_pt_contains_str IS NOT NULL THEN
4149: acq_cnt := acq_cnt+1;
4144: END IF;
4145: IF l_contact_contains_str IS NOT NULL THEN
4146: acq_cnt := acq_cnt+1;
4147: END IF;
4148: IF l_contact_pt_contains_str IS NOT NULL THEN
4149: acq_cnt := acq_cnt+1;
4150: END IF;
4151: IF acq_cnt>1 THEN
4152: other_acq_criteria_exists := TRUE;
4178: FND_MSG_PUB.ADD;
4179: RAISE FND_API.G_EXC_ERROR;
4180: END IF;
4181: END IF;
4182: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4183: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4184: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4185: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4186: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4179: RAISE FND_API.G_EXC_ERROR;
4180: END IF;
4181: END IF;
4182: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4183: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4184: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4185: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4186: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4187: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4180: END IF;
4181: END IF;
4182: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4183: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4184: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4185: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4186: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4187: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4188: END IF;
4181: END IF;
4182: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4183: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4184: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4185: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4186: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4187: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4188: END IF;
4189: END;
4182: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4183: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4184: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4185: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4186: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4187: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4188: END IF;
4189: END;
4190: /************ End of find_persons private procedures **********/
4183: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4184: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4185: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4186: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4187: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4188: END IF;
4189: END;
4190: /************ End of find_persons private procedures **********/
4191:
4218: g_score_until_thresh := false;
4219: END IF;
4220: -- ************************************************************
4221: -- STEP 2. Setup of intermedia query strings for Acquisition query
4222: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4223: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4224: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4225: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4226: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4219: END IF;
4220: -- ************************************************************
4221: -- STEP 2. Setup of intermedia query strings for Acquisition query
4222: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4223: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4224: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4225: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4226: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4227:
4220: -- ************************************************************
4221: -- STEP 2. Setup of intermedia query strings for Acquisition query
4222: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4223: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4224: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4225: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4226: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4227:
4228: -- Setup Search Context ID
4221: -- STEP 2. Setup of intermedia query strings for Acquisition query
4222: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4223: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4224: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4225: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4226: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4227:
4228: -- Setup Search Context ID
4229: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4228: -- Setup Search Context ID
4229: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4230: x_search_ctx_id := l_search_ctx_id;
4231:
4232: IF l_party_contains_str IS NULL THEN
4233: defpt := 1;
4234: END IF;
4235: IF l_party_site_contains_str IS NULL THEN
4236: defps := 1;
4231:
4232: IF l_party_contains_str IS NULL THEN
4233: defpt := 1;
4234: END IF;
4235: IF l_party_site_contains_str IS NULL THEN
4236: defps := 1;
4237: END IF;
4238: IF l_contact_contains_str IS NULL THEN
4239: defct := 1;
4234: END IF;
4235: IF l_party_site_contains_str IS NULL THEN
4236: defps := 1;
4237: END IF;
4238: IF l_contact_contains_str IS NULL THEN
4239: defct := 1;
4240: END IF;
4241: IF l_contact_pt_contains_str IS NULL THEN
4242: defcpt := 1;
4237: END IF;
4238: IF l_contact_contains_str IS NULL THEN
4239: defct := 1;
4240: END IF;
4241: IF l_contact_pt_contains_str IS NULL THEN
4242: defcpt := 1;
4243: END IF;
4244:
4245: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4243: END IF;
4244:
4245: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4246: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4247: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4248: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4249: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4250: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4251: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4244:
4245: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4246: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4247: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4248: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4249: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4250: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4251: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4252: END IF;
4245: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4246: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4247: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4248: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4249: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4250: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4251: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4252: END IF;
4253: FOR I in 1..5 LOOP
4246: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4247: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4248: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4249: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4250: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4251: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4252: END IF;
4253: FOR I in 1..5 LOOP
4254: do_eval(I);
4252: END IF;
4253: FOR I in 1..5 LOOP
4254: do_eval(I);
4255: END LOOP;
4256: IF l_contact_contains_str IS NOT NULL THEN
4257: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4258: END IF;
4259: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4260: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 79 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4253: FOR I in 1..5 LOOP
4254: do_eval(I);
4255: END LOOP;
4256: IF l_contact_contains_str IS NOT NULL THEN
4257: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4258: END IF;
4259: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4260: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 79 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4261: END IF;
5083: x_num_matches OUT NUMBER
5084: ) IS
5085:
5086: -- Strings to hold the generated Intermedia query strings
5087: l_party_contains_str VARCHAR2(32000);
5088: l_match_idx NUMBER;
5089: l_party_site_contains_str VARCHAR2(32000);
5090: l_contact_contains_str VARCHAR2(32000);
5091: l_contact_pt_contains_str VARCHAR2(32000);
5085:
5086: -- Strings to hold the generated Intermedia query strings
5087: l_party_contains_str VARCHAR2(32000);
5088: l_match_idx NUMBER;
5089: l_party_site_contains_str VARCHAR2(32000);
5090: l_contact_contains_str VARCHAR2(32000);
5091: l_contact_pt_contains_str VARCHAR2(32000);
5092: l_tmp VARCHAR2(32000);
5093:
5086: -- Strings to hold the generated Intermedia query strings
5087: l_party_contains_str VARCHAR2(32000);
5088: l_match_idx NUMBER;
5089: l_party_site_contains_str VARCHAR2(32000);
5090: l_contact_contains_str VARCHAR2(32000);
5091: l_contact_pt_contains_str VARCHAR2(32000);
5092: l_tmp VARCHAR2(32000);
5093:
5094: -- Other local variables
5087: l_party_contains_str VARCHAR2(32000);
5088: l_match_idx NUMBER;
5089: l_party_site_contains_str VARCHAR2(32000);
5090: l_contact_contains_str VARCHAR2(32000);
5091: l_contact_pt_contains_str VARCHAR2(32000);
5092: l_tmp VARCHAR2(32000);
5093:
5094: -- Other local variables
5095: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5173: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5174:
5175: -- ************************************************************
5176: -- STEP 2. Setup of intermedia query strings for Acquisition query
5177: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5178: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5179: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,p_party_site_list,HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,p_contact_point_list);
5180:
5181: -- Setup Search Context ID
5174:
5175: -- ************************************************************
5176: -- STEP 2. Setup of intermedia query strings for Acquisition query
5177: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5178: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5179: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,p_party_site_list,HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,p_contact_point_list);
5180:
5181: -- Setup Search Context ID
5182: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5183: x_search_ctx_id := l_search_ctx_id;
5184:
5185: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5186: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5187: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5188: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5189: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5190: END IF;
5191: IF l_party_site_contains_str IS NULL THEN
5184:
5185: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5186: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5187: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5188: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5189: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5190: END IF;
5191: IF l_party_site_contains_str IS NULL THEN
5192: defps := 1;
5187: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5188: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5189: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5190: END IF;
5191: IF l_party_site_contains_str IS NULL THEN
5192: defps := 1;
5193: END IF;
5194: IF l_contact_pt_contains_str IS NULL THEN
5195: defcpt := 1;
5190: END IF;
5191: IF l_party_site_contains_str IS NULL THEN
5192: defps := 1;
5193: END IF;
5194: IF l_contact_pt_contains_str IS NULL THEN
5195: defcpt := 1;
5196: END IF;
5197:
5198: IF l_party_site_contains_str IS NOT NULL THEN
5194: IF l_contact_pt_contains_str IS NULL THEN
5195: defcpt := 1;
5196: END IF;
5197:
5198: IF l_party_site_contains_str IS NOT NULL THEN
5199: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5200: LOOP
5201: FETCH l_party_site_cur INTO
5202: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX22;
5195: defcpt := 1;
5196: END IF;
5197:
5198: IF l_party_site_contains_str IS NOT NULL THEN
5199: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5200: LOOP
5201: FETCH l_party_site_cur INTO
5202: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX22;
5203: EXIT WHEN l_party_site_cur%NOTFOUND;
5218: END LOOP;
5219: CLOSE l_party_site_cur;
5220: END IF;
5221:
5222: IF l_contact_pt_contains_str IS NOT NULL THEN
5223: SAVEPOINT eval_start;
5224: IF l_match_str = ' AND ' OR (
5225: ((l_cpt_max_score/l_entered_max_score)<(269/340))
5226: ) THEN
5226: ) THEN
5227: FORALL I in 1..l_match_ps_list.COUNT
5228: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5229: values (l_search_ctx_id,l_match_ps_list(I));
5230: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'PARTY_SITES');
5231: ELSE
5232: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5233: END IF;
5234: LOOP
5228: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5229: values (l_search_ctx_id,l_match_ps_list(I));
5230: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'PARTY_SITES');
5231: ELSE
5232: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5233: END IF;
5234: LOOP
5235: FETCH l_contact_pt_cur INTO
5236: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
5328: x_num_matches OUT NUMBER
5329: ) IS
5330:
5331: -- Strings to hold the generated Intermedia query strings
5332: l_party_contains_str VARCHAR2(32000);
5333: l_party_site_contains_str VARCHAR2(32000);
5334: l_contact_contains_str VARCHAR2(32000);
5335: l_contact_pt_contains_str VARCHAR2(32000);
5336: l_tmp VARCHAR2(32000);
5329: ) IS
5330:
5331: -- Strings to hold the generated Intermedia query strings
5332: l_party_contains_str VARCHAR2(32000);
5333: l_party_site_contains_str VARCHAR2(32000);
5334: l_contact_contains_str VARCHAR2(32000);
5335: l_contact_pt_contains_str VARCHAR2(32000);
5336: l_tmp VARCHAR2(32000);
5337:
5330:
5331: -- Strings to hold the generated Intermedia query strings
5332: l_party_contains_str VARCHAR2(32000);
5333: l_party_site_contains_str VARCHAR2(32000);
5334: l_contact_contains_str VARCHAR2(32000);
5335: l_contact_pt_contains_str VARCHAR2(32000);
5336: l_tmp VARCHAR2(32000);
5337:
5338: -- Other local variables
5331: -- Strings to hold the generated Intermedia query strings
5332: l_party_contains_str VARCHAR2(32000);
5333: l_party_site_contains_str VARCHAR2(32000);
5334: l_contact_contains_str VARCHAR2(32000);
5335: l_contact_pt_contains_str VARCHAR2(32000);
5336: l_tmp VARCHAR2(32000);
5337:
5338: -- Other local variables
5339: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5406: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5407:
5408: -- ************************************************************
5409: -- STEP 2. Setup of intermedia query strings for Acquisition query
5410: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5411: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5412: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,p_contact_list,p_contact_point_list);
5413:
5414: -- Setup Search Context ID
5407:
5408: -- ************************************************************
5409: -- STEP 2. Setup of intermedia query strings for Acquisition query
5410: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5411: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5412: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,p_contact_list,p_contact_point_list);
5413:
5414: -- Setup Search Context ID
5415: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5416: x_search_ctx_id := l_search_ctx_id;
5417:
5418: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5419: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5420: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5421: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5422: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5423: END IF;
5424: IF l_contact_contains_str IS NULL THEN
5417:
5418: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5419: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5420: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5421: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5422: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5423: END IF;
5424: IF l_contact_contains_str IS NULL THEN
5425: defct := 1;
5420: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5421: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5422: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5423: END IF;
5424: IF l_contact_contains_str IS NULL THEN
5425: defct := 1;
5426: END IF;
5427: IF l_contact_pt_contains_str IS NULL THEN
5428: defcpt := 1;
5423: END IF;
5424: IF l_contact_contains_str IS NULL THEN
5425: defct := 1;
5426: END IF;
5427: IF l_contact_pt_contains_str IS NULL THEN
5428: defcpt := 1;
5429: END IF;
5430:
5431: IF l_contact_contains_str IS NOT NULL THEN
5427: IF l_contact_pt_contains_str IS NULL THEN
5428: defcpt := 1;
5429: END IF;
5430:
5431: IF l_contact_contains_str IS NOT NULL THEN
5432: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5433: LOOP
5434: FETCH l_contact_cur INTO
5435: l_org_contact_id, l_ct_party_id , l_TX22;
5428: defcpt := 1;
5429: END IF;
5430:
5431: IF l_contact_contains_str IS NOT NULL THEN
5432: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5433: LOOP
5434: FETCH l_contact_cur INTO
5435: l_org_contact_id, l_ct_party_id , l_TX22;
5436: EXIT WHEN l_contact_cur%NOTFOUND;
5449: END LOOP;
5450: CLOSE l_contact_cur;
5451: END IF;
5452:
5453: IF l_contact_pt_contains_str IS NOT NULL THEN
5454: SAVEPOINT eval_start;
5455: IF l_match_str = ' AND ' OR (
5456: ((l_cpt_max_score/l_entered_max_score)<(269/340))
5457: ) THEN
5457: ) THEN
5458: FORALL I in 1..l_match_contact_list.COUNT
5459: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5460: values (l_search_ctx_id,l_match_contact_list(I));
5461: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'CONTACTS');
5462: ELSE
5463: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5464: END IF;
5465: LOOP
5459: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5460: values (l_search_ctx_id,l_match_contact_list(I));
5461: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'CONTACTS');
5462: ELSE
5463: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5464: END IF;
5465: LOOP
5466: FETCH l_contact_pt_cur INTO
5467: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
5557: ) IS
5558:
5559:
5560: -- Strings to hold the generated Intermedia query strings
5561: l_contact_pt_contains_str VARCHAR2(32000);
5562: l_tmp VARCHAR2(32000);
5563:
5564: -- Other local variables
5565: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5618: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5619:
5620: -- ************************************************************
5621: -- STEP 2. Setup of intermedia query strings for Acquisition query
5622: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5623: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,p_contact_point_list);
5624:
5625: -- Setup Search Context ID
5626: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5626: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5627: x_search_ctx_id := l_search_ctx_id;
5628: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5629: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5630: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5631: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5632: END IF;
5633:
5634: IF l_contact_pt_contains_str IS NOT NULL THEN
5630: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5631: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5632: END IF;
5633:
5634: IF l_contact_pt_contains_str IS NOT NULL THEN
5635: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5636: cnt := 1;
5637: LOOP
5638: FETCH l_contact_pt_cur INTO
5631: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5632: END IF;
5633:
5634: IF l_contact_pt_contains_str IS NOT NULL THEN
5635: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5636: cnt := 1;
5637: LOOP
5638: FETCH l_contact_pt_cur INTO
5639: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
5702: x_search_ctx_id IN OUT NUMBER
5703: ) IS
5704:
5705: -- Strings to hold the generated Intermedia query strings
5706: l_party_contains_str VARCHAR2(32000);
5707: l_party_site_contains_str VARCHAR2(32000);
5708: l_contact_contains_str VARCHAR2(32000);
5709: l_contact_pt_contains_str VARCHAR2(32000);
5710: l_tmp VARCHAR2(32000);
5703: ) IS
5704:
5705: -- Strings to hold the generated Intermedia query strings
5706: l_party_contains_str VARCHAR2(32000);
5707: l_party_site_contains_str VARCHAR2(32000);
5708: l_contact_contains_str VARCHAR2(32000);
5709: l_contact_pt_contains_str VARCHAR2(32000);
5710: l_tmp VARCHAR2(32000);
5711:
5704:
5705: -- Strings to hold the generated Intermedia query strings
5706: l_party_contains_str VARCHAR2(32000);
5707: l_party_site_contains_str VARCHAR2(32000);
5708: l_contact_contains_str VARCHAR2(32000);
5709: l_contact_pt_contains_str VARCHAR2(32000);
5710: l_tmp VARCHAR2(32000);
5711:
5712: -- Other local variables
5705: -- Strings to hold the generated Intermedia query strings
5706: l_party_contains_str VARCHAR2(32000);
5707: l_party_site_contains_str VARCHAR2(32000);
5708: l_contact_contains_str VARCHAR2(32000);
5709: l_contact_pt_contains_str VARCHAR2(32000);
5710: l_tmp VARCHAR2(32000);
5711:
5712: -- Other local variables
5713: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5793: g_score_until_thresh := false;
5794: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5795: -- ************************************************************
5796: -- STEP 2. Setup of intermedia query strings for Acquisition query
5797: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5798: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5799: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5800: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5801:
5794: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5795: -- ************************************************************
5796: -- STEP 2. Setup of intermedia query strings for Acquisition query
5797: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5798: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5799: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5800: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5801:
5802: -- Setup Search Context ID
5795: -- ************************************************************
5796: -- STEP 2. Setup of intermedia query strings for Acquisition query
5797: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5798: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5799: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5800: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5801:
5802: -- Setup Search Context ID
5803: IF x_search_ctx_id IS NULL THEN
5815: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX40,l_TX39,1);
5816: END LOOP;
5817: CLOSE l_party_cur;
5818:
5819: IF l_party_site_contains_str IS NOT NULL THEN
5820: l_max_score := 0;
5821: l_max_id := 0;
5822: l_max_idx := 0;
5823: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5820: l_max_score := 0;
5821: l_max_id := 0;
5822: l_max_idx := 0;
5823: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5824: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5825: ELSE
5826: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5827: END IF;
5828: LOOP
5822: l_max_idx := 0;
5823: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5824: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5825: ELSE
5826: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5827: END IF;
5828: LOOP
5829: FETCH l_party_site_cur INTO
5830: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX22;
5851: INSERT_PARTY_SITES_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_party_site_list(l_max_idx), g_party_site_stage_list(l_max_idx) ,l_max_TX3,l_max_TX4,l_max_TX9,l_max_TX10,l_max_TX11,l_max_TX12,l_max_TX13,l_max_TX14,l_max_TX15,l_max_TX22,l_max_idx);
5852: END IF;
5853: END IF;
5854:
5855: IF l_contact_contains_str IS NOT NULL THEN
5856: l_max_score := 0;
5857: l_max_id := 0;
5858: l_max_idx := 0;
5859: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5855: IF l_contact_contains_str IS NOT NULL THEN
5856: l_max_score := 0;
5857: l_max_id := 0;
5858: l_max_idx := 0;
5859: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5860: LOOP
5861: FETCH l_contact_cur INTO
5862: l_org_contact_id, l_ct_party_id , l_TX22;
5863: EXIT WHEN l_contact_cur%NOTFOUND;
5874: INSERT_CONTACTS_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_contact_list(l_max_idx), g_contact_stage_list(l_max_idx) ,l_max_TX22,l_max_idx);
5875: END IF;
5876: END IF;
5877:
5878: IF l_contact_pt_contains_str IS NOT NULL THEN
5879: l_max_score := 0;
5880: l_max_id := 0;
5881: l_max_idx := 0;
5882: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5879: l_max_score := 0;
5880: l_max_id := 0;
5881: l_max_idx := 0;
5882: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5883: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5884: ELSE
5885: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5886: END IF;
5887: LOOP
5881: l_max_idx := 0;
5882: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5883: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5884: ELSE
5885: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5886: END IF;
5887: LOOP
5888: FETCH l_contact_pt_cur INTO
5889: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;