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

Line 2384: 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, TX8, TX7

2380: END IF;
2381: IF p_restrict_entity = 'CONTACTS'
2382: THEN
2383: OPEN x_cursor FOR
2384: 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, TX8, TX7
2385: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2386: WHERE contains( concat_col, p_contains_str)>0
2387: AND d.search_context_id = p_search_ctx_id
2388: AND( (l_search_merged ='Y' )

Line 2385: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2381: IF p_restrict_entity = 'CONTACTS'
2382: THEN
2383: OPEN x_cursor FOR
2384: 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, TX8, TX7
2385: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2386: WHERE contains( concat_col, p_contains_str)>0
2387: AND d.search_context_id = p_search_ctx_id
2388: AND( (l_search_merged ='Y' )
2389: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2396: 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, TX8, TX7

2392: END IF;
2393: IF p_restrict_entity = 'PARTY_SITES'
2394: THEN
2395: OPEN x_cursor FOR
2396: 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, TX8, TX7
2397: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2398: WHERE contains( concat_col, p_contains_str)>0
2399: AND d.search_context_id = p_search_ctx_id
2400: AND( (l_search_merged ='Y' )

Line 2397: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2393: IF p_restrict_entity = 'PARTY_SITES'
2394: THEN
2395: OPEN x_cursor FOR
2396: 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, TX8, TX7
2397: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2398: WHERE contains( concat_col, p_contains_str)>0
2399: AND d.search_context_id = p_search_ctx_id
2400: AND( (l_search_merged ='Y' )
2401: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2413: SELECT /+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) / CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7

2409: 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);
2410: END IF;
2411: IF p_search_rel_cpts = 'N' THEN
2412: OPEN x_cursor FOR
2413: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
2414: FROM HZ_STAGED_CONTACT_POINTS stage
2415: WHERE contains( concat_col, p_contains_str)>0
2416: AND( (l_search_merged ='Y' )
2417: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2414: FROM HZ_STAGED_CONTACT_POINTS stage

2410: END IF;
2411: IF p_search_rel_cpts = 'N' THEN
2412: OPEN x_cursor FOR
2413: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
2414: FROM HZ_STAGED_CONTACT_POINTS stage
2415: WHERE contains( concat_col, p_contains_str)>0
2416: AND( (l_search_merged ='Y' )
2417: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2418: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 2422: SELECT /+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) / CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7

2418: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2419: AND stage.party_id = p_party_id;
2420: ELSE
2421: OPEN x_cursor FOR
2422: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
2423: FROM HZ_STAGED_CONTACT_POINTS stage
2424: WHERE contains( concat_col, p_contains_str)>0
2425: AND( (l_search_merged ='Y' )
2426: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2423: FROM HZ_STAGED_CONTACT_POINTS stage

2419: AND stage.party_id = p_party_id;
2420: ELSE
2421: OPEN x_cursor FOR
2422: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
2423: FROM HZ_STAGED_CONTACT_POINTS stage
2424: WHERE contains( concat_col, p_contains_str)>0
2425: AND( (l_search_merged ='Y' )
2426: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2427: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 2430: 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, TX8, TX7

2426: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2427: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2428: AND stage.party_id = p_party_id
2429: UNION
2430: 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, TX8, TX7
2431: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2432: WHERE contains( concat_col, p_contains_str)>0
2433: AND r.object_id = p_party_id
2434: AND( (l_search_merged ='Y' )

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

2427: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2428: AND stage.party_id = p_party_id
2429: UNION
2430: 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, TX8, TX7
2431: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2432: WHERE contains( concat_col, p_contains_str)>0
2433: AND r.object_id = p_party_id
2434: AND( (l_search_merged ='Y' )
2435: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2453: FROM HZ_STAGED_CONTACT_POINTS stage

2449: 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);
2450: END IF;
2451: OPEN x_cursor FOR
2452: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
2453: FROM HZ_STAGED_CONTACT_POINTS stage
2454: WHERE contains( concat_col, p_contains_str)>0
2455: AND EXISTS (
2456: SELECT 1 FROM HZ_STAGED_PARTIES p
2457: WHERE p.PARTY_ID = stage.PARTY_ID

Line 2473: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2469: END IF;
2470: IF p_person_api = 'Y' THEN
2471: OPEN x_cursor FOR
2472: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
2473: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2474: WHERE contains( concat_col, p_contains_str)>0
2475: AND d.search_context_id = p_search_ctx_id
2476: AND d.party_id = stage.party_id
2477: AND( (l_search_merged ='Y' )

Line 2482: 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, TX8, TX7

2478: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2479: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2480: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2481: UNION
2482: 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, TX8, TX7
2483: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2484: WHERE contains( concat_col, p_contains_str)>0
2485: AND d.search_context_id = p_search_ctx_id
2486: AND d.party_id = r.subject_id

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

2479: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2480: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2481: UNION
2482: 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, TX8, TX7
2483: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2484: WHERE contains( concat_col, p_contains_str)>0
2485: AND d.search_context_id = p_search_ctx_id
2486: AND d.party_id = r.subject_id
2487: AND r.relationship_id = oc.party_relationship_id

Line 2496: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2492: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
2493: ELSE
2494: OPEN x_cursor FOR
2495: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
2496: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2497: WHERE contains( concat_col, p_contains_str)>0
2498: AND d.search_context_id = p_search_ctx_id
2499: AND d.party_id = stage.party_id
2500: AND( (l_search_merged ='Y' )

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

2512: IF (l_check_dt > 0 ) THEN
2513: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2514: 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);
2515: END IF;
2516: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) */';
2517: ELSIF (l_check > 0 ) THEN
2518: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2519: 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);
2520: END IF;

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

2517: ELSIF (l_check > 0 ) THEN
2518: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2519: 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);
2520: END IF;
2521: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
2522: END IF;
2523: IF l_search_merged = 'Y' THEN
2524: l_status_sql := ' ' ;
2525: ELSIF l_search_merged = 'I' THEN

Line 2540: 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, TX8, TX7'||

2536: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
2537: ELSE
2538: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2539: END IF;
2540: 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, TX8, TX7'||
2541: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2542: ' WHERE contains( concat_col, :cont)>0 '||
2543: ' AND (stage.org_contact_id is null '||
2544: ' AND( ('''||l_search_merged||''' =''Y'' ) '||

Line 2541: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2537: ELSE
2538: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2539: END IF;
2540: 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, TX8, TX7'||
2541: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2542: ' WHERE contains( concat_col, :cont)>0 '||
2543: ' AND (stage.org_contact_id is null '||
2544: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2545: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

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

2546: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2547: ' AND ('||p_restrict_sql||'))' ||
2548: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2549: ' UNION ' ||
2550: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7'||
2551: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2552: ' WHERE contains( concat_col, :cont)>0 '||
2553: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2554: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2551: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2547: ' AND ('||p_restrict_sql||'))' ||
2548: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2549: ' UNION ' ||
2550: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7'||
2551: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2552: ' WHERE contains( concat_col, :cont)>0 '||
2553: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2554: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2555: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 2566: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2562: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2563: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2564: ELSE
2565: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7'||
2566: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2567: ' WHERE contains( concat_col, :cont)>0'||
2568: ' AND EXISTS ('||
2569: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2570: ' WHERE p.party_id = stage.party_id ' ||

Line 2583: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2579: ,p_dup_party_id, p_dup_party_id;
2580: END IF;
2581: ELSE
2582: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7'||
2583: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2584: ' WHERE contains( concat_col, :cont)>0'||
2585: ' AND EXISTS ('||
2586: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2587: ' WHERE p.party_id = stage.party_id ' ||