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

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

2354: END IF;
2355: IF p_restrict_entity = 'CONTACTS'
2356: THEN
2357: OPEN x_cursor FOR
2358: 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
2359: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2360: WHERE contains( concat_col, p_contains_str)>0
2361: AND d.search_context_id = p_search_ctx_id
2362: AND( (l_search_merged ='Y' )

Line 2359: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2355: IF p_restrict_entity = 'CONTACTS'
2356: THEN
2357: OPEN x_cursor FOR
2358: 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
2359: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2360: WHERE contains( concat_col, p_contains_str)>0
2361: AND d.search_context_id = p_search_ctx_id
2362: AND( (l_search_merged ='Y' )
2363: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2366: END IF;
2367: IF p_restrict_entity = 'PARTY_SITES'
2368: THEN
2369: OPEN x_cursor FOR
2370: 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
2371: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2372: WHERE contains( concat_col, p_contains_str)>0
2373: AND d.search_context_id = p_search_ctx_id
2374: AND( (l_search_merged ='Y' )

Line 2371: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2367: IF p_restrict_entity = 'PARTY_SITES'
2368: THEN
2369: OPEN x_cursor FOR
2370: 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
2371: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2372: WHERE contains( concat_col, p_contains_str)>0
2373: AND d.search_context_id = p_search_ctx_id
2374: AND( (l_search_merged ='Y' )
2375: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2383: 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);
2384: END IF;
2385: IF p_search_rel_cpts = 'N' THEN
2386: OPEN x_cursor FOR
2387: 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
2388: FROM HZ_STAGED_CONTACT_POINTS stage
2389: WHERE contains( concat_col, p_contains_str)>0
2390: AND( (l_search_merged ='Y' )
2391: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2388: FROM HZ_STAGED_CONTACT_POINTS stage

2384: END IF;
2385: IF p_search_rel_cpts = 'N' THEN
2386: OPEN x_cursor FOR
2387: 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
2388: FROM HZ_STAGED_CONTACT_POINTS stage
2389: WHERE contains( concat_col, p_contains_str)>0
2390: AND( (l_search_merged ='Y' )
2391: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2392: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2392: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2393: AND stage.party_id = p_party_id;
2394: ELSE
2395: OPEN x_cursor FOR
2396: 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
2397: FROM HZ_STAGED_CONTACT_POINTS stage
2398: WHERE contains( concat_col, p_contains_str)>0
2399: AND( (l_search_merged ='Y' )
2400: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2397: FROM HZ_STAGED_CONTACT_POINTS stage

2393: AND stage.party_id = p_party_id;
2394: ELSE
2395: OPEN x_cursor FOR
2396: 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
2397: FROM HZ_STAGED_CONTACT_POINTS stage
2398: WHERE contains( concat_col, p_contains_str)>0
2399: AND( (l_search_merged ='Y' )
2400: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2401: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2400: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2401: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2402: AND stage.party_id = p_party_id
2403: UNION
2404: 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
2405: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2406: WHERE contains( concat_col, p_contains_str)>0
2407: AND r.object_id = p_party_id
2408: AND( (l_search_merged ='Y' )

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

2401: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2402: AND stage.party_id = p_party_id
2403: UNION
2404: 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
2405: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2406: WHERE contains( concat_col, p_contains_str)>0
2407: AND r.object_id = p_party_id
2408: AND( (l_search_merged ='Y' )
2409: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2427: FROM HZ_STAGED_CONTACT_POINTS stage

2423: 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);
2424: END IF;
2425: OPEN x_cursor FOR
2426: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2427: FROM HZ_STAGED_CONTACT_POINTS stage
2428: WHERE contains( concat_col, p_contains_str)>0
2429: AND EXISTS (
2430: SELECT 1 FROM HZ_STAGED_PARTIES p
2431: WHERE p.PARTY_ID = stage.PARTY_ID

Line 2447: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2443: END IF;
2444: IF p_person_api = 'Y' THEN
2445: OPEN x_cursor FOR
2446: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2447: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2448: WHERE contains( concat_col, p_contains_str)>0
2449: AND d.search_context_id = p_search_ctx_id
2450: AND d.party_id = stage.party_id
2451: AND( (l_search_merged ='Y' )

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

2452: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2453: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2454: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2455: UNION
2456: 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
2457: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2458: WHERE contains( concat_col, p_contains_str)>0
2459: AND d.search_context_id = p_search_ctx_id
2460: AND d.party_id = r.subject_id

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

2453: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2454: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2455: UNION
2456: 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
2457: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2458: WHERE contains( concat_col, p_contains_str)>0
2459: AND d.search_context_id = p_search_ctx_id
2460: AND d.party_id = r.subject_id
2461: AND r.relationship_id = oc.party_relationship_id

Line 2470: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2466: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
2467: ELSE
2468: OPEN x_cursor FOR
2469: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2470: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2471: WHERE contains( concat_col, p_contains_str)>0
2472: AND d.search_context_id = p_search_ctx_id
2473: AND d.party_id = stage.party_id
2474: AND( (l_search_merged ='Y' )

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

2486: IF (l_check_dt > 0 ) THEN
2487: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2488: 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);
2489: END IF;
2490: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) */';
2491: ELSIF (l_check > 0 ) THEN
2492: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2493: 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);
2494: END IF;

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

2491: ELSIF (l_check > 0 ) THEN
2492: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2493: 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);
2494: END IF;
2495: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
2496: END IF;
2497: IF l_search_merged = 'Y' THEN
2498: l_status_sql := ' ' ;
2499: ELSIF l_search_merged = 'I' THEN

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

2510: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
2511: ELSE
2512: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2513: END IF;
2514: 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'||
2515: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2516: ' WHERE contains( concat_col, :cont)>0 '||
2517: ' AND (stage.org_contact_id is null '||
2518: ' AND( ('''||l_search_merged||''' =''Y'' ) '||

Line 2515: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2511: ELSE
2512: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2513: END IF;
2514: 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'||
2515: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2516: ' WHERE contains( concat_col, :cont)>0 '||
2517: ' AND (stage.org_contact_id is null '||
2518: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2519: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

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

2520: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2521: ' AND ('||p_restrict_sql||'))' ||
2522: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2523: ' UNION ' ||
2524: ' 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'||
2525: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2526: ' WHERE contains( concat_col, :cont)>0 '||
2527: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2528: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2525: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2521: ' AND ('||p_restrict_sql||'))' ||
2522: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2523: ' UNION ' ||
2524: ' 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'||
2525: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2526: ' WHERE contains( concat_col, :cont)>0 '||
2527: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2528: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2529: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 2540: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2536: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2537: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2538: ELSE
2539: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2540: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2541: ' WHERE contains( concat_col, :cont)>0'||
2542: ' AND EXISTS ('||
2543: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2544: ' WHERE p.party_id = stage.party_id ' ||

Line 2557: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2553: ,p_dup_party_id, p_dup_party_id;
2554: END IF;
2555: ELSE
2556: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2557: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2558: ' WHERE contains( concat_col, :cont)>0'||
2559: ' AND EXISTS ('||
2560: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2561: ' WHERE p.party_id = stage.party_id ' ||