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

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

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

Line 2018: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

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

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

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

Line 2030: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

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

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

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

Line 2047: FROM HZ_STAGED_CONTACT_POINTS stage

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

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

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

Line 2056: FROM HZ_STAGED_CONTACT_POINTS stage

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

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

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

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

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

Line 2086: FROM HZ_STAGED_CONTACT_POINTS stage

2082: 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);
2083: END IF;
2084: OPEN x_cursor FOR
2085: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2086: FROM HZ_STAGED_CONTACT_POINTS stage
2087: WHERE contains( concat_col, p_contains_str)>0
2088: AND EXISTS (
2089: SELECT 1 FROM HZ_STAGED_PARTIES p
2090: WHERE p.PARTY_ID = stage.PARTY_ID

Line 2106: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2102: END IF;
2103: IF p_person_api = 'Y' THEN
2104: OPEN x_cursor FOR
2105: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2106: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2107: WHERE contains( concat_col, p_contains_str)>0
2108: AND d.search_context_id = p_search_ctx_id
2109: AND d.party_id = stage.party_id
2110: AND( (l_search_merged ='Y' )

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

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

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

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

Line 2129: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

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

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

2145: IF (l_check_dt > 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_cpt 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_U1) */';
2150: ELSIF (l_check > 0 ) THEN
2151: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2152: 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);
2153: END IF;

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

2150: ELSIF (l_check > 0 ) THEN
2151: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2152: 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);
2153: END IF;
2154: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
2155: END IF;
2156: IF l_search_merged = 'Y' THEN
2157: l_status_sql := ' ' ;
2158: ELSIF l_search_merged = 'I' THEN

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

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

Line 2174: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

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

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

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

Line 2184: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

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

Line 2199: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2195: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2196: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2197: ELSE
2198: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2199: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2200: ' WHERE contains( concat_col, :cont)>0'||
2201: ' AND EXISTS ('||
2202: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2203: ' WHERE p.party_id = stage.party_id ' ||

Line 2216: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2212: ,p_dup_party_id, p_dup_party_id;
2213: END IF;
2214: ELSE
2215: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2216: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2217: ' WHERE contains( concat_col, :cont)>0'||
2218: ' AND EXISTS ('||
2219: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2220: ' WHERE p.party_id = stage.party_id ' ||