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_2 dependencies on HZ_STAGED_PARTY_SITES

Line 1295: SELECT /+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) / PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3

1291: hz_utility_v2pub.debug(p_message=>'Single Party Scenario',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1292: END IF;
1293: IF p_search_rel_sites = 'N' THEN
1294: OPEN x_cursor FOR
1295: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3
1296: FROM HZ_STAGED_PARTY_SITES stage
1297: WHERE contains( concat_col, p_contains_str)>0
1298: AND( (l_search_merged ='Y' )
1299: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1296: FROM HZ_STAGED_PARTY_SITES stage

1292: END IF;
1293: IF p_search_rel_sites = 'N' THEN
1294: OPEN x_cursor FOR
1295: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3
1296: FROM HZ_STAGED_PARTY_SITES stage
1297: WHERE contains( concat_col, p_contains_str)>0
1298: AND( (l_search_merged ='Y' )
1299: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1300: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1304: SELECT /+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) / PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3

1300: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1301: AND stage.party_id = p_party_id;
1302: ELSE
1303: OPEN x_cursor FOR
1304: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3
1305: FROM HZ_STAGED_PARTY_SITES stage
1306: WHERE contains( concat_col, p_contains_str)>0
1307: AND( (l_search_merged ='Y' )
1308: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1305: FROM HZ_STAGED_PARTY_SITES stage

1301: AND stage.party_id = p_party_id;
1302: ELSE
1303: OPEN x_cursor FOR
1304: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3
1305: FROM HZ_STAGED_PARTY_SITES stage
1306: WHERE contains( concat_col, p_contains_str)>0
1307: AND( (l_search_merged ='Y' )
1308: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1309: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1312: SELECT /+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) / stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3

1308: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1309: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1310: AND stage.party_id = p_party_id
1311: UNION
1312: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3
1313: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1314: WHERE contains( concat_col, p_contains_str)>0
1315: AND( (l_search_merged ='Y' )
1316: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1313: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc

1309: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1310: AND stage.party_id = p_party_id
1311: UNION
1312: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3
1313: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1314: WHERE contains( concat_col, p_contains_str)>0
1315: AND( (l_search_merged ='Y' )
1316: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1317: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1335: FROM HZ_STAGED_PARTY_SITES stage

1331: 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);
1332: END IF;
1333: OPEN x_cursor FOR
1334: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3
1335: FROM HZ_STAGED_PARTY_SITES stage
1336: WHERE contains( concat_col, p_contains_str)>0
1337: AND EXISTS (
1338: SELECT 1 FROM HZ_STAGED_PARTIES p
1339: WHERE p.PARTY_ID = stage.PARTY_ID

Line 1355: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage

1351: END IF;
1352: IF p_person_api = 'Y' THEN
1353: OPEN x_cursor FOR
1354: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3
1355: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1356: WHERE contains( concat_col, p_contains_str)>0
1357: AND( (l_search_merged ='Y' )
1358: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1359: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1364: SELECT /+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) / stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3

1360: AND d.search_context_id = p_search_ctx_id
1361: AND d.party_id = stage.party_id
1362: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1363: UNION
1364: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3
1365: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1366: WHERE contains( concat_col, p_contains_str)>0
1367: AND( (l_search_merged ='Y' )
1368: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1365: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage

1361: AND d.party_id = stage.party_id
1362: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1363: UNION
1364: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3
1365: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1366: WHERE contains( concat_col, p_contains_str)>0
1367: AND( (l_search_merged ='Y' )
1368: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1369: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1378: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage

1374: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
1375: ELSE
1376: OPEN x_cursor FOR
1377: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3
1378: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1379: WHERE contains( concat_col, p_contains_str)>0
1380: AND( (l_search_merged ='Y' )
1381: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1382: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1398: l_hint := '/+ INDEX(stage HZ_STAGED_PARTY_SITES_U1) /';

1394: IF (l_check_dt > 0 ) THEN
1395: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1396: hz_utility_v2pub.debug(p_message=>'Restrict sql has the selective_ps hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1397: END IF;
1398: l_hint := '/*+ INDEX(stage HZ_STAGED_PARTY_SITES_U1) */';
1399: ELSIF (l_check > 0 ) THEN
1400: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1401: hz_utility_v2pub.debug(p_message=>'Restrict sql has the selective hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1402: END IF;

Line 1403: l_hint := '/+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) /';

1399: ELSIF (l_check > 0 ) THEN
1400: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1401: hz_utility_v2pub.debug(p_message=>'Restrict sql has the selective hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1402: END IF;
1403: l_hint := '/*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */';
1404: END IF;
1405: IF l_search_merged = 'Y' THEN
1406: l_status_sql := ' ' ;
1407: ELSIF l_search_merged = 'I' THEN

Line 1422: l_sqlstr := 'SELECT /+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) / PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3'||

1418: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
1419: ELSE
1420: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1421: END IF;
1422: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3'||
1423: ' FROM HZ_STAGED_PARTY_SITES stage'||
1424: ' WHERE contains( concat_col, :cont)>0'||
1425: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1426: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 1423: ' FROM HZ_STAGED_PARTY_SITES stage'||

1419: ELSE
1420: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1421: END IF;
1422: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3'||
1423: ' FROM HZ_STAGED_PARTY_SITES stage'||
1424: ' WHERE contains( concat_col, :cont)>0'||
1425: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1426: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1427: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 1432: 'SELECT /+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) / PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3'||

1428: ' AND (ORG_CONTACT_ID IS NULL ' ||
1429: ' AND ('||p_restrict_sql||'))' ||
1430: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1431: ' UNION ' ||
1432: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3'||
1433: ' FROM HZ_STAGED_PARTY_SITES stage'||
1434: ' WHERE contains( concat_col, :cont)>0'||
1435: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1436: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 1433: ' FROM HZ_STAGED_PARTY_SITES stage'||

1429: ' AND ('||p_restrict_sql||'))' ||
1430: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1431: ' UNION ' ||
1432: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3'||
1433: ' FROM HZ_STAGED_PARTY_SITES stage'||
1434: ' WHERE contains( concat_col, :cont)>0'||
1435: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1436: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1437: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 1448: ' FROM HZ_STAGED_PARTY_SITES stage'||

1444: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1445: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1446: ELSE
1447: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3'||
1448: ' FROM HZ_STAGED_PARTY_SITES stage'||
1449: ' WHERE contains( concat_col, :cont)>0'||
1450: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1451: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1452: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 1465: ' FROM HZ_STAGED_PARTY_SITES stage'||

1461: ,p_dup_party_id, p_dup_party_id;
1462: END IF;
1463: ELSE
1464: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3'||
1465: ' FROM HZ_STAGED_PARTY_SITES stage'||
1466: ' WHERE contains( concat_col, :cont)>0'||
1467: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1468: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1469: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||