[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:
1235: p_party_max_score NUMBER,
1236: p_denorm_max_score NUMBER,
1237: p_non_denorm_max_score NUMBER,
1238: p_threshold NUMBER) RETURN VARCHAR2 IS
1239: l_party_contains_str VARCHAR2(32000);
1240: l_party_filter VARCHAR2(1) := null;
1241: l_prim_temp VARCHAR2(4000);
1242: l_denorm_str VARCHAR2(4000);
1243: BEGIN
1279:
1280: IF l_denorm_str IS NOT NULL THEN
1281: IF (p_non_denorm_max_score+200)>=p_threshold THEN
1282: l_denorm_str := NULL;
1283: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1284: ELSIF (p_non_denorm_max_score+p_denorm_max_score+200)>=p_threshold THEN
1285: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1286: l_denorm_str := NULL;
1287: END IF;
1281: IF (p_non_denorm_max_score+200)>=p_threshold THEN
1282: l_denorm_str := NULL;
1283: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1284: ELSIF (p_non_denorm_max_score+p_denorm_max_score+200)>=p_threshold THEN
1285: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1286: l_denorm_str := NULL;
1287: END IF;
1288: ELSE
1289: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1285: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1286: l_denorm_str := NULL;
1287: END IF;
1288: ELSE
1289: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1290: END IF;
1291: IF lengthb(l_party_contains_str) > 4000 THEN
1292: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1293: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1287: END IF;
1288: ELSE
1289: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1290: END IF;
1291: IF lengthb(l_party_contains_str) > 4000 THEN
1292: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1293: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1294: FND_MSG_PUB.ADD;
1295: RAISE FND_API.G_EXC_ERROR;
1293: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1294: FND_MSG_PUB.ADD;
1295: RAISE FND_API.G_EXC_ERROR;
1296: END IF;
1297: IF (p_match_str = ' AND ' OR p_party_max_score
1299: 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);
1300: END IF;
1301: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1295: RAISE FND_API.G_EXC_ERROR;
1296: END IF;
1297: IF (p_match_str = ' AND ' OR p_party_max_score
1299: 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);
1300: END IF;
1301: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1302: 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);
1303: END IF;
1298: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1299: 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);
1300: END IF;
1301: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1302: 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);
1303: END IF;
1304: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1305: 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);
1306: END IF;
1303: END IF;
1304: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1305: 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);
1306: END IF;
1307: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1308: ELSE
1309: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1310: 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);
1311: 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);
1306: END IF;
1307: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1308: ELSE
1309: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1310: 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);
1311: 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);
1312: END IF;
1313: RETURN l_party_contains_str;
1314: END IF;
1307: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1308: ELSE
1309: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1310: 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);
1311: 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);
1312: END IF;
1313: RETURN l_party_contains_str;
1314: END IF;
1315: END;
1309: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1310: 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);
1311: 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);
1312: END IF;
1313: RETURN l_party_contains_str;
1314: END IF;
1315: END;
1316: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1317: l_contains_str VARCHAR2(32000);
1313: RETURN l_party_contains_str;
1314: END IF;
1315: END;
1316: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1317: l_contains_str VARCHAR2(32000);
1318: l_contains_str_temp VARCHAR2(32000);
1319: l_den_contains_str VARCHAR2(32000);
1320: l_den_contains_str_temp VARCHAR2(32000);
1321: l_filter_str VARCHAR2(4000) := null;
1314: END IF;
1315: END;
1316: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1317: l_contains_str VARCHAR2(32000);
1318: l_contains_str_temp VARCHAR2(32000);
1319: l_den_contains_str VARCHAR2(32000);
1320: l_den_contains_str_temp VARCHAR2(32000);
1321: l_filter_str VARCHAR2(4000) := null;
1322: l_prim_temp VARCHAR2(4000) := null;
1315: END;
1316: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1317: l_contains_str VARCHAR2(32000);
1318: l_contains_str_temp VARCHAR2(32000);
1319: l_den_contains_str VARCHAR2(32000);
1320: l_den_contains_str_temp VARCHAR2(32000);
1321: l_filter_str VARCHAR2(4000) := null;
1322: l_prim_temp VARCHAR2(4000) := null;
1323: l_prim_temp_den VARCHAR2(4000) := null;
1316: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1317: l_contains_str VARCHAR2(32000);
1318: l_contains_str_temp VARCHAR2(32000);
1319: l_den_contains_str VARCHAR2(32000);
1320: l_den_contains_str_temp VARCHAR2(32000);
1321: l_filter_str VARCHAR2(4000) := null;
1322: l_prim_temp VARCHAR2(4000) := null;
1323: l_prim_temp_den VARCHAR2(4000) := null;
1324: BEGIN
1326: 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);
1327: END IF;
1328: x_denorm_str := NULL;
1329:
1330: -- Dynamic setup of party site contains str
1331: --
1332: -- For each primary transformation add to intermedia query if it
1333: -- is not null
1334: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1331: --
1332: -- For each primary transformation add to intermedia query if it
1333: -- is not null
1334: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1335: l_contains_str_temp := null;
1336: l_den_contains_str_temp := null;
1337: l_filter_str := null;
1338:
1339: -- Setup query string for ADDRESS
1332: -- For each primary transformation add to intermedia query if it
1333: -- is not null
1334: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1335: l_contains_str_temp := null;
1336: l_den_contains_str_temp := null;
1337: l_filter_str := null;
1338:
1339: -- Setup query string for ADDRESS
1340: l_prim_temp := null;
1340: l_prim_temp := null;
1341: l_prim_temp_den := null;
1342: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRADDRESS
1343: g_party_site_stage_list(I).TX4,'A27',l_prim_temp);
1344: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1345:
1346: -- Setup query string for CITY
1347: l_prim_temp := null;
1348: l_prim_temp_den := null;
1349: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1350: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1351: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1352: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1353: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1354: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1355:
1356: -- Setup query string for POSTAL_CODE
1357: l_prim_temp := null;
1350: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1351: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1352: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1353: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1354: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1355:
1356: -- Setup query string for POSTAL_CODE
1357: l_prim_temp := null;
1358: l_prim_temp_den := null;
1359: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1360: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1361: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1362: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1363: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1364: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1365:
1366: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1367: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1360: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1361: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1362: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1363: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1364: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1365:
1366: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1367: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1368: END LOOP;
1362: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1363: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1364: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1365:
1366: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1367: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1368: END LOOP;
1369: -- Add the search criteria to query string
1370: IF lengthb(l_contains_str) > 4000 THEN
1363: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1364: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1365:
1366: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1367: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1368: END LOOP;
1369: -- Add the search criteria to query string
1370: IF lengthb(l_contains_str) > 4000 THEN
1371: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1366: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1367: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1368: END LOOP;
1369: -- Add the search criteria to query string
1370: IF lengthb(l_contains_str) > 4000 THEN
1371: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1372: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1373: FND_MSG_PUB.ADD;
1374: RAISE FND_API.G_EXC_ERROR;
1372: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1373: FND_MSG_PUB.ADD;
1374: RAISE FND_API.G_EXC_ERROR;
1375: END IF;
1376: x_denorm_str := l_den_contains_str;
1377: RETURN l_contains_str;
1378: END;
1379:
1380: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1373: FND_MSG_PUB.ADD;
1374: RAISE FND_API.G_EXC_ERROR;
1375: END IF;
1376: x_denorm_str := l_den_contains_str;
1377: RETURN l_contains_str;
1378: END;
1379:
1380: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1381: l_contains_str VARCHAR2(32000);
1377: RETURN l_contains_str;
1378: END;
1379:
1380: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1381: l_contains_str VARCHAR2(32000);
1382: l_contains_str_temp VARCHAR2(32000);
1383: l_den_contains_str VARCHAR2(32000);
1384: l_den_contains_str_temp VARCHAR2(32000);
1385: l_filter_str VARCHAR2(4000) := null;
1378: END;
1379:
1380: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1381: l_contains_str VARCHAR2(32000);
1382: l_contains_str_temp VARCHAR2(32000);
1383: l_den_contains_str VARCHAR2(32000);
1384: l_den_contains_str_temp VARCHAR2(32000);
1385: l_filter_str VARCHAR2(4000) := null;
1386: l_prim_temp VARCHAR2(4000) := null;
1379:
1380: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1381: l_contains_str VARCHAR2(32000);
1382: l_contains_str_temp VARCHAR2(32000);
1383: l_den_contains_str VARCHAR2(32000);
1384: l_den_contains_str_temp VARCHAR2(32000);
1385: l_filter_str VARCHAR2(4000) := null;
1386: l_prim_temp VARCHAR2(4000) := null;
1387: l_prim_temp_den VARCHAR2(4000) := null;
1380: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1381: l_contains_str VARCHAR2(32000);
1382: l_contains_str_temp VARCHAR2(32000);
1383: l_den_contains_str VARCHAR2(32000);
1384: l_den_contains_str_temp VARCHAR2(32000);
1385: l_filter_str VARCHAR2(4000) := null;
1386: l_prim_temp VARCHAR2(4000) := null;
1387: l_prim_temp_den VARCHAR2(4000) := null;
1388: BEGIN
1390: hz_utility_v2pub.debug(p_message=>'INIT_CONTACTS_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
1391: END IF;
1392: x_denorm_str := NULL;
1393:
1394: -- Dynamic setup of party site contains str
1395: --
1396: -- For each primary transformation add to intermedia query if it
1397: -- is not null
1398: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1395: --
1396: -- For each primary transformation add to intermedia query if it
1397: -- is not null
1398: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1399: l_contains_str_temp := null;
1400: l_den_contains_str_temp := null;
1401: l_filter_str := null;
1402:
1403: -- Setup query string for JOB_TITLE
1396: -- For each primary transformation add to intermedia query if it
1397: -- is not null
1398: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1399: l_contains_str_temp := null;
1400: l_den_contains_str_temp := null;
1401: l_filter_str := null;
1402:
1403: -- Setup query string for JOB_TITLE
1404: l_prim_temp := null;
1406: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1407: g_contact_stage_list(I).TX22,'A43',l_prim_temp);
1408: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1409: g_contact_stage_list(I).TX22,'D_CT',l_prim_temp_den);
1410: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1411: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1412:
1413: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1414: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1407: g_contact_stage_list(I).TX22,'A43',l_prim_temp);
1408: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1409: g_contact_stage_list(I).TX22,'D_CT',l_prim_temp_den);
1410: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1411: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1412:
1413: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1414: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1415: END LOOP;
1409: g_contact_stage_list(I).TX22,'D_CT',l_prim_temp_den);
1410: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1411: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1412:
1413: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1414: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1415: END LOOP;
1416: -- Add the search criteria to query string
1417: IF lengthb(l_contains_str) > 4000 THEN
1410: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1411: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1412:
1413: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1414: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1415: END LOOP;
1416: -- Add the search criteria to query string
1417: IF lengthb(l_contains_str) > 4000 THEN
1418: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1413: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1414: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1415: END LOOP;
1416: -- Add the search criteria to query string
1417: IF lengthb(l_contains_str) > 4000 THEN
1418: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1419: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
1420: FND_MSG_PUB.ADD;
1421: RAISE FND_API.G_EXC_ERROR;
1419: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
1420: FND_MSG_PUB.ADD;
1421: RAISE FND_API.G_EXC_ERROR;
1422: END IF;
1423: x_denorm_str := l_den_contains_str;
1424: RETURN l_contains_str;
1425: END;
1426:
1427: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1420: FND_MSG_PUB.ADD;
1421: RAISE FND_API.G_EXC_ERROR;
1422: END IF;
1423: x_denorm_str := l_den_contains_str;
1424: RETURN l_contains_str;
1425: END;
1426:
1427: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1428: l_contains_str VARCHAR2(32000);
1424: RETURN l_contains_str;
1425: END;
1426:
1427: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1428: l_contains_str VARCHAR2(32000);
1429: l_contains_str_temp VARCHAR2(32000);
1430: l_den_contains_str VARCHAR2(32000);
1431: l_den_contains_str_temp VARCHAR2(32000);
1432: l_filter_str VARCHAR2(4000) := null;
1425: END;
1426:
1427: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1428: l_contains_str VARCHAR2(32000);
1429: l_contains_str_temp VARCHAR2(32000);
1430: l_den_contains_str VARCHAR2(32000);
1431: l_den_contains_str_temp VARCHAR2(32000);
1432: l_filter_str VARCHAR2(4000) := null;
1433: l_prim_temp VARCHAR2(4000) := null;
1426:
1427: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1428: l_contains_str VARCHAR2(32000);
1429: l_contains_str_temp VARCHAR2(32000);
1430: l_den_contains_str VARCHAR2(32000);
1431: l_den_contains_str_temp VARCHAR2(32000);
1432: l_filter_str VARCHAR2(4000) := null;
1433: l_prim_temp VARCHAR2(4000) := null;
1434: l_prim_temp_den VARCHAR2(4000) := null;
1427: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1428: l_contains_str VARCHAR2(32000);
1429: l_contains_str_temp VARCHAR2(32000);
1430: l_den_contains_str VARCHAR2(32000);
1431: l_den_contains_str_temp VARCHAR2(32000);
1432: l_filter_str VARCHAR2(4000) := null;
1433: l_prim_temp VARCHAR2(4000) := null;
1434: l_prim_temp_den VARCHAR2(4000) := null;
1435: BEGIN
1437: hz_utility_v2pub.debug(p_message=>'INIT_CONTACT_POINTS_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
1438: END IF;
1439: x_denorm_str := NULL;
1440:
1441: -- Dynamic setup of party site contains str
1442: --
1443: -- For each primary transformation add to intermedia query if it
1444: -- is not null
1445: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1442: --
1443: -- For each primary transformation add to intermedia query if it
1444: -- is not null
1445: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1446: l_contains_str_temp := null;
1447: l_den_contains_str_temp := null;
1448: l_filter_str := null;
1449: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1450:
1443: -- For each primary transformation add to intermedia query if it
1444: -- is not null
1445: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1446: l_contains_str_temp := null;
1447: l_den_contains_str_temp := null;
1448: l_filter_str := null;
1449: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1450:
1451: -- Setup query string for EMAIL_ADDRESS
1452: l_prim_temp := null;
1453: l_prim_temp_den := null;
1454: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1455: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1456: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1457:
1458: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1459: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1460: END LOOP;
1454: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1455: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1456: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1457:
1458: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1459: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1460: END LOOP;
1461: -- Add the search criteria to query string
1462: IF lengthb(l_contains_str) > 4000 THEN
1455: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1456: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1457:
1458: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1459: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1460: END LOOP;
1461: -- Add the search criteria to query string
1462: IF lengthb(l_contains_str) > 4000 THEN
1463: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1458: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1459: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1460: END LOOP;
1461: -- Add the search criteria to query string
1462: IF lengthb(l_contains_str) > 4000 THEN
1463: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1464: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1465: FND_MSG_PUB.ADD;
1466: RAISE FND_API.G_EXC_ERROR;
1464: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1465: FND_MSG_PUB.ADD;
1466: RAISE FND_API.G_EXC_ERROR;
1467: END IF;
1468: x_denorm_str := l_den_contains_str;
1469: RETURN l_contains_str;
1470: END;
1471:
1472: PROCEDURE open_party_cursor_direct (
1465: FND_MSG_PUB.ADD;
1466: RAISE FND_API.G_EXC_ERROR;
1467: END IF;
1468: x_denorm_str := l_den_contains_str;
1469: RETURN l_contains_str;
1470: END;
1471:
1472: PROCEDURE open_party_cursor_direct (
1473: p_dup_party_id NUMBER,
1473: p_dup_party_id NUMBER,
1474: p_restrict_sql VARCHAR2,
1475: p_match_str VARCHAR2,
1476: p_search_merged VARCHAR2,
1477: p_party_contains_str VARCHAR2,
1478: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1479: l_sqlstr VARCHAR2(4000);
1480: l_search_merged VARCHAR2(1);
1481: BEGIN
1490: END IF;
1491: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1492: 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);
1493: END IF;
1494: 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
1495: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1496: 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);
1497: END IF;
1498: OPEN x_cursor FOR
1522: l_sqlstr := l_sqlstr || ' (TX40 like :TX40||'' %'' ';
1523: l_sqlstr := l_sqlstr || ' )) ';
1524: l_sqlstr := l_sqlstr || ' ) ';
1525: END IF;
1526: 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
1527: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1528: 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);
1529: END IF;
1530: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1524: l_sqlstr := l_sqlstr || ' ) ';
1525: END IF;
1526: 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
1527: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1528: 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);
1529: END IF;
1530: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1531: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX10||'' %'' ';
1532: l_sqlstr := l_sqlstr || ' )) ';
1548: END IF;
1549: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1550: 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);
1551: END IF;
1552: 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
1553: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1554: 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);
1555: END IF;
1556: 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
1550: 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);
1551: END IF;
1552: 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
1553: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1554: 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);
1555: END IF;
1556: 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
1557: ,g_party_stage_rec.TX40
1558: ,g_party_stage_rec.TX40
1568: 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
1569: ,g_party_stage_rec.TX40
1570: ,g_party_stage_rec.TX40
1571: ;
1572: 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
1573: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1574: 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);
1575: END IF;
1576: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id
1570: ,g_party_stage_rec.TX40
1571: ;
1572: 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
1573: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1574: 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);
1575: END IF;
1576: 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
1577: ,g_party_site_stage_list(1).TX10
1578: ,g_party_site_stage_list(1).TX10
1589: END;
1590: PROCEDURE open_party_cursor(
1591: p_dup_party_id NUMBER,
1592: p_restrict_sql VARCHAR2,
1593: p_contains_str VARCHAR2,
1594: p_search_ctx_id NUMBER,
1595: p_match_str VARCHAR2,
1596: p_search_merged VARCHAR2,
1597: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1608: l_search_merged := 'N';
1609: ELSE
1610: l_search_merged := p_search_merged;
1611: END IF;
1612: IF p_contains_str IS NULL THEN
1613: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1614: 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);
1615: END IF;
1616: OPEN x_cursor FOR
1610: l_search_merged := p_search_merged;
1611: END IF;
1612: IF p_contains_str IS NULL THEN
1613: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1614: 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);
1615: END IF;
1616: OPEN x_cursor FOR
1617: SELECT PARTY_ID , TX40, TX39
1618: FROM HZ_STAGED_PARTIES stage
1627: END IF;
1628: OPEN x_cursor FOR
1629: SELECT PARTY_ID , TX40, TX39
1630: FROM HZ_STAGED_PARTIES stage
1631: WHERE contains( concat_col, p_contains_str)>0
1632: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1633: AND( (l_search_merged ='Y' )
1634: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1635: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )
1640: END IF;
1641: OPEN x_cursor FOR
1642: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX40, TX39
1643: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1644: WHERE contains( concat_col, p_contains_str)>0
1645: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1646: AND d.party_id = stage.party_id
1647: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1648: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1663: END IF;
1664: IF p_search_ctx_id IS NULL THEN
1665: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX40, TX39'||
1666: ' FROM HZ_STAGED_PARTIES stage'||
1667: ' WHERE contains( concat_col, :cont)>0'||
1668: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1669: ' AND ('||p_restrict_sql||')' ||
1670: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1671: IF l_search_merged = 'Y' THEN
1675: ELSE
1676: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1677: END IF;
1678: output_long_strings('----------------------------------------------------------');
1679: output_long_strings('Party Contains String = '||p_contains_str);
1680: output_long_strings('Restrict Sql = '||p_restrict_sql);
1681: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1682: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1683: ,p_dup_party_id, p_dup_party_id;
1677: END IF;
1678: output_long_strings('----------------------------------------------------------');
1679: output_long_strings('Party Contains String = '||p_contains_str);
1680: output_long_strings('Restrict Sql = '||p_restrict_sql);
1681: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1682: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1683: ,p_dup_party_id, p_dup_party_id;
1684: END IF;
1685: END IF;
1708: PROCEDURE open_party_site_cursor(
1709: p_dup_party_id NUMBER,
1710: p_party_id NUMBER,
1711: p_restrict_sql VARCHAR2,
1712: p_contains_str VARCHAR2,
1713: p_search_ctx_id NUMBER,
1714: p_search_merged VARCHAR2,
1715: p_search_rel_sites VARCHAR2,
1716: p_person_api VARCHAR2,
1740: IF p_search_rel_sites = 'N' THEN
1741: OPEN x_cursor FOR
1742: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1743: FROM HZ_STAGED_PARTY_SITES stage
1744: WHERE contains( concat_col, p_contains_str)>0
1745: AND( (l_search_merged ='Y' )
1746: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1747: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1748: AND stage.party_id = p_party_id;
1749: ELSE
1750: OPEN x_cursor FOR
1751: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1752: FROM HZ_STAGED_PARTY_SITES stage
1753: WHERE contains( concat_col, p_contains_str)>0
1754: AND( (l_search_merged ='Y' )
1755: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1756: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1757: AND stage.party_id = p_party_id
1757: AND stage.party_id = p_party_id
1758: UNION
1759: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1760: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1761: WHERE contains( concat_col, p_contains_str)>0
1762: AND( (l_search_merged ='Y' )
1763: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1764: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1765: AND r.object_id = p_party_id
1779: END IF;
1780: OPEN x_cursor FOR
1781: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1782: FROM HZ_STAGED_PARTY_SITES stage
1783: WHERE contains( concat_col, p_contains_str)>0
1784: AND EXISTS (
1785: SELECT 1 FROM HZ_STAGED_PARTIES p
1786: WHERE p.PARTY_ID = stage.PARTY_ID
1787: AND( (l_search_merged ='Y' )
1799: IF p_person_api = 'Y' THEN
1800: OPEN x_cursor FOR
1801: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1802: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1803: WHERE contains( concat_col, p_contains_str)>0
1804: AND( (l_search_merged ='Y' )
1805: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1806: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1807: AND d.search_context_id = p_search_ctx_id
1809: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1810: UNION
1811: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1812: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1813: WHERE contains( concat_col, p_contains_str)>0
1814: AND( (l_search_merged ='Y' )
1815: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1816: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1817: AND d.search_context_id = p_search_ctx_id
1822: ELSE
1823: OPEN x_cursor FOR
1824: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1825: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1826: WHERE contains( concat_col, p_contains_str)>0
1827: AND( (l_search_merged ='Y' )
1828: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1829: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1830: AND d.search_context_id = p_search_ctx_id
1867: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1868: END IF;
1869: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22'||
1870: ' FROM HZ_STAGED_PARTY_SITES stage'||
1871: ' WHERE contains( concat_col, :cont)>0'||
1872: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1873: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1874: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1875: ' AND (ORG_CONTACT_ID IS NULL ' ||
1877: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1878: ' UNION ' ||
1879: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22'||
1880: ' FROM HZ_STAGED_PARTY_SITES stage'||
1881: ' WHERE contains( concat_col, :cont)>0'||
1882: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1883: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1884: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1885: ' AND ORG_CONTACT_ID IN ' ||
1887: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1888: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1889: ' and ('||p_restrict_sql1|| ') )' ||
1890: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1891: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1892: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1893: ELSE
1894: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22'||
1895: ' FROM HZ_STAGED_PARTY_SITES stage'||
1888: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1889: ' and ('||p_restrict_sql1|| ') )' ||
1890: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1891: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1892: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1893: ELSE
1894: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22'||
1895: ' FROM HZ_STAGED_PARTY_SITES stage'||
1896: ' WHERE contains( concat_col, :cont)>0'||
1892: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1893: ELSE
1894: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, 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 EXISTS ('||
1902: ' WHERE p.party_id = stage.party_id ' ||
1903: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1904: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1905: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1906: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1907: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1908: ,p_dup_party_id, p_dup_party_id;
1909: END IF;
1910: ELSE
1909: END IF;
1910: ELSE
1911: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22'||
1912: ' FROM HZ_STAGED_PARTY_SITES stage'||
1913: ' WHERE contains( concat_col, :cont)>0'||
1914: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1915: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1916: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1917: ' AND EXISTS ('||
1919: ' WHERE p.party_id = stage.party_id ' ||
1920: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1921: ' AND ('||p_restrict_sql||')' ||
1922: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1923: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1924: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1925: ,p_dup_party_id, p_dup_party_id;
1926: END IF;
1927: END IF;
1925: ,p_dup_party_id, p_dup_party_id;
1926: END IF;
1927: END IF;
1928: output_long_strings('----------------------------------------------------------');
1929: output_long_strings('Party Site Contains String = '||p_contains_str);
1930: output_long_strings('Restrict Sql = '||p_restrict_sql);
1931: exception
1932: when others then
1933: if (instrb(SQLERRM,'DRG-51030')>0) then
1953: PROCEDURE open_contact_cursor(
1954: p_dup_party_id NUMBER,
1955: p_party_id NUMBER,
1956: p_restrict_sql VARCHAR2,
1957: p_contains_str VARCHAR2,
1958: p_search_ctx_id NUMBER,
1959: p_search_merged VARCHAR2,
1960: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1961: l_sqlstr VARCHAR2(4000);
1981: END IF;
1982: OPEN x_cursor FOR
1983: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID, TX22
1984: FROM HZ_STAGED_CONTACTS stage
1985: WHERE contains( concat_col, p_contains_str)>0
1986: AND EXISTS (
1987: SELECT 1 FROM HZ_STAGED_PARTIES p
1988: WHERE p.PARTY_ID = stage.PARTY_ID
1989: AND( (l_search_merged ='Y' )
2004: END IF;
2005: OPEN x_cursor FOR
2006: SELECT ORG_CONTACT_ID, PARTY_ID, TX22
2007: FROM HZ_STAGED_CONTACTS stage
2008: WHERE contains( concat_col, p_contains_str)>0
2009: AND EXISTS (
2010: SELECT 1 FROM HZ_STAGED_PARTIES p
2011: WHERE p.PARTY_ID = stage.PARTY_ID
2012: AND( (l_search_merged ='Y' )
2023: END IF;
2024: OPEN x_cursor FOR
2025: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID, TX22
2026: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
2027: WHERE contains( concat_col, p_contains_str)>0
2028: AND d.search_context_id = p_search_ctx_id
2029: AND d.party_id = stage.party_id
2030: AND( (l_search_merged ='Y' )
2031: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2057: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
2058: END IF;
2059: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID , TX22'||
2060: ' FROM HZ_STAGED_CONTACTS stage'||
2061: ' WHERE contains( concat_col, :cont)>0'||
2062: ' AND EXISTS ('||
2063: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2064: ' WHERE p.party_id = stage.party_id ' ||
2065: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2067: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2068: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2069: ' AND ('||p_restrict_sql||')' ||
2070: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2071: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2072: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2073: ,p_dup_party_id, p_dup_party_id;
2074: END IF;
2075: output_long_strings('----------------------------------------------------------');
2072: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2073: ,p_dup_party_id, p_dup_party_id;
2074: END IF;
2075: output_long_strings('----------------------------------------------------------');
2076: output_long_strings('Contacts Contains String = '||p_contains_str);
2077: output_long_strings('Restrict Sql = '||p_restrict_sql);
2078: exception
2079: when others then
2080: if (instrb(SQLERRM,'DRG-51030')>0) then
2100: PROCEDURE open_contact_pt_cursor(
2101: p_dup_party_id NUMBER,
2102: p_party_id NUMBER,
2103: p_restrict_sql VARCHAR2,
2104: p_contains_str VARCHAR2,
2105: p_search_ctx_id NUMBER,
2106: p_search_merged VARCHAR2,
2107: p_search_rel_cpts VARCHAR2,
2108: p_person_api VARCHAR2,
2130: THEN
2131: OPEN x_cursor FOR
2132: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2133: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2134: WHERE contains( concat_col, p_contains_str)>0
2135: AND d.search_context_id = p_search_ctx_id
2136: AND( (l_search_merged ='Y' )
2137: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2138: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2142: THEN
2143: OPEN x_cursor FOR
2144: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N3) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2145: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2146: WHERE contains( concat_col, p_contains_str)>0
2147: AND d.search_context_id = p_search_ctx_id
2148: AND( (l_search_merged ='Y' )
2149: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2150: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2159: IF p_search_rel_cpts = 'N' THEN
2160: OPEN x_cursor FOR
2161: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2162: FROM HZ_STAGED_CONTACT_POINTS stage
2163: WHERE contains( concat_col, p_contains_str)>0
2164: AND( (l_search_merged ='Y' )
2165: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2166: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2167: AND stage.party_id = p_party_id;
2168: ELSE
2169: OPEN x_cursor FOR
2170: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2171: FROM HZ_STAGED_CONTACT_POINTS stage
2172: WHERE contains( concat_col, p_contains_str)>0
2173: AND( (l_search_merged ='Y' )
2174: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2175: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2176: AND stage.party_id = p_party_id
2176: AND stage.party_id = p_party_id
2177: UNION
2178: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ stage.CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , TX1, TX6, TX5
2179: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2180: WHERE contains( concat_col, p_contains_str)>0
2181: AND r.object_id = p_party_id
2182: AND( (l_search_merged ='Y' )
2183: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2184: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2198: END IF;
2199: OPEN x_cursor FOR
2200: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2201: FROM HZ_STAGED_CONTACT_POINTS stage
2202: WHERE contains( concat_col, p_contains_str)>0
2203: AND EXISTS (
2204: SELECT 1 FROM HZ_STAGED_PARTIES p
2205: WHERE p.PARTY_ID = stage.PARTY_ID
2206: AND( (l_search_merged ='Y' )
2218: IF p_person_api = 'Y' THEN
2219: OPEN x_cursor FOR
2220: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2221: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2222: WHERE contains( concat_col, p_contains_str)>0
2223: AND d.search_context_id = p_search_ctx_id
2224: AND d.party_id = stage.party_id
2225: AND( (l_search_merged ='Y' )
2226: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2228: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2229: UNION
2230: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, r.subject_id, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , TX1, TX6, TX5
2231: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2232: WHERE contains( concat_col, p_contains_str)>0
2233: AND d.search_context_id = p_search_ctx_id
2234: AND d.party_id = r.subject_id
2235: AND r.relationship_id = oc.party_relationship_id
2236: AND oc.org_contact_id = stage.org_contact_id
2241: ELSE
2242: OPEN x_cursor FOR
2243: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2244: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2245: WHERE contains( concat_col, p_contains_str)>0
2246: AND d.search_context_id = p_search_ctx_id
2247: AND d.party_id = stage.party_id
2248: AND( (l_search_merged ='Y' )
2249: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2286: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2287: END IF;
2288: l_sqlstr := ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2289: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2290: ' WHERE contains( concat_col, :cont)>0 '||
2291: ' AND (stage.org_contact_id is null '||
2292: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2293: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2294: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2296: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2297: ' UNION ' ||
2298: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2299: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2300: ' WHERE contains( concat_col, :cont)>0 '||
2301: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2302: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2303: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2304: ' AND (stage.org_contact_id in ' ||
2306: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2307: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2308: ' and ('||p_restrict_sql1 || ') ) )' ||
2309: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2310: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2311: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2312: ELSE
2313: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2314: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2307: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2308: ' and ('||p_restrict_sql1 || ') ) )' ||
2309: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2310: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2311: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2312: ELSE
2313: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2314: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2315: ' WHERE contains( concat_col, :cont)>0'||
2311: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2312: ELSE
2313: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2314: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2315: ' WHERE contains( concat_col, :cont)>0'||
2316: ' AND EXISTS ('||
2317: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2318: ' WHERE p.party_id = stage.party_id ' ||
2319: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2321: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2322: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2323: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2324: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2325: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2326: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2327: ,p_dup_party_id, p_dup_party_id;
2328: END IF;
2329: ELSE
2328: END IF;
2329: ELSE
2330: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2331: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2332: ' WHERE contains( concat_col, :cont)>0'||
2333: ' AND EXISTS ('||
2334: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2335: ' WHERE p.party_id = stage.party_id ' ||
2336: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2338: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2339: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2340: ' AND ('||p_restrict_sql||')' ||
2341: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2342: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2343: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2344: ,p_dup_party_id, p_dup_party_id;
2345: END IF;
2346: END IF;
2345: END IF;
2346: END IF;
2347: END IF;
2348: output_long_strings('----------------------------------------------------------');
2349: output_long_strings('Contact Points Contains String = '||p_contains_str);
2350: output_long_strings('Restrict Sql = '||p_restrict_sql);
2351: exception
2352: when others then
2353: if (instrb(SQLERRM,'DRG-51030')>0) then
2466: x_num_matches OUT NUMBER
2467: ) IS
2468:
2469: -- Strings to hold the generated Intermedia query strings
2470: l_party_contains_str VARCHAR2(32000);
2471: l_party_site_contains_str VARCHAR2(32000);
2472: l_contact_contains_str VARCHAR2(32000);
2473: l_contact_pt_contains_str VARCHAR2(32000);
2474: l_denorm_str VARCHAR2(32000);
2467: ) IS
2468:
2469: -- Strings to hold the generated Intermedia query strings
2470: l_party_contains_str VARCHAR2(32000);
2471: l_party_site_contains_str VARCHAR2(32000);
2472: l_contact_contains_str VARCHAR2(32000);
2473: l_contact_pt_contains_str VARCHAR2(32000);
2474: l_denorm_str VARCHAR2(32000);
2475: l_ps_denorm_str VARCHAR2(32000);
2468:
2469: -- Strings to hold the generated Intermedia query strings
2470: l_party_contains_str VARCHAR2(32000);
2471: l_party_site_contains_str VARCHAR2(32000);
2472: l_contact_contains_str VARCHAR2(32000);
2473: l_contact_pt_contains_str VARCHAR2(32000);
2474: l_denorm_str VARCHAR2(32000);
2475: l_ps_denorm_str VARCHAR2(32000);
2476: l_ct_denorm_str VARCHAR2(32000);
2469: -- Strings to hold the generated Intermedia query strings
2470: l_party_contains_str VARCHAR2(32000);
2471: l_party_site_contains_str VARCHAR2(32000);
2472: l_contact_contains_str VARCHAR2(32000);
2473: l_contact_pt_contains_str VARCHAR2(32000);
2474: l_denorm_str VARCHAR2(32000);
2475: l_ps_denorm_str VARCHAR2(32000);
2476: l_ct_denorm_str VARCHAR2(32000);
2477: l_cpt_denorm_str VARCHAR2(32000);
2571: call_type(2) := 'OR';
2572: END;
2573:
2574: /** Private procedure to acquire and score at party level ***/
2575: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2576: l_party_id_idx NUMBER:=1;
2577: l_ctx_id NUMBER;
2578: l_precalc_score BOOLEAN := FALSE;
2579: BEGIN
2602: l_search_ctx_id,H_PARTY_ID(I));
2603: l_party_id_idx := H_PARTY_ID.COUNT+1;
2604: ELSE
2605: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2606: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2607: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2608: 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);
2609: END IF;
2610: 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
2610: 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
2611: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2612: 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);
2613: END IF;
2614: IF (l_party_site_contains_str IS NULL AND
2615: l_contact_contains_str IS NULL AND
2616: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2617: 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);
2618: RAISE L_RETURN_IMM_EXC;
2611: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2612: 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);
2613: END IF;
2614: IF (l_party_site_contains_str IS NULL AND
2615: l_contact_contains_str IS NULL AND
2616: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2617: 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);
2618: RAISE L_RETURN_IMM_EXC;
2619: ELSE
2612: 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);
2613: END IF;
2614: IF (l_party_site_contains_str IS NULL AND
2615: l_contact_contains_str IS NULL AND
2616: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2617: 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);
2618: RAISE L_RETURN_IMM_EXC;
2619: ELSE
2620: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2616: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2617: 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);
2618: RAISE L_RETURN_IMM_EXC;
2619: ELSE
2620: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2621: END IF;
2622: ELSE
2623: IF p_index>1 THEN
2624: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2639: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2640: 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);
2641: END IF;
2642: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2643: 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);
2644: END IF;
2645: LOOP
2646: FETCH l_party_cur INTO
2647: l_party_id , l_TX40, l_TX39;
2661: l_party_id_idx:= l_party_id_idx+1;
2662: END IF;
2663: IF (l_party_id_idx-1)>l_max_thresh THEN
2664: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2665: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2666: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2667: AND l_contact_pt_contains_str IS NULL THEN
2668: EXIT;
2669: END IF;
2663: IF (l_party_id_idx-1)>l_max_thresh THEN
2664: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2665: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2666: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2667: AND l_contact_pt_contains_str IS NULL THEN
2668: EXIT;
2669: END IF;
2670: CLOSE l_party_cur;
2671: IF p_index>1 THEN
2692: ROLLBACK to eval_start;
2693: END;
2694:
2695: /** Private procedure to acquire and score at party site level ***/
2696: 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
2697: l_party_id_idx NUMBER:=1;
2698: l_ctx_id NUMBER;
2699: h_ps_id HZ_PARTY_SEARCH.IDList;
2700: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2704: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2705: 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);
2706: 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);
2707: END IF;
2708: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2709: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2710: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2711: l_party_id := H_SCORES.FIRST;
2712: WHILE l_party_id IS NOT NULL LOOP
2705: 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);
2706: 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);
2707: END IF;
2708: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2709: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2710: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2711: l_party_id := H_SCORES.FIRST;
2712: WHILE l_party_id IS NOT NULL LOOP
2713: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2706: 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);
2707: END IF;
2708: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2709: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2710: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2711: l_party_id := H_SCORES.FIRST;
2712: WHILE l_party_id IS NOT NULL LOOP
2713: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2714: l_party_id:=H_SCORES.NEXT(l_party_id);
2736: l_search_ctx_id,H_PARTY_ID(I));
2737: l_party_id_idx := H_PARTY_ID.COUNT+1;
2738: ELSE
2739: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2740: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2741: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2742: 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);
2743: END IF;
2744: IF p_index>1 THEN
2758: END IF;
2759: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2760: 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);
2761: END IF;
2762: 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);
2763: LOOP
2764: FETCH l_party_site_cur INTO
2765: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;
2766: EXIT WHEN l_party_site_cur%NOTFOUND;
2814: END IF;
2815: END;
2816:
2817: /** Private procedure to acquire and score at contact point level ***/
2818: 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
2819: l_party_id_idx NUMBER:=1;
2820: l_ctx_id NUMBER;
2821: h_ct_id HZ_PARTY_SEARCH.IDList;
2822: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2848: l_search_ctx_id,H_PARTY_ID(I));
2849: l_party_id_idx := H_PARTY_ID.COUNT+1;
2850: ELSE
2851: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2852: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2853: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2854: 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);
2855: END IF;
2856: IF p_index>1 THEN
2870: END IF;
2871: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2872: 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);
2873: END IF;
2874: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2875: LOOP
2876: FETCH l_contact_cur INTO
2877: l_org_contact_id, l_ct_party_id , l_TX22;
2878: EXIT WHEN l_contact_cur%NOTFOUND;
2923: END IF;
2924: END;
2925:
2926: /** Private procedure to acquire and score at contact point level ***/
2927: 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
2928: l_party_id_idx NUMBER:=1;
2929: l_ctx_id NUMBER;
2930: h_cpt_id HZ_PARTY_SEARCH.IDList;
2931: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2959: l_search_ctx_id,H_PARTY_ID(I));
2960: l_party_id_idx := H_PARTY_ID.COUNT+1;
2961: ELSE
2962: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2963: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2964: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2965: 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);
2966: END IF;
2967: IF p_index>1 THEN
2981: END IF;
2982: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2983: 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);
2984: END IF;
2985: 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);
2986: LOOP
2987: FETCH l_contact_pt_cur INTO
2988: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
2989: EXIT WHEN l_contact_pt_cur%NOTFOUND;
3081: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3082: l_threshold := 160;
3083: other_acq_criteria_exists := TRUE ;
3084: --check if acquisition criteria exists for any other entity
3085: IF l_party_contains_str IS NOT NULL THEN
3086: acq_cnt := 1;
3087: END IF;
3088: IF l_party_site_contains_str IS NOT NULL THEN
3089: acq_cnt := acq_cnt+1;
3084: --check if acquisition criteria exists for any other entity
3085: IF l_party_contains_str IS NOT NULL THEN
3086: acq_cnt := 1;
3087: END IF;
3088: IF l_party_site_contains_str IS NOT NULL THEN
3089: acq_cnt := acq_cnt+1;
3090: END IF;
3091: IF l_contact_contains_str IS NOT NULL THEN
3092: acq_cnt := acq_cnt+1;
3087: END IF;
3088: IF l_party_site_contains_str IS NOT NULL THEN
3089: acq_cnt := acq_cnt+1;
3090: END IF;
3091: IF l_contact_contains_str IS NOT NULL THEN
3092: acq_cnt := acq_cnt+1;
3093: END IF;
3094: IF l_contact_pt_contains_str IS NOT NULL THEN
3095: acq_cnt := acq_cnt+1;
3090: END IF;
3091: IF l_contact_contains_str IS NOT NULL THEN
3092: acq_cnt := acq_cnt+1;
3093: END IF;
3094: IF l_contact_pt_contains_str IS NOT NULL THEN
3095: acq_cnt := acq_cnt+1;
3096: END IF;
3097: IF acq_cnt>1 THEN
3098: other_acq_criteria_exists := TRUE;
3124: FND_MSG_PUB.ADD;
3125: RAISE FND_API.G_EXC_ERROR;
3126: END IF;
3127: END IF;
3128: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3129: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3130: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3131: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3132: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3125: RAISE FND_API.G_EXC_ERROR;
3126: END IF;
3127: END IF;
3128: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3129: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3130: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3131: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3132: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3133: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3126: END IF;
3127: END IF;
3128: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3129: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3130: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3131: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3132: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3133: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3134: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3127: END IF;
3128: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3129: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3130: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3131: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3132: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3133: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3134: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3135: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3128: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3129: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3130: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3131: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3132: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3133: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3134: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3135: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3136: END IF;
3129: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3130: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3131: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3132: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3133: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3134: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3135: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3136: END IF;
3137: END;
3130: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3131: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3132: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3133: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3134: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3135: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3136: END IF;
3137: END;
3138: /************ End of find_parties private procedures **********/
3131: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3132: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3133: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3134: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3135: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3136: END IF;
3137: END;
3138: /************ End of find_parties private procedures **********/
3139:
3214: 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);
3215: END IF;
3216: -- ************************************************************
3217: -- STEP 2. Setup of intermedia query strings for Acquisition query
3218: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3219: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3220: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3221: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3222: 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);
3215: END IF;
3216: -- ************************************************************
3217: -- STEP 2. Setup of intermedia query strings for Acquisition query
3218: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3219: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3220: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3221: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3222: 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);
3223: END IF;
3216: -- ************************************************************
3217: -- STEP 2. Setup of intermedia query strings for Acquisition query
3218: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3219: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3220: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3221: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3222: 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);
3223: END IF;
3224: l_denorm_max_score:=0;
3248: END IF;
3249: ELSE
3250: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3251: END IF;
3252: 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((160/620)*l_entered_max_score));
3253: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3254:
3255: -- Setup Search Context ID
3256: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3258:
3259: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3260: 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);
3261: END IF;
3262: IF l_party_contains_str IS NULL THEN
3263: defpt := 1;
3264: END IF;
3265: IF l_party_site_contains_str IS NULL THEN
3266: defps := 1;
3261: END IF;
3262: IF l_party_contains_str IS NULL THEN
3263: defpt := 1;
3264: END IF;
3265: IF l_party_site_contains_str IS NULL THEN
3266: defps := 1;
3267: END IF;
3268: IF l_contact_contains_str IS NULL THEN
3269: defct := 1;
3264: END IF;
3265: IF l_party_site_contains_str IS NULL THEN
3266: defps := 1;
3267: END IF;
3268: IF l_contact_contains_str IS NULL THEN
3269: defct := 1;
3270: END IF;
3271: IF l_contact_pt_contains_str IS NULL THEN
3272: defcpt := 1;
3267: END IF;
3268: IF l_contact_contains_str IS NULL THEN
3269: defct := 1;
3270: END IF;
3271: IF l_contact_pt_contains_str IS NULL THEN
3272: defcpt := 1;
3273: END IF;
3274:
3275: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3276: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3277: 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);
3278: END IF;
3279: 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);
3280: 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);
3281: 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);
3282: 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);
3283: 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);
3284: 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);
3277: 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);
3278: END IF;
3279: 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);
3280: 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);
3281: 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);
3282: 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);
3283: 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);
3284: 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);
3285: END IF;
3278: END IF;
3279: 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);
3280: 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);
3281: 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);
3282: 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);
3283: 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);
3284: 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);
3285: END IF;
3286: FOR I in 1..5 LOOP
3279: 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);
3280: 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);
3281: 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);
3282: 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);
3283: 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);
3284: 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);
3285: END IF;
3286: FOR I in 1..5 LOOP
3287: do_eval(I);
3393: x_num_matches OUT NUMBER
3394: ) IS
3395:
3396: -- Strings to hold the generated Intermedia query strings
3397: l_party_contains_str VARCHAR2(32000);
3398: l_party_site_contains_str VARCHAR2(32000);
3399: l_contact_contains_str VARCHAR2(32000);
3400: l_contact_pt_contains_str VARCHAR2(32000);
3401: l_denorm_str VARCHAR2(32000);
3394: ) IS
3395:
3396: -- Strings to hold the generated Intermedia query strings
3397: l_party_contains_str VARCHAR2(32000);
3398: l_party_site_contains_str VARCHAR2(32000);
3399: l_contact_contains_str VARCHAR2(32000);
3400: l_contact_pt_contains_str VARCHAR2(32000);
3401: l_denorm_str VARCHAR2(32000);
3402: l_ps_denorm_str VARCHAR2(32000);
3395:
3396: -- Strings to hold the generated Intermedia query strings
3397: l_party_contains_str VARCHAR2(32000);
3398: l_party_site_contains_str VARCHAR2(32000);
3399: l_contact_contains_str VARCHAR2(32000);
3400: l_contact_pt_contains_str VARCHAR2(32000);
3401: l_denorm_str VARCHAR2(32000);
3402: l_ps_denorm_str VARCHAR2(32000);
3403: l_ct_denorm_str VARCHAR2(32000);
3396: -- Strings to hold the generated Intermedia query strings
3397: l_party_contains_str VARCHAR2(32000);
3398: l_party_site_contains_str VARCHAR2(32000);
3399: l_contact_contains_str VARCHAR2(32000);
3400: l_contact_pt_contains_str VARCHAR2(32000);
3401: l_denorm_str VARCHAR2(32000);
3402: l_ps_denorm_str VARCHAR2(32000);
3403: l_ct_denorm_str VARCHAR2(32000);
3404: l_cpt_denorm_str VARCHAR2(32000);
3528: call_type(2) := 'OR';
3529: END;
3530:
3531: /** Private procedure to acquire and score at party level ***/
3532: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3533: l_party_id_idx NUMBER:=1;
3534: l_ctx_id NUMBER;
3535: BEGIN
3536: SAVEPOINT eval_start;
3549: l_search_ctx_id,H_PARTY_ID(I));
3550: l_party_id_idx := H_PARTY_ID.COUNT+1;
3551: ELSE
3552: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3553: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3554: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3555: 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);
3556: END IF;
3557: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3557: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3558: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3559: 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);
3560: END IF;
3561: IF (l_party_site_contains_str IS NULL AND
3562: l_contact_contains_str IS NULL AND
3563: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3564: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3565: RAISE L_RETURN_IMM_EXC;
3558: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3559: 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);
3560: END IF;
3561: IF (l_party_site_contains_str IS NULL AND
3562: l_contact_contains_str IS NULL AND
3563: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3564: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3565: RAISE L_RETURN_IMM_EXC;
3566: ELSE
3559: 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);
3560: END IF;
3561: IF (l_party_site_contains_str IS NULL AND
3562: l_contact_contains_str IS NULL AND
3563: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3564: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3565: RAISE L_RETURN_IMM_EXC;
3566: ELSE
3567: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3563: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3564: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3565: RAISE L_RETURN_IMM_EXC;
3566: ELSE
3567: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3568: END IF;
3569: ELSE
3570: IF p_index>1 THEN
3571: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3583: l_ctx_id := NULL;
3584: l_party_id_idx := H_PARTY_ID.COUNT+1;
3585: END IF;
3586: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3587: 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);
3588: END IF;
3589: LOOP
3590: FETCH l_party_cur INTO
3591: l_party_id , l_TX40, l_TX39;
3605: l_party_id_idx:= l_party_id_idx+1;
3606: END IF;
3607: IF (l_party_id_idx-1)>l_max_thresh THEN
3608: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3609: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3610: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3611: AND l_contact_pt_contains_str IS NULL THEN
3612: H_PARTY_ID.DELETE(l_party_id_idx-1);
3613: H_PARTY_ID_LIST.DELETE(l_index);
3607: IF (l_party_id_idx-1)>l_max_thresh THEN
3608: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3609: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3610: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3611: AND l_contact_pt_contains_str IS NULL THEN
3612: H_PARTY_ID.DELETE(l_party_id_idx-1);
3613: H_PARTY_ID_LIST.DELETE(l_index);
3614: H_SCORES.DELETE(l_index);
3615: EXIT;
3636: CLOSE l_party_cur;
3637: ROLLBACK to eval_start;
3638: END;
3639: PROCEDURE open_person_contact_cursor(
3640: p_contains_str VARCHAR2,
3641: p_search_ctx_id NUMBER,
3642: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3643: BEGIN
3644: OPEN x_cursor FOR
3643: BEGIN
3644: OPEN x_cursor FOR
3645: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID, TX22
3646: FROM HZ_STAGED_CONTACTS stage
3647: WHERE contains( concat_col, p_contains_str)>0
3648: AND ORG_CONTACT_ID in (
3649: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3650: org_contact_id
3651: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc
3667: end if;
3668: END;
3669:
3670: /** Private procedure to acquire and score at party site level ***/
3671: 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
3672: l_party_id_idx NUMBER:=1;
3673: l_ctx_id NUMBER;
3674: h_ps_id HZ_PARTY_SEARCH.IDList;
3675: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3695: ELSE
3696: l_party_id_idx := H_PARTY_ID.COUNT+1;
3697: l_ctx_id := NULL;
3698: END IF;
3699: 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);
3700: LOOP
3701: FETCH l_party_site_cur INTO
3702: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;
3703: EXIT WHEN l_party_site_cur%NOTFOUND;
3766: END IF;
3767: END;
3768:
3769: /** Private procedure to acquire and score at party site level ***/
3770: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3771: l_party_id_idx NUMBER:=1;
3772: l_ctx_id NUMBER;
3773: h_ct_id HZ_PARTY_SEARCH.IDList;
3774: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3782: FORALL I in 1..H_PARTY_ID.COUNT
3783: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3784: l_search_ctx_id,H_PARTY_ID(I));
3785: l_party_id_idx := H_PARTY_ID.COUNT+1;
3786: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3787: LOOP
3788: FETCH l_contact_cur INTO
3789: l_org_contact_id, l_ct_party_id , l_TX22;
3790: EXIT WHEN l_contact_cur%NOTFOUND;
3824: END IF;
3825: END;
3826:
3827: /** Private procedure to acquire and score at contact point level ***/
3828: 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
3829: l_party_id_idx NUMBER:=1;
3830: l_ctx_id NUMBER;
3831: h_cpt_id HZ_PARTY_SEARCH.IDList;
3832: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3869: END IF;
3870: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3871: 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);
3872: END IF;
3873: 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);
3874: LOOP
3875: FETCH l_contact_pt_cur INTO
3876: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
3877: EXIT WHEN l_contact_pt_cur%NOTFOUND;
3977: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3978: l_threshold := 160;
3979: other_acq_criteria_exists := TRUE ;
3980: --check if acquisition criteria exists for any other entity
3981: IF l_party_contains_str IS NOT NULL THEN
3982: acq_cnt := 1;
3983: END IF;
3984: IF l_party_site_contains_str IS NOT NULL THEN
3985: acq_cnt := acq_cnt+1;
3980: --check if acquisition criteria exists for any other entity
3981: IF l_party_contains_str IS NOT NULL THEN
3982: acq_cnt := 1;
3983: END IF;
3984: IF l_party_site_contains_str IS NOT NULL THEN
3985: acq_cnt := acq_cnt+1;
3986: END IF;
3987: IF l_contact_contains_str IS NOT NULL THEN
3988: acq_cnt := acq_cnt+1;
3983: END IF;
3984: IF l_party_site_contains_str IS NOT NULL THEN
3985: acq_cnt := acq_cnt+1;
3986: END IF;
3987: IF l_contact_contains_str IS NOT NULL THEN
3988: acq_cnt := acq_cnt+1;
3989: END IF;
3990: IF l_contact_pt_contains_str IS NOT NULL THEN
3991: acq_cnt := acq_cnt+1;
3986: END IF;
3987: IF l_contact_contains_str IS NOT NULL THEN
3988: acq_cnt := acq_cnt+1;
3989: END IF;
3990: IF l_contact_pt_contains_str IS NOT NULL THEN
3991: acq_cnt := acq_cnt+1;
3992: END IF;
3993: IF acq_cnt>1 THEN
3994: other_acq_criteria_exists := TRUE;
4020: FND_MSG_PUB.ADD;
4021: RAISE FND_API.G_EXC_ERROR;
4022: END IF;
4023: END IF;
4024: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4025: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4026: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4027: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4028: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4021: RAISE FND_API.G_EXC_ERROR;
4022: END IF;
4023: END IF;
4024: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4025: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4026: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4027: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4028: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4029: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4022: END IF;
4023: END IF;
4024: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4025: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4026: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4027: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4028: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4029: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4030: END IF;
4023: END IF;
4024: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4025: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4026: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4027: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4028: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4029: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4030: END IF;
4031: END;
4024: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4025: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4026: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4027: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4028: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4029: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4030: END IF;
4031: END;
4032: /************ End of find_persons private procedures **********/
4025: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4026: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4027: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4028: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4029: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4030: END IF;
4031: END;
4032: /************ End of find_persons private procedures **********/
4033:
4060: g_score_until_thresh := false;
4061: END IF;
4062: -- ************************************************************
4063: -- STEP 2. Setup of intermedia query strings for Acquisition query
4064: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4065: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4066: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4067: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4068: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4061: END IF;
4062: -- ************************************************************
4063: -- STEP 2. Setup of intermedia query strings for Acquisition query
4064: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4065: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4066: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4067: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4068: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4069:
4062: -- ************************************************************
4063: -- STEP 2. Setup of intermedia query strings for Acquisition query
4064: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4065: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4066: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4067: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4068: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4069:
4070: -- Setup Search Context ID
4063: -- STEP 2. Setup of intermedia query strings for Acquisition query
4064: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4065: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4066: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4067: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4068: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4069:
4070: -- Setup Search Context ID
4071: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4070: -- Setup Search Context ID
4071: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4072: x_search_ctx_id := l_search_ctx_id;
4073:
4074: IF l_party_contains_str IS NULL THEN
4075: defpt := 1;
4076: END IF;
4077: IF l_party_site_contains_str IS NULL THEN
4078: defps := 1;
4073:
4074: IF l_party_contains_str IS NULL THEN
4075: defpt := 1;
4076: END IF;
4077: IF l_party_site_contains_str IS NULL THEN
4078: defps := 1;
4079: END IF;
4080: IF l_contact_contains_str IS NULL THEN
4081: defct := 1;
4076: END IF;
4077: IF l_party_site_contains_str IS NULL THEN
4078: defps := 1;
4079: END IF;
4080: IF l_contact_contains_str IS NULL THEN
4081: defct := 1;
4082: END IF;
4083: IF l_contact_pt_contains_str IS NULL THEN
4084: defcpt := 1;
4079: END IF;
4080: IF l_contact_contains_str IS NULL THEN
4081: defct := 1;
4082: END IF;
4083: IF l_contact_pt_contains_str IS NULL THEN
4084: defcpt := 1;
4085: END IF;
4086:
4087: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4085: END IF;
4086:
4087: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4088: 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);
4089: 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);
4090: 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);
4091: 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);
4092: 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);
4093: 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);
4086:
4087: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4088: 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);
4089: 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);
4090: 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);
4091: 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);
4092: 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);
4093: 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);
4094: END IF;
4087: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4088: 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);
4089: 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);
4090: 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);
4091: 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);
4092: 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);
4093: 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);
4094: END IF;
4095: FOR I in 1..5 LOOP
4088: 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);
4089: 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);
4090: 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);
4091: 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);
4092: 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);
4093: 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);
4094: END IF;
4095: FOR I in 1..5 LOOP
4096: do_eval(I);
4094: END IF;
4095: FOR I in 1..5 LOOP
4096: do_eval(I);
4097: END LOOP;
4098: IF l_contact_contains_str IS NOT NULL THEN
4099: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4100: END IF;
4101: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4102: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 26 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4095: FOR I in 1..5 LOOP
4096: do_eval(I);
4097: END LOOP;
4098: IF l_contact_contains_str IS NOT NULL THEN
4099: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4100: END IF;
4101: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4102: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 26 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4103: END IF;
4958: x_num_matches OUT NUMBER
4959: ) IS
4960:
4961: -- Strings to hold the generated Intermedia query strings
4962: l_party_contains_str VARCHAR2(32000);
4963: l_match_idx NUMBER;
4964: l_party_site_contains_str VARCHAR2(32000);
4965: l_contact_contains_str VARCHAR2(32000);
4966: l_contact_pt_contains_str VARCHAR2(32000);
4960:
4961: -- Strings to hold the generated Intermedia query strings
4962: l_party_contains_str VARCHAR2(32000);
4963: l_match_idx NUMBER;
4964: l_party_site_contains_str VARCHAR2(32000);
4965: l_contact_contains_str VARCHAR2(32000);
4966: l_contact_pt_contains_str VARCHAR2(32000);
4967: l_tmp VARCHAR2(32000);
4968:
4961: -- Strings to hold the generated Intermedia query strings
4962: l_party_contains_str VARCHAR2(32000);
4963: l_match_idx NUMBER;
4964: l_party_site_contains_str VARCHAR2(32000);
4965: l_contact_contains_str VARCHAR2(32000);
4966: l_contact_pt_contains_str VARCHAR2(32000);
4967: l_tmp VARCHAR2(32000);
4968:
4969: -- Other local variables
4962: l_party_contains_str VARCHAR2(32000);
4963: l_match_idx NUMBER;
4964: l_party_site_contains_str VARCHAR2(32000);
4965: l_contact_contains_str VARCHAR2(32000);
4966: l_contact_pt_contains_str VARCHAR2(32000);
4967: l_tmp VARCHAR2(32000);
4968:
4969: -- Other local variables
4970: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5050: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5051:
5052: -- ************************************************************
5053: -- STEP 2. Setup of intermedia query strings for Acquisition query
5054: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5055: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5056: 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);
5057:
5058: -- Setup Search Context ID
5051:
5052: -- ************************************************************
5053: -- STEP 2. Setup of intermedia query strings for Acquisition query
5054: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5055: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5056: 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);
5057:
5058: -- Setup Search Context ID
5059: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5060: x_search_ctx_id := l_search_ctx_id;
5061:
5062: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5063: 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);
5064: 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);
5065: 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);
5066: 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);
5067: END IF;
5068: IF l_party_site_contains_str IS NULL THEN
5061:
5062: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5063: 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);
5064: 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);
5065: 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);
5066: 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);
5067: END IF;
5068: IF l_party_site_contains_str IS NULL THEN
5069: defps := 1;
5064: 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);
5065: 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);
5066: 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);
5067: END IF;
5068: IF l_party_site_contains_str IS NULL THEN
5069: defps := 1;
5070: END IF;
5071: IF l_contact_pt_contains_str IS NULL THEN
5072: defcpt := 1;
5067: END IF;
5068: IF l_party_site_contains_str IS NULL THEN
5069: defps := 1;
5070: END IF;
5071: IF l_contact_pt_contains_str IS NULL THEN
5072: defcpt := 1;
5073: END IF;
5074:
5075: IF l_party_site_contains_str IS NOT NULL THEN
5071: IF l_contact_pt_contains_str IS NULL THEN
5072: defcpt := 1;
5073: END IF;
5074:
5075: IF l_party_site_contains_str IS NOT NULL THEN
5076: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5077: LOOP
5078: FETCH l_party_site_cur INTO
5079: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;
5072: defcpt := 1;
5073: END IF;
5074:
5075: IF l_party_site_contains_str IS NOT NULL THEN
5076: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5077: LOOP
5078: FETCH l_party_site_cur INTO
5079: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;
5080: EXIT WHEN l_party_site_cur%NOTFOUND;
5095: END LOOP;
5096: CLOSE l_party_site_cur;
5097: END IF;
5098:
5099: IF l_contact_pt_contains_str IS NOT NULL THEN
5100: SAVEPOINT eval_start;
5101: IF l_match_str = ' AND ' OR (
5102: ((l_cpt_max_score/l_entered_max_score)<(160/620))
5103: ) THEN
5103: ) THEN
5104: FORALL I in 1..l_match_ps_list.COUNT
5105: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5106: values (l_search_ctx_id,l_match_ps_list(I));
5107: 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');
5108: ELSE
5109: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5110: END IF;
5111: LOOP
5105: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5106: values (l_search_ctx_id,l_match_ps_list(I));
5107: 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');
5108: ELSE
5109: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5110: END IF;
5111: LOOP
5112: FETCH l_contact_pt_cur INTO
5113: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
5205: x_num_matches OUT NUMBER
5206: ) IS
5207:
5208: -- Strings to hold the generated Intermedia query strings
5209: l_party_contains_str VARCHAR2(32000);
5210: l_party_site_contains_str VARCHAR2(32000);
5211: l_contact_contains_str VARCHAR2(32000);
5212: l_contact_pt_contains_str VARCHAR2(32000);
5213: l_tmp VARCHAR2(32000);
5206: ) IS
5207:
5208: -- Strings to hold the generated Intermedia query strings
5209: l_party_contains_str VARCHAR2(32000);
5210: l_party_site_contains_str VARCHAR2(32000);
5211: l_contact_contains_str VARCHAR2(32000);
5212: l_contact_pt_contains_str VARCHAR2(32000);
5213: l_tmp VARCHAR2(32000);
5214:
5207:
5208: -- Strings to hold the generated Intermedia query strings
5209: l_party_contains_str VARCHAR2(32000);
5210: l_party_site_contains_str VARCHAR2(32000);
5211: l_contact_contains_str VARCHAR2(32000);
5212: l_contact_pt_contains_str VARCHAR2(32000);
5213: l_tmp VARCHAR2(32000);
5214:
5215: -- Other local variables
5208: -- Strings to hold the generated Intermedia query strings
5209: l_party_contains_str VARCHAR2(32000);
5210: l_party_site_contains_str VARCHAR2(32000);
5211: l_contact_contains_str VARCHAR2(32000);
5212: l_contact_pt_contains_str VARCHAR2(32000);
5213: l_tmp VARCHAR2(32000);
5214:
5215: -- Other local variables
5216: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5283: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5284:
5285: -- ************************************************************
5286: -- STEP 2. Setup of intermedia query strings for Acquisition query
5287: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5288: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5289: 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);
5290:
5291: -- Setup Search Context ID
5284:
5285: -- ************************************************************
5286: -- STEP 2. Setup of intermedia query strings for Acquisition query
5287: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5288: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5289: 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);
5290:
5291: -- Setup Search Context ID
5292: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5293: x_search_ctx_id := l_search_ctx_id;
5294:
5295: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5296: 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);
5297: 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);
5298: 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);
5299: 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);
5300: END IF;
5301: IF l_contact_contains_str IS NULL THEN
5294:
5295: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5296: 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);
5297: 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);
5298: 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);
5299: 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);
5300: END IF;
5301: IF l_contact_contains_str IS NULL THEN
5302: defct := 1;
5297: 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);
5298: 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);
5299: 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);
5300: END IF;
5301: IF l_contact_contains_str IS NULL THEN
5302: defct := 1;
5303: END IF;
5304: IF l_contact_pt_contains_str IS NULL THEN
5305: defcpt := 1;
5300: END IF;
5301: IF l_contact_contains_str IS NULL THEN
5302: defct := 1;
5303: END IF;
5304: IF l_contact_pt_contains_str IS NULL THEN
5305: defcpt := 1;
5306: END IF;
5307:
5308: IF l_contact_contains_str IS NOT NULL THEN
5304: IF l_contact_pt_contains_str IS NULL THEN
5305: defcpt := 1;
5306: END IF;
5307:
5308: IF l_contact_contains_str IS NOT NULL THEN
5309: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5310: LOOP
5311: FETCH l_contact_cur INTO
5312: l_org_contact_id, l_ct_party_id , l_TX22;
5305: defcpt := 1;
5306: END IF;
5307:
5308: IF l_contact_contains_str IS NOT NULL THEN
5309: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5310: LOOP
5311: FETCH l_contact_cur INTO
5312: l_org_contact_id, l_ct_party_id , l_TX22;
5313: EXIT WHEN l_contact_cur%NOTFOUND;
5326: END LOOP;
5327: CLOSE l_contact_cur;
5328: END IF;
5329:
5330: IF l_contact_pt_contains_str IS NOT NULL THEN
5331: SAVEPOINT eval_start;
5332: IF l_match_str = ' AND ' OR (
5333: ((l_cpt_max_score/l_entered_max_score)<(160/620))
5334: ) THEN
5334: ) THEN
5335: FORALL I in 1..l_match_contact_list.COUNT
5336: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5337: values (l_search_ctx_id,l_match_contact_list(I));
5338: 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');
5339: ELSE
5340: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5341: END IF;
5342: LOOP
5336: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5337: values (l_search_ctx_id,l_match_contact_list(I));
5338: 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');
5339: ELSE
5340: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5341: END IF;
5342: LOOP
5343: FETCH l_contact_pt_cur INTO
5344: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
5434: ) IS
5435:
5436:
5437: -- Strings to hold the generated Intermedia query strings
5438: l_contact_pt_contains_str VARCHAR2(32000);
5439: l_tmp VARCHAR2(32000);
5440:
5441: -- Other local variables
5442: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5495: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5496:
5497: -- ************************************************************
5498: -- STEP 2. Setup of intermedia query strings for Acquisition query
5499: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5500: 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);
5501:
5502: -- Setup Search Context ID
5503: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5503: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5504: x_search_ctx_id := l_search_ctx_id;
5505: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5506: 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);
5507: 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);
5508: 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);
5509: END IF;
5510:
5511: IF l_contact_pt_contains_str IS NOT NULL THEN
5507: 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);
5508: 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);
5509: END IF;
5510:
5511: IF l_contact_pt_contains_str IS NOT NULL THEN
5512: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5513: cnt := 1;
5514: LOOP
5515: FETCH l_contact_pt_cur INTO
5508: 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);
5509: END IF;
5510:
5511: IF l_contact_pt_contains_str IS NOT NULL THEN
5512: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5513: cnt := 1;
5514: LOOP
5515: FETCH l_contact_pt_cur INTO
5516: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
5579: x_search_ctx_id IN OUT NUMBER
5580: ) IS
5581:
5582: -- Strings to hold the generated Intermedia query strings
5583: l_party_contains_str VARCHAR2(32000);
5584: l_party_site_contains_str VARCHAR2(32000);
5585: l_contact_contains_str VARCHAR2(32000);
5586: l_contact_pt_contains_str VARCHAR2(32000);
5587: l_tmp VARCHAR2(32000);
5580: ) IS
5581:
5582: -- Strings to hold the generated Intermedia query strings
5583: l_party_contains_str VARCHAR2(32000);
5584: l_party_site_contains_str VARCHAR2(32000);
5585: l_contact_contains_str VARCHAR2(32000);
5586: l_contact_pt_contains_str VARCHAR2(32000);
5587: l_tmp VARCHAR2(32000);
5588:
5581:
5582: -- Strings to hold the generated Intermedia query strings
5583: l_party_contains_str VARCHAR2(32000);
5584: l_party_site_contains_str VARCHAR2(32000);
5585: l_contact_contains_str VARCHAR2(32000);
5586: l_contact_pt_contains_str VARCHAR2(32000);
5587: l_tmp VARCHAR2(32000);
5588:
5589: -- Other local variables
5582: -- Strings to hold the generated Intermedia query strings
5583: l_party_contains_str VARCHAR2(32000);
5584: l_party_site_contains_str VARCHAR2(32000);
5585: l_contact_contains_str VARCHAR2(32000);
5586: l_contact_pt_contains_str VARCHAR2(32000);
5587: l_tmp VARCHAR2(32000);
5588:
5589: -- Other local variables
5590: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5673: g_score_until_thresh := false;
5674: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5675: -- ************************************************************
5676: -- STEP 2. Setup of intermedia query strings for Acquisition query
5677: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5678: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5679: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5680: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5681:
5674: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5675: -- ************************************************************
5676: -- STEP 2. Setup of intermedia query strings for Acquisition query
5677: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5678: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5679: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5680: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5681:
5682: -- Setup Search Context ID
5675: -- ************************************************************
5676: -- STEP 2. Setup of intermedia query strings for Acquisition query
5677: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5678: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5679: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5680: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5681:
5682: -- Setup Search Context ID
5683: IF x_search_ctx_id IS NULL THEN
5695: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX40,l_TX39,1);
5696: END LOOP;
5697: CLOSE l_party_cur;
5698:
5699: IF l_party_site_contains_str IS NOT NULL THEN
5700: l_max_score := 0;
5701: l_max_id := 0;
5702: l_max_idx := 0;
5703: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5700: l_max_score := 0;
5701: l_max_id := 0;
5702: l_max_idx := 0;
5703: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5704: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5705: ELSE
5706: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5707: END IF;
5708: LOOP
5702: l_max_idx := 0;
5703: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5704: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5705: ELSE
5706: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5707: END IF;
5708: LOOP
5709: FETCH l_party_site_cur INTO
5710: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;
5733: INSERT_PARTY_SITES_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_party_site_list(l_max_idx), g_party_site_stage_list(l_max_idx) ,l_max_TX3,l_max_TX4,l_max_TX9,l_max_TX10,l_max_TX11,l_max_TX12,l_max_TX13,l_max_TX14,l_max_TX15,l_max_TX20,l_max_TX21,l_max_TX22,l_max_idx);
5734: END IF;
5735: END IF;
5736:
5737: IF l_contact_contains_str IS NOT NULL THEN
5738: l_max_score := 0;
5739: l_max_id := 0;
5740: l_max_idx := 0;
5741: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5737: IF l_contact_contains_str IS NOT NULL THEN
5738: l_max_score := 0;
5739: l_max_id := 0;
5740: l_max_idx := 0;
5741: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5742: LOOP
5743: FETCH l_contact_cur INTO
5744: l_org_contact_id, l_ct_party_id , l_TX22;
5745: EXIT WHEN l_contact_cur%NOTFOUND;
5756: INSERT_CONTACTS_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_contact_list(l_max_idx), g_contact_stage_list(l_max_idx) ,l_max_TX22,l_max_idx);
5757: END IF;
5758: END IF;
5759:
5760: IF l_contact_pt_contains_str IS NOT NULL THEN
5761: l_max_score := 0;
5762: l_max_id := 0;
5763: l_max_idx := 0;
5764: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5761: l_max_score := 0;
5762: l_max_id := 0;
5763: l_max_idx := 0;
5764: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5765: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5766: ELSE
5767: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5768: END IF;
5769: LOOP
5763: l_max_idx := 0;
5764: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5765: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5766: ELSE
5767: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5768: END IF;
5769: LOOP
5770: FETCH l_contact_pt_cur INTO
5771: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;