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

Line 2146: 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, TX5, TX6, TX8

2142: END IF;
2143: IF p_restrict_entity = 'CONTACTS'
2144: THEN
2145: OPEN x_cursor FOR
2146: 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, TX5, TX6, TX8
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 = 'CONTACTS'
2144: THEN
2145: OPEN x_cursor FOR
2146: 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, TX5, TX6, TX8
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 2158: 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, TX5, TX6, TX8

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

Line 2159: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

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

Line 2175: SELECT /+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) / CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5, TX6, TX8

2171: 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);
2172: END IF;
2173: IF p_search_rel_cpts = 'N' THEN
2174: OPEN x_cursor FOR
2175: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5, TX6, TX8
2176: FROM HZ_STAGED_CONTACT_POINTS stage
2177: WHERE contains( concat_col, p_contains_str)>0
2178: AND( (l_search_merged ='Y' )
2179: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2176: FROM HZ_STAGED_CONTACT_POINTS stage

2172: END IF;
2173: IF p_search_rel_cpts = 'N' THEN
2174: OPEN x_cursor FOR
2175: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5, TX6, TX8
2176: FROM HZ_STAGED_CONTACT_POINTS stage
2177: WHERE contains( concat_col, p_contains_str)>0
2178: AND( (l_search_merged ='Y' )
2179: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2180: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 2184: SELECT /+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) / CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5, TX6, TX8

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

Line 2185: FROM HZ_STAGED_CONTACT_POINTS stage

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

Line 2192: 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, TX5, TX6, TX8

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

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

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

Line 2215: FROM HZ_STAGED_CONTACT_POINTS stage

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

Line 2235: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

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

Line 2244: 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, TX5, TX6, TX8

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

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

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

Line 2258: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

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

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

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

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

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

Line 2302: 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, TX5, TX6, TX8'||

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

Line 2303: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

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

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

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

Line 2313: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

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

Line 2328: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

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

Line 2345: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

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