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

Line 1255: FROM hz_staged_parties

1251: 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);
1252: END IF;
1253: OPEN x_cursor FOR
1254: SELECT PARTY_ID
1255: FROM hz_staged_parties
1256: WHERE TX8 LIKE g_party_stage_rec.TX8||' %'
1257: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = TX36))
1258: AND( (l_search_merged ='Y' )
1259: OR (l_search_merged = 'I' AND nvl(status, 'A') in ('A', 'I'))

Line 1266: l_sqlstr := 'SELECT PARTY_ID FROM hz_staged_parties stage ';

1262: ELSE
1263: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1264: 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);
1265: END IF;
1266: l_sqlstr := 'SELECT PARTY_ID FROM hz_staged_parties stage ';
1267: l_sqlstr := l_sqlstr || ' WHERE TX8 like :TX8||'' %'' ';
1268: l_sqlstr := l_sqlstr || ' AND (:TX36 IS NULL OR :TX36||'' '' = TX36) ';
1269: IF l_search_merged = 'N' THEN
1270: l_sqlstr := l_sqlstr || ' AND nvl(status,''A'')=''A'' ';

Line 1394: FROM HZ_STAGED_PARTIES stage

1390: 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);
1391: END IF;
1392: OPEN x_cursor FOR
1393: SELECT PARTY_ID
1394: FROM HZ_STAGED_PARTIES stage
1395: WHERE PARTY_ID = p_dup_party_id;
1396: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN
1397: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1398: 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 1406: FROM HZ_STAGED_PARTIES stage

1402: 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);
1403: END IF;
1404: OPEN x_cursor FOR
1405: SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID
1406: FROM HZ_STAGED_PARTIES stage
1407: WHERE contains( concat_col, p_contains_str)>0
1408: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1409: AND( (l_search_merged ='Y' )
1410: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))

Line 1418: SELECT /+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) / stage.PARTY_ID

1414: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1415: 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);
1416: END IF;
1417: OPEN x_cursor FOR
1418: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID
1419: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1420: WHERE contains( concat_col, p_contains_str)>0
1421: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1422: AND d.party_id = stage.party_id

Line 1419: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage

1415: 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);
1416: END IF;
1417: OPEN x_cursor FOR
1418: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID
1419: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1420: WHERE contains( concat_col, p_contains_str)>0
1421: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1422: AND d.party_id = stage.party_id
1423: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))

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

1434: IF (l_check > 0 ) THEN
1435: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1436: 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);
1437: END IF;
1438: l_hint := '/*+ INDEX(stage HZ_STAGED_PARTIES_U1) */';
1439: ELSE
1440: l_hint := '/*+ INDEX(stage HZ_STAGE_PARTIES_T1) */';
1441: END IF;
1442: IF p_search_ctx_id IS NULL THEN

Line 1444: ' FROM HZ_STAGED_PARTIES stage'||

1440: l_hint := '/*+ INDEX(stage HZ_STAGE_PARTIES_T1) */';
1441: END IF;
1442: IF p_search_ctx_id IS NULL THEN
1443: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID '||
1444: ' FROM HZ_STAGED_PARTIES stage'||
1445: ' WHERE contains( concat_col, :cont)>0'||
1446: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1447: ' AND ('||p_restrict_sql||')' ||
1448: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';

Line 1563: SELECT 1 FROM HZ_STAGED_PARTIES p

