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

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

2917: END IF;
2918: IF p_restrict_entity = 'CONTACTS'
2919: THEN
2920: OPEN x_cursor FOR
2921: 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
2922: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2923: WHERE contains( concat_col, p_contains_str)>0
2924: AND d.search_context_id = p_search_ctx_id
2925: AND( (l_search_merged ='Y' )

Line 2922: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2918: IF p_restrict_entity = 'CONTACTS'
2919: THEN
2920: OPEN x_cursor FOR
2921: 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
2922: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2923: WHERE contains( concat_col, p_contains_str)>0
2924: AND d.search_context_id = p_search_ctx_id
2925: AND( (l_search_merged ='Y' )
2926: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2929: END IF;
2930: IF p_restrict_entity = 'PARTY_SITES'
2931: THEN
2932: OPEN x_cursor FOR
2933: 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
2934: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2935: WHERE contains( concat_col, p_contains_str)>0
2936: AND d.search_context_id = p_search_ctx_id
2937: AND( (l_search_merged ='Y' )

Line 2934: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2930: IF p_restrict_entity = 'PARTY_SITES'
2931: THEN
2932: OPEN x_cursor FOR
2933: 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
2934: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2935: WHERE contains( concat_col, p_contains_str)>0
2936: AND d.search_context_id = p_search_ctx_id
2937: AND( (l_search_merged ='Y' )
2938: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2946: 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);
2947: END IF;
2948: IF p_search_rel_cpts = 'N' THEN
2949: OPEN x_cursor FOR
2950: 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
2951: FROM HZ_STAGED_CONTACT_POINTS stage
2952: WHERE contains( concat_col, p_contains_str)>0
2953: AND( (l_search_merged ='Y' )
2954: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2951: FROM HZ_STAGED_CONTACT_POINTS stage

2947: END IF;
2948: IF p_search_rel_cpts = 'N' THEN
2949: OPEN x_cursor FOR
2950: 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
2951: FROM HZ_STAGED_CONTACT_POINTS stage
2952: WHERE contains( concat_col, p_contains_str)>0
2953: AND( (l_search_merged ='Y' )
2954: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2955: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2955: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2956: AND stage.party_id = p_party_id;
2957: ELSE
2958: OPEN x_cursor FOR
2959: 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
2960: FROM HZ_STAGED_CONTACT_POINTS stage
2961: WHERE contains( concat_col, p_contains_str)>0
2962: AND( (l_search_merged ='Y' )
2963: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2960: FROM HZ_STAGED_CONTACT_POINTS stage

2956: AND stage.party_id = p_party_id;
2957: ELSE
2958: OPEN x_cursor FOR
2959: 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
2960: FROM HZ_STAGED_CONTACT_POINTS stage
2961: WHERE contains( concat_col, p_contains_str)>0
2962: AND( (l_search_merged ='Y' )
2963: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2964: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2963: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2964: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2965: AND stage.party_id = p_party_id
2966: UNION
2967: 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
2968: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2969: WHERE contains( concat_col, p_contains_str)>0
2970: AND r.object_id = p_party_id
2971: AND( (l_search_merged ='Y' )

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

2964: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2965: AND stage.party_id = p_party_id
2966: UNION
2967: 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
2968: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2969: WHERE contains( concat_col, p_contains_str)>0
2970: AND r.object_id = p_party_id
2971: AND( (l_search_merged ='Y' )
2972: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2990: FROM HZ_STAGED_CONTACT_POINTS stage

2986: 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);
2987: END IF;
2988: OPEN x_cursor FOR
2989: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5
2990: FROM HZ_STAGED_CONTACT_POINTS stage
2991: WHERE contains( concat_col, p_contains_str)>0
2992: AND EXISTS (
2993: SELECT 1 FROM HZ_STAGED_PARTIES p
2994: WHERE p.PARTY_ID = stage.PARTY_ID

Line 3010: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

3006: END IF;
3007: IF p_person_api = 'Y' THEN
3008: OPEN x_cursor FOR
3009: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5
3010: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
3011: WHERE contains( concat_col, p_contains_str)>0
3012: AND d.search_context_id = p_search_ctx_id
3013: AND d.party_id = stage.party_id
3014: AND( (l_search_merged ='Y' )

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

3015: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
3016: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
3017: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
3018: UNION
3019: 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
3020: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
3021: WHERE contains( concat_col, p_contains_str)>0
3022: AND d.search_context_id = p_search_ctx_id
3023: AND d.party_id = r.subject_id

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

3016: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
3017: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
3018: UNION
3019: 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
3020: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
3021: WHERE contains( concat_col, p_contains_str)>0
3022: AND d.search_context_id = p_search_ctx_id
3023: AND d.party_id = r.subject_id
3024: AND r.relationship_id = oc.party_relationship_id

Line 3033: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

3029: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
3030: ELSE
3031: OPEN x_cursor FOR
3032: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5
3033: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
3034: WHERE contains( concat_col, p_contains_str)>0
3035: AND d.search_context_id = p_search_ctx_id
3036: AND d.party_id = stage.party_id
3037: AND( (l_search_merged ='Y' )

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

3049: IF (l_check_dt > 0 ) THEN
3050: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3051: 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);
3052: END IF;
3053: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) */';
3054: ELSIF (l_check > 0 ) THEN
3055: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3056: 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);
3057: END IF;

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

3054: ELSIF (l_check > 0 ) THEN
3055: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3056: 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);
3057: END IF;
3058: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
3059: END IF;
3060: IF l_search_merged = 'Y' THEN
3061: l_status_sql := ' ' ;
3062: ELSIF l_search_merged = 'I' THEN

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

3073: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
3074: ELSE
3075: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
3076: END IF;
3077: 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'||
3078: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3079: ' WHERE contains( concat_col, :cont)>0 '||
3080: ' AND (stage.org_contact_id is null '||
3081: ' AND( ('''||l_search_merged||''' =''Y'' ) '||

Line 3078: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

3074: ELSE
3075: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
3076: END IF;
3077: 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'||
3078: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3079: ' WHERE contains( concat_col, :cont)>0 '||
3080: ' AND (stage.org_contact_id is null '||
3081: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3082: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

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

3083: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
3084: ' AND ('||p_restrict_sql||'))' ||
3085: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
3086: ' UNION ' ||
3087: ' 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'||
3088: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3089: ' WHERE contains( concat_col, :cont)>0 '||
3090: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3091: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 3088: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

3084: ' AND ('||p_restrict_sql||'))' ||
3085: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
3086: ' UNION ' ||
3087: ' 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'||
3088: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3089: ' WHERE contains( concat_col, :cont)>0 '||
3090: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3091: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
3092: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 3103: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

3099: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
3100: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
3101: ELSE
3102: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5'||
3103: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3104: ' WHERE contains( concat_col, :cont)>0'||
3105: ' AND EXISTS ('||
3106: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
3107: ' WHERE p.party_id = stage.party_id ' ||

Line 3120: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

3116: ,p_dup_party_id, p_dup_party_id;
3117: END IF;
3118: ELSE
3119: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5'||
3120: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3121: ' WHERE contains( concat_col, :cont)>0'||
3122: ' AND EXISTS ('||
3123: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
3124: ' WHERE p.party_id = stage.party_id ' ||