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

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

2008: END IF;
2009: IF p_restrict_entity = 'CONTACTS'
2010: THEN
2011: OPEN x_cursor FOR
2012: 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
2013: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2014: WHERE contains( concat_col, p_contains_str)>0
2015: AND d.search_context_id = p_search_ctx_id
2016: AND( (l_search_merged ='Y' )

Line 2013: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2009: IF p_restrict_entity = 'CONTACTS'
2010: THEN
2011: OPEN x_cursor FOR
2012: 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
2013: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2014: WHERE contains( concat_col, p_contains_str)>0
2015: AND d.search_context_id = p_search_ctx_id
2016: AND( (l_search_merged ='Y' )
2017: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2020: END IF;
2021: IF p_restrict_entity = 'PARTY_SITES'
2022: THEN
2023: OPEN x_cursor FOR
2024: 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
2025: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2026: WHERE contains( concat_col, p_contains_str)>0
2027: AND d.search_context_id = p_search_ctx_id
2028: AND( (l_search_merged ='Y' )

Line 2025: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2021: IF p_restrict_entity = 'PARTY_SITES'
2022: THEN
2023: OPEN x_cursor FOR
2024: 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
2025: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2026: WHERE contains( concat_col, p_contains_str)>0
2027: AND d.search_context_id = p_search_ctx_id
2028: AND( (l_search_merged ='Y' )
2029: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2037: 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);
2038: END IF;
2039: IF p_search_rel_cpts = 'N' THEN
2040: OPEN x_cursor FOR
2041: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
2042: FROM HZ_STAGED_CONTACT_POINTS stage
2043: WHERE contains( concat_col, p_contains_str)>0
2044: AND( (l_search_merged ='Y' )
2045: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2042: FROM HZ_STAGED_CONTACT_POINTS stage

2038: END IF;
2039: IF p_search_rel_cpts = 'N' THEN
2040: OPEN x_cursor FOR
2041: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
2042: FROM HZ_STAGED_CONTACT_POINTS stage
2043: WHERE contains( concat_col, p_contains_str)>0
2044: AND( (l_search_merged ='Y' )
2045: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2046: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2046: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2047: AND stage.party_id = p_party_id;
2048: ELSE
2049: OPEN x_cursor FOR
2050: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
2051: FROM HZ_STAGED_CONTACT_POINTS stage
2052: WHERE contains( concat_col, p_contains_str)>0
2053: AND( (l_search_merged ='Y' )
2054: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2051: FROM HZ_STAGED_CONTACT_POINTS stage

2047: AND stage.party_id = p_party_id;
2048: ELSE
2049: OPEN x_cursor FOR
2050: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
2051: FROM HZ_STAGED_CONTACT_POINTS stage
2052: WHERE contains( concat_col, p_contains_str)>0
2053: AND( (l_search_merged ='Y' )
2054: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2055: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2054: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2055: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2056: AND stage.party_id = p_party_id
2057: UNION
2058: 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
2059: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2060: WHERE contains( concat_col, p_contains_str)>0
2061: AND r.object_id = p_party_id
2062: AND( (l_search_merged ='Y' )

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

2055: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2056: AND stage.party_id = p_party_id
2057: UNION
2058: 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
2059: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2060: WHERE contains( concat_col, p_contains_str)>0
2061: AND r.object_id = p_party_id
2062: AND( (l_search_merged ='Y' )
2063: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2081: FROM HZ_STAGED_CONTACT_POINTS stage

2077: 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);
2078: END IF;
2079: OPEN x_cursor FOR
2080: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
2081: FROM HZ_STAGED_CONTACT_POINTS stage
2082: WHERE contains( concat_col, p_contains_str)>0
2083: AND EXISTS (
2084: SELECT 1 FROM HZ_STAGED_PARTIES p
2085: WHERE p.PARTY_ID = stage.PARTY_ID

Line 2101: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2097: END IF;
2098: IF p_person_api = 'Y' THEN
2099: OPEN x_cursor FOR
2100: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
2101: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2102: WHERE contains( concat_col, p_contains_str)>0
2103: AND d.search_context_id = p_search_ctx_id
2104: AND d.party_id = stage.party_id
2105: AND( (l_search_merged ='Y' )

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

2106: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2107: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2108: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2109: UNION
2110: 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
2111: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2112: WHERE contains( concat_col, p_contains_str)>0
2113: AND d.search_context_id = p_search_ctx_id
2114: AND d.party_id = r.subject_id

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

2107: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2108: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2109: UNION
2110: 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
2111: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2112: WHERE contains( concat_col, p_contains_str)>0
2113: AND d.search_context_id = p_search_ctx_id
2114: AND d.party_id = r.subject_id
2115: AND r.relationship_id = oc.party_relationship_id

Line 2124: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2120: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
2121: ELSE
2122: OPEN x_cursor FOR
2123: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
2124: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2125: WHERE contains( concat_col, p_contains_str)>0
2126: AND d.search_context_id = p_search_ctx_id
2127: AND d.party_id = stage.party_id
2128: AND( (l_search_merged ='Y' )

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

2140: IF (l_check_dt > 0 ) THEN
2141: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2142: 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);
2143: END IF;
2144: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) */';
2145: ELSIF (l_check > 0 ) THEN
2146: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2147: 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);
2148: END IF;

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

2145: ELSIF (l_check > 0 ) THEN
2146: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2147: 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);
2148: END IF;
2149: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
2150: END IF;
2151: IF l_search_merged = 'Y' THEN
2152: l_status_sql := ' ' ;
2153: ELSIF l_search_merged = 'I' THEN

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

2164: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
2165: ELSE
2166: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2167: END IF;
2168: 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'||
2169: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2170: ' WHERE contains( concat_col, :cont)>0 '||
2171: ' AND (stage.org_contact_id is null '||
2172: ' AND( ('''||l_search_merged||''' =''Y'' ) '||

Line 2169: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2165: ELSE
2166: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2167: END IF;
2168: 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'||
2169: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2170: ' WHERE contains( concat_col, :cont)>0 '||
2171: ' AND (stage.org_contact_id is null '||
2172: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2173: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

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

2174: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2175: ' AND ('||p_restrict_sql||'))' ||
2176: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2177: ' UNION ' ||
2178: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1'||
2179: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2180: ' WHERE contains( concat_col, :cont)>0 '||
2181: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2182: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2179: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2175: ' AND ('||p_restrict_sql||'))' ||
2176: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2177: ' UNION ' ||
2178: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1'||
2179: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2180: ' WHERE contains( concat_col, :cont)>0 '||
2181: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2182: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2183: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 2194: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2190: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2191: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2192: ELSE
2193: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1'||
2194: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2195: ' WHERE contains( concat_col, :cont)>0'||
2196: ' AND EXISTS ('||
2197: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2198: ' WHERE p.party_id = stage.party_id ' ||

Line 2211: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2207: ,p_dup_party_id, p_dup_party_id;
2208: END IF;
2209: ELSE
2210: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1'||
2211: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2212: ' WHERE contains( concat_col, :cont)>0'||
2213: ' AND EXISTS ('||
2214: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2215: ' WHERE p.party_id = stage.party_id ' ||