1559: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1560: FROM HZ_STAGED_PARTY_SITES stage
1561: WHERE contains( concat_col, p_contains_str)>0
1562: AND EXISTS (
1563: SELECT 1 FROM HZ_STAGED_PARTIES p
1564: WHERE p.PARTY_ID = stage.PARTY_ID
1565: AND( (l_search_merged ='Y' )
1566: OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
1567: OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )

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

1675: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1676: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1677: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1678: ' AND EXISTS ('||
1679: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1680: ' WHERE p.party_id = stage.party_id ' ||
1681: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1682: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1683: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';

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

1692: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1693: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1694: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1695: ' AND EXISTS ('||
1696: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1697: ' WHERE p.party_id = stage.party_id ' ||
1698: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1699: ' AND ('||p_restrict_sql||')' ||
1700: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';

Line 1765: SELECT 1 FROM HZ_STAGED_PARTIES p

1761: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1762: FROM HZ_STAGED_CONTACTS stage
1763: WHERE contains( concat_col, p_contains_str)>0
1764: AND EXISTS (
1765: SELECT 1 FROM HZ_STAGED_PARTIES p
1766: WHERE p.PARTY_ID = stage.PARTY_ID
1767: AND( (l_search_merged ='Y' )
1768: OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
1769: OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )

Line 1788: SELECT 1 FROM HZ_STAGED_PARTIES p

1784: SELECT ORG_CONTACT_ID, PARTY_ID
1785: FROM HZ_STAGED_CONTACTS stage
1786: WHERE contains( concat_col, p_contains_str)>0
1787: AND EXISTS (
1788: SELECT 1 FROM HZ_STAGED_PARTIES p
1789: WHERE p.PARTY_ID = stage.PARTY_ID
1790: AND( (l_search_merged ='Y' )
1791: OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
1792: OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )

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

1837: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
1838: ' FROM HZ_STAGED_CONTACTS stage'||
1839: ' WHERE contains( concat_col, :cont)>0'||
1840: ' AND EXISTS ('||
1841: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1842: ' WHERE p.party_id = stage.party_id ' ||
1843: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1844: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1845: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 1982: SELECT 1 FROM HZ_STAGED_PARTIES p

1978: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1979: FROM HZ_STAGED_CONTACT_POINTS stage
1980: WHERE contains( concat_col, p_contains_str)>0
1981: AND EXISTS (
1982: SELECT 1 FROM HZ_STAGED_PARTIES p
1983: WHERE p.PARTY_ID = stage.PARTY_ID
1984: AND( (l_search_merged ='Y' )
1985: OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
1986: OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )

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

2091: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2092: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2093: ' WHERE contains( concat_col, :cont)>0'||
2094: ' AND EXISTS ('||
2095: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2096: ' WHERE p.party_id = stage.party_id ' ||
2097: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2098: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2099: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

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

2108: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2109: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2110: ' WHERE contains( concat_col, :cont)>0'||
2111: ' AND EXISTS ('||
2112: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2113: ' WHERE p.party_id = stage.party_id ' ||
2114: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2115: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2116: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2164: FROM hz_staged_parties

2160: END IF;
2161: IF p_restrict_sql IS NULL THEN
2162: INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE)
2163: SELECT p_search_ctx_id, PARTY_ID, 0
2164: FROM hz_staged_parties
2165: WHERE TX8 LIKE g_party_stage_rec.TX8||' %'
2166: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = TX36))
2167: AND( (l_search_merged ='Y' )
2168: OR (l_search_merged = 'I' AND nvl(status, 'A') in ('A', 'I'))

Line 2173: l_sqlstr := 'INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) SELECT :ctx_id, PARTY_ID, 0 FROM hz_staged_parties stage ';

2169: OR (l_search_merged = 'N' AND nvl(status, 'A') in ('A')))
2170: AND (p_dup_party_id IS NULL OR party_id <> p_dup_party_id)
2171: AND rownum <= p_thresh;
2172: ELSE
2173: l_sqlstr := 'INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) SELECT :ctx_id, PARTY_ID, 0 FROM hz_staged_parties stage ';
2174: l_sqlstr := l_sqlstr || ' WHERE TX8 like :TX8||'' %'' ';
2175: l_sqlstr := l_sqlstr || ' AND (:TX36 IS NULL OR :TX36||'' '' = TX36) ';
2176: IF l_search_merged = 'N' THEN
2177: l_sqlstr := l_sqlstr || ' AND nvl(status,''A'')=''A'' ';