1694: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1695: 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);
1696: END IF;
1697: OPEN x_cursor FOR
1698: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1699: FROM HZ_STAGED_CONTACTS stage
1700: WHERE contains( concat_col, p_contains_str)>0
1701: AND EXISTS (
1702: SELECT 1 FROM HZ_STAGED_PARTIES p
1695: 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);
1696: END IF;
1697: OPEN x_cursor FOR
1698: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1699: FROM HZ_STAGED_CONTACTS stage
1700: WHERE contains( concat_col, p_contains_str)>0
1701: AND EXISTS (
1702: SELECT 1 FROM HZ_STAGED_PARTIES p
1703: WHERE p.PARTY_ID = stage.PARTY_ID
1718: 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);
1719: END IF;
1720: OPEN x_cursor FOR
1721: SELECT ORG_CONTACT_ID, PARTY_ID
1722: FROM HZ_STAGED_CONTACTS stage
1723: WHERE contains( concat_col, p_contains_str)>0
1724: AND EXISTS (
1725: SELECT 1 FROM HZ_STAGED_PARTIES p
1726: WHERE p.PARTY_ID = stage.PARTY_ID
1736: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1737: 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);
1738: END IF;
1739: OPEN x_cursor FOR
1740: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1741: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1742: WHERE contains( concat_col, p_contains_str)>0
1743: AND d.search_context_id = p_search_ctx_id
1744: AND d.party_id = stage.party_id
1737: 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);
1738: END IF;
1739: OPEN x_cursor FOR
1740: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1741: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1742: WHERE contains( concat_col, p_contains_str)>0
1743: AND d.search_context_id = p_search_ctx_id
1744: AND d.party_id = stage.party_id
1745: AND( (l_search_merged ='Y' )
1756: IF (l_check_dt > 0 ) THEN
1757: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1758: hz_utility_v2pub.debug(p_message=>'Restrict sql has the selective_ct hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1759: END IF;
1760: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */';
1761: ELSIF (l_check > 0 ) THEN
1762: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1763: 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);
1764: END IF;
1761: ELSIF (l_check > 0 ) THEN
1762: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1763: 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);
1764: END IF;
1765: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */';
1766: END IF;
1767: IF l_search_merged = 'Y' THEN
1768: l_status_sql := ' ' ;
1769: ELSIF l_search_merged = 'I' THEN
1771: ELSE
1772: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1773: END IF;
1774: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
1775: ' FROM HZ_STAGED_CONTACTS stage'||
1776: ' WHERE contains( concat_col, :cont)>0'||
1777: ' AND EXISTS ('||
1778: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1779: ' WHERE p.party_id = stage.party_id ' ||
3378: p_search_ctx_id NUMBER,
3379: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3380: BEGIN
3381: OPEN x_cursor FOR
3382: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3383: FROM HZ_STAGED_CONTACTS stage
3384: WHERE contains( concat_col, p_contains_str)>0
3385: AND ORG_CONTACT_ID in (
3386: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3379: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3380: BEGIN
3381: OPEN x_cursor FOR
3382: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3383: FROM HZ_STAGED_CONTACTS stage
3384: WHERE contains( concat_col, p_contains_str)>0
3385: AND ORG_CONTACT_ID in (
3386: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3387: org_contact_id