[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 /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ 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)
1920: END IF;
1921: IF p_search_ctx_id IS NULL THEN
1922: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX34, TX35, TX40, TX39, TX45'||
1923: ' FROM HZ_STAGED_PARTIES stage'||
1924: ' WHERE contains( concat_col, :cont)>0'||
1925: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1926: ' AND ('||p_restrict_sql||')' ||
1927: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1928: IF l_search_merged = 'Y' THEN
1932: ELSE
1933: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1934: END IF;
1935: output_long_strings('----------------------------------------------------------');
1936: output_long_strings('Party Contains String = '||p_contains_str);
1937: output_long_strings('Restrict Sql = '||p_restrict_sql);
1938: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1939: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1940: ,p_dup_party_id, p_dup_party_id;
1934: END IF;
1935: output_long_strings('----------------------------------------------------------');
1936: output_long_strings('Party Contains String = '||p_contains_str);
1937: output_long_strings('Restrict Sql = '||p_restrict_sql);
1938: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1939: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1940: ,p_dup_party_id, p_dup_party_id;
1941: END IF;
1942: END IF;
1965: PROCEDURE open_party_site_cursor(
1966: p_dup_party_id NUMBER,
1967: p_party_id NUMBER,
1968: p_restrict_sql VARCHAR2,
1969: p_contains_str VARCHAR2,
1970: p_search_ctx_id NUMBER,
1971: p_search_merged VARCHAR2,
1972: p_search_rel_sites VARCHAR2,
1973: p_person_api VARCHAR2,
1997: IF p_search_rel_sites = 'N' THEN
1998: OPEN x_cursor FOR
1999: 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
2000: FROM HZ_STAGED_PARTY_SITES stage
2001: WHERE contains( concat_col, p_contains_str)>0
2002: AND( (l_search_merged ='Y' )
2003: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2004: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2005: AND stage.party_id = p_party_id;
2006: ELSE
2007: OPEN x_cursor FOR
2008: 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
2009: FROM HZ_STAGED_PARTY_SITES stage
2010: WHERE contains( concat_col, p_contains_str)>0
2011: AND( (l_search_merged ='Y' )
2012: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2013: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2014: AND stage.party_id = p_party_id
2014: AND stage.party_id = p_party_id
2015: UNION
2016: 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
2017: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
2018: WHERE contains( concat_col, p_contains_str)>0
2019: AND( (l_search_merged ='Y' )
2020: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2021: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2022: AND r.object_id = p_party_id
2036: END IF;
2037: OPEN x_cursor FOR
2038: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
2039: FROM HZ_STAGED_PARTY_SITES stage
2040: WHERE contains( concat_col, p_contains_str)>0
2041: AND EXISTS (
2042: SELECT 1 FROM HZ_STAGED_PARTIES p
2043: WHERE p.PARTY_ID = stage.PARTY_ID
2044: AND( (l_search_merged ='Y' )
2056: IF p_person_api = 'Y' THEN
2057: OPEN x_cursor FOR
2058: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
2059: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
2060: WHERE contains( concat_col, p_contains_str)>0
2061: AND( (l_search_merged ='Y' )
2062: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2063: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2064: AND d.search_context_id = p_search_ctx_id
2066: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2067: UNION
2068: 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
2069: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
2070: WHERE contains( concat_col, p_contains_str)>0
2071: AND( (l_search_merged ='Y' )
2072: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2073: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2074: AND d.search_context_id = p_search_ctx_id
2079: ELSE
2080: OPEN x_cursor FOR
2081: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
2082: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
2083: WHERE contains( concat_col, p_contains_str)>0
2084: AND( (l_search_merged ='Y' )
2085: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2086: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2087: AND d.search_context_id = p_search_ctx_id
2124: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2125: END IF;
2126: 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'||
2127: ' FROM HZ_STAGED_PARTY_SITES stage'||
2128: ' WHERE contains( concat_col, :cont)>0'||
2129: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2130: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2131: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2132: ' AND (ORG_CONTACT_ID IS NULL ' ||
2134: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2135: ' UNION ' ||
2136: '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'||
2137: ' FROM HZ_STAGED_PARTY_SITES stage'||
2138: ' WHERE contains( concat_col, :cont)>0'||
2139: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2140: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2141: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2142: ' AND ORG_CONTACT_ID IN ' ||
2144: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2145: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2146: ' and ('||p_restrict_sql1|| ') )' ||
2147: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2148: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2149: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2150: ELSE
2151: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
2152: ' FROM HZ_STAGED_PARTY_SITES stage'||
2145: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2146: ' and ('||p_restrict_sql1|| ') )' ||
2147: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2148: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2149: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2150: ELSE
2151: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
2152: ' FROM HZ_STAGED_PARTY_SITES stage'||
2153: ' WHERE contains( concat_col, :cont)>0'||
2149: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2150: ELSE
2151: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
2152: ' FROM HZ_STAGED_PARTY_SITES stage'||
2153: ' WHERE contains( concat_col, :cont)>0'||
2154: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2155: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2156: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2157: ' AND EXISTS ('||
2159: ' WHERE p.party_id = stage.party_id ' ||
2160: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2161: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2162: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2163: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2164: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2165: ,p_dup_party_id, p_dup_party_id;
2166: END IF;
2167: ELSE
2166: END IF;
2167: ELSE
2168: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
2169: ' FROM HZ_STAGED_PARTY_SITES stage'||
2170: ' WHERE contains( concat_col, :cont)>0'||
2171: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2172: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2173: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2174: ' AND EXISTS ('||
2176: ' WHERE p.party_id = stage.party_id ' ||
2177: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2178: ' AND ('||p_restrict_sql||')' ||
2179: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2180: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2181: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2182: ,p_dup_party_id, p_dup_party_id;
2183: END IF;
2184: END IF;
2182: ,p_dup_party_id, p_dup_party_id;
2183: END IF;
2184: END IF;
2185: output_long_strings('----------------------------------------------------------');
2186: output_long_strings('Party Site Contains String = '||p_contains_str);
2187: output_long_strings('Restrict Sql = '||p_restrict_sql);
2188: exception
2189: when others then
2190: if (instrb(SQLERRM,'DRG-51030')>0) then
2210: PROCEDURE open_contact_cursor(
2211: p_dup_party_id NUMBER,
2212: p_party_id NUMBER,
2213: p_restrict_sql VARCHAR2,
2214: p_contains_str VARCHAR2,
2215: p_search_ctx_id NUMBER,
2216: p_search_merged VARCHAR2,
2217: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
2218: l_sqlstr VARCHAR2(4000);
2238: END IF;
2239: OPEN x_cursor FOR
2240: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID, TX22
2241: FROM HZ_STAGED_CONTACTS stage
2242: WHERE contains( concat_col, p_contains_str)>0
2243: AND EXISTS (
2244: SELECT 1 FROM HZ_STAGED_PARTIES p
2245: WHERE p.PARTY_ID = stage.PARTY_ID
2246: AND( (l_search_merged ='Y' )
2261: END IF;
2262: OPEN x_cursor FOR
2263: SELECT ORG_CONTACT_ID, PARTY_ID, TX22
2264: FROM HZ_STAGED_CONTACTS stage
2265: WHERE contains( concat_col, p_contains_str)>0
2266: AND EXISTS (
2267: SELECT 1 FROM HZ_STAGED_PARTIES p
2268: WHERE p.PARTY_ID = stage.PARTY_ID
2269: AND( (l_search_merged ='Y' )
2280: END IF;
2281: OPEN x_cursor FOR
2282: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID, TX22
2283: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
2284: WHERE contains( concat_col, p_contains_str)>0
2285: AND d.search_context_id = p_search_ctx_id
2286: AND d.party_id = stage.party_id
2287: AND( (l_search_merged ='Y' )
2288: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2314: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
2315: END IF;
2316: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID , TX22'||
2317: ' FROM HZ_STAGED_CONTACTS stage'||
2318: ' WHERE contains( concat_col, :cont)>0'||
2319: ' AND EXISTS ('||
2320: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2321: ' WHERE p.party_id = stage.party_id ' ||
2322: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2324: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2325: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2326: ' AND ('||p_restrict_sql||')' ||
2327: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2328: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2329: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2330: ,p_dup_party_id, p_dup_party_id;
2331: END IF;
2332: output_long_strings('----------------------------------------------------------');
2329: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2330: ,p_dup_party_id, p_dup_party_id;
2331: END IF;
2332: output_long_strings('----------------------------------------------------------');
2333: output_long_strings('Contacts Contains String = '||p_contains_str);
2334: output_long_strings('Restrict Sql = '||p_restrict_sql);
2335: exception
2336: when others then
2337: if (instrb(SQLERRM,'DRG-51030')>0) then
2357: PROCEDURE open_contact_pt_cursor(
2358: p_dup_party_id NUMBER,
2359: p_party_id NUMBER,
2360: p_restrict_sql VARCHAR2,
2361: p_contains_str VARCHAR2,
2362: p_search_ctx_id NUMBER,
2363: p_search_merged VARCHAR2,
2364: p_search_rel_cpts VARCHAR2,
2365: p_person_api VARCHAR2,
2387: THEN
2388: OPEN x_cursor FOR
2389: 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
2390: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2391: WHERE contains( concat_col, p_contains_str)>0
2392: AND d.search_context_id = p_search_ctx_id
2393: AND( (l_search_merged ='Y' )
2394: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2395: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2399: THEN
2400: OPEN x_cursor FOR
2401: 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
2402: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2403: WHERE contains( concat_col, p_contains_str)>0
2404: AND d.search_context_id = p_search_ctx_id
2405: AND( (l_search_merged ='Y' )
2406: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2407: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2416: IF p_search_rel_cpts = 'N' THEN
2417: OPEN x_cursor FOR
2418: 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
2419: FROM HZ_STAGED_CONTACT_POINTS stage
2420: WHERE contains( concat_col, p_contains_str)>0
2421: AND( (l_search_merged ='Y' )
2422: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2423: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2424: AND stage.party_id = p_party_id;
2425: ELSE
2426: OPEN x_cursor FOR
2427: 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
2428: FROM HZ_STAGED_CONTACT_POINTS stage
2429: WHERE contains( concat_col, p_contains_str)>0
2430: AND( (l_search_merged ='Y' )
2431: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2432: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2433: AND stage.party_id = p_party_id
2433: AND stage.party_id = p_party_id
2434: UNION
2435: 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
2436: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2437: WHERE contains( concat_col, p_contains_str)>0
2438: AND r.object_id = p_party_id
2439: AND( (l_search_merged ='Y' )
2440: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2441: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2455: END IF;
2456: OPEN x_cursor FOR
2457: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2458: FROM HZ_STAGED_CONTACT_POINTS stage
2459: WHERE contains( concat_col, p_contains_str)>0
2460: AND EXISTS (
2461: SELECT 1 FROM HZ_STAGED_PARTIES p
2462: WHERE p.PARTY_ID = stage.PARTY_ID
2463: AND( (l_search_merged ='Y' )
2475: IF p_person_api = 'Y' THEN
2476: OPEN x_cursor FOR
2477: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2478: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2479: WHERE contains( concat_col, p_contains_str)>0
2480: AND d.search_context_id = p_search_ctx_id
2481: AND d.party_id = stage.party_id
2482: AND( (l_search_merged ='Y' )
2483: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2485: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2486: UNION
2487: 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
2488: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2489: WHERE contains( concat_col, p_contains_str)>0
2490: AND d.search_context_id = p_search_ctx_id
2491: AND d.party_id = r.subject_id
2492: AND r.relationship_id = oc.party_relationship_id
2493: AND oc.org_contact_id = stage.org_contact_id
2498: ELSE
2499: OPEN x_cursor FOR
2500: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2501: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2502: WHERE contains( concat_col, p_contains_str)>0
2503: AND d.search_context_id = p_search_ctx_id
2504: AND d.party_id = stage.party_id
2505: AND( (l_search_merged ='Y' )
2506: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2543: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2544: END IF;
2545: 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'||
2546: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2547: ' WHERE contains( concat_col, :cont)>0 '||
2548: ' AND (stage.org_contact_id is null '||
2549: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2550: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2551: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2553: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2554: ' UNION ' ||
2555: ' 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'||
2556: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2557: ' WHERE contains( concat_col, :cont)>0 '||
2558: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2559: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2560: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2561: ' AND (stage.org_contact_id in ' ||
2563: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2564: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2565: ' and ('||p_restrict_sql1 || ') ) )' ||
2566: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2567: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2568: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2569: ELSE
2570: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2571: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2564: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2565: ' and ('||p_restrict_sql1 || ') ) )' ||
2566: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2567: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2568: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2569: ELSE
2570: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2571: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2572: ' WHERE contains( concat_col, :cont)>0'||
2568: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2569: ELSE
2570: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2571: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2572: ' WHERE contains( concat_col, :cont)>0'||
2573: ' AND EXISTS ('||
2574: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2575: ' WHERE p.party_id = stage.party_id ' ||
2576: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2578: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2579: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2580: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2581: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2582: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2583: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2584: ,p_dup_party_id, p_dup_party_id;
2585: END IF;
2586: ELSE
2585: END IF;
2586: ELSE
2587: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2588: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2589: ' WHERE contains( concat_col, :cont)>0'||
2590: ' AND EXISTS ('||
2591: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2592: ' WHERE p.party_id = stage.party_id ' ||
2593: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2595: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2596: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2597: ' AND ('||p_restrict_sql||')' ||
2598: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2599: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2600: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2601: ,p_dup_party_id, p_dup_party_id;
2602: END IF;
2603: END IF;
2602: END IF;
2603: END IF;
2604: END IF;
2605: output_long_strings('----------------------------------------------------------');
2606: output_long_strings('Contact Points Contains String = '||p_contains_str);
2607: output_long_strings('Restrict Sql = '||p_restrict_sql);
2608: exception
2609: when others then
2610: if (instrb(SQLERRM,'DRG-51030')>0) then
2723: x_num_matches OUT NUMBER
2724: ) IS
2725:
2726: -- Strings to hold the generated Intermedia query strings
2727: l_party_contains_str VARCHAR2(32000);
2728: l_party_site_contains_str VARCHAR2(32000);
2729: l_contact_contains_str VARCHAR2(32000);
2730: l_contact_pt_contains_str VARCHAR2(32000);
2731: l_denorm_str VARCHAR2(32000);
2724: ) IS
2725:
2726: -- Strings to hold the generated Intermedia query strings
2727: l_party_contains_str VARCHAR2(32000);
2728: l_party_site_contains_str VARCHAR2(32000);
2729: l_contact_contains_str VARCHAR2(32000);
2730: l_contact_pt_contains_str VARCHAR2(32000);
2731: l_denorm_str VARCHAR2(32000);
2732: l_ps_denorm_str VARCHAR2(32000);
2725:
2726: -- Strings to hold the generated Intermedia query strings
2727: l_party_contains_str VARCHAR2(32000);
2728: l_party_site_contains_str VARCHAR2(32000);
2729: l_contact_contains_str VARCHAR2(32000);
2730: l_contact_pt_contains_str VARCHAR2(32000);
2731: l_denorm_str VARCHAR2(32000);
2732: l_ps_denorm_str VARCHAR2(32000);
2733: l_ct_denorm_str VARCHAR2(32000);
2726: -- Strings to hold the generated Intermedia query strings
2727: l_party_contains_str VARCHAR2(32000);
2728: l_party_site_contains_str VARCHAR2(32000);
2729: l_contact_contains_str VARCHAR2(32000);
2730: l_contact_pt_contains_str VARCHAR2(32000);
2731: l_denorm_str VARCHAR2(32000);
2732: l_ps_denorm_str VARCHAR2(32000);
2733: l_ct_denorm_str VARCHAR2(32000);
2734: l_cpt_denorm_str VARCHAR2(32000);
2829: call_type(2) := 'OR';
2830: END;
2831:
2832: /** Private procedure to acquire and score at party level ***/
2833: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2834: l_party_id_idx NUMBER:=1;
2835: l_ctx_id NUMBER;
2836: l_precalc_score BOOLEAN := FALSE;
2837: l_TX35_new varchar2(4000);
2861: l_search_ctx_id,H_PARTY_ID(I));
2862: l_party_id_idx := H_PARTY_ID.COUNT+1;
2863: ELSE
2864: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2865: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2866: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2867: hz_utility_v2pub.debug(p_message=>'In eval 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);
2868: END IF;
2869: 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
2869: 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
2870: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2871: 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);
2872: END IF;
2873: IF (l_party_site_contains_str IS NULL AND
2874: l_contact_contains_str IS NULL AND
2875: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2876: 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);
2877: RAISE L_RETURN_IMM_EXC;
2870: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2871: 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);
2872: END IF;
2873: IF (l_party_site_contains_str IS NULL AND
2874: l_contact_contains_str IS NULL AND
2875: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2876: 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);
2877: RAISE L_RETURN_IMM_EXC;
2878: ELSE
2871: 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);
2872: END IF;
2873: IF (l_party_site_contains_str IS NULL AND
2874: l_contact_contains_str IS NULL AND
2875: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2876: 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);
2877: RAISE L_RETURN_IMM_EXC;
2878: ELSE
2879: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2875: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2876: 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);
2877: RAISE L_RETURN_IMM_EXC;
2878: ELSE
2879: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2880: END IF;
2881: ELSE
2882: IF p_index>1 THEN
2883: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2898: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2899: 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);
2900: END IF;
2901: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2902: 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);
2903: END IF;
2904: LOOP
2905: FETCH l_party_cur INTO
2906: l_party_id , l_TX34, l_TX35, l_TX40, l_TX39, l_TX45;
2922: l_party_id_idx:= l_party_id_idx+1;
2923: END IF;
2924: IF (l_party_id_idx-1)>l_max_thresh THEN
2925: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2926: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2927: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2928: AND l_contact_pt_contains_str IS NULL THEN
2929: EXIT;
2930: END IF;
2924: IF (l_party_id_idx-1)>l_max_thresh THEN
2925: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2926: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2927: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2928: AND l_contact_pt_contains_str IS NULL THEN
2929: EXIT;
2930: END IF;
2931: CLOSE l_party_cur;
2932: IF p_index>1 THEN
2953: ROLLBACK to eval_start;
2954: END;
2955:
2956: /** Private procedure to acquire and score at party site level ***/
2957: 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
2958: l_party_id_idx NUMBER:=1;
2959: l_ctx_id NUMBER;
2960: h_ps_id HZ_PARTY_SEARCH.IDList;
2961: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2965: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2966: 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);
2967: 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);
2968: END IF;
2969: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2970: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2971: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2972: l_party_id := H_SCORES.FIRST;
2973: WHILE l_party_id IS NOT NULL LOOP
2966: 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);
2967: 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);
2968: END IF;
2969: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2970: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2971: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2972: l_party_id := H_SCORES.FIRST;
2973: WHILE l_party_id IS NOT NULL LOOP
2974: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2967: 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);
2968: END IF;
2969: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2970: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2971: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2972: l_party_id := H_SCORES.FIRST;
2973: WHILE l_party_id IS NOT NULL LOOP
2974: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2975: l_party_id:=H_SCORES.NEXT(l_party_id);
2997: l_search_ctx_id,H_PARTY_ID(I));
2998: l_party_id_idx := H_PARTY_ID.COUNT+1;
2999: ELSE
3000: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3001: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
3002: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3003: 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);
3004: END IF;
3005: IF p_index>1 THEN
3019: END IF;
3020: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3021: 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);
3022: END IF;
3023: 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);
3024: LOOP
3025: FETCH l_party_site_cur INTO
3026: 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;
3027: EXIT WHEN l_party_site_cur%NOTFOUND;
3075: END IF;
3076: END;
3077:
3078: /** Private procedure to acquire and score at contact point level ***/
3079: 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
3080: l_party_id_idx NUMBER:=1;
3081: l_ctx_id NUMBER;
3082: h_ct_id HZ_PARTY_SEARCH.IDList;
3083: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3109: l_search_ctx_id,H_PARTY_ID(I));
3110: l_party_id_idx := H_PARTY_ID.COUNT+1;
3111: ELSE
3112: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3113: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
3114: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3115: 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);
3116: END IF;
3117: IF p_index>1 THEN
3131: END IF;
3132: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3133: 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);
3134: END IF;
3135: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
3136: LOOP
3137: FETCH l_contact_cur INTO
3138: l_org_contact_id, l_ct_party_id , l_TX22;
3139: EXIT WHEN l_contact_cur%NOTFOUND;
3184: END IF;
3185: END;
3186:
3187: /** Private procedure to acquire and score at contact point level ***/
3188: 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
3189: l_party_id_idx NUMBER:=1;
3190: l_ctx_id NUMBER;
3191: h_cpt_id HZ_PARTY_SEARCH.IDList;
3192: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3220: l_search_ctx_id,H_PARTY_ID(I));
3221: l_party_id_idx := H_PARTY_ID.COUNT+1;
3222: ELSE
3223: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3224: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
3225: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3226: 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);
3227: END IF;
3228: IF p_index>1 THEN
3242: END IF;
3243: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3244: 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);
3245: END IF;
3246: 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);
3247: LOOP
3248: FETCH l_contact_pt_cur INTO
3249: 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;
3250: EXIT WHEN l_contact_pt_cur%NOTFOUND;
3423: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3424: l_threshold := round(( l_entered_max_score / 520) * 416);
3425: other_acq_criteria_exists := TRUE ;
3426: --check if acquisition criteria exists for any other entity
3427: IF l_party_contains_str IS NOT NULL THEN
3428: acq_cnt := 1;
3429: END IF;
3430: IF l_party_site_contains_str IS NOT NULL THEN
3431: acq_cnt := acq_cnt+1;
3426: --check if acquisition criteria exists for any other entity
3427: IF l_party_contains_str IS NOT NULL THEN
3428: acq_cnt := 1;
3429: END IF;
3430: IF l_party_site_contains_str IS NOT NULL THEN
3431: acq_cnt := acq_cnt+1;
3432: END IF;
3433: IF l_contact_contains_str IS NOT NULL THEN
3434: acq_cnt := acq_cnt+1;
3429: END IF;
3430: IF l_party_site_contains_str IS NOT NULL THEN
3431: acq_cnt := acq_cnt+1;
3432: END IF;
3433: IF l_contact_contains_str IS NOT NULL THEN
3434: acq_cnt := acq_cnt+1;
3435: END IF;
3436: IF l_contact_pt_contains_str IS NOT NULL THEN
3437: acq_cnt := acq_cnt+1;
3432: END IF;
3433: IF l_contact_contains_str IS NOT NULL THEN
3434: acq_cnt := acq_cnt+1;
3435: END IF;
3436: IF l_contact_pt_contains_str IS NOT NULL THEN
3437: acq_cnt := acq_cnt+1;
3438: END IF;
3439: IF acq_cnt>1 THEN
3440: other_acq_criteria_exists := TRUE;
3466: FND_MSG_PUB.ADD;
3467: RAISE FND_API.G_EXC_ERROR;
3468: END IF;
3469: END IF;
3470: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3471: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3472: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3473: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3474: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3467: RAISE FND_API.G_EXC_ERROR;
3468: END IF;
3469: END IF;
3470: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3471: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3472: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3473: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3474: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3475: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3468: END IF;
3469: END IF;
3470: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3471: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3472: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3473: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3474: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3475: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3476: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3469: END IF;
3470: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3471: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3472: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3473: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3474: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3475: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3476: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3477: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3470: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3471: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3472: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3473: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3474: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3475: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3476: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3477: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3478: END IF;
3471: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3472: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3473: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3474: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3475: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3476: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3477: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3478: END IF;
3479: END;
3472: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3473: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3474: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3475: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3476: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3477: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3478: END IF;
3479: END;
3480: /************ End of find_parties private procedures **********/
3473: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3474: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3475: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3476: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3477: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3478: END IF;
3479: END;
3480: /************ End of find_parties private procedures **********/
3481:
3545: 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);
3546: END IF;
3547: -- ************************************************************
3548: -- STEP 2. Setup of intermedia query strings for Acquisition query
3549: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3550: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3551: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3552: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3553: 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);
3546: END IF;
3547: -- ************************************************************
3548: -- STEP 2. Setup of intermedia query strings for Acquisition query
3549: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3550: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3551: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3552: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3553: 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);
3554: END IF;
3547: -- ************************************************************
3548: -- STEP 2. Setup of intermedia query strings for Acquisition query
3549: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3550: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3551: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3552: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3553: 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);
3554: END IF;
3555: l_denorm_max_score:=0;
3579: END IF;
3580: ELSE
3581: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3582: END IF;
3583: 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));
3584: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3585:
3586: -- Setup Search Context ID
3587: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3589:
3590: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3591: 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);
3592: END IF;
3593: IF l_party_contains_str IS NULL THEN
3594: defpt := 1;
3595: END IF;
3596: IF l_party_site_contains_str IS NULL THEN
3597: defps := 1;
3592: END IF;
3593: IF l_party_contains_str IS NULL THEN
3594: defpt := 1;
3595: END IF;
3596: IF l_party_site_contains_str IS NULL THEN
3597: defps := 1;
3598: END IF;
3599: IF l_contact_contains_str IS NULL THEN
3600: defct := 1;
3595: END IF;
3596: IF l_party_site_contains_str IS NULL THEN
3597: defps := 1;
3598: END IF;
3599: IF l_contact_contains_str IS NULL THEN
3600: defct := 1;
3601: END IF;
3602: IF l_contact_pt_contains_str IS NULL THEN
3603: defcpt := 1;
3598: END IF;
3599: IF l_contact_contains_str IS NULL THEN
3600: defct := 1;
3601: END IF;
3602: IF l_contact_pt_contains_str IS NULL THEN
3603: defcpt := 1;
3604: END IF;
3605:
3606: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3607: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3608: 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);
3609: END IF;
3610: 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);
3611: 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);
3612: 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);
3613: 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);
3614: 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);
3615: 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);
3608: 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);
3609: END IF;
3610: 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);
3611: 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);
3612: 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);
3613: 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);
3614: 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);
3615: 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);
3616: END IF;
3609: END IF;
3610: 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);
3611: 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);
3612: 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);
3613: 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);
3614: 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);
3615: 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);
3616: END IF;
3617: FOR I in 1..5 LOOP
3610: 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);
3611: 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);
3612: 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);
3613: 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);
3614: 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);
3615: 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);
3616: END IF;
3617: FOR I in 1..5 LOOP
3618: do_eval(I);
3692: x_num_matches OUT NUMBER
3693: ) IS
3694:
3695: -- Strings to hold the generated Intermedia query strings
3696: l_party_contains_str VARCHAR2(32000);
3697: l_party_site_contains_str VARCHAR2(32000);
3698: l_contact_contains_str VARCHAR2(32000);
3699: l_contact_pt_contains_str VARCHAR2(32000);
3700: l_denorm_str VARCHAR2(32000);
3693: ) IS
3694:
3695: -- Strings to hold the generated Intermedia query strings
3696: l_party_contains_str VARCHAR2(32000);
3697: l_party_site_contains_str VARCHAR2(32000);
3698: l_contact_contains_str VARCHAR2(32000);
3699: l_contact_pt_contains_str VARCHAR2(32000);
3700: l_denorm_str VARCHAR2(32000);
3701: l_ps_denorm_str VARCHAR2(32000);
3694:
3695: -- Strings to hold the generated Intermedia query strings
3696: l_party_contains_str VARCHAR2(32000);
3697: l_party_site_contains_str VARCHAR2(32000);
3698: l_contact_contains_str VARCHAR2(32000);
3699: l_contact_pt_contains_str VARCHAR2(32000);
3700: l_denorm_str VARCHAR2(32000);
3701: l_ps_denorm_str VARCHAR2(32000);
3702: l_ct_denorm_str VARCHAR2(32000);
3695: -- Strings to hold the generated Intermedia query strings
3696: l_party_contains_str VARCHAR2(32000);
3697: l_party_site_contains_str VARCHAR2(32000);
3698: l_contact_contains_str VARCHAR2(32000);
3699: l_contact_pt_contains_str VARCHAR2(32000);
3700: l_denorm_str VARCHAR2(32000);
3701: l_ps_denorm_str VARCHAR2(32000);
3702: l_ct_denorm_str VARCHAR2(32000);
3703: l_cpt_denorm_str VARCHAR2(32000);
3828: call_type(2) := 'OR';
3829: END;
3830:
3831: /** Private procedure to acquire and score at party level ***/
3832: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3833: l_party_id_idx NUMBER:=1;
3834: l_ctx_id NUMBER;
3835: l_TX35_new varchar2(4000);
3836: BEGIN
3850: l_search_ctx_id,H_PARTY_ID(I));
3851: l_party_id_idx := H_PARTY_ID.COUNT+1;
3852: ELSE
3853: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3854: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3855: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3856: 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);
3857: END IF;
3858: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3858: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3859: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3860: 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);
3861: END IF;
3862: IF (l_party_site_contains_str IS NULL AND
3863: l_contact_contains_str IS NULL AND
3864: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3865: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3866: RAISE L_RETURN_IMM_EXC;
3859: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3860: 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);
3861: END IF;
3862: IF (l_party_site_contains_str IS NULL AND
3863: l_contact_contains_str IS NULL AND
3864: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3865: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3866: RAISE L_RETURN_IMM_EXC;
3867: ELSE
3860: 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);
3861: END IF;
3862: IF (l_party_site_contains_str IS NULL AND
3863: l_contact_contains_str IS NULL AND
3864: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3865: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3866: RAISE L_RETURN_IMM_EXC;
3867: ELSE
3868: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3864: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3865: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3866: RAISE L_RETURN_IMM_EXC;
3867: ELSE
3868: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3869: END IF;
3870: ELSE
3871: IF p_index>1 THEN
3872: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3884: l_ctx_id := NULL;
3885: l_party_id_idx := H_PARTY_ID.COUNT+1;
3886: END IF;
3887: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3888: 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);
3889: END IF;
3890: LOOP
3891: FETCH l_party_cur INTO
3892: l_party_id , l_TX34, l_TX35, l_TX40, l_TX39, l_TX45;
3908: l_party_id_idx:= l_party_id_idx+1;
3909: END IF;
3910: IF (l_party_id_idx-1)>l_max_thresh THEN
3911: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3912: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3913: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3914: AND l_contact_pt_contains_str IS NULL THEN
3915: H_PARTY_ID.DELETE(l_party_id_idx-1);
3916: H_PARTY_ID_LIST.DELETE(l_index);
3910: IF (l_party_id_idx-1)>l_max_thresh THEN
3911: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3912: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3913: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3914: AND l_contact_pt_contains_str IS NULL THEN
3915: H_PARTY_ID.DELETE(l_party_id_idx-1);
3916: H_PARTY_ID_LIST.DELETE(l_index);
3917: H_SCORES.DELETE(l_index);
3918: EXIT;
3939: CLOSE l_party_cur;
3940: ROLLBACK to eval_start;
3941: END;
3942: PROCEDURE open_person_contact_cursor(
3943: p_contains_str VARCHAR2,
3944: p_search_ctx_id NUMBER,
3945: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3946: BEGIN
3947: OPEN x_cursor FOR
3946: BEGIN
3947: OPEN x_cursor FOR
3948: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID, TX22
3949: FROM HZ_STAGED_CONTACTS stage
3950: WHERE contains( concat_col, p_contains_str)>0
3951: AND ORG_CONTACT_ID in (
3952: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3953: org_contact_id
3954: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc
3970: end if;
3971: END;
3972:
3973: /** Private procedure to acquire and score at party site level ***/
3974: 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
3975: l_party_id_idx NUMBER:=1;
3976: l_ctx_id NUMBER;
3977: h_ps_id HZ_PARTY_SEARCH.IDList;
3978: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3998: ELSE
3999: l_party_id_idx := H_PARTY_ID.COUNT+1;
4000: l_ctx_id := NULL;
4001: END IF;
4002: 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);
4003: LOOP
4004: FETCH l_party_site_cur INTO
4005: 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;
4006: EXIT WHEN l_party_site_cur%NOTFOUND;
4069: END IF;
4070: END;
4071:
4072: /** Private procedure to acquire and score at party site level ***/
4073: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
4074: l_party_id_idx NUMBER:=1;
4075: l_ctx_id NUMBER;
4076: h_ct_id HZ_PARTY_SEARCH.IDList;
4077: h_ct_party_id HZ_PARTY_SEARCH.IDList;
4085: FORALL I in 1..H_PARTY_ID.COUNT
4086: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
4087: l_search_ctx_id,H_PARTY_ID(I));
4088: l_party_id_idx := H_PARTY_ID.COUNT+1;
4089: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
4090: LOOP
4091: FETCH l_contact_cur INTO
4092: l_org_contact_id, l_ct_party_id , l_TX22;
4093: EXIT WHEN l_contact_cur%NOTFOUND;
4127: END IF;
4128: END;
4129:
4130: /** Private procedure to acquire and score at contact point level ***/
4131: 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
4132: l_party_id_idx NUMBER:=1;
4133: l_ctx_id NUMBER;
4134: h_cpt_id HZ_PARTY_SEARCH.IDList;
4135: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
4172: END IF;
4173: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4174: 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);
4175: END IF;
4176: 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);
4177: LOOP
4178: FETCH l_contact_pt_cur INTO
4179: 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;
4180: EXIT WHEN l_contact_pt_cur%NOTFOUND;
4375: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
4376: l_threshold := round(( l_entered_max_score / 520) * 416);
4377: other_acq_criteria_exists := TRUE ;
4378: --check if acquisition criteria exists for any other entity
4379: IF l_party_contains_str IS NOT NULL THEN
4380: acq_cnt := 1;
4381: END IF;
4382: IF l_party_site_contains_str IS NOT NULL THEN
4383: acq_cnt := acq_cnt+1;
4378: --check if acquisition criteria exists for any other entity
4379: IF l_party_contains_str IS NOT NULL THEN
4380: acq_cnt := 1;
4381: END IF;
4382: IF l_party_site_contains_str IS NOT NULL THEN
4383: acq_cnt := acq_cnt+1;
4384: END IF;
4385: IF l_contact_contains_str IS NOT NULL THEN
4386: acq_cnt := acq_cnt+1;
4381: END IF;
4382: IF l_party_site_contains_str IS NOT NULL THEN
4383: acq_cnt := acq_cnt+1;
4384: END IF;
4385: IF l_contact_contains_str IS NOT NULL THEN
4386: acq_cnt := acq_cnt+1;
4387: END IF;
4388: IF l_contact_pt_contains_str IS NOT NULL THEN
4389: acq_cnt := acq_cnt+1;
4384: END IF;
4385: IF l_contact_contains_str IS NOT NULL THEN
4386: acq_cnt := acq_cnt+1;
4387: END IF;
4388: IF l_contact_pt_contains_str IS NOT NULL THEN
4389: acq_cnt := acq_cnt+1;
4390: END IF;
4391: IF acq_cnt>1 THEN
4392: other_acq_criteria_exists := TRUE;
4418: FND_MSG_PUB.ADD;
4419: RAISE FND_API.G_EXC_ERROR;
4420: END IF;
4421: END IF;
4422: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4423: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4424: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4425: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4426: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4419: RAISE FND_API.G_EXC_ERROR;
4420: END IF;
4421: END IF;
4422: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4423: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4424: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4425: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4426: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4427: 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 IF;
4422: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4423: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4424: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4425: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4426: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4427: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4428: END IF;
4421: END IF;
4422: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4423: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4424: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4425: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4426: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4427: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4428: END IF;
4429: END;
4422: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4423: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4424: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4425: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4426: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4427: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4428: END IF;
4429: END;
4430: /************ End of find_persons private procedures **********/
4423: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4424: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4425: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4426: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4427: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4428: END IF;
4429: END;
4430: /************ End of find_persons private procedures **********/
4431:
4458: g_score_until_thresh := false;
4459: END IF;
4460: -- ************************************************************
4461: -- STEP 2. Setup of intermedia query strings for Acquisition query
4462: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4463: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4464: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4465: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4466: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4459: END IF;
4460: -- ************************************************************
4461: -- STEP 2. Setup of intermedia query strings for Acquisition query
4462: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4463: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4464: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4465: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4466: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4467:
4460: -- ************************************************************
4461: -- STEP 2. Setup of intermedia query strings for Acquisition query
4462: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4463: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4464: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4465: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4466: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4467:
4468: -- Setup Search Context ID
4461: -- STEP 2. Setup of intermedia query strings for Acquisition query
4462: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4463: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4464: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4465: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4466: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4467:
4468: -- Setup Search Context ID
4469: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4468: -- Setup Search Context ID
4469: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4470: x_search_ctx_id := l_search_ctx_id;
4471:
4472: IF l_party_contains_str IS NULL THEN
4473: defpt := 1;
4474: END IF;
4475: IF l_party_site_contains_str IS NULL THEN
4476: defps := 1;
4471:
4472: IF l_party_contains_str IS NULL THEN
4473: defpt := 1;
4474: END IF;
4475: IF l_party_site_contains_str IS NULL THEN
4476: defps := 1;
4477: END IF;
4478: IF l_contact_contains_str IS NULL THEN
4479: defct := 1;
4474: END IF;
4475: IF l_party_site_contains_str IS NULL THEN
4476: defps := 1;
4477: END IF;
4478: IF l_contact_contains_str IS NULL THEN
4479: defct := 1;
4480: END IF;
4481: IF l_contact_pt_contains_str IS NULL THEN
4482: defcpt := 1;
4477: END IF;
4478: IF l_contact_contains_str IS NULL THEN
4479: defct := 1;
4480: END IF;
4481: IF l_contact_pt_contains_str IS NULL THEN
4482: defcpt := 1;
4483: END IF;
4484:
4485: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4483: END IF;
4484:
4485: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4486: 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);
4487: 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);
4488: 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);
4489: 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);
4490: 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);
4491: 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:
4485: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4486: 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);
4487: 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);
4488: 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);
4489: 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);
4490: 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);
4491: 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);
4492: END IF;
4485: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4486: 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);
4487: 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);
4488: 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);
4489: 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);
4490: 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);
4491: 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);
4492: END IF;
4493: FOR I in 1..5 LOOP
4486: 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);
4487: 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);
4488: 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);
4489: 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);
4490: 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);
4491: 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);
4492: END IF;
4493: FOR I in 1..5 LOOP
4494: do_eval(I);
4492: END IF;
4493: FOR I in 1..5 LOOP
4494: do_eval(I);
4495: END LOOP;
4496: IF l_contact_contains_str IS NOT NULL THEN
4497: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4498: END IF;
4499: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4500: 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: FOR I in 1..5 LOOP
4494: do_eval(I);
4495: END LOOP;
4496: IF l_contact_contains_str IS NOT NULL THEN
4497: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4498: END IF;
4499: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4500: 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);
4501: END IF;
5344: x_num_matches OUT NUMBER
5345: ) IS
5346:
5347: -- Strings to hold the generated Intermedia query strings
5348: l_party_contains_str VARCHAR2(32000);
5349: l_match_idx NUMBER;
5350: l_party_site_contains_str VARCHAR2(32000);
5351: l_contact_contains_str VARCHAR2(32000);
5352: l_contact_pt_contains_str VARCHAR2(32000);
5346:
5347: -- Strings to hold the generated Intermedia query strings
5348: l_party_contains_str VARCHAR2(32000);
5349: l_match_idx NUMBER;
5350: l_party_site_contains_str VARCHAR2(32000);
5351: l_contact_contains_str VARCHAR2(32000);
5352: l_contact_pt_contains_str VARCHAR2(32000);
5353: l_tmp VARCHAR2(32000);
5354:
5347: -- Strings to hold the generated Intermedia query strings
5348: l_party_contains_str VARCHAR2(32000);
5349: l_match_idx NUMBER;
5350: l_party_site_contains_str VARCHAR2(32000);
5351: l_contact_contains_str VARCHAR2(32000);
5352: l_contact_pt_contains_str VARCHAR2(32000);
5353: l_tmp VARCHAR2(32000);
5354:
5355: -- Other local variables
5348: l_party_contains_str VARCHAR2(32000);
5349: l_match_idx NUMBER;
5350: l_party_site_contains_str VARCHAR2(32000);
5351: l_contact_contains_str VARCHAR2(32000);
5352: l_contact_pt_contains_str VARCHAR2(32000);
5353: l_tmp VARCHAR2(32000);
5354:
5355: -- Other local variables
5356: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5434: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5435:
5436: -- ************************************************************
5437: -- STEP 2. Setup of intermedia query strings for Acquisition query
5438: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5439: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5440: 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);
5441:
5442: -- Setup Search Context ID
5435:
5436: -- ************************************************************
5437: -- STEP 2. Setup of intermedia query strings for Acquisition query
5438: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5439: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5440: 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);
5441:
5442: -- Setup Search Context ID
5443: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5444: x_search_ctx_id := l_search_ctx_id;
5445:
5446: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5447: 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);
5448: 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);
5449: 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);
5450: 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);
5451: END IF;
5452: IF l_party_site_contains_str IS NULL THEN
5445:
5446: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5447: 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);
5448: 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);
5449: 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);
5450: 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);
5451: END IF;
5452: IF l_party_site_contains_str IS NULL THEN
5453: defps := 1;
5448: 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);
5449: 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);
5450: 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);
5451: END IF;
5452: IF l_party_site_contains_str IS NULL THEN
5453: defps := 1;
5454: END IF;
5455: IF l_contact_pt_contains_str IS NULL THEN
5456: defcpt := 1;
5451: END IF;
5452: IF l_party_site_contains_str IS NULL THEN
5453: defps := 1;
5454: END IF;
5455: IF l_contact_pt_contains_str IS NULL THEN
5456: defcpt := 1;
5457: END IF;
5458:
5459: IF l_party_site_contains_str IS NOT NULL THEN
5455: IF l_contact_pt_contains_str IS NULL THEN
5456: defcpt := 1;
5457: END IF;
5458:
5459: IF l_party_site_contains_str IS NOT NULL THEN
5460: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5461: LOOP
5462: FETCH l_party_site_cur INTO
5463: 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: defcpt := 1;
5457: END IF;
5458:
5459: IF l_party_site_contains_str IS NOT NULL THEN
5460: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5461: LOOP
5462: FETCH l_party_site_cur INTO
5463: 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;
5464: EXIT WHEN l_party_site_cur%NOTFOUND;
5479: END LOOP;
5480: CLOSE l_party_site_cur;
5481: END IF;
5482:
5483: IF l_contact_pt_contains_str IS NOT NULL THEN
5484: SAVEPOINT eval_start;
5485: IF l_match_str = ' AND ' OR (
5486: ((l_cpt_max_score/l_entered_max_score)<(416/520))
5487: ) THEN
5487: ) THEN
5488: FORALL I in 1..l_match_ps_list.COUNT
5489: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5490: values (l_search_ctx_id,l_match_ps_list(I));
5491: 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');
5492: ELSE
5493: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5494: END IF;
5495: LOOP
5489: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5490: values (l_search_ctx_id,l_match_ps_list(I));
5491: 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');
5492: ELSE
5493: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5494: END IF;
5495: LOOP
5496: FETCH l_contact_pt_cur INTO
5497: 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;
5589: x_num_matches OUT NUMBER
5590: ) IS
5591:
5592: -- Strings to hold the generated Intermedia query strings
5593: l_party_contains_str VARCHAR2(32000);
5594: l_party_site_contains_str VARCHAR2(32000);
5595: l_contact_contains_str VARCHAR2(32000);
5596: l_contact_pt_contains_str VARCHAR2(32000);
5597: l_tmp VARCHAR2(32000);
5590: ) IS
5591:
5592: -- Strings to hold the generated Intermedia query strings
5593: l_party_contains_str VARCHAR2(32000);
5594: l_party_site_contains_str VARCHAR2(32000);
5595: l_contact_contains_str VARCHAR2(32000);
5596: l_contact_pt_contains_str VARCHAR2(32000);
5597: l_tmp VARCHAR2(32000);
5598:
5591:
5592: -- Strings to hold the generated Intermedia query strings
5593: l_party_contains_str VARCHAR2(32000);
5594: l_party_site_contains_str VARCHAR2(32000);
5595: l_contact_contains_str VARCHAR2(32000);
5596: l_contact_pt_contains_str VARCHAR2(32000);
5597: l_tmp VARCHAR2(32000);
5598:
5599: -- Other local variables
5592: -- Strings to hold the generated Intermedia query strings
5593: l_party_contains_str VARCHAR2(32000);
5594: l_party_site_contains_str VARCHAR2(32000);
5595: l_contact_contains_str VARCHAR2(32000);
5596: l_contact_pt_contains_str VARCHAR2(32000);
5597: l_tmp VARCHAR2(32000);
5598:
5599: -- Other local variables
5600: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5667: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5668:
5669: -- ************************************************************
5670: -- STEP 2. Setup of intermedia query strings for Acquisition query
5671: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5672: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5673: 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);
5674:
5675: -- Setup Search Context ID
5668:
5669: -- ************************************************************
5670: -- STEP 2. Setup of intermedia query strings for Acquisition query
5671: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5672: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5673: 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);
5674:
5675: -- Setup Search Context ID
5676: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5677: x_search_ctx_id := l_search_ctx_id;
5678:
5679: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5680: 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);
5681: 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);
5682: 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);
5683: 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);
5684: END IF;
5685: IF l_contact_contains_str IS NULL THEN
5678:
5679: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5680: 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);
5681: 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);
5682: 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);
5683: 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);
5684: END IF;
5685: IF l_contact_contains_str IS NULL THEN
5686: defct := 1;
5681: 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);
5682: 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);
5683: 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);
5684: END IF;
5685: IF l_contact_contains_str IS NULL THEN
5686: defct := 1;
5687: END IF;
5688: IF l_contact_pt_contains_str IS NULL THEN
5689: defcpt := 1;
5684: END IF;
5685: IF l_contact_contains_str IS NULL THEN
5686: defct := 1;
5687: END IF;
5688: IF l_contact_pt_contains_str IS NULL THEN
5689: defcpt := 1;
5690: END IF;
5691:
5692: IF l_contact_contains_str IS NOT NULL THEN
5688: IF l_contact_pt_contains_str IS NULL THEN
5689: defcpt := 1;
5690: END IF;
5691:
5692: IF l_contact_contains_str IS NOT NULL THEN
5693: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5694: LOOP
5695: FETCH l_contact_cur INTO
5696: l_org_contact_id, l_ct_party_id , l_TX22;
5689: defcpt := 1;
5690: END IF;
5691:
5692: IF l_contact_contains_str IS NOT NULL THEN
5693: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5694: LOOP
5695: FETCH l_contact_cur INTO
5696: l_org_contact_id, l_ct_party_id , l_TX22;
5697: EXIT WHEN l_contact_cur%NOTFOUND;
5710: END LOOP;
5711: CLOSE l_contact_cur;
5712: END IF;
5713:
5714: IF l_contact_pt_contains_str IS NOT NULL THEN
5715: SAVEPOINT eval_start;
5716: IF l_match_str = ' AND ' OR (
5717: ((l_cpt_max_score/l_entered_max_score)<(416/520))
5718: ) THEN
5718: ) THEN
5719: FORALL I in 1..l_match_contact_list.COUNT
5720: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5721: values (l_search_ctx_id,l_match_contact_list(I));
5722: 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');
5723: ELSE
5724: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5725: END IF;
5726: LOOP
5720: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5721: values (l_search_ctx_id,l_match_contact_list(I));
5722: 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');
5723: ELSE
5724: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5725: END IF;
5726: LOOP
5727: FETCH l_contact_pt_cur INTO
5728: 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;
5818: ) IS
5819:
5820:
5821: -- Strings to hold the generated Intermedia query strings
5822: l_contact_pt_contains_str VARCHAR2(32000);
5823: l_tmp VARCHAR2(32000);
5824:
5825: -- Other local variables
5826: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5879: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5880:
5881: -- ************************************************************
5882: -- STEP 2. Setup of intermedia query strings for Acquisition query
5883: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5884: 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);
5885:
5886: -- Setup Search Context ID
5887: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5887: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5888: x_search_ctx_id := l_search_ctx_id;
5889: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5890: 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);
5891: 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);
5892: 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);
5893: END IF;
5894:
5895: IF l_contact_pt_contains_str IS NOT NULL THEN
5891: 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);
5892: 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);
5893: END IF;
5894:
5895: IF l_contact_pt_contains_str IS NOT NULL THEN
5896: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5897: cnt := 1;
5898: LOOP
5899: FETCH l_contact_pt_cur INTO
5892: 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);
5893: END IF;
5894:
5895: IF l_contact_pt_contains_str IS NOT NULL THEN
5896: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5897: cnt := 1;
5898: LOOP
5899: FETCH l_contact_pt_cur INTO
5900: 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;
5963: x_search_ctx_id IN OUT NUMBER
5964: ) IS
5965:
5966: -- Strings to hold the generated Intermedia query strings
5967: l_party_contains_str VARCHAR2(32000);
5968: l_party_site_contains_str VARCHAR2(32000);
5969: l_contact_contains_str VARCHAR2(32000);
5970: l_contact_pt_contains_str VARCHAR2(32000);
5971: l_tmp VARCHAR2(32000);
5964: ) IS
5965:
5966: -- Strings to hold the generated Intermedia query strings
5967: l_party_contains_str VARCHAR2(32000);
5968: l_party_site_contains_str VARCHAR2(32000);
5969: l_contact_contains_str VARCHAR2(32000);
5970: l_contact_pt_contains_str VARCHAR2(32000);
5971: l_tmp VARCHAR2(32000);
5972:
5965:
5966: -- Strings to hold the generated Intermedia query strings
5967: l_party_contains_str VARCHAR2(32000);
5968: l_party_site_contains_str VARCHAR2(32000);
5969: l_contact_contains_str VARCHAR2(32000);
5970: l_contact_pt_contains_str VARCHAR2(32000);
5971: l_tmp VARCHAR2(32000);
5972:
5973: -- Other local variables
5966: -- Strings to hold the generated Intermedia query strings
5967: l_party_contains_str VARCHAR2(32000);
5968: l_party_site_contains_str VARCHAR2(32000);
5969: l_contact_contains_str VARCHAR2(32000);
5970: l_contact_pt_contains_str VARCHAR2(32000);
5971: l_tmp VARCHAR2(32000);
5972:
5973: -- Other local variables
5974: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6060: g_score_until_thresh := false;
6061: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6062: -- ************************************************************
6063: -- STEP 2. Setup of intermedia query strings for Acquisition query
6064: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6065: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6066: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6067: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6068:
6061: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6062: -- ************************************************************
6063: -- STEP 2. Setup of intermedia query strings for Acquisition query
6064: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6065: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6066: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6067: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6068:
6069: -- Setup Search Context ID
6062: -- ************************************************************
6063: -- STEP 2. Setup of intermedia query strings for Acquisition query
6064: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6065: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6066: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6067: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6068:
6069: -- Setup Search Context ID
6070: IF x_search_ctx_id IS NULL THEN
6084: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX34,l_TX35_new,l_TX40,l_TX39,l_TX45,1);
6085: END LOOP;
6086: CLOSE l_party_cur;
6087:
6088: IF l_party_site_contains_str IS NOT NULL THEN
6089: l_max_score := 0;
6090: l_max_id := 0;
6091: l_max_idx := 0;
6092: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6089: l_max_score := 0;
6090: l_max_id := 0;
6091: l_max_idx := 0;
6092: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6093: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
6094: ELSE
6095: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
6096: END IF;
6097: LOOP
6091: l_max_idx := 0;
6092: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6093: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
6094: ELSE
6095: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
6096: END IF;
6097: LOOP
6098: FETCH l_party_site_cur INTO
6099: 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;
6120: 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);
6121: END IF;
6122: END IF;
6123:
6124: IF l_contact_contains_str IS NOT NULL THEN
6125: l_max_score := 0;
6126: l_max_id := 0;
6127: l_max_idx := 0;
6128: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
6124: IF l_contact_contains_str IS NOT NULL THEN
6125: l_max_score := 0;
6126: l_max_id := 0;
6127: l_max_idx := 0;
6128: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
6129: LOOP
6130: FETCH l_contact_cur INTO
6131: l_org_contact_id, l_ct_party_id , l_TX22;
6132: EXIT WHEN l_contact_cur%NOTFOUND;
6143: 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);
6144: END IF;
6145: END IF;
6146:
6147: IF l_contact_pt_contains_str IS NOT NULL THEN
6148: l_max_score := 0;
6149: l_max_id := 0;
6150: l_max_idx := 0;
6151: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6148: l_max_score := 0;
6149: l_max_id := 0;
6150: l_max_idx := 0;
6151: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6152: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
6153: ELSE
6154: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
6155: END IF;
6156: LOOP
6150: l_max_idx := 0;
6151: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6152: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
6153: ELSE
6154: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
6155: END IF;
6156: LOOP
6157: FETCH l_contact_pt_cur INTO
6158: 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;