[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 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)
1688: END IF;
1689: IF p_search_ctx_id IS NULL THEN
1690: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX40, TX39'||
1691: ' FROM HZ_STAGED_PARTIES stage'||
1692: ' WHERE contains( concat_col, :cont)>0'||
1693: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1694: ' AND ('||p_restrict_sql||')' ||
1695: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1696: IF l_search_merged = 'Y' THEN
1700: ELSE
1701: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1702: END IF;
1703: output_long_strings('----------------------------------------------------------');
1704: output_long_strings('Party Contains String = '||p_contains_str);
1705: output_long_strings('Restrict Sql = '||p_restrict_sql);
1706: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1707: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1708: ,p_dup_party_id, p_dup_party_id;
1702: END IF;
1703: output_long_strings('----------------------------------------------------------');
1704: output_long_strings('Party Contains String = '||p_contains_str);
1705: output_long_strings('Restrict Sql = '||p_restrict_sql);
1706: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1707: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1708: ,p_dup_party_id, p_dup_party_id;
1709: END IF;
1710: END IF;
1733: PROCEDURE open_party_site_cursor(
1734: p_dup_party_id NUMBER,
1735: p_party_id NUMBER,
1736: p_restrict_sql VARCHAR2,
1737: p_contains_str VARCHAR2,
1738: p_search_ctx_id NUMBER,
1739: p_search_merged VARCHAR2,
1740: p_search_rel_sites VARCHAR2,
1741: p_person_api VARCHAR2,
1765: IF p_search_rel_sites = 'N' THEN
1766: OPEN x_cursor FOR
1767: 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
1768: FROM HZ_STAGED_PARTY_SITES stage
1769: WHERE contains( concat_col, p_contains_str)>0
1770: AND( (l_search_merged ='Y' )
1771: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1772: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1773: AND stage.party_id = p_party_id;
1774: ELSE
1775: OPEN x_cursor FOR
1776: 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
1777: FROM HZ_STAGED_PARTY_SITES stage
1778: WHERE contains( concat_col, p_contains_str)>0
1779: AND( (l_search_merged ='Y' )
1780: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1781: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1782: AND stage.party_id = p_party_id
1782: AND stage.party_id = p_party_id
1783: UNION
1784: 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
1785: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1786: WHERE contains( concat_col, p_contains_str)>0
1787: AND( (l_search_merged ='Y' )
1788: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1789: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1790: AND r.object_id = p_party_id
1804: END IF;
1805: OPEN x_cursor FOR
1806: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
1807: FROM HZ_STAGED_PARTY_SITES stage
1808: WHERE contains( concat_col, p_contains_str)>0
1809: AND EXISTS (
1810: SELECT 1 FROM HZ_STAGED_PARTIES p
1811: WHERE p.PARTY_ID = stage.PARTY_ID
1812: AND( (l_search_merged ='Y' )
1824: IF p_person_api = 'Y' THEN
1825: OPEN x_cursor FOR
1826: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
1827: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1828: WHERE contains( concat_col, p_contains_str)>0
1829: AND( (l_search_merged ='Y' )
1830: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1831: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1832: AND d.search_context_id = p_search_ctx_id
1834: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1835: UNION
1836: 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
1837: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1838: WHERE contains( concat_col, p_contains_str)>0
1839: AND( (l_search_merged ='Y' )
1840: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1841: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1842: AND d.search_context_id = p_search_ctx_id
1847: ELSE
1848: OPEN x_cursor FOR
1849: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
1850: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1851: WHERE contains( concat_col, p_contains_str)>0
1852: AND( (l_search_merged ='Y' )
1853: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1854: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1855: AND d.search_context_id = p_search_ctx_id
1892: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1893: END IF;
1894: 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'||
1895: ' FROM HZ_STAGED_PARTY_SITES stage'||
1896: ' WHERE contains( concat_col, :cont)>0'||
1897: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1898: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1899: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1900: ' AND (ORG_CONTACT_ID IS NULL ' ||
1902: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1903: ' UNION ' ||
1904: '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'||
1905: ' FROM HZ_STAGED_PARTY_SITES stage'||
1906: ' WHERE contains( concat_col, :cont)>0'||
1907: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1908: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1909: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1910: ' AND ORG_CONTACT_ID IN ' ||
1912: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1913: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1914: ' and ('||p_restrict_sql1|| ') )' ||
1915: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1916: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1917: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1918: ELSE
1919: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
1920: ' FROM HZ_STAGED_PARTY_SITES stage'||
1913: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1914: ' and ('||p_restrict_sql1|| ') )' ||
1915: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1916: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1917: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1918: ELSE
1919: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
1920: ' FROM HZ_STAGED_PARTY_SITES stage'||
1921: ' WHERE contains( concat_col, :cont)>0'||
1917: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1918: ELSE
1919: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
1920: ' FROM HZ_STAGED_PARTY_SITES stage'||
1921: ' WHERE contains( concat_col, :cont)>0'||
1922: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1923: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1924: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1925: ' AND EXISTS ('||
1927: ' WHERE p.party_id = stage.party_id ' ||
1928: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1929: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1930: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1931: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1932: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1933: ,p_dup_party_id, p_dup_party_id;
1934: END IF;
1935: ELSE
1934: END IF;
1935: ELSE
1936: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
1937: ' FROM HZ_STAGED_PARTY_SITES stage'||
1938: ' WHERE contains( concat_col, :cont)>0'||
1939: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1940: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1941: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1942: ' AND EXISTS ('||
1944: ' WHERE p.party_id = stage.party_id ' ||
1945: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1946: ' AND ('||p_restrict_sql||')' ||
1947: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1948: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1949: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1950: ,p_dup_party_id, p_dup_party_id;
1951: END IF;
1952: END IF;
1950: ,p_dup_party_id, p_dup_party_id;
1951: END IF;
1952: END IF;
1953: output_long_strings('----------------------------------------------------------');
1954: output_long_strings('Party Site Contains String = '||p_contains_str);
1955: output_long_strings('Restrict Sql = '||p_restrict_sql);
1956: exception
1957: when others then
1958: if (instrb(SQLERRM,'DRG-51030')>0) then
1978: PROCEDURE open_contact_cursor(
1979: p_dup_party_id NUMBER,
1980: p_party_id NUMBER,
1981: p_restrict_sql VARCHAR2,
1982: p_contains_str VARCHAR2,
1983: p_search_ctx_id NUMBER,
1984: p_search_merged VARCHAR2,
1985: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1986: l_sqlstr VARCHAR2(4000);
2006: END IF;
2007: OPEN x_cursor FOR
2008: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID, TX22
2009: FROM HZ_STAGED_CONTACTS stage
2010: WHERE contains( concat_col, p_contains_str)>0
2011: AND EXISTS (
2012: SELECT 1 FROM HZ_STAGED_PARTIES p
2013: WHERE p.PARTY_ID = stage.PARTY_ID
2014: AND( (l_search_merged ='Y' )
2029: END IF;
2030: OPEN x_cursor FOR
2031: SELECT ORG_CONTACT_ID, PARTY_ID, TX22
2032: FROM HZ_STAGED_CONTACTS stage
2033: WHERE contains( concat_col, p_contains_str)>0
2034: AND EXISTS (
2035: SELECT 1 FROM HZ_STAGED_PARTIES p
2036: WHERE p.PARTY_ID = stage.PARTY_ID
2037: AND( (l_search_merged ='Y' )
2048: END IF;
2049: OPEN x_cursor FOR
2050: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID, TX22
2051: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
2052: WHERE contains( concat_col, p_contains_str)>0
2053: AND d.search_context_id = p_search_ctx_id
2054: AND d.party_id = stage.party_id
2055: AND( (l_search_merged ='Y' )
2056: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2082: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
2083: END IF;
2084: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID , TX22'||
2085: ' FROM HZ_STAGED_CONTACTS stage'||
2086: ' WHERE contains( concat_col, :cont)>0'||
2087: ' AND EXISTS ('||
2088: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2089: ' WHERE p.party_id = stage.party_id ' ||
2090: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2092: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2093: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2094: ' AND ('||p_restrict_sql||')' ||
2095: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2096: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2097: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2098: ,p_dup_party_id, p_dup_party_id;
2099: END IF;
2100: output_long_strings('----------------------------------------------------------');
2097: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2098: ,p_dup_party_id, p_dup_party_id;
2099: END IF;
2100: output_long_strings('----------------------------------------------------------');
2101: output_long_strings('Contacts Contains String = '||p_contains_str);
2102: output_long_strings('Restrict Sql = '||p_restrict_sql);
2103: exception
2104: when others then
2105: if (instrb(SQLERRM,'DRG-51030')>0) then
2125: PROCEDURE open_contact_pt_cursor(
2126: p_dup_party_id NUMBER,
2127: p_party_id NUMBER,
2128: p_restrict_sql VARCHAR2,
2129: p_contains_str VARCHAR2,
2130: p_search_ctx_id NUMBER,
2131: p_search_merged VARCHAR2,
2132: p_search_rel_cpts VARCHAR2,
2133: p_person_api VARCHAR2,
2155: THEN
2156: OPEN x_cursor FOR
2157: 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
2158: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2159: WHERE contains( concat_col, p_contains_str)>0
2160: AND d.search_context_id = p_search_ctx_id
2161: AND( (l_search_merged ='Y' )
2162: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2163: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2167: THEN
2168: OPEN x_cursor FOR
2169: 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
2170: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2171: WHERE contains( concat_col, p_contains_str)>0
2172: AND d.search_context_id = p_search_ctx_id
2173: AND( (l_search_merged ='Y' )
2174: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2175: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2184: IF p_search_rel_cpts = 'N' THEN
2185: OPEN x_cursor FOR
2186: 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
2187: FROM HZ_STAGED_CONTACT_POINTS stage
2188: WHERE contains( concat_col, p_contains_str)>0
2189: AND( (l_search_merged ='Y' )
2190: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2191: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2192: AND stage.party_id = p_party_id;
2193: ELSE
2194: OPEN x_cursor FOR
2195: 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
2196: FROM HZ_STAGED_CONTACT_POINTS stage
2197: WHERE contains( concat_col, p_contains_str)>0
2198: AND( (l_search_merged ='Y' )
2199: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2200: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2201: AND stage.party_id = p_party_id
2201: AND stage.party_id = p_party_id
2202: UNION
2203: 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
2204: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2205: WHERE contains( concat_col, p_contains_str)>0
2206: AND r.object_id = p_party_id
2207: AND( (l_search_merged ='Y' )
2208: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2209: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2223: END IF;
2224: OPEN x_cursor FOR
2225: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2226: FROM HZ_STAGED_CONTACT_POINTS stage
2227: WHERE contains( concat_col, p_contains_str)>0
2228: AND EXISTS (
2229: SELECT 1 FROM HZ_STAGED_PARTIES p
2230: WHERE p.PARTY_ID = stage.PARTY_ID
2231: AND( (l_search_merged ='Y' )
2243: IF p_person_api = 'Y' THEN
2244: OPEN x_cursor FOR
2245: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2246: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2247: WHERE contains( concat_col, p_contains_str)>0
2248: AND d.search_context_id = p_search_ctx_id
2249: AND d.party_id = stage.party_id
2250: AND( (l_search_merged ='Y' )
2251: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2253: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2254: UNION
2255: 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
2256: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2257: WHERE contains( concat_col, p_contains_str)>0
2258: AND d.search_context_id = p_search_ctx_id
2259: AND d.party_id = r.subject_id
2260: AND r.relationship_id = oc.party_relationship_id
2261: AND oc.org_contact_id = stage.org_contact_id
2266: ELSE
2267: OPEN x_cursor FOR
2268: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2269: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2270: WHERE contains( concat_col, p_contains_str)>0
2271: AND d.search_context_id = p_search_ctx_id
2272: AND d.party_id = stage.party_id
2273: AND( (l_search_merged ='Y' )
2274: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2311: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2312: END IF;
2313: 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'||
2314: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2315: ' WHERE contains( concat_col, :cont)>0 '||
2316: ' AND (stage.org_contact_id is null '||
2317: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2318: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2319: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2321: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2322: ' UNION ' ||
2323: ' 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'||
2324: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2325: ' WHERE contains( concat_col, :cont)>0 '||
2326: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2327: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2328: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2329: ' AND (stage.org_contact_id in ' ||
2331: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2332: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2333: ' and ('||p_restrict_sql1 || ') ) )' ||
2334: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2335: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2336: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2337: ELSE
2338: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2339: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2332: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2333: ' and ('||p_restrict_sql1 || ') ) )' ||
2334: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2335: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2336: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2337: ELSE
2338: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2339: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2340: ' WHERE contains( concat_col, :cont)>0'||
2336: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2337: ELSE
2338: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2339: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2340: ' WHERE contains( concat_col, :cont)>0'||
2341: ' AND EXISTS ('||
2342: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2343: ' WHERE p.party_id = stage.party_id ' ||
2344: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2346: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2347: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2348: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2349: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2350: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2351: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2352: ,p_dup_party_id, p_dup_party_id;
2353: END IF;
2354: ELSE
2353: END IF;
2354: ELSE
2355: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2356: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2357: ' WHERE contains( concat_col, :cont)>0'||
2358: ' AND EXISTS ('||
2359: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2360: ' WHERE p.party_id = stage.party_id ' ||
2361: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2363: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2364: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2365: ' AND ('||p_restrict_sql||')' ||
2366: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2367: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2368: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2369: ,p_dup_party_id, p_dup_party_id;
2370: END IF;
2371: END IF;
2370: END IF;
2371: END IF;
2372: END IF;
2373: output_long_strings('----------------------------------------------------------');
2374: output_long_strings('Contact Points Contains String = '||p_contains_str);
2375: output_long_strings('Restrict Sql = '||p_restrict_sql);
2376: exception
2377: when others then
2378: if (instrb(SQLERRM,'DRG-51030')>0) then
2491: x_num_matches OUT NUMBER
2492: ) IS
2493:
2494: -- Strings to hold the generated Intermedia query strings
2495: l_party_contains_str VARCHAR2(32000);
2496: l_party_site_contains_str VARCHAR2(32000);
2497: l_contact_contains_str VARCHAR2(32000);
2498: l_contact_pt_contains_str VARCHAR2(32000);
2499: l_denorm_str VARCHAR2(32000);
2492: ) IS
2493:
2494: -- Strings to hold the generated Intermedia query strings
2495: l_party_contains_str VARCHAR2(32000);
2496: l_party_site_contains_str VARCHAR2(32000);
2497: l_contact_contains_str VARCHAR2(32000);
2498: l_contact_pt_contains_str VARCHAR2(32000);
2499: l_denorm_str VARCHAR2(32000);
2500: l_ps_denorm_str VARCHAR2(32000);
2493:
2494: -- Strings to hold the generated Intermedia query strings
2495: l_party_contains_str VARCHAR2(32000);
2496: l_party_site_contains_str VARCHAR2(32000);
2497: l_contact_contains_str VARCHAR2(32000);
2498: l_contact_pt_contains_str VARCHAR2(32000);
2499: l_denorm_str VARCHAR2(32000);
2500: l_ps_denorm_str VARCHAR2(32000);
2501: l_ct_denorm_str VARCHAR2(32000);
2494: -- Strings to hold the generated Intermedia query strings
2495: l_party_contains_str VARCHAR2(32000);
2496: l_party_site_contains_str VARCHAR2(32000);
2497: l_contact_contains_str VARCHAR2(32000);
2498: l_contact_pt_contains_str VARCHAR2(32000);
2499: l_denorm_str VARCHAR2(32000);
2500: l_ps_denorm_str VARCHAR2(32000);
2501: l_ct_denorm_str VARCHAR2(32000);
2502: l_cpt_denorm_str VARCHAR2(32000);
2594: call_type(2) := 'OR';
2595: END;
2596:
2597: /** Private procedure to acquire and score at party level ***/
2598: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2599: l_party_id_idx NUMBER:=1;
2600: l_ctx_id NUMBER;
2601: l_precalc_score BOOLEAN := FALSE;
2602: BEGIN
2625: l_search_ctx_id,H_PARTY_ID(I));
2626: l_party_id_idx := H_PARTY_ID.COUNT+1;
2627: ELSE
2628: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2629: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2630: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2631: 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);
2632: END IF;
2633: 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
2633: 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
2634: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2635: 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);
2636: END IF;
2637: IF (l_party_site_contains_str IS NULL AND
2638: l_contact_contains_str IS NULL AND
2639: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2640: 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);
2641: RAISE L_RETURN_IMM_EXC;
2634: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2635: 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);
2636: END IF;
2637: IF (l_party_site_contains_str IS NULL AND
2638: l_contact_contains_str IS NULL AND
2639: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2640: 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);
2641: RAISE L_RETURN_IMM_EXC;
2642: ELSE
2635: 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);
2636: END IF;
2637: IF (l_party_site_contains_str IS NULL AND
2638: l_contact_contains_str IS NULL AND
2639: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2640: 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);
2641: RAISE L_RETURN_IMM_EXC;
2642: ELSE
2643: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2639: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2640: 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);
2641: RAISE L_RETURN_IMM_EXC;
2642: ELSE
2643: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2644: END IF;
2645: ELSE
2646: IF p_index>1 THEN
2647: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2662: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2663: 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);
2664: END IF;
2665: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2666: 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);
2667: END IF;
2668: LOOP
2669: FETCH l_party_cur INTO
2670: l_party_id , l_TX40, l_TX39;
2684: l_party_id_idx:= l_party_id_idx+1;
2685: END IF;
2686: IF (l_party_id_idx-1)>l_max_thresh THEN
2687: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2688: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2689: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2690: AND l_contact_pt_contains_str IS NULL THEN
2691: EXIT;
2692: END IF;
2686: IF (l_party_id_idx-1)>l_max_thresh THEN
2687: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2688: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2689: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2690: AND l_contact_pt_contains_str IS NULL THEN
2691: EXIT;
2692: END IF;
2693: CLOSE l_party_cur;
2694: IF p_index>1 THEN
2715: ROLLBACK to eval_start;
2716: END;
2717:
2718: /** Private procedure to acquire and score at party site level ***/
2719: 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
2720: l_party_id_idx NUMBER:=1;
2721: l_ctx_id NUMBER;
2722: h_ps_id HZ_PARTY_SEARCH.IDList;
2723: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2727: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2728: 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);
2729: 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);
2730: END IF;
2731: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2732: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2733: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2734: l_party_id := H_SCORES.FIRST;
2735: WHILE l_party_id IS NOT NULL LOOP
2728: 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);
2729: 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);
2730: END IF;
2731: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2732: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2733: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2734: l_party_id := H_SCORES.FIRST;
2735: WHILE l_party_id IS NOT NULL LOOP
2736: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2729: 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);
2730: END IF;
2731: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2732: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2733: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2734: l_party_id := H_SCORES.FIRST;
2735: WHILE l_party_id IS NOT NULL LOOP
2736: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2737: l_party_id:=H_SCORES.NEXT(l_party_id);
2759: l_search_ctx_id,H_PARTY_ID(I));
2760: l_party_id_idx := H_PARTY_ID.COUNT+1;
2761: ELSE
2762: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2763: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2764: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2765: 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);
2766: END IF;
2767: IF p_index>1 THEN
2781: END IF;
2782: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2783: 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);
2784: END IF;
2785: 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);
2786: LOOP
2787: FETCH l_party_site_cur INTO
2788: 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;
2789: EXIT WHEN l_party_site_cur%NOTFOUND;
2837: END IF;
2838: END;
2839:
2840: /** Private procedure to acquire and score at contact point level ***/
2841: 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
2842: l_party_id_idx NUMBER:=1;
2843: l_ctx_id NUMBER;
2844: h_ct_id HZ_PARTY_SEARCH.IDList;
2845: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2871: l_search_ctx_id,H_PARTY_ID(I));
2872: l_party_id_idx := H_PARTY_ID.COUNT+1;
2873: ELSE
2874: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2875: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2876: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2877: 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);
2878: END IF;
2879: IF p_index>1 THEN
2893: END IF;
2894: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2895: 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);
2896: END IF;
2897: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2898: LOOP
2899: FETCH l_contact_cur INTO
2900: l_org_contact_id, l_ct_party_id , l_TX22;
2901: EXIT WHEN l_contact_cur%NOTFOUND;
2946: END IF;
2947: END;
2948:
2949: /** Private procedure to acquire and score at contact point level ***/
2950: 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
2951: l_party_id_idx NUMBER:=1;
2952: l_ctx_id NUMBER;
2953: h_cpt_id HZ_PARTY_SEARCH.IDList;
2954: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2982: l_search_ctx_id,H_PARTY_ID(I));
2983: l_party_id_idx := H_PARTY_ID.COUNT+1;
2984: ELSE
2985: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2986: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2987: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2988: 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);
2989: END IF;
2990: IF p_index>1 THEN
3004: END IF;
3005: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3006: 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);
3007: END IF;
3008: 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);
3009: LOOP
3010: FETCH l_contact_pt_cur INTO
3011: 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;
3012: EXIT WHEN l_contact_pt_cur%NOTFOUND;
3185: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3186: l_threshold := round(( l_entered_max_score / 340) * 269);
3187: other_acq_criteria_exists := TRUE ;
3188: --check if acquisition criteria exists for any other entity
3189: IF l_party_contains_str IS NOT NULL THEN
3190: acq_cnt := 1;
3191: END IF;
3192: IF l_party_site_contains_str IS NOT NULL THEN
3193: acq_cnt := acq_cnt+1;
3188: --check if acquisition criteria exists for any other entity
3189: IF l_party_contains_str IS NOT NULL THEN
3190: acq_cnt := 1;
3191: END IF;
3192: IF l_party_site_contains_str IS NOT NULL THEN
3193: acq_cnt := acq_cnt+1;
3194: END IF;
3195: IF l_contact_contains_str IS NOT NULL THEN
3196: acq_cnt := acq_cnt+1;
3191: END IF;
3192: IF l_party_site_contains_str IS NOT NULL THEN
3193: acq_cnt := acq_cnt+1;
3194: END IF;
3195: IF l_contact_contains_str IS NOT NULL THEN
3196: acq_cnt := acq_cnt+1;
3197: END IF;
3198: IF l_contact_pt_contains_str IS NOT NULL THEN
3199: acq_cnt := acq_cnt+1;
3194: END IF;
3195: IF l_contact_contains_str IS NOT NULL THEN
3196: acq_cnt := acq_cnt+1;
3197: END IF;
3198: IF l_contact_pt_contains_str IS NOT NULL THEN
3199: acq_cnt := acq_cnt+1;
3200: END IF;
3201: IF acq_cnt>1 THEN
3202: other_acq_criteria_exists := TRUE;
3228: FND_MSG_PUB.ADD;
3229: RAISE FND_API.G_EXC_ERROR;
3230: END IF;
3231: END IF;
3232: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3233: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3234: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3235: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3236: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3229: RAISE FND_API.G_EXC_ERROR;
3230: END IF;
3231: END IF;
3232: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3233: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3234: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3235: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3236: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3237: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3230: END IF;
3231: END IF;
3232: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3233: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3234: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3235: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3236: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3237: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3238: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3231: END IF;
3232: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3233: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3234: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3235: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3236: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3237: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3238: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3239: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3232: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3233: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3234: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3235: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3236: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3237: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3238: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3239: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3240: END IF;
3233: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3234: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3235: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3236: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3237: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3238: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3239: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3240: END IF;
3241: END;
3234: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3235: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3236: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3237: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3238: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3239: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3240: END IF;
3241: END;
3242: /************ End of find_parties private procedures **********/
3235: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3236: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3237: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3238: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3239: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3240: END IF;
3241: END;
3242: /************ End of find_parties private procedures **********/
3243:
3307: 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);
3308: END IF;
3309: -- ************************************************************
3310: -- STEP 2. Setup of intermedia query strings for Acquisition query
3311: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3312: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3313: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3314: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3315: 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);
3308: END IF;
3309: -- ************************************************************
3310: -- STEP 2. Setup of intermedia query strings for Acquisition query
3311: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3312: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3313: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3314: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3315: 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);
3316: END IF;
3309: -- ************************************************************
3310: -- STEP 2. Setup of intermedia query strings for Acquisition query
3311: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3312: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3313: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3314: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3315: 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);
3316: END IF;
3317: l_denorm_max_score:=0;
3341: END IF;
3342: ELSE
3343: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3344: END IF;
3345: 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));
3346: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3347:
3348: -- Setup Search Context ID
3349: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3351:
3352: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3353: 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);
3354: END IF;
3355: IF l_party_contains_str IS NULL THEN
3356: defpt := 1;
3357: END IF;
3358: IF l_party_site_contains_str IS NULL THEN
3359: defps := 1;
3354: END IF;
3355: IF l_party_contains_str IS NULL THEN
3356: defpt := 1;
3357: END IF;
3358: IF l_party_site_contains_str IS NULL THEN
3359: defps := 1;
3360: END IF;
3361: IF l_contact_contains_str IS NULL THEN
3362: defct := 1;
3357: END IF;
3358: IF l_party_site_contains_str IS NULL THEN
3359: defps := 1;
3360: END IF;
3361: IF l_contact_contains_str IS NULL THEN
3362: defct := 1;
3363: END IF;
3364: IF l_contact_pt_contains_str IS NULL THEN
3365: defcpt := 1;
3360: END IF;
3361: IF l_contact_contains_str IS NULL THEN
3362: defct := 1;
3363: END IF;
3364: IF l_contact_pt_contains_str IS NULL THEN
3365: defcpt := 1;
3366: END IF;
3367:
3368: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3369: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3370: 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);
3371: END IF;
3372: 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);
3373: 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);
3374: 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);
3375: 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);
3376: 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);
3377: 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);
3370: 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);
3371: END IF;
3372: 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);
3373: 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);
3374: 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);
3375: 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);
3376: 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);
3377: 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);
3378: END IF;
3371: END IF;
3372: 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);
3373: 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);
3374: 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);
3375: 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);
3376: 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);
3377: 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);
3378: END IF;
3379: FOR I in 1..5 LOOP
3372: 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);
3373: 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);
3374: 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);
3375: 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);
3376: 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);
3377: 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);
3378: END IF;
3379: FOR I in 1..5 LOOP
3380: do_eval(I);
3454: x_num_matches OUT NUMBER
3455: ) IS
3456:
3457: -- Strings to hold the generated Intermedia query strings
3458: l_party_contains_str VARCHAR2(32000);
3459: l_party_site_contains_str VARCHAR2(32000);
3460: l_contact_contains_str VARCHAR2(32000);
3461: l_contact_pt_contains_str VARCHAR2(32000);
3462: l_denorm_str VARCHAR2(32000);
3455: ) IS
3456:
3457: -- Strings to hold the generated Intermedia query strings
3458: l_party_contains_str VARCHAR2(32000);
3459: l_party_site_contains_str VARCHAR2(32000);
3460: l_contact_contains_str VARCHAR2(32000);
3461: l_contact_pt_contains_str VARCHAR2(32000);
3462: l_denorm_str VARCHAR2(32000);
3463: l_ps_denorm_str VARCHAR2(32000);
3456:
3457: -- Strings to hold the generated Intermedia query strings
3458: l_party_contains_str VARCHAR2(32000);
3459: l_party_site_contains_str VARCHAR2(32000);
3460: l_contact_contains_str VARCHAR2(32000);
3461: l_contact_pt_contains_str VARCHAR2(32000);
3462: l_denorm_str VARCHAR2(32000);
3463: l_ps_denorm_str VARCHAR2(32000);
3464: l_ct_denorm_str VARCHAR2(32000);
3457: -- Strings to hold the generated Intermedia query strings
3458: l_party_contains_str VARCHAR2(32000);
3459: l_party_site_contains_str VARCHAR2(32000);
3460: l_contact_contains_str VARCHAR2(32000);
3461: l_contact_pt_contains_str VARCHAR2(32000);
3462: l_denorm_str VARCHAR2(32000);
3463: l_ps_denorm_str VARCHAR2(32000);
3464: l_ct_denorm_str VARCHAR2(32000);
3465: l_cpt_denorm_str VARCHAR2(32000);
3587: call_type(2) := 'OR';
3588: END;
3589:
3590: /** Private procedure to acquire and score at party level ***/
3591: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3592: l_party_id_idx NUMBER:=1;
3593: l_ctx_id NUMBER;
3594: BEGIN
3595: SAVEPOINT eval_start;
3608: l_search_ctx_id,H_PARTY_ID(I));
3609: l_party_id_idx := H_PARTY_ID.COUNT+1;
3610: ELSE
3611: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3612: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3613: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3614: 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);
3615: END IF;
3616: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3616: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' 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 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);
3619: END IF;
3620: IF (l_party_site_contains_str IS NULL AND
3621: l_contact_contains_str IS NULL AND
3622: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3623: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3624: RAISE L_RETURN_IMM_EXC;
3617: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3618: 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);
3619: END IF;
3620: IF (l_party_site_contains_str IS NULL AND
3621: l_contact_contains_str IS NULL AND
3622: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3623: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3624: RAISE L_RETURN_IMM_EXC;
3625: ELSE
3618: 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);
3619: END IF;
3620: IF (l_party_site_contains_str IS NULL AND
3621: l_contact_contains_str IS NULL AND
3622: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3623: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3624: RAISE L_RETURN_IMM_EXC;
3625: ELSE
3626: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3622: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3623: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3624: RAISE L_RETURN_IMM_EXC;
3625: ELSE
3626: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3627: END IF;
3628: ELSE
3629: IF p_index>1 THEN
3630: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3642: l_ctx_id := NULL;
3643: l_party_id_idx := H_PARTY_ID.COUNT+1;
3644: END IF;
3645: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3646: 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);
3647: END IF;
3648: LOOP
3649: FETCH l_party_cur INTO
3650: l_party_id , l_TX40, l_TX39;
3664: l_party_id_idx:= l_party_id_idx+1;
3665: END IF;
3666: IF (l_party_id_idx-1)>l_max_thresh THEN
3667: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3668: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3669: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3670: AND l_contact_pt_contains_str IS NULL THEN
3671: H_PARTY_ID.DELETE(l_party_id_idx-1);
3672: H_PARTY_ID_LIST.DELETE(l_index);
3666: IF (l_party_id_idx-1)>l_max_thresh THEN
3667: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3668: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3669: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3670: AND l_contact_pt_contains_str IS NULL THEN
3671: H_PARTY_ID.DELETE(l_party_id_idx-1);
3672: H_PARTY_ID_LIST.DELETE(l_index);
3673: H_SCORES.DELETE(l_index);
3674: EXIT;
3695: CLOSE l_party_cur;
3696: ROLLBACK to eval_start;
3697: END;
3698: PROCEDURE open_person_contact_cursor(
3699: p_contains_str VARCHAR2,
3700: p_search_ctx_id NUMBER,
3701: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3702: BEGIN
3703: OPEN x_cursor FOR
3702: BEGIN
3703: OPEN x_cursor FOR
3704: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID, TX22
3705: FROM HZ_STAGED_CONTACTS stage
3706: WHERE contains( concat_col, p_contains_str)>0
3707: AND ORG_CONTACT_ID in (
3708: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3709: org_contact_id
3710: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc
3726: end if;
3727: END;
3728:
3729: /** Private procedure to acquire and score at party site level ***/
3730: 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
3731: l_party_id_idx NUMBER:=1;
3732: l_ctx_id NUMBER;
3733: h_ps_id HZ_PARTY_SEARCH.IDList;
3734: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3754: ELSE
3755: l_party_id_idx := H_PARTY_ID.COUNT+1;
3756: l_ctx_id := NULL;
3757: END IF;
3758: 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);
3759: LOOP
3760: FETCH l_party_site_cur INTO
3761: 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;
3762: EXIT WHEN l_party_site_cur%NOTFOUND;
3825: END IF;
3826: END;
3827:
3828: /** Private procedure to acquire and score at party site level ***/
3829: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3830: l_party_id_idx NUMBER:=1;
3831: l_ctx_id NUMBER;
3832: h_ct_id HZ_PARTY_SEARCH.IDList;
3833: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3841: FORALL I in 1..H_PARTY_ID.COUNT
3842: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3843: l_search_ctx_id,H_PARTY_ID(I));
3844: l_party_id_idx := H_PARTY_ID.COUNT+1;
3845: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3846: LOOP
3847: FETCH l_contact_cur INTO
3848: l_org_contact_id, l_ct_party_id , l_TX22;
3849: EXIT WHEN l_contact_cur%NOTFOUND;
3883: END IF;
3884: END;
3885:
3886: /** Private procedure to acquire and score at contact point level ***/
3887: 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
3888: l_party_id_idx NUMBER:=1;
3889: l_ctx_id NUMBER;
3890: h_cpt_id HZ_PARTY_SEARCH.IDList;
3891: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3928: END IF;
3929: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3930: 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);
3931: END IF;
3932: 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);
3933: LOOP
3934: FETCH l_contact_pt_cur INTO
3935: 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;
3936: EXIT WHEN l_contact_pt_cur%NOTFOUND;
4131: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
4132: l_threshold := round(( l_entered_max_score / 340) * 269);
4133: other_acq_criteria_exists := TRUE ;
4134: --check if acquisition criteria exists for any other entity
4135: IF l_party_contains_str IS NOT NULL THEN
4136: acq_cnt := 1;
4137: END IF;
4138: IF l_party_site_contains_str IS NOT NULL THEN
4139: acq_cnt := acq_cnt+1;
4134: --check if acquisition criteria exists for any other entity
4135: IF l_party_contains_str IS NOT NULL THEN
4136: acq_cnt := 1;
4137: END IF;
4138: IF l_party_site_contains_str IS NOT NULL THEN
4139: acq_cnt := acq_cnt+1;
4140: END IF;
4141: IF l_contact_contains_str IS NOT NULL THEN
4142: acq_cnt := acq_cnt+1;
4137: END IF;
4138: IF l_party_site_contains_str IS NOT NULL THEN
4139: acq_cnt := acq_cnt+1;
4140: END IF;
4141: IF l_contact_contains_str IS NOT NULL THEN
4142: acq_cnt := acq_cnt+1;
4143: END IF;
4144: IF l_contact_pt_contains_str IS NOT NULL THEN
4145: acq_cnt := acq_cnt+1;
4140: END IF;
4141: IF l_contact_contains_str IS NOT NULL THEN
4142: acq_cnt := acq_cnt+1;
4143: END IF;
4144: IF l_contact_pt_contains_str IS NOT NULL THEN
4145: acq_cnt := acq_cnt+1;
4146: END IF;
4147: IF acq_cnt>1 THEN
4148: other_acq_criteria_exists := TRUE;
4174: FND_MSG_PUB.ADD;
4175: RAISE FND_API.G_EXC_ERROR;
4176: END IF;
4177: END IF;
4178: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4179: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4180: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4181: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4182: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4175: RAISE FND_API.G_EXC_ERROR;
4176: END IF;
4177: END IF;
4178: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4179: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4180: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4181: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4182: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4183: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4176: END IF;
4177: END IF;
4178: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4179: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4180: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4181: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4182: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4183: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4184: END IF;
4177: END IF;
4178: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4179: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4180: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4181: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4182: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4183: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4184: END IF;
4185: END;
4178: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4179: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4180: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4181: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4182: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4183: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4184: END IF;
4185: END;
4186: /************ End of find_persons private procedures **********/
4179: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4180: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4181: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4182: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4183: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4184: END IF;
4185: END;
4186: /************ End of find_persons private procedures **********/
4187:
4214: g_score_until_thresh := false;
4215: END IF;
4216: -- ************************************************************
4217: -- STEP 2. Setup of intermedia query strings for Acquisition query
4218: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4219: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4220: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4221: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4222: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4215: END IF;
4216: -- ************************************************************
4217: -- STEP 2. Setup of intermedia query strings for Acquisition query
4218: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4219: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4220: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4221: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4222: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4223:
4216: -- ************************************************************
4217: -- STEP 2. Setup of intermedia query strings for Acquisition query
4218: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4219: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4220: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4221: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4222: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4223:
4224: -- Setup Search Context ID
4217: -- STEP 2. Setup of intermedia query strings for Acquisition query
4218: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4219: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4220: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4221: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4222: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4223:
4224: -- Setup Search Context ID
4225: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4224: -- Setup Search Context ID
4225: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4226: x_search_ctx_id := l_search_ctx_id;
4227:
4228: IF l_party_contains_str IS NULL THEN
4229: defpt := 1;
4230: END IF;
4231: IF l_party_site_contains_str IS NULL THEN
4232: defps := 1;
4227:
4228: IF l_party_contains_str IS NULL THEN
4229: defpt := 1;
4230: END IF;
4231: IF l_party_site_contains_str IS NULL THEN
4232: defps := 1;
4233: END IF;
4234: IF l_contact_contains_str IS NULL THEN
4235: defct := 1;
4230: END IF;
4231: IF l_party_site_contains_str IS NULL THEN
4232: defps := 1;
4233: END IF;
4234: IF l_contact_contains_str IS NULL THEN
4235: defct := 1;
4236: END IF;
4237: IF l_contact_pt_contains_str IS NULL THEN
4238: defcpt := 1;
4233: END IF;
4234: IF l_contact_contains_str IS NULL THEN
4235: defct := 1;
4236: END IF;
4237: IF l_contact_pt_contains_str IS NULL THEN
4238: defcpt := 1;
4239: END IF;
4240:
4241: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4239: END IF;
4240:
4241: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4242: 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);
4243: 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);
4244: 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);
4245: 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);
4246: 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);
4247: 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);
4240:
4241: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4242: 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);
4243: 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);
4244: 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);
4245: 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);
4246: 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);
4247: 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);
4248: END IF;
4241: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4242: 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);
4243: 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);
4244: 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);
4245: 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);
4246: 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);
4247: 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);
4248: END IF;
4249: FOR I in 1..5 LOOP
4242: 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);
4243: 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);
4244: 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);
4245: 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);
4246: 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);
4247: 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);
4248: END IF;
4249: FOR I in 1..5 LOOP
4250: do_eval(I);
4248: END IF;
4249: FOR I in 1..5 LOOP
4250: do_eval(I);
4251: END LOOP;
4252: IF l_contact_contains_str IS NOT NULL THEN
4253: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4254: END IF;
4255: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4256: 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);
4249: FOR I in 1..5 LOOP
4250: do_eval(I);
4251: END LOOP;
4252: IF l_contact_contains_str IS NOT NULL THEN
4253: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4254: END IF;
4255: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4256: 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);
4257: END IF;
5079: x_num_matches OUT NUMBER
5080: ) IS
5081:
5082: -- Strings to hold the generated Intermedia query strings
5083: l_party_contains_str VARCHAR2(32000);
5084: l_match_idx NUMBER;
5085: l_party_site_contains_str VARCHAR2(32000);
5086: l_contact_contains_str VARCHAR2(32000);
5087: l_contact_pt_contains_str VARCHAR2(32000);
5081:
5082: -- Strings to hold the generated Intermedia query strings
5083: l_party_contains_str VARCHAR2(32000);
5084: l_match_idx NUMBER;
5085: l_party_site_contains_str VARCHAR2(32000);
5086: l_contact_contains_str VARCHAR2(32000);
5087: l_contact_pt_contains_str VARCHAR2(32000);
5088: l_tmp VARCHAR2(32000);
5089:
5082: -- Strings to hold the generated Intermedia query strings
5083: l_party_contains_str VARCHAR2(32000);
5084: l_match_idx NUMBER;
5085: l_party_site_contains_str VARCHAR2(32000);
5086: l_contact_contains_str VARCHAR2(32000);
5087: l_contact_pt_contains_str VARCHAR2(32000);
5088: l_tmp VARCHAR2(32000);
5089:
5090: -- Other local variables
5083: l_party_contains_str VARCHAR2(32000);
5084: l_match_idx NUMBER;
5085: l_party_site_contains_str VARCHAR2(32000);
5086: l_contact_contains_str VARCHAR2(32000);
5087: l_contact_pt_contains_str VARCHAR2(32000);
5088: l_tmp VARCHAR2(32000);
5089:
5090: -- Other local variables
5091: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5169: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5170:
5171: -- ************************************************************
5172: -- STEP 2. Setup of intermedia query strings for Acquisition query
5173: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5174: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5175: 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);
5176:
5177: -- Setup Search Context ID
5170:
5171: -- ************************************************************
5172: -- STEP 2. Setup of intermedia query strings for Acquisition query
5173: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5174: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5175: 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);
5176:
5177: -- Setup Search Context ID
5178: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5179: x_search_ctx_id := l_search_ctx_id;
5180:
5181: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5182: 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);
5183: 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);
5184: 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);
5185: 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);
5186: END IF;
5187: IF l_party_site_contains_str IS NULL THEN
5180:
5181: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5182: 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);
5183: 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);
5184: 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);
5185: 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);
5186: END IF;
5187: IF l_party_site_contains_str IS NULL THEN
5188: defps := 1;
5183: 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);
5184: 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);
5185: 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);
5186: END IF;
5187: IF l_party_site_contains_str IS NULL THEN
5188: defps := 1;
5189: END IF;
5190: IF l_contact_pt_contains_str IS NULL THEN
5191: defcpt := 1;
5186: END IF;
5187: IF l_party_site_contains_str IS NULL THEN
5188: defps := 1;
5189: END IF;
5190: IF l_contact_pt_contains_str IS NULL THEN
5191: defcpt := 1;
5192: END IF;
5193:
5194: IF l_party_site_contains_str IS NOT NULL THEN
5190: IF l_contact_pt_contains_str IS NULL THEN
5191: defcpt := 1;
5192: END IF;
5193:
5194: IF l_party_site_contains_str IS NOT NULL THEN
5195: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5196: LOOP
5197: FETCH l_party_site_cur INTO
5198: 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;
5191: defcpt := 1;
5192: END IF;
5193:
5194: IF l_party_site_contains_str IS NOT NULL THEN
5195: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5196: LOOP
5197: FETCH l_party_site_cur INTO
5198: 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;
5199: EXIT WHEN l_party_site_cur%NOTFOUND;
5214: END LOOP;
5215: CLOSE l_party_site_cur;
5216: END IF;
5217:
5218: IF l_contact_pt_contains_str IS NOT NULL THEN
5219: SAVEPOINT eval_start;
5220: IF l_match_str = ' AND ' OR (
5221: ((l_cpt_max_score/l_entered_max_score)<(269/340))
5222: ) THEN
5222: ) THEN
5223: FORALL I in 1..l_match_ps_list.COUNT
5224: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5225: values (l_search_ctx_id,l_match_ps_list(I));
5226: 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');
5227: ELSE
5228: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5229: END IF;
5230: LOOP
5224: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5225: values (l_search_ctx_id,l_match_ps_list(I));
5226: 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');
5227: ELSE
5228: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5229: END IF;
5230: LOOP
5231: FETCH l_contact_pt_cur INTO
5232: 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;
5324: x_num_matches OUT NUMBER
5325: ) IS
5326:
5327: -- Strings to hold the generated Intermedia query strings
5328: l_party_contains_str VARCHAR2(32000);
5329: l_party_site_contains_str VARCHAR2(32000);
5330: l_contact_contains_str VARCHAR2(32000);
5331: l_contact_pt_contains_str VARCHAR2(32000);
5332: l_tmp VARCHAR2(32000);
5325: ) IS
5326:
5327: -- Strings to hold the generated Intermedia query strings
5328: l_party_contains_str VARCHAR2(32000);
5329: l_party_site_contains_str VARCHAR2(32000);
5330: l_contact_contains_str VARCHAR2(32000);
5331: l_contact_pt_contains_str VARCHAR2(32000);
5332: l_tmp VARCHAR2(32000);
5333:
5326:
5327: -- Strings to hold the generated Intermedia query strings
5328: l_party_contains_str VARCHAR2(32000);
5329: l_party_site_contains_str VARCHAR2(32000);
5330: l_contact_contains_str VARCHAR2(32000);
5331: l_contact_pt_contains_str VARCHAR2(32000);
5332: l_tmp VARCHAR2(32000);
5333:
5334: -- Other local variables
5327: -- Strings to hold the generated Intermedia query strings
5328: l_party_contains_str VARCHAR2(32000);
5329: l_party_site_contains_str VARCHAR2(32000);
5330: l_contact_contains_str VARCHAR2(32000);
5331: l_contact_pt_contains_str VARCHAR2(32000);
5332: l_tmp VARCHAR2(32000);
5333:
5334: -- Other local variables
5335: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5402: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5403:
5404: -- ************************************************************
5405: -- STEP 2. Setup of intermedia query strings for Acquisition query
5406: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5407: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5408: 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);
5409:
5410: -- Setup Search Context ID
5403:
5404: -- ************************************************************
5405: -- STEP 2. Setup of intermedia query strings for Acquisition query
5406: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5407: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5408: 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);
5409:
5410: -- Setup Search Context ID
5411: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5412: x_search_ctx_id := l_search_ctx_id;
5413:
5414: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5415: 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);
5416: 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);
5417: 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);
5418: 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);
5419: END IF;
5420: IF l_contact_contains_str IS NULL THEN
5413:
5414: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5415: 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);
5416: 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);
5417: 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);
5418: 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);
5419: END IF;
5420: IF l_contact_contains_str IS NULL THEN
5421: defct := 1;
5416: 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);
5417: 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);
5418: 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);
5419: END IF;
5420: IF l_contact_contains_str IS NULL THEN
5421: defct := 1;
5422: END IF;
5423: IF l_contact_pt_contains_str IS NULL THEN
5424: defcpt := 1;
5419: END IF;
5420: IF l_contact_contains_str IS NULL THEN
5421: defct := 1;
5422: END IF;
5423: IF l_contact_pt_contains_str IS NULL THEN
5424: defcpt := 1;
5425: END IF;
5426:
5427: IF l_contact_contains_str IS NOT NULL THEN
5423: IF l_contact_pt_contains_str IS NULL THEN
5424: defcpt := 1;
5425: END IF;
5426:
5427: IF l_contact_contains_str IS NOT NULL THEN
5428: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5429: LOOP
5430: FETCH l_contact_cur INTO
5431: l_org_contact_id, l_ct_party_id , l_TX22;
5424: defcpt := 1;
5425: END IF;
5426:
5427: IF l_contact_contains_str IS NOT NULL THEN
5428: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5429: LOOP
5430: FETCH l_contact_cur INTO
5431: l_org_contact_id, l_ct_party_id , l_TX22;
5432: EXIT WHEN l_contact_cur%NOTFOUND;
5445: END LOOP;
5446: CLOSE l_contact_cur;
5447: END IF;
5448:
5449: IF l_contact_pt_contains_str IS NOT NULL THEN
5450: SAVEPOINT eval_start;
5451: IF l_match_str = ' AND ' OR (
5452: ((l_cpt_max_score/l_entered_max_score)<(269/340))
5453: ) THEN
5453: ) THEN
5454: FORALL I in 1..l_match_contact_list.COUNT
5455: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5456: values (l_search_ctx_id,l_match_contact_list(I));
5457: 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');
5458: ELSE
5459: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5460: END IF;
5461: LOOP
5455: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5456: values (l_search_ctx_id,l_match_contact_list(I));
5457: 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');
5458: ELSE
5459: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5460: END IF;
5461: LOOP
5462: FETCH l_contact_pt_cur INTO
5463: 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;
5553: ) IS
5554:
5555:
5556: -- Strings to hold the generated Intermedia query strings
5557: l_contact_pt_contains_str VARCHAR2(32000);
5558: l_tmp VARCHAR2(32000);
5559:
5560: -- Other local variables
5561: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5614: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5615:
5616: -- ************************************************************
5617: -- STEP 2. Setup of intermedia query strings for Acquisition query
5618: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5619: 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);
5620:
5621: -- Setup Search Context ID
5622: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5622: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5623: x_search_ctx_id := l_search_ctx_id;
5624: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5625: 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);
5626: 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);
5627: 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);
5628: END IF;
5629:
5630: IF l_contact_pt_contains_str IS NOT NULL THEN
5626: 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);
5627: 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);
5628: END IF;
5629:
5630: IF l_contact_pt_contains_str IS NOT NULL THEN
5631: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5632: cnt := 1;
5633: LOOP
5634: FETCH l_contact_pt_cur INTO
5627: 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);
5628: END IF;
5629:
5630: IF l_contact_pt_contains_str IS NOT NULL THEN
5631: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5632: cnt := 1;
5633: LOOP
5634: FETCH l_contact_pt_cur INTO
5635: 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;
5698: x_search_ctx_id IN OUT NUMBER
5699: ) IS
5700:
5701: -- Strings to hold the generated Intermedia query strings
5702: l_party_contains_str VARCHAR2(32000);
5703: l_party_site_contains_str VARCHAR2(32000);
5704: l_contact_contains_str VARCHAR2(32000);
5705: l_contact_pt_contains_str VARCHAR2(32000);
5706: l_tmp VARCHAR2(32000);
5699: ) IS
5700:
5701: -- Strings to hold the generated Intermedia query strings
5702: l_party_contains_str VARCHAR2(32000);
5703: l_party_site_contains_str VARCHAR2(32000);
5704: l_contact_contains_str VARCHAR2(32000);
5705: l_contact_pt_contains_str VARCHAR2(32000);
5706: l_tmp VARCHAR2(32000);
5707:
5700:
5701: -- Strings to hold the generated Intermedia query strings
5702: l_party_contains_str VARCHAR2(32000);
5703: l_party_site_contains_str VARCHAR2(32000);
5704: l_contact_contains_str VARCHAR2(32000);
5705: l_contact_pt_contains_str VARCHAR2(32000);
5706: l_tmp VARCHAR2(32000);
5707:
5708: -- Other local variables
5701: -- Strings to hold the generated Intermedia query strings
5702: l_party_contains_str VARCHAR2(32000);
5703: l_party_site_contains_str VARCHAR2(32000);
5704: l_contact_contains_str VARCHAR2(32000);
5705: l_contact_pt_contains_str VARCHAR2(32000);
5706: l_tmp VARCHAR2(32000);
5707:
5708: -- Other local variables
5709: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5788: g_score_until_thresh := false;
5789: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5790: -- ************************************************************
5791: -- STEP 2. Setup of intermedia query strings for Acquisition query
5792: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5793: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5794: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5795: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5796:
5789: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5790: -- ************************************************************
5791: -- STEP 2. Setup of intermedia query strings for Acquisition query
5792: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5793: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5794: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5795: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5796:
5797: -- Setup Search Context ID
5790: -- ************************************************************
5791: -- STEP 2. Setup of intermedia query strings for Acquisition query
5792: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5793: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5794: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5795: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5796:
5797: -- Setup Search Context ID
5798: IF x_search_ctx_id IS NULL THEN
5810: 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);
5811: END LOOP;
5812: CLOSE l_party_cur;
5813:
5814: IF l_party_site_contains_str IS NOT NULL THEN
5815: l_max_score := 0;
5816: l_max_id := 0;
5817: l_max_idx := 0;
5818: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5815: l_max_score := 0;
5816: l_max_id := 0;
5817: l_max_idx := 0;
5818: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5819: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5820: ELSE
5821: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5822: END IF;
5823: LOOP
5817: l_max_idx := 0;
5818: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5819: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5820: ELSE
5821: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5822: END IF;
5823: LOOP
5824: FETCH l_party_site_cur INTO
5825: 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;
5846: 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);
5847: END IF;
5848: END IF;
5849:
5850: IF l_contact_contains_str IS NOT NULL THEN
5851: l_max_score := 0;
5852: l_max_id := 0;
5853: l_max_idx := 0;
5854: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5850: IF l_contact_contains_str IS NOT NULL THEN
5851: l_max_score := 0;
5852: l_max_id := 0;
5853: l_max_idx := 0;
5854: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5855: LOOP
5856: FETCH l_contact_cur INTO
5857: l_org_contact_id, l_ct_party_id , l_TX22;
5858: EXIT WHEN l_contact_cur%NOTFOUND;
5869: 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);
5870: END IF;
5871: END IF;
5872:
5873: IF l_contact_pt_contains_str IS NOT NULL THEN
5874: l_max_score := 0;
5875: l_max_id := 0;
5876: l_max_idx := 0;
5877: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5874: l_max_score := 0;
5875: l_max_id := 0;
5876: l_max_idx := 0;
5877: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5878: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5879: ELSE
5880: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5881: END IF;
5882: LOOP
5876: l_max_idx := 0;
5877: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5878: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5879: ELSE
5880: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5881: END IF;
5882: LOOP
5883: FETCH l_contact_pt_cur INTO
5884: 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;