[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:
1168: p_party_max_score NUMBER,
1169: p_denorm_max_score NUMBER,
1170: p_non_denorm_max_score NUMBER,
1171: p_threshold NUMBER) RETURN VARCHAR2 IS
1172: l_party_contains_str VARCHAR2(32000);
1173: l_party_filter VARCHAR2(1) := null;
1174: l_prim_temp VARCHAR2(4000);
1175: l_denorm_str VARCHAR2(4000);
1176: BEGIN
1212:
1213: IF l_denorm_str IS NOT NULL THEN
1214: IF (p_non_denorm_max_score+50)>=p_threshold THEN
1215: l_denorm_str := NULL;
1216: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1217: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
1218: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1219: l_denorm_str := NULL;
1220: END IF;
1214: IF (p_non_denorm_max_score+50)>=p_threshold THEN
1215: l_denorm_str := NULL;
1216: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1217: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
1218: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1219: l_denorm_str := NULL;
1220: END IF;
1221: ELSE
1222: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1218: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1219: l_denorm_str := NULL;
1220: END IF;
1221: ELSE
1222: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1223: END IF;
1224:
1225: -- Setup query string for PARTY_ALL_NAMES
1226: l_prim_temp := null;
1229:
1230: IF l_denorm_str IS NOT NULL THEN
1231: IF (p_non_denorm_max_score+50)>=p_threshold THEN
1232: l_denorm_str := NULL;
1233: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1234: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
1235: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1236: l_denorm_str := NULL;
1237: END IF;
1231: IF (p_non_denorm_max_score+50)>=p_threshold THEN
1232: l_denorm_str := NULL;
1233: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1234: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
1235: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1236: l_denorm_str := NULL;
1237: END IF;
1238: ELSE
1239: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1235: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1236: l_denorm_str := NULL;
1237: END IF;
1238: ELSE
1239: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1240: END IF;
1241:
1242: -- Setup query string for DUNS_NUMBER_C
1243: l_prim_temp := null;
1252:
1253: IF l_denorm_str IS NOT NULL THEN
1254: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1255: l_denorm_str := NULL;
1256: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1257: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1258: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1259: l_denorm_str := NULL;
1260: END IF;
1254: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1255: l_denorm_str := NULL;
1256: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1257: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1258: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1259: l_denorm_str := NULL;
1260: END IF;
1261: ELSE
1262: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1258: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1259: l_denorm_str := NULL;
1260: END IF;
1261: ELSE
1262: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1263: END IF;
1264:
1265: -- Setup query string for PARTY_NUMBER
1266: l_prim_temp := null;
1269:
1270: IF l_denorm_str IS NOT NULL THEN
1271: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1272: l_denorm_str := NULL;
1273: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1274: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1275: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1276: l_denorm_str := NULL;
1277: END IF;
1271: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1272: l_denorm_str := NULL;
1273: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1274: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1275: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1276: l_denorm_str := NULL;
1277: END IF;
1278: ELSE
1279: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1275: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1276: l_denorm_str := NULL;
1277: END IF;
1278: ELSE
1279: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1280: END IF;
1281:
1282: -- Setup query string for ALL_ACCOUNT_NUMBERS
1283: l_prim_temp := null;
1286:
1287: IF l_denorm_str IS NOT NULL THEN
1288: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1289: l_denorm_str := NULL;
1290: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1291: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1292: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1293: l_denorm_str := NULL;
1294: END IF;
1288: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1289: l_denorm_str := NULL;
1290: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1291: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1292: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1293: l_denorm_str := NULL;
1294: END IF;
1295: ELSE
1296: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1292: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1293: l_denorm_str := NULL;
1294: END IF;
1295: ELSE
1296: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1297: END IF;
1298: IF lengthb(l_party_contains_str) > 4000 THEN
1299: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1300: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1294: END IF;
1295: ELSE
1296: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1297: END IF;
1298: IF lengthb(l_party_contains_str) > 4000 THEN
1299: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1300: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1301: FND_MSG_PUB.ADD;
1302: RAISE FND_API.G_EXC_ERROR;
1300: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1301: FND_MSG_PUB.ADD;
1302: RAISE FND_API.G_EXC_ERROR;
1303: END IF;
1304: IF (p_match_str = ' AND ' OR p_party_max_score
1306: 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);
1307: END IF;
1308: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1302: RAISE FND_API.G_EXC_ERROR;
1303: END IF;
1304: IF (p_match_str = ' AND ' OR p_party_max_score
1306: 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);
1307: END IF;
1308: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1309: 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);
1310: END IF;
1305: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1306: 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);
1307: END IF;
1308: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1309: 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);
1310: END IF;
1311: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1312: 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);
1313: END IF;
1310: END IF;
1311: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1312: 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);
1313: END IF;
1314: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1315: ELSE
1316: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1317: 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);
1318: 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);
1313: END IF;
1314: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1315: ELSE
1316: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1317: 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);
1318: 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);
1319: END IF;
1320: RETURN l_party_contains_str;
1321: END IF;
1314: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1315: ELSE
1316: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1317: 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);
1318: 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);
1319: END IF;
1320: RETURN l_party_contains_str;
1321: END IF;
1322: END;
1316: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1317: 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);
1318: 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);
1319: END IF;
1320: RETURN l_party_contains_str;
1321: END IF;
1322: END;
1323: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1324: l_contains_str VARCHAR2(32000);
1320: RETURN l_party_contains_str;
1321: END IF;
1322: END;
1323: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1324: l_contains_str VARCHAR2(32000);
1325: l_contains_str_temp VARCHAR2(32000);
1326: l_den_contains_str VARCHAR2(32000);
1327: l_den_contains_str_temp VARCHAR2(32000);
1328: l_filter_str VARCHAR2(4000) := null;
1321: END IF;
1322: END;
1323: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1324: l_contains_str VARCHAR2(32000);
1325: l_contains_str_temp VARCHAR2(32000);
1326: l_den_contains_str VARCHAR2(32000);
1327: l_den_contains_str_temp VARCHAR2(32000);
1328: l_filter_str VARCHAR2(4000) := null;
1329: l_prim_temp VARCHAR2(4000) := null;
1322: END;
1323: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1324: l_contains_str VARCHAR2(32000);
1325: l_contains_str_temp VARCHAR2(32000);
1326: l_den_contains_str VARCHAR2(32000);
1327: l_den_contains_str_temp VARCHAR2(32000);
1328: l_filter_str VARCHAR2(4000) := null;
1329: l_prim_temp VARCHAR2(4000) := null;
1330: l_prim_temp_den VARCHAR2(4000) := null;
1323: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1324: l_contains_str VARCHAR2(32000);
1325: l_contains_str_temp VARCHAR2(32000);
1326: l_den_contains_str VARCHAR2(32000);
1327: l_den_contains_str_temp VARCHAR2(32000);
1328: l_filter_str VARCHAR2(4000) := null;
1329: l_prim_temp VARCHAR2(4000) := null;
1330: l_prim_temp_den VARCHAR2(4000) := null;
1331: BEGIN
1333: 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);
1334: END IF;
1335: x_denorm_str := NULL;
1336:
1337: -- Dynamic setup of party site contains str
1338: --
1339: -- For each primary transformation add to intermedia query if it
1340: -- is not null
1341: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1338: --
1339: -- For each primary transformation add to intermedia query if it
1340: -- is not null
1341: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1342: l_contains_str_temp := null;
1343: l_den_contains_str_temp := null;
1344: l_filter_str := null;
1345:
1346: -- Setup query string for ADDRESS
1339: -- For each primary transformation add to intermedia query if it
1340: -- is not null
1341: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1342: l_contains_str_temp := null;
1343: l_den_contains_str_temp := null;
1344: l_filter_str := null;
1345:
1346: -- Setup query string for ADDRESS
1347: l_prim_temp := null;
1347: l_prim_temp := null;
1348: l_prim_temp_den := null;
1349: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRADDRESS
1350: g_party_site_stage_list(I).TX4,'A27',l_prim_temp);
1351: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1352:
1353: -- Setup query string for CITY
1354: l_prim_temp := null;
1355: l_prim_temp_den := null;
1356: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1357: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1358: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1359: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1360: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1361: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1362:
1363: -- Setup query string for POSTAL_CODE
1364: l_prim_temp := null;
1357: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1358: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1359: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1360: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1361: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1362:
1363: -- Setup query string for POSTAL_CODE
1364: l_prim_temp := null;
1365: l_prim_temp_den := null;
1366: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1367: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1368: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1369: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1370: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1371: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1372:
1373: -- Setup query string for STATE
1374: l_prim_temp := null;
1367: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1368: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1369: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1370: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1371: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1372:
1373: -- Setup query string for STATE
1374: l_prim_temp := null;
1375: l_prim_temp_den := null;
1376: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1377: g_party_site_stage_list(I).TX15,'A32',l_prim_temp);
1378: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1379: g_party_site_stage_list(I).TX15,'D_PS',l_prim_temp_den);
1380: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1381: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1382:
1383: -- Setup query string for COUNTRY
1384: l_prim_temp := null;
1377: g_party_site_stage_list(I).TX15,'A32',l_prim_temp);
1378: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1379: g_party_site_stage_list(I).TX15,'D_PS',l_prim_temp_den);
1380: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1381: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1382:
1383: -- Setup query string for COUNTRY
1384: l_prim_temp := null;
1385: l_prim_temp_den := null;
1386: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1387: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1388: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1389: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1390: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1391: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1392:
1393: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1394: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1387: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1388: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1389: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1390: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1391: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1392:
1393: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1394: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1395: END LOOP;
1389: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1390: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1391: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1392:
1393: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1394: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1395: END LOOP;
1396: -- Add the search criteria to query string
1397: IF lengthb(l_contains_str) > 4000 THEN
1390: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1391: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1392:
1393: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1394: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1395: END LOOP;
1396: -- Add the search criteria to query string
1397: IF lengthb(l_contains_str) > 4000 THEN
1398: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1393: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1394: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1395: END LOOP;
1396: -- Add the search criteria to query string
1397: IF lengthb(l_contains_str) > 4000 THEN
1398: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1399: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1400: FND_MSG_PUB.ADD;
1401: RAISE FND_API.G_EXC_ERROR;
1399: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1400: FND_MSG_PUB.ADD;
1401: RAISE FND_API.G_EXC_ERROR;
1402: END IF;
1403: x_denorm_str := l_den_contains_str;
1404: RETURN l_contains_str;
1405: END;
1406:
1407: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1400: FND_MSG_PUB.ADD;
1401: RAISE FND_API.G_EXC_ERROR;
1402: END IF;
1403: x_denorm_str := l_den_contains_str;
1404: RETURN l_contains_str;
1405: END;
1406:
1407: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1408: l_contains_str VARCHAR2(32000);
1404: RETURN l_contains_str;
1405: END;
1406:
1407: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1408: l_contains_str VARCHAR2(32000);
1409: l_contains_str_temp VARCHAR2(32000);
1410: l_den_contains_str VARCHAR2(32000);
1411: l_den_contains_str_temp VARCHAR2(32000);
1412: l_filter_str VARCHAR2(4000) := null;
1405: END;
1406:
1407: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1408: l_contains_str VARCHAR2(32000);
1409: l_contains_str_temp VARCHAR2(32000);
1410: l_den_contains_str VARCHAR2(32000);
1411: l_den_contains_str_temp VARCHAR2(32000);
1412: l_filter_str VARCHAR2(4000) := null;
1413: l_prim_temp VARCHAR2(4000) := null;
1406:
1407: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1408: l_contains_str VARCHAR2(32000);
1409: l_contains_str_temp VARCHAR2(32000);
1410: l_den_contains_str VARCHAR2(32000);
1411: l_den_contains_str_temp VARCHAR2(32000);
1412: l_filter_str VARCHAR2(4000) := null;
1413: l_prim_temp VARCHAR2(4000) := null;
1414: l_prim_temp_den VARCHAR2(4000) := null;
1407: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1408: l_contains_str VARCHAR2(32000);
1409: l_contains_str_temp VARCHAR2(32000);
1410: l_den_contains_str VARCHAR2(32000);
1411: l_den_contains_str_temp VARCHAR2(32000);
1412: l_filter_str VARCHAR2(4000) := null;
1413: l_prim_temp VARCHAR2(4000) := null;
1414: l_prim_temp_den VARCHAR2(4000) := null;
1415: BEGIN
1420: RETURN NULL;
1421: END;
1422:
1423: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1424: l_contains_str VARCHAR2(32000);
1425: l_contains_str_temp VARCHAR2(32000);
1426: l_den_contains_str VARCHAR2(32000);
1427: l_den_contains_str_temp VARCHAR2(32000);
1428: l_filter_str VARCHAR2(4000) := null;
1421: END;
1422:
1423: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1424: l_contains_str VARCHAR2(32000);
1425: l_contains_str_temp VARCHAR2(32000);
1426: l_den_contains_str VARCHAR2(32000);
1427: l_den_contains_str_temp VARCHAR2(32000);
1428: l_filter_str VARCHAR2(4000) := null;
1429: l_prim_temp VARCHAR2(4000) := null;
1422:
1423: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1424: l_contains_str VARCHAR2(32000);
1425: l_contains_str_temp VARCHAR2(32000);
1426: l_den_contains_str VARCHAR2(32000);
1427: l_den_contains_str_temp VARCHAR2(32000);
1428: l_filter_str VARCHAR2(4000) := null;
1429: l_prim_temp VARCHAR2(4000) := null;
1430: l_prim_temp_den VARCHAR2(4000) := null;
1423: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1424: l_contains_str VARCHAR2(32000);
1425: l_contains_str_temp VARCHAR2(32000);
1426: l_den_contains_str VARCHAR2(32000);
1427: l_den_contains_str_temp VARCHAR2(32000);
1428: l_filter_str VARCHAR2(4000) := null;
1429: l_prim_temp VARCHAR2(4000) := null;
1430: l_prim_temp_den VARCHAR2(4000) := null;
1431: TYPE CONTACT_PT_REC_TYPE IS RECORD (
1446: p_dup_party_id NUMBER,
1447: p_restrict_sql VARCHAR2,
1448: p_match_str VARCHAR2,
1449: p_search_merged VARCHAR2,
1450: p_party_contains_str VARCHAR2,
1451: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1452: l_sqlstr VARCHAR2(4000);
1453: l_search_merged VARCHAR2(1);
1454: BEGIN
1463: END IF;
1464: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1465: 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);
1466: END IF;
1467: 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
1468: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1469: 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);
1470: END IF;
1471: OPEN x_cursor FOR
1507: l_sqlstr := l_sqlstr || ' (TX41 like :TX41||'' %'' ';
1508: l_sqlstr := l_sqlstr || ' )) ';
1509: l_sqlstr := l_sqlstr || ' ) ';
1510: END IF;
1511: 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
1512: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1513: 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);
1514: END IF;
1515: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1509: l_sqlstr := l_sqlstr || ' ) ';
1510: END IF;
1511: 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
1512: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1513: 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);
1514: END IF;
1515: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1516: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX10||'' %'' ';
1517: l_sqlstr := l_sqlstr || ' )) ';
1539: END IF;
1540: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1541: 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);
1542: END IF;
1543: 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
1544: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1545: 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);
1546: END IF;
1547: 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
1541: 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);
1542: END IF;
1543: 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
1544: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1545: 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);
1546: END IF;
1547: 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
1548: ,g_party_stage_rec.TX33
1549: ,g_party_stage_rec.TX33
1579: ,g_party_stage_rec.TX40
1580: ,g_party_stage_rec.TX41
1581: ,g_party_stage_rec.TX41
1582: ;
1583: 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
1584: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1585: 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);
1586: END IF;
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
1581: ,g_party_stage_rec.TX41
1582: ;
1583: 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
1584: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1585: 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);
1586: END IF;
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_site_stage_list(1).TX10
1589: ,g_party_site_stage_list(1).TX10
1604: END;
1605: PROCEDURE open_party_cursor(
1606: p_dup_party_id NUMBER,
1607: p_restrict_sql VARCHAR2,
1608: p_contains_str VARCHAR2,
1609: p_search_ctx_id NUMBER,
1610: p_match_str VARCHAR2,
1611: p_search_merged VARCHAR2,
1612: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1623: l_search_merged := 'N';
1624: ELSE
1625: l_search_merged := p_search_merged;
1626: END IF;
1627: IF p_contains_str IS NULL THEN
1628: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1629: 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);
1630: END IF;
1631: OPEN x_cursor FOR
1625: l_search_merged := p_search_merged;
1626: END IF;
1627: IF p_contains_str IS NULL THEN
1628: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1629: 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);
1630: END IF;
1631: OPEN x_cursor FOR
1632: SELECT PARTY_ID , TX32, TX33, TX34, TX35, TX40, TX39, lpad(rtrim(TX41),9,chr(48))
1633: FROM HZ_STAGED_PARTIES stage
1642: END IF;
1643: OPEN x_cursor FOR
1644: SELECT PARTY_ID , TX32, TX33, TX34, TX35, TX40, TX39, lpad(rtrim(TX41),9,chr(48))
1645: FROM HZ_STAGED_PARTIES stage
1646: WHERE contains( concat_col, p_contains_str)>0
1647: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1648: AND( (l_search_merged ='Y' )
1649: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1650: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )
1655: END IF;
1656: OPEN x_cursor FOR
1657: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX32, TX33, TX34, TX35, TX40, TX39, lpad(rtrim(TX41),9,chr(48))
1658: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1659: WHERE contains( concat_col, p_contains_str)>0
1660: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1661: AND d.party_id = stage.party_id
1662: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1663: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1678: END IF;
1679: IF p_search_ctx_id IS NULL THEN
1680: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX32, TX33, TX34, TX35, TX40, TX39, lpad(rtrim(TX41),9,chr(48))'||
1681: ' FROM HZ_STAGED_PARTIES stage'||
1682: ' WHERE contains( concat_col, :cont)>0'||
1683: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1684: ' AND ('||p_restrict_sql||')' ||
1685: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1686: IF l_search_merged = 'Y' THEN
1690: ELSE
1691: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1692: END IF;
1693: output_long_strings('----------------------------------------------------------');
1694: output_long_strings('Party Contains String = '||p_contains_str);
1695: output_long_strings('Restrict Sql = '||p_restrict_sql);
1696: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1697: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1698: ,p_dup_party_id, p_dup_party_id;
1692: END IF;
1693: output_long_strings('----------------------------------------------------------');
1694: output_long_strings('Party Contains String = '||p_contains_str);
1695: output_long_strings('Restrict Sql = '||p_restrict_sql);
1696: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1697: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1698: ,p_dup_party_id, p_dup_party_id;
1699: END IF;
1700: END IF;
1723: PROCEDURE open_party_site_cursor(
1724: p_dup_party_id NUMBER,
1725: p_party_id NUMBER,
1726: p_restrict_sql VARCHAR2,
1727: p_contains_str VARCHAR2,
1728: p_search_ctx_id NUMBER,
1729: p_search_merged VARCHAR2,
1730: p_search_rel_sites VARCHAR2,
1731: p_person_api VARCHAR2,
1755: IF p_search_rel_sites = 'N' THEN
1756: OPEN x_cursor FOR
1757: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22
1758: FROM HZ_STAGED_PARTY_SITES stage
1759: WHERE contains( concat_col, p_contains_str)>0
1760: AND( (l_search_merged ='Y' )
1761: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1762: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1763: AND stage.party_id = p_party_id;
1764: ELSE
1765: OPEN x_cursor FOR
1766: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22
1767: FROM HZ_STAGED_PARTY_SITES stage
1768: WHERE contains( concat_col, p_contains_str)>0
1769: AND( (l_search_merged ='Y' )
1770: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1771: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1772: AND stage.party_id = p_party_id
1772: AND stage.party_id = p_party_id
1773: UNION
1774: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22
1775: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1776: WHERE contains( concat_col, p_contains_str)>0
1777: AND( (l_search_merged ='Y' )
1778: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1779: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1780: AND r.object_id = p_party_id
1794: END IF;
1795: OPEN x_cursor FOR
1796: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22
1797: FROM HZ_STAGED_PARTY_SITES stage
1798: WHERE contains( concat_col, p_contains_str)>0
1799: AND EXISTS (
1800: SELECT 1 FROM HZ_STAGED_PARTIES p
1801: WHERE p.PARTY_ID = stage.PARTY_ID
1802: AND( (l_search_merged ='Y' )
1814: IF p_person_api = 'Y' THEN
1815: OPEN x_cursor FOR
1816: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22
1817: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1818: WHERE contains( concat_col, p_contains_str)>0
1819: AND( (l_search_merged ='Y' )
1820: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1821: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1822: AND d.search_context_id = p_search_ctx_id
1824: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1825: UNION
1826: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22
1827: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, 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
1837: ELSE
1838: OPEN x_cursor FOR
1839: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22
1840: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1841: WHERE contains( concat_col, p_contains_str)>0
1842: AND( (l_search_merged ='Y' )
1843: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1844: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1845: AND d.search_context_id = p_search_ctx_id
1882: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1883: END IF;
1884: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22'||
1885: ' FROM HZ_STAGED_PARTY_SITES stage'||
1886: ' WHERE contains( concat_col, :cont)>0'||
1887: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1888: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1889: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1890: ' AND (ORG_CONTACT_ID IS NULL ' ||
1892: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1893: ' UNION ' ||
1894: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, 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 IN ' ||
1902: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1903: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1904: ' and ('||p_restrict_sql1|| ') )' ||
1905: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1906: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1907: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1908: ELSE
1909: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22'||
1910: ' FROM HZ_STAGED_PARTY_SITES stage'||
1903: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1904: ' and ('||p_restrict_sql1|| ') )' ||
1905: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1906: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1907: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1908: ELSE
1909: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22'||
1910: ' FROM HZ_STAGED_PARTY_SITES stage'||
1911: ' WHERE contains( concat_col, :cont)>0'||
1907: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1908: ELSE
1909: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22'||
1910: ' FROM HZ_STAGED_PARTY_SITES stage'||
1911: ' WHERE contains( concat_col, :cont)>0'||
1912: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1913: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1914: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1915: ' AND EXISTS ('||
1917: ' WHERE p.party_id = stage.party_id ' ||
1918: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1919: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1920: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1921: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1922: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1923: ,p_dup_party_id, p_dup_party_id;
1924: END IF;
1925: ELSE
1924: END IF;
1925: ELSE
1926: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22'||
1927: ' FROM HZ_STAGED_PARTY_SITES stage'||
1928: ' WHERE contains( concat_col, :cont)>0'||
1929: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1930: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1931: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1932: ' AND EXISTS ('||
1934: ' WHERE p.party_id = stage.party_id ' ||
1935: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1936: ' AND ('||p_restrict_sql||')' ||
1937: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1938: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1939: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1940: ,p_dup_party_id, p_dup_party_id;
1941: END IF;
1942: END IF;
1940: ,p_dup_party_id, p_dup_party_id;
1941: END IF;
1942: END IF;
1943: output_long_strings('----------------------------------------------------------');
1944: output_long_strings('Party Site Contains String = '||p_contains_str);
1945: output_long_strings('Restrict Sql = '||p_restrict_sql);
1946: exception
1947: when others then
1948: if (instrb(SQLERRM,'DRG-51030')>0) then
1968: PROCEDURE open_contact_cursor(
1969: p_dup_party_id NUMBER,
1970: p_party_id NUMBER,
1971: p_restrict_sql VARCHAR2,
1972: p_contains_str VARCHAR2,
1973: p_search_ctx_id NUMBER,
1974: p_search_merged VARCHAR2,
1975: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1976: l_sqlstr VARCHAR2(4000);
1996: END IF;
1997: OPEN x_cursor FOR
1998: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1999: FROM HZ_STAGED_CONTACTS stage
2000: WHERE contains( concat_col, p_contains_str)>0
2001: AND EXISTS (
2002: SELECT 1 FROM HZ_STAGED_PARTIES p
2003: WHERE p.PARTY_ID = stage.PARTY_ID
2004: AND( (l_search_merged ='Y' )
2019: END IF;
2020: OPEN x_cursor FOR
2021: SELECT ORG_CONTACT_ID, PARTY_ID
2022: FROM HZ_STAGED_CONTACTS stage
2023: WHERE contains( concat_col, p_contains_str)>0
2024: AND EXISTS (
2025: SELECT 1 FROM HZ_STAGED_PARTIES p
2026: WHERE p.PARTY_ID = stage.PARTY_ID
2027: AND( (l_search_merged ='Y' )
2038: END IF;
2039: OPEN x_cursor FOR
2040: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
2041: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
2042: WHERE contains( concat_col, p_contains_str)>0
2043: AND d.search_context_id = p_search_ctx_id
2044: AND d.party_id = stage.party_id
2045: AND( (l_search_merged ='Y' )
2046: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2072: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
2073: END IF;
2074: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
2075: ' FROM HZ_STAGED_CONTACTS stage'||
2076: ' WHERE contains( concat_col, :cont)>0'||
2077: ' AND EXISTS ('||
2078: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2079: ' WHERE p.party_id = stage.party_id ' ||
2080: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2082: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2083: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2084: ' AND ('||p_restrict_sql||')' ||
2085: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2086: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2087: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2088: ,p_dup_party_id, p_dup_party_id;
2089: END IF;
2090: output_long_strings('----------------------------------------------------------');
2087: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2088: ,p_dup_party_id, p_dup_party_id;
2089: END IF;
2090: output_long_strings('----------------------------------------------------------');
2091: output_long_strings('Contacts Contains String = '||p_contains_str);
2092: output_long_strings('Restrict Sql = '||p_restrict_sql);
2093: exception
2094: when others then
2095: if (instrb(SQLERRM,'DRG-51030')>0) then
2115: PROCEDURE open_contact_pt_cursor(
2116: p_dup_party_id NUMBER,
2117: p_party_id NUMBER,
2118: p_restrict_sql VARCHAR2,
2119: p_contains_str VARCHAR2,
2120: p_search_ctx_id NUMBER,
2121: p_search_merged VARCHAR2,
2122: p_search_rel_cpts VARCHAR2,
2123: p_person_api VARCHAR2,
2145: THEN
2146: OPEN x_cursor FOR
2147: 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
2148: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2149: WHERE contains( concat_col, p_contains_str)>0
2150: AND d.search_context_id = p_search_ctx_id
2151: AND( (l_search_merged ='Y' )
2152: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2153: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2157: THEN
2158: OPEN x_cursor FOR
2159: 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
2160: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2161: WHERE contains( concat_col, p_contains_str)>0
2162: AND d.search_context_id = p_search_ctx_id
2163: AND( (l_search_merged ='Y' )
2164: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2165: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2174: IF p_search_rel_cpts = 'N' THEN
2175: OPEN x_cursor FOR
2176: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2177: FROM HZ_STAGED_CONTACT_POINTS stage
2178: WHERE contains( concat_col, p_contains_str)>0
2179: AND( (l_search_merged ='Y' )
2180: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2181: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2182: AND stage.party_id = p_party_id;
2183: ELSE
2184: OPEN x_cursor FOR
2185: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2186: FROM HZ_STAGED_CONTACT_POINTS stage
2187: WHERE contains( concat_col, p_contains_str)>0
2188: AND( (l_search_merged ='Y' )
2189: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2190: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2191: AND stage.party_id = p_party_id
2191: AND stage.party_id = p_party_id
2192: UNION
2193: 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
2194: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2195: WHERE contains( concat_col, p_contains_str)>0
2196: AND r.object_id = p_party_id
2197: AND( (l_search_merged ='Y' )
2198: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2199: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2213: END IF;
2214: OPEN x_cursor FOR
2215: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2216: FROM HZ_STAGED_CONTACT_POINTS stage
2217: WHERE contains( concat_col, p_contains_str)>0
2218: AND EXISTS (
2219: SELECT 1 FROM HZ_STAGED_PARTIES p
2220: WHERE p.PARTY_ID = stage.PARTY_ID
2221: AND( (l_search_merged ='Y' )
2233: IF p_person_api = 'Y' THEN
2234: OPEN x_cursor FOR
2235: 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
2236: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2237: WHERE contains( concat_col, p_contains_str)>0
2238: AND d.search_context_id = p_search_ctx_id
2239: AND d.party_id = stage.party_id
2240: AND( (l_search_merged ='Y' )
2241: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2243: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2244: UNION
2245: 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
2246: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, 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 = r.subject_id
2250: AND r.relationship_id = oc.party_relationship_id
2251: AND oc.org_contact_id = stage.org_contact_id
2256: ELSE
2257: OPEN x_cursor FOR
2258: 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
2259: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2260: WHERE contains( concat_col, p_contains_str)>0
2261: AND d.search_context_id = p_search_ctx_id
2262: AND d.party_id = stage.party_id
2263: AND( (l_search_merged ='Y' )
2264: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2301: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2302: END IF;
2303: 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 '||
2304: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2305: ' WHERE contains( concat_col, :cont)>0 '||
2306: ' AND (stage.org_contact_id is null '||
2307: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2308: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2309: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2311: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2312: ' UNION ' ||
2313: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2314: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2315: ' WHERE contains( concat_col, :cont)>0 '||
2316: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2317: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2318: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2319: ' AND (stage.org_contact_id in ' ||
2321: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2322: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2323: ' and ('||p_restrict_sql1 || ') ) )' ||
2324: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2325: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2326: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2327: ELSE
2328: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2329: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2322: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2323: ' and ('||p_restrict_sql1 || ') ) )' ||
2324: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2325: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2326: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2327: ELSE
2328: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2329: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2330: ' WHERE contains( concat_col, :cont)>0'||
2326: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2327: ELSE
2328: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2329: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2330: ' WHERE contains( concat_col, :cont)>0'||
2331: ' AND EXISTS ('||
2332: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2333: ' WHERE p.party_id = stage.party_id ' ||
2334: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2336: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2337: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2338: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2339: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2340: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2341: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2342: ,p_dup_party_id, p_dup_party_id;
2343: END IF;
2344: ELSE
2343: END IF;
2344: ELSE
2345: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2346: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2347: ' WHERE contains( concat_col, :cont)>0'||
2348: ' AND EXISTS ('||
2349: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2350: ' WHERE p.party_id = stage.party_id ' ||
2351: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2353: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2354: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2355: ' AND ('||p_restrict_sql||')' ||
2356: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2357: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2358: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2359: ,p_dup_party_id, p_dup_party_id;
2360: END IF;
2361: END IF;
2360: END IF;
2361: END IF;
2362: END IF;
2363: output_long_strings('----------------------------------------------------------');
2364: output_long_strings('Contact Points Contains String = '||p_contains_str);
2365: output_long_strings('Restrict Sql = '||p_restrict_sql);
2366: exception
2367: when others then
2368: if (instrb(SQLERRM,'DRG-51030')>0) then
2481: x_num_matches OUT NUMBER
2482: ) IS
2483:
2484: -- Strings to hold the generated Intermedia query strings
2485: l_party_contains_str VARCHAR2(32000);
2486: l_party_site_contains_str VARCHAR2(32000);
2487: l_contact_contains_str VARCHAR2(32000);
2488: l_contact_pt_contains_str VARCHAR2(32000);
2489: l_denorm_str VARCHAR2(32000);
2482: ) IS
2483:
2484: -- Strings to hold the generated Intermedia query strings
2485: l_party_contains_str VARCHAR2(32000);
2486: l_party_site_contains_str VARCHAR2(32000);
2487: l_contact_contains_str VARCHAR2(32000);
2488: l_contact_pt_contains_str VARCHAR2(32000);
2489: l_denorm_str VARCHAR2(32000);
2490: l_ps_denorm_str VARCHAR2(32000);
2483:
2484: -- Strings to hold the generated Intermedia query strings
2485: l_party_contains_str VARCHAR2(32000);
2486: l_party_site_contains_str VARCHAR2(32000);
2487: l_contact_contains_str VARCHAR2(32000);
2488: l_contact_pt_contains_str VARCHAR2(32000);
2489: l_denorm_str VARCHAR2(32000);
2490: l_ps_denorm_str VARCHAR2(32000);
2491: l_ct_denorm_str VARCHAR2(32000);
2484: -- Strings to hold the generated Intermedia query strings
2485: l_party_contains_str VARCHAR2(32000);
2486: l_party_site_contains_str VARCHAR2(32000);
2487: l_contact_contains_str VARCHAR2(32000);
2488: l_contact_pt_contains_str VARCHAR2(32000);
2489: l_denorm_str VARCHAR2(32000);
2490: l_ps_denorm_str VARCHAR2(32000);
2491: l_ct_denorm_str VARCHAR2(32000);
2492: l_cpt_denorm_str VARCHAR2(32000);
2584: call_type(2) := 'OR';
2585: END;
2586:
2587: /** Private procedure to acquire and score at party level ***/
2588: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2589: l_party_id_idx NUMBER:=1;
2590: l_ctx_id NUMBER;
2591: l_precalc_score BOOLEAN := FALSE;
2592: BEGIN
2615: l_search_ctx_id,H_PARTY_ID(I));
2616: l_party_id_idx := H_PARTY_ID.COUNT+1;
2617: ELSE
2618: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2619: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2620: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2621: 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);
2622: END IF;
2623: 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
2623: 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
2624: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2625: 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);
2626: END IF;
2627: IF (l_party_site_contains_str IS NULL AND
2628: l_contact_contains_str IS NULL AND
2629: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2630: 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);
2631: RAISE L_RETURN_IMM_EXC;
2624: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2625: 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);
2626: END IF;
2627: IF (l_party_site_contains_str IS NULL AND
2628: l_contact_contains_str IS NULL AND
2629: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2630: 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);
2631: RAISE L_RETURN_IMM_EXC;
2632: ELSE
2625: 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);
2626: END IF;
2627: IF (l_party_site_contains_str IS NULL AND
2628: l_contact_contains_str IS NULL AND
2629: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2630: 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);
2631: RAISE L_RETURN_IMM_EXC;
2632: ELSE
2633: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2629: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2630: 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);
2631: RAISE L_RETURN_IMM_EXC;
2632: ELSE
2633: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2634: END IF;
2635: ELSE
2636: IF p_index>1 THEN
2637: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2652: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2653: 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);
2654: END IF;
2655: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2656: 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);
2657: END IF;
2658: LOOP
2659: FETCH l_party_cur INTO
2660: l_party_id , l_TX32, l_TX33, l_TX34, l_TX35, l_TX40, l_TX39, l_TX41;
2674: l_party_id_idx:= l_party_id_idx+1;
2675: END IF;
2676: IF (l_party_id_idx-1)>l_max_thresh THEN
2677: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2678: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2679: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2680: AND l_contact_pt_contains_str IS NULL THEN
2681: EXIT;
2682: END IF;
2676: IF (l_party_id_idx-1)>l_max_thresh THEN
2677: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2678: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2679: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2680: AND l_contact_pt_contains_str IS NULL THEN
2681: EXIT;
2682: END IF;
2683: CLOSE l_party_cur;
2684: IF p_index>1 THEN
2705: ROLLBACK to eval_start;
2706: END;
2707:
2708: /** Private procedure to acquire and score at party site level ***/
2709: 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
2710: l_party_id_idx NUMBER:=1;
2711: l_ctx_id NUMBER;
2712: h_ps_id HZ_PARTY_SEARCH.IDList;
2713: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2717: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2718: 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);
2719: 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);
2720: END IF;
2721: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2722: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2723: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2724: l_party_id := H_SCORES.FIRST;
2725: WHILE l_party_id IS NOT NULL LOOP
2718: 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);
2719: 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);
2720: END IF;
2721: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2722: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2723: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2724: l_party_id := H_SCORES.FIRST;
2725: WHILE l_party_id IS NOT NULL LOOP
2726: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2719: 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);
2720: END IF;
2721: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2722: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2723: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2724: l_party_id := H_SCORES.FIRST;
2725: WHILE l_party_id IS NOT NULL LOOP
2726: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2727: l_party_id:=H_SCORES.NEXT(l_party_id);
2749: l_search_ctx_id,H_PARTY_ID(I));
2750: l_party_id_idx := H_PARTY_ID.COUNT+1;
2751: ELSE
2752: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2753: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2754: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2755: 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);
2756: END IF;
2757: IF p_index>1 THEN
2771: END IF;
2772: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2773: 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);
2774: END IF;
2775: 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);
2776: LOOP
2777: FETCH l_party_site_cur INTO
2778: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15, l_TX22;
2779: EXIT WHEN l_party_site_cur%NOTFOUND;
2827: END IF;
2828: END;
2829:
2830: /** Private procedure to acquire and score at contact point level ***/
2831: 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
2832: l_party_id_idx NUMBER:=1;
2833: l_ctx_id NUMBER;
2834: h_ct_id HZ_PARTY_SEARCH.IDList;
2835: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2861: l_search_ctx_id,H_PARTY_ID(I));
2862: l_party_id_idx := H_PARTY_ID.COUNT+1;
2863: ELSE
2864: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2865: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2866: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2867: 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);
2868: END IF;
2869: IF p_index>1 THEN
2883: END IF;
2884: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2885: 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);
2886: END IF;
2887: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2888: LOOP
2889: FETCH l_contact_cur INTO
2890: l_org_contact_id, l_ct_party_id ;
2891: EXIT WHEN l_contact_cur%NOTFOUND;
2936: END IF;
2937: END;
2938:
2939: /** Private procedure to acquire and score at contact point level ***/
2940: 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
2941: l_party_id_idx NUMBER:=1;
2942: l_ctx_id NUMBER;
2943: h_cpt_id HZ_PARTY_SEARCH.IDList;
2944: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2972: l_search_ctx_id,H_PARTY_ID(I));
2973: l_party_id_idx := H_PARTY_ID.COUNT+1;
2974: ELSE
2975: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2976: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2977: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2978: 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);
2979: END IF;
2980: IF p_index>1 THEN
2994: END IF;
2995: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2996: 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);
2997: END IF;
2998: 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);
2999: LOOP
3000: FETCH l_contact_pt_cur INTO
3001: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
3002: EXIT WHEN l_contact_pt_cur%NOTFOUND;
3175: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3176: l_threshold := round(( l_entered_max_score / 505) * 76);
3177: other_acq_criteria_exists := TRUE ;
3178: --check if acquisition criteria exists for any other entity
3179: IF l_party_contains_str IS NOT NULL THEN
3180: acq_cnt := 1;
3181: END IF;
3182: IF l_party_site_contains_str IS NOT NULL THEN
3183: acq_cnt := acq_cnt+1;
3178: --check if acquisition criteria exists for any other entity
3179: IF l_party_contains_str IS NOT NULL THEN
3180: acq_cnt := 1;
3181: END IF;
3182: IF l_party_site_contains_str IS NOT NULL THEN
3183: acq_cnt := acq_cnt+1;
3184: END IF;
3185: IF l_contact_contains_str IS NOT NULL THEN
3186: acq_cnt := acq_cnt+1;
3181: END IF;
3182: IF l_party_site_contains_str IS NOT NULL THEN
3183: acq_cnt := acq_cnt+1;
3184: END IF;
3185: IF l_contact_contains_str IS NOT NULL THEN
3186: acq_cnt := acq_cnt+1;
3187: END IF;
3188: IF l_contact_pt_contains_str IS NOT NULL THEN
3189: acq_cnt := acq_cnt+1;
3184: END IF;
3185: IF l_contact_contains_str IS NOT NULL THEN
3186: acq_cnt := acq_cnt+1;
3187: END IF;
3188: IF l_contact_pt_contains_str IS NOT NULL THEN
3189: acq_cnt := acq_cnt+1;
3190: END IF;
3191: IF acq_cnt>1 THEN
3192: other_acq_criteria_exists := TRUE;
3218: FND_MSG_PUB.ADD;
3219: RAISE FND_API.G_EXC_ERROR;
3220: END IF;
3221: END IF;
3222: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3223: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3224: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3225: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3226: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3219: RAISE FND_API.G_EXC_ERROR;
3220: END IF;
3221: END IF;
3222: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3223: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3224: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3225: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3226: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3227: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3220: END IF;
3221: END IF;
3222: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3223: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3224: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3225: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3226: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3227: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3228: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3221: END IF;
3222: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3223: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3224: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3225: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3226: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3227: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3228: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3229: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3222: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3223: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3224: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3225: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3226: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3227: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3228: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3229: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3230: END IF;
3223: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3224: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3225: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3226: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3227: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3228: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3229: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3230: END IF;
3231: END;
3224: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3225: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3226: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3227: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3228: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3229: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3230: END IF;
3231: END;
3232: /************ End of find_parties private procedures **********/
3225: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3226: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3227: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3228: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3229: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3230: END IF;
3231: END;
3232: /************ End of find_parties private procedures **********/
3233:
3297: 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);
3298: END IF;
3299: -- ************************************************************
3300: -- STEP 2. Setup of intermedia query strings for Acquisition query
3301: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3302: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3303: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3304: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3305: 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);
3298: END IF;
3299: -- ************************************************************
3300: -- STEP 2. Setup of intermedia query strings for Acquisition query
3301: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3302: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3303: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3304: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3305: 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);
3306: END IF;
3299: -- ************************************************************
3300: -- STEP 2. Setup of intermedia query strings for Acquisition query
3301: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3302: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3303: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3304: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3305: 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);
3306: END IF;
3307: l_denorm_max_score:=0;
3331: END IF;
3332: ELSE
3333: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3334: END IF;
3335: 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((76/505)*l_entered_max_score));
3336: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3337:
3338: -- Setup Search Context ID
3339: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3341:
3342: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3343: 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);
3344: END IF;
3345: IF l_party_contains_str IS NULL THEN
3346: defpt := 1;
3347: END IF;
3348: IF l_party_site_contains_str IS NULL THEN
3349: defps := 1;
3344: END IF;
3345: IF l_party_contains_str IS NULL THEN
3346: defpt := 1;
3347: END IF;
3348: IF l_party_site_contains_str IS NULL THEN
3349: defps := 1;
3350: END IF;
3351: IF l_contact_contains_str IS NULL THEN
3352: defct := 1;
3347: END IF;
3348: IF l_party_site_contains_str IS NULL THEN
3349: defps := 1;
3350: END IF;
3351: IF l_contact_contains_str IS NULL THEN
3352: defct := 1;
3353: END IF;
3354: IF l_contact_pt_contains_str IS NULL THEN
3355: defcpt := 1;
3350: END IF;
3351: IF l_contact_contains_str IS NULL THEN
3352: defct := 1;
3353: END IF;
3354: IF l_contact_pt_contains_str IS NULL THEN
3355: defcpt := 1;
3356: END IF;
3357:
3358: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3359: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3360: 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);
3361: END IF;
3362: 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);
3363: 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);
3364: 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);
3365: 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);
3366: 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);
3367: 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);
3360: 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);
3361: END IF;
3362: 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);
3363: 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);
3364: 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);
3365: 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);
3366: 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);
3367: 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);
3368: END IF;
3361: END IF;
3362: 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);
3363: 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);
3364: 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);
3365: 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);
3366: 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);
3367: 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);
3368: END IF;
3369: FOR I in 1..5 LOOP
3362: 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);
3363: 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);
3364: 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);
3365: 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);
3366: 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);
3367: 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);
3368: END IF;
3369: FOR I in 1..5 LOOP
3370: do_eval(I);
3444: x_num_matches OUT NUMBER
3445: ) IS
3446:
3447: -- Strings to hold the generated Intermedia query strings
3448: l_party_contains_str VARCHAR2(32000);
3449: l_party_site_contains_str VARCHAR2(32000);
3450: l_contact_contains_str VARCHAR2(32000);
3451: l_contact_pt_contains_str VARCHAR2(32000);
3452: l_denorm_str VARCHAR2(32000);
3445: ) IS
3446:
3447: -- Strings to hold the generated Intermedia query strings
3448: l_party_contains_str VARCHAR2(32000);
3449: l_party_site_contains_str VARCHAR2(32000);
3450: l_contact_contains_str VARCHAR2(32000);
3451: l_contact_pt_contains_str VARCHAR2(32000);
3452: l_denorm_str VARCHAR2(32000);
3453: l_ps_denorm_str VARCHAR2(32000);
3446:
3447: -- Strings to hold the generated Intermedia query strings
3448: l_party_contains_str VARCHAR2(32000);
3449: l_party_site_contains_str VARCHAR2(32000);
3450: l_contact_contains_str VARCHAR2(32000);
3451: l_contact_pt_contains_str VARCHAR2(32000);
3452: l_denorm_str VARCHAR2(32000);
3453: l_ps_denorm_str VARCHAR2(32000);
3454: l_ct_denorm_str VARCHAR2(32000);
3447: -- Strings to hold the generated Intermedia query strings
3448: l_party_contains_str VARCHAR2(32000);
3449: l_party_site_contains_str VARCHAR2(32000);
3450: l_contact_contains_str VARCHAR2(32000);
3451: l_contact_pt_contains_str VARCHAR2(32000);
3452: l_denorm_str VARCHAR2(32000);
3453: l_ps_denorm_str VARCHAR2(32000);
3454: l_ct_denorm_str VARCHAR2(32000);
3455: l_cpt_denorm_str VARCHAR2(32000);
3577: call_type(2) := 'OR';
3578: END;
3579:
3580: /** Private procedure to acquire and score at party level ***/
3581: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3582: l_party_id_idx NUMBER:=1;
3583: l_ctx_id NUMBER;
3584: BEGIN
3585: SAVEPOINT eval_start;
3598: l_search_ctx_id,H_PARTY_ID(I));
3599: l_party_id_idx := H_PARTY_ID.COUNT+1;
3600: ELSE
3601: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3602: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3603: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3604: 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);
3605: END IF;
3606: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3606: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3607: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3608: 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);
3609: END IF;
3610: IF (l_party_site_contains_str IS NULL AND
3611: l_contact_contains_str IS NULL AND
3612: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3613: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3614: RAISE L_RETURN_IMM_EXC;
3607: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3608: 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);
3609: END IF;
3610: IF (l_party_site_contains_str IS NULL AND
3611: l_contact_contains_str IS NULL AND
3612: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3613: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3614: RAISE L_RETURN_IMM_EXC;
3615: ELSE
3608: 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);
3609: END IF;
3610: IF (l_party_site_contains_str IS NULL AND
3611: l_contact_contains_str IS NULL AND
3612: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3613: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3614: RAISE L_RETURN_IMM_EXC;
3615: ELSE
3616: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3612: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3613: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3614: RAISE L_RETURN_IMM_EXC;
3615: ELSE
3616: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3617: END IF;
3618: ELSE
3619: IF p_index>1 THEN
3620: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3632: l_ctx_id := NULL;
3633: l_party_id_idx := H_PARTY_ID.COUNT+1;
3634: END IF;
3635: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3636: 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);
3637: END IF;
3638: LOOP
3639: FETCH l_party_cur INTO
3640: l_party_id , l_TX32, l_TX33, l_TX34, l_TX35, l_TX40, l_TX39, l_TX41;
3654: l_party_id_idx:= l_party_id_idx+1;
3655: END IF;
3656: IF (l_party_id_idx-1)>l_max_thresh THEN
3657: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3658: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3659: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3660: AND l_contact_pt_contains_str IS NULL THEN
3661: H_PARTY_ID.DELETE(l_party_id_idx-1);
3662: H_PARTY_ID_LIST.DELETE(l_index);
3656: IF (l_party_id_idx-1)>l_max_thresh THEN
3657: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3658: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3659: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3660: AND l_contact_pt_contains_str IS NULL THEN
3661: H_PARTY_ID.DELETE(l_party_id_idx-1);
3662: H_PARTY_ID_LIST.DELETE(l_index);
3663: H_SCORES.DELETE(l_index);
3664: EXIT;
3685: CLOSE l_party_cur;
3686: ROLLBACK to eval_start;
3687: END;
3688: PROCEDURE open_person_contact_cursor(
3689: p_contains_str VARCHAR2,
3690: p_search_ctx_id NUMBER,
3691: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3692: BEGIN
3693: OPEN x_cursor FOR
3692: BEGIN
3693: OPEN x_cursor FOR
3694: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3695: FROM HZ_STAGED_CONTACTS stage
3696: WHERE contains( concat_col, p_contains_str)>0
3697: AND ORG_CONTACT_ID in (
3698: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3699: org_contact_id
3700: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc
3716: end if;
3717: END;
3718:
3719: /** Private procedure to acquire and score at party site level ***/
3720: 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
3721: l_party_id_idx NUMBER:=1;
3722: l_ctx_id NUMBER;
3723: h_ps_id HZ_PARTY_SEARCH.IDList;
3724: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3744: ELSE
3745: l_party_id_idx := H_PARTY_ID.COUNT+1;
3746: l_ctx_id := NULL;
3747: END IF;
3748: 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);
3749: LOOP
3750: FETCH l_party_site_cur INTO
3751: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15, l_TX22;
3752: EXIT WHEN l_party_site_cur%NOTFOUND;
3815: END IF;
3816: END;
3817:
3818: /** Private procedure to acquire and score at party site level ***/
3819: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3820: l_party_id_idx NUMBER:=1;
3821: l_ctx_id NUMBER;
3822: h_ct_id HZ_PARTY_SEARCH.IDList;
3823: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3831: FORALL I in 1..H_PARTY_ID.COUNT
3832: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3833: l_search_ctx_id,H_PARTY_ID(I));
3834: l_party_id_idx := H_PARTY_ID.COUNT+1;
3835: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3836: LOOP
3837: FETCH l_contact_cur INTO
3838: l_org_contact_id, l_ct_party_id ;
3839: EXIT WHEN l_contact_cur%NOTFOUND;
3873: END IF;
3874: END;
3875:
3876: /** Private procedure to acquire and score at contact point level ***/
3877: 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
3878: l_party_id_idx NUMBER:=1;
3879: l_ctx_id NUMBER;
3880: h_cpt_id HZ_PARTY_SEARCH.IDList;
3881: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3918: END IF;
3919: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3920: 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);
3921: END IF;
3922: 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);
3923: LOOP
3924: FETCH l_contact_pt_cur INTO
3925: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
3926: EXIT WHEN l_contact_pt_cur%NOTFOUND;
4121: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
4122: l_threshold := round(( l_entered_max_score / 505) * 76);
4123: other_acq_criteria_exists := TRUE ;
4124: --check if acquisition criteria exists for any other entity
4125: IF l_party_contains_str IS NOT NULL THEN
4126: acq_cnt := 1;
4127: END IF;
4128: IF l_party_site_contains_str IS NOT NULL THEN
4129: acq_cnt := acq_cnt+1;
4124: --check if acquisition criteria exists for any other entity
4125: IF l_party_contains_str IS NOT NULL THEN
4126: acq_cnt := 1;
4127: END IF;
4128: IF l_party_site_contains_str IS NOT NULL THEN
4129: acq_cnt := acq_cnt+1;
4130: END IF;
4131: IF l_contact_contains_str IS NOT NULL THEN
4132: acq_cnt := acq_cnt+1;
4127: END IF;
4128: IF l_party_site_contains_str IS NOT NULL THEN
4129: acq_cnt := acq_cnt+1;
4130: END IF;
4131: IF l_contact_contains_str IS NOT NULL THEN
4132: acq_cnt := acq_cnt+1;
4133: END IF;
4134: IF l_contact_pt_contains_str IS NOT NULL THEN
4135: acq_cnt := acq_cnt+1;
4130: END IF;
4131: IF l_contact_contains_str IS NOT NULL THEN
4132: acq_cnt := acq_cnt+1;
4133: END IF;
4134: IF l_contact_pt_contains_str IS NOT NULL THEN
4135: acq_cnt := acq_cnt+1;
4136: END IF;
4137: IF acq_cnt>1 THEN
4138: other_acq_criteria_exists := TRUE;
4164: FND_MSG_PUB.ADD;
4165: RAISE FND_API.G_EXC_ERROR;
4166: END IF;
4167: END IF;
4168: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4169: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4170: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4171: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4172: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4165: RAISE FND_API.G_EXC_ERROR;
4166: END IF;
4167: END IF;
4168: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4169: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4170: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4171: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4172: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4173: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4166: END IF;
4167: END IF;
4168: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4169: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4170: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4171: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4172: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4173: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4174: END IF;
4167: END IF;
4168: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4169: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4170: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4171: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4172: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4173: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4174: END IF;
4175: END;
4168: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4169: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4170: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4171: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4172: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4173: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4174: END IF;
4175: END;
4176: /************ End of find_persons private procedures **********/
4169: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4170: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4171: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4172: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4173: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4174: END IF;
4175: END;
4176: /************ End of find_persons private procedures **********/
4177:
4204: g_score_until_thresh := false;
4205: END IF;
4206: -- ************************************************************
4207: -- STEP 2. Setup of intermedia query strings for Acquisition query
4208: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4209: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4210: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4211: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4212: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4205: END IF;
4206: -- ************************************************************
4207: -- STEP 2. Setup of intermedia query strings for Acquisition query
4208: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4209: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4210: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4211: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4212: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4213:
4206: -- ************************************************************
4207: -- STEP 2. Setup of intermedia query strings for Acquisition query
4208: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4209: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4210: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4211: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4212: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4213:
4214: -- Setup Search Context ID
4207: -- STEP 2. Setup of intermedia query strings for Acquisition query
4208: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4209: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4210: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4211: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4212: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4213:
4214: -- Setup Search Context ID
4215: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4214: -- Setup Search Context ID
4215: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4216: x_search_ctx_id := l_search_ctx_id;
4217:
4218: IF l_party_contains_str IS NULL THEN
4219: defpt := 1;
4220: END IF;
4221: IF l_party_site_contains_str IS NULL THEN
4222: defps := 1;
4217:
4218: IF l_party_contains_str IS NULL THEN
4219: defpt := 1;
4220: END IF;
4221: IF l_party_site_contains_str IS NULL THEN
4222: defps := 1;
4223: END IF;
4224: IF l_contact_contains_str IS NULL THEN
4225: defct := 1;
4220: END IF;
4221: IF l_party_site_contains_str IS NULL THEN
4222: defps := 1;
4223: END IF;
4224: IF l_contact_contains_str IS NULL THEN
4225: defct := 1;
4226: END IF;
4227: IF l_contact_pt_contains_str IS NULL THEN
4228: defcpt := 1;
4223: END IF;
4224: IF l_contact_contains_str IS NULL THEN
4225: defct := 1;
4226: END IF;
4227: IF l_contact_pt_contains_str IS NULL THEN
4228: defcpt := 1;
4229: END IF;
4230:
4231: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4229: END IF;
4230:
4231: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4232: 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);
4233: 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);
4234: 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);
4235: 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);
4236: 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);
4237: 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);
4230:
4231: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4232: 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);
4233: 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);
4234: 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);
4235: 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);
4236: 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);
4237: 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);
4238: END IF;
4231: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4232: 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);
4233: 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);
4234: 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);
4235: 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);
4236: 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);
4237: 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);
4238: END IF;
4239: FOR I in 1..5 LOOP
4232: 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);
4233: 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);
4234: 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);
4235: 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);
4236: 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);
4237: 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);
4238: END IF;
4239: FOR I in 1..5 LOOP
4240: do_eval(I);
4238: END IF;
4239: FOR I in 1..5 LOOP
4240: do_eval(I);
4241: END LOOP;
4242: IF l_contact_contains_str IS NOT NULL THEN
4243: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4244: END IF;
4245: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4246: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 15 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4239: FOR I in 1..5 LOOP
4240: do_eval(I);
4241: END LOOP;
4242: IF l_contact_contains_str IS NOT NULL THEN
4243: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4244: END IF;
4245: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4246: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 15 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4247: END IF;
5035: x_num_matches OUT NUMBER
5036: ) IS
5037:
5038: -- Strings to hold the generated Intermedia query strings
5039: l_party_contains_str VARCHAR2(32000);
5040: l_match_idx NUMBER;
5041: l_party_site_contains_str VARCHAR2(32000);
5042: l_contact_contains_str VARCHAR2(32000);
5043: l_contact_pt_contains_str VARCHAR2(32000);
5037:
5038: -- Strings to hold the generated Intermedia query strings
5039: l_party_contains_str VARCHAR2(32000);
5040: l_match_idx NUMBER;
5041: l_party_site_contains_str VARCHAR2(32000);
5042: l_contact_contains_str VARCHAR2(32000);
5043: l_contact_pt_contains_str VARCHAR2(32000);
5044: l_tmp VARCHAR2(32000);
5045:
5038: -- Strings to hold the generated Intermedia query strings
5039: l_party_contains_str VARCHAR2(32000);
5040: l_match_idx NUMBER;
5041: l_party_site_contains_str VARCHAR2(32000);
5042: l_contact_contains_str VARCHAR2(32000);
5043: l_contact_pt_contains_str VARCHAR2(32000);
5044: l_tmp VARCHAR2(32000);
5045:
5046: -- Other local variables
5039: l_party_contains_str VARCHAR2(32000);
5040: l_match_idx NUMBER;
5041: l_party_site_contains_str VARCHAR2(32000);
5042: l_contact_contains_str VARCHAR2(32000);
5043: l_contact_pt_contains_str VARCHAR2(32000);
5044: l_tmp VARCHAR2(32000);
5045:
5046: -- Other local variables
5047: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5120: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5121:
5122: -- ************************************************************
5123: -- STEP 2. Setup of intermedia query strings for Acquisition query
5124: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5125: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5126: 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);
5127:
5128: -- Setup Search Context ID
5121:
5122: -- ************************************************************
5123: -- STEP 2. Setup of intermedia query strings for Acquisition query
5124: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5125: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5126: 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);
5127:
5128: -- Setup Search Context ID
5129: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5130: x_search_ctx_id := l_search_ctx_id;
5131:
5132: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5133: 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);
5134: 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);
5135: 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);
5136: 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);
5137: END IF;
5138: IF l_party_site_contains_str IS NULL THEN
5131:
5132: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5133: 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);
5134: 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);
5135: 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);
5136: 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);
5137: END IF;
5138: IF l_party_site_contains_str IS NULL THEN
5139: defps := 1;
5134: 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);
5135: 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);
5136: 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);
5137: END IF;
5138: IF l_party_site_contains_str IS NULL THEN
5139: defps := 1;
5140: END IF;
5141: IF l_contact_pt_contains_str IS NULL THEN
5142: defcpt := 1;
5137: END IF;
5138: IF l_party_site_contains_str IS NULL THEN
5139: defps := 1;
5140: END IF;
5141: IF l_contact_pt_contains_str IS NULL THEN
5142: defcpt := 1;
5143: END IF;
5144:
5145: IF l_party_site_contains_str IS NOT NULL THEN
5141: IF l_contact_pt_contains_str IS NULL THEN
5142: defcpt := 1;
5143: END IF;
5144:
5145: IF l_party_site_contains_str IS NOT NULL THEN
5146: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5147: LOOP
5148: FETCH l_party_site_cur INTO
5149: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15, l_TX22;
5142: defcpt := 1;
5143: END IF;
5144:
5145: IF l_party_site_contains_str IS NOT NULL THEN
5146: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5147: LOOP
5148: FETCH l_party_site_cur INTO
5149: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15, l_TX22;
5150: EXIT WHEN l_party_site_cur%NOTFOUND;
5165: END LOOP;
5166: CLOSE l_party_site_cur;
5167: END IF;
5168:
5169: IF l_contact_pt_contains_str IS NOT NULL THEN
5170: SAVEPOINT eval_start;
5171: IF l_match_str = ' AND ' OR (
5172: ((l_cpt_max_score/l_entered_max_score)<(76/505))
5173: ) THEN
5173: ) THEN
5174: FORALL I in 1..l_match_ps_list.COUNT
5175: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5176: values (l_search_ctx_id,l_match_ps_list(I));
5177: 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');
5178: ELSE
5179: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5180: END IF;
5181: LOOP
5175: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5176: values (l_search_ctx_id,l_match_ps_list(I));
5177: 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');
5178: ELSE
5179: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5180: END IF;
5181: LOOP
5182: FETCH l_contact_pt_cur INTO
5183: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
5275: x_num_matches OUT NUMBER
5276: ) IS
5277:
5278: -- Strings to hold the generated Intermedia query strings
5279: l_party_contains_str VARCHAR2(32000);
5280: l_party_site_contains_str VARCHAR2(32000);
5281: l_contact_contains_str VARCHAR2(32000);
5282: l_contact_pt_contains_str VARCHAR2(32000);
5283: l_tmp VARCHAR2(32000);
5276: ) IS
5277:
5278: -- Strings to hold the generated Intermedia query strings
5279: l_party_contains_str VARCHAR2(32000);
5280: l_party_site_contains_str VARCHAR2(32000);
5281: l_contact_contains_str VARCHAR2(32000);
5282: l_contact_pt_contains_str VARCHAR2(32000);
5283: l_tmp VARCHAR2(32000);
5284:
5277:
5278: -- Strings to hold the generated Intermedia query strings
5279: l_party_contains_str VARCHAR2(32000);
5280: l_party_site_contains_str VARCHAR2(32000);
5281: l_contact_contains_str VARCHAR2(32000);
5282: l_contact_pt_contains_str VARCHAR2(32000);
5283: l_tmp VARCHAR2(32000);
5284:
5285: -- Other local variables
5278: -- Strings to hold the generated Intermedia query strings
5279: l_party_contains_str VARCHAR2(32000);
5280: l_party_site_contains_str VARCHAR2(32000);
5281: l_contact_contains_str VARCHAR2(32000);
5282: l_contact_pt_contains_str VARCHAR2(32000);
5283: l_tmp VARCHAR2(32000);
5284:
5285: -- Other local variables
5286: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5349: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5350:
5351: -- ************************************************************
5352: -- STEP 2. Setup of intermedia query strings for Acquisition query
5353: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5354: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5355: 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);
5356:
5357: -- Setup Search Context ID
5350:
5351: -- ************************************************************
5352: -- STEP 2. Setup of intermedia query strings for Acquisition query
5353: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5354: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5355: 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);
5356:
5357: -- Setup Search Context ID
5358: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5359: x_search_ctx_id := l_search_ctx_id;
5360:
5361: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5362: 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);
5363: 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);
5364: 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);
5365: 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);
5366: END IF;
5367: IF l_contact_contains_str IS NULL THEN
5360:
5361: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5362: 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);
5363: 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);
5364: 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);
5365: 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);
5366: END IF;
5367: IF l_contact_contains_str IS NULL THEN
5368: defct := 1;
5363: 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);
5364: 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);
5365: 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);
5366: END IF;
5367: IF l_contact_contains_str IS NULL THEN
5368: defct := 1;
5369: END IF;
5370: IF l_contact_pt_contains_str IS NULL THEN
5371: defcpt := 1;
5366: END IF;
5367: IF l_contact_contains_str IS NULL THEN
5368: defct := 1;
5369: END IF;
5370: IF l_contact_pt_contains_str IS NULL THEN
5371: defcpt := 1;
5372: END IF;
5373:
5374: IF l_contact_contains_str IS NOT NULL THEN
5370: IF l_contact_pt_contains_str IS NULL THEN
5371: defcpt := 1;
5372: END IF;
5373:
5374: IF l_contact_contains_str IS NOT NULL THEN
5375: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5376: LOOP
5377: FETCH l_contact_cur INTO
5378: l_org_contact_id, l_ct_party_id ;
5371: defcpt := 1;
5372: END IF;
5373:
5374: IF l_contact_contains_str IS NOT NULL THEN
5375: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5376: LOOP
5377: FETCH l_contact_cur INTO
5378: l_org_contact_id, l_ct_party_id ;
5379: EXIT WHEN l_contact_cur%NOTFOUND;
5392: END LOOP;
5393: CLOSE l_contact_cur;
5394: END IF;
5395:
5396: IF l_contact_pt_contains_str IS NOT NULL THEN
5397: SAVEPOINT eval_start;
5398: IF l_match_str = ' AND ' OR (
5399: ((l_cpt_max_score/l_entered_max_score)<(76/505))
5400: ) THEN
5400: ) THEN
5401: FORALL I in 1..l_match_contact_list.COUNT
5402: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5403: values (l_search_ctx_id,l_match_contact_list(I));
5404: 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');
5405: ELSE
5406: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5407: END IF;
5408: LOOP
5402: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5403: values (l_search_ctx_id,l_match_contact_list(I));
5404: 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');
5405: ELSE
5406: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5407: END IF;
5408: LOOP
5409: FETCH l_contact_pt_cur INTO
5410: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
5500: ) IS
5501:
5502:
5503: -- Strings to hold the generated Intermedia query strings
5504: l_contact_pt_contains_str VARCHAR2(32000);
5505: l_tmp VARCHAR2(32000);
5506:
5507: -- Other local variables
5508: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5558: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5559:
5560: -- ************************************************************
5561: -- STEP 2. Setup of intermedia query strings for Acquisition query
5562: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5563: 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);
5564:
5565: -- Setup Search Context ID
5566: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5566: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5567: x_search_ctx_id := l_search_ctx_id;
5568: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5569: 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);
5570: 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);
5571: 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);
5572: END IF;
5573:
5574: IF l_contact_pt_contains_str IS NOT NULL THEN
5570: 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);
5571: 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);
5572: END IF;
5573:
5574: IF l_contact_pt_contains_str IS NOT NULL THEN
5575: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5576: cnt := 1;
5577: LOOP
5578: FETCH l_contact_pt_cur INTO
5571: 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);
5572: END IF;
5573:
5574: IF l_contact_pt_contains_str IS NOT NULL THEN
5575: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5576: cnt := 1;
5577: LOOP
5578: FETCH l_contact_pt_cur INTO
5579: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
5642: x_search_ctx_id IN OUT NUMBER
5643: ) IS
5644:
5645: -- Strings to hold the generated Intermedia query strings
5646: l_party_contains_str VARCHAR2(32000);
5647: l_party_site_contains_str VARCHAR2(32000);
5648: l_contact_contains_str VARCHAR2(32000);
5649: l_contact_pt_contains_str VARCHAR2(32000);
5650: l_tmp VARCHAR2(32000);
5643: ) IS
5644:
5645: -- Strings to hold the generated Intermedia query strings
5646: l_party_contains_str VARCHAR2(32000);
5647: l_party_site_contains_str VARCHAR2(32000);
5648: l_contact_contains_str VARCHAR2(32000);
5649: l_contact_pt_contains_str VARCHAR2(32000);
5650: l_tmp VARCHAR2(32000);
5651:
5644:
5645: -- Strings to hold the generated Intermedia query strings
5646: l_party_contains_str VARCHAR2(32000);
5647: l_party_site_contains_str VARCHAR2(32000);
5648: l_contact_contains_str VARCHAR2(32000);
5649: l_contact_pt_contains_str VARCHAR2(32000);
5650: l_tmp VARCHAR2(32000);
5651:
5652: -- Other local variables
5645: -- Strings to hold the generated Intermedia query strings
5646: l_party_contains_str VARCHAR2(32000);
5647: l_party_site_contains_str VARCHAR2(32000);
5648: l_contact_contains_str VARCHAR2(32000);
5649: l_contact_pt_contains_str VARCHAR2(32000);
5650: l_tmp VARCHAR2(32000);
5651:
5652: -- Other local variables
5653: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5732: g_score_until_thresh := false;
5733: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5734: -- ************************************************************
5735: -- STEP 2. Setup of intermedia query strings for Acquisition query
5736: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5737: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5738: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5739: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5740:
5733: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5734: -- ************************************************************
5735: -- STEP 2. Setup of intermedia query strings for Acquisition query
5736: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5737: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5738: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5739: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5740:
5741: -- Setup Search Context ID
5734: -- ************************************************************
5735: -- STEP 2. Setup of intermedia query strings for Acquisition query
5736: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5737: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5738: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5739: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5740:
5741: -- Setup Search Context ID
5742: IF x_search_ctx_id IS NULL THEN
5754: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX32,l_TX33,l_TX34,l_TX35,l_TX40,l_TX39,l_TX41,1);
5755: END LOOP;
5756: CLOSE l_party_cur;
5757:
5758: IF l_party_site_contains_str IS NOT NULL THEN
5759: l_max_score := 0;
5760: l_max_id := 0;
5761: l_max_idx := 0;
5762: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5759: l_max_score := 0;
5760: l_max_id := 0;
5761: l_max_idx := 0;
5762: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5763: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5764: ELSE
5765: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5766: END IF;
5767: LOOP
5761: l_max_idx := 0;
5762: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5763: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5764: ELSE
5765: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5766: END IF;
5767: LOOP
5768: FETCH l_party_site_cur INTO
5769: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15, l_TX22;
5788: 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_TX14,l_max_TX15,l_max_TX22,l_max_idx);
5789: END IF;
5790: END IF;
5791:
5792: IF l_contact_contains_str IS NOT NULL THEN
5793: l_max_score := 0;
5794: l_max_id := 0;
5795: l_max_idx := 0;
5796: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5792: IF l_contact_contains_str IS NOT NULL THEN
5793: l_max_score := 0;
5794: l_max_id := 0;
5795: l_max_idx := 0;
5796: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5797: LOOP
5798: FETCH l_contact_cur INTO
5799: l_org_contact_id, l_ct_party_id ;
5800: EXIT WHEN l_contact_cur%NOTFOUND;
5810: 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_idx);
5811: END IF;
5812: END IF;
5813:
5814: IF l_contact_pt_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_cpt_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_cpt_info THEN
5819: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5820: ELSE
5821: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5822: END IF;
5823: LOOP
5817: l_max_idx := 0;
5818: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5819: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5820: ELSE
5821: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5822: END IF;
5823: LOOP
5824: FETCH l_contact_pt_cur INTO
5825: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;