[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 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)
2450: END IF;
2451: IF p_search_ctx_id IS NULL THEN
2452: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157'||
2453: ' FROM HZ_STAGED_PARTIES stage'||
2454: ' WHERE contains( concat_col, :cont)>0'||
2455: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
2456: ' AND ('||p_restrict_sql||')' ||
2457: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2458: IF l_search_merged = 'Y' THEN
2462: ELSE
2463: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
2464: END IF;
2465: output_long_strings('----------------------------------------------------------');
2466: output_long_strings('Party Contains String = '||p_contains_str);
2467: output_long_strings('Restrict Sql = '||p_restrict_sql);
2468: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2469: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2470: ,p_dup_party_id, p_dup_party_id;
2464: END IF;
2465: output_long_strings('----------------------------------------------------------');
2466: output_long_strings('Party Contains String = '||p_contains_str);
2467: output_long_strings('Restrict Sql = '||p_restrict_sql);
2468: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2469: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2470: ,p_dup_party_id, p_dup_party_id;
2471: END IF;
2472: END IF;
2495: PROCEDURE open_party_site_cursor(
2496: p_dup_party_id NUMBER,
2497: p_party_id NUMBER,
2498: p_restrict_sql VARCHAR2,
2499: p_contains_str VARCHAR2,
2500: p_search_ctx_id NUMBER,
2501: p_search_merged VARCHAR2,
2502: p_search_rel_sites VARCHAR2,
2503: p_person_api VARCHAR2,
2527: IF p_search_rel_sites = 'N' THEN
2528: OPEN x_cursor FOR
2529: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22
2530: FROM HZ_STAGED_PARTY_SITES stage
2531: WHERE contains( concat_col, p_contains_str)>0
2532: AND( (l_search_merged ='Y' )
2533: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2534: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2535: AND stage.party_id = p_party_id;
2536: ELSE
2537: OPEN x_cursor FOR
2538: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22
2539: FROM HZ_STAGED_PARTY_SITES stage
2540: WHERE contains( concat_col, p_contains_str)>0
2541: AND( (l_search_merged ='Y' )
2542: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2543: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2544: AND stage.party_id = p_party_id
2544: AND stage.party_id = p_party_id
2545: UNION
2546: 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
2547: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
2548: WHERE contains( concat_col, p_contains_str)>0
2549: AND( (l_search_merged ='Y' )
2550: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2551: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2552: AND r.object_id = p_party_id
2566: END IF;
2567: OPEN x_cursor FOR
2568: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22
2569: FROM HZ_STAGED_PARTY_SITES stage
2570: WHERE contains( concat_col, p_contains_str)>0
2571: AND EXISTS (
2572: SELECT 1 FROM HZ_STAGED_PARTIES p
2573: WHERE p.PARTY_ID = stage.PARTY_ID
2574: AND( (l_search_merged ='Y' )
2586: IF p_person_api = 'Y' THEN
2587: OPEN x_cursor FOR
2588: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22
2589: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
2590: WHERE contains( concat_col, p_contains_str)>0
2591: AND( (l_search_merged ='Y' )
2592: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2593: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2594: AND d.search_context_id = p_search_ctx_id
2596: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2597: UNION
2598: 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
2599: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
2600: WHERE contains( concat_col, p_contains_str)>0
2601: AND( (l_search_merged ='Y' )
2602: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2603: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2604: AND d.search_context_id = p_search_ctx_id
2609: ELSE
2610: OPEN x_cursor FOR
2611: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22
2612: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
2613: WHERE contains( concat_col, p_contains_str)>0
2614: AND( (l_search_merged ='Y' )
2615: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2616: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2617: AND d.search_context_id = p_search_ctx_id
2654: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2655: END IF;
2656: 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'||
2657: ' FROM HZ_STAGED_PARTY_SITES stage'||
2658: ' WHERE contains( concat_col, :cont)>0'||
2659: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2660: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2661: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2662: ' AND (ORG_CONTACT_ID IS NULL ' ||
2664: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2665: ' UNION ' ||
2666: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22'||
2667: ' FROM HZ_STAGED_PARTY_SITES stage'||
2668: ' WHERE contains( concat_col, :cont)>0'||
2669: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2670: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2671: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2672: ' AND ORG_CONTACT_ID IN ' ||
2674: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2675: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2676: ' and ('||p_restrict_sql1|| ') )' ||
2677: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2678: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2679: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2680: ELSE
2681: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22'||
2682: ' FROM HZ_STAGED_PARTY_SITES stage'||
2675: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2676: ' and ('||p_restrict_sql1|| ') )' ||
2677: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2678: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2679: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2680: ELSE
2681: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22'||
2682: ' FROM HZ_STAGED_PARTY_SITES stage'||
2683: ' WHERE contains( concat_col, :cont)>0'||
2679: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2680: ELSE
2681: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22'||
2682: ' FROM HZ_STAGED_PARTY_SITES stage'||
2683: ' WHERE contains( concat_col, :cont)>0'||
2684: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2685: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2686: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2687: ' AND EXISTS ('||
2689: ' WHERE p.party_id = stage.party_id ' ||
2690: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2691: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2692: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2693: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2694: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2695: ,p_dup_party_id, p_dup_party_id;
2696: END IF;
2697: ELSE
2696: END IF;
2697: ELSE
2698: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22'||
2699: ' FROM HZ_STAGED_PARTY_SITES stage'||
2700: ' WHERE contains( concat_col, :cont)>0'||
2701: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2702: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2703: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2704: ' AND EXISTS ('||
2706: ' WHERE p.party_id = stage.party_id ' ||
2707: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2708: ' AND ('||p_restrict_sql||')' ||
2709: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2710: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2711: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2712: ,p_dup_party_id, p_dup_party_id;
2713: END IF;
2714: END IF;
2712: ,p_dup_party_id, p_dup_party_id;
2713: END IF;
2714: END IF;
2715: output_long_strings('----------------------------------------------------------');
2716: output_long_strings('Party Site Contains String = '||p_contains_str);
2717: output_long_strings('Restrict Sql = '||p_restrict_sql);
2718: exception
2719: when others then
2720: if (instrb(SQLERRM,'DRG-51030')>0) then
2740: PROCEDURE open_contact_cursor(
2741: p_dup_party_id NUMBER,
2742: p_party_id NUMBER,
2743: p_restrict_sql VARCHAR2,
2744: p_contains_str VARCHAR2,
2745: p_search_ctx_id NUMBER,
2746: p_search_merged VARCHAR2,
2747: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
2748: l_sqlstr VARCHAR2(4000);
2768: END IF;
2769: OPEN x_cursor FOR
2770: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID, TX5, TX2
2771: FROM HZ_STAGED_CONTACTS stage
2772: WHERE contains( concat_col, p_contains_str)>0
2773: AND EXISTS (
2774: SELECT 1 FROM HZ_STAGED_PARTIES p
2775: WHERE p.PARTY_ID = stage.PARTY_ID
2776: AND( (l_search_merged ='Y' )
2791: END IF;
2792: OPEN x_cursor FOR
2793: SELECT ORG_CONTACT_ID, PARTY_ID, TX5, TX2
2794: FROM HZ_STAGED_CONTACTS stage
2795: WHERE contains( concat_col, p_contains_str)>0
2796: AND EXISTS (
2797: SELECT 1 FROM HZ_STAGED_PARTIES p
2798: WHERE p.PARTY_ID = stage.PARTY_ID
2799: AND( (l_search_merged ='Y' )
2810: END IF;
2811: OPEN x_cursor FOR
2812: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID, TX5, TX2
2813: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
2814: WHERE contains( concat_col, p_contains_str)>0
2815: AND d.search_context_id = p_search_ctx_id
2816: AND d.party_id = stage.party_id
2817: AND( (l_search_merged ='Y' )
2818: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2844: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
2845: END IF;
2846: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID , TX5, TX2'||
2847: ' FROM HZ_STAGED_CONTACTS stage'||
2848: ' WHERE contains( concat_col, :cont)>0'||
2849: ' AND EXISTS ('||
2850: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2851: ' WHERE p.party_id = stage.party_id ' ||
2852: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2854: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2855: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2856: ' AND ('||p_restrict_sql||')' ||
2857: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2858: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2859: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2860: ,p_dup_party_id, p_dup_party_id;
2861: END IF;
2862: output_long_strings('----------------------------------------------------------');
2859: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2860: ,p_dup_party_id, p_dup_party_id;
2861: END IF;
2862: output_long_strings('----------------------------------------------------------');
2863: output_long_strings('Contacts Contains String = '||p_contains_str);
2864: output_long_strings('Restrict Sql = '||p_restrict_sql);
2865: exception
2866: when others then
2867: if (instrb(SQLERRM,'DRG-51030')>0) then
2887: PROCEDURE open_contact_pt_cursor(
2888: p_dup_party_id NUMBER,
2889: p_party_id NUMBER,
2890: p_restrict_sql VARCHAR2,
2891: p_contains_str VARCHAR2,
2892: p_search_ctx_id NUMBER,
2893: p_search_merged VARCHAR2,
2894: p_search_rel_cpts VARCHAR2,
2895: p_person_api VARCHAR2,
2917: THEN
2918: OPEN x_cursor FOR
2919: 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
2920: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2921: WHERE contains( concat_col, p_contains_str)>0
2922: AND d.search_context_id = p_search_ctx_id
2923: AND( (l_search_merged ='Y' )
2924: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2925: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2929: THEN
2930: OPEN x_cursor FOR
2931: 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
2932: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2933: WHERE contains( concat_col, p_contains_str)>0
2934: AND d.search_context_id = p_search_ctx_id
2935: AND( (l_search_merged ='Y' )
2936: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2937: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2946: IF p_search_rel_cpts = 'N' THEN
2947: OPEN x_cursor FOR
2948: 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
2949: FROM HZ_STAGED_CONTACT_POINTS stage
2950: WHERE contains( concat_col, p_contains_str)>0
2951: AND( (l_search_merged ='Y' )
2952: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2953: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2954: AND stage.party_id = p_party_id;
2955: ELSE
2956: OPEN x_cursor FOR
2957: 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
2958: FROM HZ_STAGED_CONTACT_POINTS stage
2959: WHERE contains( concat_col, p_contains_str)>0
2960: AND( (l_search_merged ='Y' )
2961: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2962: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2963: AND stage.party_id = p_party_id
2963: AND stage.party_id = p_party_id
2964: UNION
2965: 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
2966: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2967: WHERE contains( concat_col, p_contains_str)>0
2968: AND r.object_id = p_party_id
2969: AND( (l_search_merged ='Y' )
2970: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2971: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2985: END IF;
2986: OPEN x_cursor FOR
2987: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5
2988: FROM HZ_STAGED_CONTACT_POINTS stage
2989: WHERE contains( concat_col, p_contains_str)>0
2990: AND EXISTS (
2991: SELECT 1 FROM HZ_STAGED_PARTIES p
2992: WHERE p.PARTY_ID = stage.PARTY_ID
2993: AND( (l_search_merged ='Y' )
3005: IF p_person_api = 'Y' THEN
3006: OPEN x_cursor FOR
3007: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5
3008: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
3009: WHERE contains( concat_col, p_contains_str)>0
3010: AND d.search_context_id = p_search_ctx_id
3011: AND d.party_id = stage.party_id
3012: AND( (l_search_merged ='Y' )
3013: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
3015: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
3016: UNION
3017: 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
3018: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
3019: WHERE contains( concat_col, p_contains_str)>0
3020: AND d.search_context_id = p_search_ctx_id
3021: AND d.party_id = r.subject_id
3022: AND r.relationship_id = oc.party_relationship_id
3023: AND oc.org_contact_id = stage.org_contact_id
3028: ELSE
3029: OPEN x_cursor FOR
3030: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5
3031: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
3032: WHERE contains( concat_col, p_contains_str)>0
3033: AND d.search_context_id = p_search_ctx_id
3034: AND d.party_id = stage.party_id
3035: AND( (l_search_merged ='Y' )
3036: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
3073: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
3074: END IF;
3075: 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'||
3076: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3077: ' WHERE contains( concat_col, :cont)>0 '||
3078: ' AND (stage.org_contact_id is null '||
3079: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3080: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
3081: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
3083: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
3084: ' UNION ' ||
3085: ' 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'||
3086: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3087: ' WHERE contains( concat_col, :cont)>0 '||
3088: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3089: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
3090: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
3091: ' AND (stage.org_contact_id in ' ||
3093: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
3094: ' where stage1.relationship_id = oc.party_relationship_id ' ||
3095: ' and ('||p_restrict_sql1 || ') ) )' ||
3096: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
3097: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
3098: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
3099: ELSE
3100: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5'||
3101: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3094: ' where stage1.relationship_id = oc.party_relationship_id ' ||
3095: ' and ('||p_restrict_sql1 || ') ) )' ||
3096: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
3097: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
3098: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
3099: ELSE
3100: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5'||
3101: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3102: ' WHERE contains( concat_col, :cont)>0'||
3098: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
3099: ELSE
3100: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5'||
3101: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3102: ' WHERE contains( concat_col, :cont)>0'||
3103: ' AND EXISTS ('||
3104: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
3105: ' WHERE p.party_id = stage.party_id ' ||
3106: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
3108: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
3109: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
3110: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
3111: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
3112: OPEN x_cursor FOR l_sqlstr USING p_contains_str
3113: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
3114: ,p_dup_party_id, p_dup_party_id;
3115: END IF;
3116: ELSE
3115: END IF;
3116: ELSE
3117: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5'||
3118: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3119: ' WHERE contains( concat_col, :cont)>0'||
3120: ' AND EXISTS ('||
3121: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
3122: ' WHERE p.party_id = stage.party_id ' ||
3123: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
3125: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
3126: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
3127: ' AND ('||p_restrict_sql||')' ||
3128: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
3129: OPEN x_cursor FOR l_sqlstr USING p_contains_str
3130: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
3131: ,p_dup_party_id, p_dup_party_id;
3132: END IF;
3133: END IF;
3132: END IF;
3133: END IF;
3134: END IF;
3135: output_long_strings('----------------------------------------------------------');
3136: output_long_strings('Contact Points Contains String = '||p_contains_str);
3137: output_long_strings('Restrict Sql = '||p_restrict_sql);
3138: exception
3139: when others then
3140: if (instrb(SQLERRM,'DRG-51030')>0) then
3253: x_num_matches OUT NUMBER
3254: ) IS
3255:
3256: -- Strings to hold the generated Intermedia query strings
3257: l_party_contains_str VARCHAR2(32000);
3258: l_party_site_contains_str VARCHAR2(32000);
3259: l_contact_contains_str VARCHAR2(32000);
3260: l_contact_pt_contains_str VARCHAR2(32000);
3261: l_denorm_str VARCHAR2(32000);
3254: ) IS
3255:
3256: -- Strings to hold the generated Intermedia query strings
3257: l_party_contains_str VARCHAR2(32000);
3258: l_party_site_contains_str VARCHAR2(32000);
3259: l_contact_contains_str VARCHAR2(32000);
3260: l_contact_pt_contains_str VARCHAR2(32000);
3261: l_denorm_str VARCHAR2(32000);
3262: l_ps_denorm_str VARCHAR2(32000);
3255:
3256: -- Strings to hold the generated Intermedia query strings
3257: l_party_contains_str VARCHAR2(32000);
3258: l_party_site_contains_str VARCHAR2(32000);
3259: l_contact_contains_str VARCHAR2(32000);
3260: l_contact_pt_contains_str VARCHAR2(32000);
3261: l_denorm_str VARCHAR2(32000);
3262: l_ps_denorm_str VARCHAR2(32000);
3263: l_ct_denorm_str VARCHAR2(32000);
3256: -- Strings to hold the generated Intermedia query strings
3257: l_party_contains_str VARCHAR2(32000);
3258: l_party_site_contains_str VARCHAR2(32000);
3259: l_contact_contains_str VARCHAR2(32000);
3260: l_contact_pt_contains_str VARCHAR2(32000);
3261: l_denorm_str VARCHAR2(32000);
3262: l_ps_denorm_str VARCHAR2(32000);
3263: l_ct_denorm_str VARCHAR2(32000);
3264: l_cpt_denorm_str VARCHAR2(32000);
3363: call_type(2) := 'OR';
3364: END;
3365:
3366: /** Private procedure to acquire and score at party level ***/
3367: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3368: l_party_id_idx NUMBER:=1;
3369: l_ctx_id NUMBER;
3370: l_precalc_score BOOLEAN := FALSE;
3371: BEGIN
3394: l_search_ctx_id,H_PARTY_ID(I));
3395: l_party_id_idx := H_PARTY_ID.COUNT+1;
3396: ELSE
3397: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3398: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3399: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3400: 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);
3401: END IF;
3402: 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
3402: 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
3403: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3404: 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);
3405: END IF;
3406: IF (l_party_site_contains_str IS NULL AND
3407: l_contact_contains_str IS NULL AND
3408: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3409: 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);
3410: RAISE L_RETURN_IMM_EXC;
3403: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3404: 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);
3405: END IF;
3406: IF (l_party_site_contains_str IS NULL AND
3407: l_contact_contains_str IS NULL AND
3408: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3409: 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);
3410: RAISE L_RETURN_IMM_EXC;
3411: ELSE
3404: 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);
3405: END IF;
3406: IF (l_party_site_contains_str IS NULL AND
3407: l_contact_contains_str IS NULL AND
3408: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3409: 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);
3410: RAISE L_RETURN_IMM_EXC;
3411: ELSE
3412: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
3408: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
3409: 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);
3410: RAISE L_RETURN_IMM_EXC;
3411: ELSE
3412: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
3413: END IF;
3414: ELSE
3415: IF p_index>1 THEN
3416: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3431: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3432: 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);
3433: END IF;
3434: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3435: 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);
3436: END IF;
3437: LOOP
3438: FETCH l_party_cur INTO
3439: 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;
3453: l_party_id_idx:= l_party_id_idx+1;
3454: END IF;
3455: IF (l_party_id_idx-1)>l_max_thresh THEN
3456: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3457: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3458: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3459: AND l_contact_pt_contains_str IS NULL THEN
3460: EXIT;
3461: END IF;
3455: IF (l_party_id_idx-1)>l_max_thresh THEN
3456: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3457: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3458: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3459: AND l_contact_pt_contains_str IS NULL THEN
3460: EXIT;
3461: END IF;
3462: CLOSE l_party_cur;
3463: IF p_index>1 THEN
3484: ROLLBACK to eval_start;
3485: END;
3486:
3487: /** Private procedure to acquire and score at party site level ***/
3488: 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
3489: l_party_id_idx NUMBER:=1;
3490: l_ctx_id NUMBER;
3491: h_ps_id HZ_PARTY_SEARCH.IDList;
3492: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3496: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3497: 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);
3498: 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);
3499: END IF;
3500: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
3501: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
3502: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
3503: l_party_id := H_SCORES.FIRST;
3504: WHILE l_party_id IS NOT NULL LOOP
3497: 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);
3498: 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);
3499: END IF;
3500: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
3501: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
3502: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
3503: l_party_id := H_SCORES.FIRST;
3504: WHILE l_party_id IS NOT NULL LOOP
3505: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
3498: 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);
3499: END IF;
3500: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
3501: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
3502: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
3503: l_party_id := H_SCORES.FIRST;
3504: WHILE l_party_id IS NOT NULL LOOP
3505: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
3506: l_party_id:=H_SCORES.NEXT(l_party_id);
3528: l_search_ctx_id,H_PARTY_ID(I));
3529: l_party_id_idx := H_PARTY_ID.COUNT+1;
3530: ELSE
3531: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3532: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
3533: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3534: 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);
3535: END IF;
3536: IF p_index>1 THEN
3550: END IF;
3551: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3552: 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);
3553: END IF;
3554: 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);
3555: LOOP
3556: FETCH l_party_site_cur INTO
3557: 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;
3558: EXIT WHEN l_party_site_cur%NOTFOUND;
3606: END IF;
3607: END;
3608:
3609: /** Private procedure to acquire and score at contact point level ***/
3610: 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
3611: l_party_id_idx NUMBER:=1;
3612: l_ctx_id NUMBER;
3613: h_ct_id HZ_PARTY_SEARCH.IDList;
3614: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3640: l_search_ctx_id,H_PARTY_ID(I));
3641: l_party_id_idx := H_PARTY_ID.COUNT+1;
3642: ELSE
3643: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3644: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
3645: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3646: 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);
3647: END IF;
3648: IF p_index>1 THEN
3662: END IF;
3663: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3664: 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);
3665: END IF;
3666: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
3667: LOOP
3668: FETCH l_contact_cur INTO
3669: l_org_contact_id, l_ct_party_id , l_TX5, l_TX2;
3670: EXIT WHEN l_contact_cur%NOTFOUND;
3715: END IF;
3716: END;
3717:
3718: /** Private procedure to acquire and score at contact point level ***/
3719: 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
3720: l_party_id_idx NUMBER:=1;
3721: l_ctx_id NUMBER;
3722: h_cpt_id HZ_PARTY_SEARCH.IDList;
3723: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3751: l_search_ctx_id,H_PARTY_ID(I));
3752: l_party_id_idx := H_PARTY_ID.COUNT+1;
3753: ELSE
3754: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3755: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
3756: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3757: 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);
3758: END IF;
3759: IF p_index>1 THEN
3773: END IF;
3774: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3775: 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);
3776: END IF;
3777: 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);
3778: LOOP
3779: FETCH l_contact_pt_cur INTO
3780: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX5;
3781: EXIT WHEN l_contact_pt_cur%NOTFOUND;
3954: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3955: l_threshold := round(( l_entered_max_score / 880) * 528);
3956: other_acq_criteria_exists := TRUE ;
3957: --check if acquisition criteria exists for any other entity
3958: IF l_party_contains_str IS NOT NULL THEN
3959: acq_cnt := 1;
3960: END IF;
3961: IF l_party_site_contains_str IS NOT NULL THEN
3962: acq_cnt := acq_cnt+1;
3957: --check if acquisition criteria exists for any other entity
3958: IF l_party_contains_str IS NOT NULL THEN
3959: acq_cnt := 1;
3960: END IF;
3961: IF l_party_site_contains_str IS NOT NULL THEN
3962: acq_cnt := acq_cnt+1;
3963: END IF;
3964: IF l_contact_contains_str IS NOT NULL THEN
3965: acq_cnt := acq_cnt+1;
3960: END IF;
3961: IF l_party_site_contains_str IS NOT NULL THEN
3962: acq_cnt := acq_cnt+1;
3963: END IF;
3964: IF l_contact_contains_str IS NOT NULL THEN
3965: acq_cnt := acq_cnt+1;
3966: END IF;
3967: IF l_contact_pt_contains_str IS NOT NULL THEN
3968: acq_cnt := acq_cnt+1;
3963: END IF;
3964: IF l_contact_contains_str IS NOT NULL THEN
3965: acq_cnt := acq_cnt+1;
3966: END IF;
3967: IF l_contact_pt_contains_str IS NOT NULL THEN
3968: acq_cnt := acq_cnt+1;
3969: END IF;
3970: IF acq_cnt>1 THEN
3971: other_acq_criteria_exists := TRUE;
3997: FND_MSG_PUB.ADD;
3998: RAISE FND_API.G_EXC_ERROR;
3999: END IF;
4000: END IF;
4001: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4002: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4003: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4004: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4005: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3998: RAISE FND_API.G_EXC_ERROR;
3999: END IF;
4000: END IF;
4001: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4002: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4003: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4004: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4005: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4006: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3999: END IF;
4000: END IF;
4001: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4002: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4003: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4004: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4005: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4006: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4007: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4000: END IF;
4001: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4002: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4003: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4004: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4005: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4006: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4007: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4008: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4001: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4002: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4003: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4004: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4005: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4006: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4007: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4008: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4009: END IF;
4002: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4003: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4004: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4005: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4006: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4007: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4008: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4009: END IF;
4010: END;
4003: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4004: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4005: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4006: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4007: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4008: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4009: END IF;
4010: END;
4011: /************ End of find_parties private procedures **********/
4004: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4005: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
4006: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4007: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4008: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4009: END IF;
4010: END;
4011: /************ End of find_parties private procedures **********/
4012:
4076: 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);
4077: END IF;
4078: -- ************************************************************
4079: -- STEP 2. Setup of intermedia query strings for Acquisition query
4080: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4081: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4082: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4083: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4084: 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);
4077: END IF;
4078: -- ************************************************************
4079: -- STEP 2. Setup of intermedia query strings for Acquisition query
4080: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4081: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4082: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4083: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4084: 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);
4085: END IF;
4078: -- ************************************************************
4079: -- STEP 2. Setup of intermedia query strings for Acquisition query
4080: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4081: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4082: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4083: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4084: 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);
4085: END IF;
4086: l_denorm_max_score:=0;
4110: END IF;
4111: ELSE
4112: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
4113: END IF;
4114: 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));
4115: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4116:
4117: -- Setup Search Context ID
4118: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4120:
4121: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4122: 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);
4123: END IF;
4124: IF l_party_contains_str IS NULL THEN
4125: defpt := 1;
4126: END IF;
4127: IF l_party_site_contains_str IS NULL THEN
4128: defps := 1;
4123: END IF;
4124: IF l_party_contains_str IS NULL THEN
4125: defpt := 1;
4126: END IF;
4127: IF l_party_site_contains_str IS NULL THEN
4128: defps := 1;
4129: END IF;
4130: IF l_contact_contains_str IS NULL THEN
4131: defct := 1;
4126: END IF;
4127: IF l_party_site_contains_str IS NULL THEN
4128: defps := 1;
4129: END IF;
4130: IF l_contact_contains_str IS NULL THEN
4131: defct := 1;
4132: END IF;
4133: IF l_contact_pt_contains_str IS NULL THEN
4134: defcpt := 1;
4129: END IF;
4130: IF l_contact_contains_str IS NULL THEN
4131: defct := 1;
4132: END IF;
4133: IF l_contact_pt_contains_str IS NULL THEN
4134: defcpt := 1;
4135: END IF;
4136:
4137: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4138: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4139: 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);
4140: END IF;
4141: 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);
4142: 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);
4143: 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);
4144: 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);
4145: 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);
4146: 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);
4139: 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);
4140: END IF;
4141: 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);
4142: 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);
4143: 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);
4144: 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);
4145: 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);
4146: 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);
4147: END IF;
4140: END IF;
4141: 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);
4142: 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);
4143: 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);
4144: 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);
4145: 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);
4146: 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);
4147: END IF;
4148: FOR I in 1..5 LOOP
4141: 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);
4142: 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);
4143: 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);
4144: 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);
4145: 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);
4146: 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);
4147: END IF;
4148: FOR I in 1..5 LOOP
4149: do_eval(I);
4223: x_num_matches OUT NUMBER
4224: ) IS
4225:
4226: -- Strings to hold the generated Intermedia query strings
4227: l_party_contains_str VARCHAR2(32000);
4228: l_party_site_contains_str VARCHAR2(32000);
4229: l_contact_contains_str VARCHAR2(32000);
4230: l_contact_pt_contains_str VARCHAR2(32000);
4231: l_denorm_str VARCHAR2(32000);
4224: ) IS
4225:
4226: -- Strings to hold the generated Intermedia query strings
4227: l_party_contains_str VARCHAR2(32000);
4228: l_party_site_contains_str VARCHAR2(32000);
4229: l_contact_contains_str VARCHAR2(32000);
4230: l_contact_pt_contains_str VARCHAR2(32000);
4231: l_denorm_str VARCHAR2(32000);
4232: l_ps_denorm_str VARCHAR2(32000);
4225:
4226: -- Strings to hold the generated Intermedia query strings
4227: l_party_contains_str VARCHAR2(32000);
4228: l_party_site_contains_str VARCHAR2(32000);
4229: l_contact_contains_str VARCHAR2(32000);
4230: l_contact_pt_contains_str VARCHAR2(32000);
4231: l_denorm_str VARCHAR2(32000);
4232: l_ps_denorm_str VARCHAR2(32000);
4233: l_ct_denorm_str VARCHAR2(32000);
4226: -- Strings to hold the generated Intermedia query strings
4227: l_party_contains_str VARCHAR2(32000);
4228: l_party_site_contains_str VARCHAR2(32000);
4229: l_contact_contains_str VARCHAR2(32000);
4230: l_contact_pt_contains_str VARCHAR2(32000);
4231: l_denorm_str VARCHAR2(32000);
4232: l_ps_denorm_str VARCHAR2(32000);
4233: l_ct_denorm_str VARCHAR2(32000);
4234: l_cpt_denorm_str VARCHAR2(32000);
4363: call_type(2) := 'OR';
4364: END;
4365:
4366: /** Private procedure to acquire and score at party level ***/
4367: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
4368: l_party_id_idx NUMBER:=1;
4369: l_ctx_id NUMBER;
4370: BEGIN
4371: SAVEPOINT eval_start;
4384: l_search_ctx_id,H_PARTY_ID(I));
4385: l_party_id_idx := H_PARTY_ID.COUNT+1;
4386: ELSE
4387: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
4388: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
4389: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4390: 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);
4391: END IF;
4392: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
4392: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
4393: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4394: 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);
4395: END IF;
4396: IF (l_party_site_contains_str IS NULL AND
4397: l_contact_contains_str IS NULL AND
4398: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4399: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4400: RAISE L_RETURN_IMM_EXC;
4393: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4394: 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);
4395: END IF;
4396: IF (l_party_site_contains_str IS NULL AND
4397: l_contact_contains_str IS NULL AND
4398: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4399: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4400: RAISE L_RETURN_IMM_EXC;
4401: ELSE
4394: 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);
4395: END IF;
4396: IF (l_party_site_contains_str IS NULL AND
4397: l_contact_contains_str IS NULL AND
4398: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4399: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4400: RAISE L_RETURN_IMM_EXC;
4401: ELSE
4402: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
4398: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
4399: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
4400: RAISE L_RETURN_IMM_EXC;
4401: ELSE
4402: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
4403: END IF;
4404: ELSE
4405: IF p_index>1 THEN
4406: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4418: l_ctx_id := NULL;
4419: l_party_id_idx := H_PARTY_ID.COUNT+1;
4420: END IF;
4421: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
4422: 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);
4423: END IF;
4424: LOOP
4425: FETCH l_party_cur INTO
4426: 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;
4440: l_party_id_idx:= l_party_id_idx+1;
4441: END IF;
4442: IF (l_party_id_idx-1)>l_max_thresh THEN
4443: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
4444: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
4445: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
4446: AND l_contact_pt_contains_str IS NULL THEN
4447: H_PARTY_ID.DELETE(l_party_id_idx-1);
4448: H_PARTY_ID_LIST.DELETE(l_index);
4442: IF (l_party_id_idx-1)>l_max_thresh THEN
4443: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
4444: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
4445: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
4446: AND l_contact_pt_contains_str IS NULL THEN
4447: H_PARTY_ID.DELETE(l_party_id_idx-1);
4448: H_PARTY_ID_LIST.DELETE(l_index);
4449: H_SCORES.DELETE(l_index);
4450: EXIT;
4471: CLOSE l_party_cur;
4472: ROLLBACK to eval_start;
4473: END;
4474: PROCEDURE open_person_contact_cursor(
4475: p_contains_str VARCHAR2,
4476: p_search_ctx_id NUMBER,
4477: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
4478: BEGIN
4479: OPEN x_cursor FOR
4478: BEGIN
4479: OPEN x_cursor FOR
4480: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID, TX5, TX2
4481: FROM HZ_STAGED_CONTACTS stage
4482: WHERE contains( concat_col, p_contains_str)>0
4483: AND ORG_CONTACT_ID in (
4484: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
4485: org_contact_id
4486: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc
4502: end if;
4503: END;
4504:
4505: /** Private procedure to acquire and score at party site level ***/
4506: 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
4507: l_party_id_idx NUMBER:=1;
4508: l_ctx_id NUMBER;
4509: h_ps_id HZ_PARTY_SEARCH.IDList;
4510: h_ps_party_id HZ_PARTY_SEARCH.IDList;
4530: ELSE
4531: l_party_id_idx := H_PARTY_ID.COUNT+1;
4532: l_ctx_id := NULL;
4533: END IF;
4534: 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);
4535: LOOP
4536: FETCH l_party_site_cur INTO
4537: 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;
4538: EXIT WHEN l_party_site_cur%NOTFOUND;
4601: END IF;
4602: END;
4603:
4604: /** Private procedure to acquire and score at party site level ***/
4605: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
4606: l_party_id_idx NUMBER:=1;
4607: l_ctx_id NUMBER;
4608: h_ct_id HZ_PARTY_SEARCH.IDList;
4609: h_ct_party_id HZ_PARTY_SEARCH.IDList;
4617: FORALL I in 1..H_PARTY_ID.COUNT
4618: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
4619: l_search_ctx_id,H_PARTY_ID(I));
4620: l_party_id_idx := H_PARTY_ID.COUNT+1;
4621: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
4622: LOOP
4623: FETCH l_contact_cur INTO
4624: l_org_contact_id, l_ct_party_id , l_TX5, l_TX2;
4625: EXIT WHEN l_contact_cur%NOTFOUND;
4659: END IF;
4660: END;
4661:
4662: /** Private procedure to acquire and score at contact point level ***/
4663: 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
4664: l_party_id_idx NUMBER:=1;
4665: l_ctx_id NUMBER;
4666: h_cpt_id HZ_PARTY_SEARCH.IDList;
4667: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
4704: END IF;
4705: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4706: 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);
4707: END IF;
4708: 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);
4709: LOOP
4710: FETCH l_contact_pt_cur INTO
4711: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX5;
4712: EXIT WHEN l_contact_pt_cur%NOTFOUND;
4907: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
4908: l_threshold := round(( l_entered_max_score / 880) * 528);
4909: other_acq_criteria_exists := TRUE ;
4910: --check if acquisition criteria exists for any other entity
4911: IF l_party_contains_str IS NOT NULL THEN
4912: acq_cnt := 1;
4913: END IF;
4914: IF l_party_site_contains_str IS NOT NULL THEN
4915: acq_cnt := acq_cnt+1;
4910: --check if acquisition criteria exists for any other entity
4911: IF l_party_contains_str IS NOT NULL THEN
4912: acq_cnt := 1;
4913: END IF;
4914: IF l_party_site_contains_str IS NOT NULL THEN
4915: acq_cnt := acq_cnt+1;
4916: END IF;
4917: IF l_contact_contains_str IS NOT NULL THEN
4918: acq_cnt := acq_cnt+1;
4913: END IF;
4914: IF l_party_site_contains_str IS NOT NULL THEN
4915: acq_cnt := acq_cnt+1;
4916: END IF;
4917: IF l_contact_contains_str IS NOT NULL THEN
4918: acq_cnt := acq_cnt+1;
4919: END IF;
4920: IF l_contact_pt_contains_str IS NOT NULL THEN
4921: acq_cnt := acq_cnt+1;
4916: END IF;
4917: IF l_contact_contains_str IS NOT NULL THEN
4918: acq_cnt := acq_cnt+1;
4919: END IF;
4920: IF l_contact_pt_contains_str IS NOT NULL THEN
4921: acq_cnt := acq_cnt+1;
4922: END IF;
4923: IF acq_cnt>1 THEN
4924: other_acq_criteria_exists := TRUE;
4950: FND_MSG_PUB.ADD;
4951: RAISE FND_API.G_EXC_ERROR;
4952: END IF;
4953: END IF;
4954: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4955: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4956: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4957: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4958: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4951: RAISE FND_API.G_EXC_ERROR;
4952: END IF;
4953: END IF;
4954: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4955: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4956: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4957: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4958: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4959: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4952: END IF;
4953: END IF;
4954: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4955: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4956: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4957: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4958: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4959: 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;
4953: END IF;
4954: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4955: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4956: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4957: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4958: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4959: 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;
4954: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4955: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4956: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4957: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4958: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4959: 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;
4962: /************ End of find_persons private procedures **********/
4955: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4956: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4957: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4958: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4959: 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;
4962: /************ End of find_persons private procedures **********/
4963:
4990: g_score_until_thresh := false;
4991: END IF;
4992: -- ************************************************************
4993: -- STEP 2. Setup of intermedia query strings for Acquisition query
4994: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4995: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4996: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4997: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4998: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4991: END IF;
4992: -- ************************************************************
4993: -- STEP 2. Setup of intermedia query strings for Acquisition query
4994: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4995: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4996: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4997: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4998: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4999:
4992: -- ************************************************************
4993: -- STEP 2. Setup of intermedia query strings for Acquisition query
4994: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4995: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4996: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4997: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4998: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4999:
5000: -- Setup Search Context ID
4993: -- STEP 2. Setup of intermedia query strings for Acquisition query
4994: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4995: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4996: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4997: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4998: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4999:
5000: -- Setup Search Context ID
5001: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5000: -- Setup Search Context ID
5001: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5002: x_search_ctx_id := l_search_ctx_id;
5003:
5004: IF l_party_contains_str IS NULL THEN
5005: defpt := 1;
5006: END IF;
5007: IF l_party_site_contains_str IS NULL THEN
5008: defps := 1;
5003:
5004: IF l_party_contains_str IS NULL THEN
5005: defpt := 1;
5006: END IF;
5007: IF l_party_site_contains_str IS NULL THEN
5008: defps := 1;
5009: END IF;
5010: IF l_contact_contains_str IS NULL THEN
5011: defct := 1;
5006: END IF;
5007: IF l_party_site_contains_str IS NULL THEN
5008: defps := 1;
5009: END IF;
5010: IF l_contact_contains_str IS NULL THEN
5011: defct := 1;
5012: END IF;
5013: IF l_contact_pt_contains_str IS NULL THEN
5014: defcpt := 1;
5009: END IF;
5010: IF l_contact_contains_str IS NULL THEN
5011: defct := 1;
5012: END IF;
5013: IF l_contact_pt_contains_str IS NULL THEN
5014: defcpt := 1;
5015: END IF;
5016:
5017: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5015: END IF;
5016:
5017: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5018: 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);
5019: 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);
5020: 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);
5021: 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);
5022: 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);
5023: 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);
5016:
5017: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5018: 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);
5019: 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);
5020: 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);
5021: 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);
5022: 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);
5023: 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: END IF;
5017: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5018: 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);
5019: 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);
5020: 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);
5021: 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);
5022: 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);
5023: 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: END IF;
5025: FOR I in 1..5 LOOP
5018: 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);
5019: 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);
5020: 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);
5021: 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);
5022: 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);
5023: 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: END IF;
5025: FOR I in 1..5 LOOP
5026: do_eval(I);
5024: END IF;
5025: FOR I in 1..5 LOOP
5026: do_eval(I);
5027: END LOOP;
5028: IF l_contact_contains_str IS NOT NULL THEN
5029: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
5030: END IF;
5031: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5032: 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);
5025: FOR I in 1..5 LOOP
5026: do_eval(I);
5027: END LOOP;
5028: IF l_contact_contains_str IS NOT NULL THEN
5029: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
5030: END IF;
5031: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5032: 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: END IF;
5939: x_num_matches OUT NUMBER
5940: ) IS
5941:
5942: -- Strings to hold the generated Intermedia query strings
5943: l_party_contains_str VARCHAR2(32000);
5944: l_match_idx NUMBER;
5945: l_party_site_contains_str VARCHAR2(32000);
5946: l_contact_contains_str VARCHAR2(32000);
5947: l_contact_pt_contains_str VARCHAR2(32000);
5941:
5942: -- Strings to hold the generated Intermedia query strings
5943: l_party_contains_str VARCHAR2(32000);
5944: l_match_idx NUMBER;
5945: l_party_site_contains_str VARCHAR2(32000);
5946: l_contact_contains_str VARCHAR2(32000);
5947: l_contact_pt_contains_str VARCHAR2(32000);
5948: l_tmp VARCHAR2(32000);
5949:
5942: -- Strings to hold the generated Intermedia query strings
5943: l_party_contains_str VARCHAR2(32000);
5944: l_match_idx NUMBER;
5945: l_party_site_contains_str VARCHAR2(32000);
5946: l_contact_contains_str VARCHAR2(32000);
5947: l_contact_pt_contains_str VARCHAR2(32000);
5948: l_tmp VARCHAR2(32000);
5949:
5950: -- Other local variables
5943: l_party_contains_str VARCHAR2(32000);
5944: l_match_idx NUMBER;
5945: l_party_site_contains_str VARCHAR2(32000);
5946: l_contact_contains_str VARCHAR2(32000);
5947: l_contact_pt_contains_str VARCHAR2(32000);
5948: l_tmp VARCHAR2(32000);
5949:
5950: -- Other local variables
5951: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6026: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6027:
6028: -- ************************************************************
6029: -- STEP 2. Setup of intermedia query strings for Acquisition query
6030: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6031: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6032: 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);
6033:
6034: -- Setup Search Context ID
6027:
6028: -- ************************************************************
6029: -- STEP 2. Setup of intermedia query strings for Acquisition query
6030: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6031: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6032: 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);
6033:
6034: -- Setup Search Context ID
6035: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
6036: x_search_ctx_id := l_search_ctx_id;
6037:
6038: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6039: 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);
6040: 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);
6041: 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);
6042: 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);
6043: END IF;
6044: IF l_party_site_contains_str IS NULL THEN
6037:
6038: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6039: 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);
6040: 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);
6041: 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);
6042: 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);
6043: END IF;
6044: IF l_party_site_contains_str IS NULL THEN
6045: defps := 1;
6040: 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);
6041: 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);
6042: 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);
6043: END IF;
6044: IF l_party_site_contains_str IS NULL THEN
6045: defps := 1;
6046: END IF;
6047: IF l_contact_pt_contains_str IS NULL THEN
6048: defcpt := 1;
6043: END IF;
6044: IF l_party_site_contains_str IS NULL THEN
6045: defps := 1;
6046: END IF;
6047: IF l_contact_pt_contains_str IS NULL THEN
6048: defcpt := 1;
6049: END IF;
6050:
6051: IF l_party_site_contains_str IS NOT NULL THEN
6047: IF l_contact_pt_contains_str IS NULL THEN
6048: defcpt := 1;
6049: END IF;
6050:
6051: IF l_party_site_contains_str IS NOT NULL THEN
6052: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
6053: LOOP
6054: FETCH l_party_site_cur INTO
6055: 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;
6048: defcpt := 1;
6049: END IF;
6050:
6051: IF l_party_site_contains_str IS NOT NULL THEN
6052: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
6053: LOOP
6054: FETCH l_party_site_cur INTO
6055: 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: EXIT WHEN l_party_site_cur%NOTFOUND;
6071: END LOOP;
6072: CLOSE l_party_site_cur;
6073: END IF;
6074:
6075: IF l_contact_pt_contains_str IS NOT NULL THEN
6076: SAVEPOINT eval_start;
6077: IF l_match_str = ' AND ' OR (
6078: ((l_cpt_max_score/l_entered_max_score)<(528/880))
6079: ) THEN
6079: ) THEN
6080: FORALL I in 1..l_match_ps_list.COUNT
6081: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
6082: values (l_search_ctx_id,l_match_ps_list(I));
6083: 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');
6084: ELSE
6085: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6086: END IF;
6087: LOOP
6081: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
6082: values (l_search_ctx_id,l_match_ps_list(I));
6083: 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');
6084: ELSE
6085: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6086: END IF;
6087: LOOP
6088: FETCH l_contact_pt_cur INTO
6089: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX5;
6181: x_num_matches OUT NUMBER
6182: ) IS
6183:
6184: -- Strings to hold the generated Intermedia query strings
6185: l_party_contains_str VARCHAR2(32000);
6186: l_party_site_contains_str VARCHAR2(32000);
6187: l_contact_contains_str VARCHAR2(32000);
6188: l_contact_pt_contains_str VARCHAR2(32000);
6189: l_tmp VARCHAR2(32000);
6182: ) IS
6183:
6184: -- Strings to hold the generated Intermedia query strings
6185: l_party_contains_str VARCHAR2(32000);
6186: l_party_site_contains_str VARCHAR2(32000);
6187: l_contact_contains_str VARCHAR2(32000);
6188: l_contact_pt_contains_str VARCHAR2(32000);
6189: l_tmp VARCHAR2(32000);
6190:
6183:
6184: -- Strings to hold the generated Intermedia query strings
6185: l_party_contains_str VARCHAR2(32000);
6186: l_party_site_contains_str VARCHAR2(32000);
6187: l_contact_contains_str VARCHAR2(32000);
6188: l_contact_pt_contains_str VARCHAR2(32000);
6189: l_tmp VARCHAR2(32000);
6190:
6191: -- Other local variables
6184: -- Strings to hold the generated Intermedia query strings
6185: l_party_contains_str VARCHAR2(32000);
6186: l_party_site_contains_str VARCHAR2(32000);
6187: l_contact_contains_str VARCHAR2(32000);
6188: l_contact_pt_contains_str VARCHAR2(32000);
6189: l_tmp VARCHAR2(32000);
6190:
6191: -- Other local variables
6192: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6258: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6259:
6260: -- ************************************************************
6261: -- STEP 2. Setup of intermedia query strings for Acquisition query
6262: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6263: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6264: 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);
6265:
6266: -- Setup Search Context ID
6259:
6260: -- ************************************************************
6261: -- STEP 2. Setup of intermedia query strings for Acquisition query
6262: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6263: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6264: 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);
6265:
6266: -- Setup Search Context ID
6267: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
6268: x_search_ctx_id := l_search_ctx_id;
6269:
6270: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6271: 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);
6272: 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);
6273: 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);
6274: 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);
6275: END IF;
6276: IF l_contact_contains_str IS NULL THEN
6269:
6270: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6271: 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);
6272: 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);
6273: 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);
6274: 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);
6275: END IF;
6276: IF l_contact_contains_str IS NULL THEN
6277: defct := 1;
6272: 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);
6273: 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);
6274: 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);
6275: END IF;
6276: IF l_contact_contains_str IS NULL THEN
6277: defct := 1;
6278: END IF;
6279: IF l_contact_pt_contains_str IS NULL THEN
6280: defcpt := 1;
6275: END IF;
6276: IF l_contact_contains_str IS NULL THEN
6277: defct := 1;
6278: END IF;
6279: IF l_contact_pt_contains_str IS NULL THEN
6280: defcpt := 1;
6281: END IF;
6282:
6283: IF l_contact_contains_str IS NOT NULL THEN
6279: IF l_contact_pt_contains_str IS NULL THEN
6280: defcpt := 1;
6281: END IF;
6282:
6283: IF l_contact_contains_str IS NOT NULL THEN
6284: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
6285: LOOP
6286: FETCH l_contact_cur INTO
6287: l_org_contact_id, l_ct_party_id , l_TX5, l_TX2;
6280: defcpt := 1;
6281: END IF;
6282:
6283: IF l_contact_contains_str IS NOT NULL THEN
6284: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
6285: LOOP
6286: FETCH l_contact_cur INTO
6287: l_org_contact_id, l_ct_party_id , l_TX5, l_TX2;
6288: EXIT WHEN l_contact_cur%NOTFOUND;
6301: END LOOP;
6302: CLOSE l_contact_cur;
6303: END IF;
6304:
6305: IF l_contact_pt_contains_str IS NOT NULL THEN
6306: SAVEPOINT eval_start;
6307: IF l_match_str = ' AND ' OR (
6308: ((l_cpt_max_score/l_entered_max_score)<(528/880))
6309: ) THEN
6309: ) THEN
6310: FORALL I in 1..l_match_contact_list.COUNT
6311: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
6312: values (l_search_ctx_id,l_match_contact_list(I));
6313: 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');
6314: ELSE
6315: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6316: END IF;
6317: LOOP
6311: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
6312: values (l_search_ctx_id,l_match_contact_list(I));
6313: 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');
6314: ELSE
6315: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6316: END IF;
6317: LOOP
6318: FETCH l_contact_pt_cur INTO
6319: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX5;
6409: ) IS
6410:
6411:
6412: -- Strings to hold the generated Intermedia query strings
6413: l_contact_pt_contains_str VARCHAR2(32000);
6414: l_tmp VARCHAR2(32000);
6415:
6416: -- Other local variables
6417: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6469: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6470:
6471: -- ************************************************************
6472: -- STEP 2. Setup of intermedia query strings for Acquisition query
6473: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6474: 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);
6475:
6476: -- Setup Search Context ID
6477: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
6477: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
6478: x_search_ctx_id := l_search_ctx_id;
6479: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6480: 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);
6481: 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);
6482: 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);
6483: END IF;
6484:
6485: IF l_contact_pt_contains_str IS NOT NULL THEN
6481: 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);
6482: 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);
6483: END IF;
6484:
6485: IF l_contact_pt_contains_str IS NOT NULL THEN
6486: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6487: cnt := 1;
6488: LOOP
6489: FETCH l_contact_pt_cur INTO
6482: 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);
6483: END IF;
6484:
6485: IF l_contact_pt_contains_str IS NOT NULL THEN
6486: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
6487: cnt := 1;
6488: LOOP
6489: FETCH l_contact_pt_cur INTO
6490: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX5;
6553: x_search_ctx_id IN OUT NUMBER
6554: ) IS
6555:
6556: -- Strings to hold the generated Intermedia query strings
6557: l_party_contains_str VARCHAR2(32000);
6558: l_party_site_contains_str VARCHAR2(32000);
6559: l_contact_contains_str VARCHAR2(32000);
6560: l_contact_pt_contains_str VARCHAR2(32000);
6561: l_tmp VARCHAR2(32000);
6554: ) IS
6555:
6556: -- Strings to hold the generated Intermedia query strings
6557: l_party_contains_str VARCHAR2(32000);
6558: l_party_site_contains_str VARCHAR2(32000);
6559: l_contact_contains_str VARCHAR2(32000);
6560: l_contact_pt_contains_str VARCHAR2(32000);
6561: l_tmp VARCHAR2(32000);
6562:
6555:
6556: -- Strings to hold the generated Intermedia query strings
6557: l_party_contains_str VARCHAR2(32000);
6558: l_party_site_contains_str VARCHAR2(32000);
6559: l_contact_contains_str VARCHAR2(32000);
6560: l_contact_pt_contains_str VARCHAR2(32000);
6561: l_tmp VARCHAR2(32000);
6562:
6563: -- Other local variables
6556: -- Strings to hold the generated Intermedia query strings
6557: l_party_contains_str VARCHAR2(32000);
6558: l_party_site_contains_str VARCHAR2(32000);
6559: l_contact_contains_str VARCHAR2(32000);
6560: l_contact_pt_contains_str VARCHAR2(32000);
6561: l_tmp VARCHAR2(32000);
6562:
6563: -- Other local variables
6564: l_match_str VARCHAR2(30); -- Match type (AND or OR)
6657: g_score_until_thresh := false;
6658: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6659: -- ************************************************************
6660: -- STEP 2. Setup of intermedia query strings for Acquisition query
6661: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6662: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6663: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6664: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6665:
6658: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
6659: -- ************************************************************
6660: -- STEP 2. Setup of intermedia query strings for Acquisition query
6661: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6662: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6663: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6664: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6665:
6666: -- Setup Search Context ID
6659: -- ************************************************************
6660: -- STEP 2. Setup of intermedia query strings for Acquisition query
6661: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
6662: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
6663: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
6664: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
6665:
6666: -- Setup Search Context ID
6667: IF x_search_ctx_id IS NULL THEN
6679: 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,l_TX42,l_TX44,l_TX45,l_TX46,l_TX47,l_TX48,l_TX156,l_TX157,1);
6680: END LOOP;
6681: CLOSE l_party_cur;
6682:
6683: IF l_party_site_contains_str IS NOT NULL THEN
6684: l_max_score := 0;
6685: l_max_id := 0;
6686: l_max_idx := 0;
6687: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6684: l_max_score := 0;
6685: l_max_id := 0;
6686: l_max_idx := 0;
6687: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6688: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
6689: ELSE
6690: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
6691: END IF;
6692: LOOP
6686: l_max_idx := 0;
6687: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
6688: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
6689: ELSE
6690: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
6691: END IF;
6692: LOOP
6693: FETCH l_party_site_cur INTO
6694: 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;
6713: 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);
6714: END IF;
6715: END IF;
6716:
6717: IF l_contact_contains_str IS NOT NULL THEN
6718: l_max_score := 0;
6719: l_max_id := 0;
6720: l_max_idx := 0;
6721: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
6717: IF l_contact_contains_str IS NOT NULL THEN
6718: l_max_score := 0;
6719: l_max_id := 0;
6720: l_max_idx := 0;
6721: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
6722: LOOP
6723: FETCH l_contact_cur INTO
6724: l_org_contact_id, l_ct_party_id , l_TX5, l_TX2;
6725: EXIT WHEN l_contact_cur%NOTFOUND;
6737: 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);
6738: END IF;
6739: END IF;
6740:
6741: IF l_contact_pt_contains_str IS NOT NULL THEN
6742: l_max_score := 0;
6743: l_max_id := 0;
6744: l_max_idx := 0;
6745: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6742: l_max_score := 0;
6743: l_max_id := 0;
6744: l_max_idx := 0;
6745: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6746: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
6747: ELSE
6748: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
6749: END IF;
6750: LOOP
6744: l_max_idx := 0;
6745: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
6746: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
6747: ELSE
6748: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
6749: END IF;
6750: LOOP
6751: FETCH l_contact_pt_cur INTO
6752: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX5;