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_60 dependencies on HZ_STAGED_PARTIES

Line 1710: FROM hz_staged_parties

1706: 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);
1707: END IF;
1708: OPEN x_cursor FOR
1709: SELECT PARTY_ID , TX34, TX35, TX40, TX39, TX45
1710: FROM hz_staged_parties
1711: WHERE TX8 LIKE g_party_stage_rec.TX8||' %'
1712: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = TX36))
1713: AND( (l_search_merged ='Y' )
1714: OR (l_search_merged = 'I' AND nvl(status, 'A') in ('A', 'I'))

Line 1721: l_sqlstr := 'SELECT PARTY_ID , TX34, TX35, TX40, TX39, TX45 FROM hz_staged_parties stage ';

1717: ELSE
1718: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1719: 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);
1720: END IF;
1721: l_sqlstr := 'SELECT PARTY_ID , TX34, TX35, TX40, TX39, TX45 FROM hz_staged_parties stage ';
1722: l_sqlstr := l_sqlstr || ' WHERE TX8 like :TX8||'' %'' ';
1723: l_sqlstr := l_sqlstr || ' AND (:TX36 IS NULL OR :TX36||'' '' = TX36) ';
1724: IF l_search_merged = 'N' THEN
1725: l_sqlstr := l_sqlstr || ' AND nvl(status,''A'')=''A'' ';

Line 1873: FROM HZ_STAGED_PARTIES stage

1869: 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);
1870: END IF;
1871: OPEN x_cursor FOR
1872: SELECT PARTY_ID , TX34, TX35, TX40, TX39, TX45
1873: FROM HZ_STAGED_PARTIES stage
1874: WHERE PARTY_ID = p_dup_party_id;
1875: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN
1876: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1877: 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 1885: FROM HZ_STAGED_PARTIES stage

1881: 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);
1882: END IF;
1883: OPEN x_cursor FOR
1884: SELECT PARTY_ID , TX34, TX35, TX40, TX39, TX45
1885: FROM HZ_STAGED_PARTIES stage
1886: WHERE contains( concat_col, p_contains_str)>0
1887: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1888: AND( (l_search_merged ='Y' )
1889: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))

Line 1897: SELECT /+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) / stage.PARTY_ID , TX34, TX35, TX40, TX39, TX45

1893: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1894: 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);
1895: END IF;
1896: OPEN x_cursor FOR
1897: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX34, TX35, TX40, TX39, TX45
1898: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1899: WHERE contains( concat_col, p_contains_str)>0
1900: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1901: AND d.party_id = stage.party_id

Line 1898: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage

1894: 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);
1895: END IF;
1896: OPEN x_cursor FOR
1897: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX34, TX35, TX40, TX39, TX45
1898: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1899: WHERE contains( concat_col, p_contains_str)>0
1900: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1901: AND d.party_id = stage.party_id
1902: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))

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

1913: IF (l_check > 0 ) THEN
1914: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1915: 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);
1916: END IF;
1917: l_hint := '/*+ INDEX(stage HZ_STAGED_PARTIES_U1) */';
1918: END IF;
1919: IF p_search_ctx_id IS NULL THEN
1920: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX34, TX35, TX40, TX39, TX45'||
1921: ' FROM HZ_STAGED_PARTIES stage'||

Line 1921: ' FROM HZ_STAGED_PARTIES stage'||

1917: l_hint := '/*+ INDEX(stage HZ_STAGED_PARTIES_U1) */';
1918: END IF;
1919: IF p_search_ctx_id IS NULL THEN
1920: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX34, TX35, TX40, TX39, TX45'||
1921: ' FROM HZ_STAGED_PARTIES stage'||
1922: ' WHERE contains( concat_col, :cont)>0'||
1923: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1924: ' AND ('||p_restrict_sql||')' ||
1925: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';

Line 2040: SELECT 1 FROM HZ_STAGED_PARTIES p

