[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:
1681: p_party_max_score NUMBER,
1682: p_denorm_max_score NUMBER,
1683: p_non_denorm_max_score NUMBER,
1684: p_threshold NUMBER) RETURN VARCHAR2 IS
1685: l_party_contains_str VARCHAR2(32000);
1686: l_party_filter VARCHAR2(1) := null;
1687: l_prim_temp VARCHAR2(4000);
1688: l_denorm_str VARCHAR2(4000);
1689: BEGIN
1725:
1726: IF l_denorm_str IS NOT NULL THEN
1727: IF (p_non_denorm_max_score+10)>=p_threshold THEN
1728: l_denorm_str := NULL;
1729: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1730: ELSIF (p_non_denorm_max_score+p_denorm_max_score+10)>=p_threshold THEN
1731: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1732: l_denorm_str := NULL;
1733: END IF;
1727: IF (p_non_denorm_max_score+10)>=p_threshold THEN
1728: l_denorm_str := NULL;
1729: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1730: ELSIF (p_non_denorm_max_score+p_denorm_max_score+10)>=p_threshold THEN
1731: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1732: l_denorm_str := NULL;
1733: END IF;
1734: ELSE
1735: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1731: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1732: l_denorm_str := NULL;
1733: END IF;
1734: ELSE
1735: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1736: END IF;
1737:
1738: -- Setup query string for CATEGORY_CODE
1739: l_prim_temp := null;
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 REFERENCE_USE_FLAG
1756: l_prim_temp := null;
1759:
1760: IF l_denorm_str IS NOT NULL THEN
1761: IF (p_non_denorm_max_score+20)>=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+20)>=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+20)>=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+20)>=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 TAX_NAME
1773: l_prim_temp := null;
1776:
1777: IF l_denorm_str IS NOT NULL THEN
1778: IF (p_non_denorm_max_score+30)>=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+30)>=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+30)>=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+30)>=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 CORPORATION_CLASS
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 ALL_ACCOUNT_NAMES
1824: l_prim_temp := null;
1827:
1828: IF l_denorm_str IS NOT NULL THEN
1829: IF (p_non_denorm_max_score+50)>=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+50)>=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+50)>=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+50)>=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_NAME
1841: l_prim_temp := null;
1844:
1845: IF l_denorm_str IS NOT NULL THEN
1846: IF (p_non_denorm_max_score+50)>=p_threshold THEN
1847: l_denorm_str := NULL;
1848: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1849: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
1850: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1851: l_denorm_str := NULL;
1852: END IF;
1846: IF (p_non_denorm_max_score+50)>=p_threshold THEN
1847: l_denorm_str := NULL;
1848: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1849: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
1850: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1851: l_denorm_str := NULL;
1852: END IF;
1853: ELSE
1854: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1850: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1851: l_denorm_str := NULL;
1852: END IF;
1853: ELSE
1854: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1855: END IF;
1856:
1857: -- Setup query string for TAX_REFERENCE
1858: l_prim_temp := null;
1861:
1862: IF l_denorm_str IS NOT NULL THEN
1863: IF (p_non_denorm_max_score+80)>=p_threshold THEN
1864: l_denorm_str := NULL;
1865: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1866: ELSIF (p_non_denorm_max_score+p_denorm_max_score+80)>=p_threshold THEN
1867: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1868: l_denorm_str := NULL;
1869: END IF;
1863: IF (p_non_denorm_max_score+80)>=p_threshold THEN
1864: l_denorm_str := NULL;
1865: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1866: ELSIF (p_non_denorm_max_score+p_denorm_max_score+80)>=p_threshold THEN
1867: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1868: l_denorm_str := NULL;
1869: END IF;
1870: ELSE
1871: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1867: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1868: l_denorm_str := NULL;
1869: END IF;
1870: ELSE
1871: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1872: END IF;
1873:
1874: -- Setup query string for ALL_ACCOUNT_NUMBERS
1875: l_prim_temp := null;
1878:
1879: IF l_denorm_str IS NOT NULL THEN
1880: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1881: l_denorm_str := NULL;
1882: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1883: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1884: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1885: l_denorm_str := NULL;
1886: END IF;
1880: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1881: l_denorm_str := NULL;
1882: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1883: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1884: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1885: l_denorm_str := NULL;
1886: END IF;
1887: ELSE
1888: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1884: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1885: l_denorm_str := NULL;
1886: END IF;
1887: ELSE
1888: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1889: END IF;
1890:
1891: -- Setup query string for JGZZ_FISCAL_CODE
1892: l_prim_temp := null;
1895:
1896: IF l_denorm_str IS NOT NULL THEN
1897: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1898: l_denorm_str := NULL;
1899: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1900: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1901: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1902: l_denorm_str := NULL;
1903: END IF;
1897: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1898: l_denorm_str := NULL;
1899: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1900: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1901: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1902: l_denorm_str := NULL;
1903: END IF;
1904: ELSE
1905: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1901: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1902: l_denorm_str := NULL;
1903: END IF;
1904: ELSE
1905: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1906: END IF;
1907: IF lengthb(l_party_contains_str) > 4000 THEN
1908: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1909: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1903: END IF;
1904: ELSE
1905: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1906: END IF;
1907: IF lengthb(l_party_contains_str) > 4000 THEN
1908: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1909: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1910: FND_MSG_PUB.ADD;
1911: RAISE FND_API.G_EXC_ERROR;
1909: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1910: FND_MSG_PUB.ADD;
1911: RAISE FND_API.G_EXC_ERROR;
1912: END IF;
1913: IF (p_match_str = ' AND ' OR p_party_max_score
1915: 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);
1916: END IF;
1917: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1911: RAISE FND_API.G_EXC_ERROR;
1912: END IF;
1913: IF (p_match_str = ' AND ' OR p_party_max_score
1915: 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);
1916: END IF;
1917: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1918: 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);
1919: END IF;
1914: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1915: 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);
1916: END IF;
1917: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1918: 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);
1919: END IF;
1920: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1921: 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);
1922: END IF;
1919: END IF;
1920: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1921: 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);
1922: END IF;
1923: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1924: ELSE
1925: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1926: 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);
1927: 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);
1922: END IF;
1923: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1924: ELSE
1925: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1926: 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);
1927: 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);
1928: END IF;
1929: RETURN l_party_contains_str;
1930: END IF;
1923: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1924: ELSE
1925: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1926: 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);
1927: 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);
1928: END IF;
1929: RETURN l_party_contains_str;
1930: END IF;
1931: END;
1925: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1926: 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);
1927: 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);
1928: END IF;
1929: RETURN l_party_contains_str;
1930: END IF;
1931: END;
1932: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1933: l_contains_str VARCHAR2(32000);
1929: RETURN l_party_contains_str;
1930: END IF;
1931: END;
1932: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1933: l_contains_str VARCHAR2(32000);
1934: l_contains_str_temp VARCHAR2(32000);
1935: l_den_contains_str VARCHAR2(32000);
1936: l_den_contains_str_temp VARCHAR2(32000);
1937: l_filter_str VARCHAR2(4000) := null;
1930: END IF;
1931: END;
1932: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1933: l_contains_str VARCHAR2(32000);
1934: l_contains_str_temp VARCHAR2(32000);
1935: l_den_contains_str VARCHAR2(32000);
1936: l_den_contains_str_temp VARCHAR2(32000);
1937: l_filter_str VARCHAR2(4000) := null;
1938: l_prim_temp VARCHAR2(4000) := null;
1931: END;
1932: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1933: l_contains_str VARCHAR2(32000);
1934: l_contains_str_temp VARCHAR2(32000);
1935: l_den_contains_str VARCHAR2(32000);
1936: l_den_contains_str_temp VARCHAR2(32000);
1937: l_filter_str VARCHAR2(4000) := null;
1938: l_prim_temp VARCHAR2(4000) := null;
1939: l_prim_temp_den VARCHAR2(4000) := null;
1932: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1933: l_contains_str VARCHAR2(32000);
1934: l_contains_str_temp VARCHAR2(32000);
1935: l_den_contains_str VARCHAR2(32000);
1936: l_den_contains_str_temp VARCHAR2(32000);
1937: l_filter_str VARCHAR2(4000) := null;
1938: l_prim_temp VARCHAR2(4000) := null;
1939: l_prim_temp_den VARCHAR2(4000) := null;
1940: BEGIN
1942: 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);
1943: END IF;
1944: x_denorm_str := NULL;
1945:
1946: -- Dynamic setup of party site contains str
1947: --
1948: -- For each primary transformation add to intermedia query if it
1949: -- is not null
1950: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1947: --
1948: -- For each primary transformation add to intermedia query if it
1949: -- is not null
1950: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1951: l_contains_str_temp := null;
1952: l_den_contains_str_temp := null;
1953: l_filter_str := null;
1954:
1955: -- Setup query string for ADDRESS
1948: -- For each primary transformation add to intermedia query if it
1949: -- is not null
1950: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1951: l_contains_str_temp := null;
1952: l_den_contains_str_temp := null;
1953: l_filter_str := null;
1954:
1955: -- Setup query string for ADDRESS
1956: l_prim_temp := null;
1956: l_prim_temp := null;
1957: l_prim_temp_den := null;
1958: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL + WRADDRESS
1959: g_party_site_stage_list(I).TX3,'A27',l_prim_temp);
1960: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1961:
1962: -- Setup query string for CITY
1963: l_prim_temp := null;
1964: l_prim_temp_den := null;
1965: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1966: g_party_site_stage_list(I).TX9,'A29',l_prim_temp);
1967: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1968: g_party_site_stage_list(I).TX9,'D_PS',l_prim_temp_den);
1969: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1970: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1971:
1972: -- Setup query string for POSTAL_CODE
1973: l_prim_temp := null;
1966: g_party_site_stage_list(I).TX9,'A29',l_prim_temp);
1967: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1968: g_party_site_stage_list(I).TX9,'D_PS',l_prim_temp_den);
1969: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1970: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1971:
1972: -- Setup query string for POSTAL_CODE
1973: l_prim_temp := null;
1974: l_prim_temp_den := null;
1975: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1976: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1977: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1978: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1979: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1980: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1981:
1982: -- Setup query string for PROVINCE
1983: l_prim_temp := null;
1976: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1977: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1978: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1979: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1980: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1981:
1982: -- Setup query string for PROVINCE
1983: l_prim_temp := null;
1984: l_prim_temp_den := null;
1985: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1986: g_party_site_stage_list(I).TX12,'A31',l_prim_temp);
1987: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1988: g_party_site_stage_list(I).TX12,'D_PS',l_prim_temp_den);
1989: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1990: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1991:
1992: -- Setup query string for STATE
1993: l_prim_temp := null;
1986: g_party_site_stage_list(I).TX12,'A31',l_prim_temp);
1987: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1988: g_party_site_stage_list(I).TX12,'D_PS',l_prim_temp_den);
1989: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1990: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1991:
1992: -- Setup query string for STATE
1993: l_prim_temp := null;
1994: l_prim_temp_den := null;
1995: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT + WRSTATE
1996: g_party_site_stage_list(I).TX14,'A32',l_prim_temp);
1997: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT + WRSTATE
1998: g_party_site_stage_list(I).TX14,'D_PS',l_prim_temp_den);
1999: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
2000: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2001:
2002: -- Setup query string for COUNTY
2003: l_prim_temp := null;
1996: g_party_site_stage_list(I).TX14,'A32',l_prim_temp);
1997: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT + WRSTATE
1998: g_party_site_stage_list(I).TX14,'D_PS',l_prim_temp_den);
1999: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
2000: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2001:
2002: -- Setup query string for COUNTY
2003: l_prim_temp := null;
2004: l_prim_temp_den := null;
2005: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
2006: g_party_site_stage_list(I).TX20,'A35',l_prim_temp);
2007: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
2008: g_party_site_stage_list(I).TX20,'D_PS',l_prim_temp_den);
2009: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
2010: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2011:
2012: -- Setup query string for COUNTRY
2013: l_prim_temp := null;
2006: g_party_site_stage_list(I).TX20,'A35',l_prim_temp);
2007: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
2008: g_party_site_stage_list(I).TX20,'D_PS',l_prim_temp_den);
2009: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
2010: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2011:
2012: -- Setup query string for COUNTRY
2013: l_prim_temp := null;
2014: l_prim_temp_den := null;
2015: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
2016: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
2017: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
2018: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
2019: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
2020: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2021:
2022: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2023: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2016: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
2017: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
2018: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
2019: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
2020: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2021:
2022: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2023: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2024: END LOOP;
2018: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
2019: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
2020: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2021:
2022: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2023: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2024: END LOOP;
2025: -- Add the search criteria to query string
2026: IF lengthb(l_contains_str) > 4000 THEN
2019: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
2020: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2021:
2022: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2023: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2024: END LOOP;
2025: -- Add the search criteria to query string
2026: IF lengthb(l_contains_str) > 4000 THEN
2027: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
2022: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2023: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2024: END LOOP;
2025: -- Add the search criteria to query string
2026: IF lengthb(l_contains_str) > 4000 THEN
2027: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
2028: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
2029: FND_MSG_PUB.ADD;
2030: RAISE FND_API.G_EXC_ERROR;
2028: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
2029: FND_MSG_PUB.ADD;
2030: RAISE FND_API.G_EXC_ERROR;
2031: END IF;
2032: x_denorm_str := l_den_contains_str;
2033: RETURN l_contains_str;
2034: END;
2035:
2036: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2029: FND_MSG_PUB.ADD;
2030: RAISE FND_API.G_EXC_ERROR;
2031: END IF;
2032: x_denorm_str := l_den_contains_str;
2033: RETURN l_contains_str;
2034: END;
2035:
2036: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2037: l_contains_str VARCHAR2(32000);
2033: RETURN l_contains_str;
2034: END;
2035:
2036: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2037: l_contains_str VARCHAR2(32000);
2038: l_contains_str_temp VARCHAR2(32000);
2039: l_den_contains_str VARCHAR2(32000);
2040: l_den_contains_str_temp VARCHAR2(32000);
2041: l_filter_str VARCHAR2(4000) := null;
2034: END;
2035:
2036: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2037: l_contains_str VARCHAR2(32000);
2038: l_contains_str_temp VARCHAR2(32000);
2039: l_den_contains_str VARCHAR2(32000);
2040: l_den_contains_str_temp VARCHAR2(32000);
2041: l_filter_str VARCHAR2(4000) := null;
2042: l_prim_temp VARCHAR2(4000) := null;
2035:
2036: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2037: l_contains_str VARCHAR2(32000);
2038: l_contains_str_temp VARCHAR2(32000);
2039: l_den_contains_str VARCHAR2(32000);
2040: l_den_contains_str_temp VARCHAR2(32000);
2041: l_filter_str VARCHAR2(4000) := null;
2042: l_prim_temp VARCHAR2(4000) := null;
2043: l_prim_temp_den VARCHAR2(4000) := null;
2036: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2037: l_contains_str VARCHAR2(32000);
2038: l_contains_str_temp VARCHAR2(32000);
2039: l_den_contains_str VARCHAR2(32000);
2040: l_den_contains_str_temp VARCHAR2(32000);
2041: l_filter_str VARCHAR2(4000) := null;
2042: l_prim_temp VARCHAR2(4000) := null;
2043: l_prim_temp_den VARCHAR2(4000) := null;
2044: BEGIN
2046: hz_utility_v2pub.debug(p_message=>'INIT_CONTACTS_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
2047: END IF;
2048: x_denorm_str := NULL;
2049:
2050: -- Dynamic setup of party site contains str
2051: --
2052: -- For each primary transformation add to intermedia query if it
2053: -- is not null
2054: FOR I IN 1..g_contact_stage_list.COUNT LOOP
2051: --
2052: -- For each primary transformation add to intermedia query if it
2053: -- is not null
2054: FOR I IN 1..g_contact_stage_list.COUNT LOOP
2055: l_contains_str_temp := null;
2056: l_den_contains_str_temp := null;
2057: l_filter_str := null;
2058:
2059: -- Setup query string for CONTACT_NAME
2052: -- For each primary transformation add to intermedia query if it
2053: -- is not null
2054: FOR I IN 1..g_contact_stage_list.COUNT LOOP
2055: l_contains_str_temp := null;
2056: l_den_contains_str_temp := null;
2057: l_filter_str := null;
2058:
2059: -- Setup query string for CONTACT_NAME
2060: l_prim_temp := null;
2060: l_prim_temp := null;
2061: l_prim_temp_den := null;
2062: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL + WRPERSON
2063: g_contact_stage_list(I).TX5,'A39',l_prim_temp);
2064: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2065:
2066: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2067: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2068: END LOOP;
2062: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL + WRPERSON
2063: g_contact_stage_list(I).TX5,'A39',l_prim_temp);
2064: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2065:
2066: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2067: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2068: END LOOP;
2069: -- Add the search criteria to query string
2070: IF lengthb(l_contains_str) > 4000 THEN
2063: g_contact_stage_list(I).TX5,'A39',l_prim_temp);
2064: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2065:
2066: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2067: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2068: END LOOP;
2069: -- Add the search criteria to query string
2070: IF lengthb(l_contains_str) > 4000 THEN
2071: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
2066: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2067: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2068: END LOOP;
2069: -- Add the search criteria to query string
2070: IF lengthb(l_contains_str) > 4000 THEN
2071: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
2072: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
2073: FND_MSG_PUB.ADD;
2074: RAISE FND_API.G_EXC_ERROR;
2072: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
2073: FND_MSG_PUB.ADD;
2074: RAISE FND_API.G_EXC_ERROR;
2075: END IF;
2076: x_denorm_str := l_den_contains_str;
2077: RETURN l_contains_str;
2078: END;
2079:
2080: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2073: FND_MSG_PUB.ADD;
2074: RAISE FND_API.G_EXC_ERROR;
2075: END IF;
2076: x_denorm_str := l_den_contains_str;
2077: RETURN l_contains_str;
2078: END;
2079:
2080: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2081: l_contains_str VARCHAR2(32000);
2077: RETURN l_contains_str;
2078: END;
2079:
2080: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2081: l_contains_str VARCHAR2(32000);
2082: l_contains_str_temp VARCHAR2(32000);
2083: l_den_contains_str VARCHAR2(32000);
2084: l_den_contains_str_temp VARCHAR2(32000);
2085: l_filter_str VARCHAR2(4000) := null;
2078: END;
2079:
2080: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2081: l_contains_str VARCHAR2(32000);
2082: l_contains_str_temp VARCHAR2(32000);
2083: l_den_contains_str VARCHAR2(32000);
2084: l_den_contains_str_temp VARCHAR2(32000);
2085: l_filter_str VARCHAR2(4000) := null;
2086: l_prim_temp VARCHAR2(4000) := null;
2079:
2080: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2081: l_contains_str VARCHAR2(32000);
2082: l_contains_str_temp VARCHAR2(32000);
2083: l_den_contains_str VARCHAR2(32000);
2084: l_den_contains_str_temp VARCHAR2(32000);
2085: l_filter_str VARCHAR2(4000) := null;
2086: l_prim_temp VARCHAR2(4000) := null;
2087: l_prim_temp_den VARCHAR2(4000) := null;
2080: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
2081: l_contains_str VARCHAR2(32000);
2082: l_contains_str_temp VARCHAR2(32000);
2083: l_den_contains_str VARCHAR2(32000);
2084: l_den_contains_str_temp VARCHAR2(32000);
2085: l_filter_str VARCHAR2(4000) := null;
2086: l_prim_temp VARCHAR2(4000) := null;
2087: l_prim_temp_den VARCHAR2(4000) := null;
2088: TYPE CONTACT_PT_REC_TYPE IS RECORD (
2096: 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);
2097: END IF;
2098: x_denorm_str := NULL;
2099:
2100: -- Dynamic setup of party site contains str
2101: --
2102: -- For each primary transformation add to intermedia query if it
2103: -- is not null
2104: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
2101: --
2102: -- For each primary transformation add to intermedia query if it
2103: -- is not null
2104: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
2105: l_contains_str_temp := null;
2106: l_den_contains_str_temp := null;
2107: l_filter_str := null;
2108: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
2109: if(l_cnt_pt_type.count>0) then
2102: -- For each primary transformation add to intermedia query if it
2103: -- is not null
2104: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
2105: l_contains_str_temp := null;
2106: l_den_contains_str_temp := null;
2107: l_filter_str := null;
2108: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
2109: if(l_cnt_pt_type.count>0) then
2110: x_modify := 'Y';
2126: l_prim_temp := null;
2127: l_prim_temp_den := null;
2128: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL CTX
2129: g_contact_pt_stage_list(I).TX1,'A44',l_prim_temp);
2130: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2131:
2132: -- Setup query string for EMAIL_ADDRESS
2133: l_prim_temp := null;
2134: l_prim_temp_den := null;
2133: l_prim_temp := null;
2134: l_prim_temp_den := null;
2135: HZ_DQM_SEARCH_UTIL.add_transformation( -- FORCE UPPERCASE ONLY
2136: g_contact_pt_stage_list(I).TX5,'A48',l_prim_temp);
2137: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2138:
2139: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2140: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2141: IF N>1 THEN
2135: HZ_DQM_SEARCH_UTIL.add_transformation( -- FORCE UPPERCASE ONLY
2136: g_contact_pt_stage_list(I).TX5,'A48',l_prim_temp);
2137: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2138:
2139: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2140: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2141: IF N>1 THEN
2142: distinct_search_cpt_types := N-1;
2143: ELSE
2136: g_contact_pt_stage_list(I).TX5,'A48',l_prim_temp);
2137: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
2138:
2139: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
2140: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
2141: IF N>1 THEN
2142: distinct_search_cpt_types := N-1;
2143: ELSE
2144: distinct_search_cpt_types := N;
2147: 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);
2148: END IF;
2149: END LOOP;
2150: -- Add the search criteria to query string
2151: IF lengthb(l_contains_str) > 4000 THEN
2152: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
2153: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
2154: FND_MSG_PUB.ADD;
2155: RAISE FND_API.G_EXC_ERROR;
2153: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
2154: FND_MSG_PUB.ADD;
2155: RAISE FND_API.G_EXC_ERROR;
2156: END IF;
2157: x_denorm_str := l_den_contains_str;
2158: RETURN l_contains_str;
2159: END;
2160:
2161: PROCEDURE open_party_cursor_direct (
2154: FND_MSG_PUB.ADD;
2155: RAISE FND_API.G_EXC_ERROR;
2156: END IF;
2157: x_denorm_str := l_den_contains_str;
2158: RETURN l_contains_str;
2159: END;
2160:
2161: PROCEDURE open_party_cursor_direct (
2162: p_dup_party_id NUMBER,
2162: p_dup_party_id NUMBER,
2163: p_restrict_sql VARCHAR2,
2164: p_match_str VARCHAR2,
2165: p_search_merged VARCHAR2,
2166: p_party_contains_str VARCHAR2,
2167: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
2168: l_sqlstr VARCHAR2(4000);
2169: l_search_merged VARCHAR2(1);
2170: BEGIN
2179: END IF;
2180: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2181: 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);
2182: END IF;
2183: 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
2184: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2185: 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);
2186: END IF;
2187: OPEN x_cursor FOR
2241: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
2242: l_sqlstr := l_sqlstr || ' (TX36 like :TX36||'' %'' ';
2243: l_sqlstr := l_sqlstr || ' )) ';
2244: END IF;
2245: 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
2246: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2247: 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);
2248: END IF;
2249: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
2243: l_sqlstr := l_sqlstr || ' )) ';
2244: END IF;
2245: 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
2246: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2247: 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);
2248: END IF;
2249: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
2250: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX9||'' %'' ';
2251: l_sqlstr := l_sqlstr || ' )) ';
2279: END IF;
2280: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2281: 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);
2282: END IF;
2283: 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
2284: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2285: 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);
2286: END IF;
2287: 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
2281: 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);
2282: END IF;
2283: 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
2284: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2285: 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);
2286: END IF;
2287: 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
2288: ,g_party_stage_rec.TX32
2289: ,g_party_stage_rec.TX32
2347: ,g_party_stage_rec.TX157
2348: ,g_party_stage_rec.TX36
2349: ,g_party_stage_rec.TX36
2350: ;
2351: 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
2352: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2353: 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);
2354: END IF;
2355: 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
2349: ,g_party_stage_rec.TX36
2350: ;
2351: 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
2352: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2353: 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);
2354: END IF;
2355: 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
2356: ,g_party_site_stage_list(1).TX9
2357: ,g_party_site_stage_list(1).TX9
2376: END;
2377: PROCEDURE open_party_cursor(
2378: p_dup_party_id NUMBER,
2379: p_restrict_sql VARCHAR2,
2380: p_contains_str VARCHAR2,
2381: p_search_ctx_id NUMBER,
2382: p_match_str VARCHAR2,
2383: p_search_merged VARCHAR2,
2384: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
2395: l_search_merged := 'N';
2396: ELSE
2397: l_search_merged := p_search_merged;
2398: END IF;
2399: IF p_contains_str IS NULL THEN
2400: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2401: 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);
2402: END IF;
2403: OPEN x_cursor FOR
2397: l_search_merged := p_search_merged;
2398: END IF;
2399: IF p_contains_str IS NULL THEN
2400: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2401: 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);
2402: END IF;
2403: OPEN x_cursor FOR
2404: SELECT PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157
2405: FROM HZ_STAGED_PARTIES stage
2414: END IF;
2415: OPEN x_cursor FOR
2416: SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157
2417: FROM HZ_STAGED_PARTIES stage
2418: WHERE contains( concat_col, p_contains_str)>0
2419: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
2420: AND( (l_search_merged ='Y' )
2421: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
2422: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )
2427: END IF;
2428: OPEN x_cursor FOR
2429: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157
2430: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
2431: WHERE contains( concat_col, p_contains_str)>0
2432: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
2433: AND d.party_id = stage.party_id
2434: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
2435: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2452: END IF;
2453: IF p_search_ctx_id IS NULL THEN
2454: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157'||
2455: ' FROM HZ_STAGED_PARTIES stage'||
2456: ' WHERE contains( concat_col, :cont)>0'||
2457: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
2458: ' AND ('||p_restrict_sql||')' ||
2459: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2460: IF l_search_merged = 'Y' THEN
2464: ELSE
2465: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
2466: END IF;
2467: output_long_strings('----------------------------------------------------------');
2468: output_long_strings('Party Contains String = '||p_contains_str);
2469: output_long_strings('Restrict Sql = '||p_restrict_sql);
2470: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2471: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2472: ,p_dup_party_id, p_dup_party_id;
2466: END IF;
2467: output_long_strings('----------------------------------------------------------');
2468: output_long_strings('Party Contains String = '||p_contains_str);
2469: output_long_strings('Restrict Sql = '||p_restrict_sql);
2470: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2471: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2472: ,p_dup_party_id, p_dup_party_id;
2473: END IF;
2474: END IF;
2497: PROCEDURE open_party_site_cursor(
2498: p_dup_party_id NUMBER,
2499: p_party_id NUMBER,
2500: p_restrict_sql VARCHAR2,
2501: p_contains_str VARCHAR2,
2502: p_search_ctx_id NUMBER,
2503: p_search_merged VARCHAR2,
2504: p_search_rel_sites VARCHAR2,
2505: p_person_api VARCHAR2,
2529: IF p_search_rel_sites = 'N' THEN
2530: OPEN x_cursor FOR
2531: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22
2532: FROM HZ_STAGED_PARTY_SITES stage
2533: WHERE contains( concat_col, p_contains_str)>0
2534: AND( (l_search_merged ='Y' )
2535: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2536: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2537: AND stage.party_id = p_party_id;
2538: ELSE
2539: OPEN x_cursor FOR
2540: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22
2541: FROM HZ_STAGED_PARTY_SITES stage
2542: WHERE contains( concat_col, p_contains_str)>0
2543: AND( (l_search_merged ='Y' )
2544: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2545: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2546: AND stage.party_id = p_party_id
2546: AND stage.party_id = p_party_id
2547: UNION
2548: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22
2549: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
2550: WHERE contains( concat_col, p_contains_str)>0
2551: AND( (l_search_merged ='Y' )
2552: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2553: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2554: AND r.object_id = p_party_id
2568: END IF;
2569: OPEN x_cursor FOR
2570: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22
2571: FROM HZ_STAGED_PARTY_SITES stage
2572: WHERE contains( concat_col, p_contains_str)>0
2573: AND EXISTS (
2574: SELECT 1 FROM HZ_STAGED_PARTIES p
2575: WHERE p.PARTY_ID = stage.PARTY_ID
2576: AND( (l_search_merged ='Y' )
2588: IF p_person_api = 'Y' THEN
2589: OPEN x_cursor FOR
2590: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22
2591: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
2592: WHERE contains( concat_col, p_contains_str)>0
2593: AND( (l_search_merged ='Y' )
2594: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2595: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2596: AND d.search_context_id = p_search_ctx_id
2598: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2599: UNION
2600: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22
2601: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
2602: WHERE contains( concat_col, p_contains_str)>0
2603: AND( (l_search_merged ='Y' )
2604: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2605: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2606: AND d.search_context_id = p_search_ctx_id
2611: ELSE
2612: OPEN x_cursor FOR
2613: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22
2614: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
2615: WHERE contains( concat_col, p_contains_str)>0
2616: AND( (l_search_merged ='Y' )
2617: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2618: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2619: AND d.search_context_id = p_search_ctx_id
2656: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2657: END IF;
2658: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22'||
2659: ' FROM HZ_STAGED_PARTY_SITES stage'||
2660: ' WHERE contains( concat_col, :cont)>0'||
2661: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2662: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2663: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2664: ' AND (ORG_CONTACT_ID IS NULL ' ||
2666: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2667: ' UNION ' ||
2668: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX11, TX12, TX14, TX20, 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 ORG_CONTACT_ID IN ' ||
2676: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2677: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2678: ' and ('||p_restrict_sql1|| ') )' ||
2679: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2680: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2681: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2682: ELSE
2683: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22'||
2684: ' FROM HZ_STAGED_PARTY_SITES stage'||
2677: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2678: ' and ('||p_restrict_sql1|| ') )' ||
2679: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2680: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2681: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2682: ELSE
2683: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22'||
2684: ' FROM HZ_STAGED_PARTY_SITES stage'||
2685: ' WHERE contains( concat_col, :cont)>0'||
2681: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2682: ELSE
2683: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22'||
2684: ' FROM HZ_STAGED_PARTY_SITES stage'||
2685: ' WHERE contains( concat_col, :cont)>0'||
2686: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2687: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2688: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2689: ' AND EXISTS ('||
2691: ' WHERE p.party_id = stage.party_id ' ||
2692: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2693: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2694: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2695: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2696: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2697: ,p_dup_party_id, p_dup_party_id;
2698: END IF;
2699: ELSE
2698: END IF;
2699: ELSE
2700: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22'||
2701: ' FROM HZ_STAGED_PARTY_SITES stage'||
2702: ' WHERE contains( concat_col, :cont)>0'||
2703: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2704: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2705: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2706: ' AND EXISTS ('||
2708: ' WHERE p.party_id = stage.party_id ' ||
2709: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2710: ' AND ('||p_restrict_sql||')' ||
2711: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2712: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2713: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2714: ,p_dup_party_id, p_dup_party_id;
2715: END IF;
2716: END IF;
2714: ,p_dup_party_id, p_dup_party_id;
2715: END IF;
2716: END IF;
2717: output_long_strings('----------------------------------------------------------');
2718: output_long_strings('Party Site Contains String = '||p_contains_str);
2719: output_long_strings('Restrict Sql = '||p_restrict_sql);
2720: exception
2721: when others then
2722: if (instrb(SQLERRM,'DRG-51030')>0) then
2742: PROCEDURE open_contact_cursor(
2743: p_dup_party_id NUMBER,
2744: p_party_id NUMBER,
2745: p_restrict_sql VARCHAR2,
2746: p_contains_str VARCHAR2,
2747: p_search_ctx_id NUMBER,
2748: p_search_merged VARCHAR2,
2749: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
2750: l_sqlstr VARCHAR2(4000);
2770: END IF;
2771: OPEN x_cursor FOR
2772: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID, TX5, TX2
2773: FROM HZ_STAGED_CONTACTS stage
2774: WHERE contains( concat_col, p_contains_str)>0
2775: AND EXISTS (
2776: SELECT 1 FROM HZ_STAGED_PARTIES p
2777: WHERE p.PARTY_ID = stage.PARTY_ID
2778: AND( (l_search_merged ='Y' )
2793: END IF;
2794: OPEN x_cursor FOR
2795: SELECT ORG_CONTACT_ID, PARTY_ID, TX5, TX2
2796: FROM HZ_STAGED_CONTACTS stage
2797: WHERE contains( concat_col, p_contains_str)>0
2798: AND EXISTS (
2799: SELECT 1 FROM HZ_STAGED_PARTIES p
2800: WHERE p.PARTY_ID = stage.PARTY_ID
2801: AND( (l_search_merged ='Y' )
2812: END IF;
2813: OPEN x_cursor FOR
2814: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID, TX5, TX2
2815: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
2816: WHERE contains( concat_col, p_contains_str)>0
2817: AND d.search_context_id = p_search_ctx_id
2818: AND d.party_id = stage.party_id
2819: AND( (l_search_merged ='Y' )
2820: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2846: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
2847: END IF;
2848: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID , TX5, TX2'||
2849: ' FROM HZ_STAGED_CONTACTS stage'||
2850: ' WHERE contains( concat_col, :cont)>0'||
2851: ' AND EXISTS ('||
2852: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2853: ' WHERE p.party_id = stage.party_id ' ||
2854: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2856: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2857: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2858: ' AND ('||p_restrict_sql||')' ||
2859: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2860: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2861: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2862: ,p_dup_party_id, p_dup_party_id;
2863: END IF;
2864: output_long_strings('----------------------------------------------------------');
2861: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2862: ,p_dup_party_id, p_dup_party_id;
2863: END IF;
2864: output_long_strings('----------------------------------------------------------');
2865: output_long_strings('Contacts Contains String = '||p_contains_str);
2866: output_long_strings('Restrict Sql = '||p_restrict_sql);
2867: exception
2868: when others then
2869: if (instrb(SQLERRM,'DRG-51030')>0) then
2889: PROCEDURE open_contact_pt_cursor(
2890: p_dup_party_id NUMBER,
2891: p_party_id NUMBER,
2892: p_restrict_sql VARCHAR2,
2893: p_contains_str VARCHAR2,
2894: p_search_ctx_id NUMBER,
2895: p_search_merged VARCHAR2,
2896: p_search_rel_cpts VARCHAR2,
2897: p_person_api VARCHAR2,
2919: THEN
2920: OPEN x_cursor FOR
2921: 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, TX5
2922: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2923: WHERE contains( concat_col, p_contains_str)>0
2924: AND d.search_context_id = p_search_ctx_id
2925: AND( (l_search_merged ='Y' )
2926: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2927: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2931: THEN
2932: OPEN x_cursor FOR
2933: 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, TX5
2934: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2935: WHERE contains( concat_col, p_contains_str)>0
2936: AND d.search_context_id = p_search_ctx_id
2937: AND( (l_search_merged ='Y' )
2938: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2939: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2948: IF p_search_rel_cpts = 'N' THEN
2949: OPEN x_cursor FOR
2950: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5
2951: FROM HZ_STAGED_CONTACT_POINTS stage
2952: WHERE contains( concat_col, p_contains_str)>0
2953: AND( (l_search_merged ='Y' )
2954: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2955: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2956: AND stage.party_id = p_party_id;
2957: ELSE
2958: OPEN x_cursor FOR
2959: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5
2960: FROM HZ_STAGED_CONTACT_POINTS stage
2961: WHERE contains( concat_col, p_contains_str)>0
2962: AND( (l_search_merged ='Y' )
2963: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2964: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2965: AND stage.party_id = p_party_id
2965: AND stage.party_id = p_party_id
2966: UNION
2967: 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, TX5
2968: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2969: WHERE contains( concat_col, p_contains_str)>0
2970: AND r.object_id = p_party_id
2971: AND( (l_search_merged ='Y' )
2972: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2973: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2987: END IF;
2988: OPEN x_cursor FOR
2989: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5
2990: FROM HZ_STAGED_CONTACT_POINTS stage
2991: WHERE contains( concat_col, p_contains_str)>0
2992: AND EXISTS (
2993: SELECT 1 FROM HZ_STAGED_PARTIES p
2994: WHERE p.PARTY_ID = stage.PARTY_ID
2995: AND( (l_search_merged ='Y' )
3007: IF p_person_api = 'Y' THEN
3008: OPEN x_cursor FOR
3009: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5
3010: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
3011: WHERE contains( concat_col, p_contains_str)>0
3012: AND d.search_context_id = p_search_ctx_id
3013: AND d.party_id = stage.party_id
3014: AND( (l_search_merged ='Y' )
3015: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
3017: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
3018: UNION
3019: 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, TX5
3020: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
3021: WHERE contains( concat_col, p_contains_str)>0
3022: AND d.search_context_id = p_search_ctx_id
3023: AND d.party_id = r.subject_id
3024: AND r.relationship_id = oc.party_relationship_id
3025: AND oc.org_contact_id = stage.org_contact_id
3030: ELSE
3031: OPEN x_cursor FOR
3032: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5
3033: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
3034: WHERE contains( concat_col, p_contains_str)>0
3035: AND d.search_context_id = p_search_ctx_id
3036: AND d.party_id = stage.party_id
3037: AND( (l_search_merged ='Y' )
3038: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
3075: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
3076: END IF;
3077: 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, TX5'||
3078: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3079: ' WHERE contains( concat_col, :cont)>0 '||
3080: ' AND (stage.org_contact_id is null '||
3081: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3082: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
3083: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
3085: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
3086: ' UNION ' ||
3087: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5'||
3088: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3089: ' WHERE contains( concat_col, :cont)>0 '||
3090: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3091: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
3092: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
3093: ' AND (stage.org_contact_id in ' ||
3095: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
3096: ' where stage1.relationship_id = oc.party_relationship_id ' ||
3097: ' and ('||p_restrict_sql1 || ') ) )' ||
3098: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
3099: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
3100: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
3101: ELSE
3102: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5'||
3103: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3096: ' where stage1.relationship_id = oc.party_relationship_id ' ||
3097: ' and ('||p_restrict_sql1 || ') ) )' ||
3098: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
3099: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
3100: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
3101: ELSE
3102: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5'||
3103: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3104: ' WHERE contains( concat_col, :cont)>0'||
3100: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
3101: ELSE
3102: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5'||
3103: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3104: ' WHERE contains( concat_col, :cont)>0'||
3105: ' AND EXISTS ('||
3106: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
3107: ' WHERE p.party_id = stage.party_id ' ||
3108: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
3110: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
3111: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
3112: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
3113: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
3114: OPEN x_cursor FOR l_sqlstr USING p_contains_str
3115: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
3116: ,p_dup_party_id, p_dup_party_id;
3117: END IF;
3118: ELSE
3117: END IF;
3118: ELSE
3119: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5'||
3120: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3121: ' WHERE contains( concat_col, :cont)>0'||
3122: ' AND EXISTS ('||
3123: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
3124: ' WHERE p.party_id = stage.party_id ' ||
3125: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
3127: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
3128: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
3129: ' AND ('||p_restrict_sql||')' ||
3130: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
3131: OPEN x_cursor FOR l_sqlstr USING p_contains_str
3132: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
3133: ,p_dup_party_id, p_dup_party_id;
3134: END IF;
3135: END IF;
3134: END IF;
3135: END IF;
3136: END IF;
3137: output_long_strings('----------------------------------------------------------');
3138: output_long_strings('Contact Points Contains String = '||p_contains_str);
3139: output_long_strings('Restrict Sql = '||p_restrict_sql);
3140: exception
3141: when others then
3142: if (instrb(SQLERRM,'DRG-51030')>0) then
3255: x_num_matches OUT NUMBER
3256: ) IS
3257:
3258: -- Strings to hold the generated Intermedia query strings
3259: l_party_contains_str VARCHAR2(32000);
3260: l_party_site_contains_str VARCHAR2(32000);
3261: l_contact_contains_str VARCHAR2(32000);
3262: l_contact_pt_contains_str VARCHAR2(32000);
3263: l_denorm_str VARCHAR2(32000);
3256: ) IS
3257:
3258: -- Strings to hold the generated Intermedia query strings
3259: l_party_contains_str VARCHAR2(32000);
3260: l_party_site_contains_str VARCHAR2(32000);
3261: l_contact_contains_str VARCHAR2(32000);
3262: l_contact_pt_contains_str VARCHAR2(32000);
3263: l_denorm_str VARCHAR2(32000);
3264: l_ps_denorm_str VARCHAR2(32000);
3257:
3258: -- Strings to hold the generated Intermedia query strings
3259: l_party_contains_str VARCHAR2(32000);
3260: l_party_site_contains_str VARCHAR2(32000);
3261: l_contact_contains_str VARCHAR2(32000);
3262: l_contact_pt_contains_str VARCHAR2(32000);
3263: l_denorm_str VARCHAR2(32000);
3264: l_ps_denorm_str VARCHAR2(32000);
3265: l_ct_denorm_str VARCHAR2(32000);
3258: -- Strings to hold the generated Intermedia query strings
3259: l_party_contains_str VARCHAR2(32000);
3260: l_party_site_contains_str VARCHAR2(32000);
3261: l_contact_contains_str VARCHAR2(32000);
3262: l_contact_pt_contains_str VARCHAR2(32000);
3263: l_denorm_str VARCHAR2(32000);
3264: l_ps_denorm_str VARCHAR2(32000);
3265: l_ct_denorm_str VARCHAR2(32000);
3266: l_cpt_denorm_str VARCHAR2(32000);
3365: call_type(2) := 'OR';
3366: END;
3367:
3368: /** Private procedure to acquire and score at party level ***/
3369: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3370: l_party_id_idx NUMBER:=1;
3371: l_ctx_id NUMBER;
3372: l_precalc_score BOOLEAN := FALSE;
3373: l_TX35_new varchar2(4000);
3397: l_search_ctx_id,H_PARTY_ID(I));
3398: l_party_id_idx := H_PARTY_ID.COUNT+1;
3399: ELSE
3400: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3401: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3402: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3403: 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);
3404: END IF;
3405: 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
3405: 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
3406: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3407: 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);
3408: END IF;
3409: IF (l_party_site_contains_str IS NULL AND
3410: l_contact_contains_str IS NULL AND
3411: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3412: 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);
3413: RAISE L_RETURN_IMM_EXC;
3406: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3407: 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);
3408: END IF;
3409: IF (l_party_site_contains_str IS NULL AND
3410: l_contact_contains_str IS NULL AND
3411: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3412: 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);
3413: RAISE L_RETURN_IMM_EXC;
3414: ELSE
3407: 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);
3408: END IF;
3409: IF (l_party_site_contains_str IS NULL AND
3410: l_contact_contains_str IS NULL AND
3411: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3412: 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);
3413: RAISE L_RETURN_IMM_EXC;
3414: ELSE
3415: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
3411: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3412: 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);
3413: RAISE L_RETURN_IMM_EXC;
3414: ELSE
3415: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
3416: END IF;
3417: ELSE
3418: IF p_index>1 THEN
3419: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3434: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3435: 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);
3436: END IF;
3437: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3438: 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);
3439: END IF;
3440: LOOP
3441: FETCH l_party_cur INTO
3442: l_party_id , l_TX8, l_TX32, l_TX35, l_TX42, l_TX44, l_TX45, l_TX46, l_TX47, l_TX48, l_TX156, l_TX157;
3458: l_party_id_idx:= l_party_id_idx+1;
3459: END IF;
3460: IF (l_party_id_idx-1)>l_max_thresh THEN
3461: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3462: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3463: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3464: AND l_contact_pt_contains_str IS NULL THEN
3465: EXIT;
3466: END IF;
3460: IF (l_party_id_idx-1)>l_max_thresh THEN
3461: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3462: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3463: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3464: AND l_contact_pt_contains_str IS NULL THEN
3465: EXIT;
3466: END IF;
3467: CLOSE l_party_cur;
3468: IF p_index>1 THEN
3489: ROLLBACK to eval_start;
3490: END;
3491:
3492: /** Private procedure to acquire and score at party site level ***/
3493: 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
3494: l_party_id_idx NUMBER:=1;
3495: l_ctx_id NUMBER;
3496: h_ps_id HZ_PARTY_SEARCH.IDList;
3497: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3501: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3502: 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);
3503: 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);
3504: END IF;
3505: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
3506: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
3507: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
3508: l_party_id := H_SCORES.FIRST;
3509: WHILE l_party_id IS NOT NULL LOOP
3502: 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);
3503: 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);
3504: END IF;
3505: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
3506: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
3507: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
3508: l_party_id := H_SCORES.FIRST;
3509: WHILE l_party_id IS NOT NULL LOOP
3510: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
3503: 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);
3504: END IF;
3505: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
3506: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
3507: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
3508: l_party_id := H_SCORES.FIRST;
3509: WHILE l_party_id IS NOT NULL LOOP
3510: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
3511: l_party_id:=H_SCORES.NEXT(l_party_id);
3533: l_search_ctx_id,H_PARTY_ID(I));
3534: l_party_id_idx := H_PARTY_ID.COUNT+1;
3535: ELSE
3536: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3537: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
3538: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3539: 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);
3540: END IF;
3541: IF p_index>1 THEN
3555: END IF;
3556: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3557: 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);
3558: END IF;
3559: 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);
3560: LOOP
3561: FETCH l_party_site_cur INTO
3562: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX11, l_TX12, l_TX14, l_TX20, l_TX22;
3563: EXIT WHEN l_party_site_cur%NOTFOUND;
3611: END IF;
3612: END;
3613:
3614: /** Private procedure to acquire and score at contact point level ***/
3615: 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
3616: l_party_id_idx NUMBER:=1;
3617: l_ctx_id NUMBER;
3618: h_ct_id HZ_PARTY_SEARCH.IDList;
3619: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3645: l_search_ctx_id,H_PARTY_ID(I));
3646: l_party_id_idx := H_PARTY_ID.COUNT+1;
3647: ELSE
3648: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3649: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
3650: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3651: 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);
3652: END IF;
3653: IF p_index>1 THEN
3667: END IF;
3668: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3669: 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);
3670: END IF;
3671: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
3672: LOOP
3673: FETCH l_contact_cur INTO
3674: l_org_contact_id, l_ct_party_id , l_TX5, l_TX2;
3675: EXIT WHEN l_contact_cur%NOTFOUND;
3720: END IF;
3721: END;
3722:
3723: /** Private procedure to acquire and score at contact point level ***/
3724: 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
3725: l_party_id_idx NUMBER:=1;
3726: l_ctx_id NUMBER;
3727: h_cpt_id HZ_PARTY_SEARCH.IDList;
3728: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3756: l_search_ctx_id,H_PARTY_ID(I));
3757: l_party_id_idx := H_PARTY_ID.COUNT+1;
3758: ELSE
3759: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3760: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
3761: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3762: 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);
3763: END IF;
3764: IF p_index>1 THEN
3778: END IF;
3779: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3780: 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);
3781: END IF;
3782: 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);
3783: LOOP
3784: FETCH l_contact_pt_cur INTO
3785: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX5;
3786: EXIT WHEN l_contact_pt_cur%NOTFOUND;
3959: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3960: l_threshold := round(( l_entered_max_score / 880) * 528);
3961: other_acq_criteria_exists := TRUE ;
3962: --check if acquisition criteria exists for any other entity
3963: IF l_party_contains_str IS NOT NULL THEN
3964: acq_cnt := 1;
3965: END IF;
3966: IF l_party_site_contains_str IS NOT NULL THEN
3967: acq_cnt := acq_cnt+1;
3962: --check if acquisition criteria exists for any other entity
3963: IF l_party_contains_str IS NOT NULL THEN
3964: acq_cnt := 1;
3965: END IF;
3966: IF l_party_site_contains_str IS NOT NULL THEN
3967: acq_cnt := acq_cnt+1;
3968: END IF;
3969: IF l_contact_contains_str IS NOT NULL THEN
3970: acq_cnt := acq_cnt+1;
3965: END IF;
3966: IF l_party_site_contains_str IS NOT NULL THEN
3967: acq_cnt := acq_cnt+1;
3968: END IF;
3969: IF l_contact_contains_str IS NOT NULL THEN
3970: acq_cnt := acq_cnt+1;
3971: END IF;
3972: IF l_contact_pt_contains_str IS NOT NULL THEN
3973: acq_cnt := acq_cnt+1;
3968: END IF;
3969: IF l_contact_contains_str IS NOT NULL THEN
3970: acq_cnt := acq_cnt+1;
3971: END IF;
3972: IF l_contact_pt_contains_str IS NOT NULL THEN
3973: acq_cnt := acq_cnt+1;
3974: END IF;
3975: IF acq_cnt>1 THEN
3976: other_acq_criteria_exists := TRUE;
4002: FND_MSG_PUB.ADD;
4003: RAISE FND_API.G_EXC_ERROR;
4004: END IF;
4005: END IF;
4006: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4007: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4008: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4009: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4010: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4003: RAISE FND_API.G_EXC_ERROR;
4004: END IF;
4005: END IF;
4006: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4007: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4008: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4009: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4010: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4011: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4004: END IF;
4005: END IF;
4006: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4007: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4008: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4009: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4010: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4011: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4012: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4005: END IF;
4006: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4007: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4008: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4009: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4010: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4011: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4012: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4013: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4006: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4007: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4008: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4009: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4010: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4011: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4012: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4013: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4014: END IF;
4007: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4008: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4009: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4010: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4011: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4012: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4013: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4014: END IF;
4015: END;
4008: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4009: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4010: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4011: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4012: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4013: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4014: END IF;
4015: END;
4016: /************ End of find_parties private procedures **********/
4009: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4010: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4011: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4012: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4013: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4014: END IF;
4015: END;
4016: /************ End of find_parties private procedures **********/
4017:
4081: 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);
4082: END IF;
4083: -- ************************************************************
4084: -- STEP 2. Setup of intermedia query strings for Acquisition query
4085: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4086: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4087: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4088: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4089: 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);
4082: END IF;
4083: -- ************************************************************
4084: -- STEP 2. Setup of intermedia query strings for Acquisition query
4085: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4086: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4087: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4088: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4089: 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);
4090: END IF;
4083: -- ************************************************************
4084: -- STEP 2. Setup of intermedia query strings for Acquisition query
4085: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4086: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4087: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4088: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4089: 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);
4090: END IF;
4091: l_denorm_max_score:=0;
4115: END IF;
4116: ELSE
4117: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
4118: END IF;
4119: 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((528/880)*l_entered_max_score));
4120: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4121:
4122: -- Setup Search Context ID
4123: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4125:
4126: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4127: 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);
4128: END IF;
4129: IF l_party_contains_str IS NULL THEN
4130: defpt := 1;
4131: END IF;
4132: IF l_party_site_contains_str IS NULL THEN
4133: defps := 1;
4128: END IF;
4129: IF l_party_contains_str IS NULL THEN
4130: defpt := 1;
4131: END IF;
4132: IF l_party_site_contains_str IS NULL THEN
4133: defps := 1;
4134: END IF;
4135: IF l_contact_contains_str IS NULL THEN
4136: defct := 1;
4131: END IF;
4132: IF l_party_site_contains_str IS NULL THEN
4133: defps := 1;
4134: END IF;
4135: IF l_contact_contains_str IS NULL THEN
4136: defct := 1;
4137: END IF;
4138: IF l_contact_pt_contains_str IS NULL THEN
4139: defcpt := 1;
4134: END IF;
4135: IF l_contact_contains_str IS NULL THEN
4136: defct := 1;
4137: END IF;
4138: IF l_contact_pt_contains_str IS NULL THEN
4139: defcpt := 1;
4140: END IF;
4141:
4142: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4143: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4144: 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);
4145: END IF;
4146: 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);
4147: 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);
4148: 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);
4149: 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);
4150: 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);
4151: 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);
4144: 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);
4145: END IF;
4146: 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);
4147: 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);
4148: 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);
4149: 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);
4150: 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);
4151: 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);
4152: END IF;
4145: END IF;
4146: 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);
4147: 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);
4148: 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);
4149: 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);
4150: 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);
4151: 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);
4152: END IF;
4153: FOR I in 1..5 LOOP
4146: 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);
4147: 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);
4148: 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);
4149: 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);
4150: 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);
4151: 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);
4152: END IF;
4153: FOR I in 1..5 LOOP
4154: do_eval(I);
4228: x_num_matches OUT NUMBER
4229: ) IS
4230:
4231: -- Strings to hold the generated Intermedia query strings
4232: l_party_contains_str VARCHAR2(32000);
4233: l_party_site_contains_str VARCHAR2(32000);
4234: l_contact_contains_str VARCHAR2(32000);
4235: l_contact_pt_contains_str VARCHAR2(32000);
4236: l_denorm_str VARCHAR2(32000);
4229: ) IS
4230:
4231: -- Strings to hold the generated Intermedia query strings
4232: l_party_contains_str VARCHAR2(32000);
4233: l_party_site_contains_str VARCHAR2(32000);
4234: l_contact_contains_str VARCHAR2(32000);
4235: l_contact_pt_contains_str VARCHAR2(32000);
4236: l_denorm_str VARCHAR2(32000);
4237: l_ps_denorm_str VARCHAR2(32000);
4230:
4231: -- Strings to hold the generated Intermedia query strings
4232: l_party_contains_str VARCHAR2(32000);
4233: l_party_site_contains_str VARCHAR2(32000);
4234: l_contact_contains_str VARCHAR2(32000);
4235: l_contact_pt_contains_str VARCHAR2(32000);
4236: l_denorm_str VARCHAR2(32000);
4237: l_ps_denorm_str VARCHAR2(32000);
4238: l_ct_denorm_str VARCHAR2(32000);
4231: -- Strings to hold the generated Intermedia query strings
4232: l_party_contains_str VARCHAR2(32000);
4233: l_party_site_contains_str VARCHAR2(32000);
4234: l_contact_contains_str VARCHAR2(32000);
4235: l_contact_pt_contains_str VARCHAR2(32000);
4236: l_denorm_str VARCHAR2(32000);
4237: l_ps_denorm_str VARCHAR2(32000);
4238: l_ct_denorm_str VARCHAR2(32000);
4239: l_cpt_denorm_str VARCHAR2(32000);
4368: call_type(2) := 'OR';
4369: END;
4370:
4371: /** Private procedure to acquire and score at party level ***/
4372: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
4373: l_party_id_idx NUMBER:=1;
4374: l_ctx_id NUMBER;
4375: l_TX35_new varchar2(4000);
4376: BEGIN
4390: l_search_ctx_id,H_PARTY_ID(I));
4391: l_party_id_idx := H_PARTY_ID.COUNT+1;
4392: ELSE
4393: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
4394: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
4395: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4396: 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);
4397: END IF;
4398: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
4398: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
4399: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4400: 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);
4401: END IF;
4402: IF (l_party_site_contains_str IS NULL AND
4403: l_contact_contains_str IS NULL AND
4404: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4405: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4406: RAISE L_RETURN_IMM_EXC;
4399: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4400: 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);
4401: END IF;
4402: IF (l_party_site_contains_str IS NULL AND
4403: l_contact_contains_str IS NULL AND
4404: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4405: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4406: RAISE L_RETURN_IMM_EXC;
4407: ELSE
4400: 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);
4401: END IF;
4402: IF (l_party_site_contains_str IS NULL AND
4403: l_contact_contains_str IS NULL AND
4404: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4405: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4406: RAISE L_RETURN_IMM_EXC;
4407: ELSE
4408: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
4404: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4405: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4406: RAISE L_RETURN_IMM_EXC;
4407: ELSE
4408: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
4409: END IF;
4410: ELSE
4411: IF p_index>1 THEN
4412: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4424: l_ctx_id := NULL;
4425: l_party_id_idx := H_PARTY_ID.COUNT+1;
4426: END IF;
4427: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
4428: 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);
4429: END IF;
4430: LOOP
4431: FETCH l_party_cur INTO
4432: l_party_id , l_TX8, l_TX32, l_TX35, l_TX42, l_TX44, l_TX45, l_TX46, l_TX47, l_TX48, l_TX156, l_TX157;
4448: l_party_id_idx:= l_party_id_idx+1;
4449: END IF;
4450: IF (l_party_id_idx-1)>l_max_thresh THEN
4451: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
4452: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
4453: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
4454: AND l_contact_pt_contains_str IS NULL THEN
4455: H_PARTY_ID.DELETE(l_party_id_idx-1);
4456: H_PARTY_ID_LIST.DELETE(l_index);
4450: IF (l_party_id_idx-1)>l_max_thresh THEN
4451: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
4452: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
4453: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
4454: AND l_contact_pt_contains_str IS NULL THEN
4455: H_PARTY_ID.DELETE(l_party_id_idx-1);
4456: H_PARTY_ID_LIST.DELETE(l_index);
4457: H_SCORES.DELETE(l_index);
4458: EXIT;
4479: CLOSE l_party_cur;
4480: ROLLBACK to eval_start;
4481: END;
4482: PROCEDURE open_person_contact_cursor(
4483: p_contains_str VARCHAR2,
4484: p_search_ctx_id NUMBER,
4485: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
4486: BEGIN
4487: OPEN x_cursor FOR
4486: BEGIN
4487: OPEN x_cursor FOR
4488: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID, TX5, TX2
4489: FROM HZ_STAGED_CONTACTS stage
4490: WHERE contains( concat_col, p_contains_str)>0
4491: AND ORG_CONTACT_ID in (
4492: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
4493: org_contact_id
4494: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc
4510: end if;
4511: END;
4512:
4513: /** Private procedure to acquire and score at party site level ***/
4514: 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
4515: l_party_id_idx NUMBER:=1;
4516: l_ctx_id NUMBER;
4517: h_ps_id HZ_PARTY_SEARCH.IDList;
4518: h_ps_party_id HZ_PARTY_SEARCH.IDList;
4538: ELSE
4539: l_party_id_idx := H_PARTY_ID.COUNT+1;
4540: l_ctx_id := NULL;
4541: END IF;
4542: 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);
4543: LOOP
4544: FETCH l_party_site_cur INTO
4545: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX11, l_TX12, l_TX14, l_TX20, l_TX22;
4546: EXIT WHEN l_party_site_cur%NOTFOUND;
4609: END IF;
4610: END;
4611:
4612: /** Private procedure to acquire and score at party site level ***/
4613: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
4614: l_party_id_idx NUMBER:=1;
4615: l_ctx_id NUMBER;
4616: h_ct_id HZ_PARTY_SEARCH.IDList;
4617: h_ct_party_id HZ_PARTY_SEARCH.IDList;
4625: FORALL I in 1..H_PARTY_ID.COUNT
4626: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
4627: l_search_ctx_id,H_PARTY_ID(I));
4628: l_party_id_idx := H_PARTY_ID.COUNT+1;
4629: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
4630: LOOP
4631: FETCH l_contact_cur INTO
4632: l_org_contact_id, l_ct_party_id , l_TX5, l_TX2;
4633: EXIT WHEN l_contact_cur%NOTFOUND;
4667: END IF;
4668: END;
4669:
4670: /** Private procedure to acquire and score at contact point level ***/
4671: 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
4672: l_party_id_idx NUMBER:=1;
4673: l_ctx_id NUMBER;
4674: h_cpt_id HZ_PARTY_SEARCH.IDList;
4675: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
4712: END IF;
4713: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4714: 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);
4715: END IF;
4716: 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);
4717: LOOP
4718: FETCH l_contact_pt_cur INTO
4719: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX5;
4720: EXIT WHEN l_contact_pt_cur%NOTFOUND;
4915: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
4916: l_threshold := round(( l_entered_max_score / 880) * 528);
4917: other_acq_criteria_exists := TRUE ;
4918: --check if acquisition criteria exists for any other entity
4919: IF l_party_contains_str IS NOT NULL THEN
4920: acq_cnt := 1;
4921: END IF;
4922: IF l_party_site_contains_str IS NOT NULL THEN
4923: acq_cnt := acq_cnt+1;
4918: --check if acquisition criteria exists for any other entity
4919: IF l_party_contains_str IS NOT NULL THEN
4920: acq_cnt := 1;
4921: END IF;
4922: IF l_party_site_contains_str IS NOT NULL THEN
4923: acq_cnt := acq_cnt+1;
4924: END IF;
4925: IF l_contact_contains_str IS NOT NULL THEN
4926: acq_cnt := acq_cnt+1;
4921: END IF;
4922: IF l_party_site_contains_str IS NOT NULL THEN
4923: acq_cnt := acq_cnt+1;
4924: END IF;
4925: IF l_contact_contains_str IS NOT NULL THEN
4926: acq_cnt := acq_cnt+1;
4927: END IF;
4928: IF l_contact_pt_contains_str IS NOT NULL THEN
4929: acq_cnt := acq_cnt+1;
4924: END IF;
4925: IF l_contact_contains_str IS NOT NULL THEN
4926: acq_cnt := acq_cnt+1;
4927: END IF;
4928: IF l_contact_pt_contains_str IS NOT NULL THEN
4929: acq_cnt := acq_cnt+1;
4930: END IF;
4931: IF acq_cnt>1 THEN
4932: other_acq_criteria_exists := TRUE;
4958: FND_MSG_PUB.ADD;
4959: RAISE FND_API.G_EXC_ERROR;
4960: END IF;
4961: END IF;
4962: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4963: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4964: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4965: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4966: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4959: RAISE FND_API.G_EXC_ERROR;
4960: END IF;
4961: END IF;
4962: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4963: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4964: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4965: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4966: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4967: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4960: END IF;
4961: END IF;
4962: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4963: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4964: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4965: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4966: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4967: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4968: END IF;
4961: END IF;
4962: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4963: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4964: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4965: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4966: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4967: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4968: END IF;
4969: END;
4962: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4963: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4964: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4965: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4966: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4967: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4968: END IF;
4969: END;
4970: /************ End of find_persons private procedures **********/
4963: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4964: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4965: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4966: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4967: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4968: END IF;
4969: END;
4970: /************ End of find_persons private procedures **********/
4971:
4998: g_score_until_thresh := false;
4999: END IF;
5000: -- ************************************************************
5001: -- STEP 2. Setup of intermedia query strings for Acquisition query
5002: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
5003: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
5004: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
5005: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
5006: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4999: END IF;
5000: -- ************************************************************
5001: -- STEP 2. Setup of intermedia query strings for Acquisition query
5002: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
5003: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
5004: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
5005: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
5006: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5007:
5000: -- ************************************************************
5001: -- STEP 2. Setup of intermedia query strings for Acquisition query
5002: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
5003: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
5004: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
5005: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
5006: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5007:
5008: -- Setup Search Context ID
5001: -- STEP 2. Setup of intermedia query strings for Acquisition query
5002: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
5003: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
5004: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
5005: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
5006: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5007:
5008: -- Setup Search Context ID
5009: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5008: -- Setup Search Context ID
5009: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5010: x_search_ctx_id := l_search_ctx_id;
5011:
5012: IF l_party_contains_str IS NULL THEN
5013: defpt := 1;
5014: END IF;
5015: IF l_party_site_contains_str IS NULL THEN
5016: defps := 1;
5011:
5012: IF l_party_contains_str IS NULL THEN
5013: defpt := 1;
5014: END IF;
5015: IF l_party_site_contains_str IS NULL THEN
5016: defps := 1;
5017: END IF;
5018: IF l_contact_contains_str IS NULL THEN
5019: defct := 1;
5014: END IF;
5015: IF l_party_site_contains_str IS NULL THEN
5016: defps := 1;
5017: END IF;
5018: IF l_contact_contains_str IS NULL THEN
5019: defct := 1;
5020: END IF;
5021: IF l_contact_pt_contains_str IS NULL THEN
5022: defcpt := 1;
5017: END IF;
5018: IF l_contact_contains_str IS NULL THEN
5019: defct := 1;
5020: END IF;
5021: IF l_contact_pt_contains_str IS NULL THEN
5022: defcpt := 1;
5023: END IF;
5024:
5025: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5023: END IF;
5024:
5025: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5026: 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);
5027: 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);
5028: 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);
5029: 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);
5030: 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);
5031: 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);
5024:
5025: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5026: 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);
5027: 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);
5028: 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);
5029: 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);
5030: 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);
5031: 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);
5032: END IF;
5025: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5026: 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);
5027: 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);
5028: 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);
5029: 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);
5030: 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);
5031: 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);
5032: END IF;
5033: FOR I in 1..5 LOOP
5026: 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);
5027: 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);
5028: 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);
5029: 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);
5030: 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);
5031: 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);
5032: END IF;
5033: FOR I in 1..5 LOOP
5034: do_eval(I);
5032: END IF;
5033: FOR I in 1..5 LOOP
5034: do_eval(I);
5035: END LOOP;
5036: IF l_contact_contains_str IS NOT NULL THEN
5037: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
5038: END IF;
5039: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5040: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 60 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5033: FOR I in 1..5 LOOP
5034: do_eval(I);
5035: END LOOP;
5036: IF l_contact_contains_str IS NOT NULL THEN
5037: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
5038: END IF;
5039: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5040: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 60 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5041: END IF;
5947: x_num_matches OUT NUMBER
5948: ) IS
5949:
5950: -- Strings to hold the generated Intermedia query strings
5951: l_party_contains_str VARCHAR2(32000);
5952: l_match_idx NUMBER;
5953: l_party_site_contains_str VARCHAR2(32000);
5954: l_contact_contains_str VARCHAR2(32000);
5955: l_contact_pt_contains_str VARCHAR2(32000);
5949:
5950: -- Strings to hold the generated Intermedia query strings
5951: l_party_contains_str VARCHAR2(32000);
5952: l_match_idx NUMBER;
5953: l_party_site_contains_str VARCHAR2(32000);
5954: l_contact_contains_str VARCHAR2(32000);
5955: l_contact_pt_contains_str VARCHAR2(32000);
5956: l_tmp VARCHAR2(32000);
5957:
5950: -- Strings to hold the generated Intermedia query strings
5951: l_party_contains_str VARCHAR2(32000);
5952: l_match_idx NUMBER;
5953: l_party_site_contains_str VARCHAR2(32000);
5954: l_contact_contains_str VARCHAR2(32000);
5955: l_contact_pt_contains_str VARCHAR2(32000);
5956: l_tmp VARCHAR2(32000);
5957:
5958: -- Other local variables
5951: l_party_contains_str VARCHAR2(32000);
5952: l_match_idx NUMBER;
5953: l_party_site_contains_str VARCHAR2(32000);
5954: l_contact_contains_str VARCHAR2(32000);
5955: l_contact_pt_contains_str VARCHAR2(32000);
5956: l_tmp VARCHAR2(32000);
5957:
5958: -- Other local variables
5959: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6034: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6035:
6036: -- ************************************************************
6037: -- STEP 2. Setup of intermedia query strings for Acquisition query
6038: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6039: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6040: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,p_party_site_list,HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,p_contact_point_list);
6041:
6042: -- Setup Search Context ID
6035:
6036: -- ************************************************************
6037: -- STEP 2. Setup of intermedia query strings for Acquisition query
6038: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6039: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6040: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,p_party_site_list,HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,p_contact_point_list);
6041:
6042: -- Setup Search Context ID
6043: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
6044: x_search_ctx_id := l_search_ctx_id;
6045:
6046: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6047: 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);
6048: 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);
6049: 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);
6050: 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);
6051: END IF;
6052: IF l_party_site_contains_str IS NULL THEN
6045:
6046: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6047: 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);
6048: 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);
6049: 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);
6050: 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);
6051: END IF;
6052: IF l_party_site_contains_str IS NULL THEN
6053: defps := 1;
6048: 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);
6049: 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);
6050: 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);
6051: END IF;
6052: IF l_party_site_contains_str IS NULL THEN
6053: defps := 1;
6054: END IF;
6055: IF l_contact_pt_contains_str IS NULL THEN
6056: defcpt := 1;
6051: END IF;
6052: IF l_party_site_contains_str IS NULL THEN
6053: defps := 1;
6054: END IF;
6055: IF l_contact_pt_contains_str IS NULL THEN
6056: defcpt := 1;
6057: END IF;
6058:
6059: IF l_party_site_contains_str IS NOT NULL THEN
6055: IF l_contact_pt_contains_str IS NULL THEN
6056: defcpt := 1;
6057: END IF;
6058:
6059: IF l_party_site_contains_str IS NOT NULL THEN
6060: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
6061: LOOP
6062: FETCH l_party_site_cur INTO
6063: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX11, l_TX12, l_TX14, l_TX20, l_TX22;
6056: defcpt := 1;
6057: END IF;
6058:
6059: IF l_party_site_contains_str IS NOT NULL THEN
6060: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
6061: LOOP
6062: FETCH l_party_site_cur INTO
6063: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX11, l_TX12, l_TX14, l_TX20, l_TX22;
6064: EXIT WHEN l_party_site_cur%NOTFOUND;
6079: END LOOP;
6080: CLOSE l_party_site_cur;
6081: END IF;
6082:
6083: IF l_contact_pt_contains_str IS NOT NULL THEN
6084: SAVEPOINT eval_start;
6085: IF l_match_str = ' AND ' OR (
6086: ((l_cpt_max_score/l_entered_max_score)<(528/880))
6087: ) THEN
6087: ) THEN
6088: FORALL I in 1..l_match_ps_list.COUNT
6089: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
6090: values (l_search_ctx_id,l_match_ps_list(I));
6091: 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');
6092: ELSE
6093: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6094: END IF;
6095: LOOP
6089: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
6090: values (l_search_ctx_id,l_match_ps_list(I));
6091: 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');
6092: ELSE
6093: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6094: END IF;
6095: LOOP
6096: FETCH l_contact_pt_cur INTO
6097: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX5;
6189: x_num_matches OUT NUMBER
6190: ) IS
6191:
6192: -- Strings to hold the generated Intermedia query strings
6193: l_party_contains_str VARCHAR2(32000);
6194: l_party_site_contains_str VARCHAR2(32000);
6195: l_contact_contains_str VARCHAR2(32000);
6196: l_contact_pt_contains_str VARCHAR2(32000);
6197: l_tmp VARCHAR2(32000);
6190: ) IS
6191:
6192: -- Strings to hold the generated Intermedia query strings
6193: l_party_contains_str VARCHAR2(32000);
6194: l_party_site_contains_str VARCHAR2(32000);
6195: l_contact_contains_str VARCHAR2(32000);
6196: l_contact_pt_contains_str VARCHAR2(32000);
6197: l_tmp VARCHAR2(32000);
6198:
6191:
6192: -- Strings to hold the generated Intermedia query strings
6193: l_party_contains_str VARCHAR2(32000);
6194: l_party_site_contains_str VARCHAR2(32000);
6195: l_contact_contains_str VARCHAR2(32000);
6196: l_contact_pt_contains_str VARCHAR2(32000);
6197: l_tmp VARCHAR2(32000);
6198:
6199: -- Other local variables
6192: -- Strings to hold the generated Intermedia query strings
6193: l_party_contains_str VARCHAR2(32000);
6194: l_party_site_contains_str VARCHAR2(32000);
6195: l_contact_contains_str VARCHAR2(32000);
6196: l_contact_pt_contains_str VARCHAR2(32000);
6197: l_tmp VARCHAR2(32000);
6198:
6199: -- Other local variables
6200: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6266: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6267:
6268: -- ************************************************************
6269: -- STEP 2. Setup of intermedia query strings for Acquisition query
6270: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6271: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6272: 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);
6273:
6274: -- Setup Search Context ID
6267:
6268: -- ************************************************************
6269: -- STEP 2. Setup of intermedia query strings for Acquisition query
6270: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6271: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6272: 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);
6273:
6274: -- Setup Search Context ID
6275: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
6276: x_search_ctx_id := l_search_ctx_id;
6277:
6278: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6279: 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);
6280: 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);
6281: 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);
6282: 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);
6283: END IF;
6284: IF l_contact_contains_str IS NULL THEN
6277:
6278: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6279: 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);
6280: 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);
6281: 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);
6282: 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);
6283: END IF;
6284: IF l_contact_contains_str IS NULL THEN
6285: defct := 1;
6280: 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);
6281: 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);
6282: 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);
6283: END IF;
6284: IF l_contact_contains_str IS NULL THEN
6285: defct := 1;
6286: END IF;
6287: IF l_contact_pt_contains_str IS NULL THEN
6288: defcpt := 1;
6283: END IF;
6284: IF l_contact_contains_str IS NULL THEN
6285: defct := 1;
6286: END IF;
6287: IF l_contact_pt_contains_str IS NULL THEN
6288: defcpt := 1;
6289: END IF;
6290:
6291: IF l_contact_contains_str IS NOT NULL THEN
6287: IF l_contact_pt_contains_str IS NULL THEN
6288: defcpt := 1;
6289: END IF;
6290:
6291: IF l_contact_contains_str IS NOT NULL THEN
6292: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
6293: LOOP
6294: FETCH l_contact_cur INTO
6295: l_org_contact_id, l_ct_party_id , l_TX5, l_TX2;
6288: defcpt := 1;
6289: END IF;
6290:
6291: IF l_contact_contains_str IS NOT NULL THEN
6292: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
6293: LOOP
6294: FETCH l_contact_cur INTO
6295: l_org_contact_id, l_ct_party_id , l_TX5, l_TX2;
6296: EXIT WHEN l_contact_cur%NOTFOUND;
6309: END LOOP;
6310: CLOSE l_contact_cur;
6311: END IF;
6312:
6313: IF l_contact_pt_contains_str IS NOT NULL THEN
6314: SAVEPOINT eval_start;
6315: IF l_match_str = ' AND ' OR (
6316: ((l_cpt_max_score/l_entered_max_score)<(528/880))
6317: ) THEN
6317: ) THEN
6318: FORALL I in 1..l_match_contact_list.COUNT
6319: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
6320: values (l_search_ctx_id,l_match_contact_list(I));
6321: 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');
6322: ELSE
6323: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6324: END IF;
6325: LOOP
6319: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
6320: values (l_search_ctx_id,l_match_contact_list(I));
6321: 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');
6322: ELSE
6323: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6324: END IF;
6325: LOOP
6326: FETCH l_contact_pt_cur INTO
6327: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX5;
6417: ) IS
6418:
6419:
6420: -- Strings to hold the generated Intermedia query strings
6421: l_contact_pt_contains_str VARCHAR2(32000);
6422: l_tmp VARCHAR2(32000);
6423:
6424: -- Other local variables
6425: 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_TX5;
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)
6666: g_score_until_thresh := false;
6667: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6668: -- ************************************************************
6669: -- STEP 2. Setup of intermedia query strings for Acquisition query
6670: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6671: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6672: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6673: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6674:
6667: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6668: -- ************************************************************
6669: -- STEP 2. Setup of intermedia query strings for Acquisition query
6670: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6671: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6672: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6673: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6674:
6675: -- Setup Search Context ID
6668: -- ************************************************************
6669: -- STEP 2. Setup of intermedia query strings for Acquisition query
6670: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6671: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6672: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6673: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6674:
6675: -- Setup Search Context ID
6676: IF x_search_ctx_id IS NULL THEN
6690: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX8,l_TX32,l_TX35_new,l_TX42,l_TX44,l_TX45,l_TX46,l_TX47,l_TX48,l_TX156,l_TX157,1);
6691: END LOOP;
6692: CLOSE l_party_cur;
6693:
6694: IF l_party_site_contains_str IS NOT NULL THEN
6695: l_max_score := 0;
6696: l_max_id := 0;
6697: l_max_idx := 0;
6698: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6695: l_max_score := 0;
6696: l_max_id := 0;
6697: l_max_idx := 0;
6698: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6699: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
6700: ELSE
6701: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
6702: END IF;
6703: LOOP
6697: l_max_idx := 0;
6698: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6699: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
6700: ELSE
6701: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
6702: END IF;
6703: LOOP
6704: FETCH l_party_site_cur INTO
6705: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX11, l_TX12, l_TX14, l_TX20, l_TX22;
6724: INSERT_PARTY_SITES_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_party_site_list(l_max_idx), g_party_site_stage_list(l_max_idx) ,l_max_TX3,l_max_TX4,l_max_TX9,l_max_TX11,l_max_TX12,l_max_TX14,l_max_TX20,l_max_TX22,l_max_idx);
6725: END IF;
6726: END IF;
6727:
6728: IF l_contact_contains_str IS NOT NULL THEN
6729: l_max_score := 0;
6730: l_max_id := 0;
6731: l_max_idx := 0;
6732: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
6728: IF l_contact_contains_str IS NOT NULL THEN
6729: l_max_score := 0;
6730: l_max_id := 0;
6731: l_max_idx := 0;
6732: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
6733: LOOP
6734: FETCH l_contact_cur INTO
6735: l_org_contact_id, l_ct_party_id , l_TX5, l_TX2;
6736: EXIT WHEN l_contact_cur%NOTFOUND;
6748: 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_TX5,l_max_TX2,l_max_idx);
6749: END IF;
6750: END IF;
6751:
6752: IF l_contact_pt_contains_str IS NOT NULL THEN
6753: l_max_score := 0;
6754: l_max_id := 0;
6755: l_max_idx := 0;
6756: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6753: l_max_score := 0;
6754: l_max_id := 0;
6755: l_max_idx := 0;
6756: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6757: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
6758: ELSE
6759: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
6760: END IF;
6761: LOOP
6755: l_max_idx := 0;
6756: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6757: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
6758: ELSE
6759: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
6760: END IF;
6761: LOOP
6762: FETCH l_contact_pt_cur INTO
6763: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX5;