eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HZ_MATCH_RULE_240 dependencies on HZ_STAGED_CONTACT_POINTS

Line 1737: 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 , TX2, TX6

1733: END IF;
1734: IF p_restrict_entity = 'CONTACTS'
1735: THEN
1736: OPEN x_cursor FOR
1737: 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 , TX2, TX6
1738: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1739: WHERE contains( concat_col, p_contains_str)>0
1740: AND d.search_context_id = p_search_ctx_id
1741: AND( (l_search_merged ='Y' )

Line 1738: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1734: IF p_restrict_entity = 'CONTACTS'
1735: THEN
1736: OPEN x_cursor FOR
1737: 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 , TX2, TX6
1738: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1739: WHERE contains( concat_col, p_contains_str)>0
1740: AND d.search_context_id = p_search_ctx_id
1741: AND( (l_search_merged ='Y' )
1742: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1749: 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 , TX2, TX6

1745: END IF;
1746: IF p_restrict_entity = 'PARTY_SITES'
1747: THEN
1748: OPEN x_cursor FOR
1749: 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 , TX2, TX6
1750: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1751: WHERE contains( concat_col, p_contains_str)>0
1752: AND d.search_context_id = p_search_ctx_id
1753: AND( (l_search_merged ='Y' )

Line 1750: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1746: IF p_restrict_entity = 'PARTY_SITES'
1747: THEN
1748: OPEN x_cursor FOR
1749: 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 , TX2, TX6
1750: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1751: WHERE contains( concat_col, p_contains_str)>0
1752: AND d.search_context_id = p_search_ctx_id
1753: AND( (l_search_merged ='Y' )
1754: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1766: SELECT /+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) / CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6

1762: hz_utility_v2pub.debug(p_message=>'Single Party Scenario',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1763: END IF;
1764: IF p_search_rel_cpts = 'N' THEN
1765: OPEN x_cursor FOR
1766: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6
1767: FROM HZ_STAGED_CONTACT_POINTS stage
1768: WHERE contains( concat_col, p_contains_str)>0
1769: AND( (l_search_merged ='Y' )
1770: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1767: FROM HZ_STAGED_CONTACT_POINTS stage

1763: END IF;
1764: IF p_search_rel_cpts = 'N' THEN
1765: OPEN x_cursor FOR
1766: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6
1767: FROM HZ_STAGED_CONTACT_POINTS stage
1768: WHERE contains( concat_col, p_contains_str)>0
1769: AND( (l_search_merged ='Y' )
1770: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1771: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1775: SELECT /+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) / CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6

1771: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1772: AND stage.party_id = p_party_id;
1773: ELSE
1774: OPEN x_cursor FOR
1775: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6
1776: FROM HZ_STAGED_CONTACT_POINTS stage
1777: WHERE contains( concat_col, p_contains_str)>0
1778: AND( (l_search_merged ='Y' )
1779: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1776: FROM HZ_STAGED_CONTACT_POINTS stage

1772: AND stage.party_id = p_party_id;
1773: ELSE
1774: OPEN x_cursor FOR
1775: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6
1776: FROM HZ_STAGED_CONTACT_POINTS stage
1777: WHERE contains( concat_col, p_contains_str)>0
1778: AND( (l_search_merged ='Y' )
1779: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1780: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1783: 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 , TX2, TX6

1779: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1780: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1781: AND stage.party_id = p_party_id
1782: UNION
1783: 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 , TX2, TX6
1784: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1785: WHERE contains( concat_col, p_contains_str)>0
1786: AND r.object_id = p_party_id
1787: AND( (l_search_merged ='Y' )

Line 1784: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc

1780: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1781: AND stage.party_id = p_party_id
1782: UNION
1783: 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 , TX2, TX6
1784: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1785: WHERE contains( concat_col, p_contains_str)>0
1786: AND r.object_id = p_party_id
1787: AND( (l_search_merged ='Y' )
1788: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1806: FROM HZ_STAGED_CONTACT_POINTS stage

1802: hz_utility_v2pub.debug(p_message=>'Either Search context id is null',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1803: END IF;
1804: OPEN x_cursor FOR
1805: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6
1806: FROM HZ_STAGED_CONTACT_POINTS stage
1807: WHERE contains( concat_col, p_contains_str)>0
1808: AND EXISTS (
1809: SELECT 1 FROM HZ_STAGED_PARTIES p
1810: WHERE p.PARTY_ID = stage.PARTY_ID

Line 1826: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1822: END IF;
1823: IF p_person_api = 'Y' THEN
1824: OPEN x_cursor FOR
1825: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6
1826: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1827: WHERE contains( concat_col, p_contains_str)>0
1828: AND d.search_context_id = p_search_ctx_id
1829: AND d.party_id = stage.party_id
1830: AND( (l_search_merged ='Y' )

Line 1835: 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 , TX2, TX6

1831: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1832: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1833: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1834: UNION
1835: 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 , TX2, TX6
1836: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1837: WHERE contains( concat_col, p_contains_str)>0
1838: AND d.search_context_id = p_search_ctx_id
1839: AND d.party_id = r.subject_id

Line 1836: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage

1832: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1833: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1834: UNION
1835: 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 , TX2, TX6
1836: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1837: WHERE contains( concat_col, p_contains_str)>0
1838: AND d.search_context_id = p_search_ctx_id
1839: AND d.party_id = r.subject_id
1840: AND r.relationship_id = oc.party_relationship_id

Line 1849: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1845: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
1846: ELSE
1847: OPEN x_cursor FOR
1848: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6
1849: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1850: WHERE contains( concat_col, p_contains_str)>0
1851: AND d.search_context_id = p_search_ctx_id
1852: AND d.party_id = stage.party_id
1853: AND( (l_search_merged ='Y' )

Line 1869: l_hint := '/+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) /';

1865: IF (l_check_dt > 0 ) THEN
1866: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1867: hz_utility_v2pub.debug(p_message=>'Restrict Sql has the selective_cpt hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1868: END IF;
1869: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) */';
1870: ELSIF (l_check > 0 ) THEN
1871: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1872: hz_utility_v2pub.debug(p_message=>'Restrict Sql has the selective hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1873: END IF;

Line 1874: l_hint := '/+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) /';

1870: ELSIF (l_check > 0 ) THEN
1871: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1872: hz_utility_v2pub.debug(p_message=>'Restrict Sql has the selective hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1873: END IF;
1874: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
1875: END IF;
1876: IF l_search_merged = 'Y' THEN
1877: l_status_sql := ' ' ;
1878: ELSIF l_search_merged = 'I' THEN

Line 1893: 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 , TX2, TX6'||

1889: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
1890: ELSE
1891: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1892: END IF;
1893: 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 , TX2, TX6'||
1894: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1895: ' WHERE contains( concat_col, :cont)>0 '||
1896: ' AND (stage.org_contact_id is null '||
1897: ' AND( ('''||l_search_merged||''' =''Y'' ) '||

Line 1894: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

1890: ELSE
1891: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1892: END IF;
1893: 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 , TX2, TX6'||
1894: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1895: ' WHERE contains( concat_col, :cont)>0 '||
1896: ' AND (stage.org_contact_id is null '||
1897: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1898: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 1903: ' SELECT /+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) / CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6'||

1899: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1900: ' AND ('||p_restrict_sql||'))' ||
1901: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1902: ' UNION ' ||
1903: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6'||
1904: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1905: ' WHERE contains( concat_col, :cont)>0 '||
1906: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1907: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 1904: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

1900: ' AND ('||p_restrict_sql||'))' ||
1901: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1902: ' UNION ' ||
1903: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6'||
1904: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1905: ' WHERE contains( concat_col, :cont)>0 '||
1906: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1907: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1908: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 1919: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

1915: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1916: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1917: ELSE
1918: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6'||
1919: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1920: ' WHERE contains( concat_col, :cont)>0'||
1921: ' AND EXISTS ('||
1922: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1923: ' WHERE p.party_id = stage.party_id ' ||

Line 1936: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

1932: ,p_dup_party_id, p_dup_party_id;
1933: END IF;
1934: ELSE
1935: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6'||
1936: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1937: ' WHERE contains( concat_col, :cont)>0'||
1938: ' AND EXISTS ('||
1939: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1940: ' WHERE p.party_id = stage.party_id ' ||