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_PARTIES

Line 2166: FROM hz_staged_parties

2162: hz_utility_v2pub.debug(p_message=>'Restrict SQL is NULL and other conditions met to OPEN x_cursor',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2163: END IF;
2164: OPEN x_cursor FOR
2165: SELECT PARTY_ID , TX32, TX34, TX35, TX39, TX158, TX40, TX43, TX42, TX44, TX46, TX47, TX48, TX156, TX157
2166: FROM hz_staged_parties
2167: WHERE TX8 LIKE g_party_stage_rec.TX8||' %'
2168: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = TX36))
2169: AND( (l_search_merged ='Y' )
2170: OR (l_search_merged = 'I' AND nvl(status, 'A') in ('A', 'I'))

Line 2177: l_sqlstr := 'SELECT PARTY_ID , TX32, TX34, TX35, TX39, TX158, TX40, TX43, TX42, TX44, TX46, TX47, TX48, TX156, TX157 FROM hz_staged_parties stage ';

2173: ELSE
2174: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2175: hz_utility_v2pub.debug(p_message=>'Restrict SQL is NOT NULL OR other conditions not met, Else Part',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2176: END IF;
2177: l_sqlstr := 'SELECT PARTY_ID , TX32, TX34, TX35, TX39, TX158, TX40, TX43, TX42, TX44, TX46, TX47, TX48, TX156, TX157 FROM hz_staged_parties stage ';
2178: l_sqlstr := l_sqlstr || ' WHERE TX8 like :TX8||'' %'' ';
2179: l_sqlstr := l_sqlstr || ' AND (:TX36 IS NULL OR :TX36||'' '' = TX36) ';
2180: IF l_search_merged = 'N' THEN
2181: l_sqlstr := l_sqlstr || ' AND nvl(status,''A'')=''A'' ';

Line 2392: FROM HZ_STAGED_PARTIES stage

2388: hz_utility_v2pub.debug(p_message=>'part contains string is null',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2389: END IF;
2390: OPEN x_cursor FOR
2391: SELECT PARTY_ID , TX32, TX34, TX35, TX39, TX158, TX40, TX43, TX42, TX44, TX46, TX47, TX48, TX156, TX157
2392: FROM HZ_STAGED_PARTIES stage
2393: WHERE PARTY_ID = p_dup_party_id;
2394: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN
2395: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2396: hz_utility_v2pub.debug(p_message=>'Either restrict sql is null or search context id is not null',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line 2404: FROM HZ_STAGED_PARTIES stage

2400: hz_utility_v2pub.debug(p_message=>'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);
2401: END IF;
2402: OPEN x_cursor FOR
2403: SELECT PARTY_ID , TX32, TX34, TX35, TX39, TX158, TX40, TX43, TX42, TX44, TX46, TX47, TX48, TX156, TX157
2404: FROM HZ_STAGED_PARTIES stage
2405: WHERE contains( concat_col, p_contains_str)>0
2406: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
2407: AND( (l_search_merged ='Y' )
2408: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))

Line 2416: SELECT /+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) / stage.PARTY_ID , TX32, TX34, TX35, TX39, TX158, TX40, TX43, TX42, TX44, TX46, TX47, TX48, TX156, TX157

2412: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2413: hz_utility_v2pub.debug(p_message=>'Search context id is not null',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2414: END IF;
2415: OPEN x_cursor FOR
2416: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX32, TX34, TX35, TX39, TX158, TX40, TX43, TX42, TX44, TX46, TX47, TX48, TX156, TX157
2417: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
2418: WHERE contains( concat_col, p_contains_str)>0
2419: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
2420: AND d.party_id = stage.party_id

Line 2417: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage

2413: hz_utility_v2pub.debug(p_message=>'Search context id is not null',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2414: END IF;
2415: OPEN x_cursor FOR
2416: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX32, TX34, TX35, TX39, TX158, TX40, TX43, TX42, TX44, TX46, TX47, TX48, TX156, TX157
2417: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
2418: WHERE contains( concat_col, p_contains_str)>0
2419: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
2420: AND d.party_id = stage.party_id
2421: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))

Line 2436: l_hint := '/+ INDEX(stage HZ_STAGED_PARTIES_U1) /';

2432: IF (l_check > 0 ) THEN
2433: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2434: hz_utility_v2pub.debug(p_message=>'Restrict sql has a Selective Hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2435: END IF;
2436: l_hint := '/*+ INDEX(stage HZ_STAGED_PARTIES_U1) */';
2437: END IF;
2438: IF p_search_ctx_id IS NULL THEN
2439: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX32, TX34, TX35, TX39, TX158, TX40, TX43, TX42, TX44, TX46, TX47, TX48, TX156, TX157'||
2440: ' FROM HZ_STAGED_PARTIES stage'||

Line 2440: ' FROM HZ_STAGED_PARTIES stage'||

2436: l_hint := '/*+ INDEX(stage HZ_STAGED_PARTIES_U1) */';
2437: END IF;
2438: IF p_search_ctx_id IS NULL THEN
2439: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX32, TX34, TX35, TX39, TX158, TX40, TX43, TX42, TX44, TX46, TX47, TX48, TX156, TX157'||
2440: ' FROM HZ_STAGED_PARTIES stage'||
2441: ' WHERE contains( concat_col, :cont)>0'||
2442: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
2443: ' AND ('||p_restrict_sql||')' ||
2444: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';

Line 2559: SELECT 1 FROM HZ_STAGED_PARTIES p

2555: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX9, TX10, TX11, TX13, TX14, TX15, TX20, TX21, TX22
2556: FROM HZ_STAGED_PARTY_SITES stage
2557: WHERE contains( concat_col, p_contains_str)>0
2558: AND EXISTS (
2559: SELECT 1 FROM HZ_STAGED_PARTIES p
2560: WHERE p.PARTY_ID = stage.PARTY_ID
2561: AND( (l_search_merged ='Y' )
2562: OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
2563: OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )

Line 2675: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||

2671: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2672: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2673: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2674: ' AND EXISTS ('||
2675: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2676: ' WHERE p.party_id = stage.party_id ' ||
2677: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2678: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2679: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';

Line 2692: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||

2688: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2689: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2690: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2691: ' AND EXISTS ('||
2692: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2693: ' WHERE p.party_id = stage.party_id ' ||
2694: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2695: ' AND ('||p_restrict_sql||')' ||
2696: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';

Line 2761: SELECT 1 FROM HZ_STAGED_PARTIES p

2757: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
2758: FROM HZ_STAGED_CONTACTS stage
2759: WHERE contains( concat_col, p_contains_str)>0
2760: AND EXISTS (
2761: SELECT 1 FROM HZ_STAGED_PARTIES p
2762: WHERE p.PARTY_ID = stage.PARTY_ID
2763: AND( (l_search_merged ='Y' )
2764: OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
2765: OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )

Line 2784: SELECT 1 FROM HZ_STAGED_PARTIES p

2780: SELECT ORG_CONTACT_ID, PARTY_ID
2781: FROM HZ_STAGED_CONTACTS stage
2782: WHERE contains( concat_col, p_contains_str)>0
2783: AND EXISTS (
2784: SELECT 1 FROM HZ_STAGED_PARTIES p
2785: WHERE p.PARTY_ID = stage.PARTY_ID
2786: AND( (l_search_merged ='Y' )
2787: OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
2788: OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )

Line 2837: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||

2833: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
2834: ' FROM HZ_STAGED_CONTACTS stage'||
2835: ' WHERE contains( concat_col, :cont)>0'||
2836: ' AND EXISTS ('||
2837: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2838: ' WHERE p.party_id = stage.party_id ' ||
2839: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2840: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2841: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2978: SELECT 1 FROM HZ_STAGED_PARTIES p

2974: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4
2975: FROM HZ_STAGED_CONTACT_POINTS stage
2976: WHERE contains( concat_col, p_contains_str)>0
2977: AND EXISTS (
2978: SELECT 1 FROM HZ_STAGED_PARTIES p
2979: WHERE p.PARTY_ID = stage.PARTY_ID
2980: AND( (l_search_merged ='Y' )
2981: OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
2982: OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )

Line 3091: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||

3087: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4'||
3088: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3089: ' WHERE contains( concat_col, :cont)>0'||
3090: ' AND EXISTS ('||
3091: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
3092: ' WHERE p.party_id = stage.party_id ' ||
3093: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
3094: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3095: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 3108: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||

3104: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX3, TX4'||
3105: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3106: ' WHERE contains( concat_col, :cont)>0'||
3107: ' AND EXISTS ('||
3108: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
3109: ' WHERE p.party_id = stage.party_id ' ||
3110: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
3111: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3112: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 3160: FROM hz_staged_parties

3156: END IF;
3157: IF p_restrict_sql IS NULL THEN
3158: INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE)
3159: SELECT p_search_ctx_id, PARTY_ID, decode(TX8,g_party_stage_rec.TX8||' ',100,90)
3160: FROM hz_staged_parties
3161: WHERE TX8 LIKE g_party_stage_rec.TX8||' %'
3162: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = TX36))
3163: AND( (l_search_merged ='Y' )
3164: OR (l_search_merged = 'I' AND nvl(status, 'A') in ('A', 'I'))

Line 3169: l_sqlstr := 'INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) SELECT :ctx_id, PARTY_ID, decode(TX8,:TX8||'' '',100,90) FROM hz_staged_parties stage ';

3165: OR (l_search_merged = 'N' AND nvl(status, 'A') in ('A')))
3166: AND (p_dup_party_id IS NULL OR party_id <> p_dup_party_id)
3167: AND rownum <= p_thresh;
3168: ELSE
3169: l_sqlstr := 'INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) SELECT :ctx_id, PARTY_ID, decode(TX8,:TX8||'' '',100,90) FROM hz_staged_parties stage ';
3170: l_sqlstr := l_sqlstr || ' WHERE TX8 like :TX8||'' %'' ';
3171: l_sqlstr := l_sqlstr || ' AND (:TX36 IS NULL OR :TX36||'' '' = TX36) ';
3172: IF l_search_merged = 'N' THEN
3173: l_sqlstr := l_sqlstr || ' AND nvl(status,''A'')=''A'' ';