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 /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ 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: ELSE
2438: l_hint := '/*+ INDEX(stage HZ_STAGE_PARTIES_T1) */';
2439: END IF;
2440: IF p_search_ctx_id IS NULL THEN

Line 2442: ' FROM HZ_STAGED_PARTIES stage'||

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

Line 2561: SELECT 1 FROM HZ_STAGED_PARTIES p

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

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

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

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

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

Line 2763: SELECT 1 FROM HZ_STAGED_PARTIES p

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

Line 2786: SELECT 1 FROM HZ_STAGED_PARTIES p

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

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

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

Line 2980: SELECT 1 FROM HZ_STAGED_PARTIES p

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
2982: AND( (l_search_merged ='Y' )
2983: OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
2984: OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )

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

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 ' ||
3095: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
3096: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3097: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

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

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 ' ||
3112: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
3113: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3114: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 3162: FROM hz_staged_parties

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

Line 3171: 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 ';

3167: OR (l_search_merged = 'N' AND nvl(status, 'A') in ('A')))
3168: AND (p_dup_party_id IS NULL OR party_id <> p_dup_party_id)
3169: AND rownum <= p_thresh;
3170: ELSE
3171: 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 ';
3172: l_sqlstr := l_sqlstr || ' WHERE TX8 like :TX8||'' %'' ';
3173: l_sqlstr := l_sqlstr || ' AND (:TX36 IS NULL OR :TX36||'' '' = TX36) ';
3174: IF l_search_merged = 'N' THEN
3175: l_sqlstr := l_sqlstr || ' AND nvl(status,''A'')=''A'' ';