[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:
1311: p_party_max_score NUMBER,
1312: p_denorm_max_score NUMBER,
1313: p_non_denorm_max_score NUMBER,
1314: p_threshold NUMBER) RETURN VARCHAR2 IS
1315: l_party_contains_str VARCHAR2(32000);
1316: l_party_filter VARCHAR2(1) := null;
1317: l_prim_temp VARCHAR2(4000);
1318: l_denorm_str VARCHAR2(4000);
1319: BEGIN
1355:
1356: IF l_denorm_str IS NOT NULL THEN
1357: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1358: l_denorm_str := NULL;
1359: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1360: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1361: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1362: l_denorm_str := NULL;
1363: END IF;
1357: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1358: l_denorm_str := NULL;
1359: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1360: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1361: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1362: l_denorm_str := NULL;
1363: END IF;
1364: ELSE
1365: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1361: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1362: l_denorm_str := NULL;
1363: END IF;
1364: ELSE
1365: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1366: END IF;
1367:
1368: -- Setup query string for ALL_ACCOUNT_NAMES
1369: l_prim_temp := null;
1372:
1373: IF l_denorm_str IS NOT NULL THEN
1374: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1375: l_denorm_str := NULL;
1376: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1377: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1378: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1379: l_denorm_str := NULL;
1380: END IF;
1374: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1375: l_denorm_str := NULL;
1376: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1377: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1378: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1379: l_denorm_str := NULL;
1380: END IF;
1381: ELSE
1382: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1378: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1379: l_denorm_str := NULL;
1380: END IF;
1381: ELSE
1382: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1383: END IF;
1384:
1385: -- Setup query string for PARTY_NUMBER
1386: l_prim_temp := null;
1389:
1390: IF l_denorm_str IS NOT NULL THEN
1391: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1392: l_denorm_str := NULL;
1393: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1394: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1395: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1396: l_denorm_str := NULL;
1397: END IF;
1391: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1392: l_denorm_str := NULL;
1393: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1394: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1395: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1396: l_denorm_str := NULL;
1397: END IF;
1398: ELSE
1399: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1395: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1396: l_denorm_str := NULL;
1397: END IF;
1398: ELSE
1399: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1400: END IF;
1401:
1402: -- Setup query string for ALL_ACCOUNT_NUMBERS
1403: l_prim_temp := null;
1406:
1407: IF l_denorm_str IS NOT NULL THEN
1408: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1409: l_denorm_str := NULL;
1410: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1411: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1412: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1413: l_denorm_str := NULL;
1414: END IF;
1408: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1409: l_denorm_str := NULL;
1410: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1411: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1412: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1413: l_denorm_str := NULL;
1414: END IF;
1415: ELSE
1416: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1412: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1413: l_denorm_str := NULL;
1414: END IF;
1415: ELSE
1416: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1417: END IF;
1418: IF lengthb(l_party_contains_str) > 4000 THEN
1419: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1420: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1414: END IF;
1415: ELSE
1416: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1417: END IF;
1418: IF lengthb(l_party_contains_str) > 4000 THEN
1419: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1420: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1421: FND_MSG_PUB.ADD;
1422: RAISE FND_API.G_EXC_ERROR;
1420: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1421: FND_MSG_PUB.ADD;
1422: RAISE FND_API.G_EXC_ERROR;
1423: END IF;
1424: IF (p_match_str = ' AND ' OR p_party_max_score
1426: 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);
1427: END IF;
1428: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1422: RAISE FND_API.G_EXC_ERROR;
1423: END IF;
1424: IF (p_match_str = ' AND ' OR p_party_max_score
1426: 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);
1427: END IF;
1428: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1429: 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);
1430: END IF;
1425: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1426: 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);
1427: END IF;
1428: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1429: 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);
1430: END IF;
1431: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1432: 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);
1433: END IF;
1430: END IF;
1431: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1432: 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);
1433: END IF;
1434: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1435: ELSE
1436: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1437: 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);
1438: 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);
1433: END IF;
1434: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1435: ELSE
1436: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1437: 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);
1438: 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);
1439: END IF;
1440: RETURN l_party_contains_str;
1441: END IF;
1434: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1435: ELSE
1436: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1437: 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);
1438: 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);
1439: END IF;
1440: RETURN l_party_contains_str;
1441: END IF;
1442: END;
1436: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1437: 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);
1438: 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);
1439: END IF;
1440: RETURN l_party_contains_str;
1441: END IF;
1442: END;
1443: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1444: l_contains_str VARCHAR2(32000);
1440: RETURN l_party_contains_str;
1441: END IF;
1442: END;
1443: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1444: l_contains_str VARCHAR2(32000);
1445: l_contains_str_temp VARCHAR2(32000);
1446: l_den_contains_str VARCHAR2(32000);
1447: l_den_contains_str_temp VARCHAR2(32000);
1448: l_filter_str VARCHAR2(4000) := null;
1441: END IF;
1442: END;
1443: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1444: l_contains_str VARCHAR2(32000);
1445: l_contains_str_temp VARCHAR2(32000);
1446: l_den_contains_str VARCHAR2(32000);
1447: l_den_contains_str_temp VARCHAR2(32000);
1448: l_filter_str VARCHAR2(4000) := null;
1449: l_prim_temp VARCHAR2(4000) := null;
1442: END;
1443: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1444: l_contains_str VARCHAR2(32000);
1445: l_contains_str_temp VARCHAR2(32000);
1446: l_den_contains_str VARCHAR2(32000);
1447: l_den_contains_str_temp VARCHAR2(32000);
1448: l_filter_str VARCHAR2(4000) := null;
1449: l_prim_temp VARCHAR2(4000) := null;
1450: l_prim_temp_den VARCHAR2(4000) := null;
1443: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1444: l_contains_str VARCHAR2(32000);
1445: l_contains_str_temp VARCHAR2(32000);
1446: l_den_contains_str VARCHAR2(32000);
1447: l_den_contains_str_temp VARCHAR2(32000);
1448: l_filter_str VARCHAR2(4000) := null;
1449: l_prim_temp VARCHAR2(4000) := null;
1450: l_prim_temp_den VARCHAR2(4000) := null;
1451: BEGIN
1453: 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);
1454: END IF;
1455: x_denorm_str := NULL;
1456:
1457: -- Dynamic setup of party site contains str
1458: --
1459: -- For each primary transformation add to intermedia query if it
1460: -- is not null
1461: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1458: --
1459: -- For each primary transformation add to intermedia query if it
1460: -- is not null
1461: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1462: l_contains_str_temp := null;
1463: l_den_contains_str_temp := null;
1464: l_filter_str := null;
1465:
1466: -- Setup query string for ADDRESS
1459: -- For each primary transformation add to intermedia query if it
1460: -- is not null
1461: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1462: l_contains_str_temp := null;
1463: l_den_contains_str_temp := null;
1464: l_filter_str := null;
1465:
1466: -- Setup query string for ADDRESS
1467: l_prim_temp := null;
1467: l_prim_temp := null;
1468: l_prim_temp_den := null;
1469: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRADDRESS
1470: g_party_site_stage_list(I).TX4,'A27',l_prim_temp);
1471: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1472:
1473: -- Setup query string for CITY
1474: l_prim_temp := null;
1475: l_prim_temp_den := null;
1476: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1477: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1478: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1479: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1480: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1481: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1482:
1483: -- Setup query string for POSTAL_CODE
1484: l_prim_temp := null;
1477: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1478: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1479: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1480: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1481: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1482:
1483: -- Setup query string for POSTAL_CODE
1484: l_prim_temp := null;
1485: l_prim_temp_den := null;
1486: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1487: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1488: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1489: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1490: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1491: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1492:
1493: -- Setup query string for STATE
1494: l_prim_temp := null;
1487: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1488: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1489: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1490: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1491: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1492:
1493: -- Setup query string for STATE
1494: l_prim_temp := null;
1495: l_prim_temp_den := null;
1496: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1497: g_party_site_stage_list(I).TX15,'A32',l_prim_temp);
1498: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1499: g_party_site_stage_list(I).TX15,'D_PS',l_prim_temp_den);
1500: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1501: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1502:
1503: -- Setup query string for PARTY_SITE_NUMBER
1504: l_prim_temp := null;
1497: g_party_site_stage_list(I).TX15,'A32',l_prim_temp);
1498: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1499: g_party_site_stage_list(I).TX15,'D_PS',l_prim_temp_den);
1500: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1501: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1502:
1503: -- Setup query string for PARTY_SITE_NUMBER
1504: l_prim_temp := null;
1505: l_prim_temp_den := null;
1504: l_prim_temp := null;
1505: l_prim_temp_den := null;
1506: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL
1507: g_party_site_stage_list(I).TX17,'A33',l_prim_temp);
1508: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1509:
1510: -- Setup query string for COUNTRY
1511: l_prim_temp := null;
1512: l_prim_temp_den := null;
1513: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1514: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1515: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1516: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1517: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1518: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1519:
1520: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1521: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1514: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1515: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1516: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1517: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1518: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1519:
1520: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1521: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1522: END LOOP;
1516: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1517: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1518: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1519:
1520: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1521: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1522: END LOOP;
1523: -- Add the search criteria to query string
1524: IF lengthb(l_contains_str) > 4000 THEN
1517: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1518: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1519:
1520: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1521: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1522: END LOOP;
1523: -- Add the search criteria to query string
1524: IF lengthb(l_contains_str) > 4000 THEN
1525: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1520: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1521: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1522: END LOOP;
1523: -- Add the search criteria to query string
1524: IF lengthb(l_contains_str) > 4000 THEN
1525: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1526: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1527: FND_MSG_PUB.ADD;
1528: RAISE FND_API.G_EXC_ERROR;
1526: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1527: FND_MSG_PUB.ADD;
1528: RAISE FND_API.G_EXC_ERROR;
1529: END IF;
1530: x_denorm_str := l_den_contains_str;
1531: RETURN l_contains_str;
1532: END;
1533:
1534: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1527: FND_MSG_PUB.ADD;
1528: RAISE FND_API.G_EXC_ERROR;
1529: END IF;
1530: x_denorm_str := l_den_contains_str;
1531: RETURN l_contains_str;
1532: END;
1533:
1534: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1535: l_contains_str VARCHAR2(32000);
1531: RETURN l_contains_str;
1532: END;
1533:
1534: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1535: l_contains_str VARCHAR2(32000);
1536: l_contains_str_temp VARCHAR2(32000);
1537: l_den_contains_str VARCHAR2(32000);
1538: l_den_contains_str_temp VARCHAR2(32000);
1539: l_filter_str VARCHAR2(4000) := null;
1532: END;
1533:
1534: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1535: l_contains_str VARCHAR2(32000);
1536: l_contains_str_temp VARCHAR2(32000);
1537: l_den_contains_str VARCHAR2(32000);
1538: l_den_contains_str_temp VARCHAR2(32000);
1539: l_filter_str VARCHAR2(4000) := null;
1540: l_prim_temp VARCHAR2(4000) := null;
1533:
1534: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1535: l_contains_str VARCHAR2(32000);
1536: l_contains_str_temp VARCHAR2(32000);
1537: l_den_contains_str VARCHAR2(32000);
1538: l_den_contains_str_temp VARCHAR2(32000);
1539: l_filter_str VARCHAR2(4000) := null;
1540: l_prim_temp VARCHAR2(4000) := null;
1541: l_prim_temp_den VARCHAR2(4000) := null;
1534: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1535: l_contains_str VARCHAR2(32000);
1536: l_contains_str_temp VARCHAR2(32000);
1537: l_den_contains_str VARCHAR2(32000);
1538: l_den_contains_str_temp VARCHAR2(32000);
1539: l_filter_str VARCHAR2(4000) := null;
1540: l_prim_temp VARCHAR2(4000) := null;
1541: l_prim_temp_den VARCHAR2(4000) := null;
1542: BEGIN
1544: 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);
1545: END IF;
1546: x_denorm_str := NULL;
1547:
1548: -- Dynamic setup of party site contains str
1549: --
1550: -- For each primary transformation add to intermedia query if it
1551: -- is not null
1552: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1549: --
1550: -- For each primary transformation add to intermedia query if it
1551: -- is not null
1552: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1553: l_contains_str_temp := null;
1554: l_den_contains_str_temp := null;
1555: l_filter_str := null;
1556:
1557: -- Setup query string for CONTACT_NAME
1550: -- For each primary transformation add to intermedia query if it
1551: -- is not null
1552: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1553: l_contains_str_temp := null;
1554: l_den_contains_str_temp := null;
1555: l_filter_str := null;
1556:
1557: -- Setup query string for CONTACT_NAME
1558: l_prim_temp := null;
1560: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRPERSON
1561: g_contact_stage_list(I).TX6,'A39',l_prim_temp);
1562: HZ_DQM_SEARCH_UTIL.add_transformation( -- SOUNDEX
1563: g_contact_stage_list(I).TX156,'A39',l_prim_temp);
1564: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1565:
1566: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1567: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1568: END LOOP;
1562: HZ_DQM_SEARCH_UTIL.add_transformation( -- SOUNDEX
1563: g_contact_stage_list(I).TX156,'A39',l_prim_temp);
1564: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1565:
1566: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1567: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1568: END LOOP;
1569: -- Add the search criteria to query string
1570: IF lengthb(l_contains_str) > 4000 THEN
1563: g_contact_stage_list(I).TX156,'A39',l_prim_temp);
1564: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1565:
1566: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1567: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1568: END LOOP;
1569: -- Add the search criteria to query string
1570: IF lengthb(l_contains_str) > 4000 THEN
1571: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1566: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1567: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1568: END LOOP;
1569: -- Add the search criteria to query string
1570: IF lengthb(l_contains_str) > 4000 THEN
1571: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1572: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
1573: FND_MSG_PUB.ADD;
1574: RAISE FND_API.G_EXC_ERROR;
1572: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
1573: FND_MSG_PUB.ADD;
1574: RAISE FND_API.G_EXC_ERROR;
1575: END IF;
1576: x_denorm_str := l_den_contains_str;
1577: RETURN l_contains_str;
1578: END;
1579:
1580: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1573: FND_MSG_PUB.ADD;
1574: RAISE FND_API.G_EXC_ERROR;
1575: END IF;
1576: x_denorm_str := l_den_contains_str;
1577: RETURN l_contains_str;
1578: END;
1579:
1580: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1581: l_contains_str VARCHAR2(32000);
1577: RETURN l_contains_str;
1578: END;
1579:
1580: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1581: l_contains_str VARCHAR2(32000);
1582: l_contains_str_temp VARCHAR2(32000);
1583: l_den_contains_str VARCHAR2(32000);
1584: l_den_contains_str_temp VARCHAR2(32000);
1585: l_filter_str VARCHAR2(4000) := null;
1578: END;
1579:
1580: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1581: l_contains_str VARCHAR2(32000);
1582: l_contains_str_temp VARCHAR2(32000);
1583: l_den_contains_str VARCHAR2(32000);
1584: l_den_contains_str_temp VARCHAR2(32000);
1585: l_filter_str VARCHAR2(4000) := null;
1586: l_prim_temp VARCHAR2(4000) := null;
1579:
1580: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1581: l_contains_str VARCHAR2(32000);
1582: l_contains_str_temp VARCHAR2(32000);
1583: l_den_contains_str VARCHAR2(32000);
1584: l_den_contains_str_temp VARCHAR2(32000);
1585: l_filter_str VARCHAR2(4000) := null;
1586: l_prim_temp VARCHAR2(4000) := null;
1587: l_prim_temp_den VARCHAR2(4000) := null;
1580: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1581: l_contains_str VARCHAR2(32000);
1582: l_contains_str_temp VARCHAR2(32000);
1583: l_den_contains_str VARCHAR2(32000);
1584: l_den_contains_str_temp VARCHAR2(32000);
1585: l_filter_str VARCHAR2(4000) := null;
1586: l_prim_temp VARCHAR2(4000) := null;
1587: l_prim_temp_den VARCHAR2(4000) := null;
1588: TYPE CONTACT_PT_REC_TYPE IS RECORD (
1596: 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);
1597: END IF;
1598: x_denorm_str := NULL;
1599:
1600: -- Dynamic setup of party site contains str
1601: --
1602: -- For each primary transformation add to intermedia query if it
1603: -- is not null
1604: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1601: --
1602: -- For each primary transformation add to intermedia query if it
1603: -- is not null
1604: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1605: l_contains_str_temp := null;
1606: l_den_contains_str_temp := null;
1607: l_filter_str := null;
1608: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1609: if(l_cnt_pt_type.count>0) then
1602: -- For each primary transformation add to intermedia query if it
1603: -- is not null
1604: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1605: l_contains_str_temp := null;
1606: l_den_contains_str_temp := null;
1607: l_filter_str := null;
1608: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1609: if(l_cnt_pt_type.count>0) then
1610: x_modify := 'Y';
1626: l_prim_temp := null;
1627: l_prim_temp_den := null;
1628: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL CTX
1629: g_contact_pt_stage_list(I).TX1,'A44',l_prim_temp);
1630: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1631:
1632: -- Setup query string for EMAIL_ADDRESS
1633: l_prim_temp := null;
1634: l_prim_temp_den := null;
1633: l_prim_temp := null;
1634: l_prim_temp_den := null;
1635: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1636: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1637: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1638:
1639: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1640: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1641: IF N>1 THEN
1635: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1636: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1637: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1638:
1639: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1640: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1641: IF N>1 THEN
1642: distinct_search_cpt_types := N-1;
1643: ELSE
1636: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1637: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1638:
1639: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1640: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1641: IF N>1 THEN
1642: distinct_search_cpt_types := N-1;
1643: ELSE
1644: distinct_search_cpt_types := N;
1647: hz_utility_v2pub.debug(p_message=>'distinct_search_cpt_types is - '||distinct_search_cpt_types,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1648: END IF;
1649: END LOOP;
1650: -- Add the search criteria to query string
1651: IF lengthb(l_contains_str) > 4000 THEN
1652: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1653: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1654: FND_MSG_PUB.ADD;
1655: RAISE FND_API.G_EXC_ERROR;
1653: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1654: FND_MSG_PUB.ADD;
1655: RAISE FND_API.G_EXC_ERROR;
1656: END IF;
1657: x_denorm_str := l_den_contains_str;
1658: RETURN l_contains_str;
1659: END;
1660:
1661: PROCEDURE open_party_cursor_direct (
1654: FND_MSG_PUB.ADD;
1655: RAISE FND_API.G_EXC_ERROR;
1656: END IF;
1657: x_denorm_str := l_den_contains_str;
1658: RETURN l_contains_str;
1659: END;
1660:
1661: PROCEDURE open_party_cursor_direct (
1662: p_dup_party_id NUMBER,
1662: p_dup_party_id NUMBER,
1663: p_restrict_sql VARCHAR2,
1664: p_match_str VARCHAR2,
1665: p_search_merged VARCHAR2,
1666: p_party_contains_str VARCHAR2,
1667: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1668: l_sqlstr VARCHAR2(4000);
1669: l_search_merged VARCHAR2(1);
1670: BEGIN
1679: END IF;
1680: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1681: 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);
1682: END IF;
1683: 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
1684: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1685: 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);
1686: END IF;
1687: OPEN x_cursor FOR
1720: l_sqlstr := l_sqlstr || ' (TX40 like :TX40||'' %'' ';
1721: l_sqlstr := l_sqlstr || ' )) ';
1722: l_sqlstr := l_sqlstr || ' ) ';
1723: END IF;
1724: 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
1725: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1726: 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);
1727: END IF;
1728: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1722: l_sqlstr := l_sqlstr || ' ) ';
1723: END IF;
1724: 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
1725: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1726: 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);
1727: END IF;
1728: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1729: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX10||'' %'' ';
1730: l_sqlstr := l_sqlstr || ' )) ';
1752: END IF;
1753: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1754: 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);
1755: END IF;
1756: 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
1757: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1758: 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);
1759: END IF;
1760: 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
1754: 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);
1755: END IF;
1756: 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
1757: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1758: 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);
1759: END IF;
1760: 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
1761: ,g_party_stage_rec.TX33
1762: ,g_party_stage_rec.TX33
1788: ,g_party_stage_rec.TX35
1789: ,g_party_stage_rec.TX40
1790: ,g_party_stage_rec.TX40
1791: ;
1792: 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
1793: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1794: 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);
1795: END IF;
1796: 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
1790: ,g_party_stage_rec.TX40
1791: ;
1792: 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
1793: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1794: 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);
1795: END IF;
1796: 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
1797: ,g_party_site_stage_list(1).TX10
1798: ,g_party_site_stage_list(1).TX10
1813: END;
1814: PROCEDURE open_party_cursor(
1815: p_dup_party_id NUMBER,
1816: p_restrict_sql VARCHAR2,
1817: p_contains_str VARCHAR2,
1818: p_search_ctx_id NUMBER,
1819: p_match_str VARCHAR2,
1820: p_search_merged VARCHAR2,
1821: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1832: l_search_merged := 'N';
1833: ELSE
1834: l_search_merged := p_search_merged;
1835: END IF;
1836: IF p_contains_str IS NULL THEN
1837: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1838: 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);
1839: END IF;
1840: OPEN x_cursor FOR
1834: l_search_merged := p_search_merged;
1835: END IF;
1836: IF p_contains_str IS NULL THEN
1837: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1838: 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);
1839: END IF;
1840: OPEN x_cursor FOR
1841: SELECT PARTY_ID , TX32, TX33, TX34, TX35, TX40, TX39
1842: FROM HZ_STAGED_PARTIES stage
1851: END IF;
1852: OPEN x_cursor FOR
1853: SELECT PARTY_ID , TX32, TX33, TX34, TX35, TX40, TX39
1854: FROM HZ_STAGED_PARTIES stage
1855: WHERE contains( concat_col, p_contains_str)>0
1856: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1857: AND( (l_search_merged ='Y' )
1858: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1859: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )
1864: END IF;
1865: OPEN x_cursor FOR
1866: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX32, TX33, TX34, TX35, TX40, TX39
1867: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1868: WHERE contains( concat_col, p_contains_str)>0
1869: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1870: AND d.party_id = stage.party_id
1871: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1872: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1887: END IF;
1888: IF p_search_ctx_id IS NULL THEN
1889: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX32, TX33, TX34, TX35, TX40, TX39'||
1890: ' FROM HZ_STAGED_PARTIES stage'||
1891: ' WHERE contains( concat_col, :cont)>0'||
1892: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1893: ' AND ('||p_restrict_sql||')' ||
1894: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1895: IF l_search_merged = 'Y' THEN
1899: ELSE
1900: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1901: END IF;
1902: output_long_strings('----------------------------------------------------------');
1903: output_long_strings('Party Contains String = '||p_contains_str);
1904: output_long_strings('Restrict Sql = '||p_restrict_sql);
1905: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1906: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1907: ,p_dup_party_id, p_dup_party_id;
1901: END IF;
1902: output_long_strings('----------------------------------------------------------');
1903: output_long_strings('Party Contains String = '||p_contains_str);
1904: output_long_strings('Restrict Sql = '||p_restrict_sql);
1905: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1906: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1907: ,p_dup_party_id, p_dup_party_id;
1908: END IF;
1909: END IF;
1932: PROCEDURE open_party_site_cursor(
1933: p_dup_party_id NUMBER,
1934: p_party_id NUMBER,
1935: p_restrict_sql VARCHAR2,
1936: p_contains_str VARCHAR2,
1937: p_search_ctx_id NUMBER,
1938: p_search_merged VARCHAR2,
1939: p_search_rel_sites VARCHAR2,
1940: p_person_api VARCHAR2,
1964: IF p_search_rel_sites = 'N' THEN
1965: OPEN x_cursor FOR
1966: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX17, TX22
1967: FROM HZ_STAGED_PARTY_SITES stage
1968: WHERE contains( concat_col, p_contains_str)>0
1969: AND( (l_search_merged ='Y' )
1970: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1971: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1972: AND stage.party_id = p_party_id;
1973: ELSE
1974: OPEN x_cursor FOR
1975: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX17, TX22
1976: FROM HZ_STAGED_PARTY_SITES stage
1977: WHERE contains( concat_col, p_contains_str)>0
1978: AND( (l_search_merged ='Y' )
1979: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1980: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1981: AND stage.party_id = p_party_id
1981: AND stage.party_id = p_party_id
1982: UNION
1983: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX17, TX22
1984: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1985: WHERE contains( concat_col, p_contains_str)>0
1986: AND( (l_search_merged ='Y' )
1987: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1988: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1989: AND r.object_id = p_party_id
2003: END IF;
2004: OPEN x_cursor FOR
2005: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX17, TX22
2006: FROM HZ_STAGED_PARTY_SITES stage
2007: WHERE contains( concat_col, p_contains_str)>0
2008: AND EXISTS (
2009: SELECT 1 FROM HZ_STAGED_PARTIES p
2010: WHERE p.PARTY_ID = stage.PARTY_ID
2011: AND( (l_search_merged ='Y' )
2023: IF p_person_api = 'Y' THEN
2024: OPEN x_cursor FOR
2025: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX17, TX22
2026: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
2027: WHERE contains( concat_col, p_contains_str)>0
2028: AND( (l_search_merged ='Y' )
2029: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2030: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2031: AND d.search_context_id = p_search_ctx_id
2033: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2034: UNION
2035: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX17, TX22
2036: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
2037: WHERE contains( concat_col, p_contains_str)>0
2038: AND( (l_search_merged ='Y' )
2039: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2040: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2041: AND d.search_context_id = p_search_ctx_id
2046: ELSE
2047: OPEN x_cursor FOR
2048: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX17, TX22
2049: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
2050: WHERE contains( concat_col, p_contains_str)>0
2051: AND( (l_search_merged ='Y' )
2052: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2053: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2054: AND d.search_context_id = p_search_ctx_id
2091: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2092: END IF;
2093: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX17, TX22'||
2094: ' FROM HZ_STAGED_PARTY_SITES stage'||
2095: ' WHERE contains( concat_col, :cont)>0'||
2096: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2097: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2098: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2099: ' AND (ORG_CONTACT_ID IS NULL ' ||
2101: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2102: ' UNION ' ||
2103: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX17, TX22'||
2104: ' FROM HZ_STAGED_PARTY_SITES stage'||
2105: ' WHERE contains( concat_col, :cont)>0'||
2106: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2107: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2108: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2109: ' AND ORG_CONTACT_ID IN ' ||
2111: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2112: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2113: ' and ('||p_restrict_sql1|| ') )' ||
2114: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2115: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2116: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2117: ELSE
2118: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX17, TX22'||
2119: ' FROM HZ_STAGED_PARTY_SITES stage'||
2112: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2113: ' and ('||p_restrict_sql1|| ') )' ||
2114: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2115: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2116: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2117: ELSE
2118: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX17, TX22'||
2119: ' FROM HZ_STAGED_PARTY_SITES stage'||
2120: ' WHERE contains( concat_col, :cont)>0'||
2116: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2117: ELSE
2118: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX17, TX22'||
2119: ' FROM HZ_STAGED_PARTY_SITES stage'||
2120: ' WHERE contains( concat_col, :cont)>0'||
2121: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2122: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2123: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2124: ' AND EXISTS ('||
2126: ' WHERE p.party_id = stage.party_id ' ||
2127: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2128: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2129: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2130: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2131: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2132: ,p_dup_party_id, p_dup_party_id;
2133: END IF;
2134: ELSE
2133: END IF;
2134: ELSE
2135: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX17, TX22'||
2136: ' FROM HZ_STAGED_PARTY_SITES stage'||
2137: ' WHERE contains( concat_col, :cont)>0'||
2138: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2139: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2140: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2141: ' AND EXISTS ('||
2143: ' WHERE p.party_id = stage.party_id ' ||
2144: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2145: ' AND ('||p_restrict_sql||')' ||
2146: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2147: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2148: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2149: ,p_dup_party_id, p_dup_party_id;
2150: END IF;
2151: END IF;
2149: ,p_dup_party_id, p_dup_party_id;
2150: END IF;
2151: END IF;
2152: output_long_strings('----------------------------------------------------------');
2153: output_long_strings('Party Site Contains String = '||p_contains_str);
2154: output_long_strings('Restrict Sql = '||p_restrict_sql);
2155: exception
2156: when others then
2157: if (instrb(SQLERRM,'DRG-51030')>0) then
2177: PROCEDURE open_contact_cursor(
2178: p_dup_party_id NUMBER,
2179: p_party_id NUMBER,
2180: p_restrict_sql VARCHAR2,
2181: p_contains_str VARCHAR2,
2182: p_search_ctx_id NUMBER,
2183: p_search_merged VARCHAR2,
2184: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
2185: l_sqlstr VARCHAR2(4000);
2205: END IF;
2206: OPEN x_cursor FOR
2207: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID, TX6, TX5
2208: FROM HZ_STAGED_CONTACTS stage
2209: WHERE contains( concat_col, p_contains_str)>0
2210: AND EXISTS (
2211: SELECT 1 FROM HZ_STAGED_PARTIES p
2212: WHERE p.PARTY_ID = stage.PARTY_ID
2213: AND( (l_search_merged ='Y' )
2228: END IF;
2229: OPEN x_cursor FOR
2230: SELECT ORG_CONTACT_ID, PARTY_ID, TX6, TX5
2231: FROM HZ_STAGED_CONTACTS stage
2232: WHERE contains( concat_col, p_contains_str)>0
2233: AND EXISTS (
2234: SELECT 1 FROM HZ_STAGED_PARTIES p
2235: WHERE p.PARTY_ID = stage.PARTY_ID
2236: AND( (l_search_merged ='Y' )
2247: END IF;
2248: OPEN x_cursor FOR
2249: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID, TX6, TX5
2250: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
2251: WHERE contains( concat_col, p_contains_str)>0
2252: AND d.search_context_id = p_search_ctx_id
2253: AND d.party_id = stage.party_id
2254: AND( (l_search_merged ='Y' )
2255: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2281: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
2282: END IF;
2283: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID , TX6, TX5'||
2284: ' FROM HZ_STAGED_CONTACTS stage'||
2285: ' WHERE contains( concat_col, :cont)>0'||
2286: ' AND EXISTS ('||
2287: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2288: ' WHERE p.party_id = stage.party_id ' ||
2289: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2291: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2292: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2293: ' AND ('||p_restrict_sql||')' ||
2294: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2295: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2296: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2297: ,p_dup_party_id, p_dup_party_id;
2298: END IF;
2299: output_long_strings('----------------------------------------------------------');
2296: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2297: ,p_dup_party_id, p_dup_party_id;
2298: END IF;
2299: output_long_strings('----------------------------------------------------------');
2300: output_long_strings('Contacts Contains String = '||p_contains_str);
2301: output_long_strings('Restrict Sql = '||p_restrict_sql);
2302: exception
2303: when others then
2304: if (instrb(SQLERRM,'DRG-51030')>0) then
2324: PROCEDURE open_contact_pt_cursor(
2325: p_dup_party_id NUMBER,
2326: p_party_id NUMBER,
2327: p_restrict_sql VARCHAR2,
2328: p_contains_str VARCHAR2,
2329: p_search_ctx_id NUMBER,
2330: p_search_merged VARCHAR2,
2331: p_search_rel_cpts VARCHAR2,
2332: p_person_api VARCHAR2,
2354: THEN
2355: OPEN x_cursor FOR
2356: 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
2357: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2358: WHERE contains( concat_col, p_contains_str)>0
2359: AND d.search_context_id = p_search_ctx_id
2360: AND( (l_search_merged ='Y' )
2361: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2362: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2366: THEN
2367: OPEN x_cursor FOR
2368: 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
2369: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2370: WHERE contains( concat_col, p_contains_str)>0
2371: AND d.search_context_id = p_search_ctx_id
2372: AND( (l_search_merged ='Y' )
2373: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2374: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2383: IF p_search_rel_cpts = 'N' THEN
2384: OPEN x_cursor FOR
2385: 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
2386: FROM HZ_STAGED_CONTACT_POINTS stage
2387: WHERE contains( concat_col, p_contains_str)>0
2388: AND( (l_search_merged ='Y' )
2389: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2390: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2391: AND stage.party_id = p_party_id;
2392: ELSE
2393: OPEN x_cursor FOR
2394: 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
2395: FROM HZ_STAGED_CONTACT_POINTS stage
2396: WHERE contains( concat_col, p_contains_str)>0
2397: AND( (l_search_merged ='Y' )
2398: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2399: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2400: AND stage.party_id = p_party_id
2400: AND stage.party_id = p_party_id
2401: UNION
2402: 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
2403: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2404: WHERE contains( concat_col, p_contains_str)>0
2405: AND r.object_id = p_party_id
2406: AND( (l_search_merged ='Y' )
2407: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2408: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2422: END IF;
2423: OPEN x_cursor FOR
2424: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2425: FROM HZ_STAGED_CONTACT_POINTS stage
2426: WHERE contains( concat_col, p_contains_str)>0
2427: AND EXISTS (
2428: SELECT 1 FROM HZ_STAGED_PARTIES p
2429: WHERE p.PARTY_ID = stage.PARTY_ID
2430: AND( (l_search_merged ='Y' )
2442: IF p_person_api = 'Y' THEN
2443: OPEN x_cursor FOR
2444: 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
2445: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2446: WHERE contains( concat_col, p_contains_str)>0
2447: AND d.search_context_id = p_search_ctx_id
2448: AND d.party_id = stage.party_id
2449: AND( (l_search_merged ='Y' )
2450: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2452: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2453: UNION
2454: 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
2455: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2456: WHERE contains( concat_col, p_contains_str)>0
2457: AND d.search_context_id = p_search_ctx_id
2458: AND d.party_id = r.subject_id
2459: AND r.relationship_id = oc.party_relationship_id
2460: AND oc.org_contact_id = stage.org_contact_id
2465: ELSE
2466: OPEN x_cursor FOR
2467: 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
2468: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2469: WHERE contains( concat_col, p_contains_str)>0
2470: AND d.search_context_id = p_search_ctx_id
2471: AND d.party_id = stage.party_id
2472: AND( (l_search_merged ='Y' )
2473: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2510: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2511: END IF;
2512: 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'||
2513: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2514: ' WHERE contains( concat_col, :cont)>0 '||
2515: ' AND (stage.org_contact_id is null '||
2516: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2517: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2518: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2520: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2521: ' UNION ' ||
2522: ' 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'||
2523: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2524: ' WHERE contains( concat_col, :cont)>0 '||
2525: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2526: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2527: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2528: ' AND (stage.org_contact_id in ' ||
2530: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2531: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2532: ' and ('||p_restrict_sql1 || ') ) )' ||
2533: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2534: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2535: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2536: ELSE
2537: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2538: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2531: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2532: ' and ('||p_restrict_sql1 || ') ) )' ||
2533: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2534: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2535: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2536: ELSE
2537: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2538: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2539: ' WHERE contains( concat_col, :cont)>0'||
2535: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2536: ELSE
2537: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2538: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2539: ' WHERE contains( concat_col, :cont)>0'||
2540: ' AND EXISTS ('||
2541: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2542: ' WHERE p.party_id = stage.party_id ' ||
2543: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2545: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2546: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2547: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2548: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2549: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2550: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2551: ,p_dup_party_id, p_dup_party_id;
2552: END IF;
2553: ELSE
2552: END IF;
2553: ELSE
2554: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2555: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2556: ' WHERE contains( concat_col, :cont)>0'||
2557: ' AND EXISTS ('||
2558: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2559: ' WHERE p.party_id = stage.party_id ' ||
2560: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2562: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2563: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2564: ' AND ('||p_restrict_sql||')' ||
2565: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2566: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2567: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2568: ,p_dup_party_id, p_dup_party_id;
2569: END IF;
2570: END IF;
2569: END IF;
2570: END IF;
2571: END IF;
2572: output_long_strings('----------------------------------------------------------');
2573: output_long_strings('Contact Points Contains String = '||p_contains_str);
2574: output_long_strings('Restrict Sql = '||p_restrict_sql);
2575: exception
2576: when others then
2577: if (instrb(SQLERRM,'DRG-51030')>0) then
2690: x_num_matches OUT NUMBER
2691: ) IS
2692:
2693: -- Strings to hold the generated Intermedia query strings
2694: l_party_contains_str VARCHAR2(32000);
2695: l_party_site_contains_str VARCHAR2(32000);
2696: l_contact_contains_str VARCHAR2(32000);
2697: l_contact_pt_contains_str VARCHAR2(32000);
2698: l_denorm_str VARCHAR2(32000);
2691: ) IS
2692:
2693: -- Strings to hold the generated Intermedia query strings
2694: l_party_contains_str VARCHAR2(32000);
2695: l_party_site_contains_str VARCHAR2(32000);
2696: l_contact_contains_str VARCHAR2(32000);
2697: l_contact_pt_contains_str VARCHAR2(32000);
2698: l_denorm_str VARCHAR2(32000);
2699: l_ps_denorm_str VARCHAR2(32000);
2692:
2693: -- Strings to hold the generated Intermedia query strings
2694: l_party_contains_str VARCHAR2(32000);
2695: l_party_site_contains_str VARCHAR2(32000);
2696: l_contact_contains_str VARCHAR2(32000);
2697: l_contact_pt_contains_str VARCHAR2(32000);
2698: l_denorm_str VARCHAR2(32000);
2699: l_ps_denorm_str VARCHAR2(32000);
2700: l_ct_denorm_str VARCHAR2(32000);
2693: -- Strings to hold the generated Intermedia query strings
2694: l_party_contains_str VARCHAR2(32000);
2695: l_party_site_contains_str VARCHAR2(32000);
2696: l_contact_contains_str VARCHAR2(32000);
2697: l_contact_pt_contains_str VARCHAR2(32000);
2698: l_denorm_str VARCHAR2(32000);
2699: l_ps_denorm_str VARCHAR2(32000);
2700: l_ct_denorm_str VARCHAR2(32000);
2701: l_cpt_denorm_str VARCHAR2(32000);
2796: call_type(2) := 'OR';
2797: END;
2798:
2799: /** Private procedure to acquire and score at party level ***/
2800: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2801: l_party_id_idx NUMBER:=1;
2802: l_ctx_id NUMBER;
2803: l_precalc_score BOOLEAN := FALSE;
2804: BEGIN
2827: l_search_ctx_id,H_PARTY_ID(I));
2828: l_party_id_idx := H_PARTY_ID.COUNT+1;
2829: ELSE
2830: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2831: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2832: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2833: 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);
2834: END IF;
2835: 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
2835: 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
2836: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2837: 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);
2838: END IF;
2839: IF (l_party_site_contains_str IS NULL AND
2840: l_contact_contains_str IS NULL AND
2841: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2842: 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);
2843: RAISE L_RETURN_IMM_EXC;
2836: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2837: 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);
2838: END IF;
2839: IF (l_party_site_contains_str IS NULL AND
2840: l_contact_contains_str IS NULL AND
2841: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2842: 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);
2843: RAISE L_RETURN_IMM_EXC;
2844: ELSE
2837: 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);
2838: END IF;
2839: IF (l_party_site_contains_str IS NULL AND
2840: l_contact_contains_str IS NULL AND
2841: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2842: 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);
2843: RAISE L_RETURN_IMM_EXC;
2844: ELSE
2845: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2841: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2842: 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);
2843: RAISE L_RETURN_IMM_EXC;
2844: ELSE
2845: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2846: END IF;
2847: ELSE
2848: IF p_index>1 THEN
2849: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2864: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2865: 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);
2866: END IF;
2867: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2868: 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);
2869: END IF;
2870: LOOP
2871: FETCH l_party_cur INTO
2872: l_party_id , l_TX32, l_TX33, l_TX34, l_TX35, l_TX40, l_TX39;
2886: l_party_id_idx:= l_party_id_idx+1;
2887: END IF;
2888: IF (l_party_id_idx-1)>l_max_thresh THEN
2889: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2890: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2891: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2892: AND l_contact_pt_contains_str IS NULL THEN
2893: EXIT;
2894: END IF;
2888: IF (l_party_id_idx-1)>l_max_thresh THEN
2889: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2890: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2891: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2892: AND l_contact_pt_contains_str IS NULL THEN
2893: EXIT;
2894: END IF;
2895: CLOSE l_party_cur;
2896: IF p_index>1 THEN
2917: ROLLBACK to eval_start;
2918: END;
2919:
2920: /** Private procedure to acquire and score at party site level ***/
2921: 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
2922: l_party_id_idx NUMBER:=1;
2923: l_ctx_id NUMBER;
2924: h_ps_id HZ_PARTY_SEARCH.IDList;
2925: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2929: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2930: 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);
2931: 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);
2932: END IF;
2933: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2934: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2935: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2936: l_party_id := H_SCORES.FIRST;
2937: WHILE l_party_id IS NOT NULL LOOP
2930: 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);
2931: 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);
2932: END IF;
2933: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2934: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2935: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2936: l_party_id := H_SCORES.FIRST;
2937: WHILE l_party_id IS NOT NULL LOOP
2938: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2931: 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);
2932: END IF;
2933: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2934: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2935: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2936: l_party_id := H_SCORES.FIRST;
2937: WHILE l_party_id IS NOT NULL LOOP
2938: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2939: l_party_id:=H_SCORES.NEXT(l_party_id);
2961: l_search_ctx_id,H_PARTY_ID(I));
2962: l_party_id_idx := H_PARTY_ID.COUNT+1;
2963: ELSE
2964: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2965: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2966: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2967: 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);
2968: END IF;
2969: IF p_index>1 THEN
2983: END IF;
2984: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2985: 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);
2986: END IF;
2987: 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);
2988: LOOP
2989: FETCH l_party_site_cur INTO
2990: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15, l_TX17, l_TX22;
2991: EXIT WHEN l_party_site_cur%NOTFOUND;
3039: END IF;
3040: END;
3041:
3042: /** Private procedure to acquire and score at contact point level ***/
3043: 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
3044: l_party_id_idx NUMBER:=1;
3045: l_ctx_id NUMBER;
3046: h_ct_id HZ_PARTY_SEARCH.IDList;
3047: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3073: l_search_ctx_id,H_PARTY_ID(I));
3074: l_party_id_idx := H_PARTY_ID.COUNT+1;
3075: ELSE
3076: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3077: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
3078: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3079: 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);
3080: END IF;
3081: IF p_index>1 THEN
3095: END IF;
3096: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3097: 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);
3098: END IF;
3099: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
3100: LOOP
3101: FETCH l_contact_cur INTO
3102: l_org_contact_id, l_ct_party_id , l_TX6, l_TX5;
3103: EXIT WHEN l_contact_cur%NOTFOUND;
3148: END IF;
3149: END;
3150:
3151: /** Private procedure to acquire and score at contact point level ***/
3152: 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
3153: l_party_id_idx NUMBER:=1;
3154: l_ctx_id NUMBER;
3155: h_cpt_id HZ_PARTY_SEARCH.IDList;
3156: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3184: l_search_ctx_id,H_PARTY_ID(I));
3185: l_party_id_idx := H_PARTY_ID.COUNT+1;
3186: ELSE
3187: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3188: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
3189: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3190: 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);
3191: END IF;
3192: IF p_index>1 THEN
3206: END IF;
3207: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3208: 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);
3209: END IF;
3210: 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);
3211: LOOP
3212: FETCH l_contact_pt_cur INTO
3213: 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;
3214: EXIT WHEN l_contact_pt_cur%NOTFOUND;
3387: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3388: l_threshold := round(( l_entered_max_score / 700) * 77);
3389: other_acq_criteria_exists := TRUE ;
3390: --check if acquisition criteria exists for any other entity
3391: IF l_party_contains_str IS NOT NULL THEN
3392: acq_cnt := 1;
3393: END IF;
3394: IF l_party_site_contains_str IS NOT NULL THEN
3395: acq_cnt := acq_cnt+1;
3390: --check if acquisition criteria exists for any other entity
3391: IF l_party_contains_str IS NOT NULL THEN
3392: acq_cnt := 1;
3393: END IF;
3394: IF l_party_site_contains_str IS NOT NULL THEN
3395: acq_cnt := acq_cnt+1;
3396: END IF;
3397: IF l_contact_contains_str IS NOT NULL THEN
3398: acq_cnt := acq_cnt+1;
3393: END IF;
3394: IF l_party_site_contains_str IS NOT NULL THEN
3395: acq_cnt := acq_cnt+1;
3396: END IF;
3397: IF l_contact_contains_str IS NOT NULL THEN
3398: acq_cnt := acq_cnt+1;
3399: END IF;
3400: IF l_contact_pt_contains_str IS NOT NULL THEN
3401: acq_cnt := acq_cnt+1;
3396: END IF;
3397: IF l_contact_contains_str IS NOT NULL THEN
3398: acq_cnt := acq_cnt+1;
3399: END IF;
3400: IF l_contact_pt_contains_str IS NOT NULL THEN
3401: acq_cnt := acq_cnt+1;
3402: END IF;
3403: IF acq_cnt>1 THEN
3404: other_acq_criteria_exists := TRUE;
3430: FND_MSG_PUB.ADD;
3431: RAISE FND_API.G_EXC_ERROR;
3432: END IF;
3433: END IF;
3434: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3435: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3436: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3437: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3438: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3431: RAISE FND_API.G_EXC_ERROR;
3432: END IF;
3433: END IF;
3434: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3435: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3436: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3437: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3438: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3439: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3432: END IF;
3433: END IF;
3434: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3435: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3436: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3437: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3438: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3439: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3440: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3433: END IF;
3434: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3435: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3436: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3437: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3438: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3439: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3440: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3441: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3434: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3435: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3436: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3437: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3438: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3439: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3440: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3441: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3442: END IF;
3435: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3436: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3437: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3438: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3439: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3440: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3441: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3442: END IF;
3443: END;
3436: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3437: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3438: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3439: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3440: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3441: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3442: END IF;
3443: END;
3444: /************ End of find_parties private procedures **********/
3437: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3438: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3439: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3440: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3441: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3442: END IF;
3443: END;
3444: /************ End of find_parties private procedures **********/
3445:
3509: 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);
3510: END IF;
3511: -- ************************************************************
3512: -- STEP 2. Setup of intermedia query strings for Acquisition query
3513: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3514: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3515: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3516: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3517: 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);
3510: END IF;
3511: -- ************************************************************
3512: -- STEP 2. Setup of intermedia query strings for Acquisition query
3513: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3514: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3515: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3516: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3517: 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);
3518: END IF;
3511: -- ************************************************************
3512: -- STEP 2. Setup of intermedia query strings for Acquisition query
3513: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3514: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3515: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3516: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3517: 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);
3518: END IF;
3519: l_denorm_max_score:=0;
3543: END IF;
3544: ELSE
3545: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3546: END IF;
3547: 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((77/700)*l_entered_max_score));
3548: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3549:
3550: -- Setup Search Context ID
3551: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3553:
3554: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3555: 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);
3556: END IF;
3557: IF l_party_contains_str IS NULL THEN
3558: defpt := 1;
3559: END IF;
3560: IF l_party_site_contains_str IS NULL THEN
3561: defps := 1;
3556: END IF;
3557: IF l_party_contains_str IS NULL THEN
3558: defpt := 1;
3559: END IF;
3560: IF l_party_site_contains_str IS NULL THEN
3561: defps := 1;
3562: END IF;
3563: IF l_contact_contains_str IS NULL THEN
3564: defct := 1;
3559: END IF;
3560: IF l_party_site_contains_str IS NULL THEN
3561: defps := 1;
3562: END IF;
3563: IF l_contact_contains_str IS NULL THEN
3564: defct := 1;
3565: END IF;
3566: IF l_contact_pt_contains_str IS NULL THEN
3567: defcpt := 1;
3562: END IF;
3563: IF l_contact_contains_str IS NULL THEN
3564: defct := 1;
3565: END IF;
3566: IF l_contact_pt_contains_str IS NULL THEN
3567: defcpt := 1;
3568: END IF;
3569:
3570: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3571: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3572: 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);
3573: END IF;
3574: 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);
3575: 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);
3576: 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);
3577: 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);
3578: 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);
3579: 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);
3572: 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);
3573: END IF;
3574: 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);
3575: 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);
3576: 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);
3577: 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);
3578: 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);
3579: 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);
3580: END IF;
3573: END IF;
3574: 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);
3575: 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);
3576: 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);
3577: 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);
3578: 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);
3579: 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);
3580: END IF;
3581: FOR I in 1..5 LOOP
3574: 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);
3575: 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);
3576: 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);
3577: 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);
3578: 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);
3579: 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);
3580: END IF;
3581: FOR I in 1..5 LOOP
3582: do_eval(I);
3656: x_num_matches OUT NUMBER
3657: ) IS
3658:
3659: -- Strings to hold the generated Intermedia query strings
3660: l_party_contains_str VARCHAR2(32000);
3661: l_party_site_contains_str VARCHAR2(32000);
3662: l_contact_contains_str VARCHAR2(32000);
3663: l_contact_pt_contains_str VARCHAR2(32000);
3664: l_denorm_str VARCHAR2(32000);
3657: ) IS
3658:
3659: -- Strings to hold the generated Intermedia query strings
3660: l_party_contains_str VARCHAR2(32000);
3661: l_party_site_contains_str VARCHAR2(32000);
3662: l_contact_contains_str VARCHAR2(32000);
3663: l_contact_pt_contains_str VARCHAR2(32000);
3664: l_denorm_str VARCHAR2(32000);
3665: l_ps_denorm_str VARCHAR2(32000);
3658:
3659: -- Strings to hold the generated Intermedia query strings
3660: l_party_contains_str VARCHAR2(32000);
3661: l_party_site_contains_str VARCHAR2(32000);
3662: l_contact_contains_str VARCHAR2(32000);
3663: l_contact_pt_contains_str VARCHAR2(32000);
3664: l_denorm_str VARCHAR2(32000);
3665: l_ps_denorm_str VARCHAR2(32000);
3666: l_ct_denorm_str VARCHAR2(32000);
3659: -- Strings to hold the generated Intermedia query strings
3660: l_party_contains_str VARCHAR2(32000);
3661: l_party_site_contains_str VARCHAR2(32000);
3662: l_contact_contains_str VARCHAR2(32000);
3663: l_contact_pt_contains_str VARCHAR2(32000);
3664: l_denorm_str VARCHAR2(32000);
3665: l_ps_denorm_str VARCHAR2(32000);
3666: l_ct_denorm_str VARCHAR2(32000);
3667: l_cpt_denorm_str VARCHAR2(32000);
3792: call_type(2) := 'OR';
3793: END;
3794:
3795: /** Private procedure to acquire and score at party level ***/
3796: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3797: l_party_id_idx NUMBER:=1;
3798: l_ctx_id NUMBER;
3799: BEGIN
3800: SAVEPOINT eval_start;
3813: l_search_ctx_id,H_PARTY_ID(I));
3814: l_party_id_idx := H_PARTY_ID.COUNT+1;
3815: ELSE
3816: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3817: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3818: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3819: 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);
3820: END IF;
3821: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3821: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3822: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3823: 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);
3824: END IF;
3825: IF (l_party_site_contains_str IS NULL AND
3826: l_contact_contains_str IS NULL AND
3827: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3828: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3829: RAISE L_RETURN_IMM_EXC;
3822: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3823: 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);
3824: END IF;
3825: IF (l_party_site_contains_str IS NULL AND
3826: l_contact_contains_str IS NULL AND
3827: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3828: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3829: RAISE L_RETURN_IMM_EXC;
3830: ELSE
3823: 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);
3824: END IF;
3825: IF (l_party_site_contains_str IS NULL AND
3826: l_contact_contains_str IS NULL AND
3827: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3828: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3829: RAISE L_RETURN_IMM_EXC;
3830: ELSE
3831: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3827: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3828: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3829: RAISE L_RETURN_IMM_EXC;
3830: ELSE
3831: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3832: END IF;
3833: ELSE
3834: IF p_index>1 THEN
3835: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3847: l_ctx_id := NULL;
3848: l_party_id_idx := H_PARTY_ID.COUNT+1;
3849: END IF;
3850: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3851: 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);
3852: END IF;
3853: LOOP
3854: FETCH l_party_cur INTO
3855: l_party_id , l_TX32, l_TX33, l_TX34, l_TX35, l_TX40, l_TX39;
3869: l_party_id_idx:= l_party_id_idx+1;
3870: END IF;
3871: IF (l_party_id_idx-1)>l_max_thresh THEN
3872: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3873: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3874: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3875: AND l_contact_pt_contains_str IS NULL THEN
3876: H_PARTY_ID.DELETE(l_party_id_idx-1);
3877: H_PARTY_ID_LIST.DELETE(l_index);
3871: IF (l_party_id_idx-1)>l_max_thresh THEN
3872: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3873: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3874: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3875: AND l_contact_pt_contains_str IS NULL THEN
3876: H_PARTY_ID.DELETE(l_party_id_idx-1);
3877: H_PARTY_ID_LIST.DELETE(l_index);
3878: H_SCORES.DELETE(l_index);
3879: EXIT;
3900: CLOSE l_party_cur;
3901: ROLLBACK to eval_start;
3902: END;
3903: PROCEDURE open_person_contact_cursor(
3904: p_contains_str VARCHAR2,
3905: p_search_ctx_id NUMBER,
3906: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3907: BEGIN
3908: OPEN x_cursor FOR
3907: BEGIN
3908: OPEN x_cursor FOR
3909: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID, TX6, TX5
3910: FROM HZ_STAGED_CONTACTS stage
3911: WHERE contains( concat_col, p_contains_str)>0
3912: AND ORG_CONTACT_ID in (
3913: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3914: org_contact_id
3915: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc
3931: end if;
3932: END;
3933:
3934: /** Private procedure to acquire and score at party site level ***/
3935: 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
3936: l_party_id_idx NUMBER:=1;
3937: l_ctx_id NUMBER;
3938: h_ps_id HZ_PARTY_SEARCH.IDList;
3939: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3959: ELSE
3960: l_party_id_idx := H_PARTY_ID.COUNT+1;
3961: l_ctx_id := NULL;
3962: END IF;
3963: 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);
3964: LOOP
3965: FETCH l_party_site_cur INTO
3966: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15, l_TX17, l_TX22;
3967: EXIT WHEN l_party_site_cur%NOTFOUND;
4030: END IF;
4031: END;
4032:
4033: /** Private procedure to acquire and score at party site level ***/
4034: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
4035: l_party_id_idx NUMBER:=1;
4036: l_ctx_id NUMBER;
4037: h_ct_id HZ_PARTY_SEARCH.IDList;
4038: h_ct_party_id HZ_PARTY_SEARCH.IDList;
4046: FORALL I in 1..H_PARTY_ID.COUNT
4047: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
4048: l_search_ctx_id,H_PARTY_ID(I));
4049: l_party_id_idx := H_PARTY_ID.COUNT+1;
4050: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
4051: LOOP
4052: FETCH l_contact_cur INTO
4053: l_org_contact_id, l_ct_party_id , l_TX6, l_TX5;
4054: EXIT WHEN l_contact_cur%NOTFOUND;
4088: END IF;
4089: END;
4090:
4091: /** Private procedure to acquire and score at contact point level ***/
4092: 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
4093: l_party_id_idx NUMBER:=1;
4094: l_ctx_id NUMBER;
4095: h_cpt_id HZ_PARTY_SEARCH.IDList;
4096: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
4133: END IF;
4134: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4135: 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);
4136: END IF;
4137: 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);
4138: LOOP
4139: FETCH l_contact_pt_cur INTO
4140: 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;
4141: EXIT WHEN l_contact_pt_cur%NOTFOUND;
4336: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
4337: l_threshold := round(( l_entered_max_score / 700) * 77);
4338: other_acq_criteria_exists := TRUE ;
4339: --check if acquisition criteria exists for any other entity
4340: IF l_party_contains_str IS NOT NULL THEN
4341: acq_cnt := 1;
4342: END IF;
4343: IF l_party_site_contains_str IS NOT NULL THEN
4344: acq_cnt := acq_cnt+1;
4339: --check if acquisition criteria exists for any other entity
4340: IF l_party_contains_str IS NOT NULL THEN
4341: acq_cnt := 1;
4342: END IF;
4343: IF l_party_site_contains_str IS NOT NULL THEN
4344: acq_cnt := acq_cnt+1;
4345: END IF;
4346: IF l_contact_contains_str IS NOT NULL THEN
4347: acq_cnt := acq_cnt+1;
4342: END IF;
4343: IF l_party_site_contains_str IS NOT NULL THEN
4344: acq_cnt := acq_cnt+1;
4345: END IF;
4346: IF l_contact_contains_str IS NOT NULL THEN
4347: acq_cnt := acq_cnt+1;
4348: END IF;
4349: IF l_contact_pt_contains_str IS NOT NULL THEN
4350: acq_cnt := acq_cnt+1;
4345: END IF;
4346: IF l_contact_contains_str IS NOT NULL THEN
4347: acq_cnt := acq_cnt+1;
4348: END IF;
4349: IF l_contact_pt_contains_str IS NOT NULL THEN
4350: acq_cnt := acq_cnt+1;
4351: END IF;
4352: IF acq_cnt>1 THEN
4353: other_acq_criteria_exists := TRUE;
4379: FND_MSG_PUB.ADD;
4380: RAISE FND_API.G_EXC_ERROR;
4381: END IF;
4382: END IF;
4383: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4384: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4385: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4386: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4387: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4380: RAISE FND_API.G_EXC_ERROR;
4381: END IF;
4382: END IF;
4383: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4384: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4385: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4386: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4387: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4388: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4381: END IF;
4382: END IF;
4383: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4384: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4385: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4386: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4387: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4388: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4389: END IF;
4382: END IF;
4383: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4384: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4385: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4386: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4387: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4388: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4389: END IF;
4390: END;
4383: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4384: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4385: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4386: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4387: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4388: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4389: END IF;
4390: END;
4391: /************ End of find_persons private procedures **********/
4384: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4385: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4386: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4387: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4388: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4389: END IF;
4390: END;
4391: /************ End of find_persons private procedures **********/
4392:
4419: g_score_until_thresh := false;
4420: END IF;
4421: -- ************************************************************
4422: -- STEP 2. Setup of intermedia query strings for Acquisition query
4423: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4424: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4425: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4426: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4427: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4420: END IF;
4421: -- ************************************************************
4422: -- STEP 2. Setup of intermedia query strings for Acquisition query
4423: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4424: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4425: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4426: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4427: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4428:
4421: -- ************************************************************
4422: -- STEP 2. Setup of intermedia query strings for Acquisition query
4423: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4424: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4425: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4426: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4427: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4428:
4429: -- Setup Search Context ID
4422: -- STEP 2. Setup of intermedia query strings for Acquisition query
4423: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4424: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4425: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4426: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4427: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4428:
4429: -- Setup Search Context ID
4430: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4429: -- Setup Search Context ID
4430: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4431: x_search_ctx_id := l_search_ctx_id;
4432:
4433: IF l_party_contains_str IS NULL THEN
4434: defpt := 1;
4435: END IF;
4436: IF l_party_site_contains_str IS NULL THEN
4437: defps := 1;
4432:
4433: IF l_party_contains_str IS NULL THEN
4434: defpt := 1;
4435: END IF;
4436: IF l_party_site_contains_str IS NULL THEN
4437: defps := 1;
4438: END IF;
4439: IF l_contact_contains_str IS NULL THEN
4440: defct := 1;
4435: END IF;
4436: IF l_party_site_contains_str IS NULL THEN
4437: defps := 1;
4438: END IF;
4439: IF l_contact_contains_str IS NULL THEN
4440: defct := 1;
4441: END IF;
4442: IF l_contact_pt_contains_str IS NULL THEN
4443: defcpt := 1;
4438: END IF;
4439: IF l_contact_contains_str IS NULL THEN
4440: defct := 1;
4441: END IF;
4442: IF l_contact_pt_contains_str IS NULL THEN
4443: defcpt := 1;
4444: END IF;
4445:
4446: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4444: END IF;
4445:
4446: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4447: 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);
4448: 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);
4449: 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);
4450: 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);
4451: 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);
4452: 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);
4445:
4446: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4447: 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);
4448: 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);
4449: 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);
4450: 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);
4451: 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);
4452: 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);
4453: END IF;
4446: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4447: 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);
4448: 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);
4449: 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);
4450: 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);
4451: 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);
4452: 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);
4453: END IF;
4454: FOR I in 1..5 LOOP
4447: 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);
4448: 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);
4449: 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);
4450: 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);
4451: 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);
4452: 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);
4453: END IF;
4454: FOR I in 1..5 LOOP
4455: do_eval(I);
4453: END IF;
4454: FOR I in 1..5 LOOP
4455: do_eval(I);
4456: END LOOP;
4457: IF l_contact_contains_str IS NOT NULL THEN
4458: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4459: END IF;
4460: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4461: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 11 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4454: FOR I in 1..5 LOOP
4455: do_eval(I);
4456: END LOOP;
4457: IF l_contact_contains_str IS NOT NULL THEN
4458: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4459: END IF;
4460: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4461: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 11 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4462: END IF;
5305: x_num_matches OUT NUMBER
5306: ) IS
5307:
5308: -- Strings to hold the generated Intermedia query strings
5309: l_party_contains_str VARCHAR2(32000);
5310: l_match_idx NUMBER;
5311: l_party_site_contains_str VARCHAR2(32000);
5312: l_contact_contains_str VARCHAR2(32000);
5313: l_contact_pt_contains_str VARCHAR2(32000);
5307:
5308: -- Strings to hold the generated Intermedia query strings
5309: l_party_contains_str VARCHAR2(32000);
5310: l_match_idx NUMBER;
5311: l_party_site_contains_str VARCHAR2(32000);
5312: l_contact_contains_str VARCHAR2(32000);
5313: l_contact_pt_contains_str VARCHAR2(32000);
5314: l_tmp VARCHAR2(32000);
5315:
5308: -- Strings to hold the generated Intermedia query strings
5309: l_party_contains_str VARCHAR2(32000);
5310: l_match_idx NUMBER;
5311: l_party_site_contains_str VARCHAR2(32000);
5312: l_contact_contains_str VARCHAR2(32000);
5313: l_contact_pt_contains_str VARCHAR2(32000);
5314: l_tmp VARCHAR2(32000);
5315:
5316: -- Other local variables
5309: l_party_contains_str VARCHAR2(32000);
5310: l_match_idx NUMBER;
5311: l_party_site_contains_str VARCHAR2(32000);
5312: l_contact_contains_str VARCHAR2(32000);
5313: l_contact_pt_contains_str VARCHAR2(32000);
5314: l_tmp VARCHAR2(32000);
5315:
5316: -- Other local variables
5317: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5394: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5395:
5396: -- ************************************************************
5397: -- STEP 2. Setup of intermedia query strings for Acquisition query
5398: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5399: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5400: 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);
5401:
5402: -- Setup Search Context ID
5395:
5396: -- ************************************************************
5397: -- STEP 2. Setup of intermedia query strings for Acquisition query
5398: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5399: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5400: 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);
5401:
5402: -- Setup Search Context ID
5403: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5404: x_search_ctx_id := l_search_ctx_id;
5405:
5406: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5407: 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);
5408: 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);
5409: 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);
5410: 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);
5411: END IF;
5412: IF l_party_site_contains_str IS NULL THEN
5405:
5406: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5407: 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);
5408: 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);
5409: 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);
5410: 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);
5411: END IF;
5412: IF l_party_site_contains_str IS NULL THEN
5413: defps := 1;
5408: 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);
5409: 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);
5410: 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);
5411: END IF;
5412: IF l_party_site_contains_str IS NULL THEN
5413: defps := 1;
5414: END IF;
5415: IF l_contact_pt_contains_str IS NULL THEN
5416: defcpt := 1;
5411: END IF;
5412: IF l_party_site_contains_str IS NULL THEN
5413: defps := 1;
5414: END IF;
5415: IF l_contact_pt_contains_str IS NULL THEN
5416: defcpt := 1;
5417: END IF;
5418:
5419: IF l_party_site_contains_str IS NOT NULL THEN
5415: IF l_contact_pt_contains_str IS NULL THEN
5416: defcpt := 1;
5417: END IF;
5418:
5419: IF l_party_site_contains_str IS NOT NULL THEN
5420: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5421: LOOP
5422: FETCH l_party_site_cur INTO
5423: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15, l_TX17, l_TX22;
5416: defcpt := 1;
5417: END IF;
5418:
5419: IF l_party_site_contains_str IS NOT NULL THEN
5420: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5421: LOOP
5422: FETCH l_party_site_cur INTO
5423: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15, l_TX17, l_TX22;
5424: EXIT WHEN l_party_site_cur%NOTFOUND;
5439: END LOOP;
5440: CLOSE l_party_site_cur;
5441: END IF;
5442:
5443: IF l_contact_pt_contains_str IS NOT NULL THEN
5444: SAVEPOINT eval_start;
5445: IF l_match_str = ' AND ' OR (
5446: ((l_cpt_max_score/l_entered_max_score)<(77/700))
5447: ) THEN
5447: ) THEN
5448: FORALL I in 1..l_match_ps_list.COUNT
5449: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5450: values (l_search_ctx_id,l_match_ps_list(I));
5451: 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');
5452: ELSE
5453: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5454: END IF;
5455: LOOP
5449: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5450: values (l_search_ctx_id,l_match_ps_list(I));
5451: 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');
5452: ELSE
5453: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5454: END IF;
5455: LOOP
5456: FETCH l_contact_pt_cur INTO
5457: 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;
5549: x_num_matches OUT NUMBER
5550: ) IS
5551:
5552: -- Strings to hold the generated Intermedia query strings
5553: l_party_contains_str VARCHAR2(32000);
5554: l_party_site_contains_str VARCHAR2(32000);
5555: l_contact_contains_str VARCHAR2(32000);
5556: l_contact_pt_contains_str VARCHAR2(32000);
5557: l_tmp VARCHAR2(32000);
5550: ) IS
5551:
5552: -- Strings to hold the generated Intermedia query strings
5553: l_party_contains_str VARCHAR2(32000);
5554: l_party_site_contains_str VARCHAR2(32000);
5555: l_contact_contains_str VARCHAR2(32000);
5556: l_contact_pt_contains_str VARCHAR2(32000);
5557: l_tmp VARCHAR2(32000);
5558:
5551:
5552: -- Strings to hold the generated Intermedia query strings
5553: l_party_contains_str VARCHAR2(32000);
5554: l_party_site_contains_str VARCHAR2(32000);
5555: l_contact_contains_str VARCHAR2(32000);
5556: l_contact_pt_contains_str VARCHAR2(32000);
5557: l_tmp VARCHAR2(32000);
5558:
5559: -- Other local variables
5552: -- Strings to hold the generated Intermedia query strings
5553: l_party_contains_str VARCHAR2(32000);
5554: l_party_site_contains_str VARCHAR2(32000);
5555: l_contact_contains_str VARCHAR2(32000);
5556: l_contact_pt_contains_str VARCHAR2(32000);
5557: l_tmp VARCHAR2(32000);
5558:
5559: -- Other local variables
5560: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5626: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5627:
5628: -- ************************************************************
5629: -- STEP 2. Setup of intermedia query strings for Acquisition query
5630: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5631: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5632: 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);
5633:
5634: -- Setup Search Context ID
5627:
5628: -- ************************************************************
5629: -- STEP 2. Setup of intermedia query strings for Acquisition query
5630: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5631: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5632: 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);
5633:
5634: -- Setup Search Context ID
5635: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5636: x_search_ctx_id := l_search_ctx_id;
5637:
5638: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5639: 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);
5640: 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);
5641: 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);
5642: 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);
5643: END IF;
5644: IF l_contact_contains_str IS NULL THEN
5637:
5638: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5639: 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);
5640: 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);
5641: 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);
5642: 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);
5643: END IF;
5644: IF l_contact_contains_str IS NULL THEN
5645: defct := 1;
5640: 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);
5641: 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);
5642: 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);
5643: END IF;
5644: IF l_contact_contains_str IS NULL THEN
5645: defct := 1;
5646: END IF;
5647: IF l_contact_pt_contains_str IS NULL THEN
5648: defcpt := 1;
5643: END IF;
5644: IF l_contact_contains_str IS NULL THEN
5645: defct := 1;
5646: END IF;
5647: IF l_contact_pt_contains_str IS NULL THEN
5648: defcpt := 1;
5649: END IF;
5650:
5651: IF l_contact_contains_str IS NOT NULL THEN
5647: IF l_contact_pt_contains_str IS NULL THEN
5648: defcpt := 1;
5649: END IF;
5650:
5651: IF l_contact_contains_str IS NOT NULL THEN
5652: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5653: LOOP
5654: FETCH l_contact_cur INTO
5655: l_org_contact_id, l_ct_party_id , l_TX6, l_TX5;
5648: defcpt := 1;
5649: END IF;
5650:
5651: IF l_contact_contains_str IS NOT NULL THEN
5652: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5653: LOOP
5654: FETCH l_contact_cur INTO
5655: l_org_contact_id, l_ct_party_id , l_TX6, l_TX5;
5656: EXIT WHEN l_contact_cur%NOTFOUND;
5669: END LOOP;
5670: CLOSE l_contact_cur;
5671: END IF;
5672:
5673: IF l_contact_pt_contains_str IS NOT NULL THEN
5674: SAVEPOINT eval_start;
5675: IF l_match_str = ' AND ' OR (
5676: ((l_cpt_max_score/l_entered_max_score)<(77/700))
5677: ) THEN
5677: ) THEN
5678: FORALL I in 1..l_match_contact_list.COUNT
5679: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5680: values (l_search_ctx_id,l_match_contact_list(I));
5681: 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');
5682: ELSE
5683: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5684: END IF;
5685: LOOP
5679: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5680: values (l_search_ctx_id,l_match_contact_list(I));
5681: 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');
5682: ELSE
5683: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5684: END IF;
5685: LOOP
5686: FETCH l_contact_pt_cur INTO
5687: 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;
5777: ) IS
5778:
5779:
5780: -- Strings to hold the generated Intermedia query strings
5781: l_contact_pt_contains_str VARCHAR2(32000);
5782: l_tmp VARCHAR2(32000);
5783:
5784: -- Other local variables
5785: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5838: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5839:
5840: -- ************************************************************
5841: -- STEP 2. Setup of intermedia query strings for Acquisition query
5842: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5843: 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);
5844:
5845: -- Setup Search Context ID
5846: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5846: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5847: x_search_ctx_id := l_search_ctx_id;
5848: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5849: 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);
5850: 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);
5851: 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);
5852: END IF;
5853:
5854: IF l_contact_pt_contains_str IS NOT NULL THEN
5850: 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);
5851: 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);
5852: END IF;
5853:
5854: IF l_contact_pt_contains_str IS NOT NULL THEN
5855: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5856: cnt := 1;
5857: LOOP
5858: FETCH l_contact_pt_cur INTO
5851: 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);
5852: END IF;
5853:
5854: IF l_contact_pt_contains_str IS NOT NULL THEN
5855: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5856: cnt := 1;
5857: LOOP
5858: FETCH l_contact_pt_cur INTO
5859: 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;
5922: x_search_ctx_id IN OUT NUMBER
5923: ) IS
5924:
5925: -- Strings to hold the generated Intermedia query strings
5926: l_party_contains_str VARCHAR2(32000);
5927: l_party_site_contains_str VARCHAR2(32000);
5928: l_contact_contains_str VARCHAR2(32000);
5929: l_contact_pt_contains_str VARCHAR2(32000);
5930: l_tmp VARCHAR2(32000);
5923: ) IS
5924:
5925: -- Strings to hold the generated Intermedia query strings
5926: l_party_contains_str VARCHAR2(32000);
5927: l_party_site_contains_str VARCHAR2(32000);
5928: l_contact_contains_str VARCHAR2(32000);
5929: l_contact_pt_contains_str VARCHAR2(32000);
5930: l_tmp VARCHAR2(32000);
5931:
5924:
5925: -- Strings to hold the generated Intermedia query strings
5926: l_party_contains_str VARCHAR2(32000);
5927: l_party_site_contains_str VARCHAR2(32000);
5928: l_contact_contains_str VARCHAR2(32000);
5929: l_contact_pt_contains_str VARCHAR2(32000);
5930: l_tmp VARCHAR2(32000);
5931:
5932: -- Other local variables
5925: -- Strings to hold the generated Intermedia query strings
5926: l_party_contains_str VARCHAR2(32000);
5927: l_party_site_contains_str VARCHAR2(32000);
5928: l_contact_contains_str VARCHAR2(32000);
5929: l_contact_pt_contains_str VARCHAR2(32000);
5930: l_tmp VARCHAR2(32000);
5931:
5932: -- Other local variables
5933: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6018: g_score_until_thresh := false;
6019: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6020: -- ************************************************************
6021: -- STEP 2. Setup of intermedia query strings for Acquisition query
6022: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6023: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6024: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6025: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6026:
6019: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6020: -- ************************************************************
6021: -- STEP 2. Setup of intermedia query strings for Acquisition query
6022: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6023: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6024: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6025: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6026:
6027: -- Setup Search Context ID
6020: -- ************************************************************
6021: -- STEP 2. Setup of intermedia query strings for Acquisition query
6022: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6023: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6024: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6025: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6026:
6027: -- Setup Search Context ID
6028: IF x_search_ctx_id IS NULL THEN
6040: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX32,l_TX33,l_TX34,l_TX35,l_TX40,l_TX39,1);
6041: END LOOP;
6042: CLOSE l_party_cur;
6043:
6044: IF l_party_site_contains_str IS NOT NULL THEN
6045: l_max_score := 0;
6046: l_max_id := 0;
6047: l_max_idx := 0;
6048: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6045: l_max_score := 0;
6046: l_max_id := 0;
6047: l_max_idx := 0;
6048: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6049: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
6050: ELSE
6051: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
6052: END IF;
6053: LOOP
6047: l_max_idx := 0;
6048: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6049: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
6050: ELSE
6051: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
6052: END IF;
6053: LOOP
6054: FETCH l_party_site_cur INTO
6055: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15, l_TX17, l_TX22;
6075: INSERT_PARTY_SITES_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_party_site_list(l_max_idx), g_party_site_stage_list(l_max_idx) ,l_max_TX3,l_max_TX4,l_max_TX9,l_max_TX10,l_max_TX11,l_max_TX14,l_max_TX15,l_max_TX17,l_max_TX22,l_max_idx);
6076: END IF;
6077: END IF;
6078:
6079: IF l_contact_contains_str IS NOT NULL THEN
6080: l_max_score := 0;
6081: l_max_id := 0;
6082: l_max_idx := 0;
6083: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
6079: IF l_contact_contains_str IS NOT NULL THEN
6080: l_max_score := 0;
6081: l_max_id := 0;
6082: l_max_idx := 0;
6083: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
6084: LOOP
6085: FETCH l_contact_cur INTO
6086: l_org_contact_id, l_ct_party_id , l_TX6, l_TX5;
6087: EXIT WHEN l_contact_cur%NOTFOUND;
6099: 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_TX6,l_max_TX5,l_max_idx);
6100: END IF;
6101: END IF;
6102:
6103: IF l_contact_pt_contains_str IS NOT NULL THEN
6104: l_max_score := 0;
6105: l_max_id := 0;
6106: l_max_idx := 0;
6107: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6104: l_max_score := 0;
6105: l_max_id := 0;
6106: l_max_idx := 0;
6107: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6108: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
6109: ELSE
6110: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
6111: END IF;
6112: LOOP
6106: l_max_idx := 0;
6107: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6108: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
6109: ELSE
6110: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
6111: END IF;
6112: LOOP
6113: FETCH l_contact_pt_cur INTO
6114: 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;