[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:
1327: p_party_max_score NUMBER,
1328: p_denorm_max_score NUMBER,
1329: p_non_denorm_max_score NUMBER,
1330: p_threshold NUMBER) RETURN VARCHAR2 IS
1331: l_party_contains_str VARCHAR2(32000);
1332: l_party_filter VARCHAR2(1) := null;
1333: l_prim_temp VARCHAR2(4000);
1334: l_denorm_str VARCHAR2(4000);
1335: BEGIN
1371:
1372: IF l_denorm_str IS NOT NULL THEN
1373: IF (p_non_denorm_max_score+40)>=p_threshold THEN
1374: l_denorm_str := NULL;
1375: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1376: ELSIF (p_non_denorm_max_score+p_denorm_max_score+40)>=p_threshold THEN
1377: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1378: l_denorm_str := NULL;
1379: END IF;
1373: IF (p_non_denorm_max_score+40)>=p_threshold THEN
1374: l_denorm_str := NULL;
1375: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1376: ELSIF (p_non_denorm_max_score+p_denorm_max_score+40)>=p_threshold THEN
1377: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1378: l_denorm_str := NULL;
1379: END IF;
1380: ELSE
1381: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1377: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1378: l_denorm_str := NULL;
1379: END IF;
1380: ELSE
1381: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1382: END IF;
1383:
1384: -- Setup query string for DUNS_NUMBER_C
1385: l_prim_temp := null;
1394:
1395: IF l_denorm_str IS NOT NULL THEN
1396: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1397: l_denorm_str := NULL;
1398: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1399: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1400: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1401: l_denorm_str := NULL;
1402: END IF;
1396: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1397: l_denorm_str := NULL;
1398: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1399: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1400: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1401: l_denorm_str := NULL;
1402: END IF;
1403: ELSE
1404: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1400: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1401: l_denorm_str := NULL;
1402: END IF;
1403: ELSE
1404: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1405: END IF;
1406:
1407: -- Setup query string for JGZZ_FISCAL_CODE
1408: l_prim_temp := null;
1411:
1412: IF l_denorm_str IS NOT NULL THEN
1413: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1414: l_denorm_str := NULL;
1415: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1416: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1417: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1418: l_denorm_str := NULL;
1419: END IF;
1413: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1414: l_denorm_str := NULL;
1415: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1416: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1417: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1418: l_denorm_str := NULL;
1419: END IF;
1420: ELSE
1421: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1417: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1418: l_denorm_str := NULL;
1419: END IF;
1420: ELSE
1421: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1422: END IF;
1423:
1424: -- Setup query string for PARTY_NUMBER
1425: l_prim_temp := null;
1428:
1429: IF l_denorm_str IS NOT NULL THEN
1430: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1431: l_denorm_str := NULL;
1432: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1433: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1434: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1435: l_denorm_str := NULL;
1436: END IF;
1430: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1431: l_denorm_str := NULL;
1432: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1433: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1434: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1435: l_denorm_str := NULL;
1436: END IF;
1437: ELSE
1438: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1434: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1435: l_denorm_str := NULL;
1436: END IF;
1437: ELSE
1438: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1439: END IF;
1440:
1441: -- Setup query string for ALL_ACCOUNT_NUMBERS
1442: l_prim_temp := null;
1445:
1446: IF l_denorm_str IS NOT NULL THEN
1447: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1448: l_denorm_str := NULL;
1449: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1450: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1451: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1452: l_denorm_str := NULL;
1453: END IF;
1447: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1448: l_denorm_str := NULL;
1449: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1450: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1451: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1452: l_denorm_str := NULL;
1453: END IF;
1454: ELSE
1455: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1451: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1452: l_denorm_str := NULL;
1453: END IF;
1454: ELSE
1455: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1456: END IF;
1457: IF lengthb(l_party_contains_str) > 4000 THEN
1458: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1459: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1453: END IF;
1454: ELSE
1455: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1456: END IF;
1457: IF lengthb(l_party_contains_str) > 4000 THEN
1458: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1459: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1460: FND_MSG_PUB.ADD;
1461: RAISE FND_API.G_EXC_ERROR;
1459: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1460: FND_MSG_PUB.ADD;
1461: RAISE FND_API.G_EXC_ERROR;
1462: END IF;
1463: IF (p_match_str = ' AND ' OR p_party_max_score
1465: 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);
1466: END IF;
1467: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1461: RAISE FND_API.G_EXC_ERROR;
1462: END IF;
1463: IF (p_match_str = ' AND ' OR p_party_max_score
1465: 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);
1466: END IF;
1467: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1468: 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);
1469: END IF;
1464: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1465: 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);
1466: END IF;
1467: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1468: 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);
1469: END IF;
1470: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1471: 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);
1472: END IF;
1469: END IF;
1470: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1471: 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);
1472: END IF;
1473: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1474: ELSE
1475: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1476: 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);
1477: 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);
1472: END IF;
1473: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1474: ELSE
1475: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1476: 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);
1477: 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);
1478: END IF;
1479: RETURN l_party_contains_str;
1480: END IF;
1473: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1474: ELSE
1475: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1476: 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);
1477: 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);
1478: END IF;
1479: RETURN l_party_contains_str;
1480: END IF;
1481: END;
1475: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1476: 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);
1477: 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);
1478: END IF;
1479: RETURN l_party_contains_str;
1480: END IF;
1481: END;
1482: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1483: l_contains_str VARCHAR2(32000);
1479: RETURN l_party_contains_str;
1480: END IF;
1481: END;
1482: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1483: l_contains_str VARCHAR2(32000);
1484: l_contains_str_temp VARCHAR2(32000);
1485: l_den_contains_str VARCHAR2(32000);
1486: l_den_contains_str_temp VARCHAR2(32000);
1487: l_filter_str VARCHAR2(4000) := null;
1480: END IF;
1481: END;
1482: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1483: l_contains_str VARCHAR2(32000);
1484: l_contains_str_temp VARCHAR2(32000);
1485: l_den_contains_str VARCHAR2(32000);
1486: l_den_contains_str_temp VARCHAR2(32000);
1487: l_filter_str VARCHAR2(4000) := null;
1488: l_prim_temp VARCHAR2(4000) := null;
1481: END;
1482: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1483: l_contains_str VARCHAR2(32000);
1484: l_contains_str_temp VARCHAR2(32000);
1485: l_den_contains_str VARCHAR2(32000);
1486: l_den_contains_str_temp VARCHAR2(32000);
1487: l_filter_str VARCHAR2(4000) := null;
1488: l_prim_temp VARCHAR2(4000) := null;
1489: l_prim_temp_den VARCHAR2(4000) := null;
1482: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1483: l_contains_str VARCHAR2(32000);
1484: l_contains_str_temp VARCHAR2(32000);
1485: l_den_contains_str VARCHAR2(32000);
1486: l_den_contains_str_temp VARCHAR2(32000);
1487: l_filter_str VARCHAR2(4000) := null;
1488: l_prim_temp VARCHAR2(4000) := null;
1489: l_prim_temp_den VARCHAR2(4000) := null;
1490: BEGIN
1492: 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);
1493: END IF;
1494: x_denorm_str := NULL;
1495:
1496: -- Dynamic setup of party site contains str
1497: --
1498: -- For each primary transformation add to intermedia query if it
1499: -- is not null
1500: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1497: --
1498: -- For each primary transformation add to intermedia query if it
1499: -- is not null
1500: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1501: l_contains_str_temp := null;
1502: l_den_contains_str_temp := null;
1503: l_filter_str := null;
1504:
1505: -- Setup query string for ADDRESS
1498: -- For each primary transformation add to intermedia query if it
1499: -- is not null
1500: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1501: l_contains_str_temp := null;
1502: l_den_contains_str_temp := null;
1503: l_filter_str := null;
1504:
1505: -- Setup query string for ADDRESS
1506: l_prim_temp := null;
1506: l_prim_temp := null;
1507: l_prim_temp_den := null;
1508: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRADDRESS
1509: g_party_site_stage_list(I).TX4,'A27',l_prim_temp);
1510: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1511:
1512: -- Setup query string for CITY
1513: l_prim_temp := null;
1514: l_prim_temp_den := null;
1515: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1516: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1517: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1518: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1519: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1520: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1521:
1522: -- Setup query string for POSTAL_CODE
1523: l_prim_temp := null;
1516: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1517: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1518: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1519: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1520: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1521:
1522: -- Setup query string for POSTAL_CODE
1523: l_prim_temp := null;
1524: l_prim_temp_den := null;
1525: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1526: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1527: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1528: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1529: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1530: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1531:
1532: -- Setup query string for PROVINCE
1533: l_prim_temp := null;
1526: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1527: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1528: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1529: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1530: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1531:
1532: -- Setup query string for PROVINCE
1533: l_prim_temp := null;
1534: l_prim_temp_den := null;
1535: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1536: g_party_site_stage_list(I).TX13,'A31',l_prim_temp);
1537: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1538: g_party_site_stage_list(I).TX13,'D_PS',l_prim_temp_den);
1539: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1540: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1541:
1542: -- Setup query string for STATE
1543: l_prim_temp := null;
1536: g_party_site_stage_list(I).TX13,'A31',l_prim_temp);
1537: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1538: g_party_site_stage_list(I).TX13,'D_PS',l_prim_temp_den);
1539: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1540: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1541:
1542: -- Setup query string for STATE
1543: l_prim_temp := null;
1544: l_prim_temp_den := null;
1545: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1546: g_party_site_stage_list(I).TX15,'A32',l_prim_temp);
1547: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1548: g_party_site_stage_list(I).TX15,'D_PS',l_prim_temp_den);
1549: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1550: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1551:
1552: -- Setup query string for COUNTRY
1553: l_prim_temp := null;
1546: g_party_site_stage_list(I).TX15,'A32',l_prim_temp);
1547: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1548: g_party_site_stage_list(I).TX15,'D_PS',l_prim_temp_den);
1549: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1550: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1551:
1552: -- Setup query string for COUNTRY
1553: l_prim_temp := null;
1554: l_prim_temp_den := null;
1555: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1556: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1557: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1558: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1559: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1560: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1561:
1562: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1563: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1556: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1557: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1558: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1559: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1560: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1561:
1562: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1563: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1564: END LOOP;
1558: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1559: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1560: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1561:
1562: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1563: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1564: END LOOP;
1565: -- Add the search criteria to query string
1566: IF lengthb(l_contains_str) > 4000 THEN
1559: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1560: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1561:
1562: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1563: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1564: END LOOP;
1565: -- Add the search criteria to query string
1566: IF lengthb(l_contains_str) > 4000 THEN
1567: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1562: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1563: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1564: END LOOP;
1565: -- Add the search criteria to query string
1566: IF lengthb(l_contains_str) > 4000 THEN
1567: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1568: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1569: FND_MSG_PUB.ADD;
1570: RAISE FND_API.G_EXC_ERROR;
1568: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1569: FND_MSG_PUB.ADD;
1570: RAISE FND_API.G_EXC_ERROR;
1571: END IF;
1572: x_denorm_str := l_den_contains_str;
1573: RETURN l_contains_str;
1574: END;
1575:
1576: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1569: FND_MSG_PUB.ADD;
1570: RAISE FND_API.G_EXC_ERROR;
1571: END IF;
1572: x_denorm_str := l_den_contains_str;
1573: RETURN l_contains_str;
1574: END;
1575:
1576: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1577: l_contains_str VARCHAR2(32000);
1573: RETURN l_contains_str;
1574: END;
1575:
1576: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1577: l_contains_str VARCHAR2(32000);
1578: l_contains_str_temp VARCHAR2(32000);
1579: l_den_contains_str VARCHAR2(32000);
1580: l_den_contains_str_temp VARCHAR2(32000);
1581: l_filter_str VARCHAR2(4000) := null;
1574: END;
1575:
1576: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1577: l_contains_str VARCHAR2(32000);
1578: l_contains_str_temp VARCHAR2(32000);
1579: l_den_contains_str VARCHAR2(32000);
1580: l_den_contains_str_temp VARCHAR2(32000);
1581: l_filter_str VARCHAR2(4000) := null;
1582: l_prim_temp VARCHAR2(4000) := null;
1575:
1576: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1577: l_contains_str VARCHAR2(32000);
1578: l_contains_str_temp VARCHAR2(32000);
1579: l_den_contains_str VARCHAR2(32000);
1580: l_den_contains_str_temp VARCHAR2(32000);
1581: l_filter_str VARCHAR2(4000) := null;
1582: l_prim_temp VARCHAR2(4000) := null;
1583: l_prim_temp_den VARCHAR2(4000) := null;
1576: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1577: l_contains_str VARCHAR2(32000);
1578: l_contains_str_temp VARCHAR2(32000);
1579: l_den_contains_str VARCHAR2(32000);
1580: l_den_contains_str_temp VARCHAR2(32000);
1581: l_filter_str VARCHAR2(4000) := null;
1582: l_prim_temp VARCHAR2(4000) := null;
1583: l_prim_temp_den VARCHAR2(4000) := null;
1584: BEGIN
1589: RETURN NULL;
1590: END;
1591:
1592: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1593: l_contains_str VARCHAR2(32000);
1594: l_contains_str_temp VARCHAR2(32000);
1595: l_den_contains_str VARCHAR2(32000);
1596: l_den_contains_str_temp VARCHAR2(32000);
1597: l_filter_str VARCHAR2(4000) := null;
1590: END;
1591:
1592: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1593: l_contains_str VARCHAR2(32000);
1594: l_contains_str_temp VARCHAR2(32000);
1595: l_den_contains_str VARCHAR2(32000);
1596: l_den_contains_str_temp VARCHAR2(32000);
1597: l_filter_str VARCHAR2(4000) := null;
1598: l_prim_temp VARCHAR2(4000) := null;
1591:
1592: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1593: l_contains_str VARCHAR2(32000);
1594: l_contains_str_temp VARCHAR2(32000);
1595: l_den_contains_str VARCHAR2(32000);
1596: l_den_contains_str_temp VARCHAR2(32000);
1597: l_filter_str VARCHAR2(4000) := null;
1598: l_prim_temp VARCHAR2(4000) := null;
1599: l_prim_temp_den VARCHAR2(4000) := null;
1592: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1593: l_contains_str VARCHAR2(32000);
1594: l_contains_str_temp VARCHAR2(32000);
1595: l_den_contains_str VARCHAR2(32000);
1596: l_den_contains_str_temp VARCHAR2(32000);
1597: l_filter_str VARCHAR2(4000) := null;
1598: l_prim_temp VARCHAR2(4000) := null;
1599: l_prim_temp_den VARCHAR2(4000) := null;
1600: TYPE CONTACT_PT_REC_TYPE IS RECORD (
1608: 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);
1609: END IF;
1610: x_denorm_str := NULL;
1611:
1612: -- Dynamic setup of party site contains str
1613: --
1614: -- For each primary transformation add to intermedia query if it
1615: -- is not null
1616: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1613: --
1614: -- For each primary transformation add to intermedia query if it
1615: -- is not null
1616: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1617: l_contains_str_temp := null;
1618: l_den_contains_str_temp := null;
1619: l_filter_str := null;
1620: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1621: if(l_cnt_pt_type.count>0) then
1614: -- For each primary transformation add to intermedia query if it
1615: -- is not null
1616: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1617: l_contains_str_temp := null;
1618: l_den_contains_str_temp := null;
1619: l_filter_str := null;
1620: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1621: if(l_cnt_pt_type.count>0) then
1622: x_modify := 'Y';
1638: l_prim_temp := null;
1639: l_prim_temp_den := null;
1640: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL CTX
1641: g_contact_pt_stage_list(I).TX1,'A44',l_prim_temp);
1642: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1643:
1644: -- Setup query string for URL
1645: l_prim_temp := null;
1646: l_prim_temp_den := null;
1645: l_prim_temp := null;
1646: l_prim_temp_den := null;
1647: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (URL) + WRDOMAIN
1648: g_contact_pt_stage_list(I).TX8,'A49',l_prim_temp);
1649: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1650:
1651: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1652: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1653: IF N>1 THEN
1647: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (URL) + WRDOMAIN
1648: g_contact_pt_stage_list(I).TX8,'A49',l_prim_temp);
1649: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1650:
1651: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1652: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1653: IF N>1 THEN
1654: distinct_search_cpt_types := N-1;
1655: ELSE
1648: g_contact_pt_stage_list(I).TX8,'A49',l_prim_temp);
1649: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1650:
1651: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1652: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1653: IF N>1 THEN
1654: distinct_search_cpt_types := N-1;
1655: ELSE
1656: distinct_search_cpt_types := N;
1659: 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);
1660: END IF;
1661: END LOOP;
1662: -- Add the search criteria to query string
1663: IF lengthb(l_contains_str) > 4000 THEN
1664: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1665: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1666: FND_MSG_PUB.ADD;
1667: RAISE FND_API.G_EXC_ERROR;
1665: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1666: FND_MSG_PUB.ADD;
1667: RAISE FND_API.G_EXC_ERROR;
1668: END IF;
1669: x_denorm_str := l_den_contains_str;
1670: RETURN l_contains_str;
1671: END;
1672:
1673: PROCEDURE open_party_cursor_direct (
1666: FND_MSG_PUB.ADD;
1667: RAISE FND_API.G_EXC_ERROR;
1668: END IF;
1669: x_denorm_str := l_den_contains_str;
1670: RETURN l_contains_str;
1671: END;
1672:
1673: PROCEDURE open_party_cursor_direct (
1674: p_dup_party_id NUMBER,
1674: p_dup_party_id NUMBER,
1675: p_restrict_sql VARCHAR2,
1676: p_match_str VARCHAR2,
1677: p_search_merged VARCHAR2,
1678: p_party_contains_str VARCHAR2,
1679: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1680: l_sqlstr VARCHAR2(4000);
1681: l_search_merged VARCHAR2(1);
1682: BEGIN
1691: END IF;
1692: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1693: 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);
1694: END IF;
1695: 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
1696: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1697: 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);
1698: END IF;
1699: OPEN x_cursor FOR
1735: l_sqlstr := l_sqlstr || ' (TX45 like :TX45||'' %'' ';
1736: l_sqlstr := l_sqlstr || ' )) ';
1737: l_sqlstr := l_sqlstr || ' ) ';
1738: END IF;
1739: 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
1740: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1741: 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);
1742: END IF;
1743: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1737: l_sqlstr := l_sqlstr || ' ) ';
1738: END IF;
1739: 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
1740: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1741: 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);
1742: END IF;
1743: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1744: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX10||'' %'' ';
1745: l_sqlstr := l_sqlstr || ' )) ';
1770: END IF;
1771: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1772: 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);
1773: END IF;
1774: 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
1775: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1776: 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);
1777: END IF;
1778: 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
1772: 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);
1773: END IF;
1774: 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
1775: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1776: 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);
1777: END IF;
1778: 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
1779: ,g_party_stage_rec.TX34
1780: ,g_party_stage_rec.TX34
1812: ,g_party_stage_rec.TX41
1813: ,g_party_stage_rec.TX45
1814: ,g_party_stage_rec.TX45
1815: ;
1816: 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
1817: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1818: 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);
1819: END IF;
1820: 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
1814: ,g_party_stage_rec.TX45
1815: ;
1816: 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
1817: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1818: 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);
1819: END IF;
1820: 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
1821: ,g_party_site_stage_list(1).TX10
1822: ,g_party_site_stage_list(1).TX10
1839: END;
1840: PROCEDURE open_party_cursor(
1841: p_dup_party_id NUMBER,
1842: p_restrict_sql VARCHAR2,
1843: p_contains_str VARCHAR2,
1844: p_search_ctx_id NUMBER,
1845: p_match_str VARCHAR2,
1846: p_search_merged VARCHAR2,
1847: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1858: l_search_merged := 'N';
1859: ELSE
1860: l_search_merged := p_search_merged;
1861: END IF;
1862: IF p_contains_str IS NULL THEN
1863: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1864: 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);
1865: END IF;
1866: OPEN x_cursor FOR
1860: l_search_merged := p_search_merged;
1861: END IF;
1862: IF p_contains_str IS NULL THEN
1863: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1864: 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);
1865: END IF;
1866: OPEN x_cursor FOR
1867: SELECT PARTY_ID , TX34, TX35, TX39, TX40, lpad(rtrim(TX41),9,chr(48)), TX45
1868: FROM HZ_STAGED_PARTIES stage
1877: END IF;
1878: OPEN x_cursor FOR
1879: SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID , TX34, TX35, TX39, TX40, lpad(rtrim(TX41),9,chr(48)), TX45
1880: FROM HZ_STAGED_PARTIES stage
1881: WHERE contains( concat_col, p_contains_str)>0
1882: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1883: AND( (l_search_merged ='Y' )
1884: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1885: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )
1890: END IF;
1891: OPEN x_cursor FOR
1892: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX34, TX35, TX39, TX40, lpad(rtrim(TX41),9,chr(48)), TX45
1893: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1894: WHERE contains( concat_col, p_contains_str)>0
1895: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1896: AND d.party_id = stage.party_id
1897: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1898: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1915: END IF;
1916: IF p_search_ctx_id IS NULL THEN
1917: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX34, TX35, TX39, TX40, lpad(rtrim(TX41),9,chr(48)), TX45'||
1918: ' FROM HZ_STAGED_PARTIES stage'||
1919: ' WHERE contains( concat_col, :cont)>0'||
1920: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1921: ' AND ('||p_restrict_sql||')' ||
1922: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1923: IF l_search_merged = 'Y' THEN
1927: ELSE
1928: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1929: END IF;
1930: output_long_strings('----------------------------------------------------------');
1931: output_long_strings('Party Contains String = '||p_contains_str);
1932: output_long_strings('Restrict Sql = '||p_restrict_sql);
1933: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1934: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1935: ,p_dup_party_id, p_dup_party_id;
1929: END IF;
1930: output_long_strings('----------------------------------------------------------');
1931: output_long_strings('Party Contains String = '||p_contains_str);
1932: output_long_strings('Restrict Sql = '||p_restrict_sql);
1933: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1934: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1935: ,p_dup_party_id, p_dup_party_id;
1936: END IF;
1937: END IF;
1960: PROCEDURE open_party_site_cursor(
1961: p_dup_party_id NUMBER,
1962: p_party_id NUMBER,
1963: p_restrict_sql VARCHAR2,
1964: p_contains_str VARCHAR2,
1965: p_search_ctx_id NUMBER,
1966: p_search_merged VARCHAR2,
1967: p_search_rel_sites VARCHAR2,
1968: p_person_api VARCHAR2,
1992: IF p_search_rel_sites = 'N' THEN
1993: OPEN x_cursor FOR
1994: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
1995: FROM HZ_STAGED_PARTY_SITES stage
1996: WHERE contains( concat_col, p_contains_str)>0
1997: AND( (l_search_merged ='Y' )
1998: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1999: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2000: AND stage.party_id = p_party_id;
2001: ELSE
2002: OPEN x_cursor FOR
2003: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
2004: FROM HZ_STAGED_PARTY_SITES stage
2005: WHERE contains( concat_col, p_contains_str)>0
2006: AND( (l_search_merged ='Y' )
2007: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2008: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2009: AND stage.party_id = p_party_id
2009: AND stage.party_id = p_party_id
2010: UNION
2011: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
2012: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
2013: WHERE contains( concat_col, p_contains_str)>0
2014: AND( (l_search_merged ='Y' )
2015: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2016: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2017: AND r.object_id = p_party_id
2031: END IF;
2032: OPEN x_cursor FOR
2033: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
2034: FROM HZ_STAGED_PARTY_SITES stage
2035: WHERE contains( concat_col, p_contains_str)>0
2036: AND EXISTS (
2037: SELECT 1 FROM HZ_STAGED_PARTIES p
2038: WHERE p.PARTY_ID = stage.PARTY_ID
2039: AND( (l_search_merged ='Y' )
2051: IF p_person_api = 'Y' THEN
2052: OPEN x_cursor FOR
2053: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
2054: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
2055: WHERE contains( concat_col, p_contains_str)>0
2056: AND( (l_search_merged ='Y' )
2057: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2058: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2059: AND d.search_context_id = p_search_ctx_id
2061: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2062: UNION
2063: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
2064: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
2065: WHERE contains( concat_col, p_contains_str)>0
2066: AND( (l_search_merged ='Y' )
2067: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2068: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2069: AND d.search_context_id = p_search_ctx_id
2074: ELSE
2075: OPEN x_cursor FOR
2076: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
2077: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
2078: WHERE contains( concat_col, p_contains_str)>0
2079: AND( (l_search_merged ='Y' )
2080: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2081: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2082: AND d.search_context_id = p_search_ctx_id
2119: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2120: END IF;
2121: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
2122: ' FROM HZ_STAGED_PARTY_SITES stage'||
2123: ' WHERE contains( concat_col, :cont)>0'||
2124: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2125: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2126: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2127: ' AND (ORG_CONTACT_ID IS NULL ' ||
2129: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2130: ' UNION ' ||
2131: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
2132: ' FROM HZ_STAGED_PARTY_SITES stage'||
2133: ' WHERE contains( concat_col, :cont)>0'||
2134: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2135: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2136: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2137: ' AND ORG_CONTACT_ID IN ' ||
2139: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2140: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2141: ' and ('||p_restrict_sql1|| ') )' ||
2142: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2143: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2144: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2145: ELSE
2146: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
2147: ' FROM HZ_STAGED_PARTY_SITES stage'||
2140: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2141: ' and ('||p_restrict_sql1|| ') )' ||
2142: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2143: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2144: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2145: ELSE
2146: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
2147: ' FROM HZ_STAGED_PARTY_SITES stage'||
2148: ' WHERE contains( concat_col, :cont)>0'||
2144: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2145: ELSE
2146: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
2147: ' FROM HZ_STAGED_PARTY_SITES stage'||
2148: ' WHERE contains( concat_col, :cont)>0'||
2149: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2150: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2151: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2152: ' AND EXISTS ('||
2154: ' WHERE p.party_id = stage.party_id ' ||
2155: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2156: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2157: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2158: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2159: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2160: ,p_dup_party_id, p_dup_party_id;
2161: END IF;
2162: ELSE
2161: END IF;
2162: ELSE
2163: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
2164: ' FROM HZ_STAGED_PARTY_SITES stage'||
2165: ' WHERE contains( concat_col, :cont)>0'||
2166: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2167: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2168: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2169: ' AND EXISTS ('||
2171: ' WHERE p.party_id = stage.party_id ' ||
2172: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2173: ' AND ('||p_restrict_sql||')' ||
2174: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2175: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2176: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2177: ,p_dup_party_id, p_dup_party_id;
2178: END IF;
2179: END IF;
2177: ,p_dup_party_id, p_dup_party_id;
2178: END IF;
2179: END IF;
2180: output_long_strings('----------------------------------------------------------');
2181: output_long_strings('Party Site Contains String = '||p_contains_str);
2182: output_long_strings('Restrict Sql = '||p_restrict_sql);
2183: exception
2184: when others then
2185: if (instrb(SQLERRM,'DRG-51030')>0) then
2205: PROCEDURE open_contact_cursor(
2206: p_dup_party_id NUMBER,
2207: p_party_id NUMBER,
2208: p_restrict_sql VARCHAR2,
2209: p_contains_str VARCHAR2,
2210: p_search_ctx_id NUMBER,
2211: p_search_merged VARCHAR2,
2212: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
2213: l_sqlstr VARCHAR2(4000);
2233: END IF;
2234: OPEN x_cursor FOR
2235: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
2236: FROM HZ_STAGED_CONTACTS stage
2237: WHERE contains( concat_col, p_contains_str)>0
2238: AND EXISTS (
2239: SELECT 1 FROM HZ_STAGED_PARTIES p
2240: WHERE p.PARTY_ID = stage.PARTY_ID
2241: AND( (l_search_merged ='Y' )
2256: END IF;
2257: OPEN x_cursor FOR
2258: SELECT ORG_CONTACT_ID, PARTY_ID
2259: FROM HZ_STAGED_CONTACTS stage
2260: WHERE contains( concat_col, p_contains_str)>0
2261: AND EXISTS (
2262: SELECT 1 FROM HZ_STAGED_PARTIES p
2263: WHERE p.PARTY_ID = stage.PARTY_ID
2264: AND( (l_search_merged ='Y' )
2275: END IF;
2276: OPEN x_cursor FOR
2277: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
2278: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
2279: WHERE contains( concat_col, p_contains_str)>0
2280: AND d.search_context_id = p_search_ctx_id
2281: AND d.party_id = stage.party_id
2282: AND( (l_search_merged ='Y' )
2283: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2309: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
2310: END IF;
2311: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
2312: ' FROM HZ_STAGED_CONTACTS stage'||
2313: ' WHERE contains( concat_col, :cont)>0'||
2314: ' AND EXISTS ('||
2315: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2316: ' WHERE p.party_id = stage.party_id ' ||
2317: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2319: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2320: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2321: ' AND ('||p_restrict_sql||')' ||
2322: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2323: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2324: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2325: ,p_dup_party_id, p_dup_party_id;
2326: END IF;
2327: output_long_strings('----------------------------------------------------------');
2324: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2325: ,p_dup_party_id, p_dup_party_id;
2326: END IF;
2327: output_long_strings('----------------------------------------------------------');
2328: output_long_strings('Contacts Contains String = '||p_contains_str);
2329: output_long_strings('Restrict Sql = '||p_restrict_sql);
2330: exception
2331: when others then
2332: if (instrb(SQLERRM,'DRG-51030')>0) then
2352: PROCEDURE open_contact_pt_cursor(
2353: p_dup_party_id NUMBER,
2354: p_party_id NUMBER,
2355: p_restrict_sql VARCHAR2,
2356: p_contains_str VARCHAR2,
2357: p_search_ctx_id NUMBER,
2358: p_search_merged VARCHAR2,
2359: p_search_rel_cpts VARCHAR2,
2360: p_person_api VARCHAR2,
2382: THEN
2383: OPEN x_cursor FOR
2384: 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, TX8, TX7
2385: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2386: WHERE contains( concat_col, p_contains_str)>0
2387: AND d.search_context_id = p_search_ctx_id
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') )
2394: THEN
2395: OPEN x_cursor FOR
2396: 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, TX8, TX7
2397: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2398: WHERE contains( concat_col, p_contains_str)>0
2399: AND d.search_context_id = p_search_ctx_id
2400: AND( (l_search_merged ='Y' )
2401: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2402: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2411: IF p_search_rel_cpts = 'N' THEN
2412: OPEN x_cursor FOR
2413: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
2414: FROM HZ_STAGED_CONTACT_POINTS stage
2415: WHERE contains( concat_col, p_contains_str)>0
2416: AND( (l_search_merged ='Y' )
2417: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2418: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2419: AND stage.party_id = p_party_id;
2420: ELSE
2421: OPEN x_cursor FOR
2422: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
2423: FROM HZ_STAGED_CONTACT_POINTS stage
2424: WHERE contains( concat_col, p_contains_str)>0
2425: AND( (l_search_merged ='Y' )
2426: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2427: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2428: AND stage.party_id = p_party_id
2428: AND stage.party_id = p_party_id
2429: UNION
2430: 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, TX8, TX7
2431: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2432: WHERE contains( concat_col, p_contains_str)>0
2433: AND r.object_id = p_party_id
2434: AND( (l_search_merged ='Y' )
2435: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2436: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2450: END IF;
2451: OPEN x_cursor FOR
2452: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
2453: FROM HZ_STAGED_CONTACT_POINTS stage
2454: WHERE contains( concat_col, p_contains_str)>0
2455: AND EXISTS (
2456: SELECT 1 FROM HZ_STAGED_PARTIES p
2457: WHERE p.PARTY_ID = stage.PARTY_ID
2458: AND( (l_search_merged ='Y' )
2470: IF p_person_api = 'Y' THEN
2471: OPEN x_cursor FOR
2472: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
2473: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2474: WHERE contains( concat_col, p_contains_str)>0
2475: AND d.search_context_id = p_search_ctx_id
2476: AND d.party_id = stage.party_id
2477: AND( (l_search_merged ='Y' )
2478: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2480: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2481: UNION
2482: 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, TX8, TX7
2483: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2484: WHERE contains( concat_col, p_contains_str)>0
2485: AND d.search_context_id = p_search_ctx_id
2486: AND d.party_id = r.subject_id
2487: AND r.relationship_id = oc.party_relationship_id
2488: AND oc.org_contact_id = stage.org_contact_id
2493: ELSE
2494: OPEN x_cursor FOR
2495: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
2496: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2497: WHERE contains( concat_col, p_contains_str)>0
2498: AND d.search_context_id = p_search_ctx_id
2499: AND d.party_id = stage.party_id
2500: AND( (l_search_merged ='Y' )
2501: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2538: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2539: END IF;
2540: 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, TX8, TX7'||
2541: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2542: ' WHERE contains( concat_col, :cont)>0 '||
2543: ' AND (stage.org_contact_id is null '||
2544: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
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'') ) '||
2548: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2549: ' UNION ' ||
2550: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7'||
2551: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2552: ' WHERE contains( concat_col, :cont)>0 '||
2553: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2554: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2555: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2556: ' AND (stage.org_contact_id in ' ||
2558: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2559: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2560: ' and ('||p_restrict_sql1 || ') ) )' ||
2561: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2562: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2563: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2564: ELSE
2565: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7'||
2566: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2559: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2560: ' and ('||p_restrict_sql1 || ') ) )' ||
2561: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2562: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2563: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2564: ELSE
2565: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7'||
2566: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2567: ' WHERE contains( concat_col, :cont)>0'||
2563: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2564: ELSE
2565: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7'||
2566: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2567: ' WHERE contains( concat_col, :cont)>0'||
2568: ' AND EXISTS ('||
2569: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2570: ' WHERE p.party_id = stage.party_id ' ||
2571: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2573: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2574: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2575: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2576: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2577: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2578: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2579: ,p_dup_party_id, p_dup_party_id;
2580: END IF;
2581: ELSE
2580: END IF;
2581: ELSE
2582: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7'||
2583: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2584: ' WHERE contains( concat_col, :cont)>0'||
2585: ' AND EXISTS ('||
2586: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2587: ' WHERE p.party_id = stage.party_id ' ||
2588: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2590: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2591: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2592: ' AND ('||p_restrict_sql||')' ||
2593: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2594: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2595: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2596: ,p_dup_party_id, p_dup_party_id;
2597: END IF;
2598: END IF;
2597: END IF;
2598: END IF;
2599: END IF;
2600: output_long_strings('----------------------------------------------------------');
2601: output_long_strings('Contact Points Contains String = '||p_contains_str);
2602: output_long_strings('Restrict Sql = '||p_restrict_sql);
2603: exception
2604: when others then
2605: if (instrb(SQLERRM,'DRG-51030')>0) then
2718: x_num_matches OUT NUMBER
2719: ) IS
2720:
2721: -- Strings to hold the generated Intermedia query strings
2722: l_party_contains_str VARCHAR2(32000);
2723: l_party_site_contains_str VARCHAR2(32000);
2724: l_contact_contains_str VARCHAR2(32000);
2725: l_contact_pt_contains_str VARCHAR2(32000);
2726: l_denorm_str VARCHAR2(32000);
2719: ) IS
2720:
2721: -- Strings to hold the generated Intermedia query strings
2722: l_party_contains_str VARCHAR2(32000);
2723: l_party_site_contains_str VARCHAR2(32000);
2724: l_contact_contains_str VARCHAR2(32000);
2725: l_contact_pt_contains_str VARCHAR2(32000);
2726: l_denorm_str VARCHAR2(32000);
2727: l_ps_denorm_str VARCHAR2(32000);
2720:
2721: -- Strings to hold the generated Intermedia query strings
2722: l_party_contains_str VARCHAR2(32000);
2723: l_party_site_contains_str VARCHAR2(32000);
2724: l_contact_contains_str VARCHAR2(32000);
2725: l_contact_pt_contains_str VARCHAR2(32000);
2726: l_denorm_str VARCHAR2(32000);
2727: l_ps_denorm_str VARCHAR2(32000);
2728: l_ct_denorm_str VARCHAR2(32000);
2721: -- Strings to hold the generated Intermedia query strings
2722: l_party_contains_str VARCHAR2(32000);
2723: l_party_site_contains_str VARCHAR2(32000);
2724: l_contact_contains_str VARCHAR2(32000);
2725: l_contact_pt_contains_str VARCHAR2(32000);
2726: l_denorm_str VARCHAR2(32000);
2727: l_ps_denorm_str VARCHAR2(32000);
2728: l_ct_denorm_str VARCHAR2(32000);
2729: l_cpt_denorm_str VARCHAR2(32000);
2825: call_type(2) := 'OR';
2826: END;
2827:
2828: /** Private procedure to acquire and score at party level ***/
2829: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2830: l_party_id_idx NUMBER:=1;
2831: l_ctx_id NUMBER;
2832: l_precalc_score BOOLEAN := FALSE;
2833: l_TX35_new varchar2(4000);
2857: l_search_ctx_id,H_PARTY_ID(I));
2858: l_party_id_idx := H_PARTY_ID.COUNT+1;
2859: ELSE
2860: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2861: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2862: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2863: 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);
2864: END IF;
2865: 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
2865: 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
2866: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2867: 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);
2868: END IF;
2869: IF (l_party_site_contains_str IS NULL AND
2870: l_contact_contains_str IS NULL AND
2871: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2872: 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);
2873: RAISE L_RETURN_IMM_EXC;
2866: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2867: 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);
2868: END IF;
2869: IF (l_party_site_contains_str IS NULL AND
2870: l_contact_contains_str IS NULL AND
2871: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2872: 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);
2873: RAISE L_RETURN_IMM_EXC;
2874: ELSE
2867: 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);
2868: END IF;
2869: IF (l_party_site_contains_str IS NULL AND
2870: l_contact_contains_str IS NULL AND
2871: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2872: 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);
2873: RAISE L_RETURN_IMM_EXC;
2874: ELSE
2875: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2871: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2872: 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);
2873: RAISE L_RETURN_IMM_EXC;
2874: ELSE
2875: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2876: END IF;
2877: ELSE
2878: IF p_index>1 THEN
2879: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2894: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2895: hz_utility_v2pub.debug(p_message=>'========== START LOOPING THROUGH WHAT IS RETURNED BY OPEN_PARTY_CURSOR ================',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2896: END IF;
2897: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2898: 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);
2899: END IF;
2900: LOOP
2901: FETCH l_party_cur INTO
2902: l_party_id , l_TX34, l_TX35, l_TX39, l_TX40, l_TX41, l_TX45;
2918: l_party_id_idx:= l_party_id_idx+1;
2919: END IF;
2920: IF (l_party_id_idx-1)>l_max_thresh THEN
2921: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2922: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2923: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2924: AND l_contact_pt_contains_str IS NULL THEN
2925: EXIT;
2926: END IF;
2920: IF (l_party_id_idx-1)>l_max_thresh THEN
2921: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2922: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2923: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2924: AND l_contact_pt_contains_str IS NULL THEN
2925: EXIT;
2926: END IF;
2927: CLOSE l_party_cur;
2928: IF p_index>1 THEN
2949: ROLLBACK to eval_start;
2950: END;
2951:
2952: /** Private procedure to acquire and score at party site level ***/
2953: 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
2954: l_party_id_idx NUMBER:=1;
2955: l_ctx_id NUMBER;
2956: h_ps_id HZ_PARTY_SEARCH.IDList;
2957: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2961: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2962: 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);
2963: 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);
2964: END IF;
2965: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2966: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2967: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2968: l_party_id := H_SCORES.FIRST;
2969: WHILE l_party_id IS NOT NULL LOOP
2962: 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);
2963: 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);
2964: END IF;
2965: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2966: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2967: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2968: l_party_id := H_SCORES.FIRST;
2969: WHILE l_party_id IS NOT NULL LOOP
2970: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2963: 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);
2964: END IF;
2965: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2966: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2967: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2968: l_party_id := H_SCORES.FIRST;
2969: WHILE l_party_id IS NOT NULL LOOP
2970: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2971: l_party_id:=H_SCORES.NEXT(l_party_id);
2993: l_search_ctx_id,H_PARTY_ID(I));
2994: l_party_id_idx := H_PARTY_ID.COUNT+1;
2995: ELSE
2996: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2997: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2998: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2999: 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);
3000: END IF;
3001: IF p_index>1 THEN
3015: END IF;
3016: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3017: 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);
3018: END IF;
3019: 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);
3020: LOOP
3021: FETCH l_party_site_cur INTO
3022: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX22;
3023: EXIT WHEN l_party_site_cur%NOTFOUND;
3071: END IF;
3072: END;
3073:
3074: /** Private procedure to acquire and score at contact point level ***/
3075: 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
3076: l_party_id_idx NUMBER:=1;
3077: l_ctx_id NUMBER;
3078: h_ct_id HZ_PARTY_SEARCH.IDList;
3079: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3105: l_search_ctx_id,H_PARTY_ID(I));
3106: l_party_id_idx := H_PARTY_ID.COUNT+1;
3107: ELSE
3108: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3109: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
3110: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3111: 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);
3112: END IF;
3113: IF p_index>1 THEN
3127: END IF;
3128: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3129: 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);
3130: END IF;
3131: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
3132: LOOP
3133: FETCH l_contact_cur INTO
3134: l_org_contact_id, l_ct_party_id ;
3135: EXIT WHEN l_contact_cur%NOTFOUND;
3180: END IF;
3181: END;
3182:
3183: /** Private procedure to acquire and score at contact point level ***/
3184: 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
3185: l_party_id_idx NUMBER:=1;
3186: l_ctx_id NUMBER;
3187: h_cpt_id HZ_PARTY_SEARCH.IDList;
3188: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3216: l_search_ctx_id,H_PARTY_ID(I));
3217: l_party_id_idx := H_PARTY_ID.COUNT+1;
3218: ELSE
3219: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3220: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
3221: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3222: 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);
3223: END IF;
3224: IF p_index>1 THEN
3238: END IF;
3239: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3240: 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);
3241: END IF;
3242: 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);
3243: LOOP
3244: FETCH l_contact_pt_cur INTO
3245: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX8, l_TX7;
3246: EXIT WHEN l_contact_pt_cur%NOTFOUND;
3419: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3420: l_threshold := round(( l_entered_max_score / 560) * 448);
3421: other_acq_criteria_exists := TRUE ;
3422: --check if acquisition criteria exists for any other entity
3423: IF l_party_contains_str IS NOT NULL THEN
3424: acq_cnt := 1;
3425: END IF;
3426: IF l_party_site_contains_str IS NOT NULL THEN
3427: acq_cnt := acq_cnt+1;
3422: --check if acquisition criteria exists for any other entity
3423: IF l_party_contains_str IS NOT NULL THEN
3424: acq_cnt := 1;
3425: END IF;
3426: IF l_party_site_contains_str IS NOT NULL THEN
3427: acq_cnt := acq_cnt+1;
3428: END IF;
3429: IF l_contact_contains_str IS NOT NULL THEN
3430: acq_cnt := acq_cnt+1;
3425: END IF;
3426: IF l_party_site_contains_str IS NOT NULL THEN
3427: acq_cnt := acq_cnt+1;
3428: END IF;
3429: IF l_contact_contains_str IS NOT NULL THEN
3430: acq_cnt := acq_cnt+1;
3431: END IF;
3432: IF l_contact_pt_contains_str IS NOT NULL THEN
3433: acq_cnt := acq_cnt+1;
3428: END IF;
3429: IF l_contact_contains_str IS NOT NULL THEN
3430: acq_cnt := acq_cnt+1;
3431: END IF;
3432: IF l_contact_pt_contains_str IS NOT NULL THEN
3433: acq_cnt := acq_cnt+1;
3434: END IF;
3435: IF acq_cnt>1 THEN
3436: other_acq_criteria_exists := TRUE;
3462: FND_MSG_PUB.ADD;
3463: RAISE FND_API.G_EXC_ERROR;
3464: END IF;
3465: END IF;
3466: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3467: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3468: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3469: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3470: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3463: RAISE FND_API.G_EXC_ERROR;
3464: END IF;
3465: END IF;
3466: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3467: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3468: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3469: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3470: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3471: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3464: END IF;
3465: END IF;
3466: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3467: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3468: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3469: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3470: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3471: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3472: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3465: END IF;
3466: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3467: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3468: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3469: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3470: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3471: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3472: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3473: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3466: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3467: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3468: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3469: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3470: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3471: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3472: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3473: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3474: END IF;
3467: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3468: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3469: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3470: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3471: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3472: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3473: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3474: END IF;
3475: END;
3468: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3469: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3470: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3471: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3472: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3473: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3474: END IF;
3475: END;
3476: /************ End of find_parties private procedures **********/
3469: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3470: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3471: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3472: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3473: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3474: END IF;
3475: END;
3476: /************ End of find_parties private procedures **********/
3477:
3541: 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);
3542: END IF;
3543: -- ************************************************************
3544: -- STEP 2. Setup of intermedia query strings for Acquisition query
3545: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3546: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3547: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3548: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3549: 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);
3542: END IF;
3543: -- ************************************************************
3544: -- STEP 2. Setup of intermedia query strings for Acquisition query
3545: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3546: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3547: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3548: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3549: 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);
3550: END IF;
3543: -- ************************************************************
3544: -- STEP 2. Setup of intermedia query strings for Acquisition query
3545: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3546: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3547: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3548: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3549: 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);
3550: END IF;
3551: l_denorm_max_score:=0;
3575: END IF;
3576: ELSE
3577: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3578: END IF;
3579: 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((448/560)*l_entered_max_score));
3580: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3581:
3582: -- Setup Search Context ID
3583: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3585:
3586: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3587: 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);
3588: END IF;
3589: IF l_party_contains_str IS NULL THEN
3590: defpt := 1;
3591: END IF;
3592: IF l_party_site_contains_str IS NULL THEN
3593: defps := 1;
3588: END IF;
3589: IF l_party_contains_str IS NULL THEN
3590: defpt := 1;
3591: END IF;
3592: IF l_party_site_contains_str IS NULL THEN
3593: defps := 1;
3594: END IF;
3595: IF l_contact_contains_str IS NULL THEN
3596: defct := 1;
3591: END IF;
3592: IF l_party_site_contains_str IS NULL THEN
3593: defps := 1;
3594: END IF;
3595: IF l_contact_contains_str IS NULL THEN
3596: defct := 1;
3597: END IF;
3598: IF l_contact_pt_contains_str IS NULL THEN
3599: defcpt := 1;
3594: END IF;
3595: IF l_contact_contains_str IS NULL THEN
3596: defct := 1;
3597: END IF;
3598: IF l_contact_pt_contains_str IS NULL THEN
3599: defcpt := 1;
3600: END IF;
3601:
3602: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3603: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3604: 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);
3605: END IF;
3606: 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);
3607: 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);
3608: 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);
3609: 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);
3610: 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);
3611: 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);
3604: 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);
3605: END IF;
3606: 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);
3607: 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);
3608: 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);
3609: 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);
3610: 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);
3611: 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);
3612: END IF;
3605: END IF;
3606: 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);
3607: 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);
3608: 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);
3609: 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);
3610: 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);
3611: 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);
3612: END IF;
3613: FOR I in 1..5 LOOP
3606: 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);
3607: 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);
3608: 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);
3609: 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);
3610: 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);
3611: 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);
3612: END IF;
3613: FOR I in 1..5 LOOP
3614: do_eval(I);
3688: x_num_matches OUT NUMBER
3689: ) IS
3690:
3691: -- Strings to hold the generated Intermedia query strings
3692: l_party_contains_str VARCHAR2(32000);
3693: l_party_site_contains_str VARCHAR2(32000);
3694: l_contact_contains_str VARCHAR2(32000);
3695: l_contact_pt_contains_str VARCHAR2(32000);
3696: l_denorm_str VARCHAR2(32000);
3689: ) IS
3690:
3691: -- Strings to hold the generated Intermedia query strings
3692: l_party_contains_str VARCHAR2(32000);
3693: l_party_site_contains_str VARCHAR2(32000);
3694: l_contact_contains_str VARCHAR2(32000);
3695: l_contact_pt_contains_str VARCHAR2(32000);
3696: l_denorm_str VARCHAR2(32000);
3697: l_ps_denorm_str VARCHAR2(32000);
3690:
3691: -- Strings to hold the generated Intermedia query strings
3692: l_party_contains_str VARCHAR2(32000);
3693: l_party_site_contains_str VARCHAR2(32000);
3694: l_contact_contains_str VARCHAR2(32000);
3695: l_contact_pt_contains_str VARCHAR2(32000);
3696: l_denorm_str VARCHAR2(32000);
3697: l_ps_denorm_str VARCHAR2(32000);
3698: l_ct_denorm_str VARCHAR2(32000);
3691: -- Strings to hold the generated Intermedia query strings
3692: l_party_contains_str VARCHAR2(32000);
3693: l_party_site_contains_str VARCHAR2(32000);
3694: l_contact_contains_str VARCHAR2(32000);
3695: l_contact_pt_contains_str VARCHAR2(32000);
3696: l_denorm_str VARCHAR2(32000);
3697: l_ps_denorm_str VARCHAR2(32000);
3698: l_ct_denorm_str VARCHAR2(32000);
3699: l_cpt_denorm_str VARCHAR2(32000);
3825: call_type(2) := 'OR';
3826: END;
3827:
3828: /** Private procedure to acquire and score at party level ***/
3829: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3830: l_party_id_idx NUMBER:=1;
3831: l_ctx_id NUMBER;
3832: l_TX35_new varchar2(4000);
3833: BEGIN
3847: l_search_ctx_id,H_PARTY_ID(I));
3848: l_party_id_idx := H_PARTY_ID.COUNT+1;
3849: ELSE
3850: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3851: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3852: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3853: 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);
3854: END IF;
3855: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3855: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3856: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3857: 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);
3858: END IF;
3859: IF (l_party_site_contains_str IS NULL AND
3860: l_contact_contains_str IS NULL AND
3861: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3862: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3863: RAISE L_RETURN_IMM_EXC;
3856: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3857: 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);
3858: END IF;
3859: IF (l_party_site_contains_str IS NULL AND
3860: l_contact_contains_str IS NULL AND
3861: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3862: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3863: RAISE L_RETURN_IMM_EXC;
3864: ELSE
3857: 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);
3858: END IF;
3859: IF (l_party_site_contains_str IS NULL AND
3860: l_contact_contains_str IS NULL AND
3861: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3862: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3863: RAISE L_RETURN_IMM_EXC;
3864: ELSE
3865: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3861: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3862: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3863: RAISE L_RETURN_IMM_EXC;
3864: ELSE
3865: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3866: END IF;
3867: ELSE
3868: IF p_index>1 THEN
3869: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3881: l_ctx_id := NULL;
3882: l_party_id_idx := H_PARTY_ID.COUNT+1;
3883: END IF;
3884: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3885: 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);
3886: END IF;
3887: LOOP
3888: FETCH l_party_cur INTO
3889: l_party_id , l_TX34, l_TX35, l_TX39, l_TX40, l_TX41, l_TX45;
3905: l_party_id_idx:= l_party_id_idx+1;
3906: END IF;
3907: IF (l_party_id_idx-1)>l_max_thresh THEN
3908: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3909: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3910: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3911: AND l_contact_pt_contains_str IS NULL THEN
3912: H_PARTY_ID.DELETE(l_party_id_idx-1);
3913: H_PARTY_ID_LIST.DELETE(l_index);
3907: IF (l_party_id_idx-1)>l_max_thresh THEN
3908: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3909: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3910: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3911: AND l_contact_pt_contains_str IS NULL THEN
3912: H_PARTY_ID.DELETE(l_party_id_idx-1);
3913: H_PARTY_ID_LIST.DELETE(l_index);
3914: H_SCORES.DELETE(l_index);
3915: EXIT;
3936: CLOSE l_party_cur;
3937: ROLLBACK to eval_start;
3938: END;
3939: PROCEDURE open_person_contact_cursor(
3940: p_contains_str VARCHAR2,
3941: p_search_ctx_id NUMBER,
3942: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3943: BEGIN
3944: OPEN x_cursor FOR
3943: BEGIN
3944: OPEN x_cursor FOR
3945: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3946: FROM HZ_STAGED_CONTACTS stage
3947: WHERE contains( concat_col, p_contains_str)>0
3948: AND ORG_CONTACT_ID in (
3949: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3950: org_contact_id
3951: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc
3967: end if;
3968: END;
3969:
3970: /** Private procedure to acquire and score at party site level ***/
3971: 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
3972: l_party_id_idx NUMBER:=1;
3973: l_ctx_id NUMBER;
3974: h_ps_id HZ_PARTY_SEARCH.IDList;
3975: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3995: ELSE
3996: l_party_id_idx := H_PARTY_ID.COUNT+1;
3997: l_ctx_id := NULL;
3998: END IF;
3999: 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);
4000: LOOP
4001: FETCH l_party_site_cur INTO
4002: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX22;
4003: EXIT WHEN l_party_site_cur%NOTFOUND;
4066: END IF;
4067: END;
4068:
4069: /** Private procedure to acquire and score at party site level ***/
4070: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
4071: l_party_id_idx NUMBER:=1;
4072: l_ctx_id NUMBER;
4073: h_ct_id HZ_PARTY_SEARCH.IDList;
4074: h_ct_party_id HZ_PARTY_SEARCH.IDList;
4082: FORALL I in 1..H_PARTY_ID.COUNT
4083: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
4084: l_search_ctx_id,H_PARTY_ID(I));
4085: l_party_id_idx := H_PARTY_ID.COUNT+1;
4086: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
4087: LOOP
4088: FETCH l_contact_cur INTO
4089: l_org_contact_id, l_ct_party_id ;
4090: EXIT WHEN l_contact_cur%NOTFOUND;
4124: END IF;
4125: END;
4126:
4127: /** Private procedure to acquire and score at contact point level ***/
4128: 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
4129: l_party_id_idx NUMBER:=1;
4130: l_ctx_id NUMBER;
4131: h_cpt_id HZ_PARTY_SEARCH.IDList;
4132: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
4169: END IF;
4170: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4171: 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);
4172: END IF;
4173: 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);
4174: LOOP
4175: FETCH l_contact_pt_cur INTO
4176: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX8, l_TX7;
4177: EXIT WHEN l_contact_pt_cur%NOTFOUND;
4372: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
4373: l_threshold := round(( l_entered_max_score / 560) * 448);
4374: other_acq_criteria_exists := TRUE ;
4375: --check if acquisition criteria exists for any other entity
4376: IF l_party_contains_str IS NOT NULL THEN
4377: acq_cnt := 1;
4378: END IF;
4379: IF l_party_site_contains_str IS NOT NULL THEN
4380: acq_cnt := acq_cnt+1;
4375: --check if acquisition criteria exists for any other entity
4376: IF l_party_contains_str IS NOT NULL THEN
4377: acq_cnt := 1;
4378: END IF;
4379: IF l_party_site_contains_str IS NOT NULL THEN
4380: acq_cnt := acq_cnt+1;
4381: END IF;
4382: IF l_contact_contains_str IS NOT NULL THEN
4383: acq_cnt := acq_cnt+1;
4378: END IF;
4379: IF l_party_site_contains_str IS NOT NULL THEN
4380: acq_cnt := acq_cnt+1;
4381: END IF;
4382: IF l_contact_contains_str IS NOT NULL THEN
4383: acq_cnt := acq_cnt+1;
4384: END IF;
4385: IF l_contact_pt_contains_str IS NOT NULL THEN
4386: acq_cnt := acq_cnt+1;
4381: END IF;
4382: IF l_contact_contains_str IS NOT NULL THEN
4383: acq_cnt := acq_cnt+1;
4384: END IF;
4385: IF l_contact_pt_contains_str IS NOT NULL THEN
4386: acq_cnt := acq_cnt+1;
4387: END IF;
4388: IF acq_cnt>1 THEN
4389: other_acq_criteria_exists := TRUE;
4415: FND_MSG_PUB.ADD;
4416: RAISE FND_API.G_EXC_ERROR;
4417: END IF;
4418: END IF;
4419: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4420: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4421: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4422: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4423: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4416: RAISE FND_API.G_EXC_ERROR;
4417: END IF;
4418: END IF;
4419: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4420: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4421: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4422: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4423: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4424: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4417: END IF;
4418: END IF;
4419: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4420: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4421: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4422: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4423: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4424: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4425: END IF;
4418: END IF;
4419: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4420: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4421: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4422: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4423: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4424: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4425: END IF;
4426: END;
4419: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4420: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4421: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4422: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4423: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4424: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4425: END IF;
4426: END;
4427: /************ End of find_persons private procedures **********/
4420: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4421: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4422: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4423: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4424: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4425: END IF;
4426: END;
4427: /************ End of find_persons private procedures **********/
4428:
4455: g_score_until_thresh := false;
4456: END IF;
4457: -- ************************************************************
4458: -- STEP 2. Setup of intermedia query strings for Acquisition query
4459: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4460: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4461: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4462: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4463: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4456: END IF;
4457: -- ************************************************************
4458: -- STEP 2. Setup of intermedia query strings for Acquisition query
4459: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4460: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4461: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4462: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4463: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4464:
4457: -- ************************************************************
4458: -- STEP 2. Setup of intermedia query strings for Acquisition query
4459: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4460: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4461: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4462: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4463: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4464:
4465: -- Setup Search Context ID
4458: -- STEP 2. Setup of intermedia query strings for Acquisition query
4459: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4460: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4461: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4462: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4463: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4464:
4465: -- Setup Search Context ID
4466: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4465: -- Setup Search Context ID
4466: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4467: x_search_ctx_id := l_search_ctx_id;
4468:
4469: IF l_party_contains_str IS NULL THEN
4470: defpt := 1;
4471: END IF;
4472: IF l_party_site_contains_str IS NULL THEN
4473: defps := 1;
4468:
4469: IF l_party_contains_str IS NULL THEN
4470: defpt := 1;
4471: END IF;
4472: IF l_party_site_contains_str IS NULL THEN
4473: defps := 1;
4474: END IF;
4475: IF l_contact_contains_str IS NULL THEN
4476: defct := 1;
4471: END IF;
4472: IF l_party_site_contains_str IS NULL THEN
4473: defps := 1;
4474: END IF;
4475: IF l_contact_contains_str IS NULL THEN
4476: defct := 1;
4477: END IF;
4478: IF l_contact_pt_contains_str IS NULL THEN
4479: defcpt := 1;
4474: END IF;
4475: IF l_contact_contains_str IS NULL THEN
4476: defct := 1;
4477: END IF;
4478: IF l_contact_pt_contains_str IS NULL THEN
4479: defcpt := 1;
4480: END IF;
4481:
4482: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4480: END IF;
4481:
4482: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4483: 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);
4484: 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);
4485: 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);
4486: 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);
4487: 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);
4488: 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);
4481:
4482: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4483: 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);
4484: 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);
4485: 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);
4486: 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);
4487: 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);
4488: 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);
4489: END IF;
4482: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4483: 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);
4484: 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);
4485: 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);
4486: 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);
4487: 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);
4488: 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);
4489: END IF;
4490: FOR I in 1..5 LOOP
4483: 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);
4484: 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);
4485: 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);
4486: 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);
4487: 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);
4488: 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);
4489: END IF;
4490: FOR I in 1..5 LOOP
4491: do_eval(I);
4489: END IF;
4490: FOR I in 1..5 LOOP
4491: do_eval(I);
4492: END LOOP;
4493: IF l_contact_contains_str IS NOT NULL THEN
4494: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4495: END IF;
4496: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4497: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 80 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4490: FOR I in 1..5 LOOP
4491: do_eval(I);
4492: END LOOP;
4493: IF l_contact_contains_str IS NOT NULL THEN
4494: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4495: END IF;
4496: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4497: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 80 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4498: END IF;
5341: x_num_matches OUT NUMBER
5342: ) IS
5343:
5344: -- Strings to hold the generated Intermedia query strings
5345: l_party_contains_str VARCHAR2(32000);
5346: l_match_idx NUMBER;
5347: l_party_site_contains_str VARCHAR2(32000);
5348: l_contact_contains_str VARCHAR2(32000);
5349: l_contact_pt_contains_str VARCHAR2(32000);
5343:
5344: -- Strings to hold the generated Intermedia query strings
5345: l_party_contains_str VARCHAR2(32000);
5346: l_match_idx NUMBER;
5347: l_party_site_contains_str VARCHAR2(32000);
5348: l_contact_contains_str VARCHAR2(32000);
5349: l_contact_pt_contains_str VARCHAR2(32000);
5350: l_tmp VARCHAR2(32000);
5351:
5344: -- Strings to hold the generated Intermedia query strings
5345: l_party_contains_str VARCHAR2(32000);
5346: l_match_idx NUMBER;
5347: l_party_site_contains_str VARCHAR2(32000);
5348: l_contact_contains_str VARCHAR2(32000);
5349: l_contact_pt_contains_str VARCHAR2(32000);
5350: l_tmp VARCHAR2(32000);
5351:
5352: -- Other local variables
5345: l_party_contains_str VARCHAR2(32000);
5346: l_match_idx NUMBER;
5347: l_party_site_contains_str VARCHAR2(32000);
5348: l_contact_contains_str VARCHAR2(32000);
5349: l_contact_pt_contains_str VARCHAR2(32000);
5350: l_tmp VARCHAR2(32000);
5351:
5352: -- Other local variables
5353: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5431: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5432:
5433: -- ************************************************************
5434: -- STEP 2. Setup of intermedia query strings for Acquisition query
5435: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5436: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5437: 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);
5438:
5439: -- Setup Search Context ID
5432:
5433: -- ************************************************************
5434: -- STEP 2. Setup of intermedia query strings for Acquisition query
5435: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5436: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5437: 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);
5438:
5439: -- Setup Search Context ID
5440: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5441: x_search_ctx_id := l_search_ctx_id;
5442:
5443: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5444: 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);
5445: 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);
5446: 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);
5447: 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);
5448: END IF;
5449: IF l_party_site_contains_str IS NULL THEN
5442:
5443: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5444: 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);
5445: 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);
5446: 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);
5447: 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);
5448: END IF;
5449: IF l_party_site_contains_str IS NULL THEN
5450: defps := 1;
5445: 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);
5446: 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);
5447: 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);
5448: END IF;
5449: IF l_party_site_contains_str IS NULL THEN
5450: defps := 1;
5451: END IF;
5452: IF l_contact_pt_contains_str IS NULL THEN
5453: defcpt := 1;
5448: END IF;
5449: IF l_party_site_contains_str IS NULL THEN
5450: defps := 1;
5451: END IF;
5452: IF l_contact_pt_contains_str IS NULL THEN
5453: defcpt := 1;
5454: END IF;
5455:
5456: IF l_party_site_contains_str IS NOT NULL THEN
5452: IF l_contact_pt_contains_str IS NULL THEN
5453: defcpt := 1;
5454: END IF;
5455:
5456: IF l_party_site_contains_str IS NOT NULL THEN
5457: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5458: LOOP
5459: FETCH l_party_site_cur INTO
5460: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX22;
5453: defcpt := 1;
5454: END IF;
5455:
5456: IF l_party_site_contains_str IS NOT NULL THEN
5457: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5458: LOOP
5459: FETCH l_party_site_cur INTO
5460: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX22;
5461: EXIT WHEN l_party_site_cur%NOTFOUND;
5476: END LOOP;
5477: CLOSE l_party_site_cur;
5478: END IF;
5479:
5480: IF l_contact_pt_contains_str IS NOT NULL THEN
5481: SAVEPOINT eval_start;
5482: IF l_match_str = ' AND ' OR (
5483: ((l_cpt_max_score/l_entered_max_score)<(448/560))
5484: ) THEN
5484: ) THEN
5485: FORALL I in 1..l_match_ps_list.COUNT
5486: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5487: values (l_search_ctx_id,l_match_ps_list(I));
5488: 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');
5489: ELSE
5490: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5491: END IF;
5492: LOOP
5486: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5487: values (l_search_ctx_id,l_match_ps_list(I));
5488: 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');
5489: ELSE
5490: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5491: END IF;
5492: LOOP
5493: FETCH l_contact_pt_cur INTO
5494: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX8, l_TX7;
5586: x_num_matches OUT NUMBER
5587: ) IS
5588:
5589: -- Strings to hold the generated Intermedia query strings
5590: l_party_contains_str VARCHAR2(32000);
5591: l_party_site_contains_str VARCHAR2(32000);
5592: l_contact_contains_str VARCHAR2(32000);
5593: l_contact_pt_contains_str VARCHAR2(32000);
5594: l_tmp VARCHAR2(32000);
5587: ) IS
5588:
5589: -- Strings to hold the generated Intermedia query strings
5590: l_party_contains_str VARCHAR2(32000);
5591: l_party_site_contains_str VARCHAR2(32000);
5592: l_contact_contains_str VARCHAR2(32000);
5593: l_contact_pt_contains_str VARCHAR2(32000);
5594: l_tmp VARCHAR2(32000);
5595:
5588:
5589: -- Strings to hold the generated Intermedia query strings
5590: l_party_contains_str VARCHAR2(32000);
5591: l_party_site_contains_str VARCHAR2(32000);
5592: l_contact_contains_str VARCHAR2(32000);
5593: l_contact_pt_contains_str VARCHAR2(32000);
5594: l_tmp VARCHAR2(32000);
5595:
5596: -- Other local variables
5589: -- Strings to hold the generated Intermedia query strings
5590: l_party_contains_str VARCHAR2(32000);
5591: l_party_site_contains_str VARCHAR2(32000);
5592: l_contact_contains_str VARCHAR2(32000);
5593: l_contact_pt_contains_str VARCHAR2(32000);
5594: l_tmp VARCHAR2(32000);
5595:
5596: -- Other local variables
5597: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5663: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5664:
5665: -- ************************************************************
5666: -- STEP 2. Setup of intermedia query strings for Acquisition query
5667: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5668: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5669: 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);
5670:
5671: -- Setup Search Context ID
5664:
5665: -- ************************************************************
5666: -- STEP 2. Setup of intermedia query strings for Acquisition query
5667: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5668: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5669: 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);
5670:
5671: -- Setup Search Context ID
5672: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5673: x_search_ctx_id := l_search_ctx_id;
5674:
5675: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5676: 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);
5677: 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);
5678: 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);
5679: 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);
5680: END IF;
5681: IF l_contact_contains_str IS NULL THEN
5674:
5675: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5676: 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);
5677: 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);
5678: 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);
5679: 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);
5680: END IF;
5681: IF l_contact_contains_str IS NULL THEN
5682: defct := 1;
5677: 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);
5678: 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);
5679: 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);
5680: END IF;
5681: IF l_contact_contains_str IS NULL THEN
5682: defct := 1;
5683: END IF;
5684: IF l_contact_pt_contains_str IS NULL THEN
5685: defcpt := 1;
5680: END IF;
5681: IF l_contact_contains_str IS NULL THEN
5682: defct := 1;
5683: END IF;
5684: IF l_contact_pt_contains_str IS NULL THEN
5685: defcpt := 1;
5686: END IF;
5687:
5688: IF l_contact_contains_str IS NOT NULL THEN
5684: IF l_contact_pt_contains_str IS NULL THEN
5685: defcpt := 1;
5686: END IF;
5687:
5688: IF l_contact_contains_str IS NOT NULL THEN
5689: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5690: LOOP
5691: FETCH l_contact_cur INTO
5692: l_org_contact_id, l_ct_party_id ;
5685: defcpt := 1;
5686: END IF;
5687:
5688: IF l_contact_contains_str IS NOT NULL THEN
5689: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5690: LOOP
5691: FETCH l_contact_cur INTO
5692: l_org_contact_id, l_ct_party_id ;
5693: EXIT WHEN l_contact_cur%NOTFOUND;
5706: END LOOP;
5707: CLOSE l_contact_cur;
5708: END IF;
5709:
5710: IF l_contact_pt_contains_str IS NOT NULL THEN
5711: SAVEPOINT eval_start;
5712: IF l_match_str = ' AND ' OR (
5713: ((l_cpt_max_score/l_entered_max_score)<(448/560))
5714: ) THEN
5714: ) THEN
5715: FORALL I in 1..l_match_contact_list.COUNT
5716: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5717: values (l_search_ctx_id,l_match_contact_list(I));
5718: 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');
5719: ELSE
5720: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5721: END IF;
5722: LOOP
5716: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5717: values (l_search_ctx_id,l_match_contact_list(I));
5718: 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');
5719: ELSE
5720: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5721: END IF;
5722: LOOP
5723: FETCH l_contact_pt_cur INTO
5724: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX8, l_TX7;
5814: ) IS
5815:
5816:
5817: -- Strings to hold the generated Intermedia query strings
5818: l_contact_pt_contains_str VARCHAR2(32000);
5819: l_tmp VARCHAR2(32000);
5820:
5821: -- Other local variables
5822: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5875: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5876:
5877: -- ************************************************************
5878: -- STEP 2. Setup of intermedia query strings for Acquisition query
5879: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5880: 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);
5881:
5882: -- Setup Search Context ID
5883: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5883: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5884: x_search_ctx_id := l_search_ctx_id;
5885: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5886: 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);
5887: 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);
5888: 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);
5889: END IF;
5890:
5891: IF l_contact_pt_contains_str IS NOT NULL THEN
5887: 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);
5888: 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);
5889: END IF;
5890:
5891: IF l_contact_pt_contains_str IS NOT NULL THEN
5892: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5893: cnt := 1;
5894: LOOP
5895: FETCH l_contact_pt_cur INTO
5888: 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);
5889: END IF;
5890:
5891: IF l_contact_pt_contains_str IS NOT NULL THEN
5892: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5893: cnt := 1;
5894: LOOP
5895: FETCH l_contact_pt_cur INTO
5896: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX8, l_TX7;
5959: x_search_ctx_id IN OUT NUMBER
5960: ) IS
5961:
5962: -- Strings to hold the generated Intermedia query strings
5963: l_party_contains_str VARCHAR2(32000);
5964: l_party_site_contains_str VARCHAR2(32000);
5965: l_contact_contains_str VARCHAR2(32000);
5966: l_contact_pt_contains_str VARCHAR2(32000);
5967: l_tmp VARCHAR2(32000);
5960: ) IS
5961:
5962: -- Strings to hold the generated Intermedia query strings
5963: l_party_contains_str VARCHAR2(32000);
5964: l_party_site_contains_str VARCHAR2(32000);
5965: l_contact_contains_str VARCHAR2(32000);
5966: l_contact_pt_contains_str VARCHAR2(32000);
5967: l_tmp VARCHAR2(32000);
5968:
5961:
5962: -- Strings to hold the generated Intermedia query strings
5963: l_party_contains_str VARCHAR2(32000);
5964: l_party_site_contains_str VARCHAR2(32000);
5965: l_contact_contains_str VARCHAR2(32000);
5966: l_contact_pt_contains_str VARCHAR2(32000);
5967: l_tmp VARCHAR2(32000);
5968:
5969: -- Other local variables
5962: -- Strings to hold the generated Intermedia query strings
5963: l_party_contains_str VARCHAR2(32000);
5964: l_party_site_contains_str VARCHAR2(32000);
5965: l_contact_contains_str VARCHAR2(32000);
5966: l_contact_pt_contains_str VARCHAR2(32000);
5967: l_tmp VARCHAR2(32000);
5968:
5969: -- Other local variables
5970: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6058: g_score_until_thresh := false;
6059: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6060: -- ************************************************************
6061: -- STEP 2. Setup of intermedia query strings for Acquisition query
6062: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6063: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6064: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6065: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6066:
6059: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6060: -- ************************************************************
6061: -- STEP 2. Setup of intermedia query strings for Acquisition query
6062: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6063: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6064: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6065: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6066:
6067: -- Setup Search Context ID
6060: -- ************************************************************
6061: -- STEP 2. Setup of intermedia query strings for Acquisition query
6062: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6063: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6064: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6065: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6066:
6067: -- Setup Search Context ID
6068: IF x_search_ctx_id IS NULL THEN
6082: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX34,l_TX35_new,l_TX39,l_TX40,l_TX41,l_TX45,1);
6083: END LOOP;
6084: CLOSE l_party_cur;
6085:
6086: IF l_party_site_contains_str IS NOT NULL THEN
6087: l_max_score := 0;
6088: l_max_id := 0;
6089: l_max_idx := 0;
6090: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6087: l_max_score := 0;
6088: l_max_id := 0;
6089: l_max_idx := 0;
6090: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6091: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
6092: ELSE
6093: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
6094: END IF;
6095: LOOP
6089: l_max_idx := 0;
6090: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6091: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
6092: ELSE
6093: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
6094: END IF;
6095: LOOP
6096: FETCH l_party_site_cur INTO
6097: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX22;
6118: INSERT_PARTY_SITES_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_party_site_list(l_max_idx), g_party_site_stage_list(l_max_idx) ,l_max_TX3,l_max_TX4,l_max_TX9,l_max_TX10,l_max_TX11,l_max_TX12,l_max_TX13,l_max_TX14,l_max_TX15,l_max_TX22,l_max_idx);
6119: END IF;
6120: END IF;
6121:
6122: IF l_contact_contains_str IS NOT NULL THEN
6123: l_max_score := 0;
6124: l_max_id := 0;
6125: l_max_idx := 0;
6126: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
6122: IF l_contact_contains_str IS NOT NULL THEN
6123: l_max_score := 0;
6124: l_max_id := 0;
6125: l_max_idx := 0;
6126: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
6127: LOOP
6128: FETCH l_contact_cur INTO
6129: l_org_contact_id, l_ct_party_id ;
6130: EXIT WHEN l_contact_cur%NOTFOUND;
6140: INSERT_CONTACTS_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_contact_list(l_max_idx), g_contact_stage_list(l_max_idx) ,l_max_idx);
6141: END IF;
6142: END IF;
6143:
6144: IF l_contact_pt_contains_str IS NOT NULL THEN
6145: l_max_score := 0;
6146: l_max_id := 0;
6147: l_max_idx := 0;
6148: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6145: l_max_score := 0;
6146: l_max_id := 0;
6147: l_max_idx := 0;
6148: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6149: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
6150: ELSE
6151: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
6152: END IF;
6153: LOOP
6147: l_max_idx := 0;
6148: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6149: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
6150: ELSE
6151: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
6152: END IF;
6153: LOOP
6154: FETCH l_contact_pt_cur INTO
6155: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX8, l_TX7;