[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 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)
2437: END IF;
2438: IF p_search_ctx_id IS NULL THEN
2439: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX32, TX34, TX35, TX39, TX158, TX40, TX43, TX42, TX44, TX46, TX47, TX48, TX156, TX157'||
2440: ' FROM HZ_STAGED_PARTIES stage'||
2441: ' WHERE contains( concat_col, :cont)>0'||
2442: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
2443: ' AND ('||p_restrict_sql||')' ||
2444: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2445: IF l_search_merged = 'Y' THEN
2449: ELSE
2450: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
2451: END IF;
2452: output_long_strings('----------------------------------------------------------');
2453: output_long_strings('Party Contains String = '||p_contains_str);
2454: output_long_strings('Restrict Sql = '||p_restrict_sql);
2455: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2456: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2457: ,p_dup_party_id, p_dup_party_id;
2451: END IF;
2452: output_long_strings('----------------------------------------------------------');
2453: output_long_strings('Party Contains String = '||p_contains_str);
2454: output_long_strings('Restrict Sql = '||p_restrict_sql);
2455: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2456: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2457: ,p_dup_party_id, p_dup_party_id;
2458: END IF;
2459: END IF;
2482: PROCEDURE open_party_site_cursor(
2483: p_dup_party_id NUMBER,
2484: p_party_id NUMBER,
2485: p_restrict_sql VARCHAR2,
2486: p_contains_str VARCHAR2,
2487: p_search_ctx_id NUMBER,
2488: p_search_merged VARCHAR2,
2489: p_search_rel_sites VARCHAR2,
2490: p_person_api VARCHAR2,
2514: IF p_search_rel_sites = 'N' THEN
2515: OPEN x_cursor FOR
2516: 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
2517: FROM HZ_STAGED_PARTY_SITES stage
2518: WHERE contains( concat_col, p_contains_str)>0
2519: AND( (l_search_merged ='Y' )
2520: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2521: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2522: AND stage.party_id = p_party_id;
2523: ELSE
2524: OPEN x_cursor FOR
2525: 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
2526: FROM HZ_STAGED_PARTY_SITES stage
2527: WHERE contains( concat_col, p_contains_str)>0
2528: AND( (l_search_merged ='Y' )
2529: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2530: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2531: AND stage.party_id = p_party_id
2531: AND stage.party_id = p_party_id
2532: UNION
2533: 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
2534: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
2535: WHERE contains( concat_col, p_contains_str)>0
2536: AND( (l_search_merged ='Y' )
2537: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2538: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2539: AND r.object_id = p_party_id
2553: END IF;
2554: OPEN x_cursor FOR
2555: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22
2556: FROM HZ_STAGED_PARTY_SITES stage
2557: WHERE contains( concat_col, p_contains_str)>0
2558: AND EXISTS (
2559: SELECT 1 FROM HZ_STAGED_PARTIES p
2560: WHERE p.PARTY_ID = stage.PARTY_ID
2561: AND( (l_search_merged ='Y' )
2573: IF p_person_api = 'Y' THEN
2574: OPEN x_cursor FOR
2575: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22
2576: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
2577: WHERE contains( concat_col, p_contains_str)>0
2578: AND( (l_search_merged ='Y' )
2579: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2580: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2581: AND d.search_context_id = p_search_ctx_id
2583: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2584: UNION
2585: 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
2586: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
2587: WHERE contains( concat_col, p_contains_str)>0
2588: AND( (l_search_merged ='Y' )
2589: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2590: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2591: AND d.search_context_id = p_search_ctx_id
2596: ELSE
2597: OPEN x_cursor FOR
2598: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22
2599: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
2600: WHERE contains( concat_col, p_contains_str)>0
2601: AND( (l_search_merged ='Y' )
2602: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2603: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2604: AND d.search_context_id = p_search_ctx_id
2641: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2642: END IF;
2643: 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'||
2644: ' FROM HZ_STAGED_PARTY_SITES stage'||
2645: ' WHERE contains( concat_col, :cont)>0'||
2646: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2647: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2648: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2649: ' AND (ORG_CONTACT_ID IS NULL ' ||
2651: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2652: ' UNION ' ||
2653: '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'||
2654: ' FROM HZ_STAGED_PARTY_SITES stage'||
2655: ' WHERE contains( concat_col, :cont)>0'||
2656: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2657: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2658: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2659: ' AND ORG_CONTACT_ID IN ' ||
2661: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2662: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2663: ' and ('||p_restrict_sql1|| ') )' ||
2664: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2665: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2666: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2667: ELSE
2668: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22'||
2669: ' FROM HZ_STAGED_PARTY_SITES stage'||
2662: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2663: ' and ('||p_restrict_sql1|| ') )' ||
2664: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2665: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2666: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2667: ELSE
2668: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22'||
2669: ' FROM HZ_STAGED_PARTY_SITES stage'||
2670: ' WHERE contains( concat_col, :cont)>0'||
2666: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2667: ELSE
2668: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22'||
2669: ' FROM HZ_STAGED_PARTY_SITES stage'||
2670: ' WHERE contains( concat_col, :cont)>0'||
2671: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2672: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2673: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2674: ' AND EXISTS ('||
2676: ' WHERE p.party_id = stage.party_id ' ||
2677: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2678: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2679: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2680: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2681: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2682: ,p_dup_party_id, p_dup_party_id;
2683: END IF;
2684: ELSE
2683: END IF;
2684: ELSE
2685: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22'||
2686: ' FROM HZ_STAGED_PARTY_SITES stage'||
2687: ' WHERE contains( concat_col, :cont)>0'||
2688: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2689: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2690: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2691: ' AND EXISTS ('||
2693: ' WHERE p.party_id = stage.party_id ' ||
2694: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2695: ' AND ('||p_restrict_sql||')' ||
2696: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2697: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2698: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2699: ,p_dup_party_id, p_dup_party_id;
2700: END IF;
2701: END IF;
2699: ,p_dup_party_id, p_dup_party_id;
2700: END IF;
2701: END IF;
2702: output_long_strings('----------------------------------------------------------');
2703: output_long_strings('Party Site Contains String = '||p_contains_str);
2704: output_long_strings('Restrict Sql = '||p_restrict_sql);
2705: exception
2706: when others then
2707: if (instrb(SQLERRM,'DRG-51030')>0) then
2727: PROCEDURE open_contact_cursor(
2728: p_dup_party_id NUMBER,
2729: p_party_id NUMBER,
2730: p_restrict_sql VARCHAR2,
2731: p_contains_str VARCHAR2,
2732: p_search_ctx_id NUMBER,
2733: p_search_merged VARCHAR2,
2734: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
2735: l_sqlstr VARCHAR2(4000);
2755: END IF;
2756: OPEN x_cursor FOR
2757: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
2758: FROM HZ_STAGED_CONTACTS stage
2759: WHERE contains( concat_col, p_contains_str)>0
2760: AND EXISTS (
2761: SELECT 1 FROM HZ_STAGED_PARTIES p
2762: WHERE p.PARTY_ID = stage.PARTY_ID
2763: AND( (l_search_merged ='Y' )
2778: END IF;
2779: OPEN x_cursor FOR
2780: SELECT ORG_CONTACT_ID, PARTY_ID
2781: FROM HZ_STAGED_CONTACTS stage
2782: WHERE contains( concat_col, p_contains_str)>0
2783: AND EXISTS (
2784: SELECT 1 FROM HZ_STAGED_PARTIES p
2785: WHERE p.PARTY_ID = stage.PARTY_ID
2786: AND( (l_search_merged ='Y' )
2797: END IF;
2798: OPEN x_cursor FOR
2799: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
2800: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
2801: WHERE contains( concat_col, p_contains_str)>0
2802: AND d.search_context_id = p_search_ctx_id
2803: AND d.party_id = stage.party_id
2804: AND( (l_search_merged ='Y' )
2805: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2831: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
2832: END IF;
2833: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
2834: ' FROM HZ_STAGED_CONTACTS stage'||
2835: ' WHERE contains( concat_col, :cont)>0'||
2836: ' AND EXISTS ('||
2837: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2838: ' WHERE p.party_id = stage.party_id ' ||
2839: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2841: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2842: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2843: ' AND ('||p_restrict_sql||')' ||
2844: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2845: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2846: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2847: ,p_dup_party_id, p_dup_party_id;
2848: END IF;
2849: output_long_strings('----------------------------------------------------------');
2846: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2847: ,p_dup_party_id, p_dup_party_id;
2848: END IF;
2849: output_long_strings('----------------------------------------------------------');
2850: output_long_strings('Contacts Contains String = '||p_contains_str);
2851: output_long_strings('Restrict Sql = '||p_restrict_sql);
2852: exception
2853: when others then
2854: if (instrb(SQLERRM,'DRG-51030')>0) then
2874: PROCEDURE open_contact_pt_cursor(
2875: p_dup_party_id NUMBER,
2876: p_party_id NUMBER,
2877: p_restrict_sql VARCHAR2,
2878: p_contains_str VARCHAR2,
2879: p_search_ctx_id NUMBER,
2880: p_search_merged VARCHAR2,
2881: p_search_rel_cpts VARCHAR2,
2882: p_person_api VARCHAR2,
2904: THEN
2905: OPEN x_cursor FOR
2906: 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
2907: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2908: WHERE contains( concat_col, p_contains_str)>0
2909: AND d.search_context_id = p_search_ctx_id
2910: AND( (l_search_merged ='Y' )
2911: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2912: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2916: THEN
2917: OPEN x_cursor FOR
2918: 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
2919: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2920: WHERE contains( concat_col, p_contains_str)>0
2921: AND d.search_context_id = p_search_ctx_id
2922: AND( (l_search_merged ='Y' )
2923: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2924: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2933: IF p_search_rel_cpts = 'N' THEN
2934: OPEN x_cursor FOR
2935: 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
2936: FROM HZ_STAGED_CONTACT_POINTS stage
2937: WHERE contains( concat_col, p_contains_str)>0
2938: AND( (l_search_merged ='Y' )
2939: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2940: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2941: AND stage.party_id = p_party_id;
2942: ELSE
2943: OPEN x_cursor FOR
2944: 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
2945: FROM HZ_STAGED_CONTACT_POINTS stage
2946: WHERE contains( concat_col, p_contains_str)>0
2947: AND( (l_search_merged ='Y' )
2948: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2949: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2950: AND stage.party_id = p_party_id
2950: AND stage.party_id = p_party_id
2951: UNION
2952: 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
2953: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2954: WHERE contains( concat_col, p_contains_str)>0
2955: AND r.object_id = p_party_id
2956: AND( (l_search_merged ='Y' )
2957: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2958: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2972: END IF;
2973: OPEN x_cursor FOR
2974: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4
2975: FROM HZ_STAGED_CONTACT_POINTS stage
2976: WHERE contains( concat_col, p_contains_str)>0
2977: AND EXISTS (
2978: SELECT 1 FROM HZ_STAGED_PARTIES p
2979: WHERE p.PARTY_ID = stage.PARTY_ID
2980: AND( (l_search_merged ='Y' )
2992: IF p_person_api = 'Y' THEN
2993: OPEN x_cursor FOR
2994: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4
2995: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2996: WHERE contains( concat_col, p_contains_str)>0
2997: AND d.search_context_id = p_search_ctx_id
2998: AND d.party_id = stage.party_id
2999: AND( (l_search_merged ='Y' )
3000: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
3002: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
3003: UNION
3004: 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
3005: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
3006: WHERE contains( concat_col, p_contains_str)>0
3007: AND d.search_context_id = p_search_ctx_id
3008: AND d.party_id = r.subject_id
3009: AND r.relationship_id = oc.party_relationship_id
3010: AND oc.org_contact_id = stage.org_contact_id
3015: ELSE
3016: OPEN x_cursor FOR
3017: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4
3018: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
3019: WHERE contains( concat_col, p_contains_str)>0
3020: AND d.search_context_id = p_search_ctx_id
3021: AND d.party_id = stage.party_id
3022: AND( (l_search_merged ='Y' )
3023: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
3060: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
3061: END IF;
3062: 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'||
3063: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3064: ' WHERE contains( concat_col, :cont)>0 '||
3065: ' AND (stage.org_contact_id is null '||
3066: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3067: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
3068: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
3070: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
3071: ' UNION ' ||
3072: ' 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'||
3073: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3074: ' WHERE contains( concat_col, :cont)>0 '||
3075: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3076: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
3077: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
3078: ' AND (stage.org_contact_id in ' ||
3080: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
3081: ' where stage1.relationship_id = oc.party_relationship_id ' ||
3082: ' and ('||p_restrict_sql1 || ') ) )' ||
3083: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
3084: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
3085: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
3086: ELSE
3087: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4'||
3088: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3081: ' where stage1.relationship_id = oc.party_relationship_id ' ||
3082: ' and ('||p_restrict_sql1 || ') ) )' ||
3083: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
3084: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
3085: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
3086: ELSE
3087: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4'||
3088: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3089: ' WHERE contains( concat_col, :cont)>0'||
3085: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
3086: ELSE
3087: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4'||
3088: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3089: ' WHERE contains( concat_col, :cont)>0'||
3090: ' AND EXISTS ('||
3091: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
3092: ' WHERE p.party_id = stage.party_id ' ||
3093: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
3095: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
3096: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
3097: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
3098: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
3099: OPEN x_cursor FOR l_sqlstr USING p_contains_str
3100: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
3101: ,p_dup_party_id, p_dup_party_id;
3102: END IF;
3103: ELSE
3102: END IF;
3103: ELSE
3104: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4'||
3105: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3106: ' WHERE contains( concat_col, :cont)>0'||
3107: ' AND EXISTS ('||
3108: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
3109: ' WHERE p.party_id = stage.party_id ' ||
3110: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
3112: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
3113: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
3114: ' AND ('||p_restrict_sql||')' ||
3115: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
3116: OPEN x_cursor FOR l_sqlstr USING p_contains_str
3117: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
3118: ,p_dup_party_id, p_dup_party_id;
3119: END IF;
3120: END IF;
3119: END IF;
3120: END IF;
3121: END IF;
3122: output_long_strings('----------------------------------------------------------');
3123: output_long_strings('Contact Points Contains String = '||p_contains_str);
3124: output_long_strings('Restrict Sql = '||p_restrict_sql);
3125: exception
3126: when others then
3127: if (instrb(SQLERRM,'DRG-51030')>0) then
3240: x_num_matches OUT NUMBER
3241: ) IS
3242:
3243: -- Strings to hold the generated Intermedia query strings
3244: l_party_contains_str VARCHAR2(32000);
3245: l_party_site_contains_str VARCHAR2(32000);
3246: l_contact_contains_str VARCHAR2(32000);
3247: l_contact_pt_contains_str VARCHAR2(32000);
3248: l_denorm_str VARCHAR2(32000);
3241: ) IS
3242:
3243: -- Strings to hold the generated Intermedia query strings
3244: l_party_contains_str VARCHAR2(32000);
3245: l_party_site_contains_str VARCHAR2(32000);
3246: l_contact_contains_str VARCHAR2(32000);
3247: l_contact_pt_contains_str VARCHAR2(32000);
3248: l_denorm_str VARCHAR2(32000);
3249: l_ps_denorm_str VARCHAR2(32000);
3242:
3243: -- Strings to hold the generated Intermedia query strings
3244: l_party_contains_str VARCHAR2(32000);
3245: l_party_site_contains_str VARCHAR2(32000);
3246: l_contact_contains_str VARCHAR2(32000);
3247: l_contact_pt_contains_str VARCHAR2(32000);
3248: l_denorm_str VARCHAR2(32000);
3249: l_ps_denorm_str VARCHAR2(32000);
3250: l_ct_denorm_str VARCHAR2(32000);
3243: -- Strings to hold the generated Intermedia query strings
3244: l_party_contains_str VARCHAR2(32000);
3245: l_party_site_contains_str VARCHAR2(32000);
3246: l_contact_contains_str VARCHAR2(32000);
3247: l_contact_pt_contains_str VARCHAR2(32000);
3248: l_denorm_str VARCHAR2(32000);
3249: l_ps_denorm_str VARCHAR2(32000);
3250: l_ct_denorm_str VARCHAR2(32000);
3251: l_cpt_denorm_str VARCHAR2(32000);
3354: call_type(2) := 'OR';
3355: END;
3356:
3357: /** Private procedure to acquire and score at party level ***/
3358: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3359: l_party_id_idx NUMBER:=1;
3360: l_ctx_id NUMBER;
3361: l_precalc_score BOOLEAN := FALSE;
3362: BEGIN
3385: l_search_ctx_id,H_PARTY_ID(I));
3386: l_party_id_idx := H_PARTY_ID.COUNT+1;
3387: ELSE
3388: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3389: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3390: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3391: 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);
3392: END IF;
3393: 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
3393: 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
3394: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3395: 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);
3396: END IF;
3397: IF (l_party_site_contains_str IS NULL AND
3398: l_contact_contains_str IS NULL AND
3399: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3400: 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);
3401: RAISE L_RETURN_IMM_EXC;
3394: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3395: 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);
3396: END IF;
3397: IF (l_party_site_contains_str IS NULL AND
3398: l_contact_contains_str IS NULL AND
3399: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3400: 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);
3401: RAISE L_RETURN_IMM_EXC;
3402: ELSE
3395: 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);
3396: END IF;
3397: IF (l_party_site_contains_str IS NULL AND
3398: l_contact_contains_str IS NULL AND
3399: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3400: 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);
3401: RAISE L_RETURN_IMM_EXC;
3402: ELSE
3403: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
3399: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3400: 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);
3401: RAISE L_RETURN_IMM_EXC;
3402: ELSE
3403: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
3404: END IF;
3405: ELSE
3406: IF p_index>1 THEN
3407: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3422: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3423: 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);
3424: END IF;
3425: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3426: 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);
3427: END IF;
3428: LOOP
3429: FETCH l_party_cur INTO
3430: 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;
3444: l_party_id_idx:= l_party_id_idx+1;
3445: END IF;
3446: IF (l_party_id_idx-1)>l_max_thresh THEN
3447: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3448: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3449: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3450: AND l_contact_pt_contains_str IS NULL THEN
3451: EXIT;
3452: END IF;
3446: IF (l_party_id_idx-1)>l_max_thresh THEN
3447: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3448: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3449: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3450: AND l_contact_pt_contains_str IS NULL THEN
3451: EXIT;
3452: END IF;
3453: CLOSE l_party_cur;
3454: IF p_index>1 THEN
3475: ROLLBACK to eval_start;
3476: END;
3477:
3478: /** Private procedure to acquire and score at party site level ***/
3479: 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
3480: l_party_id_idx NUMBER:=1;
3481: l_ctx_id NUMBER;
3482: h_ps_id HZ_PARTY_SEARCH.IDList;
3483: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3487: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3488: 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);
3489: 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);
3490: END IF;
3491: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
3492: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
3493: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
3494: l_party_id := H_SCORES.FIRST;
3495: WHILE l_party_id IS NOT NULL LOOP
3488: 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);
3489: 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);
3490: END IF;
3491: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
3492: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
3493: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
3494: l_party_id := H_SCORES.FIRST;
3495: WHILE l_party_id IS NOT NULL LOOP
3496: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
3489: 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);
3490: END IF;
3491: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
3492: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
3493: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
3494: l_party_id := H_SCORES.FIRST;
3495: WHILE l_party_id IS NOT NULL LOOP
3496: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
3497: l_party_id:=H_SCORES.NEXT(l_party_id);
3519: l_search_ctx_id,H_PARTY_ID(I));
3520: l_party_id_idx := H_PARTY_ID.COUNT+1;
3521: ELSE
3522: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3523: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
3524: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3525: 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);
3526: END IF;
3527: IF p_index>1 THEN
3541: END IF;
3542: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3543: 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);
3544: END IF;
3545: 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);
3546: LOOP
3547: FETCH l_party_site_cur INTO
3548: 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;
3549: EXIT WHEN l_party_site_cur%NOTFOUND;
3597: END IF;
3598: END;
3599:
3600: /** Private procedure to acquire and score at contact point level ***/
3601: 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
3602: l_party_id_idx NUMBER:=1;
3603: l_ctx_id NUMBER;
3604: h_ct_id HZ_PARTY_SEARCH.IDList;
3605: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3631: l_search_ctx_id,H_PARTY_ID(I));
3632: l_party_id_idx := H_PARTY_ID.COUNT+1;
3633: ELSE
3634: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3635: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
3636: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3637: 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);
3638: END IF;
3639: IF p_index>1 THEN
3653: END IF;
3654: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3655: 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);
3656: END IF;
3657: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
3658: LOOP
3659: FETCH l_contact_cur INTO
3660: l_org_contact_id, l_ct_party_id ;
3661: EXIT WHEN l_contact_cur%NOTFOUND;
3706: END IF;
3707: END;
3708:
3709: /** Private procedure to acquire and score at contact point level ***/
3710: 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
3711: l_party_id_idx NUMBER:=1;
3712: l_ctx_id NUMBER;
3713: h_cpt_id HZ_PARTY_SEARCH.IDList;
3714: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3742: l_search_ctx_id,H_PARTY_ID(I));
3743: l_party_id_idx := H_PARTY_ID.COUNT+1;
3744: ELSE
3745: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3746: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
3747: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3748: 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);
3749: END IF;
3750: IF p_index>1 THEN
3764: END IF;
3765: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3766: 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);
3767: END IF;
3768: 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);
3769: LOOP
3770: FETCH l_contact_pt_cur INTO
3771: 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;
3772: EXIT WHEN l_contact_pt_cur%NOTFOUND;
3945: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3946: l_threshold := round(( l_entered_max_score / 805) * 72);
3947: other_acq_criteria_exists := TRUE ;
3948: --check if acquisition criteria exists for any other entity
3949: IF l_party_contains_str IS NOT NULL THEN
3950: acq_cnt := 1;
3951: END IF;
3952: IF l_party_site_contains_str IS NOT NULL THEN
3953: acq_cnt := acq_cnt+1;
3948: --check if acquisition criteria exists for any other entity
3949: IF l_party_contains_str IS NOT NULL THEN
3950: acq_cnt := 1;
3951: END IF;
3952: IF l_party_site_contains_str IS NOT NULL THEN
3953: acq_cnt := acq_cnt+1;
3954: END IF;
3955: IF l_contact_contains_str IS NOT NULL THEN
3956: acq_cnt := acq_cnt+1;
3951: END IF;
3952: IF l_party_site_contains_str IS NOT NULL THEN
3953: acq_cnt := acq_cnt+1;
3954: END IF;
3955: IF l_contact_contains_str IS NOT NULL THEN
3956: acq_cnt := acq_cnt+1;
3957: END IF;
3958: IF l_contact_pt_contains_str IS NOT NULL THEN
3959: acq_cnt := acq_cnt+1;
3954: END IF;
3955: IF l_contact_contains_str IS NOT NULL THEN
3956: acq_cnt := acq_cnt+1;
3957: END IF;
3958: IF l_contact_pt_contains_str IS NOT NULL THEN
3959: acq_cnt := acq_cnt+1;
3960: END IF;
3961: IF acq_cnt>1 THEN
3962: other_acq_criteria_exists := TRUE;
3988: FND_MSG_PUB.ADD;
3989: RAISE FND_API.G_EXC_ERROR;
3990: END IF;
3991: END IF;
3992: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3993: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3994: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3995: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3996: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3989: RAISE FND_API.G_EXC_ERROR;
3990: END IF;
3991: END IF;
3992: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3993: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3994: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3995: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3996: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3997: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3990: END IF;
3991: END IF;
3992: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3993: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3994: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3995: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3996: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3997: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3998: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3991: END IF;
3992: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3993: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3994: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3995: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3996: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3997: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3998: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3999: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3992: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3993: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3994: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3995: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3996: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3997: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3998: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3999: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4000: END IF;
3993: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3994: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3995: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3996: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3997: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3998: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3999: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4000: END IF;
4001: END;
3994: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3995: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3996: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3997: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3998: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3999: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4000: END IF;
4001: END;
4002: /************ End of find_parties private procedures **********/
3995: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3996: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3997: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3998: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3999: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4000: END IF;
4001: END;
4002: /************ End of find_parties private procedures **********/
4003:
4067: 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);
4068: END IF;
4069: -- ************************************************************
4070: -- STEP 2. Setup of intermedia query strings for Acquisition query
4071: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4072: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4073: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4074: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4075: 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);
4068: END IF;
4069: -- ************************************************************
4070: -- STEP 2. Setup of intermedia query strings for Acquisition query
4071: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4072: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4073: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4074: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4075: 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);
4076: END IF;
4069: -- ************************************************************
4070: -- STEP 2. Setup of intermedia query strings for Acquisition query
4071: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4072: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4073: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4074: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4075: 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);
4076: END IF;
4077: l_denorm_max_score:=0;
4101: END IF;
4102: ELSE
4103: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
4104: END IF;
4105: 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));
4106: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4107:
4108: -- Setup Search Context ID
4109: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4111:
4112: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4113: 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);
4114: END IF;
4115: IF l_party_contains_str IS NULL THEN
4116: defpt := 1;
4117: END IF;
4118: IF l_party_site_contains_str IS NULL THEN
4119: defps := 1;
4114: END IF;
4115: IF l_party_contains_str IS NULL THEN
4116: defpt := 1;
4117: END IF;
4118: IF l_party_site_contains_str IS NULL THEN
4119: defps := 1;
4120: END IF;
4121: IF l_contact_contains_str IS NULL THEN
4122: defct := 1;
4117: END IF;
4118: IF l_party_site_contains_str IS NULL THEN
4119: defps := 1;
4120: END IF;
4121: IF l_contact_contains_str IS NULL THEN
4122: defct := 1;
4123: END IF;
4124: IF l_contact_pt_contains_str IS NULL THEN
4125: defcpt := 1;
4120: END IF;
4121: IF l_contact_contains_str IS NULL THEN
4122: defct := 1;
4123: END IF;
4124: IF l_contact_pt_contains_str IS NULL THEN
4125: defcpt := 1;
4126: END IF;
4127:
4128: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4129: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4130: 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);
4131: END IF;
4132: 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);
4133: 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);
4134: 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);
4135: 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);
4136: 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);
4137: 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);
4130: 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);
4131: END IF;
4132: 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);
4133: 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);
4134: 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);
4135: 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);
4136: 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);
4137: 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);
4138: END IF;
4131: END IF;
4132: 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);
4133: 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);
4134: 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);
4135: 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);
4136: 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);
4137: 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);
4138: END IF;
4139: FOR I in 1..5 LOOP
4132: 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);
4133: 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);
4134: 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);
4135: 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);
4136: 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);
4137: 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);
4138: END IF;
4139: FOR I in 1..5 LOOP
4140: do_eval(I);
4214: x_num_matches OUT NUMBER
4215: ) IS
4216:
4217: -- Strings to hold the generated Intermedia query strings
4218: l_party_contains_str VARCHAR2(32000);
4219: l_party_site_contains_str VARCHAR2(32000);
4220: l_contact_contains_str VARCHAR2(32000);
4221: l_contact_pt_contains_str VARCHAR2(32000);
4222: l_denorm_str VARCHAR2(32000);
4215: ) IS
4216:
4217: -- Strings to hold the generated Intermedia query strings
4218: l_party_contains_str VARCHAR2(32000);
4219: l_party_site_contains_str VARCHAR2(32000);
4220: l_contact_contains_str VARCHAR2(32000);
4221: l_contact_pt_contains_str VARCHAR2(32000);
4222: l_denorm_str VARCHAR2(32000);
4223: l_ps_denorm_str VARCHAR2(32000);
4216:
4217: -- Strings to hold the generated Intermedia query strings
4218: l_party_contains_str VARCHAR2(32000);
4219: l_party_site_contains_str VARCHAR2(32000);
4220: l_contact_contains_str VARCHAR2(32000);
4221: l_contact_pt_contains_str VARCHAR2(32000);
4222: l_denorm_str VARCHAR2(32000);
4223: l_ps_denorm_str VARCHAR2(32000);
4224: l_ct_denorm_str VARCHAR2(32000);
4217: -- Strings to hold the generated Intermedia query strings
4218: l_party_contains_str VARCHAR2(32000);
4219: l_party_site_contains_str VARCHAR2(32000);
4220: l_contact_contains_str VARCHAR2(32000);
4221: l_contact_pt_contains_str VARCHAR2(32000);
4222: l_denorm_str VARCHAR2(32000);
4223: l_ps_denorm_str VARCHAR2(32000);
4224: l_ct_denorm_str VARCHAR2(32000);
4225: l_cpt_denorm_str VARCHAR2(32000);
4358: call_type(2) := 'OR';
4359: END;
4360:
4361: /** Private procedure to acquire and score at party level ***/
4362: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
4363: l_party_id_idx NUMBER:=1;
4364: l_ctx_id NUMBER;
4365: BEGIN
4366: SAVEPOINT eval_start;
4379: l_search_ctx_id,H_PARTY_ID(I));
4380: l_party_id_idx := H_PARTY_ID.COUNT+1;
4381: ELSE
4382: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
4383: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
4384: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4385: 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);
4386: END IF;
4387: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
4387: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
4388: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4389: 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);
4390: END IF;
4391: IF (l_party_site_contains_str IS NULL AND
4392: l_contact_contains_str IS NULL AND
4393: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4394: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4395: RAISE L_RETURN_IMM_EXC;
4388: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4389: 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);
4390: END IF;
4391: IF (l_party_site_contains_str IS NULL AND
4392: l_contact_contains_str IS NULL AND
4393: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4394: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4395: RAISE L_RETURN_IMM_EXC;
4396: ELSE
4389: 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);
4390: END IF;
4391: IF (l_party_site_contains_str IS NULL AND
4392: l_contact_contains_str IS NULL AND
4393: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4394: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4395: RAISE L_RETURN_IMM_EXC;
4396: ELSE
4397: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
4393: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4394: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4395: RAISE L_RETURN_IMM_EXC;
4396: ELSE
4397: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
4398: END IF;
4399: ELSE
4400: IF p_index>1 THEN
4401: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4413: l_ctx_id := NULL;
4414: l_party_id_idx := H_PARTY_ID.COUNT+1;
4415: END IF;
4416: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
4417: 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);
4418: END IF;
4419: LOOP
4420: FETCH l_party_cur INTO
4421: 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;
4435: l_party_id_idx:= l_party_id_idx+1;
4436: END IF;
4437: IF (l_party_id_idx-1)>l_max_thresh THEN
4438: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
4439: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
4440: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
4441: AND l_contact_pt_contains_str IS NULL THEN
4442: H_PARTY_ID.DELETE(l_party_id_idx-1);
4443: H_PARTY_ID_LIST.DELETE(l_index);
4437: IF (l_party_id_idx-1)>l_max_thresh THEN
4438: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
4439: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
4440: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
4441: AND l_contact_pt_contains_str IS NULL THEN
4442: H_PARTY_ID.DELETE(l_party_id_idx-1);
4443: H_PARTY_ID_LIST.DELETE(l_index);
4444: H_SCORES.DELETE(l_index);
4445: EXIT;
4466: CLOSE l_party_cur;
4467: ROLLBACK to eval_start;
4468: END;
4469: PROCEDURE open_person_contact_cursor(
4470: p_contains_str VARCHAR2,
4471: p_search_ctx_id NUMBER,
4472: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
4473: BEGIN
4474: OPEN x_cursor FOR
4473: BEGIN
4474: OPEN x_cursor FOR
4475: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
4476: FROM HZ_STAGED_CONTACTS stage
4477: WHERE contains( concat_col, p_contains_str)>0
4478: AND ORG_CONTACT_ID in (
4479: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
4480: org_contact_id
4481: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc
4497: end if;
4498: END;
4499:
4500: /** Private procedure to acquire and score at party site level ***/
4501: 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
4502: l_party_id_idx NUMBER:=1;
4503: l_ctx_id NUMBER;
4504: h_ps_id HZ_PARTY_SEARCH.IDList;
4505: h_ps_party_id HZ_PARTY_SEARCH.IDList;
4525: ELSE
4526: l_party_id_idx := H_PARTY_ID.COUNT+1;
4527: l_ctx_id := NULL;
4528: END IF;
4529: 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);
4530: LOOP
4531: FETCH l_party_site_cur INTO
4532: 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;
4533: EXIT WHEN l_party_site_cur%NOTFOUND;
4596: END IF;
4597: END;
4598:
4599: /** Private procedure to acquire and score at party site level ***/
4600: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
4601: l_party_id_idx NUMBER:=1;
4602: l_ctx_id NUMBER;
4603: h_ct_id HZ_PARTY_SEARCH.IDList;
4604: h_ct_party_id HZ_PARTY_SEARCH.IDList;
4612: FORALL I in 1..H_PARTY_ID.COUNT
4613: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
4614: l_search_ctx_id,H_PARTY_ID(I));
4615: l_party_id_idx := H_PARTY_ID.COUNT+1;
4616: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
4617: LOOP
4618: FETCH l_contact_cur INTO
4619: l_org_contact_id, l_ct_party_id ;
4620: EXIT WHEN l_contact_cur%NOTFOUND;
4654: END IF;
4655: END;
4656:
4657: /** Private procedure to acquire and score at contact point level ***/
4658: 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
4659: l_party_id_idx NUMBER:=1;
4660: l_ctx_id NUMBER;
4661: h_cpt_id HZ_PARTY_SEARCH.IDList;
4662: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
4699: END IF;
4700: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4701: 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);
4702: END IF;
4703: 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);
4704: LOOP
4705: FETCH l_contact_pt_cur INTO
4706: 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;
4707: EXIT WHEN l_contact_pt_cur%NOTFOUND;
4902: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
4903: l_threshold := round(( l_entered_max_score / 805) * 72);
4904: other_acq_criteria_exists := TRUE ;
4905: --check if acquisition criteria exists for any other entity
4906: IF l_party_contains_str IS NOT NULL THEN
4907: acq_cnt := 1;
4908: END IF;
4909: IF l_party_site_contains_str IS NOT NULL THEN
4910: acq_cnt := acq_cnt+1;
4905: --check if acquisition criteria exists for any other entity
4906: IF l_party_contains_str IS NOT NULL THEN
4907: acq_cnt := 1;
4908: END IF;
4909: IF l_party_site_contains_str IS NOT NULL THEN
4910: acq_cnt := acq_cnt+1;
4911: END IF;
4912: IF l_contact_contains_str IS NOT NULL THEN
4913: acq_cnt := acq_cnt+1;
4908: END IF;
4909: IF l_party_site_contains_str IS NOT NULL THEN
4910: acq_cnt := acq_cnt+1;
4911: END IF;
4912: IF l_contact_contains_str IS NOT NULL THEN
4913: acq_cnt := acq_cnt+1;
4914: END IF;
4915: IF l_contact_pt_contains_str IS NOT NULL THEN
4916: acq_cnt := acq_cnt+1;
4911: END IF;
4912: IF l_contact_contains_str IS NOT NULL THEN
4913: acq_cnt := acq_cnt+1;
4914: END IF;
4915: IF l_contact_pt_contains_str IS NOT NULL THEN
4916: acq_cnt := acq_cnt+1;
4917: END IF;
4918: IF acq_cnt>1 THEN
4919: other_acq_criteria_exists := TRUE;
4945: FND_MSG_PUB.ADD;
4946: RAISE FND_API.G_EXC_ERROR;
4947: END IF;
4948: END IF;
4949: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4950: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4951: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4952: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4953: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4946: RAISE FND_API.G_EXC_ERROR;
4947: END IF;
4948: END IF;
4949: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4950: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4951: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4952: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4953: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4954: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4947: END IF;
4948: END IF;
4949: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4950: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4951: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4952: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4953: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4954: 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;
4948: END IF;
4949: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4950: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4951: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4952: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4953: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4954: 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;
4949: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4950: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4951: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4952: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4953: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4954: 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;
4957: /************ End of find_persons private procedures **********/
4950: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4951: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4952: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4953: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4954: 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;
4957: /************ End of find_persons private procedures **********/
4958:
4985: g_score_until_thresh := false;
4986: END IF;
4987: -- ************************************************************
4988: -- STEP 2. Setup of intermedia query strings for Acquisition query
4989: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4990: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4991: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4992: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4993: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4986: END IF;
4987: -- ************************************************************
4988: -- STEP 2. Setup of intermedia query strings for Acquisition query
4989: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4990: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4991: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4992: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4993: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4994:
4987: -- ************************************************************
4988: -- STEP 2. Setup of intermedia query strings for Acquisition query
4989: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4990: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4991: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4992: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4993: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4994:
4995: -- Setup Search Context ID
4988: -- STEP 2. Setup of intermedia query strings for Acquisition query
4989: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4990: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4991: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4992: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4993: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4994:
4995: -- Setup Search Context ID
4996: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4995: -- Setup Search Context ID
4996: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4997: x_search_ctx_id := l_search_ctx_id;
4998:
4999: IF l_party_contains_str IS NULL THEN
5000: defpt := 1;
5001: END IF;
5002: IF l_party_site_contains_str IS NULL THEN
5003: defps := 1;
4998:
4999: IF l_party_contains_str IS NULL THEN
5000: defpt := 1;
5001: END IF;
5002: IF l_party_site_contains_str IS NULL THEN
5003: defps := 1;
5004: END IF;
5005: IF l_contact_contains_str IS NULL THEN
5006: defct := 1;
5001: END IF;
5002: IF l_party_site_contains_str IS NULL THEN
5003: defps := 1;
5004: END IF;
5005: IF l_contact_contains_str IS NULL THEN
5006: defct := 1;
5007: END IF;
5008: IF l_contact_pt_contains_str IS NULL THEN
5009: defcpt := 1;
5004: END IF;
5005: IF l_contact_contains_str IS NULL THEN
5006: defct := 1;
5007: END IF;
5008: IF l_contact_pt_contains_str IS NULL THEN
5009: defcpt := 1;
5010: END IF;
5011:
5012: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5010: END IF;
5011:
5012: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5013: 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);
5014: 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);
5015: 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);
5016: 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);
5017: 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);
5018: 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);
5011:
5012: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5013: 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);
5014: 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);
5015: 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);
5016: 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);
5017: 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);
5018: 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: END IF;
5012: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5013: 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);
5014: 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);
5015: 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);
5016: 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);
5017: 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);
5018: 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: END IF;
5020: FOR I in 1..5 LOOP
5013: 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);
5014: 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);
5015: 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);
5016: 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);
5017: 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);
5018: 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: END IF;
5020: FOR I in 1..5 LOOP
5021: do_eval(I);
5019: END IF;
5020: FOR I in 1..5 LOOP
5021: do_eval(I);
5022: END LOOP;
5023: IF l_contact_contains_str IS NOT NULL THEN
5024: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
5025: END IF;
5026: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5027: 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);
5020: FOR I in 1..5 LOOP
5021: do_eval(I);
5022: END LOOP;
5023: IF l_contact_contains_str IS NOT NULL THEN
5024: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
5025: END IF;
5026: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5027: 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: END IF;
5944: x_num_matches OUT NUMBER
5945: ) IS
5946:
5947: -- Strings to hold the generated Intermedia query strings
5948: l_party_contains_str VARCHAR2(32000);
5949: l_match_idx NUMBER;
5950: l_party_site_contains_str VARCHAR2(32000);
5951: l_contact_contains_str VARCHAR2(32000);
5952: l_contact_pt_contains_str VARCHAR2(32000);
5946:
5947: -- Strings to hold the generated Intermedia query strings
5948: l_party_contains_str VARCHAR2(32000);
5949: l_match_idx NUMBER;
5950: l_party_site_contains_str VARCHAR2(32000);
5951: l_contact_contains_str VARCHAR2(32000);
5952: l_contact_pt_contains_str VARCHAR2(32000);
5953: l_tmp VARCHAR2(32000);
5954:
5947: -- Strings to hold the generated Intermedia query strings
5948: l_party_contains_str VARCHAR2(32000);
5949: l_match_idx NUMBER;
5950: l_party_site_contains_str VARCHAR2(32000);
5951: l_contact_contains_str VARCHAR2(32000);
5952: l_contact_pt_contains_str VARCHAR2(32000);
5953: l_tmp VARCHAR2(32000);
5954:
5955: -- Other local variables
5948: l_party_contains_str VARCHAR2(32000);
5949: l_match_idx NUMBER;
5950: l_party_site_contains_str VARCHAR2(32000);
5951: l_contact_contains_str VARCHAR2(32000);
5952: l_contact_pt_contains_str VARCHAR2(32000);
5953: l_tmp VARCHAR2(32000);
5954:
5955: -- Other local variables
5956: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6033: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6034:
6035: -- ************************************************************
6036: -- STEP 2. Setup of intermedia query strings for Acquisition query
6037: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6038: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6039: 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);
6040:
6041: -- Setup Search Context ID
6034:
6035: -- ************************************************************
6036: -- STEP 2. Setup of intermedia query strings for Acquisition query
6037: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6038: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6039: 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);
6040:
6041: -- Setup Search Context ID
6042: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
6043: x_search_ctx_id := l_search_ctx_id;
6044:
6045: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6046: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
6047: hz_utility_v2pub.debug(p_message=>'l_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);
6048: 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);
6049: 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);
6050: END IF;
6051: IF l_party_site_contains_str IS NULL THEN
6044:
6045: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6046: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
6047: hz_utility_v2pub.debug(p_message=>'l_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);
6048: 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);
6049: 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);
6050: END IF;
6051: IF l_party_site_contains_str IS NULL THEN
6052: defps := 1;
6047: 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);
6048: 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);
6049: 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);
6050: END IF;
6051: IF l_party_site_contains_str IS NULL THEN
6052: defps := 1;
6053: END IF;
6054: IF l_contact_pt_contains_str IS NULL THEN
6055: defcpt := 1;
6050: END IF;
6051: IF l_party_site_contains_str IS NULL THEN
6052: defps := 1;
6053: END IF;
6054: IF l_contact_pt_contains_str IS NULL THEN
6055: defcpt := 1;
6056: END IF;
6057:
6058: IF l_party_site_contains_str IS NOT NULL THEN
6054: IF l_contact_pt_contains_str IS NULL THEN
6055: defcpt := 1;
6056: END IF;
6057:
6058: IF l_party_site_contains_str IS NOT NULL THEN
6059: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
6060: LOOP
6061: FETCH l_party_site_cur INTO
6062: 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;
6055: defcpt := 1;
6056: END IF;
6057:
6058: IF l_party_site_contains_str IS NOT NULL THEN
6059: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
6060: LOOP
6061: FETCH l_party_site_cur INTO
6062: 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: EXIT WHEN l_party_site_cur%NOTFOUND;
6078: END LOOP;
6079: CLOSE l_party_site_cur;
6080: END IF;
6081:
6082: IF l_contact_pt_contains_str IS NOT NULL THEN
6083: SAVEPOINT eval_start;
6084: IF l_match_str = ' AND ' OR (
6085: ((l_cpt_max_score/l_entered_max_score)<(72/805))
6086: ) THEN
6086: ) THEN
6087: FORALL I in 1..l_match_ps_list.COUNT
6088: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
6089: values (l_search_ctx_id,l_match_ps_list(I));
6090: 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');
6091: ELSE
6092: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6093: END IF;
6094: LOOP
6088: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
6089: values (l_search_ctx_id,l_match_ps_list(I));
6090: 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');
6091: ELSE
6092: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6093: END IF;
6094: LOOP
6095: FETCH l_contact_pt_cur INTO
6096: 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;
6188: x_num_matches OUT NUMBER
6189: ) IS
6190:
6191: -- Strings to hold the generated Intermedia query strings
6192: l_party_contains_str VARCHAR2(32000);
6193: l_party_site_contains_str VARCHAR2(32000);
6194: l_contact_contains_str VARCHAR2(32000);
6195: l_contact_pt_contains_str VARCHAR2(32000);
6196: l_tmp VARCHAR2(32000);
6189: ) IS
6190:
6191: -- Strings to hold the generated Intermedia query strings
6192: l_party_contains_str VARCHAR2(32000);
6193: l_party_site_contains_str VARCHAR2(32000);
6194: l_contact_contains_str VARCHAR2(32000);
6195: l_contact_pt_contains_str VARCHAR2(32000);
6196: l_tmp VARCHAR2(32000);
6197:
6190:
6191: -- Strings to hold the generated Intermedia query strings
6192: l_party_contains_str VARCHAR2(32000);
6193: l_party_site_contains_str VARCHAR2(32000);
6194: l_contact_contains_str VARCHAR2(32000);
6195: l_contact_pt_contains_str VARCHAR2(32000);
6196: l_tmp VARCHAR2(32000);
6197:
6198: -- Other local variables
6191: -- Strings to hold the generated Intermedia query strings
6192: l_party_contains_str VARCHAR2(32000);
6193: l_party_site_contains_str VARCHAR2(32000);
6194: l_contact_contains_str VARCHAR2(32000);
6195: l_contact_pt_contains_str VARCHAR2(32000);
6196: l_tmp VARCHAR2(32000);
6197:
6198: -- Other local variables
6199: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6265: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6266:
6267: -- ************************************************************
6268: -- STEP 2. Setup of intermedia query strings for Acquisition query
6269: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6270: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6271: 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);
6272:
6273: -- Setup Search Context ID
6266:
6267: -- ************************************************************
6268: -- STEP 2. Setup of intermedia query strings for Acquisition query
6269: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6270: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6271: 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);
6272:
6273: -- Setup Search Context ID
6274: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
6275: x_search_ctx_id := l_search_ctx_id;
6276:
6277: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6278: 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);
6279: 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);
6280: 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);
6281: 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);
6282: END IF;
6283: IF l_contact_contains_str IS NULL THEN
6276:
6277: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6278: 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);
6279: 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);
6280: 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);
6281: 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);
6282: END IF;
6283: IF l_contact_contains_str IS NULL THEN
6284: defct := 1;
6279: 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);
6280: 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);
6281: 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);
6282: END IF;
6283: IF l_contact_contains_str IS NULL THEN
6284: defct := 1;
6285: END IF;
6286: IF l_contact_pt_contains_str IS NULL THEN
6287: defcpt := 1;
6282: END IF;
6283: IF l_contact_contains_str IS NULL THEN
6284: defct := 1;
6285: END IF;
6286: IF l_contact_pt_contains_str IS NULL THEN
6287: defcpt := 1;
6288: END IF;
6289:
6290: IF l_contact_contains_str IS NOT NULL THEN
6286: IF l_contact_pt_contains_str IS NULL THEN
6287: defcpt := 1;
6288: END IF;
6289:
6290: IF l_contact_contains_str IS NOT NULL THEN
6291: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
6292: LOOP
6293: FETCH l_contact_cur INTO
6294: l_org_contact_id, l_ct_party_id ;
6287: defcpt := 1;
6288: END IF;
6289:
6290: IF l_contact_contains_str IS NOT NULL THEN
6291: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
6292: LOOP
6293: FETCH l_contact_cur INTO
6294: l_org_contact_id, l_ct_party_id ;
6295: EXIT WHEN l_contact_cur%NOTFOUND;
6308: END LOOP;
6309: CLOSE l_contact_cur;
6310: END IF;
6311:
6312: IF l_contact_pt_contains_str IS NOT NULL THEN
6313: SAVEPOINT eval_start;
6314: IF l_match_str = ' AND ' OR (
6315: ((l_cpt_max_score/l_entered_max_score)<(72/805))
6316: ) THEN
6316: ) THEN
6317: FORALL I in 1..l_match_contact_list.COUNT
6318: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
6319: values (l_search_ctx_id,l_match_contact_list(I));
6320: 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');
6321: ELSE
6322: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6323: END IF;
6324: LOOP
6318: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
6319: values (l_search_ctx_id,l_match_contact_list(I));
6320: 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');
6321: ELSE
6322: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6323: END IF;
6324: LOOP
6325: FETCH l_contact_pt_cur INTO
6326: 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;
6416: ) IS
6417:
6418:
6419: -- Strings to hold the generated Intermedia query strings
6420: l_contact_pt_contains_str VARCHAR2(32000);
6421: l_tmp VARCHAR2(32000);
6422:
6423: -- Other local variables
6424: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6477: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6478:
6479: -- ************************************************************
6480: -- STEP 2. Setup of intermedia query strings for Acquisition query
6481: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6482: 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);
6483:
6484: -- Setup Search Context ID
6485: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
6485: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
6486: x_search_ctx_id := l_search_ctx_id;
6487: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6488: 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);
6489: 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);
6490: 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);
6491: END IF;
6492:
6493: IF l_contact_pt_contains_str IS NOT NULL THEN
6489: 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);
6490: 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);
6491: END IF;
6492:
6493: IF l_contact_pt_contains_str IS NOT NULL THEN
6494: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6495: cnt := 1;
6496: LOOP
6497: FETCH l_contact_pt_cur INTO
6490: 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);
6491: END IF;
6492:
6493: IF l_contact_pt_contains_str IS NOT NULL THEN
6494: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6495: cnt := 1;
6496: LOOP
6497: FETCH l_contact_pt_cur INTO
6498: 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;
6561: x_search_ctx_id IN OUT NUMBER
6562: ) IS
6563:
6564: -- Strings to hold the generated Intermedia query strings
6565: l_party_contains_str VARCHAR2(32000);
6566: l_party_site_contains_str VARCHAR2(32000);
6567: l_contact_contains_str VARCHAR2(32000);
6568: l_contact_pt_contains_str VARCHAR2(32000);
6569: l_tmp VARCHAR2(32000);
6562: ) IS
6563:
6564: -- Strings to hold the generated Intermedia query strings
6565: l_party_contains_str VARCHAR2(32000);
6566: l_party_site_contains_str VARCHAR2(32000);
6567: l_contact_contains_str VARCHAR2(32000);
6568: l_contact_pt_contains_str VARCHAR2(32000);
6569: l_tmp VARCHAR2(32000);
6570:
6563:
6564: -- Strings to hold the generated Intermedia query strings
6565: l_party_contains_str VARCHAR2(32000);
6566: l_party_site_contains_str VARCHAR2(32000);
6567: l_contact_contains_str VARCHAR2(32000);
6568: l_contact_pt_contains_str VARCHAR2(32000);
6569: l_tmp VARCHAR2(32000);
6570:
6571: -- Other local variables
6564: -- Strings to hold the generated Intermedia query strings
6565: l_party_contains_str VARCHAR2(32000);
6566: l_party_site_contains_str VARCHAR2(32000);
6567: l_contact_contains_str VARCHAR2(32000);
6568: l_contact_pt_contains_str VARCHAR2(32000);
6569: l_tmp VARCHAR2(32000);
6570:
6571: -- Other local variables
6572: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6673: g_score_until_thresh := false;
6674: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6675: -- ************************************************************
6676: -- STEP 2. Setup of intermedia query strings for Acquisition query
6677: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6678: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6679: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6680: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6681:
6674: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6675: -- ************************************************************
6676: -- STEP 2. Setup of intermedia query strings for Acquisition query
6677: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6678: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6679: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6680: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6681:
6682: -- Setup Search Context ID
6675: -- ************************************************************
6676: -- STEP 2. Setup of intermedia query strings for Acquisition query
6677: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6678: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6679: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6680: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6681:
6682: -- Setup Search Context ID
6683: IF x_search_ctx_id IS NULL THEN
6695: 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,l_TX39,l_TX158,l_TX40,l_TX43,l_TX42,l_TX44,l_TX46,l_TX47,l_TX48,l_TX156,l_TX157,1);
6696: END LOOP;
6697: CLOSE l_party_cur;
6698:
6699: IF l_party_site_contains_str IS NOT NULL THEN
6700: l_max_score := 0;
6701: l_max_id := 0;
6702: l_max_idx := 0;
6703: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6700: l_max_score := 0;
6701: l_max_id := 0;
6702: l_max_idx := 0;
6703: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6704: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
6705: ELSE
6706: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
6707: END IF;
6708: LOOP
6702: l_max_idx := 0;
6703: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6704: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
6705: ELSE
6706: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
6707: END IF;
6708: LOOP
6709: FETCH l_party_site_cur INTO
6710: 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;
6730: 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);
6731: END IF;
6732: END IF;
6733:
6734: IF l_contact_contains_str IS NOT NULL THEN
6735: l_max_score := 0;
6736: l_max_id := 0;
6737: l_max_idx := 0;
6738: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
6734: IF l_contact_contains_str IS NOT NULL THEN
6735: l_max_score := 0;
6736: l_max_id := 0;
6737: l_max_idx := 0;
6738: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
6739: LOOP
6740: FETCH l_contact_cur INTO
6741: l_org_contact_id, l_ct_party_id ;
6742: EXIT WHEN l_contact_cur%NOTFOUND;
6752: 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);
6753: END IF;
6754: END IF;
6755:
6756: IF l_contact_pt_contains_str IS NOT NULL THEN
6757: l_max_score := 0;
6758: l_max_id := 0;
6759: l_max_idx := 0;
6760: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6757: l_max_score := 0;
6758: l_max_id := 0;
6759: l_max_idx := 0;
6760: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6761: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
6762: ELSE
6763: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
6764: END IF;
6765: LOOP
6759: l_max_idx := 0;
6760: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6761: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
6762: ELSE
6763: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
6764: END IF;
6765: LOOP
6766: FETCH l_contact_pt_cur INTO
6767: 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;