2036: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
2037: FROM HZ_STAGED_PARTY_SITES stage
2038: WHERE contains( concat_col, p_contains_str)>0
2039: AND EXISTS (
2040: SELECT 1 FROM HZ_STAGED_PARTIES p
2041: WHERE p.PARTY_ID = stage.PARTY_ID
2042: AND( (l_search_merged ='Y' )
2043: OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
2044: OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )

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

2152: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2153: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2154: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2155: ' AND EXISTS ('||
2156: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2157: ' WHERE p.party_id = stage.party_id ' ||
2158: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2159: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2160: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';

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

2169: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2170: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2171: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2172: ' AND EXISTS ('||
2173: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2174: ' WHERE p.party_id = stage.party_id ' ||
2175: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2176: ' AND ('||p_restrict_sql||')' ||
2177: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';

Line 2242: SELECT 1 FROM HZ_STAGED_PARTIES p

2238: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID, TX22
2239: FROM HZ_STAGED_CONTACTS stage
2240: WHERE contains( concat_col, p_contains_str)>0
2241: AND EXISTS (
2242: SELECT 1 FROM HZ_STAGED_PARTIES p
2243: WHERE p.PARTY_ID = stage.PARTY_ID
2244: AND( (l_search_merged ='Y' )
2245: OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
2246: OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )

Line 2265: SELECT 1 FROM HZ_STAGED_PARTIES p

2261: SELECT ORG_CONTACT_ID, PARTY_ID, TX22
2262: FROM HZ_STAGED_CONTACTS stage
2263: WHERE contains( concat_col, p_contains_str)>0
2264: AND EXISTS (
2265: SELECT 1 FROM HZ_STAGED_PARTIES p
2266: WHERE p.PARTY_ID = stage.PARTY_ID
2267: AND( (l_search_merged ='Y' )
2268: OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
2269: OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )

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

2314: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID , TX22'||
2315: ' FROM HZ_STAGED_CONTACTS stage'||
2316: ' WHERE contains( concat_col, :cont)>0'||
2317: ' AND EXISTS ('||
2318: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2319: ' WHERE p.party_id = stage.party_id ' ||
2320: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2321: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2322: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2459: SELECT 1 FROM HZ_STAGED_PARTIES p

2455: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
2456: FROM HZ_STAGED_CONTACT_POINTS stage
2457: WHERE contains( concat_col, p_contains_str)>0
2458: AND EXISTS (
2459: SELECT 1 FROM HZ_STAGED_PARTIES p
2460: WHERE p.PARTY_ID = stage.PARTY_ID
2461: AND( (l_search_merged ='Y' )
2462: OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
2463: OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )

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

2568: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2569: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2570: ' WHERE contains( concat_col, :cont)>0'||
2571: ' AND EXISTS ('||
2572: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2573: ' WHERE p.party_id = stage.party_id ' ||
2574: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2575: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2576: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

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

2585: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2586: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2587: ' WHERE contains( concat_col, :cont)>0'||
2588: ' AND EXISTS ('||
2589: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2590: ' WHERE p.party_id = stage.party_id ' ||
2591: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2592: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2593: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2641: FROM hz_staged_parties

2637: END IF;
2638: IF p_restrict_sql IS NULL THEN
2639: INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE)
2640: SELECT p_search_ctx_id, PARTY_ID, decode(TX8,g_party_stage_rec.TX8||' ',100,90)
2641: FROM hz_staged_parties
2642: WHERE TX8 LIKE g_party_stage_rec.TX8||' %'
2643: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = TX36))
2644: AND( (l_search_merged ='Y' )
2645: OR (l_search_merged = 'I' AND nvl(status, 'A') in ('A', 'I'))

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

2646: OR (l_search_merged = 'N' AND nvl(status, 'A') in ('A')))
2647: AND (p_dup_party_id IS NULL OR party_id <> p_dup_party_id)
2648: AND rownum <= p_thresh;
2649: ELSE
2650: 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 ';
2651: l_sqlstr := l_sqlstr || ' WHERE TX8 like :TX8||'' %'' ';
2652: l_sqlstr := l_sqlstr || ' AND (:TX36 IS NULL OR :TX36||'' '' = TX36) ';
2653: IF l_search_merged = 'N' THEN
2654: l_sqlstr := l_sqlstr || ' AND nvl(status,''A'')=''A'' ';