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

Line 2908: 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, TX3, TX4

2904: END IF;
2905: IF p_restrict_entity = 'CONTACTS'
2906: THEN
2907: OPEN x_cursor FOR
2908: 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, TX3, TX4
2909: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2910: WHERE contains( concat_col, p_contains_str)>0
2911: AND d.search_context_id = p_search_ctx_id
2912: AND( (l_search_merged ='Y' )

Line 2909: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2905: IF p_restrict_entity = 'CONTACTS'
2906: THEN
2907: OPEN x_cursor FOR
2908: 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, TX3, TX4
2909: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2910: WHERE contains( concat_col, p_contains_str)>0
2911: AND d.search_context_id = p_search_ctx_id
2912: AND( (l_search_merged ='Y' )
2913: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2920: 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, TX3, TX4

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

Line 2921: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

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

Line 2937: SELECT /+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) / CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4

2933: 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);
2934: END IF;
2935: IF p_search_rel_cpts = 'N' THEN
2936: OPEN x_cursor FOR
2937: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4
2938: FROM HZ_STAGED_CONTACT_POINTS stage
2939: WHERE contains( concat_col, p_contains_str)>0
2940: AND( (l_search_merged ='Y' )
2941: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2938: FROM HZ_STAGED_CONTACT_POINTS stage

2934: END IF;
2935: IF p_search_rel_cpts = 'N' THEN
2936: OPEN x_cursor FOR
2937: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4
2938: FROM HZ_STAGED_CONTACT_POINTS stage
2939: WHERE contains( concat_col, p_contains_str)>0
2940: AND( (l_search_merged ='Y' )
2941: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2942: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 2946: SELECT /+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) / CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4

2942: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2943: AND stage.party_id = p_party_id;
2944: ELSE
2945: OPEN x_cursor FOR
2946: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4
2947: FROM HZ_STAGED_CONTACT_POINTS stage
2948: WHERE contains( concat_col, p_contains_str)>0
2949: AND( (l_search_merged ='Y' )
2950: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2947: FROM HZ_STAGED_CONTACT_POINTS stage

2943: AND stage.party_id = p_party_id;
2944: ELSE
2945: OPEN x_cursor FOR
2946: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4
2947: FROM HZ_STAGED_CONTACT_POINTS stage
2948: WHERE contains( concat_col, p_contains_str)>0
2949: AND( (l_search_merged ='Y' )
2950: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2951: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 2954: 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, TX3, TX4

2950: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2951: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2952: AND stage.party_id = p_party_id
2953: UNION
2954: 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, TX3, TX4
2955: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2956: WHERE contains( concat_col, p_contains_str)>0
2957: AND r.object_id = p_party_id
2958: AND( (l_search_merged ='Y' )

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

2951: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2952: AND stage.party_id = p_party_id
2953: UNION
2954: 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, TX3, TX4
2955: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2956: WHERE contains( concat_col, p_contains_str)>0
2957: AND r.object_id = p_party_id
2958: AND( (l_search_merged ='Y' )
2959: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2977: FROM HZ_STAGED_CONTACT_POINTS stage

2973: 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);
2974: END IF;
2975: OPEN x_cursor FOR
2976: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4
2977: FROM HZ_STAGED_CONTACT_POINTS stage
2978: WHERE contains( concat_col, p_contains_str)>0
2979: AND EXISTS (
2980: SELECT 1 FROM HZ_STAGED_PARTIES p
2981: WHERE p.PARTY_ID = stage.PARTY_ID

Line 2997: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2993: END IF;
2994: IF p_person_api = 'Y' THEN
2995: OPEN x_cursor FOR
2996: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4
2997: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2998: WHERE contains( concat_col, p_contains_str)>0
2999: AND d.search_context_id = p_search_ctx_id
3000: AND d.party_id = stage.party_id
3001: AND( (l_search_merged ='Y' )

Line 3006: 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, TX3, TX4

3002: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
3003: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
3004: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
3005: UNION
3006: 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, TX3, TX4
3007: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
3008: WHERE contains( concat_col, p_contains_str)>0
3009: AND d.search_context_id = p_search_ctx_id
3010: AND d.party_id = r.subject_id

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

3003: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
3004: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
3005: UNION
3006: 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, TX3, TX4
3007: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
3008: WHERE contains( concat_col, p_contains_str)>0
3009: AND d.search_context_id = p_search_ctx_id
3010: AND d.party_id = r.subject_id
3011: AND r.relationship_id = oc.party_relationship_id

Line 3020: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

3016: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
3017: ELSE
3018: OPEN x_cursor FOR
3019: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4
3020: FROM HZ_DQM_PARTIES_GT d, 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 = stage.party_id
3024: AND( (l_search_merged ='Y' )

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

3036: IF (l_check_dt > 0 ) THEN
3037: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3038: 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);
3039: END IF;
3040: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) */';
3041: ELSIF (l_check > 0 ) THEN
3042: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3043: 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);
3044: END IF;

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

3041: ELSIF (l_check > 0 ) THEN
3042: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3043: 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);
3044: END IF;
3045: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
3046: END IF;
3047: IF l_search_merged = 'Y' THEN
3048: l_status_sql := ' ' ;
3049: ELSIF l_search_merged = 'I' THEN

Line 3064: 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, TX3, TX4'||

3060: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
3061: ELSE
3062: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
3063: END IF;
3064: 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, TX3, TX4'||
3065: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3066: ' WHERE contains( concat_col, :cont)>0 '||
3067: ' AND (stage.org_contact_id is null '||
3068: ' AND( ('''||l_search_merged||''' =''Y'' ) '||

Line 3065: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

3061: ELSE
3062: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
3063: END IF;
3064: 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, TX3, TX4'||
3065: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3066: ' WHERE contains( concat_col, :cont)>0 '||
3067: ' AND (stage.org_contact_id is null '||
3068: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3069: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 3074: ' SELECT /+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) / CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4'||

3070: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
3071: ' AND ('||p_restrict_sql||'))' ||
3072: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
3073: ' UNION ' ||
3074: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4'||
3075: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3076: ' WHERE contains( concat_col, :cont)>0 '||
3077: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3078: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 3075: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

3071: ' AND ('||p_restrict_sql||'))' ||
3072: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
3073: ' UNION ' ||
3074: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4'||
3075: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3076: ' WHERE contains( concat_col, :cont)>0 '||
3077: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3078: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
3079: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 3090: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

3086: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
3087: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
3088: ELSE
3089: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4'||
3090: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3091: ' WHERE contains( concat_col, :cont)>0'||
3092: ' AND EXISTS ('||
3093: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
3094: ' WHERE p.party_id = stage.party_id ' ||

Line 3107: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

3103: ,p_dup_party_id, p_dup_party_id;
3104: END IF;
3105: ELSE
3106: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4'||
3107: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3108: ' WHERE contains( concat_col, :cont)>0'||
3109: ' AND EXISTS ('||
3110: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
3111: ' WHERE p.party_id = stage.party_id ' ||