[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:
1434: p_party_max_score NUMBER,
1435: p_denorm_max_score NUMBER,
1436: p_non_denorm_max_score NUMBER,
1437: p_threshold NUMBER) RETURN VARCHAR2 IS
1438: l_party_contains_str VARCHAR2(32000);
1439: l_party_filter VARCHAR2(1) := null;
1440: l_prim_temp VARCHAR2(4000);
1441: l_denorm_str VARCHAR2(4000);
1442: BEGIN
1478:
1479: IF l_denorm_str IS NOT NULL THEN
1480: IF (p_non_denorm_max_score+25)>=p_threshold THEN
1481: l_denorm_str := NULL;
1482: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1483: ELSIF (p_non_denorm_max_score+p_denorm_max_score+25)>=p_threshold THEN
1484: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1485: l_denorm_str := NULL;
1486: END IF;
1480: IF (p_non_denorm_max_score+25)>=p_threshold THEN
1481: l_denorm_str := NULL;
1482: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1483: ELSIF (p_non_denorm_max_score+p_denorm_max_score+25)>=p_threshold THEN
1484: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1485: l_denorm_str := NULL;
1486: END IF;
1487: ELSE
1488: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1484: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1485: l_denorm_str := NULL;
1486: END IF;
1487: ELSE
1488: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1489: END IF;
1490:
1491: -- Setup query string for SIC_CODE
1492: l_prim_temp := null;
1495:
1496: IF l_denorm_str IS NOT NULL THEN
1497: IF (p_non_denorm_max_score+25)>=p_threshold THEN
1498: l_denorm_str := NULL;
1499: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1500: ELSIF (p_non_denorm_max_score+p_denorm_max_score+25)>=p_threshold THEN
1501: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1502: l_denorm_str := NULL;
1503: END IF;
1497: IF (p_non_denorm_max_score+25)>=p_threshold THEN
1498: l_denorm_str := NULL;
1499: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1500: ELSIF (p_non_denorm_max_score+p_denorm_max_score+25)>=p_threshold THEN
1501: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1502: l_denorm_str := NULL;
1503: END IF;
1504: ELSE
1505: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1501: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1502: l_denorm_str := NULL;
1503: END IF;
1504: ELSE
1505: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1506: END IF;
1507:
1508: -- Setup query string for PARTY_ALL_NAMES
1509: l_prim_temp := null;
1514:
1515: IF l_denorm_str IS NOT NULL THEN
1516: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1517: l_denorm_str := NULL;
1518: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1519: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1520: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1521: l_denorm_str := NULL;
1522: END IF;
1516: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1517: l_denorm_str := NULL;
1518: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1519: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1520: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1521: l_denorm_str := NULL;
1522: END IF;
1523: ELSE
1524: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1520: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1521: l_denorm_str := NULL;
1522: END IF;
1523: ELSE
1524: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1525: END IF;
1526:
1527: -- Setup query string for JGZZ_FISCAL_CODE
1528: l_prim_temp := null;
1531:
1532: IF l_denorm_str IS NOT NULL THEN
1533: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1534: l_denorm_str := NULL;
1535: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1536: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1537: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1538: l_denorm_str := NULL;
1539: END IF;
1533: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1534: l_denorm_str := NULL;
1535: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1536: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1537: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1538: l_denorm_str := NULL;
1539: END IF;
1540: ELSE
1541: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1537: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1538: l_denorm_str := NULL;
1539: END IF;
1540: ELSE
1541: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1542: END IF;
1543:
1544: -- Setup query string for PARTY_NUMBER
1545: l_prim_temp := null;
1548:
1549: IF l_denorm_str IS NOT NULL THEN
1550: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1551: l_denorm_str := NULL;
1552: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1553: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1554: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1555: l_denorm_str := NULL;
1556: END IF;
1550: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1551: l_denorm_str := NULL;
1552: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1553: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1554: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1555: l_denorm_str := NULL;
1556: END IF;
1557: ELSE
1558: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1554: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1555: l_denorm_str := NULL;
1556: END IF;
1557: ELSE
1558: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1559: END IF;
1560:
1561: -- Setup query string for TAX_REFERENCE
1562: l_prim_temp := null;
1565:
1566: IF l_denorm_str IS NOT NULL THEN
1567: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1568: l_denorm_str := NULL;
1569: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1570: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1571: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1572: l_denorm_str := NULL;
1573: END IF;
1567: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1568: l_denorm_str := NULL;
1569: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1570: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1571: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1572: l_denorm_str := NULL;
1573: END IF;
1574: ELSE
1575: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1571: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1572: l_denorm_str := NULL;
1573: END IF;
1574: ELSE
1575: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1576: END IF;
1577: IF lengthb(l_party_contains_str) > 4000 THEN
1578: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1579: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1573: END IF;
1574: ELSE
1575: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1576: END IF;
1577: IF lengthb(l_party_contains_str) > 4000 THEN
1578: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1579: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1580: FND_MSG_PUB.ADD;
1581: RAISE FND_API.G_EXC_ERROR;
1579: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1580: FND_MSG_PUB.ADD;
1581: RAISE FND_API.G_EXC_ERROR;
1582: END IF;
1583: IF (p_match_str = ' AND ' OR p_party_max_score
1585: 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);
1586: END IF;
1587: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1581: RAISE FND_API.G_EXC_ERROR;
1582: END IF;
1583: IF (p_match_str = ' AND ' OR p_party_max_score
1585: 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);
1586: END IF;
1587: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1588: 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);
1589: END IF;
1584: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1585: 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);
1586: END IF;
1587: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1588: 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);
1589: END IF;
1590: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1591: 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);
1592: END IF;
1589: END IF;
1590: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1591: 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);
1592: END IF;
1593: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1594: ELSE
1595: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1596: 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);
1597: 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);
1592: END IF;
1593: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1594: ELSE
1595: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1596: 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);
1597: 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);
1598: END IF;
1599: RETURN l_party_contains_str;
1600: END IF;
1593: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1594: ELSE
1595: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1596: 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);
1597: 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);
1598: END IF;
1599: RETURN l_party_contains_str;
1600: END IF;
1601: END;
1595: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1596: 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);
1597: 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);
1598: END IF;
1599: RETURN l_party_contains_str;
1600: END IF;
1601: END;
1602: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1603: l_contains_str VARCHAR2(32000);
1599: RETURN l_party_contains_str;
1600: END IF;
1601: END;
1602: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1603: l_contains_str VARCHAR2(32000);
1604: l_contains_str_temp VARCHAR2(32000);
1605: l_den_contains_str VARCHAR2(32000);
1606: l_den_contains_str_temp VARCHAR2(32000);
1607: l_filter_str VARCHAR2(4000) := null;
1600: END IF;
1601: END;
1602: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1603: l_contains_str VARCHAR2(32000);
1604: l_contains_str_temp VARCHAR2(32000);
1605: l_den_contains_str VARCHAR2(32000);
1606: l_den_contains_str_temp VARCHAR2(32000);
1607: l_filter_str VARCHAR2(4000) := null;
1608: l_prim_temp VARCHAR2(4000) := null;
1601: END;
1602: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1603: l_contains_str VARCHAR2(32000);
1604: l_contains_str_temp VARCHAR2(32000);
1605: l_den_contains_str VARCHAR2(32000);
1606: l_den_contains_str_temp VARCHAR2(32000);
1607: l_filter_str VARCHAR2(4000) := null;
1608: l_prim_temp VARCHAR2(4000) := null;
1609: l_prim_temp_den VARCHAR2(4000) := null;
1602: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1603: l_contains_str VARCHAR2(32000);
1604: l_contains_str_temp VARCHAR2(32000);
1605: l_den_contains_str VARCHAR2(32000);
1606: l_den_contains_str_temp VARCHAR2(32000);
1607: l_filter_str VARCHAR2(4000) := null;
1608: l_prim_temp VARCHAR2(4000) := null;
1609: l_prim_temp_den VARCHAR2(4000) := null;
1610: BEGIN
1612: 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);
1613: END IF;
1614: x_denorm_str := NULL;
1615:
1616: -- Dynamic setup of party site contains str
1617: --
1618: -- For each primary transformation add to intermedia query if it
1619: -- is not null
1620: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1617: --
1618: -- For each primary transformation add to intermedia query if it
1619: -- is not null
1620: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1621: l_contains_str_temp := null;
1622: l_den_contains_str_temp := null;
1623: l_filter_str := null;
1624:
1625: -- Setup query string for ADDRESS
1618: -- For each primary transformation add to intermedia query if it
1619: -- is not null
1620: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1621: l_contains_str_temp := null;
1622: l_den_contains_str_temp := null;
1623: l_filter_str := null;
1624:
1625: -- Setup query string for ADDRESS
1626: l_prim_temp := null;
1626: l_prim_temp := null;
1627: l_prim_temp_den := null;
1628: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRADDRESS
1629: g_party_site_stage_list(I).TX4,'A27',l_prim_temp);
1630: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1631:
1632: -- Setup query string for CITY
1633: l_prim_temp := null;
1634: l_prim_temp_den := null;
1635: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1636: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1637: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1638: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1639: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1640: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1641:
1642: -- Setup query string for POSTAL_CODE
1643: l_prim_temp := null;
1636: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1637: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1638: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1639: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1640: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1641:
1642: -- Setup query string for POSTAL_CODE
1643: l_prim_temp := null;
1644: l_prim_temp_den := null;
1645: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1646: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1647: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1648: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1649: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1650: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1651:
1652: -- Setup query string for STATE
1653: l_prim_temp := null;
1646: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1647: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1648: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1649: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1650: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1651:
1652: -- Setup query string for STATE
1653: l_prim_temp := null;
1654: l_prim_temp_den := null;
1655: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1656: g_party_site_stage_list(I).TX15,'A32',l_prim_temp);
1657: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1658: g_party_site_stage_list(I).TX15,'D_PS',l_prim_temp_den);
1659: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1660: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1661:
1662: -- Setup query string for COUNTRY
1663: l_prim_temp := null;
1656: g_party_site_stage_list(I).TX15,'A32',l_prim_temp);
1657: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1658: g_party_site_stage_list(I).TX15,'D_PS',l_prim_temp_den);
1659: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1660: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1661:
1662: -- Setup query string for COUNTRY
1663: l_prim_temp := null;
1664: l_prim_temp_den := null;
1665: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1666: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1667: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1668: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1669: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1670: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1671:
1672: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1673: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1666: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1667: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1668: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1669: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1670: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1671:
1672: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1673: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1674: END LOOP;
1668: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1669: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1670: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1671:
1672: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1673: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1674: END LOOP;
1675: -- Add the search criteria to query string
1676: IF lengthb(l_contains_str) > 4000 THEN
1669: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1670: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1671:
1672: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1673: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1674: END LOOP;
1675: -- Add the search criteria to query string
1676: IF lengthb(l_contains_str) > 4000 THEN
1677: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1672: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1673: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1674: END LOOP;
1675: -- Add the search criteria to query string
1676: IF lengthb(l_contains_str) > 4000 THEN
1677: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1678: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1679: FND_MSG_PUB.ADD;
1680: RAISE FND_API.G_EXC_ERROR;
1678: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1679: FND_MSG_PUB.ADD;
1680: RAISE FND_API.G_EXC_ERROR;
1681: END IF;
1682: x_denorm_str := l_den_contains_str;
1683: RETURN l_contains_str;
1684: END;
1685:
1686: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1679: FND_MSG_PUB.ADD;
1680: RAISE FND_API.G_EXC_ERROR;
1681: END IF;
1682: x_denorm_str := l_den_contains_str;
1683: RETURN l_contains_str;
1684: END;
1685:
1686: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1687: l_contains_str VARCHAR2(32000);
1683: RETURN l_contains_str;
1684: END;
1685:
1686: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1687: l_contains_str VARCHAR2(32000);
1688: l_contains_str_temp VARCHAR2(32000);
1689: l_den_contains_str VARCHAR2(32000);
1690: l_den_contains_str_temp VARCHAR2(32000);
1691: l_filter_str VARCHAR2(4000) := null;
1684: END;
1685:
1686: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1687: l_contains_str VARCHAR2(32000);
1688: l_contains_str_temp VARCHAR2(32000);
1689: l_den_contains_str VARCHAR2(32000);
1690: l_den_contains_str_temp VARCHAR2(32000);
1691: l_filter_str VARCHAR2(4000) := null;
1692: l_prim_temp VARCHAR2(4000) := null;
1685:
1686: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1687: l_contains_str VARCHAR2(32000);
1688: l_contains_str_temp VARCHAR2(32000);
1689: l_den_contains_str VARCHAR2(32000);
1690: l_den_contains_str_temp VARCHAR2(32000);
1691: l_filter_str VARCHAR2(4000) := null;
1692: l_prim_temp VARCHAR2(4000) := null;
1693: l_prim_temp_den VARCHAR2(4000) := null;
1686: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1687: l_contains_str VARCHAR2(32000);
1688: l_contains_str_temp VARCHAR2(32000);
1689: l_den_contains_str VARCHAR2(32000);
1690: l_den_contains_str_temp VARCHAR2(32000);
1691: l_filter_str VARCHAR2(4000) := null;
1692: l_prim_temp VARCHAR2(4000) := null;
1693: l_prim_temp_den VARCHAR2(4000) := null;
1694: BEGIN
1696: 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);
1697: END IF;
1698: x_denorm_str := NULL;
1699:
1700: -- Dynamic setup of party site contains str
1701: --
1702: -- For each primary transformation add to intermedia query if it
1703: -- is not null
1704: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1701: --
1702: -- For each primary transformation add to intermedia query if it
1703: -- is not null
1704: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1705: l_contains_str_temp := null;
1706: l_den_contains_str_temp := null;
1707: l_filter_str := null;
1708:
1709: -- Setup query string for CONTACT_NAME
1702: -- For each primary transformation add to intermedia query if it
1703: -- is not null
1704: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1705: l_contains_str_temp := null;
1706: l_den_contains_str_temp := null;
1707: l_filter_str := null;
1708:
1709: -- Setup query string for CONTACT_NAME
1710: l_prim_temp := null;
1712: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRPERSON
1713: g_contact_stage_list(I).TX6,'A39',l_prim_temp);
1714: HZ_DQM_SEARCH_UTIL.add_transformation( -- SOUNDEX
1715: g_contact_stage_list(I).TX156,'A39',l_prim_temp);
1716: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1717:
1718: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1719: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1720: END LOOP;
1714: HZ_DQM_SEARCH_UTIL.add_transformation( -- SOUNDEX
1715: g_contact_stage_list(I).TX156,'A39',l_prim_temp);
1716: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1717:
1718: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1719: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1720: END LOOP;
1721: -- Add the search criteria to query string
1722: IF lengthb(l_contains_str) > 4000 THEN
1715: g_contact_stage_list(I).TX156,'A39',l_prim_temp);
1716: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1717:
1718: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1719: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1720: END LOOP;
1721: -- Add the search criteria to query string
1722: IF lengthb(l_contains_str) > 4000 THEN
1723: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1718: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1719: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1720: END LOOP;
1721: -- Add the search criteria to query string
1722: IF lengthb(l_contains_str) > 4000 THEN
1723: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1724: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
1725: FND_MSG_PUB.ADD;
1726: RAISE FND_API.G_EXC_ERROR;
1724: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
1725: FND_MSG_PUB.ADD;
1726: RAISE FND_API.G_EXC_ERROR;
1727: END IF;
1728: x_denorm_str := l_den_contains_str;
1729: RETURN l_contains_str;
1730: END;
1731:
1732: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1725: FND_MSG_PUB.ADD;
1726: RAISE FND_API.G_EXC_ERROR;
1727: END IF;
1728: x_denorm_str := l_den_contains_str;
1729: RETURN l_contains_str;
1730: END;
1731:
1732: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1733: l_contains_str VARCHAR2(32000);
1729: RETURN l_contains_str;
1730: END;
1731:
1732: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1733: l_contains_str VARCHAR2(32000);
1734: l_contains_str_temp VARCHAR2(32000);
1735: l_den_contains_str VARCHAR2(32000);
1736: l_den_contains_str_temp VARCHAR2(32000);
1737: l_filter_str VARCHAR2(4000) := null;
1730: END;
1731:
1732: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1733: l_contains_str VARCHAR2(32000);
1734: l_contains_str_temp VARCHAR2(32000);
1735: l_den_contains_str VARCHAR2(32000);
1736: l_den_contains_str_temp VARCHAR2(32000);
1737: l_filter_str VARCHAR2(4000) := null;
1738: l_prim_temp VARCHAR2(4000) := null;
1731:
1732: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1733: l_contains_str VARCHAR2(32000);
1734: l_contains_str_temp VARCHAR2(32000);
1735: l_den_contains_str VARCHAR2(32000);
1736: l_den_contains_str_temp VARCHAR2(32000);
1737: l_filter_str VARCHAR2(4000) := null;
1738: l_prim_temp VARCHAR2(4000) := null;
1739: l_prim_temp_den VARCHAR2(4000) := null;
1732: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1733: l_contains_str VARCHAR2(32000);
1734: l_contains_str_temp VARCHAR2(32000);
1735: l_den_contains_str VARCHAR2(32000);
1736: l_den_contains_str_temp VARCHAR2(32000);
1737: l_filter_str VARCHAR2(4000) := null;
1738: l_prim_temp VARCHAR2(4000) := null;
1739: l_prim_temp_den VARCHAR2(4000) := null;
1740: TYPE CONTACT_PT_REC_TYPE IS RECORD (
1748: 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);
1749: END IF;
1750: x_denorm_str := NULL;
1751:
1752: -- Dynamic setup of party site contains str
1753: --
1754: -- For each primary transformation add to intermedia query if it
1755: -- is not null
1756: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1753: --
1754: -- For each primary transformation add to intermedia query if it
1755: -- is not null
1756: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1757: l_contains_str_temp := null;
1758: l_den_contains_str_temp := null;
1759: l_filter_str := null;
1760: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1761: if(l_cnt_pt_type.count>0) then
1754: -- For each primary transformation add to intermedia query if it
1755: -- is not null
1756: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1757: l_contains_str_temp := null;
1758: l_den_contains_str_temp := null;
1759: l_filter_str := null;
1760: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1761: if(l_cnt_pt_type.count>0) then
1762: x_modify := 'Y';
1778: l_prim_temp := null;
1779: l_prim_temp_den := null;
1780: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL CTX
1781: g_contact_pt_stage_list(I).TX1,'A44',l_prim_temp);
1782: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1783:
1784: -- Setup query string for EMAIL_ADDRESS
1785: l_prim_temp := null;
1786: l_prim_temp_den := null;
1785: l_prim_temp := null;
1786: l_prim_temp_den := null;
1787: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1788: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1789: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1790:
1791: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1792: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1793: IF N>1 THEN
1787: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1788: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1789: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1790:
1791: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1792: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1793: IF N>1 THEN
1794: distinct_search_cpt_types := N-1;
1795: ELSE
1788: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1789: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1790:
1791: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1792: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1793: IF N>1 THEN
1794: distinct_search_cpt_types := N-1;
1795: ELSE
1796: distinct_search_cpt_types := N;
1799: 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);
1800: END IF;
1801: END LOOP;
1802: -- Add the search criteria to query string
1803: IF lengthb(l_contains_str) > 4000 THEN
1804: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1805: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1806: FND_MSG_PUB.ADD;
1807: RAISE FND_API.G_EXC_ERROR;
1805: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1806: FND_MSG_PUB.ADD;
1807: RAISE FND_API.G_EXC_ERROR;
1808: END IF;
1809: x_denorm_str := l_den_contains_str;
1810: RETURN l_contains_str;
1811: END;
1812:
1813: PROCEDURE open_party_cursor_direct (
1806: FND_MSG_PUB.ADD;
1807: RAISE FND_API.G_EXC_ERROR;
1808: END IF;
1809: x_denorm_str := l_den_contains_str;
1810: RETURN l_contains_str;
1811: END;
1812:
1813: PROCEDURE open_party_cursor_direct (
1814: p_dup_party_id NUMBER,
1814: p_dup_party_id NUMBER,
1815: p_restrict_sql VARCHAR2,
1816: p_match_str VARCHAR2,
1817: p_search_merged VARCHAR2,
1818: p_party_contains_str VARCHAR2,
1819: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1820: l_sqlstr VARCHAR2(4000);
1821: l_search_merged VARCHAR2(1);
1822: BEGIN
1831: END IF;
1832: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1833: 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);
1834: END IF;
1835: 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
1836: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1837: 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);
1838: END IF;
1839: OPEN x_cursor FOR
1882: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1883: l_sqlstr := l_sqlstr || ' (TX36 like :TX36||'' %'' ';
1884: l_sqlstr := l_sqlstr || ' )) ';
1885: END IF;
1886: 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
1887: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1888: 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);
1889: END IF;
1890: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1884: l_sqlstr := l_sqlstr || ' )) ';
1885: END IF;
1886: 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
1887: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1888: 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);
1889: END IF;
1890: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1891: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX10||'' %'' ';
1892: l_sqlstr := l_sqlstr || ' )) ';
1914: END IF;
1915: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1916: 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);
1917: END IF;
1918: 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
1919: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1920: 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);
1921: END IF;
1922: 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
1916: 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);
1917: END IF;
1918: 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
1919: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1920: 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);
1921: END IF;
1922: 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
1923: ,g_party_stage_rec.TX34
1924: ,g_party_stage_rec.TX34
1964: ,g_party_stage_rec.TX47
1965: ,g_party_stage_rec.TX36
1966: ,g_party_stage_rec.TX36
1967: ;
1968: 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
1969: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1970: 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);
1971: END IF;
1972: 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
1966: ,g_party_stage_rec.TX36
1967: ;
1968: 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
1969: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1970: 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);
1971: END IF;
1972: 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
1973: ,g_party_site_stage_list(1).TX10
1974: ,g_party_site_stage_list(1).TX10
1989: END;
1990: PROCEDURE open_party_cursor(
1991: p_dup_party_id NUMBER,
1992: p_restrict_sql VARCHAR2,
1993: p_contains_str VARCHAR2,
1994: p_search_ctx_id NUMBER,
1995: p_match_str VARCHAR2,
1996: p_search_merged VARCHAR2,
1997: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
2008: l_search_merged := 'N';
2009: ELSE
2010: l_search_merged := p_search_merged;
2011: END IF;
2012: IF p_contains_str IS NULL THEN
2013: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2014: 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);
2015: END IF;
2016: OPEN x_cursor FOR
2010: l_search_merged := p_search_merged;
2011: END IF;
2012: IF p_contains_str IS NULL THEN
2013: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2014: 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);
2015: END IF;
2016: OPEN x_cursor FOR
2017: SELECT PARTY_ID , TX34, TX40, TX158, TX39, TX44, TX45, TX46, TX47
2018: FROM HZ_STAGED_PARTIES stage
2027: END IF;
2028: OPEN x_cursor FOR
2029: SELECT PARTY_ID , TX34, TX40, TX158, TX39, TX44, TX45, TX46, TX47
2030: FROM HZ_STAGED_PARTIES stage
2031: WHERE contains( concat_col, p_contains_str)>0
2032: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
2033: AND( (l_search_merged ='Y' )
2034: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
2035: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )
2040: END IF;
2041: OPEN x_cursor FOR
2042: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX34, TX40, TX158, TX39, TX44, TX45, TX46, TX47
2043: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
2044: WHERE contains( concat_col, p_contains_str)>0
2045: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
2046: AND d.party_id = stage.party_id
2047: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
2048: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2063: END IF;
2064: IF p_search_ctx_id IS NULL THEN
2065: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX34, TX40, TX158, TX39, TX44, TX45, TX46, TX47'||
2066: ' FROM HZ_STAGED_PARTIES stage'||
2067: ' WHERE contains( concat_col, :cont)>0'||
2068: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
2069: ' AND ('||p_restrict_sql||')' ||
2070: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2071: IF l_search_merged = 'Y' THEN
2075: ELSE
2076: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
2077: END IF;
2078: output_long_strings('----------------------------------------------------------');
2079: output_long_strings('Party Contains String = '||p_contains_str);
2080: output_long_strings('Restrict Sql = '||p_restrict_sql);
2081: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2082: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2083: ,p_dup_party_id, p_dup_party_id;
2077: END IF;
2078: output_long_strings('----------------------------------------------------------');
2079: output_long_strings('Party Contains String = '||p_contains_str);
2080: output_long_strings('Restrict Sql = '||p_restrict_sql);
2081: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2082: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2083: ,p_dup_party_id, p_dup_party_id;
2084: END IF;
2085: END IF;
2108: PROCEDURE open_party_site_cursor(
2109: p_dup_party_id NUMBER,
2110: p_party_id NUMBER,
2111: p_restrict_sql VARCHAR2,
2112: p_contains_str VARCHAR2,
2113: p_search_ctx_id NUMBER,
2114: p_search_merged VARCHAR2,
2115: p_search_rel_sites VARCHAR2,
2116: p_person_api VARCHAR2,
2140: IF p_search_rel_sites = 'N' THEN
2141: OPEN x_cursor FOR
2142: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22
2143: FROM HZ_STAGED_PARTY_SITES stage
2144: WHERE contains( concat_col, p_contains_str)>0
2145: AND( (l_search_merged ='Y' )
2146: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2147: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2148: AND stage.party_id = p_party_id;
2149: ELSE
2150: OPEN x_cursor FOR
2151: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22
2152: FROM HZ_STAGED_PARTY_SITES stage
2153: WHERE contains( concat_col, p_contains_str)>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 stage.party_id = p_party_id
2157: AND stage.party_id = p_party_id
2158: UNION
2159: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22
2160: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
2161: WHERE contains( concat_col, p_contains_str)>0
2162: AND( (l_search_merged ='Y' )
2163: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2164: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2165: AND r.object_id = p_party_id
2179: END IF;
2180: OPEN x_cursor FOR
2181: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22
2182: FROM HZ_STAGED_PARTY_SITES stage
2183: WHERE contains( concat_col, p_contains_str)>0
2184: AND EXISTS (
2185: SELECT 1 FROM HZ_STAGED_PARTIES p
2186: WHERE p.PARTY_ID = stage.PARTY_ID
2187: AND( (l_search_merged ='Y' )
2199: IF p_person_api = 'Y' THEN
2200: OPEN x_cursor FOR
2201: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22
2202: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
2203: WHERE contains( concat_col, p_contains_str)>0
2204: AND( (l_search_merged ='Y' )
2205: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2206: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2207: AND d.search_context_id = p_search_ctx_id
2209: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2210: UNION
2211: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22
2212: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
2213: WHERE contains( concat_col, p_contains_str)>0
2214: AND( (l_search_merged ='Y' )
2215: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2216: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2217: AND d.search_context_id = p_search_ctx_id
2222: ELSE
2223: OPEN x_cursor FOR
2224: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22
2225: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
2226: WHERE contains( concat_col, p_contains_str)>0
2227: AND( (l_search_merged ='Y' )
2228: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2229: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2230: AND d.search_context_id = p_search_ctx_id
2267: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2268: END IF;
2269: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22'||
2270: ' FROM HZ_STAGED_PARTY_SITES stage'||
2271: ' WHERE contains( concat_col, :cont)>0'||
2272: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2273: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2274: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2275: ' AND (ORG_CONTACT_ID IS NULL ' ||
2277: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2278: ' UNION ' ||
2279: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22'||
2280: ' FROM HZ_STAGED_PARTY_SITES stage'||
2281: ' WHERE contains( concat_col, :cont)>0'||
2282: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2283: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2284: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2285: ' AND ORG_CONTACT_ID IN ' ||
2287: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2288: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2289: ' and ('||p_restrict_sql1|| ') )' ||
2290: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2291: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2292: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2293: ELSE
2294: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22'||
2295: ' FROM HZ_STAGED_PARTY_SITES stage'||
2288: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2289: ' and ('||p_restrict_sql1|| ') )' ||
2290: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2291: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2292: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2293: ELSE
2294: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22'||
2295: ' FROM HZ_STAGED_PARTY_SITES stage'||
2296: ' WHERE contains( concat_col, :cont)>0'||
2292: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2293: ELSE
2294: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22'||
2295: ' FROM HZ_STAGED_PARTY_SITES stage'||
2296: ' WHERE contains( concat_col, :cont)>0'||
2297: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2298: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2299: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2300: ' AND EXISTS ('||
2302: ' WHERE p.party_id = stage.party_id ' ||
2303: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2304: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2305: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2306: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2307: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2308: ,p_dup_party_id, p_dup_party_id;
2309: END IF;
2310: ELSE
2309: END IF;
2310: ELSE
2311: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15, TX22'||
2312: ' FROM HZ_STAGED_PARTY_SITES stage'||
2313: ' WHERE contains( concat_col, :cont)>0'||
2314: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2315: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2316: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2317: ' AND EXISTS ('||
2319: ' WHERE p.party_id = stage.party_id ' ||
2320: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2321: ' AND ('||p_restrict_sql||')' ||
2322: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2323: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2324: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2325: ,p_dup_party_id, p_dup_party_id;
2326: END IF;
2327: END IF;
2325: ,p_dup_party_id, p_dup_party_id;
2326: END IF;
2327: END IF;
2328: output_long_strings('----------------------------------------------------------');
2329: output_long_strings('Party Site Contains String = '||p_contains_str);
2330: output_long_strings('Restrict Sql = '||p_restrict_sql);
2331: exception
2332: when others then
2333: if (instrb(SQLERRM,'DRG-51030')>0) then
2353: PROCEDURE open_contact_cursor(
2354: p_dup_party_id NUMBER,
2355: p_party_id NUMBER,
2356: p_restrict_sql VARCHAR2,
2357: p_contains_str VARCHAR2,
2358: p_search_ctx_id NUMBER,
2359: p_search_merged VARCHAR2,
2360: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
2361: l_sqlstr VARCHAR2(4000);
2381: END IF;
2382: OPEN x_cursor FOR
2383: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID, TX2, TX156, TX6, TX5
2384: FROM HZ_STAGED_CONTACTS stage
2385: WHERE contains( concat_col, p_contains_str)>0
2386: AND EXISTS (
2387: SELECT 1 FROM HZ_STAGED_PARTIES p
2388: WHERE p.PARTY_ID = stage.PARTY_ID
2389: AND( (l_search_merged ='Y' )
2404: END IF;
2405: OPEN x_cursor FOR
2406: SELECT ORG_CONTACT_ID, PARTY_ID, TX2, TX156, TX6, TX5
2407: FROM HZ_STAGED_CONTACTS stage
2408: WHERE contains( concat_col, p_contains_str)>0
2409: AND EXISTS (
2410: SELECT 1 FROM HZ_STAGED_PARTIES p
2411: WHERE p.PARTY_ID = stage.PARTY_ID
2412: AND( (l_search_merged ='Y' )
2423: END IF;
2424: OPEN x_cursor FOR
2425: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID, TX2, TX156, TX6, TX5
2426: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
2427: WHERE contains( concat_col, p_contains_str)>0
2428: AND d.search_context_id = p_search_ctx_id
2429: AND d.party_id = stage.party_id
2430: AND( (l_search_merged ='Y' )
2431: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2457: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
2458: END IF;
2459: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID , TX2, TX156, TX6, TX5'||
2460: ' FROM HZ_STAGED_CONTACTS stage'||
2461: ' WHERE contains( concat_col, :cont)>0'||
2462: ' AND EXISTS ('||
2463: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2464: ' WHERE p.party_id = stage.party_id ' ||
2465: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2467: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2468: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2469: ' AND ('||p_restrict_sql||')' ||
2470: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2471: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2472: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2473: ,p_dup_party_id, p_dup_party_id;
2474: END IF;
2475: output_long_strings('----------------------------------------------------------');
2472: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2473: ,p_dup_party_id, p_dup_party_id;
2474: END IF;
2475: output_long_strings('----------------------------------------------------------');
2476: output_long_strings('Contacts Contains String = '||p_contains_str);
2477: output_long_strings('Restrict Sql = '||p_restrict_sql);
2478: exception
2479: when others then
2480: if (instrb(SQLERRM,'DRG-51030')>0) then
2500: PROCEDURE open_contact_pt_cursor(
2501: p_dup_party_id NUMBER,
2502: p_party_id NUMBER,
2503: p_restrict_sql VARCHAR2,
2504: p_contains_str VARCHAR2,
2505: p_search_ctx_id NUMBER,
2506: p_search_merged VARCHAR2,
2507: p_search_rel_cpts VARCHAR2,
2508: p_person_api VARCHAR2,
2530: THEN
2531: OPEN x_cursor FOR
2532: 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
2533: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2534: WHERE contains( concat_col, p_contains_str)>0
2535: AND d.search_context_id = p_search_ctx_id
2536: AND( (l_search_merged ='Y' )
2537: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2538: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2542: THEN
2543: OPEN x_cursor FOR
2544: 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
2545: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2546: WHERE contains( concat_col, p_contains_str)>0
2547: AND d.search_context_id = p_search_ctx_id
2548: AND( (l_search_merged ='Y' )
2549: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2550: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2559: IF p_search_rel_cpts = 'N' THEN
2560: OPEN x_cursor FOR
2561: 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
2562: FROM HZ_STAGED_CONTACT_POINTS stage
2563: WHERE contains( concat_col, p_contains_str)>0
2564: AND( (l_search_merged ='Y' )
2565: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2566: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2567: AND stage.party_id = p_party_id;
2568: ELSE
2569: OPEN x_cursor FOR
2570: 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
2571: FROM HZ_STAGED_CONTACT_POINTS stage
2572: WHERE contains( concat_col, p_contains_str)>0
2573: AND( (l_search_merged ='Y' )
2574: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2575: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2576: AND stage.party_id = p_party_id
2576: AND stage.party_id = p_party_id
2577: UNION
2578: 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
2579: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2580: WHERE contains( concat_col, p_contains_str)>0
2581: AND r.object_id = p_party_id
2582: AND( (l_search_merged ='Y' )
2583: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2584: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2598: END IF;
2599: OPEN x_cursor FOR
2600: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2601: FROM HZ_STAGED_CONTACT_POINTS stage
2602: WHERE contains( concat_col, p_contains_str)>0
2603: AND EXISTS (
2604: SELECT 1 FROM HZ_STAGED_PARTIES p
2605: WHERE p.PARTY_ID = stage.PARTY_ID
2606: AND( (l_search_merged ='Y' )
2618: IF p_person_api = 'Y' THEN
2619: OPEN x_cursor FOR
2620: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2621: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2622: WHERE contains( concat_col, p_contains_str)>0
2623: AND d.search_context_id = p_search_ctx_id
2624: AND d.party_id = stage.party_id
2625: AND( (l_search_merged ='Y' )
2626: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2628: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2629: UNION
2630: 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
2631: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2632: WHERE contains( concat_col, p_contains_str)>0
2633: AND d.search_context_id = p_search_ctx_id
2634: AND d.party_id = r.subject_id
2635: AND r.relationship_id = oc.party_relationship_id
2636: AND oc.org_contact_id = stage.org_contact_id
2641: ELSE
2642: OPEN x_cursor FOR
2643: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2644: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2645: WHERE contains( concat_col, p_contains_str)>0
2646: AND d.search_context_id = p_search_ctx_id
2647: AND d.party_id = stage.party_id
2648: AND( (l_search_merged ='Y' )
2649: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2686: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2687: END IF;
2688: 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'||
2689: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2690: ' WHERE contains( concat_col, :cont)>0 '||
2691: ' AND (stage.org_contact_id is null '||
2692: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2693: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2694: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2696: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2697: ' UNION ' ||
2698: ' 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'||
2699: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2700: ' WHERE contains( concat_col, :cont)>0 '||
2701: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2702: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2703: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2704: ' AND (stage.org_contact_id in ' ||
2706: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2707: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2708: ' and ('||p_restrict_sql1 || ') ) )' ||
2709: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2710: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2711: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2712: ELSE
2713: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2714: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2707: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2708: ' and ('||p_restrict_sql1 || ') ) )' ||
2709: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2710: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2711: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2712: ELSE
2713: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2714: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2715: ' WHERE contains( concat_col, :cont)>0'||
2711: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2712: ELSE
2713: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2714: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2715: ' WHERE contains( concat_col, :cont)>0'||
2716: ' AND EXISTS ('||
2717: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2718: ' WHERE p.party_id = stage.party_id ' ||
2719: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2721: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2722: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2723: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2724: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2725: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2726: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2727: ,p_dup_party_id, p_dup_party_id;
2728: END IF;
2729: ELSE
2728: END IF;
2729: ELSE
2730: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2731: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2732: ' WHERE contains( concat_col, :cont)>0'||
2733: ' AND EXISTS ('||
2734: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2735: ' WHERE p.party_id = stage.party_id ' ||
2736: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2738: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2739: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2740: ' AND ('||p_restrict_sql||')' ||
2741: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2742: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2743: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2744: ,p_dup_party_id, p_dup_party_id;
2745: END IF;
2746: END IF;
2745: END IF;
2746: END IF;
2747: END IF;
2748: output_long_strings('----------------------------------------------------------');
2749: output_long_strings('Contact Points Contains String = '||p_contains_str);
2750: output_long_strings('Restrict Sql = '||p_restrict_sql);
2751: exception
2752: when others then
2753: if (instrb(SQLERRM,'DRG-51030')>0) then
2866: x_num_matches OUT NUMBER
2867: ) IS
2868:
2869: -- Strings to hold the generated Intermedia query strings
2870: l_party_contains_str VARCHAR2(32000);
2871: l_party_site_contains_str VARCHAR2(32000);
2872: l_contact_contains_str VARCHAR2(32000);
2873: l_contact_pt_contains_str VARCHAR2(32000);
2874: l_denorm_str VARCHAR2(32000);
2867: ) IS
2868:
2869: -- Strings to hold the generated Intermedia query strings
2870: l_party_contains_str VARCHAR2(32000);
2871: l_party_site_contains_str VARCHAR2(32000);
2872: l_contact_contains_str VARCHAR2(32000);
2873: l_contact_pt_contains_str VARCHAR2(32000);
2874: l_denorm_str VARCHAR2(32000);
2875: l_ps_denorm_str VARCHAR2(32000);
2868:
2869: -- Strings to hold the generated Intermedia query strings
2870: l_party_contains_str VARCHAR2(32000);
2871: l_party_site_contains_str VARCHAR2(32000);
2872: l_contact_contains_str VARCHAR2(32000);
2873: l_contact_pt_contains_str VARCHAR2(32000);
2874: l_denorm_str VARCHAR2(32000);
2875: l_ps_denorm_str VARCHAR2(32000);
2876: l_ct_denorm_str VARCHAR2(32000);
2869: -- Strings to hold the generated Intermedia query strings
2870: l_party_contains_str VARCHAR2(32000);
2871: l_party_site_contains_str VARCHAR2(32000);
2872: l_contact_contains_str VARCHAR2(32000);
2873: l_contact_pt_contains_str VARCHAR2(32000);
2874: l_denorm_str VARCHAR2(32000);
2875: l_ps_denorm_str VARCHAR2(32000);
2876: l_ct_denorm_str VARCHAR2(32000);
2877: l_cpt_denorm_str VARCHAR2(32000);
2975: call_type(2) := 'OR';
2976: END;
2977:
2978: /** Private procedure to acquire and score at party level ***/
2979: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2980: l_party_id_idx NUMBER:=1;
2981: l_ctx_id NUMBER;
2982: l_precalc_score BOOLEAN := FALSE;
2983: BEGIN
3006: l_search_ctx_id,H_PARTY_ID(I));
3007: l_party_id_idx := H_PARTY_ID.COUNT+1;
3008: ELSE
3009: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3010: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3011: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3012: 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);
3013: END IF;
3014: 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
3014: 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
3015: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3016: 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);
3017: END IF;
3018: IF (l_party_site_contains_str IS NULL AND
3019: l_contact_contains_str IS NULL AND
3020: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3021: 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);
3022: RAISE L_RETURN_IMM_EXC;
3015: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3016: 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);
3017: END IF;
3018: IF (l_party_site_contains_str IS NULL AND
3019: l_contact_contains_str IS NULL AND
3020: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3021: 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);
3022: RAISE L_RETURN_IMM_EXC;
3023: ELSE
3016: 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);
3017: END IF;
3018: IF (l_party_site_contains_str IS NULL AND
3019: l_contact_contains_str IS NULL AND
3020: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3021: 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);
3022: RAISE L_RETURN_IMM_EXC;
3023: ELSE
3024: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
3020: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3021: 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);
3022: RAISE L_RETURN_IMM_EXC;
3023: ELSE
3024: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
3025: END IF;
3026: ELSE
3027: IF p_index>1 THEN
3028: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3043: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3044: 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);
3045: END IF;
3046: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3047: 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);
3048: END IF;
3049: LOOP
3050: FETCH l_party_cur INTO
3051: l_party_id , l_TX34, l_TX40, l_TX158, l_TX39, l_TX44, l_TX45, l_TX46, l_TX47;
3065: l_party_id_idx:= l_party_id_idx+1;
3066: END IF;
3067: IF (l_party_id_idx-1)>l_max_thresh THEN
3068: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3069: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3070: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3071: AND l_contact_pt_contains_str IS NULL THEN
3072: EXIT;
3073: END IF;
3067: IF (l_party_id_idx-1)>l_max_thresh THEN
3068: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3069: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3070: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3071: AND l_contact_pt_contains_str IS NULL THEN
3072: EXIT;
3073: END IF;
3074: CLOSE l_party_cur;
3075: IF p_index>1 THEN
3096: ROLLBACK to eval_start;
3097: END;
3098:
3099: /** Private procedure to acquire and score at party site level ***/
3100: 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
3101: l_party_id_idx NUMBER:=1;
3102: l_ctx_id NUMBER;
3103: h_ps_id HZ_PARTY_SEARCH.IDList;
3104: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3108: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3109: 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);
3110: 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);
3111: END IF;
3112: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
3113: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
3114: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
3115: l_party_id := H_SCORES.FIRST;
3116: WHILE l_party_id IS NOT NULL LOOP
3109: 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);
3110: 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);
3111: END IF;
3112: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
3113: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
3114: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
3115: l_party_id := H_SCORES.FIRST;
3116: WHILE l_party_id IS NOT NULL LOOP
3117: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
3110: 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);
3111: END IF;
3112: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
3113: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
3114: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
3115: l_party_id := H_SCORES.FIRST;
3116: WHILE l_party_id IS NOT NULL LOOP
3117: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
3118: l_party_id:=H_SCORES.NEXT(l_party_id);
3140: l_search_ctx_id,H_PARTY_ID(I));
3141: l_party_id_idx := H_PARTY_ID.COUNT+1;
3142: ELSE
3143: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3144: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
3145: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3146: 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);
3147: END IF;
3148: IF p_index>1 THEN
3162: END IF;
3163: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3164: 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);
3165: END IF;
3166: 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);
3167: LOOP
3168: FETCH l_party_site_cur INTO
3169: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15, l_TX22;
3170: EXIT WHEN l_party_site_cur%NOTFOUND;
3218: END IF;
3219: END;
3220:
3221: /** Private procedure to acquire and score at contact point level ***/
3222: 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
3223: l_party_id_idx NUMBER:=1;
3224: l_ctx_id NUMBER;
3225: h_ct_id HZ_PARTY_SEARCH.IDList;
3226: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3252: l_search_ctx_id,H_PARTY_ID(I));
3253: l_party_id_idx := H_PARTY_ID.COUNT+1;
3254: ELSE
3255: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3256: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
3257: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3258: 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);
3259: END IF;
3260: IF p_index>1 THEN
3274: END IF;
3275: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3276: 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);
3277: END IF;
3278: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
3279: LOOP
3280: FETCH l_contact_cur INTO
3281: l_org_contact_id, l_ct_party_id , l_TX2, l_TX156, l_TX6, l_TX5;
3282: EXIT WHEN l_contact_cur%NOTFOUND;
3327: END IF;
3328: END;
3329:
3330: /** Private procedure to acquire and score at contact point level ***/
3331: 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
3332: l_party_id_idx NUMBER:=1;
3333: l_ctx_id NUMBER;
3334: h_cpt_id HZ_PARTY_SEARCH.IDList;
3335: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3363: l_search_ctx_id,H_PARTY_ID(I));
3364: l_party_id_idx := H_PARTY_ID.COUNT+1;
3365: ELSE
3366: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3367: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
3368: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3369: 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);
3370: END IF;
3371: IF p_index>1 THEN
3385: END IF;
3386: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3387: 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);
3388: END IF;
3389: 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);
3390: LOOP
3391: FETCH l_contact_pt_cur INTO
3392: 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;
3393: EXIT WHEN l_contact_pt_cur%NOTFOUND;
3566: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3567: l_threshold := round(( l_entered_max_score / 685) * 75);
3568: other_acq_criteria_exists := TRUE ;
3569: --check if acquisition criteria exists for any other entity
3570: IF l_party_contains_str IS NOT NULL THEN
3571: acq_cnt := 1;
3572: END IF;
3573: IF l_party_site_contains_str IS NOT NULL THEN
3574: acq_cnt := acq_cnt+1;
3569: --check if acquisition criteria exists for any other entity
3570: IF l_party_contains_str IS NOT NULL THEN
3571: acq_cnt := 1;
3572: END IF;
3573: IF l_party_site_contains_str IS NOT NULL THEN
3574: acq_cnt := acq_cnt+1;
3575: END IF;
3576: IF l_contact_contains_str IS NOT NULL THEN
3577: acq_cnt := acq_cnt+1;
3572: END IF;
3573: IF l_party_site_contains_str IS NOT NULL THEN
3574: acq_cnt := acq_cnt+1;
3575: END IF;
3576: IF l_contact_contains_str IS NOT NULL THEN
3577: acq_cnt := acq_cnt+1;
3578: END IF;
3579: IF l_contact_pt_contains_str IS NOT NULL THEN
3580: acq_cnt := acq_cnt+1;
3575: END IF;
3576: IF l_contact_contains_str IS NOT NULL THEN
3577: acq_cnt := acq_cnt+1;
3578: END IF;
3579: IF l_contact_pt_contains_str IS NOT NULL THEN
3580: acq_cnt := acq_cnt+1;
3581: END IF;
3582: IF acq_cnt>1 THEN
3583: other_acq_criteria_exists := TRUE;
3609: FND_MSG_PUB.ADD;
3610: RAISE FND_API.G_EXC_ERROR;
3611: END IF;
3612: END IF;
3613: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3614: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3615: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3616: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3617: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3610: RAISE FND_API.G_EXC_ERROR;
3611: END IF;
3612: END IF;
3613: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3614: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3615: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3616: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3617: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3618: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3611: END IF;
3612: END IF;
3613: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3614: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3615: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3616: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3617: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3618: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3619: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3612: END IF;
3613: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3614: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3615: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3616: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3617: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3618: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3619: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3620: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3613: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3614: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3615: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3616: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3617: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3618: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3619: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3620: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3621: END IF;
3614: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3615: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3616: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3617: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3618: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3619: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3620: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3621: END IF;
3622: END;
3615: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3616: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3617: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3618: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3619: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3620: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3621: END IF;
3622: END;
3623: /************ End of find_parties private procedures **********/
3616: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3617: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3618: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3619: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3620: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3621: END IF;
3622: END;
3623: /************ End of find_parties private procedures **********/
3624:
3688: 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);
3689: END IF;
3690: -- ************************************************************
3691: -- STEP 2. Setup of intermedia query strings for Acquisition query
3692: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3693: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3694: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3695: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3696: 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);
3689: END IF;
3690: -- ************************************************************
3691: -- STEP 2. Setup of intermedia query strings for Acquisition query
3692: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3693: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3694: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3695: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3696: 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);
3697: END IF;
3690: -- ************************************************************
3691: -- STEP 2. Setup of intermedia query strings for Acquisition query
3692: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3693: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3694: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3695: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3696: 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);
3697: END IF;
3698: l_denorm_max_score:=0;
3722: END IF;
3723: ELSE
3724: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3725: END IF;
3726: 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((75/685)*l_entered_max_score));
3727: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3728:
3729: -- Setup Search Context ID
3730: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3732:
3733: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3734: 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);
3735: END IF;
3736: IF l_party_contains_str IS NULL THEN
3737: defpt := 1;
3738: END IF;
3739: IF l_party_site_contains_str IS NULL THEN
3740: defps := 1;
3735: END IF;
3736: IF l_party_contains_str IS NULL THEN
3737: defpt := 1;
3738: END IF;
3739: IF l_party_site_contains_str IS NULL THEN
3740: defps := 1;
3741: END IF;
3742: IF l_contact_contains_str IS NULL THEN
3743: defct := 1;
3738: END IF;
3739: IF l_party_site_contains_str IS NULL THEN
3740: defps := 1;
3741: END IF;
3742: IF l_contact_contains_str IS NULL THEN
3743: defct := 1;
3744: END IF;
3745: IF l_contact_pt_contains_str IS NULL THEN
3746: defcpt := 1;
3741: END IF;
3742: IF l_contact_contains_str IS NULL THEN
3743: defct := 1;
3744: END IF;
3745: IF l_contact_pt_contains_str IS NULL THEN
3746: defcpt := 1;
3747: END IF;
3748:
3749: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3750: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3751: 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);
3752: END IF;
3753: 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);
3754: 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);
3755: 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);
3756: 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);
3757: 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);
3758: 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);
3751: 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);
3752: END IF;
3753: 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);
3754: 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);
3755: 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);
3756: 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);
3757: 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);
3758: 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);
3759: END IF;
3752: END IF;
3753: 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);
3754: 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);
3755: 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);
3756: 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);
3757: 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);
3758: 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);
3759: END IF;
3760: FOR I in 1..5 LOOP
3753: 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);
3754: 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);
3755: 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);
3756: 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);
3757: 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);
3758: 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);
3759: END IF;
3760: FOR I in 1..5 LOOP
3761: do_eval(I);
3835: x_num_matches OUT NUMBER
3836: ) IS
3837:
3838: -- Strings to hold the generated Intermedia query strings
3839: l_party_contains_str VARCHAR2(32000);
3840: l_party_site_contains_str VARCHAR2(32000);
3841: l_contact_contains_str VARCHAR2(32000);
3842: l_contact_pt_contains_str VARCHAR2(32000);
3843: l_denorm_str VARCHAR2(32000);
3836: ) IS
3837:
3838: -- Strings to hold the generated Intermedia query strings
3839: l_party_contains_str VARCHAR2(32000);
3840: l_party_site_contains_str VARCHAR2(32000);
3841: l_contact_contains_str VARCHAR2(32000);
3842: l_contact_pt_contains_str VARCHAR2(32000);
3843: l_denorm_str VARCHAR2(32000);
3844: l_ps_denorm_str VARCHAR2(32000);
3837:
3838: -- Strings to hold the generated Intermedia query strings
3839: l_party_contains_str VARCHAR2(32000);
3840: l_party_site_contains_str VARCHAR2(32000);
3841: l_contact_contains_str VARCHAR2(32000);
3842: l_contact_pt_contains_str VARCHAR2(32000);
3843: l_denorm_str VARCHAR2(32000);
3844: l_ps_denorm_str VARCHAR2(32000);
3845: l_ct_denorm_str VARCHAR2(32000);
3838: -- Strings to hold the generated Intermedia query strings
3839: l_party_contains_str VARCHAR2(32000);
3840: l_party_site_contains_str VARCHAR2(32000);
3841: l_contact_contains_str VARCHAR2(32000);
3842: l_contact_pt_contains_str VARCHAR2(32000);
3843: l_denorm_str VARCHAR2(32000);
3844: l_ps_denorm_str VARCHAR2(32000);
3845: l_ct_denorm_str VARCHAR2(32000);
3846: l_cpt_denorm_str VARCHAR2(32000);
3974: call_type(2) := 'OR';
3975: END;
3976:
3977: /** Private procedure to acquire and score at party level ***/
3978: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3979: l_party_id_idx NUMBER:=1;
3980: l_ctx_id NUMBER;
3981: BEGIN
3982: SAVEPOINT eval_start;
3995: l_search_ctx_id,H_PARTY_ID(I));
3996: l_party_id_idx := H_PARTY_ID.COUNT+1;
3997: ELSE
3998: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3999: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
4000: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4001: 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);
4002: END IF;
4003: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
4003: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
4004: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4005: 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);
4006: END IF;
4007: IF (l_party_site_contains_str IS NULL AND
4008: l_contact_contains_str IS NULL AND
4009: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4010: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4011: RAISE L_RETURN_IMM_EXC;
4004: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4005: 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);
4006: END IF;
4007: IF (l_party_site_contains_str IS NULL AND
4008: l_contact_contains_str IS NULL AND
4009: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4010: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4011: RAISE L_RETURN_IMM_EXC;
4012: ELSE
4005: 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);
4006: END IF;
4007: IF (l_party_site_contains_str IS NULL AND
4008: l_contact_contains_str IS NULL AND
4009: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4010: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4011: RAISE L_RETURN_IMM_EXC;
4012: ELSE
4013: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
4009: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4010: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4011: RAISE L_RETURN_IMM_EXC;
4012: ELSE
4013: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
4014: END IF;
4015: ELSE
4016: IF p_index>1 THEN
4017: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4029: l_ctx_id := NULL;
4030: l_party_id_idx := H_PARTY_ID.COUNT+1;
4031: END IF;
4032: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
4033: 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);
4034: END IF;
4035: LOOP
4036: FETCH l_party_cur INTO
4037: l_party_id , l_TX34, l_TX40, l_TX158, l_TX39, l_TX44, l_TX45, l_TX46, l_TX47;
4051: l_party_id_idx:= l_party_id_idx+1;
4052: END IF;
4053: IF (l_party_id_idx-1)>l_max_thresh THEN
4054: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
4055: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
4056: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
4057: AND l_contact_pt_contains_str IS NULL THEN
4058: H_PARTY_ID.DELETE(l_party_id_idx-1);
4059: H_PARTY_ID_LIST.DELETE(l_index);
4053: IF (l_party_id_idx-1)>l_max_thresh THEN
4054: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
4055: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
4056: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
4057: AND l_contact_pt_contains_str IS NULL THEN
4058: H_PARTY_ID.DELETE(l_party_id_idx-1);
4059: H_PARTY_ID_LIST.DELETE(l_index);
4060: H_SCORES.DELETE(l_index);
4061: EXIT;
4082: CLOSE l_party_cur;
4083: ROLLBACK to eval_start;
4084: END;
4085: PROCEDURE open_person_contact_cursor(
4086: p_contains_str VARCHAR2,
4087: p_search_ctx_id NUMBER,
4088: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
4089: BEGIN
4090: OPEN x_cursor FOR
4089: BEGIN
4090: OPEN x_cursor FOR
4091: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID, TX2, TX156, TX6, TX5
4092: FROM HZ_STAGED_CONTACTS stage
4093: WHERE contains( concat_col, p_contains_str)>0
4094: AND ORG_CONTACT_ID in (
4095: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
4096: org_contact_id
4097: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc
4113: end if;
4114: END;
4115:
4116: /** Private procedure to acquire and score at party site level ***/
4117: 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
4118: l_party_id_idx NUMBER:=1;
4119: l_ctx_id NUMBER;
4120: h_ps_id HZ_PARTY_SEARCH.IDList;
4121: h_ps_party_id HZ_PARTY_SEARCH.IDList;
4141: ELSE
4142: l_party_id_idx := H_PARTY_ID.COUNT+1;
4143: l_ctx_id := NULL;
4144: END IF;
4145: 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);
4146: LOOP
4147: FETCH l_party_site_cur INTO
4148: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15, l_TX22;
4149: EXIT WHEN l_party_site_cur%NOTFOUND;
4212: END IF;
4213: END;
4214:
4215: /** Private procedure to acquire and score at party site level ***/
4216: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
4217: l_party_id_idx NUMBER:=1;
4218: l_ctx_id NUMBER;
4219: h_ct_id HZ_PARTY_SEARCH.IDList;
4220: h_ct_party_id HZ_PARTY_SEARCH.IDList;
4228: FORALL I in 1..H_PARTY_ID.COUNT
4229: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
4230: l_search_ctx_id,H_PARTY_ID(I));
4231: l_party_id_idx := H_PARTY_ID.COUNT+1;
4232: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
4233: LOOP
4234: FETCH l_contact_cur INTO
4235: l_org_contact_id, l_ct_party_id , l_TX2, l_TX156, l_TX6, l_TX5;
4236: EXIT WHEN l_contact_cur%NOTFOUND;
4270: END IF;
4271: END;
4272:
4273: /** Private procedure to acquire and score at contact point level ***/
4274: 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
4275: l_party_id_idx NUMBER:=1;
4276: l_ctx_id NUMBER;
4277: h_cpt_id HZ_PARTY_SEARCH.IDList;
4278: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
4315: END IF;
4316: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4317: 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);
4318: END IF;
4319: 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);
4320: LOOP
4321: FETCH l_contact_pt_cur INTO
4322: 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;
4323: EXIT WHEN l_contact_pt_cur%NOTFOUND;
4518: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
4519: l_threshold := round(( l_entered_max_score / 685) * 75);
4520: other_acq_criteria_exists := TRUE ;
4521: --check if acquisition criteria exists for any other entity
4522: IF l_party_contains_str IS NOT NULL THEN
4523: acq_cnt := 1;
4524: END IF;
4525: IF l_party_site_contains_str IS NOT NULL THEN
4526: acq_cnt := acq_cnt+1;
4521: --check if acquisition criteria exists for any other entity
4522: IF l_party_contains_str IS NOT NULL THEN
4523: acq_cnt := 1;
4524: END IF;
4525: IF l_party_site_contains_str IS NOT NULL THEN
4526: acq_cnt := acq_cnt+1;
4527: END IF;
4528: IF l_contact_contains_str IS NOT NULL THEN
4529: acq_cnt := acq_cnt+1;
4524: END IF;
4525: IF l_party_site_contains_str IS NOT NULL THEN
4526: acq_cnt := acq_cnt+1;
4527: END IF;
4528: IF l_contact_contains_str IS NOT NULL THEN
4529: acq_cnt := acq_cnt+1;
4530: END IF;
4531: IF l_contact_pt_contains_str IS NOT NULL THEN
4532: acq_cnt := acq_cnt+1;
4527: END IF;
4528: IF l_contact_contains_str IS NOT NULL THEN
4529: acq_cnt := acq_cnt+1;
4530: END IF;
4531: IF l_contact_pt_contains_str IS NOT NULL THEN
4532: acq_cnt := acq_cnt+1;
4533: END IF;
4534: IF acq_cnt>1 THEN
4535: other_acq_criteria_exists := TRUE;
4561: FND_MSG_PUB.ADD;
4562: RAISE FND_API.G_EXC_ERROR;
4563: END IF;
4564: END IF;
4565: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4566: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4567: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4568: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4569: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4562: RAISE FND_API.G_EXC_ERROR;
4563: END IF;
4564: END IF;
4565: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4566: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4567: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4568: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4569: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4570: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4563: END IF;
4564: END IF;
4565: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4566: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4567: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4568: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4569: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4570: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4571: END IF;
4564: END IF;
4565: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4566: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4567: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4568: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4569: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4570: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4571: END IF;
4572: END;
4565: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4566: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4567: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4568: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4569: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4570: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4571: END IF;
4572: END;
4573: /************ End of find_persons private procedures **********/
4566: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4567: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4568: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4569: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4570: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4571: END IF;
4572: END;
4573: /************ End of find_persons private procedures **********/
4574:
4601: g_score_until_thresh := false;
4602: END IF;
4603: -- ************************************************************
4604: -- STEP 2. Setup of intermedia query strings for Acquisition query
4605: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4606: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4607: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4608: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4609: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4602: END IF;
4603: -- ************************************************************
4604: -- STEP 2. Setup of intermedia query strings for Acquisition query
4605: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4606: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4607: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4608: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4609: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4610:
4603: -- ************************************************************
4604: -- STEP 2. Setup of intermedia query strings for Acquisition query
4605: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4606: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4607: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4608: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4609: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4610:
4611: -- Setup Search Context ID
4604: -- STEP 2. Setup of intermedia query strings for Acquisition query
4605: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4606: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4607: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4608: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4609: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4610:
4611: -- Setup Search Context ID
4612: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4611: -- Setup Search Context ID
4612: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4613: x_search_ctx_id := l_search_ctx_id;
4614:
4615: IF l_party_contains_str IS NULL THEN
4616: defpt := 1;
4617: END IF;
4618: IF l_party_site_contains_str IS NULL THEN
4619: defps := 1;
4614:
4615: IF l_party_contains_str IS NULL THEN
4616: defpt := 1;
4617: END IF;
4618: IF l_party_site_contains_str IS NULL THEN
4619: defps := 1;
4620: END IF;
4621: IF l_contact_contains_str IS NULL THEN
4622: defct := 1;
4617: END IF;
4618: IF l_party_site_contains_str IS NULL THEN
4619: defps := 1;
4620: END IF;
4621: IF l_contact_contains_str IS NULL THEN
4622: defct := 1;
4623: END IF;
4624: IF l_contact_pt_contains_str IS NULL THEN
4625: defcpt := 1;
4620: END IF;
4621: IF l_contact_contains_str IS NULL THEN
4622: defct := 1;
4623: END IF;
4624: IF l_contact_pt_contains_str IS NULL THEN
4625: defcpt := 1;
4626: END IF;
4627:
4628: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4626: END IF;
4627:
4628: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4629: 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);
4630: 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);
4631: 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);
4632: 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);
4633: 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);
4634: 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);
4627:
4628: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4629: 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);
4630: 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);
4631: 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);
4632: 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);
4633: 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);
4634: 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);
4635: END IF;
4628: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4629: 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);
4630: 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);
4631: 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);
4632: 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);
4633: 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);
4634: 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);
4635: END IF;
4636: FOR I in 1..5 LOOP
4629: 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);
4630: 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);
4631: 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);
4632: 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);
4633: 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);
4634: 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);
4635: END IF;
4636: FOR I in 1..5 LOOP
4637: do_eval(I);
4635: END IF;
4636: FOR I in 1..5 LOOP
4637: do_eval(I);
4638: END LOOP;
4639: IF l_contact_contains_str IS NOT NULL THEN
4640: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4641: END IF;
4642: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4643: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 11 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4636: FOR I in 1..5 LOOP
4637: do_eval(I);
4638: END LOOP;
4639: IF l_contact_contains_str IS NOT NULL THEN
4640: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4641: END IF;
4642: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4643: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 11 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4644: END IF;
5501: x_num_matches OUT NUMBER
5502: ) IS
5503:
5504: -- Strings to hold the generated Intermedia query strings
5505: l_party_contains_str VARCHAR2(32000);
5506: l_match_idx NUMBER;
5507: l_party_site_contains_str VARCHAR2(32000);
5508: l_contact_contains_str VARCHAR2(32000);
5509: l_contact_pt_contains_str VARCHAR2(32000);
5503:
5504: -- Strings to hold the generated Intermedia query strings
5505: l_party_contains_str VARCHAR2(32000);
5506: l_match_idx NUMBER;
5507: l_party_site_contains_str VARCHAR2(32000);
5508: l_contact_contains_str VARCHAR2(32000);
5509: l_contact_pt_contains_str VARCHAR2(32000);
5510: l_tmp VARCHAR2(32000);
5511:
5504: -- Strings to hold the generated Intermedia query strings
5505: l_party_contains_str VARCHAR2(32000);
5506: l_match_idx NUMBER;
5507: l_party_site_contains_str VARCHAR2(32000);
5508: l_contact_contains_str VARCHAR2(32000);
5509: l_contact_pt_contains_str VARCHAR2(32000);
5510: l_tmp VARCHAR2(32000);
5511:
5512: -- Other local variables
5505: l_party_contains_str VARCHAR2(32000);
5506: l_match_idx NUMBER;
5507: l_party_site_contains_str VARCHAR2(32000);
5508: l_contact_contains_str VARCHAR2(32000);
5509: l_contact_pt_contains_str VARCHAR2(32000);
5510: l_tmp VARCHAR2(32000);
5511:
5512: -- Other local variables
5513: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5589: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5590:
5591: -- ************************************************************
5592: -- STEP 2. Setup of intermedia query strings for Acquisition query
5593: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5594: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5595: 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);
5596:
5597: -- Setup Search Context ID
5590:
5591: -- ************************************************************
5592: -- STEP 2. Setup of intermedia query strings for Acquisition query
5593: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5594: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5595: 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);
5596:
5597: -- Setup Search Context ID
5598: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5599: x_search_ctx_id := l_search_ctx_id;
5600:
5601: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5602: 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);
5603: 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);
5604: 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);
5605: 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);
5606: END IF;
5607: IF l_party_site_contains_str IS NULL THEN
5600:
5601: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5602: 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);
5603: 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);
5604: 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);
5605: 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);
5606: END IF;
5607: IF l_party_site_contains_str IS NULL THEN
5608: defps := 1;
5603: 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);
5604: 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);
5605: 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);
5606: END IF;
5607: IF l_party_site_contains_str IS NULL THEN
5608: defps := 1;
5609: END IF;
5610: IF l_contact_pt_contains_str IS NULL THEN
5611: defcpt := 1;
5606: END IF;
5607: IF l_party_site_contains_str IS NULL THEN
5608: defps := 1;
5609: END IF;
5610: IF l_contact_pt_contains_str IS NULL THEN
5611: defcpt := 1;
5612: END IF;
5613:
5614: IF l_party_site_contains_str IS NOT NULL THEN
5610: IF l_contact_pt_contains_str IS NULL THEN
5611: defcpt := 1;
5612: END IF;
5613:
5614: IF l_party_site_contains_str IS NOT NULL THEN
5615: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5616: LOOP
5617: FETCH l_party_site_cur INTO
5618: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15, l_TX22;
5611: defcpt := 1;
5612: END IF;
5613:
5614: IF l_party_site_contains_str IS NOT NULL THEN
5615: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5616: LOOP
5617: FETCH l_party_site_cur INTO
5618: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15, l_TX22;
5619: EXIT WHEN l_party_site_cur%NOTFOUND;
5634: END LOOP;
5635: CLOSE l_party_site_cur;
5636: END IF;
5637:
5638: IF l_contact_pt_contains_str IS NOT NULL THEN
5639: SAVEPOINT eval_start;
5640: IF l_match_str = ' AND ' OR (
5641: ((l_cpt_max_score/l_entered_max_score)<(75/685))
5642: ) THEN
5642: ) THEN
5643: FORALL I in 1..l_match_ps_list.COUNT
5644: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5645: values (l_search_ctx_id,l_match_ps_list(I));
5646: 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');
5647: ELSE
5648: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5649: END IF;
5650: LOOP
5644: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5645: values (l_search_ctx_id,l_match_ps_list(I));
5646: 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');
5647: ELSE
5648: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5649: END IF;
5650: LOOP
5651: FETCH l_contact_pt_cur INTO
5652: 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;
5744: x_num_matches OUT NUMBER
5745: ) IS
5746:
5747: -- Strings to hold the generated Intermedia query strings
5748: l_party_contains_str VARCHAR2(32000);
5749: l_party_site_contains_str VARCHAR2(32000);
5750: l_contact_contains_str VARCHAR2(32000);
5751: l_contact_pt_contains_str VARCHAR2(32000);
5752: l_tmp VARCHAR2(32000);
5745: ) IS
5746:
5747: -- Strings to hold the generated Intermedia query strings
5748: l_party_contains_str VARCHAR2(32000);
5749: l_party_site_contains_str VARCHAR2(32000);
5750: l_contact_contains_str VARCHAR2(32000);
5751: l_contact_pt_contains_str VARCHAR2(32000);
5752: l_tmp VARCHAR2(32000);
5753:
5746:
5747: -- Strings to hold the generated Intermedia query strings
5748: l_party_contains_str VARCHAR2(32000);
5749: l_party_site_contains_str VARCHAR2(32000);
5750: l_contact_contains_str VARCHAR2(32000);
5751: l_contact_pt_contains_str VARCHAR2(32000);
5752: l_tmp VARCHAR2(32000);
5753:
5754: -- Other local variables
5747: -- Strings to hold the generated Intermedia query strings
5748: l_party_contains_str VARCHAR2(32000);
5749: l_party_site_contains_str VARCHAR2(32000);
5750: l_contact_contains_str VARCHAR2(32000);
5751: l_contact_pt_contains_str VARCHAR2(32000);
5752: l_tmp VARCHAR2(32000);
5753:
5754: -- Other local variables
5755: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5823: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5824:
5825: -- ************************************************************
5826: -- STEP 2. Setup of intermedia query strings for Acquisition query
5827: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5828: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5829: 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);
5830:
5831: -- Setup Search Context ID
5824:
5825: -- ************************************************************
5826: -- STEP 2. Setup of intermedia query strings for Acquisition query
5827: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5828: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5829: 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);
5830:
5831: -- Setup Search Context ID
5832: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5833: x_search_ctx_id := l_search_ctx_id;
5834:
5835: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5836: 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);
5837: 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);
5838: 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);
5839: 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);
5840: END IF;
5841: IF l_contact_contains_str IS NULL THEN
5834:
5835: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5836: 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);
5837: 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);
5838: 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);
5839: 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);
5840: END IF;
5841: IF l_contact_contains_str IS NULL THEN
5842: defct := 1;
5837: 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);
5838: 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);
5839: 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);
5840: END IF;
5841: IF l_contact_contains_str IS NULL THEN
5842: defct := 1;
5843: END IF;
5844: IF l_contact_pt_contains_str IS NULL THEN
5845: defcpt := 1;
5840: END IF;
5841: IF l_contact_contains_str IS NULL THEN
5842: defct := 1;
5843: END IF;
5844: IF l_contact_pt_contains_str IS NULL THEN
5845: defcpt := 1;
5846: END IF;
5847:
5848: IF l_contact_contains_str IS NOT NULL THEN
5844: IF l_contact_pt_contains_str IS NULL THEN
5845: defcpt := 1;
5846: END IF;
5847:
5848: IF l_contact_contains_str IS NOT NULL THEN
5849: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5850: LOOP
5851: FETCH l_contact_cur INTO
5852: l_org_contact_id, l_ct_party_id , l_TX2, l_TX156, l_TX6, l_TX5;
5845: defcpt := 1;
5846: END IF;
5847:
5848: IF l_contact_contains_str IS NOT NULL THEN
5849: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5850: LOOP
5851: FETCH l_contact_cur INTO
5852: l_org_contact_id, l_ct_party_id , l_TX2, l_TX156, l_TX6, l_TX5;
5853: EXIT WHEN l_contact_cur%NOTFOUND;
5866: END LOOP;
5867: CLOSE l_contact_cur;
5868: END IF;
5869:
5870: IF l_contact_pt_contains_str IS NOT NULL THEN
5871: SAVEPOINT eval_start;
5872: IF l_match_str = ' AND ' OR (
5873: ((l_cpt_max_score/l_entered_max_score)<(75/685))
5874: ) THEN
5874: ) THEN
5875: FORALL I in 1..l_match_contact_list.COUNT
5876: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5877: values (l_search_ctx_id,l_match_contact_list(I));
5878: 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');
5879: ELSE
5880: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5881: END IF;
5882: LOOP
5876: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5877: values (l_search_ctx_id,l_match_contact_list(I));
5878: 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');
5879: ELSE
5880: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5881: END IF;
5882: LOOP
5883: FETCH l_contact_pt_cur INTO
5884: 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;
5974: ) IS
5975:
5976:
5977: -- Strings to hold the generated Intermedia query strings
5978: l_contact_pt_contains_str VARCHAR2(32000);
5979: l_tmp VARCHAR2(32000);
5980:
5981: -- Other local variables
5982: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6035: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6036:
6037: -- ************************************************************
6038: -- STEP 2. Setup of intermedia query strings for Acquisition query
6039: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6040: 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);
6041:
6042: -- Setup Search Context ID
6043: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
6043: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
6044: x_search_ctx_id := l_search_ctx_id;
6045: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6046: 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);
6047: 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);
6048: 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);
6049: END IF;
6050:
6051: IF l_contact_pt_contains_str IS NOT NULL THEN
6047: 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);
6048: 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);
6049: END IF;
6050:
6051: IF l_contact_pt_contains_str IS NOT NULL THEN
6052: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6053: cnt := 1;
6054: LOOP
6055: FETCH l_contact_pt_cur INTO
6048: 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);
6049: END IF;
6050:
6051: IF l_contact_pt_contains_str IS NOT NULL THEN
6052: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6053: cnt := 1;
6054: LOOP
6055: FETCH l_contact_pt_cur INTO
6056: 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;
6119: x_search_ctx_id IN OUT NUMBER
6120: ) IS
6121:
6122: -- Strings to hold the generated Intermedia query strings
6123: l_party_contains_str VARCHAR2(32000);
6124: l_party_site_contains_str VARCHAR2(32000);
6125: l_contact_contains_str VARCHAR2(32000);
6126: l_contact_pt_contains_str VARCHAR2(32000);
6127: l_tmp VARCHAR2(32000);
6120: ) IS
6121:
6122: -- Strings to hold the generated Intermedia query strings
6123: l_party_contains_str VARCHAR2(32000);
6124: l_party_site_contains_str VARCHAR2(32000);
6125: l_contact_contains_str VARCHAR2(32000);
6126: l_contact_pt_contains_str VARCHAR2(32000);
6127: l_tmp VARCHAR2(32000);
6128:
6121:
6122: -- Strings to hold the generated Intermedia query strings
6123: l_party_contains_str VARCHAR2(32000);
6124: l_party_site_contains_str VARCHAR2(32000);
6125: l_contact_contains_str VARCHAR2(32000);
6126: l_contact_pt_contains_str VARCHAR2(32000);
6127: l_tmp VARCHAR2(32000);
6128:
6129: -- Other local variables
6122: -- Strings to hold the generated Intermedia query strings
6123: l_party_contains_str VARCHAR2(32000);
6124: l_party_site_contains_str VARCHAR2(32000);
6125: l_contact_contains_str VARCHAR2(32000);
6126: l_contact_pt_contains_str VARCHAR2(32000);
6127: l_tmp VARCHAR2(32000);
6128:
6129: -- Other local variables
6130: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6221: g_score_until_thresh := false;
6222: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6223: -- ************************************************************
6224: -- STEP 2. Setup of intermedia query strings for Acquisition query
6225: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6226: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6227: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6228: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6229:
6222: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6223: -- ************************************************************
6224: -- STEP 2. Setup of intermedia query strings for Acquisition query
6225: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6226: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6227: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6228: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6229:
6230: -- Setup Search Context ID
6223: -- ************************************************************
6224: -- STEP 2. Setup of intermedia query strings for Acquisition query
6225: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6226: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6227: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6228: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6229:
6230: -- Setup Search Context ID
6231: IF x_search_ctx_id IS NULL THEN
6243: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX34,l_TX40,l_TX158,l_TX39,l_TX44,l_TX45,l_TX46,l_TX47,1);
6244: END LOOP;
6245: CLOSE l_party_cur;
6246:
6247: IF l_party_site_contains_str IS NOT NULL THEN
6248: l_max_score := 0;
6249: l_max_id := 0;
6250: l_max_idx := 0;
6251: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6248: l_max_score := 0;
6249: l_max_id := 0;
6250: l_max_idx := 0;
6251: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6252: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
6253: ELSE
6254: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
6255: END IF;
6256: LOOP
6250: l_max_idx := 0;
6251: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6252: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
6253: ELSE
6254: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
6255: END IF;
6256: LOOP
6257: FETCH l_party_site_cur INTO
6258: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15, l_TX22;
6277: INSERT_PARTY_SITES_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_party_site_list(l_max_idx), g_party_site_stage_list(l_max_idx) ,l_max_TX3,l_max_TX4,l_max_TX9,l_max_TX10,l_max_TX11,l_max_TX14,l_max_TX15,l_max_TX22,l_max_idx);
6278: END IF;
6279: END IF;
6280:
6281: IF l_contact_contains_str IS NOT NULL THEN
6282: l_max_score := 0;
6283: l_max_id := 0;
6284: l_max_idx := 0;
6285: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
6281: IF l_contact_contains_str IS NOT NULL THEN
6282: l_max_score := 0;
6283: l_max_id := 0;
6284: l_max_idx := 0;
6285: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
6286: LOOP
6287: FETCH l_contact_cur INTO
6288: l_org_contact_id, l_ct_party_id , l_TX2, l_TX156, l_TX6, l_TX5;
6289: EXIT WHEN l_contact_cur%NOTFOUND;
6303: 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_TX2,l_max_TX156,l_max_TX6,l_max_TX5,l_max_idx);
6304: END IF;
6305: END IF;
6306:
6307: IF l_contact_pt_contains_str IS NOT NULL THEN
6308: l_max_score := 0;
6309: l_max_id := 0;
6310: l_max_idx := 0;
6311: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6308: l_max_score := 0;
6309: l_max_id := 0;
6310: l_max_idx := 0;
6311: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6312: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
6313: ELSE
6314: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
6315: END IF;
6316: LOOP
6310: l_max_idx := 0;
6311: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6312: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
6313: ELSE
6314: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
6315: END IF;
6316: LOOP
6317: FETCH l_contact_pt_cur INTO
6318: 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;