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_PARTIES

Line 2189: FROM hz_staged_parties

2185: 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);
2186: END IF;
2187: OPEN x_cursor FOR
2188: SELECT PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157
2189: FROM hz_staged_parties
2190: WHERE TX8 LIKE g_party_stage_rec.TX8||' %'
2191: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = TX36))
2192: AND( (l_search_merged ='Y' )
2193: OR (l_search_merged = 'I' AND nvl(status, 'A') in ('A', 'I'))

Line 2200: l_sqlstr := 'SELECT PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157 FROM hz_staged_parties stage ';

2196: ELSE
2197: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2198: 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);
2199: END IF;
2200: l_sqlstr := 'SELECT PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157 FROM hz_staged_parties stage ';
2201: l_sqlstr := l_sqlstr || ' WHERE TX8 like :TX8||'' %'' ';
2202: l_sqlstr := l_sqlstr || ' AND (:TX36 IS NULL OR :TX36||'' '' = TX36) ';
2203: IF l_search_merged = 'N' THEN
2204: l_sqlstr := l_sqlstr || ' AND nvl(status,''A'')=''A'' ';

Line 2405: FROM HZ_STAGED_PARTIES stage

2401: 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);
2402: END IF;
2403: OPEN x_cursor FOR
2404: SELECT PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157
2405: FROM HZ_STAGED_PARTIES stage
2406: WHERE PARTY_ID = p_dup_party_id;
2407: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN
2408: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2409: 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 2417: FROM HZ_STAGED_PARTIES stage

2413: 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);
2414: END IF;
2415: OPEN x_cursor FOR
2416: SELECT PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157
2417: FROM HZ_STAGED_PARTIES stage
2418: WHERE contains( concat_col, p_contains_str)>0
2419: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
2420: AND( (l_search_merged ='Y' )
2421: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))

Line 2429: SELECT /+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) / stage.PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157

2425: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2426: 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);
2427: END IF;
2428: OPEN x_cursor FOR
2429: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157
2430: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
2431: WHERE contains( concat_col, p_contains_str)>0
2432: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
2433: AND d.party_id = stage.party_id

Line 2430: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage

2426: 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);
2427: END IF;
2428: OPEN x_cursor FOR
2429: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157
2430: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
2431: WHERE contains( concat_col, p_contains_str)>0
2432: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
2433: AND d.party_id = stage.party_id
2434: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))

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

2445: IF (l_check > 0 ) THEN
2446: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2447: 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);
2448: END IF;
2449: l_hint := '/*+ INDEX(stage HZ_STAGED_PARTIES_U1) */';
2450: END IF;
2451: IF p_search_ctx_id IS NULL THEN
2452: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157'||
2453: ' FROM HZ_STAGED_PARTIES stage'||

Line 2453: ' FROM HZ_STAGED_PARTIES stage'||

2449: l_hint := '/*+ INDEX(stage HZ_STAGED_PARTIES_U1) */';
2450: END IF;
2451: IF p_search_ctx_id IS NULL THEN
2452: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157'||
2453: ' FROM HZ_STAGED_PARTIES stage'||
2454: ' WHERE contains( concat_col, :cont)>0'||
2455: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
2456: ' AND ('||p_restrict_sql||')' ||
2457: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';

Line 2572: SELECT 1 FROM HZ_STAGED_PARTIES p

2568: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX11, TX12, TX14, TX20, TX22
2569: FROM HZ_STAGED_PARTY_SITES stage
2570: WHERE contains( concat_col, p_contains_str)>0
2571: AND EXISTS (
2572: SELECT 1 FROM HZ_STAGED_PARTIES p
2573: WHERE p.PARTY_ID = stage.PARTY_ID
2574: AND( (l_search_merged ='Y' )
2575: OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
2576: OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )

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

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

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

2701: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2702: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2703: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2704: ' AND EXISTS ('||
2705: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2706: ' WHERE p.party_id = stage.party_id ' ||
2707: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2708: ' AND ('||p_restrict_sql||')' ||
2709: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';

Line 2774: SELECT 1 FROM HZ_STAGED_PARTIES p

2770: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID, TX5, TX2
2771: FROM HZ_STAGED_CONTACTS stage
2772: WHERE contains( concat_col, p_contains_str)>0
2773: AND EXISTS (
2774: SELECT 1 FROM HZ_STAGED_PARTIES p
2775: WHERE p.PARTY_ID = stage.PARTY_ID
2776: AND( (l_search_merged ='Y' )
2777: OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
2778: OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )

Line 2797: SELECT 1 FROM HZ_STAGED_PARTIES p

2793: SELECT ORG_CONTACT_ID, PARTY_ID, TX5, TX2
2794: FROM HZ_STAGED_CONTACTS stage
2795: WHERE contains( concat_col, p_contains_str)>0
2796: AND EXISTS (
2797: SELECT 1 FROM HZ_STAGED_PARTIES p
2798: WHERE p.PARTY_ID = stage.PARTY_ID
2799: AND( (l_search_merged ='Y' )
2800: OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
2801: OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )

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

2846: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID , TX5, TX2'||
2847: ' FROM HZ_STAGED_CONTACTS stage'||
2848: ' WHERE contains( concat_col, :cont)>0'||
2849: ' AND EXISTS ('||
2850: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2851: ' WHERE p.party_id = stage.party_id ' ||
2852: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2853: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2854: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2991: SELECT 1 FROM HZ_STAGED_PARTIES p

2987: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5
2988: FROM HZ_STAGED_CONTACT_POINTS stage
2989: WHERE contains( concat_col, p_contains_str)>0
2990: AND EXISTS (
2991: SELECT 1 FROM HZ_STAGED_PARTIES p
2992: WHERE p.PARTY_ID = stage.PARTY_ID
2993: AND( (l_search_merged ='Y' )
2994: OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
2995: OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )

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

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

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

3117: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5'||
3118: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
3119: ' WHERE contains( concat_col, :cont)>0'||
3120: ' AND EXISTS ('||
3121: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
3122: ' WHERE p.party_id = stage.party_id ' ||
3123: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
3124: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
3125: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 3173: FROM hz_staged_parties

3169: END IF;
3170: IF p_restrict_sql IS NULL THEN
3171: INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE)
3172: SELECT p_search_ctx_id, PARTY_ID, decode(TX8,g_party_stage_rec.TX8||' ',100,90)
3173: FROM hz_staged_parties
3174: WHERE TX8 LIKE g_party_stage_rec.TX8||' %'
3175: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = TX36))
3176: AND( (l_search_merged ='Y' )
3177: OR (l_search_merged = 'I' AND nvl(status, 'A') in ('A', 'I'))

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

3178: OR (l_search_merged = 'N' AND nvl(status, 'A') in ('A')))
3179: AND (p_dup_party_id IS NULL OR party_id <> p_dup_party_id)
3180: AND rownum <= p_thresh;
3181: ELSE
3182: 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 ';
3183: l_sqlstr := l_sqlstr || ' WHERE TX8 like :TX8||'' %'' ';
3184: l_sqlstr := l_sqlstr || ' AND (:TX36 IS NULL OR :TX36||'' '' = TX36) ';
3185: IF l_search_merged = 'N' THEN
3186: l_sqlstr := l_sqlstr || ' AND nvl(status,''A'')=''A'' ';