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

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

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

Line 2150: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

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

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

2157: END IF;
2158: IF p_restrict_entity = 'PARTY_SITES'
2159: THEN
2160: OPEN x_cursor FOR
2161: 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
2162: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2163: WHERE contains( concat_col, p_contains_str)>0
2164: AND d.search_context_id = p_search_ctx_id
2165: AND( (l_search_merged ='Y' )

Line 2162: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2158: IF p_restrict_entity = 'PARTY_SITES'
2159: THEN
2160: OPEN x_cursor FOR
2161: 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
2162: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2163: WHERE contains( concat_col, p_contains_str)>0
2164: AND d.search_context_id = p_search_ctx_id
2165: AND( (l_search_merged ='Y' )
2166: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2174: 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);
2175: END IF;
2176: IF p_search_rel_cpts = 'N' THEN
2177: OPEN x_cursor FOR
2178: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2179: FROM HZ_STAGED_CONTACT_POINTS stage
2180: WHERE contains( concat_col, p_contains_str)>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: END IF;
2176: IF p_search_rel_cpts = 'N' THEN
2177: OPEN x_cursor FOR
2178: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2179: FROM HZ_STAGED_CONTACT_POINTS stage
2180: WHERE contains( concat_col, p_contains_str)>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 2187: SELECT /+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) / CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID

2183: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2184: AND stage.party_id = p_party_id;
2185: ELSE
2186: OPEN x_cursor FOR
2187: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2188: FROM HZ_STAGED_CONTACT_POINTS stage
2189: WHERE contains( concat_col, p_contains_str)>0
2190: AND( (l_search_merged ='Y' )
2191: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2188: FROM HZ_STAGED_CONTACT_POINTS stage

2184: AND stage.party_id = p_party_id;
2185: ELSE
2186: OPEN x_cursor FOR
2187: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2188: FROM HZ_STAGED_CONTACT_POINTS stage
2189: WHERE contains( concat_col, p_contains_str)>0
2190: AND( (l_search_merged ='Y' )
2191: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2192: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2191: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2192: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2193: AND stage.party_id = p_party_id
2194: UNION
2195: 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
2196: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2197: WHERE contains( concat_col, p_contains_str)>0
2198: AND r.object_id = p_party_id
2199: AND( (l_search_merged ='Y' )

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

2192: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2193: AND stage.party_id = p_party_id
2194: UNION
2195: 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
2196: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2197: WHERE contains( concat_col, p_contains_str)>0
2198: AND r.object_id = p_party_id
2199: AND( (l_search_merged ='Y' )
2200: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2218: FROM HZ_STAGED_CONTACT_POINTS stage

2214: 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);
2215: END IF;
2216: OPEN x_cursor FOR
2217: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2218: FROM HZ_STAGED_CONTACT_POINTS stage
2219: WHERE contains( concat_col, p_contains_str)>0
2220: AND EXISTS (
2221: SELECT 1 FROM HZ_STAGED_PARTIES p
2222: WHERE p.PARTY_ID = stage.PARTY_ID

Line 2238: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2234: END IF;
2235: IF p_person_api = 'Y' THEN
2236: OPEN x_cursor FOR
2237: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2238: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2239: WHERE contains( concat_col, p_contains_str)>0
2240: AND d.search_context_id = p_search_ctx_id
2241: AND d.party_id = stage.party_id
2242: AND( (l_search_merged ='Y' )

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

2243: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2244: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2245: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2246: UNION
2247: 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
2248: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2249: WHERE contains( concat_col, p_contains_str)>0
2250: AND d.search_context_id = p_search_ctx_id
2251: AND d.party_id = r.subject_id

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

2244: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2245: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2246: UNION
2247: 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
2248: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2249: WHERE contains( concat_col, p_contains_str)>0
2250: AND d.search_context_id = p_search_ctx_id
2251: AND d.party_id = r.subject_id
2252: AND r.relationship_id = oc.party_relationship_id

Line 2261: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2257: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
2258: ELSE
2259: OPEN x_cursor FOR
2260: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2261: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2262: WHERE contains( concat_col, p_contains_str)>0
2263: AND d.search_context_id = p_search_ctx_id
2264: AND d.party_id = stage.party_id
2265: AND( (l_search_merged ='Y' )

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

2277: IF (l_check_dt > 0 ) THEN
2278: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2279: 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);
2280: END IF;
2281: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) */';
2282: ELSIF (l_check > 0 ) THEN
2283: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2284: 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);
2285: END IF;

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

2282: ELSIF (l_check > 0 ) THEN
2283: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2284: 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);
2285: END IF;
2286: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
2287: END IF;
2288: IF l_search_merged = 'Y' THEN
2289: l_status_sql := ' ' ;
2290: ELSIF l_search_merged = 'I' THEN

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

2301: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
2302: ELSE
2303: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2304: END IF;
2305: 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 '||
2306: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2307: ' WHERE contains( concat_col, :cont)>0 '||
2308: ' AND (stage.org_contact_id is null '||
2309: ' AND( ('''||l_search_merged||''' =''Y'' ) '||

Line 2306: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2302: ELSE
2303: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2304: END IF;
2305: 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 '||
2306: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2307: ' WHERE contains( concat_col, :cont)>0 '||
2308: ' AND (stage.org_contact_id is null '||
2309: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2310: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

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

2311: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2312: ' AND ('||p_restrict_sql||'))' ||
2313: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2314: ' UNION ' ||
2315: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2316: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2317: ' WHERE contains( concat_col, :cont)>0 '||
2318: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2319: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2316: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2312: ' AND ('||p_restrict_sql||'))' ||
2313: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2314: ' UNION ' ||
2315: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2316: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2317: ' WHERE contains( concat_col, :cont)>0 '||
2318: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2319: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2320: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 2331: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2327: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2328: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2329: ELSE
2330: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2331: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2332: ' WHERE contains( concat_col, :cont)>0'||
2333: ' AND EXISTS ('||
2334: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2335: ' WHERE p.party_id = stage.party_id ' ||

Line 2348: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2344: ,p_dup_party_id, p_dup_party_id;
2345: END IF;
2346: ELSE
2347: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2348: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2349: ' WHERE contains( concat_col, :cont)>0'||
2350: ' AND EXISTS ('||
2351: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2352: ' WHERE p.party_id = stage.party_id ' ||