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

Line 1836: 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

1832: END IF;
1833: IF p_restrict_entity = 'CONTACTS'
1834: THEN
1835: OPEN x_cursor FOR
1836: 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
1837: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1838: WHERE contains( concat_col, p_contains_str)>0
1839: AND d.search_context_id = p_search_ctx_id
1840: AND( (l_search_merged ='Y' )

Line 1837: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1833: IF p_restrict_entity = 'CONTACTS'
1834: THEN
1835: OPEN x_cursor FOR
1836: 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
1837: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1838: WHERE contains( concat_col, p_contains_str)>0
1839: AND d.search_context_id = p_search_ctx_id
1840: AND( (l_search_merged ='Y' )
1841: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1848: 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

1844: END IF;
1845: IF p_restrict_entity = 'PARTY_SITES'
1846: THEN
1847: OPEN x_cursor FOR
1848: 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
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( (l_search_merged ='Y' )

Line 1849: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1845: IF p_restrict_entity = 'PARTY_SITES'
1846: THEN
1847: OPEN x_cursor FOR
1848: 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
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( (l_search_merged ='Y' )
1853: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1865: 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

1861: 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);
1862: END IF;
1863: IF p_search_rel_cpts = 'N' THEN
1864: OPEN x_cursor FOR
1865: 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
1866: FROM HZ_STAGED_CONTACT_POINTS stage
1867: WHERE contains( concat_col, p_contains_str)>0
1868: AND( (l_search_merged ='Y' )
1869: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1866: FROM HZ_STAGED_CONTACT_POINTS stage

1862: END IF;
1863: IF p_search_rel_cpts = 'N' THEN
1864: OPEN x_cursor FOR
1865: 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
1866: FROM HZ_STAGED_CONTACT_POINTS stage
1867: WHERE contains( concat_col, p_contains_str)>0
1868: AND( (l_search_merged ='Y' )
1869: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1870: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1874: 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

1870: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1871: AND stage.party_id = p_party_id;
1872: ELSE
1873: OPEN x_cursor FOR
1874: 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
1875: FROM HZ_STAGED_CONTACT_POINTS stage
1876: WHERE contains( concat_col, p_contains_str)>0
1877: AND( (l_search_merged ='Y' )
1878: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1875: FROM HZ_STAGED_CONTACT_POINTS stage

1871: AND stage.party_id = p_party_id;
1872: ELSE
1873: OPEN x_cursor FOR
1874: 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
1875: FROM HZ_STAGED_CONTACT_POINTS stage
1876: WHERE contains( concat_col, p_contains_str)>0
1877: AND( (l_search_merged ='Y' )
1878: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1879: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1882: 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

1878: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1879: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1880: AND stage.party_id = p_party_id
1881: UNION
1882: 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
1883: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1884: WHERE contains( concat_col, p_contains_str)>0
1885: AND r.object_id = p_party_id
1886: AND( (l_search_merged ='Y' )

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

1879: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1880: AND stage.party_id = p_party_id
1881: UNION
1882: 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
1883: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1884: WHERE contains( concat_col, p_contains_str)>0
1885: AND r.object_id = p_party_id
1886: AND( (l_search_merged ='Y' )
1887: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1905: FROM HZ_STAGED_CONTACT_POINTS stage

1901: 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);
1902: END IF;
1903: OPEN x_cursor FOR
1904: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1905: FROM HZ_STAGED_CONTACT_POINTS stage
1906: WHERE contains( concat_col, p_contains_str)>0
1907: AND EXISTS (
1908: SELECT 1 FROM HZ_STAGED_PARTIES p
1909: WHERE p.PARTY_ID = stage.PARTY_ID

Line 1925: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1921: END IF;
1922: IF p_person_api = 'Y' THEN
1923: OPEN x_cursor FOR
1924: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1925: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1926: WHERE contains( concat_col, p_contains_str)>0
1927: AND d.search_context_id = p_search_ctx_id
1928: AND d.party_id = stage.party_id
1929: AND( (l_search_merged ='Y' )

Line 1934: 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

1930: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1931: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1932: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1933: UNION
1934: 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
1935: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1936: WHERE contains( concat_col, p_contains_str)>0
1937: AND d.search_context_id = p_search_ctx_id
1938: AND d.party_id = r.subject_id

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

1931: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1932: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1933: UNION
1934: 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
1935: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1936: WHERE contains( concat_col, p_contains_str)>0
1937: AND d.search_context_id = p_search_ctx_id
1938: AND d.party_id = r.subject_id
1939: AND r.relationship_id = oc.party_relationship_id

Line 1948: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1944: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
1945: ELSE
1946: OPEN x_cursor FOR
1947: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1948: FROM HZ_DQM_PARTIES_GT d, 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 = stage.party_id
1952: AND( (l_search_merged ='Y' )

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

1964: IF (l_check_dt > 0 ) THEN
1965: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1966: 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);
1967: END IF;
1968: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) */';
1969: ELSIF (l_check > 0 ) THEN
1970: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1971: 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);
1972: END IF;

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

1969: ELSIF (l_check > 0 ) THEN
1970: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1971: 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);
1972: END IF;
1973: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
1974: END IF;
1975: IF l_search_merged = 'Y' THEN
1976: l_status_sql := ' ' ;
1977: ELSIF l_search_merged = 'I' THEN

Line 1992: 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'||

1988: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
1989: ELSE
1990: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1991: END IF;
1992: 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'||
1993: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1994: ' WHERE contains( concat_col, :cont)>0 '||
1995: ' AND (stage.org_contact_id is null '||
1996: ' AND( ('''||l_search_merged||''' =''Y'' ) '||

Line 1993: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

1989: ELSE
1990: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1991: END IF;
1992: 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'||
1993: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1994: ' WHERE contains( concat_col, :cont)>0 '||
1995: ' AND (stage.org_contact_id is null '||
1996: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1997: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2002: ' 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'||

1998: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1999: ' AND ('||p_restrict_sql||'))' ||
2000: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2001: ' UNION ' ||
2002: ' 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'||
2003: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2004: ' WHERE contains( concat_col, :cont)>0 '||
2005: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2006: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2003: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

1999: ' AND ('||p_restrict_sql||'))' ||
2000: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2001: ' UNION ' ||
2002: ' 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'||
2003: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2004: ' WHERE contains( concat_col, :cont)>0 '||
2005: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2006: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2007: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 2018: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2014: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2015: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2016: ELSE
2017: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2018: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2019: ' WHERE contains( concat_col, :cont)>0'||
2020: ' AND EXISTS ('||
2021: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2022: ' WHERE p.party_id = stage.party_id ' ||

Line 2035: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2031: ,p_dup_party_id, p_dup_party_id;
2032: END IF;
2033: ELSE
2034: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2035: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2036: ' WHERE contains( concat_col, :cont)>0'||
2037: ' AND EXISTS ('||
2038: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2039: ' WHERE p.party_id = stage.party_id ' ||