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

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

2385: END IF;
2386: IF p_restrict_entity = 'CONTACTS'
2387: THEN
2388: OPEN x_cursor FOR
2389: 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
2390: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2391: WHERE contains( concat_col, p_contains_str)>0
2392: AND d.search_context_id = p_search_ctx_id
2393: AND( (l_search_merged ='Y' )

Line 2390: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2386: IF p_restrict_entity = 'CONTACTS'
2387: THEN
2388: OPEN x_cursor FOR
2389: 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
2390: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2391: WHERE contains( concat_col, p_contains_str)>0
2392: AND d.search_context_id = p_search_ctx_id
2393: AND( (l_search_merged ='Y' )
2394: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2397: END IF;
2398: IF p_restrict_entity = 'PARTY_SITES'
2399: THEN
2400: OPEN x_cursor FOR
2401: 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
2402: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2403: WHERE contains( concat_col, p_contains_str)>0
2404: AND d.search_context_id = p_search_ctx_id
2405: AND( (l_search_merged ='Y' )

Line 2402: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2398: IF p_restrict_entity = 'PARTY_SITES'
2399: THEN
2400: OPEN x_cursor FOR
2401: 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
2402: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2403: WHERE contains( concat_col, p_contains_str)>0
2404: AND d.search_context_id = p_search_ctx_id
2405: AND( (l_search_merged ='Y' )
2406: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2414: 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);
2415: END IF;
2416: IF p_search_rel_cpts = 'N' THEN
2417: OPEN x_cursor FOR
2418: 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
2419: FROM HZ_STAGED_CONTACT_POINTS stage
2420: WHERE contains( concat_col, p_contains_str)>0
2421: AND( (l_search_merged ='Y' )
2422: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2419: FROM HZ_STAGED_CONTACT_POINTS stage

2415: END IF;
2416: IF p_search_rel_cpts = 'N' THEN
2417: OPEN x_cursor FOR
2418: 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
2419: FROM HZ_STAGED_CONTACT_POINTS stage
2420: WHERE contains( concat_col, p_contains_str)>0
2421: AND( (l_search_merged ='Y' )
2422: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2423: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2423: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2424: AND stage.party_id = p_party_id;
2425: ELSE
2426: OPEN x_cursor FOR
2427: 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
2428: FROM HZ_STAGED_CONTACT_POINTS stage
2429: WHERE contains( concat_col, p_contains_str)>0
2430: AND( (l_search_merged ='Y' )
2431: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2428: FROM HZ_STAGED_CONTACT_POINTS stage

2424: AND stage.party_id = p_party_id;
2425: ELSE
2426: OPEN x_cursor FOR
2427: 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
2428: FROM HZ_STAGED_CONTACT_POINTS stage
2429: WHERE contains( concat_col, p_contains_str)>0
2430: AND( (l_search_merged ='Y' )
2431: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2432: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

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

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

2432: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2433: AND stage.party_id = p_party_id
2434: UNION
2435: 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
2436: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2437: WHERE contains( concat_col, p_contains_str)>0
2438: AND r.object_id = p_party_id
2439: AND( (l_search_merged ='Y' )
2440: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2458: FROM HZ_STAGED_CONTACT_POINTS stage

2454: 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);
2455: END IF;
2456: OPEN x_cursor FOR
2457: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2458: FROM HZ_STAGED_CONTACT_POINTS stage
2459: WHERE contains( concat_col, p_contains_str)>0
2460: AND EXISTS (
2461: SELECT 1 FROM HZ_STAGED_PARTIES p
2462: WHERE p.PARTY_ID = stage.PARTY_ID

Line 2478: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2474: END IF;
2475: IF p_person_api = 'Y' THEN
2476: OPEN x_cursor FOR
2477: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2478: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2479: WHERE contains( concat_col, p_contains_str)>0
2480: AND d.search_context_id = p_search_ctx_id
2481: AND d.party_id = stage.party_id
2482: AND( (l_search_merged ='Y' )

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

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

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

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

Line 2501: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2497: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
2498: ELSE
2499: OPEN x_cursor FOR
2500: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2501: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2502: WHERE contains( concat_col, p_contains_str)>0
2503: AND d.search_context_id = p_search_ctx_id
2504: AND d.party_id = stage.party_id
2505: AND( (l_search_merged ='Y' )

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

2517: IF (l_check_dt > 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_cpt 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_U1) */';
2522: ELSIF (l_check > 0 ) THEN
2523: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2524: 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);
2525: END IF;

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

2522: ELSIF (l_check > 0 ) THEN
2523: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2524: 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);
2525: END IF;
2526: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
2527: END IF;
2528: IF l_search_merged = 'Y' THEN
2529: l_status_sql := ' ' ;
2530: ELSIF l_search_merged = 'I' THEN

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

2541: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
2542: ELSE
2543: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2544: END IF;
2545: 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'||
2546: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2547: ' WHERE contains( concat_col, :cont)>0 '||
2548: ' AND (stage.org_contact_id is null '||
2549: ' AND( ('''||l_search_merged||''' =''Y'' ) '||

Line 2546: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2542: ELSE
2543: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2544: END IF;
2545: 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'||
2546: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2547: ' WHERE contains( concat_col, :cont)>0 '||
2548: ' AND (stage.org_contact_id is null '||
2549: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2550: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

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

2551: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2552: ' AND ('||p_restrict_sql||'))' ||
2553: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2554: ' UNION ' ||
2555: ' 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'||
2556: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2557: ' WHERE contains( concat_col, :cont)>0 '||
2558: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2559: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2556: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2552: ' AND ('||p_restrict_sql||'))' ||
2553: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2554: ' UNION ' ||
2555: ' 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'||
2556: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2557: ' WHERE contains( concat_col, :cont)>0 '||
2558: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2559: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2560: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 2571: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2567: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2568: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2569: ELSE
2570: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2571: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2572: ' WHERE contains( concat_col, :cont)>0'||
2573: ' AND EXISTS ('||
2574: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2575: ' WHERE p.party_id = stage.party_id ' ||

Line 2588: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2584: ,p_dup_party_id, p_dup_party_id;
2585: END IF;
2586: ELSE
2587: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2588: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2589: ' WHERE contains( concat_col, :cont)>0'||
2590: ' AND EXISTS ('||
2591: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2592: ' WHERE p.party_id = stage.party_id ' ||