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_41 dependencies on HZ_STAGED_CONTACT_POINTS

Line 1849: 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, TX6, TX5

1845: END IF;
1846: IF p_restrict_entity = 'CONTACTS'
1847: THEN
1848: OPEN x_cursor FOR
1849: 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, TX6, TX5
1850: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1851: WHERE contains( concat_col, p_contains_str)>0
1852: AND d.search_context_id = p_search_ctx_id
1853: AND( (l_search_merged ='Y' )

Line 1850: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1846: IF p_restrict_entity = 'CONTACTS'
1847: THEN
1848: OPEN x_cursor FOR
1849: 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, TX6, TX5
1850: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1851: WHERE contains( concat_col, p_contains_str)>0
1852: AND d.search_context_id = p_search_ctx_id
1853: AND( (l_search_merged ='Y' )
1854: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1861: 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, TX6, TX5

1857: END IF;
1858: IF p_restrict_entity = 'PARTY_SITES'
1859: THEN
1860: OPEN x_cursor FOR
1861: 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, TX6, TX5
1862: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1863: WHERE contains( concat_col, p_contains_str)>0
1864: AND d.search_context_id = p_search_ctx_id
1865: AND( (l_search_merged ='Y' )

Line 1862: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1858: IF p_restrict_entity = 'PARTY_SITES'
1859: THEN
1860: OPEN x_cursor FOR
1861: 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, TX6, TX5
1862: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1863: WHERE contains( concat_col, p_contains_str)>0
1864: AND d.search_context_id = p_search_ctx_id
1865: AND( (l_search_merged ='Y' )
1866: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1878: SELECT /+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) / CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5

1874: 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);
1875: END IF;
1876: IF p_search_rel_cpts = 'N' THEN
1877: OPEN x_cursor FOR
1878: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1879: FROM HZ_STAGED_CONTACT_POINTS stage
1880: WHERE contains( concat_col, p_contains_str)>0
1881: AND( (l_search_merged ='Y' )
1882: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1879: FROM HZ_STAGED_CONTACT_POINTS stage

1875: END IF;
1876: IF p_search_rel_cpts = 'N' THEN
1877: OPEN x_cursor FOR
1878: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1879: FROM HZ_STAGED_CONTACT_POINTS stage
1880: WHERE contains( concat_col, p_contains_str)>0
1881: AND( (l_search_merged ='Y' )
1882: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1883: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1887: SELECT /+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) / CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5

1883: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1884: AND stage.party_id = p_party_id;
1885: ELSE
1886: OPEN x_cursor FOR
1887: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1888: FROM HZ_STAGED_CONTACT_POINTS stage
1889: WHERE contains( concat_col, p_contains_str)>0
1890: AND( (l_search_merged ='Y' )
1891: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1888: FROM HZ_STAGED_CONTACT_POINTS stage

1884: AND stage.party_id = p_party_id;
1885: ELSE
1886: OPEN x_cursor FOR
1887: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1888: FROM HZ_STAGED_CONTACT_POINTS stage
1889: WHERE contains( concat_col, p_contains_str)>0
1890: AND( (l_search_merged ='Y' )
1891: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1892: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1895: 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, TX6, TX5

1891: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1892: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1893: AND stage.party_id = p_party_id
1894: UNION
1895: 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, TX6, TX5
1896: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1897: WHERE contains( concat_col, p_contains_str)>0
1898: AND r.object_id = p_party_id
1899: AND( (l_search_merged ='Y' )

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

1892: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1893: AND stage.party_id = p_party_id
1894: UNION
1895: 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, TX6, TX5
1896: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1897: WHERE contains( concat_col, p_contains_str)>0
1898: AND r.object_id = p_party_id
1899: AND( (l_search_merged ='Y' )
1900: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1918: FROM HZ_STAGED_CONTACT_POINTS stage

1914: 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);
1915: END IF;
1916: OPEN x_cursor FOR
1917: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1918: FROM HZ_STAGED_CONTACT_POINTS stage
1919: WHERE contains( concat_col, p_contains_str)>0
1920: AND EXISTS (
1921: SELECT 1 FROM HZ_STAGED_PARTIES p
1922: WHERE p.PARTY_ID = stage.PARTY_ID

Line 1938: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1934: END IF;
1935: IF p_person_api = 'Y' THEN
1936: OPEN x_cursor FOR
1937: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1938: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1939: WHERE contains( concat_col, p_contains_str)>0
1940: AND d.search_context_id = p_search_ctx_id
1941: AND d.party_id = stage.party_id
1942: AND( (l_search_merged ='Y' )

Line 1947: 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, TX6, TX5

1943: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1944: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1945: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1946: UNION
1947: 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, TX6, TX5
1948: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1949: WHERE contains( concat_col, p_contains_str)>0
1950: AND d.search_context_id = p_search_ctx_id
1951: AND d.party_id = r.subject_id

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

1944: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1945: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1946: UNION
1947: 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, TX6, TX5
1948: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1949: WHERE contains( concat_col, p_contains_str)>0
1950: AND d.search_context_id = p_search_ctx_id
1951: AND d.party_id = r.subject_id
1952: AND r.relationship_id = oc.party_relationship_id

Line 1961: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1957: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
1958: ELSE
1959: OPEN x_cursor FOR
1960: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1961: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1962: WHERE contains( concat_col, p_contains_str)>0
1963: AND d.search_context_id = p_search_ctx_id
1964: AND d.party_id = stage.party_id
1965: AND( (l_search_merged ='Y' )

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

1977: IF (l_check_dt > 0 ) THEN
1978: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1979: 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);
1980: END IF;
1981: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) */';
1982: ELSIF (l_check > 0 ) THEN
1983: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1984: 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);
1985: END IF;

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

1982: ELSIF (l_check > 0 ) THEN
1983: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1984: 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);
1985: END IF;
1986: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
1987: END IF;
1988: IF l_search_merged = 'Y' THEN
1989: l_status_sql := ' ' ;
1990: ELSIF l_search_merged = 'I' THEN

Line 2005: 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, TX6, TX5'||

2001: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
2002: ELSE
2003: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2004: END IF;
2005: 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, TX6, TX5'||
2006: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2007: ' WHERE contains( concat_col, :cont)>0 '||
2008: ' AND (stage.org_contact_id is null '||
2009: ' AND( ('''||l_search_merged||''' =''Y'' ) '||

Line 2006: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2002: ELSE
2003: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2004: END IF;
2005: 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, TX6, TX5'||
2006: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2007: ' WHERE contains( concat_col, :cont)>0 '||
2008: ' AND (stage.org_contact_id is null '||
2009: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2010: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

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

2011: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2012: ' AND ('||p_restrict_sql||'))' ||
2013: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2014: ' UNION ' ||
2015: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2016: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2017: ' WHERE contains( concat_col, :cont)>0 '||
2018: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2019: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2016: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2012: ' AND ('||p_restrict_sql||'))' ||
2013: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2014: ' UNION ' ||
2015: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2016: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2017: ' WHERE contains( concat_col, :cont)>0 '||
2018: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2019: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2020: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 2031: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2027: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2028: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2029: ELSE
2030: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2031: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2032: ' WHERE contains( concat_col, :cont)>0'||
2033: ' AND EXISTS ('||
2034: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2035: ' WHERE p.party_id = stage.party_id ' ||

Line 2048: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2044: ,p_dup_party_id, p_dup_party_id;
2045: END IF;
2046: ELSE
2047: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2048: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2049: ' WHERE contains( concat_col, :cont)>0'||
2050: ' AND EXISTS ('||
2051: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2052: ' WHERE p.party_id = stage.party_id ' ||