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

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

2130: END IF;
2131: IF p_restrict_entity = 'CONTACTS'
2132: THEN
2133: OPEN x_cursor FOR
2134: 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
2135: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2136: WHERE contains( concat_col, p_contains_str)>0
2137: AND d.search_context_id = p_search_ctx_id
2138: AND( (l_search_merged ='Y' )

Line 2135: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2131: IF p_restrict_entity = 'CONTACTS'
2132: THEN
2133: OPEN x_cursor FOR
2134: 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
2135: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2136: WHERE contains( concat_col, p_contains_str)>0
2137: AND d.search_context_id = p_search_ctx_id
2138: AND( (l_search_merged ='Y' )
2139: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2142: END IF;
2143: IF p_restrict_entity = 'PARTY_SITES'
2144: THEN
2145: OPEN x_cursor FOR
2146: 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
2147: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2148: WHERE contains( concat_col, p_contains_str)>0
2149: AND d.search_context_id = p_search_ctx_id
2150: AND( (l_search_merged ='Y' )

Line 2147: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2143: IF p_restrict_entity = 'PARTY_SITES'
2144: THEN
2145: OPEN x_cursor FOR
2146: 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
2147: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2148: WHERE contains( concat_col, p_contains_str)>0
2149: AND d.search_context_id = p_search_ctx_id
2150: AND( (l_search_merged ='Y' )
2151: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2159: 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);
2160: END IF;
2161: IF p_search_rel_cpts = 'N' THEN
2162: OPEN x_cursor FOR
2163: 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
2164: FROM HZ_STAGED_CONTACT_POINTS stage
2165: WHERE contains( concat_col, p_contains_str)>0
2166: AND( (l_search_merged ='Y' )
2167: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2164: FROM HZ_STAGED_CONTACT_POINTS stage

2160: END IF;
2161: IF p_search_rel_cpts = 'N' THEN
2162: OPEN x_cursor FOR
2163: 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
2164: FROM HZ_STAGED_CONTACT_POINTS stage
2165: WHERE contains( concat_col, p_contains_str)>0
2166: AND( (l_search_merged ='Y' )
2167: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2168: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2168: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2169: AND stage.party_id = p_party_id;
2170: ELSE
2171: OPEN x_cursor FOR
2172: 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
2173: FROM HZ_STAGED_CONTACT_POINTS stage
2174: WHERE contains( concat_col, p_contains_str)>0
2175: AND( (l_search_merged ='Y' )
2176: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2173: FROM HZ_STAGED_CONTACT_POINTS stage

2169: AND stage.party_id = p_party_id;
2170: ELSE
2171: OPEN x_cursor FOR
2172: 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
2173: FROM HZ_STAGED_CONTACT_POINTS stage
2174: WHERE contains( concat_col, p_contains_str)>0
2175: AND( (l_search_merged ='Y' )
2176: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2177: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2176: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2177: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2178: AND stage.party_id = p_party_id
2179: UNION
2180: 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
2181: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2182: WHERE contains( concat_col, p_contains_str)>0
2183: AND r.object_id = p_party_id
2184: AND( (l_search_merged ='Y' )

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

2177: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2178: AND stage.party_id = p_party_id
2179: UNION
2180: 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
2181: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2182: WHERE contains( concat_col, p_contains_str)>0
2183: AND r.object_id = p_party_id
2184: AND( (l_search_merged ='Y' )
2185: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2203: FROM HZ_STAGED_CONTACT_POINTS stage

2199: 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);
2200: END IF;
2201: OPEN x_cursor FOR
2202: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2203: FROM HZ_STAGED_CONTACT_POINTS stage
2204: WHERE contains( concat_col, p_contains_str)>0
2205: AND EXISTS (
2206: SELECT 1 FROM HZ_STAGED_PARTIES p
2207: WHERE p.PARTY_ID = stage.PARTY_ID

Line 2223: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2219: END IF;
2220: IF p_person_api = 'Y' THEN
2221: OPEN x_cursor FOR
2222: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2223: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2224: WHERE contains( concat_col, p_contains_str)>0
2225: AND d.search_context_id = p_search_ctx_id
2226: AND d.party_id = stage.party_id
2227: AND( (l_search_merged ='Y' )

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

2228: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2229: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2230: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2231: UNION
2232: 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
2233: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2234: WHERE contains( concat_col, p_contains_str)>0
2235: AND d.search_context_id = p_search_ctx_id
2236: AND d.party_id = r.subject_id

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

2229: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2230: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2231: UNION
2232: 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
2233: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2234: WHERE contains( concat_col, p_contains_str)>0
2235: AND d.search_context_id = p_search_ctx_id
2236: AND d.party_id = r.subject_id
2237: AND r.relationship_id = oc.party_relationship_id

Line 2246: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2242: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
2243: ELSE
2244: OPEN x_cursor FOR
2245: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2246: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2247: WHERE contains( concat_col, p_contains_str)>0
2248: AND d.search_context_id = p_search_ctx_id
2249: AND d.party_id = stage.party_id
2250: AND( (l_search_merged ='Y' )

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

2262: IF (l_check_dt > 0 ) THEN
2263: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2264: 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);
2265: END IF;
2266: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) */';
2267: ELSIF (l_check > 0 ) THEN
2268: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2269: 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);
2270: END IF;

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

2267: ELSIF (l_check > 0 ) THEN
2268: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2269: 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);
2270: END IF;
2271: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
2272: END IF;
2273: IF l_search_merged = 'Y' THEN
2274: l_status_sql := ' ' ;
2275: ELSIF l_search_merged = 'I' THEN

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

2286: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
2287: ELSE
2288: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2289: END IF;
2290: 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'||
2291: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2292: ' WHERE contains( concat_col, :cont)>0 '||
2293: ' AND (stage.org_contact_id is null '||
2294: ' AND( ('''||l_search_merged||''' =''Y'' ) '||

Line 2291: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2287: ELSE
2288: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2289: END IF;
2290: 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'||
2291: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2292: ' WHERE contains( concat_col, :cont)>0 '||
2293: ' AND (stage.org_contact_id is null '||
2294: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2295: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

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

2296: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2297: ' AND ('||p_restrict_sql||'))' ||
2298: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2299: ' UNION ' ||
2300: ' 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'||
2301: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2302: ' WHERE contains( concat_col, :cont)>0 '||
2303: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2304: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2301: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2297: ' AND ('||p_restrict_sql||'))' ||
2298: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2299: ' UNION ' ||
2300: ' 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'||
2301: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2302: ' WHERE contains( concat_col, :cont)>0 '||
2303: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2304: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2305: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 2316: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2312: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2313: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2314: ELSE
2315: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2316: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2317: ' WHERE contains( concat_col, :cont)>0'||
2318: ' AND EXISTS ('||
2319: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2320: ' WHERE p.party_id = stage.party_id ' ||

Line 2333: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2329: ,p_dup_party_id, p_dup_party_id;
2330: END IF;
2331: ELSE
2332: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2333: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2334: ' WHERE contains( concat_col, :cont)>0'||
2335: ' AND EXISTS ('||
2336: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2337: ' WHERE p.party_id = stage.party_id ' ||