1864: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1865: 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);
1866: END IF;
1867: OPEN x_cursor FOR
1868: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1869: FROM HZ_STAGED_CONTACTS stage
1870: WHERE contains( concat_col, p_contains_str)>0
1871: AND EXISTS (
1872: SELECT 1 FROM HZ_STAGED_PARTIES p
1865: 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);
1866: END IF;
1867: OPEN x_cursor FOR
1868: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1869: FROM HZ_STAGED_CONTACTS stage
1870: WHERE contains( concat_col, p_contains_str)>0
1871: AND EXISTS (
1872: SELECT 1 FROM HZ_STAGED_PARTIES p
1873: WHERE p.PARTY_ID = stage.PARTY_ID
1888: 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);
1889: END IF;
1890: OPEN x_cursor FOR
1891: SELECT ORG_CONTACT_ID, PARTY_ID
1892: FROM HZ_STAGED_CONTACTS stage
1893: WHERE contains( concat_col, p_contains_str)>0
1894: AND EXISTS (
1895: SELECT 1 FROM HZ_STAGED_PARTIES p
1896: WHERE p.PARTY_ID = stage.PARTY_ID
1906: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1907: 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);
1908: END IF;
1909: OPEN x_cursor FOR
1910: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1911: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1912: WHERE contains( concat_col, p_contains_str)>0
1913: AND d.search_context_id = p_search_ctx_id
1914: AND d.party_id = stage.party_id
1907: 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);
1908: END IF;
1909: OPEN x_cursor FOR
1910: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1911: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1912: WHERE contains( concat_col, p_contains_str)>0
1913: AND d.search_context_id = p_search_ctx_id
1914: AND d.party_id = stage.party_id
1915: AND( (l_search_merged ='Y' )
1926: IF (l_check_dt > 0 ) THEN
1927: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1928: 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);
1929: END IF;
1930: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */';
1931: ELSIF (l_check > 0 ) THEN
1932: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1933: 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);
1934: END IF;
1931: ELSIF (l_check > 0 ) THEN
1932: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1933: 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);
1934: END IF;
1935: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */';
1936: END IF;
1937: IF l_search_merged = 'Y' THEN
1938: l_status_sql := ' ' ;
1939: ELSIF l_search_merged = 'I' THEN
1941: ELSE
1942: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1943: END IF;
1944: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
1945: ' FROM HZ_STAGED_CONTACTS stage'||
1946: ' WHERE contains( concat_col, :cont)>0'||
1947: ' AND EXISTS ('||
1948: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1949: ' WHERE p.party_id = stage.party_id ' ||
3556: p_search_ctx_id NUMBER,
3557: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3558: BEGIN
3559: OPEN x_cursor FOR
3560: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3561: FROM HZ_STAGED_CONTACTS stage
3562: WHERE contains( concat_col, p_contains_str)>0
3563: AND ORG_CONTACT_ID in (
3564: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3557: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3558: BEGIN
3559: OPEN x_cursor FOR
3560: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3561: FROM HZ_STAGED_CONTACTS stage
3562: WHERE contains( concat_col, p_contains_str)>0
3563: AND ORG_CONTACT_ID in (
3564: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3565: org_contact_id