[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:
1698: p_party_max_score NUMBER,
1699: p_denorm_max_score NUMBER,
1700: p_non_denorm_max_score NUMBER,
1701: p_threshold NUMBER) RETURN VARCHAR2 IS
1702: l_party_contains_str VARCHAR2(32000);
1703: l_party_filter VARCHAR2(1) := null;
1704: l_prim_temp VARCHAR2(4000);
1705: l_denorm_str VARCHAR2(4000);
1706: BEGIN
1742:
1743: IF l_denorm_str IS NOT NULL THEN
1744: IF (p_non_denorm_max_score+10)>=p_threshold THEN
1745: l_denorm_str := NULL;
1746: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1747: ELSIF (p_non_denorm_max_score+p_denorm_max_score+10)>=p_threshold THEN
1748: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1749: l_denorm_str := NULL;
1750: END IF;
1744: IF (p_non_denorm_max_score+10)>=p_threshold THEN
1745: l_denorm_str := NULL;
1746: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1747: ELSIF (p_non_denorm_max_score+p_denorm_max_score+10)>=p_threshold THEN
1748: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1749: l_denorm_str := NULL;
1750: END IF;
1751: ELSE
1752: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1748: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1749: l_denorm_str := NULL;
1750: END IF;
1751: ELSE
1752: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1753: END IF;
1754:
1755: -- Setup query string for CATEGORY_CODE
1756: l_prim_temp := null;
1759:
1760: IF l_denorm_str IS NOT NULL THEN
1761: IF (p_non_denorm_max_score+10)>=p_threshold THEN
1762: l_denorm_str := NULL;
1763: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1764: ELSIF (p_non_denorm_max_score+p_denorm_max_score+10)>=p_threshold THEN
1765: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1766: l_denorm_str := NULL;
1767: END IF;
1761: IF (p_non_denorm_max_score+10)>=p_threshold THEN
1762: l_denorm_str := NULL;
1763: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1764: ELSIF (p_non_denorm_max_score+p_denorm_max_score+10)>=p_threshold THEN
1765: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1766: l_denorm_str := NULL;
1767: END IF;
1768: ELSE
1769: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1765: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1766: l_denorm_str := NULL;
1767: END IF;
1768: ELSE
1769: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1770: END IF;
1771:
1772: -- Setup query string for REFERENCE_USE_FLAG
1773: l_prim_temp := null;
1776:
1777: IF l_denorm_str IS NOT NULL THEN
1778: IF (p_non_denorm_max_score+20)>=p_threshold THEN
1779: l_denorm_str := NULL;
1780: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1781: ELSIF (p_non_denorm_max_score+p_denorm_max_score+20)>=p_threshold THEN
1782: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1783: l_denorm_str := NULL;
1784: END IF;
1778: IF (p_non_denorm_max_score+20)>=p_threshold THEN
1779: l_denorm_str := NULL;
1780: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1781: ELSIF (p_non_denorm_max_score+p_denorm_max_score+20)>=p_threshold THEN
1782: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1783: l_denorm_str := NULL;
1784: END IF;
1785: ELSE
1786: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1782: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1783: l_denorm_str := NULL;
1784: END IF;
1785: ELSE
1786: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1787: END IF;
1788:
1789: -- Setup query string for TAX_NAME
1790: l_prim_temp := null;
1793:
1794: IF l_denorm_str IS NOT NULL THEN
1795: IF (p_non_denorm_max_score+30)>=p_threshold THEN
1796: l_denorm_str := NULL;
1797: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1798: ELSIF (p_non_denorm_max_score+p_denorm_max_score+30)>=p_threshold THEN
1799: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1800: l_denorm_str := NULL;
1801: END IF;
1795: IF (p_non_denorm_max_score+30)>=p_threshold THEN
1796: l_denorm_str := NULL;
1797: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1798: ELSIF (p_non_denorm_max_score+p_denorm_max_score+30)>=p_threshold THEN
1799: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1800: l_denorm_str := NULL;
1801: END IF;
1802: ELSE
1803: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1799: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1800: l_denorm_str := NULL;
1801: END IF;
1802: ELSE
1803: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1804: END IF;
1805:
1806: -- Setup query string for SIC_CODE
1807: l_prim_temp := null;
1810:
1811: IF l_denorm_str IS NOT NULL THEN
1812: IF (p_non_denorm_max_score+30)>=p_threshold THEN
1813: l_denorm_str := NULL;
1814: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1815: ELSIF (p_non_denorm_max_score+p_denorm_max_score+30)>=p_threshold THEN
1816: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1817: l_denorm_str := NULL;
1818: END IF;
1812: IF (p_non_denorm_max_score+30)>=p_threshold THEN
1813: l_denorm_str := NULL;
1814: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1815: ELSIF (p_non_denorm_max_score+p_denorm_max_score+30)>=p_threshold THEN
1816: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1817: l_denorm_str := NULL;
1818: END IF;
1819: ELSE
1820: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1816: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1817: l_denorm_str := NULL;
1818: END IF;
1819: ELSE
1820: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1821: END IF;
1822:
1823: -- Setup query string for CORPORATION_CLASS
1824: l_prim_temp := null;
1827:
1828: IF l_denorm_str IS NOT NULL THEN
1829: IF (p_non_denorm_max_score+30)>=p_threshold THEN
1830: l_denorm_str := NULL;
1831: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1832: ELSIF (p_non_denorm_max_score+p_denorm_max_score+30)>=p_threshold THEN
1833: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1834: l_denorm_str := NULL;
1835: END IF;
1829: IF (p_non_denorm_max_score+30)>=p_threshold THEN
1830: l_denorm_str := NULL;
1831: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1832: ELSIF (p_non_denorm_max_score+p_denorm_max_score+30)>=p_threshold THEN
1833: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1834: l_denorm_str := NULL;
1835: END IF;
1836: ELSE
1837: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1833: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1834: l_denorm_str := NULL;
1835: END IF;
1836: ELSE
1837: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1838: END IF;
1839:
1840: -- Setup query string for PARTY_ALL_NAMES
1841: l_prim_temp := null;
1846:
1847: IF l_denorm_str IS NOT NULL THEN
1848: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1849: l_denorm_str := NULL;
1850: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1851: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1852: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1853: l_denorm_str := NULL;
1854: END IF;
1848: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1849: l_denorm_str := NULL;
1850: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1851: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1852: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1853: l_denorm_str := NULL;
1854: END IF;
1855: ELSE
1856: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1852: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1853: l_denorm_str := NULL;
1854: END IF;
1855: ELSE
1856: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1857: END IF;
1858:
1859: -- Setup query string for ALL_ACCOUNT_NAMES
1860: l_prim_temp := null;
1863:
1864: IF l_denorm_str IS NOT NULL THEN
1865: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1866: l_denorm_str := NULL;
1867: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1868: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1869: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1870: l_denorm_str := NULL;
1871: END IF;
1865: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1866: l_denorm_str := NULL;
1867: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1868: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1869: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1870: l_denorm_str := NULL;
1871: END IF;
1872: ELSE
1873: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1869: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1870: l_denorm_str := NULL;
1871: END IF;
1872: ELSE
1873: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1874: END IF;
1875:
1876: -- Setup query string for TAX_REFERENCE
1877: l_prim_temp := null;
1880:
1881: IF l_denorm_str IS NOT NULL THEN
1882: IF (p_non_denorm_max_score+80)>=p_threshold THEN
1883: l_denorm_str := NULL;
1884: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1885: ELSIF (p_non_denorm_max_score+p_denorm_max_score+80)>=p_threshold THEN
1886: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1887: l_denorm_str := NULL;
1888: END IF;
1882: IF (p_non_denorm_max_score+80)>=p_threshold THEN
1883: l_denorm_str := NULL;
1884: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1885: ELSIF (p_non_denorm_max_score+p_denorm_max_score+80)>=p_threshold THEN
1886: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1887: l_denorm_str := NULL;
1888: END IF;
1889: ELSE
1890: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1886: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1887: l_denorm_str := NULL;
1888: END IF;
1889: ELSE
1890: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1891: END IF;
1892:
1893: -- Setup query string for PARTY_NUMBER
1894: l_prim_temp := null;
1897:
1898: IF l_denorm_str IS NOT NULL THEN
1899: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1900: l_denorm_str := NULL;
1901: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1902: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1903: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1904: l_denorm_str := NULL;
1905: END IF;
1899: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1900: l_denorm_str := NULL;
1901: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1902: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1903: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1904: l_denorm_str := NULL;
1905: END IF;
1906: ELSE
1907: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1903: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1904: l_denorm_str := NULL;
1905: END IF;
1906: ELSE
1907: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1908: END IF;
1909:
1910: -- Setup query string for ALL_ACCOUNT_NUMBERS
1911: l_prim_temp := null;
1914:
1915: IF l_denorm_str IS NOT NULL THEN
1916: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1917: l_denorm_str := NULL;
1918: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1919: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1920: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1921: l_denorm_str := NULL;
1922: END IF;
1916: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1917: l_denorm_str := NULL;
1918: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1919: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1920: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1921: l_denorm_str := NULL;
1922: END IF;
1923: ELSE
1924: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1920: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1921: l_denorm_str := NULL;
1922: END IF;
1923: ELSE
1924: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1925: END IF;
1926: IF lengthb(l_party_contains_str) > 4000 THEN
1927: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1928: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1922: END IF;
1923: ELSE
1924: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1925: END IF;
1926: IF lengthb(l_party_contains_str) > 4000 THEN
1927: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1928: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1929: FND_MSG_PUB.ADD;
1930: RAISE FND_API.G_EXC_ERROR;
1928: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1929: FND_MSG_PUB.ADD;
1930: RAISE FND_API.G_EXC_ERROR;
1931: END IF;
1932: IF (p_match_str = ' AND ' OR p_party_max_score
1934: 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);
1935: END IF;
1936: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1930: RAISE FND_API.G_EXC_ERROR;
1931: END IF;
1932: IF (p_match_str = ' AND ' OR p_party_max_score
1934: 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);
1935: END IF;
1936: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1937: 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);
1938: END IF;
1933: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1934: 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);
1935: END IF;
1936: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1937: 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);
1938: END IF;
1939: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1940: 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);
1941: END IF;
1938: END IF;
1939: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1940: 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);
1941: END IF;
1942: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1943: ELSE
1944: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1945: 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);
1946: 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);
1941: END IF;
1942: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1943: ELSE
1944: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1945: 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);
1946: 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);
1947: END IF;
1948: RETURN l_party_contains_str;
1949: END IF;
1942: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1943: ELSE
1944: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1945: 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);
1946: 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);
1947: END IF;
1948: RETURN l_party_contains_str;
1949: END IF;
1950: END;
1944: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1945: 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);
1946: 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);
1947: END IF;
1948: RETURN l_party_contains_str;
1949: END IF;
1950: END;
1951: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1952: l_contains_str VARCHAR2(32000);
1948: RETURN l_party_contains_str;
1949: END IF;
1950: END;
1951: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1952: l_contains_str VARCHAR2(32000);
1953: l_contains_str_temp VARCHAR2(32000);
1954: l_den_contains_str VARCHAR2(32000);
1955: l_den_contains_str_temp VARCHAR2(32000);
1956: l_filter_str VARCHAR2(4000) := null;
1949: END IF;
1950: END;
1951: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1952: l_contains_str VARCHAR2(32000);
1953: l_contains_str_temp VARCHAR2(32000);
1954: l_den_contains_str VARCHAR2(32000);
1955: l_den_contains_str_temp VARCHAR2(32000);
1956: l_filter_str VARCHAR2(4000) := null;
1957: l_prim_temp VARCHAR2(4000) := null;
1950: END;
1951: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1952: l_contains_str VARCHAR2(32000);
1953: l_contains_str_temp VARCHAR2(32000);
1954: l_den_contains_str VARCHAR2(32000);
1955: l_den_contains_str_temp VARCHAR2(32000);
1956: l_filter_str VARCHAR2(4000) := null;
1957: l_prim_temp VARCHAR2(4000) := null;
1958: l_prim_temp_den VARCHAR2(4000) := null;
1951: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1952: l_contains_str VARCHAR2(32000);
1953: l_contains_str_temp VARCHAR2(32000);
1954: l_den_contains_str VARCHAR2(32000);
1955: l_den_contains_str_temp VARCHAR2(32000);
1956: l_filter_str VARCHAR2(4000) := null;
1957: l_prim_temp VARCHAR2(4000) := null;
1958: l_prim_temp_den VARCHAR2(4000) := null;
1959: BEGIN
1961: 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);
1962: END IF;
1963: x_denorm_str := NULL;
1964:
1965: -- Dynamic setup of party site contains str
1966: --
1967: -- For each primary transformation add to intermedia query if it
1968: -- is not null
1969: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1966: --
1967: -- For each primary transformation add to intermedia query if it
1968: -- is not null
1969: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1970: l_contains_str_temp := null;
1971: l_den_contains_str_temp := null;
1972: l_filter_str := null;
1973:
1974: -- Setup query string for CITY
1967: -- For each primary transformation add to intermedia query if it
1968: -- is not null
1969: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1970: l_contains_str_temp := null;
1971: l_den_contains_str_temp := null;
1972: l_filter_str := null;
1973:
1974: -- Setup query string for CITY
1975: l_prim_temp := null;
1977: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1978: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1979: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1980: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1981: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1982: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1983:
1984: -- Setup query string for POSTAL_CODE
1985: l_prim_temp := null;
1978: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1979: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1980: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1981: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1982: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1983:
1984: -- Setup query string for POSTAL_CODE
1985: l_prim_temp := null;
1986: l_prim_temp_den := null;
1987: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1988: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1989: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1990: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1991: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1992: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1993:
1994: -- Setup query string for PROVINCE
1995: l_prim_temp := null;
1988: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1989: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1990: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1991: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1992: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1993:
1994: -- Setup query string for PROVINCE
1995: l_prim_temp := null;
1996: l_prim_temp_den := null;
1997: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1998: g_party_site_stage_list(I).TX13,'A31',l_prim_temp);
1999: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
2000: g_party_site_stage_list(I).TX13,'D_PS',l_prim_temp_den);
2001: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
2002: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2003:
2004: -- Setup query string for STATE
2005: l_prim_temp := null;
1998: g_party_site_stage_list(I).TX13,'A31',l_prim_temp);
1999: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
2000: g_party_site_stage_list(I).TX13,'D_PS',l_prim_temp_den);
2001: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
2002: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2003:
2004: -- Setup query string for STATE
2005: l_prim_temp := null;
2006: l_prim_temp_den := null;
2007: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
2008: g_party_site_stage_list(I).TX15,'A32',l_prim_temp);
2009: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
2010: g_party_site_stage_list(I).TX15,'D_PS',l_prim_temp_den);
2011: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
2012: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2013:
2014: -- Setup query string for COUNTY
2015: l_prim_temp := null;
2008: g_party_site_stage_list(I).TX15,'A32',l_prim_temp);
2009: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
2010: g_party_site_stage_list(I).TX15,'D_PS',l_prim_temp_den);
2011: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
2012: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2013:
2014: -- Setup query string for COUNTY
2015: l_prim_temp := null;
2016: l_prim_temp_den := null;
2017: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
2018: g_party_site_stage_list(I).TX21,'A35',l_prim_temp);
2019: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
2020: g_party_site_stage_list(I).TX21,'D_PS',l_prim_temp_den);
2021: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
2022: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2023:
2024: -- Setup query string for COUNTRY
2025: l_prim_temp := null;
2018: g_party_site_stage_list(I).TX21,'A35',l_prim_temp);
2019: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
2020: g_party_site_stage_list(I).TX21,'D_PS',l_prim_temp_den);
2021: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
2022: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2023:
2024: -- Setup query string for COUNTRY
2025: l_prim_temp := null;
2026: l_prim_temp_den := null;
2027: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
2028: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
2029: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
2030: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
2031: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
2032: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2033:
2034: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2035: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2028: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
2029: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
2030: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
2031: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
2032: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2033:
2034: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2035: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2036: END LOOP;
2030: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
2031: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
2032: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2033:
2034: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2035: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2036: END LOOP;
2037: -- Add the search criteria to query string
2038: IF lengthb(l_contains_str) > 4000 THEN
2031: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
2032: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2033:
2034: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2035: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2036: END LOOP;
2037: -- Add the search criteria to query string
2038: IF lengthb(l_contains_str) > 4000 THEN
2039: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
2034: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2035: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2036: END LOOP;
2037: -- Add the search criteria to query string
2038: IF lengthb(l_contains_str) > 4000 THEN
2039: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
2040: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
2041: FND_MSG_PUB.ADD;
2042: RAISE FND_API.G_EXC_ERROR;
2040: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
2041: FND_MSG_PUB.ADD;
2042: RAISE FND_API.G_EXC_ERROR;
2043: END IF;
2044: x_denorm_str := l_den_contains_str;
2045: RETURN l_contains_str;
2046: END;
2047:
2048: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2041: FND_MSG_PUB.ADD;
2042: RAISE FND_API.G_EXC_ERROR;
2043: END IF;
2044: x_denorm_str := l_den_contains_str;
2045: RETURN l_contains_str;
2046: END;
2047:
2048: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2049: l_contains_str VARCHAR2(32000);
2045: RETURN l_contains_str;
2046: END;
2047:
2048: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2049: l_contains_str VARCHAR2(32000);
2050: l_contains_str_temp VARCHAR2(32000);
2051: l_den_contains_str VARCHAR2(32000);
2052: l_den_contains_str_temp VARCHAR2(32000);
2053: l_filter_str VARCHAR2(4000) := null;
2046: END;
2047:
2048: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2049: l_contains_str VARCHAR2(32000);
2050: l_contains_str_temp VARCHAR2(32000);
2051: l_den_contains_str VARCHAR2(32000);
2052: l_den_contains_str_temp VARCHAR2(32000);
2053: l_filter_str VARCHAR2(4000) := null;
2054: l_prim_temp VARCHAR2(4000) := null;
2047:
2048: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2049: l_contains_str VARCHAR2(32000);
2050: l_contains_str_temp VARCHAR2(32000);
2051: l_den_contains_str VARCHAR2(32000);
2052: l_den_contains_str_temp VARCHAR2(32000);
2053: l_filter_str VARCHAR2(4000) := null;
2054: l_prim_temp VARCHAR2(4000) := null;
2055: l_prim_temp_den VARCHAR2(4000) := null;
2048: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2049: l_contains_str VARCHAR2(32000);
2050: l_contains_str_temp VARCHAR2(32000);
2051: l_den_contains_str VARCHAR2(32000);
2052: l_den_contains_str_temp VARCHAR2(32000);
2053: l_filter_str VARCHAR2(4000) := null;
2054: l_prim_temp VARCHAR2(4000) := null;
2055: l_prim_temp_den VARCHAR2(4000) := null;
2056: BEGIN
2061: RETURN NULL;
2062: END;
2063:
2064: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2065: l_contains_str VARCHAR2(32000);
2066: l_contains_str_temp VARCHAR2(32000);
2067: l_den_contains_str VARCHAR2(32000);
2068: l_den_contains_str_temp VARCHAR2(32000);
2069: l_filter_str VARCHAR2(4000) := null;
2062: END;
2063:
2064: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2065: l_contains_str VARCHAR2(32000);
2066: l_contains_str_temp VARCHAR2(32000);
2067: l_den_contains_str VARCHAR2(32000);
2068: l_den_contains_str_temp VARCHAR2(32000);
2069: l_filter_str VARCHAR2(4000) := null;
2070: l_prim_temp VARCHAR2(4000) := null;
2063:
2064: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2065: l_contains_str VARCHAR2(32000);
2066: l_contains_str_temp VARCHAR2(32000);
2067: l_den_contains_str VARCHAR2(32000);
2068: l_den_contains_str_temp VARCHAR2(32000);
2069: l_filter_str VARCHAR2(4000) := null;
2070: l_prim_temp VARCHAR2(4000) := null;
2071: l_prim_temp_den VARCHAR2(4000) := null;
2064: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2065: l_contains_str VARCHAR2(32000);
2066: l_contains_str_temp VARCHAR2(32000);
2067: l_den_contains_str VARCHAR2(32000);
2068: l_den_contains_str_temp VARCHAR2(32000);
2069: l_filter_str VARCHAR2(4000) := null;
2070: l_prim_temp VARCHAR2(4000) := null;
2071: l_prim_temp_den VARCHAR2(4000) := null;
2072: TYPE CONTACT_PT_REC_TYPE IS RECORD (
2080: 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);
2081: END IF;
2082: x_denorm_str := NULL;
2083:
2084: -- Dynamic setup of party site contains str
2085: --
2086: -- For each primary transformation add to intermedia query if it
2087: -- is not null
2088: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
2085: --
2086: -- For each primary transformation add to intermedia query if it
2087: -- is not null
2088: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
2089: l_contains_str_temp := null;
2090: l_den_contains_str_temp := null;
2091: l_filter_str := null;
2092: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
2093: if(l_cnt_pt_type.count>0) then
2086: -- For each primary transformation add to intermedia query if it
2087: -- is not null
2088: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
2089: l_contains_str_temp := null;
2090: l_den_contains_str_temp := null;
2091: l_filter_str := null;
2092: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
2093: if(l_cnt_pt_type.count>0) then
2094: x_modify := 'Y';
2110: l_prim_temp := null;
2111: l_prim_temp_den := null;
2112: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL CTX
2113: g_contact_pt_stage_list(I).TX1,'A44',l_prim_temp);
2114: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2115:
2116: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2117: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2118: IF N>1 THEN
2112: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL CTX
2113: g_contact_pt_stage_list(I).TX1,'A44',l_prim_temp);
2114: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2115:
2116: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2117: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2118: IF N>1 THEN
2119: distinct_search_cpt_types := N-1;
2120: ELSE
2113: g_contact_pt_stage_list(I).TX1,'A44',l_prim_temp);
2114: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2115:
2116: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2117: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2118: IF N>1 THEN
2119: distinct_search_cpt_types := N-1;
2120: ELSE
2121: distinct_search_cpt_types := N;
2124: 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);
2125: END IF;
2126: END LOOP;
2127: -- Add the search criteria to query string
2128: IF lengthb(l_contains_str) > 4000 THEN
2129: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
2130: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
2131: FND_MSG_PUB.ADD;
2132: RAISE FND_API.G_EXC_ERROR;
2130: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
2131: FND_MSG_PUB.ADD;
2132: RAISE FND_API.G_EXC_ERROR;
2133: END IF;
2134: x_denorm_str := l_den_contains_str;
2135: RETURN l_contains_str;
2136: END;
2137:
2138: PROCEDURE open_party_cursor_direct (
2131: FND_MSG_PUB.ADD;
2132: RAISE FND_API.G_EXC_ERROR;
2133: END IF;
2134: x_denorm_str := l_den_contains_str;
2135: RETURN l_contains_str;
2136: END;
2137:
2138: PROCEDURE open_party_cursor_direct (
2139: p_dup_party_id NUMBER,
2139: p_dup_party_id NUMBER,
2140: p_restrict_sql VARCHAR2,
2141: p_match_str VARCHAR2,
2142: p_search_merged VARCHAR2,
2143: p_party_contains_str VARCHAR2,
2144: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
2145: l_sqlstr VARCHAR2(4000);
2146: l_search_merged VARCHAR2(1);
2147: BEGIN
2156: END IF;
2157: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2158: 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);
2159: END IF;
2160: 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
2161: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2162: 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);
2163: END IF;
2164: OPEN x_cursor FOR
2222: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
2223: l_sqlstr := l_sqlstr || ' (TX36 like :TX36||'' %'' ';
2224: l_sqlstr := l_sqlstr || ' )) ';
2225: END IF;
2226: 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
2227: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2228: 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);
2229: END IF;
2230: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
2224: l_sqlstr := l_sqlstr || ' )) ';
2225: END IF;
2226: 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
2227: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2228: 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);
2229: END IF;
2230: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
2231: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX10||'' %'' ';
2232: l_sqlstr := l_sqlstr || ' )) ';
2260: END IF;
2261: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2262: 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);
2263: END IF;
2264: 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
2265: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2266: 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);
2267: END IF;
2268: 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
2262: 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);
2263: END IF;
2264: 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
2265: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2266: 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);
2267: END IF;
2268: 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
2269: ,g_party_stage_rec.TX33
2270: ,g_party_stage_rec.TX33
2334: ,g_party_stage_rec.TX157
2335: ,g_party_stage_rec.TX36
2336: ,g_party_stage_rec.TX36
2337: ;
2338: 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
2339: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2340: 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);
2341: END IF;
2342: 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
2336: ,g_party_stage_rec.TX36
2337: ;
2338: 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
2339: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2340: 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);
2341: END IF;
2342: 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
2343: ,g_party_site_stage_list(1).TX10
2344: ,g_party_site_stage_list(1).TX10
2363: END;
2364: PROCEDURE open_party_cursor(
2365: p_dup_party_id NUMBER,
2366: p_restrict_sql VARCHAR2,
2367: p_contains_str VARCHAR2,
2368: p_search_ctx_id NUMBER,
2369: p_match_str VARCHAR2,
2370: p_search_merged VARCHAR2,
2371: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
2382: l_search_merged := 'N';
2383: ELSE
2384: l_search_merged := p_search_merged;
2385: END IF;
2386: IF p_contains_str IS NULL THEN
2387: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2388: 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);
2389: END IF;
2390: OPEN x_cursor FOR
2384: l_search_merged := p_search_merged;
2385: END IF;
2386: IF p_contains_str IS NULL THEN
2387: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2388: 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);
2389: END IF;
2390: OPEN x_cursor FOR
2391: SELECT PARTY_ID , TX32, TX34, TX35, TX39, TX158, TX40, TX43, TX42, TX44, TX46, TX47, TX48, TX156, TX157
2392: FROM HZ_STAGED_PARTIES stage
2401: END IF;
2402: OPEN x_cursor FOR
2403: SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID , TX32, TX34, TX35, TX39, TX158, TX40, TX43, TX42, TX44, TX46, TX47, TX48, TX156, TX157
2404: FROM HZ_STAGED_PARTIES stage
2405: WHERE contains( concat_col, p_contains_str)>0
2406: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
2407: AND( (l_search_merged ='Y' )
2408: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
2409: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )
2414: END IF;
2415: OPEN x_cursor FOR
2416: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX32, TX34, TX35, TX39, TX158, TX40, TX43, TX42, TX44, TX46, TX47, TX48, TX156, TX157
2417: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
2418: WHERE contains( concat_col, p_contains_str)>0
2419: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
2420: AND d.party_id = stage.party_id
2421: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
2422: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2439: END IF;
2440: IF p_search_ctx_id IS NULL THEN
2441: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX32, TX34, TX35, TX39, TX158, TX40, TX43, TX42, TX44, TX46, TX47, TX48, TX156, TX157'||
2442: ' FROM HZ_STAGED_PARTIES stage'||
2443: ' WHERE contains( concat_col, :cont)>0'||
2444: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
2445: ' AND ('||p_restrict_sql||')' ||
2446: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2447: IF l_search_merged = 'Y' THEN
2451: ELSE
2452: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
2453: END IF;
2454: output_long_strings('----------------------------------------------------------');
2455: output_long_strings('Party Contains String = '||p_contains_str);
2456: output_long_strings('Restrict Sql = '||p_restrict_sql);
2457: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2458: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2459: ,p_dup_party_id, p_dup_party_id;
2453: END IF;
2454: output_long_strings('----------------------------------------------------------');
2455: output_long_strings('Party Contains String = '||p_contains_str);
2456: output_long_strings('Restrict Sql = '||p_restrict_sql);
2457: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2458: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2459: ,p_dup_party_id, p_dup_party_id;
2460: END IF;
2461: END IF;
2484: PROCEDURE open_party_site_cursor(
2485: p_dup_party_id NUMBER,
2486: p_party_id NUMBER,
2487: p_restrict_sql VARCHAR2,
2488: p_contains_str VARCHAR2,
2489: p_search_ctx_id NUMBER,
2490: p_search_merged VARCHAR2,
2491: p_search_rel_sites VARCHAR2,
2492: p_person_api VARCHAR2,
2516: IF p_search_rel_sites = 'N' THEN
2517: OPEN x_cursor FOR
2518: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22
2519: FROM HZ_STAGED_PARTY_SITES stage
2520: WHERE contains( concat_col, p_contains_str)>0
2521: AND( (l_search_merged ='Y' )
2522: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2523: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2524: AND stage.party_id = p_party_id;
2525: ELSE
2526: OPEN x_cursor FOR
2527: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22
2528: FROM HZ_STAGED_PARTY_SITES stage
2529: WHERE contains( concat_col, p_contains_str)>0
2530: AND( (l_search_merged ='Y' )
2531: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2532: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2533: AND stage.party_id = p_party_id
2533: AND stage.party_id = p_party_id
2534: UNION
2535: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22
2536: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
2537: WHERE contains( concat_col, p_contains_str)>0
2538: AND( (l_search_merged ='Y' )
2539: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2540: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2541: AND r.object_id = p_party_id
2555: END IF;
2556: OPEN x_cursor FOR
2557: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22
2558: FROM HZ_STAGED_PARTY_SITES stage
2559: WHERE contains( concat_col, p_contains_str)>0
2560: AND EXISTS (
2561: SELECT 1 FROM HZ_STAGED_PARTIES p
2562: WHERE p.PARTY_ID = stage.PARTY_ID
2563: AND( (l_search_merged ='Y' )
2575: IF p_person_api = 'Y' THEN
2576: OPEN x_cursor FOR
2577: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22
2578: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
2579: WHERE contains( concat_col, p_contains_str)>0
2580: AND( (l_search_merged ='Y' )
2581: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2582: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2583: AND d.search_context_id = p_search_ctx_id
2585: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2586: UNION
2587: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22
2588: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
2589: WHERE contains( concat_col, p_contains_str)>0
2590: AND( (l_search_merged ='Y' )
2591: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2592: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2593: AND d.search_context_id = p_search_ctx_id
2598: ELSE
2599: OPEN x_cursor FOR
2600: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22
2601: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
2602: WHERE contains( concat_col, p_contains_str)>0
2603: AND( (l_search_merged ='Y' )
2604: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2605: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2606: AND d.search_context_id = p_search_ctx_id
2643: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2644: END IF;
2645: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22'||
2646: ' FROM HZ_STAGED_PARTY_SITES stage'||
2647: ' WHERE contains( concat_col, :cont)>0'||
2648: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2649: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2650: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2651: ' AND (ORG_CONTACT_ID IS NULL ' ||
2653: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2654: ' UNION ' ||
2655: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22'||
2656: ' FROM HZ_STAGED_PARTY_SITES stage'||
2657: ' WHERE contains( concat_col, :cont)>0'||
2658: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2659: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2660: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2661: ' AND ORG_CONTACT_ID IN ' ||
2663: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2664: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2665: ' and ('||p_restrict_sql1|| ') )' ||
2666: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2667: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2668: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2669: ELSE
2670: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22'||
2671: ' FROM HZ_STAGED_PARTY_SITES stage'||
2664: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2665: ' and ('||p_restrict_sql1|| ') )' ||
2666: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2667: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2668: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2669: ELSE
2670: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22'||
2671: ' FROM HZ_STAGED_PARTY_SITES stage'||
2672: ' WHERE contains( concat_col, :cont)>0'||
2668: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2669: ELSE
2670: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22'||
2671: ' FROM HZ_STAGED_PARTY_SITES stage'||
2672: ' WHERE contains( concat_col, :cont)>0'||
2673: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2674: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2675: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2676: ' AND EXISTS ('||
2678: ' WHERE p.party_id = stage.party_id ' ||
2679: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2680: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2681: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2682: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2683: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2684: ,p_dup_party_id, p_dup_party_id;
2685: END IF;
2686: ELSE
2685: END IF;
2686: ELSE
2687: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22'||
2688: ' FROM HZ_STAGED_PARTY_SITES stage'||
2689: ' WHERE contains( concat_col, :cont)>0'||
2690: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2691: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2692: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2693: ' AND EXISTS ('||
2695: ' WHERE p.party_id = stage.party_id ' ||
2696: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2697: ' AND ('||p_restrict_sql||')' ||
2698: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2699: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2700: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2701: ,p_dup_party_id, p_dup_party_id;
2702: END IF;
2703: END IF;
2701: ,p_dup_party_id, p_dup_party_id;
2702: END IF;
2703: END IF;
2704: output_long_strings('----------------------------------------------------------');
2705: output_long_strings('Party Site Contains String = '||p_contains_str);
2706: output_long_strings('Restrict Sql = '||p_restrict_sql);
2707: exception
2708: when others then
2709: if (instrb(SQLERRM,'DRG-51030')>0) then
2729: PROCEDURE open_contact_cursor(
2730: p_dup_party_id NUMBER,
2731: p_party_id NUMBER,
2732: p_restrict_sql VARCHAR2,
2733: p_contains_str VARCHAR2,
2734: p_search_ctx_id NUMBER,
2735: p_search_merged VARCHAR2,
2736: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
2737: l_sqlstr VARCHAR2(4000);
2757: END IF;
2758: OPEN x_cursor FOR
2759: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
2760: FROM HZ_STAGED_CONTACTS stage
2761: WHERE contains( concat_col, p_contains_str)>0
2762: AND EXISTS (
2763: SELECT 1 FROM HZ_STAGED_PARTIES p
2764: WHERE p.PARTY_ID = stage.PARTY_ID
2765: AND( (l_search_merged ='Y' )
2780: END IF;
2781: OPEN x_cursor FOR
2782: SELECT ORG_CONTACT_ID, PARTY_ID
2783: FROM HZ_STAGED_CONTACTS stage
2784: WHERE contains( concat_col, p_contains_str)>0
2785: AND EXISTS (
2786: SELECT 1 FROM HZ_STAGED_PARTIES p
2787: WHERE p.PARTY_ID = stage.PARTY_ID
2788: AND( (l_search_merged ='Y' )
2799: END IF;
2800: OPEN x_cursor FOR
2801: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
2802: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
2803: WHERE contains( concat_col, p_contains_str)>0
2804: AND d.search_context_id = p_search_ctx_id
2805: AND d.party_id = stage.party_id
2806: AND( (l_search_merged ='Y' )
2807: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2833: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
2834: END IF;
2835: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
2836: ' FROM HZ_STAGED_CONTACTS stage'||
2837: ' WHERE contains( concat_col, :cont)>0'||
2838: ' AND EXISTS ('||
2839: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2840: ' WHERE p.party_id = stage.party_id ' ||
2841: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2843: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2844: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2845: ' AND ('||p_restrict_sql||')' ||
2846: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2847: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2848: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2849: ,p_dup_party_id, p_dup_party_id;
2850: END IF;
2851: output_long_strings('----------------------------------------------------------');
2848: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2849: ,p_dup_party_id, p_dup_party_id;
2850: END IF;
2851: output_long_strings('----------------------------------------------------------');
2852: output_long_strings('Contacts Contains String = '||p_contains_str);
2853: output_long_strings('Restrict Sql = '||p_restrict_sql);
2854: exception
2855: when others then
2856: if (instrb(SQLERRM,'DRG-51030')>0) then
2876: PROCEDURE open_contact_pt_cursor(
2877: p_dup_party_id NUMBER,
2878: p_party_id NUMBER,
2879: p_restrict_sql VARCHAR2,
2880: p_contains_str VARCHAR2,
2881: p_search_ctx_id NUMBER,
2882: p_search_merged VARCHAR2,
2883: p_search_rel_cpts VARCHAR2,
2884: p_person_api VARCHAR2,
2906: THEN
2907: OPEN x_cursor FOR
2908: 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, TX3, TX4
2909: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2910: WHERE contains( concat_col, p_contains_str)>0
2911: AND d.search_context_id = p_search_ctx_id
2912: AND( (l_search_merged ='Y' )
2913: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2914: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2918: THEN
2919: OPEN x_cursor FOR
2920: 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, TX3, TX4
2921: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2922: WHERE contains( concat_col, p_contains_str)>0
2923: AND d.search_context_id = p_search_ctx_id
2924: AND( (l_search_merged ='Y' )
2925: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2926: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2935: IF p_search_rel_cpts = 'N' THEN
2936: OPEN x_cursor FOR
2937: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4
2938: FROM HZ_STAGED_CONTACT_POINTS stage
2939: WHERE contains( concat_col, p_contains_str)>0
2940: AND( (l_search_merged ='Y' )
2941: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2942: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2943: AND stage.party_id = p_party_id;
2944: ELSE
2945: OPEN x_cursor FOR
2946: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4
2947: FROM HZ_STAGED_CONTACT_POINTS stage
2948: WHERE contains( concat_col, p_contains_str)>0
2949: AND( (l_search_merged ='Y' )
2950: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2951: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2952: AND stage.party_id = p_party_id
2952: AND stage.party_id = p_party_id
2953: UNION
2954: 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, TX3, TX4
2955: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2956: WHERE contains( concat_col, p_contains_str)>0
2957: AND r.object_id = p_party_id
2958: AND( (l_search_merged ='Y' )
2959: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2960: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2974: END IF;
2975: OPEN x_cursor FOR
2976: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4
2977: FROM HZ_STAGED_CONTACT_POINTS stage
2978: WHERE contains( concat_col, p_contains_str)>0
2979: AND EXISTS (
2980: SELECT 1 FROM HZ_STAGED_PARTIES p
2981: WHERE p.PARTY_ID = stage.PARTY_ID
2982: AND( (l_search_merged ='Y' )
2994: IF p_person_api = 'Y' THEN
2995: OPEN x_cursor FOR
2996: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4
2997: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2998: WHERE contains( concat_col, p_contains_str)>0
2999: AND d.search_context_id = p_search_ctx_id
3000: AND d.party_id = stage.party_id
3001: AND( (l_search_merged ='Y' )
3002: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
3004: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
3005: UNION
3006: 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, TX3, TX4
3007: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
3008: WHERE contains( concat_col, p_contains_str)>0
3009: AND d.search_context_id = p_search_ctx_id
3010: AND d.party_id = r.subject_id
3011: AND r.relationship_id = oc.party_relationship_id
3012: AND oc.org_contact_id = stage.org_contact_id
3017: ELSE
3018: OPEN x_cursor FOR
3019: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4
3020: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
3021: WHERE contains( concat_col, p_contains_str)>0
3022: AND d.search_context_id = p_search_ctx_id
3023: AND d.party_id = stage.party_id
3024: AND( (l_search_merged ='Y' )
3025: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
3062: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
3063: END IF;
3064: 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, TX3, TX4'||
3065: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3066: ' WHERE contains( concat_col, :cont)>0 '||
3067: ' AND (stage.org_contact_id is null '||
3068: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3069: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
3070: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
3072: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
3073: ' UNION ' ||
3074: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4'||
3075: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3076: ' WHERE contains( concat_col, :cont)>0 '||
3077: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3078: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
3079: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
3080: ' AND (stage.org_contact_id in ' ||
3082: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
3083: ' where stage1.relationship_id = oc.party_relationship_id ' ||
3084: ' and ('||p_restrict_sql1 || ') ) )' ||
3085: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
3086: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
3087: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
3088: ELSE
3089: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4'||
3090: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3083: ' where stage1.relationship_id = oc.party_relationship_id ' ||
3084: ' and ('||p_restrict_sql1 || ') ) )' ||
3085: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
3086: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
3087: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
3088: ELSE
3089: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4'||
3090: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3091: ' WHERE contains( concat_col, :cont)>0'||
3087: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
3088: ELSE
3089: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4'||
3090: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3091: ' WHERE contains( concat_col, :cont)>0'||
3092: ' AND EXISTS ('||
3093: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
3094: ' WHERE p.party_id = stage.party_id ' ||
3095: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
3097: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
3098: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
3099: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
3100: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
3101: OPEN x_cursor FOR l_sqlstr USING p_contains_str
3102: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
3103: ,p_dup_party_id, p_dup_party_id;
3104: END IF;
3105: ELSE
3104: END IF;
3105: ELSE
3106: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4'||
3107: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3108: ' WHERE contains( concat_col, :cont)>0'||
3109: ' AND EXISTS ('||
3110: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
3111: ' WHERE p.party_id = stage.party_id ' ||
3112: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
3114: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
3115: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
3116: ' AND ('||p_restrict_sql||')' ||
3117: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
3118: OPEN x_cursor FOR l_sqlstr USING p_contains_str
3119: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
3120: ,p_dup_party_id, p_dup_party_id;
3121: END IF;
3122: END IF;
3121: END IF;
3122: END IF;
3123: END IF;
3124: output_long_strings('----------------------------------------------------------');
3125: output_long_strings('Contact Points Contains String = '||p_contains_str);
3126: output_long_strings('Restrict Sql = '||p_restrict_sql);
3127: exception
3128: when others then
3129: if (instrb(SQLERRM,'DRG-51030')>0) then
3242: x_num_matches OUT NUMBER
3243: ) IS
3244:
3245: -- Strings to hold the generated Intermedia query strings
3246: l_party_contains_str VARCHAR2(32000);
3247: l_party_site_contains_str VARCHAR2(32000);
3248: l_contact_contains_str VARCHAR2(32000);
3249: l_contact_pt_contains_str VARCHAR2(32000);
3250: l_denorm_str VARCHAR2(32000);
3243: ) IS
3244:
3245: -- Strings to hold the generated Intermedia query strings
3246: l_party_contains_str VARCHAR2(32000);
3247: l_party_site_contains_str VARCHAR2(32000);
3248: l_contact_contains_str VARCHAR2(32000);
3249: l_contact_pt_contains_str VARCHAR2(32000);
3250: l_denorm_str VARCHAR2(32000);
3251: l_ps_denorm_str VARCHAR2(32000);
3244:
3245: -- Strings to hold the generated Intermedia query strings
3246: l_party_contains_str VARCHAR2(32000);
3247: l_party_site_contains_str VARCHAR2(32000);
3248: l_contact_contains_str VARCHAR2(32000);
3249: l_contact_pt_contains_str VARCHAR2(32000);
3250: l_denorm_str VARCHAR2(32000);
3251: l_ps_denorm_str VARCHAR2(32000);
3252: l_ct_denorm_str VARCHAR2(32000);
3245: -- Strings to hold the generated Intermedia query strings
3246: l_party_contains_str VARCHAR2(32000);
3247: l_party_site_contains_str VARCHAR2(32000);
3248: l_contact_contains_str VARCHAR2(32000);
3249: l_contact_pt_contains_str VARCHAR2(32000);
3250: l_denorm_str VARCHAR2(32000);
3251: l_ps_denorm_str VARCHAR2(32000);
3252: l_ct_denorm_str VARCHAR2(32000);
3253: l_cpt_denorm_str VARCHAR2(32000);
3356: call_type(2) := 'OR';
3357: END;
3358:
3359: /** Private procedure to acquire and score at party level ***/
3360: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3361: l_party_id_idx NUMBER:=1;
3362: l_ctx_id NUMBER;
3363: l_precalc_score BOOLEAN := FALSE;
3364: l_TX35_new varchar2(4000);
3388: l_search_ctx_id,H_PARTY_ID(I));
3389: l_party_id_idx := H_PARTY_ID.COUNT+1;
3390: ELSE
3391: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3392: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3393: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3394: 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);
3395: END IF;
3396: 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
3396: 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
3397: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3398: 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);
3399: END IF;
3400: IF (l_party_site_contains_str IS NULL AND
3401: l_contact_contains_str IS NULL AND
3402: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3403: 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);
3404: RAISE L_RETURN_IMM_EXC;
3397: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3398: 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);
3399: END IF;
3400: IF (l_party_site_contains_str IS NULL AND
3401: l_contact_contains_str IS NULL AND
3402: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3403: 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);
3404: RAISE L_RETURN_IMM_EXC;
3405: ELSE
3398: 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);
3399: END IF;
3400: IF (l_party_site_contains_str IS NULL AND
3401: l_contact_contains_str IS NULL AND
3402: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3403: 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);
3404: RAISE L_RETURN_IMM_EXC;
3405: ELSE
3406: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
3402: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3403: 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);
3404: RAISE L_RETURN_IMM_EXC;
3405: ELSE
3406: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
3407: END IF;
3408: ELSE
3409: IF p_index>1 THEN
3410: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3425: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3426: 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);
3427: END IF;
3428: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3429: 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);
3430: END IF;
3431: LOOP
3432: FETCH l_party_cur INTO
3433: l_party_id , l_TX32, l_TX34, l_TX35, l_TX39, l_TX158, l_TX40, l_TX43, l_TX42, l_TX44, l_TX46, l_TX47, l_TX48, l_TX156, l_TX157;
3449: l_party_id_idx:= l_party_id_idx+1;
3450: END IF;
3451: IF (l_party_id_idx-1)>l_max_thresh THEN
3452: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3453: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3454: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3455: AND l_contact_pt_contains_str IS NULL THEN
3456: EXIT;
3457: END IF;
3451: IF (l_party_id_idx-1)>l_max_thresh THEN
3452: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3453: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3454: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3455: AND l_contact_pt_contains_str IS NULL THEN
3456: EXIT;
3457: END IF;
3458: CLOSE l_party_cur;
3459: IF p_index>1 THEN
3480: ROLLBACK to eval_start;
3481: END;
3482:
3483: /** Private procedure to acquire and score at party site level ***/
3484: 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
3485: l_party_id_idx NUMBER:=1;
3486: l_ctx_id NUMBER;
3487: h_ps_id HZ_PARTY_SEARCH.IDList;
3488: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3492: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3493: 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);
3494: 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);
3495: END IF;
3496: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
3497: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
3498: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
3499: l_party_id := H_SCORES.FIRST;
3500: WHILE l_party_id IS NOT NULL LOOP
3493: 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);
3494: 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);
3495: END IF;
3496: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
3497: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
3498: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
3499: l_party_id := H_SCORES.FIRST;
3500: WHILE l_party_id IS NOT NULL LOOP
3501: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
3494: 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);
3495: END IF;
3496: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
3497: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
3498: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
3499: l_party_id := H_SCORES.FIRST;
3500: WHILE l_party_id IS NOT NULL LOOP
3501: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
3502: l_party_id:=H_SCORES.NEXT(l_party_id);
3524: l_search_ctx_id,H_PARTY_ID(I));
3525: l_party_id_idx := H_PARTY_ID.COUNT+1;
3526: ELSE
3527: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3528: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
3529: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3530: 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);
3531: END IF;
3532: IF p_index>1 THEN
3546: END IF;
3547: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3548: 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);
3549: END IF;
3550: 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);
3551: LOOP
3552: FETCH l_party_site_cur INTO
3553: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX9, l_TX10, l_TX11, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;
3554: EXIT WHEN l_party_site_cur%NOTFOUND;
3602: END IF;
3603: END;
3604:
3605: /** Private procedure to acquire and score at contact point level ***/
3606: 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
3607: l_party_id_idx NUMBER:=1;
3608: l_ctx_id NUMBER;
3609: h_ct_id HZ_PARTY_SEARCH.IDList;
3610: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3636: l_search_ctx_id,H_PARTY_ID(I));
3637: l_party_id_idx := H_PARTY_ID.COUNT+1;
3638: ELSE
3639: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3640: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
3641: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3642: 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);
3643: END IF;
3644: IF p_index>1 THEN
3658: END IF;
3659: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3660: 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);
3661: END IF;
3662: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
3663: LOOP
3664: FETCH l_contact_cur INTO
3665: l_org_contact_id, l_ct_party_id ;
3666: EXIT WHEN l_contact_cur%NOTFOUND;
3711: END IF;
3712: END;
3713:
3714: /** Private procedure to acquire and score at contact point level ***/
3715: 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
3716: l_party_id_idx NUMBER:=1;
3717: l_ctx_id NUMBER;
3718: h_cpt_id HZ_PARTY_SEARCH.IDList;
3719: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3747: l_search_ctx_id,H_PARTY_ID(I));
3748: l_party_id_idx := H_PARTY_ID.COUNT+1;
3749: ELSE
3750: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3751: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
3752: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3753: 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);
3754: END IF;
3755: IF p_index>1 THEN
3769: END IF;
3770: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3771: 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);
3772: END IF;
3773: 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);
3774: LOOP
3775: FETCH l_contact_pt_cur INTO
3776: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX3, l_TX4;
3777: EXIT WHEN l_contact_pt_cur%NOTFOUND;
3950: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3951: l_threshold := round(( l_entered_max_score / 805) * 72);
3952: other_acq_criteria_exists := TRUE ;
3953: --check if acquisition criteria exists for any other entity
3954: IF l_party_contains_str IS NOT NULL THEN
3955: acq_cnt := 1;
3956: END IF;
3957: IF l_party_site_contains_str IS NOT NULL THEN
3958: acq_cnt := acq_cnt+1;
3953: --check if acquisition criteria exists for any other entity
3954: IF l_party_contains_str IS NOT NULL THEN
3955: acq_cnt := 1;
3956: END IF;
3957: IF l_party_site_contains_str IS NOT NULL THEN
3958: acq_cnt := acq_cnt+1;
3959: END IF;
3960: IF l_contact_contains_str IS NOT NULL THEN
3961: acq_cnt := acq_cnt+1;
3956: END IF;
3957: IF l_party_site_contains_str IS NOT NULL THEN
3958: acq_cnt := acq_cnt+1;
3959: END IF;
3960: IF l_contact_contains_str IS NOT NULL THEN
3961: acq_cnt := acq_cnt+1;
3962: END IF;
3963: IF l_contact_pt_contains_str IS NOT NULL THEN
3964: acq_cnt := acq_cnt+1;
3959: END IF;
3960: IF l_contact_contains_str IS NOT NULL THEN
3961: acq_cnt := acq_cnt+1;
3962: END IF;
3963: IF l_contact_pt_contains_str IS NOT NULL THEN
3964: acq_cnt := acq_cnt+1;
3965: END IF;
3966: IF acq_cnt>1 THEN
3967: other_acq_criteria_exists := TRUE;
3993: FND_MSG_PUB.ADD;
3994: RAISE FND_API.G_EXC_ERROR;
3995: END IF;
3996: END IF;
3997: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3998: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3999: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4000: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4001: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3994: RAISE FND_API.G_EXC_ERROR;
3995: END IF;
3996: END IF;
3997: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3998: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3999: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4000: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4001: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4002: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3995: END IF;
3996: END IF;
3997: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3998: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3999: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4000: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4001: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4002: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4003: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3996: END IF;
3997: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3998: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3999: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4000: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4001: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4002: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4003: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4004: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3997: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3998: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3999: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4000: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4001: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4002: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4003: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4004: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4005: END IF;
3998: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3999: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4000: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4001: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4002: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4003: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4004: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4005: END IF;
4006: END;
3999: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4000: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4001: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4002: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4003: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4004: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4005: END IF;
4006: END;
4007: /************ End of find_parties private procedures **********/
4000: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4001: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4002: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4003: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4004: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4005: END IF;
4006: END;
4007: /************ End of find_parties private procedures **********/
4008:
4072: 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);
4073: END IF;
4074: -- ************************************************************
4075: -- STEP 2. Setup of intermedia query strings for Acquisition query
4076: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4077: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4078: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4079: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4080: 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);
4073: END IF;
4074: -- ************************************************************
4075: -- STEP 2. Setup of intermedia query strings for Acquisition query
4076: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4077: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4078: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4079: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4080: 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);
4081: END IF;
4074: -- ************************************************************
4075: -- STEP 2. Setup of intermedia query strings for Acquisition query
4076: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4077: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4078: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4079: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4080: 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);
4081: END IF;
4082: l_denorm_max_score:=0;
4106: END IF;
4107: ELSE
4108: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
4109: END IF;
4110: 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((72/805)*l_entered_max_score));
4111: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4112:
4113: -- Setup Search Context ID
4114: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4116:
4117: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4118: 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);
4119: END IF;
4120: IF l_party_contains_str IS NULL THEN
4121: defpt := 1;
4122: END IF;
4123: IF l_party_site_contains_str IS NULL THEN
4124: defps := 1;
4119: END IF;
4120: IF l_party_contains_str IS NULL THEN
4121: defpt := 1;
4122: END IF;
4123: IF l_party_site_contains_str IS NULL THEN
4124: defps := 1;
4125: END IF;
4126: IF l_contact_contains_str IS NULL THEN
4127: defct := 1;
4122: END IF;
4123: IF l_party_site_contains_str IS NULL THEN
4124: defps := 1;
4125: END IF;
4126: IF l_contact_contains_str IS NULL THEN
4127: defct := 1;
4128: END IF;
4129: IF l_contact_pt_contains_str IS NULL THEN
4130: defcpt := 1;
4125: END IF;
4126: IF l_contact_contains_str IS NULL THEN
4127: defct := 1;
4128: END IF;
4129: IF l_contact_pt_contains_str IS NULL THEN
4130: defcpt := 1;
4131: END IF;
4132:
4133: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4134: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4135: 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);
4136: END IF;
4137: 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);
4138: 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);
4139: 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);
4140: 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);
4141: 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);
4142: 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);
4135: 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);
4136: END IF;
4137: 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);
4138: 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);
4139: 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);
4140: 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);
4141: 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);
4142: 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);
4143: END IF;
4136: END IF;
4137: 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);
4138: 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);
4139: 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);
4140: 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);
4141: 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);
4142: 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);
4143: END IF;
4144: FOR I in 1..5 LOOP
4137: 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);
4138: 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);
4139: 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);
4140: 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);
4141: 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);
4142: 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);
4143: END IF;
4144: FOR I in 1..5 LOOP
4145: do_eval(I);
4219: x_num_matches OUT NUMBER
4220: ) IS
4221:
4222: -- Strings to hold the generated Intermedia query strings
4223: l_party_contains_str VARCHAR2(32000);
4224: l_party_site_contains_str VARCHAR2(32000);
4225: l_contact_contains_str VARCHAR2(32000);
4226: l_contact_pt_contains_str VARCHAR2(32000);
4227: l_denorm_str VARCHAR2(32000);
4220: ) IS
4221:
4222: -- Strings to hold the generated Intermedia query strings
4223: l_party_contains_str VARCHAR2(32000);
4224: l_party_site_contains_str VARCHAR2(32000);
4225: l_contact_contains_str VARCHAR2(32000);
4226: l_contact_pt_contains_str VARCHAR2(32000);
4227: l_denorm_str VARCHAR2(32000);
4228: l_ps_denorm_str VARCHAR2(32000);
4221:
4222: -- Strings to hold the generated Intermedia query strings
4223: l_party_contains_str VARCHAR2(32000);
4224: l_party_site_contains_str VARCHAR2(32000);
4225: l_contact_contains_str VARCHAR2(32000);
4226: l_contact_pt_contains_str VARCHAR2(32000);
4227: l_denorm_str VARCHAR2(32000);
4228: l_ps_denorm_str VARCHAR2(32000);
4229: l_ct_denorm_str VARCHAR2(32000);
4222: -- Strings to hold the generated Intermedia query strings
4223: l_party_contains_str VARCHAR2(32000);
4224: l_party_site_contains_str VARCHAR2(32000);
4225: l_contact_contains_str VARCHAR2(32000);
4226: l_contact_pt_contains_str VARCHAR2(32000);
4227: l_denorm_str VARCHAR2(32000);
4228: l_ps_denorm_str VARCHAR2(32000);
4229: l_ct_denorm_str VARCHAR2(32000);
4230: l_cpt_denorm_str VARCHAR2(32000);
4363: call_type(2) := 'OR';
4364: END;
4365:
4366: /** Private procedure to acquire and score at party level ***/
4367: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
4368: l_party_id_idx NUMBER:=1;
4369: l_ctx_id NUMBER;
4370: l_TX35_new varchar2(4000);
4371: BEGIN
4385: l_search_ctx_id,H_PARTY_ID(I));
4386: l_party_id_idx := H_PARTY_ID.COUNT+1;
4387: ELSE
4388: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
4389: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
4390: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4391: 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);
4392: END IF;
4393: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
4393: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
4394: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4395: 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);
4396: END IF;
4397: IF (l_party_site_contains_str IS NULL AND
4398: l_contact_contains_str IS NULL AND
4399: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4400: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4401: RAISE L_RETURN_IMM_EXC;
4394: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4395: 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);
4396: END IF;
4397: IF (l_party_site_contains_str IS NULL AND
4398: l_contact_contains_str IS NULL AND
4399: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4400: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4401: RAISE L_RETURN_IMM_EXC;
4402: ELSE
4395: 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);
4396: END IF;
4397: IF (l_party_site_contains_str IS NULL AND
4398: l_contact_contains_str IS NULL AND
4399: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4400: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4401: RAISE L_RETURN_IMM_EXC;
4402: ELSE
4403: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
4399: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4400: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4401: RAISE L_RETURN_IMM_EXC;
4402: ELSE
4403: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
4404: END IF;
4405: ELSE
4406: IF p_index>1 THEN
4407: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4419: l_ctx_id := NULL;
4420: l_party_id_idx := H_PARTY_ID.COUNT+1;
4421: END IF;
4422: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
4423: 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);
4424: END IF;
4425: LOOP
4426: FETCH l_party_cur INTO
4427: l_party_id , l_TX32, l_TX34, l_TX35, l_TX39, l_TX158, l_TX40, l_TX43, l_TX42, l_TX44, l_TX46, l_TX47, l_TX48, l_TX156, l_TX157;
4443: l_party_id_idx:= l_party_id_idx+1;
4444: END IF;
4445: IF (l_party_id_idx-1)>l_max_thresh THEN
4446: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
4447: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
4448: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
4449: AND l_contact_pt_contains_str IS NULL THEN
4450: H_PARTY_ID.DELETE(l_party_id_idx-1);
4451: H_PARTY_ID_LIST.DELETE(l_index);
4445: IF (l_party_id_idx-1)>l_max_thresh THEN
4446: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
4447: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
4448: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
4449: AND l_contact_pt_contains_str IS NULL THEN
4450: H_PARTY_ID.DELETE(l_party_id_idx-1);
4451: H_PARTY_ID_LIST.DELETE(l_index);
4452: H_SCORES.DELETE(l_index);
4453: EXIT;
4474: CLOSE l_party_cur;
4475: ROLLBACK to eval_start;
4476: END;
4477: PROCEDURE open_person_contact_cursor(
4478: p_contains_str VARCHAR2,
4479: p_search_ctx_id NUMBER,
4480: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
4481: BEGIN
4482: OPEN x_cursor FOR
4481: BEGIN
4482: OPEN x_cursor FOR
4483: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
4484: FROM HZ_STAGED_CONTACTS stage
4485: WHERE contains( concat_col, p_contains_str)>0
4486: AND ORG_CONTACT_ID in (
4487: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
4488: org_contact_id
4489: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc
4505: end if;
4506: END;
4507:
4508: /** Private procedure to acquire and score at party site level ***/
4509: 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
4510: l_party_id_idx NUMBER:=1;
4511: l_ctx_id NUMBER;
4512: h_ps_id HZ_PARTY_SEARCH.IDList;
4513: h_ps_party_id HZ_PARTY_SEARCH.IDList;
4533: ELSE
4534: l_party_id_idx := H_PARTY_ID.COUNT+1;
4535: l_ctx_id := NULL;
4536: END IF;
4537: 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);
4538: LOOP
4539: FETCH l_party_site_cur INTO
4540: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX9, l_TX10, l_TX11, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;
4541: EXIT WHEN l_party_site_cur%NOTFOUND;
4604: END IF;
4605: END;
4606:
4607: /** Private procedure to acquire and score at party site level ***/
4608: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
4609: l_party_id_idx NUMBER:=1;
4610: l_ctx_id NUMBER;
4611: h_ct_id HZ_PARTY_SEARCH.IDList;
4612: h_ct_party_id HZ_PARTY_SEARCH.IDList;
4620: FORALL I in 1..H_PARTY_ID.COUNT
4621: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
4622: l_search_ctx_id,H_PARTY_ID(I));
4623: l_party_id_idx := H_PARTY_ID.COUNT+1;
4624: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
4625: LOOP
4626: FETCH l_contact_cur INTO
4627: l_org_contact_id, l_ct_party_id ;
4628: EXIT WHEN l_contact_cur%NOTFOUND;
4662: END IF;
4663: END;
4664:
4665: /** Private procedure to acquire and score at contact point level ***/
4666: 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
4667: l_party_id_idx NUMBER:=1;
4668: l_ctx_id NUMBER;
4669: h_cpt_id HZ_PARTY_SEARCH.IDList;
4670: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
4707: END IF;
4708: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4709: 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);
4710: END IF;
4711: 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);
4712: LOOP
4713: FETCH l_contact_pt_cur INTO
4714: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX3, l_TX4;
4715: EXIT WHEN l_contact_pt_cur%NOTFOUND;
4910: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
4911: l_threshold := round(( l_entered_max_score / 805) * 72);
4912: other_acq_criteria_exists := TRUE ;
4913: --check if acquisition criteria exists for any other entity
4914: IF l_party_contains_str IS NOT NULL THEN
4915: acq_cnt := 1;
4916: END IF;
4917: IF l_party_site_contains_str IS NOT NULL THEN
4918: acq_cnt := acq_cnt+1;
4913: --check if acquisition criteria exists for any other entity
4914: IF l_party_contains_str IS NOT NULL THEN
4915: acq_cnt := 1;
4916: END IF;
4917: IF l_party_site_contains_str IS NOT NULL THEN
4918: acq_cnt := acq_cnt+1;
4919: END IF;
4920: IF l_contact_contains_str IS NOT NULL THEN
4921: acq_cnt := acq_cnt+1;
4916: END IF;
4917: IF l_party_site_contains_str IS NOT NULL THEN
4918: acq_cnt := acq_cnt+1;
4919: END IF;
4920: IF l_contact_contains_str IS NOT NULL THEN
4921: acq_cnt := acq_cnt+1;
4922: END IF;
4923: IF l_contact_pt_contains_str IS NOT NULL THEN
4924: acq_cnt := acq_cnt+1;
4919: END IF;
4920: IF l_contact_contains_str IS NOT NULL THEN
4921: acq_cnt := acq_cnt+1;
4922: END IF;
4923: IF l_contact_pt_contains_str IS NOT NULL THEN
4924: acq_cnt := acq_cnt+1;
4925: END IF;
4926: IF acq_cnt>1 THEN
4927: other_acq_criteria_exists := TRUE;
4953: FND_MSG_PUB.ADD;
4954: RAISE FND_API.G_EXC_ERROR;
4955: END IF;
4956: END IF;
4957: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4958: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4959: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4960: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4961: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4954: RAISE FND_API.G_EXC_ERROR;
4955: END IF;
4956: END IF;
4957: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4958: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4959: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4960: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4961: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4962: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4955: END IF;
4956: END IF;
4957: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4958: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4959: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4960: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4961: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4962: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4963: END IF;
4956: END IF;
4957: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4958: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4959: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4960: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4961: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4962: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4963: END IF;
4964: END;
4957: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4958: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4959: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4960: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4961: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4962: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4963: END IF;
4964: END;
4965: /************ End of find_persons private procedures **********/
4958: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4959: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4960: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4961: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4962: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4963: END IF;
4964: END;
4965: /************ End of find_persons private procedures **********/
4966:
4993: g_score_until_thresh := false;
4994: END IF;
4995: -- ************************************************************
4996: -- STEP 2. Setup of intermedia query strings for Acquisition query
4997: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4998: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4999: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
5000: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
5001: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4994: END IF;
4995: -- ************************************************************
4996: -- STEP 2. Setup of intermedia query strings for Acquisition query
4997: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4998: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4999: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
5000: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
5001: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5002:
4995: -- ************************************************************
4996: -- STEP 2. Setup of intermedia query strings for Acquisition query
4997: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4998: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4999: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
5000: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
5001: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5002:
5003: -- Setup Search Context ID
4996: -- STEP 2. Setup of intermedia query strings for Acquisition query
4997: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4998: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4999: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
5000: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
5001: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5002:
5003: -- Setup Search Context ID
5004: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5003: -- Setup Search Context ID
5004: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5005: x_search_ctx_id := l_search_ctx_id;
5006:
5007: IF l_party_contains_str IS NULL THEN
5008: defpt := 1;
5009: END IF;
5010: IF l_party_site_contains_str IS NULL THEN
5011: defps := 1;
5006:
5007: IF l_party_contains_str IS NULL THEN
5008: defpt := 1;
5009: END IF;
5010: IF l_party_site_contains_str IS NULL THEN
5011: defps := 1;
5012: END IF;
5013: IF l_contact_contains_str IS NULL THEN
5014: defct := 1;
5009: END IF;
5010: IF l_party_site_contains_str IS NULL THEN
5011: defps := 1;
5012: END IF;
5013: IF l_contact_contains_str IS NULL THEN
5014: defct := 1;
5015: END IF;
5016: IF l_contact_pt_contains_str IS NULL THEN
5017: defcpt := 1;
5012: END IF;
5013: IF l_contact_contains_str IS NULL THEN
5014: defct := 1;
5015: END IF;
5016: IF l_contact_pt_contains_str IS NULL THEN
5017: defcpt := 1;
5018: END IF;
5019:
5020: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5018: END IF;
5019:
5020: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5021: 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);
5022: 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);
5023: 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);
5024: 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);
5025: 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);
5026: 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);
5019:
5020: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5021: 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);
5022: 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);
5023: 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);
5024: 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);
5025: 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);
5026: 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);
5027: END IF;
5020: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5021: 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);
5022: 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);
5023: 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);
5024: 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);
5025: 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);
5026: 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);
5027: END IF;
5028: FOR I in 1..5 LOOP
5021: 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);
5022: 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);
5023: 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);
5024: 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);
5025: 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);
5026: 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);
5027: END IF;
5028: FOR I in 1..5 LOOP
5029: do_eval(I);
5027: END IF;
5028: FOR I in 1..5 LOOP
5029: do_eval(I);
5030: END LOOP;
5031: IF l_contact_contains_str IS NOT NULL THEN
5032: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
5033: END IF;
5034: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5035: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 9 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5028: FOR I in 1..5 LOOP
5029: do_eval(I);
5030: END LOOP;
5031: IF l_contact_contains_str IS NOT NULL THEN
5032: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
5033: END IF;
5034: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5035: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 9 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5036: END IF;
5952: x_num_matches OUT NUMBER
5953: ) IS
5954:
5955: -- Strings to hold the generated Intermedia query strings
5956: l_party_contains_str VARCHAR2(32000);
5957: l_match_idx NUMBER;
5958: l_party_site_contains_str VARCHAR2(32000);
5959: l_contact_contains_str VARCHAR2(32000);
5960: l_contact_pt_contains_str VARCHAR2(32000);
5954:
5955: -- Strings to hold the generated Intermedia query strings
5956: l_party_contains_str VARCHAR2(32000);
5957: l_match_idx NUMBER;
5958: l_party_site_contains_str VARCHAR2(32000);
5959: l_contact_contains_str VARCHAR2(32000);
5960: l_contact_pt_contains_str VARCHAR2(32000);
5961: l_tmp VARCHAR2(32000);
5962:
5955: -- Strings to hold the generated Intermedia query strings
5956: l_party_contains_str VARCHAR2(32000);
5957: l_match_idx NUMBER;
5958: l_party_site_contains_str VARCHAR2(32000);
5959: l_contact_contains_str VARCHAR2(32000);
5960: l_contact_pt_contains_str VARCHAR2(32000);
5961: l_tmp VARCHAR2(32000);
5962:
5963: -- Other local variables
5956: l_party_contains_str VARCHAR2(32000);
5957: l_match_idx NUMBER;
5958: l_party_site_contains_str VARCHAR2(32000);
5959: l_contact_contains_str VARCHAR2(32000);
5960: l_contact_pt_contains_str VARCHAR2(32000);
5961: l_tmp VARCHAR2(32000);
5962:
5963: -- Other local variables
5964: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6041: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6042:
6043: -- ************************************************************
6044: -- STEP 2. Setup of intermedia query strings for Acquisition query
6045: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6046: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6047: 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);
6048:
6049: -- Setup Search Context ID
6042:
6043: -- ************************************************************
6044: -- STEP 2. Setup of intermedia query strings for Acquisition query
6045: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6046: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6047: 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);
6048:
6049: -- Setup Search Context ID
6050: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
6051: x_search_ctx_id := l_search_ctx_id;
6052:
6053: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6054: 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);
6055: 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);
6056: 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);
6057: 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);
6058: END IF;
6059: IF l_party_site_contains_str IS NULL THEN
6052:
6053: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6054: 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);
6055: 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);
6056: 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);
6057: 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);
6058: END IF;
6059: IF l_party_site_contains_str IS NULL THEN
6060: defps := 1;
6055: 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);
6056: 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);
6057: 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);
6058: END IF;
6059: IF l_party_site_contains_str IS NULL THEN
6060: defps := 1;
6061: END IF;
6062: IF l_contact_pt_contains_str IS NULL THEN
6063: defcpt := 1;
6058: END IF;
6059: IF l_party_site_contains_str IS NULL THEN
6060: defps := 1;
6061: END IF;
6062: IF l_contact_pt_contains_str IS NULL THEN
6063: defcpt := 1;
6064: END IF;
6065:
6066: IF l_party_site_contains_str IS NOT NULL THEN
6062: IF l_contact_pt_contains_str IS NULL THEN
6063: defcpt := 1;
6064: END IF;
6065:
6066: IF l_party_site_contains_str IS NOT NULL THEN
6067: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
6068: LOOP
6069: FETCH l_party_site_cur INTO
6070: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX9, l_TX10, l_TX11, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;
6063: defcpt := 1;
6064: END IF;
6065:
6066: IF l_party_site_contains_str IS NOT NULL THEN
6067: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
6068: LOOP
6069: FETCH l_party_site_cur INTO
6070: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX9, l_TX10, l_TX11, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;
6071: EXIT WHEN l_party_site_cur%NOTFOUND;
6086: END LOOP;
6087: CLOSE l_party_site_cur;
6088: END IF;
6089:
6090: IF l_contact_pt_contains_str IS NOT NULL THEN
6091: SAVEPOINT eval_start;
6092: IF l_match_str = ' AND ' OR (
6093: ((l_cpt_max_score/l_entered_max_score)<(72/805))
6094: ) THEN
6094: ) THEN
6095: FORALL I in 1..l_match_ps_list.COUNT
6096: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
6097: values (l_search_ctx_id,l_match_ps_list(I));
6098: 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');
6099: ELSE
6100: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6101: END IF;
6102: LOOP
6096: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
6097: values (l_search_ctx_id,l_match_ps_list(I));
6098: 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');
6099: ELSE
6100: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6101: END IF;
6102: LOOP
6103: FETCH l_contact_pt_cur INTO
6104: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX3, l_TX4;
6196: x_num_matches OUT NUMBER
6197: ) IS
6198:
6199: -- Strings to hold the generated Intermedia query strings
6200: l_party_contains_str VARCHAR2(32000);
6201: l_party_site_contains_str VARCHAR2(32000);
6202: l_contact_contains_str VARCHAR2(32000);
6203: l_contact_pt_contains_str VARCHAR2(32000);
6204: l_tmp VARCHAR2(32000);
6197: ) IS
6198:
6199: -- Strings to hold the generated Intermedia query strings
6200: l_party_contains_str VARCHAR2(32000);
6201: l_party_site_contains_str VARCHAR2(32000);
6202: l_contact_contains_str VARCHAR2(32000);
6203: l_contact_pt_contains_str VARCHAR2(32000);
6204: l_tmp VARCHAR2(32000);
6205:
6198:
6199: -- Strings to hold the generated Intermedia query strings
6200: l_party_contains_str VARCHAR2(32000);
6201: l_party_site_contains_str VARCHAR2(32000);
6202: l_contact_contains_str VARCHAR2(32000);
6203: l_contact_pt_contains_str VARCHAR2(32000);
6204: l_tmp VARCHAR2(32000);
6205:
6206: -- Other local variables
6199: -- Strings to hold the generated Intermedia query strings
6200: l_party_contains_str VARCHAR2(32000);
6201: l_party_site_contains_str VARCHAR2(32000);
6202: l_contact_contains_str VARCHAR2(32000);
6203: l_contact_pt_contains_str VARCHAR2(32000);
6204: l_tmp VARCHAR2(32000);
6205:
6206: -- Other local variables
6207: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6273: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6274:
6275: -- ************************************************************
6276: -- STEP 2. Setup of intermedia query strings for Acquisition query
6277: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6278: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6279: 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);
6280:
6281: -- Setup Search Context ID
6274:
6275: -- ************************************************************
6276: -- STEP 2. Setup of intermedia query strings for Acquisition query
6277: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6278: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6279: 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);
6280:
6281: -- Setup Search Context ID
6282: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
6283: x_search_ctx_id := l_search_ctx_id;
6284:
6285: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6286: 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);
6287: 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);
6288: 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);
6289: 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);
6290: END IF;
6291: IF l_contact_contains_str IS NULL THEN
6284:
6285: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6286: 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);
6287: 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);
6288: 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);
6289: 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);
6290: END IF;
6291: IF l_contact_contains_str IS NULL THEN
6292: defct := 1;
6287: 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);
6288: 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);
6289: 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);
6290: END IF;
6291: IF l_contact_contains_str IS NULL THEN
6292: defct := 1;
6293: END IF;
6294: IF l_contact_pt_contains_str IS NULL THEN
6295: defcpt := 1;
6290: END IF;
6291: IF l_contact_contains_str IS NULL THEN
6292: defct := 1;
6293: END IF;
6294: IF l_contact_pt_contains_str IS NULL THEN
6295: defcpt := 1;
6296: END IF;
6297:
6298: IF l_contact_contains_str IS NOT NULL THEN
6294: IF l_contact_pt_contains_str IS NULL THEN
6295: defcpt := 1;
6296: END IF;
6297:
6298: IF l_contact_contains_str IS NOT NULL THEN
6299: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
6300: LOOP
6301: FETCH l_contact_cur INTO
6302: l_org_contact_id, l_ct_party_id ;
6295: defcpt := 1;
6296: END IF;
6297:
6298: IF l_contact_contains_str IS NOT NULL THEN
6299: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
6300: LOOP
6301: FETCH l_contact_cur INTO
6302: l_org_contact_id, l_ct_party_id ;
6303: EXIT WHEN l_contact_cur%NOTFOUND;
6316: END LOOP;
6317: CLOSE l_contact_cur;
6318: END IF;
6319:
6320: IF l_contact_pt_contains_str IS NOT NULL THEN
6321: SAVEPOINT eval_start;
6322: IF l_match_str = ' AND ' OR (
6323: ((l_cpt_max_score/l_entered_max_score)<(72/805))
6324: ) THEN
6324: ) THEN
6325: FORALL I in 1..l_match_contact_list.COUNT
6326: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
6327: values (l_search_ctx_id,l_match_contact_list(I));
6328: 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');
6329: ELSE
6330: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6331: END IF;
6332: LOOP
6326: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
6327: values (l_search_ctx_id,l_match_contact_list(I));
6328: 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');
6329: ELSE
6330: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6331: END IF;
6332: LOOP
6333: FETCH l_contact_pt_cur INTO
6334: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX3, l_TX4;
6424: ) IS
6425:
6426:
6427: -- Strings to hold the generated Intermedia query strings
6428: l_contact_pt_contains_str VARCHAR2(32000);
6429: l_tmp VARCHAR2(32000);
6430:
6431: -- Other local variables
6432: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6485: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6486:
6487: -- ************************************************************
6488: -- STEP 2. Setup of intermedia query strings for Acquisition query
6489: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6490: 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);
6491:
6492: -- Setup Search Context ID
6493: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
6493: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
6494: x_search_ctx_id := l_search_ctx_id;
6495: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6496: 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);
6497: 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);
6498: 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);
6499: END IF;
6500:
6501: IF l_contact_pt_contains_str IS NOT NULL THEN
6497: 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);
6498: 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);
6499: END IF;
6500:
6501: IF l_contact_pt_contains_str IS NOT NULL THEN
6502: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6503: cnt := 1;
6504: LOOP
6505: FETCH l_contact_pt_cur INTO
6498: 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);
6499: END IF;
6500:
6501: IF l_contact_pt_contains_str IS NOT NULL THEN
6502: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6503: cnt := 1;
6504: LOOP
6505: FETCH l_contact_pt_cur INTO
6506: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX3, l_TX4;
6569: x_search_ctx_id IN OUT NUMBER
6570: ) IS
6571:
6572: -- Strings to hold the generated Intermedia query strings
6573: l_party_contains_str VARCHAR2(32000);
6574: l_party_site_contains_str VARCHAR2(32000);
6575: l_contact_contains_str VARCHAR2(32000);
6576: l_contact_pt_contains_str VARCHAR2(32000);
6577: l_tmp VARCHAR2(32000);
6570: ) IS
6571:
6572: -- Strings to hold the generated Intermedia query strings
6573: l_party_contains_str VARCHAR2(32000);
6574: l_party_site_contains_str VARCHAR2(32000);
6575: l_contact_contains_str VARCHAR2(32000);
6576: l_contact_pt_contains_str VARCHAR2(32000);
6577: l_tmp VARCHAR2(32000);
6578:
6571:
6572: -- Strings to hold the generated Intermedia query strings
6573: l_party_contains_str VARCHAR2(32000);
6574: l_party_site_contains_str VARCHAR2(32000);
6575: l_contact_contains_str VARCHAR2(32000);
6576: l_contact_pt_contains_str VARCHAR2(32000);
6577: l_tmp VARCHAR2(32000);
6578:
6579: -- Other local variables
6572: -- Strings to hold the generated Intermedia query strings
6573: l_party_contains_str VARCHAR2(32000);
6574: l_party_site_contains_str VARCHAR2(32000);
6575: l_contact_contains_str VARCHAR2(32000);
6576: l_contact_pt_contains_str VARCHAR2(32000);
6577: l_tmp VARCHAR2(32000);
6578:
6579: -- Other local variables
6580: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6682: g_score_until_thresh := false;
6683: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6684: -- ************************************************************
6685: -- STEP 2. Setup of intermedia query strings for Acquisition query
6686: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6687: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6688: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6689: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6690:
6683: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6684: -- ************************************************************
6685: -- STEP 2. Setup of intermedia query strings for Acquisition query
6686: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6687: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6688: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6689: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6690:
6691: -- Setup Search Context ID
6684: -- ************************************************************
6685: -- STEP 2. Setup of intermedia query strings for Acquisition query
6686: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6687: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6688: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6689: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6690:
6691: -- Setup Search Context ID
6692: IF x_search_ctx_id IS NULL THEN
6706: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX32,l_TX34,l_TX35_new,l_TX39,l_TX158,l_TX40,l_TX43,l_TX42,l_TX44,l_TX46,l_TX47,l_TX48,l_TX156,l_TX157,1);
6707: END LOOP;
6708: CLOSE l_party_cur;
6709:
6710: IF l_party_site_contains_str IS NOT NULL THEN
6711: l_max_score := 0;
6712: l_max_id := 0;
6713: l_max_idx := 0;
6714: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6711: l_max_score := 0;
6712: l_max_id := 0;
6713: l_max_idx := 0;
6714: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6715: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
6716: ELSE
6717: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
6718: END IF;
6719: LOOP
6713: l_max_idx := 0;
6714: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6715: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
6716: ELSE
6717: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
6718: END IF;
6719: LOOP
6720: FETCH l_party_site_cur INTO
6721: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX9, l_TX10, l_TX11, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;
6741: 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_TX9,l_max_TX10,l_max_TX11,l_max_TX13,l_max_TX14,l_max_TX15,l_max_TX20,l_max_TX21,l_max_TX22,l_max_idx);
6742: END IF;
6743: END IF;
6744:
6745: IF l_contact_contains_str IS NOT NULL THEN
6746: l_max_score := 0;
6747: l_max_id := 0;
6748: l_max_idx := 0;
6749: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
6745: IF l_contact_contains_str IS NOT NULL THEN
6746: l_max_score := 0;
6747: l_max_id := 0;
6748: l_max_idx := 0;
6749: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
6750: LOOP
6751: FETCH l_contact_cur INTO
6752: l_org_contact_id, l_ct_party_id ;
6753: EXIT WHEN l_contact_cur%NOTFOUND;
6763: INSERT_CONTACTS_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_contact_list(l_max_idx), g_contact_stage_list(l_max_idx) ,l_max_idx);
6764: END IF;
6765: END IF;
6766:
6767: IF l_contact_pt_contains_str IS NOT NULL THEN
6768: l_max_score := 0;
6769: l_max_id := 0;
6770: l_max_idx := 0;
6771: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6768: l_max_score := 0;
6769: l_max_id := 0;
6770: l_max_idx := 0;
6771: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6772: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
6773: ELSE
6774: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
6775: END IF;
6776: LOOP
6770: l_max_idx := 0;
6771: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6772: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
6773: ELSE
6774: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
6775: END IF;
6776: LOOP
6777: FETCH l_contact_pt_cur INTO
6778: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX3, l_TX4;