1532: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1533: 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);
1534: END IF;
1535: OPEN x_cursor FOR
1536: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1537: FROM HZ_STAGED_CONTACTS stage
1538: WHERE contains( concat_col, p_contains_str)>0
1539: AND EXISTS (
1540: SELECT 1 FROM HZ_STAGED_PARTIES p
1533: 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);
1534: END IF;
1535: OPEN x_cursor FOR
1536: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1537: FROM HZ_STAGED_CONTACTS stage
1538: WHERE contains( concat_col, p_contains_str)>0
1539: AND EXISTS (
1540: SELECT 1 FROM HZ_STAGED_PARTIES p
1541: WHERE p.PARTY_ID = stage.PARTY_ID
1556: 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);
1557: END IF;
1558: OPEN x_cursor FOR
1559: SELECT ORG_CONTACT_ID, PARTY_ID
1560: FROM HZ_STAGED_CONTACTS 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
1574: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1575: 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);
1576: END IF;
1577: OPEN x_cursor FOR
1578: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1579: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1580: WHERE contains( concat_col, p_contains_str)>0
1581: AND d.search_context_id = p_search_ctx_id
1582: AND d.party_id = stage.party_id
1575: 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);
1576: END IF;
1577: OPEN x_cursor FOR
1578: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1579: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1580: WHERE contains( concat_col, p_contains_str)>0
1581: AND d.search_context_id = p_search_ctx_id
1582: AND d.party_id = stage.party_id
1583: AND( (l_search_merged ='Y' )
1594: IF (l_check_dt > 0 ) THEN
1595: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1596: 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);
1597: END IF;
1598: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */';
1599: ELSIF (l_check > 0 ) THEN
1600: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1601: 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);
1602: END IF;
1599: ELSIF (l_check > 0 ) THEN
1600: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1601: 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);
1602: END IF;
1603: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */';
1604: END IF;
1605: IF l_search_merged = 'Y' THEN
1606: l_status_sql := ' ' ;
1607: ELSIF l_search_merged = 'I' THEN
1609: ELSE
1610: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1611: END IF;
1612: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
1613: ' FROM HZ_STAGED_CONTACTS stage'||
1614: ' WHERE contains( concat_col, :cont)>0'||
1615: ' AND EXISTS ('||
1616: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1617: ' WHERE p.party_id = stage.party_id ' ||
3170: p_search_ctx_id NUMBER,
3171: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3172: BEGIN
3173: OPEN x_cursor FOR
3174: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3175: FROM HZ_STAGED_CONTACTS stage
3176: WHERE contains( concat_col, p_contains_str)>0
3177: AND ORG_CONTACT_ID in (
3178: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3171: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3172: BEGIN
3173: OPEN x_cursor FOR
3174: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3175: FROM HZ_STAGED_CONTACTS stage
3176: WHERE contains( concat_col, p_contains_str)>0
3177: AND ORG_CONTACT_ID in (
3178: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3179: org_contact_id