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

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

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

Line 2160: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

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

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

2167: END IF;
2168: IF p_restrict_entity = 'PARTY_SITES'
2169: THEN
2170: OPEN x_cursor FOR
2171: 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
2172: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2173: WHERE contains( concat_col, p_contains_str)>0
2174: AND d.search_context_id = p_search_ctx_id
2175: AND( (l_search_merged ='Y' )

Line 2172: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2168: IF p_restrict_entity = 'PARTY_SITES'
2169: THEN
2170: OPEN x_cursor FOR
2171: 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
2172: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2173: WHERE contains( concat_col, p_contains_str)>0
2174: AND d.search_context_id = p_search_ctx_id
2175: AND( (l_search_merged ='Y' )
2176: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2184: 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);
2185: END IF;
2186: IF p_search_rel_cpts = 'N' THEN
2187: OPEN x_cursor FOR
2188: 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
2189: FROM HZ_STAGED_CONTACT_POINTS stage
2190: WHERE contains( concat_col, p_contains_str)>0
2191: AND( (l_search_merged ='Y' )
2192: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2189: FROM HZ_STAGED_CONTACT_POINTS stage

2185: END IF;
2186: IF p_search_rel_cpts = 'N' THEN
2187: OPEN x_cursor FOR
2188: 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
2189: FROM HZ_STAGED_CONTACT_POINTS stage
2190: WHERE contains( concat_col, p_contains_str)>0
2191: AND( (l_search_merged ='Y' )
2192: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2193: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2193: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2194: AND stage.party_id = p_party_id;
2195: ELSE
2196: OPEN x_cursor FOR
2197: 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
2198: FROM HZ_STAGED_CONTACT_POINTS stage
2199: WHERE contains( concat_col, p_contains_str)>0
2200: AND( (l_search_merged ='Y' )
2201: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2198: FROM HZ_STAGED_CONTACT_POINTS stage

2194: AND stage.party_id = p_party_id;
2195: ELSE
2196: OPEN x_cursor FOR
2197: 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
2198: FROM HZ_STAGED_CONTACT_POINTS stage
2199: WHERE contains( concat_col, p_contains_str)>0
2200: AND( (l_search_merged ='Y' )
2201: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2202: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2201: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2202: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2203: AND stage.party_id = p_party_id
2204: UNION
2205: 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
2206: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2207: WHERE contains( concat_col, p_contains_str)>0
2208: AND r.object_id = p_party_id
2209: AND( (l_search_merged ='Y' )

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

2202: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2203: AND stage.party_id = p_party_id
2204: UNION
2205: 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
2206: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2207: WHERE contains( concat_col, p_contains_str)>0
2208: AND r.object_id = p_party_id
2209: AND( (l_search_merged ='Y' )
2210: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2228: FROM HZ_STAGED_CONTACT_POINTS stage

2224: 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);
2225: END IF;
2226: OPEN x_cursor FOR
2227: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2228: FROM HZ_STAGED_CONTACT_POINTS stage
2229: WHERE contains( concat_col, p_contains_str)>0
2230: AND EXISTS (
2231: SELECT 1 FROM HZ_STAGED_PARTIES p
2232: WHERE p.PARTY_ID = stage.PARTY_ID

Line 2248: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2244: END IF;
2245: IF p_person_api = 'Y' THEN
2246: OPEN x_cursor FOR
2247: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2248: FROM HZ_DQM_PARTIES_GT d, 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 = stage.party_id
2252: AND( (l_search_merged ='Y' )

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

2253: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2254: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2255: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2256: UNION
2257: 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
2258: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, 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 = r.subject_id

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

2254: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2255: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2256: UNION
2257: 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
2258: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, 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 = r.subject_id
2262: AND r.relationship_id = oc.party_relationship_id

Line 2271: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2267: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
2268: ELSE
2269: OPEN x_cursor FOR
2270: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2271: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2272: WHERE contains( concat_col, p_contains_str)>0
2273: AND d.search_context_id = p_search_ctx_id
2274: AND d.party_id = stage.party_id
2275: AND( (l_search_merged ='Y' )

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

2287: IF (l_check_dt > 0 ) THEN
2288: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2289: 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);
2290: END IF;
2291: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) */';
2292: ELSIF (l_check > 0 ) THEN
2293: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2294: 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);
2295: END IF;

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

2292: ELSIF (l_check > 0 ) THEN
2293: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2294: 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);
2295: END IF;
2296: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
2297: END IF;
2298: IF l_search_merged = 'Y' THEN
2299: l_status_sql := ' ' ;
2300: ELSIF l_search_merged = 'I' THEN

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

2311: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
2312: ELSE
2313: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2314: END IF;
2315: 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'||
2316: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2317: ' WHERE contains( concat_col, :cont)>0 '||
2318: ' AND (stage.org_contact_id is null '||
2319: ' AND( ('''||l_search_merged||''' =''Y'' ) '||

Line 2316: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2312: ELSE
2313: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2314: END IF;
2315: 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'||
2316: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2317: ' WHERE contains( concat_col, :cont)>0 '||
2318: ' AND (stage.org_contact_id is null '||
2319: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2320: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

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

2321: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2322: ' AND ('||p_restrict_sql||'))' ||
2323: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2324: ' UNION ' ||
2325: ' 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'||
2326: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2327: ' WHERE contains( concat_col, :cont)>0 '||
2328: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2329: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2326: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2322: ' AND ('||p_restrict_sql||'))' ||
2323: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2324: ' UNION ' ||
2325: ' 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'||
2326: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2327: ' WHERE contains( concat_col, :cont)>0 '||
2328: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2329: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2330: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 2341: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2337: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2338: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2339: ELSE
2340: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2341: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2342: ' WHERE contains( concat_col, :cont)>0'||
2343: ' AND EXISTS ('||
2344: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2345: ' WHERE p.party_id = stage.party_id ' ||

Line 2358: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2354: ,p_dup_party_id, p_dup_party_id;
2355: END IF;
2356: ELSE
2357: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2358: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2359: ' WHERE contains( concat_col, :cont)>0'||
2360: ' AND EXISTS ('||
2361: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2362: ' WHERE p.party_id = stage.party_id ' ||