[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:
1324: p_party_max_score NUMBER,
1325: p_denorm_max_score NUMBER,
1326: p_non_denorm_max_score NUMBER,
1327: p_threshold NUMBER) RETURN VARCHAR2 IS
1328: l_party_contains_str VARCHAR2(32000);
1329: l_party_filter VARCHAR2(1) := null;
1330: l_prim_temp VARCHAR2(4000);
1331: l_denorm_str VARCHAR2(4000);
1332: BEGIN
1368:
1369: IF l_denorm_str IS NOT NULL THEN
1370: IF (p_non_denorm_max_score+40)>=p_threshold THEN
1371: l_denorm_str := NULL;
1372: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1373: ELSIF (p_non_denorm_max_score+p_denorm_max_score+40)>=p_threshold THEN
1374: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1375: l_denorm_str := NULL;
1376: END IF;
1370: IF (p_non_denorm_max_score+40)>=p_threshold THEN
1371: l_denorm_str := NULL;
1372: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1373: ELSIF (p_non_denorm_max_score+p_denorm_max_score+40)>=p_threshold THEN
1374: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1375: l_denorm_str := NULL;
1376: END IF;
1377: ELSE
1378: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1374: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1375: l_denorm_str := NULL;
1376: END IF;
1377: ELSE
1378: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1379: END IF;
1380:
1381: -- Setup query string for ALL_ACCOUNT_NUMBERS
1382: l_prim_temp := null;
1385:
1386: IF l_denorm_str IS NOT NULL THEN
1387: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1388: l_denorm_str := NULL;
1389: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1390: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1391: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1392: l_denorm_str := NULL;
1393: END IF;
1387: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1388: l_denorm_str := NULL;
1389: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1390: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1391: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1392: l_denorm_str := NULL;
1393: END IF;
1394: ELSE
1395: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1391: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1392: l_denorm_str := NULL;
1393: END IF;
1394: ELSE
1395: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1396: END IF;
1397:
1398: -- Setup query string for PARTY_NUMBER
1399: l_prim_temp := null;
1402:
1403: IF l_denorm_str IS NOT NULL THEN
1404: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1405: l_denorm_str := NULL;
1406: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1407: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1408: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1409: l_denorm_str := NULL;
1410: END IF;
1404: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1405: l_denorm_str := NULL;
1406: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1407: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1408: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1409: l_denorm_str := NULL;
1410: END IF;
1411: ELSE
1412: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1408: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1409: l_denorm_str := NULL;
1410: END IF;
1411: ELSE
1412: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1413: END IF;
1414:
1415: -- Setup query string for JGZZ_FISCAL_CODE
1416: l_prim_temp := null;
1419:
1420: IF l_denorm_str IS NOT NULL THEN
1421: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1422: l_denorm_str := NULL;
1423: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1424: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1425: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1426: l_denorm_str := NULL;
1427: END IF;
1421: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1422: l_denorm_str := NULL;
1423: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1424: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1425: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1426: l_denorm_str := NULL;
1427: END IF;
1428: ELSE
1429: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1425: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1426: l_denorm_str := NULL;
1427: END IF;
1428: ELSE
1429: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1430: END IF;
1431: IF lengthb(l_party_contains_str) > 4000 THEN
1432: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1433: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1427: END IF;
1428: ELSE
1429: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1430: END IF;
1431: IF lengthb(l_party_contains_str) > 4000 THEN
1432: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1433: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1434: FND_MSG_PUB.ADD;
1435: RAISE FND_API.G_EXC_ERROR;
1433: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1434: FND_MSG_PUB.ADD;
1435: RAISE FND_API.G_EXC_ERROR;
1436: END IF;
1437: IF (p_match_str = ' AND ' OR p_party_max_score
1439: 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);
1440: END IF;
1441: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1435: RAISE FND_API.G_EXC_ERROR;
1436: END IF;
1437: IF (p_match_str = ' AND ' OR p_party_max_score
1439: 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);
1440: END IF;
1441: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1442: 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);
1443: END IF;
1438: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1439: 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);
1440: END IF;
1441: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1442: 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);
1443: END IF;
1444: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1445: 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);
1446: END IF;
1443: END IF;
1444: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1445: 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);
1446: END IF;
1447: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1448: ELSE
1449: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1450: 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);
1451: 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);
1446: END IF;
1447: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1448: ELSE
1449: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1450: 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);
1451: 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);
1452: END IF;
1453: RETURN l_party_contains_str;
1454: END IF;
1447: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1448: ELSE
1449: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1450: 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);
1451: 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);
1452: END IF;
1453: RETURN l_party_contains_str;
1454: END IF;
1455: END;
1449: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1450: 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);
1451: 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);
1452: END IF;
1453: RETURN l_party_contains_str;
1454: END IF;
1455: END;
1456: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1457: l_contains_str VARCHAR2(32000);
1453: RETURN l_party_contains_str;
1454: END IF;
1455: END;
1456: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1457: l_contains_str VARCHAR2(32000);
1458: l_contains_str_temp VARCHAR2(32000);
1459: l_den_contains_str VARCHAR2(32000);
1460: l_den_contains_str_temp VARCHAR2(32000);
1461: l_filter_str VARCHAR2(4000) := null;
1454: END IF;
1455: END;
1456: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1457: l_contains_str VARCHAR2(32000);
1458: l_contains_str_temp VARCHAR2(32000);
1459: l_den_contains_str VARCHAR2(32000);
1460: l_den_contains_str_temp VARCHAR2(32000);
1461: l_filter_str VARCHAR2(4000) := null;
1462: l_prim_temp VARCHAR2(4000) := null;
1455: END;
1456: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1457: l_contains_str VARCHAR2(32000);
1458: l_contains_str_temp VARCHAR2(32000);
1459: l_den_contains_str VARCHAR2(32000);
1460: l_den_contains_str_temp VARCHAR2(32000);
1461: l_filter_str VARCHAR2(4000) := null;
1462: l_prim_temp VARCHAR2(4000) := null;
1463: l_prim_temp_den VARCHAR2(4000) := null;
1456: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1457: l_contains_str VARCHAR2(32000);
1458: l_contains_str_temp VARCHAR2(32000);
1459: l_den_contains_str VARCHAR2(32000);
1460: l_den_contains_str_temp VARCHAR2(32000);
1461: l_filter_str VARCHAR2(4000) := null;
1462: l_prim_temp VARCHAR2(4000) := null;
1463: l_prim_temp_den VARCHAR2(4000) := null;
1464: BEGIN
1466: 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);
1467: END IF;
1468: x_denorm_str := NULL;
1469:
1470: -- Dynamic setup of party site contains str
1471: --
1472: -- For each primary transformation add to intermedia query if it
1473: -- is not null
1474: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1471: --
1472: -- For each primary transformation add to intermedia query if it
1473: -- is not null
1474: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1475: l_contains_str_temp := null;
1476: l_den_contains_str_temp := null;
1477: l_filter_str := null;
1478:
1479: -- Setup query string for ADDRESS
1472: -- For each primary transformation add to intermedia query if it
1473: -- is not null
1474: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1475: l_contains_str_temp := null;
1476: l_den_contains_str_temp := null;
1477: l_filter_str := null;
1478:
1479: -- Setup query string for ADDRESS
1480: l_prim_temp := null;
1480: l_prim_temp := null;
1481: l_prim_temp_den := null;
1482: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRADDRESS
1483: g_party_site_stage_list(I).TX4,'A27',l_prim_temp);
1484: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1485:
1486: -- Setup query string for CITY
1487: l_prim_temp := null;
1488: l_prim_temp_den := null;
1489: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1490: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1491: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1492: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1493: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1494: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1495:
1496: -- Setup query string for POSTAL_CODE
1497: l_prim_temp := null;
1490: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1491: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1492: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1493: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1494: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1495:
1496: -- Setup query string for POSTAL_CODE
1497: l_prim_temp := null;
1498: l_prim_temp_den := null;
1499: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1500: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1501: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1502: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1503: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1504: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1505:
1506: -- Setup query string for PROVINCE
1507: l_prim_temp := null;
1500: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1501: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1502: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1503: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1504: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1505:
1506: -- Setup query string for PROVINCE
1507: l_prim_temp := null;
1508: l_prim_temp_den := null;
1509: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1510: g_party_site_stage_list(I).TX13,'A31',l_prim_temp);
1511: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1512: g_party_site_stage_list(I).TX13,'D_PS',l_prim_temp_den);
1513: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1514: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1515:
1516: -- Setup query string for STATE
1517: l_prim_temp := null;
1510: g_party_site_stage_list(I).TX13,'A31',l_prim_temp);
1511: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1512: g_party_site_stage_list(I).TX13,'D_PS',l_prim_temp_den);
1513: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1514: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1515:
1516: -- Setup query string for STATE
1517: l_prim_temp := null;
1518: l_prim_temp_den := null;
1523: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT + WRSTATE
1524: g_party_site_stage_list(I).TX14,'A32',l_prim_temp);
1525: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT + WRSTATE
1526: g_party_site_stage_list(I).TX14,'D_PS',l_prim_temp_den);
1527: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1528: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1529:
1530: -- Setup query string for COUNTRY
1531: l_prim_temp := null;
1524: g_party_site_stage_list(I).TX14,'A32',l_prim_temp);
1525: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT + WRSTATE
1526: g_party_site_stage_list(I).TX14,'D_PS',l_prim_temp_den);
1527: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1528: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1529:
1530: -- Setup query string for COUNTRY
1531: l_prim_temp := null;
1532: l_prim_temp_den := null;
1533: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1534: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1535: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1536: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1537: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1538: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1539:
1540: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1541: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1534: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1535: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1536: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1537: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1538: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1539:
1540: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1541: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1542: END LOOP;
1536: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1537: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1538: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1539:
1540: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1541: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1542: END LOOP;
1543: -- Add the search criteria to query string
1544: IF lengthb(l_contains_str) > 4000 THEN
1537: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1538: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1539:
1540: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1541: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1542: END LOOP;
1543: -- Add the search criteria to query string
1544: IF lengthb(l_contains_str) > 4000 THEN
1545: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1540: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1541: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1542: END LOOP;
1543: -- Add the search criteria to query string
1544: IF lengthb(l_contains_str) > 4000 THEN
1545: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1546: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1547: FND_MSG_PUB.ADD;
1548: RAISE FND_API.G_EXC_ERROR;
1546: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1547: FND_MSG_PUB.ADD;
1548: RAISE FND_API.G_EXC_ERROR;
1549: END IF;
1550: x_denorm_str := l_den_contains_str;
1551: RETURN l_contains_str;
1552: END;
1553:
1554: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1547: FND_MSG_PUB.ADD;
1548: RAISE FND_API.G_EXC_ERROR;
1549: END IF;
1550: x_denorm_str := l_den_contains_str;
1551: RETURN l_contains_str;
1552: END;
1553:
1554: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1555: l_contains_str VARCHAR2(32000);
1551: RETURN l_contains_str;
1552: END;
1553:
1554: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1555: l_contains_str VARCHAR2(32000);
1556: l_contains_str_temp VARCHAR2(32000);
1557: l_den_contains_str VARCHAR2(32000);
1558: l_den_contains_str_temp VARCHAR2(32000);
1559: l_filter_str VARCHAR2(4000) := null;
1552: END;
1553:
1554: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1555: l_contains_str VARCHAR2(32000);
1556: l_contains_str_temp VARCHAR2(32000);
1557: l_den_contains_str VARCHAR2(32000);
1558: l_den_contains_str_temp VARCHAR2(32000);
1559: l_filter_str VARCHAR2(4000) := null;
1560: l_prim_temp VARCHAR2(4000) := null;
1553:
1554: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1555: l_contains_str VARCHAR2(32000);
1556: l_contains_str_temp VARCHAR2(32000);
1557: l_den_contains_str VARCHAR2(32000);
1558: l_den_contains_str_temp VARCHAR2(32000);
1559: l_filter_str VARCHAR2(4000) := null;
1560: l_prim_temp VARCHAR2(4000) := null;
1561: l_prim_temp_den VARCHAR2(4000) := null;
1554: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1555: l_contains_str VARCHAR2(32000);
1556: l_contains_str_temp VARCHAR2(32000);
1557: l_den_contains_str VARCHAR2(32000);
1558: l_den_contains_str_temp VARCHAR2(32000);
1559: l_filter_str VARCHAR2(4000) := null;
1560: l_prim_temp VARCHAR2(4000) := null;
1561: l_prim_temp_den VARCHAR2(4000) := null;
1562: BEGIN
1564: hz_utility_v2pub.debug(p_message=>'INIT_CONTACTS_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
1565: END IF;
1566: x_denorm_str := NULL;
1567:
1568: -- Dynamic setup of party site contains str
1569: --
1570: -- For each primary transformation add to intermedia query if it
1571: -- is not null
1572: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1569: --
1570: -- For each primary transformation add to intermedia query if it
1571: -- is not null
1572: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1573: l_contains_str_temp := null;
1574: l_den_contains_str_temp := null;
1575: l_filter_str := null;
1576:
1577: -- Setup query string for JOB_TITLE
1570: -- For each primary transformation add to intermedia query if it
1571: -- is not null
1572: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1573: l_contains_str_temp := null;
1574: l_den_contains_str_temp := null;
1575: l_filter_str := null;
1576:
1577: -- Setup query string for JOB_TITLE
1578: l_prim_temp := null;
1580: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1581: g_contact_stage_list(I).TX22,'A43',l_prim_temp);
1582: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1583: g_contact_stage_list(I).TX22,'D_CT',l_prim_temp_den);
1584: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1585: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1586:
1587: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1588: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1581: g_contact_stage_list(I).TX22,'A43',l_prim_temp);
1582: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1583: g_contact_stage_list(I).TX22,'D_CT',l_prim_temp_den);
1584: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1585: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1586:
1587: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1588: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1589: END LOOP;
1583: g_contact_stage_list(I).TX22,'D_CT',l_prim_temp_den);
1584: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1585: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1586:
1587: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1588: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1589: END LOOP;
1590: -- Add the search criteria to query string
1591: IF lengthb(l_contains_str) > 4000 THEN
1584: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1585: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1586:
1587: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1588: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1589: END LOOP;
1590: -- Add the search criteria to query string
1591: IF lengthb(l_contains_str) > 4000 THEN
1592: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1587: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1588: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1589: END LOOP;
1590: -- Add the search criteria to query string
1591: IF lengthb(l_contains_str) > 4000 THEN
1592: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1593: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
1594: FND_MSG_PUB.ADD;
1595: RAISE FND_API.G_EXC_ERROR;
1593: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
1594: FND_MSG_PUB.ADD;
1595: RAISE FND_API.G_EXC_ERROR;
1596: END IF;
1597: x_denorm_str := l_den_contains_str;
1598: RETURN l_contains_str;
1599: END;
1600:
1601: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1594: FND_MSG_PUB.ADD;
1595: RAISE FND_API.G_EXC_ERROR;
1596: END IF;
1597: x_denorm_str := l_den_contains_str;
1598: RETURN l_contains_str;
1599: END;
1600:
1601: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1602: l_contains_str VARCHAR2(32000);
1598: RETURN l_contains_str;
1599: END;
1600:
1601: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1602: l_contains_str VARCHAR2(32000);
1603: l_contains_str_temp VARCHAR2(32000);
1604: l_den_contains_str VARCHAR2(32000);
1605: l_den_contains_str_temp VARCHAR2(32000);
1606: l_filter_str VARCHAR2(4000) := null;
1599: END;
1600:
1601: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1602: l_contains_str VARCHAR2(32000);
1603: l_contains_str_temp VARCHAR2(32000);
1604: l_den_contains_str VARCHAR2(32000);
1605: l_den_contains_str_temp VARCHAR2(32000);
1606: l_filter_str VARCHAR2(4000) := null;
1607: l_prim_temp VARCHAR2(4000) := null;
1600:
1601: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1602: l_contains_str VARCHAR2(32000);
1603: l_contains_str_temp VARCHAR2(32000);
1604: l_den_contains_str VARCHAR2(32000);
1605: l_den_contains_str_temp VARCHAR2(32000);
1606: l_filter_str VARCHAR2(4000) := null;
1607: l_prim_temp VARCHAR2(4000) := null;
1608: l_prim_temp_den VARCHAR2(4000) := null;
1601: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1602: l_contains_str VARCHAR2(32000);
1603: l_contains_str_temp VARCHAR2(32000);
1604: l_den_contains_str VARCHAR2(32000);
1605: l_den_contains_str_temp VARCHAR2(32000);
1606: l_filter_str VARCHAR2(4000) := null;
1607: l_prim_temp VARCHAR2(4000) := null;
1608: l_prim_temp_den VARCHAR2(4000) := null;
1609: TYPE CONTACT_PT_REC_TYPE IS RECORD (
1617: 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);
1618: END IF;
1619: x_denorm_str := NULL;
1620:
1621: -- Dynamic setup of party site contains str
1622: --
1623: -- For each primary transformation add to intermedia query if it
1624: -- is not null
1625: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1622: --
1623: -- For each primary transformation add to intermedia query if it
1624: -- is not null
1625: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1626: l_contains_str_temp := null;
1627: l_den_contains_str_temp := null;
1628: l_filter_str := null;
1629: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1630: if(l_cnt_pt_type.count>0) then
1623: -- For each primary transformation add to intermedia query if it
1624: -- is not null
1625: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1626: l_contains_str_temp := null;
1627: l_den_contains_str_temp := null;
1628: l_filter_str := null;
1629: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1630: if(l_cnt_pt_type.count>0) then
1631: x_modify := 'Y';
1647: l_prim_temp := null;
1648: l_prim_temp_den := null;
1649: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL CTX
1650: g_contact_pt_stage_list(I).TX1,'A44',l_prim_temp);
1651: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1652:
1653: -- Setup query string for EMAIL_ADDRESS
1654: l_prim_temp := null;
1655: l_prim_temp_den := null;
1654: l_prim_temp := null;
1655: l_prim_temp_den := null;
1656: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1657: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1658: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1659:
1660: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1661: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1662: IF N>1 THEN
1656: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1657: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1658: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1659:
1660: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1661: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1662: IF N>1 THEN
1663: distinct_search_cpt_types := N-1;
1664: ELSE
1657: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1658: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1659:
1660: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1661: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1662: IF N>1 THEN
1663: distinct_search_cpt_types := N-1;
1664: ELSE
1665: distinct_search_cpt_types := N;
1668: 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);
1669: END IF;
1670: END LOOP;
1671: -- Add the search criteria to query string
1672: IF lengthb(l_contains_str) > 4000 THEN
1673: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1674: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1675: FND_MSG_PUB.ADD;
1676: RAISE FND_API.G_EXC_ERROR;
1674: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1675: FND_MSG_PUB.ADD;
1676: RAISE FND_API.G_EXC_ERROR;
1677: END IF;
1678: x_denorm_str := l_den_contains_str;
1679: RETURN l_contains_str;
1680: END;
1681:
1682: PROCEDURE open_party_cursor_direct (
1675: FND_MSG_PUB.ADD;
1676: RAISE FND_API.G_EXC_ERROR;
1677: END IF;
1678: x_denorm_str := l_den_contains_str;
1679: RETURN l_contains_str;
1680: END;
1681:
1682: PROCEDURE open_party_cursor_direct (
1683: p_dup_party_id NUMBER,
1683: p_dup_party_id NUMBER,
1684: p_restrict_sql VARCHAR2,
1685: p_match_str VARCHAR2,
1686: p_search_merged VARCHAR2,
1687: p_party_contains_str VARCHAR2,
1688: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1689: l_sqlstr VARCHAR2(4000);
1690: l_search_merged VARCHAR2(1);
1691: BEGIN
1700: END IF;
1701: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1702: 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);
1703: END IF;
1704: 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
1705: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1706: 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);
1707: END IF;
1708: OPEN x_cursor FOR
1741: l_sqlstr := l_sqlstr || ' (TX45 like :TX45||'' %'' ';
1742: l_sqlstr := l_sqlstr || ' )) ';
1743: l_sqlstr := l_sqlstr || ' ) ';
1744: END IF;
1745: 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
1746: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1747: 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);
1748: END IF;
1749: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1743: l_sqlstr := l_sqlstr || ' ) ';
1744: END IF;
1745: 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
1746: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1747: 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);
1748: END IF;
1749: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1750: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX10||'' %'' ';
1751: l_sqlstr := l_sqlstr || ' )) ';
1777: END IF;
1778: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1779: 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);
1780: END IF;
1781: 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
1782: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1783: 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);
1784: END IF;
1785: 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: 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);
1780: END IF;
1781: 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
1782: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1783: 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);
1784: END IF;
1785: 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
1786: ,g_party_stage_rec.TX34
1787: ,g_party_stage_rec.TX34
1816: ,g_party_stage_rec.TX40
1817: ,g_party_stage_rec.TX45
1818: ,g_party_stage_rec.TX45
1819: ;
1820: 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
1821: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1822: 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);
1823: END IF;
1824: 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
1818: ,g_party_stage_rec.TX45
1819: ;
1820: 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
1821: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1822: 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);
1823: END IF;
1824: 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
1825: ,g_party_site_stage_list(1).TX10
1826: ,g_party_site_stage_list(1).TX10
1844: END;
1845: PROCEDURE open_party_cursor(
1846: p_dup_party_id NUMBER,
1847: p_restrict_sql VARCHAR2,
1848: p_contains_str VARCHAR2,
1849: p_search_ctx_id NUMBER,
1850: p_match_str VARCHAR2,
1851: p_search_merged VARCHAR2,
1852: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1863: l_search_merged := 'N';
1864: ELSE
1865: l_search_merged := p_search_merged;
1866: END IF;
1867: IF p_contains_str IS NULL THEN
1868: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1869: 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);
1870: END IF;
1871: OPEN x_cursor FOR
1865: l_search_merged := p_search_merged;
1866: END IF;
1867: IF p_contains_str IS NULL THEN
1868: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1869: 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);
1870: END IF;
1871: OPEN x_cursor FOR
1872: SELECT PARTY_ID , TX34, TX35, TX40, TX39, TX45
1873: FROM HZ_STAGED_PARTIES stage
1882: END IF;
1883: OPEN x_cursor FOR
1884: SELECT PARTY_ID , TX34, TX35, TX40, TX39, TX45
1885: FROM HZ_STAGED_PARTIES stage
1886: WHERE contains( concat_col, p_contains_str)>0
1887: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1888: AND( (l_search_merged ='Y' )
1889: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1890: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )
1895: END IF;
1896: OPEN x_cursor FOR
1897: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX34, TX35, TX40, TX39, TX45
1898: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1899: WHERE contains( concat_col, p_contains_str)>0
1900: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1901: AND d.party_id = stage.party_id
1902: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1903: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1918: END IF;
1919: IF p_search_ctx_id IS NULL THEN
1920: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX34, TX35, TX40, TX39, TX45'||
1921: ' FROM HZ_STAGED_PARTIES stage'||
1922: ' WHERE contains( concat_col, :cont)>0'||
1923: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1924: ' AND ('||p_restrict_sql||')' ||
1925: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1926: IF l_search_merged = 'Y' THEN
1930: ELSE
1931: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1932: END IF;
1933: output_long_strings('----------------------------------------------------------');
1934: output_long_strings('Party Contains String = '||p_contains_str);
1935: output_long_strings('Restrict Sql = '||p_restrict_sql);
1936: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1937: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1938: ,p_dup_party_id, p_dup_party_id;
1932: END IF;
1933: output_long_strings('----------------------------------------------------------');
1934: output_long_strings('Party Contains String = '||p_contains_str);
1935: output_long_strings('Restrict Sql = '||p_restrict_sql);
1936: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1937: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1938: ,p_dup_party_id, p_dup_party_id;
1939: END IF;
1940: END IF;
1963: PROCEDURE open_party_site_cursor(
1964: p_dup_party_id NUMBER,
1965: p_party_id NUMBER,
1966: p_restrict_sql VARCHAR2,
1967: p_contains_str VARCHAR2,
1968: p_search_ctx_id NUMBER,
1969: p_search_merged VARCHAR2,
1970: p_search_rel_sites VARCHAR2,
1971: p_person_api VARCHAR2,
1995: IF p_search_rel_sites = 'N' THEN
1996: OPEN x_cursor FOR
1997: 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
1998: FROM HZ_STAGED_PARTY_SITES stage
1999: WHERE contains( concat_col, p_contains_str)>0
2000: AND( (l_search_merged ='Y' )
2001: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2002: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2003: AND stage.party_id = p_party_id;
2004: ELSE
2005: OPEN x_cursor FOR
2006: 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
2007: FROM HZ_STAGED_PARTY_SITES stage
2008: WHERE contains( concat_col, p_contains_str)>0
2009: AND( (l_search_merged ='Y' )
2010: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2011: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2012: AND stage.party_id = p_party_id
2012: AND stage.party_id = p_party_id
2013: UNION
2014: 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
2015: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
2016: WHERE contains( concat_col, p_contains_str)>0
2017: AND( (l_search_merged ='Y' )
2018: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2019: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2020: AND r.object_id = p_party_id
2034: END IF;
2035: OPEN x_cursor FOR
2036: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
2037: FROM HZ_STAGED_PARTY_SITES stage
2038: WHERE contains( concat_col, p_contains_str)>0
2039: AND EXISTS (
2040: SELECT 1 FROM HZ_STAGED_PARTIES p
2041: WHERE p.PARTY_ID = stage.PARTY_ID
2042: AND( (l_search_merged ='Y' )
2054: IF p_person_api = 'Y' THEN
2055: OPEN x_cursor FOR
2056: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
2057: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
2058: WHERE contains( concat_col, p_contains_str)>0
2059: AND( (l_search_merged ='Y' )
2060: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2061: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2062: AND d.search_context_id = p_search_ctx_id
2064: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2065: UNION
2066: 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
2067: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
2068: WHERE contains( concat_col, p_contains_str)>0
2069: AND( (l_search_merged ='Y' )
2070: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2071: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2072: AND d.search_context_id = p_search_ctx_id
2077: ELSE
2078: OPEN x_cursor FOR
2079: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
2080: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
2081: WHERE contains( concat_col, p_contains_str)>0
2082: AND( (l_search_merged ='Y' )
2083: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2084: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2085: AND d.search_context_id = p_search_ctx_id
2122: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2123: END IF;
2124: 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'||
2125: ' FROM HZ_STAGED_PARTY_SITES stage'||
2126: ' WHERE contains( concat_col, :cont)>0'||
2127: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2128: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2129: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2130: ' AND (ORG_CONTACT_ID IS NULL ' ||
2132: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2133: ' UNION ' ||
2134: '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'||
2135: ' FROM HZ_STAGED_PARTY_SITES stage'||
2136: ' WHERE contains( concat_col, :cont)>0'||
2137: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2138: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2139: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2140: ' AND ORG_CONTACT_ID IN ' ||
2142: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2143: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2144: ' and ('||p_restrict_sql1|| ') )' ||
2145: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2146: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2147: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2148: ELSE
2149: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
2150: ' FROM HZ_STAGED_PARTY_SITES stage'||
2143: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2144: ' and ('||p_restrict_sql1|| ') )' ||
2145: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2146: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2147: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2148: ELSE
2149: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
2150: ' FROM HZ_STAGED_PARTY_SITES stage'||
2151: ' WHERE contains( concat_col, :cont)>0'||
2147: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2148: ELSE
2149: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
2150: ' FROM HZ_STAGED_PARTY_SITES stage'||
2151: ' WHERE contains( concat_col, :cont)>0'||
2152: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2153: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2154: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2155: ' AND EXISTS ('||
2157: ' WHERE p.party_id = stage.party_id ' ||
2158: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2159: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2160: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2161: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2162: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2163: ,p_dup_party_id, p_dup_party_id;
2164: END IF;
2165: ELSE
2164: END IF;
2165: ELSE
2166: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
2167: ' FROM HZ_STAGED_PARTY_SITES stage'||
2168: ' WHERE contains( concat_col, :cont)>0'||
2169: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2170: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2171: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2172: ' AND EXISTS ('||
2174: ' WHERE p.party_id = stage.party_id ' ||
2175: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2176: ' AND ('||p_restrict_sql||')' ||
2177: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2178: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2179: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2180: ,p_dup_party_id, p_dup_party_id;
2181: END IF;
2182: END IF;
2180: ,p_dup_party_id, p_dup_party_id;
2181: END IF;
2182: END IF;
2183: output_long_strings('----------------------------------------------------------');
2184: output_long_strings('Party Site Contains String = '||p_contains_str);
2185: output_long_strings('Restrict Sql = '||p_restrict_sql);
2186: exception
2187: when others then
2188: if (instrb(SQLERRM,'DRG-51030')>0) then
2208: PROCEDURE open_contact_cursor(
2209: p_dup_party_id NUMBER,
2210: p_party_id NUMBER,
2211: p_restrict_sql VARCHAR2,
2212: p_contains_str VARCHAR2,
2213: p_search_ctx_id NUMBER,
2214: p_search_merged VARCHAR2,
2215: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
2216: l_sqlstr VARCHAR2(4000);
2236: END IF;
2237: OPEN x_cursor FOR
2238: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID, TX22
2239: FROM HZ_STAGED_CONTACTS stage
2240: WHERE contains( concat_col, p_contains_str)>0
2241: AND EXISTS (
2242: SELECT 1 FROM HZ_STAGED_PARTIES p
2243: WHERE p.PARTY_ID = stage.PARTY_ID
2244: AND( (l_search_merged ='Y' )
2259: END IF;
2260: OPEN x_cursor FOR
2261: SELECT ORG_CONTACT_ID, PARTY_ID, TX22
2262: FROM HZ_STAGED_CONTACTS stage
2263: WHERE contains( concat_col, p_contains_str)>0
2264: AND EXISTS (
2265: SELECT 1 FROM HZ_STAGED_PARTIES p
2266: WHERE p.PARTY_ID = stage.PARTY_ID
2267: AND( (l_search_merged ='Y' )
2278: END IF;
2279: OPEN x_cursor FOR
2280: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID, TX22
2281: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
2282: WHERE contains( concat_col, p_contains_str)>0
2283: AND d.search_context_id = p_search_ctx_id
2284: AND d.party_id = stage.party_id
2285: AND( (l_search_merged ='Y' )
2286: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2312: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
2313: END IF;
2314: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID , TX22'||
2315: ' FROM HZ_STAGED_CONTACTS stage'||
2316: ' WHERE contains( concat_col, :cont)>0'||
2317: ' AND EXISTS ('||
2318: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2319: ' WHERE p.party_id = stage.party_id ' ||
2320: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2322: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2323: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2324: ' AND ('||p_restrict_sql||')' ||
2325: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2326: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2327: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2328: ,p_dup_party_id, p_dup_party_id;
2329: END IF;
2330: output_long_strings('----------------------------------------------------------');
2327: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2328: ,p_dup_party_id, p_dup_party_id;
2329: END IF;
2330: output_long_strings('----------------------------------------------------------');
2331: output_long_strings('Contacts Contains String = '||p_contains_str);
2332: output_long_strings('Restrict Sql = '||p_restrict_sql);
2333: exception
2334: when others then
2335: if (instrb(SQLERRM,'DRG-51030')>0) then
2355: PROCEDURE open_contact_pt_cursor(
2356: p_dup_party_id NUMBER,
2357: p_party_id NUMBER,
2358: p_restrict_sql VARCHAR2,
2359: p_contains_str VARCHAR2,
2360: p_search_ctx_id NUMBER,
2361: p_search_merged VARCHAR2,
2362: p_search_rel_cpts VARCHAR2,
2363: p_person_api VARCHAR2,
2385: THEN
2386: OPEN x_cursor FOR
2387: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2388: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2389: WHERE contains( concat_col, p_contains_str)>0
2390: AND d.search_context_id = p_search_ctx_id
2391: AND( (l_search_merged ='Y' )
2392: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2393: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2397: THEN
2398: OPEN x_cursor FOR
2399: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N3) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2400: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2401: WHERE contains( concat_col, p_contains_str)>0
2402: AND d.search_context_id = p_search_ctx_id
2403: AND( (l_search_merged ='Y' )
2404: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2405: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2414: IF p_search_rel_cpts = 'N' THEN
2415: OPEN x_cursor FOR
2416: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2417: FROM HZ_STAGED_CONTACT_POINTS stage
2418: WHERE contains( concat_col, p_contains_str)>0
2419: AND( (l_search_merged ='Y' )
2420: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2421: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2422: AND stage.party_id = p_party_id;
2423: ELSE
2424: OPEN x_cursor FOR
2425: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2426: FROM HZ_STAGED_CONTACT_POINTS stage
2427: WHERE contains( concat_col, p_contains_str)>0
2428: AND( (l_search_merged ='Y' )
2429: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2430: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2431: AND stage.party_id = p_party_id
2431: AND stage.party_id = p_party_id
2432: UNION
2433: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ stage.CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , TX1, TX6, TX5
2434: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2435: WHERE contains( concat_col, p_contains_str)>0
2436: AND r.object_id = p_party_id
2437: AND( (l_search_merged ='Y' )
2438: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2439: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2453: END IF;
2454: OPEN x_cursor FOR
2455: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2456: FROM HZ_STAGED_CONTACT_POINTS stage
2457: WHERE contains( concat_col, p_contains_str)>0
2458: AND EXISTS (
2459: SELECT 1 FROM HZ_STAGED_PARTIES p
2460: WHERE p.PARTY_ID = stage.PARTY_ID
2461: AND( (l_search_merged ='Y' )
2473: IF p_person_api = 'Y' THEN
2474: OPEN x_cursor FOR
2475: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2476: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2477: WHERE contains( concat_col, p_contains_str)>0
2478: AND d.search_context_id = p_search_ctx_id
2479: AND d.party_id = stage.party_id
2480: AND( (l_search_merged ='Y' )
2481: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2483: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2484: UNION
2485: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, r.subject_id, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , TX1, TX6, TX5
2486: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2487: WHERE contains( concat_col, p_contains_str)>0
2488: AND d.search_context_id = p_search_ctx_id
2489: AND d.party_id = r.subject_id
2490: AND r.relationship_id = oc.party_relationship_id
2491: AND oc.org_contact_id = stage.org_contact_id
2496: ELSE
2497: OPEN x_cursor FOR
2498: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2499: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2500: WHERE contains( concat_col, p_contains_str)>0
2501: AND d.search_context_id = p_search_ctx_id
2502: AND d.party_id = stage.party_id
2503: AND( (l_search_merged ='Y' )
2504: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2541: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2542: END IF;
2543: l_sqlstr := ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2544: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2545: ' WHERE contains( concat_col, :cont)>0 '||
2546: ' AND (stage.org_contact_id is null '||
2547: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2548: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2549: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2551: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2552: ' UNION ' ||
2553: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2554: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2555: ' WHERE contains( concat_col, :cont)>0 '||
2556: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2557: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2558: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2559: ' AND (stage.org_contact_id in ' ||
2561: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2562: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2563: ' and ('||p_restrict_sql1 || ') ) )' ||
2564: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2565: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2566: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2567: ELSE
2568: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2569: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2562: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2563: ' and ('||p_restrict_sql1 || ') ) )' ||
2564: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2565: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2566: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2567: ELSE
2568: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2569: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2570: ' WHERE contains( concat_col, :cont)>0'||
2566: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2567: ELSE
2568: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2569: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2570: ' WHERE contains( concat_col, :cont)>0'||
2571: ' AND EXISTS ('||
2572: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2573: ' WHERE p.party_id = stage.party_id ' ||
2574: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2576: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2577: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2578: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2579: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2580: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2581: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2582: ,p_dup_party_id, p_dup_party_id;
2583: END IF;
2584: ELSE
2583: END IF;
2584: ELSE
2585: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2586: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2587: ' WHERE contains( concat_col, :cont)>0'||
2588: ' AND EXISTS ('||
2589: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2590: ' WHERE p.party_id = stage.party_id ' ||
2591: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2593: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2594: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2595: ' AND ('||p_restrict_sql||')' ||
2596: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2597: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2598: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2599: ,p_dup_party_id, p_dup_party_id;
2600: END IF;
2601: END IF;
2600: END IF;
2601: END IF;
2602: END IF;
2603: output_long_strings('----------------------------------------------------------');
2604: output_long_strings('Contact Points Contains String = '||p_contains_str);
2605: output_long_strings('Restrict Sql = '||p_restrict_sql);
2606: exception
2607: when others then
2608: if (instrb(SQLERRM,'DRG-51030')>0) then
2721: x_num_matches OUT NUMBER
2722: ) IS
2723:
2724: -- Strings to hold the generated Intermedia query strings
2725: l_party_contains_str VARCHAR2(32000);
2726: l_party_site_contains_str VARCHAR2(32000);
2727: l_contact_contains_str VARCHAR2(32000);
2728: l_contact_pt_contains_str VARCHAR2(32000);
2729: l_denorm_str VARCHAR2(32000);
2722: ) IS
2723:
2724: -- Strings to hold the generated Intermedia query strings
2725: l_party_contains_str VARCHAR2(32000);
2726: l_party_site_contains_str VARCHAR2(32000);
2727: l_contact_contains_str VARCHAR2(32000);
2728: l_contact_pt_contains_str VARCHAR2(32000);
2729: l_denorm_str VARCHAR2(32000);
2730: l_ps_denorm_str VARCHAR2(32000);
2723:
2724: -- Strings to hold the generated Intermedia query strings
2725: l_party_contains_str VARCHAR2(32000);
2726: l_party_site_contains_str VARCHAR2(32000);
2727: l_contact_contains_str VARCHAR2(32000);
2728: l_contact_pt_contains_str VARCHAR2(32000);
2729: l_denorm_str VARCHAR2(32000);
2730: l_ps_denorm_str VARCHAR2(32000);
2731: l_ct_denorm_str VARCHAR2(32000);
2724: -- Strings to hold the generated Intermedia query strings
2725: l_party_contains_str VARCHAR2(32000);
2726: l_party_site_contains_str VARCHAR2(32000);
2727: l_contact_contains_str VARCHAR2(32000);
2728: l_contact_pt_contains_str VARCHAR2(32000);
2729: l_denorm_str VARCHAR2(32000);
2730: l_ps_denorm_str VARCHAR2(32000);
2731: l_ct_denorm_str VARCHAR2(32000);
2732: l_cpt_denorm_str VARCHAR2(32000);
2827: call_type(2) := 'OR';
2828: END;
2829:
2830: /** Private procedure to acquire and score at party level ***/
2831: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2832: l_party_id_idx NUMBER:=1;
2833: l_ctx_id NUMBER;
2834: l_precalc_score BOOLEAN := FALSE;
2835: BEGIN
2858: l_search_ctx_id,H_PARTY_ID(I));
2859: l_party_id_idx := H_PARTY_ID.COUNT+1;
2860: ELSE
2861: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2862: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2863: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2864: 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);
2865: END IF;
2866: 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 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
2867: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2868: 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);
2869: END IF;
2870: IF (l_party_site_contains_str IS NULL AND
2871: l_contact_contains_str IS NULL AND
2872: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2873: 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);
2874: RAISE L_RETURN_IMM_EXC;
2867: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2868: 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);
2869: END IF;
2870: IF (l_party_site_contains_str IS NULL AND
2871: l_contact_contains_str IS NULL AND
2872: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2873: 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);
2874: RAISE L_RETURN_IMM_EXC;
2875: ELSE
2868: 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);
2869: END IF;
2870: IF (l_party_site_contains_str IS NULL AND
2871: l_contact_contains_str IS NULL AND
2872: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2873: 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);
2874: RAISE L_RETURN_IMM_EXC;
2875: ELSE
2876: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2872: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2873: 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);
2874: RAISE L_RETURN_IMM_EXC;
2875: ELSE
2876: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2877: END IF;
2878: ELSE
2879: IF p_index>1 THEN
2880: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2895: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2896: 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);
2897: END IF;
2898: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2899: 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);
2900: END IF;
2901: LOOP
2902: FETCH l_party_cur INTO
2903: l_party_id , l_TX34, l_TX35, l_TX40, l_TX39, l_TX45;
2917: l_party_id_idx:= l_party_id_idx+1;
2918: END IF;
2919: IF (l_party_id_idx-1)>l_max_thresh THEN
2920: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2921: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2922: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2923: AND l_contact_pt_contains_str IS NULL THEN
2924: EXIT;
2925: END IF;
2919: IF (l_party_id_idx-1)>l_max_thresh THEN
2920: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2921: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2922: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2923: AND l_contact_pt_contains_str IS NULL THEN
2924: EXIT;
2925: END IF;
2926: CLOSE l_party_cur;
2927: IF p_index>1 THEN
2948: ROLLBACK to eval_start;
2949: END;
2950:
2951: /** Private procedure to acquire and score at party site level ***/
2952: 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
2953: l_party_id_idx NUMBER:=1;
2954: l_ctx_id NUMBER;
2955: h_ps_id HZ_PARTY_SEARCH.IDList;
2956: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2960: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2961: 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);
2962: 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);
2963: END IF;
2964: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2965: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2966: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2967: l_party_id := H_SCORES.FIRST;
2968: WHILE l_party_id IS NOT NULL LOOP
2961: 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);
2962: 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);
2963: END IF;
2964: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2965: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2966: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2967: l_party_id := H_SCORES.FIRST;
2968: WHILE l_party_id IS NOT NULL LOOP
2969: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2962: 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);
2963: END IF;
2964: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2965: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2966: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2967: l_party_id := H_SCORES.FIRST;
2968: WHILE l_party_id IS NOT NULL LOOP
2969: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2970: l_party_id:=H_SCORES.NEXT(l_party_id);
2992: l_search_ctx_id,H_PARTY_ID(I));
2993: l_party_id_idx := H_PARTY_ID.COUNT+1;
2994: ELSE
2995: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2996: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2997: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2998: 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);
2999: END IF;
3000: IF p_index>1 THEN
3014: END IF;
3015: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3016: 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);
3017: END IF;
3018: 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);
3019: LOOP
3020: FETCH l_party_site_cur INTO
3021: 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;
3022: EXIT WHEN l_party_site_cur%NOTFOUND;
3070: END IF;
3071: END;
3072:
3073: /** Private procedure to acquire and score at contact point level ***/
3074: 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
3075: l_party_id_idx NUMBER:=1;
3076: l_ctx_id NUMBER;
3077: h_ct_id HZ_PARTY_SEARCH.IDList;
3078: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3104: l_search_ctx_id,H_PARTY_ID(I));
3105: l_party_id_idx := H_PARTY_ID.COUNT+1;
3106: ELSE
3107: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3108: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
3109: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3110: 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);
3111: END IF;
3112: IF p_index>1 THEN
3126: END IF;
3127: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3128: 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);
3129: END IF;
3130: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
3131: LOOP
3132: FETCH l_contact_cur INTO
3133: l_org_contact_id, l_ct_party_id , l_TX22;
3134: EXIT WHEN l_contact_cur%NOTFOUND;
3179: END IF;
3180: END;
3181:
3182: /** Private procedure to acquire and score at contact point level ***/
3183: 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
3184: l_party_id_idx NUMBER:=1;
3185: l_ctx_id NUMBER;
3186: h_cpt_id HZ_PARTY_SEARCH.IDList;
3187: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3215: l_search_ctx_id,H_PARTY_ID(I));
3216: l_party_id_idx := H_PARTY_ID.COUNT+1;
3217: ELSE
3218: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3219: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
3220: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3221: 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);
3222: END IF;
3223: IF p_index>1 THEN
3237: END IF;
3238: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3239: 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);
3240: END IF;
3241: 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);
3242: LOOP
3243: FETCH l_contact_pt_cur INTO
3244: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
3245: EXIT WHEN l_contact_pt_cur%NOTFOUND;
3418: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3419: l_threshold := round(( l_entered_max_score / 520) * 416);
3420: other_acq_criteria_exists := TRUE ;
3421: --check if acquisition criteria exists for any other entity
3422: IF l_party_contains_str IS NOT NULL THEN
3423: acq_cnt := 1;
3424: END IF;
3425: IF l_party_site_contains_str IS NOT NULL THEN
3426: acq_cnt := acq_cnt+1;
3421: --check if acquisition criteria exists for any other entity
3422: IF l_party_contains_str IS NOT NULL THEN
3423: acq_cnt := 1;
3424: END IF;
3425: IF l_party_site_contains_str IS NOT NULL THEN
3426: acq_cnt := acq_cnt+1;
3427: END IF;
3428: IF l_contact_contains_str IS NOT NULL THEN
3429: acq_cnt := acq_cnt+1;
3424: END IF;
3425: IF l_party_site_contains_str IS NOT NULL THEN
3426: acq_cnt := acq_cnt+1;
3427: END IF;
3428: IF l_contact_contains_str IS NOT NULL THEN
3429: acq_cnt := acq_cnt+1;
3430: END IF;
3431: IF l_contact_pt_contains_str IS NOT NULL THEN
3432: acq_cnt := acq_cnt+1;
3427: END IF;
3428: IF l_contact_contains_str IS NOT NULL THEN
3429: acq_cnt := acq_cnt+1;
3430: END IF;
3431: IF l_contact_pt_contains_str IS NOT NULL THEN
3432: acq_cnt := acq_cnt+1;
3433: END IF;
3434: IF acq_cnt>1 THEN
3435: other_acq_criteria_exists := TRUE;
3461: FND_MSG_PUB.ADD;
3462: RAISE FND_API.G_EXC_ERROR;
3463: END IF;
3464: END IF;
3465: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3466: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3467: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3468: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3469: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3462: RAISE FND_API.G_EXC_ERROR;
3463: END IF;
3464: END IF;
3465: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3466: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3467: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3468: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3469: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3470: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3463: END IF;
3464: END IF;
3465: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3466: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3467: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3468: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3469: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3470: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3471: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3464: END IF;
3465: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3466: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3467: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3468: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3469: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3470: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3471: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3472: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3465: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3466: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3467: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3468: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3469: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3470: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3471: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3472: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3473: END IF;
3466: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3467: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3468: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3469: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3470: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3471: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3472: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3473: END IF;
3474: END;
3467: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3468: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3469: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3470: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3471: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3472: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3473: END IF;
3474: END;
3475: /************ End of find_parties private procedures **********/
3468: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3469: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3470: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3471: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3472: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3473: END IF;
3474: END;
3475: /************ End of find_parties private procedures **********/
3476:
3540: 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);
3541: END IF;
3542: -- ************************************************************
3543: -- STEP 2. Setup of intermedia query strings for Acquisition query
3544: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3545: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3546: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3547: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3548: 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);
3541: END IF;
3542: -- ************************************************************
3543: -- STEP 2. Setup of intermedia query strings for Acquisition query
3544: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3545: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3546: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3547: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3548: 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);
3549: END IF;
3542: -- ************************************************************
3543: -- STEP 2. Setup of intermedia query strings for Acquisition query
3544: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3545: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3546: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3547: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3548: 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);
3549: END IF;
3550: l_denorm_max_score:=0;
3574: END IF;
3575: ELSE
3576: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3577: END IF;
3578: 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((416/520)*l_entered_max_score));
3579: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3580:
3581: -- Setup Search Context ID
3582: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3584:
3585: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3586: 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);
3587: END IF;
3588: IF l_party_contains_str IS NULL THEN
3589: defpt := 1;
3590: END IF;
3591: IF l_party_site_contains_str IS NULL THEN
3592: defps := 1;
3587: END IF;
3588: IF l_party_contains_str IS NULL THEN
3589: defpt := 1;
3590: END IF;
3591: IF l_party_site_contains_str IS NULL THEN
3592: defps := 1;
3593: END IF;
3594: IF l_contact_contains_str IS NULL THEN
3595: defct := 1;
3590: END IF;
3591: IF l_party_site_contains_str IS NULL THEN
3592: defps := 1;
3593: END IF;
3594: IF l_contact_contains_str IS NULL THEN
3595: defct := 1;
3596: END IF;
3597: IF l_contact_pt_contains_str IS NULL THEN
3598: defcpt := 1;
3593: END IF;
3594: IF l_contact_contains_str IS NULL THEN
3595: defct := 1;
3596: END IF;
3597: IF l_contact_pt_contains_str IS NULL THEN
3598: defcpt := 1;
3599: END IF;
3600:
3601: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3602: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3603: 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);
3604: END IF;
3605: 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);
3606: 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);
3607: 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);
3608: 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);
3609: 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);
3610: 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);
3603: 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);
3604: END IF;
3605: 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);
3606: 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);
3607: 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);
3608: 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);
3609: 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);
3610: 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);
3611: END IF;
3604: END IF;
3605: 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);
3606: 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);
3607: 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);
3608: 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);
3609: 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);
3610: 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);
3611: END IF;
3612: FOR I in 1..5 LOOP
3605: 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);
3606: 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);
3607: 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);
3608: 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);
3609: 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);
3610: 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);
3611: END IF;
3612: FOR I in 1..5 LOOP
3613: do_eval(I);
3687: x_num_matches OUT NUMBER
3688: ) IS
3689:
3690: -- Strings to hold the generated Intermedia query strings
3691: l_party_contains_str VARCHAR2(32000);
3692: l_party_site_contains_str VARCHAR2(32000);
3693: l_contact_contains_str VARCHAR2(32000);
3694: l_contact_pt_contains_str VARCHAR2(32000);
3695: l_denorm_str VARCHAR2(32000);
3688: ) IS
3689:
3690: -- Strings to hold the generated Intermedia query strings
3691: l_party_contains_str VARCHAR2(32000);
3692: l_party_site_contains_str VARCHAR2(32000);
3693: l_contact_contains_str VARCHAR2(32000);
3694: l_contact_pt_contains_str VARCHAR2(32000);
3695: l_denorm_str VARCHAR2(32000);
3696: l_ps_denorm_str VARCHAR2(32000);
3689:
3690: -- Strings to hold the generated Intermedia query strings
3691: l_party_contains_str VARCHAR2(32000);
3692: l_party_site_contains_str VARCHAR2(32000);
3693: l_contact_contains_str VARCHAR2(32000);
3694: l_contact_pt_contains_str VARCHAR2(32000);
3695: l_denorm_str VARCHAR2(32000);
3696: l_ps_denorm_str VARCHAR2(32000);
3697: l_ct_denorm_str VARCHAR2(32000);
3690: -- Strings to hold the generated Intermedia query strings
3691: l_party_contains_str VARCHAR2(32000);
3692: l_party_site_contains_str VARCHAR2(32000);
3693: l_contact_contains_str VARCHAR2(32000);
3694: l_contact_pt_contains_str VARCHAR2(32000);
3695: l_denorm_str VARCHAR2(32000);
3696: l_ps_denorm_str VARCHAR2(32000);
3697: l_ct_denorm_str VARCHAR2(32000);
3698: l_cpt_denorm_str VARCHAR2(32000);
3823: call_type(2) := 'OR';
3824: END;
3825:
3826: /** Private procedure to acquire and score at party level ***/
3827: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3828: l_party_id_idx NUMBER:=1;
3829: l_ctx_id NUMBER;
3830: BEGIN
3831: SAVEPOINT eval_start;
3844: l_search_ctx_id,H_PARTY_ID(I));
3845: l_party_id_idx := H_PARTY_ID.COUNT+1;
3846: ELSE
3847: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3848: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3849: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3850: 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);
3851: END IF;
3852: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3852: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3853: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3854: 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);
3855: END IF;
3856: IF (l_party_site_contains_str IS NULL AND
3857: l_contact_contains_str IS NULL AND
3858: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3859: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3860: RAISE L_RETURN_IMM_EXC;
3853: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3854: 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);
3855: END IF;
3856: IF (l_party_site_contains_str IS NULL AND
3857: l_contact_contains_str IS NULL AND
3858: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3859: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3860: RAISE L_RETURN_IMM_EXC;
3861: ELSE
3854: 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);
3855: END IF;
3856: IF (l_party_site_contains_str IS NULL AND
3857: l_contact_contains_str IS NULL AND
3858: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3859: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3860: RAISE L_RETURN_IMM_EXC;
3861: ELSE
3862: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3858: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3859: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3860: RAISE L_RETURN_IMM_EXC;
3861: ELSE
3862: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3863: END IF;
3864: ELSE
3865: IF p_index>1 THEN
3866: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3878: l_ctx_id := NULL;
3879: l_party_id_idx := H_PARTY_ID.COUNT+1;
3880: END IF;
3881: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3882: 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);
3883: END IF;
3884: LOOP
3885: FETCH l_party_cur INTO
3886: l_party_id , l_TX34, l_TX35, l_TX40, l_TX39, l_TX45;
3900: l_party_id_idx:= l_party_id_idx+1;
3901: END IF;
3902: IF (l_party_id_idx-1)>l_max_thresh THEN
3903: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3904: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3905: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3906: AND l_contact_pt_contains_str IS NULL THEN
3907: H_PARTY_ID.DELETE(l_party_id_idx-1);
3908: H_PARTY_ID_LIST.DELETE(l_index);
3902: IF (l_party_id_idx-1)>l_max_thresh THEN
3903: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3904: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3905: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3906: AND l_contact_pt_contains_str IS NULL THEN
3907: H_PARTY_ID.DELETE(l_party_id_idx-1);
3908: H_PARTY_ID_LIST.DELETE(l_index);
3909: H_SCORES.DELETE(l_index);
3910: EXIT;
3931: CLOSE l_party_cur;
3932: ROLLBACK to eval_start;
3933: END;
3934: PROCEDURE open_person_contact_cursor(
3935: p_contains_str VARCHAR2,
3936: p_search_ctx_id NUMBER,
3937: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3938: BEGIN
3939: OPEN x_cursor FOR
3938: BEGIN
3939: OPEN x_cursor FOR
3940: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID, TX22
3941: FROM HZ_STAGED_CONTACTS stage
3942: WHERE contains( concat_col, p_contains_str)>0
3943: AND ORG_CONTACT_ID in (
3944: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3945: org_contact_id
3946: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc
3962: end if;
3963: END;
3964:
3965: /** Private procedure to acquire and score at party site level ***/
3966: 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
3967: l_party_id_idx NUMBER:=1;
3968: l_ctx_id NUMBER;
3969: h_ps_id HZ_PARTY_SEARCH.IDList;
3970: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3990: ELSE
3991: l_party_id_idx := H_PARTY_ID.COUNT+1;
3992: l_ctx_id := NULL;
3993: END IF;
3994: 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);
3995: LOOP
3996: FETCH l_party_site_cur INTO
3997: 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;
3998: EXIT WHEN l_party_site_cur%NOTFOUND;
4061: END IF;
4062: END;
4063:
4064: /** Private procedure to acquire and score at party site level ***/
4065: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
4066: l_party_id_idx NUMBER:=1;
4067: l_ctx_id NUMBER;
4068: h_ct_id HZ_PARTY_SEARCH.IDList;
4069: h_ct_party_id HZ_PARTY_SEARCH.IDList;
4077: FORALL I in 1..H_PARTY_ID.COUNT
4078: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
4079: l_search_ctx_id,H_PARTY_ID(I));
4080: l_party_id_idx := H_PARTY_ID.COUNT+1;
4081: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
4082: LOOP
4083: FETCH l_contact_cur INTO
4084: l_org_contact_id, l_ct_party_id , l_TX22;
4085: EXIT WHEN l_contact_cur%NOTFOUND;
4119: END IF;
4120: END;
4121:
4122: /** Private procedure to acquire and score at contact point level ***/
4123: 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
4124: l_party_id_idx NUMBER:=1;
4125: l_ctx_id NUMBER;
4126: h_cpt_id HZ_PARTY_SEARCH.IDList;
4127: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
4164: END IF;
4165: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4166: 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);
4167: END IF;
4168: 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);
4169: LOOP
4170: FETCH l_contact_pt_cur INTO
4171: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
4172: EXIT WHEN l_contact_pt_cur%NOTFOUND;
4367: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
4368: l_threshold := round(( l_entered_max_score / 520) * 416);
4369: other_acq_criteria_exists := TRUE ;
4370: --check if acquisition criteria exists for any other entity
4371: IF l_party_contains_str IS NOT NULL THEN
4372: acq_cnt := 1;
4373: END IF;
4374: IF l_party_site_contains_str IS NOT NULL THEN
4375: acq_cnt := acq_cnt+1;
4370: --check if acquisition criteria exists for any other entity
4371: IF l_party_contains_str IS NOT NULL THEN
4372: acq_cnt := 1;
4373: END IF;
4374: IF l_party_site_contains_str IS NOT NULL THEN
4375: acq_cnt := acq_cnt+1;
4376: END IF;
4377: IF l_contact_contains_str IS NOT NULL THEN
4378: acq_cnt := acq_cnt+1;
4373: END IF;
4374: IF l_party_site_contains_str IS NOT NULL THEN
4375: acq_cnt := acq_cnt+1;
4376: END IF;
4377: IF l_contact_contains_str IS NOT NULL THEN
4378: acq_cnt := acq_cnt+1;
4379: END IF;
4380: IF l_contact_pt_contains_str IS NOT NULL THEN
4381: acq_cnt := acq_cnt+1;
4376: END IF;
4377: IF l_contact_contains_str IS NOT NULL THEN
4378: acq_cnt := acq_cnt+1;
4379: END IF;
4380: IF l_contact_pt_contains_str IS NOT NULL THEN
4381: acq_cnt := acq_cnt+1;
4382: END IF;
4383: IF acq_cnt>1 THEN
4384: other_acq_criteria_exists := TRUE;
4410: FND_MSG_PUB.ADD;
4411: RAISE FND_API.G_EXC_ERROR;
4412: END IF;
4413: END IF;
4414: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4415: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4416: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4417: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4418: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4411: RAISE FND_API.G_EXC_ERROR;
4412: END IF;
4413: END IF;
4414: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4415: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4416: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4417: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4418: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4419: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4412: END IF;
4413: END IF;
4414: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4415: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4416: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4417: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4418: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4419: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4420: END IF;
4413: END IF;
4414: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4415: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4416: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4417: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4418: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4419: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4420: END IF;
4421: END;
4414: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4415: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4416: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4417: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4418: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4419: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4420: END IF;
4421: END;
4422: /************ End of find_persons private procedures **********/
4415: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4416: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4417: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4418: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4419: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4420: END IF;
4421: END;
4422: /************ End of find_persons private procedures **********/
4423:
4450: g_score_until_thresh := false;
4451: END IF;
4452: -- ************************************************************
4453: -- STEP 2. Setup of intermedia query strings for Acquisition query
4454: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4455: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4456: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4457: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4458: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4451: END IF;
4452: -- ************************************************************
4453: -- STEP 2. Setup of intermedia query strings for Acquisition query
4454: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4455: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4456: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4457: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4458: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4459:
4452: -- ************************************************************
4453: -- STEP 2. Setup of intermedia query strings for Acquisition query
4454: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4455: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4456: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4457: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4458: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4459:
4460: -- Setup Search Context ID
4453: -- STEP 2. Setup of intermedia query strings for Acquisition query
4454: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4455: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4456: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4457: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4458: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4459:
4460: -- Setup Search Context ID
4461: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4460: -- Setup Search Context ID
4461: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4462: x_search_ctx_id := l_search_ctx_id;
4463:
4464: IF l_party_contains_str IS NULL THEN
4465: defpt := 1;
4466: END IF;
4467: IF l_party_site_contains_str IS NULL THEN
4468: defps := 1;
4463:
4464: IF l_party_contains_str IS NULL THEN
4465: defpt := 1;
4466: END IF;
4467: IF l_party_site_contains_str IS NULL THEN
4468: defps := 1;
4469: END IF;
4470: IF l_contact_contains_str IS NULL THEN
4471: defct := 1;
4466: END IF;
4467: IF l_party_site_contains_str IS NULL THEN
4468: defps := 1;
4469: END IF;
4470: IF l_contact_contains_str IS NULL THEN
4471: defct := 1;
4472: END IF;
4473: IF l_contact_pt_contains_str IS NULL THEN
4474: defcpt := 1;
4469: END IF;
4470: IF l_contact_contains_str IS NULL THEN
4471: defct := 1;
4472: END IF;
4473: IF l_contact_pt_contains_str IS NULL THEN
4474: defcpt := 1;
4475: END IF;
4476:
4477: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4475: END IF;
4476:
4477: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4478: 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);
4479: 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);
4480: 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);
4481: 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);
4482: 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);
4483: 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);
4476:
4477: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4478: 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);
4479: 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);
4480: 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);
4481: 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);
4482: 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);
4483: 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);
4484: END IF;
4477: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4478: 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);
4479: 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);
4480: 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);
4481: 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);
4482: 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);
4483: 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);
4484: END IF;
4485: FOR I in 1..5 LOOP
4478: 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);
4479: 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);
4480: 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);
4481: 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);
4482: 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);
4483: 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);
4484: END IF;
4485: FOR I in 1..5 LOOP
4486: do_eval(I);
4484: END IF;
4485: FOR I in 1..5 LOOP
4486: do_eval(I);
4487: END LOOP;
4488: IF l_contact_contains_str IS NOT NULL THEN
4489: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4490: END IF;
4491: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4492: 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);
4485: FOR I in 1..5 LOOP
4486: do_eval(I);
4487: END LOOP;
4488: IF l_contact_contains_str IS NOT NULL THEN
4489: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4490: END IF;
4491: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4492: 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);
4493: END IF;
5336: x_num_matches OUT NUMBER
5337: ) IS
5338:
5339: -- Strings to hold the generated Intermedia query strings
5340: l_party_contains_str VARCHAR2(32000);
5341: l_match_idx NUMBER;
5342: l_party_site_contains_str VARCHAR2(32000);
5343: l_contact_contains_str VARCHAR2(32000);
5344: l_contact_pt_contains_str VARCHAR2(32000);
5338:
5339: -- Strings to hold the generated Intermedia query strings
5340: l_party_contains_str VARCHAR2(32000);
5341: l_match_idx NUMBER;
5342: l_party_site_contains_str VARCHAR2(32000);
5343: l_contact_contains_str VARCHAR2(32000);
5344: l_contact_pt_contains_str VARCHAR2(32000);
5345: l_tmp VARCHAR2(32000);
5346:
5339: -- Strings to hold the generated Intermedia query strings
5340: l_party_contains_str VARCHAR2(32000);
5341: l_match_idx NUMBER;
5342: l_party_site_contains_str VARCHAR2(32000);
5343: l_contact_contains_str VARCHAR2(32000);
5344: l_contact_pt_contains_str VARCHAR2(32000);
5345: l_tmp VARCHAR2(32000);
5346:
5347: -- Other local variables
5340: l_party_contains_str VARCHAR2(32000);
5341: l_match_idx NUMBER;
5342: l_party_site_contains_str VARCHAR2(32000);
5343: l_contact_contains_str VARCHAR2(32000);
5344: l_contact_pt_contains_str VARCHAR2(32000);
5345: l_tmp VARCHAR2(32000);
5346:
5347: -- Other local variables
5348: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5426: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5427:
5428: -- ************************************************************
5429: -- STEP 2. Setup of intermedia query strings for Acquisition query
5430: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5431: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5432: 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);
5433:
5434: -- Setup Search Context ID
5427:
5428: -- ************************************************************
5429: -- STEP 2. Setup of intermedia query strings for Acquisition query
5430: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5431: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5432: 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);
5433:
5434: -- Setup Search Context ID
5435: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5436: x_search_ctx_id := l_search_ctx_id;
5437:
5438: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5439: 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);
5440: 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);
5441: 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);
5442: 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);
5443: END IF;
5444: IF l_party_site_contains_str IS NULL THEN
5437:
5438: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5439: 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);
5440: 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);
5441: 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);
5442: 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);
5443: END IF;
5444: IF l_party_site_contains_str IS NULL THEN
5445: defps := 1;
5440: 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);
5441: 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);
5442: 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);
5443: END IF;
5444: IF l_party_site_contains_str IS NULL THEN
5445: defps := 1;
5446: END IF;
5447: IF l_contact_pt_contains_str IS NULL THEN
5448: defcpt := 1;
5443: END IF;
5444: IF l_party_site_contains_str IS NULL THEN
5445: defps := 1;
5446: END IF;
5447: IF l_contact_pt_contains_str IS NULL THEN
5448: defcpt := 1;
5449: END IF;
5450:
5451: IF l_party_site_contains_str IS NOT NULL THEN
5447: IF l_contact_pt_contains_str IS NULL THEN
5448: defcpt := 1;
5449: END IF;
5450:
5451: IF l_party_site_contains_str IS NOT NULL THEN
5452: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5453: LOOP
5454: FETCH l_party_site_cur INTO
5455: 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;
5448: defcpt := 1;
5449: END IF;
5450:
5451: IF l_party_site_contains_str IS NOT NULL THEN
5452: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5453: LOOP
5454: FETCH l_party_site_cur INTO
5455: 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;
5456: EXIT WHEN l_party_site_cur%NOTFOUND;
5471: END LOOP;
5472: CLOSE l_party_site_cur;
5473: END IF;
5474:
5475: IF l_contact_pt_contains_str IS NOT NULL THEN
5476: SAVEPOINT eval_start;
5477: IF l_match_str = ' AND ' OR (
5478: ((l_cpt_max_score/l_entered_max_score)<(416/520))
5479: ) THEN
5479: ) THEN
5480: FORALL I in 1..l_match_ps_list.COUNT
5481: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5482: values (l_search_ctx_id,l_match_ps_list(I));
5483: 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');
5484: ELSE
5485: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5486: END IF;
5487: LOOP
5481: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5482: values (l_search_ctx_id,l_match_ps_list(I));
5483: 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');
5484: ELSE
5485: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5486: END IF;
5487: LOOP
5488: FETCH l_contact_pt_cur INTO
5489: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
5581: x_num_matches OUT NUMBER
5582: ) IS
5583:
5584: -- Strings to hold the generated Intermedia query strings
5585: l_party_contains_str VARCHAR2(32000);
5586: l_party_site_contains_str VARCHAR2(32000);
5587: l_contact_contains_str VARCHAR2(32000);
5588: l_contact_pt_contains_str VARCHAR2(32000);
5589: l_tmp VARCHAR2(32000);
5582: ) IS
5583:
5584: -- Strings to hold the generated Intermedia query strings
5585: l_party_contains_str VARCHAR2(32000);
5586: l_party_site_contains_str VARCHAR2(32000);
5587: l_contact_contains_str VARCHAR2(32000);
5588: l_contact_pt_contains_str VARCHAR2(32000);
5589: l_tmp VARCHAR2(32000);
5590:
5583:
5584: -- Strings to hold the generated Intermedia query strings
5585: l_party_contains_str VARCHAR2(32000);
5586: l_party_site_contains_str VARCHAR2(32000);
5587: l_contact_contains_str VARCHAR2(32000);
5588: l_contact_pt_contains_str VARCHAR2(32000);
5589: l_tmp VARCHAR2(32000);
5590:
5591: -- Other local variables
5584: -- Strings to hold the generated Intermedia query strings
5585: l_party_contains_str VARCHAR2(32000);
5586: l_party_site_contains_str VARCHAR2(32000);
5587: l_contact_contains_str VARCHAR2(32000);
5588: l_contact_pt_contains_str VARCHAR2(32000);
5589: l_tmp VARCHAR2(32000);
5590:
5591: -- Other local variables
5592: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5659: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5660:
5661: -- ************************************************************
5662: -- STEP 2. Setup of intermedia query strings for Acquisition query
5663: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5664: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5665: 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);
5666:
5667: -- Setup Search Context ID
5660:
5661: -- ************************************************************
5662: -- STEP 2. Setup of intermedia query strings for Acquisition query
5663: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5664: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5665: 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);
5666:
5667: -- Setup Search Context ID
5668: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5669: x_search_ctx_id := l_search_ctx_id;
5670:
5671: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5672: 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);
5673: 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);
5674: 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);
5675: 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);
5676: END IF;
5677: IF l_contact_contains_str IS NULL THEN
5670:
5671: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5672: 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);
5673: 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);
5674: 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);
5675: 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);
5676: END IF;
5677: IF l_contact_contains_str IS NULL THEN
5678: defct := 1;
5673: 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);
5674: 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);
5675: 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);
5676: END IF;
5677: IF l_contact_contains_str IS NULL THEN
5678: defct := 1;
5679: END IF;
5680: IF l_contact_pt_contains_str IS NULL THEN
5681: defcpt := 1;
5676: END IF;
5677: IF l_contact_contains_str IS NULL THEN
5678: defct := 1;
5679: END IF;
5680: IF l_contact_pt_contains_str IS NULL THEN
5681: defcpt := 1;
5682: END IF;
5683:
5684: IF l_contact_contains_str IS NOT NULL THEN
5680: IF l_contact_pt_contains_str IS NULL THEN
5681: defcpt := 1;
5682: END IF;
5683:
5684: IF l_contact_contains_str IS NOT NULL THEN
5685: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5686: LOOP
5687: FETCH l_contact_cur INTO
5688: l_org_contact_id, l_ct_party_id , l_TX22;
5681: defcpt := 1;
5682: END IF;
5683:
5684: IF l_contact_contains_str IS NOT NULL THEN
5685: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5686: LOOP
5687: FETCH l_contact_cur INTO
5688: l_org_contact_id, l_ct_party_id , l_TX22;
5689: EXIT WHEN l_contact_cur%NOTFOUND;
5702: END LOOP;
5703: CLOSE l_contact_cur;
5704: END IF;
5705:
5706: IF l_contact_pt_contains_str IS NOT NULL THEN
5707: SAVEPOINT eval_start;
5708: IF l_match_str = ' AND ' OR (
5709: ((l_cpt_max_score/l_entered_max_score)<(416/520))
5710: ) THEN
5710: ) THEN
5711: FORALL I in 1..l_match_contact_list.COUNT
5712: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5713: values (l_search_ctx_id,l_match_contact_list(I));
5714: 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');
5715: ELSE
5716: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5717: END IF;
5718: LOOP
5712: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5713: values (l_search_ctx_id,l_match_contact_list(I));
5714: 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');
5715: ELSE
5716: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5717: END IF;
5718: LOOP
5719: FETCH l_contact_pt_cur INTO
5720: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
5810: ) IS
5811:
5812:
5813: -- Strings to hold the generated Intermedia query strings
5814: l_contact_pt_contains_str VARCHAR2(32000);
5815: l_tmp VARCHAR2(32000);
5816:
5817: -- Other local variables
5818: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5871: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5872:
5873: -- ************************************************************
5874: -- STEP 2. Setup of intermedia query strings for Acquisition query
5875: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5876: 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);
5877:
5878: -- Setup Search Context ID
5879: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5879: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5880: x_search_ctx_id := l_search_ctx_id;
5881: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5882: 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);
5883: 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);
5884: 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);
5885: END IF;
5886:
5887: IF l_contact_pt_contains_str IS NOT NULL THEN
5883: 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);
5884: 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);
5885: END IF;
5886:
5887: IF l_contact_pt_contains_str IS NOT NULL THEN
5888: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5889: cnt := 1;
5890: LOOP
5891: FETCH l_contact_pt_cur INTO
5884: 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);
5885: END IF;
5886:
5887: IF l_contact_pt_contains_str IS NOT NULL THEN
5888: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5889: cnt := 1;
5890: LOOP
5891: FETCH l_contact_pt_cur INTO
5892: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
5955: x_search_ctx_id IN OUT NUMBER
5956: ) IS
5957:
5958: -- Strings to hold the generated Intermedia query strings
5959: l_party_contains_str VARCHAR2(32000);
5960: l_party_site_contains_str VARCHAR2(32000);
5961: l_contact_contains_str VARCHAR2(32000);
5962: l_contact_pt_contains_str VARCHAR2(32000);
5963: l_tmp VARCHAR2(32000);
5956: ) IS
5957:
5958: -- Strings to hold the generated Intermedia query strings
5959: l_party_contains_str VARCHAR2(32000);
5960: l_party_site_contains_str VARCHAR2(32000);
5961: l_contact_contains_str VARCHAR2(32000);
5962: l_contact_pt_contains_str VARCHAR2(32000);
5963: l_tmp VARCHAR2(32000);
5964:
5957:
5958: -- Strings to hold the generated Intermedia query strings
5959: l_party_contains_str VARCHAR2(32000);
5960: l_party_site_contains_str VARCHAR2(32000);
5961: l_contact_contains_str VARCHAR2(32000);
5962: l_contact_pt_contains_str VARCHAR2(32000);
5963: l_tmp VARCHAR2(32000);
5964:
5965: -- Other local variables
5958: -- Strings to hold the generated Intermedia query strings
5959: l_party_contains_str VARCHAR2(32000);
5960: l_party_site_contains_str VARCHAR2(32000);
5961: l_contact_contains_str VARCHAR2(32000);
5962: l_contact_pt_contains_str VARCHAR2(32000);
5963: l_tmp VARCHAR2(32000);
5964:
5965: -- Other local variables
5966: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6051: g_score_until_thresh := false;
6052: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6053: -- ************************************************************
6054: -- STEP 2. Setup of intermedia query strings for Acquisition query
6055: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6056: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6057: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6058: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6059:
6052: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6053: -- ************************************************************
6054: -- STEP 2. Setup of intermedia query strings for Acquisition query
6055: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6056: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6057: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6058: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6059:
6060: -- Setup Search Context ID
6053: -- ************************************************************
6054: -- STEP 2. Setup of intermedia query strings for Acquisition query
6055: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6056: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6057: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6058: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6059:
6060: -- Setup Search Context ID
6061: IF x_search_ctx_id IS NULL THEN
6073: 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,l_TX40,l_TX39,l_TX45,1);
6074: END LOOP;
6075: CLOSE l_party_cur;
6076:
6077: IF l_party_site_contains_str IS NOT NULL THEN
6078: l_max_score := 0;
6079: l_max_id := 0;
6080: l_max_idx := 0;
6081: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6078: l_max_score := 0;
6079: l_max_id := 0;
6080: l_max_idx := 0;
6081: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6082: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
6083: ELSE
6084: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
6085: END IF;
6086: LOOP
6080: l_max_idx := 0;
6081: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6082: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
6083: ELSE
6084: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
6085: END IF;
6086: LOOP
6087: FETCH l_party_site_cur INTO
6088: 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;
6109: 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);
6110: END IF;
6111: END IF;
6112:
6113: IF l_contact_contains_str IS NOT NULL THEN
6114: l_max_score := 0;
6115: l_max_id := 0;
6116: l_max_idx := 0;
6117: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
6113: IF l_contact_contains_str IS NOT NULL THEN
6114: l_max_score := 0;
6115: l_max_id := 0;
6116: l_max_idx := 0;
6117: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
6118: LOOP
6119: FETCH l_contact_cur INTO
6120: l_org_contact_id, l_ct_party_id , l_TX22;
6121: EXIT WHEN l_contact_cur%NOTFOUND;
6132: INSERT_CONTACTS_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_contact_list(l_max_idx), g_contact_stage_list(l_max_idx) ,l_max_TX22,l_max_idx);
6133: END IF;
6134: END IF;
6135:
6136: IF l_contact_pt_contains_str IS NOT NULL THEN
6137: l_max_score := 0;
6138: l_max_id := 0;
6139: l_max_idx := 0;
6140: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6137: l_max_score := 0;
6138: l_max_id := 0;
6139: l_max_idx := 0;
6140: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6141: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
6142: ELSE
6143: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
6144: END IF;
6145: LOOP
6139: l_max_idx := 0;
6140: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6141: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
6142: ELSE
6143: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
6144: END IF;
6145: LOOP
6146: FETCH l_contact_pt_cur INTO
6147: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;